#! /bin/sh
# fxtelno - fix telephone in Directory

# head telno.csv
#PREFIX,FIRST_NUM,LAST_NUM,DB,EXCHANGE,LIST,PH,MDF,EQTYPE,TYPE_30D,ACTIVE,MAX_LENS,PABX,PWORD
#DYL,10000,10199,55,STH DYNON LOCO,03-96199000,10199,DS12,2400 2400 2400,30DTB,1,,10198,

#INFILE="telno_16122014.csv"
INFILE="telno_fx.csv"

#OVERRIDETELEPHONE="0"
OVERIDETELEPHONE="1"

rm -f fxtelno_$INFILE.log
rm -f fxtelno_$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
	PREFIX = trim($(++f))
	FIRST_NUM = trim($(++f))
	LAST_NUM = trim($(++f))
	DB = trim($(++f))
	EXCHANGE = trim($(++f))
	LIST = trim($(++f))
	PH = trim($(++f))
	MDF = trim($(++f))
	EQTYPE = trim($(++f))
	TYPE_30D = trim($(++f))
	ACTIVE = trim($(++f))
	MAX_LENS = trim($(++f))
	PABX = trim($(++f))
	PWORD = trim($(++f))

	
	#if ( PREFIX == "EST" ) 
	#	extlen = 3
	#else
	#	extlen = 5	

	extlen = length(FIRST_NUM)
	print "extlen=" extlen

	if ( extlen != 5 && extlen != 3 ) {
		print "ERROR: skip extlen=" extlen " is not 5 or 3"
		next
	}

	if ( extlen == 3 ) {
		print "EXTLEN3"
	}


	print "PEFIX=[" PREFIX "]" "  FIRST_NUM=[" FIRST_NUM "]" "  LAST_NUM=[" LAST_NUM "]" "  LIST=[" LIST "]" "  extlen=[" extlen "]"


	if ( PREFIX == "TST" ) {
		print "Skip [" PREFIX "]"
		next
	}


	loextn = 0 + FIRST_NUM
	hiextn = 0 + LAST_NUM

	#LIST eg. 03-96199000
	if ( length(LIST) != 11 && substr(LIST, 2,1) != "-" ) {
		print "SKIP LIST=[" LIST "]"
		next
	}
		
	for ( extn = loextn; extn <= hiextn; ++extn ) {
		if ( extn < 10 ) {
			print "SKIP extn below 10"
			next
		}
		if ( extlen == 3 ) {
			extpost = "" extn
			telephone = substr(LIST,1,2) substr(LIST,4,5) extpost
		}
		else {	# assume extn 5
			extpost = substr("" + extn, 2,4)
			telephone = substr(LIST,1,2) substr(LIST,4,4) extpost
		}
		print "update extn=[" extn "]" "  extpost=[" extpost "]" "  telephone [" 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" >fxtelno_${INFILE}.log

exit 0