DELIMITER $$

DROP PROCEDURE IF EXISTS `fr_pr_KRZ`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `fr_pr_KRZ`(
	IN _Symbol VARCHAR(38),
	IN _KrNgGUID VARCHAR(38), 
	IN _Param_A VARCHAR(50),	
	IN _Param_B VARCHAR(50),
	IN _Param_C VARCHAR(50),
	IN _Param_D VARCHAR(50)
	)

#IN _DataOd DATE, IN _DataDo DATE,
#IN _wydzial VARCHAR(20), IN _brygada VARCHAR(20), IN _GUID_pracownika VARCHAR(38))
BEGIN
	DECLARE S TEXT;
	SET @Symbol = UPPER(_Symbol);
	SET @Case_GUID = '';
	-- SET @Case_GUID = IFNULL(_Param_A, '');
	-- SET @Commision_GUID = _GUID;
	
	IF @Case_GUID = '' THEN
		SELECT
			kn.PodzlecGUID
		INTO	
			@Case_GUID
		FROM pr_krng kn
		WHERE	
			kn.GUID = _KrNgGUID
		;	
		SET @Case_GUID = IFNULL(@Case_GUID, '');
	END IF;
/*
****************************************************************************************************************************************************************************
	KRZ W1
****************************************************************************************************************************************************************************
*/
	IF _Symbol = 'W1' THEN
/* SQL RD  ----------------------------------------------------------------------------------------------------------*/
		#to pierwszy i jedyny dataset - frxDBUniversalOne
		CALL pr_TOB_Create_Temp_Tob(@Case_GUID, FALSE);
		CALL pr_tob_SUM(@Case_GUID);
		SELECT
			_VarStr(_ConcatSTR_NotEqu(an.Symbol, an.Nazwa,  ' - ')) As Gniazdo, 
			_VarStr(_ConcatSTR_NotEqu(wg.Nazwa, wg.Symbol, ' - ')) AS Brygada, 
			_VarStr(_ConcatSTR_NotEqu(d.symbol, d.Nazwa,  ' - ')) AS Wydzial,
			_VarStr(_ConcatSTR_NotEqu(vd1.symbol, vd1.Nazwa,  ' - ')) AS NawaWydzial,
			
			_VarINT(kp.Seria) AS Seria, 
			_VarDouble(kp.SeriaDone) AS SeriaDone, 
			_VarDouble(kp.Seria - kp.SeriaDone) AS SeriaToDo, 
			_VarDouble(kp.Progress*100) AS Progress100, 
			_VarDouble(kp.TimeCaps) AS TimeCaps, 
			_VarDouble(kp.TimeDone) AS TimeDone, 
			_VarDouble(kp.TimeDoneProc) AS TimeDoneProc,
			
			-- _VarDouble(SUM(rr.TimeCaps)) AS R_TimeCaps, 
			-- _VarDouble(SUM(rr.TimeDone)) AS R_TimeDone, 

			-- _VarDouble(kp.Progress) AS Progress, 
			sp.DisplayValue AS StatusStr,
			_VarStr(REPLACE(REPLACE(kp.Marszruta_G, '> |', '> <font color=black><b>'), '|', '</b></font>')) AS Marszruta_G,

			kn.nrwewn,
			kp.idn,
			kp.wartosc,
			
			v.nr_zlec, v.nazwa_zlec,
			@_OsobaTOP_Str as osoba_str,
			tob.rysunek_symbol AS rysunek,
			tob.element_symbol AS symb_elementu,
			tob.element_nazwa AS nazwa_elementu,
			tob.symbol AS symbol,
			_VarINT(kp.Seria) AS sztuk, 
			tob.ilosc_j AS masa_jedn,
			_VarDouble(tob.ilosc_j*kp.Seria) AS masa_Serii,
			tob.Asortyment_Nazwa,
			tob.Wymiar_X AS szerokosc,
			tob.Wymiar_Y AS dlugosc,
			tob.naddatek_minX * 1000 AS naddatek_minX,
			tob.naddatek_maxX * 1000 AS naddatek_maxX,
			tob.naddatek_minY * 1000 AS naddatek_minY,
			tob.naddatek_maxY * 1000 AS naddatek_maxY,
			tob.marszruta_op AS marszruta_op,
			tob.gat_stali,
			tob.atest,
			group_concat(top.uwagi) as opis_oper,
			-- GetPrevGniazdo(tob.GUID, top.lp_vc) AS prev_gniazdo
			kp.GUID, kp.krngGUID, kp.PodzlecGUID, kp.TobGUID, kp.AreaGUID, kp.lp_int, kp.lp_vc, kp.Rodzaj, kp.IsOBUN 
		FROM `pr_krpo` kp
		FROM `pr_krpo` kp
		inner JOIN `pr_krng` kn ON kn.GUID = kp.KrNgGUID
		inner JOIN `temp_pr_tob_view` tob ON tob.GUID = kp.tobGUID
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = kp.PodzlecGUID
		LEFT JOIN `pr_krpo_r` rr ON rr.krpoGUID = kp.GUID
		inner JOIN `erp_area_nodes` an ON an.GUID = kn.AreaGUID
		inner JOIN `erp_workgroups` wg ON wg.GUID = kn.WorkgroupGUID
		LEFT JOIN `v_pr_division` vd1 on vd1.GUID = an.dzial
		LEFT JOIN `erp_division_schema` d ON d.guid = vd1.GUID_Wydzialu
		LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kp.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
		LEFT JOIN `erp_asort_types` aso ON aso.GUID = tob.Asortyment
		LEFT JOIN `pr_techn_def_obj` def ON def.IDN = tob.Def_Obj
		left join zmpo top ON kp.GUID = top.pr_krpo_GUID
		WHERE
			kp.krngGUID = _KrNgGUID
			and kp.status in (0,1)
			/*
			and tob.kind IN ('D', 'SE') #AND (tob.GUID_serii IS NULL OR tob.GUID_serii = '') and
			and top.res_type = 'W' AND #top.wydzial='W-1' AND
			if(@Param_A='',true,top.rcpoGUID = @Param_A) and if(@Param_B='',true,tob.rysunek_GUID = @Param_B)
			and if(@Param_C='',true,tob.asortyment = @Param_C)
			and if(@Param_D='',true,top.znacznik_tob = @Param_D)
			*/
		GROUP BY kp.GUID	
		ORDER BY asortyment_nazwa, rysunek, symbol
		;	
	
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
/*
****************************************************************************************************************************************************************************
	KRZ W2
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W2' THEN
#to pierwszy i jedyny dataset - frxDBUniversalOne
		CALL pr_TOB_Create_Temp_Tob(@Case_GUID, FALSE);
		CALL pr_tob_SUM(@Case_GUID);
		SELECT
			_VarStr(_ConcatSTR_NotEqu(an.Symbol, an.Nazwa,  ' - ')) As Gniazdo, 
			_VarStr(_ConcatSTR_NotEqu(wg.Nazwa, wg.Symbol, ' - ')) AS Brygada, 
			_VarStr(_ConcatSTR_NotEqu(d.symbol, d.Nazwa,  ' - ')) AS Wydzial,
			_VarStr(_ConcatSTR_NotEqu(vd1.symbol, vd1.Nazwa,  ' - ')) AS NawaWydzial,
			
			_VarINT(kp.Seria) AS Seria, 
			_VarDouble(kp.SeriaDone) AS SeriaDone, 
			_VarDouble(kp.Seria - kp.SeriaDone) AS SeriaToDo, 
			_VarDouble(kp.Progress*100) AS Progress100, 
			_VarDouble(kp.TimeCaps) AS TimeCaps, 
			_VarDouble(kp.TimeDone) AS TimeDone, 
			_VarDouble(kp.TimeDoneProc) AS TimeDoneProc,
			
			-- _VarDouble(SUM(rr.TimeCaps)) AS R_TimeCaps, 
			-- _VarDouble(SUM(rr.TimeDone)) AS R_TimeDone, 

			-- _VarDouble(kp.Progress) AS Progress, 
			sp.DisplayValue AS StatusStr,
			_VarStr(REPLACE(REPLACE(kp.Marszruta_G, '> |', '> <font color=black><b>'), '|', '</b></font>')) AS Marszruta_G,

			kn.nrwewn,
			kp.idn,
			kp.wartosc,
			
			v.nr_zlec, v.nazwa_zlec,
			@_OsobaTOP_Str as osoba_str,
			tob.rysunek_symbol AS rysunek,
			tob.element_symbol AS symb_elementu,
			tob.element_nazwa AS nazwa_elementu,
			tob.symbol AS symbol,
			_VarINT(kp.Seria) AS sztuk, 
			tob.ilosc_j AS masa_jedn,
			_VarDouble(tob.ilosc_j*kp.Seria) AS masa_Serii,
			tob.Asortyment_Nazwa,
			tob.Wymiar_X AS szerokosc,
			tob.Wymiar_Y AS dlugosc,
			tob.marszruta_op AS marszruta,
			tob.gat_stali,
			tob.atest,
			-- GetPrevGniazdo(tob.GUID, top.lp_vc) AS prev_gniazdo
			kp.GUID, kp.krngGUID, kp.PodzlecGUID, kp.TobGUID, kp.AreaGUID, kp.lp_int, kp.lp_vc, kp.Rodzaj, kp.IsOBUN 
		FROM `pr_krpo` kp
		LEFT JOIN `pr_krng` kn ON kn.GUID = kp.KrNgGUID
		LEFT JOIN `temp_pr_tob_view` tob ON tob.GUID = kp.tobGUID
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = kp.PodzlecGUID
		LEFT JOIN `pr_krpo_r` rr ON (rr.krpoGUID = kp.GUID)
		LEFT JOIN `erp_area_nodes` an ON an.GUID = kn.AreaGUID
		LEFT JOIN `erp_workgroups` wg ON wg.GUID = kn.WorkgroupGUID
		LEFT JOIN `v_pr_division` vd1 on vd1.GUID = an.dzial
		LEFT JOIN `erp_division_schema` d ON d.guid = vd1.GUID_Wydzialu
		LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kp.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
		LEFT JOIN `erp_asort_types` aso ON aso.GUID = tob.Asortyment
		LEFT JOIN `pr_techn_def_obj` def ON def.IDN = tob.Def_Obj
		WHERE
			kp.krngGUID = _KrNgGUID
			and kp.status in (0,1)
		
		GROUP BY kp.GUID	
		ORDER BY asortyment_nazwa, rysunek, symbol
		;	
	ELSEIF _Symbol = 'W2_2' THEN
		# a to drugi dataset - frxDBUniversalTwo
		select 
			tob.symbol as rysunek,
			top.towrsymb,
			top.ilosc,
			top.wartnet
		
		FROM `pr_krpo` kp
		join pr_tob tob on tob.GUID=kp.TobGUID
		join zmpo top ON kp.GUID = top.pr_krpo_GUID
		WHERE
			kp.krngGUID = _KrNgGUID
			and kp.status in (0,1) and tob.def_obj=1
		ORDER BY tob.symbol, top.towrsymb
		;	
		
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
/*
****************************************************************************************************************************************************************************
	KP W2ZB
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W2ZB_1' THEN
/* SQL W2ZB_1 ----------------------------------------------------------------------------------------------------------*/
		#to pierwszy dataset - frxDBUniversalOne
		CALL pr_TOP_Create_Temp_Top(@Case_GUID, FALSE, FALSE);
		CALL pr_tob_SUM(@Case_GUID);
				
		SELECT c.GUID, c.data, c.nrwewn, c.idn,
			v.nr_zlec,
			ean.symbol AS gniazdo
		FROM crm_commisions c
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = c.case_GUID
		LEFT JOIN `erp_area_nodes` ean ON c.areaGUID = ean.guid # zasoby - gniazdo
		WHERE
			c.GUID = @Commision_GUID;

/* END SQL ----------------------------------------------------------------------------------------------------------*/
	ELSEIF _Symbol = 'W2ZB_2' THEN
/* SQL W2 _2  ----------------------------------------------------------------------------------------------------------*/
		# a to drugi dataset - frxDBUniversalTwo
		SELECT top.GUID,
		    IF(P.Kind = 'D', t2.symbol, t1.symbol) AS rysunek,
			IF(P.Kind = 'D', 'Z W-1', tob.symbol) AS element,
			tob.ilosc_c AS masa_jedn,
			tob.ilosc_seria as ilosc_seria,
			#tob.masa
			ean.symbol AS gniazdo,
		    SUM(top.ilosc) AS ilosc,
		    SUM(hur.kwota * top.ilosc) AS wartosc
		FROM zmpo top
		LEFT JOIN `pr_tob` tob ON tob.guid = top.tobguid
		LEFT JOIN `pr_tob` t1 ON t1.guid = tob.parentGUID
		LEFT JOIN `pr_tob` t2 ON t2.guid = t1.parentGUID
		LEFT JOIN `pr_techn_def_obj` P ON P.IDN = tob.Def_Obj
		LEFT JOIN `erp_area_nodes` ean ON top.areaGUID = ean.guid # zasoby - gniazdo
		LEFT JOIN `rcpo` rc ON rc.GUID = top.rcpoGUID        # definicja operacji i dane operacji
		LEFT JOIN `hr_unitprice_rate` hur ON hur.kod = rc.UnitPrice_Rate #stawka operacji, z calego lancuszka wyzej
		WHERE
		      top.commision_guid = @Commision_GUID
		GROUP BY element;

		
		
		
		
		
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
	
/*
****************************************************************************************************************************************************************************
	KP W3
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W3_1' THEN
/* SQL W3_1 ----------------------------------------------------------------------------------------------------------*/
		#to pierwszy dataset - frxDBUniversalOne
		SELECT c.GUID, c.data, c.nrwewn, c.idn,
			v.nr_zlec, v.masa_zlec,
			ean.symbol AS gniazdo
		FROM crm_commisions c
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = c.case_GUID
		LEFT JOIN `erp_area_nodes` ean ON c.areaGUID = ean.guid # zasoby - gniazdo
		WHERE
			c.GUID = @Commision_GUID;

/* END SQL ----------------------------------------------------------------------------------------------------------*/
	ELSEIF _Symbol = 'W3_2' THEN
/* SQL W3 _2  ----------------------------------------------------------------------------------------------------------*/
		# a to drugi dataset - frxDBUniversalTwo
		SELECT top.GUID,
		    if(tob.def_obj=-4,concat('Nr karty: ',tob.nr_zmiany),'') as grupa,
			rc.towrsymb AS operacja,
			SUM(top.ilosc) AS ilosc,
		    SUM(hur.kwota * top.ilosc) AS wartosc
		FROM zmpo top
		LEFT JOIN `pr_tob` tob ON tob.guid = top.tobguid
		#LEFT JOIN `pr_tob` t1 ON t1.guid = tob.parentGUID
		#LEFT JOIN `pr_tob` t2 ON t2.guid = t1.parentGUID
		#LEFT JOIN `pr_techn_def_obj` P ON P.IDN = tob.Def_Obj
		#LEFT JOIN `erp_area_nodes` ean ON top.areaGUID = ean.guid # zasoby - gniazdo
		LEFT JOIN `rcpo` rc ON rc.GUID = top.rcpoGUID        # definicja operacji i dane operacji
		LEFT JOIN `hr_unitprice_rate` hur ON hur.kod = rc.UnitPrice_Rate #stawka operacji, z calego lancuszka wyzej
		WHERE
		      top.commision_guid = @Commision_GUID
		GROUP BY operacja
		ORDER BY grupa, operacja;

	
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
/*
****************************************************************************************************************************************************************************
	KP W4
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W4' THEN
/* SQL ZM2  ----------------------------------------------------------------------------------------------------------*/
		# tu bedzie filtr na asortyment
		SELECT 'A';
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
/*
****************************************************************************************************************************************************************************
	KP W4_LAP
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W4_LAP_1' THEN
/* SQL W4_LAP_1 ----------------------------------------------------------------------------------------------------------*/
		#to pierwszy dataset - frxDBUniversalOne
		SELECT c.GUID, c.data, c.nrwewn, c.idn,
			v.nr_zlec, v.masa_zlec,
			ean.symbol AS gniazdo
		FROM crm_commisions c
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = c.case_GUID
		LEFT JOIN `erp_area_nodes` ean ON c.areaGUID = ean.guid # zasoby - gniazdo
		WHERE
			c.GUID = @Commision_GUID;

/* END SQL ----------------------------------------------------------------------------------------------------------*/
	ELSEIF _Symbol = 'W4_LAP_2' THEN
/* SQL W4_LAP_2  ----------------------------------------------------------------------------------------------------------*/
		# a to drugi dataset - frxDBUniversalTwo
		SELECT top.GUID,
		    if(tob.def_obj=-4,concat('Nr karty: ',tob.nr_zmiany),'') as grupa,
			rc.towrsymb AS operacja,
			SUM(top.ilosc) AS ilosc,
		    SUM(hur.kwota * top.ilosc) AS wartosc
		FROM zmpo top
		LEFT JOIN `pr_tob` tob ON tob.guid = top.tobguid
		#LEFT JOIN `pr_tob` t1 ON t1.guid = tob.parentGUID
		#LEFT JOIN `pr_tob` t2 ON t2.guid = t1.parentGUID
		#LEFT JOIN `pr_techn_def_obj` P ON P.IDN = tob.Def_Obj
		#LEFT JOIN `erp_area_nodes` ean ON top.areaGUID = ean.guid # zasoby - gniazdo
		LEFT JOIN `rcpo` rc ON rc.GUID = top.rcpoGUID        # definicja operacji i dane operacji
		LEFT JOIN `hr_unitprice_rate` hur ON hur.kod = rc.UnitPrice_Rate #stawka operacji, z calego lancuszka wyzej
		WHERE
		      top.commision_guid = @Commision_GUID
		GROUP BY operacja
		ORDER BY grupa, operacja;
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
/*
****************************************************************************************************************************************************************************
	KP W5
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W5_1' THEN
/* SQL W5_1 ----------------------------------------------------------------------------------------------------------*/
		#to pierwszy dataset - frxDBUniversalOne
		SELECT c.GUID, c.data, c.nrwewn, c.idn,
			v.nr_zlec, v.masa_zlec,
			ean.symbol AS gniazdo
		FROM crm_commisions c
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = c.case_GUID
		LEFT JOIN `erp_area_nodes` ean ON c.areaGUID = ean.guid # zasoby - gniazdo
		WHERE
			c.GUID = @Commision_GUID;

/* END SQL ----------------------------------------------------------------------------------------------------------*/
	ELSEIF _Symbol = 'W5_2' THEN
/* SQL W5_2  ----------------------------------------------------------------------------------------------------------*/
		# a to drugi dataset - frxDBUniversalTwo
		SELECT top.GUID,
		    #rc.towrsymb AS operacja,
			if(tob.def_obj=-4,concat('Nr karty: ',tob.nr_zmiany),'') as grupa,
			ean.symbol AS gniazdo,
			SUM(top.ilosc) AS ilosc,
		    SUM(hur.kwota * top.ilosc) AS wartosc
		FROM zmpo top
		LEFT JOIN `pr_tob` tob ON tob.guid = top.tobguid
		#LEFT JOIN `pr_tob` t1 ON t1.guid = tob.parentGUID
		#LEFT JOIN `pr_tob` t2 ON t2.guid = t1.parentGUID
		#LEFT JOIN `pr_techn_def_obj` P ON P.IDN = tob.Def_Obj
		LEFT JOIN `erp_area_nodes` ean ON top.areaGUID = ean.guid # zasoby - gniazdo
		LEFT JOIN `rcpo` rc ON rc.GUID = top.rcpoGUID        # definicja operacji i dane operacji
		LEFT JOIN `hr_unitprice_rate` hur ON hur.kod = rc.UnitPrice_Rate #stawka operacji, z calego lancuszka wyzej
		WHERE
		      top.commision_guid = @Commision_GUID
		GROUP BY gniazdo
		ORDER BY grupa, gniazdo;
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
/*
****************************************************************************************************************************************************************************
	KP W6
****************************************************************************************************************************************************************************
 */
	IF _Symbol = 'W6_1' THEN
/* SQL W5_1 ----------------------------------------------------------------------------------------------------------*/
		#to pierwszy dataset - frxDBUniversalOne
		SELECT c.GUID, c.data, c.nrwewn, c.idn,
			v.nr_zlec, v.masa_zlec,
			ean.symbol AS gniazdo
		FROM crm_commisions c
		LEFT JOIN `v_pr_tob_sum` v ON v.case_GUID = c.case_GUID
		LEFT JOIN `erp_area_nodes` ean ON c.areaGUID = ean.guid # zasoby - gniazdo
		WHERE
			c.GUID = @Commision_GUID;

/* END SQL ----------------------------------------------------------------------------------------------------------*/
	ELSEIF _Symbol = 'W6_2' THEN
/* SQL W5_2  ----------------------------------------------------------------------------------------------------------*/
		# a to drugi dataset - frxDBUniversalTwo
		SELECT top.GUID,
		    #rc.towrsymb AS operacja,
			if(tob.def_obj=-4,concat('Nr karty: ',tob.nr_zmiany),'') as grupa,
			ean.symbol AS gniazdo,
			SUM(top.ilosc) AS ilosc,
		    SUM(hur.kwota * top.ilosc) AS wartosc
		FROM zmpo top
		LEFT JOIN `pr_tob` tob ON tob.guid = top.tobguid
		#LEFT JOIN `pr_tob` t1 ON t1.guid = tob.parentGUID
		#LEFT JOIN `pr_tob` t2 ON t2.guid = t1.parentGUID
		#LEFT JOIN `pr_techn_def_obj` P ON P.IDN = tob.Def_Obj
		LEFT JOIN `erp_area_nodes` ean ON top.areaGUID = ean.guid # zasoby - gniazdo
		LEFT JOIN `rcpo` rc ON rc.GUID = top.rcpoGUID        # definicja operacji i dane operacji
		LEFT JOIN `hr_unitprice_rate` hur ON hur.kod = rc.UnitPrice_Rate #stawka operacji, z calego lancuszka wyzej
		WHERE
		      top.commision_guid = @Commision_GUID
		GROUP BY gniazdo
		ORDER BY grupa, gniazdo;
/* END SQL ----------------------------------------------------------------------------------------------------------*/
	END IF;
END$$

