--
-- Host: 127.0.0.1    Database: proba
-- ------------------------------------------------------


--
-- Table structure for table `datagps`
--

CREATE TABLE `datagps` (
  `DataGps_ID` int(11) NOT NULL AUTO_INCREMENT,
  `Mobitel_ID` int(11) DEFAULT '0',
  `Message_ID` int(11) DEFAULT '0',
  `Latitude` int(11) DEFAULT '0',
  `Longitude` int(11) DEFAULT '0',
  `Altitude` int(11) DEFAULT NULL,
  `UnixTime` int(11) DEFAULT '0',
  `Speed` smallint(6) DEFAULT '0',
  `Direction` int(11) NOT NULL DEFAULT '0',
  `Valid` tinyint(4) DEFAULT '1',
  `InMobitelID` int(11) DEFAULT NULL,
  `Events` int(10) unsigned DEFAULT '0',
  `Sensor1` tinyint(4) unsigned DEFAULT '0',
  `Sensor2` tinyint(4) unsigned DEFAULT '0',
  `Sensor3` tinyint(4) unsigned DEFAULT '0',
  `Sensor4` tinyint(4) unsigned DEFAULT '0',
  `Sensor5` tinyint(4) unsigned DEFAULT '0',
  `Sensor6` tinyint(4) unsigned DEFAULT '0',
  `Sensor7` tinyint(4) unsigned DEFAULT '0',
  `Sensor8` tinyint(4) unsigned DEFAULT '0',
  `LogID` int(11) DEFAULT '0',
  `isShow` tinyint(4) DEFAULT '0',
  `whatIs` smallint(6) unsigned DEFAULT '0',
  `Counter1` int(11) NOT NULL DEFAULT '-1',
  `Counter2` int(11) NOT NULL DEFAULT '-1',
  `Counter3` int(11) NOT NULL DEFAULT '-1',
  `Counter4` int(11) NOT NULL DEFAULT '-1',
  `SrvPacketID` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ID серверного пакета в состав которого входит эта запись',
  PRIMARY KEY (`DataGps_ID`),
  KEY `Index_2` (`Mobitel_ID`,`LogID`),
  KEY `Index_3` (`Mobitel_ID`,`UnixTime`,`Valid`),
  KEY `IDX_MobitelIDSrvPacketID` (`Mobitel_ID`,`SrvPacketID`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

--
-- Dumping data for table `datagps`
--


--
-- Table structure for table `datagps64`
--

CREATE TABLE `datagps64` (
  `DataGps_ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `Mobitel_ID` int(11) NOT NULL DEFAULT '0' COMMENT 'РљРѕРґ РўРµР»РµС‚СЂРµРєР°',
  `Latitude` int(11) NOT NULL DEFAULT '0' COMMENT 'РЁРёСЂРѕС‚Р°',
  `Longitude` int(11) NOT NULL DEFAULT '0' COMMENT 'Р”РѕР»РіРѕС‚Р°',
  `Direction` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'РіСЂР°РґСѓСЃС‹/1.5',
  `Acceleration` int(11) NOT NULL DEFAULT '0' COMMENT 'РР·РјРµРЅРµРЅРёРµ СЃРєРѕСЂРѕСЃС‚Рё РїРѕ GPS Р·Р° РїРѕСЃР»РµРґРЅСЋСЋ СЃРµРєСѓРЅРґСѓ',
  `UnixTime` int(11) NOT NULL DEFAULT '0',
  `Speed` smallint(6) NOT NULL DEFAULT '0' COMMENT 'РЎРѕС‚РµРЅ РјРµС‚СЂРѕРІ РІ С‡Р°СЃ (0,1 РєРј/С‡)',
  `Valid` tinyint(1) NOT NULL DEFAULT '1',
  `Satellites` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'РљРѕР»РёС‡РµСЃС‚РІРѕ СЃРїСѓС‚РЅРёРєРѕРІ ',
  `RssiGsm` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'РЈСЂРѕРІРµРЅСЊ GSM СЃРёРіРЅР°Р»Р°',
  `Events` int(11) NOT NULL DEFAULT '0' COMMENT 'Р¤Р»Р°РіРё СЃРѕР±С‹С‚РёР№',
  `SensorsSet` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'РљРѕРґ РЅР°Р±РѕСЂР° РґР°С‚С‡РёРєРѕРІ',
  `Sensors` varbinary(50) NOT NULL DEFAULT '0' COMMENT 'Р—РЅР°С‡РµРЅРёСЏ РґР°С‚С‡РёРєРѕРІ',
  `Voltage` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT 'РќР°РїСЂСЏР¶РµРЅРёРµ РїРёС‚Р°РЅРёСЏ С‚РµРєСѓС‰РµРіРѕ РёСЃС‚РѕС‡РЅРёРєР°/1.5 РѕС‚ 0 РґРѕ 38,2',
  `DGPS` varchar(32) DEFAULT NULL,
  `LogID` int(11) NOT NULL DEFAULT '0' COMMENT 'РРґРµРЅС‚РёС„РёРєР°С‚РѕСЂ Р·Р°РїРёСЃРё РІ Р¶СѓСЂРЅР°Р»Рµ РўРµР»РµС‚СЂРµРєР°',
  `SrvPacketID` bigint(20) NOT NULL DEFAULT '0' COMMENT 'ID СЃРµСЂРІРµСЂРЅРѕРіРѕ РїР°РєРµС‚Р° РІ СЃРѕСЃС‚Р°РІ РєРѕС‚РѕСЂРѕРіРѕ РІС…РѕРґРёС‚ СЌС‚Р° Р·Р°РїРёСЃСЊ',
  PRIMARY KEY (`DataGps_ID`),
  KEY `IDX_MobitelIDSrvPacketID64` (`Mobitel_ID`,`SrvPacketID`),
  KEY `Index_2_64` (`Mobitel_ID`,`LogID`),
  KEY `Index_3_64` (`Mobitel_ID`,`UnixTime`,`Valid`),
  CONSTRAINT `FK_datagps64_mobitels_Mobitel_ID` FOREIGN KEY (`Mobitel_ID`) REFERENCES `mobitels` (`Mobitel_ID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=cp1251;

--
-- Dumping data for table `datagps64`
--


--
-- Dumping events for database 'proba'
--

--
-- Dumping routines for database 'proba'
--
/*!50003 DROP FUNCTION IF EXISTS `CheckVersion` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `CheckVersion`(maskVersion TINYTEXT) RETURNS tinyint(4)
    SQL SECURITY INVOKER
    COMMENT 'Проверка версии СУБД. Должна быть maskVersion или выше. Результа'
BEGIN
  DECLARE currentVersion TINYTEXT;
  SELECT Left(VERSION(), Length(maskVersion)) INTO currentVersion;
  RETURN currentVersion >= maskVersion;
END ;;
DELIMITER ;
/*!50003 DROP FUNCTION IF EXISTS `HaveSuperPrivileges` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` FUNCTION `HaveSuperPrivileges`() RETURNS tinyint(4)
    SQL SECURITY INVOKER
    COMMENT 'Проверка, имеет ли пользователь привилегию SUPER. Результат: 1 -'
BEGIN
  DECLARE usrstr TINYTEXT DEFAULT '';

  DECLARE usr TINYTEXT DEFAULT '';
  DECLARE hst TINYTEXT DEFAULT '';
  DECLARE pos TINYINT;

  SELECT USER() INTO usrstr;

  SELECT INSTR(usrstr, '@') INTO pos;


  SELECT Left(usrstr, pos - 1), Right(usrstr, Length(usrstr) - pos) INTO usr, hst;

  RETURN (
    SELECT COUNT(*) > 0
    FROM mysql.`user` u
    WHERE u.`User` = usr AND u.Host = hst AND Super_priv = 'Y'
  );
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CheckDB` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckDB`()
    SQL SECURITY INVOKER
    COMMENT 'Проверка БД при подключении.'
BEGIN

  SELECT 'OK';

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CheckOdo` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CheckOdo`(IN m_id INTEGER(11), IN end_time DATETIME)
    SQL SECURITY INVOKER
BEGIN

  SELECT 

   FROM_UNIXTIME(datagps.UnixTime) AS `time` ,

   (datagps.Latitude / 600000.00000) AS Lat,

   (datagps.Longitude / 600000.00000) AS Lon

  FROM

    datagps

  WHERE

    datagps.Mobitel_ID = m_id AND 

    datagps.UnixTime BETWEEN (SELECT UNIX_TIMESTAMP(odoTime)  FROM vehicle WHERE Mobitel_id = m_id) AND 

    UNIX_TIMESTAMP(end_time) AND 

    Valid = 1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `CleaningDataGpsLostOn` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `CleaningDataGpsLostOn`()
    SQL SECURITY INVOKER
    COMMENT 'Сия процедура удаляет не корректно оформленные диапазоны пропусков из таблицы datagpslost_on'
BEGIN

  /* удаляем записи у которых BeginSrvPacketId >= EndSrvPacketId */

  DELETE d FROM datagpslost_on d WHERE d.Begin_SrvPacketID >= d.End_SrvPacketID;

  /* удаляем записи у которых EndLogId - BeginLogId <= 1 */

  DELETE d FROM datagpslost_on d WHERE d.End_LogID - d.Begin_LogID <= 1;

  /* удаляем записи которые с заданным LogId и SrvPackId отсутвут в таблице datagps или datagps64 (фантомные записи) */



  CREATE TEMPORARY TABLE MobiTemp (Id bigint(20) UNSIGNED DEFAULT 0)ENGINE = INNODB

    CHARACTER SET cp1251

    COLLATE cp1251_general_ci

    COMMENT = 'Промежуточная временная таблица';



  INSERT INTO MobiTemp SELECT d.Id FROM datagpslost_on d WHERE 

  NOT EXISTS(SELECT NULL FROM datagps d1 WHERE (d.Mobitel_ID = d1.Mobitel_ID) AND ((d.Begin_LogID = d1.LogID) AND (d.Begin_SrvPacketID = d1.SrvPacketID))) AND 

  NOT EXISTS(SELECT NULL FROM datagps64 d2 WHERE (d.Mobitel_ID = d2.Mobitel_ID) AND ((d.Begin_LogID = d2.LogID) AND (d.Begin_SrvPacketID = d2.SrvPacketID))); 

  DELETE d FROM datagpslost_on d, MobiTemp m WHERE m.Id = d.Id;



  TRUNCATE MobiTemp;



  INSERT INTO MobiTemp SELECT d.Id FROM datagpslost_on d WHERE 

  NOT EXISTS(SELECT NULL FROM datagps d1 WHERE (d.Mobitel_ID = d1.Mobitel_ID) AND ((d.End_LogID = d1.LogID) AND (d.End_SrvPacketID = d1.SrvPacketID))) AND 

  NOT EXISTS(SELECT NULL FROM datagps64 d2 WHERE (d.Mobitel_ID = d2.Mobitel_ID) AND ((d.End_LogID = d2.LogID) AND (d.End_SrvPacketID = d2.SrvPacketID))); 

  DELETE d FROM datagpslost_on d, MobiTemp m WHERE m.Id = d.Id;



  DROP TEMPORARY TABLE MobiTemp;



  SELECT 1;

  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `dataview` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `dataview`(

  IN m_id INTEGER(11), 

  IN val BOOL, 

  IN begin_time DATETIME, 

  IN end_time DATETIME)
    SQL SECURITY INVOKER
BEGIN

  SELECT DataGps_ID AS DataGps_ID,

    Mobitel_ID AS Mobitel_ID,

    ((Latitude * 1.0000) / 600000) AS Lat,

    ((Longitude * 1.0000) / 600000) AS Lon,

    Altitude AS Altitude,

    FROM_UNIXTIME(UnixTime) AS `time`,

    (Speed * 1.852) AS speed,

    ((Direction * 360) / 255) AS direction,

    Valid AS Valid,

    (Sensor8 + (Sensor7 << 8) + (Sensor6 << 16) + 

      (Sensor5 << 24) + (Sensor4 << 32) + (Sensor3 << 40) + 

      (Sensor2 << 48) + (Sensor1 << 56)) AS sensor,

    Events AS Events,

    LogID AS LogID 

  FROM datagps 

  WHERE Mobitel_ID = m_id AND  

    (UnixTime BETWEEN UNIX_TIMESTAMP(begin_time) AND UNIX_TIMESTAMP(end_time)) AND                                                                                                                                

    (Valid = val) AND NOT (Latitude = 0 AND Longitude =0)

  ORDER BY UnixTime;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `delDupSrvPacket` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `delDupSrvPacket`()
    SQL SECURITY INVOKER
    COMMENT 'Удаляет дубликаты SrvPacketID из таблицы datagpsbuffer_on64'
BEGIN

DROP TEMPORARY TABLE IF EXISTS tmp64;




CREATE TEMPORARY TABLE tmp64 SELECT Mobitel_ID,

  Latitude,

  Longitude,

  Direction,

  Acceleration,

  UnixTime,

  Speed,

  Valid,

  Satellites,

  RssiGsm,

  `Events`,

  SensorsSet,

  Sensors,

  Voltage,

  DGPS,

  LogID,

  SrvPacketID

FROM datagpsbuffer_on64 d

GROUP BY d.SrvPacketID;



  TRUNCATE TABLE datagpsbuffer_on64;



  INSERT INTO datagpsbuffer_on64 SELECT Mobitel_ID,

  Latitude,

  Longitude,

  Direction,

  Acceleration,

  UnixTime,

  Speed,

  Valid,

  Satellites,

  RssiGsm,

  `Events`,

  SensorsSet,

  Sensors,

  Voltage,

  DGPS,

  LogID,

  SrvPacketID

FROM tmp64 d;



DROP TEMPORARY TABLE IF EXISTS tmp64;


END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DrListMobitelConfig` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DrListMobitelConfig`()
    SQL SECURITY INVOKER
    COMMENT 'Выборка настроек телетреков в режиме DirectOnline'
BEGIN
  SELECT m.Mobitel_id AS MobitelID, 
    imc.DevIdShort AS DevIdShort,
    cge.Pop3un AS Login, 
    cge.Pop3pw AS Password
  FROM Mobitels m JOIN ConfigMain cm ON m.Mobitel_id = cm.Mobitel_ID
    JOIN ConfigGprsEmail cge ON cm.ConfigGprsEmail_ID = cge.ID
    JOIN internalmobitelconfig imc ON m.InternalMobitelConfig_ID = imc.ID 
  WHERE (cge.ConfigGprsEmail_ID = (
      SELECT MAX(intCge.ConfigGprsEmail_ID)
      FROM ConfigGprsEmail intCge
      WHERE intCge.ID = cge.ID)) AND
    (imc.InternalMobitelConfig_ID = (
      SELECT MAX(intConf.InternalMobitelConfig_ID)
      FROM internalmobitelconfig intConf
      WHERE intConf.ID = imc.ID)) AND 
    (imc.devIdShort IS NOT NULL) AND (imc.devIdShort <> _cp1251 '')	AND 
    (cge.Pop3un IS NOT NULL) AND (cge.Pop3un <> _cp1251 '') 
  ORDER BY m.Mobitel_ID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `DrTransferBuffer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `DrTransferBuffer`()
    SQL SECURITY INVOKER
    COMMENT 'Переносит GPS данные DirectOnline из буферной таблицы в datagps'
BEGIN
  DECLARE MobitelIDInCursor INT; 
  DECLARE DataExists TINYINT DEFAULT 1; 
  
  DECLARE TmpMaxUnixTime INT; 

  DECLARE CursorMobitels CURSOR FOR
    SELECT DISTINCT(Mobitel_ID) FROM datagpsbuffer_dr;

  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;


    
  
  
  DELETE o
  FROM online o, datagpsbuffer_dr dgb
  WHERE (o.Mobitel_ID = dgb.Mobitel_ID) AND (o.LogID = dgb.LogID);
  
  SET DataExists = 1;
  OPEN CursorMobitels;
  FETCH CursorMobitels INTO MobitelIDInCursor;
  WHILE DataExists = 1 DO
    
    SELECT COALESCE(MAX(UnixTime), 0) INTO TmpMaxUnixTime
    FROM online FORCE INDEX (IDX_Mobitelid)
    WHERE Mobitel_ID = MobitelIDInCursor;

    
    INSERT INTO online (
      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
      Direction, Speed, Valid, `Events`,
      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
      Counter1, Counter2, Counter3, Counter4, whatIs)
    SELECT
      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
      Direction, Speed, Valid, `Events`,
      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
      Counter1, Counter2, Counter3, Counter4, whatIs
    FROM (
      SELECT *
      FROM datagpsbuffer_dr
      WHERE (Mobitel_ID = MobitelIDInCursor)  AND 
        (UnixTime > TmpMaxUnixTime) AND (Valid = 1)
      GROUP BY Mobitel_ID, LogID
      ORDER BY UnixTime DESC
      LIMIT 100) AS T
    ORDER BY UnixTime ASC;

    
    DELETE FROM online
    WHERE (Mobitel_ID = MobitelIDInCursor) AND (UnixTime < 
      (SELECT COALESCE(MIN(UnixTime), 0) 
       FROM (
         SELECT UnixTime
         FROM online
         WHERE Mobitel_ID = MobitelIDInCursor 
         ORDER BY UnixTime DESC
         LIMIT 100) T1)); 
  
    FETCH CursorMobitels INTO MobitelIDInCursor;
  END WHILE;
  CLOSE CursorMobitels;
   


  
  UPDATE datagps d, datagpsbuffer_dr b SET
    d.Latitude = b.Latitude,
    d.Longitude = b.Longitude,
    d.Altitude = b.Altitude,
    d.UnixTime = b.UnixTime,
    d.Speed = b.Speed,
    d.Direction = b.Direction,
    d.Valid = b.Valid,
    d.InMobitelID = b.LogID,
    d.`Events` = b.`Events`,
    d.Sensor1 = b.Sensor1,
    d.Sensor2 = b.Sensor2,
    d.Sensor3 = b.Sensor3,
    d.Sensor4 = b.Sensor4,
    d.Sensor5 = b.Sensor5,
    d.Sensor6 = b.Sensor6,
    d.Sensor7 = b.Sensor7,
    d.Sensor8 = b.Sensor8,
    d.whatIs = b.whatIs,
    d.Counter1 = b.Counter1,
    d.Counter2 = b.Counter2,
    d.Counter3 = b.Counter3,
    d.Counter4 = b.Counter4
  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);

  
  DELETE b
  FROM datagps d, datagpsbuffer_dr b
  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);

  
  INSERT INTO datagps (
    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
    Direction, Speed, Valid, `Events`, InMobitelID,
    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
    Counter1, Counter2, Counter3, Counter4, whatIs)
  SELECT
    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
    Direction, Speed, Valid, `Events`, LogID,
    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
    Counter1, Counter2, Counter3, Counter4, whatIs
  FROM datagpsbuffer_dr
  GROUP BY Mobitel_ID, LogID;

  
  TRUNCATE TABLE datagpsbuffer_dr;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `ExecuteQuery` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `ExecuteQuery`(IN sqlQuery TINYTEXT)
    SQL SECURITY INVOKER
BEGIN
  SET @s = sqlQuery;
  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getDiapasone` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getDiapasone`(

  IN mobile INTEGER(11),  

  IN prevLogId int, 

  IN currLogId int)
    SQL SECURITY INVOKER
BEGIN

  SELECT DataGps_ID AS DataGps_ID,

    Mobitel_ID AS Mobitel_ID,

    ((Latitude * 1.0000) / 600000) AS Lat,

    ((Longitude * 1.0000) / 600000) AS Lon,

    Altitude AS Altitude,

    FROM_UNIXTIME(UnixTime) AS `time`,

    (Speed * 1.852) AS speed,

    ((Direction * 360) / 255) AS direction,

    Valid AS Valid,

    (Sensor8 + (Sensor7 << 8) + (Sensor6 << 16) + 

      (Sensor5 << 24) + (Sensor4 << 32) + (Sensor3 << 40) + 

      (Sensor2 << 48) + (Sensor1 << 56)) AS sensor,

    Events AS Events,

    LogID AS LogID, 

    SrvPacketID AS srvPack

  FROM datagps 

  WHERE Mobitel_ID = mobile AND  Valid = 1 AND  

    (LogID BETWEEN prevLogId AND currLogId) ORDER BY UnixTime;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `getDiapasone64` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `getDiapasone64`(

  IN mobile INTEGER(11),  

  IN prevLogId int, 

  IN currLogId int)
    SQL SECURITY INVOKER
BEGIN

  SELECT  DataGps_ID, Mobitel_ID,

    ((Latitude * 1.000) / 10000000) AS Lat,

    ((Longitude * 1.000) / 10000000) AS Lng,

    Acceleration , 

    FROM_UNIXTIME(UnixTime) AS `time` , 

    Speed /10 AS Speed, 

    Valid,

    Sensors AS sensor, 

  `Events`, 

  LogID ,

  Voltage,

  DGPS,

  Direction,

  Satellites,

  SensorsSet,

  RssiGsm,

  SrvPacketID AS srvPack FROM datagps64 d WHERE Mobitel_ID = mobile AND d.Valid = 1 AND  

    (LogID BETWEEN prevLogId AND currLogId) ORDER BY UnixTime;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `hystoryOnline` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `hystoryOnline`(IN id INTEGER(11))
    SQL SECURITY INVOKER
BEGIN

  SELECT LogID, Mobitel_Id, 

    FROM_UNIXTIME(unixtime)  AS `time`,

    ROUND((Latitude / 600000.00000), 6) AS Lat,

    ROUND((Longitude / 600000.00000), 6)  AS Lon,

    Altitude  AS Altitude,

    (Speed * 1.852) AS speed,

    ((Direction * 360) / 255) AS direction,

    Valid AS Valid,

    (Sensor8 + (Sensor7 << 8) + (Sensor6 << 16) + 

      (Sensor5 << 24) + (Sensor4 << 32) + (Sensor3 << 40) + 

      (Sensor2 << 48) + (Sensor1 << 56)) AS sensor,

    Events AS Events,

    DataGPS_ID  AS 'DataGPS_ID'

  FROM online

  WHERE  datagps_id > id

  ORDER BY datagps_id DESC;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `IsTeletrackDatabase` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `IsTeletrackDatabase`()
    SQL SECURITY INVOKER
    COMMENT 'Проверка базы данных на соответствие структуре системы Teletrack'
BEGIN
  DECLARE i TINYINT;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `new_proc` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `new_proc`(IN sensor INTEGER(11), IN m_id INTEGER(11), IN s_id INTEGER(11), IN begin_time DATETIME, IN end_time DATETIME)
    READS SQL DATA
    SQL SECURITY INVOKER
BEGIN                                                                                                                                      
select                                                                                                                                     
    round((`datagps`.`Longitude` / 600000),6) AS `lon`,                                                                                    
    round((`datagps`.`Latitude` / 600000),6) AS `lat`,                                                                                     
    `datagps`.`Mobitel_ID` AS `Mobitel_ID`,                                                                                                
    `sensordata`.`Value` AS `Value`,                                                                                                       
    (`datagps`.`Speed` * 1.852) AS `speed`,                                                                                                
    from_unixtime(`datagps`.`UnixTime`) AS `time`,                                                                                         
    `sensordata`.`sensor_id` AS `sensor_id`,                                                                                               
    `datagps`.`DataGps_ID` AS `datagps_id`,                                                                                                
    `datagps`.`Sensor1`&`sensor` AS `sensor`                                                                                              
  from                                                                                                                                     
    (`sensordata` join `datagps` on((`sensordata`.`datagps_id` = `datagps`.`DataGps_ID`)))                                                 
  where                                                                                                                                    
    (`datagps`.`Valid` = 1) and                                                                                                            
    ( datagps.UnixTime BETWEEN UNIX_TIMESTAMP(begin_time) and UNIX_TIMESTAMP(end_time))                                                    
    and datagps.Mobitel_ID = m_id AND sensordata.sensor_id = s_id                                                                          
  order by                                                                                                                                 
    `datagps`.`UnixTime`,`datagps`.`Mobitel_ID`,`sensordata`.`sensor_id`;                                                                  
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnCorrectInfotrackLogId` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnCorrectInfotrackLogId`()
    SQL SECURITY INVOKER
    COMMENT 'Корректировка значений LogId и InMobitelID в данных инфотреков'
BEGIN

  DECLARE MobitelIDInCursor INT; /* Текущее значение MobitelID */

  DECLARE DataExists TINYINT DEFAULT 1; /* Флаг наличия данных после фетча */

  DECLARE MaxLogId INT DEFAULT 0; /* Максимальный LogId для данного дивайса */

  DECLARE SrvPacketIDInCursor BIGINT DEFAULT 0; /* Текущее значение серверного пакета */

    

  /* Курсор по инфотрекам в множестве новых данных. 

     Все инфотреки начинаются с буквы I */

  DECLARE CursorInfoTrack CURSOR FOR

    SELECT DISTINCT buf.Mobitel_ID

    FROM datagpsbuffer_on buf 

      JOIN mobitels m ON (buf.mobitel_id = m.mobitel_id)

      JOIN internalmobitelconfig c ON (c.ID = m.InternalMobitelConfig_ID)

    WHERE (c.InternalMobitelConfig_ID = (

      SELECT intConf.InternalMobitelConfig_ID

      FROM internalmobitelconfig intConf

      WHERE (intConf.ID = c.ID) 

      ORDER BY intConf.InternalMobitelConfig_ID DESC

      LIMIT 1)) AND 

      (c.devIdShort LIKE "I%") AND (buf.LogId = 0)

    ORDER BY 1;

    

  /* Курсор по данным телетрека */

  DECLARE CursorInfoTrackUpdateSet CURSOR FOR

    SELECT SrvPacketID 

    FROM datagpsbuffer_on

    WHERE (Mobitel_ID = MobitelIDInCursor) AND (LogId = 0)

    ORDER BY UnixTime;  



  /* Обработчик отсутствующей записи при FETCH следующей записи */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

  

  OPEN CursorInfoTrack;

  FETCH CursorInfoTrack INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* Находим максимальный LogId для данного инфотрека */

    SELECT COALESCE(MAX(LogID), 0) INTO	MaxLogId

    FROM DataGps

    WHERE Mobitel_ID = MobitelIDInCursor;



    /* Обновляем LogId и InMobitelID в наборе новых данных текущего инфотрека */

    OPEN CursorInfoTrackUpdateSet;

    FETCH CursorInfoTrackUpdateSet INTO SrvPacketIDInCursor;

    WHILE DataExists = 1 DO

      SET MaxLogId = MaxLogId + 1;



      UPDATE datagpsbuffer_on

      SET LogId = MaxLogId, InMobitelID = MaxLogId

      WHERE SrvPacketID = SrvPacketIDInCursor;



      FETCH CursorInfoTrackUpdateSet INTO SrvPacketIDInCursor;

    END WHILE;

    CLOSE CursorInfoTrackUpdateSet;

 

    SET DataExists = 1;

    FETCH CursorInfoTrack INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorInfoTrack;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnCorrectInfotrackLogId64` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnCorrectInfotrackLogId64`()
    SQL SECURITY INVOKER
    COMMENT 'РљРѕСЂСЂРµРєС‚РёСЂРѕРІРєР° Р·РЅР°С‡РµРЅРёР№ LogId Рё InMobitelID РІ РґР°РЅРЅС‹С… РёРЅС„РѕС‚СЂРµРєРѕРІ'
BEGIN

  DECLARE MobitelIDInCursor int; /* РўРµРєСѓС‰РµРµ Р·РЅР°С‡РµРЅРёРµ MobitelID */

  DECLARE DataExists tinyint DEFAULT 1; /* Р¤Р»Р°Рі РЅР°Р»РёС‡РёСЏ РґР°РЅРЅС‹С… РїРѕСЃР»Рµ С„РµС‚С‡Р° */

  DECLARE MaxLogId int DEFAULT 0; /* РњР°РєСЃРёРјР°Р»СЊРЅС‹Р№ LogId РґР»СЏ РґР°РЅРЅРѕРіРѕ РґРёРІР°Р№СЃР° */

  DECLARE SrvPacketIDInCursor bigint DEFAULT 0; /* РўРµРєСѓС‰РµРµ Р·РЅР°С‡РµРЅРёРµ СЃРµСЂРІРµСЂРЅРѕРіРѕ РїР°РєРµС‚Р° */



  /* РљСѓСЂСЃРѕСЂ РїРѕ РёРЅС„РѕС‚СЂРµРєР°Рј РІ РјРЅРѕР¶РµСЃС‚РІРµ РЅРѕРІС‹С… РґР°РЅРЅС‹С…. 

     Р’СЃРµ РёРЅС„РѕС‚СЂРµРєРё РЅР°С‡РёРЅР°СЋС‚СЃСЏ СЃ Р±СѓРєРІС‹ I */

  DECLARE CursorInfoTrack CURSOR FOR

  SELECT DISTINCT

    buf.Mobitel_ID

  FROM datagpsbuffer_on64 buf

    JOIN mobitels m

      ON (buf.mobitel_id = m.mobitel_id)

    JOIN internalmobitelconfig c

      ON (c.ID = m.InternalMobitelConfig_ID)

  WHERE (c.InternalMobitelConfig_ID = (SELECT

      intConf.InternalMobitelConfig_ID

    FROM internalmobitelconfig intConf

    WHERE (intConf.ID = c.ID)

    ORDER BY intConf.InternalMobitelConfig_ID DESC

    LIMIT 1)) AND

  (c.devIdShort LIKE "I%") AND (buf.LogId = 0)

  ORDER BY 1;



  /* РљСѓСЂСЃРѕСЂ РїРѕ РґР°РЅРЅС‹Рј С‚РµР»РµС‚СЂРµРєР° */

  DECLARE CursorInfoTrackUpdateSet CURSOR FOR

  SELECT

    SrvPacketID

  FROM datagpsbuffer_on64

  WHERE (Mobitel_ID = MobitelIDInCursor) AND (LogId = 0)

  ORDER BY UnixTime;



  /* РћР±СЂР°Р±РѕС‚С‡РёРє РѕС‚СЃСѓС‚СЃС‚РІСѓСЋС‰РµР№ Р·Р°РїРёСЃРё РїСЂРё FETCH СЃР»РµРґСѓСЋС‰РµР№ Р·Р°РїРёСЃРё */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  OPEN CursorInfoTrack;

  FETCH CursorInfoTrack INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* РќР°С…РѕРґРёРј РјР°РєСЃРёРјР°Р»СЊРЅС‹Р№ LogId РґР»СЏ РґР°РЅРЅРѕРіРѕ РёРЅС„РѕС‚СЂРµРєР° */

    SELECT

      COALESCE(MAX(LogID), 0) INTO MaxLogId

    FROM DataGps

    WHERE Mobitel_ID = MobitelIDInCursor;



    /* РћР±РЅРѕРІР»СЏРµРј LogId Рё InMobitelID РІ РЅР°Р±РѕСЂРµ РЅРѕРІС‹С… РґР°РЅРЅС‹С… С‚РµРєСѓС‰РµРіРѕ РёРЅС„РѕС‚СЂРµРєР° */

    OPEN CursorInfoTrackUpdateSet;

    FETCH CursorInfoTrackUpdateSet INTO SrvPacketIDInCursor;

    WHILE DataExists = 1 DO

      SET MaxLogId = MaxLogId + 1;



      UPDATE datagpsbuffer_on64

      SET LogId = MaxLogId,

          InMobitelID = MaxLogId

      WHERE SrvPacketID = SrvPacketIDInCursor;



      FETCH CursorInfoTrackUpdateSet INTO SrvPacketIDInCursor;

    END WHILE;

    CLOSE CursorInfoTrackUpdateSet;



    SET DataExists = 1;

    FETCH CursorInfoTrack INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorInfoTrack;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnDeleteDuplicates` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnDeleteDuplicates`()
    SQL SECURITY INVOKER
    COMMENT 'Удаление повторяющихся строк из datagpsbuffer_on'
BEGIN

  /* Удаляет дубликаты строк из таблицы datagpsbuffer_on. Критерием поиска

   являются одинаковые значения в полях Mobitel_Id + LogID. Из множества

   повторяющихся записей в таблице datagpsbuffer_on останется только одна - 

   последняя принятая телематическим сервером, т.е. с максимальным SrvPacketID. */

   

  /* Текущий MobitelId в курсоре CursorDuplicateGroups */

  DECLARE MobitelIdInCursor INT DEFAULT 0; 

  /* Текущий LogID в курсоре CursorDuplicateGroups */

  DECLARE LogIdInCursor INT DEFAULT 0; 

  /* Max SrvPacketId в множестве строк текущей группы дубликатов */

  DECLARE MaxSrvPacketId BIGINT DEFAULT 0; 

  DECLARE DataExists TINYINT DEFAULT 1;



  /* Курсор для прохода по всем группам дубликатов */  

  DECLARE CursorDuplicateGroups CURSOR FOR

    SELECT Mobitel_Id, LogId 

    FROM datagpsbuffer_on

    GROUP BY Mobitel_Id, LogId 

    HAVING COUNT(LogId) > 1;

    

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  SET DataExists = 1;

  OPEN CursorDuplicateGroups;

  FETCH CursorDuplicateGroups INTO MobitelIdInCursor, LogIdInCursor;

  WHILE DataExists = 1 DO

    SELECT MAX(SrvPacketID) INTO MaxSrvPacketId

    FROM datagpsbuffer_on 

    WHERE (Mobitel_ID = MobitelIdInCursor) AND (LogId = LogIdInCursor);

         

    DELETE FROM datagpsbuffer_on  

    WHERE (Mobitel_ID = MobitelIdInCursor) AND (LogId = LogIdInCursor) 

      AND (SrvPacketID < MaxSrvPacketId);

        

    FETCH CursorDuplicateGroups INTO MobitelIdInCursor, LogIdInCursor;

  END WHILE;

  CLOSE CursorDuplicateGroups; 

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnDeleteDuplicates64` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnDeleteDuplicates64`()
    SQL SECURITY INVOKER
    COMMENT 'РЈРґР°Р»РµРЅРёРµ РїРѕРІС‚РѕСЂСЏСЋС‰РёС…СЃСЏ СЃС‚СЂРѕРє РёР· datagpsbuffer_on64'
BEGIN

  /* РЈРґР°Р»СЏРµС‚ РґСѓР±Р»РёРєР°С‚С‹ СЃС‚СЂРѕРє РёР· С‚Р°Р±Р»РёС†С‹ datagpsbuffer_on64. РљСЂРёС‚РµСЂРёРµРј РїРѕРёСЃРєР°

   СЏРІР»СЏСЋС‚СЃСЏ РѕРґРёРЅР°РєРѕРІС‹Рµ Р·РЅР°С‡РµРЅРёСЏ РІ РїРѕР»СЏС… Mobitel_Id + LogID. РР· РјРЅРѕР¶РµСЃС‚РІР°

   РїРѕРІС‚РѕСЂСЏСЋС‰РёС…СЃСЏ Р·Р°РїРёСЃРµР№ РІ С‚Р°Р±Р»РёС†Рµ datagpsbuffer_on64 РѕСЃС‚Р°РЅРµС‚СЃСЏ С‚РѕР»СЊРєРѕ РѕРґРЅР° - 

   РїРѕСЃР»РµРґРЅСЏСЏ РїСЂРёРЅСЏС‚Р°СЏ С‚РµР»РµРјР°С‚РёС‡РµСЃРєРёРј СЃРµСЂРІРµСЂРѕРј, С‚.Рµ. СЃ РјР°РєСЃРёРјР°Р»СЊРЅС‹Рј SrvPacketID. */



  /* РўРµРєСѓС‰РёР№ MobitelId РІ РєСѓСЂСЃРѕСЂРµ CursorDuplicateGroups */

  DECLARE MobitelIdInCursor int DEFAULT 0;

  /* РўРµРєСѓС‰РёР№ LogID РІ РєСѓСЂСЃРѕСЂРµ CursorDuplicateGroups */

  DECLARE LogIdInCursor int DEFAULT 0;

  /* Max SrvPacketId РІ РјРЅРѕР¶РµСЃС‚РІРµ СЃС‚СЂРѕРє С‚РµРєСѓС‰РµР№ РіСЂСѓРїРїС‹ РґСѓР±Р»РёРєР°С‚РѕРІ */

  DECLARE MaxSrvPacketId bigint DEFAULT 0;

  DECLARE DataExists tinyint DEFAULT 1;



  /* РљСѓСЂСЃРѕСЂ РґР»СЏ РїСЂРѕС…РѕРґР° РїРѕ РІСЃРµРј РіСЂСѓРїРїР°Рј РґСѓР±Р»РёРєР°С‚РѕРІ */

  DECLARE CursorDuplicateGroups CURSOR FOR

  SELECT

    Mobitel_Id,

    LogId

  FROM datagpsbuffer_on64

  GROUP BY Mobitel_Id,

           LogId

  HAVING COUNT(LogId) > 1;



  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  SET DataExists = 1;

  OPEN CursorDuplicateGroups;

  FETCH CursorDuplicateGroups INTO MobitelIdInCursor, LogIdInCursor;

  WHILE DataExists = 1 DO

    SELECT

      MAX(SrvPacketID) INTO MaxSrvPacketId

    FROM datagpsbuffer_on64

    WHERE (Mobitel_ID = MobitelIdInCursor) AND (LogId = LogIdInCursor);



    DELETE

      FROM datagpsbuffer_on64

    WHERE (Mobitel_ID = MobitelIdInCursor) AND (LogId = LogIdInCursor)

      AND (SrvPacketID < MaxSrvPacketId);



    FETCH CursorDuplicateGroups INTO MobitelIdInCursor, LogIdInCursor;

  END WHILE;

  CLOSE CursorDuplicateGroups;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnDeleteLostRange` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnDeleteLostRange`(IN MobitelID INTEGER(11), IN BeginSrvPacketID BIGINT)
    SQL SECURITY INVOKER
    COMMENT 'Удаление указанного диапазона пропусков из datagpslost_on.'
BEGIN

  /* Определяем является ли запись, предназначенная для удаления,

     первой в списке заданного телетрека. Если является - тогда

     внесем поправку в значение ConfirmedID */  

     

  DECLARE MinBeginSrvPacketID BIGINT; -- Минимальное значение Begin_SrvPacketID для данного телетрека

  DECLARE NewConfirmedID INT; -- Новое значение ConfirmedID  



  /* Нахождение минимального значения Begin_SrvPacketID */  

  SELECT MIN(Begin_SrvPacketID) INTO MinBeginSrvPacketID

  FROM datagpslost_on

  WHERE Mobitel_ID = MobitelID;

  

  /* Если есть данные о пропусках у телетрека - продолжим */

  IF MinBeginSrvPacketID IS NOT NULL THEN

    /* Если удаляем первую запись - запомним новое значение ConfirmedID */

    IF MinBeginSrvPacketID = BeginSrvPacketID  THEN

      SELECT End_LogID INTO NewConfirmedID

      FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (Begin_SrvPacketID = BeginSrvPacketID);

    END IF;

  

    /* Удаление записи */

    DELETE FROM  datagpslost_on

    WHERE (Mobitel_ID = MobitelID) AND (Begin_SrvPacketID = BeginSrvPacketID);

    

    /* Если удалили первую запись - обновим ConfirmedID */  

    IF MinBeginSrvPacketID = BeginSrvPacketID  THEN

      UPDATE mobitels

      SET ConfirmedID = NewConfirmedID

      WHERE (Mobitel_ID = MobitelID) AND (NewConfirmedID > ConfirmedID);

    END IF;

  END IF;  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnInsertDatagpsLost` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnInsertDatagpsLost`(

  IN MobitelID INT, 

  IN BeginLogID INT, 

  IN EndLogID INT,

  IN BeginSrvPacketID BIGINT, 

  IN EndSrvPacketID BIGINT)
    SQL SECURITY INVOKER
    COMMENT 'Вставка данных в таблицу datagpslost_on.'
BEGIN

  /* Флаг существования записи с заданными Mobitel_ID и Begin_LogID

     в таблице datagpslost_on */

  DECLARE MobitelId_BeginLogID_Exists TINYINT DEFAULT 0; 

  /* Флаг существования записи с заданными Mobitel_ID и Begin_SrvPacketID

     в таблице datagpslost_on  */

  DECLARE MobitelId_BeginSrvPacketID_Exists TINYINT DEFAULT 0;

  

  DECLARE RowBeginSrvPacketID BIGINT; 

  

  IF EXISTS(

    SELECT NULL

    FROM datagpslost_on

    WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID = BeginLogID))

  THEN

    SET MobitelId_BeginLogID_Exists = 1;

  END IF;



  IF EXISTS(

    SELECT NULL

    FROM datagpslost_on

    WHERE (Mobitel_ID = MobitelID) AND (Begin_SrvPacketID = BeginSrvPacketID))

  THEN

    SET MobitelId_BeginSrvPacketID_Exists = 1;

  END IF;



  IF (MobitelId_BeginLogID_Exists = 0) AND (MobitelId_BeginSrvPacketID_Exists = 0)

  THEN

    /* Вставка новой записи */

    INSERT INTO datagpslost_on(Mobitel_ID, Begin_LogID, End_LogID, Begin_SrvPacketID, End_SrvPacketID)

    VALUES (MobitelID, BeginLogID, EndLogID, BeginSrvPacketID, EndSrvPacketID);

  ELSE

    IF ((MobitelId_BeginLogID_Exists = 1) AND (MobitelId_BeginSrvPacketID_Exists = 1))

    THEN

      /* Обновление существующей записи */

      UPDATE datagpslost_on

      SET End_LogID = EndLogID, End_SrvPacketID = EndSrvPacketID

      WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID = BeginLogID);

    ELSE

      /* Комбинацию MobitelId_BeginLogID_Exists = 0, MobitelId_BeginSrvPacketID_Exists = 1

         игнорируем - это исключительная ситуация, которая не должна возникать. */

      SELECT Begin_SrvPacketID

      INTO RowBeginSrvPacketID

      FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID = BeginLogID)

      LIMIT 1;

      

      IF ((MobitelId_BeginLogID_Exists = 1) AND (MobitelId_BeginSrvPacketID_Exists = 0) AND

        (BeginSrvPacketID < RowBeginSrvPacketID))

      THEN

        /* В эту ветвь попадаем, если телетрек отдаст одну и ту же запись несколько раз 

           (LogID у записей один и тотже, а SrvPacketID различны) */

        UPDATE datagpslost_on

        SET End_LogID = EndLogID, Begin_SrvPacketID = BeginSrvPacketID, End_SrvPacketID = EndSrvPacketID

        WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID = BeginLogID);

      END IF;

    END IF;

  END IF;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `online` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `online`()
    SQL SECURITY INVOKER
BEGIN

  SELECT Log_ID, MobitelId, 

    (SELECT DataGPS_ID

    FROM `online` 

    WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS 'DataGPS_ID',

      (SELECT from_unixtime(unixtime) 

      FROM `online` 

      WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS 'time',

        (SELECT ROUND((`Latitude` / 600000.00000),6)

        FROM `online` 

        WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `Lat`,

          (SELECT ROUND((`Longitude` / 600000.00000),6) 

          FROM `online` 

          WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `Lon`,

            (SELECT `Altitude` 

            FROM `online` 

            WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `Altitude`,

              (SELECT (`Speed` * 1.852)

              FROM `online` 

              WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `speed`,

                (SELECT ((`Direction` * 360) / 255)

                FROM `online` 

                WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `direction`,

                  (SELECT `Valid`

                  FROM `online` 

                  WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `Valid`,

                    (SELECT (((((((`Sensor8` + 

                    (`Sensor7` << 8)) + 

                    (`Sensor6` << 16)) + 

                    (`Sensor5` << 24)) + 

                    (`Sensor4` << 32)) + 

                    (`Sensor3` << 40)) + 

                    (`Sensor2` << 48)) + 

                    (`Sensor1` << 56)) 

                    FROM `online` 

                    WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `sensor`,

                      (SELECT `Events`

                      FROM `online` 

                      WHERE mobitel_id = MobitelId AND LogId = Log_ID Limit 1) AS `Events`

   

  FROM (SELECT MAX(`LogID`) AS `Log_ID`, mobitel_id AS MobitelId

        FROM `online` 

        GROUP BY `Mobitel_ID`) T1;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnListMobitelConfig` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnListMobitelConfig`()
    SQL SECURITY INVOKER
    COMMENT 'Выборка настроек телетреков которые могут работать в online режиме'
BEGIN

  DECLARE MobitelIDInCursor INT;

  DECLARE DevIdShortInCursor CHAR(4);

  DECLARE DataExists TINYINT DEFAULT 1;



  /* Курсор в множестве настроенных телетреков */

  DECLARE CursorMobitels CURSOR FOR

    SELECT m.Mobitel_ID AS MobitelID, c.devIdShort AS DevIdShort

    FROM mobitels m

      JOIN internalmobitelconfig c ON (c.ID = m.InternalMobitelConfig_ID)

    WHERE (c.InternalMobitelConfig_ID = (

      SELECT intConf.InternalMobitelConfig_ID

      FROM internalmobitelconfig intConf

      WHERE (intConf.ID = c.ID)

      ORDER BY intConf.InternalMobitelConfig_ID DESC

      LIMIT 1)) AND

      (c.devIdShort IS NOT NULL) AND (c.devIdShort <> _cp1251'')

    ORDER BY m.Mobitel_ID;



  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  /* Временная таблица настроек телетрека */

  CREATE TEMPORARY TABLE tmpMobitelsConfig (

    MobitelID INT NOT NULL,

    DevIdShort CHAR(4) NOT NULL,

    LastPacketID BIGINT NOT NULL) ENGINE = MEMORY;



  OPEN CursorMobitels;

  FETCH CursorMobitels INTO MobitelIDInCursor, DevIdShortInCursor;

  WHILE DataExists = 1 DO



    INSERT INTO tmpMobitelsConfig SELECT MobitelIDInCursor, DevIdShortInCursor,

      (SELECT COALESCE(MAX(SrvPacketID), 0)

       FROM datagps

       WHERE Mobitel_ID = MobitelIDInCursor) AS LastPacketID;



    FETCH CursorMobitels INTO MobitelIDInCursor, DevIdShortInCursor;

  END WHILE;

  CLOSE CursorMobitels;



  SELECT MobitelID, DevIdShort, LastPacketID

  FROM tmpMobitelsConfig;



  DROP TEMPORARY TABLE tmpMobitelsConfig;  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnListMobitelConfig64` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnListMobitelConfig64`()
    SQL SECURITY INVOKER
    COMMENT 'Выборка настроек телетреков которые могут работать в online режиме для datagps и datagps64'
BEGIN

  DECLARE MobitelIDInCursor INT;

  DECLARE Is64Cursor tinyint;

  DECLARE DevIdShortInCursor CHAR(4);

  DECLARE DataExists TINYINT DEFAULT 1;



  /* Курсор в множестве настроенных телетреков */

  DECLARE CursorMobitels CURSOR FOR

    SELECT m.Mobitel_ID AS MobitelID, m.Is64bitPackets AS Is64, c.devIdShort AS DevIdShort

    FROM mobitels m

      JOIN internalmobitelconfig c ON (c.ID = m.InternalMobitelConfig_ID)

    WHERE (c.InternalMobitelConfig_ID = (

      SELECT intConf.InternalMobitelConfig_ID

      FROM internalmobitelconfig intConf

      WHERE (intConf.ID = c.ID)

      ORDER BY intConf.InternalMobitelConfig_ID DESC

      LIMIT 1)) AND

      (c.devIdShort IS NOT NULL) AND (c.devIdShort <> _cp1251'')

    ORDER BY m.Mobitel_ID;



  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  /* Временная таблица настроек телетрека */

  CREATE TEMPORARY TABLE tmpMobitelsConfig (

    MobitelID INT NOT NULL,

    Is64 tinyint NOT NULL,

    DevIdShort CHAR(4) NOT NULL,

    LastPacketID BIGINT NOT NULL,

    LastPacketID64 BIGINT NOT NULL) ENGINE = MEMORY;



  OPEN CursorMobitels;

  FETCH CursorMobitels INTO MobitelIDInCursor, Is64Cursor, DevIdShortInCursor;

  WHILE DataExists = 1 DO



    INSERT INTO tmpMobitelsConfig SELECT MobitelIDInCursor, Is64Cursor, DevIdShortInCursor,

      (SELECT COALESCE(MAX(d.SrvPacketID), 0)

       FROM datagps d

       WHERE d.Mobitel_ID = MobitelIDInCursor) AS LastPacketID,

      (SELECT COALESCE(MAX(t.SrvPacketID), 0)

       FROM datagps64 t

       WHERE t.Mobitel_ID = MobitelIDInCursor) AS LastPacketID64;



    FETCH CursorMobitels INTO MobitelIDInCursor, Is64Cursor, DevIdShortInCursor;

  END WHILE;

  CLOSE CursorMobitels;



  SELECT MobitelID, Is64, DevIdShort, LastPacketID, LastPacketID64

  FROM tmpMobitelsConfig;



  DROP TEMPORARY TABLE tmpMobitelsConfig;

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnLostDataGPS` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnLostDataGPS`(IN MobitelID INTEGER(11), OUT NeedOptimize TINYINT)
    SQL SECURITY INVOKER
    COMMENT 'Поиск пропусков в online данных заданного телетрека и заполнение таблиц.'
BEGIN



  /********** DECLARE **********/

  

  DECLARE CurrentConfirmedID INT DEFAULT 0; /* Текущее значение ConfirmedID */

  DECLARE NewConfirmedID INT DEFAULT 0; /* Новое значение ConfirmedID */

  DECLARE StartLogIDSelect INT DEFAULT 0; /* min LogID для выборки */

  DECLARE FinishLogIDSelect INT DEFAULT 0; /* max LogID для выборки */

  DECLARE MaxEndLogID INT DEFAULT 0; /* max End_LogID в пропущенных диапазонах данного телетрека */

  

  DECLARE MinLogID_n INT DEFAULT 0; /* Минимальный LogID после ConfirmedID в множестве новых данных */

  DECLARE MaxLogID_n INT DEFAULT 0; /* Максимальный LogID в множестве новых данных */

  

  DECLARE DataExists TINYINT DEFAULT 1; /* Флаг наличия данных после фетча */

    

  DECLARE NewLogID INT; /* Значение LogID в курсоре CursorNewLogID */

  DECLARE NewSrvPacketID BIGINT; /* Значение SrvPacketID в курсоре CursorNewLogID */

  DECLARE FirstLogID INT DEFAULT -9999999; /* Первое значение LogID для анализа */

  DECLARE SecondLogID INT; /* Второе значение LogID для анализа */

  DECLARE FirstSrvPacketID BIGINT; /* Первое значение SrvPacketID */

  DECLARE SecondSrvPacketID BIGINT; /* Второе значение SrvPacketID */

  

  DECLARE tmpSrvPacketID BIGINT DEFAULT 0; /* Значение SrvPacketID для первой искусственной записи в анализе новых данных, не пересекающихся с существующими */  

  

  /* Курсор для прохода по новым LogID */

  DECLARE CursorNewLogID CURSOR FOR

    SELECT LogID, SrvPacketID 

    FROM datagpslost_ontmp;

    

  /* Обработчик отсутствующей записи при FETCH следующей записи */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

  

  /********** END DECLARE **********/



  SET NeedOptimize = 0;

 

  SELECT COALESCE(ConfirmedID, 0) INTO CurrentConfirmedID

  FROM mobitels

  WHERE Mobitel_ID = MobitelID;



  /* Минимальный LogID в новом наборе данных c которым будем работать */

  SELECT MIN(LogID) INTO MinLogID_n

  FROM datagpsbuffer_on

  WHERE (Mobitel_ID = MobitelID) AND (LogID > CurrentConfirmedID);

  

  /* Максимальный LogID в новом наборе данных c которым будем работать */

  SELECT MAX(LogID) INTO MaxLogID_n

  FROM datagpsbuffer_on 

  WHERE Mobitel_ID = MobitelID;  

  

  /* Максимальный End_LogID в пропущенных диапазонах данного телетрека */

  SELECT COALESCE(MAX(End_LogID), 0) INTO MaxEndLogID

  FROM datagpslost_on 

  WHERE Mobitel_ID = MobitelID;  

  

  /* Проверим есть ли данные для анализа */

  IF MinLogID_n IS NOT NULL THEN

  

    /********** PREPARE **********/

    

    TRUNCATE TABLE datagpslost_ontmp;

  

    IF MinLogID_n < MaxEndLogID THEN

      /* Если  MinLogID_n < MaxEndLogID значит пришли данные, 

       которые возможно закроют часть пропусков. */

       

      /* Находим стартовый LogID для пересчета. */

      SELECT COALESCE(Begin_LogID, MinLogID_n) INTO StartLogIDSelect

      FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (MinLogID_n > Begin_LogID) 

        AND (MinLogID_n < End_LogID);

      

      /* Находим последний LogID для пересчета. */  

      SELECT COALESCE(End_LogID, MaxLogID_n) INTO FinishLogIDSelect

      FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (MaxLogID_n > Begin_LogID) 

        AND (MaxLogID_n < End_LogID);

       

      /* Удалим старые записи из реестра пропущенных записей 

         (стали неактуальными, надо пересчитать) */

      DELETE FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID >= StartLogIDSelect)

        AND (Begin_LogID <= FinishLogIDSelect);

      

      /* Если первая запись LogID = 0 и этой записи нет в DataGps, тогда

         надо добавить первую фиктивную запись для анализа. */

      IF (StartLogIDSelect = 0) AND (NOT EXISTS

        (Select 1 

         FROM datagps 

         WHERE (Mobitel_ID = MobitelID) AND (LogID = StartLogIDSelect))) THEN

      

        INSERT INTO datagpslost_ontmp(LogID, SrvPacketID) VALUES (0, 0);  

      END IF;       

    ELSE

      /* Пришли новые данные. Надо добавить для анализа искусственную запись - 

         нижнюю границу */

      

      /* Находим стартовый LogID для пересчета. */

      SELECT COALESCE(MAX(End_LogID), CurrentConfirmedID) INTO StartLogIDSelect

      FROM datagpslost_on

      WHERE Mobitel_ID = MobitelID;   

         

      /* Находим последний LogID для пересчета. */  

      SELECT MAX(LogID)  INTO FinishLogIDSelect

      FROM datagpsbuffer_on

      WHERE Mobitel_ID = MobitelID;

      

      /* Выбираем из datagps соответствующее значение SrvPacketID  для первой

         искусственной записи */

      SELECT COALESCE(SrvPacketID, 0) INTO tmpSrvPacketID

      FROM datagps

      WHERE (Mobitel_ID = MobitelID) AND (LogID = StartLogIDSelect);

      

      /* Помещаем в таблицу datagpslost_ontmp для анализа пропусков 

         дополнительную первую запись */

      INSERT INTO datagpslost_ontmp(LogID, SrvPacketID)

      VALUES (StartLogIDSelect, tmpSrvPacketID);

    END IF; -- IF MinLogID_n < MaxEndLogID   

    

    /********** END PREPARE **********/

    

    /**********  INSERT ROWS **********/

    

    /* Заполнение временной таблицы последними данными телетрека

       для анализа разрывов */

    INSERT INTO datagpslost_ontmp  (LogID, SrvPacketID)

      SELECT LogID, SrvPacketID

      FROM datagps

      WHERE (Mobitel_ID = MobitelID) AND (LogID >= StartLogIDSelect)

        AND (LogID <= FinishLogIDSelect)

      ORDER BY LogID;

            

    /* Формирование команды-вставки в таблицу пропущенных диапазонов */

    SET DataExists = 1;

    OPEN CursorNewLogID;

    FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

    WHILE DataExists = 1 DO

      IF FirstLogID = -9999999 THEN

        /* Первое значение в списке */

        SET FirstLogID = NewLogID;

        SET FirstSrvPacketID = NewSrvPacketID;

      ELSE

        /* Основная ветка расчета*/

        SET SecondLogID = NewLogID;

        SET SecondSrvPacketID = NewSrvPacketID;

        

        IF (SecondLogID - FirstLogID) > 1 THEN

          /* Найден разрыв */

          CALL OnInsertDatagpsLost(MobitelID, FirstLogID, SecondLogID,  

            FirstSrvPacketID, SecondSrvPacketID);

          

          IF NeedOptimize = 0 THEN

            SET NeedOptimize = 1; 

          END IF;

        END IF;

        /* Переместим текущее второе значение на первую позицию

           для участия в следующей итерации в качестве начального значения */

        SET  FirstLogID = SecondLogID;

        SET FirstSrvPacketID = SecondSrvPacketID;

      END IF;  

      FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

    END WHILE;

    CLOSE CursorNewLogID;

 

    /********** END INSERT ROWS **********/

               

    /********** UPDATE MOBITELS **********/

    

    /* Если пропущенных записей у данного телетрека нет - 

       тогда ConfirmedID будет равен максимальному LogID 

       данного телетрека. Иначе - минимальный Begin_LogID

       из таблицы пропусков */

    SELECT MIN(Begin_LogID) INTO NewConfirmedID

    FROM datagpslost_on

    WHERE Mobitel_ID = MobitelID;

       

    IF NewConfirmedID IS NULL THEN

      SELECT MAX(LogID) INTO NewConfirmedID

      FROM datagps

      WHERE Mobitel_ID = MobitelID;

    END IF;  

    

    /* Обновим ConfirmedID если надо */

    UPDATE mobitels

    SET ConfirmedID = NewConfirmedID

    WHERE (Mobitel_ID = MobitelID) AND (ConfirmedID < NewConfirmedID);

    

    /********** END UPDATE MOBITELS **********/

        

    TRUNCATE TABLE datagpslost_ontmp;

  END IF; -- IF MaxEndLogID IS NOT NULL  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnLostDataGPS2` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnLostDataGPS2`(IN MobitelID INTEGER(11), OUT NeedOptimize TINYINT)
    SQL SECURITY INVOKER
    COMMENT 'Модификация диапазонов пропусков. Анализ дублирующих данных заданного телетрека.'
BEGIN

    /********** DECLARE **********/

    

  DECLARE NewConfirmedID INT DEFAULT 0; /* Новое значение ConfirmedID */

  DECLARE BeginLogID INT; /* Begin_LogID в курсоре CursorLostRanges */

  DECLARE EndLogID INT; /* End_LogID в курсоре CursorLostRanges */

  DECLARE BeginSrvPacketID BIGINT; /* Begin_SrvPacketID в курсоре CursorLostRanges */

  DECLARE EndSrvPacketID BIGINT; /* End_SrvPacketID в курсоре CursorLostRanges */

  DECLARE RowCountForAnalyze INT DEFAULT 0; /* Количество записей для анализа */

  DECLARE DataExists TINYINT DEFAULT 1; /* Флаг наличия данных после фетча */

    

  /* Курсор для прохода по диапазонам пропусков телетрека */

  DECLARE CursorLostRanges CURSOR FOR

    SELECT Begin_LogID, End_LogID, Begin_SrvPacketID, End_SrvPacketID 

    FROM datagpslost_on

    WHERE Mobitel_ID = MobitelID;    



  /* Курсор для прохода по новым LogID */

  DECLARE CursorNewLogID CURSOR FOR

    SELECT LogID, SrvPacketID 

    FROM datagpslost_ontmp

    ORDER BY SrvPacketID;    

    

  /* Обработчик отсутствующей записи при FETCH следующей записи */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

  

  /********** END DECLARE **********/



  SET NeedOptimize = 0;

  

  TRUNCATE TABLE datagpslost_ontmp;

  

  /* Перебираем диапазоны и сокращаем разрывы если это возможно */

  SET DataExists = 1;

  OPEN CursorLostRanges;

  FETCH CursorLostRanges INTO BeginLogID, EndLogID, BeginSrvPacketID, EndSrvPacketID;

  WHILE DataExists = 1 DO

    /* Заполнение временной таблицы дублирующими данными телетрека

       для анализа разрывов */

    INSERT INTO datagpslost_ontmp  (LogID, SrvPacketID)

      SELECT DISTINCT LogID, SrvPacketID

      FROM datagpsbuffer_ontmp

      WHERE (Mobitel_ID = MobitelID) AND (SrvPacketID > BeginSrvPacketID)

        AND (SrvPacketID < EndSrvPacketID)

      ORDER BY SrvPacketID;

      

    /* Если есть данные модифицируем диапазон */  

    SELECT COUNT(1) INTO RowCountForAnalyze

    FROM datagpslost_ontmp;  

    

    IF RowCountForAnalyze > 0 THEN  

      /* Добавляем первую и последнюю записи для анализа */  

      INSERT INTO datagpslost_ontmp (LogID, SrvPacketID)

      VALUES (BeginLogID, BeginSrvPacketID), (EndLogID, EndSrvPacketID);

      

      /* Удаляем старый диапазон */

      DELETE FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID = BeginLogID);

       

      /**********  INSERT ROWS **********/             

      BEGIN

        DECLARE NewLogID INT; /* Значение LogID в курсоре CursorNewLogID */

        DECLARE NewSrvPacketID BIGINT; /* Значение SrvPacketID в курсоре CursorNewLogID */

        DECLARE FirstLogID INT DEFAULT -9999999; /* Первое значение LogID для анализа */

        DECLARE SecondLogID INT; /* Второе значение LogID для анализа */

        DECLARE FirstSrvPacketID BIGINT; /* Первое значение SrvPacketID */

        DECLARE SecondSrvPacketID BIGINT; /* Второе значение SrvPacketID */

        

        /* Обработчик отсутствующей записи при FETCH следующей записи */

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

        

        /* Формирование команды-вставки в таблицу пропущенных диапазонов */

        SET DataExists = 1;

        OPEN CursorNewLogID;

        FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

        WHILE DataExists = 1 DO

          IF FirstLogID = -9999999 THEN

            /* Первое значение в списке */

            SET FirstLogID = NewLogID;

            SET FirstSrvPacketID = NewSrvPacketID;

          ELSE

            /* Основная ветка расчета*/

            SET SecondLogID = NewLogID;

            SET SecondSrvPacketID = NewSrvPacketID;

            

            IF (SecondSrvPacketID - FirstSrvPacketID) > 1 THEN

              /* Найден разрыв */

              CALL OnInsertDatagpsLost(MobitelID, FirstLogID, SecondLogID,  

                FirstSrvPacketID, SecondSrvPacketID);



              IF NeedOptimize = 0 THEN

                SET NeedOptimize = 1; 

              END IF;

            END IF;

            /* Переместим текущее второе значение на первую позицию

               для участия в следующей итерации в качестве начального значения */

            SET FirstLogID = SecondLogID;

            SET FirstSrvPacketID = SecondSrvPacketID;

          END IF;  

          FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

        END WHILE;

        CLOSE CursorNewLogID;

      END;

      /********** END INSERT ROWS **********/  



      TRUNCATE TABLE datagpslost_ontmp;

    END IF;

      

    FETCH CursorLostRanges INTO BeginLogID, EndLogID, BeginSrvPacketID, EndSrvPacketID;

  END WHILE;

  CLOSE CursorLostRanges;

  

  /********** UPDATE MOBITELS **********/

    

  /* Если пропущенных записей у данного телетрека нет - 

     тогда ConfirmedID будет равен максимальному LogID 

     данного телетрека. Иначе - минимальный Begin_LogID

     из таблицы пропусков */

  SELECT MIN(Begin_LogID) INTO NewConfirmedID

  FROM datagpslost_on

  WHERE Mobitel_ID = MobitelID;

       

  IF NewConfirmedID IS NULL THEN

    SELECT MAX(LogID) INTO NewConfirmedID

    FROM datagps

    WHERE Mobitel_ID = MobitelID;

  END IF;  

    

  /* Обновим ConfirmedID если надо */

  UPDATE mobitels

  SET ConfirmedID = NewConfirmedID

  WHERE (Mobitel_ID = MobitelID) AND (ConfirmedID < NewConfirmedID);

    

  /********** END UPDATE MOBITELS **********/

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnLostDataGPS264` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnLostDataGPS264`(IN MobitelID integer(11), OUT NeedOptimize tinyint)
    SQL SECURITY INVOKER
    COMMENT 'РњРѕРґРёС„РёРєР°С†РёСЏ РґРёР°РїР°Р·РѕРЅРѕРІ РїСЂРѕРїСѓСЃРєРѕРІ. РђРЅР°Р»РёР· РґСѓР±Р»РёСЂСѓСЋС‰РёС… РґР°РЅРЅС‹С… Р·Р°РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР°.'
BEGIN

  /********** DECLARE **********/



  DECLARE NewConfirmedID int DEFAULT 0; /* РќРѕРІРѕРµ Р·РЅР°С‡РµРЅРёРµ ConfirmedID */

  DECLARE BeginLogID int; /* Begin_LogID РІ РєСѓСЂСЃРѕСЂРµ CursorLostRanges */

  DECLARE EndLogID int; /* End_LogID РІ РєСѓСЂСЃРѕСЂРµ CursorLostRanges */

  DECLARE BeginSrvPacketID bigint; /* Begin_SrvPacketID РІ РєСѓСЂСЃРѕСЂРµ CursorLostRanges */

  DECLARE EndSrvPacketID bigint; /* End_SrvPacketID РІ РєСѓСЂСЃРѕСЂРµ CursorLostRanges */

  DECLARE RowCountForAnalyze int DEFAULT 0; /* РљРѕР»РёС‡РµСЃС‚РІРѕ Р·Р°РїРёСЃРµР№ РґР»СЏ Р°РЅР°Р»РёР·Р° */

  DECLARE DataExists tinyint DEFAULT 1; /* Р¤Р»Р°Рі РЅР°Р»РёС‡РёСЏ РґР°РЅРЅС‹С… РїРѕСЃР»Рµ С„РµС‚С‡Р° */



  /* РљСѓСЂСЃРѕСЂ РґР»СЏ РїСЂРѕС…РѕРґР° РїРѕ РґРёР°РїР°Р·РѕРЅР°Рј РїСЂРѕРїСѓСЃРєРѕРІ С‚РµР»РµС‚СЂРµРєР° */

  DECLARE CursorLostRanges CURSOR FOR

  SELECT

    Begin_LogID,

    End_LogID,

    Begin_SrvPacketID,

    End_SrvPacketID

  FROM datagpslost_on

  WHERE Mobitel_ID = MobitelID;



  /* РљСѓСЂСЃРѕСЂ РґР»СЏ РїСЂРѕС…РѕРґР° РїРѕ РЅРѕРІС‹Рј LogID */

  DECLARE CursorNewLogID CURSOR FOR

  SELECT

    LogID,

    SrvPacketID

  FROM datagpslost_ontmp

  ORDER BY SrvPacketID;



  /* РћР±СЂР°Р±РѕС‚С‡РёРє РѕС‚СЃСѓС‚СЃС‚РІСѓСЋС‰РµР№ Р·Р°РїРёСЃРё РїСЂРё FETCH СЃР»РµРґСѓСЋС‰РµР№ Р·Р°РїРёСЃРё */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  /********** END DECLARE **********/



  SET NeedOptimize = 0;



  TRUNCATE TABLE datagpslost_ontmp;



  /* РџРµСЂРµР±РёСЂР°РµРј РґРёР°РїР°Р·РѕРЅС‹ Рё СЃРѕРєСЂР°С‰Р°РµРј СЂР°Р·СЂС‹РІС‹ РµСЃР»Рё СЌС‚Рѕ РІРѕР·РјРѕР¶РЅРѕ */

  SET DataExists = 1;

  OPEN CursorLostRanges;

  FETCH CursorLostRanges INTO BeginLogID, EndLogID, BeginSrvPacketID, EndSrvPacketID;

  WHILE DataExists = 1 DO

    /* Р—Р°РїРѕР»РЅРµРЅРёРµ РІСЂРµРјРµРЅРЅРѕР№ С‚Р°Р±Р»РёС†С‹ РґСѓР±Р»РёСЂСѓСЋС‰РёРјРё РґР°РЅРЅС‹РјРё С‚РµР»РµС‚СЂРµРєР°

       РґР»СЏ Р°РЅР°Р»РёР·Р° СЂР°Р·СЂС‹РІРѕРІ */

    INSERT INTO datagpslost_ontmp (LogID, SrvPacketID)

      SELECT DISTINCT

        LogID,

        SrvPacketID

      FROM datagpsbuffer_ontmp64

      WHERE (Mobitel_ID = MobitelID) AND (SrvPacketID > BeginSrvPacketID)

      AND (SrvPacketID < EndSrvPacketID)

      ORDER BY SrvPacketID;



    /* Р•СЃР»Рё РµСЃС‚СЊ РґР°РЅРЅС‹Рµ РјРѕРґРёС„РёС†РёСЂСѓРµРј РґРёР°РїР°Р·РѕРЅ */

    SELECT

      COUNT(1) INTO RowCountForAnalyze

    FROM datagpslost_ontmp;



    IF RowCountForAnalyze > 0 THEN

      /* Р”РѕР±Р°РІР»СЏРµРј РїРµСЂРІСѓСЋ Рё РїРѕСЃР»РµРґРЅСЋСЋ Р·Р°РїРёСЃРё РґР»СЏ Р°РЅР°Р»РёР·Р° */

      INSERT INTO datagpslost_ontmp (LogID, SrvPacketID)

        VALUES (BeginLogID, BeginSrvPacketID), (EndLogID, EndSrvPacketID);



      /* РЈРґР°Р»СЏРµРј СЃС‚Р°СЂС‹Р№ РґРёР°РїР°Р·РѕРЅ */

      DELETE

        FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID = BeginLogID);



      /**********  INSERT ROWS **********/

      BEGIN

        DECLARE NewLogID int; /* Р—РЅР°С‡РµРЅРёРµ LogID РІ РєСѓСЂСЃРѕСЂРµ CursorNewLogID */

        DECLARE NewSrvPacketID bigint; /* Р—РЅР°С‡РµРЅРёРµ SrvPacketID РІ РєСѓСЂСЃРѕСЂРµ CursorNewLogID */

        DECLARE FirstLogID int DEFAULT -9999999; /* РџРµСЂРІРѕРµ Р·РЅР°С‡РµРЅРёРµ LogID РґР»СЏ Р°РЅР°Р»РёР·Р° */

        DECLARE SecondLogID int; /* Р’С‚РѕСЂРѕРµ Р·РЅР°С‡РµРЅРёРµ LogID РґР»СЏ Р°РЅР°Р»РёР·Р° */

        DECLARE FirstSrvPacketID bigint; /* РџРµСЂРІРѕРµ Р·РЅР°С‡РµРЅРёРµ SrvPacketID */

        DECLARE SecondSrvPacketID bigint; /* Р’С‚РѕСЂРѕРµ Р·РЅР°С‡РµРЅРёРµ SrvPacketID */



        /* РћР±СЂР°Р±РѕС‚С‡РёРє РѕС‚СЃСѓС‚СЃС‚РІСѓСЋС‰РµР№ Р·Р°РїРёСЃРё РїСЂРё FETCH СЃР»РµРґСѓСЋС‰РµР№ Р·Р°РїРёСЃРё */

        DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



        /* Р¤РѕСЂРјРёСЂРѕРІР°РЅРёРµ РєРѕРјР°РЅРґС‹-РІСЃС‚Р°РІРєРё РІ С‚Р°Р±Р»РёС†Сѓ РїСЂРѕРїСѓС‰РµРЅРЅС‹С… РґРёР°РїР°Р·РѕРЅРѕРІ */

        SET DataExists = 1;

        OPEN CursorNewLogID;

        FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

        WHILE DataExists = 1 DO

          IF FirstLogID = -9999999 THEN

            /* РџРµСЂРІРѕРµ Р·РЅР°С‡РµРЅРёРµ РІ СЃРїРёСЃРєРµ */

            SET FirstLogID = NewLogID;

            SET FirstSrvPacketID = NewSrvPacketID;

          ELSE

            /* РћСЃРЅРѕРІРЅР°СЏ РІРµС‚РєР° СЂР°СЃС‡РµС‚Р°*/

            SET SecondLogID = NewLogID;

            SET SecondSrvPacketID = NewSrvPacketID;



            IF (SecondLogID - FirstLogID) > 1 THEN

          /* РќР°Р№РґРµРЅ СЂР°Р·СЂС‹РІ */

          /* РР·-Р·Р° РІСЃСЏРєРёС… РєРѕРјР±РёРЅР°С†РёР№ СЃРѕСЃС‚РѕСЏРЅРёР№ LogId Рё SrvPacketId РёР·-Р·Р° РѕСЃРѕР±РµРЅРЅРѕСЃС‚РµР№ СЂР°Р±РѕС‚С‹ С‚РµР»РµРјР°С‚РёС‡РµСЃРєРѕРіРѕ СЃРµСЂРІРµСЂР°

          Рё РўР”Рњ РІРІРѕРґРёРј РґРѕРїРѕР»РЅРёС‚РµР»СЊРЅС‹Р№ Р°РЅР°Р»РёР· РІСЃС‚Р°РІР»СЏРµРјС‹С… Р·Р°РїРёСЃРµР№, РµСЃР»Рё Р·Р°РїРёСЃСЊ РЅРµ СЃРѕРѕС‚РІРµС‚СЃРІСѓРµС‚ СѓРєР°Р·Р°РЅС‹Рј РїСЂР°РІРёР»Р°Рј, РѕРЅР° 

          РѕС‚Р±СЂР°СЃС‹РІР°РµС‚СЃСЏ */

          IF (SecondSrvPacketID > FirstSrvPacketID) THEN

            IF EXISTS(SELECT NULL FROM datagps64 d WHERE (d.Mobitel_ID = MobitelID) AND ((d.LogID = FirstLogID) AND (d.SrvPacketID = FirstSrvPacketID))) THEN 

              IF EXISTS(SELECT NULL FROM datagps64 d WHERE (d.Mobitel_ID = MobitelID) AND ((d.LogID = SecondLogID) AND (d.SrvPacketID = SecondSrvPacketID))) THEN 



                CALL OnInsertDatagpsLost(MobitelID, FirstLogID, SecondLogID, FirstSrvPacketID, SecondSrvPacketID);

                IF NeedOptimize = 0 THEN SET NeedOptimize = 1; END IF;

                

              END IF;

            END IF;

          END IF;

        END IF;

            /* РџРµСЂРµРјРµСЃС‚РёРј С‚РµРєСѓС‰РµРµ РІС‚РѕСЂРѕРµ Р·РЅР°С‡РµРЅРёРµ РЅР° РїРµСЂРІСѓСЋ РїРѕР·РёС†РёСЋ

               РґР»СЏ СѓС‡Р°СЃС‚РёСЏ РІ СЃР»РµРґСѓСЋС‰РµР№ РёС‚РµСЂР°С†РёРё РІ РєР°С‡РµСЃС‚РІРµ РЅР°С‡Р°Р»СЊРЅРѕРіРѕ Р·РЅР°С‡РµРЅРёСЏ */

            SET FirstLogID = SecondLogID;

            SET FirstSrvPacketID = SecondSrvPacketID;

          END IF;

          FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

        END WHILE;

        CLOSE CursorNewLogID;

      END;

      /********** END INSERT ROWS **********/



      TRUNCATE TABLE datagpslost_ontmp;

    END IF;



    FETCH CursorLostRanges INTO BeginLogID, EndLogID, BeginSrvPacketID, EndSrvPacketID;

  END WHILE;

  CLOSE CursorLostRanges;



  /********** UPDATE MOBITELS **********/



  /* Р•СЃР»Рё РїСЂРѕРїСѓС‰РµРЅРЅС‹С… Р·Р°РїРёСЃРµР№ Сѓ РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР° РЅРµС‚ - 

     С‚РѕРіРґР° ConfirmedID Р±СѓРґРµС‚ СЂР°РІРµРЅ РјР°РєСЃРёРјР°Р»СЊРЅРѕРјСѓ LogID 

     РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР°. РРЅР°С‡Рµ - РјРёРЅРёРјР°Р»СЊРЅС‹Р№ Begin_LogID

     РёР· С‚Р°Р±Р»РёС†С‹ РїСЂРѕРїСѓСЃРєРѕРІ */

  SELECT

    MIN(Begin_LogID) INTO NewConfirmedID

  FROM datagpslost_on

  WHERE Mobitel_ID = MobitelID;



  IF NewConfirmedID IS NULL THEN

    SELECT

      MAX(LogID) INTO NewConfirmedID

    FROM datagps64

    WHERE Mobitel_ID = MobitelID;

  END IF;



  /* РћР±РЅРѕРІРёРј ConfirmedID РµСЃР»Рё РЅР°РґРѕ */

  UPDATE mobitels

  SET ConfirmedID = NewConfirmedID

  WHERE (Mobitel_ID = MobitelID) AND (ConfirmedID < NewConfirmedID);



/********** END UPDATE MOBITELS **********/

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnLostDataGPS64` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnLostDataGPS64`(IN MobitelID integer(11), OUT NeedOptimize tinyint)
    SQL SECURITY INVOKER
    COMMENT 'РџРѕРёСЃРє РїСЂРѕРїСѓСЃРєРѕРІ РІ online РґР°РЅРЅС‹С… Р·Р°РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР° Рё Р·Р°РїРѕР»РЅРµРЅРёРµ С‚Р°Р±Р»РёС†.'
BEGIN



  /********** DECLARE **********/



  DECLARE CurrentConfirmedID int DEFAULT 0; /* РўРµРєСѓС‰РµРµ Р·РЅР°С‡РµРЅРёРµ ConfirmedID */

  DECLARE NewConfirmedID int DEFAULT 0; /* РќРѕРІРѕРµ Р·РЅР°С‡РµРЅРёРµ ConfirmedID */

  DECLARE StartLogIDSelect int DEFAULT 0; /* min LogID РґР»СЏ РІС‹Р±РѕСЂРєРё */

  DECLARE FinishLogIDSelect int DEFAULT 0; /* max LogID РґР»СЏ РІС‹Р±РѕСЂРєРё */

  DECLARE MaxEndLogID int DEFAULT 0; /* max End_LogID РІ РїСЂРѕРїСѓС‰РµРЅРЅС‹С… РґРёР°РїР°Р·РѕРЅР°С… РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР° */



  DECLARE MinLogID_n int DEFAULT 0; /* РњРёРЅРёРјР°Р»СЊРЅС‹Р№ LogID РїРѕСЃР»Рµ ConfirmedID РІ РјРЅРѕР¶РµСЃС‚РІРµ РЅРѕРІС‹С… РґР°РЅРЅС‹С… */

  DECLARE MaxLogID_n int DEFAULT 0; /* РњР°РєСЃРёРјР°Р»СЊРЅС‹Р№ LogID РІ РјРЅРѕР¶РµСЃС‚РІРµ РЅРѕРІС‹С… РґР°РЅРЅС‹С… */



  DECLARE DataExists tinyint DEFAULT 1; /* Р¤Р»Р°Рі РЅР°Р»РёС‡РёСЏ РґР°РЅРЅС‹С… РїРѕСЃР»Рµ С„РµС‚С‡Р° */



  DECLARE NewLogID int; /* Р—РЅР°С‡РµРЅРёРµ LogID РІ РєСѓСЂСЃРѕСЂРµ CursorNewLogID */

  DECLARE NewSrvPacketID bigint; /* Р—РЅР°С‡РµРЅРёРµ SrvPacketID РІ РєСѓСЂСЃРѕСЂРµ CursorNewLogID */

  DECLARE FirstLogID int DEFAULT -9999999; /* РџРµСЂРІРѕРµ Р·РЅР°С‡РµРЅРёРµ LogID РґР»СЏ Р°РЅР°Р»РёР·Р° */

  DECLARE SecondLogID int; /* Р’С‚РѕСЂРѕРµ Р·РЅР°С‡РµРЅРёРµ LogID РґР»СЏ Р°РЅР°Р»РёР·Р° */

  DECLARE FirstSrvPacketID bigint; /* РџРµСЂРІРѕРµ Р·РЅР°С‡РµРЅРёРµ SrvPacketID */

  DECLARE SecondSrvPacketID bigint; /* Р’С‚РѕСЂРѕРµ Р·РЅР°С‡РµРЅРёРµ SrvPacketID */



  DECLARE tmpSrvPacketID bigint DEFAULT 0; /* Р—РЅР°С‡РµРЅРёРµ SrvPacketID РґР»СЏ РїРµСЂРІРѕР№ РёСЃРєСѓСЃСЃС‚РІРµРЅРЅРѕР№ Р·Р°РїРёСЃРё РІ Р°РЅР°Р»РёР·Рµ РЅРѕРІС‹С… РґР°РЅРЅС‹С…, РЅРµ РїРµСЂРµСЃРµРєР°СЋС‰РёС…СЃСЏ СЃ СЃСѓС‰РµСЃС‚РІСѓСЋС‰РёРјРё */



  /* РљСѓСЂСЃРѕСЂ РґР»СЏ РїСЂРѕС…РѕРґР° РїРѕ РЅРѕРІС‹Рј LogID */

  DECLARE CursorNewLogID CURSOR FOR

  SELECT

    LogID,

    SrvPacketID

  FROM datagpslost_ontmp;



  /* РћР±СЂР°Р±РѕС‚С‡РёРє РѕС‚СЃСѓС‚СЃС‚РІСѓСЋС‰РµР№ Р·Р°РїРёСЃРё РїСЂРё FETCH СЃР»РµРґСѓСЋС‰РµР№ Р·Р°РїРёСЃРё */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  /********** END DECLARE **********/



  SET NeedOptimize = 0;



  SELECT

    COALESCE(ConfirmedID, 0) INTO CurrentConfirmedID

  FROM mobitels

  WHERE Mobitel_ID = MobitelID;



  /* РњРёРЅРёРјР°Р»СЊРЅС‹Р№ LogID РІ РЅРѕРІРѕРј РЅР°Р±РѕСЂРµ РґР°РЅРЅС‹С… c РєРѕС‚РѕСЂС‹Рј Р±СѓРґРµРј СЂР°Р±РѕС‚Р°С‚СЊ */

  SELECT

    MIN(LogID) INTO MinLogID_n

  FROM datagpsbuffer_on64

  WHERE (Mobitel_ID = MobitelID) AND (LogID > CurrentConfirmedID);



  /* РњР°РєСЃРёРјР°Р»СЊРЅС‹Р№ LogID РІ РЅРѕРІРѕРј РЅР°Р±РѕСЂРµ РґР°РЅРЅС‹С… c РєРѕС‚РѕСЂС‹Рј Р±СѓРґРµРј СЂР°Р±РѕС‚Р°С‚СЊ */

  SELECT

    MAX(LogID) INTO MaxLogID_n

  FROM datagpsbuffer_on64

  WHERE Mobitel_ID = MobitelID;



  /* РњР°РєСЃРёРјР°Р»СЊРЅС‹Р№ End_LogID РІ РїСЂРѕРїСѓС‰РµРЅРЅС‹С… РґРёР°РїР°Р·РѕРЅР°С… РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР° */

  SELECT

    COALESCE(MAX(End_LogID), 0) INTO MaxEndLogID

  FROM datagpslost_on

  WHERE Mobitel_ID = MobitelID;



  /* РџСЂРѕРІРµСЂРёРј РµСЃС‚СЊ Р»Рё РґР°РЅРЅС‹Рµ РґР»СЏ Р°РЅР°Р»РёР·Р° */

  IF MinLogID_n IS NOT NULL THEN



    /********** PREPARE **********/



    TRUNCATE TABLE datagpslost_ontmp;



    IF MinLogID_n < MaxEndLogID THEN

      /* Р•СЃР»Рё  MinLogID_n < MaxEndLogID Р·РЅР°С‡РёС‚ РїСЂРёС€Р»Рё РґР°РЅРЅС‹Рµ, 

       РєРѕС‚РѕСЂС‹Рµ РІРѕР·РјРѕР¶РЅРѕ Р·Р°РєСЂРѕСЋС‚ С‡Р°СЃС‚СЊ РїСЂРѕРїСѓСЃРєРѕРІ. */



      /* РќР°С…РѕРґРёРј СЃС‚Р°СЂС‚РѕРІС‹Р№ LogID РґР»СЏ РїРµСЂРµСЃС‡РµС‚Р°. */

      SELECT

        COALESCE(Begin_LogID, MinLogID_n) INTO StartLogIDSelect

      FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (MinLogID_n > Begin_LogID)

      AND (MinLogID_n < End_LogID);



      /* РќР°С…РѕРґРёРј РїРѕСЃР»РµРґРЅРёР№ LogID РґР»СЏ РїРµСЂРµСЃС‡РµС‚Р°. */

      SELECT

        COALESCE(End_LogID, MaxLogID_n) INTO FinishLogIDSelect

      FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (MaxLogID_n > Begin_LogID)

      AND (MaxLogID_n < End_LogID);



      /* РЈРґР°Р»РёРј СЃС‚Р°СЂС‹Рµ Р·Р°РїРёСЃРё РёР· СЂРµРµСЃС‚СЂР° РїСЂРѕРїСѓС‰РµРЅРЅС‹С… Р·Р°РїРёСЃРµР№ 

         (СЃС‚Р°Р»Рё РЅРµР°РєС‚СѓР°Р»СЊРЅС‹РјРё, РЅР°РґРѕ РїРµСЂРµСЃС‡РёС‚Р°С‚СЊ) */

      DELETE

        FROM datagpslost_on

      WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID >= StartLogIDSelect)

        AND (Begin_LogID <= FinishLogIDSelect);



      /* Р•СЃР»Рё РїРµСЂРІР°СЏ Р·Р°РїРёСЃСЊ LogID = 0 Рё СЌС‚РѕР№ Р·Р°РїРёСЃРё РЅРµС‚ РІ DataGps, С‚РѕРіРґР°

         РЅР°РґРѕ РґРѕР±Р°РІРёС‚СЊ РїРµСЂРІСѓСЋ С„РёРєС‚РёРІРЅСѓСЋ Р·Р°РїРёСЃСЊ РґР»СЏ Р°РЅР°Р»РёР·Р°. */

      IF (StartLogIDSelect = 0) AND (NOT EXISTS (SELECT

            1

          FROM datagps64

          WHERE (Mobitel_ID = MobitelID) AND (LogID = StartLogIDSelect))) THEN



        INSERT INTO datagpslost_ontmp (LogID, SrvPacketID)

          VALUES (0, 0);

      END IF;

    ELSE

      /* РџСЂРёС€Р»Рё РЅРѕРІС‹Рµ РґР°РЅРЅС‹Рµ. РќР°РґРѕ РґРѕР±Р°РІРёС‚СЊ РґР»СЏ Р°РЅР°Р»РёР·Р° РёСЃРєСѓСЃСЃС‚РІРµРЅРЅСѓСЋ Р·Р°РїРёСЃСЊ - 

         РЅРёР¶РЅСЋСЋ РіСЂР°РЅРёС†Сѓ */



      /* РќР°С…РѕРґРёРј СЃС‚Р°СЂС‚РѕРІС‹Р№ LogID РґР»СЏ РїРµСЂРµСЃС‡РµС‚Р°. */

      SELECT

        COALESCE(MAX(End_LogID), CurrentConfirmedID) INTO StartLogIDSelect

      FROM datagpslost_on

      WHERE Mobitel_ID = MobitelID;



      /* РќР°С…РѕРґРёРј РїРѕСЃР»РµРґРЅРёР№ LogID РґР»СЏ РїРµСЂРµСЃС‡РµС‚Р°. */

      SELECT

        MAX(LogID) INTO FinishLogIDSelect

      FROM datagpsbuffer_on64

      WHERE Mobitel_ID = MobitelID;



      /* Р’С‹Р±РёСЂР°РµРј РёР· datagps СЃРѕРѕС‚РІРµС‚СЃС‚РІСѓСЋС‰РµРµ Р·РЅР°С‡РµРЅРёРµ SrvPacketID  РґР»СЏ РїРµСЂРІРѕР№

         РёСЃРєСѓСЃСЃС‚РІРµРЅРЅРѕР№ Р·Р°РїРёСЃРё */

      SELECT

        COALESCE(SrvPacketID, 0) INTO tmpSrvPacketID

      FROM datagps64

      WHERE (Mobitel_ID = MobitelID) AND (LogID = StartLogIDSelect);



      /* РџРѕРјРµС‰Р°РµРј РІ С‚Р°Р±Р»РёС†Сѓ datagpslost_ontmp64 РґР»СЏ Р°РЅР°Р»РёР·Р° РїСЂРѕРїСѓСЃРєРѕРІ 

         РґРѕРїРѕР»РЅРёС‚РµР»СЊРЅСѓСЋ РїРµСЂРІСѓСЋ Р·Р°РїРёСЃСЊ */

      INSERT INTO datagpslost_ontmp (LogID, SrvPacketID)

        VALUES (StartLogIDSelect, tmpSrvPacketID);

    END IF; -- IF MinLogID_n < MaxEndLogID   



    /********** END PREPARE **********/



    /**********  INSERT ROWS **********/



    /* Р—Р°РїРѕР»РЅРµРЅРёРµ РІСЂРµРјРµРЅРЅРѕР№ С‚Р°Р±Р»РёС†С‹ РїРѕСЃР»РµРґРЅРёРјРё РґР°РЅРЅС‹РјРё С‚РµР»РµС‚СЂРµРєР°

       РґР»СЏ Р°РЅР°Р»РёР·Р° СЂР°Р·СЂС‹РІРѕРІ */

    INSERT INTO datagpslost_ontmp (LogID, SrvPacketID)

      SELECT

        LogID,

        SrvPacketID

      FROM datagps64

      WHERE (Mobitel_ID = MobitelID) AND (LogID >= StartLogIDSelect)

      AND (LogID <= FinishLogIDSelect)

      ORDER BY LogID;



    /* Р¤РѕСЂРјРёСЂРѕРІР°РЅРёРµ РєРѕРјР°РЅРґС‹-РІСЃС‚Р°РІРєРё РІ С‚Р°Р±Р»РёС†Сѓ РїСЂРѕРїСѓС‰РµРЅРЅС‹С… РґРёР°РїР°Р·РѕРЅРѕРІ */

    SET DataExists = 1;

    OPEN CursorNewLogID;

    FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

    WHILE DataExists = 1 DO

      IF FirstLogID = -9999999 THEN

        /* РџРµСЂРІРѕРµ Р·РЅР°С‡РµРЅРёРµ РІ СЃРїРёСЃРєРµ */

        SET FirstLogID = NewLogID;

        SET FirstSrvPacketID = NewSrvPacketID;

      ELSE

        /* РћСЃРЅРѕРІРЅР°СЏ РІРµС‚РєР° СЂР°СЃС‡РµС‚Р°*/

        SET SecondLogID = NewLogID;

        SET SecondSrvPacketID = NewSrvPacketID;



        IF (SecondLogID - FirstLogID) > 1 THEN

          /* РќР°Р№РґРµРЅ СЂР°Р·СЂС‹РІ */

          /* РР·-Р·Р° РІСЃСЏРєРёС… РєРѕРјР±РёРЅР°С†РёР№ СЃРѕСЃС‚РѕСЏРЅРёР№ LogId Рё SrvPacketId РёР·-Р·Р° РѕСЃРѕР±РµРЅРЅРѕСЃС‚РµР№ СЂР°Р±РѕС‚С‹ С‚РµР»РµРјР°С‚РёС‡РµСЃРєРѕРіРѕ СЃРµСЂРІРµСЂР°

          Рё РўР”Рњ РІРІРѕРґРёРј РґРѕРїРѕР»РЅРёС‚РµР»СЊРЅС‹Р№ Р°РЅР°Р»РёР· РІСЃС‚Р°РІР»СЏРµРјС‹С… Р·Р°РїРёСЃРµР№, РµСЃР»Рё Р·Р°РїРёСЃСЊ РЅРµ СЃРѕРѕС‚РІРµС‚СЃРІСѓРµС‚ СѓРєР°Р·Р°РЅС‹Рј РїСЂР°РІРёР»Р°Рј, РѕРЅР° 

          РѕС‚Р±СЂР°СЃС‹РІР°РµС‚СЃСЏ */

          IF (SecondSrvPacketID > FirstSrvPacketID) THEN

            IF EXISTS(SELECT NULL FROM datagps64 d WHERE (d.Mobitel_ID = MobitelID) AND ((d.LogID = FirstLogID) AND (d.SrvPacketID = FirstSrvPacketID))) THEN 

              IF EXISTS(SELECT NULL FROM datagps64 d WHERE (d.Mobitel_ID = MobitelID) AND ((d.LogID = SecondLogID) AND (d.SrvPacketID = SecondSrvPacketID))) THEN 



                CALL OnInsertDatagpsLost(MobitelID, FirstLogID, SecondLogID, FirstSrvPacketID, SecondSrvPacketID);

                IF NeedOptimize = 0 THEN SET NeedOptimize = 1; END IF;

                

              END IF;

            END IF;

          END IF;

        END IF;

        /* РџРµСЂРµРјРµСЃС‚РёРј С‚РµРєСѓС‰РµРµ РІС‚РѕСЂРѕРµ Р·РЅР°С‡РµРЅРёРµ РЅР° РїРµСЂРІСѓСЋ РїРѕР·РёС†РёСЋ

           РґР»СЏ СѓС‡Р°СЃС‚РёСЏ РІ СЃР»РµРґСѓСЋС‰РµР№ РёС‚РµСЂР°С†РёРё РІ РєР°С‡РµСЃС‚РІРµ РЅР°С‡Р°Р»СЊРЅРѕРіРѕ Р·РЅР°С‡РµРЅРёСЏ */

        SET FirstLogID = SecondLogID;

        SET FirstSrvPacketID = SecondSrvPacketID;

      END IF;

      FETCH CursorNewLogID INTO NewLogID, NewSrvPacketID;

    END WHILE;

    CLOSE CursorNewLogID;



    /********** END INSERT ROWS **********/



    /********** UPDATE MOBITELS **********/



    /* Р•СЃР»Рё РїСЂРѕРїСѓС‰РµРЅРЅС‹С… Р·Р°РїРёСЃРµР№ Сѓ РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР° РЅРµС‚ - 

       С‚РѕРіРґР° ConfirmedID Р±СѓРґРµС‚ СЂР°РІРµРЅ РјР°РєСЃРёРјР°Р»СЊРЅРѕРјСѓ LogID 

       РґР°РЅРЅРѕРіРѕ С‚РµР»РµС‚СЂРµРєР°. РРЅР°С‡Рµ - РјРёРЅРёРјР°Р»СЊРЅС‹Р№ Begin_LogID

       РёР· С‚Р°Р±Р»РёС†С‹ РїСЂРѕРїСѓСЃРєРѕРІ */

    SELECT

      MIN(Begin_LogID) INTO NewConfirmedID

    FROM datagpslost_on

    WHERE Mobitel_ID = MobitelID;



    IF NewConfirmedID IS NULL THEN

      SELECT

        MAX(LogID) INTO NewConfirmedID

      FROM datagps64

      WHERE Mobitel_ID = MobitelID;

    END IF;



    /* РћР±РЅРѕРІРёРј ConfirmedID РµСЃР»Рё РЅР°РґРѕ */

    UPDATE mobitels

    SET ConfirmedID = NewConfirmedID

    WHERE (Mobitel_ID = MobitelID) AND (ConfirmedID < NewConfirmedID);



    /********** END UPDATE MOBITELS **********/



    TRUNCATE TABLE datagpslost_ontmp;

  END IF; -- IF MaxEndLogID IS NOT NULL  

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnTransferBuffer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnTransferBuffer`()
    SQL SECURITY INVOKER
    COMMENT 'Переносит GPS данные из буферной таблицы в datagps'
BEGIN

  DECLARE MobitelIDInCursor INT; /* Текущее значение MobitelID */

  DECLARE DataExists TINYINT DEFAULT 1; /* Флаг наличия данных после фетча */

  DECLARE NeedOptimize TINYINT DEFAULT 0; /* Нужна оптимизация или нет */

  DECLARE NeedOptimizeParam TINYINT;

  

  DECLARE TmpMaxUnixTime INT; /* Временная переменная для хранения Max UnixTime из таблицы online */



  /* Курсор по телетрекам в множестве новых данных */

  DECLARE CursorMobitelsInsertSet CURSOR FOR

    SELECT DISTINCT(Mobitel_ID) FROM datagpsbuffer_on;

  /* Курсор по телетрекам в множестве повторных данных */

  DECLARE CursorMobitelsUpdateSet CURSOR FOR

    SELECT DISTINCT(Mobitel_ID) FROM datagpsbuffer_ontmp;  



  /* Обработчик отсутствующей записи при FETCH следующей записи */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;



  CALL OnDeleteDuplicates();

  CALL OnCorrectInfotrackLogId();

  

  /* Сохраняем записи, которые уже есть в БД, для второго этапа анализа пропусков 

     Используется процедурой OnLostDataGPS2. */

  INSERT INTO datagpsbuffer_ontmp (Mobitel_ID, LogID, SrvPacketID)

    SELECT b.Mobitel_ID, b.LogID, b.SrvPacketID

    FROM datagpsbuffer_on b LEFT JOIN datagps d ON

      (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID)

    WHERE d.LogID IS NOT NULL;

       

  /* -------------------- Заполнение таблицы Online ---------------------- */  

  /* Удаление из таблицы online дублирующих строк таблицы datagpsbuffer_on */

  /* по полям Mobitel_ID и LogID */

  DELETE o

  FROM online o, datagpsbuffer_on dgb

  WHERE (o.Mobitel_ID = dgb.Mobitel_ID) AND (o.LogID = dgb.LogID);

  

  SET DataExists = 1;

  OPEN CursorMobitelsInsertSet;

  FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* Нахождение Max значения UnixTime в таблице online для заданного

       телетрека. p.s. UnixTime в таблице online только валидный */

    SELECT COALESCE(MAX(UnixTime), 0) INTO TmpMaxUnixTime

    FROM online FORCE INDEX (IDX_Mobitelid)

    WHERE Mobitel_ID = MobitelIDInCursor;



    /* Вставка новых записей (максимум 100)каждого телетрека в online */

    INSERT INTO online (

      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,

      Direction, Speed, Valid, `Events`,

      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,

      Counter1, Counter2, Counter3, Counter4, whatIs)

    SELECT

      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,

      Direction, Speed, Valid, `Events`,

      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,

      Counter1, Counter2, Counter3, Counter4, whatIs

    FROM (

      SELECT *

      FROM datagpsbuffer_on

      WHERE (Mobitel_ID = MobitelIDInCursor)  AND 

        (UnixTime > TmpMaxUnixTime) AND (Valid = 1)

      GROUP BY Mobitel_ID, LogID

      ORDER BY UnixTime DESC

      LIMIT 100) AS T

    ORDER BY UnixTime ASC;



    /* Удаление строк из таблицы оперативных данных не удовлетворяющие

       условиям хранения - не являются последними 100 строками */

    DELETE FROM online

    WHERE (Mobitel_ID = MobitelIDInCursor) AND (UnixTime < 

      (SELECT COALESCE(MIN(UnixTime), 0) 

       FROM (

         SELECT UnixTime

         FROM online

         WHERE Mobitel_ID = MobitelIDInCursor 

         ORDER BY UnixTime DESC

         LIMIT 100) T1)); 

  

    FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorMobitelsInsertSet;

  /* ---------------- Online ---------------- */ 

  

  /* Зачем-то нужен апдейт ... */

  UPDATE datagps d, datagpsbuffer_on b SET

    d.Latitude = b.Latitude,

    d.Longitude = b.Longitude,

    d.Altitude = b.Altitude,

    d.UnixTime = b.UnixTime,

    d.Speed = b.Speed,

    d.Direction = b.Direction,

    d.Valid = b.Valid,

    d.InMobitelID = b.LogID,

    d.`Events` = b.`Events`,

    d.Sensor1 = b.Sensor1,

    d.Sensor2 = b.Sensor2,

    d.Sensor3 = b.Sensor3,

    d.Sensor4 = b.Sensor4,

    d.Sensor5 = b.Sensor5,

    d.Sensor6 = b.Sensor6,

    d.Sensor7 = b.Sensor7,

    d.Sensor8 = b.Sensor8,

    d.whatIs = b.whatIs,

    d.Counter1 = b.Counter1,

    d.Counter2 = b.Counter2,

    d.Counter3 = b.Counter3,

    d.Counter4 = b.Counter4,

    d.SrvPacketID = b.SrvPacketID

  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID)

    AND (d.SrvPacketID < b.SrvPacketID);



  /* Удалим записи которые участвовали в апдейте */

  DELETE b

  FROM datagps d, datagpsbuffer_on b

  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);



  /* Вставка новых записей*/

  INSERT INTO datagps (

    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,

    Direction, Speed, Valid, `Events`, InMobitelID,

    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,

    Counter1, Counter2, Counter3, Counter4, whatIs, SrvPacketID)

  SELECT

    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,

    Direction, Speed, Valid, `Events`, LogID,

    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,

    Counter1, Counter2, Counter3, Counter4, whatIs, SrvPacketID

  FROM datagpsbuffer_on

  GROUP BY Mobitel_ID, LogID;



  /* ------------------- FETCH Mobitels (INSERT SET) ----------------------- */

  SET DataExists = 1;

  OPEN CursorMobitelsInsertSet;

  FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* ------ Обновление времени последней вставки записей ------- */

    UPDATE mobitels SET LastInsertTime = UNIX_TIMESTAMP(NOW())

    WHERE Mobitel_ID = MobitelIDInCursor;

       

    /* ------ Обновление списка пропущенных записей -------------- */

    CALL OnLostDataGPS(MobitelIDInCursor, NeedOptimizeParam);

    SET NeedOptimize = GREATEST(NeedOptimize, NeedOptimizeParam);

    FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorMobitelsInsertSet;



  /* Очистка буфера */

  TRUNCATE TABLE datagpsbuffer_on;

    

  /* ------------------- FETCH Mobitels (UPDATE SET) ---------------------- */

  SET DataExists = 1;

  OPEN CursorMobitelsUpdateSet;

  FETCH CursorMobitelsUpdateSet INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* ---- Обновление списка пропущенных записей по второму алгоритму --- */

    CALL OnLostDataGPS2(MobitelIDInCursor, NeedOptimizeParam);

    SET NeedOptimize = GREATEST(NeedOptimize, NeedOptimizeParam);

    FETCH CursorMobitelsUpdateSet INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorMobitelsUpdateSet;

  

  /* Очистка вспомогательного буфера */

  TRUNCATE TABLE datagpsbuffer_ontmp;



  /* Были изменения - сделаем оптимизацию */

  IF NeedOptimize = 1 THEN

    OPTIMIZE TABLE datagpslost_on;

  END IF;            

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `OnTransferBuffer64` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `OnTransferBuffer64`()
    SQL SECURITY INVOKER
BEGIN

  DECLARE MobitelIDInCursor int; /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ MobitelID */

  DECLARE DataExists tinyint DEFAULT 1; /* пїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ */

  DECLARE NeedOptimize tinyint DEFAULT 0; /* пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅ пїЅпїЅпїЅ */

  DECLARE NeedOptimizeParam tinyint;



  DECLARE TmpMaxUnixTime int; /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ Max UnixTime пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ online */



  /* пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ */

  DECLARE CursorMobitelsInsertSet CURSOR FOR

  SELECT DISTINCT

    (Mobitel_ID)

  FROM datagpsbuffer_on64;

  /* пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ */

  DECLARE CursorMobitelsUpdateSet CURSOR FOR

  SELECT DISTINCT

    (Mobitel_ID)

  FROM datagpsbuffer_ontmp64;



  /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅ FETCH пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ */

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

  /*DECLARE EXIT HANDLER FOR SQLEXCEPTION 

  BEGIN

    ROLLBACK;

    SELECT 'An error has occurred, operation rollbacked and the stored procedure was terminated';

  end;*/



  CALL delDupSrvPacket();

  CALL OnDeleteDuplicates64();

  /*CALL OnCorrectInfotrackLogId64();*/



  /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ, пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅ пїЅпїЅпїЅпїЅ пїЅ пїЅпїЅ, пїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ 

     пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ OnLostDataGPS264. */

  /*INSERT INTO datagpsbuffer_ontmp64 (Mobitel_ID, LogID, SrvPacketID)

    SELECT

      b.Mobitel_ID,

      b.LogID,

      b.SrvPacketID

    FROM datagpsbuffer_on64 b

      LEFT JOIN datagps64 d

        ON (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID)

    WHERE d.LogID IS NOT NULL GROUP BY d.SrvPacketID;*/

 DROP TEMPORARY TABLE IF EXISTS temp1;

  DROP TABLE IF EXISTS temp1;

  CREATE TEMPORARY TABLE temp1  SELECT

      b.Mobitel_ID,

      b.LogID,

      b.SrvPacketID

    FROM datagpsbuffer_on64 b

      LEFT JOIN datagps64 d

        ON (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID)

    WHERE d.LogID IS NOT NULL GROUP BY d.SrvPacketID;



INSERT INTO datagpsbuffer_ontmp64 (Mobitel_ID, LogID, SrvPacketID) SELECT * FROM temp1 GROUP BY SrvPacketID;



DROP TEMPORARY TABLE IF EXISTS temp1;


  /* -------------------- пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ Online ---------------------- */

  /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ online пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ datagpsbuffer_on64 */

  /* пїЅпїЅ пїЅпїЅпїЅпїЅпїЅ Mobitel_ID пїЅ LogID */

  DELETE o

    FROM online64 o, datagpsbuffer_on64 dgb

  WHERE (o.Mobitel_ID = dgb.Mobitel_ID) AND (o.LogID = dgb.LogID);



  SET DataExists = 1;

  OPEN CursorMobitelsInsertSet;

  FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ Max пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ UnixTime пїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ online пїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ

       пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ. p.s. UnixTime пїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ online пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ */

    SELECT

      COALESCE(MAX(UnixTime), 0) INTO TmpMaxUnixTime

    FROM online64 FORCE INDEX (IDX_Mobitelid64)

    WHERE Mobitel_ID = MobitelIDInCursor;



    /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ (пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ 100)пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅ online */

    INSERT INTO online64 (Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Acceleration,

    Direction, Speed, Valid, `Events`,

    Satellites, RssiGsm, SensorsSet, Sensors, Voltage, DGPS)

      SELECT

        Mobitel_ID,

        LogID,

        UnixTime,

        Latitude,

        Longitude,

        Acceleration,

        Direction,

        Speed,

        Valid,

        `Events`,

        Satellites,

        RssiGsm,

        SensorsSet,

        Sensors,

        Voltage,

        DGPS

      FROM (SELECT

          *

        FROM datagpsbuffer_on64

        WHERE (Mobitel_ID = MobitelIDInCursor) AND

        (UnixTime > TmpMaxUnixTime) AND (Valid = 1)

        GROUP BY Mobitel_ID,

                 LogID

        ORDER BY UnixTime DESC

        LIMIT 100) AS T

      ORDER BY UnixTime ASC;



    /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ

       пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ - пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ 100 пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ */

    DELETE

      FROM online64

    WHERE (Mobitel_ID = MobitelIDInCursor) AND (UnixTime < (SELECT

          COALESCE(MIN(UnixTime), 0)

        FROM (SELECT

            UnixTime

          FROM online64

          WHERE Mobitel_ID = MobitelIDInCursor

          ORDER BY UnixTime DESC

          LIMIT 100) T1));



    FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorMobitelsInsertSet;

  /* ---------------- Online ---------------- */

  /* пїЅпїЅпїЅпїЅпїЅ-пїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ ... */

  UPDATE datagps64 d, datagpsbuffer_on64 b

  SET d.Latitude = b.Latitude,

      d.Longitude = b.Longitude,

      d.Acceleration = b.Acceleration,

      d.UnixTime = b.UnixTime,

      d.Speed = b.Speed,

      d.Direction = b.Direction,

      d.Valid = b.Valid,

      d.LogID = b.LogID,

      d.`Events` = b.`Events`,

      d.Satellites = b.Satellites,

      d.RssiGsm = b.RssiGsm,

      d.SensorsSet = b.SensorsSet,

      d.Sensors = b.Sensors,

      d.Voltage = b.Voltage,

      d.DGPS = b.DGPS,

      d.SrvPacketID = b.SrvPacketID

  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID)

  AND (d.SrvPacketID < b.SrvPacketID);



  /* пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ */

  DELETE b

    FROM datagps64 d, datagpsbuffer_on64 b

  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);



    /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ */ 

  INSERT INTO datagps64 (Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Acceleration,

  Direction, Speed, Valid, `Events`,

  Satellites, RssiGsm, SensorsSet, Sensors, Voltage, DGPS, SrvPacketID)

    SELECT

      Mobitel_ID,

      LogID,

      UnixTime,

      Latitude,

      Longitude,

      Acceleration,

      Direction,

      Speed,

      Valid,

      `Events`,

      Satellites,

      RssiGsm,

      SensorsSet,

      Sensors,

      Voltage,

      DGPS,

      SrvPacketID

    FROM datagpsbuffer_on64

    GROUP BY Mobitel_ID,

             LogID;



  /* ------------------- FETCH Mobitels (INSERT SET) ----------------------- */

  SET DataExists = 1;

  OPEN CursorMobitelsInsertSet;

  FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* ------ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ ------- */

    UPDATE mobitels

    SET LastInsertTime = UNIX_TIMESTAMP(NOW())

    WHERE Mobitel_ID = MobitelIDInCursor;



    /* ------ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ -------------- */

    CALL OnLostDataGPS64(MobitelIDInCursor, NeedOptimizeParam);

    SET NeedOptimize = GREATEST(NeedOptimize, NeedOptimizeParam);

    FETCH CursorMobitelsInsertSet INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorMobitelsInsertSet;



  /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ */

  TRUNCATE TABLE datagpsbuffer_on64;



  /* ------------------- FETCH Mobitels (UPDATE SET) ---------------------- */

  SET DataExists = 1;

  OPEN CursorMobitelsUpdateSet;

  FETCH CursorMobitelsUpdateSet INTO MobitelIDInCursor;

  WHILE DataExists = 1 DO

    /* ---- пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ --- */

    CALL OnLostDataGPS264(MobitelIDInCursor, NeedOptimizeParam);

    SET NeedOptimize = GREATEST(NeedOptimize, NeedOptimizeParam);

    FETCH CursorMobitelsUpdateSet INTO MobitelIDInCursor;

  END WHILE;

  CLOSE CursorMobitelsUpdateSet;



  /* пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅ */

  TRUNCATE TABLE datagpsbuffer_ontmp64;



  /* пїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ - пїЅпїЅпїЅпїЅпїЅпїЅпїЅ пїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅпїЅ */

  /*IF NeedOptimize = 1 THEN

    OPTIMIZE TABLE datagpslost_on;

  END IF;*/

