DELIMITER $$

DROP PROCEDURE IF EXISTS `pr_agregator_execute`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_agregator_execute`()
BEGIN
	SELECT
		ac.rok, if(ac.mc<10,concat('0',ac.mc),ac.mc), ac.stawka_min
		INTO	
			@rok, @mc, @stawka_min
		FROM pr_agregator_conf ac
		WHERE	
		ac.status = 1
		order by ac.rok, ac.mc desc
		limit 1
	;	
	SET @rok = IFNULL(@rok, '');
	SET @stawkamin1=0;
	SET @all=0;
	SET @procent50=0.5;
 	
	
	delete from pr_agregator
	where (rok=@rok and mc=@mc) or @all;

	delete from pr_agregator_ng
	where (rok=@rok and mc=@mc) or @all;


	#exit 

	insert into pr_agregator
	select 
	year(l.modify_date) as rok,month(l.modify_date) as mc,
	concat(left(l.modify_date,8),'01') as data, 
	c.case_guid as caseguid,
	g.dzial as divguid,
	l.areaguid as areaguid,
	'' as workgroupguid,
	z.nrwewn,
	z.liczba_glowna,
	sum(c.liczba_glowna),
	w.symbol as wydzial,
	g.symbol as gniazdo,
	'' as brygada,
	if(c.podrodzaj=6,0,1) as obun,
	#0 as rodzajpracy,
	if(g.symbol in ('LEPU','LEPZ','RSU','RSZ','INDW5','PODW5','LEWGZ'), 5,0) as rodzajpracy,
	#'A' as rodzajpracystr,
	if(g.symbol in ('LEPU','LEPZ','RSU','RSZ','INDW5','PODW5','LEWGZ'), 'Z','A') as rodzajpracystr,
	sum(ifnull(if(l.zmiana_dotyczy<>'TIME_SHEET',l.time_duration,0),0)) as karty_godz_techn,
	sum(ifnull(if(l.zmiana_dotyczy<>'TIME_SHEET',l.wartosc      ,0),0)) as karty_wart_techn,
	sum(ifnull(if(l.zmiana_dotyczy ='TIME_SHEET',l.time_sheet   ,0),0)) as karty_godz,
	sum(ifnull(if(l.zmiana_dotyczy ='TIME_SHEET',l.wartosc      ,0),0)) as karty_wart,
	0 as drp_godz,
	0 as drp_godz_n,
	0 as drp_wart,
	0 as drp_wart_n,
	0 as drp_wart_80

	from crm_commisions c
	inner join crm_log l on l.commissionguid=c.guid
	left join crm_commisions z on c.case_guid=z.guid
	left join erp_area_nodes g on g.guid=l.areaguid
	LEFT JOIN v_pr_division vd on vd.GUID= g.dzial
	LEFT JOIN erp_division_schema w ON w.GUID=vd.GUID_Wydzialu

	where 
	c.deleted=0 and c.rodzaj=14 and c.podrodzaj in (6,7) 
	#and l.zmiana_dotyczy='TIME_SHEET'
	and (year(c.starttime)>2006)
	and ((year(l.modify_date)=@rok and month(date(l.modify_date))=@mc) or @all)
	#and c.guid not like "BOARC%"
	and c.guid like "BO%"
	group by rok,mc,caseguid,divguid,areaguid,obun;

	#exit

	insert into pr_agregator
	select 
	year(l.modify_date) as rok,month(l.modify_date) as mc,
	concat(left(l.modify_date,8),'01') as data, 
	c.case_guid as caseguid,
	g.dzial as divguid,

	l.areaguid as areaguid,
	'' as workgroupguid,
	z.nrwewn,
	z.liczba_glowna,
	sum(c.liczba_glowna),
	w.symbol as wydzial,
	g.symbol as gniazdo,
	'' as brygada,
	if(c.podrodzaj=6,0,1) as obun,
	#0 as rodzajpracy,
	if(g.symbol in ('LEPU','LEPZ','RSU','RSZ','INDW5','PODW5','LEWGZ'), 5,0) as rodzajpracy,
	#'A' as rodzajpracystr,
	if(g.symbol in ('LEPU','LEPZ','RSU','RSZ','INDW5','PODW5','LEWGZ'), 'Z','A') as rodzajpracystr,
	sum(ifnull(if(l.tag=1,l.time_duration,0),0)) as karty_godz_techn,
	sum(ifnull(if(l.tag=1,l.wartosc      ,0),0)) as karty_wart_techn,
	sum(ifnull(if(l.tag=2,l.time_sheet   ,0),0)) as karty_godz,
	sum(ifnull(if(l.tag=2,l.wartosc      ,0),0)) as karty_wart,
	0 as drp_godz,
	0 as drp_godz_n,
	0 as drp_wart,
	0 as drp_wart_n,
	0 as drp_wart_80

	from crm_commisions c
	inner join crm_log l on l.commissionguid=c.guid
	left join crm_commisions z on c.case_guid=z.guid
	left join erp_area_nodes g on g.guid=l.areaguid
	LEFT JOIN v_pr_division vd on vd.GUID= g.dzial
	LEFT JOIN erp_division_schema w ON w.GUID=vd.GUID_Wydzialu

	where 
	c.deleted=0 and c.rodzaj=14 and c.podrodzaj in (6,7) 
	#and l.zmiana_dotyczy='TIME_SHEET'
	and (year(c.starttime)>2006)
	and ((year(l.modify_date)=@rok and month(date(l.modify_date))=@mc) or @all)
	and c.guid not like "BO%" 
	and l.tag in (1,2)
	group by rok,mc,caseguid,divguid,areaguid,obun;




	update akord_drp_ng n,t_zlec t set n.podzlecguid=t.guid 
	where ifnull(n.podzlecguid,'')='' and n.konto_zlecenia=t.nrwewn;
	update akord_drp_ng n,t_zlec t set n.podzlecguid=t.guid 
	where ifnull(n.podzlecguid,'')='' and replace(n.konto_zlecenia,'-','')=t.nrwewn;

	insert into pr_agregator
	select 
	year(n.data_wykonania) as rok,month(n.data_wykonania) as mc,
	concat(left(n.data_wykonania,8),'01') as data, 
	n.podzlecGUID as caseguid,
	n.GUID_division as divguid,
	g.guid as areaguid,
	b.GUID as workgroupguid,
	z.nrwewn,
	z.liczba_glowna,
	0 as masa_pmw,
	w.symbol as wydzial,
	g.symbol as gniazdo,
	b.symbol as brygada,
	0 as obun,
	n.rodzaj as rodzajpracy,
	if(n.rodzaj=0,'A',if(n.rodzaj=4,'D',if(n.rodzaj=5,'Z','P'))) as rodzajpracystr,
	0 as karty_godz_tech,
	0 as karty_wart_tech,
	0 as karty_godz,
	0 as karty_wart,
	sum(round(p.godz/60,2)) as drp_godz,
	sum(round((p.godz150+p.godz200)/60,2)) as drp_godz_n,
	#sum($round(p.godz/60*p.stawka,2)) as drp_wart,
	sum(round(if(n.rodzaj=0,p.godz,0)*greatest(ceiling(100*p.stawka),@stawka_min)/6000,2))  as drp_wart,
	#sum($round((p.godz150*@procent50+p.godz200)/60*p.stawka,2)) as drp_wart_n,
	sum(round((p.godz150*@procent50+p.godz200)*greatest(ceiling(100*p.stawka),@stawka_min)/6000,2)) as drp_wart_n,
	sum(round(if(n.rodzaj=0,p.godz,0)*greatest(ceiling(80*p.stawka),@stawka_min)/6000,2))  as drp_wart_80

	from akord_drp_ng n
	inner join akord_drp_po p on p.guid_ng=n.guid
	left join erp_workgroups b on b.guid=n.guid_workgroup
	left join crm_commisions z on z.guid=n.podzlecguid
	left join erp_area_nodes g on g.guid=b.areaguid
	LEFT JOIN v_pr_division vd on vd.GUID=n.guid_division
	LEFT JOIN erp_division_schema w ON w.GUID=vd.GUID_Wydzialu

	where
	((year(n.data_wykonania)=@rok and month(n.data_wykonania)=@mc) or @all)
	and (year(n.data_wykonania)>2006)


	group by rok,mc,caseguid,divguid,areaguid,workgroupguid,rodzajpracy;
	#group by rok,mc,caseguid,divguid,areaguid,workgroupguid,rodzajpracy,p.guid_pracownika;


	insert into pr_agregator_ng
	select 
	0 as status,
	rok,mc,dataokresu,divguid,areaguid,wydzial,gniazdo,
	sum(karty_godz)/sum(drp_godz)*100 as procent_czas_obl,
	sum(karty_wart)/sum(drp_wart)*100 as procent_wart_obl,
	0 as procent_zatwierdzony, 
	now()

	from pr_agregator
	where ((rok=@rok and mc=@mc) or @all)
	and rodzajpracy in (0,4,5)
	# and wydzial like "W-%"
	#group by dataokresu,divguid,areaguid;
	group by dataokresu,wydzial,gniazdo;


	update pr_agregator_ng set procent_zatwierdzony=floor(100*procent_zatwierdzony)/100 where rok=2008 and mc<11;

	#zaokraglenia do 2 miejsc dla nowych
	update pr_agregator_ng set 
	procent_czas_obl=round(procent_czas_obl,2),
	procent_wart_obl=round(procent_wart_obl,2)
	where rok=2008 and mc>=11;

	update pr_agregator_ng
	set procent_zatwierdzony = greatest(ifnull(procent_czas_obl,0),ifnull(procent_wart_obl,0))
	where concat(rok,mc)>'200810' and (rok=@rok and mc=@mc) or @all;

	# korekta wyliczonego procentu dla KGR w pazdzierniku:
	#update pr_agregator_ng set procent_zatwierdzony=118.99  where rok=2008 and mc=10 and gniazdo='KGR'; 
	
END$$

DROP PROCEDURE IF EXISTS `pr_Clone_TOB_TOP`$$

DROP PROCEDURE IF EXISTS `pr_Clone_TOB_Wizard`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_Clone_TOB_Wizard`(
	IN _MODE INT,
	IN _Clone_MODE INT,
	IN _Case_GUID VARCHAR(38),
	IN _TobGUID VARCHAR(38),
	IN _Check_Value INT,
	IN _Znacznik_tob INT,
	IN _Copy_TOP BOOLEAN
)
BEGIN
/*
	_Clone_MODE
	Ogolnie mamy nast. tryby klonowania
	0	-	zaznaczone rysunki
	1	-	wybrana pozycja
	2	-	caly obiekt
	_MODE
	0	Inicjacja - wymagany _Case_GUID i _Clone_MODE - Tworzona jest tabelka ze zmiennymi
	5	Ustawienie param klonowania : czy podzespoy i serie
	10	Przygotowanie selekcji zaleznej od trybu  - wymagany _Znacznik_tob
		Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc rysunki TRYB A
		Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc drzewo TRYB B
	
	20	Zaznacz pole Checked dla pozycji = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)
		GDY _Check_Value = -1 to tylko ten TOB bedzie checked inne nie (Tryb B)
	NIE MUSIMY wolac dla _Clone_MODE = 2 !!!!
	
--	21	Czysc i Buduj od nowa TOB-y do klonowania
	
	30	Docelowe zlecenie - wymagany tylko _Case_GUID (RESZTA PUSTA)
	//	41	Przygotuj liste dla @Dest_Case_GUID (temp tob i top) i zwroc drzewo
	
	40	Przekaz docelową pozycję = _TobGUID do ktorej dowiazemy tob-y do klonowania
	
	80	Klonuj - wymagany _Copy_TOP
	100	Uzupelnij zmienne
	200	Usun TOB-y z docelowego zlecenia - wymagany tylko _Case_GUID (RESZTA PUSTA)

	_Znacznik_tob	
		-1 	wszystkie
		0	tylko aktywne
		1	tylko aktywne i kooperacja
		
*/
	DECLARE _Def_Obj INT DEFAULT 0;
	DECLARE Ilosc_Seria_END INT DEFAULT 1;
	DECLARE _New_UID VARCHAR(38);

	IF (_MODE > 0) AND (_MODE != 100) AND (_MODE != 200) THEN
	--	Pobierz zmienne
		CALL pr_Clone_TOB_Wizard(100, 0, '', '', 0, 0, FALSE); 
	END IF;	
	
	IF _MODE = 0 THEN
	/*	Inicjacja - wymagany _Case_GUID - Tworzona jest tabelka ze zmiennymi	*/
		DROP TEMPORARY TABLE IF EXISTS `t_pr_clone_tob_params`;
		CREATE TEMPORARY TABLE `t_pr_clone_tob_params`(
			`Case_GUID` VARCHAR(38) NOT NULL DEFAULT '',    		--	PodZlecenie
			`Case_GUID_Dest` VARCHAR(38) NOT NULL DEFAULT '',  		--	PodZlecenie docelowe
			`End_Hanger_TOB_GUID` VARCHAR(38) NOT NULL DEFAULT '', --	pozycja docelowa
			`Clone_MODE`  INT DEFAULT 0, 			           		--	0 zaznaczone rysunki, 	1 wybrana pozycja, 2 caly obiekt
			`Znacznik_tob`  INT DEFAULT -1, 			           	--	-1 	wszystkie, 0	tylko aktywne, 1	tylko aktywne i kooperacja
			`Copy_TOP`  INT DEFAULT 0, 			           		--	Ile operacji technolog. (w zmpo)
			`Obj8_TOB_GUID` VARCHAR(38) NOT NULL DEFAULT '', 		--	obiekt glowny zlecenia
			`Obj8_TOB_GUID_Dest` VARCHAR(38) NOT NULL DEFAULT '', 	--	obiekt glowny zlecenia docelowego moze byc ze Obj8_TOB_GUID_Dest = End_Hanger_TOB_GUID
			PRIMARY KEY (`Case_GUID`)
		);
		
		SELECT
			GUID
		INTO
			@Clone_Obj8_TOB_GUID
		FROM pr_tob
		WHERE
			Case_GUID = _Case_GUID AND def_obj = 8
		;
		INSERT IGNORE INTO `t_pr_clone_tob_params`
		SET
			Case_GUID = _Case_GUID,
			Clone_MODE = _Clone_MODE,
			Obj8_TOB_GUID = @Clone_Obj8_TOB_GUID
		;	
	--	Kreuj tabelke Def_obj_Filtr
		DROP TEMPORARY TABLE IF EXISTS `t_def_obj_filter`;
		-- DROP TABLE IF EXISTS `t_def_obj_filter`;
		CREATE TEMPORARY TABLE `t_def_obj_filter`(
		-- CREATE TABLE `t_def_obj_filter`(
			`Def_Obj`  INT DEFAULT 0, 			           	--	Czy seria, podzespol
			`Checked`  INT DEFAULT 0 			           	--	0 nie klonuj, 1 klonuj
		);	
		INSERT IGNORE INTO `t_def_obj_filter`
		SET 	Def_Obj = 1, Checked = 1
		;
		INSERT IGNORE INTO `t_def_obj_filter`
		SET 	Def_Obj = 2, Checked = 1
		;
		INSERT IGNORE INTO `t_def_obj_filter`
		SET 	Def_Obj = 3, Checked = 1
		;
		INSERT IGNORE INTO `t_def_obj_filter`
		SET 	Def_Obj = 8, Checked = 1
		;
		INSERT IGNORE INTO `t_def_obj_filter`
		SET 	Def_Obj = -2, Checked = 0
		;
		INSERT IGNORE INTO `t_def_obj_filter`
		SET 	Def_Obj = -3, Checked = 0
		;
	
	END IF;	-- _MODE = 0

	IF _MODE = 100 THEN
	/*	Uzupelnij zmienne	*/
		SELECT 
			`Case_GUID`, `Clone_MODE`, `Case_GUID_Dest`,  `End_Hanger_TOB_GUID`, 
			`Znacznik_tob`, `Copy_TOP`, `Obj8_TOB_GUID`, `Obj8_TOB_GUID_Dest` 
		INTO 
			@Clone_Case_GUID, @Clone_Clone_MODE, @Clone_Case_GUID_Dest,  @Clone_End_Hanger_TOB_GUID, 
			@Clone_Znacznik_tob, @Clone_Copy_TOP, @Clone_Obj8_TOB_GUID, @Clone_Obj8_TOB_GUID_Dest 
		FROM
			`t_pr_clone_tob_params`
		;	
	END IF;	-- _MODE = 100
	
	IF _MODE = 5 THEN
	/*	Ustawienie param klonowania : czy podzespoy i serie	*/
		IF _TobGUID = 'P' THEN
			SET _Def_Obj = -3;	--	Podzespol
		ELSE	
			SET _Def_Obj = -2;	--	Seria
		END IF;
		
		UPDATE `t_def_obj_filter`
		SET 	
			Checked = _Check_Value
		WHERE
			Def_Obj = _Def_Obj 
		;
	END IF;	-- _MODE = 5
	
	IF _MODE = 10 THEN
	/*	Przygotowanie selekcji zaleznej od trybu  - wymagany _Znacznik_tob:  -1 wszystkie, 0	tylko aktywne, 1	tylko aktywne i kooperacja */
		UPDATE `t_pr_clone_tob_params`
		SET
			Znacznik_tob = _Znacznik_tob
		;	
	--	Pobierz zmienne
		CALL pr_Clone_TOB_Wizard(100, 0, '', '', 0, 0, FALSE); 
	--	Buduj TOBY dla Podzlecenia @Clone_Case_GUID
		CALL pr_TOB_Create_Temp_Tob(@Clone_Case_GUID, False);
		DROP TEMPORARY TABLE IF EXISTS `t_pr_clone_tob_choice`;
		CREATE TEMPORARY TABLE `t_pr_clone_tob_choice` 
			SELECT
				tob.GUID,
				tob.ParentGUID,
				tob.Symbol,
				IFNULL(tob.Nazwa, '') As Nazwa,
				tob.case_GUID,
				tob.Rysunek_GUID,
				tob.Rysunek_Symbol,
				tob.Rysunek_Nazwa,
				tob.Element_GUID,
				tob.Element_Symbol,
				tob.Element_Nazwa,
				tob.Def_Obj,
				tob.Znacznik_tob,
				tob.Clone_From_GUID,
				_VarINT(0) AS Checked,
				_VarINT(0) AS Deleted,
				tob.Img_Idx
			--	tdo.Img_Idx
			FROM temp_pr_tob_view tob
			-- LEFT JOIN pr_techn_def_obj tdo ON tdo.IDN = tob.Def_Obj
			INNER JOIN `t_def_obj_filter` f ON f.Def_Obj = tob.Def_Obj AND f.Checked <> 0
			WHERE
				tob.Def_Obj <> -4 and tob.Def_Obj <> 8			--	BEZ kart OBUN !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
		;
		--	Uwzglednij Znacznik_tob @Clone_Znacznik_tob	-1 wszystkie, 0	tylko aktywne, 1	tylko aktywne i kooperacja
		UPDATE `t_pr_clone_tob_choice`
		SET
			Deleted = 
				IF(@Clone_Znacznik_tob = -1, 0, 
					IF(@Clone_Znacznik_tob = 0, Znacznik_tob != 0, Znacznik_tob = 2)) 
		;
		IF (@Clone_Clone_MODE = 2) THEN	-- Dla klonowania calego zlecenia
			CALL pr_Clone_TOB_Wizard(20, 2, '', '', 1, 0, FALSE); 
		ELSE								-- Dla pozostalych trybow 
			IF @Clone_Clone_MODE = 0 THEN
			--	Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc rysunki TRYB A	
				SELECT 
					IF(tobc.Def_Obj != 3, tobc.Nazwa, Concat('Detal ', Nazwa, ' ', tobc.Symbol)) AS NazwaFull,
					tobc.* 
				FROM `t_pr_clone_tob_choice` tobc
				WHERE
					Deleted = 0 AND Def_Obj = 1
				;	
			END IF;
			IF @Clone_Clone_MODE = 1 THEN
			--	Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc drzewo TRYB B	
				SELECT 
					IF(tobc.Def_Obj != 3, tobc.Nazwa, Concat('Detal ', Nazwa, ' ', tobc.Symbol)) AS NazwaFull,
					tobc.* 
				FROM `t_pr_clone_tob_choice` tobc
				WHERE
					Deleted = 0 -- AND Def_Obj >= 0
				;	
			END IF;
		END IF;
		
	END IF;	-- _MODE = 10
	
	IF _MODE = 20 THEN
	/*	Zaznacz pole Checked dla pozycji = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)
		GDY _Check_Value = -1 to tylko ten TOB bedzie checked inne nie (Tryb B)
	*/
		IF (@Clone_Clone_MODE = 1) OR (_Check_Value = -1) THEN		-- Dla tylko jednej wybrane pozycji
			UPDATE `t_pr_clone_tob_choice` cl
			SET
				cl.Checked = 0
			;
			UPDATE `t_pr_clone_tob_choice` cl
			SET
				cl.Checked = 1
			WHERE
				cl.GUID = _TobGUID AND cl.Deleted = 0
			;
		ELSE
			IF (@Clone_Clone_MODE = 0) THEN		-- Dla wielu wybranych pozycji
				UPDATE `t_pr_clone_tob_choice` cl
				SET
					cl.Checked = _Check_Value
				WHERE
					cl.GUID = _TobGUID AND cl.Deleted = 0
				;
			END IF;
		END IF;
		IF (@Clone_Clone_MODE = 2) THEN	-- Dla klonowania calego zlecenia - 1. wszystkie Checked na 0, 2. Rysunki (pod obiektem) Checked na 1
			UPDATE `t_pr_clone_tob_choice` cl
			SET
				cl.Checked = 0
			;	
			UPDATE `t_pr_clone_tob_choice` cl
			SET
				cl.Checked = 1
			WHERE
				cl.ParentGUID = @Clone_Obj8_TOB_GUID AND cl.Deleted = 0
			;
		END IF;
	END IF;	-- _MODE = 20
	
	

	IF _MODE = 30 THEN
	/*	Docelowe zlecenie - wymagany tylko _Case_GUID (RESZTA PUSTA)
		Przygotuj liste dla @Dest_Case_GUID (temp tob i top) i zwroc drzewo
 	*/
		SELECT
			GUID
		INTO
			@Clone_Obj8_TOB_GUID_Dest
		FROM pr_tob
		WHERE
			Case_GUID = _Case_GUID AND def_obj = 8
		;
		UPDATE `t_pr_clone_tob_params`
		SET
			Case_GUID_Dest = _Case_GUID,
			Obj8_TOB_GUID_Dest = @Clone_Obj8_TOB_GUID_Dest,
			End_Hanger_TOB_GUID = IF(@Clone_Clone_MODE in (0,2), @Clone_Obj8_TOB_GUID_Dest, '')
		;	
	--	Pobierz zmienne
		CALL pr_Clone_TOB_Wizard(100, 0, '', '', 0, 0, FALSE); 
	--	Buduj TOBY dla Podzlecenia docelowego @Clone_Case_GUID_Dest
		CALL pr_TOB_Create_Temp_Tob(@Clone_Case_GUID_Dest, False);
		DROP TEMPORARY TABLE IF EXISTS `t_pr_clone_tob_dest`;
		CREATE TEMPORARY TABLE `t_pr_clone_tob_dest`
			SELECT
				tob.GUID,
				tob.ParentGUID,
				tob.Symbol,
				IFNULL(tob.Nazwa, '') As Nazwa,
				tob.case_GUID,
				tob.Rysunek_GUID,
				tob.Rysunek_Symbol,
				tob.Rysunek_Nazwa,
				tob.Element_GUID,
				tob.Element_Symbol,
				tob.Element_Nazwa,
				tob.Def_Obj,
				tob.Znacznik_tob,
				tob.Clone_From_GUID,
				_VarINT(0) AS Checked,
				_VarINT(0) AS Deleted,
				tdo.Img_Idx
			FROM temp_pr_tob_view tob
			LEFT JOIN pr_techn_def_obj tdo ON tdo.IDN = tob.Def_Obj
			WHERE
				tob.Def_Obj <> -4			--	BEZ kart OBUN !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
		;
		IF @Clone_Clone_MODE = 1 THEN		-- NIE Dla klonowania calego zlecenia bo wtedy nie musimy szukac wezla podwiazania (End_Hanger_TOB_GUID)
			SELECT 
				IF(tobc.Def_Obj != 3, tobc.Nazwa, Concat('Detal ', Nazwa, ' ', tobc.Symbol)) AS NazwaFull,
				tobc.* 
			FROM `t_pr_clone_tob_dest` tobc
			WHERE
				Deleted = 0 -- AND Def_Obj >= 0
				;	
		END IF;	-- @Clone_Clone_MODE = 1
		IF @Clone_Clone_MODE = 2 THEN		-- @Clone_Clone_MODE = 2
			UPDATE `t_pr_clone_tob_choice` cl, `t_pr_clone_tob_dest` cld
			SET
				cl.Checked = 0
			WHERE
				cl.GUID = cld.Clone_From_GUID
			;
		END IF;	-- @Clone_Clone_MODE <> 2
	END IF;	-- _MODE = 30

	IF _MODE = 40 THEN
	/*	Przekaz docelową pozycję = _TobGUID do ktorej dowiazemy tob-y do klonowania	*/
		IF @Clone_Clone_MODE <> 2 THEN		-- NIE Dla klonowania calego zlecenia bo wtedy nie musimy szukac wezla podwiazania (End_Hanger_TOB_GUID)
			UPDATE `t_pr_clone_tob_params`
			SET
				End_Hanger_TOB_GUID = _TobGUID
			;	
		--	Pobierz zmienne
			CALL pr_Clone_TOB_Wizard(100, 0, '', '', 0, 0, FALSE); 
		END IF;	-- @Clone_Clone_MODE <> 2
	END IF;	-- _MODE = 40

	IF _MODE = 80 THEN
	/*	Klonuj	*/
		SELECT
			Checked INTO @_SeriaImported
		FROM `t_def_obj_filter`
		WHERE
			Def_Obj = -2 	-- Seria
		;	
		SET @_SeriaImported = IFNULL(@_SeriaImported, 0);
	/*	Klonuj	*/
		UPDATE `t_pr_clone_tob_params`
		SET
			Copy_TOP = _Copy_TOP
		;	
	--	Pobierz zmienne
		CALL pr_Clone_TOB_Wizard(100, 0, '', '', 0, 0, FALSE); 
		
	/*	Kreuj `pr_tob_tree_down`		*/
		CALL pr_Tree_Down_Tob_Tool(0, @Clone_Case_GUID, '');
		
	/*	Wpisz pozycje zaznaczone (do `t_pr_clone_tob_choice`) aby wpisac potem do `pr_tob_tree_down`	(Mode = 5)	*/
		UPDATE `t_pr_tob_zlec_all` ttob, `t_pr_clone_tob_choice` cl, `t_def_obj_filter` f 

		SET
			ttob.tag_service = cl.Checked
		WHERE
			ttob.GUID = cl.GUID AND cl.Checked = 1 AND cl.Deleted = 0 AND
			f.Def_Obj = ttob.Def_Obj AND f.Checked <> 0
		;	
	/*	Buduj w `pr_tob_tree_down`	pozycje zaznaczone (z `t_pr_clone_tob_choice`)	(Mode = 5)	*/
		CALL pr_Tree_Down_Tob_Tool(5, '', '');

		SET @uuID = NewGUID();

		DROP TEMPORARY TABLE IF EXISTS `pr_tob_clone`;
		CREATE TEMPORARY TABLE `pr_tob_clone`
			SELECT 
				ttob.*
			FROM `pr_tob_tree_down`  ttob, `t_pr_clone_tob_choice` cl	
			WHERE
				ttob.GUID = cl.GUID AND cl.Deleted = 0
		;
		UPDATE `pr_tob_clone`
			SET `parentguid` = '*'			-- wartosc specjalna - najwyzszy klonowany zapis
		WHERE
			`tag_service` = 1
		;
	
	/*	Ustaw Clone_From_GUID	`pr_tob_clone`		*/
		UPDATE `pr_tob_clone`
			SET `Clone_From_GUID` = `GUID`		
		;
		
		IF @Clone_Copy_TOP THEN
		/*	Kreowanie top		*/
			DROP TEMPORARY TABLE IF EXISTS `zm`;
			CREATE TEMPORARY TABLE `zm`
				SELECT 
				* 
				FROM `zmpo` 
				WHERE 
					`tobguid` IN (SELECT DISTINCT `guid` FROM `pr_tob_clone`)
			;
		END IF;

	/*	Ustaw nowe klucze `pr_tob_clone`		*/
		UPDATE `pr_tob_clone`
		SET
			`guid` = md5(CONCAT(`guid`, @uuID)),
			`Case_GUID` = @Clone_Case_GUID_Dest,
			`status_tob` = 4,			# oznacza Skolonowane
			`GUID_serii` = IFNULL(`GUID_serii`, ''),
			`koliaGUID` = ''
		;
		
		IF @_SeriaImported != 1 THEN
			UPDATE `pr_tob_clone`
			SET
				`GUID_serii` = ''
			;
		END IF;
		

	/*	Ustaw nowe wskażniki rodziców `pr_tob_clone`		*/
		UPDATE `pr_tob_clone`
		SET
			`ParentGUID` = md5(CONCAT(`ParentGuid`, @uuID)),
			`ParentGUID_tech` = md5(CONCAT(`Parentguid_tech`, @uuID)),
			`GUID_serii` = IF(`GUID_serii` = '', '', md5(CONCAT(`GUID_serii`, @uuID)))
		WHERE
			`ParentGUID` != '*'
		;

	/*	update nieistniejace parentguid_tech na parentguid		*/
		DROP TEMPORARY TABLE IF EXISTS `tobguids`;
		CREATE TEMPORARY TABLE `tobguids`
			SELECT
				`guid`
			FROM `pr_tob_clone`
		;
		UPDATE `pr_tob_clone`
		SET
			`parentguid_tech` = `ParentGUID`
		WHERE
			NOT (`parentguid_tech` IN (SELECT `guid` FROM `tobguids`))
		;

	/*	Najstarszych rodzicow podlacz do @Clone_End_Hanger_TOB_GUID (wartosc specjalna - najwyzszy klonowany zapis)		*/
		UPDATE `pr_tob_clone`
		SET
			`ParentGUID` = @Clone_End_Hanger_TOB_GUID,
			`parentguid_tech` = @Clone_End_Hanger_TOB_GUID
		WHERE
			`ParentGUID` = '*'
		;

	/*	Wstaw zawartosc `pr_tob_clone` do `pr_tob` (Nowe, sklonowane `pr_tob_clone`-y)		*/
		INSERT INTO `pr_tob`
			SELECT
				*
			FROM `pr_tob_clone`
		;

		IF (@Clone_Clone_MODE = 2) THEN	-- Dla klonowania calego zlecenia - Przepisz warstwy i inne do dest
			UPDATE `pr_tob` dest, `pr_tob` src
			SET
				dest.ilosc_warstw = src.ilosc_warstw,
				dest.pow_pokr_J = src.pow_pokr_J,
				dest.pow_pokr_C_Agg = src.pow_pokr_C_Agg,
				dest.Uwagi = src.Uwagi,
				dest.Uwagi_Kosztowe = src.Uwagi_Kosztowe,
-- 				dest.date_loco = src.date_loco,
				dest.need_pack = src.need_pack
-- 				dest.DeadLine = src.DeadLine,
--				dest.CzyZakoliowany = src.CzyZakoliowany
			WHERE
				dest.GUID = @Clone_Obj8_TOB_GUID_Dest AND src.GUID = @Clone_Obj8_TOB_GUID
			;	
		END IF;
		
		IF @Clone_Copy_TOP THEN
		/*	Ustaw nowe klucze top (zmpo)		*/
			UPDATE `zm` SET
				`idn` = 0,
				`guid` = md5(CONCAT(`guid`, @uuID)),
				`TOBguid` = md5(CONCAT(`TOBguid`, @uuID)),
				`Commision_GUID` = '',
				`pr_krpo_GUID` = '',
				`idnNG` = '?'
			;

		/*	Wstaw zawartosc zm do zmpo (Nowe, sklonowane top-y)		*/
			INSERT INTO `zmpo`
				SELECT 
					* 
				FROM `zm`
			;
		END IF;
	--	Nalicz cały obiekt	
		CALL pr_Recalc_TOB_After_Edit(@Clone_Case_GUID_Dest, @Clone_Obj8_TOB_GUID_Dest); 
	END IF;	-- _MODE = 80

	IF _MODE = 200 THEN
		CALL pr_Clone_TOB_Wizard(0, 0, _Case_GUID, '', 0, 0, FALSE); 
	/*	Usun TOB-y z docelowego zlecenia - wymagany tylko _Case_GUID (RESZTA PUSTA)  	*/
		IF _Case_GUID != '' THEN
			UPDATE `t_pr_clone_tob_params`
			SET
				Case_GUID_Dest = _Case_GUID
			;	
		--	Pobierz zmienne
			CALL pr_Clone_TOB_Wizard(100, 0, '', '', 0, 0, FALSE); 
		END IF;
		DELETE FROM `pr_tob`
		WHERE
			`Case_GUID` = @Clone_Case_GUID_Dest AND Def_Obj <> 8
		;	
	END IF;	-- _MODE = 200
	
END$$

DROP PROCEDURE IF EXISTS `pr_Clone_TOP`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_Clone_TOP`(
	IN _MODE INT,
	IN _TOP_GUID VARCHAR(38),
	IN _Copy_Start_TOB_GUID VARCHAR(38),
	IN _End_Hanger_TOB_GUID VARCHAR(38)
)
BEGIN
/*
	_MODE
	0	Czysc liste pr_top_copy_list (lista top-ow do kopiowania)
	1	Wstaw _TOP_GUID do pr_top_copy_list (lista top-ow do kopiowania)
	2	Wstaw wszystkie topy dotyczące  _Copy_Start_TOB_GUID do pr_top_copy_list (lista top-ow do kopiowania)
	3	Klonuj pr_top_copy_list (lista top-ow do kopiowania) i podlacz do _End_Hanger_TOB_GUID
	4	Klonuj pr_top_copy_list (lista top-ow do kopiowania) ALE NIE podlaczaj do _End_Hanger_TOB_GUID
*/
	DECLARE Max_LP INT DEFAULT 0;
	DECLARE Ilosc_Seria_END INT DEFAULT 1;
	DECLARE End_Hanger_Is_OBUN BOOLEAN DEFAULT FALSE;
	DECLARE _New_UID VARCHAR(38);

	IF _MODE = 0 THEN
	/*	Czysc liste pr_top_copy_list (lista top-ow do kopiowania)	*/
		DROP TEMPORARY TABLE IF EXISTS `pr_top_copy_list`;
	END IF;
	
	CREATE TEMPORARY TABLE IF NOT EXISTS `pr_top_copy_list`
		SELECT
			top.GUID, 
			_VarSTR('') AS Case_GUID,
			top.Commision_GUID
		FROM zmpo top
		WHERE 0 = 1	
	;
	ALTER TABLE `pr_top_copy_list` ADD UNIQUE (`guid`);
		
	IF _MODE = 1 THEN
	/*	Wstaw _TOP_GUID do pr_top_copy_list (lista top-ow do kopiowania)	*/
		INSERT IGNORE INTO `pr_top_copy_list`
			SELECT 
				top.GUID, 
				tob.Case_GUID,
				top.Commision_GUID
			FROM `zmpo` top
			INNER JOIN `pr_tob` tob ON tob.GUID = top.tobguid
			WHERE
				top.GUID = _TOP_GUID
		;
	END IF;
	
	IF _MODE = 2 THEN
	/*	Wstaw wszystkie topy dotyczące  _Copy_Start_TOB_GUID do pr_top_copy_list (lista top-ow do kopiowania)	*/
		INSERT IGNORE INTO `pr_top_copy_list`
			SELECT 
				top.GUID, 
				tob.Case_GUID,
				top.Commision_GUID
			FROM `zmpo` top
			INNER JOIN `pr_tob` tob ON tob.GUID = top.tobguid
			WHERE
				tob.GUID = _Copy_Start_TOB_GUID
		;
	END IF;
	
	IF _MODE IN (3, 4) THEN
	/*	Klonuj pr_top_copy_list (lista top-ow do kopiowania) i podlacz do _End_Hanger_TOB_GUID	*/
		SET _New_UID = NewGUID();

		/*	Ustalenie Ilosc_Seria_END		*/
		SELECT
			`Ilosc_seria`
		INTO
			Ilosc_Seria_END
		FROM
			`pr_tob`
		WHERE
			`guid` = _End_Hanger_TOB_GUID
		;	
		SET Ilosc_Seria_END = IFNULL(Ilosc_Seria_END, 1);
		
		/*	Ustalenie LP		*/
		SELECT
			MAX(`lp_int`) AS Max_LP
		INTO
			Max_LP
		FROM
			`zmpo`
		WHERE
			`TOBguid` = _End_Hanger_TOB_GUID
		;	
		SET Max_LP = IFNULL(Max_LP, 0);
		SET @LP = Max_LP; 
		
		/*	Kreowanie top		*/
		DROP TEMPORARY TABLE IF EXISTS `zm`;
		CREATE TEMPORARY TABLE `zm`
			SELECT 
				`zmpo`.* 
			FROM `zmpo` 
			WHERE 
				`guid` IN (SELECT DISTINCT `guid` FROM `pr_top_copy_list`)
		;
		
		IF _MODE = 4 THEN
			SELECT 
				(IFNULL(Def_Obj, 0) = -4)
		    INTO
				End_Hanger_Is_OBUN
			FROM pr_tob
			WHERE
				`GUID` = _End_Hanger_TOB_GUID
			;	
		END IF;	
		/*	Ustaw nowe klucze top (zmpo)		*/
		UPDATE `zm` SET
			`idn` = 0,
			`guid` = md5(CONCAT(`guid`, _New_UID)),
			`TOBguid` = _End_Hanger_TOB_GUID,
			`Commision_GUID` = '',
			`pr_krpo_GUID` = '',
			`lp_int` = (@LP := @LP + 5),
			`lp_vc` = LPAD(@LP, 3, '0'),
			`pr_ilosc_seria` = IF(End_Hanger_Is_OBUN, `pr_ilosc_seria`, Ilosc_Seria_END),
			`Ilosc` = pr_ilosc_in * pr_ilosc_mul * IF(End_Hanger_Is_OBUN, `pr_ilosc_seria`, Ilosc_Seria_END) + pr_ilosc_add,
			`wartnet` = Ilosc * cenanet,
			`idnNG` = '?'
		;

		IF _MODE = 3 THEN
			/*	Wstaw zawartosc zm do zmpo (Nowe, sklonowane top-y)		*/
			INSERT INTO `zmpo`
				SELECT * FROM `zm`;
		END IF;
		
		IF _MODE = 4 THEN
			SELECT 
				IF(zp.GUID=zp.Commision_GUID, '1', '0') As IsDomyslny, 
				GetJednostkaStr(zp.jednostka) as jednostkaStr, 
				GetJednostkaStr(zp.pr_jednostka_in) as pr_jednostka_inStr, 
				GetJednostki2Str(zp.jednostka, zp.pr_jednostka_in, NULL) As pr_jednostka_MulStr, 
				pr_TOB_Get_Top_oper_def_type_Jednostka(zp.pr_Top_oper_def_type, zp.Jednostka) As oper_def_type_Jednostka, 
				AN.Nazwa As AreaNazwa, 
				RT.DisplayValue As Res_TypeStr, 
				zp.*
				
			FROM zm zp
			LEFT JOIN erp_area_nodes AN ON AN.GUID = zp.areaGUID 
			LEFT JOIN v_resource_type RT ON RT.Value = zp.Res_Type 
			;
		END IF;
		
		/*	Sprzatanie	*/
		#DROP TEMPORARY TABLE IF EXISTS `pr_top_copy_list`;
		DROP TEMPORARY TABLE `zm`;
	END IF;	
END$$

DROP PROCEDURE IF EXISTS `pr_Clone_TOP_Multi`$$

DROP PROCEDURE IF EXISTS `pr_Clone_TOP_Wizard`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_Clone_TOP_Wizard`(
	IN _MODE INT,
	IN _Case_GUID VARCHAR(38),
	IN _TobGUID VARCHAR(38),
	IN _Check_Value INT,
	IN _MODE_Def_Obj INT
)
BEGIN
/*
JEST
	_MODE
	0	Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc rysunki
	1	Zaznacz pole Checked dla rysunku = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)
	2	zwroc elementy rysunkow wybranych (rysunek musi miec Checked <> 0)
	3	Zaznacz pole Checked dla elementu = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)
	4	Pobierz rysunki w ktorych chcesz szukac odpowiednikow	
	5	Zaznacz pole Checked dla docelowego rysunku = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)
	6	Przygotowanie do klonowania
	7	Klonuj	
		A)	Usun niedopasowane czyl te Enabled = 0
		B)	Klonuj zmpo
BYLO
	_MODE
	0	Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc rysunki
	1	Zawez temp-y do rysunku _Rysunek_GUID_Src i zwroc elementy
	2	Przygotuj liste dla _Case_GUID_Dest (temp tob i top)
	3	Przygotowanie do klonowania
		A)	Zawez temp-y do elementu gdy _Element_GUID_Src <> ''
		B)	Zawez temp-y do Def_Obj
			_MODE_Def_Obj = 0 		Elementy i Detale
			_MODE_Def_Obj = 2		Elementy
			_MODE_Def_Obj = 3		Detale
		C)	Zawez temp-y do rysunku _Rysunek_GUID_Dest o ile <> ''
		D)	Poszukaj odpowiednikow dla Src w Dest (Enabled = 1 gdy pasuja oraz Enabled = 0 gdy NIE pasuja
	4	Klonuj	
		A)	Usun niedopasowane czyl te Enabled = 0
		B)	Klonuj zmpo
*/
	DECLARE Max_LP INT DEFAULT 0;
	DECLARE Ilosc_Seria_END INT DEFAULT 1;
	DECLARE _New_UID VARCHAR(38);

	IF _MODE = 0 THEN
	/*	Przygotuj liste dla _Case_GUID (temp tob i top) i zwroc rysunki	*/
		CALL pr_TOBTOP_Tool_Wizard(0, _Case_GUID, '', _Check_Value, 0);
	END IF;	-- _MODE = 0

	IF _MODE = 1 THEN
	/*	Zaznacz pole Checked dla rysunku = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)	*/
		CALL pr_TOBTOP_Tool_Wizard(1, '', _TobGUID, _Check_Value, 0);
	END IF;	-- _MODE = 1
	
	IF _MODE = 2 THEN
	/*	zwroc elementy rysunkow wybranych (rysunek musi miec Checked <> 0) 	*/
		CALL pr_TOBTOP_Tool_Wizard(2, '', '', 0, 0);
	END IF;	-- _MODE = 2
	
	IF _MODE = 3 THEN
	/*	Zaznacz pole Checked dla elementu = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value) 	*/
		CALL pr_TOBTOP_Tool_Wizard(3, '', _TobGUID, _Check_Value, 0);
	END IF;	-- _MODE = 3

	IF _MODE = 4 THEN
	/*	Pobierz rysunki w ktorych chcesz szukac odpowiednikow	*/
		CALL pr_TOBTOP_Tool_Wizard(4, _Case_GUID, '', 0, 0);
	END IF;	-- _MODE = 4

	IF _MODE = 5 THEN
	/*	Zaznacz pole Checked dla docelowego rysunku = _TobGUID - Wywoluj n-razy dla zaznaczonych i niezaznaczonych (pole _Check_Value)	*/
		CALL pr_TOBTOP_Tool_Wizard(5, '', _TobGUID, _Check_Value, 0);
	END IF;	-- _MODE = 5

	IF _MODE = 6 THEN
	/*	Przygotowanie do klonowania	*/
		CALL pr_TOBTOP_Tool_Wizard(6, '', '', 0, _MODE_Def_Obj);
	END IF;	-- _MODE = 6
	
	IF _MODE = 7 THEN
	/*	Klonuj		*/
		/*	Kreowanie tabeli `zm` jako top	dla nieusunietych (Deleted = 0)	*/
		CALL pr_TOBTOP_Tool_Wizard(20, '', '', 0, 0);
		
		/*	Ustaw nowe klucze top (zmpo)		*/
		UPDATE `zm` SET
			`idn` = 0,
			`guid` = NewGUID(),
			`TobGUID` = (
			SELECT
				cl.TOB_GUID_Dest
			FROM pr_top_clone_list cl
			WHERE
				cl.TOB_GUID = `zm`.TobGUID
			),
			`Commision_GUID` = '',
			`pr_krpo_GUID` = '',
			`idnNG` = '?'
		;
		UPDATE `zm` top, `pr_tob` tob 
		SET
			top.`pr_ilosc_seria` = tob.Ilosc_seria
		WHERE
			top.TobGUID = tob.GUID
		;
		UPDATE `zm` top
		SET
			`Ilosc` = pr_ilosc_in * pr_ilosc_mul * `pr_ilosc_seria` + pr_ilosc_add,
			`wartnet` = Ilosc * cenanet
		;
		/*	Wstaw zawartosc zm do zmpo (Nowe, sklonowane top-y)		*/
		INSERT INTO `zmpo`
			SELECT * FROM `zm`
		;
		
		/*	Wstaw naddatki z SrcTob do DestTob		*/
		DROP TEMPORARY TABLE IF EXISTS `temp_tob_nad`;
		CREATE TEMPORARY TABLE `temp_tob_nad`
			SELECT
				ctob.TOB_GUID,
				ctob.TOB_GUID_Dest,
				tob.naddatek_minX,
				tob.naddatek_maxX,
				tob.naddatek_minY,
				tob.naddatek_maxY,
				tob.naddatek_minZ,
				tob.naddatek_maxZ,
				tob.uwagi_techn,
				tob.shape
			FROM `pr_top_clone_list` ctob
			INNER JOIN `pr_tob` tob ON tob.GUID = ctob.TOB_GUID
			WHERE
				ctob.Def_Obj IN (-2,3) AND ctob.Deleted = 0
		;
		UPDATE `pr_tob` tob, `temp_tob_nad` ctob
		SET
			tob.naddatek_minX = ctob.naddatek_minX,
			tob.naddatek_maxX = ctob.naddatek_maxX,
			tob.naddatek_minY = ctob.naddatek_minY,
			tob.naddatek_maxY = ctob.naddatek_maxY,
			tob.naddatek_minZ = ctob.naddatek_minZ,
			tob.naddatek_maxZ = ctob.naddatek_maxZ,
			tob.uwagi_techn = ctob.uwagi_techn,
			tob.shape = ctob.shape
		WHERE
			ctob.TOB_GUID_Dest = tob.GUID
		;
		DROP TEMPORARY TABLE IF EXISTS `temp_tob_nad`;
		
	END IF;	-- _MODE = 7
	
END$$

DROP PROCEDURE IF EXISTS `pr_Create_zmpo_PrzekazWydzialowy`$$

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 LastUpdateDivLinks w `_db_globals`
		CALL _Globals_Get('LastUpdateDivLinks', @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', '', '', '', '', '', '');
		
	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_KartaPracy_Log_Resume`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_KartaPracy_Log_Resume`(
	IN _GUID VARCHAR(38),
	IN _STATUS_ZREALIZOWANO BOOLEAN)
	CONTAINS SQL
BEGIN
	DECLARE _Ilosc_zmpo DOUBLE DEFAULT 0;
	DECLARE _WartNet_zmpo DOUBLE DEFAULT 0;
	DECLARE _CenaNet_zmpo DOUBLE DEFAULT 0;
	DECLARE _AreaGUID VARCHAR(38) DEFAULT NULL;

	DECLARE _TimeDuration DOUBLE DEFAULT 0;
	DECLARE _TimeSheet DOUBLE DEFAULT 0;
	DECLARE _CountSheet DOUBLE DEFAULT 0;
	DECLARE _Progress DOUBLE DEFAULT 0;
	DECLARE _TimeSheetABS DOUBLE DEFAULT 0;
	DECLARE _STATUS INTEGER DEFAULT 0;
	DECLARE _PodZlec_GUID VARCHAR(38); 
	DECLARE _Div_GUID VARCHAR(38);
	
	
	/*	Zlecz z zmpo 	*/
	SELECT
		SUM(IF(`rodzaj` = 73, `ilosc`, 0)), --  73 zlecenie produkcyjne
		SUM(`wartnet`),
		`areaGUID`,
		SUM(IF(`rodzaj` = 98, `ilosc`, 0)) --  98 przekaz miedzywydzialowy
	INTO
		_Ilosc_zmpo,
		_WartNet_zmpo, 
		_AreaGUID,
		_CountSheet
	FROM `zmpo`
	WHERE 
		`commision_guid` = _GUID
	GROUP BY `commision_guid`
	;
	
	SET _Ilosc_zmpo = ROUND(IFNULL(_Ilosc_zmpo, 0), 2);
	SET _WartNet_zmpo = ROUND(IFNULL(_WartNet_zmpo, 0), 2);
	SET _CenaNet_zmpo = 0;
	IF _Ilosc_zmpo != 0 THEN
		SET _CenaNet_zmpo = ROUND(_WartNet_zmpo / _Ilosc_zmpo, 2);
	END IF;
	
	/*	Czy LOG jest zainicjowany ?	*/
	IF NOT EXISTS (SELECT * FROM `crm_log` WHERE `commissionguid` = _GUID) THEN
		/*	Wstaw do LOG-a gdy nie jest jescze zainicjowany	*/
		CALL pr_KartaPracy_Log_Insert(_GUID, _Ilosc_zmpo, 0.0, _AreaGUID, 0, NULL); -- @CountSheet
	END IF;
	
	/*	update LOG aby wpisac wartosc realizacji	*/
	UPDATE `crm_log`
	SET 
		time_duration = ROUND(time_duration, 2),
		time_sheet = ROUND(time_sheet, 2),
		wartosc = ROUND(IFNULL(time_sheet, 0) * _CenaNet_zmpo, 2)
	WHERE 
		`commissionguid` = _GUID
	;
	
	UPDATE `crm_log` log
	SET 
		tag = IF(ABS(log.time_sheet) > 0.0001 AND log.time_duration = 0, 2, IF(log.time_duration > 0 AND log.time_sheet = 0, 1, 0))
	WHERE
		log.CommissionGUID = _GUID
	;
	/*	zliczenie czasu i ilości z loga	*/
	SELECT
		SUM(`time_duration`),
		SUM(`time_sheet`),
		SUM(ABS(`time_sheet`)),
		SUM(`count_sheet`)
	INTO
		_TimeDuration,
		_TimeSheet,
		_TimeSheetABS,
		_CountSheet
	FROM `crm_log`
	WHERE 
		`commissionguid` = _GUID
	GROUP BY `commissionguid`
	;
/*	Ustaw Progress		*/ 	
	IF _TimeDuration = 0 THEN
		SET _Progress = 0;
	ELSE	
		SET _Progress = 100 * _TimeSheet / _TimeDuration;
	END IF;
	
/*	Ustaw STATUS		*/ 	
	IF _STATUS_ZREALIZOWANO THEN
		SET _STATUS = 3;			--  CZG_STATUS_ZREALIZOWANO       = 3;
	ELSE
		IF _Progress >= 99.98 THEN
			SET _STATUS = 3;			--  CZG_STATUS_ZREALIZOWANO       = 3;
		ELSEIF _TimeSheetABS > 0.01 THEN 
			SET _STATUS = 2;			--  CZG_STATUS_WREALIZACJI        = 2;
		ELSE	
			SET _STATUS = 1;			
		END IF;
	END IF;
	
	/*	update wartości w crm_commisions	*/
	UPDATE `crm_commisions`
	SET
		`Duration` = _TimeDuration,
		`Work_Duration` = _TimeSheet,
		`pr_ilosc_work` = _CountSheet,
		`Koszt` = _WartNet_zmpo,
		`progress` = _Progress,
		-- `status` = IF(`progress` < 100, `status`, 3)
		-- `status` = IF(_STATUS_ZREALIZOWANO, 3, IF(`progress` < 99.98, IF(`progress` > 0.2, 2, `status`), 3))
		`status` = _STATUS
	WHERE 
		`GUID` = _GUID
	;
	
--	Nalicza rekord _PodZlec_GUID/_Div_GUID - pola czasow etc.
	SELECT 
		KaPr.Case_GUID AS PodZlec_GUID, n.Dzial AS Div_GUID
	INTO
		_PodZlec_GUID, _Div_GUID
	FROM `crm_commisions` KaPr 
	INNER JOIN `erp_area_nodes` n ON n.GUID = KaPr.AreaGUID
	WHERE
		KaPr.GUID = _GUID
	;
	
	IF (IFNULL(_PodZlec_GUID, '') != '') AND (IFNULL(_Div_GUID, '') != '')  THEN
		-- jmam CALL pr_ZlecWToku_Update(1, _PodZlec_GUID, _Div_GUID, '0000-00-00', '0000-00-00');
		CALL pr_div_links_Tool('INIT', _PodZlec_GUID, _Div_GUID, '', '', '', '');
	END IF;
	
		

/*		
 CZG_STATUS_WREALIZACJI        = 2;
 CZG_STATUS_ZREALIZOWANO       = 3;
 CZG_STATUS_DOSPRAWDZENIA      = 7;
*/
END$$

DROP PROCEDURE IF EXISTS `pr_KartyPracy_CloseForDivision`$$




/*
Sprawdz gdzie jest wolana
pr_KartaPracy_CheckUpdate
ORAZ
sam ta procedura w ktorej jestesmy czyli
pr_KartyPracy_CloseForDivision
*/

DROP PROCEDURE IF EXISTS `pr_KartyPracy_Create_Karta_pracy`$$



/*
jmamDelete
pr_KartyPracy_Create_Karta_pracy wolana tylko TU
Chyba w PAS

*/

DROP PROCEDURE IF EXISTS `pr_DaneDoKartPracy`$$
DROP PROCEDURE IF EXISTS `pr_KartyPracy_DaneDoKartPracy`$$





/*
jmamDelete
pr_KartyPracy_DaneDoKartPracy wolana tylko TU
Chyba w PAS

pr_DaneDoKartPracy wolana tylko TU
*/

DROP PROCEDURE IF EXISTS `pr_KartyPracy_UpdateCenyTOP`$$




/*
jmamDelete
pr_KartyPracy_UpdateCenyTOP wolana w pr_KartyPracy_Create_Karta_pracy
oraz TU
*/

DROP PROCEDURE IF EXISTS `pr_KartyPracy_Update_temp_karty_pracy`$$




/*
jmamDelete
pr_KartyPracy_Update_temp_karty_pracy wolana TU 
oraz w pr_KartyPracy_Create_Karta_pracy 
*/

DROP PROCEDURE IF EXISTS `pr_KartyPracy_Update_TOP_From_temp_karty_pracy`$$




/*
jmamDelete
pr_KartyPracy_Update_TOP_From_temp_karty_pracy wolana tylko TU
Chyba w PAS
*/

DROP PROCEDURE IF EXISTS `pr_przesunieciaMW_Create_Przez_From_Temp`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_przesunieciaMW_Create_Przez_From_Temp`(
	IN _CommsGUID VARCHAR(38),
	IN _NrWewn VARCHAR(50),
	IN _DataPrzekazu DATE
)
MODIFIES SQL DATA
BEGIN
	#DECLARE _NewGUID VARCHAR(38) DEFAULT NewGUID();
	DECLARE _NewPrzekazGUID VARCHAR(38);
	DECLARE _Case_GUID VARCHAR(38);
	DECLARE _Def_Obj INT;
	DECLARE _PodRodzaj INT;
	
	SET _NewPrzekazGUID = IFNULL(_CommsGUID, '');
	IF _NewPrzekazGUID = '' THEN
		SET _NewPrzekazGUID = NewGUID(); # Nowy klucz systemowy
	END IF;

	# Pobierz Case_GUID i Def_Obj
	SELECT
		`Case_GUID`,
		`Def_Obj`
	INTO
		_Case_GUID,
		_Def_Obj
	FROM `pr_przesunieciaMW_TempTable`
	LIMIT 1;
	
	SET _Def_Obj = IFNULL(_Def_Obj, 2);
	IF _Def_Obj = 2 THEN
		SET _PodRodzaj = 8;	/*	Elementy	*/
	ELSE
		SET _PodRodzaj = 4;	/*	Detale	*/
	END IF;	
	IF _Def_Obj = 8 THEN
		SET _PodRodzaj = 9;	/*	Objekt	*/
	END IF;	
	
	/*	Kreuje w crm_commisions zdarzenei typy 'przekaz'	*/
#	DO crm_CommsCreate(_NewPrzekazGUID, 14, _PodRodzaj, '', '', 0, _NrWewn, '', _Case_GUID, '', '', '', _DataPrzekazu);
	DO crm_Create_Commision(_NewPrzekazGUID, 14, _PodRodzaj, '', '', 0, _NrWewn, '', _Case_GUID, '', '', '', '');

	/*	Ustaw source jako typ obiektu (element/detal ale moze OBUN?)	*/
	UPDATE `crm_commisions`
		SET
                `source` = _Def_Obj,
                `StartTime` = _DataPrzekazu
	WHERE
		`GUID` = _NewPrzekazGUID;

	/*	Wstawiam toby do przekazu	*/
			
	/*	Ustaw MASY przekazu	*/
	IF _Def_Obj = 8 THEN
		INSERT INTO `pr_przesuniecie_mw`
			(
				`GUID`,
				`GUID_Ng`,
				`TobGUID`,
				`DivisionGUIDFrom`,
				`DivisionGUIDTo`,
				`ilosc`,
				`caseguid` ,
				`masa`,
				`opis`
			)
			SELECT
				NewGUID(),
				_NewPrzekazGUID,
				`TobGUID`,
				`DivisionGUIDFrom`,
				`DivisionGUIDTo`,
				1,
				`Case_GUID` ,
				`Ilosc_Wykonaj`,
				`opis`
			FROM `pr_przesunieciaMW_TempTable`
		;
			
	
	END IF;	
	IF _Def_Obj != 8 THEN
		INSERT INTO `pr_przesuniecie_mw`
			(
				`GUID`,
				`GUID_Ng`,
				`TobGUID`,
				`DivisionGUIDFrom`,
				`DivisionGUIDTo`,
				`ilosc`,
				`caseguid` ,
				`masa`
			)
			SELECT
				NewGUID(),
				_NewPrzekazGUID,
				`TobGUID`,
				`DivisionGUIDFrom`,
				`DivisionGUIDTo`,
				IF(_Def_Obj = 8, 1, `Ilosc_Wykonaj`),
				`Case_GUID` ,
				IF(_Def_Obj = 8, `Ilosc_Wykonaj`, 0)
			FROM `pr_przesunieciaMW_TempTable`
			WHERE
				`Ilosc_Wykonaj` > 0
		;
			
		UPDATE pr_przesuniecie_mw mw, pr_tob tob
		SET
			mw.Masa = mw.Ilosc*tob.Ilosc_J
		WHERE
			mw.GUID_Ng = _NewPrzekazGUID
			AND mw.TobGUID = tob.GUID;  
	
	--	Inicjuj tabele `t_Init_zlec_div`	
		CALL pr_div_links_Tool('INIT_NEW', '', '', '0', '', '', '');	
	--	wstawiaj do `t_Init_zlec_div` pary zlecenie/wydzial
		INSERT IGNORE INTO `t_Init_zlec_div`
			(SELECT Case_GUID, DivisionGUIDFrom FROM pr_przesunieciaMW_TempTable)
		;	
		INSERT IGNORE INTO `t_Init_zlec_div`
			(SELECT Case_GUID, DivisionGUIDTo FROM pr_przesunieciaMW_TempTable)
		;	
	--	Kreuj w `crm_div_links` pary zlecenie/wydzial
		CALL pr_div_links_Tool('INIT_NEW', '', '', 'CREATE', '', 'P', '');
		-- CALL pr_div_links_Tool('UPDATE', '', '', '', '', '', '');
	END IF;	
END$$

DROP PROCEDURE IF EXISTS `pr_TOBTOP_Status_Wizard`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOBTOP_Status_Wizard`(
	IN _MODE INT,
	IN _TOB_GUID VARCHAR(38),
	IN _Def_Obj INT,
	IN _MODE_TOBTOP INT,
	IN _STATUS INT,
	IN _RECUSIVE BOOLEAN,
	IN _rcpoGUID VARCHAR(38)
	)
	CONTAINS SQL
	COMMENT 'Ustawia statusy w `pr_tob` i `zmpo`'
BEGIN
	/*
	_MODE_TOBTOP
		0		Status konstrukcji TOB czyli ustawiamy Status_Tob w `pr_tob`
		1		Status technologii TOP czyli ustawiamy Status_Top w `pr_tob` oraz Status w `zmpo`
	_MODE
		0:		Ustawianie, Tworzenie tymczasowej tabeli operacji 
		1:		SELECT tymczasowej tabeli operacji - pobieranie jej SELECT
		2:		Wstawianie do tymczasowej tabeli operacji (_rcpoGUID) dla ktorych ustawiamy status
		3:		Ustawianie statusow !!!!
	_STATUS
				Ustawiany STATUS
	_MODE_OR
		0:		Wstawia wartosc wprost Status = _Status
		1:		Wstawia wartosc jako OR Status = Status OR _Status
	_RECUSIVE
		TRUE: 	Ustawia dla TOB-a i jego dzieci, wnukow etc.
		FALSE: 	Ustawia dla konkretnego TOB-a
		
	_Def_Obj 
		0:		Ustaw dla wszystkich	
		2:		Ustaw dla Elementow i Podzespolow oraz rysunkow i obiektow -  czyli wszystko z wyjatkiem serii i detali
		3:		Ustaw dla Tylko dla Detali i Serii	
	*/
	
	DECLARE _MODE_TREE INT DEFAULT 0;
	DECLARE _Ile_Top INT DEFAULT 0;
	DECLARE _Ile_Top_Valid INT DEFAULT 0;
	
/*	
	DECLARE _1_GUID VARCHAR(38) DEFAULT NULL;
	DECLARE _2_GUID VARCHAR(38) DEFAULT NULL;
	DECLARE _3_GUID VARCHAR(38) DEFAULT NULL;
	DECLARE _4_GUID VARCHAR(38) DEFAULT NULL;
	
	SET _1_GUID = _TOB_GUID;
	
	UPDATE `pr_tob` tob
	SET
		Status_Tob = IF(_STATUS_TOB < 0, Status_Tob, IF(_MODE = 0, _STATUS_TOB, Status_Tob OR _STATUS_TOB)),
		Status_Top = IF(_STATUS_TOP < 0, Status_Top, IF(_MODE = 0, _STATUS_TOP, Status_Top OR _STATUS_TOP))
	WHERE
		tob.GUID = _1_GUID
	;
*/	
	IF _RECUSIVE THEN
		SET _MODE_TREE = 0;
	ELSE
		SET _MODE_TREE = -1;
	END IF;
	
	IF _MODE = 0 THEN
	--	Kreuj `pr_tob_tree_down`		
		CALL pr_Create_Tree_Down_Tob(_TOB_GUID, _MODE_TREE);	

	--	Gdy _Def_Obj = 0 ustawiamy wszystkie wezly, ale gdy _Def_Obj <> 0 to usuwamy zj `pr_tob_tree_down`zbedne!!!!		*/
		IF _Def_Obj = 2 THEN
		--	Elementy i Podzespoly oraz rysunki i obiekty czyli wszystko z wyjatkiem serii i detali	*/
			DELETE 
			FROM `pr_tob_tree_down`
			WHERE
				(Def_Obj IN (3, -2))	/*	USUWAMY Detale i nie serie	*/
			;	
		END IF;
		IF _Def_Obj = 3 THEN
		/*	Tylko Detale i Serie	*/
			DELETE 
			FROM `pr_tob_tree_down`
			WHERE
				NOT (Def_Obj IN (3, -2))	/*	USUWAMY NIE detale i NIE serie	*/
			;	
		END IF;
		
	--	Temp technologi - operacje tobow z drzewa pr_tob_tree_down
		DROP TEMPORARY TABLE IF EXISTS `t_status_top`;
		CREATE TEMPORARY TABLE `t_status_top`
			SELECT
				_VarINT(1) As Valid,
				top.*
			FROM `zmpo` top
			INNER JOIN `pr_tob_tree_down` tob ON top.TobGUID = tob.GUID
			WHERE
				IFNULL(top.commision_guid, '') = '' AND	--	tylko dla top-ow BEZ KaPr !!!!!!!!!
				IFNULL(top.pr_krpo_GUID, '') = ''			--	tylko dla top-ow BEZ KrZ !!!!!!!!!
		;
		
	--	Temp operacji, ktore wystepuja w topach `t_status_top`
		DROP TEMPORARY TABLE IF EXISTS `t_status_oper`;
		CREATE TEMPORARY TABLE `t_status_oper`
			SELECT
				NAME_CONST('Checked', '1'),		--	Domyslnie Zaznaczony
				rcpo.GUID, rcpo.towrsymb, rcpo.opis 
			FROM `rcpo` rcpo
			INNER JOIN `t_status_top` top ON top.rcpoGUID = rcpo.GUID
			GROUP BY rcpo.GUID
		;
		
	END IF;	--	IF _MODE = 0
	
	IF _MODE = 1 THEN
	--	SELECT tymczasowej tabeli operacji - pobieranie jej SELECT
	--	Poniewaz bedziemy wybierac operacje dla ktorych ma byc ustawiony status to ZDEJMUJEMY check dla wszystkich
		UPDATE `t_status_oper`
		SET
			Checked = 0
		;
	--	SELECT	
		SELECT *	
		FROM `t_status_oper`
		;
		
	END IF;	--	IF _MODE = 1
	
	
	IF _MODE = 2 THEN
	--	Wstawianie do tymczasowej tabeli operacji (_rcpoGUID) dla ktorych ustawiamy status
		UPDATE `t_status_oper`
		SET
			Checked = 1
		WHERE 
			GUID = _rcpoGUID
		;
	
	END IF;	--	IF _MODE = 2
	
	IF _MODE = 3 THEN
	--	Ustawianie statusow !!!!
		IF _MODE_TOBTOP = 0 THEN
		--	Ustawiamy statusy 	w tob-ach (`pr_tob`)		*/
			UPDATE `pr_tob` tob, `pr_tob_tree_down` tob_tree
			SET
				tob.Status_Tob = _STATUS
			WHERE
				tob.GUID = tob_tree.GUID
			;
		END IF;	--	IF _MODE_TOBTOP = 0
		
		
		IF _MODE_TOBTOP = 1 THEN
		--	Ustawiamy statusy w top-ach (`zmpo`)		*/
		--	Tylko top-y dla operacji zaznaczonych
			UPDATE `t_status_top` top, `t_status_oper` oper
			SET
				top.Valid = IF(oper.Checked = 1, 1, 0)	
			WHERE
				top.rcpoGUID = oper.GUID
			;
		--	Ile jest Top-ow i ile jest Valid ?	
			SELECT
				SUM(1) As IleTop, SUM(IF(Valid <> 0, 1, 0)) As Ile_Top_Valid
			INTO
				_Ile_Top, _Ile_Top_Valid
			FROM `t_status_top` top
			;
			SET _Ile_Top = IFNULL(_Ile_Top, 0);
			SET _Ile_Top_Valid = IFNULL(_Ile_Top_Valid, 0);
			IF _Ile_Top = _Ile_Top_Valid THEN
			--	Ustawiamy statusy 	w tob-ach (`pr_tob`) ale tylko gdy dla wszystkich operacji		
				UPDATE `pr_tob` tob, `pr_tob_tree_down` tob_tree
				SET
					tob.Status_Top = _STATUS
				WHERE
					tob.GUID = tob_tree.GUID
				;
			END IF;
		--	Ustawiamy statusy w zmpo	
			UPDATE `zmpo` top, `t_status_top` status_top
			SET
				top.Status = _STATUS
			WHERE
				top.GUID = status_top.GUID AND
				status_top.Valid = 1
			;
		END IF;	--	IF _MODE_TOBTOP = 1
			
	END IF;	--	IF _MODE = 3
	

END$$

DROP PROCEDURE IF EXISTS `pr_TOB_GetInfoAboutTOP`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOB_GetInfoAboutTOP`(
	IN _TOB_GUID VARCHAR(38),
	OUT _IlePotomkow INT,
	OUT _S_TOP INT,
	OUT _SKartyPracy INT,
	OUT _SBezKartyPracy INT,
	OUT _StatusTOB INT,
	OUT _StatusTOP INT
)
	CONTAINS SQL
BEGIN
	DECLARE _Status INT DEFAULT 0;
	
	/*	Ile jest potomkow pozycji _TOB_GUID	*/
	SELECT
		COUNT(*) AS Ile
	INTO
		_IlePotomkow	
	FROM `pr_tob` tob
	WHERE
		tob.ParentGUID = _TOB_GUID
	;
	SET _IlePotomkow = IFNULL(_IlePotomkow, 0);
		
	/*	Daj statusy i liczbe operacji	*/
	SELECT
		SUM(1), # S_TOP
		SUM(IF(IFNULL(`commision_guid`, '') = '', 0, 1) + IF(IFNULL(`pr_krpo_GUID`, '') = '', 0, 1)), -- S_KartyPracy,
		SUM(IF(IFNULL(`commision_guid`, '') != '', 0, 1) + IF(IFNULL(`pr_krpo_GUID`, '') != '', 0, 1)) -- S_BezKartyPracy
	INTO
		_S_TOP,
		_SKartyPracy,
		_SBezKartyPracy
	FROM `zmpo` top
	WHERE
		top.TOBGUID = _TOB_GUID
	;
	SET _S_TOP = IFNULL(_S_TOP, 0);
	SET _SKartyPracy = IFNULL(_SKartyPracy, 0);
	SET _SBezKartyPracy = IFNULL(_SBezKartyPracy, 0);
	
	SELECT 
		`status`, `status_tob`, `status_top`  
	INTO 
		_Status, _StatusTOB, _StatusTOP 
	FROM `pr_tob` 
	WHERE 
		`GUID` = _TOB_GUID
	;
	SET _StatusTOB = IFNULL(_StatusTOB, -1);
	SET _StatusTOP = IFNULL(_StatusTOP, -1);
	
	/*
	# _StatusTOB 1..99
	SET _StatusTOB = _Status % 100;
	# _StatusTOP 100..9900
	SET _StatusTOP = _Status DIV 100;
	SET _StatusTOP = _StatusTOP % 100;
	*/
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_Create_Temp_Top`$$ --  zmiany jmam 12-08-2008 14:10
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_Create_Temp_Top`(
	IN _Case_GUID VARCHAR(38),
	IN _WithOBUN BOOL,
	IN _SHOW BOOL
)
	MODIFIES SQL DATA
BEGIN
	SET @Case_GUID = _Case_GUID;
	-- SET @GuidTOB = pr_GetPersistantTOB_ForCase_GUID(@Case_GUID, 8, TRUE);
	-- SET @GuidTOB = _TOB_GUID;
	CALL pr_TOB_Create_Temp_Tob(@Case_GUID, FALSE);

	DROP TEMPORARY TABLE IF EXISTS `temp_pr_top_view`;
	CREATE TEMPORARY TABLE IF NOT EXISTS `temp_pr_top_view`
		SELECT
			tob.Case_GUID,
			top.towrsymb, top.towrnazwa,
			AN.Nazwa AS AreaNazwa,
			AN.Symbol AS Gniazdo,
			CASE
				WHEN AN.Symbol LIKE 'RS%' THEN 'RS'
				WHEN AN.Symbol LIKE 'LEP%' THEN 'LEP'
				ELSE AN.Symbol
			END AS gniazdo_tech,
			tob.GUID AS tob_GUID,
			tob.Def_Obj,
			tob.Kind,
			tob.TobSymbol AS Tob_Symbol,
			tob.Rysunek_GUID,
			tob.Rysunek_Nazwa,
			tob.Rysunek_Symbol,
			tob.Rysunek_Ilosc_C_Total,
			tob.Element_GUID,
			tob.Element_Nazwa,
			tob.Element_Symbol,
			tob.Element_Ilosc_C_Total,
			tob.Elementy_Parent,
			tob.Asortyment_Nazwa,
			tob.Ilosc AS tob_Ilosc,
			tob.Ilosc_J AS tob_Ilosc_J,
			tob.Ilosc_C AS tob_Ilosc_C,
			tob.Ilosc_C_Total AS tob_Ilosc_C_Total,
			tob.Wymiar_X,
			tob.Wymiar_Y,
			tob.Wymiar_Z,
			tob.Wymiar_XYZ,
			tob.Wymiar_XYZ_Str,
			tob.naddatek_minX,
			tob.naddatek_maxX,
			tob.naddatek_minY,
			tob.naddatek_maxY,
			tob.uwagi AS tob_uwagi,
			tob.marszruta,
			tob.znacznik_tob,
			rc.towrsymb AS operacja,
			rc.opis AS kod_op,
			rc.UnitPrice_Rate AS GZ,
			RT.DisplayValue AS Res_TypeStr,
			GetJednostkaStr(top.jednostka) AS jednostkaStr,
			GetJednostkaStr(top.pr_jednostka_in) AS pr_jednostka_inStr,
			GetJednostki2Str(top.jednostka, top.pr_jednostka_in, NULL) AS pr_jednostka_MulStr,
			pr_TOB_Get_Top_oper_def_type_Jednostka(top.pr_Top_oper_def_type, top.Jednostka) AS oper_def_type_Jednostka,
			top.info,
			top.GUID, top.commision_guid, top.pr_krpo_GUID, top.TobGUID, top.idnNG, top.rodzaj, top.idntowr,
			top.ilosc, top.zrealizowano, top.pr_ilosc_in, top.pr_ilosc_mul, top.pr_ilosc_add, top.pr_ilosc_seria,
			top.cenanet, top.wartnet, top.jednostka, top.pr_jednostka_in, top.rcpoGUID,
			top.zlecono, top.terminrealizacji, top.iloscdo, top.kontrGUID,
			top.Res_Type, top.pr_Top_oper_def_type,
			top.areaGUID, top.lp_int, top.lp_vc, top.typ_zmiany, top.nr_zmiany, top.ver,
			top.Uwagi,
			top.Uwagi AS opis_oper,
			Wydzial.symbol AS Wydzial,
			Wydzial.Nazwa AS WydzialNazwa,
			Wydzial.GUID AS Wydzial_GUID,
			Wydzial_top.symbol AS Wydzial_top,
			Wydzial_top.Nazwa AS Wydzial_topNazwa,
			Wydzial_top.GUID AS Wydzial_top_GUID,
		-- 	tob.*,
			IF(top.GUID = top.Commision_GUID, '1', '0') AS IsDomyslny
		FROM `zmpo` top
		LEFT JOIN erp_area_nodes AN ON AN.GUID = top.areaGUID
		LEFT JOIN v_pr_division Wydzial_top ON Wydzial_top.GUID = AN.dzial --  to jest wejscie do wydzialu
		LEFT JOIN erp_division_schema Wydzial ON Wydzial.GUID = Wydzial_top.GUID_Wydzialu --  to jest wejscie do wydzialu
		LEFT JOIN v_resource_type RT ON RT.Value = top.Res_Type
		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
		INNER JOIN temp_pr_tob_view tob ON tob.GUID = top.TobGUID
		WHERE
			-- tob.Case_GUID = @Case_GUID AND
			top.Rodzaj = 73 AND
			(top.Rodzaj_Typ = 0 OR _WithOBUN)
	;
	
	ALTER TABLE `temp_pr_top_view`
		ADD INDEX GUID(GUID),
		ADD INDEX tob_GUID(tob_GUID),
		ADD INDEX Rysunek_GUID(Rysunek_GUID),
		ADD INDEX Element_GUID(Element_GUID),
		ADD INDEX Wydzial_GUID(Wydzial_GUID),
		ADD INDEX Wydzial_top_GUID(Wydzial_top_GUID)
	;
	
	IF _SHOW THEN		
		SELECT * FROM `temp_pr_top_view`;
	END IF;	
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_Delete_TOP`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_Delete_TOP`(
	IN _TOB_GUID VARCHAR(38),
	IN _RECUSIVE BOOLEAN,
	IN _Def_Obj INT
	)
	CONTAINS SQL
	COMMENT 'Ustawia statusy w `pr_tob`'
BEGIN
	/*
	_RECUSIVE
		TRUE: 	Ustawia dla TOB-a i jego dzieci, wnukow etc.
		FALSE: 	Ustawia dla konkretnego TOB-a
		
	_Def_Obj 
		0:		Usun wszystkie operacje
		2:		Usun operacje dla Elementow i Podzespolow oraz rysunkow i obiektow -  czyli wszystko z wyjatkiem serii i detali
		3:		Ustaw operacje dla Tylko dla Detali i Serii	
	*/
	DECLARE _STATUS INT DEFAULT 0;
	DECLARE _MODE_TREE INT DEFAULT 0;
	
/*	
	DECLARE _1_GUID VARCHAR(38) DEFAULT NULL;
	DECLARE _2_GUID VARCHAR(38) DEFAULT NULL;
	DECLARE _3_GUID VARCHAR(38) DEFAULT NULL;
	DECLARE _4_GUID VARCHAR(38) DEFAULT NULL;
	
	SET _1_GUID = _TOB_GUID;
	
	UPDATE `pr_tob` tob
	SET
		Status_Tob = IF(_STATUS_TOB < 0, Status_Tob, IF(_MODE = 0, _STATUS_TOB, Status_Tob OR _STATUS_TOB)),
		Status_Top = IF(_STATUS_TOP < 0, Status_Top, IF(_MODE = 0, _STATUS_TOP, Status_Top OR _STATUS_TOP))
	WHERE
		tob.GUID = _1_GUID
	;
*/	
	IF _RECUSIVE THEN
		SET _MODE_TREE = 0;
	ELSE
		SET _MODE_TREE = -1;
	END IF;
	
/*	Kreuj `pr_tob_tree_down`		*/
	CALL pr_Create_Tree_Down_Tob(_TOB_GUID, _MODE_TREE);	

/*	Gdy _Def_Obj = 0 ustawiamy wszystkie wezly, ale gdy _Def_Obj <> 0 to usuwamy zj `pr_tob_tree_down`zbedne!!!!		*/
	IF _Def_Obj = 2 THEN
	/*	Elementy i Podzespoly oraz rysunki i obiekty czyli wszystko z wyjatkiem serii i detali	*/
		DELETE 
		FROM `pr_tob_tree_down`
		WHERE
			(Def_Obj IN (3, -2))	/*	USUWAMY Detale i nie serie	*/
		;	
	END IF;
	IF _Def_Obj = 3 THEN
	/*	Tylko Detale i Serie	*/
		DELETE 
		FROM `pr_tob_tree_down`
		WHERE
			NOT (Def_Obj IN (3, -2))	/*	USUWAMY NIE detale i NIE serie	*/
		;	
	END IF;
	
/*	Zbuduj tabele `zm` dla tob-ow ale BEZ KaPr (commision_guid = '' AND pr_krpo_GUID = '')	*/
	DROP TEMPORARY TABLE IF EXISTS `zm`;
	CREATE TEMPORARY TABLE `zm`
		SELECT 
			GUID 
		FROM `zmpo` 
		WHERE 
			`tobguid` IN (SELECT DISTINCT `guid` FROM `pr_tob_tree_down`)
			AND IFNULL(commision_guid, '') = '' 
			AND 	IFNULL(pr_krpo_GUID, '') = ''
	;
	
/*	Usun operacje z `zmpo`		*/
	DELETE top FROM `zmpo` AS top, `zm` AS top2
	WHERE
		top.GUID = top2.GUID
	;
		

END$$

DROP PROCEDURE IF EXISTS `pr_TOP_Insert_OperacjeTransportuMW`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_Insert_OperacjeTransportuMW`(IN _Case_GUID VARCHAR(38))
BEGIN
	DECLARE _Gniazdo VARCHAR(38);
	# znajdź obiekt główny dla zlecenia _Case_GUID - do _ObiektGUID
	DELETE top
	FROM `zmpo` AS top, `pr_tob` AS tob
	WHERE
		top.`TobGUID` = tob.GUID AND
		tob.`Case_GUID` = _Case_GUID AND
		tob.`Def_Obj` = 1 AND # Rysunek
		top.`rcpoGUID` = 'rcpo_TR' AND
		top.`commision_guid` IS NULL AND
		IFNULL(top.`pr_krpo_GUID`, '') = ''
	;

	# Pobierz zestaw rysunków i w petli kursora wykonaj...
	BEGIN
		DECLARE _RysunekGUID VARCHAR(38);
		DECLARE _Masa DOUBLE DEFAULT 0;
		
		DECLARE done BOOL DEFAULT FALSE;
		DECLARE c_proc CURSOR FOR
			SELECT
				tob.GUID, tob.Ilosc_C_Total / 1000 # AS Masa_Elementow_w_Rysunku
			FROM `pr_tob` tob
			WHERE
				tob.Case_GUID = _Case_GUID AND
				tob.Def_Obj = 1;		
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		OPEN c_proc;
		loop1: LOOP
			FETCH c_proc INTO _RysunekGUID, _Masa;
			IF done THEN
				LEAVE loop1;
			END IF;
		#Petla..
			SET @IleOper = 2;
			SET @Lp = 5;
			WHILE @IleOper > 0 DO
				IF @IleOper = 2 THEN
					SET _Gniazdo = 'LEP';
				ELSE	
					SET _Gniazdo = 'RS';
				END IF;	
				INSERT INTO `zmpo`
				SET
					`GUID` = NewGUID(),
					`TobGUID` = _RysunekGUID,
					`lp_int` = @Lp,
					`lp_vc` = LPAD(lp_int, 3, '0'),
					`towrnazwa` = CONCAT('TR', '-', _Gniazdo),
					`rcpoGUID` = 'rcpo_TR',
					`areaGUID` = _Gniazdo,
					`pr_ilosc_in` = _Masa / 2,
					`pr_ilosc_mul` = 0.85,
					`pr_ilosc_add` = 0;
				SET @Lp = @Lp + 5;
				SET @IleOper = @IleOper - 1;
			END WHILE;
			# Update zmpo (operacje rcpo_TR) dla tego rysunku
			SET @I := 0;
			UPDATE `zmpo` z, `rcpo` r
			SET
				z.cenanet = r.cena_kalulacyjna,
				z.pr_ilosc_add = IFNULL(r.TPZ, 0),
				z.pr_Top_oper_def_type = r.Top_oper_def_type,
				z.towrsymb = r.towrsymb,
				z.Res_Type = r.Res_Type,
				z.jednostka = r.jednostka,
				z.pr_jednostka_in = r.pr_jednostka_in,
				z.Ilosc = @I := (z.pr_ilosc_in * z.pr_ilosc_mul * z.pr_ilosc_seria + z.pr_ilosc_add),
				z.wartnet = @I * r.cena_kalulacyjna
			WHERE
				z.TobGUID = _RysunekGUID AND
				z.rcpoGUID = 'rcpo_TR' AND
				r.GUID = z.rcpoGUID;
		#Koniec petli
		END LOOP;
		CLOSE c_proc;
	END;
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_SetTPZForTOB`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_SetTPZForTOB`(
	IN _Case_GUID VARCHAR(38),
	IN _Rysunek_GUID VARCHAR(38),
	IN _rcpoGUID VARCHAR(38),
	IN _TPZ DOUBLE
)
	CONTAINS SQL
BEGIN
	IF _Rysunek_GUID != '' THEN
		/*	do pr_tob_tree_down wstawiamy rysunek i potomkow		*/
		CALL pr_Create_Tree_Down_Tob(_Rysunek_GUID, 0);
		DROP TEMPORARY TABLE IF EXISTS `temp_top_tpz`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `temp_top_tpz`
			SELECT
				top.GUID,
				top.rcpoGUID
			FROM `pr_tob_tree_down` tob
			INNER JOIN `zmpo` top ON top.TobGUID = tob.GUID	
			WHERE
				top.rcpoGUID = _rcpoGUID
				AND
				IFNULL(top.commision_guid, '') = '' AND
				IFNULL(top.pr_krpo_GUID, '') = ''
		;
	ELSE
		/*	do temp_pr_tob_view wstawiamy toby		*/
		CALL pr_TOB_Create_Temp_Tob(_Case_GUID, FALSE);
		DROP TEMPORARY TABLE IF EXISTS `temp_top_tpz`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `temp_top_tpz`
			SELECT
				top.GUID,
				top.rcpoGUID
			FROM `temp_pr_tob_view` tob
			INNER JOIN `zmpo` top ON top.TobGUID = tob.GUID	
			WHERE
				top.rcpoGUID = _rcpoGUID
				AND
				IFNULL(top.commision_guid, '') = '' AND
				IFNULL(top.pr_krpo_GUID, '') = ''
		;	
	END IF;
	
	/*	zmiany na temp_top_tpz		*/
	UPDATE `zmpo` top, `temp_top_tpz` t
		SET
		top.`pr_ilosc_add` = _TPZ,
		top.`Ilosc` = @I := (top.pr_ilosc_in * top.pr_ilosc_mul * top.pr_ilosc_seria + top.pr_ilosc_add),
		top.`wartnet` = @I * top.cenanet
	WHERE
		top.GUID = t.GUID
	;
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_WSK_Create_`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_WSK_Create_`(
	IN _Case_GUID VARCHAR(38),
	IN _ObiektGUID VARCHAR(38),
	IN _RcpoGUID VARCHAR(38),
	IN _AreaGUID VARCHAR(38),
	IN _IloscWe DOUBLE,
	IN _Ilosc_seria DOUBLE,
	IN _LP INT,
	IN _ExtraOpis VARCHAR(50),
	IN _DELETE_OLD BOOL
)
BEGIN
	DECLARE _GUID VARCHAR(38);
	
	/*	Usuwam operacje _RcpoGUID	*/
	IF _DELETE_OLD THEN
		DELETE top
		FROM `zmpo` AS top, `pr_tob` AS tob
		WHERE
			top.`TobGUID` = tob.GUID AND
			tob.`Case_GUID` = _Case_GUID AND
			top.`rcpoGUID` = _RcpoGUID AND
			top.`commision_guid` IS NULL AND
			IFNULL(top.`pr_krpo_GUID`, '') = ''
		;
	END IF;		

	/* 	znajdź obiekt główny dla zlecenia _Case_GUID - do _ObiektGUID	*/
	IF _ObiektGUID = '' THEN
		SET _ObiektGUID = pr_GetPersistantTOB_ForCase_GUID(_Case_GUID, 8, FALSE);
	END IF;
	
	/* 	znajdź najwyższe LP ale mniejsze od 100	*/
	IF _LP <= 0 THEN
		SELECT
			MAX(top.`lp_int`)
		INTO	
			_LP
		FROM `zmpo` AS top
		WHERE
			top.`TobGUID` = _ObiektGUID AND
			top.`lp_int` < 90;
			
		SET _LP = IFNULL(_LP, 0) + 5;
	END IF;		

	/* 	INSERT	*/
	SET _GUID = NewGUID();
	INSERT INTO `zmpo`
	SET
		`GUID` = _GUID,
		`TobGUID` = _ObiektGUID,
		`Rodzaj` = 73,
		`lp_int` = _LP,
		`lp_vc` = LPAD(lp_int, 3, '0'),
		`towrnazwa` = '',
		`rcpoGUID` = _RcpoGUID,
		`areaGUID` = _AreaGUID,
		`pr_ilosc_seria` = IF(_Ilosc_seria > 1, _Ilosc_seria, 1),
		`pr_ilosc_in` = _IloscWe,
		`pr_ilosc_mul` = 1,
		`pr_ilosc_add` = 0;
		
	/* 	UPDATE z rcpo	*/
	UPDATE `zmpo` z, `rcpo` r
	SET
		z.`cenanet` = r.cena_kalulacyjna,
		z.`pr_ilosc_add` = IFNULL(r.TPZ, 0),
		z.`pr_ilosc_mul` = r.Ilosc,
		z.`pr_Top_oper_def_type` = r.Top_oper_def_type,
		z.`IdnTowr` = r.IdnTowr,
		z.`towrsymb` = r.towrsymb,
		z.`Res_Type` = r.Res_Type,
		z.`areaGUID` = IF(_AreaGUID != '', _AreaGUID, r.areaGUID),
		z.`jednostka` = r.jednostka,
		z.`pr_jednostka_in` = r.pr_jednostka_in,
		z.`Ilosc` = @I := (z.pr_ilosc_in * z.pr_ilosc_mul * z.pr_ilosc_seria + z.pr_ilosc_add),
		z.`wartnet` = @I * r.cena_kalulacyjna
	WHERE
		z.GUID = _GUID AND
		r.GUID = z.rcpoGUID;
		
	/* 	UPDATE z TowrNg	*/
	UPDATE `zmpo` z, `TowrNg` t
	SET
		`towrnazwa` = IF(_ExtraOpis != '', CONCAT(t.Nazwa, ' ', _ExtraOpis), t.Nazwa)
	WHERE
		z.GUID = _GUID AND
		t.GUID = z.IdnTowr;
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_WSK_Create_MH_MR`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_WSK_Create_MH_MR`(IN _Case_GUID VARCHAR(38))
BEGIN
	DECLARE _ObiektGUID VARCHAR(38);
	DECLARE _ilosc_warstw_curr INT DEFAULT 0;
	DECLARE _LP INTEGER DEFAULT 0;
	DECLARE _LP_Int INTEGER DEFAULT 0;
	DECLARE _POW DOUBLE DEFAULT 0;
	
	/*	znajdź obiekt główny dla zlecenia _Case_GUID - do _ObiektGUID	*/
	SET _ObiektGUID = pr_GetPersistantTOB_ForCase_GUID(_Case_GUID, 8, FALSE);
	
	/*	sprawdź czy już istnieją?	*/
	DELETE FROM `zmpo`
	WHERE 
		`rcpoGUID` = 'rcpo_MH_MR' AND 
		`TobGUID` = _ObiektGUID AND 
		`commision_guid` IS NULL AND
		IFNULL(`pr_krpo_GUID`, '') = ''
	;
	
	/*	ustal ilość warstw - @IlePokryc	*/
	SELECT
		IFNULL(`ilosc_warstw`, 0)
	INTO
		@ilosc_warstw
	FROM `pr_tob`
	WHERE
		`GUID` = _ObiektGUID;
		
	/*	ustal ilość warstw - @IlePokryc	*/
	SELECT
		COUNT(*)
	INTO
		_ilosc_warstw_curr
	FROM `zmpo`
	WHERE
		`rcpoGUID` = 'rcpo_MH_MR' AND `TobGUID` = _ObiektGUID;
	SET @IlePokryc = @ilosc_warstw - _ilosc_warstw_curr;
	
	/*	 To ponizej nie ma juz sensu!!!!!!!!!!!!!!!!!! Bo Delete	*/
	SELECT
		SUM(IF(ISNULL(`ilosc_warstw`), 1, 0) * `ilosc_seria` * IF(`pow_pokr` = 0, `pow_pokr_agg`, `pow_pokr`) * pow_pokr_proc)
	INTO
		_POW
	FROM `pr_tob`
	WHERE 
		`Case_GUID` = _Case_GUID AND 
		`Def_Obj` = 2
	;
	
	/*	Pętla For I := 1 to @IlePokryc do ...
		Inicjuj LP top-ów
	*/	
	SET @LP_Int = 95;
	UPDATE `zmpo`
		SET `lp_int` = @LP_Int := @LP_Int + 5
		WHERE `rcpoGUID` = 'rcpo_MH_MR' AND `TobGUID` = _ObiektGUID
		ORDER BY `idn`;
	SET _LP = 1;
	SET _LP_Int = @LP_Int;
	/*	Pętla		*/
	WHILE @IlePokryc > 0 DO
		# Zwiększ LP top-ów
		SET _LP_Int = _LP_Int + 5;
		# Wstaw kolejny
		CALL pr_TOP_WSK_Create_(_Case_GUID, '', 'rcpo_MH_MR', '', _POW, 1, _LP_Int, CONCAT(_LP, ' Warstwa'), FALSE);
		SET _LP = _LP + 1;
		SET @IlePokryc = @IlePokryc - 1;
	END WHILE;
	
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_WSK_Create_RC`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_WSK_Create_RC`(IN _Case_GUID VARCHAR(38))
BEGIN
	DECLARE _LP INTEGER DEFAULT 0;
	/*	Usuwam operacje RC	*/
	DELETE top
	FROM `zmpo` AS top, `pr_tob` AS tob
	WHERE
		top.`TobGUID` = tob.GUID AND
		tob.`Case_GUID` = _Case_GUID AND
		top.`rcpoGUID` = 'rcpo_RC3' AND
		tob.`Def_Obj` IN (2, 8) AND # Element lub obiekt
		top.`commision_guid` IS NULL AND
			IFNULL(top.`pr_krpo_GUID`, '') = ''
	;
		
	CALL pr_TOP_Create_Temp_Top(_Case_GUID, FALSE, FALSE);
	BEGIN
		DECLARE _SZT DOUBLE DEFAULT 0;
		DECLARE _Ilosc_do_cechowania DOUBLE DEFAULT 0;
		DECLARE _Element_GUID VARCHAR(38);
		DECLARE _GUID VARCHAR(38);
		DECLARE _Symbol VARCHAR(50);
		DECLARE done BOOL DEFAULT FALSE;
		DECLARE c_proc CURSOR FOR
			SELECT `GUID`, `Ilosc`, `Ilosc_do_cechowania`, `Symbol` FROM `temp_pr_tob_view` WHERE `Def_Obj` = 2;
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		SET _LP = 200;
		OPEN c_proc;
		loop1: LOOP
			FETCH c_proc INTO
				_Element_GUID, _SZT, _Ilosc_do_cechowania, _Symbol;
			IF done THEN
				LEAVE loop1;
			END IF;
			/* Rozpoczynam */
			SET _GUID = NewGUID();
			IF _Ilosc_do_cechowania < 0 THEN
				SET _Ilosc_do_cechowania = _SZT;
			END IF;
			/* 	INSERT	*/
			IF _Ilosc_do_cechowania > 0 THEN
				CALL pr_TOP_WSK_Create_(_Case_GUID, '', 'rcpo_RC3', '', _Ilosc_do_cechowania, 1, _LP, _Symbol, FALSE);
				SET _LP = _LP + 5;
			END IF;
		END LOOP;
		CLOSE c_proc;
	END;
END$$

DROP PROCEDURE IF EXISTS `pr_TOP_WSK_Create_TR`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_TOP_WSK_Create_TR`(IN _Case_GUID VARCHAR(38))
BEGIN
	DECLARE _IdGniazdaLEP VARCHAR(38) DEFAULT 'IdLEP';
	DECLARE _IdGniazdaRS VARCHAR(38) DEFAULT 'IdRS';
	DECLARE _Gniazdo VARCHAR(38);
	DECLARE _LP INTEGER DEFAULT 0;
	DECLARE _IleOper INTEGER DEFAULT 0;
	/*	znajdź obiekt główny dla zlecenia _Case_GUID - do _ObiektGUID	*/
	DELETE top
	FROM `zmpo` AS top, `pr_tob` AS tob
	WHERE
		top.`TobGUID` = tob.GUID AND
		tob.`Case_GUID` = _Case_GUID AND
		tob.`Def_Obj` = 1 AND # Rysunek
		top.`rcpoGUID` = 'rcpo_TR' AND
		top.`commision_guid` IS NULL AND
		IFNULL(top.`pr_krpo_GUID`, '') = ''
	;

	/*	Pobierz zestaw rysunków i w petli kursora wykonaj...	*/
	BEGIN
		DECLARE _RysunekGUID VARCHAR(38);
		DECLARE _Masa DOUBLE DEFAULT 0;
		
		DECLARE done BOOL DEFAULT FALSE;
		DECLARE c_proc CURSOR FOR
			SELECT
				tob.GUID, tob.Ilosc_C_Total / 1000 # AS Masa_Elementow_w_Rysunku
			FROM `pr_tob` tob
			WHERE
				tob.Case_GUID = _Case_GUID AND
				tob.Def_Obj = 1
			;		
		DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		OPEN c_proc;
		loop1: LOOP
			FETCH c_proc INTO _RysunekGUID, _Masa;
			IF done THEN
				LEAVE loop1;
			END IF;
		#Petla..
			SET _IleOper = 2;
			SET _LP = 5;
			WHILE _IleOper > 0 DO
				IF _IleOper = 2 THEN
					SET _Gniazdo = _IdGniazdaLEP;	#'LEP';
				ELSE	
					SET _Gniazdo = _IdGniazdaRS;	#'RS';
				END IF;	
				CALL pr_TOP_WSK_Create_(_Case_GUID, _RysunekGUID, 'rcpo_TR', _Gniazdo, _Masa / 2, 1, _LP, CONCAT('TR', '-', _Gniazdo), FALSE);
				SET _LP = _LP + 5;
				SET _IleOper = _IleOper - 1;
			END WHILE;
			
		#Koniec petli
		END LOOP;
		CLOSE c_proc;
	END;
END$$

DROP PROCEDURE IF EXISTS `pr_ZlecWToku_Update`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_ZlecWToku_Update`(
	IN _MODE INT,
	IN _PodZlec_GUID VARCHAR(38),
	IN _Div_GUID VARCHAR(38),
	IN _DateClose DATE,
	IN _DateUp DATE
	)
	CONTAINS SQL
	COMMENT 'Ustawia statusy w `pr_tob` i `zmpo`'
BEGIN
	/*
	_MODE
		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 		- WYSTEPUJE w PAS
				10 z data _DateClose
				11 z data obliczana jako nast. m-c
		12		Odblokuj (otworz zlecenie) 								- WYSTEPUJE w PAS
		20, 21	Range for `crm_div_links`
		
	*/	

	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';

	IF _MODE = 0 THEN
--	Tworzy rekord dla _PodZlec_GUID/_Div_GUID
	--	Kreowanie temp `t_zlec_wydzialy` z zleceniami i wydzialami
		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 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 IGNORE INTO `crm_div_links`
			(IDN, commisionGUID, divGUID, Status, Date_crm)
			SELECT
				IDN, commisionGUID, divGUID, Status, Date_crm
			FROM `t_zlec_wydzialy`
		;
	-- na chwile		DROP TEMPORARY TABLE IF EXISTS `t_zlec_wydzialy`; 
	END IF;	--	IF _MODE = 0
		
	IF (_MODE = 1) AND (_PodZlec_GUID != '*') AND (_Div_GUID != '*') THEN
--	Nalicza rekord _PodZlec_GUID/_Div_GUID - pola czasow etc.
	--	Wstaw rekord o ile nie istnieje	
		IF (_PodZlec_GUID != '*') AND (_Div_GUID != '*') THEN
			CALL pr_ZlecWToku_Update(0, _PodZlec_GUID, _Div_GUID, _DateClose, NULL);
		END IF;
	--	Nalicz wartosci zlecenia dla tej pary _PodZlec_GUID/_Div_GUID	
		
	--	Ustal MIn i Max Daty z crm - beda potrzebne przy nullach
		SELECT
			crm.StartTime, crm.EndTime
		INTO	
			_crm_StartTime, _crm_EndTime
		FROM crm_commisions crm 
		WHERE
			crm.GUID = _PodZlec_GUID 
		;
	--	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
			WHERE
				dl.commisionGUID = _PodZlec_GUID AND
				ean.Dzial = _Div_GUID AND
				dl.divGUID = _Div_GUID 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
		;
		
	--	Agregacja z Przekazow
		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 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 = _Div_GUID 
			WHERE
				dl.commisionGUID = _PodZlec_GUID AND
				pmw.DivisionGUIDFrom = _Div_GUID AND
				dl.divGUID = _Div_GUID 
			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
		;
		
	--	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
			WHERE
				dl.commisionGUID = _PodZlec_GUID AND
				rap.GUID_division = _Div_GUID AND
				dl.divGUID = _Div_GUID 
			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
		;
		
	--	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)
		WHERE
			dl.commisionGUID = _PodZlec_GUID AND 
			dl.divGUID = _Div_GUID
		;
	END IF;	--	IF _MODE = 1
	
	
	IF _MODE = 2 THEN
	--	Nalicz i SELECT
		CALL pr_ZlecWToku_Update(1, _PodZlec_GUID, _Div_GUID, _DateClose, NULL);
		SELECT * 
		FROM `crm_div_links`
		;
	
	END IF;	--	IF _MODE = 2
	
	
	IF _MODE IN (3,4) THEN
	--	Update zlecen w `crm_div_links`
		SET @Iter = 0;
		CALL pr_ZlecWToku_Update(20, _PodZlec_GUID, _Div_GUID, _DateClose, _DateUp);
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _LOCAL_zlec_GUID VARCHAR(38);
			DECLARE _LOCAL_Div_GUID VARCHAR(38);
			DECLARE c_proc CURSOR FOR
				SELECT
					DISTINCT zl.commisionGUID AS GUID, zl.DivGUID
				FROM `t_Range_zlec` zl
				WHERE
					IF(_MODE = 3, zl.Date_Start IS NULL, TRUE)
				LIMIT 2500	
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loop1: LOOP
				FETCH c_proc INTO _LOCAL_zlec_GUID, _LOCAL_Div_GUID;
				IF done THEN
					LEAVE loop1;
				END IF;
				SET @Iter = @Iter + 1;
				CALL pr_ZlecWToku_Update(1, _LOCAL_zlec_GUID, _LOCAL_Div_GUID, _DateClose, NULL);
			END LOOP;
			CLOSE c_proc;
		END;
		SELECT @Iter;
	END IF;	--	IF _MODE = 3
	
	IF _MODE IN (10,11) THEN
--	Zamknij zlecenie _PodZlec_GUID dla wydzialu _Div_GUID 
		CALL pr_ZlecWToku_Update(20, _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 = 10 11
	

	IF _MODE = 12 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 = 12
	
	IF _MODE IN (20,21) THEN
	--	Range for `crm_div_links`
	
		DROP TEMPORARY TABLE IF EXISTS `t_Range_zlec`; 
		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)
		;
	END IF;	--	IF _MODE = 10 11
	
END$$



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

	IF _MODE = 7 THEN
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _LOCAL_zlecUpdate_GUID VARCHAR(38);
			DECLARE _LOCAL_DivUpdate_GUID VARCHAR(38);
			DECLARE c_proc CURSOR FOR
				SELECT
				rap.podzlecGUID, rap.GUID_division
				FROM `akord_drp_ng` rap
				INNER JOIN `crm_div_links` zl ON zl.divGUID = rap.GUID_division AND zl.commisionGUID = rap.podzlecGUID
				WHERE
				zl.aLocalTime < rap.data_wykonania
				GROUP BY rap.podzlecGUID, rap.GUID_division
				;			
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loop1: LOOP
				FETCH c_proc INTO _LOCAL_zlecUpdate_GUID, _LOCAL_DivUpdate_GUID;
				IF done THEN
					LEAVE loop1;
				END IF;
				CALL pr_ZlecWToku_Update(1, _LOCAL_zlecUpdate_GUID, _LOCAL_DivUpdate_GUID, NULL);
			END LOOP;
			CLOSE c_proc;
		END;
	END IF;	--	IF _MODE = 7
*/

DELIMITER ;