<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Supplier 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('Account_model', 'Account');
    $this->load->model('Stock_model', 'Stock');
    $this->load->model('Bills_model', 'Bills');
    $this->load->model('Vehicle_model', 'Vehicle');
    $this->load->model('Settings_model', 'Settings');
    $this->load->library('Bills_management', 'bills_management');
    $this->load->library('Supplier_management', 'supplier_management');


    //        Check User Logged in or Not
    $allowed_user_roles = array("auditor", "madmin", "admin", "supervisor", "general_manager", "purchase_manager", "accounts_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'] = "suppliers";
    $this->page_data['supplier_categories'] = $this->Supplier->select_supplier_category()->result();
  }

  public function index()
  {
    $this->view();
  }

  public function view()
  {
    $this->page_data['page_name'] = 'supplier_list';
    $this->load->view('Index', $this->page_data);
  }
  public function material_report()
  {
    $this->page_data['selected_account'] = $this->page_data['selected_end_date'] = $this->page_data['selected_start_date'] = '';
    if(isset($_POST['start_date']) && $_POST['start_date']!=''){
      $this->page_data['selected_start_date'] = $_POST['start_date'];
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!=''){
      $this->page_data['selected_end_date'] = $_POST['end_date'];
    }
    if(isset($_POST['account']) && $_POST['account']!=''){
      $this->page_data['selected_account'] = $_POST['account'];
    }
    $i=0;
    $transactions = array();
    if($this->page_data['selected_account']!=''){
      $account_details = $this->Account->select_account_heads("",array("acc_supplier_id" => $this->page_data['selected_account']));
      if($account_details->num_rows()==1){
        $account_details = $account_details->row();
        $account_details->current_op_balance  = 0;


        $this->page_data['transactions'] = $transactions;
        $this->page_data['selected_account_data'] = $account_details;
      }
    }

    $this->page_data['suppliers'] = $this->Supplier->select_supplier("*")->result();
    $this->page_data['page_name'] = 'supplier_material_report';
    $this->load->view('Index', $this->page_data);
  }
  public function profile($id="")
  {
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc.assigned_users)");
    }
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();

    if($this->config->item('app_module_vehicle_management')==1){
      $columns = 'v.*,ah.acc_head_name,vc.consignee_name';
    }else{
      $columns = 'v.*,vc.consignee_name';
    }
    $this->page_data['owned_vehicles'] = $this->Vehicle->select_vehicle($columns,array('v.vehicle_type'=>'own'))->result();
    $this->page_data['vehicle_suppliers'] = $this->Supplier->select_supplier("*", array("supplier_type" => 'vehicle'))->result();


    if ($id != "") {
      $this->page_data['supplier_data'] = $this->Supplier->select_supplier("*", array("supplier_id" => $id));
      if ($this->page_data['supplier_data']->num_rows() == 1) {
        $this->page_data['supplier_data'] = $this->page_data['supplier_data']->row();
        $this->page_data['supplier_data']->acc_head_id = 0;
        $account_head = $this->Account->select_account_heads("",array("acc_supplier_id"=>$this->page_data['supplier_data']->supplier_id));
        if($account_head->num_rows()==1){
          $this->page_data['supplier_data']->acc_head_id = $account_head->row()->acc_head_id;
        }
        $this->page_data['page_name'] = 'supplier_profile';
        $this->load->view('Index', $this->page_data);
      } else {
        show_404();
      }
    } else {
      show_404();
    }
  }
  public function select_supplier()
  {

    $json_data = array();
    $j = $gt_total_op_balance = $gt_total_bill_amount = $gt_total_paid = $gt_total_balance = 0;

    $data = array();
    if (isset($_POST['category']) && $_POST['category'] != '' && $_POST['category'] != 'all') {
      $category=$_POST['category'];
      $this->db->where("FIND_IN_SET($category , s.supplier_category)");
    }
    if (isset($_POST['type']) && $_POST['type'] != '' && $_POST['type'] != 'all') {
      $data['s.supplier_type'] = $_POST['type'];
    }
    if (isset($_POST['branch_id']) && $_POST['branch_id'] != '' && $_POST['branch_id'] != 'all') {
      $data['s.branch_id'] = $_POST['branch_id'];
    }

    $result = $this->Supplier->select_supplier("*", $data);
    $result_array = $result->result();

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

    foreach ($result_array as $row) :
      $btn_edit = '';
      if ($this->session->userdata('user_role') != 'auditor') {
        $btn_edit = '<a style="margin-left: 5px;margin-right: 5px" id="suppier_edit_btn" href="#supplier_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_profile = '<a style="margin-left: 5px;margin-right: 5px"  href="' . base_url() . 'supplier/profile/' . $row->supplier_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_delete = '';
      if ($row->supplier_editable==1 && ($this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin')) {
        $btn_delete = '<a style="margin-left: 5px;margin-right: 5px" id="suppier_delete_btn" href="#supplier_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>';
      }

      $id = $row->supplier_id;
      $opening_balance = $this->Account->select_account_heads("", array("acc_supplier_id" => $id))->row()->op_balance;
      $opening_balance_paid = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("s.supplier_id" => $id, "ats.tr_type" => 'supplier_opbalance_transaction', "ats.reference_type" => 'supplier'), 'supplier')->row()->total_amount;
      $advance_paid = $this->supplier_management->calculate_advance_amount($id)['advance_paid'];
      $total_order_payment = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("po.supplier_id" => $id, "ats.tr_type" => 'purchase_order_transaction', "ats.reference_type" => 'purchase_order', "po.delete_status" => 0), 'purchase_order')->row()->total_amount;
      $total_purchase_bill_payment = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("p.supplier_id" => $id, "ats.tr_type" => 'purchase_bill_transaction', "ats.reference_type" => 'purchase_bill', "p.delete_status" => 0), 'purchase_bill')->row()->total_amount;
      $total_purchase_bill_amount = $this->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as grand_total", array("p.supplier_id" => $id, "p.delete_status" => 0))->row()->grand_total;
      $total_expense_bill_amount = $this->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as grand_total", array("b.bill_supplier_id" => $id))->row()->grand_total;
      $total_expense_bill_payment = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("b.bill_supplier_id" => $id, "ats.tr_type" => 'vehicle_bill_transaction', "ats.reference_type" => 'vehicle_bill'), 'vehicle_bill')->row()->total_amount;
      $total_expense_bill_balance_amount = $this->Bills->select_bills("COALESCE(SUM(b.bill_balance_amount),0) as grand_total", array("b.bill_supplier_id" => $id))->row()->grand_total;
      $total_purchase_bill_balance_amount = $this->Purchases->select_purchase("COALESCE(SUM(p.balance_amount),0) as total_purchase_balance", array("p.supplier_id" => $id, "p.delete_status" => 0))->row()->total_purchase_balance;
      $total_purchase_commission = $this->Purchases->select_purchase("COALESCE(SUM(p.bill_commission_amount),0) as total_amount", array("p.supplier_id" => $id, "p.delete_status" => 0))->row()->total_amount;
      $total_paid = $total_expense_bill_payment + $total_order_payment + $total_purchase_bill_payment + $opening_balance_paid + $advance_paid;
      $total_balance = round(($total_purchase_bill_amount + $total_expense_bill_amount - $total_paid + $opening_balance),$this->config->item('Currency_amount_decimals_2'));

      $array[$j][] = $row->supplier_id;
      $array[$j][] = ($row->branch_id == 0) ? 'No Branch' : $this->Branches->select_branches('*', array('branch_id' => $row->branch_id))->row()->branch_name;
      $array[$j][] = ucwords($row->supplier_type);
      $array[$j][] = '<a href="' . base_url() . 'supplier/profile/' . $row->supplier_id . '">' . $row->supplier_name . '</a>';
      $array[$j][] = $row->supplier_address;
      $array[$j][] = $row->category_names;
      $array[$j][] = $row->supplier_phone;
      $array[$j][] = $row->supplier_email;
      $array[$j][] = $row->contact_person;
      $array[$j][] = $row->tax_no;
      $array[$j][] = $row->tax_date;
      $array[$j][] = $row->pan_no;
      $array[$j][] = $row->aadhar_no;
      $array[$j][] = $row->bank_account_no;
      $array[$j][] = $row->bank_ifsc;
      $array[$j][] = $row->bank_name;
      $array[$j][] = $row->bank_branch;
      $data_supplier_head['acc_supplier_id'] = $row->supplier_id;
      $account_head_data = $this->Account->select_account_heads("*", $data_supplier_head);
      if ($account_head_data->num_rows() > 0) {
        $array[$j][] = $account_head_data->row()->op_balance;
        $gt_total_op_balance += $account_head_data->row()->op_balance;
      } else {
        $array[$j][] = '0';
      }
      $array[$j][] = $this->config->item('currency_format')->formatCurrency($total_purchase_bill_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('currency_format')->formatCurrency($total_paid, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('currency_format')->formatCurrency($total_balance, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $btn_profile . $btn_edit . $btn_delete;
      $array[$j][] = $row->supplier_category;
      $array[$j][] = $row->supplier_type;


      $gt_total_bill_amount += $total_purchase_bill_amount;
      $gt_total_paid += $total_paid;
      $gt_total_balance += $total_balance;


      $j++;
    endforeach;
    if ($result->num_rows() > 0) {
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>Total </strong>";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>" . $this->config->item('currency_format')->formatCurrency($gt_total_op_balance, $this->config->item('Currency_currency_shortname')) . "</strong>";
      $array[$j][] = "<strong>" . $this->config->item('currency_format')->formatCurrency($gt_total_bill_amount, $this->config->item('Currency_currency_shortname')) . "</strong>";
      $array[$j][] = "<strong>" . $this->config->item('currency_format')->formatCurrency($gt_total_paid, $this->config->item('Currency_currency_shortname')) . "</strong>";
      $array[$j][] = "<strong>" . $this->config->item('currency_format')->formatCurrency($gt_total_balance, $this->config->item('Currency_currency_shortname')) . "</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
    }

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

  public function create()
  {
    $data2['supplier_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Supplier->select_supplier("*", $data2)->num_rows();
    $data2['supplier_address'] = $this->security->xss_clean($this->input->post('address'));
    $data2['tax_no'] = $this->security->xss_clean($this->input->post('tax_no'));
    $data2['tax_date'] = $this->security->xss_clean($this->input->post('tax_date'));
    $data2['supplier_phone'] = $this->security->xss_clean($this->input->post('phone'));
    $data2['supplier_email'] = $this->security->xss_clean($this->input->post('email'));
    if($this->input->post('category')){
      $data2['supplier_category'] = implode(',', $this->security->xss_clean($this->input->post('category')));
    }
    $data2['bank_account_no'] = $this->security->xss_clean($this->input->post('bank_account_no'));
    $data2['bank_ifsc'] = $this->security->xss_clean($this->input->post('bank_ifsc'));
    $data2['bank_branch'] = $this->security->xss_clean($this->input->post('bank_branch'));
    $data2['bank_name'] = $this->security->xss_clean($this->input->post('bank_name'));
    $data2['pan_no'] = $this->security->xss_clean($this->input->post('pan_no'));
    $data2['aadhar_no'] = $this->security->xss_clean($this->input->post('aadhar_no'));
    $data2['contact_person'] = $this->security->xss_clean($this->input->post('contact_person'));
    if ($this->config->item('app_module_vehicle_management') == 1) {
      $data2['supplier_type'] = $this->security->xss_clean($this->input->post('supplier_type'));
    }

    if ($count == 0) {
      $result2 = $this->Supplier->create_supplier($data2);
      $flash_data['status'] = $result2['status'];
      // Create supplier Account Head
      $account_data['acc_head_name'] = $data2['supplier_name'] . "'s Account";
      $account_data['account_type'] = 12; //Direct Expense
      $account_data['acc_supplier_id'] = $result2['insert_id'];
      $account_data['head_editable'] = 0;
      $account_data['op_balance'] = $this->security->xss_clean($this->input->post('op_balance'));
      $account_data['created_date'] = date('Y-m-d H:i:s');
      $account_data['created_by'] = $this->session->userdata('user_id');

      $account_result = $this->Account->create_account_head($account_data);
      $flash_data2['status'] = $account_result['status'];

      if ($flash_data['status'] == 1 && $flash_data2['status'] == 1) {
        // if ($flash_data['status'] == 1) {
        $flash_data['flashdata_msg'] = 'Supplier Added 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;
      $flash_data['flashdata_msg'] = 'Sorry.. Supplier with Same Name Already Exist!';
      $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()
  {
    $data2['supplier_id'] = $this->security->xss_clean($this->input->post('supplier_id'));
    $data2['supplier_name'] = $this->security->xss_clean($this->input->post('supplier_name'));
    $data2['supplier_address'] = $this->security->xss_clean($this->input->post('supplier_address'));
    $data2['tax_no'] = $this->security->xss_clean($this->input->post('tax_no'));
    $data2['tax_date'] = $this->security->xss_clean($this->input->post('tax_date'));
    $data2['supplier_phone'] = $this->security->xss_clean($this->input->post('phone'));
    $data2['supplier_email'] = $this->security->xss_clean($this->input->post('email'));
    if($this->input->post('category')){
      $data2['supplier_category'] = implode(',', $this->security->xss_clean($this->input->post('category')));
    }else{
      $data2['supplier_category'] = '';
    }
    $data2['bank_account_no'] = $this->security->xss_clean($this->input->post('bank_account_no'));
    $data2['bank_ifsc'] = $this->security->xss_clean($this->input->post('bank_ifsc'));
    $data2['bank_branch'] = $this->security->xss_clean($this->input->post('bank_branch'));
    $data2['bank_name'] = $this->security->xss_clean($this->input->post('bank_name'));
    $data2['pan_no'] = $this->security->xss_clean($this->input->post('pan_no'));
    $data2['aadhar_no'] = $this->security->xss_clean($this->input->post('aadhar_no'));
    $data2['contact_person'] = $this->security->xss_clean($this->input->post('contact_person'));
    if ($this->config->item('app_module_vehicle_management') == 1) {
      $data2['supplier_type'] = $this->security->xss_clean($this->input->post('supplier_type_edit'));
    }
    $count = $this->Supplier->select_supplier("*", array("supplier_id !=" => $data2['supplier_id'], "supplier_name" => $data2['supplier_name']))->num_rows();
    if ($count == 0) {
      $result2 = $this->Supplier->update_supplier($data2);
      // update account head
      $account_head_data = $this->Account->select_account_heads("", array("acc_supplier_id" => $data2['supplier_id']));
      if ($account_head_data->num_rows() == 1) {
        // Update Account Head
        $account_data['acc_head_name'] = $data2['supplier_name'] . "'s Account";
        $account_data['acc_head_id'] = $account_head_data->row()->acc_head_id;
        $account_data['op_balance'] = $this->security->xss_clean($this->input->post('op_balance'));
        $account_data['updated_date'] = date('Y-m-d H:i:s');
        $account_data['updated_by'] = $this->session->userdata('user_id');
        $account_result = $this->Account->update_account_head($account_data);
      }

      $flash_data['status'] = $result2;
      if ($result2 == 1) {
        $flash_data['flashdata_msg'] = 'Supplier 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;
      $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()
  {
    $data2['supplier_id'] = $this->security->xss_clean($this->input->post('supplier_id'));
    $data2['delete_status'] = 1;
    $result2 = $this->Supplier->update_supplier($data2);
    $flash_data['status'] = $result2;
    if ($result2 == 1) {
      $flash_data['flashdata_msg'] = 'Supplier Deleted Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';

      //  Delete Account Head
      $account_data['account_type'] = 12;
      $account_data['acc_supplier_id'] = $data2['supplier_id'];
      $account_details = $this->Account->select_account_heads("*", $account_data);
      if ($account_details->num_rows() == 1) {
        $account_data['acc_head_id'] = $account_details->row()->acc_head_id;
        $account_data['delete_status'] = 1;
        $this->Account->update_account_head($account_data);
      }
    } 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()
  {
    $data = array();
    if (isset($_POST['id']) && $_POST['id'] != "") {
      $data['supplier_id'] = $_POST['id'];
    }
    if (isset($_POST['supplier_type']) && $_POST['supplier_type'] != "") {
      $data['supplier_type'] = $_POST['supplier_type'];
    }
    $result = $this->Supplier->select_supplier("*", $data);
    if ($result->num_rows() > 0) {
      foreach ($result->result() as $row) :
        $id[] = $row->supplier_id;
        $name[] = $row->supplier_name;
        $address[]  = $row->supplier_address;
        $accound_head_supplier_data = $this->Account->select_account_heads("", array("acc_supplier_id" => $row->supplier_id));
        if ($accound_head_supplier_data->num_rows() > 0) {
          $op_balance[] = $accound_head_supplier_data->row()->op_balance;
        }
        $supplier_mode[]  = 'supplier';
      endforeach;
      if (isset($data['supplier_type']) && $data['supplier_type'] == 'vehicle') {
        // Get Warehouse
        $warehouse = $this->Stock->select_stock_locations("", array('sl.work_id' => '0', 'sl.location_type' => 'ware_house'))->result();
        foreach ($warehouse as $row) :
          $id[] = $row->location_id;
          $name[] = $row->location_name;
          $address[]  = '';
          $op_balance[] = 0;
          $supplier_mode[]  = 'warehouse';
        endforeach;
      }
      echo json_encode(array("count" => $result->num_rows(), "id" => $id, "name" => $name, "address" => $address, "supplier_mode" => $supplier_mode));
    } else {
      echo json_encode(array("count" => $result->num_rows()));
    }
  }
  public function select_summary($return_type = '')
  {
    $json_data = $json_data2 = array();
    $j = $sl_no = 0;
    $data = array();
    $id = $_POST['supplier_id'];
    $json_data['opening_balance'] = $this->Account->select_account_heads("", array("acc_supplier_id" => $id))->row()->op_balance;
    // $json_data['opening_balance'] = 0;
    $json_data['total_advance_paid'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",array("s.supplier_id" => $id,"ats.tr_type" => 'supplier_advance_transaction',"ats.reference_type" => 'supplier'),'supplier')->row()->total_amount;
    $json_data['advance_redeemed_to_order'] = $this->Account->select_advance_return_transaction("COALESCE(SUM(art.tr_amount),0) as total_amount",array("art.supplier_id" => $id,"art.tr_type" => 'purchase_order_transaction',"art.reference_type" => 'purchase_order'))->row()->total_amount;
    $json_data['advance_redeemed_to_bill'] = $this->Account->select_advance_return_transaction("COALESCE(SUM(art.tr_amount),0) as total_amount",array("art.supplier_id" => $id,"art.tr_type" => 'purchase_bill_transaction',"art.reference_type" => 'purchase_bill'))->row()->total_amount;
    $json_data['opening_balance_paid'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("s.supplier_id" => $id, "ats.tr_type" => 'supplier_opbalance_transaction', "ats.reference_type" => 'supplier'), 'supplier')->row()->total_amount;
    $json_data['advance_paid'] = $this->supplier_management->calculate_advance_amount($id)['advance_paid'];
    $json_data['advance_available_to_redeem'] = $this->supplier_management->calculate_advance_amount($id)['balance'];
    $json_data['total_order_payment'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("po.supplier_id" => $id, "ats.tr_type" => 'purchase_order_transaction', "ats.reference_type" => 'purchase_order', "po.delete_status" => 0), 'purchase_order')->row()->total_amount;
    $json_data['total_purchase_bill_payment'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("p.supplier_id" => $id, "ats.tr_type" => 'purchase_bill_transaction', "ats.reference_type" => 'purchase_bill', "p.delete_status" => 0), 'purchase_bill')->row()->total_amount;
    $json_data['total_purchase_bill_amount'] = $this->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as grand_total", array("p.supplier_id" => $id, "p.delete_status" => 0))->row()->grand_total;
    $json_data['total_purchase_bill_balance_amount'] = $this->Purchases->select_purchase("COALESCE(SUM(p.balance_amount),0) as total_purchase_balance", array("p.supplier_id" => $id, "p.delete_status" => 0))->row()->total_purchase_balance;
    $json_data['total_purchase_commission'] = $this->Purchases->select_purchase("COALESCE(SUM(p.bill_commission_amount),0) as total_amount", array("p.supplier_id" => $id, "p.delete_status" => 0))->row()->total_amount;
    $json_data['total_expense_bill_amount'] = $this->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as grand_total", array("b.bill_supplier_id" => $id))->row()->grand_total;
    $json_data['total_expense_bill_payment'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("b.bill_supplier_id" => $id, "ats.tr_type" => 'vehicle_bill_transaction', "ats.reference_type" => 'vehicle_bill'), 'vehicle_bill')->row()->total_amount;

    $json_data['total_redeemed'] = $json_data['advance_redeemed_to_bill'] + $json_data['advance_redeemed_to_order'];
    $json_data['total_paid'] = $json_data['total_expense_bill_payment'] + $json_data['total_order_payment'] + $json_data['total_purchase_bill_payment'] + $json_data['opening_balance_paid'] + $json_data['advance_paid'];
    $json_data['total_balance'] = $json_data['total_expense_bill_amount'] + $json_data['total_purchase_bill_amount'] - $json_data['total_paid'] + $json_data['opening_balance'];
    foreach ($json_data as $key => $value) {
      $json_data[$key] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value, $this->config->item('Currency_currency_shortname'));
    }
    if ($return_type == 'count') {
      echo json_encode($json_data);  // send data as json format
    } else {
    }
  }

  public function add_supplier_payment()
  {

    $transaction_data['tr_date'] = $transaction_data2['tr_date'] = $transaction_data3['tr_date'] = date('Y-m-d', strtotime($this->security->xss_clean($this->input->post('paid_date'))));
    $transaction_data['tr_debit_acc'] = $transaction_data2['tr_debit_acc'] = $transaction_data3['tr_debit_acc'] = $this->security->xss_clean($this->input->post('debit_acc'));
    $transaction_data['tr_mode'] = $transaction_data2['tr_mode'] = $transaction_data3['tr_mode'] = $this->security->xss_clean($this->input->post('payment_mode'));
    $transaction_data['tr_reference_no'] = $transaction_data2['tr_reference_no'] = $transaction_data3['tr_reference_no'] = $this->security->xss_clean($this->input->post('reference_no'));
    $transaction_data['tr_remarks'] = $transaction_data2['tr_remarks'] = $transaction_data3['tr_remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
    $transaction_data['created_date'] = $transaction_data2['created_date'] = $transaction_data3['created_date'] = date('Y-m-d H:i:s');
    $transaction_data['created_by'] = $transaction_data2['created_by'] = $transaction_data3['created_by'] = $this->session->userdata('user_id');
    if ($this->input->post('reference_date') != '') {
      $transaction_data['tr_reference_date'] = $transaction_data2['tr_reference_date'] = $transaction_data3['tr_reference_date'] = date('Y-m-d', strtotime($this->security->xss_clean($this->input->post('reference_date'))));
    }
    if ($this->config->item('User_enable_data_entry_approval') == 1 && $this->session->userdata('user_role_id') > 2) {
      $transaction_data['tr_entry_approval_status'] = $transaction_data2['tr_entry_approval_status'] = $transaction_data3['tr_entry_approval_status'] = 0;
    }
    $account_head_data = $this->Account->select_account_heads("", array("acc_supplier_id" => $this->input->post('supplier_id')));

    $last_tr_group = $this->Account->select_account_transaction("max(ats.tr_group_id) as tr_group_id", '', '', '', '', '', 'all', 'all');
    if ($last_tr_group->num_rows() == 1) {
      $transaction_data['tr_group_id'] = ($last_tr_group->row()->tr_group_id) + 1;
    } else {
      $transaction_data['tr_group_id'] = 1;
    }
    $transaction_data2['tr_group_id'] = $transaction_data3['tr_group_id'] = $transaction_data['tr_group_id'];

    if ($account_head_data->num_rows() == 1) {
      $transaction_data2['tr_credit_acc'] = $transaction_data3['tr_credit_acc'] = $account_head_data->row()->acc_head_id;
      //opening balance
      $transaction_data2['tr_amount'] = $this->input->post("op_amount_to_be_paid");
      $transaction_data2['reference_id'] = $transaction_data3['reference_id'] = $this->input->post("supplier_id");
      $transaction_data2['reference_type'] = $transaction_data3['reference_type'] = "supplier";
      $transaction_data2['tr_type'] = "supplier_opbalance_transaction";
      $transaction_data2['tr_description'] = ucwords(str_replace('_', ' ', $transaction_data2['tr_type']));
      if ($transaction_data2['tr_amount'] > 0) {
        $transaction_result2 =  $this->Account->create_account_transaction($transaction_data2);
        $flash_data2['status'] = $transaction_result2['status']; //opening balance
        $this->User->add_user_activity('Account Transaction', $transaction_result2['insert_id'], $transaction_data2['tr_description'] . ' Created');
      }
      //advance payment
      $transaction_data3['tr_amount'] = $this->input->post("advance_amount");
      $transaction_data3['tr_type'] = "supplier_advance_transaction";
      $transaction_data3['tr_description'] = ucwords(str_replace('_', ' ', $transaction_data3['tr_type']));

      if ($transaction_data3['tr_amount'] > 0) {
        $transaction_result3 =  $this->Account->create_account_transaction($transaction_data3);
        $flash_data3['status'] = $transaction_result3['status']; //Advance payment
        $this->User->add_user_activity('Account Transaction', $transaction_result3['insert_id'], $transaction_data3['tr_description'] . ' Created');
      }
    }

    //bill payments
    $transaction_data['reference_type'] = "purchase_bill";
    $transaction_data['tr_type'] = "purchase_bill_transaction";
    $transaction_data['tr_description'] = ucwords(str_replace('_', ' ', $transaction_data['tr_type']));

    $bill_count = $this->input->post("bill_count");
    if ($bill_count > 0) {
      for ($i = 0; $i < $bill_count; $i++) {
        if($transaction_data['tr_type'] == 'purchase_bill_transaction' && $this->config->item('User_enable_data_entry_approval_2_level')==1 && $this->session->userdata('user_role_id')>2){
          $transaction_data['tr_entry_approval_status_2'] = 0;
        }
        $transaction_data['reference_id'] = $this->input->post("bill_id_" . $i);
        $transaction_data['tr_amount'] = $this->input->post("amount_" . $i);
        if ($transaction_data['tr_amount'] > 0) {
          $transaction_data['tr_credit_acc'] = 5; //Purchase Bill
          $transaction_result1 =  $this->Account->create_account_transaction($transaction_data);
          $flash_data1['status'] = $transaction_result1['status']; //bill payment
          $this->User->add_user_activity('Account Transaction', $transaction_result1['insert_id'], $transaction_data['tr_description'] . ' Created');
          $this->bills_management->calculate_purchase_bill_total($transaction_data['reference_id']);
        }
      }
    }


    if ((isset($flash_data1) && $flash_data1['status'] == 1) || (isset($flash_data2) && $flash_data2['status'] == 1) || (isset($flash_data3) && $flash_data3['status'] == 1)) {
      $flash_data['status'] = 1;
      $flash_data['flashdata_msg'] = 'Supplier Payment Created 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 supplier_payment_details()
  {
    $json_data = array();
    if($this->input->post('supplier_id')){
      $id = $this->security->xss_clean($this->input->post('supplier_id'));
      if($this->input->post('type')){
        $type = $this->security->xss_clean($this->input->post('type'));
      }else{
        $type='';
      }
      if ($type != 'advance') {
        $json_data['bill_data'] = array();
        if($this->config->item('Purchase_hide_bills_supplier_payment')==0){
          //bill data
          $bill_data =  $this->Purchases->select_purchase("p.purchase_id,p.purchase_date,p.bill_total_amount,p.bill_payable_amount,p.total_paid,p.balance_amount", array("s.supplier_id" => $id, "p.balance_amount >" => '0'))->result();
          $json_data['bill_data'] = $bill_data;
        }
        //opening balance
        $paid_op_balance = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("s.supplier_id" => $id, "ats.tr_type" => 'supplier_opbalance_transaction', "ats.reference_type" => 'supplier'), 'supplier')->row()->total_amount;
        $this->page_data['supplier_data'] = $this->Supplier->select_supplier("*", array("supplier_id" => $id))->row();
        $total = $this->Account->select_account_heads("", array("acc_supplier_id" => $id))->row()->op_balance;
        $op_balance = $total - $paid_op_balance;
        $json_data['op_data'] = array('total' => $total, 'paid' => $paid_op_balance, 'balance' => $op_balance);
      } else {
        //advance
        $json_data['advance'] = $this->supplier_management->calculate_advance_amount($id)['balance'];
      }
    }
    echo json_encode($json_data);
  }

  public function select_advance_return_payments()
  {
    $data = array();

    if (isset($_POST['supplier_id']) && $_POST['supplier_id'] != '' && $_POST['supplier_id'] != 'all') {
      $data['s.supplier_id'] = $_POST['supplier_id'];
    }

    if (isset($_POST['user_id']) && $_POST['user_id'] != '' && $_POST['user_id'] != 'all') {
      $data['art.created_by'] = $_POST['user_id'];
    }

    if ($this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'general_manager') {
      $data['art.created_by'] = $this->session->userdata('user_id');
    }
    $result = $this->Supplier->select_supplier_advance_return($data);
    $result_array = $result->result();
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = $result->num_rows();
    $json_data['recordsFiltered'] = $result->num_rows();
    $array = array();
    $sl_no = $j = $total_paid_amount = 0;

    foreach ($result_array as $row) :
      $sl_no++;
      $btn_edit = $btn_delete = '';
      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" id="advance_payment_edit_btn" href="#advance_payment_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" id="advance_payments_delete_btn" 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>';
      }
      $array[$j][] = $sl_no;
      $array[$j][] = $row->tr_id;
      $array[$j][] = date('d-m-Y', strtotime($row->tr_date));
      $array[$j][] = $row->supplier_name;
      $array[$j][] = $row->tr_amount;
      if ($row->reference_type == 'purchase_order') {
        $array[$j][] = 'Purchase Order : <a href="' . base_url() . 'purchase_order/profile/' . $row->reference_id . '">#' . $row->reference_id . '</a>';
      } else {
        $array[$j][] = 'Purchase Bill : <a href="' . base_url() . 'purchases/profile/' . $row->reference_id . '">#' . $row->reference_id . '</a>';
      }
      $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][] = '';
      }
      $btn_edit = '<a style="margin-left: 5px;margin-right: 5px" id="advance_payment_edit_btn_return" href="#advance_payment_returned_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" id="btn_delete_advance_transaction_return"  href="#advance_transaction_delete_modal_return" 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>';
      $array[$j][] = $btn_edit . $btn_delete;
      $array[$j][] = $row->tr_id;

      $total_paid_amount += $row->tr_amount;

      $j++;
    endforeach;
    if ($result->num_rows() > 0) {
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = "<strong>Total Returned </strong>";
      $array[$j][] = "<strong>" . $total_paid_amount . "</strong>";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = "";
      $array[$j][] = "";
    }

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


  public function update_advance_transaction()
  {
    $transaction_data['tr_amount'] = $this->input->post("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_remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
    $transaction_data['transaction_id'] = $this->input->post("transaction_id");
    $transaction_result =  $this->Account->update_account_transaction($transaction_data);
    $flash_data['status'] = $transaction_result;
    if ($flash_data['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Advance Payment Details Updated Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    }
    echo json_encode($flash_data);
  }

  public function update_advance_return_transaction()
  {
    $transaction_data['tr_id'] = $this->input->post("transaction_id");
    $transaction_details =  $this->Supplier->select_supplier_advance_return($transaction_data);
    if($transaction_details->num_rows()==1){
      $transaction_data['tr_amount'] = $this->input->post("paid_amount");
      $transaction_data['tr_date'] = date('Y-m-d', strtotime($this->security->xss_clean($this->input->post('paid_date'))));

      $transaction_result =  $this->Supplier->update_supplier_advance_return($transaction_data);
      $flash_data['status'] = $transaction_result;
      if ($flash_data['status'] == 1) {
        $transaction_details = $transaction_details->row();
        if($transaction_details->tr_type=='purchase_bill_transaction'){
          $this->bills_management->calculate_purchase_bill_total($transaction_details->reference_id);
        }elseif($transaction_details->tr_type=='purchase_order_transaction'){

        }

        $flash_data['flashdata_msg'] = 'Advance Payment return 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;
      $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 delete_advance_return_transaction()
  {
    $transaction_data['tr_id'] = $this->input->post("transaction_id");
    $transaction_details =  $this->Supplier->select_supplier_advance_return($transaction_data);
    if($transaction_details->num_rows()==1){
      $transaction_data['delete_status'] = '1';

      $transaction_result =  $this->Supplier->update_supplier_advance_return($transaction_data);
      $flash_data['status'] = $transaction_result;
      if ($flash_data['status'] == 1) {
        $transaction_details = $transaction_details->row();
        if($transaction_details->tr_type=='purchase_bill_transaction'){
          $this->bills_management->calculate_purchase_bill_total($transaction_details->reference_id);
        }elseif($transaction_details->tr_type=='purchase_order_transaction'){

        }

        $flash_data['flashdata_msg'] = 'Advance Payment return 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;
      $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_category()
  {
    $data['sup_category_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Supplier->select_supplier_category("", $data)->num_rows();
    if ($count == 0) {
      $result = $this->Supplier->create_supplier_category($data);
      $flash_data['status'] = $result['status'];
      if ($flash_data['status'] == 1) {
        $flash_data['flashdata_msg'] = 'Supplier Category Added 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;
      $flash_data['flashdata_msg'] = 'Sorry.. Category with Same Name Already Exist!';
      $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_category()
  {
    $data['sup_category_id'] = $where['sup_category_id !='] = $this->security->xss_clean($this->input->post('category_id'));
    $data['sup_category_name'] = $where['sup_category_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Supplier->select_supplier_category("", $where)->num_rows();
    if ($count == 0) {
      $result = $this->Supplier->update_supplier_category($data);
      $flash_data['status'] = $result;
      if ($flash_data['status'] == 1) {
        $flash_data['flashdata_msg'] = 'Supplier Category 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;
      $flash_data['flashdata_msg'] = 'Sorry.. Category with Same Name Already Exist!';
      $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_category()
  {
    $data['sup_category_id'] = $this->security->xss_clean($this->input->post('category_id'));
    $data['delete_status'] = 1;
    $result = $this->Supplier->update_supplier_category($data);
    $flash_data['status'] = $result;
    if ($flash_data['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Category 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_category()
  {
    $data = array();
    if (isset($_POST['id']) && $_POST['id'] != "") {
      $data['sup_category_id'] = $_POST['id'];
    }
    $result = $this->Supplier->select_supplier_category("", $data);
    if ($result->num_rows() > 0) {
      echo json_encode(array("count" => $result->num_rows(), "data" => $result->result()));
    } else {
      echo json_encode(array("count" => $result->num_rows()));
    }
  }
}
