Fuchs_Intranet/Fuchs_Database/dbo/Stored Procedures/fds__setInvoice.sql

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[fds__setInvoice]
	@InvId varchar(10),
	@InvoiceType char(1),
	@InvoiceTitle varchar(100),
	@InvoiceBalance numeric(10,3),
	@InvoiceBalance_net numeric(10,3),
	@InvoiceVAT_net1 numeric(10,3),
	@InvoiceVAT_1 numeric(5,2),
	@PaymentTerm varchar(5),
	@CustomerId bigint,
	@SendToAddress nvarchar(1000),
	@SendToEmail nvarchar(255),
	@ProvisionPeriod varchar(50), 
	@CustomValues nvarchar(max),
	@authuser varchar(25),
	@Id varchar(10) OUT
AS
BEGIN
	SET NOCOUNT ON;

	IF [dbo].[fis_getModuleAuth]('fds_inv', @authuser) < 2
		THROW 60000, N'not authorized', 1;  
	
	INSERT INTO [dbo].[fds__admin_activity] ([activity] ,[authuser] ,[info])
		VALUES ('fds__setInvoice' ,@authuser , '');
	

	DECLARE @now datetime = GETUTCDATE();
	DECLARE @today date = @now;
	SET @PaymentTerm = ISNULL(@PaymentTerm, '10wd');
	DECLARE @days tinyint = ISNULL(TRY_PARSE(REPLACE(REPLACE(@PaymentTerm, 'w',''),'d','') as tinyint), 10);
	DECLARE @duedate date = CASE WHEN RIGHT(@PaymentTerm, 2) = 'wd' THEN DATEADD(weekday, @days, @today) ELSE DATEADd(DAY, @days, @today) END;

	DECLARE @newid varchar(8) = [dbo].[fds__fn_invoice_id]();
	
	DECLARE @out TABLE (
		[Id] [varchar](10) NULL,
		[Version] [int] NULL,
		[InvoiceId] [nvarchar](25) NULL,
		[InvoiceType] [char](1) NULL,
		[InvoiceTitle] [varchar](100) NULL,
		[DocumentName] [varchar](50) NULL,
		[InvoiceBalance] [numeric](10, 3) NULL,
		[InvoiceBalance_net] [numeric](10, 3) NULL,
		[InvoiceVAT_net1] [numeric](10, 3) NULL,
		[InvoiceVAT_1] [numeric](5, 2) NULL,
		[InvoiceVAT_net2] [numeric](10, 3) NULL,
		[InvoiceVAT_2] [numeric](5, 2) NULL,
		[PaymentTerm] [varchar](5) NULL,
		[DueDate] [date] NULL,
		[CustomerId] [bigint] NULL,
		[SendToAddress] [nvarchar](1000) NULL,
		[SendToEmail] [nvarchar](255) NULL,
		[ProvisionPeriod] [varchar](50) NULL,
		[ProvisionLocation] [nvarchar](1000) NULL,
		[PaymentStatus] [char](1) NULL,
		[IsPayed] [bit] NULL,
		[IsSent] [bit] NULL,
		[IsFinal] [bit] NULL,
		[IsCanceled] [bit] NULL,
		[Replaces_InvId] [varchar](50) NULL,
		[CustomValues] [nvarchar](max) NULL,
		[DateSent] [datetime] NULL,
		[UserSent] [varchar](25) NULL,
		[DateFinalized] [datetime] NULL,
		[UserFinalized] [varchar](25) NULL,
		[DateCancelled] [datetime] NULL,
		[UserCancelled] [varchar](25) NULL,
		[DateCreated] [datetime] NULL,
		[UserCreated] [varchar](25) NULL,
		[DateModified] [datetime] NULL,
		[UserModified] [varchar](25) NULL,
		[ExternalId] [varchar](25) NULL,
		[isExternal] [bit] NULL,
		[file] [varbinary](max) NULL,
		[file_guid] uniqueidentifier NULL
	);


    MERGE [dbo].[fds__invoices] as TARGET
		USING (VALUES
           (@InvId
		   ,ISNULL(@InvoiceType,'r')
		   ,REPLACE(REPLACE(REPLACE(ISNULL(@InvoiceTitle,'Rechnung'), '&nbsp;',''),'<p>', ''),'</p>','')
           ,@InvoiceBalance
		   ,@InvoiceBalance_net
           ,@InvoiceVAT_net1
           ,@InvoiceVAT_1
		   ,@PaymentTerm
           ,@DueDate
           ,@CustomerId
		   ,@SendToAddress
           ,@SendToEmail
           ,@ProvisionPeriod
		   ,NULL --[ProvisionLocation]
		   ,NULL --[PaymentStatus]
           ,0 --<IsPayed, bit,>
           ,0 --<IsSent, bit,>
           ,@CustomValues
		   , NULL  --[DateSent]
		   , NULL --[UserSent] [varchar](25) NULL,
           ,NULL --<DateFinalized, datetime,>
           ,NULL --<UserFinalized, varchar(25),>
           ,NULL --<DateCancelled, datetime,>
           ,NULL --<UserCancelled, varchar(25),>
           ,@now
           ,@authuser
            ,@now
           ,@authuser
		   ,NULL -- externalid
		   ,0 --isexternal
		   ,NULL --file
		  )) SOURCE  ([Id]
		   ,[InvoiceType]
		   ,[InvoiceTitle]
           ,[InvoiceBalance]
		   ,[InvoiceBalance_net]
           ,[InvoiceVAT_net1]
           ,[InvoiceVAT_1]
		   ,[PaymentTerm]
           ,[DueDate]
           ,[CustomerId]
           ,[SendToAddress]
           ,[SendToEmail]
           ,[ProvisionPeriod]
           ,[ProvisionLocation]
		   ,[PaymentStatus]
           ,[IsPayed]
           ,[IsSent]
           ,[CustomValues]
		   ,[DateSent]
		   ,[UserSent]
           ,[DateFinalized]
           ,[UserFinalized]
           ,[DateCancelled]
           ,[UserCancelled]
           ,[DateCreated]
           ,[UserCreated]
           ,[DateModified]
           ,[UserModified]
		   ,[externalId]
		   ,[isExternal]
		   ,[file]) ON TARGET.[Id] = SOURCE.[Id]
	WHEN MATCHED AND TARGET.[IsFinal] = 0 AND TARGET.[file] is null THEN 
           UPDATE SET 
		   [Version] = [Version] + 1
		   ,[InvoiceType] =SOURCE.[InvoiceType]
		   ,[InvoiceTitle] = SOURCE.[InvoiceTitle]
           ,[InvoiceBalance] = SOURCE.[InvoiceBalance]
		   ,[InvoiceBalance_net] = SOURCE.[InvoiceBalance_net]
           ,[InvoiceVAT_net1] = SOURCE.[InvoiceVAT_net1]
           ,[InvoiceVAT_1] =SOURCE. [InvoiceVAT_1]
		   ,[PaymentTerm] = SOURCE.[PaymentTerm]
           ,[DueDate] = SOURCE.[DueDate]
           ,[CustomerId] = SOURCE.[CustomerId]
           ,[SendToAddress] = SOURCE.[SendToAddress]
           ,[SendToEmail] = SOURCE.[SendToEmail]
           ,[ProvisionPeriod] = SOURCE.[ProvisionPeriod]
           ,[ProvisionLocation] = SOURCE.[ProvisionLocation]
           ,[CustomValues] = SOURCE.[CustomValues]
           ,[DateModified] = SOURCE.[DateModified]
           ,[UserModified] = SOURCE.[UserModified]
	 OUTPUT inserted.*
		INTO @out;
     

	SET @Id = @InvId;

	SELECT TOP(1) *
	  ,[hasFile] = CAST (  CASE WHEN [file] is null THEN 0 ELSE 1 END as bit)
	  ,[UserNameFinalized] =  [dbo].[fis_admin_getUserName_byID]([UserFinalized])
	  ,[UserEmailFinalized] =  [dbo].[fis_admin_getUserEmail_byID]([UserFinalized])
	from [fds__invoices] WHERE [id] = @InvId;

END