
<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Purchase_order 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('Purchase_bill_model', 'Purchases');
    $this->load->model('Purchase_order_model', 'Purchase_order');
    $this->load->model('Purchase_request_model', 'Purchase_request');
    $this->load->model('Material_model', 'Material');
    $this->load->model('Stock_model', 'Stock');
    $this->load->model('Account_model', 'Account');
    $this->load->model('Settings_model', 'Settings');
    $this->load->library('Stock_management','stock_management');
    $this->load->library('Bills_management','bills_management');
    $this->load->model('Labour_model', 'Labour');
    $this->load->library('Projects_management','projects_management');
    $this->load->library('Accounts_management', 'accounts_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');
    }
    $company_details = $this->Settings->select_settings('*',array("settings_category"=>"Company Details"))->result();
    foreach ($company_details as $key => $value) {
      $this->page_data[$value->settings_name] = $value->value;
    }
    //        File Directory
    $this->page_data['directory'] = "purchases_order";
    $this->page_data['supplier_categories'] = $this->Supplier->select_supplier_category()->result();
    $user_accounts_where = "ur.role_name='supervisor' or ur.role_name='general_manager'";
    $this->page_data['user_accounts'] = $this->Account->select_account_heads("",$user_accounts_where,"user")->result();
    if($this->config->item('Labour_group_member_payment')==0){
      $this->db->where('(l.labour_type=0 or (l.labour_type=1 and group_position=1))');
    }
    $this->page_data['labours_2'] = $this->Labour->select_labours()->result();
    $this->page_data['terms_and_condition_templates'] = $this->Settings->select_terms_and_condition_templates('',array("terms_type"=>'Purchase Order'))->result();
    $this->page_data['billing_address'] = $this->Settings->select_billing_address("")->result();
  }
  public function index()
  {
    $this->view();
  }
  public function payments()
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_purchase_order')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    $this->page_data['page_name'] = 'purchase_order_payments';

    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['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->load->view('Index',$this->page_data);
  }
  public function view($param='')
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_purchase_order')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    $this->page_data['material_list'] = $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['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['list_type'] = $param;
    $this->page_data['page_name'] = 'purchase_order_view';
    $this->load->view('Index',$this->page_data);
  }
  public function materials()
  {
    $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['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['page_name'] = 'ordered_materials';
    $this->load->view('Index',$this->page_data);
  }

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

    if($id!=''){
      $this->page_data['order_data'] = $this->Purchase_order->select_purchase_order("po.*,po.created_by as order_created_by", array("po.order_id" =>$id ),$this->config->item('select_data_entry_approval'));
      if($this->page_data['order_data']->num_rows()==1 && $this->page_data['order_data']->row()->order_created_by == $this->session->userdata('user_id') || $this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='general_manager') {
        $this->page_data['order_data'] = $this->page_data['order_data']->row();

        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['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
        $this->page_data['material_list'] = $this->Material->select_material()->result();
        $this->page_data['measure_units'] = $this->Material->select_unit()->result();
        $this->page_data['order_items'] = $this->Purchase_order->select_purchase_order_items("*,poi.tax_amount as item_tax_amount", array("poi.order_id" =>$id ))->result();

        // Editing Disabled For Purchase Bill Created
        if($this->page_data['order_data']->purchase_bill_id==0 || $this->session->userdata('user_role')=='admin'){
          $this->page_data['page_name'] = 'purchase_order_edit';
        }else{
          $flash_data['flashdata_msg'] = 'Sorry.. Purchase Order Editing Option is Disabled After Purchase Bill is Created!';
          $flash_data['flashdata_type'] = 'warning';
          $flash_data['alert_type'] = 'warning';
          $flash_data['flashdata_title'] = 'Editing Disabled !!';
          $this->session->set_flashdata($flash_data);
          redirect(base_url(). 'purchase_order/profile/'.$id,'refresh');
        }
        $this->load->view('Index',$this->page_data);
      }else{
        show_404();
      }
    }else{
      show_404();
    }
  }
  public function profile($id="",$view_type='')
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_purchase_order')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    if($id!=''){
      $this->page_data['order_data'] = $this->Purchase_order->select_purchase_order("po.*,s.supplier_name,s.supplier_address,s.supplier_phone,s.supplier_email,sl.location_name,u.user_alias as order_created_user,po.created_by as order_created_by,u1.user_alias as approved_1_user,u2.user_alias as approved_2_user,po.created_date as order_created_date,s.tax_no as supplier_tax_no,wc.work_location", array("po.order_id" =>$id ),$this->config->item('select_data_entry_approval'));
      if($this->page_data['order_data']->num_rows()==1){
        $this->page_data['order_data'] = $this->page_data['order_data']->row();
        $this->page_data['order_data']->billing_address = $this->remove_empty_lines($this->page_data['order_data']->billing_address);
        $this->page_data['order_data']->total_amount_in_words = $this->accounts_management->amount_in_words($this->page_data['order_data']->total_est_amount);

        $this->page_data['purchase_bill_payments'] = $this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,u1.user_alias as created_user",array("p.purchase_order_id" => $id, "ats.reference_type" => 'purchase_bill',"ats.tr_payment_part" => 0,"p.delete_status" => 0),'purchase_bill')->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();

        $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
        $this->page_data['material_list'] = $this->Material->select_material()->result();
        $this->page_data['measure_units'] = $this->Material->select_unit()->result();
        $this->page_data['delivery_challan'] = $this->Purchases->select_delivery_challan("pdc.*",array("pdc.order_id"=>$id))->result();

        $this->page_data['order_items'] = $this->Purchase_order->select_purchase_order_items("*,poi.tax_amount as item_tax_amount", array("poi.order_id" =>$id ))->result();
        foreach ($this->page_data['order_items'] as $key => $value) {
          // Get Total Received Quantity
          $value->total_delivery_challan_qty = $this->Purchases->select_delivery_challan_items("COALESCE(SUM(pdci.delivery_qty),0) as total", array("pdci.order_item_id" =>$value->item_id))->row()->total;
          $value->received_qty = $this->Purchases->select_purchase_items("COALESCE(SUM(pi.quantity),0) as total", array("p.purchase_order_id" =>$id,"pi.material_id"=>$value->material_id))->row()->total;
        }
        $this->page_data['order_payments'] = $this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,u1.user_alias as created_user", array("ats.reference_id" => $id, "ats.reference_type" => 'purchase_order'),'purchase_order','','','',1,'all')->result();
        $this->page_data['advance_return_payments'] = $this->Account->select_advance_return_transaction("art.*,u.user_alias as created_user",array("art.reference_id" => $id,"art.tr_type" => 'purchase_order_transaction',"art.reference_type" => 'purchase_order'),'','','',1,'all')->result();
        $this->page_data['total_order_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $id, "ats.reference_type" => 'purchase_order'),'purchase_order','','','',1,'all')->row()->total_paid_amount;
        $this->page_data['total_bill_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("p.purchase_order_id" => $id, "ats.reference_type" => 'purchase_bill',"ats.tr_payment_part" => 0),'purchase_bill','','','',1,'all','all')->row()->total_paid_amount;
        $this->page_data['total_supplier_advance_payments'] = $this->Account->select_advance_return_transaction("COALESCE(SUM(art.tr_amount),0) as total_amount",array("art.reference_id" => $id,"art.tr_type" => 'purchase_order_transaction',"art.reference_type" => 'purchase_order'),'','','','',1,'all')->row()->total_amount;
        if($view_type=='print'){
          $this->load->view('modules/purchases_order/purchase_order_print_'.$this->config->item('Purchase_order_template'),$this->page_data);
        }else{
          $this->page_data['page_name'] = 'purchase_order_profile_'.$this->config->item('Purchase_order_template');
          $this->load->view('Index',$this->page_data);
        }

      }else{
        show_404();
      }
    }else{
      show_404();
    }
  }
  public function add($work_id="")
  {
    $this->page_data['previous_order_id'] = "";
    $previous_order_id = $this->Purchase_order->select_purchase_order("max(po.order_id) as order_id");
    if($previous_order_id->num_rows()==1){
      $this->page_data['previous_order_id'] = $previous_order_id->row()->order_id;
    }

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

    $this->page_data['page_name'] = 'purchase_order_add';

    if($work_id!=0){
      $this->page_data['location_data'] = $this->Stock->select_stock_locations("*", array("sl.work_id" =>$work_id));
    }
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, 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['purchase_request_id'] = '';
    if(isset($_GET['request_id']) && $_GET['request_id']!=''){
      //        Select Request Details
      $request_where["pr.request_id"] = $_GET['request_id'];
      $request_where["pr.approval_status_1"] = 1;
      if($this->config->item('Purchase_purchase_request_second_approval')==1){
        $request_where["pr.approval_status_2"] = 1;
      }
      $this->page_data['purchase_request_data'] = $this->Purchase_request->select_purchase_request("pr.*,u.user_alias,sl.location_name",$request_where);
      if($this->page_data['purchase_request_data']->num_rows()==1){
        $this->page_data['purchase_request_data'] = $this->page_data['purchase_request_data']->row();
        $this->page_data['purchase_request_id'] = $this->page_data['purchase_request_data']->request_id;
        $this->page_data['purchase_request_items'] = $this->Purchase_request->select_purchase_request_items("", array("pri.request_id" =>$this->page_data['purchase_request_id']))->result();
        foreach ($this->page_data['purchase_request_items'] as $key => $value) {
          $value->received_qty = 0;
        }
      }
    }

    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();

    $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['material_list'] = $this->Material->select_material()->result();
    $this->page_data['measure_units'] = $this->Material->select_unit()->result();
    $this->page_data['purchase_data'] = $this->Purchases->select_purchase()->result();
    $this->load->view('Index',$this->page_data);

  }
  public function create(){

    $data['order_type'] = $this->security->xss_clean($this->input->post('purchase_type'));
    $data['request_id'] = $this->security->xss_clean($this->input->post('purchase_request_id'));
    $data['location_id'] = $this->security->xss_clean($this->input->post('location'));
    $data['supplier_id'] = $this->security->xss_clean($this->input->post('supplier'));
    $data['order_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('order_date'))));
    if($this->input->post('delivery_date_time')!=''){
      $data['delivery_date'] = date('Y-m-d H:i:s',strtotime($this->security->xss_clean($this->input->post('delivery_date_time'))));
    }

    $po_where = array();
    $po_where['po.order_date >='] = date('Y',strtotime($data['order_date'])).'-01-01';
    $po_where['po.order_date <='] = date('Y',strtotime($data['order_date'])).'-12-31';
    $last_data = $this->Purchase_order->select_purchase_order("max(po.order_no) as max_order_no",$po_where);
    if($last_data->num_rows()==1){
      $data['order_no'] = ($last_data->row()->max_order_no)+1;
    }else{
      $data['order_no'] = 1;
    }
    $data['vender_no'] = $this->security->xss_clean($this->input->post('vendor_no'));
    $data['expense_code'] = $this->security->xss_clean($this->input->post('expense_code'));
    $data['contact_person_name'] = $this->security->xss_clean($this->input->post('contact_person'));
    $data['contact_person_phone'] = $this->security->xss_clean($this->input->post('contact_person_phone'));
    $data['reference_no'] = $this->security->xss_clean($this->input->post('order_reference_no'));
    $data['remarks'] = $this->security->xss_clean($this->input->post('remarks'));
    $data['vehicle_no'] = $this->security->xss_clean($this->input->post('vehicle_no'));
    $data['wbs'] = $this->security->xss_clean($this->input->post('wbs'));
    $data['service'] = $this->security->xss_clean($this->input->post('service'));
    $data['requiredby'] = $this->security->xss_clean($this->input->post('required_by'));
    $data['delivery_mode'] = $this->security->xss_clean($this->input->post('delivery_mode'));
    $data['payment_terms'] = $this->security->xss_clean($this->input->post('payment_terms'));
    $data['taxable_amount'] = $data['total_est_amount'] = $this->security->xss_clean($this->input->post('order_total_amount'));
    if($this->config->item('Tax_tax_enabled')==1 && $this->config->item('Tax_purchase_tax_type')=='item_wise' && $this->config->item('Purchase_order_tax_enabled')==1){
      $data['tax_amount'] = $this->security->xss_clean($this->input->post('order_tax_amount'));
      $data['taxable_amount'] = $this->security->xss_clean($this->input->post('order_taxable_amount'));
    }
    $data['billing_address'] = $this->security->xss_clean($this->input->post('billing_address'));
    $data['billing_address_id'] = $this->security->xss_clean($this->input->post('billing_address_id'));
    $data['delivery_address'] = $this->security->xss_clean($this->input->post('delivery_address'));
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');
    if($this->config->item('purchase_order_suffix')=='/d/m/Y'){
      $data['orde_no_suffix'] = "/".date('d/m/Y',strtotime($this->security->xss_clean($this->input->post('order_date'))));
    }
    if($this->config->item('User_enable_data_entry_approval')==1 && $this->session->userdata('user_role_id')>2){
      $data['entry_approval_status'] = 0;
    }

    //terms and conditions
    $terms_array=$newArrayterms=array();
    if($this->input->post('terms')){
      $terms_array = $this->input->post('terms');
      $terms_array_length = sizeof($terms_array);
      foreach (array_keys($terms_array) as $fieldKey) {
        foreach ($terms_array[$fieldKey] as $key=>$value) {
          $newArrayterms[$key][$fieldKey] = $value;
        }
      }
      $i = 0;
      foreach ($newArrayterms as $row) {
        $terms_new[$i]['title'] = $row['title'];
        $terms_new[$i]['description'] = $row['description'];
        $i++;
      }
      $data['terms_and_conditions'] = serialize($terms_new);
    }else{
      $data['terms_and_conditions'] = '';
    }
    //      File Validations
    $file_error = 0;
    if (isset($_FILES['uploaded_file']) && $_FILES['uploaded_file']["size"] > 0) {
      //      File Upload
      $config['upload_path']   = 'uploads/bills/';
      $config['allowed_types'] = 'jpg|jpeg|png|pdf|doc|docx|xls|xl';
      $config['max_size']      = 3000;
      // $config['max_width']     = 1980;
      // $config['max_height']    = 1080;
      $config['encrypt_name']    = TRUE;
      // $config['file_name'] = date('YmdHis');
      $this->load->library('upload', $config);
      if (!$this->upload->do_upload('uploaded_file')) {
        $error = array('error' => $this->upload->display_errors());
        $file_error = 1;
        $flash_data['flashdata_msg'] = $error['error'];
        $flash_data['flashdata_type'] = "warning";
        $flash_data['flashdata_title'] = "Uploaded File Error";
        $flash_data['status'] = 2;
      }else {
        $file_data = $this->upload->data();
        $data['order_attachment'] = $file_data['file_name'];
      }
    }


    $location_data=$this->Stock->select_stock_locations("sl.work_id",array("sl.location_id"=>$data['location_id']));
    if($location_data->num_rows()==1){
      $transaction_data['tr_project_stage_id'] = $data['project_stage_id'] = $this->projects_management->get_project_stage_id($location_data->row()->work_id);
    }

    if($file_error==0){
      $result = $this->Purchase_order->insert_purchase_order($data);
      $flash_data['status'] = $result['status'];
      if($result['status']==1){
        $item_data['order_id'] = $order_data['order_id'] = $return_data['reference_id'] = $transaction_data['reference_id'] = $result['insert_id'];
        $transaction_data['reference_type'] = "purchase_order";
        $transaction_data['created_date'] = date('Y-m-d H:i:s');
        $transaction_data['created_by'] = $this->session->userdata('user_id');
        // 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) {
          $item_data['material_id'] = $row['material_id'];
          $item_data['quantity'] = $row['quantity'];
          $item_data['unit_id'] = $row['unit_id'];
          $item_data['unit_price'] = $row['unit_price'];
          $item_data['total_amount'] = $row['total_amount'];
          if(isset($row['description'])){
            $item_data['description'] = $row['description'];
          }
          $item_data['item_code'] = $row['item_code'];

          if($this->config->item('Tax_tax_enabled')==1 && $this->config->item('Tax_purchase_tax_type')=='item_wise' && isset($row['tax_amount'])  && $this->config->item('Purchase_order_tax_enabled')==1){
            $item_data['tax_percent'] = $row['tax_percent'];
            $item_data['tax_amount'] = $row['tax_amount'];
          }
          // $item_data['unit_price'] = $item_data['total_amount']/$item_data['quantity'];
          $item_result = $this->Purchase_order->create_purchase_order_item($item_data);
        }
        // Insert Payment if Exist
        if($this->input->post('order_paid_amount')>0) {

          $transaction_data['tr_payment_part'] = 0; //Order Payment
          $transaction_data['tr_amount'] = $this->security->xss_clean($this->input->post('order_paid_amount'));
          $transaction_data['tr_date'] = date('Y-m-d', strtotime($this->security->xss_clean($this->input->post('paid_date'))));
          $transaction_data['tr_debit_acc'] = $this->security->xss_clean($this->input->post('debit_acc'));
          $transaction_data['tr_credit_acc'] = 6;//Purchase Order
          $transaction_data['tr_description'] = "Purchase Order Transaction";
          $transaction_data['tr_type'] = "purchase_order_transaction";
          $transaction_data['tr_mode'] = $this->security->xss_clean($this->input->post('payment_mode'));
          $transaction_data['tr_reference_no'] = $this->security->xss_clean($this->input->post('reference_no'));


          if($this->input->post('reference_date')!=''){
            $transaction_data['tr_reference_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('reference_date'))));
          }
          $transaction_data['tr_remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
          $transaction_result = $this->Account->create_account_transaction($transaction_data);
        }

        //advance return
        $payfrombalanceIsTrue = $this->input->post('payfrombalanceIsTrue');

        if($payfrombalanceIsTrue==1){
          $return_data['tr_amount'] = $this->security->xss_clean($this->input->post('advance_payment_amount'));
          $return_data['created_date'] = date('Y-m-d H:i:s');
          $return_data['supplier_id'] = $data['supplier_id'];//Supplier Head
          $return_data['tr_type'] = "purchase_order_transaction";
          $return_data['reference_type'] = 'purchase_order';
          $return_data['tr_date'] =  date('Y-m-d H:i:s');
          $return_data['created_by'] = $this->session->userdata('user_id');
          if($return_data['tr_amount']>0){
            $return_result = $this->Account->create_advance_return_transaction($return_data);
          }
        }

        $flash_data['flashdata_msg'] = 'Purchase Order Saved Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
        $flash_data['order_id'] = $order_data['order_id'];

        $order_data_2 = $this->Purchase_order->select_purchase_order("sl.work_id,sl.location_type,po.request_id",array("po.order_id"=>$order_data['order_id']),'all')->row();
        $flash_data['location_type']=$order_data_2->location_type;
        $flash_data['work_id']=$order_data_2->work_id;

        if($order_data_2->request_id!=0){
          $this->bills_management->update_purchase_request_closed_status($order_data_2->request_id);
        }
      }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);  // send data as json format
  }

  public function update(){

    $data['order_id'] = $this->security->xss_clean($this->input->post('order_id'));
    $data['location_id'] = $this->security->xss_clean($this->input->post('location'));
    $data['supplier_id'] = $this->security->xss_clean($this->input->post('supplier'));
    $data['order_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('order_date'))));
    if($this->config->item('purchase_order_suffix')=='/d/m/Y'){
      $data['orde_no_suffix'] = "/".date('d/m/Y',strtotime($this->security->xss_clean($this->input->post('order_date'))));
    }
    $data['reference_no'] = $this->security->xss_clean($this->input->post('order_reference_no'));
    $data['remarks'] = $this->security->xss_clean($this->input->post('remarks'));
    $data['vehicle_no'] = $this->security->xss_clean($this->input->post('vehicle_no'));
    $data['taxable_amount'] = $data['total_est_amount'] = $this->security->xss_clean($this->input->post('order_total_amount'));
    if($this->config->item('Tax_tax_enabled')==1 && $this->config->item('Tax_purchase_tax_type')=='item_wise' && $this->config->item('Purchase_order_tax_enabled')==1){
      $data['tax_amount'] = $this->security->xss_clean($this->input->post('order_tax_amount'));
      $data['taxable_amount'] = $this->security->xss_clean($this->input->post('order_taxable_amount'));
    }
    if($this->input->post('delivery_date_time')!=''){
      $data['delivery_date'] = date('Y-m-d H:i:s',strtotime($this->security->xss_clean($this->input->post('delivery_date_time'))));
    }else{
      $data['delivery_date'] = '0000-00-00 00:00:00';
    }
    $data['vender_no'] = $this->security->xss_clean($this->input->post('vendor_no'));
    $data['expense_code'] = $this->security->xss_clean($this->input->post('expense_code'));
    $data['contact_person_name'] = $this->security->xss_clean($this->input->post('contact_person'));
    $data['contact_person_phone'] = $this->security->xss_clean($this->input->post('contact_person_phone'));
    $data['billing_address'] = $this->security->xss_clean($this->input->post('billing_address'));
    $data['billing_address_id'] = $this->security->xss_clean($this->input->post('billing_address_id'));
    $data['delivery_address'] = $this->security->xss_clean($this->input->post('delivery_address'));
    $data['wbs'] = $this->security->xss_clean($this->input->post('wbs'));
    $data['service'] = $this->security->xss_clean($this->input->post('service'));
    $data['requiredby'] = $this->security->xss_clean($this->input->post('required_by'));
    $data['delivery_mode'] = $this->security->xss_clean($this->input->post('delivery_mode'));
    $data['payment_terms'] = $this->security->xss_clean($this->input->post('payment_terms'));
    $data['updated_date'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('user_id');

    //terms and conditions
    $terms_array=$newArrayterms=array();
    if($this->input->post('terms')){
      $terms_array = $this->input->post('terms');
      $terms_array_length = sizeof($terms_array);
      foreach (array_keys($terms_array) as $fieldKey) {
        foreach ($terms_array[$fieldKey] as $key=>$value) {
          $newArrayterms[$key][$fieldKey] = $value;
        }
      }
      $i = 0;
      foreach ($newArrayterms as $row) {
        $terms_new[$i]['title'] = $row['title'];
        $terms_new[$i]['description'] = $row['description'];
        $i++;
      }
      $data['terms_and_conditions'] = serialize($terms_new);
    }else{
      $data['terms_and_conditions'] = '';
    }

    //      File Validations
    $file_error = 0;
    if (isset($_FILES['uploaded_file']) && $_FILES['uploaded_file']["size"] > 0) {
      //      File Upload
      $config['upload_path']   = 'uploads/bills/';
      $config['allowed_types'] = 'jpg|jpeg|png|pdf|doc|docx|xls|xl';
      $config['max_size']      = 3000;
      // $config['max_width']     = 1980;
      // $config['max_height']    = 1080;
      $config['encrypt_name']    = TRUE;
      // $config['file_name'] = date('YmdHis');
      $this->load->library('upload', $config);
      if (!$this->upload->do_upload('uploaded_file')) {
        $error = array('error' => $this->upload->display_errors());
        $file_error = 1;
        $flash_data['flashdata_msg'] = $error['error'];
        $flash_data['flashdata_type'] = "warning";
        $flash_data['flashdata_title'] = "Uploaded File Error";
        $flash_data['status'] = 2;
      }else {
        $file_data = $this->upload->data();
        $data['order_attachment'] = $file_data['file_name'];
      }
    }else{
      // Check Existing Attachment Deleted
      if($this->input->post('attachment_delete_status')==1){
        $data['order_attachment'] = "";
      }
    }
    if($file_error==0){
      $result = $this->Purchase_order->update_purchase_order($data);
      $flash_data['status'] = $result;
      if($result==1){
        $item_data['order_id'] = $order_data['order_id'] = $data['order_id'];

        // Insert/Update 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) {
          $item_data=array();
          $item_data['material_id'] = $row['material_id'];
          $item_data['quantity'] = $row['quantity'];
          $item_data['unit_id'] = $row['unit_id'];
          $item_data['unit_price'] = $row['unit_price'];
          $item_data['total_amount'] = $row['total_amount'];
          if(isset($row['description'])){
            $item_data['description'] = $row['description'];
          }
          $item_data['item_code'] = $row['item_code'];

          if($this->config->item('Tax_tax_enabled')==1 && $this->config->item('Tax_purchase_tax_type')=='item_wise' && isset($row['tax_amount'])  && $this->config->item('Purchase_order_tax_enabled')==1){
            $item_data['tax_percent'] = $row['tax_percent'];
            $item_data['tax_amount'] = $row['tax_amount'];
          }
          // $item_data['unit_price'] = $item_data['total_amount']/$item_data['quantity'];
          if(isset($row['item_id']) && $row['item_id']!=''){
            // Update Existing Data
            $item_data['item_id'] = $row['item_id'];
            $item_result =  $this->Purchase_order->update_purchase_order_item($item_data);
          }else{
            // Create New Data
            $item_data['order_id'] =  $data['order_id'];
            $item_result =  $this->Purchase_order->create_purchase_order_item($item_data);
          }
        }

        // Check Items Deleted
        $deleted_items = $this->security->xss_clean($this->input->post('deleted_items'));
        if($deleted_items!=''){
          $items = explode(',',$deleted_items);
          foreach ($items as $key => $value) {
            $item_data=array();
            $item_data['delete_status'] = 1;
            $item_data['item_id'] = $value;
            $this->Purchase_order->update_purchase_order_item($item_data);
          }
        }

        $flash_data['flashdata_msg'] = 'Purchase Order Details Updated Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
        $flash_data['order_id'] = $order_data['order_id'];

        $order_data_2 = $this->Purchase_order->select_purchase_order("sl.work_id,sl.location_type,po.request_id",array("po.order_id"=>$order_data['order_id']))->row();
        $flash_data['location_type']=$order_data_2->location_type;
        $flash_data['work_id']=$order_data_2->work_id;
        if($order_data_2->request_id!=0){
          $this->bills_management->update_purchase_request_closed_status($order_data_2->request_id);
        }
      }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);  // send data as json format
  }
  public function approve_order(){

    $data['order_id'] = $this->security->xss_clean($this->input->post('order_id'));
    $order_data = $this->Purchase_order->select_purchase_order("po.*",array("po.order_id"=>$data['order_id']),'all')->row();
    if($this->input->post('approval_level')==1 && $order_data->approval_status_2==0){
      $data['approval_status_1'] = $this->security->xss_clean($this->input->post('approve_status'));
      $data['approved_by_1'] = $data['updated_by'] = $this->session->userdata('user_id');
      $data['approved_date_1'] = date('Y-m-d H:i:s');
      $result = $this->Purchase_order->update_purchase_order($data);
    }elseif($this->input->post('approval_level')==2 && $order_data->approval_status_1==1){
      $data['approval_status_2'] = $this->security->xss_clean($this->input->post('approve_status'));
      $data['approved_by_2'] = $data['updated_by'] = $this->session->userdata('user_id');
      $data['approved_date_2'] = date('Y-m-d H:i:s');
      $result = $this->Purchase_order->update_purchase_order($data);
    }
    if(isset($result)){
      $flash_data['status'] = $result;
      if($result==1){
        $flash_data['flashdata_msg'] = 'Purchase Order Approval Updated Successfully!.';
        $flash_data['flashdata_type'] = 'success';
        $flash_data['alert_type'] = 'success';
        $flash_data['flashdata_title'] = 'Success !';
        $flash_data['order_id'] = $data['order_id'];
      }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'] = 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 select_purchase_order_items(){

    $json_data=array();
    $j=0;
    $sl_no=0;
    $data=array();
    if(isset($_POST['purchase_order_id'])){
      $data['po.order_id'] = $_POST['purchase_order_id'];
    }
    $result = $this->Purchase_order->select_purchase_order_items("",$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):
      $sl_no++;
      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')) {
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="purchase_order_item_delete_btn" href="#purchase_order_item_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_edit='<a style="margin-left: 5px;margin-right: 5px" id="purchase_order_item_edit_btn" href="#purchase_order_item_edit_modal" data-toggle="modal" class="btn btn-primary m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="la la-edit"></i>
        </a>';
      }
      $array[$j][]=$sl_no;
      $array[$j][]=$row->item_id;
      $array[$j][]=$row->material_name;
      $array[$j][]=$row->quantity;
      $array[$j][]=$row->unit_name;
      $array[$j][]=number_format($row->total_amount,$this->config->item('Currency_amount_decimals_2'),'.','');
      $array[$j][]=$row->material_id;
      $array[$j][]=$row->unit_id;
      $array[$j][]=$btn_edit.$btn_delete;



      $j++;
    endforeach;

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

    $json_data=array();
    $j=0;
    $sl_no=0;
    $total_amount_paid=$total_est_amount=$grand_total_billed_amount=$grand_total_balance_to_bill=0;

    $data=array();
    $status = 'all';
    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(po.order_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(po.order_date,'%Y-%m-%d') >="]= $start_date;
    }
    if(isset($_POST['status']) && $_POST['status']!='' && $_POST['status']!='all'){
      $status = $_POST['status'];
    }
    if(isset($_POST['supplier']) && $_POST['supplier']!='' && $_POST['supplier']!='all'){
      $data['s.supplier_id'] = $_POST['supplier'];
    }
    if(isset($_POST['location']) && $_POST['location']!='' && $_POST['location']!='all'){
      $data['po.location_id'] = $_POST['location'];
    }
    if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
      $data['sl.work_id'] = $_POST['work'];
    }
    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['stage_id']) && $_POST['stage_id']!='' && $_POST['stage_id']!='all'){
      $data['po.project_stage_id'] = $_POST['stage_id'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['po.created_by'] = $_POST['user_id'];
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $data['po.order_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $data['po.order_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){
      $data['po.created_by'] = $this->session->userdata('user_id');
    }
    if($status=='approval_pending' && $this->config->item('Purchase_purchase_order_approval')==1){
      $this->db->where("(po.approval_status_1!=1 or po.approval_status_2!=1)");
    }
    if($status=='approved' && $this->config->item('Purchase_purchase_order_approval')==1){
      $this->db->where("(po.approval_status_1=1 and po.approval_status_2=1)");
    }
    $entry_approval = 1;
    if($this->config->item('User_enable_data_entry_approval')==1){//webapp users approval
      $entry_approval = 'all';
    }
    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'];
      }
    }
    $result = $this->Purchase_order->select_purchase_order("po.*,u.user_alias,s.supplier_name,s.supplier_id,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id,u.user_role as created_user_role_id,u1.user_alias as approved_1_user,u2.user_alias as approved_2_user",$data,$entry_approval);
    $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):
      $total_billed_amount = $balance_to_bill = 0;
      $bill_id_column= 15;//Column Where Bill ID Exist
      $btn_view='<a style="margin-left: 5px;margin-right: 5px"  href="'.base_url().'purchase_order/profile/'.$row->order_id.'" class="btn btn-primary m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
      <i class="la la-eye"></i>
      </a>';
      $btn_edit = $btn_delete = '';
      $btn_add_payment='';
      $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>';
      }
      $total_order_payments = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $row->order_id, "ats.reference_type" => 'purchase_order'),'purchase_order','','','',1,'all')->row()->total_paid_amount;
      $total_bill_payments = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("p.purchase_order_id" => $row->order_id, "ats.reference_type" => 'purchase_bill',"ats.tr_payment_part" => 0),'purchase_bill','','','',1,'all','all')->row()->total_paid_amount;
      $total_paid_from_supplier_advance = $this->Account->select_advance_return_transaction("COALESCE(SUM(art.tr_amount),0) as total_amount",array("art.reference_id" => $row->order_id,"art.tr_type" => 'purchase_order_transaction',"art.reference_type" => 'purchase_order'),'','','','',1,'all')->row()->total_amount;
      $total_paid = $total_order_payments + $total_bill_payments + $total_paid_from_supplier_advance;

      $purchase_bill_id_s = explode(',', $row->purchase_bill_id);
      $no_of_bills = sizeof($purchase_bill_id_s);

      $total_billed_amount = $this->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as total",array("p.purchase_order_id"=>$row->order_id))->row()->total;
      $balance_to_bill = $row->total_est_amount-$total_billed_amount;

      if($row->purchase_bill_id!=0){
        $bill_creation_status="<span style='margin-left: 5px;margin-right: 5px' class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check-circle'></i> $no_of_bills Bill Created</span>";
        foreach ($purchase_bill_id_s as $key => $bill_id) {
          $bill_creation_status.='<br/><a style="margin-top: 5px;margin-left: 5px;margin-right: 5px"  href="' . base_url() . 'purchases/profile/' . $bill_id . '" class="btn btn-warning btn-sm m-btn m-btn--icon   m-btn--pill m-btn--air">
          <i class="la la-eye"></i> # '.$bill_id.'
          </a>';
        }
      }else{
        $bill_creation_status="<span style='margin-left: 5px;margin-right: 5px' class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-times-circle'></i> Bill Not Created</span>";
        if($this->session->userdata('user_role')!='auditor' || $this->session->userdata('user_data_permission')['adding']==1){
          if($this->config->item('Purchase_delivery_challan') !=1 && $row->entry_approval_status==1){
            $bill_creation_status.='<br/><a style="margin-top: 5px;margin-left: 5px;margin-right: 5px"  href="' . base_url() . 'purchases/add?order_id=' . $row->order_id . '" class="btn btn-info btn-sm m-btn m-btn--icon   m-btn--pill m-btn--air">
            <i class="la la-plus-circle"></i> Add Purchase Bill
            </a>';
          }
        }
        if($this->session->userdata('user_role')=='admin'){
          $btn_delete='<a style="margin-left: 5px;margin-right: 5px" data-bill-type="purchase_order" data-bill-id-column="'.$bill_id_column.'" id="btn_delete_bill"  href="#bill_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($total_paid<$row->total_est_amount && $row->purchase_bill_id==0){
          if($this->session->userdata('user_role')!='auditor' || $this->session->userdata('user_data_permission')['adding']==1){
            $btn_add_payment = '<a style="margin-left: 5px;margin-right: 5px" data-bill-type="purchase_order" data-bill-id-column="'.$bill_id_column.'" id="btn_add_bill_payment" data-toggle="modal"  href="#bill_payment_add_modal" class="btn btn-warning m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
            <i class="la la-plus-circle"></i>
            </a>';
          }
        }
      }

      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_id')==$row->created_by) {
        $btn_edit = '<a style="margin-left: 5px;margin-right: 5px"  href="' . base_url() . 'purchase_order/edit/' . $row->order_id . '" class="btn btn-info m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
        <i class="la la-edit"></i>
        </a>';
      }

      $btn_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="purchase_order" data-approval-status="'.$row->entry_approval_status.'" data-id="'.$row->order_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) || $this->config->item('User_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>";
        }
      }

      $order_approval_status = '';
      if($this->config->item('Purchase_purchase_order_approval')==1){
        if($row->approval_status_1==1){
          $order_approval_status="<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> 1st Level Approved<br/>By ".$row->approved_1_user."<br/>On ".date('d-m-Y',strtotime($row->approved_date_1))."</span>";
          if($row->approval_status_2==1){
            $order_approval_status.="<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> 2nd Level Approved<br/>By ".$row->approved_2_user."<br/>On ".date('d-m-Y',strtotime($row->approved_date_2))."</span>";
          }elseif($row->approval_status_2==2){
            $order_approval_status.="<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> 2nd Level Rejected<br/>By ".$row->approved_2_user."<br/>On ".date('d-m-Y',strtotime($row->approved_date_2))."</span>";
          }else{
            $order_approval_status.="<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> 2nd Level Pending</span>";
          }
        }elseif($row->approval_status_1==2){
          $order_approval_status="<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> 1st Level Rejected<br/>By ".$row->approved_1_user."<br/>On ".date('d-m-Y',strtotime($row->approved_date_1))."</span>";
        }else{
          $order_approval_status="<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> 1st Level Pending</span>";
        }
      }

      $sl_no++;
      $billing_address = $this->remove_empty_lines($row->billing_address);
      // $billing_address = str_replace('\n','',$billing_address);
      // $billing_address = str_replace('<br>','',$billing_address);
      // $billing_address = str_replace("<br />\n", "\n", $billing_address);
      $billing_address = nl2br($billing_address);
      // $billing_address = str_replace('<br>\n<br>','<br>',$billing_address);

      if($status=='all' || $status=='approval_pending' || $status=='approved' || ($status=='balance_to_bill' && round($balance_to_bill)>0) || ($status=='billed' && $balance_to_bill<=0)){

        $order_prefix = $this->config->item('purchase_order_prefix');
        if (strpos($order_prefix, 'fy-y') !== false) {
          if (date('m',strtotime($row->order_date)) <= 3) {//Upto March
            $financial_year = (date('y',strtotime($row->order_date))-1) . '-' . date('y',strtotime($row->order_date));
          } else {//After March
            $financial_year = date('y',strtotime($row->order_date)) . '-' . (date('y',strtotime($row->order_date)) + 1);
          }
          $order_prefix = str_replace('fy-y', $financial_year, $order_prefix);
        }
        if($this->config->item('Purchase_order_show_order_no')==1){
          $order_id =  $order_prefix.$row->order_no.$row->orde_no_suffix;
        }else{
          $order_id =  $order_prefix.$row->order_id.$row->orde_no_suffix;
        }

        $array[$j][]=$sl_no;
        $array[$j][]='<a href="' . base_url() . 'purchase_order/profile/' . $row->order_id . '">#' . $order_id. '</a>';
        $array[$j][]=date('d-m-Y',strtotime($row->order_date));
        $array[$j][]='<a href="' . base_url() . 'supplier/profile/' . $row->supplier_id . '">' . $row->supplier_name . '</a>';
        $array[$j][] =$location_name;
        $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($row->total_est_amount,$this->config->item('Currency_currency_shortname'));
        $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_paid,$this->config->item('Currency_currency_shortname'));
        $array[$j][]=$bill_creation_status;
        $array[$j][]=$this->config->item('currency_format')->formatCurrency($total_billed_amount,$this->config->item('Currency_currency_shortname'));
        $array[$j][]=$this->config->item('currency_format')->formatCurrency($balance_to_bill,$this->config->item('Currency_currency_shortname'));
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Reference No : '.$row->reference_no."</li><li>Vehicle No : ".$row->vehicle_no."</li>";
        $array[$j][]=$billing_address;
        $array[$j][]=$order_approval_status;
        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>".$entry_approval_status;
        }else{
          $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->user_alias.'</li>'.$entry_approval_status;
        }
        $array[$j][]=$btn_add_payment.$btn_view.$btn_edit.$btn_delete.$btn_approve;
        $array[$j][]=$row->order_id;
        $array[$j][]=$row->entry_approval_status;


        $total_amount_paid+=$total_paid;
        $total_est_amount+=$row->total_est_amount;
        $grand_total_billed_amount+=$total_billed_amount;
        $grand_total_balance_to_bill+=$balance_to_bill;

        $j++;
      }
    endforeach;

    if($result->num_rows()>0){
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][] = '';
      $array[$j][]="<strong>Total</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_est_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_amount_paid,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_billed_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($grand_total_balance_to_bill,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="";
      $array[$j][]="";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';

    }

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

    $json_data=array();
    $j=0;
    $sl_no=0;
    $total_received_qty=$total_balance_qty=$total_amount=$total_qty=$total_without_tax=$total_tax_amount=$total_tax1_amount=$total_tax2_amount=$total_tax3_amount=0;
    $data=array();
    if(isset($_POST['date']) && $_POST['date']!=''){
      $date=array();
      $date=explode('-',$_POST['date']);
      $start_date=date('Y-m-d', strtotime($date[0]));
      $end_date=date('Y-m-d', strtotime($date[1]));
      $data["STR_TO_DATE(po.order_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(po.order_date,'%Y-%m-%d') >="]= $start_date;
    }
    if(isset($_POST['material']) && $_POST['material']!='' && $_POST['material']!='all'){
      $data['m.material_id'] = $_POST['material'];
    }
    if(isset($_POST['material_category']) && $_POST['material_category']!='' && $_POST['material_category']!='all'){
      $data['m.category'] = $_POST['material_category'];
    }
    if(isset($_POST['supplier']) && $_POST['supplier']!='' && $_POST['supplier']!='all'){
      $data['s.supplier_id'] = $_POST['supplier'];
    }
    if(isset($_POST['supplier_category']) && $_POST['supplier_category']!='' && $_POST['supplier_category']!='all'){
      $data['s.supplier_category'] = $_POST['supplier_category'];
    }
    if(isset($_POST['unit_id']) && $_POST['unit_id']!='' && $_POST['unit_id']!='all'){
      $data['poi.unit_id'] = $_POST['unit_id'];
    }
    if(isset($_POST['unit_price']) && $_POST['unit_price']!='' && $_POST['unit_price']!='all'){
      $data['poi.unit_price like'] = $_POST['unit_price'];
    }
    if(isset($_POST['location_id']) && $_POST['location_id']!='' && $_POST['location_id']!='all'){
      $data['po.location_id'] = $_POST['location_id'];
    }
    if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
      $data['sl.work_id'] = $_POST['work'];
    }
    if(isset($_POST['stage_id']) && $_POST['stage_id']!='' && $_POST['stage_id']!='all'){
      $data['po.project_stage_id'] = $_POST['stage_id'];
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['po.created_by'] = $_POST['user_id'];
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $data['po.order_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $data['po.order_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){
      $data['po.created_by'] = $this->session->userdata('user_id');
    }
    $result = $this->Purchase_order->select_purchase_order_items("poi.*,po.remarks,s.supplier_id,s.supplier_name,po.order_date,m.material_name,mu.unit_name,u.user_alias,po.created_date,po.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",$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):

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

      $row->tax1_amount = round($row->unit_price*$row->quantity*$row->tax1_percent/100,$this->config->item('Currency_amount_decimals_2'));
      $row->tax2_amount = round($row->unit_price*$row->quantity*$row->tax2_percent/100,$this->config->item('Currency_amount_decimals_2'));
      $row->tax3_amount = round($row->unit_price*$row->quantity*$row->tax3_percent/100,$this->config->item('Currency_amount_decimals_2'));

      $received_qty = $this->Purchases->select_purchase_items("COALESCE(SUM(pi.quantity),0) as total", array("p.purchase_order_id" =>$row->order_id,"pi.material_id"=>$row->material_id))->row()->total;


      $sl_no++;
      $array[$j][]=$sl_no;
      $array[$j][]='<a href="' . base_url() . 'purchase_order/profile/' . $row->order_id . '">#' . $row->order_id . '</a>';
      $array[$j][]=date('d-m-Y',strtotime($row->order_date));
      $array[$j][]='<a href="' . base_url() . 'supplier/profile/' . $row->supplier_id . '">' . $row->supplier_name . '</a>';
      $array[$j][]=$row->material_name;
      $array[$j][]=$row->item_code;
      $array[$j][]=$row->quantity;
      $array[$j][]=round($received_qty,2);
      $array[$j][]=round($row->quantity-$received_qty,2);
      $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][]=$row->tax1_percent;
      $array[$j][]=number_format($row->tax1_amount,$this->config->item('Currency_amount_decimals_2'),'.','');
      $array[$j][]=$row->tax2_percent;
      $array[$j][]=number_format($row->tax2_amount,$this->config->item('Currency_amount_decimals_2'),'.','');
      $array[$j][]=$row->tax3_percent;
      $array[$j][]=number_format($row->tax3_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->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][]='';
      }
      $array[$j][] = $location_name;


      $total_amount=$total_amount+$row->total_amount;
      $total_qty+=$row->quantity;
      $total_received_qty+=$received_qty;
      $total_balance_qty+=($row->quantity-$received_qty);
      $total_without_tax+=$row->total_amount-$row->tax_amount;
      $total_tax_amount+=$row->tax_amount;
      $total_tax1_amount+=$row->tax1_amount;
      $total_tax2_amount+=$row->tax2_amount;
      $total_tax3_amount+=$row->tax3_amount;

      $j++;
    endforeach;
    if($result->num_rows()>0){
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]="<strong>Total</strong>";
      $array[$j][]="<strong>".$total_qty."</strong>";
      $array[$j][]="<strong>".round($total_received_qty,2)."</strong>";
      $array[$j][]="<strong>".round($total_balance_qty,2)."</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][]='';
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_tax1_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]='';
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_tax2_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]='';
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_tax3_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][]="";
      $array[$j][]="";
    }

    $json_data['data']=$array;
    echo json_encode($json_data);  // send data as json format
  }


  function remove_empty_lines($string) {
    $lines = explode("\n", str_replace(array("\r\n", "\r"), "\n", $string));
    $lines = array_map('trim', $lines);
    $lines = array_filter($lines, function($value) {
      return $value !== '';
    });
    return implode("\n", $lines);
  }
}
?>
