-- =============================================
-- Author:		Graham Rook
-- Create date: 25 July 2016
-- Description:	Get Sales Invoices that are still due
-- =============================================
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) + '-Quoted:' + CONVERT(varchar(50),SUM(amount)) AS Display
		 FROM pal_Sales
		 WHERE itemType = 'QT' AND allocatedReference = '0' AND surfaceItemId = CASE WHEN @SurfaceItemID = 0 THEN surfaceItemId ELSE @SurfaceItemID END
		 GROUP BY surfaceItemId, name, surname, itemType, dateOfService, invoiceNo, receiptNo	 
		 ORDER BY dateOfService DESC
	 END
	 ELSE
	 BEGIN
		SELECT	  
			invoiceNo As quoteNo,
			CONVERT(varchar(10), dateOfService,126) + '-QTE' + CONVERT(varchar(50),invoiceNo) + '-Quoted:' + CONVERT(varchar(50),SUM(amount)) AS Display
		 FROM pal_Sales
		 WHERE itemType = 'QT' AND surfaceItemId = CASE WHEN @SurfaceItemID = 0 THEN surfaceItemId ELSE @SurfaceItemID END
		 GROUP BY surfaceItemId, name, surname, itemType, dateOfService, invoiceNo, receiptNo	 
		 ORDER BY dateOfService DESC
	 END
END



