
DELIMITER $$


DROP PROCEDURE IF EXISTS `ht_gasdroid`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_gasdroid`(
	IN _MODE VARCHAR(150),
	IN _SESSION VARCHAR(150),
	IN _DATA VARCHAR(10),
	IN _KIEROWCA VARCHAR(38),
	IN _WYJAZD VARCHAR(38),
	IN _TIME VARCHAR(20),
	IN _TOWRGUID VARCHAR(38),
	IN _ZMPOGUID VARCHAR(38),
	IN _ILOSC VARCHAR(38),
	IN _LATITUDE VARCHAR(38),
	IN _LONGITUDE VARCHAR(38),
	IN _STATUS VARCHAR(10),
	IN _SEARCH VARCHAR(100),
	IN _KONTRGUID VARCHAR(38)
)

BEGIN
-- 	SET @dr:=PI()/180;
DECLARE _LAT DOUBLE;
DECLARE _LON DOUBLE;

    SET @ht_gasdroid_result := -1;
    SELECT firma,min_wersja FROM droid_conf INTO @firma,@min_wersja;

    SET _LAT=IF(_LATITUDE='',0,_LATITUDE);
    SET _LON=IF(_LONGITUDE='',0,_LONGITUDE);
    SELECT a.dzial FROM crm_commisions c,erp_area_nodes a 
    WHERE c.areaguid=a.guid AND c.guid=_WYJAZD LIMIT 1 INTO @oddzial;
    SET @oddzial:=IFNULL(@oddzial,'');
    SET _DATA=IF(IFNULL(_DATA,'')='',CURRENT_DATE,_DATA);
		
	-- podaje liste powodow niezrealizowania
	-- call ht_gasdroid('POWODY','','','','','','','','','','','','','');
	IF _MODE = 'POWODY' THEN
		SELECT s.Value,s.DisplayValue,s.ParamExtra FROM slowniki_pierwotne s WHERE s.slownik_guid="ZM_POWODY_NIEREALIZ" ORDER BY value+0;
		SET @ht_gasdroid_result := 0;
	END IF;	
	-- podaje dane dot. wyjazdu kierowcy danego dnia
	-- call ht_gasdroid('WYJAZD','012983','2012-02-21','IM_85','','','','','','','','','','');

	IF _MODE = 'WYJAZD' THEN

	    SELECT c.guid,LEFT(c.starttime,10) AS `data`,
			c.nrwewn AS nrrej,c.opis AS kierowca,
			c.areaguid,a.nazwa AS trasa, a.dzial,
			du.autoNrZS,
			du.autoNrFV,
			du.autoNrWZ,
			CONCAT(du.nrprefix,'/%05d/',a.dzial,'/',RIGHT(YEAR(CURRENT_DATE),2)) AS formatNumeru,
			CONCAT(du.nrprefix,'/%05d/',a.dzial,'/',RIGHT(YEAR(CURRENT_DATE),2)) AS formatNumeruWZ,
			CONCAT(replace(du.nrprefix,'M','F'),'/%05d/',a.dzial,'/',RIGHT(YEAR(CURRENT_DATE),2)) AS formatNumeruFV,
			CONCAT(replace(du.nrprefix,'M','P'),'/%05d/',a.dzial,'/',RIGHT(YEAR(CURRENT_DATE),2)) AS formatNumeruZS,
			IF(IFNULL(a.kontrguid,'')='',(SELECT guid FROM kontrah WHERE symbol=CONCAT('DETAL_',a.dzial) LIMIT 1),a.kontrguid) AS detal_kontrguid,
			@min_wersja AS min_wersja


		FROM crm_usr_links ul, crm_commisions c,erp_area_nodes a , droid_users du
		WHERE c.guid=ul.commisionGUID AND c.areaguid=a.guid AND  du.osobaguid=_KIEROWCA AND
			ul.osoba=_KIEROWCA AND ul.poziom=2 AND c.status IN (0,1,2) AND LEFT(c.starttime,10)=_DATA -- CURRENT_DATE ?
			;
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	
	IF _MODE = 'KONTRLIST.pre.zmng'  THEN
		DROP TEMPORARY TABLE IF EXISTS temp_klist;
		UPDATE crm_commisions SET status=2 WHERE status IN (0,1) AND guid=_WYJAZD;
-- zamwstatus=16 - wyslane
		
-- UPDATE zmng z SET z.status=16,z.localtime=z.localtime WHERE z.status IN (0) AND z.wyjazdguid=_WYJAZD;


		CREATE TEMPORARY TABLE temp_klist
		SELECT 
		n.kontrguid, 
		GROUP_CONCAT(IFNULL(CONCAT(p.towrsymb,':',ROUND(p.ilosc)),'') ORDER BY p.towrsymb) AS towary,
		SUM(n.ilepoz) AS ilepoz,
		GROUP_CONCAT(DISTINCT concat(IFNULL(concat(n.warunki_dostawy,' '),''),IFNULL(n.uwagi,''))) AS uwagi,
		MIN(n.status) AS STATUS,
--		IF(n.numer_na_trasie_zm=0,k.numer_na_trasie,n.numer_na_trasie_zm) AS kolejnosc,
		IFNULL(NULLIF(IFNULL(NULLIF(n.numer_na_trasie_zm,0),k.numer_na_trasie),0),n.idn) AS kolejnosc,
		MAX(GREATEST(n.localtime,IFNULL(p.localtime,NOW()))) AS alocaltime,		
		GROUP_CONCAT(DISTINCT IFNULL(n.nrzamw,'') ORDER BY n.idn) AS nrDok,
		MAX(n.rodzaj2) AS rodzajDok,
		IFNULL(MAX(n.sposobplatn),k.sposplat) AS sposobplatn
		
		
		FROM zmng n 
		INNER JOIN kontrah k ON k.guid=n.kontrguid
		LEFT JOIN zmpo p ON n.guid=p.idnng 
		LEFT JOIN droid_zmpo dp ON  dp.zmpoguid=p.guid
		WHERE n.wyjazdguid=_WYJAZD AND n.wyjazdguid<>'' AND IFNULL(dp.statuszam,0)=0
		GROUP BY n.kontrguid
		HAVING alocaltime>_TIME
		ORDER BY kolejnosc;
		
		SET @ht_gasdroid_result := 0;
	END IF;	

-- call ht_gasdroid('KONTRLIST.powiadom.list','','','','','100','','','','','','','','');
--  przyklad dla 100 minut
	IF _MODE in ('KONTRLIST.powiadom','KONTRLIST.powiadom.list')  THEN
		DROP TABLE IF EXISTS temp_gd_powiadom;
		CREATE TABLE temp_gd_powiadom
		SELECT 
		n.kontrguid, 
		n.wyjazdguid,
		GROUP_CONCAT(IFNULL(CONCAT(p.towrsymb,':',ROUND(p.ilosc)),'') ORDER BY p.towrsymb) AS towary,
		GROUP_CONCAT(DISTINCT concat(IFNULL(concat(n.warunki_dostawy,' '),''),IFNULL(n.uwagi,''))) AS uwagi,
		MAX(GREATEST(n.localtime,IFNULL(p.localtime,NOW()))) AS alocaltime,		
		k.nazwa as kontrnazwa,
		@adresFV:=CONCAT(IFNULL(k.ulica,''),', ',ifnull(k.zipcode,''),' ',k.nazwamiasta) AS adresFV,
		IF(IFNULL(SUBSTRING_INDEX(g.adres,'(',1),'')='', IF(IFNULL(a.adres,'')<>'',a.adres,@adresFV) ,SUBSTRING_INDEX(g.adres,'(',1)) AS adres,
		w.kierowca,w.gmail, w.data=current_date as nadzis
				
		FROM zmng n 
		INNER JOIN v_wyjazdy w ON w.guid= n.wyjazdguid AND w.powiadom=1  
		INNER JOIN kontrah k ON k.guid=n.kontrguid
		LEFT JOIN zmpo p ON n.guid=p.idnng 
		LEFT JOIN droid_zmpo dp ON  dp.zmpoguid=p.guid
		LEFT JOIN erp_area_nodes a ON a.kontrguid=n.kontrguid
		LEFT JOIN erp_area_nodes e ON k.guid=e.kontrguid
		LEFT JOIN geo_int_adr_link g ON e.guid=g.objguid
		WHERE n.wyjazdguid<>'' AND IFNULL(dp.statuszam,0)=0 
			AND n.wydzial=0 -- znacznik - 0 - nie powiadomiono, 1 - powiadom mail+QS, 2 - przekazano do QS, 3 - powiadom tylko QS (bez maila)
			AND n.localtime>current_date -- bezpiecznik
		GROUP BY n.kontrguid
		HAVING alocaltime>date_sub(now(),interval _TIME minute) -- z crona - interpretowane jako iloæ minut wstecz
		ORDER BY alocaltime;
		
		IF _MODE in ('KONTRLIST.powiadom','KONTRLIST.powiadom.list')  THEN
			SELECT * FROM temp_gd_powiadom;
		END IF;	

		SET @ht_gasdroid_result := 0;
	END IF;	


	IF _MODE = 'KONTRLIST.pre.gps'  THEN
	--  call ht_gasdroid('KONTRLIST.gps','','','','','','','','',18.616514,54.2409,'','','');

		IF _STATUS='' THEN
			SET @promien:=0.3; -- km
		ELSE
			SET @promien:=_STATUS; -- km
		END IF;
		
		SET @diff:=@promien/50; -- przybli¿enie, tak by ko³o o promieniu zmiesci³o siê w prostokacie
		
		
		DROP TABLE IF EXISTS temp_klist;
		CREATE TEMPORARY TABLE temp_klist
		SELECT 
			k.GUID	AS kontrguid,'' as towary,0 as ilepoz,'' as uwagi,0 AS STATUS,
			geo_distance(_LAT,_LON,g.latitude,g.longitude)  as kolejnosc,
			
			k.localtime AS alocaltime,'' AS nrDok,k.rodzaj2 AS rodzajDok,k.sposplat as sposobplatn
			
			FROM kontrah k
		INNER JOIN erp_area_nodes e ON k.guid=e.kontrguid
		INNER JOIN geo_int_adr_link g ON e.guid=g.objguid
		WHERE
		-- celowo nie stosuje tu dokladnej odleglosci ze wzgledu na wydajnosc
			k.deleted=0 AND g.latitude BETWEEN _LAT-@diff AND _LAT+@diff  AND g.longitude BETWEEN  _LON-@diff AND _LON+@diff AND _LAT<>0 AND _LON<>0
		HAVING kolejnosc<=@promien
		
		ORDER by kolejnosc;
		
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	
	IF _MODE = 'SV.KONTRLIST.zmng'  THEN
	-- select uuid() as guid,g.guid as geoguid,k.guid as kontrguid,k.platnikguid,k.symbol as kontrsymbol,k.nazwa as kontrnazwa,g.zipcode,g.adres,g.latitude,g.longitude,400 as promien,4 as kolejnosc,5 as  czaspunktu, '' as czasprzybycia from kontrah k LEFT JOIN erp_area_nodes a ON k.guid=a.kontrguid LEFT JOIN geo_int_adr_link g ON a.guid=g.objguid where k.guid='IM_164b4ca4a3cf422500523764fd09c4de'
	-- select g.*,k.guid as kontrguid,k.platnikguid,k.symbol as kontrsymbol,k.nazwa as kontrnazwa,400 as promien,4 as kolejnosc,5 as  czaspunktu, '' as czasprzybycia from kontrah k LEFT JOIN erp_area_nodes a ON k.guid=a.kontrguid LEFT JOIN geo_int_adr_link g ON a.guid=g.objguid where k.guid='IM_164b4ca4a3cf422500523764fd09c4de'
	
	select g.guid,
	k.guid as kontrguid,k.platnikguid,k.symbol as kontrsymbol, CONCAT(k.nazwa, ' ',k.nip) AS kontrnazwa,
	v.nazwa_pelna as miejscowosc,gia.Srch_Cecha_N1_N2 as ulica,g.nrpos as nr_posesji,g.nrloc as nr_lokalu,
	v.woj_str as wojewodztwo,g.latitude,g.longitude,400 as promien,
	4 as kolejnosc,5 as  czaspunktu, '' as czasprzybycia 
	FROM kontrah k 
	LEFT JOIN erp_area_nodes a ON k.guid=a.kontrguid 
	LEFT JOIN geo_int_adr_link g ON a.guid=g.objguid 
	LEFT JOIN v_geo_int_miejsca v ON v.id=g.geo_int_miejsca_id 
	LEFT JOIN geo_int_adresy gia on gia.id=g.geo_int_adresy_id 
	WHERE k.guid='IM_164b4ca4a3cf422500523764fd09c4de';
	
	SET @ht_gasdroid_result := 0;
	END IF;	

	IF _MODE = 'SV.WYJAZDY'  THEN
	-- select guid,data,kierowca,trasa,oddzial, starttime* from v_wyjazdy where data=current_date limit;
		SET @ht_gasdroid_result := 0;
	END IF;	
		
		
	IF _MODE = 'KONTRLIST.pre.search.count' or _MODE = 'KONTRLIST.pre.search'  THEN
	-- call ht_gasdroid('KONTRLIST.pre.search.count','','','','{313A2517-76D6-4846-B505-AF89A68AA4C3}','','','','','','','','top','');
	-- call ht_gasdroid('KONTRLIST.search','','','','{313A2517-76D6-4846-B505-AF89A68AA4C3}','','','','','','','','','');


	
		-- SELECT c.areaguid from crm_commisions c	WHERE c.guid=_WYJAZD INTO @rewir;
		SET _SEARCH=REPLACE(_SEARCH,'+','%');
		SET _SEARCH=REPLACE(_SEARCH,' ','%');
		
		DROP TABLE IF EXISTS temp_klist;
		CREATE TEMPORARY TABLE temp_klist
		SELECT DISTINCT
			k.GUID	AS kontrguid,'' as towary,0 as ilepoz,'' as uwagi,0 AS STATUS,
			geo_distance(_LAT,_LON,g.latitude,g.longitude)  as kolejnosc,
			k.localtime AS alocaltime,'' AS nrDok,k.rodzaj2 AS rodzajDok,k.sposplat as sposobplatn
			FROM kontrah k
		LEFT JOIN erp_area_nodes a ON k.guid=a.kontrguid
		LEFT JOIN geo_int_adr_link g ON a.guid=g.objguid
		WHERE
			k.deleted=0 AND 
		--	k.areaguid=@rewir and (
			(if(@oddzial='',1,k.ugrpguid=@oddzial) or k.ugrpguid='ALL') and (
					k.nazwa like CONCAT('%',_SEARCH,'%') or
					k.ulica like CONCAT('%',_SEARCH,'%') or
					k.nip like CONCAT('%',_SEARCH,'%') or
					ifnull(a.adres,k.ulica) like CONCAT('%',_SEARCH,'%') or
					ifnull(g.adres,k.ulica) like CONCAT('%',_SEARCH,'%') or
					k.symbol like CONCAT('%',_SEARCH,'%')
				)
		ORDER by kolejnosc
		LIMIT 30; -- wiêcej nie ma sensu zwracaæ
		IF _MODE = 'KONTRLIST.pre.search.count' THEN
			SELECT count(*) as ile from temp_klist;
		END IF;
		
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	IF _MODE = 'KONTRLIST.zmng.count'  THEN
	    CALL ht_gasdroid('KONTRLIST.pre.zmng',_SESSION,_DATA,_KIEROWCA,_WYJAZD,_TIME,_TOWRGUID,_ZMPOGUID,_ILOSC,_LATITUDE,_LONGITUDE,_STATUS,_SEARCH,_KONTRGUID);
	    SELECT count(*) as ile from temp_klist;
	    SET @ht_gasdroid_result := 0;
	END IF;	

	IF _MODE = 'KONTRLIST.zmng' or _MODE = 'KONTRLIST.search' or _MODE = 'KONTRLIST.gps' THEN
		
		-- call ht_gasdroid('KONTRLIST.zmng','','','','9188537b-ef2b-11e4-bad3-001372373ee4*','','','','',53.689452,17.588307,'','','');
		-- call ht_gasdroid('KONTRLIST.search','','','','{313A2517-76D6-4846-B505-AF89A68AA4C3}','','','','',53.689452,17.588307,'','most','');
		-- wywo³anie KONTRLIST.pre.search.count przygotowuje ju¿ temp_klist wiec jesli _SEARCH jest pusty to podaje wczesniej przygotowane dane jeli nie, to wykonywane jest 
		IF _MODE = 'KONTRLIST.search' and IFNULL(_SEARCH,'')<>'' THEN
			CALL ht_gasdroid('KONTRLIST.pre.search',_SESSION,_DATA,_KIEROWCA,_WYJAZD,_TIME,_TOWRGUID,_ZMPOGUID,_ILOSC,_LATITUDE,_LONGITUDE,_STATUS,_SEARCH,_KONTRGUID);
		END IF;	

		IF _MODE = 'KONTRLIST.gps' THEN
			CALL ht_gasdroid('KONTRLIST.pre.gps',_SESSION,_DATA,_KIEROWCA,_WYJAZD,_TIME,_TOWRGUID,_ZMPOGUID,_ILOSC,_LATITUDE,_LONGITUDE,_STATUS,_SEARCH,_KONTRGUID);
			
			-- SELECT rewir,count(*) FROM temp_klist into @rewir,@ile; -- TODO: @rewir - co autor mial na mysli???
			SELECT count(*) FROM temp_klist into @ile;
			IF @ile = 0 THEN
				SET @ht_gasdroid_result := -2;
			END IF;
		
		END IF;

		IF _MODE = 'KONTRLIST.zmng' THEN
			CALL ht_gasdroid('KONTRLIST.pre.zmng',_SESSION,_DATA,_KIEROWCA,_WYJAZD,_TIME,_TOWRGUID,_ZMPOGUID,_ILOSC,_LATITUDE,_LONGITUDE,_STATUS,_SEARCH,_KONTRGUID);
		END IF;

		SELECT 
		CONCAT(rtrim(k.symbol), if(k.nip='','', ' - '), k.nip) as kontrsymbol,
		k.nazwa, n.kontrguid, 
		concat('GX',md5(concat(n.kontrguid, _WYJAZD))) AS zmngguid,  -- dla danego wyjazdu i kontrahenta unikalny
		@adresFV:=CONCAT(IFNULL(k.ulica,''),', ',ifnull(k.zipcode,''),' ',k.nazwamiasta) AS adresFV,
		IF(IFNULL(SUBSTRING_INDEX(g.adres,'(',1),'')='', IF(IFNULL(a.adres,'')<>'',a.adres,@adresFV) ,SUBSTRING_INDEX(g.adres,'(',1)) AS adres,	
		towary,
		-- sposplat=1 to gotowka 0 dni
		-- 1 | BEZPOSREDNIA |2 | KLATKA|3| HURT|4 | CENTRALNY|6 | ODSPRZEDAWCA |7| PRZEMYSL|8|UM_MPGK
		-- bylo: k.katcol0 as rodzaj_klienta,
		if(k.treenode like "*|CENTRALNY%",4,if(k.treenode like "*|KLATKA%",2,1)) as rodzaj_klienta,
		(k.sposplat=1 and not (k.treenode like "*|CENTRALNY%")) as potw_drukuj_ceny,
		n.kolejnosc, -- lub km
		n.ilepoz,
		ifnull(g.latitude,'') as latitude, ifnull(g.longitude,'') as longitude,
		CONCAT(if((select current_status from  ht_platnik_pay_info p where p.platnikguid=n.kontrguid)>10,'BLOKADA!!! ',''),
		ifnull(n.uwagi,'')) as uwagi,
		n.status,
		n.alocaltime,		
		IFNULL((SELECT SUM(ABS(IF(O.CWN, O.CKwota, -O.CKwota)- O.CRozliczone))  
			FROM opng O WHERE O.kontrguid = k.GUID AND O.CWN = 1 AND O.nierozliczony>0 AND O.deleted = 0 AND O.rozrachunek = 1), 
			_varDOUBLE(0)) AS zaleglosci,
		(SELECT ko.adres from kontakty ko where ko.kontrguid=k.guid and typ_medium in (0,2) and nieaktywny=0 order by typ_medium desc limit 1) AS telefon, 
		n.nrDok,n.rodzajDok,
		k.nip AS nip,
		date_add(CURRENT_DATE,interval ifnull((select pl.dni from platnosc pl where pl.idn=n.sposobplatn),0) day) AS terminPlatnosci,
		-- (select pl.nazwa from platnosc pl where pl.idn=n.sposobplatn) AS sposobPlatnosci,
		_VarInt(IFNULL((select pl.nazwa from platnosc pl where pl.idn=n.sposobplatn)='GOTOWKA',1)) AS sposobPlatnosci, -- 0 - przelew - 1 - gotowka
		SUBSTRING(k.Odbierajacy,1,100) AS odbiorcy,
		k.serwis_koment as stan_klatki

-- TODO: wiele lokalizacji kontrahenta
		
		FROM temp_klist n 
		INNER JOIN kontrah k ON k.guid=n.kontrguid
		LEFT JOIN erp_area_nodes a ON a.kontrguid=n.kontrguid
		LEFT JOIN geo_int_adr_link g ON a.guid=g.objguid
		WHERE n.alocaltime>_TIME
		ORDER BY kolejnosc;
		
		DROP TABLE IF EXISTS temp_klist;
		
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	IF _MODE = 'ZALADUNEK'  THEN
		SELECT 
		p.idntowr AS towrguid,
		-- p.towrsymb AS towar,
		if(ifnull(t.kod_fiskalny,'')<>'',t.kod_fiskalny,t.symbol) as towar,
		p.guid as zmpoguid,
		concat('GX',md5(concat(n.kontrguid, _WYJAZD))) AS zmngguid,  -- dla danego wyjazdu i kontrahenta unikalny
		ROUND(p.ilosc) AS ilosc,
		ROUND(p.zrealizowano) AS zrealizowano,
		ifnull(p.cenabrt,0) AS cena,
		ifnull(p.wartbrt,0) AS wartosc,
		p.idnvat AS vat,
		ROUND(p.zwrot) AS obcebutle,
		IFNULL(p.uwagi, '') AS uwagi, 
		GREATEST(n.localtime,IFNULL(p.localtime,NOW())) AS alocaltime,
		n.rodzaj2 AS rodzajDokSprzed,
		_varInt(0) AS statusZam,
		n.powod_niezrealizowania AS powod_niezrealizowania,
		_varInt(0) AS blokadaceny
		FROM zmpo p 
		LEFT JOIN zmng n ON n.guid=p.idnng 
		LEFT JOIN TowrNg t ON t.guid=p.idntowr
		LEFT JOIN droid_zmpo dp ON  dp.zmpoguid=p.guid
		WHERE n.wyjazdguid=_WYJAZD AND n.wyjazdguid<>'' AND IFNULL(dp.statuszam,0)=0
		HAVING alocaltime>_TIME
		;
	
		SET @ht_gasdroid_result := 0;
	END IF;	
	
	IF _MODE = 'ZALEGLOSCI'  THEN
		SELECT 
		n.guid as zmngguid,
		O.kontrguid, 
		O.nrdokfin, 
		O.datawystaw, 
		O.dataplatnosci, 		
		ABS(IF(O.CWN, O.CKwota, -O.CKwota)- O.CRozliczone) AS dozaplaty, 
		O.CRozliczone AS rozliczono,
		O.idn AS idnOpNg,
		_varInt(0) AS  status,
		_varInt(0) AS  checked,		
		IFNULL(O.localtime,NOW()) AS alocaltime
		FROM  zmng n
		INNER JOIN opng O ON n.kontrguid=O.kontrguid
		LEFT JOIN droid_zaplaty dz ON  dz.idnoper=O.idn
		WHERE n.wyjazdguid=_WYJAZD AND n.wyjazdguid<>'' AND (IFNULL(dz.rozliczono,0)=0 OR IFNULL(dz.status=103,1)) -- ignoujemy takie dokumenty, ktore maja juz zapis w zaplatach, ale jeszcze nie wciagniete do systemu
		-- TODO _KONTRGUID
		AND O.CWN = 1 AND O.nierozliczony>0 AND O.deleted = 0 AND O.rozrachunek = 1 		
		GROUP BY O.GUID
		HAVING alocaltime>_TIME
		ORDER BY kontrguid, dataplatnosci
		;
	
		SET @ht_gasdroid_result := 0;
	END IF;	
		
	
	IF _MODE = 'ZALEGLOSCI.kontrah'  THEN
		SELECT 
		-- n.guid as zmngguid,
		'' as zmngguid,
		O.kontrguid, 
		O.nrdokfin, 
		O.datawystaw, 
		O.dataplatnosci, 		
		-- ABS(IF(O.CWN, O.CKwota, -O.CKwota)- O.CRozliczone) AS dozaplaty, 
		ABS(IF(O.CWN, O.CKwota, -O.CKwota)- O.CRozliczone)-IFNULL(IF(dz.status=3, dz.rozliczono,0),0) AS dozaplaty, 
		O.CRozliczone AS rozliczono,
		O.idn AS idnOpNg,
		_varInt(0) AS  status,
		_varInt(0) AS  checked,		
		IFNULL(O.localtime,NOW()) AS alocaltime
		FROM opng O 
		LEFT JOIN droid_zaplaty dz ON  dz.idnoper=O.idn
		WHERE O.kontrguid=_KONTRGUID 
		-- AND IFNULL(dz.status,0)=0
		AND IFNULL(dz.rozliczono<>dz.dozaplaty OR dz.status=103,1)
		AND O.CWN = 1 AND O.nierozliczony>0 AND O.deleted = 0 AND O.rozrachunek = 1 		
		GROUP BY O.GUID
-- wnisek Rafala z 2014-05-13		HAVING alocaltime>_TIME
		ORDER BY kontrguid, dataplatnosci
		;
	
		SET @ht_gasdroid_result := 0;
	END IF;	
		
	IF _MODE = 'TOWARY' or _MODE = 'TOWAR.cena' or _MODE = 'TOWARY.stan' THEN
		-- TODO - zastanowic sie od strony aplikacji , czy pobierac wszystko, czy konkretny towar
		-- mod TOWARY pelna lista towarów z ilosciami i cenami:
		-- PARAMETRY OBOWIAZKOWE: _WYJAZD
		-- PARAMETRY OPCJONALNE: _KONTRGUID, _TOWRGUID
		-- call ht_gasdroid('TOWARY','','','','{915CA682-845E-11E1-9E3F-001372373EE4}','','','','','','','','','IM_937e0d1c064de3778d546efac3760a2d');
		
		-- mod TOWAR.cena - podaje tylko cene dla konkretnego kontrahenta i towaru
		-- GasDroid: PARAMETRY OBOWIAZKOWE: _WYJAZD, _KONTRGUID, _TOWRGUID
		-- CallCenter: PARAMETRY OBOWIAZKOWE: _KONTRGUID, _TOWRGUID
		-- call ht_gasdroid('TOWAR.cena','','','','{915CA682-845E-11E1-9E3F-001372373EE4}','','{6341715D-E105-4417-B7E1-E053E9CB25F3}','','','','','','','IM_937e0d1c064de3778d546efac3760a2d');
		-- call ht_gasdroid('TOWAR.cena','','','','','','MIX11','','','','','','','IM_937e0d1c064de3778d546efac3760a2d');
		
		-- mod TOWAR.stan - podaje pelna liste towarow na stanie dla konkretnego kontrahenta
		-- PARAMETRY OBOWIAZKOWE: _WYJAZD, _KONTRGUID
		-- PARAMETRY OPCJONALNE:_TOWRGUID
		-- call ht_gasdroid('TOWARY.stan','','','','{915CA682-845E-11E1-9E3F-001372373EE4}','','','','','','','','','IM_937e0d1c064de3778d546efac3760a2d');
		
		-- sprawdzam, czy dla trasy jest okreslony cennik indywidualny, i pobieram go
		
		
		SET @trasa:='';
		
		-- dla pustego wyjazdu pobieram trase i oddzial na podstawie danych kontrahenta - dla CallCenter
		SELECT ugrpguid,areaguid,treenode from kontrah where guid=_KONTRGUID into @oddzial,@trasa,@ktreenode;
		IF (_WYJAZD<>'') THEN
			SELECT areaguid from crm_commisions where guid=_WYJAZD into @trasa;
		END IF;
		SET @cennik:=NULL;
--		SELECT k.idn,if(k.czy_ceny_brt,1,1.23) as brt FROM erp_area_nodes a, klasycen k 
--		WHERE a.guid=@trasa and a.lp*3+10=k.idn and a.lp<>0 into @cennik,@brt;
		SELECT k.idn,if(k.czy_ceny_brt,1,1.23) as brt FROM klasycen k WHERE k.param1=@trasa and k.param3=@ktreenode into @cennik,@brt;

		
/* zmieniæ pobieranie cennika na nowy tryb - w zaleznosci od typu klienta (mid(treenode,16,20)) i idnu rewiru:
0 - Indywidualny
update ta_cenyb set cennik=1 where typ like "Gastr%";
update ta_cenyb set cennik=2 where typ like "Ogrze%";
update ta_cenyb set cennik=3 where typ like "Firma%";

update ta_cenyb t, erp_area_nodes e
set t.cennik=t.cennik+e.idn*10+2000,t.rewirguid=e.guid,t.oddzial=e.dzial
where e.type='REWIR' and (e.symbol=t.rewir or e.nazwa=t.rewir);

*/
		IF ISNULL(@cennik) THEN
			SELECT c.idn,if(c.czy_ceny_brt,1,1.23) as brt FROM klasycen c 
			WHERE nazwa=concat(@oddzial,' Indywidualny') into @cennik,@brt;
		END IF;
		
		CREATE TEMPORARY TABLE tempt		
		SELECT 
--		t.symbol, concat(t.symbol,' ',t.kasafiskalnanazwa) as nazwa, 
		if(ifnull(t.kod_fiskalny,'')<>'',t.kod_fiskalny,t.symbol) as symbol,
		left(if(ifnull(t.kasafiskalnanazwa,'')<>'',t.kasafiskalnanazwa,t.nazwa),16) as nazwa,
		t.wwwsortorder,
		ifnull((SELECT s.stan FROM temp_olap_stany s WHERE s.idntowr=t.guid and s.kontrguid=_KONTRGUID ORDER by rok,mc DESC limit 1),0) as ilosc, 
		t.wyroznik, t.GUID, 
		_varDOUBLE_NOTNULL(
		round(@brt*(t.cena_minimalna+if(t.wyroznik='G',t.waga*ifnull((select k.msc_plan_sprzed/1000 from kontrah k WHERE k.guid=_KONTRGUID ),0),0 )),2))
		as cena_minimalna,
		_Rnd2(
		ifnull(if(kc.czy_ceny_brt,1,1.23)*kc.cenaobl,
		 ifnull(@brt*c.cena,
		 ifnull(cd.cena, -- zawsze brt
		  0)))) as cena,'' as status
		FROM TowrNg t
		LEFT JOIN cennik cd on cd.idntowr=t.guid and cd.klasa=1 -- cena domyslna - zawsze brt
		LEFT JOIN cennik c  on c.idntowr=t.guid  and c.klasa=@cennik --  cena detaliczna z odpowiedniej klasy cenowej oddzialu lub trasy
		LEFT JOIN ht_kontrah_ceny kc on kc.idntowr=t.guid and kc.kontrguid=_KONTRGUID
		
		where (t.status_www=1) and (_TOWRGUID='' or t.guid=_TOWRGUID)
		;
		IF _MODE = 'TOWARY' THEN
			SELECT * from tempt ORDER BY wwwsortorder;
		END IF;
		IF _MODE = 'TOWAR.cena' THEN
			select count(*) from tempt into @ile;
			IF @ile<>1 THEN 
				SELECT _varDOUBLE_NOTNULL(0) as cena, _varDOUBLE_NOTNULL(0) as cena_minimalna, if(@ile=0,"BRAK CEN?","TOWRGUID NIEOKRESLONY") AS STATUS;
			ELSE
				SELECT _varDOUBLE_NOTNULL(cena) as cena, _varDOUBLE_NOTNULL(cena_minimalna) as cena_minimalna from tempt into @cena,@cena_minimalna;
				SELECT _varDOUBLE_NOTNULL(cena) as cena, _varDOUBLE_NOTNULL(cena_minimalna) as cena_minimalna from tempt;
			END IF;
		END IF;
		
		IF _MODE = 'TOWARY.stan' THEN
			SELECT * from tempt where ilosc<>0;
		END IF;
		DROP TABLE IF EXISTS tempt;
		SET @ht_gasdroid_result := 0;
		
	END IF;	
	
	
	IF @ht_gasdroid_result = -1 THEN
		SELECT "MODE NIE ZDEFINIOWANY" AS STATUS,_MODE AS `mode`;
	END IF;
	
END$$

DROP PROCEDURE IF EXISTS `ht_gasdroid_zmng`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_gasdroid_zmng`(
	IN _id INT, 
	IN zmngguid VARCHAR(38),
	IN kontrguid VARCHAR(38), 
	IN latitude VARCHAR(38),
	IN longitude VARCHAR(38),
	IN alocaltime DATETIME,
	IN status INT,
	IN idDevice  VARCHAR(38),
	IN autoNrZS  VARCHAR(38),
	IN autoNrFV  VARCHAR(38),
	IN autoNrWZ  VARCHAR(38),
	IN wyjazd   VARCHAR(250),
	IN sposobPlatnosci   VARCHAR(250),
	IN terminPlatnosci   VARCHAR(250),
	IN _p3   VARCHAR(250)
)

