Adding Clearing to Order Item Listing report

[Ashley Sledge]

I'm illiterate when it comes to SQL language for the most part - can anyone help with how I would go about adding the clearing option selected per order to the Order Item Listing report that's setup in ustore?

[couch]

In uStore admin:

* Presets -> System Setup -> Reports

* Locate the "Order Item Listing" report (ID 16)

* Click the edit icon on that report row.

* Click the edit localized text button

* Click the edit icon next to the required language

* Note the name of the report command "Report_ListOrderItems"

* Leave the browser open at this page and go open SQL Management application on you uStore SQL server

* Create a backup of your uStore SQL database in case something goes wrong.

* In the left pane, navigate to: Databases -> uStore -> Programmability -> Stored Procedures 

* Right-click on the Report_ListOrderItems and select Script stored procedure as -> Create to -> New query editor window

* In the create procedure line - change the name of your new procedure to something relevant - in the sample code below i changed to "Report_ListOrderItems_withClearing"

* Add the additional table and field to the report.

(In place of the last two steps, since you're not familiar with SQL, delete the automatically created script and use the sql script at the end:)

* Execute the SQL script to create the new stored procedure.

* Now back in the browser change the name of the report command to the new stored procedure name Report_ListOrderItems_withClearing

(Note that this process does not alter the original Stored Procedure, so you can always swap back the report command if necessary)

USE [uStore]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[Report_ListOrderItems_withClearing]

(

@MallID int,

@ActiveUserID int,

@StoreID int = NULL,

@StartDate datetime = NULL,

@EndDate datetime = NULL

)

AS

DECLARE @CultureID int

SET @CultureID = dbo.fn_StoreSetupCulture(@StoreID)

SELECT

OrderProduct.OrderProductID AS 'Item ID',

Orders.EncryptedOrderId AS 'Order ID',

Product_Culture.Name AS 'Product Name',

CASE 

WHEN DOC.DocTypeID in (6,7)

THEN 'True'

ELSE 'False'

END AS 'Includes URL',

OrderProduct.PurlPortName AS 'Port Name',

Users.FirstName + ' ' + Users.LastName AS 'Customer Name',

Orders.OrderAmount AS 'Subtotal Order Price',

Orders.BillAmount AS 'Total Order Price',

Orders.Bill_Add1, Orders.Bill_Add2,

Orders.Bill_AddressReference,

Orders.Bill_City,

Orders.Bill_Company,

Orders.Bill_Email,

Orders.Bill_Fax,

Orders.Bill_Name,

Orders.Bill_Phone,

Orders.Bill_Zip,

Bill_StateName.Name AS 'Bill State Name',

Bill_CountryName.Name AS 'Bill Country Name',

Product_Culture.ProductID,

ISNULL(CAST(DeliveryItem.QuantityPerRecipient AS nvarchar(50))

+ ' '

+ dbo.fn_GetProductUnitName(DeliveryItem.QuantityPerRecipient, OrderProduct.ProductUnitID, @CultureID),

'-')

AS 'Quantity Per Recipient',

OrderProduct.NumRecipients,

CASE

WHEN DeliveryItem.QuantityPerRecipient IS NULL

THEN CAST(OrderProduct.TotalQuantity AS nvarchar(50)) + ' ' + dbo.fn_GetProductUnitName(OrderProduct.TotalQuantity, OrderProduct.ProductUnitID, @CultureID)

ELSE CAST(OrderProduct.NumRecipients * DeliveryItem.QuantityPerRecipient AS nvarchar(50)) + ' ' + dbo.fn_GetProductUnitName(OrderProduct.NumRecipients * DeliveryItem.QuantityPerRecipient, OrderProduct.ProductUnitID, @CultureID)

END AS 'Total Quantity',

OrderProduct.PricePerRecipient,

OrderProduct.ProductPriceSubtotal,

OrderProduct.ShippingPriceSubtotal,

OrderProduct.TotalPrice AS 'Total Item Price',

OrderProduct.DateAdded,

Orders.DateOrderCreated,

DeliveryMethod.Name AS 'Delivery Method',

DPS.Name AS 'Delivery Service',

OrderProduct.RecipientListPrice,

OrderProduct.ApprovalRejectNotes,

OrderProduct.Cost AS 'Item Cost',

OrderProduct.TaxAmount AS 'Item Tax Amount',

ClearingConfig.Name as 'Clearing Method',

DeliveryTentative.DeliveryTentativeID,

DeliveryItem.DeliveryItemID,

DeliveryTentative.Ship_Add1,

DeliveryTentative.Ship_Add2,

DeliveryTentative.Ship_AddressReference,

DeliveryTentative.Ship_City,

DeliveryTentative.Ship_Company,

DeliveryTentative.Ship_Fax,

DeliveryTentative.Ship_Name,

DeliveryTentative.Ship_Phone,

Ship_StateName.Name AS 'Ship State Name',

Ship_CountryName.Name AS 'Ship Country Name',

DeliveryTentative.Ship_Zip

FROM OrderProduct

JOIN Orders ON Orders.OrderID = OrderProduct.OrderID

JOIN Product_Culture ON OrderProduct.ProductID = Product_Culture.ProductID

AND Product_Culture.CultureID = @CultureID

JOIN DOC ON Doc.ProductID = OrderProduct.ProductID

JOIN DeliveryItem ON DeliveryItem.OrderProductID = OrderProduct.OrderProductID

JOIN DeliveryTentative ON DeliveryTentative.DeliveryTentativeID = DeliveryItem.DeliveryTentativeID

JOIN DeliveryMethod ON DeliveryMethod.DeliveryMethodId = OrderProduct.DeliveryMethodId

JOIN DeliveryProviderService DPS ON DPS.DeliveryProviderServiceID = DeliveryTentative.DeliveryServiceId

JOIN Users ON Users.UserID = Orders.UserID

JOIN fn_UserStores(@ActiveUserId, 12) US ON Orders.StoreId = US.StoreID

LEFT OUTER JOIN ClearingConfig on ClearingConfig.ClearingConfigId = Orders.PaymentMethodId

LEFT OUTER JOIN Province_Culture AS Bill_StateName ON Bill_StateName.ProvinceId = Orders.Bill_State

AND Bill_StateName.CultureId = @CultureID

LEFT OUTER JOIN Province_Culture AS Ship_StateName ON Ship_StateName.ProvinceId = DeliveryTentative.Ship_State

AND Ship_StateName.CultureId = @CultureID

LEFT OUTER JOIN Country_Culture AS Bill_CountryName ON Bill_CountryName.CountryId = Orders.Bill_Country

AND Bill_CountryName.CultureID = @CultureID

LEFT OUTER JOIN Country_Culture AS Ship_CountryName ON Ship_CountryName.CountryId = DeliveryTentative.Ship_Country

AND Ship_CountryName.CultureID = @CultureID

WHERE

Orders.StatusID = 1

AND Orders.IsCart = 0

AND Orders.IsSaveForLater = 0

AND OrderProduct.IsDraft = 0

AND OrderProduct.StatusID = 1

AND OrderProduct.ParentOrderProductID IS NULL

AND (Orders.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 = '')

GO

[Ashley Sledge]

Thank you, Couch!

Is there somewhere online where I can go to learn SQL?

[AnwarG]

You could give w3schools a try, usually pretty decent tutorials to get you started.

https://www.w3schools.com/sql/default.asp