# ucdevimoport.awk

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

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

#----------------------------------------------------------

function start_database(dbname)
{
	"echo $$" | getline udbcmdid
	udbcmdid += dbcmdcounter
	dbcmdcounter += 1

	# if not gawk need...
	dbcmdidf[udbcmdid] = "/tmp/dbcmdidf" udbcmdid
	##print "dbcmdidf[" udbcmdid "] = " dbcmdidf[udbcmdid]
	#system("rm -f " dbcmdidf[udbcmdid] "; touch " dbcmdidf[udbcmdid])
	system("rm -f " dbcmdidf[udbcmdid] "; mknod " dbcmdidf[udbcmdid] " p")
	##print "Done: clean " dbcmdidf[udbcmdid]
	#...

	dbcmd[udbcmdid] = "txsql -s"
	if ( dbname != "" )
		dbcmd[udbcmdid] = dbcmd[udbcmdid] " -d " dbname
	# if not gawk...
	dbcmd[udbcmdid] = dbcmd[udbcmdid] " 2>&1 1>>" dbcmdidf[udbcmdid]
	#dbcmd[udbcmdid] = dbcmd[udbcmdid] " -o " dbcmdidf[udbcmdid]
	#...
	##dbcmd[udbcmdid] = "cat"
	#print "dbcmd[" udbcmdid "] = " dbcmd[udbcmdid]

	## gawk only
	#print "" |& dbcmd[udbcmdid]
	#fflush(dbcmd[udbcmdid])
	##dbcmd[udbcmdid] |& getline response
	##print "response = [" response "]"
	# or if not gawk...
	print "" | dbcmd[udbcmdid]

	return udbcmdid
}


function close_database(dbid)
{
	if ( dbcmd[dbid] != "" ) {
		#print "close_database: dbid = " dbid
		close(dbcmd[dbid])
		#print dbcmd[dbid] " closed."
		delete dbcmd[dbid]
		# if not gawk...
		close(dbcmdidf[dbid])
		#print dbcmdidf[dbid] " closed."
		#system("cp " dbcmdidf[dbid] " 3a")
		system("rm -f " dbcmdidf[dbid])
		#print dbcmdidf[dbid] " removed."
		delete dbcmdidf[dbid]
		#...
		#print "close done."
	}
}


function stop_database(dbid)
{
	if ( dbcmd[dbid] == "" ) {	# stop all
		for ( dbid in dbcmd )
			close_database(dbid)
	}
	else {				# stop this one
		close_database(dbid)
	}
}


