#! /bin/sh
# get Invoice info from TBS
# 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 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" ]; then
	outfile="$4"
else
	outfile="${0}.unl"
fi
echo "$0: for $IYYYYMMDD, $IOFFSETDAYS, $APERIOD 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]"

(
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
SELECT DISTINCT
 tCustomer.Shipto AS Div,
 tInvoice.PlatinumFileName,
 tInvoice.PlatinumInvoiceNo,
 ${transactiontable}.BatchID AS Sundry,
 tSupplier.SupplierName AS Supplier,
 tInvoiceDetail.ElementID,
 tElement.Description,
 tInvoiceDetail.Period,
 ${transactiontable}.InvoiceDetailID,
 tInvoice.InvoiceDate,
 tInvoiceDetail.Description AS InvParticular,
 tElement.ReportGroupID,
 tInvoiceDetail.GSTAmount
FROM ${transactiontable}
INNER JOIN tBatch ON
	tBatch.ID = ${transactiontable}.BatchID
INNER JOIN tSupplier ON
	tSupplier.ID = tBatch.SupplierID
INNER JOIN tElement ON
	tElement.ID = ${transactiontable}.ElementID
INNER JOIN tInvoiceDetail ON
	tInvoiceDetail.ID = ${transactiontable}.InvoiceDetailID
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
--#		     )
INNER JOIN tCustomer ON
	tInvoice.CustomerID = tCustomer.ID
WHERE
 tInvoice.PlatinumInvoiceNo IS NOT NULL
 AND tInvoiceDetail.Period = "$tbsbillingperiod"
--# 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
ORDER BY
 Div,
 tInvoice.PlatinumFileName,
 tInvoice.PlatinumInvoiceNo,
 tInvoice.InvoiceDate,
 Sundry,
 Supplier,
 tInvoiceDetail.ElementID
\go -m bcp
SQLCMD0
) | tr "," ";" >$outfile

#cat $outfile