DELIMITER $$

/* ************************************************************************************************************************************************************************
	E->O
	ERP do OPTI
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `opti_E_2_O`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `opti_E_2_O`(
	IN _MODE VARCHAR(250),
	IN _LastModify   VARCHAR(50),
	IN _RecUID       VARCHAR(38),
	INOUT _Result 	 VARCHAR(38)
	)
	
S_opti_E_2_O: BEGIN
	DECLARE _LastModifyTime, _date_c DATETIME;
	DECLARE _KontrGUID, _IdnTowr, _Oferta_ID, _Order_ID, _TYPE  VARCHAR(38);
	DECLARE _lastID INTEGER;
	DECLARE _done BOOL DEFAULT FALSE;
		
	DECLARE _pricelist_proc CURSOR FOR			
		SELECT  
			Oferta_ID, date_c
		FROM 
			`t_sync_cennik`
	;
	
	DECLARE _k_proc CURSOR FOR			
		SELECT  
			GUID, date_c
		FROM 
			`t_sync_kontrah`
	;
	
	DECLARE _t_proc CURSOR FOR			
		SELECT  
			GUID, date_c
		FROM 
			`t_sync_towar`
	;
		
	DECLARE _xxx_order_proc CURSOR FOR			
		SELECT  
			Order_ID, date_c
		FROM 
			`t_sync_xxx_orders`
	;
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;			

	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'KiT' LUB 'KT'
			Synchronizacja Kontrahentów i Towarów RAZEM
			CALL opti_E_2_O('KiT', '', '', @_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE IN ('KT', 'KiT') THEN
		CALL C_HT('', '', '', '');
		CALL opti_E_2_O('PRICELIST', '', '', _Result);
		CALL opti_E_2_O('K', '', '', _Result);
		CALL opti_E_2_O('T', '', '', _Result);
		CALL opti_E_2_O('OUTCOME_ORDER', '', '', _Result);		--	zlec. wyd.
		CALL opti_E_2_O('PURCHASE_ORDER', '', '', _Result);	--	zam. dd
	END IF;
	
	
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
			Ustawienia wstępne dla K i T
			Synchronizacja kartoteki kontrahentów/towarów (E20)
				Parametry
				_LastModify		Gdy '' to brana jest ostatnia data date_c ->  z SELECT MAX(date_c) FROM `opti_msg_e2o_header` WHERE type = CNTR_CARD/'ITEM_CARD'
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE IN ('K', 'T', 'PRICELIST*******', 'OUTCOME_ORDER', 'PURCHASE_ORDER') THEN
		SET
			_LastModify = IFNULL(_LastModify, '')
		;
		SET
			_LastModifyTime = 
				IF(_LastModify != '',
					_VarDateTime(_LastModify),
					opti_Get_DateTime('e2o.MAX', IF(_MODE = 'PRICELIST', 'PRICELIST', IF(_MODE = 'OUTCOME_ORDER', 'OUTCOME_ORDER', IF(_MODE = 'PURCHASE_ORDER', 'PURCHASE_ORDER', IF(_MODE = 'K', 'CNTR_CARD', 'ITEM_CARD')))), '1942-07-22 07:12','', '')
				)	
		;	
		
		SET _done = FALSE;			
		SET @_LastModifyTime = _LastModifyTime;
		
		
		IF _MODE = 'PRICELIST*******'  THEN	--	juz nieaktualne
		-- Cennik
		
			DROP TEMPORARY TABLE IF EXISTS `t_sync_cennik`;
			CREATE TEMPORARY TABLE `t_sync_cennik` AS
				SELECT
					o.IDN AS Oferta_ID,
					t.idn AS ITEM_CODE,
					k.idn AS CNTR_CODE,
					o.cena AS PRICE,
					DATE(o.dataoferty) AS PRICE_DATE,
					o.alocaltime AS date_c,
					o.warunek 
				FROM `oferty` o, `kontrah` k, `TowrNg` t 
				WHERE 
					o.kontrguid = k.guid 
					AND o.idntowr = t.guid 
					AND o.`alocaltime` > _LastModifyTime
				-- LIMIT 10	
			;  

			OPEN _pricelist_proc;
				loop_pricelist: LOOP
					FETCH _pricelist_proc INTO
						_Oferta_ID, _date_c;
					IF _done THEN
						LEAVE loop_pricelist;
					ELSE	
						CALL opti_E_2_O('PRICELIST.one.rec', _date_c, _Oferta_ID, _Result);
					END IF; 	
				END LOOP;
			CLOSE _pricelist_proc;
			
		END IF;		-- Cennik
		
		IF _MODE = 'K' THEN
		-- Kontrahenci
		
			DROP TEMPORARY TABLE IF EXISTS `t_sync_kontrah`;
			CREATE TEMPORARY TABLE `t_sync_kontrah` AS
				SELECT 
					k.GUID, k.localtime AS date_c 
				FROM `kontrah` k
				WHERE
					k.`localtime` > _LastModifyTime
					--	AND k.Dostawca = 0
					AND k.deleted = 0
					AND CONCAT(';',kategoria,';') LIKE "%;opti;%"
			;  
			
			OPEN _k_proc;
				loop1: LOOP
					FETCH _k_proc INTO
						_KontrGUID, _date_c;
					IF _done THEN
						LEAVE loop1;
					ELSE	
						CALL opti_E_2_O('K.one.rec', _date_c, _KontrGUID, _Result);
					END IF; 	
				END LOOP;
			CLOSE _k_proc;
			
		END IF;		-- Kontrahenci

		
		IF _MODE = 'T' THEN
		--	Towary

			DROP TEMPORARY TABLE IF EXISTS `t_sync_towar`;
			CREATE TEMPORARY TABLE `t_sync_towar` AS
				SELECT 
					t.GUID, t.localtime AS date_c
				FROM `TowrNg` t
				WHERE 
					t.`localtime` > _LastModifyTime
					AND t.deleted = 0 
					AND t.usluga = 0 
					AND t.promocja_status <> 2
			;  -- max(date_c) from opti_msg_e2o_header where type='CNTR_CARD' and status = 'F'
			
			OPEN _t_proc;	
				loop2: LOOP
					FETCH _t_proc INTO 
						_IdnTowr, _date_c;
					IF _done THEN
						LEAVE loop2;
					ELSE	
						CALL opti_E_2_O('T.one.rec', _date_c, _IdnTowr, _Result);
					END IF; 	
				END LOOP;
			CLOSE _t_proc;
			
		END IF;		-- Towary
		
		IF _MODE IN ('OUTCOME_ORDER', 'PURCHASE_ORDER')  THEN
			DROP TEMPORARY TABLE IF EXISTS `t_sync_xxx_orders`;
			
			IF _MODE = 'OUTCOME_ORDER' THEN
			-- zam. sprzedazy 
				CREATE TEMPORARY TABLE `t_sync_xxx_orders` AS
					SELECT
						n.GUID AS Order_GUID,
						n.IDN AS Order_ID,
						n.`localtime` AS date_c,
						n.IDN AS NUMBER,
						n.Rodzaj AS SUBTYPE,
						k.idn AS CNTR_CODE,
						IF(n.idnmagazyn = 101, 'MC', 'MS') AS WHSE_CODE,
						_varStr_NOTNULL(n.uwagi) AS REMARKS, 
						n.TerminRealizacji AS DEADLINE,
						n.nrzamw AS ORDER_NUMBER
					FROM `zmng` n, `kontrah` k
					WHERE 
						n.kontrguid = k.guid 
						AND n.`localtime` > _LastModifyTime
						AND n.Rodzaj = @ht.CZmOO
						AND n.idnmagazyn IN (101, 2)
					-- LIMIT 10	
				;  
			END IF;	
			
			IF _MODE = 'PURCHASE_ORDER' THEN
			-- zam. DD (do dostawców) 
				CREATE TEMPORARY TABLE `t_sync_xxx_orders` AS
					SELECT
						n.GUID AS Order_GUID,
						n.IDN AS Order_ID,
						n.`localtime` AS date_c,
						n.IDN AS NUMBER,
						n.Rodzaj AS SUBTYPE,
						k.idn AS CNTR_CODE,
						IF(n.idnmagazyn = 101, 'MC', 'MS') AS WHSE_CODE,
						_varStr_NOTNULL(n.uwagi) AS REMARKS, 
						n.TerminRealizacji AS DEADLINE,
						n.nrzamw AS ORDER_NUMBER
					FROM `zmng` n, `kontrah` k
					WHERE 
						n.kontrguid = k.guid 
						AND n.`localtime` > _LastModifyTime
						AND n.Rodzaj = @ht.CZmDD
						AND n.ilepozzreal = 0
					-- LIMIT 10	
				;  
			END IF;	
			
			OPEN _xxx_order_proc;
				loop_Outcome_order: LOOP
					FETCH _xxx_order_proc INTO
						_Order_ID, _date_c;
					IF _done THEN
						LEAVE loop_Outcome_order;
					ELSE	
						CALL opti_E_2_O(CONCAT(_MODE, '.one.rec'), _date_c, _Order_ID, _Result);
					END IF; 	
				END LOOP;
			CLOSE _xxx_order_proc;
			
		END IF;		-- zam. sprzedazy 
		
	END IF;		--	Kontrahenci i Towary

	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'PRICELIST.one.rec'
			Synchronizacja cen z ofert - prawd. do zamówień do dostawców
			_LastModify to _date_c
			CALL opti_E_2_O('PRICELIST.one.rec', '', '_Oferta_ID', @_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'PRICELIST.one.rec' THEN
		CALL opti_msg_e2o_header_REPLACE('PRICELIST', _LastModify, 'E', _lastID);
			
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID, `DATE`, C1, C2, C3, D1, F1)  
		SELECT 
			_lastID AS H_ID,  NOW() AS `DATE`, 
			'H' AS c1, 
			c.ITEM_CODE AS c2, 
			c.CNTR_CODE AS c3, 
			c.PRICE_DATE AS D1, 
			c.PRICE AS F1
		FROM 
			`t_sync_cennik` c
		WHERE
			c.Oferta_ID = _RecUID
		;		
			
		CALL opti_tools('E2O_set_status_for_ID', _lastID, 'T', '', '');	
	END IF;		-- Cennik
	
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'K.one.rec'
			Synchronizacja pojedynczego rekordu z kartoteki kontrahentów (E20)
			Parametry
			_RecUID			GUID kontrahenta
			_Result  		0 lub 1
			_LastModify to _date_c
			CALL opti_E_2_O('K.one.rec', '', '123', @E2O_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'K.one.rec' THEN

		DROP TEMPORARY TABLE IF EXISTS `t_one_kontrah`;
		CREATE TEMPORARY TABLE `t_one_kontrah` AS
			SELECT 
				k.GUID,
				k.localTime, 
				k.IDN AS CNTR_CODE,
				k.Nazwa,
				k.dostawca,
				k.odbiorca,
				k.nip, 
				k.regon, 
				_VarStr('') AS GLN,
				_VarStr('') AS COUNTRY,
				k.NazwaMiasta AS CITY,	
				k.zipcode AS POSTAL_CODE, 
				k.ulica AS STREET,			
				_VarStr('') AS STREET_NUMBER,
				_VarStr('') AS PHONE1,
				_VarStr('') AS PHONE2,
				_VarStr('') AS EMAIL,
				_VarStr('') AS CONTACT_PERSON,
				k.symbol AS CNTR_SYMBOL
			FROM `kontrah` k
			WHERE 
				k.GUID = _RecUID
		;
		
		CALL opti_msg_e2o_header_REPLACE('CNTR_CARD', _LastModify, 'E', _lastID);
		/*
		REPLACE INTO opti_msg_e2o_header
			(TYPE, date_c, STATUS)
		SELECT 
			'CNTR_CARD', t.`localtime`, 'E
			'
		FROM 
			`t_one_kontrah` t
		;	
			
		--	Ustal _lastID jako pole `ID` i `H_ID`
		SET _lastID = 0;
		SELECT LAST_INSERT_ID() INTO _lastID;	
		*/	
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID,  `DATE`, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17)  -- d1 = t.localtime    r_id = idn,
		SELECT 
			_lastID AS H_ID,  NOW() AS `DATE`, 
			'H' AS c1, 
			k.CNTR_CODE AS c2, 
			k.nazwa AS c3, 
			CASE 
				WHEN IFNULL(k.dostawca, 0) = 1 AND IFNULL(k.odbiorca, 0) = 1 THEN 'CS' 
				WHEN IFNULL(k.dostawca, 0) = 1 AND IFNULL(k.odbiorca, 0) = 0 THEN 'S' 
				WHEN IFNULL(k.dostawca, 0) = 0 AND IFNULL(k.odbiorca, 0) = 1 THEN 'C' 
			END 
			AS C4, 					--	(Type)
			k.nip AS C5, 
			k.regon AS C6, 
			k.GLN AS C7, 
			k.COUNTRY AS C8,		--	COUNTRY
			k.CITY AS C9,			--	CITY 
			k.POSTAL_CODE AS C10,	--	POSTAL_CODE
			k.STREET AS C11,		--	STREET
			k.STREET_NUMBER AS C12,	--	STREET_NUMBER
			k.PHONE1 AS C13,		--	PHONE1
			k.PHONE2 AS C14,		--	PHONE2
			k.EMAIL AS C15,			--	EMAIL
			k.CONTACT_PERSON AS C16,--	CONTACT_PERSON
			k.CNTR_SYMBOL AS C17	--	CNTR_SYMBOL
		FROM 
			`t_one_kontrah` k
		;		
			
		CALL opti_tools('E2O_set_status_for_ID', _lastID, 'T', '', '');	
		-- CALL opti_set_status_for_ID('', _lastID, 'T');	
		
	END IF;		--	_MODE = 'K.one.rec'
	
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'T.one.rec'
			Synchronizacja pojedynczego rekordu z kartoteki towarów (E20)
			Parametry
			_RecUID			GUID towaru
			_Result  		0 lub 1
			_LastModify to _date_c
			CALL opti_E_2_O('T.one.rec', '', 'IdnTowr', @E2O_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'T.one.rec' THEN
		DROP TEMPORARY TABLE IF EXISTS `t_one_towar`;
		CREATE TEMPORARY TABLE `t_one_towar` AS
			SELECT 
				t.GUID, 
				t.`localtime` AS date_c, 
				t.idn AS H_CODE, 
				t.nazwa AS H_NAME, 
				_VarStr('') AS H_GRP,
				_VarStr(IFNULL(k.Idn, '')) AS H_DEF_SUPP,
				_VarStr('') AS H_REMARKS,
				t.symbol AS H_ITEM_SYMBOL, 
				
				IFNULL(g.nazwa, '') AS D_CODE, 
				IFNULL(g.opis, '') AS D_NAME,
				_VarStr('1') AS D_BASE, 
				_VarStr('') AS D_BARCODE, 
				_VarInt(1) AS D_NUMERATOR,
				_VarInt(1) AS D_DENOMINATOR,

				IFNULL(p.nazwa, '') AS DD_CODE, 
				IFNULL(p.opis, '') AS DD_NAME,
				_VarStr('0') AS DD_BASE, 
				_VarStr('') AS DD_BARCODE, 
				_VarInt(t.przelicznik_l) AS DD_NUMERATOR,
				_VarInt(t.przelicznik_m) AS DD_DENOMINATOR,
				t.jednostka_pom, t.przelicznik_l, t.przelicznik_m
			FROM `TowrNg` t  
			LEFT JOIN `Jednostki` g ON t.jednostka = g.idn
			LEFT JOIN `Jednostki` p ON t.jednostka_pom = p.idn
			LEFT JOIN `kontrah` k ON k.GUID = t.dostawcaGUID
			WHERE 
				t.GUID = _RecUID
		;
		
		CALL opti_msg_e2o_header_REPLACE('ITEM_CARD', _LastModify, 'E', _lastID);
		/*
		REPLACE INTO opti_msg_e2o_header
			(TYPE, date_c, STATUS)
		SELECT 
			'ITEM_CARD', t.date_c, 'E'
		FROM 
			`t_one_towar` t
		;	
			
		--	Ustal _lastID jako pole `ID` i `H_ID`
		SET _lastID = 0;
		SELECT LAST_INSERT_ID() INTO _lastID;			
		*/	
		-- naglowek towaru	
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID, `DATE`, c1, c2, c3, c4, c5, c6, c7)  --  , c4, c5, c6, c7, c9, c10, c11    d1 = t.localtime  r_id = idn
		SELECT 
			_lastID, NOW(), 
			'H' AS C1, 
			t.H_CODE AS C2, 
			t.H_NAME AS C3, 
			t.H_GRP AS C4, 
			t.H_DEF_SUPP AS C5, 
			t.H_REMARKS AS C6, 
			t.H_ITEM_SYMBOL AS C7
		FROM 
			`t_one_towar` t
		;	

		-- dane jednostek miary            c1= 'D'
		-- jednostka podst. 			
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID, `DATE`, c1, c2, c3, c4, c5, f1, f2)   -- r_id = idn
		SELECT 
			_lastID, NOW(), 
			'D' AS C1, 
			D_CODE AS C2, 
			D_NAME AS C3, 
			D_BASE AS C4, 
			D_BARCODE AS C5, 
			D_NUMERATOR AS F1, 
			D_DENOMINATOR AS F2        
		FROM 
			`t_one_towar` t
		;
		
		-- i cd jednostek miary            c1= 'D'
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID, `DATE`, c1, c2, c3, c4, c5, f1, f2)   -- r_id = idn
		SELECT 
			_lastID, NOW(), 
			'D' AS C1, 
			DD_CODE AS C2, 
			DD_NAME AS C3, 
			DD_BASE AS C4, 
			DD_BARCODE AS C5, 
			DD_NUMERATOR AS F1, 
			DD_DENOMINATOR AS F2        
		FROM 
			`t_one_towar` t
		;
		CALL opti_tools('E2O_set_status_for_ID', _lastID, 'T', '', '');	
		-- CALL opti_set_status_for_ID('', _lastID, 'T');	
		
	END IF;		--	_MODE = 'T.one.rec'
	
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'XXX_ORDER.one.rec'
			Synchronizacja cen z ofert - prawd. do zamówień do dostawców
			_LastModify to _date_c
			CALL opti_E_2_O('XXX_ORDER.one.rec', '', '_Oferta_ID', @_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE LIKE '%_ORDER.one.rec' THEN
		SET _TYPE = IF(_MODE = 'OUTCOME_ORDER.one.rec', 'OUTCOME_ORDER', 'PURCHASE_ORDER');
		CALL opti_msg_e2o_header_REPLACE(_TYPE, _LastModify, 'E', _lastID);
		
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID, `DATE`, C1, C2, C3, C4, C5, C6, C7, D1)  
			SELECT 
				_lastID AS H_ID,  
				NOW() AS `DATE`, 
				'H' AS C1, 
				n.Order_GUID AS C2,		--	n.Order_GUID zawiera GUID zamowienia
				n.SUBTYPE AS C3,
				n.CNTR_CODE AS C4,
				n.WHSE_CODE AS C5,
				n.REMARKS AS C6,
				n.ORDER_NUMBER AS C7,
				n.DEADLINE AS D1
			FROM `t_sync_xxx_orders` n
			WHERE
				n.Order_ID = _RecUID
		;
		
		REPLACE INTO `opti_msg_e2o_detail`
			(H_ID, `DATE`, C1, C2, C3, F1)  
			SELECT 
				_lastID AS H_ID,  
				NOW() AS `DATE`, 
				'D' AS C1, 
				t.IDN AS C2, 			--	ITEM_CODE
				g.nazwa AS C3, 			--	UOM_CODE
				p.Ilosc AS F1
			FROM `t_sync_xxx_orders` n
			INNER JOIN `zmpo` p ON p.IdnNg = n.Order_GUID
			INNER JOIN `TowrNg` t ON t.GUID = p.IdnTowr
			LEFT JOIN `Jednostki` g ON g.idn = p.jednostka
			WHERE
				n.Order_ID = _RecUID
		;		
			
		CALL opti_tools('E2O_set_status_for_ID', _lastID, 'T', '', '');	
	END IF;		-- -- zam. sprzedazy 
	

