 DELIMITER $$

/*
	U W A G A   dotyczy vat-u `opng_vat`
*/


DROP PROCEDURE IF EXISTS `erp_vat_tools`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `erp_vat_tools`(
	IN _MODE VARCHAR(150),
	IN _Key VARCHAR(255),	
	-- IN _Param_A VARCHAR(255),
	IN _Param_A TEXT,
	IN _Param_B VARCHAR(255),
	IN _Param_C VARCHAR(255),
	IN _Param_D VARCHAR(255)
)
SPROC: BEGIN 
	
	DECLARE _SQLText TEXT DEFAULT '';
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'system.conv'
	Pobiera domy�lna warto�� stawki vat - dla nowej karty towarowej
		Parametry
			_Key		Oznacza etap konwersji VAT
			_Param_A
	CALL erp_vat_tools('system.conv', '1', '', '', '', '');	--	 etap 1
	CALL erp_vat_tools('system.conv', '2', '', '', '', '');	--	 etap 2
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'system.conv') THEN
		CALL _Globals_Get('ht.vat.conv.etap', @session.ht.vat.conv.etap, 0);
		IF (_Key = '1') AND (@session.ht.vat.conv.etap = 0) THEN
			CALL _Globals_Set('ht.vat.conv.etap', _Key);
		END IF;
		IF (_Key = '2') AND (@session.ht.vat.conv.etap = 1) THEN
		
		--	1. wykonac update na TowrNg
			UPDATE TowrNg SET VatPrevYear = 2010;
			UPDATE TowrNg SET VatPrevId = vat where vat<14;
			UPDATE TowrNg SET vat = 15 WHERE vat = 0;
			UPDATE TowrNg SET vat = 16 WHERE vat = 1;

		--	2. wykonac zmiany w erp_vat
			UPDATE erp_vat SET not_used = 1 WHERE ID IN (0,1) AND country_code = 'PL';
			UPDATE erp_vat SET PrevYear = 2010 WHERE ID IN (0,1) AND country_code =  'PL';
			UPDATE erp_vat SET not_used = 0 WHERE ID IN (15,16) AND country_code =  'PL';

			UPDATE `erp_vat` SET `IsDefault`='0' WHERE `ID`='0' AND `country_code`='PL';
			UPDATE `erp_vat` SET `IsDefault`='1' WHERE `ID`='15' AND `country_code`='PL';
		--	3. wykonac wpis w IReg
			UPDATE `IReg` SET `ValueB`='1' WHERE `IdKey`='WidocznaStawkaVat23';
			UPDATE `IReg` SET `ValueB`='1' WHERE `IdKey`='WidocznaStawkaVat8';
		
			CALL _Globals_Set('ht.vat.conv.etap', _Key);
		END IF;
		
		CALL _Globals_Get('ht.vat.conv.etap', @session.ht.vat.conv.etap, 0);
		SELECT @session.ht.vat.conv.etap;
		
	END IF;	--	IF _MODE = 'system.conv'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Select.default.IdVat'
	Pobiera domy�lna warto�� stawki vat - dla nowej karty towarowej
		Parametry
			_Key		NIE ISTOTNY
			_Param_A
	CALL erp_vat_tools('Select.default.IdVat', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Select.default.IdVat') THEN
		SELECT
			ID INTO @vat.IdVat
		FROM v_erp_vat	
		WHERE
			IsDefault
		LIMIT 1	
		;	
		SELECT IFNULL(@vat.IdVat, 0) AS IdVat;
		
	END IF;	--	IF _MODE = 'Select.default.IdVat'


	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'erp_vat_dev.%'
		Parametry
			_Key		IdnTowr
			_Param_A	Data
				
		CALL erp_vat_tools('erp_vat_dev.Fill', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'erp_vat_dev.%') THEN
		SET _Param_A = IFNULL(_Param_A, '');
		IF (_MODE = 'erp_vat_dev.Recreate') THEN
			-- DROP TABLE if EXISTS `erp_vat_dev`;
			CREATE TABLE if NOT EXISTS `erp_vat_dev` (
			  `IdVat` int(11) NOT NULL DEFAULT '0' COMMENT 'Stawka VAT',
			  `DevSymbol` varchar(38) NOT NULL DEFAULT '' COMMENT 'Symbol urzadzenia np. POSNET, ELZAB,...',
			  `Symbol` VARCHAR(20) DEFAULT '' COMMENT 'Znacznik np. A B C ...',
			  `ValueDescr` varchar(20) DEFAULT '' COMMENT 'Wartosc (informacyjnie)',
			  PRIMARY KEY (`IdVat`,`DevSymbol`)
			) ENGINE=MyISAM DEFAULT CHARSET=cp1250
			;
			
		END IF;
		
		IF (_MODE = 'erp_vat_dev.Fill.Dev') THEN
			INSERT IGNORE INTO `erp_vat_dev`
				(IdVat, DevSymbol, Symbol, ValueDescr)
				SELECT
					v.ID, _Param_A, '', v.ShortName
				FROM `v_erp_vat` v
				WHERE NOT not_used
			;		
		END IF;
		
		IF (_MODE = 'erp_vat_dev.Fill') THEN
			TRUNCATE `erp_vat_dev`;
			CALL erp_vat_tools('erp_vat_dev.Fill.Dev', '', 'POSNET', '', '', '');
			CALL erp_vat_tools('erp_vat_dev.Fill.Dev', '', 'ELZAB', '', '', '');
			/*
			   Cstawka22  = 0;       // A      #1
			   Cstawka7   = 1;       // B      #2
			   Cstawka0   = 2;       // C      #4
			   CstawkaZw  = 3;       // Z      #5
			   CStawka3   = 4;       // D      #3
			   CStawkaNp  = 5;       // Z      #5
			   CStawkaX   = 6;       // E      #5
			   CStawkaY   = 7;       // F      #5
			   COstStawka = 7;

			   Cstawka5  = 8;       // -> CStawka3  D #3 dla konkola to jest 5 !!!! 3 nie jest uzywana chyba...
			   Cstawka23  = 15;     // -> Cstawka22 A #1
			   Cstawka8  = 16;      // -> Cstawka7  B #2
			*/
			UPDATE `erp_vat_dev` SET Symbol = 'A' WHERE DevSymbol = 'POSNET' AND IdVat = 15;	--	23% (A)
			UPDATE `erp_vat_dev` SET Symbol = 'B' WHERE DevSymbol = 'POSNET' AND IdVat = 16;	--	8% (B)
			UPDATE `erp_vat_dev` SET Symbol = 'X' WHERE DevSymbol = 'POSNET' AND IdVat = 17;	--	4% (X)
			UPDATE `erp_vat_dev` SET Symbol = 'Z' WHERE DevSymbol = 'POSNET' AND IdVat = 3;	--	Zwolniony (Z)
			UPDATE `erp_vat_dev` SET Symbol = 'D' WHERE DevSymbol = 'POSNET' AND IdVat = 8;	--	5% (D)
			UPDATE `erp_vat_dev` SET Symbol = 'Z' WHERE DevSymbol = 'POSNET' AND IdVat = 5;	--	Niepodlega (Z)
			UPDATE `erp_vat_dev` SET Symbol = 'E' WHERE DevSymbol = 'POSNET' AND IdVat = NULL;	--	??? (E)
			UPDATE `erp_vat_dev` SET Symbol = 'F' WHERE DevSymbol = 'POSNET' AND IdVat = NULL;	--	??? (F)
			UPDATE `erp_vat_dev` SET Symbol = 'C' WHERE DevSymbol = 'POSNET' AND IdVat = 2;	--	0% (C)
			
			UPDATE `erp_vat_dev` SET Symbol = '1' WHERE DevSymbol = 'ELZAB' AND IdVat = 15;	--	23% (1)
			UPDATE `erp_vat_dev` SET Symbol = '2' WHERE DevSymbol = 'ELZAB' AND IdVat = 16;	--	8% (2)
			UPDATE `erp_vat_dev` SET Symbol = '8' WHERE DevSymbol = 'ELZAB' AND IdVat = 17;	--	4% (8)
			UPDATE `erp_vat_dev` SET Symbol = '5' WHERE DevSymbol = 'ELZAB' AND IdVat = 3;	--	Zwolniony (5)
			UPDATE `erp_vat_dev` SET Symbol = '3' WHERE DevSymbol = 'ELZAB' AND IdVat = 8;	--	5% (3)
			UPDATE `erp_vat_dev` SET Symbol = '5' WHERE DevSymbol = 'ELZAB' AND IdVat = 5;	--	Niepodlega (5)
			UPDATE `erp_vat_dev` SET Symbol = '5' WHERE DevSymbol = 'ELZAB' AND IdVat = NULL;	--	??? (5)
			UPDATE `erp_vat_dev` SET Symbol = '5' WHERE DevSymbol = 'ELZAB' AND IdVat = NULL;	--	??? (5)
			UPDATE `erp_vat_dev` SET Symbol = '5' WHERE DevSymbol = 'ELZAB' AND IdVat = 2;	--	0% (5)
			
		END IF;
		
		IF (_MODE = 'erp_vat_dev.Select') THEN
			SELECT * FROM `erp_vat_dev` WHERE DevSymbol = _Param_A ORDER BY IF(Symbol = '', 'ZZZ', Symbol);
		END IF;
		
		IF (_MODE = 'erp_vat_dev.doc.Select') THEN
		--	CALL erp_vat_tools('erp_vat_dev.doc.Select', '', 'POSNET', '', '', '');
			SELECT 
				o.GUID, 
				o.IdnTowr,
				o.TowrNazwa, o.TowrSymb, 
				o.Ilosc, 
				o.IdnVat, 
				dv.Symbol AS VatChar,
				o.CenaBrt, o.WartBrt, -- ??? o.ACenaBrt, 
				n.NrDokFin
			FROM `oppo` o
			INNER JOIN `opng` n ON n.GUID = o.IdnOperFin AND n.paragon_printed = 0
			LEFT JOIN `erp_vat_dev` dv ON o.IdnVat = dv.IdVat AND dv.DevSymbol = _Param_A 
			WHERE
				o.IdnOperFin = _Key
			ORDER BY o.LP	
			;
			
		END IF;
		
		IF (_MODE = 'erp_vat_dev.doc.Select.opng_vat') THEN
		--	CALL erp_vat_tools('erp_vat_dev.doc.Select.opng_vat', _Key, 'POSNET', '', '', '');
			SELECT
				ov.*,
				dv.Symbol AS VatChar
			FROM `opng_vat` ov
			LEFT JOIN `erp_vat_dev` dv ON ov.IdVat = dv.IdVat AND dv.DevSymbol = _Param_A 
			WHERE
				ov.IdnOper = _Key
			;			
		END IF;
		
		IF (_MODE = 'erp_vat_dev.doc.Printed') THEN
		--	CALL erp_vat_tools('erp_vat_dev.doc.Printed', @G, '0', '', '', '');
		--	CALL erp_vat_tools('erp_vat_dev.doc.Printed', @G, '1', '', '', '');
		--	CALL erp_vat_tools('erp_vat_dev.doc.Printed', @G, '2', '', '', '');
			IF _Param_A = 1 THEN
			--	dla 1 
				UPDATE `opng` SET paragon_printed = _Param_A, datasprzed = CURRENT_DATE() WHERE GUID = _Key;
				UPDATE `oppo` SET datasprzed = CURRENT_DATE() WHERE idnoper = _Key;   
			ELSE
			--	dla 0, 2  (2 - w trakcie druku fisk.)
				UPDATE `opng` SET paragon_printed = _Param_A WHERE GUID = _Key;
			END IF;
		END IF;
	END IF;	--	IF _MODE = 'erp_vat_dev.%'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Put.IdVat.for IdnTowr and date.to.@session.vat.EditedIdVat'
	NIE U�YWANA !!!!!!!!!!!!!!!!!!
		Parametry
			_Param_A	
				
		CALL erp_vat_tools('erp_vat_dev.Fill', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'Put.IdVat.for IdnTowr and date.to.@session.vat.EditedIdVat') THEN
		SET _Param_A = IFNULL(_Param_A, '');
	END IF;	--	IF _MODE = 'erp_vat_dev.%'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Select.Check.IdVat.for.IdnTowr.and.Date'
		Parametry
			_Key		IdnTowr
			_Param_A	Data
			_Param_B	Current Id Vat (gdy -1 lub '' to odrazu sprawdza towar
				
	CALL erp_vat_tools('Select.Check.IdVat.for.IdnTowr.and.Date', 'A01', '2010-12-30', '1', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Select.Check.IdVat.for.IdnTowr.and.Date') THEN
		SET _Param_A = IFNULL(_Param_A, '');
		SET _Param_A = IF(_Param_A = '', CURRENT_DATE, _Param_A);
		SET @session.vat.edityear = YEAR(_Param_A);
		SET _Param_B = -1;  -- wymuszam zeby zawsze pobieral z karty towarowej
		SET _Param_B = IFNULL(_Param_B, '');
		SET _Param_B = IF(_Param_B = '', -1, _Param_B);
		SET @session.vat.IdVatIsValid = (_Param_B != -1);
		IF @session.vat.IdVatIsValid THEN
		--	Sprawdz not_used
			SET @session.vat.IdVatIsValid = FALSE;
			SELECT 
				(NOT not_used) OR (@session.vat.edityear <= PrevYear)
			INTO 
				@session.vat.IdVatIsValid 
			FROM v_erp_vat 
			WHERE 
				ID = _Param_B
			;
		END IF;
		
		IF NOT @session.vat.IdVatIsValid THEN
		--	Sprawdz w TorNg
			SELECT
				IF((@session.vat.edityear <= t.VatPrevYear), t.VatPrevId, t.Vat) AS EditIdVat
			INTO
				@session.vat.ReturnIdVat
			FROM `TowrNg` t
			WHERE
				t.GUID = _Key
			;
		ELSE
		--	Zwr�� _Param_B
			SET @session.vat.ReturnIdVat = _Param_B;
		END IF;
		
		SELECT @session.vat.ReturnIdVat AS `IdVat`;
		
	END IF;	--	IF _MODE = 'Select.Check.IdVat.for.IdnTowr.and.Date'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Select.erp_vat'
	'Select.erp_vat.all'
	Pobiera stawki vat - wszystkie/tylko u�ywane (do combo)
		Parametry
			_Key		NIE ISTOTNY
			_Param_A	
				ALL		wszystkie
				U		u�ywane (wystepuja w combo ustawien vat)
				
	CALL erp_vat_tools('Select.erp_vat', '', '', '', '', '');
	CALL erp_vat_tools('Select.erp_vat.all', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'Select.erp_vat%') THEN
		IF (_MODE = 'Select.erp_vat.all') THEN
			SET _Param_A = 'ALL';
		ELSE
			SET _Param_A = 'U';
		END IF;		
		SELECT
			*
		FROM v_erp_vat	
		WHERE
			
			(_Param_A IN ('', 'ALL')) OR
			(NOT not_used)
		ORDER BY IdOrder	
		;	
		
	END IF;	--	IF _MODE = 'Select.erp_vat'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	't_edit_ng_vat.Create'
	Inicjuje `t_edit_ng_vat`
	CALL erp_vat_tools('t_edit_ng_vat.Create', '', '', '', '', '');
	
			CALL erp_vat_tools('t_edit_ng_vat.Create', '', '', '', '', '');
			CALL erp_vat_tools('t_edit_ng_vat.Init', 'bla', '', '', '', '');
			CALL erp_vat_tools('t_edit_ng_vat.Init', @_Key, '', '', '', '');
			SELECT * FROM `t_edit_ng_vat`;
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 't_edit_ng_vat.Create') THEN
		DROP TEMPORARY TABLE IF EXISTS `t_edit_ng_vat`;
		CREATE TEMPORARY TABLE `t_edit_ng_vat` (
		  `IdnOper` VARCHAR(38) NOT NULL DEFAULT '',
		  `IdVat` INT(11) NOT NULL DEFAULT '0',
		  `Vat_Name` VARCHAR(38) NOT NULL DEFAULT '',
		  `Vat_ShortName` VARCHAR(38) NOT NULL DEFAULT '',
		  `Vat_Value` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Netto` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Vat` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Brutto` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `NettoWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `VatWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `BruttoWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `OperCount` INT(11) NOT NULL DEFAULT '0',
		  `Exist` INT(11) NOT NULL DEFAULT '0',
		  `IsChanged` INT(11) NOT NULL DEFAULT '0',
		  `IdOrder` VARCHAR(38) NOT NULL DEFAULT '',
		  PRIMARY KEY (`IdnOper`,`IdVat`),
		  KEY `IdnOper` (`IdnOper`)
		) ENGINE=MYISAM DEFAULT CHARSET=cp1250
		;

	END IF;	--	IF _MODE = 't_edit_ng_vat.Create'


/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	't_edit_ng_vat.Init'
	Inicjuje `t_edit_ng_vat`
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A
	CALL erp_vat_tools('t_edit_ng_vat.Init', @_Key, '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 't_edit_ng_vat.Init') THEN
		TRUNCATE `t_edit_ng_vat`;
		/*
		INSERT IGNORE INTO `t_edit_ng_vat`
				(
				SELECT
					ov.IdnOper,
					ov.IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					ov.Netto, ov.Vat, ov.Brutto,
					ov.NettoWal, ov.VatWal, ov.BruttoWal,
					ov.OperCount,
					_VarInt(1) AS Exist,
					_VarInt(0) AS IsChanged,
					v.IdOrder
				FROM `opng_vat` ov
				INNER JOIN `v_erp_vat` v ON v.ID = ov.IdVat
				WHERE
					ov.IdnOper = _Key
				)		
				UNION DISTINCT 
				(
				SELECT
					_Key AS IdnOper,
					v.ID AS IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					_VarDouble(0) AS Netto, _VarDouble(0) AS Vat, _VarDouble(0) AS Brutto,
					_VarDouble(0) AS NettoWal, _VarDouble(0) AS VatWal, _VarDouble(0) AS BruttoWal,
					_VarInt(0) AS OperCount,
					_VarInt(0) AS Exist,
					_VarInt(0) AS IsChanged,
					v.IdOrder
				FROM `v_erp_vat` v
				WHERE
					NOT v.not_used
				)	
				
				ORDER BY IdOrder, IdVat
		;
		*/
		INSERT IGNORE INTO `t_edit_ng_vat`
				SELECT
					ov.IdnOper,
					ov.IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					ov.Netto, ov.Vat, ov.Brutto,
					ov.NettoWal, ov.VatWal, ov.BruttoWal,
					ov.OperCount,
					_VarInt(1) AS Exist,
					_VarInt(0) AS IsChanged,
					v.IdOrder
				FROM `opng_vat` ov
				INNER JOIN `v_erp_vat` v ON v.ID = ov.IdVat
				WHERE
					ov.IdnOper = _Key
		;
		INSERT IGNORE INTO `t_edit_ng_vat`
				SELECT
					_Key AS IdnOper,
					v.ID AS IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					_VarDouble(0) AS Netto, _VarDouble(0) AS Vat, _VarDouble(0) AS Brutto,
					_VarDouble(0) AS NettoWal, _VarDouble(0) AS VatWal, _VarDouble(0) AS BruttoWal,
					_VarInt(0) AS OperCount,
					_VarInt(0) AS Exist,
					_VarInt(0) AS IsChanged,
					v.IdOrder
				FROM `v_erp_vat` v
				WHERE
					NOT v.not_used
		;

	END IF;	--	IF _MODE = 't_edit_ng_vat.Init'
	 
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	't_edit_ng_vat.calc.from._e'
	Inicjuje `t_edit_ng_vat`
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A
	CALL erp_vat_tools('t_edit_ng_vat.calc.from._e', @_Key, '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 't_edit_ng_vat.calc.from._e') THEN
		CALL ng_vat_e_build_from_po_PROTEZA('', 'OP', _Key, '', '');	--	Operacje
		TRUNCATE `t_edit_ng_vat`;
		INSERT IGNORE INTO `t_edit_ng_vat`
				SELECT
					ov.IdnOper,
					ov.IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					ov.Netto, ov.Vat, ov.Brutto,
					ov.NettoWal, ov.VatWal, ov.BruttoWal,
					ov.OperCount,
					_VarInt(1) AS Exist,
					_VarInt(ov.Fixed) AS IsChanged,
					v.IdOrder
				FROM `opng_vat_e` ov 
				INNER JOIN `v_erp_vat` v ON v.ID = ov.IdVat
				WHERE
					ov.IdnOper = _Key
				GROUP BY 
					IdVat
		;
		DELETE FROM `opng_vat_e` WHERE IdnOper = _Key;
		LEAVE SPROC;
-- D A L E J ju� NIE!!!		
		
	
		CALL erp_vat_tools('t_opng_vat.Build', _Key, '0', 'x', '', '');	-- normalny dok. w czasie edycji `oppo_e`
		TRUNCATE `t_edit_ng_vat`;
		
-- TODO uwzglednic sytuacje gdy niezerowe pozycje sie zeruja (np z zaliczkami)
		INSERT IGNORE INTO `t_edit_ng_vat`
				SELECT
					ov.IdnOper,
					ov.IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					SUM(ov.Netto), SUM(ov.Vat), SUM(ov.Brutto),
					SUM(ov.NettoWal), SUM(ov.VatWal), SUM(ov.BruttoWal),
					ov.OperCount,
					_VarInt(1) AS Exist,
					_VarInt(0) AS IsChanged,
					v.IdOrder
				FROM `t_opng_vat` ov 
				INNER JOIN `v_erp_vat` v ON v.ID = ov.IdVat
				WHERE
					ov.IdnOper = _Key
				GROUP BY 
					IdVat
		;
		INSERT IGNORE INTO `t_edit_ng_vat`
				SELECT
					_Key AS IdnOper,
					v.ID AS IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					_VarDouble(0) AS Netto, _VarDouble(0) AS Vat, _VarDouble(0) AS Brutto,
					_VarDouble(0) AS NettoWal, _VarDouble(0) AS VatWal, _VarDouble(0) AS BruttoWal,
					_VarInt(0) AS OperCount,
					_VarInt(0) AS Exist,
					_VarInt(0) AS IsChanged,
					v.IdOrder
				FROM `v_erp_vat` v
				WHERE
					NOT v.not_used
		;
		DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
	END IF;	--	IF _MODE = 't_edit_ng_vat.calc.from._e'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	't_edit_ng_vat.Update.After.edit'
		Nastapi�o Apply `oppo_e` i teraz z tego wynika np. inna zawartosc tabelki vat, etc
		Parametry
			_Key		Dokument
			_Param_B	'' to samo co 0 lub 0 przeliczam lyb 1 nie przeliczam
	
		CALL erp_vat_tools('t_edit_ng_vat.Update.After.edit', @_Key, '', '', '', '');
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 't_edit_ng_vat.Update.After.edit') THEN
		CALL ht_oppo_edit('tool.assign vat props', _Key, '', '', '');		
		/*
		Tutaj musimy od�wie�a� tabelk� VAT
		Mamy trzy grupy
		G1 -	ZP SI
			brak oppo, tabelka jest edytowalna
		G2 -	ST WZ
			jest oppo, tabelka jest klasycznie przeliczana
		G3 -	ZT PZ
			jest oppo, tabelka jest klasycznie przeliczana ALE mo�na j� edytowa�
			gdy tabelka jest wyedytowana to ustawi� trzeba  jaki� znacznik w nag��wku
		*/
		
		SET _Param_B = IFNULL(_Param_B, '0');
		IF _Param_B = '' THEN
			SET _Param_B = '0';
		END IF;
		
		IF _Param_B = '0' THEN
			CALL erp_vat_tools('t_edit_ng_vat.calc.from._e', _Key, '', '', '', '');
		END IF;
		
		LEAVE SPROC;
		
		SET _Param_A = IFNULL(_Param_A, '');
		SET _Param_A = IF(_Param_A = '', 'G2', _Param_A);
		
		
		IF _Param_A = 'G1' THEN
			SET _Param_A = IFNULL(_Param_A, '');
		END IF;
		IF _Param_A = 'G2' THEN
			CALL erp_vat_tools('t_edit_ng_vat.calc.from._e', _Key, '', '', '', '');
		END IF;
		IF _Param_A = 'G3' THEN
			IF _Param_B = '0' THEN
				CALL erp_vat_tools('t_edit_ng_vat.calc.from._e', _Key, '', '', '', '');
			END IF;	
		END IF;
		
	END IF;	--	IF _MODE = 't_edit_ng_vat.Update.After.edit'
	

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	't_edit_ng_vat.to.opng_vat'
		Nastapi�o Apply `oppo_e` i teraz z tego wynika np. inna zawartosc tabelki vat, etc
		Parametry
			_Key		Dokument
			_Param_B	'' to samo co 0 lub 0 przeliczam lyb 1 nie przeliczam
	
		CALL erp_vat_tools('t_edit_ng_vat.to.opng_vat', @_Key, '', '', '', '');
	
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 't_edit_ng_vat.to.opng_vat') THEN
	/*
		  `IdnOper` VARCHAR(38) NOT NULL DEFAULT '',
		  `IdVat` INT(11) NOT NULL DEFAULT '0',
		  `Vat_Name` VARCHAR(38) NOT NULL DEFAULT '',
		  `Vat_ShortName` VARCHAR(38) NOT NULL DEFAULT '',
		  `Vat_Value` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Netto` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Vat` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Brutto` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `Brutto` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `NettoWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `VatWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `BruttoWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0000',
		  `OperCount` INT(11) NOT NULL DEFAULT '0',
		  `Exist` INT(11) NOT NULL DEFAULT '0',
		  `IsChanged` INT(11) NOT NULL DEFAULT '0',
		  `IdOrder` VARCHAR(38) NOT NULL DEFAULT '',
	*/
	--	Usu� z `opng_vat` wszystkie rekordy operacji _Key
		DELETE FROM `opng_vat` 
		WHERE
			IdnOper = _Key
			AND
			NOT Fixed
		;	
		
	--	Wstaw do `opng_vat` z naliczonego wyzej `t_edit_ng_vat` wszystkie rekordy 
	
		INSERT IGNORE INTO `opng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				_Key,			--	IdnOper
				IdVat,		--	IdVat
			--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
				_Rnd2(SUM(Netto)) AS Netto,		
			--	Vat
				_Rnd2(SUM(Vat)) AS Vat,		
			--	Brutto
				_Rnd2(SUM(Brutto)) AS Brutto,		
			--	NettoWal
				_Rnd2(SUM(NettoWal)) AS NettoWal,		
			--	VatWal
				_Rnd2(SUM(VatWal)) AS VatWal,		
			--	BruttoWal
				_Rnd2(SUM(BruttoWal)) AS BruttoWal,
			--	OperCount
				OperCount
			FROM `t_edit_ng_vat` p
			WHERE
				p.Brutto <> 0 
			GROUP BY 
				IdVat
		;
		
	END IF;	--	IF _MODE = 't_edit_ng_vat.to.opng_vat'
	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'opng_vat.select.edit'
	U W A G A !!!!!!!!!!!!!!!
	Dopiero gdy w 2011 przejdziemy w ca�o�ci na edytowaln� tabelk� opng_vat mo�emy u�ywac tej procedury!!!!!!!
	
		Parametry
			_Key		GUID operacji (IdnOper)
				
	CALL erp_vat_tools('opng_vat.select.edit', _Key, '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'opng_vat.select.edit') THEN
		SELECT
			ov.IdnOper,
			ov.IdVat,
			v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
			ov.Netto, ov.Vat, ov.Brutto,
			ov.NettoWal, ov.VatWal, ov.BruttoWal,
			ov.OperCount,
			_VarInt(1) AS Exist,
			_VarInt(0) AS Changed,
			v.IdOrder
		FROM `opng_vat` ov
		INNER JOIN `v_erp_vat` v ON v.ID = ov.IdVat
		WHERE
			ov.IdnOper = _Key
		ORDER BY IdOrder
		;	
		/*
			(
			)
			UNION DISTINCT
			(
			SELECT
				_Key AS IdnOper,
				v.ID AS IdVat,
				v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
				_VarDouble(0) AS Netto, _VarDouble(0) AS Vat, _VarDouble(0) AS Brutto,
				_VarDouble(0) AS NettoWal, _VarDouble(0) AS VatWal, _VarDouble(0) AS BruttoWal,
				_VarInt(0) AS OperCount,
				_VarInt(0) AS Exist,
				_VarInt(0) AS Changed,
				v.IdOrder
			FROM `v_erp_vat` v
			WHERE
				NOT v.not_used
			)	
			ORDER BY IdVat
		;
		
		*/
	END IF;	--	IF _MODE = 'opng_vat.select.edit'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'opng_vat.select.edit.fill.missing'
	U W A G A !!!!!!!!!!!!!!!
	Dopiero gdy w 2011 przejdziemy w ca�o�ci na edytowaln� tabelk� opng_vat mo�emy u�ywac tej procedury!!!!!!!
	W NewRecord pobieramy takiego call-a
	
		Parametry
			_Key		GUID operacji (IdnOper) NIEKONIECZNY
			_Param_A	IDVat dla NewRec tabelki vat KONIECZNA
				
	CALL erp_vat_tools('opng_vat.select.edit.fill.missing', '', '2', '', '', '');		--	2 czyli stawka 0%
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'opng_vat.select.edit.fill.missing') THEN
		SELECT
			_Key AS IdnOper,
			v.ID AS IdVat,
			v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
			_VarDouble(0) AS Netto, _VarDouble(0) AS Vat, _VarDouble(0) AS Brutto,
			_VarDouble(0) AS NettoWal, _VarDouble(0) AS VatWal, _VarDouble(0) AS BruttoWal,
			_VarInt(0) AS OperCount,
			_VarInt(ov.IdnOper IS NOT NULL) AS Exist,
			_VarInt(0) AS Changed,
			v.IdOrder
		FROM `v_erp_vat` v
		LEFT JOIN `opng_vat` ov ON ov.IdVat = v.Id AND ov.IdnOper = _Key
		WHERE
			IF(_Param_A = '', TRUE, v.Id = _Param_A)
		HAVING NOT Exist
		ORDER BY IdOrder
-- 			LIMIT 1	
		;
		
	END IF;	--	IF _MODE = 'opng_vat.select.edit.fill.missing'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	't_opng_vat.Build'
	Nalicza `opng_vat` dla danej (_Key) operacj - usuwa i wstawia aktualne vatowe agregaty z `oppo`
		Parametry
			_Key		GUID operacji (IdnOper)
		JUZ NIE	_Param_A	0 - normalny dokument 1 korekta 
			_Param_B	Gdy <> '' to wylicza nie z `oppo` ale z `oppo_e` 
	CALL erp_vat_tools('t_opng_vat.Build', @_Key, '', '', '', '');		-- normalny dok.
	CALL erp_vat_tools('t_opng_vat.Build', @_Key, '', '', '', '');		-- korekta
	CALL erp_vat_tools('t_opng_vat.Build', @_Key, '', 'x', '', '');	-- normalny dok. w czasie edycji `oppo_e`
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 't_opng_vat.Build') THEN
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;
		
	--	Jesli w naglowku zaakceptowano roznice m-dzy TabVat i naliczeniem TabVat to pole `blokada_przeliczenia_tab_vat` ma wartos 1
		SET @Session.vat.blokada_przeliczenia_tab_vat = NULL;
		CALL ht_oppo_edit('tool.assign vat props', _Key, '', '', '');		
		
		IF IFNULL(@Session.vat.blokada_przeliczenia_tab_vat, 0) != 0 THEN
			LEAVE SPROC;
		END IF;
		
		SET @Session.vat.koszt_nabycia.coeff = IF(IFNULL(@session.HT.IR.PomniejszZTUEOKosztNabycia, FALSE), 1, 0);
	
	-- 	SET _Param_A = IFNULL(_Param_A, 0);
	-- 	SET _Param_A = _VarInt(_Param_A);
		SET _Param_B = IFNULL(_Param_B, '');
		
		
		DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
		
		-- stare IF _Param_A = 0 THEN
		IF @Session.opng.ZwyklyDoc THEN
		--	Z W Y K L Y  D O K U M E N T
			--	Dodaj do `t_opng_vat` rekordy agregowane z pozycji operacji _Key
			IF _Param_B = '' THEN
				DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
				CREATE TEMPORARY TABLE `t_opng_vat` 
				(
					SELECT
						_Key AS IdnOper,			
						p.idnvat AS IdVat,		
					--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
						_Rnd2(SUM(p.WartNet) - SUM(@Session.vat.koszt_nabycia.coeff * p.koszt_nabycia)) AS Netto,		
					--	Vat
						_Rnd2(SUM(p.WartVat)) AS Vat,		
					--	Brutto
						_Rnd2(SUM(p.WartBrt)) AS Brutto,		
					--	NettoWal
						_Rnd2(SUM(p.WartNetWal)) AS NettoWal,		
					--	VatWal
						_Rnd2(SUM(0)) AS VatWal,		
					--	BruttoWal
						_Rnd2(SUM(0)) AS BruttoWal,
					--	OperCount
						_VarInt(Count(*)) AS OperCount
					FROM `oppo` p	
					WHERE
						(
						p.IdnOper = _Key
						OR 
						p.IdnOperFin = _Key
						)
						AND 
						(p.Ilosc <> 0 OR p.WartVat <> 0)
		/*				
						AND p.Ilosc <> 0 
						AND p.WartVat <> 0
		*/				
					GROUP BY 
					-- p.IdnOper,  czemu tak?
						p.idnvat	
				)	
				;
			ELSE
				DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
				CREATE TEMPORARY TABLE `t_opng_vat` 
				(
					SELECT
						_Key AS IdnOper,			
						p.idnvat AS IdVat,		
					--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
						_Rnd2(SUM(p.WartNet) - SUM(@Session.vat.koszt_nabycia.coeff * p.koszt_nabycia)) AS Netto,		
					--	Vat
						_Rnd2(SUM(p.WartVat)) AS Vat,		
					--	Brutto
						_Rnd2(SUM(p.WartBrt)) AS Brutto,		
					--	NettoWal
						_Rnd2(SUM(p.WartNetWal)) AS NettoWal,		
					--	VatWal
						_Rnd2(SUM(0)) AS VatWal,		
					--	BruttoWal
						_Rnd2(SUM(0)) AS BruttoWal,
					--	OperCount
						_VarInt(Count(*)) AS OperCount
					FROM `oppo_e` p	
					WHERE
						(
						p.IdnOper = _Key
						OR 
						p.IdnOperFin = _Key
						)
						AND 
						(p.Ilosc <> 0 OR p.WartVat <> 0)
		/*				
						AND p.Ilosc <> 0 
						AND p.WartVat <> 0
		*/				
					GROUP BY 
					-- p.IdnOper,  czemu tak?
						p.idnvat	
				)	
				;
			END IF;
		ELSE
		--	K O R E K T A
			--	Dodaj do `t_opng_vat` rekordy agregowane z pozycji operacji _Key
			IF _Param_B = '' THEN
				DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
				CREATE TEMPORARY TABLE `t_opng_vat` 
				(
					SELECT
						_Key AS IdnOper,			--	IdnOper
						p.j_idnvat AS IdVat,		--	IdVat
					--	Netto	
						_Rnd2(SUM(p.j_WartNet)) AS Netto,		
					--	Vat
						_Rnd2(0) AS Vat,		
					--	Brutto
						_Rnd2(SUM(p.j_WartBrt)) AS Brutto,	
					--	NettoWal
						_Rnd2(SUM(p.j_WartNetWal)) AS NettoWal,		
					--	VatWal
						_Rnd2(SUM(0)) AS VatWal,		
					--	BruttoWal
						_Rnd2(SUM(0)) AS BruttoWal,
					--	OperCount
						_VarInt(Count(*))  AS OperCount
					FROM `oppo_corr` p
					WHERE
						p.IdnOper_corr = _Key
					GROUP BY 
						p.j_idnvat	
				)
				UNION 
				(
					SELECT
						_Key AS IdnOper,			--	IdnOper
						p.b_idnvat AS IdVat,		--	IdVat
					--	Netto	
						_Rnd2(SUM(-p.b_WartNet)) AS Netto,		
					--	Vat
						_Rnd2(0) AS Vat,	
					--	Brutto
						_Rnd2(SUM(-p.b_WartBrt)) AS Brutto,	
					--	NettoWal
						_Rnd2(SUM(-p.b_WartNetWal)) AS NettoWal,		
					--	VatWal
						_Rnd2(SUM(0)) AS VatWal,		
					--	BruttoWal
						_Rnd2(SUM(0)) AS BruttoWal,
					--	OperCount
						_VarInt(Count(*)) AS OperCount
					FROM `oppo_corr` p
					WHERE
						p.IdnOper_corr = _Key
					GROUP BY 
						p.b_idnvat	
				)
				;
			ELSE
				DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
				CREATE TEMPORARY TABLE `t_opng_vat` 
				(
					SELECT
						_Key AS IdnOper,			--	IdnOper
						p.j_idnvat AS IdVat,		--	IdVat
					--	Netto	
						_Rnd2(SUM(p.j_WartNet)) AS Netto,		
					--	Vat
						_Rnd2(0) AS Vat,		
					--	Brutto
						_Rnd2(SUM(p.j_WartBrt)) AS Brutto,	
					--	NettoWal
						_Rnd2(SUM(p.j_WartNetWal)) AS NettoWal,		
					--	VatWal
						_Rnd2(SUM(0)) AS VatWal,		
					--	BruttoWal
						_Rnd2(SUM(0)) AS BruttoWal,
					--	OperCount
						_VarInt(Count(*))  AS OperCount
					FROM `oppo_corr_e` p
					WHERE
						p.IdnOper_corr = _Key
					GROUP BY 
						p.j_idnvat	
				)
				UNION 
				(
					SELECT
						_Key AS IdnOper,			--	IdnOper
						p.b_idnvat AS IdVat,		--	IdVat
					--	Netto	
						_Rnd2(SUM(-p.b_WartNet)) AS Netto,		
					--	Vat
						_Rnd2(0) AS Vat,	
					--	Brutto
						_Rnd2(SUM(-p.b_WartBrt)) AS Brutto,	
					--	NettoWal
						_Rnd2(SUM(-p.b_WartNetWal)) AS NettoWal,		
					--	VatWal
						_Rnd2(SUM(0)) AS VatWal,		
					--	BruttoWal
						_Rnd2(SUM(0)) AS BruttoWal,
					--	OperCount
						_VarInt(Count(*)) AS OperCount
					FROM `oppo_corr_e` p
					WHERE
						p.IdnOper_corr = _Key
					GROUP BY 
						p.b_idnvat	
				)
				;
			END IF;
				
		END IF;
		
		IF @Session.vat.przeliczenie_B_N = 0 THEN
		--	Netto -> Brutto
			UPDATE `t_opng_vat` v, opng n, v_erp_vat ev
			SET
-- 				v.Brutto = _Rnd2(v.Netto*(1 + ev.`value`)),
-- 				v.Vat = _Rnd2(v.Netto*(1 + ev.`value`)) - v.Netto,
-- 				v.BruttoWal = _Rnd2(v.NettoWal*(1 + ev.`value`)),
-- 				v.VatWal = _Rnd2(v.NettoWal*(1 + ev.`value`)) - v.NettoWal

 				v.Vat = _Rnd2(v.Netto*(ev.`value`)),
 				v.Brutto = _Rnd2(v.Netto+ _Rnd2(v.Netto*(ev.`value`))),
 				v.VatWal = _Rnd2(v.NettoWal*(ev.`value`)),
 				v.BruttoWal = _Rnd2(v.NettoWal + _Rnd2(v.NettoWal*(ev.`value`)))
						
			WHERE
				v.IdnOper = _Key and n.GUID=_Key and ev.id=v.IdVat AND 
				n.przeliczenie_B_N = 0			
			;
		ELSE
		--	Brutto -> Netto
			UPDATE `t_opng_vat` v, opng n, v_erp_vat ev
			SET
				v.Netto = _Rnd2(v.Brutto/(1 + ev.`value`)),
				v.Vat = v.Brutto - _Rnd2(v.Brutto/(1 + ev.`value`)),
				v.NettoWal = _Rnd2(v.BruttoWal/(1 + ev.`value`)),
				v.VatWal = v.BruttoWal - _Rnd2(v.BruttoWal/(1 + ev.`value`))
			WHERE
				v.IdnOper = _Key and n.GUID=_Key and ev.id=v.IdVat AND 
				n.przeliczenie_B_N = 1			
			;
			
		END IF;
		
	END IF;	--	IF _MODE = 't_opng_vat.Build'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Update.oper'
	Nalicza `opng_vat` dla danej (_Key) operacj - usuwa i wstawia aktualne vatowe agregaty z `oppo`
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	Gdy <> '' to wpisuje do naglowka wartosci do TabWart***
			_Param_B	Gdy <> '' to wylicza nie z `oppo` ale z `oppo_e` 
	CALL erp_vat_tools('Update.oper', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Update.oper') THEN
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;
		
	--	Jesli w naglowku zaakceptowano roznice m-dzy TabVat i naliczeniem TabVat to pole `blokada_przeliczenia_tab_vat` ma wartos 1
		SET @Session.vat.blokada_przeliczenia_tab_vat = NULL;
		
		CALL ht_oppo_edit('tool.assign vat props', _Key, '', '', '');		
		
		SET _Param_A = IFNULL(_Param_A, '');
		
		IF IFNULL(@Session.vat.blokada_przeliczenia_tab_vat, 0) != 0 THEN
			IF _Param_A != '' THEN
				CALL erp_vat_tools('Update.oper.opng_vat.to.opng', _Key, '', '', '', '');
			END IF;	
			LEAVE SPROC;
		END IF;
		
		SET @Session.vat.koszt_nabycia.coeff = IF(IFNULL(@session.HT.IR.PomniejszZTUEOKosztNabycia, FALSE), 1, 0);

		CALL erp_vat_tools('t_opng_vat.Build', _Key, '', _Param_B, '', '');  
		
		/*
		SET @Session.opng.ZwyklyDoc = TRUE;
		IF (@Session.opng.Rodzaj IN (3, 4, 7, 8, 65, 85, 86, 90)) AND (@Session.opng.Rodzaj_typ != 0) THEN
			SET @Session.opng.ZwyklyDoc = FALSE;
		END IF;	
		
		-- !!!Gdy nie kortekta
		-- IF NOT ht_oper_Is('korekta', @Session.opng.Rodzaj) THEN
		-- IF NOT (@Session.opng.Rodzaj IN (3, 4, 7, 8, 65, 85, 86, 90)) THEN
		IF @Session.opng.ZwyklyDoc THEN
		
		--	Z W Y K L Y  D O K U M E N T
			--	Dodaj do `t_opng_vat` rekordy agregowane z pozycji operacji _Key
			CALL erp_vat_tools('t_opng_vat.Build', _Key, '0', _Param_B, '', '');  -- normalny dok. gdy _Param_B <> '' to w czasie edycji `oppo_e`

		ELSE
		--	K O R E K T A
			--	Dodaj do `t_opng_vat` rekordy agregowane z pozycji operacji _Key
			CALL erp_vat_tools('t_opng_vat.Build', _Key, '1', _Param_B, '', '');  -- korekta
		END IF;
		*/
		
	--	Usu� z `opng_vat` wszystkie rekordy operacji _Key
		DELETE FROM `opng_vat` 
		WHERE
			IdnOper = _Key
			AND
			NOT Fixed
		;	
		
	--	Wstaw do `opng_vat` z naliczonego wyzej `t_opng_vat` wszystkie rekordy 
	
		INSERT IGNORE INTO `opng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				IdnOper,			--	IdnOper
				IdVat,		--	IdVat
			--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
				_Rnd2(SUM(Netto)) AS Netto,		
			--	Vat
				_Rnd2(SUM(Vat)) AS Vat,		
			--	Brutto
				_Rnd2(SUM(Brutto)) AS Brutto,		
			--	NettoWal
				_Rnd2(SUM(NettoWal)) AS NettoWal,		
			--	VatWal
				_Rnd2(SUM(VatWal)) AS VatWal,		
			--	BruttoWal
				_Rnd2(SUM(BruttoWal)) AS BruttoWal,
			--	OperCount
				OperCount
			FROM `t_opng_vat` p
			
			GROUP BY 
				IdVat
		;
		DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
		
		SET _Param_A = IFNULL(_Param_A, '');
		IF _Param_A != '' THEN
			CALL erp_vat_tools('Update.oper.opng_vat.to.opng', _Key, '', '', '', '');
		END IF;	
		
	END IF;	--	IF _MODE = 'Update.oper'

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Update.oper.opng_vat.to.opng'
	Nalicza `opng_vat` dla danej (_Key) operacj - usuwa i wstawia aktualne vatowe agregaty z `oppo`
		Parametry
			_Key		GUID operacji (IdnOper)
	CALL erp_vat_tools('Update.oper.opng_vat.to.opng', @_Key, '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Update.oper.opng_vat.to.opng') THEN
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;
		DROP TEMPORARY TABLE IF EXISTS `t_tabwart_ng`;
		CREATE TEMPORARY TABLE `t_tabwart_ng` (
			SELECT 
				SUM(IF(v.work_FieldName=1,o.netto,0)) AS tabwartnetto1,
				SUM(IF(v.work_FieldName=2,o.netto,0)) AS tabwartnetto2,
				SUM(IF(v.work_FieldName=3,o.netto,0)) AS tabwartnetto3,
				SUM(IF(v.work_FieldName=4,o.netto,0)) AS tabwartnetto4,
				SUM(IF(v.work_FieldName=5,o.netto,0)) AS tabwartnetto5,
				SUM(IF(v.work_FieldName=6,o.netto,0)) AS tabwartnetto6,
				SUM(IF(v.work_FieldName=7,o.netto,0)) AS tabwartnetto7,
				SUM(IF(v.work_FieldName=8,o.netto,0)) AS tabwartnetto8,
				SUM(IF(v.work_FieldName=9,o.netto,0)) AS tabwartnetto9,
				SUM(IF(v.work_FieldName=10,o.netto,0)) AS tabwartnetto10,
				SUM(IF(v.work_FieldName=11,o.netto,0)) AS tabwartnetto11,
				SUM(IF(v.work_FieldName=12,o.netto,0)) AS tabwartnetto12,
				SUM(IF(v.work_FieldName=13,o.netto,0)) AS tabwartnetto13,
				SUM(IF(v.work_FieldName=14,o.netto,0)) AS tabwartnetto14,

				SUM(IF(v.work_FieldName=1,o.vat,0)) AS tabwartvat1,
				SUM(IF(v.work_FieldName=2,o.vat,0)) AS tabwartvat2,
				SUM(IF(v.work_FieldName=3,o.vat,0)) AS tabwartvat3,
				SUM(IF(v.work_FieldName=4,o.vat,0)) AS tabwartvat4,
				SUM(IF(v.work_FieldName=5,o.vat,0)) AS tabwartvat5,
				SUM(IF(v.work_FieldName=6,o.vat,0)) AS tabwartvat6,
				SUM(IF(v.work_FieldName=7,o.vat,0)) AS tabwartvat7,
				SUM(IF(v.work_FieldName=8,o.vat,0)) AS tabwartvat8,
				SUM(IF(v.work_FieldName=9,o.vat,0)) AS tabwartvat9,
				SUM(IF(v.work_FieldName=10,o.vat,0)) AS tabwartvat10,
				SUM(IF(v.work_FieldName=11,o.vat,0)) AS tabwartvat11,
				SUM(IF(v.work_FieldName=12,o.vat,0)) AS tabwartvat12,
				SUM(IF(v.work_FieldName=13,o.vat,0)) AS tabwartvat13,
				SUM(IF(v.work_FieldName=14,o.vat,0)) AS tabwartvat14,

				SUM(IF(v.work_FieldName=1,o.brutto,0)) AS tabwartbrutto1,
				SUM(IF(v.work_FieldName=2,o.brutto,0)) AS tabwartbrutto2,
				SUM(IF(v.work_FieldName=3,o.brutto,0)) AS tabwartbrutto3,
				SUM(IF(v.work_FieldName=4,o.brutto,0)) AS tabwartbrutto4,
				SUM(IF(v.work_FieldName=5,o.brutto,0)) AS tabwartbrutto5,
				SUM(IF(v.work_FieldName=6,o.brutto,0)) AS tabwartbrutto6,
				SUM(IF(v.work_FieldName=7,o.brutto,0)) AS tabwartbrutto7,
				SUM(IF(v.work_FieldName=8,o.brutto,0)) AS tabwartbrutto8,
				SUM(IF(v.work_FieldName=9,o.brutto,0)) AS tabwartbrutto9,
				SUM(IF(v.work_FieldName=10,o.brutto,0)) AS tabwartbrutto10,
				SUM(IF(v.work_FieldName=11,o.brutto,0)) AS tabwartbrutto11,
				SUM(IF(v.work_FieldName=12,o.brutto,0)) AS tabwartbrutto12,
				SUM(IF(v.work_FieldName=13,o.brutto,0)) AS tabwartbrutto13,
				SUM(IF(v.work_FieldName=14,o.brutto,0)) AS tabwartbrutto14,
				
				SUM(o.Netto) AS Netto, 
				SUM(o.Vat) AS Vat,  
				SUM(o.Brutto) AS Brutto,
				SUM(o.NettoWal) AS NettoWal, 
				SUM(o.VatWal) AS VatWal,  
				SUM(o.BruttoWal) AS BruttoWal

			FROM opng_vat o
			INNER JOIN v_erp_vat v ON o.idvat=v.ID
			WHERE o.IdnOper = _Key
			GROUP BY o.idnoper
		)	
		;   
		UPDATE `opng` n, `t_tabwart_ng` tw
		SET
			n.tabwartnetto1 = tw.tabwartnetto1,
			n.tabwartnetto2 = tw.tabwartnetto2,
			n.tabwartnetto3 = tw.tabwartnetto3,
			n.tabwartnetto4 = tw.tabwartnetto4,
			n.tabwartnetto5 = tw.tabwartnetto5,
			n.tabwartnetto6 = tw.tabwartnetto6,
			n.tabwartnetto7 = tw.tabwartnetto7,
			n.tabwartnetto8 = tw.tabwartnetto8,
			n.tabwartnetto9 = tw.tabwartnetto9,
			n.tabwartnetto10 = tw.tabwartnetto10,
			n.tabwartnetto11 = tw.tabwartnetto11,
			n.tabwartnetto12 = tw.tabwartnetto12,
			n.tabwartnetto13 = tw.tabwartnetto13,
			n.tabwartnetto14 = tw.tabwartnetto14,
			
			n.tabwartvat1 = tw.tabwartvat1,
			n.tabwartvat2 = tw.tabwartvat2,
			n.tabwartvat3 = tw.tabwartvat3,
			n.tabwartvat4 = tw.tabwartvat4,
			n.tabwartvat7 = tw.tabwartvat7,
			n.tabwartvat8 = tw.tabwartvat8,
			n.tabwartvat9 = tw.tabwartvat9,
			n.tabwartvat10 = tw.tabwartvat10,
			n.tabwartvat11 = tw.tabwartvat11,
			n.tabwartvat12 = tw.tabwartvat12,
			
			n.tabwartBrutto1 = tw.tabwartBrutto1,
			n.tabwartBrutto2 = tw.tabwartBrutto2,
			n.tabwartBrutto3 = tw.tabwartBrutto3,
			n.tabwartBrutto4 = tw.tabwartBrutto4,
			n.tabwartBrutto7 = tw.tabwartBrutto7,
			n.tabwartBrutto8 = tw.tabwartBrutto8,
			n.tabwartBrutto9 = tw.tabwartBrutto9,
			n.tabwartBrutto10 = tw.tabwartBrutto10,
			n.tabwartBrutto11 = tw.tabwartBrutto11,
			n.tabwartBrutto12 = tw.tabwartBrutto12,
			
			n.Netto = tw.Netto, 
			n.Vat = tw.Vat,  
			n.Brutto = tw.Brutto,
			n.NettoWal = tw.NettoWal, 	--	Gdy Rachunek zakup-rach uproszczony i kurs=1 a kurs_rozliczen <> 1
			-- n.VatWal = tw.VatWal,  
			n.BruttoWal = tw.BruttoWal
		WHERE 
			n.GUID = _Key	
		;
		DROP TEMPORARY TABLE IF EXISTS `t_tabwart_ng`;
	END IF;	--	IF _MODE = 'Update.oper.opng_vat.to.opng'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Update.oper.from.tabwart_x'
	Nalicza `opng_vat` dla jednej operacj GUID - usuwa i wstawia aktualne vatowe agregaty warto�ci z tabwart_x
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A
	CALL erp_vat_tools('Update.oper.from.tabwart_x', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Update.oper.from.tabwart_x') THEN
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;
		
	--	Usu� z `opng_vat` wszystkie rekordy operacji _Key
		DELETE v FROM `opng_vat` v, `opng` n 
		WHERE
			v.IdnOper = _Key
			AND
			n.GUID = _Key
			AND
			(NOT Fixed OR n.Calc_Ver = 0)
		;	
		/*
		UPDATE `opng`
		SET
			tabwartnetto13 = bezodliczen
		WHERE
			GUID = _Key AND
			(tabwartnetto13 != bezodliczen) AND
			(bezodliczen != 0)
--			(tabwartnetto13 != 0 OR bezodliczen != 0)
		;
		*/
	--	Dodaj do `opng_vat` rekordy z tabwart...
		INSERT IGNORE INTO `opng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				n.GUID AS IdnOper,
				v.ID AS IdVat,
				elt(v.work_FieldName, tabwartnetto1,tabwartnetto2,tabwartnetto3,tabwartnetto4,tabwartnetto5,tabwartnetto6,tabwartnetto7,tabwartnetto8,tabwartnetto9,tabwartnetto10,tabwartnetto11,tabwartnetto12,tabwartnetto13,tabwartnetto14) AS Netto, 
				elt(v.work_FieldName, tabwartvat1,tabwartvat2,tabwartvat3,tabwartvat4,0,0,tabwartvat7,tabwartvat8,tabwartvat9,tabwartvat10,tabwartvat11,tabwartvat12,0,0) AS Vat, 
				elt(v.work_FieldName, tabwartBrutto1,tabwartBrutto2,tabwartBrutto3,tabwartBrutto4,0,0,tabwartBrutto7,tabwartBrutto8,tabwartBrutto9,tabwartBrutto10,tabwartBrutto11,tabwartBrutto12,0,0) AS Brutto, 
				0 AS NettoWal, 0 AS VatWal, 0 AS BruttoWal,
				0 AS OperCount
			FROM `opng` n 
			LEFT JOIN `v_erp_vat` v ON v.work_FieldName > 0 -- TRUE
			WHERE
				n.GUID = _Key
			HAVING Netto <> 0 OR Vat <> 0

		;	
		UPDATE `opng_vat` v
		SET
			Brutto = Netto + Vat,
			OperCount = 
				IFNULL(
					(SELECT COUNT(*) FROM `oppo` WHERE IdnOperFin = _KEY AND IdnVat = v.IdVat),
					0
				)	
		WHERE
			v.IdnOper = _Key
		;
		
	END IF;	--	IF _MODE = 'Update.oper.from.tabwart_x'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Update.oper.all'
	Nalicza `opng_vat` dla wszystkich operacj - usuwa i wstawia aktualne vatowe agregaty warto�ci z tabwart_x
		Parametry
			_Key		NIE ISTOTNY
			_Param_A
	CALL erp_vat_tools('Update.oper.all', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Update.oper.all') THEN
	--	Usu� z `opng_vat` wszystkie rekordy 
		TRUNCATE `opng_vat`;	
		
	--	Dodaj do `opng_vat` rekordy z tabwart...
		INSERT IGNORE INTO `opng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				n.GUID AS IdnOper,
				v.ID AS IdVat,
				elt(v.work_FieldName, tabwartnetto1,tabwartnetto2,tabwartnetto3,tabwartnetto4,tabwartnetto5,tabwartnetto6,tabwartnetto7,tabwartnetto8,tabwartnetto9,tabwartnetto10,tabwartnetto11,tabwartnetto12,tabwartnetto13,tabwartnetto14) AS Netto, 
				elt(v.work_FieldName, tabwartvat1,tabwartvat2,tabwartvat3,tabwartvat4,0,0,tabwartvat7,tabwartvat8,tabwartvat9,tabwartvat10,tabwartvat11,tabwartvat12,0,0) AS Vat, 
				elt(v.work_FieldName, tabwartBrutto1,tabwartBrutto2,tabwartBrutto3,tabwartBrutto4,0,0,tabwartBrutto7,tabwartBrutto8,tabwartBrutto9,tabwartBrutto10,tabwartBrutto11,tabwartBrutto12,0,0) AS Brutto, 
				
				0 AS NettoWal, 0 AS VatWal, 0 AS BruttoWal,
				0 AS OperCount
			FROM `opng` n 
			LEFT JOIN `v_erp_vat` v ON v.work_FieldName > 0 -- TRUE
			WHERE
				n.netto<>0 or n.brutto<>0 
			HAVING Netto <> 0 OR Vat <> 0

		;	
		UPDATE `opng_vat` v
		SET
			Brutto = Netto + Vat
		;
		LEAVE SPROC;
		
		
		
		
		
		SET @Session.vat.koszt_nabycia.coeff = IF(IFNULL(@session.HT.IR.PomniejszZTUEOKosztNabycia, FALSE), 1, 0);
	--	Dodaj do `opng_vat` rekordy agregowane z ka�dej pozycji 
		INSERT IGNORE INTO `opng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				p.IdnOper,		--	IdnOper
				p.idnvat,		--	IdVat
			--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1
				_Rnd2(SUM(p.WartNet) - SUM(@Session.vat.koszt_nabycia.coeff * p.koszt_nabycia)),		
			--	Vat
				_Rnd2(SUM(p.WartVat)),		
			--	Brutto
				_Rnd2(SUM(p.WartBrt)),		
			--	NettoWal
				_Rnd2(SUM(0)),		
			--	VatWal
				_Rnd2(SUM(0)),		
			--	BruttoWal
				_Rnd2(SUM(0)),
			--	OperCount
				_VarInt(Count(*))
			FROM `oppo` p	
			WHERE
				TRUE
				AND p.Ilosc <> 0 
				AND p.WartVat <> 0
			GROUP BY p.IdnOper, p.idnvat	
		;
	END IF;	--	IF _MODE = 'Update.oper.all'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Update.zam'
	Nalicza `opng_vat` dla danej (_Key) operacj - usuwa i wstawia aktualne vatowe agregaty z `zmpo`
		Parametry
			_Key		GUID operacji (IdnNg)
			_Param_A
	CALL erp_vat_tools('Update.zam', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Update.zam') THEN
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;

	--	Jesli w naglowku zaakceptowano roznice m-dzy TabVat i ...
		SELECT 
			Rodzaj, przeliczenie_B_N 
		INTO 
			@Session.opng.Rodzaj, @Session.vat.przeliczenie_B_N 
		FROM `zmng` n 
		WHERE 
			n.GUID = _Key
		;
		SET @Session.vat.koszt_nabycia.coeff = IF(IFNULL(@session.HT.IR.PomniejszZTUEOKosztNabycia, FALSE), 1, 0);
		
		--	Z W Y K L Y  D O K U M E N T
		--	Dodaj do `t_opng_vat` rekordy agregowane z pozycji operacji _Key
		IF @session.globals.Owner = 'HYDROMEGA' THEN
			DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
			CREATE TEMPORARY TABLE `t_opng_vat` 
			(
				SELECT
					_Key AS IdnOper,			--	IdnOper
					p.idnvat AS IdVat,		--	IdVat
				--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
					_Rnd2(SUM(if(p.usluga=8, 0, p.WartNet))) AS Netto,		
				--	Vat
					_Rnd2(SUM(if(p.usluga=8, 0, p.WartVat))) AS Vat,		
				--	Brutto
					_Rnd2(SUM(if(p.usluga=8, 0, p.WartBrt))) AS Brutto,		
				--	NettoWal
					_Rnd2(SUM(if(p.usluga=8, 0, p.WartNetWal))) AS NettoWal,		
				--	VatWal
					_Rnd2(SUM(0)) AS VatWal,		
				--	BruttoWal
					_Rnd2(SUM(0)) AS BruttoWal,
				--	OperCount
					_VarInt(Count(*)) AS OperCount
				FROM `zmng` n	
				LEFT JOIN `zmpo` p	ON p.IdnNg = n.GUID AND p.Wersja = n.Wersja
					-- FROM `zmpo` p	
				WHERE
					n.GUID = _Key
					-- p.IdnNg = _Key
					AND 
					(p.Ilosc <> 0 OR p.WartVat <> 0)
				GROUP BY 
				-- p.IdnOper,  czemu tak?
				p.idnvat	
			)	
			;
		ELSE
			DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
			CREATE TEMPORARY TABLE `t_opng_vat` 
			(
				SELECT
					_Key AS IdnOper,			--	IdnOper
					p.idnvat AS IdVat,		--	IdVat
				--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
					_Rnd2(SUM(p.WartNet)) AS Netto,		
				--	Vat
					_Rnd2(SUM(p.WartVat)) AS Vat,		
				--	Brutto
					_Rnd2(SUM(p.WartBrt)) AS Brutto,		
				--	NettoWal
					_Rnd2(SUM(p.WartNetWal)) AS NettoWal,		
				--	VatWal
					_Rnd2(SUM(0)) AS VatWal,		
				--	BruttoWal
					_Rnd2(SUM(0)) AS BruttoWal,
				--	OperCount
					_VarInt(Count(*)) AS OperCount
				FROM `zmng` n	
				LEFT JOIN `zmpo` p	ON p.IdnNg = n.GUID AND p.Wersja = n.Wersja
					-- FROM `zmpo` p	
				WHERE
					n.GUID = _Key
					-- p.IdnNg = _Key
					AND 
					(p.Ilosc <> 0 OR p.WartVat <> 0)
				GROUP BY 
				-- p.IdnOper,  czemu tak?
				p.idnvat	
			)	
			;
		END IF;
		
		IF @Session.vat.przeliczenie_B_N = 0 THEN
		--	Netto -> Brutto
			UPDATE `t_opng_vat` v, zmng n, v_erp_vat ev
			SET
				v.Brutto = _Rnd2(v.Netto*(1 + ev.`value`)),
				v.Vat = _Rnd2(v.Netto*(1 + ev.`value`)) - v.Netto,
				v.BruttoWal = _Rnd2(v.NettoWal*(1 + ev.`value`)),
				v.VatWal = _Rnd2(v.NettoWal*(1 + ev.`value`)) - v.NettoWal
						
			WHERE
				v.IdnOper = _Key and n.GUID=_Key and ev.id=v.IdVat AND 
				n.przeliczenie_B_N = 0			
			;
			
		ELSE
		--	Brutto -> Netto
			UPDATE `t_opng_vat` v, zmng n, v_erp_vat ev
			SET
				v.Netto = _Rnd2(v.Brutto/(1 + ev.`value`)),
				v.Vat = v.Brutto - _Rnd2(v.Brutto/(1 + ev.`value`)),
				v.NettoWal = _Rnd2(v.BruttoWal/(1 + ev.`value`)),
				v.VatWal = v.BruttoWal - _Rnd2(v.BruttoWal/(1 + ev.`value`))
			WHERE
				v.IdnOper = _Key and n.GUID=_Key and ev.id=v.IdVat AND 
				n.przeliczenie_B_N = 1			
			;
			
		END IF;
		
	--	Usu� z `zmng_vat` wszystkie rekordy operacji _Key
		DELETE FROM `zmng_vat` 
		WHERE
			IdnOper = _Key
		;	
		
	--	Wstaw do `opng_vat` z naliczonego wyzej `t_opng_vat` wszystkie rekordy 
	
		INSERT IGNORE INTO `zmng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				IdnOper,			--	IdnOper
				IdVat,		--	IdVat
			--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1 ?????? APRO si� zastanowi
				_Rnd2(SUM(Netto)) AS Netto,		
			--	Vat
				_Rnd2(SUM(Vat)) AS Vat,		
			--	Brutto
				_Rnd2(SUM(Brutto)) AS Brutto,		
			--	NettoWal
				_Rnd2(SUM(NettoWal)) AS NettoWal,		
			--	VatWal
				_Rnd2(SUM(VatWal)) AS VatWal,		
			--	BruttoWal
				_Rnd2(SUM(BruttoWal)) AS BruttoWal,
			--	OperCount
				OperCount
			FROM `t_opng_vat` p
			
			GROUP BY 
				IdVat
		;
		DROP TEMPORARY TABLE IF EXISTS `t_opng_vat`;
		
		UPDATE `zmng` n
		SET
			n.Brutto = (
				SELECT IFNULL(SUM(v.Brutto), 0) FROM `zmng_vat` v WHERE v.IdnOper = n.GUID
			),
			n.BruttoWal = (
				SELECT IFNULL(SUM(v.BruttoWal), 0) FROM `zmng_vat` v WHERE v.IdnOper = n.GUID
			)
		WHERE
			n.GUID = _Key
		;
		
		



	/*		
		
		LEAVE SPROC;
		
	--	Usu� z `zmng_vat` wszystkie rekordy operacji _Key
		DELETE FROM `zmng_vat` 
		WHERE
			IdnOper = _Key
		;	
		
		-- SET @Session.vat.koszt_nabycia.coeff = IF(IFNULL(@session.HT.IR.PomniejszZTUEOKosztNabycia, FALSE), 1, 0);
	--	Dodaj do `opng_vat` rekordy agregowane z pozycji operacji _Key
		INSERT IGNORE INTO `zmng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				_Key,			--	IdnOper
				p.idnvat,		--	IdVat
			--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1
				_Rnd2(SUM(p.WartNet)), -- - SUM(@Session.vat.koszt_nabycia.coeff * p.koszt_nabycia),),		
			--	Vat
				-- by�o ROUND(SUM(p.WartVat), 2),		
				_Rnd2(_Rnd2(SUM(p.WartNet) * v.VALUE)),		
			--	Brutto
				_Rnd2(SUM(p.WartBrt)),		
			--	NettoWal
				_Rnd2(SUM(0)),		
			--	VatWal
				_Rnd2(SUM(0)),		
			--	BruttoWal
				_Rnd2(SUM(0)),
			--	OperCount
				_VarInt(Count(*))
			FROM `zmng` n	
			LEFT JOIN `zmpo` p	ON p.IdnNg = n.GUID AND p.Wersja = n.Wersja
			INNER JOIN `v_erp_vat` v ON v.ID = p.idnvat
			WHERE
				n.GUID = _Key
				AND p.Ilosc <> 0 
				AND (p.WartNet <> 0 OR p.WartVat <> 0)
			GROUP BY p.IdnNg, p.idnvat	
		;
		UPDATE `zmng_vat` v
		SET
			Brutto = Netto + Vat
		WHERE
			v.IdnOper = _Key
		;
	*/		
	END IF;	--	IF _MODE = 'Update.zam'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Update.zam.all'
	Nalicza `opng_vat` dla wszystkich operacj - usuwa i wstawia aktualne vatowe agregaty z `oppo`
		Parametry
			_Key		NIE ISTOTNY
			_Param_A
	CALL erp_vat_tools('Update.zam.all', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Update.zam.all') THEN
	
	
		BEGIN
			DECLARE _Curr_Key VARCHAR(38);
			
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE c_proc_zm_all CURSOR FOR
				SELECT
					n.GUID
				FROM `zmng` n	
				INNER JOIN `zmpo` p ON p.IdnNg = n.GUID AND p.Wersja = n.Wersja
				WHERE
					TRUE
					AND 
						(p.WartNet <> 0 OR p.WartVat <> 0)
				GROUP BY p.IdnNg
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc_zm_all;
			loop1: LOOP
				FETCH c_proc_zm_all INTO
					_Curr_Key
				;
				IF done THEN
					LEAVE loop1;
				ELSE
				
				--	kolejny rekord
					CALL erp_vat_tools('Update.zam', _Curr_Key, '', '', '', '');
				END IF;
			END LOOP;
			CLOSE c_proc_zm_all;
		END;
		
		LEAVE SPROC;
	
		/*	
	
	--	Usu� z `opng_vat` wszystkie rekordy 
		TRUNCATE `zmng_vat`;	
		
		-- SET @Session.vat.koszt_nabycia.coeff = IF(IFNULL(@session.HT.IR.PomniejszZTUEOKosztNabycia, FALSE), 1, 0);
	--	Dodaj do `opng_vat` rekordy agregowane z ka�dej pozycji 
		INSERT IGNORE INTO `zmng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				p.IdnNg,		--	IdnNg
				p.idnvat,		--	IdVat
			--	Netto	/ gdy w��czone jest odejmowanie koszt�w naby cia to @Session.vat.koszt_nabycia.coeff = 1
				_Rnd2(SUM(p.WartNet)), -- - SUM(@Session.vat.koszt_nabycia.coeff * p.koszt_nabycia), 2),		
			--	Vat
				-- by�o ROUND(SUM(p.WartVat), 2),		
				_Rnd2(_Rnd2(SUM(p.WartNet) * v.VALUE)),			
			--	Brutto
				_Rnd2(SUM(p.WartBrt)),		
			--	NettoWal
				_Rnd2(SUM(0)),		
			--	VatWal
				_Rnd2(SUM(0)),		
			--	BruttoWal
				_Rnd2(SUM(0)),
			--	OperCount
				_VarInt(Count(*))
			FROM `zmng` n	
			LEFT JOIN `zmpo` p	ON p.IdnNg = n.GUID AND p.Wersja = n.Wersja
			INNER JOIN `v_erp_vat` v ON v.ID = p.idnvat
			WHERE
				TRUE
				AND 
					(p.WartNet <> 0 OR p.WartVat <> 0)
			GROUP BY p.IdnNg, p.idnvat	
		;
		UPDATE `zmng_vat` v
		SET
			Brutto = Netto + Vat
		;
		
		UPDATE `zmng` n
		SET
			n.Brutto = (
				SELECT SUM(v.Brutto) FROM `zmng_vat` v WHERE v.IdnOper = n.GUID
			),
			n.BruttoWal = (
				SELECT SUM(v.BruttoWal) FROM `zmng_vat` v WHERE v.IdnOper = n.GUID
			)
		;
		*/		
	END IF;	--	IF _MODE = 'Update.zam.all'
	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'Select.oper.agg.netto.vat.brutto'
	Pobiera zagregowane wartosci Netto, Vat i Brutto (do wpisu do nag��wka w Delphi)
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A
	CALL erp_vat_tools('Update.oper', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'Select.oper.agg.netto.vat.brutto') THEN
		SELECT
			IdnOper, 
			_VarDouble(SUM(Netto)) AS Netto, 
			_VarDouble(SUM(Vat)) AS Vat, 
			_VarDouble(SUM(Brutto)) AS Brutto 
		FROM `opng_vat` 	
		WHERE
			IdnOper = _Key
		;
		
	END IF;	--	IF _MODE = 'Select.oper.agg.netto.vat.brutto'
	
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'vattable.prepare'
	Pobiera tabelk� vat - rzadk� (tylko niezerowe np. dla wydruk�w) lub pe�na czyli wszystkie stawki 
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	IsZM	0 - to operacja i bierzemy z opng_vat;	1 - to zam�wienie i bierzemy z zmng_vat
			_Param_B	'' - tabelka VAT rzadka, 'ALL' tabelka g�sta
	CALL erp_vat_tools('vattable.prepare', 'GUID', '', '', '', '');	-- tabelka oper rzadka
	CALL erp_vat_tools('vattable.prepare', 'GUID', '1', '', '', '');	-- tabelka zam rzadka
	CALL erp_vat_tools('vattable.prepare', 'GUID', '', 'ALL', '', '');	-- tabelka oper g�sta
	CALL erp_vat_tools('vattable.prepare', 'GUID', '1', 'ALL', '', '');	-- tabelka zam g�sta
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'vattable.prepare') THEN
		DROP TEMPORARY TABLE IF EXISTS `t_oper_vattable`;
		CREATE TEMPORARY TABLE IF NOT EXISTS `t_oper_vattable` 
		(
			`IdnOper` VARCHAR(38) NOT NULL DEFAULT '', 
			`IdVat` INTEGER NOT NULL DEFAULT '0', 
			`Vat_Name` VARCHAR(200) NOT NULL DEFAULT '',
			`Vat_ShortName` VARCHAR(200) NOT NULL DEFAULT '',
			`Vat_Value` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`Netto` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`Vat` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`Brutto` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`NettoWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`VatWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`BruttoWal` DOUBLE(16,4) NOT NULL DEFAULT '0.0',
			`OperCount` INTEGER NOT NULL DEFAULT 0, 
			`OperCountStr` VARCHAR(200) NOT NULL DEFAULT '',
			`Exist` INTEGER NOT NULL DEFAULT 0, 
			`Changed` INTEGER NOT NULL DEFAULT 0, 
			`IdOrder` VARCHAR(20) NOT NULL DEFAULT '',
			PRIMARY KEY (`IdVat`)
		) 
		;
		
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;
		
		SET _Param_A = IFNULL(_Param_A, '');
		SET _Param_A = IF(_Param_A = '', '0', _Param_A);
		IF (_Param_B = '') OR TRUE THEN
			SET _SQLText =
			'
			INSERT INTO `t_oper_vattable` 
				(IdnOper, IdVat, Vat_Name, Vat_ShortName, Vat_Value, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount, Exist, Changed, IdOrder)
				SELECT
					ov.IdnOper,
					v.Id AS IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					ov.Netto, ov.Vat, ov.Brutto,
					ov.NettoWal, ov.VatWal, ov.BruttoWal,
					_VarInt(IFNULL(ov.OperCount, 0)) AS OperCount,
					_VarInt(ov.IdnOper IS NOT NULL) AS Exist,
					_VarInt(0) AS Changed,
					v.IdOrder
				FROM `###` ov
				INNER JOIN `v_erp_vat` v ON v.ID = ov.IdVat
				WHERE
					ov.IdnOper = "_Key"
					AND 
					NOT ((ov.Netto = 0) AND (ov.Vat = 0) AND (ov.Brutto = 0))
				ORDER BY IdOrder
			;'
			;
			
		END IF;
		
		IF _Param_B = 'ALL' THEN
			SET _SQLText =
			'
			INSERT IGNORE INTO `t_oper_vattable` 
				(IdnOper, IdVat, Vat_Name, Vat_ShortName, Vat_Value, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount, Exist, Changed, IdOrder)
				SELECT
					"_Key",
					v.Id AS IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					ov.Netto, ov.Vat, ov.Brutto,
					ov.NettoWal, ov.VatWal, ov.BruttoWal,
					_VarInt(IFNULL(ov.OperCount, 0)) AS OperCount,
					_VarInt(ov.IdnOper IS NOT NULL) AS Exist,
					_VarInt(0) AS Changed,
					v.IdOrder
				FROM `v_erp_vat` v
				LEFT JOIN `###` ov ON v.ID = ov.IdVat AND ov.IdnOper = "_Key"
				WHERE
					(NOT v.not_used)
				ORDER BY IdOrder
			;'
			;
		END IF;
		
		IF _Param_A = '0' THEN
			SET _SQLText =	REPLACE(_SQLText, '###', 'opng_vat');
		ELSE
			SET _SQLText =	REPLACE(_SQLText, '###', 'zmng_vat');
		END IF;
		
		SET _SQLText =	REPLACE(_SQLText, '_Key', _Key);
		SET @_SQLText =	_SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
		
		UPDATE `t_oper_vattable` t
		SET
			t.OperCountStr = IF(t.OperCount = 0, '', CONCAT('(', t.OperCount, ' oper.)'))
		;
		
	END IF;	--	IF _MODE = 'vattable.prepare'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'vattable.select'
	Pobiera tabelk� vat - rzadk� (tylko niezerowe np. dla wydruk�w) lub pe�na czyli wszystkie stawki 
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	IsZM	0 - to operacja i bierzemy z opng_vat;	1 - to zam�wienie i bierzemy z zmng_vat
			_Param_B	'' - tabelka VAT rzadka, 'ALL' tabelka g�sta
	CALL erp_vat_tools('vattable.select', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'vattable.select') THEN
		CALL erp_vat_tools('vattable.prepare', _Key, _Param_A, _Param_B, _Param_C, _Param_D);
		SELECT
			CONCAT(t.Vat_Name, ' ', t.OperCountStr) AS Vat_Descr,
			t.*,
			CONCAT(t.Vat_Name, ' ', t.OperCountStr) AS IdnVarStr,
			-- CONCAT(t.Vat_ShortName, ' ', t.OperCountStr) AS `Name`
			t.Vat_ShortName AS `Name`
		FROM `t_oper_vattable` t
		;
		
	END IF;	--	IF _MODE = 'vattable.select'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'vattable.select.oper'
	Pobiera tabelk� vat operacji - rzadk� (tylko niezerowe np. dla wydruk�w) lub pe�na czyli wszystkie stawki 
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	
	CALL erp_vat_tools('vattable.select.oper', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'vattable.select.oper') THEN
		CALL erp_vat_tools('vattable.select', _Key, '0', _Param_B, _Param_C, _Param_D);
	END IF;	--	IF _MODE = 'vattable.select.oper'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'vattable.select.zam'
	Pobiera tabelk� vat zam�wienia - rzadk� (tylko niezerowe np. dla wydruk�w) lub pe�na czyli wszystkie stawki 
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	
	CALL erp_vat_tools('vattable.select.zam', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'vattable.select.zam') THEN
		CALL erp_vat_tools('vattable.select', _Key, '1', _Param_B, _Param_C, _Param_D);
	END IF;	--	IF _MODE = 'vattable.select.zam'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'vattable.select.print'
	Pobiera tabelk� vat - rzadk� (tylko niezerowe np. dla wydruk�w) lub pe�na czyli wszystkie stawki 
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	IsZM	0 - to operacja i bierzemy z opng_vat;	1 - to zam�wienie i bierzemy z zmng_vat
			_Param_B	'' - tabelka VAT rzadka, 'ALL' tabelka g�sta
	CALL erp_vat_tools('vattable.select.print', 'GUID', '', '', '', '');
	CALL erp_vat_tools('vattable.select.print', 'GUID', '1', '', '', '');		-- zam�wienia
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'vattable.select.print') THEN
		CALL erp_vat_tools('vattable.select', _Key, _Param_A, '', _Param_C, _Param_D);
	END IF;	--	IF _MODE = 'oper.vattable.select.print'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'oper.vattable.apply'
	Pobiera tabelk� vat - rzadk� (tylko niezerowe np. dla wydruk�w) lub pe�na czyli wszystkie stawki 
		Parametry
			_Key		GUID operacji (IdnOper)
			_Param_A	
	CALL erp_vat_tools('oper.vattable.apply', 'GUID', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'oper.vattable.apply') THEN
		IF IFNULL(_Key, '') = '' THEN
			LEAVE SPROC;
		END IF;
		
	--	Usu� z `opng_vat` wszystkie rekordy operacji _Key
		DELETE FROM `opng_vat` 
		WHERE
			IdnOper = _Key
		;	
		
		INSERT IGNORE INTO `opng_vat` 
			(IdnOper, IdVat, Netto, Vat, Brutto, NettoWal, VatWal, BruttoWal, OperCount)
			SELECT
				_Key, t.IdVat, t.Netto, t.Vat, t.Brutto, t.NettoWal, t.VatWal, t.BruttoWal, 0
			FROM `t_oper_vattable` t
			WHERE
				t.IdnOper = _Key AND
				(
					t.Netto <> 0 OR
					t.Vat <> 0 OR
					t.Brutto <> 0
				)	
		;
		UPDATE `opng_vat` ov
		SET
			OperCount = 
				IFNULL(
					(SELECT COUNT(*) FROM oppo WHERE IdnOper = _Key AND idnvat = ov.IdVat),
					0)
		WHERE
			ov.IdnOper = _Key
		;	
		
	END IF;	--	IF _MODE = 'oper.vattable.apply'
	
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'RV.prepare.field.part'
	'RV.prepare.field.part_update'
	Pobiera domy�lna warto�� stawki vat - dla nowej karty towarowej
		Parametry
			_Key		NIE ISTOTNY
			_Param_A
			_Param_B	<> '' to ASEKURACJA
	CALL erp_vat_tools('RV.prepare.field.part', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'RV.prepare.field.part%') THEN
		IF (_MODE = 'RV.prepare.field.part') AND (@RV.field.part IS NOT NULL) THEN
			LEAVE SPROC;
		END IF;
		IF (_MODE = 'RV.prepare.field.part_update') AND (@RV.field.part_update IS NOT NULL) THEN
			LEAVE SPROC;
		END IF;
		
		IF (_MODE = 'RV.prepare.field.part') THEN
			SET @RV.field.part = '';
		ELSE	
			SET @RV.field.part_update = '';
		END IF;
		
		BEGIN
			DECLARE _IdVat INTEGER;
			DECLARE _Not_Used INTEGER;
			DECLARE _work_Field_Prefix VARCHAR(50);
			
			DECLARE done BOOL DEFAULT FALSE;
			DECLARE c_proc CURSOR FOR
				SELECT
					v.ID AS IdVat, 
					v.Not_Used,
					v.work_Field_Prefix
				FROM `v_erp_vat` v
				;
			DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
			OPEN c_proc;
			loop1: LOOP
				FETCH c_proc INTO
					_IdVat, 
					_Not_Used,
					_work_Field_Prefix
				;
				IF done THEN
					LEAVE loop1;
				ELSE
				
				--	kolejny rekord
					IF (_MODE = 'RV.prepare.field.part') THEN
					--	Netto
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							CONCAT('				SUM(IF(ov.IdVat = ', _IdVat, ', ov.Netto, 0)) AS ', _work_Field_Prefix, 'Netto'), 
							',\n\n')
						;
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							IF(_Param_B != '',
							CONCAT('				SUM(IF(OP.idnvat= ', _IdVat, ' and T.wyroznik in ("ZC","SAM ZC"), OP.wartnet, 0)) AS ', _work_Field_Prefix, 'Netto_C'), 
							CONCAT('				_VarDouble(0) AS ', _work_Field_Prefix, 'Netto_C')
							), 
							',\n')
						;
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							IF(_Param_B != '',
							CONCAT('				SUM(IF(OP.idnvat= ', _IdVat, ' and T.wyroznik in ("ZN","SAM ZN"), OP.wartnet, 0)) AS ', _work_Field_Prefix, 'Netto_N'), 
							CONCAT('				_VarDouble(0) AS ', _work_Field_Prefix, 'Netto_N')
							), 
							',\n')
						;
					--	Vat
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							CONCAT('				SUM(IF(ov.IdVat = ', _IdVat, ', ov.Vat, 0)) AS ', _work_Field_Prefix, 'Vat'), 
							',\n')
						;
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							IF(_Param_B != '',
							CONCAT('				SUM(IF(OP.idnvat= ', _IdVat, ' and T.wyroznik in ("ZC","SAM ZC"), OP.wartvat, 0)) AS ', _work_Field_Prefix, 'Vat_C'), 
							CONCAT('				_VarDouble(0) AS ', _work_Field_Prefix, 'Vat_C')
							), 
							',\n')
						;
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							IF(_Param_B != '',
							CONCAT('				SUM(IF(OP.idnvat= ', _IdVat, ' and T.wyroznik in ("ZN","SAM ZN"), OP.wartvat, 0)) AS ', _work_Field_Prefix, 'Vat_N'), 
							CONCAT('				_VarDouble(0) AS ', _work_Field_Prefix, 'Vat_N')
							), 
							',\n')
						;
					--	Brutto
						SET 
							@RV.field.part = _ConcatSQLFields(@RV.field.part,
							CONCAT('				SUM(IF(ov.IdVat = ', _IdVat, ', ov.Brutto, 0)) AS ', _work_Field_Prefix, 'Brutto'), 
							',\n')
						;
					--	Zako�cz	
					-- 	SET 
					-- 		@RV.field.part = CONCAT(@RV.field.part, '\n');
					ELSE		
					--	Netto
						SET 
							@RV.field.part_update = _ConcatSQLFields(@RV.field.part_update,
								CONCAT('				', _work_Field_Prefix, 'Netto = ', _work_Field_Prefix, 'Netto - ', _work_Field_Prefix, 'Netto_C - ', _work_Field_Prefix, 'Netto_N'), 
								',\n\n'
								)
						;
					--	Vat
						SET 
							@RV.field.part_update = _ConcatSQLFields(@RV.field.part_update,
								CONCAT('				', _work_Field_Prefix, 'Vat = ', _work_Field_Prefix, 'Vat - ', _work_Field_Prefix, 'Vat_C - ', _work_Field_Prefix, 'Vat_N'), 
								',\n'
								)
						;
					--	Zako�cz	
					-- 	SET 
					-- 		@RV.field.part_update = CONCAT(@RV.field.part_update, '\n');
					END IF;
				END IF;
			END LOOP;
			CLOSE c_proc;
		END;
		
	END IF;	--	IF _MODE LIKE 'RV.prepare.field.part%'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'RV.prepare.sql.part'
	Pobiera domy�lna warto�� stawki vat - dla nowej karty towarowej
		Parametry
			_Key		RV_Id
			_Param_A	FILTR
			_Param_B	<> '' to ASEKURACJA
	CALL erp_vat_tools('RV.prepare.sql.part', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'RV.prepare.sql.part') THEN
		SET _Key = IF(_Key = '', 2021, _Key); 
		CALL erp_vat_tools('RV.prepare.field.part', '', '', _Param_B, '', '');
		CALL erp_vat_tools('RV.prepare.field.part_update', '', '', _Param_B, '', '');
		DROP TEMPORARY TABLE IF EXISTS `t_RV`;
		IF _Param_B = '' THEN
			SET _SQLText =
		'
			CREATE TEMPORARY TABLE `t_RV`
				SELECT
					n.RV_Id, n.grupaoper, n.nrwewn, n.nrdokfin, n.rodzaj, n.datawplywu, n.datasprzed, n.datawystaw, n.data_vat, n.datarozliczenia, n.brutto, n.netto, n.vat, \n###1,
					n.vat_bez_odliczenia_paliwo, n.vat_bez_odliczenia_pojazd, 
					IF(n.bezodliczen != 0, n.bezodliczen, n.tabwartnetto13) AS bezodliczen, 
					_VarStr(Substring(n.KontrNazwa, 1, 150)) AS Kontrahent, 
					_VarStr(Substring(n.KontrAdres, 1, 150)) AS Kontrahent_Adres, 
					n.statusEDI,
					n.kontrsymbol AS Kontrahent_Symbol,
					n.Kontrnip AS Kontrahent_nip,
					_VarStr(Substring(K.nazwa, 1, 150)) AS Platnik, 
					K.symbol AS Platnik_Symbol,
					K.nip AS Platnik_nip,
					CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta) AS Platnik_adres,
					NN.nrdokfin AS nrkorekty 
				FROM opng n 
				LEFT JOIN `opng_vat` ov ON ov.IdnOper = n.GUID 
				LEFT JOIN opng NN on NN.GUID = n.korektaGUID 
				INNER JOIN operdoc D on D.rodzaj = n.rodzaj 
				LEFT JOIN kontrah K ON n.platnikGUID=K.guid
				WHERE
					(n.Deleted = 0 AND #n.tymczasowa = 0#)
					AND n.RV_Id = #RV_Id AND
					#WHERE
					
				GROUP BY n.GUID
				ORDER BY n.data_vat, n.nrdokfin 
			;
		';
		ELSE	-- ASEKURACJA
			SET _SQLText =
			'
			CREATE TEMPORARY TABLE `t_RV`
				SELECT
					n.RV_Id, n.grupaoper, n.nrwewn, n.nrdokfin, n.rodzaj, n.datawplywu, n.datasprzed, n.datawystaw, n.data_vat, n.datarozliczenia, n.brutto, n.netto, n.vat, \n###1,
					n.vat_bez_odliczenia_paliwo, n.vat_bez_odliczenia_pojazd, 
					IF(n.bezodliczen != 0, n.bezodliczen, n.tabwartnetto13) AS bezodliczen, 
					_VarStr(Substring(n.KontrNazwa, 1, 150)) AS Kontrahent, 
					_VarStr(Substring(n.KontrAdres, 1, 150)) AS Kontrahent_Adres, 
					n.kontrsymbol AS Kontrahent_Symbol,
					n.Kontrnip AS Kontrahent_nip,
					_VarStr(Substring(K.nazwa, 1, 150)) AS Platnik, 
					K.symbol AS Platnik_Symbol,
					K.nip AS Platnik_nip,
					CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta) AS Platnik_adres,
					NN.nrdokfin AS nrkorekty 
				FROM opng n 
				LEFT JOIN `opng_vat` ov ON ov.IdnOper = n.GUID 
				LEFT JOIN opng NN on NN.GUID = n.korektaGUID 
				LEFT JOIN oppo OP on OP.idnoper = n.guid 
				LEFT JOIN TowrNg T on OP.idntowr = T.guid 
				INNER JOIN operdoc D on D.rodzaj = n.rodzaj 
				LEFT JOIN kontrah K ON n.platnikGUID=K.guid
				WHERE
					(n.Deleted = 0 AND #n.tymczasowa = 0#)
					AND n.RV_Id = #RV_Id AND
					#WHERE
					
				GROUP BY n.GUID
				ORDER BY n.data_vat, n.nrdokfin 
			;
		';
		END IF;
		
		SET _SQLText =	REPLACE(_SQLText, '#n.tymczasowa = 0#', IF(@session.globals.Owner = 'KONKOL', 'TRUE', 'n.tymczasowa = 0'));
		SET _SQLText =	REPLACE(_SQLText, '###1', @RV.field.part);
		SET _SQLText =	REPLACE(_SQLText, '#RV_Id', _Key);
		SET _SQLText =	REPLACE(_SQLText, '#WHERE', IF(_Param_A != '', _Param_A, 'TRUE'));
		SET @_SQLText =	_SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
		
		SET _SQLText =
		'
		UPDATE `t_RV`
		SET \n###2
		;
		';
		IF _Param_B != '' THEN	-- ASEKURACJA
			SET _SQLText =	REPLACE(_SQLText, '###2', @RV.field.part_update);
			SET @_SQLText2 =	_SQLText;
			CALL _ExecuteCmd(_SQLText, @Error);
		END IF;
		
	END IF;	--	IF _MODE = 'RV.prepare.sql.part'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'RV.select.print.columns'
	Pobiera domy�lna warto�� stawki vat - dla nowej karty towarowej
		Parametry
			_Key		RV_Id
			_Param_A	FILTR
			_Param_B	<> '' to ASEKURACJA
	CALL erp_vat_tools('RV.select.print.columns', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
	CALL erp_vat_tools('RV.select.print.columns', '2021', 'n.DataWystaw > "2009-03-01"', '1', '', '');	-- ASEKURACJA
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE = 'RV.select.print.columns') THEN
		CALL erp_vat_tools('RV.prepare.sql.part', _Key, _Param_A, _Param_B, '', '');
		SELECT * FROM `t_RV`
		ORDER BY data_vat
		;
	END IF;	--	IF _MODE = 'RV.select.print.columns'
	
/*----------------------------------------------------------------------------------------------------------------------------
	'RV.print.clear'
	Czy�ci sytuacje czyli @session.vat.RV.print.RV_Id wolane np. przy zamykaniu okna rejestrow
	Powoduje to �e gdy ponownie wywolamy (F5) tean sam zestaw parametru i filtru to system i tak na nowo buduje tempy i �wie�y prtzez to zestaw danych.
	Gdyby nie robic tego to po powrocie (np. po 2h) system dal by ten sam zestaw co 2h temu (absurd)
		Parametry
	CALL erp_vat_tools('RV.print.clear', '', '', '', '', '');
----------------------------------------------------------------------------------------------------------------------------*/
	IF (_MODE = 'RV.print.clear') THEN
		SET @session.vat.RV.print.RV_Id = NULL;
		SET @session.vat.RV.print.Filter = NULL;
	END IF;	--	IF _MODE = 'RV.print.clear'
	
	
/*-----------------------------------------------------------------------------------------------------------------------------
	'RV.print.prepare temtable'
	Przygotowuje tabele tymczasowe Master i detal
		Parametry
			_Key		RV_Id
			_Param_A	FILTR
			_Param_B	<> '' to ASEKURACJA
	CALL erp_vat_tools('RV.print.prepare temtable', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
-----------------------------------------------------------------------------------------------------------------------------*/
	IF (_MODE = 'RV.print.prepare temtable') THEN
		SET @session.vat.RV.print.NeedPrepare = (@session.vat.RV.print.RV_Id IS NULL) OR (@session.vat.RV.print.RV_Id != _Key);
		IF NOT @session.vat.RV.print.NeedPrepare THEN
			SET @session.vat.RV.print.NeedPrepare = (@session.vat.RV.print.Filter IS NULL) OR (@session.vat.RV.print.Filter != _Param_A);
		END IF;
		SET
			@session.vat.RV.print.RV_Id = _Key,
			@session.vat.RV.print.Filter = _Param_A
		;
		IF NOT @session.vat.RV.print.NeedPrepare THEN
  			LEAVE SPROC;	--	NIC sie nie zmienilo zatem wychodzimy
		END IF;
		
		CALL _IReg_Get('', 'RejestrVatPokazZerowePoz', 0, 'BOOLEAN', @session.HT.IR.RejestrVatPokazZerowePoz, TRUE);

		SET @platnik = 0;
		If @session.globals.Owner IN ('MARPOL') then 
			set @platnik = 1;
		end if;
		
		DROP TEMPORARY TABLE IF EXISTS `t_vat_rv_ng`;		
		SET _SQLText = IF(@session.globals.Owner = 'AMICUS',
		'
		CREATE TEMPORARY TABLE `t_vat_rv_ng`
			SELECT
				n.GUID, n.RV_Id, n.grupaoper, n.nrwewn, n.nrdokfin, n.rodzaj, 
				n.datawplywu, n.datasprzed, n.datawystaw, n.data_vat, n.datarozliczenia, 
				n.brutto, n.NrFvWewn,
				IF(#RV_BezOdl, n.netto + n.vat_bez_odliczenia_paliwo + n.vat_bez_odliczenia_pojazd - n.netto_dla_vat_bez_odliczenia, n.netto - n.netto_dla_vat_bez_odliczenia) as netto,
				n.netto - n.netto_dla_vat_bez_odliczenia AS netto_2,
				n.vat, 
				n.vat_bez_odliczenia_paliwo, n.vat_bez_odliczenia_pojazd, 
				-- _VarDouble(_Rnd2(n.vat - (vat_bez_odliczenia_paliwo+vat_bez_odliczenia_pojazd))) AS vat_podleg_odlicz,
				#vat_odl# AS vat_podleg_odlicz,
				n.statusEDI,
				n.bezodliczen,
				_VarStr(Substring(n.KontrNazwa, 1, 150)) AS Kontrahent, 
				_VarStr(Substring(n.KontrAdres, 1, 150)) AS Kontrahent_Adres, 
				n.kontrsymbol AS Kontrahent_Symbol,
				n.Kontrnip AS Kontrahent_nip,
				_VarStr(Substring(K.nazwa, 1, 150)) AS Platnik, 
				K.symbol AS Platnik_Symbol,
				K.nip AS Platnik_nip,
				CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta) AS Platnik_adres,
				_VarStr(IFNULL(IF(n.korektaGUID != "", (SELECT NN.nrdokfin FROM opng NN WHERE NN.GUID = n.korektaGUID), ""),"")) AS nrkorekty, 
				YEAR(n.data_vat) AS data_vat_Rok,
				YEAR(n.data_vat)*100 + MONTH(n.data_vat) AS data_vat_Ms,
				YEAR(n.data_vat)*100 + QUARTER(n.data_vat) AS data_vat_Kw,
				YEAR(n.data_vat)*100 + WEEK(n.data_vat) AS data_vat_Ty,
				DAYOFWEEK(n.data_vat) AS data_vat_DzienTy,
				((SELECT COUNT(*) FROM `opng_vat` ov WHERE ov.IdnOper = n.GUID ) != 0) AS IsValid
			FROM opng n 
			LEFT JOIN `kontrah` K ON n.platnikGUID=K.guid
			INNER JOIN `operdoc` D ON D.rodzaj = n.rodzaj 
			WHERE
				(n.Deleted = 0 AND #n.tymczasowa = 0#)
				AND n.RV_Id = #RV_Id AND
				#WHERE
				
			GROUP BY n.GUID
			ORDER BY n.data_vat, n.nrdokfin 
		;
		'
		,IF(@session.HT.IR.RejestrVatPokazZerowePoz = TRUE,
		'
		CREATE TEMPORARY TABLE `t_vat_rv_ng`
			SELECT
				n.GUID, n.RV_Id, n.grupaoper, n.nrwewn, n.nrdokfin, n.rodzaj, 
				n.datawplywu, n.datasprzed, n.datawystaw, n.data_vat, n.datarozliczenia, 
				n.brutto, n.NrFvWewn,
				IF(#RV_BezOdl, n.netto + n.vat_bez_odliczenia_paliwo +  n.bezodliczen + n.vat_bez_odliczenia_pojazd - n.netto_dla_vat_bez_odliczenia, n.netto - n.netto_dla_vat_bez_odliczenia) as netto,
				n.netto - n.netto_dla_vat_bez_odliczenia AS netto_2,
				n.netto_dla_vat_bez_odliczenia,
				n.vat, 
				n.vat_bez_odliczenia_paliwo, n.vat_bez_odliczenia_pojazd, 
				#vat_odl# AS vat_podleg_odlicz,
				n.statusEDI,
				n.bezodliczen,
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN _VarStr(Substring(K.nazwa, 1, 150))
				WHEN @platnik = 1 THEN (SELECT nazwa FROM kontrah WHERE guid = n.platnikGUID)
				ELSE _VarStr(Substring(n.KontrNazwa, 1, 150)) 
				END AS Kontrahent, 
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI"
					THEN CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta)
				WHEN @platnik = 1 THEN (SELECT CONCAT(ulica,", ",zipcode," ",nazwamiasta) FROM kontrah WHERE guid = n.platnikGUID)
				ELSE _VarStr(Substring(n.KontrAdres, 1, 150)) 
				END AS Kontrahent_Adres, 
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN K.symbol
				WHEN @platnik = 1 THEN (SELECT symbol FROM kontrah WHERE guid = n.platnikGUID)
				ELSE n.kontrsymbol 
				END AS Kontrahent_Symbol,
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN K.nip
				WHEN @platnik = 1 THEN (SELECT nip FROM kontrah WHERE guid = n.platnikGUID)
				ELSE n.Kontrnip 
				END AS Kontrahent_nip,
				_VarStr(Substring(K.nazwa, 1, 150)) AS Platnik, 
				K.symbol AS Platnik_Symbol,
				K.nip AS Platnik_nip,
				CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta) AS Platnik_adres,
				_VarStr(IFNULL(IF(n.korektaGUID != "", (SELECT NN.nrdokfin FROM opng NN WHERE NN.GUID = n.korektaGUID), ""),"")) AS nrkorekty, 
				YEAR(n.data_vat) AS data_vat_Rok,
				YEAR(n.data_vat)*100 + MONTH(n.data_vat) AS data_vat_Ms,
				YEAR(n.data_vat)*100 + QUARTER(n.data_vat) AS data_vat_Kw,
				YEAR(n.data_vat)*100 + WEEK(n.data_vat) AS data_vat_Ty,
				DAYOFWEEK(n.data_vat) AS data_vat_DzienTy,
				_VarInt(1) AS IsValid
			FROM opng n 
			LEFT JOIN kontrah K ON n.platnikGUID=K.guid
			INNER JOIN operdoc D ON D.rodzaj = n.rodzaj 
			
			WHERE
				(n.Deleted = 0 AND #n.tymczasowa = 0#)
				AND n.RV_Id = #RV_Id AND
				#WHERE
				
			GROUP BY n.GUID
			ORDER BY n.data_vat, n.nrdokfin 
		;
		',
		'
		CREATE TEMPORARY TABLE `t_vat_rv_ng`
			SELECT
				n.GUID, n.RV_Id, n.grupaoper, n.nrwewn, n.nrdokfin, n.rodzaj, 
				n.datawplywu, n.datasprzed, n.datawystaw, n.data_vat, n.datarozliczenia, 
				n.brutto, n.NrFvWewn,
				IF(#RV_BezOdl, n.netto + n.vat_bez_odliczenia_paliwo +  n.bezodliczen + n.vat_bez_odliczenia_pojazd - n.netto_dla_vat_bez_odliczenia, n.netto - n.netto_dla_vat_bez_odliczenia) as netto,
				n.netto - n.netto_dla_vat_bez_odliczenia AS netto_2,
				n.netto_dla_vat_bez_odliczenia,
				n.vat, 
				n.vat_bez_odliczenia_paliwo, n.vat_bez_odliczenia_pojazd, 
				-- _VarDouble(_Rnd2(n.vat - (vat_bez_odliczenia_paliwo+vat_bez_odliczenia_pojazd))) AS vat_podleg_odlicz,
				#vat_odl# AS vat_podleg_odlicz,
				n.statusEDI,
				n.bezodliczen,
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN _VarStr(Substring(K.nazwa, 1, 150))
				WHEN @platnik = 1 THEN (SELECT nazwa FROM kontrah WHERE guid = n.platnikGUID)
				ELSE _VarStr(Substring(n.KontrNazwa, 1, 150)) 
				END AS Kontrahent, 
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta)
				WHEN @platnik = 1 THEN (SELECT CONCAT(ulica,", ",zipcode," ",nazwamiasta) FROM kontrah WHERE guid = n.platnikGUID)
				ELSE _VarStr(Substring(n.KontrAdres, 1, 150)) 
				END AS Kontrahent_Adres, 
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN K.symbol
				WHEN @platnik = 1 THEN (SELECT symbol FROM kontrah WHERE guid = n.platnikGUID)
				ELSE n.kontrsymbol 
				END AS Kontrahent_Symbol,
				CASE WHEN @session.globals.Owner IN ("IZOHAN") AND n.rodzaj IN (1,3,2,4,105,0,69,92,50,80,29,30,31,32) AND K.symbol <> "JEDNORAZOWI POZOSTALI" 
					THEN K.nip
				WHEN @platnik = 1 THEN (SELECT nip FROM kontrah WHERE guid = n.platnikGUID)
				ELSE n.Kontrnip 
				END AS Kontrahent_nip,
				_VarStr(Substring(K.nazwa, 1, 150)) AS Platnik, 
				K.symbol AS Platnik_Symbol,
				K.nip AS Platnik_nip,
				CONCAT(K.ulica,", ",K.zipcode,", ",K.NazwaMiasta) AS Platnik_adres,
				_VarStr(IFNULL(IF(n.korektaGUID != "", (SELECT NN.nrdokfin FROM opng NN WHERE NN.GUID = n.korektaGUID), ""),"")) AS nrkorekty, 
				YEAR(n.data_vat) AS data_vat_Rok,
				YEAR(n.data_vat)*100 + MONTH(n.data_vat) AS data_vat_Ms,
				YEAR(n.data_vat)*100 + QUARTER(n.data_vat) AS data_vat_Kw,
				YEAR(n.data_vat)*100 + WEEK(n.data_vat) AS data_vat_Ty,
				DAYOFWEEK(n.data_vat) AS data_vat_DzienTy,
				((SELECT COUNT(*) FROM `opng_vat` ov WHERE ov.IdnOper = n.GUID ) != 0) AS IsValid
			FROM opng n 
			LEFT JOIN `kontrah` K ON n.platnikGUID=K.guid
			INNER JOIN `operdoc` D ON D.rodzaj = n.rodzaj 
			
			WHERE
				(n.Deleted = 0 AND (#n.tymczasowa = 0# OR (@session.globals.Owner = "KONKOL" AND n.rodzaj = 3)))
				AND n.RV_Id = #RV_Id AND
				#WHERE
				
			GROUP BY n.GUID
			ORDER BY n.data_vat, n.nrdokfin 
		;
		'));

		SET _SQLText =	REPLACE(_SQLText, '#n.tymczasowa = 0#', IF(@session.globals.Owner = 'KONKOL', 'TRUE', 'n.tymczasowa = 0'));
		SET _SQLText =	REPLACE(_SQLText, '#RV_BezOdl', IFNULL(@session.HT.IR.RV_DodajBezOdliczenDoNetto,0));
		SET _SQLText =	REPLACE(_SQLText, '#RV_Id', IFNULL(@session.vat.RV.print.RV_Id,0));
		SET _SQLText =	REPLACE(_SQLText, '#WHERE', IF(@session.vat.RV.print.Filter != '', @session.vat.RV.print.Filter, 'TRUE'));


		IF @session.globals.Owner = 'SKOK' THEN 
		SET _SQLText =	REPLACE(_SQLText, '#vat_odl#', 
			 'IF(data_vat < "2019-11-01",_VarDouble(_Rnd2(vat_bez_odliczenia_paliwo + vat_bez_odliczenia_pojazd)),_VarDouble(_Rnd2(n.vat - (vat_bez_odliczenia_paliwo + vat_bez_odliczenia_pojazd))))');	
		ELSE 
			SET _SQLText =	REPLACE(_SQLText, '#vat_odl#', '_VarDouble(_Rnd2(n.vat - (vat_bez_odliczenia_paliwo + vat_bez_odliczenia_pojazd)))');
		END IF;


		SET @_SQLText =	_SQLText;
		CALL _ExecuteCmd(_SQLText, @Error);
		ALTER TABLE `t_vat_rv_ng`
			ADD INDEX GUID(GUID)
		;	
		
		DROP TEMPORARY TABLE IF EXISTS `t_vat_rv_po`;
		SET @.current.IdnOper := '';
		CREATE TEMPORARY TABLE `t_vat_rv_po`
			SELECT
--					IF(@.current.IdnOper != ov.IdnOper, n.vat_podleg_odlicz, 0) AS vat_podleg_odlicz,
--	zdarzaj� si� przypadki ze ov.idnoper jest null - w takim przypadku zerowana jest 1 pozycja do spr
					IF(!(@.current.IdnOper <=> ov.IdnOper), n.vat_podleg_odlicz, 0) AS vat_podleg_odlicz,
					(@.current.IdnOper := ov.IdnOper) AS IdnOper,
					v.Id AS IdVat,
					v.name AS Vat_Name, v.ShortName AS Vat_ShortName, v.value AS Vat_Value, 
					-- bylo _VarDouble(IF(NOT IFNULL(@session.HT.IR.RV_DodajBezOdliczenDoNetto,0) AND (ov.IdVat = 18), 0, ov.Netto)) AS Netto, 	-- dla bez odliczen koniecznie pokazuj 0 w netto
					-- _VarDouble(IF(NOT IFNULL(@session.HT.IR.RV_DodajBezOdliczenDoNetto,0) AND (ov.IdVat = 18), 0, IFNULL(ov.Netto, 0))) AS Netto, 	-- dla bez odliczen koniecznie pokazuj 0 w netto

					_VarDouble(IF(NOT IFNULL(@session.HT.IR.RV_DodajBezOdliczenDoNetto,0) AND (ov.IdVat = 18), 0, IFNULL(ov.Netto - n.netto_dla_vat_bez_odliczenia, 0))) AS Netto,

					IFNULL(ov.Vat, 0) AS Vat, 
					IFNULL(ov.Brutto, 0) AS Brutto,
					_VarDouble(0) AS Netto_C, _VarDouble(0) AS Vat_C, 
					_VarDouble(0) AS Netto_N, _VarDouble(0) AS Vat_N, 
					ov.NettoWal, ov.VatWal, ov.BruttoWal,
					_VarInt(IFNULL(ov.OperCount, 0)) AS OperCount,
					CONCAT(v.name, ' ', IF(ov.OperCount = 0, '', CONCAT('(', ov.OperCount, ' oper.)'))) AS IdnVarStr,
					v.IdOrder
			FROM `t_vat_rv_ng` n
			LEFT JOIN `opng_vat` ov ON ov.IdnOper = n.GUID 
			LEFT JOIN `v_erp_vat` v ON v.ID = ov.IdVat
			having not (netto = 0 and vat = 0 and brutto = 0)
			ORDER BY ov.IdnOper, v.IdOrder
		;
		UPDATE `t_vat_rv_ng` n
		SET 
			n.netto_2 = n.netto,
			n.netto = (
				SELECT
					SUM(p.Netto) AS netto_2
				FROM `t_vat_rv_po` p
				WHERE
					p.IdnOper = n.GUID
			)
		;	
		ALTER TABLE `t_vat_rv_po`
			ADD INDEX IdnOper(IdnOper)
		;	
		IF _Param_B != '' THEN 	-- ASEKURACJA
			DROP TEMPORARY TABLE IF EXISTS `t_vat_rv_po_work`;
			CREATE TEMPORARY TABLE `t_vat_rv_po_work`
				SELECT
					rpv.IdnOper, rpv.IdVat,
					SUM(IF(t.wyroznik IN ('ZC','SAM ZC'), OP.wartnet, 0)) AS Netto_C,
					SUM(IF(t.wyroznik IN ('ZC','SAM ZC'), OP.wartvat, 0)) AS Vat_C,
					SUM(IF(t.wyroznik IN ('ZN','SAM ZN'), OP.wartnet, 0)) AS Netto_N,
					SUM(IF(t.wyroznik IN ('ZN','SAM ZN'), OP.wartvat, 0)) AS Vat_N
				FROM `t_vat_rv_po` rpv
				LEFT JOIN `oppo` OP ON OP.IdnOper = rpv.IdnOper AND OP.IdnVat = rpv.IdVat
				LEFT JOIN `TowrNg` t ON t.GUID = OP.IdnTowr
				GROUP BY rpv.IdnOper, rpv.IdVat
			;
			
			UPDATE `t_vat_rv_po` rpv, `t_vat_rv_po_work` rpvwork
			SET
				rpv.Netto_C = rpvwork.Netto_C,
				rpv.Vat_C = rpvwork.Vat_C,
				rpv.Netto_N = rpvwork.Netto_N,
				rpv.Vat_N = rpvwork.Vat_N
			WHERE
				rpv.IdnOper = rpvwork.IdnOper AND
				rpv.IdVat = rpvwork.IdVat 
			;
			UPDATE `t_vat_rv_po` rpv
			SET
				Netto = Netto - Netto_C - Netto_N,
				Vat = Vat - Vat_C - Vat_N
			;
			
		END IF;
	END IF;	--	IF _MODE = 'RV.print.prepare temtable'
	
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	' '
	Pobiera domy�lna warto�� stawki vat - dla nowej karty towarowej
		Parametry
			_Key		RV_Id
			_Param_A	FILTR
			_Param_B	<> '' to ASEKURACJA
    CALL erp_vat_tools('RV.print.select.n', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
    CALL erp_vat_tools('RV.print.select.p', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
    CALL erp_vat_tools('RV.print.select.np', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
    CALL erp_vat_tools('RV.print.select.np.footer', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
    CALL erp_vat_tools('RV.print.select.n', '2050', "((if(n.data_vat='0000-00-00','2999-01-01', n.data_vat) between '2024-08-08' and '2024-08-08'))", "1", '', '');
    CALL erp_vat_tools('RV.print.select.p', '2050', "((if(n.data_vat='0000-00-00','2999-01-01', n.data_vat) between '2024-08-08' and '2024-08-08'))", "1", '', '');
  i uwaga na wywolania tworzenia tabel tymczasowych - sa blokowane gdy powtarzamy takie samo wywolanie!
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
	IF (_MODE LIKE 'RV.print.select%') THEN
		IF (_MODE != 'RV.print.select.np.footer') THEN
			CALL erp_vat_tools('RV.print.prepare temtable', _Key, _Param_A, _Param_B, '', '');
		END IF;
		IF (_MODE = 'RV.print.select.n') THEN
			SELECT
				n.*
			FROM `t_vat_rv_ng` n
			WHERE
				n.IsValid 
			;
		END IF;
		IF (_MODE = 'RV.print.select.p') THEN
			SELECT
				p.*
			FROM `t_vat_rv_po` p
			;
		END IF;
		
		IF (_MODE = 'RV.print.select.np') THEN
			SELECT
				n.GUID, n.RV_Id, n.grupaoper, n.nrwewn, n.nrdokfin, n.rodzaj, 
				n.datawplywu, n.datasprzed, n.datawystaw, n.data_vat, n.datarozliczenia, 
				n.brutto AS n_brutto, n.vat AS n_vat, n.netto AS n_netto,
				n.netto_dla_vat_bez_odliczenia,
				n.vat_bez_odliczenia_paliwo, n.vat_bez_odliczenia_pojazd, 
				_VarDouble(p.vat_podleg_odlicz) as vat_podleg_odlicz,
				n.vat_podleg_odlicz AS n_vat_podleg_odlicz,
				n.bezodliczen, 
				n.Kontrahent, 
				n.Kontrahent_Adres, 
				n.Kontrahent_Symbol,
				n.Kontrahent_nip,
				n.Platnik, 
				n.Platnik_Symbol,
				n.Platnik_nip, 
				n.Platnik_adres, 
				n.nrkorekty,
				n.statusEDI,
				p.IdnOper,
				p.IdVat,
				p.Vat_Name, p.Vat_ShortName, p.Vat_Value, 
				IFNULL(p.Netto, n.netto) AS p_Netto, 
				IFNULL(p.Vat, n.Vat) AS p_Vat, 
				IFNULL(p.Brutto, n.Brutto) AS p_Brutto,
				p.NettoWal, p.VatWal, p.BruttoWal,
				p.Netto_C, p.Vat_C, 
				p.Netto_N, p.Vat_N, 
				p.OperCount,
				p.IdnVarStr,
				p.IdOrder,
				n.NrFvWewn
			FROM `t_vat_rv_ng` n
			LEFT JOIN `t_vat_rv_po` p ON p.IdnOper = n.GUID
			WHERE
				n.IsValid 
			ORDER BY GUID, vat_podleg_odlicz DESC
			;
		END IF;
		IF (_MODE = 'RV.print.select.np.footer') THEN
			SELECT
				p.Vat_Name, 
				CONCAT(p.Vat_ShortName, ' (',COUNT(*), '.poz.)') AS IdnVarStr,
				_VarDouble(SUM(n.vat_bez_odliczenia_paliwo)) AS vat_bez_odliczenia_paliwo, _VarDouble(SUM(n.vat_bez_odliczenia_pojazd)) AS vat_bez_odliczenia_pojazd, 
				_VarDouble(SUM(n.bezodliczen)) AS bezodliczen, 
				_VarDouble(SUM(p.Netto)) AS p_Netto, _VarDouble(SUM(p.Vat)) AS p_Vat, _VarDouble(SUM(p.Brutto)) AS p_Brutto,
				_VarDouble(SUM(p.Netto_C)) AS Netto_C, _VarDouble(SUM(p.Vat_C)) AS Vat_C, 
				_VarDouble(SUM(p.Netto_N)) AS Netto_N, _VarDouble(SUM(p.Vat_N)) AS Vat_N, 
				_VarDouble(SUM(p.NettoWal)) AS NettoWal, _VarDouble(SUM(p.VatWal)) AS VatWal, _VarDouble(SUM(p.BruttoWal)) AS BruttoWal
		
			FROM `t_vat_rv_ng` n
			LEFT JOIN `t_vat_rv_po` p ON p.IdnOper = n.GUID
			GROUP BY p.IdVat
			;
		END IF;
	END IF;	--	IF _MODE = 'RV.select.print'
	
	
	
	
	
	
	
END$$

DELIMITER ;

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

UPDATE erp_vat SET IdOrder = CONCAT('10', 10*ID);
UPDATE erp_vat SET work_Field_Prefix = CONCAT('v_', ROUND(Value*100,0), '_');

DESC erp_vat;
SELECT * FROM erp_vat;
DESC oper_vat;
SELECT * FROM oper_vat;

select n.GUID, n.* from opng n order by n.idn desc limit 20;

select n.GUID, n.netto, n.vat, n.brutto, n.* 
from opng n 
WHERE
n.GUID = '{9A1ABC61-38DA-4C3A-8E69-94106AD6242A}'
;
CALL erp_vat_tools('Update.oper', '{9A1ABC61-38DA-4C3A-8E69-94106AD6242A}', '', '', '', '');
CALL erp_vat_tools('oper.vattable.select', '{9A1ABC61-38DA-4C3A-8E69-94106AD6242A}', '', '', '', '');
CALL erp_vat_tools('oper.vattable.select', '{9A1ABC61-38DA-4C3A-8E69-94106AD6242A}', 'ALL', '', '', '');

CALL erp_vat_tools('Update.oper.all', '', '', '', '', '');
select * from `opng_vat`;



SELECT * FROM v_erp_vat;
SHOW CREATE TABLE erp_vat;
CREATE TABLE `erp_vat` (
  `ID` int(11) NOT NULL COMMENT 'Identyfikator',
  `name` varchar(38) DEFAULT NULL COMMENT 'Nazwa',
  `shortname` varchar(20) DEFAULT NULL COMMENT 'Nazwa skrocona',
  `value` double(16,4) DEFAULT NULL COMMENT 'Wartosc',
  `calculated` int(11) NOT NULL DEFAULT '1' COMMENT 'Kalkulacyjna stawka?',
  `visible` int(11) NOT NULL DEFAULT '1' COMMENT 'Czy widoczna, False - nie jest do wyboru w aplikacji',
  `not_used` int(11) NOT NULL DEFAULT '0' COMMENT 'Czy uzywana, False - nie jest brana pod uwage',
  `country_code` varchar(3) NOT NULL DEFAULT 'PL' COMMENT 'Kraj',
  `IDFiskalny` varchar(3) NOT NULL DEFAULT '' COMMENT 'Onaczenie dla urz.fiskalnych',
  `IsDefault` int(11) NOT NULL DEFAULT '0' COMMENT '1 - jest domyslna stawka dla nowej karty towarowej',
  `work_FieldName` varchar(50) NOT NULL DEFAULT '' COMMENT 'Techniczne - nazwa pola tabwart czyli 0 1 2 ...',
  `IdOrder` varchar(20) NOT NULL DEFAULT '' COMMENT 'Pole do sortowania',
  PRIMARY KEY (`ID`,`country_code`),
  KEY `ID` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250
;

SHOW CREATE TABLE opng_vat;

DROP TABLE `opng_vat`;
CREATE TABLE `opng_vat` (
  `IdnOper` varchar(38) NOT NULL DEFAULT '' COMMENT 'GUID operacji',
  `IdVat` int(11) NOT NULL DEFAULT '0' COMMENT 'ID stawki VAT',
  `Netto` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Netto',
  `Vat` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Vat',
  `Brutto` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Brutto',
  `NettoWal` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Netto w walucie',
  `VatWal` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Vat w walucie',
  `BruttoWal` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Brutto w walucie',
  `OperCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Ile operacji',
  PRIMARY KEY (`IdnOper`,`IdVat`),
  KEY `IdnOper` (`IdnOper`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250
;
DROP TABLE `zmng_vat`;
CREATE TABLE `zmng_vat` (
  `IdnOper` varchar(38) NOT NULL DEFAULT '' COMMENT 'GUID operacji',
  `IdVat` int(11) NOT NULL DEFAULT '0' COMMENT 'ID stawki VAT',
  `Netto` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Netto',
  `Vat` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Vat',
  `Brutto` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Brutto',
  `NettoWal` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Netto w walucie',
  `VatWal` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Vat w walucie',
  `BruttoWal` double(16,4) NOT NULL DEFAULT '0.0000' COMMENT 'Wart. Brutto w walucie',
  `OperCount` int(11) NOT NULL DEFAULT '0' COMMENT 'Ile operacji',
  PRIMARY KEY (`IdnOper`,`IdVat`),
  KEY `IdnOper` (`IdnOper`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250
;

CALL erp_vat_tools('RV.print.select.n', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
CALL erp_vat_tools('RV.print.select.p', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');
CALL erp_vat_tools('RV.print.select.np', '2021', 'n.DataWystaw > "2009-03-01"', '', '', '');

CALL erp_vat_tools('RV.print.select.n', '2021', 'n.DataWystaw > "2009-03-01"', '1', '', '');
CALL erp_vat_tools('RV.print.select.p', '2021', 'n.DataWystaw > "2009-03-01"', '1', '', '');
CALL erp_vat_tools('RV.print.select.np', '2021', 'n.DataWystaw > "2009-03-01"', '1', '', '');

CALL erp_vat_tools('RV.print.select.n', '2021', "(((n.datawystaw >= '2010-10-01') and (n.datawystaw <= '2010-10-31')))", "", '', '') ;
CALL erp_vat_tools('RV.print.select.p', '2021', "(((n.datawystaw >= '2010-10-01') and (n.datawystaw <= '2010-10-31')))", "", '', '') ;
CALL erp_vat_tools('RV.print.select.np', '2021', "(((n.datawystaw >= '2010-08-01') and (n.datawystaw <= '2010-11-31')))", "", '', '') ;
CALL erp_vat_tools('RV.print.select.np.footer', '2021', "(((n.datawystaw >= '2010-08-01') and (n.datawystaw <= '2010-11-31')))", "", '', '') ;

CALL erp_vat_tools('RV.print.select.np', '2021', "(((n.datawystaw >= '2010-11-02') and (n.datawystaw <= '2010-11-30')))", "", '', '') ;
CALL erp_vat_tools('RV.print.select.np.footer', '', '', '', '', '');

CALL erp_vat_tools('RV.prepare.sql.part', '2032', "(((n.data_vat >= '2010-11-01') and (n.data_vat <= '2010-11-30')))", '', '', '');
CALL erp_vat_tools('RV.select.print.columns', '2032', "(((n.data_vat >= '2010-11-01') and (n.data_vat <= '2010-11-30')))", '', '', '');
CALL erp_vat_tools('RV.select.print.columns', '2021', "(((n.datawystaw >= '2010-11-02') and (n.datawystaw <= '2010-11-30')))", "", '', '');

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
DROP TABLE `erp_vat_dev`;
CREATE TABLE `erp_vat_dev` (
  `IdVat` int(11) NOT NULL DEFAULT '0' COMMENT 'Stawka VAT',
  `DevSymbol` varchar(38) NOT NULL DEFAULT '' COMMENT 'Symbol urzadzenia np. POSNET, ELZAB,...',
  `Symbol` VARCHAR(20) DEFAULT '' COMMENT 'Znacznik np. A B C ...',
  `ValueDescr` varchar(20) DEFAULT '' COMMENT 'Wartosc (informacyjnie)',
  PRIMARY KEY (`IdVat`,`DevSymbol`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1250
;

W Delphi jest tak......

       Cstawka22  = 0;       // A      #1
       Cstawka7   = 1;       // B      #2
       Cstawka0   = 2;       // C      #4
       CstawkaZw  = 3;       // Z      #5
       CStawka3   = 4;       // D      #3
       CStawkaNp  = 5;       // Z      #5
       CStawkaX   = 6;       // E      #5
       CStawkaY   = 7;       // F      #5
       COstStawka = 7;

       Cstawka5  = 8;       // -> CStawka3  D #3 dla konkola to jest 5 !!!! 3 nie jest uzywana chyba...
       Cstawka23  = 15;     // -> Cstawka22 A #1
       Cstawka8  = 16;      // -> Cstawka7  B #2
PATRZ
	http://www.posnet.com.pl/index.php?pid=1020&newsid=4091
	
	

CALL erp_vat_tools('erp_vat_dev.Recreate', '', '', '', '', '');	   
CALL erp_vat_tools('erp_vat_dev.Fill', '', '', '', '', '');
CALL erp_vat_tools('erp_vat_dev.Select', '', 'POSNET', '', '', '');	   


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



CALL ht_oppo_edit('edit.rec.refresh', @_Key, '', '', '');

CALL erp_vat_tools('t_edit_ng_vat.calc.from._e', @_Key, '', '', '', '');
SELECT * FROM `t_edit_ng_vat`
WHERE
	TRUE
	AND exist
;
SELECT * FROM `opng_vat` WHERE IdnOper = @_Key;

SET @za = '1';
SELECT @za = 1 AS OK;

DROP TEMPORARY TABLE IF EXISTS `t_edit_ng_vat`;
CALL erp_vat_tools('t_edit_ng_vat.Create', '', '', '', '', '');
SELECT * FROM `t_edit_ng_vat`;

*/

/*
$Log: erp_vat_tools.sql,v $
Revision 1.105  2025/02/26 10:37:07  jmam
$send$ Zmiany po TK

*/
