-- =============================================
-- Author:		Graham Rook
-- Create date: 25 July 2016
-- Description:	Get Sales Invoices that are still due
-- =============================================
-- Modifier:	Charlene van Heerden
-- Update Date:	2016-09-14
-- Description:	Transaction Number pad with 0's
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesInvoicesToAllocate]
	-- Add the parameters for the stored procedure here
	@SurfaceItemID int = 0
AS
BEGIN

	Declare @INVPrefix As Varchar(50) 
	Declare @INVLength As Varchar(50) 

	SET @INVPrefix = (Select Top 1 ISNULL(Nullif(saleINVPrefix, ''), 'INV') FROM pal_Setup)
	SET @INVLength = CAST((Select Top 1 ISNULL(saleINVNumLength, 0) FROM pal_Setup) AS VARCHAR(50))

	SELECT	  
		invoiceNo,
		CONVERT(varchar(10), dateOfService,126) + ' (' + @INVPrefix + ISNULL(REPLICATE('0', @INVLength - (LEN(RTRIM(invoiceNo)) + LEN(@INVPrefix))), '') + RTRIM(invoiceNo) + '=' + CONVERT(VARCHAR,CONVERT(MONEY,SUM(amount-allocated)),1) + ')' AS Display		
	 FROM pal_Sales
	 WHERE itemType = 'TI' AND surfaceItemId = CASE WHEN @SurfaceItemID = 0 THEN surfaceItemId ELSE @SurfaceItemID END
	 GROUP BY surfaceItemId, name, surname, itemType, dateOfService, invoiceNo, receiptNo
	 Having  SUM (amount - allocated) > 0
	 ORDER BY dateOfService DESC
END