DROP PROCEDURE IF EXISTS `ad_pr_Tool`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ad_pr_Tool`(
	IN _MODE VARCHAR(50),		
	IN _GUID VARCHAR(38),
	IN _GUID2 VARCHAR(38),
	IN _Param_A VARCHAR(50),	
	IN _Param_B VARCHAR(50),	-- np. _AreaGUID
	IN _Param_C VARCHAR(50),
	IN _Param_D VARCHAR(50)
)
SPROC: BEGIN

	/*	
	_GUID oznacza np. GUID karty rozliczen lub podzlecenia, zlecenia etc
	
	_MODE oznacza:
	
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_okres'		
		Pokazuje stan realizacji na gniazdach w okresie 
		Parametry
			_Param_A 		jako zmienna _DateDown
			_Param_B 		jako zmienna _DateUp
			_Param_C 		'P' - grupuje po podzleceniach, 'G' - po gniazdach. '' - nie grupuje
		CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', 'P', '');		
		CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', 'G', '');		
		CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', '', '');		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_Stan_Init'		
		Pokazuje stan na gniazdach na zlec. otwartych przez ost. 30 dni
		Parametry
		CALL ad_pr_Tool('GNIAZDA_Stan_Init', '', '', '', '', '', '');		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_Stan'		
		Pokazuje stan na gniazdach na zlec. otwartych przez ost. 30 dni
		Parametry
		CALL ad_pr_Tool('GNIAZDA_Stan', '', '', '', '', '', '');		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_Stan_Wydzialy'		
		Pokazuje stan na gniazdach na zlec. otwartych przez ost. 30 dni
		Parametry
		CALL ad_pr_Tool('GNIAZDA_Stan_Wydzialy', '', '', '', '', '', '');		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'PMW_elementy_np'		
		Elementy nie przekazane dla konkretnego zlecenia 
		Parametry
			_Param_A 		GUID podzlecenia 
			_Param_B 		Okresla NrWewn
							'' - oznacza, że szukamy z _Param_A jako GUID podzlecenia
							<> '' - oznacza, że szukamy z _Param_B jako nrwewn czyli  nrwewn like _ParamB	
			_Param_C 		GUID wydzialu
		CALL ad_pr_Tool('PMW_elementy_np', '', '', PodZlecGuid, '', 'DivGUID', '');		
		CALL ad_pr_Tool('PMW_elementy_np', '', '', '1980%', 'x', 'DivGUID', '');		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	*/
	
	DECLARE _DateDown DATE;
	DECLARE _DateUp DATE;
	
	DECLARE _LogType VARCHAR(20) DEFAULT '';
	DECLARE _RozliczOneRec INT DEFAULT 1;
	
	DECLARE _AreaGUID VARCHAR(38) DEFAULT _Param_B;
	DECLARE _SeriaDone DOUBLE; 
	DECLARE _TimeCaps DOUBLE;
	DECLARE _TimeDone DOUBLE;
	DECLARE _Progress DOUBLE;
	DECLARE _Wartosc DOUBLE;
	DECLARE _MasaSeria DOUBLE;
	DECLARE _MasaSeriaDone DOUBLE;
	
	SET _Param_A = IFNULL(_Param_A, '');
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_okres'		
		Pokazuje stan realizacji na gniazdach w okresie 
		Parametry
			_Param_A 		jako zmienna _DateDown
			_Param_B 		jako zmienna _DateUp
		CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_okres' THEN
	--	Przygotowuje tabele `t_pr_kr_id` z identifikatorami
		SET _DateDown = DATE(IFNULL(_Param_A, '2009-09-01'));
		SET _DateUp = DATE(IFNULL(_Param_B, '2009-10-01'));
		
		DROP TEMPORARY TABLE IF EXISTS `t_ad_gniazda_okres_init`;
		CREATE TEMPORARY TABLE `t_ad_gniazda_okres_init` (
			`PodzlecGUID` varchar(38) NOT NULL COMMENT 'Zlecenie',
			`AreaGUID` varchar(38) NOT NULL COMMENT 'Gniazdo',
-- 			`divGUID` varchar(38) NOT NULL COMMENT 'Wydzial',
-- 			`div_NawaGUID` varchar(38) DEFAULT NULL COMMENT 'Wydzial/Nawa',

			`WartoscCaps_ALL` double DEFAULT '0' COMMENT 'Pula robocizny',
			`WartoscDone_ALL` double DEFAULT '0' COMMENT 'Przepracowano robocizny',
			`WartoscCaps_Okres_ALL` double DEFAULT '0' COMMENT 'Pula robocizny',
			`WartoscDone_Okres_ALL` double DEFAULT '0' COMMENT 'Przepracowano robocizny',

			`TimeCaps` double DEFAULT '0' COMMENT 'Pula czasu',
			`TimeCapsOBUN` double DEFAULT '0',
			`TimeDone` double DEFAULT '0' COMMENT 'Przepracowano',
			`TimeDoneOBUN` double DEFAULT '0',
			`TimeDoneProc` double DEFAULT '0' COMMENT 'Procent czasu',
			
			`TimeCaps_Okres` double DEFAULT '0' COMMENT 'Pula czasu',
			`TimeCapsOBUN_Okres` double DEFAULT '0',
			`TimeDone_Okres` double DEFAULT '0' COMMENT 'Przepracowano',
			`TimeDoneOBUN_Okres` double DEFAULT '0',
			`TimeDoneProc_Okres` double DEFAULT '0' COMMENT 'Procent czasu',
			
			`Seria` double DEFAULT '0' COMMENT 'Seria',
			`SeriaDone` double DEFAULT '0' COMMENT 'Seria wykonano',

			`Seria_Okres` double DEFAULT '0' COMMENT 'Seria',
			`SeriaDone_Okres` double DEFAULT '0' COMMENT 'Seria wykonano',
			
			`Progress` double DEFAULT '0' COMMENT 'Postep',
			-- `IsOBUN` int(11) DEFAULT '0',
			`Wartosc` double DEFAULT '0' COMMENT 'Kwota',
			`krz_TimeCaps` double DEFAULT '0',
			`krz_TimeCapsOBUN` double DEFAULT '0',
			`krz_TimeDone` double DEFAULT '0',
			`krz_TimeDoneOBUN` double DEFAULT '0',
			`krz_C` int(11) DEFAULT '0',
			`krz_C_Done` int(11) DEFAULT '0',
			`krz_FirstDate` datetime DEFAULT NULL,
			`krz_LastDate` datetime DEFAULT NULL,
			`kpr_TimeCaps` double DEFAULT '0',
			`kpr_TimeCapsOBUN` double DEFAULT '0',
			`kpr_TimeDone` double DEFAULT '0',
			`kpr_TimeDoneOBUN` double DEFAULT '0',
			`kpr_C` int(11) DEFAULT '0',
			PRIMARY KEY (`PodzlecGUID`,`AreaGUID`)
		);
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_okres_aktywne`;
		CREATE TEMPORARY TABLE `t_gniazda_okres_aktywne` (
			`PodzlecGUID` varchar(38) NOT NULL COMMENT 'Zlecenie',
			`AreaGUID` varchar(38) NOT NULL COMMENT 'Gniazdo',
			PRIMARY KEY (`PodzlecGUID`,`AreaGUID`)
		);
	--	Zgromadz aktywne zlecenia w tym okresie:	od _DateDown do _DateUp
	--	A) z kart pracy 
		INSERT IGNORE INTO `t_ad_gniazda_okres_init` 
			(PodzlecGUID, AreaGUID)
			SELECT
				KaPr.Case_GUID AS PodzlecGUID,
				log.AreaGUID AS AreaGUID
			FROM crm_commisions KaPr 
			INNER JOIN crm_log log ON log.commissionguid = KaPr.GUID
			WHERE
				KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) AND
				log.modify_date IS NOT NULL AND 
				(Date(log.modify_date) BETWEEN _DateDown AND _DateUp) AND
				log.tag IN (1, 2)
			GROUP BY PodzlecGUID, AreaGUID
		;
	--	B) z kart realizacji 
		INSERT IGNORE INTO `t_ad_gniazda_okres_init` 
			(PodzlecGUID, AreaGUID)
			SELECT
				krng.PodzlecGUID AS PodzlecGUID,
				krng.AreaGUID AS AreaGUID
			FROM pr_krng krng	
			INNER JOIN pr_krpo_r krz ON krz.krngGUID = krng.GUID
			WHERE
				krz.DateDone IS NOT NULL AND 
				(Date(krz.DateDone) BETWEEN _DateDown AND _DateUp) AND
				krz.LogType IN ('-', '+')
 			GROUP BY PodzlecGUID, AreaGUID
		;
		
	--	Naliczanie
	--	A) z kart pracy
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_gniazda_need_update_calc`
			SELECT
				dl.PodZlecGUID AS PodzlecGUID,
				log.AreaGUID AS AreaGUID,
				_VarInt(COUNT(DISTINCT KaPr.GUID )) AS kpr_C,
				
				SUM(IF(log.tag = 1, IF(TRUE, 
					_varDOUBLE_NOTNULL(log.wartosc),0),0)) AS kpr_WartoscCaps_ALL,		
				SUM(IF(log.tag = 1, IF(TRUE AND (log.modify_date BETWEEN _DateDown AND _DateUp),  
					_varDOUBLE_NOTNULL(log.wartosc),0),0)) AS kpr_WartoscCaps_Okres_ALL,		
				SUM(IF(log.tag = 2, IF(TRUE, 
					_varDOUBLE_NOTNULL(log.wartosc),0),0)) AS kpr_WartoscDone_ALL,		
				SUM(IF(log.tag = 2, IF(TRUE AND (log.modify_date BETWEEN _DateDown AND _DateUp),  
					_varDOUBLE_NOTNULL(log.wartosc),0),0)) AS kpr_WartoscDone_Okres_ALL,		
				
				SUM(IF(log.tag = 2, IF(KaPr.PodRodzaj = 6, _varDOUBLE_NOTNULL(log.wartosc),0),0)) AS kpr_WartoscDone,		
				SUM(IF(log.tag = 2, IF(KaPr.PodRodzaj = 7, _varDOUBLE_NOTNULL(log.wartosc),0),0)) AS kpr_WartoscDoneOBUN,		
				
				SUM(IF(log.tag = 1, IF(KaPr.PodRodzaj = 6, _varDOUBLE_NOTNULL(log.time_duration),0),0)) AS kpr_TimeCaps,		--	Godziny technologiczne z LOG-a (godz. zarejestrowane)
				SUM(IF(log.tag = 1, IF(KaPr.PodRodzaj = 7, _varDOUBLE_NOTNULL(log.time_duration),0),0)) AS kpr_TimeCapsOBUN,	--	Godziny technologiczne z LOG-a (godz. zarejestrowane)
				SUM(IF(log.tag = 2, IF(KaPr.PodRodzaj = 6, _varDOUBLE_NOTNULL(log.time_sheet),0),0)) AS kpr_TimeDone,			--	Godziny zreazlizowane z LOG-a (godz. realizowane)
				SUM(IF(log.tag = 2, IF(KaPr.PodRodzaj = 7, _varDOUBLE_NOTNULL(log.time_sheet),0),0)) AS kpr_TimeDoneOBUN,		--	Godziny OBUN zreazlizowane z LOG-a (godz. realizowane)
				
				SUM(IF(log.tag = 1, IF(KaPr.PodRodzaj = 6 AND (log.modify_date BETWEEN _DateDown AND _DateUp), 
					_varDOUBLE_NOTNULL(log.time_duration),0),0)) AS kpr_TimeCaps_Okres,			--	w okresie Godziny technologiczne z LOG-a (godz. zarejestrowane)
				SUM(IF(log.tag = 1, IF(KaPr.PodRodzaj = 7 AND (log.modify_date BETWEEN _DateDown AND _DateUp), 
					_varDOUBLE_NOTNULL(log.time_duration),0),0)) AS kpr_TimeCapsOBUN_Okres,		--	w okresie Godziny technologiczne z LOG-a (godz. zarejestrowane)
				SUM(IF(log.tag = 2, IF(KaPr.PodRodzaj = 6 AND (log.modify_date BETWEEN _DateDown AND _DateUp), 
					_varDOUBLE_NOTNULL(log.time_sheet),0),0)) AS kpr_TimeDone_Okres,				--	w okresie Godziny zreazlizowane z LOG-a (godz. realizowane)
				SUM(IF(log.tag = 2, IF(KaPr.PodRodzaj = 7 AND (log.modify_date BETWEEN _DateDown AND _DateUp), 
					_varDOUBLE_NOTNULL(log.time_sheet),0),0)) AS kpr_TimeDoneOBUN_Okres			--	w okresie Godziny OBUN zreazlizowane z LOG-a (godz. realizowane)
			FROM `t_ad_gniazda_okres_init` dl 
			INNER JOIN crm_commisions KaPr ON KaPr.Case_GUID = dl.PodzlecGUID AND KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) 
			INNER JOIN crm_log log ON log.commissionguid = KaPr.GUID AND log.AreaGUID = dl.AreaGUID
			WHERE
				log.modify_date IS NOT NULL AND 
				(Date(log.modify_date) <= _DateUp) AND
				log.tag IN (1, 2)
			GROUP BY PodzlecGUID, AreaGUID
		;
	--	Uzupelnij `ad_pr_gniazda_zlec`	
		UPDATE `t_ad_gniazda_okres_init`  agz, `t_gniazda_need_update_calc` calc
		SET
			agz.kpr_C = calc.kpr_C,
			
			agz.WartoscCaps_ALL = agz.WartoscCaps_ALL + Round(calc.kpr_WartoscCaps_ALL, 4),
			agz.WartoscCaps_Okres_ALL = agz.WartoscCaps_Okres_ALL + Round(calc.kpr_WartoscCaps_Okres_ALL, 4),
			agz.WartoscDone_ALL = agz.WartoscDone_ALL + Round(calc.kpr_WartoscDone_ALL, 4),
			agz.WartoscDone_Okres_ALL = agz.WartoscDone_Okres_ALL + Round(calc.kpr_WartoscDone_Okres_ALL, 4),
			
			agz.TimeCaps = agz.TimeCaps + calc.kpr_TimeCaps,
			agz.TimeCapsOBUN = agz.TimeCapsOBUN + calc.kpr_TimeCapsOBUN,
			agz.TimeDone = agz.TimeDone + calc.kpr_TimeDone,
			agz.TimeDoneOBUN = agz.TimeDoneOBUN + calc.kpr_TimeDoneOBUN,

			agz.TimeCaps_Okres = agz.TimeCaps_Okres + calc.kpr_TimeCaps_Okres,
			agz.TimeCapsOBUN_Okres = agz.TimeCapsOBUN_Okres + calc.kpr_TimeCapsOBUN_Okres,
			agz.TimeDone_Okres = agz.TimeDone_Okres + calc.kpr_TimeDone_Okres,
			agz.TimeDoneOBUN_Okres = agz.TimeDoneOBUN_Okres + calc.kpr_TimeDoneOBUN_Okres,
			
			agz.kpr_TimeCaps = calc.kpr_TimeCaps,
			agz.kpr_TimeCapsOBUN = calc.kpr_TimeCapsOBUN,
			agz.kpr_TimeDone = calc.kpr_TimeDone,
			agz.kpr_TimeDoneOBUN = calc.kpr_TimeDoneOBUN
		WHERE
			agz.PodzlecGUID = calc.PodzlecGUID AND
			agz.AreaGUID = calc.AreaGUID
		;
		
	--	Naliczanie
	--	B) z kart realizacji
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_gniazda_need_update_calc`
			SELECT
				dl.PodZlecGUID AS PodzlecGUID,
				dl.AreaGUID,
				_VarInt(COUNT(DISTINCT krng.GUID)) AS krz_C,
				_VarInt(SUM(krng.Status IN (2,3))) AS krz_C_Done,
				_VarInt(SUM(krz.Seria)) AS Seria,
				_VarInt(SUM(krz.SeriaDone)) AS SeriaDone,
				
				_VarInt(SUM(IF((krz.DateDone BETWEEN _DateDown AND _DateUp), krz.Seria, 0))) AS Seria_Okres,
				_VarInt(SUM(IF((krz.DateDone BETWEEN _DateDown AND _DateUp), krz.SeriaDone, 0))) AS SeriaDone_Okres,
				
				_VarInt(AVG(krng.Progress)) AS Progress,
				
				SUM(IF(TRUE, 
					_VarDouble(krp.Wartosc), 0)) AS krz_WartoscCaps_ALL,		
				SUM(IF(TRUE AND (krz.DateDone BETWEEN _DateDown AND _DateUp), 
					_VarDouble(krp.Wartosc), 0)) AS krz_WartoscCaps_Okres_ALL,		
				SUM(IF(TRUE, 
					_VarDouble(krz.TimeDone*krp.Wartosc/krp.TimeCaps), 0)) AS krz_WartoscDone_ALL,		
				SUM(IF(TRUE AND (krz.DateDone BETWEEN _DateDown AND _DateUp), 
					_VarDouble(krz.TimeDone*krp.Wartosc/krp.TimeCaps), 0)) AS krz_WartoscDone_Okres_ALL,		
					
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0, 
					_VarDouble(krz.TimeDone*krp.Wartosc/krp.TimeCaps), 0)) AS krz_WartoscDone,		
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1, 
					_VarDouble(krz.TimeDone*krp.Wartosc/krp.TimeCaps), 0)) AS krz_WartoscDoneOBUN,				
				
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0, 
					_VarDouble(krz.TimeCaps), 0)) AS krz_TimeCaps,		--	Godziny technologiczne (godz. zarejestrowane)
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1, 
					_VarDouble(krz.TimeCaps), 0)) AS krz_TimeCapsOBUN,	--	Godziny technologiczne OBUN (godz. zarejestrowane)
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0, 
					_VarDouble(krz.TimeDone), 0)) AS krz_TimeDone,		--	Godziny zreazlizowane (godz. realizowane)
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1, 
					_VarDouble(krz.TimeDone), 0)) AS krz_TimeDoneOBUN,	--	Godziny zreazlizowane OBUN (godz. realizowane)
					
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0 AND (krz.DateDone BETWEEN _DateDown AND _DateUp), 
					_VarDouble(krz.TimeCaps), 0)) AS krz_TimeCaps_Okres,		--	Godziny technologiczne (godz. zarejestrowane)
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1 AND (krz.DateDone BETWEEN _DateDown AND _DateUp), 
					_VarDouble(krz.TimeCaps), 0)) AS krz_TimeCapsOBUN_Okres,	--	Godziny technologiczne OBUN (godz. zarejestrowane)
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0 AND (krz.DateDone BETWEEN _DateDown AND _DateUp), 
					_VarDouble(krz.TimeDone), 0)) AS krz_TimeDone_Okres,		--	Godziny zreazlizowane (godz. realizowane)
				SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1 AND (krz.DateDone BETWEEN _DateDown AND _DateUp), 
					_VarDouble(krz.TimeDone), 0)) AS krz_TimeDoneOBUN_Okres	--	Godziny zreazlizowane OBUN (godz. realizowane)
			FROM `t_ad_gniazda_okres_init` dl 
			INNER JOIN pr_krpo_r krz ON krz.PodzlecGUID = dl.PodzlecGUID AND krz.AreaGUID = dl.AreaGUID 
			LEFT JOIN pr_krpo krp ON krp.GUID = krz.krpoGUID 
			INNER JOIN pr_krng krng ON krng.GUID = krz.KrngGUID 
			WHERE
				krz.DateDone IS NOT NULL AND 
				(Date(krz.DateDone) <= _DateUp) AND
				krz.LogType IN ('-', '+')
 			GROUP BY PodzlecGUID, AreaGUID
		;
	--	Uzupelnij `ad_pr_gniazda_zlec`	
		UPDATE `t_ad_gniazda_okres_init`  agz, `t_gniazda_need_update_calc` calc
		SET
			agz.krz_C = calc.krz_C,
			
			agz.WartoscCaps_ALL = agz.WartoscCaps_ALL + Round(calc.krz_WartoscCaps_ALL, 4),
			agz.WartoscCaps_Okres_ALL = agz.WartoscCaps_Okres_ALL + Round(calc.krz_WartoscCaps_Okres_ALL, 4),
			agz.WartoscDone_ALL = agz.WartoscDone_ALL + Round(calc.krz_WartoscDone_ALL, 4),
			agz.WartoscDone_Okres_ALL = agz.WartoscDone_Okres_ALL + Round(calc.krz_WartoscDone_Okres_ALL, 4),
			
			agz.TimeCaps = agz.TimeCaps + calc.krz_TimeCaps,
			agz.TimeCapsOBUN = agz.TimeCapsOBUN + calc.krz_TimeCapsOBUN,
			agz.TimeDone = agz.TimeDone + calc.krz_TimeDone,
			agz.TimeDoneOBUN = agz.TimeDoneOBUN + calc.krz_TimeDoneOBUN,
			 
			agz.TimeCaps_Okres = agz.TimeCaps_Okres + calc.krz_TimeCaps_Okres,
			agz.TimeCapsOBUN_Okres = agz.TimeCapsOBUN_Okres + calc.krz_TimeCapsOBUN_Okres,
			agz.TimeDone_Okres = agz.TimeDone_Okres + calc.krz_TimeDone_Okres,
			agz.TimeDoneOBUN_Okres = agz.TimeDoneOBUN_Okres + calc.krz_TimeDoneOBUN_Okres,
			
			
			agz.krz_C_Done = IFNULL(calc.krz_C_Done, 0),
			-- agz.krz_TimeCaps = calc.krz_TimeCaps,
			-- agz.krz_TimeCapsOBUN = calc.krz_TimeCapsOBUN,
			-- agz.krz_TimeDone = calc.krz_TimeDone,
			-- agz.krz_TimeDoneOBUN = calc.krz_TimeDoneOBUN,
			 
			agz.Seria = calc.Seria,
			agz.SeriaDone = calc.SeriaDone,
			
			agz.Seria = calc.Seria_Okres,
			agz.SeriaDone = calc.SeriaDone_Okres,
			
			agz.krz_TimeCaps = calc.krz_TimeCaps,
			agz.krz_TimeCapsOBUN = calc.krz_TimeCapsOBUN,
			agz.krz_TimeDone = calc.krz_TimeDone,
			agz.krz_TimeDoneOBUN = calc.krz_TimeDoneOBUN
			
			 
			-- agz.TimeCaps = calc.krz_TimeCaps + agz.kpr_TimeCaps,
			-- agz.TimeCapsOBUN = calc.krz_TimeCapsOBUN + agz.kpr_TimeCapsOBUN,
			-- agz.TimeDone = calc.krz_TimeDone + agz.kpr_TimeDone,
			-- agz.TimeDoneOBUN = calc.krz_TimeDoneOBUN + agz.kpr_TimeDoneOBUN
		WHERE
			agz.PodzlecGUID = calc.PodzlecGUID AND
			agz.AreaGUID = calc.AreaGUID
		;
		
		UPDATE `t_ad_gniazda_okres_init`  agz
		SET
			agz.Progress = ROUND(_varDOUBLE_NOTNULL(agz.SeriaDone/agz.Seria), 4),
			 -- agz.Progress = calc.Progress,
			-- agz.TimeCaps = agz.krz_TimeCaps + agz.kpr_TimeCaps,
			-- agz.TimeCapsOBUN = agz.krz_TimeCapsOBUN + agz.kpr_TimeCapsOBUN,
			-- agz.TimeDone = agz.krz_TimeDone + agz.kpr_TimeDone,
			-- agz.TimeDoneOBUN = agz.krz_TimeDoneOBUN + agz.kpr_TimeDoneOBUN,
			agz.TimeDoneProc = ROUND(_varDOUBLE_NOTNULL((agz.TimeDone + agz.TimeDoneOBUN)/(agz.TimeCaps + agz.TimeCapsOBUN)), 4)
		;
		IF _Param_C = '' THEN
			DROP TEMPORARY TABLE IF EXISTS `t_ad_gniazda_okres`;
			CREATE TEMPORARY TABLE `t_ad_gniazda_okres` 
				SELECT
					agz.`PodzlecGUID` AS PodzlecGUID,
					agz.`AreaGUID` AS AreaGUID,

					agz.WartoscCaps_ALL AS WartoscCaps_ALL,
					agz.WartoscCaps_Okres_ALL AS WartoscCaps_Okres_ALL,
					agz.WartoscDone_ALL AS WartoscDone_ALL,
					agz.WartoscDone_Okres_ALL AS WartoscDone_Okres_ALL,
					
					agz.`TimeCaps` AS TimeCaps,
					agz.`TimeCapsOBUN` AS TimeCapsOBUN,
					agz.`TimeDone` AS TimeDone,
					agz.`TimeDoneOBUN` AS TimeDoneOBUN,
					agz.`TimeDoneProc` AS TimeDoneProc,
					
					_VarDouble(TimeCaps + TimeCapsOBUN) AS TimeCaps_ALL, 
					_VarDouble(TimeDone + TimeDoneOBUN) AS TimeDone_ALL, 
					_VarDouble(TimeDone + TimeDoneOBUN) AS TimeDoneProc_ALL,
					
					agz.`TimeCaps_Okres` AS TimeCaps_Okres,
					agz.`TimeCapsOBUN_Okres` AS TimeCapsOBUN_Okres,
					agz.`TimeDone_Okres` AS TimeDone_Okres,
					agz.`TimeDoneOBUN_Okres` AS TimeDoneOBUN_Okres,
					agz.`TimeDoneProc_Okres` AS TimeDoneProc_Okres,
					
					_VarDouble(TimeCaps_Okres + TimeCapsOBUN_Okres) AS TimeCaps_Okres_ALL, 
					_VarDouble(TimeDone_Okres + TimeDoneOBUN_Okres) AS TimeDone_Okres_ALL, 
					_VarDouble(TimeDone + TimeDoneOBUN) AS TimeDoneProc_Okres_ALL, 
					
					agz.`Seria` AS Seria,
					agz.`SeriaDone` AS SeriaDone,
					agz.`Seria_Okres` AS Seria_Okres,
					agz.`SeriaDone_Okres` AS SeriaDone_Okres,
					
					agz.`Progress` AS Progress,
					agz.`Progress` AS Progress_Okres,
					agz.`Wartosc` AS Wartosc,
					agz.`krz_TimeCaps` AS krz_TimeCaps,
					agz.`krz_TimeCapsOBUN` AS krz_TimeCapsOBUN,
					agz.`krz_TimeDone` AS krz_TimeDone,
					agz.`krz_TimeDoneOBUN` AS krz_TimeDoneOBUN,
					agz.`krz_C` AS krz_C,
					agz.`krz_C_Done` AS krz_C_Done,
					agz.`kpr_TimeCaps` AS kpr_TimeCaps,
					agz.`kpr_TimeCapsOBUN` AS kpr_TimeCapsOBUN,
					agz.`kpr_TimeDone` AS kpr_TimeDone,
					agz.`kpr_TimeDoneOBUN` AS kpr_TimeDoneOBUN,
					agz.`kpr_C` AS kpr_C,
					
					_ConcatSTR_NotEqu(g.Symbol, g.Nazwa, ' - ') AS AreaName,
					d.GUID_Wydzialu AS DivGUID,
					d.GUID AS div_NawaGUID,
					_ConcatSTR_NotEqu(ediv.Symbol, ediv.Nazwa, ' - ') AS DivName,
					pzl.NrWewn AS NrPodZlec,
					_VarStr_NOTNull(zl.NrWewn) AS NrZlec,
					_VarStr_NOTNull(kt.NrWewn) AS NrKontr
				FROM `t_ad_gniazda_okres_init`  agz
				LEFT JOIN erp_area_nodes g ON g.GUID = agz.AreaGUID 
				LEFT JOIN v_pr_division d ON d.GUID = g.Dzial 
				INNER JOIN erp_division_schema ediv ON ediv.GUID = d.GUID_Wydzialu
				INNER JOIN crm_commisions pzl ON pzl.GUID = agz.PodzlecGUID
				LEFT JOIN crm_commisions zl ON zl.GUID = pzl.ParentGUID
				LEFT JOIN crm_commisions kt ON kt.GUID = zl.ParentGUID
			;	
		ELSE
			DROP TEMPORARY TABLE IF EXISTS `t_ad_gniazda_okres`;
			CREATE TEMPORARY TABLE `t_ad_gniazda_okres` 
				SELECT
					agz.`PodzlecGUID` AS PodzlecGUID,
					agz.`AreaGUID` AS AreaGUID,
					
					_VarDouble(SUM(agz.WartoscCaps_ALL)) AS WartoscCaps_ALL,
					_VarDouble(SUM(agz.WartoscCaps_Okres_ALL)) AS WartoscCaps_Okres_ALL,
					_VarDouble(SUM(agz.WartoscDone_ALL)) AS WartoscDone_ALL,
					_VarDouble(SUM(agz.WartoscDone_Okres_ALL)) AS WartoscDone_Okres_ALL,
					
					_VarDouble(SUM(agz.`TimeCaps`)) AS TimeCaps,
					_VarDouble(SUM(agz.`TimeCapsOBUN`)) AS TimeCapsOBUN,
					_VarDouble(SUM(agz.`TimeDone`)) AS TimeDone,
					_VarDouble(SUM(agz.`TimeDoneOBUN`)) AS TimeDoneOBUN,
					_VarDouble(SUM(agz.`TimeDoneProc`)) AS TimeDoneProc,
					
					_VarDouble(SUM(TimeCaps + TimeCapsOBUN)) AS TimeCaps_ALL, 
					_VarDouble(SUM(TimeDone + TimeDoneOBUN)) AS TimeDone_ALL, 
					_VarDouble(TimeDone + TimeDoneOBUN) AS TimeDoneProc_ALL,
					
					_VarDouble(SUM(agz.`TimeCaps_Okres`)) AS TimeCaps_Okres,
					_VarDouble(SUM(agz.`TimeCapsOBUN_Okres`)) AS TimeCapsOBUN_Okres,
					_VarDouble(SUM(agz.`TimeDone_Okres`)) AS TimeDone_Okres,
					_VarDouble(SUM(agz.`TimeDoneOBUN_Okres`)) AS TimeDoneOBUN_Okres,
					_VarDouble(SUM(agz.`TimeDoneProc_Okres`)) AS TimeDoneProc_Okres,
					
					_VarDouble(SUM(TimeCaps_Okres + TimeCapsOBUN_Okres)) AS TimeCaps_Okres_ALL, 
					_VarDouble(SUM(TimeDone_Okres + TimeDoneOBUN_Okres)) AS TimeDone_Okres_ALL, 
					_VarDouble(TimeDone + TimeDoneOBUN) AS TimeDoneProc_Okres_ALL, 
					
					_VarDouble(SUM(agz.`Seria`)) AS Seria,
					_VarDouble(SUM(agz.`SeriaDone`)) AS SeriaDone,
					_VarDouble(SUM(agz.`Seria_Okres`)) AS Seria_Okres,
					_VarDouble(SUM(agz.`SeriaDone_Okres`)) AS SeriaDone_Okres,
					
					_VarDouble(AVG(agz.`Progress`)) AS Progress,
					_VarDouble(AVG(agz.`Progress`)) AS Progress_Okres,
					_VarDouble(SUM(agz.`Wartosc`)) AS Wartosc,
					_VarDouble(SUM(agz.`krz_TimeCaps`)) AS krz_TimeCaps,
					_VarDouble(SUM(agz.`krz_TimeCapsOBUN`)) AS krz_TimeCapsOBUN,
					_VarDouble(SUM(agz.`krz_TimeDone`)) AS krz_TimeDone,
					_VarDouble(SUM(agz.`krz_TimeDoneOBUN`)) AS krz_TimeDoneOBUN,
					_VarINT(SUM(agz.`krz_C`)) AS krz_C,
					_VarINT(SUM(agz.`krz_C_Done`)) AS krz_C_Done,
					_VarDouble(SUM(agz.`kpr_TimeCaps`)) AS kpr_TimeCaps,
					_VarDouble(SUM(agz.`kpr_TimeCapsOBUN`)) AS kpr_TimeCapsOBUN,
					_VarDouble(SUM(agz.`kpr_TimeDone`)) AS kpr_TimeDone,
					_VarDouble(SUM(agz.`kpr_TimeDoneOBUN`)) AS kpr_TimeDoneOBUN,
					_VarINT(SUM(agz.`kpr_C`)) AS kpr_C,
					
					_ConcatSTR_NotEqu(g.Symbol, g.Nazwa, ' - ') AS AreaName,
					d.GUID_Wydzialu AS DivGUID,
					d.GUID AS div_NawaGUID,
					_ConcatSTR_NotEqu(ediv.Symbol, ediv.Nazwa, ' - ') AS DivName,
					pzl.NrWewn AS NrPodZlec,
					_VarStr_NOTNull(zl.NrWewn) AS NrZlec,
					_VarStr_NOTNull(kt.NrWewn) AS NrKontr
				FROM `t_ad_gniazda_okres_init`  agz
				LEFT JOIN erp_area_nodes g ON g.GUID = agz.AreaGUID 
				LEFT JOIN v_pr_division d ON d.GUID = g.Dzial 
				INNER JOIN erp_division_schema ediv ON ediv.GUID = d.GUID_Wydzialu
				INNER JOIN crm_commisions pzl ON pzl.GUID = agz.PodzlecGUID
				LEFT JOIN crm_commisions zl ON zl.GUID = pzl.ParentGUID
				LEFT JOIN crm_commisions kt ON kt.GUID = zl.ParentGUID
				GROUP BY IF(_Param_C = 'P', agz.PodzlecGUID, IF(_Param_C = 'G', agz.AreaGUID, agz.AreaGUID))
			;	
		END IF;
		
		UPDATE `t_ad_gniazda_okres`
		SET
			TimeDoneProc_ALL = _VarDouble(IF(TimeCaps_ALL = 0, 0, Round(100*TimeDone_ALL/TimeCaps_ALL,2))),
			TimeDoneProc_Okres_ALL = _VarDouble(IF(TimeCaps_Okres_ALL = 0, 0, Round(100*TimeDone_Okres_ALL/TimeCaps_Okres_ALL,2))),
			Progress =  _VarDouble(IF(Seria = 0, 0, Round(100*SeriaDone/Seria,2))),
			Progress_Okres =  _VarDouble(IF(Seria_Okres = 0, 0, Round(100*SeriaDone_Okres/Seria_Okres,2)))
		;	
	END IF; 	-- _MODE = 'GNIAZDA_okres' 
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_Stan_Init'		
		Pokazuje stan na gniazdach na zlec. otwartych przez ost. 30 dni
		Parametry
		CALL ad_pr_Tool('GNIAZDA_Stan_Init', '', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_Stan_Init' THEN
		SET _DateDown = CURRENT_DATE;
		SET _DateDown = DATE_ADD(_DateDown, INTERVAL -1 MONTH);
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_TIME', '', '', '', '', '', '');
		-- CALL pr_div_links_Tool('GNIAZDA_UPDATE', '*', '*', '', '', '', '');
		DROP TEMPORARY TABLE IF EXISTS `t_ad_zlec_czynne_last_month`;
		CREATE TEMPORARY TABLE `t_ad_zlec_czynne_last_month` 
			SELECT 
				commisionGUID AS PodzlecGUID, 
				Status, Date_close, _DateDown
			FROM `crm_div_links`
			WHERE
				Status = 0 OR Date_close > _DateDown
			GROUP BY commisionGUID	
		;
		DROP TEMPORARY TABLE IF EXISTS `t_ad_pr_gniazda_zlec_init`;
		CREATE TEMPORARY TABLE `t_ad_pr_gniazda_zlec_init` LIKE ad_pr_gniazda_zlec;
		INSERT INTO `t_ad_pr_gniazda_zlec_init`
			SELECT 
				gz.*
			FROM ad_pr_gniazda_zlec gz
			INNER JOIN `t_ad_zlec_czynne_last_month` tczynne ON tczynne.PodzlecGUID = gz.PodzlecGUID
		;
		
		DROP TEMPORARY TABLE IF EXISTS `t_ad_pr_gniazda_zlec`;
		CREATE TEMPORARY TABLE `t_ad_pr_gniazda_zlec`
			SELECT 
				gz.*, 
				_VarDouble((gz.TimeCaps + gz.TimeCapsOBUN)) AS TimeCaps_ALL, 
				_VarDouble((gz.TimeDone + gz.TimeDoneOBUN)) AS TimeDone_ALL, 
				dl.Status,
				-- sp.DisplayValue as StatusStr,
				_ConcatSTR_NotEqu(g.Symbol, g.Nazwa, ' - ') AS AreaName,
				_ConcatSTR_NotEqu(ediv.Symbol, ediv.Nazwa, ' - ') AS DivName,
				pzl.NrWewn AS NrPodZlec,
				_ConcatSTR(_ConcatSTR(IF(dl.Status = 0, 'Otw. na', 'Zam. na'), ediv.Symbol, ' '), CONCAT('(',pzl.NrWewn,')'), ' - ')  AS WydzialStatusNrPodZlec,
				_VarStr_NOTNull(zl.NrWewn) AS NrZlec,
				_VarStr_NOTNull(kt.NrWewn) AS NrKontr
			FROM t_ad_pr_gniazda_zlec_init gz
			INNER JOIN `crm_div_links` dl ON dl.commisionGUID = gz.PodzlecGUID AND dl.divGUID = gz.divGUID
			LEFT JOIN erp_area_nodes g ON g.GUID = gz.AreaGUID 
			INNER JOIN erp_division_schema ediv ON ediv.GUID = gz.divGUID
			INNER JOIN crm_commisions pzl ON pzl.GUID = gz.PodzlecGUID
			LEFT JOIN crm_commisions zl ON zl.GUID = pzl.ParentGUID
			LEFT JOIN crm_commisions kt ON kt.GUID = zl.ParentGUID
			-- LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kn.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
		;
		UPDATE `t_ad_pr_gniazda_zlec`
		SET
			TimeDoneProc = _VarDouble(Round(100*TimeDoneProc, 2)),
			Progress =  _VarDouble(Round(100*Progress, 2))
		;
	END IF; 	-- _MODE = 'GNIAZDA_Stan_Init'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_Stan'		
		Pokazuje stan na gniazdach na zlec. otwartych przez ost. 30 dni
		Parametry
		CALL ad_pr_Tool('GNIAZDA_Stan', '', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_Stan' THEN
		CALL ad_pr_Tool('GNIAZDA_Stan_Init', '', '', '', '', '', '');		
	END IF; 	-- _MODE = 'GNIAZDA_Stan'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_Stan_Wydzialy'		
		Pokazuje stan na gniazdach na zlec. otwartych przez ost. 30 dni
		Parametry
		CALL ad_pr_Tool('GNIAZDA_Stan_Wydzialy', '', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_Stan_Wydzialy' THEN
		CALL ad_pr_Tool('GNIAZDA_Stan_Init', '', '', '', '', '', '');		
	END IF; 	-- _MODE = 'GNIAZDA_Stan_Wydzialy'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'PMW_elementy_np'		
		Elementy nie przekazane dla konkretnego zlecenia 
		Parametry
			_Param_A 		GUID podzlecenia 
			_Param_B 		Okresla NrWewn
							'' - oznacza, że szukamy z _Param_A jako GUID podzlecenia
							<> '' - oznacza, że szukamy z _Param_B jako nrwewn czyli  nrwewn like _ParamB	
			_Param_C 		GUID wydzialu ktorego to dotyczy
		CALL ad_pr_Tool('PMW_elementy_np', '', '', PodZlecGuid, '', 'DivGUID', '');		
		CALL ad_pr_Tool('PMW_elementy_np', '', '', '1980%', 'x', 'DivGUID', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'PMW_elementy_np' THEN
		IF _Param_B != '' THEN
			IF LOCATE('%', _Param_B) < 1 THEN
				SET _Param_B = CONCAT(_Param_B, '%');
			END IF;
		END IF;
			select 
			tob1.symbol as rysunek,
			tob.symbol, tob.nazwa, tob.ilosc,
			tob.ilosc_j, 
			tob.ilosc*tob.ilosc_j as ciezar_razem,
				ifnull((SELECT sum(mw.ilosc)
				FROM pr_przesuniecie_mw mw
				LEFT JOIN crm_commisions ng on ng.GUID=mw.GUID_Ng
				LEFT JOIN v_pr_division vd2 on vd2.GUID=mw.DivisionGUIDTo
				WHERE mw.TobGUID=tob.GUID and ng.rodzaj=14 and ng.podrodzaj=8
				and vd2.symbol_wydzialu='W-3'
				group by mw.tobguid),0) as wydano, 
			tob.ilosc-
				ifnull((SELECT sum(mw.ilosc)
				FROM pr_przesuniecie_mw mw
				LEFT JOIN crm_commisions ng on ng.GUID=mw.GUID_Ng
				LEFT JOIN v_pr_division vd2 on vd2.GUID=mw.DivisionGUIDTo
				WHERE mw.TobGUID=tob.GUID and ng.rodzaj=14 and ng.podrodzaj=8
				and vd2.symbol_wydzialu='W-3'
				group by mw.tobguid),0) as pozostalo
			from pr_tob tob
			inner join pr_tob tob1 on tob.parentguid= tob1.guid
			inner join crm_commisions podzlec on podzlec.guid=tob.case_guid
			where tob.def_obj=2 and 
				IF(_Param_B != '', nrwewn like _ParamB, GUID = _Param_A)	
			-- podzlec.nrwewn like '18560/H1'
			having ilosc-wydano<>0;

	--	WHWRE
	--		IF(_Param_B != '', nrwewn like _ParamB, GUID = _Param_A)	
	END IF; 	-- _MODE = 'GNIAZDA_Stan_Wydzialy'
	
END$$



/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================
SET @CASEguid = '{F1A79CB6-2C12-4950-8C98-57D8A0C688DA}'; #17500 Zlec
SET @CASEguid = '{871CB62C-4846-49C2-9881-80719747BAC5}'; #17500/01 Podzlec
SET @CASEguid = '{453C2091-2AD8-4881-98E4-BEBD6BE5EA1A}'; #17500/02 Podzlec
SET @CASEguid = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}'; #17500/03 Podzlec
SET @CASEguid = '{1EE560C8-F23E-46EF-AFE2-E306F5592708}'; #17500/Sloniasty
SET @CASEguid = '{DD496100-9357-44A8-8629-916584C0271E}'; #17500/Klon
SET @Case_GUID = @CASEguid;

SET @GKrNg = '{7E6CB6D2-9EA8-11DE-A5B6-00065BF0376E}'; -- ten testuj
SET @GKrNg = '{89B199C6-9D2D-11DE-A5B6-00065BF0376E}'; -- 

SET @GKrPo = '176993651a735e6ef674ca3ade26d8e1';
SET @GKrPo = '0e6af188292c13e535a1d2acf482eb1e';


CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', '', '');		
CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', 'P', '');		
CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-09-01', '2009-09-30', 'G', '');		

CALL ad_pr_Tool('GNIAZDA_okres', '', '', '2009-08-01', '2009-08-30', 'G', '');		
SELECT * FROM `t_ad_gniazda_okres`; 

CALL ad_pr_Tool('GNIAZDA_Stan', '', '', '', '', '', '');		

DROP TABLE `ad_t_ad_pr_gniazda_zlec`;
CREATE TABLE `ad_t_ad_pr_gniazda_zlec`
SELECT * FROM `t_ad_pr_gniazda_zlec`
;

SELECT a.TimeCaps_ALL,a.TimeDone_ALL,a.* 
FROM `ad_t_ad_pr_gniazda_zlec` a
WHERE
TRUE
 AND a.NrPodZlec like '1750%'
 AND DivGUID = '{8022922F-1712-49CD-A531-F7958180B305}'
;
place_0708

*/

DROP PROCEDURE IF EXISTS `pr_div_links_Tool`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_div_links_Tool`(
	IN _MODE VARCHAR(20),
	IN _PodZlec_GUID VARCHAR(38),
	IN _Div_GUID VARCHAR(38),
	IN _Param_A VARCHAR(50),	-- np. _DateClose
	IN _Param_B VARCHAR(50),	-- np. _DateUp
	IN _Param_C VARCHAR(50),	-- np. 
	IN _Param_D VARCHAR(50)	-- np. NOT Show?
	)
	CONTAINS SQL
	COMMENT 'Narzedzia do `crm_div_links`'
BEGIN
	/*
	_MODE
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'INIT'
		Inicjowanie zlecenia na wydziale
		Parametry
			_PodZlec_GUID		Moze byc '*' ale _DateClose i _DateUp musza byc okreslone
			_Div_GUID		Moze byc '*' 
		CALL pr_div_links_Tool('0', @CASEguid, 'Nawa1', '', '', '', '');
		CALL pr_div_links_Tool('INIT', @CASEguid, @Wydz, '', '', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'INIT_NEW'
		Inicjowanie tabeli nowych rtekordów (2 pola zlecenie / wydzial 
		Parametry
			_PodZlec_GUID		
			_Div_GUID		
			_Param_A 		'0' lub ''  kreowanie tabelki pomocniczej 
							'CREATE' wstawianie 
							'2' przelicznie
			_Param_B 		TYLKO dla _Param_A = 'CREATE'
							Gdy <> '' to wstawiam do `t_Init_zlec_div` wszystkie podzlecenia o numerze like _Param_B (np. like '19220/%'				
			_Param_C 		kombinacja liter K R P A 0
		CALL pr_div_links_Tool('INIT_NEW', @CASEguid, 'Nawa1', '', '', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'DIVLINKS_UPDATE_TIME'
		Przeliczanie crm_div_links wg aLocalTime
		Parametry 	BRAK
		CALL pr_div_links_Tool('DIVLINKS_UPDATE_TIME', '', '', '', '', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	
	'RANGE'
		Range for `crm_div_links`
		Parametry
			_PodZlec_GUID		Moze byc '*' ale _DateClose i _DateUp musza byc okreslone
			_Div_GUID		Moze byc '*' 
			_Param_A 		jako zmienna _DateClose
			_Param_B 		jako zmienna _DateUp
			_Param_C 		'' - bez znaczenia, <>'' np. '1' oznacza tylko te `NeedUpdate` <> ''
		CALL pr_div_links_Tool('RANGE', '*', '*', '2009-09-01', '2009-09-30', '', '');	-- wszystkie w przedziale 2009-09-01, 2009-09-30
		CALL pr_div_links_Tool('RANGE', '*', @W1, '2009-09-01', '2009-09-30', '', '');	-- wszystkie z wydzialu W-1 w przedziale 2009-09-01, 2009-09-30
		CALL pr_div_links_Tool('RANGE', @ZlecXYZ, '*', '', '', '', '');					-- wszystkie wydzialy zlecenia XYZ (daty ignorowane)
		CALL pr_div_links_Tool('RANGE', '', '', '', '', '1', '');				-- wszystkie wydzialy zlecenia XYZ wymagajace Update-a
			
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'NEED_UPDATE'
			Zadanie wykonania update - wpis do pola `NeedUpdate` wartosci bedacej kombinacja liter K R P A 0
			KRPA:	K - Karty pracy, R - Karty rozliczen, P - Przekazy, A - Akordy
					0 - Nalicz dane poczatkore (inicjalne)
		Parametry
			_PodZlec_GUID		patrz 'RANGE'
			_Div_GUID		patrz 'RANGE'
			_Param_A 		patrz 'RANGE'
			_Param_B 		patrz 'RANGE'
			_Param_C 		kombinacja liter K R P A 0
		CALL pr_div_links_Tool('NEED_UPDATE', @ZlecXYZ, @W1, '', '', 'R', '');			-- Zmienono karte pracy wydzialu W-1 zlecenia XYZ
		CALL pr_div_links_Tool('NEED_UPDATE', @ZlecXYZ, @W1, '', '', 'P', '');			-- Zmienono/utowrzono przekaz wydzialu W-1 zlecenia XYZ

	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'UPDATE'
			Wykonania update - dla wszystkich ktore w polu `NeedUpdate` maja wartosci bedace kombinacja liter K R P A 0
			KRPA:	K - Karty pracy, R - Karty rozliczen, P - Przekazy, A - Akordy
					0 - Nalicz dane poczatkore (inicjalne)
			1.	CALL pr_div_links_Tool('RANGE', '', '', '', '', '1', '');	Jako zakres tych co wymagaja Update-a					
			2. Wykonana jest petla na Rang-u i wolanie 
				CALL pr_div_links_Tool('UPDATE_EXECUTE', Case_GUID[i], Div_GUID[i], '', '', '', '');	-- nalicz
		Parametry
			_Param_C 		kombinacja liter K R P A 0 , gdy chcemy wszystkie wstawiamy _Param_C = 'KRPA0' lub _Param_C = ''
		CALL pr_div_links_Tool('UPDATE', '', '', '', '', 'K', '');			-- Zmienono karte rozliczen
		CALL pr_div_links_Tool('UPDATE', '', '', '', '', 'P', '');			-- Zmienono/utowrzono przekaz

	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'UPDATE_EXECUTE'
			Wykonanie update dla konkretnego rekordu _PodZlec_GUID x _Div_GUID
		Parametry
			_PodZlec_GUID		Musi byc okreslony <> '*'
			_Div_GUID		Musi byc okreslony <> '*'
		CALL pr_div_links_Tool('UPDATE_EXECUTE', Case_GUID, Div_GUID, '', '', '', '');	-- nalicz
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_NEED_UPDATE'
		Inicjowanie ad_pr_gniazda_zlec
		Parametry
			_PodZlec_GUID		Zlecenie
			_Div_GUID		jako AreaGUID 
			_Param_A 		jako zmienna _DateDown - istotne gdy _PodZlec_GUID = '' i _Div_GUID = ''
			_Param_B 		jako zmienna _DateUp - istotne gdy _PodZlec_GUID = '' i _Div_GUID = ''
		CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', @CASEguid, 'CG', '', '', '', '');
		CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', '', '', '2009-01-01', '2010-01-01', '', '');
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_UPDATE_TIME'
		Przeliczanie ad_pr_gniazda_zlec wg aLocalTime
		Parametry 	BRAK
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_TIME', '', '', '', '', '', '');
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_UPDATE'
		Przeliczanie ad_pr_gniazda_zlec czyli tych NeedUpdate_krz = 1 lub NeedUpdate_kpr = 1
		Parametry
			_PodZlec_GUID		Zlecenie może być '*' lub Pusty;
			_Div_GUID		jako AreaGUID może być '*' lub Pusty
		CALL pr_div_links_Tool('GNIAZDA_UPDATE', @CASEguid, 'CG', '', '', '', '');
		CALL pr_div_links_Tool('GNIAZDA_UPDATE', '*', 'CG', '', '', '', '');
		CALL pr_div_links_Tool('GNIAZDA_UPDATE', '*', '*', '', '', '', '');
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'GNIAZDA_UPDATE_DO'
		Przeliczanie `ad_pr_gniazda_zlec` na podstawie `t_gniazda_need_update` tworzonej w GNIAZDA_UPDATE
		Parametry
			BRAK
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_DO', '', '', '', '', '', '');
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	
	
		0		Tworzy rekord dla _PodZlec_GUID/_Div_GUID
		1		Nalicza rekord _PodZlec_GUID/_Div_GUID - pola czasow etc.
		2		SELECT
		3,4		Update zlecen w `crm_div_links`
		10,11	Zamknij zlecenie _PodZlec_GUID dla wydzialu _Div_GUID 
				10 z data _DateClose
				11 z data obliczana jako nast. m-c
		12		Odblokuj (otworz zlecenie) 		
		20, 21	Range for `crm_div_links`
		
	*/	

	DECLARE C_UPDATE_SYGN VARCHAR(4) DEFAULT 'KRPA';
	--	KRPA:	K - Karty pracy, R - Karty rozliczen, P - Przekazy, A - Akordy
	DECLARE _EndTime DATE;
	DECLARE _DateDeadLine DATE;
	DECLARE _crm_StartTime DATE;
	DECLARE _crm_EndTime DATE;
	DECLARE _ZeroDate DATE DEFAULT '0000-00-00';
	DECLARE _ZeroDateTime DATE DEFAULT '0000-00-00 00:00:00';
	DECLARE _DateClose DATE;
	DECLARE _DateDown DATE;
	DECLARE _DateUp DATE;
	DECLARE _AreaGUID VARCHAR(38);

	SET _PodZlec_GUID = IF(_varSTR_NOTNULL(_PodZlec_GUID) = '', '*', _PodZlec_GUID);
	SET _Div_GUID = IF(_varSTR_NOTNULL(_Div_GUID) = '', '*', _Div_GUID);
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'INIT'
		Inicjowanie zlecenia na wydziale
		Tworzy rekord dla _PodZlec_GUID/_Div_GUID
		Parametry
			_PodZlec_GUID		Moze byc '*' ale _DateClose i _DateUp musza byc okreslone
			_Div_GUID		Moze byc '*' 
		CALL pr_div_links_Tool('0', @CASEguid, 'Nawa1', '', '', '', '');
		CALL pr_div_links_Tool('INIT', @CASEguid, @Wydz, '', '', '', '');
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'INIT') OR (_MODE = '0') THEN
	--	Kreowanie temp `t_zlec_wydzialy` z zleceniami i wydzialami
		SET _DateClose = DATE(IFNULL(_Param_A, '2009-09-01'));
		SET _DateUp = DATE(IFNULL(_Param_B, '2009-10-01'));
		DROP TEMPORARY TABLE IF EXISTS `t_zlec_wydzialy`; 
		CREATE TEMPORARY TABLE `t_zlec_wydzialy`
			SELECT
				0 AS IDN,
				crm.GUID AS commisionGUID,
				crm.Data AS Date_crm,
				d.GUID_Wydzialu AS DivGUID,
				0 AS Status
			FROM crm_commisions crm
			LEFT JOIN v_pr_division d ON IF(_Div_GUID != '*', (d.GUID = _Div_GUID), (NOT (d.GUID like 'Nawa%')))
			WHERE
				IF(_PodZlec_GUID = '*', (crm.Data between _DateClose and _DateUp), crm.GUID = _PodZlec_GUID) AND
				crm.Rodzaj = 12 AND crm.Podrodzaj = 1
		;
	--	Kreowanie i update zlecen w `crm_div_links`
		INSERT INTO `crm_div_links`
			(IDN, commisionGUID, divGUID, Status, Date_crm, NeedUpdate_krz, NeedUpdate_kpr, NeedUpdate_pmw, NeedUpdate_drp)
			SELECT
				IDN, commisionGUID, divGUID, Status, Date_crm, 1, 1, 1, 1
			FROM `t_zlec_wydzialy`
		ON DUPLICATE KEY UPDATE 
			NeedUpdate_krz = 1,
			NeedUpdate_kpr = 1,
			NeedUpdate_pmw = 1, 
			NeedUpdate_drp = 1				
		;
	-- na chwile		DROP TEMPORARY TABLE IF EXISTS `t_zlec_wydzialy`; 
	END IF;	--	IF _MODE = 0
		
		
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'INIT_NEW'
	Inicjowanie tabeli nowych rtekordów (2 pola zlecenie / wydzial 
		Parametry
			_PodZlec_GUID		
			_Div_GUID		
			_Param_A 		'0' lub ''  kreowanie tabelki pomocniczej `t_Init_zlec_div`
							'CREATE' wstawianie 
							'2' przelicznie
			_Param_B 		TYLKO dla _Param_A = 'CREATE'
							Gdy <> '' to wstawiam do `t_Init_zlec_div` wszystkie podzlecenia o numerze like _Param_B (np. like '19220/%'				
			_Param_C 		kombinacja liter K R P A 0
		CALL pr_div_links_Tool('INIT_NEW', @CASEguid, 'Nawa1', '', '', '', '');
		CALL pr_div_links_Tool('INIT_NEW', '', '', '0', '', '', '');		KREUJ tabelke `t_Init_zlec_div`
		PRZYKŁAD:
			CALL pr_div_links_Tool('INIT_NEW', '', '', '0', '', '', '');
			INSERT IGNORE INTO `t_Init_zlec_div`
				(commisionGUID, divGUID)
			VALUES 
				(@CASEguid, 'Nawa1');
			INSERT IGNORE INTO `t_Init_zlec_div`
				(commisionGUID, divGUID)
			VALUES 
				(@CASEguid, @W3);
			CALL pr_div_links_Tool('INIT_NEW', '', '', 'CREATE', '', '', '');	WSTAWIANIE
		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'INIT_NEW') THEN
		SET _Param_A = _varSTR_NOTNULL(_Param_A);
		SET _Param_A = IF(_Param_A = '', '0', _Param_A);
		IF _Param_A = '0' THEN
			DROP TEMPORARY TABLE IF EXISTS `t_Init_zlec_div`; 
			CREATE TEMPORARY TABLE `t_Init_zlec_div`(
				commisionGUID VARCHAR(38) NOT NULL DEFAULT '',	
				divGUID VARCHAR(38) NOT NULL DEFAULT '',	
			PRIMARY KEY (`commisionGUID`, `divGUID`)
			);
		END IF;
		IF _Param_A = 'CREATE' THEN
	--	Kreowanie i update zlecen w `crm_div_links`
			SET _Param_C = _varSTR_NOTNULL(_Param_C);
			IF LOCATE('%', _Param_B) > 0 THEN
			--	_Param_B <> '' to wstawiam do `t_Init_zlec_div` wszystkie podzlecenia o numerze like _Param_B (np. like '19220/%'
				INSERT IGNORE INTO `t_Init_zlec_div`
					SELECT 
						c.GUID, '*'	-- Tworze pare GUID zlec/ * czyli wszystkie wydzialy
					FROM crm_commisions c 
					WHERE 
						c.nrWewn LIKE _Param_B
					AND c.Rodzaj = 12 AND c.Podrodzaj = 1
				;			
			
			END IF;
			
			INSERT INTO `crm_div_links`
				-- BYLO chyba rezygnujemy (commisionGUID, divGUID, Status, Date_crm, NeedUpdate)
				(commisionGUID, divGUID, Status, Date_crm, NeedUpdate_krz, NeedUpdate_kpr, NeedUpdate_pmw, NeedUpdate_drp)
				SELECT
					izd.commisionGUID, d.GUID_Wydzialu, 0, crm.Data, 1, 1, 1, 1 -- , _Param_C
				FROM `t_Init_zlec_div` izd
				LEFT JOIN `crm_commisions` crm ON crm.GUID = izd.commisionGUID
				-- LEFT JOIN v_pr_division d ON d.GUID = izd.divGUID
				LEFT JOIN v_pr_division d ON IF(izd.divGUID = '*', NOT d.IsNawa AND (d.Symbol like 'W-%' OR d.Symbol = 'MWG'), d.GUID = izd.divGUID)
			ON DUPLICATE KEY UPDATE 
				NeedUpdate_krz = 1,
				NeedUpdate_kpr = 1,
				NeedUpdate_pmw = 1, 
				NeedUpdate_drp = 1				
			;
		END IF;
	END IF;	--	IF _MODE = 'INIT_NEW'
		
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'RANGE'
		Range for `crm_div_links`
		Parametry
			_PodZlec_GUID		Moze byc '*' ale _DateClose i _DateUp musza byc okreslone
			_Div_GUID		Moze byc '*' 
			_Param_A 		jako zmienna _DateClose
			_Param_B 		jako zmienna _DateUp
			_Param_C 		'' - bez znaczenia, <>'' np. '1' oznacza tylko te `NeedUpdate` <> ''
		CALL pr_div_links_Tool('RANGE', '*', '*', '2009-09-01', '2009-09-30', '', '');	-- wszystkie w przedziale 2009-09-01, 2009-09-30
		CALL pr_div_links_Tool('RANGE', '*', @W1, '2009-09-01', '2009-09-30', '', '');	-- wszystkie z wydzialu W-1 w przedziale 2009-09-01, 2009-09-30
		CALL pr_div_links_Tool('RANGE', @ZlecXYZ, '*', '', '', '', '');					-- wszystkie wydzialy zlecenia XYZ (daty ignorowane)
		CALL pr_div_links_Tool('RANGE', '', '', '', '', '1', '');				-- wszystkie wydzialy zlecenia XYZ wymagajace Update-a
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'RANGE') OR (_MODE IN (20,21))  THEN
	
		SET _DateClose = DATE(IFNULL(_Param_A, '2009-09-01'));
		SET _DateUp = DATE(IFNULL(_Param_B, '2009-10-01'));
		SET _Param_C = _varSTR_NOTNULL(_Param_C);
		DROP TEMPORARY TABLE IF EXISTS `t_Range_zlec`; 
		IF _Param_C = '' THEN
			CREATE TEMPORARY TABLE `t_Range_zlec`
			SELECT 
				zl.IDN, zl.commisionGUID, zl.DivGUID, zl.Status, zl.Date_crm, zl.Date_Start, zl.Date_end
				FROM `crm_div_links` zl
				WHERE
					IF(_PodZlec_GUID = '*', (zl.Date_crm between _DateClose and _DateUp), zl.commisionGUID = _PodZlec_GUID) AND
					IF(_Div_GUID = '*', TRUE, zl.DivGUID = _Div_GUID)
			;
		ELSE	
			CREATE TEMPORARY TABLE `t_Range_zlec`
			SELECT 
				zl.IDN, zl.commisionGUID, zl.DivGUID, zl.Status, zl.Date_crm, zl.Date_Start, zl.Date_end
				FROM `crm_div_links` zl
				WHERE
					-- (IFNULL(zl.NeedUpdate, '') != '')
					(NeedUpdate_krz != 0) OR (NeedUpdate_kpr != 0) OR (NeedUpdate_pmw != 0) OR (NeedUpdate_drp)				
			;
		END IF;	
	END IF;	--	IF _MODE = 'RANGE'  OR  _MODE = 20 21

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'NEED_UPDATE'
	Zadanie wykonania update - wpis do pola `NeedUpdate` wartosci bedacej kombinacja liter K R P A 0
		KRPA:	K - Karty pracy, R - Karty rozliczen, P - Przekazy, A - Akordy
				0 - Nalicz dane poczatkore (inicjalne)
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'NEED_UPDATE' THEN
		UPDATE `crm_div_links` zl
		SET	
			NeedUpdate_krz = IF(_Param_C = 'R', 1, NeedUpdate_krz),
			NeedUpdate_kpr = IF(_Param_C = 'K', 1, NeedUpdate_kpr),
			NeedUpdate_pmw = IF(_Param_C = 'P', 1, NeedUpdate_pmw),
			NeedUpdate_drp = IF(_Param_C = 'A', 1, NeedUpdate_drp)				
			-- zl.NeedUpdate = CONCAT(zl.NeedUpdate,_Param_C)	--	dopisujemy
		WHERE
			IF(_PodZlec_GUID = '*', (zl.Date_crm between _DateClose and _DateUp), zl.commisionGUID = _PodZlec_GUID) AND
			IF(_Div_GUID = '*', TRUE, zl.DivGUID = _Div_GUID)
		;	
	END IF;	--	IF _MODE = 'NEED_UPDATE'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'UPDATE'
			Wykonania update - dla wszystkich ktore w polu `NeedUpdate` maja wartosci bedace kombinacja liter K R P A 0
			KRPA:	K - Karty pracy, R - Karty rozliczen, P - Przekazy, A - Akordy
					0 - Nalicz dane poczatkore (inicjalne)
			1.	CALL pr_div_links_Tool('RANGE', '', '', '', '', '1', '');	Jako zakres tych co wymagaja Update-a					
			2. Wykonana jest petla na Rang-u i wolanie 
				CALL pr_div_links_Tool('UPDATE_EXECUTE', Case_GUID[i], Div_GUID[i], '', '', '', '');	-- nalicz
		Parametry
			_Param_C 		kombinacja liter K R P A 0 , gdy chcemy wszystkie wstawiamy _Param_C = 'KRPA0' lub _Param_C = ''
		CALL pr_div_links_Tool('UPDATE', '', '', '', '', 'K', '');			-- Zmienono karte rozliczen
		CALL pr_div_links_Tool('UPDATE', '', '', '', '', 'P', '');			-- Zmienono/utowrzono przekaz
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'UPDATE' THEN
		CALL pr_div_links_Tool('RANGE', '', '', '', '', '1', '');	--	Jako zakres tych co wymagaja Update-a					
		
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _LOCAL_update_GUID VARCHAR(38);
			DECLARE _LOCAL_update_Div_GUID VARCHAR(38);
			DECLARE c_proc CURSOR FOR
				SELECT
					DISTINCT zl.commisionGUID AS GUID, zl.DivGUID
				FROM `t_Range_zlec` zl
			-- 	LIMIT 2500	
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loop1: LOOP
				FETCH c_proc INTO _LOCAL_update_GUID, _LOCAL_update_Div_GUID;
				IF done THEN
					LEAVE loop1;
				END IF;
				SET @Iter = @Iter + 1;
				CALL pr_div_links_Tool('UPDATE_EXECUTE', _LOCAL_update_GUID, _LOCAL_update_Div_GUID, '', '', '', '');	-- nalicz
			END LOOP;
			CLOSE c_proc;
		END;
	END IF;	--	IF _MODE = 'UPDATE'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'UPDATE_EXECUTE'
		Wykonanie update dla konkretnego rekordu _PodZlec_GUID x _Div_GUID
		Parametry
			_PodZlec_GUID		Musi byc okreslony <> '*'
			_Div_GUID		Musi byc okreslony <> '*'
		CALL pr_div_links_Tool('UPDATE_EXECUTE', Case_GUID, Div_GUID, '', '', '', '');	-- nalicz
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'UPDATE_EXECUTE' THEN
		-- CALL pr_div_links_Tool('RANGE', '', '', '', '', '1', '');	--	Jako zakres tych co wymagaja Update-a					
		
		BEGIN
			DECLARE _NeedUpdate VARCHAR(38);
			SELECT
				zl.NeedUpdate
			INTO
				_NeedUpdate
			FROM `crm_div_links` zl
			WHERE
				(zl.commisionGUID = _PodZlec_GUID) AND
				(zl.DivGUID = _Div_GUID)
			;
		--	K R P A 0	
		--	0 - Nalicz dane poczatkore (inicjalne)
		--	K - Karty pracy, R - Karty rozliczen, P - Przekazy, A - Akordy
		
			IF LOCATE('0', _NeedUpdate) > 0 THEN
			--	0 - Nalicz dane poczatkore (inicjalne)
				SELECT FALSE;
			END IF;	--	0 - Nalicz dane poczatkore (inicjalne)
			
			IF LOCATE('K', _NeedUpdate) > 0 THEN
			--	K - Karty pracy
			--	Agregacja z KaPr LOG
				DROP TEMPORARY TABLE IF EXISTS `t_PodZelcOkres_Agg`; 
				CREATE TEMPORARY TABLE `t_PodZelcOkres_Agg`
					SELECT
						_PodZlec_GUID AS PodzlecGUID,
						_VarInt(SUM(KaPr.GUID IS NOT NULL)) As C_KaPr,
						Date(MIN(KaPr.StartTime)) AS MinDate,
						Date(MAX(KaPr.StartTime)) AS MaxDate,
						Date(MIN(Date(log.modify_date))) AS MinDate2,
						Date(MAX(Date(log.modify_date))) AS MaxDate2,
						_VarDouble(SUM(log.time_duration)) AS godz_techn,	--	Godziny technologiczne z LOG-a (godz. zarejestrowane)
						_VarDouble(SUM(log.time_sheet)) AS godz_work,		--	Godziny technologiczne z LOG-a (godz. realizowane)
						_VarDouble(SUM(IF(KaPr.PodRodzaj = 7, log.time_duration, 0))) AS godz_techn_OBUN,	--	Godziny technologiczne OBUN z LOG-a (godz. zarejestrowane)
						_VarDouble(SUM(IF(KaPr.PodRodzaj = 7, log.time_sheet, 0))) AS godz_work_OBUN		--	Godziny technologiczne OBUN z LOG-a (godz. realizowane)
					FROM `crm_div_links` dl 
					LEFT JOIN crm_commisions KaPr ON KaPr.Case_GUID = dl.commisionGUID AND KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) 
					LEFT JOIN crm_log log ON log.commissionguid = KaPr.GUID
					LEFT JOIN erp_area_nodes ean ON ean.GUID = log.AreaGUID
					LEFT JOIN v_pr_division d ON d.GUID = ean.Dzial
					WHERE
						dl.commisionGUID = _PodZlec_GUID AND
						dl.divGUID = _Div_GUID AND
						d.GUID_Wydzialu = dl.divGUID AND
						log.modify_date IS NOT NULL AND 
						(Date(log.modify_date) != _ZeroDate)
					GROUP BY PodzlecGUID
				;
			--	Aktualizuj pola Agg  z KaPr LOG 
				UPDATE `crm_div_links` dl, `t_PodZelcOkres_Agg` pzoagg
				SET
					dl.C_KaPr = pzoagg.C_KaPr,
					dl.Date_min_KaPr = pzoagg.MinDate2,
					dl.Date_max_KaPr = pzoagg.MaxDate2,
					dl.godz_techn = pzoagg.godz_techn,
					dl.godz_work = pzoagg.godz_work,
					dl.godz_techn_OBUN = pzoagg.godz_techn_OBUN,
					dl.godz_work_OBUN = pzoagg.godz_work_OBUN
				WHERE
					dl.commisionGUID = pzoagg.PodzlecGUID AND 
					dl.divGUID = _Div_GUID
				;
			END IF;	--	K - Karty pracy
			
			IF LOCATE('R', _NeedUpdate) > 0 THEN
			--	R - Karty rozliczen
				SELECT FALSE;
			END IF;	--	R - Karty rozliczen
			
			IF LOCATE('P', _NeedUpdate) > 0 THEN
			--	P - Przekazy
			--	Agregacja z Przekazow
				SET @_NeedUpdate = _NeedUpdate;
				DROP TEMPORARY TABLE IF EXISTS `t_PodZelcOkres_Agg`; 
				CREATE TEMPORARY TABLE `t_PodZelcOkres_Agg`
					SELECT
						_PodZlec_GUID AS PodzlecGUID,
						_VarInt(SUM(pw.GUID IS NOT NULL)) As C_MW,
						Date(MIN(pw.Data)) AS MinDate,
						Date(MAX(pw.Data)) AS MaxDate,
						_VarDouble(SUM(pmw.masa)) AS ilosc_work
					FROM `crm_div_links` dl 
					LEFT JOIN pr_przesuniecie_mw pmw ON pmw.caseguid = dl.commisionGUID
					LEFT JOIN crm_commisions pw ON pw.GUID = pmw.GUID_Ng -- AND pw.Rodzaj = 14 AND pw.PodRodzaj in (4,8,9)  
					LEFT JOIN v_pr_division d ON d.GUID = pmw.DivisionGUIDFrom
					WHERE
						dl.commisionGUID = _PodZlec_GUID AND
						dl.divGUID = _Div_GUID  AND
						-- pmw.DivisionGUIDFrom = @W1 AND
						d.GUID_Wydzialu = dl.divGUID
						
					GROUP BY PodzlecGUID
				;
			--	Aktualizuj pola Agg z Przekazow 
				UPDATE `crm_div_links` dl, `t_PodZelcOkres_Agg` pzoagg
				SET
					dl.C_MW = pzoagg.C_MW,
					dl.Date_min_MW = pzoagg.MinDate,
					dl.Date_max_MW = pzoagg.MaxDate,
					dl.ilosc_work = pzoagg.ilosc_work
				WHERE
					dl.commisionGUID = pzoagg.PodzlecGUID AND 
					dl.divGUID = _Div_GUID
				;
				
				
				
			END IF;	--	P - Przekazy
			
			IF LOCATE('A', _NeedUpdate) > 0 THEN
			--	A - Akordy
			--	Agregacja z akord_drp_ng i akord_drp_po
				DROP TEMPORARY TABLE IF EXISTS `t_PodZelcOkres_Agg`; 
				CREATE TEMPORARY TABLE `t_PodZelcOkres_Agg`
					SELECT
						_PodZlec_GUID AS PodzlecGUID,
						_VarInt(SUM(rap.GUID IS NOT NULL)) As C_akord,
						Date(MIN(rap.data_wykonania)) AS MinDate,
						Date(MAX(rap.data_wykonania)) AS MaxDate
					FROM `crm_div_links` dl 
					LEFT JOIN akord_drp_ng rap ON rap.podzlecGUID = dl.commisionGUID AND rap.GUID_division = _Div_GUID 
					LEFT JOIN akord_drp_po rappo ON rappo.GUID_ng = rap.GUID
					LEFT JOIN v_pr_division d ON d.GUID = rap.GUID_division
					WHERE
						dl.commisionGUID = _PodZlec_GUID AND
						dl.divGUID = _Div_GUID  AND
						d.GUID_Wydzialu = dl.divGUID
						-- rap.GUID_division = _Div_GUID AND
					GROUP BY PodzlecGUID
				;
			--	Aktualizuj pola Agg z akord_drp_ng i akord_drp_po 
				UPDATE `crm_div_links` dl, `t_PodZelcOkres_Agg` pzoagg
				SET
					dl.C_akord = pzoagg.C_akord,
					dl.Date_min_akord = pzoagg.MinDate,
					dl.Date_max_akord = pzoagg.MaxDate
				WHERE
					dl.commisionGUID = pzoagg.PodzlecGUID AND 
					dl.divGUID = _Div_GUID
				;
			END IF;	--	A - Akordy
			
			
		--	Aktualizuj C_All
			UPDATE `crm_div_links` dl
			SET
				C_All = _VarInt(C_KaPr + C_MW + C_akord)
			WHERE
				dl.commisionGUID = _PodZlec_GUID AND 
				dl.divGUID = _Div_GUID
			;
			
		--	Ustal daty Start i End jako Min i Max
			SET @MaxD = Date('2100-12-31');
			SET @MinD = Date('1900-12-31');
			
			SELECT
				DATE(Date_min_KaPr), DATE(Date_min_MW), DATE(Date_min_akord),
				DATE(Date_max_KaPr), DATE(Date_max_MW), DATE(Date_max_akord)
			INTO
				@MinKaPr, @MinMW, @MinAkord,
				@MaxKaPr, @MaxMW, @MaxAkord
			FROM `crm_div_links`
			WHERE
				commisionGUID = _PodZlec_GUID AND divGUID = _Div_GUID
			;
			SET @MinKaPr = IF(@MinKaPr = _ZeroDate, NULL, @MinKaPr);
			SET @MinMW = IF(@MinMW = _ZeroDate, NULL, @MinMW);
			SET @MinAkord = IF(@MinAkord = _ZeroDate, NULL, @MinAkord);
			IF @MinKaPr IS NULL AND @MinMW IS NULL AND @MinAkord IS NULL THEN
				SET @StartDate = _crm_StartTime;
			ELSE
				SET @MinKaPr = Date(IF(IFNULL(@MinKaPr, _ZeroDate) = _ZeroDate, @MaxD, @MinKaPr));
				SET @MinMW = Date(IF(IFNULL(@MinMW, _ZeroDate) = _ZeroDate, @MaxD, @MinMW));
				SET @MinAkord = Date(IF(IFNULL(@MinAkord, _ZeroDate) = _ZeroDate, @MaxD, @MinAkord));
				SET @StartDate = Date(
						IF(@MinKaPr <= @MinMW AND @MinKaPr <= @MinAkord, @MinKaPr,
							IF(@MinMW <= @MinKaPr AND @MinMW <= @MinAkord, @MinMW,
							@MinAkord))
				);
			END IF;
			SET @MaxKaPr = IF(@MaxKaPr = _ZeroDate, NULL, @MaxKaPr);
			SET @MaxMW = IF(@MaxMW = _ZeroDate, NULL, @MaxMW);
			SET @MaxAkord = IF(@MaxAkord = _ZeroDate, NULL, @MaxAkord);
			IF @MaxKaPr IS NULL AND @MaxMW IS NULL AND @MaxAkord IS NULL THEN
				SET @EndDate = _crm_EndTime;
			ELSE
				SET @MaxKaPr = Date(IF(IFNULL(@MaxKaPr, _ZeroDate) = _ZeroDate, @MinD, @MaxKaPr));
				SET @MaxMW = Date(IF(IFNULL(@MaxMW, _ZeroDate) = _ZeroDate, @MinD, @MaxMW));
				SET @MaxAkord = Date(IF(IFNULL(@MaxAkord, _ZeroDate) = _ZeroDate, @MinD, @MaxAkord));
				SET @EndDate = Date(
						IF(@MaxKaPr >= @MaxMW AND @MaxKaPr >= @MaxAkord, @MaxKaPr,
							IF(@MaxMW >= @MaxKaPr AND @MaxMW >= @MaxAkord, @MaxMW,
							@MaxAkord))
				);
				
			END IF;
			
			
			UPDATE `crm_div_links` dl
			SET
				dl.Date_start = Date(@StartDate),
				dl.Date_end = Date(@EndDate),
				dl.NeedUpdate = ''
			WHERE
				dl.commisionGUID = _PodZlec_GUID AND 
				dl.divGUID = _Div_GUID
			;
			
		END;
	END IF;	--	IF _MODE = 'UPDATE_EXECUTE'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	Zamknij zlecenie _PodZlec_GUID dla wydzialu _Div_GUID 
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'CLOSE' THEN
		SET _DateClose = DATE(IFNULL(_Param_A, '2009-09-01'));
		SET _DateUp = DATE(IFNULL(_Param_B, '2009-10-01'));
		CALL pr_div_links_Tool('RANGE', _PodZlec_GUID, _Div_GUID, _DateClose, _DateUp, '', '');
		DROP TEMPORARY TABLE IF EXISTS `t_datezlec`; 
		CREATE TEMPORARY TABLE `t_datezlec`
		SELECT 
			zl.commisionGUID, zl.DivGUID, zl.Date_end,
			DATE(IF(_Mode = 10, _DateClose, DATE_ADD(zl.Date_end, INTERVAL 1 MONTH))) As DateClose,
			DATE_FORMAT(zl.Date_end, '%Y-%m-01') AS FirstDay, 				-- Pierwszy dzie m-ca z daty zamk.
			DATE(zl.Date_end) AS FirstDayMonthLater,
			DATE(zl.Date_end) AS EndTime,
			DATE(zl.Date_end) AS DateDeadLine,
			DAYOFWEEK(zl.Date_end) AS DayOfWeek
			FROM `t_Range_zlec` zl
			WHERE
				zl.Status = 0
			LIMIT 2500	
		;
		UPDATE `t_datezlec`
		SET
			FirstDay = DATE_FORMAT(DateClose, '%Y-%m-01'),
			FirstDayMonthLater = DATE_ADD(FirstDay, INTERVAL 1 MONTH),	-- Pierwszy dzien nast. m-ca 
			EndTime = DATE_SUB(FirstDayMonthLater, INTERVAL 1 DAY),		-- Ostatni dzien m-ca z daty zamk.
			DateDeadLine = DATE_ADD(FirstDayMonthLater, INTERVAL 4 DAY),	-- Czwarty dzien nast. m-ca
			DayOfWeek = DAYOFWEEK(DateDeadLine)
		;
		UPDATE `t_datezlec`
		SET
			DateDeadLine = IF(DayOfWeek = 1, DATE_ADD(DateDeadline, INTERVAL 1 DAY),
				IF(DayOfWeek = 7, DATE_ADD(DateDeadline, INTERVAL 2 DAY),
				DateDeadLine))
		;

		UPDATE `crm_div_links` zl, `t_datezlec` dz
		SET
			zl.`Status` = 1,
			zl.`Date_close` = dz.`DateDeadLine`		-- Czas ostateczny (zamkniecie)  -  Czwarty dzien nast. m-ca
		--	zl.`Date_end` = dz.`EndTime`				-- Czas zakonczenia  -  Ostatni dzien m-ca z daty zamk.
		WHERE
			zl.`commisionGUID` = dz.`commisionGUID` AND 
			zl.`divGUID` = dz.`DivGUID`
		;
		
		SELECT * FROM `t_datezlec`;
		DROP TEMPORARY TABLE IF EXISTS `t_datezlec`; 
	END IF;	--	IF _MODE = 'CLOSE'
	
	
	IF _MODE = 'REOPEN' THEN
--	Odblokuj (otworz zlecenie) 		
		UPDATE `crm_div_links` zl
		SET
			zl.`Status` = 0
		WHERE
			zl.`commisionGUID` = _PodZlec_GUID AND 
			zl.`divGUID` = _Div_GUID
		;
	END IF;	--	IF _MODE = 'REOPEN'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'DIVLINKS_UPDATE_TIME'
		Przeliczanie crm_div_links wg aLOcalTime
		Parametry 	BRAK
		CALL pr_div_links_Tool('DIVLINKS_UPDATE_TIME', '', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'DIVLINKS_UPDATE_TIME' THEN
	--	Pobierz LastUpdateDivLinks w `_db_globals`
		CALL _Globals_Get('LastUpdateDivLinks', @LastTime, NULL);
		
		DROP TEMPORARY TABLE IF EXISTS `t_dl_need_update`; 
		CREATE TEMPORARY TABLE `t_dl_need_update` LIKE `crm_div_links`;
		INSERT IGNORE INTO `t_dl_need_update`
			SELECT
				dl.*
			FROM `crm_div_links` dl
			WHERE
				(dl.alocaltime > @LastTime)
				-- (dl.NeedUpdate_krz = 1) OR (dl.NeedUpdate_kpr = 1)
			LIMIT 5000	
		;
		
	--	Agregacja z KaPr LOG
		DROP TEMPORARY TABLE IF EXISTS `t_dl_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_dl_need_update_calc`
			SELECT 
				tu.CommisionGUID AS CommisionGUID, 
				tu.DivGUID,
				Date(MIN(LEAST(IFNULL(KaPr.StartTime, '2100-01-01'), IFNULL(log.modify_date, '2100-01-01')))) AS Date_min_KaPr,
				Date(MAX(GREATEST(IFNULL(KaPr.StartTime, '0000-00-00'), IFNULL(log.modify_date, '0000-00-00')))) AS Date_max_KaPr,
				_VarInt(SUM(KaPr.GUID IS NOT NULL)) AS C_KaPr
				-- 		_VarDouble(SUM(log.time_duration)) AS godz_techn,	--	Godziny technologiczne z LOG-a (godz. zarejestrowane)
				-- 		_VarDouble(SUM(log.time_sheet)) AS godz_work,		--	Godziny technologiczne z LOG-a (godz. realizowane)
				-- 		_VarDouble(SUM(IF(KaPr.PodRodzaj = 7, log.time_duration, 0))) AS godz_techn_OBUN,	--	Godziny technologiczne OBUN z LOG-a (godz. zarejestrowane)
				-- 		_VarDouble(SUM(IF(KaPr.PodRodzaj = 7, log.time_sheet, 0))) AS godz_work_OBUN		--	Godziny technologiczne OBUN z LOG-a (godz. realizowane)
			FROM `t_dl_need_update` tu 
			INNER JOIN crm_commisions KaPr ON KaPr.Case_GUID = tu.commisionGUID AND KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) 
			LEFT JOIN crm_log log ON log.commissionguid = KaPr.GUID
			LEFT JOIN erp_area_nodes ean ON ean.GUID = log.AreaGUID
			INNER JOIN v_pr_division d ON d.GUID = ean.Dzial AND d.GUID_Wydzialu = tu.divGUID
			WHERE
				log.modify_date IS NOT NULL AND 
				(Date(log.modify_date) != _ZeroDate)
			GROUP BY CommisionGUID, DivGUID
		;
	--	Aktualizuj pola Agg  z KaPr LOG 
		UPDATE `crm_div_links` dl, `t_dl_need_update_calc` calc
		SET
			dl.C_KaPr = calc.C_KaPr,
			dl.Date_min_KaPr = calc.Date_min_KaPr,
			dl.Date_max_KaPr = calc.Date_max_KaPr
			-- dl.godz_techn = calc.godz_techn,
			-- dl.godz_work = calc.godz_work,
			-- dl.godz_techn_OBUN = calc.godz_techn_OBUN,
			-- dl.godz_work_OBUN = calc.godz_work_OBUN
		WHERE
			dl.commisionGUID = calc.CommisionGUID AND 
			dl.divGUID = calc.DivGUID
		;
		
	--	Agregacja z Przekazow
		DROP TEMPORARY TABLE IF EXISTS `t_dl_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_dl_need_update_calc`
			SELECT 
				tu.CommisionGUID AS CommisionGUID, 
				tu.DivGUID,
				Date(MIN(pw.Data)) AS Date_min_MW,
				Date(MAX(pw.Data)) AS Date_max_MW,
				_VarInt(SUM(pw.GUID IS NOT NULL)) AS C_MW,
				_VarDouble(SUM(pmw.masa)) AS ilosc_work
			FROM `t_dl_need_update` tu 
			LEFT JOIN pr_przesuniecie_mw pmw ON pmw.caseguid = tu.commisionGUID AND tu.divGUID IN (pmw.DivisionGUIDFrom, pmw.DivisionGUIDTo)
			LEFT JOIN crm_commisions pw ON pw.GUID = pmw.GUID_Ng -- AND pw.Rodzaj = 14 AND pw.PodRodzaj in (4,8,9)  
			WHERE
				pw.Data IS NOT NULL 
			GROUP BY CommisionGUID, DivGUID
		;
	--	Aktualizuj pola Agg z Przekazow 
		UPDATE `crm_div_links` dl, `t_dl_need_update_calc` calc
		SET
			dl.C_MW = calc.C_MW,
			dl.Date_min_MW = calc.Date_min_MW,
			dl.Date_max_MW = calc.Date_max_MW,
			dl.ilosc_work = calc.ilosc_work
		WHERE
			dl.commisionGUID = calc.CommisionGUID AND 
			dl.divGUID = calc.DivGUID
		;
		
		
		DROP TEMPORARY TABLE IF EXISTS `t_dl_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_dl_need_update_calc`
			SELECT
				tu.CommisionGUID AS CommisionGUID,
				tu.DivGUID,
				_VarInt(SUM(agz.kpr_C + agz.krz_C)) AS C_KaPr,
				Date(MIN(LEAST(IFNULL(agz.kpr_FirstDate, '2100-01-01'), IFNULL(agz.krz_FirstDate, '2100-01-01')))) AS Date_min_KaPr,
				Date(MAX(GREATEST(IFNULL(agz.kpr_LastDate, '0000-00-00'), IFNULL(agz.krz_LastDate, '0000-00-00')))) AS Date_max_KaPr,
				
				SUM(_varDOUBLE_NOTNULL(agz.TimeCaps)) AS TimeCaps,		
				SUM(_varDOUBLE_NOTNULL(agz.TimeCapsOBUN)) AS TimeCapsOBUN,		
				SUM(_varDOUBLE_NOTNULL(agz.TimeDone)) AS TimeDone,		
				SUM(_varDOUBLE_NOTNULL(agz.TimeDoneOBUN)) AS TimeDoneOBUN,		
				SUM(_varDOUBLE_NOTNULL(agz.Seria)) AS Seria,		
				SUM(_varDOUBLE_NOTNULL(agz.SeriaDone)) AS SeriaDone,		
				
				SUM(_varDOUBLE_NOTNULL(Wartosc)) AS Wartosc
				
			FROM `t_dl_need_update` tu 
			INNER JOIN ad_pr_gniazda_zlec agz ON agz.PodzlecGUID = tu.CommisionGUID AND agz.DivGUID = tu.DivGUID 
			WHERE
				tu.NeedUpdate_kpr = 1 OR tu.NeedUpdate_krz = 1
			GROUP BY CommisionGUID, DivGUID
		;
	--	Uzupelnij `ad_pr_gniazda_zlec`	
		UPDATE `crm_div_links` dl, `t_dl_need_update_calc` calc
		SET
			dl.C_KaPr = calc.C_KaPr,
			dl.Date_min_KaPr = calc.Date_min_KaPr,
			dl.Date_max_KaPr = calc.Date_max_KaPr,
			dl.godz_techn = calc.TimeCaps,
			dl.godz_work = calc.TimeDone,
			dl.wart_techn = calc.Wartosc,
			-- dl.wart_work
			dl.godz_techn_OBUN = calc.TimeCapsOBUN,
			dl.godz_work_OBUN = calc.TimeDoneOBUN
			-- dl.wart_techn_OBUN
			-- dl.wart_work_OBUN
		WHERE
			dl.CommisionGUID = calc.CommisionGUID AND
			dl.DivGUID = calc.DivGUID
		;
		
		
		
		
		
		
	--	Aktualizuj C_All
		UPDATE `crm_div_links` dl, `t_dl_need_update` tu
		SET
			dl.C_All = _VarInt(dl.C_KaPr + dl.C_MW + dl.C_akord),
			dl.Date_start = DATE(LEAST(IFNULL(dl.Date_min_KaPr, '2100-01-01'), IFNULL(dl.Date_min_MW, '2100-01-01'), IFNULL(dl.Date_min_akord, '2100-01-01'))),
			dl.Date_end = DATE(GREATEST(IFNULL(dl.Date_max_KaPr, '0000-00-00'), IFNULL(dl.Date_max_MW, '0000-00-00'), IFNULL(dl.Date_max_akord, '0000-00-00')))
		WHERE
			dl.CommisionGUID = tu.CommisionGUID AND
			dl.DivGUID = tu.DivGUID
		;
		
		SELECT COUNT(*) INTO @C_DIVLINKS_UPDATE_TIME FROM t_dl_need_update;
	--	Aktualizuj LastUpdateDivLinks w `_db_globals`
		CALL _Globals_Set('LastUpdateDivLinks', Now());

	END IF;	--	IF _MODE = 'DIVLINKS_UPDATE_TIME'
	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'DIVLINKS_NEED_UPDATE'
	Inicjowanie ad_pr_gniazda_zlec
		Inicjowanie `ad_pr_gniazda_zlec`
		Parametry
			_PodZlec_GUID		Zlecenie
			_Div_GUID		jako AreaGUID 
			_Param_A 		jako zmienna _DateDown - istotne gdy _PodZlec_GUID = '' i _Div_GUID = ''
			_Param_B 		jako zmienna _DateUp - istotne gdy _PodZlec_GUID = '' i _Div_GUID = ''
		CALL pr_div_links_Tool('DIVLINKS_NEED_UPDATE', @CASEguid, 'CG', '', '', '', '');
		CALL pr_div_links_Tool('DIVLINKS_NEED_UPDATE', '', '', '2009-01-01', '2010-01-01', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'DIVLINKS_NEED_UPDATE' THEN
		SET _DateDown = DATE(IFNULL(_Param_A, '2009-09-01'));
		SET _DateUp = DATE(IFNULL(_Param_B, '2009-10-01'));
		SET _PodZlec_GUID = IF(_varSTR_NOTNULL(_PodZlec_GUID) = '*', '', _PodZlec_GUID);
	END IF;	--	IF _MODE = 'DIVLINKS_NEED_UPDATE'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'DIVLINKS_UPDATE'
		Przeliczanie ad_pr_gniazda_zlec czyli tych NeedUpdate_krz = 1 lub NeedUpdate_kpr = 1
		Parametry
			_PodZlec_GUID		Zlecenie może być '*' lub Pusty;
			_Div_GUID		jako AreaGUID może być '*' lub Pusty
		CALL pr_div_links_Tool('DIVLINKS_UPDATE', @CASEguid, 'CG', '', '', '', '');
		CALL pr_div_links_Tool('DIVLINKS_UPDATE', '*', 'CG', '', '', '', '');
		CALL pr_div_links_Tool('DIVLINKS_UPDATE', '*', '*', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'DIVLINKS_UPDATE' THEN
		SET _PodZlec_GUID = IF(_varSTR_NOTNULL(_PodZlec_GUID) = '*', '', _PodZlec_GUID);
		SET _Div_GUID = IF(_varSTR_NOTNULL(_Div_GUID) = '*', '', _Div_GUID);
		DROP TEMPORARY TABLE IF EXISTS `t_dl_need_update`; 
		CREATE TEMPORARY TABLE `t_dl_need_update` LIKE `crm_div_links`;
		INSERT IGNORE INTO `t_dl_need_update`
			SELECT
				dl.*
			FROM `crm_div_links` dl
			WHERE
				(dl.NeedUpdate_krz = 1) OR (dl.NeedUpdate_kpr = 1)
				AND IF(_PodZlec_GUID = '', TRUE, dl.CommisionGUID = _PodZlec_GUID)
				AND IF(_Div_GUID = '', TRUE, dl.divGUID = _Div_GUID)
			LIMIT 1500	
		;
		DROP TEMPORARY TABLE IF EXISTS `t_dl_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_dl_need_update_calc`
			SELECT
				dl.CommisionGUID AS CommisionGUID,
				dl.DivGUID,
				_VarInt(SUM(agz.kpr_C + agz.krz_C)) AS C_KaPr,
				Date(MIN(LEAST(IFNULL(agz.kpr_FirstDate, '2100-01-01'), IFNULL(agz.krz_FirstDate, '2100-01-01')))) AS Date_min_KaPr,
				Date(MAX(GREATEST(IFNULL(agz.kpr_LastDate, '0000-00-00'), IFNULL(agz.krz_LastDate, '0000-00-00')))) AS Date_max_KaPr,
				
				SUM(_varDOUBLE_NOTNULL(agz.TimeCaps)) AS TimeCaps,		
				SUM(_varDOUBLE_NOTNULL(agz.TimeCapsOBUN)) AS TimeCapsOBUN,		
				SUM(_varDOUBLE_NOTNULL(agz.TimeDone)) AS TimeDone,		
				SUM(_varDOUBLE_NOTNULL(agz.TimeDoneOBUN)) AS TimeDoneOBUN,		
				SUM(_varDOUBLE_NOTNULL(agz.Seria)) AS Seria,		
				SUM(_varDOUBLE_NOTNULL(agz.SeriaDone)) AS SeriaDone,		
				
				SUM(_varDOUBLE_NOTNULL(Wartosc)) AS Wartosc
				
			FROM `t_dl_need_update` dl 
			INNER JOIN ad_pr_gniazda_zlec agz ON agz.PodzlecGUID = dl.CommisionGUID AND agz.DivGUID = dl.DivGUID
			WHERE
				dl.NeedUpdate_kpr = 1 OR dl.NeedUpdate_krz = 1
			GROUP BY CommisionGUID, DivGUID
		;
	--	Uzupelnij `ad_pr_gniazda_zlec`	
		UPDATE `crm_div_links` dl, `t_dl_need_update_calc` calc
		SET
			dl.C_KaPr = calc.C_KaPr,
			dl.Date_min_KaPr = calc.Date_min_KaPr,
			dl.Date_max_KaPr = calc.Date_max_KaPr,
			dl.godz_techn = calc.TimeCaps,
			dl.godz_work = calc.TimeDone,
			dl.wart_techn = calc.Wartosc,
			-- dl.wart_work
			dl.godz_techn_OBUN = calc.TimeCapsOBUN,
			dl.godz_work_OBUN = calc.TimeDoneOBUN
			-- dl.wart_techn_OBUN
			-- dl.wart_work_OBUN
		WHERE
			dl.CommisionGUID = calc.CommisionGUID AND
			dl.DivGUID = calc.DivGUID
		;
	--	Aktualizuj C_All
		UPDATE `crm_div_links` dl, `t_dl_need_update_calc` calc
		SET
			dl.C_All = _VarInt(dl.C_KaPr + dl.C_MW + dl.C_akord),
			dl.Date_start = DATE(LEAST(IFNULL(dl.Date_min_KaPr, '2100-01-01'), IFNULL(dl.Date_min_MW, '2100-01-01'), IFNULL(dl.Date_min_akord, '2100-01-01'))),
			dl.Date_end = DATE(GREATEST(IFNULL(dl.Date_max_KaPr, '0000-00-00'), IFNULL(dl.Date_max_MW, '0000-00-00'), IFNULL(dl.Date_max_akord, '0000-00-00')))
		WHERE
			dl.CommisionGUID = calc.CommisionGUID AND
			dl.DivGUID = calc.DivGUID
		;

	END IF;	--	IF _MODE = 'DIVLINKS_UPDATE'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_NEED_UPDATE'
	Inicjowanie ad_pr_gniazda_zlec
		Inicjowanie `ad_pr_gniazda_zlec`
		Parametry
			_PodZlec_GUID		Zlecenie
			_Div_GUID		jako AreaGUID 
			_Param_A 		jako zmienna _DateDown - istotne gdy _PodZlec_GUID = '' i _Div_GUID = ''
			_Param_B 		jako zmienna _DateUp - istotne gdy _PodZlec_GUID = '' i _Div_GUID = ''
		CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', @CASEguid, 'CG', '', '', '', '');
		CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', '', '', '2009-01-01', '2010-01-01', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_NEED_UPDATE' THEN
		SET _DateDown = DATE(IFNULL(_Param_A, '2009-09-01'));
		SET _DateUp = DATE(IFNULL(_Param_B, '2009-10-01'));
		SET _PodZlec_GUID = IF(_varSTR_NOTNULL(_PodZlec_GUID) = '*', '', _PodZlec_GUID);
		SET _AreaGUID = IF(_varSTR_NOTNULL(_Div_GUID) = '*', '', _Div_GUID);
		IF _AreaGUID != '' THEN
			INSERT INTO `ad_pr_gniazda_zlec`
				(PodzlecGUID,AreaGUID,divGUID,div_NawaGUID,NeedUpdate_krz,NeedUpdate_kpr)
				SELECT
					_PodZlec_GUID, _AreaGUID, d.GUID_Wydzialu, d.GUID, 1, 1 
				FROM erp_area_nodes ean
				LEFT JOIN v_pr_division d ON d.GUID = ean.Dzial
				WHERE
					ean.GUID = _AreaGUID
			ON DUPLICATE KEY UPDATE 
				NeedUpdate_krz = 1,
				NeedUpdate_kpr = 1 
			;	
		END IF;		
		IF _AreaGUID = '' THEN
		--	Kreuje na podstawie starych KaPr w zakresie dat BETWEEN _DateDown AND _DateUp
			DROP TEMPORARY TABLE IF EXISTS `t_new_zlecenia_gniazda`; 
			CREATE TEMPORARY TABLE `t_new_zlecenia_gniazda`
				SELECT
					KaPr.Case_GUID AS PodzlecGUID,
					log.AreaGUID AS AreaGUID
				FROM `crm_commisions` KaPr 
				INNER JOIN crm_log log ON log.commissionguid = KaPr.GUID 
				WHERE
					KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) AND
					log.modify_date BETWEEN _DateDown AND _DateUp AND
					(Date(log.modify_date) != '0000-00-00')
				GROUP BY PodzlecGUID, AreaGUID
			;
			INSERT INTO `ad_pr_gniazda_zlec`
				(PodzlecGUID,AreaGUID,divGUID,div_NawaGUID,NeedUpdate_krz,NeedUpdate_kpr)
				SELECT
					t_new.PodzlecGUID, t_new.AreaGUID, d.GUID_Wydzialu, d.GUID, 1, 1 
				FROM `t_new_zlecenia_gniazda` t_new
				INNER JOIN `erp_area_nodes` ean ON ean.GUID = t_new.AreaGUID
				INNER JOIN `v_pr_division` d ON d.GUID = ean.Dzial
			ON DUPLICATE KEY UPDATE 
				NeedUpdate_krz = 1,
				NeedUpdate_kpr = 1 
			;	
		END IF;		
		/*
		INSERT IGNORE INTO `ad_pr_gniazda_zlec`
			(PodzlecGUID,AreaGUID,divGUID,div_NawaGUID,NeedUpdate_krz,NeedUpdate_kpr)
			SELECT
				_PodZlec_GUID, _Div_GUID, d.GUID_Wydzialu, d.GUID, 1, 1 
			FROM erp_area_nodes ean
			LEFT JOIN v_pr_division d ON d.GUID = ean.Dzial
			WHERE
				ean.GUID = _Div_GUID
		;
		*/		
	END IF;	--	IF _MODE = 'GNIAZDA_NEED_UPDATE'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_UPDATE_TIME'
		Przeliczanie ad_pr_gniazda_zlec wg aLocalTime
		Parametry 	BRAK
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_TIME', '', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_UPDATE_TIME' THEN
	
	--	Pobierz LastUpdate_ad_pr_gniazda_zlec w `_db_globals`
		CALL _Globals_Get('LastUpdate_ad_pr_gniazda_zlec', @LastTime, NULL);
		
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_need_update`; 
		CREATE TEMPORARY TABLE `t_gniazda_need_update` LIKE `ad_pr_gniazda_zlec`;
		INSERT IGNORE INTO `t_gniazda_need_update`
			SELECT
				agz.*
			FROM `ad_pr_gniazda_zlec` agz
			WHERE
				(agz.localtime > @LastTime)
			LIMIT 5000	
		;
	-- 	PRZELICZ `ad_pr_gniazda_zlec` na podstawie `t_gniazda_need_update` tworzonej w GNIAZDA_UPDATE	
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_DO', '', '', '', '', '', '');
	--	Aktualizuj LastUpdate_ad_pr_gniazda_zlec w `_db_globals`
		CALL _Globals_Set('LastUpdate_ad_pr_gniazda_zlec', Now());
		
		
	END IF;	--	IF _MODE = 'GNIAZDA_UPDATE_TIME'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_UPDATE'
		Przeliczanie ad_pr_gniazda_zlec czyli tych NeedUpdate_krz = 1 lub NeedUpdate_kpr = 1
		Parametry
			_PodZlec_GUID		Zlecenie może być '*' lub Pusty;
			_Div_GUID		jako AreaGUID może być '*' lub Pusty
		CALL pr_div_links_Tool('GNIAZDA_UPDATE', @CASEguid, 'CG', '', '', '', '');
		CALL pr_div_links_Tool('GNIAZDA_UPDATE', '*', 'CG', '', '', '', '');
		CALL pr_div_links_Tool('GNIAZDA_UPDATE', '*', '*', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_UPDATE' THEN
		SET _PodZlec_GUID = IF(_varSTR_NOTNULL(_PodZlec_GUID) = '*', '', _PodZlec_GUID);
		SET _AreaGUID = IF(_varSTR_NOTNULL(_Div_GUID) = '*', '', _Div_GUID);
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_need_update`; 
		CREATE TEMPORARY TABLE `t_gniazda_need_update` LIKE `ad_pr_gniazda_zlec`;
		INSERT IGNORE INTO `t_gniazda_need_update`
			SELECT
				agz.*
			FROM `ad_pr_gniazda_zlec` agz
			WHERE
				(agz.NeedUpdate_krz = 1) OR (agz.NeedUpdate_kpr = 1)
				AND IF(_PodZlec_GUID = '', TRUE, agz.PodZlecGUID = _PodZlec_GUID)
				AND IF(_AreaGUID = '', TRUE, agz.AreaGUID = _AreaGUID)
			LIMIT 5000	
		;
	-- 	PRZELICZ `ad_pr_gniazda_zlec` na podstawie `t_gniazda_need_update` tworzonej w GNIAZDA_UPDATE	
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_DO', '', '', '', '', '', '');
		
	END IF;	--	IF _MODE = 'GNIAZDA_UPDATE'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GNIAZDA_UPDATE_DO'
		Przeliczanie `ad_pr_gniazda_zlec` na podstawie `t_gniazda_need_update` tworzonej w GNIAZDA_UPDATE
		Parametry
			BRAK
		CALL pr_div_links_Tool('GNIAZDA_UPDATE_DO', '', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'GNIAZDA_UPDATE_DO' THEN
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_gniazda_need_update_calc`
			SELECT
				dl.PodZlecGUID AS PodzlecGUID,
				dl.AreaGUID,
				_VarInt(COUNT(DISTINCT KaPr.GUID )) As kpr_C,
				Date(MIN(Date(log.modify_date))) AS kpr_FirstDate,
				Date(MAX(Date(log.modify_date))) AS kpr_LastDate,
				SUM(_varDOUBLE_NOTNULL(IF(log.tag = 1, IF(KaPr.PodRodzaj = 6, log.time_duration,0),0))) AS kpr_TimeCaps,		--	Godziny technologiczne z LOG-a (godz. zarejestrowane)
				SUM(_varDOUBLE_NOTNULL(IF(log.tag = 1, IF(KaPr.PodRodzaj = 7, log.time_duration,0),0))) AS kpr_TimeCapsOBUN,		--	Godziny technologiczne z LOG-a (godz. zarejestrowane)
				SUM(_varDOUBLE_NOTNULL(IF(log.tag = 2, IF(KaPr.PodRodzaj = 6, log.time_sheet,0),0))) AS kpr_TimeDone,		--	Godziny zreazlizowane z LOG-a (godz. realizowane)
				SUM(_varDOUBLE_NOTNULL(IF(log.tag = 2, IF(KaPr.PodRodzaj = 7, log.time_sheet,0),0))) AS kpr_TimeDoneOBUN		--	Godziny OBUN zreazlizowane z LOG-a (godz. realizowane)
			FROM `t_gniazda_need_update` dl 
			INNER JOIN crm_commisions KaPr ON KaPr.Case_GUID = dl.PodzlecGUID AND KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) 
			INNER JOIN crm_log log ON log.commissionguid = KaPr.GUID AND log.AreaGUID = dl.AreaGUID
			WHERE
				dl.NeedUpdate_kpr = 1 AND
				log.AreaGUID = dl.AreaGUID AND
				log.modify_date IS NOT NULL AND 
				(Date(log.modify_date) != '0000-00-00')
			GROUP BY PodzlecGUID, AreaGUID
		;
	--	Uzupelnij `ad_pr_gniazda_zlec`	
		UPDATE `ad_pr_gniazda_zlec`  agz, `t_gniazda_need_update_calc` calc
		SET
			agz.kpr_C = calc.kpr_C,
			agz.kpr_FirstDate = calc.kpr_FirstDate,
			agz.kpr_LastDate = calc.kpr_LastDate,
			agz.kpr_TimeCaps = calc.kpr_TimeCaps,
			agz.kpr_TimeCapsOBUN = calc.kpr_TimeCapsOBUN,
			agz.kpr_TimeDone = calc.kpr_TimeDone,
			agz.kpr_TimeDoneOBUN = calc.kpr_TimeDoneOBUN,
			agz.TimeCaps = agz.krz_TimeCaps + calc.kpr_TimeCaps,
			agz.TimeCapsOBUN = agz.krz_TimeCapsOBUN + calc.kpr_TimeCapsOBUN,
			agz.TimeDone = agz.krz_TimeDone + calc.kpr_TimeDone,
			agz.TimeDoneOBUN = agz.krz_TimeDoneOBUN + calc.kpr_TimeDoneOBUN
		WHERE
			agz.PodzlecGUID = calc.PodzlecGUID AND
			agz.AreaGUID = calc.AreaGUID
		;
		
		DROP TEMPORARY TABLE IF EXISTS `t_gniazda_need_update_calc`; 
		CREATE TEMPORARY TABLE `t_gniazda_need_update_calc`
			SELECT
				dl.PodZlecGUID AS PodzlecGUID,
				dl.AreaGUID,
				_VarInt(COUNT(DISTINCT krng.GUID)) As krz_C,
				_VarInt(SUM(krng.Status IN (2,3))) As krz_C_Done,
				_VarInt(SUM(krz.Seria)) As Seria,
				_VarInt(SUM(krz.SeriaDone)) As SeriaDone,
				_VarInt(AVG(krng.Progress)) As Progress,
				Date(MIN(Date(krz.DateDone))) AS krz_FirstDate,
				Date(MAX(Date(krz.DateDone))) AS krz_LastDate,
				_VarDouble(SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0, krz.TimeCaps, 0))) AS krz_TimeCaps,		--	Godziny technologiczne (godz. zarejestrowane)
				_VarDouble(SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1, krz.TimeCaps, 0))) AS krz_TimeCapsOBUN,	--	Godziny technologiczne OBUN (godz. zarejestrowane)
				_VarDouble(SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 0, krz.TimeDone, 0))) AS krz_TimeDone,		--	Godziny zreazlizowane (godz. realizowane)
				_VarDouble(SUM(IF(_varINT_NOTNULL(krp.IsOBUN) = 1, krz.TimeDone, 0))) AS krz_TimeDoneOBUN	--	Godziny zreazlizowane OBUN (godz. realizowane)
			FROM `t_gniazda_need_update` dl 
			INNER JOIN pr_krpo_r krz ON krz.PodzlecGUID = dl.PodzlecGUID AND krz.AreaGUID = dl.AreaGUID 
			LEFT JOIN pr_krpo krp ON krp.GUID = krz.krpoGUID 
			INNER JOIN pr_krng krng ON krng.GUID = krz.KrngGUID 
			WHERE
				dl.NeedUpdate_krz = 1 
			GROUP BY PodzlecGUID, AreaGUID
		;
	--	Uzupelnij `ad_pr_gniazda_zlec`	
		UPDATE `ad_pr_gniazda_zlec`  agz, `t_gniazda_need_update_calc` calc
		SET
			agz.krz_C = calc.krz_C,
			agz.krz_C_Done = IFNULL(calc.krz_C_Done, 0),
			agz.krz_FirstDate = calc.krz_FirstDate,
			agz.krz_LastDate = calc.krz_LastDate,
			agz.krz_TimeCaps = calc.krz_TimeCaps,
			agz.krz_TimeCapsOBUN = calc.krz_TimeCapsOBUN,
			agz.krz_TimeDone = calc.krz_TimeDone,
			agz.krz_TimeDoneOBUN = calc.krz_TimeDoneOBUN,
			 
			agz.Seria = calc.Seria,
			agz.SeriaDone = calc.SeriaDone,
			 
			agz.TimeCaps = calc.krz_TimeCaps + agz.kpr_TimeCaps,
			agz.TimeCapsOBUN = calc.krz_TimeCapsOBUN + agz.kpr_TimeCapsOBUN,
			agz.TimeDone = calc.krz_TimeDone + agz.kpr_TimeDone,
			agz.TimeDoneOBUN = calc.krz_TimeDoneOBUN + agz.kpr_TimeDoneOBUN
		WHERE
			agz.PodzlecGUID = calc.PodzlecGUID AND
			agz.AreaGUID = calc.AreaGUID
		;
		
		UPDATE `ad_pr_gniazda_zlec`  agz, `t_gniazda_need_update` dl
		SET
			agz.Progress = ROUND(_varDOUBLE_NOTNULL(agz.SeriaDone/agz.Seria), 4),
			 -- agz.Progress = calc.Progress,
			 
			agz.TimeCaps = agz.krz_TimeCaps + agz.kpr_TimeCaps,
			agz.TimeCapsOBUN = agz.krz_TimeCapsOBUN + agz.kpr_TimeCapsOBUN,
			agz.TimeDone = agz.krz_TimeDone + agz.kpr_TimeDone,
			agz.TimeDoneOBUN = agz.krz_TimeDoneOBUN + agz.kpr_TimeDoneOBUN,
			agz.TimeDoneProc = ROUND(_varDOUBLE_NOTNULL((agz.TimeDone + agz.TimeDoneOBUN)/(agz.TimeCaps + agz.TimeCapsOBUN)), 4),
			agz.NeedUpdate_krz = 0,
			agz.NeedUpdate_kpr = 0 
		WHERE
			agz.PodzlecGUID = dl.PodzlecGUID AND
			agz.AreaGUID = dl.AreaGUID
		;
		
	-- 	Chce wstawic do divlinks-ow pary zlec/dzial	
		CALL pr_div_links_Tool('INIT_NEW', '', '', '0', '', '', '');
		INSERT IGNORE INTO `t_Init_zlec_div`
			SELECT
				gnu.PodZlecGUID, gnu.divGUID
			FROM `t_gniazda_need_update` gnu
		;
		CALL pr_div_links_Tool('INIT_NEW', '', '', 'CREATE', '', '', '');	-- WSTAWIANIE
		
	END IF;	--	IF _MODE = 'GNIAZDA_UPDATE_DO'

