Stefan
628802db19
- Created a new function `fds__getCompanyAddressJson` to return a company's postal address as a structured JSON object. - Modified stored procedures `fds__createInvoice`, `fds__setInvoice`, and `fds__prepInvoice` to include a new parameter `@SendToAddressJson` for handling the address data. - Updated the invoice table and user-defined types to accommodate the new `SendToAddressJson` field. - Ensured that the address data is properly retrieved and stored in the invoice records.
42 lines
1.6 KiB
Transact-SQL
42 lines
1.6 KiB
Transact-SQL
-- =============================================
|
|
-- Returns the recipient company's postal address as a structured JSON object
|
|
-- ({name, street, postalCode, city, state, country}) for the invoice editor's
|
|
-- structured address dialog (EN 16931 / eRechnung). Mirrors the location
|
|
-- resolution of [fds__getCompanyNameAddress] but keeps the fields separate
|
|
-- instead of composing them into one free-text string. See ADR 0012.
|
|
-- =============================================
|
|
CREATE FUNCTION [dbo].[fds__getCompanyAddressJson]
|
|
(
|
|
@companyid bigint
|
|
)
|
|
RETURNS nvarchar(max)
|
|
AS
|
|
BEGIN
|
|
DECLARE @locationid bigint, @name nvarchar(255);
|
|
DECLARE @street nvarchar(255), @postal nvarchar(255), @city nvarchar(255), @state nvarchar(255), @country varchar(15);
|
|
|
|
SELECT TOP(1) @locationid = cy.[Location#ID], @name = cy.[name]
|
|
FROM [dbo].[mfr__companies] as cy WHERE cy.[id] = @companyid;
|
|
|
|
IF @locationid IS NULL
|
|
SELECT TOP(1) @locationid = l.[ID]
|
|
FROM [dbo].[mfr__#locations] as l
|
|
JOIN [dbo].[mfr__companies] as cy ON l.[Property] = 'Company:Location' AND l.[EntityId] = cy.[Id]
|
|
WHERE cy.[id] = @companyid;
|
|
|
|
SELECT TOP(1) @street = loc.[AddressString], @postal = loc.[Postal], @city = loc.[City],
|
|
@state = loc.[State], @country = loc.[Country]
|
|
FROM [dbo].[mfr__#locations] as loc WHERE loc.[id] = @locationid;
|
|
|
|
RETURN (
|
|
SELECT
|
|
ISNULL(@name, '') AS [name]
|
|
, ISNULL(@street, '') AS [street]
|
|
, ISNULL(@postal, '') AS [postalCode]
|
|
, ISNULL(@city, '') AS [city]
|
|
, ISNULL(@state, '') AS [state]
|
|
, ISNULL(@country, '') AS [country]
|
|
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
|
|
);
|
|
END
|