<?php

include_once 'server/config/config.php';
include_once 'server/class/Unit.php';
include_once 'server/lib/mod_dbm/package.dbm.php';
include_once 'server/class/GeoUtils.php';
include_once 'server/lib/mod_util/Util.php';

set_time_limit(90);

class MachineManager {

    private static $positionFieldList = "p.`log_id`,
					    p.`timestamp`,
					    p.`lat`,
					    p.`lng`,
					    p.`speed`,
					    p.`heading`,
					    p.`altitude`,
					    p.`precision`,
					    p.`valid`,
					    p.`satellites`,
					    p.`motion_status`,
					    p.`MNC`,
					    p.`CID`,
					    p.`LAC`,
					    p.`MCC`,
					    p.`total_mileage`,
					    p.`total_hours`,
					    p.`current_hours`,
					    p.`current_mileage`,
					    p.`external_voltage`,
					    p.`gps_antenna_connected`,
					    p.`ad1`,
					    p.`ad2`,
					    p.`ad3`,
					    p.`in1`,
					    p.`in2`,
					    p.`in3`,
					    p.`in4`,
					    p.`in5`,
					    p.`out1`,
					    p.`out2`,
					    p.`out3`,
					    p.`out4`,
					    p.`out5`,
					    p.`ignition`,
					    p.`message_type`,
					    p.`original_type`,
					    p.`abstract_type`,
					    p.`report_id`,
					    p.`report_type`";
    private static $fieldList = "u.`unit_id`,
						u.`vers_protocol`,
					    u.`vers_firmware`,
					    u.`vers_hardware`,
					    u.`vers_mcu`,
					    u.`nombre_comun`,
					    u.`id_flota`,
					    u.`IMEI`,
					    u.`current_position`,
					    u.`device_type_id`,
					    u.`last_fix_timestamp`,
					    u.`activo`,
					    u.`matricula`,
					    u.`icon_id`,
						p.`log_id`,
					    p.`timestamp`,
					    p.`lat`,
					    p.`lng`,
					    p.`speed`,
					    p.`heading`,
					    p.`altitude`,
					    p.`precision`,
					    p.`valid`,
					    p.`satellites`,
					    p.`motion_status`,
					    p.`MNC`,
					    p.`CID`,
					    p.`LAC`,
					    p.`MCC`,
					    p.`total_mileage`,
					    p.`total_hours`,
					    p.`current_hours`,
					    p.`current_mileage`,
					    p.`external_voltage`,
					    p.`gps_antenna_connected`,
					    p.`ad1`,
					    p.`ad2`,
					    p.`ad3`,
					    p.`in1`,
					    p.`in2`,
					    p.`in3`,
					    p.`in4`,
					    p.`in5`,
					    p.`out1`,
					    p.`out2`,
					    p.`out3`,
					    p.`out4`,
					    p.`out5`,
					    p.`ignition`,
					    p.`message_type`,
					    p.`original_type`,
					    p.`abstract_type`,
					    p.`report_id`,
					    p.`report_type`,
						f.nombre_flota";

    public static function getUnitsCommStatus() {


        $q = "SELECT nombre_flota, u.* , r.reception_timestamp,p.timestamp, p.battery_level as internal, round(p.external_voltage/1000,1) as external
				FROM units u
				LEFT JOIN flotas f ON u.id_flota = f.id_flota
				LEFT JOIN log_position p ON u.current_position = p.log_id
				LEFT JOIN log_raw r ON r.log_id = p.log_id
				WHERE current_position_timestamp IS NOT NULL
				ORDER BY nombre_flota, current_position_timestamp";

        //echo $q;

        $dao = new dbObject();
        $res = $dao->fetchQuery($q);

        //echo "<pre>";
        //print_r($res);

        return $res;
    }

    public static function getUnitName($unitId) {
        $q = sprintf("SELECT nombre_comun FROM units WHERE unit_id = '%s'", $unitId);
        $dao = new dbObject();
        $res = $dao->fetchQuery($q);
        $name = (isset($res[0]['nombre_comun'])) ? $res[0]['nombre_comun'] : null;
        return $name;
    }

