-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[backup__fds__fn_unpaidInvoices]
(	
	
)
RETURNS TABLE 
AS
RETURN 
(
	WITH inv as (
	SELECT [Id] = cast([id] as varchar(15))
				,[Invoiceid]
				,[DateFinalized]
				,[InvoiceBalance]
				,[DocumentName]
				,[file]
				,[InvoiceTitle]
				,[customerid]
				,[dateSent]
				,[fds] = CAST(1 as bit) 
			FROM [dbo].[fds__invoices] as iv
				WHERE [isfinal] = 1 and CAST(CASE WHEN ISNULL(iv.[IsCanceled],0) = 1 THEN 1 ELSE ISNULL([IsPayed],0) END  as bit) = 0
					and InvoiceBalance > 0
			UNION
			SELECT [Id] = cast([id] as varchar(15))
				,[Invoiceid]
				,[DateFinalized] = [DateOfCreation]
				,[InvoiceBalance]
				,[DocumentName]
				,[file] = NULL
				,[InvoiceTitle] = ''
				,[customerid] = [dbo].[fds__fn_invoice_customerid](mfri.[id])
				,[datesent] = NULL
				,[fds] = CAST(0 as bit) 
			FROM [dbo].[mfr__invoices] as mfri
					LEFT JOIN [dbo].[fds__custom_invoiceinfo] as ivi on mfri.[id] = ivi.[invid]
			WHERE [invoicestate] in ('eIsSent','eIsOpen')
				AND ISNULL(ivi.isPayed,0) = 0
				AND not [invoiceId] like 'preview'
				and InvoiceBalance > 0
	)
	SELECT *  /*[Rechnung] = [invoiceId], [Name] = [InvoiceTitle] + CHAR(10) + '  (' + (SELECT TOP(1) c.[name] FROM [dbo].[mfr__companies] as c WHERE inv.[customerid] = c.[id]) + ')', [Betrag (net)] = FORMAT([invoicebalance], '#0.00€', 'de'), [Datum] = FORMAT([DateFinalized], 'dd.MM.yy' , 'de'), [Zahlungen] = FORMAT([dbo].[fds__fn_InvoicePaymentAmount]([id]), '#0.00€', 'de'), [order] = ROW_NUMBER() OVER (ORDER BY [DateFinalized], [DateSent]) */
	FROM inv
)