: !/bin/sh

SDATE="$1"
EDATE="$2"
platinvfile="$3"
cdefile="$4"
detodir="$5"
splitby="$6"
mcsvodir="$7"

SHIPTO="all"
BATCHID="all"
INVOICENO="all"

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

MMYYYY=`SDATEtoMMYYYY $SDATE`
echo "MMYYYY = [$MMYYYY]"

billperiod=`MMYYYYtobillperiod $MMYYYY _`
echo "$MMYYYY - billperiod = [$billperiod]"

# run additional customer import csv creation
if [ -f "invcde.excl_${billperiod}" ]; then
        . invcde.excl_${billperiod}
fi

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


echo "getinvcde6:"
echo "    BATCHID = $BATCHID"
echo "  INVOICENO = $INVOICENO"
echo "    EXCLUDEINVBIT = $EXCLUDEINVBIT"
echo "    EXCLUDEBATCHIDBIT = $EXCLUDEBATCHIDBIT"

tmp1file="/tmp/gci6_$$"
tmpmcsvoutfile="/tmp/getmcsvout$$.unl"

rm -f "$platinvfile"
rm -f "$cdefile"
rm -f "/tmp/platinv$$"
rm -f "/tmp/cde$$"
rm -f "/tmp/mcsvout$$"
rm -f "$tmpmcsvoutfile"
rm -f "$tmp1file"

IOFFSETDAYS="9"

# convert EDATE to YYYYMMDD
EYYYYMMDD=`awk -v "ADATE=$EDATE" -v "sep=" '
BEGIN {
	dd = 0 + substr(ADATE,1,2);
	mm = 0 + substr(ADATE,4,2);
	yyyy = 0 + substr(ADATE,7,4);
	YYYYMMDD = sprintf("%04d%s%02d%s%02d", yyyy, sep, mm, sep, dd);
	print YYYYMMDD
	exit
}'`
echo "EYYYYMMDD=[$EYYYYMMDD]"

EYYYYMM=`echo "$EYYYYMMDD" | cut -c1-6`
echo "EYYYYMM=[$EYYYYMM]"

. tbsserver_vars

# detail files output dir
if [ -n "$detodir" ]; then
	detailodir="$detodir"
else
	##detailodir="detail_${EYYYYMMDD}_${BATCHID}"
	#detailodir="detail_${EYYYYMM}_${BATCHID}"
	detailodir="detail_${EYYYYMM}"
fi
echo "    detailodir=[$detailodir]"

##Testing
# remake detail output dir
if [ -d "${detailodir}" -a "$detailodir" != "." ]; then
	rm -r "${detailodir}"
fi
#mkdir -p "${detailodir}"


# monthly import csv output dir
mcsvoutfilepref="import"
# monthly import csv files output dir
if [ -n "$mcsvodir" ]; then
	mcsvoutdir="$mcsvodir"
else
	mcsvoutdir="mimportcsv_${EYYYYMM}"
fi

## remove target files
##rm -f "${mcsvoutdir}/${mcsvoutfilepref}_${billperiod}_summary.csv"
##rm -f "${mcsvoutdir}/${mcsvoutfilepref}_${billperiod}_detail.csv"
#rm -f "${mcsvoutdir}/${mcsvoutfilepref}.csv"
if [ "$mcsvoutdir" != "." -a -d "${mcsvoutdir}" ]; then
       rm -r "${mcsvoutdir}"
       mkdir -p "${mcsvoutdir}"
fi

if [ -z "$SHIPTO" ]; then
	SHIPTO="all"
fi

