: !/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 SHIPTO env var # $5 will override BATCHID env var # $6 will override INVOICENO env var # $7 will override default outdir # $8 will override default outfilepref ############################# ## Testing SDATE="01/04/2010" EDATE="30/04/2010" IYYYYMMDD="20100430" IOFFSETDAYS="9" APERIOD="2010/04" SHIPTO="" ##BATCHID="33671" ###BATCHID="33792" #INVOICENO="INV003314" #outdir="." outdir="import" ############################# 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 echo "IYYYYMMDD = [$IYYYYMMDD]" IYYYYMM=`echo "$IYYYYMMDD" | cut -c1-6` 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 ndays" 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 YYYY=`awk -v"IYYYYMM=$IYYYYMM" 'BEGIN { print substr(IYYYYMM,1,4); exit 0}'` MM=`awk -v"IYYYYMM=$IYYYYMM" 'BEGIN { print substr(IYYYYMM,5,2); exit 0}'` MMYYYY="${MM}${YYYY}" echo "MMYYYY = [$MMYYYY]" billperiod=`MMYYYYtobillperiod $MMYYYY _` echo "billperiod = [$billperiod]" tbsbillingperiod="$APERIOD" echo "tbsbillingperiod=[$tbsbillingperiod]" AYYYYMM=`echo "$tbsbillingperiod" | sed 's/\///'` echo "AYYYYMM=[$AYYYYMM]" #transactiontable="tFinalisedTransaction" transactiontable="tFinalisedTransaction${AYYYYMM}" 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]" if [ -n "$4" ]; then SHIPTO="$4" else if [ -z "$SHIPTO" ]; then SHIPTO="all" fi fi if [ "$SHIPTO" != "all" -a "$SHIPTO" != "" ]; then SHIPTOBIT=" and tCustomer.ShipTo = '$SHIPTO'" else SHIPTOBIT="" fi if [ -n "$5" ]; then BATCHID="$5" else if [ -z "$BATCHID" ]; then BATCHID="all" fi fi if [ "$BATCHID" != "all" -a "$BATCHID" != "" ]; then BATCHIDBIT=" and ${transactiontable}.BatchID = $BATCHID" else BATCHIDBIT="" fi if [ -n "$6" ]; then INVOICENO="$6" else if [ -z "$INVOICENO" ]; then INVOICENO="all" fi fi if [ "$INVOICENO" != "all" -a "$INVOICENO" != "" ]; then INVOICENOBIT=" and tInvoice.PlatinumInvoiceNo = '$INVOICENO'" else INVOICENOBIT="" fi if [ -n "$7" ]; then outdir="$7" else if [ -z "$outdir" ]; then #outdir="mcsv_${IYYYYMM}_${BATCHID}" #outdir="$billperiod/all_${SHIPTO}/${divgroup}/${SHIPTO}" outdir="." fi fi if [ -n "$8" ]; then outfilepref="$8" else outfilepref="import" fi echo "$0: for $IYYYYMMDD, $IOFFSETDAYS, $APERIOD, $billperiod" echo " batchid $BATCHID shipto $SHIPTO invoiceno $INVOICENO" echo " to outdir $outdir outfilepref $outfilepref" . tbsserver_vars if [ "$outdir" != "." -a -d "${outdir}" ]; then rm -r "${outdir}" mkdir -p "${outdir}" fi if [ ! -d "${outdir}" ]; then mkdir -p "${outdir}" fi #-------------------------------------------------------------- ( #cat </dev/null < "VRT" AND tBatchType.Active = 1 AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD') AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')) $EXCLUDEINVBIT $EXCLUDEBATCHIDBIT $EXCLUDEINVBIT $EXCLUDEBATCHIDBIT GROUP BY tElement.Description, tServiceID.ServiceID, tInvoiceDetail.BatchID, tBatchType.SupplierDisplayedName, tLocation.Description, tPerson.Person, tCentre.Description, tServiceType.Description, tInvoiceDetail.Description, tSubledger.Description, tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, ${transactiontable}.Var01, ${transactiontable}.Var02, ${transactiontable}.Var03, ${transactiontable}.Var04, ${transactiontable}.Var05, tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, tDepartment.Description, ${transactiontable}.PayType, tActivity.Description, tElement_1.Description ORDER BY tCentre.Description \go -m bcp SQLCMD0 #--------------------------- if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then #cat </dev/null < 1 ) printf(",") >> ofile printf("%s", fldname) >> ofile } printf("\n") >> ofile close(ofile) done_header[ofile] = "1" } # output record for ( i = 1; i <= (NF-1); ++i ) { fldname = fldname_arr[i] fldvalue = trim($i) fldvalue_arr[fldname] = fldvalue if ( i > 1 ) printf(",") >> ofile printf("%s", fldvalue) >> ofile } printf("\n") >> ofile close(ofile) # create summary #The Monthly summary groups and shows the following fields from the above dataset: #. Invoice (Platinum Invoice) #. Cost Centre (tServiceID!CustomerCentre) #. Cost Centre Description (tDepartment.Description AS DeptDesc) #. SubLedger (tServiceID.CustomerSubLedgerID) #. Sum of ExGST #. Sum of GST #. Sum of IncGST # ... costcentre = fldvalue_arr["CustCentre"] department = fldvalue_arr["DeptDesc"] subledger = fldvalue_arr["CustSubledger"] ExGST = fldvalue_arr["ExGST"] GST = fldvalue_arr["GST"] IncGST = fldvalue_arr["IncGST"] atag = shipto "|" invoiceno "|" costcentre "|" department "|" subledger atag_arr[atag] = 1 ExGST_total_arr[atag] += ExGST GST_total_arr[atag] += GST IncGST_total_arr[atag] += IncGST } END { # create temp sorted atag file tmpatagfile = "/tmp/getmcsvtmpatag_" pid system("rm -f " tmpatagfile) sortcmd = "sort >" tmpatagfile for ( atag in atag_arr ) { print atag | sortcmd } close(sortcmd) # output summary while ( (getline atag < tmpatagfile) > 0 ) { split(atag,a_arr,"|") f = 0 shipto = a_arr[++f] invoiceno = a_arr[++f] CustCentre = a_arr[++f] DeptDesc = a_arr[++f] CustSubledger = a_arr[++f] ExGST_total = ExGST_total_arr[atag] GST_total = GST_total_arr[atag] IncGST_total = IncGST_total_arr[atag] #print "atag=[" atag "]" " totals ExGST=[" ExGST_total "]" " GST=[" GST_total "]" " IncGST=[" IncGST_total "]" >"sumtots" summary_ofile = outfilepref "_" shipto "_" billperiod "_" invoiceno "_summary.csv" ofile = outdir "/" summary_ofile # create file with header if ( done_header[ofile] == "" ) { print "ofile=[" ofile "]" system("rm -f \"" ofile "\"") printf("%s,%s,%s,%s,%s,%s\n", "PlatinumInvoiceNo", "CustCentre", "DeptDesc", "CustSubledger", "ExGST", "GST", "IncGST") > ofile done_header[ofile] = "1" } # output record printf("%s,%s,%s,%0.2f,%0.2f,%0.2f\n", invoiceno, CustCentre, DeptDesc, CustSubledger, ExGST_total, GST_total, IncGST_total) >> ofile close(ofile) } close(tmpatagfile) system("rm -f " tmpatagfile) } ' # eg. data #Telco Rentals & Calls|1000PAS|33667|VICTRACK|PAS 570 BOURKE ST||10000|||34572||TELCO RENTAL&CALLS|VicTrack Rent and Calls APR 2010|2010/04|VLP|INV003314|0.05|0.01|0.06|CALL|15937|97|23085|out|1000PAS|VicTrack Rent and Calls|Network Services|CHIEF FINANCIAL OFFICER|