END$$


/* ************************************************************************************************************************************************************************
	O->E
	OPTI do ERP 
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `opti_O_2_E`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `opti_O_2_E`(
	IN _MODE VARCHAR(250),
	IN _LastModify   VARCHAR(50),
	IN _RecUID       VARCHAR(38),
	IN _MsgType		 VARCHAR(50),
	INOUT _Result 	 VARCHAR(38)
	)

S_opti_O_2_E: BEGIN
	DECLARE _LastModifyTime DATETIME;
	DECLARE _lastID INTEGER;
	DECLARE _done BOOL DEFAULT FALSE;
		
	DECLARE _Rodzaj, _Rodzaj_Typ INTEGER;
	DECLARE
			_FROM,
			_ORDER_NUMBER,
			_NUMBER,
			_ORDER_GUID,
			_DOC_NUMBER,
			_SUBTYPE,
			_CNTR_CODE,
			_WHSE_CODE,
			_SRC_WHSE_CODE,
			_DST_WHSE_CODE,
			CODE_zmagazynu,
			CODE_domagazynu,
			CODE_idnmagazyn,
			_REMARKS,
			_ITEM_CODE,
			_ZmngGUID VARCHAR(200)
	;
	DECLARE _DATE_R, _DATE_PLANNED DATE;

	DECLARE _zmagazynu, _domagazynu, _idnmagazyn INTEGER;
	
	DECLARE _KontrGUID, _IdnTowr, _IdnOper, _GUIDzm, _oppo_GUID  VARCHAR(38);
	
	DECLARE _CenaNet DECIMAL(16,4);
	
	DECLARE _Tymczasowa BOOLEAN;
	DECLARE _I, _key_Id, _idnmag, _R_ID, _recID, _lp, _OperType INTEGER;
	
	DECLARE _KontrSymb, _H_CODE VARCHAR(38);
	DECLARE _nrzamw, _KodMag VARCHAR(30);
	DECLARE _nrdokmag VARCHAR(20);
	DECLARE  _uwagi VARCHAR(200);  
	-- DECLARE  _MsgType VARCHAR(100);  
	DECLARE  _DataPlan, _DataWystaw, _D1 DATE;
	DECLARE  _Qty  DOUBLE;
	DECLARE  _C2, _C3, _C4, _C5, _C6  VARCHAR(200);
	
		
	DECLARE _key_zamw_c CURSOR FOR			
	SELECT  
		id
	FROM `t_sync_zamw`
	;	
	
	DECLARE _poz_oper_cur CURSOR FOR				
		SELECT * FROM `t_poz_oper_cur`
	;
	/*
	SELECT 
		d.R_ID, 
		d.C2 AS ITEM_CODE, 
		t.guid AS IdnTowr,
		d.F1 AS QTY
	FROM `opti_msg_o2e_detail` d
	INNER JOIN `TowrNg` t ON t.IDN = d.C2
	WHERE 
		d.H_ID = _RecUID  
		AND d.C1 = 'D'
	;
	*/
	
	
	DECLARE _key_oper_c CURSOR FOR			
	SELECT  
		id, msg_type
	FROM `t_sync_oper`
	;		
	
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET _done = TRUE;			



	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'OPZM' LUB 'OPiZM'
			Synchronizacja Kontrahentów i Towarów RAZEM
			CALL opti_O_2_E('OPiZM', '', '', '', @_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE IN ('OPZM', 'OPiZM') THEN
		CALL opti_O_2_E('OP', '', '', '', _Result);
		--	CALL opti_O_2_E('ZM', 	'', '', '', _Result);
	END IF;
	
	
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
			Ustawienia wstępne dla OP i ZM
			Synchronizacja operacji i/lub zamówień
				Parametry
				PO CO????????? _LastModify		Gdy '' to brana jest ostatnia remotetime ->  z opng lub zmng
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE IN ('OP', 'ZM') THEN
		CALL C_HT('', '', '', '');
		CALL opti_tools('O2E_set_status_T_for_all_R', '', '', '', '');	
		SET 
			_LastModify = IFNULL(_LastModify, '')
		;
		/*
		PO CO ???????????
		SET
			_LastModifyTime = 
				IF(_LastModify != '',
					_VarDateTime(_LastModify),
					opti_Get_DateTime('ng.RemoteTime', IF(_MODE = 'OP', 'opng', 'zmng'), NOW(),'', '')
				)
		;	
		*/
		SET _done = FALSE;			
		
		IF _MODE = 'OP' THEN
		-- Operacje np. magazynowe
			DROP TEMPORARY TABLE IF EXISTS `t_sync_oper`;
			CREATE TEMPORARY TABLE `t_sync_oper` AS
				SELECT 
					id, `type` AS msg_type
				FROM opti_msg_o2e_header
				WHERE 
					`status` = 'T'
					--	`date_c` > _LastModifyTime 
					--	AND TYPE <> 'PURCHASE_ORDER' 
			;  -- max(date_c) from opti_msg_e2o_header where type='CNTR_CARD' and status = 'F'
			
			SET _done = FALSE;			
			
			OPEN _key_oper_c;	
				loopO: LOOP
					FETCH _key_oper_c INTO 
						_key_Id, _MsgType;
					IF _done THEN
						LEAVE loopO;
					ELSE
						CALL opti_tools('O2E_set_status_for_ID', _key_Id, 'R', '', '');	
						CALL opti_O_2_E('OP.one.rec', '', _key_Id, _MsgType, _Result);
						CALL opti_tools('O2E_set_status_for_ID', _key_Id, IF(_Result = 0, 'F', 'E'), '', '');	
						
					END IF; 	
				END LOOP;
			CLOSE _key_oper_c;
		
		END IF;		-- OP
		/*
		IF _MODE = 'ZM' THEN
		-- Zamówienia
			DROP TEMPORARY TABLE IF EXISTS `t_sync_zamw`;
			CREATE TEMPORARY TABLE t_sync_zamw AS
				SELECT 
					id 
				FROM opti_msg_o2e_header
				WHERE 
					`status` = 'T'
					-- `date_c` > _LastModify 
					AND TYPE = 'PURCHASE_ORDER' 
			;  -- max(date_c) from opti_msg_e2o_header where type='CNTR_CARD' and status = 'F'
			
			SET _done = FALSE;			
			
			OPEN _key_zamw_c;	
				loopZ: LOOP
					FETCH _key_zamw_c INTO 
						_key_Id;
					IF _done THEN
						LEAVE loopZ;
					ELSE	
						CALL opti_tools('O2E_set_status_for_ID', _key_Id, 'R', '', '');	
						CALL opti_O_2_E('ZM.one.rec', '', _key_Id, 'PURCHASE_ORDER', _Result);
						CALL opti_tools('O2E_set_status_for_ID', _key_Id, IF(_Result = 0, 'F', 'E'), '', '');	
						
					END IF; 	
				END LOOP;
			CLOSE _key_zamw_c;
		
		END IF;		-- ZM
		*/
		
	END IF;		--	Operacje i zamówienia
		
		
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'OP.one.rec'
			Synchronizacja pojedynczego rekordu z kartoteki kontrahentów (E20)
			Parametry
			_RecUID			ID komunikatu
			_MsgType		np. 'INCOME_REALISED'
			_Result  		0 lub 1
			CALL opti_O_2_E('OP.one.rec', '', '1234', 'PURCHASE_ORDER', @O2E_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'OP.one.rec' THEN
		SET _Result = 1;
		
		-- dane z nagłowkiem zamówienia - zawsze jeden rekord
		/*	
		SELECT R_id, C2, C3, C5, C6, D1 
		FROM 
			opti_msg_o2e_detail 
		WHERE H_ID =  _RecUID  AND C1 = 'H'
		INTO
			_recID, _nrzamw, _nrdokmag, _KontrSymb, _KodMag;  
		*/

		IF IFNULL(_MsgType, '') = '' THEN
			SET
				_MsgType = (SELECT h.Type FROM `opti_msg_o2e_header` h WHERE ID = _RecUID)
			;
		END IF;
		
		SET
			_Rodzaj =
			CASE _MsgType 
				WHEN 'PURCHASE_OFFER' THEN @ht.CZMWE	 	--  Zamówienie wewnętrzne - realizowane MM-ką
				WHEN 'INCOME_REALISED' THEN @ht.CPzPZ 	 	--	PZ (12)
				WHEN 'EXOFFSET_REALISED' THEN @ht.CMMMM 	--	MM (9)
				WHEN 'OUTCOME_REALISED' THEN @ht.CRzWZ		--	WZ (10)
				WHEN 'INTOUT_REALISED' THEN @ht.CRzRW 	 	--	RW
				WHEN 'DESTROY_REALISED' THEN 11 			--	Likwidacja ?  11 = @ht.CRzRW
				WHEN 'STOCK_REALISED' THEN @ht.CInwn		--	Inwentaryzacja ? (39) kiedys -101 
			END,
			_Rodzaj_Typ = IF(_MsgType IN ('DESTROY_REALISED', 'PURCHASE_OFFER'), 1, 0)
		;	
		SET @_Rodzaj = _Rodzaj, @_Rodzaj_Typ = _Rodzaj_Typ;
		CALL ht_rodzaj_calc(_Rodzaj);
		
		SET
			_ORDER_NUMBER = '',
			_FROM = IF(@ht.doc.Optyp = @ht.ZM, 'ZM', 'OP'),
			@T_PZWZ = _MsgType IN ('INCOME_REALISED', 'OUTCOME_REALISED'),
			@T_MM = _MsgType = 'EXOFFSET_REALISED',
			@T_ZMDD = _MsgType = 'PURCHASE_OFFER'
		;
		SET
			@T_ELSE = NOT @T_ZMDD AND NOT @T_MM AND NOT @T_PZWZ
		;	
		
		SELECT 
			R_id,
			_VarStr(
				IF(@T_PZWZ,
					C2, ''
				)
			) AS _ORDER_NUMBER,
			
			_VarStr(
				IF(_Rodzaj = 28,
					C2, ''
				)
			) AS _NUMBER,
			
			_VarStr(
				CASE _MsgType 	
					WHEN 'OUTCOME_REALISED' THEN CONCAT('WMS:', _Rodzaj, '/NR:', C2)
					WHEN 'INCOME_REALISED' THEN C2
					ELSE
					''
				END
			) AS _ORDER_GUID,
							-- (SELECT GUID FROM `zmng` WHERE nrzamw = C2 LIMIT 1), 
			_VarStr(
				IF(@T_PZWZ,
					C3, C2
				)
			) AS _DOC_NUMBER,
			
			_VarStr(
			 	IF(@T_PZWZ,
					C4, C3
				)
			) AS _SUBTYPE,
			
			_VarStr(
				IF(@T_PZWZ,
					C5, 
					IF(_Rodzaj = 28,
						C3,
						''
					)	
				)
			) AS _CNTR_CODE,
			
			_VarStr(
				IF(@T_PZWZ,
					C6, 
					IF(@T_MM,
						0,
						C4
					)
				)
			) AS _WHSE_CODE,
			
			_VarStr(
				IF(@T_MM,
					C4, 0
				)
			) AS _SRC_WHSE_CODE,
			
			_VarStr(
				IF(@T_MM,
					C5, 0
				)
			) AS _DST_WHSE_CODE,
			
			_VarStr(
				IF(_Rodzaj = 28,
					C5, ''
				)
			) AS _REMARKS,
			
			D1 AS _DATE_R,
			
			D1 AS _DATE_PLANNED,
			
			CONCAT('O2E_', MD5(CONCAT(H_ID, r_id, c1, c2, c3)) ) 
		FROM `opti_msg_o2e_detail`
		WHERE 
			H_ID =  _RecUID  
			AND C1 = 'H'
		INTO
			_recID,
			_ORDER_NUMBER,
			_NUMBER,
			_ORDER_GUID,
			_DOC_NUMBER,
			_SUBTYPE,
			_CNTR_CODE,
			_WHSE_CODE,
			_SRC_WHSE_CODE,
			_DST_WHSE_CODE,
			_REMARKS,
			_DATE_R, 
			_DATE_PLANNED, 
			_IdnOper		--	właściwie to nie potrzebuję tego -> patrz poniżej
		;
		SET
			_IdnOper = CONCAT('WMS:', _Rodzaj, '/NR:', _DOC_NUMBER),
			CODE_zmagazynu = 
				IF(@T_MM, 
					_SRC_WHSE_CODE, 
					IF(@ht.doc.MagTyp = @ht.mtRZ, 
						_WHSE_CODE,
						0
					)
				),	
			CODE_domagazynu =
				IF(@T_MM, 
					_DST_WHSE_CODE, 
					IF(@ht.doc.MagTyp = @ht.mtPZ, 
						_WHSE_CODE,
						0
					)	
				),
			CODE_idnmagazyn =
				IF(_Rodzaj = 28, 
					_WHSE_CODE, 
					0
				),
			_KontrGUID = 
				IFNULL(
					(SELECT k.GUID FROM `kontrah` k WHERE k.IDN = _CNTR_CODE), ''
				)
		;
		SET
			_zmagazynu = IF(CODE_zmagazynu = 'MP', 1, IF(CODE_zmagazynu = 'MS', 2, 0)),
			_domagazynu = IF(CODE_domagazynu = 'MP', 1, IF(CODE_domagazynu = 'MS', 2, 0)),
			_idnmagazyn = IF(CODE_idnmagazyn = 'MP', 1, IF(CODE_idnmagazyn = 'MS', 2, 0))
		;
		
		SET
		@jmam = '1',			-- test
		@_KontrGUID = _KontrGUID,
		@_Rodzaj = _Rodzaj,
		@_MsgType = _MsgType,
		@_zmagazynu = _zmagazynu, 
		@_domagazynu = _domagazynu
		
		
		;
		/*
		SELECT 
			_IdnOper,
			_Rodzaj,
			_DATE_R,
			_KontrGUID,
			@ht.doc.MagTyp,
			_WHSE_CODE,
			_zmagazynu,
			_domagazynu,
			CODE_zmagazynu,
			_SRC_WHSE_CODE,
			CODE_domagazynu,
			_DST_WHSE_CODE
		;
		*/
		CALL ht_vat_init('');
		IF _FROM = 'OP' THEN
			SET @OP = 'INSERT';
		--	Operacje
			
			CALL Opng_E_init('INSERT', _IdnOper, 'WMS', '1', ''); 	-- _Param_A jest tu != '' aby nie wywoływać selecta wewn. innej proc. _ng_e_rec_init
			CALL Opng_E_init_set('', _IdnOper, _Rodzaj, _Rodzaj_Typ, '', 3, _KontrGUID, _zmagazynu, _domagazynu, FALSE, 0, '', '', '', 0, '', '', '');
			
			-- BYŁO CALL opng_E_LetInsert('', _IdnOper, _Rodzaj, _Rodzaj_Typ, _KontrGUID, _zmagazynu, _domagazynu, FALSE, 0, '', '', '', '', '', '');
			CALL Opng_E_Run('before.run', _IdnOper, '', '', '');
			CALL Opng_E_Run('', _IdnOper, '', '', '');
			CALL Opng_E_Run('after.run', _IdnOper, '', '', '');
			
			SET @IdnOper = _IdnOper;
			UPDATE `opng_e` n 
			SET
				n.DataWystaw = _DATE_R,
				n.DataWplywu = _DATE_R,
				n.NrDokMag = _DOC_NUMBER,
				n.Info1 = CONCAT('WMS ID=' , _RecUID)
			WHERE
				n.GUID = _IdnOper
			;	
			--		SELECT * FROM opng_e WHERE GUID = @IdnOper; 

			-- 	LEAVE S_opti_O_2_E; 
			UPDATE `oppo_e` SET Ilosc = 0 WHERE IdnOper = _IdnOper;
		ELSE
		--	Zamówienia
			SET _Tymczasowa = (_MsgType = 'PURCHASE_OFFER');
			CALL Zmng_E_init('INSERT', _IdnOper, 'WMS', '1', ''); 	-- _Param_A jest tu != '' aby nie wywoływać selecta wewn. innej proc. _ng_e_rec_init
			CALL Zmng_E_init_set('', _IdnOper, _Rodzaj, _Rodzaj_Typ, '', 3, _KontrGUID, _idnmagazyn, _idnmagazyn, _Tymczasowa, 0, '', '', '', 0, '', '', '');
			-- BYŁO CALL zmng_E_LetInsert('', _IdnOper, _Rodzaj, _Rodzaj_Typ, _KontrGUID, _idnmagazyn, _idnmagazyn, _Tymczasowa, 0, '', '', '', _DATE_PLANNED, '', '');
			
			CALL zmng_E_Run('before.run', _IdnOper, '', '', '');
			CALL zmng_E_Run('', _IdnOper, '', '', '');
			CALL zmng_E_Run('after.run', _IdnOper, '', '', '');
			
			
			SET @IdnNg = _IdnOper;
			UPDATE `zmng_e` n 
			SET
				n.nrzamw = _ORDER_NUMBER,
				n.Info1 = CONCAT('WMS ID=' , _RecUID),
				terminrealizacji = _DATE_PLANNED
			WHERE
				n.GUID = _IdnOper
			;	
			UPDATE `zmpo_e` SET Ilosc = 0 WHERE IdnNg = _IdnOper;
		END IF;
		
		-- dane z pozycjami operacji 
		SET _done = FALSE;	
		IF _MsgType != 'STOCK_REALISED' THEN
			CALL opti_tools('OP.prepare.t_poz_oper_cur', _RecUID, _MsgType, _ORDER_NUMBER, _ORDER_GUID);
		ELSE
			CALL opti_tools('OP.prepare.t_poz_oper_cur', _RecUID, _MsgType, CURRENT_DATE(), _zmagazynu);
		END IF;
		
		SET _lp = 1;
		OPEN _poz_oper_cur;
		loopOppo: LOOP
			FETCH _poz_oper_cur INTO _R_ID, _ITEM_CODE, _IdnTowr, _Qty, _GUIDzm, _CenaNet;
			IF _done THEN
					LEAVE loopOppo;
			ELSE
				-- INSERT IGNORE INTO `oppo`
				SET
					_oppo_GUID	= CONCAT('O2E_', MD5(CONCAT(_R_ID, _IdnOper))),
					--	_IdnTowr	= IFNULL((SELECT t.guid FROM `TowrNg` t WHERE t.IDN = _H_CODE LIMIT 1), ''),
					_GUIDzm		= _ORDER_GUID
				;
				IF _FROM = 'OP' THEN
				--	Operacje
			SET @OP = _lp;
					SET @_COS_oppo_GUID = _oppo_GUID;
					CALL oppo_E_Let_Insert('IdnTowr', _oppo_GUID, _IdnOper, _IdnTowr, _Qty, _CenaNet, '', '', '', '');  -- dodawanie pozycji
					-- Było CALL oppo_e_LetInsert('IdnTowr', _oppo_GUID, _IdnOper, _IdnTowr, _Qty, _CenaNet, '', '', '', '');
					UPDATE `oppo_e` p 
					SET
						lp = _lp,
						-- POTEM!!!!!!!!!!! p.GUIDzm = _GUIDzm,
					--	na prośbę p.Janusza i ZANIM nie sprawdzimy co nie działa w naliczaniu cen (wywoływane jest to via CALL pozycje_cenyrabat_oppo_e('dla.IdnOper', _IdnOper, '', '', ''); ) 
					--	poniższe dwie linie wstawiamy aby ceny były na zero!!!
						p.CenaNet = 0,	
						p.CenaBrt = 0,
						p.DataWystaw = _DATE_R
					WHERE
						p.GUID = _oppo_GUID
					;	
					
				ELSE
				--	Zamówienia
					SET @_oppo_GUID = _oppo_GUID;
					CALL zmpo_E_Let_Insert('IdnTowr', _oppo_GUID, _IdnOper, _IdnTowr, _Qty, _CenaNet, '', '', '', '');  -- dodawanie pozycji
					-- Było CALL zmpo_e_LetInsert('IdnTowr', _oppo_GUID, _IdnOper, _IdnTowr, _Qty, _CenaNet, '', '', '', '');
				END IF;
				

				SET _lp = _lp + 1;
			END IF;	
		
		END LOOP;
		CLOSE _poz_oper_cur;
		
		-- Było CALL ng_vat_e_build_from_po('', _FROM, _IdnOper, '', '');
		IF _FROM = 'OP' THEN
		--	Operacje
		
			-- juz NIE CALL opng_vat_e('nalicz_opng_vat_e', _IdnOper);
			CALL opng_E_Let('Post', _IdnOper, '', '');
			-- Było CALL opng_E_LetPost('', _IdnOper, '', '');
			SET @OP = CONCAT(@OP, ' ', 'POST', ' ', _IdnOper);
		--	Na koniec Apply i End
			IF _MsgType != 'STOCK_REALISED' THEN
				SET @OP = CONCAT(@OP, ' ', 'APPLY');
				CALL opng_E_Let('ApplyAndClear', _IdnOper, '', '');
				/*
				BYŁO
				CALL opng_E_LetApply('', _IdnOper, '', '');
				CALL opng_E_LetCancel('', _IdnOper, '', '');
				CALL opng_E_LetEnd('', _IdnOper, '', '');
				*/
			ELSE
				CALL opng_E_Let('Clear', _IdnOper, '', '');
			END IF;
		ELSE
		--	Zamówienia
				
			CALL zmng_E_Let('Post', _IdnOper, '', '');
			-- Było CALL zmng_E_LetPost('', _IdnOper, '', '');
		--	Na koniec Apply i End
			CALL zmng_E_Let('ApplyAndClear', _IdnOper, '', '');
			/*
			BYŁO
			CALL zmng_E_LetApply('', _IdnOper, '', '');
			CALL zmng_E_LetCancel('', _IdnOper, '', '');
			CALL zmng_E_LetEnd('', _IdnOper, '', '');
			*/
		END IF;
		
		SET _Result = 0;
		
	END IF;		--	_MODE = 'OP.one.rec'
	
	/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
		'ZM.one.rec'
			Synchronizacja pojedynczego rekordu z kartoteki towarów (E20)
			Parametry
			_RecUID			ID komunikatu
			_MsgType		zawsze 'PURCHASE_ORDER'
			_Result  		0 lub 1
			CALL opti_O_2_E('ZM.one.rec', '', '1234', 'PURCHASE_ORDER', @O2E_Result);
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'ZM.one.rec' THEN
		
		SET _Result = 1;
		
		-- dane z nagłowkiem zamówienia - zawsze jeden rekord
		SELECT 
			R_id, C2, C3, C4, C5, D1, 
			CONCAT('O2E_', MD5(CONCAT(H_ID, r_id, c1, c2)) )  
		FROM `opti_msg_o2e_detail`
		WHERE 
			H_ID =  _RecUID  
			AND C1 = 'H'
		INTO
			_recID, _nrzamw, _KontrSymb, _KodMag, _uwagi, _DataPlan, _ZmngGUID
		;
				
		INSERT IGNORE INTO `zmng`
		SET
			GUID       		 = _ZmngGUID,
			rodzaj           = 28,  -- ZM DD
			kontrGUID  		 = (SELECT guid FROM kontrah WHERE symbol = _KontrSymb LIMIT 1),
		    idnmagazyn 		 = IF(_KodMag = 'MP', 1, 2),
			MagazynWys 		 = IF(_KodMag = 'MP', 1, 2),
			nrzamw     		 = _nrzamw,
			terminrealizacji = _DataPlan,
			uwagi      		 = _uwagi
		;
		
		-- dane z pozycjami zamówienia
		SET _done = FALSE;	
		
		SET _lp = 1;
		OPEN _poz_oper_cur;
		loopZK: LOOP
			FETCH _poz_oper_cur INTO 
					_R_ID, _H_CODE, _Qty;
			IF _done THEN
					LEAVE loopZK;
			ELSE
				INSERT IGNORE INTO zmpo
				SET
					GUID       		 = CONCAT('O2E_', MD5(CONCAT(r_id, _ZmngGUID) ) ),
					idnNG            = _ZmngGUID,
					lp               = _lp, 
					_IdnTowr  		 = IFNULL((SELECT t.guid FROM `TowrNg` t WHERE t.IDN = _H_CODE LIMIT 1), ''),
					ilosc 		 	 = _Qty,
					iloscdo 		 = _Qty					
				;
				
				SET _lp = _lp + 1;
			END IF;	
		
		END LOOP;
		CLOSE _poz_oper_cur;
		
		SET _Result = 0;
	END IF;		--	_MODE = 'ZM.one.rec'

END$$


/* ************************************************************************************************************************************************************************
	Narzędzia 
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `opti_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `opti_tools`(
	IN _MODE VARCHAR(250),
	IN _Param_A VARCHAR(255),
	IN _Param_B VARCHAR(255),
	IN _Param_C VARCHAR(255),
	IN _Param_D VARCHAR(255)
	)
BEGIN

	DECLARE _RecUID       VARCHAR(38);
	DECLARE _MsgType		 VARCHAR(50);
	DECLARE _Date, _DataBO DATETIME;
	DECLARE
			_ORDER_NUMBER,
			_ORDER_GUID,
			_Mag
			VARCHAR(200)
	;
	
	
	IF _MODE = 'E2O_set_status_for_ID' THEN
	--	Ustawia w rekordzie _Param_A tabeli `opti_msg_e2o_header` status na _Param_B
		UPDATE `opti_msg_e2o_header` h
		SET
			h.status = _Param_B
		WHERE 
			h.id = _Param_A
		;		
	END IF;		--	_MODE = 'E2O_set_status_for_ID'
	
	IF _MODE = 'O2E_set_status_for_ID' THEN
	--	Ustawia w rekordzie _Param_A tabeli `opti_msg_e2o_header` status na _Param_B
		UPDATE `opti_msg_o2e_header` h
		SET
			h.status = _Param_B
		WHERE 
			h.id = _Param_A
		;		
	END IF;		--	_MODE = 'O2E_set_status_for_ID'
	
	IF _MODE = 'O2E_set_status_T_for_all_R' THEN
	--	Ustawia w rekordach tabeli `opti_msg_e2o_header` status na T gdy jest R i jest starszy niz 1 1/2 godziny
		UPDATE `opti_msg_o2e_header` h
		SET
			h.Status = 'T'
		WHERE 
			TRUE
			AND h.Status = 'R'
			AND DATE_ADD(date_c, INTERVAL 90 MINUTE) < NOW()
		;
	END IF;		--	_MODE = 'O2E_set_status_T_for_all_R'

	IF _MODE = 'GET.MIN.Date_C' THEN
		SELECT 
			opti_Get_DateTime('e2o.MIN', '', NOW(),'', '') AS Min_Date_C
		;	
	END IF;		--	_MODE = 'GET.MIN.Date_C' 
	
	IF _MODE = 'PREPARE.opti_msg_e2o_header.STATUS=T' THEN
	--	pobierz wszystkie ze statusem 'T'
		DROP TEMPORARY TABLE IF EXISTS `t_opti_msg_e2o_header_STATUS_T`;
		CREATE TEMPORARY TABLE `t_opti_msg_e2o_header_STATUS_T` AS
			SELECT 
				_VarInt(0) Disabled,
				h.* 
			FROM `opti_msg_e2o_header` h
			WHERE 
				h.Status = 'T'
		;	
	END IF;		--	_MODE = 'PREPARE.opti_msg_e2o_header.STATUS=T'
	
	IF _MODE = 'SELECT.opti_msg_e2o_header.STATUS=T' THEN
	--	pobierz wszystkie ze statusem 'T'
		SELECT
			h.* 
		FROM `t_opti_msg_e2o_header_STATUS_T` h
		WHERE
			NOT h.Disabled
		;
	END IF;		--	_MODE = 'SELECT.opti_msg_e2o_header.STATUS=T'
	
	IF _MODE = 'SELECT.opti_msg_e2o_detail.STATUS=T' THEN
	--	pobierz wszystkie ze statusem 'T'
		SELECT 
			d.* 
		FROM `t_opti_msg_e2o_header_STATUS_T` h
		INNER JOIN `opti_msg_e2o_detail` d ON d.H_ID = h.ID
		WHERE
			NOT h.Disabled
		;	
	END IF;		--	_MODE = 'SELECT.opti_msg_e2o_detail.STATUS=T'
	
	IF _MODE = 'DISABLE.opti_msg_e2o_detail.for.ID' THEN
	--	ustaw Disabled dla ID = _Param_A
		UPDATE `t_opti_msg_e2o_header_STATUS_T` t
		SET
			t.Disabled = 1
		WHERE 
			t.ID = _Param_A
		;		
	END IF;		--	_MODE = 'FINISH.opti_msg_e2o_header.STATUS=T'
	
	IF _MODE = 'FINISH.opti_msg_e2o_header.STATUS=T' THEN
	--	ustaw wszystkie na 'C' które mają 'T'
		UPDATE `t_opti_msg_e2o_header_STATUS_T` t, `opti_msg_e2o_header` h
		SET
			h.status = 'C'
		WHERE 
			t.ID = h.ID
		;		
	END IF;		--	_MODE = 'FINISH.opti_msg_e2o_header.STATUS=T'

	IF _MODE = 'PREPARE.opti_msg_o2e_header.temp' THEN
	--	tempy dla nagłówka i pozycji - Stwórz pustego tempa nagłowków i pustego tempa detali
		DROP TEMPORARY TABLE IF EXISTS `t_opti_msg_o2e_header`;
		CREATE TEMPORARY TABLE `t_opti_msg_o2e_header` AS
			SELECT 
				h.* 
			FROM `opti_msg_o2e_header` h
			WHERE 
				FALSE
		;	
		DROP TEMPORARY TABLE IF EXISTS `t_opti_msg_o2e_detail`;
		CREATE TEMPORARY TABLE `t_opti_msg_o2e_detail` AS
			SELECT 
				d.* 
			FROM `opti_msg_o2e_detail` d
			WHERE 
				FALSE
		;	
		/* potem
		SELECT * FROM `t_opti_msg_o2e_header`;
		SELECT * FROM `t_opti_msg_o2e_detail`;
		*/
	END IF;		--	_MODE = 'PREPARE.opti_msg_o2e_header.temp'
	
	IF _MODE = 'AFTER.APPLY.opti_msg_o2e_header.temp' THEN
	--	usun z tempa naglowkowego te złączone z normalnymi nagłokami (czyli istniejące)
		DELETE t FROM `t_opti_msg_o2e_header` t, `opti_msg_o2e_header` h
		WHERE
			t.ID = h.ID
		;	
	--	usun z tempa detali te złączone z normalnymi nagłokami (czyli istniejące)
		DELETE t FROM `t_opti_msg_o2e_detail` t, `opti_msg_o2e_header` h
		WHERE
			t.H_ID = h.ID
		;	
	--	Ustaw wszystkim Status T -> patrz WHERE w CREATE TEMPORARY TABLE `t_sync_oper` 
		UPDATE `t_opti_msg_o2e_header` SET STATUS = 'T';
	--	wstaw do normalnych naglowkow tempa naglówków
		INSERT IGNORE INTO `opti_msg_o2e_header`
			SELECT * FROM `t_opti_msg_o2e_header`
		;	
	--	wstaw do normalnych detali tempa detali
		INSERT IGNORE INTO `opti_msg_o2e_detail` 
			SELECT * FROM `t_opti_msg_o2e_detail`
		;	
	END IF;		--	_MODE = 'AFTER.APPLY.opti_msg_o2e_header.temp'


	IF _MODE = 'OP.prepare.t_poz_oper_cur' THEN
	
		/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
			'OP.prepare.t_poz_oper_cur'
				Synchronizacja pojedynczego rekordu z kartoteki kontrahentów (E20)
				Parametry
				_Param_A	_RecUID			ID komunikatu
				_Param_B	_MsgType		np. 'INCOME_REALISED'
				_Param_C	_ORDER_NUMBER  	Numer zam
				_Param_D	_ORDER_GUID  	GUID zam lub Magazy inwentaryzacyjny
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '74', 'INCOME_REALISED', '_ORDER_NUMBER', '_ORDER_GUID');
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '74', 'STOCK_REALISED', '2012-06-27', '2');
				
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '74', 'INCOME_REALISED', '002741/02', '');
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '74', 'INCOME_REALISED', '002751/02', '');
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '74', 'OUTCOME_REALISED', '002741/02', '');
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '67', 'OUTCOME_REALISED', '', '{CB07EF45-C57A-4C93-B083-9C2CED38D1E7}');
				CALL opti_tools('OP.prepare.t_poz_oper_cur', '74', 'STOCK_REALISED', '2012-06-27', '2');
					SELECT * FROM `t_poz_oper_cur`;
	------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
		DROP TEMPORARY TABLE IF EXISTS `t_poz_oper_cur`;
		SET 
			_RecUID = _Param_A,
			_MsgType = _Param_B,
			_ORDER_NUMBER = _Param_C,
			_Date = _Param_C,
			_ORDER_GUID = _Param_D,
			_Mag = _Param_D
		;
		IF _MsgType != 'STOCK_REALISED' THEN
			IF _MsgType = 'INCOME_REALISED' AND IFNULL(_ORDER_NUMBER, '') != '' THEN
				SET 
					_ORDER_GUID = _VarStr_NotNull((
						SELECT z.GUID FROM `zmng` z WHERE z.Nrzamw = _ORDER_NUMBER LIMIT 1
					))
				;
			END IF;
			
			CREATE TEMPORARY TABLE `t_poz_oper_cur`
				SELECT 
					d.R_ID, 
					d.C2 AS ITEM_CODE, 
					t.guid AS IdnTowr,
					d.F1 AS QTY,
					_VarStr_NotNull(_ORDER_GUID) AS _ORDER_GUID,
					_VarDecimal(0) AS CenaNet
				FROM `opti_msg_o2e_detail` d
				INNER JOIN `TowrNg` t ON t.IDN = d.C2
				WHERE 
					d.H_ID = _RecUID
					AND d.C1 = 'D'
			;
			IF _MsgType IN ('INCOME_REALISED', 'OUTCOME_REALISED') AND IFNULL(_ORDER_GUID, '') != '' THEN
				IF _ORDER_GUID != '' THEN
					UPDATE `t_poz_oper_cur` t, `zmpo` z 
					SET
						t.CenaNet = z.CenaNet
					WHERE
						z.IdnNg = _ORDER_GUID
						AND
						z.IdnTowr = t.IdnTowr
					;	
				END IF;
			END IF;
		ELSE
		--	INWENTARYZACJA
			DROP TEMPORARY TABLE IF EXISTS `t_poz_oper_cur_1`;
			CREATE TEMPORARY TABLE `t_poz_oper_cur_1`
				SELECT 
					d.R_ID, 
					d.C2 AS ITEM_CODE, 
					t.guid AS IdnTowr,
					d.F1 AS QTY,
					_VarStr('') AS _ORDER_GUID,
					_VarDecimal(0) AS CenaNet
				FROM `opti_msg_o2e_detail` d
				INNER JOIN `TowrNg` t ON t.IDN = d.C2
				WHERE 
					d.H_ID = _RecUID
					AND d.C1 = 'D'
			;
			ALTER TABLE `t_poz_oper_cur_1` ADD KEY IdnTowr (IdnTowr);
			
			SELECT DATE(h.date_c) FROM `opti_msg_o2e_header` h WHERE h.ID = _RecUID INTO _Date; 
			SELECT MAX(DataWplywu) AS DataWplywu FROM oppo WHERE Rodzaj = 51 INTO _DataBO; 
			
			SET @_Date = _Date;
			SET @T = 'CREATE TEMPORARY TABLE `t_poz_oper_cur_inwent`';
			DROP TEMPORARY TABLE IF EXISTS `t_poz_oper_cur_inwent`;
			CREATE TEMPORARY TABLE `t_poz_oper_cur_inwent`
				SELECT 
					SQL_BUFFER_RESULT 
					d.R_ID, 
					d.ITEM_CODE, 
					_varDECIMAL(d.QTY) AS Ilosc_Fakt,
					SUM(IFNULL(o.ilosc, 0)) AS Ilosc_Mag, 
					-- _varDECIMAL(_varDECIMAL(d.QTY) - _varDECIMAL(SUM(o.ilosc))) AS Diff,
					_varDECIMAL(0) AS Diff,
					d.idntowr, 
					t.cenazkpnet AS cena_zakupu, 
					t.cenaewid, t.nazwa AS nazwa, 
					t.symbol AS symbol 
				FROM `t_poz_oper_cur_1` d  
				LEFT JOIN `oppo_mag` o ON o.idntowr = d.idntowr AND (o.datawplywu <= _Date) AND (o.datawplywu >= _DataBO) AND o.tymczasowa = 0 AND (o.magazyn = _Mag)
				INNER JOIN `TowrNg` t ON t.guid = d.idntowr 
				WHERE  
				TRUE
				GROUP BY  d.idntowr 
			;
			DROP TEMPORARY TABLE IF EXISTS `t_poz_oper_cur_1`;
			
			SET @T = 'UPDATE `t_poz_oper_cur_inwent` t';
			UPDATE `t_poz_oper_cur_inwent` t
			SET
				Diff = _varDECIMAL(t.Ilosc_Fakt - t.Ilosc_Mag)
			;
			CREATE TEMPORARY TABLE `t_poz_oper_cur`
				SELECT 
					d.R_ID, 
					d.ITEM_CODE, 
					d.IdnTowr,
					d.Diff AS QTY,
					_VarStr('') AS _ORDER_GUID,
					d.cenaewid AS CenaNet
				FROM `t_poz_oper_cur_inwent` d
				WHERE
					d.Diff != 0
			;
			DROP TEMPORARY TABLE IF EXISTS `t_poz_oper_cur_inwent`;
			
		
		END IF;		
		ALTER TABLE `t_poz_oper_cur` ADD KEY IdnTowr (IdnTowr);	
	
		
	END IF;		--	_MODE = 'E2O_set_status_for_ID'
	
	
