<?php

/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 * Description of Clients Model FrontEnd
 *
 * @author Deepak saini
 */
if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class Custom_model extends CI_Model {    
   
	 /* SELECT `id`, `UserName`, `FirstName`, `LastName`, `EmailId`, `Mobile`, `DoB`, `Gender`, `CollegeDetails`, `IsActive`, `CreatedDate` FROM `tblstudents` WHERE 1 */
	function get_students_list($id=''){
		$this->db->select('*');
    	$this->db->from('tblstudents');
		if($id!=""){
			$this->db->where('ParticipateTestId',$id);
		}		
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	/* SELECT `id`, `StudentCode`, `ParticipateTestId`, `Name`, `ParentsName`, `EmailId`, `DoB`, `Education`, ` LastExaminationMarks`, `DistrictCode`, `TrainingCenterCode`, `State`, `IsActive`, `CreatedDate` FROM `tblstudents` WHERE 1 */
	function get_exit_student($tid,$sid){
		$this->db->select('*');
    	$this->db->from('tblstudents');
		$this->db->where('ParticipateTestId',$tid);
		$this->db->where('id',$sid);
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		if($query->num_rows()>0){
			return $query->row();
		}else{
			return false;
		}
	}
	/* SELECT `id`, `TestId`, `TestTypeName`, `TotalQuestion`, `IsActive`, `CreatedDate` FROM `mtblcareer_test_type` WHERE 1 */
	function get_ext_test_type($tid){
		$this->db->select('*');
    	$this->db->from('mtblcareer_test_type');
		$this->db->where('TestId',$tid);
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_test_details($tid){
		$this->db->select('t.*');
    	$this->db->from('mtblcareer_tests t');	 
		$this->db->where('id',$tid);
		$this->db->where('t.IsActive',1);
		$query = $this->db->get();
		return $query->row();
	}
	
	function get_test_list($aid){
		$this->db->select('t.*');
    	$this->db->from('mtblcareer_tests t');
		$this->db->join('tbladmin_test_assigns a','a.TestId=t.id','left');
		$this->db->where('a.AdminId',$aid);
		$this->db->where('t.IsActive',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	/* SELECT `id`, `TestId`, `TestTypeId`, `SectionName`, `TotalMap`, `IsActive`, `CreatedDate` FROM `mtblcareer_test_type_section` WHERE 1 */
	function get_ext_test_type_section_grade($sid, $tid, $ttid){
		
	/* 	SELECT  FROM mtblcareer_test_type_section s
left join mtblcareer_test_type_section_mapping sm on s.id=sm.SectionId
left join tblstudent_marks as m on m.QuestionId=sm.QuestionId
WHERE m.TestId=1 and s.TestTypeId=1 and m.TestTypeId=1 and m.StudentId=1 group by sm.SectionId order by sm.SectionId asc */
		$this->db->select('sm.SectionId, s.SectionName, s.TotalMap, group_concat(m.QuestionMarks) as MarksText, sum(m.QuestionMarks) as Marks, (SELECT Grade FROM mtblcareer_test_type_section_range WHERE SectionId=sm.SectionId and RangeMin<=sum(m.QuestionMarks) and RangeMax>=sum(m.QuestionMarks) ) SGrade');
    	$this->db->from('mtblcareer_test_type_section s');
		$this->db->join('mtblcareer_test_type_section_mapping sm','s.id=sm.SectionId','left');
		$this->db->join('tblstudent_marks m','m.QuestionId=sm.QuestionId','left');
		$this->db->where('m.TestId',$tid);
		$this->db->where('s.TestTypeId',$ttid);
		$this->db->where('m.TestTypeId',$ttid);
		$this->db->where('m.StudentId',$sid);
		$this->db->group_by('sm.SectionId');
		$this->db->order_by('sm.SectionId asc');
		$query = $this->db->get();
		return $query->result_array();
	}
 
	function get_ext_test_type_group_grade($tid,$ttid){
		$this->db->select('sm.GroupId, g.SectionTypeName ,g.STotalMap, GROUP_CONCAT(s.SectionName) SectionNames, GROUP_CONCAT(sm.SectionId) SectionIds');
		$this->db->from('mtblcareer_test_type_group g');
		$this->db->join('mtblcareer_test_type_group_section_mapping sm ','sm.GroupId=g.id','left');
		$this->db->join('mtblcareer_test_type_section s ','s.id = sm.SectionId','left');
		$this->db->where('g.TestId',$tid);
		$this->db->where('g.TestTypeId',$ttid);
		$this->db->group_by('sm.GroupId');
		$this->db->order_by('sm.GroupId asc');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_student_career_interest($tid,$sid){
		$this->db->select('i.VocationalCareerId, c.VocationalCareerName');
    	$this->db->from('tblstudent_career_interest_mapping i');
		$this->db->join('mtblvocational_career_name c','c.id=i.VocationalCareerId','left');
		$this->db->where('i.TestId',$tid);
		$this->db->where('i.StudentId',$sid);
		$this->db->where('i.IsActive',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	
	function get_career_interest_map($tid, $interestId){
		$test_types=$this->get_ext_test_type($tid);
		$data_result=array();
		foreach($test_types as $row){
			$this->db->select('*');
			$this->db->from('mtblvocational_career_group_mapping');
			$this->db->where('TestId', $tid);
			$this->db->where('TestTypeId', $row['id']);
			$this->db->where('VocationalCareerId',$interestId);
			$this->db->where('GroupGrade','H');
			$this->db->where('IsActive',1);
			$query = $this->db->get();
			$res = $query->result_array();
			$data_result[] = $res;
		}
		return $data_result;
	}
	
	/* SELECT VocationalCareerId, count(VocationalCareerId) Total FROM `mtblvocational_career_group_mapping` WHERE GroupId in('8','18') group by VocationalCareerId having Total>1  */
	
	function get_recommendation_list($tid, $gids, $s=""){
		$this->db->select('i.VocationalCareerId, c.VocationalCareerName, count(i.VocationalCareerId) Total');
    	$this->db->from('mtblvocational_career_group_mapping i');
		$this->db->join('mtblvocational_career_name c','c.id=i.VocationalCareerId','left');
		$this->db->where('i.TestId',$tid);
		$this->db->where_in('i.GroupId',$gids);
		if($s==""){
			$this->db->where('GroupGrade','H');
		}
		$this->db->where('i.IsActive',1);
		$this->db->group_by('i.VocationalCareerId');
		$this->db->having('Total>1');
		$query = $this->db->get();
		return $query->result_array();
	}
	
	
	function get_group_grade($Sectiom_marks_array,$sectionIds){
		 
		$ids_array=explode(',',$sectionIds);
		$total_ids = count($ids_array);
		 
		$h=0; $m=0; $l=0; $e=0;
		foreach($ids_array as $row){
			if(isset($Sectiom_marks_array[$row])){
				if($Sectiom_marks_array[$row]=="E"){
					$e++;
				}
				if($Sectiom_marks_array[$row]=="H"){
					$h++;
				}
				if($Sectiom_marks_array[$row]=="M"){
					$m++;
				}
				if($Sectiom_marks_array[$row]=="L"){
					$l++;
				}
			}
						
		}
		
		if($l>0){
			return "L";
		}
		if($e>0){
			return "H";
		}
		
		if($h==$total_ids){
			return "H";
		}
		
		if($m==$total_ids){
			return "M";
		}
		
		if($m==$h){
			return "M";
		}
		
		if($h>$m){
			return "H";
		}
		if($m>$h){
			return "M";
		}
		
	}
	
	function get_group_score($Sectiom_marks_array,$sectionIds){
		 
		$ids_array=explode(',',$sectionIds);
		$total_ids = count($ids_array);
		 
		 $total=0;
		foreach($ids_array as $row){
			if(isset($Sectiom_marks_array[$row])){
				 $total=$total +$Sectiom_marks_array[$row];
			}						
		}
		
		return round($total/$total_ids,2);
		
	}
	
	
     
}
