See my portfolio for information on the Document Builder project that used the Query Tree component.
This is the SQL file referenced by the Store node in the query tree XML file. The queries are executed in the InitDataReader method of the Node object. Each record returned by the query is loaded into the caller's entity object; the Store class in this case. Note that the columns returned in the query correspond to the properties of the Store class. The entity object is then notified of the record through the methods of the IEntityInstance interface.
WITH cteLiableForDebt(RecordID, [Status]) AS
(
-- This CTE works around the data anomaly when there are two identical topic code links for the same entity and topic code.
-- If there is more than one link for the same entity record, we arbitrarily take the link with the most recent date.
SELECT
inside.RecordID,
inside.Status
FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY link.EntityID, link.RecordID, link.TopicCodeID ORDER BY link.DateAdded DESC) AS 'Priority',
link.RecordID,
link.Status
FROM
vwTopicCodeLinks AS link
WHERE
link.EntityID = 1601 -- NACSStoreDemo
AND
link.TopicCodeID = 66 -- Liable for Debt
) AS inside
WHERE
inside.Priority = 1
)
SELECT
directory.InstitutionID AS 'InstitutionID',
store.ID AS 'StoreID',
RTRIM(store.Name) AS 'StoreName',
CASE WHEN store.Status IN (2, 6, 3) THEN 1 ELSE 0 END AS 'StoreIsMember',
CASE WHEN ISNULL(liableForDebt.Status, '') = 'Active' THEN 1 ELSE 0 END AS 'StoreIsInstitutionLiableForDebts',
RTRIM(store.NACSCorpID) AS 'StoreNACSCorpID',
RTRIM(store.ParentName) AS 'StoreLeaseOperator',
dbo.fnNACSFormatNameFirstLast(contact.FirstName, contact.MiddleName, contact.LastName, contact.Suffix, contact.NACSCredentials) AS 'StoreContactName',
RTRIM(contact.Title) AS 'StoreContactTitle',
CASE WHEN RTRIM(ISNULL(store.AddressLine1, '')) <> '' THEN RTRIM(store.AddressLine1) ELSE RTRIM(store.POBox) END AS 'StoreAddress1',
CASE WHEN RTRIM(ISNULL(store.AddressLine1, '')) <> '' THEN RTRIM(store.AddressLine2) ELSE RTRIM(store.POBoxLine2) END AS 'StoreAddress2',
CASE WHEN RTRIM(ISNULL(store.AddressLine1, '')) <> '' THEN RTRIM(store.City) ELSE RTRIM(store.POBoxCity) END AS 'StoreCity',
CASE WHEN RTRIM(ISNULL(store.AddressLine1, '')) <> '' THEN RTRIM(store.State) ELSE RTRIM(store.POBoxState) END AS 'StoreState',
CASE WHEN RTRIM(ISNULL(store.AddressLine1, '')) <> '' THEN RTRIM(store.ZipCode) ELSE RTRIM(store.POBoxZipCode) END AS 'StoreZipCode',
CASE WHEN RTRIM(ISNULL(store.AddressLine1, '')) <> ''
THEN CASE WHEN RTRIM(store.Country) = 'United States' THEN '' ELSE RTRIM(store.Country) END
ELSE CASE WHEN RTRIM(store.POBoxCountry) = 'United States' THEN '' ELSE RTRIM(store.POBoxCountry) END
END AS 'StoreCountry',
dbo.fnNACSFormatPhone(store.MainCountryCode, store.MainAreaCode, store.MainPhone, store.MainPhoneExtension) AS 'StorePhone',
dbo.fnNACSFormatPhone(store.MainFaxCountryCode, store.MainFaxAreaCode, store.MainFaxNumber, NULL) AS 'StoreFax',
RTRIM(store.MainEmail) AS 'StoreEmail',
RTRIM(REPLACE(REPLACE(store.WebSite, 'https://', ''), 'http://', '')) AS 'StoreWebSite',
RTRIM(demographics.SAN) AS 'StoreSAN',
RTRIM(demographics.StoreHours) AS 'StoreHours',
RTRIM(demographics.RepHours) AS 'StoreSalesPersonHours',
RTRIM(store.OwnType) AS 'StoreOwnership',
-- This next section pulls out all the institution names that this store also serves besides the primary one.
-- FOR XML PATH then combines the institution names to give us one string containing all of the institution names.
-- It will also encode "<", ">", and "&" which we don't want. We have to use an XPath expression on the returned
-- XML in order to get the value without encoding. We cannot just use REPLACE since the original strings may
-- have something like & that needs to be left untouched. The STUFF call deletes the first comma and space.
STUFF
(
(
SELECT TOP 5 -- Only list the first five at most
', ' + RTRIM(alsoServes.Name)
FROM
vwCompanies AS alsoServes
INNER JOIN
vwCompanyRelationships AS relationship ON alsoServes.ID = relationship.RelatedCompanyID AND relationship.CompanyRelationshipTypeID = 2 -- Other Institution Served
WHERE
relationship.CompanyID = store.ID
AND
alsoServes.CompanyTypeID = 3 -- Institution
AND
alsoServes.Status IN (5, 6) -- Institutions and High Schools - I HS
ORDER BY
RTRIM(alsoServes.DirSort)
FOR XML PATH(''), TYPE -- Using TYPE means this SELECT returns an XML type so we can use the value() method below.
).value('.', 'NVARCHAR(MAX)') -- The value method does an XPath query to return the current (and only) node ('.') in the XML type.
, 1, 2, ''
) AS 'StoreAlsoServes',
store.SurveyDate AS 'StoreSurveyDate'
FROM
vwNACSDirectoryStores AS directory
INNER JOIN
vwCompanies AS store ON directory.StoreID = store.ID
LEFT OUTER JOIN
vwNACSStoreDemo AS demographics ON store.ID = demographics.CompanyID
LEFT OUTER JOIN
cteLiableForDebt AS liableForDebt ON demographics.ID = liableForDebt.RecordID
LEFT OUTER JOIN
vwPersons AS contact ON store.MembershipPrimaryContactID = contact.ID
ORDER BY
directory.CityID,
directory.InstitutionSort,
directory.InstitutionID,
directory.StoreSort,
directory.StoreID
