DELIMITER $$

-- SET NAMES cp1250;

DROP PROCEDURE IF EXISTS `cdg_geo`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `cdg_geo`(
	IN _MODE VARCHAR(150),
	IN _WYJAZDGUID VARCHAR(38),
	IN _PARAM_A VARCHAR(100),
	IN _PARAM_B VARCHAR(100),
	IN _PARAM_C VARCHAR(100),
	IN _PARAM_D VARCHAR(100),
	IN _PARAM_E VARCHAR(100)
)

/*
Planowany wyjazd
SET @wyjazd="{402C548E-54AA-46D7-8AE9-ADD77DD43681}";
CALL cdg_geo('GET_ROUTE_HEADER',@wyjazd, '', '', '', '', '');
CALL cdg_geo('SELECT_ROUTE_POINTS',@wyjazd, '', '', '', '', '');
CALL cdg_geo('SELECT_ROUTE_POINTS_SELECT',@wyjazd, '', '', '', '', '');

CALL cdg_geo('UPDATE_ROUTE_ORDER_INDEX', @wyjazd, '', '', '', '', '');

Dane rzeczywistego wyjazdu
CALL cdg_geo('SELECT_ACTUAL_POINTS',@wyjazd, '', '', '', '', '');
CALL cdg_geo('SELECT_ACTUAL_POINTS_SELECT',@wyjazd, '', '', '', '', '');

Handlowcy
CALL cdg_geo('GET_HANDLOWCY','', '', '2015-12-01', '2015-12-10', '', '');
CALL cdg_geo('SELECT_ACTUAL_POINTS_MH','', 'bielawskim', '2015-12-01', '2015-12-10', '', '');
CALL cdg_geo('SELECT_ACTUAL_POINTS_MH_SELECT','', 'bielawskim', '2015-12-01', '2015-12-10', '', '');

Na koniec zawsze: !!!
CALL cdg_geo('CLEAN','', '', '', '', '', '');

*/


-- TODO wizyty z MHDroida


SPROC: BEGIN
-- (select guid from v_wyjazdy where login like '%kierowca' and data='data');
DECLARE _DepotGUID VARCHAR(38);

IF _WYJAZDGUID='' THEN
  SELECT guid FROM v_wyjazdy WHERE login=_PARAM_A AND DATA=_VarDate(_PARAM_B) INTO _WYJAZDGUID;
END IF;

IF 
-- _WYJAZDGUID='' OR 
_MODE IN ('','help') THEN
  SELECT CONCAT(
  "brak parametrow:\n\n",
  "parametry: _mode, _wyjazdguid, _param_a (user), _param_b (data od), _param_c,(data do), _param_d, _param_e,\n\n",
  "CALL cdg_geo('GET_ROUTE_HEADER','{}', '', '', '', '', '');\n",
  "CALL cdg_geo('SELECT_ROUTE_POINTS','{}', '', '', '', '', '');\n",
  "CALL cdg_geo('SELECT_ROUTE_POINTS_SELECT','{}', '', '', '', '', '');\n",
  "CALL cdg_geo('UPDATE_ROUTE_ORDER_INDEX', '{}', '', '', '', '', '');\n\n",
  "Dane rzeczywistego wyjazdu\n",
  "CALL cdg_geo('SELECT_ACTUAL_POINTS','{}', '', '', '', '', '');\n",
  "CALL cdg_geo('SELECT_ACTUAL_POINTS_SELECT','{}', '', '', '', '', '');\n\n",
  "Lista handlowcow\n",
  "CALL cdg_geo('GET_HANDLOWCY', '', '', '2015-05-30', '2015-05-30', '', '');\n\n",
  "Dane rzeczywistego wyjazdu handlowca\n",
  "CALL cdg_geo('GET_ROUTE_HEADER_MH','{}', '', '', '', '', '');\n",
  "CALL cdg_geo('SELECT_ACTUAL_POINTS_MH','{}', '', '', '', '', '');\n",
  "CALL cdg_geo('SELECT_ACTUAL_POINTS_MH_SELECT','{}', '', '', '', '', '');\n\n",
  "CALL cdg_geo('SELECT_ANKIETA','{}', '', '', '', '', '');\n\n",
  "Dane planowanych wizyt handlowca\n",
  "CALL cdg_geo('SELECT_WIZYTY','', 'bielawskim', '', '', '', '');\n\n",
  
  "Na koniec zawsze: !!!\n",
  "CALL cdg_geo('CLEAN','', '', '', '', '', '');") AS pomoc;
  LEAVE SPROC;
