A Cost Center clearing custom SQL report - 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

Out of the box, uStore provides a cost centre clearing method that enables customers to place orders and enter their cost center number. This report is useful for store administrators to aggregate and report (or claim) charges across different cost centers at the end of the month.

SELECT 
CONVERT(char(11),o.[DateOrderCreated],113) AS "Order Date",
CONVERT(char(5),o.[DateOrderCreated],108) AS "Time",
CAST(u.[FirstName] + ' ' + u.[LastName] AS varchar(100)) AS "Name",
CAST(o.[ClearingResult] AS XML).value('(/UserData//CostCenter/node())[1]', 'varchar(max)') AS "Cost Centre",
o.[EncryptedOrderID] AS "Order ID",
op.[TotalQuantity] AS "Total Pages",
CONVERT(decimal(10,2),o.[BillAmount]) AS "Total Price"
FROM 
[uStore].[dbo].[Orders] o
LEFT OUTER JOIN [uStore].[dbo].[Province_Culture] bst ON (o.[Bill_State] = bst.[ProvinceID] AND bst.[CultureID] = [uStore].[dbo].fn_StoreSetupCulture(o.[StoreID]))
INNER JOIN [uStore].[dbo].[Store] s ON (s.[StoreID]=o.[StoreID])
INNER JOIN [uStore].[dbo].[Store_Culture] sc on (s.[StoreID]=sc.[StoreID] and sc.[CultureID]=[uStore].[dbo].fn_StoreSetupCulture(o.[StoreID]))
LEFT OUTER JOIN [uStore].[dbo].[Customer] c ON (o.[CustomerID] = c.[CustomerID])
INNER JOIN [uStore].[dbo].[Users] u ON (c.[UserID] = u.[UserID])
LEFT OUTER JOIN [uStore].[dbo].[Coupon] cpn ON (o.CouponID = cpn.CouponID)
LEFT OUTER JOIN [uStore].[dbo].[CouponBatch] cpnb ON (cpn.CouponBatchID = cpnb.CouponBatchID)
LEFT OUTER JOIN [uStore].[dbo].[ClearingConfig] cc ON (o.PaymentMethodId = cc.ClearingConfigId)
LEFT OUTER JOIN [uStore].[dbo].[OrderProduct] op ON (o.OrderID = op.OrderID)
WHERE
o.[IsCart]=0 AND
op.[IsDraft]=0 AND
o.[IsSaveForLater]=0 AND
o.[StoreID]=@StoreID AND
o.[DateOrderCreated]>=@StartDate AND
o.[DateOrderCreated]<DATEADD(d,1,@EndDate) AND
op.[TotalQuantity] IS NOT NULL
ORDER BY 
o.[DateOrderCreated], u.[FirstName]