#! /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/07/2012"
#EDATE="31/07/2012"
#IYYYYMMDD="20120731"
#IOFFSETDAYS="9"
#APERIOD="2012/07"
##SHIPTO=""
###BATCHID="33671"
####BATCHID="33792"
##INVOICENO="INV003314"
##splitby="0"	# invoiceno/batchtype
###outdir="."
#outdir="tdetail"

#SDATE="01/11/2012"
#EDATE="30/11/2012"
#IYYYYMMDD="20121130"
#IOFFSETDAYS="9"
#APERIOD="2012/11"
#outdir="tdet"

#testing="1"
#SDATE="01/12/2015"
#EDATE="31/12/2015"
#IYYYYMMDD="20151231"
#IOFFSETDAYS="15"
#APERIOD="2015/12"
#outdir="indata/Dec_2015/tdetail"
#splitby="0"

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

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]"

EYYYYMMDD="$IYYYYMMDD"

. tbsserver_vars

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

if [ ! -d "${outdir}" ]; then
	mkdir -p "${outdir}"
fi

gdetdebugfile="gdetail6_${AYYYYMM}.unl"
if [ "$testing" = "" ]; then
	rm -f "$gdetdebugfile"
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, rectype, 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"

if [ "$testing" = "" ]; then
(
#cat <<SQLCMD0
#cat >/dev/null <<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,
 tServiceID.PayType,
 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,

${transactiontable}.CustomerCentreID,
${transactiontable}.DepartmentID,
${transactiontable}.GroupID,
${transactiontable}.CustomerID

--# 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'))
  and tInvoice.InvoiceDate >= dateadd(mm,-1,dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD'))
  and tInvoice.InvoiceDate < dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD')
--# exclude Victrack Rent and Calls
	AND tBatchType.ID <> 199
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
--#  and tBatch.ID = 33671
--#  and tServiceID.ServiceID = '0409-428164'
ORDER BY tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo, tBatch.ID,
	 tServiceID.ServiceID, ${transactiontable}.Var01,
	 tReportGroup.ID
\go -m bcp
SQLCMD0

#---------------------------
if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then
#cat <<SQLCMD1
#cat >/dev/null <<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,
 tServiceID.PayType,
 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,

${transactiontable}.CustomerCentreID,
${transactiontable}.DepartmentID,
${transactiontable}.GroupID,
${transactiontable}.CustomerID

--# 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'))
--#  and tInvoice.InvoiceDate >= dateadd(mm,-1,dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD'))
--#  and tInvoice.InvoiceDate < dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD')
--# exclude Victrack Rent and Calls
	AND tBatchType.ID <> 199
          $EXCLUDEBATCHIDBIT
--#  and tBatch.ID = 33671
--#  and tServiceID.ServiceID = '0409-428164'
ORDER BY tCustomer.ShipTo, PlatinumInvoiceNo, tBatch.ID,
	 tServiceID.ServiceID, ${transactiontable}.Var01,
	 tReportGroup.ID
\go -m bcp
SQLCMD1
fi
#) >getdetail6.out
#exit 0
#) | tr "," ";" | tee "$gdetdebugfile" | awk -F'|' \

) | tr "," ";" > "$gdetdebugfile"
fi

cat "$gdetdebugfile" | \
awk -F'|' \
	-v"pid=$$" \
	-v"CURMMYYYY=$CURMMYYYY" \
	-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] = "tServiceID_PayType"

	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"

	fldname_arr[++f] = "tTransactionTableCustomerCentreID"
	fldname_arr[++f] = "tTransactionTableDepartmentID"
	fldname_arr[++f] = "tTransactionTableGroupID"
	fldname_arr[++f] = "tTransactionTableCustomerID"

	#print "fldname_arr[38]=[" fldname_arr[38] "]"

	mmmstr["Jan"] = 1
	mmmstr["Feb"] = 2
	mmmstr["Mar"] = 3
	mmmstr["Apr"] = 4
	mmmstr["May"] = 5
	mmmstr["Jun"] = 6
	mmmstr["Jul"] = 7
	mmmstr["Aug"] = 8
	mmmstr["Sep"] = 9
	mmmstr["Oct"] = 10
	mmmstr["Nov"] = 11
	mmmstr["Dec"] = 12

	# IYYYYMMDD
	IYYYY = substr(IYYYYMMDD,1,4)
	IMM = substr(IYYYYMMDD,5,2)
	IDD = substr(IYYYYMMDD,7,2)
	EDATE = sprintf("%02d/%02d/%04d", IDD, IMM, IYYYY)

	#APERIOD="2010/04"
	billyyyy = substr(aperiod,1,4)
	billmm = substr(aperiod,6,2)

	#m11file = outdir "/" "wb4_m11_" billmm billyyyy ".in"
	#system("rm -f \"" m11file "\"")

	do_Smartbus_KBUSAGEsummary = 0
}

