: !/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"
#
#SDATE="01/12/2010"
#EDATE="31/12/2010"
#IYYYYMMDD="20101231"
#
IOFFSETDAYS="9"
APERIOD="2010/04"
#SHIPTO=""
#BATCHID="33671"
##BATCHID="33792"
INVOICENO="INV003314"
#outdir="."
outdir="import"
outfilepref="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="."
        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 <<SQLCMD0
#cat >/dev/null <<SQLCMD0
#sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
tbssql <<SQLCMD0
SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 tInvoiceDetail.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element,
 tSubledger.Description AS SubLedger,
 ${transactiontable}.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 SUM(${transactiontable}.AmountExGST) AS ExGST,
 SUM(${transactiontable}.GSTAmount) AS GST,
 SUM(${transactiontable}.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 ${transactiontable}.Var01,
 ${transactiontable}.Var02,
 ${transactiontable}.Var03,
 ${transactiontable}.Var04,
 ${transactiontable}.Var05,
 ${transactiontable}.Var06,
 ${transactiontable}.Var07,
 ${transactiontable}.Var08,
 ${transactiontable}.Var09,
 ${transactiontable}.Var10,
 ${transactiontable}.Var11,
 ${transactiontable}.Var12,
 ${transactiontable}.Var13,
 ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 ${transactiontable}
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = ${transactiontable}.BatchID
 LEFT JOIN tServiceID
        ON ${transactiontable}.ServiceID = tServiceID.ID
 INNER JOIN ( tInvoiceDetail
		INNER JOIN tInvoice
			ON tInvoiceDetail.InvoiceID = tInvoice.ID
--#			   and tInvoice.ID NOT IN (
--#				SELECT distinct tInvoice.CreditNoteInvID
--#				FROM tInvoice
--#				WHERE tInvoice.CreditNoteInvID is not null
--#			)
	) ON ${transactiontable}.InvoiceDetailID = tInvoiceDetail.ID
 LEFT JOIN tTransactionType
	ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON ${transactiontable}.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON ${transactiontable}.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON ${transactiontable}.ServiceTypeID = tServiceType.ID

 LEFT JOIN tCustomer
        ON ${transactiontable}.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON ${transactiontable}.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON ${transactiontable}.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON ${transactiontable}.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON ${transactiontable}.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON ${transactiontable}.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON ${transactiontable}.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '$tbsbillingperiod' $SHIPTOBIT $BATCHIDBIT $INVOICENOBIT
        AND ${transactiontable}.RevenueFlag = 1
        AND tCustomer.ShipTo <> "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, tServiceID.Service,
 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, ${transactiontable}.Var06,
 ${transactiontable}.Var07, ${transactiontable}.Var08,
 ${transactiontable}.Var09, ${transactiontable}.Var10,
 ${transactiontable}.Var11, ${transactiontable}.Var12,
 ${transactiontable}.Var13, ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 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 <<SQLCMD1
#cat >/dev/null <<SQLCMD1
#sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD1
tbssql <<SQLCMD1
SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 ${transactiontable}.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element,
 tSubledger.Description AS SubLedger,
 ${transactiontable}.PayType,
 tServiceType.Description AS ServiceType,
 '' AS InvoiceDescription,
 tBatch.BillingPeriod AS Period,
 tCustomer.ShipTo,
 'VRT999999' AS Invoice,
 SUM(${transactiontable}.AmountExGST) AS ExGST,
 SUM(${transactiontable}.GSTAmount) AS GST,
 SUM(${transactiontable}.AmountIncGST) AS IncGST,
 tTransactionGroup.Description AS TxnGrp,
 ${transactiontable}.Var01,
 ${transactiontable}.Var02,
 ${transactiontable}.Var03,
 ${transactiontable}.Var04,
 ${transactiontable}.Var05,
 ${transactiontable}.Var06,
 ${transactiontable}.Var07,
 ${transactiontable}.Var08,
 ${transactiontable}.Var09,
 ${transactiontable}.Var10,
 ${transactiontable}.Var11,
 ${transactiontable}.Var12,
 ${transactiontable}.Var13,
 ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service
FROM
 ${transactiontable}
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = ${transactiontable}.BatchID
 LEFT JOIN tServiceID
        ON ${transactiontable}.ServiceID = tServiceID.ID
 LEFT JOIN tTransactionType
	ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON ${transactiontable}.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON ${transactiontable}.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON ${transactiontable}.ServiceTypeID = tServiceType.ID

 LEFT JOIN tCustomer
        ON ${transactiontable}.CustomerID = tCustomer.ID
 LEFT JOIN tCentre
        ON ${transactiontable}.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
        ON ${transactiontable}.CustomerActivityID = tActivity.ID
 LEFT JOIN tSubledger
        ON ${transactiontable}.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tDepartment
        ON ${transactiontable}.DepartmentID = tDepartment.ID
 LEFT JOIN tLocation
        ON ${transactiontable}.LocationID = tLocation.ID
 LEFT JOIN tPerson
        ON ${transactiontable}.PersonID = tPerson.ID
WHERE
        tBatch.BillingPeriod = '$tbsbillingperiod' $SHIPTOBIT $BATCHIDBIT
        AND ${transactiontable}.RevenueFlag = 1
        AND tCustomer.ShipTo = "VRT"
        AND tBatchType.Active = 1
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
GROUP BY
 tElement.Description, tServiceID.ServiceID, tServiceID.Service,
 ${transactiontable}.BatchID,
 tBatchType.SupplierDisplayedName, tLocation.Description, 
 tPerson.Person, tCentre.Description, tServiceType.Description,
 tSubledger.Description, 
 tBatch.BillingPeriod, tCustomer.ShipTo,
 ${transactiontable}.Var01, ${transactiontable}.Var02,
 ${transactiontable}.Var03, ${transactiontable}.Var04, 
 ${transactiontable}.Var05, ${transactiontable}.Var06,
 ${transactiontable}.Var07, ${transactiontable}.Var08,
 ${transactiontable}.Var09, ${transactiontable}.Var10,
 ${transactiontable}.Var11, ${transactiontable}.Var12,
 ${transactiontable}.Var13, ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, ${transactiontable}.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
 tCentre.Description
SQLCMD1
fi
) | tee $0.out \
 | tr "," ";" | awk -F'|' \
	-v"pid=$$" \
        -v"thisDIVID=$SHIPTO" \
        -v "platinumBATCH=all" \
	-v"IYYYYMMDD=$IYYYYMMDD" \
	-v"billperiod=$billperiod" \
	-v"aperiod=$APERIOD" \
	-v"outdir=$outdir" \
	-v"outfilepref=$outfilepref" '
