: !/bin/sh # IYYYYMMDD env var must me set to which month # APERIOD env var must me set to which month # $1 will override IYYYYMMDD env var # $2 will override IOFFSETDAYS env var # $3 will override APERIOD env var # $4 will override BATCHID env var # $5 will override default outfile ## Testing #IYYYYMMDD="20120131" #IOFFSETDAYS="9" #APERIOD="2012/01" if [ -n "$1" ]; then IYYYYMMDD="$1" export IYYYYMMDD fi if [ -z "$IYYYYMMDD" ]; then echo "$0: Must set invoiced date IYYYYMMDD env var OR $0 yyyymmdd" exit 1 fi if [ -n "$2" ]; then IOFFSETDAYS="$2" export IOFFSETDAYS fi if [ -z "$IOFFSETDAYS" ]; then echo "$0: Must set invoiced date offset days IOFFSETDAYS env var OR $0 days" exit 1 fi if [ -n "$3" ]; then APERIOD="$3" export APERIOD fi if [ -z "$APERIOD" ]; then echo "$0: Must set bill Period date APERIOD env var OR $0 yyyy/mm" exit 1 fi if [ -n "$4" -a "$4" != "all" ]; then BATCHID="$4" BATCHIDBIT=" and ${transactiontable}.BatchID = $BATCHID" else BATCHID="all" BATCHIDBIT="" fi if [ -n "$5" ]; then outfile="$5" else outfile="gtch1.unl" fi #--------------------------------------- echo "$0: for $APERIOD to file $outfile" . tbsserver_vars tbsbillingperiod="$APERIOD" #echo "tbsbillingperiod=[$tbsbillingperiod]" YYYYMM=`echo "$tbsbillingperiod" | sed 's/\///'` #echo "YYYYMM=[$YYYYMM]" #transactiontable="tFinalisedTransaction" transactiontable="tFinalisedTransaction${YYYYMM}" echo "transactiontable=[$transactiontable]" CURMMYYYY=`awk -v"EYYYYMMDD=$IYYYYMMDD" 'BEGIN { print substr(EYYYYMMDD,5,2) substr(EYYYYMMDD,1,4) }'` #echo "CURMMYYYY=[$CURMMYYYY]" curtbsbillingperiod=`MMYYYYtotbsbillperiod $CURMMYYYY /` echo "curtbsbillingperiod=[$curtbsbillingperiod]" #---------------------------- rm -f "$outfile" tbssql >>"$outfile" < "VRT" --# and tBatchType.Active = 1 AND ${transactiontable}.RevenueFlag = 1 AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD') AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')) $EXCLUDEINVBIT $EXCLUDEBATCHIDBIT SQLCMD1 #---------------------------- if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then echo "Do VRT" tbssql >>"$outfile" <