Further Custom Script Examples

Overview
The following guide extends the set of examples on the main Custom Scripts page. Clicking on the image icon next to each title will show an image of the database structure for the relevant Linnworks database tables.

Table of Contents

Order Scripts

  Orders with a Refund

--Orders with a Refund
SELECT 
	o.nOrderId  AS 'Order ID',
	ISNULL(si.ItemNumber, oi.ChannelSKU)  AS 'SKU',
	ISNULL(si.ItemTitle, sis.cItemName)  AS 'Product Title',
	o.Source  AS 'Source', 
	o.SubSource  AS 'SubSource',
	o.fTotalCharge  AS 'Order Total', 
	o.dReceievedDate  AS 'Order Date', 
	orf.CreateDate  AS 'Refund Creation Date',
	o.cEmailAddress  AS 'Customer Email', 
	o.cFullName  AS 'Customer Name',
	oi.CostIncTax  AS 'Item Cost', 
	o.cCurrency  AS 'Currency',
	orf.Amount  AS 'Refund Cost',
	orf.Actioned  AS 'Refund Actioned',
	orf.Reason  AS 'Refund Note (Reason)'

FROM
	[Order]  AS o WITH (NOLOCK)
	INNER JOIN [OrderItem]  AS oi WITH (NOLOCK) ON o.pkOrderId = oi.fkOrderId
	INNER JOIN [StockItems]  AS sis WITH (NOLOCK) ON sis.pkStockId = oi.fkStockId
	LEFT OUTER JOIN [StockItem]  AS si WITH (NOLOCK) ON si.pkStockItemId = sis.fkStockControlStockItemId
	INNER JOIN [Order_Refund]  AS orf WITH (NOLOCK) ON orf.fkOrderId = o.pkOrderId and orf.fkOrderItemId = oi.rowid
ORDER BY
	o.CreatedDate, o.nOrderId
	

 Number of Processed Orders grouped by Year/Month and Postal Service

--Number of Processed Orders grouped by Year/Month and Postal Service

SELECT
	CAST(CAST(o.dProcessedOn AS DATE)  AS  VARCHAR(7)) AS 'DATE', 
	ps.PostalServiceName,
	COUNT(pkPostalServiceId) AS 'Qty',
	ROUND(CAST(SUM(o.fPostageCost) AS NUMERIC(12,2)),2) AS 'Postage Charged'
FROM 
	[Order] o WITH (NOLOCK)
	INNER JOIN PostalServices ps WITH (NOLOCK) ON ps.pkPostalServiceId = o.fkPostalServiceId
WHERE
	o.bProcessed=1 AND o.dProcessedOn != ''
GROUP BY
	CAST(CAST(o.dProcessedOn AS DATE) AS VARCHAR(7)),
	ps.PostalServiceName
ORDER BY
	CAST(CAST(o.dProcessedOn AS DATE) AS VARCHAR(7)) DESC,
	ps.PostalServiceName ASC

 Order Life History for Processed Orders Across A Date Range

--Order Life History for Processed Orders Across A Date Range

SET DATEFORMAT  YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME

SET @FromDate = GETDATE()-30
SET @ToDate = GETDATE()

SELECT 
	o.nOrderId,
	o.dProcessedOn,
	o.Source, 
	o.SubSource,
	h.fkOrderHistoryTypeId,
	h.HistoryNote,
	h.Tag, 
	h.UpdatedBy,
	h.DateStamp
FROM
	[Order] o WITH (NOLOCK)
	INNER JOIN Order_LifeHistory h WITH (NOLOCK) ON o.pkOrderId = h.fkOrderId
WHERE
	o.bProcessed = 1 AND o.dProcessedOn BETWEEN @FromDate AND @ToDate
ORDER BY
	h.DateStamp ASC

 Order Notes

--Order Notes for Orders Received Between Dates 

SET DATEFORMAT  YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME

SET @FromDate = GETDATE()-30
SET @ToDate = GETDATE()

SELECT 
	o.nOrderId,
	n.Note, 
	n.NoteUserName,
	n.NoteEntryDate
FROM
	[Order] o WITH (NOLOCK)
	INNER JOIN OrderNotes n WITH (NOLOCK)ON o.pkOrderId = n.fkOrderId
WHERE
	 o.dReceievedDate BETWEEN @FromDate AND @ToDate
ORDER BY
	o.nOrderId, n.NoteEntryDate	

 Packaging Group Use

	
--This report shows a count of orders that were processed using different packaging groups beween the given dates 

SET DATEFORMAT YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME

SET @FromDate = '2016.01.01 00:00:00'
SET @ToDate = GETDATE()

SELECT 
	UpdatedBy AS 'Name',
	PackageCategory AS 'Packaging',
	COUNT(UpdatedBy) AS 'Number of Items',
	MIN(dProcessedOn) AS 'First',
	MAX(dProcessedOn) AS 'Last', 
	'Time Diff (Hours)' = 
	CASE
		WHEN MIN(dProcessedOn) = MAX(dProcessedOn) THEN '0'
		ELSE CAST(ROUND((DATEDIFF(second, MIN(dProcessedOn),MAX(dProcessedOn))/60.0/60.0),2) AS NUMERIC(10,2))
	END,
	'Avg per Hour' = 
	CASE
		WHEN MIN(dProcessedOn) = MAX(dProcessedOn) THEN '0'
		ELSE CAST(ROUND(COUNT(UpdatedBy) / (DATEDIFF(second, MIN(dProcessedOn),MAX(dProcessedOn))/60.0/60.0),2) AS NUMERIC(10,2))
	END