END$$

DROP PROCEDURE IF EXISTS `opti_msg_e2o_header_REPLACE`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `opti_msg_e2o_header_REPLACE`(
	IN _TYPE VARCHAR(255),
	IN _DATE_C DATETIME,
	IN _STATUS VARCHAR(255),
	INOUT _lastID INTEGER
	)
BEGIN
	REPLACE INTO `opti_msg_e2o_header`
		(`TYPE`, `date_c`, `STATUS`)
	SELECT 
		_TYPE, _DATE_C, _STATUS
	;	
	SET _lastID = 0;
	SELECT LAST_INSERT_ID() INTO _lastID;	
END$$

DROP FUNCTION IF EXISTS `opti_Get_DateTime`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `opti_Get_DateTime`(
	_MODE VARCHAR(150),		
	_Type VARCHAR(150),
	_Default DATETIME,	--	Data domyślna w razie NULL-a
	_Param_A VARCHAR(50),
	_Param_B VARCHAR(50)
) 
RETURNS DATETIME
    NO SQL
    DETERMINISTIC
S_opti_Get_DateTime: BEGIN
	DECLARE _opti_Get_DateTime DATETIME;
	
	IF _MODE = 'e2o.MAX' THEN
		SELECT _VarDateTime(MAX(Date_C)) FROM `opti_msg_e2o_header` WHERE TYPE = _Type INTO _opti_Get_DateTime;
		SET _opti_Get_DateTime = IFNULL(_opti_Get_DateTime, _Default);
	END IF;
	
	IF _MODE = 'e2o.MIN' THEN
		SELECT _VarDateTime(MIN(Date_C)) FROM `opti_msg_e2o_header` WHERE STATUS = 'C' INTO _opti_Get_DateTime;
		SET _opti_Get_DateTime = IFNULL(_opti_Get_DateTime, _Default);
	END IF;
	
	IF _MODE = 'ng.RemoteTime' THEN 
		SET _Default = '1942-07-22 07:12';
		IF _Type = 'opng' THEN
			SELECT _VarDateTime(MAX(n.remotetime)) FROM `opng` n INTO _opti_Get_DateTime;
		ELSE	
			SELECT _VarDateTime(MAX(n.remotetime)) FROM `zmng` n INTO _opti_Get_DateTime;
		END IF;
		
		SET _opti_Get_DateTime = IFNULL(_opti_Get_DateTime, _Default);
	END IF;
	
	RETURN _opti_Get_DateTime;
