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>
108 lines
3.1 KiB
Transact-SQL
108 lines
3.1 KiB
Transact-SQL
-- =============================================
|
|
-- Author: <Author,,Name>
|
|
-- Create date: <Create Date,,>
|
|
-- Description: <Description,,>
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[fds__getInvoice]
|
|
@Id varchar(10),
|
|
@authuser varchar(25)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
IF [dbo].[fis_getModuleAuth]('fds_inv', @authuser) < 1
|
|
THROW 60000, N'not authorized', 1;
|
|
|
|
|
|
DECLARE @inv [dbo].[fds__tt__invoice_base];
|
|
DECLARE @req [dbo].[fds__tt__invoice_servicerequests];
|
|
DECLARE @itm [dbo].[fds__tt__invoice_items];
|
|
|
|
INSERT INTO @inv
|
|
SELECT TOP(1) [Id]
|
|
,[Version]
|
|
,[InvoiceId]
|
|
,[InvoiceType]
|
|
,[InvoiceTitle]
|
|
,[DocumentName]
|
|
,[InvoiceBalance]
|
|
,[InvoiceBalance_net]
|
|
,[InvoiceVAT_net1]
|
|
,[InvoiceVAT_1]
|
|
,[InvoiceVAT_net2]
|
|
,[InvoiceVAT_2]
|
|
,[PaymentTerm]
|
|
,[DueDate]
|
|
,[CustomerId]
|
|
,[SendToAddress]
|
|
,[SendToEmail]
|
|
,[ProvisionPeriod]
|
|
,[ProvisionLocation]
|
|
,[PaymentStatus]
|
|
,[IsPayed]
|
|
,[IsSent]
|
|
,[IsFinal]
|
|
,[IsCanceled]
|
|
,[Replaces_InvId]
|
|
,[CustomValues]
|
|
,[DateSent]
|
|
,[UserSent]
|
|
,[DateFinalized]
|
|
,[UserFinalized]
|
|
,[DateCancelled]
|
|
,[UserCancelled]
|
|
,[DateCreated]
|
|
,[UserCreated]
|
|
,[DateModified]
|
|
,[UserModified]
|
|
,[ExternalId]
|
|
,[isExternal]
|
|
,[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])
|
|
,[PaymentTermPhrase] = null
|
|
,[InvoiceService] = [InvoiceService_net] + [InvoiceService_VAT]
|
|
,[InvoiceService_net]
|
|
,[InvoiceService_VAT]
|
|
,[InvoiceOptions] = ISNULL(_d.[InvoiceOptions], '')
|
|
FROM [dbo].[fds__invoices] as _i LEFT JOIN [dbo].[fds__invoice_details] as _d ON _i.[Id] = _d.[InvId]
|
|
WHERE [Id] = @Id AND @Id is not null;
|
|
|
|
|
|
INSERT INTO @req
|
|
SELECT * FROM [dbo].[fds__invoice_servicerequests] WHERE [invId] = @Id AND @Id is not null;
|
|
INSERT INTO @itm
|
|
SELECT _i.* FROM [dbo].[fds__invoice_items] as _i WHERE _i.[invId] = @Id AND @Id is not null AND EXISTS( SELECT * from @req as _r WHERE _r.[id] = _i.[InvRqId]);
|
|
|
|
DELETE _R FROM @req as _R WHERE NOT EXISTS (SELECT * FROM @itm as _i WHERE _i.[InvRqId] = _R.[Id] AND (ISNULL(_i.[text], '') <> '' OR ISNULL(_i.[value_total],0) <> 0));
|
|
DELETE _i FROM @itm as _i WHERE NOT EXISTS( SELECT * from @req as _r WHERE _r.[id] = _i.[InvRqId]);
|
|
|
|
|
|
|
|
|
|
|
|
---output
|
|
|
|
--admin
|
|
SELECT TOP(1)
|
|
[today]
|
|
, [sender] = N'Sebastian Fuchs GmbH & Co. KG ● Germaniastraße 15 ● 40223 Düsseldorf'
|
|
, [tax_servicerefund] = 0.2
|
|
, [paymentterms] = iv.[PaymentTerm]
|
|
, [CustomerId] = iv.[CustomerId]
|
|
, [type] = iv.[InvoiceType]
|
|
FROM (VALUES(CAST(GETDATE() as date)))base ([today]) cross JOIN @inv as iv;
|
|
|
|
--inv
|
|
SELECT TOP(1)
|
|
*
|
|
,[tax_servicerefund] = 0.2
|
|
FROM @inv;
|
|
SELECT *
|
|
, [order] = ROW_NUMBER() OVER (ORDER BY [SortOrder])
|
|
FROM @req;
|
|
SELECT *
|
|
, [order] = ROW_NUMBER() OVER (PARTITION BY [InvRqId] ORDER BY [SortOrder])
|
|
FROM @itm;
|
|
|
|
END |