DELIMITER $$

DROP PROCEDURE IF EXISTS `ht_Kompensata3Tool`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_Kompensata3Tool`(
	IN _MODE VARCHAR(250),
	IN _Key VARCHAR(38),
	IN _Param_A VARCHAR(255),
	IN _Param_B VARCHAR(255),
	IN _Param_C VARCHAR(255),
	IN _Param_D VARCHAR(255)
	)
	READS SQL DATA
SPROC: BEGIN 
/*
	_MODE:
	0:	Dziala na dokumentach z data od _DateOd do _DateDo limit _LIMIT 
	
	1:	Dziala na konkretnjym dokumencie GUID = _GUIDFin
	
	
	DECLARE SqlText TEXT;
	DECLARE NonText VARCHAR(20) DEFAULT '*** poufne ***';
	DECLARE _GUIDFin VARCHAR(38);
	DECLARE _MaxCheck INT; 
	DECLARE _IsOK BOOLEAN;
*/
	DECLARE _NextSearch BOOLEAN;
	DECLARE _New_GUID VARCHAR(38);
	DECLARE _New_oppo_GUID VARCHAR(38);
	DECLARE _KontrGUID VARCHAR(38);




/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.init.date'
		Uzupelnia daty
		_Param_A, _Param_B, _Param_C, _Param_D
		CALL ht_Kompensata3Tool('wiz.init.date', '', _Param_A, _Param_B, _Param_C, _Param_D);		
		CALL ht_Kompensata3Tool('wiz.init.date', '', '2012-02-01', '2012-03-01', '2012-02-01', '2012-03-01');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.init.date' THEN
		SET 
			@session.komps3.DateZW_Od = _Param_A,
			@session.komps3.DateZW_Do = _Param_B,
			@session.komps3.DateOR_Od = _Param_C,
			@session.komps3.DateOR_Do = _Param_D
		;	
		CALL ht_oppo_Delta('Delta.Init', '', '', '', ''); -- w desperacji bo wciaz brakuje `t_ba_oppo`
	END IF;	-- _MODE = 'wiz.init.date'
	

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.init.select.kontrah'
		Zwraca kontrahentów z polem Checked
		CALL ht_Kompensata3Tool('wiz.init.select.kontrah', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.init.select.kontrah' THEN
		CALL _Session_Refresh_Variables('SYS', '', '');
		SET 
			@session.komps3.only.noncorrected.orig = TRUE,
			@session.komps3.use.tymczasowa.1 = TRUE, -- (@session.globals.Owner IN ('KONKOL')),
			@session.komps3.use.graniczna.korekta = TRUE, -- (@session.globals.Owner IN ('KONKOL')),
			@session.komps3.use.OdNowychDoStarych = TRUE -- (@session.globals.Owner IN ('KONKOL'))

		;
	
		/* BYŁO
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_kontrahenta_z_dat_3k`;
		CREATE TEMPORARY TABLE `t_zwroty_kontrahenta_z_dat_3k`
			SELECT 
				n.kontrguid, n.kontrsymbol, n.kontrnazwa, 
				_VarInt(0) AS Checked, 
				n.zmagazynu, n.domagazynu, m.Opis AS Magazyn, p.IdnWZ, _VarInt(0) AS Invalid,
				DATE(@session.komps3.DateZW_Od) AS DateOd,
				DATE(IFNULL(nk.DataWystaw, @session.komps3.DateZW_Od)) AS LastKorDoZWDate
			FROM `opng` n 
			LEFT JOIN `Magazyny` m ON m.IdnMagazynu = n.domagazynu
			INNER JOIN `oppo` p ON p.IdnOper = n.GUID
			INNER JOIN `kontrah` k ON k.GUID = n.kontrguid
			LEFT JOIN `ht_kontrah_info` i ON (i.KontrGuid = n.KontrGuid) 
			LEFT JOIN `opng` nk ON (i.LastKorDoZWGUID != '') AND (nk.GUID = i.LastKorDoZWGUID) 
			WHERE
				IFNULL(n.kontrguid, '') != '' AND
				k.limit_zwrotu > 0 AND
				-- n.datawplywu >= @session.komps3.DateZW_Od AND 
				n.datawplywu >= DATE(IFNULL(nk.DataWystaw, @session.komps3.DateZW_Od)) AND 
				n.datawplywu <= @session.komps3.DateZW_Do AND 
				n.rodzaj=70 AND 
				-- n.rodzaj2 = 1 AND
				p.rodzaj_typ = 1 AND
				p.Ilosc <> 0
			ORDER BY kontrsymbol
		;
		
		
		SELECT DISTINCT 
			k.*
		FROM `t_zwroty_kontrahenta_z_dat_3k` k 
		WHERE
			k.IdnWZ = '' 
		GROUP BY kontrguid
		ORDER BY kontrsymbol
		;
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_kontrahenta_z_dat_3k`;
		
		*/
		
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_kontrahenci`;
		CREATE TEMPORARY TABLE `t_zwroty_kontrahenci`
			SELECT 
				n.kontrguid,
				DATE(GREATEST(DATE(IFNULL(nk.DataWystaw, @session.komps3.DateZW_Od)), DATE(@session.komps3.DateZW_Od))) AS DateZW_Od,
				DATE(@session.komps3.DateZW_Do) AS DateZW_Do,
				_VarInt(0) AS Invalid,
				_VarInt(0) AS Checked, 
				n.zmagazynu, n.domagazynu, m.Opis AS Magazyn,
				k.AkwizytorGUID
			FROM `opng` n 
			INNER JOIN `kontrah` k ON k.GUID = n.kontrguid
			LEFT JOIN `ht_kontrah_info` i ON (i.KontrGuid = n.KontrGuid) 
			LEFT JOIN `opng` nk ON (i.LastKorDoZWGUID != '') AND (nk.GUID = i.LastKorDoZWGUID) AND @session.komps3.use.graniczna.korekta
			LEFT JOIN `Magazyny` m ON m.IdnMagazynu = n.domagazynu
			WHERE
				IFNULL(n.kontrguid, '') != '' 
				AND
				k.limit_zwrotu > 0 
				AND
				n.datawplywu BETWEEN DATE(@session.komps3.DateZW_Od) AND DATE(@session.komps3.DateZW_Do)
				AND
				IF(_Key != '', k.AkwizytorGUID = _Key, TRUE)
				AND 
				n.rodzaj = 70 
				-- n.rodzaj2 = 1 
				-- AND
				-- p.rodzaj_typ = 1 AND
			GROUP BY kontrguid

		;
		ALTER TABLE `t_zwroty_kontrahenci`
			ADD UNIQUE kontrguid(kontrguid),
			ADD INDEX DateZW_Od(DateZW_Od),
			ADD INDEX DateZW_Do(DateZW_Do)
		;
		
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_kontrahenci_p`;
		CREATE TEMPORARY TABLE `t_zwroty_kontrahenci_p`
			SELECT 
				n.GUID, n.kontrguid	
			FROM `t_zwroty_kontrahenci` k	
			INNER JOIN `opng` n ON k.KontrGUID = n.KontrGUID AND n.datawplywu BETWEEN k.DateZW_Od AND k.DateZW_Do
			INNER JOIN `oppo` p ON p.IdnOper = n.GUID
			WHERE 
				TRUE
				AND
				n.datawplywu BETWEEN DATE(@session.komps3.DateZW_Od) AND DATE(@session.komps3.DateZW_Do)
				-- jmam n.datawplywu BETWEEN k.DateZW_Od AND k.DateZW_Do
				-- n.datawplywu >= k.DateZW_Od 
				-- AND
				-- n.datawplywu <= k.DateZW_Do
				AND
				n.rodzaj = 70 
				AND
				p.rodzaj_typ IN (0, 1)
				AND
				p.GUID_zwrotu = ''
				AND
				p.Ilosc <> 0
				
			GROUP BY n.kontrguid	
		;		
		ALTER TABLE `t_zwroty_kontrahenci_p`
			ADD UNIQUE kontrguid(kontrguid)
		;	

		UPDATE `t_zwroty_kontrahenci` k
		SET
			k.Invalid = 1
		;	
		UPDATE `t_zwroty_kontrahenci` k, `t_zwroty_kontrahenci_p` t
		SET
			k.Invalid = 0
		WHERE
			k.KontrGUID = t.KontrGUID
		;	

		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_kontrahenci_p`;

		/*		
		
		UPDATE `t_zwroty_kontrahenci` k
		SET
			k.Invalid = 
			NOT EXISTS(
			SELECT 
				n.GUID 
			FROM `opng` n 
			INNER JOIN `oppo` p ON p.IdnOper = n.GUID
			WHERE 
				k.KontrGUID = n.kontrguid
				AND
				n.datawplywu BETWEEN k.DateZW_Od AND k.DateZW_Do
				-- n.datawplywu >= k.DateZW_Od 
				-- AND
				-- n.datawplywu <= k.DateZW_Do
				AND
				n.rodzaj = 70 
				AND
				p.rodzaj_typ = 1 
				AND
				p.Ilosc <> 0
				
			)
		;
		*/
		
		DELETE FROM `t_zwroty_kontrahenci` WHERE Invalid;

		SELECT 
			k.*,
			kk.Symbol AS kontrsymbol, 
			kk.Nazwa AS kontrnazwa,
			ak.Nazwa AS Akwizytor 
		FROM `t_zwroty_kontrahenci` k
		INNER JOIN `kontrah` kk ON kk.GUID = k.kontrguid
		LEFT JOIN `kontrah` ak ON ak.GUID = kk.AkwizytorGUID
		ORDER BY kontrsymbol
		;
		
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_kontrahenci`;
	END IF;	-- _MODE = 'wiz.init.select.kontrah'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'prepare.zwroty.one'
		Tworzy `t_zwroty_oppo_3k` dla 1 zwrotu = _Key
		_Key	GUID zwrotu
		CALL ht_Kompensata3Tool('prepare.zwroty.one', _Key, '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------
	IF _MODE = 'prepare.zwroty.one' THEN
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_oppo_3k`;
		CREATE TEMPORARY TABLE `t_zwroty_oppo_3k`
			SELECT 
				_VarINT(0) AS Invalid,
				_VarDouble(ABS(p.Ilosc)) AS Ilosc_Brakuje,
				_VarDouble(p.Ilosc) AS Ilosc_Korekta,
				_VarDouble(0) AS Ilosc_Wzieto,
				_VarINT(0) AS Bylem,
				p.* 
			FROM opng n
			INNER JOIN oppo p on (n.guid=p.idnoper) 
			INNER JOIN TowrNg T on (T.guid = p.idnTowr) 
			WHERE
				n.GUID = _Key
		;	
		ALTER TABLE `t_zwroty_oppo_3k`
			ADD INDEX GUID(GUID),
			ADD INDEX IdnTowr(IdnTowr)
		;
		SELECT
			n.KontrGUID INTO _KontrGUID
		FROM opng n
		WHERE
			n.GUID = _Key
		;	
		CALL ht_Kompensata3Tool('prepare.zwroty.after', _KontrGUID, '', '', '', '');		
	END IF;	-- _MODE = 'prepare.zwroty.one'
