 /*!40101 SET NAMES utf8 */;
DELIMITER $$

DROP PROCEDURE IF EXISTS `fr_ht_fv_spec`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `fr_ht_fv_spec`(
	IN _Symbol VARCHAR(38),
	IN _GUID VARCHAR(38),
	IN _Param_A VARCHAR(50),
	IN _Param_B VARCHAR(50),
	IN _Param_C VARCHAR(50)
)
BEGIN
	# Procedura tworzaca rozne zalaczniki i dodatki do dokumentiow operacji
	# SPEC1 i SPEC2 - specyfikacje dla Domaxu
	# SPEC3 - podsumowanie opakowan dla tow. niebiezpiecznych (Amicus)
	#			grupowanie wg numeru UN, grupy pakowania, rodzaju opakowan - te dane sa zawarte w kategoriach kolumnowych
	#			do tego waga opakowan i ilosc opakowan
	# SPEC4 - CRIST - grupowanie pozycji dokumentu (nazwa towaru, kategoria kolumnowa) tak, zeby uzyskac jedno pole zamiast listy pozycji
	#		 - potrzebne do zalcznikow (umowy, gwarancje, protokol) do faktur typu RR
	# SPEC4S - CRIST - wersja nie korzytajaca z tabel opng i oppo, wydruki z kartoteki kontrahentow
	# SPEC5 - CDG - podsumowanie wagi towarow wg symboli dostawcow
	# SPEC6 - ATM - podsumowanie wartosci towarow i zaliczki(potrzebne dla faktur zagranicznych)
	# SPEC7 - BALTIMA - oswiadczenie zbiorcze do faktur z danego miesiaca dla danego kontrahenta
	# SPEC7B - BALTIMA - oswiadczenie do faktury, dane z zamowienia
	# SPEC8 - PBUCH - dodatkowa tabelka vat (zl i waluta) dla faktur koncowych po zaliczkach
	# SPEC9 - PBUCH - informacja o towarach z zamowienia
	# SPEC10 - PBUCH/QWANTUM - informacja o wczesniejszych (wszystkich) fakturach zaliczkowych
	# SPEC11 - PBUCH/QWANTUM - informacja o danych z zamowienia
# tu brakuje pozycji z procedury
	# SPECATM1 - informacje o info..info4 z TowrNg na bazie towarów z oppo
  # SPEC14 - IZOHAN (NEXLER) - informacje o paletach (potrzebne dodatkowe dane z TowrNg)
  # SPEC15 - KONKOL - wydobycie idn dokumentu do wydruku nalepki
	# SPEC16 - SIMEX - wydruk dokumentu IN (póki call fr_ht_towary  nie zacznie działać)
	#SPEC17- wydruk specyfikacji wz do FV
	#SPEC21- wydruk specyfikacji do FV Stanpol
-- SPEC10B, SPEC12B - dane dla innej sciezki rozliczen zaliczek, dla Koloru 

	DECLARE _GUID_zamowienia VARCHAR(38);
	DECLARE _GUID_POM VARCHAR(38);
	DECLARE _DATAWYSTAW, _data_dostawy DATE;

	DECLARE _kontrGUID VARCHAR(38);
	DECLARE _mc, _rok, _rodzaj INT;
	DECLARE _idn INT;
	DECLARE _uwagi TEXT;
	
	DECLARE _nr_transportu VARCHAR(100);

	SET @Symbol = UPPER(_Symbol);
	SET @GUID = _GUID;
	
	SET @Param_A = _Param_A;
	SET @Param_B = _Param_B;
	SET @Param_C = _Param_C;
	
	SET SESSION group_concat_max_len = 1024*1024*1024;


/* *******************************************************************************
	SPEC1 - specyfikacja - pierwszy dataset (pozycje faktury)
******************************************************************************* */
	IF _Symbol = 'SPEC1' THEN

	SELECT 
		
		t.pcn, 
		o.towrsymb,
		o.towrnazwa,
		o.ilosc,
		o.ilosc/o.ilewopakowaniu as opakowan,
		o.cenanetzRabWal as cena,
		o.wartnetwal as wartosc,
		o.waga/1000 as waga,
		t.opakowanie_zbiorcze_d  -- dodane dla DOS-a
		FROM oppo o
		LEFT JOIN TowrNg t ON t.GUID = o.idntowr
		inner JOIN opng n on (n.guid=o.idnoperfin or n.guid=o.idnoper)
		WHERE n.GUID = _GUID
		
		order by t.pcn; 


	END IF;
/*
*******************************************************************************
	SPEC2 - drugi dataset (grupowanie po PCN)
*******************************************************************************
*/

	IF _Symbol = 'SPEC2' THEN

	SELECT t.pcn, 
		sum(o.ilosc) as ilosc,
		sum(o.wartnetwal) as wartosc, 
		sum(o.waga)/1000 as waga, 
		sum(o.waga)*(n.waga_brutto/n.waga)/1000 as waga_brutto,
		n.waga/1000 as waga_netto_sum,
		n.waga_brutto/1000 as waga_brutto_sum
		FROM oppo o
		LEFT JOIN TowrNg t ON t.GUID = o.idntowr
		inner JOIN opng n on (n.guid=o.idnoperfin or n.guid=o.idnoper)
		WHERE n.GUID = _GUID
		group by t.pcn
		order by t.pcn; 

	END IF;

/*
*******************************************************************************
	SPEC3 - grupowanie po numerze UN, grupy pakowania i rodzaju opakowan
	
	CALL fr_ht_fv_spec('SPEC3','{7D31F2F9-12C3-4E70-A7AB-53C00E50D139}','','','');
*******************************************************************************
*/

	IF _Symbol = 'SPEC3' THEN

	SELECT
		KC0.Valuex as numerUN, KC2.Valuex as nalepka,
		KC3.Valuex as GrupaPakowania, 
		-- KC4.Valuex as opakowanie,
		KC9.Valuex as opakowanie,
		t.info2 AS info2,
		'w sztukach przesylek' as tekst1,
		sum(if(wyroznik='U',1,ceil(o.ilosc/o.ilewopakowaniu))) as opakowan,
		sum(o.waga + if(wyroznik='U',1,ceil(o.ilosc/o.ilewopakowaniu))*t.opak_waga_metal) as waga_brutto,
		sum(if(wyroznik='U',1,ceil(o.ilosc/o.ilewopakowaniu))*t.opak_waga_metal) as waga_opak,
		sum(o.waga) as waga_tow,
		sum(t.waga*o.ilosc) as waga_tow2,
		sum(o.ilosc*t.objetosc) as objetosc,
		'AD' as tekst2
		FROM oppo o
		inner JOIN TowrNg t ON t.GUID = o.idntowr
		inner JOIN opng n on (n.guid=o.idnoperfin or n.guid=o.idnoper)
		LEFT JOIN katcols KC0 ON o.kat5 = KC0.klucz AND KC0.colnr = 1 AND KC0.typ =1 
		LEFT JOIN katcols KC2 ON o.kat7 = KC2.klucz AND KC2.colnr = 3 AND KC2.typ =1 
		LEFT JOIN katcols KC3 ON o.kat8 = KC3.klucz AND KC3.colnr = 4 AND KC3.typ =1 
		LEFT JOIN katcols KC4 ON o.kat9 = KC4.klucz AND KC4.colnr = 5 AND KC4.typ =1 
		LEFT JOIN katcols KC9 ON o.kat9 = KC9.klucz AND KC9.colnr = 5 AND KC9.typ =1
		WHERE n.GUID = _GUID
		AND o.kat7<>0
		GROUP BY KC0.Valuex, KC2.Valuex, KC3.Valuex, KC9.Valuex,t.info2
		ORDER BY KC0.Valuex, KC2.Valuex, KC3.Valuex, KC9.Valuex,t.info2; 

	END IF;
	

/*
*******************************************************************************
	SPEC4 - grupowanie pozycji dokumentu w jedno ladne pole

	CALL fr_ht_fv_spec('SPEC4','{CD8E9E94-1A8D-4CE0-BE67-CB2CBA19EDE6}','','','')
*******************************************************************************
*/

	IF _Symbol = 'SPEC4' THEN

	SELECT
		'fr_ht_fv_spec__SPEC4' as fr_ht_fv_spec__SPEC4,
		group_concat(concat(towrnazwa,'\n','Zlecenie: ',ifnull(KC2.Valuex,'')) SEPARATOR '\n')  AS pozycje,
		if(ifnull(k.zatrudnienie,'')='T','T','') as zatrudnienie,
		k.data_waznosci_umowy,
		-- DATE_ADD(k.data_waznosci_umowy, INTERVAL 1 YEAR) as data_waznosci_umowy2, -- dopisek dla IRKO - umowa z definicji na rok
		-- ifnull(k.data_waznosci_umowy_do,'2015-12-31') as data_waznosci_umowy2,
		ifnull(k.data_waznosci_umowy_do,'czas nieokreślony') as data_waznosci_umowy2,
		CASE 
			WHEN @session.globals.Owner IN ('CRIST','SHIPYARD','OFFSHORE','KADRY') THEN '2020-01-02'
			ELSE current_date() 
		END as data_aneksu,
		k.samofakturowanie,
		k.odbierajacy as kontrodbierajacy,
		CONCAT(LEFT(SUBSTRING_INDEX(odp_za_zam,' ',-2),1),
			LEFT(SUBSTRING_INDEX(odp_za_zam,' ',-1),1)) as odp_za_zam, -- sa to teraz inicjaly osoby, dla sa i ship
		k.odp_za_zam as odp_za_zam2, -- pelne pole
		k.symbol as kontrsymbol,
		k.nazwa as kontrnazwa,
		k.nip as kontrnip,
		k.regon as kontrREGON,
		k.krs,
		k.vat as platnik_vat,
		CONCAT(k.zipcode,' ',k.nazwamiasta,', ',k.ulica) AS adres,
		(select NazwaKonta from rachbankowe where kontrGUID=k.GUID order by idn desc limit 1) as kontr_bank_nazwa,
		(select NrRachunku from rachbankowe where kontrGUID=k.GUID order by idn desc limit 1) as kontr_bank_numer,
		ifnull(k.opis_dzialalnosci,'') as opis_dzialalnosci,
		(select adres from kontakty where kontrguid = k.GUID AND typ_medium = 3 AND opis = 'E-Faktura' LIMIT 1) AS kontremail,
		
		case when (SELECT valuea FROM IReg WHERE idkey ='nip' AND idpath LIKE '%owner' LIMIT 1) = '9571087826' then 'IRKO OFFSHORE'
		ELSE
			(SELECT `value` FROM _db_globals WHERE `key`='owner') 
		END as nfirma,
		_Param_A as parametr,
		Month(n.datasprzed) as mc,
		_Rnd2(n.ryczalt_stawka) as godziny,
		if(n.ryczalt_stawka=0,0,_Rnd2(n.netto/n.ryczalt_stawka)) as stawka_godz
		FROM oppo o
		inner join opng n on (n.guid=o.idnoperfin or n.guid=o.idnoper)
		inner join kontrah k on k.GUID=n.kontrGUID
		-- LEFT JOIN katcols KC2 ON o.kat2 = KC2.klucz and KC2.colnr = 3 and KC2.typ =0 
		LEFT JOIN v_kkn2 KC2 ON o.kat2 = KC2.klucz and KC2.rok IN (0, @session.FK.AktRokObrach)
		WHERE n.GUID = _GUID
		GROUP BY n.GUID
		;
		#order by KC0.Valuex, KC2.Valuex, KC3.Valuex, KC4.Valuex; 

	END IF;
	
	
	IF _Symbol = 'SPEC4S' THEN

	SELECT
		'fr_ht_fv_spec__SPEC4s' as fr_ht_fv_spec__SPEC4s,
		'' as pozycje,
		if(ifnull(k.zatrudnienie,'')='T','T','') as zatrudnienie,
		k.data_waznosci_umowy,
		-- DATE_ADD(k.data_waznosci_umowy, INTERVAL 1 YEAR) as data_waznosci_umowy2, -- dopisek dla IRKO - umowa z definicji na rok
		-- ifnull(k.data_waznosci_umowy_do,'2015-12-31') as data_waznosci_umowy2,
		ifnull(k.data_waznosci_umowy_do,'czas nieokreślony') as data_waznosci_umowy2,
		current_date() as data_aneksu,
		k.samofakturowanie,
		k.odbierajacy as kontrodbierajacy,
		CONCAT(LEFT(SUBSTRING_INDEX(odp_za_zam,' ',-2),1),
			LEFT(SUBSTRING_INDEX(odp_za_zam,' ',-1),1)) as odp_za_zam, -- sa to teraz inicjaly osoby, dla sa i ship
		k.odp_za_zam as odp_za_zam2, -- pelne pole
		k.symbol as kontrsymbol,
		k.nazwa as kontrnazwa,
		k.nip as kontrnip,
		k.regon as kontrREGON,
		k.krs,
		k.vat as platnik_vat,
		CONCAT(k.zipcode,' ',k.nazwamiasta,', ',k.ulica) AS adres,
		(select NazwaKonta from rachbankowe where kontrGUID=k.GUID order by idn desc limit 1) as kontr_bank_nazwa,
		(select NrRachunku from rachbankowe where kontrGUID=k.GUID order by idn desc limit 1) as kontr_bank_numer,
		ifnull(k.opis_dzialalnosci,'') as opis_dzialalnosci,
		(select adres from kontakty where kontrguid = k.GUID AND typ_medium = 3 AND opis = 'E-Faktura' LIMIT 1) AS kontremail,
		case when (SELECT valuea FROM IReg WHERE idkey ='nip' AND idpath LIKE '%owner' LIMIT 1) = '9571087826' then 'IRKO OFFSHORE'
		ELSE
			(SELECT `value` FROM _db_globals WHERE `key`='owner') 
		END as nfirma,
		_Param_A as parametr
		FROM kontrah k
		WHERE k.GUID = _GUID
		;

	END IF;
	