{
	#print $0

	# store vals
	for ( i = 1; i <= (NF-1); ++i ) {
		fldname = fldname_arr[i]
		fldvalue = $i
		fldvalue_arr[fldname] = fldvalue
	}

	batchid = fldvalue_arr["tBatch_ID"]
	batchtypeid = fldvalue_arr["tBatchType_ID"]
	suppliername = fldvalue_arr["tSupplier_SupplierName"]
	serviceid = fldvalue_arr["tServiceID_ServiceID"]
	reportgroupid = fldvalue_arr["tReportGroupID"]
	invoiceno = fldvalue_arr["tInvoice_PlatinumInvoiceNo"]
	shipto = fldvalue_arr["tCustomer_ShipTo"]
	var01 = fldvalue_arr["tTransactionTableYYYYMM_Var01"]

	centreid = fldvalue_arr["tTransactionTableCustomerCentreID"]
	departmentid = fldvalue_arr["tTransactionTableDepartmentID"]
	groupid = fldvalue_arr["tTransactionTableGroupID"]
	customerid = fldvalue_arr["tTransactionTableCustomerID"]

	split(suppliername, suppliername_arr, " ")
	carrier = toupper(suppliername_arr[1])

	if ( splitby == "" )
		splitby = "0"

	domobdet = "0"
	dodet = "0"
	dosplitby = splitby

	# to determine if its a mobile sid
	# match SID with 04..-......
	if (match(serviceid, "04[0-9][0-9]-[0-9][0-9][0-9][0-9][0-9][0-9]")) {
		#if ( batchtypeid != 141 &&	# not Victrack Call Diversion
		if ( reportgroupid != 15 &&	# not Smartbus GPRS
		     reportgroupid != 19 ) {	# not Smartbus Aggregate
			dosplitby = "-1"
			domobdet = "1"
		}
	}	

	if ( domobdet == "0" ) {
		# to detimine if we should have other
		# than mobile call detail data
		# test for Victrack call diversion (batchtypeid 141)
		#     or supliers AAPT, TELSTRA, OPTUS, VODAFONE
		#     batchtypeid == 141 ||
		if ( reportgroupid == 10 ||
		     carrier == "AAPT" ||
		     carrier == "TELSTRA" ||
		     carrier == "OPTUS" ||
		     carrier == "VODAFONE" ) {
			dosplitby = "-1"
			dodet = "1"

			# Cload IaaS
			if ( batchtypeid == 648 || batchtypeid == 761 || batchtypeid == 762 ) {
				# cload report group is 10, so the 
				# summary RENT/CALL/OTHER/ADMIN format from cde
				# will be used, and Robyn want to keep this
				# reportinglevel in the Bilamx21, including
				# service reports, but we also want detail
				# data for this from the transaction Var fields
				# (but not m11 calls format)
				dodet = 0
				# so create the report group transaction
				# detail data as well
				# split by shipto_invoice_batch_reporgroup
				dosplitby = 0
				print "RG10: cload batchtypeid = " batchtypeid
			}

		}
	}


	if ( reportgroupid == 15 ||	# Smartbus GPRS
	     reportgroupid == 19 ) {	# Smartbus Aggregate
		dosplitby = "-1"
		dodet = "1"
		do_Smartbus_KBUSAGEsummary = 1

		# reportgroupx fields...
                #"ServiceID", "tServiceID_ServiceID",
                #"Location", "tLocation_Description",
                #"Application", "tApplication_Description",
                ##"Access Charge", "tRate_Cost",
                #"TransactionGroup", "tTransactionGroup_Description",
                #"Usage (kB)", "tTransactionTableYYYYMM_Var01",
                #"Total", "tTransactionTableYYYYMM_AmountExGST"

		# must sum Var01 kb and amountexgst
		#print "getdetail6: still need to sum Smartbus Aggregate"
		# ...
		atag = ""
		#print "---------------------"
                for ( i = 1; i <= (NF-1); ++i ) {
                        fldname = fldname_arr[i]
			#print fldname " value=[" fldvalue_arr[fldname] "]"

			if ( fldname == "tBatch_ID" ||
			     #fldname == "tBatch_BillingPeriod" ||
			     fldname == "tBatchType_ID" ||
			     fldname == "tBatchType_SupplierID" ||
			     fldname == "tBatchType_Description" ||
			     fldname == "tSupplier_SupplierName" ||
			     fldname == "tServiceID_ServiceID" ||
			     fldname == "tServiceID_Service" ||
			     fldname == "tServiceType_Code" ||
			     fldname == "tServiceType_Description" ||
			     #fldname == "tInvoice_InvoiceDate" ||
			     #fldname == "tTransactionTableYYYYMM_TxnDate" ||
			     fldname == "tReportGroupID" ||
			     fldname == "tReportGroup_GroupDesc" ||
			     fldname == "tInvoice_PlatinumFileName" ||
			     fldname == "tInvoice_PlatinumInvoiceNo" ||
			     fldname == "tCustomer_ShipTo" ||
			     fldname == "tCustomer_FullName" ||
			     fldname == "tCentre_Description" ||
			     fldname == "tActivity_Description" ||
			     fldname == "tElement_ElementCode" ||
			     fldname == "tElement_Description" ||
			     fldname == "tElement_1_ElementCode" ||
			     fldname == "tElement_1_Description" ||
			     fldname == "tElement_2_ElementCode" ||
			     fldname == "tElement_2_Description" ||
			     fldname == "tSubledger_Description" ||
			     fldname == "tDepartment_Description" ||
			     fldname == "tLocation_Description" ||
			     fldname == "tPerson_Person" ||

			     fldname == "tApplication_ID" ||
			     fldname == "tApplication_Description" ||

			     fldname == "tTransactionTableCustomerCentreID" ||
			     fldname == "tTransactionTableDepartmentID" ||
			     fldname == "tTransactionTableGroupID" ||
			     fldname == "tTransactionTableCustomerID" ||

			     #fldname == "tOrigin_Description" ||
			     #fldname == "tDestination_Description" ||
			     #fldname == "tRateDescription_Description" ||
			     ##fldname == "tsate_Cost" ||

			     #fldname == "tTransactionType_Description" ||
			     #fldname == "tTransactionGroup_Description" ||
			     #fldname == "tDialledNumber_Description" ||
			     #fldname == "tTransactionTableYYYYMM_Duration" ||
			     1 == 0 ) {
				fldvalue = fldvalue_arr[fldname]
			}
			else  {
				fldvalue = ""
			}
			atag = atag fldvalue "|"
                }
		TxnGrp = fldvalue_arr["tTransactionGroup_Description"]
		if ( TxnGrp == "RENT" ) {
			UsagekB = 0 + fldvalue_arr["tTransactionTableYYYYMM_Var01"]
			AmountExGST = 0 + fldvalue_arr["tTransactionTableYYYYMM_AmountExGST"]

			Smartbus_KBUSAGE_UsagekB_total_arr[atag] += UsagekB
			Smartbus_KBUSAGE_RENT_AmountExGST_total_arr[atag] += AmountExGST
			# append usage to dialledno field for detail report
			dialno = fldvalue_arr["tDialledNumber_Description"]
			if ( dialno != "" )
				dialno = dialno " "
			dialno = dialno "Smartbus " fldvalue_arr["tTransactionTableYYYYMM_Var01"] "KB"
			fldvalue_arr["tDialledNumber_Description"] = dialno
		}
		else {
			UsagekB = 0 + fldvalue_arr["tTransactionTableYYYYMM_Var01"]
			AmountExGST = 0 + fldvalue_arr["tTransactionTableYYYYMM_AmountExGST"]

			Smartbus_KBUSAGE_UsagekB_total_arr[atag] += UsagekB
			Smartbus_KBUSAGE_EXCESS_AmountExGST_total_arr[atag] += AmountExGST
			# append usage to dialledno field for detail report
			dialno = fldvalue_arr["tDialledNumber_Description"]
			if ( dialno != "" )
				dialno = dialno " "
			dialno = dialno "Smartbus " fldvalue_arr["tTransactionTableYYYYMM_Var01"] "KB"
			fldvalue_arr["tDialledNumber_Description"] = dialno
		}

		#print "row: atag=[" atag "]" " TxnGrp=[" TxnGrp "]" " UsagekB=[" UsagekB "]" " AmountExGST=[" AmountExGST "]"
	}

	#print "serviceid=[" serviceid "]  dosplitby=" dosplitby

	if ( dosplitby == "0" ) {		# shipto/invoiceno/batchid/reportgroupid
		#ofile = outdir "/" outfilepref "_" shipto "_" batchid ".unl"
		ofile = outdir "/" outfilepref "_" shipto "_" invoiceno "_" batchid "_" reportgroupid ".unl"
	}
	if ( dosplitby == "1" ) {		# batchid/serviceid
		# cant have "/" in SID (re: filenames)
		gsub("/",".",serviceid)
		#ofile = outdir "/" outfilepref "_" batchid "_" serviceid "_" var01 ".unl"
		#ofile= outdir "/" outfilepref "_" batchid "_" serviceid ".unl"
		ofile = outdir "/" outfilepref "_" shipto "_" invoiceno "_" batchid "_" serviceid "_" reportgroupid ".unl"
	}
	if ( dosplitby == "2" ) {		# invoiceno/batchid
		ofile = outdir "/" outfilepref "_" invoiceno "_" batchid ".unl"
	}


	if ( reportgroupid == 5 ) {
		print "RG5: dosplitby=[" dosplitby "]" " ofile=[" ofile "]" " dodet=[" dodet "]" " domobdet=[" domobdet "]"
		print "rg5: " $0
	}


	if ( dosplitby >= 0 ) { 
		handle_ofile()
	}

	if ( dodet == "1" || domobdet == "1" ) {
		# create m11 mobile detail format
		spit_m11()
	}

}


