-- =============================================
-- Author:	 Graham Rook	
-- Create date: 22 July 2016
-- Description:	Get Sales Data
-- Editor: Jas Rademeyer
-- Update Date: 19 August 2016
-- Description: Cater for extra filters
--				Calculate status per item
-- =============================================
-- Modifier:	Charlene van Heerden
-- Update Date:	2016-08-24
-- Description:	Include Related To field (Quote/Invoice)
--				Exclude isVisible=false
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesData]
	(
		@SurfaceItemID int,
		@Type as varchar(100),
		@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, CONVERT(date, dateOfService) dateOfService,CONVERT(date, dateDue), name, surname, S.itemType,  
			invoiceNo, receiptNo, reference,
			CASE WHEN S.itemType = ''PM'' THEN ''RCT'' + CAST(receiptNo AS VARCHAR(20)) 
			WHEN S.itemType = ''PR'' THEN ''REV'' + CAST(receiptNo AS VARCHAR(20)) 
			WHEN S.itemType = ''QT'' THEN ''QTE'' + CAST(invoiceNo AS VARCHAR(20)) 
			WHEN S.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 S.itemType = ''PM'' THEN (SUM(amount * -1)) ELSE 0 END [Payments],
			CASE WHEN S.itemType = ''CN'' THEN (SUM(amount)) ELSE 0 END [CreditNotes],
			CASE WHEN SUM(amount) > 0 AND S.itemType <> ''QT'' THEN SUM(amount) ELSE 0 END [Invoiced],
			CASE WHEN SUM(amount) > 0 AND S.itemType = ''QT'' THEN SUM(amount) ELSE 0 END [Quoted],
			CASE WHEN S.itemType = ''PM'' THEN SUM(amount*-1) ELSE SUM(amount) END [Amount],
			SUM(allocated) [allocated],
			CASE WHEN  S.itemType = ''TI'' THEN SUM (amount - allocated) ELSE 0 END AS [Due],
			CAST(MIN(S.emailSent+0) AS BIT) AS emailSent,
			CASE WHEN isDraft = 1 THEN ''Draft''
				ELSE
					CASE S.itemType
						WHEN ''QT''
							THEN ISNULL(SS.status,''UNKNOWN'')
						WHEN ''TI''
							THEN CASE WHEN CONVERT(date, dateDue) < CONVERT(date, GetDate())
									THEN ''Overdue''
									ELSE CASE WHEN SUM (amount - allocated) = SUM(amount)
											THEN ''Unpaid''
											ELSE CASE WHEN SUM (amount - allocated) > 0
													THEN ''Partially Paid''
													ELSE CASE WHEN SUM(amount-allocated)=0
															THEN ''Paid''
															ELSE ISNULL(SS.status,''UNKNOWN'')
															END
													END
											END
									END
						WHEN ''CN''
							THEN CASE SUM(allocated)
								WHEN 0 THEN ''Unallocated''
								ELSE ''Allocated''
								END
						WHEN ''PM''
							THEN CASE SUM(allocated)
								WHEN 0 THEN ''Unallocated''
								ELSE ''Allocated''
								END
						ELSE ''UNKNOWN''
					END 
				END AS saleStatus, 
				CASE 
				WHEN S.itemType = ''QT'' THEN 
					ISNULL((SELECT TOP 1 CASE WHEN a.allocatedReference = ''0'' THEN '''' ELSE a.allocatedReference END 
						FROM pal_Sales a WHERE a.itemType = ''QT'' 
						AND a.surfaceItemId = S.surfaceItemId AND a.invoiceNo = S.invoiceNo),'''')  
				WHEN S.itemType = ''TI'' THEN 
					ISNULL((SELECT TOP 1 ''QTE'' + CAST(a.invoiceNo AS VARCHAR(20)) FROM pal_Sales a 
						WHERE a.itemType = ''QT'' AND a.surfaceItemId = S.surfaceItemId AND a.isVisible = 1
						AND a.allocatedReference = ''INV'' + CAST(S.invoiceNo AS VARCHAR(20))),'''')  
				ELSE '''' END AS RelatedTo
	FROM	pal_Sales S
			LEFT JOIN pal_SalesStatus SS
				ON SS.recId = S.statusId
	 WHERE surfaceItemId = surfaceItemId AND S.isVisible = 1 '

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

	IF @Type <> ''
		BEGIN
		 SET @Query = @Query + ' AND (LEN('''+@Type+''') = 0 OR S.itemType IN (SELECT splitdata FROM dbo.SplitString('''+@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, S.itemType, CONVERT(date, dateOfService), CONVERT(date, dateDue), invoiceNo, receiptNo, reference,isDraft,ISNULL(SS.status,''UNKNOWN'') '

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

END




GO