-- CALL fr_ht_fv_spec('SPEC4S','{84A8684C-51CF-4B1D-88E3-71A3161E55D7}','','','');

/* *******************************************************************************
	SPEC5 - drugi dataset (sumowanie wagi grupowane wg symbolu dostawcy)
******************************************************************************* */

	IF _Symbol = 'SPEC5' THEN

		SELECT 
		t.symboludost AS symboludost,
		_Rnd2((o.wartnet)/(o.ilosc*t.waga)) AS cena_kg,
		_Rnd2(SUM(o.ilosc*t.waga)) AS waga
		FROM oppo o
		LEFT JOIN TowrNg t ON t.GUID = o.idntowr
		INNER JOIN opng n ON n.GUID = o.idnoper
		WHERE o.idnoperfin = @GUID
		GROUP BY t.symboludost, _Rnd2((o.wartnet)/(o.ilosc*t.waga))

		UNION SELECT 
		t.symboludost AS symboludost,
		_Rnd2((o.wartnet)/(o.ilosc*t.waga)) AS cena_kg,
		_Rnd2(SUM(o.ilosc*t.waga)) AS waga
		FROM oppo o
		LEFT JOIN TowrNg t ON t.GUID = o.idntowr
		INNER JOIN opng n ON (n.guid=o.idnoperfin or n.guid=o.idnoper)
		WHERE n.GUID = _GUID
		GROUP BY t.symboludost, _Rnd2((o.wartnet)/(o.ilosc*t.waga))
		ORDER BY symboludost; 				
		
	END IF;	

-- CALL fr_ht_fv_spec('SPEC5','{4A968BED-DADB-43CA-8A17-E73057F66A22}','','','');
	

/* *******************************************************************************
	SPEC6 - drugi dataset (sumowanie wartosci osobno dla towarow i zaliczki)
******************************************************************************* */

	IF _Symbol = 'SPEC6' THEN

		SELECT 
		IF(o.towrnazwa LIKE 'ZALICZKA%',if(n.lang_id='pl','Zaliczka','Value of prepayment'),if(n.lang_id='pl','Towary','Value of goods')) AS rodzajpoz,
		SUM(o.wartnetwal) AS wartosc
		FROM oppo o
		inner join opng n on (n.guid=o.idnoperfin or n.guid=o.idnoper)
		WHERE n.GUID = @GUID
		GROUP BY IF(o.towrnazwa LIKE 'ZALICZKA%',if(n.lang_id='pl','Zaliczka','Value of prepayment'),if(n.lang_id='pl','Towary','Value of goods'))
		-- GROUP BY IF(o.towrnazwa LIKE 'ZALICZKA%','ZALICZKA','TOWARY')

		UNION SELECT 
		IF(o.towrnazwa LIKE 'ZALICZKA%',if(n.lang_id='pl','Zaliczka','Value of prepayment'),if(n.lang_id='pl','Towary','Value of goods')) AS rodzajpoz,
		SUM(o.wartnetwal) AS wartosc
		FROM oppo o
		inner join opng n on (n.guid=o.idnoperfin or n.guid=o.idnoper)
		WHERE n.GUID = _GUID
		GROUP BY IF(o.towrnazwa LIKE 'ZALICZKA%',if(n.lang_id='pl','Zaliczka','Value of prepayment'),if(n.lang_id='pl','Towary','Value of goods'))
		ORDER BY rodzajpoz;
		
	END IF;	

-- CALL fr_ht_fv_spec('SPEC6','{ABE841AE-1413-4442-B7E2-40D9614C92F3}','','','');
	

/* ************************************************************************************************************
	SPEC7 - drugi dataset (faktury zbiorczo w danym miesiacu dla danego klienta - oswiadczenie odbioru towaru)
************************************************************************************************************* */

	IF _Symbol = 'SPEC7' THEN

		SELECT 
		kontrGUID, 
		MONTH(datawystaw),
		YEAR(datawystaw),
		rodzaj
		INTO _kontrGUID, _mc, _rok, _rodzaj
		FROM opng
		WHERE GUID = _GUID
		;

		
		SELECT 
		k.nazwa as kontrahent,
		k.ulica as adres, 
		k.zipcode, k.nazwamiasta, k.nip,
		kr.nazwa as kraj,kr.nazwa_2 as kraj_pl,
		n.CKwotaWal as wartosc,
		w.symbol as waluta,  
		n.nrdokfin,  n.datawystaw, 
		kt.valuex AS kontrakt,
		(SELECT GROUP_CONCAT(CONCAT(p.ilosc,' ',j.nazwa)) FROM oppo p INNER JOIN Jednostki j ON j.idn=jednostka
		WHERE (p.idnoper=n.guid OR p.idnoperfin=n.guid)) AS waga,
		(SELECT GROUP_CONCAT(DISTINCT towrnazwa) FROM oppo WHERE (idnoper=n.guid OR idnoperfin=n.guid)) AS towary,
		left(SUBSTRING_INDEX(n.uwagi,'PLACE OF DESTINATION: ',-1),2) AS miejsce_dostawy
		FROM opng n
		-- LEFT JOIN katcols kt ON kt.klucz=n.katcol2 AND kt.colnr=3 AND kt.typ=0
		LEFT JOIN v_kkn2 kt ON kt.klucz = n.katcol2 and kt.rok IN (0, @session.FK.AktRokObrach)
		INNER JOIN kontrah k ON k.GUID=n.kontrGUID
		inner join waluty w ON n.waluta=w.idn
		left join Miasta m ON k.NazwaMiasta=m.NazwaMiasta
		left join Kraje kr ON kr.idn_kraju=m.IDNKraju
		WHERE kontrGUID = _kontrGUID AND MONTH(datawystaw)= _mc AND YEAR(datawystaw)= _rok
		AND (rodzaj = _rodzaj OR
			CASE WHEN _rodzaj = 80 THEN rodzaj IN (80,85) WHEN _rodzaj = 50 THEN rodzaj IN (50,90)
						WHEN _rodzaj = 1 THEN rodzaj IN (1,3) END)
		AND n.deleted=0;
		
	END IF;	

-- CALL fr_ht_fv_spec('SPEC7','{72A3FEE4-F17C-42FB-8B91-D9C6BAD04180}','','','');
	

/* ********************************************************************************************
	SPEC7B - dane do wydruku statement w Baltimie - pobierane takze z zamowienia
    CALL fr_ht_fv_spec('SPEC7B','{72A3FEE4-F17C-42FB-8B91-D9C6BAD04180}','','','');
****************************************************************************************** */

