: !/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 splitby

#############################
# Testing
SDATE="01/04/2010"
EDATE="30/04/2010"
IYYYYMMDD="20100430"
IOFFSETDAYS="9"
APERIOD="2010/04"
SHIPTO="VNA"
#BATCHID="33795"
##BATCHID="33671"
###BATCHID="33792"
#INVOICENO="INV003314"
#splitby="0"	# invoiceno/batchtype
##outdir="."
#outdir="tdetail"

#############################

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="tdetail_${APERIOD}_${BATCHID}"
		#outdir="tdetail_${IYYYYMMDD}_${BATCHID}"
		outdir="tdetail_${IYYYYMM}_${BATCHID}"
	fi
fi

if [ -n "$8" ]; then
	splitby="$8"
else
	if [ -z "$splitby" ]; then
		# split tbs detail data into files by...
		#splitby=""	# all
		splitby="0"	# shipto/batchid
		#splitby="1"	# batchid/serviceid
		#splitby="2	# invoiceno/batchid
	fi
fi


##outfilepref="$0_${AYYYYMM}_${BATCHID}.unl"
#outfilepref="$0_${AYYYYMM}"
outfilepref="tdetail"

echo "$0: for $IYYYYMMDD, $IOFFSETDAYS, $APERIOD, batchid [$BATCHID], invoiceno [$INVOICENO] to outdir [$outdir] outfilepref prefix [$outfilepref] splitby [$splitby]"

. tbsserver_vars

##if [ "$outdir" != "." ]; then
##	rm -r "${outdir}"
##	mkdir -p "${outdir}"
##fi
#
#if [ ! -d "${outdir}" ]; then
#	mkdir -p "${outdir}"
#fi

#--------------------------------------------------------------
# Bilmax21 standard internal mobile call data format
# first is sundry then folowed by..
#	printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%d|%0.2f|\n", servicenumber, carrier, callclass, billperiod, sdate_str, btxt_stime_str, btxt_origin, btxt_destination, btxt_dialled, btxt_rate, dursecs, costinclgst)
#then "%0.2f|", recost
# RENT / OTHER / (also has CALL summary)
#sdate_str = "CHARGE_DESC"
#btxt_origin = "RENT"

