#! /bin/sh
# vmchargeback

# vars from batch script
#export progname
#export SDATE EDATE STIME ETIME
#export SITEID
#export PDFPASSWORD CUSTPARAM DEFCUSTPARAM
#export BATCHID T21BATCHVAR1VALU
#export SHIPTO T21BATCHVAR2VALU
#export RUNTDATASQL T21BATCHVAR3VALU

#---------------------------------------
## Testing
#USETBSTEST="1"; export USETBSTEST

#----------------------------------------
## Testing

#SDATE="01/10/2015"
#EDATE="31/10/2015"
##billperiod="Oct_2015"
#SITEID="ALL"
#PDFPASSWORD="19i5zbG9P6PBpBWsB8FmKgOweJxTxPejUDb4GLWV"
#BATCHID="47547"
#SHIPTO="VBA"

#SDATE="01/11/2015"
#EDATE="30/11/2015"
##billperiod="Nov_2015"
#SITEID="ALL"
#PDFPASSWORD="5b0uOh+woVmINUoa5tL2OwKHDMPNvT+gNIO9arbk"
#BATCHID="47520"
#SHIPTO="VBA"

#SDATE="01/12/2015"
#EDATE="31/12/2015"
##billperiod="Dec_2015"
#SITEID="ALL"
##BATCHID="47703"; SHIPTO="VBA"
##PDFPASSWORD="G53YpGwvLfKRaz+ZbLJ3QvKlCj3Dq+U+jI8GRqx"
##BATCHID="47718"; SHIPTO="CIT"
##PDFPASSWORD="yn7FPUhkIVviRDAWm_slash_vnHWEOeoJNoY7l7nbqmCa0"
#BATCHID="47719"; SHIPTO="VRT"
#PDFPASSWORD="7qM7DF2rLX9q+0llQl6AoVucnWmiS_slash_s58wUPwsl_slash_"

#SDATE="01/02/2016"
#EDATE="29/02/2016"
#SITEID="ALL"
#BATCHID="48058"; SHIPTO="VRT"
#PDFPASSWORD="puD/qHZd5jWqWT9UWFQdnfhqy7mftus0S/mmIgAH"

#SDATE="01/11/2017"
#EDATE="30/11/2017"
#SITEID="ALL"
#BATCHID="52090"; SHIPTO="VBA"
#PDFPASSWORD="TLaw64FE3EdlYlrEL138FQ18RMcXuvd19+dP/vIT"



## Testing
#RUNTDATASQL="0"
#EMAILOVERRIDE="rod@transmit.com.au"


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

#INCLUDEATTACHMENTS="1"
INCLUDEATTACHMENTS="0"


#-------

#indata/Oct_2015/VMCHARGEBACK
#-rwxr--r--  1 robynd   catcom 2588474 Nov  1 08:22 D8843 - VBA PAYG Monthly Cost Report.12329.pdf
#-rwxr--r--  1 robynd   catcom   38400 Nov  4 11:06 Information to open the Chargeback report D8843-VBA.msg
#drwxrwxrwx 13 bilmax21 catcom    4096 Dec  7 10:59 ..
#drwxr-xr-x  2 robynd   catcom    4096 Dec  7 11:01 .
#
#indata/Nov_2015/VMCHARGEBACK
#-rwxr--r--  1 robynd   catcom 301402 Dec  1 08:22 D8843 - VBA PAYG Monthly Cost Report.12498.pdf
#-rwxr--r--  1 robynd   catcom  40448 Dec  2 09:30 Information to open the Chargeback report D8843 - VBA.msg
#drwxrwxrwx 10 bilmax21 catcom   4096 Dec  7 11:00 ..
#drwxr-xr-x  2 robynd   catcom   4096 Dec  7 11:01 .

#/u/catcom/wb/ex4/indata/Dec_2015/VMCHARGEBACK/VRT
#-rwxr--r-- 1 robynd   catcom 258352 Jan 15 18:00 7qM7DF2rLX9q+0llQl6AoVucnWmiS_slash_s58wUPwsl_slash__D9038 - Regional PID's PAYG Monthly Cost Report.13964.pdf

## these files should be placed in indata/VMCHARGEBACK/SHIPTO/passwd filename.pdf

## Testing
##billperiod="Aug_2015"

#infile="./D8843_-_VBA_PAYG_Monthly_Cost_Report_10229.pdf"

#billperiod="Oct_2015"
##orginfile="../indata/${billperiod}/VMCHARGEBACK/D8843 - VBA PAYG Monthly Cost Report.12329.pdf"
#orginfile="../indata/VMCHARGEBACK/VBA/19i5zbG9P6PBpBWsB8FmKgOweJxTxPejUDb4GLWV D8843 - VBA PAYG Monthly Cost Report.12329.pdf"

#billperiod="Nov_2015"
##orginfile="../indata/${billperiod}/VMCHARGEBACK/D8843 - VBA PAYG Monthly Cost Report.12498.pdf"
#orginfile="../indata/VMCHARGEBACK/VBA/5b0uOh+woVmINUoa5tL2OwKHDMPNvT+gNIO9arbk D8843 - VBA PAYG Monthly Cost Report.12498.pdf"

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


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

lastdayinmonth()        # Returns the last day in month, Inputs: $1=mm $2=yyyy
{
        cal $1 $2 | awk '{ if ( NF > 0 ) x=$NF } END { print x }'
}

#################################################################
# MAIN

tmpfile=/tmp/vmchargeback_$$
tmp1file=/tmp/vmchargeback_1_$$