IF _Symbol = 'SPEC7B' THEN

	SET _GUID_zamowienia =
	(select distinct idnng from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID
	 where o.idnoper = _GUID  or o.idnoperfin = _GUID limit 1);

    SELECT group_concat(cysterna), date(data_dostawy) INTO _nr_transportu, _data_dostawy
    FROM zmpo p
    LEFT JOIN zmpo_logistyka zl ON p.GUID = zl.GUID
    WHERE p.idnng = _GUID_zamowienia
    ;

    SELECT uwagi INTO _uwagi FROM opng WHERE GUID = _GUID;

	SELECT
	k.nazwa as kontrahent,
	k.ulica as adres,
	k.zipcode, k.nazwamiasta, k.nip,
	kr.nazwa as kraj,kr.nazwa_2 as kraj_pl,
	n.CKwotaWal as wartosc,
	w.symbol as waluta,
	n.nrdokfin,  n.datawystaw,
	(SELECT nrzamw FROM zmng WHERE GUID = _GUID_zamowienia) AS nr_zamowienia,
	kt.valuex AS kontrakt,
	-- _nr_transportu AS nr_transportu,
	-- _data_dostawy AS data_dostawy,
	(SELECT GROUP_CONCAT(CONCAT(p.ilosc,' ',j.nazwa)) FROM oppo p INNER JOIN Jednostki j ON j.idn=jednostka
	WHERE (p.idnoper=n.guid OR p.idnoperfin=n.guid)) AS waga,
	(SELECT GROUP_CONCAT(DISTINCT towrnazwa) FROM oppo WHERE (idnoper=n.guid OR idnoperfin=n.guid)) AS towary,

	CASE WHEN LOCATE('TRUCK NO:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'TRUCK NO: ',-1),'\n',1)
		WHEN LOCATE('NR REJESTRACYJNE:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'NR REJESTRACYJNE: ',-1),'\n',1)
	ELSE '' END AS nr_transportu,
	CASE WHEN LOCATE('PLACE OF LOADING:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'PLACE OF LOADING: ',-1),'\n',1)
		WHEN LOCATE('MIEJSCE DOSTAWY:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'MIEJSCE DOSTAWY: ',-1),'\n',1)
	ELSE '' END AS miejsce_dostawy,
	CASE WHEN LOCATE('DATE OF LOADING:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'DATE OF LOADING: ',-1),'\n',1)
		WHEN LOCATE('DATA DOSTAWY:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'DATA DOSTAWY: ',-1),'\n',1)
	ELSE '' END AS data_dostawy,

	CASE WHEN LOCATE('PLACE OF DESTINATION:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'PLACE OF DESTINATION: ',-1),'\n',1)
		WHEN LOCATE('MIEJSCE ODBIORU:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'MIEJSCE ODBIORU: ',-1),'\n',1)
	ELSE '' END AS miejsce_odbioru,

	CASE WHEN LOCATE('DATE OF UNLOADING:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'DATE OF UNLOADING: ',-1),'\n',1)
		WHEN LOCATE('DATA ODBIORU:',n.uwagi) > 0 THEN
		SUBSTRING_INDEX(SUBSTRING_INDEX(n.uwagi,'DATA ODBIORU: ',-1),'\n',1)
	ELSE '' END AS data_odbioru

	FROM opng n
	-- LEFT JOIN katcols kt ON kt.klucz=n.katcol2 AND kt.colnr=3 AND kt.typ=0
	LEFT JOIN v_kkn2 kt ON kt.klucz = n.katcol2 and kt.rok IN (0, @session.FK.AktRokObrach)
	INNER JOIN kontrah k ON k.GUID=n.kontrGUID
	inner join waluty w ON n.waluta=w.idn
	left join Miasta m ON k.NazwaMiasta=m.NazwaMiasta
	left join Kraje kr ON kr.idn_kraju=m.IDNKraju
	WHERE n.GUID = _GUID
	AND n.deleted=0;

END IF;

/* *******************************************************************************
	SPEC8 - sumowanie wartosci bez zaliczek - i z tego tabelki vat, pln i walutowa
******************************************************************************* */

	IF _Symbol = 'SPEC8' THEN

		SELECT 
		o.idnvat, v.shortname as idnVatStr,
		SUM(o.wartnetwal) AS wartnetwal,
		SUM(o.wartnetwal)*v.value AS wartvatwal,
		SUM(o.wartnetwal)+ SUM(o.wartnetwal)*v.value AS wartbrtwal,
		SUM(o.wartnet) AS wartnet,
		SUM(o.wartvat) AS wartvat,
		SUM(o.wartbrt) AS wartbrt
		
		FROM oppo o
		inner join opng n on n.GUID=o.idnoperfin
		inner join erp_vat v on v.ID=o.idnvat
		WHERE o.idnoperfin = _GUID and o.towrsymb <>'ZALICZKA'
		-- GROUP BY o.idnvat
		

		UNION SELECT 
		o.idnvat, v.shortname as idnVatStr,
		SUM(o.wartnetwal) AS wartnetwal,
		SUM(o.wartnetwal)*v.value AS wartvatwal,
		SUM(o.wartnetwal)+ SUM(o.wartnetwal)*v.value AS wartbrtwal,
		SUM(o.wartnet) AS wartnet,
		SUM(o.wartvat) AS wartvat,
		SUM(o.wartbrt) AS wartbrt
		
		FROM oppo o
		inner join opng n on n.GUID=o.idnoper
		inner join erp_vat v on v.ID=o.idnvat
		WHERE o.idnoper = _GUID and o.towrsymb <>'ZALICZKA'
		GROUP BY o.idnvat
		
		ORDER BY idnVatStr;
		
	END IF;	

-- CALL fr_ht_fv_spec('SPEC8','{5E2AAD45-4637-41A1-A8C6-988B22766D97}','','','');

/* *******************************************************************************
	SPEC9 - informacje o towarach z zamowienia

CALL fr_ht_fv_spec('SPEC9','{09B9F835-9EF9-4D05-B348-90BB254413B0}','','','');
******************************************************************************* */

	IF _Symbol = 'SPEC9' THEN

	SET _GUID_zamowienia = (select distinct idnng from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID 
									 where o.idnoper = _GUID  or o.idnoperfin = _GUID limit 1);
	
   SET @lang_id = (select lang_id from opng where guid = _GUID);
  
		SELECT

		if(@lang_id='pl',zo.towrnazwa,l.tostr) as towrnazwa,
		zo.towrnazwa as towrnazwa_pl
		FROM zmpo zo
		LEFT JOIN lang_translations l ON zo.idntowr = l.fromstr AND l.to_lang = @lang_id
		where zo.idnng = _GUID_zamowienia
		and zo.towrsymb not in ('Zaliczka','Transport')
		;
		
	END IF;	


/* *******************************************************************************
	SPEC10 - informacje o wczesniejszych fakturach zaliczkowych

	 CALL fr_ht_fv_spec('SPEC10','{44A03908-8D61-4EF6-9FD2-B60C02DBFABF}','','','');

******************************************************************************* */

	IF _Symbol = 'SPEC10' THEN 

	SET _GUID_zamowienia = (select distinct idnng from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID 
									 where zo.idnng <> '' AND
									 -- o.usluga=8 AND  -- patrz wpis dla spec12
									 (o.idnoper = _GUID or o.idnoperfin = _GUID) limit 1);
	 
	-- select _GUID_zamowienia;

	 SELECT DISTINCT n.guid, n.datawystaw, n.nrdokfin, n.rodzaj, n.netto, n.brutto, n.vat, 
	 n.nettowal, _Rnd2(n.brutto/n.kurs) AS bruttoWal, (_Rnd2(n.brutto/n.kurs))- n.nettowal AS vatWal,
	 W.Symbol AS WalutaStr
	 FROM opng n
	 inner join oppo o ON (n.guid=o.idnoperfin or n.guid=o.idnoper)
	 inner join zmpo zo ON o.guidzm=zo.guid
	 INNER JOIN TowrNg T ON T.GUID=o.idntowr
	 INNER JOIN waluty W ON W.idn = n.waluta
	 where zo.idnng = _GUID_zamowienia
	 and T.usluga = 8 AND o.wartnetwal>=0
	 -- and o.towrsymb in('Zaliczka','ZALICZKA') 
	 and n.guid <> _GUID
	 and n.datawystaw <= (select datawystaw from opng where guid = _GUID)
	 and n.idn < (select idn from opng where guid = _GUID)
	 and n.rodzaj IN (1,2,50,80) -- eliminuje duble kombinacji WZ-FV (paraqua)

	 UNION
	 
	 SELECT DISTINCT corn.guid, corn.datawystaw, corn.nrdokfin, corn.rodzaj, corn.netto, corn.brutto, corn.vat, 
	 corn.nettowal, _Rnd2(corn.brutto/corn.kurs) AS bruttoWal, (_Rnd2(corn.brutto/corn.kurs))- corn.nettowal AS vatWal,
	 W.Symbol AS WalutaStr
	 FROM opng n
	 INNER JOIN oppo o ON (n.guid=o.idnoperfin OR n.guid=o.idnoper)
	 INNER JOIN zmpo zo ON o.guidzm=zo.guid
	 INNER JOIN TowrNg T ON T.GUID=o.idntowr
	 	 
	 INNER JOIN oppo cor ON cor.GUIDKorPo=o.guid
	 INNER JOIN opng corn ON cor.idnoperfin=corn.guid
	 INNER JOIN waluty W ON W.idn = n.waluta
	 WHERE zo.idnng = _GUID_zamowienia
	 AND T.usluga = 8 AND o.wartnetwal>=0
	 
	 AND n.guid <> _GUID
	 -- AND n.datawystaw<= (SELECT datawystaw FROM opng WHERE guid = _GUID) --  to bez sensu, bo bada niewlasciwa tabele dla korekt
	 AND corn.datawystaw <= (SELECT datawystaw FROM opng WHERE guid = _GUID)
	 AND n.idn < (SELECT idn FROM opng WHERE guid = _GUID)
	 ORDER BY datawystaw;
		
	END IF;	

/* *******************************************************************************
	SPEC10B - informacje o wczesniejszych fakturach zaliczkowych - dla Koloru inna wersja

	 CALL fr_ht_fv_spec('SPEC10B','{44A03908-8D61-4EF6-9FD2-B60C02DBFABF}','','','');

******************************************************************************* */

	IF _Symbol = 'SPEC10Bold' THEN -- kolor - zapytanie kompletnie niewydajne - do wywalenie po wyjasnieniu

		DROP TABLE IF EXISTS temp_poz_zam;
		CREATE TABLE temp_poz_zam 
		select distinct zo.idnng, zo.guid from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID where o.idnoper = @GUID or o.idnoperfin = @GUID; 

	 SELECT DISTINCT n.guid, n.datawystaw, n.nrdokfin, n.rodzaj, n.netto, n.brutto, n.vat, 
	 n.nettowal, _Rnd2(n.brutto/n.kurs) AS bruttoWal, (_Rnd2(n.brutto/n.kurs))- n.nettowal AS vatWal,
	 W.Symbol AS WalutaStr
	 FROM opng n
	 inner join oppo o ON (n.guid=o.idnoperfin or n.guid=o.idnoper) -- tu jest problem
	 inner join zmpo zo ON o.guidzm=zo.guid
	 INNER JOIN TowrNg T ON T.GUID=o.idntowr
	 INNER JOIN waluty W ON W.idn = n.waluta
	 where zo.idnng IN (select distinct idnng from temp_poz_zam)
	 and T.usluga = 8 AND o.wartnetwal>=0
	 -- and o.towrsymb in('Zaliczka','ZALICZKA') 
	 and n.guid <> @GUID
	 and n.datawystaw<= (select datawystaw from opng where guid = @GUID) 
	 and n.idn < (select idn from opng where guid = @GUID)
	 order by n.datawystaw;
		
	END IF;	

	IF _Symbol = 'SPEC10B' THEN -- zoptymalizowne, ale do weryfikacji TK - dlaczego było j.w.?
		
        DROP TABLE IF EXISTS temp_poz_zam;
        CREATE TABLE temp_poz_zam
        select distinct zo.idnng, zo.guid
        from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID
        where o.idnoper = _GUID or o.idnoperfin = _GUID;

        select datawystaw, idn INTO _DATAWYSTAW, _idn from opng where guid = _GUID;

        SELECT DISTINCT n.guid, n.datawystaw, n.nrdokfin, n.rodzaj, n.netto, n.brutto, n.vat,
        n.nettowal, _Rnd2(n.brutto/n.kurs) AS bruttoWal, (_Rnd2(n.brutto/n.kurs))- n.nettowal AS vatWal,
        W.Symbol AS WalutaStr
        FROM opng n
        inner join oppo o ON n.guid=o.idnoperfin -- alternatywnie (n.guid=o.idnoperfin or n.guid=o.idnoper)
                                                 -- to wynika z tego, ze zaliczki moga byc realizowane przez pozycje WZ
        inner join zmpo zo ON o.guidzm=zo.guid
        INNER JOIN TowrNg T ON T.GUID=o.idntowr
        INNER JOIN waluty W ON W.idn = n.waluta
        where zo.idnng IN (select distinct idnng from temp_poz_zam)
        and T.usluga = 8 AND o.wartnetwal>=0
        and n.guid <> _GUID
        and n.datawystaw <= _DATAWYSTAW
        and n.idn < _idn
        order by n.datawystaw;

        DROP TABLE IF EXISTS temp_poz_zam;

    END IF;


/* *******************************************************************************
	SPEC11 - informacje o zamowieniu Hydromega, Kolor w innym wariancie

	 CALL fr_ht_fv_spec('SPEC11','{44A03908-8D61-4EF6-9FD2-B60C02DBFABF}','','','');

******************************************************************************* */

	IF _Symbol = 'SPEC11' THEN 

	SET _GUID_zamowienia = (select distinct idnng from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID 
										 WHERE o.usluga=8 AND (o.idnoper = _GUID OR o.idnoperfin = _GUID) LIMIT 1);

		IF @session.globals.Owner IN ('BYSEWO','IRKO') THEN
			SELECT 
				p.towrsymb, p.towrnazwa, T.pkwiu,T.pcn, p.ilosc, 
				IF(J.Nazwa = '', '', J.Nazwa) As Jednostka,
				v.shortname as idnvat, p.wartnet, p.wartvat, (p.wartnet+ p.wartvat) AS brutto,
				p.wartnetwal as nettowal, (p.wartbrtwal-p.wartnetwal) as vatwal, p.wartbrtwal as bruttowal,
				W.Symbol AS WalutaStr
				-- (select bruttowal from zmng where guid=p.idnng) as zam_bruttowal - to jest kompletnie zle
			FROM 
				oppo p
			INNER JOIN opng N ON p.idnoper = N.GUID OR p.idnoperfin = N.GUID
			JOIN TowrNg T ON T.GUID=p.idntowr
			LEFT JOIN Jednostki J ON J.idn = p.jednostka 
			inner join erp_vat v on v.ID=p.idnvat
			INNER JOIN waluty W ON W.idn = N.waluta
			WHERE 
				(p.idnoper = _GUID or p.idnoperfin = _GUID)
				and T.usluga not in (8,9) -- bez zaliczek i platnosci czesciowych/koncowych
			;

		ELSE
			SELECT 
				p.towrsymb,
			--	p.towrnazwa,
			    IF(N.lang_id<>"pl", IF(T.nazwa=p.towrnazwa, 
				IFNULL(( SELECT tr.tostr FROM lang_translations tr WHERE tr.to_lang=N.lang_id AND tr.object_name="TowrNg" AND tr.object_key=p.idntowr LIMIT 1),
				 p.towrnazwa), p.towrnazwa),p.towrnazwa) AS towrnazwa,
				 p.uwagi, p.uwagi2,
				T.pkwiu,T.pcn, p.ilosc, 
				IF(J.Nazwa = '', '', J.Nazwa) As Jednostka,
				_Rnd2(p.wartnet/p.ilosc) AS cena,
				_Rnd2(p.wartnetwal/p.ilosc) AS cenawal,
				v.shortname as idnvat, p.wartnet, p.wartvat, (p.wartnet+ p.wartvat) AS brutto,
				p.wartnetwal as nettowal, (p.wartbrtwal-p.wartnetwal) as vatwal, p.wartbrtwal as bruttowal,
				W.Symbol AS WalutaStr,
				N.bruttowal as zam_bruttowal,
				N.bruttowal-(select sum(p2.wartbrtwal) from zmpo p2 inner join TowrNg t2 where t2.GUID = p2.idntowr and p2.idnNG = _GUID_zamowienia and t2.usluga in (8,9)) as zam_bruttowal2 -- specjalnie dla Axisu, gdy czasami nie kazda pozycja zaliczkowa jest zerowana
			FROM 
				zmpo p
			JOIN TowrNg T ON T.GUID=p.idntowr
			LEFT JOIN Jednostki J ON J.idn = p.jednostka 
			inner join erp_vat v on v.ID=p.idnvat
			INNER JOIN zmng N ON N.GUID = p.idnNG 
			INNER JOIN waluty W ON W.idn = N.waluta
			WHERE 
				p.idnNG = _GUID_zamowienia
				and T.usluga not in (8,9) -- bez zaliczek i platnosci czesciowych/koncowych
			;	

		END IF;

	END IF;	


/* *******************************************************************************
SPEC11_GRP - informacje o zamowieniu przez procedure fr_ht_ZM2 dlatego, zeby wlaczyc grupowanie internalne

faktura zaliczkowa z simexu
CALL fr_ht_fv_spec('SPEC11_GRP','{209CF1D6-BCDC-405C-8505-6895C524EC90}','','','');
******************************************************************************* */

	IF _Symbol = 'SPEC11_GRP' THEN

	SET _GUID_zamowienia = (select distinct idnng from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID
										 WHERE o.usluga=8 AND (o.idnoper = _GUID OR o.idnoperfin = _GUID) LIMIT 1);

 CALL fr_ht_ZM2('ZM_PO_DEFAULT', _GUID_zamowienia, 'FAKTURA_ZALICZKOWA','',0,'','','','');

	END IF;

/* *******************************************************************************
	SPEC12 - informacje o wczesniejszych fakturach zaliczkowych Hydromega

	 CALL fr_ht_fv_spec('SPEC12','{44A03908-8D61-4EF6-9FD2-B60C02DBFABF}','','','');

******************************************************************************* */

	IF _Symbol = 'SPEC12' THEN 

	SET _GUID_zamowienia = (select distinct idnng from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID 
									 	 WHERE zo.idnng <> '' AND
									 	 -- o.usluga=8 AND -- zmiana dla simexu, ale problem chyba jest wiekszy - to nie mialo prawa dzialac w rozliczeniowych, gdy zaliczka byla na innej fakturze realizowana
									 	 (o.idnoper = _GUID OR o.idnoperfin = _GUID) LIMIT 1);
	 
	-- select _GUID_zamowienia;

	select datawystaw, idn INTO _DATAWYSTAW, _idn from opng where guid = _GUID;

	 SELECT DISTINCT n.datawystaw,o.towrsymb, 
		o.towrnazwa AS zoper,
	  IF(n.lang_id<>"pl", IF(T.nazwa=zo.towrnazwa,
	  IFNULL(( SELECT tostr FROM lang_translations WHERE to_lang=n.lang_id AND object_name="TowrNg" AND object_key=zo.IdnTowr LIMIT 1), zo.towrnazwa), zo.towrnazwa),zo.towrnazwa) AS towrnazwa,
		T.pkwiu, o.ilosc,n.nrdokfin, 
		
		  CASE WHEN n.lang_id <> "pl" THEN
			IF((J.Nazwa1 = "" OR ISNULL(J.Nazwa1)), J.Nazwa, J.Nazwa1) 
			ELSE J.Nazwa END AS Jednostka, 
			v.shortname AS idnvat, o.wartnet,o.wartnetwal, o.wartvat,(o.wartBrtWal-o.wartnetwal) AS wartVatWal, (o.wartnet+ o.wartvat) AS brutto,o.wartBrtWal,
			W.Symbol AS WalutaStr
	 FROM opng n
	 inner join oppo o ON (n.guid=o.idnoperfin or n.guid=o.idnoper)
	 inner join zmpo zo ON o.guidzm=zo.guid
	JOIN TowrNg T ON T.GUID=o.idntowr
	LEFT JOIN Jednostki J ON J.idn = o.jednostka 
 	inner join erp_vat v on v.ID=o.idnvat
	INNER JOIN waluty W ON W.idn = n.waluta
	 where zo.idnng = _GUID_zamowienia
	 -- and o.towrsymb in('Zaliczka','ZALICZKA') 
	 and T.usluga = 8 
	 and IF(@session.globals.Owner IN ('HYDROMEGA','PARAQUA'),o.wartnetwal>=0,TRUE)
	 AND IF(@session.globals.Owner NOT IN ('CDG','TRANSBUD','BYSEWO','IRKO'),n.guid <> _GUID,TRUE) -- wylacza pozycje wlasne faktury
	-- and n.guid <> @GUID
	 and n.datawystaw<= _DATAWYSTAW
	 AND IF(@session.globals.Owner IN ('HYDROMEGA'),n.idn < _idn,TRUE)
	-- and n.idn < (select idn from opng where guid = @GUID)
	GROUP BY o.guid -- dla Polamu, zeby nie rozmnazac pozycji WZ podpiętych do faktury
	
	UNION
	 
    SELECT DISTINCT corn.datawystaw, cor.towrsymb, cor.towrnazwa AS zoper,
	IF(corn.lang_id<>"pl", IF(T.nazwa=zo.towrnazwa,
	IFNULL(( SELECT tostr FROM lang_translations WHERE to_lang=n.lang_id AND object_name="TowrNg" AND object_key=zo.IdnTowr LIMIT 1), zo.towrnazwa), zo.towrnazwa),zo.towrnazwa) AS towrnazwa,
	T.pkwiu, cor.ilosc,corn.nrdokfin,
    CASE WHEN n.lang_id <> "pl" THEN
		IF((J.Nazwa1 = "" OR ISNULL(J.Nazwa1)), J.Nazwa, J.Nazwa1)
	ELSE J.Nazwa END AS Jednostka,

    v.shortname AS idnvat, cor.wartnet,cor.wartnetwal, cor.wartvat,(cor.wartBrtWal-cor.wartnetwal) AS wartVatWal, (cor.wartnet+ cor.wartvat) AS brutto,cor.wartBrtWal,
	W.Symbol AS WalutaStr
	FROM opng n
	INNER JOIN oppo o ON (n.guid=o.idnoperfin OR n.guid=o.idnoper)
	INNER JOIN zmpo zo ON o.guidzm=zo.guid
	JOIN TowrNg T ON T.GUID=o.idntowr
	LEFT JOIN Jednostki J ON J.idn = o.jednostka 
 	INNER JOIN erp_vat v ON v.ID=o.idnvat
	INNER JOIN waluty W ON W.idn = n.waluta
	INNER JOIN oppo cor ON cor.GUIDKorPo=o.guid
	INNER JOIN opng corn ON cor.idnoperfin=corn.guid
	WHERE zo.idnng = _GUID_zamowienia
	AND T.usluga = 8
	AND IF(@session.globals.Owner IN ('HYDROMEGA', 'PARAQUA'),o.wartnetwal>=0,TRUE)
	AND IF(@session.globals.Owner NOT IN ('CDG','TRANSBUD','BYSEWO','IRKO'),n.guid <> _GUID,TRUE)
	
	AND n.datawystaw<= _DATAWYSTAW
	AND IF(@session.globals.Owner IN ('CDG','HYDROMEGA','TRANSBUD'),n.idn <= _idn,TRUE)
    ORDER BY datawystaw;

	END IF;	

/* *******************************************************************************
	SPEC12B - informacje o wczesniejszych realizacjach zaliczek (Kolor) - inny wariant rozliczeń
	 SET @g='x';
	 SELECT guid from opng where idn=1 INTO @g;
	 CALL fr_ht_fv_spec('SPEC12B',@g,'','','');

******************************************************************************* */

	IF _Symbol = 'SPEC12B' THEN 

	DROP TABLE IF EXISTS temp_poz_zam;
	CREATE TABLE temp_poz_zam 
	select distinct zo.idnng, zo.guid
	from zmpo zo inner join oppo o ON o.GUIDzm=zo.GUID
	where o.idnoper = _GUID or o.idnoperfin = _GUID;

    select datawystaw, idn INTO _DATAWYSTAW, _idn from opng where guid = _GUID;

    SELECT DISTINCT o.towrsymb,
        o.towrnazwa AS zoper,
        IF(n.lang_id<>"pl", IF(T.nazwa=zo.towrnazwa,
        IFNULL(( SELECT tostr FROM lang_translations WHERE to_lang=n.lang_id AND object_name="TowrNg" AND object_key=zo.IdnTowr LIMIT 1), zo.towrnazwa), zo.towrnazwa),zo.towrnazwa) AS towrnazwa,
		T.pkwiu, o.ilosc,n.nrdokfin, 
		CASE WHEN n.lang_id <> "pl" THEN
			IF((J.Nazwa1 = "" OR ISNULL(J.Nazwa1)), J.Nazwa, J.Nazwa1) 
			ELSE J.Nazwa END AS Jednostka, 
			v.shortname AS idnvat, o.wartnet,o.wartnetwal, o.wartvat,(o.wartBrtWal-o.wartnetwal) AS wartVatWal, (o.wartnet+ o.wartvat) AS brutto,o.wartBrtWal 

	FROM opng n
	inner join oppo o ON (n.guid=o.idnoperfin or n.guid=o.idnoper)
	inner join zmpo zo ON o.guidzm=zo.guid
	JOIN TowrNg T ON T.GUID=o.idntowr
	LEFT JOIN Jednostki J ON J.idn = o.jednostka 
 	inner join v_erp_vat v on v.ID=o.idnvat

	where zo.idnng IN (select distinct idnng from temp_poz_zam)
    and T.usluga = 8
-- 	 and IF(@session.globals.Owner IN ('HYDROMEGA'),o.wartnetwal>=0,TRUE)
-- 	 AND IF(@session.globals.Owner NOT IN ('TRANSBUD','BYSEWO','IRKO'),n.guid <>@GUID,TRUE) -- wylacza pozycje wlasne faktury
	-- and n.guid <> @GUID
	and n.datawystaw <= _DATAWYSTAW
-- 	 AND IF(@session.globals.Owner IN ('HYDROMEGA'),n.idn < (SELECT idn FROM opng WHERE guid = @GUID),TRUE)
	-- and n.idn < (select idn from opng where guid = @GUID)
	order by n.datawystaw;

    DROP TABLE IF EXISTS temp_poz_zam;

	END IF;	

/* *******************************************************************************
	SPEC13 - tabelka vat w fakturze koncowej
******************************************************************************* */

	IF _Symbol = 'SPEC13' THEN

	SET _GUID_zamowienia = (SELECT DISTINCT idnng FROM zmpo zo INNER JOIN oppo o ON o.GUIDzm=zo.GUID
									  WHERE o.usluga=8 AND (o.idnoper = _GUID OR o.idnoperfin = _GUID) LIMIT 1);
	
		IF @session.globals.Owner IN ('CDG','BYSEWO','IRKO','PARAQUA','MAXFORM') THEN
			SELECT DISTINCT 
				o.idnvat, v.shortname AS idnVatStr,
				SUM(o.wartnetwal) AS wartnetwal,
				SUM(o.wartnetwal)*v.value AS wartvatwal,
				SUM(o.wartnetwal)+ SUM(o.wartnetwal)*v.value AS wartbrtwal,
				SUM(o.wartnet) AS wartnet,
				SUM(o.wartvat) AS wartvat,
				SUM(o.wartbrt) AS wartbrt
			FROM oppo o
			INNER JOIN erp_vat v ON v.ID=o.idnvat
			INNER JOIN TowrNg T ON T.GUID=o.idntowr
			WHERE 
				(o.idnoper = _GUID or o.idnoperfin = _GUID)
				and T.usluga not in (8,9) -- bez zaliczek i platnosci czesciowych/koncowych
			-- AND o.wartnetwal>=0
			ORDER BY idnVatStr;

		ELSE
			SELECT DISTINCT 
				o.idnvat, v.shortname AS idnVatStr,
				SUM(o.wartnetwal) AS wartnetwal,
				SUM(o.wartnetwal)*v.value AS wartvatwal,
				SUM(o.wartnetwal)+ SUM(o.wartnetwal)*v.value AS wartbrtwal,
				SUM(o.wartnet) AS wartnet,
				SUM(o.wartvat) AS wartvat,
				SUM(o.wartbrt) AS wartbrt
			
			FROM opng n
			INNER JOIN oppo o ON (n.guid=o.idnoperfin OR n.guid=o.idnoper)
			INNER JOIN zmpo zo ON o.guidzm=zo.guid
			
			JOIN TowrNg T ON T.GUID=o.idntowr
			LEFT JOIN Jednostki J ON J.idn = o.jednostka 
			INNER JOIN erp_vat v ON v.ID=o.idnvat
			WHERE zo.idnng = _GUID_zamowienia
			-- and o.towrsymb not in ('Zaliczka','ZALICZKA') 
			and T.usluga <> 8
			AND o.wartnetwal>=0
			-- and n.guid <> @GUID
			-- and n.datawystaw<= (select datawystaw from opng where guid = @GUID) 
			-- and n.idn < (select idn from opng where guid = @GUID)
			ORDER BY idnVatStr;
		END IF;

	END IF;	

-- CALL fr_ht_fv_spec('SPEC13','{5E2AAD45-4637-41A1-A8C6-988B22766D97}','','','');
	
/* *******************************************************************************
	SPECATM1 - informacje o info..info4 z TowrNg na bazie towarów z oppo

	 CALL fr_ht_fv_spec('SPECATM1','GUIDOper','','','');

******************************************************************************* */
	IF _Symbol = 'SPECATM1' THEN 

		SELECT 
			GROUP_CONCAT(DISTINCT CONCAT(T.info," ", T.info2," ",  T.info3," ",  T.info4) SEPARATOR "|") AS opis
		FROM 
			oppo o
		JOIN TowrNg T ON T.GUID=o.idntowr
		WHERE
			o.idnoper=_GUID
		;	
		
	END IF;


/********************************************************************************
	SPEC14 - dodatkowe dane z TowrNg i wyliczenie ilosci palet

	 CALL fr_ht_fv_spec('SPEC14','{0D27E2C1-2795-4C70-BC5B-B667F98326B1}','','','');

********************************************************************************/
	IF _Symbol = 'SPEC14' THEN

	SELECT 
		
		o.towrsymb,
		o.towrnazwa,
		o.ilosc,
		jm.nazwa as jednostka,
		_varDOUBLE(_Rnd2(o.ilosc/o.ilewopakowaniu)) as opakowan,
		-- _varDOUBLE(_Rnd2(o.ilosc/t.ilewopakowaniu_zbiorczym_d)) as palet,
		_varDOUBLE(_Rnd2(o.ilosc/t.opakowanie_zbiorcze_d)) as palet,
		if(_varDOUBLE(_Rnd2(o.ilosc * t.przelicznik_l / t.przelicznik_m)) = o.ilosc,0, _varDOUBLE(_Rnd2(o.ilosc * t.przelicznik_l / t.przelicznik_m))) as rolek,
		IF((jp.Nazwa = "" or isnull(jp.Nazwa)), jm.Nazwa, jp.Nazwa) As JednostkaPom, 
		t.kod_kreskowy as kod_kreskowy,
		o.waga as waga
-- 	if(t.iloscpom = o.ilosc,0,t.iloscpom) as rolek
/*		o.cenanetzRabWal as cena,
		o.wartnetwal as wartosc,
		o.waga/1000 as waga
*/
		FROM oppo o
		LEFT JOIN TowrNg t ON t.GUID = o.idntowr
		left join Jednostki jm ON o.jednostka = jm.idn
		LEFT JOIN Jednostki jp ON jp.idn = t.jednostka_pom
		inner JOIN opng n on n.GUID = o.idnoper
		WHERE o.idnoper = _GUID
		
		order by o.lp; 


	END IF;


/********************************************************************************
	SPEC15 - idn do wydruku nalepek

	 CALL fr_ht_fv_spec('SPEC15','{0D27E2C1-2795-4C70-BC5B-B667F98326B1}','','','');

********************************************************************************/
	IF _Symbol = 'SPEC15' THEN

	SELECT 
		n.*
		
		FROM opng n
		WHERE n.guid = _GUID
		;
		


	END IF;

IF _Symbol = 'SPEC16' THEN
	
	IF  (SELECT COUNT(*) FROM oppo o WHERE o.idnoper = _GUID) = 0 THEN
	
	SELECT
		'-' AS towrsymb, '-' AS towrnazwa, '' , IF(n.zmagazynu=0,n.domagazynu,n.zmagazynu) AS magazyn,
		m.opis AS magnazwa,'' AS symbol,
		'' AS wartosc,
		'' AS ilosc, '' AS iloscbylo, '' AS iloscjest, 
		'-'  AS jm,n.nrdokmag,
		'' AS uwagi, n.rodzaj, 1 AS lp, '' AS lokalizacja1, '' AS lokalizacja2, '-' AS idn, '' AS wyroznik
		FROM opng n
		LEFT JOIN Magazyny m ON m.IdnMagazynu= IF(n.zmagazynu=0,n.domagazynu,n.zmagazynu)
		WHERE n.guid = _GUID;
	
	ELSE
	
	SELECT
		o.towrsymb, o.towrnazwa, o.cenanet, IF(o.zmagazynu=0,o.domagazynu,o.zmagazynu) AS magazyn,
		m.opis AS magnazwa,IFNULL(k.symbol,'') AS symbol,
		ROUND(o.cenanet*o.ilosc,2) AS wartosc,
		o.ilosc, IF(_Param_B=1,'',o.iloscbylo) AS iloscbylo, o.iloscjest, 
		j.nazwa AS jm,n.nrdokmag,
		o.uwagi, o.rodzaj, o.lp,t.lokalizacja1,t.lokalizacja2,t.idn,t.wyroznik
		FROM oppo o
		INNER JOIN Jednostki j ON j.idn = o.jednostka
		LEFT JOIN opng n ON n.guid=o.idnoper
		LEFT JOIN TowrNg t ON o.idntowr=t.guid
		LEFT JOIN Magazyny m ON m.IdnMagazynu= IF(o.zmagazynu=0,o.domagazynu,o.zmagazynu)
		LEFT JOIN kontrah k ON t.dostawcaGUID=k.guid
		WHERE o.idnoper = _GUID
		AND IF(_Param_C=1,o.iloscbylo>0,TRUE) 	
		ORDER BY o.towrsymb; 
	END IF;	
	
	END IF;	
/*
*******************************************************************************
	SPEC16_1- wydruk arkusza spisowego
*******************************************************************************
 */
	IF _Symbol = 'SPEC16_1' THEN
	
IF  (SELECT COUNT(*) FROM oppo o WHERE o.idnoper = _GUID) = 0 THEN
	
	SELECT
		'-' AS towrsymb, '-' AS towrnazwa, '' , IF(n.zmagazynu=0,n.domagazynu,n.zmagazynu) AS magazyn,
		m.opis AS magnazwa,'' AS symbol,
		'' AS wartosc,
		'' AS ilosc, '' AS iloscbylo, '' AS iloscjest, 
		'-'  AS jm,n.nrdokmag,
		'' AS uwagi, n.rodzaj, 1 AS lp, '' AS lokalizacja1, '' AS lokalizacja2, '-' AS idn, '' AS wyroznik
		FROM opng n
		LEFT JOIN Magazyny m ON m.IdnMagazynu= IF(n.zmagazynu=0,n.domagazynu,n.zmagazynu)
		WHERE n.guid = _GUID;
	
	ELSE	
	
	SELECT
		o.towrsymb, o.towrnazwa, o.cenanet, IF(o.zmagazynu=0,o.domagazynu,o.zmagazynu) AS magazyn,
		m.opis AS magnazwa,IFNULL(k.symbol,'') AS symbol,
		ROUND(o.cenanet*o.ilosc,2) AS wartosc,
		o.ilosc, IF(_Param_B=1,'',o.iloscbylo) AS iloscbylo, o.iloscjest, 
		j.nazwa AS jm,n.nrdokmag,
		o.uwagi, o.rodzaj, o.lp,t.lokalizacja1,t.lokalizacja2,t.idn,t.wyroznik
		FROM oppo o
		INNER JOIN Jednostki j ON j.idn = o.jednostka
		LEFT JOIN opng n ON n.guid=o.idnoper
		LEFT JOIN TowrNg t ON o.idntowr=t.guid
		LEFT JOIN Magazyny m ON m.IdnMagazynu= IF(o.zmagazynu=0,o.domagazynu,o.zmagazynu)
		LEFT JOIN kontrah k ON t.dostawcaGUID=k.guid
		WHERE o.idnoper = _GUID
		AND IF(_Param_C=1,o.iloscbylo>0,TRUE) 	
		ORDER BY IF(@session.globals.Owner IN ('SIMEX'),t.lokalizacja1,t.idn); 

	END IF;		
	END IF;	

/*
*******************************************************************************
	SPEC16_2- wydruk arkusza spisowego dla hydromegi, z informacjami o dostawie
*******************************************************************************
 */
IF _Symbol = 'SPEC16_2' THEN

	IF  (SELECT COUNT(*) FROM oppo o WHERE o.idnoper = _GUID) = 0 THEN

	SELECT
		'-' AS towrsymb, '-' AS towrnazwa, '' , IF(n.zmagazynu=0,n.domagazynu,n.zmagazynu) AS magazyn,
		m.opis AS magnazwa,'' AS symbol,
		'' AS wartosc,
		'' AS ilosc, '' AS iloscbylo, '' AS iloscjest,
		'-'  AS jm,n.nrdokmag,
		'' AS uwagi, n.rodzaj, 1 AS lp, '' AS lokalizacja1, '' AS lokalizacja2, '-' AS idn, '' AS wyroznik
		FROM opng n
		LEFT JOIN Magazyny m ON m.IdnMagazynu= IF(n.zmagazynu=0,n.domagazynu,n.zmagazynu)
		WHERE n.guid = _GUID;

	ELSE

	SELECT
		o.towrsymb, o.towrnazwa, o.cenanet, IF(o.zmagazynu=0,o.domagazynu,o.zmagazynu) AS magazyn,
		m.opis AS magnazwa,IFNULL(k.symbol,'') AS symbol,
		ROUND(o.cenanet*o.ilosc,2) AS wartosc,
		o.ilosc, o.iloscbylo, o.iloscjest,
		j.nazwa AS jm,n.nrdokmag,
		o.uwagi, o.rodzaj, o.lp,t.lokalizacja1,t.lokalizacja2,t.idn,t.wyroznik,
		CONCAT(n2.datawplywu,'/',n2.nrdokmag) AS dostawa
		FROM oppo o
		INNER JOIN Jednostki j ON j.idn = o.jednostka
		LEFT JOIN opng n ON n.guid=o.idnoper
		LEFT JOIN TowrNg t ON o.idntowr=t.guid
		LEFT JOIN Magazyny m ON m.IdnMagazynu= IF(o.zmagazynu=0,o.domagazynu,o.zmagazynu)
		LEFT JOIN kontrah k ON t.dostawcaGUID=k.guid
		LEFT JOIN oppo o2 ON o2.guid = o.GUID_dostawy
		LEFT JOIN opng n2 ON n2.guid = o2.idnoper
		WHERE o.idnoper = _GUID
		--	AND NOT (o.iloscbylo=0)
		ORDER BY t.idn;
	END IF;
END IF;


/*
*******************************************************************************
	SPEC17- wydruk specyfikacji wz do FV
*******************************************************************************
 */
	IF _Symbol = 'SPEC17' THEN
	SELECT 
		n.nrdokmag,n.datawystaw,n.netto,n.brutto 
		FROM oppo o 
		LEFT JOIN opng n ON o.idnoper=n.guid
		WHERE o.idnwz=_GUID
		GROUP BY n.guid	
		ORDER BY n.datawystaw; 
	END IF;	
	
	
/*
*******************************************************************************
    SPEC18- wydruk arkusza spisowego/specyfikacji transbud
******************************************************************************
  */   
	IF _Symbol = 'SPEC18' THEN
    SELECT
       o.towrsymb, o.towrnazwa, t.pcn,
       o.ilosc,
       (SELECT valuex FROM v_kkn4 WHERE rok IN (0, 2014) AND klucz=N.katcol4) AS katcol4Str,
	    (SELECT param1 FROM v_ktw0 WHERE rok IN (0, 2014) AND klucz=o.kat5) AS param1,
         (SELECT valuex FROM v_kkn0 WHERE rok IN (0, 2014) AND klucz=o.kat0) AS katcol0Str
       FROM oppo o
       LEFT JOIN opng N ON o.idnoper=N.guid
       LEFT JOIN TowrNg t ON o.idntowr=t.guid
       WHERE o.idnoper = _GUID
           AND t.wyroznik='W'
       ORDER BY o.towrsymb;
  END IF;        


/*
*******************************************************************************
    SPEC19- platnik
*****************************************************************************
*/

    
	IF _Symbol = 'SPEC19' THEN
  SELECT
     k_p.analityka
       FROM  opng N 
       LEFT JOIN kontrah k_p ON N.platnikGUID=k_p.GUID
       WHERE N.guid = _GUID;
         
  END IF;        
/*
*******************************************************************************
	SPEC20 - grupowanie po grupie transportowej (param 1 KC3)
	
	CALL fr_ht_fv_spec('SPEC3','{7D31F2F9-12C3-4E70-A7AB-53C00E50D139}','','','');
*******************************************************************************
*/
	IF _Symbol = 'SPEC20' THEN
		DROP TABLE IF EXISTS grupa_t;
        CREATE TEMPORARY TABLE grupa_t
		(grupa_transportowa INT ,
		grupa_str VARCHAR(38) DEFAULT '',
		ilosc DOUBLE(16,2) NOT NULL DEFAULT '0.00',
		mnoznik INT,
		razem DOUBLE(16,4) NOT NULL DEFAULT '0.0000');
		
        INSERT INTO grupa_t
        SELECT
		'1',
		'Kategoria transportowa 1',
		IFNULL(SUM(o.ilosc*t.objetosc),0),
		IFNULL(KC3.param2,1),
		IFNULL(SUM(o.ilosc*t.objetosc * IF(KC3.param2='',1,KC3.param2)),0)		
		FROM oppo o
		INNER JOIN TowrNg t ON t.GUID = o.idntowr
		INNER JOIN opng n ON (n.guid=o.idnoperfin OR n.guid=o.idnoper)
		LEFT JOIN katcols KC3 ON t.katcol3 = KC3.klucz AND KC3.colnr = 4 AND KC3.typ =1 
		WHERE n.GUID = _GUID AND KC3.param1=1;

        INSERT INTO grupa_t
        SELECT
		'2',
		'Kategoria transportowa 2',
		IFNULL(SUM(o.ilosc*t.objetosc),0),
		IFNULL(KC3.param2,2),
		IFNULL(SUM(o.ilosc*t.objetosc * IF(KC3.param2='',1,KC3.param2)),0)		
		FROM oppo o
		INNER JOIN TowrNg t ON t.GUID = o.idntowr
		INNER JOIN opng n ON (n.guid=o.idnoperfin OR n.guid=o.idnoper)
		LEFT JOIN katcols KC3 ON t.katcol3 = KC3.klucz AND KC3.colnr = 4 AND KC3.typ =1 
		WHERE n.GUID = _GUID  AND KC3.param1=2;

        INSERT INTO grupa_t
        SELECT
		'3',
		'Kategoria transportowa 3',
		IFNULL(SUM(o.ilosc*t.objetosc),0),
		IFNULL(KC3.param2,3),
		IFNULL(SUM(o.ilosc*t.objetosc* IF(KC3.param2='',1,KC3.param2)),0)		
		FROM oppo o
		INNER JOIN TowrNg t ON t.GUID = o.idntowr
		INNER JOIN opng n ON (n.guid=o.idnoperfin OR n.guid=o.idnoper)
		LEFT JOIN katcols KC3 ON t.katcol3 = KC3.klucz AND KC3.colnr = 4 AND KC3.typ =1 
		WHERE n.GUID = _GUID AND KC3.param1=3;
		
		
		SELECT * FROM grupa_t;

		DROP TABLE IF EXISTS grupa_t;
	
	END IF;

/*
*******************************************************************************
	SPEC21 - specyfikacja Stanpol 
	
	
*******************************************************************************
*/
IF _Symbol = 'SPEC21' THEN
	
	SELECT "fr_ht_fv_spec_SPEC20" AS fr_ht_fv_spec_SPEC20,
  IF(T.nazwa=P.towrnazwa, IFNULL(( SELECT tostr FROM lang_translations WHERE to_lang=N.lang_id AND object_name="TowrNg" AND object_key=P.IdnTowr LIMIT 1), P.towrnazwa), P.towrnazwa) AS towrnazwaTranslate, 
  P.nrseryjny,T.NazwaUProducenta, P.ilosc AS Ilosc,  (P.Ilosc/P.ilewopakowaniu) AS ileopakowan, P.ilewopakowaniu,T.waga AS wagatowaru, 
  P.waga_pozycji,P.waga,P.termin_waznosci AS terminrealizacji,
  IF(P.kat5 > 0, (SELECT kc.valuex FROM katcols kc WHERE kc.klucz=P.kat5 AND kc.typ=1 AND kc.colnr=1), "" ) AS katcol5Str,
  IF(P.kat6 > 0, (SELECT kc.valuex FROM katcols kc WHERE kc.klucz=P.kat6 AND kc.typ=1 AND kc.colnr=2), "" ) AS katcol6Str,
  IF(P.kat7 > 0, (SELECT kc.valuex FROM katcols kc WHERE kc.klucz=P.kat7 AND kc.typ=1 AND kc.colnr=3), "" ) AS katcol7Str,
  IF(P.kat8 > 0, (SELECT kc.valuex FROM katcols kc WHERE kc.klucz=P.kat8 AND kc.typ=1 AND kc.colnr=4), "" ) AS katcol8Str,
  IF(P.kat9 > 0, (SELECT kc.valuex FROM katcols kc WHERE kc.klucz=P.kat9 AND kc.typ=1 AND kc.colnr=5), "" ) AS katcol9Str,
  IF(P.kat5 > 0, (SELECT kc.param1 FROM katcols kc WHERE kc.klucz=P.kat5 AND kc.typ=1 AND kc.colnr=1), "" ) AS katcol5param1,
  IF(P.kat5 > 0, (SELECT kc.param2 FROM katcols kc WHERE kc.klucz=P.kat5 AND kc.typ=1 AND kc.colnr=1), "" ) AS katcol5param2,
  IFNULL((SELECT d.datawplywu FROM oppo d WHERE P.guid_dostawy=d.guid),'brak dostawy') AS data_produkcji_po_dost,
  IFNULL((SELECT d.info3 FROM oppo d WHERE P.guid_dostawy=d.guid),'brak dostawy') AS data_polowu,
  IFNULL((SELECT s.datawplywu FROM oppo s WHERE P.nrseryjny=s.nrseryjny AND s.rodzaj IN (12,13) LIMIT 1),'brak dostawy') AS data_produkcji_po_ser
  FROM opng N
  INNER JOIN oppo P ON P.idnoper = N.GUID OR P.idnoperfin = N.GUID 
  LEFT JOIN TowrNg T ON T.GUID = P.idntowr
  LEFT JOIN Jednostki J ON J.idn = P.jednostka
  LEFT JOIN Jednostki JP ON JP.idn = T.jednostka_pom
  WHERE N.GUID = _GUID;
	
	
	END IF;
	
/********************************************************************************
    SPEC22 - odpady Transbud, zdublowane juz takze w spec2 fr_ht_OP dla koncowych po zaliczkach
******************************************************************************/

    
	IF _Symbol = 'SPEC22' THEN
  SELECT
     k.KRS AS numer_dowodu,
     (SELECT IFNULL(SUM(op.ilosc),0) FROM oppo op,TowrNg t WHERE op.idnoperfin=N.guid AND op.idntowr=t.guid AND t.wyroznik like 'A%' ) AS akcyza,
     (SELECT IFNULL(SUM(op.ilosc),0) FROM oppo op,TowrNg t WHERE op.idnoperfin=N.guid AND op.idntowr=t.guid AND t.wyroznik='W' ) AS wegiel
       FROM  opng N 
       LEFT JOIN kontrah k ON N.kontrguid=k.GUID
       WHERE N.guid = _GUID;
         
  END IF;        

/*
*******************************************************************************
    SPEC23 - parametry węgla Transbud
*****************************************************************************
*/
	IF _Symbol = 'SPEC23' THEN
	
	DROP TEMPORARY TABLE IF EXISTS cert;
	
	IF DATABASE()= 'ht' THEN
	CREATE TEMPORARY TABLE cert
	SELECT 
	o.idntowr,o.towrsymb,o.ilosc,o.kat5,CONCAT('Węgiel kamienny - ',IF(k0.descr<>'',CONCAT(k0.valuex,' - "',k0.descr,'"'),k0.param3)) AS param3,
	IF(ifnull(o.info2,'')='',CONCAT(o.towrnazwa,' - ',k0.valuex),o.info2) AS towrnazwa,
	k0.param3 AS param3join,
	MAX(_varDate(sp.ParamExtra))  AS datacert,
	IF(n.datawystaw<'2021-12-15',n.nrdokfin,CONCAT(n.nrdokfin,'_',o.lp)) AS cert_trans,
	k0.param3 AS grupa,CONCAT('Węgiel kamienny - ',k0.param3) AS rodzajwegla
	FROM oppo o
	LEFT JOIN opng n ON o.idnoperfin=n.guid 
	LEFT JOIN  katcols k0 ON k0.klucz=o.kat5 AND k0.colnr=1 AND k0.typ=1
	LEFT JOIN slowniki_pierwotne_def spd ON k0.param3=spd.param3
	LEFT JOIN slowniki_pierwotne sp ON spd.guid=sp.Slownik_GUID AND sp.ParamExtra <=o.datawystaw
	LEFT JOIN TowrNg tn ON o.idntowr=tn.guid
	WHERE o.idnoperfin=_GUID AND tn.wyroznik='W' AND k0.param3<>'' GROUP BY k0.param3 ;
	
	ELSE
	
	DROP TEMPORARY TABLE IF EXISTS cert_prev;
	CREATE TEMPORARY TABLE cert_prev
	SELECT 
	o.idntowr,o.towrsymb,o.ilosc,o.kat5,CONCAT('Węgiel kamienny - ',IF(k0.descr<>'',CONCAT(k0.valuex,' - "',k0.descr,'"'),k0.param3)) AS param3,
	IF(ifnull(o.info2,'')='',CONCAT(o.towrnazwa,' - ',k0.valuex),o.info2) AS towrnazwa,
	k0.param3 AS param3join,
	MAX(_varDate(sp.ParamExtra))  AS datacert,
	IF(n.datawystaw<'2021-12-15',n.nrdokfin,CONCAT(n.nrdokfin,'_',o.lp)) AS cert_trans,
	k0.param3 AS grupa,CONCAT('Węgiel kamienny - ',k0.param3) AS rodzajwegla
	FROM oppo o 
	LEFT JOIN opng n ON o.idnoperfin=n.guid 
	LEFT JOIN  ht.katcols k0 ON o.idntowr=k0.guidlink AND k0.colnr=1 AND k0.typ=1 AND nieaktywna=0
	LEFT JOIN ht.slowniki_pierwotne_def spd ON k0.param3=spd.param3
	LEFT JOIN ht.slowniki_pierwotne sp ON spd.guid=sp.Slownik_GUID AND sp.ParamExtra <=o.datawystaw
	LEFT JOIN TowrNg tn ON o.idntowr=tn.guid
	WHERE o.idnoperfin=_GUID AND tn.wyroznik='W' AND k0.param3<>'' AND IFNULL(sp.ParamExtra,'')<>''  GROUP BY k0.param3 ;
      --  UPDATE cert_prev SET param3=CONCAT('Węgiel kamienny - ','miał') WHERE param3 LIKE '%miał%';

    CREATE TEMPORARY TABLE cert
    SELECT * FROM cert_prev GROUP BY param3;
        
	END IF;

    SELECT valueA INTO @nip FROM IReg WHERE IdKey LIKE 'NIP' AND IdPath LIKE '%DaneFirmy%' LIMIT 1;
	SELECT valueA INTO @regon FROM IReg WHERE IdKey LIKE 'REGON' AND IdPath LIKE '%DaneFirmy%' LIMIT 1;
	SELECT 
	c.*,
	@nip AS nip,@regon AS regon,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='ZP' LIMIT 1),'') AS ZP,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='ZP' LIMIT 1),'') AS ZP_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='ZP' LIMIT 1),'') AS ZP_max,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='ZCL' LIMIT 1),'') AS ZCL,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='ZCL' LIMIT 1),'') AS ZCL_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='ZCL' LIMIT 1),'') AS ZCL_max,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='WO' LIMIT 1),'') AS WO,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='WO' LIMIT 1),'') AS WO_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='WO' LIMIT 1),'') AS WO_max,	
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='ZS' LIMIT 1),'') AS ZS,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='ZS' LIMIT 1),'') AS ZS_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='ZS' LIMIT 1),'') AS ZS_max,	
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='WZ' LIMIT 1),'') AS WZ,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='WZ' LIMIT 1),'') AS WZ_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='WZ' LIMIT 1),'') AS WZ_max,	
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='ZPZ' LIMIT 1),'') AS ZPZ,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='ZPZ' LIMIT 1),'') AS ZPZ_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='ZPZ' LIMIT 1),'') AS ZPZ_max,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='ZNZ' LIMIT 1),'') AS ZNZ,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='ZNZ' LIMIT 1),'') AS ZNZ_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='ZNZ' LIMIT 1),'') AS ZNZ_max,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='ZWC' LIMIT 1),'') AS ZWC,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='ZWC' LIMIT 1),'') AS ZWC_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='ZWC' LIMIT 1),'') AS ZWC_max,	
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='SC' LIMIT 1),'') AS ZWC,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='SC' LIMIT 1),'') AS ZWC_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='SC' LIMIT 1),'') AS ZWC_max,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert AND sp.MasterKey='SC' LIMIT 1),'') AS SC,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='minimalnie' AND sp.MasterKey='SC' LIMIT 1),'') AS SC_min,
	IFNULL((SELECT sp.param2 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra='maksymalnie' AND sp.MasterKey='SC' LIMIT 1),'') AS SC_max,	
	
	
	IFNULL((SELECT sp.ParamExtra FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),'') AS Data_od,
	IF (DATABASE()IN ('skweglazoosk', 'ht'),
	IFNULL((SELECT sp.param3 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),''),CONCAT(IFNULL((SELECT sp.param4 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),''),'/',c.grupa,'/',
	YEAR(IFNULL((SELECT sp.ParamExtra FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),'') ),'/TRANS')) AS cert_nr
	-- IF (DATABASE()= 'ht',
	-- CONCAT(IFNULL((SELECT sp.param4 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),''),'/',c.param3,'/',
	-- YEAR(IFNULL((SELECT sp.ParamExtra FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),'') ),'/TRANS'),
	-- CONCAT(IFNULL((SELECT sp.param4 FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),''),'/',c.param3,'/',
	-- YEAR(IFNULL((SELECT sp.ParamExtra FROM ht.slowniki_pierwotne sp WHERE spd.guid=sp.Slownik_GUID  AND sp.ParamExtra=c.datacert LIMIT 1 ),'') ),'/SK')) AS cert_trans
	FROM cert c LEFT JOIN ht.slowniki_pierwotne_def spd ON spd.param3=c.param3join;
	
	
    DROP TEMPORARY TABLE IF EXISTS cert;
    DROP TEMPORARY TABLE IF EXISTS cert_prev;
 
  END IF;              

	
