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

class Congno_model extends CI_Model
{
    public function __construct()
    {
        parent::__construct();
    }

    public function get_congno_data()
    {
        $this->db->select('congno.*, tblclients.company as Ten_KH');
        $this->db->from(db_prefix() . 'congno as congno');
        $this->db->join(db_prefix() . 'clients as tblclients', 'congno.MA_KH = tblclients.userid', 'left');
        return $this->db->get()->result_array();
    }

    public function get_clients()
    {
        return $this->db->get(db_prefix() . 'clients')->result_array();
    }

    public function get_status()
    {
        return $this->db->get(db_prefix() . 'leads_status')->result_array();
    }
    public function get_xenha()
    {
        return $this->db->get(db_prefix() . 'leads_sources')->result_array();
    }
    public function insert_congno($data)
    {
        $this->db->insert(db_prefix() . 'congno', $data);
        return $this->db->insert_id();
    }

    public function update_congno_data($id, $data)
    {
        $this->db->where('id', $id);
        $this->db->update(db_prefix() . 'congno', $data);
    }

    public function get_congno_by_id($id)
    {
        return $this->db->get_where(db_prefix() . 'congno', array('id' => $id))->row();
    }
    public function get_xenha_by_id($id)
    {
        return $this->db->get_where(db_prefix() . 'leads_sources', array('id' => $id))->row();
    }

    public function delete_congno($id)
    {
        $this->db->delete(db_prefix() . 'congno', array('id' => $id));
    }

    public function update_field($id, $field, $value)
    {
        $this->db->where('id', $id);
        $this->db->update(db_prefix() . 'congno', array($field => $value));
    }

    public function get_filtered_data($filter_MA_KH = '', $filter_So_cont = '')
    {
        $this->db->select('congno.*, tblclients.company as Ten_KH');
        $this->db->from(db_prefix() . 'congno as congno');
        $this->db->join(db_prefix() . 'clients as tblclients', 'congno.MA_KH = tblclients.userid', 'left');

        if (!empty($filter_MA_KH)) {
            $this->db->where('congno.MA_KH', $filter_MA_KH);
        }

        if (!empty($filter_So_cont)) {
            $this->db->where('congno.So_cont', $filter_So_cont);
        }

        return $this->db->get()->result_array();
    }
    
    public function get_staff_data()
    {
        return $this->db->get(db_prefix() . 'staff')->result_array();
    }

    public function sync_salary_table()
    {
                $query = "
            INSERT INTO " . db_prefix() . "cluong (id, MA_KH, Ngay_ghi_so, So_cont, Diem_di, Diem_den, Loai_cont, So_xe, bien_so_xe, manual_entry)
            SELECT id, MA_KH, Ngay_ghi_so, So_cont, Diem_di, Diem_den, Loai_cont, So_xe, bien_so_xe, 0
            FROM " . db_prefix() . "congno
            ON DUPLICATE KEY UPDATE
                MA_KH = VALUES(MA_KH),
                Ngay_ghi_so = VALUES(Ngay_ghi_so),
                So_cont = VALUES(So_cont),
                Diem_di = VALUES(Diem_di),
                Diem_den = VALUES(Diem_den),
                Loai_cont = VALUES(Loai_cont),
                So_xe = VALUES(So_xe),
                bien_so_xe = VALUES(bien_so_xe)
        ";
        $this->db->query($query);

        // Lấy danh sách ID trong bảng cluong
        $query_select_ids = $this->db->select('id')
                                    ->where('manual_entry', 0) // Chỉ kiểm tra bản ghi không thêm thủ công
                                    ->get(db_prefix() . 'cluong');

        if ($query_select_ids->num_rows() > 0) {
            $cluong_ids = $query_select_ids->result_array();
            $cluong_id_array = array_column($cluong_ids, 'id');

            // Lấy danh sách ID trong bảng congno
            $query_select_congno_ids = $this->db->select('id')
                                                ->get(db_prefix() . 'congno');

            if ($query_select_congno_ids->num_rows() > 0) {
                $congno_ids = $query_select_congno_ids->result_array();
                $congno_id_array = array_column($congno_ids, 'id');

                // Xóa các bản ghi không còn trong congno
                $ids_to_delete = array_diff($cluong_id_array, $congno_id_array);
                if (!empty($ids_to_delete)) {
                    $this->db->where_in('id', $ids_to_delete)
                            ->where('manual_entry', 0) // Không xóa bản ghi thêm thủ công
                            ->delete(db_prefix() . 'cluong');
                }
            } else {
                // Chỉ xóa các bản ghi không thêm thủ công nếu congno không có dữ liệu
                $this->db->where('manual_entry', 0)
                        ->empty_table(db_prefix() . 'cluong');
            }
        }

    }
    

}