function execute_sql(dbid,sqlcmd,
	ainit, rowidx, aline, a_arr, f1, f2, msg_arr)
{
	if ( dbcmd[dbid] == "" ) {
		printf("ERROR: database not started.")
		return 1
	}
	sub(/\n$/,"",sqlcmd)
	sub(/\;$/,"",sqlcmd)
	#sub(/$/,";\n",sqlcmd)
	#print "execute_sql: print " sqlcmd "; to " dbcmd[dbid]

	# unique sql id
	++sqlid

	## gawk only
	#print sqlcmd ";" |& dbcmd[dbid]
	#fflush(dbcmd[dbid])
	#return dbcmd[dbid]
	# else if not gawk
	print sqlcmd ";" | dbcmd[dbid]

	#return dbcmdidf[dbid]

	# init array
	for ( ainit in result_arr ) {
		split(ainit,msg_arr,"|")
		if ( msg_arr[1] == sqlid )
			delete result_arr[ainit]
	}

	ainit = 1
	rowidx = 1

	result_arr[sqlid "|" "status"] = ""
	result_arr[sqlid "|" "sqlcode"] = 0
	result_arr[sqlid "|" "sqlerrmsg"] = ""
	result_arr[sqlid "|" "sqlerrd1"] = 0
	result_arr[sqlid "|" "sqlerrd2"] = 0
	result_arr[sqlid "|" "sqlerrd3"] = 0
	result_arr[sqlid "|" "sqlerrd4"] = 0
	result_arr[sqlid "|" "sqlerrd5"] = 0
	result_arr[sqlid "|" "sqlerrd6"] = 0
	result_arr[sqlid "|" "nrows"] = 0

	# gawk
	#while ( (dbcmd[dbid] |& getline aline) > 0 ) {
	# else
	while ( (getline aline <dbcmdidf[dbid]) > 0 ) {
		if ( ainit ) {	# first
			#print "Got first."
			ainit = 0
		}
		#print "aline = [" aline "]"
		split(aline,a_arr,"|")
		f = 0; f1 = a_arr[++f]; f2 = a_arr[++f]; f3 = a_arr[++f]
		if ( f1 == "Status:" ) {
			result_arr[sqlid "|" "status"] = f2
			split(f2,msg_arr,"~")
			sqlcode = int(msg_arr[2])
			errmsg = msg_arr[3]
			if ( msg_arr[1] == "SQLCODE" ) {
				result_arr[sqlid "|" "sqlcode"] = sqlcode
				result_arr[sqlid "|" "sqlerrmsg"] = errmsg
			}
			if ( msg_arr[4] == "SQLERRD" ) {
				result_arr[sqlid "|" "sqlerrd1"] = int(msg_arr[5])
				result_arr[sqlid "|" "sqlerrd2"] = int(msg_arr[6])
				result_arr[sqlid "|" "sqlerrd3"] = int(msg_arr[7])
				result_arr[sqlid "|" "sqlerrd4"] = int(msg_arr[8])
				result_arr[sqlid "|" "sqlerrd5"] = int(msg_arr[9])
				result_arr[sqlid "|" "sqlerrd6"] = int(msg_arr[10])
			}
			result_arr[sqlid "|" "nrows"] = int(f3)
			if ( sqlcode < 0 ) {
				print "SQLERROR: " f2
			}
			break
		}
		result_arr[sqlid "|" rowidx++] = aline
	}

	return sqlid
}


function my_exit(exit_status)
{
	stop_database()
	exit exit_statud
}

#---------------------------------------------------


function load_Bundle()
{
	sql0 = ""
	sql0 = sql0 "select unique directtemplate.extension, directtemplate.extgroupunique, gengroup.parentgroupid, groupname.grname1, groupname.grname2, gnextra.grname3"
	sql0 = sql0 " from directtemplate, groupname, gengroup, gnextra"
	sql0 = sql0 " where directtemplate.extgroupunique = groupname.grid"
	sql0 = sql0 "   and gengroup.childgroupid = groupname.grid"
	sql0 = sql0 "   and gnextra.grid  = groupname.grid"
	sql0 = sql0 "   and directtemplate.site = '" UC_SITE "'"
	sql0 = sql0 "   and directtemplate.extension > 0"
	sql0 = sql0 " order by directtemplate.extension"

	sql0id = execute_sql(dbid0,sql0)
	sql0_nrows = result_arr[sql0id "|" "nrows"]
	for ( i = 1; i <= sql0_nrows; ++i ) {
		sql0_row = result_arr[sql0id "|" i]
		split(sql0_row, sql0_arr, "|")
		f = 0
		extn = trim(sql0_arr[++f])
		extgrid = trim(sql0_arr[++f])
		pargrid = trim(sql0_arr[++f])
		grname1 = trim(sql0_arr[++f])
		grname3 = trim(sql0_arr[++f])
		grname3 = trim(sql0_arr[++f])

		#print "extn=[" extn "] extgrid=[" extgrid "] grname3=[" grname3 "]" "  sql0_row = [" sql0_row "]"
		#extension_to_row_arr[extn] = sql0_row
		extension_to_grname3_arr[extn] = grname3
	}
	print "load_Bundle() nrows = " sql0_nrows
	print ""
}


