DELIMITER $$

DROP PROCEDURE IF EXISTS `GUS_reports`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `GUS_reports`(
     IN _MODE 		VARCHAR(100),        -- Z-06
	 IN _RepSymbol  VARCHAR(100),   	 -- parametr juz nieuzywany
     IN _Rok  		INTEGER,
     IN _MiesStart  INTEGER,
     IN _MiesKoniec INTEGER,     
     INOUT _RepContent TEXT
)
/*
procedura generująca raporty GUS
*/

SPROC: BEGIN

	DECLARE _SQLText TEXT;
	DECLARE _Counter INTEGER;
	DECLARE _PKD, _REGON  VARCHAR(20);		
	DECLARE _TabStat  	  VARCHAR(50);
	DECLARE _PelnaNazwa, _Adres, _Email  VARCHAR(200);		
	

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'DG1.get'
		Zebranie danych HR do przetwarzania
		Parametry
		_MODE			Tryb pracy
		_RepSymbol		
		_Rok	    	Rok, za który jest przygotowywane sprawozdanie
		_MiesStart      Miesiac, za który jest przygotowywane sprawozdanie
		_MiesKoniec     tu ma znaczenie ostatniego dnia miesiaca, za który jest przygotowywane sprawozdanie
		
		CALL GUS_reports('DG1.get', '', '2012', '10', '30', @DG1.RepContent);		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/		
	IF _MODE = 'DG1.get' THEN
	
		-- D1.6  -- DAY(data_pracy) = #Dzien# moze byc zastapione przez LAST_DAY(s.data_pracy)
		SET _SQLText =  'SELECT 
		  COUNT(jest) AS osoby_prac_mies 		
		  FROM stat_prac_#Rok# s 		  
		  WHERE YEAR(data_pracy) = #Rok# AND MONTH(data_pracy)= #Mies#  AND DAY(data_pracy) = #Dzien# AND typ_absencji <> 10
		  INTO @GUS_reports.DG1.osoby_prac_mies;';
		  
		  -- #42614  do pracujacych nie zalicza sie osob przebywajacych na swiadczeniu rehab.  ( typ_absencji = 10)
		  		  
		  SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);	
		  SET _SQLText = REPLACE(_SQLText, '#Mies#', _MiesStart);	
		  SET _SQLText = REPLACE(_SQLText, '#Dzien#', _MiesKoniec);	
		  SET @_SQLText = _SQLText;
		  CALL _ExecuteCmd(_SQLText, @Error);	
		  
		-- D1.7  = (D1.1 w Z-06) 
		-- sr_zatr_rok liczy sie jako suma srednich miesiecznych podzielona przez liczbe miesiecy
		SET _counter = 1;  -- zaczynamy od poczatku roku
		SET @GUS_reports.DG1.sr_zatr_rok = 0.0;
		REPEAT		
			SET _SQLText =  'SELECT  
			ROUND(SUM(s.etat_l/s.etat_m)/DAY(LAST_DAY(s.data_pracy)), 1) AS sr_zatr_mies						
			FROM stat_prac_#Rok# s 
			JOIN prac_gus G ON G.GUID_Pracownika=s.guid_prac 
			JOIN osoby o ON o.guid=s.guid_prac 		
			WHERE s.bezplZ06 = 0 AND s.typ_absencji NOT IN (1, 6, 11, 20) -- chorobowe, macierzyńskie, bezpłatne, wychow.
				AND G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0
				AND MONTH(s.data_pracy) = #Mies# 
			INTO @GUS_reports.DG1.sr_zatr_mies ;';
			-- 1, 11, 20  = chorob., bezpł., wychow.
			
			SET _SQLText = REPLACE(_SQLText, '#Rok#',  _Rok);	
			SET _SQLText = REPLACE(_SQLText, '#Mies#', _counter);	
			-- SET _SQLText = REPLACE(_SQLText, '#Dzien#', _MiesKoniec);	
			SET @_SQLText = _SQLText;
			SET @D1.7_SQLText = _SQLText;
			CALL _ExecuteCmd(_SQLText, @Error);		
			SET @GUS_reports.DG1.sr_zatr_rok = @GUS_reports.DG1.sr_zatr_rok + IFNULL(@GUS_reports.DG1.sr_zatr_mies, 0.0);
			SET  _counter = _counter + 1;
		UNTIL _counter > _MiesStart  -- naliczamy naastajaco az do wskazanego miesiaca, za ktory skladane jest DG-1
		END REPEAT;
		/* Przeciętne zatrudnienie w okresach narastających należy obliczać jako  sumę przeciętnego zatrudnienia w poszczególnych miesiącach 
		   podzieloną przez liczbę miesięcy w okresie sprawozdawczym		 */  
		SET @GUS_reports.DG1.sr_zatr_rok = ROUND(@GUS_reports.DG1.sr_zatr_rok / _MiesStart, 1);
				

		-- D.1.8 czas przepracowany ( = D3.1 w Z-06) 
		SET @GUS_reports.DG1.czas_przepr = 0.0;
		IF _MiesStart IN (3, 6, 9, 12) THEN
			select 
			round(sum(f.godz100prac + f.godz_plus50prac +
					  f.godz_plus50wolne + f.godz_plus100prac + f.godz_plus100wolne)/(60*1000),1)  AS czas_przepr_ogolem
			-- round(sum(f.godz_plus50prac+f.godz_plus50wolne + f.godz_plus100prac + f.godz_plus100wolne)/(60*1000), 1)  as czas_przepr_nadl						  
			from frekw f 
			join prac_gus g on g.guid_pracownika = f.pracguid 
			where f.rok = _Rok  and f.deleted = 0 and g.agent = 0 and g.PracaNakladcza = 0 
				and g.ZatrPozaGranica = 0 and g.ZatrPrzygotZawod = 0 and f.miesiac between 1 and _MiesStart 
			into
				@GUS_reports.DG1.czas_przepr;
		ELSE			
		  SET @GUS_reports.DG1.czas_przepr = NULL;
		END IF;
		
		-- D.1.9 wynagr. brutto (= D2.1 w Z-06, przy czym dodany został "dodatek funkcyjny" (C_4)) 
		SET @GUS_reports.DG1.wynagr_brutto_mies := 0.0;  
		SET @GUS_reports.DG1.wynagr_brutto_rok := 0.0; 
		
		SET _SQLText =  
		'SELECT 
		ROUND((IFNULL(SUM(IF(((ps.GUID_definicja_skladnika_placy IN(''C_0'', ''C_4'', ''C_6'',''C_7'',''C_8'',''C_14'') 
			 OR ps.rodzajskladnika IN(1, 2, 7) )) AND (G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0) AND (PL.miesiac = #Mies#), 
			   ps.kwota + ps.ZmniejszZwieksz, 0)), 0)/(1000)),
			 1) AS brutto_mies,
		ROUND((IFNULL(SUM(IF(((ps.GUID_definicja_skladnika_placy IN(''C_0'', ''C_4'', ''C_6'',''C_7'',''C_8'',''C_14'') 
			 OR ps.rodzajskladnika IN(1, 2, 7) )) AND (G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0), ps.kwota + ps.ZmniejszZwieksz, 0)), 0)/(1000)),
			 1) AS brutto_rok		
		FROM place PL 	
		JOIN prac_gus G ON PL.GUID_Pracownika = G.GUID_Pracownika 
		JOIN place_skladniki ps ON ps.GUID_zapisu_placowego = PL.GUID 
		JOIN definicja_skladnika_placy DSP ON ps.GUID_definicja_skladnika_placy = DSP.GUID 
		  AND DSP.deleted = 0 
		WHERE PL.rok = #Rok# AND PL.deleted = 0 AND PL.miesiac <= #Mies# 		
		INTO @GUS_reports.DG1.wynagr_brutto_mies, @GUS_reports.DG1.wynagr_brutto_rok;
		';  -- AND DSP.UwzglednicGUSZ06 = 1 
		
		SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);	
		SET _SQLText = REPLACE(_SQLText, '#Mies#', _MiesStart);	
		-- SET _SQLText = REPLACE(_SQLText, '#Dzien#', _MiesKoniec);	
		SET @_SQLText = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);		
		
		-- D.1.11 = (D1.4 w Z-06) 
		SET @GUS_reports.DG1.skladki_ubezp = 0.0;
		IF _MiesStart IN (3, 6, 9, 12) THEN		
			SET _SQLText =  
				'select 
					round(sum(p.SklFemerytalnyPracow + p.SklFrentowyPracow + p.SklFchorobowyPracow)/(1000), 1) AS skladki 
				from place p
				join prac_gus g on g.guid_pracownika=p.guid_pracownika and g.PracaNakladcza=0 and g.ZatrPrzygotZawod = 0 and g.Agent = 0 and g.ZatrPozaGranica = 0   
				where p.rok = #Rok#  and p.miesiac <= #Mies# and p.deleted = 0
				INTO @GUS_reports.DG1.skladki_ubezp;	';  
			
			SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);	
			SET _SQLText = REPLACE(_SQLText, '#Mies#', _MiesStart);				
			SET @_SQLText = _SQLText;
			CALL _ExecuteCmd(_SQLText, @Error);
		ELSE
			SET @GUS_reports.DG1.skladki_ubezp = NULL;		
		END IF;
		
	/*	SELECT 
		       @GUS_reports.DG1.osoby_prac_mies, 								
			   @GUS_reports.DG1.sr_zatr_mies,  @GUS_reports.DG1.sr_zatr_rok,    
		       @GUS_reports.DG1.czas_przepr, 									
			   @GUS_reports.DG1.wynagr_brutto_mies, @GUS_reports.DG1.wynagr_brutto_rok, 
			   @GUS_reports.DG1.skladki_ubezp;									*/
			   
		/* kreujemy tabele wynikowa i wstawiamy do niej dane */
		DROP TEMPORARY TABLE IF EXISTS t_dg1;
		
		CREATE TEMPORARY TABLE t_dg1(
			ID varchar(2),
			miesiac integer,
			opis varchar(255),
			wart_mies double (16,4),
			wart_rok  double (16,4)
		);
	
		-- wstawiamy wyliczone dane do tabeli wynikowej  (zestaw znaków Windows1250)
		INSERT INTO t_dg1 VALUES
		('06', _MiesStart, 'Pracujący (w osobach) - stan w ostatnim dniu mies.', @GUS_reports.DG1.osoby_prac_mies, NULL),
		('07', _MiesStart, 'Przeciętna liczba zatr. (w osobach)', @GUS_reports.DG1.sr_zatr_mies,  @GUS_reports.DG1.sr_zatr_rok),
		('08', _MiesStart, 'Czas przepracowany (w tys. godz.)', NULL, @GUS_reports.DG1.czas_przepr),
		('09', _MiesStart, 'Wynagrodzenia brutto', @GUS_reports.DG1.wynagr_brutto_mies, @GUS_reports.DG1.wynagr_brutto_rok),
		('11', _MiesStart, 'Składki na ubezp.', NULL, @GUS_reports.DG1.skladki_ubezp);
		
		-- zwracamy dane do dataseta
		SELECT * FROM t_dg1;
	
	END IF;
	
		
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Z12.select.HR'
		Zebranie danych HR do przetwarzania
		Parametry
		_MODE			Tryb pracy
		_RepSymbol		tu ma znaczenie: join do tabeli ze slownikiem
		_Rok	    	Rok, za który jest przygotoywane sprawozdanie
		_MiesStart      const = 10
		
		CALL GUS_reports('Z12.select.HR', '', '2012', '10', '', @Z12.RepContent);		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/		
	IF _MODE = 'Z12.select.HR' THEN
	
		SET _SQLText =  'SELECT 
		  P.GUID, Substr(Z.KodGUS, 1, 6) as kod, O.plec, O.nazwisko, O.imiona 
		  FROM Pracownicy P 
		  join osoby O on P.guid = O.GUID 
		  join frekw F on F.PracGUID = P.GUID and 
		  (select count(*) as ile  from frekw_po FP join frekw FR ON FR.GUID=FP.FrekwGUID where FP.absencja in (''J'',''C'',''L'',''K'',''M'',''R'',''N'',''Z'',''A'',''Y'') and 
		   YEAR(FP.rok_msc_dzien)= #Rok# and MONTH(FP.rok_msc_dzien)=10 and FR.PracGUID=P.GUID  and FR.deleted = 0 ) = 0 and 
		  (select count(*) as ile  from frekw_po FP join frekw FR ON FR.GUID=FP.FrekwGUID where FP.absencja in (''B'') and 
		   YEAR(FP.rok_msc_dzien)= #Rok# and MONTH(FP.rok_msc_dzien)=10 and FR.PracGUID=P.GUID  and FR.deleted = 0 ) <= 10 

		  and F.rok = #Rok# and F.miesiac = 10  and F.deleted = 0 
		  join prac_gus G on G.guid_pracownika=P.guid and 
			(G.PracaNakladcza=0 and G.ZatrPrzygotZawod = 0 and G.Agent = 0 and G.ZatrPraceInterw=0) 
		  LEFT JOIN #join_zawody# 
		  where (P.ToPracownik = 1 OR P.ProwadziDzialalnosc = 1) and P.DataZatrudnieniaWzakl <= ''#Rok#-10-01'' 
		  and (if(!ISNULL(P.DataZwolnienia), P.DataZwolnienia, ''2099-01-01'') > ''#Rok#-10-31'') 
		  group by P.GUID 
		  order by kod ASC, O.plec DESC, O.nazwisko, O.imiona ASC ';	
	
		  -- Z ON P.ZawodWykonGUS = Z.idn 
	
		  -- 26.03.2013 wylaczylismy warunek na (P.etat_l/P.etat_m = 1), dodajemy warunek (P.ToPracownik = 1 OR P.ProwadziDzialalnosc = 1)
		  
		  SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);	
		  SET _SQLText = REPLACE(_SQLText, '#join_zawody#', _RepSymbol);	
		  SET @_SQLText = _SQLText;
		  CALL _ExecuteCmd(_SQLText, @Error);	
	
		  -- docelowo (gdy calosc naliczania bedzie tutaj), powyzszy select bedzie do create temp table
	END IF;
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Z06.create'
		Wygenerowanie raportu Z-06
		Parametry
		_MODE			Tryb pracy
		_RepSymbol		Symbol formularza (zgodny z oknem "O formularzu": Symbol wewnętrzny: GZ06_10 (za 2011),  GZ06_11 (za 2012)
		_Rok	    	Rok, za który jest przygotoywane sprawozdanie
		_MiesStart	
		_MiesKoniec
		_RepContent     Wygenerowana zawartość raportu, która zostanie zapisana do pliku 1IPSFORM.DTA
		
		CALL GUS_reports('Z06.create', 'GZ06_10', '2011', '1', '12' @RepContent);
		CALL GUS_reports('Z06.create', 'GZ06_11', '2012', '1', '12' @RepContent);
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/		
IF _MODE = 'Z06.create' THEN
BEGIN	

	DECLARE _pr_zatr_ogolem, _pr_zatr_robotn, _pr_zatr_uczniowie, _pr_zatr_zagr, _pr_zatr_interw, 
	  _skladki, _brt_ogolem, _brt_uczniowie, _brt_zagranica, _brt_interw,
	  _czas_przepr_ogolem, _czas_przepr_nadl, _czas_nieprzepr_ogolem, _czas_nieprzepr_oplacony, _c1, _c2 	DOUBLE;
--	DECLARE  _prac_ogolem,	_prac_ogolem_wmn INTEGER;
	DECLARE  _LastDay  DATE;
	
	SET _TabStat = REPLACE('stat_prac_#Rok#', '#Rok#', _Rok);   -- po przejsciu na hr_Stat_tools - bedzie jedna tabela , z kolumną "rok"
	
	SET _SQLText =  'SELECT DATE(''#Rok#-12-31'') INTO @GUS_reports.Z06.create_LastDay';
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);	
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);	
	SET _LastDay = @GUS_reports.Z06.create_LastDay;

	SET @GUS_reports.Z06_RepContent = '';
	
	-- SET _RepSymbol = 'GZ06_11'; ustawiac symbol  !!!!
	
	SET @GUS_reports.Z06_RepContent = CONCAT('[form_1]', '\nsymbol=', _RepSymbol, '\nedit=1', '\ntyp=1', '\nopis=Z-06(za #Rok#)', '\nbylWydruk=0');
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\nzatw=0', '\ndataFormul=20000101', '\nnrPrac=', '\nnrPPrac=0', '\nnrKontr=0', '\nnrPKontr=0');
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\nprocO=');
	
	CALL _IReg_Get('DaneFirmy', 'Pelna_nazwa', '0', 'STRING', _PelnaNazwa, '(brak danych)');
	CALL _IReg_Get('DaneFirmy', 'Address Info', '0', 'STRING', _Adres, '(brak danych)');
	CALL _IReg_Get('DaneFirmy', 'EKD', '0', 'STRING', _PKD, '(brak danych)');   
	CALL _IReg_Get('DaneFirmy', 'REGON', '0', 'STRING', _REGON, '(brak danych');   
	CALL _IReg_Get('DaneFirmy', 'OurMail', '0', 'STRING', _Email, '(brak danych');   
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\npNAG.1=', _PelnaNazwa);
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\npNAG.2=', _Adres);
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\npNAG.3=', _REGON);  
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\npNAG.4=', _PKD); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\npNAG.5=', _Email); 
	
	-- "stan aktywności prawnej i ekonomicznej" (A i B), "status zatrudnienia pracujących"
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np0.1.A=01', '\np0.1.B=0000', '\np0.1.C=01001000');  -- zazn.: prowadzacy dzialaność (A i B); umowa o prace, umowa o dzielo (C)
	
	-- Dział 1.
	
	DROP TEMPORARY TABLE IF EXISTS tt_d1_liczba_zatr; -- kolumna "przecietna liczba zatrudnionych"
	
	SET _SQLText =  
	'CREATE TEMPORARY TABLE tt_d1_liczba_zatr
		SELECT MONTH(s.data_pracy) AS msc, 
			SUM(IF(G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0, s.etat_l/s.etat_m, 0))/DAY(LAST_DAY(s.data_pracy)) AS sr_mies_ogolem, 
			SUM(IF(ks.cecha = ''C_GUS_Z06_robotnicze'' AND G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0, s.etat_l/s.etat_m, 0))/DAY(LAST_DAY(s.data_pracy)) AS sr_mies_robotn, 
			SUM(IF(G.ZatrPrzygotZawod=1 AND G.PracaNakladcza=0 AND G.ZatrPraceInterw = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0, s.etat_l/s.etat_m, 0))/DAY(LAST_DAY(s.data_pracy)) AS sr_mies_uczniowie, 
			SUM(IF(G.ZatrPozaGranica=1 AND G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPraceInterw = 0, s.etat_l/s.etat_m, 0))/DAY(LAST_DAY(s.data_pracy)) AS sr_mies_zagranica, 
			SUM(IF(G.PracaNakladcza=0 AND G.ZatrPraceInterw=1 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0, s.etat_l/s.etat_m, 0))/DAY(LAST_DAY(s.data_pracy)) AS sr_mies_interw 
		FROM stat_prac_#Rok# s 
		JOIN prac_gus G ON G.GUID_Pracownika=s.guid_prac 
		JOIN osoby o ON o.guid=s.guid_prac 
		LEFT JOIN klasyfikacja_stanowisk ks ON ks.guid_stanowiska = s.stanowisko AND ks.klasa_cech = ''C_GUS_Z06_robotn.'' 
		WHERE s.bezplZ06 = 0 AND MONTH(s.data_pracy) BETWEEN #MiesStart# AND #MiesKoniec# 
		GROUP BY MONTH(s.data_pracy); 
	';
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET _SQLText = REPLACE(_SQLText, '#MiesStart#', _MiesStart);
	SET _SQLText = REPLACE(_SQLText, '#MiesKoniec#', _MiesKoniec);
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	ALTER TABLE tt_d1_liczba_zatr ADD INDEX msc(msc);	
	
	-- dla Z-06 dzielimy zawsze przez 12 (niezależnie od tego, czy zaklad funkcjonowal przez cały rok, czy też nie)
	SELECT 
		ROUND(SUM(sr_mies_ogolem)    / (_MiesKoniec - _MiesStart + 1), 2), 	
		ROUND(SUM(sr_mies_robotn)    / (_MiesKoniec - _MiesStart + 1), 2), 	
		ROUND(SUM(sr_mies_uczniowie) / (_MiesKoniec - _MiesStart + 1), 2), 	
		ROUND(SUM(sr_mies_zagranica) / (_MiesKoniec - _MiesStart + 1), 2), 	
		ROUND(SUM(sr_mies_interw)    / (_MiesKoniec - _MiesStart + 1), 2)
    FROM	
		tt_d1_liczba_zatr
	INTO _pr_zatr_ogolem, _pr_zatr_robotn, _pr_zatr_uczniowie, _pr_zatr_zagr, _pr_zatr_interw;
	
	SET @GUS_reports.Z06.create_brt_ogolem = 0.0;
	SET @GUS_reports.Z06.create_brt_uczniowie = 0.0;
	SET @GUS_reports.Z06.create_brt_zagranica = 0.0;
	SET @GUS_reports.Z06.create_brt_interw = 0.0;
	
	SET _SQLText =  
	'SELECT 
    ROUND((IFNULL(SUM(IF(((ps.GUID_definicja_skladnika_placy IN(''C_0'', ''C_6'',''C_7'',''C_8'',''C_14'') 
         OR ps.rodzajskladnika IN(1, 2, 7) )) AND (G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0), ps.kwota + ps.ZmniejszZwieksz, 0)), 0)/(1000)),
         1) AS brutto_ogolem,
    ROUND((IFNULL(SUM(IF(((ps.GUID_definicja_skladnika_placy NOT IN(''C_9'', ''C_10'', ''C_15'', ''C_16'', ''C_17'', ''C_18'', ''C_19'', ''C_20'') 
          )) AND (G.ZatrPrzygotZawod = 1), ps.kwota + ps.ZmniejszZwieksz, 0)), 0)/(1000)),
         1) AS brutto_uczniowie,
    ROUND((IFNULL(SUM(IF(((ps.GUID_definicja_skladnika_placy NOT IN(''C_9'', ''C_10'', ''C_15'', ''C_16'', ''C_17'', ''C_18'', ''C_19'', ''C_20'') 
          )) AND (G.ZatrPozaGranica = 1), ps.kwota + ps.ZmniejszZwieksz, 0)), 0)/(1000)),
         1) AS brutto_zagranica,
    ROUND((IFNULL(SUM(IF(((ps.GUID_definicja_skladnika_placy NOT IN(''C_9'', ''C_10'', ''C_15'', ''C_16'', ''C_17'', ''C_18'', ''C_19'', ''C_20'') 
          )) AND (G.ZatrPraceInterw = 1 AND G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0), ps.kwota + ps.ZmniejszZwieksz, 0)), 0)/(1000)),
         1) AS brutto_interw	
	FROM place PL 	
	JOIN prac_gus G ON PL.GUID_Pracownika = G.GUID_Pracownika 
	JOIN place_skladniki ps ON ps.GUID_zapisu_placowego = PL.GUID 
	JOIN definicja_skladnika_placy DSP ON ps.GUID_definicja_skladnika_placy = DSP.GUID 
	  AND DSP.deleted = 0 AND DSP.UwzglednicGUSZ06 = 1 
	WHERE PL.rok = #Rok# AND PL.deleted = 0  		
	INTO @GUS_reports.Z06.create_brt_ogolem, @GUS_reports.Z06.create_brt_uczniowie, @GUS_reports.Z06.create_brt_zagranica, @GUS_reports.Z06.create_brt_interw	;
	';
	-- JOIN Pracownicy K ON PL.GUID_Pracownika = K.GUID AND K.DataZatrudnieniaWzakl <= ''2010-01-01''
	--       AND (IF(!ISNULL(K.DataZwolnienia), K.DataZwolnienia, ''2099-01-01'') > ''2010-12-31'')
	
	
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	-- SET @m._SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	-- SET @m._brt_ogolem = _brt_ogolem;
	-- SET @m.debug = _SQLText;
	
	SET @GUS_reports.Z06.create_skladki = 0.0;
	
	SET _SQLText =  
	'select 
		round(sum(p.SklFemerytalnyPracow + p.SklFrentowyPracow + p.SklFchorobowyPracow)/(1000), 1) AS skladki 
	from place p
    join prac_gus g on g.guid_pracownika=p.guid_pracownika and g.PracaNakladcza=0 and g.ZatrPrzygotZawod = 0 and g.Agent = 0 and g.ZatrPozaGranica = 0   
    where p.rok = #Rok#  and p.deleted = 0
	INTO @GUS_reports.Z06.create_skladki;	';  -- D.1.4
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	
	SET @GUS_reports.Z06.create_prog1 = 0;   -- w 2011: 0       - 1386.00
	SET @GUS_reports.Z06.create_prog2 = 0;   -- w 2011: 1108.80 - 1385.99
	
	SET _SQLText =  
	'SELECT
		_varINT_NOTNULL(SUM(IF(
			(SELECT sum(GREATEST(h.PlacaZasadnicza,(pl.PodstFchorobowy+pl.chorobowefirma)))/(S.etat_l/S.etat_m) from place pl 
			JOIN place_skladniki ps on ps.guid_zapisu_placowego=pl.guid and ps.guid_definicja_skladnika_placy =''C_0'' 
			where ps.kwota > 0 and pl.rok = #Rok# and pl.miesiac = 12 and (pl.deleted = 0)  and pl.guid_pracownika=S.guid_prac
			) between 0 and #MinWynagr#, 1, 0))) as ile1,
		_varINT_NOTNULL(SUM( IF(
			(SELECT SUM(GREATEST(h.PlacaZasadnicza,(pl.PodstFchorobowy+pl.chorobowefirma)))/(S.etat_l/S.etat_m) FROM place pl 
			JOIN place_skladniki ps ON ps.guid_zapisu_placowego=pl.guid AND ps.guid_definicja_skladnika_placy =''C_0''
			WHERE ps.kwota > 0 AND pl.rok = #Rok# AND pl.miesiac = 12 AND (pl.deleted = 0)  AND pl.guid_pracownika=S.guid_prac
			) BETWEEN #MinWynagr.prog2# AND (#MinWynagr# - 0.01), 1, 0))) AS ile2  
		FROM stat_prac_#Rok# S
		JOIN osoby o on o.guid = S.guid_prac 
		JOIN historia_pracy h ON h.GUIDPr = S.guid_prac AND h.OdDnia = (SELECT MAX(OdDnia) FROM historia_pracy WHERE GUIDPr = h.GUIDPr AND h.OdDnia <= "#Rok#-12-31")
		JOIN prac_gus G on G.GUID_Pracownika=S.guid_prac AND G.PracaNakladcza=0 AND G.ZatrPrzygotZawod = 0 AND G.Agent = 0 AND G.ZatrPozaGranica = 0 
			where S.data_pracy = ''#Rok#-12-31''   
		INTO @GUS_reports.Z06.create_prog1, @GUS_reports.Z06.create_prog2;		
	';   
		
	CALL _IReg_Get('Place2003\\Progi_podatkowe_stawki_ZUS', 'Minimalne_wynagrodzenie', _Rok, 'FLOAT', @IREG.MinWynagr, 1750);  -- wartosc def. 1680
	-- wiersz D.1.6.2:
	CALL _IReg_Get('Place2003\\Progi_podatkowe_stawki_ZUS', 'Minimalne_wynagrodzenie_Z06_prog2', _Rok, 'FLOAT', @IREG.MinWynagr.prog2, 1480);  -- wartosc def. 1344
	
	/*  RegistryInt.GetRegIntValueDouble_R(DBPlacesKadryPlaceUpodobania.AktywnyRok, 'Minimalne_wynagrodzenie',
        cMin_wynagr, 'Place2003\Progi_podatkowe_stawki_ZUS', 1);  = 1680 */
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);
	SET _SQLText = REPLACE(_SQLText, '#MinWynagr#', @IREG.MinWynagr);
	SET _SQLText = REPLACE(_SQLText, '#MinWynagr.prog2#', @IREG.MinWynagr.prog2);
	SET @MinWynagr_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);  
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R1.K1=', IFNULL(_pr_zatr_ogolem, 0.0)); 
	-- SET @m.debug = @GUS_reports.Z06_RepContent;
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R1.K2=', IFNULL(@GUS_reports.Z06.create_brt_ogolem, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R2.K1=', IFNULL(_pr_zatr_uczniowie, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R2.K2=', IFNULL(@GUS_reports.Z06.create_brt_uczniowie, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R3.K1=', IFNULL(_pr_zatr_zagr, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R3.K2=', IFNULL(@GUS_reports.Z06.create_brt_zagranica, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R4.K2=', IFNULL(@GUS_reports.Z06.create_skladki, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R5.K1=', IFNULL(_pr_zatr_interw, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R5.K2=', IFNULL(@GUS_reports.Z06.create_brt_interw, 0.0)); 
	
	-- od dekl. za 2017 zamiast R6.KA i R6.KB  jest jedno pole
	/* SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R6.KA=', @GUS_reports.Z06.create_prog1); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R6.KB=', @GUS_reports.Z06.create_prog2); 
	-- p1.R6.KA=6
	-- p1.R6.KB=3  */
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np1.R6.KA=', @GUS_reports.Z06.create_prog1); 
	
	/* sekcja podpisu na dole pierwszej strony - jesli te pola są puste, to nie pojawiają sie w pliku 1IPSFORM.DTA
	pPOD.1=Edyta Slwoik
	pPOD.2=58 320 40 60
	pPOD.3=Gdansk
	pPOD.4=16-01-2012
	pPOD.5=pieczatka, podpis
	*/
	
	/* Dział 2. Składniki wynagrodzeń - brutto */
	SET _SQLText =  
	'SELECT
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and DSP.Interpretacja_GUS not in(1, 2), 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS wynagr_osob_2_1, 
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and DSP.Interpretacja_GUS = 4, 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS wynagr_osob_nadliczb_2_2, 
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and G.PracaNakladcza = 1, 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS wynagr_osob_naklad_2_3, 
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and DSP.Interpretacja_GUS = 2, 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS wyplaty_zysk_2_4, 
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and DSP.Interpretacja_GUS = 1, 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS wyplaty_budzet_2_5, 		 
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and DSP.Interpretacja_GUS = 3, 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS honoraria_2_7,
		  ROUND(sum(if(G.ZatrPrzygotZawod = 0 and G.Agent = 1, 
		   (PS.kwota+PS.ZmniejszZwieksz), 0))/1000, 1) AS wynagr_agencyjne  
		FROM place PL 
		JOIN place_skladniki PS on PS.GUID_zapisu_placowego = PL.GUID 
		JOIN definicja_skladnika_placy DSP on PS.guid_definicja_skladnika_placy = DSP.GUID and 
		  DSP.UwzglednicGUSZ06 = 1
		JOIN prac_gus G on G.GUID_Pracownika = PL.GUID_Pracownika 
		WHERE PL.rok = #Rok# and (PL.deleted = 0) and DSP.deleted = 0 and 
		  (DSP.guid in(''C_0'', ''C_6'', ''C_7'', ''C_8'', ''C_14'') or PS.rodzajskladnika in(1, 2, 7) )
		INTO 
		    @GUS_reports.Z06.create_wynagr_osob, @GUS_reports.Z06.create_wynagr_osob_nadliczb,
			@GUS_reports.Z06.create_wynagr_naklad, @GUS_reports.Z06.create_wyplaty_zysk,
			@GUS_reports.Z06.create_wyplaty_budzet, @GUS_reports.Z06.create_honoraria,	
			@GUS_reports.Z06.create_wynagr_agencyjne;  
    ';  
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	SET @GUS_reports.Z06.create_ChoroboweFirma = 0.0;
	SET _SQLText =  
	'SELECT round(SUM(PL.ChoroboweFirma)/1000, 1) as ChoroboweFirma 
      FROM place PL 	
      WHERE PL.rok = #Rok# and PL.deleted = 0 
	INTO   @GUS_reports.Z06.create_ChoroboweFirma
	';
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	SET @GUS_reports.Z06.create_D2_1 = 0.0;
	SET @GUS_reports.Z06.create_D2_1 = IFNULL(@GUS_reports.Z06.create_wynagr_osob, 0.0); -- "Chorobowe firma" jest juz zawarte w tej kwocie  ( cChoroboweFirma = 7;) + IFNULL(@GUS_reports.Z06.create_ChoroboweFirma, 0.0);
		
	
	SET @GUS_reports.Z06.create_wynagr_bezosob = 0.0;
	SET _SQLText =  
	'SELECT round(sum(KwotaBrutto - (ZasChorRazem + ZasRehab + ZasMacierz) )/1000, 1) as wynagr_bezosob 
      FROM prace_zlecone PL 
      WHERE PL.rok = #Rok# and PL.deleted = 0 and RodzajRyczaltu <> 3	
	INTO   @GUS_reports.Z06.create_wynagr_bezosob 
	';  -- and RodzajRyczaltu <> 3 <- nie bierzemy dywidend;  CDG sugerowało, że nalezy tu wyłaczyć także 13 "udział w org. stanowiących osób praw." ale AS tego nie potwierdzil
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R1=', @GUS_reports.Z06.create_D2_1); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R2=', IFNULL(@GUS_reports.Z06.create_wynagr_osob_nadliczb, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R3=', IFNULL(@GUS_reports.Z06.create_wynagr_naklad, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R4=0.0');   -- prac. obcy
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R5=', IFNULL(@GUS_reports.Z06.create_wyplaty_zysk, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R6=0.0');   -- prac. obcy
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R7=', IFNULL(@GUS_reports.Z06.create_wyplaty_budzet, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R8=0.0');   -- prac. obcy
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R9=', IFNULL(@GUS_reports.Z06.create_wynagr_bezosob, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R10=', IFNULL(@GUS_reports.Z06.create_honoraria, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R11=0.0');   -- prac. obcy
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2.R12=', IFNULL(@GUS_reports.Z06.create_wynagr_agencyjne, 0.0)); 	   -- wynagr. agencyjno-prowiz.
		
	/* Dział 2a. Zaleglosci */	
		
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2A.R1=0');   
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np2A.R2=0.0');   
	
	/* Dział 3. Czas pracy */
	SET _czas_przepr_ogolem = 0.0;
	SET _czas_przepr_nadl = 0.0;
	SET _czas_nieprzepr_ogolem = 0.0;
	
	select 
		round(sum(f.godz100prac + f.godz_plus50prac +
				  f.godz_plus50wolne + f.godz_plus100prac + f.godz_plus100wolne)/(60*1000),1)  AS czas_przepr_ogolem,
		round(sum(f.godz_plus50prac+f.godz_plus50wolne + f.godz_plus100prac + f.godz_plus100wolne)/(60*1000), 1)  as czas_przepr_nadl						  
	from frekw f 
	join prac_gus g on g.guid_pracownika = f.pracguid 
	where f.rok = _Rok  and f.deleted = 0 and g.agent = 0 and g.PracaNakladcza = 0 
		and g.ZatrPozaGranica = 0 and g.ZatrPrzygotZawod = 0 and f.miesiac between _MiesStart and _MiesKoniec
	into
	  _czas_przepr_ogolem, _czas_przepr_nadl;
	
	select round(sum(if(trim(fo.absencja)
		not in ('E','F','','-'), fo.kp_godziny, 0))/(60*1000),1) as czas_nieprzepr_ogolem
	from frekw_po fo 
	join frekw f on fo.FrekwGUID=f.GUID 
	join prac_gus g on g.guid_pracownika=f.pracguid 
	where f.rok = _Rok  and f.deleted = 0 and g.agent=0 and g.PracaNakladcza=0 
		and g.ZatrPozaGranica = 0 and g.ZatrPrzygotZawod = 0 and f.miesiac between _MiesStart and _MiesKoniec
	into
	  _czas_nieprzepr_ogolem;	

	-- platne przez zaklad pracy - z absencji: ("W","G","P","S","O","D","T")  
	select round(sum(if(trim(fo.absencja)
		in ("W","G","P","S","O","D","T"), fo.kp_godziny, 0))/(60*1000),1) as czas_nieprzepr_oplacony
	from frekw_po fo 
	join frekw f on fo.FrekwGUID=f.GUID 
	join prac_gus g on g.guid_pracownika=f.pracguid 
	where f.rok = _Rok  and f.deleted = 0 and g.agent=0 and g.PracaNakladcza=0 
		and g.ZatrPozaGranica = 0 and g.ZatrPrzygotZawod = 0 and f.miesiac between _MiesStart and _MiesKoniec
	into _c1	  ;	  
	  
	-- platne przez zaklad pracy - z rozliczenia zasilkow
	select  
 
		  round(sum(if(data between r.dataod and date_add(r.dataod, interval r.dnichfirma-1 day), kp.godziny, 0)) /(1000*60), 1)
		as czas_nieprzepr_oplacony_rz
		  -- sum(if(data between r.dataod and date_add(r.dataod, interval r.dnichfirma-1 day), kp.godziny, 0))/60 as godziny
		from rozliczenie_zasilku r 
		join place pl on r.guid_place=pl.guid and (pl.deleted = 0) 
		join Pracownicy p on pl.guid_pracownika=p.guid 
		join osoby o on p.guid=o.guid
		join prac_gus g on g.guid_pracownika=p.guid 
		join kalendpracy k on p.symbolkalendarza=k.symbol and k.rok = _Rok AND k.deleted = 0 
		join kalendpracy_po kp on kp.guidng=k.guid
		where kwotafirma>0 and r.powod in (1,4) and pl.rok = _Rok
		  and (g.agent=0 and g.PracaNakladcza=0 and g.ZatrPozaGranica=0 and g.ZatrPrzygotZawod=0) 
		-- group by pl.miesiac, p.guid
	into	_c2; 
	
	SET _czas_nieprzepr_oplacony = IFNULL(_C1, 0.0) + IFNULL(_C2, 0.0);
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np3.R1=', IFNULL(_czas_przepr_ogolem, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np3.R2=', IFNULL(_czas_przepr_nadl, 0.0)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np3.R3=', IFNULL(_czas_nieprzepr_ogolem, 0.0)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np3.R4=', IFNULL(_czas_nieprzepr_oplacony, 0.0)); 	-- nowy wiersz dodany w raporcie za 2013 r.
	
	/* Dział 4. Pracujacy - stan w dniu 31.XII */
	SET _LastDay = REPLACE('#Rok#-12-31', '#Rok#', _Rok);	
	
	SET @GUS_reports.Z06.create_prac_ogolem := 0;
	SET @GUS_reports.Z06.create_prac_ogolem_wmn := 0;
	
	SET _SQLText =  
	'select
	  sum(1) AS pracujacy_ogolem,
	  sum(if(O.plec=0, 1, 0)) AS pracujacy_ogolem_kobiety
	from stat_prac_#Rok# P
	join prac_gus G on G.guid_pracownika = P.GUID_prac
	join osoby O on P.GUID_prac = O.GUID
		where  P.Data_pracy = ''#Rok#-12-31''
	into @GUS_reports.Z06.create_prac_ogolem,	@GUS_reports.Z06.create_prac_ogolem_wmn;
	';   -- QUOTE(_LastDay)
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	-- p4.R01.K1=41
    -- p4.R01.K2=20

	SET _SQLText =  
	'SELECT
		  sum(1) AS GMP_prac_ogolem,
		  sum(if(O.plec=0, 1, 0)) AS GMP_prac_ogolem_kobiety,
		  sum(IF(P.Etat_L/P.Etat_M = 1, 1, 0)) AS GMP_prac_pelnozatr,
		  sum(if(O.plec=0 and P.Etat_L/P.Etat_M = 1, 1, 0)) AS GMP_prac_pelnozatr_kobiety,
		  sum(IF(P.Etat_L/P.Etat_M < 1, 1, 0)) AS GMP_prac_niepelnozatr,
		  sum(if(O.plec=0 and P.Etat_L/P.Etat_M < 1, 1, 0)) AS GMP_prac_niepelnozatr_kobiety,
		  0 AS GMP_prac_wlasc, -- sum(IF((G.Wlasciciel or G.CzlonekRodzinyWlasc), 1, 0)) AS GMP_prac_wlasc,  -- tutaj wykazujemy wlascicieli bez umowy o prace, wiec niniejsza suma zawsze da zero, trzeba policzyc oddzielnym zapytaniem
		  0 AS GMP_prac_wlasc_kobiety, -- sum(if(O.plec=0 and (G.Wlasciciel or G.CzlonekRodzinyWlasc), 1, 0)) AS GMP_prac_wlasc_kobiety,
		  sum(IF(G.PracaNakladcza, 1, 0)) AS GMP_prac_nakl,
		  sum(if(O.plec=0 and G.PracaNakladcza, 1, 0)) AS GMP_prac_nakl_kobiety,
		  sum(IF(G.Agent, 1, 0)) AS GMP_prac_agenci,
		  sum(if(O.plec=0 and G.Agent, 1, 0)) AS GMP_prac_agenci_kobiety,
		  sum(IF(G.CzlonekRSP, 1, 0)) AS GMP_prac_RSP,
		  sum(if(O.plec=0 and G.CzlonekRSP, 1, 0)) AS GMP_prac_RSP_kobiety,
		  sum(IF(G.PracaNocna, 1, 0)) AS GMP_prac_noc,
		  sum(if(O.plec=0 and G.PracaNocna, 1, 0)) AS GMP_prac_noc_kobiety,
		  sum(IF(P.TypNiepelnospr, 1, 0)) AS GMP_prac_inw,
		  sum(if(O.plec=0 and P.TypNiepelnospr, 1, 0)) AS GMP_prac_inw_kobiety,
		  sum(IF(G.Cudzoziemiec, 1, 0)) AS GMP_prac_fgn,
		  sum(if(O.plec=0 and G.Cudzoziemiec, 1, 0)) AS GMP_prac_fgn_kobiety,
		  sum(IF(G.emeryt_rencista, 1, 0)) AS GMP_prac_er,
		  sum(if(O.plec=0 and G.emeryt_rencista, 1, 0)) AS GMP_prac_er_kobiety,
		  sum(IF(G.telepraca, 1, 0)) AS GMP_telepraca,
		  sum(if(O.plec=0 and G.telepraca, 1, 0)) AS GMP_telepraca_kobiety
		FROM stat_prac_#Rok# P
		JOIN prac_gus G on G.guid_pracownika = P.GUID_prac AND G.GlowneMP = 1
		JOIN osoby O on P.GUID_prac = O.GUID
		WHERE  P.Data_pracy = ''#Rok#-12-31'' 
	INTO 
		@GUS_reports.Z06.create_GMP_ogolem, @GUS_reports.Z06.create_GMP_ogolem_wmn,	
		@GUS_reports.Z06.create_GMP_peln, @GUS_reports.Z06.create_GMP_peln_wmn,	
		@GUS_reports.Z06.create_GMP_niepeln, @GUS_reports.Z06.create_GMP_niepeln_wmn,	
		@GUS_reports.Z06.create_GMP_owner, @GUS_reports.Z06.create_GMP_owner_wmn,	
		@GUS_reports.Z06.create_GMP_nakl, @GUS_reports.Z06.create_GMP_nakl_wmn,	
		@GUS_reports.Z06.create_GMP_agent, @GUS_reports.Z06.create_GMP_agent_wmn,	
		@GUS_reports.Z06.create_GMP_RSP, @GUS_reports.Z06.create_GMP_RSP_wmn,	
		@GUS_reports.Z06.create_GMP_noc, @GUS_reports.Z06.create_GMP_noc_wmn,	
		@GUS_reports.Z06.create_GMP_inw, @GUS_reports.Z06.create_GMP_inw_wmn,	
		@GUS_reports.Z06.create_GMP_fgn, @GUS_reports.Z06.create_GMP_fgn_wmn,	
		@GUS_reports.Z06.GMP_prac_er,   @GUS_reports.Z06.GMP_prac_er_wmn,
		@GUS_reports.Z06.GMP_telepraca, @GUS_reports.Z06.GMP_telepraca_wmn
	';
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @D4_SQL = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	--  R04 i R06:
	SET _SQLText =  
	'SELECT	
	  ifnull(sum(if((P.Etat_L/P.Etat_M = 1), 1, 0)), 0)  as peln_um_okr,
	  ifnull(sum(if((P.Etat_L/P.Etat_M = 1) AND O.plec=0, 1, 0)), 0)  as peln_um_okr_wmn,
	  ifnull(sum(if((P.Etat_L/P.Etat_M < 1), 1, 0)), 0)  as niepeln_um_okr,
	  ifnull(sum(if((P.Etat_L/P.Etat_M < 1) AND O.plec=0, 1, 0)), 0)  as niepeln_um_okr_wmn
	from stat_prac_#Rok# P
	join prac_gus G on G.guid_pracownika = P.GUID_prac and  G.GlowneMP
	join osoby O on P.GUID_prac=O.GUID 
	join historia_pracy h on P.GUID_prac = h.GUIDPr AND h.TypOkresuUmowy <> 2 AND
	   h.OdDnia = (SELECT MAX(OdDnia) FROM historia_pracy WHERE OdDnia <= ''#Rok#-12-31'' AND GUIDPr = O.GUID )
	WHERE  P.Data_pracy = ''#Rok#-12-31''
	INTO
		@GUS_reports.Z06.create_GMP_peln_um_okr, @GUS_reports.Z06.create_GMP_peln_um_okr_wmn,	
		@GUS_reports.Z06.create_GMP_niepeln_um_okr, @GUS_reports.Z06.create_GMP_niepeln_um_okr_wmn	
	'; 	
			
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;	
	CALL _ExecuteCmd(_SQLText, @Error);
	
	-- "Właściciele mający umowę o pracę wykazywani są wierszu 03 lub 05, a bez umowy w wierszu 07"
	-- R4.07 : zaznaczeni jako zleceniobiorcy a nie pracownicy, dane do GUS: "wlasciciel, wspolwlascicel", posiadajacy zlecenie za XII.rok, o typie = "udział w org. stanowiących osób praw."
	SET _SQLText =  
	'SELECT	
	    ifnull(sum(IF((G.Wlasciciel or G.CzlonekRodzinyWlasc), 1, 0)), 0)   		   AS GMP_prac_wlasc, 
		ifnull(sum(if(O.plec=0 and (G.Wlasciciel or G.CzlonekRodzinyWlasc), 1, 0)), 0) AS GMP_prac_wlasc_kobiety		
	from Pracownicy P
	join prac_gus G on G.guid_pracownika = P.GUID and  G.GlowneMP
	join osoby O on P.GUID = O.GUID 
	join historia_pracy h on P.GUID = h.GUIDPr AND	h.OdDnia = (SELECT MAX(OdDnia) FROM historia_pracy WHERE OdDnia <= ''#Rok#-12-31'' AND GUIDPr = P.GUID )
	WHERE 
		(h.ToZleceniobiorca = 1) AND (h.ToPracownik = 0)  AND
		EXISTS (SELECT * FROM prace_zlecone z WHERE z.GUID_pracownika = P.GUID AND z.rok = #Rok# AND z.miesiac = 12 and z.RodzajRyczaltu = 13 and z.deleted = 0)
	INTO 
		@GUS_reports.Z06.create_GMP_owner, @GUS_reports.Z06.create_GMP_owner_wmn
	';
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @D4_7_SQL = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
		
	-- jesli mamy wynik niezerowy, to dodajemy do wierszy "ogółem"  (1 i 2)
	IF @GUS_reports.Z06.create_GMP_owner > 0 THEN
		SET @GUS_reports.Z06.create_prac_ogolem = @GUS_reports.Z06.create_prac_ogolem + @GUS_reports.Z06.create_GMP_owner;
		SET @GUS_reports.Z06.create_GMP_ogolem = @GUS_reports.Z06.create_GMP_ogolem + @GUS_reports.Z06.create_GMP_owner;
		
		IF @GUS_reports.Z06.create_GMP_owner_wmn > 0 THEN
			SET @GUS_reports.Z06.create_prac_ogolem_wmn = @GUS_reports.Z06.create_prac_ogolem_wmn + @GUS_reports.Z06.create_GMP_owner_wmn;
			SET @GUS_reports.Z06.create_GMP_ogolem_wmn = @GUS_reports.Z06.create_GMP_ogolem_wmn + @GUS_reports.Z06.create_GMP_owner_wmn; 	 
		END IF;		
	END IF;
		
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R01.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_prac_ogolem)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R01.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_prac_ogolem_wmn)); 
		
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R02.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_ogolem)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R02.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_ogolem_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R03.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_peln)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R03.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_peln_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R04.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_peln_um_okr)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R04.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_peln_um_okr_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R05.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_niepeln)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R05.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_niepeln_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R06.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_niepeln_um_okr)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R06.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_niepeln_um_okr_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R07.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_owner)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R07.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_owner_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R08.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_nakl)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R08.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_nakl_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R09.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_agent)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R09.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_agent_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R10.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_RSP)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R10.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_RSP_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R11.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_noc)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R11.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_noc_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R12.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_inw)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R12.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_inw_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R13.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_fgn)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R13.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_fgn_wmn)); 
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R14.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.GMP_prac_er)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R14.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.GMP_prac_er_wmn)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R15.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.GMP_telepraca)); 	 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R15.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.GMP_telepraca_wmn)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R16.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R16.K2=0'); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R17.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R17.K2=0'); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R18.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R18.K2=0'); 
	-- dodane od roku 2013:
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R19.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R19.K2=0'); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R20.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R20.K2=0'); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R21.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np4.R21.K2=0'); 
	 
	/* Dział 5. Przyjęcia do pracy i zwolnienia z pracy... */ 
	SET _SQLText =  
	'SELECT	
	  SUM(1) AS poprz_pelnozatr,
	  SUM(IF(!O.plec, 1, 0)) AS poprz_pelnozatr_wmn
	from stat_prac_#Rok# P
	join prac_gus G on G.guid_pracownika = P.GUID_prac and !G.PracaSezonowaDorywcza and G.GlowneMP
	join osoby O on O.guid = P.GUID_prac 
	where (P.Etat_L/P.Etat_M = 1) and  P.Data_pracy = ''#Rok#-12-31''
	INTO 
		@GUS_reports.Z06.create_poprz_pelnozatr,	@GUS_reports.Z06.create_poprz_pelnozatr_wmn
	';
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok - 1);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error); 
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R01.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_poprz_pelnozatr)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R01.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_poprz_pelnozatr_wmn)); 
	-- p5.R01.K1=21
    -- p5.R01.K2=2

	-- przyjeci (pełnozatrudnieni)
	SET _SQLText =  
	'SELECT
		SUM(1) AS D_5_2_1, SUM(IF(O.plec = 0, 1, 0)) AS D_5_2_2, 
		SUM(IF(G.PierwszaPraca = 1, 1, 0)) AS D_5_3_1, 
		SUM(IF((G.PierwszaPraca = 1) AND (O.plec = 0), 1, 0)) AS D_5_3_2, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 1), 1, 0)) AS D_5_4_1, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 1) AND (O.plec = 0), 1, 0)) AS D_5_4_2, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 2), 1, 0)) AS D_5_5_1, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 2) AND (O.plec = 0), 1, 0)) AS D_5_5_2, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 3), 1, 0)) AS D_5_6_1, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 3) AND (O.plec = 0), 1, 0)) AS D_5_6_2, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 4), 1, 0)) AS D_5_7_1, 
		SUM(IF((G.PierwszaPraca = 1) AND (G.JakiAbsolwent = 4) AND (O.plec = 0), 1, 0)) AS D_5_7_2, 
		SUM(IF((G.WczesniejPracowalDlaZ06 = 1), 1, 0)) AS D_5_8_1, 
		SUM(IF((G.WczesniejPracowalDlaZ06 = 1) AND (O.plec = 0), 1, 0)) AS D_5_8_2, 
		0 AS D_5_10_1, 0 AS D_5_10_2,
		SUM(IF((G.PierwszaPraca = 0) AND (G.WczesniejPracowalDlaZ06 = 0), 1, 0)) AS D_5_11_1, 
		SUM(IF((G.PierwszaPraca = 0) AND (G.WczesniejPracowalDlaZ06 = 0) AND (O.plec = 0), 1, 0)) AS D_5_11_2 		
	FROM Pracownicy P 
		JOIN prac_gus G ON G.guid_pracownika = P.GUID AND G.GlowneMP
		JOIN osoby O ON P.GUID = O.GUID 
		JOIN historia_pracy H ON P.GUID = H.GUIDPr AND H.OdDnia = 
       (SELECT MIN(h1.OdDnia) FROM historia_pracy h1 
       WHERE h1.guidpr=H.guidpr AND h1.OdDnia >= P.DataZatrudnieniaWzakl AND h1.zmiany LIKE "%zatr%") 
	WHERE  (H.Etat_L/H.Etat_M = 1)  
	  -- (P.Etat_L/P.Etat_M = 1) 
		  AND  P.DataZatrudnieniaWzakl BETWEEN ''#Rok#-01-01'' AND ''#Rok#-12-31''
	INTO
		@GUS_reports.Z06.create_D_5_2_1, 	@GUS_reports.Z06.create_D_5_2_2,
		@GUS_reports.Z06.create_D_5_3_1, 	@GUS_reports.Z06.create_D_5_3_2,
		@GUS_reports.Z06.create_D_5_4_1, 	@GUS_reports.Z06.create_D_5_4_2,
		@GUS_reports.Z06.create_D_5_5_1, 	@GUS_reports.Z06.create_D_5_5_2,
		@GUS_reports.Z06.create_D_5_6_1, 	@GUS_reports.Z06.create_D_5_6_2,
		@GUS_reports.Z06.create_D_5_7_1, 	@GUS_reports.Z06.create_D_5_7_2,
		@GUS_reports.Z06.create_D_5_8_1, 	@GUS_reports.Z06.create_D_5_8_2,
		@GUS_reports.Z06.create_D_5_10_1, 	@GUS_reports.Z06.create_D_5_10_2,
		@GUS_reports.Z06.create_D_5_11_1, 	@GUS_reports.Z06.create_D_5_11_2
	;';  -- 0 AS D_5_12_1, 0 AS D_5_12_2 
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R02.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_2_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R02.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_2_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R03.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_3_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R03.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_3_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R04.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_4_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R04.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_4_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R05.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_5_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R05.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_5_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R06.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_6_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R06.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_6_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R07.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_7_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R07.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_7_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R08.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_8_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R08.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_8_2)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R09.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R09.K2=0'); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R10.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_10_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R10.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_10_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R11.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_11_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R11.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_11_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R12.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R12.K2=0'); 
	
	-- zwolnieni (pełnozatrudnieni)
	-- SUM(IF(G.TytulZwolnienia IN (1, 2, 3, 4, 5, 6, 7), 1, 0)) AS D_5_13_1, SUM(IF(G.TytulZwolnienia IN (1,2,3,4, 5, 6, 7) AND (O.plec = 0), 1, 0)) AS D_5_13_2, 
	SET _SQLText =  
	'SELECT	
		SUM(1) AS D_5_13_1, SUM(IF((O.plec = 0), 1, 0)) AS D_5_13_2, 
		SUM(IF(G.TytulZwolnienia = 1, 1, 0)) AS D_5_14_1, 
		SUM(IF((G.TytulZwolnienia = 1) AND (O.plec = 0), 1, 0)) AS D_5_14_2, 
		SUM(IF(G.TytulZwolnienia = 1, 1, 0)) AS D_5_15_1, 
		SUM(IF((G.TytulZwolnienia = 1) AND (O.plec = 0), 1, 0)) AS D_5_15_2, 
		SUM(IF(G.TytulZwolnienia = 2, 1, 0)) AS D_5_16_1, 
		SUM(IF((G.TytulZwolnienia = 2) AND (O.plec = 0), 1, 0)) AS D_5_16_2, 
		SUM(IF(G.TytulZwolnienia = 3, 1, 0)) AS D_5_17_1, 
		SUM(IF((G.TytulZwolnienia = 3) AND (O.plec = 0), 1, 0)) AS D_5_17_2, 
		SUM(IF(G.TytulZwolnienia = 4, 1, 0)) AS D_5_18_1, 
		SUM(IF((G.TytulZwolnienia = 4) AND (O.plec = 0), 1, 0)) AS D_5_18_2, 
		SUM(IF(G.TytulZwolnienia = 5, 1, 0)) AS D_5_19_1, 
		SUM(IF((G.TytulZwolnienia = 5) AND (O.plec = 0), 1, 0)) AS D_5_19_2, 
		SUM(IF(G.TytulZwolnienia = 8, 1, 0)) AS D_5_21_1, 
		SUM(IF((G.TytulZwolnienia = 8) AND (O.plec = 0), 1, 0)) AS D_5_21_2, 
		SUM(IF(G.TytulZwolnienia IN (0,7), 1, 0)) AS D_5_22_1, 
		SUM(IF((G.TytulZwolnienia IN (0,7)) AND (O.plec = 0), 1, 0)) AS D_5_22_2 
	FROM Pracownicy P 
		JOIN prac_gus G ON G.guid_pracownika = P.GUID    AND G.GlowneMP
		JOIN osoby O ON P.GUID = O.GUID 
	WHERE (P.Etat_L/P.Etat_M = 1)  
		AND  P.DataZwolnienia BETWEEN ''#Rok#-01-01'' AND ''#Rok#-12-31''
	INTO
		@GUS_reports.Z06.create_D_5_13_1, 	@GUS_reports.Z06.create_D_5_13_2,
		@GUS_reports.Z06.create_D_5_14_1, 	@GUS_reports.Z06.create_D_5_14_2,
		@GUS_reports.Z06.create_D_5_15_1, 	@GUS_reports.Z06.create_D_5_15_2,
		@GUS_reports.Z06.create_D_5_16_1, 	@GUS_reports.Z06.create_D_5_16_2,
		@GUS_reports.Z06.create_D_5_17_1, 	@GUS_reports.Z06.create_D_5_17_2,
		@GUS_reports.Z06.create_D_5_18_1, 	@GUS_reports.Z06.create_D_5_18_2,
		@GUS_reports.Z06.create_D_5_19_1, 	@GUS_reports.Z06.create_D_5_19_2,
		@GUS_reports.Z06.create_D_5_21_1, 	@GUS_reports.Z06.create_D_5_21_2,
		@GUS_reports.Z06.create_D_5_22_1, 	@GUS_reports.Z06.create_D_5_22_2
	;		
	';
	/* 0 AS D_5_19_1, 0 AS D_5_19_2, 
		0 AS D_5_21_1, 0 AS D_5_21_2, */
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
/*	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R13.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_13_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R13.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_13_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R14.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_14_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R14.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_14_2));	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R15.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_15_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R15.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_15_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R16.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_16_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R16.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_16_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R17.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_17_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R17.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_17_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R18.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_18_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R18.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_18_2)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R19.K1=0'); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R19.K2=0'); */
	
	SET _SQLText =  
	'SELECT	
			 count(distinct f.PracGUID) as D_5_20_1
	from frekw f 
			join Pracownicy p on p.guid = f.pracguid 
			JOIN prac_gus G ON G.guid_pracownika = p.GUID  and G.GlowneMP
			join absencje a on a.frekwguid = f.guid 
	where f.rok=2010 and a.absencjado >= ''#Rok#-01-01'' and ((a.rodzaj=20 and (to_days(a.absencjado)-to_days(a.absencjaod)>90) ) )
			and p.etat_l/p.etat_m = 1 
	into		
		@GUS_reports.Z06.create_D_5_20_1		;
    ';
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	SET _SQLText =  
	'SELECT	
			 count(distinct f.PracGUID) as D_5_20_2
	from frekw f 
			join Pracownicy p on p.guid = f.pracguid 
			join osoby o on o.guid=f.pracguid
			JOIN prac_gus G ON G.guid_pracownika = p.GUID  and G.GlowneMP
			join absencje a on a.frekwguid = f.guid 
	where f.rok=2010 and a.absencjado >= ''#Rok#-01-01'' and ((a.rodzaj=20 and (to_days(a.absencjado)-to_days(a.absencjaod)>90) ) )
			and p.etat_l/p.etat_m = 1 and !o.plec
	INTO		
		@GUS_reports.Z06.create_D_5_20_2		;
    ';
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	IF @GUS_reports.Z06.create_D_5_20_1 > 0 THEN
	  SET @GUS_reports.Z06.create_D_5_13_1 = @GUS_reports.Z06.create_D_5_13_1 + @GUS_reports.Z06.create_D_5_20_1;
	END IF;
	
	IF @GUS_reports.Z06.create_D_5_20_2 > 0 THEN
	  SET @GUS_reports.Z06.create_D_5_13_2 = @GUS_reports.Z06.create_D_5_13_2 + @GUS_reports.Z06.create_D_5_20_2;
	END IF;
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R13.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_13_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R13.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_13_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R14.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_14_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R14.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_14_2));	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R15.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_15_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R15.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_15_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R16.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_16_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R16.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_16_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R17.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_17_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R17.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_17_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R18.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_18_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R18.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_18_2)); 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R19.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_19_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R19.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_19_2)); 
	
	-- urlopy wychow.
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R20.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_20_1)); 	 
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R20.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_20_2)); 
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R21.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_21_1)); 	  	  
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R21.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_21_2)); 	  
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R22.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_22_1)); 	-- z innych przyczyn
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R22.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_22_2));
	
	-- "dodatkowe informacje" (D5.23 i D5.24)
	SET _SQLText =  
	'SELECT	
		IFNULL(SUM(IF(H.Etat_L/H.Etat_M = 1, 1, 0)), 0) AS D5_23_1, IFNULL(SUM(IF( (O.plec = 0) AND (H.Etat_L/H.Etat_M = 1), 1, 0)), 0) AS D5_23_2,  
		IFNULL(SUM(IF(H.Etat_L/H.Etat_M < 1, 1, 0)), 0) AS D5_24_1, IFNULL(SUM(IF( (O.plec = 0) AND (H.Etat_L/H.Etat_M < 1), 1, 0)), 0) AS D5_24_2 
	FROM stat_prac_#Rok# S 
		JOIN prac_gus G ON G.guid_pracownika = S.guid_prac  
		JOIN osoby O ON S.guid_prac = O.GUID  
		JOIN historia_pracy H ON S.guid_prac = H.GUIDPr 
	WHERE (G.GlowneMP = 1) AND S.data_pracy = ''#Rok#-12-31'' AND H.OdDnia >= ''#Rok#-01-01'' 
		  AND H.OdDnia = (SELECT MAX(h_ostatni.OdDnia) FROM historia_pracy h_ostatni  
		   WHERE h_ostatni.guidpr=H.guidpr AND h_ostatni.zmiany LIKE ''%%etat%%'' AND h_ostatni.OdDnia <= S.data_pracy)
		  AND H.OdDnia <>  (SELECT MIN(h_pierwszy.OdDnia) FROM historia_pracy h_pierwszy  
		   WHERE h_pierwszy.guidpr=H.guidpr AND h_pierwszy.zmiany LIKE ''%%etat%%'' AND h_pierwszy.OdDnia <= S.data_pracy)
	INTO		
		@GUS_reports.Z06.create_D_5_23_1,	@GUS_reports.Z06.create_D_5_23_2,
		@GUS_reports.Z06.create_D_5_24_1,	@GUS_reports.Z06.create_D_5_24_2;	   
	';
	
	
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	CALL _ExecuteCmd(_SQLText, @Error);
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R23.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_23_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R23.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_23_2));
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R24.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_24_1)); 	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np5.R24.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_D_5_24_2));
	
	/* Dział 6. Pracujacy w miastach i gminach... */  
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R01.K1=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_ogolem)); 	-- = p4.R02.K1
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R01.K2=', _varSTR_NOTNULL(@GUS_reports.Z06.create_GMP_ogolem_wmn)); -- = p4.R02.K2
	
	SET @GUS_reports.Z06.create_D6_lp := 1;
	
	DROP TEMPORARY TABLE IF EXISTS `t_prac_gus`;
	SET _SQLText =  '
		CREATE TEMPORARY TABLE `t_prac_gus` 
				select  
					@GUS_reports.Z06.create_D6_lp:= @GUS_reports.Z06.create_D6_lp + 1 as lp,
					m.nazwa as nazwa_miejsc, if(m.miasto=0, 2, 1) as rodzaj_miejsc, m.symbolteryt, 
					sum(1) as ogolem, sum(if(o.plec=0, 1, 0)) as kobiety,
					g.MiejsceZatrGUID					
				from prac_gus g 
				join osoby o on o.guid = g.guid_pracownika 
				join gus_miejsca_zatr m on m.GUID = g.MiejsceZatrGUID   -- m.idn = g.miejscezatrudnienia 
				join stat_prac_#Rok# s on s.guid_prac = g.guid_pracownika and s.data_pracy = @GUS_reports.Z06.create_LastDay
				where g.GlowneMP
				group by m.symbol 	
	'	;   
	
	SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);		
	SET @_SQLText = _SQLText;
	-- SET @GUS_reports.Z06.create_RepContent = @GUS_reports.Z06_RepContent;
	--	Wykonaj
	CALL _ExecuteCmd(_SQLText, @Error);
	
	BEGIN
		DECLARE _nazwa, _mzGUID VARCHAR(50); 
		DECLARE _symbolteryt VARCHAR(20); 
		DECLARE _lp, _rodzaj_miejsc, _ogolem, _kobiety INTEGER;
		 
			
		DECLARE _done BOOL DEFAULT FALSE;
		DECLARE _c_proc CURSOR FOR			
				select  
					g.*
				from t_prac_gus g 
				;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;	
		
		OPEN _c_proc;	
			loop1: LOOP
				FETCH _c_proc INTO
						_lp, _nazwa, _rodzaj_miejsc, _symbolteryt, _ogolem, _kobiety, _mzGUID;
				IF _done THEN
						LEAVE loop1;
				ELSE
					-- SET @m.debug = _nazwa	;K0.A  R02.K0.A
					-- sprawdzamy, czy dla danego MZ nie ma osob z zarzadu nie bedacych na umowie o prace (jesli sa, to dodajemy do sumy):
					SET _SQLText =  
					'SELECT	
						ifnull(sum(IF((G.Wlasciciel or G.CzlonekRodzinyWlasc), 1, 0)), 0)   		   AS GMP_prac_wlasc, 
						ifnull(sum(if(O.plec=0 and (G.Wlasciciel or G.CzlonekRodzinyWlasc), 1, 0)), 0) AS GMP_prac_wlasc_kobiety		
					from Pracownicy P
					join prac_gus G on G.guid_pracownika = P.GUID and  G.GlowneMP
					join osoby O on P.GUID = O.GUID 
					join historia_pracy h on P.GUID = h.GUIDPr AND	h.OdDnia = (SELECT MAX(OdDnia) FROM historia_pracy WHERE OdDnia <= ''#Rok#-12-31'' AND GUIDPr = P.GUID )
					WHERE 
						(h.ToZleceniobiorca = 1) AND (h.ToPracownik = 0) AND (G.MiejsceZatrGUID = ''#mzGUID#'') AND
						EXISTS (SELECT * FROM prace_zlecone z WHERE z.GUID_pracownika = P.GUID AND z.rok = #Rok# AND z.miesiac = 12 and z.RodzajRyczaltu = 13 and z.deleted = 0)
					INTO 
						@GUS_reports.Z06.D6_owner, @GUS_reports.Z06.D6_owner_wmn
					';
					
					SET _SQLText = REPLACE(_SQLText, '#Rok#', _Rok);							
					SET _SQLText = REPLACE(_SQLText, '#mzGUID#', _mzGUID);							
					CALL _ExecuteCmd(_SQLText, @Error);
					
					IF @GUS_reports.Z06.D6_owner > 0 THEN
						SET _ogolem = _ogolem + @GUS_reports.Z06.D6_owner;
					    IF @GUS_reports.Z06.D6_owner_wmn > 0 THEN
							SET _kobiety = _kobiety + @GUS_reports.Z06.D6_owner_wmn;
						END IF;
					END IF;
										
					SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R0', _lp, '.K0.A=', _nazwa); 	
					SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R0', _lp, '.K0.C=', _rodzaj_miejsc); 
					SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R0', _lp, '.K0.D=', _symbolteryt); 	
					SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R0', _lp, '.K1=', _ogolem); 
					SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\np6.R0', _lp, '.K2=', _kobiety); 
				END IF;		
			END LOOP;
		CLOSE _c_proc;
		
	  -- CLOSE _c_proc;		
	END;
	-- DROP TEMPORARY TABLE IF EXISTS `t_prac_gus`;
	
	DROP TEMPORARY TABLE IF EXISTS tt_d1_liczba_zatr;	
	
	-- SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\npPODP.6=', ''); 
	
	SET @GUS_reports.Z06_RepContent = CONCAT(@GUS_reports.Z06_RepContent, '\n\n[Formularze]', '\nCount=1');
	
	-- zamiana parametrów na wartosci
	SET @GUS_reports.Z06_RepContent = REPLACE(@GUS_reports.Z06_RepContent, '#Rok#', _Rok);
	SET _RepContent = @GUS_reports.Z06_RepContent;
	-- SELECT _varSTR_NOTNULL(@GUS_reports.Z06_RepContent) AS _RepContent;
	
	
	
END;  
END IF; 	-- _MODE = 'Z06.create'

IF _MODE = 'Z06.create.get' THEN
-- BEGIN	
	SET @GUS_reports.Z06_RepContent = '';
	SET _RepSymbol = 'GZ06_18';  -- za 2018: 'GZ06_17';za 2017: 'GZ06_16'; za 2016: 'GZ06_15'; za 2015: 'GZ06_14'; za 2014: 'GZ06_13' 
	CALL GUS_reports('Z06.create', _RepSymbol, _Rok, _MiesStart, _MiesKoniec, @GUS_reports.Z06_RepContent);
	-- SET @exp.MaxaZmienna = IFNULL(@exp.MaxaZmienna, 0);
	-- SELECT _varSTR_NOTNULL(@GUS_reports.Z06_RepContent) AS _RepContent;
	SELECT @GUS_reports.Z06_RepContent AS _RepContent;
END IF; 	-- _MODE = 'Z06.create.get'

END$$

DELIMITER ;

