 DELIMITER $$

DROP PROCEDURE IF EXISTS `pr_planprodxx_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_planprodxx_tools`(
	IN _MODE VARCHAR(150),
	IN _Key VARCHAR(38),	
	IN _Ilosc DECIMAL(20,6), 
	IN _Param_A VARCHAR(250),	
	IN _Param_B VARCHAR(250),	
	IN _Param_C VARCHAR(250),	
	IN _Param_D VARCHAR(250)	
)
S_pr_planprodxx_tools: BEGIN
	DECLARE _RcNg_GUID, _IdnNg, _pGUID, _IdnTowr_RC, _IdnTowr, _AreaGUID VARCHAR(38) DEFAULT '';
	DECLARE _Ilosc_RC DECIMAL(20, 6) DEFAULT 0;
	DECLARE _SQLText MEDIUMTEXT;

				IF _MODE = 'Before.Create.NEW.planprodng' THEN
				/* *************************************************************************************************************************************************************
					CALL pr_planprodxx_tools('Before.Create.NEW.planprodng', '', 0, '2015-04-03', 'tydzien', '', '');		
					_Param_A	terminrealizacji
					_Param_B	tydzien
				**************************************************************************************************************************************************************/
					IF _Param_B != '' THEN
					--	czyli numer tygodnia
						SET
						@w.CurDayofWeek = WEEKDAY(CURRENT_DATE()),
						@w.DestDayofWeek = 0, -- pon 3, -- czw
						@w.DestWeekOfYear = _VarInt(_Param_B)	--	docelowy week
						;
						SET
						@w.DestDate = DATE_ADD(CURRENT_DATE(), INTERVAL @w.DestDayofWeek - @w.CurDayofWeek DAY)
						;
						SET
						@w.CurWeekOfYear = WEEKOFYEAR(@w.DestDate)
						;
						SET
						@w.FinalDate = DATE_ADD(@w.DestDate, INTERVAL @w.DestWeekOfYear - @w.CurWeekOfYear WEEK)
						;
						SET @w.TerminRealizacji = DATE_ADD(@w.FinalDate, INTERVAL 7 DAY);
					ELSE
						SET @w.TerminRealizacji = DATE(_Param_A);
						SET @w.FinalDate = DATE_ADD(@w.TerminRealizacji, INTERVAL -7 DAY);
					END IF;
				
				END IF;

	IF _MODE LIKE 'Create.NEW.planprodng%' THEN
	/* *************************************************************************************************************************************************************
		A)	'Create.NEW.planprodng'
		Kreowanie zwyczajne
		CALL pr_planprodxx_tools('Create.NEW.planprodng', 'IdnNg', 0, '2015-04-03', 'tydzien', 'AreaGUID', '');		
		_Key 	jest _IdnNg, czyli GUID w `planprodng`
		_Param_A	terminrealizacji
		_Param_B	tydzien
		_Param_C	AreaGUID
		B) 'Create.NEW.planprodng.z.109.0'
		CALL pr_planprodxx_tools('Create.NEW.planprodng.z.109.0', 'IdnNg', 0, '2015-04-03', 'tydzien', '11658ff0edde76eb22f1bc7c05b2ba87', '');		
		_Key 	jest _IdnNg, czyli GUID w `planprodng`
		_Param_A	terminrealizacji
		_Param_B	tydzien
		_Param_C	GUID 109.1	planu strategicznego
		
	**************************************************************************************************************************************************************/
		CALL pr_planprodxx_tools('Before.Create.NEW.planprodng', '', 0, _Param_A, _Param_B, '', '');		
		SET _IdnNg = _Key;
		

		IF _MODE = 'Create.NEW.planprodng' THEN
			INSERT INTO `planprodng` 
			(`GUID`, `Nazwa`, `datawystaw`, `terminrealizacji`, `AreaGUID`, `Deleted`) 
			SELECT
				_IdnNg, 
				'?', -- teraz to sie dzieje w Update_Status = 'Set.Name' IFNULL(@w.Name, 'Plan tyg.'),
				CURRENT_DATE(),
				@w.TerminRealizacji,
				_Param_C,
				0
			ON DUPLICATE KEY UPDATE 
				`Deleted` = 0,
				`AreaGUID` = _Param_C,
				-- `Nazwa` = IFNULL(@w.Name, 'Plan tyg.'),
				`terminrealizacji` = @w.TerminRealizacji
			;
		ELSEIF _MODE = 'Create.NEW.planprodng.z.109.0' THEN
			INSERT INTO `planprodng` 
			(`GUID`, `Nazwa`, `datawystaw`, `terminrealizacji`, `AreaGUID`, `Deleted`) 
			SELECT
				_IdnNg, 
				'?', -- teraz to sie dzieje w Update_Status = 'Set.Name' IFNULL(@w.Name, 'Plan tyg.'),
				CURRENT_DATE(),
				@w.TerminRealizacji,
				n.AreaGUID,
				1
			FROM `zmng` n
			WHERE
				n.GUID = _Param_C
			ON DUPLICATE KEY UPDATE 
				`Deleted` = 1,
				-- `Nazwa` = IFNULL(@w.Name, 'Plan tyg.'),
				`terminrealizacji` = @w.TerminRealizacji
			;
		END IF;
		
		UPDATE `planprodng` n SET n.Update_Status = 'Set.Name' WHERE n.GUID = _IdnNg;
		
		
	END IF; -- IF _MODE = 'Create.NEW.planprodng' THEN
	
	IF _MODE = 'Update.Status_Realizacji' THEN
	/* *************************************************************************************************************************************************************
		A)	Update wszystkie plany ktorych pozycje maja w polu Status_Realizacji = -1
		CALL pr_planprodxx_tools('Update.Status_Realizacji', '', 0, '', '', '', '');		
		B)	Update wszystkie pozycje planu _IdnNg
		Przelicz 
		1. `planprodpo` pole Status_Realizacji - robimy to wstawiajac do Update_Status = 'Set.Status_Realizacji'
		2. to samo w `planprodng` pole Status_Realizacji - robimy to wstawiajac do Update_Status = 'Set.Status_Realizacji'
		CALL pr_planprodxx_tools('Update.Status_Realizacji', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 0, '', '', '', '');		
		W _Key 	jest _IdnNg, czyli GUID w `planprodng`
	**************************************************************************************************************************************************************/
		SET _IdnNg = IFNULL(_Key, '');
		IF _IdnNg = '' THEN
		--	Update wszystkie plany ktorych pozycje maja w polu Status_Realizacji = -1
		--	SHOW CREATE TABLE `t_planprod_for_update`;
			DROP TEMPORARY TABLE IF EXISTS `t_planprod_for_update`;
			CREATE TEMPORARY TABLE `t_planprod_for_update` (
				`GUID` VARCHAR(38) NOT NULL,
				`IdnNg` VARCHAR(38) NOT NULL,
				PRIMARY KEY (`GUID`),
				KEY `IdnNg` (`IdnNg`)
			)DEFAULT CHARSET=cp1250
			;
			INSERT IGNORE INTO `t_planprod_for_update`
			SELECT
				p.GUID, p.IdnNg
			FROM `planprodpo` p
			WHERE
				p.Status_Realizacji = -1
			;
			UPDATE `planprodpo` p, `t_planprod_for_update` t
			SET
				p.Update_Status = 'Set.Status_Realizacji'
			WHERE
				p.GUID = t.GUID
			;
			UPDATE `planprodng` n, `t_planprod_for_update` t
			SET
				n.Update_Status = 'Set.Status_Realizacji'
			WHERE
				n.GUID = t.IdnNg
			;
			DROP TEMPORARY TABLE IF EXISTS `t_planprod_for_update`;
		ELSE
		--	Update wszystkie pozycje planu _IdnNg
			UPDATE `planprodpo` p
			SET
				p.Update_Status = 'Set.Status_Realizacji'
			WHERE
				p.IdnNg = _IdnNg
			;
			UPDATE `planprodng` n
			SET
				n.Update_Status = 'Set.Status_Realizacji'
			WHERE
				n.GUID = _IdnNg
			;
		END IF;
	END IF;	--	_MODE = 'Update.Status_Realizacji' 


	IF _MODE LIKE 'select.%' THEN
	
				IF _MODE = 'select.receptury.for.IdnTowr' THEN
				/* *************************************************************************************************************************************************************
					Zwraca receptury dla danego asortymentu
					CALL pr_rcpo_calc_tools('select.receptury.for.IdnTowr', '{E867730E-DAD6-4BA7-8991-CCEB93285DEB}', 0, '', '', '', '');		
					lub dowolna recepture
					CALL pr_rcpo_calc_tools('select.receptury.for.IdnTowr', '', 0, '', '', '', '');		
					W _Key IdnTowr
				**************************************************************************************************************************************************************/
					SET _IdnTowr = IFNULL(_Key, '');
					IF _IdnTowr != '' THEN
						SELECT 
							rn.GUID, CONCAT(rn.Nazwa, ' - ', t.Symbol) AS Nazwa, rn.data_receptury, rn.IDN,
							t.Nazwa AS TowarStr
						-- ,rp.*
						FROM `rcpo` rp
						INNER JOIN `rcng` rn ON rn.GUID = rp.idnng 
						INNER JOIN `TowrNg` t ON t.GUID = rp.IdnTowr
						WHERE
							rp.IdnTowr = _IdnTowr
							AND rp.przychod = 1
							AND rp.priorytet = -1
							AND rn.widoczna_w_zam
							AND rn.nieaktywna = 0
						;
					ELSE
						SELECT 
							rn.GUID, CONCAT(rn.Nazwa, ' - ', t.Symbol) AS Nazwa, rn.data_receptury, rn.IDN,
							t.Nazwa AS TowarStr
						-- ,rp.*
						FROM `rcpo` rp
						INNER JOIN `rcng` rn ON rn.GUID = rp.idnng 
						INNER JOIN `TowrNg` t ON t.GUID = rp.IdnTowr
						WHERE
							TRUE
							AND rp.przychod = 1
							AND rp.priorytet = -1
							AND rn.widoczna_w_zam
							AND rn.nieaktywna = 0
						GROUP BY rn.GUID
						;
					END IF;
				END IF;	--	IF _MODE = 'select.receptury.for.IdnTowr' 
				
				IF _MODE = 'select.areanodes.for.UgrpGUID' THEN
				/* *************************************************************************************************************************************************************
					Zwraca miejsca/lokalzacje czyli fabryki dla  dla danego Ownera
					CALL pr_rcpo_calc_tools('select.areanodes.for.UgrpGUID', 'ALL', 0, '', '', '', '');		
					W _Key UserGUID
				**************************************************************************************************************************************************************/
					SELECT 
						a.* 
					FROM `erp_area_nodes` a
					WHERE
						a.Type ='PROD'
						AND
						EXISTS(SELECT grp.osobaGUID FROM `grupy_osob` grp WHERE grp.grupaGUID = a.ugrpGUID AND grp.osobaGUID = _Key)
					;
				END IF;	--	IF _MODE = 'select.areanodes.for.UgrpGUID' 
	
				IF _MODE = 'select.planprod.ng' THEN
				/* *************************************************************************************************************************************************************
					Zwraca planery
					CALL pr_planprodxx_tools('select.planprod.ng', 'AreaGUID', 0, '', '', '', '');		--	biezace
					CALL pr_planprodxx_tools('select.planprod.ng', 'AreaGUID', 0, '1', '', '', '');		--	zrealizowane
					_Key 		AreaGUID plannera czyli fabryka
					_Param_A	gdy '' to biezace a gdy != '' to zrealizowane 
				**************************************************************************************************************************************************************/
					CALL pr_planprodxx_tools('Update.Status_Realizacji', '', 0, '', '', '', '');		
					SET _AreaGUID = _Key;
					SELECT 
						pn.*, 
						IF(COUNT(pp.GUID) = 0,
							'brak',
							CONCAT(
								'', COUNT(pp.GUID), ' prod. w sumie ',
								-- SUM(TIMESTAMPDIFF(HOUR, pp.Plan_StartTime, Plan_EndTime)),
								CEIL(SUM(TIMESTAMPDIFF(MINUTE, pp.Plan_StartTime, Plan_EndTime))/60), 
								'h od ',
								IF(YEAR(MIN(pp.Plan_StartTime)) != YEAR(MAX(pp.Plan_StartTime)),
									_Day_Month_Year_Fmt(MIN(pp.Plan_StartTime), 'D MR YYYY'),
									_Day_Month_Year_Fmt(MIN(pp.Plan_StartTime), 'D MR')
								),
								' do ',
								_Day_Month_Year_Fmt(MAX(pp.Plan_EndTime), 'DD MR YYYY')
							)
						) AS OpisDetal
					FROM `planprodng` pn
					LEFT JOIN `planprodpo` pp ON pp.IdnNg = pn.GUID
					WHERE 
						pn.AreaGUID = _AreaGUID
						-- AND pn.Deleted = 0
						AND IF(_Param_A = '', pn.Status_Realizacji < 1,  pn.Status_Realizacji = 1)
					GROUP BY pn.GUID
					;

					-- SET _pGUID = _Key;
				END IF;	--	IF _MODE = 'select.planprod.ng'
		
				IF _MODE LIKE 'select.planprod.po%' THEN
				/* *************************************************************************************************************************************************************
					Zwraca pozycje planera
					CALL pr_planprodxx_tools('select.planprod.po', 'IdnNg', 0, '', '', '', '');		
					W _Key GUID plannera czyli IdnNg w `planprodpo`
					ALBO					CALL pr_planprodxx_tools('select.planprod.po.calendar', 'AreaGUID', 0, '2014-10-24', '', '', '');	7 dni 
					CALL pr_planprodxx_tools('select.planprod.po.calendar', 'AreaGUID', 0, '2014-10-24', '1', '', '');	1 dzien
					W _Key AreaGUID plannera czyli fabryka
					W _Param_A data OD
					W _Param_B ile dni domyslnie 7
					ALBO
					CALL pr_planprodxx_tools('select.planprod.po.cube', '', 0, '2014-10-01', '2014-10-30', '', '');
					W _Param_A data OD
					W _Param_B data DO
					ALBO
					CALL pr_planprodxx_tools('select.planprod.po.zuzycie', 'AreaGUID', 0, '2014-10-01', '2014-10-30', '', '');
					W _Param_A data OD
					W _Param_B data DO
				**************************************************************************************************************************************************************/
					DROP TEMPORARY TABLE IF EXISTS `t_planprod_present`;
					SET _SQLText = 
					"
					CREATE TEMPORARY TABLE `t_planprod_present`
						SELECT 
							pp.*,
							rn.Nazwa AS RecepturaNazwa,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = '0..1'
							), 0) AS Ilosc_0_1,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Electr'
							), 0) AS Ilosc_Electr,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Gas'
							), 0) AS Ilosc_Gas,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Time'
							), 0) AS Ilosc_Time,
							IFNULL((
								SELECT _DisplayDurationFormat(IFNULL(SUM(r.Ilosc), 0)/60) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Time'
							), 0) AS Ilosc_Time_str,

							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = '0..1_Const'
							), 0) AS Ilosc_0_1_Const,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Electr_Const'
							), 0) AS Ilosc_Electr_Const,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Gas_Const'
							), 0) AS Ilosc_Gas_Const,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Time_Const'
							), 0) AS Ilosc_Time_Const,

							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Time' AND r.Time_Res = 'BEGIN'
							), 0) AS Ilosc_Time_BEGIN,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Time' AND r.Time_Res = 'DURING'
							), 0) AS Ilosc_Time_DURING,
							IFNULL((
								SELECT IFNULL(SUM(r.Ilosc), 0) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type = 'Time' AND r.Time_Res = 'END'
							), 0) AS Ilosc_Time_END,
							
							-- ,pp.Plan_EndTime AS Plan_EndTime
							_VarInt(0) AS IsOverLap,
							(IFNULL(pp.GuidZleceniaPo, '') != '') AS ZlecExists,
							IFNULL(zmn.nrzamw, '') AS ZlecNr,
							-- _VarInt(IFNULL(IF(zmn.ilepoz = zmn.ilepozzreal, 100, IF(zmn.ilepozzreal = 0, 0, zmn.ilepozzreal/zmn.ilepoz)), 0)) AS ZlecStatus,
							IFNULL(zm.Ilosc = zm.zrealizowano, 0) AS ZlecZrealizowano,
							IFNULL((
								SELECT
									IF(SUM(p.ilosc - p.zrealizowano) = SUM(LEAST(tp.ILosc , p.ilosc - p.zrealizowano)),
										'Gotowe w calosci do realiz.',
										IF(SUM(LEAST(tp.ILosc , p.ilosc - p.zrealizowano)) > 0,
											'Czesciowo gotowe do realiz.',
											'Brak towaru do realiz.'
										)
									) AS Stan
								FROM `zmpo` p
								LEFT JOIN `TowrPo` tp ON tp.Magazyn = p.IdnMagazyn AND tp.IdnTowr = p.IdnTowr
								WHERE
									p.IdnNg = zm.IdnNg
									AND  p.magtyp = 2
							), '') AS Status_Gotowa_Do_Realizacji,
							IFNULL(zmn.GUID, '') AS GUID_zlec,
							IFNULL(
								(
								SELECT 
									SUM(rp.Ilosc) AS Ilosc
								FROM `oppo` rp
								WHERE
									rp.GUIDZm = zm.GUID
								), 0
							) AS ilosc_zreal

						FROM `planprodpo` pp 
						LEFT JOIN `rcng` rn ON rn.GUID = pp.RecepturaGUID
						#LEFT JOIN `planprodng` n ON n.GUID = pp.IdnNg#
						LEFT JOIN `zmpo` zm ON zm.GUID = pp.GuidZleceniaPo
						LEFT JOIN `zmng` zmn ON zmn.GUID = zm.IdnNg
						WHERE 
							#WHERE#
						ORDER BY Plan_StartTime
					;
					";
					
					
					IF _MODE LIKE 'select.planprod.po' THEN
						CALL pr_planprodxx_tools('Update.Status_Realizacji', _Key, 0, '', '', '', '');	-- przed odswiezeniem
						SET _IdnNg = _Key;
						SET _SQLText = REPLACE(_SQLText, '#WHERE#', "pp.IdnNg = #_IdnNg#");
						SET _SQLText = REPLACE(_SQLText, '#_IdnNg#', QUOTE(_IdnNg));
						SET _SQLText = REPLACE(_SQLText, '#LEFT JOIN `planprodng` n ON n.GUID = pp.IdnNg#', '');
						SET @_SQLText = _SQLText;
						CALL _ExecuteCmd(_SQLText, @Error);
					END IF;
					
					IF _MODE LIKE 'select.planprod.po.calendar' THEN
						IF _Param_D = '' THEN
							SET _AreaGUID = _Key;
						ELSE
							SET _IdnNg = _Param_D;
						END IF;
						-- SELECT n.AreaGUID FROM `planprodng` n WHERE n.GUID = _IdnNg INTO @AreaGUID;
						IF IFNULL(_Param_B, '') = '' THEN
							SET _Param_B = 7;
						END IF;
						SET @DataOd = _varDATETIME(_Param_A);
						SET @DataDo = _varDATETIME(DATE_ADD(@DataOd, INTERVAL _Param_B DAY));
						SET @DataOd = QUOTE(_VarStr(@DataOd));
						SET @DataDo = QUOTE(_VarStr(@DataDo));
						
						SET _SQLText = REPLACE(_SQLText, '#LEFT JOIN `planprodng` n ON n.GUID = pp.IdnNg#', "LEFT JOIN `planprodng` n ON n.GUID = pp.IdnNg");
						-- bylo SET _SQLText = REPLACE(_SQLText, '#WHERE#', "n.AreaGUID = #AreaGUID# AND  pp.Plan_StartTime BETWEEN #DateOd# AND #DateDo#");
						IF _Param_D = '' THEN
							SET _AreaGUID = QUOTE(_VarStr(_AreaGUID));
							SET _SQLText = REPLACE(_SQLText, '#WHERE#', "n.AreaGUID = #AreaGUID# AND  pp.Plan_EndTime >= #DateOd# AND pp.Plan_StartTime <= #DateDo#");
							SET _SQLText = REPLACE(_SQLText, '#AreaGUID#', _AreaGUID);
						ELSE
							SET _SQLText = REPLACE(_SQLText, '#WHERE#', "pp.IdnNg = #_IdnNg#");
							SET _SQLText = REPLACE(_SQLText, '#_IdnNg#', QUOTE(_IdnNg));
						END IF;
						SET @_SQLText = _SQLText;
						SET _SQLText = REPLACE(_SQLText, '#DateOd#', @DataOd);
						SET _SQLText = REPLACE(_SQLText, '#DateDo#', @DataDo);
						SET @_SQLText = _SQLText;
						CALL _ExecuteCmd(_SQLText, @Error);
					END IF;
					
					IF _MODE LIKE 'select.planprod.po.zuzycie%' THEN
						IF _MODE = 'select.planprod.po.zuzycieXXXX' THEN
						SET _SQLText = 
						"
						CREATE TEMPORARY TABLE `t_planprod_present`
							SELECT
								pp.TowrNazwa AS dim_RecepturaNazwa,
								-- rn.Nazwa AS dim_RecepturaNazwa,
								
								ptl.*
							FROM `planprodpo_time_line` ptl
							INNER JOIN `planprodpo` pp ON ptl.pGUID = pp.GUID
							INNER JOIN `planprodng` n ON n.GUID = pp.IdnNg
							LEFT JOIN `rcng` rn ON rn.GUID = pp.RecepturaGUID
							WHERE 
								#WHERE#
							GROUP BY ptl.pGUID, ptl.StartTime
							;
						";
						ELSE
						SET _SQLText = 
						"
						-- CREATE TEMPORARY TABLE `t_planprod_present`
						CREATE TEMPORARY TABLE `t_planprod_present_A`
							SELECT
								_VarStr(pp.TowrNazwa) AS dim_RecepturaNazwa,
								-- rn.Nazwa AS dim_RecepturaNazwa,
								IF((@CCC := COUNT(*)) > 1, 
									CONCAT(GROUP_CONCAT(ROUND(ptl.electr,2) SEPARATOR ' + '), ' = ',  ROUND(SUM(ptl.electr), 2), ''),
									CONCAT(ROUND(SUM(ptl.electr), 2), '')
								) AS electr_sum,
								
								IF((@CCC := COUNT(*)) > 1, 
									CONCAT(GROUP_CONCAT(ROUND(ptl.gas,2) SEPARATOR ' + '), ' = ',  ROUND(SUM(ptl.gas), 2), ''),
									CONCAT(ROUND(SUM(ptl.gas), 2), '')
								) AS gas_sum,
								SUM(ptl.electr) AS S_electr, 
								SUM(ptl.gas) AS S_gas, 
								ptl.*
							FROM `planprodpo_time_line` ptl
							INNER JOIN `planprodpo` pp ON ptl.pGUID = pp.GUID
							INNER JOIN `planprodng` n ON n.GUID = pp.IdnNg
							LEFT JOIN `rcng` rn ON rn.GUID = pp.RecepturaGUID
							WHERE 
								#WHERE#
							GROUP BY ptl.StartTime
							;
						";
						END IF;
						SET @_SQLText = _SQLText;
						
						SET _AreaGUID = _Key;
						-- SELECT n.AreaGUID FROM `planprodng` n WHERE n.GUID = _IdnNg INTO @AreaGUID;
						IF IFNULL(_Param_B, '') = '' THEN
							SET _Param_B = 7;
						END IF;
						SET @DataOd = _varDATETIME(_Param_A);
						SET @DataDo = _varDATETIME(DATE_ADD(@DataOd, INTERVAL _Param_B DAY));
						SET @DataOd = QUOTE(_VarStr(@DataOd));
						SET @DataDo = QUOTE(_VarStr(@DataDo));
						SET _AreaGUID = QUOTE(_VarStr(_AreaGUID));
						
						SET _SQLText = REPLACE(_SQLText, '#WHERE#', "n.AreaGUID = #AreaGUID# AND  ptl.StartTime BETWEEN #DateOd# AND #DateDo#");
						
						SET @_SQLText = _SQLText;
						SET _SQLText = REPLACE(_SQLText, '#DateOd#', @DataOd);
						SET _SQLText = REPLACE(_SQLText, '#DateDo#', @DataDo);
						SET _SQLText = REPLACE(_SQLText, '#AreaGUID#', _AreaGUID);
						SET @_SQLText = _SQLText;
						
						DROP TEMPORARY TABLE IF EXISTS `t_planprod_present_A`;
						CALL _ExecuteCmd(_SQLText, @Error);
						
						ALTER TABLE `t_planprod_present_A` ADD PRIMARY KEY StartTime(StartTime);
						SELECT 
							_varDATETIME(MIN(StartTime)) AS StartTime_From, _varDATETIME(MAX(StartTime)) AS StartTime_To 
						INTO
							@StartTime_From, @StartTime_To
						FROM `t_planprod_present_A`
						;
						SET 
							@StartTime_From = _varDATETIME(@StartTime_From), 
							@StartTime_To = _varDATETIME(@StartTime_To)
						;
						WHILE _varDATETIME(@StartTime_From) <= _varDATETIME(@StartTime_To) DO
							SET @StartTime_From = DATE_ADD(@StartTime_From, INTERVAL 1 HOUR);
							/* */
							INSERT IGNORE INTO `t_planprod_present_A`
							SET
								StartTime = @StartTime_From,
								`hour` = HOUR(@StartTime_From),
								electr = 10,
								S_electr = 10,
								electr_sum = 10,
								gas = 2,
								S_gas = 2,
								gas_sum = 2,
								Minutes_Norm = 60,
								Minutes_Total = 60,
								Minutes_Const = 0,
								dim_RecepturaNazwa = _VarStr('[....]')
							;
							/* */
						END WHILE;
						
						CREATE TEMPORARY TABLE `t_planprod_present` 
							SELECT * FROM `t_planprod_present_A`
						;
					END IF;
					
					IF _MODE LIKE 'select.planprod.po.cube' THEN
						SET _SQLText = 
						"
						CREATE TEMPORARY TABLE `t_planprod_present`
							SELECT 
								-- pp.*,
								pp.TowrNazwa AS dim_RecepturaNazwa,
								-- rn.Nazwa AS dim_RecepturaNazwa,
								n.AreaGUID AS dim_AreaGUID,
								DATE(pp.Plan_StartTime) AS dim_Day,
								SUM(pp.Ilosc) AS Ilosc,
								SUM(IFNULL((
									SELECT SUM(IFNULL(r.Ilosc, 0)) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type LIKE '0..1%'
								), 0)) AS Ilosc_0_1,
								SUM(IFNULL((
									SELECT SUM(IFNULL(r.Ilosc, 0)) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type LIKE 'Electr%'
								), 0)) AS Ilosc_Electr,
								SUM(IFNULL((
									SELECT SUM(IFNULL(r.Ilosc, 0)) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type LIKE 'Gas%'
								), 0)) AS Ilosc_Gas,
								SUM(IFNULL((
									SELECT SUM(IFNULL(r.Ilosc, 0)) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type LIKE 'Time%'
								), 0)) AS Ilosc_Time_stary,
								SUM(IFNULL((
									SELECT SUM(IFNULL(r.Ilosc, 0)) FROM `planprodpo_res` r WHERE r.pGUID = pp.GUID AND r.Res_Type IN ('BEGIN','DURING','END')
								), 0)) AS Ilosc_Time,
								-- ,pp.Plan_EndTime AS Plan_EndTime
								_VarInt(0) AS IsOverLap
							FROM `planprodpo` pp 
							LEFT JOIN `rcng` rn ON rn.GUID = pp.RecepturaGUID
							LEFT JOIN `planprodng` n ON n.GUID = pp.IdnNg
							INNER JOIN `erp_area_nodes` a ON a.Type ='PROD' AND EXISTS(SELECT grp.osobaGUID FROM `grupy_osob` grp WHERE grp.grupaGUID = a.ugrpGUID AND grp.osobaGUID = #_Key#)
							WHERE 
								#WHERE#
							-- group by pp.IDN	
							GROUP BY dim_RecepturaNazwa, dim_AreaGUID, dim_Day 
							;
						";
						
					
						IF IFNULL(_Param_B, '') = '' THEN
							SET _Param_B = _Param_A;
						END IF;
						SET @DataOd = _varDATETIME(_Param_A);
						SET @DataDo = _varDATETIME(_Param_B);
						IF @DataOd = @DataDo THEN
							SET @DataDo = _varDATETIME(DATE_ADD(@DataOd, INTERVAL 1 DAY));
						END IF;
						SET @DataOd = QUOTE(_VarStr(@DataOd));
						SET @DataDo = QUOTE(_VarStr(@DataDo));
						
						SET _SQLText = REPLACE(_SQLText, '#WHERE#', "pp.Plan_StartTime BETWEEN #DateOd# AND #DateDo#");
						SET @_SQLText = _SQLText;
						SET _SQLText = REPLACE(_SQLText, '#DateOd#', @DataOd);
						SET _SQLText = REPLACE(_SQLText, '#DateDo#', @DataDo);
						SET _SQLText = REPLACE(_SQLText, '#_Key#', QUOTE(_Key));
						SET @_SQLText = _SQLText;
						CALL _ExecuteCmd(_SQLText, @Error);
					END IF;
					/* Kiedys tak bylo teraz pozycje maja wlasne czasy zakoncz.
					UPDATE `t_planprod_present` pp, `planprodpo` p
					SET
						pp.Plan_EndTime = DATE_ADD(pp.Plan_StartTime, INTERVAL pp.Ilosc_Time MINUTE)
					;
					*/
				--	Teraz OverLap ALE nie dla CUBE
					IF _MODE IN ('select.planprod.po', 'select.planprod.po.calendar') THEN
					--	OverLap
						DROP TEMPORARY TABLE IF EXISTS `t_planprod_ng_overlap_search`;
						CREATE TEMPORARY TABLE `t_planprod_ng_overlap_search`
							SELECT
								n.*
							FROM `planprodng` nwe, `planprodng` n
							WHERE
								nwe.GUID = _Key -- '1111xxx'
								AND n.AreaGUID = nwe.AreaGUID 
								AND n.DataWystaw >= nwe.DataWystaw
						;					
						UPDATE `t_planprod_present` pp
						SET
							pp.IsOverLap = _VarInt(IFNULL((
								SELECT
									COUNT(*) AS EXIST_ONE
								FROM `t_planprod_ng_overlap_search` ns, `planprodpo` p
								LEFT JOIN `planprodng` n ON n.`GUID`=p.`idnNG`
								WHERE
									TRUE
									AND ns.GUID = p.idnNG
									AND p.Plan_StartTime > pp.Plan_StartTime
									AND p.GUID != pp.GUID
									AND n.Deleted = 0
									AND
									_TimeRange('conflict', pp.Plan_StartTime, pp.Plan_EndTime, p.Plan_StartTime, p.Plan_EndTime, '', '') != 0
							), 0) != 0)
						;
						DROP TEMPORARY TABLE IF EXISTS `t_planprod_ng_overlap_search`;
					END IF;
				--	SELECT	
					SELECT * FROM `t_planprod_present`;
					DROP TEMPORARY TABLE IF EXISTS `t_planprod_present`;
				END IF;	--	IF _MODE LIKE 'select.planprod.po%' 
				
				
				IF _MODE = 'select.receptury.for.IdnTowr' THEN
				/* *************************************************************************************************************************************************************
					Zwraca receptury dla danego asortymentu
					CALL pr_planprodxx_tools('select.receptury.for.IdnTowr', '{E867730E-DAD6-4BA7-8991-CCEB93285DEB}', 0, '', '', '', '');		
					lub dowolna recepture
					CALL pr_planprodxx_tools('select.receptury.for.IdnTowr', '', 0, '', '', '', '');		
					W _Key IdnTowr
				**************************************************************************************************************************************************************/
					SET _IdnTowr = IFNULL(_Key, '');
					IF _IdnTowr != '' THEN
						SELECT 
							rn.GUID, rn.Nazwa, rn.data_receptury, rn.IDN
						-- ,rp.*
						FROM `rcpo` rp
						INNER JOIN `rcng` rn ON rn.GUID = rp.idnng 
						WHERE
							rp.IdnTowr = _IdnTowr
							AND rp.przychod = 1
							AND rp.priorytet = -1
							AND rn.widoczna_w_zam
						;
					ELSE
						SELECT 
							rn.GUID, rn.Nazwa, rn.data_receptury, rn.IDN
						-- ,rp.*
						FROM `rcpo` rp
						INNER JOIN `rcng` rn ON rn.GUID = rp.idnng 
						WHERE
							TRUE
							AND rp.przychod = 1
							AND rp.priorytet = -1
							AND rn.widoczna_w_zam
						GROUP BY rn.GUID
						;
					END IF;
				END IF;	--	IF _MODE = 'select.receptury.for.IdnTowr' 
				
				IF _MODE = 'select.areanodes.for.UgrpGUID' THEN
				/* *************************************************************************************************************************************************************
					Zwraca miejsca/lokalzacje czyli fabryki dla  dla danego Ownera
					CALL pr_planprodxx_tools('select.areanodes.for.UgrpGUID', 'ALL', 0, '', '', '', '');		
					W _Key UserGUID
				**************************************************************************************************************************************************************/
					SELECT 
						a.* 
					FROM `erp_area_nodes` a
					WHERE
						a.Type ='PROD'
						AND
						EXISTS(SELECT grp.osobaGUID FROM `grupy_osob` grp WHERE grp.grupaGUID = a.ugrpGUID AND grp.osobaGUID = _Key)
					;
				END IF;	--	IF _MODE = 'select.areanodes.for.UgrpGUID' 

				
	END IF;	--	IF _MODE LIKE 'select.%' 
	
	IF _MODE = 'move.planprodpo.by.min' THEN
	/* *************************************************************************************************************************************************************
		Przesun rekord `planprodpo` o ilosc minut
		CALL pr_planprodxx_tools('move.planprodpo.by.min', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 60, '', '', '', '');		
		sam rekord
		LUB
		CALL pr_planprodxx_tools('move.planprodpo.by.min', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 60, '1', '', '', '');		'
		i pozostale za nim gdy _Param_A <> 0
			_Key pGUID, czyli pozycja czyli GUID w `planprodpo` ktory przesuwamy ALE takze gdy _Param_A <> 0  pozycja przesuwana od kt�rej starsze beda przesuwane,	
			_Ilosc 	Ile minut +/-
			_Param_A	gdy <> 0 to przesuwam wszystkie inne
	**************************************************************************************************************************************************************/
		IF _Ilosc = 0 THEN
			LEAVE S_pr_planprodxx_tools;
		END IF;
		SET _pGUID = _Key;
		UPDATE `planprodpo` p 
		SET
			p.Plan_StartTime = DATE_ADD(p.Plan_StartTime, INTERVAL _Ilosc MINUTE),
			p.Plan_EndTime = DATE_ADD(p.Plan_EndTime, INTERVAL _Ilosc MINUTE)
		WHERE
			p.GUID = _pGUID
		;
		CALL planprodpo_time_line_create_from_planprodpo('', _pGUID, '', '', '');
		IF _Param_A != '' THEN
		--	gdy <> 0 to przesuwam wszystkie inne
			SELECT p.Plan_StartTime, n.AreaGUID, p.idnNG INTO @Plan_StartTime, @AreaGUID, @idnNG FROM `planprodpo` p, `planprodng` n WHERE p.GUID = _pGUID AND p.idnNG = n.GUID;
			UPDATE `planprodpo` p, `planprodng` n 
			SET
				p.Plan_StartTime = DATE_ADD(p.Plan_StartTime, INTERVAL _Ilosc MINUTE),
				p.Plan_EndTime = DATE_ADD(p.Plan_EndTime, INTERVAL _Ilosc MINUTE),
				p.Update_Status = 'Set.TIME'
			WHERE
				n.AreaGUID = @AreaGUID
				AND n.GUID = p.idnNG
				-- AND p.idnNG = @idnNG
				AND	p.Plan_StartTime > @Plan_StartTime
			;
		END IF;
	END IF;	--	IF _MODE = 'move.planprodpo.by.min' 
	
	IF _MODE = 'check.planprodpo.overlap' THEN
	/* *************************************************************************************************************************************************************
		Sprawdz czy jest konflikt czasowy
		LUB
		Przelicz `planprodpo` biorac ilosc zasobu 0..1 i wyliczajac cala recepture i energie i inne
		CALL pr_planprodxx_tools('check.planprodpo.overlap', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 60, '', '', '', '');		
			_Key pGUID, czyli pozycja czyli GUID w `planprodpo` ktory przesuwamy ALE takze gdy _Param_A <> 0  pozycja przesuwana od kt�rej starsze beda przesuwane,	
	**************************************************************************************************************************************************************/
		SET _pGUID = _Key;
		SELECT p.Plan_StartTime, p.Plan_EndTime, n.AreaGUID, p.idnNG
		INTO @Plan_StartTime, @Plan_EndTime, @AreaGUID, @idnNG
		FROM `planprodpo` p, `planprodng` n WHERE p.GUID = _pGUID AND p.idnNG = n.GUID;
		
		
		UPDATE `planprodpo` p 
		SET
			p.datawystaw = DATE_ADD(p.datawystaw, INTERVAL _Ilosc MINUTE)
		WHERE
			p.GUID = _pGUID
		;
		IF _Param_A != '' THEN
		--	gdy <> 0 to przesuwam wszystkie inne
			SELECT datawystaw, idnNG INTO @datawystaw, @idnNG FROM `planprodpo` WHERE GUID = _pGUID;
			UPDATE `planprodpo` p 
			SET
				p.datawystaw = DATE_ADD(p.datawystaw, INTERVAL _Ilosc MINUTE)
			WHERE
				p.idnNG = @idnNG
				AND
				p.datawystaw > @datawystaw
			;
		END IF;
	END IF;	--	IF _MODE = 'check.planprodpo.overlap' 
	
END$$



DROP PROCEDURE IF EXISTS `pr_rcpo_calc_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_rcpo_calc_tools`(
	IN _MODE VARCHAR(150),
	IN _Key VARCHAR(38),	
	IN _Ilosc DECIMAL(20,6), 
	IN _Param_A VARCHAR(250),	
	IN _Param_B VARCHAR(250),	
	IN _Param_C VARCHAR(250),	
	IN _Param_D VARCHAR(250)	
)
S_pr_rcpo_calc_tools: BEGIN
	DECLARE _RcNg_GUID, _IdnNg, _pGUID, _IdnTowr_RC, _IdnTowr, _AreaGUID VARCHAR(38) DEFAULT '';
	DECLARE _Ilosc_RC DECIMAL(20, 6) DEFAULT 0;
	DECLARE _SQLText MEDIUMTEXT;
	
	IF _MODE = 'work.INIT' THEN
	/* *************************************************************************************************************************************************************
		Inicjowanie (tempy)
		CALL pr_rcpo_calc_tools('work.INIT', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 100, '', '', '', '');		
	**************************************************************************************************************************************************************/
		SET _RcNg_GUID = _Key;
		SET 
			@.pr_rcpo_calc_atom.Err = 0
		;
		DROP TEMPORARY TABLE IF EXISTS `t_pr_rcpo_calc_atom`;
		CREATE TEMPORARY TABLE `t_pr_rcpo_calc_atom`
			SELECT 
				_VarInt(0) AS _LP, 
				_VarInt(0) AS _Enter,
				_VarInt(0) AS _Level, 
				_VarDECIMAL(0) AS _Factor,
				_VarDECIMAL(0) AS _Ilosc,
			--	_VarInt(0) AS _IloscIsConst,
				_VarStr(NULL) AS _Time_Res,
				_VarInt(0) AS _INPUT_from_planprodpo_rcpo,
				p.*
			FROM `rcpo` p
			WHERE 
				FALSE
		;
		ALTER TABLE `t_pr_rcpo_calc_atom` 
			ADD UNIQUE KEY `Time_Res` (`Time_Res`)
		;
	END IF;	--	IF _MODE = 'work.INIT'
	
	IF _MODE = 'work.DROP' THEN
	/* *************************************************************************************************************************************************************
		Inicjowanie (tempy)
		CALL pr_rcpo_calc_tools('work.DROP', '', 0, '', '', '', '');		
	**************************************************************************************************************************************************************/
		SET @X = 1;
		DROP TEMPORARY TABLE IF EXISTS `t_pr_rcpo_calc_atom`;
	END IF;	--	IF _MODE = 'work.DROP'

	

	IF _MODE = 'work.prepare.t_pr_rcpo_calc_atom' THEN
	/* *************************************************************************************************************************************************************
		Naliczenie atomiczne
		CALL pr_rcpo_calc_tools('work.prepare.t_pr_rcpo_calc_atom', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 100, '', '', '', '');		
	**************************************************************************************************************************************************************/
		SET _RcNg_GUID = _Key;
		
		CALL pr_rcpo_calc_tools('work.INIT', '', 0, '', '', '', '');		
		SELECT Ilosc, IdnTowr INTO _Ilosc_RC, _IdnTowr_RC FROM `rcpo` WHERE idnNG = _RcNg_GUID AND deleted = 0 AND Przychod = 1 LIMIT 1
		;
		SET
			_IdnTowr_RC = IFNULL(_IdnTowr_RC, ''),
			_Ilosc = IF(IFNULL(_Ilosc, 0) > 0, _Ilosc, _Ilosc_RC)
		;
		/*
				SET
					_IdnTowr_RC = 
					IFNULL((
					SELECT IdnTowr FROM `rcpo` WHERE idnNG = _RcNg_GUID AND deleted = 0 AND Przychod = 1 LIMIT 1 
					), '')
				;
		*/
		IF _IdnTowr_RC = '' OR IFNULL(_Ilosc, 0) = 0 THEN
			SET @.pr_rcpo_calc_atom.Err = 1;
			IF _Param_D = 'select' THEN
				SELECT @.pr_rcpo_calc_atom.Err AS Err, 'Brak receptury' AS Msg;
			END IF;
			LEAVE S_pr_rcpo_calc_tools;
		END IF;
	--	ZACZYNAMY	
		SET 
			@.pr_rcpo_calc_atom.LP = 0,
			@.pr_rcpo_calc_atom._Enter = 0 
		;
		
		
		CALL pr_rcpo_calc_atom('', 
			_RcNg_GUID, 
			0,
			_IdnTowr_RC, 
			_Ilosc, 
		--	0,
			NULL,
			_Param_A,	-- pGUID ???
			_Param_B,	
			_Param_C	
			-- _Param_D
		);
	END IF;	 	--	IF _MODE = 'work.INIT'



	IF _MODE LIKE 'Set.and.Calc.RES.planprodpo.z.%' THEN
	/* *************************************************************************************************************************************************************
		Przelicz `planprodpo` biorac ilosc i wyliczajac cala recepture i energie i inne
		CALL pr_rcpo_calc_tools('Set.and.Calc.RES.planprodpo.z.Ilosci', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 100, '', '', '', '');		
		LUB
		Przelicz `planprodpo` biorac ilosc zasobu 0..1 i wyliczajac cala recepture i energie i inne
		CALL pr_rcpo_calc_tools('Set.and.Calc.RES.planprodpo.z.Ilosc_1_N', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 100, '', '', '', '');		
		W 
		_Key 	pGUID, czyli pozycja czyli GUID w `planprodpo`
		_Ilosc	ilosc lub Ilosc_1_N
	**************************************************************************************************************************************************************/
		SET _pGUID = _Key;

		IF _MODE = 'Set.and.Calc.RES.planprodpo.z.Ilosci' THEN
			UPDATE `planprodpo` p
			SET
				p.Ilosc = _Ilosc,
				p.Update_Status = 'Set.Ilosc'
			WHERE 
				p.GUID = _pGUID
			;
		END IF;
		
		IF _MODE = 'Set.and.Calc.RES.planprodpo.z.Ilosc_1_N' THEN
			UPDATE `planprodpo` p
			SET
				p.Ilosc_1_N = _Ilosc,
				p.Update_Status = 'Set.Ilosc_1_N'
			WHERE 
				p.GUID = _pGUID
			;
		END IF;
		
	--	Odzyskaj/pobierz wyliczona Ilosc
		SELECT RecepturaGUID, Ilosc INTO _RcNg_GUID, @planprodpo.Ilosc FROM `planprodpo` WHERE GUID = _pGUID;
		
		CALL pr_rcpo_calc_tools('work.prepare.t_pr_rcpo_calc_atom', _RcNg_GUID, @planprodpo.Ilosc, _pGUID, _Param_B, _Param_C, _Param_D);		
		
	--	Media	
		DELETE FROM `planprodpo_res` WHERE pGUID = _pGUID;
		INSERT IGNORE INTO `planprodpo_res`
		(pGUID, Res_Type, Ilosc, Time_Res)
		SELECT 
			_pGUID, 
			t.Res_Type, -- CONCAT(t.Res_Type, IF(_IloscIsConst = 1, '_Const', '')),
			SUM(t._Ilosc),
			t._Time_Res
		FROM `t_pr_rcpo_calc_atom` t
		WHERE
			t.Przychod = 0
			AND t.Res_Type IN ('Electr','Gas','Time')
			AND IFNULL(idnng_merge, '')  = '' 
		GROUP BY Res_Type, _Time_Res -- , _IloscIsConst
		;
		
		-- CALL pr_rcpo_calc_tools('work.DROP', '', 0, '', '', '', '');		
		UPDATE `planprodpo` p SET p.Update_Status = 'Set.TIME' WHERE p.GUID = _pGUID;
		
	END IF; -- _MODE LIKE 'Set.and.Calc.RES.planprodpo.z.%' THEN

	IF _MODE = 'Update.Status_Realizacji' THEN
		CALL pr_planprodxx_tools('Update.Status_Realizacji', _Key, _Ilosc, _Param_A, _Param_B, _Param_C, _Param_D);		
	END IF;	--	_MODE = 'Update.Status_Realizacji' 
	
	IF _MODE LIKE 'select.%' THEN
		CALL pr_planprodxx_tools(_MODE, _Key, _Ilosc, _Param_A, _Param_B, _Param_C, _Param_D);		
	END IF;	--	IF _MODE = 'select.receptury.for.IdnTowr' 
	
	IF _MODE = 'move.planprodpo.by.min' THEN
		CALL pr_planprodxx_tools('move.planprodpo.by.min', _Key, _Ilosc, _Param_A, _Param_B, _Param_C, _Param_D);		
	END IF;	--	IF _MODE = 'move.planprodpo.by.min' 
	
	IF _MODE = 'check.planprodpo.overlap' THEN
		CALL pr_planprodxx_tools('check.planprodpo.overlap', _Key, _Ilosc, _Param_A, _Param_B, _Param_C, _Param_D);		
	END IF;	--	IF _MODE = 'check.planprodpo.overlap' 
END$$

		DROP PROCEDURE IF EXISTS `pr_rcpo_calc_atom`$$
		CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_rcpo_calc_atom`(
			IN _MODE VARCHAR(150),
			IN _RcNg_GUID VARCHAR(38),	
			IN _Level INTEGER, 
			IN _IdnTowr VARCHAR(38), 
			IN _Ilosc DECIMAL(20, 6), 
		--	IN _IloscIsConst INTEGER,
			IN _Time_Res VARCHAR(250), 
			
			IN _pGUID VARCHAR(250),	
			IN _Param_B VARCHAR(250),	
			IN _Param_C VARCHAR(250)	
			-- IN _Param_D VARCHAR(250)	
		)
		S_pr_rcpo_calc_atom: BEGIN
			DECLARE _Factor, _Ilosc_RC DECIMAL(20, 6) DEFAULT 0;
			DECLARE _IdnTowr_RC VARCHAR(38) DEFAULT '';
			DECLARE _INPUT_from_planprodpo_rcpo BOOLEAN;
			
			IF IFNULL(_IdnTowr, '') = '' THEN
				SET _IdnTowr = '';
				SELECT IdnTowr INTO _IdnTowr FROM `rcpo` WHERE idnNG = _RcNg_GUID AND deleted = 0 AND Przychod = 1 LIMIT 1
				;
			END IF;
			
			SELECT Ilosc, IdnTowr INTO _Ilosc_RC, _IdnTowr_RC FROM `rcpo` WHERE idnNG = _RcNg_GUID AND deleted = 0 AND Przychod = 1 AND IdnTowr = _IdnTowr LIMIT 1
			;
			SET
				_IdnTowr = IF(IFNULL(_IdnTowr, '') = '', _IdnTowr_RC, _IdnTowr),
				_Factor = IF(IFNULL(_Ilosc_RC, 0) > 0, _Ilosc/ _Ilosc_RC, 0)
			;
			/*
			SET
				_Ilosc_RC = 
				IFNULL((
				SELECT Ilosc FROM `rcpo` WHERE idnNG = _RcNg_GUID AND deleted = 0 LIMIT 1 
				), 0)
			;
			SET _Factor = IF(_Ilosc_RC > 0, _Ilosc/ _Ilosc_RC, 0);
			*/
set @.X.recep = '1';
			IF _Factor = 0 THEN
				LEAVE S_pr_rcpo_calc_atom;
			END IF;
			
			/*
			ALE NAJPIERW SPRAWDZ CZY NIE MA pozycji udawanych z `planprodpo_rcpo`
			_pGUID zawiera wtedy GUID pozycji `planprodpo`
			*/
			SET _INPUT_from_planprodpo_rcpo = _pGUID != '';
set @.X.recep = '2';
			IF _INPUT_from_planprodpo_rcpo THEN
				INSERT INTO `t_pr_rcpo_calc_atom`
				(_LP, _Enter, _Level, _Factor, _ILosc, 
			--	_IloscIsConst, 
				_Time_Res, _INPUT_from_planprodpo_rcpo, GUID, idnNg, ilosc, idnng_merge, Res_Type, Time_Res, IloscIsConst, Deleted)
					SELECT
						_VarInt(@.pr_rcpo_calc_atom.LP := @.pr_rcpo_calc_atom.LP + 1) AS _LP, 
						@.pr_rcpo_calc_atom._Enter AS _Enter,
						_Level, 
						_Factor,
						-- p.Ilosc * _Factor, 
						IF(p.IloscIsConst = 1, p.Ilosc, p.Ilosc * _Factor) AS _ILosc, 
				--		p.IloscIsConst AS _IloscIsConst,
						p.Time_Res AS _Time_Res, 
						1 AS _INPUT_from_planprodpo_rcpo,
						p.rcpoGUID AS GUID, 
						_RcNg_GUID AS idnNg, 
						p.ilosc, 
						p.rcng_GUID_merge AS idnng_merge, 
						-- 'Time' AS Res_Type, 
						NULL AS Res_Type, 
						p.Time_Res, 
						p.IloscIsConst, 
						0 AS Deleted
					FROM `planprodpo_rcpo` p
					WHERE 
						p.pGUID = _pGUID
				;
				SET _pGUID = '';
set @.X.recep = '2.1';
			END IF;
			/*
			Mozna by odrazu wstawiac ALE niestety musze zminic jeszcze updatem ilosc (factor)
			Zatem najpierw temp
			*/
			DROP TEMPORARY TABLE IF EXISTS `t_pr_rcpo_calc_atom_1`;
			CREATE TEMPORARY TABLE `t_pr_rcpo_calc_atom_1` LIKE `t_pr_rcpo_calc_atom`;
			
set @.X.recep = '3';
		--	Wstaw niezagniezdzone
			SET @.pr_rcpo_calc_atom._Enter = @.pr_rcpo_calc_atom._Enter + 1;
			INSERT INTO `t_pr_rcpo_calc_atom_1`
				SELECT
					_VarInt(@.pr_rcpo_calc_atom.LP := @.pr_rcpo_calc_atom.LP + 1) AS _LP, 
					@.pr_rcpo_calc_atom._Enter AS _Enter,
					_Level, 
					_Factor,
					-- p.Ilosc * _Factor, 
					IF(p.IloscIsConst = 1, p.Ilosc, p.Ilosc * _Factor), 
				--	_IloscIsConst,
					IFNULL(p.Time_Res, _Time_Res) AS _Time_Res, 
					0 AS _INPUT_from_planprodpo_rcpo,
					p.*
				FROM `rcpo` p
				WHERE 
					p.idnNG = _RcNg_GUID 
					AND p.deleted = 0 
					AND p.Res_Type IN ('Electr','Gas','Time')
					-- AND p.pomin = 0
					-- AND p.Idnng_merge = ''
			;
set @.X.recep = '4';
			-- aaaaaaaaaaaaa
			/*
			UPDATE `t_pr_rcpo_calc_atom_1` t
			SET
				t.Time_Res = t._Time_Res
			;
			*/
			INSERT IGNORE INTO `t_pr_rcpo_calc_atom`
				SELECT
					p.*
				FROM `t_pr_rcpo_calc_atom_1` p
			;
			DROP TEMPORARY TABLE IF EXISTS `t_pr_rcpo_calc_atom_1`;
			
		--	P�tla przetwarzania
			BEGIN
			-- pozycje receptury
			
				DECLARE done BOOL DEFAULT FALSE;
				DECLARE _rcpo_Idnng_merge, _rcpo_IdnTowr VARCHAR(38);
				DECLARE _rcpo_Ilosc DECIMAL(20, 6);
				DECLARE _rcpo_Jednostka, _rcpo_Magazyn INTEGER;
			--	DECLARE _rcpo_IloscIsConst INTEGER;
				DECLARE _rcpo_Time_Res VARCHAR(250); 
			--	3.1 Kursor	
				DECLARE c_pr_rcpo_calc_atom CURSOR FOR
					SELECT
						p.Idnng_merge, p.IdnTowr, 
						IF(p.IloscIsConst = 1, p.Ilosc, p.Ilosc * _Factor) AS rcpo_Ilosc, 
						p.Jednostka, p.Magazyn, 
					--	p.IloscIsConst,
						IFNULL(p.Time_Res, _Time_Res) AS _rcpo_Time_Res
					-- FROM `rcpo` p
					FROM `t_pr_rcpo_calc_atom` p
					WHERE 
						p.idnNG = _RcNg_GUID 
						AND p.deleted = 0 
						AND p.Idnng_merge != ''
					
				;	
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			--	3.2 Open Kursor	
				OPEN c_pr_rcpo_calc_atom;
				loop_c_pr_rcpo_calc_atom: LOOP
					FETCH c_pr_rcpo_calc_atom INTO 
						_rcpo_Idnng_merge, _rcpo_IdnTowr, _rcpo_Ilosc, _rcpo_Jednostka, _rcpo_Magazyn, 
					--	_rcpo_IloscIsConst, 
						_rcpo_Time_Res
					;
					IF done THEN
						LEAVE loop_c_pr_rcpo_calc_atom;
					END IF;
				--	Wykonaj rekusje
					CALL pr_rcpo_calc_atom('', 
						_rcpo_Idnng_merge, 
						_Level + 1,
						_rcpo_IdnTowr, 
						_rcpo_Ilosc, 
					--	_rcpo_IloscIsConst,
						_rcpo_Time_Res,
						
						_pGUID,	
						_Param_B,	
						_Param_C	
						-- _Param_D
					);
				END LOOP;
				CLOSE c_pr_rcpo_calc_atom;
			
			END;
			
		END$$


DROP PROCEDURE IF EXISTS `pr_receptury_cechy_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_receptury_cechy_tools`(
	IN _MODE VARCHAR(150),
	IN _GUID VARCHAR(38),	
	IN _Param_A VARCHAR(250),	
	IN _Param_B VARCHAR(250),	
	IN _Param_C VARCHAR(250),	
	IN _Param_D VARCHAR(250)	
)
S_pr_receptury_cechy_tools: BEGIN
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		_GUID		    GUID nag��wka receptury, dla kt�rej zerujemy parametry
		CALL pr_receptury_cechy_tools('calc', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', '1', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	DECLARE	_rcng_cechy_idncechy_kCal INTEGER DEFAULT 6;
	IF _MODE = 'get.idntowr.masa.for.rcng' THEN
	/* *************************************************************************************************************************************************************
		Pobiera z receptury Towar-produkt glowny oraz Mas� produkowana przez recepture 
		i zwaraca do
			@rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
		CALL pr_receptury_cechy_tools('get.idntowr.masa.for.rcng', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', '1', '', '', '');		
	**************************************************************************************************************************************************************/
set @XXXXX = 'hej';
		SELECT 
			IdnTowr, 10 * SUM(r.Ilosc * IF(t.Waga = 0, 0.1, t.Waga)) AS masa_100g 
		INTO
			@rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
		FROM `rcpo` r, `TowrNg` t 
		WHERE
			idnng = _GUID
			AND r.przychod = 1
			AND t.GUID = r.IdnTowr
			AND r.Priorytet = -1
		;
		SELECT 
			10 * SUM(r.Ilosc * IF(t.Waga = 0, 0.1, t.Waga)) AS masa_skladnikow_100g
		INTO
			@rcng.cechy.Masa_skladnikow_100g
		FROM `rcpo` r, `TowrNg` t 
		WHERE
			idnng = _GUID
			AND r.przychod = 0
			AND t.GUID = r.IdnTowr
		;
		
	END IF;	--	IF _MODE = 'get.idntowr.masa.for.rcng'
	
	IF _MODE = 'calc' THEN
	/* *************************************************************************************************************************************************************
		Nalicza cech receptury (a scisle towaru glownego) 
		CALL pr_receptury_cechy_tools('calc', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', '1', '', '', '');	
			_Param_A = 1 to czysci poprzednie cechy a zatem tutaj nie czyscimy tylko wstawiamy aktualne cechy
	**************************************************************************************************************************************************************/
	--	wstaw wartosci do @rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
		CALL pr_receptury_cechy_tools('get.idntowr.masa.for.rcng', _GUID, _Param_A, _Param_B, _Param_C, _Param_D);		
		DROP TEMPORARY TABLE IF EXISTS `t_rcpo_dane_tech_surow`;
		CREATE TEMPORARY TABLE `t_rcpo_dane_tech_surow`
			SELECT
				@rcng.cechy.IdnTowr_Main AS IdnTowr_Main, 
				@rcng.cechy.Masa_skladnikow_100g AS Masa_skladnikow_100g, 
				@rcng.cechy.Masa_100g.Receptury AS Masa_Receptury_100g, 
				r.Ilosc, 
				10*r.Ilosc * t.Waga AS masa_100g, 
				t.Symbol, t.Nazwa, r.IdnTowr, idnng_merge -- , r.*
			FROM `rcpo` r, `TowrNg` t 
			WHERE
				r.idnng = _GUID
				AND r.przychod = 0
				AND t.GUID = r.IdnTowr
		;
	--	Wstaw wszystkie cechy odzywcze wszystkich surowcow (towarow) z listy recepturowej (UWAGA zagniezdzone receptury sa reprezentoane tez przez IdnTowr a nie przez rekurencje zatem nalezy polprodukty naliczyc wczesniej)
		DROP TEMPORARY TABLE IF EXISTS `t_rcpo_dane_tech_surow_cechy`;
		CREATE TEMPORARY TABLE `t_rcpo_dane_tech_surow_cechy`
			SELECT
				s.*, c.opis, l.value, 
				_VarDouble(1*REPLACE(l.value, ',','.')/1) AS Content,
				-- _VarDouble(1*REPLACE(l.value, ',','.')/100) AS Content_per_kg,
				_VarDouble(1*REPLACE(l.value, ',','.')/1*s.Masa_100g) AS Ilosc_Cechy,
				l.idncechy,  
				c.Value_E_J, c.Value_E_kCal
			FROM `t_rcpo_dane_tech_surow` s, `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
			WHERE
				l.objguid = s.IdnTowr
				AND l.idncechy = c.idn
				AND l.idncechy != _rcng_cechy_idncechy_kCal
				AND cd.idn = c.IdnCechyDef
				-- AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
				AND cd.kategoria = 'nutrition information'
		;

		-- SHOW CREATE TABLE `erp_cechy_links`;
		DROP TEMPORARY TABLE IF EXISTS `t_erp_cechy_links_e`;
		CREATE TEMPORARY TABLE `t_erp_cechy_links_e` (
		  `objGUID` VARCHAR(38) NOT NULL DEFAULT '',
		  `objTable` VARCHAR(100) NOT NULL DEFAULT '',
		  `IdnCechy` INT(11) NOT NULL DEFAULT '0',
		  `Value` DECIMAL(18,4) DEFAULT NULL,
		  `DataOd` DATE DEFAULT NULL COMMENT 'np. data nadania',
		  `DataDo` DATE DEFAULT NULL COMMENT 'np. data waznosci',
		  `UGrpGUID` VARCHAR(38) NOT NULL DEFAULT 'ALL',
		  `Opis` TEXT,
		  `Calculated` INT(11) NOT NULL DEFAULT '0',
		  `Value_E_J` DECIMAL(20,4) NOT NULL DEFAULT 0,
		  `Value_E_kCal` DECIMAL(20,4) NOT NULL DEFAULT 0,
		  PRIMARY KEY (`IdnCechy`),
		  UNIQUE KEY `klucz_unikalny` (`objGUID`,`objTable`,`IdnCechy`),
		  KEY `objGUID` (`objGUID`),
		  KEY `objTable` (`objTable`),
		  KEY `IdnCechy` (`IdnCechy`)
		) DEFAULT CHARSET=cp1250
		;

		IF _Param_A != '1' THEN
		--	CZYLI _Param_A = 0 ---  GDY _Param_A = 1 to czysci poprzednie cechy a zatem tutaj nie czyscimy tylko wstawiamy aktualne cechy
			INSERT IGNORE INTO `t_erp_cechy_links_e`
			-- REPLACE `t_erp_cechy_links_e`
			(objGUID, objTable, IdnCechy, Value, DataOd, DataDo, UGrpGUID, Opis, Calculated, Value_E_J, Value_E_kCal)
				SELECT
					l.objGUID, l.objTable, l.IdnCechy, l.Value, l.DataOd, l.DataDo, l.UGrpGUID, l.Opis, l.Calculated, c.Value_E_J, c.Value_E_kCal
				FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
				WHERE
					l.objguid = @rcng.cechy.IdnTowr_Main
					AND l.idncechy = c.idn
					AND cd.idn = c.IdnCechyDef
					-- AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
					AND cd.kategoria = 'nutrition information'
			;
			DELETE l FROM `t_erp_cechy_links_e` l 
			WHERE
				l.IdnCechy = _rcng_cechy_idncechy_kCal
			;
		END IF;
	--	Tutaj tylko dostawiamy cechy nie istniejace - Gdy _Param_A = 1 to `t_erp_cechy_links_e` jest pusta i wstawiamy wszystko
		INSERT IGNORE INTO `t_erp_cechy_links_e`
		-- REPLACE `t_erp_cechy_links_e`
		(objGUID, objTable, IdnCechy, VALUE, Calculated, Value_E_J, Value_E_kCal)
			SELECT
				IdnTowr_Main AS objGUID, 
				'TowrNg' AS objTable,
				idncechy,   
				-- _VarDecimal(ROUND(SUM(Ilosc_Cechy)/Masa_Receptury_100g, 2)) AS `Value`
				_VarDecimal(ROUND(SUM(Ilosc_Cechy)/Masa_Receptury_100g, 3)) AS `Value`,
				-- APRO zakomentowal: _VarDecimal(ROUND(SUM(Ilosc_Cechy)/Masa_skladnikow_100g, 3)) AS `Value`,
				1,
				Value_E_J, Value_E_kCal
			FROM `t_rcpo_dane_tech_surow_cechy`
			GROUP BY idncechy  
		;
		
		SET @rcng.cechy.COUNT = 0;
		SELECT COUNT(*) INTO @rcng.cechy.COUNT FROM `t_erp_cechy_links_e`;
		
		IF @rcng.cechy.COUNT > 0 THEN
		--	usun juz poprzednie
			DELETE l FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
			WHERE
				cd.kategoria = 'nutrition information'
				AND c.IdnCechyDef = cd.idn
				AND l.idncechy = c.idn
				AND l.objguid = @rcng.cechy.IdnTowr_Main
				AND l.Fixed = 0
			;
		--	wstaw nowe-przygotowane w `t_erp_cechy_links_e`
			INSERT IGNORE INTO `erp_cechy_links`
			(objGUID, objTable, IdnCechy, VALUE, Calculated)
				SELECT
					objGUID, objTable, IdnCechy, ROUND(VALUE, 2), Calculated
				FROM `t_erp_cechy_links_e`
			;
			SELECT
				SUM(IF(IdnCechy = 1, Value_E_kCal * VALUE, 0)) +
				SUM(IF(IdnCechy = 2, Value_E_kCal * VALUE, 0)) +
				SUM(IF(IdnCechy = 3, Value_E_kCal * VALUE, 0)) +
				SUM(IF(IdnCechy = 4,   0.00 * VALUE, 0)) +
				SUM(IF(IdnCechy = 5,   0.00 * VALUE, 0)) AS Razem
			INTO @rcng.cechy.Razem.E_kCal
			FROM `t_erp_cechy_links_e`
			;
			
			INSERT IGNORE INTO `erp_cechy_links`
			(objGUID, objTable, IdnCechy, VALUE, Calculated)
			VALUES 
			(
				@rcng.cechy.IdnTowr_Main,	--  AS objGUID, 
				'TowrNg',	--  AS objTable,
				_rcng_cechy_idncechy_kCal,   
				_VarDecimal(ROUND(@rcng.cechy.Razem.E_kCal, 2)),	--   AS `Value`,
				1
			)
			;
		END IF;
		
		-- DROP TEMPORARY TABLE IF EXISTS `t_rcpo_dane_tech_surow`;
		-- DROP TEMPORARY TABLE IF EXISTS `t_rcpo_dane_tech_surow_cechy`;
		-- DROP TEMPORARY TABLE IF EXISTS `t_erp_cechy_links_e`;
	END IF;	--	IF _MODE = 'calc'


			IF _MODE = 'select.final.for' THEN
			/* *************************************************************************************************************************************************************
				Zwraca zestaw cech dla tej receptury - aq scislej rzecz ujmujac dla produktu glownego tejze receptury
				commandText := 
					CALL pr_receptury_cechy_tools('select.final.for', '', '', '', '', '');		
			**************************************************************************************************************************************************************/
				SET 
					@we.masa.100g.prod = IF(IFNULL(@we.masa.100g.prod, 1) = '', 1, IFNULL(@we.masa.100g.prod, 1))
				;
				DROP TEMPORARY TABLE IF EXISTS `t_receptury.select.final.for`;
				CREATE TEMPORARY TABLE `t_receptury.select.final.for`
				SELECT
					l.idn, l.ObjGUID AS IdnTowr, 
					@WeMasa100gProd := _VarDecimal(@we.masa.100g.prod*100) AS WeMasa100gProd,
					IdnCechy, -- l.value,
					c.opis, 
					@Content := _VarDouble(@we.masa.100g.prod*REPLACE(l.value, ',','.')) AS Content,
					@Content_g := _VarDouble(
						@we.masa.100g.prod*REPLACE(l.value, ',','.')*
						IF(c.JednostkaStr = 'g', 1, IF(c.JednostkaStr = 'mg', 0.001, 0))
					) AS Content_g,
					_VarStr(CONCAT(
						REPLACE(_VarStr(@Content), '.',','), ' ', c.JednostkaStr,
						_VarStr(
						IF(c.priorytet = 101,
							CONCAT(' (', _VarStr(ROUND(@Content * 4.1868, 2)), ' kJ)'),
							''
						))
					)) AS ContentStr,
					@ContentProc := ROUND(100*@Content_g/@WeMasa100gProd, 2) AS ContentProc,
					_VarStr(IF(@ContentProc = 0, 
						'-',
						REPLACE(CONCAT(@ContentProc, '%'), '.', ',')
					)) AS ContentProcStr
				FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
				WHERE
					l.objguid = @rcng.cechy.IdnTowr_Main
					AND l.idncechy = c.idn
					AND cd.idn = c.IdnCechyDef
					AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
				ORDER BY 
					c.priorytet
				;
				
				IF _Param_D = '' THEN
					SELECT 
						t.Opis, t.ContentStr AS `Zawart. w 100g`, t.ContentProcStr, t.* 
					FROM `t_receptury.select.final.for` t
					;
				ELSE
					IF _Param_D = '1' THEN
						SELECT GROUP_CONCAT(CONCAT(Opis, ': ', ContentStr) SEPARATOR '\n') AS ContentStr FROM `t_receptury.select.final.for`;
					ELSEIF _Param_D = '2' THEN
						SELECT GROUP_CONCAT(CONCAT(Opis, ': ', ContentStr, IF(ContentProc > 0, CONCAT(' - ', ContentProcStr), '')) SEPARATOR '\n') AS ContentStr FROM `t_receptury.select.final.for`;
					ELSEIF _Param_D = '3' THEN
						SELECT GROUP_CONCAT(CONCAT(Opis, ': ', ContentProcStr) SEPARATOR '\n') AS ContentStr FROM `t_receptury.select.final.for` WHERE ContentProc > 0;
					END IF;
				END IF;
				
				-- DROP TEMPORARY TABLE IF EXISTS `t_receptury.select.final.for`;
			END IF;
			

	
	IF _MODE = 'select.for.rcng' THEN
	/* *************************************************************************************************************************************************************
		Zwraca zestaw cech dla tej receptury - aq scislej rzecz ujmujac dla produktu glownego tejze receptury
		commandText := 
			CALL pr_receptury_cechy_tools('select.for.rcng', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', '', '', '', '');		
	**************************************************************************************************************************************************************/
	--	wstaw wartosci do @rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
		CALL pr_receptury_cechy_tools('get.idntowr.masa.for.rcng', _GUID, _Param_A, _Param_B, _Param_C, _Param_D);		
		SET
			@we.masa.100g.prod = 1
		;
		CALL pr_receptury_cechy_tools('select.final.for', '', '', '', '', _Param_D);		
		/*
		by�o
		SELECT
			l.idn, l.ObjGUID AS IdnTowr, 
			IdnCechy, -- l.value,
			c.opis, 
			_VarDouble(1*REPLACE(l.value, ',','.')) AS Content
		FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
		WHERE
			l.objguid = @rcng.cechy.IdnTowr_Main
			AND l.idncechy = c.idn
			AND cd.idn = c.IdnCechyDef
			AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
		ORDER BY 
			c.priorytet
		;
		*/
	END IF;	--	IF _MODE = 'select.for.rcng'
	
	IF _MODE = 'select.for.rcng.for.kg' THEN
	/* *************************************************************************************************************************************************************
		Zwraca zestaw cech dla tej receptury - aq scislej rzecz ujmujac dla produktu glownego tejze receptury
		commandText := 
			CALL pr_receptury_cechy_tools('select.for.rcng.for.kg', '{3D15A370-08F7-45DF-9F1E-6691B490EC2B}', '', '10', '', '');		-- dla 10 kg
			CALL pr_receptury_cechy_tools('select.for.rcng.for.kg', '{3D15A370-08F7-45DF-9F1E-6691B490EC2B}', '', '0.5', '', '');		-- dla 0,5 kg
			_Param_B != '' to wart kg.
	**************************************************************************************************************************************************************/
	--	wstaw wartosci do @rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
		CALL pr_receptury_cechy_tools('get.idntowr.masa.for.rcng', _GUID, _Param_A, _Param_B, _Param_C, _Param_D);		
		SET @rcng.cechy.Masa_calc100g := 
			IF(_Param_B = '', 
				1,
				_VarDouble(_Param_B * 10)
			)
		;
		SET 
			@we.masa.100g.prod = @rcng.cechy.Masa_calc100g
		;

		SELECT
			l.idn, l.ObjGUID AS IdnTowr, 
			IdnCechy, -- l.value,
			c.opis, 
			_VarDouble(@rcng.cechy.Masa_calc100g*REPLACE(l.value, ',','.')) AS Content
		FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
		WHERE
			l.objguid = @rcng.cechy.IdnTowr_Main
			AND l.idncechy = c.idn
			AND cd.idn = c.IdnCechyDef
			AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
		ORDER BY 
			c.priorytet;
	END IF;	--	IF _MODE = 'select.for.rcng.for.kg'
	
	IF _MODE = 'select.for.TowrNg.for.kg' THEN
	/* *************************************************************************************************************************************************************
		Zwraca zestaw cech dla towaru
		commandText := 
			CALL pr_receptury_cechy_tools('select.for.TowrNg.for.kg', IdnTowr, '', '', '', '');			-- dla 100 g
			CALL pr_receptury_cechy_tools('select.for.TowrNg.for.kg', IdnTowr, '', '10', '', '');		-- dla 10 kg
			CALL pr_receptury_cechy_tools('select.for.TowrNg.for.kg', IdnTowr, '', '0.5', '', '');		-- dla 0,5 kg
			_Param_B != '' to wart kg.
	**************************************************************************************************************************************************************/
		SET @rcng.cechy.Masa_calc100g := 
			IF(_Param_B = '', 
				1,
				_VarDouble(_Param_B * 10)
			),
			@rcng.cechy.IdnTowr_Main = _GUID
		;
		SET 
			@we.masa.100g.prod = @rcng.cechy.Masa_calc100g
		;
		SELECT
			l.idn, l.ObjGUID AS IdnTowr, 
			IdnCechy, -- l.value,
			c.opis, 
			_VarDouble(@rcng.cechy.Masa_calc100g*REPLACE(l.value, ',','.')) AS Content
		FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
		WHERE
			l.objguid = _GUID
			AND l.idncechy = c.idn
			AND cd.idn = c.IdnCechyDef
			AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
		ORDER BY 
			c.priorytet;
	END IF;	--	IF _MODE = 'select.for.TowrNg.for.kg'
	
	IF _MODE = 'select.for.t_FDBC_TowrNg' THEN
	/* *************************************************************************************************************************************************************
		Zwraca dla Fastreporta zestaw cech dla towarow w widoku w FDBC teczki towarow
			CALL pr_receptury_cechy_tools('select.for.t_FDBC_TowrNg', '', '', '', '', '');			
	**************************************************************************************************************************************************************/
		SELECT
			t.IDN,t.GUID,
			t.Nazwa,
			c.Opis,
			ROUND(l.Value, 2) AS Wartosc,
			-- l.Value, l.Idn,
			c.JednostkaStr,
			d.Nazwa AS def_Nazwa
		FROM `t_FDBC_TowrNg` tt
		INNER JOIN `TowrNg` t ON t.GUID = tt.GUID
		INNER JOIN `erp_cechy_links` l ON l.objGUID = t.GUID AND l.objTable = 'TowrNg' 
		INNER JOIN  `erp_cechy` c ON c.Idn = l.IdnCechy
		INNER JOIN  `erp_cechy_def` d ON d.Idn = c.IdnCechyDef
		WHERE
			TRUE
		ORDER BY t.Nazwa,c.priorytet, l.idn, t.Symbol -- c.Opis
		;
	END IF;	--	IF _MODE = 'select.for.t_FDBC_TowrNg'


	
	
	
	IF _MODE LIKE 'select.content.for.%' THEN
		/*
		PRZYKLADY
		CALL pr_receptury_cechy_tools('select.content.for.rcng', 'B01-2', '', '', '', '');		
		CALL pr_receptury_cechy_tools('select.content.for.rcng.for.kg', 'B01-2', '30.56', '', '', '');		-- dla 10 kg
			CALL pr_receptury_cechy_tools('select.content.for.rcng.for.kg', 'B01-2', '0.12', '', '', 'content');		-- dla 10 kg
		CALL pr_receptury_cechy_tools('select.content.for.rcng.for.kg', 'B01-2', '10', '', '', '');		-- dla 10 kg
		CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', 'B01', '10', '', '', 'content');		-- dla 10 kg
		CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', 'B01', '10', '', '', 'content.proc');		-- dla 10 kg
		CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', 'B01', '10', '', '', 'porcja');		-- dla 10 kg
		CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', 'B01', '10', '', '', 'porcja.proc');		-- dla 10 kg
		CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', 'B01', '10', '', '', 'procent');		-- dla 10 kg
		*/
		IF _MODE = 'select.content.for.rcng' THEN
		/* *************************************************************************************************************************************************************
			Zwraca zestaw cech dla tej receptury - aq scislej rzecz ujmujac dla produktu glownego tejze receptury dla 100g
			commandText := 
				CALL pr_receptury_cechy_tools('select.content.for.rcng', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', '', '', '', '');		
			_GUID	receptura	
			_Param_D	tryb rezultatu
		**************************************************************************************************************************************************************/
		--	wstaw wartosci do @rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
			CALL pr_receptury_cechy_tools('get.idntowr.masa.for.rcng', _GUID, _Param_A, _Param_B, _Param_C, _Param_D);		
			SET @cechy.Masa_we_1kg = 0.1;
			CALL pr_receptury_cechy_tools('select.content.for.FINAL', @rcng.cechy.IdnTowr_Main, @cechy.Masa_we_1kg, '', '', _Param_D);		
	
		END IF;	--	IF _MODE = 'select.content.for.rcng' 
		
		
		IF _MODE = 'select.content.for.rcng.for.kg' THEN
		/* *************************************************************************************************************************************************************
			Zwraca zestaw cech dla tej receptury - aq scislej rzecz ujmujac dla produktu glownego tejze receptury
			commandText := 
				CALL pr_receptury_cechy_tools('select.content.for.rcng.for.kg', '{3D15A370-08F7-45DF-9F1E-6691B490EC2B}', '', '10', '', '');		-- dla 10 kg
				CALL pr_receptury_cechy_tools('select.content.for.rcng.for.kg', '{3D15A370-08F7-45DF-9F1E-6691B490EC2B}', '', '0.5', '', '');		-- dla 0,5 kg
			_GUID	receptura	
			_Param_A	kg
			_Param_D	tryb rezultatu
		**************************************************************************************************************************************************************/
		--	wstaw wartosci do @rcng.cechy.IdnTowr_Main, @rcng.cechy.Masa_100g.Receptury
			CALL pr_receptury_cechy_tools('get.idntowr.masa.for.rcng', _GUID, _Param_A, _Param_B, _Param_C, _Param_D);		
			SET @cechy.Masa_we_1kg = _Param_A;
			
			CALL pr_receptury_cechy_tools('select.content.for.FINAL', @rcng.cechy.IdnTowr_Main, @cechy.Masa_we_1kg, '', '', _Param_D);		
		END IF;	--	IF _MODE = 'select.for.rcng.for.kg'
		
		IF _MODE = 'select.content.for.TowrNg.for.kg' THEN
		/* *************************************************************************************************************************************************************
			Zwraca zestaw cech dla towaru
			commandText := 
				CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', IdnTowr, '', '', '', '');			-- dla 100 g
				CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', IdnTowr, '', '10', '', '');		-- dla 10 kg
				CALL pr_receptury_cechy_tools('select.content.for.TowrNg.for.kg', IdnTowr, '', '0.5', '', '');		-- dla 0,5 kg
			_GUID	IdnTowr
			_Param_A	kg
			_Param_B	gdy 'Porcja' to kg zczytujemy z porcji
			_Param_D	tryb rezultatu
		**************************************************************************************************************************************************************/
			SET @rcng.cechy.IdnTowr_Main = _GUID;
			IF _Param_B = 'Porcja' THEN
				SET @cechy.Masa_we_1kg = _Param_A;
			ELSE
				SET @cechy.Masa_we_1kg = _Param_A;
			END IF;
			
			CALL pr_receptury_cechy_tools('select.content.for.FINAL', @rcng.cechy.IdnTowr_Main, @cechy.Masa_we_1kg, '', '', _Param_D);		
		END IF;	--	IF _MODE = 'select.for.TowrNg.for.kg'
		
		
			IF _MODE = 'select.content.for.FINAL' THEN
			/* *************************************************************************************************************************************************************
				Zwraca zestaw cech dla tej receptury - aq scislej rzecz ujmujac dla produktu glownego tejze receptury
				commandText := 
					CALL pr_receptury_cechy_tools('select.content.for.FINAL', '', '', '', '', '');		
					_GUID		IdnTowr
					_Param_A	kg
					_Param_D	tryb rezultatu
					
			**************************************************************************************************************************************************************/
				BEGIN
				DECLARE	_IdnTowr VARCHAR(38);
				DECLARE	_Content_Masa_Str, _Content_Masa_Porcja_Str, _Content_Masa_Jedn VARCHAR(100);
				DECLARE _Masa_V_kg, _Masa_V_g , _Ile_V_100g, _Ile_V_porcja_100g, _Content_Masa DECIMAL(18, 4) DEFAULT 1;
				
				SET 
					_IdnTowr = _GUID,
					_Masa_V_kg = _Param_A,
					@_porcja_str = '',
					@_Porcja_V = 0
				;
				SELECT 
					CONCAT(_VarDouble(ROUND(licz_L, 3)), 
						IF(licz_M != 1, 
							REPLACE(CONCAT('/', _VarDouble(ROUND(licz_M, 3)), ' = ', _VarDouble(ROUND(licz_L, 3)/ROUND(licz_M, 3))), '.', ','), 
							''
						),
					' kg' 
					) AS porcja_str, 
					_VarDouble(ROUND(licz_L, 3)/ROUND(licz_M, 3)) AS Porcja_V
				INTO
					@_porcja_str,
					@_Porcja_V
				FROM `przeliczniki` p
				WHERE 
                    TRUE
                    AND p.idntowr = _IdnTowr
                    AND p.jedn_Wej = 45 AND p.jedn_wyj = 2 
                LIMIT 1
				;
				
				SET
					_Ile_V_100g = (_Masa_V_kg * 10),
					_Masa_V_g = (_Masa_V_kg * 1000),
					_Content_Masa_Str = _VarStr(CONCAT(
						'Zawartosc dla ',
						IF(_Masa_V_kg < 1,
							CONCAT(_VarStr(ROUND(_Masa_V_kg*1000, 0)), ' g'),
							CONCAT(_VarStr(ROUND(_Masa_V_kg, 2)),  ' kg')
						)
					)),
					_Ile_V_porcja_100g = @_Porcja_V * 10,
					_Content_Masa_Porcja_Str = 
						IF(@_Porcja_V > 0,
							CONCAT('dla porcji (', @_porcja_str, ')'),
							''
						)
				;
				IF _Masa_V_kg < 1 THEN
					SET
						_Content_Masa = _Masa_V_kg*1000,
						_Content_Masa_Jedn = ' g'
					;
				ELSE
					SET
						_Content_Masa = _Masa_V_kg,
						_Content_Masa_Jedn = ' kg'
					;
				END IF;
				-- SET _Content_Masa_Str = REPLACE(REPLACE(IF((_Content_Masa - TRUNCATE(_Content_Masa, 0) > 0), FORMAT(_Content_Masa, 4), FORMAT(_Content_Masa, 0)), ',', ' '), '.', ',');
				-- SET _Content_Masa_Str = REPLACE(REPLACE(IF((_Content_Masa - TRUNCATE(_Content_Masa, 0) > 0), _VarStr(_VarDouble(_Content_Masa + 1 - 1)), FORMAT(_Content_Masa, 0)), ',', ' '), '.', ',');
				SET _Content_Masa_Str = REPLACE(REPLACE(_VarStr(_VarDouble(_Content_Masa + 1 - 1)), ',', ' '), '.', ',');
				SET _Content_Masa_Str = _VarStr(CONCAT('Zawartosc dla ',_Content_Masa_Str, _Content_Masa_Jedn));
				
				DROP TEMPORARY TABLE IF EXISTS `t_receptury.select.final.for`;
				CREATE TEMPORARY TABLE `t_receptury.select.final.for`
				SELECT
					l.idn, l.ObjGUID AS IdnTowr, 
					_VarDecimal(_Masa_V_g) AS Masa_V_g,
					_Content_Masa_Str AS Content_Masa_Str,
					IdnCechy, -- l.value,
					c.opis, 
					@Content := _VarDouble(_Ile_V_100g*_VarDouble(REPLACE(l.value, ',','.'))) AS Content,
					@Content_g := _VarDouble(
						@Content*
						IF(c.JednostkaStr = 'g', 1, IF(c.JednostkaStr = 'mg', 0.001, 0))
					) AS Content_g,
					_VarStr(CONCAT(
						REPLACE(_VarStr(@Content), '.',','), ' ', _VarStr(c.JednostkaStr),
						IF(c.priorytet = 101,
							CONCAT(' (', _VarStr(ROUND(@Content * 4.1868, 2)), ' kJ)'),
							''
						)
					)) AS ContentStr,
					@ContentProc := ROUND(100*@Content_g/_Masa_V_g, 2) AS ContentProc,
					_VarStr(IF(@ContentProc = 0, 
						'-',
						REPLACE(CONCAT(_VarStr(@ContentProc), '%'), '.', ',')
					)) AS ContentProcStr,
					
					_Content_Masa_Porcja_Str AS Content_Masa_Porcja_Str,
					@Content_porcja := _VarDouble(_Ile_V_porcja_100g*_VarDecimal(_VarDouble(REPLACE(l.value, ',','.')))) AS Content_porcja,
					_VarStr(CONCAT(
						REPLACE(_VarStr(@Content_porcja), '.',','), ' ', _VarStr(c.JednostkaStr),
						IF(c.priorytet = 101,
							CONCAT(' (', _VarStr(ROUND(@Content_porcja * 4.1868, 2)), ' kJ)'),
							''
						)
					)) AS Content_porcja_Str,
					@WartOdzGDA := _VarDouble(IF(c.Param1 != '', c.Param1, 0)) AS WartOdzGDA,
					@WartOdzGDA_Proc := IF(@WartOdzGDA = 0, 0, @WartOdzGDA_Proc := _VarDouble(ROUND(100*@Content_porcja/@WartOdzGDA, 2))) AS WartOdzGDA_Proc,
					_VarStr(IF(@WartOdzGDA_Proc = 0, '-', CONCAT(REPLACE(@WartOdzGDA_Proc, '.',','), '%'))) AS WartOdzGDA_Proc_Str
						
					
				FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
				WHERE
					l.objguid = _IdnTowr
					AND l.idncechy = c.idn
					AND cd.idn = c.IdnCechyDef
					AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
				ORDER BY 
					c.priorytet
				;
				
				IF _Param_D = '' THEN
					SELECT * FROM `t_receptury.select.final.for`;
				ELSE
					IF _Param_D LIKE 'content%' THEN
					--	zawartosc i/lub procent
						SELECT 
							CONCAT(
								_Content_Masa_Str, 
								'\n', 
								GROUP_CONCAT(
									-- CONCAT(Opis, ': ', ContentStr) 
									CONCAT(Opis, ': ', ContentStr, IF((_Param_D = 'content.proc') AND (ContentProc > 0), CONCAT(' - ', ContentProcStr), '')) 
								SEPARATOR '\n')
							) AS ContentStr 
						FROM `t_receptury.select.final.for`
						;
					ELSEIF _Param_D LIKE 'porcja%' THEN
					--	zawartosc porcji i/lub procent
						IF @_Porcja_V > 0 THEN
						SELECT 
							CONCAT(
								Content_Masa_Porcja_Str, 
								'\n', 
								GROUP_CONCAT(
									CONCAT(Opis, ': ', Content_porcja_Str, IF((_Param_D = 'porcja.proc') AND (ContentProc > 0), CONCAT(' - ', ContentProcStr), '')) 
								SEPARATOR '\n')
							) AS ContentStr 
						FROM `t_receptury.select.final.for`
						;
						ELSE
						SELECT
						_VarStr('(brak definicji porcji)'
						) AS ContentStr 
						;
						END IF;
					ELSEIF _Param_D = 'procent' THEN
						SELECT 
							CONCAT(
								_Content_Masa_Str, 
								'\n', 
								GROUP_CONCAT(CONCAT(Opis, ': ', ContentProcStr) SEPARATOR '\n')
							) AS ContentStr 
						FROM `t_receptury.select.final.for` 
						WHERE ContentProc > 0
						;
					END IF;
				END IF;
				
				-- DROP TEMPORARY TABLE IF EXISTS `t_receptury.select.final.for`;
				END;
			END IF;
		
	END IF;	--	IF _MODE LIKE 'select.content.for.%' 
	
END$$

DROP PROCEDURE IF EXISTS `pr_receptury_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_receptury_tools`(
	IN _MODE VARCHAR(150),
	IN _GUID VARCHAR(38),	
	IN _Param_A VARCHAR(250),	
	IN _Param_B VARCHAR(250),	
	IN _Param_C VARCHAR(250),	
	IN _Param_D VARCHAR(250),	
	IN _Param_E VARCHAR(250),	
	IN _Param_F VARCHAR(250)	
)
SPROC: BEGIN
	DECLARE _SQLText TEXT;
	
	-- DECLARE _TerminRealizacji DATETIME;
	DECLARE _IdnTowr, _IdnNg VARCHAR(38);
	DECLARE _zawartosc, _Ilosc DOUBLE;
	DECLARE _IdnCechy, _MasaReceptury INTEGER;
	DECLARE _done BOOL DEFAULT FALSE;
		
	DECLARE _c_proc CURSOR FOR
	SELECT 
		_varDouble(REPLACE(l.value, ',', '.')), l.idncechy    -- t.nazwa, c.opis,
	FROM 
		`erp_cechy_links` l, TowrNg t, erp_cechy c
	WHERE l.objguid = t.guid
			AND l.idncechy = c.idn
			AND l.objguid = _Param_A AND l.value <> '0';   -- l.objguid  = 'CUKIER_K'; 
	-- ORDER BY nazwa  ;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'rcng.SELECT.Tree'
	    _GUID		    GUID naglowka receptury	    
		_Param_A		Ile surowca (towaru)
		
		CALL pr_receptury_tools('rcng.SELECT.Tree', '{3DBA71D0-B0CE-40E5-BBF0-257E5AC63F71}', '28', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'rcng.SELECT.Tree' THEN
		SET
			_IdnNg = _GUID,
			_Ilosc = _Param_A
		;
		SET
			_IdnTowr = IFNULL(
			(
				SELECT 
					rp.IdnTowr
					-- , rp.Priorytet, rp.*
				FROM `rcpo` rp
				WHERE
					rp.IdnNg = _IdnNg
					AND rp.Przychod = 1
				ORDER BY rp.Priorytet
				LIMIT 1
			), ''
			)
		;
		CALL pr_build_zlecprod_tools('INIT.from.TowrNg', _IdnTowr, _IdnNg, _Ilosc, '', '', '', '');		
		CALL pr_build_zlecprod_tools('BUILD.pr_build_zlecprod_po_e', _IdnTowr, '', 0, '', '', '', '');		
		CALL pr_build_zlecprod_tools('PREPARE.pr_build_zlecprod_RWPW_e.from.pr_build_zlecprod_po_e', _IdnTowr, '', 0, '', '', '', '');		
		
		SELECT
			t.ID AS Id,
			t.Parent_Id AS ParentId,
			-- t._LP AS lp,
			t.lp,
			t._Level AS `Level`,
			t.TowrSymb,
			tow.Nazwa AS Towar,
			sp.DisplayValue AS typ_zasobu_str, 
			t.przychod,
			ROUND(IF(t.Przychod != 0, t._Ilosc, -t._Ilosc), 6) AS Ilosc,
			j.Nazwa AS JednostkaStr,
			ROUND(IF(t.Przychod != 0, tow.waga*t._Ilosc, -tow.waga*t._Ilosc), 6) AS Masa,
			(ROUND(1*t._ilosc, 6) * IF(t.przychod=0, -1, 1)* t.cena_kalulacyjna) AS WyliczonaWart, 
			w.nazwa AS wydzialStr,
			w.symbol AS WydzialSymbol,
			Mg.Opis AS MagazynStr,
			t.idntowr,
				
			t.GUID,
			t.IdnNG,
			t.sr_cena_kalulacyjna,
			t.cena_kalulacyjna,
			t.idnng_merge,
			IFNULL(rn.Nazwa, '') AS Nazwa,
			_varDOUBLE(0.0) AS IsDrilled
		/*	
			,
			-- SUM(IF(t.Przychod = 0, tow.waga*t._Ilosc, 0)) AS SIlosc,
			t.towrsymb, 
			tow.Nazwa AS TowrNazwa,
			ROUND(IF(t.Przychod != 0, t._Ilosc, -t._Ilosc), 3) AS Ilosc,
			ROUND(IF(t.Przychod != 0, tow.waga*t._Ilosc, -tow.waga*t._Ilosc), 3) AS Masa,
			j.`Nazwa`,
			-- SUM(IF(t.Przychod = 0, t._Ilosc, 0)) AS SIlosc,
			t.* 
		*/
		FROM `t_zlecenia_prod_atom` t
		LEFT JOIN rcng rn ON rn.GUID = t.idnng_merge 
		INNER JOIN `TowrNg` tow ON tow.GUID = t.IdnTowr
		LEFT JOIN Jednostki j ON j.`idn` = t.jednostka
		LEFT JOIN wydzialy_produkcyjne w ON w.idn = t.wydzial 
		LEFT JOIN Magazyny Mg ON Mg.idnmagazynu = t.magazyn 
		LEFT JOIN slowniki_pierwotne sp ON sp.Value = t.typ_zasobu AND sp.Slownik_GUID = 'TYP_KART_TOW'

		WHERE
		TRUE
		-- and t._level = 0
		ORDER BY ID, Towar
		;
		
	END IF;	--	IF _MODE = 'rcng.SELECT.Tree'
				
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'add.next'
	    _GUID		    GUID nag��wka receptury	    
		_Param_A		GUID surowca (towaru)
		
		Dodaje kolejn� pozycje (surowiec) do tabelki z parametrami danej receptury
		CALL pr_receptury_tools('add.next', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', 'CUKIER_K', '', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'add.next' THEN
	
	    SET _MasaReceptury = 1000;		
		
		OPEN _c_proc;
				loop1: LOOP
					FETCH _c_proc INTO
						_zawartosc, _IdnCechy;
					IF _done THEN
						LEAVE loop1;
					ELSE
					  INSERT INTO rcpo_dane_tech(idnNg, IdnCechy, zawartosc, w_masie_gr)
					  VALUES (_GUID, _IdnCechy, _zawartosc * ROUND(_MasaReceptury / 100, 0), _MasaReceptury  )
					  ON DUPLICATE KEY UPDATE zawartosc = zawartosc + (_zawartosc * ROUND(_MasaReceptury / 100, 0) );					  
					END IF;
				END LOOP;
		CLOSE _c_proc;		
		
	END IF;	--	IF _MODE = 'add.next'	


/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'clear'
		_GUID		    GUID nag��wka receptury, dla kt�rej zerujemy parametry
		
		Czy�ci tabele `rcpo_dane_tech` dla wskazanej receptury
		CALL pr_receptury_tools('clear', '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}', '', '', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'clear' THEN
						
		DELETE FROM rcpo_dane_tech
		WHERE idnNg = _GUID; 
				
	END IF;	--	IF _MODE = 'clear'
	
IF _MODE LIKE 'component%' THEN
	
		IF _MODE LIKE '%prepare' THEN
		
			SET @rec_mag=0;
		
			DROP  TABLE IF EXISTS tb_rcng;
			CREATE TABLE tb_rcng (
			guid 	  VARCHAR(38),     				  
			symbol VARCHAR(38),			
			nazwa  VARCHAR(100),	
			ilosc DOUBLE(16,4),	
			podreceptura INT DEFAULT 0,
			opis  VARCHAR(50),
			idntowr VARCHAR(38),
			rcpo_guid VARCHAR(38),
			maxpoz INT
			);
			
			
			SET @merge = (SELECT COUNT(*)=1 FROM rcpo WHERE przychod=1 AND idntowr =IF(_PARAM_B='',_PARAM_A,_PARAM_B));
			
			
			IF @session.globals.Owner IN ('ATM') THEN
				SET @rec_mag = (SELECT IF(t.wyroznik='M',2,IF(t.wyroznik='W',1,IF(t.wyroznik='D',4,0))) FROM TowrNg t WHERE t.guid=_PARAM_A);
			END IF;
		
			INSERT INTO tb_rcng
			SELECT t.guid,r.symbol,r.nazwa,IF(_PARAM_B<>'',IFNULL(p.ilosc,0),1),IF(p.idnng_merge<>'',@merge,0),
			IF(p.guid IS NULL AND _PARAM_B<>'', 'Brak wybranego do zmiany towaru w recepturze','') AS opis,
			_Param_A,p.guid, (SELECT MAX(rp.lp) FROM rcpo rp WHERE rp.idnng=t.guid) AS maxpoz FROM t_rcng t
			LEFT JOIN rcng r ON t.guid=r.guid
			LEFT JOIN rcpo p ON t.guid=p.idnng AND p.przychod=0 AND p.idntowr=_Param_A
			ORDER BY opis,r.nazwa;
	
			SELECT * FROM tb_rcng;
	
	
		END IF;
		
		IF _MODE LIKE '%apply%' THEN
	
			IF _MODE LIKE '%add' THEN
		
				UPDATE tb_rcng  SET rcpo_guid= UUID();
			
				INSERT INTO rcpo (GUID,idnng,idntowr,przychod,ilosc,cena_kalulacyjna,priorytet,typ_zasobu,towrsymb,jednostka,magazyn,lp)
				SELECT t.rcpo_guid,t.guid,t.idntowr,0,t.ilosc,1,1,0,tr.symbol,tr.jednostka,@rec_mag,maxpoz+1 FROM tb_rcng t ,TowrNg tr WHERE t.idntowr=tr.guid
				AND t.ilosc>0;
			
			END IF;	
	
			IF _MODE LIKE '%remove' THEN
				DELETE FROM tb_rcng WHERE rcpo_guid IS NULL;	
				DELETE r.* FROM rcpo r,tb_rcng t  WHERE r.guid=t.rcpo_guid AND t.rcpo_guid IS NOT NULL;
			END IF;
			
			IF _MODE LIKE '%change' THEN
			
				SELECT t.guid,t.symbol,t.jednostka INTO @new_towr,@new_symbol,@new_jed FROM TowrNg t WHERE t.guid=_Param_B;
				SET @new_merge = (SELECT idnng FROM rcpo WHERE przychod=1 AND idntowr=_PARAM_B LIMIT 1);
				
			        DELETE FROM tb_rcng WHERE rcpo_guid IS NULL;	
					UPDATE rcpo r,tb_rcng t  SET 
					r.ilosc=t.ilosc,
					r.idntowr=@new_towr,
					r.towrsymb=@new_symbol,
					r.idnng_merge=IF(t.podreceptura=1,IFNULL(@new_merge,''),''),
					r.jednostka=@new_jed
				-- katcol0,katcol1,katcol2,katcol3,katcol4
				WHERE r.guid=t.rcpo_guid;
			END IF;	
	
			INSERT INTO `log` (useridn,czas,miejsce,czynnosc,info,GUIDlink,IDSession,osobaguid)	
			SELECT IFNULL(@session.Registration.UserLogin,@session.Registration.IdnUser),NOW(),5940,5,
			CONCAT('Zbiorcza modyfikacje receptur ',IF(_MODE LIKE '%add',' dodanie ','usuniecie ' ),'idntowr:',idntowr ),
			guid,@SYSTEM.Client.Session.GUID,@session.Registration.UserGUID FROM tb_rcng WHERE ilosc>0;
			
			SELECT CONCAT('Liczba zmodyfikowanych receptur:  ',(SELECT COUNT(*) FROM tb_rcng WHERE rcpo_guid IS NOT NULL), ' Przekalkuluj receptury!!!' ) AS MSG;
	
			DROP TEMPORARY TABLE IF EXISTS tb_rcng;
			SET @new_towr=NULL,@new_symbol=NULL,@new_jed=NULL,@new_merge=NULL;
	
	
		END IF;	
	
	END IF;
	
	
	IF _MODE LIKE 'towr_update' THEN
	
		UPDATE rcpo p, TowrNg t SET p.towrsymb=t.symbol WHERE p.idntowr=t.guid AND  t.guid=_Param_A;
	SELECT "Procedura zako�czona" AS MSG;

	END IF;
	
END$$



DROP PROCEDURE IF EXISTS `pr_receptury_kalkulacja`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pr_receptury_kalkulacja`(
	IN _MODE VARCHAR(150),
	IN _GUID VARCHAR(38),	
	IN _Param_A VARCHAR(250),	
	IN _Param_B VARCHAR(250),	
	IN _Param_C VARCHAR(250),	
	IN _Param_D VARCHAR(250)	
)
S_pr_receptury_kalkulacja: BEGIN
	DECLARE _IdnNG, _IdnTowr VARCHAR(38);
				IF _MODE = 'Create.t_receptuty_przeliczone' THEN
					CALL _IReg_Get('Produkcja', 'KalkulacjaTylkoDlaProduktuGlownego', 0, 'BOOLEAN', @session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego, TRUE);
					CALL _IReg_Get('Produkcja', 'Kalkukacja receptury wg. cen ewidencyjnych', 0, 'BOOLEAN', @session.HT.IR.Kalkukacja_receptury_wg._cen_ewidencyjnych, FALSE);
					DROP TEMPORARY TABLE IF EXISTS `t_receptuty_przeliczone`;
					CREATE TEMPORARY TABLE `t_receptuty_przeliczone` (
						`IdnNG` VARCHAR(38) NOT NULL DEFAULT '',
						`Calculated` BOOLEAN NOT NULL DEFAULT FALSE,
						PRIMARY KEY (`IdnNG`)
					)
					;
				END IF;

				IF _MODE LIKE 'CALC.RcNg.1.CHECK.AND.CALC.pozycje.z.idnNg_merge' THEN
					SET	
						_IdnNG = _GUID
					;
					
                                    set @.rcng.x := concat_ws('/', @.rcng.x, concat('IdnNG=', _IdnNG));
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 'check.1');
					SET 
						@.EXISTS.pozycje.ERR = 
						EXISTS(
							SELECT 
								r.IDN
							FROM `rcpo` r 
							WHERE 
								r.IdnNG = _IdnNG 
								AND r.idnNg_merge= _IdnNG		
						)
					;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 'check.2');
                    /*  cz. I zmiana 8 IV 2025 - rozdzielenie UNIONA bowiem generował
                        Error Code: 1137    Can't reopen table: 'rp'
					SET 
						@.EXISTS.pozycje.do.liczenia = 
						EXISTS(
							SELECT 
								r.IDN
							FROM `rcpo` r 
							WHERE 
								r.IdnNG = _IdnNG 
								AND IFNULL(r.idnNg_merge, '') != ''
								AND NOT r.idnNg_merge IN (SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp)
								
                            UNION	
							SELECT m.idnng 
							FROM rcpo r ,rcpo m 
							WHERE r.idntowr=m.idntowr AND m.przychod=1 
								AND r.idnng=_IdnNG 
								AND r.przychod=0 
								AND r.idnng_merge='' 	
								AND m.idnng <>_IdnNG		
								AND NOT m.idnng IN (SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp)		
						)
					;*/
                    DROP TEMPORARY TABLE IF EXISTS `t_merge_rcng`;
                    CREATE TEMPORARY TABLE `t_merge_rcng`
                    (
                        idnNg_merge VARCHAR(38) NOT NULL PRIMARY KEY
                    );
                    INSERT IGNORE INTO `t_merge_rcng`
                    (idnNg_merge)
                    SELECT 
                        r.idnNg_merge
                    FROM `rcpo` r 
                    WHERE 
                        r.IdnNG = _IdnNG 
                        AND IFNULL(r.idnNg_merge, '') != ''
                        AND IFNULL(r.idnNg_merge, '') != _IdnNG
                        AND NOT r.idnNg_merge IN (SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp)
                    ;
					SET @.EXISTS.pozycje.do.liczenia := ROW_COUNT();
                    INSERT IGNORE INTO `t_merge_rcng`
                    (idnNg_merge)
                    SELECT 
                        m.idnng AS idnNg_merge
                    FROM rcpo r ,rcpo m 
                    WHERE 
                        r.idntowr=m.idntowr AND m.przychod=1 
                        AND r.idnng=_IdnNG 
                        AND r.przychod=0 
                        AND r.idnng_merge='' 	
                        AND m.idnng <>_IdnNG		
                        AND NOT m.idnng IN (SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp)	
                    ;
					SET @.EXISTS.pozycje.do.liczenia := @.EXISTS.pozycje.do.liczenia + ROW_COUNT();
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 'check.3');
				
                                    set @.rcng.x := concat_ws('/', @.rcng.x, concat('do.liczenia=', @.EXISTS.pozycje.do.liczenia));
						
					IF @.EXISTS.pozycje.do.liczenia > 0 THEN
					--	Teraz stworz cursor na takie zagniezdzone pozycje i dla kazdej zawolaj rekurencyjnie
						BEGIN
							DECLARE done BOOL DEFAULT FALSE;
							DECLARE _idnNg_merge VARCHAR(200);
							DECLARE c_proc CURSOR FOR
                            /*  cz. I zmiana 8 IV 2025 - rozdzielenie UNIONA bowiem generował
                                Error Code: 1137    Can't reopen table: 'rp'
								SELECT 
									r.idnNg_merge
								FROM `rcpo` r 
								WHERE 
									r.IdnNG = _IdnNG 
									AND IFNULL(r.idnNg_merge, '') != '' <>_IdnNG
									AND NOT r.idnNg_merge IN (SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp)
							UNION	
								SELECT m.idnng FROM rcpo r ,rcpo m 
									WHERE r.idntowr=m.idntowr AND m.przychod=1 
									AND r.idnng=_IdnNG 
									AND r.przychod=0 
									AND r.idnng_merge='' 	
									AND m.idnng <>_IdnNG		
									AND NOT m.idnng IN (SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp)	
									
									
							;*/
								SELECT 
									r.idnNg_merge
								FROM `t_merge_rcng` r 
							;
							DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
							OPEN c_proc;
							loop_idnNg_merge: 
							LOOP
								FETCH c_proc INTO _idnNg_merge;
								IF done THEN
									LEAVE loop_idnNg_merge;
								END IF;
								set @.N.lp = IFNULL(@.N.lp, 0) + 1;
							--	REKURSJA	
                                    set @.rcng.x := concat_ws('/', @.rcng.x, concat('Recurs: IdnNG=', _idnNg_merge));
								CALL pr_receptury_kalkulacja('CALC.RcNg.1.Calculate', _idnNg_merge, '', '', '', '');
							END LOOP;
							CLOSE c_proc;
						END;
						
						
					END IF;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 'check.4');
				--	Teraz mam juz na pewno dzieci policzone - zatem pobierz sumaryczne wartosci
				END IF;
				
				IF _MODE = 'CALC.RcNg.1.Calculate' THEN
					/* *************************************************************************************************************************************************************
						Nalicza recepture
						CALL pr_receptury_kalkulacja('CALC.RcNg.1.Calculate', _IdnNG, '', '', '', '');
					**************************************************************************************************************************************************************/
                                set @.CALC.RcNg.1.Calculate := IFNULL(@.CALC.RcNg.1.Calculate, 0) + 1;
					SET	
						_IdnNG = _GUID
					;
					IF EXISTS((SELECT rp.IdnNg FROM `t_receptuty_przeliczone` rp WHERE rp.IdnNg = _IdnNG)) THEN
					--	Wyjdz jesli naliczona
						LEAVE S_pr_receptury_kalkulacja;
					END IF;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '1.calculate...');
				--	Nie ma w `t_receptuty_przeliczone` zatem wstaw ale ...
					INSERT IGNORE INTO `t_receptuty_przeliczone` (IdnNg) VALUES (_IdnNG);
				--	... UWAGA mamy teraz w `t_receptuty_przeliczone` guid tej receptury jednakze pole Calculated jest FALSE !!! Ustawimy je na koncu
					UPDATE rcng SET status_calc=''  WHERE guid=_IdnNG;
				--	Srawdz czy nie ma pod receptur w li�cie receptury _IdnNG	
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 'CALC.RcNg.1.CHECK...');
					CALL pr_receptury_kalkulacja('CALC.RcNg.1.CHECK.AND.CALC.pozycje.z.idnNg_merge', _IdnNG, '', '', '', '');
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 'OK');
				--	Teraz mam juz na pewno dzieci policzone - zatem pobierz sumaryczne wartosci
				--	Buduj tabelke pozycji rozchodowych zarowno 
				--	dla pozycji zwyklych na (-) wtedy pobierz ceny z kart towarowych z pola cenazkpnet jaki i 
				--	dla pozycji zagniezdzonych na (-) wtedy pobierz ceny z pozycji podreceptur
				
				
				IF @.EXISTS.pozycje.ERR THEN	
					SET @err_count=	@err_count+1;		
					UPDATE rcng n SET status_calc=CONCAT('ERR : ',NOW()) WHERE n.guid =_IdnNG;
					
				ELSE
                                    set @.rcng.x := concat_ws('/', @.rcng.x, 't_nalicz_rcpo...');
					DROP TEMPORARY TABLE IF EXISTS `t_nalicz_rcpo`;
					CREATE TEMPORARY TABLE `t_nalicz_rcpo` 
						SELECT
							r.GUID,
							IF(IFNULL(r.idnNg_merge, '') = '',
							--	zwykla pozycja towarowa
								(SELECT IF(@session.HT.IR.Kalkukacja_receptury_wg._cen_ewidencyjnych, t.cenaewid, t.cenazkpnet) FROM `TowrNg` t WHERE t.GUID = r.idntowr),
								(SELECT SUM(rs.cena_kalulacyjna) FROM `rcpo` rs  WHERE rs.IdnNG = r.idnNg_merge AND rs.przychod AND rs.IdnTowr = r.IdnTowr)
							--	pozycja zagniezdzona
							) AS cena_kalulacyjna,
							IF(IFNULL(r.idnNg_merge, '') = '',
							--	zwykla pozycja towarowa
								(SELECT t.sr_cena_zakupu FROM `TowrNg` t WHERE t.GUID = r.idntowr),
								(SELECT SUM(rs.sr_cena_kalulacyjna) FROM `rcpo` rs  WHERE rs.IdnNG = r.idnNg_merge AND rs.przychod AND rs.IdnTowr = r.IdnTowr)
							--	pozycja zagniezdzona
							) AS sr_cena_kalulacyjna
						FROM `rcpo` r 	
						WHERE
							r.IdnNG = _IdnNG  -- 'B16-2' 
							AND NOT r.przychod	--	na (-)
					;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '12 ');
					UPDATE `rcpo` r, `t_nalicz_rcpo` t
					SET
						r.cena_kalulacyjna = t.cena_kalulacyjna,
						r.sr_cena_kalulacyjna = t.sr_cena_kalulacyjna
					WHERE 
						r.GUID = t.GUID
					;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '13 ');
				--	Teraz pozycje na (+)
				
					SET
						@_Ile_Poz_PRZYCHOW = 0,
						@_Ile_Poz_PRZYCHOD_CALC = 0,
						@_Ile_Poz_ROZCHOD = 0,
						@_PULA_WAGA = 0,
						@_Ilosc_PRZYCHOD_CALC = 0
					;
					SELECT 	
						SUM(r.przychod <> 0) AS _Ile_Poz_PRZYCHOW,
						SUM(
							(r.przychod <> 0) AND
							IF(@session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego,
								r.Priorytet = -1,
								1
							)
						) AS _Ile_Poz_PRZYCHOD_CALC,
						SUM(NOT r.przychod) AS _Ile_Poz_ROZCHOD,
						
						SUM((r.przychod = 0) * r.cena_kalulacyjna * r.Ilosc) AS _kwota_kalulacyjna,
						SUM((r.przychod = 0) * r.sr_cena_kalulacyjna * r.Ilosc) AS _sr_kwota_kalulacyjna,
						
						-- SUM((r.przychod = 0) * r.cena_kalkulacyjna_ewid * r.Ilosc) AS _kwota_kalulacyjna_ewid,
						-- SUM((r.przychod = 0) * r.sr_cena_kalulacyjna_ewid * r.Ilosc) AS _sr_kwota_kalulacyjna_ewid,
						
						SUM((r.przychod != 0) * r.ilosc * IFNULL(t.cenaewid, 0)) AS _PULA_WAGA
					INTO
						@_Ile_Poz_PRZYCHOW,
						@_Ile_Poz_PRZYCHOD_CALC,
						@_Ile_Poz_ROZCHOD,

						@_kwota_kalulacyjna,
						@_sr_kwota_kalulacyjna,

						-- @_kwota_kalulacyjna_ewid,
						-- @_sr_kwota_kalulacyjna_ewid,

						@_PULA_WAGA
					FROM `rcpo` r, `TowrNg` t 
					WHERE
						r.IdnNG = _IdnNG  -- 'B16-2' 
						AND t.GUID = r.IdnTowr
					;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '14 ');
					IF @_Ile_Poz_PRZYCHOD_CALC <= 0 THEN
					 	LEAVE S_pr_receptury_kalkulacja;
					END IF;
					
					
					
					IF _Param_D='CHECK' THEN
						
						SELECT CONCAT('Hipotetyczna cena kalkulacyjna: ',ROUND((@_kwota_kalulacyjna / r.Ilosc) *IF(@session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego,(r.Priorytet = -1),1),2))  AS MSG
						FROM rcpo r
						WHERE
							r.IdnNG = _IdnNG 
							AND r.przychod
							AND IFNULL(r.idnNg_merge, '') = '' ;
						LEAVE S_pr_receptury_kalkulacja;
						
					END IF;
					
					IF @_Ile_Poz_PRZYCHOD_CALC > 1 THEN
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '15.1 ');
					--	rozrzuc ...
							CALL _rozrzucanie_tools('INIT', 0, '', '');
					--	1. najpierw cena_kalulacyjna 
							INSERT INTO `t_rozrzucanie` 
							(ID, Waga)
							SELECT
								r.GUID AS ID,
								IF(@session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego,
									(r.ilosc * IFNULL(t.cenaewid, 0) * (r.Priorytet = -1)),
									(r.ilosc * IFNULL(t.cenaewid, 0))
								) AS Waga
							FROM `rcpo` r, `TowrNg` t 
							WHERE
								r.IdnNG = _IdnNG  -- 'B16-2' 
								AND r.przychod != 0	--	na (+)
								AND t.GUID = r.IdnTowr
							;
						--	Rozrzuc cala kwote kosztow
							CALL _rozrzucanie_tools('CALC', @_kwota_kalulacyjna, '', '');
						--	przepisz do pozycji na (+)
							UPDATE `rcpo` r, `t_rozrzucanie` t
							SET
								r.cena_kalulacyjna = t.Value / r.Ilosc
							WHERE
								r.GUID = t.ID  -- AND r.IdnNG = _IdnNG  
							;
					--	zeruje bo ....
							UPDATE `t_rozrzucanie` t SET t.LP = 0, t.VALUE = 0;
					--	2. teraz sr_cena_kalulacyjna 
						--	Rozrzuc cala kwote kosztow
							CALL _rozrzucanie_tools('CALC', @_sr_kwota_kalulacyjna, '', '');
						--	przepisz do pozycji na (+)
							UPDATE `rcpo` r, `t_rozrzucanie` t 
							SET
								r.sr_cena_kalulacyjna = t.Value / r.Ilosc
							WHERE
								r.GUID = t.ID  -- AND r.IdnNG = _IdnNG  
							;
							
							-- CALL _rozrzucanie_tools('DROP', 0, '', '');
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '15.2 ');
						
					ELSE
					--	jest 1 rekord przychodowy lub mamy ustawiony klucz aby kalkulowac li tylko prod. glowny
					--	zatem wstaw tylko do pozycji przychodowej i juz
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '16.1 ');
						UPDATE `rcpo` r 
						SET
							r.cena_kalulacyjna = 
								(@_kwota_kalulacyjna / r.Ilosc) *
								IF(@session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego,
									(r.Priorytet = -1),
									1
								),
							r.sr_cena_kalulacyjna = 
								(@_sr_kwota_kalulacyjna / r.Ilosc) *
								IF(@session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego,
									(r.Priorytet = -1),
									1
								)
						WHERE
							r.IdnNG = _IdnNG  -- 'B16-2' 
							AND r.przychod
							AND IFNULL(r.idnNg_merge, '') = ''
						;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '16.2 ');
					
					END IF;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '2 ');
					
					UPDATE `TowrNg` t, `rcpo` r, `rcng` rn 
					SET 
						t.localtime = now(),
						t.cenazkpnet = IF(@session.globals.Owner IN ('AMICUS'),ROUND(r.cena_kalulacyjna,2),
							ROUND(r.cena_kalulacyjna * (1 + rn.narzut_kosztow/100), 2)),
						t.sr_cena_zakupu = 
							ROUND(r.sr_cena_kalulacyjna * (1 + rn.narzut_kosztow/100), 2),
						t.cena_minimalna = 
							IF(rn.narzutDoCenyMin > 0,
								ROUND(t.cenazkpnet*(100 + IFNULL(rn.narzutDoCenyMin, 0))/100 , 2),
								t.cena_minimalna
							)
					WHERE 
						rn.GUID = _IdnNG  -- 'B16-2' 
						AND r.przepisac_cene_kalk <> 0
						AND rn.nieaktywna = 0
						AND r.IdnNG = rn.GUID
						AND r.przychod
						AND t.GUID = r.idnTowr
					;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '3 ');
					IF @session.globals.Owner IN ('AXIS') THEN
					
						set @wart_1=0,@wart_2=0,@wart_3=0,@wart_4=0;
						CALL pr_build_t_zlecenia_prod_atom('Prepar.t_zlecenia_prod_atom.to.print',0,'','',_IdnNG,'',1,0,'','',FALSE,'','');	
						SELECT 
                            SUM(IF(typ_zasobu=0,ROUND(_ilosc*cena_kalulacyjna,2),0)) AS typ_0,
                            SUM(IF(typ_zasobu=1,ROUND(_ilosc*cena_kalulacyjna,2),0)) AS typ_1,
                            SUM(IF(typ_zasobu=6,ROUND(_ilosc*cena_kalulacyjna,2),0)) AS typ_6,
                            SUM(IF(typ_zasobu NOT IN (0,1,6),ROUND(_ilosc*cena_kalulacyjna,2),0)) AS typ_X,
                            SUM(IF(typ_zasobu=6,ROUND(_ilosc,2),0)) AS ilosc_typ_6
						INTO 
                            @wart_1,@wart_2,@wart_3,@wart_4,@wart_5
						FROM t_zlecenia_prod_atom 
                        WHERE   
                            przychod=0 AND recepturaguid='' 
                        ;
						UPDATE `rcng` rn
						SET 
							rn.wart_1=@wart_1,
							rn.wart_2=@wart_2,
							rn.wart_3=@wart_3,
							rn.wart_4=@wart_4,
							rn.wart_5=@wart_5
						WHERE 
							rn.GUID = _IdnNG
                        ;
						DROP TEMPORARY TABLE IF EXISTS t_zlecenia_prod_atom;
					END IF;
					
					IF @session.globals.Owner IN ('POLAM') THEN
					
						SET @wart_1=0,@wart_2=0,@wart_3=0,@wart_4=0;
						CALL pr_build_t_zlecenia_prod_atom('Prepar.t_zlecenia_prod_atom.to.print',0,'','',_IdnNG,'',1,0,'','',FALSE,'','');	
						SELECT 
                            SUM(IF(tw.wyroznik='R',ROUND(_ilosc*t.cena_kalulacyjna,2),0)) AS typ_R,
                            SUM(IF(tw.wyroznik<>'R',ROUND(_ilosc*t.cena_kalulacyjna,2),0)) AS typ_T
						INTO 
                            @wart_1,@wart_2
						FROM t_zlecenia_prod_atom t ,TowrNg tw
                        WHERE  
                            t.idntowr=tw.guid AND  t.przychod=0 AND t.recepturaguid='' 
                        ;
						UPDATE `rcng` rn
						SET 
							rn.wart_1=@wart_1,
							rn.wart_2=@wart_2
						WHERE 
							rn.GUID = _IdnNG
                        ;
						DROP TEMPORARY TABLE IF EXISTS t_zlecenia_prod_atom;
					END IF;
					
					
					IF @session.globals.Owner IN ('SIMEX') THEN
					
						SET @wart_1=0,@wart_2=0,@wart_3=0,@wart_4=0;
						CALL pr_build_t_zlecenia_prod_atom('Prepar.t_zlecenia_prod_atom.to.print',0,'','',_IdnNG,'',1,0,'','',FALSE,'','nie_tylko_merge');	
						SELECT 
                            SUM(IF(tw.wyroznik='RBH',ROUND(_ilosc*t.cena_kalulacyjna,2),0)) AS typ_R,
                            SUM(IF(tw.wyroznik<>'RBH',ROUND(_ilosc*t.cena_kalulacyjna,2),0)) AS typ_T
						INTO 
                            @wart_1,@wart_2
						FROM t_zlecenia_prod_atom t ,TowrNg tw
                        WHERE  
                            t.idntowr=tw.guid AND t.przychod=0 AND t.recepturaguid='' 
                        ;					
						UPDATE `rcng` rn
						SET 
							rn.wart_1=@wart_1,
							rn.wart_2=@wart_2
						WHERE 
							rn.GUID = _IdnNG
                        ;
						DROP TEMPORARY TABLE IF EXISTS t_zlecenia_prod_atom;
					
					END IF;
						
					
					SET @_Ilosc_PRZYCHOD_CALC = (SELECT SUM(p.ilosc) FROM rcpo p 
					WHERE p.przychod=1 
					AND IF(@session.HT.IR.KalkulacjaTylkoDlaProduktuGlownego,p.priorytet=-1,TRUE) AND p.idnng=_IdnNG);
					
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '4 ');
					UPDATE `rcng` rn
					SET 
						rn.wartosc_kalkulacyjna = @_kwota_kalulacyjna,
						rn.sr_wartosc_kalkulacyjna = @_sr_kwota_kalulacyjna,
						rn.ilosc_produktu_ng = @_Ilosc_PRZYCHOD_CALC,
						rn.status_calc=CONCAT('OK : ',NOW())
					WHERE 
						rn.GUID = _IdnNG  -- 'B16-2' 
					;
				--	UWAGA na koniec ustaw te recepture jako przeliczona
					UPDATE `t_receptuty_przeliczone` rp SET rp.Calculated = 1 WHERE rp.IdnNg = _IdnNG;
                                    set @.rcng.x := concat_ws('/', @.rcng.x, '6 ');
					
				--	{$IFDEF KalkulacjaCenySprzedWRecepturach}	****************************************************************************************
				--	de facto tylko dla AMICUS, ATM, POLAMREM
					IF @session.globals.Owner IN ('AMICUS', 'ATM', 'POLAMREM') THEN
					--	Temp
						DROP TEMPORARY TABLE IF EXISTS `t_klasy_KalkulacjaCenySprzedWRecepturach`;
						CREATE TEMPORARY TABLE `t_klasy_KalkulacjaCenySprzedWRecepturach` (
							`klasa` INT(11) NOT NULL DEFAULT '0',
							`MUL_cena_kalulacyjna` DOUBLE(16,4) DEFAULT 1,
							PRIMARY KEY (`klasa`)
						)
						;
						INSERT IGNORE INTO `t_klasy_KalkulacjaCenySprzedWRecepturach`
						(klasa, MUL_cena_kalulacyjna) VALUES(0, 1)
						;
						IF @session.globals.Owner = 'AMICUS' THEN
						--	Dodatkowo dla AMICUS dodaj sztucznie 2 wpis dla klasy = 2 i 4 (cena mnozona x 0.82
							INSERT IGNORE INTO `t_klasy_KalkulacjaCenySprzedWRecepturach`
							(klasa, MUL_cena_kalulacyjna) VALUES (2, 0.82), (4, 0.82)
							;
						END IF;
						
						DROP TEMPORARY TABLE IF EXISTS `t_KalkulacjaCenySprzedWRecepturach`;
						CREATE TEMPORARY TABLE `t_KalkulacjaCenySprzedWRecepturach` (
							`GUID` VARCHAR(36) NOT NULL,
							`idntowr` VARCHAR(38) NOT NULL DEFAULT '',
							`klasa` INT(11) NOT NULL DEFAULT '0',
							`cena_kalulacyjna` DOUBLE(16,4) DEFAULT NULL,
							`cena` DOUBLE(16,4) DEFAULT NULL,
							`waluta` INT(11) NOT NULL DEFAULT '0',
							`kurs` DOUBLE(16,6) DEFAULT 1,
							PRIMARY KEY (`GUID`)
						)
						;
						INSERT IGNORE `t_KalkulacjaCenySprzedWRecepturach`
						(GUID, idntowr, klasa, cena_kalulacyjna, waluta)
							SELECT
								MD5(CONCAT(r.idntowr, _VarStr(IF(tk.Klasa = 0, rn.klasacen, tk.Klasa)), _VarStr(rn.klasacen_waluta))) AS GUID,
								r.idntowr, 
								IF(tk.Klasa = 0, rn.klasacen, tk.Klasa) AS klasa, 
								ROUND(r.cena_kalulacyjna * (1 + rn.narzut_kosztow/100)*tk.MUL_cena_kalulacyjna, 2) AS cena_kalulacyjna, 
								rn.klasacen_waluta AS waluta
							FROM `t_klasy_KalkulacjaCenySprzedWRecepturach` tk, `rcpo` r, `rcng` rn  
							WHERE 
								rn.GUID = _IdnNG
								AND r.przepisac_cene_kalk <> 0
								AND rn.nieaktywna = 0
								AND r.IdnNG = rn.GUID
								AND r.przychod
						;
					--	Ustaw kurs i wg tego kursu cene przeliczon�
						UPDATE `t_KalkulacjaCenySprzedWRecepturach` t
						LEFT JOIN `kursy_calc` k ON k.idnwaluty2 = t.waluta AND k.idnwaluty1 = 0 
						SET 
							t.kurs = IFNULL(k.kurs, 1),
							t.cena = _Rnd2(t.cena_kalulacyjna*IFNULL(k.kurs, 1)) 
						;
					--	Usun z cennika wszystkie ceny dla tego towaru, klasy i waluty
						DELETE c FROM `cennik` c, `t_KalkulacjaCenySprzedWRecepturach` t
						WHERE 
							TRUE
							AND c.idntowr = t.idnTowr
							AND c.klasa = t.klasa
							AND c.waluta = t.waluta
						;
					-- i finalnie, wstaw do cennika nowe ceny
						INSERT IGNORE INTO `cennik` 
						(GUID, idntowr, klasa, cena, waluta) 
						SELECT
							t.GUID, t.idntowr, t.klasa, t.cena, t.waluta
						FROM `t_KalkulacjaCenySprzedWRecepturach` t
						;
					
					END IF;
				--	{$ENDIF KalkulacjaCenySprzedWRecepturach}	****************************************************************************************
				END IF;
				END IF;

				IF _MODE = 'CALC.RcNg.N.CREATE.t_kalkulacja_receptur_N_IdnNG' THEN
					DROP TEMPORARY TABLE IF EXISTS `t_kalkulacja_receptur_N_IdnNG`;
					CREATE TEMPORARY TABLE `t_kalkulacja_receptur_N_IdnNG` (
						`IdnNG` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'Klucz glowny - ID receptury',
						PRIMARY KEY (`IdnNG`)
					);
					DROP TEMPORARY TABLE IF EXISTS `t_kalkulacja_receptur_N_IdnTowr`;
					CREATE TEMPORARY TABLE `t_kalkulacja_receptur_N_IdnTowr` (
						`IdnTowr` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'Klucz glowny',
						PRIMARY KEY (`IdnTowr`)
					);
					
				END IF;
				
				IF _MODE = 'CALC.RcNg.N.Calculate' THEN
					CALL pr_receptury_kalkulacja('Create.t_receptuty_przeliczone', '', '', '', '', '');
					BEGIN
						DECLARE done BOOL DEFAULT FALSE;
						DECLARE _idnNg_1 VARCHAR(200);
						DECLARE c_proc CURSOR FOR
							SELECT 
								r.IdnNG
							FROM `t_kalkulacja_receptur_N_IdnNG` r
							WHERE 
								r.IdnNG != ''
						;
						DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
						OPEN c_proc;
						loop_kalkulacja_N_IdnTowr: 
						LOOP
							FETCH c_proc INTO _idnNg_1;
							IF done THEN
								LEAVE loop_kalkulacja_N_IdnTowr;
							END IF;
							CALL pr_receptury_kalkulacja('CALC.RcNg.1.Calculate', _idnNg_1, '', '', '', '');
						END LOOP;
						CLOSE c_proc;
					END;
					
					SET @ile_calc_all=(SELECT COUNT(*) FROM t_receptuty_przeliczone); 
					SET @ile_calc=(SELECT COUNT(*) FROM t_receptuty_przeliczone t,t_kalkulacja_receptur_N_IdnNG w WHERE t.IdnNG =w.IdnNG);
				
				END IF;
				
	
	
	IF _MODE LIKE 'KALKULACJA.INIT' THEN
		CALL pr_receptury_kalkulacja('Create.t_receptuty_przeliczone', '', '', '', '', '');
		CALL pr_receptury_kalkulacja('CALC.RcNg.N.CREATE.t_kalkulacja_receptur_N_IdnNG', '', '', '', '', '');

	END IF;
	
	IF _MODE LIKE 'KALKULACJA.dla.%' THEN
		CALL pr_receptury_kalkulacja('Create.t_receptuty_przeliczone', '', '', '', '', '');

		IF _MODE = 'KALKULACJA.dla.1.RcNg' THEN
			SET	
				_IdnNG = _GUID
			;
			SET @err_count=0;
                        set @.rcng.x := 'START';
			CALL pr_receptury_kalkulacja('CALC.RcNg.1.Calculate', _IdnNG, '', '', '', _Param_D);
                        set @.rcng.x := concat_ws('/', @.rcng.x, 'END');
			
			SELECT CONCAT('Liczba przeliczonych receptur:' ,
			(SELECT COUNT(*) FROM t_receptuty_przeliczone WHERE  calculated=1),'.',
			IF(@err_count,CONCAT(' Liczba błędów: ',@err_count),''))  AS MSG;
			
		END IF; --	_MODE LIKE 'KALKULACJA.dla.1.RcNg' THEN
		
		IF _MODE = 'KALKULACJA.dla.N.RcNg' THEN
			
			SET @ile_calc_all=0;
			SET @ile_calc=0;
			SET @err_count=0;
			
			CALL pr_receptury_kalkulacja('CALC.RcNg.N.Calculate', _IdnNG, '', '', '', '');
			
			SELECT CONCAT('Liczba przeliczonych receptur z widoku kartoteki: ',@ile_calc,'.', 
			' Liczba przeliczonych podreceptur: ', 
			@ile_calc_all-@ile_calc,'.',IF(@err_count,CONCAT('. Liczba b��d�w: ',@err_count),'')) AS MSG;
		END IF; --	_MODE LIKE 'KALKULACJA.dla.N.RcNg' THEN
		
		
		IF _MODE LIKE 'KALKULACJA.dla.%.IdnTowr%' THEN
		--	wszystkie mody towarowe
			IF _MODE LIKE 'KALKULACJA.dla.1.IdnTowr%' THEN
			--	dla 1 towaru musimy wstawic do lisy
				SET	
					_IdnTowr = _GUID
				;
				SET @_IdnNG = _IdnNG;
				TRUNCATE `t_kalkulacja_receptur_N_IdnTowr`;
				INSERT INTO `t_kalkulacja_receptur_N_IdnTowr`
				VALUES (_IdnTowr)
				;
			END IF;

			--	dla N toawrow liste juz mamy ... 
			--	a dla 1 towaru tez - bo wlasnie wstawilismy - zatem ... kialkulujemy
			
			IF _MODE LIKE 'KALKULACJA.dla.%.IdnTowr.ALL' THEN
			--	TYLKO jedna receptura dla tego towaru 
				INSERT IGNORE INTO `t_kalkulacja_receptur_N_IdnNG`
				SELECT 
					rn.GUID 
				FROM `t_kalkulacja_receptur_N_IdnTowr` t, `rcpo` rp, `rcng` rn 
				WHERE 
					rp.IdnTowr = t.IdnTowr 
					AND rp.przepisac_cene_kalk <> 0 
					AND rn.GUID = rp.IdnNG 
					AND NOT rn.Nieaktywna 
				ORDER BY rn.Priorytet, rp. lp 
				;
			ELSE
			--	wszystkie aktywne receptury dla tego towaru 
				INSERT IGNORE INTO `t_kalkulacja_receptur_N_IdnNG`
				SELECT 
					rn.GUID 
				FROM `t_kalkulacja_receptur_N_IdnTowr` t, `rcpo` rp, `rcng` rn 
				WHERE 
					rp.IdnTowr = t.IdnTowr 
					AND rp.przepisac_cene_kalk <> 0 
					AND rn.GUID = rp.IdnNG 
					AND NOT rn.Nieaktywna 
				ORDER BY rn.Priorytet, rp. lp 
				LIMIT 1
				;
			END IF;
		--	teraqz mamy liste receptur do kalkulacji (`t_kalkulacja_receptur_N_IdnNG`) zatem ... kialkulujemy
			CALL pr_receptury_kalkulacja('CALC.RcNg.N.Calculate', _IdnNG, '', '', '', '');
		END IF; --	_MODE LIKE 'KALKULACJA.dla.1.IdnTowr' THEN
		
		
	END IF; --	_MODE LIKE 'KALKULACJA.dla.%' THEN

	IF _MODE LIKE 'AD.KALKULACJA.dla.1.dzien' THEN
		-- wiele towarow
		CALL pr_receptury_kalkulacja('KALKULACJA.INIT', '', '', '', '', '');
		INSERT INTO `t_kalkulacja_receptur_N_IdnTowr` 
			SELECT 
				DISTINCT t.GUID 
			FROM `rcng` rn, `rcpo` rp, `TowrNg` t 
			WHERE 
				rp.Przychod 
				AND rp.przepisac_cene_kalk <> 0 
				AND rn.GUID = rp.IdnNG 
				AND !rn.Nieaktywna
				AND t.GUID = rp.IdnTowr
			;
		CALL pr_receptury_kalkulacja('KALKULACJA.dla.N.IdnTowr', '', '', '', '', '');
		
		
		SET @.ad_towr_kalkulacja_cen.Calc_Day = CURRENT_DATE();
		-- SET @.ad_towr_kalkulacja_cen.Calc_Day = '2017-10-22';
		SET @.ad_towr_kalkulacja_cen.Ext = NULL;
		-- SET @.ad_towr_kalkulacja_cen.Pre_ID = CONCAT(_VarStr(@.ad_towr_kalkulacja_cen.calc_day), '-', @.ad_towr_kalkulacja_cen.Ext);
		INSERT IGNORE INTO `ad_towr_kalkulacja_cen`
		(IdnTowr, calc_day, Ext, cenazkpnet, sr_cena_zakupu, cena_minimalna)
		
		SELECT 
			t.GUID AS IdnTowr,
			@.ad_towr_kalkulacja_cen.Calc_Day AS Calc_Day,
			@.ad_towr_kalkulacja_cen.Ext AS Ext,
			t.cenazkpnet, 
			t.sr_cena_zakupu, 
			t.cena_minimalna
		FROM `t_kalkulacja_receptur_N_IdnTowr` tk, `TowrNg` t
		WHERE
			tk.IdnTowr = t.GUID
		;
		
	END IF;
END$$

DELIMITER ;	


/*
$Log: pr_receptury_tools.sql,v $
Revision 1.54  2025/03/17 13:25:14  waliant
$SEND$ Warningi

Revision 1.53  2025/02/25 20:51:31  jmam
$send$ Warningi

Revision 1.52  2023/07/06 21:26:58  rafalg
no message

Revision 1.51  2023/06/27 06:02:19  apro
*** empty log message ***

Revision 1.50  2023/03/22 14:01:08  rafalg
nie_tylko_merge

Revision 1.49  2023/01/18 14:09:38  jmam
$SEND$

Revision 1.48  2022/12/21 10:17:56  rafalg
zmiany dla POLAM, naliczanie wart_1wart_2

Revision 1.47  2022/12/01 12:05:22  apro
collation

Revision 1.46  2022/11/17 08:41:06  rafalg
ostatnia cena nabycia bez narzutu dla AMICUS

Revision 1.45  2022/08/23 10:43:16  rafalg
ilosc_przychod
$SEND$

Revision 1.44  2022/07/29 12:39:51  rafalg
magazyn dla ATM

Revision 1.43  2022/07/26 06:07:39  rafalg
poprawka w komunikacie
$SEND$

Revision 1.42  2022/07/21 13:01:44  rafalg
naliczanie ilosci produktu w naglowku
$SEND$

Revision 1.41  2022/07/20 08:49:39  rafalg
naliczanie ilosci produktu w naglowku
$SEND$

Revision 1.40  2022/07/13 13:45:42  rafalg
no message

Revision 1.39  2022/06/27 12:43:22  rafalg
zmiany atm,axis,inni
$SEND$

Revision 1.38  2022/06/10 09:19:02  rafalg
zatwierdzona zbiorcza modyfikacja rec

Revision 1.37  2022/06/08 06:43:48  rafalg
Zbiorcza modyfikacja receptur

Revision 1.36  2018/05/10 11:03:59  jmam
$SEND$

Revision 1.35  2018/05/10 10:15:21  jmam
$SEND$

Revision 1.34  2018/04/24 08:08:07  jmam
$SEND$

Revision 1.33  2018/04/13 13:37:22  jmam
$SEND$

Revision 1.32  2018/04/13 13:36:40  jmam
$SEND$

Revision 1.31  2018/04/09 12:32:59  apro
$SEND$



DESC rcpo;
DESC `rcpo_dane_tech`;

SELECT 
IdnTowr, 10*SUM(r.Ilosc * IF(t.Waga = 0, 0.1, t.Waga)) AS masa_100g 
FROM `rcpo` r, `TowrNg` t 
WHERE
	idnng = '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}'
	AND r.przychod = 1
	AND t.GUID = r.IdnTowr
	AND r.Priorytet = -1
INTO
	@IdnTowr_Main, @Masa_100g.Receptury
;
DROP TEMPORARY TABLE IF EXISTS `t_rcpo_dane_tech_surow`;
CREATE TEMPORARY TABLE `t_rcpo_dane_tech_surow`
SELECT
@Masa_100g.Receptury AS Masa_Receptury_100g, r.Ilosc, 10*r.Ilosc * t.Waga AS masa_100g, r.IdnTowr, idnng_merge -- , r.*
FROM `rcpo` r, `TowrNg` t 
WHERE
	idnng = '{27D86763-CCBB-44E5-A03E-EF578EF0D85A}'
	AND przychod = 0
	AND t.GUID = r.IdnTowr
	
;
SELECT
*
FROM `t_rcpo_dane_tech_surow`
;

DROP TEMPORARY TABLE IF EXISTS `t_rcpo_dane_tech_surow_cechy`;
CREATE TEMPORARY TABLE `t_rcpo_dane_tech_surow_cechy`
SELECT
s.*, c.opis, l.value, 
_VarDouble(1*REPLACE(l.value, ',','.')/1) AS Content,
-- _VarDouble(1*REPLACE(l.value, ',','.')/100) AS Content_per_kg,
_VarDouble(1*REPLACE(l.value, ',','.')/1*Masa_100g) AS Ilosc_Cechy,
l.idncechy  
FROM `t_rcpo_dane_tech_surow` s, `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
WHERE
l.objguid = s.IdnTowr
AND l.idncechy = c.idn
AND cd.idn = c.IdnCechyDef
AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
;

SELECT
idncechy,   
COUNT(*) AS Ile,
SUM(Ilosc_Cechy) AS Ilosc_Cechy,
SUM(Ilosc_Cechy)/Masa_Receptury_100g AS Content
FROM `t_rcpo_dane_tech_surow_cechy`
GROUP BY idncechy  
;
SELECT 
c.opis, l.value, _VarDouble(1*REPLACE(l.value, ',','.')/100) AS Content, l.idncechy  
FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
WHERE 
l.objguid = 'CUKIER_K'
AND l.idncechy = c.idn
AND cd.idn = c.IdnCechyDef
AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
;

DESC `erp_cechy_def`;

SELECT t.nazwa, c.opis, l.value, _VarDouble(1*REPLACE(l.value, ',','.')*10) AS Content_per_kg, _VarDouble(1*REPLACE(l.value, ',','.')/100) AS Content, l.idncechy 
FROM `erp_cechy_links` l, TowrNg t, erp_cechy c
WHERE 
l.objguid = t.guid
  AND l.idncechy = c.idn
 AND l.objguid = 'CUKIER_K'
 ;
 
 SELECT * FROM Jednostki WHERE idn IN (3,10,4);
 
 
 
 <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<


	
	
	
SELECT 
c.opis, l.value, _VarDouble(1*REPLACE(l.value, ',','.')/100) AS Content, l.idncechy,
l.*  
FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
WHERE 
l.objguid = 'CUKIER_K'
AND l.idncechy = c.idn
AND cd.idn = c.IdnCechyDef
AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
;

DESC TowrNg '%rc%';


SHOW CREATE TABLE `erp_cechy_links`;
DROP TEMPORARY TABLE IF EXISTS `t_erp_cechy_links_e`;
CREATE TEMPORARY TABLE `t_erp_cechy_links_e` (
  `objGUID` VARCHAR(38) NOT NULL DEFAULT '',
  `objTable` VARCHAR(100) NOT NULL DEFAULT '',
  `IdnCechy` INT(11) NOT NULL DEFAULT '0',
  `Value` VARCHAR(100) DEFAULT NULL,
  `DataOd` DATE DEFAULT NULL COMMENT 'np. data nadania',
  `DataDo` DATE DEFAULT NULL COMMENT 'np. data waznosci',
  `UGrpGUID` VARCHAR(38) NOT NULL DEFAULT 'ALL',
  `Opis` TEXT,
  PRIMARY KEY (`IdnCechy`),
  UNIQUE KEY `klucz_unikalny` (`objGUID`,`objTable`,`IdnCechy`),
  KEY `objGUID` (`objGUID`),
  KEY `objTable` (`objTable`),
  KEY `IdnCechy` (`IdnCechy`)
) DEFAULT CHARSET=cp1250
;

INSERT IGNORE INTO `t_erp_cechy_links_e`
-- REPLACE `t_erp_cechy_links_e`
(objGUID, objTable, IdnCechy, VALUE)
SELECT
@IdnTowr_Main AS objGUID, 'TowrNg' AS objTable,
idncechy,   
-- COUNT(*) AS Ile,
-- SUM(Ilosc_Cechy) AS Ilosc_Cechy, 
SUM(Ilosc_Cechy)/Masa_Receptury_100g AS `Value`
FROM `t_rcpo_dane_tech_surow_cechy`
GROUP BY idncechy  
;

SELECT * FROM `t_erp_cechy_links_e`;

SELECT 
c.opis, l.value, _VarDouble(1*REPLACE(l.value, ',','.')/100) AS Content, l.idncechy,
l.*  
FROM `erp_cechy_links` l, `erp_cechy` c, `erp_cechy_def` cd
WHERE 
l.objguid = @IdnTowr_Main
AND l.idncechy = c.idn
AND cd.idn = c.IdnCechyDef
AND cd.nazwa LIKE 'Warto%(gram / 100 g)'
;


ALTER TABLE `rcpo`   
  ADD COLUMN `Time_Res` ENUM('BEGIN','DURING','END') NULL  COMMENT 'Okreslenie gest. czasowej jako podreceptury'
;
ALTER TABLE `rcng`   
  ADD COLUMN `Is_Time_Res` INT DEFAULT 0 NOT NULL COMMENT 'Gdy 1 to jako podreceptura moze miec Time_Res = BEGIN,DURING,END'
;
ALTER TABLE `planprodpo`
  ADD COLUMN `Deleted` INT DEFAULT 0 NOT NULL COMMENT 'Gdy 1 to pozycja do usuniecia'
;
ALTER TABLE `planprodpo_res`   
  ADD COLUMN `Time_Res` ENUM('BEGIN','DURING','END') NULL  COMMENT 'Okreslenie fazy zasobow'
;

TRUNCATE `planprodpo_res`;



SHOW CREATE TABLE `planprodpo_rcpo`;
CREATE TABLE `planprodpo_rcpo` (
  `pGUID` VARCHAR(200) NOT NULL DEFAULT '' COMMENT 'GUID w `planprodpo`',
  `rcng_GUID_merge` VARCHAR(38) DEFAULT '' COMMENT 'recptura zagniezdzona czyli odpowiednik `idnng_merge` w `rcpo`',
  `Time_Res` ENUM('BEGIN','DURING','END') NOT NULL DEFAULT 'BEGIN' COMMENT 'Okreslenie gest. czasowej jako podreceptury',
  `rcpoGUID` VARCHAR(38) NOT NULL DEFAULT '' COMMENT 'Klucz pozycji receptury - moze byc NULL - zonacza to ze rekord jest wpisany recznie',
  `ilosc` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
  `IloscIsConst` INT(11) NOT NULL DEFAULT '0' COMMENT 'Gdy 1 to ilosc nie jest przemnazana tylko brana jako stala z pola ilosc',
  `Update_Status` VARCHAR(250) NOT NULL DEFAULT '' COMMENT 'Zawsze "" gdy <> "" to specjalny Update',
  PRIMARY KEY (`pGUID`,`Time_Res`),
  UNIQUE KEY `pGUID_rcpoGUID` (`pGUID`,`rcpoGUID`)
) DEFAULT CHARSET=cp1250 COMMENT='planowane pozycje receptu dot. czasu - tzw gest. czasowej BEGIN,DURING,END'
;

CALL _Session_Refresh_Variables('', '', '');
CALL C_HT('', '', '', '');

*/