#---------------------------------------------------

function getPhoneType(DeviceType)
{
	PhoneType = ""
	sql1 = ""
	sql1 = sql1 "select PhoneType"
	sql1 = sql1 " from  PhoneType"
	sql1 = sql1 " where DeviceType = '" DeviceType "'"
	sql1 = sql1 "   and Active = 'Y'"

	print "getPhoneType() Searching PhoneType DeviceType=[" DeviceType "]"

	sql1id = execute_sql(dbid0,sql1)
	sql1_nrows = result_arr[sql1id "|" "nrows"]
	if ( sql1_nrows == 1 ) {
                sql1_row = result_arr[sql1id "|" 1]
                split(sql1_row, sql1_arr, "|")
                f = 0
                PhoneType = trim(sql1_arr[++f])
		print "FOUND: 1 Row. PhoneType=[" PhoneType "]"
	}
	if ( sql1_nrows == 0  )
		print "ERROR: NO Rows found."
	if ( sql1_nrows > 1  )
		print "ERROR: More than 1 Row found."
	return PhoneType
}


function getTEC(PhoneType,Bundle)
{
	tec = ""
	tecDesc = ""
	sql2 = ""
	sql2 = sql2 "select tec, tecDesc"
	sql2 = sql2 " from tec"
	sql2 = sql2 " where PhoneType = '" PhoneType "'"
	sql2 = sql2 "   and Bundle = '" Bundle "'"
	sql2 = sql2 "   and Active = 'Y'"

	print "getTEC() Searching tec for PhoneType=[" PhoneType "] Bundle=[" Bundle "]"

	sql2id = execute_sql(dbid0,sql2)
	sql2_nrows = result_arr[sql2id "|" "nrows"]
	if ( sql2_nrows == 1 ) {
                sql2_row = result_arr[sql2id "|" 1]
                split(sql2_row, sql2_arr, "|")
                f = 0
                tec = trim(sql2_arr[++f])
                tecDesc = trim(sql2_arr[++f])
		tec_to_tecDesc_arr[tec] = tecDesc
		print "FOUND: 1 Row. tec=[" tec "]"
	}
	if ( sql2_nrows == 0  )
		print "ERROR: No Rows found."
	if ( sql2_nrows > 1  )
		print "ERROR: More than 1 Row found."
	return tec
}


function getSFC(BasicPhone,VoiceMail,OnTheMove,ConnectedAndAccessible,Bundle)
{
	sfc = ""
	sfcDesc = ""
	sql3 = ""
	sql3 = sql3 "select sfc, sfcDesc"
	sql3 = sql3 " from sfc"
	sql3 = sql3 " where BasicPhone = '" BasicPhone "'"
	sql3 = sql3 "   and VoiceMail = '" VoiceMail "'"
	sql3 = sql3 "   and OnTheMove = '" OnTheMove "'"
	sql3 = sql3 "   and ConAndAccesible = '" ConnectedAndAccessible "'"
	sql3 = sql3 "   and Bundle = '" Bundle "'"
	sql3 = sql3 "   and Active = 'Y'"

	print "getSFC() Searching sfc for BasicPhone=[" BasicPhone "] VoiceMail=[" VoiceMail "] OnTheMove=[" OnTheMove "] ConAndAccesible=[" ConnectedAndAccessible "] Bundle=[" Bundle "]"

	sql3id = execute_sql(dbid0,sql3)
	sql3_nrows = result_arr[sql3id "|" "nrows"]
	if ( sql3_nrows == 1 ) {
                sql3_row = result_arr[sql3id "|" 1]
                split(sql3_row, sql3_arr, "|")
                f = 0
                sfc = trim(sql3_arr[++f])
                sfcDesc = trim(sql3_arr[++f])
		sfc_to_sfcDesc_arr[sfc] = sfcDesc
		print "FOUND: 1 Row. sfc=[" sfc "]"
	}
	if ( sql3_nrows == 0  )
		print "ERROR: No Rows found."
	if ( sql3_nrows > 1  )
		print "ERROR: More than 1 Row found."
	return sfc
}



