<?php 
   class Doctor_model extends CI_Model {
	
function __construct() { 
	 parent::__construct(); 
} 

public function get_single_doctor($id)
{
	$key = $this->config->item('encryption_key');
	$this->db->select("tbl_doctors.id as doctorid,
					   tbl_doctors.name as dr_name,
					   tbl_doctors.profile_pic as dr_pic,
					   tbl_doctors.email as dr_email,
					   tbl_doctors.username as dr_username,
					   CAST(AES_DECRYPT(tbl_doctors.dob,'".$key."') as CHAR) as dr_dob,
					   tbl_doctors.about as dr_bio,
					   tbl_doctors.specialization as dr_specialization_id,
					   tbl_doctors.price as dr_price,
					   tbl_doctors.consultation_duration as dr_consult_duration,
					   tbl_doctors.accept_return as dr_accept_return,
					   tbl_doctors.return_timeperiod as dr_return_timeperiod,
					   tbl_doctors.gender as dr_gender,
					   tbl_doctors.locality as dr_neighbourhood,
					   tbl_doctors.street_address as dr_rua,
					   tbl_doctors.number as dr_number,
					   tbl_doctors.telephone as dr_telephone,
					   tbl_doctors.complement as dr_complement,
					   tbl_doctors.rg as dr_rg,
					   tbl_doctors.cpf as dr_cpf,
					   tbl_doctors.crm as dr_crm,
					   tbl_doctors.cep as dr_cep,
					   tbl_specialization.specialization_name AS dr_specialization
					
							 ");
	$this->db->from('tbl_doctors');
	$this->db->join('tbl_specialization', 'tbl_specialization.id = tbl_doctors.specialization','left');

	$this->db->where('tbl_doctors.id',$id);

	$data =$this->db->get()->row_array();		
	return $data;
}

public function get_doctor_clinic_list($id)
{

	$this->db->select("tbl_clinic.name as clinic_name,
	  				   tbl_clinic.id as clinic_id
							 ");
	$this->db->from('tbl_clinic_doctors');
	$this->db->join('tbl_clinic', 'tbl_clinic.id = tbl_clinic_doctors.clinic_id','inner');
	$this->db->where('tbl_clinic_doctors.doctor_id',$id);
	$this->db->order_by('tbl_clinic.id','ASC');
	//$this->db->get();
//	$data = $this->db->last_query();
	$data =$this->db->get()->result_array();

	// $this->db->select('tbl_clinic.name as clinic_name,
	//   				   tbl_clinic.id as clinic_id');
	// $this->db->where('tbl_clinic.id','0');
	// $default_clinic = $this->db->get('tbl_clinic')->result_array();
	// $datas = array_merge($default_clinic,$data);		
	//print_r($data);die();
	return $data;
}
public function checkDoctorExist($doc_id)
{
 		$this->db->select("*");
 		$this->db->from("tbl_consultation");
 		$this->db->where_in("tbl_consultation.doctor_id",$doc_id);
 		$this->db->order_by("id", "asc");
 		//$this->db->where("tbl_consultation.clinic_id",$clinicId);
 		$query = $this->db->get();
 		return $query->result_array();
 		
}
public function Schedulelist($clinicId,$docId)
{
 		$this->db->select("date as pri_schedule,
 						   date_secondary as sec_schedule,
 						   active_schedule as active_schedule");
 		$this->db->from("tbl_consultation");
 		$this->db->where("tbl_consultation.doctor_id",$docId);
 		$this->db->where("tbl_consultation.clinic_id",$clinicId);
 		//$this->db->where("tbl_consultation.clinic_id",$clinicId);
 		$query = $this->db->get();
 		return $query->row_array();
 		
}
function set_new_consultation($data_primary,$data_secondary,$data_active_type,$clinicId,$doctor_id)
{
	
	$newData_pri = json_encode($data_primary);
	$newData_sec = json_encode($data_secondary);
	$query = $this->db->get_where('tbl_consultation',array('doctor_id'=>$doctor_id,'clinic_id'=>$clinicId))->row();
	if($query){
		$this->db->where(array('doctor_id'=>$doctor_id,'clinic_id'=>$clinicId));
		$this->db->update('tbl_consultation',array('date'=>$newData_pri,'date_secondary'=>$newData_sec,'active_schedule'=>$data_active_type));
	}else{
		$this->db->insert('tbl_consultation',array('doctor_id'=>$doctor_id,'clinic_id'=>$clinicId,'date'=>$newData_pri,'date_secondary'=>$newData_sec,'active_schedule'=>$data_active_type));
	}
	
	
}
function assignDoctors($doctors,$clinicId)
{
	foreach ($doctors as $key => $value) 
	{
		$this->db->insert('tbl_clinic_doctors',array('doctor_id'=>$value,'clinic_id'=>$clinicId));
	}
}

function assignDoctors_default($doctor_id,$clinicId)
{
	$this->db->insert('tbl_clinic_doctors',array('doctor_id'=>$doctor_id,'clinic_id'=> 0));	
	$this->db->insert('tbl_consultation',array('doctor_id'=>$doctor_id,'clinic_id'=> 0,'date'=>'""','date_secondary'=>'""','active_schedule'=> 0));
}

function insertVacation($request)
{
	if($this->db->insert('tbl_doctor_leave', $request))
	{
		return true;
	}
	else
	{
		return false;
	}
}
public function get_doctor_appointments_day($dctr_id,$list_day)
{
	if($list_day=='null')
	{
		$date = date('y-m-d');
		$today = strtotime($date.' 00:00:00');
	}
	else
	{
		$today = strtotime($list_day.' 00:00:00');
	}
	
	//print_r($today);die()
	//$this->db->select("*");
	$this->db->select("tbl_booking.id as booking_id,
	  				   tbl_booking.date as booking_date,
	  				   tbl_booking.time as booking_time,
	  				   tbl_booking.booking_status as booking_status,
	  				   tbl_booking.patient_id as pat_id,
	  				   tbl_registration.name as pat_name,
	  				   tbl_registration.profile_photo as pat_pic,
							 ");
	$this->db->from("tbl_booking");
	$this->db->where("tbl_booking.doctor_id",$dctr_id);
	$this->db->where("tbl_booking.date",$today);
	$this->db->where("tbl_booking.payment_status",1);
	$this->db->where("(tbl_booking.booking_status = '1' or tbl_booking.booking_status = '4')");
	$this->db->order_by("tbl_booking.time_start", "asc"); 
	$this->db->join('tbl_registration', 'tbl_registration.id = tbl_booking.patient_id','inner');
	//$this->db->where("tbl_consultation.clinic_id",$clinicId);
	$query = $this->db->get();
	return $query->result_array();

}
public function get_doctor_appointments_month($doctor_id)
{
		//$this->db->select("count(date) as count,dayofmonth(from_unixtime(`tbl_booking`.`date`)) as day");
		$this->db->select("count(date) as count,dayofmonth(CONVERT_TZ(FROM_UNIXTIME(date),@@session.time_zone,'+00:00')) as day",false);
 		$this->db->from("tbl_booking");
 		$this->db->where("tbl_booking.doctor_id",$doctor_id);
 		$this->db->where("tbl_booking.booking_status<4"); //checked
 		$this->db->where("tbl_booking.booking_status>0"); //checked
 		$this->db->where("tbl_booking.payment_status",1); //checked
 		//$this->db->where("MONTHNAME(FROM_UNIXTIME(tbl_booking.date))",MONTHNAME(CURRENT_DATE()));
 		$this->db->where("monthname(CONVERT_TZ(FROM_UNIXTIME(date),@@session.time_zone,'+00:00'))", date('F',time()));
 		$this->db->order_by("tbl_booking.date", "asc"); 
 		$this->db->group_by('tbl_booking.date'); 
 		//WHERE MONTH(columnName) = MONTH(CURRENT_DATE())
 		//$this->db->where_in("tbl_booking.clinic_id",$clinicId);
 		//$this->db->where("tbl_consultation.clinic_id",$clinicId);
 		$query = $this->db->get();
 		$data = $query->result_array();
 		return $data;
	
}
public function get_doctor_appointments_year($doctor_id)
{
		//$this->db->select("count(date) as count,dayofmonth(from_unixtime(`tbl_booking`.`date`)) as day");
		$this->db->select("count(date) as count");
 		$this->db->from("tbl_booking");
 		$this->db->where("tbl_booking.doctor_id",$doctor_id);
 		//$this->db->where("MONTHNAME(FROM_UNIXTIME(tbl_booking.date))",MONTHNAME(CURRENT_DATE()));
 		$this->db->where("YEAR(CONVERT_TZ(FROM_UNIXTIME(date),@@session.time_zone,'+00:00'))", date('Y',time()));
 		$this->db->order_by("tbl_booking.date", "asc"); 
 		$this->db->where("tbl_booking.booking_status<4"); //checked
 		$this->db->where("tbl_booking.booking_status>0"); //checked
 		$this->db->where("tbl_booking.payment_status",1); //checked

 		//WHERE MONTH(columnName) = MONTH(CURRENT_DATE())
 		//$this->db->where_in("tbl_booking.clinic_id",$clinicId);
 		//$this->db->where("tbl_consultation.clinic_id",$clinicId);
 		$query = $this->db->get();
 		$data = $query->row_array();
 		return $data;
	
}
public function get_doctor_appointments_week($doctor_id,$date)
{
	$date_timestamp = strtotime($date);
	
	//$this->db->select("DATE_FORMAT(FROM_UNIXTIME(tbl_booking.time_start), '%H') as hour,count(*) as count");
	$this->db->select("DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(time_start),@@session.time_zone,'+00:00'), '%H') as hour,count(*) as count",false);
	$this->db->from("tbl_booking");
	$this->db->where("tbl_booking.doctor_id",$doctor_id);
	$this->db->where("tbl_booking.date",$date_timestamp);
	$this->db->where("tbl_booking.booking_status<4"); //checked
	$this->db->where("tbl_booking.booking_status>0"); //checked
	$this->db->where("tbl_booking.payment_status",1); //checked
	//$this->db->where("MONTHNAME(FROM_UNIXTIME(tbl_booking.date))",MONTHNAME(CURRENT_DATE()));
	//$where = 'tbl_booking.date > DATE_SUB(NOW(), INTERVAL 1 WEEK)';
	//$this->db->where("tbl_booking.date >= DATE_SUB(NOW(), INTERVAL 1 WEEK)",NULL,FALSE);
	$this->db->order_by("tbl_booking.date", "asc"); 
	$this->db->group_by("DATE_FORMAT(CONVERT_TZ(FROM_UNIXTIME(time_start),@@session.time_zone,'+00:00'), '%H')",false); 
	$query = $this->db->get();
 		$data = $query->result_array();
 		$data1 = $this->db->last_query();
 		return $data;
}
public function get_single_doc_pat_attended($doc_id,$pagination)
{
	$limit = 10;
	$page = 1;
	if(!empty($pagination)) {
	$page = $pagination;
	}
	$start = ($page-1) * $limit;

	$now = time();
	$this->db->select("tbl_booking.id as book_id,
					   tbl_booking.date as book_date,
	  				   tbl_booking.time as book_time,
	  				   tbl_registration.id as pat_id,
	  				   tbl_registration.name as pat_name,
	  				   tbl_registration.profile_photo as pat_pic,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start > '$now') AND (tbl_booking.booking_status = '1') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as next_consult,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start < '$now') AND (tbl_booking.booking_status = '3') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as last_consult
							 ");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_doctors', 'tbl_booking.doctor_id = tbl_doctors.id','inner');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',3); //checked
	$this->db->group_by("tbl_booking.patient_id"); 
	$this->db->limit($limit, $start);
	$data =$this->db->get()->result_array();		
	return $data;

}
public function get_single_doc_pat_attended_count($doc_id)
{

	$now = time();
	$this->db->select("count(Distinct patient_id) as count");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_doctors', 'tbl_booking.doctor_id = tbl_doctors.id','inner');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',3); //checked
	//$this->db->group_by("tbl_booking.patient_id"); 
	$data =$this->db->get()->row_array();		
	return $data;
}

public function get_single_doc_pat_scheduled($doc_id,$pagination)
{	
	$limit = 10;
	$page = 1;
	if(!empty($pagination)) {
	$page = $pagination;
	}
	$start = ($page-1) * $limit;

	$now = time();
	$this->db->select("tbl_booking.id as book_id,
					   tbl_booking.date as book_date,
	  				   tbl_booking.time as book_time,
	  				   tbl_registration.id as pat_id,
	  				   tbl_registration.name as pat_name,
	  				   tbl_registration.profile_photo as pat_pic,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start > '$now') AND (tbl_booking.booking_status = '1') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as next_consult,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start < '$now') AND (tbl_booking.booking_status = '3') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as last_consult");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_doctors', 'tbl_booking.doctor_id = tbl_doctors.id','inner');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',1); //checked
	$this->db->group_by("tbl_booking.patient_id");
	$this->db->limit($limit, $start); 
	$data =$this->db->get()->result_array();		
	return $data;

}

public function get_single_doc_pat_scheduled_count($doc_id)
{
	$now = time();
	$this->db->select("count(Distinct patient_id) as count");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_doctors', 'tbl_booking.doctor_id = tbl_doctors.id','inner');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',1); //checked
	//$this->db->group_by("tbl_booking.patient_id"); 
	$data =$this->db->get()->row_array();		
	return $data;
}
public function get_single_doc_pat_scheduled_search($doc_id,$search)
{

	$now = time();
	$this->db->select("tbl_booking.id as book_id,
					   tbl_booking.date as book_date,
	  				   tbl_booking.time as book_time,
	  				   tbl_registration.id as pat_id,
	  				   tbl_registration.name as pat_name,
	  				   tbl_registration.profile_photo as pat_pic,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start > '$now') AND (tbl_booking.booking_status = '1') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as next_consult,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start < '$now') AND (tbl_booking.booking_status = '3') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as last_consult");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_doctors', 'tbl_booking.doctor_id = tbl_doctors.id','inner');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',1); //checked
	$this->db->like('tbl_registration.name', $search); 
	$this->db->group_by("tbl_booking.patient_id");
	//$this->db->limit($limit, $start); 
	$data =$this->db->get()->result_array();		
	return $data;
}
public function get_single_doc_pat_attended_search($doc_id,$search)
{

	$now = time();
	$this->db->select("tbl_booking.id as book_id,
					   tbl_booking.date as book_date,
	  				   tbl_booking.time as book_time,
	  				   tbl_registration.id as pat_id,
	  				   tbl_registration.name as pat_name,
	  				   tbl_registration.profile_photo as pat_pic,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start > '$now') AND (tbl_booking.booking_status = '1') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as next_consult,
	  				   (SELECT tbl_booking.time_start FROM tbl_booking WHERE (patient_id = tbl_registration.id) AND (tbl_booking.time_start < '$now') AND (tbl_booking.booking_status = '3') AND (tbl_booking.doctor_id = '$doc_id') ORDER BY tbl_booking.time_start ASC LIMIT 1) as last_consult");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_doctors', 'tbl_booking.doctor_id = tbl_doctors.id','inner');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',3); //checked
	$this->db->like('tbl_registration.name', $search); 
	$this->db->group_by("tbl_booking.patient_id");
	//$this->db->limit($limit, $start); 
	$data =$this->db->get()->result_array();		
	return $data;
}
public function get_single_doc_pat_consultation_list($doc_id,$pat_id)
{
	$now = time();
	$this->db->select("tbl_booking.id as book_id,
					   tbl_booking.date as book_date,
	  				   tbl_booking.time as book_time,
	  				   tbl_booking.total_sum as book_total,
	  				   tbl_booking.promo_name as promo");
	$this->db->from('tbl_booking');
	$this->db->where('tbl_booking.doctor_id',$doc_id);
	$this->db->where('tbl_booking.booking_status',3); //checked 
	$this->db->where('tbl_booking.patient_id',$pat_id);
	$this->db->order_by("tbl_booking.time_start", "desc"); 
	//$this->db->group_by("tbl_booking.patient_id");
	//$this->db->limit($limit, $start); 
	$data =$this->db->get()->result_array();		
	return $data;
}
public function get_booking_details($book_id)
{
	$this->db->select("tbl_booking.id as book_id,
					   tbl_booking.date as book_date,
	  				   tbl_booking.time as book_time,
	  				   tbl_booking.total_sum as book_price,
	  				   tbl_booking.visit_type as visit_type,
	  				   tbl_booking.ipok_fee as ipok_fee,
	  				   tbl_booking.time_start as time_start,
	  				   tbl_registration.id as pat_id,
	  				   tbl_registration.name as pat_name,
	  				   tbl_booking.doctor_id as doc_id");
	$this->db->from('tbl_booking');
	$this->db->join('tbl_registration', 'tbl_booking.patient_id = tbl_registration.id','inner');
	$this->db->where('tbl_booking.id',$book_id);
	//$this->db->group_by("tbl_booking.patient_id");
	//$this->db->limit($limit, $start); 
	$data =$this->db->get()->row_array();		
	return $data;
}
public function get_doctor_num_attendence_fordate($dctr_id,$date)
{

	$today = strtotime($date.' 00:00:00');

	$this->db->select("COUNT(id) AS attendence_today");
	$this->db->from('tbl_booking');
	$this->db->where('tbl_booking.payment_status',1);
	$this->db->where("tbl_booking.date",$today);
	$this->db->where('tbl_booking.booking_status<4'); //checked 
	$this->db->where('tbl_booking.booking_status>0'); //checked 
	$this->db->where('tbl_booking.doctor_id',$dctr_id);
	$data =$this->db->get()->row_array();		
	return $data;
}
public function get_doctor_num_attendance($dctr_id)
{
	$this->db->select("COUNT(id) AS count");
	$this->db->from('tbl_booking');
	$this->db->where('tbl_booking.payment_status',1);
	$this->db->where('tbl_booking.booking_status',3); //checked -> COMPLETED
	$this->db->where('tbl_booking.doctor_id',$dctr_id);
	$data =$this->db->get()->row_array();		
	return $data;
}
public function get_doctor_num_billed($dctr_id)
{
	$this->db->select_sum('amount');
	$this->db->from('tbl_booking');
	$this->db->where('tbl_booking.payment_status',1);
	$this->db->where('tbl_booking.booking_status',3); //checked -> COMPLETED
	$this->db->where('tbl_booking.doctor_id',$dctr_id);
	$data =$this->db->get()->row_array();		
	return $data;
}
public function get_doctor_num_patients($dctr_id)
{
	$this->db->select('COUNT(DISTINCT patient_id) AS count');
	$this->db->from('tbl_booking');
	$this->db->where('tbl_booking.payment_status',1);
	$this->db->where('tbl_booking.booking_status',3); //checked -> COMPLETED
	$this->db->where('tbl_booking.doctor_id',$dctr_id);
	$data =$this->db->get()->row_array();		
	return $data;
}

function update_profile($id,$data)
{
	
	$this->db->where('tbl_doctors.id',$id);
	if($this->db->update('tbl_doctors',$data))
	{
		$key = $this->config->item('encryption_key');					
		$this->db->query("Update tbl_doctors SET dob = AES_ENCRYPT(".$data['dob'].",'".$key."') where id=".$id);	
		$result = array('status' => 'success');
	}
	else
	{
		$result = array('status' => 'error');
	}
	return $result;
}

function get_single_doctor_row($id)
{
	$key = $this->config->item('encryption_key');
	$this->db->select("*,CAST(AES_DECRYPT(dob,'".$key."') as CHAR) as dr_dob");
	$this->db->from("tbl_doctors");
	$this->db->where("tbl_doctors.id",$id);
	$query = $this->db->get();
	return $query->row_array();
}
function check_consult_duration($id)
{
	$this->db->select("consultation_duration");
	$this->db->from("tbl_doctors");
	$this->db->where("tbl_doctors.id",$id);
	$query = $this->db->get();
	return $query->row_array();
}

function get_doctor_num_profileview($id)
{
	$this->db->select('COUNT(id) as count');
	$this->db->from('tbl_doctor_profileviewers');
	$this->db->where('tbl_doctor_profileviewers.doctor_id',$id);
	$query = $this->db->get();
	return $query->row_array();
}

function change_booking_status($booking_id,$status)
{	
	$data = array('booking_status' =>$status);
	$this->db->where('tbl_booking.id',$booking_id);
	$this->db->update('tbl_booking',$data);
}

function get_main_complaints()
{
	$this->db->select('*');
	$this->db->from('tbl_main_complaints');
	$query = $this->db->get();
	return $query->result_array();
}
function get_major_problems()
{
	$this->db->select('*');
	$this->db->from('tbl_major_problems');
	$query = $this->db->get();
	return $query->result_array();
}

function get_major_subproblems($id)
{
	$this->db->select('subproblem_name');
	$this->db->from('tbl_major_subproblems');
	$this->db->where('problem_category_id',$id);
	$query = $this->db->get();
	return $query->result_array();
}
  
function get_distinct_medicines()
{
	$this->db->select('medicine_name,
						id as medicine_id');
	$this->db->from('tbl_medicine');
	$this->db->group_by('medicine_name');
	$query = $this->db->get();
	return $query->result_array();
}

function get_distinct_exams()
{

	$key = $this->config->item('encryption_key');
	$this->db->select("CAST(AES_DECRYPT(exam_procedure,'".$key."') as CHAR) as exam_name,
						id as exam_id");
	$this->db->from('tbl_exams');
	$this->db->group_by('exam_procedure');
	$query = $this->db->get();
	//print_r($query->result_array());exit();
	return $query->result_array();
}

function get_distinct_procedure()
{
	$this->db->select('budget_procedure as procedure_name,
						id as procedure_id');
	$this->db->from('tbl_budget');
	$this->db->group_by('budget_procedure');
	$query = $this->db->get();
	return $query->result_array();
}

function get_distinct_cid()
{
	$this->db->select('disease_name,
						code');
	$this->db->from('tbl_disease_code');
	$this->db->group_by('disease_name');
	$query = $this->db->get();
	return $query->result_array();
}

function check_medical_record($booking_id)
{
	$this->db->select('count(id) as count,
						id as record_id');
	$this->db->from('tbl_medical_records');
	$this->db->where('booking_id',$booking_id);
	$query = $this->db->get();
	return $query->row_array();

}

function insert_medical_record($booking_id)
{
	$array = array('booking_id' => $booking_id );
	$this->db->insert('tbl_medical_records',$array);
	return $this->db->insert_id();
}

function update_booking($booking_id,$array)
{
	$this->db->where('tbl_booking.id',$booking_id);
	$this->db->update('tbl_booking',$array);
}

function update_records($booking_id,$array)
{
	$this->db->where('booking_id',$booking_id);
	if($this->db->update('tbl_medical_records',$array))
		return true;
	else
		return false;
}

function get_medicine_for_name($name)
{
	$this->db->select('medicine_dosage,
						medicine_procedure');
	$this->db->from('tbl_medicine');
	$this->db->where('medicine_name',$name);
	$query = $this->db->get();
	return $query->result_array();

}

function get_exam_for_name($name)
{
	$key = $this->config->item('encryption_key');
	$this->db->select("CAST(AES_DECRYPT(observation,'".$key."') as CHAR) as observation");
	$this->db->from('tbl_exams');
	$this->db->where("exam_procedure = AES_ENCRYPT('".$name."','".$key."') ");
	$query = $this->db->get();
	return $query->result_array();
}

function get_budget_for_name($name)
{
	$this->db->select('amount,
						quantity');
	$this->db->from('tbl_budget');
	$this->db->where('budget_procedure',$name);
	$query = $this->db->get();
	return $query->result_array();

}

function get_medical_record_for_booking($booking_id)
{
	$this->db->select('*');
	$this->db->from('tbl_medical_records');
	$this->db->where('booking_id',$booking_id);
	$query = $this->db->get();
	return $query->row_array();
}

function get_certificate_letters()
{
	$this->db->select('cid_letter,
						letter');
	$this->db->from('tbl_policy');
	$query = $this->db->get();
	return $query->row_array();
}

function get_current_booking_status($booking_id)
{
	$this->db->select('booking_status');
	$this->db->from('tbl_booking');
	$this->db->where('id',$booking_id);
	$query = $this->db->get();
	return $query->row_array();
}

function get_notifications($doctor_id,$page,$limit = null)
{
	$start = 0;
	if(!isset($limit) or empty($limit))
		{	
			$limit = $page * 5;
		}
	
	$this->db->select("id,
						type,
						CASE
							WHEN type = '0' THEN 'Redemption Made'
							WHEN type = '1' THEN 'New Review'
							WHEN type = '2' THEN 'New Consultation'
							WHEN type = '3' THEN 'Added to new clinic'
						END as type_desc,
						message,
						read_status,
						time");
	$this->db->from('tbl_doctor_notifications');
	$this->db->where('doctor_id',$doctor_id);
	$this->db->order_by('time','DESC');
	$this->db->limit($limit, $start);
	$query = $this->db->get();
	return $query->result_array();
}

function get_notifications_total_count($doctor_id)
{
	$this->db->select('count(id) as count');
	$this->db->from('tbl_doctor_notifications');
	$this->db->where('doctor_id',$doctor_id);
	$query = $this->db->get();
	//echo $this->db->last_query();exit();
	return $query->result_array();
}

function get_aniversaries_list($doc_id)
{
	//print_r($doc_id);die();
// Upcomming Birthday list
  $O_month = date('m');
  $T_month = date('m', strtotime("last day of next month"));
  if($T_month == "12") {
	  $TH_month = "01";
  }else if($T_month <= "11") {
	  $TH_month = $T_month + "01";
  }
  if(strlen($TH_month) == 1) {
	  $TH_month="0".$TH_month;
  }
  // another methods for getting months 
/* echo $date = date('m',strtotime('first day of +1 month'));
  echo $date = date('m',strtotime('first day of +2 month'));
*/
  
/* $upcomming = $this->db->query("SELECT `tbl_registration`.`id`, `tbl_registration`.`name`, `tbl_registration`.`dob`, `tbl_registration`.`profile_photo`, DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e') AS 'date_formatted' ,`tbl_booking`.`booking_status`,`tbl_booking`.`id` as 'book_id' FROM `tbl_booking` INNER JOIN `tbl_registration` ON `tbl_booking`.`patient_id` = `tbl_registration`.`id` WHERE `tbl_booking`.`doctor_id` = $doc_id AND `tbl_booking`.`booking_status` = '3' AND `tbl_booking`.`payment_status` = '1' AND (DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e') like '%-$O_month-%' or DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e') like '%-$T_month-%' or DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e') like '%-$TH_month-%') GROUP BY `tbl_registration`.`id` ORDER BY DAYOFYEAR(DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e')) < DAYOFYEAR(CURDATE()) , DAYOFYEAR(DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e'))");*/

 $upcomming = $this->db->query("SELECT DISTINCT(reg_id) as id, reg_name as name, CAST(AES_DECRYPT(`reg_dob`, 'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR) as dob,reg_photo as profile_photo,DATE_FORMAT(FROM_UNIXTIME(CAST(AES_DECRYPT(reg_dob,'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR)), '%Y-%m-%e') AS 'date_formatted',reg_booking_status as booking_status,reg_book_id as booki_id  FROM (select tbl_registration.id as reg_id, tbl_registration.name as reg_name, tbl_registration.dob as reg_dob, tbl_registration.profile_photo as reg_photo,`tbl_booking`.`booking_status` as reg_booking_status,`tbl_booking`.`id` as 'reg_book_id' from `tbl_registration` JOIN `tbl_booking` ON `tbl_registration`.`id` = `tbl_booking`.`patient_id` WHERE `tbl_booking`.`booking_status` = '3' AND `tbl_booking`.`payment_status` = '1' AND `tbl_booking`.`doctor_id` = $doc_id) temp WHERE (DATE_FORMAT(FROM_UNIXTIME(CAST(AES_DECRYPT(reg_dob,'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR)), '%Y-%m-%e') like '%-$O_month-%' or DATE_FORMAT(FROM_UNIXTIME(CAST(AES_DECRYPT(reg_dob,'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR)), '%Y-%m-%e') like '%-$T_month-%' or DATE_FORMAT(FROM_UNIXTIME(CAST(AES_DECRYPT(reg_dob,'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR)), '%Y-%m-%e') like '%-$TH_month-%') GROUP BY reg_id ORDER BY DAYOFYEAR(DATE_FORMAT(FROM_UNIXTIME(CAST(AES_DECRYPT(reg_dob,'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR)), '%Y-%m-%e')) < DAYOFYEAR(CURDATE()) , DAYOFYEAR(DATE_FORMAT(FROM_UNIXTIME(CAST(AES_DECRYPT(reg_dob,'Ptf/PWNWrULQT72syxfaaBRTS9JbiKrj9dfuVEvT3rA') as CHAR)), '%Y-%m-%e'))");

/*
	$query = $this->db->query("SELECT `tbl_registration`.`id`, `tbl_registration`.`name`, `tbl_registration`.`dob`, `tbl_registration`.`profile_photo`, DATE_FORMAT(FROM_UNIXTIME(`tbl_registration`.`dob`), '%Y-%m-%e') AS 'date_formatted'  FROM `tbl_booking` LEFT JOIN `tbl_registration` ON `tbl_booking`.`patient_id` = `tbl_registration`.`id` WHERE `tbl_booking`.`doctor_id` = '30' AND `tbl_booking`.`booking_status` = 3 AND month(CONVERT_TZ(FROM_UNIXTIME(tbl_registration.dob),@@session.time_zone,'+00:00')) >= ".date('m',time())." AND IF(month(CONVERT_TZ(FROM_UNIXTIME(tbl_registration.dob),@@session.time_zone,'+00:00'))='".date('m',time())."',day(CONVERT_TZ(FROM_UNIXTIME(tbl_registration.dob),@@session.time_zone,'+00:00'))>= '".date('d',time())."',true)")*/;
	//$this->db->get();
	// echo $this->db->last_query();die();
	return $upcomming->result_array();
}

public function get_doctor_password($id)
{
	$this->db->select("password");
	$this->db->from("tbl_doctors");
	$this->db->where("id",$id);
	$query = $this->db->get();
	return $query->row_array();
}
public function get_doctor_confirmation_code($id)
{
	$this->db->select("confirmation_code");
	$this->db->from("tbl_doctors");
	$this->db->where("id",$id);
	$query = $this->db->get();
	return $query->row_array();
}

public function disable_doctor_account($id)
{
	$data = array('account_status' =>1);
	$this->db->where('tbl_doctors.id',$id);
	$this->db->update('tbl_doctors',$data);
}

public function set_confirmation_code($user,$code)
{
    $data = array('confirmation_code' =>$code);
	$this->db->where('tbl_doctors.id',$user['id']);
	$this->db->update('tbl_doctors',$data);
}

public function add_colaborator($array)
{
	if($this->db->insert('tbl_doctor_colaborators',$array))
	{
		$insert_id = $this->db->insert_id();
		return $insert_id;
	}
	else
	{
		return 0;
	}

}

public function update_colaborator($id,$updatearray)
{
	$this->db->where('tbl_doctor_colaborators.id',$id);
	if($this->db->update('tbl_doctor_colaborators',$updatearray))
		return true;
	else
		return false;
}

public function delete_registration_collaborator($id)
{
	if($this->db->where_in('id', $id)->delete('tbl_doctor_colaborators')){}
}

public function get_all_collaborator_for_doctor($docid)
{
	$this->db->select('id,
					   name,
					   image');
	$this->db->from('tbl_doctor_colaborators');
	$this->db->where('doctor_id',$docid);
	$query = $this->db->get();
	return $query->result_array();

}

public function get_collaboator_byid_doc_id($id,$docid)
{
	$this->db->select('id,
					   name,
					   image,
					   email,
					   telephone,
					   cpf,
					   capabilities');
	$this->db->from('tbl_doctor_colaborators');
	$this->db->where('id',$id);
	$this->db->where('doctor_id',$docid);
	$query = $this->db->get();
	return $query->row_array();

}

public function get_capabilities_of_collaborator($id)
{
	$this->db->select('capabilities');
	$this->db->from('tbl_doctor_colaborators');
	$this->db->where('id',$id);
	$query = $this->db->get();
	return $query->row_array();
}

public function get_clinic_list($doc_id)
{
	$this->db->select('tbl_clinic.id as clinic_id,
					tbl_clinic.name as clinic_name,
					tbl_clinic.profile_photo as clinic_pic');
	$this->db->from('tbl_clinic');
	$this->db->join('tbl_clinic_doctors', 'tbl_clinic_doctors.clinic_id = tbl_clinic.id','inner');
	$this->db->where('tbl_clinic_doctors.doctor_id',$doc_id);
	$query = $this->db->get();
	return $query->result_array();
}

public function get_wallet_for_doctor($doc_id)
{
	$this->db->select('reedem_earn as reedem_earn,
						future_earn as future_earn,
						total_earn as total_earn');
	$this->db->from('tbl_wallet_details');
	$this->db->where('doctor_id',$doc_id);
	$query = $this->db->get();
	$query['reedem_earn'] = decrypt_data($query['reedem_earn']);
	$query['future_earn'] = decrypt_data($query['future_earn']);
	$query['total_earn'] = decrypt_data($query['total_earn']);
	return $query->row_array();
}

public function update_wallet($id,$data)
{
	$sql = $this->db->insert_string('tbl_wallet_details', array("doctor_id"=>$id,"reedem_earn"=>encrypt_data($data['reedem_earn']),"future_earn"=>encrypt_data($data['future_earn']),"total_earn"=>encrypt_data($data['total_earn']))) . ' ON DUPLICATE KEY UPDATE doctor_id = ' .$id.',reedem_earn ='.'"' .encrypt_data($data['reedem_earn']).'"'.',future_earn='.'"' .encrypt_data($data['future_earn']).'"' .',total_earn='. '"' .$data['total_earn'].'"';
		//print_r($this->db->last_sqlquery());die();
		if($this->db->query($sql)){
			$return_array = array('status'=>'success');
		}
		else{
			$return_array = array('status'=>'fail');
		}
		
		return $return_array;
}
  
   } 
?> 