END IF;

IF _Param_C='' THEN
    SET _Param_C=_Param_B;
END IF;

IF _MODE = 'GET_HANDLOWCY' THEN
    CREATE TEMPORARY TABLE temp_h 
    SELECT DISTINCT login FROM droid_ankiety_dane_ng n,v_kkn4 h WHERE 
	h.param2=n.login AND 
	LEFT(n.droidtime,10)>=LEFT(_Param_B,10) AND LEFT(n.droidtime,10)<=LEFT(_Param_C,10) AND 
	 (_PARAM_A IN ('',h.param2,'jmam') OR (_Param_A='mosakowskip' AND h.param3 LIKE 'piotr.%') OR (_Param_A='pokorskip' AND h.param3 LIKE 'pawel.%'))
	 AND n.login<>'';
    SELECT o.guid AS osobaguid,CONCAT(o.nazwisko,' ',o.imie1) AS nazwisko,t.login FROM temp_h t, osoby o WHERE o.idnuser=t.login;
    DROP TEMPORARY TABLE temp_h;
END IF;

IF _MODE = 'GET_ROUTE_HEADER_MH' THEN
  SELECT COUNT(*) AS ile,MIN(p.droidtime) AS czas_od,MAX(p.droidtime) AS czas_do,
	 GROUP_CONCAT(DISTINCT IF(d.form='pojazd',p.valuedescr,NULL)) AS pojazd  
  FROM droid_ankiety_dane_po p,droid_ankiety_dane_ng n,droid_ankiety_def_po d    
  WHERE d.guid=p.ankietadefpoguid AND n.login=_Param_A AND p.guidng=n.guid AND 
	LEFT(n.droidtime,10)>=LEFT(_Param_B,10) AND LEFT(n.droidtime,10)<=LEFT(_Param_C,10);
END IF;		-- IF _MODE = 'GET_ROUTE_HEADER_MH'

IF _MODE = 'SELECT_WIZYTY' THEN
	DROP TEMPORARY TABLE IF EXISTS temp_wizyty;
	CREATE TEMPORARY TABLE temp_wizyty
	SELECT DISTINCT c.idn,c.guid,c.nrwewn,h.param2,
	LEFT(c.starttime, 16) AS datawizyty,c.temat,k.guid AS kontrguid, k.symbol, k.nazwa, 
	IFNULL(adr.Longitude, '') AS kLongitude, IFNULL(adr.Latitude, '') AS kLatitude,
	IF(IFNULL(SUBSTRING_INDEX(adr.adres,'(',1),'')='', IF(IFNULL(ean.adres,'')<>'',ean.adres,CONCAT(IFNULL(k.ulica,''),', ',IFNULL(k.zipcode,''),' ',k.nazwamiasta)) ,SUBSTRING_INDEX(adr.adres,'(',1)) AS adres
	
	FROM crm_commisions c
	INNER JOIN crm_usr_links lk ON lk.commisionguid=c.guid AND lk.poziom=4
	INNER JOIN osoby ok ON ok.guid=lk.osoba
	INNER JOIN kontrah k ON k.guid=ok.kontrguid
	INNER JOIN v_kkn4 h ON k.katcol4=h.klucz
	LEFT JOIN erp_area_nodes ean ON k.guid = ean.KontrGUID AND ean.type = 'LOC'
	LEFT JOIN v_geo_int_adr_link adr ON adr.objGUID = ean.guid AND adr.objTable = 'erp_area_nodes' AND adr.geo_int_miejsca_ID != ''
	WHERE  (_PARAM_A IN ('',h.param2,'jmam') OR (_Param_A='mosakowskip' AND h.param3 LIKE 'piotr.%') OR (_Param_A='pokorskip' AND h.param3 LIKE 'pawel.%'))
	      AND c.rodzaj=5 AND c.status IN (1,2) AND LEFT(c.starttime,10) BETWEEN LEFT(_Param_B,10) AND LEFT(_Param_C,10);

	SELECT * FROM  temp_wizyty;
