 SET NAMES utf8;

DELIMITER $$

DROP PROCEDURE IF EXISTS `e_dekl_jpk`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `e_dekl_jpk`(
    IN _MODE VARCHAR(38),
    IN _GUID VARCHAR(38),
    IN _rok VARCHAR(38),
    IN _miesiac VARCHAR(38),
    IN _NaDzien VARCHAR(10),
    IN _OdDnia VARCHAR(10),
    IN _DoDnia VARCHAR(10),
    IN _CelZlozenia VARCHAR(10),
    IN _XML LONGTEXT,
    IN _Param_A VARCHAR(50),
    IN _Param_B VARCHAR(50), -- okresla numer pliku za dany okres dla JPK VAT3, okresla magazyn dla JPK MAG, okresla konto dla JPK WB
    IN _Param_C VARCHAR(50), -- okresla czy wybieramy opcje DEBUG czy generowanie zestawienia
    IN _Param_D VARCHAR(50)  -- okresla wybrane zestawienie JPK
    )
SPROC: BEGIN
		DECLARE _S LONGTEXT;
		DECLARE _XML LONGTEXT;
		DECLARE _XMLnag LONGTEXT;
		DECLARE _XMLnag2 LONGTEXT;
		DECLARE _XMLpoz LONGTEXT;
		DECLARE _XMLpoz1 LONGTEXT;
		DECLARE _XMLpoz_s LONGTEXT;
		DECLARE _XMLpoz_z LONGTEXT;
		DECLARE _XMLpoz_p LONGTEXT;
		DECLARE _KodUS VARCHAR(50);
		DECLARE _NIP VARCHAR(50);
		DECLARE _REGON VARCHAR(50);
		DECLARE _PelnaNazwa VARCHAR(255); -- nazwa podmiotu wystawiajacego jpk
-- 		DECLARE _Adres VARCHAR(255); -- adres podmiotu wystawiajacego jpk (fa), co jest glupota zupelna
		DECLARE _zm01 VARCHAR(255); -- zmienna do wszystkiego
		DECLARE cSL INT(11);
		DECLARE _liczba_wierszy INT(11);
		DECLARE _wartosc_faktur VARCHAR(18);
		DECLARE _wartosc_dok VARCHAR(18);
		DECLARE _wartosc_dok2 VARCHAR(18);
		DECLARE _podatek_s VARCHAR(18);
		DECLARE _podatek_z VARCHAR(18);
		DECLARE _e_Doc_Def_ID VARCHAR(100);
		DECLARE _proc_struktura DOUBLE(16,2);
		DECLARE _RodzajDekl INT(11); -- miesieczna/kwartalna
		DECLARE _RodzajKorekty INT(11); -- calosciowa/tylko ewidencja/tylko deklaracja
		
		DECLARE _K_13 VARCHAR(100); -- definicje przypisanych rejestrow vat
		DECLARE _K_15 VARCHAR(100); 
		DECLARE _K_17 VARCHAR(100); 
		DECLARE _K_19 VARCHAR(100); 
		DECLARE _K_40 VARCHAR(100); 
		DECLARE _K_42 VARCHAR(100); 
		DECLARE _K_68 VARCHAR(100);
		DECLARE _KPOd VARCHAR(100); -- KorektaPodstawyOp_def
		
		SET cSL := 2;

		SET _GUID = IFNULL(_GUID, '');
		SET _S = '';
		SET SESSION group_concat_max_len = 1024*1024*1024;
  
/*
Procedura dla zestawien dla JPK (jednolity plik kontrolny) na potrzeby kontroli skarbowej

#######Dla plikow jpk_v7m i jpk_v7k#######
CALL e_dekl_vat_tools('jpkv7.data.insert', '2020', '10', '{88913E93-1550-44FA-8708-CC6CB4266053}', @_eDeklContent, 1, '1') ;
SET '{88913E93-1550-44FA-8708-CC6CB4266053}'
CALL e_dekl_jpk('JPK_V7M',_RecGUID,'','' ,'', '','', '', '','NALICZ', '', '','');
CALL e_dekl_jpk('JPK_V7M','{8E56B9CE-B3AF-44D7-A4AA-14A431A08CC1}','','' ,'', '','', '', '','NALICZ', '', '','');
SELECT * FROM  ht_dekl_jpkv7_ewinne WHERE guid_dekl='{88913E93-1550-44FA-8708-CC6CB4266053}';
SELECT * FROM ht_dekl_jpkv7_ewz WHERE guid_dekl='{8E56B9CE-B3AF-44D7-A4AA-14A431A08CC1}';
SELECT * FROM t_vat_inne;

SET @session.globals.Owner='CRIST';
CALL e_dekl_jpk('JPK_V7M','{ADC96649-8989-47AB-BD0C-F71DF9A9AFE1}','','' ,'', '','', '', '','RAPORT_', '', '','');
CALL e_dekl_jpk('JPK_V7M','{F3D05432-F0CD-4D51-BA5C-CAB88AD2841D}','','' ,'', '','', '', '','RAPORT', '', '','');
CALL e_dekl_jpk('JPK_V7M','{0A7230E9-7645-45E6-9474-42DD9F03BFB3}','','' ,'', '','', '', '','CALC', '', '','');
CALL e_dekl_jpk('JPK_V7M','{8352AEA7-FDCA-4814-9123-D4001B2865F0}','','' ,'', '','', '', '','CREATE_XML', '', '','');

insert into `IReg` (`IdPath`, `IdKey`, `IdVal`, `SecureLevel`, `NotUsed`, `valuex`, `ValueN`, `ValueI`, `ValueB`, `ValueD`, `ValueT`, `ValueBlob`, `ValueA`, `guid`, `Opis`, `rok`)
values('INT\\SYSTEM\\MOTLAWA\\DeklaracjeVAT','JPK_definicja_z_VAT7','3','0','0',NULL,'0.0000','0','0',NULL,'00:00:00',NULL,'VAT-7 (17) / VAT-7K (11)','f2fd352d0435809bd0f983ec53c27a32',NULL,'0');

SELECT valuea, i.* FROM IReg i WHERE idpath LIKE '%owner%' OR idpath LIKE '%dane%firmy%'
OR idpath LIKE '%vat7%' ORDER BY idkey;

Wykorzystwane zmmienne systemowe:
@session.globals.Owner - wlasciciel aplikacji, wpis z tabeli _db_globals

@session.HT.IR.RV_DodajBezOdliczenDoNetto - dodaj wartosci pozycji ze stawka "bez odliczen" (nr 18) do kwoty netto
w rejestrze vat. Decyduje o tym klucz "RV_DodajBezOdliczenDoNetto" domyslnie ustawiony na nie

####dodac wykorzystywanie nowej adresacji o czym decyduje klucz DOC_with_AdrLink z _db_globals
*/

IF (_MODE = 'NAGLOWEK') THEN
	BEGIN
	DECLARE _CelZlozenia, _rok, _okres, _KodUS, 
	  _e_doc_id, _form_ver, 
	  _e_doc_type,  -- _data_wypelnienia,  
	  _doc_ver, _kodPodatku VARCHAR(100);

	DECLARE _REGON, _NIP     VARCHAR(20);
	DECLARE _Platnik_nazwisko, _Platnik_imie, _PelnaNazwa VARCHAR(200);  
	DECLARE _RodzPodat INTEGER;
	DECLARE _email VARCHAR(50);
	DECLARE _Platnik_data_urodzenia DATE;
		  
	DROP TABLE IF EXISTS t_jpk;
	CREATE TEMPORARY TABLE t_jpk (
					guid VARCHAR(38),
					xmlnag LONGTEXT,
					xmlnag2 LONGTEXT,
					xmlv7 LONGTEXT,
					xmlpoz1 LONGTEXT,
					xmlpoz2 LONGTEXT,
					xmlpoz3 LONGTEXT,
					xmlpoz4 LONGTEXT,

	KEY guid (guid)) DEFAULT CHARSET=utf8;

	INSERT INTO t_jpk VALUES (_Param_D,'','','','','','','');

	-- SET NAMES "utf8";
	SELECT 
			CelZlozenia, RodzajDekl, RodzajKorekty, rok, miesiac, 
			d.doc_id, _varSTR(d.form_ver), 
			d.doc_type,	-- p.data_wypelnienia, 	
			d.ID, d.doc_ver, d.doc_owner
		INTO 
		  _CelZlozenia, _RodzajDekl, _RodzajKorekty, _rok, _okres,
		  _e_doc_id, _form_ver, 
		  _e_doc_type,  -- _data_wypelnienia,  
		  _e_Doc_Def_ID, _doc_ver, _kodPodatku
		FROM 
			ht_dekl_jpkv7 p, e_doc_def d
		WHERE 
			p.GUID = _GUID AND  p.e_Doc_Def_ID = d.owner_doc_Id -- _e_doc_id, _doc_ver, _form_ver, _e_doc_type, _e_Doc_Def_ID, _OkresOd, _OkresDo;
		;  
	
	SET _OdDnia = CONCAT(_rok,'-',_okres,'-01');
	-- SET _OdDnia = CONCAT(_rok,'-',_miesiac,'-01');
	SET _DoDnia = LAST_DAY(_OdDnia);
		

	SET @xml.IC = 1;
	SET _XMLnag = '<?xml version="1.0" encoding="UTF-8"?>\n';
  SET _XMLnag = CONCAT(_XMLnag,'<JPK xmlns="',_e_Doc_Def_ID,'"\n'
																	'xmlns:etd="http://crd.gov.pl/xml/schematy/dziedzinowe/mf/2020/03/11/eD/DefinicjeTypy/">\n');

	SET _KodUS = '';
	SELECT _varSTR_NOTNULL(kod_urzedu) INTO _KodUS FROM US
		WHERE  guid = (SELECT valueA FROM IReg WHERE idpath='INT\\SYSTEM\\MOTLAWA\\DaneFirmy' AND idkey='Urzad_skarbowy');

	IF IFNULL(_KodUS,'') = '' THEN
		SELECT _varSTR_NOTNULL(kod_urzedu) INTO _KodUS FROM urzedy_skarbowe
		WHERE  kod_urzedu = (SELECT valueA FROM IReg WHERE idpath='INT\\SYSTEM\\MOTLAWA\\DaneFirmy' AND idkey='Urzad_skarbowy');
	END IF;


	SET _XMLnag = CONCAT(_XMLnag,'<Naglowek>\n',' <KodFormularza kodSystemowy="','JPK_V7M',' (1)',
						'" wersjaSchemy="','1-2E','">','JPK_VAT','</KodFormularza>\n',
						' <WariantFormularza>', 1,'</WariantFormularza>\n',
						' <DataWytworzeniaJPK>',CURRENT_DATE,'T',CURRENT_TIME,'</DataWytworzeniaJPK>\n',
						' <NazwaSystemu>', _to_utf_xml('Pakiet Motława'), '</NazwaSystemu>\n'
						' <CelZlozenia poz="P_7">', _CelZlozenia,'</CelZlozenia>\n',
						' <KodUrzedu>', _KodUS, '</KodUrzedu>\n',
						' <Rok>', _rok, '</Rok>\n',
						' <Miesiac>', _okres, '</Miesiac>\n',
						'</Naglowek>\n'
						);

