<?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']."%'";
            }

            $query = "SELECT vm.*,cl.client_name FROM voucher_master vm LEFT JOIN client_master cl ON(vm.vm_party_id = cl.client_id)
                WHERE vm.vm_grp_id = 5 AND vm.vm_fin_year = '$fin_year' $subsql";

            return $this->db->query($query)->num_rows();

            // echo $query;exit;
            return $this->db->query($query)->num_rows();
        }

        public function get_all_voucher_data($fin_year,$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']."%'";
            }  


            $vm_query = "SELECT vm.*,cl.client_name FROM voucher_master vm LEFT JOIN client_master cl ON(vm.vm_party_id = cl.client_id)
                WHERE vm.vm_grp_id = 5 AND vm.vm_fin_year = '$fin_year' $subsql ORDER BY vm.vm_entry_no DESC LIMIT $per_page OFFSET $offset";

            // echo $vm_query;exit();

            $record['voucher_data'] = $this->db->query($vm_query)->result_array();

            foreach ($record['voucher_data'] as $key => $value) 
            {
                $record['voucher_data'][$key]['vt_trans_cnt'] = $this->db->query("SELECT * FROM voucher_trans WHERE vt_vm_id = ".$value['vm_id']."")->num_rows();
            }

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

            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);

            $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'];
            }

            

            $client_data = $this->db->query("SELECT client_id,client_name FROM client_master")->result_array();
            $record['client_data'][0] = "PLEASE SELECT";
            foreach ($client_data as $key => $value) {
                $record['client_data'][$value['client_id']] = $value['client_name'];
            }

            $karigar_data = $this->db->query("SELECT km_id,km_name FROM karigar_master ORDER BY km_name ASC")->result_array();
            $record['karigar_data'][0] = "PLEASE SELECT";
            foreach ($karigar_data as $key => $value) {
                $record['karigar_data'][$value['km_id']] = $value['km_name'];
            }

            // echo "<pre>";print_r($record);exit();

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

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


            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['voucher_type'] = $_GET['type'];
            
            return $record;
        }
        function get_party_list_data($grp_id)
        {
            $query = '';
            if($grp_id == 23)
            {
                $query = "SELECT km.km_id, km.km_name FROM karigar_master km ORDER BY km.km_name";
            }
            else if ($grp_id == 5)
            {
                $query = "SELECT client_id, client_name FROM client_master ORDER BY client_name";
            }

            return $this->db->query($query)->result_array();;
        }

        public function get_party_acc_status($acc_id,$grp_id)
        {
            $result = array();
            $order_amt = 0;

            if ($grp_id == 5) 
            {
                $acc_data = $this->db->query("SELECT client_bal FROM client_master WHERE client_id = $acc_id")->result_array();
                $acc_bal = $acc_data[0]['client_bal'];
            }
            else
            {
                $acc_bal = 0;
            }

            $vm_query = "SELECT IFNULL(SUM(IF(v.vm_trans_type='DR',v.vm_amount,0)),0) AS TVDRTOT, IFNULL(SUM(IF(v.vm_trans_type='CR',v.vm_amount,0)),0) AS TVCRTOT FROM voucher_master v WHERE v.vm_grp_id = $grp_id AND v.vm_party_id = $acc_id";
            $voucher_op_data = $this->db->query($vm_query)->result_array();

            // echo "<pre>";print_r($vm_query);exit();

            if ($grp_id == 5) 
            {
                $order_query = "SELECT IFNULL(SUM(om_final_amt - om_round_off - om_promo_disc_amt - om_advance_amt),0) as final_total FROM order_master om WHERE om.om_client_id='".$acc_id."'";

                // echo $order_query;exit();

                $order_amt = $this->db->query($order_query)->result_array()[0]['final_total'];
                
                // echo $order_amt;exit();

                $total_cr_amt = $acc_bal + $voucher_op_data[0]['TVCRTOT'];
                $total_dr_amt = $acc_bal + $order_amt + $voucher_op_data[0]['TVDRTOT'];

                // echo "<pre>";print_r($total_cr_amt);
                // echo "<pre>";print_r($total_dr_amt);exit();

                if($total_cr_amt >= $total_dr_amt)
                {
                    $openiong_amt = $total_cr_amt - $total_dr_amt;

                    if($openiong_amt >= 0)
                    {
                        $openiong_amt = round($openiong_amt)." TO PAY";
                    }
                    else
                    {
                        $openiong_amt = round($openiong_amt)." TO RECEIVE";
                    }
                }
                else
                {
                    $openiong_amt = $total_dr_amt - $total_cr_amt;

                    // echo $openiong_amt;exit();

                    if($openiong_amt >= 0)
                    {
                        $openiong_amt = round($openiong_amt)." TO RECEIVE";
                    }
                    else
                    {
                        $openiong_amt = round($openiong_amt)." TO PAY";
                    }
                }

                $client_query = "SELECT client_mob1 FROM client_master WHERE client_id = $acc_id";

                $client_mob_no = $this->db->query($client_query)->result_array()[0]['client_mob1'];

                $result['acc_mob_no'] = $client_mob_no;
            }
            else
            {
                $hisab_query = "SELECT IFNULL(SUM(hm_amount - hm_round_off),0) as final_total FROM hisab_master WHERE hm_karigar_id = $acc_id";
                $hisab_amt = $this->db->query($hisab_query)->result_array()[0]['final_total'];

                // echo "<pre>";print_r($voucher_op_data[0]['TVCRTOT']);exit();
                // echo "<pre>";print_r($hisab_amt);exit();
                // echo "<pre>";print_r($hisab_amt);exit();

                $total_cr_amt = $acc_bal + $hisab_amt + $voucher_op_data[0]['TVCRTOT'];
                $total_dr_amt = $acc_bal + $voucher_op_data[0]['TVDRTOT'];
                
                // echo "<pre>";print_r($total_cr_amt);
                // echo "<pre>";print_r($total_dr_amt);
                // exit();

                if($total_cr_amt >= $total_dr_amt)
                {
                    $openiong_amt = $total_cr_amt - $total_dr_amt;

                    if($openiong_amt >= 0)
                    {
                        $openiong_amt = round($openiong_amt)." TO PAY";
                    }
                    else
                    {
                        $openiong_amt = round($openiong_amt)." TO RECEIVE";
                    }
                }
                else
                {
                    $openiong_amt = $total_dr_amt - $total_cr_amt;

                    if($openiong_amt >= 0)
                    {
                        $openiong_amt = round($openiong_amt)." TO RECEIVE";
                    }
                    else
                    {
                        $openiong_amt = round($openiong_amt)." TO PAY";
                    }
                }

                $karigar_query = "SELECT km_mob FROM karigar_master WHERE km_id = $acc_id";

                $karigar_mob_no = $this->db->query($karigar_query)->result_array()[0]['km_mob'];

                $result['acc_mob_no'] = $karigar_mob_no;

            }

            $result['status'] = $openiong_amt;
      
            return $result;
        }


        function get_party_payment_status($acc_id, $grp_id)
        {
            $result         = array();
            $hisab_amt      = 0;
            $acc_cr_op_data = 0;
            $acc_dr_op_data = 0;
            $total_op_amt   = 0;

            if($grp_id == 23)
            {
                $acc_data = $this->db->query("SELECT * FROM karigar_master WHERE km_id = $acc_id")->result_array();
                // echo "<pre>"; print_r($acc_data); exit;


                $acc_op_query = "
                                SELECT 
                                IFNULL(SUM(IF(km_cr_dr='CR',km_bal,0)),0) AS TVCRTOT,
                                IFNULL(SUM(IF(km_cr_dr='DR',km_bal,0)),0) AS TVDRTOT 
                                FROM 
                                karigar_master 
                                WHERE 
                                km_id = $acc_id";
                // echo "<pre>"; print_r($acc_op_query); exit;                            
                $acc_op_data = $this->db->query($acc_op_query)->result_array();
                // echo "<pre>"; print_r($acc_op_data); exit;            


                $voucher_op_query = "
                                    SELECT 
                                    IFNULL(SUM(IF(v.vm_trans_type='DR',v.vm_amount+v.vm_round_off,0)),0) AS TVDRTOT, 
                                    IFNULL(SUM(IF(v.vm_trans_type='CR',v.vm_amount+v.vm_round_off,0)),0) AS TVCRTOT 
                                    FROM 
                                    voucher_master v 
                                    WHERE 
                                    v.vm_party_id=$acc_id 
                                    AND v.vm_grp_id = $grp_id
                                    AND v.vm_refer_id = 0
                                    ";
                // echo "<pre>"; print_r($voucher_op_query); exit;            
                $voucher_op_data = $this->db->query($voucher_op_query)->result_array();
                // echo "<pre>"; print_r($voucher_op_data); exit;

                $hisab_query ="
                                SELECT 
                                IFNULL(SUM(hm_total_amt),0) as hm_total_amt 
                                FROM 
                                hisab_master hm 
                                WHERE 
                                hm.hm_karigar_id = $acc_id
                              ";
                // echo "<pre>"; print_r($purchase_amt_query); exit;            
                $hisab_amt = $this->db->query($hisab_query)->result_array()[0]['hm_total_amt'];
                // echo "<pre>"; print_r($hisab_amt); exit;

                // $total_op_amt = 0;
                // if($acc_op_data[0]['TVCRTOT'] > 0)
                // {
                //     $total_op_amt = $acc_op_data[0]['TVCRTOT'];
                // }
                // else
                // {
                //     $total_op_amt = $acc_op_data[0]['TVDRTOT'];
                // }
                $total_cr_amt = $acc_op_data[0]['TVCRTOT'] + $hisab_amt + $voucher_op_data[0]['TVDRTOT'];
                $total_dr_amt = $acc_op_data[0]['TVDRTOT'] + $voucher_op_data[0]['TVCRTOT'];

                // echo "<pre>"; print_r($total_cr_amt);   
                // echo "<pre>"; print_r($total_dr_amt); exit;          

                if($total_cr_amt > $total_dr_amt)
                {
                    $openiong_amt = $total_cr_amt - $total_dr_amt;

                    if($openiong_amt > 0)
                    {
                        $openiong_amt = round($openiong_amt)." CR";
                    }
                    else
                    {
                        $openiong_amt = round($openiong_amt)." DR";
                    }
                }
                else
                {
                    $openiong_amt = $total_dr_amt - $total_cr_amt;

                    if($openiong_amt > 0)
                    {
                        $openiong_amt = round($openiong_amt)." DR";
                    }
                    else
                    {
                        $openiong_amt = round($openiong_amt)." CR";
                    }
                }
            }
            else
            {

            }
            $result['status'] = $openiong_amt;
      
            return $result;
        }
        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 
                        hm.*,(hm.hm_total_amt - (hm.hm_allocated_amt + hm.hm_adjust_round_off)) as bal_amt,
                        DATE_FORMAT(hm.hm_entry_date,'%d-%m-%Y') AS hm_entry_date
                    FROM 
                        hisab_master hm
                    WHERE 
                        hm.hm_karigar_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 = array();

            $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'];
            }

            

            $client_data = $this->db->query("SELECT client_id,client_name FROM client_master")->result_array();
            $record['client_data'][0] = "PLEASE SELECT";
            foreach ($client_data as $key => $value) {
                $record['client_data'][$value['client_id']] = $value['client_name'];
            }

            $karigar_data = $this->db->query("SELECT km_id,km_name FROM karigar_master ORDER BY km_name ASC")->result_array();
            $record['karigar_data'][0] = "PLEASE SELECT";
            foreach ($karigar_data as $key => $value) {
                $record['karigar_data'][$value['km_id']] = $value['km_name'];
            }

            // echo "<pre>";print_r($record);exit();

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

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


            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] = "PLEASE SELECT";

            $record['voucher_type'] = $_GET['type'];

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

            $voucher_trans_query = "SELECT *,DATE_FORMAT(v.vt_entry_date, '%d-%m-%Y') AS vt_bill_date,o.* FROM voucher_trans v LEFT JOIN order_master o ON(v.vt_om_id = o.om_id) WHERE vt_vm_id = $vm_id";
            // echo $voucher_trans_query;exit();
            $record['voucher_trans'] = $this->db->query($voucher_trans_query)->result_array();


            // echo "<pre>";print_r($record['voucher_trans']);exit();

            $acc_id     = $record['voucher_data'][0]['vm_cash_acc_id'];
            $party_id   = $record['voucher_data'][0]['vm_party_id'];

            if ($record['voucher_data'][0]['vm_grp_id'] == 5) 
            {
                $client_data = $this->db->query("SELECT client_id,client_name FROM client_master WHERE client_id = $party_id")->result_array();
                $record['party_id'] = $client_data[0]['client_id'];
                $record['party_name'] = $client_data[0]['client_name'];
            }
            else
            {
                $karigar_data = $this->db->query("SELECT km_id,km_name FROM karigar_master WHERE km_id = $party_id ORDER BY km_name ASC")->result_array();
                $record['party_id'] = $karigar_data[0]['km_id'];
                $record['party_name'] = $karigar_data[0]['km_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 ***************************************     


        public function get_party_order_details($acc_id,$grp_id)
        {
            
            if ($grp_id == 5) 
            {
                $query = "SELECT om.*,(om.om_final_amt - (om.om_allocated_amt + om.om_voucher_round_off_amt + om.om_promo_disc_amt + om.om_round_off + om.om_advance_amt)) as bal_amt,DATE_FORMAT(om.om_entry_date,'%d-%m-%Y') AS om_bill_date FROM order_master om WHERE om.om_client_id = $acc_id HAVING bal_amt > 0";
                
                return  $this->db->query($query)->result_array();
            }
            else
            {
                $query2 = "SELECT hm.*,(hm.hm_amount - (hm.hm_allocated_amt + hm.hm_round_off)) as bal_amt,DATE_FORMAT(hm.hm_entry_date,'%d-%m-%Y') AS hm_entry_date FROM hisab_master hm WHERE hm.hm_karigar_id = $acc_id HAVING bal_amt > 0";
                
                return  $this->db->query($query2)->result_array();
            }
        }

        public function check_duplicate_voucher_entry($entry_no,$fin_year)
        {
            $query = "SELECT vm_id FROM voucher_master WHERE vm_entry_no = '$entry_no' AND vm_fin_year = '$fin_year'";
            return $this->db->query($query)->num_rows();
        }

        public function check_edit_duplicate_voucher_entry($entry_no,$fin_year,$vm_id)
        {
            $query = "SELECT vm_id FROM voucher_master WHERE vm_entry_no = '$entry_no' AND vm_fin_year = '$fin_year' AND vm_id != $vm_id";
            return $this->db->query($query)->num_rows();
        }



             
    }
?>