function handle_ofile()
{
	if ( ofile != last_ofile ) {
		if ( last_ofile != "" )
			close(last_ofile)
		last_ofile = ofile
		if ( done_header[ofile] == "" ) {
			system("rm -f \"" ofile "\"")
			done_header[ofile] = "1"
			f = 0
			for ( i = 1; i <= (NF-1); ++i ) {
				fldname = fldname_arr[i]
				if ( i > 1 ) 
					printf(",") >> ofile
				printf("%02d_%s", i, fldname) >> ofile
			}
			printf("\n") >> ofile
		}
	}
	for ( i = 1; i <= (NF-1); ++i ) {
		fldname = fldname_arr[i]
		fldvalue = fldvalue_arr[fldname]
		if ( i > 1 ) 
			printf(",") >> ofile
		printf("%02d: %s", i, fldvalue) >> ofile
	}
	printf("\n") >> ofile
}


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

function clip(s)
{
	sub(/[ \t]*$/,"",s)
	return s
}

function fixforfname(s)
{
	gsub(/\//,"+",s)
	return s
}



function MMM_to_MM(MMM)
{
	return 0 + mmmstr[MMM]
}


function init_servicenumber( customerid, groupid, departmentid, centreid, servicenumber, sundry)
{
	servicetag = customerid "|" groupid "|" departmentid "|" centreid "|" servicenumber "|" sundry

	#print "init_mobile() servicetag=[" servicetag "]" >> "/tmp/1"
	if ( servicetag_arr[servicetag] == 1 )
		return
	servicetag_arr[servicetag] = 1

	servicetag_to_m11file_arr[servicetag] = m11file
	servicetag_to_batchtypeid_arr[servicetag] = batchtypeid
	servicetag_to_sundry_arr[servicetag] = sundry
	servicetag_to_carrier_arr[servicetag] = carrier

	# init totals
	callcounttotal_arr[servicetag] = 0

	renttotal_arr[servicetag] = 0.0
	rent_discount_arr[servicetag] = 0.0
	calltotal_arr[servicetag] = 0.0
	call_discount_arr[servicetag] = 0.0
	othertotal_arr[servicetag] = 0.0
	other_discount_arr[servicetag] = 0.0

	internet_arr[servicetag] = 0
	internet_count_arr[servicetag] = 0
	internet_kbytes_arr[servicetag] = 0
	internet_charges_arr[servicetag] = 0.0

	#has_SMSMO_CDR[servicetag] = 0
	#FSMS_cost[servicetag] = 0
}


function spit_m11()
{
	if ( domobdet == "1") {
		#m11file = outdir "/" "wb4_m11_" billmm billyyyy ".in"
		m11file = outdir "/" "wb4_m11_" CURMMYYYY ".in"
	}
	if ( dodet == "1") {
		#m11file = outdir "/" "wb4_m11_" carrier "_" batchtypeid ".in"
		m11file = outdir "/" "wb4_m11_cdr.in"
	}

	# mobile m11 input file...
#10188|0418113733|OPTUS|CDR|MOBCL M S|Sep 2010|30/08/2010|112800||Div-VoiceMail|DEPOSIT|P|11|0.050|
#10188|0418113733|OPTUS|CHARGE_DESC|CALL|Sep 2010||||Short Messaging Charges|05/08/2010 to 04/09/2010||0|1.590|
	#	mob_sundry
	#	mob_mobile 
	#	mob_carrier 
	#	mob_rectype    "CDR|CONFIG|CHARGE_DESC|CHAGE_DESC_INFO|ERROR"
	#	mob_recclass   "CALLTYPE|COSTCENTRE|NAME|RENT|CALL|OTHER|TOTAL"
	#	mob_billperiod 
	#	mob_cdate
	#	mob_ctime
	#	mob_origin
	#	mob_destination
	#	mob_dialled
	#	mob_rate
	#	mob_duration
	#	mob_cost
	#	mob_newcost

	if ( 0 && domobdet == "1" && done_mobiles_from_config == "" ) {
		# add in mobiles from config
		# to get 0 totals for mobiles without data
		# or have no detail recs
		mobconffile = "indata/" billperiod "/mobconf.unl"
		printf("getdetail6: ld_mobconf(%s)\n", mobconffile)
		while ( (getline aline < mobconffile) > 0 ) {
			split(aline, a_arr, "|")
			#printf("aline=%s\n", aline)
			f = 0
			# from getSIDMAINTconf
			recordno = trim(a_arr[++f])
			mobconf_mobile = clip(a_arr[++f])
			sidmainttype = clip(a_arr[++f])
			mobconf_sdate = clip(a_arr[++f])
			mobconf_edate = clip(a_arr[++f])
			directoryID = clip(a_arr[++f])
			grid = fixforfname(clip(a_arr[++f]))
			datatype = clip(a_arr[++f])
			conf_carrier = clip(a_arr[++f])
			carriercode = clip(a_arr[++f])
			grname1 = clip(a_arr[++f])
			grname3 = fixforfname(clip(a_arr[++f]))
			surname = clip(a_arr[++f])
			firstname = clip(a_arr[++f])
			manufacturer = clip(a_arr[++f])
			model = clip(a_arr[++f])
			servicetype = clip(a_arr[++f])
			dataplan = clip(a_arr[++f])

			conf_centreid = ""
			conf_departmentid = ""
			conf_groupid = ""
			conf_customerid = ""

			conf_batchtypeid = 0
			datasrcid = 0
			mobile = mobconf_mobile
			gsub(" ","",mobile)
			gsub("-","",mobile)

			if ( conf_carrier == "" )
				conf_carrier = "notset"

			csvstr = conf_centreid "|" conf_departmentid "|" conf_groupid "|" conf_customerid "|" conf_batchtypeid "|" datasrcid "|" mobile "|" conf_carrier "|" "CDR" "|" "X" "|" billperiod "|" EDATE "|" "000000" "|" "" "|" "" "|" "" "|" "" "|" "0" "|" "0.00"

			printf("%s\n", csvstr) >>m11file
		}
		close(mobconffile)
		done_mobiles_from_config = 1
	}


#33671|0407191094|TELSTRA|Rent|Apr_2010|CHARGE_DESC|000000|RENT|Rent||MESSAGEBANK ACCESS FEE|0|0.00|0.00|
#33671|0407191094|TELSTRA|CALLS TO SENSIS|Apr_2010|06/04/2010|125100|MELBOURNE|Mobile Originated SMS|1234|CALLS TO SENSIS|38|1.27|1.27|

	sundry = batchid

	serviceid = fldvalue_arr["tServiceID_ServiceID"]
	if ( domobdet == "1" )
		servicenumber = substr(serviceid,1,4) substr(serviceid,6,6)
	else
		servicenumber = serviceid

        customerid = fldvalue_arr["tTransactionTableCustomerID"]
        groupid = fldvalue_arr["tTransactionTableGroupID"]
        departmentid = fldvalue_arr["tTransactionTableDepartmentID"]
        centreid = fldvalue_arr["tTransactionTableCustomerCentreID"]

	init_servicenumber( customerid, groupid, departmentid, centreid, servicenumber, sundry)

	nsmscalls = 0
	internet = 0
	internetdesc = ""
	kbytes = 0

	# set in main
	#suppliername = fldvalue_arr["tSupplier_SupplierName"]
	#split(suppliername, suppliername_arr, " ")
	#carrier = toupper(suppliername_arr[1])

	TxnGrp = fldvalue_arr["tTransactionGroup_Description"]
	if ( TxnGrp == "CALL") {
		rectype = "CDR"
		recclass = fldvalue_arr["tTransactionType_Description"]
		recclass = recclass "~"
		recclass = recclass fldvalue_arr["tRateDescription_Description"]
	}
	else { # RENT/OTHER/ADMIN
		rectype = "CHARGE_DESC"
		recclass = fldvalue_arr["tTransactionGroup_Description"]
	}

	# cnv dt to "dd/mm/yyyy" and 24hr "hhmmss"
	#Jan 05 2009 04:23PM
	dt = fldvalue_arr["tTransactionTableYYYYMM_TxnDate"]
	MM = MMM_to_MM(substr(dt,1,3))
	DD = 0 + substr(dt,5,2)
	YYYY = 0 + substr(dt,8,4)
	if ( MM == 0 || DD == 0 || YYYY == 0 ) {
		MM = IMM
		DD = IDD
		YYYY = IYYYY
	}
	sdate_str = sprintf("%02d/%02d/%04d", DD, MM, YYYY)
	hh = 0 + substr(dt,13,2)
	if ( hh == 12 && toupper(substr(dt,18,2)) == "AM" )
		hh = 0
	if ( hh < 12 && toupper(substr(dt,18,2)) == "PM" )
		hh += 12
	mm = 0 + substr(dt,16,2)
	ss = 0
	btxt_stime_str = sprintf("%02s%02d%02d", hh, mm, ss)

	dursecs = int(fldvalue_arr["tTransactionTableYYYYMM_Duration"])

	# Rate Desc
	tRateDesc = fldvalue_arr["tRateDescription_Description"]
	##tRateCost = fldvalue_arr["tRate_Cost"]
	btxt_rate = tRateDesc

	tLocationDesc = fldvalue_arr["tLocation_Description"]

	tDialledNumberDesc = fldvalue_arr["tDialledNumber_Description"]

	btxt_origin = fldvalue_arr["tOrigin_Description"]
	## if blank use Location Desciption 
	#if ( btxt_origin == "" )
	#	btxt_origin = tLocationDesc
	# if blank use ServiceType Desciption
	if ( btxt_origin == "" )
		btxt_origin = fldvalue_arr["tServiceType_Description"]

	# test for internet usage call
	#TELSTRA:tRateDesc: MOBILE WAP/INTERNET SESSIONS - 0:23 110098
	if ( match(tRateDesc,"INTERNET") > 0 ) {
		internet = 1
		internetdesc = tRateDesc
		#internetdesc = internetdesc "KB"
		internetdesc = internetdesc "KB_Internet"
		match(internetdesc,/[0-9]*KB/)
		if ( RSTART > 0 )
			kbytes = 0 + substr(internetdesc,RSTART, RLENGTH - 2)
		#print "internetdesc=[" internetdesc "]  kbytes=[" kbytes "]"
		#dursecs = 0
		btxt_rate = kbytes
	}

	#VODAFONE:tDialledNumberDesc: Internet 81Kb
	if ( match(tDialledNumberDesc,"Internet") > 0 ) {
		internet = 1
		internetdesc = tDialledNumberDesc
		#sub("Kb","KB", internetdesc)
		sub("Kb","KB_Internet", internetdesc)
		tDialledNumberDesc = ""
		match(internetdesc,/[0-9]*KB/)
		if ( RSTART > 0 )
			kbytes = 0 + substr(internetdesc,RSTART, RLENGTH - 2)
		#print "internetdesc=[" internetdesc "]  kbytes=[" kbytes "]"
		#dursecs = 0
		btxt_rate = kbytes
	}

	#Smartbus:tDialledNumberDesc: Smartbus 50KB
	if ( match(tDialledNumberDesc,"Smartbus") > 0 ) {
		internetdesc = tDialledNumberDesc
		internet = 1
		#sub("KB","KB", internetdesc)
		sub("KB","KB_InternetSB", internetdesc)
		tDialledNumberDesc = ""
		match(internetdesc,/[0-9]*KB/)
		if ( RSTART > 0 )
			kbytes = 0 + substr(internetdesc,RSTART, RLENGTH - 2)
		sub("Kb$","KB_Internet", internetdesc)
		#print "internetdesc=[" internetdesc "]  kbytes=[" kbytes "]"
		#dursecs = 0
		btxt_rate = kbytes
	}

	# set dialled no
	btxt_dialled = tDialledNumberDesc
	if ( internetdesc != "" ) {
		if ( btxt_dialled != "" )
			btxt_dialled = btxt_dialled " "
		btxt_dialled = btxt_dialled internetdesc
	}

	# set destination/description info
	btxt_destination = tLocationDesc

	if (rectype == "CHARGE_DESC" ) { # CHARGE_DESC RENT, OTHER, ADMIN
		# Use RateDescription Description
		if ( btxt_destination != "" )
			btxt_destination = btxt_destination " "
		btxt_destination = btxt_destination fldvalue_arr["tRateDescription_Description"]
		if ( btxt_destination == "" ) {
			#btxt_destination = "Blank tRateDescription.Desc. using tTransactionType.Desc. [" fldvalue_arr["tTransactionType_Description"] "]"
			btxt_destination = fldvalue_arr["tTransactionType_Description"]
		}
	}
	else {			# CDR CALL
		tDestDesc = fldvalue_arr["tDestination_Description"]
		if ( tDestDesc != "" && tDestDesc != "Mobile" ) {
			if ( btxt_destination != "" )
				btxt_destination = btxt_destination " "
			btxt_destination = btxt_destination tDestDesc
		}
		# also use TransactionType Description (Calltype/ChargeDesc)
		tTransTypeDesc = fldvalue_arr["tTransactionType_Description"]
		if ( tTransTypeDesc != "" && tTransTypeDesc != "Mobile" ) {
			if ( btxt_destination != "" )
				btxt_destination = btxt_destination " "
			btxt_destination = btxt_destination tTransTypeDesc
		}
		# if still blank use TransactionGroup Desc
		if ( btxt_destination == "" )
			btxt_destination = fldvalue_arr["tTransactionGroup_Description"]

		# remove dialled number from start of destination
		#if ( btxt_dialled == btxt_destination )
		#	btxt_destination = ""
		if ( btxt_dialled != "" ) {
			#sub(btxt_dialled,"",btxt_destination)
			if ( substr(btxt_destination,1,length(btxt_dialled)) == btxt_dialled )
				btxt_destination = substr(btxt_destination,length(btxt_dialled)+1)
		}
	}

	cost = fldvalue_arr["tTransactionTableYYYYMM_AmountExGST"]

	#print ""
	#print "btxt_origin=[" btxt_origin "]"
	#print "tLocationDesc=[" tLocationDesc "]"
	#print "btxt_destination=[" btxt_destination "]"
	#print "btxt_dialled=[" btxt_dialled "]"
	#print ""

	# create totals for CHARGE_DESC / CHARGE_DESC_INFO
	if (rectype == "CHARGE_DESC" && recclass == "RENT" ) {	# RENT
		# add up rent charges for info total
                if ( cost >= 0 ) {       # cost is not negative
                        renttotal_arr[servicetag] += cost
                }
                else {                          # cost is negative
                        rent_discount_arr[servicetag] += cost
                }
	}
	if (rectype == "CHARGE_DESC" && recclass == "OTHER" ) {	# OTHER
		# add up other charges for info total
		if ( cost >= 0 ) {       # cost is not negative
			othertotal_arr[servicetag] += cost
		}
		else {                          # cost is negative
			other_discount_arr[servicetag] += cost
		}
	}
	if (rectype == "CHARGE_DESC" && recclass == "ADMIN" ) {	# ADMIN
		# add up admin charges for info total
		if ( cost >= 0 ) {       # cost is not negative
			othertotal_arr[servicetag] += cost
		}
		else {                          # cost is negative
			other_discount_arr[servicetag] += cost
		}
	}


	if (rectype == "CDR" ) {	# CALL
		#if ( nsmscalls > 0 ) {
		#	## add sms call count
		#	#callcounttotal_arr[servicetag] != nsmscalls
		#}				
		#else {
			++callcounttotal_arr[servicetag]
		#}

                if ( internet == 1 ) {
			internet_arr[servicetag] = 1
                        # total internet for mobile
                        ++internet_count_arr[servicetag]
                        internet_kbytes_arr[servicetag] += kbytes
                        internet_charges_arr[servicetag] += cost
                }
                else {
                        # call total not incl. discount must be calculated
                        if ( cost >= 0 ) {       # cost is not negative
				calltotal_arr[servicetag] += cost
                        }
                        else {                          # cost is negative
                                call_discount_arr[servicetag] += cost
                        }
		}

	}


	outrec()

}


function outrec()
{
	m11file = servicetag_to_m11file_arr[servicetag]
	batchtypeid = servicetag_to_batchtypeid_arr[servicetag]
	sundry = servicetag_to_sundry_arr[servicetag]
	carrier = servicetag_to_carrier_arr[servicetag]

	csvstr = ""

	csvstr = csvstr sprintf("%s|",customerid)
	csvstr = csvstr sprintf("%s|",groupid)
	csvstr = csvstr sprintf("%s|",departmentid)
	csvstr = csvstr sprintf("%s|",centreid)

	csvstr = csvstr sprintf("%s|",batchtypeid)
	csvstr = csvstr sprintf("%s|",sundry)
	csvstr = csvstr sprintf("%s|%s|%s|%s|%s|", servicenumber, carrier, rectype, recclass, billperiod)
	csvstr = csvstr sprintf("%s|%s|%s|%s|%s|%s|%d|%02f|", sdate_str, btxt_stime_str, btxt_origin, btxt_destination, btxt_dialled, btxt_rate, dursecs, cost)
	csvstr = csvstr sprintf("%0.2f|", cost)

	printf("%s\n", csvstr) >>m11file
}


END {
	# output kB Usage summary (reportgroup 15 & 19)
	if ( do_Smartbus_KBUSAGEsummary ) {
                for ( atag in Smartbus_KBUSAGE_UsagekB_total_arr ) {
			split(atag, atag_arr, "|")
			for ( i = 1; i <= (NF-1); ++i ) {
				fldname = fldname_arr[i]
				fldvalue = atag_arr[i]
				fldvalue_arr[fldname] = fldvalue
			}
			UsagekB = Smartbus_KBUSAGE_UsagekB_total_arr[atag]
			RENT_AmountExGST = Smartbus_KBUSAGE_RENT_AmountExGST_total_arr[atag]
			EXCESS_AmountExGST = Smartbus_KBUSAGE_EXCESS_AmountExGST_total_arr[atag]
			AmountExGST = RENT_AmountExGST + EXCESS_AmountExGST

			fldvalue_arr["tTransactionTableYYYYMM_Var01"] =  UsagekB
			fldvalue_arr["tTransactionTableYYYYMM_Var13"] = RENT_AmountExGST
			fldvalue_arr["tTransactionTableYYYYMM_Var15"] = EXCESS_AmountExGST
			fldvalue_arr["tTransactionTableYYYYMM_AmountExGST"] = AmountExGST

			#print "TOTAL: atag=[" atag "]" " UsagekB=[" UsagekB "]" " RENT_AmountExGST=[" RENT_AmountExGST "]" " EXCESS_AmountExGST=[" EXCESS_AmountExGST "]" " AmountExGST=[" AmountExGST "]"

			batchid = fldvalue_arr["tBatch_ID"]
			batchtypeid = fldvalue_arr["tBatchType_ID"]
			suppliername = fldvalue_arr["tSupplier_SupplierName"]
			serviceid = fldvalue_arr["tServiceID_ServiceID"]
			reportgroupid = fldvalue_arr["tReportGroupID"]
			invoiceno = fldvalue_arr["tInvoice_PlatinumInvoiceNo"]
			shipto = fldvalue_arr["tCustomer_ShipTo"]

			centreid = fldvalue_arr["tTransactionTableCustomerCentreID"]
			departmentid = fldvalue_arr["tTransactionTableDepartmentID"]
			groupid = fldvalue_arr["tTransactionTableGroupID"]
			customerid = fldvalue_arr["tTransactionTableCustomerID"]

			split(suppliername, suppliername_arr, " ")
			carrier = toupper(suppliername_arr[1])

			ofile = outdir "/" outfilepref "_" shipto "_" invoiceno "_" batchid "_" reportgroupid ".unl"
			#print "ofile=" ofile

			handle_ofile()
		}
	}


	#-------------------------------------------------------------
	# CHARGE_DESC / CHARGE_DESC_INFO
	for ( servicetag in servicetag_arr ) {
		finish_mobile(servicetag)
	}

}



function finish_mobile(servicetag)
{
	#print "finish_mobile(" servicetag ")" >> "/tmp/1"
	split(servicetag, stag_arr, "|")
	customerid = stag_arr[1]
	groupid = stag_arr[2]
	departmentid = stag_arr[3]
	centreid = stag_arr[4]
	servicenumber = stag_arr[5]

	if ( servicenumber == "" )
		return

	#-------------
	# output CHARGE_DESC call total

	rectype = "CHARGE_DESC"
	recclass = "CALL"

	callcounttotal = callcounttotal_arr[servicetag]
	calltotal = calltotal_arr[servicetag]

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = "CDR"     # mark this so recalc module can skip
	btxt_destination = "Call Charges (not incl. discount/internet)"
	btxt_dialled = total_from_to
	btxt_rate = ""
	# get from CDR totals
	dursecs = callcounttotal        # total call count
	cost = calltotal

	# call total
	if ( cost != 0 ) {
		outrec()
	}


	#------------
	# call discount

	rectype = "CHARGE_DESC"
	recclass = "CALL"

	call_discount = call_discount_arr[servicetag]

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = "CDR"
	btxt_destination = "Call Discounts"
	btxt_dialled = total_from_to
	btxt_rate = ""
	dursecs = 0
	cost = 0.0 + call_discount
	# call discount
	if ( cost != 0 ) {
		outrec()
	}


	#------------
	# internet CALL/DATA charges
	if ( internet_arr[servicetag] == 1 ) {
		# output internet access
		rectype = "CHARGE_DESC"
		recclass = "CALL"

		sdate_str = ""
		btxt_stime_str = ""
		btxt_origin = "INTERNET" # mark this so recalc module can skip
		btxt_destination = "Internet Data " internet_kbytes_arr[servicetag] "Kb"
		btxt_dialled = total_from_to
		btxt_rate = internet_kbytes_arr[servicetag]
		dursecs = internet_count_arr[servicetag]     # total count
		cost = 0.0 + internet_charges_arr[servicetag]
		outrec()
	}

	#-------------
	# CALL_TOTAL (not including discounts)
	rectype = "CHARGE_DESC_INFO"
	recclass = "CALL_TOTAL"

	callcounttotal = callcounttotal_arr[servicetag]
	calltotal = calltotal_arr[servicetag]

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = ""
	btxt_destination = "Call Charges (not incl. discount/internet)"
	btxt_dialled = total_from_to
	btxt_rate = ""
	# get from CDR totals
	dursecs = callcounttotal        # total call count
	cost = calltotal

	# call total
	if ( cost != 0 ) {
		outrec()
	}


	####################################
	# call discount

	rectype = "CHARGE_DESC_INFO"
	recclass = "CALL_DISCOUNT"

	call_discount = call_discount_arr[servicetag]

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = ""
	btxt_destination = "Call Usage Discount"
	btxt_dialled = total_from_to
	btxt_rate = ""
	dursecs = 0
	cost = 0.0 + call_discount
	# call discount
	if ( cost != 0 ) {
		outrec()
	}


	####################################
	# output recurring discount
	rectype = "CHARGE_DESC_INFO"
	recclass = "RECURRING_DISCOUNT"

	recurring_discount = rent_discount_arr[servicetag]

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = ""
	btxt_destination = "Recurring Discount (not incl. call discount)"
	btxt_dialled = total_from_to
	btxt_rate = ""
	dursecs = 0
	cost = 0.0 + recurring_discount
	# recurring_discount
	if ( cost != 0 ) {
		outrec()
	}


	####################################
	# calculate and output adjustment
	rectype = "CHARGE_DESC_INFO"
	recclass = "ADJUSTMENT"

	call_discount = call_discount_arr[servicetag]

	# rjs 26/3/2010 adjustment should not include other discounts
	#other_discount = other_discount_arr[servicetag]

	#adjustment = recurring_discount + call_discount + other_discount
	adjustment = recurring_discount + call_discount

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = ""
	btxt_destination = "Total Adjustment (incl. call discount)"
	btxt_dialled = total_from_to
	btxt_rate = ""
	dursecs = 0
	cost = 0.0 + adjustment
	# adjustment
	if ( cost != 0 ) {
		outrec()
	}


	####################################
	# calculate and output call recurring
	rectype = "CHARGE_DESC_INFO"
	recclass = "RECURRING"

	recurring = renttotal_arr[servicetag]

	sdate_str = ""
	btxt_stime_str = ""
	btxt_origin = ""
	btxt_destination = "Recurring Charges"
	btxt_dialled = total_from_to
	btxt_rate = ""
	dursecs = 0
	cost = 0.0 + recurring
	# call discount
	if ( cost != 0 ) {
		outrec()
	}


	####################################
	# output internet access
	if ( internet_arr[servicetag] == 1 ) {
		# output internet access
		rectype = "CHARGE_DESC_INFO"
		recclass = "INTERNET"

		sdate_str = ""
		btxt_stime_str = ""
		btxt_origin = ""
		btxt_destination = "Internet Data " internet_kbytes_arr[servicetag] "Kb"
		btxt_dialled = total_from_to
		btxt_rate = internet_kbytes_arr[servicetag]
		dursecs = 0
		cost = 0.0 + internet_charges_arr[servicetag]
		outrec()
	}


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

	## do carrier totals
	#bill_callcounttotal += callcounttotal_arr[servicetag]
	#bill_total_cost += renttotal_arr[servicetag]
	#	 + rent_discount_arr[sericenumber]
	#	 + calltotal_arr[servicetag]
	#	 + call_discount_arr[servicetag]
	#	 + othertotal_arr[servicetag]
	#	 + other_discount_arr[servicetag]

}
'