#! /bin/sh
# fxtelno3 - fix telephone in Directory v3

# head UpdateExtn2Directory.csv
#Extn,Telephone,Hidden,CorrectTelephone
#10001 ,0396190001 ,,0396190001
#10002 ,0396190002 ,,0396190002
#10003 ,0396190003 ,,0396190003
#10004 ,0396190004 ,,0396190004
#10005 ,0396190005 ,,0396190005

INFILE="UpdateExtn2Directory.csv"

#OVERRIDETELEPHONE="0"
OVERIDETELEPHONE="1"

rm -f fxtelno3_$INFILE.log
rm -f fxtelno3_$INFILE.sql

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

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

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

	if ( NR == 1 ){
		print "SKIP header"
		next
	}
	f=0
	Extn = trim($(++f))
	oldTelephone = trim($(++f))
	Hidden = trim($(++f))
	CorrectTelephone = trim($(++f))

	print "Extn=[" Extn "]" "  CorrectTelephone=[" CorrectTelephone "]"

	#if ( length(CorrectTelephone) != 10 ) {
	#	print "SKIP CorrectTelephone=[" orrectTelephone
	#	next
	#}
		
	extn = Extn
	telephone = CorrectTelephone


	if ( telephone != "" ) {

		sql = ""
		sql = sql "update directcustom"
		sql = sql " set directcustom.telephone = \"" telephone "\""
		sql = sql " where directcustom.recordno in ("
		sql = sql "       select directtemplate.recordno"
		sql = sql "       from directtemplate"
		sql = sql "       where directtemplate.extension = " extn ""
		sql = sql "	)"
		if ( OVERIDETELEPHONE != "1" ) {
			sql = sql "  and ("
			sql = sql "        directcustom.telephone is NULL"
			sql = sql "     or directcustom.telephone = \" \""
			sql = sql "  )"
		}

		print "sql=[" sql "]"

		outfile = INFILE ".sql"
		print sql ";" > outfile

		print ""
	}
}
' < "$INFILE" >fxtelno3_${INFILE}.log

exit 0