IF TYPE_ID('HM.IntListTypeUnique') IS NOT NULL
	DROP TYPE HM.IntListTypeUnique
GO

CREATE TYPE HM.IntListTypeUnique AS TABLE
(
	[Id] INT UNIQUE
)

GO
-- Usuwanie podmiotów trzecich dla listy unikalnych Id
CREATE OR ALTER PROCEDURE [HM].[DeleteKsefRelatedPartiesByIdList]
(
    @IdList HM.IntListTypeUnique readonly -- unikalna lista Id
)
AS
BEGIN
    BEGIN TRY
    
        IF NOT EXISTS (SELECT TOP 1 1 FROM @IdList)
            RETURN;
        
        BEGIN TRANSACTION;

        DELETE op
        FROM HM.Ksef_OtherParties op
        INNER JOIN @IdList l ON l.Id = op.Id

        COMMIT TRANSACTION;

    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK TRANSACTION;
        THROW;
    END CATCH

END

GO
CREATE OR ALTER VIEW [HM].[Ksef_ContractorJstSeller_View]
AS
    SELECT
	id,
	aktywny as IsInvoiceInNameOfSeller,
	CAST(subtyp AS INT) AS RoleId,
	CAST(CASE WHEN subtyp IN (5, 7, 9) THEN 1 ELSE 0 END AS BIT) AS IsCorrectRoleId,
	nazwa as SellerName,
	nip AS SellerNip,
	typks as SellerNipUe,
	regon as SellerRegon,
	pesel as SellerPesel,
	statusUE as IsSellerVies,
	'PL' as SellerCountryCode,
	ulica as SellerAddressStreet,
	dom as SellerAddressHouseNo,
	lokal as SellerAddressApartmentNo,
	kodpocz as SellerAddressPostCode,
	miejscowosc as SellerAddressCity
FROM HM.KH
WHERE typ = 109 AND kod = 'HMF_SPRZEDAWCA' -- pobieranie po indeksie unikalnym
    -- pobieramy rekord tylko jeśli spełnione są wszystkie warunki, inaczej rekord nie jest pobrany, bo ten wpis w bazie zawsze istnieje
    AND CASE WHEN aktywny = 1 AND CASE WHEN subtyp IN (5, 7, 9) THEN 1 ELSE 0 END = 1 THEN 1 ELSE 0 END = 1
    AND NULLIF(TRIM(nip), '') IS NOT NULL -- NIP nie może być pusty

GO
CREATE OR ALTER PROCEDURE HM.UpdateKsefRelatedPartiesJstSeller
    @documentId INT,
    @subtype INT = NULL
