: !/bin/sh

infile="Centre_Metro.csv"
startdistid="3001"

# Centre_Metro.csv
#ID,Description,DepartmentID,Department,GroupID,GroupName,CustomerID,ShipTo,FullName,StartDate,EndDate,Active,PERSON,Centreid,DeptId,GroupID,CustomerID
#4105,7050,2948,NETWORK DEVELOPMENT,1521,MTM EX CONNEX MELB PTY LTD,1096,ZCB,METRO TRAINS MELBOURNE PTY LTD,4/12/2009,,TRUE,VICKIE STEWARD,4105,2948,1521,1096

# CentreVRT_15Nov2010.csv
#ID,Description,DepartmentID,Department,GroupID,GroupName,CustomerID,ShipTo,FullName,StartDate,EndDate,Active,PERSON
#192,165,2900,NOM VOICE AND DATA,1515,TELECOMMUNICATION SERVICES,499,VRT,VRT Victrack,10-05-2005,,TRUE,STEPHEN ROBERTS

# table "catcom".wbdconf 
#    recordno integer not null,
#    distid serial not null,
#    distiddesc char(40) not null,
#    lastdist char(8),
#    lastdistdate date,
#    altemail char(50),
#    altemailyn char(1),
#    wbd_method char(15) not null,
#    wbd_type char(15) not null,
#    userid char(25),
#    passwd char(132),
#    sdate date not null,
#    edate date

# table "catcom".wbdlist 
#     distid integer not null,
#     grid char(8) not null


wbdconffile="wbdc1.unl"
wbdlistfile="wbdl1.unl"

rm -f "$wbdconffile"
rm -f "$wbdlistfile"

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

function ld_tPerson(tPersonfile) {
        while ( (getline aline < tPersonfile) > 0 ) {
                split(aline, a_arr, "|")
                #printf("aline=%s\n", aline)
                f = 0
                PersonID = trim(a_arr[++f])
                Person = toupper(trim(a_arr[++f]))
		#StartDate = trim(a_arr[++f])
		#EndDate = trim(a_arr[++f])
		#Active = trim(a_arr[++f])
		emailaddress = trim(a_arr[++f])
 
                Person_to_ID_arr[Person] = PersonID
		PersonID_to_email_arr[PersonID] = emailaddress

                #print "ld_tPerson() Person_to_ID_arr[" Person "]=[" Person_to_ID_arr[Person] "]"
        }
        close(tPersonfile)
}

BEGIN{
        ld_tPerson("tPerson.unl")
	nextdistid = startdistid
}

{
	# print $0
	if ( NR == 1 )	# skip header
		next
	f = 0
	uGroupID = trim($(++f))
	uGroupDesc = trim($(++f))
	uDepartmentID = trim($(++f))
	uDepartment = trim($(++f))
	uGroupID = trim($(++f))
	uGroupName = trim($(++f))
	uCustomerID = trim($(++f))
	uShipTo = trim($(++f))
	FullName = trim($(++f))
	StartDate = trim($(++f))
	EndDate = trim($(++f))
	Active = trim($(++f))
	Person = trim($(++f))
	bmCentreID = trim($(++f))
	bmDeptID = trim($(++f))
	bmGroupID = trim($(++f))
	bmCustomerID = trim($(++f))

	# use highest non blank as bilmax distgrid
	distgrid = ""
	if ( bmCentreID != "" ) {
		distgrid = "CENTRE" bmCentreID
	}
	if ( bmDeptID != "" ) {
		distgrid = "DEPT" bmDeptID
	}
	if ( bmGroupID != "" ) {
		distgrid = "GRP" bmGroupID
	}
	if ( bmCustomerID != "" ) {
		#distgrid = "CUST" bmCustomerID
		distgrid = uShipTo
	}

	PersonID = Person_to_ID_arr[Person]
	if ( PersonID == "" ) {
		print "Error: Person [" Person "] not found in tPerson"
		next
	}

	email = PersonID_to_email_arr[PersonID]
	if ( email == "" ) {
		print "Error: Person [" Person "] ID [" PersonID "] has no email address"
		next
	}

	if ( distgrid == "" ) {
		print "Error: Person [" Person "] ID [" PersonID "] has blank distgrid"
		next
	}

	thisdistid = PersonID_to_distid_arr[PersonID]
	if ( PersonID_to_distid_arr[PersonID] == "" ) {
		# create new distid
		#thisdistid = PersonID
		thisdistid = nextdistid++
		PersonID_to_distid_arr[PersonID] = thisdistid
	}
	else {
		print "Warning: Person [" Person "] ID [" PersonID "] has more than one Department"
	}

	print "mkwbd1: Person [" Person "] ID [" PersonID "] EmailAddress [" email "]" " thisdistid [" thisdistid "]" " distgrid [" distgrid "]"

	if ( done_distid_wbdconf_arr[thisdistid] == "" ) {
		# create wbdconf entry
		recordno = PersonID
		distid  = thisdistid
		distiddesc = "Reports for " Person
		lastdist = ""
		lastdistdate = ""
		altemail = ""
		altemailyn = "N"
		wbd_method = "WEB"
		wbd_type = "STANDARD"
		userid = "email"
		passwd = "0x101e1ff4262e2147"	# (bilmax21)
		sdate = "01/01/2009"		# StartDate
		edate = ""

		print recordno "|" distid  "|" distiddesc "|" lastdist "|" lastdistdate "|" altemail "|" altemailyn "|" wbd_method "|" wbd_type "|" userid "|" passwd "|" sdate "|" edate "|" >> wbdconffile

		done_distid_wbdconf_arr[thisdistid] = "1"
	}

	# create wbdlist entry1
	distid = thisdistid
	grid = distgrid

	if ( done_distidgrid_arr[ distid "|" grid ] == "1" ) {
		print "Warning: Person [" Person "] ID [" PersonID "]" "  already has grid [" grid "]"
		next
	}

	done_distidgrid_arr[ distid "|" grid ] = "1"

	print distid "|" grid "|" >> wbdlistfile


}
' < "$infile" >$0.out
#' < CentreVRT_14Dec2010.csv >$0.out

grep "Error:" $0.out >$0.err
grep "Warning:" $0.out >$0.warn

exit 0