if [ -z "$progname" ]; then
	progname="$0"
fi

if [ -z "$SITEID" ]; then
        echo "Enter   siteid (Enter for ALL):\c"; read SITEID
fi
if [ -z "$SITEID" ]; then
        SITEID="ALL"
fi
if [ -z "$SDATE" ]; then
        echo "Enter start date:\c"; read SDATE
fi
if [ -z "$EDATE" ]; then
        echo "Enter   end date:\c"; read EDATE
fi
#if [ -z "$STIME" ]; then
#        echo "Enter start time:\c"; read STIME
#fi
#if [ -z "$ETIME" ]; then
#        echo "Enter   end time:\c"; read ETIME
#fi

if [ -z "$PDFPASSWORD" ]; then
        echo "Enter   PDF Password:\c"; read PDFPASSWORD
fi

if [ -z "$BATCHID" ]; then
        echo "Enter   BatchID:\c"; read BATCHID
fi

if [ -z "$SHIPTO" ]; then
        echo "Enter   ShipTo:\c"; read SHIPTO
fi

if [ -z "$RUNTDATASQL" ]; then
        echo "Enter   RUNTDATASQL (0/1):\c"; read RUNTDATASQL
fi

#------------------------------------------
if [ "$SITEID" = "ALL" ]; then
        SITEBIT=""
else
        SITEBIT="and siteid = $SITEID"
fi

###########################################################
###########################################################
# send file to user
exec 1>$tmpfile 2>&1

#-------------------------------------------------------
echo "progname=[$progname]"
echo "SDATE=[$SDATE] EDATE=[$EDATE]"
echo "SITEID=[$SITEID]"
echo "PDFPASSWORD=[$PDFPASSWORD]"
echo "BATCHID=[$BATCHID]"
echo "SHIPTO=[$SHIPTO]"
echo "RUNTDATASQL=[$RUNTDATASQL]"
echo ""

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

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

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

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

# 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]"

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

IYYYYMMDD="$EYYYYMMDD"
CURMMYYYY=`awk -v"EYYYYMMDD=$IYYYYMMDD" 'BEGIN { print substr(EYYYYMMDD,5,2) substr(EYYYYMMDD,1,4) }'`
echo "CURMMYYYY=[$CURMMYYYY]"
tbsbillingperiod=`../MMYYYYtotbsbillperiod $CURMMYYYY /`
echo "tbsbillingperiod=[$tbsbillingperiod]"

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

IYYYYMM=`awk -v"IYYYYMMDD=$IYYYYMMDD" 'BEGIN { print substr(IYYYYMMDD,1,6); exit 0}'`
echo "IYYYYMM = [$IYYYYMM]"

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]"

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

YYYYMM=`echo "$tbsbillingperiod" | sed 's/\///'`
echo "YYYYMM=[$YYYYMM]"

##transactiontable="tFinalisedTransaction"
#transactiontable="tFinalisedTransaction${YYYYMM}"
#echo "transactiontable=[$transactiontable]"

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

proc_chargeback_pdf()
{
billperiod="$1"
orginfile="$2"
passwd="$3"

echo "process orginfile=[$orginfile]"

basefname=`basename "$orginfile"`
echo "        basefname=[$basefname]"

shipto=`dirname "$orginfile" | awk '{x = split($0,a_arr,"/"); print a_arr[x]}'`
echo "      shipto=[$shipto]"

if [ -z "$passwd" ]; then
	echo "get passwd from filename"
	pdfpassword=`echo "$basefname" | cut -d ' ' -f1`
else
	pdfpassword="$passwd"
fi
# replash _slash_ with /
pdfpassword=`echo "$pdfpassword" | sed 's/_slash_/\//g'`
echo "      pdfpassword=[$pdfpassword]"

fixedfname=`echo "$basefname" | tr " " "_"`
echo "      fixedfname=[$fixedfname]"

indatamonthfolder="../indata/${billperiod}/VMCHARGEBACK/${shipto}"
echo "      indatamonthfolder=[$indatamonthfolder]"
mkdir -p "$indatamonthfolder"
rtrun chmod 777 "$indatamonthfolder"

outdir="${indatamonthfolder}/out"
mkdir -p "$outdir"

procfile="${outdir}/${fixedfname}"
echo "      procfile=[$procfile]"
##mv "$orginfile" "$procfile"
orgdirname=`dirname "$orginfile"`
echo "      orgdirname=[$orgdirname]"
cp "$orginfile" "$procfile"

cnvcblogfile="${outdir}/cnvchargeback_${billperiod}.log"
importerrorsfile="${outdir}/cnvchargeback_${billperiod}.errs"
detcsvfile="${outdir}/cnvchargeback.det_${billperiod}.csv"
sumcsvfile="${outdir}/cnvchargeback.sum_${billperiod}.csv"
sumhtmlfile="${outdir}/cnvchargeback.sum_${billperiod}.html"
uniqserviceidfile="${outdir}/cnvchargeback.servicied.uniq_${billperiod}.csv"

ldvmcblogfile="${outdir}/ldvmcb3_${billperiod}.log"
sqlfile="${outdir}/ldvmcb3_${billperiod}.sql"

cp /dev/null "${cnvcblogfile}"
cp /dev/null "$importerrorsfile"
cp /dev/null "${procfile}.txt"
cp /dev/null "${detcsvfile}"
cp /dev/null "${sumcsvfile}"
#cp /dev/null "${sumhtmlfile}"
cp /dev/null "${uniqserviceidfile}"

cp /dev/null "${ldvmcblogfile}"
cp /dev/null "${sqlfile}"


# header for detail csv
echo "ParentEntity,ServiceID,StartDate,StartTime,,EndTime,Resouvce,BaseRate,RateFactor,UsedUnits,Duration,TotalCharge" > "$detcsvfile"



pdftotext -upw "$pdfpassword" -layout -nopgbrk  "${procfile}" "${procfile}.txt"


awk -F',' \
  -v"billperiod=$billperiod" \
  -v"importerrorsfile=$importerrorsfile" \
  -v"detcsvfile=$detcsvfile" \
  -v"sumcsvfile=$sumcsvfile" \
  -v"sumhtmlfile=$sumhtmlfile" \
  -v"uniqserviceidfile=$uniqserviceidfile" \
'
function trim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	gsub(/\,/,";",s)
	return s
}

