-- =============================================
-- 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
-- =============================================
-- Modifier:	Graham Rook
-- Update Date:	2016-09-06
-- Description:	Improvements and working Activity columns
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesData]
	(
		@SurfaceItemID int,
		@Type as varchar(100),
		@DateFrom as varchar(15),
		@DateTo as varchar(15),
		@Statuses as varchar(150)
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	Declare @Query As Varchar(MAX)

	Declare @QTEPrefix As Varchar(50) 
	Declare @INVPrefix As Varchar(50) 
	Declare @RCTPrefix As Varchar(50) 
	Declare @CRNPrefix As Varchar(50) 
	Declare @REVPrefix As Varchar(50) 

	SET @QTEPrefix = (Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup)
	SET @INVPrefix = (Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup)
	SET @RCTPrefix = (Select Top 1 ISNULL(Nullif(saleRCTPrefix, ''), 'RCT') FROM pal_Setup)
	SET @CRNPrefix = (Select Top 1 ISNULL(Nullif(saleCRNPrefix, ''), 'CRN') FROM pal_Setup)

	SET @Query = '    
	SELECT	surfaceItemId, dateOfService, dateDue, name, surname, S.itemType,  
			invoiceNo, receiptNo, reference,
			CASE WHEN S.itemType = ''PM'' THEN ''' + @RCTPrefix + ''' + CAST(receiptNo AS VARCHAR(20))
			
			WHEN S.itemType = ''QT'' THEN ''' + @QTEPrefix + ''' + CAST(invoiceNo AS VARCHAR(20)) 
			WHEN S.itemType = ''CN'' THEN ''' + @CRNPrefix + ''' + CAST(invoiceNo AS VARCHAR(20))
			ELSE ''' + @INVPrefix + ''' + 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 * -1)) 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'' OR s.itemType = ''CN'' THEN SUM(amount*-1) ELSE SUM(amount) END [Amount],
			SUM(allocated) [allocated],
			CASE WHEN  S.itemType = ''TI'' OR  S.itemType = ''PM'' OR  S.itemType = ''CN'' THEN SUM (Abs(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 WHEN SUM (allocated) = 0
								THEN ''Unallocated''								
								ELSE CASE WHEN SUM (Abs(amount) - allocated) > 0
									THEN ''Partially Allocated''							
									ELSE ''Allocated''
									END
								END
						WHEN ''PM''
							THEN CASE WHEN SUM (allocated) = 0
								THEN ''Unallocated''								
								ELSE CASE WHEN SUM (Abs(amount) - allocated) > 0
									THEN ''Partially Allocated''							
									ELSE ''Allocated''
									END
								END
						ELSE ''UNKNOWN''
					END 
				END AS saleStatus, 
				CASE 
				WHEN S.itemType = ''QT'' THEN 
					''Quote ' + @QTEPrefix + ''' + CAST(S.invoiceNo AS VARCHAR(20)) +
					ISNULL((SELECT TOP 1 CASE WHEN a.allocatedReference = ''0'' OR a.allocatedReference = '''' 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 
					''Invoice ' + @INVPrefix + ''' + CAST(S.invoiceNo AS VARCHAR(20)) +
					ISNULL((SELECT TOP 1 '' (' + @QTEPrefix + ''' + 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 = ''' + @INVPrefix + ''' + CAST(S.invoiceNo AS VARCHAR(20))),'''') 
				WHEN S.itemType = ''PM'' THEN 
					''Receipt ' + @RCTPrefix + ''' + CAST(S.receiptNo AS VARCHAR(20)) +
					ISNULL((SELECT '' ('' + (SELECT stuff((
						SELECT DISTINCT '',' + @INVPrefix + ''' + Cast(a.invoiceNo AS varchar) + ''''  fROM pal_Sales a
						WHERE '','' + (SELECT allocatedReference FROM pal_Sales Where receiptNo = S.receiptNo AND itemType = ''PM'') + '','' LIKE ''%,'' + CAST(recId AS varchar) + '',%''
						FOR XML PATH('''')
						), 1, 1, ''''))+ '')''),'''')	
				WHEN S.itemType = ''CN'' THEN 
					''Credit Note ' + @CRNPrefix + ''' + CAST(S.invoiceNo AS VARCHAR(20)) +
					ISNULL((SELECT '' ('' + (SELECT stuff((
						SELECT DISTINCT '','+ @INVPrefix +''' + Cast(a.invoiceNo AS varchar) + ''''  fROM pal_Sales a
						WHERE '','' + (SELECT allocatedReference FROM pal_Sales Where invoiceNo = S.invoiceNo AND itemType = ''CN'') + '','' LIKE ''%,'' + CAST(recId AS varchar) + '',%''						
						FOR XML PATH('''')
						), 1, 1, ''''))+ '')''),'''')							
				ELSE '''' END AS Activity
	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, dateOfService, dateDue, invoiceNo, receiptNo, reference,isDraft,ISNULL(SS.status,''UNKNOWN'') '

	IF @Statuses <> ''
	BEGIN
		SET @Query = @Query + 'Having 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 WHEN SUM (allocated) = 0
								THEN ''Unallocated''								
								ELSE CASE WHEN SUM (Abs(amount) - allocated) > 0
									THEN ''Partially Allocated''							
									ELSE ''Allocated''
									END
								END
						WHEN ''PM''
							THEN CASE WHEN SUM (allocated) = 0
								THEN ''Unallocated''								
								ELSE CASE WHEN SUM (Abs(amount) - allocated) > 0
									THEN ''Partially Allocated''							
									ELSE ''Allocated''
									END
								END
						ELSE ''UNKNOWN''
					END 
				END IN (''' + @Statuses + ''') '
	END

	 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







