DELIMITER $$



DROP PROCEDURE IF EXISTS `pl_frekw_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pl_frekw_tools`(
	IN _MODE VARCHAR(150),
	IN _Key VARCHAR(255),	
	IN _Param_A VARCHAR(255),
	IN _Param_B VARCHAR(255),
	IN _Param_C TEXT,	
	IN _Param_D VARCHAR(255)
)
SPROC: BEGIN 
	/*
	_MODE

	------------------------------------------------------------------------------------------------------------------------------------------------------------

	------------------------------------------------------------------------------------------------------------------------------------------------------------
	*/
	DECLARE _SQLText TEXT DEFAULT '';
	DECLARE _PracGUID varchar(38);
	DECLARE _firma varchar(250);
	DECLARE _WHERE varchar(250);
	DECLARE _nadg, _NCP, _NCP_kodeks, _etat double;  -- _wolne_za_nadg,
	DECLARE  _I, _start, _counter, _msc, _ile, _days_in_month, _def, _dzien_P integer;
	DECLARE _Owner  VARCHAR(100);
	
	DECLARE _done BOOL DEFAULT FALSE;
		
	DECLARE _c_PracGUID CURSOR FOR
	SELECT 
		PracGUID
	FROM 
		t_frekw_automat;

	DECLARE _c_dni_P CURSOR FOR
	SELECT 
		dzien
	FROM 
		t_p;			
	
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
	
	/*  tego nie uzywamy a w dodatku brakuje daty !!!!
	DECLARE _DT_6_00 DATETIME DEFAULT '06:00:00';
	DECLARE _DT_5_59 DATETIME DEFAULT '05:59:59';
	DECLARE _DT_22_00 DATETIME DEFAULT '22:00:00';
	DECLARE _DT_21_59 DATETIME DEFAULT '21:59:59';
	*/

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.narast.frekw.automat'
	    naliczenie  kolumn `narast*` dla rownowaznego czasu pracy
		Parametry
			_Key		pusty
			_Param_A	rok
			_Param_B	miesiac
			_Param_C	pusty 
	CALL pl_frekw_tools('pl.nalicz.narast.frekw.automat', '', '2014', '1', '', '');			
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.narast.frekw.automat') THEN
	
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_automat`;
		CREATE TEMPORARY TABLE `t_frekw_automat`
		select 
				PracGUID, rok, miesiac
		from 
			frekw F
			LEFT JOIN historia_pracy H ON  H.GUIDPr = F.PracGUID AND H.OdDnia = (SELECT MAX(OdDnia) FROM historia_pracy WHERE GUIDPr = F.PracGUID AND 12*year(OdDnia)+month(OdDnia)+0.03*day(OdDnia) <= 12*F.rok + F.miesiac + 0.03*1) 
		where 
			F.rok = _varINT(_Param_A) 
			and F.miesiac = _varINT(_Param_B)
			and IFNULL(H.rodzaj_pracownika, 0) = 1  -- 1 = rownoważny CP
			and (F.deleted = 0) 	;
		
		SET @pl.nalicz.narast.ile = 0;	
		OPEN _c_PracGUID;
				loopA: LOOP
					FETCH _c_PracGUID INTO
						_PracGUID;
					IF _done THEN
						LEAVE loopA;
					ELSE
					  SET @pl.nalicz.narast.ile = @pl.nalicz.narast.ile + 1;
					  CALL pl_frekw_tools('pl.nalicz.narast.frekw', _PracGUID, _Param_A, _Param_B, '', '');
					  SET _done = FALSE;	
					END IF;
				END LOOP;
		CLOSE _c_PracGUID;			

	END IF;
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.narast.frekw'
	    naliczenie  kolumn `narast*` dla rownowaznego czasu pracy
		Parametry
			_Key		GUID pracownika
			_Param_A	rok
			_Param_B	miesiac
			_Param_C	pusty lub 'FORCE' (gdy chcemy wymusić naliczenie nadgodzin)
	CALL pl_frekw_tools('pl.nalicz.narast.frekw', '', '2014', '1', '', '');		
	CALL pl_frekw_tools('pl.nalicz.narast.frekw', '', '2014', '1', '', 'FORCE');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.narast.frekw') THEN
	
	    -- okreslamy, w ktorym miesiacu okresu rozl. jestesmy
		SELECT _Param_B MOD @session.globals.pl.dlugosc.okresu.rozl INTO _counter;
		-- okreslamy miesiac poczatkowy okresu rozl., w ktorym zawiera się dany miesiac (_Param_B) SET _start = _Param_B - _counter + IF(_counter > 0, 1, 0); 
		SET _start = _Param_B - IF(_counter > 0, _counter, @session.globals.pl.dlugosc.okresu.rozl) + 1; 
		
		SET @pl.frekw.narast.start =  _start;
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_GUID`;
		CREATE TEMPORARY TABLE `t_frekw_GUID`
		select 
			GUID, PracGUID, miesiac  
		from 
			frekw 
		where 
			PracGUID = _Key
			and rok = _Param_A			
			and miesiac BETWEEN _start AND _Param_B   -- od poczatku okresu rozliczeniowego do biezacego miesiaca wlacznie
			and deleted = 0
	    ;
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_narast`;
		CREATE TEMPORARY TABLE `t_frekw_narast`
		SELECT
					n.PracGUID,
					-- SUM(p.godziny - p.godziny150 - p.godziny200) AS godz100prac, 
					-- SUM(p.godziny_prod) AS godz100prac_prod, 
					-- SUM(p.godziny150) AS godz_plus50prac,
					-- SUM(p.godziny200) AS godz_plus100wolne,
					-- SUM(IF(absencja in ('','','E'), p.kp_godziny, IF(absencja IN ('H', 'Q'), p.kp_godziny - p.godz_absencji, 0))) AS narast_NCP_kalend,  -- -- SUM(IF(absencja in ('','F','E'), p.kp_godziny, 0)) AS Sumakp_godziny,
					SUM(IF(absencja in ('','H', 'Q','E'), p.kp_godziny, 0)) AS narast_NCP_kalend,  -- -- SUM(IF(absencja in ('','F','E'), p.kp_godziny, 0)) AS Sumakp_godziny,
					-- SUM(IF(absencja NOT in ('','H', 'Q', 'E'), p.kp_godziny, IF(absencja IN ('H', 'Q'), p.godz_absencji, 0))) AS narast_godz_abs,  
					SUM(IF(absencja NOT in ('','H', 'Q', 'E'), p.kp_godziny,  0)) AS narast_godz_abs,  
					SUM(p.godziny) AS narast_godz
					-- SUM(p.noc) AS SumaGodzNocnych,
					-- SUM(IF(absencja in ('','F','E'), p.kp_godziny, 0)) AS Sumakp_godziny,
					-- SUM(p.dni_absencji) AS Sum_Dni_absencji,
					-- SUM(IF(absencja = 'W', p.dni_absencji, 0)) AS Sum_Dni_absencji_W
				FROM `t_frekw_GUID` n 
				INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID 																
				GROUP BY n.PracGUID
			;
		
		/*
		 aNomCzasPracy := RegistryInt.GetRegIntValueDouble_R(DBPlacesKadryPlaceUpodobania.AktywnyRok,
            Format('NCP%d',[shlEPlace.CDS.FieldByName('miesiac').asInteger]),
          168.00, 'Place2003\Parametry_ogolne', 1)
		  
		 tu wyliczyc sume i wstawic do  narast_NCP_kodeks
		*/
		SET _nadg = 0.00; -- SET _wolne_za_nadg = 0.00;
		SET _NCP  = 0.00; SET _NCP_kodeks  = 0.00;
		SET _etat = 0.00;
		
		-- jesli pracownik byl zatrudniony pozniej niz miesiac "startowy", to zaczynamy od miesiaca zatrudnienia
		SET _I = GREATEST(_start, (SELECT MIN(miesiac) FROM t_frekw_GUID) ); -- _start; -- _Param_B - _counter + IF(_counter > 0, 1, 0);
		-- SET _I = GREATEST(_start, SELECT MIN(miesiac) FROM t_frekw_GUID)
		SET @pl.frekw.narast.start =  _I;
		
		WHILE _I <= _Param_B DO
		    -- jesli to jest pierwszy miesiac pracy, to szukamy hist.pracy z OdDnia <= DataRozpoczAktUmowy, w przeciwnym wypadku  OdDnia <= concat_ws('-', _Param_A, _I, '01')
		
		    select 
				h.etat_L/h.etat_M AS etat 
			from historia_pracy h, Pracownicy p 
			where 
				h.GUIDpr = _Key and h.GUIDpr = p.guid and h.OdDnia <= GREATEST(concat_ws('-', _Param_A, _I, '01'), p.DataRozpoczAktUmowy) order by OdDnia desc limit 1
			INTO 
				_etat; 
		
			CALL _IReg_Get('Place2003\\Parametry_ogolne', CONCAT('NCP', _VarSTR(_I)) , _VarINT(_Param_A), 'FLOAT', _nadg, 0);		
			SET _nadg = _nadg * 60;
			IF _etat <> 1.0000 THEN
			  SET _nadg = _Rnd2(_nadg * _etat);
			END IF;
			
			SET @pl.frekw._etat =  _etat;
			
			SET _NCP_kodeks = _NCP_kodeks + _nadg;
																								
			-- spr. czy w biezacym miesiacu nie ma zwolnienia w jego trakcie
			SET _ile = 0;
			IF _I = _Param_B THEN
			  SELECT 
			    COUNT(*) AS ile FROM frekw_po WHERE absencja = '-' and year(rok_msc_dzien) = _Param_A and month(rok_msc_dzien) = _I and PracGUID = _Key
			  INTO	
			  _ile;
			  IF _ile > 0 THEN
			    select MAX(dzien) FROM frekw_po WHERE year(rok_msc_dzien) = _Param_A and month(rok_msc_dzien) = _I and PracGUID = _Key
			    INTO	_days_in_month;
			    SET _nadg = _Rnd2(_nadg * ((_days_in_month - _ile)/_days_in_month) );
			  END IF;
			END IF;
			
			SET _NCP = _NCP + _nadg;
			
			SET _I = _I + 1;
		END WHILE;
		
		SET @pl.frekw._NCP_kodeks =  _NCP_kodeks;
		-- SET @pl.frekw._narast_NCP_kalend =  _narast_NCP_kalend;
		
		/* -- zliczamy godziny absencji "wolne za nadgodziny"  (H, Q)
		SELECT IFNULL(SUM(godz_absencji), 0) AS wolne_za_nadg
		FROM absencje a, t_frekw_GUID f
		WHERE a.guidpr = _Key AND a.frekwguid  = f.guid
		AND a.rodzaj IN (23, 24)
		INTO _wolne_za_nadg;  */
		
		UPDATE frekw f, t_frekw_narast n SET
			f.SaldoNarastGodzNadrNiedPocz = 0,   -- zerujemy  kolumne ze standardowym naliczaniem narast. salda nadgodzin (zamiast tego sa kolumny narast_nadgodz*)
			f.narast_NCP_kodeks_bez_zmn = _NCP_kodeks,
			f.narast_NCP_kodeks = _NCP_kodeks - n.narast_godz_abs,  
 			f.narast_NCP_kalend = n.narast_NCP_kalend, 
			f.narast_godz       = n.narast_godz,
			-- f.narast_nadgodz_kodeks = IF(n.narast_godz > (_NCP_kodeks - n.narast_godz_abs + _wolne_za_nadg), n.narast_godz - (_NCP_kodeks - n.narast_godz_abs) - _wolne_za_nadg, 0),
			-- f.narast_nadgodz_kalend = IF(n.narast_godz > (n.narast_NCP_kalend + _wolne_za_nadg), n.narast_godz - n.narast_NCP_kalend - _wolne_za_nadg, 0),
			
			f.narast_nadgodz_kodeks = IF(n.narast_godz > (_NCP_kodeks - n.narast_godz_abs), n.narast_godz - (_NCP_kodeks - n.narast_godz_abs), 0),
			f.narast_nadgodz_kalend = IF(n.narast_godz > n.narast_NCP_kalend, n.narast_godz - n.narast_NCP_kalend, 0),
			
			f.godz100prac = (SELECT SUM(IF(p.godziny <= p.kp_godziny, p.godziny, p.kp_godziny)) FROM frekw_po p WHERE p.frekwGUID = f.GUID)
		WHERE	
			f.PracGUID = _Key	
			AND f.PracGUID = n.PracGUID			
			and f.rok =     _Param_A
			and f.miesiac = _Param_B
			AND NOT f.Deleted 
			AND NOT f.Zamkniety;
	
		-- gdy koniec okresu rozl. lub wymuszono bilansowanie godzin, 
		-- wówczas okreslamy nadgodziny 
		IF (_counter = 0) OR (_Param_C	= 'FORCE')  THEN
		  SET _I = 0;
		  SET @pl.frekw.NCP = _NCP; SET _def = 1;  -- nadgodziny+100
		  
		  set _firma = '';
		  SELECT `value`  AS firma 
			FROM _db_globals WHERE `key` = 'owner'
		  INTO _firma;
		  IF   _firma = 'SECURITY' THEN
		    SET _def = 0;
		  END IF;	
		  CALL _IReg_Get('Place2003\\Parametry_ogolne', 'nadgodziny_jako_100', 0, 'INTEGER', _I, _def);		
		  IF _I = 1  THEN
			  UPDATE frekw n SET
				n.godz_plus100wolne = n.narast_nadgodz_kodeks, -- IF(n.narast_godz > (_NCP - n.narast_godz_abs), n.narast_godz - (_NCP - n.narast_godz_abs), 0),
				n.godz_plus50prac = 0.000				
			  WHERE	
				n.PracGUID = _Key					
				AND NOT n.Deleted 
				AND NOT n.Zamkniety
				and n.rok =     _Param_A
				and n.miesiac = _Param_B
				;
		  ELSE		
		    UPDATE frekw n  SET
				n.godz_plus100wolne = 0.000,
				n.godz_plus50prac = n.narast_nadgodz_kodeks -- IF(n.narast_godz > _NCP, n.narast_godz - _NCP, 0)
				-- ,  n.godz100prac = (SELECT SUM(IF(p.godziny <= p.kp_godziny, p.godziny, p.kp_godziny)) FROM frekw_po p WHERE p.frekwGUID = n.GUID)
			  WHERE	
				n.PracGUID = _Key					
				AND NOT n.Deleted 
				AND NOT n.Zamkniety
				and n.rok =     _Param_A
				and n.miesiac = _Param_B
		    ;
		  END IF;
		END IF;
			
	END IF;	--	IF _MODE = 'pl.nalicz.narast.frekw'
	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'zest.nadgodz.narast'
	Zestawienie nadgodzin (w rozbiciu na poszczególne miesiące) dla wskazanego roku
		Parametry
			_Key		nieuzywany
			_Param_A    Rok
			_Param_B    nieuzywany
			_Param_C	nieuzywany
			_Param_D    nieuzywany
			
		
	CALL pl_frekw_tools('zest.nadgodz.narast', '', '2013', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'zest.nadgodz.narast') THEN
	
	-- DROP IF NOT EXISTS TEMPORARY TABLE t_prac_nadg_narast;
	
	CREATE TEMPORARY TABLE IF NOT EXISTS t_prac_nadg_narast
	(
	 PracGUID VARCHAR(38)  NOT NULL DEFAULT '', 
	 nadg01   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg02   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg03   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg04   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg05   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg06   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg07   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg08   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg09   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg10   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg11   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg12   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 nadg_rok DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
	 KEY      PracGUID(PracGUID)
	 );
	 
		TRUNCATE t_prac_nadg_narast;
 
	-- DELETE FROM t_prac_nadg_narast;

		INSERT INTO t_prac_nadg_narast (PracGUID)
		SELECT DISTINCT PracGUID
		FROM frekw WHERE rok = _Param_A AND deleted = 0 AND SaldoNarastGodzNadrNiedPocz > 0;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg01 = godz_plus50prac + godz_plus100wolne
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 1;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg02 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 2;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg03 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 3;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg04 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 4;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg05 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 5;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg06 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 6;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg07 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 7;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg08 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 8;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg09 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 9;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg10 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 10;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg11 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 11;

		UPDATE t_prac_nadg_narast z, frekw f
		SET nadg12 = (godz_plus50prac + godz_plus100wolne)
		WHERE z.PracGUID = f.PracGUID AND (godz_plus50prac + godz_plus100wolne) > 0 AND rok = _Param_A AND miesiac = 12;

		UPDATE t_prac_nadg_narast 
		SET nadg_rok = nadg01 + nadg02 + nadg03 + nadg04 + nadg05 + nadg06 + nadg07 + nadg08 + 
					   nadg09 + nadg10 + nadg11 + nadg12;


		-- dane są przygotowane do pobrania
		SELECT 
			o.nazwisko, o.imiona, z.* 
		FROM 
			t_prac_nadg_narast z, osoby o
		WHERE 
			o.guid = z.PracGUID
		ORDER BY o.nazwisko, o.imiona;
	END IF;	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'zlicz.nadgodz.narast.total'
	Przeliczenie sumy nadgodzin (narastajaco) w rekordach frekw dla okreslonego pracownika
		Parametry
			_Key		GUID pracownika
			_Param_A    Rok
			_Param_B    Miesiac
			_Param_C	nieuzywany
			_Param_D    nieuzywany
			
		
	CALL pl_frekw_tools('zlicz.nadgodz.narast.total', 'Prac1', '2013', '9', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'zlicz.nadgodz.narast.total') THEN
	
		set _msc = 0;
					
		select max(miesiac) from frekw
		where PracGUID = _Key and rok = _varInt(_Param_A)
		and deleted = 0
		into _msc;
		
		SET _counter = _varInt(_Param_B);  -- zaczynamy od miesiąca, który jest przetwarzany
		
		-- przeliczamy kolejne miesiące
		repeat
			CALL pl_frekw_tools('zlicz.nadgodz.narast.jeden.mc', _Key, _Param_A, _VarStr(_counter), '', '');
			SET  _counter = _counter + 1;
		UNTIL _counter > _msc   
		END REPEAT;
		
	END IF;	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'zlicz.nadgodz.narast.jeden.mc'
	Przeliczenie sumy nadgodzin (narastajaco) w rekordzie frekwencji dla okreslonego pracownika
		Parametry
			_Key		GUID pracownika
			_Param_A    Rok
			_Param_B    Miesiac
			_Param_C	nieuzywany
			_Param_D    nieuzywany
			
		
	CALL pl_frekw_tools('zlicz.nadgodz.narast.jeden.mc'', 'Prac1', '2013', '9', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'zlicz.nadgodz.narast.jeden.mc') THEN
										
		set _nadg = 0.00;
				
		select 
			SUM(godz_plus50prac + godz_plus100wolne) as nadg 
		from 
			frekw
		where
            PracGUID = 	_Key and rok = _varInt(_Param_A) and (miesiac between 1 and _varInt(_Param_B)) and deleted = 0
		into
			_nadg
	    ;

		update 
			frekw 	
		set 
			SaldoNarastGodzNadrNiedPocz =  _nadg
		where PracGUID = _Key and rok = _varInt(_Param_A) and miesiac = _varInt(_Param_B) and deleted = 0;
					
	END IF;	
	
		
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'zlicz.okres.absencji'
	Wylicza liczbe dni i godzin nieobecnosci dla podanego okresu (f. analogiczna do C_Dysc.ZliczDniAbsencji)
		Parametry
			_Key		GUID pracownika
			_Param_A    Data od
			_Param_B    Data do
			_Param_C	nieuzywany
			_Param_D    rodzaj nieobecności
			
		
	CALL samba.snprod.pl_frekw_tools('zlicz.okres.absencji', '0240', '2010-12-13', '2010-12-15', '', 'W');	
	CALL samba.snprod.pl_frekw_tools('zlicz.okres.absencji', '{04786798-1F5C-4CE6-8756-D836316CD9F9}', '2010-12-20', '2010-12-20', '', 'H');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'zlicz.okres.absencji') THEN
	
		set _firma = '';
		SELECT `value`  AS firma FROM _db_globals WHERE `key` = 'owner'
		INTO _firma;
				
		CALL _Session_Refresh_Variables('PL', '', '');
		
		-- bazujemy na zalozeniu, ze dla wskazanego okresu nieobecnosci istnieja wpisy we frekw_po
		
		IF (_Param_D = 'Q') OR (_Param_D = 'V') OR (_Param_D = 'H' AND _firma <> 'CDG') THEN  
			-- urlopy godzinowe w ramach jednego dnia
			SELECT 
			   _varInt(0)  AS dni, 
			   godz_absencji/@FrekwPrec.MinFact as godziny 
			from 
				frekw_po 
			WHERE absencja = _Param_D AND rok_msc_dzien = _Param_A ;
		ELSE
			SELECT 
				SUM(IF(absencja = _Param_D, 1, 0) ) AS dni, 
				SUM(IF(kp_godziny>0 AND godziny=0, kp_godziny, 0) )/@FrekwPrec.MinFact AS godziny
				-- ... /@FrekwPrec.MinFact  =  if not DBPlacesKadryPlaceUpodobania.DBTimeInMinutes then aDelta := aDelta div 60;
			FROM 
				frekw_po
			WHERE  
				PracGUID = _Key AND rok_msc_dzien BETWEEN _Param_A AND _Param_B;
		END IF;	
	END IF;	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.frekw.absencje.mies'
	Wylicza zestawienie absencji do dolnego panelu kartoteki frekw.
		Parametry
			_Key		GUID zapisu frekw.
					
	CALL pl_frekw_tools('pl.frekw.absencje.mies', @GUIDfrekw, '', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.frekw.absencje.mies') THEN
		-- SET _Param_A = IFNULL(_Param_A, '');
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;	--	nie podano GUIDa zapisu - przerywamy pracę 
		END IF;
			
		TRUNCATE t_frekw_dp_abs;	
			
		-- wstawiamy rekordy "akt." i "poprz."
		INSERT INTO  t_frekw_dp_abs
			SELECT 'akt.' AS okres, 
			SUM(IF(absencja='C', 1, 0)) AS C, SUM(IF(absencja='W', 1, 0)) AS W,
			SUM(IF(absencja='G', 1, 0)) AS G, SUM(IF(absencja='B', 1, 0)) AS B,
			SUM(IF(absencja='L', 1, 0)) AS L, SUM(IF(absencja='K', 1, 0)) AS K,
			SUM(IF(absencja='M', 1, 0)) AS M, SUM(IF(absencja='P', 1, 0)) AS P,
			SUM(IF(absencja='S', 1, 0)) AS S, SUM(IF(absencja='O', 1, 0)) AS O,
			SUM(IF(absencja='R', 1, 0)) AS R, SUM(IF(absencja='N', 1, 0)) AS N,
			-- SUM(IF(absencja='I', 1, 0)) AS I, SUM(IF(absencja='D', 1, 0)) AS D,
			_varInt(I_godz) AS I, SUM(IF(absencja='D', 1, 0)) AS D,
			SUM(IF(absencja='Z', 1, 0)) AS Z, SUM(IF(absencja='T', 1, 0)) AS T,
			SUM(IF(absencja='A', 1, 0)) AS A, SUM(IF(absencja='E', 1, 0)) AS E,
			SUM(IF(absencja='F', 1, 0)) AS F, SUM(IF(absencja='Y', 1, 0)) AS Y,
			SUM(IF(absencja='J', 1, 0)) AS J, SUM(IF(absencja='U', 1, 0)) AS U
		FROM  frekw h, frekw_po  f
		WHERE f.FrekwGUID = h.guid  AND h.guid =  _Key  -- '{741C1192-A77B-4085-85E3-E8E7F1988073}'  
			-- GROUP BY h.guid;
		UNION
			SELECT  
			'poprz.' AS okres, 
			_varInt(0) AS C, _varInt(0) AS W, _varInt(0) AS G, _varInt(0) AS B, _varInt(0) AS L,
			_varInt(0) AS K, _varInt(0) AS M, _varInt(0) AS P, _varInt(0) AS S, _varInt(0) AS O,
			_varInt(0) AS R, _varInt(0) AS N, _varInt(0) AS I, _varInt(0) AS D, _varInt(0) AS Z,
			_varInt(0) AS T, _varInt(0) AS A, _varInt(0) AS E, _varInt(0) AS F, _varInt(0) AS Y, 
			_varInt(0) AS J, _varInt(0) AS U ;
					
		-- 	LEAVE SPROC;	--	dla TESTÓW !!!!
		
		
		BEGIN
			DECLARE _Symbol VARCHAR(2);
			DECLARE _Dni INTEGER;
			
			DECLARE _done BOOL DEFAULT FALSE;
			
			DECLARE _c_proc CURSOR FOR
				SELECT d.symbol, IF(rodzaj IN (1, 11, 4, 5, 6, 10, 15, 18, 20, 21, 22),  -- # a.AbsencjaOd, a.Absencjado, 
					TO_DAYS(IF(12*YEAR(AbsencjaDo) + MONTH(AbsencjaDo) < (12*h.rok + h.miesiac), AbsencjaDo, DATE_SUB(AbsencjaDo, INTERVAL DAY(AbsencjaDo) DAY) )) -
					TO_DAYS(AbsencjaOd)+1, 
				     -- # absencje nieciagle
					(SELECT SUM(IF(p.kp_godziny > 0, 1, 0))  FROM frekw_po p WHERE p.pracguid = h.pracguid 
					  AND p.rok_msc_dzien BETWEEN a.AbsencjaOd AND a.Absencjado )
				) AS dni 
			FROM absencje a, frekw h, absencje_dict d  
			WHERE  h.guid = _Key AND a.FrekwGUID = h.guid  AND a.PoprzOkres=1 AND d.idn=a.rodzaj;   -- h.guid = '{741C1192-A77B-4085-85E3-E8E7F1988073}'
				
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
			
			--  pobieramy wszystkie absencje z poprz. okresu i robimy updaty na tabeli (where okres='poprz.')
			OPEN _c_proc;
				loop1: LOOP
					FETCH _c_proc INTO
						_Symbol, _Dni;
					IF _done THEN
						LEAVE loop1;
					ELSE						
						-- UPDATE C = C + _dni where okres = 'poprz.'
						
						SET _SQLText =  
						'UPDATE  t_frekw_dp_abs
						SET #symbol# = #symbol# + #dni#
						WHERE	okres	= ''poprz.''  ';
						SET _SQLText = REPLACE(_SQLText, '#symbol#', _Symbol);	
						SET _SQLText = REPLACE(_SQLText, '#dni#',    _VarStr(_Dni));	
						-- SET @m._SQLText = _SQLText;
						CALL _ExecuteCmd(_SQLText, @Error);
						
					END IF;
				END LOOP;
			CLOSE _c_proc;
		END;

		select * from t_frekw_dp_abs;   -- zwracamy wynik naszych wyliczen (zapytanie to trafia jako CommandText do CDSa)
						
	END IF;	--	IF _MODE = 'pl.frekw.absencje.mies'	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.mc.frekw'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik gdy '' to wszystkich
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011)
			_Param_C	WHERE może być ''
	CALL pl_frekw_tools('pl.nalicz.mc.frekw', '', '201104', '', '', '');	
	CALL pl_frekw_tools('pl.nalicz.mc.frekw', @GUIDPrac, '201104', '', '', '');	
	CALL pl_frekw_tools('pl.nalicz.mc.frekw', @GUIDPrac, '201104', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.mc.frekw') THEN
		SET _Param_A = IFNULL(_Param_A, '');
		IF _Param_A = '' THEN
			LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw`;
		CREATE TEMPORARY TABLE `t_dataset_calc_frekw` 
			SELECT
				e.OsobaGUID, _varInt(_Param_A) AS YearMonth
			FROM `hr_events` e
			WHERE	
				-- ((_var_DateToYM(e.StartTime ) = _Param_A) OR (_var_DateToYM(e.EndTime ) = _Param_A))
				((YEAR(e.StartTime)*100 + MONTH(e.StartTime) = _Param_A) OR (YEAR(e.EndTime)*100 + MONTH(e.EndTime) = _Param_A))
				AND IF(_Key != '', e.OsobaGUID = _Key, TRUE)
				AND e.OsobaGUID != ''
				AND e.event_type = 'WORK'
				AND e.Deleted = 0
				AND duration_hour <= 24 
			GROUP BY e.OsobaGUID	
		;	
			
		DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw_cursor`;
		SET _SQLText = 
		'CREATE TEMPORARY TABLE `t_dataset_calc_frekw_cursor` 
			SELECT
				d.OsobaGUID, d.YearMonth
			FROM `t_dataset_calc_frekw` d
			INNER JOIN `Pracownicy` p ON p.GUID = d.OsobaGUID
		'	
		;	
		SET _Param_C = IF(_Key != '', '', IFNULL(_Param_C, ''));	--	Gdy _Key <> '' to dla konkretnej osoby zatem WHERE jest nieistotny!!!

		IF _Param_C <> '' THEN 
--			SET _SQLText = REPLACE(_SQLText, '#WHERE#', _PARAM_C);
			SET _SQLText = CONCAT(_SQLText, ' WHERE ', _PARAM_C);  
		END IF;

		SET _SQLText = CONCAT(_SQLText,';');  

		SET @_SQLText = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
		
		-- 	LEAVE SPROC;	--	dla TESTÓW !!!!
		DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw`;
		
		BEGIN
			DECLARE _OsobaGUID VARCHAR(38);
			DECLARE _YearMonth INTEGER;
			DECLARE _EndTime DATETIME;
			DECLARE _done BOOL DEFAULT FALSE;
			DECLARE _c_proc CURSOR FOR
				SELECT 
					OsobaGUID, YearMonth
				FROM `t_dataset_calc_frekw_cursor`
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
			
			-- SET @pl.frekw.counter = 0;
			OPEN _c_proc;
				loop1: LOOP
					FETCH _c_proc INTO
						_OsobaGUID, _YearMonth;
					IF _done THEN
						LEAVE loop1;
					ELSE
					   -- set @pl.frekw.PracGUID = _OsobaGUID;
					    
						CALL pl_frekw_tools('pl.nalicz.mc.frekw.one.prac', _OsobaGUID, _YearMonth, '', '', '');	
						/* #034080  ustawiamy done na False zerując ewentualne ustawienie go na TRUE przez zapytania, 
									które mogły wywołać NOT FOUND; jeśli był to ostatni rekord z kursora, to FETCH
									wywoła  NOT FOUND i ustawi _done = TRUE;
						*/
						set _done = FALSE;  						
			--			SET @pl.frekw.counter = @pl.frekw.counter + 1;
					END IF;
				END LOOP;
			CLOSE _c_proc;
		END;			
		DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw_cursor`;
		
		CALL pl_frekw_tools('pl.nalicz.sumy.naglowka', _Key, _Param_A, '', '', '');
	END IF;	--	IF _MODE = 'pl.nalicz.mc.frekw'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.create.hr_changed_frekw'
	Kreuj `t_hr_changed_frekw`
		Parametry
		Brak
	CALL pl_frekw_tools('pl.create.hr_changed_frekw', '', '', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.create.hr_changed_frekw') THEN
	
		DROP TEMPORARY TABLE IF EXISTS `t_hr_changed_frekw`;
		CREATE TEMPORARY TABLE `t_hr_changed_frekw`
			SELECT 
				f.GUID AS FrekwGUID, 
				cf.PracGUID,
				cf.Rok,
				cf.Miesiac,
				_VarInt(100* cf.Rok + cf.Miesiac) AS YearMonth,
				_VarInt(1) AS Checked
			FROM `hr_changed_frekw` cf
			INNER JOIN `frekw` f ON f.PracGUID = cf.PracGUID AND f.Rok = cf.Rok AND f.Miesiac = cf.Miesiac AND f.localtime < cf.last_calculated 
			WHERE
				cf.last_calculated > '0000=00=00'
				AND NOT f.Deleted
		;
		
	END IF;	--	IF _MODE = 'pl.create.hr_changed_frekw'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.select.hr_changed_frekw'
	Kreuj i pobierz `t_hr_changed_frekw`
		Parametry
		Brak
	CALL pl_frekw_tools('pl.select.hr_changed_frekw', '', '', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.select.hr_changed_frekw') THEN
	--	Kreuj `t_hr_changed_frekw`
		CALL pl_frekw_tools('pl.create.hr_changed_frekw', '', '', '', '', '');	
	--	Select	
		SELECT
			_VarStr(CONCAT(o.Nazwisko, ' ' , o.Imiona)) AS NazwiskoImiona,
			s.DisplayValue,
			tf.*
		FROM `t_hr_changed_frekw` tf
		INNER JOIN `osoby` o ON o.GUID = tf.PracGUID
		INNER JOIN `slowniki_pierwotne` s ON s.Slownik_GUID = 'MIESIACE' AND s.Value = tf.Miesiac
		;
		
	END IF;	--	IF _MODE = 'pl.select.hr_changed_frekw'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.mc.frekw.z.hr_changed_frekw'
	Wstaw z crm-a
		Parametry
		Brak
	CALL pl_frekw_tools('pl.nalicz.mc.frekw.z.hr_changed_frekw', '', '', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.mc.frekw.z.hr_changed_frekw') THEN
	
	--	Kreuj `t_hr_changed_frekw`
		CALL pl_frekw_tools('pl.create.hr_changed_frekw', '', '', '', '', '');	
		
		BEGIN
			DECLARE _OsobaGUID VARCHAR(38);
			DECLARE _YearMonth INTEGER;
			DECLARE _EndTime DATETIME;
			DECLARE _done BOOL DEFAULT FALSE;
			DECLARE _c_proc CURSOR FOR
				SELECT 
					PracGUID AS OsobaGUID, YearMonth
				FROM `t_hr_changed_frekw`
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
			OPEN _c_proc;
				loop2: LOOP
					FETCH _c_proc INTO
						_OsobaGUID, _YearMonth;
					IF _done THEN
						LEAVE loop2;
					ELSE
						CALL pl_frekw_tools('pl.nalicz.mc.frekw.one.prac', _OsobaGUID, _YearMonth, '', '', '');	
						
					END IF;
				END LOOP;
			CLOSE _c_proc;
		END;			
		
		CALL pl_frekw_tools('pl.nalicz.sumy.naglowka', '', '', '1', '', '');	-- _Param_B <> '' !!!!!!!!!!
		DROP TEMPORARY TABLE IF EXISTS `t_hr_changed_frekw`;
		-- xxxxxxx
	END IF;	--	IF _MODE = 'pl.nalicz.mc.frekw.z.hr_changed_frekw'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.mc.frekw.one.prac'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011)
						gdy '' to???
	CALL pl_frekw_tools('pl.nalicz.mc.frekw.one.prac', @GUIDPrac, '201104', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.mc.frekw.one.prac') THEN
		SET _Param_A = IFNULL(_Param_A, '');
		IF _Param_A = '' THEN
			LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
		END IF;
		
		SET @pl.frekw.GUID = '';
		
		SELECT
			n.GUID, kategoria
		FROM `frekw` n
		WHERE
			n.PracGUID = _Key AND (n.Rok*100 + n.Miesiac) = _Param_A
			AND NOT n.Deleted
			AND NOT n.Zamkniety
		LIMIT 1	
		INTO
			@pl.frekw.GUID, _def
		;
		SET @pl.frekw.GUID = IFNULL(@pl.frekw.GUID, '');
		IF @pl.frekw.GUID = '' THEN
			LEAVE SPROC;	--	wychodze bo nie mam GUID-a
		END IF;
		
		SET @bylem = '0';
		CALL pl_frekw_tools('pl.tool.update.absencje.F', @pl.frekw.GUID, '', '', '', '');		
		/*
		CALL pl_frekw_tools('pl.tool.update.Fki', _Key, _Param_A, '', '', '');			-- dla miesiaca
		*/
		
		SET @bylem = '1.';
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_po_one_prac`;
		CREATE TEMPORARY TABLE `t_frekw_po_one_prac` -- !!!!! PO CO TA TABELA ??? :) :)
			SELECT
				p.*
			FROM `frekw` n
			INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
			WHERE
				n.GUID = @pl.frekw.GUID
				/*
				n.PracGUID = _Key AND (n.Rok*100 + n.Miesiac) = _Param_A
				AND NOT n.Deleted
				AND NOT n.Zamkniety
				*/
		;	
		
		DROP TEMPORARY TABLE IF EXISTS `t_pracownika_workevents`;
		CREATE TEMPORARY TABLE `t_pracownika_workevents` 
			SELECT
				e.GUID, 
				_varInt(_Param_A) AS YearMonth,
				-- uzywana ? _varDouble(0.0) AS duration_min_ND,
				e.StartTime AS Start_Time_Plan,
				e.EndTime AS End_Time_Plan,
				DATE(e.StartTime) AS StartDate,
				LEAST(e.EndTime, TIMESTAMP(DATE(e.StartTime), '23:59:59')) AS End_Time_OR_24,
				e.duration_min_pl AS duration_min -- bylo e.duration_min
			FROM `hr_events` e
			WHERE	
				-- ((_var_DateToYM(e.StartTime) = _Param_A) OR (_var_DateToYM(e.EndTime ) = _Param_A))
				((YEAR(e.StartTime)*100 + MONTH(e.StartTime) = _Param_A) OR (YEAR(e.EndTime)*100 + MONTH(e.EndTime) = _Param_A))
				AND e.OsobaGUID = _Key
				AND e.event_type = 'WORK'
				-- AND e.Deleted = 0
				AND duration_hour <= 24 
			ORDER BY e.StartTime
		;	
		ALTER TABLE `t_pracownika_workevents` ADD UNIQUE GUID (GUID), ADD KEY StartDate (StartDate);
		
		SET @bylem = '2.';
		
		IF @session.globals.pl.FrekwCalc.FromRCP.Mode = 1 THEN  -- np. Konkol ma FromRCP.Mode = 1
		
			DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw_plan`;
			CREATE TEMPORARY TABLE `t_dataset_calc_frekw_plan` 
				SELECT
					e.GUID, 
					MIN(e.StartTime) AS StartTime,
					MAX(e.EndTime) AS EndTime,
					DATE(e.StartTime) AS StartDate
				FROM `hr_events` e
				WHERE	
					-- ((_var_DateToYM(e.StartTime) = _Param_A) OR (_var_DateToYM(e.EndTime ) = _Param_A))
					((YEAR(e.StartTime)*100 + MONTH(e.StartTime) = _Param_A) OR (YEAR(e.EndTime)*100 + MONTH(e.EndTime) = _Param_A))
					AND e.OsobaGUID = _Key
					AND e.event_type = 'PLAN'
					AND e.Deleted = 0
					AND duration_hour <= 24 
				GROUP BY StartDate
				ORDER BY e.StartTime
			;	
			ALTER TABLE `t_dataset_calc_frekw_plan` ADD UNIQUE GUID (GUID), ADD KEY StartDate (StartDate);
			UPDATE `t_pracownika_workevents` t, `t_dataset_calc_frekw_plan` p
			SET
				t.Start_Time_Plan = p.StartTime,
				t.End_Time_Plan = p.EndTime
			WHERE
				t.StartDate = p.StartDate
			;	
			UPDATE `hr_events` e, `t_pracownika_workevents` t
			SET
				e.StartTime_work = GREATEST(t.Start_Time_Plan, e.StartTime)
				-- e.EndTime_work = LEAST(t.End_Time_Plan, e.EndTime)
			WHERE
				e.GUID = t.GUID
			;	
			DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw_plan`;
		END IF;  -- IF @session.globals.pl.FrekwCalc.FromRCP.Mode = 1 THEN
		
		
		
		DROP TEMPORARY TABLE IF EXISTS `t_agg_frekw_prac`;
		CREATE TEMPORARY TABLE `t_agg_frekw_prac` 
			SELECT
				p.GUID,
				p.absencja,
				e.OsobaGUID AS PracGUID, 
				DATE(e.StartTime) AS StartDate,
				_var_DateToYM(t.StartDate) AS YearMonth,
				-- _varInt(_Param_A) AS YearMonth,
				e.event_source AS work_event_source,
				SUM(IF(NOT e.Deleted, e.duration_min_pl, 0)) AS duration_min, 
				SUM(IF(NOT e.Deleted, e.duration_min_work, 0)) AS duration_min_work,
				SUM(IF(NOT e.Deleted, e.duration_min_night, 0)) AS duration_min_night,
				SUM(IF(NOT e.Deleted, e.duration_min_night_work, 0)) AS duration_min_night_work,
				SUM(IF(NOT e.Deleted, e.duration_min_night, 0)) AS duration_min_night_plan, --	tylko dla tych porówn. z planem (np. Konkol)
				
				MIN(IF(NOT e.Deleted, e.StartTime, NULL)) AS FirstWe, 
				MAX(IF(NOT e.Deleted, e.EndTime, NULL)) AS LastWy,
				MIN(IF(NOT e.Deleted, e.StartTime, NULL)) AS FirstWePlan, 
				MAX(IF(NOT e.Deleted, e.EndTime, NULL)) AS LastWyPlan,
				MIN(IF(NOT e.Deleted, e.StartTime, NULL)) AS FirstWe_Rnd, 
				MAX(IF(NOT e.Deleted, e.EndTime, NULL)) AS LastWy_Rnd,
				MIN(IF(NOT e.Deleted, Start_Time_Plan, NULL)) AS Start_Time_Plan,
				MAX(IF(NOT e.Deleted, End_Time_Plan, NULL)) AS End_Time_Plan,
				_VarINT(COUNT(*)) AS IleZdarzen,
				o.NieNaliczajGodzNoc,
				p.kp_godziny,
				p.godziny_xx,
				p.godziny_yy,
				p.godziny_zz,
				p.noc

			FROM `t_pracownika_workevents` t
			INNER JOIN `frekw_po` p ON p.FrekwGUID = @pl.frekw.GUID AND p.rok_msc_dzien = t.StartDate
			LEFT JOIN `hr_events` e ON e.GUID = t.GUID -- AND e.Deleted = 0
			INNER JOIN `Pracownicy` o ON o.GUID = e.OsobaGUID
			GROUP BY StartDate	
			ORDER BY e.StartTime
			;	
		ALTER TABLE `t_agg_frekw_prac` ADD UNIQUE GUID (GUID);	
		
 
		UPDATE `frekw_po` p -- , `frekw` n
		SET
		-- dla np konkola kp_godziny przepisz tylko gdy godziny = 0
		-- 	p.godziny = if(p.godziny!=0 AND (@session.globals.pl.FrekwCalc.FromRCP.Mode = 1), p.godziny, p.kp_godziny ), 
			p.godziny = IF(p.ReadOnly, p.godziny, p.kp_godziny),
			p.noc = IF(p.ReadOnly, p.noc, 0),
			p.data_wej_plan = DEFAULT(p.data_wej_plan),
			p.data_wyj_plan = DEFAULT(p.data_wyj_plan),
			p.data_wej = DEFAULT(p.data_wej),
			p.data_wyj = DEFAULT(p.data_wyj),
			p.godziny_xx = DEFAULT(p.godziny_xx),
			p.godziny_yy = DEFAULT(p.godziny_yy),
			p.godziny_zz = DEFAULT(p.godziny_zz),
			p.godziny_prod = p.kp_godziny, -- LEAST(p.godziny, p.kp_godziny), -- DEFAULT(p.godziny_prod),
			p.godz_wej = NULL,
			p.godz_wyj = NULL,
			p.work_event_source = DEFAULT(p.work_event_source)
			
		WHERE
			p.FrekwGUID = @pl.frekw.GUID
			-- AND (n.Rok*100 + n.Miesiac) = _Param_A
			-- AND p.FrekwGUID = n.GUID
			-- AND (p.absencja IN ('', '-'))	dla dni gdy jest juz zwolniony lub jeszcze nie zatrudniony czyli '-' NIE PRZEPISUJEMY godzin z kalendarza
			AND p.absencja = ''
			-- AND NOT n.Deleted
			-- AND NOT n.Zamkniety
			AND NOT p.ReadOnly
		;	
		
		SET @bylem = '3.';
		
		
	
		UPDATE `frekw_po` p, `t_agg_frekw_prac` a
		SET
			p.godz_wej = TIME(a.FirstWe),
			p.godz_wyj = TIME(a.LastWy),
			p.work_event_source = a.work_event_source
			
		WHERE
			p.GUID = a.GUID
			AND NOT p.ReadOnly
		;	
		
		SET @bylem = '4.';
	
		/*
			W zależności od @session.globals.pl.FrekwCalc.FromRCP.Mode dajemy różne algorytmy
			@session.globals.pl.FrekwCalc.FromRCP.Mode:
				0	Ogólnie
				1	np. Konkol
				2	????
		*/	
		SELECT _GetAppOwner() INTO _Owner;	
			
		IF @session.globals.pl.FrekwCalc.FromRCP.Mode = 0 THEN  -- u Jarz: FromRCP.Mode = 0; Konkol : FromRCP.Mode = 1
		--	(0 -	Ogólnie)
			
			
		  SET @bylem = 'przed update sql';		
		  SET _SQLText = '	
			UPDATE `frekw_po` p, `t_agg_frekw_prac` a  -- , `frekw` n
			SET
				p.godziny = a.duration_min,  -- lepiej by dać duration_min_night_work
				-- ponizej dla Jarz zaokrąglamy godziny nocne do pelnych godzin (zawsze w górę) - wartosc ta nie moze przekroczyc godzin pracy
				p.noc = IF(''#Owner#'' = ''JARZEBINSCY'', LEAST(a.duration_min, IF (a.duration_min_night MOD 60 > 0, ((a.duration_min_night DIV 60) + 1) * 60, a.duration_min_night)), a.duration_min_night)
								
			WHERE
				p.GUID = a.GUID
				-- n.PracGUID = a.PracGUID AND (n.Rok*100 + n.Miesiac) = a.YearMonth
				-- AND p.FrekwGUID = n.GUID AND p.rok_msc_dzien = a.StartDate
				-- AND NOT n.Deleted 	-- AND NOT n.Zamkniety
				AND NOT p.ReadOnly
			; ';	
			SET _SQLText = REPLACE(_SQLText, '#Owner#', _VarStr(_Owner));
			SET @Agg_SQLText = _SQLText;
			CALL _ExecuteCmd(_SQLText, @Error);
			
			-- obsluga nieobecnosci 'P' dla Jarz.: zliczamy ich ilosc i wstawiamy do tylu dni wolnych czas pracy (standardową ilosc) 
			-- pow. nie dotyczy uczniów (kateg. prac. o idn: 12,13,14)
			IF (_Owner = 'JARZEBINSCY') AND (_def <> 12)  AND (_def <> 13) AND (_def <> 14) THEN
			
				SET _counter = 0;
				SELECT COUNT(*) AS ile 
				FROM frekw_po
				WHERE frekwguid = @pl.frekw.GUID AND absencja='P'
				INTO _counter
				;
			
			  
			  IF _counter > 0 THEN
			    -- robimy to w dwóch krokach, ponieważ "For multiple-table syntax, ORDER BY and LIMIT cannot be used. "
			  
			    -- pobieramy avg_min_per_day z hist. pracy, ktory wstawimy do dni wolnych
				SELECT IFNULL(H.avg_min_per_day, P.avg_min_per_day) AS avg_min_per_day
				FROM Pracownicy P
				LEFT JOIN historia_pracy H ON H.GUIDPr = P.GUID 
				  AND H.OdDnia = (SELECT MAX(OdDnia) FROM historia_pracy WHERE GUIDPr = H.GUIDPr AND  ( (100*YEAR(OdDnia) + MONTH(OdDnia)) <=  _Param_A) )
				WHERE  P.GUID = _Key
				INTO  _NCP		;
				/* stara wersja: wstawianie w pierwszych "_counter" dni wolnych miesiąca
				-- uzupelniamy godziny prod. w dniach wolnych, za ktore pracownik dostal wolne typu "P"
				SET _SQLText = '
				UPDATE frekw_po
				SET godziny_prod = #_NCP#
				WHERE frekwguid = @pl.frekw.GUID AND dzien_wolny = 1 
				ORDER BY dzien
				LIMIT #_counter#;	';	

				SET _SQLText = REPLACE(_SQLText, '#_NCP#', _VarStr(IFNULL(_NCP, 0)));
				SET _SQLText = REPLACE(_SQLText, '#_counter#', _VarStr(IFNULL(_counter, 0)));
				SET @_SQLText = _SQLText;
				CALL _ExecuteCmd(_SQLText, @Error);				
				*/
				
				-- nowa wersja: dni "P" mają być powiązane z dniami wolnymi w obrebie tygodnia
				DROP TEMPORARY TABLE IF EXISTS `t_wolne`;				
				CREATE TEMPORARY TABLE `t_wolne`   -- tabela z dniami wolnymi
				SELECT dzien, 0 AS zbilansowany
				FROM frekw_po
				WHERE frekwguid = @pl.frekw.GUID and dzien_wolny = 1;
				
				DROP TEMPORARY TABLE IF EXISTS `t_p`;				
				CREATE TEMPORARY TABLE `t_p`   -- tabela z dniami nieobecnosci typu "P"
				SELECT dzien
				FROM frekw_po
				WHERE frekwguid = @pl.frekw.GUID and absencja = 'P'
				order by dzien;
				
				OPEN _c_dni_P;
				loopP: LOOP
					FETCH _c_dni_P INTO
						_dzien_P;
					IF _done THEN
						LEAVE loopP;
					ELSE
					  -- dla danej literki "P" szukamy najstarszego dnia niezbilansowanego
					  select dzien from t_wolne
					  where (zbilansowany = 0)   and dzien between (_dzien_P - 7) and (_dzien_P - 1) 
					  order by dzien limit 1	-- tozsame z min(dzien)
					  into _I;
					  
					  update frekw_po f
					  set godziny_prod = _NCP
					  WHERE frekwguid = @pl.frekw.GUID AND dzien = _I;
					  
					  update t_wolne
					  set zbilansowany = 1
					  where dzien = _I;
					  
					  SET _done = FALSE;	
					  -- SET @pl.nalicz.narast.ile = @pl.nalicz.narast.ile + 1;
					  -- CALL pl_frekw_tools('pl.nalicz.narast.frekw', _PracGUID, _Param_A, _Param_B, '', '');
					  -- SET _done = FALSE;	
					END IF;
				END LOOP;
			    CLOSE _c_dni_P;			
				
			  END IF; -- IF _counter > 0 THEN
			 			 
			END IF;  -- IF _Owner = 'JARZEBINSCY' THEN
		ELSE  -- (czyli @session.globals.pl.FrekwCalc.FromRCP.Mode <> 0) :
		--	(1 -	np. Konkol)
			SET 
				@session.PL.IR.RCP_Kara_za_spoznienie_min = IFNULL(@session.PL.IR.RCP_Kara_za_spoznienie_min, 0),
				@session.PL.IR.Nalicz_place_typu_konkol_zoo = IFNULL(@session.PL.IR.Nalicz_place_typu_konkol_zoo, 0)
			;
									
			-- @session.PL.IR.Nalicz_place_typu_konkol_zoo	
		--	Cz 01
			UPDATE `t_agg_frekw_prac` a
			SET
				a.godziny_yy = 
					IF(a.NieNaliczajGodzNoc, 
						0,
						_RndTIME('MIN', 60*IF(a.duration_min_night_work > a.kp_godziny, a.duration_min_night_work - a.kp_godziny,  0), 30, '', '')
					), 
				a.noc = 
					IF(a.NieNaliczajGodzNoc,  
						0,
						_RndTIME('MIN', 60*LEAST(a.kp_godziny, a.duration_min_night_work), 30, '', '')
					),
				
				a.godziny_xx = 
					IF(TRUE, a.duration_min, 0),
				a.godziny_zz = 
					_RndTIME('MIN', 60*a.duration_min_work, 30, '', '')
			;
			
			IF @session.PL.IR.Nalicz_place_typu_konkol_zoo = 0 THEN
				UPDATE `t_agg_frekw_prac` a, `frekw_po` p -- , `frekw` n
				SET
					p.godziny_prod = p.kp_godziny, 
					p.godziny_yy = IF(a.Absencja != 'F', a.godziny_yy, a.noc),
					p.noc = IF(a.Absencja != 'F', a.noc, 0),
					p.godziny_xx = a.godziny_xx,
					p.Alert = CONCAT(
						IF((TIMESTAMPDIFF(SECOND, p.data_wej, a.Start_Time_Plan) >= 60*50) OR (TIMESTAMPDIFF(SECOND, a.End_Time_Plan, p.data_wyj) >= 60*50), 
							'G;', 
							''
						),	
						IF(a.IleZdarzen > 1, 
							'N;', 
							''
						)
					),
					
					p.godziny_zz = a.godziny_zz,
					
					p.data_wej_plan = a.Start_Time_Plan,
					p.data_wyj_plan = a.End_Time_Plan

				WHERE
					p.GUID = a.GUID
					AND NOT p.ReadOnly
				;	
			ELSE -- (czyli tutaj bedzie dla IF @session.PL.IR.Nalicz_place_typu_konkol_zoo <> 0 )
			-- np. zoo Konkol
				UPDATE `t_agg_frekw_prac` a, `frekw_po` p -- , `frekw` n
				SET
					p.godziny_prod = p.kp_godziny, 
					p.godziny_yy = a.godziny_yy + a.noc,
					p.noc = 0, -- a.noc,
					p.godziny_xx = a.godziny_xx,
					p.Alert = CONCAT(
						IF((TIMESTAMPDIFF(SECOND, p.data_wej, a.Start_Time_Plan) >= 60*50) OR (TIMESTAMPDIFF(SECOND, a.End_Time_Plan, p.data_wyj) >= 60*50), 
							'G;', 
							''
						),	
						IF(a.IleZdarzen > 1, 
							'N;', 
							''
						)
					),
					
					p.godziny_zz = a.godziny_zz,
					
					p.data_wej_plan = a.Start_Time_Plan,
					p.data_wyj_plan = a.End_Time_Plan

				WHERE
					p.GUID = a.GUID
					AND NOT p.ReadOnly
				;	
				SET @bylem = '11';
			END IF;  -- IF @session.PL.IR.Nalicz_place_typu_konkol_zoo = ...
			
			-- tutaj dać sumowanie (godz_zz) from frekw_po -> frekw.godziny_pracy
			IF (_Owner = 'KONKOL') THEN
				SELECT 
					SUM(IFNULL(godziny_zz, 0)) 
				FROM
					frekw_po WHERE frekwguid = @pl.frekw.GUID
				INTO
				    @frekw.godziny_zz;
					
				IF 	@frekw.godziny_zz > 0 THEN
					UPDATE frekw 
					SET godziny_pracy = @frekw.godziny_zz 
					WHERE guid = @pl.frekw.GUID;
				END IF;
				
			END IF;
			
			SET @AS_What = 1;
			--	jmam
			SET
				@pl_frekw.NieNaliczajGodzSNS = FALSE,
				@pl_frekw.SNS.plus = 0,
				@pl_frekw.SNS.ile.na.minus = 0
			;	
			SELECT
				SUM(IF(p.kp_info IN ('N', 'D'), p.godziny_zz, 0)) AS sns,
				SUM(IF(p.Absencja = 'F' AND p.godziny_zz = 0, 1, 0)) AS sns_ile_na_minus,
				-- SUM(IF(p.Absencja = 'F' AND p.godziny_zz = 0, -LEAST(480, p2.godziny_zz), 0)) AS sns_minus,
				n.NieNaliczajGodzSNS
				
			FROM `frekw` n
			INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
			LEFT JOIN `frekw_po` p2 ON p2.GUID = p.GUID_kp_F
			WHERE
				n.GUID = @pl.frekw.GUID
				AND NOT n.NieNaliczajGodzSNS
			INTO 
				@pl_frekw.SNS.plus,
				@pl_frekw.SNS.ile.na.minus,
				@pl_frekw.NieNaliczajGodzSNS
			;	
			
			/* #034080 tutaj ewentualnie możnaby dodać warunek  "AND (@pl.frekw.GUID != '')"
			   aby uniknąc wywołania NOT FOUND (co w konsekwencji spowoduje przerwanie pętli wywołującej
			   'pl.nalicz.mc.frekw.one.prac' dla kolejnych pracowników) w sytuacji, gdy pracownik nie ma
			   założonej karty frekwencji;
			   ostatecznie warunku tu nie dodajemy, gdyż w pętli po każdej iteracji dodaliśmy ustawianie "_done = FALSE",
			   dzieki czemu "zerujemy" ewentualne NOT FOUND tutaj wygenerowane 
			*/
			IF NOT @pl_frekw.NieNaliczajGodzSNS  THEN
				DROP TEMPORARY TABLE IF EXISTS `t_frekw_po_sns_na_minus`;
				SET _SQLText = 
				'CREATE TEMPORARY TABLE `t_frekw_po_sns_na_minus`
					SELECT
						p.godziny_zz
					FROM `frekw` n
					INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
					WHERE
						n.GUID = @pl.frekw.GUID
						AND NOT n.NieNaliczajGodzSNS
						AND p.kp_info IN (''N'', ''D'')
						AND p.Absencja != ''F''
						AND p.godziny_zz != 0
					ORDER BY p.dzien	
					LIMIT #@pl_frekw.SNS.ile.na.minus#
				;'	
				;	
				SET _SQLText = REPLACE(_SQLText, '#@pl_frekw.SNS.ile.na.minus#', _VarStr(IFNULL(@pl_frekw.SNS.ile.na.minus, 0)));
				SET @_SQLText = _SQLText;
				CALL _ExecuteCmd(_SQLText, @Error);
				SET @pl_frekw.SNS.minus = 0;
				SELECT SUM(LEAST(480, godziny_zz)) FROM `t_frekw_po_sns_na_minus` INTO @pl_frekw.SNS.minus;
				SET @pl_frekw.SNS.minus = IFNULL(@pl_frekw.SNS.minus, 0);
				DROP TEMPORARY TABLE IF EXISTS `t_frekw_po_sns_na_minus`;
				
				
				
				UPDATE `frekw` n
				SET
					n.godzSNS = 
						IF(@session.PL.IR.Nalicz_place_typu_konkol_zoo = 0,
							@pl_frekw.SNS.plus,
							@pl_frekw.SNS.plus - @pl_frekw.SNS.minus
						)	
				WHERE	
					n.GUID = @pl.frekw.GUID
					AND NOT n.NieNaliczajGodzSNS
				;
			END IF;
			
		END IF;  -- IF @session.globals.pl.FrekwCalc.FromRCP.Mode = ...
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_po_one_prac`;
		-- DROP TEMPORARY TABLE IF EXISTS `t_agg_frekw_prac`;
		-- DROP TEMPORARY TABLE IF EXISTS `t_pracownika_workevents`;
		
		-- CZY TO W OGÓLE JEST POTRZEBNE!!!!!!!!!!!!!! CALL pl_frekw_tools('pl.nalicz.mc.frekw.absencje', _Key, _Param_A, '', '', '');	
		
	END IF;	--	IF _MODE = 'pl.nalicz.mc.frekw.one.prac'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.mc.frekw.one.prac.one.event'
	Wstaw z crm-a
		Parametry
			_Key		GUID eventu
	CALL pl_frekw_tools('pl.nalicz.mc.frekw.one.prac.one.event', Event, '', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.mc.frekw.one.prac.one.event') THEN
		SELECT
			StartTime, EndTime, OsobaGUID
		INTO
			@event.StartTime, @event.EndTime, @event.OsobaGUID
		FROM `hr_events`	
		WHERE
			GUID = _Key
		;
		SET
			@event.StartTimePlan = @event.StartTime, 
			@event.EndTimePlan = @event.EndTime
		;	
		IF (@session.globals.pl.FrekwCalc.FromRCP.Mode = 1) THEN
			SELECT
				StartTime, EndTime
			INTO
				@event.StartTimePlan, @event.EndTimePlan
			FROM `hr_events`	
			WHERE
				OsobaGUID = @event.OsobaGUID
				AND Deleted = 0 
				AND event_type = 'PLAN' 
				AND DATE(StartTime) = DATE(@event.StartTime)
			LIMIT 1	
			;
			SET
				@event.StartTimePlan = IFNULL(@event.StartTimePlan, @event.StartTime), 
				@event.EndTimePlan = IFNULL(@event.EndTimePlan, @event.EndTime)
			;	
			SET
				@event.StartTime = GREATEST(@event.StartTimePlan, @event.StartTime)
			;	
		END IF;
		
		SET
			@event.StartDate = DATE(@event.StartTime),
			@event.EndDate = DATE(@event.EndTime)
		;	
		SET
			@event.A00 = _varDateToDateTime(@event.StartDate, '00:00:00'),
			@event.A06 = _varDateToDateTime(@event.StartDate, '06:00:00'),
			@event.A22 = _varDateToDateTime(@event.StartDate, '22:00:00'),
			@event.B06 = _varDateToDateTime(@event.EndDate,   '06:00:00'),
			@event.B22 = _varDateToDateTime(@event.EndDate,   '22:00:00')
		
		;	
		
		DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw_time_buffer`;
		CREATE TEMPORARY TABLE `t_dataset_calc_frekw_time_buffer` (
		  `TimeX` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'  COMMENT 'Rozpoczecie',
		  `TypeX` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'typ wpisu START END A06 A22 B06 B22',
		  `TypeHour` VARCHAR(50) NOT NULL DEFAULT '' COMMENT 'typ wpisu noc/dzień czyli N/D',
		  `duration_min` DOUBLE(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'czas w min.',
		  `deleted` BOOLEAN NOT NULL DEFAULT FALSE
		)
		;
		
		TRUNCATE `t_dataset_calc_frekw_time_buffer`;		
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.A00, 'A00', 'N', 0, FALSE);
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.A06, 'A06', 'D', 0, FALSE);
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.A22, 'A22', 'N', 0, FALSE);
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.B06, 'B06', 'D', 0, FALSE);
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.B22, 'B22', 'N', 0, FALSE);
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.StartTime, 'START', 'N', 0, FALSE);
		INSERT INTO `t_dataset_calc_frekw_time_buffer` VALUES(@event.EndTime, 'END', '', 0, FALSE);
		
		DELETE FROM `t_dataset_calc_frekw_time_buffer` WHERE TimeX > @event.EndTime;	

		BEGIN
			DECLARE _Last_TypeX VARCHAR(50);
			DECLARE _Last_TimeX DATETIME;
			DECLARE _Last_TypeHour VARCHAR(50);
			DECLARE _TimeX DATETIME;
			DECLARE _TypeX VARCHAR(50);
			DECLARE _TypeHour VARCHAR(50);
			DECLARE _duration_min DOUBLE(16,4) DEFAULT '0.0000';
			DECLARE _Started BOOLEAN DEFAULT FALSE;
			
			DECLARE _done BOOL DEFAULT FALSE;
			DECLARE _c_proc CURSOR FOR
				SELECT 
					TimeX, TypeX, TypeHour, duration_min
				FROM `t_dataset_calc_frekw_time_buffer`
				WHERE
					NOT deleted
				ORDER BY TimeX
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
			OPEN _c_proc;
				loop1: LOOP
					FETCH _c_proc INTO
						_TimeX, _TypeX, _TypeHour, _duration_min;
					IF _done THEN
						LEAVE loop1;
					ELSE
						IF _Last_TypeX IS NOT NULL THEN
							UPDATE `t_dataset_calc_frekw_time_buffer` b
							SET
								b.duration_min = TIMESTAMPDIFF(MINUTE, b.TimeX, _TimeX)
							WHERE	
								b.TypeX = _Last_TypeX
							;
							IF (_TypeX = 'START') THEN
								UPDATE `t_dataset_calc_frekw_time_buffer` SET TypeHour = _Last_TypeHour WHERE TypeX = 'START';
							END IF;
						END IF;
						SET 
							_Last_TypeX = _TypeX,
							_Last_TimeX = _TimeX,
							_Last_TypeHour = _TypeHour
						;
						IF (_TypeX = 'START') THEN
							SET _Started = TRUE;
						END IF;
						IF (_TypeX = 'END') THEN
							SET _Started = FALSE;
						END IF;
						UPDATE `t_dataset_calc_frekw_time_buffer` SET Deleted = NOT _Started WHERE TypeX = _TypeX;
					END IF;
				END LOOP;
			CLOSE _c_proc;
		END;			
		
		SET @duration_min_night = 0;
		SELECT
			SUM(duration_min)
		INTO	
			@duration_min_night
		FROM `t_dataset_calc_frekw_time_buffer`
		WHERE
			NOT deleted
			AND TypeHour = 'N'
		;
		UPDATE `hr_events`
		SET
			duration_min_night = IFNULL(@duration_min_night, 0),
			StartTime_work = @event.StartTime
		WHERE
			GUID = _Key
		;
			