function clip(s)
{
	sub(/[ \t]*$/,"",s)
	gsub(/\,/,";",s)
	return s
}

function rmc(s)
{
	gsub(/\,/,"",s)
	return s
}

{
	#print $0

	# skip blank lines
	if ( NF == 0 )
		next

	aline = $0
	print "aline: [" aline "]"
	split(aline,aarr, " ")

	# skip page break and headers
	if ( aarr[1] == "Page:" ) {
		print "skip page break and header - 0"
		# read next page header
		getline
		next
	}

	#"      Chargeback Cost Summary"
	if ( aarr[1] == "Chargeback" && 
	     aarr[2] == "Cost" &&
	     aarr[3] == "Summary") {

		procsum = 1
		sumlinecnt = 0
		sumentitycnt = 0
		summaryid = ""
		sumitem = ""
		sumentity = ""
		offset = 0
		curparent = ""
		parentpath = ""
		lastsumentity = ""
		lastoffset = 0
		while ( procsum ) {
			getline
			if ( NF == 0 )	# skip blank lne
				continue
			++sumlinecnt
			sumbline = $0
			print "sumbline: [" sumbline "]"
			split(sumbline,sumbarr, " ")

			# skip page break and headers
			if ( sumbarr[1] == "Page:" ) {
				print "skip page break and header - 1"
				# read next page header
				getline
				continue
			}

			#" Entity                                                                                Total Charge (AUD)"
			if ( sumbarr[1] == "Entity" && 
			     sumbarr[2] == "Total" &&
			     sumbarr[3] == "Charge") {
				if ( procsum == 1 ) {
					print "start Entity Summary section"
					procsum = 2
				}
				continue
			}

			#"Usage Units"
			if ( sumbarr[1] == "Usage" && 
			     sumbarr[2] == "Units") {
				if ( procsum == 2 ) {
					# very last is always bottom entity

					lastsumentity = sumentity

					# do_last_sumentity
					print "very last [" lastsumentity "] is bottom entity"

					print "end Entity Summary section"
					#procsum = 3
					procsum = 0
				}
				continue
			}

			# in Summary Entity section
			if ( procsum == 2 ) {
				sumitem = sumbline
				#print "sumitem: [" sumitem "]"

				cnt = split(sumitem,sumarr, " ")
				lastsumentity = sumentity
				sumentity1stword = sumarr[1]
				#sumentity = sumarr[1]
				# join all words except last tot cost one
				sumentityline = ""
				for (i=1; i < cnt; ++i) {
					if ( sumarr[i] == "-" )
						break
					sumentityline = sumentityline sumarr[i]
				}
				sumentity = toupper(sumentityline)
				total_cost = sumarr[cnt]
				gsub(/,/,"",total_cost)
				print "total_cost=[" total_cost "]"	


				# first summary item
				if ( ++sumentitycnt == 1 ) {

					print ""
					print "item after 1st \"Entity.....    Total Charge\""
					
					summaryid = sumentity
					print "summaryid=[" summaryid "]"	
					grandtotal_cost = total_cost
					print "grandtotal_cost=[" grandtotal_cost "]"	
					print "============================================="
				}

				# handle summary Entity hierarchy
				print "Summary Item: ["  sumitem "]"
				print "   sumentity: ["  sumentity "]"
				lastoffset = offset
				offset = index(sumitem,sumentity1stword)
				print "      offset: ["  offset "]"
				

				# first - top entity
				if ( sumentitycnt == 1 ) {
					print "sumitemis first - top entity"
					continue
				}
				
				# item is same level or above as last
				if ( offset <= lastoffset ) {
					print "last [" lastsumentity "] is bottom entity"
					# do_last_sumentity

					# item is same level as last
					if ( offset == lastoffset ) {
						print "sumitemis same level as last"
						continue
					}

					# item is above last
					if ( offset < lastoffset ) {
						print "sumitemis above last item "
						continue
					}

				}

				# item is child of last entity
				if ( offset > lastoffset ) {
					print "sumitemis child of last entity"
					if ( parentpath != "" )
						parentpath = parentpath " -> "
					parentpath = parentpath curparent
					curparent = lastsumentity
					continue
			
				}
			}
			continue
		}
	}


	#"Chargeback Cost Details"
	if ( aarr[1] == "Chargeback" && 
	     aarr[2] == "Cost" &&
	     aarr[3] == "Details") {
		print ""
		print "items after \"Chargeback Cost Details\" section"
		print "==============================================="

#[vApps]
#[Parent Entity: D8843]
#[Pricing Model: VBA IaaS Cloud PAYG Pricing Model]
#[Rolled up cost from child entities                                      420.24]
#[Total Charges                                                           420.24]
		procdetail = 1
		detlinecnt = 0
		itemline1 = ""
		itemid = ""

		while ( procdetail ) {
			getline
			if ( NF == 0 )	# skip blank line
				continue
			++detlinecnt
			if ( detlinecnt == 1 ) {
				#itemid= $0
				itemline1= $0
				print "itemline1: [" itemline1 "]"
				split(itemline1, itemline1_arr, " - ")
				itemid = itemline1_arr[1]
				itemid = toupper(itemid)
				gsub(/ /,"",itemid)
				#print "itemid =[" itemid "]"
				continue
			}

			detbline = $0
			print "detbline:  [" detbline "]"
			split(detbline, detbarr, " ")

			# skip page break and headers
			if ( detbarr[1] == "Page:" ) {
				print "skip page break and header - 1"
				# read next page header
				getline
				continue
			}
			#"Parent Entity: D8843"
			if ( detbarr[1] == "Parent" && 
			     detbarr[2] == "Entity:") {
				#parententity =  detbarr[3]
				parententityline = substr(detbline,16)

				split(parententityline, parententityline_arr, " - ")
				parententity = parententityline_arr[1]

				gsub(/ /,"",parententity)
				parententity =  toupper(parententity)
				#parententity = summaryid "-" parententity
				print "parententity=[" parententity "]"
				##serviceid = parententity "-" itemid
				if ( itemid == summaryid ) {
					in_grand_total = 1
					#print "Grand Total....."
					serviceid = "GRANDTOTAL"
				}
				else {
					serviceid = summaryid "-" itemid
					serviceid = substr(serviceid,1,45)
					serviceid = toupper(serviceid)
					#print ""
					print "__serviceid=[" serviceid "]"
					#print "---------"
				}
				continue
			}


			#"Pricing Model: VBA IaaS Cloud PAYG Pricing Model"
			if ( detbarr[1] == "Pricing" && 
			     detbarr[2] == "Model:") {
				pricingmodel =  detbarr[3]
				#print "pricingmodel=[" pricingmodel "]"
				continue
			}


			#"Usage Charges"
			if ( detbarr[1] == "Usage" && 
			     detbarr[2] == "Charges") {
				in_usage_charges = -1
				print "set in_usage_charges=[" in_usage_charges "]"
				continue
			}


			#"Total Charges                                                                                                         nnn.nn"
			if ( detbarr[1] == "Total" && 
			     detbarr[2] == "Charges") {
				totcost =  detbarr[3]
				gsub(/,/,"",totcost)
				if ( in_usage_charges != 0 ) {
					print "totcost = [" totcost "]"
					in_usage_charges = 0
					print "reset in_usage_charges=[" in_usage_charges "]"
				}


				if ( in_grand_total != 0 ) {
					print "Grand Total....."
					print "totcost = [" totcost "]"
					in_grand_total = 0
					print "reset in_grand_total=[" in_grand_total "]"
				}
				break
			}



			if ( in_usage_charges != 0 ) {
				# first time in
				if ( in_usage_charges == -1 ) {
			 		print "HANDLE: itemid =[" itemid "]"
					print ""
					print "parententity=[" parententity "]" " " "serviceid=[" serviceid "]"
					print "---------"
					print "pricingmodel=[" pricingmodel "]"
					print ""
					in_usage_charges = -2
					print "set done first time in_usage_charges=[" in_usage_charges "]"
				}
				else
				if ( in_usage_charges == -2 ) {
					#  which detail format ?
# format 1
#[ Start Time     End Time             Resource         Base Rate            Used Units      Duration]
#[                                                                  Factor                                     (AUD)]
#[ 01-Oct-15      31-Oct-15]
#[                          Storage (GB)                    0.150   1.000          8.26    MONTHLY              1.24]
#[   00:00          23:59]
#[Rolled up cost from child entities                                                                            0.00]
#[Total Charges                                                                                                 1.24]

					#if ( detbline == " Start Time     End Time             Resource         Base Rate            Used Units      Duration" ) {
					if ( detbarr[1] == "Start" && 
					     detbarr[2] == "Time" &&
					     detbarr[3] == "End" &&
					     detbarr[4] == "Time" &&
					     detbarr[5] == "Resource" &&
					     detbarr[6] == "Base" &&
					     detbarr[7] == "Rate" &&
					     detbarr[8] == "Used" &&
					     detbarr[9] == "Units" &&
					     detbarr[10] == "Duration") {
						in_usage_charges = 1
						print "set detail format 1 in_usage_charges=[" in_usage_charges "]"


						base_rate_offset = index(detbline, "  Base Rate ")
						print "   base_rate_offset=[" base_rate_offset "]"
					}

				}
				else 
				if ( in_usage_charges == 1 ) {
					print "handle_usage_charges_detail_format_1"
					#[ 19-Aug-15      19-Aug-15]
					if ( match(detbline, /^ *[0-9][0-9]-...-[0-9][0-9] *[0-9][0-9]-...-[0-9]. *$/) ) {

						print "found dates"
						split(detbline,date_arr, " ")
						sdate = date_arr[1]
						edate = date_arr[2]
						print " sdate=[" sdate "]" " edate=[" edate "]"

						getline detbline2
						print "detbline2: [" detbline2 "]"
						
						Resource = clip(substr(detbline2,27, base_rate_offset - 27))
						print "   Resource=[" Resource "]"
						split(substr(detbline2,base_rate_offset), bvals_arr, " ")
						print " bvals_arr[1]=[" bvals_arr[1] "]"
						print " bvals_arr[2]=[" bvals_arr[2] "]"
						print " bvals_arr[3]=[" bvals_arr[3] "]"
						print " bvals_arr[4]=[" bvals_arr[4] "]"
						print " bvals_arr[5]=[" bvals_arr[5] "]"



						getline detbline3
						print "detbline3: [" detbline3 "]"
						split(detbline3,time_arr, " ")
						stime = time_arr[1]
						etime = time_arr[2]
						print " stime=[" stime "]" " etime=[" etime "]"

						# write detail csv
						printf("%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s\n", rmc(parententity), rmc(serviceid), rmc(sdate), rmc(stime), rmc(edate), rmc(etime), rmc(Resource), rmc(bvals_arr[1]), rmc(bvals_arr[2]), rmc(bvals_arr[3]), rmc(bvals_arr[4]), rmc(bvals_arr[5])) >> detcsvfile

						totcost =  bvals_arr[5]
						gsub(/,/,"",totcost)

						# do serviceid totals
					
						stag = parententity "|" serviceid
						print "add serviceid=[" stag "]" "   totcost = [" totcost "]"
						parententity_serviceid_total_arr[stag] += totcost
						
						# do parent entity totals
						print "add parententity=[" parententity "]" "   totcost = [" totcost "]"
						parententity_total_arr[parententity] += totcost

					}

				}
				else
				if ( in_usage_charges == 2 ) {
					print "handle_usage_charges_detail_format_2"


					#....



				}
				else {
					outp = "ERROR: usage detail sync error NR=" NR
					print outp
					print outp >> importerrorsfile
				}
			}

		}
		print "====================================="
		next
	}


}


