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

	class Invoicemdl extends CI_model{

		public function get_max_id($table, $field)
		{
		    $this->db->select_max($field, 'max_id');
		    $record = $this->db->get($table)->result_array();

		    if(empty($record[0]['max_id']))
		    {
		       return 1;
		    }
		    else
		    {
		      return $record[0]['max_id']+1;
		    }
		}

		public function get_all_invoice_data_cnt()
		{
		    $subsql = "";
        
            if(!empty($_GET['entry_no']))
            {
                $subsql .= " AND inv.inv_id =".$_GET['entry_no'];
            }

            if(!empty($_GET['entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['entry_date']));
                $subsql .= " AND inv.inv_entry_date = '$entry_date'";
            }

            if(!empty($_GET['cust_id']))
            {
                $subsql .= " AND inv.inv_customer_id =".$_GET['cust_id'];
            }
            
			return $this->db->query("SELECT inv.*, am.*, am.acc_name, am.acc_mobile, am.acc_email, sm.state_name, cm.city_name FROM invoice_master inv 
	    		LEFT JOIN account_master am ON(am.acc_id = inv.inv_customer_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id) WHERE 1 $subsql")->num_rows();
		}

		public function get_all_invoice_data($per_page, $offset)
		{
			$subsql = "";
        
            if(!empty($_GET['entry_no']))
            {
                $subsql .= " AND inv.inv_id =".$_GET['entry_no'];
            }

            if(!empty($_GET['entry_date']))
            {
                $entry_date = date('Y-m-d', strtotime($_GET['entry_date']));
                $subsql .= " AND inv.inv_entry_date = '$entry_date'";
            }

            if(!empty($_GET['cust_id']))
            {
                $subsql .= " AND inv.inv_customer_id =".$_GET['cust_id'];
            }

			$record['inv_data'] = $this->db->query("SELECT inv.*, am.*, am.acc_name, am.acc_mobile, am.acc_email, sm.state_name, cm.city_name FROM invoice_master inv 
	    		LEFT JOIN account_master am ON(am.acc_id = inv.inv_customer_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id)
	    		WHERE 1 $subsql LIMIT $per_page OFFSET $offset")->result_array();

			$challan_data = $this->db->query("SELECT inv.inv_id, inv.inv_entry_no FROM invoice_master inv")->result_array();
            // entry no
            $record['entry_no_drop_down'][0] = 'ALL';
            foreach ($challan_data as $key => $value) 
            {
            	$record['entry_no_drop_down'][$value['inv_id']] = $value['inv_entry_no'];
            }

			$account_data = $this->db->query("SELECT * FROM account_master WHERE acc_grp_id = 5")->result_array();
            $record['account_data'][0] = "PLEASE SELECT";
            foreach ($account_data as $key => $value) {
                $record['account_data'][$value['acc_id']] = $value['acc_name'];
            }
			return $record;
		}
		
		public function get_invoice_form_assets()
	    {

	        $users = $this->db->query("SELECT acc_id, acc_name FROM account_master WHERE acc_grp_id = 5 ORDER BY acc_name ASC ")->result_array();
	        $record['user'][0] = "Please Select";
	        foreach ($users as $key => $value) {
	        	$record['user'][$value['acc_id']] = $value['acc_name'];
	        }

	        // $record['entry_no'] = $this->get_max_id('invoice_master', 'inv_entry_no');
	         $record['entry_no']   = $this->db_operations->get_fin_year_max_id('invoice_master', 'inv_entry_no', 'inv_fin_year');

	        $products = $this->db->query("SELECT pro_id, pro_name FROM product_master WHERE pro_status = 1 ORDER BY pro_name ASC ")->result_array();
	        $record['product'][0] = "Please Select";
	        foreach ($products as $key => $value) {
	        	$record['product'][$value['pro_id']] = $value['pro_name'];
	        }
	        return $record;
	    }

	    public function get_single_invoice_data_for_edit($id)
	    {
	    	$users = $this->db->query("SELECT acc_id, acc_name FROM account_master WHERE acc_grp_id = 5 ORDER BY acc_name ASC ")->result_array();
	        $record['user'][0] = "Please Select";
	        foreach ($users as $key => $value) {
	        	$record['user'][$value['acc_id']] = $value['acc_name'];
	        }

	        $record['entry_no'] = $this->get_max_id('invoice_master', 'inv_entry_no');

	        $products = $this->db->query("SELECT pro_id, pro_name FROM product_master WHERE pro_status = 1 ORDER BY pro_name ASC ")->result_array();
	        $record['product'][0] = "Please Select";
	        foreach ($products as $key => $value) {
	        	$record['product'][$value['pro_id']] = $value['pro_name'];
	        }

	    	$record['inv_data'] = $this->db->query("SELECT inv.inv_id, inv.inv_entry_no, inv.inv_dispatch_by, inv.inv_invoice_date, inv.inv_entry_date, inv.inv_customer_id, inv.inv_addon, inv.inv_addon_for_self, inv.inv_acc_type, inv_total_cgst, inv_total_sgst, inv_total_igst, inv_total_qty, inv_total_rate, inv_sub_total_amt, inv_total_amt, inv.inv_terms, am.acc_id, am.acc_name, am.acc_mobile, am.acc_email, sm.state_name, cm.city_name FROM invoice_master inv 
	    		LEFT JOIN account_master am ON(am.acc_id = inv.inv_customer_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id)
	    		WHERE inv_id = $id")->result_array();

	    	$record['inv_trans'] = $this->db->query("SELECT invt.invt_id, invt_pro_id, invt_rate, invt_qty, invt_amt_cgst, invt_amt_sgst, invt_amt_igst, invt_per_cgst, invt_per_sgst, invt_per_igst, invt_sub_total_amt, invt_total_amt, pm.pro_name, pm.pro_hsn_code
	    		FROM invoice_trans invt 
	    		LEFT JOIN product_master pm ON(pm.pro_id = invt.invt_pro_id)
	    		WHERE invt_inv_id = $id")->result_array();

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

	    	return $record;
	    }

	    public function get_product_data($pro_id,$cust_id)
	    {
	    	$record['user'] = $this->db->query("SELECT acc_id, acc_name, acc_type FROM account_master WHERE acc_id = $cust_id")->result_array();

	    	$record['products'] = $this->db->query("SELECT pro_id, pro_name, pro_hsn_code, pro_cgst, pro_sgst, pro_igst, pro_rate FROM product_master WHERE pro_id = $pro_id")->result_array();

	    	return $record;
	    }

	    public function get_single_invoice_data_for_print($id)
	    {
	    	$record['inv_data'] = $this->db->query("SELECT inv_entry_no, inv_dispatch_by, inv_terms,inv_addon, inv_invoice_date, inv_fin_year, am.acc_name, am.acc_mobile, am.acc_email,am.acc_address, am.acc_pan, am.acc_gst, sm.state_name, sm.state_code, cm.city_name, inv_total_qty, inv_total_rate, inv_total_sgst, inv_total_cgst, inv_total_igst, inv_total_amt, inv_sub_total_amt, am.acc_billing_address 
	    	    FROM invoice_master inv 
	    		LEFT JOIN account_master am ON(am.acc_id = inv.inv_customer_id)
	    		LEFT JOIN state_master sm ON(sm.state_id = am.acc_state_id)
	    		LEFT JOIN city_master cm ON(cm.city_id = am.acc_city_id)
	    		WHERE inv_id = $id")->result_array();

	    	$record['inv_trans'] = $this->db->query("SELECT invt.invt_id, invt_qty, invt_rate, invt_sub_total_amt, invt_total_amt, invt_amt_cgst, invt_amt_sgst, invt_amt_igst, pm.pro_name, pm.pro_hsn_code, invt_per_cgst, invt_per_sgst, invt_per_igst
	    		FROM invoice_trans invt 
	    		LEFT JOIN product_master pm ON(pm.pro_id = invt.invt_pro_id)
	    		WHERE invt_inv_id = $id")->result_array();

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

	    	return $record;
	    }
	}
?>