: !/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
	cp tbscustomerhierarchyservice.unl "$outfile"
else
	cp tbscustomerhierarchy.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 AS ServiecID,
    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