

-- =============================================
-- 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
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesQuotesToAllocate]
	-- Add the parameters for the stored procedure here
	@SurfaceItemID int = 0
	,@includeAllocated BIT = 0
AS
BEGIN
	IF(@includeAllocated=0)
	BEGIN
		SELECT	  
			invoiceNo As quoteNo,
			CONVERT(varchar(10), dateOfService,126) + ' (QTE' + CONVERT(varchar(50),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
		 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) + ' (QTE' + CONVERT(varchar(50),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
		 GROUP BY surfaceItemId, name, surname, itemType, CONVERT(varchar(10), dateOfService,126), invoiceNo, receiptNo	 
		 ORDER BY invoiceNo DESC
	 END
END