-- <Podmiot1>
		SET @tmp := 	@xml.IC * cSL;
		SET @P1.xml = '';
		SET @P1.xml = CONCAT(@P1.xml,  SPACE(@xml.IC*cSL), '<Podmiot1 rola="Podatnik">\n'); 
		
		CALL _IReg_Get('DaneFirmy', 'NIP', '0', 'STRING', _NIP, '(brak danych)'); 
		SET _NIP = REPLACE(_NIP, '-', '');				
		SET _RodzPodat = 0;
		CALL _IReg_Get('DaneFirmy', 'Rodzaj_platnika', '0', 'INTEGER', _RodzPodat, '0'); 
		-- CALL _IReg_Get('DeklaracjeVAT', 'JPK_email', '0', 'STRING',_email, '(brak danych)');
		SET _email = (SELECT email FROM ht_dekl_jpkv7 WHERE guid = _GUID);
		
		SET @xml.IC = @xml.IC + 1;
		SET @RodzPodat = _RodzPodat;		

		IF _RodzPodat = 0 THEN  -- osoba nie-fiz.
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<OsobaNiefizyczna>\n'); 
				
			SET @xml.IC = @xml.IC + 1;
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<NIP>');				
			SET @P1.xml = CONCAT(@P1.xml, IFNULL(_NIP, ''));  
			SET @P1.xml = CONCAT(@P1.xml, '</NIP>\n');
			
			CALL _IReg_Get('DaneFirmy', 'pelna_nazwa', '0', 'STRING', _PelnaNazwa, ''); 
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<PelnaNazwa>');				

			SET @P1.xml = CONCAT(@P1.xml, _to_utf_xml(_PelnaNazwa));    -- _PelnaNazwa
			-- SET @P1.xml = CONCAT(@P1.xml, _to_utf_xml(REPLACE(_PelnaNazwa, '&', '&#x26;')));    -- _PelnaNazwa, dla tej wersji problem kolacji
			-- SET @P1.xml = CONCAT(@P1.xml, REPLACE(_PelnaNazwa, '&', '&#x26;'));    -- _PelnaNazwa
			SET @P1.xml = CONCAT(@P1.xml, '</PelnaNazwa>\n');

			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<Email>');
			SET @P1.xml = CONCAT(@P1.xml, IFNULL(_email, ''));  
			SET @P1.xml = CONCAT(@P1.xml, '</Email>\n');

			SET @xml.IC = @xml.IC - 1;	
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '</OsobaNiefizyczna>\n'); 		
			
		END IF;
		
		IF _RodzPodat = 1 THEN  -- osoba fiz.
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<OsobaFizyczna>\n');   
		
			-- wstawiamy TIdentyfikatorOsobyFizycznej2 : NIP, imie, nazwisko, data urodz.
			SET @xml.IC = @xml.IC + 1;			
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<etd:NIP>');				
			SET @P1.xml = CONCAT(@P1.xml, _NIP);  
			SET @P1.xml = CONCAT(@P1.xml, '</etd:NIP>\n');
			
			CALL _IReg_Get('DaneFirmy', 'Platnik_imie', '0', 'STRING', _Platnik_imie, ''); 
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<etd:ImiePierwsze>');				
			SET @P1.xml = CONCAT(@P1.xml, _to_utf_xml(_Platnik_imie));    
			SET @P1.xml = CONCAT(@P1.xml, '</etd:ImiePierwsze>\n');
			
			CALL _IReg_Get('DaneFirmy', 'Platnik_nazwisko', '0', 'STRING', _Platnik_nazwisko, ''); 
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<etd:Nazwisko>');				
			SET @P1.xml = CONCAT(@P1.xml, _to_utf_xml(_Platnik_nazwisko));  
			SET @P1.xml = CONCAT(@P1.xml, '</etd:Nazwisko>\n');
			
			CALL _IReg_Get('DaneFirmy', 'Platnik_data_urodzenia', '0', 'DATE', _Platnik_data_urodzenia, ''); 
			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<etd:DataUrodzenia>');				
			SET @P1.xml = CONCAT(@P1.xml, _Platnik_data_urodzenia);  
			SET @P1.xml = CONCAT(@P1.xml, '</etd:DataUrodzenia>\n');

			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '<Email>');
			SET @P1.xml = CONCAT(@P1.xml, IFNULL(_email, ''));  
			SET @P1.xml = CONCAT(@P1.xml, '</Email>\n');
				
			SET @xml.IC = @xml.IC - 1;	

			SET @P1.xml = CONCAT(@P1.xml, SPACE(@xml.IC*cSL), '</OsobaFizyczna>\n');   
		END IF;
		
		SET _XMLnag = CONCAT(_XMLnag, @P1.xml, SPACE(@xml.IC*cSL), '</Podmiot1>\n');
--  </Podmiot1>

	IF _RodzajKorekty IN (1,3) THEN -- deklaracja, korekta calosci, korekta deklaracji
		SET _XMLnag = CONCAT(_XMLnag,'<Deklaracja>\n<Naglowek>\n', 
						'<KodFormularzaDekl kodSystemowy="VAT-7 (21)"',
						' kodPodatku="VAT" rodzajZobowiazania="Z" wersjaSchemy="1-2E">','VAT-7','</KodFormularzaDekl>\n',
						' <WariantFormularzaDekl>', 21,'</WariantFormularzaDekl>\n',
						'</Naglowek>\n'
						);
	END IF;
  -- dla korekty tylko ewidencji znika w ogole Deklaracja
	
	SET @_RodzajKorekty = _RodzajKorekty;
	SET @_XMLnag = _XMLnag;
	UPDATE t_jpk SET xmlnag = _XMLnag WHERE guid = _Param_D;

	END;
END IF; -- NAGLOWEK


	IF (_MODE = 'NAGLOWEK2') THEN -- tutaj sa dane naglowkowe rozne dla roznych zestawien

		-- SET @_XMLnag2 = 'naglowek2';
			UPDATE t_jpk SET xmlnag2 = 'naglowek2' WHERE guid = _Param_D;

	END IF; -- NAGLOWEK2