function docsvsummary(sumfile)
{
	print "" >sumfile

	print "BillPeriod,SummaryID,GrandTotal" >sumfile
	print billperiod ","  summaryid "," grandtotal_cost >sumfile


	print "" >sumfile
	print "" >sumfile
	print "ParentEntity,Total" >sumfile
	for (parenentity in parententity_total_arr ) {
		totalcost = parententity_total_arr[parenentity]
		print rmc(parenentity) "," rmc(totalcost) >sumfile
	}

	print "" >sumfile
	print "" >sumfile
	print "ParentEntity,ServiceID,Total" >sumfile
	for (stag in parententity_serviceid_total_arr ) {
		split(stag,stag_arr,"|")
		parententity = stag_arr[1]
		serviceid = stag_arr[2]
		totalcost =  parententity_serviceid_total_arr[parententity "|" serviceid]
		print parententity "," serviceid "," totalcost >sumfile
	}

}




function dohtmlsummary(sumfile)
{
	print "<br>" >sumfile

	print "billperiod=[" billperiod "]" >sumfile
	print "Total Cost for [" summaryid "] is [" grandtotal_cost "]" >sumfile


	print "<br>" >sumfile
	print "<br>" >sumfile
	print "<table border=1>" >sumfile
	print "<tr> <th>ParentEntity</th> <th>Total</th> </tr>" >sumfile
	for (parenentity in parententity_total_arr ) {
		totalcost = parententity_total_arr[parenentity]
		print "<tr> <td>" parenentity "</td> <td>" totalcost "</td> </tr>" >sumfile
	}
	print "</table>" >sumfile

	print "<br>" >sumfile
	print "<br>" >sumfile
	print "<table border=1>" >sumfile
	print "<tr> <th>ParentEntity</th> <th>ServiceID</th> <th>Total</th> </tr>" >sumfile
	for (stag in parententity_serviceid_total_arr ) {
		split(stag,stag_arr,"|")
		parententity = stag_arr[1]
		serviceid = stag_arr[2]
		totalcost =  parententity_serviceid_total_arr[parententity "|" serviceid]
		print "<tr> <td>" parententity "</td> <td>" serviceid "</td> <td>" totalcost "</td> </tr>" >sumfile
	}
	print "</table>" >sumfile

}


