<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Tools 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('Material_model', 'Material');
    $this->load->model('Settings_model', 'Settings');
    $this->load->model('Tools_model', 'Tools');
    $this->load->model('Account_model', 'Account');
    $this->load->model('Labour_model', 'Labour');
    $this->load->library('Projects_management','projects_management');

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

    $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();
  }

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

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

    // $this->page_data['tool_racks'] = $this->Tools->select_tool_racks()->result();
    $this->page_data['page_name'] = 'tools_view';
    $this->load->view('Index', $this->page_data);
  }
  public function racks()
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_tools_management')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    $this->page_data['tools'] = $this->Tools->select_tools("","",array("t.name",'asc'))->result();
    $this->page_data['page_name'] = 'tool_racks';
    $this->load->view('Index', $this->page_data);
  }
  public function transfers()
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_tools_management')==0){
      redirect(base_url().'dashboard', 'refresh');
    }

    $this->page_data['tools'] = $this->Tools->select_tools("","",array("t.name",'asc'))->result();
    $this->page_data['page_name'] = 'tools_transfers';
    $this->load->view('Index', $this->page_data);
  }
  public function rent_tools()
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_tools_management')==0){
      redirect(base_url().'dashboard', 'refresh');
    }
    // $data['t.tool_type'] = 'rent';
    $data = array();
    $this->page_data['tools'] = $this->Tools->select_tools("*",$data,array("t.name",'asc'))->result();
    $this->page_data['page_name'] = 'rent_tools';
    $this->load->view('Index', $this->page_data);
  }
  public function payments($work_id="")
  {
    // Check Module is Enabled or Not
    if($this->config->item('app_module_tools_management')==0){
      redirect(base_url().'dashboard', 'refresh');
    }
    // $data['t.tool_type'] = 'rent';
    $data = array();
    $this->page_data['tools'] = $this->Tools->select_tools("*",$data,array("t.name",'asc'))->result();
    $this->page_data['page_name'] = 'payments';
    if($work_id!=""){
      $this->page_data['work_id'] = $work_id;
      $this->page_data['page_name'] = 'payments_work';
    }

    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->load->view('Index', $this->page_data);
  }
  public function select_tools(){

    $json_data=array();
    $j=0;

    $data=array();
    if(isset($_POST['category']) && $_POST['category']!='' && $_POST['category']!='all'){
      $data['t.category_id'] = $_POST['category'];
    }
    $result	= $this->Tools->select_tools("t.*,tc.cat_name,u.user_alias",$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=0;
    foreach($result_array as $row):
      $sl_no++;

      $btn_edit=$btn_purchase='';
      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='general_manager'){
        $btn_edit='<a style="margin-left: 5px;margin-right: 5px" id="tools_edit_btn" href="#tools_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_purchase='<a style="margin-left: 5px;margin-right: 5px" id="tools_purchase_add_btn" href="#tools_purchase_add_modal" data-toggle="modal" class="btn btn-warning m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="fa fa-shopping-cart"></i>
        </a>';
      }

      $btn_delete='';
      if($this->session->userdata('user_role')=='admin' || ($this->session->userdata('user_role')!='admin' && $this->session->userdata('user_id')==$row->created_by)) {
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="tools_delete_btn" href="#tools_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>';
      }

      $total_owned_qty = $row->total_owned_qty;
      $total_remains_outside_own = $this->Tools->select_tool_transfers("COALESCE(SUM(tt.balance_to_return),0) as total",array("tt.tool_id"=>$row->tool_id,"tt.transfer_from"=>0))->row()->total;

      $total_rented_qty = $this->Tools->select_tool_rent("COALESCE(SUM(tr.rented_qty),0) as total",array("tr.tool_id"=>$row->tool_id))->row()->total;
      $total_returned_qty = $this->Tools->select_tool_rent("COALESCE(SUM(tr.total_returned_qty),0) as total",array("tr.tool_id"=>$row->tool_id))->row()->total;
      $total_remains_outside_rent = $total_rented_qty - $total_returned_qty;

      $array[$j][]=$sl_no;
      $array[$j][]=$row->tool_id;
      $array[$j][]='<a href="#available_list_modal" data-toggle="modal" id="btn_get_list">'.$row->name.'</a>';
      $array[$j][]=$row->cat_name;
      $array[$j][]=$row->description;
      $array[$j][]=$row->opening_owned_qty;
      $array[$j][]=$row->total_purchased_qty;
      $array[$j][]=$row->damaged_qty;
      $array[$j][]=$row->total_owned_qty;
      $array[$j][]=$row->price;
      $array[$j][]=$total_owned_qty-$total_remains_outside_own;
      $array[$j][]=$total_remains_outside_own;
      $array[$j][]=$total_rented_qty;
      $array[$j][]=$total_returned_qty;
      $array[$j][]=$total_remains_outside_rent;
      if ($this->config->item('UI_hide_created_date_field')==0){
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created on : '.date('d-m-Y',strtotime($row->created_date))."</li><li>Created By : ".$row->user_alias."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->user_alias.'</li>';
      }
      $array[$j][]=$btn_purchase.$btn_edit.$btn_delete;
      $array[$j][]=$row->category_id;
      $array[$j][]=$row->tool_type;

      $j++;
    endforeach;

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

  public function select_tools_payments(){

    $json_data = $array = array();
    $j = $sl_no = $total_incoming_qty = $total_returned_qty = $total_balance_qty = $total_conusmed_amt = $total_paid_amt = $total_balance_amt = 0;
    $data_rent = $data_transfer = array();

    $btn_view_details = '<a style="margin-left: 5px;margin-right: 5px" id="btn_consumption_details" href="#tool_consumption_view_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-eye"></i></a>';

    if(isset($_POST['work_id']) && $_POST['work_id']!='' && $_POST['work_id']!='all'){
      $data_rent['tr.rented_to'] = $data_transfer['tt.transfer_to'] = $_POST['work_id'];
    }

    $type = 'all';
    if(isset($_POST['type']) && $_POST['type']!='' && $_POST['type']!='all'){
      $type = $_POST['type'];
    }
    $data_transfer['tt.transfer_to !='] = 0;
    if($type=='all' || $type=='rent'){
      // Tool Rent
      if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
        $this->db->where_in('tr.rented_to',explode(',',$_POST['work_ids']));
      }
      $rented_tools_result = $this->Tools->select_tool_rent("tr.*,t.name,t.tool_id,t.total_owned_qty,t.price,wc2.work_name as rented_to_location,wc2.work_id as rented_to_location_id,tr.total_returned_qty as total_total_returned_qty,tc.cat_name",$data_rent,$order_by_column="",$order_by="",$group_by="");
      foreach($rented_tools_result->result() as $row):
        $price_perday = $row->rent_price;
        $returned_tool_cost = $balance_to_return_cost = 0;
        if($row->returned_log!=''){
          $returned_log = unserialize($row->returned_log);
        }else{
          $returned_log = array();
        }
        $return_size = sizeof($returned_log);
        $total_incoming_qty += $row->rented_qty;

        for($i=0;$i<$return_size;$i++){
          $returned_date = strtotime($returned_log[$i]['returned_date']);
          $rented_date = strtotime($row->rented_date);
          $days_used = ceil(abs(($rented_date - $returned_date)/(60*60*24)))+1;
          $returned_qty = $returned_log[$i]['returned_qty'];
          $returned_tool_cost += $price_perday*$days_used*$returned_qty;
        }

        if($row->balance_to_return>0){
          $current_date = strtotime(date('Y-m-d'));
          $rented_date = strtotime($row->rented_date);
          $days_used = ceil(abs(($rented_date - $current_date)/(60*60*24)))+1;
          $balance_to_return_cost += $price_perday*$days_used*$row->balance_to_return;
        }

        $paid_amount=$this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $row->id, "ats.reference_type" => 'tool_rent',"ats.tr_type" => 'tool_payment_transaction'),'tool')->row()->total_paid_amount;

        $total_conusmed_amt += $payable_amount = $returned_tool_cost+$balance_to_return_cost;
        $total_returned_qty += $row->total_returned_qty;
        $total_balance_qty += $row->balance_to_return;
        $total_paid_amt += $paid_amount;
        $total_balance_amt += $balance_amount = ($payable_amount-$paid_amount);

        $btn_add_payment = '';
        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="tool_rent" id="btn_add_bill_payment" href="#bill_payment_add_modal" data-toggle="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>';
        }

        $sl_no++;
        $array[$j][] = $sl_no;
        $array[$j][] = $this->config->item('Tools_rent_unique_id_prefix').date('ymd',strtotime($row->rented_date)).sprintf("%03d", $row->id);
        $array[$j][] = $row->rented_from;
        $array[$j][] = '<a href="'.base_url().'work/profile/'.$row->rented_to_location_id.'">'.$row->rented_to_location.'</a>';
        $array[$j][] = ucfirst($row->name).' [ '.$row->cat_name.']';
        $array[$j][] = "Rent";
        $array[$j][] = $price_perday;
        $array[$j][] = $row->rented_qty;
        $array[$j][] = $row->total_returned_qty;
        $array[$j][] = $row->balance_to_return;
        $array[$j][] = $this->config->item('Currency_currency_format').' '.($payable_amount);
        $array[$j][] = $paid_amount;
        $array[$j][] = $balance_amount;
        $array[$j][] = $btn_view_details.$btn_add_payment;
        $array[$j][] = $row->id;
        $array[$j][] = $row->tool_id;
        $array[$j][] = $row->rented_to_location;

        $j++;
      endforeach;
    }
    if($type=='all' || $type=='transfer'){
      //Tool Transfer
      if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
        $this->db->where_in('tt.transfer_to',explode(',',$_POST['work_ids']));
      }
      $transfer_result	= $this->Tools->select_tool_transfers("tt.*,t.name,tc.cat_name,t.price,wc1.work_name as tranfer_from_location,wc2.work_name as transfer_to_location,wc2.work_id as transfer_to_location_id",$data_transfer,$order_by_column="",$order_by="",$group_by="");
      foreach($transfer_result->result() as $row):
        $price_perday = $row->price;
        $returned_tool_cost = $balance_to_return_cost = 0;
        if($row->returned_log!=''){
          $returned_log = unserialize($row->returned_log);
        }else{
          $returned_log = array();
        }
        $return_size = sizeof($returned_log);
        $total_incoming_qty += $row->transferred_qty;

        for($i=0;$i<$return_size;$i++){
          $returned_date = strtotime($returned_log[$i]['returned_date']);
          $transfer_date = strtotime($row->transfer_date);
          $days_used = ceil(abs(($transfer_date - $returned_date)/(60*60*24)))+1;
          $returned_qty = $returned_log[$i]['returned_qty'];
          $returned_tool_cost += $price_perday*$days_used*$returned_qty;
        }

        if($row->balance_to_return>0){
          $current_date = strtotime(date('Y-m-d'));
          $transfer_date = strtotime($row->transfer_date);
          $days_used = ceil(abs(($transfer_date - $current_date)/(60*60*24)))+1;
          $balance_to_return_cost += $price_perday*$days_used*$row->balance_to_return;
        }

        $paid_amount=$this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $row->transfer_id, "ats.reference_type" => 'tool_transfer',"ats.tr_type" => 'tool_payment_transaction'),'tool')->row()->total_paid_amount;

        $total_conusmed_amt += $payable_amount = $returned_tool_cost+$balance_to_return_cost;
        $total_returned_qty += $row->total_returned_qty;
        $total_balance_qty += $row->balance_to_return;
        $total_paid_amt += $paid_amount;
        $total_balance_amt += $balance_amount = ($payable_amount-$paid_amount);

        if($row->transfer_from!=0){
          $transfer_from = $row->tranfer_from_location;
        }else{
          $transfer_from = $this->config->item('Tools_transfer_default_location_name');
        }

        $btn_add_payment = '';
        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="tool_transfer" id="btn_add_bill_payment" href="#bill_payment_add_modal" data-toggle="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>';
        }

        $sl_no++;
        $array[$j][] = $sl_no;
        $array[$j][] = '';
        $array[$j][] = $transfer_from;
        $array[$j][] = '<a href="'.base_url().'work/profile/'.$row->transfer_to_location_id.'">'.$row->transfer_to_location.'</a>';
        $array[$j][] = ucfirst($row->name).' [ '.$row->cat_name.']';
        $array[$j][] = "Transfer (Owned Tool)";
        $array[$j][] = $price_perday;
        $array[$j][] = $row->transferred_qty;
        $array[$j][] = $row->total_returned_qty;
        $array[$j][] = $row->balance_to_return;
        if($this->config->item('Tools_owned_tool_chargeable')==1){
          $array[$j][] = $this->config->item('Currency_currency_format').' '.($payable_amount);
          $array[$j][] = $paid_amount;
          $array[$j][] = $balance_amount;
        }else{
          $array[$j][] = '';
          $array[$j][] = '';
          $array[$j][] = '';
        }
        $array[$j][] = $btn_view_details.$btn_add_payment;
        $array[$j][] = $row->transfer_id;
        $array[$j][] = $row->tool_id;
        $array[$j][] = $row->transfer_to_location;

        $j++;
      endforeach;
    }

    if(sizeof($array)>0){
      $sl_no++;
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '<strong>Total</strong>';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '<strong>'.$total_incoming_qty.'</strong>';
      $array[$j][] = '<strong>'.$total_returned_qty.'</strong>';
      $array[$j][] = '<strong>'.$total_balance_qty.'</strong>';
      $array[$j][] = '<strong>'.'<strong>'.$this->config->item('Currency_currency_format').' '.($total_conusmed_amt).'</strong>';
      $array[$j][] = '<strong>'.$total_paid_amt.'</strong>';
      $array[$j][] = '<strong>'.$total_balance_amt.'</strong>';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';

      $j++;
    }

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

    $json_data=array();
    $j=0;

    $data=array();
    if(isset($_POST['category']) && $_POST['category']!='' && $_POST['category']!='all'){
      $data['t.category_id'] = $_POST['category'];
    }
    if(isset($_POST['tool_id']) && $_POST['tool_id']!='' && $_POST['tool_id']!='all'){
      $data['t.tool_id'] = $_POST['tool_id'];
    }
    if(isset($_POST['rented_to']) && $_POST['rented_to']!='' && $_POST['rented_to']!='all'){
      $data['tr.rented_to'] = $_POST['rented_to'];
    }
    if(isset($_POST['rented_to_work_ids']) && $_POST['rented_to_work_ids']!='' && $_POST['rented_to_work_ids']!='all'){
      $this->db->where_in('tr.rented_to',explode(',',$_POST['rented_to_work_ids']));
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $data['tr.rented_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $data['tr.rented_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['tr.created_by'] = $_POST['user_id'];
    }
    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(tr.rented_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(tr.rented_date,'%Y-%m-%d') >="]= $start_date;
    }
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc2.assigned_users)");
    }
    $entry_approval = 1;
    if($page_data_type=='approval_entries'){
      $entry_approval = 'all';
      $data['u.user_role >'] =2;
      if(isset($_POST['approval_status']) && $_POST['approval_status']!='' && $_POST['approval_status']!='all'){
        $entry_approval = $_POST['approval_status'];
      }
    }
    $result = $this->Tools->select_tool_rent("tr.*,t.name,t.total_owned_qty,wc2.work_name as rented_to_location,u.user_alias,u.user_role as created_user_role_id",$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();
    $sl_no=0;

    foreach($result_array as $row):
      $sl_no++;
      $btn_return = $btn_delete = $btn_edit = '';
      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='general_manager' || $this->session->userdata('user_id')==$row->created_by) {
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="tool_rent_delete_btn" href="#tool_rent_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="tool_rent_edit_btn" href="#tool_rent_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_return = '<a style="margin-left: 5px;margin-right: 5px" id="btn_return_rent" href="#rent_return_modal" data-toggle="modal" class="btn btn-warning m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air"><i class="la la-share"></i></a>';
      }

      if($row->balance_to_return<=0){
        $btn_return = '';
      }

      $return_status = '';
      if($row->return_status=='1'){
        $return_status = 'Returned';
      }elseif($row->return_status=='2'){
        $return_status = 'Partially Returned';
      }else{
        $return_status = 'Not Returned';
      }

      if($row->rented_to!=0){
        $rented_to = $row->rented_to_location;
      }else{
        $rented_to = $this->config->item('Tools_transfer_default_location_name');
      }

      $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="tool_rent" data-approval-status="'.$row->entry_approval_status.'" data-id="'.$row->id.'" id="btn_data_entry_approval" href="#data_entry_approve_modal" data-toggle="modal" class="btn btn-sm btn-warning m-btn m-btn--icon  m-btn--pill m-btn--air">
        <i class="la la-check-circle"></i> / <i class="la la-times-circle"></i>
        </a>';
      }
      if($page_data_type=='approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval')==1){
        if($row->entry_approval_status==1){
          $entry_approval_status="<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Approved</span>";
        }elseif($row->entry_approval_status==2){
          $entry_approval_status="<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> Rejected</span>";
        }else{
          $entry_approval_status="<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> Pending</span>";
        }
      }

      $array[$j][]=$sl_no;
      $array[$j][]=$row->id;
      if($this->config->item('Tools_rent_unique_id')==1){
        $array[$j][] = $this->config->item('Tools_rent_unique_id_prefix').date('ymd',strtotime($row->rented_date)).sprintf("%03d", $row->id);
      }else{
        $array[$j][]='';
      }
      $array[$j][]=$row->name;
      $array[$j][]=$row->rented_from;
      $array[$j][]=$rented_to;
      $array[$j][]=$row->rented_qty;
      $array[$j][]=$row->rent_price;
      $array[$j][]=date('d-m-Y',strtotime($row->rented_date));
      $array[$j][]=$row->total_returned_qty;
      $array[$j][]=$return_status;
      $array[$j][]=$row->balance_to_return;
      $array[$j][]=$row->remarks;
      $array[$j][]=$row->user_alias.$entry_approval_status;
      $array[$j][]=$btn_return.$btn_edit.$btn_delete.$btn_approve;
      $array[$j][]=$row->tool_id;
      $array[$j][]=$row->rented_from;
      $array[$j][]=$row->rented_to;
      $array[$j][]=$row->total_returned_qty;

      $j++;
    endforeach;

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

    $json_data=array();
    $j=0;

    $data=array();
    if(isset($_POST['category']) && $_POST['category']!='' && $_POST['category']!='all'){
      $data['t.category_id'] = $_POST['category'];
    }
    if(isset($_POST['tool_id']) && $_POST['tool_id']!='' && $_POST['tool_id']!='all'){
      $data['t.tool_id'] = $_POST['tool_id'];
    }
    if(isset($_POST['rented_to']) && $_POST['rented_to']!='' && $_POST['rented_to']!='all'){
      $data['tr.rented_to'] = $_POST['rented_to'];
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $data['tr.rented_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $data['tr.rented_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['tr.created_by'] = $_POST['user_id'];
    }
    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(tr.rented_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(tr.rented_date,'%Y-%m-%d') >="]= $start_date;
    }
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc2.assigned_users)");
    }
    $entry_approval = 1;
    if($page_data_type=='approval_entries'){
      $entry_approval = 'all';
      $data['u.user_role >'] =2;
      if(isset($_POST['approval_status']) && $_POST['approval_status']!='' && $_POST['approval_status']!='all'){
        $entry_approval = $_POST['approval_status'];
      }
    }
    $data['tr.return_status !='] = 0;
    $result = $this->Tools->select_tool_rent("tr.*,t.name,t.total_owned_qty,wc2.work_name as rented_to_location,u.user_alias,u.user_role as created_user_role_id",$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();
    $sl_no=0;

    foreach($result_array as $row):
      $sl_no++;
      $btn_return = $btn_delete = $btn_edit = '';

      $returned_log = array();
      if($row->returned_log!=''){
        $returned_log = unserialize($row->returned_log);
      }
      if($row->rented_to!=0){
        $rented_to = $row->rented_to_location;
      }else{
        $rented_to = $this->config->item('Tools_transfer_default_location_name');
      }
      foreach ($returned_log as $key => $value) {

        $array[$j][]=$sl_no;
        $array[$j][]=$row->id;
        if($this->config->item('Tools_rent_unique_id')==1){
          $array[$j][] = $this->config->item('Tools_rent_return_unique_id_prefix').date('ymd',strtotime($row->rented_date)).sprintf("%03d", $row->id);
        }else{
          $array[$j][]='';
        }
        $array[$j][]=$row->name;
        $array[$j][]=$row->rented_from;
        $array[$j][]=$rented_to;
        $array[$j][]=$row->rent_price;
        $array[$j][]=$value['returned_qty'];
        $array[$j][]=date('d-m-Y',strtotime($value['returned_date']));

        $j++;
      }
    endforeach;

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

    $json_data=array();
    $j=0;

    $data=array();
    if(isset($_POST['category']) && $_POST['category']!='' && $_POST['category']!='all'){
      $data['t.category_id'] = $_POST['category'];
    }
    if(isset($_POST['tool_id']) && $_POST['tool_id']!='' && $_POST['tool_id']!='all'){
      $data['t.tool_id'] = $_POST['tool_id'];
    }
    if(isset($_POST['transfer_from']) && $_POST['transfer_from']!='' && $_POST['transfer_from']!='all'){
      $data['tt.transfer_from'] = $_POST['transfer_from'];
    }
    if(isset($_POST['transfer_to']) && $_POST['transfer_to']!='' && $_POST['transfer_to']!='all'){
      $data['tt.transfer_to'] = $_POST['transfer_to'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('tt.transfer_to',explode(',',$_POST['work_ids']));
    }
    if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $data['tt.created_by'] = $_POST['user_id'];
    }
    if(isset($_POST['work_id']) && $_POST['work_id']!='' && $_POST['work_id']!='all'){
      $work_id = $_POST['work_id'];
      $this->db->where(('tt.transfer_to ='.$work_id.' or tt.transfer_from ='.$work_id));
    }
    if(isset($_POST['start_date']) && $_POST['start_date']!='' && $_POST['start_date']!='all'){
      $data['tt.transfer_date >='] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!='' && $_POST['end_date']!='all'){
      $data['tt.transfer_date <='] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    if(isset($_POST['date']) && $_POST['date']!=''){
      $date=array();
      $date=explode('-',$_POST['date']);
      $start_date=date('Y-m-d', strtotime($date[0]));
      $end_date=date('Y-m-d', strtotime($date[1]));
      $data["STR_TO_DATE(tt.transfer_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(tt.transfer_date,'%Y-%m-%d') >="]= $start_date;
    }
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, wc1.assigned_users)");
    }
    $entry_approval = 1;
    if($page_data_type=='approval_entries'){
      $entry_approval = 'all';
      $data['u.user_role >'] =2;
      if(isset($_POST['approval_status']) && $_POST['approval_status']!='' && $_POST['approval_status']!='all'){
        $entry_approval = $_POST['approval_status'];
      }
    }
    $result	= $this->Tools->select_tool_transfers("tt.*,t.name,wc1.work_name as tranfer_from_location,wc2.work_name as transfer_to_location,u.user_alias,u.user_role as created_user_role_id",$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();
    $sl_no=0;
    foreach($result_array as $row):
      $sl_no++;

      $btn_edit='';
      if($this->session->userdata('user_role')!='auditor'){
        $btn_edit='<a style="margin-left: 5px;margin-right: 5px" id="tool_transfer_edit_btn" href="#tool_transfer_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=$btn_return='';

      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='general_manager' || ($this->session->userdata('user_role')!='admin' && $this->session->userdata('user_id')==$row->created_by)) {
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="tool_transfer_delete_btn" href="#tool_transfer_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_return = '<a style="margin-left: 5px;margin-right: 5px" id="btn_return_transfer" href="#transfer_return_modal" data-toggle="modal" class="btn btn-warning m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air"><i class="la la-share"></i></a>';
      }
      if($row->transfer_from!=0){
        $transfer_from = $row->tranfer_from_location;
      }else{
        $transfer_from = $this->config->item('Tools_transfer_default_location_name');
      }

      if($row->transfer_to!=0){
        $transfer_to = $row->transfer_to_location;
      }else{
        $transfer_to = $this->config->item('Tools_transfer_default_location_name');
      }

      if($row->balance_to_return<=0){
        $btn_return = '';
      }

      $return_status = '';
      if($row->return_status=='1'){
        $return_status = 'Returned';
      }elseif($row->return_status=='2'){
        $return_status = 'Partially Returned';
      }else{
        $return_status = 'Not Returned';
      }

      $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="tool_transfer" data-approval-status="'.$row->entry_approval_status.'" data-id="'.$row->transfer_id.'" id="btn_data_entry_approval" href="#data_entry_approve_modal" data-toggle="modal" class="btn btn-sm btn-warning m-btn m-btn--icon  m-btn--pill m-btn--air">
        <i class="la la-check-circle"></i> / <i class="la la-times-circle"></i>
        </a>';
      }
      if($page_data_type=='approval_entries' && $this->config->item('Mobile_App_enable_data_entry_approval')==1){
        if($row->entry_approval_status==1){
          $entry_approval_status="<br/><span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Approved</span>";
        }elseif($row->entry_approval_status==2){
          $entry_approval_status="<br/><span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-check'></i> Rejected</span>";
        }else{
          $entry_approval_status="<br/><span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-times-circle'></i> Pending</span>";
        }
      }

      $array[$j][]=$sl_no;
      $array[$j][]=$row->transfer_id;
      $array[$j][]=$row->name;
      $array[$j][]=$transfer_from;
      $array[$j][]=$transfer_to;
      $array[$j][]=$row->transferred_qty;
      $array[$j][]=date('d-m-Y',strtotime($row->transfer_date));
      $array[$j][]=$row->transferred_by;
      $array[$j][]=$row->remarks;
      $array[$j][]=$row->total_returned_qty;
      $array[$j][]=$row->balance_to_return;
      $array[$j][]=$return_status;
      $array[$j][]=$row->user_alias.$entry_approval_status;
      $array[$j][]=$btn_return.$btn_edit.$btn_delete.$btn_approve;
      $array[$j][]=$row->tool_id;
      $array[$j][]=$row->transfer_from;
      $array[$j][]=$row->transfer_to;

      $j++;
    endforeach;

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

    $json_data=array();
    $j=0;

    $data=array();
    if(isset($_POST['category']) && $_POST['category']!='' && $_POST['category']!='all'){
      $data['t.category_id'] = $_POST['category'];
    }
    if(isset($_POST['tool_id']) && $_POST['tool_id']!='' && $_POST['tool_id']!='all'){
      $data['t.tool_id'] = $_POST['tool_id'];
    }
    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(tp.purchase_date,'%Y-%m-%d') <="]= $end_date;
      $data["STR_TO_DATE(tp.purchase_date,'%Y-%m-%d') >="]= $start_date;
    }
    $data["ats.tr_type"] = 'tool_purchase_transaction';
    $data["ats.reference_type"] = 'tool_purchase';
    $result	= $this->Account->select_account_transaction("ats.*,tp.*,t.name,tc.cat_name,t.description",$data,'tool_purchase');
    $result_array=$result->result();

    $json_data['draw']=5;
    $json_data['recordsTotal']=$result->num_rows();
    $json_data['recordsFiltered']=$result->num_rows();
    $array=array();
    $sl_no=$total_purchased_qty=$total_price=0;
    foreach($result_array as $row):
      $sl_no++;
      $btn_edit='';

      if($this->session->userdata('user_role')!='auditor'){
        $btn_edit='<a style="margin-left: 5px;margin-right: 5px" id="tools_purchase_edit_btn" href="#tools_purchase_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='';
      if($this->session->userdata('user_role')=='admin' || ($this->session->userdata('user_role')!='admin' && $this->session->userdata('user_id')==$row->created_by)) {
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="tools_purchase_delete_btn" href="#tools_purchase_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][]=$row->purchase_id;
      $array[$j][]=date('d-m-Y',strtotime($row->purchased_date));
      $array[$j][]=$row->name;
      $array[$j][]=$row->cat_name;
      $array[$j][]=$row->purchased_from;
      $array[$j][]=$row->purchased_qty;
      $array[$j][]=$row->total_price;
      $array[$j][]=round(($row->total_price/$row->purchased_qty),$this->config->item('Currency_amount_decimals_2'));
      $array[$j][]=$row->remarks;
      $array[$j][]=$btn_edit.$btn_delete;
      $array[$j][]=$row->tool_id;
      $array[$j][]=$row->transaction_id;
      $array[$j][]=$row->tr_debit_acc;
      $array[$j][]=$row->tr_mode;
      $array[$j][]=$row->tr_reference_no;
      $array[$j][]=($row->tr_reference_date!='0000-00-00')?date('d-m-Y',strtotime($row->tr_reference_date)):"";
      $array[$j][]=$row->description;

      $total_purchased_qty+=$row->purchased_qty;
      $total_price+=$row->total_price;

      $j++;
    endforeach;

    if($result->num_rows()>0){
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]="<strong>Total</strong>";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]="<strong>".$total_purchased_qty."</strong>";
      $array[$j][]="<strong>".$total_price."</strong>";
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $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 create()
  {
    $flash_data = array();
    $data['name'] = $this->security->xss_clean($this->input->post('name'));
    $data['category_id'] = $this->security->xss_clean($this->input->post('category'));
    // $data['tool_type'] = $this->security->xss_clean($this->input->post('tool_type'));
    $count = $this->Tools->select_tools("tool_id",array('name' => $data['name'],'category_id' => $data['category_id']))->num_rows();
    $data['price'] = $this->security->xss_clean($this->input->post('price'));
    $data['description'] = $this->security->xss_clean($this->input->post('description'));
    $data['opening_owned_qty'] = $this->security->xss_clean($this->input->post('owned_qty'));
    // if($data['tool_type'] == 'on'){
    $data['tool_type'] = 'own';
    // }else{
    //   $data['tool_type'] = 'rent';
    // }
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');
    if($count==0) {
      $result = $this->Tools->create_tools($data);
      $flash_data['status'] = $result['status'];
      if ($result['status'] == 1) {
        $this->Tools->calculate_total_owned_qty($result['insert_id']);
        $flash_data['flashdata_msg'] = 'Tools 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'] = 0;
      $flash_data['flashdata_msg'] = 'Sorry.. A Tool with Same Name in Selected Category 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()
  {
    $flash_data = array();
    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool_id'));
    $data['name'] = $this->security->xss_clean($this->input->post('name'));
    $data['category_id'] = $this->security->xss_clean($this->input->post('category'));
    $count = $this->Tools->select_tools("tool_id",array('name' => $data['name'],'category_id' => $data['category_id'],'tool_id !=' => $data['tool_id']))->num_rows();
    $data['price'] = $this->security->xss_clean($this->input->post('price'));
    $data['description'] = $this->security->xss_clean($this->input->post('description'));
    $data['opening_owned_qty'] = $this->security->xss_clean($this->input->post('owned_qty'));
    $data['damaged_qty'] = $this->security->xss_clean($this->input->post('damaged_qty'));
    $data['updated_date'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('user_id');
    if($count==0) {
      $result = $this->Tools->update_tools($data);
      $flash_data['status'] = $result;
      if ($result == 1) {
        $this->Tools->calculate_total_owned_qty($data['tool_id']);
        $flash_data['flashdata_msg'] = 'Tool 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'] = 0;
      $flash_data['flashdata_msg'] = 'Sorry.. A Tool with Same Name in Selected Category 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()
  {
    $flash_data = array();
    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool_id'));
    $data['delete_status'] = 1;
    $result = $this->Tools->update_tools($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $flash_data['flashdata_msg'] = 'Tool 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 create_purchase()
  {
    $flash_data = array();
    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool_id'));
    $data['purchased_qty'] = $this->security->xss_clean($this->input->post('purchase_qty'));
    $data['purchased_date'] = date('Y-m-d',strtotime($this->input->post('paid_date')));
    $data['total_price'] = $this->security->xss_clean($this->input->post('paid_amount'));
    $data['purchased_from'] = $this->security->xss_clean($this->input->post('purchase_from'));
    $data['remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');
    $result = $this->Tools->create_tools_purchase($data);
    $flash_data['status'] = $result['status'];
    if ($result['status'] == 1) {
      $this->Tools->calculate_total_owned_qty($data['tool_id']);

      $transaction_data['reference_id'] = $result['insert_id'];
      $transaction_data['reference_type'] = "tool_purchase";
      $transaction_data['tr_type'] = "tool_purchase_transaction";
      $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'] = 17;//Tool Purchase payments
      $transaction_data['tr_description'] = "Tool Purchase Transaction";
      $transaction_data['tr_amount'] = $this->security->xss_clean($this->input->post('paid_amount'));
      $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_data['created_date'] = date('Y-m-d H:i:s');
      $transaction_data['created_by'] = $this->session->userdata('user_id');
      $transaction_result =  $this->Account->create_account_transaction($transaction_data);

      $flash_data['flashdata_msg'] = 'Tools Purchase Entry 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 !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];

    echo json_encode($flash_data);
  }
  public function update_purchase()
  {
    $flash_data = array();
    $data['purchase_id'] = $this->security->xss_clean($this->input->post('purchase_id'));
    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool_id'));
    $data['purchased_qty'] = $this->security->xss_clean($this->input->post('purchase_qty'));
    $data['purchased_date'] = date('Y-m-d',strtotime($this->input->post('paid_date')));
    $data['total_price'] = $this->security->xss_clean($this->input->post('paid_amount'));
    $data['purchased_from'] = $this->security->xss_clean($this->input->post('purchase_from'));
    $data['remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
    $data['updated_date'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('user_id');
    $result = $this->Tools->update_tools_purchase($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $this->Tools->calculate_total_owned_qty($data['tool_id']);
      // Update Transaction if Exist or Create
      $transaction_select_data = $this->Account->select_account_transaction("ats.transaction_id",array("tp.purchase_id" => $data['purchase_id'],"ats.tr_type" => 'tool_purchase_transaction',"ats.reference_type" => 'tool_purchase'),'tool_purchase');
      $transaction_data['reference_id'] = $data['purchase_id'];
      $transaction_data['reference_type'] = "tool_purchase";
      $transaction_data['tr_type'] = "tool_purchase_transaction";
      $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'] = 17;//Tool Purchase payments
      $transaction_data['tr_description'] = "Tool Purchase Transaction";
      $transaction_data['tr_amount'] = $this->security->xss_clean($this->input->post('paid_amount'));
      $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'));

      if($transaction_select_data->num_rows()==0){
        $transaction_data['created_date'] = date('Y-m-d H:i:s');
        $transaction_data['created_by'] = $this->session->userdata('user_id');
        $transaction_result =  $this->Account->create_account_transaction($transaction_data);
      }elseif($transaction_select_data->num_rows()==1){
        $transaction_data['transaction_id'] = $transaction_select_data->row()->transaction_id;
        $transaction_data['updated_date'] = date('Y-m-d H:i:s');
        $transaction_data['updated_by'] = $this->session->userdata('user_id');
        $transaction_result =  $this->Account->update_account_transaction($transaction_data);
      }
      $flash_data['flashdata_msg'] = 'Tools Purchase 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 !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];

    echo json_encode($flash_data);
  }
  public function delete_purchase()
  {
    $flash_data = array();
    $data['purchase_id'] = $this->security->xss_clean($this->input->post('purchase_id'));
    $data['delete_status'] = 1;
    $result = $this->Tools->update_tools_purchase($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $this->Tools->calculate_total_owned_qty($this->input->post('tool_id'));

      $flash_data['flashdata_msg'] = 'Tools Purchase 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 create_transfer()
  {
    $flash_data = array();
    $data['transfer_from'] = $this->security->xss_clean($this->input->post('transfer_from'));
    $data['transfer_to'] = $this->security->xss_clean($this->input->post('transfer_to'));

    if($data['transfer_from']!=0){
      $work_id = $data['transfer_from'];
    }else {
      $work_id = $data['transfer_to'];
    }
    $data['project_stage_id']=$this->projects_management->get_project_stage_id($work_id);

    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool'));
    $data['transferred_qty'] = $this->security->xss_clean($this->input->post('transferred_qty'));
    $data['balance_to_return'] = $data['transferred_qty'];
    $data['transfer_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('transferred_date'))));
    $data['transferred_by'] = $this->security->xss_clean($this->input->post('transferred_by'));
    $data['remarks'] = $this->security->xss_clean($this->input->post('remarks'));
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');
    $result = $this->Tools->create_tool_transfer($data);
    $flash_data['status'] = $result['status'];
    if ($result['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Tool Transfer 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 !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];

    echo json_encode($flash_data);
  }
  public function update_transfer()
  {
    $flash_data = array();
    $data['transfer_id'] = $this->security->xss_clean($this->input->post('transfer_id'));
    $data['transfer_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('transferred_date'))));
    $data['transferred_by'] = $this->security->xss_clean($this->input->post('transferred_by'));
    $data['remarks'] = $this->security->xss_clean($this->input->post('remarks'));
    $data['updated_date'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('user_id');
    $result = $this->Tools->update_tool_transfer($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $flash_data['flashdata_msg'] = 'Transfer 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 !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];

    echo json_encode($flash_data);
  }
  public function delete_transfer()
  {
    $flash_data = array();
    $data['transfer_id'] = $this->security->xss_clean($this->input->post('transfer_id'));
    $data['delete_status'] = 1;
    $result = $this->Tools->update_tool_transfer($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $flash_data['flashdata_msg'] = 'Tool Transfer 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 update_transfer_return()
  {
    $flash_data = array();
    $data['transfer_id'] = $this->security->xss_clean($this->input->post('transfer_id'));
    $transfer_log = $this->Tools->select_tool_transfers("tt.*,t.name,wc1.work_name as tranfer_from_location,wc2.work_name as transfer_to_location",array("tt.transfer_id"=>$data['transfer_id']));
    if($transfer_log->num_rows()==1){
      $transfer_log = $transfer_log->row();

      if($transfer_log->returned_log!=''){
        $returned_log = unserialize($transfer_log->returned_log);
        $array_index = sizeof($returned_log);
      }else{
        $array_index = 0;
      }
      $returned_log[$array_index]['returned_qty'] = $this->security->xss_clean($this->input->post('quantity'));
      $returned_log[$array_index]['returned_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('date'))));
      $returned_log[$array_index]['created_date'] = date('Y-m-d H:i:s');
      $returned_log[$array_index]['created_by'] = $this->session->userdata('user_id');
      $data['returned_log'] = serialize($returned_log);
      $data['total_returned_qty'] = $transfer_log->total_returned_qty + $this->security->xss_clean($this->input->post('quantity'));
      $data['balance_to_return'] = $transfer_log->transferred_qty-$data['total_returned_qty'];
      if($data['balance_to_return']==0){
        $data['return_status'] = 1;
      }elseif($data['balance_to_return']>0 && $data['balance_to_return']<$transfer_log->transferred_qty){
        $data['return_status'] = 2;
      }else{
        $data['return_status'] = 0;
      }
      $result = $this->Tools->update_tool_transfer($data);
      $flash_data['status'] = $result;
      if ($result == 1) {
        $flash_data['flashdata_msg'] = 'Tool Transfer Return Saved 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['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 create_rent()
  {
    $flash_data = array();
    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool'));
    $data['rented_from'] = $this->security->xss_clean($this->input->post('rented_from'));
    $data['rented_to'] = $this->security->xss_clean($this->input->post('rented_to'));
    $data['rent_price'] = $this->security->xss_clean($this->input->post('price'));
    $data['project_stage_id'] = $this->projects_management->get_project_stage_id($data['rented_to']);

    $data['rented_qty'] = $this->security->xss_clean($this->input->post('rented_qty'));
    $data['balance_to_return'] = $data['rented_qty'];
    $data['rented_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('rented_date'))));
    $data['remarks'] = $this->security->xss_clean($this->input->post('remarks'));
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');

    $result = $this->Tools->create_rent_tools($data);
    $flash_data['status'] = $result['status'];
    if ($result['status'] == 1) {
      $flash_data['rent_unique_id'] = '';
      if($this->config->item('Tools_rent_unique_id')==1){
        $flash_data['rent_unique_id'] = $this->config->item('Tools_rent_unique_id_prefix').date('ymd',strtotime($data['rented_date'])).sprintf("%03d", $result['insert_id']);
      }
      if($this->input->post('paid_amount')>0){
        $transaction_data['reference_id'] = $result['insert_id'];
        $transaction_data['reference_type'] = 'tool_rent';
        $transaction_data['tr_type'] = 'tool_payment_transaction';
        $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'] = 18;//Tool Payments
        $transaction_data['tr_description'] = "Tool Payment Transaction";
        $transaction_data['tr_amount'] = $this->security->xss_clean($this->input->post('paid_amount'));
        $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_data['created_date'] = date('Y-m-d H:i:s');
        $transaction_data['created_by'] = $this->session->userdata('user_id');
        $transaction_result =  $this->Account->create_account_transaction($transaction_data);
      }

      $flash_data['flashdata_msg'] = 'Tool Rent 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 !!';
    }

    $flash_data['message'] = $flash_data['flashdata_msg'];

    echo json_encode($flash_data);
  }
  public function update_rent()
  {
    $flash_data = array();
    $data['id'] = $this->security->xss_clean($this->input->post('rent_id'));
    $data['tool_id'] = $this->security->xss_clean($this->input->post('tool'));
    $data['rented_from'] = $this->security->xss_clean($this->input->post('rented_from'));
    $data['rented_to'] = $this->security->xss_clean($this->input->post('rented_to'));
    $data['rented_qty'] = $this->security->xss_clean($this->input->post('rented_qty'));
    $data['rent_price'] = $this->security->xss_clean($this->input->post('price'));
    $data['rented_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('rented_date'))));
    $data['remarks'] = $this->security->xss_clean($this->input->post('remarks'));
    $data['updated_date'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('user_id');
    $rent_log = $this->Tools->select_tool_rent("tr.return_status,tr.id,tr.rented_from,tr.rented_qty,tr.rented_date,tr.total_returned_qty,tr.balance_to_return,tr.returned_log",array("tr.id"=>$this->input->post('rent_id')));
    if($rent_log->num_rows()==1){
      $result = $this->Tools->update_tool_rent($data);
      $flash_data['status'] = $result;
      if ($result == 1) {
        $flash_data['flashdata_msg'] = 'Tool Rent 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['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
      $flash_data['flashdata_type'] = 'error';
      $flash_data['alert_type'] = 'danger';
      $flash_data['flashdata_title'] = 'Error !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];

    echo json_encode($flash_data);
  }
  public function delete_rent()
  {
    $flash_data = array();
    $data['id'] = $this->security->xss_clean($this->input->post('rent_id'));
    $data['delete_status'] = 1;
    $result = $this->Tools->update_tool_rent($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      $flash_data['flashdata_msg'] = 'Tool Rent 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 update_rent_return()
  {
    $flash_data = array();
    $data['id'] = $this->security->xss_clean($this->input->post('rent_id'));
    $rent_log = $this->Tools->select_tool_rent("tr.return_status,tr.id,tr.rented_from,tr.rented_qty,tr.rented_date,tr.total_returned_qty,tr.balance_to_return,tr.returned_log",array("tr.id"=>$this->security->xss_clean($this->input->post('rent_id'))));
    if($rent_log->num_rows()==1){
      $rent_log = $rent_log->row();

      if($rent_log->returned_log!=''){
        $returned_log = unserialize($rent_log->returned_log);
        $array_index = sizeof($returned_log);
      }else{
        $array_index = 0;
      }
      $returned_log[$array_index]['returned_qty'] = $this->security->xss_clean($this->input->post('quantity'));
      $returned_log[$array_index]['returned_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('date'))));
      $returned_log[$array_index]['created_date'] = date('Y-m-d H:i:s');
      $returned_log[$array_index]['created_by'] = $this->session->userdata('user_id');
      $data['returned_log'] = serialize($returned_log);
      $data['total_returned_qty'] = $rent_log->total_returned_qty + $this->security->xss_clean($this->input->post('quantity'));
      $data['balance_to_return'] = $rent_log->rented_qty-$data['total_returned_qty'];
      if($data['balance_to_return']==0){
        $data['return_status'] = 1;
      }elseif($data['balance_to_return']>0 && $data['balance_to_return']<$rent_log->rented_qty){
        $data['return_status'] = 2;
      }else{
        $data['return_status'] = 0;
      }
      $result = $this->Tools->update_tool_rent($data);
      $flash_data['status'] = $result;
      if ($result == 1) {
        $flash_data['rent_return_unique_id'] = '';
        if($this->config->item('Tools_rent_unique_id')==1){
          $flash_data['rent_return_unique_id'] = $this->config->item('Tools_rent_return_unique_id_prefix').date('ymd',strtotime($rent_log->rented_date)).sprintf("%03d", $rent_log->id);
        }
        $flash_data['flashdata_msg'] = 'Tool Rent Return Saved 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['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 create_category()
  {
    $flash_data = array();
    $data['cat_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Tools->select_tools_category("cat_id",array('cat_name' => $data['cat_name']))->num_rows();
    if($count==0) {
      $result = $this->Tools->create_tools_category($data);
      $flash_data['status'] = $result['status'];
      if ($result['status'] == 1) {
        $flash_data['flashdata_msg'] = 'Tools 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'] = 0;
      $flash_data['flashdata_msg'] = 'Sorry.. A 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 select_category_list(){
    $data = array();
    if(isset($_POST['id']) && $_POST['id']!=""){
      $data['tc.cat_id'] = $_POST['id'];
    }
    $result	= $this->Tools->select_tools_category("",$data);
    if($result->num_rows()>0) {
      foreach ($result->result() as $row):
        $id[] = $row->cat_id;
        $name[] = $row->cat_name;
      endforeach;
      echo json_encode(array("count" => $result->num_rows(),"id" => $id,"name" => $name));
    }else{
      echo json_encode(array("count" => $result->num_rows()));
    }
  }
  public function select_tools_list(){
    $data = array();
    if(isset($_POST['category_id']) && $_POST['category_id']!="" && $_POST['category_id']!="all"){
      $data['t.category_id'] = $_POST['category_id'];
    }
    if(isset($_POST['tool_id']) && $_POST['tool_id']!="" && $_POST['tool_id']!="all"){
      $data['t.tool_id'] = $_POST['tool_id'];
    }
    if(isset($_POST['type'])){
      $data['t.tool_type'] = $_POST['type'];
    }
    $result	= $this->Tools->select_tools("",$data,array("t.name",'asc'));
    if($result->num_rows()>0) {
      foreach ($result->result() as $row):
        $id[] = $row->tool_id;
        $name[] = $row->name;
        $price[] = $row->price;
      endforeach;
      echo json_encode(array("count" => $result->num_rows(),"id" => $id,"name" => $name,"price" => $price));
    }else{
      echo json_encode(array("count" => $result->num_rows()));
    }
  }
  public function select_available_locations($list_type=''){
    $j=0;$sl_no=1;$i=0;
    $data = $available_locations = $json_data = $data2 = $json_table_data = $table_data = array();
    if(isset($_POST['tool_id']) && $_POST['tool_id']!=""){
      $data['tt.tool_id'] = $data3['tr.tool_id'] = $data2['t.tool_id'] = $_POST['tool_id'];

      $data['tt.transfer_to !='] = 0;
      $tool_transfer_data = $this->Tools->select_tool_transfers("tt.transfer_to,wc2.work_name",$data,"","","","","","distinct");
      $tool_data	= $this->Tools->select_tools("name,total_owned_qty",$data2);

      // Check Office Location's Quantity
      $total_in_office = $tool_data->row()->total_owned_qty;
      // Get Total Remaining in Sites
      $total_out_office = $this->Tools->select_tool_transfers("COALESCE(SUM(tt.balance_to_return),0) as total",array("tt.tool_id"=>$data['tt.tool_id'],"tt.transfer_from"=>0))->row()->total;

      $available_locations[$i]['id'] = 0;
      $available_locations[$i]['name'] = $this->config->item('Tools_transfer_default_location_name');
      $available_locations[$j]['qty']=$total_in_office-$total_out_office;

      $table_data[$j][]=$sl_no;
      $table_data[$j][]=$tool_data->row()->name;
      $table_data[$j][]=$this->config->item('Tools_transfer_default_location_name');
      $table_data[$j][]=$total_in_office-$total_out_office;
      $j++;

      $available_locations = array_map("unserialize", array_unique(array_map("serialize", $available_locations)));
      $json_data['count'] = sizeof($available_locations);
      $json_data['locations'] = $available_locations;

      if($list_type=='table'){
        $json_table_data['data']=$table_data;
        $json_table_data['draw']=5;
        $json_table_data['recordsTotal']=sizeof($table_data);
        $json_table_data['recordsFiltered']=sizeof($table_data);
        echo json_encode($json_table_data);
      }else{
        echo json_encode($json_data);
      }
    }else{
      $json_data['count'] = 0;
      echo json_encode($json_data);
    }

  }
  public function select_all_available_locations_list($list_type=''){
    $j=$sl_no=$i=0;
    $transfer_data = $rent_data = $table_data = array();
    if(isset($_POST['tool_id']) && $_POST['tool_id']!=""){
      $transfer_data['tt.tool_id'] = $rent_data['tr.tool_id'] = $data['t.tool_id'] = $_POST['tool_id'];

      $transfer_data['tt.balance_to_return >'] = 0;
      $rent_data['tr.balance_to_return >'] = 0;
      $tool_transfer_data = $this->Tools->select_tool_transfers("COALESCE(SUM(tt.balance_to_return),0) as total,tt.transfer_to,wc2.work_name,balance_to_return",$transfer_data,'','','tt.transfer_to')->result();
      $tool_rent_data = $this->Tools->select_tool_rent("COALESCE(SUM(tr.balance_to_return),0) as total,wc2.work_name,balance_to_return",$rent_data,'','','tr.rented_to')->result();

      $tool_data	= $this->Tools->select_tools("name,total_owned_qty",$data);
      // Check Office Location's Quantity
      $total_in_office = $tool_data->row()->total_owned_qty;
      // Get Total Remaining in Sites
      $total_out_office = $this->Tools->select_tool_transfers("COALESCE(SUM(tt.balance_to_return),0) as total",array("tt.tool_id"=>$transfer_data['tt.tool_id'],"tt.transfer_from"=>0))->row()->total;

      if($total_in_office-$total_out_office>0){
        $sl_no++;
        $table_data[$j][]=$sl_no;
        $table_data[$j][]=$tool_data->row()->name;
        $table_data[$j][]='Owned';
        $table_data[$j][]=$this->config->item('Tools_transfer_default_location_name');
        $table_data[$j][]=$total_in_office-$total_out_office;
        $j++;
      }
      foreach ($tool_transfer_data as $key => $value) {
        $sl_no++;
        $table_data[$j][]=$sl_no;
        $table_data[$j][]=$tool_data->row()->name;
        $table_data[$j][]='Owned';
        $table_data[$j][]=$value->work_name;
        $table_data[$j][]=$value->total;
        $j++;
      }
      foreach ($tool_rent_data as $key => $value) {
        $sl_no++;
        $table_data[$j][]=$sl_no;
        $table_data[$j][]=$tool_data->row()->name;
        $table_data[$j][]='Rent';
        $table_data[$j][]=$value->work_name;
        $table_data[$j][]=$value->total;
        $j++;
      }

    }

    $json_table_data['data']=$table_data;
    $json_table_data['draw']=5;
    $json_table_data['recordsTotal']=sizeof($table_data);
    $json_table_data['recordsFiltered']=sizeof($table_data);
    echo json_encode($json_table_data);
  }
  public function select_available_qty(){

    $data = $json_data= array();

    if(isset($_POST['location_id']) && $_POST['location_id']!="" && isset($_POST['tool_id']) && $_POST['tool_id']!=""){
      $data['tt.tool_id'] = $data2['t.tool_id'] = $_POST['tool_id'];
      $location_id = $_POST['location_id'];
      $result2	= $this->Tools->select_tools("total_owned_qty",$data2);

      if($location_id==0 && $result2->num_rows()==1){
        $available_qty = $result2->row()->total_owned_qty;
        $available_qty -= $this->Tools->select_tool_transfers("COALESCE(SUM(tt.balance_to_return),0) as total",array("tt.tool_id"=>$data['tt.tool_id'],"tt.transfer_from"=>$location_id))->row()->total;
      }elseif($location_id!=0 && $result2->num_rows()==1){
        // get Total Transferred In
        $available_qty = $this->Tools->select_tool_transfers("COALESCE(SUM(tt.balance_to_return),0) as total",array("tt.tool_id"=>$data['tt.tool_id'],"tt.transfer_to"=>$location_id))->row()->total;
      }

      $json_data['count'] = 1;
      $json_data['available_qty'] = $available_qty;
    }
    elseif(isset($_POST['type']) && $_POST['type']=="rent" && isset($_POST['tool_id']) && $_POST['tool_id']!=""){
      $data['tr.tool_id'] =  $data2['t.tool_id'] = $_POST['tool_id'];
      $total_in =  $this->Tools->select_tool_rent("COALESCE(SUM(tr.rented_qty),0) as total",array("tr.tool_id"=>$data['tr.tool_id']))->row()->total;

      $total_out = $this->Tools->select_tool_rent("COALESCE(SUM(tr.total_returned_qty),0) as total",array("tr.tool_id"=>$data['tr.tool_id']))->row()->total;

      if(!isset($total_out)){
        $available_qty = $total_in;
      }
      else{
        $available_qty = $total_in - $total_out;
      }

      $json_data['count'] = 1;
      $json_data['available_qty'] = $available_qty;
    }
    else{
      $json_data['count'] = 0;
    }

    echo json_encode($json_data);
  }
  public function select_locations(){

    $data = $location_id = $location_name = $json_data= array();
    if(isset($_POST['location_id']) && $_POST['location_id']!="" && $_POST['location_id']=='all'){
      $location_id[] = 0;
      $location_name[] = $this->config->item('Tools_transfer_default_location_name');

      $json_data['count'] = sizeof($location_id);
      $json_data['id'] = $location_id;
      $json_data['name'] = $location_name;
    }else{
      $data['work_id !='] = 0;
      $result	= $this->Work->select_work("work_id,work_name", $data);
      if($result->num_rows()>0) {
        foreach ($result->result() as $row):
          $location_id[] = $row->work_id;
          $location_name[] = $row->work_name;
        endforeach;

        $json_data['count'] = sizeof($location_id);
        $json_data['id'] = $location_id;
        $json_data['name'] = $location_name;
      }else{
        $json_data['count'] = 0;
      }
    }
    echo json_encode($json_data);
  }
  public function select_consumption_details($list_type=''){
    $j=$sl_no=$i=0;
    $transfer_data = $rent_data = $table_data = array();
    if(isset($_POST['id']) && $_POST['id']!="" && isset($_POST['type']) && $_POST['type']!=""){
      $type = $_POST['type'];
      $transfer_data['tt.transfer_id'] = $rent_data['tr.id'] = $_POST['id'];

      if($type=='transfer'){
        $result = $this->Tools->select_tool_transfers("tt.*,wc2.work_name,t.name,t.price",$transfer_data);
        if($result->num_rows()==1){
          $row = $result->row();

          $sl_no++;
          $table_data[$j][]=$sl_no;
          $table_data[$j][]=date('d-m-Y',strtotime($row->transfer_date));
          $table_data[$j][]=$row->name." (".$row->price."/Day)";
          $table_data[$j][]=$row->work_name;
          $table_data[$j][]='Tool Transferred From Office';
          $table_data[$j][]=$row->transferred_qty;
          $table_data[$j][]='';
          $table_data[$j][]='';
          $j++;

          $price_perday = $row->price;
          $returned_tool_cost = $balance_to_return_cost = 0;
          if($row->returned_log!=''){
            $returned_log = unserialize($row->returned_log);
          }else{
            $returned_log = array();
          }
          $return_size = sizeof($returned_log);

          for($i=0;$i<$return_size;$i++){

            $returned_date = strtotime($returned_log[$i]['returned_date']);
            $transfer_date = strtotime($row->transfer_date);
            $days_used = ceil(abs(($transfer_date - $returned_date)/(60*60*24)))+1;
            $returned_qty = $returned_log[$i]['returned_qty'];
            $returned_tool_cost = $price_perday*$days_used*$returned_qty;

            $sl_no++;
            $table_data[$j][]=$sl_no;
            $table_data[$j][]=date('d-m-Y',strtotime($returned_log[$i]['returned_date']));
            $table_data[$j][]=$row->name." (".$row->price."/Day)";
            $table_data[$j][]=$row->work_name;
            $table_data[$j][]='Tool Returned';
            $table_data[$j][]=$returned_log[$i]['returned_qty'];
            $table_data[$j][]=$days_used;
            if($this->config->item('Tools_owned_tool_chargeable')==1){
              $table_data[$j][]="Estimated : ".$returned_tool_cost;
            }else{
              $table_data[$j][]="";
            }
            $j++;

          }
          $payments=$this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user", array("ats.reference_id" =>$transfer_data['tt.transfer_id'], "ats.reference_type" => 'tool_transfer',"ats.tr_type" => 'tool_payment_transaction'),'tool')->result();
          foreach ($payments as $key => $value) {
            $sl_no++;
            $table_data[$j][]=$sl_no;
            $table_data[$j][]=date('d-m-Y',strtotime($value->tr_date));
            $table_data[$j][]=$row->name." (".$row->price."/Day)";
            $table_data[$j][]=$row->work_name;
            $table_data[$j][]='Tool Transfer Payment to Office';
            $table_data[$j][]='';
            $table_data[$j][]='';
            $table_data[$j][]="Paid Amount : ".$value->tr_amount;
            $j++;
          }

          if($row->balance_to_return>0){
            $current_date = strtotime(date('Y-m-d'));
            $transfer_date = strtotime($row->transfer_date);
            $days_used = ceil(abs(($transfer_date - $current_date)/(60*60*24)))+1;
            $balance_to_return_cost = $price_perday*$days_used*$row->balance_to_return;

            $sl_no++;
            $table_data[$j][]=$sl_no;
            $table_data[$j][]=date('d-m-Y');
            $table_data[$j][]=$row->name." (".$row->price."/Day)";
            $table_data[$j][]=$row->work_name;
            $table_data[$j][]='Balance to Return';
            $table_data[$j][]=$row->balance_to_return;
            $table_data[$j][]=$days_used;
            $table_data[$j][]="Estimated : ".$balance_to_return_cost;
            $j++;
          }

        }
      }else{
        $result = $this->Tools->select_tool_rent("tr.*,wc2.work_name,t.name,t.price",$rent_data);
        if($result->num_rows()==1){
          $row = $result->row();

          $sl_no++;
          $table_data[$j][]=$sl_no;
          $table_data[$j][]=date('d-m-Y',strtotime($row->rented_date));
          $table_data[$j][]=$row->name." (".$row->price."/Day)";
          $table_data[$j][]=$row->work_name;
          $table_data[$j][]='Tool Rented From '.$row->rented_from;
          $table_data[$j][]=$row->rented_qty;
          $table_data[$j][]='';
          $table_data[$j][]='';
          $j++;

          $price_perday = $row->price;
          $returned_tool_cost = $balance_to_return_cost = 0;
          if($row->returned_log!=''){
            $returned_log = unserialize($row->returned_log);
          }else{
            $returned_log = array();
          }
          $return_size = sizeof($returned_log);

          for($i=0;$i<$return_size;$i++){

            $returned_date = strtotime($returned_log[$i]['returned_date']);
            $rented_date = strtotime($row->rented_date);
            $days_used = ceil(abs(($rented_date - $returned_date)/(60*60*24)))+1;
            $returned_qty = $returned_log[$i]['returned_qty'];
            $returned_tool_cost = $price_perday*$days_used*$returned_qty;

            $sl_no++;
            $table_data[$j][]=$sl_no;
            $table_data[$j][]=date('d-m-Y',strtotime($returned_log[$i]['returned_date']));
            $table_data[$j][]=$row->name." (".$row->price."/Day)";
            $table_data[$j][]=$row->work_name;
            $table_data[$j][]='Tool Returned';
            $table_data[$j][]=$returned_log[$i]['returned_qty'];
            $table_data[$j][]=$days_used;
            $table_data[$j][]="Estimated : ".$returned_tool_cost;
            $j++;

          }

          $payments=$this->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user", array("ats.reference_id" =>$rent_data['tr.id'], "ats.reference_type" => 'tool_rent',"ats.tr_type" => 'tool_payment_transaction'),'tool')->result();
          foreach ($payments as $key => $value) {
            $sl_no++;
            $table_data[$j][]=$sl_no;
            $table_data[$j][]=date('d-m-Y',strtotime($value->tr_date));
            $table_data[$j][]=$row->name." (".$row->price."/Day)";
            $table_data[$j][]=$row->work_name;
            $table_data[$j][]='Tool Rent Payment to '.$row->rented_from;;
            $table_data[$j][]='';
            $table_data[$j][]='';
            $table_data[$j][]="Paid Amount : ".$value->tr_amount;
            $j++;
          }

          if($row->balance_to_return>0){
            $current_date = strtotime(date('Y-m-d'));
            $rented_date = strtotime($row->rented_date);
            $days_used = ceil(abs(($rented_date - $current_date)/(60*60*24)))+1;
            $balance_to_return_cost = $price_perday*$days_used*$row->balance_to_return;

            $sl_no++;
            $table_data[$j][]=$sl_no;
            $table_data[$j][]=date('d-m-Y');
            $table_data[$j][]=$row->name." (".$row->price."/Day)";
            $table_data[$j][]=$row->work_name;
            $table_data[$j][]='Balance to Return';
            $table_data[$j][]=$row->balance_to_return;
            $table_data[$j][]=$days_used;
            $table_data[$j][]="Estimated : ".$balance_to_return_cost;
            $j++;
          }
        }
      }

    }

    $json_table_data['data']=$table_data;
    $json_table_data['draw']=5;
    $json_table_data['recordsTotal']=sizeof($table_data);
    $json_table_data['recordsFiltered']=sizeof($table_data);
    echo json_encode($json_table_data);
  }

  // public function select_tool_tracks(){
  //
  //   $json_data=array();
  //   $j=0;
  //
  //   $data=array();
  //   $result	= $this->Tools->select_tool_racks("",$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=0;
  //   foreach($result_array as $row):
  //     $sl_no++;
  //
  //     $btn_edit=$btn_purchase='';
  //     if($this->session->userdata('user_role')=='admin'){
  //       $btn_edit='<a style="margin-left: 5px;margin-right: 5px" id="rack_edit_btn" href="#rack_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='';
  //     if($this->session->userdata('user_role')=='admin' || ($this->session->userdata('user_role')!='admin' && $this->session->userdata('user_id')==$row->created_by)) {
  //       $btn_delete='<a style="margin-left: 5px;margin-right: 5px" id="rack_delete_btn" href="#rack_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->rack_id;
  //     $array[$j][]=$row->rack_name;
  //     $array[$j][]=$btn_edit.$btn_delete;
  //
  //     $j++;
  //   endforeach;
  //
  //   $json_data['data']=$array;
  //   echo json_encode($json_data);  // send data as json format
  // }
  // public function create_rack()
  // {
  //   $data['rack_name'] = $this->security->xss_clean($this->input->post('name'));
  //   $count = $this->Tools->select_tool_racks("rack_id",array('rack_name' => $data['rack_name']))->num_rows();
  //   if($count==0) {
  //     $result = $this->Tools->create_tool_racks($data);
  //     $flash_data['status'] = $result['status'];
  //     if ($result['status'] == 1) {
  //       $flash_data['flashdata_msg'] = 'Tool Rack 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'] = 0;
  //     $flash_data['flashdata_msg'] = 'Sorry.. A Tool Rack 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_rack()
  // {
  //   $data['rack_id'] = $this->security->xss_clean($this->input->post('rack_id'));
  //   $data['rack_name'] = $this->security->xss_clean($this->input->post('name'));
  //   $count = $this->Tools->select_tool_racks("rack_id",array('rack_id !=' => $data['rack_id'],'rack_name' => $data['rack_name']))->num_rows();
  //   if($count==0) {
  //     $result = $this->Tools->update_tool_racks($data);
  //     $flash_data['status'] = $result;
  //     if ($flash_data['status'] == 1) {
  //       $flash_data['flashdata_msg'] = 'Tool Rack 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'] = 0;
  //     $flash_data['flashdata_msg'] = 'Sorry.. A Tool Rack 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_rack()
  // {
  //   $data['rack_id'] = $this->security->xss_clean($this->input->post('rack_id'));
  //   $data['delete_status'] = 1;
  //   $result = $this->Tools->update_tool_racks($data);
  //   $flash_data['status'] = $result;
  //   if ($flash_data['status'] == 1) {
  //     $flash_data['flashdata_msg'] = 'Tool Rack 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 !!';
  //   }
  //   $flash_data['message'] = $flash_data['flashdata_msg'];
  //   echo json_encode($flash_data);
  // }
}
?>
