Custom Ordered Products Clicks by Location with composite components roll up - xmpie-users/uStore-js GitHub Wiki
Custom Ordered Products Clicks by Location with composite components roll-up
How to add custom SQL reports to uStore Refer to the following knowledgebase article for information on how to add custom SQL reports to uStore [https://help.xmpie.com/KBA/index.htm#t=0061%2F0061_Creating_Custom_uStore_Reports.htm
Overview This report covers the following:
Order Information - Order Date, Store, Order Id, Company and Customer Name, Number of of Recipients, Qty per Recipient, Total Price
Order Item Information - Job Id, Job Name, Number of Uploaded Pages
Product Information - Product Name, Product Type,
Production Information - BW Clicks, Color Clicks, Simplex Sheets, Duplex Sheets, Simplex pages, Duplex pages, and Paper Type
Note: Sheets is physical paper and pages is the surface. Ex. 1 duplex sheet holds 2 duplex pages
sp_Report_OrderedProductClicksbyLocation - 090321.sql
USE [uStore]
GO
/****** Object: StoredProcedure [dbo].[sp_Report_OrderedProductClicksbyLocation] Script Date: 9/3/2021 4:09:00 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: XMPie Application Services
-- Report name: Ordered Products Clicks by Location
-- Description: Ordered products with color definition and quantity (number of clicks) by delivery location
-- =============================================
ALTER PROCEDURE [dbo].[sp_Report_OrderedProductClicksbyLocation]
(
@MallID int,
@ActiveUserID int,
@StoreID int = NULL,
@CultureID int = 1,
@StartDate datetime = NULL,
@EndDate datetime = NULL
)
AS
/* create temp_cte query to... */
WITH temp_cte
AS (SELECT
orders.encryptedorderid
AS
OrderNumber,
delivery.creationdate
AS ShippingDate,
orders.displayorderdate
AS OrderDate,
orderproduct.orderproductid
AS JobNumber,
store_culture.NAME
AS storename,
DeliveryTentative.Ship_Company
AS ShipCompany,
users.firstname
AS customerFirstName,
users.lastname
AS customerLastName,
(SELECT TOP(1) Name
FROM [uStore].[dbo].[ACL_UserGroup]
WHERE UserGroupId IN (SELECT UserGroupID
FROM [uStore].[dbo].[ACL_UserGroupMembership]
WHERE userid = users.userid )
) AS usergroup,
product_culture.NAME
AS productName,
(SELECT TOP 1 title
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproduct AS fileNameQuery
WHERE parentorderproductid = orderproduct.orderproductid
)
)
AS documenttitle,
orderproduct.QuantityPerRecipient
AS QuantityPerRecipient,
orderproduct.NumRecipients
AS NumRecipients,
orderproduct.TotalQuantity
AS TotalQuantity,
orderproduct.TotalNumberOfPages
AS TotalNumberOfPages,
orderproduct.totalprice
AS TotalPrice,
(SELECT doctype.NAME
FROM doctype
WHERE doctypeid IN (SELECT doctypeid
FROM doc
WHERE doc.productid = orderproduct.productid
)
)
AS DocumentType,
/* product property Job Name */
(SELECT Top (1) DialValue
FROM OrderProductDialValue
WHERE OrderProductID = orderproduct.orderproductid
AND DialID IN (SELECT DialID
FROM [uStore].[dbo].[Dial_Culture]
WHERE CultureID = 1 AND FriendlyName = 'Job Name')
)
AS JobName,
/* product property Paper */
(SELECT Top (1) DialValue
FROM OrderProductDialValue
WHERE OrderProductID IN (SELECT orderproductid
FROM orderproduct AS fileNameQuery
WHERE parentorderproductid = orderproduct.orderproductid
)
AND DialID IN (SELECT DialID
FROM [uStore].[dbo].[Dial_Culture]
WHERE CultureID = 1 AND FriendlyName = 'Paper')
)
AS Paper,
/* DuplexPages */
(SELECT COALESCE(Sum(numofpages), 0)
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE
uproducedialname = 'Double Sided Printing'
)
AND orderproductid IN (SELECT orderproductid
FROM
orderproduct AS duplexOrderNumber
WHERE
parentorderproductid = orderproduct.orderproductid
)
AND dialvalue = 'True'
)
)
AS DuplexPages,
/* SimplexPages */
(SELECT COALESCE(Sum(numofpages), 0)
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE
uproducedialname = 'Double Sided Printing'
)
AND orderproductid IN (SELECT orderproductid
FROM
orderproduct AS duplexOrderNumber
WHERE
parentorderproductid = orderproduct.orderproductid
)
AND dialvalue = 'False'
)
)
AS SimplexPages,
papersize.displayname AS UploadSize,
(SELECT Sum( Cast(Replace(pagesxml, 'encoding="utf-8"', '') AS XML).value('count(/PageList/Page/ColorType[text() = "Color"])','int') )
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE uproducedialname = 'Color'
)
AND orderproductid IN (SELECT orderproductid
FROM orderproduct AS MySelection
WHERE parentorderproductid = orderproduct.orderproductid
)
AND dialvalue = 'False'
)
)
AS PagesColorForced,
(SELECT Sum( Cast(Replace(pagesxml, 'encoding="utf-8"', '') AS XML).value('count(/PageList/Page/ColorType[text() = "Color"])','int') )
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE uproducedialname = 'Color'
)
AND orderproductid IN (SELECT orderproductid
FROM orderproduct AS MySelection
WHERE parentorderproductid = orderproduct.orderproductid
)
AND dialvalue = 'tRUE'
)
)
AS PagesColor,
(SELECT Sum( Cast(Replace(pagesxml, 'encoding="utf-8"', '') AS XML).value('count(/PageList/Page/ColorType[text() = "Grayscale"])','int') )
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE uproducedialname = 'Color'
)
AND orderproductid IN (SELECT orderproductid
FROM orderproduct AS MySelection
WHERE parentorderproductid = orderproduct.orderproductid
)
AND dialvalue = 'True'
)
)
AS PagesGray,
(SELECT Sum( Cast(Replace(pagesxml, 'encoding="utf-8"', '') AS XML).value('count(/PageList/Page/ColorType[text() = "Grayscale"])','int') )
FROM filesubmissiondocument
WHERE suborderproductid IN (SELECT orderproductid
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE uproducedialname = 'Color'
)
AND orderproductid IN (SELECT orderproductid
FROM orderproduct AS MySelection
WHERE parentorderproductid = orderproduct.orderproductid
)
AND dialvalue = 'False'
)
)
AS PagesGrayForced,
(SELECT dialvalue
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial
WHERE uproducedialname = 'Color'
AND orderproductid IN (SELECT
orderproductid
FROM
orderproduct AS MySelection
WHERE
orderproductid = orderproduct.orderproductid)))
AS UseColor,
/* */
(SELECT dialvalue
FROM orderproductdialvalue
WHERE dialid IN (SELECT dialid
FROM dial_culture
WHERE friendlyname = 'Paper Type'
AND orderproductid IN (SELECT orderproductid
FROM orderproduct AS MySelection
WHERE orderproductid = orderproduct.orderproductid
)
)) AS CustomPaperSize
FROM orderproduct
JOIN orders
ON orderproduct.orderid = orders.orderid
JOIN store
ON store.storeid = orders.storeid
JOIN store_culture
ON store.storeid = store_culture.storeid
AND store_culture.cultureid = @cultureId
JOIN users
ON orders.userid = users.userid
JOIN product_culture
ON orderproduct.productid = product_culture.productid
AND product_culture.cultureid = @cultureId
LEFT JOIN productcompositioncontent
ON productcompositioncontent.compositionproductid =
product_culture.productid
LEFT JOIN doc
ON doc.productid =
productcompositioncontent.componentproductid
LEFT JOIN papersize
ON doc.papersizeid = papersize.papersizeid
LEFT JOIN deliveryitem
ON deliveryitem.orderproductid = orderproduct.orderproductid
LEFT JOIN delivery
ON deliveryitem.deliveryid = delivery.deliveryid
LEFT JOIN DeliveryTentative
ON DeliveryTentative.orderid = orders.orderid
WHERE (
( (store.storeid = @StoreId) OR (Isnull(@StoreID, -1) <= 0) )
AND ( @StartDate <= orders.displayorderdate
OR @StartDate IS NULL
OR @StartDate = '' )
AND ( Dateadd(day, 1, @EndDate) >= orders.dateordercreated
OR @EndDate IS NULL
OR @EndDate = '' )
AND orders.iscart = 0
AND orderproduct.isdraft = 0
AND orders.dateordersubmitted IS NOT NULL
AND orders.dateordersubmitted <> ''
AND ( (orders.approvalstatusid IS NULL) OR (orders.approvalstatusid = 1 ) )
AND orderproduct.totalquantity >= 1
)
)
/* */
/* create query to roll up infor from temp_cte */
/* */
SELECT DISTINCT
ShipCompany AS 'Shipped To',
FORMAT ( orderdate, 'd', 'en-US' ) AS 'Date',
storename AS 'Store',
ordernumber AS 'Order ID',
customerlastname AS 'Last Name',
customerfirstname AS 'First Name',
/* ( customerfirstname + ' ' + customerlastname ) AS 'Customer name', */
/* JobNumber AS 'Job ID', */
JobName AS 'Job Name',
/*
productname AS 'Product Name',
Substring(documenttype, 1, 1) AS 'Product Type',
*/
Paper AS 'Paper Type',
TotalNumberOfPages AS '# Uploaded Pages',
/* */
SimplexPages,
DuplexPages,
QuantityPerRecipient,
NumRecipients,
( Isnull(pagesgray, 0)
+ Isnull(pagescolorforced, 0)
+Isnull(pagesgrayforced, 0)) * QuantityPerRecipient AS 'BW Clicks'
,
( Isnull(pagescolor, 0) ) * QuantityPerRecipient AS 'Color Clicks'
,
SimplexPages * TotalQuantity AS 'Simplex Sheets'
,
(( DuplexPages + ( DuplexPages% 2))/2) * TotalQuantity AS 'Duplex Sheets'
,
TotalPrice AS 'Cost'
FROM temp_cte
WHERE Substring(documenttype, 1, 1) = 'C'
/*GROUP BY 'Shipped To' */
/*ORDER BY orderdate */
/* end of procedure */
~~~