END {
	print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
	print "billperiod=[" billperiod "]"
	print "summaryid=[" summaryid "] grandtotal_cost=[" grandtotal_cost "]"
	print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
	for (parenentity in parententity_total_arr ) {
		print "parententity_total_arr[" parenentity "] = " parententity_total_arr[parenentity]
	}
	print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
	for (stag in parententity_serviceid_total_arr ) {
		split(stag,stag_arr,"|")
		parententity = stag_arr[1]
		serviceid = stag_arr[2]
		print "parententity_serviceid_total_arr[" parententity "|" serviceid "] = " parententity_serviceid_total_arr[parententity "|" serviceid]

		# generate a unique list of serviceids
		sortcmd = "sort -u > " uniqserviceidfile
		print parententity "," serviceid | sortcmd
	}
	print "++++++++++++++++++++++++++++++++++++++++++++++++++++++++++"
	print "done."

	docsvsummary(sumcsvfile)
	#dohtmlsummary(sumhtmlfile)
}
' <"${procfile}.txt" >"${cnvcblogfile}"


if [ -s "$importerrorsfile" ]; then
	importerrors=`cat "$importerrorsfile"`
else
	importerrors="No Import Errors"
fi


#----------------------------------------------------------
# from ldvmcb3

if [ ! -f "$detcsvfile" ]; then
	echo "$0: detcsvfile=[$detcsvfile] does not exist"
	exit 1
fi


