<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
function format_interval(DateInterval $interval) {
    $result = "";
    if ($interval->y) { $result .= $interval->format("%y years "); }
    if ($interval->m) { $result .= $interval->format("%m months "); }
    if ($interval->d) { $result .= $interval->format("%d days "); }
    if ($interval->h) { $result .= $interval->format("%h hr "); }
    if ($interval->i) { $result .= $interval->format("%i min "); }
	if (!$interval->i) {
		if ($interval->s) { $result .= "1 min"; }
	}
    return $result;
}
function round_in_month($tagid,$curdate,$from,$to){
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$sql = "SELECT count( * ) as totalround FROM `log` WHERE";
	if(($from == "novalue") && ($to == "novalue")){
		$sql .= " DATE_FORMAT( log.time_in, '%m-%Y' ) = '".$curdate."'";
	}
	else{
		$sql .= " DATE_FORMAT( log.time_in, '%Y-%m-%d' ) BETWEEN '".$from."' and '".$to."'"; 
	}
	$sql .= " AND `tag_id` = '".$tagid."'";
	$query = $ci->db->query($sql);
	//echo $sql;
	if($query->num_rows() > 0){
        $sqlquery = $query->row_array();
        return $sqlquery;
    }else{
        return false;
    }
 }
 
 function bus_route($vehicle_id,$change_date){
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$sql = "SELECT * FROM `tbl_route` WHERE  vehicle_id = '".$vehicle_id."'";
	$query1 = $ci->db->query($sql);
	if($query1->num_rows() > 0){
		$sql = "SELECT * FROM `tbl_route` WHERE  vehicle_id = '".$vehicle_id."' and change_date < '".$change_date."'";
		$query = $ci->db->query($sql);
		if($query->num_rows() > 0){
			$sqlquery = $query->row_array();
			return $sqlquery;
		}
		else{
			$sqlquery = $query1->row_array();
			$change = "till";
			return array($sqlquery, $change);
		}
    }else{
		$noresult = "noresult";
        return $noresult;
    }
 }
 
function login_details() {
	$CI = & get_instance();  //get instance, access the CI superobject
	$LoggedInId = $CI->session->userdata('adminid');
	$LoggedInName = $CI->session->userdata('loginname');
	return array($LoggedInId, $LoggedInName);
}
function Admin_details($admin_id) {
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$sql = "SELECT name FROM `tbl_admin` WHERE admin_id = '".$admin_id."'";
	$query = $ci->db->query($sql);
	return $query->row_array();	
}
function Log_For_Vehicle($VehicleId) {
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$sql = "SELECT vehicle_number FROM `tbl_vehicle_master` WHERE vehicle_id = '".$VehicleId."'";
	$query = $ci->db->query($sql);
	return $query->row_array();	
}
function Log_For_Penalty($PenaltyId) {
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	//$sql = "SELECT vehicle_number FROM `tbl_penalty` WHERE id = '".$PenaltyId."'";
	//$query = $ci->db->query($sql);
	//return $query->row_array();	
}
function Penalty_Details($curdate,$from,$to,$VehicleId) {
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	//$curdate = date('m-Y');
	$sql = "SELECT sum(amount) as totalpenalty FROM tbl_penalty where";
	if(($from == "novalue") && ($to == "novalue")){
		$sql .= " DATE_FORMAT( pdate, '%m-%Y' ) = '".$curdate."'";
	}
	else{
		$sql .= " DATE_FORMAT( pdate, '%Y-%m-%d' ) BETWEEN '".$from."' and '".$to."'"; 
	}
	$sql .= " AND vehicle_id = '".$VehicleId."'";
	$query = $ci->db->query($sql);
	return $query->row_array();	
}

function Charges_Details() {
    $ci =& get_instance();
    $ci->load->database();
	$curyear = date('Y');
	$curmonth = date('m');
	$sql = "SELECT round FROM tbl_monthly_round where year = '".$curyear."' and month= '".$curmonth."'";
	$query = $ci->db->query($sql);
	return $query->row_array();	
}

