: !/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
#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


## 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
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
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
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
ORDER BY 1
SQLCMD1
fi

exit 0