# decied not to load summary data into tbs first
## Testing
##../tbssql >tdatacollection_${billperiod}.out <<SQLCMD0
##select tDataCollection.* from tDataCollection, tBatch
##where tBatch.ID = tDataCollection.Batchid
##  and tBatch.BillingPeriod = "$tbsbillingperiod"
##  and tBatch.BatchTypeID = 648
##order by ServiceID
##SQLCMD0

awk -F'|' \
  -v"detcsvfile=$detcsvfile" \
  -v"sqlfile=$sqlfile" \
  -v"ldvmcblogfile=$ldvmcblogfile" \
  -v"batchid=$BATCHID" \
'
function trim(s)
{
        sub(/^[ \t]*/,"",s)
        sub(/[ \t]*$/,"",s)
        return s
}


function mround2(cst)
{
	if ( match(cst,"\...5$") > 0 )
		cst += 0.001
	return sprintf("%0.2f", cst)
}


function ld_detcsvfile(detcsvfile) {
	printf("ld_detcsvfile(%s)\n", detcsvfile)
	detreccnt = 0
	while ( (getline aline < detcsvfile) > 0 ) {
		split(aline, a_arr, ",")
		##printf("aline=%s\n", aline)
		f = 0
		ParentEntity = a_arr[++f]
		ServiceID = a_arr[++f]
		StartDate = a_arr[++f]
		StartTime = a_arr[++f]
		EndDate = a_arr[++f]
		EndTime = a_arr[++f]
		Resource = a_arr[++f]
		BaseRate = a_arr[++f]
		RateFactor = a_arr[++f]
		UsedUnits = a_arr[++f]
		Duration = a_arr[++f]
		TotalCharge = a_arr[++f]

		if ( ++detreccnt == 1 )	# skip header
			continue

		print "TotalCharge=[" TotalCharge "]"

		#stag = ParentEntity "|" ServiceID
		stag = ServiceID
		detidx = csvdet_stag_cnt[stag] + 1
		csvdet_stag_cnt[stag] = detidx

		csvdetrec[stag "|" detidx] = aline
		print "------------------------------------------------------"
		print " csvdetrec[" stag "|" detidx "]=[" csvdetrec[stag "|" detidx] "]"

		csvdetrec_ParentEntity[stag "|" detidx] = ParentEntity
		csvdetrec_ServiceID[stag "|" detidx] = ServiceID
		csvdetrec_StartDate[stag "|" detidx] = StartDate
		csvdetrec_StartTime[stag "|" detidx] = StartTime
		csvdetrec_EndDate[stag "|" detidx] = EndDate
		csvdetrec_EndTime[stag "|" detidx] = EndTime
		csvdetrec_Resource[stag "|" detidx] = Resource
		csvdetrec_BaseRate[stag "|" detidx] = BaseRate
		csvdetrec_RateFactor[stag "|" detidx] = RateFactor
		csvdetrec_UsedUnits[stag "|" detidx] = UsedUnits
		csvdetrec_Duration[stag "|" detidx] = Duration
		csvdetrec_TotalCharge[stag "|" detidx] = TotalCharge

		handle_tdatacollection_rec(stag, detidx)
	}
	close(detcsvfile)

	for ( stag in csvdet_stag_cnt ) {
		print "    csvdet_stag_cnt[" stag "]=" csvdet_stag_cnt[stag]
	}

}


function dotbssql(sql)
{
	print "dotbssql: " sql
	print sql ";" > sqlfile
	#sqlcmd = "tbssql >>" ldvmcblogfile " 2>&1"
	#print sql | sqlcmd
	#close(sqlcmd)
##	print sql "\n" "\\go" | sqlcmd
##	close(sqlcmd)
}
                                                                           

function tDataCollection_insert(arec)
{
	sub(/\r$/,"",$0)
        sql = "insert into " "tDataCollection" " values ("
	
	for ( i = 2; i <= 39; ++i ) {	# skip over ID at start
                if ( i > 2 )
                        sql = sql ", "
		val = trim(arec[i])
		if ( val == "" ) {
			sql = sql "null"
		}
		else {
# 1, ID|int|no|4|10|0|no|(n/a)|(n/a)||
#12, Peak|bit|no|1| | |yes|(n/a)|(n/a)||
#15, AmountExGST|money|no|8|19|4|yes|(n/a)|(n/a)||
#16, GSTAmount|money|no|8|19|4|yes|(n/a)|(n/a)||
#17, AmountIncGST|money|no|8|19|4|yes|(n/a)|(n/a)||
#20, BatchID|int|no|4|10|0|yes|(n/a)|(n/a)||
#21, TransactionTypeID|int|no|4|10|0|yes|(n/a)|(n/a)||
#22, StatusID|int|no|4|10|0|yes|(n/a)|(n/a)||
#38, SupplierServiceCodeID|int|no|4|10|0|yes|(n/a)|(n/a)||
#39, RevenueFlag|bit|no|1| | |yes|(n/a)|(n/a)||
			if ( i == 1 || i == 12 || i == 15 || i == 16 || i == 17 || i == 20 || i == 21 || i == 22 || i == 38 || i == 39 )
				sql = sql val
			else
				sql = sql "\"" val "\""
		}
        }
        sql = sql ")"
        dotbssql(sql)
        #if ( NR > 100 ) {
        #       dotbssql("select count(*) from " atable")
        #       exit 0
        #}
}


BEGIN {
	datacollectiontable = "tDataCollection"
        #dotbssql("select count(*) from " datacollectiontable)

	# delete summary entry(s)
        dotbssql("delete from " datacollectiontable " where BatchID = " batchid )

	ld_detcsvfile(detcsvfile)
	print "-----------------------------"


	exit(0)
}


