#! /bin/sh

SDATE="$1"
EDATE="$2"
distid="$3"

#######################
# Testing
SDATE="01/06/2012"
EDATE="30/06/2012"
distid="1001"

#------------------------------------------------------------------

progtag="vd1"

outcsvfile="${progtag}_out.csv"
rm -f "$outcsvfile"

mismatchcsvfile="${progtag}_mismatch.csv"
rm -f "$mismatchcsvfile"

IOFFSETDAYS="9"

# convert EDATE to YYYYMMDD
EYYYYMMDD=`awk -v "ADATE=$EDATE" -v "sep=" '
BEGIN {
        dd = 0 + substr(ADATE,1,2);
        mm = 0 + substr(ADATE,4,2);
        yyyy = 0 + substr(ADATE,7,4);
        YYYYMMDD = sprintf("%04d%s%02d%s%02d", yyyy, sep, mm, sep, dd);
        print YYYYMMDD
        exit
}'`

MMYYYY=`SDATEtoMMYYYY $SDATE`

billperiod=`MMYYYYtobillperiod $MMYYYY "_"`

tbsbillperiod=`MMYYYYtotbsbillperiod $MMYYYY`

#--------------------------
# Bilmax21 DISTID csv format
#csvstr = sprintf("%s,%s,%s,%s,%s,%s,CUSTOMERcode,CUSTOMERdesc,SHIPTO,groupdesc,group,centredesc,centre,projectdesc,project,projectfilter,ccpdesc,servicetypedesc,servicetypeID,reportgroupdesc,reportgroupid,supplier,invoiceno,sundry,chargecategory,totcount:Int,totduration:Int,totcost:Float\n", "V2 Cost Centre Summary", exbillperiod, "SDATE", "EDATE", DISTIDdesc, DISTID)
#--------------------------


awk -F ',' \
 -v "SDATE=$SDATE" -v"EDATE=$EDATE" \
 -v "billperiod=$billperiod" \
 -v "tbsbillperiod=$tbsbillperiod" \
 -v "DISTID=$distid"  \
 -v "IOFFSETDAYS=$IOFFSETDAYS" \
 -v "MMYYYY=$MMYYYY" \
 -v "EYYYYMMDD=$EYYYYMMDD" \
 -v "outcsvfile=$outcsvfile" \
 -v "mismatchcsvfile=$mismatchcsvfile" \
'

function trim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	gsub(/\,/,";",s)
	return s
}


function get_invoice_total()
{
	#VNA,GRP1710,DEPT2583,CENTRE3620
	SHIPTO = d_SHIPTO
	groupid = substr(d_group,4)
	departmentid = substr(d_centre,5)
	centreid = substr(d_project,7)
	batchid = d_sundry
	invoiceno = d_invoiceno

	#if ( invoiceno == "VRT999999" ) {
	#	INVOICENOSQLBIT = "tInvoice.PlatinumInvoiceNo IS NULL"
	#}
	#else  {
		INVOICENOSQLBIT = "tInvoice.PlatinumInvoiceNo = \\\"" invoiceno "\\\""
	#}

	s=""
	s=s "SELECT"
	s=s " tInvoice.PlatinumInvoiceNo AS InvoiceNo,"
	s=s " tCustomer.ShipTo, tCustomer.FullName AS Customer,"
	s=s " tBatch.ID AS BatchID,"
	s=s " tBatchType.Description AS BatchType, "
	s=s " tSupplier.SupplierName,"
	s=s " tBatch.BillingPeriod,"
	s=s " SUM(tInvoiceDetail.AmountExGST)"
	s=s " FROM tInvoice INNER JOIN"
	s=s "      tInvoiceDetail ON tInvoice.ID = tInvoiceDetail.InvoiceID INNER JOIN"
	s=s "      tBatch ON tInvoiceDetail.BatchID = tBatch.ID INNER JOIN"
	s=s "      tBatchType ON tInvoiceDetail.BatchTypeID = tBatchType.ID INNER JOIN"
	s=s "      tCustomer ON tInvoice.CustomerID = tCustomer.ID INNER JOIN"
	s=s "      tSupplier ON tBatch.SupplierID = tSupplier.ID"
	#s=s " WHERE tInvoice.PlatinumInvoiceNo = \\\"" invoiceno "\\\""
	s=s " WHERE " INVOICENOSQLBIT
	s=s "   AND tCustomer.ShipTO = \\\"" SHIPTO "\\\""
	s=s "   AND tBatch.ID = " batchid

	#s=s "   AND tInvoiceDetail.Period = \\\"" tbsbillingperiod "\\\""
	s=s "   AND tInvoice.InvoiceDate > dateadd(dd," IOFFSETDAYS ",\\\"" EYYYYMMDD "\\\")"
	s=s " AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd," IOFFSETDAYS ",\\\"" EYYYYMMDD "\\\"))"

	s=s" GROUP BY "
	s=s"  tInvoice.PlatinumInvoiceNo,"
	s=s"  tCustomer.ShipTo, tCustomer.FullName,"
	s=s"  tBatch.ID,"
	s=s"  tBatchType.Description,"
	s=s"  tSupplier.SupplierName,"
	s=s"  tBatch.BillingPeriod"
	##print "get_invouice_totol(): sql=[" s "]"

	invsql = s
	sqlresult = ""
	cmd = "echo \"" invsql "\"  | tbssql"
	cmd | getline sqlresult
	close(cmd)

	split(sqlresult, sqlresult_arr, "|")
	invtotcost = sqlresult_arr[8]
	#invtotcost = sqlresult_arr[1]

	return invtotcost
}

