: !/bin/sh carrier="TELSTRA" month="$1" infile="$2" outfile="$3" datasrcid="$4" ## Testing #month="122007" #infile="indata/Dec_2007/TELSTRA/mobile_phones/10060_RCP39886.DAT" #outfile="1" #datasrcid="10060" #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}/rcp*.dat` if [ "$outfile" = "" ]; then outfile="${carrier}_${month}.out" fi #echo "$0: infile=[$infile] outfile=[$outfile]" rm -f $outfile sed -e 's/ $//' "$infile" | \ awk -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 billcode = substr($0,82,1) billsection = substr($0,83,1) transactiontype = substr($0,84,2) linetype = substr($0,86,1) ################################ # total cost / account number / invoice number #1234567890123456789012345678901234567890123456789012345678901234567890123456789 #RBMICAUNIVERSITY OF WESTERN SYDNEY 4295910402 060802 1A D # TOTAL OF THIS BILL (INCLUDING GST) $1,014.86 946.76 68.10 1014.86 if ( billcode == "1" && billsection == "A" && transactiontype == " " && linetype == "D" && substr($0,86+32,34) == "TOTAL OF THIS BILL (INCLUDING GST)" ) { btxt = substr($0,87,113) if ( length(btxt) != 113 || substr(btxt,75,1) != "." || substr(btxt,88,1) != "." || substr(btxt,100,1) != "." || substr(btxt,111,1) != ".") { ++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(substr(btxt,103,11)) outrec() next } rectype = "CHARGE_DESC_INFO" recclass = "TOTAL" datestr = substr($0,55,6) yy = 0 + substr(datestr,1,2) mm = 0 + substr(datestr,3,2) dd = 0 + substr(datestr,5,2) issue_date = sprintf("%d %3s %02d", dd, monthstr[mm], yy) from_date = sprintf("%02d/%02d/20%02d", dd, mm, yy) sdate_str = from_date to_date = from_date btxt_stime_str = "" account_number = trim(substr($0,37,10)) btxt_origin = account_number # account number btxt_destination = "Total Cost" # save total_from = from_date total_to = to_date total_from_to = total_from " to " total_to btxt_dialled = total_from_to btxt_stime_str = "" invoice_number = issue_date btxt_rate = invoice_number # invoice number dursecs = 0 btxt_cost = trim(substr(btxt,103,11)) costinclgst = 0.0 + btxt_cost outrec() next } #ZZZZZZ # ################################ # # 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 # } # #ZZZZZZ #123456789012345678901234567890123456789012345678901234567890123456789012345678 #RBMICAUNIVERSITY OF WESTERN SYDNEY 4295910402 0608020403097700 2S H # CALL & USAGE CHARGES ################################ # start individual mobile if ( billcode == "2" && billsection == "S" && transactiontype == " " && linetype == "H" ) { btxt = substr($0,87,79) btxt_desc = substr(btxt,6) if ( substr(btxt_desc,1,17) == "MOBILENET SERVICE" ) { finish_mobile() # finish prev mobile servicenumber = trim(substr($0,61,18)) # init mobilevars recurring = 0.0 recurring_discount = 0.0 adjustment = 0.0 callcounttotal = 0 calltotal = 0.0 call_discount = 0.0 recclass = "RENT" } if ( substr(btxt_desc,1,20) == "CALL & USAGE CHARGES" ) { recclass = "CALL" } if ( substr(btxt_desc,1,15) == "SERVICE CHARGES" ) { recclass = "RENT" } next } ################################ # rent/call/other summary recs #123456789012345678901234567890123456789012345678901234567890123456789012345678 #RBMICAVICTRACK -ATT GEORGE COOPER 2084549282 0210220409235702 2S02V # CALL MANAGER 22 OCT TO 21 NOV 5.00 5.00N 0.50 5.50 if ( billcode == "2" && billsection == "S" && transactiontype != " " ) { if ( linetype == "D" ) { # skip summary D recs next } if ( linetype == "T" ) { # skip summary T recs next } btxt = substr($0,87,113) if ( length(btxt) != 113 || substr(btxt,75,1) != "." || substr(btxt,88,1) != "." || substr(btxt,100,1) != "." || substr(btxt,111,1) != ".") { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = billcode "~" billsection "~" transactiontype "~" linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } rectype = "CHARGE_DESC" datestr = substr($0,55,6) yy = 0 + substr(datestr,1,2) mm = 0 + substr(datestr,3,2) dd = 0 + substr(datestr,5,2) from_date = sprintf("%02d/%02d/20%02d", dd, mm, yy) sdate_str = from_date btxt_stime_str = "000000" btxt_origin = "" btxt_destination = trim(substr(btxt,6,30)) # description btxt_dialled = trim(substr(btxt,39,16)) # from - to btxt_rate = "" dursecs = 0 btxt_cost = trim(substr(btxt,103,11)) costinclgst = 0.0 + btxt_cost if ( costinclgst >= 0 ) { # cost is not negative if ( recclass == "CALL" ) { # CALL calltotal += costinclgst } else { # RENT recurring += costinclgst } } else { # cost is negative if ( recclass == "CALL" ) { # CALL call_discount += costinclgst } else { # RENT recurring_discount += costinclgst } } # only output if cost is non zero if ( costinclgst != 0 ) { outrec() next } next } ############################################# # call detail records if (billsection == "D" && linetype == "D") { docalldetail() next } } function docalldetail() { if ( length($0) < 199 ) { printf("too short\n") return } # Bill Image # TYPE: 1 # CALL ANSWERS - MOBILENET MEMO # CALL FORWARDING CHARGES # INTERNATIONAL DIRECT # CALLS RECEIVED O/S (GST FREE) # CALLS MADE O/S (GST FREE) # CALLS MADE O/S -SMS (GST FREE) # MOBILENET MESSAGEBANK # WAKE-UP AND REMINDER CALL # DIAL-IT CALLS # INFORMATION CALLS # MOBILE ORIGINATED SMS # MOBILE WAP/INTERNET CALLS # NATIONAL DIRECT # NATIONAL DIRECT - DATA # SERVICE CALL SURCHARGE # SERVICE CALLS # DIVERTED TO 101 # 1 2 3 4 5 6 7 #SEQ DATE TIME ORIGIN NUMBER RATE MIN/SEC INCL. GST #12345678901234567890123456789012345678901234567890123456789012345678901234 #00001 22/08/02 11:18 MELBOURNE 0404814106MNET P 2:43 1.06 # # TYPE: 2 # ENHANCED SMS # 1 2 3 4 5 6 7 #12345678901234567890123456789012345678901234567890123456789012345678901234 #SEQ DATE TIME SERVICE TYPE DESCRIPTION QTY UNITS INCL. GST #00681 31/08/02 21:52 POCKETNEWS AFL LIVE SCO 1 ONDEMAN 0.04 # # TYPE: 3 # MOBILE WAP/INTERNET SESSIONS # 1 2 3 4 5 6 7 #12345678901234567890123456789012345678901234567890123456789012345678901234 #SEQ DATE TIME ORIGIN ACCESS POINT RATE HH:MM:SS VOL(KB) INCL. GST #00263 27/08/02 11:09 589 COLLINS TELSTRA.INTE O 00:02:16 144 3.39 # # TYPE: 4 (2D13D) # DIVERTED TO 101 # 1 2 3 4 5 6 7 #12345678901234567890123456789012345678901234567890123456789012345678901234 #SEQ DATE TIME DESTINATION NUMBER MIN/SEC INCL. GST #00001 09/11/07 15:08 DIVERTED TO 101 0:30 0.06 0.05 0.01 0.06 rectype = "CDR" btxt = substr($0,87,79) btxt_start = substr(btxt,1,3) if ( btxt_start == " " ) { # call class line class = callclass = trim(btxt); return } recclass = callclass = class; if ( btxt_start == "SEQ" ) { # header if ( substr(btxt,24,6) == "ORIGIN" ) rtype = 1 if ( substr(btxt,24,12) == "SERVICE TYPE" ) rtype = 2 if ( substr(btxt,34,12) == "ACCESS POINT" ) rtype = 3 if ( substr(btxt,24,11) == "DESTINATION" ) rtype = 4 return } #printf("Service Number = [%s] Call Class = [%s]\n", servicenumber, callclass); #printf("rtype = %d\n", rtype); #printf("%s\n", btxt); btxt_origin = "NOT SET" btxt_dialled = "NOT SET" dialstr = btxt_dialled btxt_rate = "NOT SET" btxt_duration = "NOT SET" if ( rtype == 1 ) { if ( length(btxt) != 79 || substr(btxt,9,1) != "/" || substr(btxt,12,1) != "/" || substr(btxt,19,1) != ":" || substr(btxt,63,1) != ":" || substr(btxt,77,1) != ".") { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = billcode "~" billsection "~" transactiontype "~" linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() return } dd = 0 + substr(btxt,7,2) mm = 0 + substr(btxt,10,2) yy = 0 + substr(btxt,13,2) stimestr = substr(btxt,17,5) btxt_stime_str = substr(stimestr,1,2) substr(stimestr,4,2) "00" btxt_origin = trim(substr(btxt,24,13)) btxt_dialled = trim(substr(btxt,38,17)) dialstr = btxt_dialled ## use unprocessed NUMBER for dest description #btxt_destination = btxt_dialled # use callclass for description btxt_destination = callclass # process dialled number to remove inconsistencies sub(/MNET/, "", btxt_dialled) # remove MNET sub(/MN/, "", btxt_dialled) # remove MN sub(/C/, "", btxt_dialled) # remove C sub(/^\+61/, "0", btxt_dialled) # replace with 0 if ( length(btxt_dialled) >= 13 ) sub(/^0061/, "0", btxt_dialled) # replace with 0 if ( length(btxt_dialled) >= 11 ) sub(/^61/, "0", btxt_dialled) # replace with 0 btxt_rate = substr(btxt,57,1) btxt_duration = substr(btxt,59,7) ":00" durhh = 0 durmm = 0 + substr(btxt_duration,1,4) durss = 0 + substr(btxt_duration,6,2) dursecs = (durhh * 60 * 60) + (durmm * 60) + durss btxt_cost = substr(btxt,67,13) } if ( rtype == 2 ) { if ( length(btxt) != 79 || substr(btxt,9,1) != "/" || substr(btxt,12,1) != "/" || substr(btxt,19,1) != ":" || substr(btxt,77,1) != ".") { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = billcode "~" billsection "~" transactiontype "~" linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() return } dd = 0 + substr(btxt,7,2) mm = 0 + substr(btxt,10,2) yy = 0 + substr(btxt,13,2) stimestr = substr(btxt,17,5) btxt_stime_str = substr(stimestr,1,2) substr(stimestr,4,2) "00" btxt_servicetype = trim(substr(btxt,24,13)) btxt_qty = trim(substr(btxt,51,7)) btxt_units = trim(substr(btxt,59,7)) btxt_origin = btxt_servicetype btxt_dialled = "" dialstr = btxt_dialled btxt_destination = trim(substr(btxt,38,12)) btxt_rate = btxt_qty " " btxt_units btxt_duration = "00:00:00" durhh = 0 durmm = 0 durss = 0 dursecs = 0 btxt_cost = substr(btxt,67,13) } if ( rtype == 3 ) { if ( length(btxt) != 79 || substr(btxt,9,1) != "/" || substr(btxt,12,1) != "/" || substr(btxt,18,1) != ":" || substr(btxt,53,1) != ":" || substr(btxt,56,1) != ":" || substr(btxt,77,1) != ".") { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = billcode "~" billsection "~" transactiontype "~" linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() return } dd = 0 + substr(btxt,7,2) mm = 0 + substr(btxt,10,2) yy = 0 + substr(btxt,13,2) stimestr = substr(btxt,16,5) btxt_stime_str = substr(stimestr,1,2) substr(stimestr,4,2) "00" btxt_accesspoint = trim(substr(btxt,35,12)) xoff = length(btxt_accesspoint) - 4 if ( substr(btxt_accesspoint, xoff) == ".INTE" ) btxt_accesspoint = btxt_accesspoint "RNET" btxt_volume = trim(substr(btxt,60,7)) btxt_origin = trim(substr(btxt,22,12)) btxt_dialled = "" btxt_volume " KB" dialstr = btxt_dialled btxt_destination = btxt_accesspoint btxt_rate = trim(substr(btxt,49,1)) btxt_rate = btxt_rate " " btxt_volume btxt_duration = substr(btxt,51,8) durhh = 0 + substr(btxt_duration,1,2) durmm = 0 + substr(btxt_duration,4,2) durss = 0 + substr(btxt_duration,7,2) dursecs = (durhh * 60 * 60) + (durmm * 60) + durss btxt_cost = substr(btxt,67,13) } if ( rtype == 4 ) { if ( length(btxt) < 79 || substr(btxt,9,1) != "/" || substr(btxt,12,1) != "/" || substr(btxt,19,1) != ":" || substr(btxt,63,1) != ":" || substr(btxt,77,1) != ".") { ++conv_errors rectype = "ERROR" recclass = "CONVERROR" sdate_str = "" btxt_stime_str = "" btxt_origin = billcode "~" billsection "~" transactiontype "~" linetype btxt_destination = NR btxt_dialled = NF btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() return } dd = 0 + substr(btxt,7,2) mm = 0 + substr(btxt,10,2) yy = 0 + substr(btxt,13,2) stimestr = substr(btxt,17,5) btxt_stime_str = substr(stimestr,1,2) substr(stimestr,4,2) "00" btxt_origin = trim(substr(btxt,24,13)) btxt_dialled = trim(substr(btxt,38,17)) dialstr = btxt_dialled ## use unprocessed NUMBER for dest description #btxt_destination = btxt_dialled # use callclass for description btxt_destination = callclass # process dialled number to remove inconsistencies sub(/MNET/, "", btxt_dialled) # remove MNET sub(/MN/, "", btxt_dialled) # remove MN sub(/C/, "", btxt_dialled) # remove C sub(/^\+61/, "0", btxt_dialled) # replace with 0 if ( length(btxt_dialled) >= 13 ) sub(/^0061/, "0", btxt_dialled) # replace with 0 if ( length(btxt_dialled) >= 11 ) sub(/^61/, "0", btxt_dialled) # replace with 0 btxt_rate = substr(btxt,57,1) btxt_duration = substr(btxt,59,7) ":00" durhh = 0 durmm = 0 + substr(btxt_duration,1,4) durss = 0 + substr(btxt_duration,6,2) dursecs = (durhh * 60 * 60) + (durmm * 60) + durss btxt_cost = substr(btxt,67,13) } # see whats in fields #print transactiontype | "sort -u >tt.uniq" #print dialstr | "sort -u >btxt_dialled.uniq" #print substr(dialstr,1,6) | "sort -u >btxt_dialled_6.uniq" #print btxt_origin | "sort -u >btxt_origin.uniq" #"date '+%Y'" | getline curyyyy #curCC = substr(curyyyy,1,2) #print "current yyyy = " curyyyy " CC = " curCC sdate_str = sprintf("%02d/%02d/20%02d", dd, mm, yy) #exclgst = substr($0,166,11) #inclgst = substr($0,189,11) costinclgst = 0.0 + btxt_cost #printf("%s: ", servicenumber) #printf("%s", btxt) #printf("%s", substr(btxt,1,68)) #printf(" BTXTCST:%s", btxt_cost) #printf(" EXCLGST:%s", exclgst) #printf(" INCLGST:%s", inclgst) #if ( btxt_cost != inclgst ) # printf(" COSTDIFF") #printf(" %0.2f", costinclgst) #printf("\n") # 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) # add cdr call count ++callcounttotal outrec() #printf("\n") } function finish_mobile() { if ( servicenumber == "" ) return #------------- # CALL_TOTAL (not including discounts) 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 = "" # get from CDR totals dursecs = callcounttotal # total call count costinclgst = calltotal # call total if ( costinclgst != 0 ) { outrec() } #################################### # call discount 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 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() } } function outrec() { if ( length(datasrcid) > 0 ) printf("%s|", datasrcid) printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%d|%0.2f|\n", servicenumber, carrier, rectype, recclass, billperiod, sdate_str, btxt_stime_str, btxt_origin, btxt_destination, btxt_dialled, btxt_rate, dursecs, costinclgst) } END { finish_mobile() } ' >$outfile test -f tt.uniq && mv -f tt.uniq tt_$1.uniq test -f btxt_dialled.uniq && mv -f btxt_dialled.uniq btxt_dialled_$1.uniq test -f btxt_dialled_6.uniq && mv -f btxt_dialled_6.uniq btxt_dialled_6_$1.uniq test -f btxt_origin.uniq && mv -f btxt_origin.uniq btxt_origin_$1.uniq exit 0