END$$



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

-- crm_div_links
SET @CASEguid = '{F1A79CB6-2C12-4950-8C98-57D8A0C688DA}'; #17500 Zlec
SET @CASEguid = '{871CB62C-4846-49C2-9881-80719747BAC5}'; #17500/01 Podzlec
SET @CASEguid = '{453C2091-2AD8-4881-98E4-BEBD6BE5EA1A}'; #17500/02 Podzlec
SET @CASEguid = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}'; #17500/03 Podzlec
SET @CASEguid = '{1EE560C8-F23E-46EF-AFE2-E306F5592708}'; #17500/Sloniasty
SET @CASEguid = '{DD496100-9357-44A8-8629-916584C0271E}'; #17500/Klon
SET @Case_GUID = @CASEguid;

SELECT
	tob.Case_GUID,d.GUID_Wydzialu, 
	-- g.Dzial, 
	COUNT(*) As Top_C,
	SUM(tob.Ilosc_Seria) AS Tob_Seria,
	SUM(top.pr_ilosc_seria) AS Top_Seria,
	SUM(top.Ilosc) AS Top_h,
	top.*
FROM pr_tob tob 
INNER JOIN zmpo top ON top.TobGUID = tob.GUID
-- INNER JOIN erp_area_nodes g ON g.GUID = top.AreaGUID
INNER JOIN v_area_nodes g ON g.GUID = top.AreaGUID
INNER JOIN v_pr_division d ON d.GUID = g.Dzial
WHERE
	tob.Case_GUID = @Case_GUID