# get required bill Periods from TBS invoice tables
# based on InvoiceDate
(
sqsh -S $server -D $dbname -U $username -P $password <<SQLCMD0
SELECT DISTINCT Period
--, convert(varchar, tInvoice.InvoiceDate, 120) as Idate
--, PlatinumInvoiceNo
FROM tInvoice, tInvoiceDetail
where tInvoiceDetail.InvoiceID = tInvoice.ID
--#  and tInvoice.ID NOT IN (
--#	SELECT distinct tInvoice.CreditNoteInvID
--#		FROM tInvoice
--#		WHERE tInvoice.CreditNoteInvID is not null
--#  )
  and tInvoice.InvoiceDate > dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD')
  and tInvoice.InvoiceDate <= dateadd(mm,1,dateadd(dd,$IOFFSETDAYS,'$EYYYYMMDD'))
  $EXCLUDEINVBIT
  $EXCLUDEBATCHIDBIT
ORDER BY
 Period
\go -m bcp
SQLCMD0
) | sed 's/\|$//' > "$tmp1file"

#echo "2009/01" >"$tmp1file"

# loop for each required bill Period
cat "$tmp1file" | \
while read aperiod
do
	echo "aperiod=[$aperiod]"
	###################################
	# get platinum invoice info from TBSdata
	getinv6 $EYYYYMMDD $IOFFSETDAYS "$aperiod" "/tmp/platinv$$"
	cat "/tmp/platinv$$" >>"$platinvfile"
	rm -f "/tmp/platinv$$"

	###################################
	# get CDE data info from TBSdata
	getcde6 $EYYYYMMDD $IOFFSETDAYS "$aperiod" "$BATCHID" "/tmp/cde$$"
	cat "/tmp/cde$$" >>"$cdefile"
	rm -f "/tmp/cde$$"

	###################################
	## get detail data info from TBSdata
	getdetail6 $EYYYYMMDD $IOFFSETDAYS "$aperiod" "$SHIPTO" "$BATCHID" "$INVOICENO" "$detailodir" "$splitby"


	###################################
	# monthly customer import detail csv files  (see below - summary files)
	getmcsv6 $EYYYYMMDD $IOFFSETDAYS "$aperiod" "$SHIPTO" "$BATCHID" "$INVOICENO" "/tmp/mcsvout$$"
        cat "/tmp/mcsvout$$" >>"$tmpmcsvoutfile"
        rm -f "/tmp/mcsvout$$"

done

## Testing
#cp cde_all_test.cde cde_all.cde
#cp platinv_all_test.cde platinv_all.unl

#----------------------------------
# process mcsv unl file and create input import detail files by invoiceno

# eg. data
#Telco Rentals & Calls|1000PAS|33667|VICTRACK|PAS 570 BOURKE ST||10000|||34572||TELCO RENTAL&CALLS|VicTrack Rent and Calls APR 2010|2010/04|VLP|INV003314|0.05|0.01|0.06|CALL|15937|97|23085|out|1000PAS|VicTrack Rent and Calls|Network Services|CHIEF FINANCIAL OFFICER|

cat "$tmpmcsvoutfile"  | tr "," ";" | awk -F'|' \
	-v"pid=$$" \
        -v"thisDIVID=$SHIPTO" \
        -v "platinumBATCH=all" \
	-v"IYYYYMMDD=$EYYYYMMDD" \
	-v"billperiod=$billperiod" \
	-v"outdir=$mcsvoutdir" \
	-v"outfilepref=$mcsvoutfilepref" '
function trim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	return s
}

