: !/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=""
#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

#--------------------------------------------------------------
(
#cat <<SQLCMD0
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
--#SELECT     TOP 100 PERCENT
SELECT
 tElement.Description AS Element,
 tServiceID.ServiceID AS ServiceIDDesc,
 tInvoiceDetail.BatchID AS Batch,
 tBatchType.SupplierDisplayedName,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS CustCentre,
 tActivity.Description AS CustActivity,
 tElement_1.Description AS CustElement,
 tSubledger.Description AS CustSubledger,
 ${transactiontable}.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvDetailDesc,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo,
 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,
 tBatchType.Description,
 tReportGroup.GroupDesc,
 tDepartment.Description AS DeptDesc
FROM
  tServiceType RIGHT OUTER JOIN
  tElement RIGHT OUTER JOIN
  tLocation RIGHT OUTER JOIN
  tPerson RIGHT OUTER JOIN
  tTransactionGroup RIGHT OUTER JOIN
  tTransactionType ON
  tTransactionGroup.ID = tTransactionType.TransactionGroupID RIGHT OUTER JOIN
  tElement tElement_1 RIGHT OUTER JOIN
  tInvoice WITH (NOLOCK) INNER JOIN
  tInvoiceDetail WITH (NOLOCK) ON
  tInvoice.ID = tInvoiceDetail.InvoiceID INNER JOIN
  tCustomer ON tInvoice.CustomerID = tCustomer.ID INNER JOIN
  ${transactiontable} ON
  tInvoiceDetail.ID = ${transactiontable}.InvoiceDetailID INNER JOIN
  tBatchType WITH (NOLOCK) ON
  tInvoiceDetail.BatchTypeID = tBatchType.ID ON 
  tElement_1.ID = ${transactiontable}.CustomerElementID LEFT OUTER JOIN
  tActivity ON ${transactiontable}.CustomerActivityID = tActivity.ID LEFT OUTER JOIN
  tSubledger ON ${transactiontable}.CustomerSubledgerID = tSubledger.ID LEFT OUTER JOIN
  tCentre ON ${transactiontable}.CustomerCentreID = tCentre.ID LEFT OUTER JOIN
  tDepartment ON tCentre.DepartmentID = tDepartment.ID ON 
  tTransactionType.ID = ${transactiontable}.TransactionTypeID ON tPerson.ID = ${transactiontable}.PersonID ON 
  tLocation.ID = ${transactiontable}.LocationID ON tElement.ID = ${transactiontable}.ElementID ON 
  tServiceType.ID = ${transactiontable}.ServiceTypeID LEFT OUTER JOIN
  tReportGroup ON tBatchType.ReportGroupID = tReportGroup.ID FULL OUTER JOIN
  tServiceID ON ${transactiontable}.ServiceID = tServiceID.ID
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
HAVING      (tCustomer.ShipTo = 'VNA')
ORDER BY tCentre.Description

\go -m bcp
SQLCMD0

) | awk -F '|' '
{
	print $0

	# 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

	cost = $17
	print cost
	totcost += cost
}
END {
	print "tot = " totcost
}
' >g7.out