GROUP BY d.GUID_Wydzialu	
;

SELECT * FROM v_pr_division;


desc ad_pr_gniazda_zlec;
 
SET @GN = 'IdCA'; 
SET @GN = 'IdCG'; 
CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', @CASEguid, @GN, '', '', '', ''); 
CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', '', '', '2009-09-11', '2010-01-01', '', '');

SELECT 
en.Symbol,
d.Symbol,
d2.Symbol,
a.* 
FROM ad_pr_gniazda_zlec a
LEFT JOIN erp_area_nodes en ON en.GUID = a.AreaGUID
LEFT JOIN erp_division_schema d ON d.GUID = divGUID
LEFT JOIN erp_division_schema d2 ON d2.GUID = div_NawaGUID
;

CALL pr_div_links_Tool('GNIAZDA_UPDATE', '*', '*', '', '', '', '');
SELECT * FROM `t_gniazda_need_update`;
SELECT * FROM `t_gniazda_need_update_calc`;



			DROP TEMPORARY TABLE IF EXISTS `t_new_zlecenia_gniazda`; 
			CREATE TEMPORARY TABLE `t_new_zlecenia_gniazda`
			SELECT
				KaPr.Case_GUID AS PodzlecGUID,
				log.AreaGUID AS AreaGUID
			FROM `crm_commisions` KaPr 
			INNER JOIN crm_log log ON log.commissionguid = KaPr.GUID 
			WHERE
				KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) AND
				log.modify_date BETWEEN '2009-01-01' AND '2009-10-01' AND
				(Date(log.modify_date) != '0000-00-00')
			GROUP BY PodzlecGUID, AreaGUID
		;
		
