: !/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="20120131"
#IOFFSETDAYS="9"
#APERIOD="2012/01"

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 days"
        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="gtch1.unl"
fi

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

echo "$0: for $APERIOD 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]"


#----------------------------
rm -f "$outfile"

tbssql >>"$outfile" <<SQLCMD1
SELECT distinct
    ${transactiontable}.ServiceID AS ServiceIDID, tServiceID.ServiceID,
    tCentre.ID AS CentreID, tCentre.Description AS CentreDesc,
    tDepartment.ID As DeptID, tDepartment.Description AS DeptDesc,
    tGroup.ID AS GrpID, tGroup.Description AS GrpDesc, 
    tCustomer.ID AS CustID, tCustomer.ShipTo, tCustomer.FullName,
    tCustomer.VNumber
, 1, 1, 1, 1, 1
FROM
 ${transactiontable}
 LEFT JOIN tStatus
        ON ${transactiontable}.StatusID = tStatus.ID
 LEFT JOIN tServiceID
	ON ${transactiontable}.ServiceID = tServiceID.ID
--# LEFT JOIN tCentre
--#        ON ${transactiontable}.CustomerCentreID = tCentre.ID
--# LEFT JOIN tDepartment
--#	ON ${transactiontable}.DepartmentID = tDepartment.ID
 LEFT JOIN ( tCentre
        LEFT JOIN tDepartment
                ON tCentre.DepartmentID = tDepartment.ID
        ) ON ${transactiontable}.CustomerCentreID = tCentre.ID
 LEFT JOIN tCustomer
        ON ${transactiontable}.CustomerID = tCustomer.ID
 LEFT JOIN tGroup
	ON ${transactiontable}.GroupID = tGroup.ID
 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
WHERE tBatch.BillingPeriod = '$tbsbillingperiod' $BATCHIDBIT
 and ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo <> "VRT"
--#    and tBatchType.Active = 1
          AND ${transactiontable}.RevenueFlag = 1
          AND tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD')
          AND tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$IYYYYMMDD'))
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
SQLCMD1

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

if [ "$tbsbillingperiod" = "$curtbsbillingperiod" ]; then
echo "Do VRT"
tbssql >>"$outfile" <<SQLCMD2
SELECT distinct
    ${transactiontable}.ServiceID AS ServiceIDID, tServiceID.ServiceID,
    tCentre.ID AS CentreID, tCentre.Description AS CentreDesc,
    tDepartment.ID As DeptID, tDepartment.Description AS DeptDesc,
    tGroup.ID AS GrpID, tGroup.Description AS GrpDesc, 
    tCustomer.ID AS CustID, tCustomer.ShipTo, tCustomer.FullName,
    tCustomer.VNumber
, 1, 1, 1, 1, 1
FROM
 ${transactiontable}
 LEFT JOIN tStatus
        ON ${transactiontable}.StatusID = tStatus.ID
 LEFT JOIN tServiceID
	ON ${transactiontable}.ServiceID = tServiceID.ID
--# LEFT JOIN tCentre
--#        ON ${transactiontable}.CustomerCentreID = tCentre.ID
--# LEFT JOIN tDepartment
--#	ON ${transactiontable}.DepartmentID = tDepartment.ID
 LEFT JOIN ( tCentre
	LEFT JOIN tDepartment
		ON tCentre.DepartmentID = tDepartment.ID
	  ) ON ${transactiontable}.CustomerCentreID = tCentre.ID
 LEFT JOIN tCustomer
        ON ${transactiontable}.CustomerID = tCustomer.ID
 LEFT JOIN tGroup
	ON ${transactiontable}.GroupID = tGroup.ID
 LEFT JOIN tBatch
	ON ${transactiontable}.BatchID = tBatch.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
WHERE tBatch.BillingPeriod = '$curtbsbillingperiod' $BATCHIDBIT
 and ( tStatus.Status IS NULL OR tStatus.Status NOT LIKE '%Information%' )
    and tCustomer.ShipTo = "VRT"
--#    and tBatchType.Active = 1
          AND ${transactiontable}.RevenueFlag = 1
          $EXCLUDEINVBIT
          $EXCLUDEBATCHIDBIT
SQLCMD2
fi


exit 0