<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Stock 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('Account_model', 'Account');
    $this->load->model('Vehicle_model', 'Vehicle');
    $this->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $this->load->library('Boq_management', 'boq_management');
    $this->load->library('Projects_management', 'projects_management');

    //        Check User Logged in or Not
    $allowed_user_roles = array("madmin", "admin", "supervisor", "general_manager", "purchase_manager", "accounts_manager", "auditor","qs","qs_manager","procurement_engineer","project_manager","operation_manager");
    if ($this->User->is_logged_in($allowed_user_roles) == false) {
      redirect(base_url(), 'refresh');
    }
    //        File Directory
    $this->page_data['directory'] = "stock";
  }
  public function index()
  {
    $this->register();
  }

  public function register()
  {
    // Check Module is Enabled or Not
    if ($this->config->item('app_module_stock_management') == 0) {
      redirect(base_url() . 'dashboard', 'refresh');
    }

    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)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->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['page_name'] = 'stock_register';
    $this->load->view('Index', $this->page_data);
  }
  public function branch_report()
  {
    // Check Module is Enabled or Not
    if ($this->config->item('app_module_stock_management') == 0) {
      redirect(base_url() . 'dashboard', 'refresh');
    }

    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)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();

    if ($this->session->userdata('user_role') == 'supervisor') {
    }
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['material_categories'] = $this->Material->select_category("")->result();

    $this->page_data['materials'] = $this->Material->select_material()->result();

    $this->page_data['page_name'] = 'stock_report_branch';
    $this->load->view('Index', $this->page_data);
  }
  public function inward_register()
  {
    $this->page_data['materials'] = $this->Material->select_material()->result();

    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, w.assigned_users)");
    }
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();

    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)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();
    $this->page_data['supplier_categories'] = $this->Supplier->select_supplier_category()->result();

    $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['page_name'] = 'material_inward_register';
    $this->load->view('Index',$this->page_data);
  }
  public function transfer_history()
  {

    // Check Module is Enabled or Not
    if ($this->config->item('app_module_stock_management') == 0) {
      redirect(base_url() . 'dashboard', 'refresh');
    }

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


    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->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['page_name'] = 'stock_transfer_history';
    $this->load->view('Index', $this->page_data);
  }
  public function transfer_bill($bill_id)
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_stock_management')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    $this->page_data['bill_data']	= $this->Stock->select_stock_transfer_bill("v.vehicle_no,stb.*,sl1.work_id as transferred_from_work_id,sl2.work_id as transferred_to_work_id,sl1.location_name as transferred_from_loc_name,sl2.location_name as transferred_to_loc_name,u.user_alias,wc1.work_location as transferred_from_loc_address,wc2.work_location as transferred_to_loc_address",array("stb.bill_id"=>$bill_id));
    if($bill_id!='' && $this->page_data['bill_data']->num_rows()==1){
      $this->page_data['bill_data'] = $this->page_data['bill_data']->row();
      $this->page_data['bill_items'] = $this->Stock->select_stock_transfers("m.item_code,transfer_bill_id,COALESCE(SUM(st.transferred_qty),0) as total_transferred_qty,COALESCE(SUM(st.transferred_qty*sr2.unit_price),0) as total_transferred_amt,st.*,sr1.unit_price,sr1.material_id,m.material_name,mu.unit_name,sr1.unit,sl1.location_name as transferred_from_loc_name,sl2.location_name as transferred_to_loc_name,u.user_alias as created_user",array("st.transfer_bill_id"=>$bill_id),array("st.transfer_bill_id","sr1.material_id","sr1.unit","sr2.unit_price",))->result();
      $transaction_data['ats.reference_id'] = $bill_id;
      $transaction_data['ats.reference_type'] = 'stock_transfer_bill';
      $this->page_data['bill_payments']	= $this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user",$transaction_data,$transaction_data['ats.reference_type'])->result();


      $this->page_data['page_name'] = 'stock_transfer_bill';
      $this->load->view('Index', $this->page_data);
    }else{
      show_404();
    }

  }
  public function transfer_add()
  {
    // Check Module is Enabled or Not
    if ($this->config->item('app_module_stock_management') == 0) {
      redirect(base_url() . 'dashboard', 'refresh');
    }
    $data = array();
    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['wc.assigned_users'] = $supervisor_id;
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations("", $data)->result();
    $this->page_data['stock_locations_warehouse'] = $this->Stock->select_stock_locations("", array('sl.work_id' => '0'))->result();
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['materials'] = $this->Material->select_material()->result();

    $this->page_data['page_name'] = 'stock_transfer_add';
    $this->load->view('Index', $this->page_data);
  }
  public function consumption()
  {
    // Check Module is Enabled or Not
    if ($this->config->item('app_module_stock_management') == 0) {
      redirect(base_url() . 'dashboard', 'refresh');
    }

    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)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->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['material_categories'] = $this->Material->select_category("")->result();

    $this->page_data['page_name'] = 'stock_consumptions';
    $this->load->view('Index', $this->page_data);
  }
  public function opening_stock($page_type='view')
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_stock_management')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    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)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->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();
    if($page_type=='add'){
      $this->page_data['page_name'] = 'stock_opening_add';
    }else{
      $this->page_data['page_name'] = 'stock_opening_view';
    }
    $this->load->view('Index', $this->page_data);
  }
  public function locations($param1='',$param2='')
  {
    // Check Module is Enabled or Not
    if ($this->config->item('app_module_stock_management') == 0) {
      redirect(base_url() . 'dashboard', 'refresh');
    }
    $this->page_data['materials'] = $this->Material->select_material()->result();
    $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['supplier_categories'] = $this->Supplier->select_supplier_category()->result();

    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)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();

    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    if ($param1 == 'profile') {
      $this->page_data['location_id'] = $param2;
      $this->page_data['location_data'] = $this->Stock->select_stock_locations("sl.*,wc.work_name", array("location_id" => $param2));
      if ($this->page_data['location_data']->num_rows() == 1) {
        $this->page_data['page_name'] = 'stock_location_profile';
        $this->page_data['location_data'] = $this->page_data['location_data']->row();
      } else {
        show_404();
      }
    } else {
      $this->page_data['page_name'] = 'stock_locations';
    }
    $this->load->view('Index', $this->page_data);
  }
  public function select_stock_location($param = "")
  {

    $json_data = array();
    $j = 0;
    $sl_no = 0;
    $data = array();
    if($this->session->userdata('branch_id')!=0) {
      $data['sl.location_branch_id'] = $this->session->userdata('branch_id');
    }
    $result  = $this->Stock->select_stock_locations("sl.*,wc.work_name,wc.assigned_users AS supervisor", $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) :
      $id = $row->work_id;

      $btn_edit = '';
      $btn_delete = '';


      if ($row->location_type == 'work_site') {
        $location_type = "Work Site";
        $work_link = '<a href="' . base_url() . 'work/profile/' . $row->work_id . '">' . $row->work_name . '</a>';
      } else {
        $location_type = "Ware House";
        $work_link = '';
        if ($this->session->userdata('user_role') == 'admin') {

          // if editable is true show edit and delete button
          if ($row->editable_status == 1) {
            $btn_edit = '<a style="margin-left: 5px;margin-right: 5px" id="stock_location_edit_btn" href="#stock_location_edit_modal" data-toggle="modal" 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;display:none;" id="stock_location_delete_btn" href="#stock_location_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>';
          $btn_delete = '';
        }
      }


      if ($row->location_type == 'work_site') {
        if ($this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'general_manager') {
          if ($this->session->userdata('user_role') == 'supervisor' && $row->supervisor == $this->session->userdata('user_id')) {
            $flag = 1;
          } else {
            $flag = 0;
          }
        } else {
          $flag = 1;
        }
      } else {
        $flag = 1;
      }
      if ($flag == 1) {

        if($row->location_type=='work_site'){
          $location_name = '<a href="' . base_url() . 'work/profile/' . $row->work_id . '">' . $row->location_name . '<br/>[Work Site]</a>';
        }elseif($row->location_type=='ware_house'){
          $location_name = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '<br/>[Warehouse]</a>';
        }

        $sl_no++;
        $array[$j][] = $sl_no;
        $array[$j][] = $row->location_id;
        $array[$j][] = $location_name;
        $array[$j][] = $location_type;
        $array[$j][] = $work_link;
        $array[$j][] = $row->work_id;
        $array[$j][] = $row->location_type;
        $array[$j][] = $btn_edit . $btn_delete;
        $j++;
      }
    endforeach;

    $json_data['data'] = $array;
    echo json_encode($json_data);  // send data as json format
  }
  public function create_location()
  {
    $data['location_type'] = $this->security->xss_clean($this->input->post('location_type'));
    $data['location_name'] = $location_name = $this->security->xss_clean($this->input->post('location_name'));
    $where = "location_name='$location_name'";
    if ($data['location_type'] == 'work_site') {
      $data['work_id'] = $work_id = $this->security->xss_clean($this->input->post('work_id'));
      $where .= " or sl.work_id='$work_id'";
    } else {
      $data['work_id'] = 0;
    }
    // Check Already Exist
    $count  = $this->Stock->select_stock_locations("sl.*", $where);
    if ($count->num_rows() == 0) {
      $result =  $this->Stock->insert_stock_location($data);
      $flash_data['status'] = $result['status'];
      if ($result['status'] == 1) {
        $flash_data['flashdata_msg'] = 'Stock Location Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
      } else {
        $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;
      if ($count->row()->location_name == $data['location_name']) {
        $flash_data['flashdata_msg'] = 'Stock Location with Same Name Already Exist!.';
      } else {
        $flash_data['flashdata_msg'] = 'Stock Location Already Created for this Project.';
      }
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Duplication !';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];
    echo json_encode($flash_data);
  }
  public function update_location()
  {
    $data['location_id'] = $location_id = $this->security->xss_clean($this->input->post('location_id'));
    $data['location_type'] = $this->security->xss_clean($this->input->post('location_type_2'));
    $data['location_name'] = $location_name = $this->security->xss_clean($this->input->post('location_name'));
    $where = "location_id !='$location_id' and (location_name='$location_name'";
    if ($data['location_type'] == 'work_site') {
      $data['work_id'] = $work_id = $this->security->xss_clean($this->input->post('work_id'));
      $where .= " or sl.work_id='$work_id'";
    } else {
      $data['work_id'] = 0;
    }
    $where .= ")";
    // Check Already Exist
    $count  = $this->Stock->select_stock_locations("sl.*", $where);
    if ($count->num_rows() == 0) {
      $result =  $this->Stock->update_stock_location($data);
      $flash_data['status'] = $result;
      if ($result == 1) {
        $flash_data['flashdata_msg'] = 'Stock Location Details Updated Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
      } else {
        $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;
      if ($count->row()->location_name == $data['location_name']) {
        $flash_data['flashdata_msg'] = 'Stock Location with Same Name Already Exist!.';
      } else {
        $flash_data['flashdata_msg'] = 'Stock Location Already Created for this Project.';
      }
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Duplication !';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];
    echo json_encode($flash_data);
  }
  public function delete_location()
  {
    $data['location_id'] = $this->security->xss_clean($this->input->post('location_id'));
    $data['delete_status'] = 1;

    $result =  $this->Stock->update_stock_location($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $flash_data['flashdata_msg'] = 'Stock Location Deleted Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    } else {
      $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 !!';
    }
    echo json_encode($flash_data);
  }
  public function select_location_summary($return_type = '')
  {
    $json_data = $json_data2 = array();
    $j = $sl_no = 0;
    $data = array();
    $id = $_POST['location_id'];
    $json_data['total_purchase_bill_amount'] = round($this->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as total", array("p.location_id" => $id))->row()->total,$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_purchase_bill_payment'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("p.location_id" => $id, "ats.tr_type" => 'purchase_bill_transaction', "ats.reference_type" => 'purchase_bill'), 'purchase_bill')->row()->total_amount;
    $json_data['total_purchase_order_payment'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("po.location_id" => $id, "ats.tr_type" => 'purchase_order_transaction', "ats.reference_type" => 'purchase_order'), 'purchase_order')->row()->total_amount;
    $json_data['total_purchase_bill_pending_amount'] = round($json_data['total_purchase_bill_amount'] - $json_data['total_purchase_bill_payment'],$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_purchase_payment'] = round($json_data['total_purchase_order_payment'] + $json_data['total_purchase_bill_payment'],$this->config->item('Currency_amount_decimals_2'));

    $json_data['total_transferred_in_stock_amt'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_in_qty*sr.unit_price),0) as total", array("sr.location_id" => $id))->row()->total,$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_transferred_out_stock_amt'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_out_qty*sr.unit_price),0) as total", array("sr.location_id" => $id))->row()->total,$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_consumption_stock_amt'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_consumption_qty*sr.unit_price),0) as total", array("sr.location_id" => $id))->row()->total,$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_balance_stock_amt'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty*sr.unit_price),0) as total", array("sr.location_id" => $id))->row()->total,$this->config->item('Currency_amount_decimals_2'));

    if ($return_type == 'count') {
      echo json_encode($json_data);  // send data as json format
    } else {
    }
  }

  public function create_transfer()
  {
    $bill_data['transferred_to_loc_id'] = $this->security->xss_clean($this->input->post('location_to'));
    $bill_data['transferred_from_loc_id'] = $this->security->xss_clean($this->input->post('location_form'));
    $bill_data['vehicle_id'] = $this->security->xss_clean($this->input->post('vehicle_id'));
    $bill_data['bill_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('transfer_date'))));
    $bill_data['created_date'] = date('Y-m-d H:i:s');
    $bill_data['created_by'] = $this->session->userdata('user_id');
    $bill_result = $this->Stock->insert_stock_transfer_bill($bill_data);

    $data['transfer_bill_id'] = $bill_result['insert_id'];
    $stock_trans_data = $this->Stock->select_stock_transfers("MAX(trans_group_id) as last_group_id");
    if ($stock_trans_data->num_rows() == 1) {
      $data['trans_group_id'] = ($stock_trans_data->row()->last_group_id) + 1;
    } else {
      $data['trans_group_id'] = 1;
    }
    $data['transferred_to_loc_id'] = $this->security->xss_clean($this->input->post('location_to'));
    $data['transferred_from_loc_id'] = $this->security->xss_clean($this->input->post('location_form'));

    $location_data_to = $this->Stock->select_stock_locations("sl.work_id,sl.location_id,sl.location_type", array("sl.location_id" => $data['transferred_to_loc_id']));
    if ($location_data_to->num_rows() == 1) {
      $data['transfer_to_project_stage_id'] = $this->projects_management->get_project_stage_id($location_data_to->row()->work_id);
    }
    $location_data_from = $this->Stock->select_stock_locations("sl.work_id,sl.location_id,sl.location_type", array("sl.location_id" => $data['transferred_from_loc_id']));
    if ($location_data_from->num_rows() == 1) {
      $data['transfer_from_project_stage_id'] = $this->projects_management->get_project_stage_id($location_data_from->row()->work_id);
    }

    $data['transfer_date'] = date('Y-m-d', strtotime($this->security->xss_clean($this->input->post('transfer_date'))));
    $data['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');

    // Cart Items
    $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;
      }
    }
    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();
      if($this->config->item('Stock_stock_register_material_based_only')!=1){
        $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_transferred_qty = $row['quantity'];
      // Get Stock ID From Stock Register for this Material
      $this->db->where('sr.stock_id IN (' . $stock_ids . ')');
      if($this->config->item('Stock_stock_register_material_based_only')==1){
        $stock_register = $this->Stock->select_stock_register("sr.*", array("sr.location_id" => $data['transferred_from_loc_id'], "sr.material_id" => $data2['material_id'], "sr.unit" => $data2['unit_id'],  "sr.total_balance_qty >" => 0));
      }else{
        $stock_register = $this->Stock->select_stock_register("sr.*", array("sr.location_id" => $data['transferred_from_loc_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_transferred_qty;
      foreach ($stock_register->result() as $key => $value) {
        if ($remaining_qty > 0) {
          $available_stock_item_qty = $value->total_balance_qty;
          if ($remaining_qty > $available_stock_item_qty) {
            $data['transferred_qty']  = $available_stock_item_qty;
          } else {
            $data['transferred_qty']  = $remaining_qty;
          }

          $remaining_qty = $remaining_qty - $data['transferred_qty'];
          $data['transferred_from_stock_id'] = $value->stock_id;

          // Insert Transferred Stock as New Stock Entry
          $stock_data['location_id'] = $data['transferred_to_loc_id'];
          $stock_data['purchase_supplier_id'] = $value->purchase_supplier_id;
          $stock_data['stock_name'] = $value->stock_name;
          $stock_data['material_id'] = $value->material_id;
          $stock_data['unit'] = $value->unit;
          $stock_data['unit_price'] = $value->unit_price;
          $stock_data['total_transferred_in_qty'] = $data['transferred_qty'];
          $stock_data['created_date'] = date('Y-m-d H:i:s');
          $stock_data['created_by'] = $this->session->userdata('user_id');
          $result3 = $this->Stock->insert_stock_register($stock_data);

          $data['unit_price'] = $value->unit_price;
          $data['total_amount'] = round($value->unit_price*$data['transferred_qty'],$this->config->item('Currency_amount_decimals_2'));
          $data['transferred_to_stock_id'] = $result3['insert_id'];
          // Update Stock Consumption

          $result = $this->Stock->insert_stock_transfer($data);
          //  Update Stock Register
          $this->stock_management->calculate_stock_balance($data['transferred_from_stock_id']);
          //  Update Stock Register
          $this->stock_management->calculate_stock_balance($result3['insert_id']);

          $this->stock_management->calculate_stock_transfer_bill($bill_result['insert_id']);
        }
      }
    }
    if (isset($result) && $result['status'] == 1) {
      $flash_data['status'] = $result['status'];
      $flash_data['flashdata_msg'] = 'Stock Transfer Saved Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';

      if($this->config->item('app_module_vehicle_management')==1 && $this->input->post('vehicle_id') && $this->input->post('vehicle_id')!=''){
        $vehicle_work_slip['vehicle_id'] =$this->security->xss_clean($this->input->post('vehicle_id'));
        $vehicle_work_slip['worked_date'] = $bill_data['bill_date'];
        $vehicle_work_slip['stock_transfer_bill_id'] = $data['transfer_bill_id'];
        if($location_data_to->row()->location_type=='work_site'){
          $vehicle_work_slip['work_slip_type'] = 'inside_work';
          $vehicle_work_slip['vehicle_work_id'] = $location_data_to->row()->work_id;
          $vehicle_work_slip['project_stage_id'] = $this->projects_management->get_project_stage_id($location_data_to->row()->work_id);
        }else{
          $vehicle_work_slip['work_slip_type'] = 'inside_warehouse';
          $vehicle_work_slip['vehicle_location_id'] = $location_data_to->row()->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'));
        if($this->input->post('vehicle_driver')){
          $vehicle_work_slip['driver_id'] = $this->security->xss_clean($this->input->post('vehicle_driver'));
          $vehicle_work_slip['start_time'] = date('H:i:s',strtotime($this->input->post('vehicle_start_time')));
          $vehicle_work_slip['end_time'] = date('H:i:s',strtotime($this->input->post('vehicle_end_time')));
          $vehicle_work_slip['start_km'] = $this->security->xss_clean($this->input->post('vehicle_start_km'));
          $vehicle_work_slip['end_km'] = $this->security->xss_clean($this->input->post('vehicle_end_km'));
          $vehicle_work_slip['total_km'] = $this->security->xss_clean($this->input->post('vehicle_total_km'));
          $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);

      }

      if($this->config->item('BOQ_dpr_template')==2){
        $this->boq_management->auto_correct_material_consumption_balance($location_data_to->row()->work_id);
        $this->boq_management->auto_correct_material_consumption_balance($location_data_from->row()->work_id);
      }

    } else {
      $flash_data['status'] = 0;
      $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 !!';
    }
    echo json_encode($flash_data);
  }

  public function create_consumption($type='construction')
  {
    $stock_slnos = $this->input->post('stock_slnos');

    if($type=='vehicle'){
      $data['vehicle_id'] = $this->security->xss_clean($this->input->post('vehicle_id'));
    }else{
      $data['work_id'] = $this->security->xss_clean($this->input->post('work'));
    }
    $data['consumption_date'] = date('Y-m-d', strtotime($this->input->post('date')));
    $stock_cons_data = $this->Stock->select_stock_consumption("MAX(cons_group_id) as last_group_id");
    if ($stock_cons_data->num_rows() == 1) {
      $data['cons_group_id'] = ($stock_cons_data->row()->last_group_id) + 1;
    } else {
      $data['cons_group_id'] = 1;
    }

    if($type=='construction'){
      $data['project_stage_id'] = $this->projects_management->get_project_stage_id($data['work_id']);
    }

    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');
    $data['remarks'] = "";
    $count = $this->security->xss_clean($this->input->post('count'));
    foreach ($stock_slnos as $key22 => $i) {
      $stock_ids = explode(',', $this->security->xss_clean($this->input->post('stock_ids_' . $i)));
      $remaining_qty = $qty = $this->security->xss_clean($this->input->post('qty_' . $i));
      if ($qty > 0) {
        foreach ($stock_ids as $key => $value) {
          $stock_register = $this->Stock->select_stock_register("stock_id,total_balance_qty", array("sr.stock_id" => $value));
          if ($stock_register->num_rows() == 1 && $stock_register->row()->total_balance_qty > 0 && $remaining_qty > 0) {
            $available_stock_item_qty = $stock_register->row()->total_balance_qty;
            if ($remaining_qty > $available_stock_item_qty) {
              $data['consumption_qty']  = $available_stock_item_qty;
            } else {
              $data['consumption_qty']  = $remaining_qty;
            }

            $remaining_qty = $remaining_qty - $data['consumption_qty'];

            $data['stock_id'] = $value;
            // Update Stock Consumption
            $result = $this->Stock->insert_stock_consumption($data);
            //  Update Stock Register
            $result2 = $this->stock_management->calculate_stock_balance($data['stock_id']);
          }
        }
      }
    }
    if (isset($result) && $result['status'] == 1) {
      $flash_data['status'] = $result['status'];
      $flash_data['flashdata_msg'] = 'Stock Consumption Saved Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    } else {
      $flash_data['status'] = 0;
      $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 !!';
    }
    echo json_encode($flash_data);
  }
  public function select_stock_register($list_type = "")
  {
    $json_data = array();
    $j = $show_qty_total = 0;
    $sl_no = 0;
    $data = $array = array();
    if (isset($_POST['sale_price_category']) && $_POST['sale_price_category'] != '' && $_POST['sale_price_category'] != 'all') {
      $sale_price_category = $_POST['sale_price_category'];
    }
    if (isset($_POST['location_id']) && $_POST['location_id'] != '' && $_POST['location_id'] != 'all') {
      $data['sr.location_id'] = $_POST['location_id'];
    }
    if (isset($_POST['supplier']) && $_POST['supplier'] != '' && $_POST['supplier'] != 'all') {
      $data['sr.purchase_supplier_id'] = $_POST['supplier'];
    }
    if (isset($_POST['work_id']) && $_POST['work_id'] != '' && $_POST['work_id'] != 'all') {
      $data['sl.work_id'] = $_POST['work_id'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('sl.work_id',explode(',',$_POST['work_ids']));
    }
    if (isset($_POST['material_id']) && $_POST['material_id'] != '' && $_POST['material_id'] != 'all') {
      $data['sr.material_id'] = $_POST['material_id'];
      $show_qty_total = 1;
    }
    if (isset($_POST['user_id']) && $_POST['user_id'] != '' && $_POST['user_id'] != 'all') {
      $data['sr.created_by'] = $_POST['user_id'];
    }
    $available_stock_only = 0;
    if (isset($_POST['quantity']) && $_POST['quantity'] == 'available') {
      $available_stock_only = 1;
    }
    if($this->config->item('Stock_stock_register_material_based_only')==1){
      $group_by_columns = array('sr.material_id','sr.unit','sr.location_id');
    }else{
      $group_by_columns = array('sr.material_id','sr.unit','sr.purchase_supplier_id','sr.unit_price','sr.location_id');
    }
    if ($this->session->userdata('user_role') == 'supervisor') {
      $data['sl.location_type'] = 'work_site';
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc.assigned_users)");
      // Select All Available Materials in Stock
      $materials	= $this->Stock->select_stock_register_2("GROUP_CONCAT(sr.stock_id) AS stock_ids,sr.stock_description,sr.stock_id,sr.purchase_supplier_id,sr.unit_price,s.supplier_name,sr.unit,sr.material_id,m.material_specification,mu.unit_name,mu.unit_id,m.item_code,m.material_name,sr.location_id,sl.location_name,sl.location_type,sl.work_id,sr.purchase_item_id",$data,array(3));
    }else{
      // Select All Available Materials in Stock
      $materials	= $this->Stock->select_stock_register("GROUP_CONCAT(sr.stock_id) AS stock_ids,sr.stock_description,sr.stock_id,sr.purchase_supplier_id,sr.unit_price,s.supplier_name,sr.unit,sr.material_id,m.material_specification,mu.unit_name,mu.unit_id,m.item_code,m.material_name,sr.location_id,sl.location_name,sl.location_type,sl.work_id,sr.purchase_item_id",$data,$group_by_columns);
    }


    $grand_total_damaged_stock_qty = $grand_total_purchase_returned_stock_qty = $grand_total_sold_stock_qty = $grand_total_production_stock_qty = $grand_total_opening_stock_qty = $grand_total_transferred_out_stock_qty = $grand_total_transferred_in_stock_qty = $grand_total_consumption_stock_qty = $grand_total_purchased_stock_qty = $grand_total_available_stock_qty = $grand_total_available_stock_price = $grand_total_purchased_stock_price = $grand_total_transferred_in_stock_price = $grand_total_damaged_stock_price = $grand_total_transferred_out_stock_price = $grand_total_consumption_stock_price = $grand_total_purchase_returned_stock_price = $grand_total_production_stock_price = $grand_total_sold_stock_price = $grand_total_opening_stock_price = 0;

    foreach ($materials->result() as $row) :


      $data['sr.material_id'] = $row->material_id;
      $data['sr.location_id'] = $row->location_id;
      $data['sr.unit'] = $row->unit;
      if($this->config->item('Stock_stock_register_material_based_only')!=1){
        $data['sr.purchase_supplier_id'] = $row->purchase_supplier_id;
        $data['sr.unit_price like'] = $row->unit_price;
      }

      $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
      $available_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty),0) as total", $data)->row()->total,$this->config->item('Currency_amount_decimals_2'));
      $grand_total_available_stock_qty += $available_stock_qty;
      if (($available_stock_only == 1 && $available_stock_qty > 0) || ($available_stock_only == 0)) {

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $available_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_available_stock_price += $available_stock_price;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_purchased_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_purchased_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_purchased_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_purchased_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_purchased_stock_price += $total_purchased_stock_price;
        $grand_total_purchased_stock_qty += $total_purchased_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_in_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_in_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_in_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_in_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_transferred_in_stock_price += $total_transferred_in_stock_price;
        $grand_total_transferred_in_stock_qty += $total_transferred_in_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_out_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_out_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_out_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_out_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_transferred_out_stock_price += $total_transferred_out_stock_price;
        $grand_total_transferred_out_stock_qty += $total_transferred_out_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_consumption_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_consumption_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_consumption_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_consumption_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_consumption_stock_price += $total_consumption_stock_price;
        $grand_total_consumption_stock_qty += $total_consumption_stock_qty;
        // Sale
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_sold_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_sold_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_sold_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_sold_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_sold_stock_price += $total_sold_stock_price;
        $grand_total_sold_stock_qty += $total_sold_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_production_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_production_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_production_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_production_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_production_stock_price += $total_production_stock_price;
        $grand_total_production_stock_qty += $total_production_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_opening_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_opening_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_opening_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_opening_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_opening_stock_price += $total_opening_stock_price;
        $grand_total_opening_stock_qty += $total_opening_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_purchase_returned_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_purchase_return_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_purchase_returned_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_purchase_return_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_purchase_returned_stock_price += $total_purchase_returned_stock_price;
        $grand_total_purchase_returned_stock_qty += $total_purchase_returned_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_damaged_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_damaged_qty),0) as total", $data)->row()->total, 2);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_damaged_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_damaged_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_damaged_stock_price += $total_damaged_stock_price;
        $grand_total_damaged_stock_qty += $total_damaged_stock_qty;

        $purchased_rate = 0;
        $purchase_item_data = $this->Purchases->select_purchase_items("pi.*,m.material_name,mu.unit_name,tt.tax_percentage,tt.tax1_percent,tt.tax2_percent,tt.tax3_percent",array("pi.item_id"=>$row->purchase_item_id),'','','','all');
        if($purchase_item_data->num_rows()==1){
          $purchased_rate = $purchase_item_data->row()->unit_price;
        }

        if ($row->location_type == 'work_site') {
          $location_type = "Work Site";
          $profile_page = '<a href="' . base_url() . 'work/profile/' . $row->work_id . '">' . $row->location_name . '</a>';
        } else {
          $location_type = "Ware House";
          $profile_page = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '</a>';
        }
        $material_name=$row->material_name;
        if($row->item_code!=''){
          $material_name.="(HSN: ".$row->item_code.")";
        }
        if($row->stock_description!=''){
          $material_name.="[".$row->stock_description."]";
        }
        if($row->material_specification!=''){
          $material_name.=" - ".$row->material_specification;
        }
        $sl_no++;
        $array[$j][]=$sl_no;
        $array[$j][]=$row->stock_ids;
        $array[$j][]=$row->material_id;
        $array[$j][]=$row->unit;
        $array[$j][]=$row->location_id;
        $array[$j][]=$material_name;
        // $array[$j][]=$row->material_name."<br/>".$row->stock_ids;
        $array[$j][] = $row->unit_name;
        if ($list_type != 'transfer_list' && $list_type != 'sales_list') {
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->unit_price, $this->config->item('Currency_currency_shortname'));
        } else {
          $array[$j][] = round($row->unit_price, 2);
        }
        if ($list_type == 'sales_list') {
          if(isset($sale_price_category)){
            $sale_price_where_data['mp.material_id'] = $row->material_id;
            $sale_price_where_data['mp.branch_id'] = $this->session->userdata('branch_id');
            $material_sale_price = $this->Material->select_material_prices("",$sale_price_where_data);
            if($material_sale_price->num_rows()==1){
              $material_sale_price = $material_sale_price->row();
              $array[$j][] = $material_sale_price->{'sale_price'.$sale_price_category};
            }else{
              $array[$j][] = '';
            }
          }else{
            $array[$j][]=$row->unit_price;
          }
        }
        if ($row->purchase_item_id == 0) {
          if ($total_production_qty != 0) {
            $array[$j][] = "(Production)";
          }elseif($total_opening_qty != 0) {
            $array[$j][] = "(Opening Stock)";
          }elseif($total_transferred_in_stock_qty != 0) {
            $array[$j][] = $row->supplier_name . "<br/>(Transfered IN)";
          }else{
            $array[$j][] = $row->supplier_name;
          }
        } else {
          $array[$j][] = $row->supplier_name;
        }

        $array[$j][] = $available_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($available_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $profile_page;
        $array[$j][] = $location_type;
        $array[$j][] = $total_opening_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_opening_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = '<a href="#purchase_history_modal" data-toggle="modal" data-supplier-id="'.$row->purchase_supplier_id.'" data-unit-price="'.(int)($row->unit_price).'" id="purchase_history_btn">' . $total_purchased_stock_qty . '</a>';
        $array[$j][] = '<a href="#purchase_history_modal" data-toggle="modal" data-supplier-id="'.$row->purchase_supplier_id.'" data-unit-price="'.(int)($row->unit_price).'" id="purchase_history_btn">' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_purchased_stock_price, $this->config->item('Currency_currency_shortname')) . '</a>';
        if ($this->config->item('app_module_production') == 1){
          $array[$j][] = $total_production_qty;
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_production_stock_price, $this->config->item('Currency_currency_shortname'));
        }
        $array[$j][] = $total_transferred_in_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_transferred_in_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $total_transferred_out_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_transferred_out_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $total_consumption_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_consumption_stock_price, $this->config->item('Currency_currency_shortname'));
        if ($this->config->item('app_module_sales_invoice') == 1){
          $array[$j][] = $total_sold_stock_qty;
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_sold_stock_price, $this->config->item('Currency_currency_shortname'));
        }
        $array[$j][] = $total_purchase_returned_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_purchase_returned_stock_price, $this->config->item('Currency_currency_shortname'));
        if ($this->config->item('app_module_production') == 1){
          $array[$j][] = $total_damaged_stock_qty;
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_damaged_stock_price, $this->config->item('Currency_currency_shortname'));
        }
        $array[$j][] = $row->purchase_supplier_id;
        if ($list_type == 'transfer_list' || $list_type == 'sales_list') {
          $btn_add_cart = '<button type="button" style="margin-left: 5px;margin-right: 5px" id="btn_transfer_cart"  class="btn btn-primary m-btn m-btn--icon  m-btn--pill m-btn--air">
          <i class="fa fa-plus-circle"></i> Add to Cart
          </button>';
          $array[$j][] = $btn_add_cart;
        } elseif ($list_type == 'consumption') {
          $consumed_qty_input = '<div class="form-group m-form__group">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="0" max="' . $available_stock_qty . '" type="text" name="qty_' . $sl_no . '" class="consumed_qty numeric_fields form-control m-input m-input--pill m-input--air" placeholder="Consumed Qty"  aria-describedby="basic-addon1">
          <div class="input-group-append"><span class="input-group-text" id="basic-addon1">' . $row->unit_name . '</span></div>
          </div>
          </div>
          <input type="hidden" name="stock_ids_' . $sl_no . '" value="' . $row->stock_ids . '" />
          <input type="hidden" name="stock_slnos[]" value="' . $sl_no . '" />';
          $array[$j][] = $consumed_qty_input;
        }elseif ($list_type == 'return') {
          $array[$j][] = '<div class="form-group m-form__group">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="0" data-slno="' . $sl_no . '" max="' . $available_stock_qty . '" type="text" name="qty_' . $sl_no . '" id="qty_' . $sl_no . '" class="consumed_qty amt_fields numeric_fields form-control m-input m-input--pill m-input--air" placeholder="Returned Qty"  aria-describedby="basic-addon1">
          <div class="input-group-append"><span class="input-group-text" id="basic-addon1">' . $row->unit_name . '</span></div>
          </div>
          </div>
          <input type="hidden" name="stock_ids_' . $sl_no . '" value="' . $row->stock_ids . '" />
          <input type="hidden" name="stock_slnos[]" value="' . $sl_no . '" />';
          $array[$j][] = '<div class="form-group m-form__group">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="'.$purchased_rate.'" type="text" data-slno="' . $sl_no . '" name="rate_' . $sl_no . '" id="rate_' . $sl_no . '" class="numeric_fields amt_fields form-control m-input m-input--pill m-input--air" placeholder="Rate"  aria-describedby="basic-addon1">
          </div></div>';
          $array[$j][] = '<div class="form-group m-form__group">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required readonly value="0" data-slno="' . $sl_no . '"  type="text" name="total_' . $sl_no . '" id="total_' . $sl_no . '" class="total_amt_field numeric_fields form-control m-input m-input--pill m-input--air"  aria-describedby="basic-addon1">
          </div></div>';
        } else {
          $array[$j][] = "";
        }
        $array[$j][]=$row->item_code;
        $array[$j][]=$row->stock_description;
        $array[$j][]=$row->material_specification;
        $j++;
      }

    endforeach;
    if (sizeof($array) > 0) {
      if ($show_qty_total == 0) {
        $grand_total_transferred_out_stock_qty = $grand_total_transferred_in_stock_qty = $grand_total_consumption_stock_qty = $grand_total_purchased_stock_qty = $grand_total_available_stock_qty = '';
      } else {
        $grand_total_transferred_out_stock_qty = round($grand_total_transferred_out_stock_qty, 2);
        $grand_total_transferred_in_stock_qty = round($grand_total_transferred_in_stock_qty, 2);
        $grand_total_consumption_stock_qty = round($grand_total_consumption_stock_qty, 2);
        $grand_total_purchased_stock_qty = round($grand_total_purchased_stock_qty, 2);
        $grand_total_available_stock_qty = round($grand_total_available_stock_qty, 2);
      }

      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>Grand Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = $grand_total_available_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_available_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][] = $grand_total_opening_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_opening_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]=$grand_total_purchased_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_purchased_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      if ($this->config->item('app_module_production') == 1){
        $array[$j][]=$grand_total_production_stock_qty;
        $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_production_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      }
      $array[$j][]=$grand_total_transferred_in_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_transferred_in_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]=$grand_total_transferred_out_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_transferred_out_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]=$grand_total_consumption_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_consumption_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      if ($this->config->item('app_module_sales_invoice') == 1){
        $array[$j][]=$grand_total_sold_stock_qty;
        $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_sold_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      }
      $array[$j][]=$grand_total_purchase_returned_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_purchase_returned_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      if ($this->config->item('app_module_production') == 1){
        $array[$j][]=$grand_total_damaged_stock_qty;
        $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_damaged_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      }
      $array[$j][]='';
      $array[$j][]='<input type="hidden" name="count" value="'.$sl_no.'" />';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      if ($list_type == 'return') {
        $array[$j][]='';
        $array[$j][]='';
      }

    }

    $json_data['data'] = $array;
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = sizeof($array);
    $json_data['recordsFiltered'] = sizeof($array);
    echo json_encode($json_data);  // send data as json format


  }
  public function select_stock_register_2($list_type = "")
  {
    $json_data = array();
    $j = $show_qty_total = 0;
    $sl_no = 0;
    $data = $array = array();
    if (isset($_POST['sale_price_category']) && $_POST['sale_price_category'] != '' && $_POST['sale_price_category'] != 'all') {
      $sale_price_category = $_POST['sale_price_category'];
    }
    if (isset($_POST['location_id']) && $_POST['location_id'] != '' && $_POST['location_id'] != 'all') {
      $data['sr.location_id'] = $_POST['location_id'];
    }
    if (isset($_POST['supplier']) && $_POST['supplier'] != '' && $_POST['supplier'] != 'all') {
      $data['sr.purchase_supplier_id'] = $_POST['supplier'];
    }
    if (isset($_POST['work_id']) && $_POST['work_id'] != '' && $_POST['work_id'] != 'all') {
      $data['sl.work_id'] = $_POST['work_id'];
    }
    if (isset($_POST['material_type']) && $_POST['material_type'] != '' && $_POST['material_type'] != 'all') {
      $data['m.material_type'] = $_POST['material_type'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('sl.work_id',explode(',',$_POST['work_ids']));
    }
    if (isset($_POST['material_id']) && $_POST['material_id'] != '' && $_POST['material_id'] != 'all') {
      $data['sr.material_id'] = $_POST['material_id'];
      $show_qty_total = 1;
    }
    if (isset($_POST['user_id']) && $_POST['user_id'] != '' && $_POST['user_id'] != 'all') {
      $data['sr.created_by'] = $_POST['user_id'];
    }
    $available_stock_only = 0;
    if (isset($_POST['quantity']) && $_POST['quantity'] == 'available') {
      $available_stock_only = 1;
    }
    if($this->config->item('Stock_stock_register_material_based_only')==1){
      $group_by_columns = array('sr.material_id','sr.unit','sr.location_id');
    }else{
      $group_by_columns = array('sr.material_id','sr.unit','sr.purchase_supplier_id','sr.unit_price','sr.location_id');
    }
    if ($this->session->userdata('user_role') == 'supervisor') {
      $data['sl.location_type'] = 'work_site';
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc.assigned_users)");
      // Select All Available Materials in Stock
      $materials	= $this->Stock->select_stock_register_2("GROUP_CONCAT(sr.stock_id) AS stock_ids,sr.stock_description,sr.stock_id,sr.purchase_supplier_id,sr.unit_price,s.supplier_name,sr.unit,sr.material_id,m.material_specification,mu.unit_name,mu.unit_id,m.item_code,m.material_name,sr.location_id,sl.location_name,sl.location_type,sl.work_id,sr.purchase_item_id",$data,array(3));
    }else{
      // Select All Available Materials in Stock
      $materials	= $this->Stock->select_stock_register("GROUP_CONCAT(sr.stock_id) AS stock_ids,sr.stock_description,sr.stock_id,sr.purchase_supplier_id,sr.unit_price,s.supplier_name,sr.unit,sr.material_id,m.material_specification,mu.unit_name,mu.unit_id,m.item_code,m.material_name,sr.location_id,sl.location_name,sl.location_type,sl.work_id,sr.purchase_item_id",$data,$group_by_columns);
    }


    $grand_total_transferred_out_stock_qty = $grand_total_transferred_in_stock_qty = $grand_total_consumption_stock_qty = $grand_total_purchased_stock_qty = $grand_total_available_stock_qty = $grand_total_available_stock_price = $grand_total_purchased_stock_price = $grand_total_transferred_in_stock_price = $grand_total_transferred_out_stock_price = $grand_total_consumption_stock_price = 0;
    foreach ($materials->result() as $row) :


      $data['sr.material_id'] = $row->material_id;
      $data['sr.location_id'] = $row->location_id;
      $data['sr.unit'] = $row->unit;
      if($this->config->item('Stock_stock_register_material_based_only')!=1){
        $data['sr.purchase_supplier_id'] = $row->purchase_supplier_id;
        $data['sr.unit_price like'] = $row->unit_price;
      }

      $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
      $available_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty),0) as total", $data)->row()->total, 2);
      $grand_total_available_stock_qty += $available_stock_qty;
      if (($available_stock_only == 1 && $available_stock_qty > 0) || ($available_stock_only == 0)) {

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $available_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_available_stock_price += $available_stock_price;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_purchased_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_purchased_qty),0) as total", $data)->row()->total);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_purchased_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_purchased_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_purchased_stock_price += $total_purchased_stock_price;
        $grand_total_purchased_stock_qty += $total_purchased_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_in_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_in_qty),0) as total", $data)->row()->total);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_in_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_in_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_transferred_in_stock_price += $total_transferred_in_stock_price;
        $grand_total_transferred_in_stock_qty += $total_transferred_in_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_out_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_out_qty),0) as total", $data)->row()->total);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_transferred_out_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_out_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_transferred_out_stock_price += $total_transferred_out_stock_price;
        $grand_total_transferred_out_stock_qty += $total_transferred_out_stock_qty;

        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_consumption_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_consumption_qty),0) as total", $data)->row()->total);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_consumption_stock_price = $this->Stock->select_stock_register("COALESCE(SUM(sr.total_consumption_qty*sr.unit_price),0) as total", $data)->row()->total;
        // $grand_total_consumption_stock_price += $total_consumption_stock_price;
        // Sale
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_consumption_stock_qty += round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_sold_qty),0) as total", $data)->row()->total);
        $this->db->where('sr.stock_id IN (' . $row->stock_ids . ')');
        $total_consumption_stock_price += $this->Stock->select_stock_register("COALESCE(SUM(sr.total_sold_qty*sr.unit_price),0) as total", $data)->row()->total;
        $grand_total_consumption_stock_price += $total_consumption_stock_price;
        $grand_total_consumption_stock_qty += $total_consumption_stock_qty;

        $purchased_rate = $purchased_tax = 0;
        $purchase_item_data = $this->Purchases->select_purchase_items("pi.*,m.material_name,mu.unit_name,tt.tax_percentage,tt.tax1_percent,tt.tax2_percent,tt.tax3_percent",array("pi.item_id"=>$row->purchase_item_id),'','','','all','','all');
        if($purchase_item_data->num_rows()==1){
          $purchased_rate = $purchase_item_data->row()->unit_price;
          $purchased_tax = $purchase_item_data->row()->tax_percent;
        }

        if ($row->location_type == 'work_site') {
          $location_type = "Work Site";
          $profile_page = '<a href="' . base_url() . 'work/profile/' . $row->work_id . '">' . $row->location_name . '</a>';
        } else {
          $location_type = "Ware House";
          $profile_page = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '</a>';
        }
        $material_name=$row->material_name;
        if($row->item_code!=''){
          $material_name.="(HSN: ".$row->item_code.")";
        }
        if($row->stock_description!=''){
          $material_name.="[".$row->stock_description."]";
        }
        if($row->material_specification!=''){
          $material_name.=" - ".$row->material_specification;
        }
        $sl_no++;
        $array[$j][]=$sl_no;
        $array[$j][]=$row->stock_ids;
        $array[$j][]=$row->material_id;
        $array[$j][]=$row->unit;
        $array[$j][]=$row->location_id;
        $array[$j][]=$material_name;
        // $array[$j][]=$row->material_name."<br/>".$row->stock_ids;
        $array[$j][] = $row->unit_name;
        if ($list_type != 'transfer_list' && $list_type != 'sales_list') {
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->unit_price, $this->config->item('Currency_currency_shortname'));
        } else {
          $array[$j][] = round($row->unit_price, 2);
        }
        if ($list_type == 'sales_list') {
          if(isset($sale_price_category)){
            $sale_price_where_data['mp.material_id'] = $row->material_id;
            $sale_price_where_data['mp.branch_id'] = $this->session->userdata('branch_id');
            $material_sale_price = $this->Material->select_material_prices("",$sale_price_where_data);
            if($material_sale_price->num_rows()==1){
              $material_sale_price = $material_sale_price->row();
              $array[$j][] = $material_sale_price->{'sale_price'.$sale_price_category};
            }else{
              $array[$j][] = '';
            }
          }else{
            $array[$j][]=$row->unit_price;
          }
        }
        if ($row->purchase_item_id == 0) {
          $array[$j][] = $row->supplier_name . "<br/>(Transfered IN)";
        } else {
          $array[$j][] = $row->supplier_name;
        }

        $array[$j][] = $available_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($available_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $profile_page;
        $array[$j][] = $location_type;
        $array[$j][] = '<a href="#purchase_history_modal" data-toggle="modal" data-supplier-id="'.$row->purchase_supplier_id.'" data-unit-price="'.(int)($row->unit_price).'" id="purchase_history_btn">' . $total_purchased_stock_qty . '</a>';
        $array[$j][] = '<a href="#purchase_history_modal" data-toggle="modal" data-supplier-id="'.$row->purchase_supplier_id.'" data-unit-price="'.(int)($row->unit_price).'" id="purchase_history_btn">' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_purchased_stock_price, $this->config->item('Currency_currency_shortname')) . '</a>';
        $array[$j][] = $total_transferred_in_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_transferred_in_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $total_transferred_out_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_transferred_out_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $total_consumption_stock_qty;
        $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_consumption_stock_price, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $row->purchase_supplier_id;
        if ($list_type == 'transfer_list' || $list_type == 'sales_list') {
          $btn_add_cart = '<button type="button" style="margin-left: 5px;margin-right: 5px" id="btn_transfer_cart"  class="btn btn-primary m-btn m-btn--icon  m-btn--pill m-btn--air">
          <i class="fa fa-plus-circle"></i> Add to Cart
          </button>';
          $array[$j][] = $btn_add_cart;
        } elseif ($list_type == 'consumption') {
          $consumed_qty_input = '<div class="form-group m-form__group">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="0" max="' . $available_stock_qty . '" type="text" name="qty_' . $sl_no . '" class="consumed_qty numeric_fields form-control m-input m-input--pill m-input--air" placeholder="Consumed Qty"  aria-describedby="basic-addon1">
          <div class="input-group-append"><span class="input-group-text" id="basic-addon1">' . $row->unit_name . '</span></div>
          </div>
          </div>
          <input type="hidden" name="stock_ids_' . $sl_no . '" value="' . $row->stock_ids . '" />
          <input type="hidden" name="stock_slnos[]" value="' . $sl_no . '" />';
          $array[$j][] = $consumed_qty_input;
        }elseif ($list_type == 'damages') {
          $consumed_qty_input = '<div class="form-group m-form__group">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="0" max="' . $available_stock_qty . '" type="text" name="qty_' . $sl_no . '" class="damaged_qty numeric_fields form-control m-input m-input--pill m-input--air" placeholder="Damaged Qty"  aria-describedby="basic-addon1">
          <div class="input-group-append"><span class="input-group-text" id="basic-addon1">' . $row->unit_name . '</span></div>
          </div>
          </div>
          <input type="hidden" name="stock_ids_' . $sl_no . '" value="' . $row->stock_ids . '" />
          <input type="hidden" name="stock_slnos[]" value="' . $sl_no . '" />';
          $array[$j][] = $consumed_qty_input;
        }elseif ($list_type == 'return') {
          $array[$j][] = '<div class="form-group m-form__group" style="width:150px">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="0" data-slno="' . $sl_no . '" max="' . $available_stock_qty . '" type="text" name="qty_' . $sl_no . '" id="qty_' . $sl_no . '" class="consumed_qty amt_fields numeric_fields form-control m-input m-input--pill m-input--air" placeholder="Returned Qty"  aria-describedby="basic-addon1">
          <div class="input-group-append"><span class="input-group-text" id="basic-addon1">' . $row->unit_name . '</span></div>
          </div>
          </div>
          <input type="hidden" name="stock_ids_' . $sl_no . '" value="' . $row->stock_ids . '" />
          <input type="hidden" name="stock_slnos[]" value="' . $sl_no . '" />';
          $array[$j][] = '<div class="form-group m-form__group" style="width:100px">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required value="'.$purchased_rate.'" type="text" data-slno="' . $sl_no . '" name="rate_' . $sl_no . '" id="rate_' . $sl_no . '" class="numeric_fields amt_fields form-control m-input m-input--pill m-input--air" placeholder="Rate"  aria-describedby="basic-addon1">
          </div></div>';
          $tax = '<div class="form-group m-form__group" style="width:150px">
          <div class="m-select2 m-select2--air m-select2--pill ">
            <select required id="tax_percent_' . $sl_no . '" name="tax_percent_' . $sl_no . '" data-slno="' . $sl_no . '"  style="width: 100%" class="form-control m-input amt_fields tax_percent_fields" aria-describedby="basic-addon1" data-placeholder="Select Tax Percentage">';
              $tax_percent = $this->config->item('Tax_tax_percent');
              foreach($tax_percent as $row1):
                $selected_tax='';
                if($row1->tax_percentage==$purchased_tax){
                  $selected_tax='selected';
                }
                $tax .= '<option '.$selected_tax.'  data-tax-percent="'.$row1->tax_percentage.'" value="'.$row1->tax_id.'">'.$row1->tax_name.'</option>';
              endforeach;
            $tax .= '</select>
          </div>
          </div>';
          $array[$j][] = $tax;
          $array[$j][] = '<div class="form-group m-form__group" style="width:100px">
          <div class="input-group m-input-group m-input-group--pill">
          <input style="max-width: 100px;" required readonly value="0" data-slno="' . $sl_no . '"  type="text" name="total_' . $sl_no . '" id="total_' . $sl_no . '" class="total_amt_field numeric_fields form-control m-input m-input--pill m-input--air"  aria-describedby="basic-addon1">
          </div></div>';
        } else {
          $array[$j][] = "";
        }
        $array[$j][]=$row->item_code;
        $array[$j][]=$row->stock_description;
        $array[$j][]=$row->material_specification;
        $j++;
      }

    endforeach;
    if (sizeof($array) > 0) {
      if ($show_qty_total == 0) {
        $grand_total_transferred_out_stock_qty = $grand_total_transferred_in_stock_qty = $grand_total_consumption_stock_qty = $grand_total_purchased_stock_qty = $grand_total_available_stock_qty = '';
      } else {
        $grand_total_transferred_out_stock_qty = round($grand_total_transferred_out_stock_qty, 2);
        $grand_total_transferred_in_stock_qty = round($grand_total_transferred_in_stock_qty, 2);
        $grand_total_consumption_stock_qty = round($grand_total_consumption_stock_qty, 2);
        $grand_total_purchased_stock_qty = round($grand_total_purchased_stock_qty, 2);
        $grand_total_available_stock_qty = round($grand_total_available_stock_qty, 2);
      }

      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>Grand Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = $grand_total_available_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_available_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]=$grand_total_purchased_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_purchased_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]=$grand_total_transferred_in_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_transferred_in_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]=$grand_total_transferred_out_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_transferred_out_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]=$grand_total_consumption_stock_qty;
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_consumption_stock_price,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]='';
      $array[$j][]='<input type="hidden" name="count" value="'.$sl_no.'" />';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      if ($list_type == 'return') {
        $array[$j][]='';
        $array[$j][]='';
      }

    }

    $json_data['data'] = $array;
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = sizeof($array);
    $json_data['recordsFiltered'] = sizeof($array);
    echo json_encode($json_data);  // send data as json format


  }
  public function select_stock_consumption($page_data_type = "")
  {

    $json_data = array();
    $j = 0;
    $sl_no = 0;
    $data = $array = array();
    if (isset($_POST['location_id']) && $_POST['location_id'] != '' && $_POST['location_id'] != 'all') {
      $data['sr.location_id'] = $_POST['location_id'];
    }
    if (isset($_POST['work_id']) && $_POST['work_id'] != '' && $_POST['work_id'] != 'all') {
      $data['sc.work_id'] = $_POST['work_id'];
    }
    if (isset($_POST['vehicle_id']) && $_POST['vehicle_id'] != '' && $_POST['vehicle_id'] != 'all') {
      $data['sc.vehicle_id'] = $_POST['vehicle_id'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('sc.work_id',explode(',',$_POST['work_ids']));
    }
    if (isset($_POST['stage_id']) && $_POST['stage_id'] != '' && $_POST['stage_id'] != 'all') {
      $data['sc.project_stage_id'] = $_POST['stage_id'];
    }
    if (isset($_POST['material_id']) && $_POST['material_id'] != '' && $_POST['material_id'] != 'all') {
      $data['sr.material_id'] = $_POST['material_id'];
    }
    if (isset($_POST['material_category']) && $_POST['material_category'] != '' && $_POST['material_category'] != 'all') {
      $data['m.category'] = $_POST['material_category'];
    }
    if (isset($_POST['user_id']) && $_POST['user_id'] != '' && $_POST['user_id'] != 'all') {
      $data['sc.created_by'] = $_POST['user_id'];
    }
    if (isset($_POST['start_date']) && $_POST['start_date'] != '' && $_POST['start_date'] != 'all') {
      $data['sc.consumption_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if (isset($_POST['end_date']) && $_POST['end_date'] != '' && $_POST['end_date'] != 'all') {
      $data['sc.consumption_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    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(sc.consumption_date,'%Y-%m-%d') <="] = $end_date;
      $data["STR_TO_DATE(sc.consumption_date,'%Y-%m-%d') >="] = $start_date;
    }
    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)");
    }
    $entry_approval = 1;
    if ($page_data_type == 'approval_entries') {
      $entry_approval = 'all';
      $data['u.user_role >'] = 2;
      if (isset($_POST['approval_status']) && $_POST['approval_status'] != '' && $_POST['approval_status'] != 'all') {
        $entry_approval = $_POST['approval_status'];
      }
    }
    if (isset($_POST['type']) && $_POST['type'] =='vehicle') {
      $data['sc.vehicle_id !='] = 0;
    }else{
      $data['sc.work_id !='] = 0;
    }
    // Select All Available Materials in Stock
    $consumption  = $this->Stock->select_stock_consumption("GROUP_CONCAT(sc.cons_id) AS cons_ids,COALESCE(SUM(sc.consumption_qty),0) as total_consumed_qty,COALESCE(SUM(sc.consumption_qty*sr.unit_price),0) as total_consumed_amt,sc.*,sr.unit_price,sr.material_id,m.material_name,mu.unit_name,sr.unit,sr.location_id,u.user_alias as created_user,wc.work_name,u.user_role as created_user_role_id,v.vehicle_name,v.vehicle_no", $data, array("sc.consumption_date", "sc.cons_group_id", 'sr.material_id','sr.unit','sr.purchase_supplier_id','sr.unit_price','sr.location_id'), $entry_approval);
    $total_amount = 0;
    foreach ($consumption->result() as $row) :
      if ($row->cons_id != '') {
        $stock_amount = round($row->total_consumed_amt,$this->config->item('Currency_amount_decimals_2'));
        $total_amount += $stock_amount;

        $btn_delete=$btn_approve = $entry_approval_status = '';
        if (($this->session->userdata('user_role') == 'admin' || ($this->session->userdata('user_role') == 'accounts_manager' && $row->created_user_role_id > 2 && (($this->config->item('User_enable_data_entry_approval_2_level')==0 && $row->created_user_role_id != 16) || $this->config->item('User_enable_data_entry_approval_2_level')==1))) && $page_data_type == 'approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval') == 1) {
          $btn_approve = '<a style="margin-left: 5px;margin-right: 5px" data-type="stock_consumption" data-approval-status="' . $row->entry_approval_status . '" data-id="' . $row->cons_id . '" id="btn_data_entry_approval" href="#data_entry_approve_modal" data-toggle="modal" class="btn btn-sm btn-warning m-btn m-btn--icon  m-btn--pill m-btn--air">
          <i class="la la-check-circle"></i> / <i class="la la-times-circle"></i>
          </a>';
        }
        if(($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_id')==$row->created_by) && ($this->config->item('User_enable_data_entry_approval')!=1 || $row->entry_approval_status!=1 || $this->session->userdata('user_role')=='admin')) {
          if($row->dpr_cons_id == 0 && $row->production_used_id == 0){
            $btn_delete='<a style="margin-left: 5px;margin-right: 5px"  id="btn_delete_consumption"  href="#consumption_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 ($page_data_type == 'approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval') == 1) {
          if ($row->entry_approval_status == 1) {
            $entry_approval_status = "<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Approved</span>";
          } elseif ($row->entry_approval_status == 2) {
            $entry_approval_status = "<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> Rejected</span>";
          } else {
            $entry_approval_status = "<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> Pending</span>";
          }
        }


        $sl_no++;
        $array[$j][] = $sl_no;
        $array[$j][] = $row->cons_id;
        $array[$j][] = date('d-m-Y', strtotime($row->consumption_date));
        $array[$j][] = $row->material_name;
        $array[$j][] = $row->unit_name;
        $array[$j][] = round($row->total_consumed_qty,2);
        $array[$j][] = $stock_amount;
        if($row->vehicle_id!=0){
          $array[$j][] = '<a href="'.base_url().'vehicles/profile/'.$row->vehicle_id.'">'.$row->vehicle_name.' ['.$row->vehicle_no.']</a>';
        }else{
          $array[$j][] = '<a href="' . base_url() . 'work/profile/' . $row->work_id . '">' . $row->work_name . '</a>';
        }
        $array[$j][] = $row->created_user . $entry_approval_status;
        $array[$j][] = $btn_approve.$btn_delete;

        $j++;
      }

    endforeach;
    if (sizeof($array) > 0) {
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>Grand Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>" . round($total_amount,$this->config->item('Currency_amount_decimals_2')) . "</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
    }
    $json_data['data'] = $array;
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = sizeof($array);
    $json_data['recordsFiltered'] = sizeof($array);
    echo json_encode($json_data);  // send data as json format
  }
  public function select_stock_transfer($page_data_type = "")
  {

    $json_data = array();
    $j = 0;
    $sl_no = 0;
    $data = $array = array();
    if (isset($_POST['location_from']) && $_POST['location_from'] != '' && $_POST['location_from'] != 'all') {
      $data['st.transferred_from_loc_id'] = $_POST['location_from'];
    }
    if (isset($_POST['work_id_in']) && $_POST['work_id_in'] != '' && $_POST['work_id_in'] != 'all') {
      $data['sl2.work_id'] = $_POST['work_id_in'];
    }
    if(isset($_POST['work_ids_in']) && $_POST['work_ids_in']!='' && $_POST['work_ids_in']!='all'){
      $this->db->where_in('sl2.work_id',explode(',',$_POST['work_ids_in']));
    }
    if (isset($_POST['work_id_out']) && $_POST['work_id_out'] != '' && $_POST['work_id_out'] != 'all') {
      $data['sl1.work_id'] = $_POST['work_id_out'];
    }
    if(isset($_POST['work_ids_out']) && $_POST['work_ids_out']!='' && $_POST['work_ids_out']!='all'){
      $this->db->where_in('sl1.work_id',explode(',',$_POST['work_ids_out']));
    }
    if (isset($_POST['work_id']) && $_POST['work_id'] != '' && $_POST['work_id'] != 'all') {
      $work_id = $_POST['work_id'];
      $this->db->where('(sl1.work_id=' . $work_id . ' or sl2.work_id=' . $work_id . ')');
    }
    if (isset($_POST['location_to']) && $_POST['location_to'] != '' && $_POST['location_to'] != 'all') {
      $data['st.transferred_to_loc_id'] = $_POST['location_to'];
    }
    if (isset($_POST['material_id']) && $_POST['material_id'] != '' && $_POST['material_id'] != 'all') {
      $data['sr1.material_id'] = $_POST['material_id'];
    }
    if (isset($_POST['material_category']) && $_POST['material_category'] != '' && $_POST['material_category'] != 'all') {
      $data['m.category'] = $_POST['material_category'];
    }
    if (isset($_POST['user_id']) && $_POST['user_id'] != '' && $_POST['user_id'] != 'all') {
      $data['st.created_by'] = $_POST['user_id'];
    }
    if ($this->config->item('user_data_access_view_all') != 1) {
      $data['st.created_by'] = $this->session->userdata('user_role');
    }
    if (isset($_POST['start_date']) && $_POST['start_date'] != '' && $_POST['start_date'] != 'all') {
      $data['st.transfer_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if (isset($_POST['end_date']) && $_POST['end_date'] != '' && $_POST['end_date'] != 'all') {
      $data['st.transfer_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    $entry_approval = 1;
    if ($page_data_type == 'approval_entries') {
      $entry_approval = 'all';
      $data['u.user_role >'] = 2;
      if (isset($_POST['approval_status']) && $_POST['approval_status'] != '' && $_POST['approval_status'] != 'all') {
        $entry_approval = $_POST['approval_status'];
      }
    }
    // Select All Available Materials in Stock
    $transfer  = $this->Stock->select_stock_transfers("COALESCE(SUM(st.transferred_qty),0) as total_transferred_qty,COALESCE(SUM(st.transferred_qty*sr2.unit_price),0) as total_transferred_amt,st.*,sr1.unit_price,sr1.material_id,m.material_name,mu.unit_name,sr1.unit,sl1.location_name as transferred_from_loc_name,sl2.location_name as transferred_to_loc_name,u.user_alias as created_user,u.user_role as created_user_role_id,sl1.location_type as location_type_from,sl2.location_type as location_type_to,sl1.work_id as location_work_id_from,sl2.work_id as location_work_id_to ", $data, array("st.transfer_date", "st.trans_group_id", "sr1.material_id", "sr1.unit"), $entry_approval);
    $total_amount = 0;
    foreach ($transfer->result() as $row) :

      if ($row->transfer_id != '') {
        $stock_amount = round($row->total_transferred_amt, $this->config->item('Currency_amount_decimals_2'));
        $total_amount += $row->total_transferred_amt;

        $btn_approve = $entry_approval_status = '';
        if (($this->session->userdata('user_role') == 'admin' || ($this->session->userdata('user_role') == 'accounts_manager' && $row->created_user_role_id > 2 && (($this->config->item('User_enable_data_entry_approval_2_level')==0 && $row->created_user_role_id != 16) || $this->config->item('User_enable_data_entry_approval_2_level')==1))) && $page_data_type == 'approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval') == 1) {
          $btn_approve = '<a style="margin-left: 5px;margin-right: 5px" data-type="stock_transfer" data-approval-status="' . $row->entry_approval_status . '" data-id="' . $row->transfer_id . '" id="btn_data_entry_approval" href="#data_entry_approve_modal" data-toggle="modal" class="btn btn-sm btn-warning m-btn m-btn--icon  m-btn--pill m-btn--air">
          <i class="la la-check-circle"></i> / <i class="la la-times-circle"></i>
          </a>';
        }
        if ($page_data_type == 'approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval') == 1) {
          if ($row->entry_approval_status == 1) {
            $entry_approval_status = "<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Approved</span>";
          } elseif ($row->entry_approval_status == 2) {
            $entry_approval_status = "<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> Rejected</span>";
          } else {
            $entry_approval_status = "<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> Pending</span>";
          }
        }

        if($row->location_type_from=='work_site'){
          $location_name_from = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id_from . '">' . $row->transferred_from_loc_name . '<br/>[Work Site]</a>';
        }elseif($row->location_type_from=='ware_house'){
          $location_name_from = '<a href="' . base_url() . 'stock/locations/profile/' . $row->transferred_from_loc_id . '">' . $row->transferred_from_loc_name . '<br/>[Warehouse]</a>';
        }

        if($row->location_type_to=='work_site'){
          $location_name_to = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id_to . '">' . $row->transferred_to_loc_name . '<br/>[Work Site]</a>';
        }elseif($row->location_type_to=='ware_house'){
          $location_name_to = '<a href="' . base_url() . 'stock/locations/profile/' . $row->transferred_to_loc_id . '">' . $row->transferred_to_loc_name . '<br/>[Warehouse]</a>';
        }

        $sl_no++;
        $array[$j][] = $sl_no;
        $array[$j][] = $row->transfer_id;
        $array[$j][] = '<a href="'.base_url().'stock/transfer_bill/'.$row->transfer_bill_id.'">'."#".$row->transfer_bill_id.'</a>';
        $array[$j][] = date('d-m-Y',strtotime($row->transfer_date));
        $array[$j][] = $row->material_name;
        $array[$j][] = $row->unit_name;
        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->total_transferred_qty, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $this->config->item('currency_format')->formatCurrency($stock_amount, $this->config->item('Currency_currency_shortname'));
        $array[$j][] = $location_name_from;
        $array[$j][] = $location_name_to;
        $array[$j][] = $row->created_user . $entry_approval_status;
        if ($this->config->item('UI_hide_created_date_field')==0){
          $array[$j][] = date('d-m-Y', strtotime($row->created_date));
        }else{
          $array[$j][] = '';
        }
        $array[$j][] = $row->remarks;
        $array[$j][] = $btn_approve;

        $j++;
      }

    endforeach;
    if (sizeof($array) > 0) {
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>Grand Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>" . round($total_amount,$this->config->item('Currency_amount_decimals_2')) . "</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
    }
    $json_data['data'] = $array;
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = sizeof($array);
    $json_data['recordsFiltered'] = sizeof($array);
    echo json_encode($json_data);  // send data as json format
  }


  public function calculate_stock_balance($stock_id)
  {
    $this->stock_management->calculate_stock_balance($stock_id);
  }
  public function calculate_all_stock_balance()
  {
    $stock_register = $this->Stock->select_stock_register("stock_id")->result();
    foreach ($stock_register as $row) {
      $this->stock_management->calculate_stock_balance($row->stock_id);
    }
  }
  public function select_stock_transfer_bills($param=""){

    $json_data=array();
    $j=0;
    $sl_no=0;
    $data=$array=array();
    if (isset($_POST['start_date']) && $_POST['start_date'] != '' && $_POST['start_date'] != 'all') {
      $data['stb.bill_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if (isset($_POST['end_date']) && $_POST['end_date'] != '' && $_POST['end_date'] != 'all') {
      $data['stb.bill_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    if(isset($_POST['location_from']) && $_POST['location_from']!='' && $_POST['location_from']!='all'){
      $data['stb.transferred_from_loc_id'] = $_POST['location_from'];
    }
    if(isset($_POST['work_id_in']) && $_POST['work_id_in']!='' && $_POST['work_id_in']!='all'){
      $data['sl2.work_id'] = $_POST['work_id_in'];
    }
    if(isset($_POST['work_id_out']) && $_POST['work_id_out']!='' && $_POST['work_id_out']!='all'){
      $data['sl1.work_id'] = $_POST['work_id_out'];
    }
    if(isset($_POST['location_to']) && $_POST['location_to']!='' && $_POST['location_to']!='all'){
      $data['stb.transferred_to_loc_id'] = $_POST['location_to'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['stb.created_by'] = $_POST['user_id'];
    }
    if($this->session->userdata('user_role')!='admin' && $this->config->item('user_data_access_view_all')!=1){
      $data['stb.created_by'] = $this->session->userdata('user_role');
    }
    $transfer	= $this->Stock->select_stock_transfer_bill("stb.*,sl1.location_name as transferred_from_loc_name,sl2.location_name as transferred_to_loc_name,u.user_alias,sl1.location_type as location_type_from,sl2.location_type as location_type_to,sl1.work_id as location_work_id_from,sl2.work_id as location_work_id_to",$data);
    $total_amount = 0;
    foreach ($transfer->result() as $row):

      $total_amount += $row->total_bill_amount;

      if($row->location_type_from=='work_site'){
        $location_name_from = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id_from . '">' . $row->transferred_from_loc_name . '<br/>[Work Site]</a>';
      }elseif($row->location_type_from=='ware_house'){
        $location_name_from = '<a href="' . base_url() . 'stock/locations/profile/' . $row->transferred_from_loc_id . '">' . $row->transferred_from_loc_name . '<br/>[Warehouse]</a>';
      }

      if($row->location_type_to=='work_site'){
        $location_name_to = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id_to . '">' . $row->transferred_to_loc_name . '<br/>[Work Site]</a>';
      }elseif($row->location_type_to=='ware_house'){
        $location_name_to = '<a href="' . base_url() . 'stock/locations/profile/' . $row->transferred_to_loc_id . '">' . $row->transferred_to_loc_name . '<br/>[Warehouse]</a>';
      }

      $sl_no++;
      $array[$j][]=$sl_no;
      $array[$j][]='<a href="'.base_url().'stock/transfer_bill/'.$row->bill_id.'">'."#".$row->bill_id.'</a>';
      $array[$j][]=date('d-m-Y',strtotime($row->bill_date));
      $array[$j][] = $location_name_from;
      $array[$j][] = $location_name_to;
      $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->total_bill_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->total_received_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->total_balance_amount, $this->config->item('Currency_currency_shortname'));
      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;
    if(sizeof($array)>0){
      $array[$j][]='';
      $array[$j][] = '';
      $array[$j][]="<strong>Grand Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][]="<strong>".round($total_amount,$this->config->item('Currency_amount_decimals_2'))."</strong>";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';

    }
    $json_data['data']=$array;
    $json_data['draw']=5;
    $json_data['recordsTotal']=sizeof($array);
    $json_data['recordsFiltered']=sizeof($array);
    echo json_encode($json_data);  // send data as json format
  }

  public function select_stock_opening($page_data_type=""){

    $json_data=array();
    $j=0;
    $sl_no=0;
    $data=$array=array();
    if(isset($_POST['location_id']) && $_POST['location_id']!='' && $_POST['location_id']!='all'){
      $data['sr.location_id'] = $_POST['location_id'];
    }
    if(isset($_POST['work_id']) && $_POST['work_id']!='' && $_POST['work_id']!='all'){
      $data['sl.work_id'] = $_POST['work_id'];
    }
    if(isset($_POST['material_id']) && $_POST['material_id']!='' && $_POST['material_id']!='all'){
      $data['sr.material_id'] = $_POST['material_id'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['sr.created_by'] = $_POST['user_id'];
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $data['sr.stock_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $data['sr.stock_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    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(sr.stock_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(sr.stock_date,'%Y-%m-%d') >="]= $start_date;
    }
    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)");
    }
    $entry_approval = 1;
    if($page_data_type=='approval_entries'){
      $entry_approval = 'all';
      $data['u.user_role >'] =2;
      if(isset($_POST['approval_status']) && $_POST['approval_status']!='' && $_POST['approval_status']!='all'){
        $entry_approval = $_POST['approval_status'];
      }
    }
    $data['sr.total_opening_qty >'] = 0;
    // Select All Available Materials in Stock
    $stock_register	= $this->Stock->select_stock_register_2("sr.*,sl.location_type,m.material_name,mu.unit_name,u.user_alias as created_user,sl.location_name,sl.work_id as location_work_id",$data,'',$entry_approval);
    $total_amount = 0;
    foreach ($stock_register->result() as $row):
      $btn_edit = $btn_delete = '';
      $stock_amount = round($row->unit_price*$row->total_opening_qty,$this->config->item('Currency_amount_decimals_2'));
      $total_amount += $stock_amount;

      $btn_approve = $entry_approval_status = '';
      if(($this->session->userdata('user_role')=='admin' || ($this->session->userdata('user_role')=='accounts_manager' && $row->created_user_role_id>2 && (($this->config->item('User_enable_data_entry_approval_2_level')==0 && $row->created_user_role_id != 16) || $this->config->item('User_enable_data_entry_approval_2_level')==1))) && $page_data_type=='approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval')==1){
        $btn_approve = '<a style="margin-left: 5px;margin-right: 5px" data-type="stock_consumption" data-approval-status="'.$row->entry_approval_status.'" data-id="'.$row->cons_id.'" id="btn_data_entry_approval" href="#data_entry_approve_modal" data-toggle="modal" class="btn btn-sm btn-warning m-btn m-btn--icon  m-btn--pill m-btn--air">
        <i class="la la-check-circle"></i> / <i class="la la-times-circle"></i>
        </a>';
      }
      if($page_data_type=='approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval')==1){
        if($row->entry_approval_status==1){
          $entry_approval_status="<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Approved</span>";
        }elseif($row->entry_approval_status==2){
          $entry_approval_status="<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> Rejected</span>";
        }else{
          $entry_approval_status="<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> Pending</span>";
        }
      }

      $location_name = $row->location_name;
      if($row->location_type=='work_site'){
        $location_name = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id . '">' . $row->location_name . '<br/>[Work Site]</a>';
      }elseif($row->location_type=='ware_house'){
        $location_name = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '<br/>[Warehouse]</a>';
      }

      if ($this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'general_manager') {
        $btn_edit = '<a style="margin-left: 5px;margin-right: 5px" data-stock-id="'.$row->stock_id.'"  id="opening_stock_edit_btn" href="#opening_stock_edit_modal" data-toggle="modal" 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" data-stock-id="'.$row->stock_id.'" id="opening_stock_delete_btn" href="#opening_stock_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>';
      }

      $sl_no++;
      $array[$j][]=$sl_no;
      $array[$j][]=$row->stock_id;
      $array[$j][]=date('d-m-Y',strtotime($row->stock_date));
      $array[$j][]=$location_name;
      $array[$j][]=$row->material_name;
      $array[$j][]=$row->unit_name;
      $array[$j][]=round($row->total_opening_qty);
      $array[$j][]=$row->unit_price;
      $array[$j][]=$stock_amount;
      $array[$j][]=$row->stock_description;
      $array[$j][]=$row->stock_remarks;
      $array[$j][]=$row->created_user.$entry_approval_status;
      $array[$j][]=$btn_edit.$btn_delete.$btn_approve;

      $j++;

    endforeach;
    if(sizeof($array)>0){
      $array[$j][]='';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][]="<strong>Grand Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][]="<strong>".round($total_amount,$this->config->item('Currency_amount_decimals_2'))."</strong>";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
    }
    $json_data['data']=$array;
    $json_data['draw']=5;
    $json_data['recordsTotal']=sizeof($array);
    $json_data['recordsFiltered']=sizeof($array);
    echo json_encode($json_data);  // send data as json format
  }
  public function create_opening_stock(){

    // Insert  Cart Items
    $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;
      }
    }

    foreach ($newArrayitems as $row) {
      if($row['quantity']>0){
        $stock_data['stock_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('date'))));
        $stock_data['location_id'] = $this->security->xss_clean($this->input->post('location'));
        $stock_data['stock_remarks'] = $this->security->xss_clean($this->input->post('remarks'));
        $stock_data['stock_name'] = $row['material_name'];
        $stock_data['material_id'] = $row['material_id'];
        $stock_data['unit'] = $row['unit_id'];
        $stock_data['unit_price'] = $row['unit_price'];
        $stock_data['total_opening_qty'] = $row['quantity'];
        $stock_data['stock_description'] = $row['description'];
        $stock_data['created_date'] = date('Y-m-d H:i:s');
        $stock_data['created_by'] = $this->session->userdata('user_id');

        $result = $this->Stock->insert_stock_register($stock_data);
        $this->stock_management->calculate_stock_balance($result['insert_id']);
      }
    }
    if(isset($result) && $result['status']==1){
      $flash_data['status'] = 1;
      $flash_data['flashdata_msg'] = 'Opening Stock Saved Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    }else{
      $flash_data['status'] = 0;
      $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);  // send data as json format
  }
  public function update_opening_stock()
  {
    $data['stock_id'] = $this->security->xss_clean($this->input->post('stock_id'));
    $data['unit_price'] = $this->security->xss_clean($this->input->post('unit_price'));
    $data['total_opening_qty'] = $this->security->xss_clean($this->input->post('quantity'));
    $result = $this->Stock->update_stock_register($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $result2 = $this->stock_management->calculate_stock_balance($data['stock_id']);
      $flash_data['flashdata_msg'] = 'Stock Opening Entry Updated Successfully!';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    } else {
      $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 delete_opening_stock()
  {
    $data['stock_id'] = $this->security->xss_clean($this->input->post('stock_id'));
    $data['total_opening_qty'] = 0;
    $result = $this->Stock->update_stock_register($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $result2 = $this->stock_management->calculate_stock_balance($data['stock_id']);
      $flash_data['flashdata_msg'] = 'Stock Opening Entry Deleted Successfully!';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    } else {
      $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 delete_consumption()
  {
    $flash_data = array();
    $flash_data['status'] = 0;
    $cons_ids = explode(',',$this->input->post('cons_ids'));
    foreach ($cons_ids as $key => $value) {
      $data['cons_id'] = $value;
      $data['delete_status'] = 1;
      // Get Consumption Details
      $consumption_data = $this->Stock->select_stock_consumption('sc.*', array("sc.cons_id"=>$data['cons_id']),'', 'all');
      if($consumption_data->num_rows()==1){
        $result = $this->Stock->update_stock_consumption($data);
        $flash_data['status'] = $result;
        //  Update Stock Register
        $result2 = $this->stock_management->calculate_stock_balance($consumption_data->row()->stock_id);
      }
    }
    if ($result == 1) {
      $flash_data['flashdata_msg'] = 'Consumption Deleted Successfully!';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    } else {
      $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 select_stock_report_branches($page_data_type = "")
  {

    $json_data = array();
    $j = 0;
    $sl_no = 0;
    $branch_data = $material_data = $array = array();
    if (isset($_POST['branch']) && $_POST['branch'] != '' && $_POST['branch'] != 'all') {
      $branch_data['branch_id'] = $_POST['branch'];
    }
    if (isset($_POST['material_id']) && $_POST['material_id'] != '' && $_POST['material_id'] != 'all') {
      $material_data['m.material_id'] = $_POST['material_id'];
    }
    if (isset($_POST['material_category']) && $_POST['material_category'] != '' && $_POST['material_category'] != 'all') {
      $material_data['m.category'] = $_POST['material_category'];
    }
    $material_data['m.material_branch_id'] = 0;
    $branches  = $this->Branches->select_branches("*",$branch_data)->result();
    $materials = $this->Material->select_material('*',$material_data)->result();
    foreach ($branches as $row) :
      foreach ($materials as $key => $value) {

        $stock_data['sr.material_id'] = $value->material_id;
        $stock_data['sl.location_branch_id'] = $row->branch_id;
        $available_stock_qty = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty),0) as total", $stock_data)->row()->total, 2);
        if ((isset($_POST['min_qty']) && $_POST['min_qty'] != '' && $available_stock_qty<=$_POST['min_qty']) || (isset($_POST['min_qty']) && $_POST['min_qty'] == '') || (!isset($_POST['min_qty']))) {
          $sl_no++;
          $array[$j][] = $sl_no;
          $array[$j][] = $row->branch_name;
          $array[$j][] = $value->material_name;
          $array[$j][] = $value->unit_name;
          $array[$j][] = $available_stock_qty;

          $j++;
        }

      }
    endforeach;

    $json_data['data'] = $array;
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = sizeof($array);
    $json_data['recordsFiltered'] = sizeof($array);
    echo json_encode($json_data);  // send data as json format
  }
  public function select_material_inward_register(){

    $json_data=$array=array();
    $j=$sl_no=0;
    $total_amount=$total_qty=$total_without_tax=$total_tax_amount=0;
    $purchase_where=$transfer_where=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]));
      $purchase_where["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') <="]= $end_date;
      $purchase_where["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') >="]= $start_date;
      $transfer_where["STR_TO_DATE(st.transfer_date,'%Y-%m-%d') <="]= $end_date;
      $transfer_where["STR_TO_DATE(st.transfer_date,'%Y-%m-%d') >="]= $start_date;
    }
    if(isset($_POST['material']) && $_POST['material']!='' && $_POST['material']!='all'){
      $purchase_where['m.material_id'] = $transfer_where['sr1.material_id'] = $_POST['material'];
    }
    if(isset($_POST['material_category']) && $_POST['material_category']!='' && $_POST['material_category']!='all'){
      $purchase_where['m.category'] = $transfer_where['m.category'] = $_POST['material_category'];
    }
    if(isset($_POST['supplier']) && $_POST['supplier']!='' && $_POST['supplier']!='all'){
      $purchase_where['s.supplier_id'] = $_POST['supplier'];
    }
    if(isset($_POST['supplier_category']) && $_POST['supplier_category']!='' && $_POST['supplier_category']!='all'){
      $purchase_where['s.supplier_category'] = $_POST['supplier_category'];
    }
    if(isset($_POST['unit_id']) && $_POST['unit_id']!='' && $_POST['unit_id']!='all'){
      $purchase_where['pi.unit_id'] = $transfer_where['m.unit_id'] = $_POST['unit_id'];
    }
    if(isset($_POST['unit_price']) && $_POST['unit_price']!='' && $_POST['unit_price']!='all'){
      $purchase_where['pi.unit_price like'] = $_POST['unit_price'];
    }
    if(isset($_POST['location_id']) && $_POST['location_id']!='' && $_POST['location_id']!='all'){
      $purchase_where['p.location_id'] = $transfer_where['st.transferred_to_loc_id'] = $_POST['location_id'];
    }
    if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
      $purchase_where['sl.work_id'] = $transfer_where['sl2.work_id'] = $_POST['work'];
    }
    if(isset($_POST['stage_id']) && $_POST['stage_id']!='' && $_POST['stage_id']!='all'){
      $purchase_where['p.project_stage_id'] = $_POST['stage_id'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $purchase_where['p.created_by'] = $transfer_where['st.created_by'] = $_POST['user_id'];
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $purchase_where['p.purchase_date >='] = $transfer_where['st.transfer_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $purchase_where['p.purchase_date <='] = $transfer_where['st.transfer_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    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($this->config->item('user_data_access_view_all')!=1){
      $purchase_where['p.created_by'] = $transfer_where['st.created_by'] = $this->session->userdata('user_id');
    }
    $purchased_materials = $this->Purchases->select_purchase_items("pi.*,p.vehicle_no,p.remarks,s.supplier_id,s.supplier_name,p.purchase_date,m.material_name,mu.unit_name,u.user_alias,p.created_date,p.location_id,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,tt.tax_percentage,tt.tax1_percent,tt.tax2_percent,tt.tax3_percent",$purchase_where);
    foreach($purchased_materials->result() as $row):

      $location_name = $row->location_name;
      if($row->location_type=='work_site'){
        $location_name = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id . '">' . $row->location_name . '<br/>[Work Site]</a>';
      }elseif($row->location_type=='ware_house'){
        $location_name = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '<br/>[Warehouse]</a>';
      }

      $sl_no++;

      $array[$j][]=$sl_no;
      $array[$j][]='Purchase';
      $array[$j][]=date('d-m-Y',strtotime($row->purchase_date));
      $array[$j][]='<a href="' . base_url() . 'purchases/profile/' . $row->purchase_id . '">#' . $row->purchase_id . '</a>';
      $array[$j][]='<a href="' . base_url() . 'supplier/profile/' . $row->supplier_id . '">' . $row->supplier_name . '</a>';
      $array[$j][]=$location_name;
      $array[$j][]=$row->material_name;
      $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->quantity,$this->config->item('Currency_currency_shortname'));
      $array[$j][]=$row->unit_name;
      $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($row->unit_price,$this->config->item('Currency_currency_shortname'));
      $array[$j][]=$row->tax_percentage."%";
      $array[$j][]=number_format($row->tax_amount,$this->config->item('Currency_amount_decimals_2'),'.','');
      $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($row->total_amount-$row->tax_amount),$this->config->item('Currency_currency_shortname'));
      $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($row->total_amount,$this->config->item('Currency_currency_shortname'));
      $array[$j][]=$row->description;
      $array[$j][]=$row->remarks;
      $array[$j][] = $row->vehicle_no;
      $array[$j][]=$row->user_alias;
      if ($this->config->item('UI_hide_created_date_field')==0){
        $array[$j][]=date('d-m-Y',strtotime($row->created_date));
      }else{
        $array[$j][] = '';
      }

      $total_amount=$total_amount+$row->total_amount;
      $total_qty+=$row->quantity;
      $total_without_tax+=$row->total_amount-$row->tax_amount;
      $total_tax_amount+=$row->tax_amount;

      $j++;
    endforeach;

    if(isset($_POST['supplier']) && $_POST['supplier']=='all' && isset($_POST['supplier_category']) && $_POST['supplier_category']=='all'){
      // Select All Available Materials in Stock
      $transfer  = $this->Stock->select_stock_transfers("COALESCE(SUM(st.transferred_qty),0) as total_transferred_qty,COALESCE(SUM(st.transferred_qty*sr2.unit_price),0) as total_transferred_amt,st.*,sr1.unit_price,sr1.material_id,m.material_name,mu.unit_name,sr1.unit,sl1.location_name as transferred_from_loc_name,sl2.location_name as transferred_to_loc_name,u.user_alias as created_user,u.user_role as created_user_role_id,sl1.location_type as location_type_from,sl2.location_type as location_type_to,sl1.work_id as location_work_id_from,sl2.work_id as location_work_id_to ", $transfer_where, array("st.transfer_date", "st.trans_group_id", "sr1.material_id", "sr1.unit"));
      foreach ($transfer->result() as $row) :

        if ($row->transfer_id != '') {
          $stock_amount = round($row->total_transferred_amt, $this->config->item('Currency_amount_decimals_2'));
          $total_amount += $row->total_transferred_amt;

          $bill_data = $this->Stock->select_stock_transfer_bill('stb.*,v.vehicle_no',array("stb.bill_id"=>$row->transfer_bill_id))->row();

          if($row->location_type_from=='work_site'){
            $location_name_from = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id_from . '">' . $row->transferred_from_loc_name . '<br/>[Work Site]</a>';
          }elseif($row->location_type_from=='ware_house'){
            $location_name_from = '<a href="' . base_url() . 'stock/locations/profile/' . $row->transferred_from_loc_id . '">' . $row->transferred_from_loc_name . '<br/>[Warehouse]</a>';
          }

          if($row->location_type_to=='work_site'){
            $location_name_to = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id_to . '">' . $row->transferred_to_loc_name . '<br/>[Work Site]</a>';
          }elseif($row->location_type_to=='ware_house'){
            $location_name_to = '<a href="' . base_url() . 'stock/locations/profile/' . $row->transferred_to_loc_id . '">' . $row->transferred_to_loc_name . '<br/>[Warehouse]</a>';
          }

          $sl_no++;
          $array[$j][] = $sl_no;
          $array[$j][] = 'Transfer In';
          $array[$j][] = date('d-m-Y',strtotime($row->transfer_date));
          $array[$j][] = '<a href="'.base_url().'stock/transfer_bill/'.$row->transfer_bill_id.'">'."#".$row->transfer_bill_id.'</a>';
          $array[$j][] = $location_name_from;
          $array[$j][] = $location_name_to;
          $array[$j][] = $row->material_name;
          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->total_transferred_qty, $this->config->item('Currency_currency_shortname'));
          $array[$j][] = $row->unit_name;
          $array[$j][] = $this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($stock_amount/$row->total_transferred_qty), $this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('currency_format')->formatCurrency(0, $this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('currency_format')->formatCurrency(0, $this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($stock_amount, $this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($stock_amount, $this->config->item('Currency_currency_shortname'));
          $array[$j][] = '';
          $array[$j][] = $row->remarks;
          $array[$j][] = (isset($bill_data->vehicle_no))?$bill_data->vehicle_no:"";
          $array[$j][] = $row->created_user;
          if ($this->config->item('UI_hide_created_date_field')==0){
            $array[$j][] = date('d-m-Y', strtotime($row->created_date));
          }else{
            $array[$j][] = '';
          }
          $j++;

          $total_qty+=$row->total_transferred_qty;
          $total_without_tax+=$stock_amount;
          $total_tax_amount+=0;
        }

      endforeach;
    }

    if(sizeof($array)>0){
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]="<strong>Total</strong>";
      $array[$j][]="<strong>".$this->config->item('currency_format')->formatCurrency($total_qty,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]='';
      $array[$j][]="";
      $array[$j][]="";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_tax_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_without_tax,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="";
      $array[$j][]="";
      $array[$j][]="";
      $array[$j][]="";
      $array[$j][]="";
    }
    $json_data['draw']=5;
    $json_data['recordsTotal']=sizeof($array);
    $json_data['recordsFiltered']=sizeof($array);
    $json_data['data']=$array;
    echo json_encode($json_data);  // send data as json format
  }
}
