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

	class Challanmdl extends CI_model{
	/*challan master*/
    	public function get_all_challan_data_cnt()
        {
            // echo "<pre>"; print_r($_GET);exit;
            $subsql = "";
        
            if(!empty($_GET['search_value_entry_no']))
            {
                $subsql .= " AND cm.cm_id =".$_GET['search_value_entry_no'];
            } 
            if(!empty($_GET['search_value_entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['search_value_entry_date']));
                $subsql .= " AND cm.cm_entry_date = '$entry_date'";
            }
            if(!empty($_GET['search_value_cust_id']))
            {
                $subsql .= " AND cm.cm_cust_id =".$_GET['search_value_cust_id'];
            }
            if(!empty($_GET['search_value_broker_id']))
            {
                $subsql .= " AND cm.cm_broker_id =".$_GET['search_value_broker_id'];
            }
            return $this->db->query("SELECT cm.* 
                    FROM challan_master cm                    
                    LEFT JOIN account_master cust ON(cust.acc_id = cm.cm_cust_id)
                    LEFT JOIN account_master brk ON(brk.acc_id = cm.cm_broker_id)
                    WHERE cm.cm_fin_year = '".$_SESSION['fin_year']."' 
                    $subsql
                ")->num_rows();
        }
        public function get_all_challan_data($per_page, $offset)
        {
            $subsql = "";
        
            if(!empty($_GET['search_value_entry_no']))
            {
                $subsql .= " AND cm.cm_id =".$_GET['search_value_entry_no'];
            }
            if(!empty($_GET['search_value_entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['search_value_entry_date']));
                $subsql .= " AND cm.cm_entry_date = '$entry_date'";
            }
            if(!empty($_GET['search_value_cust_id']))
            {
                $subsql .= " AND cm.cm_cust_id =".$_GET['search_value_cust_id'];
            }
            if(!empty($_GET['search_value_broker_id']))
            {
                $subsql .= " AND cm.cm_broker_id =".$_GET['search_value_broker_id'];
            }
            $challan_query = "SELECT cm.*, cust.acc_name as customer, brk.acc_name as broker
                    FROM challan_master cm
                    LEFT JOIN account_master cust ON(cust.acc_id = cm.cm_cust_id)
                    LEFT JOIN account_master brk ON(brk.acc_id = cm.cm_broker_id)
                    WHERE cm.cm_fin_year = '".$_SESSION['fin_year']."' 
                    $subsql 
                    ORDER BY cm.cm_id DESC 
                    LIMIT $per_page 
                    OFFSET $offset";
            // echo "<pre>"; print_r($challan_query); exit;
            $record['challan_data'] = $this->db->query($challan_query)->result_array();

            $challan_data = $this->db->query("SELECT cm.* FROM challan_master cm")->result_array();
            // entry no
            $record['entry_no_drop_down'][0] = 'ALL';
            foreach ($challan_data as $key => $value) 
            {
            	$record['entry_no_drop_down'][$value['cm_id']] = $value['cm_entry_no'];
            }
            // entry no

            // customer
            $customer_data = $this->db->query("SELECT cust.acc_id, cust.acc_name FROM challan_master cm
                    LEFT JOIN account_master cust ON(cust.acc_id = cm.cm_cust_id)
                    WHERE cust.acc_grp_id = 5 ORDER BY cm.cm_id DESC")->result_array();
            $record['customer_drop_down'][0] = 'ALL';
            foreach ($customer_data as $key => $value) 
            {
                $record['customer_drop_down'][$value['acc_id']] = strtoupper($value['acc_name']);
            }
            // customer

            // broker
            $broker_data = $this->db->query("SELECT brk.acc_id, brk.acc_name FROM challan_master cm
                    LEFT JOIN account_master brk ON(brk.acc_id = cm.cm_broker_id)
                    WHERE brk.acc_grp_id = 23 ORDER BY cm.cm_id DESC")->result_array();
            $record['broker_drop_down'][0] = 'ALL';
            foreach ($broker_data as $key => $value) 
            {
                $record['broker_drop_down'][$value['acc_id']] = strtoupper($value['acc_name']);
            }
            // broker
            

            return $record;
           
        }    
        public function get_challan_data_for_add()
        {
            $record['entry_no']   = $this->db_operations->get_fin_year_max_id('challan_master', 'cm_entry_no', 'cm_fin_year');

            // customer drop down
            $customer_query = "SELECT cust.* FROM account_master cust WHERE cust.acc_grp_id = 5";
            $customer_data = $this->db->query($customer_query)->result_array();
            $record['customer_data'][0] = 'PLEASE SELECT';
            foreach ($customer_data as $key => $value) 
            {
                $record['customer_data'][$value['acc_id']] = strtoupper($value['acc_name']) .' - '.$value['acc_gst'];
            }
            // customer drop down

            // broker drop down
            $broker_query = "SELECT brk.* FROM account_master brk WHERE brk.acc_grp_id = 23";
            $broker_data = $this->db->query($broker_query)->result_array();
            $record['broker_data'][0] = 'PLEASE SELECT';
            foreach ($broker_data as $key => $value) 
            {
                $record['broker_data'][$value['acc_id']] = strtoupper($value['acc_name']) .' - '.$value['acc_pan'].' - '.$value['acc_mobile'];
            }
            // broker drop down            

            // diamond drop down
            $diamond_query = "SELECT dm.* FROM diamond_master dm WHERE dm.dm_status = 0 OR dm.dm_status = 4";
            $diamond_data = $this->db->query($diamond_query)->result_array();
            $record['diamond_data'][0] = 'PLEASE SELECT';
            foreach ($diamond_data as $key => $value) 
            {
                $record['diamond_data'][$value['dm_id']] = strtoupper($value['dm_code']);
            }
            // diamond drop down            

            return $record;
        }
        public function get_diamond_data($dm_id)
        {
            $diamond_query = "SELECT dm.*, ct.ct_id, ct.ct_dm_disc FROM diamond_master dm LEFT JOIN challan_trans ct ON(ct.ct_dm_id = dm.dm_id) WHERE dm.dm_id = $dm_id ORDER BY ct.ct_id DESC LIMIT 1";
            $diamond_data = $this->db->query($diamond_query)->result_array();

            return $diamond_data;
        }
        public function get_challan_data_for_edit($cm_id)
        {
            $record = [];
            $cm_query = "SELECT cm.*, cust.acc_name as customer,  cust.acc_address, cust.acc_gst as customer_gst, cust.acc_mobile as customer_mobile, cust.acc_tel_1 as qbc_no, brk.acc_name as broker, brk.acc_pan as broker_pan, brk.acc_mobile as broker_mobile, cust.acc_type, brm.bank_rate,  cm.cm_notes FROM challan_master cm LEFT JOIN account_master cust ON(cust.acc_id = cm.cm_cust_id) LEFT JOIN account_master brk ON(brk.acc_id = cm.cm_broker_id) LEFT JOIN bank_rate_master brm ON(brm.bank_rate_date = cm.cm_entry_date) WHERE cm.cm_id = $cm_id";
            $record['cm_data']  = $this->db->query($cm_query)->result_array();
            $record['cm_data'][0]['is_editable'] = 1;
            $ct_query = "SELECT ct.*, dm.* FROM challan_trans ct LEFT JOIN diamond_master dm ON(dm.dm_id = ct.ct_dm_id) WHERE ct.ct_cm_id = $cm_id";
            $record['ct_data']  = $this->db->query($ct_query)->result_array();

            // customer drop down
            $customer_query = "SELECT cust.* FROM account_master cust WHERE cust.acc_grp_id = 5";
            $customer_data = $this->db->query($customer_query)->result_array();
            $record['customer_data'][0] = 'PLEASE SELECT';
            foreach ($customer_data as $key => $value) 
            {
                $record['customer_data'][$value['acc_id']] = strtoupper($value['acc_name']);
            }
            // customer drop down

            // broker drop down
            $broker_query = "SELECT brk.* FROM account_master brk WHERE brk.acc_grp_id = 23";
            $broker_data = $this->db->query($broker_query)->result_array();
            $record['broker_data'][0] = 'PLEASE SELECT';
            foreach ($broker_data as $key => $value) 
            {
                $record['broker_data'][$value['acc_id']] = strtoupper($value['acc_name']) .' - '.$value['acc_pan'].' - '.$value['acc_mobile'];
            }
            // broker drop down            

            // diamond drop down
            $diamond_query = "SELECT dm.* FROM diamond_master dm WHERE dm.dm_status = 0 OR dm.dm_status = 4";
            $diamond_data = $this->db->query($diamond_query)->result_array();
            $record['diamond_data'][0] = 'PLEASE SELECT';
            foreach ($record['ct_data'] as $key => $value) {
                $record['diamond_data'][$value['ct_dm_id']] = strtoupper($value['ct_dm_code']);
                if($value['ct_delete'] == 0)
                {
                    $record['cm_data'][0]['is_editable'] = 0;
                }
            }
            foreach ($diamond_data as $key => $value) 
            {
                $record['diamond_data'][$value['dm_id']] = strtoupper($value['dm_code']);
            }
            
            // diamond drop down            

            return $record;
        }
        public function get_challan_data_by_id($dm_id)
        {
            return $this->db->query("SELECT cm.* FROM challan_trans ct LEFT JOIN challan_master cm ON(cm.cm_id = ct.ct_cm_id) WHERE ct.ct_dm_id = $dm_id ORDER BY cm.cm_id DESC LIMIT 1")->result_array();
        }
        public function get_challan_return_data_by_id($dm_id)
        {
            return $this->db->query("SELECT crm.* FROM challan_return_trans crt LEFT JOIN challan_return_master crm ON(crm.crm_id = crt.crt_crm_id) WHERE crt.crt_dm_id = $dm_id ORDER BY crm.crm_id DESC LIMIT 1")->result_array();
        } 
        public function get_latest_crt_id($dm_id)
        {
            return $this->db->query("SELECT crt.crt_id FROM challan_return_trans crt WHERE crt.crt_dm_id = $dm_id ORDER BY crt.crt_id DESC LIMIT 1")->result_array();
        }     
    /*/challan master*/

    /*challan return master*/
        public function get_all_challan_return_data_cnt()
        {
            // echo "<pre>"; print_r($_GET);exit;
            $subsql = "";
        
            if(!empty($_GET['search_value_entry_no']))
            {
                $subsql .= " AND crm.crm_id =".$_GET['search_value_entry_no'];
            } 
            if(!empty($_GET['search_value_entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['search_value_entry_date']));
                $subsql .= " AND crm.crm_entry_date = '$entry_date'";
            }
            if(!empty($_GET['search_value_cust_id']))
            {
                $subsql .= " AND crm.crm_cust_id =".$_GET['search_value_cust_id'];
            }
            if(!empty($_GET['search_value_broker_id']))
            {
                $subsql .= " AND crm.crm_broker_id =".$_GET['search_value_broker_id'];
            }
            return $this->db->query("SELECT crm.* 
                    FROM challan_return_master crm                    
                    LEFT JOIN account_master cust ON(cust.acc_id = crm.crm_cust_id)
                    LEFT JOIN account_master brk ON(brk.acc_id = crm.crm_broker_id)
                    WHERE crm.crm_fin_year = '".$_SESSION['fin_year']."' 
                    $subsql
                ")->num_rows();
        }
        public function get_all_challan_return_data($per_page, $offset)
        {
            $subsql = "";
        
            if(!empty($_GET['search_value_entry_no']))
            {
                $subsql .= " AND crm.crm_id =".$_GET['search_value_entry_no'];
            } 
            if(!empty($_GET['search_value_entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['search_value_entry_date']));
                $subsql .= " AND crm.crm_entry_date = '$entry_date'";
            }
            if(!empty($_GET['search_value_cust_id']))
            {
                $subsql .= " AND crm.crm_cust_id =".$_GET['search_value_cust_id'];
            }
            if(!empty($_GET['search_value_broker_id']))
            {
                $subsql .= " AND crm.crm_broker_id =".$_GET['search_value_broker_id'];
            }
            $challan_return_query = "SELECT crm.*, cust.acc_name as customer, brk.acc_name as broker
                    FROM challan_return_master crm
                    LEFT JOIN account_master cust ON(cust.acc_id = crm.crm_cust_id)
                    LEFT JOIN account_master brk ON(brk.acc_id = crm.crm_broker_id)
                    WHERE crm.crm_fin_year = '".$_SESSION['fin_year']."'  
                    $subsql 
                    ORDER BY crm.crm_id DESC 
                    LIMIT $per_page 
                    OFFSET $offset";
            // echo "<pre>"; print_r($challan_return_query); exit;
            $record['challan_return_data'] = $this->db->query($challan_return_query)->result_array();

            $challan_return_data = $this->db->query("SELECT crm.* FROM challan_return_master crm")->result_array();
            // entry no
            $record['entry_no_drop_down'][0] = 'ALL';
            foreach ($challan_return_data as $key => $value) 
            {
                $record['entry_no_drop_down'][$value['crm_id']] = $value['crm_entry_no'];
            }
            // entry no

            // customer
            $customer_data = $this->db->query("SELECT cust.acc_id, cust.acc_name FROM challan_return_master crm
                    LEFT JOIN account_master cust ON(cust.acc_id = crm.crm_cust_id)
                    WHERE cust.acc_grp_id = 5 ORDER BY crm.crm_id DESC")->result_array();
            $record['customer_drop_down'][0] = 'ALL';
            foreach ($customer_data as $key => $value) 
            {
                $record['customer_drop_down'][$value['acc_id']] = strtoupper($value['acc_name']);
            }
            // customer

            // broker
            $broker_data = $this->db->query("SELECT brk.acc_id, brk.acc_name FROM challan_return_master crm
                    LEFT JOIN account_master brk ON(brk.acc_id = crm.crm_broker_id)
                    WHERE brk.acc_grp_id = 23 ORDER BY crm.crm_id DESC")->result_array();
            $record['broker_drop_down'][0] = 'ALL';
            foreach ($broker_data as $key => $value) 
            {
                $record['broker_drop_down'][$value['acc_id']] = strtoupper($value['acc_name']);
            }
            // broker
            

            return $record;
           
        } 
        public function get_challan_return_data_for_add()
        {
            $record['entry_no']   = $this->db_operations->get_fin_year_max_id('challan_return_master', 'crm_entry_no', 'crm_fin_year');

            // customer drop down
            $customer_query = "SELECT cust.*, cm.* FROM challan_master cm LEFT JOIN account_master cust ON(cust.acc_id = cm.cm_cust_id) LEFT JOIN challan_trans ct ON(ct.ct_cm_id = cm.cm_id) LEFT JOIN diamond_master dm ON(ct.ct_dm_id = dm.dm_id) WHERE ct.ct_status = 1 GROUP BY cm.cm_cust_id DESC";
            $customer_data = $this->db->query($customer_query)->result_array();
            // echo "<pre>"; print_r($customer_data); exit;
            $record['customer_data'][0] = 'PLEASE SELECT';
            foreach ($customer_data as $key => $value) 
            {
                if($value['cm_cust_id'] == 0)
                {
                    $record['customer_data']['-1'] = '-';
                }
                else
                {
                    $record['customer_data'][$value['acc_id']] = strtoupper($value['acc_name']) .' - '.$value['acc_gst'];    
                }
                
            }
            // echo "<pre>"; print_r($record['customer_data']); exit;

            // customer drop down

            // broker drop down
            $record['broker_data'][0] = 'SELECT PARTY FIRST';            
            // broker drop down            

            // through drop down
            $record['through_data'][0] = 'SELECT PARTY BROKER';            
            // through drop down            

            // diamond drop down           
            $record['diamond_data'][0] = 'SELECT PARTY OR BROKER';
            // diamond drop down            

            return $record;
        }

        public function get_broker_data_from_challan($cust_id)
        {
            $record = [];
            if($cust_id == '-1')
            {
                $cust_id = 0;
            }
            $broker_query = "SELECT brk.*, cm.* FROM challan_master cm LEFT JOIN account_master brk ON(brk.acc_id = cm.cm_broker_id) LEFT JOIN challan_trans ct ON(ct.ct_cm_id = cm.cm_id) LEFT JOIN diamond_master dm ON(dm.dm_id = ct.ct_dm_id) WHERE cm.cm_cust_id = $cust_id AND ct.ct_status = 1 GROUP BY cm.cm_broker_id";
            $record['broker_data'] = $this->db->query($broker_query)->result_array();

            $through_query = "SELECT cm.* FROM challan_master cm WHERE cm.cm_cust_id = $cust_id GROUP BY cm.cm_through";
            $record['through_data'] = $this->db->query($through_query)->result_array();

            $diamond_query = "SELECT dm.* FROM challan_trans ct LEFT JOIN diamond_master dm ON(dm.dm_id = ct.ct_dm_id) LEFT JOIN challan_master cm ON(cm.cm_id = ct.ct_cm_id) WHERE (ct.ct_status = 1) AND cm.cm_cust_id = $cust_id GROUP BY ct.ct_dm_id" ;
            $record['diamond_data'] = $this->db->query($diamond_query)->result_array();

            return $record;
        }

        public function get_through_data_from_challan($broker_id, $cust_id)
        {
            $record = [];
            if($cust_id == '-1')
            {
                $cust_id = 0;
            }
            if($broker_id == '-1')
            {
                $broker_id = 0;
            }
            $through_query = "SELECT cm.* FROM challan_master cm WHERE cm.cm_cust_id = $cust_id AND cm.cm_broker_id = $broker_id GROUP BY cm.cm_through";
            $record['through_data'] = $this->db->query($through_query)->result_array();

            $diamond_query = "SELECT dm.* FROM challan_trans ct LEFT JOIN diamond_master dm ON(dm.dm_id = ct.ct_dm_id) LEFT JOIN challan_master cm ON(cm.cm_id = ct.ct_cm_id) WHERE ct.ct_status = 1 AND cm.cm_cust_id = $cust_id AND cm.cm_broker_id = $broker_id GROUP BY ct.ct_dm_id";
            $record['diamond_data'] = $this->db->query($diamond_query)->result_array();
            return $record;
        }

        public function get_challan_return_trans_data_for_edit($crm_id)
        {
            $record = [];
            $crm_query = "SELECT crm.*, cust.acc_id as cust_id, cust.acc_name as customer, cust.acc_gst as customer_gst, cust.acc_mobile as customer_mobile, brk.acc_id as broker_id, brk.acc_name as broker, brk.acc_pan as broker_pan, brk.acc_mobile as broker_mobile FROM challan_return_master crm LEFT JOIN account_master cust ON(cust.acc_id = crm.crm_cust_id) LEFT JOIN account_master brk ON(brk.acc_id = crm.crm_broker_id) WHERE crm.crm_id = $crm_id";
            $record['crm_data']  = $this->db->query($crm_query)->result_array();
            $record['crm_data'][0]['is_editable'] = 1;

            $crt_query = "SELECT crt.*, dm.* FROM challan_return_trans crt LEFT JOIN diamond_master dm ON(dm.dm_id = crt.crt_dm_id) WHERE crt.crt_crm_id = $crm_id";
            $record['crt_data']  = $this->db->query($crt_query)->result_array();

            // customer drop down
            
            foreach ($record['crm_data'] as $key => $value) 
            {
                if(empty($value['crm_cust_id']) || $value['crm_cust_id'] <= 0)
                {
                    $record['customer_data'][$value['crm_cust_id']] = '-';
                }
                else
                {
                    $record['customer_data'][$value['cust_id']] = strtoupper($value['customer']) .' - '.$value['customer_gst'];    
                }                
            }
            // customer drop down

            // broker drop down
            foreach ($record['crm_data'] as $key => $value) 
            {
                if(empty($value['broker_id']) || $value['broker_id'] <= 0)
                {
                    $record['broker_data'][$value['broker_id']] = '-';
                }
                else
                {
                    $record['broker_data'][$value['broker_id']] = strtoupper($value['broker']) .' - '.$value['broker_pan'];    
                }                
            }
            // broker drop down            

            // through drop down
            foreach ($record['crm_data'] as $key => $value) 
            {
                $record['through_data'][$value['crm_through']] = strtoupper($value['crm_through']);
            }
            // broker drop down            

            // diamond drop down
            $diamond_query = "SELECT dm.* FROM challan_trans ct LEFT JOIN diamond_master dm ON(dm.dm_id = ct.ct_dm_id) LEFT JOIN challan_master cm ON(cm.cm_id = ct.ct_cm_id) WHERE dm.dm_status = 1 AND cm.cm_cust_id = ".$record['crm_data'][0]['crm_cust_id']." AND cm.cm_broker_id = ".$record['crm_data'][0]['crm_broker_id'];
            $diamond_data = $this->db->query($diamond_query)->result_array();
            $record['diamond_data'][0] = 'PLEASE SELECT';
            foreach ($record['crt_data'] as $key => $value) {
                $record['diamond_data'][$value['crt_dm_id']] = strtoupper($value['crt_dm_code']);
                if($value['crt_delete'] == 0)
                {
                    $record['crm_data'][0]['is_editable'] = 0;
                }
            }
            foreach ($diamond_data as $key => $value) 
            {
                $record['diamond_data'][$value['dm_id']] = strtoupper($value['dm_code']);
            }
            
            // diamond drop down            

            return $record;
        }

    /*/challan return master*/


	}
?>