<?php

if (!defined('BASEPATH'))
    exit('No direct script access allowed');

class checkupinfo_model extends CI_Model
{

    public $limit;
    public $offset;

    function __construct()
    {
        parent::__construct();
    }

    /* This function create checkupinfo checkupinfo. */

    function create()
    {
        $num = $this->input->post('chi_pi_num');
        $type = $this->input->post('chi_type_id');
        $sy = $this->input->post('chi_sy_id');
        $med = $this->input->post('chi_med_id');
        $xtype = $this->input->post('chi_xtype_id');
        $xsy = $this->input->post('chi_xsy_id');
        $xmed = $this->input->post('chi_xmed_id');
        $this->db->where('chi_pi_id', $num);
        $this->db->where('chi_nexttype', $type);
        $this->db->set('chi_nextdate_state', 1);
        $this->db->update('d_checkupinfo');
        $this->db->set('chi_pi_id', $this->input->post('chi_pi_id'));
        $this->db->set('chi_pi_num', $this->input->post('chi_pi_num'));
        $this->db->set('chi_move_type', $this->input->post('chi_move_type'));

        $this->db->set('chi_branch_id', $this->input->post('chi_branch_id'));
        $this->db->set('chi_date', strtotime($this->input->post('chi_date')));
        if (!empty($type)) {
            $this->db->set('chi_type_id', $type);
        }
        if (!empty($sy)) {
            $this->db->set('chi_type_id', $sy);
        }
        if (!empty($med)) {
            $this->db->set('chi_type_id', $med);
        }
        if (!empty($xtype)) {
            $this->db->set('chi_nexttype', $xtype);
        }
        if (!empty($xsy)) {
            $this->db->set('chi_nexttype', $xsy);
        }
        if (!empty($xmed)) {
            $this->db->set('chi_nexttype', $xmed);
        }
        $this->db->set('chi_nextmove', $this->input->post('chi_nextmove'));
        $this->db->set('chi_nextdate', strtotime($this->input->post('chi_nextdate')));
        $this->db->set('chi_phone', $this->input->post('chi_phone'));
        $this->db->set('chi_cost', $this->input->post('chi_cost'));
        $this->db->set('chi_address', $this->input->post('chi_address'));
        $this->db->set('chi_hospital', $this->input->post('chi_hospital'));
        $this->db->set('chi_steps', $this->input->post('chi_steps'));
        $this->db->set('chi_notice', $this->input->post('chi_notice'));
        $this->db->set('chi_created', time());
        $this->db->insert('d_checkupinfo');
        $id = $this->db->insert_id();
        $this->_update_page_assoc($id);
        // cancel alert if the checkup done
    }

    /*  This function get all checkupinfo in database sort by order asc. */

