: !/bin/sh

if [ -n "$1" ]; then
	outfile="$1"
else
	outfile="tbspersonservicelist.unl"
fi

## Testing
#cp tbspersonservicelist.unl.test  "$outfile"
#exit 0


# don't use view anymore 9/6/2011
## view created by Robyn 08/05/2010
## update to match my sql 14/01/2011
#tbssql >"$outfile" <<SQLCMD0
#select * from vwRDBilmaxPersonServiceList
#order by 1
#SQLCMD0

#exit 0

#----------------------------
# SQL for view is...
tbssql >"$outfile" <<SQLCMD0
SELECT
    dbo.tPerson.ID AS PersonID, dbo.tPerson.Person,
    dbo.tServiceID.ID, dbo.tServiceID.ServiceID,
    dbo.tCustomer.ShipTo, dbo.tCustomer.FullName,
    dbo.tLocation.ID, dbo.tLocation.Description,
    dbo.tDepartment.ID, dbo.tDepartment.Description,
    dbo.tSupplier.ID, dbo.tSupplier.SupplierName
FROM
    dbo.tPerson
    LEFT OUTER JOIN dbo.tGroup
	    INNER JOIN dbo.tCentre
     INNER JOIN dbo.tServiceID
		    LEFT OUTER JOIN dbo.tLocation
			ON dbo.tLocation.ID = dbo.tServiceID.LocationID
		ON dbo.tCentre.ID = dbo.tServiceID.CustomerCentreID

		    LEFT JOIN tServiceType
		        ON tServiceType.ID = tServiceID.ServiceTypeID
			    LEFT JOIN tSupplier
			 	ON tServiceType.SupplierID = tSupplier.ID

	    INNER JOIN dbo.tDepartment
		ON dbo.tCentre.DepartmentID = dbo.tDepartment.ID
        ON dbo.tGroup.ID = dbo.tDepartment.GroupID
    INNER JOIN dbo.tCustomer
        ON dbo.tGroup.CustomerID = dbo.tCustomer.ID
    ON dbo.tPerson.ID = dbo.tServiceID.PersonID
WHERE
    (dbo.tPerson.Active = 1) AND (dbo.tServiceID.Active = 1)
  AND (NOT (dbo.tServiceID.ServiceID LIKE 'kamco%'))
  AND (NOT (dbo.tServiceID.ServiceID LIKE 'annual%'))
ORDER BY dbo.tPerson.Person
SQLCMD0

exit 0