AS
BEGIN
    BEGIN TRY
        IF @documentId = 0
            RETURN;

        --sprawdzamy czy dokument jest dokumentem KSeF
        IF (ISNULL(@subtype, 0) NOT IN (40, 41, 42, 43, 45, 46, 68, 69, 79, 80, 99, 100, 101, 102, 114, 120, 124, 125, 126, 127, 136, 137, 138, 139, 152, 153, 157, 158, 159, 160))
            RETURN;

        BEGIN TRANSACTION

        -- sprzedawca w imieniu którego wystawiany jest dokument w podmiotach powiązanych
        DECLARE @JstSellers TABLE (Id INT PRIMARY KEY, AddedByUser BIT)

        INSERT INTO @JstSellers (Id, AddedByUser)
        SELECT Id, AddedByUser
        FROM HM.Ksef_OtherParties
        WHERE DocumentId = @documentId AND PartyType = 0 -- typ nieznany

        DECLARE @isJSTSeller BIT = 0
        DECLARE @Role INT
        IF EXISTS (SELECT TOP 1 1 FROM [HM].[Ksef_ContractorJstSeller_View])
        BEGIN
            SET @isJSTSeller = 1
            SELECT @Role = RoleId FROM [HM].[Ksef_ContractorJstSeller_View]
        END
        ELSE
        BEGIN

            DECLARE @BranchToDeleteIdList HM.IntListTypeUnique

            INSERT INTO @BranchToDeleteIdList (Id)
            SELECT DISTINCT p.Id 
            FROM HM.Ksef_OtherParties AS kop
            INNER JOIN @JstSellers AS p ON p.Id = kop.Id
            WHERE p.AddedByUser = 0

            -- usuwanie wpisów z podmiotów powiązanych
            EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList
        END

        IF @isJSTSeller = 0
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        --Pobieranie danych firmy i Wstawianie do podmiotów powiązanych JST sprzedawca, o ile już ich tam nie ma
        IF @isJSTSeller = 1 AND @Role IS NOT NULL AND @Role IN (5, 7, 9) AND NOT EXISTS (
            SELECT TOP 1 1 
            FROM HM.Ksef_OtherParties 
            WHERE DocumentId = @documentId AND PartyType = 0
        )
        BEGIN
        
            DECLARE @adresName NVARCHAR(512),
                    @name NVARCHAR(512),
                    @nip NVARCHAR(20),
                    @vies BIT, 
                    @address NVARCHAR(512);

            DECLARE @StartIssueDate DATETIME
            -- Znany jest scenariusz, że ktoś zmieni date wystawienia, a dane firmy są zapisane między datami obowiązywania
            SELECT @StartIssueDate = d.[data] FROM HM.DK d WHERE d.Id = @documentId
            IF @StartIssueDate IS NULL
            BEGIN
               COMMIT TRANSACTION
               RETURN;
            END

            SELECT TOP 1 
                @name = TRIM(CompanyData1.[Name]),
                @nip = TRIM(CompanyData1.[NIP]),
                @vies = CompanyData1.[Vies],
                @address = HM.GetFullAddress(
                    TRIM(PostOfficeAddresses5.[Place]) + IIF(TRIM(PostOfficeAddresses5.[Place]) <> '', ', ' ,'') + PostOfficeAddresses5.[Street], 
                    PostOfficeAddresses5.[HouseNo], 
                    PostOfficeAddresses5.[ApartmentNo], 
                    PostOfficeAddresses5.[PostCode],
                    PostOfficeAddresses5.[PostOffice])
            FROM SSCommon.[STCompany] as Company0 
            INNER JOIN SSCommon.[STCompanyData] as CompanyData1 
	            ON (CompanyData1.[CompanyGuid]=Company0.[Guid])  
            LEFT OUTER JOIN SSCommon.[STContacts] as Contacts3 
	            ON (Contacts3.[Guid]=CompanyData1.[ContactGuid])  
            LEFT OUTER JOIN SSCommon.[STPostOfficeAddresses] as PostOfficeAddresses5 
	            ON (PostOfficeAddresses5.[Guid]=Contacts3.[MainPostOfficeAddress])  
            WHERE  CompanyData1.[BeginDate] <= @StartIssueDate 
            AND CompanyData1.[EndDate] >= @StartIssueDate
            ORDER BY CompanyData1.[BeginDate] ASC

            DECLARE @country CHAR(2) = 'PL'
            DECLARE @normalizedNip NVARCHAR(20) = HM.fnNormalizeNip(@nip, @country, @vies, 0, 1);
            DECLARE @identyfierType INT = 1     -- NIP

            -- Firma musi posiadać polski NIP
            IF @normalizedNip IS NULL OR @normalizedNip = ''
            BEGIN
                COMMIT TRANSACTION
                RETURN
            END

            -- brak wpisu w podmiotach powiazanych - dodajemy wskazanego
            INSERT INTO HM.Ksef_OtherParties (
                DocumentId,
                PartyType,
                Name,
                Role,
                IdentifierType,
                Nip,
                CountryCode,
                Address,
                AddedByUser
            )
            VALUES (
                @documentId,
                0,
                @name,
                @Role,
                @identyfierType,
                @normalizedNip,
                @country,
                @address,
                0
            );
        END

    	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END

GO
CREATE OR ALTER PROCEDURE HM.UpdateKsefRelatedPartiesBranch
    @documentId INT,
    @receiverAddressId INT,
    @contractorAddressId INT,
    @subtype INT = NULL