END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `PopListMobitelConfig` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `PopListMobitelConfig`()
    SQL SECURITY INVOKER
    COMMENT 'Выборка настроек телетреков для работы в режиме POP3'
BEGIN
  SELECT m.Mobitel_ID AS MobitelID, c.devIdShort AS DevIdShort,
    COALESCE(
      (SELECT s.EmailMobitel 
       FROM Mobitels m2 JOIN ServiceSend s ON s.ID = m2.ServiceSend_ID 
       WHERE m2.Mobitel_ID = m.Mobitel_ID
       ORDER BY s.ServiceSend_ID DESC
       LIMIT 1), "") AS Email
  FROM mobitels m JOIN internalmobitelconfig c ON c.ID = m.InternalMobitelConfig_ID
  WHERE (c.InternalMobitelConfig_ID = (
    SELECT intConf.InternalMobitelConfig_ID
    FROM internalmobitelconfig intConf
    WHERE (intConf.ID = c.ID)
    ORDER BY intConf.InternalMobitelConfig_ID DESC
    LIMIT 1)) AND (c.devIdShort IS NOT NULL) AND (c.devIdShort <> _cp1251 '')
  ORDER BY m.Mobitel_ID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `PopTransferBuffer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `PopTransferBuffer`()
    SQL SECURITY INVOKER
    COMMENT 'Переносит GPS данные из буферной таблицы в datagps'
BEGIN
  DECLARE MobitelIDInCursor INT; 
  DECLARE DataExists TINYINT DEFAULT 1; 
  
  DECLARE TmpMaxUnixTime INT; 

  DECLARE CursorMobitels CURSOR FOR
    SELECT DISTINCT(Mobitel_ID) FROM datagpsbuffer_pop;

  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

    
  
  
  DELETE o
  FROM online o, datagpsbuffer_pop dgb
  WHERE (o.Mobitel_ID = dgb.Mobitel_ID) AND (o.LogID = dgb.LogID);
  
  SET DataExists = 1;
  OPEN CursorMobitels;
  FETCH CursorMobitels INTO MobitelIDInCursor;
  WHILE DataExists = 1 DO
    
    SELECT COALESCE(MAX(UnixTime), 0) INTO TmpMaxUnixTime
    FROM online FORCE INDEX (IDX_Mobitelid)
    WHERE Mobitel_ID = MobitelIDInCursor;

    
    INSERT INTO online (
      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
      Direction, Speed, Valid, `Events`,
      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
      Counter1, Counter2, Counter3, Counter4, whatIs)
    SELECT
      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
      Direction, Speed, Valid, `Events`,
      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
      Counter1, Counter2, Counter3, Counter4, whatIs
    FROM (
      SELECT *
      FROM datagpsbuffer_pop
      WHERE (Mobitel_ID = MobitelIDInCursor)  AND 
        (UnixTime > TmpMaxUnixTime) AND (Valid = 1)
      GROUP BY Mobitel_ID, LogID
      ORDER BY UnixTime DESC
      LIMIT 100) AS T
    ORDER BY UnixTime ASC;

    
    DELETE FROM online
    WHERE (Mobitel_ID = MobitelIDInCursor) AND (UnixTime < 
      (SELECT COALESCE(MIN(UnixTime), 0) 
       FROM (
         SELECT UnixTime
         FROM online
         WHERE Mobitel_ID = MobitelIDInCursor 
         ORDER BY UnixTime DESC
         LIMIT 100) T1)); 
  
    FETCH CursorMobitels INTO MobitelIDInCursor;
  END WHILE;
  CLOSE CursorMobitels;
    

  
  UPDATE datagps d, datagpsbuffer_pop b SET
    d.Latitude = b.Latitude,
    d.Longitude = b.Longitude,
    d.Altitude = b.Altitude,
    d.UnixTime = b.UnixTime,
    d.Speed = b.Speed,
    d.Direction = b.Direction,
    d.Valid = b.Valid,
    d.InMobitelID = b.LogID,
    d.`Events` = b.`Events`,
    d.Sensor1 = b.Sensor1,
    d.Sensor2 = b.Sensor2,
    d.Sensor3 = b.Sensor3,
    d.Sensor4 = b.Sensor4,
    d.Sensor5 = b.Sensor5,
    d.Sensor6 = b.Sensor6,
    d.Sensor7 = b.Sensor7,
    d.Sensor8 = b.Sensor8,
    d.whatIs = b.whatIs,
    d.Counter1 = b.Counter1,
    d.Counter2 = b.Counter2,
    d.Counter3 = b.Counter3,
    d.Counter4 = b.Counter4
  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);

  
  DELETE b
  FROM datagps d, datagpsbuffer_pop b
  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);

  
  INSERT INTO datagps (
    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
    Direction, Speed, Valid, `Events`, InMobitelID,
    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
    Counter1, Counter2, Counter3, Counter4, whatIs)
  SELECT
    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
    Direction, Speed, Valid, `Events`, LogID,
    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
    Counter1, Counter2, Counter3, Counter4, whatIs
  FROM datagpsbuffer_pop
  GROUP BY Mobitel_ID, LogID;

  
  TRUNCATE TABLE datagpsbuffer_pop;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RfFillLostDataGPS` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RfFillLostDataGPS`()
    COMMENT 'Поиск пропусков в RF данных и заполнение таблицы datagpslost'
BEGIN
  DECLARE MobitelIDInCursor INT; 
  DECLARE DataExists TINYINT DEFAULT 1; 
  DECLARE NeedOptimize TINYINT DEFAULT 0; 
  DECLARE NeedOptimizeParam TINYINT;

  
  DECLARE CursorMobitels CURSOR FOR
    SELECT m.Mobitel_ID AS Mobitel_ID
    FROM mobitels m
      JOIN internalmobitelconfig c ON (c.ID = m.InternalMobitelConfig_ID)
    WHERE (c.InternalMobitelConfig_ID = (
      SELECT intConf.InternalMobitelConfig_ID
      FROM internalmobitelconfig intConf
      WHERE intConf.ID = c.ID 
      ORDER BY intConf.InternalMobitelConfig_ID DESC
      LIMIT 1)) AND
      (c.moduleIdRf IS NOT NULL) AND (c.moduleIdRf <> _cp1251'')
    ORDER BY m.Mobitel_ID;
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;
  
  SET DataExists = 1;
  
  
  DELETE d
  FROM datagpslost d
  WHERE NOT EXISTS(
    SELECT 1
    FROM mobitels m
    WHERE m.Mobitel_ID = d.Mobitel_ID);
  
  
  OPEN CursorMobitels;
  FETCH CursorMobitels INTO MobitelIDInCursor;
  WHILE DataExists = 1 DO
    CALL RfLostDataGPS(MobitelIDInCursor, NeedOptimizeParam);
    SET NeedOptimize = GREATEST(NeedOptimize, NeedOptimizeParam);
    FETCH CursorMobitels INTO MobitelIDInCursor;
  END WHILE;
  CLOSE CursorMobitels;

  
  IF NeedOptimize = 1 THEN
    OPTIMIZE TABLE datagpslost;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RfListMobitelConfig` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RfListMobitelConfig`()
    COMMENT 'Выборка RF настроек телетреков'
BEGIN
  DECLARE MobitelIDInCursor INT;
  DECLARE NameInCursor CHAR(50);
  DECLARE DescrInCursor CHAR(200);
  DECLARE ConfirmedIDInCursor INT;
  DECLARE moduleIdRfInCursor CHAR(50);
  DECLARE LastInsertTimeInCursor DateTime;
  DECLARE DataExists TINYINT DEFAULT 1;

  
  DECLARE CursorMobitels CURSOR FOR
    SELECT m.Mobitel_ID AS Mobitel_ID,
      m.Name AS Name,
      m.Descr AS Descr,
      m.ConfirmedID AS ConfirmedID,
      c.moduleIdRf AS moduleIdRf,
      FROM_UNIXTIME(m.LastInsertTime) AS LastInsertTime
    FROM mobitels m
      JOIN internalmobitelconfig c ON (c.ID = m.InternalMobitelConfig_ID)
    WHERE (c.InternalMobitelConfig_ID = (
      SELECT intConf.InternalMobitelConfig_ID
      FROM internalmobitelconfig intConf
      WHERE intConf.ID = c.ID 
      ORDER BY intConf.InternalMobitelConfig_ID DESC
      LIMIT 1)) AND
      (c.moduleIdRf IS NOT NULL) AND (c.moduleIdRf <> _cp1251'')
    ORDER BY m.Mobitel_ID;

  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

  
  CREATE TEMPORARY TABLE tmpMobitelsConfig (
    Mobitel_ID INT NOT NULL,
    Name CHAR(50),
    Descr CHAR(200),
    ConfirmedID INT NOT NULL,
    moduleIdRf CHAR(50) NOT NULL,
    LastInsertTime DateTime NOT NULL,
    LastLogID INT NOT NULL) ENGINE = MEMORY;

  OPEN CursorMobitels;
  FETCH CursorMobitels INTO MobitelIDInCursor, NameInCursor, DescrInCursor, 
    ConfirmedIDInCursor, moduleIdRfInCursor, LastInsertTimeInCursor;
  WHILE DataExists = 1 DO

    INSERT INTO tmpMobitelsConfig SELECT MobitelIDInCursor, NameInCursor, DescrInCursor, 
      ConfirmedIDInCursor, moduleIdRfInCursor, LastInsertTimeInCursor,
      (SELECT COALESCE(MAX(LogID), 0)
       FROM datagps
       WHERE Mobitel_ID = MobitelIDInCursor) AS LastLogID;

    FETCH CursorMobitels INTO MobitelIDInCursor, NameInCursor, DescrInCursor, 
    ConfirmedIDInCursor, moduleIdRfInCursor, LastInsertTimeInCursor;
  END WHILE;
  CLOSE CursorMobitels;

  SELECT Mobitel_ID, Name, Descr, ConfirmedID, moduleIdRf, LastInsertTime, LastLogID
  FROM tmpMobitelsConfig;

  DROP TEMPORARY TABLE tmpMobitelsConfig;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RfLostDataGPS` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RfLostDataGPS`(IN MobitelID INTEGER(11), OUT NeedOptimize TINYINT)
    COMMENT 'Поиск пропусков в RF данных заданного телетрека и заполнение таб'
BEGIN
  
  DECLARE MAX_RECORD_COUNT INT DEFAULT 33792;
  DECLARE FirstActualLogID INT; 

  DECLARE CurrentConfirmedID INT; 
  DECLARE NewConfirmedID INT DEFAULT 0; 
  DECLARE NewStartLogIDSelect INT DEFAULT 0; 
  DECLARE MaxMobitelLogID INT; 

  DECLARE BeginLogIDInCursor INT; 
  DECLARE EndLogIDInCursor INT; 
  DECLARE CountInLostRange INT; 
  DECLARE DataExists TINYINT DEFAULT 1; 
  DECLARE DataChanged TINYINT DEFAULT 0; 
  DECLARE NeedAnalyze TINYINT DEFAULT 0; 
  
  DECLARE NewRowCount INT DEFAULT 0; 
  
  DECLARE NewLogID INT; 
  DECLARE FirstLogID INT DEFAULT -9999999; 
  DECLARE SecondLogID INT; 
                                                                                    
  
  
  DECLARE CursorLostData CURSOR FOR
    SELECT Begin_LogID, End_LogID
    FROM datagpslost
    WHERE Mobitel_ID = MobitelID
    ORDER BY Begin_LogID;
    
  
  DECLARE CursorNewLogID CURSOR FOR
    SELECT LogID 
    FROM datagpslost_rftmp;
    
  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;

  SET DataExists = 1;
  SET NeedOptimize = 0;
 
  SELECT COALESCE(ConfirmedID, 0) INTO CurrentConfirmedID
  FROM mobitels
  WHERE Mobitel_ID = MobitelID;

  
  SELECT COALESCE(MAX(LogID), 0) INTO MaxMobitelLogID
  FROM datagps
  WHERE (Mobitel_ID = MobitelID);

  IF CurrentConfirmedID <> MaxMobitelLogID THEN
    
    SET FirstActualLogID = MaxMobitelLogID - MAX_RECORD_COUNT;
    IF FirstActualLogID < 0 THEN
      SET FirstActualLogID = 0;
    END IF;

    
    DELETE FROM datagpslost
    WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID < FirstActualLogID);
    
    
    OPEN CursorLostData;
    FETCH CursorLostData INTO BeginLogIDInCursor, EndLogIDInCursor;
    
    
    IF DataExists = 0 THEN
      SET DataChanged = 0;
      SET NeedAnalyze = 1;
      
      SET NewStartLogIDSelect = GREATEST(CurrentConfirmedID, FirstActualLogID);
    END IF;

    
    WHILE DataExists = 1 DO
      
      SELECT COUNT(1) INTO CountInLostRange
      FROM datagps
      WHERE (Mobitel_ID = MobitelID) AND
        (LogID > BeginLogIDInCursor) AND (LogID < EndLogIDInCursor);

      
      IF (CountInLostRange > 0)  THEN
        
        DELETE FROM datagpslost
        WHERE (Mobitel_ID = MobitelID) AND (Begin_LogID >= BeginLogIDInCursor);

        SET DataChanged = 1;
        SET NeedAnalyze = 1;
        SET NewStartLogIDSelect = BeginLogIDInCursor;
        
        
        SET DataExists = 0;
      ELSE
        FETCH CursorLostData INTO BeginLogIDInCursor, EndLogIDInCursor;
      END IF;	
    END WHILE;
    CLOSE CursorLostData;
   
    
       
    IF (NeedAnalyze = 0) AND (EndLogIDInCursor < MaxMobitelLogID) THEN
      SET NewStartLogIDSelect = EndLogIDInCursor;
      SET NeedAnalyze = 1;
    END IF;
   
    

    IF NeedAnalyze = 1 THEN
      TRUNCATE TABLE datagpslost_rftmp;

      
      INSERT INTO datagpslost_rftmp	(LogID)
      SELECT DISTINCT(LogID)
      FROM datagps
      WHERE (Mobitel_ID = MobitelID) AND (LogID >= NewStartLogIDSelect)
      ORDER BY LogID;
      
      
      SET @InsertLostDataStatement = 
        "INSERT INTO datagpslost(Mobitel_ID, Begin_LogID, End_LogID) VALUES ";
      
      
      SET DataExists = 1;
      OPEN CursorNewLogID;
      FETCH CursorNewLogID INTO NewLogID;
      WHILE DataExists = 1 DO
        IF FirstLogID = -9999999 THEN
          
          SET FirstLogID = NewLogID;
        ELSE
          
          SET SecondLogID = NewLogID;
          IF (SecondLogID -	FirstLogID) > 1 THEN
            
            IF NewRowCount > 0 THEN
              SET @InsertLostDataStatement = 
                CONCAT(@InsertLostDataStatement, ",");
            END IF;
            SET @InsertLostDataStatement = 
              CONCAT(@InsertLostDataStatement, 
                " (", MobitelID, ", ", FirstLogID, ", ", SecondLogID, ")");
            
            SET NewRowCount = NewRowCount + 1;
          END IF;
          
          SET	FirstLogID = SecondLogID;
        END IF;	
        FETCH CursorNewLogID INTO NewLogID;
      END WHILE;
      CLOSE CursorNewLogID;
      
        
      IF NewRowCount > 0 THEN
        
        PREPARE stmt FROM @InsertLostDataStatement;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        SET DataChanged = 1;
      END IF; 
             
      
      
      SELECT COALESCE(MIN(Begin_LogID), -1) INTO NewConfirmedID
      FROM datagpslost
      WHERE Mobitel_ID = MobitelID;

      
      IF NewConfirmedID = -1 THEN
        SET NewConfirmedID = MaxMobitelLogID;
      END IF;

      
      IF NewConfirmedID > CurrentConfirmedID THEN
        UPDATE mobitels
        SET ConfirmedID = NewConfirmedID
        WHERE (Mobitel_ID = MobitelID) AND (ConfirmedID < NewConfirmedID);
      END IF;
      
      TRUNCATE TABLE datagpslost_rftmp;
    END IF;	 
    
    IF DataChanged = 1 THEN
      SET NeedOptimize = 1;
    END IF;
  
  END IF;	
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RfMobitelConfig` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RfMobitelConfig`(IN paramMobitelID INTEGER(11))
    COMMENT 'Выборка RF настроек заданного телетрека'
BEGIN
SELECT
  m.Mobitel_ID AS Mobitel_ID,
  m.Name AS Name,
  m.Descr AS Descr,
  m.ConfirmedID AS ConfirmedID,
  c.moduleIdRf AS moduleIdRf,
  FROM_UNIXTIME(m.LastInsertTime) AS LastInsertTime,
  ( SELECT COALESCE(d.LogID, 0)
    FROM datagps d
    WHERE d.Mobitel_ID = m.Mobitel_ID
    ORDER BY d.LogID DESC
    LIMIT 1 ) AS LastLogID
FROM mobitels m
  JOIN internalmobitelconfig c ON (c.ID = m.InternalMobitelConfig_ID)
WHERE (m.Mobitel_ID = paramMobitelID) AND
  (c.InternalMobitelConfig_ID = (
    SELECT intConf.InternalMobitelConfig_ID
    FROM internalmobitelconfig intConf
    WHERE intConf.ID = c.ID
    ORDER BY intConf.InternalMobitelConfig_ID DESC
    LIMIT 1)) AND
  (c.moduleIdRf IS NOT NULL) AND (c.moduleIdRf <> _cp1251'')
ORDER BY m.Mobitel_ID;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `RfTransferBuffer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `RfTransferBuffer`()
    SQL SECURITY INVOKER
    COMMENT 'Переносит GPS данные из буферной таблицы в datagps.'
