
-- =============================================
-- Author:	 Graham Rook	
-- Create date: 22 July 2016
-- Description:	Get Sales Data
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesData]
	(
		@SurfaceItemID int,
		@Type as varchar(10),
		@DateFrom as varchar(15),
		@DateTo as varchar(15)
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @Query As Varchar(MAX)

	SET @Query = '    
	SELECT	surfaceItemId, dateOfService, name, surname, itemType,  
		invoiceNo, receiptNo, reference,
		CASE WHEN itemType = ''PM'' THEN ''RCT'' + CAST(receiptNo AS VARCHAR(20)) 
		WHEN itemType = ''PR'' THEN ''REV'' + CAST(receiptNo AS VARCHAR(20)) 
		WHEN itemType = ''QT'' THEN ''QTE'' + CAST(invoiceNo AS VARCHAR(20)) 
		WHEN itemType = ''CN'' THEN ''CRN'' + CAST(invoiceNo AS VARCHAR(20))
		ELSE ''INV'' + CAST(invoiceNo AS VARCHAR(20)) END [Number],
		CASE WHEN SUM(amount) < 0 THEN (SUM(amount * -1)) ELSE 0 END [Received],
		CASE WHEN SUM(amount) > 0 AND itemType <> ''QT'' THEN SUM(amount) ELSE 0 END [Invoiced],
		CASE WHEN SUM(amount) > 0 AND itemType = ''QT'' THEN SUM(amount) ELSE 0 END [Quoted],
		SUM(amount) [Amount],
		SUM(allocated) [allocated],

		CASE WHEN  itemType = ''TI'' THEN SUM (amount - allocated) ELSE 0 END AS [Due]
	 FROM pal_Sales  WHERE surfaceItemId = surfaceItemId '

	 --Where
	 if @SurfaceItemID > 0
		 BEGIN
		 SET @Query = @Query + ' AND surfaceItemId = ' + CAST(@SurfaceItemID AS VARCHAR(50)) + ' '
		 END

	IF @Type <> ''
		BEGIN
		 SET @Query = @Query + ' AND itemType = ''' + @Type + ''' '
		 END

	IF @DateFrom <> ''
		BEGIN
		 SET @Query = @Query + ' AND dateOfService >= ''' + @DateFrom + ''' '
		 END

	IF @DateTo <> ''
		BEGIN
		 SET @Query = @Query + ' AND dateOfService <= ''' + @DateTo + ''' '
	END

	 SET @Query = @Query + ' 
	 GROUP BY surfaceItemId, name, surname, itemType, dateOfService, invoiceNo, receiptNo, reference '

	 SET @Query = @Query + '
	 ORDER BY dateOfService DESC '
	 
	 --	 CASE WHEN invoiceNo > 0 THEN CAST(invoiceNo AS VARCHAR(20)) ELSE itemType END
	 Exec(@Query)
	

END