/*
CALL e_dekl_jpk('JPK_V7M','{ADC96649-8989-47AB-BD0C-F71DF9A9AFE1}','','' ,'', '','', '', '','RAPORT_', '', '','');
*/
IF _MODE IN ('JPK_V7M','JPK_V7K') THEN
-- dla v7m wychodzimy zawsze od guidu deklaracji
-- co zrobic dla wersji kwartalnej to nie wiem na razie - deklracji nie ma, wiec z programu trzeba podawac daty od do, jak wczesniej dla jpkvat3

	IF _Param_A IN ('RAPORT1') THEN
		CALL e_dekl_jpk('RAPORT1', _GUID, _rok, _miesiac, '','', _OdDnia,_DoDnia, '', '', _Param_B, '','');
		LEAVE SPROC;
	END IF;

	IF _Param_A IN ('RAPORT2') THEN
		CALL e_dekl_jpk('RAPORT2', _GUID, _rok, _miesiac, '','', _OdDnia,_DoDnia, '', '', _Param_B, '','');
		LEAVE SPROC;
	END IF;
	
	IF _Param_A = 'CALC' THEN
		CALL e_dekl_jpk('CALC', _GUID, _rok, _miesiac, '','', _OdDnia,_DoDnia, '', '', _Param_B, '','');
		LEAVE SPROC;
	END IF;
	
	IF _Param_A = 'CREATE_XML' THEN
		CALL e_dekl_jpk('CREATE_XML', _GUID, _rok, _miesiac, '','', _OdDnia,_DoDnia, '', '', _Param_B, '','');
		LEAVE SPROC;
	END IF;

	
	SELECT rok, miesiac, e_doc_def_id INTO _rok, _miesiac, _e_Doc_Def_ID FROM ht_dekl_jpkv7 WHERE guid = _GUID;
	SET _OdDnia = CONCAT(_rok,'-',_miesiac,'-01');
	SET _DoDnia = LAST_DAY(_OdDnia);

	/*
	IF _Param_A NOT IN ('RAPORT') THEN
		CALL e_dekl_jpk('NAGLOWEK', _GUID, _rok, _miesiac, '','', _OdDnia,_DoDnia, '', '', _Param_B, '',_MODE);
	END IF;
*/
	
	
	SELECT DATABASE() INTO @baza;
	
	SET @doc_id = SUBSTRING_INDEX(_e_Doc_Def_ID,'_',1);
	
	SET _S = 
		'SELECT guid FROM ht_dekl_vat7_def WHERE guid LIKE "%#GUID#%" INTO @vat7_guid';
	SET _S = REPLACE(_S, '#GUID#', SUBSTRING_INDEX(_e_Doc_Def_ID,'_',1));
	SET @_S = _S;
	CALL _ExecuteCmd(_S, @Error);
		
	SET @platnik = 0;
	IF @session.globals.Owner IN ('MARPOL') THEN
					SET @platnik = 1;
	END IF;

	-- dodac wykorztsywanie _db_globals.DOC_with_AdrLink dla wlaczenia nowej adresacji, o ile w ogole warto to robic

	SET @test1 = CONCAT(_OdDnia,_DoDnia,@baza, @vat7_guid, _rok, _miesiac);

	-- rozbijanie pol definicji rejestrow na skladowe
	SELECT 
		REPLACE(IFNULL(K_13,-10),';',','), REPLACE(IFNULL(K_15,-10),';',','), REPLACE(IFNULL(K_17,-10),';',','), REPLACE(IFNULL(K_19,-10),';',','), 
		REPLACE(IFNULL(K_40,-10),';',','), REPLACE(IFNULL(K_42,-10),';',','), REPLACE(IFNULL(K_68,-10),';',','),
		REPLACE(IFNULL(KorektaPodstawyOp_def,-10),';',',')
		INTO _K_13, _K_15, _K_17, _K_19, _K_40, _K_42, _K_68, _KPOd 
	FROM ht_dekl_vat7_def 
		WHERE GUID = @vat7_guid;
		
		SELECT SUBSTRING_INDEX(_K_13,',',1), RIGHT(SUBSTRING_INDEX(_K_13,',',2),4), RIGHT(SUBSTRING_INDEX(_K_13,',',3),4), RIGHT(SUBSTRING_INDEX(_K_13,',',4),4), RIGHT(SUBSTRING_INDEX(_K_13,',',5),4)
		INTO @K_13_1, @K_13_2, @K_13_3, @K_13_4, @K_13_5;
		
		SELECT SUBSTRING_INDEX(_K_15,',',1), RIGHT(SUBSTRING_INDEX(_K_15,',',2),4), RIGHT(SUBSTRING_INDEX(_K_15,',',3),4), RIGHT(SUBSTRING_INDEX(_K_15,',',4),4), RIGHT(SUBSTRING_INDEX(_K_15,',',5),4)
		INTO @K_15_1, @K_15_2, @K_15_3, @K_15_4, @K_15_5;
		
		SELECT SUBSTRING_INDEX(_K_17,',',1), RIGHT(SUBSTRING_INDEX(_K_17,',',2),4), RIGHT(SUBSTRING_INDEX(_K_17,',',3),4), RIGHT(SUBSTRING_INDEX(_K_17,',',4),4), RIGHT(SUBSTRING_INDEX(_K_17,',',5),4)
		INTO @K_17_1, @K_17_2, @K_17_3, @K_17_4, @K_17_5;
		
		SELECT SUBSTRING_INDEX(_K_19,',',1), RIGHT(SUBSTRING_INDEX(_K_19,',',2),4), RIGHT(SUBSTRING_INDEX(_K_19,',',3),4), RIGHT(SUBSTRING_INDEX(_K_19,',',4),4), RIGHT(SUBSTRING_INDEX(_K_19,',',5),4)
		INTO @K_19_1, @K_19_2, @K_19_3, @K_19_4, @K_19_5;
			
		SELECT SUBSTRING_INDEX(_K_40,',',1), RIGHT(SUBSTRING_INDEX(_K_40,',',2),4), RIGHT(SUBSTRING_INDEX(_K_40,',',3),4), RIGHT(SUBSTRING_INDEX(_K_40,',',4),4), RIGHT(SUBSTRING_INDEX(_K_40,',',5),4)
		INTO @K_40_1, @K_40_2, @K_40_3, @K_40_4, @K_40_5;
		
		SELECT SUBSTRING_INDEX(_K_42,',',1), RIGHT(SUBSTRING_INDEX(_K_42,',',2),4), RIGHT(SUBSTRING_INDEX(_K_42,',',3),4), RIGHT(SUBSTRING_INDEX(_K_42,',',4),4), RIGHT(SUBSTRING_INDEX(_K_42,',',5),4)
		INTO @K_42_1, @K_42_2, @K_42_3, @K_42_4, @K_42_5;
			
		SELECT SUBSTRING_INDEX(_K_68,',',1), RIGHT(SUBSTRING_INDEX(_K_68,',',2),4), RIGHT(SUBSTRING_INDEX(_K_68,',',3),4), RIGHT(SUBSTRING_INDEX(_K_68,',',4),4), RIGHT(SUBSTRING_INDEX(_K_68,',',5),4)
		INTO @K_68_1, @K_68_2, @K_68_3, @K_68_4, @K_68_5;
		
		SELECT SUBSTRING_INDEX(_KPOd,',',1), RIGHT(SUBSTRING_INDEX(_KPOd,',',2),4), RIGHT(SUBSTRING_INDEX(_KPOd,',',3),4), RIGHT(SUBSTRING_INDEX(_KPOd,',',4),4), RIGHT(SUBSTRING_INDEX(_KPOd,',',5),4)
		INTO @KPOd_1, @KPOd_2, @KPOd_3, @KPOd_4, @KPOd_5;
		
	
	DROP TABLE IF EXISTS t10_guid;
	CREATE TEMPORARY TABLE t10_guid (guid VARCHAR(38), lp INT(11),KEY guid (guid));

	DROP TABLE IF EXISTS t_vat_s;
	CREATE TEMPORARY TABLE t_vat_s (
	guid_dekl VARCHAR(38),
	guid VARCHAR(38),
	rv_id VARCHAR(38),
	rv_id_oper VARCHAR(38),
	LpSprzedazy INT(11),
	KodKrajuNadaniaTIN VARCHAR(2),
	NrKontrahenta VARCHAR(15),
	NazwaKontrahenta VARCHAR(150),
	DowodSprzedazy VARCHAR(50), -- sprawdzic dlugosc pola, numer dowodu sprzedazy
	DataWystawienia VARCHAR(10),
	DataSprzedazy VARCHAR(10),
	TypDokumentu VARCHAR(5), -- RO, WEW, FP
	jpkgtu VARCHAR(200),
	jpkflags VARCHAR(200),
	KorektaPodstawyOpodt VARCHAR(5),
	TerminPlatnosci VARCHAR(10),
	DataZaplaty VARCHAR(10),
	K_10 DOUBLE(16,2) DEFAULT 0,
	K_11 DOUBLE(16,2) DEFAULT 0,
	K_12 DOUBLE(16,2) DEFAULT 0,
	K_13 DOUBLE(16,2) DEFAULT 0,
	K_14 DOUBLE(16,2) DEFAULT 0,
	K_15 DOUBLE(16,2) DEFAULT 0,
	K_16 DOUBLE(16,2) DEFAULT 0,
	K_17 DOUBLE(16,2) DEFAULT 0,
	K_18 DOUBLE(16,2) DEFAULT 0,
	K_19 DOUBLE(16,2) DEFAULT 0,
	K_20 DOUBLE(16,2) DEFAULT 0,
	K_21 DOUBLE(16,2) DEFAULT 0,
	K_22 DOUBLE(16,2) DEFAULT 0,
	K_23 DOUBLE(16,2) DEFAULT 0,
	K_24 DOUBLE(16,2) DEFAULT 0,
	K_25 DOUBLE(16,2) DEFAULT 0,
	K_26 DOUBLE(16,2) DEFAULT 0,
	K_27 DOUBLE(16,2) DEFAULT 0,
	K_28 DOUBLE(16,2) DEFAULT 0,
	K_29 DOUBLE(16,2) DEFAULT 0,
	K_30 DOUBLE(16,2) DEFAULT 0,
	K_31 DOUBLE(16,2) DEFAULT 0,
	K_32 DOUBLE(16,2) DEFAULT 0,
	K_33 DOUBLE(16,2) DEFAULT 0,
	K_34 DOUBLE(16,2) DEFAULT 0,
	K_35 DOUBLE(16,2) DEFAULT 0,
	K_36 DOUBLE(16,2) DEFAULT 0,
	SprzedazVAT_Marza DOUBLE(16,2) DEFAULT 0,
	xml TEXT,
	KEY guid (guid)) DEFAULT CHARSET=utf8;

	DROP TABLE IF EXISTS t_vat_z;
	CREATE TEMPORARY TABLE t_vat_z (
	guid_dekl VARCHAR(38),
	guid VARCHAR(38),
	rv_id VARCHAR(38),
	rv_id_oper VARCHAR(38),
	LpZakupu INT(11),
	KodKrajuNadaniaTIN VARCHAR(2),
	NrDostawcy VARCHAR(15),
	NazwaDostawcy VARCHAR(150),
	DowodZakupu VARCHAR(50), -- sprawdzic dlugosc pola
	DataZakupu VARCHAR(10),
	DataWplywu VARCHAR(10),
	DokumentZakupu VARCHAR(10), -- ewentualnie jedna z trzech mozliwosci: MK, VAT_RR, WEW
	MPP VARCHAR(5),
	IMP VARCHAR(5),
	K_40 DOUBLE(16,2) DEFAULT 0,
	K_41 DOUBLE(16,2) DEFAULT 0,
	K_42 DOUBLE(16,2) DEFAULT 0,
	K_43 DOUBLE(16,2) DEFAULT 0,
	K_44 DOUBLE(16,2) DEFAULT 0,
	K_45 DOUBLE(16,2) DEFAULT 0,
	K_46 DOUBLE(16,2) DEFAULT 0,
	K_47 DOUBLE(16,2) DEFAULT 0,
	ZakupVAT_Marza DOUBLE(16,2) DEFAULT 0,
	xml TEXT,
	KEY guid (guid)) DEFAULT CHARSET=utf8;
	
	-- SET @a :=0;
					INSERT INTO t_vat_s
					SELECT _GUID, n.guid, rv.rv_id_JPK, n.rv_id, 
					-- @a:=@a+1 AS LpSprzedazy,
					0 AS LpSprzedazy,
					'' as KodKrajuNadaniaTIN,
					CASE WHEN @session.globals.Owner IN ('IZOHAN','JARZEBINSCY','POLAM','WEGA')  AND n.rodzaj NOT IN (0,2,4,83)
									AND n.kontrsymbol<> 'JEDNORAZOWI POZOSTALI' THEN (SELECT nip FROM kontrah WHERE guid = n.platnikGUID)
					WHEN @session.globals.Owner IN ('IZOHAN','JARZEBINSCY','POLAM') AND n.rodzaj IN (0,2,4,83) AND n.kontrnip = '' THEN 'brak'
					WHEN @session.globals.Owner IN ('TRANSBUD') AND DATABASE()<> "skweglazoosk" AND n.rodzaj NOT IN (0,2,4,83) AND  
						n.platnikguid<>'{4E576C7D-CC21-43B5-99D8-6FE1637FC12A}' 
					  THEN  (SELECT nip FROM kontrah WHERE guid = n.platnikGUID)
					WHEN    @session.globals.Owner IN ('TRANSBUD') AND DATABASE()= "skweglazoosk" AND n.rodzaj NOT IN (0,2,4,83,92) AND 
						((SELECT extraA FROM kontrah WHERE guid = n.platnikGUID) NOT IN ('SPRZEDAZ_RATALNA','KARTA_PLATNICZA'))
					 THEN 
					        (SELECT nip FROM kontrah WHERE guid = n.platnikGUID)	
					WHEN @session.globals.Owner IN ('MATELBUD') AND n.kontrsymbol = 'ZZG-Klient detaliczny' THEN 'brak'
					WHEN n.rodzaj IN (0,2) AND n.kontrnip = '' THEN 'brak'
					WHEN @platnik = 1 THEN (SELECT nip FROM kontrah WHERE guid = n.platnikGUID)
					ELSE IF(n.kontrnip = '','brak',n.kontrnip)
					END AS kontrnip,
					CASE WHEN @session.globals.Owner IN ('IZOHAN','JARZEBINSCY','POLAM','WEGA') AND n.rodzaj NOT IN (0,2,4,83)
									AND n.kontrsymbol<> 'JEDNORAZOWI POZOSTALI' THEN
									(SELECT REPLACE(REPLACE(REPLACE(nazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') FROM kontrah WHERE guid = n.platnikGUID)
									
					WHEN @session.globals.Owner IN ('TRANSBUD') AND DATABASE()<> "skweglazoosk" AND n.rodzaj NOT IN (0,2,4,83) AND  
					n.platnikguid<>'{4E576C7D-CC21-43B5-99D8-6FE1637FC12A}' 
					THEN  (SELECT REPLACE(REPLACE(REPLACE(nazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') FROM kontrah WHERE guid = n.platnikGUID)
					WHEN    @session.globals.Owner IN ('TRANSBUD') AND DATABASE()= "skweglazoosk" AND n.rodzaj NOT IN (0,2,4,83,92) AND 
						((SELECT extraA FROM kontrah WHERE guid = n.platnikGUID) NOT IN ('SPRZEDAZ_RATALNA','KARTA_PLATNICZA'))
					 THEN 
					        (SELECT REPLACE(REPLACE(REPLACE(nazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') FROM kontrah WHERE guid = n.platnikGUID)	
					WHEN @session.globals.Owner IN ('JARZEBINSCY','POLAM') AND n.rodzaj IN (0,2) THEN
									(SELECT REPLACE(REPLACE(REPLACE(nazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') FROM kontrah WHERE guid = n.kontrGUID)
					WHEN @platnik = 1 THEN (SELECT REPLACE(REPLACE(REPLACE(nazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') FROM kontrah WHERE guid = n.platnikGUID)
					ELSE REPLACE(REPLACE(REPLACE(kontrnazwa, CHAR(38), ' and '),'<',' '), '&', ' and ')
					END AS kontrnazwa,
					REPLACE(REPLACE(REPLACE(n.nrdokfin, CHAR(38), ' and '),'<',' '), '&', ' and ') AS nrdokfin,
					datawystaw, IF(datasprzed<>datawystaw,datasprzed,''),
					CASE WHEN n.jpktyp IN (0,100) THEN '' WHEN n.jpktyp=1 THEN 'RO' WHEN n.jpktyp=2 THEN 'WEW' WHEN n.jpktyp=3 THEN 'FP'
					END AS TypDokumentu,
					jpkgtu, jpkflags,
					IF(n.rv_id IN (@KPOd_1, @KPOd_2, @KPOd_3, @KPOd_4, @KPOd_5),'1', '') AS KorektaPodstawyOpodt,
					'' AS TerminPlatnosci, -- dla zgodnosci z nowa wersja
					'' AS DataZaplaty, -- jw
					SUM(IF(n.rv_id IN (IFNULL(d.C1r1, -10), IFNULL(d.C1r2, -10)) AND v.idvat IN (3),v.netto, 0)) AS K_10,          -- C.1
					SUM(IF(n.rv_id IN (IFNULL(d.C2r1, -10), IFNULL(d.C2r2, -10), IFNULL(d.C2r3, -10)), v.netto, 0)) AS K_11,   -- C.2
					SUM(IF(n.rv_id IN (IFNULL(d.C2Ar1, -10), IFNULL(d.C2Ar2, -10), IFNULL(d.C2Ar3, -10)), v.netto, 0)) AS K_12,                     -- C.2a
					SUM(IF(n.rv_id IN (IFNULL(d.C3r1, -10), IFNULL(d.C3r2, -10), IFNULL(d.C3r3, -10), IFNULL(d.C3r4, -10), @K_13_1, @K_13_2, @K_13_3, @K_13_4, @K_13_5) AND v.idvat IN (2), v.netto, 0)) AS K_13,          -- C.3
					SUM(IF(n.rv_id IN (IFNULL(d.C3Ar1, -10), IFNULL(d.C3Ar2, -10)) AND v.idvat IN (2), v.netto, 0)) AS K_14,          -- C.3a

					SUM(IF(n.rv_id IN (IFNULL(d.C4r1, -10), IFNULL(d.C4r2, -10), IFNULL(d.C4r3, -10), IFNULL(d.C4r4, -10), @K_15_1, @K_15_2, @K_15_3, @K_15_4, @K_15_5) AND v.idvat IN (8), v.netto, 0)) AS K_15,          -- C.4   -- do 2016r. bylo 3% lub 5%, od 2016: tylko 5%  (tabwartnetto9 = 5%)
					SUM(IF(n.rv_id IN (IFNULL(d.C4r1, -10), IFNULL(d.C4r2, -10), IFNULL(d.C4r3, -10), IFNULL(d.C4r4, -10), @K_15_1, @K_15_2, @K_15_3, @K_15_4, @K_15_5) AND v.idvat IN (8), v.vat, 0)) AS K_16,          -- C.4 VAT
					SUM(IF(n.rv_id IN (IFNULL(d.C5r1, -10), IFNULL(d.C5r2, -10), IFNULL(d.C5r3, -10), IFNULL(d.C5r4, -10), @K_17_1, @K_17_2, @K_17_3, @K_17_4, @K_17_5) AND v.idvat IN (1,16), v.netto, 0)) AS K_17,          -- C.5
					SUM(IF(n.rv_id IN (IFNULL(d.C5r1, -10), IFNULL(d.C5r2, -10), IFNULL(d.C5r3, -10), IFNULL(d.C5r4, -10), @K_17_1, @K_17_2, @K_17_3, @K_17_4, @K_17_5) AND v.idvat IN (1,16), v.vat, 0)) AS K_18,          -- C.5 VAT
					SUM(IF(n.rv_id IN (IFNULL(d.C6r1, -10), IFNULL(d.C6r2, -10), IFNULL(d.C6r3, -10), IFNULL(d.C6r4, -10), IFNULL(d.C6r5, -10), IFNULL(d.C6r6, -10), @K_19_1, @K_19_2, @K_19_3, @K_19_4, @K_19_5) AND v.idvat IN (0,15,20), v.netto, 0)) AS K_19,          -- C.6
					SUM(IF(n.rv_id IN (IFNULL(d.C6r1, -10), IFNULL(d.C6r2, -10), IFNULL(d.C6r3, -10), IFNULL(d.C6r4, -10), IFNULL(d.C6r5, -10), IFNULL(d.C6r6, -10), @K_19_1, @K_19_2, @K_19_3, @K_19_4, @K_19_5) AND v.idvat IN (0,15,20), v.vat, 0)) AS K_20,   -- C.6 vat
					SUM(IF(n.rv_id IN (IFNULL(d.C7r1, -10), IFNULL(d.C7r2, -10)), v.netto, 0)) AS K_21,                     -- C.7 (WDT)
					SUM(IF(n.rv_id IN (IFNULL(d.C8r1, -10), IFNULL(d.C8r2, -10)), v.netto, 0)) AS K_22,                     -- C.8 (Eksport towar?
					SUM(IF(n.rv_id IN (IFNULL(d.C9r1, -10), IFNULL(d.C9r2, -10)), v.netto, 0)) AS K_23,                     -- C.9 (WNT)
					SUM(IF(n.rv_id IN (IFNULL(d.C9r1, -10), IFNULL(d.C9r2, -10)), v.vat, 0)) AS K_24,                     -- C.9 VAT (WNT)
					SUM(IF(n.rv_id IN (IFNULL(d.C10r1, -10), IFNULL(d.C10r2, -10)), v.netto, 0)) AS K_25,                     -- C.10
					SUM(IF(n.rv_id IN (IFNULL(d.C10r1, -10), IFNULL(d.C10r2, -10)), v.vat, 0)) AS K_26,                     -- C.10 VAT
					SUM(IF(n.rv_id IN (IFNULL(d.C11r1, -10), IFNULL(d.C11r2, -10)), v.netto, 0)) AS K_27,                     -- C.11
					SUM(IF(n.rv_id IN (IFNULL(d.C11r1, -10), IFNULL(d.C11r2, -10)), v.vat, 0)) AS K_28,                     -- C.11 VAT
					SUM(IF(n.rv_id IN (IFNULL(d.C12r1, -10), IFNULL(d.C12r2, -10)), v.netto, 0)) AS K_29,                     -- C.12
					SUM(IF(n.rv_id IN (IFNULL(d.C12r1, -10), IFNULL(d.C12r2, -10)), v.vat, 0)) AS K_30,                     -- C.12 VAT
					-- SUM(IF(n.rv_id IN (IFNULL(d.C13r1, -10), IFNULL(d.C13r2, -10)) AND v.IdVat=19, v.netto, 0)) AS K_31,  -- C.13

					SUM(IF(n.rv_id IN (IFNULL(d.C14r1, -10), IFNULL(d.C14r2, -10)), v.netto, 0)) AS K_31,     --  do wyjasnienia              
					SUM(IF(n.rv_id IN (IFNULL(d.C14r1, -10), IFNULL(d.C14r2, -10)), v.vat, 0)) AS K_32,                     

					-- SUM(IF(n.rv_id IN (IFNULL(d.C15r1, -10), IFNULL(d.C15r2, -10), IFNULL(d.C15r3, -10)), v.netto, 0)) AS K_34,
					-- SUM(IF(n.rv_id IN (IFNULL(d.C15r1, -10), IFNULL(d.C15r2, -10), IFNULL(d.C15r3, -10)), v.vat, 0)) AS K_35,        
					SUM(IF(n.rv_id IN (IFNULL(d.C16r1, -10), IFNULL(d.C16r2, -10) ), v.vat, 0)) AS K_33,                
					SUM(IF(n.rv_id IN (IFNULL(d.C17r1, -10), IFNULL(d.C17r2, -10) ), v.vat, 0)) AS K_34,
					SUM(IF(n.rv_id IN (IFNULL(d.C18r1, -10), IFNULL(d.C18r2, -10) ), v.vat, 0)) AS K_35,                
					SUM(IF(n.rv_id IN (IFNULL(d.C19r1, -10), IFNULL(d.C19r2, -10) ), v.vat, 0)) AS K_36,
					SUM(IF(n.rv_id IN (IFNULL(d.C20r1, -10), IFNULL(d.C20r2, -10) ), v.brutto, 0)) AS SprzedazVAT_Marza,
					'' as xml
					FROM opng n, opng_vat v, operdoc op, v_rejestry_vat rv, ht_dekl_vat7_def d
					WHERE
									v.idnoper = n.guid AND rv.Idn = n.rv_id AND n.rodzaj = op.rodzaj
									AND n.deleted = 0
									AND d.GUID = @vat7_guid
									AND n.rv_id NOT IN (0) -- wylacza pozycje poza ewidencja vat zdefiniowane w rejestrach
									AND n.data_vat BETWEEN _OdDnia AND _DoDnia
									AND n.rv_id IN (IFNULL(d.C1r1, -10),IFNULL(d.C1r2, -10),IFNULL(d.C2r1, -10), IFNULL(d.C2r2, -10), IFNULL(d.C2r3, -10),
													IFNULL(d.C2Ar1, -10), IFNULL(d.C2Ar2, -10), IFNULL(d.C2Ar3, -10),
													IFNULL(d.C3r1, -10), IFNULL(d.C3r2, -10), IFNULL(d.C3r3, -10), IFNULL(d.C3r4, -10),
													IFNULL(d.C4r1, -10), IFNULL(d.C4r2, -10), IFNULL(d.C4r3, -10),IFNULL(d.C5r1, -10), IFNULL(d.C5r2, -10),
													IFNULL(d.C6r1, -10), IFNULL(d.C6r2, -10), IFNULL(d.C6r3, -10), IFNULL(d.C6r4, -10), IFNULL(d.C6r5, -10), IFNULL(d.C6r6, -10),
													IFNULL(d.C7r1, -10), IFNULL(d.C7r2, -10),IFNULL(d.C8r1, -10), IFNULL(d.C8r2, -10),
													IFNULL(d.C9r1, -10), IFNULL(d.C9r2, -10),IFNULL(d.C10r1, -10), IFNULL(d.C10r2, -10),IFNULL(d.C11r1, -10), IFNULL(d.C11r2, -10),
													IFNULL(d.C12r1, -10), IFNULL(d.C12r2, -10),IFNULL(d.C13r1, -10), IFNULL(d.C13r2, -10),
													IFNULL(d.C14r1, -10), IFNULL(d.C14r2, -10),IFNULL(d.C15r1, -10), IFNULL(d.C15r2, -10), IFNULL(d.C15r3, -10),
													IFNULL(d.C16r1, -10), IFNULL(d.C16r2, -10), IFNULL(d.C17r1, -10), IFNULL(d.C17r2, -10),
													IFNULL(d.C18r1, -10), IFNULL(d.C18r2, -10), IFNULL(d.C19r1, -10), IFNULL(d.C19r2, -10),
													IFNULL(d.C20r1, -10), IFNULL(d.C20r2, -10), -- definicja vat marza
													@K_13_1, @K_13_2, @K_13_3, @K_13_4, @K_13_5, @K_15_1, @K_15_2, @K_15_3, @K_15_4, @K_15_5, 
													@K_17_1, @K_17_2, @K_17_3, @K_17_4, @K_17_5, @K_19_1, @K_19_2, @K_19_3, @K_19_4, @K_19_5)
									AND v.IdVat NOT IN (IFNULL(d.Cstawka1,-1),IFNULL(d.Cstawka2,-1),IFNULL(d.Cstawka3,-1))
					GROUP BY n.guid
					ORDER BY n.datawplywu, n.idn;

			SET @a :=0;
			UPDATE t_vat_s SET LpSprzedazy = @a:=@a+1;
					
					
			IF @session.globals.Owner IN ('BYSEWO') THEN
				DROP TABLE IF EXISTS temp_nag_guid;
				CREATE TEMPORARY TABLE temp_nag_guid
								SELECT n.guid AS guid, LEFT(GROUP_CONCAT(DISTINCT tt.wyroznik),20) AS wyroznik
								FROM oppo p, opng n, TowrNg tt, t_vat_s t
								WHERE n.guid=p.idnoper AND tt.guid=p.idntowr AND t.guid=n.guid
								AND n.rv_id IN (2023,2054)
								GROUP BY n.guid;
				ALTER TABLE temp_nag_guid ADD INDEX guid(guid);

				DROP TABLE IF EXISTS temp_poz;
				CREATE TEMPORARY TABLE temp_poz
								SELECT n.guid, tt.wyroznik AS wyroznik, wartnet AS netto
								FROM oppo p, TowrNg tt, temp_nag_guid n
								WHERE n.guid = p.idnoper AND tt.guid = p.idntowr;
				ALTER TABLE temp_poz ADD INDEX guid(guid);
				
				DROP TABLE IF EXISTS temp_poz2;
				CREATE TEMPORARY TABLE temp_poz2
								SELECT guid, wyroznik, SUM(netto) AS netto
								FROM temp_poz
								GROUP BY guid,wyroznik;
				ALTER TABLE temp_poz2 ADD INDEX guid(guid);
				UPDATE t_vat_s SET K_21=0,K_11=0,K_12=0 WHERE rv_id IN (2023,2054) ;
				UPDATE t_vat_s t, temp_poz2 t2 SET
								K_21 = netto
				WHERE t.guid = t2.guid AND t.rv_id IN (2023,2054)AND t2.wyroznik IN  ('ST','T');

				UPDATE t_vat_s t, temp_poz2 t2 SET
								K_11 = netto,
								K_12 = netto
				WHERE t.guid = t2.guid AND t.rv_id  AND t2.wyroznik = 'U';
			END IF;
	
	-- LEAVE SPROC;



		-- sekcja zakupow
		SET _S = 
		"INSERT INTO t_vat_z
		SELECT
			'_GUID', n.guid, rv.rv_id_JPK, n.rv_id, 
			-- @a:=@a+1 AS LpZakupu,
			0 AS LpZakupu,
			IF(LEFT(n.kontrnip,1) NOT REGEXP '^[0-9]' AND n.kontrnip <> 'brak',LEFT(n.kontrnip,2),'') as KodKrajuNadaniaTIN,
			IF(n.kontrnip='','brak',n.kontrnip) AS nrdostawcy,
			REPLACE(REPLACE(REPLACE(kontrnazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') AS kontrnazwa,
			REPLACE(REPLACE(REPLACE(n.nrdokfin, CHAR(38), ' and '),'<',' '), '&', ' and ') AS nrdokfin,
			datawystaw, IF(@session.globals.Owner IN ('PROBIER','WEGA') ,n.data_vat ,n.datawplywu) AS  datawplywu,
			CASE WHEN n.jpktyp IN (0,100) THEN '' WHEN n.jpktyp IN (1,101) THEN 'MK' WHEN n.jpktyp IN (2,102) THEN 'VAT_RR' WHEN n.jpktyp IN (3,103) THEN 'WEW'
			END AS DokumentZakupu,
			IF(jpkflags like '%MPP%','1','0') AS MPP,
			IF(jpkflags like '%IMP%','1','0') AS IMP,
			SUM(IF(n.rv_id IN (IFNULL(d.D2_1r1, -10), IFNULL(d.D2_1r2, -10), IFNULL(d.D2_1r3, -10), @K_40_1, @K_40_2, @K_40_3, @K_40_4, @K_40_5), v.netto, 0)) AS K_40, -- D.2.40
			SUM(IF(n.rv_id IN (IFNULL(d.D2_1r1, -10), IFNULL(d.D2_1r2, -10), IFNULL(d.D2_1r3, -10), @K_40_1, @K_40_2, @K_40_3, @K_40_4, @K_40_5), v.vat,0))
											- IF(n.rv_id IN (IFNULL(d.D2_1r1, -10), IFNULL(d.D2_1r2, -10), IFNULL(d.D2_1r3, -10), @K_40_1, @K_40_2, @K_40_3, @K_40_4, @K_40_5),(n.vat_bez_odliczenia_paliwo + n.vat_bez_odliczenia_pojazd),0) AS K_41, -- D.2.41

			_Rnd2(SUM(IF(n.rv_id IN (IFNULL(d.D2_2r1, -10), IFNULL(d.D2_2r2, -10), IFNULL(d.D2_2r3, -10), IFNULL(d.D2_2r4, -10),
																																											IFNULL(d.D2_2r5, -10), IFNULL(d.D2_2r6, -10), IFNULL(d.D2_2r7, -10), IFNULL(d.D2_2r8, -10), IFNULL(d.D2_2r9, -10), @K_42_1, @K_42_2, @K_42_3, @K_42_4, @K_42_5),
															( v.netto - n.bezodliczen), 0))) AS K_42,   -- D.2.42

			_Rnd2(SUM(IF(n.rv_id IN (IFNULL(d.D2_2r1, -10), IFNULL(d.D2_2r2, -10), IFNULL(d.D2_2r3, -10), IFNULL(d.D2_2r4, -10),
																																							IFNULL(d.D2_2r5, -10), IFNULL(d.D2_2r6, -10), IFNULL(d.D2_2r7, -10),IFNULL(d.D2_2r8, -10), IFNULL(d.D2_2r9, -10), @K_42_1, @K_42_2, @K_42_3, @K_42_4, @K_42_5),v.vat, 0))
											- IF(n.rv_id IN (IFNULL(d.D2_2r1, -10), IFNULL(d.D2_2r2, -10), IFNULL(d.D2_2r3, -10), IFNULL(d.D2_2r4, -10),
																																							IFNULL(d.D2_2r5, -10), IFNULL(d.D2_2r6, -10), IFNULL(d.D2_2r7, -10),IFNULL(d.D2_2r8, -10), IFNULL(d.D2_2r9, -10),@K_42_1, @K_42_2, @K_42_3, @K_42_4, @K_42_5),
																																							(n.vat_bez_odliczenia_paliwo + n.vat_bez_odliczenia_pojazd),0)) AS K_43,  -- D.2.43
			-- sekcja D.3
			SUM(IF(n.rv_id IN (IFNULL(d.D3_1r1, -10), IFNULL(d.D3_1r2, -10) ), v.vat, 0)) AS K_44,                
			SUM(IF(n.rv_id IN (IFNULL(d.D3_2r1, -10), IFNULL(d.D3_2r2, -10) ), v.vat, 0)) AS K_45,        
			SUM(IF(n.rv_id IN (IFNULL(d.D3_3r1, -10), IFNULL(d.D3_3r2, -10) ), v.vat, 0)) AS K_46,                
			SUM(IF(n.rv_id IN (IFNULL(d.D3_4r1, -10), IFNULL(d.D3_4r2, -10) ), v.vat, 0)) AS K_47,
			SUM(IF(n.rv_id IN (IFNULL(d.D3_5r1, -10), IFNULL(d.D3_5r2, -10) ), v.brutto, 0)) AS ZakupVAT_Marza,
			'' as xml
			FROM opng n, opng_vat v, operdoc op, v_rejestry_vat rv, ht_dekl_vat7_def d
			WHERE
							v.idnoper = n.guid AND rv.Idn = n.rv_id AND n.rodzaj = op.rodzaj
							AND n.rodzaj not in (104) -- dodane dla hydromegi tylko oni cofają dokumenty do NN
							AND n.deleted = 0
							AND d.GUID = '@vat7_guid'
							AND n.rv_id NOT IN (0) -- wylacza pozycje poza ewidencja vat zdefiniowane w rejestrach
							AND n.data_vat BETWEEN '_OdDnia' AND '_DoDnia'
							AND n.rv_id IN (IFNULL(d.D2_1r1, -10), IFNULL(d.D2_1r2, -10), IFNULL(d.D2_1r3, -10),
											IFNULL(d.D2_2r1, -10), IFNULL(d.D2_2r2, -10), IFNULL(d.D2_2r3, -10), IFNULL(d.D2_2r4, -10),
											IFNULL(d.D2_2r5, -10), IFNULL(d.D2_2r6, -10), IFNULL(d.D2_2r7, -10), IFNULL(d.D2_2r8, -10), IFNULL(d.D2_2r9, -10),
											IFNULL(d.D3_1r1, -10), IFNULL(d.D3_1r2, -10), IFNULL(d.D3_2r1, -10), IFNULL(d.D3_2r2, -10),
											IFNULL(d.D3_3r1, -10), IFNULL(d.D3_3r2, -10), IFNULL(d.D3_4r1, -10), IFNULL(d.D3_4r2, -10),
											IFNULL(d.D3_5r1, -10), IFNULL(d.D3_5r2, -10),
											@K_40_1, @K_40_2, @K_40_3, @K_40_4, @K_40_5,
											@K_42_1, @K_42_2, @K_42_3, @K_42_4, @K_42_5)
							AND v.IdVat NOT IN (IFNULL(d.Dstawka1,-1),IFNULL(d.Dstawka2,-1),IFNULL(d.Dstawka3,-1),
											IFNULL(d.Dstawka4,-1),IFNULL(d.Dstawka5,-1),IFNULL(d.Dstawka6,-1),IFNULL(d.Dstawka7,-1))
			GROUP BY n.guid
			ORDER BY n.datawplywu, n.idn
			";
			SET _S =	REPLACE(_S, '_GUID', _GUID);
			SET _S =	REPLACE(_S, '@vat7_guid', @vat7_guid);
			SET _S =	REPLACE(_S, '_OdDnia', _OdDnia);
			SET _S =	REPLACE(_S, '_DoDnia', _DoDnia);
			SET _S =	REPLACE(_S, '#K_40#', _K_40);
		
			SET @_SQLText =	_S;
			CALL _ExecuteCmd(_S, @Error);
			
			
			SET @a :=0;
			UPDATE t_vat_z SET LpZakupu = @a:=@a+1;
			
			
		IF @session.globals.Owner IN ('ASEKURACJA') THEN
						-- SET _proc_struktura = 0.83; -- zrobic odczyt z klucza, wartosc na dany rok
						SET @rok = (SELECT YEAR(_OdDnia));
						CALL _IReg_Get('', 'ProcentVAT_struktura', @rok, 'FLOAT', _proc_struktura, '0.83');

		-- SET @_proc_struktura = _proc_struktura;

						DROP TABLE IF EXISTS temp_nag_guid;
						CREATE TEMPORARY TABLE temp_nag_guid
										SELECT n.guid AS guid, LEFT(GROUP_CONCAT(DISTINCT tt.wyroznik),20) AS wyroznik
										FROM oppo p, opng n, TowrNg tt, t_vat_z t
										WHERE n.guid=p.idnoper AND tt.guid=p.idntowr AND t.guid=n.guid
										AND tt.wyroznik IN ('ZC','ZN','ZO','SAM ZC','SAM ZN')
										GROUP BY n.guid;
						ALTER TABLE temp_nag_guid ADD INDEX guid(guid);

						DELETE t FROM t_vat_z t, temp_nag_guid tt WHERE tt.guid=t.guid AND tt.wyroznik IN ('ZN','SAM ZN');
						DELETE FROM temp_nag_guid WHERE wyroznik IN ('ZN','SAM ZN');


						DROP TABLE IF EXISTS temp_poz;
						CREATE TEMPORARY TABLE temp_poz
										SELECT n.guid, tt.wyroznik AS wyroznik, p.idnvat AS idvat, v.value AS valuevat,
										wartnet AS netto, wartvat AS vat
										-- wartnetwal as nettowal, (bruttowal-wartnetwal) as vatwal
										FROM oppo p, TowrNg tt, temp_nag_guid n, v_erp_vat v
										WHERE n.guid=p.idnoper AND tt.guid=p.idntowr AND v.id = p.idnvat
										AND tt.wyroznik NOT IN ('ZN','SAM ZN');
						
						ALTER TABLE temp_poz ADD INDEX guid(guid);

						UPDATE temp_poz SET netto = _Rnd2(netto * _proc_struktura) WHERE wyroznik IN ('ZC','SAM ZC');
						UPDATE temp_poz SET vat = _Rnd2(netto * valuevat) WHERE wyroznik IN ('ZC','SAM ZC');
						
						DROP TABLE IF EXISTS temp_poz2;
						CREATE TEMPORARY TABLE temp_poz2
										SELECT guid, SUM(netto) AS netto, SUM(vat) AS vat
										FROM temp_poz
										GROUP BY guid;
						ALTER TABLE temp_poz2 ADD INDEX guid(guid);
						
						UPDATE t_vat_z t, temp_poz2 t2 SET
										K_42 = netto,
										K_43 = vat
						WHERE t.guid = t2.guid AND t.rv_id <> 2030; -- docelowo zastapic `t.rv_id <> 2030` przez `t.rv_id in (d.D2_2r1 ... d.D2_2r7)`  (ht_dekl_vat7_def d)

						UPDATE t_vat_z t, temp_poz2 t2 SET
										K_40 = netto,
										K_41 = vat
						WHERE t.guid = t2.guid AND t.rv_id = 2030;  -- docelowo zastapic `t.rv_id = 2030` przez `t.rv_id in (d.D2_1r1, d.D2_1r2)`  (ht_dekl_vat7_def d)

		END IF; -- asekuracja, struktura

	-- sekcja inne (na razie pola P_68, P_69) ale to musi byc archiwizowane
		DROP TABLE IF EXISTS t_vat_inne;
		CREATE TEMPORARY TABLE t_vat_inne (
			guid_dekl VARCHAR(38),	
			guid_oper VARCHAR(38),
			rv_id VARCHAR(38),
			rv_id_oper VARCHAR(38),
			LpSprzedazy INT(11),
			NrKontrahenta VARCHAR(15),
			NazwaKontrahenta VARCHAR(150),
			DataWystawienia VARCHAR(10),
			DataSprzedazy VARCHAR(10),
			DataWplywu VARCHAR(10),
			K_68 DOUBLE(16,2) DEFAULT 0,
			K_69 DOUBLE(16,2) DEFAULT 0,
			`xml` TEXT,
		KEY guid_dekl (guid_dekl));
		
		INSERT INTO t_vat_inne
		SELECT
		_GUID, n.guid, rv.rv_id_JPK, n.rv_id, 
		0 AS LpSprzedazy,
		IF(n.kontrnip='','brak',n.kontrnip) AS NrKontrahenta,
		REPLACE(REPLACE(REPLACE(kontrnazwa, CHAR(38), ' and '),'<',' '), '&', ' and ') AS kontrnazwa,
		REPLACE(REPLACE(REPLACE(n.nrdokfin, CHAR(38), ' and '),'<',' '), '&', ' and ') AS nrdokfin,
		datawystaw, IF(@session.globals.Owner IN ('PROBIER','WEGA') ,n.data_vat ,n.datawplywu) AS datawplywu,
		SUM(IF(n.rv_id IN (@K_68_1, @K_68_2, @K_68_3, @K_68_4, @K_68_5), v.netto, 0)) AS K_68,
		SUM(IF(n.rv_id IN (@K_68_1, @K_68_2, @K_68_3, @K_68_4, @K_68_5), v.vat, 0)) AS K_69,
		'' as xml
		FROM opng n, opng_vat v, operdoc op, v_rejestry_vat rv, ht_dekl_vat7_def d
		WHERE
						v.idnoper = n.guid AND rv.Idn = n.rv_id AND n.rodzaj = op.rodzaj
						AND n.rodzaj not in (104) -- dodane dla hydromegi tylko oni cofają dokumenty do NN
						AND n.deleted = 0
						AND d.GUID = @vat7_guid
						AND n.rv_id NOT IN (0) -- wylacza pozycje poza ewidencja vat zdefiniowane w rejestrach
						AND n.data_vat BETWEEN _OdDnia AND _DoDnia
						-- AND n.rv_id IN (IFNULL(d.K_68, -10))
						AND n.rv_id IN (@K_68_1, @K_68_2, @K_68_3, @K_68_4, @K_68_5)
		GROUP BY n.guid
		ORDER BY n.datawplywu, n.idn;
		
		SET @a :=0;
		UPDATE t_vat_inne SET LpSprzedazy = @a:=@a+1;
		
		-- poprawki dla nipow, kodu nadania tin, wycinanie prefiksu
		UPDATE t_vat_s SET
		KodKrajuNadaniaTIN = IF(LEFT(NrKontrahenta,1) NOT REGEXP '^[0-9]' AND MID(NrKontrahenta,2,1) NOT REGEXP '^[0-9]' AND NrKontrahenta <> 'brak',LEFT(NrKontrahenta,2),'');
		UPDATE t_vat_s SET
		NrKontrahenta = IF(LEFT(NrKontrahenta,1) NOT REGEXP '^[0-9]' AND MID(NrKontrahenta,2,1) NOT REGEXP '^[0-9]' AND NrKontrahenta <> 'brak',RIGHT(NrKontrahenta,LENGTH(NrKontrahenta)-2),NrKontrahenta);
				
		UPDATE t_vat_z SET
		NrDostawcy = IF(LEFT(NrDostawcy,1) NOT REGEXP '^[0-9]' AND MID(NrDostawcy,2,1) NOT REGEXP '^[0-9]' AND NrDostawcy <> 'brak',RIGHT(NrDostawcy,LENGTH(NrDostawcy)-2),NrDostawcy);
		
		
		-- naliczenie vat marzy - tutaj uwaga: program nie obsluguje, w definicji deklracji, obslugi pol zakup i sprzedaz marza, czyli rejestry mozna przypisac tylko w delfinku - ale pola sa i procedura je uwzglednia
		UPDATE t_vat_s t, opng n
		SET t.K_19 = t.SprzedazVAT_Marza - n.koszt_nabycia,
		t.K_20 = IF(t.SprzedazVAT_Marza - n.koszt_nabycia > 0, _Rnd2(t.SprzedazVAT_Marza - n.koszt_nabycia), 0)
		WHERE n.guid = t.guid AND t.SprzedazVAT_Marza > 0;
		
		-- korekta podatku dla TT_WNT - to jest w pryszlosci do zrobienia - chyba powinno byc tak, ze vat jest zerowany
		/*
		UPDATE t_vat_s t
		SET t.K_20 = 0
		WHERE t.jpkflags like '%TT_WNT%';
		*/
		

-- naliczanie deklaracji na podstawie tabel tymczasowych
		IF _Param_A = 'NALICZ' THEN
			-- najpierw zapis tabel do bazy
			DELETE FROM ht_dekl_jpkv7_ews WHERE guid_dekl = _GUID;
			DELETE FROM ht_dekl_jpkv7_ewz WHERE guid_dekl = _GUID;
			DELETE FROM ht_dekl_jpkv7_ewinne WHERE guid_dekl = _GUID;
			
			INSERT INTO ht_dekl_jpkv7_ews SELECT t.*, CURRENT_TIMESTAMP() FROM t_vat_s t ORDER BY LpSprzedazy;
			INSERT INTO ht_dekl_jpkv7_ewz SELECT t.*, CURRENT_TIMESTAMP() FROM t_vat_z t ORDER BY LpZakupu;
			INSERT INTO ht_dekl_jpkv7_ewinne SELECT t.*, CURRENT_TIMESTAMP() FROM t_vat_inne t ORDER BY LpSprzedazy;
			
		
			DROP TABLE IF EXISTS t_jpkv7_s;
			CREATE TEMPORARY TABLE t_jpkv7_s (
				guid_dekl VARCHAR(38) DEFAULT '',
				P_10 DOUBLE(16,2) DEFAULT 0,
				P_11 DOUBLE(16,2) DEFAULT 0,
				P_12 DOUBLE(16,2) DEFAULT 0,
				P_13 DOUBLE(16,2) DEFAULT 0,
				P_14 DOUBLE(16,2) DEFAULT 0,
				P_15 DOUBLE(16,2) DEFAULT 0,
				P_16 DOUBLE(16,2) DEFAULT 0,
				P_17 DOUBLE(16,2) DEFAULT 0,
				P_18 DOUBLE(16,2) DEFAULT 0,
				P_19 DOUBLE(16,2) DEFAULT 0,
				P_20 DOUBLE(16,2) DEFAULT 0,
				P_21 DOUBLE(16,2) DEFAULT 0,
				P_22 DOUBLE(16,2) DEFAULT 0,
				P_23 DOUBLE(16,2) DEFAULT 0,
				P_24 DOUBLE(16,2) DEFAULT 0,
				P_25 DOUBLE(16,2) DEFAULT 0,
				P_26 DOUBLE(16,2) DEFAULT 0,
				P_27 DOUBLE(16,2) DEFAULT 0,
				P_28 DOUBLE(16,2) DEFAULT 0,
				P_29 DOUBLE(16,2) DEFAULT 0,
				P_30 DOUBLE(16,2) DEFAULT 0,
				P_31 DOUBLE(16,2) DEFAULT 0,
				P_32 DOUBLE(16,2) DEFAULT 0,
				P_33 DOUBLE(16,2) DEFAULT 0,
				P_34 DOUBLE(16,2) DEFAULT 0,
				P_35 DOUBLE(16,2) DEFAULT 0,
				P_36 DOUBLE(16,2) DEFAULT 0,
				KEY guid_dekl (guid_dekl));
			
			INSERT INTO t_jpkv7_s 
			SELECT _GUID, _Rnd(SUM(K_10),0), _Rnd(SUM(K_11),0), _Rnd(SUM(K_12),0), _Rnd(SUM(K_13),0), _Rnd(SUM(K_14),0), _Rnd(SUM(K_15),0), 
			_Rnd(SUM(K_16),0), _Rnd(SUM(K_17),0), _Rnd(SUM(K_18),0), _Rnd(SUM(K_19),0), _Rnd(SUM(K_20),0), _Rnd(SUM(K_21),0), _Rnd(SUM(K_22),0), 
			_Rnd(SUM(K_23),0), _Rnd(SUM(K_24),0), _Rnd(SUM(K_25),0), _Rnd(SUM(K_26),0), _Rnd(SUM(K_27),0), _Rnd(SUM(K_28),0), _Rnd(SUM(K_29),0), 
			_Rnd(SUM(K_30),0), _Rnd(SUM(K_31),0), _Rnd(SUM(K_32),0), _Rnd(SUM(K_33),0), _Rnd(SUM(K_34),0), _Rnd(SUM(K_35),0), _Rnd(SUM(K_36),0)
			FROM t_vat_s 
			WHERE ifnull(TypDokumentu,'') <> 'FP' 
			AND NOT (K_19 < 0 AND SprzedazVAT_Marza > 0);
			-- AND (jpkflags NOT LIKE '%TT_WNT%');
				
			UPDATE ht_dekl_jpkv7 v, t_jpkv7_s s SET
			 v.P_10 = IFNULL(s.P_10,0), v.P_11 = IFNULL(s.P_11,0), v.P_12 = IFNULL(s.P_12,0), v.P_13 = IFNULL(s.P_13,0), 
			 v.P_14 = IFNULL(s.P_14,0), v.P_15 = IFNULL(s.P_15,0),
			 v.P_16 = IFNULL(s.P_16,0), v.P_17 = IFNULL(s.P_17,0), v.P_18 = IFNULL(s.P_18,0), v.P_19 = IFNULL(s.P_19,0), 
			 v.P_20 = IFNULL(s.P_20,0), v.P_21 = IFNULL(s.P_21,0), 
			 v.P_22 = IFNULL(s.P_22,0), v.P_23 = IFNULL(s.P_23,0), v.P_24 = IFNULL(s.P_24,0), v.P_25 = IFNULL(s.P_25,0), 
			 v.P_26 = IFNULL(s.P_26,0), v.P_27 = IFNULL(s.P_27,0),
			 v.P_28 = IFNULL(s.P_28,0), v.P_29 = IFNULL(s.P_29,0), v.P_30 = IFNULL(s.P_30,0), v.P_31 = IFNULL(s.P_31,0),
			 v.P_32 = IFNULL(s.P_32,0), v.P_33 = IFNULL(s.P_33,0), v.P_34 = IFNULL(s.P_34,0), v.P_35 = IFNULL(s.P_35,0), v.P_36 = IFNULL(s.P_36,0)
			WHERE v.guid = s.guid_dekl;
			
			DROP TABLE IF EXISTS t_jpkv7_z;
			CREATE TEMPORARY TABLE t_jpkv7_z (
				guid_dekl VARCHAR(38) DEFAULT '',
				P_40 DOUBLE(16,2) DEFAULT 0,
				P_41 DOUBLE(16,2) DEFAULT 0,
				P_42 DOUBLE(16,2) DEFAULT 0,
				P_43 DOUBLE(16,2) DEFAULT 0,
				P_44 DOUBLE(16,2) DEFAULT 0,
				P_45 DOUBLE(16,2) DEFAULT 0,
				P_46 DOUBLE(16,2) DEFAULT 0,
				P_47 DOUBLE(16,2) DEFAULT 0,
				KEY guid_dekl (guid_dekl));
			
			INSERT INTO t_jpkv7_z 
			SELECT _GUID, _Rnd(SUM(K_40),0), _Rnd(SUM(K_41),0), _Rnd(SUM(K_42),0), _Rnd(SUM(K_43),0), _Rnd(SUM(K_44),0), 
			_Rnd(SUM(K_45),0), _Rnd(SUM(K_46),0), _Rnd(SUM(K_47),0)
			FROM t_vat_z;
				
			UPDATE ht_dekl_jpkv7 v, t_jpkv7_z z SET
			 v.P_40 = IFNULL(z.P_40,0), v.P_41 = IFNULL(z.P_41,0), v.P_42 = IFNULL(z.P_42,0), v.P_43 = IFNULL(z.P_43,0), 
			 v.P_44 = IFNULL(z.P_44,0), v.P_45 = IFNULL(z.P_45,0), v.P_46 = IFNULL(z.P_46,0), v.P_47 = IFNULL(z.P_47,0)
			WHERE v.guid = z.guid_dekl;
			
			UPDATE ht_dekl_jpkv7 SET 
				email = (SELECT ValueA FROM IReg WHERE idkey = 'JPK_email' LIMIT 1),
				tel_kontakt = (SELECT ValueA FROM IReg WHERE idkey = 'JPK_telefon' LIMIT 1)
			WHERE guid = _GUID;

			-- sekcja poboru kwoty z pola 62 z popredniej deklaracji do pola 39 lub pola 39 do 39 dla korekty
			SET @P_62 = NULL;
			
			SELECT CONCAT(ROK,LPAD(MIESIAC,2,'0')), CelZlozenia INTO @RM, @CEL FROM ht_dekl_jpkv7 WHERE GUID = _GUID;
			IF @CEL=1 THEN -- siegam do poprzedniego miesiaca dla deklaracji zwyklych, dla korekt ma byc ten sam miesiac
				SET @RM = IF(RIGHT(@RM,2)='01',CONCAT(LEFT(@RM,4)-1,'12'),@RM-1);
			END IF;
			
			SELECT IF(@CEL = 1, P_62, P_39) INTO @P_62 FROM ht_dekl_jpkv7
				WHERE CONCAT(ROK,LPAD(MIESIAC,2,'0')) = @RM AND DELETED=0 AND guid <> _GUID
				ORDER BY data_wypelnienia DESC LIMIT 1;
			
			IF @P_62 IS NULL THEN -- odwolanie do vat7
				SELECT P_62 INTO @P_62 FROM ht_dekl_vat7
					WHERE CONCAT(ROK,LPAD(MIESIAC,2,'0')) = @RM AND DELETED=0
					ORDER BY data_wypelnienia DESC LIMIT 1;
			END IF;
			
			SET @P_62 = IFNULL(@P_62,0);
			
			UPDATE ht_dekl_jpkv7 SET 
				P_39 = @P_62
			WHERE guid = _GUID;
			
			-- sekcja naliczania pol 68 i 69 na podstawie SW FV w rejestrach
			DROP TABLE IF EXISTS t_jpkv7_inne;
			CREATE TEMPORARY TABLE t_jpkv7_inne (
				guid_dekl VARCHAR(38) DEFAULT '',
				P_68 DOUBLE(16,2) DEFAULT 0,
				P_69 DOUBLE(16,2) DEFAULT 0,
				KEY guid_dekl (guid_dekl));
			
			INSERT INTO t_jpkv7_inne 
			SELECT _GUID, _Rnd(SUM(K_68),0), _Rnd(SUM(K_69),0)
			FROM t_vat_inne;
			
			UPDATE ht_dekl_jpkv7 v, t_jpkv7_inne z SET
			 v.P_68 = IFNULL(z.P_68,0), v.P_69 = IFNULL(z.P_69,0)
			WHERE v.guid = z.guid_dekl;
			
		END IF;


END IF; -- _MODE = JPK_V7M/JPK_V7K



IF _MODE = 'jpkv7m.xml.create.202010' THEN  -- JPK_V7K(1) 

	DROP TABLE IF EXISTS t_vat_v7;
	CREATE TEMPORARY TABLE t_vat_v7 LIKE ht_dekl_jpkv7;
	INSERT INTO t_vat_v7 SELECT * FROM ht_dekl_jpkv7 WHERE guid = _GUID;
	ALTER TABLE t_vat_v7 ADD COLUMN xml TEXT CHARACTER SET utf8;

	
	-- IF(K_40 = 0 ,'',CONCAT('<K_40>',K_40,'</K_40>\n')),
	
	UPDATE t_vat_v7 SET xml =
		CONCAT('<PozycjeSzczegolowe>\n',
		IF(P_10 = 0,'',CONCAT('<P_10>', P_10, '</P_10>\n')),
		IF(P_11 = 0,'',CONCAT('<P_11>', P_11, '</P_11>\n')),
		IF(P_12 = 0,'',CONCAT('<P_12>', P_12, '</P_12>\n')),
		IF(P_13 = 0,'',CONCAT('<P_13>', P_13, '</P_13>\n')),
		IF(P_14 = 0,'',CONCAT('<P_14>', P_14, '</P_14>\n')),
		IF(P_15 = 0,'',CONCAT('<P_15>', P_15, '</P_15>\n')),
		IF(P_15 = 0 and P_16 = 0,'',CONCAT('<P_16>', P_16, '</P_16>\n')),
		IF(P_17 = 0,'',CONCAT('<P_17>', P_17, '</P_17>\n')),
		IF(P_17 = 0 and P_18 = 0,'',CONCAT('<P_18>', P_18, '</P_18>\n')),
		IF(P_19 = 0,'',CONCAT('<P_19>', P_19, '</P_19>\n')),		
		IF(P_20 = 0,'',CONCAT('<P_20>', P_20, '</P_20>\n')),
		IF(P_21 = 0,'',CONCAT('<P_21>', P_21, '</P_21>\n')),
		IF(P_22 = 0,'',CONCAT('<P_22>', P_22, '</P_22>\n')),
		IF(P_23 = 0,'',CONCAT('<P_23>', P_23, '</P_23>\n')),
		IF(P_24 = 0,'',CONCAT('<P_24>', P_24, '</P_24>\n')),
		IF(P_25 = 0,'',CONCAT('<P_25>', P_25, '</P_25>\n')),
		IF(P_26 = 0,'',CONCAT('<P_26>', P_26, '</P_26>\n')),
		IF(P_27 = 0 AND P_28 = 0,'',CONCAT('<P_27>', P_27, '</P_27>\n')),
		IF(P_27 = 0 AND P_28 = 0,'',CONCAT('<P_28>', P_28, '</P_28>\n')),
		IF(P_29 = 0 AND P_30 = 0,'',CONCAT('<P_29>', P_29, '</P_29>\n')),		
		IF(P_29 = 0 AND P_30 = 0,'',CONCAT('<P_30>', P_30, '</P_30>\n')),
		IF(P_31 = 0,'',CONCAT('<P_31>', P_31, '</P_31>\n')),
		IF(P_32 = 0,'',CONCAT('<P_32>', P_32, '</P_32>\n')),
		IF(P_33 = 0,'',CONCAT('<P_33>', P_33, '</P_33>\n')),
		IF(P_34 = 0,'',CONCAT('<P_34>', P_34, '</P_34>\n')),
		IF(P_35 = 0,'',CONCAT('<P_35>', P_35, '</P_35>\n')),
		IF(P_36 = 0,'',CONCAT('<P_36>', P_36, '</P_36>\n')),
		IF(P_37 = 0,'',CONCAT('<P_37>', P_37, '</P_37>\n')),
		'<P_38>', P_38, '</P_38>\n',
		IF(P_39 = 0,'',CONCAT('<P_39>', P_39, '</P_39>\n')), -- kwota z poprzedniej deklaracji
		IF(P_40 = 0,'',CONCAT('<P_40>', P_40, '</P_40>\n')),
		IF(P_41 = 0,'',CONCAT('<P_41>', P_41, '</P_41>\n')),
		IF(P_42 = 0,'',CONCAT('<P_42>', P_42, '</P_42>\n')),
		IF(P_43 = 0,'',CONCAT('<P_43>', P_43, '</P_43>\n')),
		IF(P_44 = 0,'',CONCAT('<P_44>', P_44, '</P_44>\n')),
		IF(P_45 = 0,'',CONCAT('<P_45>', P_45, '</P_45>\n')),
		IF(P_46 = 0,'',CONCAT('<P_46>', P_46, '</P_46>\n')),
		IF(P_47 = 0,'',CONCAT('<P_47>', P_47, '</P_47>\n')),
		IF(P_48 = 0,'',CONCAT('<P_48>', P_48, '</P_48>\n')),
		IF(P_49 = 0,'',CONCAT('<P_49>', P_49, '</P_49>\n')),
		IF(P_50 = 0,'',CONCAT('<P_50>', P_50, '</P_50>\n')),
		'<P_51>', P_51, '</P_51>\n',
		IF(P_52 = 0,'',CONCAT('<P_52>', P_52, '</P_52>\n')),
		IF(P_53 = 0,'',CONCAT('<P_53>', P_53, '</P_53>\n')),
		IF(P_54 = 0 AND P_59 = 0,'',CONCAT('<P_54>', P_54, '</P_54>\n')),
		IF(P_55 = 0,'',CONCAT('<P_55>', P_55, '</P_55>\n')),
		IF(P_56 = 0,'',CONCAT('<P_56>', P_56, '</P_56>\n')),
		IF(P_57 = 0,'',CONCAT('<P_57>', P_57, '</P_57>\n')),
		IF(P_58 = 0,'',CONCAT('<P_58>', P_58, '</P_58>\n')),
		IF(P_59 = 0,'',CONCAT('<P_59>', P_59, '</P_59>\n')),
		IF(P_60 = 0,'',CONCAT('<P_60>', P_60, '</P_60>\n')),
		IF(P_59 = 0,'',CONCAT('<P_61>', IFNULL(_varSTR(_to_utf_xml(P_61)),''), '</P_61>\n')),
		IF(P_62 = 0,'',CONCAT('<P_62>', P_62, '</P_62>\n')),
		IF(P_63 = 0,'',CONCAT('<P_63>', P_63, '</P_63>\n')),
		IF(P_64 = 0,'',CONCAT('<P_64>', P_64, '</P_64>\n')),
		IF(P_65 = 0,'',CONCAT('<P_65>', P_65, '</P_65>\n')),
		IF(P_66 = 0,'',CONCAT('<P_66>', P_66, '</P_66>\n')),
		IF(P_67 = 0,'',CONCAT('<P_67>', P_67, '</P_67>\n')),
		IF(P_68 = 0,'',CONCAT('<P_68>', P_68, '</P_68>\n')),
		IF(P_69 = 0,'',CONCAT('<P_69>', P_69, '</P_69>\n')),
		IF(IFNULL(przyczyna_ORD_ZU,'') = '','',CONCAT('<P_ORDZU>', _to_utf_xml(przyczyna_ORD_ZU), '</P_ORDZU>\n')),
		-- IF(IFNULL(przyczyna_ORD_ZU,'') = '','',CONCAT('<P_ORDZU>', przyczyna_ORD_ZU, '</P_ORDZU>\n')),
		'</PozycjeSzczegolowe>\n',
		'<Pouczenia>1</Pouczenia>\n'
		);
	
	-- brakuje deklaracji zerowej

		
	UPDATE t_jpk SET xmlv7 = (SELECT CONCAT(GROUP_CONCAT(xml SEPARATOR ''),'</Deklaracja>') FROM t_vat_v7);
	
END IF;
     



IF (_MODE = 'CREATE_XML') THEN
	CALL e_dekl_jpk('NAGLOWEK', _GUID, _rok, _miesiac, '','', _OdDnia,_DoDnia, '', '', _Param_B, '',_MODE);

	DROP TABLE IF EXISTS t_vat_s;
	CREATE TEMPORARY TABLE t_vat_s LIKE ht_dekl_jpkv7_ews;
	INSERT INTO t_vat_s SELECT * FROM ht_dekl_jpkv7_ews WHERE guid_dekl = _GUID ORDER BY LpSprzedazy;
	
	DROP TABLE IF EXISTS t_vat_z;
	CREATE TEMPORARY TABLE t_vat_z LIKE ht_dekl_jpkv7_ewz;
	INSERT INTO t_vat_z SELECT * FROM ht_dekl_jpkv7_ewz WHERE guid_dekl = _GUID ORDER BY LpZakupu;
	
	UPDATE t_vat_s SET xml =
		CONCAT('<SprzedazWiersz>\n',
	'<LpSprzedazy>',LpSprzedazy,'</LpSprzedazy>\n',
	IF(KodKrajuNadaniaTIN = '','',CONCAT('<KodKrajuNadaniaTIN>',KodKrajuNadaniaTIN,'</KodKrajuNadaniaTIN>\n')),
	'<NrKontrahenta>',IF(NrKontrahenta='','brak',NrKontrahenta),'</NrKontrahenta>\n',
	'<NazwaKontrahenta>',_to_utf_xml(NazwaKontrahenta),'</NazwaKontrahenta>\n',
	-- '<NazwaKontrahenta>',CONVERT(NazwaKontrahenta USING utf8),'</NazwaKontrahenta>\n',
	'<DowodSprzedazy>',_to_utf_xml(DowodSprzedazy),'</DowodSprzedazy>\n',
	'<DataWystawienia>',datawystawienia,'</DataWystawienia>\n',
	IF(DataSprzedazy = '','',CONCAT('<DataSprzedazy>',DataSprzedazy,'</DataSprzedazy>\n')),
	IF(TypDokumentu = '','',CONCAT('<TypDokumentu>',TypDokumentu,'</TypDokumentu>\n')),
	IF(jpkgtu like '%GTU01%','<GTU_01>1</GTU_01>\n',''),
	IF(jpkgtu like '%GTU02%','<GTU_02>1</GTU_02>\n',''),
	IF(jpkgtu like '%GTU03%','<GTU_03>1</GTU_03>\n',''),
	IF(jpkgtu like '%GTU04%','<GTU_04>1</GTU_04>\n',''),
	IF(jpkgtu like '%GTU05%','<GTU_05>1</GTU_05>\n',''),
	IF(jpkgtu like '%GTU06%','<GTU_06>1</GTU_06>\n',''),
	IF(jpkgtu like '%GTU07%','<GTU_07>1</GTU_07>\n',''),
	IF(jpkgtu like '%GTU08%','<GTU_08>1</GTU_08>\n',''),
	IF(jpkgtu like '%GTU09%','<GTU_09>1</GTU_09>\n',''),
	IF(jpkgtu like '%GTU10%','<GTU_10>1</GTU_10>\n',''),
	IF(jpkgtu like '%GTU11%','<GTU_11>1</GTU_11>\n',''),
	IF(jpkgtu like '%GTU12%','<GTU_12>1</GTU_12>\n',''),
	IF(jpkgtu like '%GTU13%','<GTU_13>1</GTU_13>\n',''),
	
	IF(jpkflags like '%SW%','<SW>1</SW>\n',''),
	IF(jpkflags like '%EE%','<EE>1</EE>\n',''),
	IF(jpkflags like '%TP%','<TP>1</TP>\n',''),
	IF(jpkflags like '%TT_WNT%','<TT_WNT>1</TT_WNT>\n',''),
	IF(jpkflags like '%TT_D%','<TT_D>1</TT_D>\n',''),
	IF(jpkflags like '%MR_T%','<MR_T>1</MR_T>\n',''),
	IF(jpkflags like '%MR_UZ%','<MR_UZ>1</MR_UZ>\n',''),
	IF(jpkflags like '%I_42%','<I_42>1</I_42>\n',''),
	IF(jpkflags like '%I_63%','<I_63>1</I_63>\n',''),
	IF(jpkflags like '%B_SPV;%' OR jpkflags like '%B_SPV','<B_SPV>1</B_SPV>\n',''),
	IF(jpkflags like '%B_SPV_DOSTAWA%','<B_SPV_DOSTAWA>1</B_SPV_DOSTAWA>\n',''),
	IF(jpkflags like '%B_MPV_PROWIZJA%','<B_MPV_PROWIZJA>1</B_MPV_PROWIZJA>\n',''),
	IF(jpkflags like '%MPP%','<MPP>1</MPP>\n',''),
	
	IF(KorektaPodstawyOpodt = '','',CONCAT('<KorektaPodstawyOpodt>',KorektaPodstawyOpodt,'</KorektaPodstawyOpodt>\n')),
	IF(K_10=0,'',CONCAT('<K_10>',K_10,'</K_10>\n')),
	IF(K_11=0,'',CONCAT('<K_11>',K_11,'</K_11>\n')),
	IF(K_12=0,'',CONCAT('<K_12>',K_12,'</K_12>\n')),
	IF(K_13=0,'',CONCAT('<K_13>',K_13,'</K_13>\n')),
	IF(K_14=0,'',CONCAT('<K_14>',K_14,'</K_14>\n')),
	IF(K_15=0 AND K_16=0,'',CONCAT('<K_15>',K_15,'</K_15>\n')),
	IF(K_15=0 AND K_16=0,'',CONCAT('<K_16>',K_16,'</K_16>\n')),
	IF(K_17=0 AND K_18=0,'',CONCAT('<K_17>',K_17,'</K_17>\n')),
	IF(K_17=0 AND K_18=0,'',CONCAT('<K_18>',K_18,'</K_18>\n')),
	IF(K_19=0 AND K_20=0,'',CONCAT('<K_19>',K_19,'</K_19>\n')),
	IF(K_19=0 AND K_20=0,'',CONCAT('<K_20>',K_20,'</K_20>\n')),
	IF(K_21=0,'',CONCAT('<K_21>',K_21,'</K_21>\n')),
	IF(K_22=0,'',CONCAT('<K_22>',K_22,'</K_22>\n')),
	IF(K_23=0 AND K_24=0,'',CONCAT('<K_23>',K_23,'</K_23>\n')),
	IF(K_23=0 AND K_24=0,'',CONCAT('<K_24>',K_24,'</K_24>\n')),
	IF(K_25=0 AND K_26=0,'',CONCAT('<K_25>',K_25,'</K_25>\n')),
	IF(K_25=0 AND K_26=0,'',CONCAT('<K_26>',K_26,'</K_26>\n')),
	IF(K_27=0 AND K_28=0,'',CONCAT('<K_27>',K_27,'</K_27>\n')),
	IF(K_27=0 AND K_28=0,'',CONCAT('<K_28>',K_28,'</K_28>\n')),
	IF(K_29=0 AND K_30=0,'',CONCAT('<K_29>',K_29,'</K_29>\n')),
	IF(K_29=0 AND K_30=0,'',CONCAT('<K_30>',K_30,'</K_30>\n')),
	IF(K_31=0,'',CONCAT('<K_31>',K_31,'</K_31>\n')),
	IF(K_32=0 AND K_33=0,'',CONCAT('<K_32>',K_32,'</K_32>\n')),
	IF(K_32=0 AND K_33=0,'',CONCAT('<K_33>',K_33,'</K_33>\n')),
	IF(K_34=0 AND K_35=0,'',CONCAT('<K_34>',K_34,'</K_34>\n')),
	IF(K_34=0 AND K_35=0,'',CONCAT('<K_35>',K_35,'</K_35>\n')),
	IF(K_36=0,'',CONCAT('<K_36>',K_36,'</K_36>\n')),
	IF(SprzedazVAT_Marza = 0,'',CONCAT('<SprzedazVAT_Marza>',SprzedazVAT_Marza,'</SprzedazVAT_Marza>\n')),
	'</SprzedazWiersz>\n')
	;
	
	UPDATE t_jpk SET xmlpoz1 = (SELECT GROUP_CONCAT(xml SEPARATOR '') FROM t_vat_s);
			
	SELECT COUNT(*) INTO _liczba_wierszy FROM t_vat_s;
	SELECT IFNULL(SUM(_Rnd2(K_16 + K_18 + K_20 + K_24 + K_26 + K_28 + K_30 + K_32 + K_33 + K_34 - K_35 - K_36)),0) 
		INTO _podatek_s
		FROM t_vat_s WHERE ifnull(TypDokumentu,'') <> 'FP';

	SET _XMLpoz = '';
	SET _XMLpoz = CONCAT(SPACE(@xml.IC*cSL), '<SprzedazCtrl>\n');
	SET _XMLpoz = CONCAT(_XMLpoz, SPACE(@xml.IC*cSL), '<LiczbaWierszySprzedazy>',_liczba_wierszy,'</LiczbaWierszySprzedazy>\n');
	SET _XMLpoz = CONCAT(_XMLpoz, SPACE(@xml.IC*cSL), '<PodatekNalezny>',_podatek_s,'</PodatekNalezny>\n');
	SET _XMLpoz = CONCAT(_XMLpoz, SPACE(@xml.IC*cSL), '</SprzedazCtrl>\n');

	UPDATE t_jpk SET xmlpoz2 = CONCAT(xmlpoz2,IFNULL(_XMLpoz,'brak1'));

		UPDATE t_vat_z SET xml =
			CONCAT('<ZakupWiersz>\n',
			'<LpZakupu>',LpZakupu,'</LpZakupu>\n',
			IF(KodKrajuNadaniaTIN = '','',CONCAT('<KodKrajuNadaniaTIN>',KodKrajuNadaniaTIN,'</KodKrajuNadaniaTIN>\n')),
			'<NrDostawcy>',IF(NrDostawcy='','brak',NrDostawcy),'</NrDostawcy>\n',
			'<NazwaDostawcy>',_to_utf_xml(NazwaDostawcy),'</NazwaDostawcy>\n',
			'<DowodZakupu>',_to_utf_xml(IFNULL(DowodZakupu,'')),'</DowodZakupu>\n',
			'<DataZakupu>',DataZakupu,'</DataZakupu>\n',
			'<DataWplywu>',DataWplywu,'</DataWplywu>\n',
			IF(DokumentZakupu = '' ,'',CONCAT('<DokumentZakupu>',DokumentZakupu,'</DokumentZakupu>\n')),
			IF(MPP = '0' ,'',CONCAT('<MPP>',MPP,'</MPP>\n')),
			IF(IMP = '0' ,'',CONCAT('<IMP>',IMP,'</IMP>\n')),
			IF(K_40 = 0 AND K_41 = 0,'',CONCAT('<K_40>',K_40,'</K_40>\n')),
			IF(K_40 = 0 AND K_41 = 0,'',CONCAT('<K_41>',K_41,'</K_41>\n')),

			IF(K_42 = 0 AND K_43 = 0,'',CONCAT('<K_42>',K_42,'</K_42>\n')),
			IF(K_42 = 0 AND K_43 = 0,'',CONCAT('<K_43>',K_43,'</K_43>\n')), -- to sekwencja, tak ma byc

			IF(K_44 = 0 ,'',CONCAT('<K_44>',K_44,'</K_44>\n')),
			IF(K_45 = 0 ,'',CONCAT('<K_45>',K_45,'</K_45>\n')),

			IF(K_46 = 0 ,'',CONCAT('<K_46>',K_46,'</K_46>\n')),
			IF(K_47 = 0 ,'',CONCAT('<K_47>',K_47,'</K_47>\n')),
			IF(ZakupVAT_Marza = 0,'',CONCAT('<ZakupVAT_Marza>',ZakupVAT_Marza,'</ZakupVAT_Marza>\n')),
			'</ZakupWiersz>\n')
			;
		
		UPDATE t_jpk SET xmlpoz3 = (SELECT GROUP_CONCAT(xml SEPARATOR '') FROM t_vat_z);
		
		SELECT COUNT(*) INTO _liczba_wierszy FROM t_vat_z;
		SELECT IFNULL(SUM(_Rnd2(K_41 + K_43 + K_44 + K_45 + K_46 + K_47)),0) INTO _podatek_z FROM t_vat_z;

		SET _XMLpoz = '';

		SET _XMLpoz = CONCAT(SPACE(@xml.IC*cSL), '<ZakupCtrl>\n');
		SET _XMLpoz = CONCAT(_XMLpoz, SPACE(@xml.IC*cSL), '<LiczbaWierszyZakupow>',_liczba_wierszy,'</LiczbaWierszyZakupow>\n');
		SET _XMLpoz = CONCAT(_XMLpoz, SPACE(@xml.IC*cSL), '<PodatekNaliczony>',_podatek_z,'</PodatekNaliczony>\n');
		SET _XMLpoz = CONCAT(_XMLpoz, SPACE(@xml.IC*cSL), '</ZakupCtrl>\n');

		UPDATE t_jpk SET xmlpoz4 = CONCAT(xmlpoz4,IFNULL(_XMLpoz,'brak4'));

		
-- czesc dla deklaracji               
		CALL e_dekl_jpk('jpkv7m.xml.create.202010', _GUID, '', '', '', '', _OdDnia,_DoDnia, '', '', _Param_B, '',_MODE);

		IF @_RodzajKorekty = 1 THEN  -- deklaracja albo korekta pełna
			SET _XML := (SELECT CONCAT(xmlnag,xmlv7,'\n<Ewidencja>\n',IFNULL(xmlpoz1,''),xmlpoz2,IFNULL(xmlpoz3,''),xmlpoz4,'</Ewidencja>\n</JPK>') FROM t_jpk);
		END IF;
		
		IF @_RodzajKorekty = 2 THEN  -- korekta ewidencji
			SET _XML := (SELECT CONCAT(xmlnag,'<Ewidencja>\n',IFNULL(xmlpoz1,''),xmlpoz2,IFNULL(xmlpoz3,''),xmlpoz4,'</Ewidencja>\n</JPK>') FROM t_jpk);
		END IF;

		IF @_RodzajKorekty = 3 THEN  -- korekta deklaracji
			SET _XML := (SELECT CONCAT(xmlnag,xmlv7,'\n</JPK>') FROM t_jpk);
		END IF;
		
		
		SET @_XML := _XML;
		SET @eVAT7.Content = _XML;
		SET @_eDeklContent = _XML;
		SELECT _XML;
	

END IF; -- _MODE = CREATE_XML


IF (_MODE = 'CALC') THEN
	UPDATE ht_dekl_jpkv7 SET
		P_37 = P_10+P_11+P_13+P_15+P_17+P_19+P_21+P_22+P_23+P_25+P_27+P_29+P_31,
		P_38 = P_16+P_18+P_20+P_24+P_26+P_28+P_30+P_32+P_33+P_34 - P_35 - P_36,
		P_48 = P_39 + P_41 + P_43 + P_44 + P_45 + P_46 + P_47
	WHERE guid = _GUID;

	UPDATE ht_dekl_jpkv7 SET
		P_59 = IF(P_60 > 0,1,0),
		P_53 = IF(P_48-P_38 >= 0,P_48 - P_38 + P_52,0),
		P_51 = IF(P_38-P_48 >= 0,P_38 - P_48 - P_49 - P_50,0)
	WHERE guid = _GUID;

	UPDATE ht_dekl_jpkv7 SET
		-- P_62 = P_53 - P_54 - P_60
		P_62 = P_53 - P_54
	WHERE guid = _GUID;
	
END IF; -- _MODE = CALC

IF (_MODE = 'PRINT') THEN
	SELECT * FROM  ht_dekl_jpkv7 
	WHERE guid = _GUID;

END IF; 

IF (_MODE = 'PRINT2') THEN

	CALL _IReg_Get('DaneFirmy', 'NIP', '0', 'STRING', @NIP_print, '(brak danych)'); 
	CALL _IReg_Get('DaneFirmy', 'Rodzaj_platnika', '0', 'INTEGER', @RodzPodat_print, '0'); 

	SET @KodUS_print = '';
	SELECT _varSTR_NOTNULL(kod_urzedu) INTO @KodUS_print FROM US
		WHERE  guid = (SELECT valueA FROM IReg WHERE idpath='INT\\SYSTEM\\MOTLAWA\\DaneFirmy' AND idkey='Urzad_skarbowy');

	IF IFNULL(@KodUS_print,'') = '' THEN
		SELECT _varSTR_NOTNULL(kod_urzedu) INTO @KodUS_print FROM urzedy_skarbowe
		WHERE  kod_urzedu = (SELECT valueA FROM IReg WHERE idpath='INT\\SYSTEM\\MOTLAWA\\DaneFirmy' AND idkey='Urzad_skarbowy');
	END IF;

SELECT _varSTR_NOTNULL(Nazwa) INTO @KodUS_print FROM urzedy_skarbowe WHERE kod_urzedu=@KodUS_print;

CALL _IReg_Get('DaneFirmy', 'pelna_nazwa', '0', 'STRING', @PelnaNazwa_print, '');
CALL _IReg_Get('DaneFirmy', 'Platnik_imie', '0', 'STRING', @Platnik_imie_print, ''); 		
CALL _IReg_Get('DaneFirmy', 'Platnik_nazwisko', '0', 'STRING', @Platnik_nazwisko_print, ''); 
CALL _IReg_Get('DaneFirmy', 'Platnik_data_urodzenia', '0', 'DATE', @Platnik_data_print, ''); 

SELECT @NIP_print AS NIP, @RodzPodat_print AS rodzaj,@KodUS_print AS US,
IF(@RodzPodat_print=0,@PelnaNazwa_print,CONCAT(@Platnik_imie_print ,' ',@Platnik_nazwisko_print,' data ur. ',@Platnik_data_print))  AS nazwa,
@Platnik_imie_print AS imie,@Platnik_nazwisko_print AS nazwisko,@Platnik_data_print AS dataur;
	
END IF;	
-- NA PRZYSZLOSC ZEBY GP NIE MUSIAL GRZEBAC
 
IF (_MODE = 'PRINT3') THEN
	SELECT * FROM  ht_dekl_jpkv7 
	WHERE guid = _GUID;

END IF;

IF (_MODE = 'RAPORT1') THEN
	DROP TABLE IF EXISTS t_vat_s;
	CREATE TEMPORARY TABLE t_vat_s
	SELECT * FROM ht_dekl_jpkv7_ews WHERE guid_dekl = _GUID;
END IF; -- _MODE = RAPORT1

IF (_MODE = 'RAPORT2') THEN
	DROP TABLE IF EXISTS t_vat_z;
	CREATE TEMPORARY TABLE t_vat_z
	SELECT * FROM ht_dekl_jpkv7_ewz WHERE guid_dekl = _GUID;
END IF; -- _MODE = RAPORT2



/*

SET @session.globals.Owner = 'CRIST';
CALL e_dekl_jpk('JPK_V7M','{F22B7C51-6187-4C2F-89DE-75EAC17AFD02}','','' ,'', '','', '', '','RAPORT_', '', '','');
CALL e_dekl_jpk('JPK_V7M','{BD198020-8368-4132-B318-1FDFD2EEC534}','','' ,'', '','', '', '','CREATE_XML', '', '','');
SELECT * FROM t_jpk;

CALL e_dekl_jpk('JPK_V7M', '', '','', '', '', '_OdDnia','_DoDnia', '', '', '', '','_MODE');

SET @_OdDnia='2020-07-01', @_DoDnia='2020-07-31';

CALL e_dekl_jpk('JPK_V7M', '', '','', '', @_OdDnia,@_DoDnia, '','', 'RAPORT', '', '','');


SELECT @vat7_guid, @test1, @test10;

SELECT * FROM t_vat_s;

SELECT * FROM e_doc_def WHERE doc_type='VAT-7'

SELECT valuea, i.* FROM IReg i WHERE idpath LIKE '%owner%' OR idpath LIKE '%dane%firmy%' OR idkey like '%jpk%' ORDER BY idkey;

SELECT
SUM(K_43), SUM(K_44), SUM(K_45), SUM(K_46)
-- distinct rv_id
FROM t_vat_z t;
WHERE rv_id IN (2020, 2024, 2025, 2036, 2051, 2040)
-- K_23<>0 or
;


SELECT *
-- K_19, K_20, t.*
-- SUM(K_10), SUM(K_11), SUM(K_12), SUM(K_15),SUM(K_16),SUM(K_17), SUM(K_18), SUM(K_19), SUM(K_20), SUM(K_21),SUM(K_22),SUM(K_23),
-- SUM(K_24), SUM(K_27), SUM(K_28),SUM(K_29), SUM(K_30), SUM(K_31), SUM(K_34), SUM(K_35)
-- distinct rv_id
FROM t_vat_s t
;
WHERE rv_id IN (2023)

;

SELECT -- sum(v.netto), sum(v.vat),
rv.rv_id_JPK, n.data_vat, op.grupa, n.rodzaj, v.*, n.*
FROM opng n,  opng_vat v, operdoc op, v_rejestry_vat rv
WHERE v.idnoper = n.guid AND n.rodzaj = op.rodzaj AND rv.Idn = n.rv_id
AND n.deleted=0
-- AND v.idvat<>5
AND n.data_vat BETWEEN '2017-05-01' AND '2017-05-31'
AND rv.rv_id_jpk IN (2022, 2035)
;

select * from rejestry_vat;
select * from erp_vat;
*/



END$$

DELIMITER ;
