# gett21ovh.awk ################################################ function dosql(sql) { print "dosql(", sql, ")" "echo $$" | getline uniqsqlid uniqsqlid += sqlcounter sqlcounter += 1 ##print "uniqsqlid = ", uniqsqlid tmpsqlout = "/tmp/sqlout" uniqsqlid ##print "tmpsqlout = ", tmpsqlout #sqlproc = "runisql 2>&1" sqlproc = "runisql 1>/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 DBG() { } ################################################ function get_ovhinfo() { # get days in period sql2 = "select (DATE('" edate "') - DATE('" sdate "') + 1) daysinperiod from ovhtype where rowid=1" sql2id = dosql( sql2 ); while ((getline aline < sql2id) > 0) { gsub(/\\\|/, ";",aline) split(aline, sql2arr, "|") daysinperiod = sql2arr[1] break } closesql(sql2id) DBG(); print "daysinperiod = " daysinperiod # get ovhtype info DBG(); print "" DBG(); print "Loading ovhtype info" sql3 = "select ovhtype.* from ovhtype" sql3id = dosql( sql3 ); while ((getline aline < sql3id) > 0) { gsub(/\\\|/, ";",aline) split(aline, sql3arr, "|") ovhtype = sql3arr[1] ovhtypedesc_arr[ovhtype] = sql3arr[2] ovhcodehead_arr[ovhtype] = sql3arr[3] ovhitemhead_arr[ovhtype] = sql3arr[4] ovhrefhead_arr[ovhtype] = sql3arr[5] ovhdeschead_arr[ovhtype] = sql3arr[6] ovhcostmethod_arr[ovhtype] = sql3arr[7] ovhdefreptype_arr[ovhtype] = sql3arr[8] DBG(); print "XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX" DBG(); print "ovhtype = [" ovhtype "]" DBG(); print "ovhtypedesc = [" ovhtypedesc_arr[ovhtype] "]" DBG(); print "ovhcodehead = [" ovhcodehead_arr[ovhtype] "]" DBG(); print "ovhitemhead = [" ovhitemhead_arr[ovhtype] "]" DBG(); print "ovhrefhead = [" ovhrefhead_arr[ovhtype] "]" DBG(); print "ovhdeschead = [" ovhdeschead_arr[ovhtype] "]" DBG(); print "ovhcostmethod = [" ovhcostmethod_arr[ovhtype] "]" DBG(); print "ovhdefreptype = [" ovhdefreptype_arr[ovhtype] "]" DBG(); print "" } closesql(sql3id) # count number of configured overheads ovhovhcount="0" DBG(); print "" DBG(); print "Finding overheads count" sql4 = "select count(unique ovhdefn.ovhcode) ovhovhcount from ovhdefn where ovhdefn.ovhtype = '" ovhdotype "'" sql4id = dosql( sql4 ); while ((getline aline < sql4id) > 0) { gsub(/\\\|/, ";",aline) split(aline, sql4arr, "|") ovhovhcount = sql4arr[1] break } closesql(sql4id) DBG(); print "ovhovhcount = [" ovhovhcount "]" } function do_ovh(ovhdotype) { ovhtotal = 0.0 ovhaverage = 0.0 ovhtmpcost = 0.0 ovhgstperc = 0.0 ovhextcount = 0 ovhoutfile = outfile print "ovhoutfile =[" ovhoutfile "]" SITEBIT="ovhasso.siteid IS NOT NULL AND ovhasso.siteid = " siteid SDATEBIT="" SDATEBIT=SDATEBIT " and (ovhdefn.ovhtype != 'M' OR" SDATEBIT=SDATEBIT " (ovhdefn.ovhtype = 'M' AND" # rjs 3/10/2009 SDATEBIT=SDATEBIT " ovhasso.sdate >= '" prsdate "'" SDATEBIT=SDATEBIT " )" SDATEBIT=SDATEBIT " )" SDATEBIT=SDATEBIT " and NOT (ovhasso.edate IS NOT NULL AND" SDATEBIT=SDATEBIT " ovhasso.edate < '" prsdate "'" SDATEBIT=SDATEBIT " )" SDATEBIT=SDATEBIT " and (dasso != 'Y' OR ovhcostmethod != 'F' OR" SDATEBIT=SDATEBIT " (dasso = 'Y' AND ovhcostmethod = 'F' AND" SDATEBIT=SDATEBIT " ovhasso.sdate <= '" prsdate "'" SDATEBIT=SDATEBIT " )" SDATEBIT=SDATEBIT " )" EDATEBIT= " and ovhasso.sdate <= '" predate "'" EDATEBIT=EDATEBIT " and (dasso != 'Y' OR ovhcostmethod != 'F' OR" EDATEBIT=EDATEBIT " (dasso = 'Y' AND ovhcostmethod = 'F' AND" EDATEBIT=EDATEBIT " ((ovhasso.edate IS NOT NULL AND" EDATEBIT=EDATEBIT " ovhasso.edate >= '" predate "'" EDATEBIT=EDATEBIT " ) OR" EDATEBIT=EDATEBIT " ovhasso.edate IS NULL OR" EDATEBIT=EDATEBIT " ovhasso.edate = ' '" EDATEBIT=EDATEBIT " )" EDATEBIT=EDATEBIT " )" EDATEBIT=EDATEBIT " )" EXTNGRIDBIT="ovhasso.groupid IS NOT NULL AND ovhasso.groupid = '" egrid "'" EXTNBIT="ovhasso.extension IS NOT NULL AND ovhasso.extension = " extn DBG(); print "Proc ovhead" DBG(); print " ovhdotype = " ovhdotype DBG(); print " prsdate = " prsdate DBG(); print " predate = " predate DBG(); print " siteid = " siteid DBG(); print " ggrid = " ggrid DBG(); print " egrid = " egrid DBG(); print " extn = " extn # count number of associated overheads extn_ovhcount = 0 print "INIT spit_last_ovh" spit_last_ovh = "0" last_assoextension = "" last_dasso = "" last_ovhcode = "" last_ovhitem = "" last_ovhreference = "" last_ovhdescription = "" last_ovhorder = "" last_assosdate = "" last_assoedate = "" last_ovhtmpcost = "" # init dasso count ovhdassocount = 0 s= "" s=s "SELECT ovhasso.*, ovhdefn.*, ovhcost.*" s=s ", (ovhasso.sdate - DATE('" prsdate "'))" " sresval" s=s ", (ovhasso.edate - DATE('" predate "'))" " eresval" s=s " FROM ovhasso, ovhdefn, ovhtype, ovhcost" s=s " WHERE ovhdefn.ovhtype = '" ovhdotype "'" s=s " and ovhasso.ovhcode = ovhdefn.ovhcode" #s=s " and ovhasso.ovhcode != 'VIRTUAL_EXTN'" s=s " and ovhtype.ovhtype = ovhdefn.ovhtype " SDATEBIT " " EDATEBIT s=s " AND" s=s " ( (ovhasso.siteid IS NULL" s=s " AND ovhasso.groupid IS NULL" s=s " AND ovhasso.extension is NULL)" s=s " OR ( " SITEBIT s=s " AND ovhasso.groupid IS NULL AND ovhasso.extension is NULL" s=s " )" s=s " OR ( " SITEBIT s=s " AND " EXTNGRIDBIT s=s " AND ovhasso.extension is NULL" s=s " )" s=s " OR ( " SITEBIT s=s " AND " EXTNGRIDBIT s=s " AND " EXTNBIT s=s " )" s=s " )" s=s " AND ovhcost.ovhcode = ovhdefn.ovhcode" s=s " AND ovhcost.costsdate = " s=s " (SELECT MAX(ovhcost.costsdate) FROM ovhcost" s=s " WHERE ovhcost.costsdate <= '" predate "'" s=s " AND ovhcost.ovhcode = ovhdefn.ovhcode" s=s " )" #rjs s=s " ORDER BY ovhasso.dasso, ovhdefn.ovhorder, ovhasso.sdate" s=s " ORDER BY ovhasso.ovhcode desc, ovhasso.dasso, ovhasso.sdate, ovhdefn.ovhorder" #print "s=[" s "]" DBG(); print "" sql6 = s sql6id = dosql( sql6 ); while ((getline aline < sql6id) > 0) { print "aline=[" aline "]" gsub(/\\\|/, ";",aline) split(aline, sql6arr, "|") # from ovhasso #recno = sql6arr[1] assositeid = sql6arr[2] assogroupid = sql6arr[3] assoextension = sql6arr[4] dasso = sql6arr[5] ovhcode = sql6arr[6] assosdate = sql6arr[7] assoedate = sql6arr[8] # from ovhdefn #ovhtype = sql6arr[9] #ovhcode = sql6arr[10] ovhitem = sql6arr[11] ovhreference = sql6arr[12] ovhdescription = sql6arr[13] ovhorder = sql6arr[14] # from ovhcost #ovhcode = sql6arr[15] ovhcost = sql6arr[16] costsdate = sql6arr[17] DBG(); print "dasso=[" dasso "]" DBG(); print "assositeid=[" assositeid "]" DBG(); print "assogroupid=[" assogroupid "]" DBG(); print "assoextension=[" assoextension "]" sresval = sql6arr[18] DBG(); print "sresval=[ " sresval "]" eresval = sql6arr[19] DBG(); print "eresval=[ " eresval "]" if ( spit_last_ovh == 0 || last_ggrid != ggrid || last_egrid != egrid || last_extn != extn || last_ovhcode != ovhcode ) { extn_ovhcount = 0 print "zero extn_ovhcount" } #assign cost from table #ovhtmpcost=`echo "$ovhcost" | tr -d '\$'` ovhtmpcost = ovhcost DBG(); print "ovhtmpcost=[ " ovhtmpcost "]" if ( dasso == "Y" ) { DBG(); print "dasso=[" dasso "]" # looking at whole group if ( extn == "" ) { # is it for one extension or all extn's in group UT if ( assoextension == "" || assoextension == "0" ) ovhdassocount = ovhdassocount + ovhextcount else ovhdassocount = ovhdassocount + 1 } else { ovhdassocount = ovhdassocount + 1 } DBG(); print "ovhdassocount=[" ovhdassocount "]" ovhtmpcost = -1 * ovhtmpcost DBG(); print "ovhtmpcost=[" ovhtmpcost "]" } DBG(); print "acts sresval = " sresval if ( sresval > 0 ) actsdate = assosdate else actsdate = prsdate DBG(); print "actsdate = " actsdate # test if asso end date is within period if ( assoedate != "" ) { DBG(); print "acte eresval = " eresval if ( eresval < 0 ) actedate = assoedate else actedate = predate } else { actedate = predate } DBG(); print "actedate = " actedate # Calculate cost - # For E & O - cost in table is Per Extension Per Annum # if costmethod is (F)ixed # then cost is calculated for 1 month (ie. / 12) # and a fraction based on actual days in month # # if costmethod is (V)ariable # then cost is pro-rata over report period # # For M - cost in Table is absolute and does not apply # per extension nor monthly or pro-rata over report period # if ( ovhdotype == "O" || ovhdotype == "E" ) { # note: cost in the table are per extension per annum if ( ovhcostmethod_arr[ovhdotype] == "F" ) { # fixed monthly cost ovhmonthcost = sprintf("%.2f", ovhtmpcost / 12) DBG(); print "F ovhmonthcost=[" ovhmonthcost "]" # fraction of fixed monthly cost sql7a = "select ((DATE('" actedate "') - DATE('" actsdate "') + 1)) cresval from ovhtype where rowid=1" DBG(); print "F sql7a=[" sql7a "]" sql7aid = dosql( sql7a ); while ((getline aline < sql7aid) > 0) { gsub(/\\\|/, ";",aline) split(aline, sql7aarr, "|") cresval = sql7aarr[1] break } closesql(sql7aid) actualdays = cresval DBG(); print "F daysinperiod=[" daysinperiod "]" DBG(); print " actualdays=[" actualdays "]" if ( actualdays == daysinperiod ) ovhtmpcost = ovhmonthcost else ovhtmpcost = sprintf("%.2f", ovhmonthcost * actualdays / daysinperiod) DBG(); print "F ovhtmpcost=[" ovhtmpcost "]" } if ( ovhcostmethod_arr[ovhdotype] == "V" ) { # variable cost set so pro-rata for the period sql7b = "select ( " ovhtmpcost " * (DATE('" actedate "') - DATE('" actsdate "') + 1) / 365) cresval from ovhtype where rowid=1" sql7bid = dosql( sql7b ); while ((getline aline < sql7bid) > 0) { gsub(/\\\|/, ";",aline) split(aline, sql7barr, "|") cresval = sql7barr[1] break } closesql(sql7bid) ovhtmpcost = cresval DBG(); print "V ovhtmpcost=[" ovhtmpcost "]" } # looking at whole group if ( extn == "" ) { # calculate cost per extension # If the extension number is not set in the asso table # then apply cost to all extension in the group # if ( assoextension == "" || assoextension == "0" ) { ovhtmpcost = sprintf("%.2f", ovhtmpcost * ovhextcount) DBG(); print "GRP ovhtmpcost=[" ovhtmpcost "]" } } } if ( ovhdotype == "G" ) { # gst calculated in Summary PRINT section below ovhgstperc = sprintf("%.2f", ovhgstperc + ovhtmpcost) ovhtmpcost = 0.0 DBG(); print "GST ovhgstperc=[" ovhgstperc "] ovhtmpcost=[" ovhtmpcost "]" } # round to 2 decimals #ovhtmpcost = int((ovhtmpcost * 100) + 0.5) / 100 ovhtmpcost = sprintf("%.2f", ovhtmpcost) # get rid of -0.00 if ( sprintf("%.2f", ovhtmpcost) == "-0.00" ) ovhtmpcost = "0.00" #DBG(); print "ovhtmpcost=[" ovhtmpcost "]" ovhtotal = sprintf("%.2f", ovhtotal + ovhtmpcost) #DBG(); print "SUB ovhtotal=[" ovhtotal "]" #DBG(); print assoextension "|" dasso "|" ovhcode "|" ovhitem "|" ovhreference "|" ovhdescription "|" assosdate "|" assoedate "|" ovhtmpcost "|" ovhtotal "|" virtualextn = 0 if ( last_ovhcode == "VIRTUAL_EXTN" ) { virtualextn = 1 spit_last_ovh = 0 } DBG(); print "virtualextn=[" virtualextn "]" #rjs # dasso #rjs if ( spit_last_ovh == 1 && last_ovhcode == ovhcode && dasso == "Y") { #rjs print "DASSO RESET spit_last_ovh" #rjs spit_last_ovh = 0 #rjs } if ( last_ovhcode == ovhcode && dasso == "Y") { DBG(); print "DASSO" if ( last_virtualextn ) { DBG(); print "DASSO - virtualextn" ovhcode = "VIRTUAL_EXTN" ovhitem = "Virtual Extension" ovhreference = "Virtual Extension" ovhdescription = "Virtual Extension" if ( last_actsdate == actsdate && last_actedate == actedate && last_ovhtmpcost == (-1 * ovhtmpcost) ) { ovhtmpcost = 0 spit_last_ovh = 0 } } else { DBG(); print "DASSO - NOT virtualextn" if ( ovhdotype == "O" ) { DBG(); print "DASSO - NOT virtualextn - O" ovhcode = "NO_OVERHEAD" ovhitem = "No Overhead" ovhreference = "No Overhead" ovhdescription = "No Overhead" } else { DBG(); print "DASSO - NOT virtualextn - NOT O" ovhcode = "NO_EQUIPMENT" ovhitem = "No Equipment" ovhreference = "No Equipment" ovhdescription = "No Equipment" } if ( last_actsdate == actsdate && last_actedate == actedate && last_ovhtmpcost == (-1 * ovhtmpcost) ) { DBG(); print "DASSO - NOT virtualextn - NO SPIT" ovhtmpcost = 0 spit_last_ovh = 0 } } } #rjs if ( spit_last_ovh == "1" && last_dasso != "Y" ) { if ( spit_last_ovh == "1" ) { print "OUTREC LOOP" out_extn = extn dospit = 1 if ( ovhdotype == "M" ) { if ( last_assoextension == "" ) { DBG(); print "OUTREC LOOP - MISC and blank assoextension" out_extn = "ALL" } if ( done_global_asso[last_ovhcode] == 1 ) { print "OUTREC LOOP - Misc already spat" dospit = 0 } else { done_global_asso[last_ovhcode] = 1 print "OUTREC LOOP - spit Misc" } } if ( dospit ) { #rjs outrec(ggrid, egrid, out_extn, siteid, ovhdotype, last_ovhorder, last_ovhcode, last_ovhitem, last_ovhreference, last_ovhdescription, last_assosdate, last_assoedate, last_ovhtmpcost) outrec(ggrid, egrid, out_extn, siteid, ovhdotype, last_ovhorder, last_ovhcode, last_ovhitem, last_ovhreference, last_ovhdescription, last_actsdate, last_actedate, last_ovhtmpcost) } ++extn_ovhcount } #rjs if ( dasso != "Y" ) { #rjs print "SET spit_last_ovh" #rjs spit_last_ovh = 1 #rjs } spit_last_ovh = 1 last_virtualextn = virtualextn last_assoextension = assoextension last_dasso = dasso last_ovhcode = ovhcode last_ovhitem = ovhitem last_ovhreference = ovhreference last_ovhdescription = ovhdescription last_ovhorder = ovhorder last_assosdate = assosdate last_assoedate = assoedate last_actsdate = actsdate last_actedate = actedate last_ovhtmpcost = ovhtmpcost } closesql(sql6id) # spit last #rjs if ( spit_last_ovh == "1" && last_dasso != "Y" ) { if ( spit_last_ovh == "1" ) { print "OUTREC LAST" out_extn = extn dospit = 1 if ( ovhdotype == "M" ) { if ( last_assoextension == "" ) { DBG(); print "OUTREC LAST - MISC and blank assoextension" out_extn = "ALL" } if ( done_global_asso[last_ovhcode] == 1 ) { print "OUTREC LAST - Misc already spat" dospit = 0 } else { done_global_asso[last_ovhcode] = 1 print "OUTREC LAST - spit Misc" } } if ( dospit ) { #rjs outrec(ggrid, egrid, out_extn, siteid, ovhdotype, last_ovhorder, last_ovhcode, last_ovhitem, last_ovhreference, last_ovhdescription, last_assosdate, last_assoedate, last_ovhtmpcost) outrec(ggrid, egrid, out_extn, siteid, ovhdotype, last_ovhorder, last_ovhcode, last_ovhitem, last_ovhreference, last_ovhdescription, last_actsdate, last_actedate, last_ovhtmpcost) } ++extn_ovhcount } # spit NO overhead if ( ovhdotype == "O" && extn_ovhcount == 0 ) { print "OUTREC O NONE" outrec(ggrid, egrid, extn, siteid, ovhdotype, "0", "NO_OVERHEAD", "NO_OVERHEAD", "NO_OVERHEAD", "NO_OVERHEAD", "01/01/2000", "", "0") } # spit NO equipment if ( ovhdotype == "E" && extn_ovhcount == 0 ) { print "OUTREC E NONE" outrec(ggrid, egrid, extn, siteid, ovhdotype, "0", "NO_EQUIPMENT", "NO_EQUIPMENT", "NO_EQUIPMENT", "NO_EQUIPMENT", "01/01/2000", "", "0") } last_ggrid = ggrid last_siteid = siteid last_egrid = egrid last_extn = extn ############################## DBG(); print "OVH TOTAL ovhtotal=[" ovhtotal "]" # looking at whole group if ( extn == "" ) { if ( ovhextcount > 0 ) { ovhaverage = sprintf("%.2f", ovhtotal / ovhextcount) } DBG(); print " AVERAGE " ovhaverage " per extn" } ovhgrandtotal = sprintf("%.2f", ovhgrandtotal + ovhtotal) DBG(); print "GRAND SUB ovhgrandtotal=[" ovhgrandtotal "]" if ( ovhdotype != "G" ) { DBG(); print "SUMMARY " ovhtypedesc_arr[ovhdotype] " " ovhextcount " " ovhtotal } if ( ovhdotype == "G" && ovhgstperc != 0 ) { DBG(); print "GST Before " ovhgrandtotal ovhtotal = sprintf("%.2f", ovhgrandtotal * ovhgstperc / 100) ovhgrandtotal = sprintf("%.2f", ovhgrandtotal + ovhtotal) DBG(); print "GST INC. " ovhgrandtotal } # round to 2 decimals #ovhtotal = int((ovhtotal * 100) + 0.5) / 100 ovhtotal = sprintf("%.2f", total) # get rid of -0.00 if ( sprintf("%.2f", ovhtotal) == "-0.00" ) ovhtotal = "0.00" return ovhtotal } function outrec(ggrid, egrid, extn, siteid, ovhtype, ovhorder, ovhcode, ovhitem, ovhreference, ovhdescription, assosdate, assoedate, ovhcost) { ostr1 = sprintf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s", ggrid, egrid, extn, siteid, ovhtype, ovhorder, ovhcode, ovhitem, ovhreference, ovhdescription, assosdate, assoedate, ovhcost) gsub(",", ";", ostr1) print ostr1 print ostr1 >>ovhoutfile } ################################################ BEGIN { ovhgrandtotal = 0.0 print "outfile =[" outfile "]" get_ovhinfo(ovhdotype) last_ggrid = "" last_egrid = "" last_extn = "" last_siteid = "" } { f = 0 ggrid = $(++f) siteid = $(++f) egrid = $(++f) extn = $(++f) prsdate = $(++f) predate = $(++f) # for global M only the first extension in the input for each group is # processed so the relevant date range for that extn # should bew ignored, and the sdate/edate for the month used. if ( ovhdotype == "M" ) { prsdate = sdate predate = edate } # get costs tot_gcost = do_ovh(ovhdotype) }