#! /bin/sh
# fxemaddr2 - swap surname.firstname in email address
#             (surname.firstname is wrong - see fxemaddr1)
#	      for entries with Jobno begining with "RSEM "

tag="fxemaddr2"

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}"
select directcustom.recordno, directcustom.surname, directcustom.firstname,
 "vline.com.au"
from directcustom
where 1=1
  and directcustom.surname not matches ".*"
  and directcustom.jobno matches "RSEM *"
;
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_firstname "." dc_surname "@" domainname))
	print "emailaddr=[" emailaddr "]"

	sql = ""
	sql = sql "update directcustom"
	sql = sql " set email = \"" emailaddr "\""
	sql = sql "   , directcustom.jobno[4,4] = \"2\""
	sql = sql "   , directcustom.date_updated = TODAY"
	sql = sql " where 1=1"
	sql = sql "   and directcustom.recordno = " dc_recordno

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

exit 0