: !/bin/sh carrier="VODAFONE" month="$1" infile="$2" outfile="$3" datasrcid="$4" #adjmonth=$month #adjmonth=`../../../adjmonth $month` #echo "$0: adjmonth=[$adjmonth]" mm=`echo "$adjmonth" | awk '{ print substr($1,1,2) }'` cc=`echo "$adjmonth" | awk '{ print substr($1,3,2) }'` yy=`echo "$adjmonth" | awk '{ print substr($1,5,2) }'` #echo "$0: mm=[$mm] cc=[$cc] yy=[$yy]" #infile=`ls *${mm}-${yy}-${cc}*.txt` 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 if (linetype == "130000" ) { if ( NF != 13 ) { ++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($13) outrec() next } rectype = "CHARGE_DESC_INFO" recclass = "TOTAL" datestr = trim($5) 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($6) 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) sdate_str = to_date btxt_stime_str = "" btxt_origin = trim($3) # account number btxt_destination = "Total Cost" btxt_dialled = from_date " to " to_date # save total_from_to = from_date " to " to_date btxt_rate = trim($4) # invoice number dursecs = 0 costinclgst = 0.0 + trim($13) # total cost outrec() next } # store cost centre, name if (linetype == "333505" ) { if ( NF != 6 && NF != 7 && NF != 8 && NF != 9 ) { ++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 = "CONFIG" recclass = "COSTCENTRE" servicenumber = trim($3) sdate_str = "" btxt_stime_str = "" cc = trim($7) " " trim($8) # cost centre btxt_origin = cc btxt_destination = trim($4) # name btxt_dialled = "" btxt_rate = "" dursecs = 0 costinclgst = 0.0 outrec() next } # store name, account number, invoice number #if ( linetype == "263500" ) { if ( linetype == "260500" ) { if ( NF != 7 ) { ++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 calltotal = 0.0 call_discount = 0.0 calltotal_with_discount = 0.0 internet = 0 internet_kbytes = 0 internet_charges = 0.0 rectype = "CONFIG" recclass = "NAME" servicenumber = trim($5) sdate_str = "" btxt_stime_str = "" btxt_origin = trim($3) # account number btxt_destination = trim($6) # name btxt_dialled = "" btxt_rate = trim($4) # invoice number dursecs = 0 costinclgst = 0.0 outrec() next } ################################################## # rent/call/other (originally based on TAS vodafone.pas code) # rent if (linetype == "363005" ) { if ( NF != 7 ) { ++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" recclass = "RENT" datestr = trim($3) 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) sdate_str = to_date btxt_stime_str = "" btxt_origin = "" btxt_destination = trim($5) # description btxt_dialled = from_date " to " to_date btxt_rate = "" dursecs = 0 costinclgst = 0.0 + trim($6) test_internet() if ( !internet ) { # add up recurring chages recurring = recurring + costinclgst } # output rent if not 0 if ( costinclgst != 0 ) { outrec() next } next } ################# # rent if (linetype == "361505" ) { if ( NF != 6 && NF != 7 ) { ++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" recclass = "RENT" f = 3 datestr = trim($(f++)) 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) if ( NF == 7 ) { datestr = trim($(f++)) 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) } sdate_str = from_date btxt_stime_str = "" btxt_origin = "" btxt_destination = trim($(f++)) # description #btxt_dialled = " to " to_date if ( NF == 6 ) btxt_dialled = total_from_to else btxt_dialled = from_date " to " to_date btxt_rate = "" dursecs = 0 costinclgst = 0.0 + trim($(f++)) test_internet() # output rent if not 0 if ( costinclgst != 0 ) { outrec() next } next } ############### # rent/call/other if (linetype == "361005" ) { 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) ## output rent if not 0 #if ( costinclgst != 0 && # (linecode == "STDCHG" || linecode == "CREDIT") ) { # recclass = "RENT" # outrec() # next #} # output call total (incl. discount) if not 0 if ( costinclgst != 0 && (linecode == "USAGE") ) { recclass = "CALL" # includes discount btxt_destination = btxt_destination " (incl. discount)" # store cost for adjustment calc. later calltotal_with_discount = 0.0 + trim($5) outrec() next } # output other if not 0 if ( costinclgst != 0 && (linecode == "NSTDCH") ) { recclass = "OTHER" outrec() next } next } ################# # recurring_discount if (linetype == "261599" ) { 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 if ( linetype == "363505" ) { if ( NF != 15 && NF != 16 ) { ++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" recclass = callclass = trim($4) # Bill Data btxt = $0 #printf("Service Number = [%s] Call Class = [%s]\n", servicenumber, callclass); #printf("%s\n", btxt); datestr = trim($5) yyyy = 0 + substr(datestr,1,4) mm = 0 + substr(datestr,5,2) dd = 0 + substr(datestr,7,2) #printf("yyyy=[%s]\n", yyyy); #printf("dd=[%s] mm=[%s]\n", dd, mm); sdate_str = sprintf("%02d/%02d/%04d", dd, mm, yyyy) stimestr = trim($6) btxt_stime_str = substr(stimestr,1,2) substr(stimestr,3,2) substr(stimestr,5,2) btxt_origin = "" #trim($7) btxt_destination = trim($9) btxt_dialled = trim($8) # Internet ? kbytes = 0 if ( recclass == "PKT" ) { internet = 1 match(btxt_dialled,/[0-9]+[kK][bB]/) if ( RSTART > 0 ) kbytes = 0 + substr(btxt_dialled,RSTART,RLENGTH - 2) #print "Internet: kbytes = " kbytes " dialled=[" btxt_dialled "]" " 0:" $0 internet_kbytes += kbytes } btxt_duration = trim($11) #printf("btxt_duration=[%s]\n", btxt_duration); #durhhh = 0 + substr(btxt_duration,1,3) #durmm = 0 + substr(btxt_duration,4,2) #durss = 0 + substr(btxt_duration,6,2) #printf("durhhh=[%s] durmm[%s] durss[%s]\n", durhhh, durmm, durss); #dursecs = (durhhh * 60 * 60) + (durmm * 60) + durss dursecs = 0 + substr(btxt_duration,1,7) #printf("dursecs=[%s]\n", dursecs); # see whats in fields #print callclass | "sort -u >callclass.uniq" #print btxt_dialled | "sort -u >btxt_dialled.uniq" #print btxt_duration | "sort -u >btxt_duration.uniq" ### use unprocessed NUMBER for dest description ###btxt_destination = btxt_dialled ## process dialled number to remove inconsistencies #sub(/YES$/, "", btxt_dialled) # remove YES 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 = trim($10) btxt_cost = trim($14) costinclgst = 0.0 + sprintf("%0.2f", 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) outrec() #printf("\n") next } ########################################## # call totals (not including discounts) if (linetype == "263599" ) { if ( NF != 6 ) { ++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($6) # total call count costinclgst = 0.0 + trim($5) # total call cost # 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() } #################################### if ( internet ) { # output internet access rectype = "CHARGE_DESC_INFO" recclass = "INTERNET" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Internet Access " internet_kbytes "Kb" btxt_dialled = total_from_to btxt_rate = internet_kbytes dursecs = 0 #costinclgst = 0.0 costinclgst = 0.0 + internet_charges 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.2f|\n", servicenumber,carrier, rectype, recclass, billperiod, sdate_str, btxt_stime_str, btxt_origin, btxt_destination, btxt_dialled, btxt_rate, dursecs, costinclgst) } function test_internet() { # Internet 363005 or 361505 recs? #"0013420","363005","20081005","20081104","Data 1GB ","+000000090.860","Y" #"0096847","363005","20081005","20081104","Data Bundle 5MB ","+000000013.590","Y" #"0082012","363005","20081005","20081104","Mobile Email 50 ","+000000045.410","Y" #"0082013","363005","20081005","20081104","Mobile Office $2 ","+000000001.810","Y" if ( match(btxt_destination,/Data [0-9]*GB/) || match(btxt_destination,/Data Bundle [0-9]*MB/) || match(btxt_destination,/Mobile Email/) ) { internet = 1 internet_charges += costinclgst #print "Internet: cost = " costinclgst " 0:" $0 #rectype = "CHARGE_DESC_INFO" #recclass = "INTERNET" #outrec() #rectype = "CHARGE_DESC" #recclass = "RENT" } } 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 test -f btxt_duration.uniq && mv -f btxt_duration.uniq btxt_duration_$1.uniq exit 0