: !/bin/sh

if [ -z "$includeservice" ]; then
	includeservice="0"
fi
if [ -n "$2" ]; then
	includeservice="1"
fi

if [ "$1" = "" ]; then
	if [ "$includeservice" = "1" ]; then
		outfile="tbscustomerhierarchyservice.unl"
	else
		outfile="tbscustomerhierarchy.unl"
	fi
else
        outfile="$1"
fi


## Testing
#includeservice="1"
#outfile="gth1.unl"
##if [ "$includeservice" = "1" ]; then
##	# used by gett21groups4
##	cp tbscustomerhierarchyservice.unl "$outfile"
##	#cp tbscustomerhierarchyservice.test.unl "$outfile"
##else
##	# used by WBDM
##	cp tbscustomerhierarchy.unl "$outfile"
##	#cp tbscustomerhierarchy.test.unl "$outfile"
##fi
##exit 0


# don't use view anymore 9/6/2011
## view created by Robyn 18/05/2010
#tbssql > "$outfile" <<SQLCMD0
#select * from vwRDBilmaxCustomerHeirarchy
#order by 1
#SQLCMD0
#
#exit 0

#----------------------------
# SQL for view is...
if [ "$includeservice" != "1" ]; then		# dont include service level
tbssql > "$outfile" <<SQLCMD0
SELECT
    dbo.tCentre.ID AS CentreID, dbo.tCentre.Description AS CentreDesc,
    dbo.tDepartment.ID As DeptID, dbo.tDepartment.Description AS DeptDesc,
    dbo.tGroup.ID AS GrpID, dbo.tGroup.Description AS GrpDesc, 
    dbo.tCustomer.ID AS CustID, dbo.tCustomer.ShipTo, dbo.tCustomer.FullName,
    dbo.tCustomer.VNumber
, tCustomer.Active, tGroup.Active, tDepartment.Active, tCentre.Active
FROM
    dbo.tCentre INNER JOIN
    dbo.tDepartment ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID INNER JOIN
    dbo.tGroup ON dbo.tDepartment.GroupID = dbo.tGroup.ID INNER JOIN
    dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID
WHERE
    (tCustomer.VNumber not like 'V%' or tCustomer.VNumber = 'VRT')
ORDER BY 1
SQLCMD0
else						# include service level
tbssql > "$outfile" <<SQLCMD1
SELECT
    dbo.tServiceID.ID AS ServiceIDID, dbo.tServiceID.ServiceID,
    dbo.tCentre.ID AS CentreID, dbo.tCentre.Description AS CentreDesc,
    dbo.tDepartment.ID As DeptID, dbo.tDepartment.Description AS DeptDesc,
    dbo.tGroup.ID AS GrpID, dbo.tGroup.Description AS GrpDesc, 
    dbo.tCustomer.ID AS CustID, dbo.tCustomer.ShipTo, dbo.tCustomer.FullName,
    dbo.tCustomer.VNumber
, tCustomer.Active, tGroup.Active, tDepartment.Active, tCentre.Active
, tServiceID.Active
FROM
    dbo.tServiceID INNER JOIN dbo.tCentre ON
    dbo.tServiceID.CustomerCentreID = dbo.tCentre.ID INNER JOIN
    dbo.tDepartment ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID INNER JOIN
    dbo.tGroup ON dbo.tDepartment.GroupID = dbo.tGroup.ID INNER JOIN
    dbo.tCustomer ON dbo.tGroup.CustomerID = dbo.tCustomer.ID
WHERE
    (tCustomer.VNumber not like 'V%' or tCustomer.VNumber = 'VRT')
ORDER BY 1
SQLCMD1
fi

exit 0