(
#cat <<SQLCMD0
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
SELECT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.ID,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 tInvoice.InvoiceDate,
 ${transactiontable}.TxnDate,
 tReportGroup.ID AS ReportGrpID,
 tReportGroup.GroupDesc AS ReportGrpDesc,
 tInvoice.PlatinumFileName,
 tInvoice.PlatinumInvoiceNo,
 tCustomer.ShipTo,
 tCustomer.FullName,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement.ElementCode AS EleRevServTypeCode,
 tElement.Description AS EleRevServTypeDesc,
 tElement_1.ElementCode AS CustEleCode,
 tElement_1.Description AS CustEleDesc,
 tElement_2.ElementCode AS EleAdmCode,
 tElement_2.Description AS EleAdmDesc,
 tSubledger.Description AS CustSubLedger,
 tDepartment.Description AS CustDept,
 tLocation.Description AS Location,
 tPerson.Person,
 tApplication.ID,
 tApplication.Description,

 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 tTransactionType.Description AS TransactionType,
 tTransactionGroup.Description AS TransactionGroup,
 tDialledNumber.Description AS DialledNumber,
 ${transactiontable}.Duration,

 ${transactiontable}.AmountExGST,

 tBatchType.Var01Description,
 ${transactiontable}.Var01,
 tBatchType.Var02Description,
 ${transactiontable}.Var02,
 tBatchType.Var03Description,
 ${transactiontable}.Var03,
 tBatchType.Var04Description,
 ${transactiontable}.Var04,
 tBatchType.Var05Description,
 ${transactiontable}.Var05,
 tBatchType.Var06Description,
 ${transactiontable}.Var06,
 tBatchType.Var07Description,
 ${transactiontable}.Var07,
 tBatchType.Var08Description,
 ${transactiontable}.Var08,
 tBatchType.Var09Description,
 ${transactiontable}.Var09,
 tBatchType.Var10Description,
 ${transactiontable}.Var10,
 tBatchType.Var11Description,
 ${transactiontable}.Var11,
 tBatchType.Var12Description,
 ${transactiontable}.Var12,
 tBatchType.Var13Description,
 ${transactiontable}.Var13,
 tBatchType.Var14Description,
 ${transactiontable}.Var14,
 tBatchType.Var15Description,
 ${transactiontable}.Var15

--# tServiceType.SupplierID,
--# tSupplierServiceCode.SupplierID,
--# tSupplierServiceCode.Code AS SupplierServCode,

FROM
 ${transactiontable}
 INNER JOIN (
	tBatch
	LEFT JOIN (
		tBatchType
		LEFT JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		)
		ON tBatch.BatchTypeID = tBatchType.ID
	)
	ON ${transactiontable}.BatchID = tBatch.ID
 LEFT JOIN ( tServiceID
	 LEFT JOIN tApplication
		ON tServiceID.ApplicationID = tApplication.ID
	) ON ${transactiontable}.ServiceID = tServiceID.ID
 LEFT 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 tDialledNumber
	ON ${transactiontable}.DialledNumberID = tDialledNumber.ID
 LEFT JOIN tRateDescription
	ON ${transactiontable}.RateDescriptionID = tRateDescription.ID
 LEFT JOIN tDestination
	ON ${transactiontable}.DestinationID = tDestination.ID
 LEFT JOIN tOrigin
	ON ${transactiontable}.OriginID = tOrigin.ID
 LEFT JOIN tElement
	ON ${transactiontable}.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
	ON ${transactiontable}.CustomerElementID = tElement_1.ID
 LEFT JOIN (
	tServiceType
	LEFT JOIN tSupplier
		ON tServiceType.SupplierID = tSupplier.ID
	LEFT JOIN tElement tElement_2
		ON tServiceType.RevenueElementID = tElement_2.ID
	)
	ON ${transactiontable}.ServiceTypeID = tServiceType.ID
  LEFT JOIN (
	tTransactionType
	LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
--#	LEFT JOIN tSupplierServiceCode
--#		ON tTransactionType.ID = tSupplierServiceCode.TransactionTypeID
	) ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
--# LEFT JOIN tSupplierServiceCode
--#	ON ${transactiontable}.SupplierServiceCodeID = tSupplierServiceCode.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 tSupplierServiceCode.Active <> 0

--#	and tElement.ID = tServiceType.RevenueElementID

--#	and tTransactionType.ID = tSupplierServiceCode.TransactionTypeID

--#	and tSupplier.ID = SupplierServiceCode.SupplierID

	  AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')
	  AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD'))
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
--# and tServiceID.ServiceID = "0407-326077
ORDER BY tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tBatch.ID,
	 tServiceID.ServiceID, ${transactiontable}.Var01,
	 tReportGroup.ID
\go -m bcp
SQLCMD0

#---------------------------
if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then
#cat <<SQLCMD1
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD1
SELECT
 tBatch.ID,
 tBatch.BillingPeriod,
 tBatchType.ID,
 tBatchType.SupplierID,
 tBatchType.Description AS BatchType,
 tSupplier.SupplierName,
 tServiceID.ServiceID,
 tServiceID.Service,
 tServiceType.Code AS ServiceTypeCode,
 tServiceType.Description AS ServiceType,
 "" AS InvoiceDate,
 ${transactiontable}.TxnDate,
 tReportGroup.ID AS ReportGrpID,
 tReportGroup.GroupDesc AS ReportGrpDesc,
 "" AS PlatinumFileName,
 "VRT999999" AS PlatinumInvoiceNo,
 tCustomer.ShipTo,
 tCustomer.FullName,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement.ElementCode AS EleRevServTypeCode,
 tElement.Description AS EleRevServTypeDesc,
 tElement_1.ElementCode AS CustEleCode,
 tElement_1.Description AS CustEleDesc,
 tElement_2.ElementCode AS EleAdmCode,
 tElement_2.Description AS EleAdmDesc,
 tSubledger.Description AS CustSubLedger,
 tDepartment.Description AS CustDept,
 tLocation.Description AS Location,
 tPerson.Person,
 tApplication.ID,
 tApplication.Description,

 tOrigin.Description AS Origin,
 tDestination.Description AS Destination,
 tRateDescription.Description AS RateDescription,
 tTransactionType.Description AS TransactionType,
 tTransactionGroup.Description AS TransactionGroup,
 tDialledNumber.Description AS DialledNumber,
 ${transactiontable}.Duration,

 ${transactiontable}.AmountExGST,

 tBatchType.Var01Description,
 ${transactiontable}.Var01,
 tBatchType.Var02Description,
 ${transactiontable}.Var02,
 tBatchType.Var03Description,
 ${transactiontable}.Var03,
 tBatchType.Var04Description,
 ${transactiontable}.Var04,
 tBatchType.Var05Description,
 ${transactiontable}.Var05,
 tBatchType.Var06Description,
 ${transactiontable}.Var06,
 tBatchType.Var07Description,
 ${transactiontable}.Var07,
 tBatchType.Var08Description,
 ${transactiontable}.Var08,
 tBatchType.Var09Description,
 ${transactiontable}.Var09,
 tBatchType.Var10Description,
 ${transactiontable}.Var10,
 tBatchType.Var11Description,
 ${transactiontable}.Var11,
 tBatchType.Var12Description,
 ${transactiontable}.Var12,
 tBatchType.Var13Description,
 ${transactiontable}.Var13,
 tBatchType.Var14Description,
 ${transactiontable}.Var14,
 tBatchType.Var15Description,
 ${transactiontable}.Var15

--# tServiceType.SupplierID,
--# tSupplierServiceCode.SupplierID,
--# tSupplierServiceCode.Code AS SupplierServCode,

FROM
 ${transactiontable}
 INNER JOIN (
	tBatch
	LEFT JOIN (
		tBatchType
		LEFT JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		)
		ON tBatch.BatchTypeID = tBatchType.ID
	)
	ON ${transactiontable}.BatchID = tBatch.ID
 LEFT JOIN ( tServiceID
	 LEFT JOIN tApplication
		ON tServiceID.ApplicationID = tApplication.ID
	) ON ${transactiontable}.ServiceID = tServiceID.ID
--# LEFT 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 tDialledNumber
	ON ${transactiontable}.DialledNumberID = tDialledNumber.ID
 LEFT JOIN tRateDescription
	ON ${transactiontable}.RateDescriptionID = tRateDescription.ID
 LEFT JOIN tDestination
	ON ${transactiontable}.DestinationID = tDestination.ID
 LEFT JOIN tOrigin
	ON ${transactiontable}.OriginID = tOrigin.ID
 LEFT JOIN tElement
	ON ${transactiontable}.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
	ON ${transactiontable}.CustomerElementID = tElement_1.ID
 LEFT JOIN (
	tServiceType
	LEFT JOIN tSupplier
		ON tServiceType.SupplierID = tSupplier.ID
	LEFT JOIN tElement tElement_2
		ON tServiceType.RevenueElementID = tElement_2.ID
	)
	ON ${transactiontable}.ServiceTypeID = tServiceType.ID
  LEFT JOIN (
	tTransactionType
	LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
--#	LEFT JOIN tSupplierServiceCode
--#		ON tTransactionType.ID = tSupplierServiceCode.TransactionTypeID
	) ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
--# LEFT JOIN tSupplierServiceCode
--#	ON ${transactiontable}.SupplierServiceCodeID = tSupplierServiceCode.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 tSupplierServiceCode.Active <> 0

--#	and tElement.ID = tServiceType.RevenueElementID

--#	and tTransactionType.ID = tSupplierServiceCode.TransactionTypeID

--#	and tSupplier.ID = SupplierServiceCode.SupplierID

--#--#	  AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')
--#--#	  AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD'))
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
--# and tServiceID.ServiceID = "0407-326077
ORDER BY tCustomer.ShipTo, PlatinumInvoiceNo, tBatch.ID,
	 tServiceID.ServiceID, ${transactiontable}.Var01,
	 tReportGroup.ID
\go -m bcp
SQLCMD1
fi
) | tr "," ";" | awk -F'|' \
	-v"IYYYYMMDD=$IYYYYMMDD" \
	-v"billperiod=$billperiod" \
	-v"aperiod=$APERIOD" \
	-v"splitby=$splitby" \
	-v"outfilepref=$outfilepref" \
	-v"outdir=$outdir" '
