#! /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 "
" >sumfile
print "billperiod=[" billperiod "]" >sumfile
print "Total Cost for [" summaryid "] is [" grandtotal_cost "]" >sumfile
print "
" >sumfile
print "
" >sumfile
print "
" >sumfile
print " ParentEntity | Total |
" >sumfile
for (parenentity in parententity_total_arr ) {
totalcost = parententity_total_arr[parenentity]
print " " parenentity " | " totalcost " |
" >sumfile
}
print "
" >sumfile
print "
" >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
}
print "
" >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 < 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 <