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
*******************************************************************************
 */
	IF _Symbol = 'RK1' 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 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,
    n.uwagi, -- chodzi ponoc o uwagi z zamowienia
    #CONCAT((SELECT GROUP_CONCAT(opis,' ',adres SEPARATOR ', ') FROM kontakty WHERE kontrGUID=k.GUID AND typ_medium IN (0,2)),' ',IFNULL(k.uwagi,'')) 
	#AS uwagi,
	ean.nazwa AS rewir, edev.nazwa AS oddzial, 
	c.nrwewn,
	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='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='BM',p.ilosc,0)) AS k13,

	SUM(IF(p.towrsymb in ('GA000001','GA0002','GA0051','GA0073'),p.ilosc,0)) AS k24, -- mix 11
	SUM(IF(p.towrsymb='GA0047',p.ilosc,0)) AS k25, -- kolor 10
	SUM(IF(p.towrsymb='GA0076',p.ilosc,0)) AS k26, -- syfon green	
	SUM(IF(p.towrsymb='GA000006',p.ilosc,0)) AS k27, -- syfon
	SUM(IF(p.towrsymb='GA0070',p.ilosc,0)) AS k28, -- moto
	SUM(IF(p.towrsymb in ('GA0001','GA0048'),p.ilosc,0)) AS k29, -- light
	SUM(IF(p.towrsymb='GA000004',p.ilosc,0)) AS k30, -- prop 11
	SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k31, -- mix 33
	SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k32, -- mix 22
	SUM(IF(p.towrsymb in ('GA000009','GA0057'),p.ilosc,0)) AS k33, -- prop 33
	SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k34, -- prop 22

	GROUP_CONCAT(CONCAT(p.towrsymb,'-',ROUND(p.wartbrt/p.ilosc,2))) AS ceny,
	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
	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
	WHERE c.GUID=@GUID
	GROUP BY k.guid, n.sposobplatn
	ORDER BY k.numer_na_trasie, k.nazwa
	;
	

	END IF;
/*
*******************************************************************************
	LP1
*******************************************************************************
*/

	IF _Symbol = 'LP1' THEN
	
	SELECT 
	ean.nazwa AS rewir, edev.nazwa AS oddzial, 
	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='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 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 in ('GA000001','GA0002','GA0051','GA0073'),p.ilosc,0)) AS k24, -- mix 11
	SUM(IF(p.towrsymb='GA0047',p.ilosc,0)) AS k25, -- kolor 10
	SUM(IF(p.towrsymb='GA0076',p.ilosc,0)) AS k26, -- syfon green	
	SUM(IF(p.towrsymb='GA000006',p.ilosc,0)) AS k27, -- syfon
	SUM(IF(p.towrsymb='GA0070',p.ilosc,0)) AS k28, -- moto
	SUM(IF(p.towrsymb in ('GA0001','GA0048'),p.ilosc,0)) AS k29, -- light
	SUM(IF(p.towrsymb='GA000004',p.ilosc,0)) AS k30, -- prop 11
	SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k31, -- mix 33
	SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k32, -- mix 22
	SUM(IF(p.towrsymb in ('GA000009','GA0057'),p.ilosc,0)) AS k33, -- prop 33
	SUM(IF(p.towrsymb='nn',p.ilosc,0)) AS k34 -- prop 22

	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;

/*
*******************************************************************************
	ZM1 - inaczej dokument dostawy
*******************************************************************************
*/

	IF _Symbol = 'ZM1' 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 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,
			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 ;