# 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)
}