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

class Home_model extends CI_Model
{


    public function checkUser($data = array())
    {
        return $this->db->select("
				user.id, 
				CONCAT_WS(' ', user.firstname, user.lastname) AS fullname,
				user.email, 
				user.image, 
				user.last_login,
				user.last_logout, 
				user.ip_address, 
				user.status, 
				user.is_admin, 
				IF (user.is_admin=1, 'Admin', 'User') as user_level
			")
            ->from('user')
            ->where('email', $data['email'])
            ->where('password', md5($data['password']))
            ->get();
    }

    public function userPermission($id = null)
    {
        return $this->db->select("
			module.controller, 
			module_permission.fk_module_id, 
			module_permission.create, 
			module_permission.read, 
			module_permission.update, 
			module_permission.delete
			")
            ->from('module_permission')
            ->join('module', 'module.id = module_permission.fk_module_id', 'full')
            ->where('module_permission.fk_user_id', $id)
            ->get()
            ->result();
    }


    public function last_login($id = null)
    {
        return $this->db->set('last_login', date('Y-m-d H:i:s'))
            ->set('ip_address', $this->input->ip_address())
            ->where('id', $this->session->userdata('id'))
            ->update('user');
    }

    public function last_logout($id = null)
    {
        return $this->db->set('last_logout', date('Y-m-d H:i:s'))
            ->where('id', $this->session->userdata('id'))
            ->update('user');
    }

    public function profile($id = null)
    {
        return $this->db->select("
			*, 
				CONCAT_WS(' ', firstname, lastname) AS fullname,
				IF (user.is_admin=1, 'Admin', 'User') as user_level
			")
            ->from("user")
            ->where("id", $id)
            ->get()
            ->row();
    }

    public function employee_data($id = null)
    {
        return $this->db->select('p.*,d.*,u.*,po.position_name')
            ->from('employee_history p')
            ->join('department d', 'p.dept_id = d.dept_id', 'left')
            ->join('position po', 'p.pos_id = po.pos_id', 'left')
            ->join('user u', 'p.email = u.email', 'left')
            ->where('p.employee_id', $id)
            ->get()
            ->row();
    }

    public function prob_evaluation($id = null)
    {
        return $this->db->select('first_name, last_name')
            ->from('employee_history p')
            ->where('employee_id', $id)
            ->get()
            ->row();
    }

    public function updateedu($id)
    {
        return $this->db->select('*')
            ->from('employee_history')
            ->where('employee_id', $id)
            ->get()
            ->result();
    }

    public function benifit($id)
    {
        $this->db->select('*');
        $this->db->from('employee_benifit');
        $this->db->where('employee_id', $id);
        $query = $this->db->get();
        return $query->result();
    }

    public function award($id)
    {
        $this->db->select('*');
        $this->db->from('award ');
        $this->db->where('employee_id', $id);
        $query = $this->db->get();
        return $query->result();
    }

    public function performance($id)
    {
        $this->db->select('AVG(number_of_star) as star');
        $this->db->from('employee_performance ');
        $this->db->where('employee_id', $id);
        $query = $this->db->get();
        $result = $query->row();
        return $star = $result->star;
    }

    public function salary_log_single($id)
    {
        return $this->db->select('*')
            ->from('employee_salary_log')
            ->where('employee_id', $id)
            ->get()
            ->result();
    }

    public function get_warnings($id)
    {
        return $this->db->select('*')
            ->from('warning')
            ->where('employee_id', $id)
            ->get()
            ->result();
    }

    public function setting($data = array())
    {
        return $this->db->where('id', $data['id'])
            ->update('user', $data);
    }

    public function empnumber()
    {
        return $this->db->select('COUNT(DISTINCT(employee_id)) AS employee_id')
            ->from("employee_history")
            ->get()
            ->row();
    }


    public function atntd()
    {
        $date = date('Y-m-d');
        $this->db->select('*,DATE(time) as mydate');
        $this->db->from('attendance_history');
        $this->db->like('time', $date, 'after');
        $this->db->group_by('uid');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }


    public function paidloanamnt()
    {
        $year = date('Y');
        $month = date('m');
        return $this->db->select('SUM(amount) AS amount')
            ->from("grand_loan")
            ->where('YEAR(date_of_approve)', $year)
            ->where('MONTH(date_of_approve)', $month)
            ->get()
            ->row();
    }


    public function givenloan($month)
    {
        $year = date('Y');
        $data = $this->db->select('SUM(amount) AS amount')
            ->from("grand_loan")
            ->where('YEAR(date_of_approve)', $year)
            ->where('MONTH(date_of_approve)', $month)
            ->get()
            ->row();
        return (!empty($data->amount) ? $data->amount : 0);
    }


    public function receiveloanamnt()
    {

        $year = date('Y');
        $month = date('m');
        return $this->db->select('SUM(payment) AS amount')
            ->from("loan_installment")
            ->where('YEAR(date)', $year)
            ->where('MONTH(date)', $month)
            ->get()
            ->row();
    }

    public function awarded_person($month)
    {

        $year = date('Y');
        return $this->db->select('*')
            ->from("award")
            ->where('YEAR(date)', $year)
            ->where('MONTH(date)', $month)
            ->get()
            ->num_rows();
    }

    public function receivedloan($month = 04)
    {
        $year = date('Y');
        $data = $this->db->select('SUM(payment) AS amount')
            ->from("loan_installment")
            ->where('YEAR(date)', $year)
            ->where('MONTH(date)', $month)
            ->get()
            ->row();
        return (!empty($data->amount) ? $data->amount : 0);
    }

    public function atnwork()
    {

        $d = date('Y-m-d');

        $att_in = $this->db->select('MIN(time) as intime,MAX(time) as outtime,uid')
            ->from('attendance_history')
            ->like('time', $d, 'after')
            ->group_by('uid')
            ->get()
            ->result();

        $idx = 1;
        $totalhour = [];
        foreach ($att_in as $attendancedata) {
            $date_a = new DateTime($attendancedata->outtime);
            $date_b = new DateTime($attendancedata->intime);
            $interval = date_diff($date_a, $date_b);
            $totalwhour = $interval->format('%h:%i:%s');
            $totalhour[$idx] = $totalwhour;
            $idx++;
        }
        $seconds = 0;
        foreach ($totalhour as $t) {
            $timeArr = array_reverse(explode(":", $t));

            foreach ($timeArr as $key => $value) {
                if ($key > 2) break;
                $seconds += pow(60, $key) * $value;
            }

        }

        $hours = floor($seconds / 3600);
        $mins = floor(($seconds - ($hours * 3600)) / 60);
        $secs = floor($seconds % 60);
        $totalworkinghours = $hours . ':' . $mins . ':' . $secs;
        $totalworkinghours = array(
            'hours' => $hours,
            'minutes' => $mins,
            'sec' => $secs
        );
        return $totalworkinghours;

    }

    function leave_employee()
    {
        $year = date('Y-m-d');
        $query = $this->db->select("count(employee_id) as leave_total")
            ->from('leave_apply')
            ->where('apply_strt_date <=', $year)
            ->where('apply_end_date >=', $year)
            ->where('status', 1)
            ->where('status_by_sup', 1)
            ->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return null;
        }
    }

    public function details($id)
    {
        return $this->db->select('*')
            ->from('notice_board')
            ->where('notice_id', $id)
            ->get()
            ->result();
    }

    public function monthlyincome($year, $month)
    {
        $head = $this->db->select('HeadCode')->from('acc_coa')->where('PHeadName', 'Income')->get()->result_array();
        $totalcredit = 0;
        foreach ($head as $headcode) {
            $income = $this->db->select('sum(Credit) as totalamount')->from('acc_transaction')->where('COAID', $headcode['HeadCode'])->where('YEAR(VDate)', $year)->where('MONTH(VDate)', $month)->get()->row();
            $totalcredit = $totalcredit + (!empty($income->totalamount) ? $income->totalamount : 0);
        }
        return (!empty($totalcredit) ? $totalcredit : 0);

    }

    //Expence
    public function monthlyexpense($year, $month)
    {
        $head = $this->db->select('HeadCode')->from('acc_coa')->where('PHeadName', 'Expence')->get()->result_array();
        $totalexpense = 0;
        foreach ($head as $headcode) {
            $expense = $this->db->select('sum(Debit) as totalamount')->from('acc_transaction')->where('COAID', $headcode['HeadCode'])->where('YEAR(VDate)', $year)->where('MONTH(VDate)', $month)->get()->row();
            $totalexpense = $totalexpense + (!empty($expense->totalamount) ? $expense->totalamount : 0);
        }
        return (!empty($totalexpense) ? $totalexpense : 0);
    }

    public function count_attent_employee()
    {
        $date = date('Y-m-d');
        $this->db->select('*');
        $this->db->from('employee_attendance');
        $this->db->where('check_in_date', $date);
        $this->db->group_by('att_id');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return 0;
    }

    public function count_male_employee()
    {
        $this->db->select('*');
        $this->db->from('employee_history');
        $this->db->where('gender', 1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }

    public function count_female_employee()
    {
        $this->db->select('*');
        $this->db->from('employee_history');
        $this->db->where('gender', 2);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }

    public function last_thirtydays_attendance()
    {
        $startdate = date('Y-m-d', strtotime('-30 days'));
        $currentdate = date('Y-m-d');
        $this->db->select('DATE(time) as mydate');
        $this->db->from('attendance_history');
        $this->db->where('DATE(time) >=', $startdate);
        $this->db->where('DATE(time) <=', $currentdate);
        $this->db->group_by('mydate');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result_array();
        }
        return false;
    }

    public function count_30daysattendance($date)
    {
        $this->db->select('*');
        $this->db->from('attendance_history');
        $this->db->where('DATE(time)', $date);
        $this->db->group_by('uid');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return false;
    }

    public function last_15days_absent()
    {
        $startdate = date('Y-m-d', strtotime('-15 days'));
        $currentdate = date('Y-m-d');
        $this->db->select('DATE(time) as mydate');
        $this->db->from('attendance_history');
        $this->db->where('DATE(time) >=', $startdate);
        $this->db->where('DATE(time) <=', $currentdate);
        $this->db->group_by('mydate');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result_array();
        }
        return false;
    }

    public function count_15daysabsent($date)
    {
        $this->db->select('*');
        $this->db->from('attendance_history');
        $this->db->where('DATE(time)', $date);
        $this->db->group_by('uid');
        $present = $this->db->get()->num_rows();

        $leave = $this->db->select("count(employee_id) as leave_total")->from('leave_apply')->where('leave_aprv_strt_date <=', $date)->where('leave_aprv_end_date >=', $date)->get()->num_rows();

        $this->db->select('*');
        $this->db->from('employee_history');
        $this->db->group_by('employee_id');
        $totalemployee = $this->db->get()->num_rows();

        $absent = (!empty($totalemployee) ? $totalemployee : 0) - ((!empty($present) ? $present : 0) + (!empty($leave) ? $leave : 0));
        return $absent;
    }

    public function hired_employee_current_year($month)
    {
        $year = date('Y');
        $this->db->select('*');
        $this->db->from('employee_history');
        $this->db->where('YEAR(hire_date)', $year);
        $this->db->where('MONTH(hire_date)', $month);
        $this->db->group_by('employee_id');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->num_rows();
        }
        return 0;

    }

