DELIMITER $$

DROP PROCEDURE IF EXISTS `pl_frekw_pres_rcp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pl_frekw_pres_rcp`(
	IN _Mode VARCHAR(38), 
	IN _WhereSQL VARCHAR(250), 
	IN _Param_A VARCHAR(50),
	IN _Param_B VARCHAR(50),
	IN _Param_C VARCHAR(50),
	IN _Param_D VARCHAR(50))
SPROC: BEGIN
	DECLARE _SQLText TEXT;

	/* 
	 #041318 zestawienie porównawcze danych zarejestrowane ręcznie przez użytkownika z danymi wczytanymi z RCP:
	CALL pl_frekw_pres_rcp('ZREMBZestDiff', '', '2015', '11', '', '');
	*/
	IF _Mode = 'ZREMBZestDiff' THEN  -- , Id: IdZREMBzestDiff
	
		DROP TEMPORARY TABLE IF EXISTS t_frekw_diff ;
		
		CREATE TEMPORARY TABLE t_frekw_diff
		(
		  miesiac INTEGER NOT NULL DEFAULT 0,
		  guid VARCHAR(38) NOT NULL DEFAULT '',
		  pracownik VARCHAR(100) NOT NULL DEFAULT '',  
		  godz100 DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz100RCP DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz100Diff DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz150 DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz150RCP DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz150Diff DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz200 DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz200RCP DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz200Diff DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  noc DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  nocRCP DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  nocDiff DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godz DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godzRCP DOUBLE(16,4) NOT NULL DEFAULT 0.0000,
		  godzDiff DOUBLE(16,4) NOT NULL DEFAULT 0.0000
		);
		
		INSERT INTO t_frekw_diff
		SELECT f.miesiac, k.guid, CONCAT(o.nazwisko, ' ', o.imiona) ,
		  f.godz100prac  AS CPnomin, SUM(GREATEST(p.godziny_xx - p.kp_godziny, 0)) AS CPnominRCP, 0.00,  -- diff do przeliczenia 
		  f.godz_plus50prac AS godz150, SUM(p.godziny150RCP) AS godziny150RCP,  0.00,  	 -- diff do przeliczenia 
		  f.godz_plus100wolne AS godz200, SUM(p.godziny200RCP) AS godziny200RCP,  0.00,  -- diff do przeliczenia 
		  SUM(p.noc), SUM(p.godziny_yy), 0.00,  	-- diff do przeliczenia 
		  SUM(p.godziny), SUM(p.godziny_xx),  0.00  -- diff do przeliczenia   
		FROM frekw f, Pracownicy k, osoby o, frekw_po p
		WHERE 
		  f.Pracguid = k.guid AND o.guid = k.guid  AND p.frekwguid = f.guid 
		  AND rok = _varINT(_Param_A) AND miesiac = _varINT(_Param_B) AND f.deleted = 0
		  -- and k.guid = 'Prac247'
		  GROUP BY f.guid 		  ;
		  
		# naliczamy roznice:
		UPDATE t_frekw_diff SET
			godz100Diff = godz100 - godz100RCP ,
			godz150Diff = godz150 - godz150RCP ,
			godz200Diff = godz200 - godz200RCP ,
			nocDiff = noc - nocRCP,
			godzDiff = godz - godzRCP;
  
		SELECT * FROM t_frekw_diff   -- wszystkie wartosci w minutach
		ORDER BY pracownik;   
		
	END IF;

	/*
	CALL pl_frekw_pres_rcp('ZestCzasPracy', 'F.rok=2012 and F.miesiac=1', '','','','');
	*/
	IF _Mode = 'ZestCzasPracy' THEN

	SET _SQLText = '
			select 
				O.imiona, 
				O.nazwisko, 
				F.godzSNS, 
				SUM(FP.godziny_xx) as godz_xx, 
				SUM(FP.godziny_zz) as godz_zz, 
				SUM(FP.godziny_yy + FP.noc) as godz_yy_noc,
				_pl_Min2Godz(SUM(FP.godziny_yy + FP.noc)) as godz_yy_noc_str,
				_pl_Min2Godz(SUM(FP.godziny_zz)) as godz_zz_str,
				_pl_Min2Godz(F.godzSNS) as godzSNS_str,
				SUM(FP.godziny_yy) as godz_yy, 
				SUM(FP.noc) as godz_noc  
			from 
				frekw_po FP  
			join frekw F on F.GUID=FP.FrekwGUID  and F.deleted = 0 
			join osoby O on O.GUID=F.pracguid 
			where #WHERE#
			group by O.GUID order by O.Nazwisko, O.imiona';

		SET _SQLText = REPLACE(_SQLText, '#WHERE#', _WhereSQL);
		SET @_SQLText = _SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);	
	END IF;
	
END$$

DELIMITER ;
