SET STATISTICS IO ON
SET STATISTICS TIME ON

SET DATEFORMAT dmy;
GO

DECLARE
@paramCompany			varchar(3),		
@paramSalesCategory		varchar(max),		
@paramSalesStatus		varchar(100),		
@paramSite				varchar(max),		
@paramVehicleType		varchar(max),	
@paramActShipDateFrom	datetime,
@paramActShipDateTo		datetime

SET @paramCompany			=	'abb'
SET @paramSalesCategory		=	'All' --,001,002,003,004,005,006,100,200,250,300,350,Classical,Ebay,Ebay SM,Landscape,OrdrSelect,Paver,Stonemarkt,StoneShip'
SET @paramSalesStatus		=	'99' --,Open order,Delivered'
SET @paramSite				=	'All' --,ABR,ALC,BBD,BFT,BLD,CAN,CAR,CHE,EAG,FAL,FEB,FLX,GEE,GGR,GMS,HAM,HDV,HEA,HIP,HLI,HLL,HPO,LLY,LSH,MAN,MLT,MRL,MSQ,MSS,MSU,NPT,NSH,NWL,PEN,PON,PTR,RBT,REG,RYT,SAN,SAW,SHN,SIT,SND,STN,STV,THL,WAF,WLA,ZZZ'
SET @paramVehicleType		=	'All' --@VehicleType
SET @paramActShipDateFrom	=	'01/01/2013' --@ActShipDateFrom
SET @paramActShipDateTo		=	'21/03/2013' --@ActShipDateTo

--Create SalesCategory temp table variable
DECLARE @SalesCategoryTable TABLE
(
	SalesCategory VARCHAR(100)
)

INSERT INTO @SalesCategoryTable (SalesCategory)
         SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramSalesCategory)

--Create SalesStatus temp table variable
DECLARE @SalesStatusTable TABLE
(
	SalesStatus VARCHAR(100)
)

INSERT INTO @SalesStatusTable (SalesStatus)
         SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramSalesStatus)

--Create Site temp table variable
DECLARE @SiteTable TABLE
(
	[SITE] VARCHAR(100)
)

INSERT INTO @SiteTable ([SITE])
         SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramSite)

--Create VehicleType temp table variable
DECLARE @VehicleTypeTable TABLE
(
	VehicleType VARCHAR(100)
)

INSERT INTO @VehicleTypeTable (VehicleType)
         SELECT item FROM dbo.udf_MultiValueParameterHandlingString(@paramVehicleType)


SELECT
	st.SALESID as 'SalesOrder',
	st.MARSALESCATEGORY as 'SalesCategory',
	st.MARVEHICLETYPE as 'VehicleType',
	st.MARSPECINST1 as 'SpecialInstructions1',
	st.MARSPECINST2 as 'SpecialInstructions2',
	st.SHIPPINGDATEREQUESTED as 'ExpShipDate',
	sohead.ShippedDate as 'ActShipDate',
	st.RECEIPTDATEREQUESTED as 'DelDate',
	st.CUSTACCOUNT as 'CustomerId',
	st.SALESNAME as 'CustomerName',
	st.DELIVERYNAME as 'DeliveryName',
	st.DELIVERYADDRESS as 'DeliveryAddress', 
	st.DELIVERYZIPCODE as 'PostCode',
	ctt.Telephone,
	CASE WHEN jz.ItemId is null THEN 'No Delivery Item' ELSE jz.ItemId END as 'ItemId',
	CASE WHEN jz.ItemDescription is null THEN 'No Delivery Item' ELSE jz.ItemDescription END as 'ItemDescription',
	jz.[Site],
	jz.Warehouse,
	ABS(isnull(jz.NoOfPallets,0)) as 'NoOfPallets',
	ABS(isnull(jz.NoOfQtrPallets,0)) as 'NoOfQtrPallets',
	'' as 'PalletsPicked',
	'' as 'QtrPalletsPicked',
	ABS(isnull(jz.Cost,0)) as 'Cost',
	'' as 'CostPayable',
	'' as 'Carrier',
	'' as 'Notes',
	CASE	WHEN st.SALESTYPE = 4 THEN 'Return Order'
			WHEN (isnull(sline.MinSalesQty,0) < 0 or isnull(sline.MinSalesQty,0) < 0) THEN 'Return Order'
			ELSE 'SalesOrder' END as 'SalesType',
	CASE	WHEN st.SALESTYPE = 4 THEN 'COLLECT'
			WHEN (isnull(sline.MinSalesQty,0) < 0 or isnull(sline.MinSalesQty,0) < 0) THEN 'COLLECT'
			ELSE 'DELIVER' END as 'Del/Col'
				
FROM
	DynamicsV5Realtime.dbo.SALESTABLE st
INNER JOIN
	(SELECT distinct
		tr.TRANSREFID as 'SalesId',
		tr.DATAAREAID,
		tr.DATEPHYSICAL as 'ShippedDate'
	FROM	
		DynamicsV5Realtime.dbo.INVENTTRANS tr
	WHERE
		tr.DATEPHYSICAL between @paramActShipDateFrom and @paramActShipDateTo
		and tr.DATAAREAID = @paramCompany) sohead
ON
	sohead.SalesId = st.SALESID
	and sohead.DATAAREAID = st.DATAAREAID	
