#! /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 outfile

#############################
## Testing
#SDATE="01/04/2010"
#EDATE="30/04/2010"
#IYYYYMMDD="20100430"
##
#SDATE="01/12/2010"
#EDATE="31/12/2010"
#IYYYYMMDD="20101231"
##
#IOFFSETDAYS="9"
#APERIOD="2010/04"
##SHIPTO=""
##BATCHID="33671"
###BATCHID="33792"
##INVOICENO="INV003314"
#outfile="getmcsv6.unl"

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

if [ -n "$1" -a -z "$IYYYYMMDD" ]; 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" -a -z "$IOFFSETDAYS" ]; 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" -a -z "$APERIOD" ]; 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" -a -z "$SHIPTO" ]; 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" -a -z "$BATCHID" ]; 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" -a -z "$INVOICENO" ]; 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" -a -z "$outfile" ]; then
        outfile="$7"
else
        if [ -z "$outfile" ]; then
                outfile="mcsv_${IYYYYMM}_${INVOICENO}_${BATCHID}"
        fi
fi

echo "$0: for $IYYYYMMDD, $IOFFSETDAYS, $APERIOD, $billperiod"
echo "   batchid $BATCHID shipto $SHIPTO invoiceno $INVOICENO"
echo "   to outfile $outfile"

EYYYYMMDD="$IYYYYMMDD"

. tbsserver_vars

#--------------------------------------------------------------