function getRSC(srch_rscDesc)
{
	rsc = ""
	rsc = ""
	rscDesc = ""

	sql4 = ""
	sql4 = sql4 "select rsc, rscDesc"
	sql4 = sql4 " from rsc"
	sql4 = sql4 " where rscDesc MATCHES '" srch_rscDesc "'"
	sql4 = sql4 "   and Active = 'Y'"

	print "getRSC() Searching rsc for srch_rscDesc=[" srch_rscDesc "]"

	sql4id = execute_sql(dbid0,sql4)
	sql4_nrows = result_arr[sql4id "|" "nrows"]
	if ( sql4_nrows == 1 ) {
                sql4_row = result_arr[sql4id "|" 1]
                split(sql4_row, sql4_arr, "|")
                f = 0
                rsc = trim(sql4_arr[++f])
                rscDesc = trim(sql4_arr[++f])
                rsc_to_rscDesc_arr[rsc] = rscDesc
		print "FOUND: 1 Row. rsc=[" rsc "]"
	}
	if ( sql4_nrows == 0  )
		print "ERROR: No Rows found."
	if ( sql4_nrows > 1  )
		print "ERROR: More than 1 Row found."
	return rsc
}



#---------------------------------------------------
# from doccm.awk

function setup_extnalter()
{
	nextnalters= split(ccmextnalterlist, extnalter_arr, "|");
	if ( ccmloglevel >= 1 ) {
		printf("config - ccmextnalterlist= %s\n", ccmextnalterlist)
	}
	for ( i = 1; i <= nextnalters; ++ i ) {
		extnalter = extnalter_arr[i]
		if ( extnalter == "" )
			continue
		split(extnalter, extnalter_item_arr, ",");
		ndigits_before[i] = sprintf("%d", extnalter_item_arr[1])
		digits_prefix[i] = sprintf("%d", extnalter_item_arr[2])
		digits_start[i] = sprintf("%d", extnalter_item_arr[3])
		ndigits_after[i] = sprintf("%d", extnalter_item_arr[4])
		return_prefix[i] = sprintf("%s", extnalter_item_arr[5])
		return_postfix[i] = sprintf("%s", extnalter_item_arr[6])
		if ( ccmloglevel >= 1 ) {
			printf("config - extnalter = [%s]\n", extnalter)
			printf("                  ndigits_before[%d] = %d\n", i, ndigits_before[i])
			printf("                  digits_prefix[%d] = %d\n", i, digits_prefix[i])
			printf("                  digits_start[%d] = %d\n", i, digits_start[i])
			printf("                  ndigits_after[%d] = %d\n", i, ndigits_after[i])
			printf("                  return_prefix[%d] = %s\n", i, return_prefix[i])
			printf("                  return_postfix[%d] = %s\n", i, return_postfix[i])
		}
	}
}