#--------------------------------------------------------------
# main

BEGIN {
        print "Invoice cost totals comparison"
	print ""
	print "           SDATE=[" SDATE "]" "  EDATE=[" EDATE "]"
	print "     IOFFSETDAYS=[" IOFFSETDAYS "]"
	print "       EYYYYMMDD=[" EYYYYMMDD "]"
	print "          MMYYYY=[" MMYYYY "]"
	print "      billperiod=[" billperiod "]"
	print "   tbsbillperiod=[" tbsbillperiod "]"
	print ""

	monthdir = billperiod
	DISTID_fname = sprintf("topdat/%s/%s/data/distid/distid_%s.csv", monthdir, DISTID, DISTID)
	print "    DISTID_fname=[" DISTID_fname "]"
	print ""

	ivid = 0
        while ( (getline d_aline < DISTID_fname) > 0 ) {
                split(d_aline, a_arr, ",")
                ##printf("d_aline=%s\n", d_aline)
		f = 0
		d_ReportDesc = trim(a_arr[++f])
		d_exbillperiod = trim(a_arr[++f])
		d_SDATE = trim(a_arr[++f])
		d_EDATE = trim(a_arr[++f])
		d_DISTIDdesc = trim(a_arr[++f])
		d_DISTID = trim(a_arr[++f])
		d_CUSTOMERcode = trim(a_arr[++f])
		d_CUSTOMERdesc = trim(a_arr[++f])
		d_SHIPTO = trim(a_arr[++f])
		d_groupdesc = trim(a_arr[++f])
		d_group = trim(a_arr[++f])
		d_centredesc = trim(a_arr[++f])
		d_centre = trim(a_arr[++f])
		d_projectdesc = trim(a_arr[++f])
		d_project = trim(a_arr[++f])
		d_projectfilter = trim(a_arr[++f])
		d_ccpdesc = trim(a_arr[++f])
		d_servicetypedesc = trim(a_arr[++f])
		d_servicetypeID = trim(a_arr[++f])
		d_reportgroupdesc = trim(a_arr[++f])
		d_reportgroupid = trim(a_arr[++f])
		d_supplier = trim(a_arr[++f])
		d_invoiceno = trim(a_arr[++f])
		d_sundry = trim(a_arr[++f])
		d_chargecategory = trim(a_arr[++f])
		d_totcount = trim(a_arr[++f])
		d_totduration = trim(a_arr[++f])
		d_totcost = trim(a_arr[++f])

		if ( d_SHIPTO != "All" &&
		     d_group == "All" &&
		     d_centre == "All" &&
		     d_project == "All" && d_projectfilter == "All" &&
		     d_supplier != "All" &&
		     d_invoiceno != "All" &&
		     d_sundry != "All" &&
		     d_servicetypeID != "All" &&
		     d_chargecategory == "All") {

			if ( d_invoiceno == "VRT999999" )
				continue

			invtag = billperiod "|" d_SHIPTO "|" d_invoiceno "|" d_sundry
			#print "invtag=[" invtag "]"

			# add up ...
			#VicTrack Rent and Calls - Switchboard
			#VicTrack Rent and Calls - Voicemail
			#VicTrack Rent and Calls - Calls
			victrackrentandcalls = "VicTrack Rent and Calls"
			regexp1 = "^" victrackrentandcalls
			if ( match(d_servicetypedesc,regexp1) ) {
				vid = vtrandc_vid_arr[invtag]	
				if ( vid == "" ) {
					vid = ++ivid
					print "New: " d_servicetypedesc "  invtag=[" invtag "]" "  vid=[" vid "]"
					vtrandc_vid_arr[invtag] = vid
					d_servicetypedesc =victrackrentandcalls
				}
				else {
					print "Reuse: " d_servicetypedesc "  invtag=[" invtag "]" "  vid=[" vid "]"
				}
			}
			else {
				vid = ++ivid
			}

			if ( d_aline_arr[vid] == "" ) {
				d_aline_arr[vid] = d_aline
				d_SHIPTO_arr[vid] = d_SHIPTO
				d_groupid_arr[vid] = d_groupid
				d_departmentid_arr[vid] = d_departmentid
				d_centreid_arr[vid] = d_centreid
				d_supplier_arr[vid] = d_supplier
				d_invoiceno_arr[vid] = d_invoiceno
				d_sundry_arr[vid] = d_sundry
				d_servicetypedesc_arr[vid] = d_servicetypedesc
			}
			if ( d_totcost_arr[vid] == "" )
				d_totcost_arr[vid] = 0
			d_totcost_arr[vid] += d_totcost

		}
        }
        close(DISTID_fname)
	maxvid = ivid
	print "maxvid=" maxvid
	
	print "validating..."
	outcount = 0
	mismatchcount = 0

	totcostdiff = 0.00

	# csv header - out csv
	print "OutCount,ShipTo,Supplier,InvoiceNo,BatchID,ServiceTypeDesc,BilmaxTotCost,InvoiceTotCost,CostDiff" >>outcsvfile

	# csv header - mismatch csv
	print "MisMatchCount,ShipTo,Supplier,InvoiceNo,BatchID,ServiceTypeDesc,BilmaxTotCost,InvoiceTotCost,CostDiff" >>mismatchcsvfile

	for ( vid=1; vid <= maxvid; ++vid ) { 
		d_aline = d_aline_arr[vid]
		d_SHIPTO = d_SHIPTO_arr[vid]
		d_groupid = d_groupid_arr[vid]
		d_department = d_departmentid_arr[vid]
		d_centreid = d_centreid_arr[vid]
		d_supplier = d_supplier_arr[vid]
		d_invoiceno = d_invoiceno_arr[vid]
		d_sundry = d_sundry_arr[vid]
		d_servicetypedesc = d_servicetypedesc_arr[vid]
		d_totcost = sprintf("%0.2f", d_totcost_arr[vid])

		invtag = billperiod "|" d_SHIPTO "|" d_invoiceno "|" d_sundry

		# Testing
		#if ( d_invoiceno != "INV018864" )
		#	continue
		print "-------"
		print "d_aline (vid=" vid "): " d_aline
		#print d_SHIPTO "," d_group "," d_centre "," d_project "," d_supplier "," d_invoiceno "," d_sundry "," d_servicetypedesc "," d_totcost
		print d_SHIPTO "," d_supplier "," d_invoiceno "," d_sundry "," d_servicetypedesc "," d_totcost
		if ( vtrandc_vid_arr[invtag] != "" ) {
			print "vtrandc:"
		}

		get_invoice_total()

		invtotcost = sprintf("%0.2f", invtotcost)
		print "invtotcost=[" invtotcost "]"

		costdiff = ""
		if ( d_totcost != invtotcost ) {
			print "sql=[" s "]"
			print "sqlresultt=[" sqlresult "]"
			#print "sqlresult: invtotcost=[" invtotcost "]"
			print ""
			++mismatchcount

			costdiff = d_totcost - invtotcost
totcostdiff += costdiff

			printf("MISMATCH %d bilmax21_cost=[%0.2f] - get_invoice_total()=[%0.2f] (costdiff=%0.2f)\n", mismatchcount, d_totcost, invtotcost, costdiff)
			print d_SHIPTO "," d_supplier "," d_invoiceno "," d_sundry "," d_servicetypedesc "," d_totcost
			#print " d_aline: " d_aline

			# csv data - mismatch cev
			printf("%d,%s,%s,%s,%s,%s,%0.2f,%0.2f,%s\n", mismatchcount, d_SHIPTO, d_supplier, d_invoiceno, d_sundry, d_servicetypedesc, d_totcost, invtotcost, (costdiff == "" ? "" : sprintf("%0.2f",costdiff))) >> mismatchcsvfile
		}
		else {
			print "MATCHED"
		}

		++outcount
		# csv data - out cev
		printf("%d,%s,%s,%s,%s,%s,%0.2f,%0.2f,%s\n", outcount, d_SHIPTO, d_supplier, d_invoiceno, d_sundry, d_servicetypedesc, d_totcost, invtotcost, (costdiff == "" ? "" : sprintf("%0.2f",costdiff))) >> outcsvfile

	}
	print ""
	if ( mismatchcount == 0)
		printf("No mismatched totals outcount=[%d]\n", outcount)
	else
		printf("outcount=[%d]  mismatchcount=[%d]  totcostdiff=[%0.2f]\n", outcount, mismatchcount, totcostdiff)

	exit(0)
}
' >vd1.out

t21em_content_file="vd1.out"
export t21em_content_file


ematt "$outcsvfile" "Bilmax21 - Invoice totals validation" rod@transmit.com.au

ematt "$mismatchcsvfile" "Bilmax21 - Invoice totals validation mismatches" rod@transmit.com.au

/usr/mmdf/bin/deliver
exit 0