<?php

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

class charge_model extends CI_Model {

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

    /* This function create charge charge. */

    function create() {
        $this->db->set('chg_pi_id', $this->input->post('chg_pi_id'));
        $this->db->set('chg_pi_num', $this->input->post('chg_pi_num'));
        $this->db->set('chg_branch_id', $this->input->post('chg_branch_id'));
        $this->db->set('chg_date', strtotime($this->input->post('chg_date')));
        $this->db->set('chg_type', $this->input->post('chg_type'));
        $this->db->set('chg_phone', $this->input->post('chg_phone'));
        $this->db->set('chg_cost', $this->input->post('chg_cost'));
        $this->db->set('chg_address', $this->input->post('chg_address'));
        $this->db->set('chg_steps', $this->input->post('chg_steps'));
        $this->db->set('chg_notice', $this->input->post('chg_notice'));
        $this->db->set('chg_created', time());
        $this->db->insert('d_charge');
        $id = $this->db->insert_id();
        $this->_update_page_assoc($id);
        return TRUE;
    }

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

    function get_charge($row) {
        $result = $this->db->query("SELECT 
  d_charge.*,
  d_branch.*,
  d_patient.*
FROM d_charge
  LEFT JOIN d_branch ON d_charge.chg_branch_id = d_branch.branch_id
  LEFT JOIN d_patient ON d_charge.chg_pi_num = d_patient.pi_id
    ORDER BY chg_id 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_charge");
        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_category($id) {
        $this->db->where('branch_id', $id);

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

    function get_branch_active() {

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

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

        return $result->result_array();
    }

    /* This function delete charge from database. */

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

    function is_valid_charge($id) {
        $this->db->where('chg_id', $id);
        $this->db->from('d_charge');
        if ($this->db->count_all_results() > 0) {
            return TRUE;
        } else {
            return FALSE;
        }
    }

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

    function get_page($id) {
        $this->db->where('chg_id', $id);
        $result = $this->db->get('d_charge');
        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("chg_ord < $order_by");
            $this->db->order_by('chg_ord', 'desc');
            $this->db->limit(1, 0);
        } else {
            $this->db->where("chg_ord > $order_by");
            $this->db->order_by('chg_ord', 'asc');
            $this->db->limit(1, 0);
        }
        $result = $this->db->get('d_charge');
        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['chg_ord']);
        if (is_array($next_page)) {
            $this->db->set('chg_ord', $next_page['chg_ord']);
            $this->db->where('chg_id', $page['chg_id']);
            $this->db->update('d_charge');

            $this->db->set('chg_ord', $page['chg_ord']);
            $this->db->where('chg_id', $next_page['chg_id']);
            $this->db->update('d_charge');
            return TRUE;
        } else {
            return FALSE;
        }
    }

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

    function activate($id) {
        $this->db->set('chg_hide', '1');
        $this->db->where('chg_id', $id);
        $this->db->update('d_charge');
    }

    function deactivate($id) {
        $this->db->set('chg_hide', '0');
        $this->db->where('chg_id', $id);
        $this->db->update('d_charge');
    }

    function update($id) {
        $this->db->where('chg_id', $id);
        $this->db->set('chg_pi_id', $this->input->post('chg_pi_id'));
        $this->db->set('chg_pi_num', $this->input->post('chg_pi_num'));
        $this->db->set('chg_branch_id', $this->input->post('chg_branch_id'));
        $this->db->set('chg_date', strtotime($this->input->post('chg_date')));
        $this->db->set('chg_type', $this->input->post('chg_type'));
        $this->db->set('chg_phone', $this->input->post('chg_phone'));
        $this->db->set('chg_cost', $this->input->post('chg_cost'));
        $this->db->set('chg_address', $this->input->post('chg_address'));
        $this->db->set('chg_steps', $this->input->post('chg_steps'));
        $this->db->set('chg_notice', $this->input->post('chg_notice'));
        $this->db->set('chg_created', time());
        $this->db->update('d_charge');
        return TRUE;
    }

    function get_charge_one($id) {
        $result = $this->db->query("SELECT
                    d_charge.*,
                      d_patient.*
                    FROM d_charge
                      LEFT JOIN d_patient ON d_charge.chg_pi_num = d_patient.pi_id
                    WHERE d_charge.chg_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('chg_id', $products[$i]);
                        $this->db->set('chg_hide', '1');
                        $this->db->update('d_charge');
                        $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_active_all'));
                        break;

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

                    case "delete":
                        $this->db->where('chg_id', $products[$i]);
                        $result = $this->db->get('d_charge');
                        $r = $result->row();
                        $this->db->where('chg_id', $products[$i]);
                        $this->db->delete('d_charge');
                        $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_del_all'));
                        break;
                }
            }
        }
//        if (isset($_POST['check_nf2'])) {
//            $products = $_POST['check_nf2'];
//            switch ($_POST['operation']) {
//                case "active":
//                    $this->db->set('chg_hide', '1');
//                    $this->db->update('d_charge');
//                    $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_active_all'));
//                    break;
//                case "inactive":
//                    $this->db->set('chg_hide', '0');
//                    $this->db->update('d_charge');
//                    $this->session->set_flashdata('success_msg', $this->lang->line('success_msg_inactive_all'));
//                    break;
//                case "delete":
//                    $result = $this->db->get('d_charge');
//                    $r = $result->result_array();
//                    foreach ($r as $rm):
//                        if (file_exists(PUBPATH . "global/uploads/" . $rm['chg_img'])) {
//                            unlink(PUBPATH . "global/uploads/" . $rm['chg_img']);
//                        }
//                        $this->db->delete('d_charge');
//                    endforeach;
//                    $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_charge.*,
  d_checkup.*,
  d_branch.*,
  d_patient.*
FROM d_charge
  LEFT JOIN d_checkup ON d_charge.chg_type = d_checkup.checkup_id
  LEFT JOIN d_branch ON d_charge.chg_branch_id = d_branch.branch_id
  LEFT JOIN d_patient ON d_charge.chg_pi_num = d_patient.pi_id');
        return $result->result_array();
    }

    function get_one($id) {
        $result = $this->db->query(
                " 
SELECT 
  d_charge.*,
  d_checkup.*,
  d_branch.*,
  d_patient.*
FROM d_charge
  LEFT JOIN d_checkup ON d_charge.chg_type = d_checkup.checkup_id
  LEFT JOIN d_branch ON d_charge.chg_branch_id = d_branch.branch_id
  LEFT JOIN d_patient ON d_charge.chg_pi_num = d_patient.pi_id
  where d_charge.chg_id = $id");
        return $result->row();
    }

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


    function add_count($count, $id) {
        $this->db->where('chg_id', $id);
        $this->db->set('chg_count_visit', ($count + 1));
        $this->db->update('d_charge');
    }

    function _update_page_assoc2($id) {
        $this->db->select_max('cn_ord');
        $result = $this->db->get('d_comments_charge');
        $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_charge');
        return TRUE;
    }

    /////////////////////////////////paging////////////////////////////
    // get 5 rows at a time
    function getUsers($row) {
        $this->db->order_by('chg_created', 'desc');
        $this->db->where('chg_hide', 1);
        $query = $this->db->get('d_charge', 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_charge where chg_hide = '1'");
        return $query->num_rows();
    }

    // get total number of users
    function getNumbycat($pt_id) {
        $query = $this->db->query("select * from d_charge where chg_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 d_patient.pi_name like '%" . $pi_name . "%'"
        );
        return $query;
    }

    function search_all() {
        $chg_pi_id = $this->input->post('chg_pi_id');
        $chg_cost = $this->input->post('chg_cost');
        $chg_date = strtotime($this->input->post('chg_date'));
        $chg_date2 = strtotime($this->input->post('chg_date2'));
        $pi_name = $this->input->post('pi_name');
        $chg_type = $this->input->post('chg_type');
        $pi_branch_id = $this->input->post('pi_branch_id');
        $conditions = array();

        if ($chg_pi_id) {
            $conditions[] = 'chg_pi_id = "' . $chg_pi_id . '"';
        }
        if ($chg_cost) {
            $conditions[] = 'chg_cost = "' . $chg_cost . '"';
        }
        if ($chg_date !== '' AND $chg_date2 !== '') {
            $conditions[] = "(chg_date  BETWEEN '" . $chg_date . "' AND '" . $chg_date2 . "')";
        }

        if ($pi_name) {
            $conditions[] = 'CONCAT(d_patient.pi_name, " ", d_patient.pi_father," ",d_patient.pi_family) = "' . $pi_name . '"';
        }
        if ($chg_type) {
            $conditions[] = 'chg_type = "' . $chg_type . '"';
        }
        if ($pi_branch_id) {
            $conditions[] = 'pi_branch_id = "' . $pi_branch_id . '"';
        }


        $sqlStatement = 'SELECT 
  d_charge.*,
  d_branch.*,
  d_checkup.*,
  d_patient.*
FROM d_charge
  LEFT JOIN d_patient ON d_charge.chg_pi_num = d_patient.pi_id
  LEFT JOIN d_branch ON d_charge.chg_branch_id = d_branch.branch_id
  LEFT JOIN d_checkup ON d_charge.chg_type = d_checkup.checkup_id WHERE ' . implode(' AND ', $conditions)
        ;
        $result = $this->db->query($sqlStatement);

        return $result->result_array();
    }

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

/////// auto filter search /////////////////

    public function listBarang() {
        $result = $this->db->select('d_charge.*,d_branch.*,d_patient.*')
                ->limit($this->limit, $this->offset)
                ->join('d_branch', 'd_charge.chg_branch_id = d_branch.branch_id', 'left')
                ->join('d_patient', 'd_charge.chg_pi_num = d_patient.pi_id', 'left')
                ->get('d_charge');
        return $result->result_array();
    }

    public function listBarang_page($descp) {
        $result = $this->db->select('d_charge.*,d_branch.*,d_patient.*')
                ->limit($this->limit, $this->offset)
                ->join('d_branch', 'd_charge.chg_branch_id = d_branch.branch_id', 'left')
                ->join('d_patient', 'd_charge.chg_pi_num = d_patient.pi_id', 'left')
                ->like('CONCAT(d_patient.pi_name, " ", d_patient.pi_father," ",d_patient.pi_family)', $descp)
                ->or_like('chg_pi_num', $descp)
                ->or_like('chg_cost', $descp)
                ->get('d_charge', $this->limit, $this->offset);
        return $result->result_array();
    }

    public function numRec() {
        $query = $this->db->count_all("d_charge");
        return $query;
    }

    public function numRec_page($descp) {
        $result = $this->db->select('d_charge.*,d_branch.*,d_patient.*')
                ->like('CONCAT(d_patient.pi_name, " ", d_patient.pi_father," ",d_patient.pi_family)', $descp)
                ->join('d_branch', 'd_charge.chg_branch_id = d_branch.branch_id', 'left')
                ->join('d_patient', 'd_charge.chg_pi_num = d_patient.pi_id', 'left')
                ->count_all_results('d_charge', $this->limit, $this->offset);
        return $result;
    }

/////////////     all json queries for iphone and android ///////////////////////////
//                      GROUP BY d_charge.chg_name
}