explain
				SELECT
					t_new.PodzlecGUID, t_new.AreaGUID, d.GUID_Wydzialu, d.GUID, 1, 1 
				FROM `t_new_zlecenia_gniazda` t_new
				INNER JOIN `erp_area_nodes` ean ON ean.GUID = t_new.AreaGUID
				INNER JOIN `v_pr_division` d ON d.GUID = ean.Dzial
		;
SELECT * FROM `t_new_zlecenia_gniazda`;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SERIWS:
	Wstaw wszystkie pary zlecenie/wydzial od 01-01-2009 Z Kart pracy
		DROP TEMPORARY TABLE IF EXISTS `t_ad_insert_crm_div_links`;
		CREATE TEMPORARY TABLE `t_ad_insert_crm_div_links` 
			SELECT
				KaPr.Case_GUID AS commisionGUID,
				d.GUID_Wydzialu As divGUID
			FROM crm_commisions KaPr 
			INNER JOIN crm_log log ON log.commissionguid = KaPr.GUID
				LEFT JOIN erp_area_nodes g ON g.GUID = log.AreaGUID 
				LEFT JOIN v_pr_division d ON d.GUID = g.Dzial 
				INNER JOIN erp_division_schema ediv ON ediv.GUID = d.GUID_Wydzialu
			WHERE
				KaPr.Rodzaj = 14 AND KaPr.PodRodzaj in (6,7) AND
				log.modify_date IS NOT NULL AND 
				(Date(log.modify_date) BETWEEN '2009-01-01' AND '2009-10-01') AND
				log.tag IN (1, 2)
			GROUP BY commisionGUID, divGUID
		;
		
			INSERT IGNORE INTO `crm_div_links`
				-- BYLO chyba rezygnujemy (commisionGUID, divGUID, Status, Date_crm, NeedUpdate)
				(commisionGUID, divGUID, Status, Date_crm)
				SELECT
					izd.commisionGUID, d.GUID_Wydzialu, 0, crm.Data
				FROM `t_ad_insert_crm_div_links` izd
				LEFT JOIN `crm_commisions` crm ON crm.GUID = izd.commisionGUID
				LEFT JOIN v_pr_division d ON IF(izd.divGUID = '*', NOT d.IsNawa AND (d.Symbol like 'W-%' OR d.Symbol = 'MWG'), d.GUID = izd.divGUID)
			;
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SERIWS:
	Wstaw wszystkie pary zlecenie/wydzial od 01-01-2009 Z PRZEKAZOW
		DROP TEMPORARY TABLE IF EXISTS `t_ad_insert_crm_div_links`;
		CREATE TEMPORARY TABLE `t_ad_insert_crm_div_links` 
			SELECT
				pw.Case_GUID AS commisionGUID,
				pmw.DivisionGUIDFrom AS divGUID_1,
				pmw.DivisionGUIDTo AS divGUID_2
			FROM crm_commisions pw
			INNER JOIN `pr_przesuniecie_mw` pmw  ON pmw.GUID_Ng = pw.GUID
			WHERE
				pw.PodRodzaj IN (8, 9) AND
				pw.StartTime IS NOT NULL AND 
				(DATE(pw.StartTime) BETWEEN '2009-09-01' AND '2009-10-30') 
			GROUP BY commisionGUID, divGUID_1, divGUID_2
		;
		
		
			INSERT IGNORE INTO `crm_div_links`
				-- BYLO chyba rezygnujemy (commisionGUID, divGUID, Status, Date_crm, NeedUpdate)
				(commisionGUID, divGUID, Status, Date_crm)
				SELECT
					izd.commisionGUID, d.GUID_Wydzialu, 0, crm.Data
				FROM `t_ad_insert_crm_div_links` izd
				LEFT JOIN `crm_commisions` crm ON crm.GUID = izd.commisionGUID
				LEFT JOIN v_pr_division d ON IF(izd.divGUID_1 = '*', NOT d.IsNawa AND (d.Symbol like 'W-%' OR d.Symbol = 'MWG'), d.GUID = izd.divGUID_1)
			ON DUPLICATE KEY UPDATE 
				NeedUpdate_krz = 1,
				NeedUpdate_kpr = 1,
				NeedUpdate_pmw = 1, 
				NeedUpdate_drp = 1				
			;
			INSERT IGNORE INTO `crm_div_links`
				-- BYLO chyba rezygnujemy (commisionGUID, divGUID, Status, Date_crm, NeedUpdate)
				(commisionGUID, divGUID, Status, Date_crm)
				SELECT
					izd.commisionGUID, d.GUID_Wydzialu, 0, crm.Data
				FROM `t_ad_insert_crm_div_links` izd
				LEFT JOIN `crm_commisions` crm ON crm.GUID = izd.commisionGUID
				LEFT JOIN v_pr_division d ON IF(izd.divGUID_2 = '*', NOT d.IsNawa AND (d.Symbol like 'W-%' OR d.Symbol = 'MWG'), d.GUID = izd.divGUID_2)
			ON DUPLICATE KEY UPDATE 
				NeedUpdate_krz = 1,
				NeedUpdate_kpr = 1,
				NeedUpdate_pmw = 1, 
				NeedUpdate_drp = 1				
			;
		
		UPDATE crm_div_links
		SET
			NeedUpdate = 'P'
		WHERE	
			NeedUpdate_pmw <> 0
		;	

CALL pr_div_links_Tool('UPDATE', '', '', '', '', 'P', '');



