<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

/**
 * Eye View Design CMS module Ajax Model
 *
 * PHP version 5
 *
 * @category  CodeIgniter
 * @package   EVD CMS
 * @author    Frederico Carvalho
 * @copyright 2008 Mentes 100Limites
 * @version   0.1
 */
class Summeryreport_model extends Model {

    public function stopreport_model() {
        parent::Model();
        $this->CI = & get_instance();
        $this->load->database();
    }

    public function get_distance($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }

        $SQL = "SELECT SUM(distance) as distance from tbl_start_report WHERE device_id='" . $device . "' AND  add_date BETWEEN '" . $sdate . "' AND '" . $edate . "'";
        //die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function get_asset_summery($device) {
        $SQL = "select id,assets_name,device_desc,asset_model,asset_brand,fuel_type,asset_year,asset_sieges,fuel_consumption,carbon_emission,gasoline_costs FROM assests_master WHERE id='" . $device . "'";
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function get_max_avg_speed($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }
        $SQL = "SELECT max(speed) as maxspeed,avg(speed) as avgspeed FROM tbl_track where assets_id='" . $device . "' AND CONVERT_TZ(add_date,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "'";
        // die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function get_departure_address($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }
        $SQL = "SELECT ignition_on_address as address FROM tbl_start_report WHERE ignition_on IS NOT NULL AND device_id='" . $device . "' AND CONVERT_TZ(ignition_on,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "' ORDER BY ignition_on ASC LIMIT 1";
        //die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function get_arrival_address($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }
        $SQL = "SELECT ignition_off_address as address FROM tbl_start_report WHERE ignition_off IS NOT NULL AND device_id='" . $device . "' AND CONVERT_TZ(ignition_off,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "' ORDER BY ignition_off DESC LIMIT 1";
        //die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function driving_time($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }
        $SQL = "SELECT sec_to_time(sum(time_to_sec(duration))) as duration FROM tbl_start_report WHERE device_id='" . $device . "' AND CONVERT_TZ(add_date,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "' ";
        //die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function time_off($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }
        $SQL = "SELECT sec_to_time(sum(time_to_sec(duration))) as totalduration FROM tbl_stop_report WHERE device_id='" . $device . "' AND CONVERT_TZ(ignition_off,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "' AND ignition_status='Ignition Off'";
        //die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function get_rides($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }
        //$SQL = "SELECT count(1) AS num_of_ride FROM tbl_start_report WHERE time(duration) > '00:15:00'  AND ignition_off IS NOT NULL AND device_id='" . $device . "' AND CONVERT_TZ(add_date,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "'";
       
        $SQL = "SELECT count(1) AS num_of_ride FROM tbl_start_report WHERE ignition_off_address IS NOT NULL AND device_id='" . $device . "' AND CONVERT_TZ(add_date,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "'";
        //die($SQL);
        $query = $this->db->query($SQL);
        return $query->result();
    }

    public function get_all_rides_report($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }

        $SQL = "SELECT ignition_on,if(ignition_off_address is not null,ignition_off,false) as ignition_off,duration,distance,ignition_on_address,ignition_off_address,max_speed,avg_speed FROM tbl_start_report WHERE device_id='" . $device . "' AND CONVERT_TZ(add_date,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "' ";
       // $SQL.= "LIMIT 5,20";

        $query = $this->db->query($SQL);
        return $query->result_array();
    }
    
   public function assetDetail($device)
   {
   		$SQL = "SELECT driver_name FROM assests_master WHERE id='" . $device . "' ";
   		$query = $this->db->query($SQL);
   		return $query->result_array();
   }
    
    public function get_all_rides_report_count($sdate, $edate, $device) {
        if ($sdate && $edate) {
            $sdate = date("Y-m-d H:i:s", strtotime($sdate));
            $edate = date("Y-m-d H:i:s", strtotime($edate));
        } else {
            $sdate = date("Y-m-d H:i:s");
            $edate = date("Y-m-d H:i:s");
        }

        $SQL = "SELECT count(*) FROM tbl_start_report WHERE device_id='" . $device . "' AND CONVERT_TZ(add_date,'+00:00','" . $this->session->userdata('timezone') . "') BETWEEN '" . $sdate . "' AND '" . $edate . "' ";
        $query = $this->db->query($SQL);
        return $query->result();
    }

}

?>