<?php

class cron_model extends CI_Model {

    public function __construct() {
        $this->load->database();
    }

    function count_data($table, $where = '')
    {
        if ($where) {
            foreach ($where as $key => $val) {
                $this->db->where($key, $val);
            }
        }
        $this->db->from($table);
        return $this->db->count_all_results();
    }
    
    function get_data($table, $where = '', $order = '')
    {
        $this->db->select('*');
        $this->db->from($table);
        if ($where) {
            foreach ($where as $key => $val) {
                $this->db->where($key, $val);
            }
        }
        if ($order) {
            foreach ($order as $key => $val) {
                $this->db->order_by($key, $val);
            }
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
        $query->free_result();
    }
    
    function get_value($column, $table, $where = '')
    {
         $this->db->select($column);
        $this->db->from($table);
        if ($where) {
            foreach ($where as $key => $val) {
                $this->db->where($key, $val);
            }
        }
        
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return FALSE;
        }
        $query->free_result();
    }
    
    function get_single($single, $table, $name, $value)
    {
        $this->db->select($single);
        $this->db->from($table);
        $this->db->where($name, $value);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row();
        } else {
            return FALSE;
        }
        $query->free_result();
    }
    
    function insert_data($table, $data)
    {
        $this->db->insert($table, $data);
        return $this->db->insert_id();
    }
    
    function update_data($table, $data, $where)
    {
        $res = $this->db->update($table, $data, $where);
//        if ($this->db->affected_rows()) {
        if ($res) {
            return true;
        } else {
            return false;
        }
    }

    function update_stock($id, $qty) {
        $data = ['quantity' => $qty];

        $this->db->where('item_number', $id);
        $this->db->update('items', $data);

        if ($this->db->affected_rows()) {
            return true;
        } else {
            return false;
        }
    }
    
    function sale_info($startTime, $endTime){
        $this->db->select(
                    "(SELECT MAX(sale_id) FROM ospos_sales_items WHERE item_id = b.item_id) trans_no, (SELECT item_number FROM ospos_items WHERE item_id = b.item_id) stock_id, a.location loc_code,
                    DATE(a.sale_time) trans_date, b.item_unit_price price, SUM(b.quantity_purchased) qty, AVG(b.discount_percent) discount_percent", FALSE
                );
        $this->db->from('sales a');
        $this->db->join('sales_items b', 'a.sale_id = b.sale_id', 'INNER');
        $this->db->where('a.sale_time <', $startTime);
        $this->db->where('a.sale_time >', $endTime);
        $this->db->where('a.is_sync', 0);
        $this->db->group_by(['b.item_id', 'a.location']);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
        $query->free_result();
    }
    
    function get_customers_info(){
        $this->db->select('*');
        $this->db->from('people');        
        $this->db->where('person_id NOT IN', '(SELECT person_id FROM ospos_employees)', FALSE);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
        $query->free_result();
    }
    
    function get_products($location){
         $sql = "
                SELECT  a.description, a.category_id, a.stock_id, a.long_description, b.price, a.gender, c.size, (SELECT SUM(qty) FROM stock_moves WHERE stock_id = a.stock_id AND loc_code = '".mysql_real_escape_string($location)."') qty,
                a.inactive, (SELECT d.rate FROM tax_types d INNER JOIN item_tax_type_exemptions e ON d.id = e.tax_type_id INNER JOIN item_tax_types f ON e.item_tax_type_id = f.id WHERE d.name = 'Tax' AND d.id = a.tax_type_id LIMIT 1) tax
                FROM stock_master a
                INNER JOIN prices b ON a.stock_id = b.stock_id AND b.sales_type_id = 1 AND b.curr_abrev = (SELECT VALUE FROM sys_prefs WHERE NAME = 'curr_default')
                LEFT JOIN pos_item_sizes c ON c.id = a.item_size_id
                WHERE (SELECT SUM(qty) FROM stock_moves WHERE stock_id = a.stock_id AND loc_code = '".mysql_real_escape_string($location)."') IS NOT NULL";
         
         $query = $this->db->query($sql);

        if ($query->num_rows() > 0) {
            return $query->result();
        } else {
            return FALSE;
        }
        $query->free_result();
    }

}