/*
*******************************************************************************
    SPEC24 - Różnice inwentaryzacyjne z dokumentu
*****************************************************************************
*/
	IF _Symbol = 'SPEC24' THEN
 
    SELECT MAX(datawplywu) INTO @bo FROM oppo WHERE rodzaj=51;
    SELECT datawplywu, domagazynu INTO @datainw, @magazyn FROM opng WHERE guid = _GUID;


    DROP TABLE IF EXISTS StanMag;
    CREATE TEMPORARY TABLE StanMag
    SELECT om.magazyn,o.idntowr, SUM(om.ilosc) AS ilosc_r, SUM(IF(om.przychod,o.wartmag,-o.wartmag)) AS wartosc_r
    FROM oppo_mag om
    INNER JOIN oppo o ON o.guid=om.guid
    WHERE om.tymczasowa = 0  AND o.usluga=0   AND om.DataWplywu >= @bo AND om.DataWplywu <= @datainw AND om.magazyn=@magazyn
    GROUP BY o.idntowr;
  

   
    SELECT
    n.nrdokmag,n.nrdokfin,
    IF(n.domagazynu<>0,n.domagazynu,n.zmagazynu) AS magazyn,
    t.symbol AS towar,t.nazwa,
    j.nazwa AS jednostka,
    ROUND(o.iloscbylo,4) AS iloscbylo,
    IF(o.domagazynu<>0,ABS(o.ilosc),0) AS ilosc_plus,
    IF(o.zmagazynu<>0,ABS(o.ilosc),0) AS ilosc_minus,
    IF(o.domagazynu<>0,o.wartmag,0) AS wart_plus,
    IF(o.zmagazynu<>0,o.wartmag,0) AS wart_minus,
    o.iloscbylo+o.ilosc AS ilosc_jest,

    t.cenaewid,
    ROUND(IFNULL(s.wartosc_r,0),2) AS wartosc_jest,
    IFNULL(s.wartosc_r-IF(o.domagazynu=0,-1,1)*o.wartmag,0) AS wartosc_bylo

    FROM opng n
    INNER JOIN oppo o ON n.guid=o.idnoper
    INNER JOIN TowrNg t ON t.guid=o.idntowr
    LEFT JOIN StanMag s ON s.idntowr=o.idntowr AND (IF(n.domagazynu<>0,n.domagazynu,n.zmagazynu))=s.magazyn
    LEFT JOIN Jednostki j ON j.idn=t.jednostka
    WHERE n.guid=_GUID;

    DROP TABLE IF EXISTS StanMag;

    END IF;


	IF _Symbol = 'SPEC25' THEN
	
 
    SELECT IFNULL(v1.Nr_Rej,'') AS samochod,IFNULL(v2.Nr_Rej,'') AS naczepa,n.guid,IFNULL(c.StartTime,'') AS StartTime,IFNULL(c.EndTime,'') AS EndTime FROM opng n LEFT JOIN  crm_commisions c
    ON n.wyjazdGUID=c.guid
    LEFT JOIN crm_spedycje s ON s.commisionGUID=c.guid
    LEFT JOIN erp_vehicles v1 ON s.Veh_Guid=v1.guid
    LEFT JOIN erp_vehicles v2 ON s.Veh_Guid_2=v2.guid
    WHERE  n.guid=_GUID;

    END IF;


	IF _Symbol = 'SPEC25A' THEN
	
    SELECT adres  FROM kontakty k
    LEFT JOIN opng n ON n.kontrguid=k.kontrguid
    WHERE  n.guid=_GUID
    AND  k.typ_medium IN (0,2) AND k.adres<>'' ;
 
    END IF;
  
    IF _Symbol = 'SPEC25B' THEN
 
 SELECT 
	
		t.guid,t.symbol,
		k.valuex AS Rodzajwegla,

	
		IFNULL(REPLACE(c.liczba_glowna,'.',','),'') AS Netto,
		IFNULL(REPLACE(c.liczba_glowna_postep,'.',','),'') AS Brutto,
		c.pr_ilosc AS Tonaz,
		40-c.pr_ilosc AS Tonaz_sped,
		o.ilosc AS Ilosc_operacja,
		j.Nazwa AS Jednostka,
		(SELECT nrzamw FROM zmng zn,zmpo zo WHERE zo.idnng=zn.guid AND zo.guid=o.guidzm) AS nrzamw
	
	FROM oppo o 
	LEFT JOIN opng n ON o.idnoper=n.guid 
	LEFT JOIN opng f ON f.guid=o.idnoperfin 
	LEFT JOIN Jednostki j ON j.idn=o.jednostka
	LEFT JOIN TowrNg t ON o.idntowr=t.guid 
	LEFT JOIN v_ktw0 k  ON k.klucz=o.kat5  

	LEFT JOIN crm_commisions c ON c.guid=n.wyjazdguid AND n.wyjazdguid<>''



	WHERE 

		n.guid=_GUID;
		
 
   END IF;  	
  
 
  
	IF _Symbol = 'SPEC26' THEN
	
		SELECT kontrguid INTO _kontrGUID FROM opng WHERE guid = _GUID;
		DROP TEMPORARY TABLE IF EXISTS op_zwrot;

		CREATE TEMPORARY TABLE op_zwrot(
			symbol VARCHAR (50),
			nazwa VARCHAR (150),
			stan INT,
			ilosc INT,
			idntowr VARCHAR (38));

		INSERT INTO op_zwrot
			SELECT  t.symbol,t.nazwa,SUM(IF(idnoper=@guid,0,ilosc*-1)),
			SUM(IF(idnoper= _GUID, ilosc*-1,0)), idntowr FROM oppo_mag m, TowrNg t,opng n  WHERE m.idnoper=n.guid AND  m.idnTowr=t.guid AND t.opakowanie_zwrotne=1 AND n.kontrguid= _kontrGUID
			GROUP BY idntowr;

		SELECT symbol,nazwa,stan ,ilosc,idntowr FROM op_zwrot;

		DROP TEMPORARY TABLE IF EXISTS op_zwrot;
 
	END IF; 

	
	IF _Symbol = 'SPEC27' THEN -- dla kogo to jest do licha? sciagniete z procedury na serwerze Amicusa, wrrrr
	
		SELECT IFNULL(e.Symbol,'') AS Symbol, IFNULL(e.Nazwa,'') AS Nazwa,IFNULL(e.dane_adresowe,'') AS dane_adresowe, IFNULL(e.dane_kontaktowe,'') AS dane_kontaktowe 
		FROM opng n 
		LEFT JOIN kasy k ON k.idn=n.nrKasy
		LEFT JOIN `erp_division_schema` e ON k.ugrpguid=e.GUID 
		WHERE  n.guid=_GUID;

	END IF; 

    IF _Symbol = 'SPEC28' THEN
		
		DROP TEMPORARY TABLE IF EXISTS t_spec;
		CREATE TEMPORARY TABLE t_spec
		SELECT
			o.towrnazwa,
			o.towrsymb,
			IFNULL(t.pcn,'') AS pcn,
			o.Jednostka,
			CONCAT(ROUND(o.ilewopakowaniu,2),' ',j.nazwa) AS rodz_op,
			ROUND((o.Ilosc/o.ilewopakowaniu)) AS ileopakowan,
			ROUND(o.ilewopakowaniu) AS ile_w_opak,
			ROUND(IF(o.jednostka=86,(o.Ilosc/o.ilewopakowaniu)*o.ilewopakowaniu,(o.Ilosc/o.ilewopakowaniu)*o.ilewopakowaniu*t.waga),2) AS waganetto,
			ROUND(IF(o.jednostka=86,(o.Ilosc/o.ilewopakowaniu)*o.ilewopakowaniu,(o.Ilosc/o.ilewopakowaniu)*o.ilewopakowaniu*t.waga) +
			((o.Ilosc/o.ilewopakowaniu)*IF(o.wymiar_x>0,o.wymiar_x,
			(IF(IFNULL(t.opak_waga_papier,0)='',0,t.opak_waga_papier)+
			IF(IFNULL(t.opak_waga_drewno,0)='',0,t.opak_waga_drewno)+
			IF(IFNULL(t.opak_waga_tw_sztuczne,0)='',0,t.opak_waga_tw_sztuczne)+
			IF(IFNULL(t.opak_waga_metal,0)='',0,t.opak_waga_metal)))),2) AS wagabrt,
			ROUND(o.wartnetwal,2) AS wart_wal,
			CONCAT(k_d.symbol,'\n',krp.nazwa) AS dostawca
		FROM oppo o 
		LEFT JOIN TowrNg t ON o.idntowr=t.guid
		LEFT JOIN Jednostki j ON o.Jednostka=j.idn
		LEFT JOIN kontrah k_d ON k_d.guid=t.dostawcaGUID
		LEFT JOIN Miasta mip ON mip.NazwaMiasta = k_d.NazwaMiasta  
		LEFT JOIN Kraje krp ON krp.idn_kraju = mip.IDNKraju
		WHERE  t.wyroznik<>'TR' AND idnoperfin=_GUID
		ORDER BY o.lp;
		
		
		IF _PARAM_A = 'GROUP' THEN
			SELECT pcn,SUM(ileopakowan) AS ile_opak,SUM(waganetto) AS waganetto,SUM(wagabrt) AS wagabrt,SUM(wart_wal) AS wart_wal FROM t_spec GROUP BY pcn;
		ELSE
			SELECT * FROM t_spec;
		END IF; 

        DROP TEMPORARY TABLE IF EXISTS t_spec;
		
	END IF; 	
		
    IF _Symbol = 'SPEC28_A' THEN
		
	
		SELECT
			o.lp,
			o.towrnazwa,
			o.towrsymb,
			o.wartnet,
			o.wartnetwal,
			t.wyroznik
		FROM oppo o 
		LEFT JOIN TowrNg t ON o.idntowr=t.guid
		LEFT JOIN Jednostki j ON o.Jednostka=j.idn
		LEFT JOIN kontrah k_d ON k_d.guid=t.dostawcaGUID
		LEFT JOIN Miasta mip ON mip.NazwaMiasta = k_d.NazwaMiasta  
		LEFT JOIN Kraje krp ON krp.idn_kraju = mip.IDNKraju
		WHERE t.wyroznik='TR' AND idnoperfin = _GUID
		ORDER BY o.lp;
		

	END IF; 
	
	IF _Symbol = 'SPEC29' THEN 
		
		SELECT
			wp.idn            AS IDN,
			sped.Kierowca     AS Kierowca,
