#! /bin/sh

SHIPTO="NAT"
BATCHID="39358"
SDATE="01/06/2012"
EDATE="30/06/2012"

IOFFSETDAYS="9"

#-----------------------------------
echo "IOFFSETDAYS=[$IOFFSETDAYS]"

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

MMYYYY=`SDATEtoMMYYYY $SDATE`
echo "MMYYYY=[$MMYYYY]"

billperiod=`MMYYYYtobillperiod $MMYYYY "_"`
echo "billperiod=[$billperiod]"

tbsbillingperiod=`MMYYYYtotbsbillperiod $MMYYYY`
echo "tbsbillingperiod=[$tbsbillingperiod]"

YYYYMM=`echo "$tbsbillingperiod" | sed 's/\///'`
echo "YYYYMM=[$YYYYMM]"

transactiontable="tFinalisedTransaction${YYYYMM}"
echo "transactiontable=[$transactiontable]"

if [ -n "$BATCHID" ]; then
        BATCHIDBIT=" and ${transactiontable}.BatchID = $BATCHID"
else
        BATCHIDBIT=""
fi


#cat <<SQL0
tbssql <<SQL0
SELECT
    tCustomer.ShipTo,
    TN1.BatchID AS Sundry,
    TN1.RentAmt,
    TN1.CallAmt,
    TN1.OtherAmt,
    TN1.AdminAmt,
    (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt + TN1.AdminAmt) AS TotalAll,
    tBatchType.ID AS BatchTypeID,
    tBatchType.Description AS BatchTypeDesc,
    tBatchType.ReportGroupID,
    tReportGroup.GroupDesc AS ReportGroupDesc,
    tReportGroup.ReportOrder,
    tServiceType.Code,
    tServiceID.ServiceID AS ServiceName,
    tLocation.Description AS Location,
    tPerson.Person, 
    tCentre.ID, tCentre.Description AS Centre,
    tActivity.Description AS Activity,
    tElement.ElementCode AS Element,
    tSubledger.Description AS Subledger,
    tServiceID.PayType,
    tDepartment.ID, tDepartment.Description AS Department, 
    tServiceID.Description AS ServiceDesc,
    tServiceID.InstallationDate,
    tStatus.Status,
    tBatch.BillingPeriod,
    TN1.InvoiceDetailID
FROM
 (SELECT
	${transactiontable}.batchID,
        ${transactiontable}.ServiceID,
	${transactiontable}.ServiceTypeID,
	${transactiontable}.CustomerID,
	${transactiontable}.StatusID,
	${transactiontable}.PersonID,
	${transactiontable}.LocationID,
	${transactiontable}.CustomerCentreID,
	${transactiontable}.CustomerActivityID,
	${transactiontable}.CustomerElementID,
	${transactiontable}.CustomerSubledgerID,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'RENT' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS RentAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'CALL' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS CallAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'OTHER' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS OtherAmt,
	Sum(CASE tTransactionGroup.Description
	    WHEN 'ADMIN' THEN
		${transactiontable}.AmountExGST ELSE 0 END
	   ) AS AdminAmt,
	${transactiontable}.InvoiceDetailID
	FROM ${transactiontable}
	 INNER JOIN tBatch
		ON tBatch.ID = ${transactiontable}.BatchID
	 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
	WHERE (1=1)
--# and tBatch.BillingPeriod = '$tbsbillingperiod'
 $BATCHIDBIT
	  AND ${transactiontable}.RevenueFlag = 1
	  AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')
	  AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD'))
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
	GROUP BY
		${transactiontable}.ServiceID,
		${transactiontable}.BatchID,
		${transactiontable}.ServiceTypeID,
		${transactiontable}.CustomerID,
		${transactiontable}.StatusID,
		${transactiontable}.PersonID,
		${transactiontable}.LocationID,
		${transactiontable}.CustomerCentreID,
		${transactiontable}.CustomerActivityID,
		${transactiontable}.CustomerElementID,
		${transactiontable}.CustomerSubledgerID,
		${transactiontable}.InvoiceDetailID
 ) AS TN1
 LEFT JOIN tStatus
	ON TN1.StatusID = tStatus.ID
 LEFT JOIN tCustomer
	ON TN1.CustomerID = tCustomer.ID
 LEFT JOIN tPerson
	ON TN1.PersonID = tPerson.ID
 LEFT JOIN tLocation
	ON TN1.LocationID = tLocation.ID
 LEFT JOIN ( tCentre
	LEFT JOIN tDepartment
		ON tCentre.DepartmentID = tDepartment.ID
	) ON TN1.CustomerCentreID = tCentre.ID
 LEFT JOIN tActivity
	ON TN1.CustomerActivityID = tActivity.ID
 LEFT JOIN tElement
	ON TN1.CustomerElementID = tElement.ID
 LEFT JOIN tSubledger
	ON TN1.CustomerSubledgerID = tSubledger.ID
 LEFT JOIN tServiceID
	ON TN1.ServiceID = tServiceID.ID
 LEFT JOIN tServiceType
	ON TN1.ServiceTypeID = tServiceType.ID
 LEFT JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON TN1.BatchID = tBatch.ID
WHERE
 ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo = "$SHIPTO"
--#    and tBatchType.Active = 1
ORDER BY tCustomer.ShipTo, tServiceID.ServiceID, tServiceType.Code, Sundry
SQL0