END$$


/* ************************************************************************************************************************************************************************
	_WELT_GET_ ...
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `_WELT_GET_ARTYKULY`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_WELT_GET_ARTYKULY`(
	IN _ID_Sklepu VARCHAR(250)
	)
/*
	Zwracajaca tabele ARTYKULY dla wskazanego sklepu wraz z jego cenami.
	CALL _WELT_GET_ARTYKULY(@_ID_Sklepu);
*/
BEGIN
	CALL Int_2_infoPiek('select.artykuly', _ID_Sklepu, '');
END$$

GRANT EXECUTE ON PROCEDURE _WELT_GET_ARTYKULY TO pos $$
GRANT EXECUTE ON PROCEDURE _WELT_GET_ARTYKULY TO pos@'%' $$


DROP PROCEDURE IF EXISTS `_WELT_GET_GRUPY_ART`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_WELT_GET_GRUPY_ART`(
	)
/*
	Zwracajaca grupy wyrobow
	CALL _WELT_GET_GRUPY_ART();
*/
BEGIN
	CALL Int_2_infoPiek('select.grupy', '', '');
END$$

GRANT EXECUTE ON PROCEDURE _WELT_GET_GRUPY_ART TO pos $$
GRANT EXECUTE ON PROCEDURE _WELT_GET_GRUPY_ART TO pos@'%' $$

DROP PROCEDURE IF EXISTS `_WELT_GET_USERS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_WELT_GET_USERS`(
	)
/*
	Zwracajaca tabele osob.
	CALL _WELT_GET_USERS();
*/
BEGIN
	CALL Int_2_infoPiek('select.pos.users', '', '');
END$$

GRANT EXECUTE ON PROCEDURE _WELT_GET_USERS TO pos $$
GRANT EXECUTE ON PROCEDURE _WELT_GET_USERS TO pos@'%' $$

DROP PROCEDURE IF EXISTS `_WELT_GET_Sklepy`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_WELT_GET_Sklepy`(
	)
/*
	Zwracajaca tabele sklepow.
	CALL _WELT_GET_Sklepy();
*/
BEGIN
	CALL Int_2_infoPiek('select.pos.sklepy', '', '');
END$$

GRANT EXECUTE ON PROCEDURE _WELT_GET_Sklepy TO pos $$
GRANT EXECUTE ON PROCEDURE _WELT_GET_Sklepy TO pos@'%' $$


/* ************************************************************************************************************************************************************************
	INT->infoPiek
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `Int_2_infoPiek`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `Int_2_infoPiek`(
	IN _MODE VARCHAR(250),
	_ID_Sklepu VARCHAR(50),	
	_Param_A VARCHAR(50)	
	)
/*
	CALL Int_2_infoPiek('select.artykuly', '', '');
	CALL Int_2_infoPiek('select.grupy', '', '');
	CALL Int_2_infoPiek('select.grupy', _ID_Sklepu, '');
	CALL Int_2_infoPiek('select.pos.users', '', '');
	
	Zwracajaca tabele ARTYKULY dla wskazanego sklepu wraz z jego cenami.
	CALL _WELT_GET_ARTYKULY(@_ID_Sklepu);
	Zwracajaca grupy wyrobow
	CALL _WELT_GET_GRUPY_ART();
	
*/

S_Int_2_infoPiek: BEGIN

	IF _MODE = 'select.artykuly' THEN
		/* 
		JEST ...
		*/
		DROP TEMPORARY TABLE IF EXISTS `t_Int_2_infoPiek_import_artykuly`;
		CREATE TEMPORARY TABLE `t_Int_2_infoPiek_import_artykuly`(
			idntowr VARCHAR(38) PRIMARY KEY,
			IdnCennik INTEGER,
			KEY IdnCennik(IdnCennik)
		);
		INSERT IGNORE INTO `t_Int_2_infoPiek_import_artykuly`
			SELECT 
				t.GUID AS IdnTowr,
				_VarInt(
				(SELECT c.IDN AS IdnCennik FROM `cennik` c WHERE c.IdnTowr = t.GUID AND c.CzyZakup = 0 AND c.Klasa = 10 LIMIT 1) --	zastanowic sie nad konfiguracja tego prametru c.Klasa	
				) AS IdnCennik 
			FROM `TowrNg` t
			WHERE
				TRUE
				-- AND t.promocja_status != 2 
				AND t.katcol3 = 1
				AND (t.kod_fiskalny != '')
			HAVING IdnCennik IS NOT NULL
		;
		
		SELECT 
			(NOT t.deleted AND (t.promocja_status != 2)) AS artyk_akt, -- czy pozycja jest aktywna ?
			t.IDN AS artyk_hex, -- id wyroby
			t.katcol4 AS artyk_gru, -- grupa wyrobow
			t.Nazwa AS artyk_naz, -- nazwa wyrobu
			t.kasafiskalnanazwa AS artyk_skr, -- nazwa skrocona wyrobu
			_VarDouble(100*IFNULL(v.Value, 0)) AS artyk_vat, -- stwka vat numerycznie
			SUBSTR(v.Name, 8, 6) AS artyk_vac, -- stawka vat tekstowo
			v.IDFiskalny AS artyk_vfi, -- symbol stawki na urzadzeniu fiskalnym
			j.Nazwa AS artyk_jed, -- jednostka sprzedazy, magazynowania
			_VarInt(j.precyzja != 0) AS artyk_pod, -- czy mozna sprzedawac czesci ulamkowe wyrobu 
			_VarInt(0) AS artyk_sor, -- sortowanie wlasne !!!? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
			_VarInt(0) AS artyk_pop, -- zawsze 0 
			
			_VarDOUBLE(_Rnd2(
				IF(CURRENT_DATE BETWEEN IFNULL(c.waznoscod, '2012-01-01') AND IFNULL(c.waznoscdo, '2212-01-01') AND (IFNULL(c.waznoscod, c.waznoscdo) IS NOT NULL), 
					c.cena_promocyjna, 
					c.cena
				)*
				IF(kl.czy_ceny_brt, 1, 1+v.Value)
			)) AS artyk_cen, -- cena promocyjna
			
			_VarDOUBLE(_Rnd2(
				c.Cena *
				IF(kl.czy_ceny_brt, 1, 1+v.Value)
			)) AS artyk_ceb, -- cena brutto
		 -- 	c.Cena,(1+v.Value),
			t.kod_kreskowy1 AS artyk_kod, -- kod kreskowy
			_VarInt(0) AS artyk_typ, -- typ wyrobu 0 = pobrana z centrali 1=wpisany z reki 
			_VarInt(t.kod_fiskalny) AS artyk_plu, -- kod PLU  
			t.GUID AS ARTYK_GUID
		FROM `t_Int_2_infoPiek_import_artykuly` tt
		INNER JOIN `TowrNg` t ON t.GUID = tt.IdnTowr
		INNER JOIN `v_erp_vat` v ON v.ID = t.vat AND NOT not_Used
		LEFT JOIN `Jednostki` j ON j.IDN = t.jednostka
		INNER JOIN `cennik` c ON c.Idn = tt.IdnCennik
		LEFT JOIN `klasycen` kl ON kl.IDN = c.Klasa
		WHERE
			TRUE
		;
		DROP TEMPORARY TABLE IF EXISTS `t_Int_2_infoPiek_import_artykuly`;
		/*
		BYŁO
		SELECT 
			(NOT t.deleted AND (t.promocja_status != 2)) AS artyk_akt, -- czy pozycja jest aktywna ?
			t.IDN AS artyk_hex, -- id wyroby
			t.katcol4 AS artyk_gru, -- grupa wyrobow
			t.Nazwa AS artyk_naz, -- nazwa wyrobu
			t.kasafiskalnanazwa AS artyk_skr, -- nazwa skrocona wyrobu
			_VarDouble(100*IFNULL(v.Value, 0)) AS artyk_vat, -- stwka vat numerycznie
			SUBSTR(v.Name, 8, 6) AS artyk_vac, -- stawka vat tekstowo
			v.IDFiskalny AS artyk_vfi, -- symbol stawki na urzadzeniu fiskalnym
			j.Nazwa AS artyk_jed, -- jednostka sprzedazy, magazynowania
			_VarInt(j.precyzja != 0) AS artyk_pod, -- czy mozna sprzedawac czesci ulamkowe wyrobu 
			_VarInt(0) AS artyk_sor, -- sortowanie wlasne !!!? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
			_VarInt(0) AS artyk_pop, -- zawsze 0 
			
			_VarDOUBLE(_Rnd2(
				IF(CURRENT_DATE BETWEEN IFNULL(c.waznoscod, '2012-01-01') AND IFNULL(c.waznoscdo, '2212-01-01') AND (IFNULL(c.waznoscod, c.waznoscdo) IS NOT NULL), 
					c.cena_promocyjna, 
					c.cena
				)*
				IF(kl.czy_ceny_brt, 1, 1+v.Value)
			)) AS artyk_cen, -- cena promocyjna
			
			_VarDOUBLE(_Rnd2(
				c.Cena *
				IF(kl.czy_ceny_brt, 1, 1+v.Value)
			)) AS artyk_ceb, -- cena brutto
		 -- 	c.Cena,(1+v.Value),
			t.kod_kreskowy1 AS artyk_kod, -- kod kreskowy
			_VarInt(0) AS artyk_typ, -- typ wyrobu 0 = pobrana z centrali 1=wpisany z reki 
			_VarInt(t.kod_fiskalny) AS artyk_plu, -- kod PLU  
			t.GUID AS ARTYK_GUID
		FROM `TowrNg` t
		INNER JOIN `v_erp_vat` v ON v.ID = t.vat AND NOT not_Used
		LEFT JOIN `Jednostki` j ON j.IDN = t.jednostka
		INNER JOIN `cennik` c ON c.IdnTowr = t.GUID AND c.CzyZakup = 0 AND c.Klasa = 10	--	zastanowic sie nad konfiguracja tego prametru c.Klasa
		LEFT JOIN `klasycen` kl ON kl.IDN = c.Klasa
		WHERE
			TRUE
			-- AND t.promocja_status != 2 
			AND t.katcol3 = 1
			AND (t.kod_fiskalny != '')
		;
		*/
	END IF;		--	_MODE = 'select.artykuly'

	IF _MODE = 'select.grupy' THEN
		
		SELECT
			t.katcol4 AS artg_hex, -- id grupy wyrobow
			k.valuex AS artg_naz, -- nazwa grupy
			_VarInt(0) AS artg_sor, -- sortowanie !!!? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ?
			_VarInt(1) AS artd_akt, -- czy grupa aktywna ?
			_VarInt(0) AS artg_typ -- typ grupy 0=pobrana z centrali 1=pobrana z reki
		FROM `TowrNg` t
		INNER JOIN `katcols` k ON k.Typ = 1 AND k.Colnr = 5 AND k.Klucz = t.katcol4
		WHERE
			TRUE
			AND t.promocja_status != 2 
		GROUP BY artg_hex
		;
		
	END IF;		--	_MODE = 'select.grupy'
	
	IF _MODE = 'select.pos.users' THEN

		SELECT 
			o.IDN AS ID, 
			o.idnUser AS Nazwa, 
			_VarStr(CONCAT(o.Nazwisko, ' ', o.Imiona)) AS NazwiskoImiona, 
			_VarStr(IF(o.editinfo = '', o.IDN, o.editinfo)) AS Haslo,
			_VarInt(eh.Status = 0) AS Aktywny
		FROM `erp_area_nodes_hr` eh
		INNER JOIN `osoby` o ON o.GUID = eh.osobaGUID
		WHERE
			TRUE
			-- AND eh.Status = 0
		GROUP BY ID
		ORDER BY ID
		;
		
	END IF;		--	_MODE = 'select.pos.users'
	
	IF _MODE = 'select.pos.sklepy' THEN

		SELECT 
			-- n.IDN AS ID, 
			m.IdnMagazynu AS ID, 
			n.Nazwa AS Sklep_Nazwa, n.Symbol AS Sklep_Symbol,
			m.IdnMagazynu,
			m.Opis AS Magazyn_Opis,
			_VarInt(1) AS Aktywny
		FROM `erp_area_nodes_hr` eh
		INNER JOIN `erp_area_nodes` n ON n.GUID = eh.areaGUID
		INNER JOIN `Magazyny` m ON m.AreaGUID = n.GUID
		WHERE
			TRUE
			AND n.Symbol > '000'
		GROUP BY ID
		ORDER BY ID
		;
	END IF;		--	_MODE = 'select.pos.sklepy'
	
END$$


/* ************************************************************************************************************************************************************************
	infoPiek->INT
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `infoPiek_2_Int`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `infoPiek_2_Int`(
	IN _MODE VARCHAR(250),
	_IDOK_HEX VARCHAR(50),	
	_Param_A VARCHAR(50)	
	)
/*
	CALL infoPiek_2_Int('import', '', '');
	CALL infoPiek_2_Int('import.one.rec', '222', '');
		CALL infoPiek_2_Int('Set.infpie_dok_ng_rec.Status', IDOK_HEX, 'E');
	
*/

