 DELIMITER $$


DROP PROCEDURE IF EXISTS `fr_ht_wyjazdy`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `fr_ht_wyjazdy`(
	IN _Symbol VARCHAR(38),
	IN _GUID VARCHAR(38),
	IN _Param_A VARCHAR(50),
	IN _Param_B VARCHAR(50),
	IN _Param_C VARCHAR(50)
)
BEGIN
	# RK1 - raport kierowcy (wyjazd kierowcy w danym dniu) dla CDG
	# LP1 - list przewozowy dla kierowcy, dla danego wyjazdu dla CDG
	# ZM1 - zamowienia zbiorcze/dokumenty dostawy dla danego klienta
	
	SET @Symbol = UPPER(_Symbol);
	SET @GUID = _GUID;
	
	SET @Param_A = _Param_A;
	SET @Param_B = _Param_B;
	SET @Param_C = _Param_C;
	
/*
*******************************************************************************
	RK1
CALL fr_ht_wyjazdy('RK1','fd5ef482-db2e-11e3-b1e2-001372373ee4*', '', '', '') 
*******************************************************************************
 */
IF _Symbol IN ('RK1','R_HT_RK1','R_HT_RK1B') THEN
	
	IF(@session.globals.Owner IN ('CDG')) THEN
		SELECT k.symbol, 
		CONCAT(k.nazwamiasta,' ',k.ulica) AS adres,
		CASE 
		WHEN EXISTS (SELECT geo.guid FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
						WHERE ean2.kontrGUID=k.guid AND geo.status IN (0, -1) AND ean2.type='LOC') 
		THEN (SELECT REPLACE(geo.adres, SUBSTRING(geo.adres,(SELECT LENGTH(geo.adres) - LOCATE('(', REVERSE(geo.adres))+1)
		,(SELECT LENGTH(geo.adres) - LOCATE(')', REVERSE(geo.adres))+1)	
		), '') FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
						WHERE ean2.kontrGUID=k.guid AND geo.status IN (0, -1) AND ean2.type='LOC' ORDER BY ean.idn DESC LIMIT 1) 
		ELSE 
			-- IF(IFNULL(ean_a.adres,'')='',CONCAT(k.nazwamiasta,' ',k.ulica),ean_a.adres)
			CASE 
			WHEN EXISTS (SELECT guid FROM erp_area_nodes ean 
							WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
			THEN (SELECT adres FROM erp_area_nodes ean 
							WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
			ELSE CONCAT(k.nazwamiasta,' ',k.ulica)
			END
		END AS adres_dost,
		SUBSTRING(c.StartTime,1,10) AS DataWyjazdu,
		k.opis_dzialalnosci AS opisd,	
		GROUP_CONCAT(DISTINCT(IFNULL(n.uwagi,'')),IFNULL(k.uwagi,'')) AS uwagi,
			
			-- chodzi ponoc o uwagi z zamowienia
		-- CASE WHEN @session.globals.Owner = 'CDG' THEN
		-- CONCAT(IFNULL((SELECT GROUP_CONCAT(opis,' ',adres SEPARATOR ', ') FROM kontakty WHERE kontrGUID=k.GUID AND typ_medium IN (0,2)),''),' ',IFNULL(n.uwagi,''),' ',IFNULL(k.uwagi,''))
		-- ELSE 
		-- CONCAT(IFNULL(n.uwagi,'')) 
	--	 END AS uwagi,
		(SELECT CONCAT('SMS: ',s.DisplayValue) FROM slowniki_pierwotne s WHERE s.slownik_guid="POWIADOMIENIA_STATUSY" AND n.status_powiadomien=s.value) AS statusSMS,
		
		k.treenode AS kategoria_drzewo,
		ean.nazwa AS rewir, edev.nazwa AS oddzial, 
		c.nrwewn,
		CONCAT(o.imiona,' ',o.nazwisko) AS osoba,
		-- IF(k.katcol1 = 21 and k.katcol2 = 2, 3, IF(k.katcol1 = 21, 2, IF(k.katcol2 = 2, 1, 0))) as klient_kluczowy,
		IF(k.katcol1 = 21 AND k.katcol2 = 2, 'KM', IF(k.katcol1 = 21, 'M', IF(k.katcol2 = 2, 'K', ''))) AS klient_kluczowy,
		SUM(IF(p.towrsymb='MIX2',p.ilosc,0)) AS k1,
		SUM(IF(p.towrsymb='MIX3',p.ilosc,0)) AS k2,
		SUM(IF(p.towrsymb IN ('MIX11'),p.ilosc,0)) AS k3,
		SUM(IF(p.towrsymb='P11',p.ilosc,0)) AS k4,
		SUM(IF(p.towrsymb IN ('P10','NP10','GP10'),p.ilosc,0)) AS k4a,
		SUM(IF(p.towrsymb IN ('P10','NP10'),p.ilosc,0)) AS k4b, -- wersja dla Krakowa
		SUM(IF(p.towrsymb='H10',p.ilosc,0)) AS k15,
		SUM(IF(p.towrsymb='SP11',p.ilosc,0)) AS k5,
		SUM(IF(p.towrsymb='L10',p.ilosc,0)) AS k6,
		SUM(IF(p.towrsymb='MIX33',p.ilosc,0)) AS k7,
		SUM(IF(p.towrsymb in ('P33','GP33'),p.ilosc,0)) AS k8,
		SUM(IF(p.towrsymb='S11',p.ilosc,0)) AS k11,
		SUM(IF(p.towrsymb='SML11',p.ilosc,0)) AS k12,
		SUM(IF(p.towrsymb='MIXbk',p.ilosc,0)) AS MIXbk,
		SUM(IF(p.towrsymb='BM',p.ilosc,0)) AS k13,
		SUM(IF(p.idntowr IN ('IM_babe18c3cdb6c12bd200de950d7df23a','IM_382e7677197ea84a5269aa5138abefd7','IM_2e013df360604abeadadcbce1dc1f3fc','IM_0da01b8515bd7a1fd86505892ec3129b'),p.ilosc,0)) AS k24, -- mix 11
		SUM(IF(p.idntowr ='{1C3F5FB5-5EEA-4A35-963A-1A3F484CD4C6}',p.ilosc,0)) AS k25, -- kolor 10
		SUM(IF(p.idntowr='IM_bc0bae79d4540613a8d070d56716389e',p.ilosc,0)) AS k26, -- syfon green	
		SUM(IF(p.idntowr='IM_3f85d6e2dc25698fce5d75816ce92f5e',p.ilosc,0)) AS k27, -- syfon
		SUM(IF(p.idntowr='IM_c24b084b5421b2e8ff902923fa072b4d',p.ilosc,0)) AS k28, -- moto
		SUM(IF(p.idntowr IN ('IM_530ddb93b07a08545c06235037487f73','IM_768b5a77ba3f0b528cd95c5942af7abc'),p.ilosc,0)) AS k29, -- light
		SUM(IF(p.idntowr='IM_58cfc45c737d3304bd59ddca7589935a',p.ilosc,0)) AS k30, -- prop 11
		SUM(IF(p.idntowr='IM_2ac292b5912d80fb704f8c0ce9f9e2c1',p.ilosc,0)) AS k31, -- mix 33
		SUM(IF(p.idntowr='IM_d8a1d413b2027a6945172433af2fbca1',p.ilosc,0)) AS k32, -- mix 22
		SUM(IF(p.idntowr IN ('IM_2224c1239dea6694ddfd61b7b19279b6'),p.ilosc,0)) AS k33, -- prop 33
		SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k34, -- prop 22
		SUM(IF(p.idntowr IN ('{D53E66CB-868E-4D76-A02C-31244F6B1CAB}'),p.ilosc,0)) AS k44,

		SUM(IF(p.towrnazwa LIKE '%dwutlenek%',p.ilosc,0)) AS k45, -- CO2
		SUM(IF(p.towrnazwa LIKE '%promiennik%',p.ilosc,0)) AS k46, -- promiennik
		SUM(IF(p.towrnazwa LIKE '%reduktor%',p.ilosc,0)) AS k47, -- reduktor
		SUM(IF(p.towrsymb IN ('CO2 5','CO2 6'),p.ilosc,0)) AS k48,  -- CO2 5/6 kg
		SUM(IF(p.towrsymb IN ('S15'),p.ilosc,0)) AS k49,  -- gaz butla 15 kg
		SUM(IF(p.towrsymb IN ('BS15'),p.ilosc,0)) AS k50,  -- butla 15 kg

		SUM(IF(p.towrsymb IN ('GP10'),p.ilosc,0)) AS k51,
				
		GROUP_CONCAT(CONCAT(p.towrsymb,'-',ROUND(p.wartbrt/p.ilosc,2))) AS ceny,
		IF(of.dataoferty<op.dataoferty AND of.cena<>op.cena,GROUP_CONCAT(CONCAT('N.CENA ',p.towrsymb,'-' ,ROUND(op.cena,2))),'') AS nowacena,
		SUM(p.wartbrt) AS wartosc,
		IF(k.rodzaj2=0,'P','F') AS fp,
		IF(LEFT(pl.nazwa,1)='G',CONCAT('G ',ROUND(SUM(p.wartbrt),2)),'przelew') AS platnosc,
		(SELECT IF(COUNT(*)>0,'Z','') FROM opng WHERE platnikguid=k.GUID AND grupaoper=0 
		AND nierozliczony>0 AND CURRENT_DATE>dataplatnosci) AS zadluzony,
		-- (SELECT GROUP_CONCAT(CONCAT(displayvalue,' - ',paramextra) SEPARATOR '\n')
	--		FROM slowniki_pierwotne WHERE Slownik_GUID='ZM_POWODY_NIEREALIZ') AS legenda
		'' AS legenda
		FROM crm_commisions c
		LEFT JOIN zmng n ON c.guid=n.wyjazdGUID
		LEFT JOIN zmpo p ON n.guid=p.idnNG
		LEFT JOIN kontrah k ON k.guid=n.kontrGUID
		LEFT JOIN platnosc pl ON IFNULL(n.sposobplatn,k.sposplat)=pl.idn 
		LEFT JOIN t_oferty_poczekalnia op ON n.kontrGUID=op.kontrguid AND p.idntowr=op.idntowr
		LEFT JOIN oferty of ON op.kontrGUID=of.kontrGUID AND op.idntowr=of.idntowr
		-- ??? left join erp_area_nodes ean_a on ean_a.kontrGUID=k.GUID and ean_a.type='LOC'-- ean_a.kontekst=3
		INNER JOIN erp_area_nodes ean ON ean.guid=c.areaGUID
		INNER JOIN erp_division_schema edev ON edev.guid=ean.dzial
		INNER JOIN crm_usr_links cu ON cu.commisionGUID=c.GUID
		INNER JOIN osoby o ON o.guid=cu.osoba
		WHERE c.GUID = _GUID AND n.status NOT IN (8,4)
		GROUP BY k.guid, n.sposobplatn
		ORDER BY n.numer_na_trasie_zm, k.nazwa
		; 
	ELSE -- czesc procedury dla MPGK
		IF (SELECT oddzial FROM v_wyjazdy WHERE guid=@GUID) ='PR' THEN
			SELECT
			'fr_ht_wyjazdy_rk_mpgk_pr' AS fr_ht_wyjazdy_rk_mpgk_pr,
			k.symbol,
			CONCAT(k.nazwamiasta,' ',k.ulica) AS adres,
			CASE 
			WHEN EXISTS (SELECT geo.guid FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
							WHERE ean2.kontrGUID=k.guid AND geo.status IN (0, -1) AND ean2.type='LOC') 
			THEN (SELECT geo.adres FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
							WHERE ean2.kontrGUID=k.guid AND geo.status IN (0, -1) AND ean2.type='LOC' ORDER BY ean.idn DESC LIMIT 1)
			ELSE 
				-- IF(IFNULL(ean_a.adres,'')='',CONCAT(k.nazwamiasta,' ',k.ulica),ean_a.adres)
				CASE 
				WHEN EXISTS (SELECT guid FROM erp_area_nodes ean 
								WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
				THEN (SELECT adres FROM erp_area_nodes ean 
								WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
				ELSE CONCAT(k.nazwamiasta,' ',k.ulica)
				END
			END AS adres_dost,
			SUBSTRING(c.StartTime,1,10) AS DataWyjazdu,
			k.opis_dzialalnosci AS opisd,	
				--  n.uwagi, -- chodzi ponoc o uwagi z zamowienia
			CASE WHEN @session.globals.Owner = 'CDG' THEN
			CONCAT(IFNULL((SELECT GROUP_CONCAT(opis,' ',adres SEPARATOR ', ') FROM kontakty WHERE kontrGUID=k.GUID AND typ_medium IN (0,2)),'')   ,' ',IFNULL(n.uwagi,''),' ',IFNULL(k.uwagi,''))
			ELSE 
			CONCAT(IFNULL(n.uwagi,'')) 
			END AS uwagi,
			(SELECT CONCAT('SMS: ',s.DisplayValue) FROM slowniki_pierwotne s WHERE s.slownik_guid="POWIADOMIENIA_STATUSY" AND n.status_powiadomien=s.value) AS statusSMS,
			
			k.treenode AS kategoria_drzewo,
			ean.nazwa AS rewir, edev.nazwa AS oddzial, 
			c.nrwewn,
			CONCAT(o.imiona,' ',o.nazwisko) AS osoba,
			-- IF(k.katcol1 = 21 and k.katcol2 = 2, 3, IF(k.katcol1 = 21, 2, IF(k.katcol2 = 2, 1, 0))) as klient_kluczowy,
			IF(k.katcol1 = 21 AND k.katcol2 = 2, 'KM', IF(k.katcol1 = 21, 'M', IF(k.katcol2 = 2, 'K', ''))) AS klient_kluczowy,
			SUM(IF(p.towrsymb='MIX2',p.ilosc,0)) AS k1,
			SUM(IF(p.towrsymb='MIX3',p.ilosc,0)) AS k2,
			SUM(IF(p.towrsymb='MIX11',p.ilosc,0)) AS k3,
			SUM(IF(p.towrsymb='P11',p.ilosc,0)) AS k4,
			SUM(IF(p.towrsymb='P10',p.ilosc,0)) AS k4a,
			SUM(IF(p.towrsymb='H10',p.ilosc,0)) AS k15,
			SUM(IF(p.towrsymb='SP11',p.ilosc,0)) AS k5,
			SUM(IF(p.towrsymb='L10',p.ilosc,0)) AS k6,
			SUM(IF(p.towrsymb='MIX33',p.ilosc,0)) AS k7,
			SUM(IF(p.towrsymb='P33',p.ilosc,0)) AS k8,
			SUM(IF(p.towrsymb='S11',p.ilosc,0)) AS k11,
			SUM(IF(p.towrsymb='SML11',p.ilosc,0)) AS k12,
			SUM(IF(p.towrsymb='MIXbk',p.ilosc,0)) AS MIXbk,
			SUM(IF(p.towrsymb='BM',p.ilosc,0)) AS k13,
			SUM(IF(p.idntowr IN ('IM_babe18c3cdb6c12bd200de950d7df23a','IM_382e7677197ea84a5269aa5138abefd7','IM_2e013df360604abeadadcbce1dc1f3fc'),p.ilosc,0)) AS k24, -- mix 11
			SUM(IF(p.idntowr ='{1C3F5FB5-5EEA-4A35-963A-1A3F484CD4C6}',p.ilosc,0)) AS k25, -- kolor 10
			SUM(IF(p.idntowr ='f921d792-a35e-11eb-9c17-525400eb203e',p.ilosc,0)) AS k25_1,
			SUM(IF(p.idntowr='IM_bc0bae79d4540613a8d070d56716389e',p.ilosc,0)) AS k26, -- syfon green	
			SUM(IF(p.idntowr='IM_3f85d6e2dc25698fce5d75816ce92f5e',p.ilosc,0)) AS k27, -- syfon
			SUM(IF(p.idntowr='IM_c24b084b5421b2e8ff902923fa072b4d',p.ilosc,0)) AS k28, -- moto
			SUM(IF(p.idntowr IN ('IM_530ddb93b07a08545c06235037487f73','IM_768b5a77ba3f0b528cd95c5942af7abc'),p.ilosc,0)) AS k29, -- light
			SUM(IF(p.idntowr='53076752-6158-11eb-b7c8-525400eb203e',p.ilosc,0)) AS k30, -- prop 10
			SUM(IF(p.idntowr='{9EE90DC5-EAD9-4645-889E-5E11EC829725}',p.ilosc,0)) AS k31, -- prop 10
			SUM(IF(p.idntowr='IM_0da01b8515bd7a1fd86505892ec3129b',p.ilosc,0)) AS k32, -- mix 11 kolor
			SUM(IF(p.idntowr IN ('IM_2224c1239dea6694ddfd61b7b19279b6'),p.ilosc,0)) AS k33, -- prop 33
			SUM(IF(p.idntowr='{80D97594-602E-429D-A060-1F81FE007E9E}',p.ilosc,0)) AS k34, -- prop 22
			SUM(IF(p.towrsymb IN ('CO2 5','CO2 6'),p.ilosc,0)) AS k48,  -- CO2 5/6 kg
			SUM(IF(p.towrsymb IN ('S15'),p.ilosc,0)) AS k49,  -- gaz butla 15 kg
			SUM(IF(p.towrsymb IN ('BS15'),p.ilosc,0)) AS k50,  -- butla 15 kg

			SUM(IF(p.towrsymb IN ('GP10'),p.ilosc,0)) AS k51,

			SUM(IF(p.towrsymb IN ('SYFON MOTO 11KG KOMPOZYT'),p.ilosc,0)) AS k52, -- moto light 11 kompozyt
			SUM(IF(p.towrsymb IN ('PROPAN 30KG GREENGAS'),p.ilosc,0)) AS k53, -- prop 30 GG
			SUM(IF(p.towrsymb IN ('PROPAN 30KG KOLOR'),p.ilosc,0)) AS k54, -- prop 30 kolor
			SUM(IF(p.towrsymb IN ('PROPAN 22KG'),p.ilosc,0)) AS k55, -- prop 22 kg
				
			GROUP_CONCAT(CONCAT(p.towrsymb,'-',ROUND(p.wartbrt/p.ilosc,2))) AS ceny,
			GROUP_CONCAT(IF(of.dataoferty<op.dataoferty AND of.cena<>op.cena,CONCAT('N.CENA ',p.towrsymb,'-' ,ROUND(op.cena,2)),NULL)) AS nowacena,
			SUM(p.wartbrt) AS wartosc,
			IF(k.rodzaj2=0,'P','F') AS fp,
			IF(LEFT(pl.nazwa,1)='G',CONCAT('G ',ROUND(SUM(p.wartbrt),2)),'przelew') AS platnosc,
			(SELECT IF(COUNT(*)>0,'Z','') FROM opng WHERE platnikguid=k.GUID AND grupaoper=0 
			AND nierozliczony>0 AND CURRENT_DATE>dataplatnosci) AS zadluzony,
			(SELECT GROUP_CONCAT(CONCAT(displayvalue,' - ',paramextra) SEPARATOR '\n')
				FROM slowniki_pierwotne WHERE Slownik_GUID='ZM_POWODY_NIEREALIZ') AS legenda,
				n.numer_na_trasie_zm
			FROM crm_commisions c
			LEFT JOIN zmng n ON c.guid=n.wyjazdGUID
			LEFT JOIN zmpo p ON n.guid=p.idnNG
			LEFT JOIN kontrah k ON k.guid=n.kontrGUID
			LEFT JOIN platnosc pl ON IFNULL(n.sposobplatn,k.sposplat)=pl.idn 
			LEFT JOIN t_oferty_poczekalnia op ON n.kontrGUID=op.kontrguid AND p.idntowr=op.idntowr
			LEFT JOIN oferty of ON op.kontrGUID=of.kontrGUID AND op.idntowr=of.idntowr
			-- ??? left join erp_area_nodes ean_a on ean_a.kontrGUID=k.GUID and ean_a.type='LOC'-- ean_a.kontekst=3
			INNER JOIN erp_area_nodes ean ON ean.guid=c.areaGUID
			INNER JOIN erp_division_schema edev ON edev.guid=ean.dzial
			INNER JOIN crm_usr_links cu ON cu.commisionGUID=c.GUID
			INNER JOIN osoby o ON o.guid=cu.osoba
			WHERE c.GUID = _GUID AND n.deleted=0 AND n.ilepoz<>n.ilepozzreal
			GROUP BY k.guid, n.sposobplatn
			
			ORDER BY  n.numer_na_trasie_zm, k.nazwa
			;
		ELSE
			SELECT
			'fr_ht_wyjazdy_rk_mpgk' AS fr_ht_wyjazdy_rk_mpgk,
			k.symbol,
			CONCAT(k.nazwamiasta,' ',k.ulica) AS adres,
			CASE 
			WHEN EXISTS (SELECT geo.guid FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
							WHERE ean2.kontrGUID=k.guid AND geo.status IN (0, -1) AND ean2.type='LOC') 
			THEN (SELECT geo.adres FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
							WHERE ean2.kontrGUID=k.guid AND geo.status IN (0, -1) AND ean2.type='LOC' ORDER BY ean.idn DESC LIMIT 1)
			ELSE 
				-- IF(IFNULL(ean_a.adres,'')='',CONCAT(k.nazwamiasta,' ',k.ulica),ean_a.adres)
				CASE 
				WHEN EXISTS (SELECT guid FROM erp_area_nodes ean 
								WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
				THEN (SELECT adres FROM erp_area_nodes ean 
								WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
				ELSE CONCAT(k.nazwamiasta,' ',k.ulica)
				END
			END AS adres_dost,
			SUBSTRING(c.StartTime,1,10) AS DataWyjazdu,
			k.opis_dzialalnosci AS opisd,	
				--  n.uwagi, -- chodzi ponoc o uwagi z zamowienia
			CASE WHEN @session.globals.Owner = 'CDG' THEN
			CONCAT(IFNULL((SELECT GROUP_CONCAT(opis,' ',adres SEPARATOR ', ') FROM kontakty WHERE kontrGUID=k.GUID AND typ_medium IN (0,2)),'')   ,' ',IFNULL(n.uwagi,''),' ',IFNULL(k.uwagi,''))
			ELSE 
			CONCAT(IFNULL(n.uwagi,'')) 
			END AS uwagi,
			(SELECT CONCAT('SMS: ',s.DisplayValue) FROM slowniki_pierwotne s WHERE s.slownik_guid="POWIADOMIENIA_STATUSY" AND n.status_powiadomien=s.value) AS statusSMS,
			
			k.treenode AS kategoria_drzewo,
			ean.nazwa AS rewir, edev.nazwa AS oddzial, 
			c.nrwewn,
			CONCAT(o.imiona,' ',o.nazwisko) AS osoba,
			-- IF(k.katcol1 = 21 and k.katcol2 = 2, 3, IF(k.katcol1 = 21, 2, IF(k.katcol2 = 2, 1, 0))) as klient_kluczowy,
			IF(k.katcol1 = 21 AND k.katcol2 = 2, 'KM', IF(k.katcol1 = 21, 'M', IF(k.katcol2 = 2, 'K', ''))) AS klient_kluczowy,
			SUM(IF(p.towrsymb='MIX2',p.ilosc,0)) AS k1,
			SUM(IF(p.towrsymb='MIX3',p.ilosc,0)) AS k2,
			SUM(IF(p.towrsymb='MIX11',p.ilosc,0)) AS k3,
			SUM(IF(p.towrsymb='P11',p.ilosc,0)) AS k4,
			SUM(IF(p.towrsymb='P10',p.ilosc,0)) AS k4a,
			-- SUM(IF(p.towrsymb IN ('P10','NP10'),p.ilosc,0)) AS k4a,
			SUM(IF(p.towrsymb='H10',p.ilosc,0)) AS k15,
			SUM(IF(p.towrsymb='SP11',p.ilosc,0)) AS k5,
			SUM(IF(p.towrsymb='L10',p.ilosc,0)) AS k6,
			SUM(IF(p.towrsymb='MIX33',p.ilosc,0)) AS k7,
			SUM(IF(p.towrsymb='P33',p.ilosc,0)) AS k8,
			SUM(IF(p.towrsymb='S11',p.ilosc,0)) AS k11,
			SUM(IF(p.towrsymb='SML11',p.ilosc,0)) AS k12,
			SUM(IF(p.towrsymb='MIXbk',p.ilosc,0)) AS MIXbk,
			SUM(IF(p.towrsymb='BM',p.ilosc,0)) AS k13,
			SUM(IF(p.idntowr IN ('IM_babe18c3cdb6c12bd200de950d7df23a','IM_382e7677197ea84a5269aa5138abefd7','IM_2e013df360604abeadadcbce1dc1f3fc'),p.ilosc,0)) AS k24, -- mix 11
			SUM(IF(p.idntowr ='{1C3F5FB5-5EEA-4A35-963A-1A3F484CD4C6}',p.ilosc,0)) AS k25, -- kolor 10
			SUM(IF(p.idntowr ='f921d792-a35e-11eb-9c17-525400eb203e',p.ilosc,0)) AS k25_1,
			SUM(IF(p.idntowr='IM_bc0bae79d4540613a8d070d56716389e',p.ilosc,0)) AS k26, -- syfon green	
			SUM(IF(p.idntowr='IM_3f85d6e2dc25698fce5d75816ce92f5e',p.ilosc,0)) AS k27, -- syfon
			SUM(IF(p.idntowr='IM_c24b084b5421b2e8ff902923fa072b4d',p.ilosc,0)) AS k28, -- moto
			SUM(IF(p.idntowr IN ('IM_530ddb93b07a08545c06235037487f73','IM_768b5a77ba3f0b528cd95c5942af7abc'),p.ilosc,0)) AS k29, -- light
			SUM(IF(p.idntowr='53076752-6158-11eb-b7c8-525400eb203e',p.ilosc,0)) AS k30, -- prop 10
			SUM(IF(p.idntowr='{9EE90DC5-EAD9-4645-889E-5E11EC829725}',p.ilosc,0)) AS k31, -- prop 10
			SUM(IF(p.idntowr='IM_0da01b8515bd7a1fd86505892ec3129b',p.ilosc,0)) AS k32, -- mix 11 kolor
			SUM(IF(p.idntowr IN ('IM_2224c1239dea6694ddfd61b7b19279b6'),p.ilosc,0)) AS k33, -- prop 33
			SUM(IF(p.idntowr='{80D97594-602E-429D-A060-1F81FE007E9E}',p.ilosc,0)) AS k34, -- syfon moto 15 kg
			SUM(IF(p.towrsymb IN ('CO2 5','CO2 6'),p.ilosc,0)) AS k48,  -- CO2 5/6 kg
			SUM(IF(p.towrsymb IN ('S15'),p.ilosc,0)) AS k49,  -- gaz butla 15 kg
			SUM(IF(p.towrsymb IN ('BS15'),p.ilosc,0)) AS k50,  -- butla 15 kg

			SUM(IF(p.towrsymb IN ('GP10'),p.ilosc,0)) AS k51,

			SUM(IF(p.towrsymb IN ('SYFON MOTO 11KG KOMPOZYT'),p.ilosc,0)) AS k52, -- moto light 11 kompozyt
			SUM(IF(p.towrsymb IN ('PROPAN 30KG GREENGAS'),p.ilosc,0)) AS k53, -- prop 30 GG
			SUM(IF(p.towrsymb IN ('PROPAN 30KG KOLOR'),p.ilosc,0)) AS k54, -- prop 30 kolor
			SUM(IF(p.towrsymb IN ('PROPAN 22KG'),p.ilosc,0)) AS k55, -- prop 22 kg
				
			GROUP_CONCAT(CONCAT(p.towrsymb,'-',ROUND(p.wartbrt/p.ilosc,2))) AS ceny,
			GROUP_CONCAT(IF(of.dataoferty<op.dataoferty AND of.cena<>op.cena,CONCAT('N.CENA ',p.towrsymb,'-' ,ROUND(op.cena,2)),NULL)) AS nowacena,
			SUM(p.wartbrt) AS wartosc,
			IF(k.rodzaj2=0,'P','F') AS fp,
			IF(LEFT(pl.nazwa,1)='G',CONCAT('G ',ROUND(SUM(p.wartbrt),2)),'przelew') AS platnosc,
			(SELECT IF(COUNT(*)>0,'Z','') FROM opng WHERE platnikguid=k.GUID AND grupaoper=0 
			AND nierozliczony>0 AND CURRENT_DATE>dataplatnosci) AS zadluzony,
			(SELECT GROUP_CONCAT(CONCAT(displayvalue,' - ',paramextra) SEPARATOR '\n')
				FROM slowniki_pierwotne WHERE Slownik_GUID='ZM_POWODY_NIEREALIZ') AS legenda,
				n.numer_na_trasie_zm
			FROM crm_commisions c
			LEFT JOIN zmng n ON c.guid=n.wyjazdGUID
			LEFT JOIN zmpo p ON n.guid=p.idnNG
			LEFT JOIN kontrah k ON k.guid=n.kontrGUID
			LEFT JOIN platnosc pl ON IFNULL(n.sposobplatn,k.sposplat)=pl.idn 
			LEFT JOIN t_oferty_poczekalnia op ON n.kontrGUID=op.kontrguid AND p.idntowr=op.idntowr
			LEFT JOIN oferty of ON op.kontrGUID=of.kontrGUID AND op.idntowr=of.idntowr
			-- ??? left join erp_area_nodes ean_a on ean_a.kontrGUID=k.GUID and ean_a.type='LOC'-- ean_a.kontekst=3
			INNER JOIN erp_area_nodes ean ON ean.guid=c.areaGUID
			INNER JOIN erp_division_schema edev ON edev.guid=ean.dzial
			INNER JOIN crm_usr_links cu ON cu.commisionGUID=c.GUID
			INNER JOIN osoby o ON o.guid=cu.osoba
			WHERE c.GUID = _GUID AND n.deleted=0 AND n.ilepoz<>n.ilepozzreal
			GROUP BY k.guid, n.sposobplatn
			ORDER BY  n.numer_na_trasie_zm, k.nazwa
			;
		END IF;
	END IF;

END IF;
/*
*******************************************************************************
	LP1
*******************************************************************************
*/
IF _Symbol IN ('LP1','R_HT_LP1','R_HT_LP1B') THEN
	IF(@session.globals.Owner IN ('CDG')) THEN	
		SELECT 
		ean.nazwa AS rewir, edev.nazwa AS oddzial, 
		edev.dane_adresowe AS oddzial_adres,
		c.nrwewn,
		SUBSTRING(c.StartTime,1,10) AS DataWyjazdu,
		CONCAT(o.imiona,' ',o.nazwisko) AS osoba,
		SUM(IF(p.towrsymb='MIX2',p.ilosc,0)) AS k1,
		SUM(IF(p.towrsymb='MIX3',p.ilosc,0)) AS k2,
		SUM(IF(p.towrsymb IN ('MIX11'),p.ilosc,0)) AS k3,
		SUM(IF(p.towrsymb='P11',p.ilosc,0)) AS k4,
		-- SUM(IF(p.towrsymb='P10',p.ilosc,0)) AS k4a,
		SUM(IF(p.towrsymb IN ('P10','NP10','GP10'),p.ilosc,0)) AS k4a,
		SUM(IF(p.towrsymb IN ('P10','NP10'),p.ilosc,0)) AS k4b, -- wersja dla Krakowa
		SUM(IF(p.towrsymb='H10',p.ilosc,0)) AS k15,
		SUM(IF(p.towrsymb='SP11',p.ilosc,0)) AS k5,
		SUM(IF(p.towrsymb='L10',p.ilosc,0)) AS k6,
		SUM(IF(p.towrsymb='MIX33',p.ilosc,0)) AS k7,
		SUM(IF(p.towrsymb IN ('P33','GP33'),p.ilosc,0)) AS k8,
		SUM(IF(p.towrsymb IN('P11','SP11','S11'),p.ilosc,0)) AS k9,
		SUM(IF(p.towrsymb IN('P33','MIX33'),p.ilosc,0)) AS k10,
		SUM(IF(p.towrsymb='S11',p.ilosc,0)) AS k11,
		SUM(IF(p.towrsymb='SML11',p.ilosc,0)) AS k12,
		SUM(IF(p.towrsymb='MIXbk',p.ilosc,0)) AS MIXbk,
		SUM(IF(p.idntowr IN ('IM_babe18c3cdb6c12bd200de950d7df23a','IM_382e7677197ea84a5269aa5138abefd7','IM_2e013df360604abeadadcbce1dc1f3fc','IM_0da01b8515bd7a1fd86505892ec3129b'),p.ilosc,0)) AS k24, -- mix 11
		SUM(IF(p.idntowr ='{1C3F5FB5-5EEA-4A35-963A-1A3F484CD4C6}',p.ilosc,0)) AS k25, -- kolor 10
		SUM(IF(p.idntowr='IM_bc0bae79d4540613a8d070d56716389e',p.ilosc,0)) AS k26, -- syfon green	
		SUM(IF(p.idntowr='IM_3f85d6e2dc25698fce5d75816ce92f5e',p.ilosc,0)) AS k27, -- syfon
		SUM(IF(p.idntowr='IM_c24b084b5421b2e8ff902923fa072b4d',p.ilosc,0)) AS k28, -- moto
		SUM(IF(p.idntowr IN ('IM_530ddb93b07a08545c06235037487f73','IM_768b5a77ba3f0b528cd95c5942af7abc'),p.ilosc,0)) AS k29, -- light
		SUM(IF(p.idntowr='IM_58cfc45c737d3304bd59ddca7589935a',p.ilosc,0)) AS k30, -- prop 11
		SUM(IF(p.idntowr='IM_2ac292b5912d80fb704f8c0ce9f9e2c1',p.ilosc,0)) AS k31, -- mix 33
		SUM(IF(p.idntowr='IM_d8a1d413b2027a6945172433af2fbca1',p.ilosc,0)) AS k32, -- mix 22
		SUM(IF(p.idntowr IN ('IM_2224c1239dea6694ddfd61b7b19279b6'),p.ilosc,0)) AS k33, -- prop 33
		SUM(IF(p.idntowr='nn',p.ilosc,0)) AS k34, -- prop 22
		SUM(IF(p.idntowr IN ('GIM_0aad9c5680a1e0059a004782d4f184c8','IM_4af292462de4620b84cc6818a04c8bf7'),p.ilosc,0)) AS k35, -- ACETYLEN
		SUM(IF(p.idntowr IN ('IM_95b60ff2fa5698b5e520a97636afaf4b'),p.ilosc,0)) AS k36, -- AMONIAK
		SUM(IF(p.idntowr IN ('IM_d9e6048f83f2f94b4f9be8e749d9464e','IM_4c16463cc973c08f6bdc9242f784bdf7'),p.ilosc,0)) AS k37, -- ARGON
		SUM(IF(p.idntowr IN ('IM_eb19a2d268474a3eaf1d72a5a35f1d80','IM_5780b4c74f0fa4952d625d9bf1bd9fc7','IM_5cf1b35866945cf98906227fbfcd4e5b','IM_e33f01ef346d9d40706ab3942b274754'),p.ilosc,0)) AS k38, -- CO2 MAĹ?Y
		SUM(IF(p.idntowr IN ('IM_39b1ea392c50bcd9f704237cf2400d5d','IM_69c9f20db4445862ffb583f9f7428aad','IM_fff56657b160dddafdfa67a28e9e6a23','IM_f318b0a5c352b5f049d1ba4a2c894a70'),p.ilosc,0)) AS k39, -- CO2 DUZY
		SUM(IF(p.idntowr IN ('IM_a94b0819b29a9d811367d113e98ae16a','IM_2c45719f4f3d46864b7a1f3312c8592e'),p.ilosc,0)) AS k40, -- CO2 MALY SYFON
		SUM(IF(p.idntowr IN ('IM_69c9f20db4445862ffb583f9f7428aad','IM_704a1aecd7e421caef1b65098144b5a9'),p.ilosc,0)) AS k41, -- CO2 DUZY SYFON
		SUM(IF(p.idntowr IN ('IM_bdcb7811efadf4be2a461fb53b12705e'),p.ilosc,0)) AS k42, -- tlen
		SUM(IF(p.idntowr IN ('IM_980e4e959c4c7cc7b3d572b25547b0aa'),p.ilosc,0)) AS k43, -- ferromix
		SUM(IF(p.idntowr IN ('{D53E66CB-868E-4D76-A02C-31244F6B1CAB}'),p.ilosc,0)) AS k44, -- N8
		SUM(IF(p.towrnazwa LIKE '%dwutlenek%',p.ilosc,0)) AS k45, -- CO2
		SUM(IF(p.towrnazwa LIKE '%promiennik%',p.ilosc,0)) AS k46, -- promiennik
		SUM(IF(p.towrnazwa LIKE '%reduktor%',p.ilosc,0)) AS k47, -- reduktor
		SUM(IF(p.towrsymb IN ('CO2 5','CO2 6'),p.ilosc,0)) AS k48,  -- CO2 5/6 kg
		SUM(IF(p.towrsymb IN ('S15'),p.ilosc,0)) AS k49,  -- gaz butla 15 kg
		SUM(IF(p.towrsymb IN ('BS15'),p.ilosc,0)) AS k50,  -- butla 15 kg

		SUM(IF(p.towrsymb IN ('GP10'),p.ilosc,0)) AS k51

		FROM crm_commisions c
		LEFT JOIN zmng n ON c.guid=n.wyjazdGUID
		LEFT JOIN zmpo p ON n.guid=p.idnNG
		LEFT JOIN kontrah k ON k.guid=n.kontrGUID
		LEFT JOIN platnosc pl ON n.sposobplatn=pl.idn 
		INNER JOIN erp_area_nodes ean ON ean.guid=c.areaGUID
		INNER JOIN erp_division_schema edev ON edev.guid=ean.dzial
		INNER JOIN crm_usr_links cu ON cu.commisionGUID=c.GUID
		INNER JOIN osoby o ON o.guid=cu.osoba
		WHERE c.GUID=@GUID AND n.status NOT IN (8,4)
		GROUP BY rewir
		;
	ELSE -- czesc dla mpgk
		SELECT 
		'fr_ht_wyjazdy_lp_mpgk' AS fr_ht_wyjazdy_lp_mpgk,
		ean.nazwa AS rewir, edev.nazwa AS oddzial, 
		(SELECT k0.valuex FROM katcols k0 WHERE c.katcol0=k0.klucz AND k0.colnr=1 AND k0.typ=7) AS nr_rej,
		c.nrwewn,
		SUBSTRING(c.StartTime,1,10) AS DataWyjazdu,
		CONCAT(o.imiona,' ',o.nazwisko) AS osoba,
		SUM(IF(p.towrsymb='MIX2',p.ilosc,0)) AS k1,
		SUM(IF(p.towrsymb='MIX3',p.ilosc,0)) AS k2,
		SUM(IF(p.towrsymb='MIX11',p.ilosc,0)) AS k3,
		SUM(IF(p.towrsymb='P11',p.ilosc,0)) AS k4,
		SUM(IF(p.towrsymb='P10',p.ilosc,0)) AS k4a,
		SUM(IF(p.towrsymb='H10',p.ilosc,0)) AS k15,
		SUM(IF(p.towrsymb='SP11',p.ilosc,0)) AS k5,
		SUM(IF(p.towrsymb='L10',p.ilosc,0)) AS k6,
		SUM(IF(p.towrsymb='MIX33',p.ilosc,0)) AS k7,
		SUM(IF(p.towrsymb='P33',p.ilosc,0)) AS k8,
		SUM(IF(p.towrsymb='MIXbk',p.ilosc,0)) AS MIXbk,
		SUM(IF(p.towrsymb IN('P11','SP11','S11'),p.ilosc,0)) AS k9,
		SUM(IF(p.towrsymb IN('P33','MIX33'),p.ilosc,0)) AS k10,
		SUM(IF(p.towrsymb='S11',p.ilosc,0)) AS k11,
		SUM(IF(p.towrsymb='SML11',p.ilosc,0)) AS k12,
		SUM(IF(p.idntowr IN ('IM_babe18c3cdb6c12bd200de950d7df23a','IM_382e7677197ea84a5269aa5138abefd7','IM_2e013df360604abeadadcbce1dc1f3fc'),p.ilosc,0)) AS k24, -- mix 11
		SUM(IF(p.idntowr ='{1C3F5FB5-5EEA-4A35-963A-1A3F484CD4C6}',p.ilosc,0)) AS k25, -- kolor 10
		SUM(IF(p.idntowr ='f921d792-a35e-11eb-9c17-525400eb203e',p.ilosc,0)) AS k25_1, -- horeca 10
		SUM(IF(p.idntowr='IM_bc0bae79d4540613a8d070d56716389e',p.ilosc,0)) AS k26, -- syfon green	
		SUM(IF(p.idntowr='IM_3f85d6e2dc25698fce5d75816ce92f5e',p.ilosc,0)) AS k27, -- syfon 11 kolor
		SUM(IF(p.idntowr='IM_c24b084b5421b2e8ff902923fa072b4d',p.ilosc,0)) AS k28, -- moto 11
		SUM(IF(p.idntowr IN ('IM_530ddb93b07a08545c06235037487f73','IM_768b5a77ba3f0b528cd95c5942af7abc'),p.ilosc,0)) AS k29, -- light
		SUM(IF(p.idntowr='53076752-6158-11eb-b7c8-525400eb203e',p.ilosc,0)) AS k30, -- prop 10
		SUM(IF(p.idntowr='{9EE90DC5-EAD9-4645-889E-5E11EC829725}',p.ilosc,0)) AS k31, -- prop 10 
		SUM(IF(p.idntowr='IM_0da01b8515bd7a1fd86505892ec3129b',p.ilosc,0)) AS k32, -- mix 11 kolor
		SUM(IF(p.idntowr IN ('IM_2224c1239dea6694ddfd61b7b19279b6'),p.ilosc,0)) AS k33, -- prop 33
		SUM(IF(p.idntowr='{80D97594-602E-429D-A060-1F81FE007E9E}',p.ilosc,0)) AS k34,  -- syfon moto 15
		SUM(IF(p.idntowr IN ('IM_0aad9c5680a1e0059a004782d4f184c8','IM_4af292462de4620b84cc6818a04c8bf7','IM_5d031cb7a4ea72487628f00d6dafa917'),p.ilosc,0)) AS k35, -- ACETYLEN
		SUM(IF(p.idntowr IN ('IM_643f27580add948da6d0f1f41a94bbb2'),p.ilosc,0)) AS k36, -- AMONIAK
		SUM(IF(p.idntowr IN ('IM_4f3dc0f2b731b0e631f983bedacca7e7','IM_d9e6048f83f2f94b4f9be8e749d9464e'),p.ilosc,0)) AS k37, -- ARGON
		SUM(IF(p.idntowr IN ('IM_eb19a2d268474a3eaf1d72a5a35f1d80','IM_5780b4c74f0fa4952d625d9bf1bd9fc7','IM_5cf1b35866945cf98906227fbfcd4e5b'),p.ilosc,0)) AS k38, -- CO2 MAĹ?Y
		SUM(IF(p.idntowr IN ('IM_39b1ea392c50bcd9f704237cf2400d5d','IM_f81e1bbb8c669c676f912eb0801aa942','IM_f318b0a5c352b5f049d1ba4a2c894a70','{1AD784A3-25DD-45A7-A5DD-6606175EE941}','IM_e33f01ef346d9d40706ab3942b274754'),p.ilosc,0)) AS k39, -- CO2 DUZY
		SUM(IF(p.idntowr IN ('IM_009b5a5b6dafd6416f350b93b0f32293','IM_2c45719f4f3d46864b7a1f3312c8592e','IM_a94b0819b29a9d811367d113e98ae16a'),p.ilosc,0)) AS k40, -- CO2 MALY SYFON
		SUM(IF(p.idntowr IN ('IM_704a1aecd7e421caef1b65098144b5a9'),p.ilosc,0)) AS k41, -- CO2 DUZY SYFON
		SUM(IF(p.idntowr IN ('{F2BE4FEB-CA70-4570-A57E-470FF0F0B181}','IM_980e4e959c4c7cc7b3d572b25547b0aa'),p.ilosc,0)) AS k42, -- tlen
		SUM(IF(p.idntowr IN ('IM_fa50e909006254b84e46af2da6dbc832','IM_bdcb7811efadf4be2a461fb53b12705e'),p.ilosc,0)) AS k43, -- ferromix
		SUM(IF(p.towrsymb IN ('CO2 5','CO2 6'),p.ilosc,0)) AS k48,  -- CO2 5/6 kg
		SUM(IF(p.towrsymb IN ('S15'),p.ilosc,0)) AS k49,  -- gaz butla 15 kg
		SUM(IF(p.towrsymb IN ('BS15'),p.ilosc,0)) AS k50,  -- butla 15 kg

		SUM(IF(p.towrsymb IN ('GP10'),p.ilosc,0)) AS k51,

		SUM(IF(p.towrsymb IN ('SYFON MOTO 11KG KOMPOZYT'),p.ilosc,0)) AS k52, -- moto light 11 kompozyt
		SUM(IF(p.towrsymb IN ('PROPAN 30KG GREENGAS'),p.ilosc,0)) AS k53, -- prop 30 GG
		SUM(IF(p.towrsymb IN ('PROPAN 30KG KOLOR'),p.ilosc,0)) AS k54, -- prop 30 kolor
		SUM(IF(p.towrsymb IN ('PROPAN 22KG'),p.ilosc,0)) AS k55, -- prop 22 kg

		-- od tego miejsca uslugi dowozu butli obcych
		SUM(IF(p.idntowr ='{08267C40-9869-4C04-B09E-B3245C7A5F15}',p.ilosc,0)) AS um11,
		SUM(IF(p.idntowr ='e0ea704d-4a9d-11eb-8ee6-525400eb203e',p.ilosc,0)) AS um14,
		SUM(IF(p.idntowr ='eb7778b9-4a9d-11eb-8ee6-525400eb203e',p.ilosc,0)) AS um11f,
		SUM(IF(p.idntowr ='3ee9128a-4a9e-11eb-8ee6-525400eb203e',p.ilosc,0)) AS up10,
		SUM(IF(p.idntowr ='ea75ab38-4a9e-11eb-8ee6-525400eb203e',p.ilosc,0)) AS up33,
		SUM(IF(p.idntowr ='0adbc75e-4a9f-11eb-8ee6-525400eb203e',p.ilosc,0)) AS up30,
		SUM(IF(p.idntowr ='866226e5-4a9f-11eb-8ee6-525400eb203e',p.ilosc,0)) AS up08,
		SUM(IF(p.idntowr ='d3b8395e-4a9f-11eb-8ee6-525400eb203e',p.ilosc,0)) AS um05,
		SUM(IF(p.idntowr ='242db73c-4aa0-11eb-8ee6-525400eb203e',p.ilosc,0)) AS um02
		FROM crm_commisions c
		LEFT JOIN zmng n ON c.guid=n.wyjazdGUID
		LEFT JOIN zmpo p ON n.guid=p.idnNG
		LEFT JOIN kontrah k ON k.guid=n.kontrGUID
		LEFT JOIN platnosc pl ON n.sposobplatn=pl.idn 
		INNER JOIN erp_area_nodes ean ON ean.guid=c.areaGUID
		INNER JOIN erp_division_schema edev ON edev.guid=ean.dzial
		INNER JOIN crm_usr_links cu ON cu.commisionGUID=c.GUID
		INNER JOIN osoby o ON o.guid=cu.osoba
		WHERE c.GUID=@GUID
		GROUP BY rewir
		;
	END IF;
END IF;
/*
*******************************************************************************
	ZM1 - inaczej dokument dostawy
*******************************************************************************
*/
	IF _Symbol IN ('ZM1','R_HT_ZM1','R_HT_ZM1B') THEN
	
		DROP TEMPORARY TABLE IF EXISTS `t_ZM1_kontr`;
		CREATE TEMPORARY TABLE `t_ZM1_kontr`
			SELECT
				z.kontrGUID AS GUID, 
				k.magazyn_akwizytora
			FROM `zmng` z
			INNER JOIN `kontrah` k ON k.GUID = z.kontrGUID
			WHERE
				z.wyjazdGUID = @GUID
				-- z.wyjazdGUID = '{29A95AF6-E8CB-4FED-89B1-BFA4FC5AD3AE}'
			GROUP BY z.kontrGUID
		;	
		DROP TEMPORARY TABLE IF EXISTS `t_ZM1_kontr_stany`;
		CREATE TEMPORARY TABLE `t_ZM1_kontr_stany`
			SELECT
				k.GUID AS KontrGUID,
				t.Symbol AS TowrSymb,
				SUM(ROUND(IF(k.magazyn_akwizytora=o.magazyn,o.ilosc,0), 0)) AS Stan
			FROM `t_ZM1_kontr` k
			INNER JOIN `opng` n ON n.kontrGUID = k.GUID 
			INNER JOIN `oppo_mag` o ON o.IdnOper = n.guid AND (o.magazyn=k.magazyn_akwizytora OR k.magazyn_akwizytora=0)
			INNER JOIN `TowrNg` t ON t.guid = o.IdnTowr
			GROUP BY k.GUID, o.IdnTowr 
			HAVING Stan != 0
		;	
	
		DROP TEMPORARY TABLE IF EXISTS `t_ZM1_kontr`;
	
		SELECT 
			0 AS rodzaj,
			SUBSTRING(c.StartTime,1,10) AS DataWyjazdu,
			IFNULL(hki.zam_next_date,'') AS DataNastPlanDostawy, 
			IFNULL(k.symbol,' ') AS symbol, 
			CONCAT(k.nazwamiasta,' ',k.ulica) AS adres,
			#ean_a.adres as adres_dost,
			CASE 
			WHEN EXISTS (SELECT geo.guid FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
						  WHERE ean2.kontrGUID=k.guid AND geo.status=0 AND ean2.type='LOC') 
			THEN (SELECT geo.adres FROM geo_int_adr_link geo INNER JOIN erp_area_nodes ean2 ON ean2.guid=geo.objGUID 
						  WHERE ean2.kontrGUID=k.guid AND geo.status=0 AND ean2.type='LOC' ORDER BY ean.idn DESC LIMIT 1)
			ELSE 
			  -- IF(IFNULL(ean_a.adres,'')='',CONCAT(k.nazwamiasta,' ',k.ulica),ean_a.adres)
				CASE 
				WHEN EXISTS (SELECT guid FROM erp_area_nodes ean 
							  WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
				THEN (SELECT adres FROM erp_area_nodes ean 
							  WHERE kontrGUID=k.guid AND TYPE='LOC' AND IFNULL(adres,'')<>'' ORDER BY idn DESC LIMIT 1)
				ELSE CONCAT(k.nazwamiasta,' ',k.ulica)
				END
			END AS adres_dost,
			n.uwagi,
			edev.nazwa AS oddzial, 
			CONCAT(o.imiona,' ',o.nazwisko) AS osoba,
			n.nrzamw,
			p.towrsymb, p.towrnazwa,
			SUM(p.ilosc) AS ilosc,
			#round(p.wartbrt/p.ilosc,2) as cenabrt,
			IF(LEFT(pl.nazwa,1)='G',FORMAT(ROUND(p.wartbrt/p.ilosc,2),2),'') AS cenabrt,
			p.wartbrt,
			(
				SELECT
					GROUP_CONCAT( CONCAT(s.towrsymb, ': ', s.Stan, ' szt.') ORDER BY s.towrsymb SEPARATOR '\n' ) 
				FROM `t_ZM1_kontr_stany` s
				WHERE
					s.KontrGUID = k.GUID
					AND s.Stan <> 0					
			) AS co_ma_klient,
			(SELECT GROUP_CONCAT(CONCAT(symbol,': ',ROUND(cena,2)) ORDER BY symbol SEPARATOR '\n')
			FROM t_oferty_poczekalnia WHERE kontrGUID=k.GUID)
			AS nowe_ceny
		FROM crm_commisions c
		LEFT JOIN zmng n ON c.guid=n.wyjazdGUID
		LEFT JOIN zmpo p ON n.guid=p.idnNG
		LEFT JOIN kontrah k ON k.guid=n.kontrGUID
		LEFT JOIN platnosc pl ON n.sposobplatn=pl.idn 
		-- left join erp_area_nodes ean_a on ean_a.kontrGUID=k.GUID and ean_a.type='LOC'-- ean_a.kontekst=3
		INNER JOIN erp_area_nodes ean ON ean.guid=c.areaGUID
		INNER JOIN erp_division_schema edev ON edev.guid=ean.dzial
		INNER JOIN crm_usr_links cu ON cu.commisionGUID=c.GUID
		INNER JOIN osoby o ON o.guid=cu.osoba
		LEFT JOIN ht_kontrah_info hki ON  hki.kontrGUID = k.GUID 
		WHERE c.GUID=@GUID
		GROUP BY k.guid, p.towrsymb
		ORDER BY k.symbol, p.towrsymb
		;
	
		DROP TEMPORARY TABLE IF EXISTS `t_ZM1_kontr_stany`;
	END IF;
	
	
	
	
END$$

DELIMITER ;
