DELIMITER $$

DROP PROCEDURE IF EXISTS `ht_pr_tool`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_pr_tool`(
	IN _MODE VARCHAR(150),
	IN _Param_A VARCHAR(50),	
	IN _Param_B VARCHAR(50),	
	IN _Param_C VARCHAR(50),	
	IN _Param_D VARCHAR(50),	
	IN _Param_E VARCHAR(50)	
)
BEGIN
	/*
	_MODE

	------------------------------------------------------------------------------------------------------------------------------------------------------------
		
	------------------------------------------------------------------------------------------------------------------------------------------------------------
	*/
	DECLARE CZM_Status_WYSLANE INTEGER DEFAULT 16;
	DECLARE _ParentGUID VARCHAR(38) DEFAULT '';
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'BUILD_ZLEC_INIT_ZESTAW'
		Zakłada konta w planie kont
		Parametry
			_Param_A	'T' - towar, 'Z' - zamowienie, 'P' - planer, 'O' Operacje
			_Param_B	GUID towaru, zamowienia, etc
			_Param_C	<> '' - oznacza każdy towar (produkt) osobno na zleceniu 
			_Param_D	<> '' - oznacza towary grupujemy 
		CALL ht_pr_tool('BUILD_ZLEC_INIT_ZESTAW', 'Z', 'GUID', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'BUILD_ZLEC_INIT_ZESTAW' THEN
		DROP TEMPORARY TABLE IF EXISTS `t_shell_zlec_pwrw`;
		CREATE TEMPORARY TABLE `t_shell_zlec_pwrw` (
			`class_id` VARCHAR(50) DEFAULT 'ALL',
			`class_descr` VARCHAR(250) DEFAULT 'Wszystkie',
            `Build_From` VARCHAR(20),
            `GUID` VARCHAR(38),
            `ParentGUID` VARCHAR(38),
            `RecepturaGUID` VARCHAR(38),
			`IdnTowr` VARCHAR(38),
            `TowrNazwa` VARCHAR(250),
			`Jednostka` INT(11),
			`Ilosc` DOUBLE(18,6),
			`Uwagi` TEXT,
			`osobaGUID` VARCHAR(38),
			`nr_seryjny_do_instancji` VARCHAR(250),
			`KontrGUID` VARCHAR(38),
			`numer_src` VARCHAR(38),
			`UwagiNg` TEXT,
			`terminrealizacjiNg` DATE,
			`idnmagazyn` INT(11),
            `ZamowienieGUID` VARCHAR(38),	
			-- dla Maxform:
			nrzamwkontr VARCHAR(30),
			zamawiajacy	VARCHAR(50),
			KEY GUID(GUID)
		)
		;
		IF _Param_A = 'Z' THEN	--	zamowienia
			INSERT INTO `t_shell_zlec_pwrw`
			(class_id, class_descr, Build_From, GUID, ParentGUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc, Uwagi, osobaGUID, 
			 nr_seryjny_do_instancji, KontrGUID, numer_src, UwagiNg, terminrealizacjiNg, idnmagazyn, ZamowienieGUID,
			 nrzamwkontr, zamawiajacy)
			SELECT
				IF(_Param_C = '', 'ALL', p.GUID) AS class_id,
				IF(_Param_C = '', 'Wszystkie', p.towrnazwa) AS class_descr,
				_Param_A,
				p.GUID, _Param_B, p.RecepturaGUID, p.idntowr, p.towrnazwa, p.Jednostka, 
				_varDOUBLE_NOTNULL(p.ilosc-p.zrealizowano-ifnull(p.zlecono,0)) AS Ilosc, 
				p.Uwagi, p.osobaGUID, p.nr_seryjny_do_instancji,
				n.kontrguid, n.nrzamw, n.Uwagi as UwagiNg, n.terminrealizacji, n.idnmagazyn, p.GUID,
				n.nrzamwkontr, n.zamawiajacy -- dla Maxform
			FROM `zmpo` p, `zmng` n			
			WHERE
				n.guid = _Param_B and 
				p.idnNg = n.guid
			;
		END IF;
		
		IF _Param_A = 'O' THEN	--	Operacje
			IF _Param_D != '' THEN	--	z grupowaniem
				INSERT INTO `t_shell_zlec_pwrw`
				(class_id, class_descr, Build_From, GUID, ParentGUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc, osobaGUID)
				SELECT
					IF(_Param_C = '', 'ALL', p.GUID) AS class_id,
					IF(_Param_C = '', 'Wszystkie', p.towrnazwa) AS class_descr,
					_Param_A,
					p.GUID, _Param_B, p.RecepturaGUID, p.idntowr, p.towrnazwa, p.Jednostka, 
					_varDOUBLE_NOTNULL(SUM(ilosc)) AS Ilosc, 
					p.osobaGUID
				FROM `oppo` p
				WHERE
					p.idnOper = _Param_B
				GROUP BY p.idntowr, p.osobaGUID	
				;
			ELSE
				INSERT INTO `t_shell_zlec_pwrw`
				(class_id, class_descr, Build_From, GUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc, osobaGUID)
				SELECT
					IF(_Param_C = '', 'ALL', p.GUID) AS class_id,
					IF(_Param_C = '', 'Wszystkie', p.towrnazwa) AS class_descr,
					_Param_A,
					p.GUID, p.RecepturaGUID, p.idntowr, p.towrnazwa, p.Jednostka, 
					_varDOUBLE_NOTNULL(ilosc) AS Ilosc, 
					p.osobaGUID
				FROM `oppo` p
				WHERE
					p.idnOper = _Param_B
				;
			END IF;
		END IF;

		IF _Param_A = 'T' THEN	--	Towar/Produkt
			INSERT INTO `t_shell_zlec_pwrw`
			(class_id, Build_From, GUID, RecepturaGUID, IdnTowr, TowrNazwa, Jednostka, Ilosc)
			SELECT
				'ALL' AS class_id,
				_Param_A,
				t.GUID, '', t.GUID AS idntowr, t.nazwa, t.Jednostka, 
				_VarDouble(0) AS Ilosc
			FROM `TowrNg` t
			WHERE
				t.GUID = _Param_B
			;
		END IF;
		
		DELETE FROM `t_shell_zlec_pwrw`
		WHERE
			Ilosc <= 0
		;
		DROP TEMPORARY TABLE IF EXISTS `t_shell_zlec_pwrw_group`;
		CREATE TEMPORARY TABLE `t_shell_zlec_pwrw_group` 
			SELECT
				class_id,
				_VarInt(COUNT(*)) AS Ile				
			FROM `t_shell_zlec_pwrw`	
			GROUP BY class_id	
		;
		
	END IF;	--	IF _MODE = 'BUILD_ZLEC_INIT_ZESTAW'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'BUILD_ZLEC_INIT_GET'
		Zwraca zestaw danych
		Parametry
			_Param_A	'1' - `t_shell_zlec_pwrw_group`, '' - `t_shell_zlec_pwrw`
			_Param_B	class_id (może być 'ALL' może być '' wszystkie)
		CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '1', '', '', '');		
		CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'BUILD_ZLEC_INIT_GET' THEN
		IF _Param_A != '' THEN
			SELECT * from `t_shell_zlec_pwrw_group`;			
		ELSE
			SELECT * from `t_shell_zlec_pwrw` WHERE IF(_Param_B = '', TRUE, class_id = _Param_B);
		END IF;
	END IF;	--	IF _MODE = 'BUILD_ZLEC_INIT_GET'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'BUILD_ZLEC_AFTER'
		Po budowaniu
		Parametry
			_Param_A	'T' - towar, 'Z' - zamowienie, 'P' - planer, 'O' Operacje
		CALL ht_pr_tool('BUILD_ZLEC_AFTER', 'Z', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'BUILD_ZLEC_AFTER' THEN
		IF _Param_A = 'Z' THEN	--	zamowienia
			UPDATE `t_shell_zlec_pwrw` zp
			SET
				zp.Ilosc = _varDOUBLE_NOTNULL((select sum(pp.Ilosc) from zmpo pp where pp.zamowienieGUID = zp.GUID))
			WHERE
				zp.Build_From = 'Z' 
			;
			
			UPDATE `t_shell_zlec_pwrw` zp, `zmpo` p
			SET
				p.zlecono = ifnull(p.zlecono,0) + zp.Ilosc
			WHERE
				zp.Build_From = 'Z' AND 
				p.GUID = zp.GUID
			;

			UPDATE `t_shell_zlec_pwrw` zp, `zmng` n
			SET 
				n.Status = 0
			WHERE 
				zp.Build_From = 'Z' AND 
				zp.ParentGUID IS NOT NULL AND
				n.GUID = zp.ParentGUID
			;
		END IF;
	END IF;	--	IF _MODE = 'BUILD_ZLEC_AFTER'
	
END$$

DELIMITER ;

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

CALL ht_pr_tool('BUILD_ZLEC_INIT_ZESTAW', 'Z', '{12E3DCB2-2DD5-48D8-84B8-FE16C58381BD}', '', '', '');		
CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '1', '', '', '');		
CALL ht_pr_tool('BUILD_ZLEC_INIT_GET', 'Z', '', '', '', '');		

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
*/

