<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
class Work 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('Subcontracts_model', 'Subcontracts');
    $this->load->model('Purchase_order_model', 'Purchase_order');
    $this->load->model('Labour_model', 'Labour');
    $this->load->model('Labour_wages_model', 'Labour_wages');
    $this->load->model('Account_model', 'Account');
    $this->load->model('Settings_model', 'Settings');
    $this->load->model('Stock_model', 'Stock');
    $this->load->model('Employees_model', 'Employee');
    $this->load->model('Salary_slips_model', 'Salary_slips');
    $this->load->model('Project_tasks_model', 'Project_tasks');
    $this->load->model('Invoices_model', 'Invoices');
    $this->load->model('Material_model', 'Material');
    $this->load->model('Vehicle_model', 'Vehicle');
    $this->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $this->load->model('Data_model', 'Data');
    $this->load->model('Enquiries_model','Enquiries');


    $this->load->library('Projects_management','projects_management');

    //        Check User Logged in or Not
    $allowed_user_roles = array("madmin","admin","supervisor","general_manager","purchase_manager","boq_manager","accounts_manager","auditor","qs","qs_manager","procurement_engineer","project_manager","operation_manager");
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }
    $company_details = $this->Settings->select_settings('*',array("settings_category"=>"Company Details"))->result();
    foreach ($company_details as $key => $value) {
      $this->page_data[$value->settings_name] = $value->value;
    }
    //        File Directory
    $this->page_data['directory'] = "work";
    $this->page_data['supplier_categories'] = $this->Supplier->select_supplier_category()->result();
    $this->page_data['vehicles'] = $this->Vehicle->select_vehicle()->result();
    $this->page_data['meter_makes'] = $this->Data->select_meter_makes("")->result();
    $this->page_data['tax_state_codes'] = $this->Settings->select_tax_state_codes("*")->result();
    $this->page_data['companies'] = $this->Data->select_companies("")->result();
    $this->page_data['master_projects'] = $this->Work->select_work("*",array("parent_project_id"=>0))->result();
  }

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

  public function view($type='')
  {
    $this->page_data['assign_users'] = $this->User->select_user("",array("role_name"=>'supervisor'))->result();
    $this->page_data['project_area_locations'] = $this->Work->select_project_area("")->result();
    $this->page_data['project_categories'] = $this->Work->select_work_category()->result();
    if($type=='completed'){
      $this->page_data['page_name'] = 'projects_view_completed';
    }else{
      $this->page_data['page_name'] = 'projects_view_ongoing';
    }

    $this->load->view('Index',$this->page_data);
  }
  public function project_enquiry_report($type='')
  {
    $this->page_data['assign_users'] = $this->User->select_user("",array("role_name"=>'supervisor'))->result();
    $this->page_data['project_area_locations'] = $this->Work->select_project_area("")->result();
    $this->page_data['project_categories'] = $this->Work->select_work_category()->result();
    $this->page_data['page_name'] = 'project_enquiry_report';
    $this->load->view('Index',$this->page_data);
  }
  public function custom_requirements()
  {
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['page_name'] = 'project_custom_requirements';
    $this->load->view('Index',$this->page_data);
  }
  public function beneficiary_accounts($page='view',$id="",$print="")
  {
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    if($page=='profile' && $id!=''){
      $customer_data = $this->Work->select_beneficiary_accounts("pba.*,mm.meter_name,u1.user_alias as supervisor_name,u2.user_alias as created_user,wc.work_name,wc.work_id,c.contractor_id,c.contractor_name,c.phone as contractor_phone",array("customer_id"=>$id));
      if($customer_data->num_rows()==1){
        $this->page_data['customer_data'] = $customer_data->row();
        $this->page_data['page_name'] = 'project_beneficiary_account_profile';
        $this->load->view('Index',$this->page_data);
      }else{
        show_404();
      }
    }elseif($page=='print'){
      $data=array();
      $this->page_data['selected_project'] = $this->page_data['selected_subcontract'] = $this->page_data['selected_supervisor'] = $this->page_data['selected_ward'] = 'All';
      if(isset($_GET['work']) && $_GET['work']!='' && $_GET['work']!='all' && $_GET['work']!='null'){
        $data['pba.project_id'] = $_GET['work'];
        $work_data = $this->Work->select_work("*", array("work_id" => $_GET['work']))->row();
        $this->page_data['selected_project'] = $work_data->work_id." - ".$work_data->work_name;
      }
      if(isset($_GET['subcontract']) && $_GET['subcontract']!='' && $_GET['subcontract']!='all' && $_GET['subcontract']!='null'){
        $data['pba.subcontract_id'] = $_GET['subcontract'];
        $subcontract_data = $this->Subcontracts->select_subcontract("sc.*,c.*,u.user_alias,wc.work_name,wc.work_location,wc.reference_no",array("sc.contract_id"=>$_GET['subcontract']))->row();
        $this->page_data['selected_subcontract'] = $subcontract_data->contractor_id." - ".$subcontract_data->contractor_name;
      }
      if(isset($_GET['supervisor']) && $_GET['supervisor']!='' && $_GET['supervisor']!='all' && $_GET['supervisor']!='null'){
        $data['pba.supervisor_id'] = $_GET['supervisor'];
        $user_data = $this->User->select_user("", array("u.user_id" => $_GET['supervisor']))->row();
        $this->page_data['selected_supervisor'] = $user_data->user_alias;
      }
      if(isset($_GET['wardno']) && $_GET['wardno']!='' && $_GET['wardno']!='all'){
        $wardno = $_GET['wardno'];
        $this->db->where('pba.ward_no like "%'.$wardno.'%"');
        $this->page_data['selected_ward'] = $_GET['wardno'];
      }
      $this->page_data['customer_data'] = $this->Work->select_beneficiary_accounts("pba.*,mm.meter_name,u1.user_alias as supervisor_name,u2.user_alias as created_user,wc.work_name,wc.work_id,c.contractor_id,c.contractor_name,c.phone as contractor_phone",$data)->result();
      $this->load->view('modules/work/project_beneficiary_accounts_list_print',$this->page_data);
    }else{
      $this->page_data['page_name'] = 'project_beneficiary_accounts';
      $this->load->view('Index',$this->page_data);
    }


  }
  public function area_locations()
  {
    $this->page_data['page_name'] = 'project_area_locations';
    $this->load->view('Index',$this->page_data);
  }
  public function all_summary()
  {
    $this->page_data['page_name'] = 'project_all_summary';
    $this->load->view('Index',$this->page_data);
  }
  public function add()
  {
    $this->page_data['enquiry_data'] = array();
    if(isset($_GET['enquiry_id']) && $_GET['enquiry_id']!=''){
      $this->page_data['enquiry_data'] = $this->Enquiries->select_enquiries("*,u1.user_alias as assigned_user", array("enquiry_id"=>$_GET['enquiry_id']));
      if($this->page_data['enquiry_data']->num_rows()==1){
        $this->page_data['enquiry_data'] = $this->page_data['enquiry_data']->row();
      }
    }

    //        Check User Logged in or Not
    if($this->config->item('User_project_creation_admin_only')==1) {
      $allowed_user_roles = array("madmin","admin");
    }else{
      $allowed_user_roles = array("madmin","admin","general_manager","qs","qs_manager","accounts_manager","operation_manager");
    }
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }
    $this->page_data['assign_users'] = $this->User->select_user("",array("role_name"=>'supervisor'))->result();
    $this->page_data['project_area_locations'] = $this->Work->select_project_area("")->result();
    $this->page_data['project_categories'] = $this->Work->select_work_category()->result();

    if ($this->config->item('Project_disable_add_button') == 0 || ($this->config->item('Project_disable_add_button') != 0 && !empty($this->page_data['enquiry_data']))){
      $this->page_data['page_name'] = 'work_add';
      $this->load->view('Index',$this->page_data);
    }else{
      $this->view();
    }
  }

  public function edit($id="")
  {
    //        Check User Logged in or Not
    if($this->config->item('User_project_creation_admin_only')==1) {
      $allowed_user_roles = array("madmin","admin");
    }else{
      $allowed_user_roles = array("madmin","admin","general_manager","qs","qs_manager","operation_manager");
    }
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }
    if($id!="") {
      $this->page_data['work_category'] = $this->Work->select_work_category()->result();
      $this->page_data['project_area_locations'] = $this->Work->select_project_area("")->result();
      if($this->session->userdata('user_role')=='qs_manager' || $this->session->userdata('user_role')=='qs'){
        $this->db->where("w.created_by",$this->session->userdata('user_id'));
      }
      $this->page_data['project_data'] = $this->Work->select_work("*", array("work_id" => $id));
      if($this->page_data['project_data']->num_rows()==1){
        $this->page_data['project_data'] = $this->page_data['project_data']->row();

        $this->page_data['enquiry_data'] = array();
        if($this->page_data['project_data']->enquiry_id!=0){
          $this->page_data['enquiry_data'] = $this->Enquiries->select_enquiries("*,u1.user_alias as assigned_user", array("enquiry_id"=>$this->page_data['project_data']->enquiry_id));
          if($this->page_data['enquiry_data']->num_rows()==1){
            $this->page_data['enquiry_data'] = $this->page_data['enquiry_data']->row();
          }
        }

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

  }
  public function profile($id="")
  {
    if($id!="") {
      $this->page_data['work_data'] = $this->Work->select_work("*,w.created_by as work_created_by,w.created_date as work_created_date", array("work_id" => $id));
      if($this->page_data['work_data']->num_rows()==1){
        $this->page_data['work_data'] = $this->page_data['work_data']->row();
        $this->page_data['parent_project'] = '';
        $this->page_data['sub_projects'] = array();
        if ($this->config->item('Project_parent_child_enable') == 1) {
          if($this->page_data['work_data']->parent_project_id!=0){
            $parent_project_data = $this->Work->select_work("*",array("work_id"=>$this->page_data['work_data']->parent_project_id))->row();
            $this->page_data['parent_project'] = "<br/><a href='".base_url()."work/profile/".$parent_project_data->work_id."'><div style='font-size:20px;font-weight:600;padding: 0.5rem 0.5rem;' class='m-alert m-alert--outline alert alert-info alert-dismissible fade show'><i class='fa fa-sitemap'></i> Sub Project Of <br/>#".$parent_project_data->work_id." - ".$parent_project_data->work_name."</div></a>";
          }else{
            $this->page_data['parent_project'] = "<br/><div style='font-size:20px;font-weight:600;padding: 0.5rem 0.5rem;' class='m-alert m-alert--outline alert alert-info alert-dismissible fade show'><i class='fa fa-sitemap'></i> Master Project</div>";
            $this->page_data['sub_projects'] = $this->Work->select_work("*",array("parent_project_id"=>$id))->result();

            if(sizeof($this->page_data['sub_projects'])>0){
              $this->page_data['parent_project'] .= "<div style='font-size:20px;font-weight:600;padding: 0.5rem 0.5rem;' class='m-alert m-alert--outline alert alert-warning alert-dismissible fade show'><i class='fa fa-sitemap'></i> Have ".sizeof($this->page_data['sub_projects'])." Sub Projects</div>";
              $this->page_data['parent_project'] .= "<a href='".base_url()."work/master_profile/".$id."' class='btn  m-btn--pill  btn-warning'><i class='fa fa-sitemap'></i> View Sub Projects Summary</a>";
            }
          }
        }

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

        $this->page_data['stage_data'] = $this->projects_management->get_project_stages($id);
        $this->page_data['stages'] = $this->Work->select_project_stage('*',array('ps.project_id'=>$id))->result();

        $this->page_data['expense_heads'] = $this->Account->select_account_heads("","ah.account_type=11")->result();
        $this->page_data['purchase_data'] = $this->Purchases->select_purchase("", array("sl.work_id" => $id))->row();
        $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
        $this->page_data['assign_users'] = $this->User->select_user("",array("role_name"=>'supervisor'))->result();
        $this->page_data['work_types'] = $this->Work->select_work_types()->result();
        $this->page_data['work_category'] = $this->Work->select_work_category()->result();
        $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
        $this->page_data['labours'] = $this->Labour->select_labours()->result();
        $this->page_data['labour_groups'] = $this->Labour->select_labours("",array("labour_type"=>1,"labour_group !="=>0,"group_position"=>1))->result();
        $this->page_data['employees'] = $this->Employee->select_employee("e.*,u.user_alias")->result();
        $this->page_data['assigned_users'] = $this->page_data['supervisors'] = $this->Work->select_work_assign_log("u.*,wp.project_commission_percent",array("wp.work_id"=>$id))->result();
        $this->page_data['other_works'] = $this->Work->select_work("*",array("w.work_id !="=>$id))->result();
        $this->page_data['invoices'] = $this->Invoices->select_invoices("*,u1.user_id as i_created_by,u1.user_alias as i_created",array('i.project_id' => $id ))->result();
        $user_accounts_where = "ur.role_name='supervisor' or ur.role_name='general_manager'";
        $this->page_data['user_accounts'] = $this->Account->select_account_heads("",$user_accounts_where,"user")->result();
        $this->page_data['works'] = $this->Work->select_work("*")->result();
        if($this->config->item('Labour_group_member_payment')==0){
          $this->db->where('(l.labour_type=0 or (l.labour_type=1 and group_position=1))');
        }
        $this->page_data['labours_2'] = $this->Labour->select_labours()->result();
        $this->page_data['labour_groups'] = $this->Labour->select_labours("",array("labour_type"=>1,"labour_group !="=>0,"group_position"=>1))->result();
        $this->page_data['materials'] = $this->Material->select_material()->result();
        if($this->session->userdata('user_role')=='supervisor') {
          //              Select Assigned Works
          $assigned_works = $this->Work->select_work_assign_log("wp.work_id",array("wp.work_id"=>$id,"wp.user_id"=>$this->session->userdata('user_id')));
          if($assigned_works->num_rows()==1) {
            $this->load->view('Index', $this->page_data);
          }else{
            show_404();
          }
        }else{
          $this->load->view('Index', $this->page_data);
        }
      }else{
        show_404();
      }
    }else{
      show_404();
    }

  }
  public function master_profile($id="")
  {
    $allowed_user_roles = array("admin","madmin","general_manager","accounts_manager");
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }

    if($id!="" && $this->config->item('Project_parent_child_enable') == 1) {
      $this->page_data['work_data'] = $this->Work->select_work("*,w.created_by as work_created_by,w.created_date as work_created_date", array("work_id" => $id));
      if($this->page_data['work_data']->num_rows()==1 && $this->page_data['work_data']->row()->parent_project_id==0){
        $this->page_data['work_data'] = $this->page_data['work_data']->row();
        $this->page_data['parent_project'] = '';
        $this->page_data['sub_projects'] = $this->page_data['sub_project_ids'] = array();

        $this->page_data['sub_projects'] = $this->Work->select_work("*",array("parent_project_id"=>$id))->result();
        foreach ($this->page_data['sub_projects'] as $key => $value) {
          $this->page_data['sub_project_ids'][] = $value->work_id;
        }
        if(sizeof($this->page_data['sub_projects'])>0){
          $this->page_data['parent_project'] .= "<div style='font-size:20px;font-weight:600;padding: 0.5rem 0.5rem;' class='m-alert m-alert--outline alert alert-warning alert-dismissible fade show'><i class='fa fa-sitemap'></i> ".sizeof($this->page_data['sub_projects'])." Sub Projects</div>";
        }

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

        $this->page_data['stage_data'] = $this->projects_management->get_project_stages($id);
        $this->page_data['stages'] = $this->Work->select_project_stage('*',array('ps.project_id'=>$id))->result();

        $this->page_data['expense_heads'] = $this->Account->select_account_heads("","ah.account_type=11")->result();
        $this->page_data['purchase_data'] = $this->Purchases->select_purchase("", array("sl.work_id" => $id))->row();
        $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
        $this->page_data['assign_users'] = $this->User->select_user("",array("role_name"=>'supervisor'))->result();
        $this->page_data['work_types'] = $this->Work->select_work_types()->result();
        $this->page_data['work_category'] = $this->Work->select_work_category()->result();
        $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
        $this->page_data['labours'] = $this->Labour->select_labours()->result();
        $this->page_data['labour_groups'] = $this->Labour->select_labours("",array("labour_type"=>1,"labour_group !="=>0,"group_position"=>1))->result();
        $this->page_data['employees'] = $this->Employee->select_employee("e.*,u.user_alias")->result();
        $this->page_data['assigned_users'] = $this->page_data['supervisors'] = $this->Work->select_work_assign_log("u.*,wp.project_commission_percent",array("wp.work_id"=>$id))->result();
        $this->page_data['other_works'] = $this->Work->select_work("*",array("w.work_id !="=>$id))->result();
        $this->page_data['invoices'] = $this->Invoices->select_invoices("*,u1.user_id as i_created_by,u1.user_alias as i_created",array('i.project_id' => $id ))->result();
        $user_accounts_where = "ur.role_name='supervisor' or ur.role_name='general_manager'";
        $this->page_data['user_accounts'] = $this->Account->select_account_heads("",$user_accounts_where,"user")->result();
        $this->page_data['works'] = $this->Work->select_work("*")->result();
        if($this->config->item('Labour_group_member_payment')==0){
          $this->db->where('(l.labour_type=0 or (l.labour_type=1 and group_position=1))');
        }
        $this->page_data['labours_2'] = $this->Labour->select_labours()->result();
        $this->page_data['labour_groups'] = $this->Labour->select_labours("",array("labour_type"=>1,"labour_group !="=>0,"group_position"=>1))->result();
        $this->page_data['materials'] = $this->Material->select_material()->result();
        if($this->session->userdata('user_role')=='supervisor') {
          //              Select Assigned Works
          $assigned_works = $this->Work->select_work_assign_log("wp.work_id",array("wp.work_id"=>$id,"wp.user_id"=>$this->session->userdata('user_id')));
          if($assigned_works->num_rows()==1) {
            $this->load->view('Index', $this->page_data);
          }else{
            show_404();
          }
        }else{
          $this->load->view('Index', $this->page_data);
        }
      }else{
        show_404();
      }
    }else{
      show_404();
    }

  }

  public function stage_profile($stage_id="")
  {
    if($stage_id!="") {

      $this->page_data['stage_data'] = $this->Work->select_project_stage("*,ps.created_by as stage_created_by,ps.created_date as stage_created_date", array("stage_id" => $stage_id));

      $work_id = $this->page_data['stage_data']->row()->project_id;

      $this->page_data['stage_data'] = $this->page_data['stage_data']->row();

      $this->page_data['work_data'] = $this->Work->select_work("*,w.created_by as work_created_by,w.created_date as work_created_date", array("work_id" => $work_id));
      if($this->page_data['work_data']->num_rows()==1){
        $this->page_data['work_data'] = $this->page_data['work_data']->row();
        $this->page_data['page_name'] = 'stage_profile';

        $id = $work_id;

        $this->page_data['expense_heads'] = $this->Account->select_account_heads("","ah.account_type=11")->result();
        $this->page_data['purchase_data'] = $this->Purchases->select_purchase("", array("sl.work_id" => $id))->row();
        $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
        $this->page_data['assign_users'] = $this->User->select_user("",array("role_name"=>'supervisor'))->result();
        $this->page_data['work_types'] = $this->Work->select_work_types()->result();
        $this->page_data['work_category'] = $this->Work->select_work_category()->result();
        $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
        $this->page_data['labours'] = $this->Labour->select_labours()->result();
        $this->page_data['labour_groups'] = $this->Labour->select_labours("",array("labour_type"=>1,"labour_group !="=>0,"group_position"=>1))->result();
        $this->page_data['employees'] = $this->Employee->select_employee("e.*,u.user_alias")->result();
        $this->page_data['assigned_users'] = $this->Work->select_work_assign_log("u.*,wp.project_commission_percent",array("wp.work_id"=>$id))->result();
        $this->page_data['other_works'] = $this->Work->select_work("*",array("w.work_id !="=>$id))->result();
        $this->page_data['invoices'] = $this->Invoices->select_invoices("*,u1.user_id as i_created_by,u1.user_alias as i_created",array('i.project_id' => $id ))->result();
        $user_accounts_where = "ur.role_name='supervisor' or ur.role_name='general_manager'";
        $this->page_data['user_accounts'] = $this->Account->select_account_heads("",$user_accounts_where,"user")->result();
        $this->page_data['works'] = $this->Work->select_work("*")->result();
        if($this->config->item('Labour_group_member_payment')==0){
          $this->db->where('(l.labour_type=0 or (l.labour_type=1 and group_position=1))');
        }
        $this->page_data['labours_2'] = $this->Labour->select_labours()->result();
        $this->page_data['labour_groups'] = $this->Labour->select_labours("",array("labour_type"=>1,"labour_group !="=>0,"group_position"=>1))->result();
        $this->page_data['materials'] = $this->Material->select_material()->result();
        if($this->session->userdata('user_role')=='supervisor') {
          //              Select Assigned Works
          $assigned_works = $this->Work->select_work_assign_log("wp.work_id",array("wp.work_id"=>$id,"wp.user_id"=>$this->session->userdata('user_id')));
          if($assigned_works->num_rows()==1) {
            $this->load->view('Index', $this->page_data);
          }else{
            show_404();
          }
        }else{
          $this->load->view('Index', $this->page_data);
        }
      }else{
        show_404();
      }
    }else{
      show_404();
    }

  }

  public function create()
  {
    //        Check User Logged in or Not
    $allowed_user_roles = array("admin","madmin","general_manager","qs","qs_manager","accounts_manager","operation_manager");
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }

    //      insert work
    if($this->input->post('enquiry_id')){
      $data2['enquiry_id'] = $this->security->xss_clean($this->input->post('enquiry_id'));
      $enquiry_data = $this->Enquiries->select_enquiries("e.*", array("enquiry_id"=>$data2['enquiry_id']));
      if($enquiry_data->num_rows()==1){
        $customer_data = $this->Customers->select_customer("*", array("customer_id" => $enquiry_data->row()->customer_id))->row();
        $client_data['client_name'] = $customer_data->customer_name;
        $client_data['client_contact'] = $customer_data->customer_phone;
        $client_data['contact_person'] = $customer_data->contact_person;
        $client_data['client_address'] = $customer_data->customer_location;
        $client_data['client_email'] = $customer_data->customer_email;
        $client_data['created_date'] = date('Y-m-d H:i:s');
        $client_data['created_by'] = $this->session->userdata('user_id');
        // Check Client exist with email/phone
        $client_contact = $client_data['client_contact'];
        $client_email = $client_data['client_email'];
        if($enquiry_data->row()->customer_id!=0){
          $client_data['customer_id'] = $enquiry_data->row()->customer_id;
          $this->db->where("(customer_id='".$enquiry_data->row()->customer_id."')");
        }else{
          $this->db->where("(client_contact='".$client_contact."')");
        }
        $count = $this->Client->select_client('client_id');
        if($count->num_rows()==0){
          $client_result = $this->Client->insert_client($client_data);
          $client_id = $client_result['insert_id'];
        }else{
          $client_id = $count->result()[0]->client_id;
        }
        $data2['client_id'] = $client_id;
      }
    }else{
      $data2['client_id'] = $this->security->xss_clean($this->input->post('client'));
    }
    $data2['work_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Work->select_work("work_id", array("w.client_id"=>$data2['client_id'],"w.work_name"=>$data2['work_name']));
    $data2['work_location'] = $this->security->xss_clean($this->input->post('location'));
    $data2['reference_no'] = strtoupper($this->security->xss_clean($this->input->post('reference_no')));
    $data2['reference_no_2'] = strtoupper($this->security->xss_clean($this->input->post('reference_no_2')));
    $data2['lpo_no'] = strtoupper($this->security->xss_clean($this->input->post('lpo_no')));
    $data2['lpo_value'] = $this->security->xss_clean($this->input->post('lpo_value'));
    $data2['lpo_status'] = $this->security->xss_clean($this->input->post('lpo_status'));
    if($this->input->post('location_area')){
      $data2['location_area'] = $this->security->xss_clean($this->input->post('location_area'));
    }
    if($this->input->post('company')){
      $data2['company_id'] = $this->security->xss_clean($this->input->post('company'));
    }
    $data2['project_section'] = $this->security->xss_clean($this->input->post('project_section'));
    $data2['work_type'] = $this->security->xss_clean($this->input->post('project_type'));

    $budget_array=$newArraybudget=array();
    if($this->input->post('budget')){
      $budget_array = $this->input->post('budget');
    }
    $budget_array_length = sizeof($budget_array);

    foreach (array_keys($budget_array) as $fieldKey) {
      foreach ($budget_array[$fieldKey] as $key=>$value) {
        $newArraybudget[$key][$fieldKey] = $value;
      }
    }

    $i = 0;
    if($this->config->item('Project_multiple_budget')==0){
      $data2['est_budget_amt'] = $newArraybudget[0]['amount'];
      $data2['project_tax_included'] = $newArraybudget[0]['tax_type'];
      $data2['project_tax_percent'] = $newArraybudget[0]['tax_percent'];
      $data2['project_tax_amount'] = $newArraybudget[0]['tax_amount'];
    }else{
      foreach ($newArraybudget as $row) {
        $budget_new[$i]['section'] = $row['section'];
        $budget_new[$i]['amount'] = $row['amount'];
        $budget_new[$i]['tax_percent'] = $row['tax_percent'];
        $budget_new[$i]['tax_amount'] = $row['tax_amount'];
        $budget_new[$i]['total'] = $row['total'];
        $i++;
      }
      if(sizeof($newArraybudget)){
        $data2['project_budget_details'] = serialize($budget_new);
      }
    }

    $data2['total_budget_amount'] = $this->security->xss_clean($this->input->post('grand_total_budget_amount'));

    $data2['profit_margin_percent'] = $this->security->xss_clean($this->input->post('profit_margin_percent'));
    $data2['project_priority'] = $this->security->xss_clean($this->input->post('priority'));
    $data2['initial_security_deposit'] = $this->security->xss_clean($this->input->post('initial_security_deposit'));
    $data2['security_deposit_details'] = $this->security->xss_clean($this->input->post('security_deposit_details'));
    if($this->input->post('completion_date') !=''){
      $stage_data['stage_end_date'] = $data2['planned_date_of_completion'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('completion_date'))));
    }
    if($this->input->post('start_date') !=''){
      $stage_data['stage_start_date'] = $data2['planned_date_of_start'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('start_date'))));
    }
    if($this->input->post('project_management') && $this->input->post('project_management')==1){
      $data2['project_management_commission'] = $this->security->xss_clean($this->input->post('commission_percent'));
    }
    $data2['project_op_type'] = $this->security->xss_clean($this->input->post('project_op_status'));
    if($this->input->post('opening_date') !=''){
      $data2['op_upto_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('opening_date'))));
    }
    if($this->input->post('lpo_date') !=''){
      $data2['lpo_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('lpo_date'))));
    }
    $data2['op_expense_amount'] = $this->security->xss_clean($this->input->post('expense_spent'));
    $data2['op_income_amount'] = $this->security->xss_clean($this->input->post('income_received'));
    $data2['work_description'] = $this->security->xss_clean($this->input->post('description'));
    $data2['created_date'] = date('Y-m-d H:i:s');
    $data2['created_by'] = $this->session->userdata('user_id');
    $data2['work_status'] = 2; //In Process Default
    if($this->input->post('user_assign') && $this->input->post('user_assign')!=0){
      $data2['assigned_users'] = $this->input->post('user_assign');
    }
    //Child Project
    if($this->input->post('project_master_type') && $this->input->post('project_master_type')!=0){
      $data2['parent_project_id'] = $this->input->post('parent_project');
      $parent_project_data = $this->Work->select_work("*",array("work_id"=>$data2['parent_project_id']))->row();
      $data2['client_id'] = $parent_project_data->client_id;
      if($parent_project_data->company_id!=0){
        $data2['company_id'] = $parent_project_data->company_id;
      }
    }
    if($count->num_rows()==0){

      $file_error = 0;
      if (isset($_FILES['uploaded_file']) && $_FILES['uploaded_file']["size"] > 0) {
        $projectname = strtolower($data2['work_name']);
        $file_name_prefix = preg_replace('/[^A-Za-z0-9\-]/', '-', $projectname);
        $file_name_prefix = preg_replace('/\s+/', '-', $file_name_prefix);
        //      File Upload
        $config['upload_path']   = 'uploads/projects/';
        $config['allowed_types'] = 'jpg|jpeg|png';
        // $config['max_size']      = 300;
        $config['overwrite']      = FALSE;
        // $config['max_width']     = 1980;
        // $config['max_height']    = 1080;
        $config['encrypt_name']    = TRUE;
        $this->load->library('upload', $config);
        if (!$this->upload->do_upload('uploaded_file')) {
          $error = array('error' => $this->upload->display_errors());
          $file_error = 1;
          $flash_data['flashdata_msg'] = $error['error'];
          $flash_data['flashdata_type'] = "warning";
          $flash_data['flashdata_title'] = "Uploaded File Error";
          $flash_data['status'] = 2;
        }else {
          $file_data = $this->upload->data();
          $data2['photo'] = $file_data['file_name'];
          $this->resizeImage("projects",$file_data['file_name']);
        }
      }else{
        $data2['photo'] = "default.png";
      }
      if($file_error==0){
        $result2 = $this->Work->insert_work($data2);

        $flash_data['status'] = $result2['status'];
        if($flash_data['status'] == 1){
          // Create Stock Location
          $loc_data['location_type'] = 'work_site';
          $loc_data['location_name'] = $data2['work_name'];
          $loc_data['work_id'] = $result2['insert_id'];
          $result =  $this->Stock->insert_stock_location($loc_data);

          $flash_data['flashdata_msg'] = 'Project Created Successfully!.';
          $flash_data['flashdata_type'] = 'success';
          $flash_data['alert_type'] = 'success';
          $flash_data['flashdata_title'] = 'Success !';
          $flash_data['work_id'] = $result2['insert_id'];

          if($this->input->post('user_assign') && $this->input->post('user_assign')!=0){
            $assign_data['user_id'] = $this->input->post('user_assign');
            $assign_data['work_id'] = $result2['insert_id'];
            $assign_data['created_date'] = date('Y-m-d H:i:s');
            $assign_data['created_by'] = $this->session->userdata('user_id');
            $result = $this->Work->create_work_assign_log($assign_data);
          }


          // Default Stage
          $stage_data['project_id'] = $result2['insert_id'];
          $stage_data['stage_no'] = 1;
          $stage_data['stage_active_status'] = 1;
          $stage_data['stage_name'] = 'First Stage';
          $stage_data['stage_budget'] = $data2['total_budget_amount'];
          // $stage_data['stage_description'] = 'First Stage ';
          $stage_data['created_date'] = date('Y-m-d H:i:s');
          $stage_data['created_by'] = $this->session->userdata('user_id');
          $result_stage = $this->Work->insert_project_stage($stage_data);

          $stage_log_data['stage_id'] = $result_stage['insert_id'];
          $stage_log_data['project_id'] = $result2['insert_id'];
          $stage_log_data['active_on'] = date('Y-m-d h:i:s');
          $result_stage_log = $this->Work->insert_project_stage_log($stage_log_data);

          if($this->input->post('received_amount')!='' && $this->input->post('received_amount')!=0){
            $reference_id = $result2['insert_id'];
            $reference_type = 'project';
            $transaction_data['taxable_amount'] = $this->security->xss_clean($this->input->post('received_amount'));
            $transaction_data['tax_percent'] = 0;
            $transaction_data['tax_amount'] = 0;
            $transaction_data['tax_inclusive'] = 0;
            $transaction_data['tr_type'] = 'client_payment_transaction';
            $transaction_data['reference_id'] = $reference_id;
            $transaction_data['reference_type'] = $reference_type;
            $transaction_data['tr_description'] = ucwords(str_replace('_',' ',$transaction_data['tr_type']));
            $transaction_data['tr_reference_no'] = $this->security->xss_clean($this->input->post('tr_reference_no'));
            $transaction_data['tr_remarks'] = $this->security->xss_clean($this->input->post('tr_remarks'));
            $transaction_data['created_date'] = date('Y-m-d H:i:s');
            $transaction_data['created_by'] = $this->session->userdata('user_id');
            $transaction_data['tr_credit_acc'] = $this->security->xss_clean($this->input->post('credit_acc'));
            $transaction_data['tr_debit_acc'] = 2;//Client Payment Receipts
            $transaction_data['tr_amount'] = $this->security->xss_clean($this->input->post('received_amount'));
            $transaction_data['tr_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('received_date'))));
            $transaction_result =  $this->Account->create_account_transaction($transaction_data);
          }


        }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'] = 'A Project With Same Name Already Exist with this Client!';
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Already Exist !!';
    }

    // $this->session->set_flashdata($flash_data);
    echo json_encode($flash_data);

  }
  public function resizeImage($directory,$filename)
  {
    $source_path = FCPATH.'/uploads/'.$directory."/". $filename;
    $target_path = FCPATH.'/uploads/'.$directory."/";
    $config_manip = array(
      'image_library' => 'gd2',
      'source_image' => $source_path,
      'new_image' => $target_path,
      'maintain_ratio' => TRUE,
      // 'quality' => '60%',
      'width' => 150,
      'height' => 200,
    );

    $this->load->library('image_lib', $config_manip);
    if (!$this->image_lib->resize()) {
      echo $this->image_lib->display_errors();
    }
    $this->image_lib->clear();
  }
  public function update()
  {
    //        Check User Logged in or Not
    $allowed_user_roles = array("madmin","admin","general_manager","operation_manager");
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }

    $data2['work_id'] = $this->security->xss_clean($this->input->post('work_id'));
    $data2['work_name'] = $this->security->xss_clean($this->input->post('name'));
    if($this->input->post('client')){
      $data2['client_id'] = $this->security->xss_clean($this->input->post('client'));
      $count = $this->Work->select_work("work_id", array("w.client_id"=>$data2['client_id'],"w.work_name"=>$data2['work_name'],"w.work_id !="=>$data2['work_id']));
    }else{
      $project_data = $this->Work->select_work("*,w.created_by as work_created_by,w.created_date as work_created_date", array("work_id" => $data2['work_id']))->row();
      $count = $this->Work->select_work("work_id", array("w.client_id"=>$project_data->client_id,"w.work_name"=>$data2['work_name'],"w.work_id !="=>$data2['work_id']));
    }
    $data2['work_location'] = $this->security->xss_clean($this->input->post('location'));
    $data2['reference_no'] = strtoupper($this->security->xss_clean($this->input->post('reference_no')));
    $data2['reference_no_2'] = strtoupper($this->security->xss_clean($this->input->post('reference_no_2')));
    $data2['lpo_no'] = strtoupper($this->security->xss_clean($this->input->post('lpo_no')));
    $data2['lpo_value'] = $this->security->xss_clean($this->input->post('lpo_value'));
    $data2['lpo_status'] = $this->security->xss_clean($this->input->post('lpo_status'));
    if($this->input->post('location_area')){
      $data2['location_area'] = $this->security->xss_clean($this->input->post('location_area'));
    }
    if($this->input->post('company')){
      $data2['company_id'] = $this->security->xss_clean($this->input->post('company'));
    }
    $data2['project_section'] = $this->security->xss_clean($this->input->post('project_section'));
    $data2['work_type'] = $this->security->xss_clean($this->input->post('project_type'));

    $budget_array=$newArraybudget=array();
    if($this->input->post('budget')){
      $budget_array = $this->input->post('budget');
    }
    $budget_array_length = sizeof($budget_array);

    foreach (array_keys($budget_array) as $fieldKey) {
      foreach ($budget_array[$fieldKey] as $key=>$value) {
        $newArraybudget[$key][$fieldKey] = $value;
      }
    }

    $i = 0;
    if($this->config->item('Project_multiple_budget')==0){
      $data2['est_budget_amt'] = $newArraybudget[0]['amount'];
      $data2['project_tax_included'] = $newArraybudget[0]['tax_type'];
      $data2['project_tax_percent'] = $newArraybudget[0]['tax_percent'];
      $data2['project_tax_amount'] = $newArraybudget[0]['tax_amount'];
    }else{
      foreach ($newArraybudget as $row) {
        $budget_new[$i]['section'] = $row['section'];
        $budget_new[$i]['amount'] = $row['amount'];
        $budget_new[$i]['tax_percent'] = $row['tax_percent'];
        $budget_new[$i]['tax_amount'] = $row['tax_amount'];
        $budget_new[$i]['total'] = $row['total'];
        $i++;
      }
      if(sizeof($newArraybudget)){
        $data2['project_budget_details'] = serialize($budget_new);
      }
    }

    $data2['total_budget_amount'] = $this->security->xss_clean($this->input->post('grand_total_budget_amount'));

    $data2['profit_margin_percent'] = $this->security->xss_clean($this->input->post('profit_margin_percent'));
    $data2['project_priority'] = $this->security->xss_clean($this->input->post('priority'));
    $data2['initial_security_deposit'] = $this->security->xss_clean($this->input->post('initial_security_deposit'));
    $data2['security_deposit_details'] = $this->security->xss_clean($this->input->post('security_deposit_details'));
    if($this->input->post('completion_date') !=''){
      $data2['planned_date_of_completion'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('completion_date'))));
    }else{
      $data2['planned_date_of_completion'] = '0000-00-00';
    }
    if($this->input->post('start_date') !=''){
      $data2['planned_date_of_start'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('start_date'))));
    }else{
      $data2['planned_date_of_start'] = '0000-00-00';
    }
    if($this->input->post('project_management') && $this->input->post('project_management')==1){
      $data2['project_management_commission'] = $this->security->xss_clean($this->input->post('commission_percent'));
    }
    $data2['project_op_type'] = $this->security->xss_clean($this->input->post('project_op_status'));
    if($this->input->post('opening_date') !=''){
      $data2['op_upto_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('opening_date'))));
    }else{
      $data2['op_upto_date'] = '0000-00-00';
    }
    if($this->input->post('lpo_date') !=''){
      $data2['lpo_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('lpo_date'))));
    }else{
      $data2['lpo_date'] = '0000-00-00';
    }
    $data2['op_expense_amount'] = $this->security->xss_clean($this->input->post('expense_spent'));
    $data2['op_income_amount'] = $this->security->xss_clean($this->input->post('income_received'));
    $data2['work_description'] = $this->security->xss_clean($this->input->post('description'));
    $data2['updated_by'] = $this->session->userdata('user_id');
    $data2['updated_date'] = date('Y-m-d H:i:s');
    //Child Project
    if($this->input->post('project_master_type') && $this->input->post('project_master_type')!=0){
      $data2['parent_project_id'] = $this->input->post('parent_project');
      $parent_project_data = $this->Work->select_work("*",array("work_id"=>$data2['parent_project_id']))->row();
      $data2['client_id'] = $parent_project_data->client_id;
      if($parent_project_data->company_id!=0){
        $data2['company_id'] = $parent_project_data->company_id;
      }
    }else{
      $data2['parent_project_id'] = 0;
    }
    if($count->num_rows()==0){
      $file_error = 0;
      if (isset($_FILES['uploaded_file']) && $_FILES['uploaded_file']["size"] > 0) {
        $projectname = strtolower($data2['work_name']);
        $file_name_prefix = preg_replace('/[^A-Za-z0-9\-]/', '-', $projectname);
        $file_name_prefix = preg_replace('/\s+/', '-', $file_name_prefix);
        //      File Upload
        $config['upload_path']   = 'uploads/projects/';
        $config['allowed_types'] = 'jpg|jpeg|png';
        // $config['max_size']      = 300;
        $config['overwrite']      = FALSE;
        // $config['max_width']     = 1980;
        // $config['max_height']    = 1080;
        $config['encrypt_name']    = TRUE;
        $this->load->library('upload', $config);
        if (!$this->upload->do_upload('uploaded_file')) {
          $error = array('error' => $this->upload->display_errors());
          $file_error = 1;
          $flash_data['flashdata_msg'] = $error['error'];
          $flash_data['flashdata_type'] = "warning";
          $flash_data['flashdata_title'] = "Uploaded File Error";
          $flash_data['status'] = 2;
        }else {
          $file_data = $this->upload->data();
          $data2['photo'] = $file_data['file_name'];
          $this->resizeImage("projects",$file_data['file_name']);
        }
      }
      if($file_error==0){
        $result2 = $this->Work->update_work($data2);
        $flash_data['status'] = $result2;
        if($flash_data['status'] == 1){

          // Update Stock Location
          $loc_where_data['sl.location_type'] = 'work_site';
          $loc_where_data['sl.work_id'] = $data2['work_id'];
          $stock_locations = $this->Stock->select_stock_locations("sl.location_id",$loc_where_data)->result();
          foreach ($stock_locations as $key => $value) {
            $loc_data['location_id'] = $value->location_id;
            $loc_data['location_name'] = $data2['work_name'];
            $result =  $this->Stock->update_stock_location($loc_data);
          }

          $flash_data['flashdata_msg'] = 'Project Details Updated Successfully!.';
          $flash_data['flashdata_type'] = 'success';
          $flash_data['alert_type'] = 'success';
          $flash_data['flashdata_title'] = 'Success !';
          $flash_data['work_id'] = $data2['work_id'];

        }else {
          $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
          $flash_data['flashdata_type'] = 'error';
          $flash_data['alert_type'] = 'danger';
          $flash_data['flashdata_title'] = 'Error !!';
        }
      }
    }else {
      $flash_data['status'] = 2;
      $flash_data['flashdata_msg'] = 'A Project With Same Name Already Exist with this Client!';
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Already Exist !!';
    }
    // $this->session->set_flashdata($flash_data);
    echo json_encode($flash_data);
  }
  public function delete()
  {
    //        Check User Logged in or Not
    $allowed_user_roles = array("madmin","admin");
    if($this->User->is_logged_in($allowed_user_roles)==false){
      redirect(base_url(), 'refresh');
    }
    $data2['work_id'] = $this->security->xss_clean($this->input->post('work_id'));
    $data2['delete_status'] = 1;
    $result2 = $this->Work->update_work($data2);

    if($result2 == 1){
      // Delete Stock Location
      $loc_where_data['sl.location_type'] = 'work_site';
      $loc_where_data['sl.work_id'] = $data2['work_id'];
      $stock_locations = $this->Stock->select_stock_locations("sl.location_id",$loc_where_data)->result();
      foreach ($stock_locations as $key => $value) {
        $loc_data['location_id'] = $value->location_id;
        $loc_data['delete_status'] = 1;
        $result =  $this->Stock->update_stock_location($loc_data);
      }
      $flash_data['flashdata_msg'] = 'Work Deleted Successfully!.';
      $flash_data['flashdata_type'] = 'info';
      $flash_data['alert_type'] = 'info';
      $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 !!';
    }

    $this->session->set_flashdata($flash_data);
    redirect(base_url(). 'work/','refresh');
  }
  //    update_work_status
  public function update_work_status(){
    $redirect_url= $this->security->xss_clean($this->input->post('redirect_url'));
    $data['work_id'] = $this->security->xss_clean($this->input->post('work_id'));
    $data['work_status'] = $this->security->xss_clean($this->input->post('work_status'));
    if($data['work_status']==1 && $this->input->post('work_completed_date')!=''){
      $data['project_completed_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('work_completed_date'))));
    }else{
      $data['project_completed_date'] = '0000-00-00';
    }
    $data['updated_by'] = $this->session->userdata('user_id');
    $data['updated_date'] = date('Y-m-d H:i:s');

    $result = $this->Work->update_work($data);
    $flash_data['status'] = $result;
    if ($result == 1) {
      if($this->config->item('User_project_commission_payment')==1){
        $work_details = $this->Work->select_work("*,w.created_by as work_created_by,w.created_date as work_created_date", array("work_id" => $data['work_id']));
        $work_details = $work_details->row();
        $transaction_where['tr_work_id'] = $data['work_id'];
        $transaction_where['reference_type'] = "user_account_head";
        $transaction_where['tr_type'] = "user_commission_transaction";
        $assigned_users = $this->Work->select_work_assign_log("u.*,wp.project_commission_percent,wp.assign_log_id",array("wp.work_id"=>$data['work_id']))->result();
        foreach ($assigned_users as $key => $value) {
          $account_data = $this->Account->select_account_heads("",array("acc_user_id"=>$value->user_id));
          $transaction_where['reference_id'] = $account_data->row()->acc_head_id;
          $update_data['total_paid'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transaction_where,'user_commission')->row()->total_amount;
          $update_data['project_commission_amount'] = (float) ($work_details->est_budget_amt*$value->project_commission_percent/100);
          $update_data['total_balance'] = (float)$update_data['project_commission_amount']- (float)$update_data['total_paid'];
          $update_data['assign_log_id'] = $value->assign_log_id;
          $this->Work->update_work_assign_log($update_data);
        }
      }

      $flash_data['flashdata_msg'] = 'Work Status Updated Successfully!.';
      $flash_data['flashdata_type'] = 'info';
      $flash_data['alert_type'] = 'info';
      $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 !!';
    }

    // $this->session->set_flashdata($flash_data);
    // redirect($redirect_url,'refresh');
    $flash_data['message'] = $flash_data['flashdata_msg'];
    echo json_encode($flash_data);  // send data as json format
  }

  public function update_stage_status(){
    $redirect_url= $this->security->xss_clean($this->input->post('redirect_url'));
    $stage_update_data['stage_id'] = $this->security->xss_clean($this->input->post('stage_id'));
    $data['stage_active_status'] = $this->security->xss_clean($this->input->post('stage_status'));

    if($data['stage_active_status']==1){
      $stage_log_data['stage_id'] = $stage_update_data['stage_id'];
      $stage_log_data['project_id'] = $this->Work->select_project_stage("*", array("stage_id" => $stage_update_data['stage_id']))->row()->project_id;
      $stage_log_data['active_on'] = date('Y-m-d h:i:s');
      $result_stage_log = $this->Work->insert_project_stage_log($stage_log_data);
    }

    $result = $this->Work->update_project_stage($stage_update_data,$data);
    $flash_data['status'] = $result;
    if ($result == 1) {

      $flash_data['flashdata_msg'] = 'Stage Status Updated Successfully!.';
      $flash_data['flashdata_type'] = 'info';
      $flash_data['alert_type'] = 'info';
      $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 !!';
    }

    // $this->session->set_flashdata($flash_data);
    // redirect($redirect_url,'refresh');
    $flash_data['message'] = $flash_data['flashdata_msg'];
    echo json_encode($flash_data);  // send data as json format
  }

  public function select_work($list_type=''){

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

    $data=array();
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    if($list_type=='assigned' && isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $supervisor_id = $_POST['user_id'];
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    if(isset($_POST['work_id']) && $_POST['work_id']!='' && $_POST['work_id']!='all'){
      $data['w.work_id'] = $_POST['work_id'];
    }
    if(isset($_POST['work_status']) && $_POST['work_status']!='' && $_POST['work_status']!='all'){
      $work_status = $_POST['work_status'];
      if($work_status==0){
        $data['w.work_status !='] = 1;
      }else{
        $data['w.work_status'] = 1;
      }
    }
    if(isset($_POST['project_section']) && $_POST['project_section']!='' && $_POST['project_section']!='all'){
      $data['w.project_section'] = $_POST['project_section'];
    }
    if(isset($_POST['client']) && $_POST['client']!='' && $_POST['client']!='all'){
      $data['w.client_id'] = $_POST['client'];
    }
    if(isset($_POST['project_type']) && $_POST['project_type']!='' && $_POST['project_type']!='all'){
      $data['w.work_type'] = $_POST['project_type'];
    }
    if(isset($_POST['company_id']) && $_POST['company_id']!='' && $_POST['company_id']!='all'){
      $data['w.company_id'] = $_POST['company_id'];
    }
    if(isset($_POST['parent_project']) && $_POST['parent_project']!='' && $_POST['parent_project']!='all'){
      $data['w.parent_project_id'] = $_POST['parent_project'];
    }
    if(isset($_POST['assigned_user']) && $_POST['assigned_user']!='' && $_POST['assigned_user']!='all'){
      $supervisor_id = $_POST['assigned_user'];
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
      $data['w.branch_id'] = $_POST['branch_id'];
    }
    if($this->session->userdata('branch_id')!=0) {
      $data['w.branch_id'] = $this->session->userdata('branch_id');
    }
    $data['w.delete_status'] = 0;
    $result	= $this->Work->select_work("w.parent_project_id,cm.company_name,w.enquiry_id,w.work_order_id,w.lpo_no,w.reference_no,w.reference_no_2,w.quotation_id,w.project_completed_date,w.project_section,wc.wcat_name,w.work_id,w.work_type,w.work_status,w.work_name,client_name,client_contact,work_location,total_budget_amount,u.user_alias as created_user,w.created_date,u3.user_alias as updated_user,w.updated_date,w.created_by,pa.area_name,w.location_area,w.branch_id",$data);
    $result_array=$result->result();

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

    foreach($result_array as $row):
      $id=$row->work_id;
      $btn_delete='';
      if($this->session->userdata('user_role')=='admin' || (($this->session->userdata('user_role')=='general_manager' || $this->session->userdata('user_role')=='qs_manager' || $this->session->userdata('user_role')=='qs') && $this->session->userdata('user_id')==$row->created_by)) {
        $btn_edit = '<a style="margin-left: 5px;margin-right: 5px"  href="' . base_url() . 'work/edit/' . $id . '" class="btn btn-success m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
        <i class="la la-edit"></i>
        </a>';
        if($row->quotation_id==0 && $row->enquiry_id==0 && $row->work_order_id==0){
          $btn_delete = '<a data-toggle="modal" style="margin-left: 5px;margin-right: 5px" id="work_delete_btn"  href="#work_delete_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>';
        }
      }else{
        $btn_edit=$btn_delete='';
      }

      $btn_profile='<a style="margin-left: 5px;margin-right: 5px"  href="'.base_url().'work/profile/'.$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>';

      if($row->work_status==1){
        $work_status="<span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Completed</span>";
        if($row->project_completed_date!='0000-00-00'){
          $work_status.="<br/>On ".date('d-m-Y',strtotime($row->project_completed_date));
        }
      }elseif($row->work_status==3){
        $work_status="<span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-ellipsis-h'></i> Freeze</span>";
      }else{
        $work_status="<span class='m-badge m-badge--info m-badge--wide'><i class='fa fa-ellipsis-h'></i> In Progress</span>";
      }

      $assigned_users = "";
      if($row->location_area!='' && $row->location_area!=0){
        // Get Area Manager
        $where = "((FIND_IN_SET($row->location_area,u.project_area_location)) and u.user_role=9 and u.project_area_location!=0)";// Area Manager
        $area_managers = $this->User->select_user("u.user_id,u.user_alias",$where)->result();
        foreach ($area_managers as $key => $value) {
          $assigned_users.=$value->user_alias.", ";
        }
      }
      $assigned_users .= $row->users_assigned;
      $assigned_users = rtrim($assigned_users, ', ');
      $slno++;

      $work_name = '';

      $work_name .= '#'.$row->work_id." - ".$row->work_name;
      $other_details = '';
      if($row->lpo_no!=''){
        $other_details .= 'LPO No: '.$row->lpo_no.'<br/>';
      }
      if($row->reference_no_2!=''){
        $other_details .= $this->config->item('Project_reference_no_2_title').': '.$row->reference_no_2;
      }
      $parent_project = '';
      if($row->parent_project_id!=0){
        $parent_project_data = $this->Work->select_work("*",array("work_id"=>$row->parent_project_id))->row();
        $parent_project = "<br/><a href='".base_url()."work/profile/".$parent_project_data->work_id."'><div style='padding: 0.5rem 0.5rem;' class='m-alert m-alert--outline alert alert-info alert-dismissible fade show'><i class='fa fa-sitemap'></i> Sub Project Of #".$parent_project_data->work_id." - ".$parent_project_data->work_name."</div></a>";
      }

      $array[$j][]=$row->work_id;
      $array[$j][]=$slno;
      $array[$j][]=$row->company_name;
      $array[$j][]=($row->branch_id==0)?'No Branch':$this->Branches->select_branches('*',array('branch_id'=>$row->branch_id))->row()->branch_name;
      $array[$j][]='<a style="font-size:15px" href="'.base_url().'work/profile/'.$id.'"><strong>'.$work_name.'</strong></a>'.$parent_project;
      $array[$j][]=$row->reference_no;
      if($list_type=='assigned' || $this->session->userdata('user_role')=='supervisor') {
        if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
          $supervisor_id = $_POST['user_id'];
        }else{
          $supervisor_id = $this->session->userdata('user_id');
        }
        $role = $this->Work->select_work_assign_log("wp.role",array("wp.work_id"=>$row->work_id,"wp.user_id"=>$supervisor_id));
        if($role->num_rows()==1){
          $array[$j][]=$role->row()->role;
        }else{
          $array[$j][]="";
        }
      }elseif($this->session->userdata('user_role')!='supervisor') {
        $array[$j][]=$assigned_users;
      }
      $array[$j][]=$row->client_name;
      $array[$j][]=$row->client_contact;
      $array[$j][]=$row->work_location;
      $array[$j][]=$row->area_name;
      $array[$j][]=$row->project_section;
      $array[$j][]=$row->wcat_name;
      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='auditor') {
        $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($row->total_budget_amount,$this->config->item('Currency_currency_shortname'));
      }
      $array[$j][]=$work_status;
      $array[$j][]=$other_details;
      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->created_user."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->created_user.'</li>';
      }
      $array[$j][]='';
      $array[$j][]='';
      if($row->updated_date!='0000-00-00 00:00:00'){
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Updated on : '.date('d-m-Y',strtotime($row->created_date))."</li><li>Updated By : ".$row->updated_user."</li>";
      }else{
        $array[$j][]='';
      }

      $array[$j][]=$btn_profile;
      $array[$j][]=$btn_edit.$btn_delete;

      $j++;
    endforeach;

    $json_data['data']=$array;

    echo json_encode($json_data);  // send data as json format
  }

  public function select_project_enquiry_report($list_type=''){

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

    $data=array();
    if($this->session->userdata('user_role')=='supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    if($list_type=='assigned' && isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
      $supervisor_id = $_POST['user_id'];
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    if(isset($_POST['work_id']) && $_POST['work_id']!='' && $_POST['work_id']!='all'){
      $data['w.work_id'] = $_POST['work_id'];
    }
    if(isset($_POST['work_status']) && $_POST['work_status']!='' && $_POST['work_status']!='all'){
      $work_status = $_POST['work_status'];
      if($work_status==0){
        $data['w.work_status !='] = 1;
      }else{
        $data['w.work_status'] = 1;
      }
    }
    if(isset($_POST['project_section']) && $_POST['project_section']!='' && $_POST['project_section']!='all'){
      $data['w.project_section'] = $_POST['project_section'];
    }
    if(isset($_POST['client']) && $_POST['client']!='' && $_POST['client']!='all'){
      $data['w.client_id'] = $_POST['client'];
    }
    if(isset($_POST['project_type']) && $_POST['project_type']!='' && $_POST['project_type']!='all'){
      $data['w.work_type'] = $_POST['project_type'];
    }
    if(isset($_POST['assigned_user']) && $_POST['assigned_user']!='' && $_POST['assigned_user']!='all'){
      $supervisor_id = $_POST['assigned_user'];
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
      $data['w.branch_id'] = $_POST['branch_id'];
    }
    if($this->session->userdata('branch_id')!=0) {
      $data['w.branch_id'] = $this->session->userdata('branch_id');
    }
    $result	= $this->Work->select_work("w.lpo_no,w.planned_date_of_start,w.planned_date_of_completion,w.work_description,c.client_address,c.contact_person,w.enquiry_id,w.work_order_id,w.lpo_no,w.reference_no,w.reference_no_2,w.quotation_id,w.project_completed_date,w.project_section,wc.wcat_name,w.work_id,w.work_type,w.work_status,w.work_name,client_name,client_contact,work_location,total_budget_amount,u.user_alias as created_user,w.created_date,u3.user_alias as updated_user,w.updated_date,w.created_by,pa.area_name,w.location_area,w.branch_id,w.lpo_status,w.lpo_no,w.lpo_date,w.lpo_value",$data);
    $result_array=$result->result();

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

    foreach($result_array as $row):
      $id=$row->work_id;

      if($row->work_status==1){
        $work_status="<span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Completed</span>";
        if($row->project_completed_date!='0000-00-00'){
          $work_status.="<br/>On ".date('d-m-Y',strtotime($row->project_completed_date));
        }
      }elseif($row->work_status==3){
        $work_status="<span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-ellipsis-h'></i> Freeze</span>";
      }else{
        $work_status="<span class='m-badge m-badge--info m-badge--wide'><i class='fa fa-ellipsis-h'></i> In Progress</span>";
      }

      $assigned_users = "";
      if($row->location_area!='' && $row->location_area!=0){
        // Get Area Manager
        $where = "((FIND_IN_SET($row->location_area,u.project_area_location)) and u.user_role=9 and u.project_area_location!=0)";// Area Manager
        $area_managers = $this->User->select_user("u.user_id,u.user_alias",$where)->result();
        foreach ($area_managers as $key => $value) {
          $assigned_users.=$value->user_alias.", ";
        }
      }
      $assigned_users .= $row->users_assigned;
      $assigned_users = rtrim($assigned_users, ', ');
      $slno++;

      $work_name = '';

      $work_name .= '#'.$row->work_id." - ".$row->work_name;
      $other_details = '';
      if($row->reference_no!=''){
        $other_details .= $this->config->item('Project_reference_no_title').': '.$row->reference_no;
      }

      if($row->enquiry_id!=0){
        $enquiry_data = $this->Enquiries->select_enquiries("e.*,s.service_name,st.service_type_name",array("enquiry_id"=>$row->enquiry_id));
        if($enquiry_data->num_rows()==1){
          $enquiry_data = $enquiry_data->row();
          if($enquiry_data->enquiry_priority==1){
            $enquiry_priority="<span class='m-badge m-badge--danger m-badge--wide'> High</span>";
          }elseif($enquiry_data->enquiry_priority==2){
            $enquiry_priority="<span class='m-badge m-badge--warning m-badge--wide'> Med</span>";
          }else{
            $enquiry_priority="<span class='m-badge m-badge--info m-badge--wide'> Low</span>";
          }
        }else{
          unset($enquiry_data);
        }
      }else{
        unset($enquiry_data);
      }

      $array[$j][]=$slno;
      $array[$j][]=($row->branch_id==0)?'No Branch':$this->Branches->select_branches('*',array('branch_id'=>$row->branch_id))->row()->branch_name;
      $array[$j][]=$row->work_id;
      if(isset($enquiry_data)){
        $array[$j][]="<a href='".base_url()."enquiries/profile/".$enquiry_data->enquiry_id."'>#".$enquiry_data->enquiry_id."</a><br/>".$enquiry_data->enquiry_reference_no;
      }else{
        $array[$j][]='';
      }
      $array[$j][]='<a href="'.base_url().'work/profile/'.$id.'">'.$work_name.'</a>';
      $array[$j][]= date('d-m-Y',strtotime($row->created_date));
      $array[$j][]=$row->client_name;
      $array[$j][]=$row->client_address;
      if(isset($enquiry_data)){
        $array[$j][]=$enquiry_data->enq_contact_person;
        $array[$j][]=$enquiry_data->enq_contact_person_mobile;
      }else{
        $array[$j][]=$row->contact_person;
        $array[$j][]=$row->client_contact;
      }
      if(isset($enquiry_data)){
        $array[$j][]=$enquiry_data->enquiry_mode;
        $array[$j][]=$enquiry_priority;
        $array[$j][]=$enquiry_data->service_name;
        $array[$j][]=$enquiry_data->service_details;
        $array[$j][]=$enquiry_data->service_type_name;
        $array[$j][]=$enquiry_data->work_place;
        $array[$j][]=$enquiry_data->nature_of_enquiry;
        $array[$j][]=$enquiry_data->observations;
        $array[$j][]=$enquiry_data->actions_taken;
      }else{
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
      }
      $array[$j][]='';
      if(isset($enquiry_data)){
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]=$enquiry_data->enquiry_status;
      }else{
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
      }
      $array[$j][]=$row->reference_no_2;
      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='auditor') {
        $array[$j][]=$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($row->total_budget_amount,$this->config->item('Currency_currency_shortname'));
      }else{
        $array[$j][]='';
      }
      $array[$j][]=$row->lpo_status;
      $array[$j][]=$row->lpo_no;
      $array[$j][]=$row->lpo_value;
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]=$row->work_description;
      if($row->planned_date_of_start!='0000-00-00'){
        $array[$j][]= date('d-m-Y',strtotime($row->planned_date_of_start));
      }else{
        $array[$j][]='';
      }
      if($row->planned_date_of_completion!='0000-00-00'){
        $array[$j][]= date('d-m-Y',strtotime($row->planned_date_of_completion));
      }else{
        $array[$j][]='';
      }
      if($list_type=='assigned' || $this->session->userdata('user_role')=='supervisor') {
        if(isset($_POST['user_id']) && $_POST['user_id']!='' && $_POST['user_id']!='all'){
          $supervisor_id = $_POST['user_id'];
        }else{
          $supervisor_id = $this->session->userdata('user_id');
        }
        $role = $this->Work->select_work_assign_log("wp.role",array("wp.work_id"=>$row->work_id,"wp.user_id"=>$supervisor_id));
        if($role->num_rows()==1){
          $array[$j][]=$role->row()->role;
        }else{
          $array[$j][]="";
        }
      }elseif($this->session->userdata('user_role')!='supervisor') {
        $array[$j][]=$assigned_users;
      }
      $array[$j][]=$row->project_section;
      $array[$j][]=$row->wcat_name;
      $array[$j][]=$work_status;
      $array[$j][]=$other_details;
      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->created_user."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->created_user.'</li>';
      }

      $j++;
    endforeach;

    $json_data['data']=$array;

    echo json_encode($json_data);  // send data as json format
  }

  public function print_summary($work_id=''){
    $this->page_data['page_name'] = 'work_summary';
    $this->page_data['work_data'] = $this->Work->select_work("*", array("work_id" => $work_id))->row();
    $this->page_data['work'] = $work_id;
    $this->load->view('Index',$this->page_data);
  }
  public function print_summary_master($work_id=''){
    if($work_id!="" && $this->config->item('Project_parent_child_enable') == 1) {
      $this->page_data['page_name'] = 'work_summary_master';
      $this->page_data['work_data'] = $this->Work->select_work("*", array("work_id" => $work_id))->row();
      $this->page_data['work'] = $work_id;
      $this->page_data['sub_projects'] = $this->page_data['sub_project_ids'] = array();

      $this->page_data['sub_projects'] = $this->Work->select_work("*",array("parent_project_id"=>$work_id))->result();
      foreach ($this->page_data['sub_projects'] as $key => $value) {
        $this->page_data['sub_project_ids'][] = $value->work_id;
      }
      if(sizeof($this->page_data['sub_projects'])>0){
        $this->page_data['parent_project'] .= "<div style='font-size:20px;font-weight:600;padding: 0.5rem 0.5rem;' class='m-alert m-alert--outline alert alert-warning alert-dismissible fade show'><i class='fa fa-sitemap'></i> ".sizeof($this->page_data['sub_projects'])." Sub Projects</div>";
      }

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

  public function print_stage_summary($work_id='',$stage_id=''){
    $this->page_data['page_name'] = 'stage_summary';
    $this->page_data['work_data'] = $this->Work->select_work("*", array("work_id" => $work_id))->row();
    $this->page_data['stage_data'] = $this->Work->select_project_stage("*", array("stage_id" => $stage_id))->row();

    $this->page_data['work'] = $work_id;
    $this->page_data['stage'] = $stage_id;

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

  public function select_summary($return_type='',$project_page_type=''){
    $start_date = $end_date = '';
    $project_id = $_POST['work_id'];
    if(isset($_POST['start_date']) && $_POST['start_date']!=''){
      $start_date = $_POST['start_date'];
    }
    if(isset($_POST['end_date']) && $_POST['end_date']!=''){
      $end_date = $_POST['end_date'];
    }
    $project_summary_data = $this->projects_management->get_project_summary($project_id,$start_date,$end_date,$project_page_type);
    foreach ($project_summary_data as $key => $value) {
      if($key!='total_labour_days' && $key!='total_labours_worked' && $key!='total_labours_worked_distinct'){
        $project_summary_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($project_summary_data);  // send data as json format
    }else {

    }
  }

  public function select_stage_summary($return_type=''){
    $json_data=$json_data2=array();
    $j=$sl_no=0;
    $data=array();
    $id = $_POST['stage_id'];
    $work_id = $_POST['work_id'];
    $work_data = $this->Work->select_work("*", array("work_id" => $work_id))->row();
    $stage_data = $this->Work->select_project_stage("*", array("stage_id" => $id))->row();
    $json_data['total_stage_budget'] = $stage_data->stage_budget;
    $json_data['total_project_budget'] = $work_data->total_budget_amount;

    $user_commission_where = $salary_payment_where = $purchase_order_payment_where = $client_payment_where = $other_expense_where = $purchase_payment_where = $purchase_bill_where= $subcontract_payment_where = $vehicle_payment_where = array();
    if($this->config->item('user_data_access_view_all')!=1){
      $user_commission_where['ats.created_by'] = $vehicle_payment_where['ats.created_by'] = $salary_payment_where['ats.created_by'] = $purchase_order_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $purchase_payment_where['ats.created_by'] = $purchase_bill_where['p.created_by'] = $subcontract_payment_where['ats.created_by'] = $this->session->userdata('user_id');
    }

    $client_payment_where['ats.tr_project_stage_id'] = $id;
    $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
    $client_payment_where['ats.reference_type'] = 'project';
    if($this->session->userdata('user_role')=='general_manager' && $this->config->item('User_general_manager_client_payment_view_all')==0) {
      $client_payment_where['ats.created_by'] = $this->session->userdata('user_id');
    }
    $json_data['total_client_payments_received'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$client_payment_where,'client_payment')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $other_expense_where['ats.tr_project_stage_id'] = $id;
    $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
    $other_expense_where['ats.reference_type'] = 'expense_bill';
    $json_data['total_other_expense'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$other_expense_where,'expense_bill')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $salary_payment_where['ats.tr_project_stage_id'] = $id;
    $this->db->where("(ats.tr_type='salary_slip_transaction' or ats.tr_type='salary_advance_transaction' or ats.tr_type='employee_bata_transaction')");
    $this->db->where("(ats.reference_type='salary_slip' or ats.reference_type='employee')");
    $json_data['total_salary_payments'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$salary_payment_where,'employee')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $user_commission_where['ats.tr_project_stage_id'] = $id;
    $user_commission_where['ats.tr_type'] = 'user_commission_transaction';
    $user_commission_where['ats.reference_type'] = 'user_account_head';
    $json_data['total_user_commission_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$user_commission_where,'user_commission')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $vehicle_payment_where['ats.tr_project_stage_id'] = $id;
    $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
    $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
    $json_data['total_vehicle_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_payment_where,'vehicle_work_slip')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $purchase_payment_where['ats.tr_project_stage_id'] = $id;
    $purchase_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
    $purchase_payment_where['ats.reference_type'] = 'purchase_bill';
    $json_data['total_purchase_bill_payment'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$purchase_payment_where,'purchase_bill')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $purchase_order_payment_where['ats.tr_project_stage_id'] = $id;
    $purchase_order_payment_where['ats.tr_type'] = 'purchase_order_transaction';
    $purchase_order_payment_where['ats.reference_type'] = 'purchase_order';
    // $purchase_order_payment_where['po.purchase_bill_id'] = 0;
    $json_data['total_purchase_order_payment'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$purchase_order_payment_where,'purchase_order')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $json_data['total_purchase_payment'] = $json_data['total_purchase_order_payment']+$json_data['total_purchase_bill_payment'];

    $purchase_bill_where['p.project_stage_id'] = $id;
    $json_data['total_purchase_bill_amount'] = round($this->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as grand_total",$purchase_bill_where)->row()->grand_total,$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_purchase_bill_balance_amount'] = round($this->Purchases->select_purchase("COALESCE(SUM(p.balance_amount),0) as total_purchase_balance",$purchase_bill_where)->row()->total_purchase_balance,$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_purchase_bill_commission'] = round($this->Purchases->select_purchase("COALESCE(SUM(p.bill_commission_amount),0) as total_amount",$purchase_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $this->db->where("((ats.reference_type='subcontract_bill' and b.project_stage_id=$id) or (ats.reference_type='subcontract' and sc2.project_stage_id=$id))");
    $json_data['total_subcontract_payment'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$subcontract_payment_where,'subcontract_transactions')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $labour_wage_where_data['lw.project_stage_id'] = $id;
    $json_data['total_labour_wages'] = round($this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.total_wage),0) as total",$labour_wage_where_data)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $stock_transfer_in_where['sl.work_id'] = $work_id;
    $json_data['total_stock_transfer_in_amount'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_in_qty*sr.unit_price),0) as total", $stock_transfer_in_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $stock_transfer_out_where['sl.work_id'] = $work_id;
    $json_data['total_stock_transfer_out_amount'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_transferred_out_qty*sr.unit_price),0) as total", $stock_transfer_out_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $stock_consumption_where['sl.work_id'] = $work_id;
    $json_data['total_stock_consumption_amount'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_consumption_qty*sr.unit_price),0) as total", $stock_consumption_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $stock_balance_where['sl.work_id'] = $work_id;
    $json_data['total_stock_balance_amount'] = round($this->Stock->select_stock_register("COALESCE(SUM(sr.total_balance_qty*sr.unit_price),0) as total", $stock_balance_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $project_commission_percent = $work_data->project_management_commission;
    $work_type = $work_data->work_type;
    $json_data['total_expense_amount'] = $json_data['total_user_commission_payments'] + $json_data['total_other_expense']  + $json_data['total_purchase_payment'] +  $json_data['total_subcontract_payment'] + $json_data['total_labour_wages'] + $json_data['total_salary_payments'] + $json_data['total_vehicle_payments'];
    $json_data['total_project_commission_amount'] = round((($json_data['total_expense_amount'] * $project_commission_percent)/100),$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_amount_to_receive'] = $json_data['total_stage_budget'];
    $json_data['balance_to_receive'] = round($json_data['total_stage_budget'] - $json_data['total_client_payments_received'],$this->config->item('Currency_amount_decimals_2'));
    $json_data['total_profit'] = round($json_data['total_client_payments_received'] - $json_data['total_expense_amount'],$this->config->item('Currency_amount_decimals_2'));
    // if($work_type==1) {
    //   $json_data['gross_profit'] = round($json_data['total_client_payments_received'] - ($json_data['total_expense_amount'] + $json_data['total_project_commission_amount']),$this->config->item('Currency_amount_decimals_2'));
    // }else{
    //   $json_data['gross_profit'] = round($json_data['total_client_payments_received'] - ($json_data['total_expense_amount']),$this->config->item('Currency_amount_decimals_2'));
    // }

    // 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'));
    // }
    // $json_data['total_labours_worked'] = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.no_of_labours),0) as total",$labour_wage_where_data)->row()->total;
    // $json_data['total_labour_days'] = $this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.work_days),0) as total",$labour_wage_where_data)->row()->total;

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


    }
  }
  public function select_assigned_users($param1='',$user_types=''){

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

    $data=array();
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('wp.work_id',explode(',',$_POST['work_ids']));
    }
    if(isset($_POST['work_id']) && $_POST['work_id']!='' && $_POST['work_id']!='all'){
      $data['wp.work_id'] = $_POST['work_id'];
    }
    $result	= $this->Work->select_work_assign_log("wp.*,u.user_alias as assigned_user_name,u.user_designation,ur.role_title",$data);
    $result_array=$result->result();

    $array=array();
    $sl_no=0;
    foreach($result_array as $row):
      $sl_no++;

      $btn_add_payment = '';
      if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='general_manager') {
        $btn_delete = '<a style="margin-left: 5px;margin-right: 5px" id="btn_delete_user_assign" data-toggle="modal" href="#user_assign_delete_modal" class="btn btn-danger m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
        <i class="la la-trash"></i>
        </a>';
        if($this->config->item('User_project_commission_payment')==1){
          $btn_add_payment = '<a style="margin-left: 5px;margin-right: 5px" id="btn_add_user_commission_payment" data-toggle="modal" href="#user_commission_payment_add_modal" class="btn btn-focus m-btn m-btn--icon  m-btn--icon-only  m-btn--pill m-btn--air">
          <i class="la la-plus-circle"></i>
          </a>';
        }
      }else{
        $btn_delete = '';
      }

      $user_acc_head_id = '';
      $account_data = $this->Account->select_account_heads("",array("acc_user_id"=>$row->user_id));
      if($account_data->num_rows()==1){
        $user_acc_head_id = $account_data->row()->acc_head_id;
      }

      $array[$j][]=$sl_no;
      $array[$j][]=$row->assign_log_id;
      $array[$j][]='<a href="'.base_url().'user/profile/'.$row->user_id.'">'.$row->assigned_user_name.'</a><br/>'.$row->user_designation;
      $array[$j][]=$row->role;
      $array[$j][]=$row->project_commission_percent;
      $array[$j][]=$row->project_commission_amount;
      $array[$j][]=$row->total_paid;
      $array[$j][]=$row->total_balance;
      $array[$j][]=$user_acc_head_id;
      $array[$j][]=$btn_add_payment.$btn_delete;

      $id[] = $row->user_id;
      $name[] = $row->assigned_user_name." [".$row->role_title."]";

      $j++;
    endforeach;


    $work_data	= $this->Work->select_work("w.location_area",array("w.work_id"=>$_POST['work_id']));
    // Select Area Manager
    if($work_data->num_rows()==1 && $work_data->row()->location_area!='' && $work_data->row()->location_area!=0){
      // Get Area Manager
      $location_area = $work_data->row()->location_area;
      $where = "((FIND_IN_SET($location_area,u.project_area_location)) and u.user_role=9 and u.project_area_location!=0)";// Area Manager
      $area_managers = $this->User->select_user("u.user_id,u.user_alias,u.user_designation",$where)->result();
      foreach ($area_managers as $key => $value) {
        $sl_no++;
        $array[$j][]=$sl_no;
        $array[$j][]='';
        $array[$j][]='<a href="'.base_url().'user/profile/'.$value->user_id.'">'.$value->user_alias.'</a><br/>'.$row->user_designation;
        $array[$j][]='Area Manager';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $array[$j][]='';
        $j++;

      }
    }


    if($user_types==2){
      //Select Manager Accounts
      $user_data='ur.role_id!=9 and ur.role_name!="supervisor"';
      $users = $this->User->select_user_2("*",$user_data);
      foreach ($users->result() as $key => $value) {
        $id[] = $value->user_id;
        $name[] = $value->user_alias." [".$value->role_title."]";
      }
    }

    if($param1=='select'){
      if(sizeof($id)>0){
        $id = array_unique($id);
        $name = array_unique($name);
        echo json_encode(array("count" => sizeof($id),"id" => $id,"name" => $name));
      }else{
        echo json_encode(array("count" => sizeof($id)));
      }
    }else{
      $json_data['draw']=5;
      $json_data['recordsTotal']=sizeof($array);
      $json_data['recordsFiltered']=sizeof($array);
      $json_data['data']=$array;
      echo json_encode($json_data);  // send data as json format
    }

  }
  public function assign_user()
  {
    //      insert client
    $data['user_id'] = $data2['wp.user_id'] = $this->security->xss_clean($this->input->post('user_assign'));
    $data['work_id'] =  $data2['wp.work_id'] = $this->security->xss_clean($this->input->post('work_id'));

    $count = $this->Work->select_work_assign_log('',$data2)->num_rows();

    if($count==0){
      $data['role'] = $this->security->xss_clean($this->input->post('role'));
      if($this->input->post('commission_percent')){
        $data['project_commission_percent'] = $this->security->xss_clean($this->input->post('commission_percent'));
      }
      $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->Work->create_work_assign_log($data);
      $flash_data['status'] = $result;
      if($result == 1){

        // Update assigned Supervisor in work
        $this->update_work_user_assigned($data['work_id']);

        $flash_data['flashdata_msg'] = 'User Assigned to This Work 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.. Selected User is Already Assigned to This Work!';
      $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_assign_user()
  {
    //      insert client
    $data['assign_log_id'] = $this->security->xss_clean($this->input->post('assign_log_id'));
    //      Select Work Id
    $work_id	= $this->Work->select_work_assign_log("wp.work_id",$data)->row()->work_id;

    $result = $this->Work->delete_work_assign_log($data);
    $flash_data['status'] = $result;
    if($result == 1){

      // Update assigned Supervisor in work
      $this->update_work_user_assigned($work_id);

      $flash_data['flashdata_msg'] = 'User Assignation 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_work_user_assigned($work_id)
  {
    // Select Assigned Supervisors
    $assigned_users=array();
    $data['wp.work_id'] = $work_id;
    $users	= $this->Work->select_work_assign_log("wp.user_id",$data)->result();
    foreach ($users as $key => $value) {
      $assigned_users[] = $value->user_id;
    }

    $data2['work_id'] = $work_id;
    $data2['assigned_users'] = implode(',',$assigned_users);
    $result2 = $this->Work->update_work($data2);

    return $result2;
  }

  public function select_details(){
    $data=array();
    if(isset($_POST['work_id']) && $_POST['work_id']!=""){
      $data['work_id'] = $_POST['work_id'];
    }
    $result	= $this->Work->select_work("*,c.tax_no as client_taxno",$data);
    if($result->num_rows()==1) {
      $row = $result->row();
      if($row->lpo_date!='0000-00-00'){
        $row->lpo_date = date('d-m-Y',strtotime($row->lpo_date));
      }
      echo json_encode(array("count" => $result->num_rows(),"data" => $row));
    }else{
      echo json_encode(array("count" => $result->num_rows()));
    }
  }

  //Project Area
  public function select_area_locations_list(){
    $data=array();
    $result	= $this->Work->select_project_area("*",$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()));
    }
  }
  public function select_area_locations(){

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

    $data=array();
    $result	= $this->Work->select_project_area("",$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="area_location_edit_btn" href="#area_location_edit_modal" data-toggle="modal" class="btn btn-info m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="la la-edit"></i>
        </a>';
      }
      $array[$j][]=$row->area_id;
      $array[$j][]=$row->area_name;
      $array[$j][]=$btn_edit;



      $j++;
    endforeach;

    $json_data['data']=$array;
    echo json_encode($json_data);  // send data as json format
  }
  public function create_area()
  {
    $data2['area_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Work->select_project_area("area_id",array('area_name' => $data2['area_name']))->num_rows();
    if($count==0){
      $result2 =  $this->Work->insert_project_area($data2);
      $flash_data['status'] = $result2['status'];
      if ($result2['status'] == 1) {
        $flash_data['flashdata_msg'] = 'New Project Area 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.. Submitted Area Name Already Exists!';
      $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_area()
  {
    $data2['area_id'] = $this->security->xss_clean($this->input->post('area_id'));
    $data2['area_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Work->select_project_area("area_id",array('area_name' => $data2['area_name'],'area_id !=' => $data2['area_id']))->num_rows();
    if($count==0){
      $result2 = $this->Work->update_project_area($data2);
      $flash_data['status'] = $result2;
      if ($result2 == 1) {
        $flash_data['flashdata_msg'] = 'Project Area 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.. Submitted Area Name Already Exists!';
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Duplication !!';
    }
    echo(json_encode($flash_data));
  }
  public function select_area(){
    $result	= $this->Work->select_project_area("",$data);
    if($result->num_rows()>0) {
      foreach ($result->result() as $row):
        $id[] = $row->area_id;
        $name[] = $row->area_name;
      endforeach;
      echo json_encode(array("count" => $result->num_rows(),"id" => $id,"name" => $name));
    }else{
      echo json_encode(array("count" => $result->num_rows()));
    }
  }

  // Project Stages
  public function create_stage()
  {
    $data['project_id'] = $this->security->xss_clean($this->input->post('work_id'));
    $data['stage_name'] = $this->security->xss_clean($this->input->post('stage_name'));
    $data['stage_no'] = $this->security->xss_clean($this->input->post('stage_no'));
    $data['stage_budget'] = $this->security->xss_clean($this->input->post('budget'));

    if($this->security->xss_clean($this->input->post('start_date'))!=''){
      $data['stage_start_date'] = date('Y-m-d',strtotime($this->input->post('start_date')));
    }else{
      $data['stage_start_date'] = '0000-00-00';
    }

    if($this->security->xss_clean($this->input->post('end_date'))!=''){
      $data['stage_end_date'] = date('Y-m-d',strtotime($this->input->post('end_date')));
    }else{
      $data['stage_end_date'] = '0000-00-00';
    }

    // $data['stage_end_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('end_date'))));
    $data['stage_description'] = $this->security->xss_clean($this->input->post('description'));
    $data['stage_active_status'] = $this->security->xss_clean($this->input->post('active_status'));
    if($data['stage_active_status'] =='on'){
      $data['stage_active_status'] = 1;
    }
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');

    $active_count = $this->Work->select_project_stage("stage_id",array('ps.stage_active_status' => 1,'ps.project_id' => $data['project_id']))->num_rows();

    if($active_count==0 || $data['stage_active_status'] != 1){
      $count = $this->Work->select_project_stage("stage_id",array('ps.stage_no' => $data['stage_no'],'ps.project_id' => $data['project_id']))->num_rows();
      if($count==0){
        $result = $this->Work->insert_project_stage($data);
        if($data['stage_active_status'] =='on'){
          // Deactivate all other stages of this Project
          $stage_update_where['ps.stage_id !='] = $result['insert_id'];
          $stage_update_where['ps.project_id'] = $data['project_id'];
          $stage_update_data['stage_active_status'] = 0;
          $this->Work->update_project_stage($stage_update_where,$stage_update_data);
        }
        $flash_data['status'] = $result['status'];
        if ($flash_data['status'] == 1) {
          $flash_data['flashdata_msg'] = 'Project Stage 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 !!';
        }
      }else {
        $flash_data['status'] = 0;
        $flash_data['flashdata_msg'] = 'Sorry.. Submitted Stage No Already Exists!';
        $flash_data['flashdata_type'] = 'warning';
        $flash_data['alert_type'] = 'warning';
        $flash_data['flashdata_title'] = 'Duplication !!';
      }
    }else {
      $flash_data['status'] = 0;
      $flash_data['flashdata_msg'] = 'Sorry.. There is a Stage Active ! You can only add in-active stages';
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Duplication !!';
    }
    echo(json_encode($flash_data));
  }

  public function update_stage()
  {
    $data['stage_id'] = $this->security->xss_clean($this->input->post('stage_id'));

    $data['project_id'] = $this->security->xss_clean($this->input->post('work_id'));
    $data['stage_name'] = $this->security->xss_clean($this->input->post('stage_name'));
    $data['stage_no'] = $this->security->xss_clean($this->input->post('stage_no'));
    $data['stage_budget'] = $this->security->xss_clean($this->input->post('budget'));

    if($this->security->xss_clean($this->input->post('start_date'))!=''){
      $data['stage_start_date'] = date('Y-m-d',strtotime($this->input->post('start_date')));
    }else{
      $data['stage_start_date'] = '0000-00-00';
    }

    if($this->security->xss_clean($this->input->post('end_date'))!=''){
      $data['stage_end_date'] = date('Y-m-d',strtotime($this->input->post('end_date')));
    }else{
      $data['stage_end_date'] = '0000-00-00';
    }

    // $data['stage_end_date'] = date('Y-m-d',strtotime($this->security->xss_clean($this->input->post('end_date'))));
    $data['stage_description'] = $this->security->xss_clean($this->input->post('description'));
    $data['stage_active_status'] = $this->security->xss_clean($this->input->post('active_status'));
    $active_stages_count = 1;
    if($data['stage_active_status'] =='on'){
      $data['stage_active_status'] = 1;
    }
    else{
      $stage_active_status = $this->Work->select_project_stage("stage_active_status",array('ps.stage_id'=>$data['stage_id']))->row()->stage_active_status;
      if($stage_active_status==1){
        $active_stages_count = $this->Work->select_project_stage("stage_id",array('ps.stage_active_status' => 1,'ps.project_id' => $data['project_id']))->num_rows()-1;
      }
    }

    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');

    $active_count = $this->Work->select_project_stage("stage_id",array('ps.stage_active_status' => 1,'ps.project_id' => $data['project_id'],'ps.stage_id!='=>$data['stage_id']))->num_rows();



    if($active_stages_count==1){


      if($active_count==0 || $data['stage_active_status'] != 1){
        $count = $this->Work->select_project_stage("stage_id",array('ps.stage_no' => $data['stage_no'],'ps.project_id' => $data['project_id'],'ps.stage_no!='=>$data['stage_no']))->num_rows();
        if($count==0){
          $data_where['stage_id'] = $data['stage_id'];

          $result = $this->Work->update_project_stage($data_where,$data);

          if($data['stage_active_status'] =='on'){
            // Deactivate all other stages of this Project
            $stage_update_where['ps.stage_id !='] = $data['stage_id'];
            $stage_update_where['ps.project_id'] = $data['project_id'];
            $stage_update_data['stage_active_status'] = 0;
            $this->Work->update_project_stage($stage_update_where,$stage_update_data);

          }

          $flash_data['status'] = $result;
          if ($flash_data['status'] == 1) {
            $flash_data['flashdata_msg'] = 'Project Stage Update 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.. Submitted Stage No Already Exists!';
          $flash_data['flashdata_type'] = 'warning';
          $flash_data['alert_type'] = 'warning';
          $flash_data['flashdata_title'] = 'Duplication !!';
        }
      }else {
        $flash_data['status'] = 0;
        $flash_data['flashdata_msg'] = 'Sorry.. There is a Stage Active ! You can only add in-active stages';
        $flash_data['flashdata_type'] = 'warning';
        $flash_data['alert_type'] = 'warning';
        $flash_data['flashdata_title'] = 'Duplication !!';
      }

    }else{
      $flash_data['status'] = 0;
      $flash_data['flashdata_msg'] = 'Sorry.. There must be atleast 1 active stage';
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Duplication !!';
    }

    echo(json_encode($flash_data));
  }

  public function select_project_stages(){

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

    if(isset($_POST['project']) && $_POST['project']!='' && $_POST['project']!='all'){
      $data['ps.project_id'] = $_POST['project'];
    }

    $result = $this->Work->select_project_stage("*",$data);
    $result_array=$result->result();
    $json_data['draw']=5;
    $json_data['recordsTotal']=$result->num_rows();
    $json_data['recordsFiltered']=$result->num_rows();
    $array=$array_out=array();
    $btn_payment='';

    foreach($result_array as $row):

      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="project_stages_edit_btn" href="#project_stages_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="invoice_delete_btn" href="#invoice_delete_modal" data-toggle="modal" class="btn btn-danger m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="la la-trash"></i>
        </a>';
      }else{
        $btn_edit=$btn_delete='';
      }
      $btn_delete='';

      if($row->stage_active_status==1){
        $status = '<span style="margin-top: 5px" class="m-badge m-badge--success m-badge--wide"><i class="fa fa-check-circle"></i> Active</span>';
      }else{
        $status = '<span style="margin-top: 5px" class="m-badge m-badge--danger m-badge--wide"><i class="fa fa-times-circle"></i> In-active</span>';
      }

      if($row->stage_start_date!='0000-00-00'){
        $start = date('d-m-Y',strtotime($row->stage_start_date));;
      }else{
        $start = '-';
      }

      if($row->stage_end_date!='0000-00-00'){
        $end = date('d-m-Y',strtotime($row->stage_end_date));;
      }else{
        $end = '-';
      }

      $sl_no++;
      $array[$j][]=$row->stage_id;
      $array[$j][]=$row->stage_no;
      $array[$j][]='<a href="'.base_url().'work/stage_profile/'.$row->stage_id.'" >'.$row->stage_name.'</a>';
      $array[$j][]=$row->stage_budget;
      $array[$j][]=$start;
      $array[$j][]=$end;
      $array[$j][]=$this->projects_management->calculate_project_stage_income($row->stage_id);
      $array[$j][]=$this->projects_management->calculate_project_stage_expense($row->stage_id);;
      $array[$j][]=$status;
      $array[$j][]=$btn_payment.$btn_edit.$btn_delete;
      $array[$j][]=$row->stage_active_status;
      $array[$j][]=$row->stage_description;

      $j++;
    endforeach;

    $json_data['data']=$array;
    echo json_encode($json_data);
  }

  public function update_work_stage()
  {
    $data['stage_id'] = $this->security->xss_clean($this->input->post('project_stage'));
    $data['project_id'] = $this->security->xss_clean($this->input->post('work_id'));

    $data_where_1['stage_id !='] = $data['stage_id'];
    $data_where_1['project_id'] = $data['project_id'];
    $data_1['stage_active_status'] = 0;
    $result = $this->Work->update_project_stage($data_where_1,$data_1);

    $data_where_2['stage_id'] = $data['stage_id'];
    $data_2['stage_active_status'] = 1;
    $result = $this->Work->update_project_stage($data_where_2,$data_2);

    // insert Log
    $stage_log_data['stage_id'] = $data['stage_id'];
    $stage_log_data['project_id'] = $data['project_id'];
    $stage_log_data['active_on'] = date('Y-m-d h:i:s');
    $result_stage_log = $this->Work->insert_project_stage_log($stage_log_data);

    $flash_data['status'] = $result;
    if ($flash_data['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Project Stage Update 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));
  }

  // Project  Sections

  public function project_sections()
  {
    $this->page_data['page_name'] = 'project_sections';
    $this->load->view('Index', $this->page_data);
  }

  public function select_project_sections(){

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

    $data=array();
    // $data['delete_status'] = 0;
    $result = $this->Work->select_project_sections("",$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 = 1;
    foreach($result_array as $row):
      $btn_edit=$btn_delete='';
      if($this->session->userdata('user_role')!='auditor'){

        $btn_edit='<a style="margin-left: 5px;margin-right: 5px" id="project_section_edit_btn" href="#project_section_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="project_section_delete_btn" href="#project_section_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->section_id;
      $array[$j][]=$sl_no++;
      $array[$j][]=$row->section_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 update_project_section()
  {
    $data2['section_id'] = $this->security->xss_clean($this->input->post('section_id'));
    $data2['section_name'] = $this->security->xss_clean($this->input->post('project_section_name'));
    $count = $this->Work->select_project_sections("section_id",array('section_name' => $data2['section_name'],'section_id !=' => $data2['section_id']))->num_rows();
    if($count==0){
      $result2 = $this->Work->update_project_section($data2);
      $flash_data['status'] = $result2;
      if ($result2 == 1) {
        $flash_data['flashdata_msg'] = 'Project Section 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.. Submitted Project Section Already Exists!';
      $flash_data['flashdata_type'] = 'warning';
      $flash_data['alert_type'] = 'warning';
      $flash_data['flashdata_title'] = 'Duplication !!';
    }

    echo(json_encode($flash_data));

  }

  public function delete_project_section()
  {
    $data2['section_id'] = $this->security->xss_clean($this->input->post('section_id'));
    $data2['delete_status'] = 1;
    $result2 = $this->Work->update_project_section($data2);
    $flash_data['status'] = $result2;
    if ($result2 == 1) {
      $flash_data['flashdata_msg'] = 'Project Section Deleted Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';

    } else {
      $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
      $flash_data['flashdata_type'] = 'error';
      $flash_data['alert_type'] = 'danger';
      $flash_data['flashdata_title'] = 'Error !!';
    }

    echo(json_encode($flash_data));

  }
  public function create_project_section()
  {
    $data2['section_name'] = $this->security->xss_clean($this->input->post('name'));
    $count = $this->Work->select_project_sections("section_id",array('section_name' => $data2['section_name']))->num_rows();
    if($count==0){
      $result2 =  $this->Work->insert_project_section($data2);
      $flash_data['status'] = $result2['status'];
      if ($result2['status'] == 1) {
        $flash_data['flashdata_msg'] = 'New Project Section 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.. Submitted Project Section Already Exists!';
      $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_project_sections_list(){
    $data['delete_status'] = 0;
    $result = $this->Work->select_project_sections("",$data);
    if($result->num_rows()>0) {
      foreach ($result->result() as $row):
        $id[] = $row->section_id;
        $name[] = $row->section_no;
      endforeach;
      echo json_encode(array("count" => $result->num_rows(),"id" => $id,"name" => $name));
    }else{
      echo json_encode(array("count" => $result->num_rows()));
    }
  }

  public function create_custom_requirement()
  {
    $data['project_id'] = $this->security->xss_clean($this->input->post('work'));
    $data['requirement_title'] = $this->security->xss_clean($this->input->post('title'));
    $data['requirement_date'] = date('Y-m-d',strtotime($this->input->post('date')));
    $data['requirement_type'] = $this->security->xss_clean($this->input->post('requirement_type'));
    $data['taxable_amount'] = $this->security->xss_clean($this->input->post('taxable_amount'));
    $data['tax_percent'] = $this->security->xss_clean($this->input->post('tax_percent'));
    $data['amount'] = $this->security->xss_clean($this->input->post('amount'));
    $data['requirement_description'] = $this->security->xss_clean($this->input->post('description'));
    $data['requirement_status'] = $this->security->xss_clean($this->input->post('requirement_status'));
    $data['created_date'] = date('Y-m-d H:i:s');
    $data['created_by'] = $this->session->userdata('user_id');
    $result = $this->Work->create_custom_requirement($data);
    $flash_data['status'] = $result['status'];
    if ($flash_data['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Custom Requirement 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_custom_requirement()
  {
    $data['requirement_id'] = $this->security->xss_clean($this->input->post('requirement_id'));
    $data['project_id'] = $this->security->xss_clean($this->input->post('work'));
    $data['requirement_title'] = $this->security->xss_clean($this->input->post('title'));
    $data['requirement_date'] = date('Y-m-d',strtotime($this->input->post('date')));
    $data['taxable_amount'] = $this->security->xss_clean($this->input->post('taxable_amount'));
    $data['tax_percent'] = $this->security->xss_clean($this->input->post('tax_percent'));
    $data['amount'] = $this->security->xss_clean($this->input->post('amount'));
    $data['requirement_type'] = $this->security->xss_clean($this->input->post('requirement_type'));
    $data['requirement_description'] = $this->security->xss_clean($this->input->post('description'));
    $data['requirement_status'] = $this->security->xss_clean($this->input->post('requirement_status_edit'));
    $data['updated_date'] = date('Y-m-d H:i:s');
    $data['updated_by'] = $this->session->userdata('user_id');
    $result = $this->Work->update_custom_requirement($data);
    $flash_data['status'] = $result;
    if ($flash_data['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Custom Requirement 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_custom_requirement()
  {
    $data['requirement_id'] = $this->security->xss_clean($this->input->post('requirement_id'));
    $data['delete_status'] = 1;
    $result = $this->Work->update_custom_requirement($data);
    $flash_data['status'] = $result;
    if ($flash_data['status'] == 1) {
      $flash_data['flashdata_msg'] = 'Custom Requirement 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_custom_requirements(){

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

    $data=array();
    if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
      $data['pcr.project_id'] = $_POST['work'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('pcr.project_id',explode(',',$_POST['work_ids']));
    }
    if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
      $data['wc.branch_id'] = $_POST['branch_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)");
    }
    $result = $this->Work->select_custom_requirement("pcr.*,u.user_alias,wc.work_name,wc.work_id,wc.branch_id,tt.tax_percentage",$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 = 1;
    $total_amount = 0;
    foreach($result_array as $row):
      $btn_edit=$btn_delete='';
      if($this->session->userdata('user_role')!='auditor'){
        $btn_edit='<a style="margin-left: 5px;margin-right: 5px" id="project_custom_requirement_edit_btn" href="#project_custom_requirement_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="project_custom_requirement_delete_btn" href="#project_custom_requirement_delete_modal" data-toggle="modal" class="btn btn-danger m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="la la-trash"></i>
        </a>';
      }

      if($row->requirement_status==1){
        $requirement_status="<span class='m-badge m-badge--success m-badge--wide'><i class='fa fa-check'></i> Completed</span>";
      }elseif($row->requirement_status==2){
        $requirement_status="<span class='m-badge m-badge--danger m-badge--wide'><i class='fa fa-times-circle'></i> Rejected</span>";
      }elseif($row->requirement_status==3){
        $requirement_status="<span class='m-badge m-badge--info m-badge--wide'><i class='fa fa-ellipsis-h'></i> In Progress</span>";
      }else{
        $requirement_status="<span class='m-badge m-badge--warning m-badge--wide'><i class='fa fa-ellipsis-h'></i> Pending</span>";
      }

      $total_amount += $row->amount;
      $array[$j][]=$sl_no++;
      $array[$j][]=($row->branch_id==0)?'No Branch':$this->Branches->select_branches('*',array('branch_id'=>$row->branch_id))->row()->branch_name;
      $array[$j][]=$row->requirement_id;
      $array[$j][]='<a href="'.base_url().'work/profile/'.$row->work_id.'">#'.$row->work_id." - ".$row->work_name.'</a>';
      $array[$j][]=$row->requirement_title;
      $array[$j][]=date('d-m-Y',strtotime($row->requirement_date));
      $array[$j][]=$row->requirement_type;
      $array[$j][]=$row->taxable_amount;
      $array[$j][]=$row->tax_percentage."%";
      $array[$j][]=$row->amount;
      $array[$j][]=$row->requirement_description;
      $array[$j][]=$requirement_status;
      if ($this->config->item('UI_hide_created_date_field')==0){
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created on : '.date('d-m-Y',strtotime($row->created_date))."</li><li>Created By : ".$row->user_alias."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->user_alias.'</li>';
      }
      $array[$j][]=$btn_edit.$btn_delete;
      $array[$j][]=$row->work_id;
      $array[$j][]=$row->requirement_status;
      $array[$j][]=$row->tax_percent;

      $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][] = "<strong>" . $total_amount . "</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
    }

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

  public function create_beneficiary_account()
  {
    $data['project_id'] = $this->security->xss_clean($this->input->post('project'));
    $data['subcontract_id'] = $this->security->xss_clean($this->input->post('subcontract'));
    $data['supervisor_id'] = $this->security->xss_clean($this->input->post('supervisor'));
    $data['customer_name'] = $this->security->xss_clean($this->input->post('beneficiary_name'));
    $data['guardian_name'] = $this->security->xss_clean($this->input->post('guardian_name'));
    $data['customer_address'] = $this->security->xss_clean($this->input->post('address'));
    $data['ward_no'] = $this->security->xss_clean($this->input->post('ward_no'));
    $data['house_no'] = $this->security->xss_clean($this->input->post('house_no'));
    $data['street_name'] = $this->security->xss_clean($this->input->post('street_name'));
    $data['landmark'] = $this->security->xss_clean($this->input->post('landmark'));
    $data['phone_no'] = $this->security->xss_clean($this->input->post('phone_no'));
    $data['aadhar_no'] = $this->security->xss_clean($this->input->post('aadhar_no'));
    $data['meter_make_no'] = $this->security->xss_clean($this->input->post('meter_make'));
    $data['meter_no'] = $this->security->xss_clean($this->input->post('meter_no'));
    $data['approval_no'] = $this->security->xss_clean($this->input->post('kwa_approval_no'));
    $data['road_cutting_concrete'] = $this->security->xss_clean($this->input->post('road_cutting_concrete'));
    $data['additional_pipe'] = $this->security->xss_clean($this->input->post('additional_pipe'));
    $data['other_measurements'] = $this->security->xss_clean($this->input->post('other_measurements'));
    $data['customer_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');
    // $count = $this->Work->select_beneficiary_accounts("phone_no",array('phone_no' => $data['phone_no']))->num_rows();
    // if($count==0){
    $result2 =  $this->Work->create_beneficiary_account($data);
    $flash_data['status'] = $result2['status'];
    if ($result2['status'] == 1) {
      $flash_data['flashdata_msg'] = 'New Beneficiary Account 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.. Provided Phone No Already Exists!';
    //   $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_beneficiary_account()
  {
    $where_data['customer_id'] = $this->security->xss_clean($this->input->post('customer_id'));
    $data['subcontract_id'] = $this->security->xss_clean($this->input->post('subcontract'));
    $data['supervisor_id'] = $this->security->xss_clean($this->input->post('supervisor'));
    $data['customer_name'] = $this->security->xss_clean($this->input->post('beneficiary_name'));
    $data['guardian_name'] = $this->security->xss_clean($this->input->post('guardian_name'));
    $data['customer_address'] = $this->security->xss_clean($this->input->post('address'));
    $data['ward_no'] = $this->security->xss_clean($this->input->post('ward_no'));
    $data['house_no'] = $this->security->xss_clean($this->input->post('house_no'));
    $data['street_name'] = $this->security->xss_clean($this->input->post('street_name'));
    $data['landmark'] = $this->security->xss_clean($this->input->post('landmark'));
    $data['phone_no'] = $this->security->xss_clean($this->input->post('phone_no'));
    $data['aadhar_no'] = $this->security->xss_clean($this->input->post('aadhar_no'));
    $data['meter_make_no'] = $this->security->xss_clean($this->input->post('meter_make'));
    $data['meter_no'] = $this->security->xss_clean($this->input->post('meter_no'));
    $data['approval_no'] = $this->security->xss_clean($this->input->post('kwa_approval_no'));
    $data['road_cutting_concrete'] = $this->security->xss_clean($this->input->post('road_cutting_concrete'));
    $data['additional_pipe'] = $this->security->xss_clean($this->input->post('additional_pipe'));
    $data['other_measurements'] = $this->security->xss_clean($this->input->post('other_measurements'));
    $data['customer_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');
    $result2 =  $this->Work->update_beneficiary_accounts($where_data,$data);
    $flash_data['status'] = $result2;
    if ($result2 == 1) {
      $flash_data['flashdata_msg'] = 'Beneficiary Account Deleted Successfully!.';
      $flash_data['flashdata_type'] = 'success';
      $flash_data['alert_type'] = 'success';
      $flash_data['flashdata_title'] = 'Success !';
    } else {
      $flash_data['flashdata_msg'] = 'Sorry.. There Have been Some Error Occurred. Please Try Again!';
      $flash_data['flashdata_type'] = 'error';
      $flash_data['alert_type'] = 'danger';
      $flash_data['flashdata_title'] = 'Error !!';
    }
    $flash_data['message'] = $flash_data['flashdata_msg'];
    echo(json_encode($flash_data));
  }
  public function delete_beneficiary_account()
  {
    $where_data['customer_id'] = $this->security->xss_clean($this->input->post('customer_id'));
    $update_data['delete_status'] = 1;
    $result2 =  $this->Work->update_beneficiary_accounts($where_data,$update_data);
    $flash_data['status'] = $result2;
    if ($result2 == 1) {
      $flash_data['flashdata_msg'] = 'Beneficiary Account 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_beneficiary_accounts(){

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

    $data=array();
    if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
      $data['pba.project_id'] = $_POST['work'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('pba.project_id',explode(',',$_POST['work_ids']));
    }
    if(isset($_POST['subcontract']) && $_POST['subcontract']!='' && $_POST['subcontract']!='all'){
      $data['pba.subcontract_id'] = $_POST['subcontract'];
    }
    if(isset($_POST['supervisor']) && $_POST['supervisor']!='' && $_POST['supervisor']!='all'){
      $data['pba.supervisor_id'] = $_POST['supervisor'];
    }
    if(isset($_POST['wardno']) && $_POST['wardno']!='' && $_POST['wardno']!='all'){
      $wardno = $_POST['wardno'];
      $this->db->where('pba.ward_no like "%'.$wardno.'%"');
    }
    $result = $this->Work->select_beneficiary_accounts("pba.*,mm.meter_name,u1.user_alias as supervisor_name,u2.user_alias as created_user,wc.work_name,wc.work_id,c.contractor_id,c.contractor_name,c.phone as contractor_phone",$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 = 1;
    $total_amount = 0;
    foreach($result_array as $row):
      $btn_edit=$btn_delete='';
      if($this->session->userdata('user_role')!='auditor'){
        $btn_edit='<a style="margin-left: 5px;margin-right: 5px" data-id="'.$row->customer_id.'" id="beneficiary_edit_btn" href="#beneficiary_edit_modal" data-toggle="modal" class="btn btn-info m-btn m-btn--icon  m-btn--icon-only m-btn--pill m-btn--air">
        <i class="la la-edit"></i>
        </a>';
        $btn_delete='<a style="margin-left: 5px;margin-right: 5px" data-id="'.$row->customer_id.'" id="beneficiary_delete_btn" href="#beneficiary_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][]='<a href="'.base_url().'work/beneficiary_accounts/profile/'.$row->customer_id.'">#'.$row->customer_id.'</a>';
      $array[$j][]='<a href="'.base_url().'work/profile/'.$row->work_id.'">#'.$row->work_id." - ".$row->work_name.'</a>';
      $array[$j][]=$row->customer_name;
      $array[$j][]=$row->guardian_name;
      $array[$j][]=$row->customer_address;
      $array[$j][]=$row->ward_no;
      $array[$j][]=$row->house_no;
      $array[$j][]=$row->street_name;
      $array[$j][]=$row->landmark;
      $array[$j][]=$row->phone_no;
      $array[$j][]=$row->aadhar_no;
      $array[$j][]=$row->meter_name;
      $array[$j][]=$row->meter_no;
      $array[$j][]=$row->approval_no;
      $array[$j][]="#".$row->contractor_id."-".$row->contractor_name."<br/>".$row->contractor_phone;
      $array[$j][]=$row->supervisor_name;
      $array[$j][]=$row->road_cutting_concrete;
      $array[$j][]=$row->additional_pipe;
      $array[$j][]=$row->other_measurements;
      $array[$j][]=$row->customer_remarks;
      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->created_user."</li>";
      }else{
        $array[$j][]='<ul style="padding: .75rem;margin-bottom: 0"><li>Created By : '.$row->created_user.'</li>';
      }
      $array[$j][]=$btn_edit.$btn_delete;
      $array[$j][]=$row->work_id;
      $array[$j][]=$row->meter_make_no;
      $array[$j][]=$row->subcontract_id;
      $array[$j][]=$row->supervisor_id;


      $j++;
    endforeach;

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

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

    $data=array();
    if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
      $data['work_id'] = $_POST['work'];
    }
    if(isset($_POST['work_ids']) && $_POST['work_ids']!='' && $_POST['work_ids']!='all'){
      $this->db->where_in('work_id',explode(',',$_POST['work_ids']));
    }
    $result = $this->Work->select_work("w.*",$data);

    $array=$subcontract_payment_where=array();
    $sl_no = 1;
    $total_credit_amount = $total_debit_amount = 0;
    if($result->num_rows()>0){
      foreach ($result->result() as $key11 => $value11) {
        $work_id = $value11->work_id;
        $start_date = $end_date = '';
        $project_id =  $vehicle_work_slip_where['vws.vehicle_work_id'] = $stock_transfer_where['sl2.work_id'] =  $expense_bills_where['b.work_id'] = $purchase_bill_where['sl.work_id'] = $value11->work_id;
        if(isset($_POST['start_date']) && $_POST['start_date']!=''){
          $start_date = $vehicle_work_slip_where['vws.worked_date >='] = $stock_transfer_where['st.transfer_date >='] = $labour_wage_where['lw.work_date >='] = $subcontract_payment_where['ats.tr_date >='] = $expense_bills_where['b.bill_date >='] = $purchase_bill_where['p.purchase_date >='] = $_POST['start_date'];
        }
        if(isset($_POST['end_date']) && $_POST['end_date']!=''){
          $end_date = $vehicle_work_slip_where['vws.worked_date <='] = $stock_transfer_where['st.transfer_date <='] = $labour_wage_where['lw.work_date <='] = $subcontract_payment_where['ats.tr_date <='] = $expense_bills_where['b.bill_date <='] = $purchase_bill_where['p.purchase_date <='] = $_POST['end_date'];
        }
        $project_summary_data = $this->projects_management->get_project_summary($project_id,$start_date,$end_date);

        // Client Payment
        $array[$j][]=$sl_no++;
        $array[$j][]='Client Payment Received';
        $array[$j][]='';
        // $array[$j][]='';
        $array[$j][]='';
        $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($project_summary_data['total_client_payments_received'], $this->config->item('Currency_currency_shortname'));
        $array[$j][]='';
        $total_credit_amount += $project_summary_data['total_client_payments_received'];
        $j++;

        // Expense Bills
        $expense_bills_where['b.bill_type'] = 'expense';
        $expense_bill_head_wise	= $this->Bills->select_bills("ah1.acc_head_name as expense_head_name,COALESCE(SUM(b.bill_tax_amount),0) as sum_tax_amount,COALESCE(SUM(b.bill_payable_amount),0) as sum_payable_amount,COALESCE(SUM(b.bill_total_paid),0) as sum_total_paid,COALESCE(SUM(b.bill_balance_amount),0) as sum_balance_amount",$expense_bills_where,'','','b.bill_credit_ac');
        foreach($expense_bill_head_wise->result() as $row):

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->expense_head_name;
          $array[$j][]='';
          // $array[$j][]='';
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->sum_payable_amount, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Expense Bill';
          $total_debit_amount+=$row->sum_payable_amount;

          $j++;
        endforeach;

        //Material Purchase
        $purchased_materials = $this->Purchases->select_purchase_items("pi.*,COALESCE(SUM(pi.quantity),0) as total_quantity,COALESCE(SUM(pi.total_amount),0) as total_total_amount,mc.cat_name,m.material_name,mu.unit_name,",$purchase_bill_where,'',array("m.category"));
        foreach($purchased_materials->result() as $row):

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->cat_name;
          $array[$j][]=number_format($row->total_quantity,$this->config->item('Currency_amount_decimals_2'),'.','');
          // $array[$j][]=number_format($row->unit_price,$this->config->item('Currency_amount_decimals_2'),'.','');
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_total_amount, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Material Purchase';
          $total_debit_amount+=$row->total_total_amount;

          $j++;
        endforeach;

        //Stock Transfer In
        $stock_transfer_items = $this->Stock->select_stock_transfers("mc.cat_name,COALESCE(SUM(st.transferred_qty),0) as total_transferred_qty,COALESCE(SUM(st.transferred_qty*sr2.unit_price),0) as total_transferred_amt,st.*,sr1.unit_price,sr1.material_id,m.material_name,mu.unit_name,sr1.unit,sl1.location_name as transferred_from_loc_name,sl2.location_name as transferred_to_loc_name,u.user_alias as created_user,u.user_role as created_user_role_id,sl1.location_type as location_type_from,sl2.location_type as location_type_to,sl1.work_id as location_work_id_from,sl2.work_id as location_work_id_to ", $stock_transfer_where, array("m.category"));
        foreach($stock_transfer_items->result() as $row):

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->cat_name;
          $array[$j][]=number_format($row->total_transferred_qty,$this->config->item('Currency_amount_decimals_2'),'.','');
          // $array[$j][]=number_format($row->unit_price,$this->config->item('Currency_amount_decimals_2'),'.','');
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_transferred_amt, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Stock transfer In';
          $total_debit_amount+=$row->total_transferred_amt;

          $j++;
        endforeach;


        // Subcontract Payment
        $subcontracts = $this->Subcontracts->select_subcontract("sc.*,c.*,u.user_alias,wc.work_name,wc.work_location,wc.reference_no",array("sc.work_id"=>$work_id))->result();
        foreach($subcontracts as $row):
          $bill_paid_amount = $this->Bills->select_bills("COALESCE(SUM(bill_total_paid),0) as total_amount", array("b.subcontract_id" => $row->contract_id, "b.bill_type" => 'subcontract'))->row()->total_amount;
          $advance_paid_amount = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_paid_amount", array("ats.reference_id" => $row->contract_id, "ats.reference_type" => 'subcontract'), 'subcontract_advance')->row()->total_paid_amount;
          $total_paid = $bill_paid_amount+$advance_paid_amount;

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->sub_work_name." [".$row->contractor_name."]";
          $array[$j][]='';
          // $array[$j][]='';
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_paid, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Subcontract Payment';
          $total_debit_amount+=$total_paid;

          $j++;

        endforeach;
        // Vehicle Payment
        $vehicle_work_slip = $this->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(vws.total_payable_amount),0) as total_amount,v.vehicle_name,v.vehicle_no,vc.consignee_name",$vehicle_work_slip_where,'','','vws.vehicle_id')->result();
        foreach($vehicle_work_slip as $row):

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->vehicle_name."(".$row->vehicle_no.") [".$row->consignee_name."]";
          $array[$j][]='';
          // $array[$j][]='';
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_amount, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Vehicle Work Slip';
          $total_debit_amount+=$row->total_amount;

          $j++;
        endforeach;

        //Tool Payment
        $tool_payment_where['ats.tr_type'] = 'tool_payment_transaction';
        $this->db->where("((ats.reference_type='tool_rent' and tr.rented_to='".$work_id."'))");
        $this->db->where("(ats.reference_type='tool_rent')");
        $tool_rent_payments	= $this->Account->select_account_transaction("t.name,COALESCE(SUM(ats.tr_amount),0) as total_amount",$tool_payment_where,'tool','tr.tool_id')->result();
        foreach($tool_rent_payments as $row):
          if($row->total_amount>0){
            $array[$j][]=$sl_no++;
            $array[$j][]=$row->name;
            $array[$j][]='';
            // $array[$j][]='';
            $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_amount, $this->config->item('Currency_currency_shortname'));
            $array[$j][]='';
            $array[$j][]='Tool Rental Payment';
            $total_debit_amount+=$row->total_amount;

            $j++;
          }
        endforeach;

        $this->db->where("((ats.reference_type='tool_transfer' and tt.transfer_to='".$work_id."'))");
        $this->db->where("(ats.reference_type='tool_transfer')");
        $tool_transfer_payments	= $this->Account->select_account_transaction("t2.name,COALESCE(SUM(ats.tr_amount),0) as total_amount",$tool_payment_where,'tool','tt.tool_id')->result();
        foreach($tool_transfer_payments as $row):
          if($row->total_amount>0){
            $array[$j][]=$sl_no++;
            $array[$j][]=$row->name;
            $array[$j][]='';
            // $array[$j][]='';
            $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_amount, $this->config->item('Currency_currency_shortname'));
            $array[$j][]='';
            $array[$j][]='Tool Transfer Payment';
            $total_debit_amount+=$row->total_amount;

            $j++;
          }
        endforeach;

        // Labour Wage
        $labour_wage_where['lw.work_id'] = $work_id;
        $labour_wages = $this->Labour_wages->select_labour_wages("wt.type_name,COALESCE(SUM(lw.total_wage),0) as total",$labour_wage_where,'','','l.labour_type')->result();
        foreach($labour_wages as $row):

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->type_name;
          $array[$j][]='';
          // $array[$j][]='';
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Labour Wage';
          $total_debit_amount+=$row->total;

          $j++;
        endforeach;

        // Salary SLips
        $salary_slips_where['ss.work_id'] = $work_id;
        $salary_slips = $this->Salary_slips->select_salary_slips("ss.*,e.employee_name,COALESCE(SUM(ss.net_salary_amount),0) as total",$salary_slips_where,'','','ss.employee_id')->result();
        foreach ($salary_slips as $key => $row) {

          $array[$j][]=$sl_no++;
          $array[$j][]=$row->employee_name;
          $array[$j][]='';
          // $array[$j][]='';
          $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total, $this->config->item('Currency_currency_shortname'));
          $array[$j][]='';
          $array[$j][]='Employee Salary';
          $total_debit_amount+=$row->total;

          $j++;
        }

        // Purchase Discount
        $array[$j][]=$sl_no++;
        $array[$j][]='Purchase Discount/Round (-)';
        $array[$j][]='';
        // $array[$j][]='';
        $array[$j][]=$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($project_summary_data['total_purchase_bill_discount_amount'], $this->config->item('Currency_currency_shortname'));
        $array[$j][]='';
        $array[$j][]='Material Purchase';
        $total_debit_amount -= $project_summary_data['total_purchase_bill_discount_amount'];
        $j++;


      }


      $array[$j][]='';
      $array[$j][]='<strong>Total</strong>';
      // $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='<strong>'.$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_debit_amount, $this->config->item('Currency_currency_shortname')).'</strong>';
      $array[$j][]='<strong>'.$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_credit_amount, $this->config->item('Currency_currency_shortname')).'</strong>';
      $array[$j][]='';
      $j++;

      $array[$j][]='';
      $array[$j][]='<strong>Profit/Loss</strong>';
      // $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='<strong>'.$this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_credit_amount-$total_debit_amount, $this->config->item('Currency_currency_shortname')).'</strong>';
      $array[$j][]='';
    }

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