--		SELECT b.*, b.duration_min/60 as hour FROM `t_dataset_calc_frekw_time_buffer` b ORDER BY TimeX;			
		DROP TEMPORARY TABLE IF EXISTS `t_dataset_calc_frekw_time_buffer`;
	END IF;	--	IF _MODE = 'pl.nalicz.mc.frekw.one.prac.one.event'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.mc.frekw.absencje'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011)
	CALL pl_frekw_tools('pl.nalicz.mc.frekw.absencje', '', '201104', '', '', '');	
	CALL pl_frekw_tools('pl.nalicz.mc.frekw.absencje', @PracGUID, '201104', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.mc.frekw.absencje') THEN
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_miesiaca`;
		CREATE TEMPORARY TABLE `t_frekw_miesiaca` 
			SELECT
				n.GUID AS FrekwGUID, 
				p.GUID AS FrekwPoGUID, 
				n.PracGUID AS PracGUID, 
				p.rok_msc_dzien AS Dzien,
				_varDateTime(p.rok_msc_dzien) AS DzienStart,
				_VarStr('') AS Abs_Type,	--	Literka KODOWA
				p.Duration_min,
				p.all_day,
				FALSE AS Changed,
				godziny, noc, absencja, godziny150, godziny200, kp_info, kp_godziny, godziny_prod, godziny_xx, pula_url, godz_absencji, korekta_plus, korekta_minus 
			FROM `frekw` n
			LEFT JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
			WHERE
				(n.Rok*100 + n.Miesiac) = _Param_A
				AND IF(_Key != '', n.PracGUID = _Key, TRUE)
				AND NOT n.Deleted
				AND NOT n.Zamkniety
		;	
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_miesiaca_events`;
		CREATE TEMPORARY TABLE `t_frekw_miesiaca_events` 
			SELECT
				f.FrekwGUID,
				f.FrekwPoGUID,
				f.PracGUID,
				IF(e.GUID IS NULL, 0, 1) AS IsAbsencja,
				e.GUID AS GUID_event,
				f.Dzien,
				IFNULL(e.PodRodzaj, 7) - 8 AS PodRodzaj,
				a.GUID AS Absencja_GUID,
				d.Symbol AS Absencja_Symbol,
				d.definicja AS Absencja_definicja,
				GREATEST(IFNULL(e.StartTime, f.DzienStart), _varDateToDateTime(f.Dzien, '00:00:00')) AS Day_StartTime,
				LEAST(IFNULL(e.EndTime, _varDateToDateTime(Dzien, '23:59:59')), _varDateToDateTime(Dzien, '23:59:59')) AS Day_EndTime,
				_VarDouble(0.0) AS Duration_min,
				e.StartTime, 
				e.EndTime
			FROM `t_frekw_miesiaca` f
			LEFT JOIN `hr_events` e ON e.event_source = 'ABS' AND e.OsobaGUID = f.PracGUID AND e.Deleted = 0 AND ((f.DzienStart BETWEEN e.StartTime AND e.EndTime)OR(DATE(e.StartTime) = f.Dzien))
			LEFT JOIN `absencje` a ON a.GUID = e.SrcGUID 
			LEFT JOIN `absencje_dict` d ON d.IDN = IFNULL(a.Rodzaj, (IFNULL(e.PodRodzaj, 9) - 7))  -- IFNULL(e.PodRodzaj, 7) - 8 
			WHERE 
				TRUE
		;
		DELETE FROM `t_frekw_miesiaca_events` WHERE NOT IsAbsencja;
		UPDATE `t_frekw_miesiaca_events`
		SET
			Duration_min = TIMESTAMPDIFF(MINUTE, Day_StartTime, Day_EndTime)
		WHERE	
			IsAbsencja
		;
		UPDATE `t_frekw_miesiaca_events`
		SET
			Duration_min = IF(Duration_min = 1439, 1440, Duration_min)
		WHERE	
			IsAbsencja
		;
		
		-- SELECT f.*, f.Duration_min/60 FROM `t_frekw_miesiaca_events` f;
			
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_miesiaca_events_agg`;
		CREATE TEMPORARY TABLE `t_frekw_miesiaca_events_agg` 
			SELECT 
				FrekwGUID,
				FrekwPoGUID, 
				PracGUID,
				Dzien,
				SUM(f.Duration_min) AS Duration_min,
				Absencja_GUID,
				Absencja_Symbol,
				Absencja_definicja
			FROM `t_frekw_miesiaca_events` f
			GROUP BY FrekwPoGUID
		;
		
		UPDATE `t_frekw_miesiaca` f, `t_frekw_miesiaca_events_agg` fa
		SET
			f.Changed = TRUE,
			f.Abs_Type = fa.Absencja_Symbol,	
			f.Duration_min = fa.Duration_min,
			f.all_day = fa.Duration_min >= 1440
		WHERE
			fa.FrekwPoGUID = f.FrekwPoGUID
		;
		UPDATE `t_frekw_miesiaca` f
		SET
			f.godz_absencji = IF(all_day, kp_godziny, f.Duration_min),
			f.absencja = f.Abs_Type -- CONCAT(f.absencja, f.Abs_Type)
		WHERE
			f.Changed
		;
		UPDATE `t_frekw_miesiaca` f
		SET
			f.godziny = f.kp_godziny - f.godz_absencji
		WHERE
			f.Changed
		;
--				godziny, noc, absencja, godziny150, godziny200, kp_info, kp_godziny, godziny_prod, godziny_xx, pula_url, godz_absencji, korekta_plus, korekta_minus, 
		
		UPDATE `frekw_po` p, `t_frekw_miesiaca` pt
		SET
			p.Absencja = ''
		WHERE
			p.GUID = pt.FrekwPoGUID
		;
		UPDATE `frekw_po` p, `t_frekw_miesiaca` pt
		SET
			p.Absencja = pt.Absencja,
			p.godz_absencji = pt.godz_absencji,
			p.Duration_min = pt.Duration_min,
			p.all_day = pt.all_day,
			p.godziny = 0,
			p.godziny150 = 0, 
			p.godziny200 = 0,
			p.godziny_prod = 0,
			p.noc = 0
		WHERE
			p.GUID = pt.FrekwPoGUID
			AND
			pt.Changed
		;
		
		/*
		SELECT 
			FrekwGUID,
			FrekwPoGUID, 
			PracGUID,
			Dzien,
			SUM(f.Duration_min) AS Duration_min,
			Absencja_GUID,
			Absencja_Symbol,
			Absencja_definicja
		FROM `t_frekw_miesiaca_events` f
		GROUP BY FrekwPoGUID
		ORDER BY PracGUID, Dzien
		;
		*/
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_miesiaca_events_agg`;
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_miesiaca_events`;
	END IF;	--	IF _MODE = 'pl.nalicz.mc.frekw.absencje'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.sumy.naglowka'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik gdy '' to wszystkich (Tylko gdy _Param_B = '')
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011) (Tylko gdy _Param_B = '')
			_Param_B	(domyślnie pusty!!! Ale GDY <> '' to tworzymy tempa `t_frekw_sumy` z innego tempa `t_hr_changed_frekw` (patrz 'pl.nalicz.mc.frekw.z.hr_changed_frekw')
	CALL pl_frekw_tools('pl.nalicz.sumy.naglowka', '', '201104', '', '', '');	
	CALL pl_frekw_tools('pl.nalicz.sumy.naglowka', @GUIDPrac, '201104', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.sumy.naglowka') THEN
	
	
	-- slon wstawka ktora tworzy tymczasowa tabele w ktorej zmieniane sa dni wolne na pracujace z powodu F-a
		SET @CountF = 0;
		SELECT
			count(*)
		INTO	
			@CountF
		FROM `frekw` n 
		INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
		WHERE
			(n.Rok*100 + n.Miesiac) = _Param_A
			AND IF(_Key != '', n.PracGUID = _Key, TRUE)
			AND NOT n.Deleted 
			AND NOT n.Zamkniety
			AND p.absencja = 'F'
		;
		
			
	-- slon as dodanie F i E bo nie mozemy zmniejszac NCP 
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_sumy`;
		IF IFNULL(_Param_B, '') = '' THEN
			CREATE TEMPORARY TABLE `t_frekw_sumy` 
				SELECT
					n.GUID,
					SUM(p.godziny - p.godziny150 - p.godziny200) AS godz100prac, 
					SUM(p.godziny_prod) AS godz100prac_prod, 
					SUM(p.godziny150) AS godz_plus50prac,
					SUM(p.godziny200) AS godz_plus100wolne,
					SUM(p.godziny) AS SumaGodzin,
					SUM(p.noc) AS SumaGodzNocnych,
					SUM(IF(absencja in ('','F','E'), p.kp_godziny, 0)) AS Sumakp_godziny,
					SUM(p.dni_absencji) AS Sum_Dni_absencji,
					SUM(IF(absencja = 'W', p.dni_absencji, 0)) AS Sum_Dni_absencji_W,
					SUM(IF(absencja = 'W', p.kp_godziny, 0)) AS Sum_Godz_W  -- docelowo p.godz_absencji										
				FROM `frekw` n 
				INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID 
				-- frekw_po
				WHERE
					(n.Rok*100 + n.Miesiac) = _Param_A
					AND IF(_Key != '', n.PracGUID = _Key, TRUE)
					AND NOT n.Deleted
					AND NOT n.Zamkniety
				GROUP BY n.GUID
			;
		ELSE
			CREATE TEMPORARY TABLE `t_frekw_sumy` 
				SELECT
					n.GUID,
					SUM(p.godziny - p.godziny150 - p.godziny200) AS godz100prac, 
					SUM(p.godziny_prod) AS godz100prac_prod, 
					SUM(p.godziny150) AS godz_plus50prac,
					SUM(p.godziny200) AS godz_plus100wolne,
					SUM(p.godziny) AS SumaGodzin,
					SUM(p.noc) AS SumaGodzNocnych,
					SUM(IF(absencja in ('','F','E'), p.kp_godziny, 0)) AS Sumakp_godziny,
					SUM(p.dni_absencji) AS Sum_Dni_absencji,
					SUM(IF(absencja = 'W', p.dni_absencji, 0)) AS Sum_Dni_absencji_W,
					SUM(IF(absencja = 'W', p.kp_godziny, 0)) AS Sum_Godz_W  -- docelowo p.godz_absencji
				FROM `t_hr_changed_frekw` cf	
				INNER JOIN `frekw` n ON n.GUID = cf.FrekwGUID
				INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
				-- frekw_po
				WHERE
					NOT n.Deleted
					AND NOT n.Zamkniety
				GROUP BY n.GUID
			;
		END IF;
		
		IF @session.globals.pl.FrekwCalc.Nadgodz.Narastajaco THEN
		-- IF @session.globals.Owner = '.JARZEBINSCY' THEN	--	slon dodal kropkę bo Jarzebinscy sie wycofuja z tego IF-a Zostawiamy jednak to na wykorzystanie pozniej
		--		algorytm ten rozlicza godziny i nadgodziny miesiecznie, a nie dziennie.
		--		dopelnia sume godzin100 nadgodzinami do wysokosci sumy kp_godzin (NCP) pozostala czesc nadgodzin rozrzuca miedzy nadgodziny 100 i 50
		--		wyzszy priorytet maja nadgodziny 100
			UPDATE `frekw` n, `t_frekw_sumy` t
			SET
			/*
				n.godz100prac = 
					IF(t.SumaGodzin > t.Sumakp_godziny, 
			w			t.SumaGodzin - t.godz_plus50prac - t.godz_plus100wolne,
						t.SumaGodzin
					),
			*/		
				n.godz_plus100wolne = 
					IF(t.SumaGodzin > t.Sumakp_godziny, 
						LEAST(t.SumaGodzin - t.Sumakp_godziny, t.godz_plus100wolne),
						-- t.godz_plus100wolne, -- t.SumaGodzin - t.Sumakp_godziny,
						0
					),	
				n.godz_plus50prac = 
					IF(t.SumaGodzin > t.Sumakp_godziny, 
						GREATEST(t.SumaGodzin - t.Sumakp_godziny - t.godz_plus100wolne, 0),
						0
					),	
				n.IleGodzinNocnych = t.SumaGodzNocnych,
				n.W_Dni = t.Sum_Dni_absencji_W,
				n.W_godz = t.Sum_Godz_W,
				n.PozostUrlopWypoczDni = GREATEST(0, n.DostepnyUrlWypoczDni - t.Sum_Dni_absencji_W),  -- AS pozost_wypocz,
				n.PozostUrlopWypoczGodz = GREATEST(0, n.DostepnyUrlWypoczGodz - t.Sum_Godz_W)
				-- GREATEST(0, PozostUrlopZaleglyDni - W_dni)  AS pozost_zaleg
			WHERE
				t.GUID = n.GUID
			;	
			UPDATE `frekw` n, `t_frekw_sumy` t
			SET
				n.godz100prac = 
					IF(t.SumaGodzin > t.Sumakp_godziny, 
						t.SumaGodzin - n.godz_plus100wolne - n.godz_plus50prac,
						t.SumaGodzin
					)
			WHERE
				t.GUID = n.GUID
			;	
		ELSE
			UPDATE `frekw` n, `t_frekw_sumy` t
			SET
				n.godz100prac = t.godz100prac, 
				n.godz100prac_prod = t.godz100prac_prod, 
				n.godz_plus50prac = t.godz_plus50prac,
				n.godz_plus100wolne = t.godz_plus100wolne,
				n.IleGodzinNocnych = t.SumaGodzNocnych,
				n.W_Dni = t.Sum_Dni_absencji_W,
				n.W_godz = t.Sum_Godz_W,
				n.PozostUrlopWypoczDni = GREATEST(0, n.DostepnyUrlWypoczDni - t.Sum_Dni_absencji_W),  -- AS pozost_wypocz,
				n.PozostUrlopWypoczGodz = GREATEST(0, n.DostepnyUrlWypoczGodz - t.Sum_Godz_W)
				-- GREATEST(0, PozostUrlopZaleglyDni - W_dni)  AS pozost_zaleg
			WHERE
				t.GUID = n.GUID
			;	
		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_sumy`;
		-- DROP TEMPORARY TABLE IF EXISTS `t_frekw_poF`;
		-- DROP TEMPORARY TABLE IF EXISTS `t_frekw_poOnlyF`;
		
	END IF;	--	IF _MODE = 'pl.nalicz.sumy.naglowka'

	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.nalicz.mc.frekw.one.prac.1.absencje.2.frekw.one.prac.sumy.naglowka'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik KONIECZNY
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011)
	CALL pl_frekw_tools('pl.nalicz.mc.frekw.one.prac.1.absencje.2.frekw.one.prac.sumy.naglowka', @GUIDPrac, '201505', '', '', '');	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.nalicz.mc.frekw.one.prac.1.absencje.2.frekw.one.prac.sumy.naglowka') THEN
		CALL pl_frekw_tools('pl.nalicz.mc.frekw.absencje', _Key, _Param_A, '', '', '');	
		CALL pl_frekw_tools('pl.nalicz.mc.frekw.one.prac', _Key, _Param_A, '', '', '');	
		CALL pl_frekw_tools('pl.nalicz.sumy.naglowka', _Key, _Param_A, '', '', '');	
		
	END IF;	--	IF _MODE = 'pl.nalicz.mc.frekw.1.absencje.2.frekw.one.prac.sumy.naglowka'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.select.osoba_mc.frekw'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik gdy '' to wszystkich
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011)
			_Param_B	Gdy _Key = '' oznacza że mamy GUIDy w tabeli tymczasowej `t_wybraneosoby`
			_Param_C    określa, czy pobierać tylko zapisy niezamkniete ('' = wart. domyślna.), czy wszystkie (= 'ALL')
	CALL pl_frekw_tools('pl.select.osoba_mc.frekw', @GUIDPrac, '201107', '', '', '');	
	CALL pl_frekw_tools('pl.select.osoba_mc.frekw', '', '201107', '', '', '');	
	CALL pl_frekw_tools('pl.select.osoba_mc.frekw', '', '201107', '1', '', '');	
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.select.osoba_mc.frekw') THEN
		
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_osoba_mc`;
		CREATE TEMPORARY TABLE `t_frekw_osoba_mc` 
			SELECT
				p.GUID,
				p.dzien,
				p.rok_msc_dzien AS StartDate,
				p.absencja,
				p.dzien_wolny,
				p.kp_info,
				p.kp_godziny,
				p.godziny,
				p.noc,
				p.godziny150,
				p.godziny200,
				p.godziny_xx,
				p.godziny_yy,
				p.godziny_zz,
				p.godz_wej,
				p.godz_wyj,
				p.duration_min,
				p.korekta_plus,
				p.korekta_minus,
				_varInt(0) AS IsKorekta_We,
				_varInt(0) AS IsKorekta_Wy,
				n.PracGUID,
				_varStr(n.PracGUID) AS NrKarty,
				p.FrekwGUID,
				n.godz100prac AS ng_godz100prac, 
				n.godz100prac_prod AS ng_godz100prac_prod, 
				n.godz_plus50prac AS ng_godz_plus50prac, 
				n.godz_plus100wolne AS ng_godz_plus100wolne,
				_varDouble(Round(1.00*n.IleGodzinNocnych, 2)) AS ng_godz_Noc,
				n.godz_plus50prac*1.5 AS ng_godz_plus50prac_15, 
				n.godz_plus100wolne*2 AS ng_godz_plus100wolne_20,
				n.godzSNS AS ng_godzSNS,
				IFNULL(p.alert, '') AS alert,
				CONCAT(
					IFNULL(p.alert, ''),
					IF(p.data_wej != '0000-00-00 00:00:00' AND p.work_event_source = '', 'K;', ''),
					IF(p.data_wej_plan < p.data_wej, 'S;', '')
				) AS alertPrn
			FROM `frekw` n 
			INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
			WHERE
				NOT n.Deleted
				AND IF(_Param_C = 'ALL', TRUE, NOT n.Zamkniety) 
				AND (n.Rok*100 + n.Miesiac) = _Param_A
				AND IF(_Key != '', n.PracGUID = _Key, TRUE)
			GROUP BY p.GUID
		;
		
		SET _Param_B = IFNULL(_Param_B, '');
		IF _Key = '' AND _Param_B != '' THEN
			-- t_wybraneosoby (pracGUID varchar(38))
			DELETE FROM `t_frekw_osoba_mc`
			WHERE
				PracGUID NOT IN (SELECT pracGUID FROM `t_wybraneosoby`)
			;
		END IF;
		/*
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_osoba_mc_prac`;
		CREATE TEMPORARY TABLE `t_frekw_osoba_mc_prac` 
			SELECT 
				t.PracGUID, 
				f.godz100prac, 
				f.godz_plus50prac, 
				f.godz_plus100wolne,
				f.IleGodzinNocnych
				
			FROM `t_frekw_osoba_mc` t
			INNER JOIN `frekw` f ON f.GUID = t.FrekwGUID
			GROUP BY t.PracGUID
		;
		*/
		SELECT
			ID INTO @pl.select.osoba_mc.frekw.NrKarty
		FROM `v_rcp_cards`
		WHERE
			OsobaGUID = _Key AND 
			Status = 0
		LIMIT 1	
		;	
		SET @pl.select.osoba_mc.frekw.NrKarty = IFNULL(@pl.select.osoba_mc.frekw.NrKarty, '?');
		UPDATE `t_frekw_osoba_mc` t
		SET
			t.IsKorekta_We = EXISTS(
				SELECT IDN FROM `rcp_rejestracje` r WHERE DATE(r.rcp_czas) = DATE(t.StartDate) AND r.OsobaGUID = t.PracGUID AND r.rcp_typ = 'WE' AND r.korekta != 0
			),
			t.IsKorekta_Wy = EXISTS(
				SELECT IDN FROM `rcp_rejestracje` r WHERE DATE(r.rcp_czas) = DATE(t.StartDate) AND r.OsobaGUID = t.PracGUID AND r.rcp_typ = 'WY' AND r.korekta != 0
			),
			t.NrKarty = @pl.select.osoba_mc.frekw.NrKarty
		
		;
		
	END IF;	--	IF _MODE = 'pl.select.osoba_mc.frekw'

	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.set.abs.F'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik gdy '' to wszystkich
			_Param_A	Dzien F-ki
			_Param_B	<> '' to wycofujemy F-ke
	CALL pl_frekw_tools('pl.set.abs.F', @GUIDPrac, '20120113', '', '', '');	
	CALL pl_frekw_tools('pl.set.abs.F', @GUIDPrac, '20120113', '1', '', '');	
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'pl.set.abs.F') THEN


	-- 		LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
	
		SET
			-- @pl.set.abs.YM = 100*YEAR(_Param_A) + MONTH(_Param_A),
			@ng_GUID = '',
			@ng_GUID_kp_F = '',
			@fk_GUID = '',
			@fk_GUID_kp_F = ''
		;	
		
		SELECT
			p.GUID, p.GUID_kp_F
		INTO
			@fk_GUID, @fk_GUID_kp_F
		FROM `frekw` n 
		INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
		WHERE
			n.PracGUID = _Key
			AND 
			n.Rok = YEAR(_Param_A)
			AND 
			n.Miesiac = MONTH(_Param_A)
			AND NOT n.Deleted
			AND NOT n.Zamkniety
			-- (n.Rok*100 + n.Miesiac) = _Param_A
			AND 
			p.rok_msc_dzien = _Param_A
		LIMIT 1	
		;
		IF IFNULL(@fk_GUID, '') = '' THEN
			LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
		END IF;
		
		IF _Param_B != '' THEN
		--	Przywracam do pola GUID_kp_F wartosc pola GUID
			UPDATE `frekw_po` p
			SET
				p.GUID_kp_F = p.GUID,
				p.kp_info_F = p.kp_info,
				p.kp_godziny_F = p.kp_godziny,
				p.dzien_wolny_F = p.dzien_wolny
			WHERE
				p.GUID IN (@fk_GUID, @fk_GUID_kp_F)
			;	
			LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
		ELSE
		--	wstawiam fk-ę 
		--	zatem jest @fk_GUID <> @fk_GUID_kp_F to OZNACZA ŻE JUZ JEST fka 
			IF @fk_GUID != @fk_GUID_kp_F THEN
				LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
			END IF;	
		END IF;
		
		SELECT
			p.GUID, p.GUID_kp_F
		INTO
			@ng_GUID, @ng_GUID_kp_F
		FROM `frekw` n 
		INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
		WHERE
			n.PracGUID = _Key
			AND 
			n.Rok = YEAR(_Param_A)
			AND 
			n.Miesiac = MONTH(_Param_A)
			AND NOT n.Deleted
			AND NOT n.Zamkniety
			AND 
			p.GUID = p.GUID_kp_F
			AND 
			p.dzien_wolny = 1 
			AND 
			p.godziny > 0
		ORDER BY dzien	
		LIMIT 1	
		;
		IF IFNULL(@ng_GUID, '') = '' THEN
			LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
		END IF;
	--	ZMIEN	
		--	1. pracujący
		UPDATE `frekw_po` p, `frekw_po` p2
		SET
			p.GUID_kp_F = @fk_GUID,
			p.kp_info_F = p2.kp_info, 
			p.kp_godziny_F = p2.kp_godziny, 
			p.dzien_wolny_F = p2.dzien_wolny
		WHERE
			p.GUID = @ng_GUID
			AND
			p2.GUID = @fk_GUID
		;	
		--	2. f-ka
		UPDATE `frekw_po` p, `frekw_po` p2
		SET
			p.GUID_kp_F = @ng_GUID,
			p.kp_info_F = p2.kp_info, 
			p.kp_godziny_F = p2.kp_godziny, 
			p.dzien_wolny_F = p2.dzien_wolny
		WHERE
			p.GUID = @fk_GUID
			AND
			p2.GUID = @ng_GUID
		;	
	END IF;	--	IF _MODE = 'pl.set.abs.F'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.tool.update.Fki'
	Wstaw z crm-a
		Parametry
			_Key		Pracownik MOŻE BYĆ = '' !!!!!!!!!!!!
			_Param_A	Miesiac w postaci YM (np. 201106 to czerwiec 2011)
			_Param_B	<> '' to wycofujemy F-ke 
	CALL pl_frekw_tools('pl.tool.update.Fki', 'Prac28', '201112', '', '', '');		-- dla pracownika i miesiaca
		CALL pl_frekw_tools('pl.tool.update.Fki.SetOff', _Key, _Param_A, '', '', '');	
		CALL pl_frekw_tools('pl.tool.update.Fki.SetOn', _Key, _Param_A, '', '', '');	
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'pl.tool.update.Fki%') THEN

		IF (_MODE = 'pl.tool.update.Fki') THEN
			CALL pl_frekw_tools('pl.tool.update.Fki.SetOff', _Key, _Param_A, '', '', '');	
			CALL pl_frekw_tools('pl.tool.update.Fki.SetOn', _Key, _Param_A, '', '', '');	
			LEAVE SPROC;	
		END IF;	--	IF _MODE = 'pl.tool.update.Fki'
	
		IF (_MODE = 'pl.tool.update.Fki.SetOff') THEN
		--	Odkręcamy o ile co kolwiek było
			UPDATE `frekw` n, `frekw_po` p
			SET
				p.GUID_kp_F = p.GUID,
				p.kp_info_F = p.kp_info,
				p.kp_godziny_F = p.kp_godziny,
				p.dzien_wolny_F = p.dzien_wolny,
				p.godziny150 = 0,
				p.godziny200 = 0
			WHERE
				(n.Rok*100 + n.Miesiac) = _Param_A
				AND IF(_Key != '', n.PracGUID = _Key, TRUE)
				AND p.FrekwGUID = n.GUID
			;
			LEAVE SPROC;	
		END IF;	--	IF _MODE = 'pl.tool.update.Fki.SetOff'
		
		IF (_MODE = 'pl.tool.update.Fki.SetOn') THEN
		--	Pętla naliczenia wszystkich F-ek 
			BEGIN
				DECLARE _OsobaGUID VARCHAR(38);
				DECLARE _rok_msc_dzien DATETIME;
				DECLARE _done BOOL DEFAULT FALSE;
				DECLARE _c_proc CURSOR FOR
					SELECT
						n.PracGUID, p.rok_msc_dzien 
					FROM `frekw` n
					INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
					WHERE
						(n.Rok*100 + n.Miesiac) = _Param_A
						AND IF(_Key != '', n.PracGUID = _Key, TRUE)
						AND p.Absencja = 'F'
						AND NOT n.Deleted
						AND NOT n.Zamkniety
					ORDER BY p.rok_msc_dzien	
					;
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
				OPEN _c_proc;
					loop_Update_fki: LOOP
						FETCH _c_proc INTO
							_OsobaGUID, _rok_msc_dzien;
						IF _done THEN
							LEAVE loop_Update_fki;
						ELSE
							CALL pl_frekw_tools('pl.set.abs.F', _OsobaGUID, _rok_msc_dzien, '', '', '');	
						END IF;
					END LOOP;
				CLOSE _c_proc;
			END;			
		END IF;	--	IF _MODE = 'pl.tool.update.Fki.SetOn'
		
	END IF;	--	IF _MODE = 'pl.tool.update.Fki'
	