    function get_checkupinfo($row)
    {
        if (!empty($represantative)) {
            $conditions[] = 'd_rep_id = "' . $represantative . '"';
        }
        $result = $this->db->query("
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS  type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3
    ORDER BY chi_created ASC
      LIMIT $row,10");

        return $result->result_array();
    }

    /*  This function get all categories of product_type from database sort by order asc. */

    function get_checkup_category()
    {
        $result = $this->db->get('d_checkup');
        return $result->result_array();
    }

    /**
     *
     * @param integer $book_id
     * @param string $field_name the name of the field
     * @param string $value the modified value
     */
    public function insert_one_field($book_id, $value)
    {
        $this->db->set("ph_name", $value);
        $this->db->set("ph_dept_photos_id", $book_id);
        $this->db->insert($this->table);
    }

    // get total number of users
    function getNumUsersAdmin()
    {
        $query = $this->db->query("select * from d_checkupinfo");
        return $query->num_rows();
    }

    function get_product_type_active()
    {

        $this->db->where('pt_hide', '1');

        $result = $this->db->get('d_product_type_category');

        return $result->result_array();
    }

    function get_branch_active()
    {

        $this->db->where('branch_hide', '1');

        $result = $this->db->get('d_branch');

        return $result->result_array();
    }

    /* This function delete checkupinfo from database. */

    function delete($id)
    {
        $this->db->where('chi_id', $id);
        $this->db->delete('d_checkupinfo');
        return TRUE;
    }

    function is_valid_checkupinfo($id)
    {
        $this->db->where('chi_id', $id);
        $this->db->from('d_checkupinfo');
        if ($this->db->count_all_results() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function is_valid_page($id)
    {
        $this->db->where('chi_id', $id);
        $this->db->from('d_checkupinfo');
        if ($this->db->count_all_results() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function get_page($id)
    {
        $this->db->where('chi_id', $id);
        $result = $this->db->get('d_checkupinfo');
        if ($result->num_rows() > 0) {
            return $result->row_array();
        } else {
            return FALSE;
        }
    }

    function _get_next_page($direction, $order_by)
    {
        if ($direction == 'up') {
            $this->db->where("chi_ord < $order_by");
            $this->db->order_by('chi_ord', 'desc');
            $this->db->limit(1, 0);
        } else {
            $this->db->where("chi_ord > $order_by");
            $this->db->order_by('chi_ord', 'asc');
            $this->db->limit(1, 0);
        }
        $result = $this->db->get('d_checkupinfo');
        if ($result->num_rows() == 1) {
            return $result->row_array();
        } else {
            return FALSE;
        }
    }

    function sort($direction, $id)
    {
        $page = $this->get_page($id);
        $next_page = $this->_get_next_page($direction, $page['chi_ord']);
        if (is_array($next_page)) {
            $this->db->set('chi_ord', $next_page['chi_ord']);
            $this->db->where('chi_id', $page['chi_id']);
            $this->db->update('d_checkupinfo');

            $this->db->set('chi_ord', $page['chi_ord']);
            $this->db->where('chi_id', $next_page['chi_id']);
            $this->db->update('d_checkupinfo');
            return TRUE;
        } else {
            return FALSE;
        }
    }

    function _update_page_assoc($id)
    {
        $this->db->select_max('chi_ord');
        $result = $this->db->get('d_checkupinfo');
        $row = $result->row();
        $max_ord = $row->chi_ord + 1;
        $this->db->where('chi_id', $id);
        $this->db->set('chi_ord', $max_ord);
        $this->db->update('d_checkupinfo');
        return TRUE;
    }

    function activate($id)
    {
        $this->db->set('chi_hide', '1');
        $this->db->where('chi_id', $id);
        $this->db->update('d_checkupinfo');
    }

    function deactivate($id)
    {
        $this->db->set('chi_hide', '0');
        $this->db->where('chi_id', $id);
        $this->db->update('d_checkupinfo');
    }

    function update($id)
    {
        $type = $this->input->post('chi_type_id');
        $sy = $this->input->post('chi_sy_id');
        $med = $this->input->post('chi_med_id');
        $num = $this->input->post('chi_pi_num');
        $mov = $this->input->post('chi_move_type');
        $xtype = $this->input->post('chi_xtype_id');
        $xsy = $this->input->post('chi_xsy_id');
        $xmed = $this->input->post('chi_xmed_id');
        if (!empty($type) and $type != 0) {
            $this->db->set('chi_type_id', $type);
        }
        if (!empty($mov) and $mov != 0) {
            $this->db->set('chi_move_type', $this->input->post('chi_move_type'));
        }
        if (!empty($sy) and $sy != 0) {
            $this->db->set('chi_type_id', $sy);
        }
        if (!empty($med) and $med != 0) {
            $this->db->set('chi_type_id', $med);

        }
        if (!empty($xtype) and $xtype != 0) {
            $this->db->set('chi_nexttype', $xtype);
        }
        if (!empty($xsy) and $xsy != 0) {
            $this->db->set('chi_nexttype', $sy);
        }
        if (!empty($xmed) and $xmed != 0) {
            $this->db->set('chi_nexttype', $xmed);
        }
        if (!empty($xmov) and $xmov != 0) {
            $this->db->set('chi_nextmove', $this->input->post('chi_nextmove'));
        }

        $this->db->where('chi_id', $id);
        $this->db->set('chi_pi_id', $this->input->post('chi_pi_id'));
        $this->db->set('chi_pi_num', $this->input->post('chi_pi_num'));
        $this->db->set('chi_branch_id', $this->input->post('chi_branch_id'));
        $this->db->set('chi_date', strtotime($this->input->post('chi_date')));
        $this->db->set('chi_nextdate', strtotime($this->input->post('chi_nextdate')));
        $this->db->set('chi_phone', $this->input->post('chi_phone'));
        $this->db->set('chi_cost', $this->input->post('chi_cost'));
        $this->db->set('chi_address', $this->input->post('chi_address'));
        $this->db->set('chi_hospital', $this->input->post('chi_hospital'));
        $this->db->set('chi_steps', $this->input->post('chi_steps'));
        $this->db->set('chi_notice', $this->input->post('chi_notice'));
        $this->db->set('chi_created', time());
        $this->db->update('d_checkupinfo');

        $this->db->where('chi_pi_id', $num);
        $this->db->where('chi_nexttype', $type);
        $this->db->set('chi_nextdate_state', 1);
    }

    function get_branch_category($id)
    {
        $this->db->where('branch_id', $id);

        $result = $this->db->get('d_branch');
        return $result->row();
    }

    function get_checkupinfo_one($id)
    {
        $result = $this->db->query("SELECT
                    d_checkupinfo.*,
                      d_patient.*
                    FROM d_checkupinfo
                      INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
                    WHERE d_checkupinfo.chi_id = $id 
");
        return $result->row();
    }

    function operation()
    {
        if (!empty($_POST['check_nf'])) {
            $products = $_POST['check_nf'];
            for ($i = 0; $i <= count($products) - 1; $i++) {
                switch ($_POST['operation']) {
                    case "active":
                        $this->db->where('chi_id', $products[$i]);
                        $this->db->set('chi_hide', '1');
                        $this->db->update('d_checkupinfo');
                        $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_active_all'));
                        break;

                    case "inactive":
                        $this->db->where('chi_id', $products[$i]);
                        $this->db->set('chi_hide', '0');
                        $this->db->update('d_checkupinfo');
                        $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_inactive_all'));
                        break;

                    case "delete":
                        $this->db->where('chi_id', $products[$i]);
                        $result = $this->db->get('d_checkupinfo');
                        $r = $result->row();
                        $this->db->where('chi_id', $products[$i]);
                        $this->db->delete('d_checkupinfo');
                        $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_del_all'));
                        break;
                }
            }
        }

    }

    function get_category()
    {
        $result = $this->db->get('d_product_type_category');
        return $result->result_array();
    }

    function get_all()
    {
        $result = $this->db->query('SELECT 
  d_checkupinfo.*,
  d_checkup.*,
  d_branch.*,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id');
        return $result->result_array();
    }

    function get_one($id)
    {
        $result = $this->db->query(
            "
        SELECT 
          d_checkupinfo.*,
          d_branch.*,
          d_patient.*,
          d_checkup.*
        FROM d_checkupinfo
          INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
          INNER JOIN d_patient ON d_checkupinfo.chi_pi_id = d_patient.pi_id
          INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
          where d_checkupinfo.chi_id = $id"
        );
        return $result->row();
    }

    function get_bycheckup($checkup)
    {
        $result = $this->db->query(
            "
SELECT 
  d_checkupinfo.*,
  d_checkup.*,
  d_branch.*,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  where d_checkupinfo.chi_type_id = $checkup"
        );
        return $result->result_array();
    }

    ////////////////////////////////////////////////////////////////////////////////////////////////
    //////////////frontend//////////////////////////////////////////////////////////


    function add_count($count, $id)
    {
        $this->db->where('chi_id', $id);
        $this->db->set('chi_count_visit', ($count + 1));
        $this->db->update('d_checkupinfo');
    }

    function _update_page_assoc2($id)
    {
        $this->db->select_max('cn_ord');
        $result = $this->db->get('d_comments_checkupinfo');
        $row = $result->row();
        $max_ord = $row->cn_ord + 1;
        $this->db->where('cn_id', $id);
        $this->db->set('cn_ord', $max_ord);
        $this->db->update('d_comments_checkupinfo');
        return TRUE;
    }

    /////////////////////////////////paging////////////////////////////
    // get 5 rows at a time
    function getUsers($row)
    {
        $this->db->order_by('chi_created', 'desc');
        $this->db->where('chi_hide', 1);
        $query = $this->db->get('d_checkupinfo', 5, $row);
        if ($query->num_rows() > 0) {
            // return result set as an associative array
            return $query->result_array();
        }
    }

    // get total number of users
    function getNumUsers()
    {
        $query = $this->db->query("select * from d_checkupinfo where chi_hide = '1'");
        return $query->num_rows();
    }

    // get total number of users
    function getNumbycat($pt_id)
    {
        $query = $this->db->query("select * from d_checkupinfo where chi_product_type_id = $pt_id ");
        return $query->num_rows();
    }

    //////////////////////////////////end paging///////////////////

    /**
     * join between d_country and d_cities to bind the country name behind the
     * city name
     * @param string $KeyWord
     * @return array
     */
    public function get_autocomplete($pi_name)
    {
        $query = $this->db->query(
            "SELECT
  d_patient.pi_id,
  d_patient.pi_name,
  d_patient.pi_father,
  d_patient.pi_family,
  d_patient.pi_num,
  d_branch.branch_name,
  d_patient.pi_branch_id
FROM d_branch
  INNER JOIN d_patient
    ON d_branch.branch_id = d_patient.pi_branch_id
    WHERE '$pi_name'
      LIKE Concat(Concat('%',d_patient.pi_name), '%')
      OR '$pi_name'
         LIKE Concat(Concat('%',d_patient.pi_father), '%')
      OR '$pi_name'
         LIKE Concat(Concat('%',d_patient.pi_family), '%')
    "
        );
        return $query;
    }

    function search_all()
    {

        $chi_pi_id = $this->input->post('chi_pi_id');
        $chi_cost = $this->input->post('chi_cost');
        $chi_date = strtotime($this->input->post('chi_date'));
        $chi_date2 = strtotime($this->input->post('chi_date2'));
        $pi_name = $this->input->post('pi_name');
        $chi_type_id = $this->input->post('chi_type_id');
        $chi_med_id = $this->input->post('chi_med_id');
        $chi_sy_id = $this->input->post('chi_sy_id');
        $chi_move_type = $this->input->post('chi_move_type');
        $pi_branch_id = $this->input->post('pi_branch_id');
        $conditions = array();
        if ($chi_pi_id) {
            $conditions[] = 'chi_pi_id = "' . $chi_pi_id . '"';
        }
        if ($chi_cost) {
            $conditions[] = 'chi_cost = "' . $chi_cost . '"';
        }

        if ($chi_date !== '' AND $chi_date2 !== '') {
            $conditions[] = "AND (chi_date  BETWEEN '" . $chi_date . "' AND '" . $chi_date2 . "')";
        }
        if ($pi_name) {
            $conditions[] = 'CONCAT(d_patient.pi_name, " ", d_patient.pi_father," ",d_patient.pi_family) = "' . $pi_name . '"';
        }

        if (!empty($chi_move_type)) {

            if($chi_type_id){
                $conditions[] = 'chi_type_id = "' . $chi_type_id . '"';
            }
            if($chi_sy_id){

                $conditions[] = 'chi_type_id = "' . $chi_sy_id . '"';
            }
            if($chi_med_id){
                $conditions[] = 'chi_type_id = "' . $chi_med_id . '"';
            }
            if ($chi_move_type == 1){
                $select = "SELECT
d_checkupinfo.*,
d_branch.branch_name,
d_checkup.checkup_name AS type_name,
d_patient.*
FROM d_checkupinfo
INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1 ";
            } elseif ($chi_move_type == 2) {
                $select = "SELECT
d_checkupinfo.*,
d_branch.branch_name,
d_surgery.surgery_name AS  type_name,
d_patient.*
FROM d_checkupinfo
INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2 ";
            }  elseif ($chi_move_type == 3) {
                $select = "SELECT
d_checkupinfo.*,
d_branch.branch_name,
d_medicaleq.medicaleq_name AS type_name,
d_patient.*
FROM d_checkupinfo
INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3 ";
            }
        }else{
            $select = "SELECT
d_checkupinfo.*,
d_branch.branch_name,
d_checkup.checkup_name AS type_name,
d_patient.*
FROM d_checkupinfo
INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
d_checkupinfo.*,
d_branch.branch_name,
d_surgery.surgery_name AS  type_name,
d_patient.*
FROM d_checkupinfo
INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
d_checkupinfo.*,
d_branch.branch_name,
d_medicaleq.medicaleq_name AS type_name,
d_patient.*
FROM d_checkupinfo
INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3 ";

        }


        if ($pi_branch_id) {
            $conditions[] = 'pi_branch_id = "' . $pi_branch_id . '"';
        }


        $sqlStatement = $select.implode(' AND ', $conditions);

        $result = $this->db->query($sqlStatement);

        return $result->result_array();
    }
    function search_checkup()
    {
        $chi_pi_id = $this->input->post('chi_pi_id');
        $chi_cost = $this->input->post('chi_cost');
        $chi_date = strtotime($this->input->post('chi_date'));
        $chi_date2 = strtotime($this->input->post('chi_date2'));
        $pi_name = $this->input->post('pi_name');
        $chi_type_id = $this->input->post('chi_type_id');
        $pi_branch_id = $this->input->post('pi_branch_id');
        $conditions = array();

        if ($chi_pi_id) {
            $conditions[] = 'chi_pi_id = "' . $chi_pi_id . '"';
        }
        if ($chi_cost) {
            $conditions[] = 'chi_cost = "' . $chi_cost . '"';
        }

        if ($chi_date !== '' AND $chi_date2 !== '') {
            $conditions[] = "(chi_date  BETWEEN '" . $chi_date . "' AND '" . $chi_date2 . "')";
        }
        if ($pi_name) {
            $conditions[] = 'CONCAT(d_patient.pi_name, " ", d_patient.pi_father," ",d_patient.pi_family) = "' . $pi_name . '"';
        }
        if ($chi_type_id) {
            $conditions[] = 'chi_type_id = "' . $chi_type_id . '"';
        }
        if ($pi_branch_id) {
            $conditions[] = 'pi_branch_id = "' . $pi_branch_id . '"';
        }


        $sqlStatement = 'SELECT 
   d_checkup.checkup_id,
   d_checkup.checkup_name
FROM d_checkupinfo
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE ' . implode(' AND ', $conditions) . 'GROUP BY checkup_id';
        $result = $this->db->query($sqlStatement);

        return $result->result_array();
    }

/////////////     all dashboard counter ///////////////////////////
    function count_all()
    {
        $query = $this->db->query(" 
    select 
    sum(a.count)  AS count
    from
    (select count(*) as count from d_checkupinfo
     union all
     select count(*) as count from d_charge) a
              ");
        return $query->row();
    }

    function count_all_month()
    {
        $query = $this->db->query(" 
    select 
    sum(a.count)  AS count
    from
    (
    select count(*) as count from d_checkupinfo WHERE  EXTRACT(month FROM (NOW())) = MONTH(FROM_UNIXTIME(chi_date))
     union all
     select count(*) as count from d_charge WHERE  EXTRACT(month FROM (NOW())) = MONTH(FROM_UNIXTIME(chg_date))  
     ) a
              ");
        return $query->row();
    }

    function count_all_checkupinfo()
    {
        $query = $this->db->query("
select d_checkup.checkup_name, count(*) AS count,
 (SELECT count(*) FROM d_checkupinfo AS Y WHERE Y.chi_type_id=x.chi_type_id AND EXTRACT(month FROM (NOW())) = MONTH(FROM_UNIXTIME(Y.chi_date))) AS a,
   (SELECT count(*) FROM d_checkupinfo AS Y WHERE Y.chi_type_id=x.chi_type_id) AS b
from d_checkupinfo AS x
  INNER JOIN d_checkup ON x.chi_type_id = d_checkup.checkup_id 
GROUP by chi_type_id 
  ");
        return $query->result_array();
    }

    function count_all_charge()
    {
        $query = $this->db->query("
SELECT 
(SELECT count(*) FROM d_charge WHERE  EXTRACT(month FROM (NOW())) >= MONTH(FROM_UNIXTIME(chg_date))) AS ct,
(SELECT count(*) as month_count FROM d_charge WHERE  EXTRACT(month FROM (NOW())) = MONTH(FROM_UNIXTIME(chg_date))) AS month_count
from  d_charge
  ");
        return $query->row();
    }

    function notify_nextcheckupinfo()
    {
        $query = $this->db->query("
SELECT
  d_checkupinfo.*,
  d_checkupnext_alert.chx_days
FROM d_checkupnext_alert,
     d_checkupinfo
  WHERE CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL chx_days day) AND FROM_UNIXTIME(chi_nextdate) and chi_nextdate_state = 0
  ");
        return $query->result_array();
    }

    function print_all_notify($days)
    {
        $result = $this->db->query("
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo

  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
WHERE chi_move_type = 1
      AND CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL 2 DAY) AND FROM_UNIXTIME(chi_nextdate) AND
      chi_nextdate_state = 0

UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery ON d_checkupinfo.chi_type_id = d_surgery.surgery_id
WHERE chi_move_type = 2
      AND CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL 2 DAY) AND FROM_UNIXTIME(chi_nextdate) AND
      chi_nextdate_state = 0
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq ON d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id
    AND CURDATE() BETWEEN DATE_SUB(FROM_UNIXTIME(chi_nextdate), INTERVAL 2 DAY) AND FROM_UNIXTIME(chi_nextdate) AND
                            chi_nextdate_state = 0
WHERE chi_move_type = 3");
        return $result->result_array();
    }

    function get_max_id()
    {
        $result = $this->db->query('SELECT chi_id
                                    FROM d_checkupinfo
                                    ORDER BY chi_id DESC
                                    LIMIT 1');
        return $result->row();
    }

/////// auto filter search /////////////////
    public function listBarang()
    {
        $result = $this->db->query('SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS  type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3 ');
        $this->db->limit($this->limit, $this->offset);
        return $result->result_array();
    }

    public function listdelyaed($row)
    {
        $result = $this->db->query("
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS  type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3
AND  chi_nexttype != 0 and chi_nextdate_state = 0
limit $row,10

");
        return $result->result_array();
    }

    public function dnumRec()
    {
        $result = $this->db->query("
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS  type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3
AND  chi_nexttype != 0 and chi_nextdate_state = 0
            ");
        return $result->num_rows();
    }

    public function listBarang_page($descp)
    {
        $result = $this->db->query("SELECT x.*,x.branch_name,x.type_name FROM (
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id
WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery ON d_checkupinfo.chi_type_id = d_surgery.surgery_id
WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq ON d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id
WHERE chi_move_type = 3 ) x
WHERE   Concat(Concat('%',x.pi_name,' ', x.pi_father,' ',x.pi_family), '%') LIKE '%$descp%'
OR x.chi_pi_id = '$descp'
OR x.chi_cost =  '$descp'
LIMIT $this->limit OFFSET $this->offset
  ");
        return $result->result_array();
    }

    public function numRec()
    {
        $result = $this->db->query('SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS  type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3 ');
        $this->db->count_all('d_checkupinfo');
        return $result;
    }

    public function numRec_page($descp)
    {
        $result = $this->db->query('SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_checkup.checkup_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_checkup ON d_checkupinfo.chi_type_id = d_checkup.checkup_id WHERE chi_move_type = 1
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_surgery.surgery_name AS  type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_surgery on d_checkupinfo.chi_type_id = d_surgery.surgery_id WHERE chi_move_type = 2
UNION
SELECT
  d_checkupinfo.*,
  d_branch.branch_name,
  d_medicaleq.medicaleq_name AS type_name,
  d_patient.*
FROM d_checkupinfo
  INNER JOIN d_branch ON d_checkupinfo.chi_branch_id = d_branch.branch_id
  INNER JOIN d_patient ON d_checkupinfo.chi_pi_num = d_patient.pi_id
  INNER JOIN d_medicaleq on d_checkupinfo.chi_type_id = d_medicaleq.medicaleq_id WHERE chi_move_type = 3 ');
        $this->db->count_all_results('d_checkupinfo', $this->limit, $this->offset);
        return $result;
    }

}
