DELIMITER $$


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)
)

/*
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('SELECT_ACTUAL_POINTS_MH','', 'osinskim', '2015-06-12', '');
CALL cdg_geo('SELECT_ACTUAL_POINTS_MH_SELECT','', 'osinskim', '2015-06-12', '');

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",
  "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",
  "Na koniec zawsze: !!!\n",
  "CALL cdg_geo('CLEAN','', '', '', '');") as pomoc;
  LEAVE SPROC;
END IF;

		
/*------------------------------------------------------------------------------------------------------------------------------------------------------------------	
	'GET_HANDLOWCY'
		Zwraca naglowek trasy
		Parametry
			_PARAM_A Data
		CALL cdg_geo('GET_HANDLOWCY', '', '2015-05-30', '', '');
------------------------------------------------------------------------------------------------------------------------------------------------------------------*/	

IF _MODE = 'GET_HANDLOWCY' THEN
    SELECT v.klucz,v.valuex as nazwisko,v.kontofk as mail,v.param2 as login,(SELECT o.guid from osoby o WHERE o.idnuser=v.param2 LIMIT 1) as osobaguid FROM v_kkn4 v WHERE v.param2<>'';
END IF;


IF _MODE = 'GET_ROUTE_HEADER_MH' THEN
-- TODO
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'	

-- CALL cdg_geo('SELECT_ACTUAL_POINTS_MH_SELECT','', 'osinskim', '2015-06-12', '');

IF _MODE IN ('SELECT_ACTUAL_POINTS_MH','SELECT_ACTUAL_POINTS_MH_SELECT') THEN
  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,
  group_concat(DISTINCT d.label) as czynnosci,
  IFNULL((select concat(k.symbol) from kontrah k where k.guid=p.ctx1),'-') as kontrahent,
  if(p.ankietadefpoguid in ('CDG_MHv120','CDG_MHv130','CDG_MHv140','CDG_MHv150'),p.guid,if(p.ctx3='',p.ctx1,p.ctx3)) as punkt,
  avg(p.latitude) as latitude,avg(p.longitude) as longitude,
  0 AS startPoint, 0 AS endPoint	

  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 left(n.droidtime,10)=LEFT(_Param_B,10) and p.guidng=n.guid and p.longitude<>'err' 
  GROUP BY punkt 
  HAVING punkt<>'' 
  ORDER BY czasstart;
  
  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;
  
  -- CALL cdg_geo('internal.ADD_START_END', _WYJAZDGUID, '', '', '');

  IF _MODE='SELECT_ACTUAL_POINTS_MH_SELECT' THEN
    SELECT * FROM t_SELECT_ROUTE_POINTS ORDER BY numer_na_trasie;
  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	
	  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;    
		  
  ALTER TABLE t_SELECT_ROUTE_POINTS ADD PRIMARY KEY zm_guid (zm_guid);

  /*
  DROP TEMPORARY TABLE IF EXISTS trasa;
  CREATE TEMPORARY TABLE trasa
  SELECT _VarDouble(latitude) as latitude,_VarDouble(longitude) as logitude,
    kontrsymbol as kontrahent,
    @tm:=cast(alocaltime as datetime) as godzina,
    (SELECT GROUP_CONCAT(CONCAT(IFNULL(t.kasafiskalnanazwa,p.towrguid),':',ROUND(zrealizowano))) 
      FROM v_dpo p 
      LEFT JOIN TowrNg t ON t.guid=p.towrguid WHERE n.zmngguid=p.zmngguid) as towar,
      _VarInt(0) as kolejnosc
  FROM v_dng n WHERE n.wyjazd=_WYJAZDGUID
  ORDER BY alocaltime;
*/

  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			  		  
	  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	
		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
		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					
		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
		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.6  2015/06/26 06:17:56  apro
*** empty log message ***

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 ***

*/