AS
BEGIN
    BEGIN TRY

        IF @documentId = 0
            RETURN;

        --sprawdzamy czy dokument jest dokumentem KSeF
        IF (ISNULL(@subtype, 0) NOT IN (40, 41, 42, 43, 45, 46, 68, 69, 79, 80, 99, 100, 101, 102, 114, 120, 124, 125, 126, 127, 136, 137, 138, 139, 152, 153, 157, 158, 159, 160))
            RETURN;

    BEGIN TRANSACTION

        -- Aktualizacja (usunięcie/dodanie) podmiotów powiązanych dla sprzedawcy wystawiającego fakturę w imieniu (Jednostki Samorządu Terytorialnego)
        EXEC HM.UpdateKsefRelatedPartiesJstSeller
            @documentId = @documentId,
            @subtype = @subtype

        -- odział w podmiotach powiazanych
        -- (id wpisu, id oddziału, dodany przez uzytkownika w oknie podmioty powiazane)
        DECLARE @branches TABLE (Id INT PRIMARY KEY, BranchId INT, AddedByUser BIT)

        INSERT INTO @branches (Id, BranchId, AddedByUser)
        SELECT Id, ISNULL(BranchId, -1), AddedByUser
        FROM HM.Ksef_OtherParties
         --    biezacy dokument             oddział        odbiorca      
        WHERE DocumentId = @documentId AND PartyType = 1 AND Role = 2

        DECLARE @receiverId INT
        DECLARE @branchId INT
        DECLARE @adrName VARCHAR(150)
        DECLARE @email NVARCHAR(100) 
        DECLARE @phone NVARCHAR(50)

        --pobranie id odbiorcy oraz numeru oddziału KSeF
        SELECT TOP 1 @receiverId = idkh, @adrName = nazwaAdr, @email = ISNULL(ad.EmailThirdParty, ''),
                     @phone = ISNULL(IIF(ISNULL(LEN(TRIM(ad.TelThirdParty)), 0) > 16, NULL, TRIM(ad.TelThirdParty)), '')
        FROM HM.AD WHERE Id = @receiverAddressId

        DECLARE @BranchToDeleteIdList HM.IntListTypeUnique

        IF (ISNULL(@receiverAddressId, 0) = ISNULL(@contractorAddressId, 0))
        BEGIN
            
            INSERT INTO @BranchToDeleteIdList (Id)
            SELECT DISTINCT Id 
            FROM @branches p
            WHERE p.BranchId IS NOT NULL AND p.AddedByUser = 0

            -- usuwanie wpisów z podmiotów powiązanych
            EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList
            
            COMMIT TRANSACTION
            RETURN;
        END

        -- pobranie id odziału KSeF
        SELECT TOP 1 @branchId = s.Id
        FROM SSCommon.STContractors AS Contractors0
        INNER JOIN SSCommon.STContacts contact ON contact.Guid = Contractors0.ContactGuid
        INNER JOIN SSCommon.STPostOfficeAddresses s ON s.ContactGuid = contact.Guid
            AND s.AddressName = @adrName
        LEFT JOIN HM.KSef_Departments AS KSeFDep ON KSeFDep.STPostOfficeAddressesId = s.Id
        WHERE Contractors0.Id = @receiverId

        --pobranie kraju odbiorcy
        DECLARE @receiverCountry CHAR(2)
        SELECT TOP 1 @receiverCountry = s.Country
        FROM SSCommon.STContractors AS Contractors0
        INNER JOIN SSCommon.STContacts contact ON contact.Guid = Contractors0.ContactGuid
        INNER JOIN SSCommon.STPostOfficeAddresses s ON s.ContactGuid = contact.Guid
	        AND s.Guid = contact.MainPostOfficeAddress
        WHERE Contractors0.Id = @receiverId

        SET @branchId = ISNULL(@branchId, 0)

        -- istnieje juz wlasciwy wpis odziału w podmiotach powiazanych
        IF EXISTS (SELECT TOP 1 1 FROM @branches WHERE BranchId = @branchId AND AddedByUser = 0)
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        -- dokument nie ma odziału w podmiotach powiazanych i nie wskazano odziału
        IF EXISTS (SELECT TOP 1 1 FROM @branches WHERE AddedByUser = 0) AND @branchId = 0
        BEGIN
            COMMIT TRANSACTION
            RETURN;
        END

        --czyszczenie wpisów w TVP dla kolejnego delete
        DELETE FROM @BranchToDeleteIdList

        INSERT INTO @BranchToDeleteIdList (Id)
        SELECT DISTINCT p.Id
        FROM HM.Ksef_OtherParties AS kop
        INNER JOIN @branches AS p ON p.Id = kop.Id
        WHERE p.BranchId <> @branchId AND p.AddedByUser = 0

        -- usuwanie wpisów z podmiotów powiązanych
        EXEC [HM].[DeleteKsefRelatedPartiesByIdList] @BranchToDeleteIdList

        DELETE
        FROM @branches
        WHERE BranchId <> @branchId AND AddedByUser = 0

        IF @branchId <> 0 AND NOT EXISTS (SELECT 1 FROM @branches WHERE BranchId = @branchId) 
        BEGIN
            -- odczytujemy dane odziału
            DECLARE @branchNr VARCHAR(5),
                    @name NVARCHAR(512),
                    @nip NVARCHAR(20),
                    @nip_ue NVARCHAR(20), 
                    @country CHAR(2), 
                    @vies BIT, 
                    @address NVARCHAR(512),
                    @gln NVARCHAR(13),
                    @eori NVARCHAR(256);
            
            IF EXISTS (SELECT TOP 1 1 FROM HM.KSef_Departments AS d WHERE d.STPostOfficeAddressesId = @branchId)
            BEGIN
                --Pobieranie danych oddziału
                SELECT @branchNr = LTRIM(RTRIM(REPLACE(REPLACE(d.KsefDepartmentValue, '-', ''), ' ', ''))),
                       @name = TRIM(a.AddressName),
                       @nip = TRIM(c.NIP), 
                       @nip_ue = TRIM(c.NIP_UE), 
                       @country = SUBSTRING(TRIM(a.Country), 1, 2), 
                       @vies = c.VIES,
                       @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place),
                       @gln = TRIM(d.GLN),
                       @eori = TRIM(d.EORI),
                       @email = d.EmailThirdParty,
                       @phone = IIF(ISNULL(LEN(TRIM(d.TelThirdParty)), 0) > 16, NULL, TRIM(d.TelThirdParty))
                FROM HM.KSef_Departments AS d
                INNER JOIN SSCommon.STPostOfficeAddresses a ON a.Id = d.STPostOfficeAddressesId
                INNER JOIN SSCommon.STContacts ct ON ct.Guid = a.ContactGuid
                INNER JOIN SSCommon.STContractors AS c ON c.ContactGuid = ct.Guid
                WHERE d.STPostOfficeAddressesId = @branchId 
            END
            ELSE
            BEGIN
                -- EORI
                EXEC HM.GetContractorEori
                     @contractorId = @receiverId,
                     @value        = @eori OUTPUT;
                 
                -- GLN
                EXEC HM.GetContractorGln
                     @contractorId = @receiverId,
                     @value        = @gln OUTPUT;

                --Pobieranie danych dla adresu domyślnego
                SELECT @branchNr = '', --brak numeru oddziału KSeF dla adresu domyślnego
                       @name = TRIM(c.Name),
                       @nip = TRIM(c.NIP), 
                       @nip_ue = TRIM(c.NIP_UE), 
                       @country = SUBSTRING(TRIM(a.Country), 1, 2), 
                       @vies = c.VIES,
                       @address = HM.GetFullAddress(a.Street, a.HouseNo, a.ApartmentNo, a.PostCode, a.Place)
                FROM SSCommon.STPostOfficeAddresses a 
                INNER JOIN SSCommon.STContacts ct ON ct.Guid = a.ContactGuid
                INNER JOIN SSCommon.STContractors AS c ON c.ContactGuid = ct.Guid
                WHERE a.Id = @branchId
            END
            
            -- okreslamy typ identyfikatora ( NIP / VAT-UE / Brak )
            DECLARE @identyfierType INT = 0     -- Brak
            DECLARE @normalizedNipUE NVARCHAR(20) = HM.fnNormalizeNip(@nip_ue, @receiverCountry, @vies, 1, 1);
            DECLARE @normalizedNip NVARCHAR(20) = HM.fnNormalizeNip(@nip, @receiverCountry, @vies, 0, 1);
            DECLARE @otherTaxId NVARCHAR(100)

            DECLARE @InternalId VARCHAR(40)
            IF ISNULL(LEN(TRIM(@normalizedNip)), 0) > 0 AND ISNULL(LEN(TRIM(@branchNr)), 0) > 0
                SET @InternalId = TRIM(@normalizedNip) + '-' + TRIM(@branchNr)

			DECLARE @isCountryFromEu INT
			SELECT @isCountryFromEu = IsFromUe FROM HM.fnIsCountryFromUe(@receiverCountry)

            IF ISNULL(LEN(TRIM(@InternalId)), 0) > 0
                SET @identyfierType = 4 -- Wewnętrzny
            ELSE IF ISNULL(LEN(TRIM(@normalizedNip)), 0) > 0 AND @receiverCountry = 'PL'
                SET @identyfierType = 1 -- NIP
            ELSE IF ISNULL(LEN(TRIM(@normalizedNipUE)), 0) > 0 AND @receiverCountry <> 'PL' AND @vies = 1 AND @isCountryFromEu = 1
                SET @identyfierType = 2 -- VatUE
            ELSE IF ISNULL(LEN(TRIM(@nip)), 0) > 0 
            BEGIN
                SET @identyfierType = 3 -- Identyfikator podatkowy inny.
                IF (LEFT(@nip, 2) = @receiverCountry)
                    SET @otherTaxId = SUBSTRING(@nip, 3, LEN(@nip) - 2);
                ELSE
                    SET @otherTaxId = @nip
            END
            ELSE
                SET @identyfierType = 0 -- Nieokreślony (Brak)

            -- brak odziału w podmiotach powiazanych - dodajemy wskazanego
            INSERT INTO HM.Ksef_OtherParties (
                DocumentId,
                PartyType,
                BranchId,
                Name,
                Role,
                InternalId,
                IdentifierType,
                Nip,
                NipUE,
                OtherTaxId,
                Eori,
                CountryCode,
                Address,
                Gln,
                Email,
                Phone,
                AddedByUser
            )
            VALUES (
                @documentId,
                1,
                @branchId,
                @name,
                2,
                @InternalId,
                @identyfierType,
                @normalizedNip,
                @normalizedNipUE,
                @otherTaxId,
                @eori,
                @country,
                @address,
                @gln,
                @email,
                @phone,
                0
            );
        END

    	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		IF @@TRANCOUNT > 0
			ROLLBACK TRANSACTION;
		THROW;
	END CATCH
END