# !/bin/sh

id="$0"

outfile="${id}.unl"
errfile="${id}.err"

tmpfile1="/tmp/${id}.$$"

rm -f "${outfile}"
rm -f "${errfile}"
rm -f "${tmpfile1}"

runisql <<SQLCMD0
unload to "$tmpfile1" DELIMITER "|"
select unique recordno, mobile, mobile_carrier
from directcustom
where mobile != ""
  and mobile_carrier = "U"
order by 2;
SQLCMD0

awk -F '|' -v"errfile=${errfile}" '

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

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

function digitsonly(s)
{
	gsub(/[^0-9]/,"",s)
	return s
}


{
	#print $0
	f = 1
	dc_recordno = trim($f++)
	dc_mobile = trim($f++)
	dc_mobile_carrier = trim($f++)

	mobile = digitsonly(dc_mobile)
	sid = mobile
	sidmainttype = "mobile_phones"
	customid = sidmainttype
	sdate = "01/01/2005"
	edate = ""
	directoryid = dc_recordno
	groupid = ""
	grid = groupid
	datatype = 1	# Telstra
	purchased = ""
	supplier = "Unknown"
	manufacturer = "Unknown"
	model = "Unknown"
	carriercode = "t"	# Telstra onnet
	servicetype = "Unknown"
	introaming = ""
	gprs = ""
	simnumber = "Unknown"
	imeinumber = "Unknown"
	puk = ""
	dataplan = ""
	contractstart = ""
	contractfinishS = ""
	archivecomment = ""
	updated = "20/11/2008"

	if ( dc_mobile_carrier != "U" ) {
		print "skipping carrier [" dc_mobile_carrier "] " $0 > errfile
		next
	}

	if ( length(mobile) != 10 ) {
		print "skipping mobile [" mobile "] " $0 > errfile
		next
	}

	if ( gotmobile_arr[mobile] == 1 ) {
		print "skipping duplicate mobile [" mobile "] " $0 > errfile
		next
	}

	gotmobile_arr[mobile] = 1

	if ( recordno == 0 )
		recordno = 10000
	else
		++recordno

	# sidconf table
	#printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|
	#	%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|
	#	%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|
	#	%s|%s|%s|%s|%s|%s|%s|%s|%s|\n",
	#	recordno, sid, sidmainttype, sdate, edate,
	#	grid, directoryid,
	#	serialnumber,
	#	purchased,
	#	manufacturer, model,
	#	supplier, datatype,
	#	carriercode, servicetype,
	#	"", "", "", "", "", "", "", "", "",
	#	"", "", "", "", "", "", "", "", "",
	#	introaming,contractstart,contractfinish,"","","","","","",
	#	"", "", "", "", "", "", "", "", "",
	#	gprs,simnumber,imeinumber,dataplan,archivecomment,puk,"","","",
	#	updated,
	#	msg1, msg2, msg3, msg4, msg5, msg6, msg7, msg8, msg9)

	# mobconf view on sidconf table
	printf("%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|%s|\n",
		recordno, mobile, customid, sdate, edate,
		directoryid, grid,
		datatype, purchased, supplier,
		manufacturer, model,
		carriercode, servicetype,
		introaming,
		gprs, simnumber, imeinumber, puk,
		dataplan,
		contractstart, contractfinish,
		archivecomment,
		updated,
		msg1, msg2, msg3, msg4, msg5, msg6, msg7, msg8, msg9)

}' < "$tmpfile1" >${outfile}

rm -f "$tmpfile1"

runisql <<SQLCMD1
#unload to "/u/catcom/tx/sidconf_ecu_$$.unl" select * from sidconf;
delete from sidconf where sidmainttype = "mobile_phones";
SQLCMD1

load -d catcom -t mobconf -f "${outfile}" 

exit 0