USP_GET_FORMS_USAGE_TRACKING_DATA
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ProductName | varchar(4) | IN |
Definition
Copy
CREATE procedure dbo.USP_GET_FORMS_USAGE_TRACKING_DATA(
@ProductName varchar(4)
)
as
begin
DECLARE @LeastProcessingDate DateTime
SET @LeastProcessingDate = '2017-11-24T23:59:59' --This date is hardcoded as a part the requirements of "Usage Tracking" feature.
DECLARE @OrganizationDetails NVARCHAR(400)
SELECT @OrganizationDetails = CLARIFYSITEID,
@OrganizationDetails = @OrganizationDetails + ' - ' + INSTALLATIONNAME
FROM dbo.INSTALLATIONINFO;
DECLARE @YYYYMM NVARCHAR(7)
SET @YYYYMM = ( CONVERT(NCHAR(6), Getutcdate(), 112) + '%' );
WITH PAGES_HAVING_PART(PagesHavingPart, PartTypeID)
AS (SELECT Count(PageId),
PartTypeID
FROM (SELECT DISTINCT SITEPAGES.Id PageId,
VWCONTENTTYPES.ID PartTypeID
FROM SITEPAGES
INNER JOIN dbo.PAGECONTENT
ON SitePages.Id = PAGECONTENT.SitePagesID
INNER JOIN dbo.SITECONTENT
ON sitecontent.Id = PAGECONTENT.SiteContentID
INNER JOIN dbo.VWCONTENTTYPES
ON sitecontent.ContentTypesID = VWCONTENTTYPES.Id
WHERE SITEPAGES.DELETED = 0 AND Sitecontent.Deleted = 0
AND SiteContent.InTrashBin = 0
AND SiteContent.Approved = 1) AS A
GROUP BY PartTypeID),
PARTS_ACTIVE_INSTANCES(ActiveInstances, PartTypeId)
AS (SELECT ActiveInstances,
PartTypeId
FROM (SELECT Count(sitecontent.id) ActiveInstances,
Sitecontent.ContentTypesID PartTypeId
FROM Sitecontent
WHERE Sitecontent.Deleted = 0
AND SiteContent.InTrashBin = 0
AND SiteContent.Approved = 1
GROUP BY Sitecontent.ContentTypesID) AS A),
PART_TRANSACTIONS(TransactionCount, PartTypeID)
AS (SELECT Count(TransactionId) AS TransactionCount,
PartTypeID
FROM (SELECT DonationTransactionsID AS TransactionId,
CASE
WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0
THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
/* Calculate SUBSTRING length as end - start. */
( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
ELSE NULL
END AS PartTypeID
FROM dbo.DONATIONTRANSACTIONS
WHERE DONATIONTRANSACTIONS.Status = 1
AND IsDeleted = 0
AND DONATIONTRANSACTIONS.DateAdded > @LeastProcessingDate
UNION
SELECT EventTransactionsID AS TransactionId,
CASE
WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0
THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
/* Calculate SUBSTRING length as end - start. */
( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
ELSE NULL
END AS PartTypeID
FROM dbo.EVENTTRANSACTIONS
WHERE EVENTTRANSACTIONS.Status = 1
AND IsDeleted = 0
AND EVENTTRANSACTIONS.DateAdded > @LeastProcessingDate
UNION
SELECT ID AS TransactionId,
CASE
WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0
THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
/* Calculate SUBSTRING length as end - start. */
( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
ELSE NULL
END AS PartTypeID
FROM dbo.MEMBERSHIPTRANSACTIONS
WHERE MEMBERSHIPTRANSACTIONS.Status = 1
AND IsDeleted = 0
AND MEMBERSHIPTRANSACTIONS.DateAdded > @LeastProcessingDate
UNION
SELECT SignupTransactionsID AS TransactionId,
CASE
WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0
THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
/* Calculate SUBSTRING length as end - start. */
( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
ELSE NULL
END AS PartTypeID
FROM dbo.SIGNUPTRANSACTIONS
WHERE SIGNUPTRANSACTIONS.IsDeleted = 0
AND SIGNUPTRANSACTIONS.DateAdded > @LeastProcessingDate
UNION
SELECT ProfileUpdateTransactionsID AS TransactionId,
CASE
WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0
THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
/* Calculate SUBSTRING length as end - start. */
( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
ELSE NULL
END AS PartTypeID
FROM dbo.PROFILEUPDATETRANSACTIONS
WHERE PROFILEUPDATETRANSACTIONS.IsDeleted = 0
UNION
SELECT ID AS TransactionId,
CASE
WHEN Charindex('PartTypeID', CONVERT(VARCHAR(max), XMLObjectData)) > 0
THEN Substring(CONVERT(VARCHAR(max), XMLObjectData), ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ),
/* Calculate SUBSTRING length as end - start. */
( Patindex('%</PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) - ( Patindex('%<PartTypeID>%', CONVERT(VARCHAR(max), XMLObjectData)) + 12 ) ))
ELSE NULL
END AS PartTypeID
FROM dbo.VOLUNTEERTRANSACTIONS
WHERE VOLUNTEERTRANSACTIONS.IsDeleted = 0
AND VOLUNTEERTRANSACTIONS.DateAdded > @LeastProcessingDate) AS A
WHERE PartTypeID IS NOT NULL
AND PartTypeID > 0
GROUP BY PartTypeID),
MONTH_PAGEVIEWS(PageId, PageViews)
AS (SELECT PageId,
Sum(PageViews)
FROM dbo.STATISTICSMONTHPAGE
GROUP BY PageID),
DAY_PAGEVIEWS(PageId, PageViews)
AS (SELECT PageId,
Sum(PageViews)
FROM dbo.STATISTICSDAYPAGE
WHERE STATISTICSDAYPAGE.YYYYMMDD LIKE @YYYYMM
GROUP BY PageID),
ALL_PAGE_VIEWS(PageId, PageViews)
AS (SELECT MONTH_PAGEVIEWS.PageId,
Isnull(MONTH_PAGEVIEWS.PageViews, 0)
+ Isnull(DAY_PAGEVIEWS.PageViews, 0)
FROM MONTH_PAGEVIEWS
LEFT OUTER JOIN DAY_PAGEVIEWS
ON MONTH_PAGEVIEWS.PageId = DAY_PAGEVIEWS.PageId),
CONTENT_PAGE_VIEWS(PageViews, PartTypeId)
AS (SELECT Sum(ALL_PAGE_VIEWS.PageViews),
VWCONTENTTYPES.ID
FROM ALL_PAGE_VIEWS
INNER JOIN SitePages
ON ALL_PAGE_VIEWS.PageId = SitePages.ID
INNER JOIN dbo.PAGECONTENT
ON SitePages.Id = PAGECONTENT.SitePagesID
INNER JOIN dbo.SITECONTENT
ON sitecontent.Id = PAGECONTENT.SiteContentID
INNER JOIN dbo.VWCONTENTTYPES
ON sitecontent.ContentTypesID = VWCONTENTTYPES.Id
GROUP BY VWCONTENTTYPES.ID)
SELECT @ProductName + '-' + 'PartsUsage' + '-'
+ @OrganizationDetails AS 'Group',
NAME AS 'PartType',
VWCONTENTTYPES.Deprecated AS 'IsPartDeprecated',
Max(PARTS_ACTIVE_INSTANCES.ActiveInstances) AS 'ActiveInstances',
Max(SiteContent.UpdateDate) AS 'LastUpdated',
Max(PAGES_HAVING_PART.PagesHavingPart) AS 'PagesHavingPart',
Max(PART_TRANSACTIONS.TransactionCount) AS 'TotalTransactions',
Max(CONTENT_PAGE_VIEWS.PageViews) AS 'TotalViews'
FROM dbo.VWCONTENTTYPES
LEFT OUTER JOIN dbo.SiteContent
ON SiteContent.ContentTypesID = VWCONTENTTYPES.id
LEFT OUTER JOIN dbo.PageContent
ON PageContent.SiteContentID = SiteContent.ID
LEFT OUTER JOIN PAGES_HAVING_PART
ON PAGES_HAVING_PART.PartTypeID = VWCONTENTTYPES.id
LEFT OUTER JOIN PARTS_ACTIVE_INSTANCES
ON PARTS_ACTIVE_INSTANCES.PartTypeId = VWCONTENTTYPES.id
LEFT OUTER JOIN PART_TRANSACTIONS
ON PART_TRANSACTIONS.PartTypeID = VWCONTENTTYPES.ID
LEFT OUTER JOIN CONTENT_PAGE_VIEWS
ON CONTENT_PAGE_VIEWS.PartTypeId = VWCONTENTTYPES.ID
WHERE
VWCONTENTTYPES.Static=0 AND VWCONTENTTYPES.Hidden=0
--AND VWCONTENTTYPES.ID NOT IN(117,9106,150,9108,9109,9110,9112,9111)
--Part Id's 117,9106,150,9108,9109,9110,9112,9111 are not supported by BBNC
GROUP BY VWCONTENTTYPES.NAME,
VWCONTENTTYPES.Deprecated
ORDER BY VWCONTENTTYPES.NAME
END