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

	class Vouchermdl extends CI_model
    {

// ********************* VOUCHER MASTER START ***************************************
		public function get_all_voucher_data_cnt()
        {
            $subsql = "";
            $fin_year = $this->session->userdata('fin_year');
            if(!empty($_GET['search_value_entry_no']))
            {
                $subsql .= " AND vm.vm_entry_no like '%".$_GET['search_value_entry_no']."%'";
            } 
            if(!empty($_GET['search_value_entry_date']))
            {
                $subsql .= " AND vm.vm_entry_date like '%".$_GET['search_value_entry_date']."%'";
            }
            if(!empty($_GET['search_value_v_type']))
            {
                $subsql .= " AND vm.vm_voucher_type like '%".$_GET['search_value_v_type']."%'";
            }
            if(!empty($_GET['search_value_ac_type']))
            {
                $subsql .= " AND vm.vm_cash_acc_id like '%".$_GET['search_value_ac_type']."%'";
            }
            if(!empty($_GET['search_value_party_name']))
            {
                $subsql .= " AND am.acc_name like '%".$_GET['search_value_party_name']."%'";
            }
            if(!empty($_GET['search_value_group']))
            {
                $subsql .= " AND vm.vm_grp_id like '%".$_GET['search_value_group']."%'";
            }
            if(!empty($_GET['search_value_amt']))
            {
                $subsql .= " AND vm.vm_amount like '%".$_GET['search_value_amt']."%'";
            }
            if(!empty($_GET['search_value_chq_no']))
            {
                $subsql .= " AND vm.vm_cheque_no like '%".$_GET['search_value_chq_no']."%'";
            }    
            $query = "
                        SELECT vm.*
                        FROM voucher_master vm
                        LEFT JOIN client_master client ON(vm.vm_party_id = client.client_id)
                        LEFT JOIN karigar_master km ON(vm.vm_party_id = km.km_id)
                        WHERE vm.vm_refer_id = 0
                        AND vm.vm_fin_year = '$fin_year'
                        $subsql
                        ORDER BY vm.vm_id DESC
                    ";
            // echo $query;exit;
            return $this->db->query($query)->num_rows();
        }
        public function get_all_voucher_data($per_page, $offset)
        {
            $subsql = "";
            $record = [];
            $fin_year = $this->session->userdata('fin_year');

            if(!empty($_GET['search_value_entry_no']))
            {
                $subsql .= " AND vm.vm_entry_no like '%".$_GET['search_value_entry_no']."%'";
            } 
            if(!empty($_GET['search_value_entry_date']))
            {
                $subsql .= " AND vm.vm_entry_date like '%".date('Y-m-d', strtotime($_GET['search_value_entry_date']))."%'";
            }
            if(!empty($_GET['search_value_v_type']))
            {
                $subsql .= " AND vm.vm_voucher_type like '%".$_GET['search_value_v_type']."%'";
            }
            if(!empty($_GET['search_value_ac_type']))
            {
                $subsql .= " AND vm.vm_cash_acc_id like '%".$_GET['search_value_ac_type']."%'";
            }
            if(!empty($_GET['search_value_party_name']))
            {
                $subsql .= " AND am.acc_name like '%".$_GET['search_value_party_name']."%'";
            }
            if(!empty($_GET['search_value_group']))
            {
                $subsql .= " AND vm.vm_grp_id like '%".$_GET['search_value_group']."%'";
            }
            if(!empty($_GET['search_value_amt']))
            {
                $subsql .= " AND vm.vm_amount like '%".$_GET['search_value_amt']."%'";
            }  
            if(!empty($_GET['search_value_chq_no']))
            {
                $subsql .= " AND vm.vm_cheque_no like '%".$_GET['search_value_chq_no']."%'";
            } 
            $record['voucher_data'] = $this->db->query("
                    SELECT 
                        vm.*,client.client_name, km.km_name, km.km_code
                    FROM 
                        voucher_master vm  
                    LEFT JOIN 
                            karigar_master km ON(km.km_id = vm.vm_party_id)
                    LEFT JOIN 
                            client_master client ON(client.client_id = vm.vm_party_id)
                    WHERE 
                        vm.vm_fin_year = '$fin_year'
                        $subsql 
                    ORDER BY 
                        vm.vm_id DESC 
                    LIMIT
                        $per_page 
                    OFFSET
                        $offset
                ")->result_array(); 

            $record['v_type'][0] = 'ALL';     
            $record['v_type'][1] = 'PAYMENT';     
            $record['v_type'][2] = 'RECEIPT'; 

            
            $account_data = $this->db->query("SELECT * FROM account_master WHERE acc_grp_id = 9")->result_array();        

            $record['account_data'][0] = "SELECT";
            foreach ($account_data as $key => $value) {
                $record['account_data'][$value['acc_id']] = $value['acc_name'];
            }

            $record['group_data'][0] = "SELECT";
            // $record['group_data'][1] = "GENERAL";
            $record['group_data'][5] = "CLIENT";
            // $record['group_data'][6] = "SUPPLIER";
            $record['group_data'][23] = "KARIGAR";
            return $record ;
        }
        public function get_voucher_data()
        {
            $record = array();
            $fin_year = $this->session->userdata('fin_year');
            // $record['entry_no'] = $this->db_operations->get_max_id_custom('voucher_master', 'vm_entry_no');
            $record['entry_no'] = $this->db_operations->get_fin_year_max_id_custom('voucher_master', 'vm_entry_no', 'vm_fin_year', $fin_year);

            if($_GET['type'] == "OPENING")
            {
                $account_data = $this->db->query("SELECT * FROM account_master WHERE acc_id = 9")->result_array();
            }
            else
            {
                $account_data = $this->db->query("SELECT * FROM account_master WHERE acc_grp_id = 9")->result_array();
            }

            $record['account_data'][''] = "SELECT";
            $record['account_data']['CASH'] = 'CASH';
            $record['account_data']['BANK'] = 'BANK';

            $record['group_data'][0] = "SELECT";
            // $record['group_data'][1] = "GENERAL";
            $record['group_data'][5] = "CLIENT";
            // $record['group_data'][6] = "SUPPLIER";
            $record['group_data'][23] = "KARIGAR";

            if($_GET['type'] == "PAYMENT")
                $record['voucher_type_data'][1] = "PAYMENT";
            else if($_GET['type'] == "RECEIPT")
                $record['voucher_type_data'][2] = "RECEIPT";
            else
                $record['voucher_type_data'][-1] = "OPENING";

            $record['party_data'][0] = "SELECT GROUP";

            $record['voucher_type'] = $_GET['type'];
            
            $user_data = $this->db_operations->get_record('user_master', ['user_status' => 1]);
            if(!empty($user_data)){
                $record['user_data'][0] = 'SELECT'; 
                foreach ($user_data as $key => $value) {
                    $record['user_data'][$value['user_id']] = strtoupper($value['user_full_name']);     
                }
            }else{
                $record['user_data'][0] = 'NO USER ADDED';   
            }

            return $record;
        }
        function get_party_list_data($grp_id)
        {
            if($grp_id == 5) //Client
            {
                $query ="
                            SELECT client.client_id, client.client_name, client.client_mob1
                            FROM client_master client                            
                            ORDER BY client.client_name ASC
                        ";
            }
            else if($grp_id == 23) //Karigar
            {
                $query ="
                            SELECT km.km_id, km.km_name, km.km_code, km.km_mobile
                            FROM karigar_master km
                            ORDER BY km.km_code ASC
                        ";
            }

            return $this->db->query($query)->result_array();
            
        }
        public function get_client_payment_status($client_id)
        {
            $record = [];
            $client_query= "
                        SELECT client.client_bal as balance
                        FROM client_master client
                        WHERE client.client_id = $client_id
                    ";
            $record['party_data'] = $this->db->query($client_query)->result_array();

            if(1)
            {
                $order_query= "
                        SELECT om.om_id as _id, CONCAT(UPPER(om.om_bill_series),'',om.om_entry_no) as entry_no, 
                        om.om_entry_date as entry_date, om.om_total_qty as total_qty, om.om_final_amt as total_amt, 
                        om.om_allocated_amt as allocated_amt, om.om_adjust_round_off as adjust_round_off, om.om_adjust_amt as adjust_amt
                        FROM order_master om
                        WHERE om.om_client_id = $client_id
                        AND om.om_final_amt > 0
                        AND om.om_delete_status = 0
                    ";
                $record['order_data'] = $this->db->query($order_query)->result_array();    
            }
            
            return $record;
        }
        public function get_karigar_payment_status($km_id)
        {
            $record = [];
            $karigar_query= "
                        SELECT km.km_bal as balance
                        FROM karigar_master km
                        WHERE km.km_id = $km_id
                    ";
            $record['party_data'] = $this->db->query($karigar_query)->result_array();
            if(0)
            {
                $hisab_query= "
                            SELECT hm.hm_id as _id, hm.hm_entry_no as entry_no, hm.hm_entry_date as entry_date, 
                            hm.hm_total_job as total_qty, hm.hm_total_amt as total_amt, hm.hm_allocated_amt as allocated_amt,
                            hm.hm_adjust_round_off as adjust_round_off, hm.hm_adjust_amt as adjust_amt
                            FROM hisab_master hm
                            WHERE hm.hm_km_id = $km_id
                            AND (hm.hm_total_amt - hm.hm_allocated_amt) > 0
                        ";
                $record['hisab_data'] = $this->db->query($hisab_query)->result_array();    
            }
            
            return $record;
        }
        public function get_party_purchase_details($acc_id)
        {
            // return $this->db->query("SELECT pm.*,(pm.pm_final_amt - (pm.pm_allocated_amt + pm.pm_adjust_round_off)) as bal_amt,DATE_FORMAT(pm.pm_entry_date,'%d-%m-%Y') AS pm_entry_date,DATE_FORMAT(pm.pm_bill_date,'%d-%m-%Y') AS pm_bill_date  FROM purchase_master pm WHERE pm.pm_supplier_id = $acc_id HAVING bal_amt > 0")->result_array();
            $query = "
                        SELECT 
                            pm.*,(pm.pm_final_amt - (pm.pm_allocated_amt + pm.pm_adjust_round_off)) as bal_amt,
                            DATE_FORMAT(pm.pm_entry_date,'%d-%m-%Y') AS pm_entry_date,pm.pm_bill_date AS pm_bill_date1, 
                            DATE_FORMAT(pm.pm_bill_date,'%d-%m-%Y') AS pm_bill_date 
                        FROM 
                            purchase_master pm 
                        WHERE 
                            pm.pm_acc_id = $acc_id 
                        HAVING 
                            bal_amt > 0
                    ";
            // echo "<pre>"; print_r($query);exit;

            return $this->db->query($query)->result_array();
            // echo "<pre>"; print_r($purchase_data);exit;
        }
        public function get_single_update_voucher($vm_id)
        {
            $record = [];
            
            $master_query="
                            SELECT vm.*, IF(vm.vm_cheque_date = '0000-00-00', '', DATE_FORMAT(vm.vm_cheque_date, '%d-%m-%Y')) as vm_cheque_date, 
                            client.client_name, client.client_mob1, km.km_name, km.km_code, km.km_mobile, 
                            UPPER(pre.user_full_name) as prepared, UPPER(chk.user_full_name) as checked
                            FROM voucher_master vm
                            LEFT JOIN karigar_master km ON(km.km_id = vm.vm_party_id)
                            LEFT JOIN client_master client ON(client.client_id = vm.vm_party_id)
                            LEFT JOIN user_master pre ON(vm.vm_prepared_id = pre.user_id)
                            LEFT JOIN user_master chk ON(vm.vm_checked_id = chk.user_id)
                            WHERE vm.vm_id = $vm_id
                          ";
            $record['master_data'] = $this->db->query($master_query)->result_array();
            $record['master_data'][0]['grp_name'] = $record['master_data'][0]['vm_grp_id'] == 5 ? 'CLIENT' : 'KARIGAR';


            $trans_query="
                            SELECT vt.*, om.om_allocated_amt, hm.hm_allocated_amt
                            FROM voucher_trans vt
                            LEFT JOIN order_master om ON(om.om_id = vt.vt_om_id)
                            LEFT JOIN hisab_master hm ON(hm.hm_id = vt.vt_hm_id)
                            WHERE vt.vt_vm_id = $vm_id
                            AND om.om_delete_status = 0
                          ";
            $record['trans_data'] = $this->db->query($trans_query)->result_array();

            $record['voucher_type'] = $_GET['type'];
            $user_data = $this->db_operations->get_record('user_master', ['user_status' => 1]);
            if(!empty($user_data)){
                $record['user_data'][0] = 'SELECT'; 
                foreach ($user_data as $key => $value) {
                    $record['user_data'][$value['user_id']] = strtoupper($value['user_full_name']);     
                }
            }else{
                $record['user_data'][0] = 'NO USER ADDED';   
            }            
            return $record;
        }
        public function get_single_update_voucher1($vm_id)
        {

            $record['voucher_data'] = $this->db->query("SELECT v.*,DATE_FORMAT(v.vm_entry_date, '%d-%m-%Y') AS vm_entry_date FROM voucher_master v WHERE v.vm_id=$vm_id")->result_array();

            $record['voucher_trans'] = $this->db->query("SELECT vt.*,DATE_FORMAT(vt.vt_entry_date, '%d-%m-%Y') AS vt_entry_date,DATE_FORMAT(vt.vt_bill_date, '%d-%m-%Y') AS vt_bill_date FROM voucher_trans vt WHERE vt.vt_vm_id = $vm_id")->result_array();

            if($record['voucher_data'][0]['vm_voucher_type'] == -1)
                $account_data = $this->db_operations->get_record("account_master", array('acc_id' => 9));
            else
                $account_data = $this->db_operations->get_record("account_master", array('acc_grp_id' => 9));

            $record['account_data'][0] = "PLEASE SELECT";
            foreach ($account_data as $key => $value) {
                $record['account_data'][$value['acc_id']] = $value['acc_name'];
            }

        
            // $group_data = $this->db->query("SELECT * FROM group_master WHERE grp_id IN(1,5,6)")->result_array();
            // $record['group_data'][0] = "PLEASE SELECT";
            // foreach ($group_data as $key => $value) {
            //  $record['group_data'][$value['grp_id']] = $value['grp_name'];
            // }
            $record['group_data'][0] = "PLEASE SELECT";
            $record['group_data'][1] = "GENERAL";
            $record['group_data'][5] = "CUSTOMER";
            $record['group_data'][6] = "SUPPLIER";
            $record['group_data'][23] = "EMPLOYEE";

            if($record['voucher_data'][0]['vm_voucher_type'] == 1)
            {
                $record['voucher_type_data'][1] = "PAYMENT";
                $record['voucher_type'] = "PAYMENT";
            }
            else if($record['voucher_data'][0]['vm_voucher_type'] == 2)
            {
                $record['voucher_type_data'][2] = "RECEIPT";
                $record['voucher_type'] = "RECEIPT";
            }
            else
            {
                $record['voucher_type_data'][-1] = "OPENING";
                $record['voucher_type'] = "OPENING";
            }
                
            if($record['voucher_data'][0]['vm_grp_id'] == 1)
            {
                $party_data = $this->db->query("SELECT * FROM account_master WHERE acc_grp_id NOT IN(5,6) ORDER BY acc_name")->result_array();
            }
            else
            {
                $party_data = $this->db->query("SELECT * FROM account_master WHERE acc_grp_id = ".$record['voucher_data'][0]['vm_grp_id']." ORDER BY acc_name")->result_array();
            }
            

            $record['party_data'][0] = "PLEASE SELECT";
            foreach ($party_data as $key => $value) {
                $record['party_data'][$value['acc_id']] = $value['acc_name'];
            }

            return $record;
        }
        public function get_party_sale_details($acc_id)
        {
            
            // return  $this->db->query("SELECT 
            //     sm.*,(sm.sm_final_total - (sm.sm_allocated_amt + sm.sm_adjust_round_off)) as bal_amt,DATE_FORMAT(sm.sm_bill_date,'%d-%m-%Y') AS sm_bill_date  
            //     FROM sales_master sm 
            //     WHERE sm.sm_cust_id = $acc_id HAVING bal_amt > 0")->result_array();

            return  $this->db->query("SELECT 
                im.*,(im.im_final_amt - (im.im_allocated_amt + im.im_adjust_round_off)) as bal_amt,im.im_est_date AS im_est_date1,DATE_FORMAT(im.im_est_date,'%d-%m-%Y') AS im_est_date, im.im_inv_date AS im_inv_date1,DATE_FORMAT(im.im_inv_date,'%d-%m-%Y') AS im_inv_date  
                FROM invoice_master im 
                WHERE im.im_acc_id = $acc_id HAVING bal_amt > 0")->result_array();
        }
// ********************* VOUCHER MASTER END ***************************************        
             
	}
?>