: !/bin/sh

rm -f gdir1.csv
rm -f gdir1.upd


(
txsql -d catcom -F'|' <<SQLCMD0
select dc.surname, dc.firstname, dc.email
from directtemplate dt, directcustom dc
where dt.recordno = dc.recordno
  and ( dt.extension != 0 or (dc.mobile is not null and dc.mobile != " ") )
order by 1,2,3
SQLCMD0
) | awk -F'|' '

function trim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	return s
}

function ld_tPerson(tPersonfile) {
	while ( (getline aline < tPersonfile) > 0 ) {
		split(aline, a_arr, "|")
		#printf("aline=%s\n", aline)
		f = 0
		PersonID = trim(a_arr[++f])
		Person = toupper(trim(a_arr[++f]))
		
		Person_to_ID_arr[Person] = PersonID
		print "ld_tPerson() Person_to_ID_arr[" Person "]=[" Person_to_ID_arr[Person] "]"
	}
	close(tPersonfile)
}

BEGIN{
	ld_tPerson("tPerson.unl")

	# hard coded from robyn
	name_to_email["ANDREW SANTANA"] = "andrew.santana@victrack.com.au"
	name_to_email["RUSSELL CRUSE"] = "russell.cruse@victrack.com.au"
	name_to_email["LEO FELICISSIMO"] = "leo.felicissimo@victrack.com.au"
	name_to_email["KATHRYN HORE"] = "kathryn.hore@victrack.com.au"
	name_to_email["ALLEN JESUDASAN"] = "allen.jesudasan@victrack.com.au"
	name_to_email["SHEETAL SHAH"] = "sheetal.shah@victrack.com.au"
	name_to_email["JAMIE WOOD"] = "jamie.wood@victrack.com.au"
	name_to_email["PHIL BOYD"] = "phil.boyd@victrack.com.au"
	name_to_email["BEN NEEDHAM"] = "ben.needham@victrack.com.au"
	name_to_email["MIKE BLACKBURN"] = "mike.blackburn@victrack.com.au"
	name_to_email["JOHN MARSHALL"] = "john.marshall@victrack.com.au"
	name_to_email["JAYNE RADBONE"] = "jayne.radbone@victrack.com.au"
	name_to_email["FRANK MITCHELL"] = "frank.mitchell@victrack.com.au"
	name_to_email["VINCE SCOTTO"] = "vincent.scotto@victrack.com.au"
}

function enquote(s)
{
	return "\047" s "\047"
}

{
	surname = $1
	firstname = $2
	email = $3

	if ( surname == ".ASSIGNED" )
		next
	if ( surname == ".UNPROGRAMMED" )
		next
	if ( surname == ".SILENT" )
		next
	if ( surname == ".VACANT" )
		next

	name = firstname
	if ( name != "" && surname != "" )
		name = name " "
	name = name surname

	if ( name == "" )
		next

	PersonID = Person_to_ID_arr[name]

	if ( name_to_email[name] != "" )
		email = name_to_email[name]

	if ( PersonID != "" ) {
		print "Exists in tPerson [" name "]"
		print "# Update existing [" name "]" > "gdir1.upd"
		print "update tPerson set emailaddress = " enquote(email) " where tPerson.ID = " PersonID ";" > "gdir1.upd"
		next
	}


	print name "," email | "sort -u >gdir1.csv"
}

END {
	# manually fixed entries not in Telmax21 Directory

	name = "FRANK MITCHELL"
	PersonID  = "14713"
	email = name_to_email[name]
	print "update tPerson set emailaddress = " enquote(email) " where tPerson.ID = " PersonID ";" > "gdir1.upd"

	name = "VINCE SCOTTO"
	PersonID = "5642"
	email = name_to_email[name]
	print "update tPerson set emailaddress = " enquote(email) " where tPerson.ID = " PersonID ";" > "gdir1.upd"
}

' >gdir1.log