function trim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	return s
}

function ld_exwebconf(exwebconffile)
{
	printf("ld_exwebconf(%s)\n", exwebconffile)

	#noddsupfile = "noddsup.txt";
	#while ( (getline custline < noddsupfile) > 0 ) {
	#	split(custline, noddsupgggrid, " ");
	#	noddsuparr[noddsupgggrid[1]] = 1;
	#}
	#close(noddsupfile);

	while ( (getline exwebconfline < exwebconffile) > 0 ) {
		split(exwebconfline, exwebconfarr, ",");
		#printf("exwebconfline=%s\n", exwebconfline);
		#printf("exwebconfarr 1=%s 2=%s 3=%s 4=%s\n", exwebconfarr[1], exwebconfarr[2], exwebconfarr[3], exwebconfarr[4]);

		exwebconf_parent = trim(exwebconfarr[1])
		exwebconf_dialxx = trim(exwebconfarr[2])
		exwebconf_email = trim(exwebconfarr[3])
		exwebconf_emailyn = trim(exwebconfarr[4])
		exwebconf_status1 = trim(exwebconfarr[5])
		exwebconf_lastemailed = trim(exwebconfarr[6])
		exwebconf_lastemaileddate = trim(exwebconfarr[7])
		exwebconf_print = trim(exwebconfarr[8])
		exwebconf_cdgroup = trim(exwebconfarr[9])

		# skip retired entries
		if ( exwebconf_status1 == "R" )
			continue

		# flag parent in exwebconf
		exwebconf_parent_arr[exwebconf_parent] = 1

#		if ( platinv_parent_arr[exwebconf_parent] != 1 ) {
#			print "WARNING: parent = " exwebconf_parent " found in exwebconf, but is not in platinum invoice data"
#		}
#
#		#printf("noddsuparr[%s] = %d\n", exwebconf_parent, noddsuparr[exwebconf_parent]);
#		if ( toupper(exwebconf_dialxx) == "N" )
#			noddsuparr[exwebconf_parent] = 1;
#		else
#			noddsuparr[exwebconf_parent] = 0;
#		#tmpgggrid = exwebconf_parent;
#		#if ( noddsuparr[tmpgggrid] == 1 )
#		#	printf("     NO DD SUPPRESS\n");
#		#else
#		#	printf("     DO DD SUPPRESS\n");
#
		# store parent grouping
		exwebconf_parent_to_cdgroup_arr[exwebconf_parent] = exwebconf_cdgroup
	}
	close(exwebconffile);

}

