<?php

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

class Admin_db extends CI_Model
{

    public function __construct()
    {
        $this->load->library('session');
        $this->load->database();
        parent::__construct();
    }

    public function get_custom_table_data($sql, $num_of_row = false)
    {

        $query = $this->db->query($sql);

        if ($num_of_row) {
            return $query->num_rows();

        }

        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_admin_details()
    {
        //get admin details
        $this->db->select('*');
        $this->db->from('tbl_admin_user');
        $this->db->where('id', $this->session->userdata('admin_id'));
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false; // admin is not Exists
        }
    }
    
    public function admin_login($username, $password)
    {
        //check inputed username and password is tally
        $this->db->select('*');
        $this->db->from('tbl_admin_user');
        $this->db->where('username', $username);
        $this->db->where('password', $password);
        $this->db->where('status', 1);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $row = $query->row();
            $newdata = array(
                'admin_id' => $row->id,
                'contact_name' => $row->contact_name,
                'user_type_id' => $row->user_type_id,
                'admin_email' => $username,
                'isLoggedIn' => true);
            $this->session->set_userdata($newdata);
            return true;
        } else {
            return false; // Admin username&Password not Exists
        }
    }

    public function save_admin_user_log($agent, $platform)
    {
        // insert user log details
        if ('A_' . $this->session->userdata('admin_id') != null) {
            $data = array(
                'browser' => $agent,
                'user_ip' => $this->input->ip_address(),
                'platform' => $platform,
                'logged_on' => date('Y-m-d H:i:s'),
                'user_id' => $this->session->userdata('admin_id'));
            if ($this->db->insert('tbl_admin_user_log', $data)) {
                return true; // sucessfully inserted user log details
            } else {
                return false; // there is some error when saving data
            }
        }
    }

    public function log_out()
    {
        // admin logout from the system
        $newdata = array('admin_id' => null,
            'isLoggedIn' => false);

        $this->session->set_userdata($newdata);
    }

    public function change_user_privilege_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_user_types', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_user_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_admin_user', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_user_by_id($id)
    {
        $this->db->select('au.id, au.user_type_id, contact_name, contact_number, user_privilege, username, au.status AS user_status, ut.status AS privilege_status');
        $this->db->from('tbl_admin_user au');
        $this->db->join('tbl_user_types ut','au.user_type_id = ut.id');
        $this->db->where('au.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_user_types()
    {
        $this->db->distinct();
        $this->db->select('id, user_privilege');
        $this->db->from('tbl_user_types');
        $this->db->order_by('user_privilege', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }

    public function save_edit_user_details($id, $data)
    {
        $data = array(
            'contact_name' => $data['contact_name'],
            'contact_number' => $data['contact_number'],
            'username' => $data['username'],
            'user_type_id' => $data['user_type'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_admin_user', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_user_password($id, $data)
    {
        $data = array(
            'password' => $data['password'],

        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_admin_user', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function check_username_existing($username, $user_id = null)
    {
        $this->db->select('*');
        $this->db->from('tbl_admin_user');
        $this->db->where('username', $username);
        if($user_id != null){
            $this->db->where('id !=', $user_id);
        }
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            return true;
        } else {
            return false;
        }

    }
    
    public function save_add_user_details($data)
    {
        $data = array(
            'contact_name' => $data['contact_name'],
            'contact_number' => $data['contact_number'],
            'username' => $data['username'],
            'password' => $data['password'],
            'user_type_id' => $data['user_type'],
            'status' => 1, // 1 - active
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_admin_user', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    function get_all_privileges() {
        //select all modules
        $this->db->select('*');
        $this->db->from('tbl_admin_privileges');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = false;
        }
        return $data;
    }

    public function update_admin_module_status($privilege, $module_id, $check)
    {
        //update module status

        $update_data = array($privilege => $check,
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'));
        $this->db->where('module_id', $module_id);
        if ($this->db->update('tbl_admin_privileges', $update_data)) {
            return true;
        } else {
            return false;
        }
    }

    public function get_admin_privilege_modules($privilege)
    {
        $this->db->select('module_id, module_name');
        $this->db->from('tbl_admin_privileges');
        $this->db->where($privilege, '1');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false; // admin is not Exists
        }
    }
    
    public function save_add_customer_details($data)
    {
        $data = array(
            'customer_name' => $data['customer_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'customer_type' => $data['customer_type'],
            'status' => 1, // 1 - active
            'send_sms' => 1, // 1 - Yes, 0 - No
            'debt_amount_gold' => 0.000,
            'debt_amount_cash' => 0.00,
            'over_paid_gold' => $data['over_paid_gold'],
            'over_paid_cash' => $data['over_paid_cash'],
            'box_debt_amount_gold' => 0.000,
            'box_debt_amount_cash' => 0.00,
            'box_over_paid_gold' => $data['box_over_paid_gold'],
            'box_over_paid_cash' => $data['box_over_paid_cash'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_customers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_customer_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_customers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function update_customer_accounts($id, $amounts)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'debt_amount_gold' => $amounts['debt_amount_gold'],
            'debt_amount_cash' => $amounts['debt_amount_cash'],
            'over_paid_gold' => $amounts['over_paid_gold'],
            'over_paid_cash' => $amounts['over_paid_cash'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_customers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function update_customer_box_accounts($id, $amounts)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'box_debt_amount_gold' => $amounts['box_debt_amount_gold'],
            'box_debt_amount_cash' => $amounts['box_debt_amount_cash'],
            'box_over_paid_gold' => $amounts['box_over_paid_gold'],
            'box_over_paid_cash' => $amounts['box_over_paid_cash'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_customers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_customer_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_customers');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_customer_details($id, $data)
    {
        $data = array(
            'customer_name' => $data['customer_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'customer_type' => $data['customer_type'],
            'over_paid_gold' => $data['over_paid_gold'],
            'over_paid_cash' => $data['over_paid_cash'],
            'box_over_paid_gold' => $data['box_over_paid_gold'],
            'box_over_paid_cash' => $data['box_over_paid_cash'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_customers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_customers_to_do_sales()
    {
        $this->db->distinct();
        $this->db->select('id, customer_name, contact_number');
        $this->db->from('tbl_customers');
        $this->db->where('status !=', 3);
        $this->db->order_by('customer_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }

    public function get_24k_customers_to_do_24k_sales()
    {
        $this->db->distinct();
        $this->db->select('id, customer_name, contact_number');
        $this->db->from('tbl_24k_customers');
        $this->db->where('status !=', 3);
        $this->db->order_by('customer_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }

    public function get_carriers_to_do_purchases()
    {
        $this->db->distinct();
        $this->db->select('id, carrier_name, contact_number');
        $this->db->from('tbl_carriers');
        $this->db->where('status !=', 3);
        $this->db->order_by('carrier_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }
    
    public function add_customer_details_sale($data)
    {
        $data = array(
            'customer_name' => $data['customer_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'customer_type' => $data['customer_type'],
            'debt_amount_gold' => $data['debt_amount_gold'],
            'debt_amount_cash' => $data['debt_amount_cash'],
            'over_paid_gold' => $data['over_paid_gold'],
            'over_paid_cash' => $data['over_paid_cash'],
            'box_debt_amount_gold' => $data['box_debt_amount_gold'],
            'box_debt_amount_cash' => $data['box_debt_amount_cash'],
            'box_over_paid_gold' => $data['box_over_paid_gold'],
            'box_over_paid_cash' => $data['box_over_paid_cash'],
            'status' => $data['status'], // 1 - active, 2 - inactive, 3 - deleted, 4 - hold-on
            'send_sms' => 1, // 1 - Yes, 0 - No
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_customers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_sale($data)
    {
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'invoice_num' => $data['invoice_number'],
            'sale_category' => $data['sale_category'],
            'sale_type' => $data['sale_type'],
            '24k_rate' => $data['24k_rate'],
            'status' => $data['status'], // 1 - Completed Payment, 2 - Incompleted Payment, 3 - Pending Payment, 4 - Requested, 5 - Rejected
            'bill_amount' => $data['total_amount'],
            'total_amount' => $data['total_amount'],
            'balance_amount' => $data['total_amount'],
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_sales', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_sale_items($data)
    {
        $data = array(
            'sale_id' => $data['sale_id'],
            'selling_percentage' => $data['selling_percentage'],
            'selling_rate' => $data['selling_rate'],
            'sales_grams' => $data['sales_grams'],
            'actual_grams' => $data['sales_grams'],
            'item_name' => $data['item_name'],
            'description' => $data['description'],
            'amount' => $data['amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id')
        );

        if ($query = $this->db->insert('tbl_sale_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_sale_item_actual_grams($id, $actual_grams)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'actual_grams' => $actual_grams,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_sale_items', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function get_sale_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_sales');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }
    
    public function get_box_sale_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_box_sales');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }
    
    public function get_24k_sale_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_24k_sales');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }
    
    public function get_sale_details_by_id($id)
    {
        $this->db->select('s.*, s.24k_rate as k_rate, s.status as sale_status, c.id as customer_id, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, c.over_paid_gold as over_paid_gold, c.over_paid_cash as over_paid_cash');
        $this->db->from('tbl_sales s');
        $this->db->join('tbl_customers c','c.id = s.customer_id');
        $this->db->where('s.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }
    
    public function get_24k_sale_details_by_id($id)
    {
        $this->db->select('s.*, s.24k_rate as k_rate, s.status as sale_status, c.id as customer_id, c.customer_name, c.contact_number, c.status as customer_status, c.over_paid_cash');
        $this->db->from('tbl_24k_sales s');
        $this->db->join('tbl_24k_customers c','c.id = s.customer_id');
        $this->db->where('s.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_sale_items_by_sale_id($sale_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_sale_items');
        $this->db->where('sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    } 

    public function get_24k_sale_items_by_sale_id($sale_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_24k_sale_items');
        $this->db->where('sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    } 

    public function get_box_sale_items_by_sale_id($sale_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_box_sale_items');
        $this->db->where('box_sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }  

    public function get_sale_items_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_sale_items');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }  

    public function get_returned_sale_details_by_id($id)
    {
        $this->db->select('rs.*, rs.24k_rate as k_rate, rs.status as sale_status, c.id as customer_id, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, s.invoice_num as sale_invoice_num');
        $this->db->from('tbl_return_sales rs');
        $this->db->join('tbl_customers c','c.id = rs.customer_id');
        $this->db->join('tbl_sales s','s.id = rs.sale_id');
        $this->db->where('rs.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_returned_sale_details_by_sale_id($id)
    {
        $this->db->select('rs.*, rs.24k_rate as k_rate, rs.status as sale_status, c.id as customer_id, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status');
        $this->db->from('tbl_return_sales rs');
        $this->db->join('tbl_customers c','c.id = rs.customer_id');
        $this->db->where('rs.sale_id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_returned_sale_items_by_returned_sale_id($sale_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_return_sale_items');
        $this->db->where('return_sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    } 

    public function get_returned_sale_items_for_approvals($return_sale_id)
    {
        $this->db->select('rsi.*, si.actual_grams');
        $this->db->from('tbl_return_sale_items rsi');
        $this->db->join('tbl_sale_items si','si.id = rsi.item_id');
        $this->db->where('rsi.return_sale_id', $return_sale_id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_payment_items_by_sale_id($sale_id)
    {
        $this->db->select('pi.*, p.*, b.*, ba.*, p.status as payment_status, pf.paid_amount_in_gold as fixing_gold');
        $this->db->from('tbl_payment_items pi');
        $this->db->join('tbl_payments p','p.id = pi.payment_id');
        $this->db->join('tbl_banks b','b.id = p.bank_id','left outer');
        $this->db->join('tbl_bank_accounts ba','ba.id = p.account_id','left outer');
        $this->db->join('tbl_payment_fixing pf','pf.payment_item_id = pi.id','left outer');
        $this->db->where('pi.sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_24k_payment_items_by_sale_id($sale_id)
    {
        $this->db->select('pi.*, p.*, b.*, ba.*, p.status as payment_status');
        $this->db->from('tbl_24k_payment_items pi');
        $this->db->join('tbl_24k_payments p','p.id = pi.payment_id');
        $this->db->join('tbl_banks b','b.id = p.bank_id','left outer');
        $this->db->join('tbl_bank_accounts ba','ba.id = p.account_id','left outer');
        $this->db->where('pi.sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_box_payment_items_by_sale_id($sale_id)
    {
        $this->db->select('pi.*, p.*, b.*, ba.*, p.status as payment_status, pf.paid_amount_in_gold as fixing_gold');
        $this->db->from('tbl_box_payment_items pi');
        $this->db->join('tbl_box_payments p','p.id = pi.box_payment_id');
        $this->db->join('tbl_banks b','b.id = p.bank_id','left outer');
        $this->db->join('tbl_bank_accounts ba','ba.id = p.account_id','left outer');
        $this->db->join('tbl_box_payment_fixing pf','pf.box_payment_item_id = pi.id','left outer');
        $this->db->where('pi.box_sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_cheques_by_customer_id($customer_id, $status = null)
    {
        $this->db->select('c.*, b.bank_name');
        $this->db->from('tbl_cheques c');
        $this->db->join('tbl_banks b','b.id = c.bank_id','left outer');
        $this->db->where('customer_id', $customer_id);
        if($status != null){
            $this->db->where('c.status', $status);
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_dxb_sale_cheques_by_customer_id($customer_id, $status = null)
    {
        $this->db->select('c.*, b.bank_name');
        $this->db->from('tbl_cheques c');
        $this->db->join('tbl_banks b','b.id = c.bank_id','left outer');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('cheque_type', 'Sale');
        if($status != null){
            $this->db->where('c.status', $status);
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_box_sale_cheques_by_customer_id($customer_id, $status = null)
    {
        $this->db->select('c.*, b.bank_name');
        $this->db->from('tbl_cheques c');
        $this->db->join('tbl_banks b','b.id = c.bank_id','left outer');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('cheque_type', 'Box Sale');
        if($status != null){
            $this->db->where('c.status', $status);
        }
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_all_banks()
    {
        $this->db->distinct();
        $this->db->select('id, bank_name');
        $this->db->from('tbl_banks');
        $this->db->where('status', 1);
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }   

    public function get_banks_for_receipts()
    {
        $this->db->distinct();
        $this->db->select('id, bank_name');
        $this->db->from('tbl_banks');
        $this->db->where('status', 1);
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }   

    public function get_bank_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_banks');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_bank_accounts_for_receipts()
    {
        $this->db->distinct();
        $this->db->select('ba.id, b.bank_name, ba.account_name, ba.account_number');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b','b.id = ba.bank_id');
        $this->db->where('ba.status', 1);
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_dxb_bank_accounts_for_receipts()
    {
        $this->db->distinct();
        $this->db->select('ba.id, b.bank_name, ba.account_name, ba.account_number');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b','b.id = ba.bank_id');
        $this->db->where('ba.status', 1);
        $this->db->where('ba.account_type', 'Dxb');
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_box_bank_accounts_for_receipts()
    {
        $this->db->distinct();
        $this->db->select('ba.id, b.bank_name, ba.account_name, ba.account_number');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b','b.id = ba.bank_id');
        $this->db->where('ba.status', 1);
        $this->db->where('ba.account_type', 'Box');
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_bank_accounts()
    {
        $this->db->distinct();
        $this->db->select('ba.*, b.bank_name');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b','b.id = ba.bank_id');
        $this->db->where('ba.status', 1);
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_dxb_bank_accounts()
    {
        $this->db->distinct();
        $this->db->select('ba.*, b.bank_name');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b','b.id = ba.bank_id');
        $this->db->where('ba.status', 1);
        $this->db->where('ba.account_type', 'Dxb');
        $this->db->order_by('bank_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 
    
    public function add_payment_details($data)
    {       
        
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'payment_method' => $data['payment_method'],
            'payment_type' => $data['payment_type'],
            'bank_id' => $data['cheq_bank'],
            'cheque_number' => $data['cheq_cheque_number'],
            'cheque_deposit_date' => $data['cheq_deposit_date'],
            'account_id' => $data['deposit_bank_account'],
            'amount' => $data['payment_amount'],
            'status' => $data['status'], // 1 - completed, 2 - pending
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_payments', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    } 
    
    public function add_payment_items($data)
    {       
        
        $data = array(
            'payment_id' => $data['payment_id'],
            'sale_id' => $data['sale_id'],
            'paid_amount' => $data['payment_amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_payment_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_fixing_payments($data)
    {       
        
        $data = array(
            'date' => $data['date'],
            'customer_id' => $data['customer_id'],
            'sale_id' => $data['sale_id'],
            'payment_item_id' => $data['payment_item_id'],
            '24k_rate' => $data['24k_rate'],
            'paid_amount_in_cash' => $data['paid_amount_in_cash'],
            'paid_amount_in_gold' => $data['paid_amount_in_gold'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_payment_fixing', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_sale_bal_amount($id, $balance_amount)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'balance_amount' => $balance_amount,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function update_sale_discount_amount($id, $data)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'discount_amount' => $data['discount_amount'],
            'balance_amount' => $data['balance_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_sale_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_summery_debt_details_by_cus_id($customer_id, $statuses = null, $sale_type)
    {
        $this->db->select('SUM(total_amount) as total_amount, SUM(balance_amount) as balance_amount');
        $this->db->from('tbl_sales');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('sale_type', $sale_type);
        if($statuses != null){
            $this->db->where_in('status', $statuses);
        }
        $this->db->group_by('customer_id');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_summery_dxb_sale_return_details_by_cus_id($customer_id, $statuses = null, $sale_type)
    {
        $this->db->select('SUM(amount) as total_amount');
        $this->db->from('tbl_return_sales');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('sale_type', $sale_type);
        if($statuses != null){
            $this->db->where_in('status', $statuses);
        }
        $this->db->group_by('customer_id');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_summery_box_debt_details_by_cus_id($customer_id, $statuses = null, $sale_type)
    {
        $this->db->select('SUM(total_amount) as total_amount, SUM(balance_amount) as balance_amount');
        $this->db->from('tbl_box_sales');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('sale_type', $sale_type);
        if($statuses != null){
            $this->db->where_in('status', $statuses);
        }
        $this->db->group_by('customer_id');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_summery_box_sale_return_details_by_cus_id($customer_id, $statuses = null, $sale_type)
    {
        $this->db->select('SUM(amount) as total_amount');
        $this->db->from('tbl_box_return_sales');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('sale_type', $sale_type);
        if($statuses != null){
            $this->db->where_in('status', $statuses);
        }
        $this->db->group_by('customer_id');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_debt_details_by_cus_id($customer_id, $statuses, $sale_type)
    {
        $this->db->select('*');
        $this->db->from('tbl_sales');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('sale_type', $sale_type);
        $this->db->where_in('status', $statuses);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_box_debt_details_by_cus_id($customer_id, $statuses, $sale_type)
    {
        $this->db->select('*');
        $this->db->from('tbl_box_sales');
        $this->db->where('customer_id', $customer_id);
        $this->db->where('sale_type', $sale_type);
        $this->db->where_in('status', $statuses);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_stock_details()
    {
        $this->db->select('*');
        $this->db->from('tbl_stock');
        $this->db->where('id', 1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_dxb_cash_at_bank()
    {
        $this->db->select('SUM(bank_balance) as cash_at_bank');
        $this->db->from('tbl_bank_accounts');
        $this->db->where('status !=', 3);
        $this->db->where('account_type', 'Dxb');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_box_cash_at_bank()
    {
        $this->db->select('SUM(bank_balance) as cash_at_bank');
        $this->db->from('tbl_bank_accounts');
        $this->db->where('status !=', 3);
        $this->db->where('account_type', 'Box');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_total_sales()
    {
        $this->db->select('COUNT(id) AS tot_sales');
        $this->db->from('tbl_sales');
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function update_stock_details($column, $value)
    {
        $update_data = array(
            $column => $value,
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
        );
        $this->db->where('id', 1);
        $query = $this->db->update('tbl_stock', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_bank_account_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_bank_accounts');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_account_details_by_id($id)
    {
        $this->db->select('ba.*, b.bank_name');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b', 'b.id = ba.bank_id');
        $this->db->where('ba.id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_account_details_by_acc_type($account_type)
    {
        $this->db->select('ba.*, b.bank_name');
        $this->db->from('tbl_bank_accounts ba');
        $this->db->join('tbl_banks b', 'b.id = ba.bank_id');
        $this->db->where('ba.account_type', $account_type);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function update_bank_balance($id, $bank_balance)
    {
        $update_data = array(
            'bank_balance' => $bank_balance,
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_bank_accounts', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_bank_account_logs($data)
    {      
        $data = array(
            'account_id' => $data['account_id'],
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_bank_account_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_cash_logs($data)
    {      
        $data = array(
            'action_date' => $data['action_date'],
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_cash_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_22k_gold_logs($data)
    {      
        $data = array(
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_22k_gold_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_24k_gold_logs($data)
    {      
        $data = array(
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_24k_gold_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_box_bank_account_logs($data)
    {      
        $data = array(
            'account_id' => $data['account_id'],
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_box_bank_account_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_box_cash_logs($data)
    {      
        $data = array(
            'action_date' => $data['action_date'],
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_box_cash_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_box_22k_gold_logs($data)
    {      
        $data = array(
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_box_22k_gold_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_box_24k_gold_logs($data)
    {      
        $data = array(
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'action_type' => $data['action_type'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_box_24k_gold_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function save_add_bank_account_details($data)
    {
        $data = array(
            'bank_id' => $data['bank_id'],
            'account_type' => $data['account_type'],
            'account_name' => $data['account_name'],
            'account_number' => $data['account_number'],
            'bank_balance' => '0.00',
            'status' => 1, // 1 - active
            'created_on' => date('Y-m-d'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_bank_accounts', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_bank_account_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_bank_accounts', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function save_edit_bank_account_details($id, $data)
    {
        $data = array(
            'bank_id' => $data['bank_id'],
            'account_type' => $data['account_type'],
            'account_name' => $data['account_name'],
            'account_number' => $data['account_number'],
            'bank_balance' => $data['bank_balance'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_bank_accounts', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_cheque_details($data)
    {      
        $data = array(
            'bank_id' => $data['bank_id'],
            'payment_id' => $data['payment_id'],
            'customer_id' => $data['customer_id'],
            'cheque_type' => $data['cheque_type'],
            'cheque_number' => $data['cheque_number'],
            'cheque_amount' => $data['cheque_amount'],
            'deposit_date' => $data['deposit_date'],
            'status' => 'Pending',
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );
                
//                print_r($data);exit;

        if ($query = $this->db->insert('tbl_cheques', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_latest_customers($limit)
    {
        $this->db->select('*');
        $this->db->from('tbl_customers');
        $this->db->limit($limit);
        $this->db->order_by('id', 'DESC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function get_latest_debtors($limit, $statuses)
    {
        $this->db->select('c.*');
        $this->db->from('tbl_sales s');
        $this->db->join('tbl_customers c', 'c.id = s.customer_id');
        $this->db->where_in('s.status', $statuses);
        $this->db->group_by('customer_id');
        $this->db->limit($limit);
        $this->db->order_by('s.id', 'DESC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function get_latest_sales_by_status($limit, $status)
    {
        $this->db->select('s.id as sale_id, s.sale_type, s.balance_amount, s.total_amount, s.status as sale_status, s.date as invoiced_on, c.id as customer_id, c.customer_name');
        $this->db->from('tbl_sales s');
        $this->db->join('tbl_customers c', 'c.id = s.customer_id');
        if($status != null){
           $this->db->where('s.status', $status); 
        }
        $this->db->limit($limit);
        $this->db->order_by('s.id', 'DESC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function get_collection_data_by_week($date, $month, $year) {
        $sql = "Select SUM(amount) as tot_amount from tbl_payments where status = 1 AND IF(payment_method != 'Cheque',Month(date)='" . $month . "' AND Month(date)='" . $month . "' AND YEAR(date)='" . $year . "',Month(cheque_deposit_date)='" . $month . "' AND YEAR(cheque_deposit_date)='" . $year . "') AND (payment_method != 'Gold')";
        $result = $this->db->query($sql);
        if ($result->num_rows() == 1) {
            $row = $result->row();
            return $row->tot_amount;
        } else {
            $data = 0;
        }
        return $data;
    }

    public function get_cheque_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_cheques');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_cheque_details_by_id($id, $payment_type)
    {
        if($payment_type == 'Sale'){
            $this->db->select('c.*, p.payment_type');
        }elseif($payment_type == 'Box Sale'){
            $this->db->select('c.*, bp.payment_type');
        }else{
            $this->db->select('*');
        }
        $this->db->from('tbl_cheques c');
        if($payment_type == 'Sale'){
            $this->db->join('tbl_payments p', 'p.id = c.payment_id','left outer');
        }elseif($payment_type == 'Box Sale'){
            $this->db->join('tbl_box_payments bp', 'bp.id = c.payment_id','left outer');
        }
        $this->db->where('c.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_sales_by_payment_id($payment_id, $payment_type = NULL)
    {
        if($payment_type != NULL && $payment_type == 'Fixing'){
            $select_statement = '*, p.payment_type, pf.paid_amount_in_cash, pf.paid_amount_in_gold';
        }else{
            $select_statement = '*, p.payment_type';
        }
        $this->db->select($select_statement);
        $this->db->from('tbl_payment_items pi');
        $this->db->join('tbl_sales s', 's.id = pi.sale_id');
        $this->db->join('tbl_payments p', 'p.id = pi.payment_id');
        
        if($payment_type != NULL && $payment_type == 'Fixing'){
            $this->db->join('tbl_payment_fixing pf','pf.payment_item_id = pi.id','left outer');
        }
        
        $this->db->where('pi.payment_id', $payment_id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function change_payment_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_payments', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_box_payment_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_box_payments', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_cheque_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_cheques', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_stock_item_categories()
    {
        $this->db->distinct();
        $this->db->select('id, category_name');
        $this->db->from('tbl_stock_item_categories');
        $this->db->where('status', 1);
        $this->db->order_by('category_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 
    
    public function save_add_stock_item_details($data)
    {
        $data = array(
            'item_type' => $data['item_type'],
            'category_id' => $data['category_id'],
            'item_code' => $data['item_code'],
            'item_name' => $data['item_name'],
            'description' => $data['item_description'],
            'status' => 1, // 1 - active
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_stock_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_stock_item_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_stock_items', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function save_edit_stock_item_details($id, $data)
    {
        $data = array(
            'item_type' => $data['item_type'],
            'category_id' => $data['category_id'],
            'item_code' => $data['item_code'],
            'item_name' => $data['item_name'],
            'description' => $data['item_description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_stock_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_item_details_by_id($id)
    {
        $this->db->select('si.*, sic.category_name, sic.description as category_description');
        $this->db->from('tbl_stock_items si');
        $this->db->join('tbl_stock_item_categories sic', 'sic.id = si.category_id', 'left outer');
        $this->db->where('si.id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_all_stock_items()
    {
        $this->db->distinct();
        $this->db->select('id, item_code, item_name');
        $this->db->from('tbl_stock_items');
        $this->db->where('status', 1);
        $this->db->order_by('item_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }  

    public function get_all_dxb_stock_items()
    {
        $this->db->distinct();
        $this->db->select('id, item_code, item_name');
        $this->db->from('tbl_stock_items');
        $this->db->where('status', 1);
        $this->db->where('item_type', 'Dxb');
        $this->db->order_by('item_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_all_box_stock_items()
    {
        $this->db->distinct();
        $this->db->select('id, item_code, item_name');
        $this->db->from('tbl_stock_items');
        $this->db->where('status', 1);
        $this->db->where('item_type', 'Box');
        $this->db->order_by('item_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_all_24k_stock_items()
    {
        $this->db->distinct();
        $this->db->select('id, item_code, item_name');
        $this->db->from('tbl_stock_items');
        $this->db->where('status', 1);
        $this->db->where('item_type', '24K Sale');
        $this->db->order_by('item_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_last_added_stock_items_by_type($item_type)
    {
        $this->db->distinct();
        $this->db->select('id, item_code, item_name');
        $this->db->from('tbl_stock_items');
        $this->db->where('status', 1);
        $this->db->where('item_type', $item_type);
        $this->db->order_by('id', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 

    public function get_fixing_details_by_sale_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_payment_fixing');
        $this->db->where('sale_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    } 

    public function get_rate_details()
    {
        $this->db->select('*');
        $this->db->from('tbl_rates');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function get_rate_detail_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_rates');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_admin_detail($id)
    {
        //get admin details
        $this->db->select('*');
        $this->db->from('tbl_admin_user au');
        $this->db->join('tbl_user_types ut', 'ut.id = au.user_type_id');
        $this->db->where('au.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false; // admin is not Exists
        }
    }

    public function update_current_rate($id, $data)
    {
        $data = array(
            'item_rate' => $data['rate_amount'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_rates', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function save_add_rate_log($data)
    {
        $data = array(
            'rate_id' => $data['rate_id'],
            'rate_amount' => $data['amount'],
            'description' => $data['description'],
            'status' => $data['status'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_rate_logs', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function save_add_expense_category_details($data)
    {
        $data = array(
            'category_name' => $data['category_name'],
            'description' => $data['description'],
            'status' => 1, // 1 - active
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_expense_categories', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_expense_category_details_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_expense_categories');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_expense_category_details($id, $data)
    {
        $data = array(
            'category_name' => $data['category_name'],
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_expense_categories', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_expense_category_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_expense_categories', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_expense_categories()
    {
        $this->db->distinct();
        $this->db->select('id, category_name');
        $this->db->from('tbl_expense_categories');
        $this->db->where('status', 1);
        $this->db->order_by('category_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    } 
    
    public function add_expense_details($data)
    {       
        $data = array(
            'date' => $data['date'],
            'expense_type' => $data['expense_type'],
            'category_id' => $data['category_id'],
            'payment_method' => $data['payment_method'],
            'account_id' => $data['account_id'],
            'cheque_number' => $data['cheque_number'],
            'cheque_deposit_date' => $data['cheque_deposit_date'],
            'third_party_bank_id' => $data['third_party_bank_id'],
            'third_party_account_number' => $data['third_party_account_number'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'status' => $data['status'], // 1 - completed
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_expenses', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_expense_details_by_id($id)
    {
        $this->db->select('*, e.id as expense_id, e.status AS expense_status, e.description AS expense_description, ec.description AS category_description');
        $this->db->from('tbl_expenses e');
        $this->db->join('tbl_expense_categories ec', 'ec.id = e.category_id');
        $this->db->where('e.id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function change_expense_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
//            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_expenses', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function save_add_creditor_details($data)
    {
        $data = array(
            'creditor_name' => $data['creditor_name'],
            'contact_number' => $data['contact_number'],
            'creditor_type' => $data['creditor_type'],
            'amount' => '0.00',
            'bal_amount' => '0.00',
            'paid_amount' => '0.00',
            'una_amount' => '0.00',
            'una_bal_amount' => '0.00',
            'una_paid_amount' => '0.00',
            'status' => 1, // 1 - active
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_creditors', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_creditor_details_sale($data)
    {
        $data = array(
            'creditor_name' => $data['creditor_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'creditor_type' => $data['creditor_type'],
            'amount' => $data['amount'],
            'bal_amount' => $data['amount'],
            'paid_amount' => '0.00',
            'una_amount' => $data['una_amount'],
            'una_bal_amount' => $data['una_amount'],
            'una_paid_amount' => '0.00',
            'status' => 1, // 1 - active, 2 - inactive, 3 - deleted
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_creditors', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_creditor_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_creditors', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_creditor_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_creditors');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_creditor_details($id, $data)
    {
        $data = array(
            'creditor_name' => $data['creditor_name'],
            'contact_number' => $data['contact_number'],
            'creditor_type' => $data['creditor_type'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_creditors', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_approval_requests($data)
    {
        $data = array(
            'item_id' => $data['item_id'],
            'item_type' => $data['item_type'],
            'amount' => $data['amount'],
            'reason' => $data['description'],
            'status' => 'Pending',
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_approvals', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_approval_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_approvals', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_approval_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_approvals');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_approval_by_item_type_id($item_type, $item_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_approvals');
        $this->db->where('item_type', $item_type);
        $this->db->where('item_id', $item_id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_creditors_to_add_una()
    {
        $this->db->distinct();
        $this->db->select('id, creditor_name, contact_number');
        $this->db->from('tbl_creditors');
        $this->db->where('status =', 1);
        $this->db->where_in('creditor_type', array('UNA Creditor','UNA & Normal Creditor'));
        $this->db->order_by('creditor_name', 'ASC');
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }

    public function get_creditors_to_settle_una()
    {
        $this->db->select('*');
        $this->db->from('tbl_creditors');
        $this->db->where('status =', 1);
        $this->db->where_in('creditor_type', array('Normal Creditor','UNA & Normal Creditor'));
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }
    
    public function add_una($data)
    {
        $data = array(
            'creditor_id' => $data['creditor_id'],
            'date' => $data['date'],
            'una_amount' => $data['una_amount'],
            'una_rate' => $data['una_rate'],
            'lkr_amount' => $data['lkr_amount'],
            'bal_amount' => $data['lkr_amount'],
            'paid_amount' => '0.00',
            'recipient' => $data['recipient'],
            'remarks' => $data['remarks'],
            'admin_remarks' => '',
            'status' => $data['status'], // 1 - Completed Payment, 2 - Incompleted Payment, 3 - Pending Payment, 4 - Requested, 5 - Rejected
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_una', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_una_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_una');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_pending_una_by_creditor_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_una');
        $this->db->where('creditor_id', $id);
        $this->db->where('status != ', 1);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_una_details($id, $data)
    {
        $data = array(
            'creditor_id' => $data['creditor_id'],
            'date' => $data['date'],
            'una_amount' => $data['una_amount'],
            'una_rate' => $data['una_rate'],
            'lkr_amount' => $data['lkr_amount'],
            'bal_amount' => $data['bal_amount'],
            'recipient' => $data['recipient'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_una', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_admin_remarks($id, $data)
    {
        $data = array(
            'admin_remarks' => $data['admin_remarks'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_una', $data)) {
            return true;
        } else {
            return false;
        }
    }

    public function update_expense_admin_remarks($id, $data)
    {
        $data = array(
            'admin_remarks' => $data['admin_remarks'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_expenses', $data)) {
            return true;
        } else {
            return false;
        }
    }

    public function update_creditor_amounts($id, $amounts)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'amount' => $amounts['credit_amount'],
            'bal_amount' => $amounts['credit_bal_amount'],
            'una_amount' => $amounts['una_amount'],
            'una_bal_amount' => $amounts['una_bal_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_creditors', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_creditor_accounts($data)
    {
        $data = array(
            'date' => date('Y-m-d'),
            'creditor_id' => $data['creditor_id'],
            'item_type' => $data['item_type'],
            'item_id' => $data['item_id'],
            'amount' => $data['amount'],
            'status' => 1, // 1 - Active
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_creditor_accounts', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_creditors_to_settle()
    {
        $this->db->distinct();
        $this->db->select('id, creditor_name, contact_number');
        $this->db->from('tbl_creditors');
        $this->db->where('bal_amount >', 0);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }

    public function get_una_creditors_to_settle()
    {
        $this->db->distinct();
        $this->db->select('id, creditor_name, contact_number');
        $this->db->from('tbl_creditors');
        $this->db->where('una_bal_amount >', 0);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
        } else {
            $data = array();
        }
        return $data;
    }
    
    public function add_creditor_payments($data)
    {       
        $data = array(
            'date' => $data['date'],
            'creditor_id' => $data['creditor_id'],
            'payment_method' => $data['payment_method'],
            'una_payment_id' => $data['una_payment_id'],
            'account_id' => $data['account_id'],
            'cheque_number' => $data['cheque_number'],
            'cheque_deposit_date' => $data['cheque_deposit_date'],
            'third_party_bank_id' => $data['third_party_bank_id'],
            'third_party_account_number' => $data['third_party_account_number'],
            'amount' => $data['amount'],
            'status' => $data['status'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_creditor_payments', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_creditor_payment_amounts($id, $data)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'bal_amount' => $data['bal_amount'],
            'paid_amount' => $data['paid_amount'],
            'una_bal_amount' => $data['una_bal_amount'],
            'una_paid_amount' => $data['una_paid_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_creditors', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_creditor_accounts_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_creditor_accounts');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_creditor_accounts_by_item_id($item_type, $item_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_creditor_accounts');
        $this->db->where('item_type', $item_type);
        $this->db->where('item_id', $item_id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function update_creditor_una_account($id, $una_amount)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'una_amount' => $una_amount,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_creditor_accounts', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_creditor_payments_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_creditor_payments');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function change_una_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_una', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_una_payments($data)
    {       
        $data = array(
            'date' => $data['date'],
            'una_id' => $data['una_id'],
            'creditor_id' => $data['creditor_id'],
            'amount' => $data['amount'],
            'status' => $data['status'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_una_payments', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_una_payment_items($data)
    {       
        $data = array(
            'una_payment_id' => $data['una_payment_id'],
            'item_type' => $data['item_type'], // Cash in hand, Cash at bank, Gold, Creditor
            'item_id' => $data['item_id'],
            'amount' => $data['amount'],
            'description' => $data['description'],
            'status' => 'Completed',
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_una_payment_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_una_payment_amounts($id, $data)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'bal_amount' => $data['bal_amount'],
            'paid_amount' => $data['paid_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_una', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_return_sale($data)
    {
        $data = array(
            'sale_id' => $data['sale_id'],
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'invoice_num' => $data['invoice_number'],
            'sale_type' => $data['sale_type'],
            '24k_rate' => $data['24k_rate'],
            'amount' => $data['amount'],
            'status' => $data['status'], // 1 - Completed, 2 - Requested, 3 - Rejected
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_return_sales', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_return_sale_items($data)
    {
        $data = array(
            'return_sale_id' => $data['return_sale_id'],
            'item_id' => $data['item_id'],
            'selling_percentage' => $data['selling_percentage'],
            'selling_rate' => $data['selling_rate'],
            'sales_grams' => $data['sales_grams'],
            'item_name' => $data['item_name'],
            'description' => $data['description'],
            'amount' => $data['amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_return_sale_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_return_sale_amounts($id, $data)
    {
        $update_data = array(
            'balance_amount' => $data['balance_amount'],
            'return_sale_amount' => $data['return_sale_amount'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function update_return_sale_items($id, $data)
    {
        $update_data = array(
            'sales_grams' => $data['sales_grams'],
            'amount' => $data['amount'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_sale_items', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_sale_discount($data)
    {
        $data = array(
            'sale_id' => $data['sale_id'],
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'discount_amount' => $data['discount_amount'],
            'status' => 1, // 1 - Active
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_sale_discounts', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_una_payments_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_una_payments');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_una_payment_items_by_payment_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_una_payment_items');
        $this->db->where('una_payment_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function get_payment_details_by_id($id)
    {
        $this->db->select('p.*, c.customer_name, c.contact_number');
        $this->db->from('tbl_payments p');
        $this->db->join('tbl_customers c', 'c.id = p.customer_id');
        $this->db->where('p.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_payment_items_by_payment_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_payment_items');
        $this->db->where('payment_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function get_pending_approvals_count()
    {
        
        $user_type = $this->session->userdata('user_type_id');
        
        $sql = "SELECT DISTINCT id FROM  tbl_approvals WHERE status = 'Pending' ";
        if($user_type == 3){
            $sql .= "AND item_type IN ('Dxb Sale', 'Box Sale', 'Return Dxb Sale')";            
        }
        
        $query = $this->db->query($sql);
        if ($query->num_rows() >= 1) {
            $data = $query->num_rows();
        } else {
            $data = 0;
        }
        return $data;
    } 

    public function get_last_cash_log()
    {
        $this->db->select('*');
        $this->db->from('tbl_cash_logs');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_last_22k_gold_log()
    {
        $this->db->select('*');
        $this->db->from('tbl_22k_gold_logs');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_last_24k_gold_log()
    {
        $this->db->select('*');
        $this->db->from('tbl_24k_gold_logs');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_last_box_cash_log()
    {
        $this->db->select('*');
        $this->db->from('tbl_box_cash_logs');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_last_box_22k_gold_log()
    {
        $this->db->select('*');
        $this->db->from('tbl_box_22k_gold_logs');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_last_box_24k_gold_log()
    {
        $this->db->select('*');
        $this->db->from('tbl_box_24k_gold_logs');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function update_balance_stock($column_name, $value)
    {
        $data = array(
            $column_name => $value,
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', 1);
        if ($this->db->update('tbl_stock', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function save_add_daily_audit_details($data)
    {
        $data = array(
            'date' => $data['date'],
            'cash_balance' => $data['cash_balance'],
            'gold_balance' => $data['gold_balance'],
            'description' => $data['description'],
            'status' => 1, // 1 - active
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_daily_audits', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_daily_audit_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_daily_audits', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function save_edit_daily_audit_details($id, $data)
    {
        $data = array(
            'date' => $data['date'],
            'cash_balance' => $data['cash_balance'],
            'gold_balance' => $data['gold_balance'],
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_daily_audits', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_audit_details_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_daily_audits');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_last_daily_audit()
    {
        $this->db->select('*');
        $this->db->from('tbl_daily_audits');
        $this->db->order_by('added_on', 'DESC');
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    function get_pending_cheque_amount_within($from_date, $to_date) {
        $sql = "Select COUNT(id) as no_of_cheques, SUM(cheque_amount) as tot_amount from tbl_cheques where status = 'Pending' AND (deposit_date BETWEEN '".$from_date."' AND '".$to_date."')";
        $result = $this->db->query($sql);
        if ($result->num_rows() == 1) {
            $row = $result->row();
            return $row;
        } else {
            $data = 0;
        }
        return $data;
    }

    function get_pending_cheque_amount_more_than($four_weeks_date) {
        $sql = "Select COUNT(id) as no_of_cheques, SUM(cheque_amount) as tot_amount from tbl_cheques where status = 'Pending' AND (deposit_date > '".$four_weeks_date."')";
        $result = $this->db->query($sql);
        if ($result->num_rows() == 1) {
            $row = $result->row();
            return $row;
        } else {
            $data = 0;
        }
        return $data;
    }

    public function get_una_details_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_una');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    } 
    
    public function add_other_income_details($data)
    {       
        
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'other_income' => $data['other_income'],
            'payment_method' => $data['payment_method'],
            'bank_id' => $data['cheq_bank'],
            'cheque_number' => $data['cheq_cheque_number'],
            'cheque_deposit_date' => $data['cheq_deposit_date'],
            'account_id' => $data['deposit_bank_account'],
            'amount' => $data['payment_amount'],
            'description' => $data['description'],
            'status' => $data['status'],  // 1 - Completed, 2 - Pending, 3 - Cheque Returned, 4 - Cancelled
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_other_income', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    } 

    public function get_other_income_details_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_other_income');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function change_other_payment_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_other_income', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_box_sale($data)
    {
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'invoice_num' => $data['invoice_number'],
            'sale_category' => $data['sale_category'],
            'sale_type' => $data['sale_type'],
            '24k_rate' => $data['24k_rate'],
            'status' => $data['status'], // 1 - Completed Payment, 2 - Incompleted Payment, 3 - Pending Payment, 4 - Requested, 5 - Rejected
            'bill_amount' => $data['total_amount'],
            'total_amount' => $data['total_amount'],
            'balance_amount' => $data['total_amount'],
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_sales', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_box_sale_items($data)
    {
        $data = array(
            'box_sale_id' => $data['sale_id'],
            'selling_percentage' => $data['selling_percentage'],
            'selling_rate' => $data['selling_rate'],
            'sales_grams' => $data['sales_grams'],
            'actual_grams' => $data['sales_grams'],
            'item_name' => $data['item_name'],
            'description' => $data['description'],
            'amount' => $data['amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id')
        );

        if ($query = $this->db->insert('tbl_box_sale_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function get_box_sale_details_by_id($id)
    {
        $this->db->select('s.*, s.24k_rate as k_rate, s.status as sale_status, c.id as customer_id, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, c.over_paid_gold as over_paid_gold, c.over_paid_cash as over_paid_cash');
        $this->db->from('tbl_box_sales s');
        $this->db->join('tbl_customers c','c.id = s.customer_id');
        $this->db->where('s.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_box_sale_items_by_box_sale_id($box_sale_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_box_sale_items');
        $this->db->where('box_sale_id', $box_sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_box_payment_items_by_box_sale_id($box_sale_id)
    {
        $this->db->select('pi.*, p.*, b.*, ba.*, p.status as payment_status, pf.paid_amount_in_gold as fixing_gold');
        $this->db->from('tbl_box_payment_items pi');
        $this->db->join('tbl_box_payments p','p.id = pi.box_payment_id');
        $this->db->join('tbl_banks b','b.id = p.bank_id','left outer');
        $this->db->join('tbl_bank_accounts ba','ba.id = p.account_id','left outer');
        $this->db->join('tbl_box_payment_fixing pf','pf.box_payment_item_id = pi.id','left outer');
        $this->db->where('pi.box_sale_id', $box_sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_box_fixing_details_by_box_sale_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_box_payment_fixing');
        $this->db->where('box_sale_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    } 

    public function update_box_sale_discount_amount($id, $data)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'discount_amount' => $data['discount_amount'],
            'balance_amount' => $data['balance_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_box_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_box_sale_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_box_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_box_payment_details($data)
    {       
        
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'payment_method' => $data['payment_method'],
            'payment_type' => $data['payment_type'],
            'bank_id' => $data['cheq_bank'],
            'cheque_number' => $data['cheq_cheque_number'],
            'cheque_deposit_date' => $data['cheq_deposit_date'],
            'account_id' => $data['deposit_bank_account'],
            'amount' => $data['payment_amount'],
            'status' => $data['status'], // 1 - completed, 2 - pending
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_payments', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    } 
    
    public function add_box_payment_items($data)
    {       
        
        $data = array(
            'box_payment_id' => $data['payment_id'],
            'box_sale_id' => $data['sale_id'],
            'paid_amount' => $data['payment_amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_payment_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_box_sale_discount($data)
    {
        $data = array(
            'box_sale_id' => $data['sale_id'],
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'discount_amount' => $data['discount_amount'],
            'status' => 1, // 1 - Active
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_sale_discounts', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_box_sale_bal_amount($id, $balance_amount)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'balance_amount' => $balance_amount,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_box_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_box_payment_details_by_id($id)
    {
        $this->db->select('p.*, c.customer_name, c.contact_number');
        $this->db->from('tbl_box_payments p');
        $this->db->join('tbl_customers c', 'c.id = p.customer_id');
        $this->db->where('p.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_box_sales_by_payment_id($payment_id, $payment_type = NULL)
    {        
        if($payment_type != NULL && $payment_type == 'Fixing'){
            $select_statement = '*, p.payment_type, pf.paid_amount_in_cash, pf.paid_amount_in_gold';
        }else{
            $select_statement = '*, p.payment_type';
        }
        $this->db->select($select_statement);
        $this->db->from('tbl_box_payment_items pi');
        $this->db->join('tbl_box_sales s', 's.id = pi.box_sale_id');
        $this->db->join('tbl_box_payments p', 'p.id = pi.box_payment_id');
        
        if($payment_type != NULL && $payment_type == 'Fixing'){
            $this->db->join('tbl_box_payment_fixing pf','pf.box_payment_item_id = pi.id','left outer');
        }
        
        $this->db->where('pi.box_payment_id', $payment_id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }
    
    public function add_fixing_box_payments($data)
    {       
        
        $data = array(
            'date' => $data['date'],
            'customer_id' => $data['customer_id'],
            'box_sale_id' => $data['sale_id'],
            'box_payment_item_id' => $data['payment_item_id'],
            '24k_rate' => $data['24k_rate'],
            'paid_amount_in_cash' => $data['paid_amount_in_cash'],
            'paid_amount_in_gold' => $data['paid_amount_in_gold'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_payment_fixing', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_return_box_sale_amounts($id, $data)
    {
        $update_data = array(
            'balance_amount' => $data['balance_amount'],
            'return_sale_amount' => $data['return_sale_amount'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_box_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_return_box_sale($data)
    {
        $data = array(
            'box_sale_id' => $data['sale_id'],
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'invoice_num' => $data['invoice_number'],
            'sale_type' => $data['sale_type'],
            '24k_rate' => $data['24k_rate'],
            'amount' => $data['amount'],
            'status' => 1, // 1 - Completed
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_return_sales', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_return_box_sale_items($data)
    {
        $data = array(
            'box_return_sale_id' => $data['return_sale_id'],
            'item_id' => $data['item_id'],
            'selling_percentage' => $data['selling_percentage'],
            'selling_rate' => $data['selling_rate'],
            'sales_grams' => $data['sales_grams'],
            'item_name' => $data['item_name'],
            'description' => $data['description'],
            'amount' => $data['amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_box_return_sale_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_box_sale_item_actual_grams($id, $actual_grams)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'actual_grams' => $actual_grams,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_box_sale_items', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_returned_box_sale_details_by_id($id)
    {
        $this->db->select('rs.*, rs.24k_rate as k_rate, rs.status as sale_status, c.id as customer_id, c.customer_name, c.contact_number, c.customer_type, c.status as customer_status, s.invoice_num as sale_invoice_num');
        $this->db->from('tbl_box_return_sales rs');
        $this->db->join('tbl_customers c','c.id = rs.customer_id');
        $this->db->join('tbl_box_sales s','s.id = rs.box_sale_id');
        $this->db->where('rs.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_returned_box_sale_items_by_returned_box_sale_id($sale_id)
    {
        $this->db->select('*');
        $this->db->from('tbl_box_return_sale_items');
        $this->db->where('box_return_sale_id', $sale_id);
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    } 
    
    public function save_add_24k_customer_details($data)
    {
        $data = array(
            'customer_name' => $data['customer_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'status' => 1, // 1 - active
            'send_sms' => 0, // 1 - Yes, 0 - No
            'dxb_gold_tot_amount' => 0.000,
            'dxb_cash_tot_amount' => 0.00,
            'dxb_paid_amount' => 0.00,
            'dxb_balance_amount' => 0.00,
            'box_gold_tot_amount' => 0.000,
            'box_cash_tot_amount' => 0.00,
            'box_paid_amount' => 0.00,
            'box_balance_amount' => 0.00,
            'over_paid_cash' => 0.00,
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_24k_customers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_24k_customer_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_24k_customers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_24k_customer_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_24k_customers');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_24k_customer_details($id, $data)
    {
        $data = array(
            'customer_name' => $data['customer_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_24k_customers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_24k_customer_dxb_accounts($id, $amounts)
    {
        $update_data = array(
            'dxb_gold_tot_amount' => $amounts['dxb_gold_tot_amount'],
            'dxb_cash_tot_amount' => $amounts['dxb_cash_tot_amount'],
            'dxb_paid_amount' => $amounts['dxb_paid_amount'],
            'dxb_balance_amount' => $amounts['dxb_balance_amount'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id')

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_24k_customers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function update_24k_customer_box_accounts($id, $amounts)
    {
        $update_data = array(
            'box_gold_tot_amount' => $amounts['box_gold_tot_amount'],
            'box_cash_tot_amount' => $amounts['box_cash_tot_amount'],
            'box_paid_amount' => $amounts['box_paid_amount'],
            'box_balance_amount' => $amounts['box_balance_amount'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id')

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_24k_customers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function add_24k_sale($data)
    {
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'invoice_num' => $data['invoice_number'],
            'sale_category' => $data['sale_category'],
            'sale_type' => $data['sale_type'],
            '24k_rate' => $data['24k_rate'],
            'gold_amount' => $data['gold_amount'],
            'bill_amount' => $data['total_amount'],
            'discount' => $data['discount'],
            'total_amount' => $data['total_amount'],
            'paid_amount' => 0.00,
            'balance_amount' => $data['total_amount'],
            'description' => $data['description'],
            'status' => $data['status'], // 1 - Completed Payment, 2 - Incompleted Payment, 3 - Pending Payment
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_24k_sales', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_24k_sale_items($data)
    {
        $data = array(
            'sale_id' => $data['sale_id'],
            'sales_grams' => $data['sales_grams'],
            'actual_grams' => $data['sales_grams'],
            'item_name' => $data['item_name'],
            'description' => $data['description'],
            'amount' => $data['amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id')
        );

        if ($query = $this->db->insert('tbl_24k_sale_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_24k_payment_details_by_id($id)
    {
        $this->db->select('p.*, c.customer_name, c.contact_number');
        $this->db->from('tbl_24k_payments p');
        $this->db->join('tbl_24k_customers c', 's.id = p.customer_id');
        $this->db->where('p.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function get_24k_payment_items_by_payment_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_24k_payment_items');
        $this->db->where('payment_id', $id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function get_dxb_sales_details_for_reports($from_date, $to_date) {
        $sql = "Select s.*, si.item_name, si.actual_grams, si.selling_rate, si.amount, c.customer_name "
             . "FROM tbl_sales s JOIN tbl_sale_items si ON s.id = si.sale_id "
             . "JOIN tbl_customers c ON c.id = s.customer_id "
             . "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (s.date BETWEEN '".$from_date."' AND '".$to_date."')";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function get_box_sales_details_for_reports($from_date, $to_date) {
        $sql = "Select s.*, si.item_name, si.actual_grams, si.selling_rate, si.amount, c.customer_name "
             . "FROM tbl_box_sales s JOIN tbl_box_sale_items si ON s.id = si.sale_id "
             . "JOIN tbl_customers c ON c.id = s.customer_id "
             . "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (s.date BETWEEN '".$from_date."' AND '".$to_date."')";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function get_24k_sales_details_for_reports($from_date, $to_date) {
        $sql = "Select s.*, si.item_name, si.actual_grams, si.selling_rate, si.amount, c.customer_name "
             . "FROM tbl_24k_sales s JOIN tbl_24k_sale_items si ON s.id = si.sale_id "
             . "JOIN tbl_24k_customers c ON c.id = s.customer_id "
             . "WHERE s.status NOT IN (5,6) AND s.sale_category = 'Normal' AND (s.date BETWEEN '".$from_date."' AND '".$to_date."')";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function get_dxb_debtors_details_for_reports($date_as_of) {
        $sql = "Select s.*, c.customer_name "
             . "FROM tbl_sales s "
             . "JOIN tbl_customers c ON c.id = s.customer_id "
             . "WHERE s.status IN (2,3,4) AND (s.date <= '".$date_as_of."')";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $datas = $result->result_array();
            
            $new_array = [];
            foreach($datas as $key => $data){
                $new_array[$data['customer_id']][] = $data; 
            }
            
            return $new_array;
        } else {
            return false;
        }
    }

    function get_dxb_debtors_aging_for_reports($sale_type, $date_as_of, $one_week, $two_weeks, $four_weeks) {
        $sql = "SELECT s.id, s.date, s.balance_amount, c.customer_name, s.customer_id, "
             . "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$one_week."' AND '".$date_as_of."') AND sales.sale_type = '".$sale_type."' AND sales.date <= '".$date_as_of."' AND sales.status IN (2,3,4)) AS '0-7', "
             . "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$two_weeks."' AND '".$one_week."') AND sales.sale_type = '".$sale_type."' AND sales.date <= '".$date_as_of."' AND sales.status IN (2,3,4)) AS '8-14', "
             . "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date BETWEEN '".$four_weeks."' AND '".$two_weeks."') AND sales.sale_type = '".$sale_type."' AND sales.date <= '".$date_as_of."' AND sales.status IN (2,3,4)) AS '15-30', "
             . "(SELECT SUM(balance_amount) FROM tbl_sales sales WHERE sales.customer_id = c.id AND (date < '".$four_weeks."') AND sales.sale_type = '".$sale_type."' AND sales.date <= '".$date_as_of."' AND sales.status IN (2,3,4)) AS 'above_30' "
             . "FROM tbl_sales s JOIN tbl_customers c ON c.id = s.customer_id "
             . "WHERE s.status IN (2,3,4) AND s.sale_type = '".$sale_type."' AND date <= '".$date_as_of."' "
             . "GROUP BY c.id";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function get_dxb_cash_logs_for_reports($from_date, $to_date) {
        $sql = "SELECT cl.*, au.id as admin_id, au.contact_name, ut.user_privilege "
            . "FROM tbl_cash_logs cl JOIN tbl_admin_user au ON au.id = cl.added_by "
            . "JOIN tbl_user_types ut ON ut.id = au.user_type_id "
            . "WHERE (action_date BETWEEN '".$from_date."' AND '".$to_date."') ORDER BY action_date desc";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }
    
    public function add_24k_customer_details_sale($data)
    {
        $data = array(
            'customer_name' => $data['customer_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'dxb_gold_tot_amount' => $data['dxb_gold_tot_amount'],
            'dxb_cash_tot_amount' => $data['dxb_cash_tot_amount'],
            'dxb_paid_amount' => $data['dxb_paid_amount'],
            'dxb_balance_amount' => $data['dxb_balance_amount'],
            'box_gold_tot_amount' => $data['box_gold_tot_amount'],
            'box_cash_tot_amount' => $data['box_cash_tot_amount'],
            'box_paid_amount' => $data['box_paid_amount'],
            'box_balance_amount' => $data['box_balance_amount'],
            'over_paid_cash' => 0.00,
            'status' => $data['status'], // 1 - active, 2 - inactive, 3 - deleted
            'send_sms' => 1, // 1 - Yes, 0 - No
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_24k_customers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_24k_payment_details($data)
    {       
        
        $data = array(
            'customer_id' => $data['customer_id'],
            'date' => $data['date'],
            'payment_method' => $data['payment_method'],
            'payment_type' => $data['payment_type'],
            'bank_id' => $data['cheq_bank'],
            'cheque_number' => $data['cheq_cheque_number'],
            'cheque_deposit_date' => $data['cheq_deposit_date'],
            'account_id' => $data['deposit_bank_account'],
            'amount' => $data['payment_amount'],
            'status' => $data['status'], // 1 - completed, 2 - pending
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_24k_payments', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    } 
    
    public function add_24k_payment_items($data)
    {       
        
        $data = array(
            'payment_id' => $data['payment_id'],
            'sale_id' => $data['sale_id'],
            'paid_amount' => $data['payment_amount'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_24k_payment_items', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_24k_sale_bal_amount($id, $amount)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'paid_amount' => $amount['paid_amount'],
            'balance_amount' => $amount['balance_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_24k_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_24k_sale_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_24k_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_return_sale_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_return_sales', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function save_add_stock_item_category_details($data)
    {
        $data = array(
            'category_name' => $data['category_name'],
            'description' => $data['description'],
            'status' => 1, // 1 - active
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        if ($query = $this->db->insert('tbl_stock_item_categories', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_stock_item_category_details_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_stock_item_categories');
        $this->db->where('id', $id);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_stock_item_category_details($id, $data)
    {
        $data = array(
            'category_name' => $data['category_name'],
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_stock_item_categories', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_stock_item_category_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_stock_item_categories', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function save_add_supplier_details($data)
    {
        $data = array(
            'supplier_name' => $data['supplier_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'gold_tot_amount' => $data['gold_tot_amount'],
            'cash_tot_amount' => $data['cash_tot_amount'],
            'paid_amount' => $data['paid_amount'],
            'balance_amount' => $data['balance_amount'],
            'status' => 1, // 1 - active
            'send_sms' => 0, // 1 - Yes, 0 - No
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_suppliers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_supplier_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_suppliers');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_supplier_details($id, $data)
    {
        $data = array(
            'supplier_name' => $data['supplier_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_suppliers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_supplier_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_suppliers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function save_add_carrier_details($data)
    {
        $data = array(
            'carrier_name' => $data['carrier_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'gold_tot_amount' => $data['gold_tot_amount'],
            'cash_tot_amount' => $data['cash_tot_amount'],
            'paid_amount' => $data['paid_amount'],
            'balance_amount' => $data['balance_amount'],
            'status' => 1, // 1 - active
            'send_sms' => 0, // 1 - Yes, 0 - No
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_carriers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function get_carrier_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_carriers');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }

    public function save_edit_carrier_details($id, $data)
    {
        $data = array(
            'carrier_name' => $data['carrier_name'],
            'contact_number' => $data['contact_number'],
            'tel_number' => $data['tel_number'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
        );

        $this->db->where('id', $id);
        if ($this->db->update('tbl_carriers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function change_carrier_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_carriers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_suppliers_to_do_purchase()
    {
        $this->db->select('*');
        $this->db->from('tbl_suppliers');
        $this->db->where('status', 1);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }

    public function update_carrier_accounts($id, $amounts)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'gold_tot_amount' => $amounts['gold_tot_amount'],
            'cash_tot_amount' => $amounts['cash_tot_amount'],
            'paid_amount' => $amounts['paid_amount'],
            'balance_amount' => $amounts['balance_amount'],

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_carriers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function get_pending_purchase_supplier_invoices()
    {
        $this->db->select('*');
        $this->db->from('tbl_purchase_suppliers');
        $this->db->where('status', 2);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }
    }
    
    public function add_purchase($data)
    {
        $data = array(
            'carrier_id' => $data['carrier_id'],
            'date' => $data['date'],
            '22k_stock_type' => $data['22k_stock_type'],
            'invoice_num' => $data['invoice_number'],
            'quantity_1' => $data['quantity_1'],
            'rate_1' => $data['rate_1'],
            'amount_1' => $data['amount_1'],
            'quantity_2' => $data['quantity_2'],
            'rate_2' => $data['rate_2'],
            'amount_2' => $data['amount_2'],
            'quantity_3' => $data['quantity_3'],
            'rate_3' => $data['rate_3'],
            'amount_3' => $data['amount_3'],
            'quantity_4' => $data['quantity_4'],
            'rate_4' => $data['rate_4'],
            'amount_4' => $data['amount_4'],
            'quantity_5' => $data['quantity_5'],
            'rate_5' => $data['rate_5'],
            'amount_5' => $data['amount_5'],
            'total_amount' => $data['total_amount'],
            'paid_amount' => $data['paid_amount'],
            'balance_amount' => $data['balance_amount'],
            'status' => $data['status'], // 1 - Approved, 2 - Pending Approval, 3 - Rejected, 4 - Deleted
            'payment_status' => $data['payment_status'], // 1 - Payment Completed, 2 - Payment Incompleted, 3 - Payment Pending
            'description' => $data['description'],
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_purchases', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }
    
    public function add_purchase_suppliers($data)
    {
        $data = array(
            'purchase_id' => $data['purchase_id'],
            'supplier_id' => $data['supplier_id'],
            'invoice_num' => $data['invoice_num'],
            'invoice_weight' => $data['invoice_weight'],
            'actual_weight' => $data['actual_weight'],
            'pending_weight' => $data['pending_weight'],
            'received_weight' => $data['received_weight'],
            'status' => $data['status'],
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id')
        );

        if ($query = $this->db->insert('tbl_purchase_suppliers', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_purchase_supplier_accounts($id, $data)
    {
        $update_data = array(
            'pending_weight' => $data['pending_weight'],
            'received_weight' => $data['received_weight'],
            'status' => $data['status'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_purchase_suppliers', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }
    
    public function get_purchase_details_by_id($id)
    {
        $this->db->select('p.*, p.status as purchase_status, c.id as carrier_id, c.carrier_name, c.contact_number, c.status as carrier_status');
        $this->db->from('tbl_purchases p');
        $this->db->join('tbl_carriers c','c.id = p.carrier_id');
        $this->db->where('p.id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }

    }

    public function get_purchase_suppliers_by_purchase_id($purchase_id)
    {
        $this->db->select('ps.*, ps.status as purchase_status, s.id as supplier_id, s.supplier_name, s.contact_number, s.status as supplier_status');
        $this->db->from('tbl_purchase_suppliers ps');
        $this->db->join('tbl_suppliers s','s.id = ps.supplier_id');
        $this->db->where('ps.purchase_id', $purchase_id);
        $query = $this->db->get();
        if ($query->num_rows() >= 1) {
            $data = $query->result_array();
            return $data;
        } else {
            return false;
        }

    }

    public function get_purchase_supplier_by_id($id)
    {
        $this->db->select('*');
        $this->db->from('tbl_purchase_suppliers');
        $this->db->where('id', $id);
        $this->db->limit(1);
        $query = $this->db->get();
        if ($query->num_rows() == 1) {
            $data = $query->row();
            return $data;
        } else {
            return false;
        }
    }
    
    public function add_carrier_charges_payment($data)
    {       
        $data = array(
            'date' => $data['date'],
            'carrier_id' => $data['carrier_id'],
            'purchase_id' => $data['purchase_id'],
            'payment_method' => $data['payment_method'],
            'account_id' => $data['account_id'],
            'cheque_number' => $data['cheque_number'],
            'cheque_deposit_date' => $data['cheque_deposit_date'],
            'third_party_bank_id' => $data['third_party_bank_id'],
            'third_party_account_number' => $data['third_party_account_number'],
            'amount' => $data['amount'],
            'status' => $data['status'], // 1 - completed
            'added_on' => date('Y-m-d H:i:s'),
            'added_by' => $this->session->userdata('admin_id'),
            'updated_on' => date('Y-m-d H:i:s'),
            'updated_by' => $this->session->userdata('admin_id'),

        );

        if ($query = $this->db->insert('tbl_carrier_charges_payments', $data)) {
            return array('id' => $this->db->insert_id(), 'status' => true);
        } else {
            return false;
        }
    }

    public function update_purchase_accounts($id, $data)
    {
        $update_data = array(
            'total_amount' => $data['total_amount'],
            'paid_amount' => $data['paid_amount'],
            'balance_amount' => $data['balance_amount'],
            'payment_status' => $data['payment_status'],
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_purchases', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    public function change_purchase_status($id, $status)
    {
        $update_data = array(
            'updated_on' => date("Y-m-d H:i:s"),
            'updated_by' => $this->session->userdata('admin_id'),
            'status' => $status,

        );
        $this->db->where('id', $id);
        $query = $this->db->update('tbl_purchases', $update_data);
        if ($query) {
            return true;
        } else {
            return false;
        }
    }

    function dxb_sales_return_report($from_date, $to_date) {
        $sql = "SELECT rsi.id, rs.date, rs.invoice_num, rs.sale_type, c.customer_name, rsi.item_name, s.status as sale_status, rsi.sales_grams, rs.24k_rate, rsi.selling_rate, rsi.amount "
            . "FROM tbl_return_sale_items rsi JOIN tbl_return_sales rs ON rs.id = rsi.return_sale_id "
            . "JOIN tbl_sales s ON s.id = rs.sale_id "
            . "JOIN tbl_customers c ON c.id = rs.customer_id "
            . "WHERE (rs.date BETWEEN '".$from_date."' AND '".$to_date."') ORDER BY rsi.id desc";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }

    function box_sales_return_report($from_date, $to_date) {
        $sql = "SELECT rsi.id, rs.id as box_return_sale_id, rs.date, rs.invoice_num, rs.sale_type, c.customer_name, rsi.item_name, s.status as sale_status, rsi.sales_grams, rs.24k_rate, rsi.selling_rate, rsi.amount "
            . "FROM tbl_box_return_sale_items rsi JOIN tbl_box_return_sales rs ON rs.id = rsi.box_return_sale_id "
            . "JOIN tbl_box_sales s ON s.id = rs. box_sale_id "
            . "JOIN tbl_customers c ON c.id = rs.customer_id "
            . "WHERE (rs.date BETWEEN '".$from_date."' AND '".$to_date."') ORDER BY rsi.id desc";
        $result = $this->db->query($sql);
        if ($result->num_rows() >= 1) {
            $data = $result->result_array();
            return $data;
        } else {
            return false;
        }
    }

    //end of Admin_db
}