------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CALL pr_div_links_Tool('INIT', @CASEguid, @Wydz, '', '', '', '');

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CALL pr_div_links_Tool('DIVLINKS_UPDATE_TIME', '', '', '', '', '', '');
SELECT @C_DIVLINKS_UPDATE_TIME;

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ZAMIEN
pr_ZlecWToku_Update
CALL pr_div_links_Tool('INIT', @CASEguid, @Wydz, '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

DROP PROCEDURE IF EXISTS `pr_kr_Tool`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_kr_Tool`(
	IN _MODE VARCHAR(50),		
	IN _GUID VARCHAR(38),
	IN _CheckID VARCHAR(38),
	IN _Check_Value INTEGER,	
	IN _Param_A VARCHAR(50),	
	IN _Param_B VARCHAR(50),	-- np. _AreaGUID
	IN _Param_C VARCHAR(50),
	IN _Param_D VARCHAR(50)
)
SPROC: BEGIN

	/*	
	_GUID oznacza np. GUID karty rozliczen lub podzlecenia, zlecenia etc
	
	_MODE oznacza:
	
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'DEATAL_krng'		
		Pokazuje detale karty rozliczen (dolny panel) - 
		Parametry
			_GUID (GUID z `pr_krng`)
		CALL pr_kr_Tool('DEATAL_krng', @GKrNg, '', 0, '', '', '', '');		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'NALICZ_krpo_r'	
		Nalicza pola `pr_krpo_r`  - parametrem jest _GUID (GUID z `pr_krpo_r`)
		Parametry
			_GUID			GUID z `pr_krpo_r`
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'NALICZ_krpo'		
		Nalicza `pr_krpo` z wszystkich `pr_krpo_r` - parametrem jest _GUID (GUID z `pr_krpo`)
		Parametry
			_GUID			GUID (GUID z `pr_krpo`) GDY _Param_A = ''
			_Param_A 		Patrz _GUID - GDY <>'' - to parametr GUID oznacza pole krngGUID w `pr_krpo`
		CALL pr_kr_Tool('NALICZ_krpo', @GKrPo, '', 0, '', '', '', '');
		CALL pr_kr_Tool('NALICZ_krpo', @GKrNg, '', 0, 'x', '', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'NALICZ_krng'		
		Ustala ogolny progress karty `pr_krng` z wszystkich `pr_krpo` - parametrem jest _GUID (GUID z `pr_krng`)
		Parametry
			_GUID			GUID (GUID z `pr_krng`)
			_Param_D			GDY _Param_D <> '' to robi show	
		CALL pr_kr_Tool('NALICZ_krng', @GKrNg, '', 0, '', '', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'CLOSE_krng'			
		Zamyka gdy status zrealizowano
		Parametry
			_GUID			GUID lub IDN 
		CALL pr_kr_Tool('CLOSE_krng', @GUID, '', 0, '', '', '', '');
	
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'FIND'			
		Przygotowuje tabele `t_pr_kr_id` z identifikatorami
		Parametry
			_GUID			GUID lub IDN 
			_Param_A 		'krng' - szukam po krng, 'krpo' szukam po krpo, '*' szukam po krng gdy _GUID like 'KRZ-' lub po krpo 
			_Param_B 		'IDN' - szukam po IDN, 'GUID' szukam po GUID
		CALL pr_kr_Tool('FIND', '{7E6D3B02-9EA8-11DE-A5B6-00065BF0376E}', '', 0, 'krng', 'GUID', '', '');
		CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, 'krng', 'NrWewn', '', '1');
		CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, 'krng', 'IDN', '', '1');
		CALL pr_kr_Tool('FIND', '0e6af188292c13e535a1d2acf482eb1e', '', 0, 'krpo', 'GUID', '', '');
		CALL pr_kr_Tool('FIND', 44, '', 0, 'krpo', 'IDN', '', '');
		CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, '*', '', '', '1');
		CALL pr_kr_Tool('FIND', '25', '', 0, '*', '', '', '1');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'ROZLICZ_krpo_INIT'
		Przygotowuje rekord (rekordy) do rozliczania +/-
		Parametry
			Parametry takie jak przy FIND
			_Param_C 		'+' - zmiana puli, '-' - rozliczanie
			_Param_D			'Ng' - dotyczy naglowka, 'Po' - dotyczy pozycji (1 lub wielu, a  to zalezy od tego czy _KrPoIDN <> 0)				
			
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'ROZLICZ_krpo_APPLY'
		Zapisuje rekord (rekordy) do rozliczania +/- i przelicza
		Parametry
			BRAK
			
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'EDIT_krng'			
		Przygotowuje tabele ng, po, po_r do edycji/rozliczen
		Parametry
			_GUID			jako IDN w `pr_krng`
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'EDIT'			
		Przygotowuje tabele ng, po, po_r do edycji/rozliczen (WOLA 'FIND')
		Parametry
			_GUID			GUID lub IDN 
			_Param_A 		'krng' - szukam po krng, 'krpo' szukam po krpo
			_Param_B 		'IDN' - szukam po IDN, 'GUID' szukam po GUID
		CALL pr_kr_Tool('EDIT', 35, '', 0, 'krng', 'IDN', '', '');
		CALL pr_kr_Tool('EDIT', '{7E6D3B02-9EA8-11DE-A5B6-00065BF0376E}', '', 0, 'krng', 'GUID', '', '');
		CALL pr_kr_Tool('EDIT', 44, '', 0, 'krpo', 'IDN', '', '');
		CALL pr_kr_Tool('EDIT', '0e6af188292c13e535a1d2acf482eb1e', '', 0, 'krpo', 'GUID', '', '');
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	'PRINT_krng'	
		Pokazuje detale karty rozliczen (dolny panel) - parametrem jest _GUID (GUID z `pr_krng`)
		Parametry
			_GUID			GUID Karty
	------------------------------------------------------------------------------------------------------------------------------------------------------------
			
	0	- 	Kreuj inicjalne tabele
			
	10	- 	Zwroc select z akwizytorami
	11	- 	Zaznacz akwizytora
	12	- 	Zwroc select z akwizytorami zaznaczonymi

	20	- 	Zwroc select jako - wybor sposrod wszystkich zamowien 	
	21	- 	Zaznacz zamowienie
	
	30	-	Zwroc select jako - wybor tylko sposrod zamowien dla zaznaczonych akwizytorow	
	31	- 	Zaznacz zamowienie
	32	- 	Select zamowien zaznaczonych
	33	- 	Select zamowien dla konkretnego Akwizytora _Param_B !!!!!!!!!!!!!!!!!!!!!!!
	
--	Dotyczy paskow ze zlecen oraz krojczego	
	50	-	Zwroc select jako - wybor KatColi
	51	- 	Zaznacz KatCole
	52	- 	Usun z t_prn_zmpo te pozycje ktore nie spelniaja KatCol-i
	53	- 	Ustaw status w zmpo na 32 czyli wydrukowane
	
--	Dotyczy transzy
	60	-	Zwroc select jako - transze
	
	
--	Dotyczy Planu Prodiukcji np. Karty zaladunkowe
	200	-	Kreuj inicjalne tabele
	210	-	Zwroc select z Transzami pole `wyroznik_zlecenia`
	211	-	Zaznacz Transze `wyroznik_zlecenia`
	230	-	Przygotuj do wydruku (pole Delete)
	240	-	Zwroc select z akwizytorami MAGAZYNY
	241	-	Zaznacz akwizytora MAGAZYN
	242	-	Pokaz akwizytora MAGAZYN tylko zaczekowane

	_Symbol oznacza
	KROJ	- 	dla krojczego
		
	*/
	
	DECLARE _NewGUID VARCHAR(38);
	DECLARE _OsobaGUID VARCHAR(38);
	DECLARE _KrNgGUID VARCHAR(38) DEFAULT _GUID;
	DECLARE _KrPoGUID VARCHAR(38) DEFAULT _GUID;
	DECLARE _KrNgIDN INT DEFAULT _GUID;
	DECLARE _KrPoIDN INT DEFAULT _GUID;
	
	DECLARE _LogType VARCHAR(20) DEFAULT '';
	DECLARE _RozliczOneRec INT DEFAULT 1;
	
	DECLARE _AreaGUID VARCHAR(38) DEFAULT _Param_B;
	DECLARE _SeriaDone DOUBLE; 
	DECLARE _TimeCaps DOUBLE;
	DECLARE _TimeDone DOUBLE;
	DECLARE _Progress DOUBLE;
	DECLARE _Wartosc DOUBLE;
	DECLARE _MasaSeria DOUBLE;
	DECLARE _MasaSeriaDone DOUBLE;
	
	SET _Param_A = IFNULL(_Param_A, '');
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'FIND'			
		Przygotowuje tabele `t_pr_kr_id` z identifikatorami
		Parametry
			_GUID			GUID lub IDN 
			_Param_A 		'krng' - szukam po krng, 'krpo' szukam po krpo, '*' szukam po krng gdy _GUID like 'KRZ-' lub po krpo 
			_Param_B 		'IDN' - szukam po IDN, 'GUID' szukam po GUID
		CALL pr_kr_Tool('FIND', '{7E6D3B02-9EA8-11DE-A5B6-00065BF0376E}', '', 0, 'krng', 'GUID', '', '');
		CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, 'krng', 'NrWewn', '', '1');
		CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, 'krng', 'IDN', '', '1');
		CALL pr_kr_Tool('FIND', '0e6af188292c13e535a1d2acf482eb1e', '', 0, 'krpo', 'GUID', '', '');
		CALL pr_kr_Tool('FIND', 44, '', 0, 'krpo', 'IDN', '', '');
		CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, '*', '', '', '1');
		CALL pr_kr_Tool('FIND', '25', '', 0, '*', '', '', '1');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'FIND' THEN
	--	Przygotowuje tabele `t_pr_kr_id` z identifikatorami
		SET _KrNgGUID = '';
		SET _KrNgIDN = 0;
		SET _KrPoGUID = '';
		SET _KrPoIDN = 0;
		CREATE TEMPORARY TABLE IF NOT EXISTS `t_pr_kr_id`(
			`KrNgGUID` VARCHAR(38) NOT NULL DEFAULT '',		--	Klucz glowny z tabeli pr_krng
			`KrNgIDN` INT DEFAULT 0,						--	Klucz IDN glowny z tabeli pr_krng
			`KrPoGUID` VARCHAR(38) NOT NULL DEFAULT '', 			--	Klucz glowny z tabeli pr_krpo
			`KrPoIDN` INT DEFAULT 0						--	Klucz IDN glowny z tabeli pr_krpo
		); 
		DELETE FROM `t_pr_kr_id`;
		
		IF _Param_A = '*' THEN
			IF (LOCATE('KRZ', _GUID) > 0) THEN
				SET _Param_B = 'NrWewn';
				SET _Param_A = 'krng';
			ELSE	
				SET _Param_B = 'IDN';
				SET _Param_A = 'krpo';
			END IF;
		END IF;
		IF _Param_A = 'krpo' THEN
		-- krpo
			IF _Param_B = 'IDN' THEN
				SELECT
					po.IDN, po.GUID, ng.IDN, po.krngGUID
				INTO
					_KrPoIDN, _KrPoGUID, _KrNgIDN, _KrNgGUID
				FROM `pr_krpo` po
				LEFT JOIN `pr_krng` ng ON ng.GUID = po.krngGUID
				WHERE
					po.IDN = _GUID
				;
			ELSE	
				SELECT
					po.IDN, po.GUID, ng.IDN, po.krngGUID
				INTO
					_KrPoIDN, _KrPoGUID, _KrNgIDN, _KrNgGUID
				FROM `pr_krpo` po
				LEFT JOIN `pr_krng` ng ON ng.GUID = po.krngGUID
				WHERE
					po.GUID = _GUID
				;
			END IF;
			SET _KrNgIDN = IFNULL(_KrNgIDN, 0);
			SET _KrNgGUID = IFNULL(_KrNgGUID, '');
			SET _KrPoGUID = IFNULL(_KrPoGUID, '');
			SET _KrPoIDN = IFNULL(_KrPoIDN, 0);
		ELSE		-- krpo
		--	krng
			IF (_Param_B = 'IDN') AND (LOCATE('KRZ', _GUID) > 0) THEN
				SET _Param_B = 'NrWewn';
			END IF;
			IF _Param_B = 'IDN' THEN
				SELECT
					IDN, GUID
				INTO
					_KrNgIDN, _KrNgGUID
				FROM pr_krng
				WHERE
					IDN = _GUID
				;
			ELSE	
				IF _Param_B = 'NrWewn' THEN
					SELECT
						IDN, GUID
					INTO
						_KrNgIDN, _KrNgGUID
					FROM pr_krng
					WHERE
						NrWewn = _GUID
					;
				ELSE
					SELECT
						IDN, GUID
					INTO
						_KrNgIDN, _KrNgGUID
					FROM pr_krng
					WHERE
						GUID = _GUID
					;
				END IF;
			END IF;
			SET _KrNgGUID = IFNULL(_KrNgGUID, '');
			SET _KrNgIDN = IFNULL(_KrNgIDN, 0);
		END IF;	-- krng
		INSERT INTO `t_pr_kr_id`
			SELECT _KrNgGUID, _KrNgIDN, _KrPoGUID, _KrPoIDN
		;
		IF _Param_D <> '' THEN
			SELECT * FROM `t_pr_kr_id`;
		END IF;	-- SELECT
	
	END IF; 	-- _MODE = 'FIND' 
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'EDIT'			
		Przygotowuje tabele ng, po, po_r do edycji/rozliczen (WOLA 'FIND')
		Parametry
			_GUID			GUID lub IDN 
			_Param_A 		'krng' - szukam po krng, 'krpo' szukam po krpo
			_Param_B 		'IDN' - szukam po IDN, 'GUID' szukam po GUID
		CALL pr_kr_Tool('EDIT', 35, '', 0, 'krng', 'IDN', '', '');
		CALL pr_kr_Tool('EDIT', '{7E6D3B02-9EA8-11DE-A5B6-00065BF0376E}', '', 0, 'krng', 'GUID', '', '');
		CALL pr_kr_Tool('EDIT', 44, '', 0, 'krpo', 'IDN', '', '');
		CALL pr_kr_Tool('EDIT', '0e6af188292c13e535a1d2acf482eb1e', '', 0, 'krpo', 'GUID', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'EDIT' THEN
		SET _KrNgGUID = '';
		SET _KrNgIDN = 0;
		SET _KrPoGUID = '';
		SET _KrPoIDN = 0;
	-- Szukaj
		CALL pr_kr_Tool('FIND', _GUID, _CheckID, _Check_Value, _Param_A,	_Param_B, _Param_C, _Param_D);
		SELECT 
			KrPoIDN, KrPoGUID, KrNgIDN, KrNgGUID
		INTO 
			_KrPoIDN, _KrPoGUID, _KrNgIDN, _KrNgGUID
		FROM `t_pr_kr_id`	
		;
		IF _KrNgIDN != 0 THEN
			CALL pr_kr_Tool('EDIT_krng', _KrNgIDN, '', 0, '', '', '', '');
		
		END IF;
		
	END IF; 	-- _MODE = 'EDIT' 
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'EDIT_krng'			
		Przygotowuje tabele ng, po, po_r do edycji/rozliczen
		Parametry
			_GUID			jako IDN w `pr_krng`
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'EDIT_krng' THEN
	--	Tutaj zawsze parametr _GUID jako IDN w `pr_krng`
		SET _KrNgGUID = IFNULL(_KrNgGUID, '');
		SET _KrNgIDN = IFNULL(_KrNgIDN, 0);
		SET _KrPoGUID = IFNULL(_KrPoGUID, '');
		SET _KrPoIDN = IFNULL(_KrPoIDN, 0);
		IF _Param_D = '' THEN
			SELECT
				IDN, GUID
			INTO
				_KrNgIDN, _KrNgGUID
			FROM pr_krng
			WHERE
				IDN = _GUID
			;
		ELSE
			SELECT
				IDN, GUID
			INTO
				_KrNgIDN, _KrNgGUID
			FROM pr_krng
			WHERE
				GUID = _GUID
			;
		END IF; --	_Param_D	
		SET _KrNgGUID = IFNULL(_KrNgGUID, '');
		SET _KrNgIDN = IFNULL(_KrNgIDN, 0);
		DROP TEMPORARY TABLE IF EXISTS `t_pr_krng`;
		CREATE TEMPORARY TABLE `t_pr_krng` 
			SELECT 
				_VarINT(0) AS ModifyStatus, 
				kn.*,
				_VarDouble(kn.Progress*100) AS Progress100, 
				_VarStr(_ConcatSTR_NotEqu(an.Symbol, an.Nazwa,  ' - ')) As Gniazdo, 
				_VarStr(_ConcatSTR_NotEqu(wg.Nazwa, wg.Symbol, ' - ')) AS Brygada, 
				_VarStr(_ConcatSTR_NotEqu(d.symbol, d.Nazwa,  ' - ')) AS Wydzial,
				_VarStr(_ConcatSTR_NotEqu(vd1.symbol, vd1.Nazwa,  ' - ')) AS NawaWydzial,
				sp.DisplayValue as StatusStr,
				_VarINT(IFNULL(dl.Status, 0)) AS Div_Status,
				_VarINT((IFNULL(dl.Status, 0) < 2) AND IFNULL(CURRENT_DATE > CAST(dl.Date_close AS DATE), 0)) AS Div_CurrDayIsNotValid,
				DATE(dl.Date_close) AS Div_Date_close,
				DATE(DATE_SUB(DATE_FORMAT(dl.Date_close, '%Y-%m-01'), INTERVAL 1 DAY)) AS Div_Date_LastDay
			FROM `pr_krng` kn
			LEFT JOIN `erp_area_nodes` an ON an.GUID = kn.AreaGUID
			LEFT JOIN `erp_workgroups` wg ON wg.GUID = kn.WorkgroupGUID
			LEFT JOIN `v_pr_division` vd1 on vd1.GUID = an.dzial
			LEFT JOIN `crm_div_links` dl ON dl.commisionGUID = kn.PodzlecGUID AND dl.divGUID = vd1.GUID_Wydzialu  
			LEFT JOIN `erp_division_schema` d ON d.guid = vd1.GUID_Wydzialu
			LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kn.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
			WHERE
				kn.IDN = _KrNgIDN
		;
		IF _Param_D = '' THEN
			DROP TEMPORARY TABLE IF EXISTS `t_pr_krpo`;
			CREATE TEMPORARY TABLE `t_pr_krpo` 
				SELECT
					_VarINT(0) AS ModifyStatus, 
					kp.*,
					_VarDouble(kp.Progress*100) AS Progress100, 
					_VarStr(_ConcatSTR_NotEqu(an.Symbol, an.Nazwa,  ' - ')) As Gniazdo, 
					_VarStr(_ConcatSTR_NotEqu(wg.Nazwa, wg.Symbol, ' - ')) AS Brygada, 
					_VarStr(_ConcatSTR_NotEqu(d.symbol, d.Nazwa,  ' - ')) AS Wydzial,
					_VarStr(_ConcatSTR_NotEqu(vd1.symbol, vd1.Nazwa,  ' - ')) AS NawaWydzial,
					sp.DisplayValue AS StatusStr,
					def.Nazwa AS Tob_Def_Obj_Nazwa,
					_VarStr(_ConcatSTR_NotEqu(tob.Symbol, tob.Nazwa,  ' - ')) As TobSymbolNazwa, 
					_VarStr(_ConcatSTR_NotEqu(aso.Symbol, aso.Nazwa,  ' - ')) As AsortymentNazwa, 
					tob.Asortyment
				FROM `pr_krpo` kp
				LEFT JOIN `erp_area_nodes` an ON an.GUID = kp.AreaGUID
				LEFT JOIN `erp_workgroups` wg ON wg.GUID = kp.WorkgroupGUID
				LEFT JOIN `v_pr_division` vd1 on vd1.GUID = an.dzial
				LEFT JOIN `erp_division_schema` d ON d.guid = vd1.GUID_Wydzialu
				LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kp.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
				LEFT JOIN `pr_tob` tob ON tob.GUID = kp.TobGUID
				LEFT JOIN `erp_asort_types` aso ON aso.GUID = tob.Asortyment
				LEFT JOIN `pr_techn_def_obj` def ON def.IDN = tob.Def_Obj
				WHERE
					FALSE 		-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
					AND kp.krngGUID = _KrNgGUID
				GROUP BY kp.GUID	
			;
			DROP TEMPORARY TABLE IF EXISTS `t_pr_krpo_r`;
			CREATE TEMPORARY TABLE `t_pr_krpo_r` 
				SELECT
					_VarINT(0) AS ModifyStatus, 
					r.*,
					_VarDouble(r.Progress*100) AS Progress100, 
					_VarStr(_ConcatSTR_NotEqu(an.Symbol, an.Nazwa,  ' - ')) As Gniazdo, 
					_VarStr(_ConcatSTR_NotEqu(wg.Nazwa, wg.Symbol, ' - ')) AS Brygada, 
					_VarStr(_ConcatSTR_NotEqu(d.symbol, d.Nazwa,  ' - ')) AS Wydzial,
					_VarStr(_ConcatSTR_NotEqu(vd1.symbol, vd1.Nazwa,  ' - ')) AS NawaWydzial
				FROM `pr_krpo_r` r
				LEFT JOIN `erp_area_nodes` an ON an.GUID = r.AreaGUID
				LEFT JOIN `erp_workgroups` wg ON wg.GUID = r.WorkgroupGUID
				LEFT JOIN `v_pr_division` vd1 on vd1.GUID = an.dzial
				LEFT JOIN `erp_division_schema` d ON d.guid = vd1.GUID_Wydzialu
				WHERE
					r.krngGUID = _KrNgGUID
			;
		ELSE	
			SELECT
			*
			FROM `t_pr_krng`
			;
		END IF; --	_Param_D	
	END IF; 	-- _MODE = 'EDIT_krng' 
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'NALICZ_krpo_r'	
		Nalicza pola `pr_krpo_r`  - parametrem jest _GUID (GUID z `pr_krpo_r`)
		Parametry
			_GUID			GUID z `pr_krpo_r`
		-- 0 - nierealizowana, 1 - realizacja, 2 - zrealizowana, 3 - zamknieta, 4 - usunieta/anulowana
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'NALICZ_krpo_r' THEN
--	Nalicza pola `pr_krpo_r`  - parametrem jest _GUID (GUID z `pr_krpo_r`)
		UPDATE `pr_krpo_r` r, `pr_krpo` kp
		SET
			r.Progress = r.SeriaDone/IF(r.Seria = 0, kp.Seria, r.Seria)
		WHERE
			r.GUID = _GUID
			AND r.krpoGUID = kp.GUID
		;
	END IF; 	-- _MODE = 'NALICZ_krpo_r' 
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'NALICZ_krpo'		
		Nalicza `pr_krpo` z wszystkich `pr_krpo_r` - parametrem jest _GUID (GUID z `pr_krpo`)
		Parametry
			_GUID			GUID (GUID z `pr_krpo`) GDY _Param_A = ''
			_Param_A 		Patrz _GUID - GDY <>'' - to parametr GUID oznacza pole krngGUID w `pr_krpo`
		CALL pr_kr_Tool('NALICZ_krpo', @GKrPo, '', 0, '', '', '', '');
		CALL pr_kr_Tool('NALICZ_krpo', @GKrNg, '', 0, 'x', '', '', '');
		-- 0 - nierealizowana, 1 - realizacja, 2 - zrealizowana, 3 - zamknieta, 4 - usunieta/anulowana
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'NALICZ_krpo' THEN
	--	Nalicza Progress `pr_krpo` z wszystkich `pr_krpo_r` - parametrem jest _GUID (GUID z `pr_krpo`)
		DROP TEMPORARY TABLE IF EXISTS `t_c_pr_krpo`;
		CREATE TEMPORARY TABLE `t_c_pr_krpo` 
			SELECT 
				kp.GUID, SUM(r.SeriaDone) AS _SeriaDone, SUM(r.TimeCaps) AS _TimeCaps, SUM(r.TimeDone) AS _TimeDone
			FROM `pr_krpo` kp
			INNER JOIN `pr_krpo_r` r ON r.krpoGUID = kp.GUID
			WHERE
				IF(_Param_A = '', kp.GUID = _GUID, kp.krngGUID = _GUID)
			GROUP BY kp.GUID		
		;
		/*
		SELECT 
			SUM(r.SeriaDone), SUM(r.TimeCaps), SUM(r.TimeDone)
		INTO	
			_SeriaDone, _TimeCaps, _TimeDone
		FROM `pr_krpo_r` r
		WHERE
			r.krpoGUID = _GUID
		;
		*/
		-- 0 - nierealizowana, 1 - realizacja, 2 - zrealizowana, 3 - zamknieta, 4 - usunieta/anulowana
		-- oraz Czas wykonany/Pula czasowa w %
		UPDATE `pr_krpo` kp, `t_c_pr_krpo` kr
		SET
			kp.TimeCaps = kr._TimeCaps,
			kp.TimeDone = kr._TimeDone,
			kp.TimeDoneProc = IF(kr._TimeCaps = 0, 0, ROUND(kr._TimeDone/kr._TimeCaps,4)),		
			kp.SeriaDone = kr._SeriaDone,
			kp.Progress = IF(kp.Seria < 0.0001, 0, IF(kr._SeriaDone >= kp.Seria, 1, kr._SeriaDone/kp.Seria)),
			kp.Status = IF(kp.Status IN (3,4), kp.Status, IF(kp.Progress > 0.999, 2, IF(kp.Progress > 0, 1, 0))) 
		WHERE
			IF(_Param_A = '', kp.GUID = _GUID, kp.krngGUID = _GUID)
			AND kr.GUID = kp.GUID
		;
		UPDATE `pr_krpo` kp
		SET
			kp.Status = IF(kp.Status IN (3,4), kp.Status, IF(kp.Progress > 0.999, 2, IF(kp.Progress > 0, 1, 0))) 
		WHERE
			IF(_Param_A = '', kp.GUID = _GUID, kp.krngGUID = _GUID)
		;
		/*
		UPDATE `pr_krpo` kp
		SET
			kp.TimeCaps = _TimeCaps,
			kp.TimeDone = _TimeDone,
			kp.SeriaDone = _SeriaDone,
			kp.Progress = IF(kp.Seria < 0.0001, 0, IF(_SeriaDone >= kp.Seria, 1, _SeriaDone/kp.Seria)),
			kp.Status = IF(kp.Status IN (3,4), kp.Status, IF(kp.Progress > 0.999, 2, IF(kp.Progress > 0, 1, 0))) 
		WHERE
			kp.GUID = _GUID
		;
		*/
		IF _Param_D <> '' THEN
			SELECT
				kp.GUID, 
				_VarDouble(kp.Progress) AS Progress, 
				_VarDouble(kp.TimeCaps) AS TimeCaps, 
				_VarDouble(SUM(r.TimeCaps)) AS R_TimeCaps, 
				_VarDouble(SUM(r.TimeDone)) AS R_TimeDone, 
				_VarDouble(kp.Seria) AS Seria, _VarDouble(kp.SeriaDone) AS SeriaDone, 
				kp.Status
			FROM `pr_krpo` kp
			INNER JOIN `pr_krpo_r` r ON r.krpoGUID = kp.GUID
			WHERE
				kp.GUID = _GUID
			;			
		END IF;
	END IF; 	-- _MODE = 'NALICZ_krpo' 
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'NALICZ_krng'		
		Ustala ogolny progress karty `pr_krng` z wszystkich `pr_krpo` - parametrem jest _GUID (GUID z `pr_krng`)
		Parametry
			_GUID			GUID (GUID z `pr_krng`)
			_Param_D			GDY _Param_D <> '' to robi show	
		CALL pr_kr_Tool('NALICZ_krng', @GKrNg, '', 0, '', '', '', '');
		-- 0 - nierealizowana, 1 - realizacja, 2 - zrealizowana, 3 - zamknieta, 4 - usunieta/anulowana
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'NALICZ_krng' THEN
	--	Ustala ogolny progress karty `pr_krng` z wszystkich `pr_krpo` - parametrem jest _GUID (GUID z `pr_krng`)
		-- 0 - nierealizowana, 1 - realizacja, 2 - zrealizowana, 3 - zamknieta, 4 - usunieta/anulowana
		-- Wolaj przeliczenie krpo dla rekordow (_ParamA <> '' np. 'x') naglowka parametr _GUID wstaw jjako parametr _GUID call-a
		CALL pr_kr_Tool('NALICZ_krpo', _GUID, '', 0, 'x', '', '', '');
		SELECT 
			_varDOUBLE_NOTNULL(SUM(kp.Progress*kp.TimeCaps)/ SUM(kp.TimeCaps)),
			_varDOUBLE_NOTNULL(SUM(kp.TimeCaps)),
			_varDOUBLE_NOTNULL(SUM(kp.TimeDone)),
			_varDOUBLE_NOTNULL(SUM(kp.Wartosc)),
			_varDOUBLE_NOTNULL(SUM(kp.Masa_J*kp.Seria)),
			_varDOUBLE_NOTNULL(SUM(kp.Masa_J*kp.SeriaDone))
		INTO	
			_Progress, _TimeCaps, _TimeDone, _Wartosc, _MasaSeria, _MasaSeriaDone
		FROM `pr_krpo` kp
		WHERE
			kp.krngGUID = _GUID
		;
		UPDATE `pr_krng` kn
		SET
			kn.TimeDoneProc = IF(_TimeCaps = 0, 0, ROUND(_TimeDone/_TimeCaps, 4)),		
			kn.Progress = ROUND(IF(_Progress < 0.0001, 0, IF(_Progress > 0.999, 1, _Progress)), 4),
			kn.Status = IF(kn.Status IN (3,4), kn.Status, IF(kn.Progress > 0.999, 2, IF(kn.Progress > 0, 1, 0))),
			kn.Wartosc = ROUND(_Wartosc, 4), 
			kn.MasaCaps = ROUND(_MasaSeria, 3),
			kn.MasaDone = ROUND(_MasaSeriaDone, 3)
		WHERE
			kn.GUID = _GUID
		;
		SELECT 
			PodzlecGUID,AreaGUID
		INTO	
			@PodzlecGUID,@AreaGUID
		FROM `pr_krng` kn
		WHERE
			kn.GUID = _GUID
		;
		SET @PodzlecGUID = _VarSTR_NOTNULL(@PodzlecGUID);
		SET @AreaGUID = _VarSTR_NOTNULL(@AreaGUID);
		IF (@PodzlecGUID != '') AND (@AreaGUID != '') THEN
			CALL pr_div_links_Tool('GNIAZDA_NEED_UPDATE', @PodzlecGUID, @AreaGUID, '', '', '', ''); 
		END IF;
		IF _Param_D <> '' THEN
			SELECT
				kn.GUID, kn.Opis, kn.aLocalTime, 
				_VarDouble(SUM(kp.TimeCaps)) AS S_TimeCaps, 
				_VarDouble(SUM(kp.TimeDone)) AS S_TimeDone, 
				_VarDouble(kn.Progress) AS Progress, 
				kn.Status,
				_VarDouble(kn.wartosc) AS wartosc 
			FROM `pr_krng` kn
			INNER JOIN `pr_krpo` kp ON kp.krngGUID = kn.GUID
			WHERE
				kn.GUID = _GUID
			;
		END IF;
	END IF; 	-- _MODE = 'NALICZ_krng'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'CLOSE_krng'			
		Zamyka gdy status zrealizowano
		Parametry
			_GUID			GUID lub IDN 
		CALL pr_kr_Tool('CLOSE_krng', @GUID, '', 0, '', '', '', '');
		-- 0 - nierealizowana, 1 - realizacja, 2 - zrealizowana, 3 - zamknieta, 4 - usunieta/anulowana
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'CLOSE_krng' THEN
	--	Najpierw nalicz naglowek
		CALL pr_kr_Tool('NALICZ_krng', _GUID, '', 0, '', '', '', '');
		UPDATE `pr_krng` kn
		SET
			kn.Status = IF(kn.Status = 2, 3, kn.Status)
		WHERE
			kn.GUID = _GUID
		;
	END IF; 	-- _MODE = 'CLOSE_krng'
		
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'DEATAL_krng'		
		Pokazuje detale karty rozliczen (dolny panel) - 
		Parametry
			_GUID (GUID z `pr_krng`)
		CALL pr_kr_Tool('DEATAL_krng', @GKrNg, '', 0, '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'DEATAL_krng' THEN
	--	_GUID oznacza GUID karty w `pr_krpo` 
		SELECT
			tob.Symbol AS Tob_Symbol, tob.Nazwa AS Tob_Nazwa, 
			_VarDouble(kp.TimeCaps) AS TimeCaps, 
			_VarDouble(SUM(rr.TimeCaps)) AS R_TimeCaps, 
			_VarDouble(kp.TimeDone) AS TimeDone, 
			_VarDouble(SUM(rr.TimeDone)) AS R_TimeDone, 
			
			_VarINT(kp.Seria) AS Seria, 
			_VarDouble(kp.Progress) AS Progress, 
			_VarDouble(kp.Progress*100) AS Progress100, 
			_VarDouble(TimeDoneProc) AS TimeDoneProc,
			kp.Marszruta_G,
			_VarStr(_ConcatSTR_NotEqu(an.Symbol, an.Nazwa,  ' - ')) As Gniazdo, 
			_VarStr(_ConcatSTR_NotEqu(wg.Nazwa, wg.Symbol, ' - ')) AS Brygada, 
			sp.DisplayValue AS StatusStr,
			def.Nazwa AS Tob_Def_Obj_Nazwa,
			_VarStr(_ConcatSTR_NotEqu(tob.Symbol, tob.Nazwa,  ' - ')) As TobSymbolNazwa, 
			_VarStr(_ConcatSTR_NotEqu(aso.Symbol, aso.Nazwa,  ' - ')) As AsortymentNazwa, 
			tob.Asortyment,
			_VarStr_NotNull(sp1.DisplayValue) AS gat_stali,
			_VarStr_NotNull(sp2.DisplayValue) AS atest,
			kp.IDN,
			kp.Wartosc,
			(
				SELECT
				
					Group_CONCAT(
					--	CONCAT('----------------------------------\r\n', OsobaGUID,' dnia: ',(aLocalTime), '\r\n----------------------------------\r\n',
						CONCAT('**********************************\r\n', 
							IF(LogType = '+', '  Pula godz.techn\r\n  ', IF(LogType = '-', '  Rozliczenie\r\n  ', IF(LogType = 'PRINT', '  Wydruk karty\r\n  ', '?'))),
							OsobaGUID,' dnia: ',(aLocalTime), '\r\n**********************************\r\n  ',
							IF(LogType = '+', 
								CONCAT('','Do karty +/-: ', r.TimeCaps, ' h \r\n  ', 'Seria do wyk: ', _VarInt(r.Seria), ' szt.'), 
								IF(LogType = '-',
									CONCAT('','Wykonano godz.techn: ', r.TimeDone, ' h \r\n  ', 'Seria wykonana: ', _VarInt(r.SeriaDone), ' szt.'),
									IF(LogType = 'PRINT', 
										CONCAT('Drukowanie karty rozliczeń'),
										'')
								 )
							), 
							IF(IFNULL(r.Opis, '') != '', CONCAT('\r\n  ',r.Opis), ''),
							'\r\n----------------------------------\r\n'
						) 
						ORDER BY r.IDN SEPARATOR '\r\n'  
					)
				FROM pr_krpo_r r
				WHERE
					(r.krpoGUID = kp.GUID) OR (r.krngGUID = kp.krngGUID AND r.LogType = 'PRINT')
			) AS Log_Info,
			kp.GUID, kp.krngGUID, kp.PodzlecGUID, kp.TobGUID, kp.AreaGUID, kp.lp_int, kp.lp_vc, kp.Rodzaj, kp.IsOBUN 
			
		FROM `pr_krpo` kp
		LEFT JOIN `pr_tob` tob ON tob.GUID = kp.tobGUID
		LEFT JOIN `pr_krpo_r` rr ON (rr.krpoGUID = kp.GUID)
		LEFT JOIN `erp_area_nodes` an ON an.GUID = kp.AreaGUID
		LEFT JOIN `erp_workgroups` wg ON wg.GUID = kp.WorkgroupGUID
		LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kp.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
		LEFT JOIN `erp_asort_types` aso ON aso.GUID = tob.Asortyment
		LEFT JOIN `pr_techn_def_obj` def ON def.IDN = tob.Def_Obj
		LEFT JOIN `slowniki_pierwotne` sp1 ON tob.ParamA = sp1.value AND sp1.slownik_guid = "G_STALI"
		LEFT JOIN `slowniki_pierwotne` sp2 ON tob.atest_id = sp2.value AND sp2.slownik_guid = "ATESTY_STALI"
		WHERE
			kp.krngGUID = _GUID
		GROUP BY kp.GUID	
		;
	
	END IF; 	-- _MODE = 'DEATAL_krng' 
	
	IF _MODE = 'PRINT_krng' THEN
		SET _OsobaGUID = IFNULL(_Param_C, '');
		IF _OsobaGUID = '' THEN
			SET _OsobaGUID = @session.CurrOsobaGUID;
		END IF;
		
		SET _NewGUID = NewGUID();
		INSERT INTO `pr_krpo_r`
			(GUID,PodzlecGUID,krngGUID,krpoGUID,AreaGUID,WorkgroupGUID,LogType,Rodzaj,Progress,TimeCaps,TimeDone,Seria,SeriaDone,IsOBUN,OsobaGUID)
			SELECT
				_NewGUID,
				PodzlecGUID, 				
				_GUID,	
				'',
				AreaGUID, 
				WorkgroupGUID, 
				'PRINT',
				0, 0,
				0, 0,
				0, 0,
				0, 
				_OsobaGUID
			FROM `pr_krng` kn
			WHERE
				kn.GUID = _GUID
		;
		UPDATE `pr_krpo_r`
		SET
			DateDone = aLocalTime
		WHERE
			GUID = _NewGUID
		;
		
	END IF; 	-- _MODE = 'PRINT_krng'	
	
END$$



/*
====================================================================================================================================================================================
	NOTATNIK
====================================================================================================================================================================================
SET @CASEguid = '{F1A79CB6-2C12-4950-8C98-57D8A0C688DA}'; #17500 Zlec
SET @CASEguid = '{871CB62C-4846-49C2-9881-80719747BAC5}'; #17500/01 Podzlec
SET @CASEguid = '{453C2091-2AD8-4881-98E4-BEBD6BE5EA1A}'; #17500/02 Podzlec
SET @CASEguid = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}'; #17500/03 Podzlec
SET @CASEguid = '{1EE560C8-F23E-46EF-AFE2-E306F5592708}'; #17500/Sloniasty
SET @CASEguid = '{DD496100-9357-44A8-8629-916584C0271E}'; #17500/Klon
SET @Case_GUID = @CASEguid;

SET @GKrNg = '{7E6CB6D2-9EA8-11DE-A5B6-00065BF0376E}'; -- ten testuj
SET @GKrNg = '{89B199C6-9D2D-11DE-A5B6-00065BF0376E}'; -- 

SET @GKrPo = '176993651a735e6ef674ca3ade26d8e1';
SET @GKrPo = '0e6af188292c13e535a1d2acf482eb1e';


CALL pr_kr_Tool('EDIT', 35, '', 0, 'krng', 'IDN', '', '');
SELECT * FROM `t_pr_krng`;
CALL pr_kr_Tool('EDIT', '{7E6D3B02-9EA8-11DE-A5B6-00065BF0376E}', '', 0, 'krng', 'GUID', '', '');
SELECT * FROM `t_pr_krng`;
CALL pr_kr_Tool('EDIT', 44, '', 0, 'krpo', 'IDN', '', '');
SELECT * FROM `t_pr_krng`;
CALL pr_kr_Tool('EDIT', '0e6af188292c13e535a1d2acf482eb1e', '', 0, 'krpo', 'GUID', '', '');


SET @GKrNg = '{7E6CB6D2-9EA8-11DE-A5B6-00065BF0376E}'; -- ten testuj
SET @GKrNg = '{89B199C6-9D2D-11DE-A5B6-00065BF0376E}'; -- 

SET @GKrPo = '176993651a735e6ef674ca3ade26d8e1';
SET @GKrPo = '0e6af188292c13e535a1d2acf482eb1e';
CALL pr_kr_Tool('DEATAL_krng', @GKrNg, '', 0, '', '', '', '');

CALL pr_kr_Tool('NALICZ_krpo', @GKrPo, '', 0, '', '', '', '');
CALL pr_kr_Tool('NALICZ_krpo', @GKrNg, '', 0, 'x', '', '', '');
CALL pr_kr_Tool('NALICZ_krng', @GKrNg, '', 0, '', '', '', '');



CALL pr_kr_Tool('FIND', '{7E6D3B02-9EA8-11DE-A5B6-00065BF0376E}', '', 0, 'krng', 'GUID', '', '');
CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, 'krng', 'NrWewn', '', '1');
CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, 'krng', 'IDN', '', '1');
CALL pr_kr_Tool('FIND', '0e6af188292c13e535a1d2acf482eb1e', '', 0, 'krpo', 'GUID', '', '');
CALL pr_kr_Tool('FIND', 44, '', 0, 'krpo', 'IDN', '', '');
CALL pr_kr_Tool('FIND', 'KRZ-25', '', 0, '*', '', '', '1');
CALL pr_kr_Tool('FIND', '25', '', 0, '*', '', '', '1');
SELECT * FROM `t_pr_kr_id`;
SELECT @_KrPoIDN, @_KrPoGUID, @_KrNgIDN, @_KrNgGUID;
		SELECT 
			KrPoIDN, KrPoGUID, KrNgIDN, KrNgGUID
		INTO 
			@_KrPoIDN, @_KrPoGUID, @_KrNgIDN, @_KrNgGUID
		FROM `t_pr_kr_id`	
		;

			DROP TEMPORARY TABLE IF EXISTS `t_edit_pr_krpo`;
			CREATE TEMPORARY TABLE IF NOT EXISTS `t_edit_pr_krpo`	
				SELECT
					_VarStr('-') AS LogType,
					_VarInt(0) AS Checked,
					_VarINT(0) AS ModifyStatus, 
					_VarDouble(0) As TimeCapsALL,
					_VarDouble(0) As TimeCapsALLTob,
					_VarDouble(1) As Waga,
					_VarDouble(kp.TimeCaps - kp.TimeDone) AS TimeToDo,
					_VarDouble(0) As TimeToDoRest,
					_VarDouble(kp.Seria - kp.SeriaDone) AS SeriaToDo,
					_VarDouble(0) As New_TimeDone,
					_VarDouble(0) As New_SeriaDone,
					_VarDouble(0) As New_TimeCaps,
					kp.AreaGUID AS New_AreaGUID, 
					kp.WorkgroupGUID AS New_WorkgroupGUID, 
					kp.*
				FROM `pr_krpo` kp	
				WHERE
					IF(@_KrPoGUID != '', kp.GUID = @_KrPoGUID, kp.KrNgGUID = @_KrNgGUID) 
			; 

			SELECT
				_VarDouble(SUM(IF(KrPoGUID != '', TimeCaps, 0))),
				_VarDouble(SUM(TimeCaps))
			INTO	
				@_TimeCapsALLTob, @_TimeCapsALL
				FROM `pr_krpo_r` kr	
				WHERE
					kr.KrNgGUID = @_KrNgGUID
			; 
			UPDATE `t_edit_pr_krpo`
			SET
				TimeCapsALLTob = ROUND(@_TimeCapsALLTob, 4),
				TimeCapsALL = ROUND(@_TimeCapsALL, 4),
				Waga = _VarDouble(ROUND(IF(TimeCapsALLTob = 0, 0, TimeCaps/TimeCapsALLTob),5)),
				TimeToDoRest = ROUND(TimeCapsALL*Waga, 4)
			;

select 	* FROM `t_edit_pr_krpo`;
select 	SUM(Waga), SUM(TimeToDoRest), TimeCapsALL, TimeCapsALLTob FROM `t_edit_pr_krpo`;
SELECT @_TimeCaps, @_TimeCapsALL;
SELECT @_KrPoIDN, @_KrPoGUID, @_KrNgIDN, @_KrNgGUID;
CALL pr_kr_Tool('ROZLICZ_krpo_INIT', 'KRZ-35', '', 0, '*', '', '-', 'Ng');
CALL pr_kr_Tool('ROZLICZ_krpo_INIT', '46', '', 0, '*', '', '-', 'Po');

CALL pr_kr_Tool('ROZLICZ_krpo_INIT', 'KRZ-35', '', 0, '*', '', '-', 'Po');




select 	idn, 
	GUID, 
	krngGUID, 
	PodzlecGUID, 
	TobGUID, 
	AreaGUID, 
	WorkgroupGUID, 
	lp_int, 
	lp_vc, 
	Rodzaj, 
	Progress, 
	TimeCaps, 
	TimeDone, 
	TimeDoneProc, 
	Seria, 
	SeriaDone, 
	Status, 
	IsOBUN, 
	Marszruta_G
	 
	from 
	mostostal.pr_krpo 
	limit 0, 94560;

			INSERT INTO `t_edit_pr_krpo`	
				SELECT
					NewGUID(),
					@_KrNgGUID,
					@_KrPoGUID,
					kp.PodzlecGUID, 
					kp.AreaGUID, 
					kp.WorkgroupGUID, 
					kp.TobGUID, 
					kp.Progress, 
					kp.TimeCaps, 
					kp.TimeDone, 
					kp.TimeDoneProc, 
					kp.Seria, 
					kp.SeriaDone, 
					kp.Status, 
					kp.Marszruta_G,
					
					_VarStr(@_LogType),	--  AS LogType,
					_VarInt(0),	--  AS Checked,
					_VarINT(0),	--  AS ModifyStatus, 
					_VarDouble(0),	--  As TimeCapsALL,
					_VarDouble(0),	--  As TimeCapsALLTob,
					_VarDouble(0),	--  As TimeDoneALL, 
					_VarDouble(0),	--  As TimeDoneALLTob, 
					_VarDouble(1),	--  As Waga,
					_VarDouble(kp.TimeCaps - kp.TimeDone),	--  AS TimeToDo,
					_VarDouble(0),	--  As TimeToDoRest,
					_VarDouble(kp.Seria - kp.SeriaDone),	--  AS SeriaToDo,
					_VarDouble(0),	--  As New_TimeDone,
					_VarDouble(0),	--  As New_SeriaDone,
					_VarDouble(0),	--  As New_TimeCaps,
					kp.AreaGUID,	--  AS New_AreaGUID, 
					kp.WorkgroupGUID	--  AS New_WorkgroupGUID 
				FROM `pr_krpo` kp	
				WHERE
						IF(@_KrPoGUID != '', kp.GUID = @_KrPoGUID, kp.KrNgGUID = @_KrNgGUID)
			; 


					/*
					kp.idn, kp.GUID,
					kp.krngGUID, kp.PodzlecGUID, kp.TobGUID,
					kp.AreaGUID, 
					kp.WorkgroupGUID, 
					kp.lp_int, 
					kp.lp_vc, 
					kp.Rodzaj, 
					kp.Progress, 
					kp.TimeCaps, 
					kp.TimeDone, 
					kp.TimeDoneProc, 
					kp.Seria, 
					kp.SeriaDone, 
					kp.Status, 
					kp.IsOBUN, 
					kp.Marszruta_G
*/

DROP PROCEDURE IF EXISTS `pr_kr_Wizard`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_kr_Wizard`(
	IN _MODE VARCHAR(50),
	IN _Check_GUID VARCHAR(100),
	IN _Check_Value INT,
	IN _Param_A VARCHAR(50),	-- np. _krng_GUID, _DataOd
	IN _Param_B VARCHAR(50),	-- np. _DataDo
	IN _Param_C VARCHAR(50),	-- np. _OsobaGUID	
	IN _Param_D VARCHAR(50)	-- np. NOT Show?
	)
	READS SQL DATA
SPROC: BEGIN
/*
	CALL pr_kr_Wizard('INIT', @Case_GUID, 0, '', '', '', '');
	CALL pr_kr_Wizard('DZIALY', @Case_GUID, 1, '', '', '', '');
	-- CALL pr_kr_Wizard(2, @Case_GUID, 0, '', '', '', '');
	CALL pr_kr_Wizard('INFO', @Case_GUID, 0, '', '', '', '');

	SEt @ID = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}IdCK0';  -- CK
	CALL pr_kr_Wizard('CHECK_KARTY', @ID, 1, '', '', '', '');
	SEt @ID = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}IdCA0'; -- CA
	CALL pr_kr_Wizard('CHECK_KARTY', @ID, 1, '', '', '', '');

	_MODE:
	
	0, 
	'INIT':	
		Kreowanie tabel tymczasowych (Start) oraz  SELECT zleceń z okresu Od - Do  (z polem check)  lub dla Zlecenia z parametru _Check_GUID o ile _Check_GUID <> ''
		Parametry
			_Param_A (_DataOd), _Param_B (_DataDo) 
			LUB 
			_Check_GUID
		CALL pr_kr_Wizard('INIT', @Case_GUID, 0, '', '', '', '');
		LUB
		CALL pr_kr_Wizard('INIT', '', 0, '2009-08-01', '2009-09-01', '', '');
	
	1, 
	'CHECK_ZLEC':	
		Wstawianie zleceń zaznaczonych (Insert jeden rekord n-razy)
		Parametry
			_Check_GUID, 		zlecenie
			_Check_Value		0/1	
		CALL pr_kr_Wizard('INFO', _Check_GUID, _Check_Value, '', '', '', '');	
		
	2, 
	'DZIALY':	
		SELECT wydzialow z top-ow/gniaz/bez KaPr dla zlecen wybranych (z polem check)  - gdy pusty select to oznacza, ze wszystkie karty juz istnieja
		Parametry
			_Check_Value 		0/1 czyli zwraca wszystkie zaznaczone lub nie
		CALL pr_kr_Wizard('DZIALY', '', 0, '', '', '', '');  zwraca wszystkie NIE zaznaczone
		CALL pr_kr_Wizard('DZIALY', '', 1, '', '', '', '');  zwraca wszystkie zaznaczone
	
	3, 
	'CHECK_DZIALY':	
		Wstawianie wydzialow zaznaczonych (Insert jeden rekord n-razy)
		Parametry
			_Check_GUID, 		wydzial
			_Check_Value		0/1	
		CALL pr_kr_Wizard('CHECK_DZIALY', _Check_GUID, _Check_Value, '', '', '', '');	
		
	4, 
	'INFO':
		Przygotowanie kart (usuwam rekordy dla wydzialow niewybranych na koniec SELECT sumaryczny
		Parametry
			BRAK
		CALL pr_kr_Wizard('INFO', '', 0, '', '', '', '');
		
	5, 
	'CHECK_KARTY':
		Wstawianie Kart zaznaczonych (Insert jeden rekord n-razy)
		Parametry
			_Check_GUID, 		Klucz z pola ID dataset-a otrzymanego z CALL pr_kr_Wizard('INFO', ...)
			_Check_Value		0/1	
			_Param_A			(NIEWYMAGANY)	jako _krng_GUID - UWAGA mozemy dac pusty bo i tak wykonamy NewGUID()
		CALL pr_kr_Wizard('CHECK_KARTY', ID, _Check_Value, '', '', '', '');
			
	6, 
	'CREATE':
		Kreowanie kart rozliczen
		Parametry
			BRAK
			_Param_C			(NIEWYMAGANY)	GUID osoby, gdy _Param_C = '' wykorzystamy @session.CurrOsobaGUID
		CALL pr_kr_Wizard('CREATE', '', 0, '', '', '', '');	
			
	7, 
	'CLEAR':
		Sprzatanie
		Parametry
			BRAK
		CALL pr_kr_Wizard('CLEAR', '', 0, '', '', '', '');	
	
	
	'ROZLICZ_krpo_INIT'
		Przygotowuje rekord (rekordy) do rozliczania +/-
		Parametry
			Parametry takie jak przy FIND
			_Check_GUID, 		GUID lub IDN karty/pozycji
			_Param_A 		'krng' - szukam po krng, 'krpo' szukam po krpo, '*' szukam po krng gdy _GUID like 'KRZ-' lub po krpo 
			_Param_B 		'IDN' - szukam po IDN, 'GUID' szukam po GUID
			_Param_C 		'+' - zmiana puli, '-' - rozliczanie
			_Param_D			'Ng' - dotyczy naglowka, 'Po' - dotyczy pozycji (1 lub wielu, a  to zalezy od tego czy _KrPoIDN <> 0)				
				
	'ROZLICZ_krpo_APPLY'
		Zapisuje rekord (rekordy) do rozliczania +/- i przelicza
		Parametry
			_Param_C			(NIEWYMAGANY)	GUID osoby, gdy _Param_C = '' wykorzystamy @session.CurrOsobaGUID
				
	_Check_Value:
	0:	niezaznaczony
	1:	zaznaczony	
*/
	DECLARE _Case_GUID VARCHAR(38);
	DECLARE _Area_GUID VARCHAR(38);
	DECLARE _Area_Symbol VARCHAR(58);
	DECLARE _Workgroup_GUID VARCHAR(38);
	DECLARE _PodZlec_GUID VARCHAR(38); 
	DECLARE _Div_GUID VARCHAR(38);
	DECLARE _Div_Symbol VARCHAR(50);
	DECLARE _S1 VARCHAR(50);
	DECLARE _S2 VARCHAR(50);
	DECLARE _NrWewn VARCHAR(38);
	DECLARE _DataOd DATE;
	DECLARE _DataDo DATE;
	DECLARE _krng_GUID VARCHAR(38);
	DECLARE _OsobaGUID VARCHAR(38);
--	DECLARE _krpo_GUID VARCHAR(38);
	DECLARE _IsOBUN INT;

	DECLARE _NewGUID VARCHAR(38);
	DECLARE _KrNgGUID VARCHAR(38) DEFAULT _Check_GUID;
	DECLARE _KrPoGUID VARCHAR(38) DEFAULT _Check_GUID;
	DECLARE _KrNgIDN INT DEFAULT _Check_GUID;
	DECLARE _KrPoIDN INT DEFAULT _Check_GUID;
	
	DECLARE _LogType VARCHAR(20) DEFAULT '';
	DECLARE _RozliczOneRec INT DEFAULT 1;
	
	SET _Check_GUID = IFNULL(_Check_GUID, '');
	SET _krng_GUID = IF(IFNULL(_Param_A, '') = '', NewGUID(), _Param_A);
	SET _DataDo = IFNULL(_Param_B, CURRENT_DATE());
	SET _DataOd = IFNULL(_Param_A, DATE_ADD(_DataDo, INTERVAL -1 MONTH));
	
	IF (_MODE = 'INIT') OR (_MODE = '0') THEN
--	Kreowanie tabel tymczasowych (Start) oraz  SELECT zleceń z okresu Od - Do  (z polem check) 
	--	Tymczasowki
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_podzlec1`;
		IF _Check_GUID = '' THEN
			CREATE TEMPORARY TABLE `t_hurt_podzlec1`
				SELECT
					z.GUID,
					NAME_CONST('Checked', '1'),		--	Domyslnie Zaznaczony
					z.`nrwewn` AS `NrWewn`,
					SUBSTR(IF(z.`Temat` != '', z.`Temat`, `cr`.`nazwa`), 1, 250) AS `Temat`,
					_VarStr(z.StartTime) AS StartTime,
					_VarStr(IF(z.`Status` = 7, z.DeadLine, 'Otwarte')) AS CloseTime,
					z.Opis,
					z.`case_guid` AS `Case_GUID`
				FROM crm_commisions z
				LEFT JOIN `crm_rodzaje` `cr` ON `cr`.`rodzaj` = `z`.`rodzaj` AND `cr`.`podrodzaj` = `z`.`podrodzaj`
				WHERE
					(z.`rodzaj` = 12 AND z.`podrodzaj` = 1) AND
					z.StartTime < _DataDo AND z.DeadLine > _DataOd
				-- GROUP BY `C`.`GUID`
			;
		ELSE
			CREATE TEMPORARY TABLE `t_hurt_podzlec1`
				SELECT
					z.GUID,
					NAME_CONST('Checked', '1'),		--	Domyslnie Zaznaczony
					z.`nrwewn` AS `NrWewn`,
					SUBSTR(IF(z.`Temat` != '', z.`Temat`, `cr`.`nazwa`), 1, 250) AS `Temat`,
					_VarStr(z.StartTime) AS StartTime,
					_VarStr(IF(z.`Status` = 7, z.DeadLine, 'Otwarte')) AS CloseTime,
					z.Opis,
					z.`case_guid` AS `Case_GUID`
				FROM crm_commisions z
				LEFT JOIN `crm_rodzaje` `cr` ON `cr`.`rodzaj` = `z`.`rodzaj` AND `cr`.`podrodzaj` = `z`.`podrodzaj`
				WHERE
					z.GUID = _Check_GUID
				;
		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_top`;
		CREATE TEMPORARY TABLE `t_hurt_top`
			SELECT
				z.GUID AS Case_GUID,
				tob.Def_Obj,
				tob.Ilosc_Seria,
				tob.nr_zmiany AS tob_nr_zmiany,
				tob.Asortyment,
				tob.status_top,
				tob.Marszruta,
				((_VarStr_NotNull(top.pr_krpo_GUID) = '') AND (_VarStr_NotNull(top.commision_guid) = '')) AS IsValid, 
				IF(tob.Def_Obj = -4, 0, tob.status_top != 1) AS IsLock,
				top.*
			FROM `t_hurt_podzlec1` z
			INNER JOIN pr_tob tob ON `tob`.`Case_GUID` = `z`.`GUID` -- AND IF(_IsOBUN != 0, `tob`.`Def_Obj` = -4, `tob`.`Def_Obj` != -4)
			-- `tob`.`Def_Obj` = -4
			INNER JOIN `zmpo` top ON top.TobGUID = `tob`.`GUID` -- AND (IFNULL(top.pr_krpo_GUID, '') = '')
		;

		DROP TEMPORARY TABLE IF EXISTS `t_hurt_podzlec`;
		CREATE TEMPORARY TABLE `t_hurt_podzlec`
			SELECT
				z.*, _VarINT(COUNT(*)) AS IleOp
			FROM `t_hurt_podzlec1` z
			INNER JOIN `t_hurt_top` top ON top.Case_GUID = `z`.`GUID` AND top.IsValid
			GROUP BY `z`.`GUID`
		;
	--	SELECT gotowej tabeli (do zaznaczenia)	
		IF _Param_D = '' THEN
			SELECT
				z.*
			FROM `t_hurt_podzlec` z
			ORDER BY z.`NrWewn`
			;
		END IF;
	END IF;	-- (_MODE = 0) OR (_MODE = 'INIT')
	
	IF (_MODE = 1) OR (_MODE = 'CHECK_ZLEC') THEN
--	Wstawianie zleceń zaznaczonych (Insert jeden rekord n-razy)
		UPDATE `t_hurt_podzlec` z
			SET Checked = _Check_Value
		WHERE
			z.GUID = _Check_GUID
		;
	END IF;	-- (_MODE = 1) OR (_MODE = 'CHECK_ZLEC')
	
	IF (_MODE = 'DZIALY') OR (_MODE = 2) THEN
--	SELECT wydzialow z top-ow/gniaz/bez KaPr dla zlecen wybranych (z polem check)  - gdy pusty select to oznacza, ze wszystkie karty juz istnieja
	--	Przygotuj zestaw gniaz dla wybranych zlecen (checked) i IS_OBUN dla ktorych nie ma jeszcze kart pracy 
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_gniazda_dla_zazn_zlecen`;
		CREATE TEMPORARY TABLE `t_hurt_gniazda_dla_zazn_zlecen`
			SELECT
				z.GUID AS Case_GUID,
				top.AreaGUID, 
				n.Dzial,
				NAME_CONST('Checked', '0')		--	wydzial tego gniazda zaznaczono? Domyslnie NIEzaznaczony
			FROM `t_hurt_podzlec` z	
			INNER JOIN `t_hurt_top` top ON top.`Case_GUID` = `z`.`GUID` AND top.IsValid
			INNER JOIN erp_area_nodes n ON n.GUID = top.AreaGUID
			WHERE 
				z.Checked != 0 AND 
				IFNULL(top.AreaGUID, '') != ''
			GROUP BY top.AreaGUID
		;
	--	Selekcja gniazd z opisem operacji na gniazdach	
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_Gniazda_z_TOP`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `t_hurt_Gniazda_z_TOP`
			SELECT
				d.GUID AS Wydzial_GUID,
				d.Symbol AS Wydzial,
				d.Nazwa AS WydzialNazwa,
				top.AreaGUID AS AreaGUID,
				n.Symbol AS Gniazdo,
				GROUP_CONCAT(DISTINCT top.towrsymb ORDER BY top.towrsymb SEPARATOR '-') AS Operacje,
				COUNT(DISTINCT top.GUID) AS Ile_Op,
				SUM(top.IsValid) AS Ile_Op_BezKart,
				-- bylo SUM(_VarStr_NotNull(top.pr_krpo_GUID) = '') AS Ile_Op_BezKart,
				SUM(IF(top.IsValid AND IsLock, 1, 0)) AS Ile_Op_BezKart_StatusLock,
				-- bylo SUM(IF(_VarStr_NotNull(top.pr_krpo_GUID) = '' AND top.status_top != 1, 1, 0)) AS Ile_Op_BezKart_StatusLock,
				SUM(NOT top.IsValid) AS Ile_Op_ZKart
				-- bylo SUM(_VarStr_NotNull(top.pr_krpo_GUID) != '') AS Ile_Op_ZKart
			FROM t_hurt_gniazda_dla_zazn_zlecen g
			INNER JOIN `t_hurt_top` top ON top.AreaGUID = g.AreaGUID AND top.`Case_GUID` = g.Case_GUID
			INNER JOIN erp_area_nodes n ON n.GUID = g.AreaGUID
			INNER JOIN erp_division_schema d ON d.GUID = n.Dzial
			GROUP BY n.GUID
			ORDER BY Gniazdo
		;
			
	--	Przygotuj zestaw wydzialow
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_wydzialy_gniaz_bez_kart`;
		CREATE TEMPORARY TABLE `t_hurt_wydzialy_gniaz_bez_kart`
		    SELECT
				g.Wydzial_GUID AS GUID,
				-- NAME_CONST('Checked', '0'),		--	Domyslnie NIEzaznaczony/Zaznaczony?
				_VarINT(_Check_Value) AS Checked,		--	Domyslnie NIEzaznaczony/Zaznaczony?
				g.Wydzial_GUID AS Wydzial_GUID,
				g.Wydzial AS Wydzial,
				g.WydzialNazwa AS WydzialNazwa,
				IFNULL(GROUP_CONCAT(DISTINCT IF(Ile_Op_BezKart > 0,g.Gniazdo, NULL) ORDER BY g.Gniazdo SEPARATOR '-'), '') AS Gniazda,
				IFNULL(GROUP_CONCAT(DISTINCT IF(Ile_Op_ZKart > 0,g.Gniazdo, NULL) ORDER BY g.Gniazdo SEPARATOR '-'), '') AS GniazdaKarty,
				IFNULL(GROUP_CONCAT(DISTINCT IF(Ile_Op_BezKart_StatusLock > 0, g.Gniazdo, NULL) ORDER BY g.Gniazdo SEPARATOR '-'), '') AS GniazdaStatusLock,
				COUNT(DISTINCT g.Gniazdo) AS Ile_Gniazd,
				IFNULL(GROUP_CONCAT(DISTINCT IF(Ile_Op_BezKart > 0,g.Operacje, NULL) ORDER BY g.Operacje SEPARATOR '-'), '') AS Operacje,
				IFNULL(GROUP_CONCAT(DISTINCT IF(Ile_Op_ZKart > 0,g.Operacje, NULL) ORDER BY g.Operacje SEPARATOR '-'), '') AS OperacjeKarty,
				IFNULL(GROUP_CONCAT(DISTINCT IF(Ile_Op_BezKart_StatusLock > 0,g.Operacje, NULL) ORDER BY g.Operacje SEPARATOR '-'), '') AS OperacjeStatusLock,
				SUM(g.Ile_Op) AS Ile_Op,
				SUM(g.Ile_Op_BezKart) AS Ile_Op_BezKart,
				SUM(g.Ile_Op_ZKart) AS Ile_Op_ZKart,
				SUM(g.Ile_Op_BezKart_StatusLock) AS Ile_Op_BezKart_StatusLock
			FROM `t_hurt_Gniazda_z_TOP` g
			GROUP BY GUID
			HAVING Ile_Op_BezKart > 0 OR TRUE
			ORDER BY g.Wydzial
			;
			
	--	SELECT	wydzialow	
		IF _Param_D = '' THEN
			SELECT 
				d.*, _VarINT(((d.Ile_Op - d.Ile_Op_BezKart_StatusLock) <= 0)) AS IsDisabled  
			FROM `t_hurt_wydzialy_gniaz_bez_kart` d
			ORDER BY Wydzial -- d.Symbol
			;
		END IF;
	END IF;	-- (_MODE = 2) OR (_MODE = 'DZIALY')
	
	IF (_MODE = 3) OR (_MODE = 'CHECK_DZIALY') THEN
--	Wstawianie wydzialow zaznaczonych (Insert jeden rekord n-razy)
		UPDATE `t_hurt_wydzialy_gniaz_bez_kart` d
			SET Checked = _Check_Value
		WHERE
			d.GUID = _Check_GUID
		;
		UPDATE `t_hurt_gniazda_dla_zazn_zlecen` g
			SET g.Checked = (SELECT d.Checked FROM `t_hurt_wydzialy_gniaz_bez_kart` d WHERE g.Dzial = d.GUID )
		WHERE g.Dzial = _Check_GUID	
		;
	END IF;	-- (_MODE = 3) OR (_MODE = 'CHECK_DZIALY')
	
	IF (_MODE = 4) OR (_MODE = 'INFO') THEN
--	Przygotowanie kart (usuwam rekordy dla wydzialow niewybranych na koniec SELECT sumaryczny
	--	Przygotuj liste gniaz - checked z wydzialow odznacz w gniazdach tych wydzialow (te niezaznaczone beda ignorowane)
		UPDATE `t_hurt_gniazda_dla_zazn_zlecen` g
			SET g.Checked = (SELECT d.Checked FROM `t_hurt_wydzialy_gniaz_bez_kart` d WHERE g.Dzial = d.GUID)
		;
	--	Kreuj `temp_karty_pracy` ale nie wypelniaj w CALL	
		--	kiedys bylo CALL pr_KartyPracy_CreateTemp_karty_pracy('');
		DROP TEMPORARY TABLE IF EXISTS `temp_karty_pracy`;
		CREATE TEMPORARY TABLE `temp_karty_pracy`(
			`GUID` VARCHAR(38) NOT NULL DEFAULT '',		-- Klucz glowny z tabeli zmpo
			`Ilosc` DOUBLE (16,4) DEFAULT 0,			-- Ilosc z zmpo
			`Ilosc_Seria` DOUBLE (16,4) DEFAULT 0,		-- Ilosc_Seria z tob
			`pr_ilosc_seria` DOUBLE (16,4) DEFAULT 0,	-- pr_ilosc_seria z zmpo
			`cenanet` DOUBLE (16,4) DEFAULT 0,			-- cenanet z zmpo
			`wartnet` DOUBLE (16,4) DEFAULT 0,			-- wartnet z zmpo
			`rcpoGUID` VARCHAR(38) DEFAULT NULL,		-- Pole do listy operacji, stawek etc.
			`pr_krng_GUID` VARCHAR(38) DEFAULT NULL,	-- Pole karty rozliczen
			`pr_krpo_GUID` VARCHAR(38) DEFAULT NULL,	-- Pole do sprawdzenia czy juz podpieta jest karta rozliczen
			`tobGUID` VARCHAR(38) DEFAULT NULL,
			`case_GUID` VARCHAR(38) DEFAULT NULL,
			`SymbAsortyment` VARCHAR(50) DEFAULT '',	-- Symbol asortymentu
			`SymbZmpo` VARCHAR(38) DEFAULT '',			-- Symbol z pozycji zamowien
			`Symbol` VARCHAR(150) DEFAULT '',			-- Symbol polaczenie symbolu z zamowien z symbolm asortymentu
			`Res_type` VARCHAR(20) DEFAULT '',			-- Typ zasobu
			`areaGUID` VARCHAR(38) DEFAULT NULL,
			`dzial` VARCHAR(38) DEFAULT NULL,
			`wydzial` VARCHAR(38) DEFAULT NULL,
			`Symboldzialu` VARCHAR(150) DEFAULT NULL,
			`SymbolMiejsca` VARCHAR(150) DEFAULT NULL,
			`Asortyment` VARCHAR(38) DEFAULT NULL,
			`status_top` INT DEFAULT 0,
			`EnabledAsortyment` INT DEFAULT 0,
			`IsOBUN` INT DEFAULT 0,
			`extra_nr_karty` VARCHAR(50) DEFAULT NULL,
			`Nr_Zlec` VARCHAR(100) DEFAULT NULL,
			`Marszruta` VARCHAR(255) DEFAULT NULL,
			PRIMARY KEY (`GUID`),
			KEY `case_GUID` (`case_GUID`)
		);
	
	--	Uzupelnij `temp_karty_pracy` 	
		INSERT INTO `temp_karty_pracy`
		SELECT
			top.GUID,
			top.ilosc,
			top.Ilosc_Seria,
			top.pr_ilosc_seria,
			top.cenanet,
			top.wartnet,
			top.rcpoGUID,
			top.pr_krpo_GUID,								-- na razie jako pr_krng_GUID 
			top.pr_krpo_GUID,
			top.tobGUID,
			top.case_GUID,
			a.symbol AS symbAsortyment,
			top.towrsymb AS symbZmpo,
			CONCAT(IFNULL(top.towrsymb, ''), ' ', IFNULL(a.symbol, '')) AS symbol,
			top.Res_type,
			top.areaGUID,
			n.dzial,
			vd1.GUID_wydzialu,
			d.symbol AS Symboldzialu,
			n.symbol AS SymbolMiejsca,
			top.Asortyment,
			top.status,		--	top.status_top,
			_VarINT(0),
			_VarINT(IF(top.Def_Obj = -4, 1, 0)),
			_VarSTR(IF(top.Def_Obj = -4, top.tob_nr_zmiany, '')),
			z.NrWewn,
			top.Marszruta
		FROM `t_hurt_podzlec` z	
		INNER JOIN `t_hurt_top` top ON top.`Case_GUID` = `z`.`GUID` AND top.IsValid AND top.IsLock = 0
		INNER JOIN `t_hurt_gniazda_dla_zazn_zlecen` g ON g.AreaGUID = top.areaGUID AND g.Checked != 0 	
		LEFT JOIN `erp_asort_types` a ON a.guid = top.Asortyment
		LEFT JOIN `erp_area_nodes` n ON n.guid = top.areaGUID
		LEFT JOIN v_pr_division vd1 on vd1.GUID = n.dzial
		LEFT JOIN `erp_division_schema` d ON d.guid = vd1.GUID_Wydzialu
		WHERE
			z.Checked != 0 -- AND 
		--	(top.status_top = 1 OR top.Def_Obj = -4) AND
		-- 	(top.Status = 1 OR top.Def_Obj = -4)
		GROUP BY top.GUID	
		;
		UPDATE `temp_karty_pracy` tkp, `rcpo` r
		SET
			tkp.cenanet = r.cena_kalulacyjna,
			tkp.wartnet = r.cena_kalulacyjna * tkp.ilosc
		WHERE
			tkp.rcpoGUID = r.GUID
		;
		
	--	SELECT	zagregowanych kart	
		SELECT DISTINCT
			kp.`Nr_Zlec` As NrPodzelc, z.`Temat`,
			-- kp.Symbol,
			kp.Symboldzialu As Wydzial,
			kp.SymbolMiejsca As Miejsce,
			_varDOUBLE(ROUND(SUM(kp.Ilosc),2)) AS Pula_Karty,
			_varDOUBLE(ROUND(SUM(kp.wartnet),2)) AS Wartosc_Karty,
			kp.IsOBUN,
			_varINT(COUNT(*)) AS IleOperacji,
			GROUP_CONCAT(kp.symbZmpo, '') As Operacje,
			kp.areaGUID,
			kp.pr_krng_GUID,
			kp.pr_krpo_GUID,
			kp.extra_nr_karty,
			NAME_CONST('Checked', '1'),
			-- Bylo _VarStr(CONCAT(kp.case_GUID, kp.`areaGUID`, `IsOBUN`)) As ID
			_VarStr(CONCAT(kp.case_GUID, kp.`areaGUID`)) AS ID
		FROM `temp_karty_pracy` kp
		LEFT JOIN `t_hurt_podzlec` z ON z.GUID = kp.case_GUID	
		WHERE
			IFNULL(kp.pr_krpo_GUID, '') = '' AND
			kp.Res_type = 'W'
			# AND kp.EnabledAsortyment
		GROUP BY kp.`case_GUID`, kp.`areaGUID`	-- NIE!!!!, kp.`IsOBUN`
		ORDER BY NrPodzelc, Wydzial, Miejsce
		;
	END IF;	-- (_MODE = 4) OR (_MODE = 'INFO')
	
	IF (_MODE = 5) OR (_MODE = 'CHECK_KARTY') THEN
--	Wstawianie Kart zaznaczonych (Insert jeden rekord n-razy)
	--	_krng_GUID gdy pusty to wstaw wlasny GUID
	--	wstaw przyszle GUIDy Kapr do tabeli `temp_karty_pracy` wedle agregatu karty czyli case_GUID, areaGUID, IsOBUN
		UPDATE `temp_karty_pracy` kp
		SET 
			kp.pr_krng_GUID = IF(_Check_Value != 0, _krng_GUID, '')
		WHERE
			-- Bylo CONCAT(kp.case_GUID, kp.`areaGUID`, `IsOBUN`) = _Check_GUID
			CONCAT(kp.case_GUID, kp.`areaGUID`) = _Check_GUID
		;
		UPDATE `temp_karty_pracy` kp
		SET 
			-- Bylo kp.pr_krpo_GUID = IF(_Check_Value != 0, Md5(CONCAT(_krng_GUID, kp.case_GUID, kp.`areaGUID`, kp.`TobGUID`, `IsOBUN`)), '')
			kp.pr_krpo_GUID = IF(_Check_Value != 0, Md5(CONCAT(_krng_GUID, kp.case_GUID, kp.`areaGUID`, kp.`TobGUID`)), '')
		WHERE
			-- Bylo CONCAT(kp.case_GUID, kp.`areaGUID`, `IsOBUN`) = _Check_GUID
			CONCAT(kp.case_GUID, kp.`areaGUID`) = _Check_GUID
		;
		
	END IF;	-- (_MODE = 5) OR (_MODE = 'CHECK_KARTY')
	
	IF (_MODE = 6) OR (_MODE = 'CREATE') THEN
--	Kreowanie kart
	-- 	Wstaw do zmpo GUIDy kart (do pola `pr_krpo_GUID`)	UWAGA tylko niepuste GUIDY do topow majacych puste pole `pr_krpo_GUID`
		/* NA RAZIE
		UPDATE `zmpo` z, `temp_karty_pracy` tkp, `rcpo` r
		SET
			z.pr_krpo_GUID = tkp.pr_krpo_GUID,
			z.cenanet = r.cena_kalulacyjna,
			z.wartnet = r.cena_kalulacyjna * z.ilosc
		WHERE
			tkp.GUID = z.GUID AND	
			IFNULL(tkp.pr_krpo_GUID, '') != '' AND	
			IFNULL(z.pr_krpo_GUID, '') = '' AND
			z.rcpoGUID = r.GUID
		;
		*/
		-- ODBLOKOWUJE	LEAVE SPROC;	-- Na razie (23-09-2009) w Mostostalu nie kreujemy do 5-10-2009
		
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_KaPr`;
		CREATE TEMPORARY TABLE `t_hurt_KaPr`
			SELECT 
				_VarINT(0) AS `IsCreated`, 
				kp.`pr_krng_GUID`, 
				-- kp.`pr_krpo_GUID`, 
				kp.`Case_GUID`, kp.`areaGUID`, kp.`IsOBUN`, 
				_ConcatSTR(kp.`Nr_Zlec`, _ConcatSTR(kp.SymbolMiejsca, IF(kp.`IsOBUN` != 0, 'OBUN', ''), ' - '), ' - ') As NrWewn,
				d.GUID_Wydzialu AS Div_GUID,
				d.Symbol AS Div_Symbol,
				n.Symbol AS Area_Symbol
			FROM `temp_karty_pracy` kp
			LEFT JOIN `erp_area_nodes` n ON n.GUID = kp.`AreaGUID`
			LEFT JOIN `v_pr_division` d ON d.GUID = n.Dzial
			WHERE 
				IFNULL(kp.`pr_krng_GUID`, '') != ''
			GROUP BY kp.`pr_krng_GUID`	
		;
		
	--	Petla z GUID-ami KaPr - w petli dla kazdego GUIDa wolamy kreowanie czyli CALL pr_kr_Wizard(100, ...
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _LOCAL_krng_GUID VARCHAR(38);
			DECLARE c_proc CURSOR FOR
				SELECT 
					kp.`pr_krng_GUID`
				FROM `t_hurt_KaPr` kp
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loop1: LOOP
				FETCH c_proc INTO _LOCAL_krng_GUID;
				IF done THEN
					LEAVE loop1;
				END IF;
				CALL pr_kr_Wizard('CREATE_ONE', '', 0, _LOCAL_krng_GUID, '', _Param_C, '');
			END LOOP;
			CLOSE c_proc;
		END;
		
	END IF;	-- (_MODE = 6) OR (_MODE = 'CREATE')
	
	IF (_MODE = 100) OR (_MODE = 'CREATE_ONE') THEN
--	Atomiczne Kreowanie karty
	--	Ustaw zmienne		
	--	Ustaw _Case_GUID, _Area_GUID, _IsOBUN, _NrWewn, _Div_GUID 
		-- ODBLOKOWUJE	LEAVE SPROC;	-- Na razie (23-09-2009) w Mostostalu nie kreujemy do 5-10-2009
	
		SELECT
			`Case_GUID`, `areaGUID`, `IsOBUN`, `NrWewn`, `Div_GUID`, `Div_Symbol`, `Area_Symbol` 
		INTO
			_Case_GUID, _Area_GUID, _IsOBUN, _NrWewn, _Div_GUID, _Div_Symbol, _Area_Symbol 
		FROM `t_hurt_KaPr` tkp
		WHERE
			pr_krng_GUID = _krng_GUID
		LIMIT 1
		;
		SET _Div_Symbol = IFNULL(_Div_Symbol, '');
		SET _Area_Symbol = IFNULL(_Area_Symbol, ''); 
	--	Ustaw _Workgroup_GUID
		SELECT
			GUID 
		INTO
			_Workgroup_GUID 
		FROM `erp_workgroups` wg
		WHERE
			wg.areaGUID = _Area_GUID
		LIMIT 1
		;
		SET _Workgroup_GUID = IFNULL(_Workgroup_GUID, '');
		
	--	Kreuj zdarzenie karta rozliczen oraz uzupelnij gniazdo w KaPr			
		INSERT INTO `pr_krng`
			(GUID, PodzlecGUID, AreaGUID, WorkgroupGUID, Opis)
			VALUES
			(_krng_GUID, _Case_GUID, _Area_GUID, _Workgroup_GUID, CONCAT('Karta rozliczeń: ', _NrWewn, ' GN: ', _Area_Symbol))
		;
		UPDATE `pr_krng`
		SET
			NrWewn = CONCAT('KRZ-', IDN),
			DataWyst = aLocalTime
		WHERE
			GUID = _krng_GUID
		;
-- INNER JOIN `t_hurt_top` top ON top.`Case_GUID` = `z`.`GUID` AND top.IsValid
		DROP TEMPORARY TABLE IF EXISTS `t_kr_po`;
		CREATE TEMPORARY TABLE `t_kr_po`
			SELECT 
				-- _VarINT(0) AS `IsCreated`, 
				_VarStr(top.pr_krpo_GUID) AS GUID_kr_po, 
				_VarINT(COUNT(*)) AS Top_C,
				_VarDouble(SUM(top.Ilosc_Seria)) AS Tob_Seria,
				_VarDouble(SUM(top.pr_ilosc_seria)) AS Top_Seria,
				_VarDouble(SUM(top.Ilosc)) AS Top_h,
				top.Marszruta AS Marszruta_G,
				_VarDouble(SUM(top.wartnet)) AS Wartosc,
				top.TobGUID,
				_ConcatSTR_NotEqu(def.Nazwa, _ConcatSTR_NotEqu(tob.Symbol, tob.Nazwa, ' - '), ': ') AS Tob_SymbolNazwa,
				tob.ParentGUID AS Rysunek_GUID,
				tob.ParentGUID AS Element_GUID,
				_VarStr('???????????????????????????????????????????') AS Rys_SymbolNazwa,
				_VarStr('???????????????????????????????????????????') AS Elem_SymbolNazwa,
				tob.Ilosc_J AS Ilosc_J,
				top.IsOBUN,
				top.extra_nr_karty
			FROM `temp_karty_pracy` top
			INNER JOIN `pr_tob` tob ON tob.GUID = top.TobGUID
			LEFT JOIN `pr_techn_def_obj` def ON def.IDN = tob.Def_Obj
			WHERE 
				top.pr_krng_GUID = _krng_GUID
				-- Bylo top.Case_GUID = _Case_GUID
				-- Bylo AND top.AreaGUID = _Area_GUID
			GROUP BY top.TobGUID	
		;
	-- 	Wstaw GUIDy rysunku i elementu
		UPDATE `t_kr_po`
		SET
			`Rys_SymbolNazwa` = '',
			`Elem_SymbolNazwa` = '',
			`Rysunek_GUID` = (@Rysunek_GUID := pr_GetPersistantTOB(`TobGUID`, 1, FALSE)),
			`Element_GUID` = (@Element_GUID := pr_GetPersistantTOB(`TobGUID`, 2, FALSE))
		;
	-- 	Uzupelnij dane rysunku
		UPDATE `t_kr_po` t, `pr_tob` tob
		SET
			t.Rys_SymbolNazwa = _ConcatSTR_NotEqu(tob.Symbol, tob.Nazwa, ' - ')
		WHERE
			t.Rysunek_GUID = tob.GUID
		;
	-- 	Uzupelnij dane elementu
		UPDATE `t_kr_po` t, `pr_tob` tob
		SET
			t.Elem_SymbolNazwa = _ConcatSTR_NotEqu(tob.Symbol, tob.Nazwa, ' - ')
		WHERE
			t.Element_GUID = tob.GUID
		;
		
		UPDATE `t_kr_po` krpo
		SET
			Marszruta_G = (
				SELECT
					_VarStr(GROUP_CONCAT(DISTINCT g.Symbol ORDER BY top.lp_vc SEPARATOR ' > '))
				FROM zmpo top
				LEFT JOIN `erp_area_nodes` g ON g.GUID = top.AreaGUID
				WHERE
					top.TobGUID = krpo.TobGUID
			)
		;
		IF _Area_Symbol != '' THEN
			SET _S1 = CONCAT('> ', _Area_Symbol);			--	np. '> CG'   dla gniazda IdCG
			SET _S2 = CONCAT('> |', _Area_Symbol, '|');		--	np. '> |CG|' dla gniazda IdCG
			UPDATE `t_kr_po` krpo
			SET
				Marszruta_G = _VarStr(IF(IFNULL(Marszruta_G, '') = '', '', CONCAT('> ', Marszruta_G))),
				Marszruta_G = _VarStr(Replace(Marszruta_G, _S1, _S2))
			;
		END IF;
		
	--	Kreuj pozycje karty rozliczen `pr_krpo`
		INSERT INTO `pr_krpo`
			(GUID,krngGUID,PodzlecGUID,TobGUID,AreaGUID,WorkgroupGUID,lp_int,lp_vc,Rodzaj,Progress,TimeCaps,Seria,IsOBUN,-- uwagi,
				Marszruta_G,Wartosc,
				Tob_SymbolNazwa,
				Rys_SymbolNazwa,
				Elem_SymbolNazwa,
				Masa_J
				)
			SELECT
				GUID_kr_po,
				_krng_GUID,	
				_Case_GUID, 				
				TobGUID,
				_Area_GUID, 
				_Workgroup_GUID, 
				0, '0',
				1,
				0,
				Top_h,
				Tob_Seria,
				IsOBUN,
				-- extra_nr_karty,
				Marszruta_G,
				Wartosc,
				Tob_SymbolNazwa,
				Rys_SymbolNazwa,
				Elem_SymbolNazwa,
				Ilosc_J
			FROM t_kr_po
		;
	-- 	Wstaw do zmpo (operacje czyli TOP-y) pr_krpo_GUID  bedacymi guidami pozycji kart realizacji
		UPDATE `zmpo` z, `temp_karty_pracy` top
		SET
			z.pr_krpo_GUID = top.pr_krpo_GUID
		WHERE
			z.GUID = top.GUID
		;

		
	--	Kreuj pozycje karty rozliczen `pr_krpo_r`
		SET _OsobaGUID = IFNULL(_Param_C, '');
		IF _OsobaGUID = '' THEN
			SET _OsobaGUID = @session.CurrOsobaGUID;
		END IF;
	--	Wstawianie	
		INSERT INTO `pr_krpo_r`
			(GUID,PodzlecGUID,krngGUID,krpoGUID,AreaGUID,WorkgroupGUID,LogType,Rodzaj,Progress,TimeCaps,TimeDone,Seria,SeriaDone,IsOBUN,OsobaGUID)
			SELECT
				NewGUID(),
				_Case_GUID, 				
				_krng_GUID,	
				GUID_kr_po,
				_Area_GUID, 
				_Workgroup_GUID, 
				'+',
				0, 0,
				Top_h, 0,
				Tob_Seria, 0,
				_IsOBUN, 
				_OsobaGUID
			FROM t_kr_po
		;
		UPDATE `pr_krpo_r`
		SET
			DateDone = aLocalTime
		WHERE
			krngGUID = _krng_GUID
		;
		
	--	Przelicz w KRz - Ustala ogolny progress karty `pr_krng` z wszystkich `pr_krpo` - parametrem jest _GUID (GUID z `pr_krng`)
		CALL pr_kr_Tool('NALICZ_krng', _krng_GUID, '', 0, '', '', '', '');		
/*		
	--	Przelicz w KaPr dodaj LOG do KaPr 
		CALL pr_KartaPracy_Log_Resume(_krng_GUID, FALSE);
		
		UPDATE `t_hurt_KaPr` kp
			SET kp.`IsCreated` = 1
		WHERE	
			kp.`pr_krpo_GUID` = _krpo_GUID
		;	
*/		
	END IF;	-- (_MODE = 100) OR (_MODE = 'CREATE_ONE') 
	
	IF (_MODE = 7) OR (_MODE = 'CLEAR') THEN
--	Sprzatanie
	--	Wyczysc ewentualne sieroty w zmpo (podczepione do zmpo KaPr, ktore nie istnieja 
/*	NA RAZIE 		
		UPDATE zmpo top, `t_hurt_KaPr` kp
			SET top.pr_krpo_GUID = '' 
		WHERE 
			kp.pr_krpo_GUID = top.pr_krpo_GUID 
			AND kp.IsCreated = 0 
		;
*/		
	--	Zwracaj karty ale tylko zalozone !!!!!!!
		SELECT * 
		FROM `t_hurt_KaPr` kp
		WHERE
			kp.`IsCreated` = 1
		;
	--	Drop tabeli 
		DROP TEMPORARY TABLE IF EXISTS `t_hurt_KaPr`;
		
	END IF;	-- (_MODE = 7) OR (_MODE = 'CLEAR') 

	IF _MODE = 'ROZLICZ_krpo_INIT' THEN
	--	Przygotowuje rekord (rekordy) do rozliczania +/-
	--	Parametry takie jak przy FIND
	--	_Param_C 		'+' - zmiana puli, '-' - rozliczanie
	--	_Param_D			'Ng' - dotyczy naglowka, 'Po' - dotyczy pozycji (1 lub wielu, ato zalezy od tego czy _KrPoIDN <> 0)
		SET _LogType = IFNULL(_Param_C, '-');
		SET _KrNgGUID = '';
		SET _KrNgIDN = 0;
		SET _KrPoGUID = '';
		SET _KrPoIDN = 0;
	-- Szukaj
		CALL pr_kr_Tool('FIND', _Check_GUID, '', _Check_Value, _Param_A,	_Param_B, '', '');
		SELECT 
			KrPoIDN, KrPoGUID, KrNgIDN, KrNgGUID
		INTO 
			_KrPoIDN, _KrPoGUID, _KrNgIDN, _KrNgGUID
		FROM `t_pr_kr_id`	
		;
		
		-- SET _RozliczOneRec = IF((_LogType = '-') AND (_KrPoIDN != 0), 1, 
		DROP TEMPORARY TABLE IF EXISTS `t_edit_pr_krpo_r_header`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `t_edit_pr_krpo_r_header`	
			SELECT
				_VarInt(_KrPoIDN) AS KrPoIDN, 
				_VarStr(_KrPoGUID) AS KrPoGUID, 
				_VarInt(_KrNgIDN) AS KrNgIDN, 
				_VarStr(_KrNgGUID) AS KrNgGUID,
				_VarStr(_LogType) AS LogType
		; 

		IF (_LogType = '-') AND (_KrPoIDN != 0) THEN
		--	Rozliczanie 
			IF _KrPoGUID != '' THEN
				CALL pr_kr_Tool('NALICZ_krpo', _KrPoGUID, '', 0, '', '', '', '');
			ELSE
				CALL pr_kr_Tool('NALICZ_krpo', _KrNgGUID, '', 0, 'x', '', '', '');
			END IF;
		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS `t_edit_pr_krpo_r`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `t_edit_pr_krpo_r`(
			`ID` VARCHAR(38) NOT NULL DEFAULT '',    	-- Klucz glowny z tabeli `t_edit_pr_krpo_r`
			`KrNgGUID` VARCHAR(38) NOT NULL DEFAULT '',
			`KrPoGUID` VARCHAR(38) NOT NULL DEFAULT '',
			`PodzlecGUID` VARCHAR(38) NOT NULL DEFAULT '', 
			`AreaGUID` VARCHAR(38) NOT NULL DEFAULT '', 
			`WorkgroupGUID` VARCHAR(38) NOT NULL DEFAULT '', 
			`kp_TobGUID` VARCHAR(38) NOT NULL DEFAULT '', 
			`kp_Progress` DOUBLE (16,4) DEFAULT 0, 
			`kp_Progress100` DOUBLE (16,4) DEFAULT 0, 
			`DateDone` DATE,
			`kp_TimeCaps` DOUBLE (16,4) DEFAULT 0, 
			`kp_TimeDone` DOUBLE (16,4) DEFAULT 0, 
			`kp_TimeDoneProc` DOUBLE (16,4) DEFAULT 0, 
			`kp_Seria` DOUBLE (16,4) DEFAULT 0, 
			`kp_SeriaDone` DOUBLE (16,4) DEFAULT 0, 
			`kp_Status` INT DEFAULT 0, 
			`kp_StatusStr` VARCHAR(50) NOT NULL DEFAULT '', 
			`kp_Marszruta_G` VARCHAR(255) NOT NULL DEFAULT '',
			`kp_Tob_SymbolNazwa` VARCHAR(255) NOT NULL DEFAULT '',
			`kp_Rys_SymbolNazwa` VARCHAR(255) NOT NULL DEFAULT '',
			`kp_Elem_SymbolNazwa` VARCHAR(255) NOT NULL DEFAULT '',			
			`AsortymentNazwa` VARCHAR(200) NOT NULL DEFAULT '',		
			`gat_stali` VARCHAR(200) NOT NULL DEFAULT '',		
			`atest` VARCHAR(200) NOT NULL DEFAULT '',		
			`LogType` VARCHAR(20) NOT NULL DEFAULT '-',
			`Checked` INT DEFAULT 0, 
			`ModifyStatus` INT DEFAULT 0, 
			`TimeCapsALL` DOUBLE (16,4) DEFAULT 0, 
			`TimeCapsALLTob` DOUBLE (16,4) DEFAULT 0, 
			`TimeDoneALL` DOUBLE (16,4) DEFAULT 0, 
			`TimeDoneALLTob` DOUBLE (16,4) DEFAULT 0, 
			`Waga` DOUBLE (16,4) DEFAULT 0, 
			`TimeToDo` DOUBLE (16,4) DEFAULT 0, 
			`TimeToDoRest` DOUBLE (16,4) DEFAULT 0, 
			`SeriaToDo` DOUBLE (16,4) DEFAULT 0, 
			`New_TimeDone` DOUBLE (16,4) DEFAULT 0, 
			`New_SeriaDone` DOUBLE (16,4) DEFAULT 0, 
			`New_TimeCaps` DOUBLE (16,4) DEFAULT 0, 
			`New_AreaGUID` VARCHAR(38) NOT NULL DEFAULT '',  
			`New_WorkgroupGUID` VARCHAR(38) NOT NULL DEFAULT '',  
			PRIMARY KEY (`ID`)
		);
		DELETE FROM `t_edit_pr_krpo_r`;
		
		--	Jaki _Param_D		???		
		IF _Param_D = 'Ng' THEN
		--	dotyczy naglowka
			INSERT INTO `t_edit_pr_krpo_r`	
				SELECT
					NewGUID(),
					_KrNgGUID,
					_KrPoGUID,
					kp.PodzlecGUID, 
					kp.AreaGUID, 
					kp.WorkgroupGUID, 
					kp.TobGUID, 
					kn.Progress, 
					_VarDouble(kn.Progress*100), 
					CURRENT_DATE() AS DateDone,
					SUM(kp.TimeCaps) AS TimeCaps,  
					SUM(kp.TimeDone) AS TimeDone,  
					AVG(kp.TimeDoneProc) AS TimeDoneProc,  
					SUM(kp.Seria) AS Seria, 
					SUM(kp.SeriaDone) AS SeriaDone, 
					kn.Status, 
					sp.DisplayValue,		-- as StatusStr,
					kp.Marszruta_G,
					kp.Tob_SymbolNazwa,
					kp.Rys_SymbolNazwa,
					kp.Elem_SymbolNazwa,

					_VarStr(_ConcatSTR_NotEqu(aso.Symbol, aso.Nazwa,  ' - ')), 
					_VarStr_NotNull(sp1.DisplayValue),
					_VarStr_NotNull(sp2.DisplayValue),
					
					_VarStr(_LogType),	--  AS LogType,
					_VarInt(0),	-- AS Checked,
					_VarInt(0),	--  AS ModifyStatus, 
					_VarDouble(0),	--  As TimeCapsALL,
					_VarDouble(0),	--  As TimeCapsALLTob,
					_VarDouble(0),	--  As TimeDoneALL, 
					_VarDouble(0),	--  As TimeDoneALLTob, 
					_VarDouble(1),	--  As Waga,
					_VarDouble(0),	--  AS TimeToDo,
					_VarDouble(0),	--  As TimeToDoRest,
					_VarDouble(0),	--  AS SeriaToDo,
					_VarDouble(0),	--  As New_TimeDone,
					_VarDouble(0),	--  As New_SeriaDone,
					_VarDouble(0),	--  As New_TimeCaps,
					kp.AreaGUID,	--  AS New_AreaGUID, 
					kp.WorkgroupGUID	--  AS New_WorkgroupGUID 
				FROM `pr_krpo` kp	
				LEFT JOIN `pr_krng` kn ON kn.GUID = kp.KrNgGUID
				LEFT JOIN `pr_tob` tob ON tob.GUID = kp.tobGUID
				LEFT JOIN `erp_asort_types` aso ON aso.GUID = tob.Asortyment
				LEFT JOIN `slowniki_pierwotne` sp1 ON tob.ParamA = sp1.value AND sp1.slownik_guid = "G_STALI"
				LEFT JOIN `slowniki_pierwotne` sp2 ON tob.atest_id = sp2.value AND sp2.slownik_guid = "ATESTY_STALI"
				LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kn.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
				WHERE
					kp.KrNgGUID = _KrNgGUID
				GROUP BY kp.KrNgGUID		
				-- LIMIT 1		
			; 
		ELSE
		--	dotyczy pozycji (1 lub wielu, ato zalezy od tego czy _KrPoIDN <> 0)
			INSERT INTO `t_edit_pr_krpo_r`	
				SELECT
					NewGUID(),
					_KrNgGUID,
					kp.GUID,
					kp.PodzlecGUID, 
					kp.AreaGUID, 
					kp.WorkgroupGUID, 
					kp.TobGUID, 
					kp.Progress, 
					_VarDouble(kp.Progress*100), 
					CURRENT_DATE() AS DateDone,
					kp.TimeCaps, 
					kp.TimeDone, 
					kp.TimeDoneProc, 
					kp.Seria, 
					kp.SeriaDone, 
					kp.Status, 
					sp.DisplayValue,		-- as StatusStr,
					kp.Marszruta_G,
					kp.Tob_SymbolNazwa,
					kp.Rys_SymbolNazwa,
					kp.Elem_SymbolNazwa,

					_VarStr(_ConcatSTR_NotEqu(aso.Symbol, aso.Nazwa,  ' - ')), 
					_VarStr_NotNull(sp1.DisplayValue),
					_VarStr_NotNull(sp2.DisplayValue),
					
					_VarStr(_LogType),	--  AS LogType,
					_VarInt(0),	--  AS Checked,
					_VarINT(0),	--  AS ModifyStatus, 
					_VarDouble(0),	--  As TimeCapsALL,
					_VarDouble(0),	--  As TimeCapsALLTob,
					_VarDouble(0),	--  As TimeDoneALL, 
					_VarDouble(0),	--  As TimeDoneALLTob, 
					_VarDouble(1),	--  As Waga,
					_VarDouble(kp.TimeCaps - kp.TimeDone),	--  AS TimeToDo,
					_VarDouble(0),	--  As TimeToDoRest,
					_VarDouble(kp.Seria - kp.SeriaDone),	--  AS SeriaToDo,
					_VarDouble(0),	--  As New_TimeDone,
					_VarDouble(0),	--  As New_SeriaDone,
					_VarDouble(0),	--  As New_TimeCaps,
					kp.AreaGUID,	--  AS New_AreaGUID, 
					kp.WorkgroupGUID	--  AS New_WorkgroupGUID 
				FROM `pr_krpo` kp	
				LEFT JOIN `pr_tob` tob ON tob.GUID = kp.tobGUID
				LEFT JOIN `erp_asort_types` aso ON aso.GUID = tob.Asortyment
				LEFT JOIN `slowniki_pierwotne` sp1 ON tob.ParamA = sp1.value AND sp1.slownik_guid = "G_STALI"
				LEFT JOIN `slowniki_pierwotne` sp2 ON tob.atest_id = sp2.value AND sp2.slownik_guid = "ATESTY_STALI"
				LEFT JOIN `slowniki_pierwotne` sp ON sp.Value = kp.status AND sp.Slownik_GUID = 'PR_STATUS_KRz'
				WHERE
						IF(_KrPoGUID != '', kp.GUID = _KrPoGUID, kp.KrNgGUID = _KrNgGUID)
			; 
		END IF;	--	_Param_D
		
	--	Rozliczenie
		SELECT
			_VarDouble(SUM(TimeCaps)),
			_VarDouble(SUM(IF(KrPoGUID != '', TimeCaps, 0))),
			_VarDouble(SUM(TimeDone)),
			_VarDouble(SUM(IF(KrPoGUID != '', TimeDone, 0)))
		INTO	
			@_TimeCapsALL, @_TimeCapsALLTob, @_TimeDoneALL, @_TimeDoneALLTob
			FROM `pr_krpo_r` kr	
			WHERE
				kr.KrNgGUID = _KrNgGUID
		; 
		UPDATE `t_edit_pr_krpo_r`
		SET
			TimeCapsALL = ROUND(@_TimeCapsALL, 4),
			TimeCapsALLTob = ROUND(@_TimeCapsALLTob, 4),
			TimeDoneALL = ROUND(@_TimeDoneALL, 4), 
			TimeDoneALLTob = ROUND(@_TimeDoneALLTob, 4),
			Waga = _VarDouble(ROUND(IF(TimeCapsALLTob = 0, 0, (kp_TimeCaps - kp_TimeDone)/(TimeCapsALLTob - TimeDoneALLTob)),5)),
			TimeToDoRest = ROUND(TimeCapsALL*Waga, 4)
		;
		
		IF _LogType = '+' THEN
		--	Pula czasowa
			SET _LogType = '+';
		ELSE
			SET _LogType = '+';
		END IF;	--	LogType

		
		IF (_LogType = '+') AND (_KrPoIDN != 0) THEN
		--	+ PULA Naglowka	
			SET _LogType = '+';
		END IF;
		IF (_LogType = '+') AND (_KrPoIDN = 0) THEN
		--	+ PULA Pozycji
			SET _LogType = '+';
		END IF;

		DROP TEMPORARY TABLE IF EXISTS `t_edit_pr_krpo_r_select`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `t_edit_pr_krpo_r_select`	
			SELECT
				
				r.*
			FROM `t_edit_pr_krpo_r` r	
		; 
		
		select 	* FROM `t_edit_pr_krpo_r`;
		/*
		IF _KrPoIDN != 0 THEN
		--	Rozliczaj jedna pozycje karty
			CALL pr_kr_Tool('EDIT_krng', _KrNgIDN, '', 0, '', '', '', '');
		
		END IF;
		*/
	END IF; 	-- _MODE = 'ROZLICZ_krpo_INIT' 
	
	IF _MODE = 'ROZLICZ_krpo_APPLY' THEN
	--	Zapisuje rekord (rekordy) do rozliczania +/- i przelicza
	--	Bez parametrow
		SET _KrNgGUID = '';
		SET _KrNgIDN = 0;
		SET _KrPoGUID = '';
		SET _KrPoIDN = 0;
	-- Pobierz zmienne
		SELECT 
			KrPoIDN, KrPoGUID, KrNgIDN, KrNgGUID, LogType
		INTO 
			_KrPoIDN, _KrPoGUID, _KrNgIDN, _KrNgGUID, _LogType
		FROM `t_edit_pr_krpo_r_header`	
		;
	--	Kreuj pozycje karty rozliczen `pr_krpo_r`
		SET _OsobaGUID = IFNULL(_Param_C, '');
		IF _OsobaGUID = '' THEN
			SET _OsobaGUID = @session.CurrOsobaGUID;
		END IF;
		
		UPDATE `t_edit_pr_krpo_r`
		SET
			New_TimeCaps = IF(_LogType = '+', New_TimeCaps, 0),
			New_TimeDone = IF(_LogType = '+', 0, New_TimeDone),
			New_SeriaDone = IF(_LogType = '+', 0, New_SeriaDone)
		;
	--	Wstawianie	
		INSERT INTO `pr_krpo_r`
			(
				GUID,
				PodzlecGUID,krngGUID,krpoGUID,
				AreaGUID,WorkgroupGUID,
				LogType,Rodzaj,Progress,
				DateDone,
				TimeCaps,TimeDone,
				Seria,SeriaDone,
				IsOBUN,OsobaGUID
			)
			SELECT
				ID,
				PodzlecGUID,krngGUID,krpoGUID, 				
				New_AreaGUID,New_WorkgroupGUID, 
				_LogType,0, 0,
				DateDone,
				New_TimeCaps, New_TimeDone,
				0, New_SeriaDone, 
				0, _OsobaGUID
			FROM `t_edit_pr_krpo_r`
			WHERE
				ModifyStatus != 0		--	Tylko zmodyfikowane
		;
		/*
		IF _LogType = '+' THEN
		-- Pula czasowa (+)
		ELSE
		-- Rozliczenie (-)
		END IF;
		*/
	
	--	Przelicz w KRz - Ustala ogolny progress karty `pr_krng` z wszystkich `pr_krpo` - parametrem jest _GUID (GUID z `pr_krng`)
		CALL pr_kr_Tool('NALICZ_krng', _KrNgGUID, '', 0, '', '', '', '');		
	END IF; 	-- _MODE = 'ROZLICZ_krpo_APPLY' 
	
	
END$$



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

SET @CASEguid = '{F1A79CB6-2C12-4950-8C98-57D8A0C688DA}'; #17500 Zlec
SET @CASEguid = '{871CB62C-4846-49C2-9881-80719747BAC5}'; #17500/01 Podzlec
SET @CASEguid = '{453C2091-2AD8-4881-98E4-BEBD6BE5EA1A}'; #17500/02 Podzlec
SET @CASEguid = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}'; #17500/03 Podzlec
SET @CASEguid = '{1EE560C8-F23E-46EF-AFE2-E306F5592708}'; #17500/Sloniasty
SET @CASEguid = '{DD496100-9357-44A8-8629-916584C0271E}'; #17500/Klon
SET @Case_GUID = @CASEguid;

CALL pr_kr_Wizard('INIT', @Case_GUID, 0, '', '', '', '');
CALL pr_kr_Wizard('DZIALY', @Case_GUID, 1, '', '', '', '');
-- CALL pr_kr_Wizard(2, @Case_GUID, 0, '', '', '', '');
CALL pr_kr_Wizard('INFO', @Case_GUID, 0, '', '', '', '');

SEt @ID = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}IdCK0';  -- CK
CALL pr_kr_Wizard('CHECK_KARTY', @ID, 1, '', '', '', '');
SEt @ID = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}IdCA0'; -- CA
CALL pr_kr_Wizard('CHECK_KARTY', @ID, 1, '', '', '', '');

SELECT 
tob.Symbol,tob.Def_obj,kp.areaGUID,kp.symbol,kp.pr_krpo_GUID,kp.* 
FROM `temp_karty_pracy` kp
LEFT JOIN `pr_tob` tob ON tob.GUID = kp.tobGUID

;
SELECT * FROM `temp_karty_pracy`
WHERE pr_krpo_GUID != '';

CALL pr_kr_Wizard('CREATE', '', 0, '', '', '', '');

-- erp_workgroups
-- areaGUID
WorkgroupGUID
Wskazanie na GUID tabeli `erp_workgroups`

CALL pr_kr_Wizard('ROZLICZ_krpo_INIT', 'KRZ-66', 0, '*', '', '-', 'Po');

CALL pr_kr_Wizard('ROZLICZ_krpo_INIT', 'KRZ-66', 0, '*', '', '-', 'Po');
CALL pr_kr_Wizard('ROZLICZ_krpo_INIT', 'KRZ-66', 0, '*', '', '+', 'Po');
CALL pr_kr_Wizard('ROZLICZ_krpo_APPLY', '', 0, '', '', '', '');
CALL pr_kr_Tool('NALICZ_krng', '{7E6CB6D2-9EA8-11DE-A5B6-00065BF0376E}', '', 0, '', '', '', '');		

SET @CASEguid = '{2B254A41-69F8-4A45-BDD3-CF770A3A58E3}'; #17500/03 Podzlec
SET @CASEguid = '{DD496100-9357-44A8-8629-916584C0271E}'; #17500/Klon
CALL pr_ZlecWToku_Update(0, zlecGUID, divGUID, '', '');
-- 'Nawa1'
SET @W3 = '{5CA680FC-DFE2-448C-B997-2673440084C7}';
SET @W1 = '{8022922F-1712-49CD-A531-F7958180B305}';

SELECT d.Symbol,dl.* 
FROM crm_div_links dl
LEFT JOIN erp_division_schema d ON d.GUID = dl.DivGUID
WHERE
dl.commisionGUID = @CASEguid;

SELECT * FROM v_pr_division;

CALL pr_div_links_Tool('INIT', @CASEguid, 'Nawa1', '', '', '', '');

CALL pr_div_links_Tool('INIT_NEW', '', '', '0', '', '', '');
INSERT IGNORE INTO `t_Init_zlec_div`
(commisionGUID, divGUID)
VALUES (@CASEguid, 'Nawa1');
INSERT IGNORE INTO `t_Init_zlec_div`
(commisionGUID, divGUID)
VALUES (@CASEguid, @W3);

CALL pr_div_links_Tool('INIT_NEW', '', '', 'CREATE', '', '', '');

CALL pr_div_links_Tool('UPDATE', '', '', '', '', 'P', '');			
CALL pr_div_links_Tool('UPDATE_EXECUTE', @CASEguid, @W1, '', '', '', '');	-- nalicz

SELECT @_NeedUpdate;
SELECT  * FROM `t_PodZelcOkres_Agg`;

				explain
					SELECT
						@CASEguid AS PodzlecGUID,
						_VarInt(SUM(pw.GUID IS NOT NULL)) As C_MW,
						Date(MIN(pw.Data)) AS MinDate,
						Date(MAX(pw.Data)) AS MaxDate,
						_VarDouble(SUM(pmw.masa)) AS ilosc_work
					FROM `crm_div_links` dl 
					LEFT JOIN crm_commisions pw ON pw.Case_GUID = dl.commisionGUID AND pw.Rodzaj = 14 AND pw.PodRodzaj in (4,8,9)  
					LEFT JOIN pr_przesuniecie_mw pmw ON pmw.GUID_Ng = pw.GUID -- AND pmw.DivisionGUIDFrom = @W1 
					LEFT JOIN v_pr_division d ON d.GUID = pmw.DivisionGUIDFrom
					WHERE
						dl.commisionGUID = @CASEguid AND
						-- pmw.DivisionGUIDFrom = @W1 AND
						d.GUID_Wydzialu = @W1 AND
						dl.divGUID = @W1 
					GROUP BY PodzlecGUID
				;
				
				
				explain
					SELECT
						@CASEguid AS PodzlecGUID,
						_VarInt(SUM(pw.GUID IS NOT NULL)) As C_MW,
						Date(MIN(pw.Data)) AS MinDate,
						Date(MAX(pw.Data)) AS MaxDate,
						_VarDouble(SUM(pmw.masa)) AS ilosc_work
					FROM `crm_div_links` dl 
					LEFT JOIN pr_przesuniecie_mw pmw ON pmw.caseguid = dl.commisionGUID
					LEFT JOIN crm_commisions pw ON pw.GUID = pmw.GUID_Ng -- AND pw.Rodzaj = 14 AND pw.PodRodzaj in (4,8,9)  
					LEFT JOIN v_pr_division d ON d.GUID = pmw.DivisionGUIDFrom
					WHERE
						dl.commisionGUID = @CASEguid AND
						-- pmw.DivisionGUIDFrom = @W1 AND
						d.GUID_Wydzialu = @W1 AND
						dl.divGUID = @W1 
					GROUP BY PodzlecGUID
				;
				
				explain
					SELECT
						@CASEguid AS PodzlecGUID,d.GUID_Wydzialu
					FROM `crm_div_links` dl 
					LEFT JOIN crm_commisions pw ON pw.Case_GUID = dl.commisionGUID AND pw.Rodzaj = 14 AND pw.PodRodzaj in (4,8,9)  
					LEFT JOIN pr_przesuniecie_mw pmw ON pmw.GUID_Ng = pw.GUID -- AND pmw.DivisionGUIDFrom = @W1 
					LEFT JOIN v_pr_division d ON d.GUID = pmw.DivisionGUIDFrom
					WHERE
						dl.commisionGUID = @CASEguid AND
						-- pmw.DivisionGUIDFrom = @W1 AND
						d.GUID_Wydzialu = @W1 AND
						dl.divGUID = @W1 
					GROUP BY PodzlecGUID
;				
select * 
from pr_przesuniecie_mw pmw 
WHERE pmw.caseguid = @CASEguid
;


SELECT 
pw.GUID, pw.case_guid, pmw.caseguid, pmw.*
FROM pr_przesuniecie_mw pmw
LEFT JOIN crm_commisions pw ON pw.GUID = pmw.GUID_Ng AND pw.Rodzaj = 14 AND pw.PodRodzaj in (4,8,9)  
WHERE 
pw.case_guid <> pmw.caseguid
;

*/

DELIMITER ;