END IF;		-- IF _MODE = 'SELECT_WIZYTY'


IF _MODE IN ('SELECT_ANKIETA','SELECT_ACTUAL_POINTS_MH','SELECT_ACTUAL_POINTS_MH_SELECT') THEN
  DROP TEMPORARY TABLE IF EXISTS t_SELECT_ROUTE_POINTS_ankieta;
  CREATE TEMPORARY TABLE t_SELECT_ROUTE_POINTS_ankieta
  SELECT d.label,d.form,p.guid,p.idn,p.droidtime,p.AnkietaDefPoGUID,p.valuekey,p.valuedescr,p.ctx1,p.ctx2,p.ctx3,
  ROUND(p.latitude,7) AS latitude,ROUND(p.longitude,7) AS longitude,
--  if(p.ankietadefpoguid in ('CDG_MHv120','CDG_MHv130','CDG_MHv140','CDG_MHv150') OR d.form in ('typ_biurowy'),concat(p.latitude,p.longitude),if(p.ctx1='',p.ctx3,p.ctx1)) as punkt
  IFNULL(p.ctx1,'') AS punkt
  FROM droid_ankiety_dane_po p,droid_ankiety_dane_ng n,droid_ankiety_def_po d 
  WHERE d.guid=p.ankietadefpoguid AND n.login=_Param_A AND p.guidng=n.guid
  AND LEFT(n.droidtime,10)>=LEFT(_Param_B,10) AND LEFT(n.droidtime,10)<=LEFT(_Param_C,10) 
  ORDER BY p.droidtime,p.idn;

  UPDATE t_SELECT_ROUTE_POINTS_ankieta SET punkt=CONCAT(ROUND(latitude,5),ROUND(longitude,5),LEFT(droidtime,13)) WHERE punkt='';

  DROP TEMPORARY TABLE IF EXISTS t_SELECT_ROUTE_POINTS;
  CREATE TEMPORARY TABLE t_SELECT_ROUTE_POINTS
  SELECT COUNT(*) AS ile,_VarInt(0) AS numer_na_trasie,
  MIN(p.droidtime) AS czasstart,MAX(p.droidtime) AS czasstop,
  CONCAT(MIN(p.droidtime),' - ',TIME(MAX(p.droidtime))) AS czas,
  GROUP_CONCAT(DISTINCT CONCAT(p.label,IF(p.label IN ('nazwa firmy','dodatkowe uwagi o wizycie'),CONCAT(':',p.valuedescr),'')) ORDER BY p.idn) AS czynnosci,
  CONCAT(p.form,':',GROUP_CONCAT(DISTINCT IFNULL((SELECT CONCAT(k.symbol,' ',k.nazwamiasta,',',k.ulica) FROM kontrah k WHERE k.guid=p.ctx1),'-'))) AS kontrahent,
  p.punkt,
  AVG(p.latitude) AS latitude,AVG(p.longitude) AS longitude,
  MAX(p.latitude)-MIN(p.latitude)+MAX(p.longitude)-MIN(p.longitude) AS diff,
  0 AS STARTPOINT, 0 AS ENDPOINT	

  FROM t_SELECT_ROUTE_POINTS_ankieta p
  WHERE p.longitude<>'err' AND p.valuedescr<>'-' AND 
	p.form NOT IN ('wiz_typ','pojazd','wiz_istniejacy','zmiana_ceny_klient_towar','zmiana_ceny_klient','typ_pracy_inny','typ_pracy') AND
	p.form NOT LIKE 'typ_biurowy%'
  GROUP BY punkt 
  ORDER BY czasstart,p.idn;
  
  SET @kolejnosc:=0;
  
  UPDATE t_SELECT_ROUTE_POINTS SET numer_na_trasie=@kolejnosc:=@kolejnosc+1 ORDER BY czasstart;
  
  UPDATE t_SELECT_ROUTE_POINTS SET startpoint=1 WHERE numer_na_trasie=1;
  UPDATE t_SELECT_ROUTE_POINTS SET endpoint=1 WHERE numer_na_trasie=@kolejnosc;
  DELETE FROM t_SELECT_ROUTE_POINTS WHERE czynnosci like '%start pracy%' AND startpoint=0;
  
  IF _MODE='SELECT_ACTUAL_POINTS_MH_SELECT' THEN
      SELECT * FROM t_SELECT_ROUTE_POINTS ORDER BY numer_na_trasie;
  END IF;	
  IF _MODE='SELECT_ANKIETA' THEN
      IF _WYJAZDGUID='' THEN
	  SELECT * FROM t_SELECT_ROUTE_POINTS_ankieta ORDER BY idn;
      ELSE
	  SELECT p.idn,TIME(p.droidtime) AS czas,p.form,p.label,p.valuedescr,CONCAT(p.latitude,',',p.longitude) AS geo FROM t_SELECT_ROUTE_POINTS_ankieta p 
	  WHERE punkt=_WYJAZDGUID AND p.form NOT LIKE 'zmiana_ceny%'
	  ORDER BY idn;
      END IF;	
  END IF;	

