# gett21groups4.awk
#----------------------------------------------------------------------------

function sanetrim(s)
{
	sub(/^[ \t]*/,"",s)
	sub(/[ \t]*$/,"",s)
	gsub(/\'/,"`",s)
	gsub(/\"/,"",s)
	return s
}

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

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

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


function puthier(parent,child,type,level,name1,name2,name3)
{
	if ( done_parent_child_arr[parent "|" child] == "1" )
		return
	parentgrid = parent
	grid = child
	grtype = type
	grlevel = level
	grname1 = name1
	grname2 = name2
	grname3 = name3

	result = parentgrid "|" grid "|" grtype "|" grlevel "|" grname1 "|" grname2 "|" grname3 "|"
	print result
	++nrows
	done_parent_child_arr[parent "|" child] = "1"
}


#-----------------------
BEGIN {
	if ( docentre == "" )
		docentre = "1"
	if ( hasservice == "" )
		hasservice = "0"
	nrows = 0
}


{
# with service
#1|(C)1|3336|MEG1|2359|MAINCO|1158|MAINCO|498|MEG|MAINCO MELBOURNE PTY LTD|
#7|(C)1008|1848|410|2786|VRT NETWORK SALES|1515|TELECOMMUNICATION SERVICES|499|VRT|VRT Victrack|
#10|(C)1010|4152|ZMW1|2986|METRO TRAINS MELB PTY LTD|1528|MTM EX CONNEX RENT/CALLS|1130|ZMW|METRO TRAINS MELBOURNE PTY LTD|

# without service
#1|666|1|3RD PARTY PASS THRU|1515|TELECOMMUNICATION SERVICES|499|VRT|VRT Victrack|
#2|4500|2|BAYSIDE|2|NATIONAL EXPRESS|459|CME|CONNEX MELBOURNE PTY LTD|

	f = 0
	if ( hasservice == "1" ) {
		ServiceIDID = "SERVICE" sanetrim($++f)
		ServiceID = sanetrim($++f)
	}	
	CentreID = "CENTRE" sanetrim($++f)
	CentreDesc = sanetrim($++f)
	DeptID = "DEPT" sanetrim($++f)
	DeptDesc = sanetrim($++f)
	GrpID = "GRP" sanetrim($++f)
	GrpDesc = sanetrim($++f)
	CustID = "CUST" sanetrim($++f)
	ShipTo = sanetrim($++f)
	FullName = sanetrim($++f)
	VNumber = sanetrim($++f)

	CustActive = sanetrim($++f)
	GrpActive = sanetrim($++f)
	DeptActive = sanetrim($++f)
	CentreActive = sanetrim($++f)
	if ( hasservice == "1" ) {
		ServiceIDActive = sanetrim($++f)
	}

	if ( hasservice == "1" ) {
		#ServiceIDDesc = ServiceID " (" ServiceIDID ")"
		ServiceIDDesc = ServiceID
	}

	CustDesc = FullName
	ShipToDesc = CustDesc " (" CustID ")"

	CustNumber = "VNUM_" VNumber
	# use first occurance of FulName as CustNumberDesc
	if ( CustNumberDesc_arr[CustNumber] == "" ) {
		CustNumberDesc = CustDesc
		CustNumberDesc_arr[CustNumber] = CustNumberDesc 
	}
	else {
		CustNumberDesc = CustNumberDesc_arr[CustNumber]
	}

	if ( hasservice == "1" ) {
		if ( ServiceID == "" ) {
			print "blank ServiceID"
			exit 1
		}
		if ( CentreID == "" ) {
			print "blank CentreID"
			exit 1
		}
	}
	if ( DeptID == "" ) {
		print "blank DeptID"
		exit 1
	}
	if ( GrpID == "" ) {
		print "blank GrpID"
		exit 1
	}
	if ( CustID == "" ) {
		print "blank CustID"
		exit 1
	}
	if ( hasservice == "1" ) {
		if ( ServiceIDDesc == "" ) {
			print "blank ServiceID"
			exit 1
		}
	}

	#if ( hasservice == "1" )
	#	ServiceIDID_to_ServiceIDDesc_arr[ServiceIDID] = ServiceIDDesc
	#CentreID_to_CentreDesc_arr[CentreID] = CentreDesc
	#DeptID_to_DeptDesc_arr[DeptID] = DeptDesc
	#GrpID_to_GrpDesc_arr[GrpID] = GrpDesc
	#CustID_to_Shipto_arr[CustID] = Shipto
	#CustID_to_CustDesc_arr[CustID] = CustDesc
	#ShipTo_to_ShipToDesc_arr[ShipTo] = ShipToDesc

	#print "CentreID_to_CentreDesc_arr[" CentreID "]=[" CentreID_to_CentreDesc_arr[CentreID] "]"

	# Hierarchy is Cust -> Grp -> Dept -> Centre -> Service
	#sethier(sqlid,parent,child,type,level,name1,name2,name3)

	CustNumgrtype = "G"
	Custgrtype = "G"
	Grpgrtype = "G"
	Deptgrtype = "G"
	Centregrtype = "G"
	ServiceIDgrtype = "E"
        if ( CustActive != "1" )
		CustNumgrtype = "g"
        if ( CustActive != "1" )
		Custgrtype = "g"
        if ( GrpActive != "1" )
		Grpgrtype = "g"
        if ( DeptActive != "1" )
		Deptgrtype = "g"
        if ( CentreActive != "1" )
		Centregrtype = "g"
        if ( hasservice == "1" && ServiceIDActive != "1" )
		ServiceIDgrtype = "e"

	puthier("","STATE","G","0","State","State","State")
	puthier("STATE",CustNumber,CustNumgrtype,"1",CustNumberDesc,CustNumberDesc,CustNumberDesc)


	puthier(CustNumber,ShipTo,Custgrtype,"2",ShipToDesc,ShipToDesc,ShipToDesc)

	puthier(ShipTo,GrpID,Grpgrtype,"3",GrpDesc,GrpDesc,GrpDesc)
	puthier(GrpID,DeptID,Deptgrtype,"4",DeptDesc,DeptDesc,DeptDesc)
	if ( docentre == "1" )
		puthier(DeptID,CentreID,Centregrtype,"5",CentreDesc,CentreDesc,CentreDesc)
	if ( hasservice == "1" )
		puthier(CentreID,ServiceID,ServiceIDgrtype,"6",ServiceIDDesc,ServiceIDDesc,ServiceIDDesc)

}


END {
}