: /bin/sh ######################################################################## ## batch file for Generating a Time Pricing Configuration Report # T21BATCHNAME tpcnf # T21BATCHDESC Time Pricing Configuration Report # ALLOWBLANKDATES true # CUSTOMDESC # CUSTOMDEF # T21BATCHVAR1DESC List of Carrier Codes (or ALL) # T21BATCHVAR1VALU ALL # T21BATCHVAR2DESC List of Call Category Codes [LSIMEOF] (or ALL) # T21BATCHVAR2VALU ALL # T21BATCHVAR3DESC Show ONLY Differences with Last run # T21BATCHVAR3VALU N # T21BATCHVAR4DESC # T21BATCHVAR4VALU # T21BATCHVAR5DESC # T21BATCHVAR5VALU # T21BATCHENDARGS ######################################################################## progname=$0 SDATE=$1; EDATE=$2; STIME=$3; ETIME=$4 if [ -n "$5" ]; then SITEID=$5 fi if [ -n "$6" ]; then CARRIER=$6 else CARRIER="" fi if [ -n "$7" ]; then CALLCAT=$7 else CALLCAT="" fi # handle envvars - override command line if [ -n "$T21BATCHVAR1VALU" ]; then CARRIER=$T21BATCHVAR1VALU fi if [ -n "$T21BATCHVAR2VALU" ]; then CALLCAT=$T21BATCHVAR2VALU fi if [ -n "$T21BATCHVAR3VALU" ]; then diffwithlast=$T21BATCHVAR3VALU fi ################################################################# tmpfile=/tmp/tpcnf_$$ awk0tmpfile=/tmp/awk0tmp.$$ tmpfile1=/tmp/tmp1tpcnf_$$ tmpfile2=/tmp/tmp2tpcnf_$$ tmpfile3=/tmp/tmp3tpcnf_$$ ##-------------------------------- if [ -z "$SITEID" ]; then echo "Enter siteid (Enter for ALL):\c"; read SITEID fi if [ "$SITEID" = "" ]; then SITEID="ALL" fi if [ "$SITEID" = "ALL" ]; then SITEBIT="" else SITEBIT="and siteid = $SITEID" 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 "$CARRIER" ]; then echo "Enter Carrier (ENTER for ALL):\c"; read CARRIER fi if [ "$CARRIER" = "" ]; then CARRIER="ALL" fi if [ -z "$CALLCAT" ]; then echo "Enter Call Category (ENTER for ALL):\c"; read CALLCAT fi if [ "$CALLCAT" = "" ]; then CALLCAT="ALL" fi if [ -z "$diffwithlast" ]; then echo "Enter Only show differences with Last run (ENTER for N):\c"; read diffwithlast fi if [ "$diffwithlast" = "y" ]; then diffwithlast="Y" fi if [ "$diffwithlast" = "n" ]; then diffwithlast="N" fi if [ "$diffwithlast" != "Y" -a "$diffwithlast" != "N" ]; then diffwithlast="N" fi ##--------------------------- ## send file to user exec 1>$tmpfile 2>&1 ##---------------------------------------------------------------- cat >$awk0tmpfile </dev/null 2>&1" printf( "unload to %s %s;", tmpsqlout, sql) | sqlproc close(sqlproc) return tmpsqlout } function closesql(sqlf) { ##print "closesql(", sqlf, ")" close(sqlf) system("rm -f " sqlf) } ################################################ ################################################ ################################################ ################################################ ################################################ function dobands() { ##printf(" dobands:\n"); sql5id = dosql( "select unique up.dayno, up.endtimband, (up.acost / up.aperiod * 60) cpermin from unitpulse up where up.chcode = '" chcode "' and up.pulsetype = '" carrier "' and up.aperiod > 0 union select unique up.dayno, up.endtimband, 0 cpermin from unitpulse up where up.chcode = '" chcode "' and up.pulsetype = '" carrier "' and up.aperiod <= 0 order by 1, 2, 3"); while ((getline aline < sql5id) > 0) { split(aline, sql5arr, "|"); dayno = sql5arr[1]; etimband = sql5arr[2]; cpermin = sql5arr[3]; uparr[ dayno etimband ] = rateid[cpermin]; } closesql(sql5id) ##for ( i in uparr ) ## printf( "uparr[ %s ] = %s\n", i, uparr[i] ); daydesc[0] = "Sun"; daydesc[1] = "Mon"; daydesc[2] = "Tue"; daydesc[3] = "Wed"; daydesc[4] = "Thu"; daydesc[5] = "Fri"; daydesc[6] = "Sat"; sql6id = dosql( "select unique up.dayno from unitpulse up where up.chcode = '" chcode "' and up.pulsetype = '" carrier "' order by 1"); while ((getline aline < sql6id) > 0) { split(aline, sql6arr, "|"); daynoarr[sql6arr[1]] = 1; } closesql(sql6id) ##for ( i in daynoarr ) ## printf( "daynoarr[ %s ] = %d\n", daydesc[i], daynoarr[i] ); sql7id = dosql( "select unique up.endtimband from unitpulse up where up.chcode = '" chcode "' and up.pulsetype = '" carrier "' order by 1"); ntimes = 0; while ((getline aline < sql7id) > 0) { split(aline, sql7arr, "|"); etimarr[++ntimes] = sql7arr[1]; } closesql(sql7id) ##for ( i in etimarr ) ## printf( "etimarr[ %s ] = %s\n", i, etimarr[i] ); ########## printf("\n"); printf(" EndTime "); for ( i=0; i <= 6; ++i ) { if ( daynoarr[i] == 1 ) printf( " %3.3s ", daydesc[i] ); else printf( " %3.3s ", "XXX" ); } printf("\n"); for ( et=1; et <= ntimes; ++et ) { printf(" %s ", etimarr[et]); for ( dn=0; dn <= 6; ++dn ) { printf( " %3.3s ", uparr[ dn etimarr[et] ]); } printf("\n"); } } function docost(iperiod,icost,aperiod,acost,flagfall,mincost,discount,cpermin) { ##printf(" docost: %s %s %s %s %s %s %s >> %f cents/min %s <<\n", iperiod, icost, aperiod, acost, flagfall, mincost, discount, cpermin, rateid[cpermin]); printf(" %10.10s %10.10s %10.10s %10.10s %10.10s %10.10s %10.10s >>>> %f <<<< %7.7s\n", iperiod, icost, aperiod, acost, flagfall, mincost, discount, cpermin, rateid[cpermin]); } function ratestr(r) { ratearr[1] = "aaa"; ratearr[2] = "bbb"; ratearr[3] = "ccc"; ratearr[4] = "ddd"; ratearr[5] = "eee"; ratearr[6] = "fff"; ratearr[7] = "ggg"; ratearr[8] = "hhh"; ratearr[9] = "iii"; if ( r >= 1 && r <= 9 ) trtstr = ratearr[r]; else trtstr = sprintf("%3.3s", r); return trtstr; } function dochcode( chcode, chcodedesc ) { ##printf("\n chcode: %s Desc: %s\n", chcode, chcodedesc); printf("\n\n"); printf("Category: %s %s", callcat, callcatdesc); printf(" "); printf("Calltype: %s %s", calltype, calltypedesc); printf(" "); printf("Chargecode: %s %s", chcode, chcodedesc); printf("\n\n"); sql8id = dosql( "select t.tarifftype, t.fidfac, t.surcharge from tariff t where t.calltype = '" calltype "' order by 1"); while ((getline aline < sql8id) > 0) { split(aline, sql8arr, "|"); tarifftype = sql8arr[1]; fidfac = sql8arr[2]; surcharge = sql8arr[3]; printf(" tariff = %s fidfac = %s surcharge = %s", tarifftype, fidfac, surcharge ); } closesql(sql8id) printf("\n\n"); printf(" %10.10s %10.10s %10.10s %10.10s %10.10s %10.10s %10.10s %14.14s %7.7s\n", "iperiod", "icost", "aperiod", "acost", "flagfall", "mincost", "discount", " RATE cents/min", "RateTag"); nrates = 0; sql4id = dosql( "select unique up.iperiod, up.icost, up.aperiod, up.acost, up.flagfall, up.mincost, up.discount, (up.acost / up.aperiod * 60) cpermin from unitpulse up where up.chcode = '" chcode "' and up.pulsetype = '" carrier "' and up.aperiod > 0 union select unique up.iperiod, up.icost, up.aperiod, up.acost, up.flagfall, up.mincost, up.discount, 0 cpermin from unitpulse up where up.chcode = '" chcode "' and up.pulsetype = '" carrier "' and up.aperiod <= 0 order by 8"); while ((getline aline < sql4id) > 0) { split(aline, sql4arr, "|"); iperiod = sql4arr[1]; icost = sql4arr[2]; aperiod = sql4arr[3]; acost = sql4arr[4]; flagfall = sql4arr[5]; mincost = sql4arr[6]; discount = sql4arr[7]; cpermin = sql4arr[8]; rateid[cpermin] = ratestr(++nrates); docost(iperiod,icost,aperiod,acost,flagfall,mincost,discount,cpermin); } closesql(sql4id) dobands(); } function docalltype( calltype, calltypedesc ) { ##printf("\n"); ##printf(" calltype: %s Desc: %s\n", calltype, calltypedesc); #sql3id = dosql( "select unique dp.chcode, '_' from dialled_prefix dp where dp.calltype = '" calltype "' and dp.pulsetype = '" carrier "' order by 1"); sql3id = dosql( "select unique dp.chcode, ccdesc.chcodedesc from dialled_prefix dp, outer ccdesc where dp.calltype = '" calltype "' and dp.pulsetype = '" carrier "' and dp.pulsetype = ccdesc.pulsetype and ccdesc.chcode = dp.chcode order by 1"); while ((getline aline < sql3id) > 0) { split(aline, sql3arr, "|"); chcode = sql3arr[1]; chcodedesc = sql3arr[2]; dochcode( chcode, chcodedesc ); } closesql(sql3id) } function docallcat( callcat, callcatdesc ) { ##printf("\n\n"); ##printf(" callcat: %s Desc: %s\n", callcat, callcatdesc); sql2id = dosql( "select unique ct.calltypecode, ct.calltypedesc from calltypedef ct, dialled_prefix dp where ct.calltypecode = dp.calltype and dp.pulsetype = '" carrier "' and ct.callcategory = '" callcat "' order by 1"); while ((getline aline < sql2id) > 0) { split(aline, sql2arr, "|"); calltype = sql2arr[1]; calltypedesc = sql2arr[2]; docalltype( calltype, calltypedesc ); } closesql(sql2id) } ############### BEGIN { "date" | getline the_datetime print "Telmax21 Time Price Configuration Summary - tpcnf Date: " the_datetime printf("Carrier: %s %s\n", carrier, carrierdesc) print "---------------------------------------------------------------------------------------------------------------------" if ( CALLCAT == "ALL" ) { CATBIT = "" } else { CATBIT = sprintf(" cc.callcategory MATCHES '[%s]' and ", CALLCAT); } sql1 = sprintf( "select unique cc.callcategory, cc.callcatdesc from callcategory cc, calltypedef ct, dialled_prefix dp where %s cc.callcategory = ct.callcategory and ct.calltypecode = dp.calltype and dp.pulsetype = '" carrier "' order by 1", CATBIT); ##printf("sql1=[%s]\n", sql1); sql1id = dosql( sql1 ); while ((getline aline < sql1id) > 0) { split(aline, sql1arr, "|"); callcat = sql1arr[1]; callcatdesc = sql1arr[2]; docallcat( callcat, callcatdesc ); } closesql(sql1id) exit } { } END { print "" print "" print "" print "From tariff table:" print "tarifftype = code assigned to extn groups to apply user based markups" print " fidfac = % increase/decrease (+/-) for tarifftype & calltype " print " surcharge = surcharge cost for tarifftype & calltype " print "" print "From unitpulse table:" print " iperiod = number of seconds in initial period" print " icost = cost for iperiod" print " aperiod = number of seconds in additional periods" print " acost = cost for aperiod in cents" print "flagfall = flag fall cost" print " mincost = minimum call cost" print "discount = percentage discount to be applied on unitpulse values" print "" print " RATE = icost / iperiod x 60" print "" print " all costs are in cents" } AWK0SCRIPT ##---------------------------------------------------------------- ##---------------------------------------------------------------- ##---------------------------------------------------------------- docarrier() { carrier=$1 carrierdesc=$2 ##echo "\n\ndocarrier(carrier=$carrier, carrierdesc=$carrierdesc)" if [ -z "$carrierdesc" ]; then carrierdesc=`\ echo "select \"CARRIER\", pulsetype, pulsedescription from carriers where pulsetype = \"$CARRIER\" order by pulsetype" | runisql 2>/dev/null | \ while read carrier_marker carrier carrierdesc do if [ "$carrier_marker" = "CARRIER" ]; then echo "Desc: $carrierdesc" fi done` fi awk -f $awk0tmpfile -v "carrier=$carrier" -v "carrierdesc=$carrierdesc" -v "CALLCAT=$CALLCAT" } ################################################################# ## MAIN ## do report(s) if [ "$CARRIER" = "ALL" ]; then ##echo "select unique \"CARRIER\", carriers.pulsetype, pulsedescription from carriers, dialled_prefix where carriers.pulsetype = dialled_prefix.pulsetype order by carriers.pulsetype" | runisql 2>/dev/null | \ echo "select \"CARRIER\", pulsetype, pulsedescription from carriers where 1=1 order by pulsetype" | runisql 2>/dev/null | \ while read carrier_marker carrier carrierdesc do if [ "$carrier_marker" = "CARRIER" ]; then docarrier "$carrier" "Desc: $carrierdesc" fi done else ## do report for a specific gen group docarrier "$CARRIER" "" fi rm -f $awk0tmpfile ################################################## # add left margin sed -e 's/^/ /' $tmpfile > $tmpfile1 cp $tmpfile1 $tmpfile rm -f $tmpfile1 ################################################## # handle diff with last if [ "$diffwithlast" = "Y" ]; then ( echo "Telmax21 Comparison of Time Price Configuration Summary - tpcnf Date: `date`" echo "---------------------------------------------------------------------------------------------------------------------" tpcmpdir="$HOME/tpcmp" tpcmpf1=${tpcmpdir}/tpcnf_${CARRIER}_1 tpcmpf2=${tpcmpdir}/tpcnf_${CARRIER}_2 # make cmp dir for user if [ ! -d $tpcmpdir ]; then mkdir $tpcmpdir fi # make dummy last if one doesnt exist if [ ! -f $tpcmpf2 ]; then touch $tpcmpf2 fi # copy last run over run from before last cp "$tpcmpf2" "$tpcmpf1" # save this run cp $tmpfile "$tpcmpf2" # remove left margin from each - before diff cut -c16- "$tpcmpf1" | grep -v "Telmax21 Time Price Configuration" >$tmpfile1 cut -c16- "$tpcmpf2" | grep -v "Telmax21 Time Price Configuration" >$tmpfile2 # show header from each file head -1 $tpcmpf1 | cut -c16- head -1 $tpcmpf1 | cut -c16- echo "---------------------------------------------------------------------------------------------------------------------" echo "" # compare last with this run and add left margin diff -C 5 $tmpfile1 $tmpfile2 >$tmpfile3 cat $tmpfile3 if [ ! -s $tmpfile3 ]; then echo "No Changes." fi ) 2>&1 | sed -e 's/^/ /' | pr_rep system tpcmp_ rm -f $tmpfile rm -f $tmpfile1 rm -f $tmpfile2 rm -f $tmpfile3 exit 0 fi ################################################## ## send file to user # Force mail to pass DATE as GMT TZ=''; export TZ if [ -s "$tmpfile" ]; then if [ -n "$TPCNF_EMAIL" ]; then ( echo "Telmax21 Report - Time Pricing Configuration Summary\c" echo " (tpcnf_$$)\c" echo " ..." # create report as uuencode file attatchment uuencode $tmpfile "tpcnf_$$.rtf" ) | mail -s "Time Pricing Configuration Summary" $TPCNF_EMAIL else cat $tmpfile | pr_rep system tpcnf_ fi fi rm -f $tmpfile rm -f $tmpfile1 rm -f $tmpfile2 exit 0