BEGIN {
	f = 0
	fldname_arr[++f] = "tBatch_ID"
	fldname_arr[++f] = "tBatch_BillingPeriod"
	fldname_arr[++f] = "tBatchType_ID"
	fldname_arr[++f] = "tBatchType_SupplierID"
	fldname_arr[++f] = "tBatchType_Description"
	fldname_arr[++f] = "tSupplier_SupplierName"
	fldname_arr[++f] = "tServiceID_ServiceID"
	fldname_arr[++f] = "tServiceID_Service"
	fldname_arr[++f] = "tServiceType_Code"
	fldname_arr[++f] = "tServiceType_Description"
	fldname_arr[++f] = "tInvoice_InvoiceDate"
	fldname_arr[++f] = "tTransactionTableYYYYMM_TxnDate"
	fldname_arr[++f] = "tReportGroupID"
	fldname_arr[++f] = "tReportGroup_GroupDesc"
	fldname_arr[++f] = "tInvoice_PlatinumFileName"
	fldname_arr[++f] = "tInvoice_PlatinumInvoiceNo"
	fldname_arr[++f] = "tCustomer_ShipTo"
	fldname_arr[++f] = "tCustomer_FullName"
	fldname_arr[++f] = "tCentre_Description"
	fldname_arr[++f] = "tActivity_Description"
	fldname_arr[++f] = "tElement_ElementCode"
	fldname_arr[++f] = "tElement_Description"
	fldname_arr[++f] = "tElement_1_ElementCode"
	fldname_arr[++f] = "tElement_1_Description"
	fldname_arr[++f] = "tElement_2_ElementCode"
	fldname_arr[++f] = "tElement_2_Description"
	fldname_arr[++f] = "tSubledger_Description"
	fldname_arr[++f] = "tDepartment_Description"
	fldname_arr[++f] = "tLocation_Description"
	fldname_arr[++f] = "tPerson_Person"

	fldname_arr[++f] = "tApplication_ID"
	fldname_arr[++f] = "tApplication_Description"

	fldname_arr[++f] = "tOrigin_Description"
	fldname_arr[++f] = "tDestination_Description"
	fldname_arr[++f] = "tRateDescription_Description"
	fldname_arr[++f] = "tTransactionType_Description"
	fldname_arr[++f] = "tTransactionGroup_Description"
	fldname_arr[++f] = "tDialledNumber_Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Duration"

	fldname_arr[++f] = "tTransactionTableYYYYMM_AmountExGST"

	fldname_arr[++f] = "tBatchType_Var01Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var01"
	fldname_arr[++f] = "tBatchType_Var02Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var02"
	fldname_arr[++f] = "tBatchType_Var03Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var03"
	fldname_arr[++f] = "tBatchType_Var04Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var04"
	fldname_arr[++f] = "tBatchType_Var05Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var05"
	fldname_arr[++f] = "tBatchType_Var06Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var06"
	fldname_arr[++f] = "tBatchType_Var07Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var07"
	fldname_arr[++f] = "tBatchType_Var08Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var08"
	fldname_arr[++f] = "tBatchType_Var09Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var09"
	fldname_arr[++f] = "tBatchType_Var10Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var10"
	fldname_arr[++f] = "tBatchType_Var11Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var11"
	fldname_arr[++f] = "tBatchType_Var12Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var12"
	fldname_arr[++f] = "tBatchType_Var13Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var13"
	fldname_arr[++f] = "tBatchType_Var14Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var14"
	fldname_arr[++f] = "tBatchType_Var15Description"
	fldname_arr[++f] = "tTransactionTableYYYYMM_Var15"

}

{
	#print $0
	print "NF=" NF
	exit

	# store data by field name
        for ( i = 1; i <= (NF-1); ++i ) {
                fldvalue_arr[fldname_arr[i]] = $i
        }

	bcost = fldvalue_arr["tTransactionTableYYYYMM_AmountExGST"]

	# detail 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

	# summary group by...
	#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

	Invoice = fldvalue_arr["tInvoice_PlatinumInvoiceNo"]
	CustomerCentre = fldvalue_arr["tCentre_Description"]
	DepartmentDesc = fldvalue_arr["tDepartment_Description"]
	SubLedger = fldvalue_arr["tSubledger_Description"]

	if ( Subledegr == "" )
		SubLedger = "blank"

	atag = Invoice "|" CustomerCentre "|" DepartmentDesc "|" SubLedger

	summary_total[atag] += cost

	print atag " =  " cost
	totcost += cost
}
END {
	print "tot = " totcost
}
' >g6.out