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>
81 lines
3.8 KiB
Transact-SQL
81 lines
3.8 KiB
Transact-SQL
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fds__getBankingtransfers_questionable]
|
|
@mode varchar(1) = 'm'
|
|
, @authuser varchar(100)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
IF [dbo].[fis_getModuleAuth]('fds_bam', @authuser) < 1
|
|
THROW 60000, N'not authorized', 1;
|
|
|
|
INSERT INTO [dbo].[fds__admin_activity] ([activity] ,[authuser] ,[info])
|
|
VALUES ('fds__getBankingtransfers_questionable' ,@authuser , '');
|
|
|
|
DECLARE @startdate date, @enddate date, @today date = GETDATE();
|
|
DECLARE @true bit = 1, @false bit = 0;
|
|
SET @mode = LOWER(@mode);
|
|
|
|
|
|
--output admin
|
|
SELECT [startdate] = @startdate, [enddate] = @enddate, [mode] = @mode, [title] = 'Auffällige Zahlungen (neueste zuerst)'
|
|
,[note] = 'Letzes Buchungsdatum: ' + FORMAT((SELECT MAX([valuedate]) from [fds__bankingtransactions]), 'dd.MM.yy');
|
|
|
|
|
|
with inv1 as (
|
|
SELECT
|
|
[InvId] = CAST([Id] as varchar(25)), [InvoiceId], [InvoiceBalance] = CAST([InvoiceBalance] as numeric(10,2)), [ispayed], i.[IsCanceled], i.IsExternal, i.ExternalId
|
|
FROM [dbo].[fds__invoices] as i
|
|
), inv as (
|
|
SELECT
|
|
[fds] = IIF(inv1.IsExternal = 1,0,1), [InvId], [InvoiceId], [InvoiceBalance], [ispayed], [IsCanceled]--, i.IsExternal, i.ExternalId
|
|
,bta.[taID]
|
|
FROM inv1
|
|
JOIN dbo.[fds__bankingtransactions_assigns] as bta on [InvId] = bta.[invoice_id] OR (inv1.IsExternal = 1 AND inv1.ExternalId = bta.[invoice_id])
|
|
)
|
|
select
|
|
bt.[taID]
|
|
--inv.InvoiceId,
|
|
--[IsPayed],
|
|
, inv.IsCanceled
|
|
, inv.fds
|
|
, inv.InvoiceId
|
|
, [auto] = [dbo].[ott_csv_contains](bts.auto_invoice_id,inv.InvId)
|
|
, [ValueDate] = FORMAT(bt.[ValueDate], 'dd.MM.yy')
|
|
, [InvoiceBalance] = FORMAT(inv.[InvoiceBalance], '0.00€', 'de') + ISNULL( CASE WHEN id2.[invid] is not null then (SELECT TOP(1) FORMAT(inv2.[InvoiceBalance], '0.00€', 'de') FROM [dbo].[fds__invoices] as inv2 where inv2.Id = id2.[invid]) ELSE '' END ,'')
|
|
, [Amount] = FORMAT(bt.[amount], '0.00€', 'de')
|
|
, [Skonto] = CAST(CASE WHEN bt.[amount] between (0.965 * inv.[InvoiceBalance]) and (0.975 * inv.[InvoiceBalance]) THEN 1 ELSE 0 END as bit)
|
|
, [Deviation] = FORMAT(CASE WHEN ISNULL(inv.[InvoiceBalance],0) > 9 THEN bt.[amount] / inv.[InvoiceBalance] ELSE NULL END , '0.00%')
|
|
--, ic.*
|
|
, bt.AccountNumberOfPayer
|
|
, bt.NameOfPayer
|
|
, bt.SepaRemittanceInformation
|
|
, bt.EndToEndReference
|
|
, [order] = ROW_NUMBER() OVER (ORDER BY bt.[valuedate] DESC)
|
|
FROM
|
|
dbo.[fds__bankingtransactions] as bt
|
|
LEft JOIN dbo.[fds__bankingtransactions_settings] as bts on bt.[taID] = bts.[taID]
|
|
--LEFT JOIN [dbo].[fds__getInvoiceCredits] (null, null) as ic ON ic.[taID] = bt.[taID]
|
|
--LEFT JOIN inv on inv.[InvoiceId] = ic.[invoiceid]
|
|
LEFT JOIN inv on inv.[taID] = bt.[taID]
|
|
LEFT JOIN [dbo].[fds__invoice_details] as id2 ON inv.InvId = id2.[stornoto_invid]
|
|
WHERE
|
|
(bt.[DebitCreditMark] in ('C')
|
|
AND NOT (ISNULL(bt.[AccountNumberOfPayer],'') IN ('DE52301502000002091478') AND ISNULL(bt.[SepaRemittanceInformation],'') like ('%umbuchung%'))
|
|
AND NOT (ISNULL(bt.[AccountNumberOfPayer],'') IN ('DE23501108006161606386') AND ISNULL(bt.[SepaRemittanceInformation],'') like ('%ebay%'))
|
|
AND NOT (ISNULL(bt.[SepaRemittanceInformation],'') like ('%Umsatzsteuer%'))
|
|
AND ISNULL(bt.AccountNumberOfPayer ,'') <> ''
|
|
) AND
|
|
CASE WHEN inv.[InvId] is null THEN 1
|
|
WHEN (ISNULL(inv.ispayed,0) = 1 and fds = 1) THEN 0
|
|
WHEN inv.[IsCanceled] = 1 THEN 1
|
|
WHEN bt.[amount] not between (inv.InvoiceBalance - 0.99) and (inv.InvoiceBalance + 0.99) THEN 1
|
|
ELSE 0
|
|
END = 1
|
|
|
|
--AND NOT EXISTS (SELECT 0 FROM [dbo].[fds__bankingtransactions_settings] as bs WHERE bs.[banking_uid] = bt.[uid] AND (ISNULL(bs.[done_manually],'') <> '' OR ISNULL(bs.[assigned_invoice_id],'') <> ''))
|
|
AND ISNULL(bts.[done_manually],'') = ''
|
|
END |