BEGIN
	REPLACE `droid_zmng` (_id, zmngguid, kontrguid, latitude, longitude, alocaltime, status, idDevice, autoNrZS ,autoNrFV , autoNrWZ, wyjazd, sposobPlatnosci, terminPlatnosci)
	VALUES (_id, zmngguid, kontrguid, latitude, longitude, alocaltime, status, idDevice, autoNrZS ,autoNrFV , autoNrWZ, wyjazd, sposobPlatnosci, terminPlatnosci);

END$$

DROP PROCEDURE IF EXISTS `ht_gasdroid_zmpo`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_gasdroid_zmpo`(
	IN _id INT, 
	IN zmngguid VARCHAR(38),
	IN zrealizowano DOUBLE(16,4), 
	IN obcebutle INT, 
	IN alocaltime DATETIME,
	IN rodzajDokSprzed INT, 
	IN statusZam INT, 
	IN wartosc DOUBLE(16,2), 
	IN powod_niezrealizowania INT, 
	IN towrguid  VARCHAR(38),
	IN zmpoguid  VARCHAR(38),
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250),
	IN _p4   VARCHAR(250)
)

BEGIN
  SET @zrealizowano:=0;
  SELECT zrealizowano FROM droid_zmpo p WHERE p.zmpoguid=zmpoguid into @zrealizowano;
  IF IFNULL(@zrealizowano,0)=0 THEN 
 	  REPLACE `droid_zmpo` (_id, zmngguid, zrealizowano, obcebutle, alocaltime, rodzajDokSprzed, statusZam, wartosc, powod_niezrealizowania, towrguid, zmpoguid)
	  VALUES (_id, zmngguid, zrealizowano, obcebutle, alocaltime, rodzajDokSprzed, statusZam, wartosc, powod_niezrealizowania, towrguid, zmpoguid);
	END IF;

END$$

DROP PROCEDURE IF EXISTS `ht_gasdroid_zaplaty`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `ht_gasdroid_zaplaty`(
	IN _id INT, 
	IN kontrguid VARCHAR(38),
	IN datawystaw DATE,
	IN zmngguid VARCHAR(38),
	IN rozliczono DOUBLE(16,2), 
	IN alocaltime DATETIME,
	IN idnoper INT, 
	IN status INT, 
	IN dozaplaty DOUBLE(16,2), 
	IN wyjazd   VARCHAR(250),
	IN _p1   VARCHAR(250),
	IN _p2   VARCHAR(250),
	IN _p3   VARCHAR(250)
)

BEGIN
	REPLACE `droid_zaplaty` (_id, kontrGUID, datawystaw, zmngguid, rozliczono, alocaltime, idnoper, status, dozaplaty, wyjazd)
	VALUES (_id, kontrGUID, datawystaw, zmngguid, rozliczono, alocaltime, idnoper, status, dozaplaty, wyjazd);
	
END$$

DELIMITER ;