*/

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'prepare.zwroty.kontrahenta'
		Tworzy `t_zwroty_oppo_3k` z wielu zwrotów dla 1 kontrahenta = _Key
		_Key	GUID Kontrahenta
		CALL ht_Kompensata3Tool('prepare.zwroty.kontrahenta', _KontrGUID, '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'prepare.zwroty.kontrahenta' THEN
		SET _KontrGUID = _Key;
		CALL ht_Kompensata3Tool('zwroty.prepare.kontrah.calc', _KontrGUID, '0', '0', @session.komps3.DateZW_Od, @session.komps3.DateZW_Do);				
		
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_oppo_3k`;
		CREATE TEMPORARY TABLE `t_zwroty_oppo_3k`
			SELECT 
				_VarINT(0) AS Invalid,
				_VarDouble(ABS(p.Ilosc)) AS Ilosc_Brakuje,
				_VarDouble(p.Ilosc) AS Ilosc_Korekta,
				_VarDouble(0.0) AS Ilosc_Wzieto,
				_VarINT(0) AS Bylem,
				p.* 
			FROM opng n
			INNER JOIN oppo p on (n.guid=p.idnoper) 
			INNER JOIN TowrNg T on (T.guid = p.idnTowr) 
			WHERE
				p.datawystaw BETWEEN DATE(@session.komps3.calc.Date_Od) AND DATE(@session.komps3.calc.Date_Do) AND 
				-- p.datawystaw >= @session.komps3.DateZW_Od AND 
				-- p.datawystaw <= @session.komps3.DateZW_Do AND 
				p.rodzaj = 70 AND 
				n.rodzaj = 70 AND 
				-- n.rodzaj2 = 1 AND 
				p.rodzaj_typ = 1 AND
				p.IdnWZ = '' AND 
				ABS(p.Ilosc) > 0
				-- ??? MOGE??? AND IFNULL(p.GUIDKorpo, '') = ''
				AND n.KontrGUID = _KontrGUID
				AND T.opakowanie_zwrotne = 0
				--	TUTAJ dodałem BAAARDZO WAŻNE
				AND (@session.komps3.calc.Limit_zwrotu > 0) 
				AND (@session.komps3.calc.SumNetto > 0)
		;	
		ALTER TABLE `t_zwroty_oppo_3k`
			ADD INDEX GUID(GUID),
			ADD INDEX IdnTowr(IdnTowr)
		;
		CALL ht_Kompensata3Tool('prepare.zwroty.after', _KontrGUID, '', '', '', '');		
		
		CALL ht_Kompensata3Tool('wiz.calc.set', '', '', '', '', '');
		
	END IF;	-- _MODE = 'prepare.zwroty.kontrahenta'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'prepare.zwroty.after'
		Tworzy `t_orig_oppo_3k` 
		_Key	GUID Kontrahenta
		CALL ht_Kompensata3Tool('prepare.zwroty.after', _KontrGUID, '', '', '', '');		
		SET @session.komps3.DateZW_Od = _Param_A;
		SET @session.komps3.DateZW_Do = _Param_B;
		SET @session.komps3.DateOR_Od = _Param_C;
		SET @session.komps3.DateOR_Do = _Param_D;
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'prepare.zwroty.after' THEN
		SET _KontrGUID = _Key;
		SET @session.komps3.calc.KontrGUID = _Key;
		SET @session.komps3.only.noncorrected.orig = TRUE;
		
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_oppo_grp_by_idntowr`;
		CREATE TEMPORARY TABLE `t_zwroty_oppo_grp_by_idntowr`
			SELECT 
				z.GUID,
				z.Invalid,
				SUM(z.Ilosc_Brakuje) AS Ilosc_Brakuje,
				_VarDouble(SUM(z.Ilosc_Korekta)) AS Ilosc_Korekta,
				_VarDouble(SUM(z.Ilosc_Wzieto)) AS Ilosc_Wzieto,
				_VarINT(0) AS Bylem,
				z.IdnTowr 
			FROM `t_zwroty_oppo_3k` z
			GROUP BY z.IdnTowr	
		;	
		ALTER TABLE `t_zwroty_oppo_grp_by_idntowr`
			ADD INDEX IdnTowr(IdnTowr)
		;
	
		
		DROP TEMPORARY TABLE IF EXISTS `t_orig_oppo_3k`;
		CREATE TEMPORARY TABLE `t_orig_oppo_3k`
			SELECT 
				_VarINT(0) AS Invalid,
				_VarDouble(0.0) AS Ilosc_Koryg,
				_VarDouble(p.Ilosc) AS Ilosc_Dostepna,
				_VarDouble(0.0) AS Ilosc_Wzieto,
				_VarINT(0) AS Included,
				p.rodzaj As p_rodzaj, n.rodzaj AS n_rodzaj,
				-- p.IdnOperFin,p.GUIDKorpo,
				n.KontrGUID, 
				n.GUID AS OrigNgGUID, 
				n.GUID AS NewNgGUID, 
				_VarInt(IFNULL(i.KreujOsobnyDocDlaKazdegoOryg, 0)) AS KreujOsobnyDocDlaKazdegoOryg, 
				p.* 
			FROM `opng` n
			INNER JOIN `oppo` p ON (n.guid = p.idnoperFin) 
			LEFT JOIN `ht_kontrah_info` i ON (i.KontrGuid = n.KontrGuid) 
			WHERE
				p.datawystaw >= @session.komps3.DateOR_Od AND 
				p.datawystaw <= @session.komps3.DateOR_Do AND 
				n.rodzaj IN (1,2) AND 
				-- AND p.rodzaj IN (1,2,10)
				n.KontrGUID = _KontrGUID	-- '{D1D2E07B-B68A-4DF3-8092-29F9FECAFCD2}'
				AND
					EXISTS (SELECT GUID FROM `t_zwroty_oppo_grp_by_idntowr` z WHERE z.IdnTowr = p.IdnTowr LIMIT 1)
			;
		ALTER TABLE `t_orig_oppo_3k`
			ADD INDEX GUID(GUID),
			ADD INDEX IdnTowr(IdnTowr),
			ADD INDEX Ilosc_Dostepna(Ilosc_Dostepna)
		;
		UPDATE `t_orig_oppo_3k` t
		SET
			NewNgGUID = '', -- czyscimy na razie
			t.Ilosc_Koryg = IFNULL((SELECT SUM(Ilosc) FROM oppo o WHERE o.GuidKorpo = t.GUID), 0)
		;
		UPDATE `t_orig_oppo_3k` t
		SET
			t.Ilosc_Dostepna = t.Ilosc_Koryg + t.Ilosc
		;
		UPDATE `t_orig_oppo_3k` t
		SET
			Invalid = 1,
			t.Ilosc_Dostepna = 0
		WHERE	
			t.Ilosc_Dostepna < 0
		;
		IF @session.komps3.only.noncorrected.orig THEN
			UPDATE `t_orig_oppo_3k` t
			SET
				Invalid = 1,
				t.Ilosc_Dostepna = 0
			WHERE	
				t.Ilosc_Koryg < 0
				AND
				Invalid = 0
			;
		END IF;
		DROP TEMPORARY TABLE IF EXISTS `t_work_oppo`;
		CREATE TEMPORARY TABLE `t_work_oppo` 
			SELECT * FROM `oppo` WHERE FALSE
		;
		DROP TEMPORARY TABLE IF EXISTS `t_new_korekta_oppo`;
		CREATE TEMPORARY TABLE `t_new_korekta_oppo` 
			SELECT * FROM `oppo` WHERE FALSE
		;
	END IF;	-- _MODE = 'prepare.zwroty.after'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.calc.set'
		Nalicza 1 korekte ze zwrotów dla kontrahenta
		CALL ht_Kompensata3Tool('wiz.calc.set', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.calc.set' THEN
	
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			-- juz niepotrzebny DECLARE _LOCAL_GUID VARCHAR(38);
			DECLARE _orig_GUID VARCHAR(38);
			DECLARE _Ilosc_Dostepna DOUBLE;
			DECLARE _Ilosc_Biore DOUBLE;
			DECLARE _IDN_Found DOUBLE;
			DECLARE _LOCAL_Ilosc_Brakuje DOUBLE;
			DECLARE _LOCAL_IdnTowr VARCHAR(38);
			DECLARE c_proc CURSOR FOR
				SELECT 
					-- z.GUID,
					z.IdnTowr
				FROM `t_zwroty_oppo_grp_by_idntowr` z
			;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loopCS: 
			LOOP
				FETCH c_proc 
				-- juz niepotrzebny INTO _LOCAL_GUID, _LOCAL_IdnTowr
				INTO _LOCAL_IdnTowr
				;
				IF done THEN
					LEAVE loopCS;
				END IF;
				
				UPDATE `t_zwroty_oppo_grp_by_idntowr` z
				SET
					Bylem = 1
				WHERE
					z.IdnTowr = _LOCAL_IdnTowr
				;
				
				SET _Ilosc_Dostepna = 0;
				SET _NextSearch = TRUE;
				
				WHILE _NextSearch DO
				-- 	Pobierz aktualne brakuje z `t_zwroty_oppo_grp_by_idntowr`	
					SELECT
						z.Ilosc_Brakuje
					INTO	
						_LOCAL_Ilosc_Brakuje
					FROM `t_zwroty_oppo_grp_by_idntowr` z
					WHERE
						z.IdnTowr = _LOCAL_IdnTowr
					;	
					SET 
						@_LOCAL_Ilosc_Brakuje = _LOCAL_Ilosc_Brakuje,
						@_LOCAL_IdnTowr = _LOCAL_IdnTowr,
						_NextSearch = (_LOCAL_Ilosc_Brakuje > 0)
					;
					IF _NextSearch THEN
					-- 	Szukaj w `t_orig_oppo_3k`	
						SET 
							_IDN_Found = 0,
							@session.komps3.IDN_Min = 0,
							@session.komps3.IDN_Max = 0
						;
						SELECT
							MIN(p.IDN), --	bo NIE OdNowychDoStarych
							MAX(p.IDN) 	--	bo OdNowychDoStarych
						FROM `t_orig_oppo_3k` p
						WHERE
							p.Invalid = 0 
							AND	
							p.IdnTowr = _LOCAL_IdnTowr
							AND 
							p.Ilosc_Dostepna > 0
						INTO	
							@session.komps3.IDN_Min,
							@session.komps3.IDN_Max
						;
						
						IF @session.komps3.use.OdNowychDoStarych THEN
						--	Np. Konkol
							SET _IDN_Found = IFNULL(@session.komps3.IDN_Max, 0);
						ELSE	
							SET _IDN_Found = IFNULL(@session.komps3.IDN_Min, 0);
						END IF;	
						
						/*
						IF @session.komps3.use.OdNowychDoStarych THEN
						--	Np. Konkol
							SELECT
								MAX(p.IDN) 	--	bo OdNowychDoStarych
							INTO	
								_IDN_Found
							FROM `t_orig_oppo_3k` p
							WHERE
								p.Invalid = 0 
								AND	
								p.IdnTowr = _LOCAL_IdnTowr
								AND 
								p.Ilosc_Dostepna > 0
							;
						ELSE	
							SELECT
								MIN(p.IDN) --	bo NIE OdNowychDoStarych
							INTO	
								_IDN_Found
							FROM `t_orig_oppo_3k` p
							WHERE
								p.Invalid = 0 
								AND	
								p.IdnTowr = _LOCAL_IdnTowr
								AND 
								p.Ilosc_Dostepna > 0
							;
						END IF;	
						SET _IDN_Found = IFNULL(_IDN_Found, 0);
						*/
						SET _NextSearch = (_IDN_Found > 0);
						IF _NextSearch THEN
							SET _Ilosc_Dostepna = 0;
							SELECT
								p.GUID, p.Ilosc_Dostepna
							INTO	
								_orig_GUID, _Ilosc_Dostepna
							FROM `t_orig_oppo_3k` p
							WHERE
								p.Idn = _IDN_Found
							;	
							
							SET _orig_GUID = IFNULL(_orig_GUID, '');
							SET @_orig_GUID = _orig_GUID;
							SET _Ilosc_Dostepna = IFNULL(_Ilosc_Dostepna, 0);
							SET @_Ilosc_Dostepna = _Ilosc_Dostepna;
							
							SET _NextSearch = (_Ilosc_Dostepna > 0);
						END IF;	
					END IF;	
					-- SET _NextSearch = FALSE;  -- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
					IF _NextSearch THEN
						IF _LOCAL_Ilosc_Brakuje < _Ilosc_Dostepna THEN
							SET _Ilosc_Biore = _LOCAL_Ilosc_Brakuje;
						ELSE
							SET _Ilosc_Biore = _Ilosc_Dostepna;
						END IF;
						
					--	Zmniejsz Ilosc_Brakuje w `t_zwroty_oppo_grp_by_idntowr`	
						UPDATE `t_zwroty_oppo_grp_by_idntowr` z
						SET
							z.Ilosc_Brakuje = z.Ilosc_Brakuje - _Ilosc_Biore,			-- Brakuje zmiejszam o _Ilosc_Biore
							z.Ilosc_Wzieto = z.Ilosc_Wzieto + _Ilosc_Biore				-- Wzieto zwiekszam o _Ilosc_Biore

						WHERE
							z.IdnTowr = _LOCAL_IdnTowr
						;
					--	Zmniejsz Ilosc_Dostepna w `t_orig_oppo_3k`	
						UPDATE `t_orig_oppo_3k` p
						SET
							p.Ilosc_Wzieto = p.Ilosc_Wzieto + _Ilosc_Biore,				-- Wzieto zwiekszam o _Ilosc_Biore
							p.Ilosc_Dostepna = p.Ilosc_Dostepna - _Ilosc_Biore			-- Dostepne zmiejszam o _Ilosc_Biore
						WHERE
							p.GUID = _orig_GUID
						;
						
					END IF;
					
				END WHILE;
				
			END LOOP;
			CLOSE c_proc;
		END;
		
	--	Teraz dobierz braci towarowych
	--	Lista dok. (FV <- WZ,ZW) po IdnOperFin
		DROP TEMPORARY TABLE IF EXISTS `t_orig_ng_idntowr`;
		CREATE TEMPORARY TABLE `t_orig_ng_idntowr`
			SELECT 
				p.IdnOperFin AS GUID, p.IdnTowr AS IdnTowr
			FROM `t_orig_oppo_3k` p
			WHERE
				p.Invalid = 0 AND
				p.Ilosc_Wzieto > 0 
			GROUP BY p.IdnOperFin, p.IdnTowr		
		;
		UPDATE `t_orig_oppo_3k` p, `t_orig_ng_idntowr` n
		SET
			p.Included = 1
		WHERE
			p.IdnOperFin = n.GUID AND		-- czyli NIE TYLKO te które były wzięte ale mają ten sam IdnOperFin (mogły nie by wzięte - np. Na WZ jest kilka pozycji tego samego towaru ale tylko wzięto z jednego - system weźmie wszystkie)
			p.IdnTowr = n.IdnTowr
		;
		DROP TEMPORARY TABLE IF EXISTS `t_orig_ng_idntowr`;

		
		
		/*
		Musze rozstrzygnac czy kreuje korektę dla każdego dokumentu oryfginalu czy jedna zbiorcż. Decydowac bedzie pole NewNgGUID
		Ale zanim ustale GUID przyszlej korekty (lub wielu korekt) to musze wiedziec czy klient wymaga wielu cvy zadowoli sie zbiorczą. 
		O tym decyduje pole KreujOsobnyDocDlaKazdegoOryg w `t_orig_oppo_3k`
		Plan:
		1. Pobierz (do tabeli `t_orig_ng_work`) wszystkie krotki o różnych GUIDach oryginalow (GROUP BY p.OrigNgGUID) z tabeli `t_orig_ng_work`
		2. Ustaw t.NewNgGUID jako ten sam guid z zmiennej @session.komps3.NewNgGUID lub dla każdego wiersza nowy (gdy t.KreujOsobnyDocDlaKazdegoOryg = 1)
		3. Ustaw zwrotnie w `t_orig_oppo_3k` pola NewNgGUID z `t_orig_ng_work`
		*/
		DROP TEMPORARY TABLE IF EXISTS `t_orig_ng_work`;
		CREATE TEMPORARY TABLE `t_orig_ng_work`
			SELECT 
				p.OrigNgGUID, p.NewNgGUID, p.KreujOsobnyDocDlaKazdegoOryg
			FROM `t_orig_oppo_3k` p
			WHERE
				p.Invalid = 0 
				AND
				p.Included = 1
			GROUP BY p.OrigNgGUID		
		;
		-- Ustaw t.NewNgGUID jako ten sam guid z zmiennej @session.komps3.NewNgGUID lub dla każdego wiersza nowy (gdy t.KreujOsobnyDocDlaKazdegoOryg = 1)
		SET @session.komps3.NewNgGUID = NewGUID();
		
		UPDATE `t_orig_ng_work` t
		SET
			t.NewNgGUID = IF(t.KreujOsobnyDocDlaKazdegoOryg, NewGUID(), @session.komps3.NewNgGUID)
		;
		-- Ustaw zwrotnie w `t_orig_oppo_3k` pola NewNgGUID z `t_orig_ng_work`
		UPDATE `t_orig_oppo_3k` t, `t_orig_ng_work` tw
		SET
			t.NewNgGUID = tw.NewNgGUID
		WHERE
			t.OrigNgGUID = tw.OrigNgGUID
			AND
			t.Included = 1
		;
		DROP TEMPORARY TABLE IF EXISTS `t_orig_ng_work`;
		
	END IF;	-- _MODE = 'wiz.calc.set'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.select.NewNgGUID.doc.'
		CALL ht_Kompensata3Tool('wiz.select.NewNgGUID.doc', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.select.NewNgGUID.doc' THEN
		SELECT
			DISTINCT t.NewNgGUID
		FROM `t_orig_oppo_3k` t
		WHERE
			t.Included = 1
		;
	END IF;	-- _MODE = 'wiz.select.NewNgGUID.doc'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'ht_kontrah_info.insert.korekta'
	JUż po wystawieniu korekty jej GUID niech wedruje do `ht_kontrah_info`
		_Key		KontrGUID
		_Param_A	GUID dokumentu
		CALL ht_Kompensata3Tool('ht_kontrah_info.insert.korekta', 'KontrGUID', 'GUIDKorekty', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'ht_kontrah_info.insert.korekta' THEN
	--	Tylko dla @session.komps3.use.graniczna.korekta np. Konkol
		IF @session.komps3.use.graniczna.korekta THEN
			SET
				_Key = IFNULL(_Key, ''),
				_Param_A = IFNULL(_Param_A, '')
			;
			UPDATE `ht_kontrah_info` i
			SET
				i.LastKorDoZWGUID = _Param_A
			WHERE
				i.KontrGUID = _Key
			;	
		END IF;	
	END IF;	-- _MODE = 'ht_kontrah_info.insert.korekta'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.build.oppo_corr'
		_Key	GUID dokumentu
		
		Wstawiamy rekordy do `t_init_oppo_corr`
		CALL ht_Kompensata3Tool('wiz.build.oppo_corr', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.build.oppo_corr' THEN
	
		CALL ht_oppo_corr('init.drop temp', _Key, '', '', '');		--	Teraz GUID dok. korekty będzie w @session.oppo_corr.NewGUID (Nawet gdy _Key był pusty to teraz @session.oppo_corr.NewGUID będzie już określony)
		SET _Param_A = IFNULL(_Param_A, '');	--	GUID oryginalu
		SET _Param_A = IF(_Param_A = '', NewGUID(), _Param_A);
		INSERT IGNORE INTO `t_init_oppo_corr`
			(GUID, GUID_orig, IdnOper_corr,
			b_idnTowr, b_towrsymb, b_towrnazwa, b_rodzaj_przeliczenia, b_ilosc, b_cenanet, b_cenabrt, b_rabat, b_idnvat, b_wartnet, b_wartnetWal, b_wartvat, b_wartbrt, b_pkwiu, b_uwagi, b_uwagi2, b_Jednostka, b_kurs
			)
			SELECT
				NewGUID(), GUID, @session.oppo_corr.NewGUID,
				idnTowr, towrsymb, towrnazwa, rodzaj_przeliczenia, ilosc, cenanet, cenabrt, rabat, idnvat, wartnet, wartnetWal, wartvat, wartbrt, pkwiu, uwagi, uwagi2, Jednostka, kurs
			FROM `t_orig_oppo_3k` o
			WHERE
				o.Included = 1
				AND
				o.NewNgGUID = _Key
		;
		
		CALL ht_oppo_corr('init.search last corr', '', '', '', '');		
		
		UPDATE `t_init_oppo_corr` t, `t_orig_oppo_3k` o
		SET
			j_ilosc = o.Ilosc_Dostepna
		WHERE
			t.GUID_orig = o.GUID
		;
		
		CALL ht_oppo_corr('init.temp t_init_oppo_corr to oppo_corr_e', @session.oppo_corr.NewGUID, '', '', '');		
		-- teraz apply ...
		CALL ht_oppo_corr('edit.after apply oppo_corr_e', @session.oppo_corr.NewGUID, '', '', '');		
		-- kończ
		IF @session.komps3.use.tymczasowa.1 THEN
			-- 'X' znacza _Param_A = 'X' - _Param_A	gdy <> '' to nie używaj oper DELTA!!!
			--	1 w _Param_B oznacza Tymczasowa = 1
			CALL ht_oppo_corr('edit.oppo_corr to oppo', @session.oppo_corr.NewGUID, 'X', '1', '');	
		ELSE
			-- 'X' znacza _Param_A = 'X' - _Param_A	gdy <> '' to nie używaj oper DELTA!!!
			CALL ht_oppo_corr('edit.oppo_corr to oppo', @session.oppo_corr.NewGUID, 'X', '', '');		
		END IF;
		
	/* UWAGA!!!!!!!!!!!!!
	Tutaj rezygnujemy z update na oppo (zwroty) aby ilosc zerować !
	Zamiast tego nie ruszamu tu niczego a w korekcie (ściślej w pozycjach) wstawimy tymczasowa = 1
	Wszystko zależy od zmiennej @session.komps3.use.tymczasowa.1		

	*/
	--	TERAZ Zwroty zerujemy z wyjątkiem tych co im coś pozostało
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_oppo_nozero`;
		CREATE TEMPORARY TABLE `t_zwroty_oppo_nozero`
			SELECT 
				o.GUID, o.IdnTowr, g.Ilosc_Brakuje
			FROM `t_zwroty_oppo_grp_by_idntowr` g
			INNER JOIN `t_zwroty_oppo_3k` o ON o.IdnTowr = g.IdnTowr
			WHERE
				g.Ilosc_Brakuje > 0
			GROUP BY g.IdnTowr
		;	
		IF NOT @session.komps3.use.tymczasowa.1 THEN
			UPDATE `t_zwroty_oppo_3k` o, `oppo` p
			SET
				p.Ilosc = 0,
				p.WartNet = 0,
				p.WartNetWal = 0
			WHERE
				p.GUID = o.GUID
			;
			UPDATE `t_zwroty_oppo_nozero` o, `oppo` p
			SET
				p.Ilosc = o.Ilosc_Brakuje,
				p.WartNet = p.CenaNet * o.Ilosc_Brakuje,
				p.WartNetWal = _Rnd2(p.CenaNet * o.Ilosc_Brakuje*p.Kurs)
			WHERE
				p.GUID = o.GUID
			;
		ELSE	
			UPDATE `t_zwroty_oppo_3k` o, `oppo` p
			SET
				p.Info4 = CONCAT(
				'Ilosc= 0, WartNet = 0, WartNetWal = 0'
				),
				p.wymiar_x = ABS(p.Ilosc)
			WHERE
				p.GUID = o.GUID
			;
			UPDATE `t_zwroty_oppo_nozero` o, `oppo` p
			SET
				p.Info4 = CONCAT(
				'Ilosc= ', o.Ilosc_Brakuje, ', WartNet = ', p.CenaNet * o.Ilosc_Brakuje, ', WartNetWal = ',_Rnd2(p.CenaNet * o.Ilosc_Brakuje*p.Kurs)
				),
				p.wymiar_x = ABS(p.Ilosc) - ABS(o.Ilosc_Brakuje)
			WHERE
				p.GUID = o.GUID
			;
		END IF;
		
		DROP TEMPORARY TABLE IF EXISTS `t_zwroty_oppo_nozero`;
		
		IF NOT @session.komps3.use.tymczasowa.1 THEN
			CALL ht_Kompensata3Tool('wiz.update.oppo_mag', '', '', '', '', '');		
		END IF;
	END IF;	-- _MODE = 'wiz.build.oppo_corr'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.update.oppo_mag'
		Uaktualnia `oppo_mag` na podstawie `t_zwroty_oppo_3k`
		CALL ht_Kompensata3Tool('wiz.update.oppo_mag', '', '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.update.oppo_mag' THEN
		BEGIN
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE _LOCAL_GUID VARCHAR(38);
			DECLARE c_proc CURSOR FOR
				SELECT 
					o.IdnOper AS GUID
				FROM `t_zwroty_oppo_3k` o
				GROUP BY o.IdnOper
			;	
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loopOM: 
			LOOP
				FETCH c_proc INTO _LOCAL_GUID
				;
				IF done THEN
					LEAVE loopOM;
				END IF;
				
			--	Odswieza rekord opng w `oppo_mag` 
				CALL ht_oppo_mag_tool('UPDATE_OPNG_REC', _LOCAL_GUID, '', '', '');		
				
			END LOOP;
			CLOSE c_proc;
		END;
		
	END IF;	-- _MODE = 'wiz.update.oppo_mag'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'wiz.build.doc'
		_Key	GUID dokumentu
		
		CALL ht_Kompensata3Tool('wiz.build.doc', _Key, '', '', '', '');		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'wiz.build.doc' THEN
	--  bylo kiedys tutaj	CALL ht_Kompensata3Tool('wiz.calc.set', '', '', '', '', '');		
	--  a teraz jest w CALL ht_Kompensata3Tool('prepare.zwroty.kontrahenta', _KontrGUID, '', '', '', '');		
		CALL ht_Kompensata3Tool('wiz.build.oppo_corr', _Key, '', '', '', '');		
		CALL ht_Kompensata3Tool('wiz.update.oppo_mag', _Key, '', '', '', '');		
		
	--	W polu `korektatytulem` daj okres zwrotów uwzględnionych w korekcie
		UPDATE `opng` n
		SET
			n.korektatytulem = CONCAT('Za zwroty z okresu: od ', DATE(@session.komps3.calc.Date_Od), ' do ', DATE(@session.komps3.calc.Date_Do))
		WHERE
			n.GUID = _Key
		;
	--	NA KONIEC WSTAW 700 rdo Rodzaj2
		UPDATE `opng` n, `oppo` p
		SET
			-- n.rodzaj2 = 700
			p.rodzaj_typ = 700
		WHERE
			n.rodzaj = 70 AND
			n.datawplywu BETWEEN DATE(@session.komps3.DateZW_Od) AND DATE(@session.komps3.DateZW_Do) AND 
			n.KontrGUID = @session.komps3.calc.KontrGUID AND
			p.IdnOper = n.GUID
		;
	
	END IF;	-- _MODE = 'wiz.build.doc'
	


	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'zwroty.prepare.kontrah'
		_Key		GUID Kontrahenta NIE OBLIGO ! ! ! MOŻE BYĆ PYSTY!!!!!
		_Param_A, _Param_B	Daty inicjalne
		CALL ht_Kompensata3Tool('zwroty.prepare.kontrah', '', _Param_A, _Param_B, '', '');		
		SET @session.komps3.calc.Date_Od = _Param_A;
		SET @session.komps3.calc.Date_Do = _Param_B;
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'zwroty.prepare.kontrah' THEN
		LEAVE SPROC;
		
		SET _Key = IFNULL(_Key, '');
		SET @session.komps3.calc.Date_Od = DATE(_Param_A);
		SET @session.komps3.calc.Date_Do = DATE(_Param_B);
			BEGIN
				DECLARE _GUID VARCHAR(38);
				DECLARE _SumNetto DOUBLE;
				DECLARE _Limit_zwrotu DOUBLE;
				DECLARE done BOOL DEFAULT FALSE;
				DECLARE c_proc CURSOR FOR
					SELECT
						n.kontrguid,
						-- n.kontrsymbol,n.kontrnazwa,
						SUM(n.netto) AS SumNetto,
						k.limit_zwrotu
					FROM `opng` n 
					INNER JOIN `kontrah` k ON k.GUID = n.KontrGUID
					LEFT JOIN `ht_kontrah_info` i ON (i.KontrGuid = n.KontrGUID) 
					WHERE 
						n.datawplywu BETWEEN DATE(@session.komps3.calc.Date_Od) AND DATE(@session.komps3.calc.Date_Do) 
						AND 
						n.Rodzaj IN (0, 1, 2) 
						AND
						IF(_Key != '', n.KontrGUID = _Key, TRUE)
					GROUP BY kontrguid
					;
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
				OPEN c_proc;
				loopPK: LOOP
					FETCH c_proc INTO
						_KontrGUID, 
						_SumNetto,
						_Limit_zwrotu
					;
					IF done THEN
						LEAVE loopPK;
					ELSE
						--	kolejny rekord
						CALL ht_Kompensata3Tool('zwroty.prepare.kontrah.calc', _KontrGUID, _SumNetto, _Limit_zwrotu, @session.komps3.calc.Date_Od, @session.komps3.calc.Date_Do);		
					END IF;
				END LOOP;
				CLOSE c_proc;
			END;
	END IF;	-- _MODE = 'zwroty.prepare.kontrah'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'zwroty.prepare.kontrah.calc'
		
		_Key		GUID Kontrahenta
		_Param_A	wartosc Nettp
		_Param_B	_Limit_zwrotu kontrahenta w proc. czyli gdy 5 oznacza 5% 
		_Param_C	Data OD
		_Param_D	Data DO
		CALL ht_Kompensata3Tool('zwroty.prepare.kontrah.calc', _KontrGUID, _Param_A, _Param_B, _Param_C, _Param_D);		
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF _MODE = 'zwroty.prepare.kontrah.calc' THEN
		SET 
			@session.komps3.calc.rodzaj_typ = 0,
			@session.komps3.calc.SumNetto = _VarDouble(_Param_A),
			@session.komps3.calc.Limit_zwrotu = _VarDouble(_Param_B),
			@session.komps3.calc.Date_Od = _Param_C,
			@session.komps3.calc.Date_Do = _Param_D,
			_KontrGUID = _Key
		;
		SELECT
			DATE(IFNULL(n.DataWystaw, @session.komps3.calc.Date_Od)) AS Date_Od,
			k.limit_zwrotu,
			i.LastKorDoZWGUID,
			_VarInt(IFNULL(n.Idn, 0)) AS FirstIdn
		INTO 	
			@session.komps3.calc.Date_Od,
			@session.komps3.calc.Limit_zwrotu,
			@session.komps3.calc.LastKorDoZWGUID,
			@session.komps3.calc.LastKorDoZWIdn
		FROM `ht_kontrah_info` i
		INNER JOIN `kontrah` k ON k.GUID = i.KontrGUID
		LEFT JOIN `opng` n ON (n.GUID = i.LastKorDoZWGUID) AND @session.komps3.use.graniczna.korekta
		WHERE
			i.KontrGuid = _KontrGUID
		;
		SELECT
			SUM(n.netto) AS SumNetto
		INTO	
			@session.komps3.calc.SumNetto
		FROM `opng` n 
		WHERE 
			n.datawplywu BETWEEN DATE(@session.komps3.calc.Date_Od) AND DATE(@session.komps3.calc.Date_Do)
			AND 
			n.Idn > @session.komps3.calc.LastKorDoZWIdn
			AND
			n.Rodzaj IN (0, 1, 2) 
			AND
			n.KontrGUID = _KontrGUID
			AND
			n.GUID != @session.komps3.calc.LastKorDoZWGUID
		GROUP BY kontrguid
		;
		
		IF @session.komps3.calc.Limit_zwrotu <= 0 THEN
			LEAVE SPROC;
		END IF;
		
		SET @session.komps3.calc.PulaNetto = @session.komps3.calc.SumNetto * @session.komps3.calc.Limit_zwrotu/100;		--	PULA przekroczonia
		SET @session.komps3.calc.Netto = 0;		-- LICZNIK
			BEGIN
				DECLARE _GUID VARCHAR(38);
				DECLARE _Netto DOUBLE;
				DECLARE _Rodzaj2 INTEGER;
				DECLARE _rodzaj_typ INTEGER;
				DECLARE done BOOL DEFAULT FALSE;
				DECLARE c_proc CURSOR FOR
					SELECT 
						-- n.GUID,
						p.GUID,
						-- _VarDouble(ABS(n.Netto)) AS Netto,
						_VarDouble(ABS(p.WartNet)) AS Netto,
						-- n.rodzaj2
						p.rodzaj_typ
					FROM `opng` n
					INNER JOIN `oppo` p ON p.IdnOper = n.GUID
					WHERE
						n.datawplywu BETWEEN DATE(@session.komps3.calc.Date_Od) AND DATE(@session.komps3.calc.Date_Do) AND 
						n.rodzaj = 70 AND 
									-- n.rodzaj2 <> 700 AND 
						p.rodzaj_typ <> 700 AND 
						n.KontrGUID = _KontrGUID
					ORDER BY n.datawplywu, Netto DESC
				;
				
				DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
				OPEN c_proc;
				loopPC: LOOP
					FETCH c_proc INTO
						_GUID, 
						_Netto,
						-- _Rodzaj2
						_rodzaj_typ
					;
					IF done THEN
						LEAVE loopPC;
					ELSE
					--	kolejny rekord
						SET @session.komps3.calc.Netto = @session.komps3.calc.Netto + _Netto;
						
					--	Czy przekroczyłeś pule???
						IF @session.komps3.calc.Netto <= @session.komps3.calc.PulaNetto THEN
						--	Jeszce się mieszczę w puli
							SET @session.komps3.calc.rodzaj_typ = 1;
							SET @session.komps3.calc.SetRodzaj2 = 1;
						ELSE
						--	Przekroczyłem
							SET @session.komps3.calc.rodzaj_typ = 70;
							SET @session.komps3.calc.SetRodzaj2 = 70;
						END IF;
						
					--	Zapisz do rodzaj_typ
						IF _rodzaj_typ != @session.komps3.calc.rodzaj_typ THEN
							UPDATE `oppo` 
							SET 
								rodzaj_typ = @session.komps3.calc.rodzaj_typ 
							WHERE 
								GUID = _GUID
							;
						END IF;
						
						/*
					--	Zapisz do Rodzaj2
						IF _Rodzaj2 != @session.komps3.calc.SetRodzaj2 THEN
							UPDATE `opng` 
							SET 
								Rodzaj2 = @session.komps3.calc.SetRodzaj2 
							WHERE 
								GUID = _GUID
							;
						END IF;	
						*/
						-- DODAJ (po sprawdzeniu - pierwszy zwrot przekraczający jeszcze akceptujemy - jest to pewne przyliżenie, ale akceptowalne)
					-- !!	SET @session.komps3.calc.Netto = @session.komps3.calc.Netto + _Netto;
					END IF;
				END LOOP;
				CLOSE c_proc;
			END;
	END IF;	-- _MODE = 'zwroty.prepare.kontrah.calc'
	
	
END$$

DELIMITER ;

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

SET @_Param_A = '2011-02-01';
SET @_Param_B = '2011-04-01';
SET @_Param_C = '2011-01-01';
SET @_Param_D = '2011-04-01';

CALL ht_Kompensata3Tool('wiz.init.date', '', @_Param_A, @_Param_B, @_Param_C, @_Param_D);		
SELECT 	@session.komps3.DateZW_Od, @session.komps3.DateZW_Do, @session.komps3.DateOR_Od, @session.komps3.DateOR_Do;

SELECT n.KontrGUID, n.* FROM opng n ORDER BY idn DESC LIMIT 10;
SET @_KontrGUID = '{0A0BD460-B8C5-463A-A430-886076B331E1}';
CALL ht_Kompensata3Tool('wiz.init.select.kontrah', '', '', '', '', '');		

CALL ht_Kompensata3Tool('prepare.zwroty.kontrahenta', @_KontrGUID, '', '', '', '');		
SELECT * FROM `t_zwroty_oppo_3k`;
SELECT * FROM `t_zwroty_oppo_grp_by_idntowr`;
SELECT p.IdnOperFin AS Doc, p.IdnTowr, p.* FROM `t_orig_oppo_3k` p;
CALL ht_Kompensata3Tool('wiz.build.doc', _Key, '', '', '', '');		
	CALL ht_Kompensata3Tool('wiz.calc.set', '', '', '', '', '');		
	CALL ht_Kompensata3Tool('wiz.build.oppo_corr', _Key, '', '', '', '');		

SELECT * FROM `t_orig_ng_idntowr`;



CALL _Session_Refresh_Variables('', '', '');
SET @_Param_A = '2011-02-01';
SET @_Param_B = '2011-04-01';
SET @_Param_C = '2011-01-01';
SET @_Param_D = '2011-04-01';

CALL ht_Kompensata3Tool('wiz.init.date', '', @_Param_A, @_Param_B, @_Param_C, @_Param_D);		
SELECT 	@session.komps3.DateZW_Od, @session.komps3.DateZW_Do, @session.komps3.DateOR_Od, @session.komps3.DateOR_Do;

SELECT n.KontrGUID, n.* FROM opng n ORDER BY idn DESC LIMIT 10;
SET @_KontrGUID = '{0A0BD460-B8C5-463A-A430-886076B331E1}';
SET @_KontrGUID = 'PHA1';	--	u Jarzebinskich
SET @_KontrGUID = 'DAWID';	--	u Jarzebinskich
CALL ht_Kompensata3Tool('wiz.init.select.kontrah', '', '', '', '', '');		

CALL ht_Kompensata3Tool('prepare.zwroty.kontrahenta', @_KontrGUID, '', '', '', '');		
SELECT o.IdnTowr, o.* FROM `t_zwroty_oppo_3k` o ORDER BY IdnTowr;
SELECT o.IdnTowr, o.* FROM `t_zwroty_oppo_3k` o WHERE IdnTowr = '62C' ORDER BY IdnTowr;
SELECT * FROM `t_zwroty_oppo_grp_by_idntowr`;

SELECT p.IdnOperFin AS Doc, p.IdnTowr, p.* FROM `t_orig_oppo_3k` p;

CALL ht_Kompensata3Tool('wiz.build.doc', _Key, '', '', '', '');		
	CALL ht_Kompensata3Tool('wiz.calc.set', '', '', '', '', '');		
SET @_Key = 'VXYZ';
	CALL ht_Kompensata3Tool('wiz.build.oppo_corr', @_Key, '', '', '', '');		
	SELECT * FROM `t_orig_ng_idntowr`;

SELECT * FROM `t_orig_ng_idntowr`;


CALL ht_Kompensata3Tool('zwroty.prepare.kontrah', '', @_Param_A, @_Param_B, '', '');		



		SELECT DISTINCT 
			n.kontrguid, n.kontrsymbol, n.kontrnazwa, _VarInt(1) AS Checked, n.zmagazynu, n.domagazynu, m.Opis AS Magazyn
		FROM `opng` n 
		LEFT JOIN `Magazyny` m ON m.IdnMagazynu = n.domagazynu
		WHERE
			n.datawplywu >= @session.komps3.DateZW_Od AND 
			n.datawplywu <= @session.komps3.DateZW_Do AND 
			n.rodzaj=70 AND 
			n.rodzaj2 <> 70 
		GROUP BY n.kontrguid
		ORDER BY kontrsymbol
		;
		SELECT DISTINCT 
			n.kontrguid, n.kontrsymbol, n.kontrnazwa, _VarInt(1) AS Checked, n.zmagazynu, n.domagazynu, m.Opis AS Magazyn
		FROM `opng` n 
		LEFT JOIN `Magazyny` m ON m.IdnMagazynu = n.domagazynu
		WHERE
			IFNULL(n.kontrguid, '') != '' AND
			n.datawplywu >= @session.komps3.DateZW_Od AND 
			n.datawplywu <= @session.komps3.DateZW_Do AND 
			n.rodzaj=70 AND 
			n.rodzaj2 <> 70 
		GROUP BY n.kontrguid
		ORDER BY kontrsymbol
		;

SELECT 
	p.IdnTowr, p. TowrSymb, SUM(p.Ilosc) AS Ilosc
FROM `test_zwroty_oppo` p
INNER JOIN `opng` n ON n.GUID = p.IdnOper
WHERE
	n.KontrGUID = @_KontrGUID AND
	n.datawplywu >= @session.komps3.DateZW_Od AND 
	n.datawplywu <= @session.komps3.DateZW_Do AND 
	n.rodzaj=70 AND 
	p.Rodzaj = 70
GROUP BY p.IdnTowr	
;	
SELECT 
	p.IdnTowr, p. TowrSymb, SUM(p.Ilosc) AS Ilosc
FROM `oppo` p
INNER JOIN `opng` n ON n.GUID = p.IdnOper
WHERE
	n.KontrGUID = @_KontrGUID AND
	n.datawplywu >= @session.komps3.DateZW_Od AND 
	n.datawplywu <= @session.komps3.DateZW_Do AND 
	n.rodzaj=3
GROUP BY p.IdnTowr	
;	

DROP TABLE `test_zwroty_oppo`;
CREATE TABLE `test_zwroty_oppo`
	SELECT p.* 
	FROM `oppo` p
	INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		n.datawplywu >= '2011-01-01' AND 
		n.rodzaj=70
;
INSERT INTO `test_zwroty_oppo`
	SELECT p.* 
	FROM `oppo` p
	INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		n.datawplywu >= '2011-01-01' AND 
		n.rodzaj=3
;
DROP TEMPORARY TABLE `test_zwroty_oppo_check`;
CREATE TEMPORARY TABLE `test_zwroty_oppo_check` (
			`kontrGUID` VARCHAR(38) DEFAULT NULL,
			`idnTowr` VARCHAR(38) NOT NULL COMMENT 'Towar',
			`towrsymb` VARCHAR(38) NOT NULL DEFAULT '',
			`ilosc_zwr` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
			`ilosc_kor` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
			`ilosc_zwr_po` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
			UNIQUE KEY `kontrGUID_idnTowr` (`kontrGUID`, `idnTowr`)
		)
		;

SET @_Param_A = '2011-02-01';
SET @_Param_B = '2011-04-01';
SET @_KontrGUID = 'PHA1';	

TRUNCATE `test_zwroty_oppo_check`;

INSERT INTO `test_zwroty_oppo_check`
	(KontrGUID, IdnTowr, TowrSymb, ilosc_zwr)
	SELECT 
		n.KontrGUID, p.IdnTowr, p. TowrSymb, @kk:=SUM(p.Ilosc) AS Ilosc
	FROM `test_zwroty_oppo` p
	INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		n.KontrGUID = @_KontrGUID AND
		n.datawplywu >= @_Param_A AND 
		n.datawplywu <= @_Param_B AND 
		n.rodzaj=70 AND 
		p.Rodzaj = 70
	GROUP BY p.IdnTowr	
;	
INSERT INTO `test_zwroty_oppo_check` 
	(KontrGUID, IdnTowr, TowrSymb, ilosc_kor)
	SELECT 
		n.KontrGUID, p.IdnTowr, p.TowrSymb, @ss := SUM(p.Ilosc)
	FROM `test_zwroty_oppo` p
	INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		n.KontrGUID = @_KontrGUID AND
		n.datawplywu >= @_Param_A AND 
		n.datawplywu <= @_Param_B AND 
		n.rodzaj=3
	GROUP BY p.IdnTowr	
	ON DUPLICATE KEY
	UPDATE
		Ilosc_kor = @ss
	
;	
INSERT INTO `test_zwroty_oppo_check` 
	(KontrGUID, IdnTowr, TowrSymb, ilosc_zwr_po)
	SELECT 
		n.KontrGUID, p.IdnTowr, p.TowrSymb, @zz := SUM(p.Ilosc)
	FROM `test_zwroty_oppo` p
	INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		n.KontrGUID = @_KontrGUID AND
		n.datawplywu >= @_Param_A AND 
		n.datawplywu <= @_Param_B AND 
		n.rodzaj=70 AND 
		p.Rodzaj = 70
	GROUP BY p.IdnTowr	
	ON DUPLICATE KEY
	UPDATE
		ilosc_zwr_po = @zz
	
;	
UPDATE `test_zwroty_oppo_check`  t
SET
	t.ilosc_kor = (
	SELECT 
		SUM(p.Ilosc) 
	FROM `oppo` p INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		p.IdnTowr = t.IdnTowr AND
		n.KontrGUID = t.KontrGUID AND
		n.datawplywu >= @_Param_A AND 
		n.datawplywu <= @_Param_B AND 
		n.rodzaj=3
	)
;	
UPDATE `test_zwroty_oppo_check`  t
SET
	t.ilosc_zwr_po = (
	SELECT 
		SUM(p.Ilosc) 
	FROM `oppo` p INNER JOIN `opng` n ON n.GUID = p.IdnOper
	WHERE
		p.IdnTowr = t.IdnTowr AND
		n.KontrGUID = t.KontrGUID AND
		n.datawplywu >= @_Param_A AND 
		n.datawplywu <= @_Param_B AND 
		n.rodzaj=70 AND 
		p.Rodzaj = 70
	)
;	
SELECT t.*, t.ilosc_zwr - ilosc_zwr_po AS Dif_zw, ilosc_kor AS kor FROM `test_zwroty_oppo_check` t;



SET 
	@_Param_A = '2012-01-01',
	@_Param_B = '2012-02-09'
	@_Param_C = '2012-01-01',
	@_Param_D = '2012-02-09'
;

CALL ht_Kompensata3Tool('wiz.init.date', '', @_Param_A, @_Param_B, @_Param_C, @_Param_D);		

SET @_KontrGUID = 'IWONA';
CALL ht_Kompensata3Tool('prepare.zwroty.kontrahenta', @_KontrGUID, '', '', '', '');		


SELECT 
@session.komps3.calc.KontrGUID,
@session.komps3.calc.Date_Od, @session.komps3.calc.Date_Do
;

		UPDATE `opng` n
		SET
			n.rodzaj2 = 700
		WHERE
			n.rodzaj = 70 AND
			n.datawplywu BETWEEN @session.komps3.calc.Date_Od AND @session.komps3.calc.Date_Do AND 
			n.KontrGUID = @session.komps3.calc.KontrGUID

			;

CALL ht_Kompensata3Tool('zwroty.prepare.kontrah', '', '2011-03-01', '2011-04-01', '', '');		

SELECT 
p.* 
FROM `opng` n
INNER JOIN `oppo` p ON p.IdnOper = n.GUID
WHERE
	n.datawplywu BETWEEN @session.komps3.calc.Date_Od AND @session.komps3.calc.Date_Do AND 
	n.rodzaj = 70 AND 
	-- n.rodzaj2 <> 700 AND 
	p.rodzaj_typ != 70 
ORDER BY n.datawplywu
;
SELECT 
p.rodzaj_typ, COUNT(*), _VarDouble(ABS(SUM(p.WartNet))) AS Netto
FROM `opng` n
INNER JOIN `oppo` p ON p.IdnOper = n.GUID
WHERE
	n.datawplywu BETWEEN @session.komps3.calc.Date_Od AND @session.komps3.calc.Date_Do AND 
	n.rodzaj = 70
GROUP BY p.rodzaj_typ	
ORDER BY n.datawplywu
;



CALL _Session_Refresh_Variables('', '', '');
SET 
	@_Param_A = '2012-01-01',
	@_Param_B = '2012-02-09',
	@_Param_C = '2012-01-01',
	@_Param_D = '2012-02-09'
;
CALL ht_Kompensata3Tool('wiz.init.date', '', @_Param_A, @_Param_B, @_Param_C, @_Param_D);		

SET @_KontrGUID = '{4CAFB02F-53E4-4FEC-A44C-FAC6B573C7F4}';
CALL ht_Kompensata3Tool('wiz.init.select.kontrah', '', '', '', '', '');		
SELECT
			@session.komps3.only.noncorrected.orig,
			@session.komps3.use.tymczasowa.1,
			@session.komps3.use.graniczna.korekta,
			@session.komps3.use.OdNowychDoStarych,
			@session.komps3.calc.Date_Od,
			@session.komps3.calc.Limit_zwrotu,
			@session.komps3.calc.LastKorDoZWGUID
;			

CALL ht_Kompensata3Tool('prepare.zwroty.kontrahenta', @_KontrGUID, '', '', '', '');		

SELECT *
FROM `t_zwroty_oppo_3k` t
WHERE
TRUE
AND t.IdnTowr = 'C01'

;

SELECT n.GUID, n.* FROM opng n WHERE rodzaj = 3 AND kontrguid = '12' AND datasprzed > '2012-01-30';

SELECT 
p.IdnTowr, p.ILosc, p.*
FROM
oppo p
WHERE
TRUE
AND p.idnoper = '{02793010-50B1-11E1-8DD0-002481F9239A}'
AND p.IdnTowr = 'C01'

;
			
*/