FROM
	[Order] o
	LEFT OUTER JOIN Order_LifeHistory h ON o.pkOrderId = h.fkOrderId
	LEFT OUTER JOIN PackageGroups pg ON o.fkPackagingGroupId = pg.PackageCategoryId
WHERE		
	o.bProcessed=1
	AND o.dProcessedOn BETWEEN @FromDate AND @ToDate AND fkOrderHistoryTypeId = 'ORDER_PROCESSED'
GROUP BY
	UpdatedBy, 
	PackageCategory
ORDER BY 		
	UpdatedBy, 
	PackageCategory	

 Retrieve the folder orders were assigned to based on date range

--Retrieve the folder an order is assigned to

SET DATEFORMAT  YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME

SET @FromDate = '2016.01.01 00:00:00'
SET @ToDate = GETDATE()
SELECT
	o.nOrderId, 
	f.FolderName
FROM 
	[Order] o
	INNER JOIN Order_Folder f ON o.pkOrderId = f.fkOrderId
WHERE
	o.dReceievedDate BETWEEN @FromDate AND @ToDate
ORDER BY
	o.nOrderId,
	f.FolderName

Inventory Scripts

 Retrieve Stock from all Locations


--Stock Level in all Locations
SELECT 
	si.ItemNumber,
	si.ItemTitle,
	sLoc.Location,
	sl.Quantity,
	sl.MinimumLevel,
	sl.InOrderBook,
	sl.OnOrder,
	il.BinRackNumber
FROM
	[StockItem] si
	LEFT OUTER JOIN StockLevel sl ON si.pkStockItemId = sl.fkStockItemId
	LEFT OUTER JOIN StockLocation sLoc ON sl.fkStockLocationId = sLoc.pkStockLocationId
	LEFT OUTER JOIN ItemLocation il ON il.fkStockItemId = si.pkStockItemId AND il.fkLocationId = sLoc.pkStockLocationId
WHERE
	si.bLogicalDelete = 0
ORDER BY
	si.ItemNumber, 
	sLoc.Location

 Retrieve Stock Item Extended Properties


--Retrieve the top 100 extended properties associated with stock items
SELECT TOP 100
	si.ItemNumber,
	si.ItemTitle,
	siep.ProperyName,
	siep.ProperyValue,
	siep.ProperyType
FROM
	[StockItem] si
	INNER JOIN StockItem_ExtendedProperties siep ON si.pkStockItemId = siep.fkStockItemId
ORDER BY 
	ItemNumber, 
	ProperyName	

 Retrieve Number of Stock Items in Each Category


--Retrieve the number of StockItems in each category
SELECT
	c.CategoryName,
	COUNT(c.CategoryName) AS 'Qty per Category'
FROM
	StockItem si
	INNER JOIN ProductCategories c ON si.CategoryId = c.CategoryId
WHERE
	si.bLogicalDelete = 0
GROUP BY 
	c.CategoryName
ORDER BY 
	c.CategoryName			

 Retrieve Channel Specific Titles, Prices and Descriptions


-- Retrieve Title, Price and Description as used by LinnLive when generating listings
DECLARE @Source NVARCHAR(25) = 'EBAY'
DECLARE @SubSource NVARCHAR(255) = 'EBAY1'

SELECT 
	si.ItemNumber, 
	t.Source,
	t.SubSource,
	t.Title,
	p.SalePrice,
	d.Description
FROM
	[StockItem] si
	INNER JOIN StockItem_Titles t ON si.pkStockItemId = t.fkStockItemId
	INNER JOIN StockItem_Descriptions d ON si.pkStockItemId = d.fkStockItemId
				AND t.Source = d.Source
				AND t.SubSource = d.SubSource
	INNER JOIN StockItem_Pricing p ON si.pkStockItemId = p.fkStockItemId
				AND t.Source = p.Source 
				AND t.SubSource = p.SubSource
WHERE
	si.bLogicalDelete = 0
	AND t.Source = @Source
	AND t.SubSource = @SubSource
ORDER BY
	si.ItemNumber

 Stock Item Audit Across a Date Range


--Stock Item Audit Across a Date Range
SET DATEFORMAT  YMD;

DECLARE
@FromDate AS DATETIME,
@ToDate AS DATETIME

SET @FromDate = '2016.01.01 00:00:00'
SET @ToDate = GETDATE()

SELECT
	si.ItemNumber,
	si.ItemTitle,
	sia.AuditTrailDate,
	sia.AuditType,
	sia.AuditText,
	sia.UserName
FROM
	[StockItem] si
	INNER JOIN StockItem_Audit sia ON si.pkStockItemId = sia.fkStockItemId
WHERE
	sia.AuditTrailDate BETWEEN @FromDate AND @ToDate
ORDER BY
	sia.AuditTrailDate ASC

Further Reading