    public function notice_list()
    {
        $this->db->select('*');
        $this->db->from('notice_board');
        $this->db->order_by('notice_id', 'desc');
        $this->db->limit(10);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result_array();
        }
        return false;
    }

    public function latest_recuited_employee()
    {
        $this->db->select('eh.*,d.department_name');
        $this->db->from('employee_history eh');
        $this->db->join('department d','eh.dept_id = d.dept_id','left');
        $this->db->order_by('emp_his_id', 'desc');
        $this->db->limit(10);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result_array();
        }
        return false;
    }

    public function getAllEmployeeData()
    {
        $this->db->select('eh.*,d.department_name');
        $this->db->from('employee_history eh');
        $this->db->join('department d','eh.dept_id = d.dept_id','left');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result_array();
        }
        return false;
    }

    public function update_password($data = array())
    {
        return $this->db->where('email', $data["email"])
            ->update("user", $data);
    }

    public function update_emp_password($data = array())
    {
        return $this->db->where('employee_id', $data["employee_id"])
            ->update("employee_history", $data);
    }

    public function update_employee($data = array())
    {
        return $this->db->where('employee_id', $data["employee_id"])
            ->update("employee_history", $data);
    }

    public function skills_list_dropdown()
    {
        $this->db->select('*');
        $this->db->from('skills');
        $this->db->order_by('skill_id', 'desc');
        $query = $this->db->get();
        $data = $query->result();

        if (!empty($data)) {
            return $data;
        } else {
            return false;
        }
    }

    public function documentsinfo($id)
    {
        $this->db->select('*');
        $this->db->from('professional_experience');
        $this->db->where('employee_id', $id);
        $query = $this->db->get();
        return $query->result();
    }

    public function certificationsinfo($id)
    {
        $this->db->select('*');
        $this->db->from('certificate_and_trainings');
        $this->db->where('employee_id', $id);
        $query = $this->db->get();
        return $query->result();
    }

    public function this_month_employee_present($id, $start_date, $end_date)
    {
        $this->db->select('COUNT(employee_id) as present');
        $this->db->from('employee_attendance');
        $this->db->where('employee_id', $id);
        $this->db->where('check_in_date BETWEEN "' . $start_date . '" AND "' . $end_date . '"');

        $query = $this->db->get();
        return $query->result();
    }

    public function today_attendance_status($id,$today)
    {
        return $this->db->select('check_in_date')
            ->from('employee_attendance')
            ->where('employee_id', $id)
            ->where('check_in_date', $today)
            ->get()
            ->result();
    }

    public function this_month_late_comings($id, $start_date, $end_date)
    {
        return $this->db->select('COUNT(check_in_date) AS time_dif')
            ->from('employee_attendance')
            ->where('late > 0')
            ->where('employee_id', $id)
            ->where('check_in_date BETWEEN "' . $start_date . '" AND "' . $end_date . '"')
            ->get()
            ->result();
    }
    public function this_month_absent_dates($id){
        return $this->db->select('check_in_date')
            ->from('employee_attendance')
            ->where('employee_id', $id)
            ->where('check_in_date BETWEEN CONCAT(year(curdate()), "-",MONTH(curdate()) , "-01" ) AND CURDATE()')
            ->order_by('check_in_date','ASC')
            ->get()
            ->result();
    }
    public function check_manager($id){
        return $this->db->select('is_manager')
            ->from('employee_history')
            ->where('employee_id',$id)
            ->get()
            ->result();
    }
    public function total_employee_in_team($id){
        return $this->db->select('COUNT(employee_id) AS total_emp')
            ->from('employee_history')
            ->where('dept_id = (SELECT dept_id FROM `employee_history` WHERE `employee_id` = '.$id.')')
            ->get()
            ->result();
    }
    public function total_employee_present_in_team($id,$today){
        return $this->db->select('COUNT(employee_id) AS present')
            ->from('employee_attendance')
            ->where('employee_id IN ( SELECT employee_id FROM `employee_history` WHERE dept_id = ( SELECT dept_id FROM `employee_history` WHERE `employee_id` = '.$id.'))')
            ->where('check_in_date',$today)
            ->get()
            ->result();
    }
    public function late_comers_with_mimutes($id,$today){
        return $this->db->select('ea.employee_id,eh.first_name,eh.last_name,TIMEDIFF(ea.check_in_time,ea.employee_shift_time) as late_minute')
            ->from('employee_attendance ea')
            ->join('employee_history eh','ea.employee_id = eh.employee_id','left')
            ->where('ea.late',1)
            ->where('ea.employee_id IN ( SELECT employee_id FROM `employee_history` WHERE dept_id = ( SELECT dept_id FROM `employee_history` WHERE `employee_id` = '.$id.'))')
            ->where('check_in_date',$today)
            ->get()
            ->result();
    }
    public function team_pending_leaves($id){
        return $this->db->select('la.employee_id,eh.first_name,eh.last_name,la.apply_strt_date,la.apply_end_date')
            ->from('leave_apply la')
            ->join('employee_history eh','la.employee_id = eh.employee_id','left')
            ->where('la.status_by_sup',0)
            ->where('la.employee_id IN ( SELECT employee_id FROM `employee_history` WHERE dept_id = ( SELECT dept_id FROM `employee_history` WHERE `employee_id` = '.$id.'))')
            ->get()
            ->result();
    }
    public function team_pending_resignation($id){
        return $this->db->select('r.employee_id,eh.first_name,eh.last_name,r.res_initiate_date,r.notice_date')
            ->from('resignation r')
            ->join('employee_history eh','r.employee_id = eh.employee_id','left')
            ->where('r.approve_by_supervisor IS NULL')
//            ->where('r.employee_id IN ( SELECT employee_id FROM `employee_history` WHERE dept_id = ( SELECT dept_id FROM `employee_history` WHERE `employee_id` = '.$id.'))')
            ->get()
            ->result();
    }
    public function over_time_pending_request($id){
        return $this->db->select('ot.employee_id,eh.first_name,eh.last_name,ot.ovt_start_date,ot.total_time')
            ->from('employee_overtime ot')
            ->join('employee_history eh','ot.employee_id = eh.employee_id','left')
            ->where('ot.approve_by_sup IS NULL')
            ->where('ot.employee_id IN ( SELECT employee_id FROM `employee_history` WHERE dept_id = ( SELECT dept_id FROM `employee_history` WHERE `employee_id` = '.$id.'))')
            ->get()
            ->result();
    }