/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'pl.tool.update.absencje.F'
	Wstaw z crm-a
		Parametry
			_Key		GUID frekwencji  N I E  MOŻE BYĆ = '' !!!!!!!!!!!!
	CALL pl_frekw_tools('pl.tool.update.absencje.F', 'FrekwGUID', '', '', '', '');		
		CALL pl_frekw_tools('pl.tool.update.absencje.F.SetOff', _Key, '', '', '', '');	
		CALL pl_frekw_tools('pl.tool.update.absencje.F.SetOn', _Key, '', '', '', '');	
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'pl.tool.update.absencje.F%') THEN

		IF (_MODE = 'pl.tool.update.absencje.F') THEN
			CALL pl_frekw_tools('pl.tool.update.absencje.F.SetOff', _Key, '', '', '', '');	
			CALL pl_frekw_tools('pl.tool.update.absencje.F.SetOn', _Key, '', '', '', '');	
			LEAVE SPROC;	
		END IF;	--	IF _MODE = 'pl.tool.update.absencje.F'
	
		IF (_MODE = 'pl.tool.update.absencje.F.SetOff') THEN
		--	Odkręcamy o ile co kolwiek było
		/*
			UPDATE `frekw_po` p
			SET
				p.godziny150 = 0,
				p.godziny200 = 0
			WHERE
				p.FrekwGUID = _Key
			;
		*/	
			UPDATE `frekw_po` p
			SET
				p.GUID_kp_F = p.GUID,
				p.kp_info_F = p.kp_info,
				p.kp_godziny_F = p.kp_godziny,
				p.dzien_wolny_F = p.dzien_wolny
			WHERE
				p.FrekwGUID = _Key
			;
			LEAVE SPROC;	
		END IF;	--	IF _MODE = 'pl.tool.update.absencje.F.SetOff'
		
		IF (_MODE = 'pl.tool.update.absencje.F.SetOn') THEN
		--	Pętla naliczenia wszystkich F-ek 
			BEGIN
				DECLARE _FrekwPoGUID VARCHAR(38);
				DECLARE _OsobaGUID VARCHAR(38);
				DECLARE _rok_msc_dzien DATETIME;
				DECLARE _done BOOL DEFAULT FALSE;
				DECLARE _c_proc CURSOR FOR
					SELECT
						p.GUID, n.PracGUID, p.rok_msc_dzien 
					FROM `frekw` n
					INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
					WHERE
						n.GUID = _Key
						AND p.Absencja = 'F'
						AND NOT n.Deleted
						AND NOT n.Zamkniety
					ORDER BY p.rok_msc_dzien	
					;
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;
				OPEN _c_proc;
					loop_Update_fki: LOOP
						FETCH _c_proc INTO
							_FrekwPoGUID, _OsobaGUID, _rok_msc_dzien;
						IF _done THEN
							LEAVE loop_Update_fki;
						ELSE
							CALL pl_frekw_tools('pl.tool.update.absencje.F.update.rec', _FrekwPoGUID, '', '', '', '');	
						END IF;
					END LOOP;
				CLOSE _c_proc;
			END;			
		END IF;	--	IF _MODE = 'pl.tool.update.absencje.F.SetOn'

		IF (_MODE = 'pl.tool.update.absencje.F.update.rec') THEN
		--	UWAGA tutaj _Key to GUID frekwpo
			SET
				@ng_GUID = '',
				@ng_GUID_kp_F = '',
				@fk_GUID = '',
				@fk_GUID_kp_F = ''
			;	
			
			SELECT
				p.GUID, p.GUID_kp_F, p.FrekwGUID, p.rok_msc_dzien
			INTO
				@fk_GUID, @fk_GUID_kp_F, @fk_FrekwGUID, @fk_rok_msc_dzien
			FROM `frekw_po` p
			WHERE
				p.GUID = _Key
			LIMIT 1	
			;
			IF IFNULL(@fk_GUID, '') = '' THEN
				LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
			END IF;
			
			--	wstawiam fk-ę 
			--	zatem jest @fk_GUID <> @fk_GUID_kp_F to OZNACZA ŻE JUZ JEST fka 
			IF @fk_GUID != @fk_GUID_kp_F THEN
				LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
			END IF;
			
			SELECT
				p.GUID, p.GUID_kp_F
			INTO
				@ng_GUID, @ng_GUID_kp_F
			FROM `frekw_po` p
			WHERE
				p.FrekwGUID = @fk_FrekwGUID
				AND 
				p.dzien_wolny = 1 			--	DNI WOLNE I ...
				AND 
				p.godziny > 0 				--	NADGODZINY  I ...
				AND 
				p.GUID = p.GUID_kp_F		--	NIE OBSADZONE PRZEZ F-kę i NIE BĘDĄCE F-ką
			ORDER BY dzien	
			LIMIT 1	
			;
			IF IFNULL(@ng_GUID, '') = '' THEN
				LEAVE SPROC;	--	wychodze bo nie mam nic do zapisywania
			END IF;
		--	ZMIEN	
			--	1. pracujący
			UPDATE `frekw_po` p, `frekw_po` p2
			SET
				p.GUID_kp_F = @fk_GUID,
				p.kp_info_F = p2.kp_info, 
				p.kp_godziny_F = p2.kp_godziny, 
				p.dzien_wolny_F = p2.dzien_wolny
			WHERE
				p.GUID = @ng_GUID
				AND
				p2.GUID = @fk_GUID
			;	
			--	2. f-ka
			UPDATE `frekw_po` p, `frekw_po` p2
			SET
				p.GUID_kp_F = @ng_GUID,
				p.kp_info_F = p2.kp_info, 
				p.kp_godziny_F = p2.kp_godziny, 
				p.dzien_wolny_F = p2.dzien_wolny
			WHERE
				p.GUID = @fk_GUID
				AND
				p2.GUID = @ng_GUID
			;	
		
		
			LEAVE SPROC;	
		END IF;	--	IF _MODE = 'pl.tool.update.absencje.F.update.rec'
		
	END IF;	--	IF _MODE = 'pl.tool.update.absencje.F%'
	
	
	


	
	IF (_MODE = 'pl.test.jarz.fki') THEN
		SET @_Key_X = 0;
		SET @OldPracGUID := '';
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_poFki`;
		CREATE TEMPORARY TABLE `t_frekw_poFki` 
			SELECT
				CONCAT(n.PracGUID, '_', IF(@OldPracGUID != n.PracGUID, @_Key_X := 1, @_Key_X := @_Key_X + 1)) AS Key_X,
				IF(dzien_wolny = 1 AND godziny200 > 0, 1, 0) AS Fka,
				@OldPracGUID := n.PracGUID AS OldPracGUID_,
				n.PracGUID AS PracGUID,
				-- @PracGUID := n.PracGUID AS PracGUID,
				p.GUID AS GUID_Nadg, 
				p.* 
				-- @_pseudo_klucz:=@_pseudo_klucz+1,0)) as pseudo_klucz
			FROM `frekw` n 
			INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
			WHERE
				(n.Rok*100 + n.Miesiac) = 201110
				AND NOT n.Deleted
				AND NOT n.Zamkniety
				AND p.Absencja = 'F'
			ORDER BY n.PracGUID, p.IDN	
		;
		ALTER TABLE `t_frekw_poFki`
			ADD KEY Key_X (Key_X),
			ADD KEY GUID_Nadg (GUID_Nadg),
			ADD KEY PracGUID (PracGUID)
		;	
		UPDATE `t_frekw_poFki` f
		SET
			f.GUID_Nadg = ''
		WHERE
			f.Absencja = 'F'
		;	
		
		
		-- select @_Key_Fki, f.* FROm t_frekw_poFki f ORDER BY PracGUID;
		SET @_Key_X = 0;
		SET @OldPracGUID = '';
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_poNadg`;
		CREATE TEMPORARY TABLE `t_frekw_poNadg` 
			SELECT
				CONCAT(n.PracGUID, '_', IF(@OldPracGUID != n.PracGUID, @_Key_X := 1, @_Key_X := @_Key_X + 1)) AS Key_X,
				@OldPracGUID := n.PracGUID AS OldPracGUID_,
				n.PracGUID AS PracGUID,
				-- @PracGUID := n.PracGUID AS PracGUID,
				p.GUID AS GUID_Nadg, 
				p.* 
				-- @_pseudo_klucz:=@_pseudo_klucz+1,0)) as pseudo_klucz
			FROM `frekw` n 
			INNER JOIN `frekw_po` p ON p.FrekwGUID = n.GUID
			WHERE
				(n.Rok*100 + n.Miesiac) = 201110
				AND NOT n.Deleted
				AND NOT n.Zamkniety
				AND p.dzien_wolny = 1 AND p.godziny200 > 0
			ORDER BY n.PracGUID, p.IDN	
		;
		ALTER TABLE `t_frekw_poNadg`
			ADD KEY Key_X (Key_X),
			ADD KEY GUID_Nadg (GUID_Nadg),
			ADD KEY PracGUID (PracGUID)
		;	
		UPDATE `t_frekw_poNadg` f
		SET
			f.GUID_Nadg = ''
		WHERE
			f.Absencja = 'F'
		;	
		
		UPDATE `t_frekw_poFki` f
		SET
			f.GUID_Nadg = (
				SELECT GUID FROM `t_frekw_poNadg` f2
				WHERE
					f2.Key_X = f.Key_X
				LIMIT 1	
					
			)
		;	
			/*
				SELECT 
					f.Absencja, f.kp_info, f.kp_godziny, f.dzien_wolny
				,
					f.rok_msc_dzien AS Dzien_Fki,
					f2.rok_msc_dzien AS dzien_Nadg,
					f2.dzien_wolny, f2.godziny200,
					f2.PracGUID,
					f.*
				FROM `t_frekw_poFki` f
				LEFT JOIN `t_frekw_poNadg` f2 ON f2.GUID = f.GUID_Nadg
				WHERE
					TRUE
					AND f.Absencja = 'F'
				ORDER BY f.PracGUID, f.idn	
				;
			*/	
		--	Nalcz nadg
		UPDATE `t_frekw_poNadg` fn, `t_frekw_poFki` ff, `frekw_po` f
		SET	
			fn.kp_info = f.kp_info, 
			fn.kp_godziny = f.kp_godziny, 
			fn.dzien_wolny = f.dzien_wolny
		WHERE
			f.GUID = ff.GUID
			AND
			ff.GUID_Nadg = fn.GUID
		;
		--	Nalcz fki
		UPDATE `t_frekw_poFki` ff, `t_frekw_poNadg` fn, `frekw_po` f
		SET	
			ff.kp_info = f.kp_info, 
			ff.kp_godziny = f.kp_godziny, 
			ff.dzien_wolny = f.dzien_wolny
		WHERE
			f.GUID = fn.GUID
			AND
			ff.GUID_Nadg = fn.GUID
		;

		SELECT f.kp_info, f.kp_godziny, f.dzien_wolny, f.* FROM t_frekw_poFki f ORDER BY PracGUID;
		SELECT f.kp_info, f.kp_godziny, f.dzien_wolny, f.* FROM t_frekw_poNadg f ORDER BY PracGUID;


		--	Nalcz z fki
		UPDATE `frekw_po` f, `t_frekw_poFki` ff
		SET	
			f.kp_info = ff.kp_info, 
			f.kp_godziny = ff.kp_godziny, 
			f.dzien_wolny = ff.dzien_wolny
		WHERE
			f.GUID = ff.GUID
		;
		--	Nalcz z nadg
		UPDATE `frekw_po` f, `t_frekw_poNadg` fn
		SET	
			f.kp_info = fn.kp_info, 
			f.kp_godziny = fn.kp_godziny, 
			f.dzien_wolny = fn.dzien_wolny
		WHERE
			f.GUID = fn.GUID
		;
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_poFki`;
		DROP TEMPORARY TABLE IF EXISTS `t_frekw_poNadg`;
	END IF;	--	IF _MODE = 'pl.test.jarz.fki'
	
/*
  CALL pl_frekw_tools('pl.jarz.GetData',@PracGUID, @Rok, @Msc,'','pracGUID');
  CALL pl_frekw_tools('pl.jarz.GetData',@frekwGUID, '', '','','');

*/	
	IF (_MODE = 'pl.jarz.GetData') THEN
		SET _SQLText =  
		'SELECT 
			MAX(F.godz100prac)/60 as godz100, 
			SUM(PO.kp_godziny)/60 as NCP, 
			SUM(PO.godziny)/60 as CP, 
            Max(F.godziny_pracy) as GodzinyPracy, 
			SUM(PO.noc)/60 as GodzNocne, 
            (Max(F.godz_plus50prac/60) + Max(F.godz_plus50wolne/60)) AS godz150, 
            (Max(F.godz_plus100prac/60) + Max(F.godz_plus100wolne/60)) AS godz200, 
            (SUM(if(PO.kp_godziny=0 ,PO.godziny, 0))/60) as PracaWolne, 
            (SUM(IF(PO.absencja IN ("E", "P", ""), PO.kp_godziny, 0))/60) AS godz_prod, 
            
            (SUM(IF(PO.absencja IN ("E", "P", "") , 0, PO.kp_godziny))/60) AS dni_absencji,
			(SUM(IF(absencja IN ("P"), kp_godziny, 0))/60) AS godzP,
			0 as zerox,
			F.StawkaDlaKolek as StawkaDlaKolek,
			F.WynagrNadGodz,
			F.StawkaGodzinowa,
			F.PlacaZasad 
        FROM 
			frekw F 
        left join 
			frekw_po PO on PO.FrekwGUID = F.GUID 
        WHERE 
			IF(#paramd# <> "pracGUID", 
				F.GUID = #guid#,
			F.PracGUID = #guid#
			and F.rok = #rok# 
			and F.miesiac = #msc#
			) 
			and F.deleted = 0
		';
		/*
		StawkaGodzinowa = SG1
		
		*/
		
		SET _SQLText = REPLACE(_SQLText, '#paramd#', concat("'",_Param_D,"'"));	
		SET _SQLText = REPLACE(_SQLText, '#guid#', concat("'",_Key,"'"));	
		SET _SQLText = REPLACE(_SQLText, '#rok#',  concat("'",_Param_A,"'"));	
		SET _SQLText = REPLACE(_SQLText, '#msc#',  concat("'",_Param_B,"'"));	
		SET @_SQLText = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
				
			
	END IF;	--	IF _MODE = 'pl.jarz.GetData'
	IF (_MODE = 'pl.jarz.GetData.Print') THEN
		SET _SQLText =  
		'SELECT 
			_pl_Min2Godz_Full(MAX(F.godz100prac)) as godz100, 
			_pl_Min2Godz_Full(SUM(PO.kp_godziny)) as NCP, 
			_pl_Min2Godz_Full(SUM(PO.godziny)) as CP, 
            _pl_Min2Godz_Full(Max(F.godziny_pracy)) as GodzinyPracy, 
			_pl_Min2Godz_Full(SUM(PO.noc)) as GodzNocne, 
            _pl_Min2Godz_Full(Max(F.godz_plus50prac) + Max(F.godz_plus50wolne)) AS godz150, 
            _pl_Min2Godz_Full(Max(F.godz_plus100prac) + Max(F.godz_plus100wolne)) AS godz200, 
            _pl_Min2Godz_Full(SUM(if(PO.kp_godziny=0 ,PO.godziny, 0))) as PracaWolne, 
            _pl_Min2Godz_Full(SUM(IF(PO.absencja IN ("E", "P", ""), PO.kp_godziny, 0))) AS godz_prod, 
            
            _pl_Min2Godz_Full(SUM(IF(PO.absencja IN ("E", "P", "") , 0, PO.kp_godziny))) AS dni_absencji,
			_pl_Min2Godz_Full(SUM(IF(absencja IN ("P"), kp_godziny, 0))) AS godzP,
			_rnd2(F.StawkaDlaKolek) as StawkaDlaKolek,
			_rnd2(F.WynagrNadGodz)as WynagrNadGodz ,
			_rnd2(F.StawkaGodzinowa) as StawkaGodzinowa,
			_rnd2(F.PlacaZasad ) as PlacaZasad,
			_rnd2(SUM(IF(PO.absencja IN ("E", "P", ""), PO.kp_godziny, 0))*F.StawkaGodzinowa) AS PlacaZasadnicza, 
			_rnd2((SUM(PO.godziny)-SUM(IF(PO.absencja IN ("E", "P", ""), PO.kp_godziny, 0))) * F.StawkaDlaKolek) as PlacaZlecenie
			
			
        FROM 
			frekw F 
        left join 
			frekw_po PO on PO.FrekwGUID = F.GUID 
        WHERE 
			IF(#paramd# <> "pracGUID", 
				F.GUID = #guid#,
			F.PracGUID = #guid#
			and F.rok = #rok# 
			and F.miesiac = #msc#
			) 
			and F.deleted = 0
		';
		/*
		StawkaGodzinowa = SG1
		
		*/
		
		SET _SQLText = REPLACE(_SQLText, '#paramd#', concat("'",_Param_D,"'"));	
		SET _SQLText = REPLACE(_SQLText, '#guid#', concat("'",_Key,"'"));	
		SET _SQLText = REPLACE(_SQLText, '#rok#',  concat("'",_Param_A,"'"));	
		SET _SQLText = REPLACE(_SQLText, '#msc#',  concat("'",_Param_B,"'"));	
		SET @_SQLText = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
				
			
	END IF;	--	IF _MODE = 'pl.jarz.GetData'
END$$

DELIMITER ;

/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================

DROP TABLE `hr_events`;
CREATE TABLE `hr_events` (
  `idn` INT(11) NOT NULL AUTO_INCREMENT COMMENT 'IDN',
  `GUID` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'Unikalny ID - moze pochodzic np. rcp lub z absencji lub z zadania crm',
  `event_source` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'skad pochodzi wpis "" wpis reczny, "RCP","ABS","CRM", etc ',
  `event_type` VARCHAR(10) NOT NULL DEFAULT '' COMMENT 'typ zdarzenia "WORK","WORK_PLAN","JOB","ABS","CRM", etc ',
  `rodzaj` INT(11) NOT NULL DEFAULT '0' COMMENT 'rodzaj np. z `crm_commisions` lub `absencje_dict`',
  `podrodzaj` INT(11) NOT NULL DEFAULT '0' COMMENT 'podrodzaj np. z `crm_commisions`',
  `StartTime` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00'  COMMENT 'Rozpoczecie',
  `EndTime` DATETIME DEFAULT NULL  COMMENT 'Zakonczenie',
  `duration_min` DOUBLE(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'czas w min.',
  `duration_sec` DOUBLE(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'czas w sek.',
  `duration_hour` DOUBLE(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'czas w godz.',
  `osobaguid` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'Pracownik (posiadacz karty)',
  `status` INT(11) NOT NULL DEFAULT '0',
  `Progress` DOUBLE(16,2) DEFAULT NULL,
  `alocaltime` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `SrcGUID` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'Zrodlowy ID - na wszelki wypadek',
  
  PRIMARY KEY (`idn`),
  UNIQUE KEY `GUID` (`GUID`),
  KEY `StartTime` (`StartTime`),
  KEY `osobaguid` (`osobaguid`),
  KEY `duration_min` (`duration_min`)
) ENGINE=MYISAM AUTO_INCREMENT=49 DEFAULT CHARSET=cp1250
;



SELECT * FROM `hr_events`;

SET
 @GUIDcrm = '{32CC84CA-92EF-4CE5-9943-946C7704396D}'
-- @GUIDcrm = '{6323EEEF-ED3D-4EDE-8472-1E22E62236CC}'
-- @GUIDcrm = '{75CEEDC0-F2D2-4453-95C8-32972A4A7EF1}'
;
SELECT * FROM crm_commisions crm
WHERE rodzaj = 5 AND EXISTS (SELECT IDN FROM `crm_usr_links` cul WHERE cul.commisionGUID = crm.GUID AND cul.Poziom = 2);
CALL pl_frekw_tools('hr_update.from.crm', @GUIDcrm, '', '', '', '');	

		SELECT
			MD5(CONCAT(crm.GUID, cul.osoba)), 'CRM', 'CRM', crm.rodzaj, crm.podrodzaj, crm.StartTime, crm.EndTime, 
			TIMESTAMPDIFF(MINUTE, crm.StartTime, crm.EndTime), TIMESTAMPDIFF(SECOND, crm.StartTime, crm.EndTime), TIMESTAMPDIFF(HOUR, crm.StartTime, crm.EndTime), 
			cul.osoba, crm.status, crm.Progress, crm.GUID
		FROM `crm_commisions` crm
		LEFT JOIN `crm_usr_links` cul ON cul.commisionGUID = crm.GUID AND cul.Poziom = 2 AND IF('' != '', cul.osoba = '', TRUE) 
		WHERE
			crm.GUID = @GUIDcrm -- AND 
			-- cul.osoba IS NOT NULL
		;	
SELECT * FROM `hr_events`;


cInsCRM_SQL =
'insert ignore into crm_commisions set guid = ''%s'', rodzaj = 10, podrodzaj = 8 + %d, '+
  'StartTime = %s, EndTime = %s, duration = %d, temat = ''%s'', opis = ''%s'' ';
 cUpdtCRM_SQL =
  'update crm_commisions set podrodzaj = 8 + %d, StartTime = %s, EndTime = %s, '+
  'duration = %d, temat = ''%s'', opis = ''%s'' where guid = ''%s'' ';
 cInsUsrLinksSQL =
  'insert ignore into crm_usr_links set osoba = ''%s'', commisionGUID = ''%s'', poziom = %d ';


SELECT * FROM absencje_dict -> tu masz kolumne crm_subrodzaj i TO trafia DO podrodzah
a rodzaj nieobcności trafia DO pól "temat" i "opis"




DESC absencje_dict;
DESC absencje;
SELECT
	a.guid,
	8 AS Rodzaj,
	d.crm_subrodzaj AS PodRodzaj,
	_VarDateTime(a.AbsencjaOd) AS StartTime, 
	_VarDateTime(CONCAT(a.AbsencjaDo, ' 23:59:59')) AS EndTime, 
	(TIMESTAMPDIFF(MINUTE, _VarDateTime(a.AbsencjaOd), _VarDateTime(CONCAT(a.AbsencjaDo, ' 23:59:59'))) + 1)/60 AS duration,
	d.definicja AS Temat,
	d.Symbol AS Opis
FROM `absencje` a
INNER JOIN `absencje_dict` d ON d.IDN = a.Rodzaj
LIMIT 100;


		INSERT INTO `crm_commisions` 
		(GUID, Rodzaj, PodRodzaj, StartTime, EndTime, duration, Temat, Opis, Progress, STATUS)
			SELECT
				a.guid,
				10 AS Rodzaj,
				8 + d.crm_subrodzaj AS PodRodzaj,
				_VarDateTime(a.AbsencjaOd) AS StartTime, 
				_VarDateTime(CONCAT(a.AbsencjaDo, ' 23:59:59')) AS EndTime, 
				(TIMESTAMPDIFF(MINUTE, _VarDateTime(a.AbsencjaOd), _VarDateTime(CONCAT(a.AbsencjaDo, ' 23:59:59'))) + 1)/60 AS duration,
				d.definicja AS Temat,
				d.Symbol AS Opis,
				0,
				0
			FROM `absencje` a
			INNER JOIN `absencje_dict` d ON d.IDN = a.Rodzaj
			WHERE
				a.GUID = @_Key
		ON DUPLICATE KEY
			UPDATE
				podrodzaj = 8 + d.crm_subrodzaj,
				StartTime = _VarDateTime(a.AbsencjaOd), 
				EndTime = _VarDateTime(CONCAT(a.AbsencjaDo, ' 23:59:59')), 
				duration = (TIMESTAMPDIFF(MINUTE, _VarDateTime(a.AbsencjaOd), _VarDateTime(CONCAT(a.AbsencjaDo, ' 23:59:59'))) + 1)/60, 
				temat = d.definicja, 
				opis = d.Symbol,
				Progress = 0, 
				STATUS = 0
		;	
		
		INSERT IGNORE INTO `crm_usr_links` 
		(osoba, commisionGUID, poziom) 
			SELECT
				a.GUIDpr,
				a.GUID,
				2
			FROM `absencje` a
			WHERE
				a.GUID = @_Key
		;
		
SELECT * FROM crm_commisions WHERE guid = @_Key;		
SELECT * FROM crm_usr_links WHERE commisionGUID = @_Key;		
DESC crm_usr_links;
SET 
@_Key = 'Abs011996Prac9_1996/01/01_1996/01/31'
;

CALL pl_frekw_tools('hr_update.from.absencje', 'Abs011996Prac15_1996/01/01_1996/01/14', '', '', '', '');	

		INSERT INTO `hr_events`
			(GUID, event_source, event_type, rodzaj, podrodzaj, StartTime, EndTime, duration_min, duration_sec, duration_hour, osobaguid, STATUS, Progress, SrcGUID)
			SELECT
				MD5(CONCAT(crm.GUID, cul.osoba)), 'ABS', 'ABS', crm.rodzaj, crm.podrodzaj, crm.StartTime, crm.EndTime, 
				TIMESTAMPDIFF(MINUTE, crm.StartTime, crm.EndTime), TIMESTAMPDIFF(SECOND, crm.StartTime, crm.EndTime), TIMESTAMPDIFF(HOUR, crm.StartTime, crm.EndTime), 
				cul.osoba, crm.status, crm.Progress, crm.GUID
			FROM `crm_commisions` crm
			INNER JOIN `crm_usr_links` cul ON cul.commisionGUID = crm.GUID AND cul.Poziom = 2 
			WHERE
				crm.GUID = @_Key AND 
				cul.osoba IS NOT NULL
		ON DUPLICATE KEY
			UPDATE
				StartTime = crm.StartTime,
				EndTime = crm.EndTime,
				duration_min = TIMESTAMPDIFF(MINUTE, crm.StartTime, crm.EndTime),
				duration_sec = TIMESTAMPDIFF(SECOND, crm.StartTime, crm.EndTime),
				duration_hour = TIMESTAMPDIFF(HOUR, crm.StartTime, crm.EndTime),
				STATUS = crm.status, 
				Progress = crm.Progress
		;	


SELECT * FROM `hr_events` WHERE event_type = 'ABS';


@session.globals.pl.FrekwCalc.FromRCP.Mode


UPDATE `hr_events`
SET
duration_min_only = TIMESTAMPDIFF(MINUTE, StartTime, EndTime),
duration_min_pl = TIMESTAMPDIFF(MINUTE, _RndDATETIME('MIN TRUNC', StartTime), _RndDATETIME('MIN TRUNC', EndTime))
;

CALL _Session_Refresh_Variables('', '', '');
CALL _Session_Refresh_Variables('PL', '', '');
CALL pl_frekw_tools('pl.nalicz.mc.frekw', '', '201110', '', '', '');	
CALL pl_frekw_tools('pl.nalicz.mc.frekw', '', '201111', '', '', '');	

CALL _Session_Refresh_Variables('', '', '');
SELECT * FROM `osoby` WHERE Nazwisko LIKE 'Rusze%';
SET 
	@_Param_A = '201112',
	@_OsobaGUID = '{16559E52-600A-42A1-9EC6-C35910A0E324}' -- 'Prac28'
;
/SET  @_Key = @_OsobaGUID;

	_VarInt(CASE WHEN e.event_type = 'WORK' THEN 0 WHEN e.event_type = 'ABS' THEN 1 WHEN e.event_type = 'PLAN' THEN 2 ELSE 99 END) AS event_type_int2,

	
CALL _Session_Refresh_Variables('', '', '');
SET @Prac = 'Prac53';
SELECT * FROM frekw WHERE PracGUID = @Prac AND rok = 2012 AND Miesiac = 3;

SET @Frekw = '{D223B66D-25E4-4C48-B9BF-EA93C8C7F1FA}';
SELECT * FROM frekw_po WHERE FrekwGUID = @Frekw;

CALL pl_frekw_tools('pl.nalicz.mc.frekw', '', '201203', '', '', '');	

CALL pl_frekw_tools('pl.nalicz.mc.frekw', @Prac, '201203', '', '', '');	

SELECT * FROM osoby WHERE nazwisko LIKE '%szomb%';
SELECT * FROM `frekw_po`
WHERE 
FrekwGUID = '{781D2762-703A-479F-9823-79D68AA71647}'
;
SET 
@FrekwGUID = '{781D2762-703A-479F-9823-79D68AA71647}',
@Prac = '{72191475-10D6-4BA9-8D45-DF7F07B16C4F}'
;

SELECT 
e.event_type,
e.event_source,
e.GUID,
e.StartTime, e.StartTime_work, 
e.EndTime, e.EndTime_work, 
e.duration_min_night,
e.duration_min_night_work,
e.duration_min,
e.duration_min_work,
-- e.godz_yy,
e.* 
FROM  hr_events e 
WHERE 
osobaGUID = @Prac 
AND YEAR(starttime) = 2012 AND MONTH(starttime) = 5 
-- AND DAY(starttime) IN (1,6)
ORDER BY StartTime
;

SELECT * FROM `t_agg_frekw_prac` a;

CREATE TEMPORARY TABLE t_prac_nadg_narast
(
 PracGUID VARCHAR(38)  NOT NULL DEFAULT '', 
 nadg01   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg02   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg03   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg04   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg05   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg06   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg07   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg08   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg09   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg10   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg11   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg12   DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 nadg_rok DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
 KEY      PracGUID(PracGUID)
 );

INSERT INTO t_prac_nadg_narast (PracGUID)
SELECT DISTINCT PracGUID
FROM frekw WHERE rok = 2012 AND deleted = 0 AND SaldoNarastGodzNadrNiedPocz > 0;


	

*/