INNER JOIN
	(SELECT
		sl.SALESID as 'SalesId',
		sl.DATAAREAID,
		MIN(sl.SALESQTY) as 'MinSalesQty'
	FROM	
		DynamicsV5Realtime.dbo.SALESLINE sl
	WHERE
		sl.DATAAREAID = @paramCompany
		and (sl.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
		and sl.SALESSTATUS IN(2,3)
	GROUP BY
		sl.SALESID,
		sl.DATAAREAID  ) sline
ON
	sline.SalesId = st.SALESID
	and sline.DATAAREAID = st.DATAAREAID
		
LEFT OUTER JOIN
	(SELECT
	sl.DATAAREAID,
	sl.SALESID as 'SalesId',
	sl.ITEMID as 'ItemId',
	sl.NAME as 'ItemDescription',
	id.INVENTSITEID as 'Site',
	id.INVENTLOCATIONID as 'Warehouse',
	CASE WHEN it.MARITEMFIXINGCODE = '001' THEN ABS(sum(tr.QTY))  ELSE 0 END as 'NoOfPallets',
	CASE WHEN it.MARITEMFIXINGCODE = '002' THEN ABS(sum(tr.QTY)) ELSE 0 END as 'NoOfQtrPallets',
	ABS(Sum(tr.QTY * itm.PRICE / CASE WHEN itm.PRICEUNIT = 0 THEN 1 ELSE itm.PRICEUNIT END)) as 'Cost',
	tr.DATEPHYSICAL as 'ShippedDate'
FROM
	DynamicsV5Realtime.dbo.SALESLINE sl
INNER JOIN
	DynamicsV5Realtime.dbo.SALESTABLE st
ON
	st.SALESID = sl.SALESID
	and st.DATAAREAID = sl.DATAAREAID
INNER JOIN
	DynamicsV5Realtime.dbo.INVENTTABLE it
ON
	it.ITEMID = sl.ITEMID
	and it.DATAAREAID = sl.DATAAREAID 
INNER JOIN
	DynamicsV5Realtime.dbo.INVENTTABLEMODULE itm
ON
	itm.ITEMID = sl.ITEMID
	and itm.DATAAREAID = sl.DATAAREAID 
	and itm.MODULETYPE = 1  -- purchase
LEFT OUTER JOIN
	DynamicsV5Realtime.dbo.INVENTDIM id
ON
	id.INVENTDIMID = sl.INVENTDIMID
	and id.DATAAREAID = sl.DATAAREAID 
LEFT OUTER JOIN
	DynamicsV5Realtime.dbo.INVENTTRANS tr
ON
	tr.INVENTTRANSID = sl.INVENTTRANSID
	and tr.DATAAREAID = sl.DATAAREAID
WHERE
	sl.DATAAREAID = @paramCompany
	and sl.ITEMID like 'JZ%'
	and st.SALESTYPE in(3,4)
	and (st.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
	and st.SALESSTATUS IN(2,3)
	and (sl.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
	and sl.SALESSTATUS IN(2,3)
	and (st.MARSALESCATEGORY IN(select SalesCategory from @SalesCategoryTable) or @paramSalesCategory = 'All')
	and (id.INVENTSITEID IN(select [SITE] from @SiteTable) or @paramSite = 'All')
	and (tr.DATEPHYSICAL between @paramActShipDateFrom and @paramActShipDateTo)
GROUP BY
	sl.DATAAREAID,
	sl.SALESID,
	sl.ITEMID,
	sl.NAME,
	id.INVENTSITEID,
	id.INVENTLOCATIONID,
	it.MARITEMFIXINGCODE,
	tr.QTY,
	tr.DATEPHYSICAL				) jz
	
ON
	jz.DATAAREAID = st.DATAAREAID
	and jz.SalesId = st.SALESID
	and jz.ShippedDate = sohead.ShippedDate
INNER JOIN
	(SELECT 
		ct.ACCOUNTNUM, 
		ct.DATAAREAID, 
		CASE WHEN ct.PHONE = '' THEN '' ELSE  'Tel: ' + ct.PHONE + '   ' END
		+ 
		CASE WHEN ct.CELLULARPHONE = '' THEN '' ELSE 'Mob: ' + ct.CELLULARPHONE END as 'Telephone'
	FROM
		DynamicsV5Realtime.dbo.CUSTTABLE ct
	WHERE
		ct.DATAAREAID = @paramCompany) ctt
ON
	ctt.DATAAREAID = st.DATAAREAID
	and ctt.ACCOUNTNUM = st.CUSTACCOUNT
WHERE
	st.DATAAREAID = @paramCompany
	and st.SALESTYPE IN(3,4)
	and (st.SALESSTATUS IN(select SalesStatus from @SalesStatusTable) or @paramSalesStatus = '99')
	and st.SALESSTATUS IN(2,3)
	and (st.MARSALESCATEGORY IN(select SalesCategory from @SalesCategoryTable) or @paramSalesCategory = 'All')
	and (st.INVENTSITEID IN(select [SITE] from @SiteTable) or @paramSite = 'All')
	and (st.MARVEHICLETYPE IN(select VehicleType from @VehicleTypeTable) or @paramVehicleType = 'All')
ORDER BY
	3,6,1,14

SET STATISTICS IO OFF
SET STATISTICS TIME OFF