S_infoPiek_2_Int: BEGIN
	
	DECLARE _KontrGUID VARCHAR(38) DEFAULT '@ST_ZS';
	DECLARE _IdnOper VARCHAR(38);
	DECLARE _ZMagazynu INTEGER DEFAULT 0; --	IKON_HEX
	DECLARE _DoMagazynu INTEGER DEFAULT 0; --	IKON_HEX
	
	
	
	IF _MODE = 'import' THEN
		CALL _Session_Refresh_Variables('', '', '');
	--	0.	Ustawiam staus 'F' dla tych co mamy już w `opng` potem usune
		UPDATE `infpie_dok_ng_rec` r, `infpie_dok_ng` d, `opng` n
		SET
			r.INT_Status = 'F',	--	Zakonczono skora sa juz w `opng`
			d.INT_Status = 'F'	--	Zakonczono skora sa juz w `opng`
		WHERE
			r.IDOK_HEX = d.IDOK_HEX
			AND
			n.GUID = d.INT_GUID
		;
		DELETE r FROM `infpie_dok_ng_rec` r
		WHERE
			r.INT_Status = 'F'
		;
	--	1.	kreuję tymcz.
		DROP TEMPORARY TABLE IF EXISTS `t_infpie_dok_ng_rec`;
		CREATE TEMPORARY TABLE `t_infpie_dok_ng_rec`
			SELECT * FROM `infpie_dok_ng_rec` WHERE INT_Status = ''
		;
		SELECT COUNT(*) FROM `t_infpie_dok_ng_rec` INTO @ROW_COUNT;
		-- SELECT ROW_COUNT() INTO @ROW_COUNT;
		IF _Param_A != '' THEN select @ROW_COUNT; END IF;
		IF @ROW_COUNT <= 0 THEN
		--	BRAK rekordów do przetwarzania
			DROP TEMPORARY TABLE IF EXISTS `t_infpie_dok_ng_rec`;
			LEAVE S_infoPiek_2_Int;
		END IF;
		
			
	--	2.	ustawiam status na 'T' temporary czyli przetwarzam
		UPDATE `infpie_dok_ng_rec` r, `t_infpie_dok_ng_rec` t
		SET
			r.INT_Status = 'T'	--	Temporary czyli przetwarzam
		WHERE
			r.IDOK_HEX = t.IDOK_HEX
		;
	--	3.	Pętla przetwarzania
		BEGIN
		-- Operacje np. magazynowe
		
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE IDOK_HEX INTEGER DEFAULT 0;
		--	3.1 Kursor	
			DECLARE c_infpie_dok_ng_import CURSOR FOR
				SELECT
					t.IDOK_HEX
				FROM `t_infpie_dok_ng_rec` t
			;	
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		--	3.2 Open Kursor	
			OPEN c_infpie_dok_ng_import;
			loop_c_infpie_dok_ng_import: LOOP
				FETCH c_infpie_dok_ng_import INTO 
					IDOK_HEX
				;
				IF done THEN
					LEAVE loop_c_infpie_dok_ng_import;
				END IF;
			--	Wykonaj naliczanie
				CALL infoPiek_2_Int('import.one.rec', IDOK_HEX, '');
			END LOOP;
			CLOSE c_infpie_dok_ng_import;
		
		END;
		
	--	4.	usuń tymczsowkę
		DROP TEMPORARY TABLE IF EXISTS `t_infpie_dok_ng_rec`;
	--	5.	przepisz status do infpie_dok_ng
		UPDATE `infpie_dok_ng` n, `infpie_dok_ng_rec` r
		SET
			n.INT_Status = r.INT_Status	--	czyli zakonczono 
		WHERE
			r.INT_Status = 'F'
			AND
			r.IDOK_HEX = n.IDOK_HEX
		;
	--	6.	usuń rekordy z `infpie_dok_ng_rec` ze statusem na 'F' czyli zakonczono 
		DELETE r FROM `infpie_dok_ng_rec` r
		WHERE
			r.INT_Status = 'F'
		;
	END IF;		--	_MODE = 'import'
	
	IF _MODE = 'Set.infpie_dok_ng_rec.Status' THEN
		UPDATE `infpie_dok_ng_rec` r
		SET
			r.INT_Status = _Param_A
		WHERE
			r.IDOK_HEX = _IDOK_HEX
		;
	END IF;		--	_MODE = 'Set.infpie_dok_ng_rec.Status'
	
	IF _MODE = 'import.one.rec' THEN
	
	--	1.	ustawiam status na 'E' czyli teraz przetwarzam (E ma znaczenie: error gdy zostanie nie zmieniony na F)
	
		CALL infoPiek_2_Int('Set.infpie_dok_ng_rec.Status', _IDOK_HEX, 'E');
		SELECT 
			dn.INT_GUID,
			dn.IKON_HEX
		FROM `infpie_dok_ng` dn
		WHERE
			dn.IDOK_HEX = _IDOK_HEX
		INTO
			_IdnOper,
			_ZMagazynu
		;
		SET
			_KontrGUID = IFNULL((SELECT k.GUID FROM `kontrah` k WHERE k.sklep = _ZMagazynu LIMIT 1), _KontrGUID)
		;
		SET 
			@Rodzaj = 0,
			@IdnOper = _IdnOper,
			@KontrGUID = _KontrGUID,
			@_Tymczasowa = TRUE,
			@G = '',
			@ZMagazynu = _ZMagazynu
		;
CALL _WatchTimeLogs_Tools('start', "Opng_E_init", '-', '', '', '');
		
CALL _WatchTimeLogs_Tools('start', "CALL Opng_E_init('INSERT', _IdnOper, 'jmam_alg', '1', '');", '-', '', '', '');
		CALL Opng_E_init('INSERT', _IdnOper, 'jmam_alg', '1', ''); 	-- _Param_A jest tu != '' aby nie wywoływać selecta wewn. innej proc. _ng_e_rec_init
CALL _WatchTimeLogs_Toolsf('stop', "CALL Opng_E_init('INSERT', _IdnOper, 'jmam_alg', '1', '');", '-', '', '', '');
		
CALL _WatchTimeLogs_Tools('start', "CALL Opng_E_init_set('', _IdnOper, @Rodzaj, 0, '', '3', _KontrGUID, _ZMagazynu, 0, 0, 0, '', '', '', 0, '', '', '');", '-', '', '', '');
		CALL Opng_E_init_set('', _IdnOper, @Rodzaj, 0, '', '3', _KontrGUID, _ZMagazynu, 0, 0, 0, '', '', '', 0, '', '', '');
CALL _WatchTimeLogs_Tools('stop', "CALL Opng_E_init_set('', _IdnOper, @Rodzaj, 0, '', '3', _KontrGUID, _ZMagazynu, 0, 0, 0, '', '', '', 0, '', '', '');", '-', '', '', '');



CALL _WatchTimeLogs_Tools('start', "CALL Opng_E_Run('before.run', _IdnOper, '', '', ''); ...", '-', '', '', '');
		CALL Opng_E_Run('before.run', _IdnOper, '', '', '');
		CALL Opng_E_Run('', _IdnOper, '', '', '');
		CALL Opng_E_Run('after.run', _IdnOper, '', '', '');
CALL _WatchTimeLogs_Tools('stop', "CALL Opng_E_Run('before.run', _IdnOper, '', '', ''); ...", '-', '', '', '');
		
CALL _WatchTimeLogs_Tools('start', "Updaty...", '-', '', '', '');
		UPDATE	`opng_e` n, `infpie_dok_ng` dn
		SET
			n.NrDokFin = CONCAT(LPAD(_ZMagazynu, 3, '0'), '/', dn.IDOK_NUM, '/', YEAR(dn.IDOK_DAT)),
			n.NrWewn = IF(_IDOK_HEX = 999999, '', ''),
			n.przeliczenie_B_N = 1,
			n.paragon_printed = 1,
			n.Rozrachunek = 1, 	-- p.Marek 2013-08-14 14:58 ZAWSZE 1 n.Rozrachunek = IF(dn.IDOK_PLA = 1, 0, 1),
			n.Datawystaw = IF(dn.IDOK_DAT = '0000-00-00', CURRENT_DATE(), dn.IDOK_DAT),
			n.DataSprzed = IF(dn.IDOK_DAT = '0000-00-00', CURRENT_DATE(), dn.IDOK_DAT),
			n.DataWplywu = IF(dn.IDOK_DAT = '0000-00-00', CURRENT_DATE(), dn.IDOK_DAT),
			n.remotetime = dn.IDOK_TIM,
			n.id_kasy_fis = dn.IDOK_LIC,
			n.AreaGUID = (SELECT m.AreaGUID FROM Magazyny m WHERE m.IdnMagazynu = _ZMagazynu LIMIT 1),
			n.osobaGUID = (SELECT o.GUID FROM `osoby` o WHERE o.IDN = dn.IDOK_KTO LIMIT 1),
			n.wzordekr = 'K'
		WHERE
			dn.IDOK_HEX = _IDOK_HEX
			AND
			n.GUID = _IdnOper
		;
		UPDATE	`opng_e` n, `erp_area_nodes` an
		SET
			n.opis = CONCAT('Paragon fisk. z urz.: ', n.id_kasy_fis, ' w : ', an.Nazwa, ' (', n.remotetime, ')')
		WHERE
			n.GUID = _IdnOper
			AND
			an.GUID = n.AreaGUID
		;
		UPDATE	`opng_e` n, `osoby` o
		SET
			n.idnuser = o.idnuser,
			n.opis = CONCAT(n.opis, ' wyst.: ', o.Nazwisko, ' ', o.Imiona)
		WHERE
			n.GUID = _IdnOper
			AND
			o.GUID = n.osobaGUID
		;
		UPDATE `infpie_dok_po` dp
		SET 
			dp.ARTYK_GUID = RTRIM(dp.ARTYK_GUID)
		WHERE
			dp.IDOK_HEX = _IDOK_HEX 
		;		
		UPDATE `infpie_dok_po` dp
		SET
			dp.ARTYK_GUID = 
			IFNULL(
			(SELECT GUID FROM `TowrNg` t WHERE t.IDN = dp.ARTYK_HEX ORDER BY t.IDN LIMIT 1), ''
			)
		WHERE
			dp.ARTYK_GUID = ''
			AND
			dp.IDOK_HEX = _IDOK_HEX 
		;
CALL _WatchTimeLogs_Tools('stop', "Updaty...", '-', '', '', '');
		
CALL _WatchTimeLogs_Tools('start', "`t_infpie_dok_po_insert`...", '-', '', '', '');

		DROP TEMPORARY TABLE IF EXISTS `t_infpie_dok_po_insert`;
		IF FALSE THEN	--	Wartosc Brutto
			CREATE TEMPORARY TABLE `t_infpie_dok_po_insert`
				SELECT
					dp.INT_GUID AS GUID, -- 	CONCAT('Infpie_', dp.IPOZ_HEX) AS GUID,
					_IdnOper AS IdnOper,
					-- (SELECT GUID FROM `TowrNg` t WHERE t.kod_fiskalny = dp.ARTYK_PLU ORDER BY t.IDN LIMIT 1) AS IdnTowr,
					dp.ARTYK_GUID AS IdnTowr,
					-- było (SELECT GUID FROM `TowrNg` t WHERE t.IDN = dp.ARTYK_HEX ORDER BY t.IDN LIMIT 1) AS IdnTowr,
					-- t.GUID AS IdnTowr,
					IFNULL(dp.IPOZ_ILE, 0.0) AS Ilosc,
					IFNULL(dp.IPOZ_WAB, 0.0) AS WartBrt,	--	bylo IFNULL(dp.IPOZ_CEB, 0.0) AS CenaBrt,
					IFNULL(0*dp.IPOZ_RAB, 0.0) AS rabat,	--	UWAGA TERAZ Rabat daje juz na 0
					4 AS rodzaj_przeliczenia, -- z wart. brutto do ceny brt itd	a bylo 5 	-- Brutto z ceny brutto  BYŁO 7 (_BN_CB_WN_POS) jest 5 
					dp.INT_IDNVAT AS IdnVat
				FROM `infpie_dok_po` dp
				-- INNER JOIN `TowrNg` t ON t.IDN = dp.ARTYK_HEX 	-- Bylo ale uznaliśmy że lepiej gdy będzie to kod_fiskalny czyli ARTYK_PLU
				-- INNER JOIN `TowrNg` t ON t.kod_fiskalny = dp.ARTYK_PLU
				WHERE
					dp.IDOK_HEX = _IDOK_HEX 
					AND
					dp.ARTYK_PLU > 0
			;
			INSERT IGNORE INTO `oppo_e` 
			(GUID, IdnOper, IdnTowr, Ilosc, WartBrt, rabat, rodzaj_przeliczenia, IdnVat)
				SELECT
					dp.GUID,
					dp.IdnOper,
					dp.IdnTowr,
					dp.Ilosc,
					dp.WartBrt,
					dp.rabat,
					dp.rodzaj_przeliczenia, 	-- Brutto z ceny brutto
					dp.IdnVat
				FROM `t_infpie_dok_po_insert` dp
			ON DUPLICATE KEY UPDATE
				`oppo_e`.IdnTowr = dp.IdnTowr,
				`oppo_e`.Ilosc = dp.Ilosc
			;
		ELSE	--	Cena Brutto
			CREATE TEMPORARY TABLE `t_infpie_dok_po_insert`
				SELECT
					dp.INT_GUID AS GUID, -- 	CONCAT('Infpie_', dp.IPOZ_HEX) AS GUID,
					_IdnOper AS IdnOper,
					-- (SELECT GUID FROM `TowrNg` t WHERE t.kod_fiskalny = dp.ARTYK_PLU ORDER BY t.IDN LIMIT 1) AS IdnTowr,
					dp.ARTYK_GUID AS IdnTowr,
					-- było (SELECT GUID FROM `TowrNg` t WHERE t.IDN = dp.ARTYK_HEX ORDER BY t.IDN LIMIT 1) AS IdnTowr,
					-- t.GUID AS IdnTowr,
					IFNULL(dp.IPOZ_ILE, 0.0) AS Ilosc,
					IFNULL(dp.IPOZ_WAB, 0.0) AS CenaBrt,	--	bylo IFNULL(dp.IPOZ_CEB, 0.0) AS CenaBrt,
					IFNULL(0*dp.IPOZ_RAB, 0.0) AS rabat,	--	UWAGA TERAZ Rabat daje juz na 0
					5 AS rodzaj_przeliczenia, -- Brutto z ceny brutto  BYŁO 7 (_BN_CB_WN_POS) jest 5 
					dp.INT_IDNVAT AS IdnVat
				FROM `infpie_dok_po` dp
				-- INNER JOIN `TowrNg` t ON t.IDN = dp.ARTYK_HEX 	-- Bylo ale uznaliśmy że lepiej gdy będzie to kod_fiskalny czyli ARTYK_PLU
				-- INNER JOIN `TowrNg` t ON t.kod_fiskalny = dp.ARTYK_PLU
				WHERE
					dp.IDOK_HEX = _IDOK_HEX 
					AND
					dp.ARTYK_PLU > 0
			;
			INSERT IGNORE INTO `oppo_e` 
			(GUID, IdnOper, IdnTowr, Ilosc, CenaBrt, rabat, rodzaj_przeliczenia, IdnVat)
				SELECT
					dp.GUID,
					dp.IdnOper,
					dp.IdnTowr,
					dp.Ilosc,
					dp.CenaBrt,
					dp.rabat,
					dp.rodzaj_przeliczenia, 	-- Brutto z ceny brutto
					dp.IdnVat
				FROM `t_infpie_dok_po_insert` dp
			ON DUPLICATE KEY UPDATE
				`oppo_e`.IdnTowr = dp.IdnTowr,
				`oppo_e`.Ilosc = dp.Ilosc
			;
		END IF;
		
CALL _WatchTimeLogs_Tools('stop', "`t_infpie_dok_po_insert`...", '-', '', '', '');

CALL _WatchTimeLogs_Tools('start', "CALL oppo_E_Let_Update('from_opng_e', '', _IdnOper, '', ''); i update...", '-', '', '', '');
		CALL oppo_E_Let_Update('from_opng_e', '', _IdnOper, '', '');
		--	IdnVat TRACĘ w triggerze `oppo_e` po wstawieniu IdnTowr - zatem raz jeszcze go wstawiam
		
		UPDATE `oppo_e` p, `t_infpie_dok_po_insert` dp
		SET
			p.IdnVat = dp.IdnVat
		WHERE
			p.GUID = dp.GUID
		;

		DROP TEMPORARY TABLE IF EXISTS `t_infpie_dok_po_insert`;
			-- CALL oppo_E_Let_Update('one_rec', _GUID, '', '', '');
CALL _WatchTimeLogs_Tools('stop', "CALL oppo_E_Let_Update('from_opng_e', '', _IdnOper, '', ''); i update...", '-', '', '', '');
		
CALL _WatchTimeLogs_Tools('start', "Post...", '-', '', '', '');
		CALL opng_E_Let('Post', _IdnOper, '', '');