function NotifyList($notification_for,$expiry_date) {
	
	if($notification_for == 'Insurance'){
		$expiry_type= 'insurance_expiry_date';
	}
	else{
		$expiry_type = 'pollution_expiry_date';
	}
	$date = date("Y-m-d");
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$sql = "SELECT vehicle_number, vehicle_type FROM `tbl_vehicle_master` where $expiry_type BETWEEN '".$expiry_date."' and '".$date."'";
	$query = $ci->db->query($sql);
	return $query->result();	
}

function Ind_Relationship($tagid) {
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$query = $ci->db->query("select tag, tag_id, ntag, ntag_id from tbl_relationship where ntag_id='".$tagid."'");
	$fatchrow = $query->row_array();
	$tag_id =  $fatchrow['tag_id'];
	$tag =  $fatchrow['tag'];
	
	$sqlquery = "SELECT tag_id, time_in, time_out FROM log WHERE tag_id = '".$tag_id."'";
	$queryresult = $ci->db->query($sqlquery);	
	return array($tag, $queryresult->result());
}
function Relationship($tagid,$curdate,$fromdate,$todate) {
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$query = $ci->db->query("select tag, tag_id, ntag, ntag_id from tbl_relationship where ntag_id='".$tagid."'");
	$fatchrow = $query->row_array();
	$tag_id =  $fatchrow['tag_id'];
	$tag =  $fatchrow['tag'];
	
	
	$sqlquery = "SELECT tag_id, time_in, time_out FROM log WHERE tag_id = '".$tag_id."' AND DATE_FORMAT( time_in, '%Y-%m-%d' ) BETWEEN '".$fromdate."' and '".$todate."'";
	//if($fromdate == '2016-01-21'){
		//$sqlquery .= " and tag_id != 'E2004106260D01960480E6B2'";
	//}
	$sqlquery .=" GROUP BY tag_id";
	//echo $sqlquery."<br>";
	$queryresult = $ci->db->query($sqlquery);	
	return array($tag, $queryresult->result());
}

function TagRelationship($tagid,$fromdate,$todate,$vehicletype,$total){
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$query = $ci->db->query("select tag, tag_id, ntag, ntag_id from tbl_relationship where ntag_id='".$tagid."'");
	$fatchResult = $query->result();
	$totalrow = $query->num_rows();
	if($totalrow != "0"){
		$i = $total;
		foreach($fatchResult as $fatchRow){
			$i++;
			$curdate = date('m-Y');
			$sql = "SELECT tag_id, time_in, time_out FROM log WHERE tag_id = '".$fatchRow->tag_id."'";
			if((trim($fromdate) != "") && (trim($todate) != ""))
			{
				$sql .= " AND DATE_FORMAT(time_in, '%Y-%m-%d') BETWEEN '".trim($fromdate)."' and '".trim($todate)."'"; 
			}
			else
			{
				$sql .= " AND DATE_FORMAT( time_in, '%m-%Y' ) = '".$curdate."'";
			}
			$query = $ci->db->query($sql);
			$result = $query->result();	
			$i = $total;
			foreach($result as $datarow)
			{
				$i++;
				$query = $ci->db->query("select * from tbl_vehicle_master where tag_id = '".$tagid."'");
				$row = $query->row_array();
				?>
				<tr>
					<td><?php echo $i;?></td>
					<td><?php echo $fatchRow->tag; ?></td>
					<td><?php echo $datarow->time_in; ?></td>
					<td>
						<?php 
						if($datarow->time_out == '2010-01-01 01:01:01'){
							echo "AUTU OUT";
						} else {
							echo $datarow->time_out;
						} 
						?>
					</td>
					<td>
						<a href="<?php echo base_url(); ?>index.php/Administrator/VehicleDetails/<?php echo $proid; ?>" title="View" class="ajax"><?php echo $row['vehicle_number']; ?></a>
					</td>
					<td><?php echo $row['route_no']; ?></td>
					<td><?php echo $row['route_name']; ?></td>
				</tr>
				<?php
			}
		}	
	}
}

