#!/bin/bash
#< Script to demonstrate how to dynamically generate SQL, then pull data from Sybase
# Dumps to a pipe delimited file - called via cron at 2am, to pull data from the
# previous day....

AWK="/bin/awk"
BASENAME="/bin/basename"
DATE="/bin/date"
ECHO="/bin/echo"
GREP="/bin/grep"
ID="/usr/bin/id"
ISQL_BINARY="/opt/sybase/OCS-12_5/bin/isql"
MV="/bin/mv"
RM="/bin/rm"
SED="/bin/sed"
UNIX2DOS="/usr/bin/unix2dos"

AUTHOR="Kevin Waldron"
MAJOR_VER="0"
MINOR_VER="1"
OUTPUT_DELIM="|"
TMP_OUTPUT_DIR="/var/tmp"
OUTPUT_DIR="/backup"
SYBASE_DB="SOMEDB"
SYBASE_PASS="somepass"
SYBASE_USER="sa"
SYBASE_WIDTH="65535"
THIS_PROG=$( ${BASENAME} $0 )
USE_DB="somedbname"
TABLE_NAME="sometablename"
TMP_FILE="/var/tmp/tmp_$$.sql"
VERSION="${MAJOR_VER}.${MINOR_VER}"
YEAR_WRITTEN="2006"

OUTPUT_FILE="${TMP_OUTPUT_DIR}/${USE_DB}-sometablename-$( ${DATE} +%d-%b-%Y ).out"
ISQL_OPTIONS="-U${SYBASE_USER} -P${SYBASE_PASS} -S${SYBASE_DB} -w${SYBASE_WIDTH} -s${OUTPUT_DELIM} -i${TMP_FILE} -o${OUTPUT_FILE}"
ISQL="${ISQL_BINARY} ${ISQL_OPTIONS}"

trap "${RM} -f ${TMP_FILE}; exit 1" 1 2 3 15

echo_stderr() {
  ${ECHO} "$@" >&2
}

usage() {
  echo_stderr "${THIS_PROG} [-h]
  -h  Print this help message"
}

check_uid() {
  MY_USERNAME=$( ${GREP} "^[^:]*:[^:]*:$( ${ID} -u ):.*$" /etc/passwd | ${AWK} -vFS=':' '{print $1}' )
  if [ "${MY_USERNAME}" != "sybase" ]; then
     return 1
  else
     return 0
  fi
}

set_date_vars() {
  YESTERDAY_DAY=$( ${DATE} -d yesterday +%d | ${SED} 's/^0//' )
  YESTERDAY_MONTH=$( ${DATE} -d yesterday +%b )
  YESTERDAY_YEAR=$( ${DATE} -d yesterday +%Y )
  START_TIME="00:00:00"
  END_TIME="23:59:59"
  START_DATE="${YESTERDAY_DAY}-${YESTERDAY_MONTH}-${YESTERDAY_YEAR} ${START_TIME}"
  END_DATE="${YESTERDAY_DAY}-${YESTERDAY_MONTH}-${YESTERDAY_YEAR} ${END_TIME}"
}

generate_sql() {
  set_date_vars
  exec 3>&1 1>${TMP_FILE}
  printf "use ${USE_DB}\n"
  printf "go\n"
  printf "select * from ${TABLE_NAME} "
  printf "where dateadd(hh, 10, time_of_contact) between \'${START_DATE}\' and \'${END_DATE}\'\n"
  printf "go\n"
  exec 1>&3-
}

post_process() {
  ${SED} -e 's/[ ]*|/|/g' -e 's/|[ ]*/|/g' ${OUTPUT_FILE} > ${OUTPUT_FILE}.post_process
  ${SED} '1,2d' ${OUTPUT_FILE}.post_process | ${SED} '$d' | ${SED} '$d' > ${OUTPUT_FILE}.post_process_2
  ${MV} ${OUTPUT_FILE}.post_process_2 ${OUTPUT_FILE}
  ${RM} -f ${OUTPUT_FILE}.post_process
  ${UNIX2DOS} ${OUTPUT_FILE}
  ${MV} ${OUTPUT_FILE} ${OUTPUT_DIR}
}

execute_sql() {
  ${ISQL}
  post_process
}

check_uid
if [ "$?" -ne "0" ]; then
  echo_stderr "<-- You must be sybase to run this script"
  exit 2
fi

if [ "$#" -eq "1" ]; then
  case $1 in
     -h)  usage
          exit 0
          ;;
     *)   usage
          exit 0
  esac
fi

generate_sql
execute_sql

${RM} -f ${TMP_FILE}

exit 0