<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Sales_invoice extends CI_Controller
{
  public $page_data = array();

  function __construct()
  {
    parent::__construct();
    $this->initial();
  }

  public function initial()
  {
    ini_set('max_execution_time', 5000);
    ini_set("memory_limit", "-1");

    $this->load->model('User_model', 'User');
    $this->load->model('Work_model', 'Work');
    $this->load->model('Client_model', 'Client');
    $this->load->model('Supplier_model', 'Supplier');
    $this->load->model('Stock_model', 'Stock');
    $this->load->model('Material_model', 'Material');
    $this->load->model('Purchase_bill_model', 'Purchases');
    $this->load->model('Subcontracts_model', 'Subcontracts');
    $this->load->model('Purchase_order_model', 'Purchase_order');
    $this->load->library('Stock_management','stock_management');
    $this->load->model('Sales_invoice_model','Sales_invoice');
    $this->load->model('Sales_estimation_model','Sales_estimation');
    $this->load->model('Customers_model','Customer');
    $this->load->model('Account_model','Account');
    $this->load->model('Branches_model', 'Branches');
    $this->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $this->load->model('Bills_model', 'Bills');
    $this->load->model('Settings_model', 'Settings');
    $this->load->library('Accounts_management', 'accounts_management');
    $this->load->library('Bills_management','bills_management');

    //        Check User Logged in or Not
    $allowed_user_roles = array("madmin","admin","supervisor","general_manager","sales_manager","auditor","sales_staff","accounts_manager");
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }
    //        File Directory
    $this->page_data['directory'] = "sales_invoice";
    $this->page_data['tax_state_codes'] = $this->Settings->select_tax_state_codes("*")->result();
    $this->page_data['branches'] = $this->Branches->select_branches("*")->result();
  }
  public function index()
  {
    $this->view();
  }

  public function view()
  {
    $this->page_data['list_type'] = '';
    $this->page_data['material_list'] = $this->Material->select_material()->result();
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['customers'] = $this->Customer->select_customer()->result();
    $data_stock['sl.location_type'] = 'ware_house';
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations($columns="*",$data_stock)->result();
    $this->page_data['page_name'] = 'sales_invoice_view';
    $this->load->view('Index', $this->page_data);
  }
  public function view_items()
  {
    $this->page_data['list_type'] = '';
    $this->page_data['material_list'] = $this->Material->select_material()->result();
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['customers'] = $this->Customer->select_customer()->result();
    $data_stock['sl.location_type'] = 'ware_house';
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations($columns="*",$data_stock)->result();
    $this->page_data['page_name'] = 'sales_invoice_items_view';
    $this->load->view('Index', $this->page_data);
  }
  public function invoice($id="")
  {
    $this->page_data['branch_data'] = array();
    if($id!=''){
      $this->page_data['invoice_data'] = $this->Sales_invoice->select_invoices("si.*,ba.*,fy.fin_year,u.user_alias,c.customer_id,c.customer_name,c.customer_location,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,tsc.state_name,tsc.state_code,si.branch_id as invoice_branch_id,b.branch_name,b.branch_address,b.branch_taxno", array("si.invoice_id" =>$id ));
      if($this->page_data['invoice_data']->num_rows()==1){
        $this->page_data['invoice_data'] = $this->page_data['invoice_data']->row();
        if($this->page_data['invoice_data']->invoice_branch_id!=0){
          $this->page_data['branch_data'] = $this->Branches->select_branches("*", array("branch_id"=>$this->page_data['invoice_data']->invoice_branch_id))->row();
        }
        $this->page_data['invoice_data']->receivable_amount_in_words = $this->accounts_management->amount_in_words($this->page_data['invoice_data']->receivable_amount);
        $this->page_data['page_name'] = 'sales_invoice_profile';

        $this->page_data['invoice_items'] = $this->Sales_invoice->select_invoice_sale_item("material_name,unit_name,COALESCE(SUM(sii.quantity),0) as quantity,sii.unit_sale_price,COALESCE(SUM(sii.discount_amount),0) as discount_amount,COALESCE(SUM(sii.taxable_amount),0) as taxable_amount,COALESCE(SUM(sii.total_amount),0) as total_amount,tt.*,sii.description,sii.item_code",array("si.invoice_id"=>$id),"sii.material_id,sii.unit_id,sii.unit_sale_price")->result();
        $this->page_data['sales_receipts'] = $this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,u1.user_alias as created_user", array("ats.reference_id" => $id, "ats.reference_type" => 'sales_invoice'),'sales_invoice')->result();

        $this->load->view('Index',$this->page_data);
      }else{
        show_404();
      }
    }else{
      show_404();
    }

  }
  public function add()
  {

    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc.assigned_users)");
    }

    $data_stock['sl.location_type'] = 'ware_house';
    $data_stock['sl.location_branch_id'] = $this->session->userdata('branch_id');
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations($columns="*",$data_stock)->result();

    if($this->session->userdata('user_role')=='supervisor') {

    }
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['materials'] = $this->Material->select_material()->result();
    $this->page_data['bank_accounts'] = $this->Settings->select_bank_account_details()->result();

    $this->page_data['estimation_data'] = $this->page_data['estimation_id'] = '';
    if(isset($_GET['estimation_id']) && $_GET['estimation_id']!=''){
      $this->page_data['estimation_data'] = $this->Sales_estimation->select_sale_estimation("se.*,ba.*,u.user_alias,c.customer_id,c.customer_name,c.customer_location,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,tsc.state_name,tsc.state_code,se.branch_id as estimation_branch_id,b.branch_name,b.branch_address", array("se.estimation_id" =>$_GET['estimation_id']));
      if($this->page_data['estimation_data']->num_rows()==1){
        $this->page_data['estimation_data'] = $this->page_data['estimation_data']->row();
        $this->page_data['estimation_id'] = $this->page_data['estimation_data']->estimation_id;
        $this->page_data['estimation_items'] = $this->Sales_estimation->select_estimation_sale_item("material_name,unit_name,COALESCE(SUM(sei.quantity),0) as quantity,COALESCE(SUM(sei.unit_sale_price),0) as unit_sale_price,COALESCE(SUM(sei.discount_amount),0) as discount_amount,COALESCE(SUM(sei.taxable_amount),0) as taxable_amount,COALESCE(SUM(sei.total_amount),0) as total_amount,tt.*,sei.description,sei.item_code",array("se.estimation_id"=>$_GET['estimation_id']),"sei.material_id,sei.unit_id,sei.unit_sale_price")->result();

      }
    }

    $this->page_data['page_name'] = 'sales_invoice_add';
    $this->load->view('Index', $this->page_data);
  }

  public function create_sale()
  {
    $data['branch_id'] = $this->session->userdata('branch_id');
    $data['location_id'] = $this->security->xss_clean($this->input->post('location_id'));

    $item_qty_error = 0;
    // Check Items & qty with Stock Register
    $row_count = $this->security->xss_clean($this->input->post('current_row_count'));

    // Cart Items
    $item_data=$items_array=$newArrayItems=array();
    if($this->input->post('item')){
      $items_array = $this->input->post('item');
    }
    $items_array_length = sizeof($items_array);
    //      Convert to New Formatted Array
    foreach (array_keys($items_array) as $fieldKey) {
      foreach ($items_array[$fieldKey] as $key=>$value) {
        $newArrayItems[$key][$fieldKey] = $value;
      }
    }
    $item_key = 0;
    foreach ($newArrayItems as $row) {

      $stock_ids = $row['stock_id'];

      $this->db->where('sr.stock_id IN (' . $stock_ids . ')');
      $stock_details = $this->Stock->select_stock_register("sr.purchase_supplier_id,sr.unit_price,sr.material_id,sr.unit", "")->row();

      $data2['purchase_supplier_id'] = $stock_details->purchase_supplier_id;
      $data2['unit_price'] = $stock_details->unit_price;
      $data2['material_id'] = $stock_details->material_id;
      $data2['unit_id']  = $stock_details->unit;
      $total_stock_sold_qty = $row['quantity'];
      // $total_stock_sold_amount = $row['total_amount'];
      // Get Stock ID From Stock Register for this Material
      $this->db->where('sr.stock_id IN (' . $stock_ids . ')');
      $stock_register = $this->Stock->select_stock_register("sr.*", array("sr.location_id" => $data['location_id'], "sr.material_id" => $data2['material_id'], "sr.unit" => $data2['unit_id'], "sr.purchase_supplier_id" => $data2['purchase_supplier_id'], "sr.unit_price like" => $data2['unit_price'], "sr.total_balance_qty >" => 0));
      $remaining_qty = $total_stock_sold_qty;
      $item_stock_have_qty = $item_stock_have_sold_amount = 0;
      $total_loading_charge = $total_unloading_charge = 0;

      foreach ($stock_register->result() as $key => $value) {
        if($remaining_qty>0){
          $item_data[$item_key]['stock_id'] = $value->stock_id;
          $item_data[$item_key]['material_id'] = $stock_details->material_id;
          $item_data[$item_key]['unit_id'] = $stock_details->unit;
          $item_data[$item_key]['unit_stock_price'] = $stock_details->unit_price;
          $item_data[$item_key]['unit_sale_price'] = $row['unit_price'];
          $item_data[$item_key]['description'] = $row['description'];
          $item_data[$item_key]['item_code'] = $row['item_code'];
          if ($this->config->item('app_module_production') == 1 ){
            $item_data[$item_key]['loading_charge'] = $row['loading_charge'];
            $item_data[$item_key]['unloading_charge'] = $row['unloading_charge'];
            $total_loading_charge += $row['loading_charge'];
            $total_unloading_charge += $row['unloading_charge'];
          }
          // Splitting Quantity
          $available_stock_item_qty = $value->total_balance_qty;
          if($remaining_qty>$available_stock_item_qty){
            $item_data[$item_key]['quantity'] = $available_stock_item_qty;
          }else{
            $item_data[$item_key]['quantity'] = $remaining_qty;
          }
          $remaining_qty = $remaining_qty-$item_data[$item_key]['quantity'];

          if($item_data[$item_key]['quantity']!=$total_stock_sold_qty){
            $item_data[$item_key]['discount_amount'] = ($row['disc_amount']/$item_data[$item_key]['quantity']);
            $item_data[$item_key]['taxable_amount'] = ($item_data[$item_key]['unit_sale_price']*$item_data[$item_key]['quantity'])-($item_data[$item_key]['discount_amount']);
            $item_data[$item_key]['tax_percent'] = $row['tax_percent'];
            $item_data[$item_key]['tax_amount'] = ($item_data[$item_key]['taxable_amount']*$row['tax_percentage']/100);
            $item_data[$item_key]['total_amount'] = $item_data[$item_key]['taxable_amount']+$item_data[$item_key]['tax_amount'];
          }else{
            $item_data[$item_key]['discount_amount'] = $row['disc_amount'];
            $item_data[$item_key]['taxable_amount'] = $row['taxable_amount'];
            $item_data[$item_key]['tax_percent'] = $row['tax_percent'];
            $item_data[$item_key]['tax_amount'] = $row['tax_amount'];
            $item_data[$item_key]['total_amount'] = $row['total_amount'];
          }

          $item_stock_have_qty += $item_data[$item_key]['quantity'];
          // $item_stock_have_sold_amount += $item_data[$item_key]['total_amount'];
          $item_key ++;
        }
      }
      if($item_stock_have_qty!=$total_stock_sold_qty){
        $item_qty_error = 1;
      }
    }

    if($item_qty_error==0){

      $data['buyer_customer_id'] = $this->security->xss_clean($this->input->post('customer_id'));
      $data['buyer_branch_id'] = $this->security->xss_clean($this->input->post('branch_id'));
      $data['invoice_type'] = $this->security->xss_clean($this->input->post('invoice_type'));
      $data['invoice_to'] = $this->security->xss_clean($this->input->post('invoice_to'));
      $data['invoice_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('invoice_date'))));
      $data['delivery_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('delivery_date'))));
      $data['vehicle'] = $this->security->xss_clean($this->input->post('vehicle'));
      $data['vehicle_id'] = $this->security->xss_clean($this->input->post('vehicle_id'));
      $data['delivery_note_no'] = $this->security->xss_clean($this->input->post('delivery_note_no'));
      $data['despatched_through'] = $this->security->xss_clean($this->input->post('despatched_through'));
      $data['destination'] = $this->security->xss_clean($this->input->post('destination'));
      $data['buyers_order_no'] = $this->security->xss_clean($this->input->post('buyers_order_no'));
      if($this->input->post('buyers_order_date')){
        $data['buyers_order_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('buyers_order_date'))));
      }
      $data['buyer_name'] = $this->security->xss_clean($this->input->post('buyer_name'));
      $data['buyer_address'] = $this->security->xss_clean($this->input->post('buyer_address'));
      $data['buyer_phone'] = $this->security->xss_clean($this->input->post('buyer_phone'));
      $data['buyer_taxno'] = $this->security->xss_clean($this->input->post('buyer_taxno'));
      $data['buyer_state_id'] = $this->security->xss_clean($this->input->post('buyer_state_id'));
      if($this->input->post('bank_account')){
        $data['bill_bank_account'] = $this->security->xss_clean($this->input->post('bank_account'));
      }

      $invoice_month = date('m',strtotime($data['invoice_date']));
      $invoice_year = date('Y',strtotime($data['invoice_date']));
      if($invoice_month>=4){
        $fin_year_name = $invoice_year.'-'.($invoice_year+1);
        $fin_year_short = date('y',strtotime($invoice_year)).'-'.(date('y',strtotime($invoice_year))+1);
      }else{
        $fin_year_name = ($invoice_year-1).'-'.($invoice_year);
        $fin_year_short = (date('y',strtotime($invoice_year))-1).'-'.date('y',strtotime($invoice_year));
      }
      $fin_year_data = $this->Settings->select_finyear('',array("fin_year"=>$fin_year_name));
      if($fin_year_data->num_rows()==1){
        $data['finyear'] = $fin_year_data->row()->finyear_id;
        $fin_year = $fin_year_data->row()->fin_year;
      }elseif($fin_year_data->num_rows()==0){
        $fin_year = $insert_data['fin_year'] = $fin_year_name;
        $insert_data['fin_year_short'] = $fin_year_short;
        $insert_data['active_status'] = 0;
        $fin_year_insert_result = $this->Settings->insert_finyear($insert_data);
        $data['finyear'] = $fin_year_insert_result['insert_id'];
      }elseif($fin_year_data->num_rows()>1){
        $data['finyear'] = $fin_year_data[0]->row()->finyear_id;
        $fin_year = $fin_year_data[0]->row()->fin_year;
      }

      // Generate Invoice No
      $invoice_where['si.branch_id'] = $data['branch_id'];
      $invoice_where['si.invoice_type'] = $data['invoice_type'];
      $invoice_where['si.finyear'] = $data['finyear'];
      $invoice_last_data = $this->Sales_invoice->select_invoices("max(si.invoice_no) as max_invoice_no",$invoice_where);
      if($invoice_last_data->num_rows()==1){
        $data['invoice_no'] = ($invoice_last_data->row()->max_invoice_no)+1;
      }else{
        $data['invoice_no'] = 1;
      }
      if($data['invoice_type']=='B2C'){
        $data['invoice_no_prefix'] = 'INV-';
      }


      $data['net_total'] = $this->security->xss_clean($this->input->post('bill_net_total_amount'));
      $data['tax_amount'] = $this->security->xss_clean($this->input->post('bill_tax_amount'));
      $data['round_amount'] = $this->security->xss_clean($this->input->post('bill_round_amount'));
      $data['receivable_amount'] = $this->security->xss_clean($this->input->post('bill_receivable_amount'));
      $data['total_received'] = $this->security->xss_clean($this->input->post('total_received'));
      $data['balance_amount'] = $this->security->xss_clean($this->input->post('balance_amount'));
      $data['bill_remarks'] = $this->security->xss_clean($this->input->post('remarks'));
      $data['created_date'] = date('Y-m-d H:i:s');
      $data['created_by'] = $this->session->userdata('user_id');

      $result = $this->Sales_invoice->create_sale_invoice($data);
      $flash_data['status'] = $result['status'];
      if($flash_data['status']==1){
        // Insert Items
        foreach ($item_data as $key => $value) {
          $item_insert_data = array();
          $item_insert_data = $value;
          $item_insert_data['created_date'] = date('Y-m-d H:i:s');
          $item_insert_data['created_by'] = $this->session->userdata('user_id');
          $item_insert_data['invoice_id'] =  $result['insert_id'];
          $result2 = $this->Sales_invoice->create_invoice_sale_items($item_insert_data);

          $this->stock_management->calculate_stock_balance($item_insert_data['stock_id']);
        }

        $received_amount = $this->security->xss_clean($this->input->post('received_amount'));
        if($received_amount > 0){
          $reference_id = $result['insert_id'];
          $reference_type = "sales_invoice";
          $transaction_data['tr_type'] = "sales_invoice_transaction";

          $transaction_data['reference_id'] = $reference_id;
          $transaction_data['reference_type'] = $reference_type;
          $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
          $transaction_data['tr_reference_no'] = $this->security->xss_clean($this->input->post('reference_no'));
          $transaction_data['tr_remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
          $transaction_data['created_date'] = date('Y-m-d H:i:s');
          $transaction_data['created_by'] = $this->session->userdata('user_id');
          $transaction_data['tr_credit_acc'] = $this->security->xss_clean($this->input->post('credit_acc'));
          $transaction_data['tr_debit_acc'] = 11;//Sales Invoice Receipts
          $transaction_data['tr_amount'] = $received_amount;
          $transaction_data['tr_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('received_date'))));
          $transaction_result =  $this->Account->create_account_transaction($transaction_data);
        }
        // Generate Purchase Bill
        $this->stock_management->sales_invoice_branch_purchase_bill_generate($result['insert_id']);

        if ($this->config->item('app_module_production') == 1 ){
          $vehicle_work_slip['vehicle_id'] =$this->security->xss_clean($this->input->post('vehicle_id'));
          $vehicle_work_slip['work_slip_type'] = 'inside_warehouse';
          $vehicle_work_slip['worked_date'] = $data['invoice_date'];
          $vehicle_work_slip['sales_invoice_id'] = $result['insert_id'];
          $vehicle_work_slip['vehicle_location_id'] = $data['location_id'];
          $vehicle_work_slip['duration_type'] = $this->security->xss_clean($this->input->post('vehicle_work_duration_type'));
          $vehicle_work_slip['worked_duration'] = $this->security->xss_clean($this->input->post('vehicle_worked_duration'));
          $vehicle_work_slip['basic_charge'] = $this->security->xss_clean($this->input->post('vehicle_basic_charge'));
          $vehicle_work_slip['driver_bata'] = $this->security->xss_clean($this->input->post('driver_bata'));
          $vehicle_work_slip['driver_bata_include'] = $this->security->xss_clean($this->input->post('driver_bata_include'));
          $vehicle_work_slip['total_payable_amount'] = $this->security->xss_clean($this->input->post('vehicle_total_charge'));
          $vehicle_work_slip['total_paid'] = 0;
          $vehicle_work_slip['balance'] = $vehicle_work_slip['total_payable_amount'];
          if($this->config->item('User_enable_data_entry_approval')==1 && $this->session->userdata('user_role_id')>2){
            $vehicle_work_slip['entry_approval_status'] = 0;
          }
          $vehicle_work_slip['created_date'] = date('Y-m-d H:i:s');
          $vehicle_work_slip['created_by'] = $this->session->userdata('user_id');
          $work_slips_result = $this->Vehicle_work_slips->create_vehicle_work_slip($vehicle_work_slip);

          $grand_total_loading_charge = $total_loading_charge + $total_unloading_charge;
          if($grand_total_loading_charge>0){
            $other_charges_data['invoice_id'] = $result['insert_id'];
            $other_charges_data['type'] = 'loading_charge';
            $other_charges_data['bill_date'] = $data['invoice_date'];
            $other_charges_data['bill_total_amount'] = $grand_total_loading_charge;
            $other_charges_data['cosignee'] = '';
            $other_charges_data['amount'] = $grand_total_loading_charge;
            $other_charges_data['paid_date'] = $data['invoice_date'];
            $other_charges_data['debit_acc'] = 1;
            $other_charges_data['payment_mode'] = 'CASH';
            $this->add_update_other_charges_bill($other_charges_data);
          }
        }

        $flash_data['invoice_id'] = $result['insert_id'];
        $flash_data['flashdata_msg'] = 'Sales Invoice Saved Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
      }else {
        $flash_data['status'] = 2;
        $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
        $flash_data['flashdata_type'] = 'error';
        $flash_data['alert_type'] = 'danger';
        $flash_data['flashdata_title'] = 'Error !!';
      }
    }else{
      $flash_data['status'] = 2;
      $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred With Items Stock Qty. Please Try Again!';
      $flash_data['flashdata_type'] = 'error';
      $flash_data['alert_type'] = 'danger';
      $flash_data['flashdata_title'] = 'Items Stock Qty Error !!';
    }

    echo json_encode($flash_data);
  }
  public function select_invoices($list_type=''){

    $json_data=array();
    $j=0;
    $sl_no=0;
    $total_amount=0;
    $total_amount1=0;
    $total_amount2=0;
    $data=array();
    if(isset($_POST['date']) && $_POST['date']!=''){
      $date=array();
      $date=explode('-',$_POST['date']);
      $start_date=date('Y-m-d', strtotime($date[0]));
      $end_date=date('Y-m-d', strtotime($date[1]));
      $data["STR_TO_DATE(si.invoice_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(si.invoice_date,'%Y-%m-%d') >="]= $start_date;
    }
    if(isset($_POST['buyer']) && $_POST['buyer']!='' && $_POST['buyer']!='all'){
      $buyer = explode('_',$_POST['buyer']);
      if($buyer[0]=='branch'){
        $data['si.buyer_branch_id'] = $buyer[1];
      }else{
        $data['si.buyer_customer_id'] = $buyer[1];
      }
    }
    if(isset($_POST['branch']) && $_POST['branch']!='' && $_POST['branch']!='all'){
      $data['si.branch_id'] = $_POST['branch'];
    }
    if(isset($_POST['invoice_type']) && $_POST['invoice_type']!='' && $_POST['invoice_type']!='all'){
      $data['si.invoice_type'] = $_POST['invoice_type'];
    }
    if(isset($_POST['location_id']) && $_POST['location_id']!='' && $_POST['location_id']!='all'){
      $data['si.location_id'] = $_POST['location_id'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['si.created_by'] = $_POST['user_id'];
    }
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc.assigned_users)");
    }
    if(isset($_POST['payment_status']) && $_POST['payment_status']!='' && $_POST['payment_status']!='all'){
      $data['si.payment_status'] = $_POST['payment_status'];
    }
    if($this->config->item('user_data_access_view_all')!=1){
      $data['si.created_by'] = $this->session->userdata('user_id');
    }
    $result = $this->Sales_invoice->select_invoices("si.*,fy.fin_year,b.branch_name,u.user_alias,c.customer_id,c.customer_name,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,b2.branch_name as buyer_branch_name",$data);
    $result_array=$result->result();

    $json_data['draw']=5;
    $json_data['recordsTotal']=$result->num_rows();
    $json_data['recordsFiltered']=$result->num_rows();
    $array=array();

    foreach($result_array as $row):
      $bill_id_column=12;
      $id=$row->work_id;
      $btn_edit = $btn_delete = $btn_add_payment='';
      $btn_view='<a style="margin-left: 5px;margin-right: 5px"  href="'.base_url().'sales_invoice/invoice/'.$row->invoice_id.'" class="btn btn-primary m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
      <i class="la la-eye"></i>
      </a>';
      $location_name = $row->location_name;

      $location_name = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '</a>';
      if(($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_id')==$row->created_by) && ($this->session->userdata('user_role')!='auditor') ) {
        // $btn_edit = '<a style="margin-left: 5px;margin-right: 5px"  href="' . base_url() . 'sales_invoice/edit/' . $row->invoice_id . '" class="btn btn-info m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
        // <i class="la la-edit"></i>
        // </a>';
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="sales_invoice_delete_btn" href="#sales_invoice_delete_modal" data-toggle="modal" class="btn btn-danger m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="la la-trash"></i>
        </a>';
      }
      if($row->balance_amount<=0){
        $payment_status="<span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Received</span>";
      }else{
        if($row->total_received>0){
          $payment_status="<span class='m-badge m-badge--info m-badge--wide'><i class='fa fa-times-circle'></i> Partially Received</span>";
        }else{
          $payment_status="<span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-times-circle'></i> Not Received</span>";
        }
        // if($this->session->userdata('user_role')=='admin' || ($this->session->userdata('user_role')=='supervisor' && $this->session->userdata('user_id')==$row->created_by)) {

        // if($this->session->userdata('user_role')!='auditor'){
        //   $btn_add_payment = '<a data-bill-type="sales_invoice" data-bill-id-column="'.$bill_id_column.'" style="margin-left: 5px;margin-right: 5px" id="btn_receive_payment_add" data-toggle="modal"  href="#receive_payment_add_modal" class="btn btn-warning m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
        //   <i class="la la-plus-circle"></i>
        //   </a>';
        // }
        // }
      }

      $invoice_suffix = '';
      if($row->fin_year!=''){
        $fin_year_array = explode("-",$row->fin_year);
        $fin_year_1 = $fin_year_array[0];
        $fin_year_1 = substr($fin_year_1, -2);
        $fin_year_2 = $fin_year_array[1];
        $fin_year_2 = substr($fin_year_2, -2);
        $invoice_suffix = "/".$fin_year_1."-".$fin_year_2;
      }

      $sl_no++;
      $array[$j][]=$sl_no;
      $array[$j][]='<a href="' . base_url() . 'sales_invoice/invoice/' . $row->invoice_id . '">#' . $row->invoice_no_prefix.sprintf('%03u', $row->invoice_no).$invoice_suffix.'</a>';
      $array[$j][]=$row->branch_name;
      $array[$j][]=date('d-m-Y',strtotime($row->invoice_date));
      $array[$j][]=$row->invoice_type;
      $array[$j][] = $location_name;
      $array[$j][]=$row->buyer_name;
      $array[$j][]=$row->receivable_amount;
      $array[$j][]=$row->total_received;
      $array[$j][]=$row->balance_amount;
      $array[$j][]=$payment_status;
      $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Delivery Date : '.date('d-m-Y',strtotime($row->delivery_date)).'</li><li>Remarks : '.$row->bill_remarks."</li>";
      if ($this->config->item('UI_hide_created_date_field')==0){
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created on : '.date('d-m-Y',strtotime($row->created_date))."</li><li>Created By : ".$row->user_alias."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->user_alias.'</li>';
      }
      $array[$j][]=$btn_add_payment.$btn_view.$btn_edit.$btn_delete;
      $array[$j][]=$row->invoice_id;

      $total_amount=$total_amount+$row->receivable_amount;
      $total_amount1=$total_amount1+$row->total_received;
      $total_amount2=$total_amount2+$row->balance_amount;
      $j++;
    endforeach;

    if($result->num_rows()>0){
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][]="<strong>Total</strong>";
      $array[$j][]="<strong>".$total_amount."</strong>";
      $array[$j][]="<strong>".$total_amount1."</strong>";
      $array[$j][]="<strong>".$total_amount2."</strong>";
      $array[$j][]="";
      $array[$j][]="";
      $array[$j][]='';
      $array[$j][]="";
      $array[$j][]='';

    }

    $json_data['data']=$array;
    echo json_encode($json_data);  // send data as json format
  }
  public function select_invoice_items($list_type=''){

    $json_data=array();
    $j=0;
    $sl_no=0;
    $total_amount=0;
    $total_amount1=0;
    $total_amount2=0;
    $data=array();
    if(isset($_POST['date']) && $_POST['date']!=''){
      $date=array();
      $date=explode('-',$_POST['date']);
      $start_date=date('Y-m-d', strtotime($date[0]));
      $end_date=date('Y-m-d', strtotime($date[1]));
      $data["STR_TO_DATE(si.invoice_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(si.invoice_date,'%Y-%m-%d') >="]= $start_date;
    }
    if(isset($_POST['buyer']) && $_POST['buyer']!='' && $_POST['buyer']!='all'){
      $buyer = explode('_',$_POST['buyer']);
      if($buyer[0]=='branch'){
        $data['si.buyer_branch_id'] = $buyer[1];
      }else{
        $data['si.buyer_customer_id'] = $buyer[1];
      }
    }
    if(isset($_POST['branch']) && $_POST['branch']!='' && $_POST['branch']!='all'){
      $data['si.branch_id'] = $_POST['branch'];
    }
    if(isset($_POST['invoice_type']) && $_POST['invoice_type']!='' && $_POST['invoice_type']!='all'){
      $data['si.invoice_type'] = $_POST['invoice_type'];
    }
    if(isset($_POST['location_id']) && $_POST['location_id']!='' && $_POST['location_id']!='all'){
      $data['si.location_id'] = $_POST['location_id'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['si.created_by'] = $_POST['user_id'];
    }
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc.assigned_users)");
    }
    if(isset($_POST['payment_status']) && $_POST['payment_status']!='' && $_POST['payment_status']!='all'){
      $data['si.payment_status'] = $_POST['payment_status'];
    }
    if($this->config->item('user_data_access_view_all')!=1){
      $data['si.created_by'] = $this->session->userdata('user_id');
    }
    $result = $this->Sales_invoice->select_invoice_sale_item("sii.*,si.*,tt.tax_percentage,material_name,unit_name,fy.fin_year,b.branch_name,u.user_alias,c.customer_id,c.customer_name,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id",$data);
    $result_array=$result->result();

    $json_data['draw']=5;
    $json_data['recordsTotal']=$result->num_rows();
    $json_data['recordsFiltered']=$result->num_rows();
    $array=array();

    foreach($result_array as $row):

      $btn_view='<a style="margin-left: 5px;margin-right: 5px"  href="'.base_url().'sales_invoice/invoice/'.$row->invoice_id.'" class="btn btn-primary m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
      <i class="la la-eye"></i>
      </a>';
      $location_name = $row->location_name;
      $location_name = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '</a>';

      $invoice_suffix = '';
      if($row->fin_year!=''){
        $fin_year_array = explode("-",$row->fin_year);
        $fin_year_2 = $fin_year_array[0];
        $fin_year_2 = substr( $fin_year_2, -2);
        $invoice_suffix = "/".$fin_year_2;
      }

      $sl_no++;
      $array[$j][]=$sl_no;
      $array[$j][]='<a href="' . base_url() . 'sales_invoice/invoice/' . $row->invoice_id . '">#' . $row->invoice_no_prefix.sprintf('%03u', $row->invoice_no).$invoice_suffix.'</a>';
      $array[$j][]=$row->branch_name;
      $array[$j][]=date('d-m-Y',strtotime($row->invoice_date));
      $array[$j][]=$row->material_name;
      $array[$j][]=$row->item_code;
      $array[$j][]=$row->unit_name;
      $array[$j][]=$row->quantity;
      $array[$j][]=$row->unit_sale_price;
      $array[$j][]=$row->discount_amount;
      $array[$j][]=$row->taxable_amount;
      $array[$j][]=$row->tax_percentage;
      $array[$j][]=$row->tax_amount;
      $array[$j][]=$row->total_amount;
      $array[$j][]=$row->description;
      $array[$j][]=$row->invoice_type;
      $array[$j][]=$location_name;
      $array[$j][]=$row->buyer_name;
      $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Delivery Date : '.date('d-m-Y',strtotime($row->delivery_date)).'</li><li>Remarks : '.$row->bill_remarks."</li>";
      if ($this->config->item('UI_hide_created_date_field')==0){
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created on : '.date('d-m-Y',strtotime($row->created_date))."</li><li>Created By : ".$row->user_alias."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->user_alias.'</li>';
      }
      $array[$j][]='';
      $j++;
    endforeach;


    $json_data['data']=$array;
    echo json_encode($json_data);  // send data as json format
  }
  public function get_invoice_details()
  {
    $data = array();
    $json_data['count'] = 0;
    $data['si.invoice_id'] = $_POST['invoice_id'];
    $bill_data = $this->Sales_invoice->select_invoices("si.*,b.branch_name,u.user_alias,c.customer_id,c.customer_name,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id",$data);
    if($bill_data->num_rows()==1){
      $bill_data = $bill_data->row();
      $json_data['count'] = 1;
      $json_data['bill_id'] = "#".$bill_data->invoice_id;
      $json_data['bill_no'] = '#' . $bill_data->invoice_no_prefix.$bill_data->invoice_no;
      $json_data['bill_date'] = date('d-m-Y',strtotime($bill_data->invoice_date));
      $json_data['bill_total_amount'] = number_format($bill_data->receivable_amount,$this->config->item('Currency_amount_decimals_2'),'.','');
      $json_data['bill_total_paid'] = number_format($bill_data->total_received,$this->config->item('Currency_amount_decimals_2'),'.','');
      $json_data['bill_balance_amount'] = number_format($bill_data->balance_amount,$this->config->item('Currency_amount_decimals_2'),'.','');
      $json_data['customer'] = $bill_data->customer_name;
    }

    echo json_encode($json_data);
  }
  public function delete()
  {
    $data['invoice_id'] = $this->security->xss_clean($this->input->post('invoice_id'));
    $invoice_data = $this->Sales_invoice->select_invoices("si.*,ba.*,u.user_alias,c.customer_id,c.customer_name,c.customer_location,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,tsc.state_name,tsc.state_code,si.branch_id as invoice_branch_id,b.branch_name,b.branch_address", array("si.invoice_id"=>$data['invoice_id']));
    if($invoice_data->num_rows()==1){
      $invoice_data = $invoice_data->row();
      $invoice_items = $this->Sales_invoice->select_invoice_sale_item("sii.stock_id",array("si.invoice_id"=>$data['invoice_id']),"sii.material_id,sii.unit_id,sii.unit_sale_price")->result();
      // Delete Invoice
      $data['delete_status'] = 1;
      $result = $this->Sales_invoice->update_sale_invoice($data);
      $flash_data['status'] = $result;
      if($flash_data['status']==1){
        foreach ($invoice_items as $key => $value) {
          $this->stock_management->calculate_stock_balance($value->stock_id);
        }
        $flash_data['flashdata_msg'] = 'Sales Invoice Deleted Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
      }else{
        $flash_data['status'] = 2;
        $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
        $flash_data['flashdata_type'] = 'error';
        $flash_data['alert_type'] = 'danger';
        $flash_data['flashdata_title'] = 'Error !!';
      }

    }else {
      $flash_data['status'] = 2;
      $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
      $flash_data['flashdata_type'] = 'error';
      $flash_data['alert_type'] = 'danger';
      $flash_data['flashdata_title'] = 'Error !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];
    echo json_encode($flash_data);
  }
  public function add_update_other_charges_bill($data){
    $invoice_data = $this->Sales_invoice->select_invoices("si.*",array("invoice_id"=>$data['invoice_id']),'all');
    if($invoice_data->num_rows()==1){
      $invoice_data = $invoice_data->row();
      $bill_data['bill_type'] = 'expense';
      $bill_data['bill_credit_ac'] = 19; //Expense Account Head - Loading
      $bill_data['bill_consignee_name'] = $data['cosignee'];
      $bill_data['bill_description'] = 'Loading/Unloading Charge For Sales Invoice #'.$data['invoice_id'];
      $bill_data['sales_invoice_id'] = $data['invoice_id'];
      $bill_data['bill_date'] = $data['bill_date'];
      $bill_data['bill_no'] = '';
      $bill_data['bill_gross_amount'] = $data['bill_total_amount'];
      $bill_data['bill_payable_amount'] = $bill_data['bill_amount'] = $bill_data['bill_gross_amount'];

      // Check Bill Already Created
      $bill_select_data = $this->Bills->select_bills("b.bill_id",array("b.sales_invoice_id"=>$bill_data['sales_invoice_id'],"b.bill_credit_ac"=>$bill_data['bill_credit_ac']),'expense_bill');
      if($bill_select_data->num_rows()==0){
        $bill_data['created_date'] = date('Y-m-d H:i:s');
        $bill_data['created_by'] = $this->session->userdata('user_id');
        if($this->config->item('User_enable_data_entry_approval')==1 && $this->session->userdata('user_role_id')>2){
          $bill_data['entry_approval_status'] = 0;
        }
        $bill_result =  $this->Bills->create_bills($bill_data);
        $bill_result_status = $bill_result['status'];
        $bill_id = $bill_result['insert_id'];
      }else{
        $bill_data['updated_date'] = date('Y-m-d H:i:s');
        $bill_data['updated_by'] = $this->session->userdata('user_id');
        $bill_data['bill_id'] = $bill_id = $bill_select_data->row()->bill_id;
        $bill_result =  $this->Bills->update_bills($bill_data);
        $bill_result_status = $bill_result;
      }

      if ($bill_result_status == 1) {
        $transaction_data['reference_id'] = $bill_id;
        $transaction_data['reference_type'] = $bill_data['bill_type']."_bill";
        $transaction_data['tr_type'] = $bill_data['bill_type']."_bill_transaction";
        if(isset($bill_data['bill_date'])){
          $transaction_data['tr_date'] = $bill_data['bill_date'];
        }
        if(isset($data['debit_acc'])){
          $transaction_data['tr_debit_acc'] = $data['debit_acc'];
        }
        $transaction_data['tr_credit_acc'] = $bill_data['bill_credit_ac'];
        $transaction_data['tr_description'] = ucwords($bill_data['bill_type'])." Bill Transaction";
        $transaction_data['tr_amount'] = $data['amount'];
        if(isset($data['payment_mode'])){
          $transaction_data['tr_mode'] = $data['payment_mode'];
        }
        $transaction_select_data = $this->Account->select_account_transaction("ats.transaction_id",array("b.bill_id" => $bill_id,"ats.tr_type" => 'expense_bill_transaction',"ats.reference_type" => 'expense_bill'),'expense_bill');
        if($transaction_select_data->num_rows()==0){
          $transaction_data['created_date'] = date('Y-m-d H:i:s');
          $transaction_data['created_by'] = $this->session->userdata('user_id');
          $transaction_result =  $this->Account->create_account_transaction($transaction_data);
        }else{
          $transaction_data['transaction_id'] = $transaction_select_data->row()->transaction_id;
          $transaction_data['updated_date'] = date('Y-m-d H:i:s');
          $transaction_data['updated_by'] = $this->session->userdata('user_id');
          $transaction_result =  $this->Account->update_account_transaction($transaction_data);
        }

      }
      // Calculate & Update Bill Total
      $this->bills_management->calculate_bill_total($bill_id);
    }

  }
}
?>
