: !/bin/sh carrier="OPTUS" month="$1" infile="$2" outfile="$3" datasrcid="$4" #adjmonth=$month #adjmonth=`../../../adjmonth $month` #datadirpat=`echo "$adjmonth" | awk '{ print "??" substr($1,1,2) substr($1,5,2) }'` #echo "$0: datadirpat=[$datadirpat]" #infile=`ls ${datadirpat}/*/*.bil` if [ "$outfile" = "" ]; then outfile="${carrier}_${month}.out" fi #echo "$0: infile=[$infile] outfile=[$outfile]" rm -f $outfile #rplcomma <$infile | sed -e 's/^"//;s/","/|/g;s/ $//;s/"$//;s/,/;/g' "$infile" | \ awk -F '|' -v "billmonth=$month" -v "carrier=$carrier" \ -v "datasrcid=$datasrcid" ' function trim(is) { #sub(/^\"/,"", is) #sub(/\"$/,"", is) while (sub(/^ /, "", is) != 0) is = is "" while (sub(/ $/, "", is) != 0) is = is "" return is } ##################################################### # main BEGIN { monthstr[1] = "Jan"; monthstr[2] = "Feb"; monthstr[3] = "Mar"; monthstr[4] = "Apr"; monthstr[5] = "May"; monthstr[6] = "Jun"; monthstr[7] = "Jul"; monthstr[8] = "Aug"; monthstr[9] = "Sep"; monthstr[10] = "Oct"; monthstr[11] = "Nov"; monthstr[12] = "Dec"; billmm = 0 + substr(billmonth,1,2) billyyyy = 0 + substr(billmonth,3,4) billperiod = monthstr[billmm] " " billyyyy } { gsub(/\r/, "") # remove CR gsub(/\032/, "") # remove FF linetype = trim($2) # total cost / account number / invoice number (issue date "dd MMM yy") if (linetype == "000000" ) { if ( NF != 11 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 + trim($9) outrec() next } rectype = "CHARGE_DESC_INFO" recclass = "TOTAL" datestr = trim($6) yyyy = 0 + substr(datestr,1,4) mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) from_date = sprintf("%02d/%02d/%04d", dd, mm, yyyy) datestr = trim($4) yyyy = 0 + substr(datestr,1,4) mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) to_date = sprintf("%02d/%02d/%04d", dd, mm, yyyy) datestr = trim($7) yyyy = 0 + substr(datestr,1,4) yy = 0 + substr(datestr,3,2) mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) issue_date = sprintf("%d %3s %02d", dd, monthstr[mm], yy) sdate_str = to_date btxt_stime_str = "" account_number = trim($3) # account number btxt_origin = account_number btxt_destination = "Total Cost" btxt_dialled = from_date " to " to_date # save total_from = from_date total_to = to_date total_from_to = total_from " to " total_to invoice_number = issue_date btxt_rate = invoice_number # invoice number dursecs = 0 costinclgst = 0.0 + trim($9) # total cost outrec() next } # store cost centre, name # store name, account number, invoice number if ( linetype == "323005" ) { if ( NF != 11 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } # cost centre, name rectype = "CONFIG" recclass = "COSTCENTRE" servicenumber = trim($3) sdate_str = "" btxt_stime_str = "" btxt_origin = trim($9) # cost centre btxt_destination = trim($8) # name btxt_dialled = "" btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() # name, account number, invoice number rectype = "CONFIG" recclass = "NAME" servicenumber = trim($3) sdate_str = "" btxt_stime_str = "" btxt_origin = account_number # account number btxt_destination = trim($8) # name btxt_dialled = "" btxt_rate = invoice_number # invoice number dursecs = 0 costinclgst = 0.0 outrec() next } # Phone Summary Section if ( linetype == "250500" ) { if ( NF != 10 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } # init mobilevars recurring = 0.0 recurring_discount = 0.0 adjustment = 0.0 callcounttotal = 0 calltotal = 0.0 call_discount = 0.0 calltotal_with_discount = 0.0 class = "" billdate = trim($4) servicenumber = trim($6) next } ################################################## # rent/call/other (originally based on TAS vodafone.pas code) # call summary / other summary if (linetype == "350505" ) { if ( NF != 5 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } rectype = "CHARGE_DESC" linecode = trim($3) sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = trim($4) # description btxt_dialled = total_from_to btxt_rate = "" dursecs = 0 costinclgst = 0.0 + trim($5) # call summary (incl. discounts) if (linecode == "U-CAL" || linecode == "U-DAS" || linecode == "U-IAR" || linecode == "F-SMC" || linecode == "F-SMT" || linecode == "PROMO") { recclass = "CALL" if ( costinclgst != 0 ) { # store cost for adjustment calc. later calltotal_with_discount += costinclgst outrec() } next } # other if (linecode == "OCC") { recclass = "OTHER" if ( costinclgst != 0 ) { outrec() } next } next } # rent summary / SMS CALL Detail if (linetype == "351005" ) { if ( NF != 11 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } linecode = trim($3) datestr = trim($8) if ( datestr != "00000000" ) { yyyy = 0 + substr(datestr,1,4) mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) from_date = sprintf("%02d/%02d/%04d", dd, mm, yyyy) } else { from_date = total_from } datestr = trim($9) if ( datestr != "00000000" ) { yyyy = 0 + substr(datestr,1,4) mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) to_date = sprintf("%02d/%02d/%04d", dd, mm, yyyy) } else { to_date = total_to } sdate_str = to_date btxt_stime_str = "" btxt_origin = "" btxt_destination = trim(trim($4) " " trim($6)) # description btxt_dialled = from_date " to " to_date btxt_rate = "" dursecs = 0 costinclgst = 0.0 + trim($5) # RENT if (linecode == "SRVC" || linecode == "F-VM" || linecode == "F-SMS" ) { rectype = "CHARGE_DESC" recclass = "RENT" # add up recurring chages recurring = recurring + costinclgst # output rent if not 0 if ( costinclgst != 0 ) { outrec() next } } # CALL if (linecode == "F-SMC") { rectype = "CDR" btxt_stime_str = "000000" recclass = linecode if ( costinclgst != 0 ) { ## call total not incl. discount must be calc. ## as NO record contains it #calltotal += costinclgst # add cdr call count ++callcounttotal outrec() next } } next } ################ # recurring_discount if (linetype == "222599" ) { if ( NF != 4 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } # store so we can add call discount later recurring_discount = recurring_discount + 0.0 + trim($4) next } ################################################## # call detail # Call Detail section header if ( linetype == "353501" ) { class = trim($7) next } if ( linetype == "353505" ) { if ( NF != 20 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } rectype = "CDR" callclass = trim($4) billsection = trim($3) if ( class != "" ) callclass = class " " billsection else callclass = billsection # Bill Data btxt = $0 #printf("Service Number = [%s] Call Class = [%s]\n", servicenumber, callclass); #printf("%s\n", btxt); datestr = trim($4) #yyyy = 0 + substr(datestr,1,4) # is always 0000 ??? mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) #printf("dd=[%s] mm=[%s]\n", dd, mm); billcc = 0 + substr(billdate,1,2) billyy = 0 + substr(billdate,3,2) billmm = 0 + substr(billdate,5,2) billdd = 0 + substr(billdate,7,2) #printf("billdate=[%s]\n", billdate); #printf("billcc=[%s]\n", billcc); #printf("billyy=[%s]\n", billyy); sdate_str = sprintf("%02d/%02d/%02d%02d", dd, mm, billcc, billyy) stimestr = trim($5) btxt_stime_str = substr(stimestr,1,2) substr(stimestr,3,2) "00" btxt_origin = trim($7) btxt_destination = trim($8) btxt_dialled = trim($9) #if ( btxt_dialled == "**INCOMING*" ) # next # see whats in fields #print callclass | "sort -u >callclass.uniq" #print btxt_dialled | "sort -u >btxt_dialled.uniq" ### use unprocessed NUMBER for dest description ###btxt_destination = btxt_dialled ## process dialled number to remove inconsistencies #sub(/YES$/, "", btxt_dialled) # remove YES if ( length(btxt_dialled) >= 11 ) sub(/^61/, "0", btxt_dialled) # replace with 0 btxt_rate = trim($10) btxt_distance_flag = trim($11) if ( btxt_distance_flag != "" ) callclass = callclass " " btxt_distance_flag if ( btxt_distance_flag == "L" || btxt_distance_flag == "N" ) { if ( btxt_destination != "" ) btxt_dialled = btxt_dialled " " btxt_destination btxt_destination = "NATIONAL" if ( btxt_distance_flag != "" ) btxt_destination = btxt_destination " " btxt_distance_flag } recclass = callclass btxt_duration = trim($14) #printf("btxt_duration=[%s]\n", btxt_duration); #durstr = btxt_duration #durmins = 0.0 + durstr ##printf("durmins=[%s]\n", durmins); #dursecs = int(durmins * 60) ##printf("dursecs=[%s]\n", dursecs); split(btxt_duration, dur_arr, ".") durmmmm = 0 + dur_arr[1] durss = 0 + dur_arr[2] dursecs = (60 * durmmmm) + durss #printf("durmmmm=[%s] durss=[%s] dursecs[%s]\n", durmmmm, durss, dursecs); btxt_cost = trim($15) costinclgst = 0.0 + sprintf("%0.3f", 0.0 + btxt_cost) # get dest onnet/offnet #cmd0 = sprintf("../getnet %s", btxt_dialled) #cmd0 | getline netdesttype #close cmd0 #printf("cmd0=[%s]\n", cmd0) #nettype = sprintf("MOBILEONNET_%s", netdesttype) ## call total not incl. discount must be calculated ## as NO record contains it #calltotal += costinclgst # add cdr call count ++callcounttotal outrec() #printf("\n") next } ########################################## # call totals if (linetype == "150099" ) { if ( NF != 4 ) { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } rectype = "CHARGE_DESC_INFO" recclass = "CALL_TOTAL" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Call Charges (not incl. discount)" btxt_dialled = total_from_to btxt_rate = "" #dursecs = 0 + trim($3) # total call count # the above count includes other non cdr recs dursecs = callcounttotal # total call count costinclgst = 0.0 + trim($4) # total call cost ## the above includes discount ## so get from CDR totals ## (verify report detail$ will always match) #costinclgst = calltotal # for adjustment calc. below calltotal = costinclgst # call total (not incl.discount) # call total if ( costinclgst != 0 ) { outrec() } #################################### # calculate and output call discount call_discount = calltotal_with_discount - calltotal rectype = "CHARGE_DESC_INFO" recclass = "CALL_DISCOUNT" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Call Usage Discount" btxt_dialled = total_from_to btxt_rate = "" dursecs = 0 costinclgst = 0.0 + call_discount # call discount if ( costinclgst != 0 ) { outrec() } #################################### # output recurring discount rectype = "CHARGE_DESC_INFO" recclass = "RECURRING_DISCOUNT" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Recurring Discount (not incl. call discount)" btxt_dialled = total_from_to btxt_rate = "" dursecs = 0 costinclgst = 0.0 + recurring_discount # recurring_discount if ( costinclgst != 0 ) { outrec() } #################################### # calculate and output adjustment call_discount = calltotal_with_discount - calltotal rectype = "CHARGE_DESC_INFO" recclass = "ADJUSTMENT" adjustment = recurring_discount + call_discount sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Total Adjustment (incl. call discount)" btxt_dialled = total_from_to btxt_rate = "" dursecs = 0 costinclgst = 0.0 + adjustment # adjustment if ( costinclgst != 0 ) { outrec() } #################################### # calculate and output call recurring rectype = "CHARGE_DESC_INFO" recclass = "RECURRING" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Recurring Charges" btxt_dialled = total_from_to btxt_rate = "" dursecs = 0 costinclgst = 0.0 + recurring # call discount if ( costinclgst != 0 ) { outrec() } #------------- next } } function outrec() { if ( length(datasrcid) > 0 ) printf("%s|", datasrcid) printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%d|%0.3f|\n", servicenumber, carrier, rectype, recclass, billperiod, sdate_str, btxt_stime_str, btxt_origin, btxt_destination, btxt_dialled, btxt_rate, dursecs, costinclgst) } END { if ( conv_errors > 0 ) { rectype = "ERROR" recclass = "CONVERRORS" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "" btxt_dialled = conv_errors dursecs = 0 costinclgst = 0.0 outrec() } } ' >$outfile test -f callclass.uniq && mv -f callclass.uniq callclass_$1.uniq test -f btxt_dialled.uniq && mv -f btxt_dialled.uniq btxt_dialled_$1.uniq exit 0