#! /bin/sh
# fxemaddr1 - set email addres in directory if blank
#	      for Yarra TRams, Metro Trains, and V/LINE

tag="fxemaddr1"

pwd=`pwd`

UNLFILE="${tag}.unl"
SQLFILE="${tag}.sql"
LOGFILE="${tag}.log"

rm -f "${UNLFILE}"
rm -f "${SQLFILE}"
rm -f "${LOGFILE}"

runisql <<SQLCMD0
unload to "${PWD}/${UNLFILE}"
--# V/LINE PASSENGER
select directcustom.recordno, directcustom.surname, directcustom.firstname,
 "vline.com.au"
from directcustom
where (directcustom.email is NULL OR directcustom.email = " ")
  and directcustom.surname not matches ".*"
--#  and (division in
  and recordno in (select directtemplate.recordno
	 from directtemplate, gengroup
	 where directtemplate.extgroupunique = gengroup.childgroupid
		and parentgroupid in
	    ("VXP", "VLP", "VLI", "VPA", "VTC", "VNA", "VNS", "VNI", "VLC")
	)
UNION
--# Yarra Trams
select directcustom.recordno,directcustom.surname, directcustom.firstname,
 "yarratrams.com.au"
from directcustom
where (directcustom.email is NULL OR directcustom.email = " ")
  and directcustom.surname not matches ".*"
--#  and (division in
  and recordno in (select directtemplate.recordno
	 from directtemplate, gengroup
	 where directtemplate.extgroupunique = gengroup.childgroupid
		and parentgroupid in
	    ("KDR", "KDM", "KDC")
	)
UNION
--# Metro Trains
select directcustom.recordno, directcustom.surname, directcustom.firstname,
    "metrotrains.com.au"
from directcustom
where (directcustom.email is NULL OR directcustom.email = " ")
  and directcustom.surname not matches ".*"
--#  and (division in
  and recordno in (select directtemplate.recordno
	 from directtemplate, gengroup
	 where directtemplate.extgroupunique = gengroup.childgroupid
		and parentgroupid in
	    ("ZOO", "ZCG", "ZCZ", "ZPV", "ZCV", "ZXA", "ZXB")
	)
;
SQLCMD0

awk -F '|' -v"UNLFILE=$INFILE" -v"SQLFILE=$SQLFILE" -v"LOGFILE=$LOGILE" '
#---------------------------------------------
function trim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	return s
}

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

function rmspace(s)
{
	gsub(/[ \t]*/,"",s)
	return s
}

#---------------------------------------------
# main
{
	print "------------------------------------------"
	print "\$0:[" $0 "]"

	#if ( NR == 1 ){
	#	print "SKIP header"
	#	next
	#}

	f=0
	dc_recordno = trim($(++f))
	dc_surname = trim($(++f))
	dc_firstname = trim($(++f))
	domainname = trim($(++f))

	emailaddr = rmspace(tolower(dc_surname "." dc_firstname "@" domainname))
	print "emailaddr=[" emailaddr "]"

	sql = ""
	sql = sql "update directcustom"
	sql = sql " set email = \"" emailaddr "\""
	sql = sql "   , directcustom.jobno = \"RSEM \" || directcustom.jobno"
	sql = sql "   , directcustom.date_updated = TODAY"
	sql = sql " where"
	sql = sql " (directcustom.email is NULL OR directcustom.email = \" \")"
	sql = sql "   and directcustom.recordno = " dc_recordno

	print "sql=[" sql "]"
	print sql ";" > SQLFILE
	print ""
}
' <"${UNLFILE}" >"${LOGFILE}"

exit 0