function handle_partynumber(partynumberstr)
{
	###gsub("[#]","",partynumberstr)
	##gsub("[#*]","",partynumberstr)
	#gsub("[#*+]","",partynumberstr)
	gsub("[^0-9]","",partynumberstr)
	partynumber = partynumberstr
	if ( ccmloglevel >= 9 ) {
		printf("handle_partynumber(%s) partynumber=[%s]\n", partynumberstr, partynumber)
	}
	if ( (partynumberstr + 0) > 9999 ) {
		#fst2 = substr(partynumberstr,1,2)
		#fst3 = substr(partynumberstr,1,3)
		#if (length(partynumberstr) == 6) {
		#	if (fst3 == "382")
		#		partynumber = substr(partynumberstr,4,3)
		#	if (fst2 == "32" || fst2 == "33" || fst2 == "37")
		#		partynumber = substr(partynumberstr,3,4)
		#}
		#else {
		#	partynumber = 0
		#}

		# handle extn alter config list
		for ( i = 1; i <= nextnalters; ++ i ) {
			# yyyXXXzzz = passed party number string
			# ndigits_before[i] = number of ys Xs zs
			# digits_prefix[i] = yyy
			# digits_start[i] = offset to first z (starting from 1)
			# ndigits_after[i] = number of zs
			# return_prefix[i] = pp
			# return_postfix[i] = PP
			# ppzzzPP = partynumber to return
			if (length(partynumberstr) == ndigits_before[i]) {
				dpref = digits_prefix[i]
				if (substr(partynumberstr,1,length(dpref)) == dpref) {
					partynumber = return_prefix[i] substr(partynumberstr, digits_start[i], ndigits_after[i]) return_postfix[i]
					if ( ccmloglevel >= 9 ) {
						printf("extnalter: handle_partynumber(%s) partynumber = [%s]\n", partynumberstr, partynumber)
					}
				}
			}
		}
	}

	return partynumber
}

#----------------------------------------------------------
# main

BEGIN {
	dbid0 = start_database("catcom")
	#print "dbid0 = " dbid0

	UC_SITE = "UCP"

	fldexpected_arr["Directory Number 1"] = 1
	fldexpected_arr["User ID 1"] = 1
	fldexpected_arr["Description"] = 1
	fldexpected_arr["Device Type"] = 1
	fldexpected_arr["Device Name"] = 1
	fldexpected_arr["Voice Mail Profile 1"] = 1
	fldexpected_arr["Extension Mobility"] = 1
	fldexpected_arr["Line CSS 1"] = 1

	ccmloglevel = 0
	setup_extnalter()

	load_Bundle()
}

#----------------------------------------------------------
# process input