//      inam Changings
    public function total_active_employees(){
        return $this->db->select('COUNT(*) total_emp')
            ->from('employee_history')
            ->where('employee_status',1)
            ->get()
            ->result();
    }
    public function total_present_employee($today){
        return $this->db->select('COUNT(*) present_emp ')
            ->from('employee_attendance')
            ->where('check_in_date',$today)
            ->get()
            ->result();
    }
    public function today_total_late_comers($today){
        return $this->db->select('COUNT(employee_id) as late_comer')
            ->from('employee_attendance')
            ->where('late',1)
            ->where('check_in_date',$today)
            ->get()
            ->result();
    }
    public function late_comers_with_mimutes_admin($today){
        return $this->db->select('ea.employee_id,eh.first_name,eh.last_name,TIMEDIFF(ea.check_in_time,ea.employee_shift_time) as late_minute')
            ->from('employee_attendance ea')
            ->join('employee_history eh','ea.employee_id = eh.employee_id','left')
            ->where('ea.late',1)
            ->where('check_in_date',$today)
            ->get()
            ->result();
    }
    public function pending_leaves_admin(){
        return $this->db->select('la.employee_id,eh.first_name,eh.last_name,la.apply_strt_date,la.apply_end_date')
            ->from('leave_apply la')
            ->join('employee_history eh','la.employee_id = eh.employee_id','left')
            ->where('la.status_by_sup',1)
            ->where('la.status',0)
            ->get()
            ->result();
    }
    public function over_time_pending_request_admin(){
        return $this->db->select('ot.employee_id,eh.first_name,eh.last_name,ot.ovt_start_date,ot.total_time')
            ->from('employee_overtime ot')
            ->join('employee_history eh','ot.employee_id = eh.employee_id','left')
            ->where('ot.approve_by_sup IS NULL')
            ->get()
            ->result();
    }
    public function upcomming_employee_confirmations($today,$two_week)
    {
        $this->db->select('eh.first_name,eh.last_name,eh.probation_end_date,d.department_name');
        $this->db->from('employee_history eh');
        $this->db->join('department d','eh.dept_id = d.dept_id', 'left');
        $this->db->where('eh.probation_end_date >=',$today);
        $this->db->where('eh.probation_end_date <=',$two_week);
        $this->db->where('eh.employee_status',1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
    }
    public function employee_confirmations_due($today,$two_week)
    {
        $this->db->select('eh.first_name,eh.last_name,eh.probation_end_date,d.department_name');
        $this->db->from('employee_history eh');
        $this->db->join('department d','eh.dept_id = d.dept_id', 'left');
        $this->db->where('eh.probation_end_date <',$today);
        $this->db->where('eh.probation_status',3);
        $this->db->where('eh.employee_status',1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
    }
    public function leaving_employees_this_month($start_date,$today)
    {
        $this->db->select('eh.first_name,eh.last_name,eh.voluntary_termination ,d.department_name');
        $this->db->from('employee_history eh');
        $this->db->join('department d','eh.dept_id = d.dept_id', 'left');
        $this->db->where('eh.termination_date >=',$start_date);
        $this->db->where('eh.termination_date <=',$today);
        $this->db->where('eh.voluntary_termination IN (1,2)');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
    }

    public function percentage_employee_by_department()
    {
        $this->db->select('ROUND((COUNT(eh.dept_id)/(SELECT COUNT(*) FROM `employee_history`))*100) AS per ,d.department_name');
        $this->db->from('employee_history eh');
        $this->db->join('department d','eh.dept_id = d.dept_id', 'left');
        $this->db->group_by('eh.dept_id');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        }
    }
//      Inam Changings end
}