BEGIN {
	f = 0
	fldname_arr[++f] = "Element"
	fldname_arr[++f] = "ServiceIDDesc"
	fldname_arr[++f] = "Batch"
	fldname_arr[++f] = "Supplier"
	fldname_arr[++f] = "Location"
	fldname_arr[++f] = "Person"
	fldname_arr[++f] = "Centre"
	fldname_arr[++f] = "Activity"
	fldname_arr[++f] = "Element"
	fldname_arr[++f] = "SubLedger"
	fldname_arr[++f] = "PayType"
	fldname_arr[++f] = "ServiceType"
	fldname_arr[++f] = "InvoiceDescription"
	fldname_arr[++f] = "Period"
	fldname_arr[++f] = "ShipTo"
	fldname_arr[++f] = "Invoice"
	fldname_arr[++f] = "ExGST"
	fldname_arr[++f] = "GST"
	fldname_arr[++f] = "IncGST"
	fldname_arr[++f] = "TxnGrp"
	fldname_arr[++f] = "Var01"
	fldname_arr[++f] = "Var02"
	fldname_arr[++f] = "Var03"
	fldname_arr[++f] = "Var04"
	fldname_arr[++f] = "Var05"
	fldname_arr[++f] = "Var06"
	fldname_arr[++f] = "Var07"
	fldname_arr[++f] = "Var08"
	fldname_arr[++f] = "Var09"
	fldname_arr[++f] = "Var10"
	fldname_arr[++f] = "Var11"
	fldname_arr[++f] = "Var12"
	fldname_arr[++f] = "Var13"
	fldname_arr[++f] = "Var14"
	fldname_arr[++f] = "Var15"
	fldname_arr[++f] = "BatchType"
	fldname_arr[++f] = "Group"
	fldname_arr[++f] = "Dept"
	fldname_arr[++f] = "Service"

	# IYYYYMMDD
	IYYYY = substr(IYYYYMMDD,1,4)
	IMM = substr(IYYYYMMDD,5,2)
	IDD = substr(IYYYYMMDD,7,2)

	billyyyy = substr(aperiod,1,4)
	billmm = substr(aperiod,6,2)

	# bilmax V3
	monthdir = "topdat" "/" billperiod
	targdir = monthdir "/" platinumBATCH "_" thisDIVID
	batchtag = billperiod "_" platinumBATCH "_" thisDIVID
	#logdir = monthdir "/log_" batchtag
	exwebconffile = targdir "/" "log_" billperiod "_" thisDIVID "_" platinumBATCH "/exwebconf_" batchtag ".csv"

	## bilmax V4
	#exwebconffile = "indata" "/" billperiod "/" "exwebconf.csv"

	ld_exwebconf(exwebconffile)

}