(
#cat <<SQLCMD0
#cat >/dev/null <<SQLCMD0
#sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
tbssql <<SQLCMD0
SELECT
 tElement.Description AS ElementDesc,
 tServiceID.ServiceID AS ServiceID,
 tInvoiceDetail.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element1Desc,
 tSubledger.Description AS SubLedger,
 ${transactiontable}.PayType,
 tServiceType.Description AS ServiceType,
 tInvoiceDetail.Description AS InvoiceDescription,
 tInvoiceDetail.Period,
 tCustomer.ShipTo,
 tInvoice.PlatinumInvoiceNo AS Invoice,
 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,
 ${transactiontable}.Var06,
 ${transactiontable}.Var07,
 ${transactiontable}.Var08,
 ${transactiontable}.Var09,
 ${transactiontable}.Var10,
 ${transactiontable}.Var11,
 ${transactiontable}.Var12,
 ${transactiontable}.Var13,
 ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc ReportGroup,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service,
 tCentre.ID AS CentreID,
 tDepartment.ID AS DeptID,
 ${transactiontable}.GroupID

FROM
 ${transactiontable}
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = ${transactiontable}.BatchID
 LEFT JOIN tServiceID
        ON ${transactiontable}.ServiceID = tServiceID.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
 LEFT JOIN tTransactionType
	ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON ${transactiontable}.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON ${transactiontable}.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON ${transactiontable}.ServiceTypeID = tServiceType.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 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
 tElement.Description, tServiceID.ServiceID, tServiceID.Service,
 tInvoiceDetail.BatchID,
 tBatchType.SupplierDisplayedName, tLocation.Description, 
 tPerson.Person, tCentre.ID, tCentre.Description, tServiceType.Description,
 tInvoiceDetail.Description, tSubledger.Description, 
 tInvoiceDetail.Period, tCustomer.ShipTo, tInvoice.PlatinumInvoiceNo,
 tDepartment.ID,
 ${transactiontable}.GroupID,
 ${transactiontable}.Var01, ${transactiontable}.Var02,
 ${transactiontable}.Var03, ${transactiontable}.Var04, 
 ${transactiontable}.Var05, ${transactiontable}.Var06,
 ${transactiontable}.Var07, ${transactiontable}.Var08,
 ${transactiontable}.Var09, ${transactiontable}.Var10,
 ${transactiontable}.Var11, ${transactiontable}.Var12,
 ${transactiontable}.Var13, ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, ${transactiontable}.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
tInvoice.PlatinumInvoiceNo, Batch
\go -m bcp
SQLCMD0

#---------------------------
if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then
#cat <<SQLCMD1
#cat >/dev/null <<SQLCMD1
#sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD1
tbssql <<SQLCMD1
SELECT
 tElement.Description AS ElementDesc,
 tServiceID.ServiceID AS ServiceID,
 ${transactiontable}.BatchID AS Batch,
 tBatchType.SupplierDisplayedName AS Supplier,
 tLocation.Description AS Location,
 tPerson.Person,
 tCentre.Description AS Centre,
 tActivity.Description AS Activity,
 tElement_1.Description AS Element1Desc,
 tSubledger.Description AS SubLedger,
 ${transactiontable}.PayType,
 tServiceType.Description AS ServiceType,
 '' AS InvoiceDescription,
 tBatch.BillingPeriod AS Period,
 tCustomer.ShipTo,
 'VRT999999' AS Invoice,
 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,
 ${transactiontable}.Var06,
 ${transactiontable}.Var07,
 ${transactiontable}.Var08,
 ${transactiontable}.Var09,
 ${transactiontable}.Var10,
 ${transactiontable}.Var11,
 ${transactiontable}.Var12,
 ${transactiontable}.Var13,
 ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description AS BatchType,
 tReportGroup.GroupDesc ReportGroup,
 tDepartment.Description AS Dept,
 tServiceID.Service AS Service,
 tCentre.ID AS CentreID,
 tDepartment.ID AS DeptID,
 ${transactiontable}.GroupID
FROM
 ${transactiontable}
 INNER JOIN ( tBatch
	LEFT JOIN ( tBatchType
		LEFT OUTER JOIN tReportGroup
			ON tBatchType.ReportGroupID = tReportGroup.ID
		) ON tBatch.BatchTypeID = tBatchType.ID
	) ON tBatch.ID = ${transactiontable}.BatchID
 LEFT JOIN tServiceID
        ON ${transactiontable}.ServiceID = tServiceID.ID
 LEFT JOIN tTransactionType
	ON ${transactiontable}.TransactionTypeID = tTransactionType.ID
 LEFT JOIN tTransactionGroup
	ON tTransactionType.TransactionGroupID = tTransactionGroup.ID

 LEFT JOIN tElement
	ON ${transactiontable}.ElementID = tElement.ID
 LEFT JOIN tElement tElement_1
        ON ${transactiontable}.CustomerElementID = tElement_1.ID
 LEFT JOIN tServiceType
        ON ${transactiontable}.ServiceTypeID = tServiceType.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
        AND ${transactiontable}.RevenueFlag = 1
        AND tCustomer.ShipTo = "VRT"
--#        AND tBatchType.Active = 1
          $EXCLUDEBATCHIDBIT
GROUP BY
 tElement.Description, tServiceID.ServiceID, tServiceID.Service,
 ${transactiontable}.BatchID,
 tBatchType.SupplierDisplayedName, tLocation.Description, 
 tPerson.Person, tCentre.ID, tCentre.Description, tServiceType.Description,
 tSubledger.Description, 
 tBatch.BillingPeriod, tCustomer.ShipTo,
 tDepartment.ID,
 ${transactiontable}.GroupID,
 ${transactiontable}.Var01, ${transactiontable}.Var02,
 ${transactiontable}.Var03, ${transactiontable}.Var04, 
 ${transactiontable}.Var05, ${transactiontable}.Var06,
 ${transactiontable}.Var07, ${transactiontable}.Var08,
 ${transactiontable}.Var09, ${transactiontable}.Var10,
 ${transactiontable}.Var11, ${transactiontable}.Var12,
 ${transactiontable}.Var13, ${transactiontable}.Var14,
 ${transactiontable}.Var15,
 tBatchType.Description, tReportGroup.GroupDesc, tTransactionGroup.Description, 
 tDepartment.Description, ${transactiontable}.PayType,
 tActivity.Description, tElement_1.Description
ORDER BY
Batch
SQLCMD1
fi
#) | tee $0.out
) > "$outfile"

exit 0