BEGIN

  DECLARE MobitelIDInCursor INT; 
  DECLARE DataExists TINYINT DEFAULT 1; 
  DECLARE NeedOptimize TINYINT DEFAULT 0; 
  DECLARE NeedOptimizeParam TINYINT;
  DECLARE TmpMaxUnixTime INT; 

  DECLARE CursorMobitels CURSOR FOR
    SELECT DISTINCT(Mobitel_ID) FROM datagpsbuffer;

  
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET DataExists = 0;
  
    
  
  
  DELETE o
  FROM online o, datagpsbuffer_pop dgb
  WHERE (o.Mobitel_ID = dgb.Mobitel_ID) AND (o.LogID = dgb.LogID);
  
  SET DataExists = 1;
  OPEN CursorMobitels;
  FETCH CursorMobitels INTO MobitelIDInCursor;
  WHILE DataExists = 1 DO
    
    SELECT COALESCE(MAX(UnixTime), 0) INTO TmpMaxUnixTime
    FROM online FORCE INDEX (IDX_Mobitelid)
    WHERE Mobitel_ID = MobitelIDInCursor;

    
    INSERT INTO online (
      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
      Direction, Speed, Valid, `Events`,
      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
      Counter1, Counter2, Counter3, Counter4, whatIs)
    SELECT
      Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
      Direction, Speed, Valid, `Events`,
      Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
      Counter1, Counter2, Counter3, Counter4, whatIs
    FROM (
      SELECT * 
      FROM datagpsbuffer
      WHERE (Mobitel_ID = MobitelIDInCursor)  AND 
        (UnixTime > TmpMaxUnixTime) AND (Valid = 1)
      GROUP BY Mobitel_ID, LogID
      ORDER BY UnixTime DESC
      LIMIT 100) AS T
    ORDER BY UnixTime ASC; 

    
    DELETE FROM online
    WHERE (Mobitel_ID = MobitelIDInCursor) AND (UnixTime < 
      (SELECT COALESCE(MIN(UnixTime), 0) 
       FROM (
         SELECT UnixTime
         FROM online
         WHERE Mobitel_ID = MobitelIDInCursor 
         ORDER BY UnixTime DESC
         LIMIT 100) T1)); 
  
    FETCH CursorMobitels INTO MobitelIDInCursor;
  END WHILE;
  CLOSE CursorMobitels;
    


  
  UPDATE datagps d, datagpsbuffer b SET
    d.Message_ID = 0, d.Latitude = b.Latitude,
    d.Longitude = b.Longitude,
    d.Altitude = b.Altitude,
    d.UnixTime = b.UnixTime,
    d.Speed = b.Speed,
    d.Direction = b.Direction,
    d.Valid = b.Valid,
    d.InMobitelID = b.LogID,
    d.`Events` = b.`Events`,
    d.Sensor1 = b.Sensor1,
    d.Sensor2 = b.Sensor2,
    d.Sensor3 = b.Sensor3,
    d.Sensor4 = b.Sensor4,
    d.Sensor5 = b.Sensor5,
    d.Sensor6 = b.Sensor6,
    d.Sensor7 = b.Sensor7,
    d.Sensor8 = b.Sensor8,
    d.isShow = 0,
    d.whatIs = b.whatIs,
    d.Counter1 = b.Counter1,
    d.Counter2 = b.Counter2,
    d.Counter3 = b.Counter3,
    d.Counter4 = b.Counter4
  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);

  
  DELETE b
  FROM datagps d, datagpsbuffer b
  WHERE (d.Mobitel_ID = b.Mobitel_ID) AND (d.LogID = b.LogID);

  
  INSERT INTO datagps (
    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
    Direction, Speed, Valid, `Events`, InMobitelID,
    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
    Counter1, Counter2, Counter3, Counter4, whatIs)
  SELECT
    Mobitel_ID, LogID, UnixTime, Latitude, Longitude, Altitude,
    Direction, Speed, Valid, `Events`, LogID,
    Sensor1, Sensor2, Sensor3, Sensor4, Sensor5, Sensor6, Sensor7, Sensor8,
    Counter1, Counter2, Counter3, Counter4,
    whatIs
  FROM datagpsbuffer
  GROUP BY Mobitel_ID, LogID;

  
  SET DataExists = 1;
  OPEN CursorMobitels;
  FETCH CursorMobitels INTO MobitelIDInCursor;
  WHILE DataExists = 1 DO
    
    UPDATE mobitels SET LastInsertTime = UNIX_TIMESTAMP(NOW())
    WHERE Mobitel_ID = MobitelIDInCursor;
        
    
    CALL RfLostDataGPS(MobitelIDInCursor, NeedOptimizeParam);
    SET NeedOptimize = GREATEST(NeedOptimize, NeedOptimizeParam);
    
    FETCH CursorMobitels INTO MobitelIDInCursor;
  END WHILE;
  CLOSE CursorMobitels;

  
  TRUNCATE TABLE datagpsbuffer;

  
  IF NeedOptimize = 1 THEN
    OPTIMIZE TABLE datagpslost;
  END IF;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `SelectDataGpsPeriodAll` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `SelectDataGpsPeriodAll`(IN mobile int, IN beginperiod datetime, IN endperiod datetime,

IN timebegin varchar(9), IN timeend varchar(9), IN weekdays varchar(15), IN daymonth varchar(90))
BEGIN

  DECLARE time0 time DEFAULT '23:59:59';

  DECLARE time1 time DEFAULT '00:00:00'; 



  DROP TEMPORARY TABLE IF EXISTS tempperiod4;



  CREATE TEMPORARY TABLE tempperiod4 -- временная таблица выборок в указанном диапазоне

  SELECT

    DataGps_ID AS DataGps_ID,

    Mobitel_ID AS Mobitel_ID,

    ((Latitude * 1.0000) / 600000) AS Lat,

    ((Longitude * 1.0000) / 600000) AS Lon,

    Altitude AS Altitude,

    FROM_UNIXTIME(UnixTime) AS `time`,

    (Speed * 1.852) AS speed,

    Valid AS Valid,

    (Sensor8 + (Sensor7 << 8) + (Sensor6 << 16) + (Sensor5 << 24) + (Sensor4 << 32) + (Sensor3 << 40) + (Sensor2 << 48) + (Sensor1 << 56)) AS sensor,

    Events AS Events,

    LogID AS LogID

  FROM datagps

  WHERE Mobitel_ID = mobile AND

  (UnixTime BETWEEN UNIX_TIMESTAMP(beginperiod) AND UNIX_TIMESTAMP(endperiod)) AND

  (Valid = 1) AND NOT (Latitude = 0 AND Longitude = 0)

  ORDER BY UnixTime;



  DROP TEMPORARY TABLE IF EXISTS tempweeks;



  IF weekdays != '' THEN



    CREATE TEMPORARY TABLE tempweeks (

      shead varchar(2)

    );



    SET @saTail = weekdays;

    WHILE @saTail != '' DO

      SET @sHead = SUBSTRING_INDEX(@saTail, ';', 1);

      SET @saTail = SUBSTRING(@saTail, LENGTH(@sHead) + 2);

      INSERT INTO tempweeks (shead)

        VALUES (@sHead);

    END WHILE;

  END IF;



  DROP TEMPORARY TABLE IF EXISTS tempday0;



  IF daymonth != '' THEN



    CREATE TEMPORARY TABLE tempday0 (

      shead varchar(2)

    );



    SET @saTail = daymonth;

    WHILE @saTail != '' DO

      SET @sHead = SUBSTRING_INDEX(@saTail, ';', 1);

      SET @saTail = SUBSTRING(@saTail, LENGTH(@sHead) + 2);

      INSERT INTO tempday0 (shead)

        VALUES (@sHead);

    END WHILE;

  END IF;



  -- getting selecting data

  IF (daymonth != '') AND (weekdays != '') THEN

    IF TIME(timebegin) < TIME(timeend) then

    SELECT

      *

    FROM tempperiod4

    WHERE ((DAY(`time`) IN (SELECT

      *

    FROM tempday0)) AND -- чтение по дням месяца

    (WEEKDAY(`time`) IN (SELECT

      *

    FROM tempweeks)) AND -- чтение по дням недели

    (TIME(`time`) BETWEEN TIME(timebegin) AND TIME(timeend))); -- чтение дат

    END IF;



    IF TIME(timebegin) > TIME(timeend) then

    SELECT

      *

    FROM tempperiod4

    WHERE ((DAY(`time`) IN (SELECT

      *

    FROM tempday0)) AND -- чтение по дням месяца

    (WEEKDAY(`time`) IN (SELECT

      *

    FROM tempweeks)) AND -- чтение по дням недели

    ((time(`time`) > time(timebegin) and time(`time`) < time(time0))

    OR (time(`time`) > time(time1) AND time(`time`) < time(timeend)))); -- чтение дат

    END IF;

    END IF;



      IF (daymonth = '') AND (weekdays != '') THEN

        IF TIME(timebegin) < TIME(timeend) then

        SELECT

          *

        FROM tempperiod4

        WHERE ((WEEKDAY(`time`) IN (SELECT

          *

        FROM tempweeks)) AND -- чтение по дням недели

        (TIME(`time`) BETWEEN TIME(timebegin) AND TIME(timeend))); -- чтение дат

          end IF;



        IF TIME(timebegin) > TIME(timeend) then

        SELECT

          *

        FROM tempperiod4

        WHERE ((WEEKDAY(`time`) IN (SELECT

          *

        FROM tempweeks)) AND -- чтение по дням недели

        ((time(`time`) > time(timebegin) and time(`time`) < time(time0))

    OR (time(`time`) > time(time1) AND time(`time`) < time(timeend)))); -- чтение дат

          end IF;

        END IF;



          IF (daymonth != '') AND (weekdays = '') THEN

            IF TIME(timebegin) < TIME(timeend) then

            SELECT

              *

            FROM tempperiod4

            WHERE ((DAY(`time`) IN (SELECT

              *

            FROM tempday0)) AND -- чтение по дням месяца

            (TIME(`time`) BETWEEN TIME(timebegin) AND TIME(timeend))); -- чтение дат

              END IF;



            IF TIME(timebegin) > TIME(timeend) then

            SELECT

              *

            FROM tempperiod4

            WHERE ((DAY(`time`) IN (SELECT

              *

            FROM tempday0)) AND -- чтение по дням месяца

            ((time(`time`) > time(timebegin) and time(`time`) < time(time0))

              OR (time(`time`) > time(time1) AND time(`time`) < time(timeend)))); -- чтение дат

              END IF;

            END IF;



              IF (daymonth = '') AND (weekdays = '') THEN

                IF TIME(timebegin) < TIME(timeend) then

                SELECT

                  *

                FROM tempperiod4

                WHERE (TIME(`time`) BETWEEN TIME(timebegin) AND TIME(timeend)); -- чтение дат

                  end IF;



                IF TIME(timebegin) > TIME(timeend) then

                SELECT

                  *

                FROM tempperiod4

                WHERE ((time(`time`) > time(timebegin) and time(`time`) < time(time0))

                  OR (time(`time`) > time(time1) AND time(`time`) < time(timeend))); -- чтение дат

                  end IF;

              END IF;

            END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `TestP` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `TestP`()
