Ordered composite products with color definition and quantity - xmpie-users/uStore-js GitHub Wiki

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

Overview

The following report shows a example for the SQL query to fetch the ordered product and include the color definition of the composite / upload product with the amount and size of the pages.

This report will show the following columns:

• Order_ID
• cryptedordernumber
• storename
• productname
• documenttype
• PropertyColor
• Customername
• Papersize
• quantity
• pagesgray
• pagesgrayforced
• pagescolor
• pagescolorforced
• TotalNumberOfBlackPages
• TotalNumberOfColorPages
• orderdate
• shippingdate
• CustomProductProperty
• price

 DECLARE @enddate DATE
DECLARE @startdate DATE;

WITH temp_cte
     AS (SELECT orders.encryptedorderid
                AS
                   CryptedOrderNumber,
                delivery.creationdate
                AS
                   ShippingDate,
                orders.displayorderdate
                AS
                   OrderDate,
                orderproduct.orderproductid
                AS
                   JobReferenceNumber,
                orderproduct.totalprice
                AS
                   Price,
                store_culture.NAME
                AS
                   storename,
                users.firstname
                AS
                   customerFirstName,
                users.lastname
                AS
                   customerLastName,
                users.email
                AS
                   customerEmail,
                users.department,
                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
                   title,
                orderproduct.quantityperrecipient
                AS
                   quantity,
                (SELECT doctype.NAME
                 FROM   doctype
                 WHERE  doctypeid IN (SELECT doctypeid
                                      FROM   doc
                                      WHERE  doc.productid =
                                             orderproduct.productid))
                AS
                DocumentType,
                (SELECT dialvalue
                 FROM   orderproductdialvalue
                 WHERE  dialid IN (SELECT dialid
                                   FROM   dial_culture
                                   WHERE  friendlyname = 'CustomProductPropertyName'
                                          AND orderproductid IN (SELECT
                                              orderproductid
                                                                 FROM
                                              orderproduct AS duplexOrderNumber
                                                                 WHERE
                                              orderproductid =
                orderproduct.orderproductid)))
                AS
                   CustomProductProperty,
                (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,
(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
simplexOrderProduct
   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
WHERE  ( orderproduct.totalquantity >= 1 )
AND ( 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 ) ))
SELECT jobreferencenumber                             AS Order_ID,
       cryptedordernumber,
       storename,
       productname,
       Substring(documenttype, 1, 1)                  AS Type,
       ( CASE
           WHEN usecolor = 'True' THEN 'Color'
           WHEN usecolor = 'False' THEN 'Gray'
           ELSE NULL
         END )                                        AS PropertyColor,
       ( customerfirstname + ' ' + customerlastname ) AS Customername,
       title,
       ( CASE
           WHEN Substring(documenttype, 1, 1) = 'C' THEN uploadsize
           ELSE custompapersize
         END )                                        AS Papersize,
       quantity,
       pagesgray,
       pagesgrayforced,
       pagescolor,
       pagescolorforced,
       ( Isnull(pagesgray, 0)
         + Isnull(pagescolorforced, 0) ) * quantity   AS TotalNumberOfBlackPages
       ,
       ( Isnull(pagesgrayforced, 0)
         + Isnull(pagescolor, 0) ) * quantity         AS TotalNumberOfColorPages
       ,
       orderdate,
       shippingdate,
       CustomProductProperty,
       price
FROM   temp_cte
WHERE  ( documenttype = 'Composite'
         AND title IS NOT NULL )
        OR ( documenttype != 'Composite' )
ORDER  BY orderdate