-- =============================================
-- Author:		Jas Rademeyer
-- Create date: 28 July 2016
-- Description:	Get Receipts For a SurfaceItemId
-- =============================================
CREATE PROCEDURE [dbo].[sp_GetSalesReceipts]
	-- Add the parameters for the stored procedure here
	@SurfaceItemID int
AS
BEGIN
	SELECT	  
		S.receiptNo,
		CONVERT(varchar(10), S.dateOfService,126) + '-RCT' + CONVERT(varchar(50),S.receiptNo) + '-Paid:' + CONVERT(varchar(50),SUM(S.amount)) + ' on Invoice # INV'+ CONVERT(varchar(50),T.invoiceNo)  AS Display
	 FROM pal_Sales S
		LEFT JOIN pal_Sales T
			ON T.recId IN (SELECT (CASE WHEN SUBSTRING(splitdata,0,CHARINDEX(':',splitdata))='' THEN splitdata ELSE SUBSTRING(splitdata,0,CHARINDEX(':',splitdata)) END) FROM dbo.SplitString(CASE WHEN S.itemType = 'PM' THEN S.allocatedReference ELSE '' END,','))
	 WHERE S.itemType = 'PM' AND S.surfaceItemId = @SurfaceItemID
	 GROUP BY S.surfaceItemId, S.name, S.surname, S.itemType, S.dateOfService, S.invoiceNo, S.receiptNo,T.invoiceNo
	 ORDER BY S.dateOfService DESC
END