BEGIN
  SELECT VERSION();
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `TransferBuffer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `TransferBuffer`()
    SQL SECURITY INVOKER
    COMMENT 'Переносит GPS данные из буферной таблицы в datagps.'
BEGIN





CREATE TEMPORARY TABLE IF NOT EXISTS temp1 ENGINE=HEAP(
  SELECT DataGPS_ID FROM datagpsbuffer
  );



UPDATE datagps d, datagpsbuffer b, temp1 t SET
  d.Message_ID = 0, 
  d.Latitude = b.Latitude,
  d.Longitude = b.Longitude,
  d.Altitude = b.Altitude,
  d.UnixTime = b.UnixTime,
  d.Speed = b.Speed,
  d.Direction = b.Direction,
  d.Valid = b.Valid,
  d.InMobitelID = NULL, 
  d.`Events` = b.`Events`,
  d.Sensor1 = b.Sensor1,
  d.Sensor2 = b.Sensor2,
  d.Sensor3 = b.Sensor3,
  d.Sensor4 = b.Sensor4,
  d.Sensor5 = b.Sensor5,
  d.Sensor6 = b.Sensor6,
  d.Sensor7 = b.Sensor7,
  d.Sensor8 = b.Sensor8,
  d.isShow = 0, 
  d.whatIs = b.whatIs,
  d.Counter1 = b.Counter1,
  d.Counter2 = b.Counter2,
  d.Counter3 = b.Counter3,
  d.Counter4 = b.Counter4
WHERE b.DataGps_ID = t.DataGps_ID
  AND d.Mobitel_ID = b.Mobitel_ID
  AND d.LogID = b.LogID;


DELETE b
FROM datagps d, datagpsbuffer b, temp1 t
WHERE b.DataGps_ID = t.DataGps_ID
  AND d.Mobitel_ID = b.Mobitel_ID
  AND d.LogID = b.LogID;


INSERT INTO datagps
  (
  Mobitel_ID,
  LogID,
  UnixTime,
  Latitude,
  Longitude,
  Altitude,
  Direction,
  Speed,
  Valid,
  `Events`,
  Sensor1,
  Sensor2,
  Sensor3,
  Sensor4,
  Sensor5,
  Sensor6,
  Sensor7,
  Sensor8,
  Counter1,
  Counter2,
  Counter3,
  Counter4,
  whatIs
  )
SELECT
  b.Mobitel_ID,
  b.LogID,
  b.UnixTime,
  b.Latitude,
  b.Longitude,
  b.Altitude,
  b.Direction,
  b.Speed,
  b.Valid,
  b.`Events`,
  b.Sensor1,
  b.Sensor2,
  b.Sensor3,
  b.Sensor4,
  b.Sensor5,
  b.Sensor6,
  b.Sensor7,
  b.Sensor8,
  b.Counter1,
  b.Counter2,
  b.Counter3,
  b.Counter4,
  b.whatIs
FROM datagpsbuffer b, temp1 t
WHERE b.DataGps_ID = t.DataGps_ID
GROUP BY b.Mobitel_ID, b.LogID;


DELETE b
FROM datagpsbuffer b, temp1 t
WHERE b.DataGps_ID = t.DataGps_ID;


DROP TEMPORARY TABLE temp1;
END ;;
DELIMITER ;
/*!50003 DROP PROCEDURE IF EXISTS `TransferLiteBuffer` */;
DELIMITER ;;
CREATE DEFINER=`root`@`localhost` PROCEDURE `TransferLiteBuffer`()
    SQL SECURITY INVOKER
    COMMENT 'Переносит GPS данные из буферной таблицы в datagps.'
BEGIN





CREATE TEMPORARY TABLE IF NOT EXISTS temp2 ENGINE=HEAP(
  SELECT DataGPS_ID FROM datagpsbufferlite
  );



UPDATE datagps d, datagpsbuffer b, temp2 t SET
  d.Message_ID = b.Message_ID,
  d.Latitude = b.Latitude,
  d.Longitude = b.Longitude,
  d.Altitude = b.Altitude,
  d.UnixTime = b.UnixTime,
  d.Speed = b.Speed,
  d.Direction = b.Direction,
  d.Valid = b.Valid,
  d.InMobitelID = NULL, 
  d.`Events` = b.`Events`,
  d.Sensor1 = b.Sensor1,
  d.Sensor2 = b.Sensor2,
  d.Sensor3 = b.Sensor3,
  d.Sensor4 = b.Sensor4,
  d.Sensor5 = b.Sensor5,
  d.Sensor6 = b.Sensor6,
  d.Sensor7 = b.Sensor7,
  d.Sensor8 = b.Sensor8,
  d.isShow = b.IsShow,
  d.whatIs = b.whatIs,
  d.Counter1 = b.Counter1,
  d.Counter2 = b.Counter2,
  d.Counter3 = b.Counter3,
  d.Counter4 = b.Counter4
WHERE b.DataGps_ID = t.DataGps_ID
  AND d.Mobitel_ID = b.Mobitel_ID
  AND d.Latitude = b.Latitude
  AND d.Longitude = b.Longitude
  AND d.Altitude = b.Altitude
  AND d.UnixTime = b.UnixTime;


DELETE b
FROM datagps d, datagpsbufferlite b, temp2 t
WHERE b.DataGps_ID = t.DataGps_ID
  AND d.Mobitel_ID = b.Mobitel_ID
  AND d.Latitude = b.Latitude
  AND d.Longitude = b.Longitude
  AND d.Altitude = b.Altitude
  AND d.UnixTime = b.UnixTime;


INSERT INTO datagps
  (
  Mobitel_ID,
  LogID,
  UnixTime,
  Latitude,
  Longitude,
  Altitude,
  Direction,
  Speed,
  Valid,
  `Events`,
  Sensor1,
  Sensor2,
  Sensor3,
  Sensor4,
  Sensor5,
  Sensor6,
  Sensor7,
  Sensor8,
  Counter1,
  Counter2,
  Counter3,
  Counter4,
  whatIs
  )
SELECT
  b.Mobitel_ID,
  b.LogID,
  b.UnixTime,
  b.Latitude,
  b.Longitude,
  b.Altitude,
  b.Direction,
  b.Speed,
  b.Valid,
  b.`Events`,
  b.Sensor1,
  b.Sensor2,
  b.Sensor3,
  b.Sensor4,
  b.Sensor5,
  b.Sensor6,
  b.Sensor7,
  b.Sensor8,
  b.Counter1,
  b.Counter2,
  b.Counter3,
  b.Counter4,
  b.whatIs
FROM datagpsbufferlite b, temp2 t
WHERE b.DataGps_ID = t.DataGps_ID
GROUP BY b.Mobitel_ID, b.LogID, b.Latitude, b.Longitude, b.Altitude, b.UnixTime;


DELETE b
FROM datagpsbufferlite b, temp2 t
WHERE b.DataGps_ID = t.DataGps_ID;


DROP TEMPORARY TABLE temp2;
END ;;
DELIMITER ;


