: !/bin/sh # doOPTUSINSIGHTCDR_mobile_phones # /* # ************************************************************************* # ** * # ** Copyright (C) 1992-2010 Trans-Mit Pty. Ltd. Melbourne Australia * # ** This software may not be loaned, resold, changed or copied * # ** in any way without the express written permission of author * # ** and Trans-Mit Pty. Ltd., Australia. * # ** * # ************************************************************************* # ** # ** $Header: /usr/local/lib/cake/RCS/rcshead,v 1.10 2004/01/07 02:42:19 root Exp $ # ** $Author: root $ # ** $Date: 2004/01/07 02:42:19 $ # ** $Locker: $ # ** # ************************************************************************* # */ 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" sed -e 's/"//g;s/,/|/g;s/ $//;s/"$//' "$infile" | \ awk -F '|' -v "billmonth=$month" -v "carrier=$carrier" \ -v "datasrcid=$datasrcid" ' #--------------------------------- function trim(s) { sub(/^[ \t]*/,"",s) sub(/[ \t]*$/,"",s) return s } ##################################################### # 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 bill_total_account_number = "" bill_total_invoice_number = "" bill_callcounttotal = 0 bill_total_cost = 0.0 #bill_total_to = sprintf("%02d/%02d/%04d", dd, mm, yyyy) bill_total_to = billperiod bill_total_from = "" total_from_to = bill_total_from " to " bill_total_to } { gsub(/\r/, "") # remove CR gsub(/\032/, "") # remove FF #print "NF=" NF #print "\$0=" $0 # OPTUS datafeed .bil cdr #"0000298","353505"," ","20080508","0924"," ","AVALON ","Biz Fleet ","61402150124 "," P"," N"," "," ","0000020.00","+000000002.790","PN "," "," "," "," " # OPTUS insight cdr #"9127480494","0400844851",13/02/2009,," "," "," ",,,"Y",13.620,"SMSMO","SMS Text Messaging","60 units @ 0.227"," " #"9127480494","0400844851",24/06/2009,"15:20:00"," ","Curtin Uni","Extra Volume","00:00:00",0.000,"Y",0.000,"U-DAV","Optus Wireless Connect"," ","P" f = 0 AccountNumber = trim($++f) ServiceNumber = trim($++f) CallDate = trim($++f) CallTime = trim($++f) NumberDialled = trim($++f) CallOrigin = trim($++f) CallDestination = trim($++f) CallDuration = trim($++f) VolumeMB = trim($++f) GSTFlag = trim($++f) CallCharge = trim($++f) CallType = trim($++f) ProductName = trim($++f) ServiceDescription = trim($++f) RateFlag = trim($++f) Hierarchy_CostCentre = trim($++f) Hierarchy_UserName = trim($++f) linetype = "1" if ( linetype == "1" ) { if ( NF < 15 ) { ++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 = CallType servicenumber = ServiceNumber init_servicenumber(servicenumber) # Bill Data btxt = $0 #printf("Service Number = [%s] Call Class = [%s]\n", servicenumber, callclass); #printf("%s\n", btxt); datestr = CallDate # dd/mm/yyyy yyyy = 0 + substr(datestr,7,4) mm = 0 + substr(datestr,4,2) dd = 0 + substr(datestr,1,2) #printf("dd=[%s] mm=[%s]\n", dd, mm); sdate_str = sprintf("%02d/%02d/%04d", dd, mm, yyyy) stimestr = CallTime # HH:MM:SS btxt_stime_str = substr(stimestr,1,2) substr(stimestr,4,2) substr(stimestr,7,2) btxt_origin = CallOrigin # rjs 11/1/10 SMSMO dialno is in destination if ( callclass == "SMSMO" ) { has_SMSMO_CDR[servicenumber] = 1 s1 = CallDestination CallDestination = NumberDialled NumberDialled = s1 } btxt_destination = CallDestination if ( btxt_destination != "" && ProductName != "" ) btxt_destination = btxt_destination " " btxt_destination = btxt_destination ProductName if ( btxt_destination != "" && ServiceDescription != "" ) btxt_destination = btxt_destination " " btxt_destination = btxt_destination ServiceDescription btxt_dialled = NumberDialled # see whats in fields print callclass | "sort -u >callclass.uniq" print btxt_dialled | "sort -u >btxt_dialled.uniq" #if ( length(btxt_dialled) >= 11 ) # sub(/^61/, "0", btxt_dialled) # replace with 0 if ( btxt_dialled == "**INCOMING**" ) btxt_dialled = "--INCOMING--" if ( btxt_dialled == "" ) btxt_dialled = callclass # rjs 14/1/10 class in desc if ( callclass != "" ) { if ( btxt_destination != "" ) btxt_destination = btxt_destination " " btxt_destination = btxt_destination callclass } btxt_rate = RateFlag recclass = callclass btxt_duration = CallDuration # HH:MM:SS split(btxt_duration, dur_arr, ":") durhh = 0 + dur_arr[1] durmm = 0 + dur_arr[2] durss = 0 + dur_arr[3] dursecs = (60 * 60 * durhh) + (60 * durmm) + durss #printf("durhh=[%s] durmm=[%s] durss=[%s] dursecs[%s]\n", durhh, durmmmm, durss, dursecs); btxt_cost = CallCharge costinclgst = 0.0 + sprintf("%0.5f", 0.0 + btxt_cost) # Internet ? internet = 0 kbytes = 0 if ( VolumeMB != "" ) kbytes = int(((0 + VolumeMB ) * 1000) + 0.5) #print "Internet?: VolumeMB=[" VolumeMB "]" #if ( kbytes > 0 || (callclass == "U-DAE" || if ( kbytes > 0 || ( callclass == "U-DAV" || callclass == "U-MDF")) { internet = 1 internet_arr[servicenumber] = 1 if ( btxt_dialled != "" ) btxt_dialled = btxt_dialled " " btxt_dialled = btxt_dialled "Data " kbytes "KB" #print "Internet: kbytes=" kbytes " dialledno=[" btxt_dialled "]" } if ( internet == 1 ) { # total internet for mobile ++internet_count_arr[servicenumber] internet_kbytes_arr[servicenumber] += kbytes internet_charges_arr[servicenumber] += costinclgst } else { # rjs 15/1/10 SMSMO create separate F-SMS CHARGE_DESC summary if ( callclass == "SMSMO" ) { FSMS_cost[servicenumber] += costinclgst # add up call charges for info total if ( costinclgst >= 0 ) { # cost is not negative info_calltotal_arr[servicenumber] += costinclgst } else { # cost is negative info_call_discount_arr[servicenumber] += costinclgst } } else { # call total not incl. discount must be calculated if ( costinclgst >= 0 ) { # cost is not negative calltotal_arr[servicenumber] += costinclgst } else { # cost is negative call_discount_arr[servicenumber] += costinclgst } } # add cdr call count ++callcounttotal_arr[servicenumber] } outrec() #printf("\n") next } } #--------------------------------- function init_servicenumber(servicenumber) { if ( servicenumber_arr[servicenumber] == 1 ) return servicenumber_arr[servicenumber] = 1 # init totals callcounttotal_arr[servicenumber] = 0 renttotal_arr[servicenumber] = 0.0 rent_discount_arr[servicenumber] = 0.0 calltotal_arr[servicenumber] = 0.0 call_discount_arr[servicenumber] = 0.0 othertotal_arr[servicenumber] = 0.0 other_discount_arr[servicenumber] = 0.0 internet_arr[servicenumber] = 0 internet_count_arr[servicenumber] = 0 internet_kbytes_arr[servicenumber] = 0 internet_charges_arr[servicenumber] = 0.0 has_SMSMO_CDR[servicenumber] = 0 FSMS_cost[servicenumber] = 0 } #--------------------------------- function finish_mobile(servicenumber) { if ( servicenumber == "" ) return #------------- # 15/1/10 create F-SMS CHARGE_DESC summary rectype = "CHARGE_DESC" linecode = "F-SMS" desc = linecode sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = desc # description btxt_dialled = total_from_to btxt_rate = "" dursecs = 0 costinclgst = 0.0 + FSMS_cost[servicenumber] # store calltype code for recalc module btxt_origin = linecode recclass = "CALL" if ( costinclgst != 0 ) { outrec() } #------------- # calculate and output CHARGE_DESC call total rectype = "CHARGE_DESC" recclass = "CALL" callcounttotal = callcounttotal_arr[servicenumber] calltotal = calltotal_arr[servicenumber] # rjs 26/3/2010 back to separate internet #callcounttotal += internet_count_arr[servicenumber] #calltotal += internet_charges_arr[servicenumber] sdate_str = "" btxt_stime_str = "" btxt_origin = "CDR" # mark this so recalc module can skip btxt_destination = "Total Call Charges (not incl. discount/internet)" btxt_dialled = total_from_to btxt_rate = "" # get from CDR totals dursecs = callcounttotal # total call count costinclgst = calltotal # already set from detail recs #if ( costinclgst >= 0 ) { # cost is not negative # calltotal_arr[servicenumber] += costinclgst #} #else { # cost is negative # call_discount_arr[servicenumber] += costinclgst #} # call total if ( costinclgst != 0 ) { outrec() } # rjs 26/3/2010 back to separate internet ## add to call totals above #------------ # internet CALL/DATA charges if ( internet_arr[servicenumber] == 1 ) { # output internet access rectype = "CHARGE_DESC" recclass = "CALL" sdate_str = "" btxt_stime_str = "" btxt_origin = "INTERNET" # mark this so recalc module can skip btxt_destination = "Internet Data " internet_kbytes_arr[servicenumber] "Kb" btxt_dialled = total_from_to btxt_rate = internet_kbytes_arr[servicenumber] dursecs = internet_count_arr[servicenumber] # total count costinclgst = 0.0 + internet_charges_arr[servicenumber] outrec() } #------------- # CALL_TOTAL (not including discounts) rectype = "CHARGE_DESC_INFO" recclass = "CALL_TOTAL" callcounttotal = callcounttotal_arr[servicenumber] calltotal = calltotal_arr[servicenumber] calltotal += info_calltotal_arr[servicenumber] 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" call_discount = call_discount_arr[servicenumber] call_discount += info_call_discount_arr[servicenumber] 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" recurring_discount = rent_discount_arr[servicenumber] 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" call_discount = call_discount_arr[servicenumber] 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" recurring = renttotal_arr[servicenumber] 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() } #################################### # output internet access if ( internet_arr[servicenumber] == 1 ) { # output internet access rectype = "CHARGE_DESC_INFO" recclass = "INTERNET" sdate_str = "" btxt_stime_str = "" btxt_origin = "" btxt_destination = "Internet Access " internet_kbytes_arr[servicenumber] "Kb" btxt_dialled = total_from_to btxt_rate = internet_kbytes_arr[servicenumber] dursecs = internet_count_arr[servicenumber] # total count #costinclgst = 0.0 costinclgst = 0.0 + internet_charges_arr[servicenumber] outrec() } #################################### # do carrier totals bill_callcounttotal += callcounttotal_arr[servicenumber] bill_total_cost += renttotal_arr[servicenumber] + rent_discount_arr[servicenumber] + calltotal_arr[servicenumber] + call_discount_arr[servicenumber] + othertotal_arr[servicenumber] + other_discount_arr[servicenumber] bill_total_cost += info_calltotal_arr[servicenumber] + info_call_discount_arr[servicenumber] } function outrec() { if ( length(datasrcid) > 0 ) printf("%s|", datasrcid) printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%d|%0.4f|\n", servicenumber, carrier, rectype, recclass, billperiod, sdate_str, btxt_stime_str, btxt_origin, btxt_destination, btxt_dialled, btxt_rate, dursecs, costinclgst) } END { for ( servicenumber in servicenumber_arr ) { finish_mobile(servicenumber) } #------------------------------------ # Bill Total # total cost / account number / invoice number servicenumber = "ALL" rectype = "CHARGE_DESC_INFO" recclass = "TOTAL" sdate_str = bill_total_to btxt_stime_str = "" btxt_origin = bill_total_account_number btxt_destination = "Total Cost" btxt_dialled = total_from_to btxt_rate = bill_total_invoice_number dursecs = bill_callcounttotal costinclgst = 0.0 + bill_total_cost outrec() 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