-- ktw.valuex AS Towar,
			k.nazwa AS KontrahNazwa,
-- zn.nrzamw         AS nrzamw,
			wp.Create_At      AS Create_AT,
			wp.W1_At          AS W1_AT,
			wp.W2_At          AS W2_AT,
			IFNULL(TRIM(CONCAT(oW1.Imiona,' ',oW1.Nazwisko)),'-') AS W1_Osoba,
			IFNULL(TRIM(CONCAT(oW2.Imiona,' ',oW2.Nazwisko)),'-') AS W2_Osoba,
			IFNULL(v.Nr_Rej,sped.nr_rej) AS VEH1_Str,
			IFNULL(v2.Nr_Rej,sped.nr_rej_2) AS VEH2_Str,
			wp.W1             AS WagaPrzed,
			wp.W2             AS WagaPo,
			wp.W              AS WagaNetto
		FROM wazenia_po wp
		LEFT JOIN wazenia_ng wn ON wn.GUID = wp.NgGUID
		LEFT JOIN v_crm_spedycje_NOWY sped  ON sped.GUID = wn.FID
		LEFT JOIN erp_vehicles v ON v.guid = sped.Veh_GUID
		LEFT JOIN erp_vehicles v2 ON v2.guid = sped.Veh_GUID_2
