<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class Department extends CI_Controller {

	public function __construct()
	{
		parent::__construct();
		$this->load->library('excel');
		$this->load->helper(array('form', 'url','string'));
	}

	public function index() 
	{
		// Total No of  registrations in a batch
		$query = $this->db
              ->select('BatchID, count(RegdNo) AS num_of_time')
              ->group_by('BatchID')
              ->get('pc_training');
		$data['batch_wise_count']=$query->result_array();
		$query = $this->db
              ->select('result, count(id) AS q_data')
              ->group_by('result')
			  ->order_by("result", "desc")
              ->get('pc_training');
		$data['qualified_count']=$query->result_array();
		//select members
		$query = $this->db
		->select('Selected, count(id) AS total_data')
		->group_by('Selected')
		->order_by("Selected", "desc")
		->get('pc_training');
 		 $data['slected_data']=$query->result_array(); 
		$sql="SELECT * FROM `pc_training` ORDER BY `pc_training`.`time_taken` ASC LIMIT 10";
		$query = $this->db->query($sql);
		$data['top_ten']= $query->result();
		$this->load->view('dp_dashboard',$data);
	}
	public function insertFile(){
		if(isset($_FILES["file"]["name"]))
		{
			
			$path = $_FILES["file"]["tmp_name"];
			$object = PHPExcel_IOFactory::load($path);
			foreach($object->getWorksheetIterator() as $worksheet)
			{
				$highestRow = $worksheet->getHighestRow();
				$highestColumn = $worksheet->getHighestColumn();
				for($row=2; $row<=$highestRow; $row++)
				{
						if($worksheet->getCellByColumnAndRow(0, $row)->getValue() !=""){ 
							$data[]=array(
								"RegdNo"=> $worksheet->getCellByColumnAndRow(0, $row)->getValue(),
								"CName"=>$worksheet->getCellByColumnAndRow(1, $row)->getValue(),
								"gender"=>$worksheet->getCellByColumnAndRow(2, $row)->getValue(),
								"PostApplied"=>$worksheet->getCellByColumnAndRow(3, $row)->getValue(),
								"BatchID"=>$worksheet->getCellByColumnAndRow(4, $row)->getValue(),
								"EXM"=>$worksheet->getCellByColumnAndRow(5, $row)->getValue(),
								"BibNo"=>$worksheet->getCellByColumnAndRow(6, $row)->getValue(),
								"StartTime"=>$worksheet->getCellByColumnAndRow(7, $row)->getValue(),
								"EndTIme"=>$worksheet->getCellByColumnAndRow(8, $row)->getValue(),
								"NetTime"=>$worksheet->getCellByColumnAndRow(9, $row)->getValue(),
								"PETResult"=>$worksheet->getCellByColumnAndRow(10, $row)->getValue(),
								"runners"=>$worksheet->getCellByColumnAndRow(11, $row)->getValue(),
								"result"=>$worksheet->getCellByColumnAndRow(12, $row)->getValue(),
								"time_taken"=>$worksheet->getCellByColumnAndRow(13, $row)->getValue(),
								"jacket_number"=>$worksheet->getCellByColumnAndRow(14, $row)->getValue(),
								"stime"=>$worksheet->getCellByColumnAndRow(15, $row)->getValue(),
								"etime"=>$worksheet->getCellByColumnAndRow(16, $row)->getValue(),
								"MsStart"=>$worksheet->getCellByColumnAndRow(17, $row)->getValue(),
								"MsEnd"=>$worksheet->getCellByColumnAndRow(18, $row)->getValue(),
								"LapsCovered"=>$worksheet->getCellByColumnAndRow(19, $row)->getValue(),
								"status"=>$worksheet->getCellByColumnAndRow(29, $row)->getValue(),
								"report"=>$worksheet->getCellByColumnAndRow(21, $row)->getValue(),
								"respost"=>$worksheet->getCellByColumnAndRow(22, $row)->getValue(),
								"conspost"=>$worksheet->getCellByColumnAndRow(23, $row)->getValue(),
								"LAP1"=>$worksheet->getCellByColumnAndRow(24, $row)->getValue(),
								"LAP1MS"=>$worksheet->getCellByColumnAndRow(25, $row)->getValue(),
								"LAP2"=>$worksheet->getCellByColumnAndRow(26, $row)->getValue(),
								"LAP2MS"=>$worksheet->getCellByColumnAndRow(27, $row)->getValue(),
								"LAP3"=>$worksheet->getCellByColumnAndRow(28, $row)->getValue(),
								"LAP3MS"=>$worksheet->getCellByColumnAndRow(29, $row)->getValue(),
								"LAP4"=>$worksheet->getCellByColumnAndRow(30, $row)->getValue(),
								"LAP4MS"=>$worksheet->getCellByColumnAndRow(31, $row)->getValue(),
								"Selected"=>$worksheet->getCellByColumnAndRow(32, $row)->getValue()
							);
						}
					
				}
			$this->db->insert_batch('pc_training', $data);
			$this->session->set_flashdata('successmessage','successfully generate graphs');
			redirect('department');
			//echo $this->db->last_query();
		}	
		}
	}
	public function ViewData(){
		$id = $this->uri->segment(3);		
		$this->db->select('*');
		$this->db->from('pc_training');
		$batch_data=substr($id,0,5);
		if($batch_data == "Batch"){ 
			$BatchID=substr($id,6);			
			$this->db->where('BatchID', $BatchID);
			$data['lable']="Batch".$BatchID; 
		}elseif($id == "Qualified"){
			//$result=substr($id,10);
			$result="Q";
			$this->db->where('result', $result );
			 $data['lable']="Qualified Data";
		}elseif($id == "Not%20Qualified"){
		 //  $result=substr($id,16);
		   $result="NQ";
		   $this->db->where('result', $result );
		    $data['lable']="Not Qualified Data";
		}elseif($id == "Selected"){
			$result="YES";
			$data['lable']="Selected";
			$this->db->where('Selected', $result );
		}elseif($id == "Not%20Selected"){
			$result="NO";
			$data['lable']="Not Selected"; 
			$this->db->where('Selected', $result );
		}else{
			 $id=$id;
			 $this->db->where('id', $id );
			 $data['lable']="Top Ten";
		}
		$query = $this->db->get();
		$data['result_data']=$query->result_array();
		$this->load->view('view_data',$data); 
	}
	
	public function department_data_insert(){
		$this->load->helper('url');
		$currentURL = current_url();
		$params   = $_SERVER['QUERY_STRING'];
		//print_r($params);
		//exit();
		$qs = str_replace('$', '', $_SERVER['QUERY_STRING']); // get rid of the $
			$qs = str_replace('*', '', $qs); // get rid of the *
			
			$submissions = explode(',', $qs); // split the subs
			
			$SID = ""; // store for sid
			$MID = ""; // store for mid

			// loop
			for ($i = 0; $i < count($submissions); $i++) {
				$sections = explode('&', $submissions[$i]);
				
				
				if($i == 0) {
					$SID = $sections[0];
					$MID = $sections[1];
					$RFID = $sections[2];
					$DOT = $sections[3];
				} else {
					$RFID = $sections[0];
					$DOT = $sections[1];
				
				}
				
			//Creates new record as per request
				//Connect to database
			
				$string = $DOT;

				$day = substr($string, 0, 2);
				$month = substr($string, 2, 2);
				$year = substr($string, 4, 4);

				$hour = substr($string, 8, 2);
				$min = substr($string, 10, 2);
				$sec = substr($string, 12, 2);

				$result_date = $day.'-'.$month.'-'.$year.' '.$hour.':'.$min.':'.$sec;

				//echo $result;
			
				//$sql="insert into silicondemo values('".$SID."','".$MID."','".$RFID."','".$result_date."')";
				$sql="insert into silicondemo values('', '".$SID."','".$MID."','".$RFID."','".$result_date."')";
				$query = $this->db->query($sql); 
				
			}
	}
	
}