BEGIN {
	f = 0
	fldname_arr[++f] = "ElementDesc"
	fldname_arr[++f] = "ServiceID"
	fldname_arr[++f] = "Batch"
	fldname_arr[++f] = "Supplier"
	fldname_arr[++f] = "Location"
	fldname_arr[++f] = "Person"
	fldname_arr[++f] = "Centre"
	fldname_arr[++f] = "Activity"
	fldname_arr[++f] = "Element1Desc"
	fldname_arr[++f] = "SubLedger"
	fldname_arr[++f] = "PayType"
	fldname_arr[++f] = "ServiceType"
	fldname_arr[++f] = "InvoiceDescription"
	fldname_arr[++f] = "Period"
	fldname_arr[++f] = "ShipTo"
	fldname_arr[++f] = "Invoice"
	fldname_arr[++f] = "ExGST"
	fldname_arr[++f] = "GST"
	fldname_arr[++f] = "IncGST"
	fldname_arr[++f] = "TxnGrp"
	fldname_arr[++f] = "Var01"
	fldname_arr[++f] = "Var02"
	fldname_arr[++f] = "Var03"
	fldname_arr[++f] = "Var04"
	fldname_arr[++f] = "Var05"
	fldname_arr[++f] = "Var06"
	fldname_arr[++f] = "Var07"
	fldname_arr[++f] = "Var08"
	fldname_arr[++f] = "Var09"
	fldname_arr[++f] = "Var10"
	fldname_arr[++f] = "Var11"
	fldname_arr[++f] = "Var12"
	fldname_arr[++f] = "Var13"
	fldname_arr[++f] = "Var14"
	fldname_arr[++f] = "Var15"
	fldname_arr[++f] = "BatchType"
	fldname_arr[++f] = "Group"
	fldname_arr[++f] = "Dept"
	fldname_arr[++f] = "Service"
	fldname_arr[++f] = "CentreID"

	# IYYYYMMDD
	IYYYY = substr(IYYYYMMDD,1,4)
	IMM = substr(IYYYYMMDD,5,2)
	IDD = substr(IYYYYMMDD,7,2)

}


{
	#print $0
	# load record
	for ( i = 1; i <= (NF-1); ++i ) {
		fldname = fldname_arr[i]
		fldvalue = trim($i)
		fldvalue_arr[fldname] = fldvalue
	}

	ShipTo = fldvalue_arr["ShipTo"]
	Invoice = fldvalue_arr["Invoice"]
	BatchID = fldvalue_arr["Batch"]

	# handle detail
	#detail_ofile = outfilepref "_" billperiod "_detail.csv"
	#detail_ofile = outfilepref "_" ShipTo "_" billperiod "_" Invoice "_detail.csv"
	#detail_ofile = outfilepref "_" ShipTo "_" Invoice "_" BatchID "_detail.csv"
	detail_ofile = outfilepref "_" ShipTo "_" Invoice "_detail.csv"

	#dirpath = outdir "/" billperiod
	dirpath = outdir

	ofile = dirpath "/" detail_ofile

	## create file with header
	# no header - but create dir
	if ( done_header[ofile] == "" ) {
		cmd = sprintf("[ ! -d \"%s\" ] && mkdir -p \"%s\"", dirpath, dirpath);
		system(cmd);
	#	print "ofile=[" ofile "]"
	#	system("rm -f \"" ofile "\"")
	#	f = 0
	#	for ( i = 1; i <= (NF-1); ++i ) {
	#		fldname = fldname_arr[i]
	#		if ( i > 1 ) 
	#			printf(",") >> ofile
	#		printf("%s", fldname) >> ofile
	#	}
	#	printf("\n") >> ofile
	#	close(ofile)
	#	done_header[ofile] = "1"
	}
	# output record
	for ( i = 1; i <= (NF-1); ++i ) {
		fldname = fldname_arr[i]
		fldvalue = trim($i)
		fldvalue_arr[fldname] = fldvalue
		if ( i > 1 ) 
			printf(",") >> ofile
		printf("%s", fldvalue) >> ofile
	}
	printf("\n") >> ofile
	close(ofile)
}
'

# eg. data
#Telco Rentals & Calls|1000PAS|33667|VICTRACK|PAS 570 BOURKE ST||10000|||34572||TELCO RENTAL&CALLS|VicTrack Rent and Calls APR 2010|2010/04|VLP|INV003314|0.05|0.01|0.06|CALL|15937|97|23085|out|1000PAS|VicTrack Rent and Calls|Network Services|CHIEF FINANCIAL OFFICER|


rm -f "$tmpmcsvoutfile"
rm -f "$tmp1file"
exit 0