function MMM_to_MM(MMM)
{
	mmmstr["Jan"] = 1
	mmmstr["Feb"] = 2
	mmmstr["Mar"] = 3
	mmmstr["Apr"] = 4
	mmmstr["May"] = 5
	mmmstr["Jun"] = 6
	mmmstr["Jul"] = 7
	mmmstr["Aug"] = 8
	mmmstr["Sep"] = 9
	mmmstr["Oct"] = 10
	mmmstr["Nov"] = 11
	mmmstr["Dec"] = 12
	return 0 + mmmstr[MMM]
}

function fixdetdate(detdatestr)
{
	# fix date format
	## ie. 30-Nov-15 -> 30-Nov-2015
	##return substr(detdatestr,1,7) "20" substr(detdatestr,8,2)
	# ie. 30-Nov-15 -> 30/11/2015
	DD = substr(detdatestr,1,2)
	MMM = substr(detdatestr,4,3)
	YY = substr(detdatestr,8,2)
	MM = sprintf("%02d", MMM_to_MM(MMM))
	YYYY = "20" YY
	return DD "/" MM "/" YYYY
}


function handle_tdatacollection_rec(stag, detidx)
{
	#print "------------------------------------------"

	f = 0
	ID = ""
	SequenceNo = ""
	ServiceID = ""
	ServiceType = ""
	TxnDate = ""
	TxnTime = ""
	Origin = ""
	Destination = ""
	Duration = ""
	CallCode = ""
	RateDescription = ""
	Peak = ""
	DialledNumber = ""
	GSTFlag = ""
	AmountExGST = ""
	GSTAmount = ""
	AmountIncGST = ""
	FromDate = ""
	ToDate = ""
	BatchID = ""
	TransactionTypeID = ""
	StatusID = ""
	Var01 = ""
	Var02 = ""
	Var03 = ""
	Var04 = ""
	Var05 = ""
	Var06 = ""
	Var07 = ""
	Var08 = ""
	Var09 = ""
	Var10 = ""
	Var11 = ""
	Var12 = ""
	Var13 = ""
	Var14 = ""
	Var15 = ""
	SupplierServiceCodeID = ""
	RevenueFlag = ""

	i = detidx
	print "stag=[" stag "]" "  detidx=[" detidx "]"
	dodatacollectionrec = 1

	if ( dodatacollectionrec == 1 ) {
		print "i =" i
		detParentEntity = csvdetrec_ParentEntity[stag "|" i]
		detServiceID = csvdetrec_ServiceID[stag "|" i]
		detStartDate = csvdetrec_StartDate[stag "|" i]
		detStartTime = csvdetrec_StartTime[stag "|" i]
		detEndDate = csvdetrec_EndDate[stag "|" i]
		detEndTime = csvdetrec_EndTime[stag "|" i]
		detResource = csvdetrec_Resource[stag "|" i]
		detBaseRate = csvdetrec_BaseRate[stag "|" i]
		detRateFactor = csvdetrec_RateFactor[stag "|" i]
		detUsedUnits = csvdetrec_UsedUnits[stag "|" i]
		detDuration = csvdetrec_Duration[stag "|" i]
		detTotalCharge = csvdetrec_TotalCharge[stag "|" i]

		ServiceID = detServiceID
		BatchID = batchid
		TransactionTypeID = 106
		if ( done_serviceid[stag] != 1 ) {
			++serviceidcnt	
			done_serviceid[stag] = 1
		}
		SequenceNo = serviceidcnt

		fix_detStartDate = fixdetdate(detStartDate)
		fix_detEndDate = fixdetdate(detEndDate)

		print "insert det rec csvdetrec[" stag "|" i "]=[" csvdetrec[stag "|" i] "]"
		print "       detServiceID=[" detServiceID "]"

		#detAmountIncGST = detTotalCharge
		#detGSTAmount = mround2(detAmountIncGST / 11)
		#detAmountExGST = mround2(detAmountIncGST - detGSTAmount)

		detAmountExGST = detTotalCharge
		detGSTAmount = mround2(detAmountExGST * 10 / 100)
		detAmountIncGST = detAmountExGST + detGSTAmount

		print "       detAmountExGST=[" detAmountExGST "]"
		print "         detGSTAmount=[" detGSTAmount "]"
		print "      detAmountIncGST=[" detAmountIncGST "]"
		print ""


		# set detail tDataCollection value 
		dcrecf = 0
		datacollection_rec[++dcrecf] = ID
		datacollection_rec[++dcrecf] = (SequenceNo * 10000) + dcrecf
		datacollection_rec[++dcrecf] = ServiceID
		datacollection_rec[++dcrecf] = ServiceType
		datacollection_rec[++dcrecf] = TxnDate
		datacollection_rec[++dcrecf] = TxnTime
		datacollection_rec[++dcrecf] = Origin
		datacollection_rec[++dcrecf] = Destination
		datacollection_rec[++dcrecf] = Duration
		datacollection_rec[++dcrecf] = CallCode
		datacollection_rec[++dcrecf] = RateDescription
		datacollection_rec[++dcrecf] = Peak
		datacollection_rec[++dcrecf] = DialledNumber

		datacollection_rec[++dcrecf] = "Y"
		datacollection_rec[++dcrecf] = detAmountExGST
		datacollection_rec[++dcrecf] = detGSTAmount
		datacollection_rec[++dcrecf] = detAmountIncGST

		#datacollection_rec[++dcrecf] = detStartDate " " detStartTime
		datacollection_rec[++dcrecf] = fix_detStartDate
		#datacollection_rec[++dcrecf] = detEndDate " " detEndTime
		datacollection_rec[++dcrecf] = fix_detEndDate

		datacollection_rec[++dcrecf] = BatchID
		datacollection_rec[++dcrecf] = TransactionTypeID
		datacollection_rec[++dcrecf] = StatusID

		datacollection_rec[++dcrecf] = fix_detStartDate
		datacollection_rec[++dcrecf] = detStartTime
		datacollection_rec[++dcrecf] = fix_detEndDate
		datacollection_rec[++dcrecf] = detEndTime
		datacollection_rec[++dcrecf] = detResource
		datacollection_rec[++dcrecf] = detBaseRate
		datacollection_rec[++dcrecf] = detRateFactor
		datacollection_rec[++dcrecf] = detUsedUnits
		datacollection_rec[++dcrecf] = detDuration
		datacollection_rec[++dcrecf] = detParentEntity

		datacollection_rec[++dcrecf] = Var11
		datacollection_rec[++dcrecf] = Var12
		datacollection_rec[++dcrecf] = Var13
		datacollection_rec[++dcrecf] = Var14
		datacollection_rec[++dcrecf] = "VMCHARGEBACKDETAIL"
		datacollection_rec[++dcrecf] = SupplierServiceCodeID
		datacollection_rec[++dcrecf] = RevenueFlag

		tDataCollection_insert(datacollection_rec)
	}

}

