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

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
	outfile="$0_${BATCHID}.unl"
fi

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

. 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,
    TN1.RentAmt,
    TN1.CallAmt,
    (TN1.OtherAmt + TN1.AdminAmt),
    (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt+TN1.AdminAmt) AS TotalAll,
    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
FROM
((((((((((
    SELECT
	${transactiontable}.batchID,
        ${transactiontable}.ServiceID,
	${transactiontable}.ServiceTypeID,
	tInvoice.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
	FROM ((
		${transactiontable} LEFT JOIN tTransactionType ON
	           ${transactiontable}.TransactionTypeID = tTransactionType.ID
	     )
	 LEFT JOIN tTransactionGroup ON
		 tTransactionType.TransactionGroupID = tTransactionGroup.ID
	 ) 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,
	 tBatch
	WHERE tBatch.ID = ${transactiontable}.BatchID
	  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,
		tInvoice.CustomerID,
		${transactiontable}.StatusID,
		${transactiontable}.PersonID,
		${transactiontable}.LocationID,
		${transactiontable}.CustomerCentreID,
		${transactiontable}.CustomerActivityID,
		${transactiontable}.CustomerElementID,
		${transactiontable}.CustomerSubledgerID
         ) 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
WHERE
 ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo <> "VRT"
ORDER BY tServiceID.ServiceID;

\go -m bcp
SQLCMD0


if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD1
SELECT
    tCustomer.ShipTo,
    TN1.BatchID,
    TN1.RentAmt,
    TN1.CallAmt,
    (TN1.OtherAmt + TN1.AdminAmt),
    (TN1.RentAmt + TN1.CallAmt + TN1.OtherAmt+TN1.AdminAmt) AS TotalAll,
    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
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
	FROM ((
		${transactiontable} LEFT JOIN tTransactionType ON
	           ${transactiontable}.TransactionTypeID = tTransactionType.ID
	     )
	 LEFT JOIN tTransactionGroup ON
		 tTransactionType.TransactionGroupID = tTransactionGroup.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,
	 tBatch
	WHERE tBatch.ID = ${transactiontable}.BatchID
	  AND 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
         ) 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
WHERE
 ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo = "VRT"
ORDER BY tServiceID.ServiceID

\go -m bcp
SQLCMD1
fi


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

#cat $outfile