#! /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 BATCHID env var
# $5 will override default outfile

#############################
## Testing
#IYYYYMMDD="20120630"
#IOFFSETDAYS="9"
#APERIOD="2012/06"
#BATCHID=""
#outfile="Jun_2012.cde"

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

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

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

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

if [ -n "$5" ]; then
	outfile="$5"
else
	if [ -z "$outfile" ]; then
		outfile="$0_${BATCHID}.unl"
	fi
fi

echo "$0: for $IYYYYMMDD, $IOFFSETDAYS, $APERIOD , batchid $BATCHID to file $outfile"

EYYYYMMDD="$IYYYYMMDD"

. tbsserver_vars

tbsbillingperiod="$APERIOD"
#echo "tbsbillingperiod=[$tbsbillingperiod]"

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

#transactiontable="tFinalisedTransaction"
transactiontable="tFinalisedTransaction${YYYYMM}"
#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]"


(
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
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,
    TN1.GroupID
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,
	${transactiontable}.GroupID
	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 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'))
  and tInvoice.InvoiceDate >= dateadd(mm,-1,dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD'))
  and tInvoice.InvoiceDate < dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD')
          $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,
		${transactiontable}.GroupID
 ) 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 <> "VRT"
--#    and tBatchType.Active = 1
ORDER BY tCustomer.ShipTo, tServiceID.ServiceID, tServiceType.Code, Sundry

\go -m bcp
SQLCMD0


if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD1
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,
    TN1.GroupID
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,
	${transactiontable}.GroupID
	FROM ${transactiontable}
	 INNER JOIN tBatch
		ON tBatch.ID = ${transactiontable}.BatchID
	 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 tTransactionType
		ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
	 LEFT JOIN tTransactionGroup
		ON tTransactionType.TransactionGroupID = tTransactionGroup.ID
	WHERE tBatch.BillingPeriod = '$curtbsbillingperiod' $BATCHIDBIT
	  AND ${transactiontable}.RevenueFlag = 1
          $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,
		${transactiontable}.GroupID
 ) 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 = "VRT"
--#    and tBatchType.Active = 1
ORDER BY tCustomer.ShipTo, tServiceID.ServiceID, tServiceType.Code, Sundry

\go -m bcp
SQLCMD1
fi


) | tr "," ";" >$outfile

#cat $outfile