{
	#print "NF=" NF " : " $0

	# process header
	if ( NR == 1 ) {
		# store name for each field
		for ( fld = 1; fld <= NF; ++ fld ) {
			fldname = trim($fld)
			fldname_arr[fld] = fldname
			fldexists_arr[fldname] = 1
			#print "fldname_arr[" fld "] = [" fldname_arr[fld] "]"
		}
		maxfld = NF

		# check we have the required fields
		print ""
		for ( fldname in fldexpected_arr ) {
			printf("Check for fldname[" fldname "]... ")
			if ( fldexists_arr[fldname] != 1 ) {
				print "ERROR: missing input fldname=[" fldname "]"
				my_exit(1);
			}
			printf("OK\n")
		}
		print ""
		next
	}
	
	print "-----------------------------------"
	# process data record
	for ( fld = 1; fld <= maxfld; ++fld ) {
		fldname = fldname_arr[fld]
		fldvalue = trim($fld)
		fldvalue_arr[fldname] = fldvalue
		#if ( fld == 1 )
		#	print "NR=" NR " fldname_arr[" fld "] = [" fldname_arr[fld] "] val=["  fldvalue_arr[fldname_arr[fld]] "]"
	}

	DirectoryNumber = fldvalue_arr["Directory Number 1"]
	partynumber = handle_partynumber(DirectoryNumber)
	print " DirectoryNumber=[" DirectoryNumber "]" "  partynumber=[" partynumber "]"
	if ( DirectoryNumber == ""  ) {
		print "   skipping blank Directory Number"
		next
	}
	if ( have_pn_arr[partynumber] != 1 ) {
		have_pn_arr[partynumber] = 1
		print "    new partynumber=[" partynumber "]"
		# init pn arrays
		pn_UserID_arr[partynumber] = ""
		pn_Description[partynumber] = ""
		pn_DeviceType_arr_arr[partynumber] = ""
		pn_DeviceName_arr[partynumber] = ""
		pn_MACaddress_arr[partynumber] = ""
		pn_PhoneType_arr[partynumber] = ""
		pn_Bundle_arr[partynumber] = ""
		pn_tec_arr[partynumber] = "  "
		pn_BasicPhone_arr[partynumber] = "N"
		pn_VoiceMail_arr[partynumber] = "N"
		pn_OnTheMove_arr[partynumber] = "N"
		pn_ConnectedAndAccessible_arr[partynumber] = "N"
		pn_sfc_arr[partynumber] = "  "
		pn_Subscription_arr[partynumber] = ""
		pn_rsc_arr[partynumber] = "  "
		pn_rscDesc_arr[partynumber] = "  "
	}
	else {
		print "    have seen partunumber=[" partynumber "] before."
	}


	UserID = fldvalue_arr["User ID 1"]
	print "UserID=[" UserID "]"

	Description = fldvalue_arr["Description"]
	print "Description=[" Description "]"

	DeviceType = fldvalue_arr["Device Type"]
	print "DeviceType=[" DeviceType "]"

	DeviceName = fldvalue_arr["Device Name"]
	print "DeviceName=[" DeviceName "]"

	# lookup PhoneType from DeviceType
	PhoneType = getPhoneType(DeviceType)
	print "PhoneType=[" PhoneType "]"

	# get Bgridundle - From Hierarchy grname3
	grname3 = extension_to_grname3_arr[partynumber]
	if ( grname3 != "" )
		Bundle = grname3
	else
		Bundle = "5 year bundle"
	print "Bundle=[" Bundle "] for extension=[" partynumber "]"

	# lookup up TEC Code and Desc
	tec = getTEC(PhoneType,Bundle)
	tecDesc = tec_to_tecDesc_arr[tec]
	print "tec=[" tec "]  tecDesc=[" tecDesc "]"

	BasicPhone = "N"
	MACaddress = ""
	if ( match(DeviceName,"^SEP") > 0 ) {
		BasicPhone = "Y"
		MACaddress = substr(DeviceName,4,12)
	}
	print "BasicPhone=[" BasicPhone "]"
	print "MACaddress=[" MACaddress "]"

	VoiceMailProfile = fldvalue_arr["Voice Mail Profile 1"]
	VoiceMail = "N"
	if ( VoiceMailProfile == "Unity_Connection_Voicemail" ) {
		VoiceMail = "Y"
	}
	print "VoiceMailProfile=[" VoiceMailProfile "]"
	print "VoiceMail=[" VoiceMail "]"

	ExtensionMobility = fldvalue_arr["Extension Mobility"]
	print "ExtensionMobility=[" ExtensionMobility "]"
	OnTheMove = "N"
	if ( tolower(ExtensionMobility) == "t" ) {
		OnTheMove = "Y"
	}
	print "OnTheMove=[" OnTheMove "]"

	ConnectedAndAccessible = "N"
	if ( match(DeviceName,"^CSF|^IM|^TCT") > 0 ) {
		ConnectedAndAccessible = "Y"
	}
	print "ConnectedAndAccessible=[" ConnectedAndAccessible "]"

	# lookup SFC and Desc (Subscription)
	sfc = getSFC(BasicPhone,VoiceMail,OnTheMove,ConnectedAndAccessible,Bundle)
	sfcDesc = sfc_to_sfcDesc_arr[sfc]
	print "sfc=[" sfc "]  sfcDesc=[" sfcDesc "]"
	Subscription = sfcDesc
	print "Subscription=[" Subscription "]"

	# lookup up RSC Code
	srch_rscDesc = "*" fldvalue_arr["Line CSS 1"]
	rsc = getRSC(srch_rscDesc)
	rscDesc = rsc_to_rscDesc_arr[rsc]
	print "rsc=[" rsc "]  rscDesc=[" rscDesc "]"

	#-------------------------------

	# store info for each partynumber
	partynumber_arr[partynumber] = 1
	if ( UserID != "" ) {
		if ( pn_UserID_arr[partynumber] != "" && pn_UserID_arr[partynumber] != UserID ) {
			print "ERROR: partynumber=[" partynumber "]" " UserID conflict"
		}
		pn_UserID_arr[partynumber] = UserID
	}
	if ( Description != "" ) {
		if ( pn_Description_arr[partynumber] != "" ) {
			# add if not already there
			if ( index(pn_Description_arr[partynumber], Description) <= 0 ) {
				pn_Description_arr[partynumber] = pn_Description_arr[partynumber] "|"
				pn_Description_arr[partynumber] = pn_Description_arr[partynumber] Description
			}
		}
		else {
			pn_Description_arr[partynumber] = Description
		}
	}
	if ( DeviceType != "" ) {
		if ( pn_DeviceType_arr[partynumber] != "" ) {
			# add if not already there
			if ( index(pn_DeviceType_arr[partynumber], DeviceType) <= 0 ) {
				pn_DeviceType_arr[partynumber] = pn_DeviceType_arr[partynumber] "|"
				pn_DeviceType_arr[partynumber] = pn_DeviceType_arr[partynumber] DeviceType
			}
		}
		else {
			pn_DeviceType_arr[partynumber] = DeviceType
		}
	}
	if ( DeviceName != "" ) {
		pn_DeviceName_arr[partynumber] = DeviceName
	}
	if ( PhoneType != "" ) {
		pn_PhoneType_arr[partynumber] = PhoneType
	}
	if ( Bundle != "" ) {
		pn_Bundle_arr[partynumber] = Bundle
	}
	if ( tec != "" ) {
		pn_tec_arr[partynumber] = tec
	}
	if ( MACaddress != "" ) {
		if ( pn_MACaddress_arr[partynumber] != "" && pn_MACaddress_arr[partynumber] != MACaddress ) {
			print "ERROR: partynumber=[" partynumber "]" " MACaddress conflict"
		}
		pn_MACaddress_arr[partynumber] = MACaddress
	}
	if ( BasicPhone != "" ) {
		pn_BasicPhone_arr[partynumber] = BasicPhone
	}
	if ( VoiceMail != "" ) {
		pn_VoiceMail_arr[partynumber] = VoiceMail
	}
	if ( OnTheMove == 1 ) {
		pn_OnTheMove_arr[partynumber] = OnTheMove
	}
	if ( ConnectedAndAccessible == 1 ) {
		pn_ConnectedAndAccessible_arr[partynumber] = ConnectedAndAccessible
	}
	if ( sfc != "" ) {
		pn_sfc_arr[partynumber] = sfc
	}
	if ( Subscription != "" ) {
		pn_Subscription_arr[partynumber] = Subscription
	}
	if ( rsc != "" ) {
		pn_rsc_arr[partynumber] = rsc
	}
	if ( rscDesc != "" ) {
		pn_rscDesc_arr[partynumber] = rscDesc
	}
		
}