{
	#print $0
	# load record
	for ( i = 1; i <= (NF-1); ++i ) {
		fldname = fldname_arr[i]
		fldvalue = trim($i)
		fldvalue_arr[fldname] = fldvalue
	}

	ShipTo = fldvalue_arr["ShipTo"]
	Invoice = fldvalue_arr["Invoice"]

	# handle detail
	detail_ofile = outfilepref "_" ShipTo "_" billperiod "_" Invoice "_detail.csv"
        divgroup = exwebconf_parent_to_cdgroup_arr[ShipTo]
	if ( divgroup == "" )
		divgroup = ShipTo
	pardir = billperiod "/" platinumBATCH "_" thisDIVID
	divdir = pardir "/" divgroup "/" ShipTo
	dirpath = outdir "/" divdir 
	#dirpath = outdir

	ofile = dirpath "/" detail_ofile

	# create file with header
	if ( done_header[ofile] == "" ) {
		cmd = sprintf("[ ! -d \"%s\" ] && mkdir -p \"%s\"", dirpath, dirpath);
		system(cmd);
		print "ofile=[" ofile "]"
		system("rm -f \"" ofile "\"")
		f = 0
		for ( i = 1; i <= (NF-1); ++i ) {
			fldname = fldname_arr[i]
			if ( i > 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 Dept)
	#.    Activity
	#.    Element
	#.    SubLedger (tServiceID.CustomerSubLedgerID)
	#.    PayType
	#.    Sum of ExGST
	#.    Sum of GST
	#.    Sum of IncGST
	# ...
	Centre = fldvalue_arr["Centre"]
	Dept = fldvalue_arr["Dept"]
	Activity = fldvalue_arr["Activity"]
	Element = fldvalue_arr["Element"]
	SubLedger = fldvalue_arr["SubLedger"]
	PayType = fldvalue_arr["PayType"]
	ExGST = fldvalue_arr["ExGST"]
	GST = fldvalue_arr["GST"]
	IncGST = fldvalue_arr["IncGST"]
	atag = ShipTo "|" Invoice "|" Centre "|" Dept "|" Activity "|" Element "|" SubLedger "|" PayType
	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]
		Invoice = a_arr[++f]
		Centre = a_arr[++f]
		Dept = a_arr[++f]
		Activity = a_arr[++f]
		Element = a_arr[++f]
		SubLedger = a_arr[++f]
		PayType = 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 "_" Invoice "_summary.csv"
		divgroup = exwebconf_parent_to_cdgroup_arr[ShipTo]
		if ( divgroup == "" )
			divgroup = ShipTo
		pardir = billperiod "/" thisDIVID "_" platinumBATCH
		divdir = pardir "/" divgroup "/" ShipTo
		dirpath = outdir "/" divdir 
		#dirpath = outdir

		ofile = dirpath "/" summary_ofile

		# create file with header
		if ( done_header[ofile] == "" ) {
			cmd = sprintf("[ ! -d \"%s\" ] && mkdir -p \"%s\"", dirpath, dirpath);
			system(cmd);
			print "ofile=[" ofile "]"
			system("rm -f \"" ofile "\"")
			printf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n",
				"Invoice",
				"Centre",
				"Dept",
				"Activity",
				"Element",
				"SubLedger",
				"PayType",
				"ExGST",
				"GST",
				"IncGST") > ofile
			done_header[ofile] = "1"
		}
		# output record
		printf("%s,%s,%s,%s,%s,%s,%s,%0.2f,%0.2f,%0.2f\n",
			Invoice,
			Centre,
			Dept,
			Activity,
			Element,
			SubLedger,
			PayType,
			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|