END IF;	


/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GET_ROUTE_HEADER'
		Zwraca naglowek trasy
		Parametry
			_WYJAZD	GUID wyjazdu (trasy)
		CALL cdg_geo('GET_ROUTE_HEADER', GUID, '', '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	

IF _MODE = 'GET_ROUTE_HEADER' THEN

SELECT	
  CONCAT(DATE_FORMAT(w.data, '%Y-%m-%d'), 'T', DATE_FORMAT(w.data, '%H:%i:%s')) AS data, 
-- REPLACE(nrrej, ' ', '') AS nrrej, 
  IFNULL(a.valuex, w.nrrej) AS nrrej, 
  w.kierowca,w.trasa,
  w.GUID, w.dystans_km,w.dystans_km_plan
FROM v_wyjazdy w 
  LEFT JOIN  katcols a  ON w.katcol0 = a.klucz AND a.typ = 7 AND a.colnr = 1 
  WHERE w.GUID = _WYJAZDGUID;
	
END IF;	--	IF _MODE = 'GET_ROUTE_HEADER'	

IF _MODE IN ('SELECT_ACTUAL_POINTS','SELECT_ACTUAL_POINTS_SELECT') THEN
  IF _WYJAZDGUID='' THEN
    SELECT guid from v_wyjazdy where login=_PARAM_A and data=_VarDate(_PARAM_B) INTO _WYJAZDGUID;
  END IF;
  
  DROP TEMPORARY TABLE IF EXISTS t_SELECT_ROUTE_POINTS;
  CREATE TEMPORARY TABLE t_SELECT_ROUTE_POINTS
	  SELECT -- adr.adres,  adr.*, 
		  adr.Idcountry, adr.wojewodztwo, adr.miejscowosc, adr.KodPocztowy, adr.ulica, adr.NrPos, 
		  IFNULL(z.Longitude, '') AS Longitude, IFNULL(z.Latitude, '') AS Latitude,
		  IFNULL(adr.Longitude, '') AS kLongitude, IFNULL(adr.Latitude, '') AS kLatitude,
		  IF(IFNULL(z.Longitude, '')='','Er','OK') as geo,
		  z.alocaltime as godzina,
		  k.nazwa AS kontrah_nazwa, ean.symbol, 
		  z.zmngguid AS zm_guid, z.kontrguid,
		  _VarInt(0) AS numer_na_trasie, 
		  adr.guid AS adr_guid, k.platnikguid, k.symbol as kontrsymbol,
		  0 AS startPoint, 0 AS endPoint,(select max(p.powod_niezrealizowania) from v_dpo p where p.zmngguid=z.zmngguid) as powod_niezrealizowania
	  FROM v_dng z 
	  JOIN kontrah k ON z.kontrguid = k.guid
	  LEFT JOIN erp_area_nodes ean ON k.guid = ean.KontrGUID  
	  INNER JOIN v_geo_int_adr_link adr ON adr.objGUID = ean.guid AND adr.objTable = 'erp_area_nodes' AND adr.geo_int_miejsca_ID != ''
	  WHERE 
		  ean.type = 'LOC' AND z.wyjazd = _WYJAZDGUID 
	  HAVING powod_niezrealizowania NOT IN (1,5,7,8,9,10,18,23,24);
		  
  ALTER TABLE t_SELECT_ROUTE_POINTS ADD PRIMARY KEY zm_guid (zm_guid);

  SET @kolejnosc:=1;SET @lat:='',@lon:='';
  
  UPDATE t_SELECT_ROUTE_POINTS SET numer_na_trasie=@kolejnosc:=@kolejnosc+1 ORDER BY godzina;
  UPDATE t_SELECT_ROUTE_POINTS SET Latitude=kLatitude,Longitude=kLongitude,geo='KL' WHERE geo='Er' AND kLatitude<>'';
  
  CALL cdg_geo('internal.ADD_START_END', _WYJAZDGUID, '', '', '', '', '');

  IF _MODE='SELECT_ACTUAL_POINTS_SELECT' THEN
    SELECT * FROM t_SELECT_ROUTE_POINTS ORDER BY numer_na_trasie;
  END IF;	

END IF;	

IF _MODE = 'CLEAN' THEN
  DROP TEMPORARY TABLE IF EXISTS t_SELECT_ROUTE_POINTS;
END IF;	

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'UPDATE_ROUTE_ORDER_INDEX'
		Aktualizuje kolejnosc na trasie w zamowieniach
		Parametry
			_WYJAZDGUID	GUID wyjazdu (trasy)
		CALL cdg_geo('UPDATE_ROUTE_ORDER_INDEX', GUID, '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
IF _MODE = 'UPDATE_ROUTE_ORDER_INDEX' THEN

  UPDATE 
	zmng z, t_SELECT_ROUTE_POINTS r
  SET
	z.numer_na_trasie_zm = r.numer_na_trasie
  WHERE z.GUID = r.zm_guid AND ((r.startPoint = 0) AND (r.endPoint = 0) )	;   
	
END IF;	--	IF _MODE = 'UPDATE_ROUTE_ORDER_INDEX'	
		

/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
'SELECT_ROUTE_POINTS'
	Zwraca poszczegolne punkty na danej trasie
	Parametry
		_WYJAZD	GUID wyjazdu (trasy)
	CALL cdg_geo('SELECT_ROUTE_POINTS', GUID, '', '', '', '', '');
	CALL cdg_geo('SELECT_ROUTE_POINTS_SELECT', GUID, '', '', '', '', '');
	CALL cdg_geo('internal.ADD_START_END', GUID, '', '', '', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	
IF _MODE = 'internal.ADD_START_END' THEN
	-- okreslamy lokalizacje oddzialu, skad wyjezdza i dokad wraca kierowca: 
	-- v_wyjazdy.areaguid -> erp_area_nodes.guid (rewir) 
	-- erp_area_nodes.parentguid (rewir) -> erp_area_nodes.guid (oddzial DEPOT)
	
	SET _DepotGUID = '';
	SELECT dep.guid  -- , dep.symbol
	FROM erp_area_nodes dep, erp_area_nodes rew, v_wyjazdy vw
	WHERE rew.parentguid = dep.guid AND vw.areaguid = rew.guid AND vw.GUID = _WYJAZDGUID
	INTO _DepotGUID;
	
	SET @route.DepotGUID =  _DepotGUID;

	-- wstawiamy dane adresowe oddzialu jako punkt startowy i koncowy
	DROP TEMPORARY TABLE IF EXISTS t_start_end;
	CREATE TEMPORARY TABLE t_start_end 
	SELECT _VarInt(0) as s,(SELECT max(godzina) FROM t_SELECT_ROUTE_POINTS) as godzina;
	INSERT INTO t_start_end
	SELECT _VarInt(1) as s,(SELECT min(godzina) FROM t_SELECT_ROUTE_POINTS) as godzina;
	
	INSERT  INTO `t_SELECT_ROUTE_POINTS`  -- IGNORE
	SELECT 
	  adr.IdCountry, adr.wojewodztwo, adr.miejscowosc, adr.KodPocztowy, 
	  adr.ulica, adr.NrPos, 
	  IFNULL(adr.Longitude, '') AS Longitude, IFNULL(adr.Latitude, '') AS Latitude,
	  IFNULL(adr.Longitude, '') AS kLongitude, IFNULL(adr.Latitude, '') AS kLatitude,
	  IF(IFNULL(adr.Longitude, '')='','Er','OK') as geo,
	  t.godzina,
	  'CDG' AS kontrah_nazwa,  -- kontrahenta chyba tu nie wstawiamy, bo to jest punkt wyjazdowy (ewentualnie wstawi� nazwe wlasna CDG)
	  ean.symbol, _VarInt(t.s) AS zm_guid, 'CDG' AS kontrguid, 
	  if(t.s=0,999,1) AS numer_na_trasie,
	  adr.GUID AS adr_guid, 'CDG' AS platnikguid, 'CDG' AS kontrsymbol,
	  t.s AS startPoint, (t.s+1) mod 2 AS endPoint,0 as powod
	  FROM `erp_area_nodes` ean
	  LEFT JOIN `v_geo_int_adr_link` adr ON adr.ObjGUID = ean.GUID AND adr.objTable = 'erp_area_nodes'
	  INNER JOIN t_start_end t
	-- FROM 
	--	v_geo_int_adr_link adr -- ON adr.objGUID = n.guid AND 
	WHERE 
		adr.objTable = 'erp_area_nodes'	AND	adr.objguid = _DepotGUID
	;

	DROP TEMPORARY TABLE IF EXISTS t_start_end;
END IF;	--	IF _MODE = 'GET_ROUTE_HEADER'	

IF _MODE IN ('SELECT_ROUTE_POINTS','SELECT_ROUTE_POINTS_SELECT') THEN
	DROP TEMPORARY TABLE IF EXISTS `t_SELECT_ROUTE_POINTS`;
	CREATE TEMPORARY TABLE `t_SELECT_ROUTE_POINTS`
		SELECT -- adr.adres,  adr.*, 
			adr.Idcountry, adr.wojewodztwo, adr.miejscowosc, adr.KodPocztowy, adr.ulica, adr.NrPos, 
			IFNULL(adr.Longitude, '') AS Longitude, IFNULL(adr.Latitude, '') AS Latitude,
			IFNULL(adr.Longitude, '') AS kLongitude, IFNULL(adr.Latitude, '') AS kLatitude,
			IF(IFNULL(adr.Longitude, '')='','Er','OK') as geo,
			z.localtime as godzina,
			k.nazwa AS kontrah_nazwa, ean.symbol, 
			z.guid AS zm_guid, z.kontrguid,
			IF(z.numer_na_trasie_zm > 0, z.numer_na_trasie_zm, k.numer_na_trasie) AS numer_na_trasie, 
			adr.guid AS adr_guid, k.platnikguid, z.kontrsymbol,
			0 AS startPoint, 0 AS endPoint,z.powod_niezrealizowania	
		FROM zmng z 
		JOIN kontrah k ON z.kontrguid = k.guid
		LEFT JOIN erp_area_nodes ean ON k.guid = ean.KontrGUID  
		INNER JOIN v_geo_int_adr_link adr ON adr.objGUID = ean.guid AND adr.objTable = 'erp_area_nodes' AND adr.geo_int_miejsca_ID != ''
		WHERE 
			ean.type = 'LOC' AND z.wyjazdGUID = _WYJAZDGUID AND z.powod_niezrealizowania NOT IN (1,5,7,8,9,10,18,23,24)
			
		GROUP BY zm_guid
	;    
	ALTER TABLE `t_SELECT_ROUTE_POINTS` ADD PRIMARY KEY zm_guid (zm_guid);
			
	INSERT IGNORE INTO `t_SELECT_ROUTE_POINTS`
		SELECT -- adr.adres,  adr.*, 
			'PL' AS Idcountry, 
			'' AS wojewodztwo, 
			adr.c_Miejsce AS miejscowosc, 
			adr.c_Kod, adr.c_ulica, adr.c_Numery, 
			0.0 AS Longitude, 0.0 AS Latitude,  -- jak to wyliczyc ?
			0.0 AS kLongitude, 0.0 AS kLatitude,  -- jak to wyliczyc ?
			'Er' as geo,
			z.localtime as godzina,
			k.nazwa AS kontrah_nazwa, ean.symbol, 
			z.guid AS zm_guid, z.kontrguid,
			IF(z.numer_na_trasie_zm > 0, z.numer_na_trasie_zm, k.numer_na_trasie) AS numer_na_trasie,   -- bedzie uaktualnione przez optymalizacje dokonana przez SV/Google
			adr.aIDN AS adr_guid, k.platnikguid, z.kontrsymbol,
			0 AS startPoint, 0 AS endPoint,z.powod_niezrealizowania					
		FROM zmng z 
		JOIN kontrah k ON z.kontrguid = k.guid
		LEFT JOIN erp_area_nodes ean ON k.guid = ean.KontrGUID  
		INNER JOIN `ta_cdg_adresy` adr ON adr.GUID = k.guid
		WHERE 
			ean.type = 'LOC' AND z.wyjazdGUID = _WYJAZDGUID AND z.powod_niezrealizowania NOT IN (1,5,7,8,9,10,18,23,24)
		GROUP BY zm_guid
	;    
	
	CALL cdg_geo('internal.ADD_START_END', _WYJAZDGUID, '', '', '', '', '');
	
	IF _MODE ='SELECT_ROUTE_POINTS_SELECT' THEN
	  SELECT * FROM `t_SELECT_ROUTE_POINTS` ORDER BY numer_na_trasie;
	END IF;
	
	-- koniec wstawiania danych adresowych oddzialu jako punktu startowego i koncowego

	/* to zostaje zaremowane i bedzie podstawiane do CDS.CommandText, dzieki czemu bedzie mozna wykonac
	  ApplyUpdates (potrzebna do tego jest tabela zrodlowa, nie moze to byc CALL)
	select * from `t_SELECT_ROUTE_POINTS`;
	
	*/
END IF;	--	IF _MODE = 'SELECT_ROUTE_POINTS'


END $$

DELIMITER ;

/*****************************************************
REWIZJE
******************************************************
$Log: cdg_geo.sql,v $
Revision 1.19  2016/01/18 12:38:13  plaskowski
aktualziacja

Revision 1.18  2016/01/18 12:35:24  apro
*** empty log message ***

Revision 1.17  2016/01/18 08:56:31  apro
planowanie wizyt handlowców

Revision 1.16  2016/01/04 10:29:19  apro
dodanie modu dla planowania wizyt handlowców

Revision 1.15  2015/12/08 10:39:04  apro
ifnull

Revision 1.14  2015/12/08 09:32:08  apro
zmiana prezentacji daty startowej

Revision 1.13  2015/12/08 08:41:02  apro
zmiana parametrów, dodanie daty do dla MH

Revision 1.12  2015/12/07 14:30:45  plaskowski
$SEND$
Poprawki w mechanizmie kontrahentow
Committed on the Free edition of March Hare Software CVSNT Client.
Upgrade to CVS Suite for more features and support:
http://march-hare.com/cvsnt/

Revision 1.11  2015/10/20 10:25:37  apro
*** empty log message ***

Revision 1.10  2015/10/05 13:06:10  apro
naglowek wyjazdu handlowca

Revision 1.9  2015/10/05 13:03:35  apro
*** empty log message ***

Revision 1.7  2015/09/21 06:42:46  apro
wyjazdy handlowcow

Revision 1.3  2015/05/18 11:21:36  apro
help

Revision 1.2  2015/05/18 11:11:12  apro
*** empty log message ***

Revision 1.1  2015/05/18 10:05:24  apro
*** empty log message ***

*/