-- LEFT JOIN v_ktw0 ktw ON ktw.klucz = p.kat5
		LEFT JOIN osoby oW1 ON oW1.GUID = wp.W1_UserGUID
		LEFT JOIN osoby oW2 ON oW2.GUID = wp.W2_UserGUID
		LEFT JOIN oppo p ON p.GUID = wp.W_ID
		LEFT JOIN v_ktw0 ktw ON ktw.klucz = p.kat5
		LEFT JOIN opng n ON n.GUID = p.idnoper
		LEFT JOIN kontrah k ON k.GUID = n.kontrGUID

        WHERE wp.IDN = _GUID;

	END IF; 	



/*********************************************
SPEC30 - wydruk nalepek w kancelarii jeszcze bez operacji, tylko na podstawie licznika
CALL fr_ht_fv_spec('SPEC30','{0D27E2C1-2795-4C70-BC5B-B667F98326B1}','','','');

**********************************************/
IF _Symbol = 'SPEC30' THEN

	CALL erp_obj_counters_GetNumber_and_build_number_text('', @_Numer, @_NumerText, 'opng.lp_ks_podawcza_str', 104, 0, 0, 0, '', YEAR(CURRENT_DATE()), MONTH(CURRENT_DATE()), DAY(CURRENT_DATE()), '', '', '', '', '');

	-- zawartość numer i numer sformatowany
	SELECT LPAD(@_Numer,9,0) AS numer, @_NumerText AS numer_text, CURRENT_DATE AS data_wydruku;

END IF; -- SPEC30

END$$

DELIMITER ;