END {
        dotbssql("update tBatch set BatchStatusID = 2 where ID = " batchid )
        #dotbssql("select count(*) from " datacollectiontable)
}
'  > "$ldvmcblogfile"

#' <"tdatacollection_${billperiod}.out" > "$ldvmcblogfile"


echo "" >>"$ldvmcblogfile"
echo "-------------------------------------------------" >>"$ldvmcblogfile"
if [ "$RUNTDATASQL" == "1" ]; then
	echo "Running tDataCollection SQL" >>"$ldvmcblogfile"
	(tbssql <"$sqlfile" 2>&1) >>"$ldvmcblogfile"
else
	echo "Not Running tDataCollection SQL" >>"$ldvmcblogfile"
fi


#----------------------------------------------------------
# email results....
# Testing rjs
#emaillist="rod@transmit.com.au"
#emaillist="rshep2@tpg.com.au"
##emaillist="rod@transmit.com.au Karl.Coelho@victrack.com.au"
##emaillist="rod@transmit.com.au robyn.douglas@victrack.com.au"
#emaillist="rod@transmit.com.au robyn.douglas@victrack.com.au michael.bridges@victrack.com.au Telco.billing@victrack.com.au"
emaillist="rod@transmit.com.au michael.wang@victrack.com.au Telco.billing@victrack.com.au"

##"${sumhtmlfile} \

if [ -n "$EMAILOVERRIDE" ]; then
	emaillist="$EMAILOVERRIDE"
	echo "EMAILOVERRIDE is set = [$EMAILOVERRIDE]"
fi

attfiles=""

if [ "$INCLUDEATTACHMENTS" == "1" ]; then
	attfiles="\
	${procfile} 
	${procfile}.txt \
	${uniqserviceidfile} \
	${detcsvfile} \
	${sumcsvfile} \
	${cnvcblogfile} \
	${sqlfile} \
	${ldvmcblogfile} \
	"

	#attfiles="\
	#${procfile}.txt \
	#${detcsvfile} \
	#${sumcsvfile} \
	#${cnvcblogfile} \
	#"
fi

#Please find the attached files containing

subject="cnvchargeback: $procfile"
emcontent="VMWare ChargeBack PDF Import Processing"
t21em_content=`cat <<SOMETEXT1

file://s-vm-telmax01/homes/links/bilmax21_ex4-3b_indata/${billperiod}/VMCHARGEBACK/${SHIPTO}/out

Please follow the above link to files containing
the import data PDF and converted text version of it.
a Summary CSV Excel file,
a Detail CSV Excel file of the data that will be loaded,
a unique list of ServiceId's,
a SQL file that was run into TBS tDataCollection
and PDF processing and SQL loading Log files
for...

    Billperid: $billperiod
  PDFPassword: $PDFPASSWORD
      BatchID: $BATCHID
       ShipTo: $SHIPTO

${importerrors}

For Transmit support only
https://support:20443/t21/topdat/indata/${billperiod}/VMCHARGEBACK/${SHIPTO}/out
SOMETEXT1
`

# inline content text from file
t21em_content_file="$tmpfile"
export emcontent t21em_content t21em_content_file


ematt "$attfiles" "$subject" $emaillist
}


#---------------------------------------------------------------
# main


if [ -n "$orginfile" ]; then
	proc_chargeback_pdf "$billperiod" "$orginfile" "$PDFPASSWORD" "$BATCHID"

else
	#ls ../indata/VMCHARGEBACK/???/*.[pP][dD][fF] |  \

	ls ../indata/$billperiod/VMCHARGEBACK/$SHIPTO/*.[pP][dD][fF] |  \
	while read orginfile
	do
		echo "proc_chargeback_pdf \"$billperiod\" \"$orginfile\""
		proc_chargeback_pdf "$billperiod" "$orginfile" "$PDFPASSWORD" "$BATCHID"
	done
fi


##########################################################
# send file to user
#cat $tmpfile | pr_rep system vmchargeback_
rm -f "$tmpfile"
rm -f "$tmp1file"

exit 0