

-- =============================================
-- Author:		Graham Rook
-- Create date: 25 July 2016
-- Description:	Get Sales Invoices that are still due
-- =============================================
-- Modified By:	Charlene van Heerden
-- Date:		23 August 2016
-- Description:	Allow allocatedRefence '0' or ''. Group by formatted date, Order by invoice number.
--				Don't return isVisible=false
-- =============================================
-- Modifier:	Charlene van Heerden
-- Update Date:	2016-09-14
-- Description:	Transaction Number pad with 0's
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesQuotesToAllocate]
	-- Add the parameters for the stored procedure here
	@SurfaceItemID int = 0
	,@includeAllocated BIT = 0
AS
BEGIN

	Declare @QTEPrefix As Varchar(50) 
	Declare @QTELength As Varchar(50) 

	SET @QTEPrefix = (Select Top 1 ISNULL(Nullif(saleQTEPrefix, ''), 'QTE') FROM pal_Setup)
	SET @QTELength = CAST((Select Top 1 ISNULL(saleQTENumLength, 0) FROM pal_Setup) AS VARCHAR(50))

	IF(@includeAllocated=0)
	BEGIN
		SELECT	  
			invoiceNo As quoteNo,
			CONVERT(varchar(10), dateOfService,126) + ' (' + @QTEPrefix + ISNULL(REPLICATE('0', @QTELength - (LEN(RTRIM(invoiceNo)) + LEN(@QTEPrefix))), '') + RTRIM(invoiceNo) + '=' + CONVERT(VARCHAR,CONVERT(MONEY,SUM(amount)),1) + ')' AS Display
		 FROM pal_Sales
		 WHERE itemType = 'QT' AND allocatedReference IN ('0','') AND surfaceItemId = CASE WHEN @SurfaceItemID = 0 THEN surfaceItemId ELSE @SurfaceItemID END
		 AND isVisible = 1 and isDraft = 0
		 GROUP BY surfaceItemId, name, surname, itemType, CONVERT(varchar(10), dateOfService,126), invoiceNo, receiptNo	 
		 ORDER BY invoiceNo DESC
	 END
	 ELSE
	 BEGIN
		SELECT	  
			invoiceNo As quoteNo,
			CONVERT(varchar(10), dateOfService,126) + ' (' + @QTEPrefix + ISNULL(REPLICATE('0', @QTELength - (LEN(RTRIM(invoiceNo)) + LEN(@QTEPrefix))), '') + RTRIM(invoiceNo) + '=' + CONVERT(VARCHAR,CONVERT(MONEY,SUM(amount)),1) + ')' AS Display
		 FROM pal_Sales
		 WHERE itemType = 'QT' AND surfaceItemId = CASE WHEN @SurfaceItemID = 0 THEN surfaceItemId ELSE @SurfaceItemID END
		 AND isVisible = 1 and isDraft = 0
		 GROUP BY surfaceItemId, name, surname, itemType, CONVERT(varchar(10), dateOfService,126), invoiceNo, receiptNo	 
		 ORDER BY invoiceNo DESC
	 END
END










GO


