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);
		CALL opti_E_2_O('PURCHASE_ORDER', '', '', _Result);
	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
	ERP do OPTI
************************************************************************************************************************************************************************ */	

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('', '', '', '');
		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 12 
				WHEN 'EXOFFSET_REALISED' THEN 9 
				WHEN 'OUTCOME_REALISED' THEN 10
				WHEN 'INTOUT_REALISED' THEN 11 
				WHEN 'DESTROY_REALISED' THEN 11 
				WHEN 'STOCK_REALISED' THEN @ht.CInwn	-- (39) kiedys -101 
			END,
			_Rodzaj_Typ = IF(_MsgType IN ('DESTROY_REALISED', 'PURCHASE_OFFER'), 1, 0)
		;	
		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
		
		;
		/*
		SELECT 
			_IdnOper,
			_Rodzaj,
			_DATE_R,
			_KontrGUID,
			@ht.doc.MagTyp,
			_WHSE_CODE,
			_zmagazynu,
			_domagazynu,
			CODE_zmagazynu,
			_SRC_WHSE_CODE,
			CODE_domagazynu,
			_DST_WHSE_CODE
		;
		*/
		IF _FROM = 'OP' THEN
			SET @OP = 'INSERT';
		--	Operacje
			CALL opng_E_LetInsert('', _IdnOper, _Rodzaj, _Rodzaj_Typ, _KontrGUID, _zmagazynu, _domagazynu, FALSE, 0, '', '', '', '', '', '');
			
			SET @IdnOper = _IdnOper;
			UPDATE `opng_e` n 
			SET
				n.DataWystaw = _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_LetInsert('', _IdnOper, _Rodzaj, _Rodzaj_Typ, _KontrGUID, _idnmagazyn, _idnmagazyn, _Tymczasowa, 0, '', '', '', _DATE_PLANNED, '', '');
			SET @IdnNg = _IdnOper;
			UPDATE `zmng_e` n 
			SET
				n.nrzamw = _ORDER_NUMBER,
				n.Info1 = CONCAT('WMS ID=' , _RecUID)
			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_LetInsert('IdnTowr', _oppo_GUID, _IdnOper, _IdnTowr, _Qty, _CenaNet, '', '', '', '');
					UPDATE `oppo_e` p 
					SET
						lp = _lp,
						-- POTEM!!!!!!!!!!! p.GUIDzm = _GUIDzm,
						p.DataWystaw = _DATE_R
					WHERE
						p.GUID = _oppo_GUID
					;	
					
				ELSE
				--	Zamówienia
					SET @_oppo_GUID = _oppo_GUID;
					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;
		
		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_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_LetApply('', _IdnOper, '', '');
			CALL opng_E_LetCancel('', _IdnOper, '', '');
			CALL opng_E_LetEnd('', _IdnOper, '', '');
			END IF;
		ELSE
		--	Zamówienia
				
			CALL zmng_E_LetPost('', _IdnOper, '', '');
		--	Na koniec Apply i End
			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 = '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$$



DELIMITER ;

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

CALL opti_E_2_O('K', '', '', @_Result);
CALL opti_E_2_O('T', '', '', @_Result);
CALL opti_E_2_O('KiT', '', '', @_Result);	


SELECT
opti_Get_DateTime('e2o.MAX', 'CNTR_CARD', '1942-07-22 07:12','', ''),
opti_Get_DateTime('e2o.MAX', 'ITEM_CARD', '1942-07-22 07:12','', ''),
opti_Get_DateTime('e2o.MAX', 'ITEM_XXX', '1942-07-22 07:12','', '')
;

CALL opti_E_2_O('K', '', '', @_Result);
CALL opti_E_2_O('T', '', '', @_Result);
CALL opti_E_2_O('KiT', '', '', @_Result);

SELECT COUNT(*) FROM `opti_msg_e2o_header` WHERE STATUS = 'S';
SELECT MIN(ID), MAX(ID), SUM(1), SUM(STATUS = 'T'), SUM(STATUS = 'S')  FROM `opti_msg_e2o_header`;
SELECT COUNT(*) FROM `opti_msg_e2o_detail`;
SELECT * FROM `opti_msg_e2o_detail` ORDER BY H_ID DESC LIMIT 100;

UPDATE `opti_msg_e2o_header` SET STATUS = 'C';

-- DELETE FROM `opti_msg_e2o_header` WHERE ID > 5577;   DELETE FROM `opti_msg_e2o_header` WHERE TYPE = 'CNTR_CARD';
-- DELETE FROM `opti_msg_e2o_detail`;

SELECT * FROM `opti_msg_e2o_header` ORDER BY ID DESC;
SELECT * FROM `opti_msg_e2o_detail` ORDER BY H_ID DESC;

CALL opti_tools('PREPARE.opti_msg_e2o_header.STATUS=T', '', '', '', '');
CALL opti_tools('SELECT.opti_msg_e2o_header.STATUS=T', '', '', '', '');
CALL opti_tools('SELECT.opti_msg_e2o_detail.STATUS=T', '', '', '', '');

CALL opti_tools('DISABLE.opti_msg_e2o_detail.for.ID', '25593', '', '', '');
CALL opti_tools('FINISH.opti_msg_e2o_header.STATUS=T', '', '', '', '');

SELECT 
opti_Get_DateTime('ng.RemoteTime', 'opng', '','', ''),
opti_Get_DateTime('ng.RemoteTime', 'zmng', '','', '')
;


--EDYCJA
SELECT * FROM kontrah WHERE dostawca = 1 AND Idn < 200 AND DELETED = 0sa1;
SELECT * FROM TowrNg WHERE Idn < 200;

		SELECT 
		*
		FROM `opti_msg_o2e_header`
		WHERE 
			H_ID =  _RecUID  
			AND C1 = 'H'
		;
		SELECT 
			H_ID, C1, R_id, C2, C3, C4, C5, C6, D1, F1,
			CONCAT('O2E_', MD5(CONCAT(H_ID, r_id, c1, c2)) )  
		FROM `opti_msg_o2e_detail`
		WHERE 
			H_ID =  4  
			-- AND C1 = 'H'
		;


	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 = 4  
		AND d.C1 = 'D'
	;

CALL ht_init('');
CALL C_ht('', '', '', '');


CALL opti_O_2_E('OP.one.rec', '', 12, 'PURCHASE_ORDER', @_Result);
CALL opti_O_2_E('OP.one.rec', '', 12, '', @_Result);
CALL opti_O_2_E('OP.one.rec', '', 14, '', @_Result);




CALL opti_O_2_E('OP', '', '', '', @_Result);
DESC oppo_instancje_e;
	SELECT 
		h.*
	FROM `opti_msg_o2e_header` h
	-- INNER JOIN `TowrNg` t ON t.IDN = d.C2
	WHERE 
		TRUE
		AND h.Status != 'F'
		-- AND type = 'INCOME_REALISED'
	ORDER BY Date_c DESC	
	;

	SELECT 
		d.R_ID, 
		C1,
		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 
		TRUE
		AND d.H_ID = 58  
		AND d.C1 = 'D'
	;

	SELECT 
		d.* 
	FROM `opti_msg_o2e_detail` d
	WHERE 
		TRUE
		-- AND d.H_ID = 48  
		AND d.C1 = 'H'
		-- AND d.C3 = 'RPZ/00016/2012'
	;

SELECT * FROM opng WHERE rodzaj = -101;
SELECT 
n.GUID, n.Netto, n.Info1,
n.* 
FROM `opng` n
WHERE 
n.datawystaw > '2012-06-25'
AND n.rodzaj = 12
;
-- RPZ/00015/2012





*/
