Stefan
10ecdfa2e4
Adds the SQL Server Data Tools project for the fuchs_fds database — tables, table types, functions and stored procedures that the backend calls (e.g. fds__getInvoice, fds__merge_bankingtransactions, fds__tt__bankingtransactions, fds__admin_getReportCatalog, fis_* auth). Build/model caches (bin, obj, *.dbmdl, *.jfm, *.user) are git-ignored. Co-Authored-By: Claude Opus 4.8 <noreply@anthropic.com>
113 lines
3.5 KiB
Transact-SQL
113 lines
3.5 KiB
Transact-SQL
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fds__lookupReminders]
|
|
@InvId varchar(15)
|
|
, @authuser varchar(100)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF [dbo].[fis_getModuleAuth]('fds_reminder', @authuser) < 2
|
|
THROW 60000, N'not authorized', 1;
|
|
ELSE IF NOT EXISTS (select 0 FROM [dbo].[fds__invoices] WHERE [id] = @InvId)
|
|
THROW 60000, N'invoice not found', 1;
|
|
|
|
INSERT INTO [dbo].[fds__admin_activity] ([activity] ,[authuser] ,[info])
|
|
VALUES ('fds__lookupReminders' ,@authuser , (SELECT * FROM (VALUES(@InvId, @authuser)) as z ([InvId],[authuser]) FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER));
|
|
|
|
DECLARE @REM TABLE (
|
|
[Id] [varchar](10) NOT NULL,
|
|
[Version] [int] NOT NULL,
|
|
[DocumentName] [varchar](100) NULL,
|
|
[InvId] [varchar](15) NOT NULL,
|
|
[CustomerId] [bigint] NULL,
|
|
[SendToAddress] [nvarchar](1000) NULL,
|
|
[SendToEmail] [nvarchar](255) NULL,
|
|
[type] [varchar](3) NOT NULL,
|
|
[amount] [numeric](10, 3) NULL,
|
|
[amount_payed] [numeric](10, 3) NULL,
|
|
[amount_open] [numeric](10, 3) NULL,
|
|
[subject] [nvarchar](255) NULL,
|
|
[text] [nvarchar](2000) NULL,
|
|
[IsSent] [bit] NOT NULL,
|
|
[IsFinal] [bit] NULL,
|
|
[CustomValues] [nvarchar](max) NULL,
|
|
[DateSent] [datetime] NULL,
|
|
[UserSent] [varchar](25) NULL,
|
|
[DateFinalized] [datetime] NULL,
|
|
[UserFinalized] [varchar](25) NULL,
|
|
[DateCreated] [datetime] NOT NULL,
|
|
[UserCreated] [varchar](25) NOT NULL,
|
|
[DateModified] [datetime] NOT NULL,
|
|
[UserModified] [varchar](25) NOT NULL,
|
|
[order] int
|
|
);
|
|
|
|
INSERT INTO @REM
|
|
([Id]
|
|
,[Version]
|
|
,[DocumentName]
|
|
,[InvId]
|
|
,[CustomerId]
|
|
,[SendToAddress]
|
|
,[SendToEmail]
|
|
,[type]
|
|
,[amount]
|
|
,[amount_payed]
|
|
,[subject]
|
|
,[text]
|
|
,[IsSent]
|
|
,[IsFinal]
|
|
,[CustomValues]
|
|
,[DateSent]
|
|
,[UserSent]
|
|
,[DateFinalized]
|
|
,[UserFinalized]
|
|
,[DateCreated]
|
|
,[UserCreated]
|
|
,[DateModified]
|
|
,[UserModified]
|
|
,[order])
|
|
SELECT [Id]
|
|
,[Version]
|
|
,[DocumentName]
|
|
,[InvId]
|
|
,[CustomerId]
|
|
,[SendToAddress]
|
|
,[SendToEmail]
|
|
,[type]
|
|
,[amount]
|
|
,[amount_payed]
|
|
,[subject]
|
|
,[text]
|
|
,[IsSent]
|
|
,[IsFinal]
|
|
,[CustomValues]
|
|
,[DateSent]
|
|
,[UserSent]
|
|
,[DateFinalized]
|
|
,[UserFinalized]
|
|
,[DateCreated]
|
|
,[UserCreated]
|
|
,[DateModified]
|
|
,[UserModified]
|
|
, [order] = ROW_NUMBER() OVER (ORDER BY DateFinalized ASC)
|
|
FROM [dbo].[fds__reminder] WHERE [invid] = @InvId AND [IsFinal] = 1;
|
|
|
|
DECLARE @last varchar(1) = ISNULL(( SELECT TOP(1) [type] FROM @rem ORDER BY [DateFinalized] DESC), '');
|
|
DECLARE @newnum int = CASE WHEN @last IN('l','f') THEN (SELECT COUNT(*) FROM @REM) + 1
|
|
WHEN @last = 'm' THEN ISNULL( (SELECT TOP(1) TRY_CAST(LEFT([type],1) as int) FROM @rem ORDER BY [DateFinalized] DESC) , (SELECT COUNT(*) FROM @REM) + 1)
|
|
ELSE 1
|
|
END;
|
|
|
|
---output
|
|
|
|
SELECT
|
|
[type] = CAST( CASE WHEN @last = 'f' THEN 'm' WHEN @last = 'm' THEN 'l' ELSE 'f' END as varchar(1))
|
|
,[level] = @newnum;
|
|
|
|
SELECT [subject], [amount], [amount_payed], [DateFinalized] FROM @REM;
|
|
|
|
END |