#-----------------------------------------------------------------------
END {
	print "END: ===================================================="
	for ( partynumber in partynumber_arr ) {
		UserID = pn_UserID_arr[partynumber]
		Description = pn_Description_arr[partynumber]
		DeviceType = pn_DeviceType_arr[partynumber]
		DeviceName = pn_DeviceName_arr[partynumber]
		MACaddress = pn_MACaddress_arr[partynumber]
		PhoneType = pn_PhoneType_arr[partynumber]
		Bundle = pn_Bundle_arr[partynumber]
		tec = pn_tec_arr[partynumber]
		BasicPhone = pn_BasicPhone_arr[partynumber]
		VoiceMail = pn_VoiceMail_arr[partynumber]
		OnTheMove = pn_OnTheMove_arr[partynumber]
		ConnectedAndAccessible = pn_ConnectedAndAccessible_arr[partynumber]
		sfc = pn_sfc_arr[partynumber]
		Subscription = pn_Subscription_arr[partynumber]
		rsc = pn_rsc_arr[partynumber]
		rscDesc = pn_rscDesc_arr[partynumber]

		print "partynumber = [" partynumber "]"
		print "                  UserID=[" UserID "]"
		print "             Description=[" Description "]"
		print "              DeviceType=[" DeviceType "]"
		print "              DeviceName=[" DeviceName "]"
		print "              MACaddress=[" MACaddress "]"
		print "               PhoneType=[" PhoneType "]"
		print "                  Bundle=[" Bundle "]"
		print "                     tec=[" tec "]"
		print "              BasicPhone=[" BasicPhone "]"
		print "               VoiceMail=[" VoiceMail "]"
		print "               OnTheMove=[" OnTheMove "]"
		print "  ConnectedAndAccessible=[" ConnectedAndAccessible "]"
		print "                     sfc=[" sfc "]"
		print "            Subscription=[" Subscription "]"
		print "                     rsc=[" rsc "]"
		print "                 rscDesc=[" rscDesc "]"

		print "   - - - - - - - - - - - - - - - - - - - - - -  "

		# update Directory

		TEC = sprintf("T%02dR%02dS%02d", tec, rsc, sfc)
		extn = partynumber

		dtselsql = ""
		dtselsql = dtselsql "select recordno"
		dtselsql = dtselsql " from directtemplate"
		dtselsql = dtselsql " where site = '" UC_SITE "'"
		dtselsql = dtselsql "   and directtemplate.extension = " extn

		print " dtselsql = [" dtselsql "]"

		dtselsqlid = execute_sql(dbid0,dtselsql)
		dtselsql_nrows = result_arr[dtselsqlid "|" "nrows"]
		if ( dtselsql_nrows  < 1 ) {
			print "WARNING: No Rows found in directory to update"
		}

		for ( i = 1; i <= dtselsql_nrows; ++i ) {
			dtselsql_row = result_arr[dtselsqlid "|" i]
			split(dtselsql_row, dtselsql_arr, "|")
			f = 0
			recordno = trim(dtselsql_arr[++f])

			print "UPDATE recordno = " recordno	

			dtupdsql = ""
			dtupdsql = dtupdsql "update directtemplate"
			dtupdsql = dtupdsql " set"
			dtupdsql = dtupdsql " firstinit = '" TEC "'"
			dtupdsql = dtupdsql " where recordno = " recordno

			dcupdsql = ""
			dcupdsql = dcupdsql "update directcustom"
			dcupdsql = dcupdsql " set "
			dcupdsql = dcupdsql " UserID = '" UserID "',"
			dcupdsql = dcupdsql " MACaddress = '" MACaddress "',"
			dcupdsql = dcupdsql " SubscriptionDesc = '" Subscription "',"
			dcupdsql = dcupdsql " DeviceType = '" DeviceType "',"
			dcupdsql = dcupdsql " PhoneType = '" PhoneType "'"
			dcupdsql = dcupdsql " where recordno = " recordno

			print" dtupdsql = [" dtupdsql "]"
			dtupdsqlid = execute_sql(dbid0,dtupdsql)
			dtupdsql_nrows = result_arr[dtupdsqlid "|" "nrows"]
			print "dtupdsql_nrows=[" dtupdsql_nrows "]"
			if ( dtupdsql_nrows != 1 ) {
				print "ERROR: dtupdsql failed."
			}

			print" dcupdsql = [" dcupdsql "]"
			dcupdsqlid = execute_sql(dbid0,dcupdsql)
			dcupdsql_nrows = result_arr[dcupdsqlid "|" "nrows"]
			print "dcupdsql_nrows=[" dcupdsql_nrows "]"
			if ( dcupdsql_nrows != 1 ) {
				print "ERROR: dcupdsql failed."
			}

			print ""

		}
		print " ------------------------------------------------"
	}
		
	my_exit(0)
}