function TagRelationshipCab($tagid,$fromdate,$todate,$vehicletype,$total){
	//get main CodeIgniter object
    $ci =& get_instance();
    //load databse library
    $ci->load->database();
	$query = $ci->db->query("select tag, tag_id, ntag, ntag_id from tbl_relationship where ntag_id='".$tagid."'");
	$fatchResult = $query->result();
	$totalrow = $query->num_rows();
	if($totalrow != "0"){
		$i = $total;
		foreach($fatchResult as $fatchRow){
			$i++;
			$curdate = date('m-Y');
			$sql = "SELECT tag_id, time_in, time_out FROM log WHERE tag_id = '".$fatchRow->tag_id."'";
			if((trim($fromdate) != "") && (trim($todate) != ""))
			{
				$sql .= " AND DATE_FORMAT(time_in, '%Y-%m-%d') BETWEEN '".trim($fromdate)."' and '".trim($todate)."'"; 
			}
			else
			{
				$sql .= " AND DATE_FORMAT( time_in, '%m-%Y' ) = '".$curdate."'";
			}
			$query = $ci->db->query($sql);
			$result = $query->result();	
			$i = $total;
			foreach($result as $datarow)
			{
				$i++;
				$query = $ci->db->query("select * from tbl_vehicle_master where tag_id = '".$tagid."'");
				$row = $query->row_array();
				?>
				<tr>
					<td><?php echo $i;?></td>
					<td><?php echo $fatchRow->tag; ?></td>
					<td><?php echo $datarow->time_in; ?></td>
					<td>
						<?php 
						if($datarow->time_out == '2010-01-01 01:01:01'){
							echo "AUTU OUT";
						} else {
							echo $datarow->time_out;
						} 
						?>
					</td>
					<td>
						<?php
						$time_in = new DateTime($datarow->time_in);
						$time_out = new DateTime($datarow->time_out);
						$difference = $time_in->diff($time_out);
						if($datarow->time_out == ''){	
							echo " ";	
						}
						else if($datarow->time_out == '2010-01-01 01:01:01'){	
							echo "--";	
						}
						else{	
							echo format_interval($difference);	
						}
						?>
					</td>
					<td>
						<a href="<?php echo base_url(); ?>index.php/Administrator/VehicleDetails/<?php echo $proid; ?>" title="View" class="ajax"><?php echo $row['vehicle_number']; ?></a>
					</td>
					<td><?php echo $row['route_no']; ?></td>
					<td><?php echo $row['route_name']; ?></td>
				</tr>
				<?php
			}
		}	
	}
}

function BusRoute()
	{
		$ci =& get_instance();
		$ci->load->database();
	
		$sql = "SELECT DISTINCT route_no FROM tbl_vehicle_master where route_no != '0' order by route_no asc";
		//echo $sql;
		$query = $ci->db->query($sql);
		return $query->result();				
	}

function BusReportRoute($vehicleid)
	{
		$ci =& get_instance();
		$ci->load->database();
		$curdate = date('m-Y');
	
		$sql = "SELECT log.id, log.tag_id, log.time_in, log.time_out, tbl_vehicle_master.vehicle_id, tbl_vehicle_master.tag_no, tbl_vehicle_master.tag_id, tbl_vehicle_master.alias_tag, tbl_vehicle_master.vehicle_number, tbl_vehicle_master.insurance_expiry_date, tbl_vehicle_master.pollution_expiry_date, tbl_vehicle_master.charges, tbl_vehicle_master.route_no, tbl_vehicle_master.route_name, tbl_vehicle_master.zone, tbl_vehicle_master.fuel_type, tbl_vehicle_master.km_to_fro, tbl_vehicle_master.seating_capacity, tbl_vehicle_master.acutal_seating_capacity, tbl_vehicle_master.cab_type, tbl_vehicle_master.vender, tbl_vehicle_master.Vehicle_model, tbl_vehicle_master.registration_date, tbl_vehicle_master.fitness, tbl_vehicle_master.permit, tbl_vehicle_master.driver_name, tbl_vehicle_master.contact_number, tbl_vehicle_master.license_number, tbl_vehicle_master.status
		FROM log, tbl_vehicle_master
		WHERE (log.tag_id = tbl_vehicle_master.tag_id or log.tag_id = tbl_vehicle_master.alias_tag)
		AND tbl_vehicle_master.vehicle_type = 'Bus'
		AND DATE_FORMAT( log.time_in, '%m-%Y' ) = '02-2016'
		AND tbl_vehicle_master.vehicle_id = '".$vehicleid."' GROUP BY  log.tag_id";
		//echo $sql;
		$query = $ci->db->query($sql);
		return $query->result();				
	}