<?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`, `TestName`, `Location`, `DistrictCode`, `TrainingCenterCode`, `State`, `Upload`,
	`Aptitude`, `Personality`, `Interest`, `IsActive`, `CreatedDate` FROM `mtblcareer_tests` WHERE 1 */
	
	function get_test_details($id){
		$this->db->select('*');
    	$this->db->from('mtblcareer_tests');
		$this->db->where('id',$id);
		$this->db->where('IsActive',1);
		 
		$query = $this->db->get();
		if($query->num_rows()>0){
			return $query->row();
		}else{
			return false;
		}
	}
   
	 /* 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`, `StudentId`, `ParticipateTestId`, `IsAptitudeAccept`, `IsPersonalityAccept`, `IsInterestAccept`, `AptitudeStatus`, `PersonalityStatus`, `InterestStatus`, `IsActive`, `CreatedDate` FROM `tblstudents_test_status` WHERE 1 */
	function get_students_test_status($id){
		$this->db->select('*');
    	$this->db->from('tblstudents_test_status');
		$this->db->where('StudentId',$id);
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		if($query->num_rows()>0){
			return $query->row();
		}else{
			return false;
		}
	}
	
	/* SELECT `id`, `TestId`, `VocationalCareerName`, `IsActive`, `CreatedDate` FROM `mtblvocational_career_name` WHERE 1 */
	function get_careers_list($testid){
		$this->db->select('id, VocationalCareerName');
    	$this->db->from('mtblvocational_career_name');
		$this->db->where('TestId',$testid);
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		return $query->result_array();
	}
	function get_students_details($id){
		$this->db->select('*');
    	$this->db->from('tblstudents');
		$this->db->where('id',$id);
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		return $query->row();
	}
	
	/* 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_ext_test_answer_type($tid,$typeId){
		$this->db->select('AnswerType');
    	$this->db->from('mtblcareer_test_type');
		$this->db->where('TestId',$tid);
		$this->db->where('id',$typeId);
		$this->db->where('IsActive',1);
		$query = $this->db->get();
		return $query->row()->AnswerType;
	}
	
	/* 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);
		
	}
	
	
	/* SELECT * FROM `mtblcareer_test_type_questions` where TestId=2 and TestTypeId=3 order by QuestionId asc limit 12,6 */
	function get_six_question($testId,$typeId,$lastId){
		//var_dump($testId);var_dump($typeId);var_dump($lastId);die;
		$last=$lastId;
		$where ='TestId="'.$testId.'" and TestTypeId="'.$typeId.'" order by QuestionId asc limit '.$last.',6';
		
		$this->db->select('*');
    	$this->db->from('mtblcareer_test_type_questions');
		$this->db->where($where);		
		/* $this->db->where('TestId',$testId);
		$this->db->where('TestTypeId',$typeId); 
		$this->db->order_by('QuestionId','asc');
		$this->db->limit(0,6);		  */
		$query = $this->db->get();
		return $query->result_array();
	}
	/* SELECT `id`, `StudentId`, `TestId`, `TestTypeId`, `QuestionId`, `QuestionMarks`, `IsActive`, `MarksDate` FROM `tblstudent_marks` WHERE 1 */
	function student_question_total($sid,$testId,$typeId){
		$this->db->select('*');
    	$this->db->from('tblstudent_marks');
		$this->db->where('StudentId',$sid);
		$this->db->where('TestId',$testId);
		$this->db->where('TestTypeId',$typeId);
		$query = $this->db->get();		 
		return $query->num_rows();
		
	}
	
	function get_student_last_question($sid,$testId,$typeId){
		$this->db->select('QuestionId');
    	$this->db->from('tblstudent_marks');
		$this->db->where('StudentId',$sid);
		$this->db->where('TestId',$testId);
		$this->db->where('TestTypeId',$typeId);
		$this->db->order_by('QuestionId','desc');
		$this->db->limit(1);
		$query = $this->db->get();		 
		if($query->num_rows()>0){
			return $query->row()->QuestionId;
		}else{
			return 0;
		}
		
	}
     
}
