 DELIMITER $$

DROP PROCEDURE IF EXISTS `kontrah_nip24_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `kontrah_nip24_tools`(
	IN _MODE VARCHAR(150),
	IN _K1 VARCHAR(150),	
	IN _K2 VARCHAR(150),	
	IN _K3 VARCHAR(150),	
	IN _Param_A VARCHAR(150),	
	IN _Param_B MEDIUMTEXT
)
/*
	CALL _geo_conv.ta_geo_conv_kontrah_('create.table', '', '', '', '');
	-- SELECT * FROM `ta_kontrah_adresy`;
*/
S_ta_geo_conv_kontrah_: BEGIN
	DECLARE _SQLText MEDIUMTEXT;
	DECLARE _KontrGUID VARCHAR(38);
	DECLARE _NIP VARCHAR(50);
	IF ISNULL(@session.globals.Owner) THEN SELECT g.value  INTO @session.globals.Owner from _db_globals g WHERE g.key='Owner'; END IF;
	 
	/************************************************************************************************************************************************************* 
		STRONA serwisu - bazy na serwerze INT lub u klienta mającego repozytorium danych z GUS / NIP24 (tabela `kontrah_nip24`)
	*************************************************************************************************************************************************************/
	IF _MODE IN ('SERVICE.NIP.Exists') THEN
	/* *************************************************************************************************************************************************************
		Zwraca informacje czy dany rekord NIP istnieje (pole NIP_Exists)
		CALL kontrah_nip24_tools('SERVICE.NIP.Exists', '', 'nip', 'ile dni wstecz', '', '');	
		IN _K1 IDCountry
		IN _K2 NIP,	
		IN _K3 Ile dni

	**************************************************************************************************************************************************************/
		SELECT 
			EXISTS(
				SELECT 
					kn.nip 
				FROM `kontrah_nip24` kn 
				WHERE 
					kn.NIP = _K2 	--	NIP
					AND kn.IDCountry = IF(IFNULL(_K1, '') = '', 'PL', _K1) 
					AND kn.LastSyncAt > DATE_ADD(NOW(), INTERVAL -_K3 DAY) 
			) AS NIP_Exists
		;
	END IF;
	
	IF _MODE IN ('SERVICE.NIP.INSERT') THEN
	/* *************************************************************************************************************************************************************
		Wstawianie sznurka do tabeli nip24 `kontrah_nip24`
		CALL kontrah_nip24_tools('SERVICE.NIP.INSERT', '', 'nip', 'kontr', 'SET.ADR.kontrah', 'fields');	
		IN _K1 IDCountry
		IN _K2 NIP,	
		IN _K3 KontrGUID,	
		IN _Param_A Update_Status np. = 'SET.ADR'
		_Param_B sznurek danych (jak dotyczczas)

	**************************************************************************************************************************************************************/
		DROP TEMPORARY TABLE IF EXISTS `t_we_kontrah_nip24`;
		CREATE TEMPORARY TABLE `t_we_kontrah_nip24` LIKE `kontrah_nip24`;
	--	aby sie pozbyc przecinka ewntualnego to markujemy end stringa
		SET @Fields = CONCAT(TRIM(_Param_B), '<END>');
		SET @Fields = REPLACE(REPLACE(@Fields, ',<END>', ''), '<END>', '');
		SET @Fields = REPLACE(@Fields, '",', '",\n');
		SET @Fields = REPLACE(@Fields, ''',', ''',\n');
		SET _SQLText =
			"REPLACE `kontrah_nip24`\nSET \nKontrGUID_local=<KontrGUID_local>,\n<BODY>\n;"
		;
		SET @_SQLTextkontrah_nip24_tools = 'xcy';
		SET _SQLText = REPLACE(_SQLText, '<KontrGUID_local>', IF(IFNULL(_K3, '') = '', 'NULL', QUOTE(_K3)));
		SET _SQLText = REPLACE(_SQLText, '<BODY>', @Fields);
		SET @_SQLTextkontrah_nip24_tools = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
        /*
    --  Może brakować tego miejsca i adrsu - UZUPEŁNIJ
        INSERT IGNORE INTO `geo_int_miejsca`
        (ID, ParentId, ParentIdMiasto, IDCountry, ID_WOJ, ID_POW, ID_GMI, ID_Adm, GUS_WOJ, GUS_POW, GUS_GMI, Typ_GMI, GUS_RM, GUS_MZ, Nazwa, Stan_Na)
        SELECT 
            kn.ID,
            kn.ID AS ParentId,
            kn.ID AS ParentIdMiasto,
            kn.IDCountry,
            (@.ID_WOJ := CONCAT('PL', 'W', kn.GUS_WOJ)) AS ID_WOJ,
                    -- gm.ID_WOJ,
            (@.ID_POW := CONCAT(@.ID_WOJ, 'P', kn.GUS_POW)) AS ID_POW,
                    -- gm.ID_POW,
            (@.ID_GMI := CONCAT(@.ID_POW, 'G', kn.GUS_GMI, '-', kn.Typ_GMI)) AS ID_GMI,
                    -- gm.ID_GMI,
            @.ID_GMI AS ID_Adm,
            
            kn.GUS_WOJ,		-- gm.GUS_WOJ,
            kn.GUS_POW,		-- gm.GUS_POW,
            kn.GUS_GMI,		-- gm.GUS_GMI,
            kn.Typ_GMI,		-- gm.Typ_GMI,
            '' AS GUS_RM,	--	skąd wziąc ???
            1 AS GUS_MZ,
            kn.Nazwa,		-- gm.Nazwa,
            kn.Stan_Na
            -- kn.*
        FROM (	
            SELECT 
                -- IFNULL(NULLIF(kn.adr_geo_int_adresy_ID, ''), kn.reg_geo_int_adresy_ID) AS ID,
                CONCAT('PL', cityCode) AS ID,
                kn.IDCountry,
                kn.StateCode AS GUS_WOJ,
                kn.CountyCode AS GUS_POW,
                IF((@GUS_GMI_L := LENGTH(@GUS_GMI := kn.CommunityCode) > 2),
                    SUBSTR(@GUS_GMI, 1, 2),
                    @GUS_GMI
                ) AS GUS_GMI,			
                IF(@GUS_GMI_L,
                    SUBSTR(@GUS_GMI, 3, 1),
                    ''
                ) AS Typ_GMI,			
                
                kn.City AS Nazwa,
                kn.PostCode AS ZipCode,
                kn.adr_geo_int_miejsca_ID,
                -- IF(g.Rodz_GMI != '', g.Rodz_GMI, 0) AS Typ_GMI, 
                -- g.RM AS GUS_RM, g.MZ AS GUS_MZ, 
                kn.CreateAt AS Stan_Na

                -- kn.*
            FROM `kontrah_nip24` kn 
            WHERE
                TRUE
                AND kn.NIP = _K2
        ) kn
        WHERE
            TRUE	
        ON DUPLICATE KEY UPDATE
            Stan_Na = kn.Stan_Na
        ;
        INSERT IGNORE INTO `geo_int_adresy`
        (
            ID, IDCountry, ID_Adm, ID_Miejsca, Nazwa_1, Nazwa_2, 
            GUS_WOJ, GUS_POW, GUS_GMI, Typ_GMI, GUS_SYM, GUS_SYM_UL, GUS_CECHA, 
            Srch_Cecha_N1_N2, Srch_Cecha_N2_N1, Srch_N1_N2, Srch_N2_N1,
            ZipCode, Stan_Na
        )
        SELECT
            ID, IDCountry, ID_Adm, ID_Miejsca, Nazwa_1, Nazwa_2, 
            GUS_WOJ, GUS_POW, GUS_GMI, Typ_GMI, GUS_SYM, GUS_SYM_UL, GUS_CECHA, 
            Srch_Cecha_N1_N2, Srch_Cecha_N2_N1, Srch_N1_N2, Srch_N2_N1,
            ZipCode, Stan_Na
        FROM (	
            SELECT 
                IFNULL(NULLIF(kn.adr_geo_int_adresy_ID, ''), kn.reg_geo_int_adresy_ID) AS ID,
                -- concat_WS('-', kn.CityCode, kn.StreetCode) AS ID2,
                gm.IDCountry, gm.ID_Adm, gm.ID AS ID_Miejsca,
                (@.Nazwa_1 := REPLACE(REPLACE(REPLACE(REPLACE(kn.street, 
                    'ul.', ''), 'pl.', ''), 'al.', ''), 'os.', '')  
                )AS Nazwa_1,
                (@.Nazwa_2 := '') AS Nazwa_2,
                gm.GUS_WOJ, gm.GUS_POW, gm.GUS_GMI, gm.Typ_GMI, 
                kn.CityCode AS GUS_SYM, kn.StreetCode AS GUS_SYM_UL,
                (@.GUS_CECHA :=
                IF(kn.street LIKE '%ul.%', 'ul.',
                IF(kn.street LIKE '%pl.%', 'pl.',
                IF(kn.street LIKE '%al.%', 'al.',
                IF(kn.street LIKE '%os.%', 'os.',
                ''
                ))))) AS GUS_CECHA,
                CONCAT(@.GUS_CECHA, CONCAT_WS(' ', IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1)), IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2)))) AS Srch_Cecha_N1_N2,
                CONCAT(@.GUS_CECHA, CONCAT_WS(' ', IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2)), IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1)))) AS Srch_Cecha_N2_N1,
                CONCAT_WS(' ', IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1)), IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2))) AS Srch_N1_N2,
                CONCAT_WS(' ', IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2)), IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1))) AS Srch_N2_N1,
                
                kn.PostCode AS ZipCode,
                kn.adr_geo_int_miejsca_ID,
                -- kn.lastUpdateDate AS Stan_Na
                kn.CreateAt AS Stan_Na
                -- IF(g.Rodz_GMI != '', g.Rodz_GMI, 0) AS Typ_GMI, 
                -- g.RM AS GUS_RM, g.MZ AS GUS_MZ, 
                
                -- kn.*
            FROM `kontrah_nip24` kn 
            LEFT JOIN `geo_int_miejsca` gm ON gm.ID = CONCAT('PL', kn.cityCode)
            WHERE
                TRUE
                AND kn.NIP = _K2
        ) kn
        ON DUPLICATE KEY UPDATE
            Stan_Na = kn.Stan_Na
		;
        */
		UPDATE `kontrah_nip24` n
		SET
			n.Update_Status = 'INIT.ADR'
		WHERE
			n.NIP = _K2 
			AND n.IDCountry = IF(IFNULL(_K1, '') = '', 'PL', _K1)
		;
		UPDATE `kontrah_nip24` n
		SET
			n.KontrGUID_local = IF(IFNULL(_K3, '') = '', n.KontrGUID_local, _K3),
			n.Update_Status = _Param_A
		WHERE
			n.NIP = _K2 
			AND n.IDCountry = IF(IFNULL(_K1, '') = '', 'PL', _K1)
		;
	END IF;
	
	IF _MODE LIKE ('SERVICE.NIP.GET.%') THEN
	/* *************************************************************************************************************************************************************
		Select sznurki danych - zwraca w polach
		CALL kontrah_nip24_tools('SERVICE.NIP.GET.NIP24', '', 'nip', '', '', '');	
		CALL kontrah_nip24_tools('SERVICE.NIP.GET.ADR', '', 'nip', '', '', '');	
		IN _K1 IDCountry
		IN _K2 NIP,	

	**************************************************************************************************************************************************************/
	--	aby sie pozbyc przecinka ewntualnego to markujemy end stringa
		IF _MODE IN ('SERVICE.NIP.GET.NIP24') THEN
			SELECT 
				kn.* 
			FROM `kontrah_nip24` kn 
			WHERE
				kn.NIP = _K2 
				AND kn.IDCountry = IF(IFNULL(_K1, '') = '', 'PL', _K1)
			;
		ELSE
			SELECT 
				al.* 
			FROM `kontrah_nip24` kn 
			INNER JOIN `geo_int_adr_link` al ON al.GUID = CONCAT('NIP:', kn.IDCountry, kn.NIP, ':ADR')
			WHERE
				kn.NIP = _K2 
				AND kn.IDCountry = IF(IFNULL(_K1, '') = '', 'PL', _K1)
			;
		END IF;
		
	END IF;

	
	/************************************************************************************************************************************************************* 
		STRONA klienta czyli bazy naszego klienta wołającego pobieranie danych z GUS-u/NIP 24
	*************************************************************************************************************************************************************/
	IF _MODE LIKE ('CLIENT.NIP.PUT.%') THEN
	/* *************************************************************************************************************************************************************
		Wstawia w bazie docelowej dane kontrahenta (NIP i adres) sznurki danych w memeo 
		CALL kontrah_nip24_tools('CLIENT.NIP.PUT.NIP24.amd.ADR', '', '', 'KontrGUID', '', 'dane');	
		IN _K3 KontrGUID,	
		_Param_B sznurek danych (oddzielone markerami <NIP> i <ADR_LINK>)

	**************************************************************************************************************************************************************/
		SET 
			_NIP = IFNULL(_K2, ''),
			_KontrGUID = _K3,
			@.Fields.NIP24 = _FetchParams('', _Param_B, 'NIP', ''),
			@.Fields.ADR = _FetchParams('', _Param_B, 'ADR_LINK', '')
		;
	--	//////////////////////////////////////////////////////	
	--	Dane kontraheta - WSTAW gdy brak
		INSERT IGNORE INTO `kontrah` SET GUID = _KontrGUID, NIP = _NIP, PlatnikGUID = _KontrGUID;
	--	Dane kontraheta - uzupelnianie
		DROP TEMPORARY TABLE IF EXISTS `t_put_kontrah_nip24`;
		CREATE TEMPORARY TABLE `t_put_kontrah_nip24` LIKE `kontrah_nip24`;
		SET _SQLText =
			"INSERT IGNORE INTO `t_put_kontrah_nip24`\nSET \n<BODY>\n;"
		;
		SET @_SQLText.PUT.Fields.NIP24 = 'NIP24';
		SET @.Fields.NIP24 = CONCAT(TRIM(@.Fields.NIP24), '<END>');
		SET @.Fields.NIP24 = REPLACE(REPLACE(@.Fields.NIP24, ',<END>', ''), '<END>', '');
		SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, "\\'", "'");
		SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, '\\"', '"');
		SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, '",', '",\n');
		SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, ''',', ''',\n');
		
		SET _SQLText = REPLACE(_SQLText, '<BODY>', @.Fields.NIP24);
		SET @_SQLText.PUT.Fields.NIP24 = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
		
		/*
		Kiedy bedziemy gromadzic w `kontrah_nip24` po stronie klienta to ODKOMENTUJ
		REPLACE `kontrah_nip24`
			SELECT * FROM `t_put_kontrah_nip24`
		;
		UPDATE `kontrah_nip24` kn, `t_put_kontrah_nip24` t
		SET
			kn.Update_Status = 'SET.ADR.kontrah'
		WHERE
			kn.IDCountry = t.IDCountry
			AND
			kn.NIP = t.NIP
		;
		*/
    --  Może brakować tego miejsca i adrsu - UZUPEŁNIJ
        INSERT IGNORE INTO `geo_int_miejsca`
        (ID, ParentId, ParentIdMiasto, IDCountry, ID_WOJ, ID_POW, ID_GMI, ID_Adm, GUS_WOJ, GUS_POW, GUS_GMI, Typ_GMI, GUS_RM, GUS_MZ, Nazwa, Stan_Na)
        SELECT 
            kn.ID,
            kn.ID AS ParentId,
            kn.ID AS ParentIdMiasto,
            kn.IDCountry,
            (@.ID_WOJ := CONCAT('PL', 'W', kn.GUS_WOJ)) AS ID_WOJ,
                    -- gm.ID_WOJ,
            (@.ID_POW := CONCAT(@.ID_WOJ, 'P', kn.GUS_POW)) AS ID_POW,
                    -- gm.ID_POW,
            (@.ID_GMI := CONCAT(@.ID_POW, 'G', kn.GUS_GMI, '-', kn.Typ_GMI)) AS ID_GMI,
                    -- gm.ID_GMI,
            @.ID_GMI AS ID_Adm,
            
            kn.GUS_WOJ,		-- gm.GUS_WOJ,
            kn.GUS_POW,		-- gm.GUS_POW,
            kn.GUS_GMI,		-- gm.GUS_GMI,
            kn.Typ_GMI,		-- gm.Typ_GMI,
            '' AS GUS_RM,	--	skąd wziąc ???
            1 AS GUS_MZ,
            kn.Nazwa,		-- gm.Nazwa,
            kn.Stan_Na
            -- kn.*
        FROM (	
            SELECT 
                -- IFNULL(NULLIF(kn.adr_geo_int_adresy_ID, ''), kn.reg_geo_int_adresy_ID) AS ID,
                CONCAT('PL', cityCode) AS ID,
                kn.IDCountry,
                kn.StateCode AS GUS_WOJ,
                kn.CountyCode AS GUS_POW,
                IF((@GUS_GMI_L := LENGTH(@GUS_GMI := kn.CommunityCode) > 2),
                    SUBSTR(@GUS_GMI, 1, 2),
                    @GUS_GMI
                ) AS GUS_GMI,			
                IF(@GUS_GMI_L,
                    SUBSTR(@GUS_GMI, 3, 1),
                    ''
                ) AS Typ_GMI,			
                
                kn.City AS Nazwa,
                kn.PostCode AS ZipCode,
                kn.adr_geo_int_miejsca_ID,
                -- IF(g.Rodz_GMI != '', g.Rodz_GMI, 0) AS Typ_GMI, 
                -- g.RM AS GUS_RM, g.MZ AS GUS_MZ, 
                kn.CreateAt AS Stan_Na

                -- kn.*
            FROM `t_put_kontrah_nip24` kn 
            WHERE
                TRUE
                -- and kn.NIP = '7940003819'
        ) kn
        -- LEFT JOIN `geo_int_miejsca` gm ON gm.ID = kn.adr_geo_int_miejsca_ID
        WHERE
            TRUE
        ON DUPLICATE KEY UPDATE
            Stan_Na = kn.Stan_Na
        ;
        INSERT IGNORE INTO `geo_int_adresy`
        (
            ID, IDCountry, ID_Adm, ID_Miejsca, Nazwa_1, Nazwa_2, 
            GUS_WOJ, GUS_POW, GUS_GMI, Typ_GMI, GUS_SYM, GUS_SYM_UL, GUS_CECHA, 
            Srch_Cecha_N1_N2, Srch_Cecha_N2_N1, Srch_N1_N2, Srch_N2_N1,
            ZipCode, Stan_Na
        )
        SELECT
            ID, IDCountry, ID_Adm, ID_Miejsca, Nazwa_1, Nazwa_2, 
            GUS_WOJ, GUS_POW, GUS_GMI, Typ_GMI, GUS_SYM, GUS_SYM_UL, GUS_CECHA, 
            Srch_Cecha_N1_N2, Srch_Cecha_N2_N1, Srch_N1_N2, Srch_N2_N1,
            ZipCode, Stan_Na
        FROM (	
            SELECT 
                IFNULL(NULLIF(kn.adr_geo_int_adresy_ID, ''), kn.reg_geo_int_adresy_ID) AS ID,
                -- concat_WS('-', kn.CityCode, kn.StreetCode) AS ID2,
                gm.IDCountry, gm.ID_Adm, gm.ID AS ID_Miejsca,
                (@.Nazwa_1 := REPLACE(REPLACE(REPLACE(REPLACE(kn.street, 
                    'ul.', ''), 'pl.', ''), 'al.', ''), 'os.', '')  
                )AS Nazwa_1,
                (@.Nazwa_2 := '') AS Nazwa_2,
                gm.GUS_WOJ, gm.GUS_POW, gm.GUS_GMI, gm.Typ_GMI, 
                kn.CityCode AS GUS_SYM, kn.StreetCode AS GUS_SYM_UL,
                (@.GUS_CECHA :=
                IF(kn.street LIKE '%ul.%', 'ul.',
                IF(kn.street LIKE '%pl.%', 'pl.',
                IF(kn.street LIKE '%al.%', 'al.',
                IF(kn.street LIKE '%os.%', 'os.',
                ''
                ))))) AS GUS_CECHA,
                CONCAT(@.GUS_CECHA, CONCAT_WS(' ', IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1)), IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2)))) AS Srch_Cecha_N1_N2,
                CONCAT(@.GUS_CECHA, CONCAT_WS(' ', IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2)), IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1)))) AS Srch_Cecha_N2_N1,
                CONCAT_WS(' ', IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1)), IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2))) AS Srch_N1_N2,
                CONCAT_WS(' ', IF(TRIM(@.Nazwa_2) = '', NULL, TRIM(@.Nazwa_2)), IF(TRIM(@.Nazwa_1) = '', NULL, TRIM(@.Nazwa_1))) AS Srch_N2_N1,
                
                -- kn.PostCode AS ZipCode,
                IF(LOCATE('-', kn.PostCode) > 0, kn.PostCode, INSERT(kn.PostCode, 3, 0, '-')) AS ZipCode,
                kn.adr_geo_int_miejsca_ID,
                -- kn.lastUpdateDate AS Stan_Na
                kn.CreateAt AS Stan_Na
                -- IF(g.Rodz_GMI != '', g.Rodz_GMI, 0) AS Typ_GMI, 
                -- g.RM AS GUS_RM, g.MZ AS GUS_MZ, 
                
                -- kn.*
            FROM `t_put_kontrah_nip24` kn 
            LEFT JOIN `geo_int_miejsca` gm ON gm.ID = CONCAT('PL', kn.cityCode)
            WHERE
                TRUE
                -- and kn.NIP = '7940003819'
        ) kn
        ON DUPLICATE KEY UPDATE
            Stan_Na = kn.Stan_Na
        /*
            Srch_Cecha_N1_N2 = CONCAT(a.GUS_CECHA, CONCAT_WS(' ', IF(TRIM(a.Nazwa_1) = '', NULL, TRIM(a.Nazwa_1)), IF(TRIM(a.Nazwa_2) = '', NULL, TRIM(a.Nazwa_2)))),
            Srch_Cecha_N2_N1 = CONCAT(a.GUS_CECHA, CONCAT_WS(' ', IF(TRIM(a.Nazwa_2) = '', NULL, TRIM(a.Nazwa_2)), IF(TRIM(a.Nazwa_1) = '', NULL, TRIM(a.Nazwa_1)))),
            Srch_N1_N2 = CONCAT_WS(' ', IF(TRIM(a.Nazwa_1) = '', NULL, TRIM(a.Nazwa_1)), IF(TRIM(a.Nazwa_2) = '', NULL, TRIM(a.Nazwa_2))),
            Srch_N2_N1 = CONCAT_WS(' ', IF(TRIM(a.Nazwa_2) = '', NULL, TRIM(a.Nazwa_2)), IF(TRIM(a.Nazwa_1) = '', NULL, TRIM(a.Nazwa_1)))
        */
		;
    
	--	Ustaw KontrGUID
		UPDATE `t_put_kontrah_nip24` t
		SET	
			t.KontrGUID_local = _KontrGUID, -- (SELECT k.GUID FROM `kontrah` k WHERE k.NIP = t.NIP AND t.IDCountry = 'PL' LIMIT 1)
			t.shortname = 
				IF(IFNULL(t.shortname, '') = '',
					SUBSTRING_INDEX(
						TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(t.Name), '"', ''), '''', ''), 'SP. ZOO', ''), 'SP. C', ''), 'SP.ZOO', ''), 'SP.C', ''), 'S.C', ''), ',', ''), '.', ''), '  ', ' ')),
						' ', 3
					),
					''
				)
		;
		--  LEAVE S_ta_geo_conv_kontrah_;
	--	Teraz do `kontrah` wstaw dane
		UPDATE `kontrah` k, `t_put_kontrah_nip24` t
		SET	
			k.Nazwa = t.Name,
			k.Symbol = IF(IFNULL(k.Symbol, '') = '', t.shortname, k.Symbol),
			k.regon = t.regon,
			-- k.opis_dzialalnosci = t.regon,
			-- k.regon = t.regon,
			k.regon = t.regon,
			k.NIP = IF(IFNULL(k.NIP, '') = '', t.NIP, k.NIP)
			
		WHERE
			k.GUID = t.KontrGUID_local
			--	amoze po nipie ??? wtedy wiele rekordow zmieniam
		;
		IF @session.globals.Owner in ('SKOK') THEN
			UPDATE `kontrah` k, `t_put_kontrah_nip24` t
			SET k.symbol=lpad(idn,5,'0')
			WHERE k.GUID = t.KontrGUID_local;
		END IF;
	--	//////////////////////////////////////////////////////	
	--	ADRESY	
		DROP TEMPORARY TABLE IF EXISTS `t_put_geo_int_adr_link`;
		-- CREATE TEMPORARY TABLE `t_put_geo_int_adr_link` LIKE `geo_int_adr_link`;
		CREATE TEMPORARY TABLE `t_put_geo_int_adr_link` SELECT * FROM `geo_int_adr_link` WHERE FALSE;
		ALTER TABLE `t_put_geo_int_adr_link` ADD PRIMARY KEY GUID(GUID);
		SET _SQLText =
			"INSERT IGNORE INTO `t_put_geo_int_adr_link`\nSET \n<BODY>\n;"
		;
		SET @_SQLText.PUT.Fields.ADR = 'ADR';
		SET @.Fields.ADR = CONCAT(TRIM(@.Fields.ADR), '<END>');
		SET @.Fields.ADR = REPLACE(REPLACE(@.Fields.ADR, ',<END>', ''), '<END>', '');
		SET @.Fields.ADR = REPLACE(@.Fields.ADR, "\\'", "'");
		SET @.Fields.ADR = REPLACE(@.Fields.ADR, '\\"', '"');
		SET @.Fields.ADR = REPLACE(@.Fields.ADR, '",', '",\n');
		SET @.Fields.ADR = REPLACE(@.Fields.ADR, ''',', ''',\n');
		SET _SQLText = REPLACE(_SQLText, '<BODY>', @.Fields.ADR);
		SET @_SQLText.PUT.Fields.ADR = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
	--	Teraz namasc w temporarce `t_put_geo_int_adr_link` specyficzne poa : GUIDy i inne...
		UPDATE `t_put_geo_int_adr_link` tal
		SET
			tal.ObjGUID = _KontrGUID,
			tal.objTable = 'kontrah',
			tal.AdrTyp = ';F;K;W;'
		;
	--	Pobierz dotychczasowe	
		DROP TEMPORARY TABLE IF EXISTS `t_kontrah_geo_int_adr_link`;
		-- CREATE TEMPORARY TABLE `t_kontrah_geo_int_adr_link` LIKE `geo_int_adr_link`;
		CREATE TEMPORARY TABLE `t_kontrah_geo_int_adr_link` SELECT * FROM `geo_int_adr_link` WHERE FALSE;
		ALTER TABLE `t_kontrah_geo_int_adr_link` ADD PRIMARY KEY GUID(GUID);
		
		INSERT INTO `t_kontrah_geo_int_adr_link`
			SELECT
				al.*
			FROM `geo_int_adr_link` al
			WHERE
				al.ObjGUID = _KontrGUID
				AND al.objTable = 'kontrah'
				AND (al.AdrTyp = 'F' OR CONCAT(';', al.AdrTyp, ';') LIKE '%;F;%')
		;
		/*
	--	Do usuniecia te ktore maja tylko "F" lub ""	
		UPDATE `t_kontrah_geo_int_adr_link` tal
		SET
			tal.Status = -1
		WHERE
			tal.AdrTyp IN ('F', '')
		;
		*/
	--	Usun dotyczczasowe	
		DELETE al FROM `geo_int_adr_link` al, `t_kontrah_geo_int_adr_link` tal WHERE al.GUID = tal.GUID;
	--	Teraz wstaw nowe	
		REPLACE `geo_int_adr_link` 
			SELECT * FROM `t_put_geo_int_adr_link` tal
		;
	--	ustwa dotychczasowe jako old i ...	
		UPDATE `t_kontrah_geo_int_adr_link` tal
		SET
			tal.GUID = MD5(CONCAT(tal.ObjGUID, tal.objTable, tal.AdrTyp, 'OLD')),
			tal.Status = -1,
			tal.AdrTyp = ';OLD;'
		;
	--	Usun poprzednie dotyczczasowe	
		DELETE al FROM `geo_int_adr_link` al, `t_kontrah_geo_int_adr_link` tal WHERE al.GUID = tal.GUID;
	--	wstaw do adresow jako old	
		REPLACE `geo_int_adr_link` 
			SELECT * FROM `t_kontrah_geo_int_adr_link` tal
		;
	--	I jeszcze w `kontrah` popraw dane adresowe
		UPDATE `kontrah` k, `t_put_geo_int_adr_link` tal
		SET	
			k.Ulica = 
			_f_AdrLink_Select_Mask_from_adr_MASKS('<UM> <N>', tal.adr_MASKS, '', '', ''),
			k.NazwaMiasta = 
			_f_AdrLink_Select_Mask_from_adr_MASKS('<MaP>', tal.adr_MASKS, '', '', ''),
			k.zipcode = tal.zipcode
		WHERE
			TRUE
			AND k.GUID = tal.ObjGUID
			-- a jeszcze dodam...
			AND k.GUID = _KontrGUID
			--	amoze po nipie ??? wtedy wiele rekordow zmieniam
		;
	END IF;

	
	IF _MODE = 'CLIENT.PUT.2.kontrah.and.adr' THEN
	/* *************************************************************************************************************************************************************
		Wstawia w bazie docelowej dane kontrahenta (NIP i adres) sznurki danych w memeo 
		CALL kontrah_nip24_tools('CLIENT.PUT.2.kontrah.and.adr', '', '', 'KontrGUID', '', 'dane');	
		IN _K3 KontrGUID,	
		_Param_B sznurek danych (oddzielone markerami <NIP> i <ADR_LINK>)

	**************************************************************************************************************************************************************/
		CALL kontrah_nip24_tools('CLIENT.PUT.Kontrah', _K1, _K2, _K3, _Param_A, _Param_B);	
		CALL kontrah_nip24_tools('CLIENT.PUT.Adress.Kontrah', _K1, _K2, _K3, _Param_A, _Param_B);	
	END IF;
	

			IF _MODE = 'CLIENT.PUT.Kontrah' THEN
			/* *************************************************************************************************************************************************************
				Wstawia w bazie docelowej dane kontrahenta (NIP i adres) sznurki danych w memeo 
				CALL kontrah_nip24_tools('CLIENT.PUT.Kontrah', '', '', 'KontrGUID', '', 'dane');	
				IN _K3 KontrGUID,	
				_Param_B sznurek danych (oddzielone markerami <NIP> i <ADR_LINK>)

			**************************************************************************************************************************************************************/
			
				SET 
					_NIP = IFNULL(_K2, ''),
					_KontrGUID = _K3,
					@.Fields.NIP24 = _FetchParams('', _Param_B, 'NIP', ''),
					@.Fields.ADR = _FetchParams('', _Param_B, 'ADR_LINK', '')
				;
			--	//////////////////////////////////////////////////////	
			--	Dane kontraheta - WSTAW gdy brak
				INSERT IGNORE INTO `kontrah` SET GUID = _KontrGUID, NIP = _NIP, PlatnikGUID = _KontrGUID;
			--	Dane kontraheta - uzupelnianie
				DROP TEMPORARY TABLE IF EXISTS `t_put_kontrah_nip24`;
				CREATE TEMPORARY TABLE `t_put_kontrah_nip24` LIKE `kontrah_nip24`;
				SET _SQLText =
					"INSERT IGNORE INTO `t_put_kontrah_nip24`\nSET \n<BODY>\n;"
				;
				SET @_SQLText.PUT.Fields.NIP24 = 'NIP24';
				SET @.Fields.NIP24 = CONCAT(TRIM(@.Fields.NIP24), '<END>');
				SET @.Fields.NIP24 = REPLACE(REPLACE(@.Fields.NIP24, ',<END>', ''), '<END>', '');
				SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, "\\'", "'");
				SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, '\\"', '"');
				SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, '",', '",\n');
				SET @.Fields.NIP24 = REPLACE(@.Fields.NIP24, ''',', ''',\n');
				
				SET _SQLText = REPLACE(_SQLText, '<BODY>', @.Fields.NIP24);
				SET @_SQLText.PUT.Fields.NIP24 = _SQLText;
				CALL _ExecuteCmd(_SQLText, @Error);
				
				/*
				Kiedy bedziemy gromadzic w `kontrah_nip24` po stronie klienta to ODKOMENTUJ
				REPLACE `kontrah_nip24`
					SELECT * FROM `t_put_kontrah_nip24`
				;
				UPDATE `kontrah_nip24` kn, `t_put_kontrah_nip24` t
				SET
					kn.Update_Status = 'SET.ADR.kontrah'
				WHERE
					kn.IDCountry = t.IDCountry
					AND
					kn.NIP = t.NIP
				;
				*/
			--	Ustaw KontrGUID
				UPDATE `t_put_kontrah_nip24` t
				SET	
					t.KontrGUID_local = _KontrGUID, -- (SELECT k.GUID FROM `kontrah` k WHERE k.NIP = t.NIP AND t.IDCountry = 'PL' LIMIT 1)
					t.shortname = 
						IF(IFNULL(t.shortname, '') = '',
							SUBSTRING_INDEX(
								TRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(UPPER(t.Name), '"', ''), '''', ''), 'SP. ZOO', ''), 'SP. C', ''), 'SP.ZOO', ''), 'SP.C', ''), 'S.C', ''), ',', ''), '.', ''), '  ', ' ')),
								' ', 3
							),
							''
						)
				;
				
			--	Teraz do `kontrah` wstaw dane
				UPDATE `kontrah` k, `t_put_kontrah_nip24` t
				SET	
					k.Nazwa = t.Name,
					k.Symbol = IF(IFNULL(k.Symbol, '') = '', t.shortname, k.Symbol),
					k.regon = t.regon,
					-- k.opis_dzialalnosci = t.regon,
					-- k.regon = t.regon,
					k.regon = t.regon,
					k.NIP = IF(IFNULL(k.NIP, '') = '', t.NIP, k.NIP)
					
				WHERE
					k.GUID = t.KontrGUID_local
					--	amoze po nipie ??? wtedy wiele rekordow zmieniam
				;
				-- symbol na podst idn
				IF @session.globals.Owner in ('SKOK') THEN
					UPDATE `kontrah` k, `t_put_kontrah_nip24` t
					SET k.symbol=lpad(idn,5,'0')
					WHERE k.GUID = t.KontrGUID_local;
				END IF;
					
				
			END IF;
	
			IF _MODE LIKE 'CLIENT.PUT.Adress.%' THEN
			/* *************************************************************************************************************************************************************
				Wstawia w bazie docelowej dane adresowe kontrahenta (adres do kontrahenta lub jao NIP do porownan) sznurki danych w memeo 
				UWAGA
				'CLIENT.PUT.Adress.Kontrah'
				wstawia do `geo_int_adr_link` adres jako adres kontrahenta objTable = 'kontrah' i AdrTyp = ';F;'
				CALL kontrah_nip24_tools('CLIENT.PUT.Adress.Kontrah', '', '', 'KontrGUID', '', 'dane');	
				
				'CLIENT.PUT.Adress.Nip'
				wstawia do `geo_int_adr_link` adres jako adres NIP objTable = 'kontrah' i AdrTyp = ';NIP;'
				CALL kontrah_nip24_tools('CLIENT.PUT.Adress.Nip', '', '', 'KontrGUID', '', 'dane');	
				
				IN _K3 KontrGUID,	
				_Param_B sznurek danych (oddzielone markerami <NIP> i <ADR_LINK>)

			**************************************************************************************************************************************************************/
				SET 
					_NIP = IFNULL(_K2, ''),
					_KontrGUID = _K3,
					@.Fields.NIP24 = _FetchParams('', _Param_B, 'NIP', ''),
					@.Fields.ADR = _FetchParams('', _Param_B, 'ADR_LINK', '')
				;
			--	ADRESY	
			
				DROP TEMPORARY TABLE IF EXISTS `t_put_geo_int_adr_link`;
				CREATE TEMPORARY TABLE `t_put_geo_int_adr_link` LIKE `geo_int_adr_link`;
				SET _SQLText =
					"INSERT IGNORE INTO `t_put_geo_int_adr_link`\nSET \n<BODY>\n;"
				;
				SET @_SQLText.PUT.Fields.ADR = 'ADR';
				SET @.Fields.ADR = CONCAT(TRIM(@.Fields.ADR), '<END>');
				SET @.Fields.ADR = REPLACE(REPLACE(@.Fields.ADR, ',<END>', ''), '<END>', '');
				SET @.Fields.ADR = REPLACE(@.Fields.ADR, "\\'", "'");
				SET @.Fields.ADR = REPLACE(@.Fields.ADR, '\\"', '"');
				SET @.Fields.ADR = REPLACE(@.Fields.ADR, '",', '",\n');
				SET @.Fields.ADR = REPLACE(@.Fields.ADR, ''',', ''',\n');
				SET _SQLText = REPLACE(_SQLText, '<BODY>', @.Fields.ADR);
				SET @_SQLText.PUT.Fields.ADR = _SQLText;
				CALL _ExecuteCmd(_SQLText, @Error);
			--	Teraz namasc w temporarce `t_put_geo_int_adr_link` specyficzne poa : GUIDy i inne...
				UPDATE `t_put_geo_int_adr_link` tal
				SET
				--	dla adresow kontrahenta GUID pozostaje ale gdy wstawiamy NIP do porownan to GUID ustaw jako sam nip bez slowa adr
					tal.GUID = IF(_MODE = 'CLIENT.PUT.Adress.Kontrah', tal.GUID, REPLACE(tal.GUID, ':ADR', '')),
					tal.ObjGUID = _KontrGUID,
					tal.objTable = 'kontrah',
					tal.AdrTyp = IF(_MODE = 'CLIENT.PUT.Adress.Kontrah', ';F;K;W;', ';NIP;'),
				--	Uwaga zipcode dostajemy bez kreski niestety
					tal.zipcode = IF(LOCATE('-', tal.zipcode) > 0, tal.zipcode, INSERT(tal.zipcode, 3, 0, '-'))
				;
			--	Dotychczasowe -> OLD
			--	Ale zanim wstawimy nowe to przepiszemy stare jako old i usuniemy
			--	TYLKO dla adresow kontrahenta gdy wstawiamy NIP do porownan to niczego nie przenosimy 
			--	Dotychczasowe -> OLD Usuwanie Dotychczasowych
				IF _MODE = 'CLIENT.PUT.Adress.Kontrah' THEN
				--	Dla zwyklego wstawianie przenies stary-dotychczasowy adres
				--	Pobierz dotychczasowe	
					DROP TEMPORARY TABLE IF EXISTS `t_kontrah_geo_int_adr_link`;
					CREATE TEMPORARY TABLE `t_kontrah_geo_int_adr_link` LIKE `geo_int_adr_link`;
					INSERT INTO `t_kontrah_geo_int_adr_link`
						SELECT
							al.*
						FROM `geo_int_adr_link` al
						WHERE
							al.ObjGUID = _KontrGUID
							AND al.objTable = 'kontrah'
							AND (al.AdrTyp = 'F' OR CONCAT(';', al.AdrTyp, ';') LIKE '%;F;%')
					;
					/*
				--	Do usuniecia te ktore maja tylko "F" lub ""	
					UPDATE `t_kontrah_geo_int_adr_link` tal
					SET
						tal.Status = -1
					WHERE
						tal.AdrTyp IN ('F', '')
					;
					*/
				--	Usun dotyczczasowe	
					DELETE al FROM `geo_int_adr_link` al, `t_kontrah_geo_int_adr_link` tal WHERE al.GUID = tal.GUID;
				END IF;
				
			--	Teraz wstaw nowe	
				REPLACE `geo_int_adr_link` 
					SELECT * FROM `t_put_geo_int_adr_link` tal
				;
				
			--	Dotychczasowe -> OLD wstawianie jako OLD
				IF _MODE = 'CLIENT.PUT.Adress.Kontrah' THEN
				--	ustaw dotychczasowe jako old i ...	
					UPDATE `t_kontrah_geo_int_adr_link` tal
					SET
						tal.GUID = MD5(CONCAT(tal.ObjGUID, tal.objTable, tal.AdrTyp, 'OLD')),
						tal.Status = -1,
						tal.AdrTyp = ';OLD;'
					;
				--	Usun poprzednie dotyczczasowe	
					DELETE al FROM `geo_int_adr_link` al, `t_kontrah_geo_int_adr_link` tal WHERE al.GUID = tal.GUID;
				--	wstaw do adresow jako old	
					REPLACE `geo_int_adr_link` 
						SELECT * FROM `t_kontrah_geo_int_adr_link` tal
					;
				END IF;
			
				
			--	I jeszcze w `kontrah` popraw dane adresowe
				UPDATE `kontrah` k, `t_put_geo_int_adr_link` tal
				SET	
					k.Ulica = 
					_f_AdrLink_Select_Mask_from_adr_MASKS('<UM> <N>', tal.adr_MASKS, '', '', ''),
					k.NazwaMiasta = 
					_f_AdrLink_Select_Mask_from_adr_MASKS('<MaP>', tal.adr_MASKS, '', '', ''),
					k.zipcode = tal.zipcode
				WHERE
					TRUE
					AND k.GUID = tal.ObjGUID
					-- a jeszcze dodam...
					AND k.GUID = _KontrGUID
					--	amoze po nipie ??? wtedy wiele rekordow zmieniam
				;
			END IF;
END$$


DELIMITER ;

/*
$Log$
*/