CALL _WatchTimeLogs_Tools('stop', "Post...", '-', '', '', '');
		-- CALL ng_vat_e_build_from_po('', 'OP', _IdnOper, '', '');              Niepotrzebne wolane juz w opng_E_Let('Post', ...
		
CALL _WatchTimeLogs_Tools('start', "Apply...", '-', '', '', '');
		CALL opng_E_Let('ApplyAndClear', _IdnOper, '', '1');		--	 Wraz z numeracja
CALL _WatchTimeLogs_Tools('stop', "Apply...", '-', '', '', '');

CALL _WatchTimeLogs_Tools('stop', "Opng_E_init", '-', '', '', '');

		/*
		
		SELECT n.rozrachunek, n.grupaoper, n.wzordekr, n.ugrpGUID, n.* FROM opng_e n WHERE GUID = @IdnOper;
		SELECT p.* FROM oppo_e p WHERE IdnOper = @IdnOper;
			CALL opng_E_Let('Post', _IdnOper, '', '');
			-- CALL opng_E_Let('Apply', _IdnOper, '', '');
			CALL opng_E_Let('ApplyAndClear', _IdnOper, '', '');
			-- CALL opng_E_Let('End', _IdnOper, '', '');
		*/
		
		
	--	3.	ustawiam status na 'F' czyli zakonczono 
		CALL infoPiek_2_Int('Set.infpie_dok_ng_rec.Status', _IDOK_HEX, 'F'); 	-- 'F' czyli zakonczono 
	END IF;		--	_MODE = 'import.one.rec'
	
END$$

DROP PROCEDURE IF EXISTS `infpiek_int_Tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `infpiek_int_Tools`(
	IN _MODE VARCHAR(250),
	_Param_A VARCHAR(50),	
	_Param_B VARCHAR(50)	
	)
/*
	CALL infpiek_int_Tools('set.Status', IDnaglowka, 'F');
	
*/
S_infpiek_int_Tools: BEGIN
	
	IF _MODE = 'set.Status' THEN
		UPDATE `infpiek_int_ng`
		SET
			INT_Status = _Param_B
		WHERE
			DOWN_HEX = _Param_A
		;
	END IF;		-- 	'set.Status'
	
END$$


/* ************************************************************************************************************************************************************************
	INT_2_POS
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `_POS_INT_2_POS`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_POS_INT_2_POS`(
	IN _MODE VARCHAR(250),
	_IDOK VARCHAR(50),	
	_Param_A VARCHAR(50)	
	)
/*
	CALL _POS_INT_2_POS('get.ng', '', '');
	CALL _POS_INT_2_POS('get.po', _IDOK, '');
		CALL _POS_INT_2_POS('set.Status', _IDOK, 'F');
	dla INT
		CALL _POS_INT_2_POS('refresh.PZ', '', '3');		--	w _Param_A ile dni wstecz
	
*/

S_POS_INT_2_POS: BEGIN
	DECLARE _Ile_Dni_Wstecz INTEGER DEFAULT 1;
	
	IF _MODE = 'get.ng' THEN
		SELECT
			tn.*
		FROM `infpiek_int_ng` tn
		WHERE
			tn.INT_Dir_Status = 'zINT'
			AND
			tn.DOWN_TYP = '9'	-- 9 ????
			AND
			tn.INT_Status = ''	-- nie przetworzone
		;
	END IF;		-- 	'get.ng'
	
	IF _MODE = 'get.po' THEN
		/*
		Najpierw wstaw pozycje - wstawiamy dopiero w tej procedurze bo byc może PZ-ka bedzie poprawiana
		*/
		INSERT IGNORE INTO `infpiek_int_po`
		(WYRO_HEX, WYRO_PLU, DOWN_HEX, DOWP_ILE, DOWP_RAB, DOWP_BRU, OPIS_TXT, INT_GUID, INT_IDNVAT, ARTYK_GUID)
		SELECT
			t.Idn AS WYRO_HEX,
			_VarInt(t.kod_fiskalny) AS WYRO_PLU,
			tn.DOWN_HEX AS DOWN_HEX,
			p.Ilosc AS DOWP_ILE,
			p.Rabat AS DOWP_RAB,
			p.CenaBrt AS DOWP_BRU,
			p.towrnazwa AS OPIS_TXT,
			p.GUID AS INT_GUID,
			p.IdnVat AS INT_IDNVAT,
			p.IdnTowr AS ARTYK_GUID
		FROM `infpiek_int_ng` tn
		INNER JOIN `opng` n ON n.GUID = tn.INT_GUID
		INNER JOIN `oppo` p ON p.IdnOper = n.GUID
		INNER JOIN `TowrNg` t ON t.GUID = p.IdnTowr
		WHERE
			tn.DOWN_HEX = _IDOK
		ON DUPLICATE KEY UPDATE
			DOWP_ILE = p.ILosc,
			DOWN_HEX = tn.DOWN_HEX
		;

		/*
		... i SELECT
		*/
		SELECT
			tp.*
		FROM `infpiek_int_po` tp
		WHERE
			tp.DOWN_HEX = _IDOK
		;
	END IF;		-- 	'get.po'
	
	IF _MODE = 'set.Status' THEN
		CALL infpiek_int_Tools('set.Status', _IDOK, _Param_A);
	END IF;		-- 	'set.Status'

	IF _MODE = 'refresh.PZ' THEN
	/*******************************************************************************************************************************
		PZ-ki do `infpiek_int_ng`
	*******************************************************************************************************************************/
		CALL _Session_Refresh_Variables('', '', '');
		IF _Param_A != '' THEN
			SET _Ile_Dni_Wstecz = _Param_A;
		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS `t_infpiek_int_PZ`;
		CREATE TEMPORARY TABLE  `t_infpiek_int_PZ` LIKE `opng_e`;
		INSERT IGNORE INTO `t_infpiek_int_PZ`
		-- CREATE TEMPORARY TABLE  `t_infpiek_int_PZ`
			SELECT
				n.*
			FROM `opng` n
			LEFT JOIN `infpiek_int_ng` tn ON tn.INT_GUID = n.GUID 
			WHERE
				n.rodzaj = 12
				AND
				n.Status < @COPNG_Status_ZREALIZOWANA 	-- @COPNG_Status_ZREALIZOWANA = 2
				AND
				n.LocalTime > DATE_ADD(NOW(), INTERVAL -_Ile_Dni_Wstecz DAY)
				AND
				IFNULL(tn.INT_Status, '') = ''	--	GDY jest rekord w `infpiek_int_ng` (czyli nie NULL) i INT_Status jest 'F' czyli zakonczono TO NIE POBIERAJ
				AND
				NOT(n.GUID LIKE 'WZ_z_PPT%')
				AND 
				n.domagazynu BETWEEN 201 AND 299
		;
	
		INSERT IGNORE INTO `infpiek_int_ng`
		(DOWN_TYP, DOWN_DAT, DOWN_NUM, DOWN_NET, DOWN_BRU, FAKT_HEX, FAKT_DAT, FAKT_NUM, DOWN_DOS, DOWN_KTO, INT_Status, INT_Dir_Status, INT_UserGUID, INT_GUID, KONT_HEX)
		SELECT
			'9' AS DOWN_TYP, 
			n.DataWplywu AS DOWN_DAT, 
			n.NrDokMag AS DOWN_NUM, 
			n.Netto AS DOWN_NET, n.Brutto AS DOWN_BRU, 
			n.Idn AS FAKT_HEX, n.DataWystaw AS FAKT_DAT, n.nrDokFin AS FAKT_NUM, 
			0 AS DOWN_DOS, 
			0 AS DOWN_KTO, 
			'' AS INT_Status, 
			'zINT' AS INT_Dir_Status, 
			n.OsobaGUID AS INT_UserGUID, 
			n.GUID AS INT_GUID,
			n.DoMagazynu AS KONT_HEX
		FROM `t_infpiek_int_PZ` n
		WHERE
			TRUE
		
		;
	
		DROP TEMPORARY TABLE IF EXISTS `t_infpiek_int_PZ`;
		CALL _Globals_Set("CALL _POS_INT_2_POS('refresh.PZ', '', '3');", NOW());
	END IF;		-- 	'refresh.PZ'
	
	
END$$

GRANT EXECUTE ON PROCEDURE _POS_INT_2_POS TO pos $$
GRANT EXECUTE ON PROCEDURE _POS_INT_2_POS TO pos@'%' $$

/* ************************************************************************************************************************************************************************
	POS_2_INT
************************************************************************************************************************************************************************ */	

		DROP PROCEDURE IF EXISTS `_POS_E_NG`$$
		CREATE DEFINER=`root`@`localhost` PROCEDURE `_POS_E_NG`(
			IN _MODE VARCHAR(250),
			_IDOK VARCHAR(50),	
			_DOWN_TYP VARCHAR(38),
			_IdnOper VARCHAR(38), 
			_Rodzaj INTEGER,
			_KontrGUID VARCHAR(38),
			_ZMagazynu INTEGER,
			_DoMagazynu INTEGER,
			_INT_Dir_Status VARCHAR(50),
			_Param_A VARCHAR(50)	
			)
		/*
			CALL _POS_E_NG('OP.Create.ng', _IDOK, _DOWN_TYP, _IdnOper, _Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');
		*/

		S_POS_E_NG: BEGIN
			DECLARE _IS_MM BOOLEAN DEFAULT FALSE;
			
			SET @Rodzaj = _Rodzaj;
			
			IF _MODE = 'OP.Create.ng' THEN
				CALL Opng_E_init('INSERT', _IdnOper, 'jmam_alg', '1', ''); 	-- _Param_A jest tu != '' aby nie wywoływać selecta wewn. innej proc. _ng_e_rec_init
				
				CALL Opng_E_init_set('', _IdnOper, @Rodzaj, 0, '', '3', _KontrGUID, _ZMagazynu, _DoMagazynu, 0, 0, '', '', '', 0, '', '', '');
				CALL Opng_E_Run('before.run', _IdnOper, '', '', '');
				CALL Opng_E_Run('', _IdnOper, '', '', '');
				CALL Opng_E_Run('after.run', _IdnOper, '', '', '');
			
				UPDATE	`opng_e` n, `infpiek_int_ng` dn
				SET
					n.NrDokFin = CONCAT(LPAD(_ZMagazynu, 3, '0'), '/', dn.DOWN_NUM, '/', YEAR(dn.DOWN_DAT)),
					-- ???? n.przeliczenie_B_N = 1,
					-- n.paragon_printed = 1,
					n.Datawystaw = IF(dn.DOWN_DAT = '0000-00-00', CURRENT_DATE(), dn.DOWN_DAT),
					n.DataSprzed = IF(dn.DOWN_DAT = '0000-00-00', CURRENT_DATE(), dn.DOWN_DAT),
					n.DataWplywu = IF(dn.DOWN_DAT = '0000-00-00', CURRENT_DATE(), dn.DOWN_DAT),
					-- n.remotetime = dn.IDOK_TIM,
					-- n.id_kasy_fis = dn.IDOK_LIC,
					n.AreaGUID = (SELECT m.AreaGUID FROM Magazyny m WHERE m.IdnMagazynu = _ZMagazynu LIMIT 1),
					n.osobaGUID = (SELECT o.GUID FROM `osoby` o WHERE o.IDN = dn.DOWN_KTO LIMIT 1)
				WHERE
					dn.DOWN_HEX = _IDOK
					AND
					n.GUID = _IdnOper
				;
				/*
				UPDATE	`opng_e` n, `erp_area_nodes` an
				SET
					n.opis = CONCAT('Paragon fisk. z urz.: ', n.id_kasy_fis, ' w : ', an.Nazwa, ' (', n.remotetime, ')')
				WHERE
					n.GUID = _IdnOper
					AND
					an.GUID = n.AreaGUID
				;
				*/
				UPDATE	`opng_e` n, `osoby` o
				SET
					n.idnuser = o.idnuser,
					n.opis = CONCAT(n.opis, ' wyst.: ', o.Nazwisko, ' ', o.Imiona)
				WHERE
					n.GUID = _IdnOper
					AND
					o.GUID = n.osobaGUID
				;
			END IF;		-- 	'OP.Create.ng'
			
			IF _MODE = 'OP.Create.po' THEN
			--	 Operacje
				
SET @_POS_POS_2_INT = "PRZED CALL _POS_E_NG('temp.t_infpiek_dok_po_insert', _IDOK";										
				SET _IS_MM = IFNULL(_Param_A, '') != '';
				IF !_IS_MM THEN
					CALL _POS_E_NG('temp.t_infpiek_dok_po_insert', _IDOK, _DOWN_TYP, _IdnOper, _Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');
				END IF;
				
SET @_POS_POS_2_INT = "PRZED INSERT IGNORE INTO `oppo_e` ";										
				IF _Rodzaj != @ht.CInwn THEN
				--	NORMALNY DOKUMENT - NIE INWENTARYZACJA
					INSERT IGNORE INTO `oppo_e` 
					(GUID, IdnOper, IdnTowr, Ilosc, CenaBrt, rabat, /* rodzaj_przeliczenia,*/ IdnVat, Info)
						SELECT
							IF(_IS_MM, MD5(dp.GUID), dp.GUID),
							_IdnOper, -- bylo dp.IdnOper,   ale przeciez na dla zwrotu powstaje potem MM-ka a ona ma inny IdnOper
							dp.IdnTowr,
							IF(_Rodzaj IN (@ht.CMGZW), -ABS(dp.Ilosc), ABS(dp.Ilosc)),
							dp.CenaBrt,
							dp.rabat,
							-- ??? dp.rodzaj_przeliczenia, 	-- Brutto z ceny brutto
							dp.IdnVat,
							CONCAT(_Param_A, ' Rodzaj:', _Rodzaj, ', ilosc:', IF(_Rodzaj IN (@ht.CMGZW), -ABS(dp.Ilosc), ABS(dp.Ilosc)), ', z mag:', _ZMagazynu, ', do mag:', _DoMagazynu)
						FROM `t_infpiek_dok_po_insert` dp
						-- INNER JOIN `TowrNg` t ON t.GUID = dp.IdnTowr !!!!!
						ORDER BY dp.TowrSymbol
					ON DUPLICATE KEY UPDATE
						`oppo_e`.IdnTowr = dp.IdnTowr,
						`oppo_e`.Ilosc = IF(_Rodzaj IN (@ht.CMGZW), -ABS(dp.Ilosc), ABS(dp.Ilosc))
					;
				ELSE
				--	INWENTARYZACJA
SET @_POS_POS_2_INT = "SELECT IFNULL(MAX(DataWplywu), '2001-01-01') AS DataWplywu FROM oppo WHERE Rodzaj = 51 INTO @_DataBO;";						
					SELECT DATE(IFNULL(MAX(DataWplywu), '2001-01-01')) AS DataWplywu FROM oppo WHERE Rodzaj = 51 INTO @_DataBO;
					SELECT DOWN_DAT AS DOWN_DAT FROM `infpiek_int_ng` dn WHERE dn.DOWN_HEX = _IDOK INTO @_DataINW;
					
				
					DROP TEMPORARY TABLE IF EXISTS `t_inwentaryzacja_stan_na_dzien`;
					CREATE TEMPORARY TABLE `t_inwentaryzacja_stan_na_dzien`(
						idntowr VARCHAR(38) PRIMARY KEY,
						iloscbylo DECIMAL(16,6),
						iloscjest DECIMAL(16,6)
					);
					
					INSERT IGNORE INTO `t_inwentaryzacja_stan_na_dzien`
						SELECT 
							o.idntowr, 
							-- SUM(IFNULL(o.ilosc, 0)) AS iloscbylo 
							SUM(IFNULL(o.ilosc, 0)) AS iloscbylo,
							SUM(IFNULL(o.ilosc, 0)) AS iloscjest 
						FROM `oppo_mag` o 
						WHERE 
							o.datawplywu BETWEEN @_DataBO AND @_DataINW
							AND o.tymczasowa = 0 
							AND (o.magazyn=_DoMagazynu) 
						GROUP BY o.idntowr 
					;
					
					INSERT IGNORE INTO `t_inwentaryzacja_stan_na_dzien`
					(IdnTowr, iloscbylo, iloscjest)
						SELECT
							dp.IdnTowr,
							0,
							dp.Ilosc
						FROM `t_infpiek_dok_po_insert` dp
					ON DUPLICATE KEY UPDATE
						`t_inwentaryzacja_stan_na_dzien`.iloscjest = dp.Ilosc
					;
					
SET @_POS_POS_2_INT = "INSERT IGNORE INTO `oppo_e` ";						
					INSERT IGNORE INTO `oppo_e` 
					(GUID, IdnOper, IdnTowr, iloscbylo, iloscjest, CenaBrt, rabat, Info)
						SELECT
							NEWGUID(),
							@IdnOper, -- bylo dp.IdnOper,   ale przeciez na dla zwrotu powstaje potem MM-ka a ona ma inny IdnOper
							t.IdnTowr,
							t.iloscbylo,
							t.iloscjest,
							0,
							0,
							''
						FROM `t_inwentaryzacja_stan_na_dzien` t
					;
					DROP TEMPORARY TABLE IF EXISTS `t_inwentaryzacja_stan_na_dzien`;
					
					/*
					UPDATE `oppo_e` p, `t_infpiek_dok_po_insert` dp
					SET
						p.GUID = dp.GUID,
						p.iloscjest = dp.Ilosc,
						-- p.ilosc = -1,
						p.Tymczasowa = 1
					WHERE
						p.IdnOper = @IdnOper
						AND
						p.IdnTowr = dp.IdnTowr
					;
					*/

		-- LEAVE S_POS_E_NG;
					
				END IF; 	--	INWENTARYZACJA

SET @_POS_POS_2_INT = "CALL oppo_E_Let_Update('from_opng_e', '', _IdnOper, '', ''); ";										
				
				CALL oppo_E_Let_Update('from_opng_e', '', _IdnOper, '', '');
				--	IdnVat TRACĘ w triggerze `oppo_e` po wstawieniu IdnTowr - zatem raz jeszcze go wstawiam
				
SET @_POS_POS_2_INT = "UPDATE `oppo_e` p, `t_infpiek_dok_po_insert` dp";										
				UPDATE `oppo_e` p, `t_infpiek_dok_po_insert` dp
				SET
					p.IdnVat = dp.IdnVat
				WHERE
					p.GUID = dp.GUID
				;

					-- CALL oppo_E_Let_Update('one_rec', _GUID, '', '', '');
				
SET @_POS_POS_2_INT = "CALL opng_E_Let('Post', _IdnOper, '', ''); ";						
				CALL opng_E_Let('Post', _IdnOper, '', '');
				-- CALL ng_vat_e_build_from_po('', 'OP', _IdnOper, '', '');              Niepotrzebne wolane juz w opng_E_Let('Post', ...
				CALL _pozycje_cenyrabat_oppo_e('dla.IdnOper', _IdnOper, FALSE, FALSE, '', '');  --	ustaw ceny

				CALL opng_E_Let('Post', _IdnOper, '', '');
				
SET @_POS_POS_2_INT = "CALL opng_E_Let('ApplyAndClear', _IdnOper, '', '');";						
				CALL opng_E_Let('ApplyAndClear', _IdnOper, '', '');
				CALL opng_E_Let('Clear', _IdnOper, '', '');
				
			END IF;		-- 	'OP.Create.po'

			IF _MODE = 'ZM.Create.ng' THEN
				IF _INT_Dir_Status = 'PPT' THEN
				--	PPT
					CALL zmng_E_init('EDIT', _IdnOper, 'jmam_alg', '', '');
				ELSE
				--	 ZAMOWIENIA
					CALL zmng_E_init('INSERT', _IdnOper, 'jmam_alg', '1', '');
					CALL zmng_E_init_set('', _IdnOper, @Rodzaj, 0, '', '3', _KontrGUID, _ZMagazynu, 0, 0, 0, '', '', '', 0, '', '', '');
				END IF;
				
				CALL zmng_E_Run('before.run', _IdnOper, '', '', '');
				CALL zmng_E_Run('', _IdnOper, '', '', '');
				CALL zmng_E_Run('after.run', _IdnOper, '', '', '');
				
				IF _INT_Dir_Status != 'PPT' THEN
					UPDATE	`zmng_e` n, `infpiek_int_ng` dn
					SET
						n.nrzamw = CONCAT(LPAD(_ZMagazynu, 3, '0'), '/', dn.DOWN_NUM, '/', YEAR(dn.DOWN_DAT)),
						-- ??? n.przeliczenie_B_N = 1,
						n.Datawystaw = IF(dn.DOWN_DAT = '0000-00-00', CURRENT_DATE(), dn.DOWN_DAT),
						n.DataWplywu = IF(dn.DOWN_DAT = '0000-00-00', CURRENT_DATE(), dn.DOWN_DAT),
						n.TerminRealizacji = IF(dn.DOWN_TER = '0000-00-00', CURRENT_DATE(), dn.DOWN_TER),
						n.AreaGUID = (SELECT m.AreaGUID FROM Magazyny m WHERE m.IdnMagazynu = _ZMagazynu LIMIT 1),
						n.osobaGUID = (SELECT o.GUID FROM `osoby` o WHERE o.IDN = dn.DOWN_KTO LIMIT 1),
						n.transza_zamow = dn.DOWN_DOS --	Transza = DOWN_DOS
					WHERE
						dn.DOWN_HEX = _IDOK
						AND
						n.GUID = _IdnOper
					;
					UPDATE	`zmng_e` n, `osoby` o
					SET
						n.idnuser = o.idnuser,
						n.uwagi = CONCAT(n.uwagi, ' wyst.: ', o.Nazwisko, ' ', o.Imiona)
					WHERE
						n.GUID = _IdnOper
						AND
						o.GUID = n.osobaGUID
					;
					UPDATE	`zmng_e` n, `kontrah` k 
					SET
						n.KontrSymbol = k.Symbol
					WHERE
						n.GUID = _IdnOper
						AND
						k.GUID = n.KontrGUID
					;
				END IF;
			END IF;		-- 	'ZM.Create.ng'
			
			IF _MODE = 'ZM.Create.po' THEN
			--	 ZAMOWIENIA
				CALL _POS_E_NG('temp.t_infpiek_dok_po_insert', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');
SET @_POS_POS_2_INT = "INSERT IGNORE INTO `zmpo_e` ";		
				INSERT IGNORE INTO `zmpo_e` 
				(GUID, IdnNG, IdnTowr, Ilosc, CenaBrt, rabat, IdnVat, Uwagi, transza_zamow)
					SELECT
						dp.GUID,
						dp.IdnOper,
						dp.IdnTowr,
						IF(_INT_Dir_Status = 'PPT', dp.Ilosc_Potw, dp.Ilosc),
						dp.CenaBrt,
						dp.rabat,
						dp.IdnVat,
						dp.OPIS_TXT,
						IF(_DOWN_TYP = 'ZZZ', 9, dp.DOWN_DOS)		-- gdy zam. chemiczne to transza 9 = Zamwienia.Chemia
					FROM `t_infpiek_dok_po_insert` dp
					-- INNER JOIN `TowrNg` t ON t.GUID = dp.IdnTowr !!!
					ORDER BY dp.TowrSymbol
				ON DUPLICATE KEY UPDATE
					`zmpo_e`.IdnTowr = dp.IdnTowr,
					`zmpo_e`.iloscdo = IF(_INT_Dir_Status = 'PPT', `zmpo_e`.ilosc, `zmpo_e`.Iloscdo),
					`zmpo_e`.Ilosc = IF(_INT_Dir_Status = 'PPT', IF(dp.Ilosc_Potw != 0, dp.Ilosc_Potw, 0), dp.Ilosc)
				;
				--	UWAGA moze byc tak ze w PPT usunieto juz pozycje a w zamowieniu jeszcze jest!!! 
				--	ZATEM usun z zmpo_e te ktorych nie ma w `t_infpiek_dok_po_insert`
				UPDATE `zmpo_e` p
				SET 
					p.Ilosc = 0
				WHERE
					p.IdnNG = _IdnOper
					AND NOT EXISTS(SELECT dp.GUID FROM `t_infpiek_dok_po_insert` dp WHERE dp.GUID = p.GUID LIMIT 1)
				;
				CALL zmpo_E_Let_Update('lp', '', _IdnOper, @ht.COpTypSortTyp_Usluga_TreeNode_Symbol, ''); -- LP nalicz wg symbolu towaru
				CALL zmpo_E_Let_Update('from_zmng_e', '', _IdnOper, '', '');
				--	IdnVat TRACĘ w triggerze `zmpo_e` po wstawieniu IdnTowr - zatem raz jeszcze go wstawiam
				UPDATE `zmpo_e` p, `t_infpiek_dok_po_insert` dp
				SET
					p.IdnVat = dp.IdnVat
				WHERE
					p.GUID = dp.GUID
				;

					-- CALL oppo_E_Let_Update('one_rec', _GUID, '', '', '');
				
				CALL zmng_E_Let('Post', _IdnOper, '', '');
				
				CALL _pozycje_cenyrabat_zmpo_e('dla.IdnOper', _IdnOper, FALSE, FALSE, '', '');  --	ustaw ceny

				CALL zmng_E_Let('Post', _IdnOper, '', '');
				
				CALL zmng_E_Let('ApplyAndClear', _IdnOper, '', '');
			END IF;		-- 	'ZM.Create.po'
			
			IF _MODE = 'temp.t_infpiek_dok_po_insert' THEN
			--	 Kreuj `t_infpiek_dok_po_insert`
				UPDATE `infpiek_int_po` dp
				SET 
					dp.ARTYK_GUID = RTRIM(dp.ARTYK_GUID)
				WHERE
					dp.DOWN_HEX = _IDOK 
				;		
				UPDATE `infpiek_int_po` dp
				SET
					dp.ARTYK_GUID = 
					IFNULL(
					(SELECT GUID FROM `TowrNg` t WHERE t.kod_fiskalny = dp.WYRO_PLU ORDER BY t.IDN LIMIT 1), ''
					)
				WHERE
					dp.ARTYK_GUID = ''
					AND
					dp.DOWN_HEX = _IDOK 
				;
				DROP TEMPORARY TABLE IF EXISTS `t_infpiek_dok_po_insert`;
				CREATE TEMPORARY TABLE `t_infpiek_dok_po_insert`
					SELECT
						dp.INT_GUID AS GUID, -- 	CONCAT('Infpie_', dp.IPOZ_HEX) AS GUID,
						_IdnOper AS IdnOper,
						dp.ARTYK_GUID AS IdnTowr,
						-- było (SELECT GUID FROM `TowrNg` t WHERE t.kod_fiskalny = dp.WYRO_PLU ORDER BY t.IDN LIMIT 1) AS IdnTowr,
						IFNULL(dp.DOWP_ILE, 0.0) AS Ilosc,
						IFNULL(dp.DOWP_ILE_POTW, 0.0) AS Ilosc_Potw,
						IFNULL(dp.DOWP_BRU, 0.0) AS CenaBrt,
						IFNULL(dp.DOWP_RAB, 0.0) AS rabat,
						-- ??? 5 AS rodzaj_przeliczenia, 	-- Brutto z ceny brutto
						dp.INT_IDNVAT AS IdnVat,
						dp.OPIS_TXT,
						dn.DOWN_DOS - 1 AS DOWN_DOS,
						IFNULL(t.Symbol, '') AS TowrSymbol
					FROM `infpiek_int_po` dp 
					INNER JOIN `infpiek_int_ng` dn ON dn.DOWN_HEX = dp.DOWN_HEX
					LEFT JOIN `TowrNg` t ON t.GUID = dp.ARTYK_GUID
					WHERE
						dp.DOWN_HEX = _IDOK 
						-- AND
						-- dn.DOWN_HEX = dp.DOWN_HEX
				;
			END IF;		-- 	'temp.t_infpiek_dok_po_insert'
			
		END$$

DROP PROCEDURE IF EXISTS `_POS_POS_2_INT`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_POS_POS_2_INT`(
	IN _MODE VARCHAR(250),
	_IDOK VARCHAR(50),	
	_INT_Dir_Status VARCHAR(50),
	_Param_A VARCHAR(50)	
	)
/*
	dla INT
		CALL _POS_POS_2_INT('refresh.XXX', '', 'doINT', '1');		--	w _Param_A ile dni wstecz
		CALL _POS_POS_2_INT('refresh.XXX', '', 'PPT', '1');		--	w _Param_A ile dni wstecz
	
	CALL _POS_POS_2_INT('refresh.ALL', '', '', '');		
*/

S_POS_POS_2_INT: BEGIN
	DECLARE _Ile_Dni_Wstecz INTEGER DEFAULT 1;
	DECLARE _KontrGUID VARCHAR(38) DEFAULT '@ST_ZS';
	DECLARE _IdnOper, _DOWN_TYP VARCHAR(38);
	DECLARE _ZMagazynu INTEGER DEFAULT 0; --	KONT_HEX // juz nie KONT_ZAK
	DECLARE _DoMagazynu INTEGER DEFAULT 0; --	KONT_HEX // juz nie KONT_ZAK
	DECLARE _DOMM_HEX INTEGER DEFAULT 0; --	DOMM_HEX // dla MM-ki to docelowy magazyn/sklep

	IF _MODE = 'refresh.ALL' THEN
		CALL _POS_POS_2_INT('refresh.XXX', '', 'doINT', '3');		--	w _Param_A ile dni wstecz
		CALL _POS_POS_2_INT('refresh.XXX', '', 'PPT', '3');		--	w _Param_A ile dni wstecz
		CALL _Globals_Set("CALL _POS_POS_2_INT('refresh.ALL', '', '', '');", NOW());
	END IF;		-- 	'refresh.ALL'
	
	IF _MODE = 'refresh.ZamDD' THEN
		CALL _POS_POS_2_INT('refresh.XXX', '', 'doINT', '3');		--	w _Param_A ile dni wstecz
	END IF;		-- 	'refresh.ZamDD'
	
	IF _MODE = 'refresh.PPT' THEN
		CALL _POS_POS_2_INT('refresh.XXX', '', 'PPT', '3');		--	w _Param_A ile dni wstecz
	END IF;		-- 	'refresh.PPT'
	
	IF _MODE = 'refresh.XXX' THEN
SET @_POS_POS_2_INT = "refresh.XXX????";						
	/*******************************************************************************************************************************
		Zamowienia DD w POS czyli -> do ht jako ZM_OO
	*******************************************************************************************************************************/
		CALL _Session_Refresh_Variables('', '', '');
		IF _Param_A != '' THEN
			SET _Ile_Dni_Wstecz = _Param_A;
		END IF;

		SELECT 
			IFNULL(MAX(tn.INT_localtime), LOCALTIME()) AS Max_INT_localtime 
		FROM `infpiek_int_ng` tn
		WHERE
			tn.INT_Status = 'F'
			AND tn.INT_Dir_Status = 'doINT'
		INTO @Max_INT_localtime
		;
		
	--	1.	kreuję tymcz.
		DROP TEMPORARY TABLE IF EXISTS `t_infpiek_int_ng_2_INT`;
		CREATE TEMPORARY TABLE `t_infpiek_int_ng_2_INT`
			SELECT
				tn.*
			FROM `infpiek_int_ng` tn
			WHERE
				tn.INT_Status = ''
				AND
				tn.INT_Dir_Status = _INT_Dir_Status -- 'doINT', 'PPT' etc
				AND
				tn.INT_localtime > DATE_ADD(NOW(), INTERVAL -_Ile_Dni_Wstecz DAY)
				AND
				tn.DOWN_TYP IN ('0', '1', '3',  '8', 'E', 'R')
		;
		SELECT COUNT(*) FROM `t_infpiek_int_ng_2_INT` INTO @ROW_COUNT;
		-- SELECT ROW_COUNT() INTO @ROW_COUNT;
		IF IFNULL(@_POS_POS_2_INT.Select.ROW_COUNT, FALSE) THEN select @ROW_COUNT; END IF;
		-- IF _Param_A != '' THEN select @ROW_COUNT; END IF;
		IF @ROW_COUNT <= 0 THEN
		--	BRAK rekordów do przetwarzania
			-- DROP TEMPORARY TABLE IF EXISTS `t_infpiek_int_ng_2_INT`;
SET @_POS_POS_2_INT = CONCAT('_INT_Dir_Status:', _INT_Dir_Status, ' _Ile_Dni_Wstecz:', _Ile_Dni_Wstecz, ' @ROW_COUNT=', @ROW_COUNT, " LEAVE S_POS_POS_2_INT;");
			LEAVE S_POS_POS_2_INT;
		END IF;
		
			
	--	2.	ustawiam status na 'T' temporary czyli przetwarzam
		UPDATE `infpiek_int_ng` r, `t_infpiek_int_ng_2_INT` t
		SET
			r.INT_Status = 'T'	--	Temporary czyli przetwarzam
		WHERE
			r.DOWN_HEX = t.DOWN_HEX
		;
	--	3.	Pętla przetwarzania
SET @_POS_POS_2_INT = "Pętla przetwarzania";						
		BEGIN
		-- Operacje np. magazynowe
		
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE DOWN_HEX INTEGER DEFAULT 0;
		--	3.1 Kursor	
			DECLARE c_infpiek_int_ng_2_INT CURSOR FOR
				SELECT
					t.DOWN_HEX
				FROM `t_infpiek_int_ng_2_INT` t
			;	
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
		--	3.2 Open Kursor	
			OPEN c_infpiek_int_ng_2_INT;
			loop_c_infpiek_int_ng_2_INT: LOOP
				FETCH c_infpiek_int_ng_2_INT INTO 
					DOWN_HEX
				;
				IF done THEN
					LEAVE loop_c_infpiek_int_ng_2_INT;
				END IF;
			--	Wykonaj naliczanie
				CALL _POS_POS_2_INT('refresh.one.rec', DOWN_HEX, _INT_Dir_Status, '');
			END LOOP;
			CLOSE c_infpiek_int_ng_2_INT;
		
		END;
		
	--	4.	usuń tymczsowkę
		DROP TEMPORARY TABLE IF EXISTS `t_infpiek_int_ng_2_INT`;
	--	5.	przepisz status do infpie_dok_ng
	--	6.	usuń rekordy z `infpie_dok_ng_rec` ze statusem na 'F' czyli zakonczono 
		
	END IF;		-- 	'refresh.XXX'

	IF _MODE = 'refresh.one.rec' THEN
					SET @_POS_POS_2_INT = "refresh.one.rec";						
	/*******************************************************************************************************************************
		Pobiera jeden rekord i ....
	*******************************************************************************************************************************/
	--	1.	ustawiam status na 'E' czyli teraz przetwarzam (E ma znaczenie: error gdy zostanie nie zmieniony na F)
		CALL infpiek_int_Tools('set.Status', _IDOK, 'E'); 	-- 'E' czyli err
		
		SELECT 
			dn.INT_GUID,
			dn.DOWN_TYP,
			dn.KONT_HEX,	--	 juz nie dn.KONT_ZAK,
			IFNULL(
			(SELECT k.GUID FROM `kontrah` k WHERE k.magazyn_akwizytora = dn.KONT_HEX AND k.PlatnikGUID = '200' AND NOT k.deleted LIMIT 1), '200'		-- 200 to spolka z o.o.
			) AS KontrGUID,
			dn.DOMM_HEX
		FROM `infpiek_int_ng` dn
		WHERE
			dn.DOWN_HEX = _IDOK
		INTO
			_IdnOper,
			_DOWN_TYP,
			_ZMagazynu,
			_KontrGUID,
			_DOMM_HEX
		;
		 
		SET 
			@Rodzaj = 
				CASE _DOWN_TYP 	
					WHEN '1' THEN @ht.CZmOO 			-- = 27,			--  Zamówienie od odbiorcy 
					WHEN 'E' THEN @ht.CMGZW 			-- = 70,			--  Zwrot towaru do magazynu (ilosci na -) + MM na 7
					WHEN 'R' THEN @ht.CMGZW 			-- = 70,			--  zwrot reklamacyjny  do magazynu  (ilosci na -) + MM na 8
					WHEN '8' THEN @ht.CInwn 			-- = 39,			--  Inwentaryzacja / Remanent 
					WHEN '3' THEN @ht.CRzRW 			-- = 11,			--  RW - Dokument likwidacji (RW)
					WHEN 'M' THEN @ht.CMMMM 			-- = 9,				--  MM-ka
					ELSE
					@ht.CZmOO
					/*
					Typy Dokumentow pole `DOWN_TYP`:
					'1' = Zamowienia														ht		ZROBILEM	
					'0' = PPT																ht		ZROBILEM
					
					'E' = Dowod zwrotu do magazynu (ilosci na -)							ht		ZROBILEM
					'R' = Reklamacja - zwrot reklamacyjny  do magazynu (ilosci na -)		ht		ZROBILEM
					
					'3' = Dokument likwidacji (RW)											zoo		ZROBILEM
					'8' = Remanent 															zoo		z apro zrobimy
					
					'9' = Przychod towarow obcych											?
					'6' = Zwrot towarow obcych												?
					'4' = Zwrot na przecene													?  A u p.Jansza jako mm-ka
					'5' = Przyjecie na przecene												?
					*/					
				END,
			
			@IdnOper = _IdnOper,
			@KontrGUID = _KontrGUID,
			@_Tymczasowa = FALSE,
			@G = '',
			@ZMagazynu = _ZMagazynu
		;
		
		IF (_DOWN_TYP IN ('3', '8')) THEN
			IF SCHEMA() != 'zoo' AND SCHEMA() != 'httest' THEN LEAVE S_POS_POS_2_INT; END IF;
		ELSE
			IF SCHEMA() != 'ht' AND SCHEMA() != 'httest' THEN LEAVE S_POS_POS_2_INT; END IF;
		END IF;
						SET @_POS_POS_2_INT = "Wstawia dokument";						
		

	/*******************************************************************************************************************************
		Wstawia dokument
	*******************************************************************************************************************************/
		IF @Rodzaj IN (@ht.CZmOO, @ht.CZmDD) THEN
		/*********************************************************************************************************************
			ZAMOWIENIA/PPT
		*********************************************************************************************************************/
							SET @_POS_POS_2_INT = "IF @Rodzaj IN (@ht.CZmOO, @ht.CZmDD) THEN";		
							-- SET @PPT.Co = 'ZM.Create.ng';
			IF _DOWN_TYP = 'ZZZ' THEN
			--	ZMOO ale Chemiczne czyli na Magazyn 101
				SET _ZMagazynu = 101;
				
			END IF;
			
			SET 
				_DoMagazynu = _ZMagazynu,
				@_INT_Dir_Status = _INT_Dir_Status
			;
			
			IF _INT_Dir_Status = 'PPT' THEN
			--	PPT		******************************************************************************************************************
				SET 
					@ZM.Create.ng.Doc.Exists = Exists(
						SELECT n.GUID FROM `zmng` n WHERE n.GUID = _IdnOper
					)
				;
				IF NOT @ZM.Create.ng.Doc.Exists THEN
				--	UWAGA gdy nie ma dokumentu a jest to PPT to... rób jakby to było zamówienie
					SET @_INT_Dir_Status = 'doINT';
				END IF;
			END IF;
									SET @_POS_POS_2_INT = "CALL _POS_E_NG('ZM.Create.ng', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, @_INT_Dir_Status, '');";		
			--	w @_INT_Dir_Status mamy wypadkowa wartosc zatem NIE ZASTEPUJ zmiennej @_INT_Dir_Status zmienna _INT_Dir_Status !!!!!
			CALL _POS_E_NG('ZM.Create.ng', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, @_INT_Dir_Status, '');
							-- SET @PPT.Co = 'ZM.Create.po';
									SET @_POS_POS_2_INT = "CALL _POS_E_NG('ZM.Create.po', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');";		
			--	Tutaj mozna wstawiac _INT_Dir_Status !!!!!
			CALL _POS_E_NG('ZM.Create.po', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');
			
			IF _INT_Dir_Status = 'PPT' THEN
			--	PPT	zatem dodatkowo realizu	*****************************************************************************************************
				SET @IdnOperRealiz = CONCAT('WZ_z_PPT_', _IDOK);
				CALL _zmng_Realizuj('', _IdnOper, @IdnOperRealiz, 10, 0, NULL, 0, 0, FALSE, 0, '', '', '', 0, '', '', '', '');
			END IF;
		ELSE
		/*********************************************************************************************************************
			Operacje
		*********************************************************************************************************************/
			IF @Rodzaj IN (@ht.CMGZW, @ht.CInwn) THEN
				SET
					_DoMagazynu = _ZMagazynu,
					_ZMagazynu = 0
				;
			ELSEIF @Rodzaj IN (@ht.CMMMM) THEN
				SET
					_DoMagazynu = _DOMM_HEX
				;
			END IF;
			
			CALL _POS_E_NG('OP.Create.ng', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');
			CALL _POS_E_NG('OP.Create.po', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, '');
			IF @Rodzaj = @ht.CMGZW THEN
			-- LEAVE S_POS_POS_2_INT;
			/*********************************************************************************************************************
				MM - Zwrot musi konczyc sie MM-ką na 7 lub 8 magazyn
			*********************************************************************************************************************/
				SET 
					-- _IdnOper = CONCAT('MM-', _IDOK), -- MD5(_IdnOper),
					_IdnOper = MD5(_IdnOper),
					_ZMagazynu = _DoMagazynu,
					_DoMagazynu = IF(_DOWN_TYP = 'E', 7, 8),
					@Rodzaj = @ht.CMMMM 		--	= 09  MM 
				;
				SET @IdnOper = _IdnOper;
				CALL _POS_E_NG('OP.Create.ng', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, 'MM-ka!!!!');
				CALL _POS_E_NG('OP.Create.po', _IDOK, _DOWN_TYP, _IdnOper, @Rodzaj, _KontrGUID, _ZMagazynu, _DoMagazynu, _INT_Dir_Status, 'MM-ka!!!!');
				
			END IF;
			
		END IF;
		DROP TEMPORARY TABLE IF EXISTS `t_infpiek_dok_po_insert`;
		
	--	3.	ustawiam status na 'F' czyli zakonczono 
		CALL infpiek_int_Tools('set.Status', _IDOK, 'F'); 	-- 'F' czyli zakonczono 
	END IF;		-- 	'refresh.one.rec'
END$$

/* ************************************************************************************************************************************************************************
	Skrypty
************************************************************************************************************************************************************************ */	

DROP PROCEDURE IF EXISTS `_Script_Ht_2_Zoo`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `_Script_Ht_2_Zoo`(
	IN _MODE VARCHAR(250),
	_interval_min INTEGER,	
	_oddaty DATE,
	_Param_A VARCHAR(50),
	_Param_B VARCHAR(50)	
	)
/*
	dla INT
		CALL _Script_Ht_2_Zoo('ht.clear.tables', 0, "2013-07-11", '', '');		--	czysc tabele ht_2_zoo_xxx
		CALL _Script_Ht_2_Zoo('ht.prepare.tables', 120, "2013-07-11", '', '');		--	przygotuj tabele ht_2_zoo_xxx co 2h (120 min)
		
		CALL _Script_Ht_2_Zoo('zoo.read.tables', 0, "2013-07-11", '', '');		--	odczytaj tabele z bazy rht do bazy zoo
*/

S_Script_Ht_2_Zoo: BEGIN
	-- DECLARE _oddaty DATE DEFAULT "2013-07-11";
	DECLARE _KontrGUID_zoo VARCHAR(38) DEFAULT '{4FF688B3-3025-4B5B-9752-26CD66737EF8}';
	
	SET _oddaty = IFNULL(_oddaty, DATE('2013-07-11'));
	
	IF _MODE = 'ht.clear.tables' THEN
		DROP TABLE IF EXISTS `ht_2_zoo_TowrNg`;
		DROP TABLE IF EXISTS `ht_2_zoo_cennik`;
		DROP TABLE IF EXISTS `ht_2_zoo_opng`;
		DROP TABLE IF EXISTS `ht_2_zoo_oppo`;
		DROP TABLE IF EXISTS `ht_2_zoo_oppo_corr`;
		DROP TABLE IF EXISTS `ht_2_zoo_opng_vat`;
	END IF; -- _MODE = 'ht.clear.tables' 
	
	IF _MODE = 'ht.prepare.tables' THEN
	/***************************************************************************************************************************************************************************************************
	S K R Y P T   ht_2_zoo_rht.sh Przygotowanie tabel ht_2_zoo_xxx ktore beda "wyrzucone" do zoo 
	***************************************************************************************************************************************************************************************************/
		IF SCHEMA() != 'ht' THEN LEAVE S_Script_Ht_2_Zoo; END IF;
		
		CALL _Script_Ht_2_Zoo('ht.clear.tables', 0, _oddaty, '', '');
		
		CREATE TABLE `ht_2_zoo_TowrNg` LIKE `TowrNg`;
		CREATE TABLE `ht_2_zoo_cennik` LIKE `cennik`;
		CREATE TABLE `ht_2_zoo_opng` LIKE `opng`;
		CREATE TABLE `ht_2_zoo_oppo` LIKE `oppo`;
		CREATE TABLE `ht_2_zoo_oppo_corr` LIKE `oppo_corr`;
		CREATE TABLE `ht_2_zoo_opng_vat` LIKE `opng_vat`;

	--	Wstaw karty towarowe
		INSERT INTO `ht_2_zoo_TowrNg` 
			SELECT * FROM TowrNg t WHERE katcol3 = 1 AND DATE_ADD(t.localtime, INTERVAL _interval_min MINUTE) > NOW() 
		;
	--	Wstaw cenniki kart towarowych
		INSERT INTO `ht_2_zoo_cennik` 
			SELECT c.* FROM TowrNg t, cennik c WHERE t.guid=c.idntowr AND t.katcol3=1 AND c.klasa=10 AND DATE_ADD(c.localtime, INTERVAL _interval_min MINUTE) > NOW()
		;
	--	Wstaw naglowki WZ-ek i FV (FV do WZ)
		INSERT IGNORE INTO `ht_2_zoo_opng` 
			SELECT n.* 
			FROM `opng` n 
			WHERE 
				n.Deleted = 0
				AND
				n.platnikguid = '200' 
				AND 
				n.datawystaw >= _oddaty
				AND 
				n.Status = 0
				AND 
				n.rodzaj IN (1, 10)
				-- AND n.zmagazynu BETWEEN 201 AND 299
				AND 
				DATE_ADD(n.localtime, INTERVAL _interval_min MINUTE) > NOW()
		;
	--	Wstaw naglowki Korekt FV-
		INSERT IGNORE INTO `ht_2_zoo_opng` 
			SELECT n.* 
			FROM `opng` n 
			WHERE 
				n.platnikguid = '200' 
				AND 
				n.datawystaw >= _oddaty
				AND 
				n.Status = 0
				AND 
				n.rodzaj = 3
				-- AND n.domagazynu BETWEEN 201 AND 299
				AND 
				DATE_ADD(n.localtime, INTERVAL _interval_min MINUTE) > NOW()
		;
		UPDATE `ht_2_zoo_opng` n SET n.Tag = 0;
		
		UPDATE `ht_2_zoo_opng` n, `kontrah` k
		SET
			n.Tag = k.magazyn_Akwizytora
		WHERE 
			n.KontrGUID = k.GUID
			AND
			n.Rodzaj IN (1, 3)
			AND
			NOT (n.ZMagazynu + n.DoMagazynu) BETWEEN 201 AND 299 	--	Ale nie dla sklepow
		;
	--	Wstaw pozycje do wstawionych juz naglowkow WZ/FV/FV-
		INSERT IGNORE INTO `ht_2_zoo_oppo` 
			SELECT o.* FROM `ht_2_zoo_opng` n, `oppo` o WHERE n.guid = o.idnoperfin AND (n.ZMagazynu + n.DoMagazynu) BETWEEN 201 AND 299 	--	Ale tylko dla sklepow
		;
	--	Wstaw pozycje zapisow korygujacych `oppo_corr` do wstawionych juz naglowkow WZ/FV/FV-
		INSERT IGNORE INTO `ht_2_zoo_oppo_corr` 
			SELECT oc.* FROM `ht_2_zoo_opng` n, `oppo_corr` oc WHERE n.Rodzaj = 3 AND n.guid = oc.IdnOper_corr AND (n.ZMagazynu + n.DoMagazynu) BETWEEN 201 AND 299 	--	Ale tylko dla sklepow
		;
	--	Wstaw tabelkę VAT z `opng_vat`
		INSERT IGNORE INTO `ht_2_zoo_opng_vat` 
			SELECT ov.* FROM `ht_2_zoo_opng` n, `opng_vat` ov WHERE n.guid = ov.IdnOper
		;
	--	Na koniec ustaw status do przerzucanych dokumentow
		UPDATE `opng` n, `ht_2_zoo_opng` t SET n.status = 1 WHERE t.guid = n.guid;

	END IF; -- _MODE = 'ht.prepare.tables' 
	
	IF _MODE = 'zoo.read.tables' THEN
	/***************************************************************************************************************************************************************************************************
	S K R Y P T   ????.sh odczyt tabel ktore beda "wrzucone" w zoo 
	***************************************************************************************************************************************************************************************************/
		IF SCHEMA() != 'zoo' THEN LEAVE S_Script_Ht_2_Zoo; END IF;
		
		DROP TEMPORARY TABLE IF EXISTS zoo.`t_zoo_opng`;
		DROP TEMPORARY TABLE IF EXISTS zoo.`t_zoo_oppo`;
		DROP TEMPORARY TABLE IF EXISTS zoo.`t_zoo_oppo_corr`;
		DROP TEMPORARY TABLE IF EXISTS zoo.`t_zoo_opng_vat`;
	--	Kreuj tymczasowki
		CREATE TEMPORARY TABLE zoo.`t_zoo_opng` SELECT * FROM rht.`ht_2_zoo_opng`;
		CREATE TEMPORARY TABLE zoo.`t_zoo_oppo` SELECT * FROM rht.`ht_2_zoo_oppo`;
		CREATE TEMPORARY TABLE zoo.`t_zoo_oppo_corr` SELECT * FROM rht.`ht_2_zoo_oppo_corr`;
		CREATE TEMPORARY TABLE zoo.`t_zoo_opng_vat` SELECT * FROM rht.`ht_2_zoo_opng_vat`;
		
	/* **************************************************************************************************************************************************************************************************
		Modyfikuj tymczasowki
	************************************************************************************************************************************************************************************************** */
	--	Naglowek
		--	sprzed. na zakup pozostaly
		UPDATE zoo.`t_zoo_opng` n
		SET
			n.Rodzaj = 23,
			n.zmagazynu = n.Tag
		WHERE 
			n.Rodzaj = 1
			AND
			n.DoMagazynu = 0
			AND
			NOT (n.zmagazynu BETWEEN 201 AND 299)
		;
		--	korekta sprzed. na korekta zakup pozostaly
		UPDATE zoo.`t_zoo_opng` n
		SET
			n.Rodzaj = 25,
			n.domagazynu = n.Tag
		WHERE 
			n.Rodzaj = 3
			AND
			n.ZMagazynu = 0
			AND
			NOT (n.DoMagazynu BETWEEN 201 AND 299)
		;
	
		UPDATE zoo.`t_zoo_opng` n
		SET
			-- n.idn = 0,
			-- Gdy WZ (10) to zamien na PZ (12) w przeciwnym razie (z faktury 1) zrob fakture zakupu (5) a z korekty sprzedazy 3 zrob korekte zakupu 7
			n.rodzaj = CASE n.rodzaj
			WHEN 10 THEN 12
			WHEN 1 THEN 5
			WHEN 3 THEN 7
			END
		WHERE 
			NOT(n.Rodzaj IN (23, 25))
		;
		
		UPDATE zoo.`t_zoo_opng` n, `operdoc` d
		SET	
			n.rv_id = CASE n.rv_id
			WHEN 0 THEN 0
			WHEN 2021 THEN 2020
			END,
			n.wzordekr = '',
			n.status = 0,
			n.nrdokmag ='',
			n.zaksiegowana = 0,
			n.info1 = 'IMPORT',
			n.CWN = NOT n.CWN,
			n.grupaoper = d.Grupa,
			n.gruparozrach = d.rztyp,
			n.CRozliczone = 0, 
			n.CRozliczoneWal = 0,
			n.nierozliczony = 2,
			n.Calc_Ver = 4
		WHERE 
			d.Rodzaj = n.Rodzaj
		;
		
		UPDATE zoo.`t_zoo_opng` n, zoo.`kontrah` k 
		SET
			n.kontrsymbol = k.symbol,
			n.kontrnazwa = k.nazwa,
			n.kontofk = k.kontofk,
			n.kontrguid = k.guid,
			-- byĹ‚o n.platnikguid = k.guid A JEST...
			n.platnikguid = k.platnikGUID
		WHERE 
			k.GUID = _KontrGUID_zoo	-- dodano 4-10-2013 po kosultacji z p.Markiem ("AL Piekarnia-Cukiernia spĂłĹ‚ka z ograniczonÄ… odpowiedzialnoĹ›ciÄ… S.K.A.") i ponowny import
			-- k.symbol = n.kontrsymbol -- CONCAT('KONKOL ', MID(n.kontrsymbol, 3, 2))
		;
	--	Pozycje
		UPDATE zoo.`t_zoo_oppo` p 
		SET 
			-- p.idn = 0,
			-- Gdy WZ (10) to zamien na PZ (12) w przeciwnym razie (z faktury 1) zrob fakture zakupu (5) a z korekty sprzedazy 3 zrob korekte zakupu 7
			p.rodzaj = CASE p.rodzaj
			WHEN 10 THEN 12
			WHEN 1 THEN 5
			WHEN 3 THEN 7
			END,
			p.zrealizowana = 0
		;
	--	Magazyn DO Naglowek
		UPDATE zoo.`t_zoo_opng` n 
		SET 
			n.domagazynu = n.zmagazynu,
			n.zmagazynu = 0,
			n.idn = 0
		WHERE
			n.zmagazynu != 0
			AND
			n.idn != 0
		;
	--	Magazyn Z Naglowek
		UPDATE zoo.`t_zoo_opng` n 
		SET 
			n.zmagazynu = n.domagazynu,
			n.domagazynu = 0,
			n.idn = 0
		WHERE
			n.domagazynu != 0
			AND
			n.idn != 0
		;
	-- 	IDN	
		UPDATE zoo.`t_zoo_opng` n
		SET
			n.idn = 0
		;
	--	Magazyn DO Pozycje
		UPDATE zoo.`t_zoo_oppo` p 
		SET 
			p.domagazynu = p.zmagazynu,
			p.zmagazynu = 0,
			p.idn = 0
		WHERE
			p.zmagazynu != 0
			AND
			p.idn != 0
		;
	--	Magazyn Z Pozycje
		UPDATE zoo.`t_zoo_oppo` p 
		SET 
			p.zmagazynu = p.domagazynu,
			p.domagazynu = 0,
			p.idn = 0
		WHERE
			p.domagazynu != 0
			AND
			p.idn != 0
		;
	-- 	IDN	
		UPDATE zoo.`t_zoo_oppo` p 
		SET
			p.idn = 0
		;
	--	Pozycje korekty
		UPDATE zoo.`t_zoo_oppo_corr` oc 
		SET 
			oc.idn = 0
		;
	--	Tabelka vat musi miec dla zakupow pozostalych Fixed = 1
		UPDATE zoo.`t_zoo_opng_vat` v,  zoo.`t_zoo_opng` n
		SET 
			v.Fixed = 1
		WHERE
			v.IdnOper = n.GUID
			AND
			n.Rodzaj IN (23, 25)
		;
	/* **************************************************************************************************************************************************************************************************
		Wstawiaj
	************************************************************************************************************************************************************************************************** */
	--	Naglowek
	/**/
		INSERT IGNORE INTO zoo.`opng` 
			SELECT n.* FROM zoo.`t_zoo_opng` n
		;
	/*
		REPLACE zoo.`opng` 
			SELECT n.* FROM zoo.`t_zoo_opng` n
		;
	/**/
	--	Pozycje
	/*
		INSERT IGNORE INTO zoo.`oppo` 
			(
				guid,idnoper,idntowr,ilosc,cenanet,cenabrt,wartnetwal,wartnet,wartvat,wartbrt,rodzaj_przeliczenia,uwagi,calc_ver,
				zmagazynu,domagazynu,rodzaj,towrsymb,towrnazwa,datawystaw,datasprzed,usluga,jednostka,idnvat,lp,tymczasowa,
				kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9
			)
			SELECT
				guid,idnoper,idntowr,ilosc,cenanet,cenabrt,wartnetwal,wartnet,wartvat,wartbrt,rodzaj_przeliczenia,uwagi,calc_ver,
				zmagazynu,domagazynu,rodzaj,towrsymb,towrnazwa,datawystaw,datasprzed,usluga,jednostka,idnvat,lp,tymczasowa,
				kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9
			FROM zoo.`t_zoo_oppo` p
		ON DUPLICATE KEY UPDATE
			zoo.`oppo`.IdnOperFin = p.IdnOperFin,
			zoo.`oppo`.IdnWz = p.IdnWz
		;
	*/
		REPLACE zoo.`oppo` 
		-- INSERT IGNORE INTO zoo.`oppo` 
			(
				guid,idnoper,idnWZ,idnoperFin,idntowr,ilosc,cenanet,cenabrt,wartnetwal,wartnet,wartvat,wartbrt,rodzaj_przeliczenia,uwagi,calc_ver,
				zmagazynu,domagazynu,rodzaj,towrsymb,towrnazwa,datawystaw,datasprzed,usluga,jednostka,idnvat,lp,tymczasowa,
				kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9
			)
			SELECT
				guid,idnoper,idnWZ,idnoperFin,idntowr,ilosc,cenanet,cenabrt,wartnetwal,wartnet,wartvat,wartbrt,rodzaj_przeliczenia,uwagi,calc_ver,
				zmagazynu,domagazynu,rodzaj,towrsymb,towrnazwa,datawystaw,datasprzed,usluga,jednostka,idnvat,lp,tymczasowa,
				kat0,kat1,kat2,kat3,kat4,kat5,kat6,kat7,kat8,kat9
			FROM zoo.`t_zoo_oppo` p
		;
	--	Pozycje korekty
		INSERT IGNORE INTO zoo.`oppo_corr` 
			SELECT oc.* FROM zoo.`t_zoo_oppo_corr` oc
		;
	--	Tabelka VAT
		INSERT IGNORE INTO zoo.`opng_vat` 
			SELECT ov.* FROM zoo.`t_zoo_opng_vat` ov
		;
	--	oppo_mag
-- TODO: #insert ignore into zoo.opng_vat select v.* from $db.opng_vat v, oo where v.idnoper=oo.guid;
-- TODO: - wykonac w petli dla kazdego t_zoo_opng mod UPDATE_OPNG_REC
		CALL ht_oppo_mag_tool('UPDATE_DATE_RANGE', '', '', DATE_SUB(CURRENT_DATE,INTERVAL 1 DAY), CURRENT_DATE);
	
		DROP TEMPORARY TABLE zoo.`t_zoo_opng`;
		DROP TEMPORARY TABLE zoo.`t_zoo_oppo`;
		DROP TEMPORARY TABLE zoo.`t_zoo_oppo_corr`;
		DROP TEMPORARY TABLE zoo.`t_zoo_opng_vat`;
		
	END IF; -- _MODE = 'zoo.read.tables' 
END$$	


DELIMITER ;
