#! /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 "" >sumfile for (parenentity in parententity_total_arr ) { totalcost = parententity_total_arr[parenentity] print "" >sumfile } print "
ParentEntity Total
" parenentity " " totalcost "
" >sumfile print "
" >sumfile print "
" >sumfile print "" >sumfile print "" >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 "" >sumfile } print "
ParentEntity ServiceID Total
" parententity " " serviceid " " totalcost "
" >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 <