    public static function belongsToOrganization($unitId, $org) {
        $query = sprintf("SELECT u.unit_id, o.id_organizacion
							FROM organizaciones o
							JOIN flotas f ON o.id_organizacion = f.id_organizacion
							JOIN units u ON u.id_flota = f.id_flota
							WHERE unit_id = '%s' AND o.id_organizacion = %s", $unitId, $org);
        $dao = new dbObject();
        $res = $dao->fetchQuery($query);
        return (is_array($res) && count($res));
    }

    public static function getFleet($aUnitId) {

        $dao = new dbObject();
        //$
    }

    public static function getUnitsCurrentPosition($user, $unitIds = false) {

        if ($unitIds === false) {

            if ($user->isSysAdmin()) {
                $query = sprintf("
					  SELECT u.unit_id,u.custom_id,f.nombre_flota,f.activa,u.nombre_comun,u.matricula,u.max_allowed_speed,u.hour_offset,  u.km_offset,
					  u.current_position_timestamp as timestamp,
					    p.`lat`,
					    p.`lng`,
					    p.`speed`,
					    p.`heading`,
					    p.`altitude`,
					    p.`precision`,
					    p.`valid`,
					    p.`satellites`,
					    p.`motion_status`,
					    p.`MNC`,
					    p.`CID`,
					    p.`LAC`,
					    p.`MCC`,
					    p.`total_mileage`,
					    p.`total_hours`,
					    p.`current_hours`,
					    p.`current_mileage`,
					    p.`external_voltage`,
					    p.`gps_antenna_connected`,
					    p.`ad1`,
					    p.`ad2`,
					    p.`ad3`,
					    p.`in1`,
					    p.`in2`,
					    p.`in3`,
					    p.`in4`,
					    p.`in5`,
					    p.`out1`,
					    p.`out2`,
					    p.`out3`,
					    p.`out4`,
					    p.`out5`,
					    p.`ignition`,
					    p.`message_type`,
					    p.`original_type`,
					    p.`abstract_type`,
					    p.`report_id`,
					    p.`report_type`, i.* FROM units u
					  LEFT JOIN log_position p ON u.current_position = p.log_id
					  LEFT JOIN flotas f ON u.id_flota = f.id_flota
					  LEFT JOIN icons i ON u.icon_id=i.icon_id
					  WHERE u.current_position IS NOT NULL

					ORDER BY nombre_comun");
            } elseif ($user->isOrgAdmin()) {
                $query = sprintf("SELECT u.unit_id,u.custom_id,f.nombre_flota,f.activa,u.nombre_comun,u.matricula,u.max_allowed_speed,u.hour_offset, u.km_offset, u.current_position_timestamp as timestamp,
					    p.`lat`,
					    p.`lng`,
					    p.`speed`,
					    p.`heading`,
					    p.`altitude`,
					    p.`precision`,
					    p.`valid`,
					    p.`satellites`,
					    p.`motion_status`,
					    p.`MNC`,
					    p.`CID`,
					    p.`LAC`,
					    p.`MCC`,
					    p.`total_mileage`,
					    p.`total_hours`,
					    p.`current_hours`,
					    p.`current_mileage`,
					    p.`external_voltage`,
					    p.`gps_antenna_connected`,
					    p.`ad1`,
					    p.`ad2`,
					    p.`ad3`,
					    p.`in1`,
					    p.`in2`,
					    p.`in3`,
					    p.`in4`,
					    p.`in5`,
					    p.`out1`,
					    p.`out2`,
					    p.`out3`,
					    p.`out4`,
					    p.`out5`,
					    p.`ignition`,
					    p.`message_type`,
					    p.`original_type`,
					    p.`abstract_type`,
					    p.`report_id`,
					    p.`report_type`,i.* FROM units u
					  LEFT JOIN log_position p ON u.current_position = p.log_id
					  JOIN flotas f ON u.id_flota = f.id_flota
					  LEFT JOIN icons i ON u.icon_id=i.icon_id
					  WHERE f.id_organizacion = %s AND u.current_position IS NOT NULL
					  ORDER BY nombre_comun", $user->getId_organizacion());
            } else {
                $query = sprintf("SELECT u.unit_id,u.custom_id,f.nombre_flota,f.activa,u.nombre_comun,u.matricula,u.max_allowed_speed,u.hour_offset,  u.km_offset, u.current_position_timestamp as timestamp,
					    p.`lat`,
					    p.`lng`,
					    p.`speed`,
					    p.`heading`,
					    p.`altitude`,
					    p.`precision`,
					    p.`valid`,
					    p.`satellites`,
					    p.`motion_status`,
					    p.`MNC`,
					    p.`CID`,
					    p.`LAC`,
					    p.`MCC`,
					    p.`total_mileage`,
					    p.`total_hours`,
					    p.`current_hours`,
					    p.`current_mileage`,
					    p.`external_voltage`,
					    p.`gps_antenna_connected`,
					    p.`ad1`,
					    p.`ad2`,
					    p.`ad3`,
					    p.`in1`,
					    p.`in2`,
					    p.`in3`,
					    p.`in4`,
					    p.`in5`,
					    p.`out1`,
					    p.`out2`,
					    p.`out3`,
					    p.`out4`,
					    p.`out5`,
					    p.`ignition`,
					    p.`message_type`,
					    p.`original_type`,
					    p.`abstract_type`,
					    p.`report_id`,
					    p.`report_type`,i.* FROM units u
					  LEFT JOIN log_position p ON u.current_position = p.log_id
					  LEFT JOIN flotas f ON u.id_flota = f.id_flota
					  LEFT JOIN icons i ON u.icon_id=i.icon_id
					  WHERE u.current_position IS NOT NULL AND u.id_flota IN (SELECT id_flota FROM flotas_de_usuario WHERE id_usuario = %s) AND u.activo = 1 ORDER BY nombre_comun", $user->getId_usuario());
            }
        } else {
            $query = sprintf("SELECT * FROM units u
						    JOIN log_position p ON u.current_position = p.log_id
							JOIN icons i ON u.icon_id=i.icon_id
							WHERE p.unit_id IN %s  AND u.current_position IS NOT NULL ORDER BY nombre_comun", MachineManager::getFilter($user, $unitIds));
        }
        //echo $query;
        $dao = new dbObject();
        $positions = $dao->fetchQuery($query);

        //echo "<pre>x";
        //print_r($positions);

        $processedPositions = null;
        if (is_array($positions) && count($positions)) {
            foreach ($positions as $position) {
                $position['style'] = GeoUtils::getRowStyle($position['motion_status']);
                $processedPositions[] = $position;
            }
        }
        return $processedPositions;
    }

    public static function getUnits($user) {
        if ($user->isSysAdmin()) {
            $query = sprintf("SELECT u.unit_id, nombre_comun,custom_id,hour_offset,km_offset FROM units u");
        } elseif ($user->isOrgAdmin()) {
            $query = sprintf("SELECT u.unit_id, nombre_comun,custom_id,hour_offset,km_offset FROM units u JOIN flotas f ON u.id_flota = f.id_flota WHERE f.id_organizacion = %s", $user->getId_organizacion());
        } else {
            $query = sprintf("SELECT u.unit_id,nombre_comun,custom_id,hour_offset,km_offset FROM units WHERE u.current_position IS NOT NULL AND u.id_flota IN (SELECT id_flota FROM flotas_de_usuario WHERE id_usuario = %s)", $user->getId_usuario());
        }
        //echo $query;
        $dao = new dbObject();
        $units = $dao->fetchQuery($query);

        return $units;
    }

    public static function getKmInicioMes($unitId, $mesInicio, $anioInicio) {

        try {
            $mesAnioFin = Util::getMesAnioSiguiente($mesInicio, $anioInicio);

            $mesFin = $mesAnioFin['mes'];
            $anioFin = $mesAnioFin['anio'];

            $ts1 = mktime(0, 0, 0, $mesInicio, 1, $anioInicio);
            $ts2 = mktime(0, 0, 0, $mesFin, $anioFin);

            $km = MachineManager::getKilometraje($unitId, $ts1, $ts2);
        } catch (Exception $e) {
            $km = false;
        }
        return $km;
    }

    public static function getKilometraje($unitId, $ts1, $ts2) {
        $q = sprintf("SELECT p.total_mileage as km
                FROM log_position p
                WHERE unit_id = '%s'
                    AND timestamp > %s AND timestamp < %s
                ORDER BY timestamp
                LIMIT 0,1", $unitId, $ts1, $ts2);

        $dao = new dbObject();
        $res = $dao->fetchQuery($q);
        try {
            $km = $res[0]['km'];
        } catch (Exception $e) {
            $km = 0;
        }
        return $km;
    }

    public static function getResume($unitId, $fromTimestamp, $toTimestamp) {

        $timeConstraint = MachineManager::buildTimeConstraint($fromTimestamp, $toTimestamp);
        $dao = new dbObject();
        $query = sprintf("SELECT * FROM log_position WHERE unit_id = '%s' AND %s ORDER BY timestamp", $unitId, $timeConstraint);
        //echo $query;
        $res = $dao->fetchQuery($query);

        $brief['startTime'] = 0;
        $brief['startMileage'] = 0;
        $brief['endTime'] = 0;
        $brief['endMileage'] = 0;
        $brief['maxSpeed'] = 0;
        $brief['encendidos'] = 0;
        $brief['detenciones'] = 0;

        if (is_array($res) && count($res)) {

            foreach ($res as $i => $point) {

                if ($i == 0) {
                    $brief['startTime'] = $point['total_hours'];
                    $brief['startMileage'] = $point['total_mileage'];
                }

                if ($i == (count($res) - 1)) {
                    $brief['endTime'] = $point['total_hours'];
                    $brief['endMileage'] = $point['total_mileage'];
                }

                if ($point['speed'] > $brief['maxSpeed'])
                    $brief['maxSpeed'] = $point['speed'];
                //if(($point['report_id'] == 16) && ($point['report_type'] == 1)) $brief['encendidos']++;
                //if(($point['motion_status'] == 21)) $brief['detenciones']++;
                if (($point['original_type'] == 13) && ($point['message_type'] == "+EVT" || $point['message_type'] == "+BVT"))
                    $brief['encendidos']++;
                if (($point['original_type'] == 24) && ($point['message_type'] == "+EVT" || $point['message_type'] == "+BVT"))
                    $brief['detenciones']++;
            }
        }
        //echo "<pre>";print_r($brief);
        return $brief;
    }

    /*
      public static function getKmTableData($id,$initTs,$endTs){

      $q1 = "SELECT date(from_unixtime(timestamp)),total_mileage FROM log_position WHERE unit_id = '%s' AND timestamp >= %d AND timestamp < %d+86400 LIMIT 0,1";
      $q2 = "SELECT date(from_unixtime(timestamp)),total_mileage FROM log_position WHERE unit_id = '%s' AND timestamp >= %d AND timestamp < %d+86400 ORDER BY TIMESTAMP DESC LIMIT 0,1";
      $ts = $initTs;
      $dao = new dbObject();

      $kmTable = array();

      $i=0;

      $lastEnd = "-";

      while($ts <= $endTs){
      $query = sprintf($q1,$id,$ts,$ts);
      $rs = $dao->fetchQuery($query);
      $date = gmdate("d/m/Y", $ts);
      $kmTable[$i]['date'] = $date;
      if(is_array($rs) && count($rs)){
      $kmTable[$i]['init'] = $rs[0]['total_mileage'];
      if($i>0){
      $kmTable[$i-1]['end'] = $rs[0]['total_mileage'];
      }
      $lastEnd = $rs[0]['total_mileage'];
      } else {
      $kmTable[$i]['init'] = $lastEnd;
      if($i>0){
      $kmTable[$i-1]['end'] = $lastEnd;
      }
      }
      $ts = $ts + 86400;
      $i++;
      }
      $query = sprintf($q2,$id,$ts,$ts);
      $rs = $dao->fetchQuery($q2);

      if(is_array($rs) && count($rs)){
      if($i>0){
      $kmTable[$i-1]['end'] = $rs[0]['total_mileage'];
      }
      $lastEnd = $rs[0]['total_mileage'];
      } else {
      $kmTable[$i-1]['end'] = $lastEnd;
      }
      //echo "<pre>";
      //print_r($kmTable);
      return $kmTable;
      }
     */

    public static function getHrTableData($id, $initTs, $endTs) {

        $q1 = "SELECT date(from_unixtime(timestamp)),total_hours FROM log_position WHERE unit_id = '%s' AND timestamp >= %d AND timestamp < %d+86400 ORDER BY timestamp LIMIT 0,1";
        $q2 = "SELECT date(from_unixtime(timestamp)),total_hours FROM log_position WHERE unit_id = '%s' AND timestamp >= %d AND timestamp < %d+86400 ORDER BY timestamp DESC LIMIT 0,1";
        $ts = $initTs;
        $dao = new dbObject();

        $hrTable = array();

        $i = 0;

        $lastEnd = "-";

        while ($ts <= $endTs) {
            $query1 = sprintf($q1, $id, $ts, $ts);
            $rs1 = $dao->fetchQuery($query1);

            $query2 = sprintf($q2, $id, $ts, $ts);
            $rs2 = $dao->fetchQuery($query2);

            $date = gmdate("d/m/Y", $ts);

            $hrTable[$i]['date'] = $date;
            $hrTable[$i]['init'] = (is_array($rs1) && count($rs1)) ? $rs1[0]['total_hours'] : "-";
            $hrTable[$i]['end'] = (is_array($rs2) && count($rs2)) ? $rs2[0]['total_hours'] : "-";

            $ts = $ts + 86400;
            $i++;
        }

        return $hrTable;
    }

    public static function getKmTableData($id, $initTs, $endTs) {

        $q = sprintf("SELECT date_format(from_unixtime(timestamp),\"%%d/%%m/%%Y\") as `date` ,min(total_mileage) as init
				FROM log_position
				WHERE unit_id = \"%s\" AND timestamp BETWEEN %d AND %d
				GROUP BY  date_format(from_unixtime(timestamp),\"%%d/%%m/%%Y\")", $id, $initTs, $endTs + 2 * 86400);

        $ts = $initTs;
        $dao = new dbObject();
        $kmTable = $dao->fetchQuery($q);
        if (is_array($kmTable) && count($kmTable)) {
            foreach ($kmTable as $i => $dateKm) {
                if ($i < (count($kmTable) - 1)) {
                    $kmTableP[$i] = $dateKm;
                }
                if ($i > 0) {
                    $kmTableP[$i - 1]['end'] = $dateKm['init'];
                }
            }
        }
        //echo "<pre>";
        //print_r($kmTableP);
        return $kmTableP;
    }

    public static function getCurrentHours($aUnitId) {

        $currentHours = false;

        $dao = new dbObject();
        $q = sprintf("SELECT u.unit_id, total_hours,hour_offset,total_hours+hour_offset as gps_hours
                        FROM units u LEFT JOIN log_position p
                        ON u.current_position = p.log_id
                        WHERE u.unit_id = '%s'", $aUnitId);
        $res = $dao->fetchQuery($q);
        if (is_array($res) && count($res)) {
            $currentHours = $res[0]['gps_hours'];
        }

        return $currentHours;
    }

    public static function getCurrentKm($aUnitId) {

        $currentHours = false;

        $dao = new dbObject();
        $q = sprintf("SELECT u.unit_id, total_hours,hour_offset,total_mileage+km_offset as gps_km
                        FROM units u LEFT JOIN log_position p
                        ON u.current_position = p.log_id
                        WHERE u.unit_id = '%s'", $aUnitId);
        $res = $dao->fetchQuery($q);
        if (is_array($res) && count($res)) {
            $currentHours = $res[0]['gps_km'];
        }

        return $currentHours;
    }

    public static function getPositionLog($unitIds, $fromTimestamp, $toTimestamp, $simplified = false) {

        $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
        $timeConstraint = MachineManager::buildTimeConstraint($fromTimestamp, $toTimestamp, false);
        $simplifyConstraint = ($simplified) ? " AND (original_type=7 AND report_type IN (1,3))" : "";
        $query = sprintf("SELECT p.*,from_unixtime(timestamp) as fecha
						  FROM log_position p WHERE %s AND %s %s
						  ORDER BY timestamp,p.log_id", $unitConstraint, $timeConstraint, $simplifyConstraint);
        //echo $query;
        $dao = new dbObject();
        $res = $dao->fetchQuery($query);

        //echo "<pre>";
        //print_r($res);

        return $res;
    }

    public static function getRecorridoReferenciado($unitIds, $fromTimestamp, $toTimestamp) {

        $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
        $timeConstraint = MachineManager::buildTimeConstraint($fromTimestamp, $toTimestamp);

        $query = sprintf("SELECT p.*,from_unixtime(timestamp) as fecha
						  FROM log_position p WHERE %s AND %s
						  ORDER BY timestamp,p.log_id", $unitConstraint, $timeConstraint);

        $dao = new dbObject();
        $res = $dao->fetchQuery($query);

        return $res;
    }

    public static function getStops($unitIds, $fromTimestamp, $toTimestamp) {


        $stops = Array();

        $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
        $timeConstraint = sprintf("(timestamp BETWEEN %s AND %s)", $fromTimestamp, $toTimestamp); //UnitManager::buildTimeConstraint($fromTimestamp,$toTimestamp);
        $query = sprintf("SELECT p.*,from_unixtime(timestamp) as fecha
						  FROM log_position p WHERE %s AND %s
						  ORDER BY timestamp", $unitConstraint, $timeConstraint);
        //echo $query;
        $dao = new dbObject();
        $res = $dao->fetchQuery($query);

        if (is_array($res) && count($res)) {

            $lastQuietTimestamp = $res[0]['timestamp'];
            $lastQuietLat = $res[0]['lat'];
            $lastQuietLng = $res[0]['lng'];
            $lastQuietMileage = $res[0]['total_mileage'];
            $currentlyQuiet = false;

            foreach ($res as $i => $point) {

                $currentLat = $point['lat'];
                $currentLng = $point['lng'];
                $currentTimestamp = $point['timestamp'];
                $currentMileage = $point['total_mileage'];

                if (GeoUtils::getTheDistance($lastQuietLat, $lastQuietLng, $currentLat, $currentLng) < QUIET_RADIUS) {
                    if ((($currentTimestamp - $lastQuietTimestamp) > QUIET_THRESHOLD) && !$currentlyQuiet) {
                        $currentlyQuiet = true;
                    }
                } else {
                    if ($currentlyQuiet) {
                        $stop['lat'] = $lastQuietLat;
                        $stop['lng'] = $lastQuietLng;
                        $stop['start'] = $lastQuietTimestamp;
                        $stop['end'] = $currentTimestamp;
                        $stop['mileage'] = $lastQuietMileage;
                        $stops[] = $stop;
                    }
                    $lastQuietLat = $currentLat;
                    $lastQuietLng = $currentLng;
                    $lastQuietTimestamp = $currentTimestamp;
                    $lastQuietMileage = $currentMileage;
                    $currentlyQuiet = false;
                }
            }
        }

        return $stops;
    }

    public static function getVelocidades($machineId, $fromTimestamp, $toTimestamp) {


        $speedProfile = array();
        $unit = new Unit($machineId);
        $maxSpeed = $unit->getMaxAllowedSpeed();

        $timeConstraint = sprintf("(gps_timestamp BETWEEN %s AND %s)", $fromTimestamp, $toTimestamp); //UnitManager::buildTimeConstraint($fromTimestamp,$toTimestamp);
        // $dataPA[0] = array('id' => '1', 'fecha' => '03-08-2022', 'hora' => '15:31', 'max' =>'110', 'vel' =>'35');

        $query = sprintf("SELECT date_format(from_unixtime(gps_timestamp),\"%%d/%%m %%h:%%i\")
                            as fecha, speed as vel, IF(inputs=1,%s,0) as max FROM log_positions 
                            WHERE machine_id = %d
                             AND %s", $maxSpeed, $machineId, $timeConstraint);
        $dao = new dbObject();
        $res = $dao->fetchQuery($query);

        return $res;
    }

    public static function getEcuData($unitIds, $fromTimestamp, $toTimestamp) {


        $ecudata = Array();

        $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
        $timeConstraint = sprintf("(timestamp BETWEEN %s AND %s)", $fromTimestamp, $toTimestamp); //UnitManager::buildTimeConstraint($fromTimestamp,$toTimestamp);
        $query = sprintf("SELECT e.*,from_unixtime(timestamp) as fecha
						  FROM ecudata e WHERE %s AND %s
						  ORDER BY timestamp", $unitConstraint, $timeConstraint);
        //echo $query;
        $dao = new dbObject();
        $ecudata = $dao->fetchQuery($query);
        return $ecudata;
    }

    public static function getGeoData($unitIds, $fromTimestamp, $toTimestamp) {


        $geoData = Array();

        $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
        $timeConstraint = sprintf("(timestamp BETWEEN %s AND %s)", $fromTimestamp, $toTimestamp); //UnitManager::buildTimeConstraint($fromTimestamp,$toTimestamp);
        $query = sprintf("SELECT e.*,from_unixtime(timestamp) as fecha
						  FROM geo_events e WHERE %s AND %s
						  ORDER BY timestamp", $unitConstraint, $timeConstraint);
        //echo $query;
        $dao = new dbObject();
        $geoData = $dao->fetchQuery($query);
        return $geoData;
    }

    public static function getMaxAllowedSpeed($unitId) {
        $maxSpd = DEFAULT_MAX_ALLOWED_SPEED;
        $q = sprintf("SELECT max_allowed_speed FROM units WHERE unit_id = '%s'", $unitId);
        $dao = new dbObject();
        $res = $dao->fetchQuery($q);

        if (is_array($res) && count($res)) {
            $maxSpd = $res[0]['max_allowed_speed'];
        }
        return $maxSpd;
    }

    public static function getEngineStarts($unitIds, $fromTimestamp, $toTimestamp) {

        $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
        $timeConstraint = sprintf("(timestamp BETWEEN %s AND %s)", $fromTimestamp, $toTimestamp); //UnitManager::buildTimeConstraint($fromTimestamp,$toTimestamp);
        $query = sprintf("SELECT p.*,from_unixtime(timestamp) as fecha
						  FROM log_position p
					      WHERE %s AND %s
						  AND (message_type = '+EVT' OR message_type = '+BVT')
						  AND original_type = 13
						  ORDER BY timestamp", $unitConstraint, $timeConstraint);
        //echo $query;
        $dao = new dbObject();
        $res = $dao->fetchQuery($query);

        return $res;
    }

    public static function getOverspeedTraces($unitId, $fromTimestamp, $toTimestamp) {

        $stops = Array();

        $unitConstraint = MachineManager::buildUnitConstraint($unitId);
        $timeConstraint = $timeConstraint = sprintf("(timestamp BETWEEN %s AND %s)", $fromTimestamp, $toTimestamp); //UnitManager::buildTimeConstraint($fromTimestamp,$toTimestamp);

        $maxSpeed = MachineManager::getMaxAllowedSpeed($unitId);

        $query = sprintf("SELECT p.*,from_unixtime(timestamp) as fecha
						  FROM log_position p WHERE unit_id = '%s' AND %s
						  ORDER BY timestamp", $unitId, $timeConstraint);

        $dao = new dbObject();
        $res = $dao->fetchQuery($query);

        $tracks = array();
        $trackOpen = false;

        if (is_array($res) && count($res)) {
            foreach ($res as $i => $point) {
                if ($point['speed'] > $maxSpeed) {

                    if (isset($newPoint['lat']) && isset($newPoint['lng']) && isset($newPoint['speed']) && isset($newPoint['timestamp'])) {

                        if (!(($newPoint['lat'] == $point['lat']) && ($newPoint['lng'] == $point['lng']) && ($newPoint['speed'] == $point['speed']) && ($newPoint['timestamp'] == $point['timestamp']))) {
                            $newPoint['lat'] = $point['lat'];
                            $newPoint['lng'] = $point['lng'];
                            $newPoint['speed'] = $point['speed'];
                            $newPoint['timestamp'] = $point['timestamp'];
                            $newPoint['mileage'] = $point['total_mileage'];
                            $track[] = $newPoint;
                        }
                    } else {
                        $newPoint['lat'] = $point['lat'];
                        $newPoint['lng'] = $point['lng'];
                        $newPoint['speed'] = $point['speed'];
                        $newPoint['timestamp'] = $point['timestamp'];
                        $newPoint['mileage'] = $point['total_mileage'];
                        $track[] = $newPoint;
                    }

                    $trackOpen = true;
                } else {
                    if ($trackOpen) {
                        $trackOpen = false;
                        $tracks[] = $track;
                        unset($track);
                    }
                }
            }
        }

        return $tracks;
    }

    /*
      public static function getHorasDeUso($machineId, $fromTimestamp, $toTimestamp, $interval = UN_DIA) {

      $reporte = array();
      $init = $fromTimestamp;
      $dao = new dbObject();

      $unit = new Unit($machineId);
      $costoKm = $unit->getCostoKm();
      $costoHr = $unit->getCostoHr();

      $mileageOffset = $unit->getMileageOffset();
      $hourOffset = $unit->getHourOffset();

      $count = 0;
      $totKm = 0;
      $totHr = 0;
      $totCostoKm = 0;
      $totCostoHr = 0;

      $dao = new dbObject();
      $q = sprintf("SELECT machine_id, DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),\"%%d/%%m/%%y\") as fecha, DAYOFWEEK(DATE(FROM_UNIXTIME(gps_timestamp))) as diasem, min(mileage) as kmI,max(mileage) as kmF, max(mileage)-min(mileage) as distance,
      min(hours) as hI, max(hours) as hF, max(hours) - min(hours) as use_hours
      FROM log_positions WHERE machine_id = %d AND gps_timestamp BETWEEN %d AND %d AND mileage >0
      GROUP BY DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),\"%%d/%%m/%%y\")", $machineId, $fromTimestamp, $toTimestamp);

      $res = $dao->fetchQuery($q);

      if (is_array($res) && count($res)) {
      foreach ($res as $i => $day) {
      $pDay['fecha'] = $day['fecha'];
      $pDay['diaSem'] = Util::getDiaSemana($day['diasem']);
      $pDay['hInicio'] = number_format($day['hI'] + $hourOffset, 2);
      $pDay['hFin'] = number_format($day['hF'] + $hourOffset, 2);
      $pDay['kmFin'] = number_format($day['kmF'] + $mileageOffset, 2);
      $pDay['kmInicio'] = number_format($day['kmI'] + $mileageOffset, 2);
      $pDay['km'] = number_format($day['distance'], 2);
      $pDay['horas'] = number_format($day['use_hours'], 2);
      $pDay['pct'] = number_format((floatval($pDay['horas']) / 24) * 100, 2) . " %";
      $pDay['costoHr'] = number_format(floatval($pDay['horas']) * $costoHr, 2);
      $pDay['costoKm'] = number_format(floatval($pDay['km']) * $costoKm, 2);
      $pDay['costo'] = number_format(floatval($pDay['costoHr']) + floatval($pDay['costoKm']), 2);

      $totKm += floatval($pDay['km']);
      $totHr += floatval($pDay['horas']);
      $totCostoKm += $pDay['costoKm'];
      $totCostoHr += $pDay['costoHr'];

      $pDays[] = $pDay;
      }
      }

      // Totales
      $pDay['fecha'] = "Totales";
      $pDay['diaSem'] = "";
      $pDay['hInicio'] = "";
      $pDay['hFin'] = "<b>Hr</b>";
      $pDay['kmFin'] = "<b>Km</b>";
      $pDay['kmInicio'] = "";
      $pDay['km'] = number_format($totKm, 2);
      $pDay['horas'] = number_format($totHr, 2);
      $pDay['pct'] = "<b>Costos</b>";
      $pDay['costoHr'] = number_format($totCostoHr, 2);
      $pDay['costoKm'] = number_format($totCostoKm, 2);
      $pDay['costo'] = number_format($totCostoHr + $totCostoKm, 2);
      $pDays[] = $pDay;

      // Promedios
      $days = round(($toTimestamp - $fromTimestamp) / 86400);

      $pDay['fecha'] = "Promedios";
      $pDay['diaSem'] = "";
      $pDay['hInicio'] = "";
      $pDay['hFin'] = "<b>Hr</b>";
      $pDay['kmFin'] = "<b>Km</b>";
      $pDay['kmInicio'] = "";
      $pDay['km'] = ($days > 0) ? number_format($totKm / $days, 2) : "-";
      $pDay['horas'] = ($days > 0) ? number_format($totHr / $days, 2) : "-";
      $pDay['pct'] = "<b>Costos</b>";

      $pDay['costoHr'] = ($days > 0) ? number_format($totCostoHr / $days, 2) : "-";
      $pDay['costoKm'] = ($days > 0) ? number_format($totCostoKm / $days, 2) : "-";
      $pDay['costo'] = ($days > 0) ? number_format(($totCostoHr + $totCostoKm) / $days, 2) : "-";
      $pDays[] = $pDay;

      return $pDays;
      }
     */

    public static function getHorasDeUso($machineId, $fromTimestamp, $toTimestamp, $interval = UN_DIA) {

        $reporte = array();
        $init = $fromTimestamp;
        $dao = new dbObject();

        $unit = new Unit($machineId);
        $costoKm = $unit->getCostoKm();
        $costoHr = $unit->getCostoHr();

        $mileageOffset = $unit->getMileageOffset();
        $hourOffset = $unit->getHourOffset();

        $count = 0;
        //$totKm = 0;
        //$totHr = 0;
        $totCostoKm = 0;
        $totCostoHr = 0;

        $q = sprintf("SELECT machine_id, DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),\"%%d/%%m/%%y\") as fecha, DAYOFWEEK(DATE(FROM_UNIXTIME(gps_timestamp))) as diasem, min(mileage) as kmI,max(mileage) as kmF, max(mileage)-min(mileage) as distance, 
                     min(hours) as hI, max(hours) as hF, max(hours) - min(hours) as use_hours
                FROM log_positions WHERE machine_id = %d AND gps_timestamp BETWEEN %d AND %d AND mileage >0 
                GROUP BY DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),\"%%d/%%m/%%y\")", $machineId, $fromTimestamp, $toTimestamp);

        $res = $dao->fetchQuery($q);
        $pDays = array();
        if (is_array($res) && count($res)) {
            foreach ($res as $i => $day) {
                $pDay['fecha'] = $day['fecha'];
                $pDay['diaSem'] = Util::getDiaSemana($day['diasem']);
                $pDay['hInicio'] = floatval($day['hI'] + $hourOffset);
                $pDay['hFin'] = floatval($day['hF'] + $hourOffset);
                $pDay['kmFin'] = floatval($day['kmF'] + $mileageOffset);
                $pDay['kmInicio'] = floatval($day['kmI'] + $mileageOffset);
                $pDay['km'] = floatval($day['distance']);
                $pDay['horas'] = floatval($day['use_hours']);
                $pDay['pct'] = (floatval($pDay['horas']) / 24) * 100;
                $pDay['costoHr'] = floatval($pDay['horas']) * $costoHr;
                $pDay['costoKm'] = floatval($pDay['km']) * $costoKm;
                $pDay['costo'] = floatval($pDay['costoHr']) + floatval($pDay['costoKm']);

                //$totKm += floatval($pDay['km']);
                //$totHr += floatval($pDay['horas']);
                $totCostoKm += $pDay['costoKm'];
                $totCostoHr += $pDay['costoHr'];

                $pDays[] = $pDay;
            }
        }

        // Totales
        $pDay['fecha'] = "Totales";
        $pDay['diaSem'] = "";
        $pDay['hInicio'] = "";
        $pDay['hFin'] = "<b>Hr</b>";
        $pDay['kmFin'] = "<b>Km</b>";
        $pDay['kmInicio'] = "";
        //$pDay['km'] = $totKm;
        
        $totKm = (count($pDays))?($pDays[count($pDays)-1]['kmFin'] - $pDays[0]['kmInicio']):0;
        $pDay['km'] = $totKm;
        
        $totHr = (count($pDays))?($pDays[count($pDays)-1]['hFin'] - $pDays[0]['hInicio']):0;
        $pDay['horas'] = $totHr;
        
        $pDay['pct'] = "<b>Costos</b>";
        $pDay['costoHr'] = $totCostoHr;
        $pDay['costoKm'] = $totCostoKm;
        $pDay['costo'] = $totCostoHr + $totCostoKm;
        $pDays[] = $pDay;

        // Promedios
        $days = round(($toTimestamp - $fromTimestamp) / 86400);

        $pDay['fecha'] = "Promedios";
        $pDay['diaSem'] = "";
        $pDay['hInicio'] = "";
        $pDay['hFin'] = "<b>Hr</b>";
        $pDay['kmFin'] = "<b>Km</b>";
        $pDay['kmInicio'] = "";
        $pDay['km'] = ($days > 0) ? $totKm / $days : 0;
        $pDay['horas'] = ($days > 0) ? $totHr / $days : 0;
        $pDay['pct'] = "<b>Costos</b>";

        $pDay['costoHr'] = ($days > 0) ? $totCostoHr / $days : 0;
        $pDay['costoKm'] = ($days > 0) ? $totCostoKm / $days : 0;
        $pDay['costo'] = ($days > 0) ? ($totCostoHr + $totCostoKm) / $days : 0;
        $pDays[] = $pDay;

        return $pDays;
    }

    public static function getHorasFlota($fromTimestamp, $toTimestamp, User $user) {


        $dao = new dbObject();
        $machineListQuery = sprintf("SELECT m.machine_id, CONCAT(m.machine_name,\" (\",m.matricula,\")\") as machine_name, IF(ISNULL(m.costo_km),0,m.costo_km) as costoKm, "
                . "IF(ISNULL(m.costo_hr),0,m.costo_hr) as costoHr "
                . "FROM machines m "
                . "WHERE id_grupo IN (%s)", $user->getGroupsString());
        $machineList = $dao->fetchQuery($machineListQuery);
        
        $totKm = 0;
        $totHr = 0;
        $totCostoHr = 0;
        $totCostoKm = 0;
        
        if (is_array($machineList) && count($machineList)) {
            foreach ($machineList as $machine) {
                $machineData = self::getHorasDeUso($machine['machine_id'], $fromTimestamp, $toTimestamp);

                if (is_array($machineData) && count($machineData) > 2) {
                    $totalesRow = count($machineData) - 2;
                    $pUnit['unidad'] = $machine['machine_name'];
                    $pUnit['unitId'] = $machine['machine_id'];

                    $pUnit['hInicio'] = $machineData[0]['hInicio'];
                    $pUnit['hFin'] = $machineData[$totalesRow - 1]['hFin'];
                    $pUnit['kmInicio'] = $machineData[0]['kmInicio'];
                    $pUnit['kmFin'] = $machineData[$totalesRow - 1]['kmFin'];

                    $pUnit['km'] = $pUnit['kmFin'] - $pUnit['kmInicio'];
                    $pUnit['horas'] = $pUnit['hFin'] - $pUnit['hInicio'];
                    $pUnit['pct'] = ($pUnit['horas'] / (($toTimestamp - $fromTimestamp) / 3600) * 100);

                    $pUnit['costoHr'] = floatval($pUnit['horas']) * floatval($machine['costoHr']);
                    $pUnit['costoKm'] = floatval($pUnit['km']) * floatval($machine['costoKm']);

                    $pUnit['costo'] = floatval($pUnit['costoHr']) + floatVal($pUnit['costoKm']);

                    $totKm += floatval($pUnit['km']);
                    $totHr += floatval($pUnit['horas']);
                    $totCostoKm += floatval($machine['costoKm']);
                    $totCostoHr += floatval($machine['costoHr']);

                    $pUnits[] = $pUnit;
                }
            }
        }
        return $pUnits;
    }

    public static function getHorasFlota_bk($fromTimestamp, $toTimestamp, User $user) {

        $machineConstraint = sprintf("SELECT machine_id FROM machines WHERE id_grupo IN (%s)", $user->getGroupsString());

        $dao = new dbObject();
        $q = sprintf("SELECT m.machine_id, CONCAT(m.machine_name,\" (\",m.matricula,\")\") as machine_name, IF(ISNULL(m.costo_km),0,m.costo_km) as costoKm, IF(ISNULL(m.costo_hr),0,m.costo_hr) as costoHr, DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),\"%%d/%%m/%%y\") as fecha, 
                    DAYOFWEEK(DATE(FROM_UNIXTIME(gps_timestamp))) as diasem, min(mileage) as kmI,max(mileage) as kmF, max(mileage)-min(mileage) as distance, 
                     min(hours) as hI, max(hours) as hF, max(hours) - min(hours) as use_hours, mileage_offset, hour_offset
                FROM log_positions l LEFT JOIN machines m ON l.machine_id = m.machine_id
                WHERE gps_timestamp BETWEEN %d AND %d and l.machine_id IN (%s) AND mileage > 0
                GROUP BY l.machine_id", $fromTimestamp, $toTimestamp, $machineConstraint);

        $res = $dao->fetchQuery($q);

        $totKm = 0;
        $totHr = 0;

        $totCostoKm = 0;
        $totCostoHr = 0;

        if (is_array($res) && count($res)) {

            foreach ($res as $i => $unitData) {
                $pUnit['unidad'] = $unitData['machine_name'];
                $pUnit['unitId'] = $unitData['machine_id'];
                $pUnit['hInicio'] = number_format($unitData['hI'] + $unitData['hour_offset'], 2);
                $pUnit['hFin'] = number_format($unitData['hF'] + $unitData['hour_offset'], 2);
                $pUnit['kmFin'] = number_format($unitData['kmF'] + $unitData['mileage_offset'], 2);
                $pUnit['kmInicio'] = number_format($unitData['kmI'] + $unitData['mileage_offset'], 2);
                $pUnit['km'] = number_format($unitData['distance'], 2);
                $pUnit['horas'] = number_format($unitData['use_hours'], 2);
                $pUnit['pct'] = number_format($unitData['use_hours'] / (($toTimestamp - $fromTimestamp) / 3600) * 100, 2) . " %";
                $pUnit['costoHr'] = number_format(floatval($unitData['use_hours']) * floatval($unitData['costoHr']), 2);
                $pUnit['costoKm'] = number_format(floatval($unitData['distance']) * floatval($unitData['costoKm']), 2);
                $pUnit['costo'] = number_format(floatval($unitData['distance']) * floatval($unitData['costoKm']) +
                        (floatval($unitData['use_hours']) * floatval($unitData['costoHr'])), 2);

                $totKm += floatval($pUnit['km']);
                $totHr += floatval($pUnit['horas']);
                $totCostoKm += floatval($unitData['costoKm']);
                $totCostoHr += floatval($unitData['costoHr']);

                $pUnits[] = $pUnit;
            }
        }

        // Totales
        $pUnit['unidad'] = "<b>Totales</b>";
        //$pUnit['diaSem'] = "";
        $pUnit['hInicio'] = "";
        $pUnit['hFin'] = "<b>Hr</b>";
        $pUnit['kmFin'] = "<b>Km</b>";
        $pUnit['kmInicio'] = "";
        $pUnit['km'] = number_format($totKm, 2);
        $pUnit['horas'] = number_format($totHr, 2);
        $pUnit['pct'] = "<b>Costos</b>";
        $pUnit['costoHr'] = number_format($totCostoHr, 2);
        $pUnit['costoKm'] = number_format($totCostoKm, 2);
        $pUnit['costo'] = number_format($totCostoHr + $totCostoKm, 2);
        $pUnits[] = $pUnit;

        /*
          // Promedios
          $days = round(($toTimestamp-$fromTimestamp)/86400);

          $pUnit['fecha'] = "Promedios";
          $pUnit['diaSem'] = "";
          $pUnit['hInicio'] = "";
          $pUnit['hFin'] = "<b>Hr</b>";
          $pUnit['kmFin'] = "<b>Km</b>";
          $pUnit['kmInicio'] = "";
          $pUnit['km'] = ($days>0)?number_format($totKm/$days,2):"-";
          $pUnit['horas'] = ($days>0)?number_format($totHr/$days,2):"-";
          $pUnit['pct'] = "<b>Costos</b>";

          $pUnit['costoHr'] = ($days > 0)? number_format($totCostoHr/$days,2):"-";
          $pUnit['costoKm'] =  ($days > 0)? number_format($totCostoKm/$days,2):"-";
          $pUnit['costo'] =  ($days > 0)? number_format(($totCostoHr + $totCostoKm)/$days,2):"-";
          $pUnits[] = $pUnit;
         */

        return $pUnits;
    }

    /*
      public static function getDetenciones($unitIds,$fromTimestamp,$toTimestamp,$limit=null,$offset=null){

      $detenciones = Array();
      $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
      $cursorConstraint = (!is_null($limit)) ? sprintf(" LIMIT %d,%d", $offset, $limit * 2) : "";
      $dao = new dbObject();
      // GTSLP = EVT 29
      $countQuery = sprintf("SELECT count(*) as total FROM log_position p "
      . "WHERE %s AND p.timestamp BETWEEN %d AND %d "
      . "AND message_type IN (\"+EVT\",\"+BVT\") AND original_type=29 "
      . "ORDER BY timestamp,log_id", $unitConstraint, $fromTimestamp, $toTimestamp);
      $countRes = $dao->fetchQuery($countQuery);
      $total = (is_array($countRes) && count($countRes)) ? floor($countRes[0]['total']) : 0;

      $dataQuery = sprintf("SELECT DATE_FORMAT(from_unixtime(p.timestamp),\"%%d/%%m/%%y\") as fecha, "
      . "DATE_FORMAT(from_unixtime(p.timestamp),\"%%H:%%i\") as hora, timestamp, lat, lng, "
      . "message_type, original_type, report_type, report_id FROM log_position p "
      . "WHERE %s AND p.timestamp BETWEEN %d AND %d "
      . "AND message_type IN (\"+EVT\",\"+BVT\") AND original_type=29 "
      . "ORDER BY timestamp,log_id %s", $unitConstraint, $fromTimestamp, $toTimestamp, $cursorConstraint);
      $dataRes = $dao->fetchQuery($dataQuery);

      $ans['total'] = $total;
      $ans['detenciones'] = $dataRes;
      return $ans;
      }
     */

    public static function getDetenciones($machineId, $fromTimestamp, $toTimestamp) {

        $viajes = array();
        //$unitConstraint = MachineManager::buildUnitConstraint($machineIds);
        //$cursorConstraint = (!is_null($limit)) ? sprintf(" LIMIT %d,%d", $offset, $limit * 2) : "";
        $dao = new dbObject();

        $q = sprintf("SELECT i.log_id, i.lat, i.lng, i.gps_timestamp,DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%d/%%m/%%y') AS fecha, 
                        DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%H:%%i') AS hora, i.event_id,  i.mileage, l.reference, p.nombre_poi
                        FROM f22.log_ignition i
                        LEFT JOIN locations l ON i.location_id = l.location_id
                        LEFT JOIN pois p ON p.id_poi = i.poi_id
                        WHERE machine_id = %d
                        AND gps_timestamp BETWEEN %d AND %d
                        AND event_id IN ('GTSTP','GTLSP','GTSTR')
                        ORDER BY gps_timestamp", $machineId, $fromTimestamp, $toTimestamp);

        $eventos = $dao->fetchQuery($q);
        return $eventos;
    }

    public static function getReporteDetallado($machineId, $fromTimestamp, $toTimestamp) {

        $dao = new dbObject();

        $q = sprintf("SELECT DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%d/%%m/%%y') AS fecha, DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%H:%%i') AS hora, 
                        lat,lng,speed as vel,motion_status, external_power FROM log_positions
                        WHERE machine_id = %d AND gps_timestamp BETWEEN %d AND %d ORDER BY gps_timestamp ;", $machineId, $fromTimestamp, $toTimestamp);

        $nodes = $dao->fetchQuery($q);
        return $nodes;
    }

    public static function getViajes($machineId, $fromTimestamp, $toTimestamp) {

        $viajes = array();
        //$unitConstraint = MachineManager::buildUnitConstraint($machineIds);
        //$cursorConstraint = (!is_null($limit)) ? sprintf(" LIMIT %d,%d", $offset, $limit * 2) : "";
        $dao = new dbObject();

        $q = sprintf("SELECT i.log_id, i.lat, i.lng, i.gps_timestamp,DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%d/%%m/%%y') AS fecha, 
                        DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%H:%%i') AS hora, i.event_id, i.ign_on_off_duration, i.mileage, l.reference, p.nombre_poi
                        FROM f22.log_ignition i
                        LEFT JOIN locations l ON i.location_id = l.location_id
                        LEFT JOIN pois p ON p.id_poi = i.poi_id
                        WHERE machine_id = %d
                        AND gps_timestamp BETWEEN %d AND %d
                        AND event_id IN ('GTIGN','GTIGF')
                        ORDER BY gps_timestamp", $machineId, $fromTimestamp, $toTimestamp);

        $eventos = $dao->fetchQuery($q);

        if (is_array($eventos) && count($eventos)) {
            $currentViaje = array();
            foreach ($eventos as $evento) {
                switch ($evento['event_id']) {
                    case "GTIGN":
                        $currentViaje['inicio'] = $evento;
                        break;
                    case "GTIGF":
                        if (isset($currentViaje['inicio'])) {
                            $currentViaje['fin'] = $evento;
                            // $currentViaje['dur'] = Util::getFormattedTimeDiff($evento['gps_timestamp'] - $currentViaje['inicio']['gps_timestamp'], true);
                            $viajes[] = $currentViaje;
                            $currentViaje = array();
                        }
                        break;
                }
            }
        }
        return $viajes;
    }

    public static function getParadas($machineId, $fromTimestamp, $toTimestamp) {

        $viajes = array();
        //$unitConstraint = MachineManager::buildUnitConstraint($machineIds);
        //$cursorConstraint = (!is_null($limit)) ? sprintf(" LIMIT %d,%d", $offset, $limit * 2) : "";
        $dao = new dbObject();

        $q = sprintf("SELECT i.log_id, i.lat, i.lng, i.gps_timestamp,DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%d/%%m/%%y') AS fecha, 
                        DATE_FORMAT(FROM_UNIXTIME(gps_timestamp),'%%H:%%i') AS hora, i.event_id, i.ign_on_off_duration, i.mileage, l.reference, p.nombre_poi
                        FROM f22.log_ignition i
                        LEFT JOIN locations l ON i.location_id = l.location_id
                        LEFT JOIN pois p ON p.id_poi = i.poi_id
                        WHERE machine_id = %d
                        AND gps_timestamp BETWEEN %d AND %d
                        AND event_id IN ('GTIGN')
                        ORDER BY gps_timestamp", $machineId, $fromTimestamp, $toTimestamp);

        $eventos = $dao->fetchQuery($q);

        return $eventos;
    }

    /*
      public static function getViajes($unitIds, $fromTimestamp, $toTimestamp, $limit = null, $offset = null) {

      $viajes = Array();
      $unitConstraint = MachineManager::buildUnitConstraint($unitIds);
      $cursorConstraint = (!is_null($limit)) ? sprintf(" LIMIT %d,%d", $offset, $limit * 2) : "";
      $dao = new dbObject();


      $countQuery = sprintf("SELECT count(*) as total FROM log_position p WHERE %s AND p.timestamp BETWEEN %d AND %d AND (message_type =\"+RSP\" OR message_type =\"+BSP\")  AND original_type=16 ORDER BY timestamp,log_id", $unitConstraint, $fromTimestamp, $toTimestamp);
      $countRes = $dao->fetchQuery($countQuery);
      $total = (is_array($countRes) && count($countRes)) ? floor($countRes[0]['total'] / 2) : 0;

      $dataQuery = sprintf("SELECT DATE_FORMAT(from_unixtime(p.timestamp),\"%%d/%%m/%%y\") as fecha, DATE_FORMAT(from_unixtime(p.timestamp),\"%%H:%%i\") as hora, timestamp,total_hours,total_mileage, lat, lng,  message_type, original_type, report_type, report_id FROM log_position p WHERE %s AND p.timestamp BETWEEN %d AND %d AND (message_type =\"+RSP\" OR message_type =\"+BSP\") AND original_type=16 ORDER BY timestamp,log_id %s", $unitConstraint, $fromTimestamp, $toTimestamp, $cursorConstraint);
      $dataRes = $dao->fetchQuery($dataQuery);

      if (is_array($dataRes) && count($dataRes)) {

      $lastTimestamp = $dataRes[0]['timestamp'];
      $lastLat = $dataRes[0]['lat'];
      $lastLng = $dataRes[0]['lng'];
      ;
      $lastMileage = $dataRes[0]['total_mileage'];

      $stopped = true;

      $currentTimestamp = null;
      $currentLat = null;
      $currentLng = null;
      $currentMileage = null;

      $lastPotentiallyQuietTimestamp = null;

      $estabaApagado = false;
      $estabaEncendido = false;

      foreach ($dataRes as $i => $evento) {

      if ($evento['report_type'] == ENCENDIDO_MOTOR) {
      //if ($estabaApagado) {
      $viaje['stTimestamp'] = $evento['timestamp'];
      $stOdometer = $evento['total_mileage'];
      $stHr = $evento['total_hours'];
      $viaje['stFecha'] = $evento['fecha'];
      $viaje['stHora'] = $evento['hora'];
      $viaje['stKm'] = $evento['fecha'];
      $viaje['stLat'] = $evento['lat'];
      $viaje['stLng'] = $evento['lng'];
      //}
      $estabaApagado = false;
      $estabaEncendido = true;
      }

      if ($evento['report_type'] == APAGADO_MOTOR) {
      if ($estabaEncendido) {
      $viaje['endFecha'] = $evento['fecha'];
      $viaje['endTimestamp'] = $evento['timestamp'];
      $viaje['duracion'] = Util::getFormattedTimeDiff($evento['timestamp'] - $viaje['stTimestamp'], true);
      $viaje['distancia'] = number_format($evento['total_mileage'] - $stOdometer, 1, ",", ".");
      $viaje['endLat'] = $evento['lat'];
      $viaje['endLng'] = $evento['lng'];
      $viaje['endKm'] = $evento['total_mileage'];

      $viaje['velMedia'] = ($evento['timestamp'] > $viaje['stTimestamp']) ? number_format(($evento['total_mileage'] - $stOdometer) / (($evento['timestamp'] - $viaje['stTimestamp']) / 3600), 1, ",", ".") . " Km/h" : "-";
      $viajes[] = $viaje;
      }
      $estabaApagado = true;
      $estabaEncendido = false;
      }
      }
      }

      $ans['total'] = $total;
      $ans['viajes'] = $viajes;
      return $ans;
      }
     */

    public static function getPositionReferencedLog($unitIds, $fromTimestamp, $toTimestamp) {

        $referencedLog = array();

        $logPoints = MachineManager::getPositionLog($unitIds, $fromTimestamp, $toTimestamp, true);
        if (is_array($logPoints) && count($logPoints)) {

            $j = 0;
            foreach ($logPoints as $i => $logPoint) {

                if ($i == 0) {
                    $previousLat = $logPoint['lat'];
                    $previousLng = $logPoint['lng'];
                    $previousSpd = $logPoint['speed'];
                    $previousHdg = $logPoint['heading'];
                    $previousStt = $logPoint['motion_status'];
                    $previousTim = $logPoint['timestamp'];
                }

                $currentLat = $logPoint['lat'];
                $currentLng = $logPoint['lng'];
                $currentSpd = $logPoint['speed'];
                $currentHdg = $logPoint['heading'];
                $currentStt = $logPoint['motion_status'];
                $currentTim = $logPoint['timestamp'];

                if ($i == 0 || $i == (count($logPoints) - 1) || ($currentStt != $previousStt) || (GeoUtils::getTheDistance($currentLat, $currentLng, $previousLat, $previousLng) > 10000) || ($currentSpd < 10 and $previousSpd >= 10) || (abs($currentHdg - $previousHdg) > 50) || ($currentTim - $previousTim > 86400)) {

                    $ref = MachineManager::getReference($logPoint['lat'], $logPoint['lng']);
                    $calles = "";
                    if (is_array($ref["street"]) && count($ref["street"])) {
                        $streets = $ref["street"];
                        foreach ($streets as $calle) {
                            $calles .= ($calles) ? " y " : "";
                            $calles .= $calle;
                        }
                    }
                    $calles = sprintf("%s (%s)", $calles, $ref['city']);

                    $referencedLog[$j] = $logPoint;
                    $referencedLog[$j]['calles'] = $calles;
                    $referencedLog[$j]['originalIndex'] = $i;
                    $referencedLog[$j]['dia'] = date("d/m/Y", $logPoint['timestamp']);
                    $referencedLog[$j]['hora'] = date("H:i", $logPoint['timestamp']);

                    $previousLat = $currentLat;
                    $previousLng = $currentLng;
                    $previousSpd = $currentSpd;
                    $previousHdg = $currentHdg;
                    $previousStt = $currentStt;
                    $previousTim = $currentTim;

                    $j++;
                }
            }
        }
        return $referencedLog;
        ECHO "<PRE>";
        //print_r($referencedLog);
        //die("k");
    }

    public static function getReference($lat, $lng) {

        $reference = Array();
        $dao = new dbObject ();
        $q = sprintf("SELECT node_id FROM ref_routing_nodes
		ORDER BY (POW((lat-(%s)),2)+POW((lng-(%s)),2))
		LIMIT 0,1;", $lat, $lng);
        //echo $q."\n";
        $res1 = $dao->fetchQuery($q);
        if (is_array($res1) && count($res1)) {
            //echo $res1[0]['node_id'];
            $p = sprintf("SELECT s.street_name, c.city_name
			FROM ref_street_nodes n
			LEFT JOIN ref_streets s ON n.street_id = s.street_id
			LEFT JOIN ref_cities c ON s.city_id = c.city_id
			WHERE node_id = %d", $res1 [0] ['node_id']);
            //echo $p."\n";
            $res2 = $dao->fetchQuery($p);
            if (is_array($res2) && count($res2)) {

                $reference ["city"] = $res2 [0] ['city_name'];
                foreach ($res2 as $i => $street) {
                    $reference ["street"] [$i] = $street ['street_name'];
                }
            }
            //echo "<pre>";
            //print_r($reference);
            return $reference;
        }
    }

    public static function buildUnitConstraint($machineIds) {

        $constraint = "false";
        if (is_array($machineIds)) {
            if (strlen($machineIds)) {
                $machineList = "";
                foreach ($machineIds as $machineId) {
                    $machineList .= ($machineList == "") ? "" : ",";
                    $machineList .= $machineId;
                }
                $constraint = "machine_id IN (%s)";
            }
        } else {
            $constraint = sprintf("unit_id = '%s'", $machineIds);
        }
        return $constraint;
    }

    public static function buildTimeConstraint($from, $to, $adjust = true) {
        $adjFrom = ($adjust) ? MachineManager::getAdjustedTimestamp($from) : $from;
        $adjTo = ($adjust) ? MachineManager::getAdjustedTimestamp($to) : $to;
        $constraint = sprintf("timestamp BETWEEN %d AND %d", $adjFrom, $adjTo);
        return $constraint;
    }

    public static function getAdjustedTimestamp($aTimestamp, $offset = null) {
        if (is_null($offset))
            $offset = SYS_TIMEZONE;
        //$offset = 0;
        return ($aTimestamp + ($offset * 3600));
    }

    private static function getFilter($unitIds) {

        if (is_array($unitIds) && count($unitIds)) {
            foreach ($unitIds as $unitId) {
                //$filter ="";
                $filter .= ($filter != "") ? ", " : "";
                $filter .= $unitId;
            }
            $filter = sprintf("(%s)", $filter);
        } else {
            $filter = sprintf("(%s)", $unitIds);
        }
        return $filter;
    }

    public static function getFleetKmTableData($user, $initTs, $endTs) {


        $units = MachineManager::getUnits($user);

        $q1 = "SELECT date(from_unixtime(timestamp)),total_mileage FROM log_position WHERE unit_id = '%s' AND timestamp >= %d  ORDER BY timestamp LIMIT 0,1";
        $q2 = "SELECT date(from_unixtime(timestamp)),total_mileage FROM log_position WHERE unit_id = '%s' AND timestamp < %d ORDER BY timestamp DESC LIMIT 0,1";
        $dao = new dbObject();

        $kmTable = array();
        $lastEnd = "-";

        if (is_array($units) && count($units)) {
            foreach ($units as $i => $unit) {
                $query1 = sprintf($q1, $unit['unit_id'], $initTs);
                $rs1 = $dao->fetchQuery($query1);
                $query2 = sprintf($q2, $unit['unit_id'], $endTs);
                $rs2 = $dao->fetchQuery($query2);

                $initDate = gmdate("d/m/Y", $initTs);
                $endDate = gmdate("d/m/Y", $endTs);

                $kmTable[$i]['unit'] = $unit['nombre_comun'];
                $kmTable[$i]['init'] = (is_array($rs1) && count($rs1)) ? $rs1[0]['total_mileage'] : "-";
                $kmTable[$i]['end'] = (is_array($rs2) && count($rs2)) ? $rs2[0]['total_mileage'] : "-";
            }
        }

        return $kmTable;
    }

}

?>