<?php if (!defined('BASEPATH')) exit('No direct script access allowed');
class Reports extends CI_Controller
{
  public $page_data = array();

  function __construct()
  {
    parent::__construct();
    $this->initial();
  }

  public function initial()
  {
    ini_set('max_execution_time', 5000);
    ini_set("memory_limit", "-1");

    $this->load->model('User_model', 'User');
    $this->load->model('Work_model', 'Work');
    $this->load->model('Client_model', 'Client');
    $this->load->model('Supplier_model', 'Supplier');
    $this->load->model('Purchase_bill_model', 'Purchases');
    $this->load->model('Purchase_order_model', 'Purchase_order');
    $this->load->model('Material_model', 'Material');
    $this->load->model('Subcontracts_model', 'Subcontracts');
    $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('Invoices_model', 'Invoices');
    $this->load->model('Bills_model', 'Bills');
    $this->load->library('Accounts_management', 'accounts_management');
    $this->load->model('Salary_slips_model', 'Salary_slips');
    $this->load->model('Vehicle_model', 'Vehicle');
    $this->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $this->load->model('Loan_model', 'Loan');
    $this->load->model('Stock_model', 'Stock');
    $this->load->model('project_tasks_model', 'project_tasks');
    $this->load->model('Boq_model', 'Boq');
    $this->load->library('Supplier_management', 'supplier_management');
    $this->load->library('Projects_management','projects_management');

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

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

  public function activity()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_activity';
    $this->load->view('Index', $this->page_data);
  }
  public function profit()
  {
    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['project_categories'] = $this->Work->select_work_category()->result();
    $this->page_data['project_sections'] = $this->Work->select_project_sections()->result();
    $this->page_data['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_income_expense_profit';
    $this->load->view('Index', $this->page_data);
  }

  public function transactions()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_transaction';
    $this->load->view('Index', $this->page_data);
  }
  public function labour_attendance($type = '')
  {
    $this->page_data['start_date'] = date('Y-m-d', strtotime('-6 days'));
    $this->page_data['end_date'] = date('Y-m-d');
    if (isset($_POST['start_date']) && $_POST['start_date'] != '' && $_POST['start_date'] != 'all') {
      $this->page_data['start_date'] = date('Y-m-d', strtotime($_POST['start_date']));
    }
    if (isset($_POST['end_date']) && $_POST['end_date'] != '' && $_POST['end_date'] != 'all') {
      $this->page_data['end_date'] = date('Y-m-d', strtotime($_POST['end_date']));
    }
    if (isset($_GET['start_date']) && $_GET['start_date'] != '' && $_GET['start_date'] != 'all') {
      $this->page_data['start_date'] = date('Y-m-d', strtotime($_GET['start_date']));
    }
    if (isset($_GET['end_date']) && $_GET['end_date'] != '' && $_GET['end_date'] != 'all') {
      $this->page_data['end_date'] = date('Y-m-d', strtotime($_GET['end_date']));
    }
    if ($this->page_data['start_date'] != '' && $this->page_data['end_date'] != '') {

      $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
      foreach ($this->page_data['work_contracts'] as $key => $value) {
        // Get Assigned labours*
        $this->db->where("FIND_IN_SET($value->work_id, l.assigned_projects)");
        $value->assigned_labours  = $this->Labour->select_labours("l.*,wt.type_name,u.user_alias", array(), array("labour_group", "desc"), array("group_position", "desc"), "", "", "")->result();
        foreach ($value->assigned_labours as $key2 => $value2) {
          $labour_wage_data['lw.work_id'] = $value->work_id;
          $labour_wage_data['lw.labour_id'] = $value2->labour_id;
          $labour_wage_data['lw.work_date <='] = $this->page_data['end_date'];
          $labour_wage_data['lw.work_date >='] = $this->page_data['start_date'];
          $value2->worked_days  = $this->Labour_wages->select_labour_wages("lw.work_date", $labour_wage_data, "", "", "", "", "")->result();
        }

        $this->db->where("NOT FIND_IN_SET($value->work_id, l.assigned_projects)");
        $labour_wage_data_2['lw.work_id'] = $value->work_id;
        $labour_wage_data_2['lw.work_date <='] = $this->page_data['end_date'];
        $labour_wage_data_2['lw.work_date >='] = $this->page_data['start_date'];
        $value->not_assigned_labours  = $this->Labour_wages->select_labour_wages("distinct(l.labour_id),labour_name", $labour_wage_data_2, "", "", "", "", "")->result();
        foreach ($value->not_assigned_labours as $key22 => $value22) {
          $labour_wage_data_22['lw.work_id'] = $value->work_id;
          $labour_wage_data_22['lw.labour_id'] = $value22->labour_id;
          $labour_wage_data_22['lw.work_date <='] = $this->page_data['end_date'];
          $labour_wage_data_22['lw.work_date >='] = $this->page_data['start_date'];
          $value22->worked_days  = $this->Labour_wages->select_labour_wages("lw.work_date", $labour_wage_data_22, "", "", "", "", "")->result();
        }

        // Get Subcontract Contractors
        $value->subcontracts  = $this->Subcontracts->select_subcontract("distinct(sc.contractor),sc.sub_work_name,c.contractor_name", array("sc.work_id" => $value->work_id))->result();
        foreach ($value->subcontracts as $key33 => $value33) {
          // Get Labours
          $value33->labours = $this->Subcontracts->select_labours("sl.*", array("sl.contractor_id" => $value33->contractor))->result();
          foreach ($value33->labours as $key44 => $value44) {
            $attendance_data['sc.work_id'] = $value->work_id;
            $attendance_data['sla.labour_id'] = $value44->labour_id;
            $attendance_data['sla.date <='] = $this->page_data['end_date'];
            $attendance_data['sla.date >='] = $this->page_data['start_date'];
            $value44->worked_days  = $this->Subcontracts->select_subcontract_labour_attendance("sla.date", $attendance_data, "", "", "", "", "")->result();
          }
        }
      }
    }

    if ($type == 'print') {
      $this->page_data['print_page_type'] = 'landscape';
      $this->page_data['print_content_page'] = 'modules/include_tables/table_report_labour_attendance';
      $this->load->view('modules/print/print_page', $this->page_data);
    } else {
      $this->page_data['page_name'] = 'reports_labour_attendance';
      $this->load->view('Index', $this->page_data);
    }
  }
  public function daybook()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_daybook';
    $this->load->view('Index', $this->page_data);
  }
  public function daily_transactions()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_daily_transactions';
    $this->load->view('Index', $this->page_data);
  }
  public function project_daybook()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_project_daybook';
    $this->load->view('Index', $this->page_data);
  }
  public function purchase()
  {
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();
    $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['material_list'] = $this->Material->select_material()->result();

    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_purchase';
    $this->load->view('Index', $this->page_data);
  }
  public function tax()
  {
    $this->page_data['stock_locations'] = $this->Stock->select_stock_locations()->result();
    $this->page_data['suppliers_list'] = $this->Supplier->select_supplier('*')->result();
    $this->page_data['material_list'] = $this->Material->select_material()->result();
    $this->page_data['tax_types'] = $this->Settings->select_tax_types('', '', 'all')->result();

    if ($this->session->userdata('user_role') == 'supervisor') {
      //          Select Assigned Works Only
      $supervisor_id = $this->session->userdata('user_id');
      $this->db->where("FIND_IN_SET($supervisor_id, w.assigned_users)");
    }
    $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
    $this->page_data['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_tax';
    $this->load->view('Index', $this->page_data);
  }
  public function trial_balance()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_trial_balance';
    $this->load->view('Index', $this->page_data);
  }
  public function trial_balance_old()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_trial_balance_old';
    $this->load->view('Index', $this->page_data);
  }
  public function project_tasks()
  {
    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['users'] = $this->User->select_user()->result();
    $this->page_data['page_name'] = 'reports_project_tasks';
    $this->load->view('Index', $this->page_data);
  }

  public function report_print($start_date = "", $end_date = "", $work = "")
  {
    $this->page_data['page_name'] = 'report_print';
    $start_date = explode('-', $start_date);
    $end_date = explode('-', $end_date);
    $start_date = $start_date[1] . "-" . date('m', strtotime($start_date[0])) . "-" . $start_date[2];
    $end_date = $end_date[1] . "-" . date('m', strtotime($end_date[0])) . "-" . $end_date[2];
    $this->page_data['date'] = date('F d, Y', strtotime($start_date)) . " - " . date('F d, Y', strtotime($end_date));
    $this->page_data['work'] = $work;
    $this->load->view('Index', $this->page_data);
  }

  public function boq_purchase($project_id = "")
  {
    if ($this->config->item('boq_purchase_integration') == 1) {
      $this->page_data['page_name'] = 'reports_boq_purchase';
      $this->page_data['work_contracts'] = $this->Work->select_work("*")->result();
      $this->page_data['project_id'] = $project_id;
      $this->load->view('Index', $this->page_data);
    } else {
      show_404();
    }
  }

  public function boq_purchase_integration()
  {
    $project_id =  $this->input->post('project_id');
    if ($project_id != '' && $this->config->item('boq_purchase_integration') == 1) {
      $array = $json_data = array();

      $data['b.project_id'] = $project_id;
      $data['b.boq_approval_status'] = 1;
      $column = 'b.boq_id,b.boq_name,b.total_est_amount,bti.material_id,COALESCE(SUM(bti.total_amount),0) as bti_total_amount,bti.unit_rate,COALESCE(SUM(bti.qnty),0) as bti_qnty,bti.unit,mu.unit_name,m.material_name';

      $result = $this->Boq->select_task_items($column, $data, '', '', '', 'm.material_id');
      $result_array = $result->result();

      $json_data['count'] = sizeof($result_array);

      $column2 = "COALESCE(SUM(pi.quantity),0) as pi_quantity,COALESCE(SUM(pi.unit_price),0) as pi_unit_price,COALESCE(SUM(pi.total_amount),0) as pi_total_amount";

      $data3['p.location_id'] = $this->Stock->select_stock_locations("sl.location_id", array("sl.work_id" => $project_id))->row()->location_id;

      $j = $boq_qnty = $pi_amount = $pi_balance = 0;
      $table_body = '';
      foreach ($result_array as $key => $value) {

        $data3['pi.material_id'] = $value->material_id;
        $data3['pi.unit_id'] = $value->unit;

        $purchase_data = $this->Purchases->select_purchase_items($column2, $data3, '', '', '', 'pi.material_id')->row();

        $consumed_qty = 0;
        $consumption_where['sr.location_id'] = $data3['p.location_id'];
        $consumption_where['sr.material_id'] = $value->material_id;
        $consumption_where['sr.unit'] = $value->unit;
        $consumption  = $this->Stock->select_stock_consumption("COALESCE(SUM(sc.consumption_qty),0) as total_consumed_qty", $consumption_where, array("sc.cons_group_id", "sr.material_id", "sr.unit"));
        if ($consumption->num_rows() == 1) {
          $consumed_qty = $consumption->row()->total_consumed_qty;
        }
        $table_body .= '<tr><td>' . ($j + 1) . '</td>';
        $table_body .= '<td>' . $value->material_name . ' [' . $value->unit_name . ']' . '</td>';
        // $table_body .= '<td>'.$value->unit.'</td>';
        $table_body .= '<td>' . $value->bti_qnty . '</td>';
        $table_body .= '<td>' . $purchase_data->pi_quantity . '</td>';
        $table_body .= '<td>' . $consumed_qty . '</td>';
        $table_body .= '<td>' . ($value->bti_qnty - $purchase_data->pi_quantity) . '</td>';
        $table_body .= '<td>' . ($purchase_data->pi_quantity - $consumed_qty) . '</td>';

        // $table_body .= '<td>'.$unit_rate.'</td>';
        $balance = $value->bti_total_amount - $purchase_data->pi_total_amount;
        $table_body .= '<td>' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value->bti_total_amount, $this->config->item('Currency_currency_shortname')) . '</td>';
        $table_body .= '<td>' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($purchase_data->pi_total_amount, $this->config->item('Currency_currency_shortname')) . '</td>';
        $table_body .= '<td>' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($balance, $this->config->item('Currency_currency_shortname')) . '</td>';

        $boq_qnty += $value->bti_total_amount;
        $pi_amount += $purchase_data->pi_total_amount;
        $pi_balance += $balance;

        $j++;
      }

      $table_body .= '<tr><td></td><td></td><td></td><td></td><td></td><td></td><td><b>Total</b></td>';
      $table_body .= '<td><b>' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($boq_qnty, $this->config->item('Currency_currency_shortname')) . '</b></td>';
      $table_body .= '<td><b>' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($pi_amount, $this->config->item('Currency_currency_shortname')) . '</b></td>';
      $table_body .= '<td><b>' . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($pi_balance, $this->config->item('Currency_currency_shortname')) . '</td></tr>';



      $json_data['table_body'] = $table_body;
      if ($table_body == '') {
        $json_data['count'] = 0;
      }
    } else {
      $json_data['count'] = 0;
    }

    echo json_encode($json_data);  // send data as json format
  }
  public function select_report_summary($param = "")
  {

    $json_data = array();
    $vehicle_payment_where = $salary_payment_where = $labour_wage_where_data = $tool_purchase_where = $user_payment_released_where = $user_payment_received_where = $client_payment_where = $purchase_payment_where = $purchase_order_payment_where = $subcontract_payment_where = $other_expense_where = $office_expense_where = $labour_wage_payment_where = $salary_payment_where = $purchase_bill_where = array();
    $json_data['total_commission'] = $commission_percent = $total_purchase_payments = $total_sub_payments = $total_other_expense = $total_commission = $total_expenses = 0;
    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));

      $user_payment_released_where["ats.tr_date <="] = $end_date;
      $user_payment_released_where["ats.tr_date >="] = $start_date;

      $tool_purchase_where["ats.tr_date <="] = $end_date;
      $tool_purchase_where["ats.tr_date >="] = $start_date;

      $user_payment_received_where["ats.tr_date <="] = $end_date;
      $user_payment_received_where["ats.tr_date >="] = $start_date;

      $client_payment_where["ats.tr_date <="] = $end_date;
      $client_payment_where["ats.tr_date >="] = $start_date;

      $purchase_payment_where["ats.tr_date <="] = $end_date;
      $purchase_payment_where["ats.tr_date >="] = $start_date;

      $purchase_order_payment_where["ats.tr_date <="] = $end_date;
      $purchase_order_payment_where["ats.tr_date >="] = $start_date;

      $subcontract_payment_where["ats.tr_date <="] = $end_date;
      $subcontract_payment_where["ats.tr_date >="] = $start_date;

      $other_expense_where["ats.tr_date <="] = $end_date;
      $other_expense_where["ats.tr_date >="] = $start_date;

      $office_expense_where["ats.tr_date <="] = $end_date;
      $office_expense_where["ats.tr_date >="] = $start_date;

      $labour_wage_payment_where["ats.tr_date <="] = $end_date;
      $labour_wage_payment_where["ats.tr_date >="] = $start_date;

      $labour_wage_where_data["lw.work_date <="] = $end_date;
      $labour_wage_where_data["lw.work_date >="] = $start_date;

      $salary_payment_where["ats.tr_date <="] = $end_date;
      $salary_payment_where["ats.tr_date >="] = $start_date;

      $vehicle_payment_where["ats.tr_date <="] = $end_date;
      $vehicle_payment_where["ats.tr_date >="] = $start_date;
    }
    if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
      $client_payment_where["wc.work_id"] = $_POST['work'];
      $purchase_payment_where["sl.work_id"] = $_POST['work'];
      $purchase_order_payment_where["sl.work_id"] = $_POST['work'];
      $purchase_bill_where["sl.work_id"] = $_POST['work'];
      $other_expense_where["wc.work_id"] = $_POST['work'];
      $salary_payment_where["ss.work_id"] = $_POST['work'];
      $salary_payment_where["ss.slip_type"] = "Project";
      $labour_wage_where_data['lw.work_id'] = $_POST['work'];
      $salary_payment_where['ss.work_id'] =  $_POST['work'];
      $salary_payment_where['ss.slip_type'] = 'Project';
      $vehicle_payment_where['wc.work_id'] = $_POST['work'];;
      // $labour_wage_payment_where["lw.work_id"]= $_POST['work'];
      $labour_wage_where_data["lw.work_id"] = $_POST['work'];
      $data['w.work_id'] = $_POST['work'];
    }
    if (isset($_POST['user']) && $_POST['user'] != '' && $_POST['user'] != 'all') {
      $tool_purchase_where['ats.created_by'] = $labour_wage_where_data["lw.created_by"] = $labour_wage_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $office_expense_where['ats.created_by'] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_where['p.created_by'] = $subcontract_payment_where['ats.created_by']  = $salary_payment_where['ats.created_by'] = $user_payment_released_where['ats.created_by'] = $user_payment_received_where['u3.user_id'] =  $_POST['user'];
    }
    if ($this->session->userdata('user_role') != 'admin') {

      $tool_purchase_where['ats.created_by'] = $labour_wage_where_data["lw.created_by"] = $labour_wage_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $office_expense_where['ats.created_by'] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_where['p.created_by'] = $subcontract_payment_where['ats.created_by'] = $salary_payment_where['ats.created_by'] = $user_payment_released_where['ats.created_by'] = $user_payment_received_where['u3.user_id'] = $this->session->userdata('user_id');
    }

    //      Select Client Payment
    $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
    $client_payment_where['ats.reference_type'] = 'project';

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

    //      Select Purchase Order Payment
    $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'));

    //      Select Purchase Payment
    $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'));

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

    //        Select Purchase Bill
    $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'));

    //      Select Subcontract Payment
    if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
      $work_id = $_POST['work'];
      $this->db->where("((ats.reference_type='subcontract_bill' and b.work_id=$work_id) or (ats.reference_type='subcontract' and sc2.work_id=$work_id))");
    } else {
      $this->db->where("(ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction')");
      $this->db->where("(ats.reference_type='subcontract_bill' or ats.reference_type='subcontract')");
    }
    $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'));


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


    //      Select Work Expense Payment
    $other_expense_where['b.work_id !='] = 0;
    $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'));

    if (!isset($_POST['work']) || $_POST['work'] == 'all') {
      //      Select Office Expense Payment
      $office_expense_where['b.work_id'] = 0;
      $office_expense_where['ats.tr_type'] = 'expense_bill_transaction';
      $office_expense_where['ats.reference_type'] = 'expense_bill';
      $json_data['total_office_expense']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $office_expense_where, 'expense_bill')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

      //      Select Labour Wage Payment
      $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
      $labour_wage_payment_where['ats.reference_type'] = 'labour';
      $json_data['total_labour_wage_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $labour_wage_payment_where, 'labour')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

      $user_payment_released_where['ats.tr_type'] = 'user_payment_receipt_transaction';
      $user_payment_released_where['ats.reference_type'] = 'user_account_head';
      $json_data['total_user_payments_released'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_released_where, 'user_payment_receipt')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

      $user_payment_received_where['ats.tr_type'] = 'user_payment_receipt_transaction';
      $user_payment_received_where['ats.reference_type'] = 'user_account_head';
      $json_data['total_user_payments_received'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_received_where, 'user_payment_receipt')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
    } else {
      $json_data['total_user_payments_received'] = $json_data['total_user_payments_released'] = $json_data['total_office_expense']  = $json_data['total_labour_wage_payments']  = 0;
    }
    $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'));

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

    $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
    $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
    $json_data['total_purchase_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_purchase_where, 'tool_purchase')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    //      Total and profit
    $json_data['total_expense_amount'] = $json_data['total_vehicle_payments'] + $json_data['total_subcontract_payment'] + $json_data['total_purchase_payment'] + $json_data['total_other_expense'] + $json_data['total_office_expense'] + $json_data['total_labour_wage_payments'] + $json_data['total_salary_payments'] + $json_data['total_purchase_payments'];
    $json_data['total_cash_balance'] = $json_data['total_client_payments_received'] - $json_data['total_expense_amount'];
    $json_data['total_amount_to_receive'] =  $json_data['total_expense_amount'] + $json_data['total_commission'];
    $json_data['total_income'] =  $json_data['total_client_payments_received'];
    $json_data['total_balance'] = sprintf("%+d", $json_data['total_income'] - $json_data['total_amount_to_receive']);
    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_balance'] = sprintf("%+d", $json_data['total_balance']);
    $json_data['status'] = 1;
    echo json_encode($json_data);  // send data as json format
  }

  public function select_daybook($type = '')
  {

    $json_data = array();
    $table_body = "";
    $j = $i = 0;
    $data = array();
    $total_received = $total_payment = $op_balance = $total_balance = 0;

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));
      // of period 1 day
      $interval = new DateInterval('P1D');
      $realEnd = new DateTime($end_date);
      $realEnd->add($interval);
      $date_period = new DatePeriod(new DateTime($start_date), $interval, $realEnd);

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

      $subcontract_bills_where = $vehicle_expense_transaction_where = $expense_bills_where = $vehicle_expense_bills_where = $vehicle_work_slips_where = $salary_slips_where = $labour_wage_slips_where = $client_invoice_where = $purchase_bill_where = $supplier_opbalance_payment_where = $supplier_advance_payment_where = $user_commission_payment_where = $tool_payment_where=$tool_purchase_where=$loan_receipt_where = $loan_payment_where = $user_receipt_where = $user_return_where = $salary_payment_where = $other_income_where = $user_payment_receipt_where = $user_payment_return_where = $client_payment_where = $purchase_payment_where = $purchase_order_payment_where = $subcontract_payment_where = $other_expense_where = $office_expense_where = $employee_payment_where = $labour_wage_payment_where = $purchase_bill_payment_where = array();
      if($this->session->userdata('user_role')!='admin' && $this->session->userdata('user_role')!='madmin' && $this->session->userdata('user_role')!='accounts_manager' && $this->session->userdata('user_role')!='general_manager'){
        $subcontract_bills_where['b.created_by'] = $vehicle_expense_transaction_where['ats.created_by'] = $expense_bills_where['b.created_by'] = $vehicle_expense_bills_where['b.created_by'] = $vehicle_work_slips_where['vws.created_by'] = $salary_slips_where['ss.created_by'] = $labour_wage_slips_where['lw.created_by'] = $purchase_bill_where['p.created_by'] = $client_invoice_where['i.created_by'] = $sales_invoice_receipt_where['ats.created_by'] = $employee_payment_where['ats.created_by'] = $supplier_advance_payment_where['ats.created_by'] = $user_commission_payment_where['ats.created_by'] = $tool_payment_where['ats.created_by'] = $tool_purchase_where['ats.created_by'] = $loan_payment_where['ats.created_by'] = $other_income_where['ats.created_by'] = $salary_payment_where['ats.created_by'] =$vehicle_payment_where['ats.created_by'] = $labour_wage_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $office_expense_where['ats.created_by'] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_payment_where['ats.created_by'] = $subcontract_payment_where['ats.created_by'] = $user_payment_receipt_where['ats.created_by'] = $user_payment_return_where['ats.created_by'] = $this->session->userdata('user_id');
      }
      $work_id = '';
      if(isset($_POST['work']) && $_POST['work']!='' && $_POST['work']!='all'){
        $subcontract_bills_where['b.work_id'] = $expense_bills_where['b.work_id'] = $vehicle_expense_bills_where['b.work_id'] = $vehicle_work_slips_where['vws.vehicle_work_id'] = $salary_slips_where['ss.work_id'] = $labour_wage_slips_where['lw.work_id'] = $purchase_bill_where['sl.work_id'] = $client_invoice_where['i.project_id'] = $work_id = $user_commission_payment_where['ats.tr_work_id'] = $loan_receipt_where['wc.work_id'] = $loan_payment_where['wc.work_id'] = $salary_payment_where['wc.work_id'] =$vehicle_payment_where['wc.work_id'] = $client_payment_where['wc.work_id'] = $other_expense_where['wc.work_id']  = $purchase_order_payment_where["wc.work_id"] = $purchase_payment_where['wc.work_id'] = $purchase_bill_payment_where['wc.work_id']  = $_POST['work'];
      }

      // Calculate Day OP Balance
      $day_op_balance = 0;
      if ($type != 'daily_transactions') {
        if ($this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
          $day_op_balance = $this->accounts_management->calculate_trial_balance('', date('Y-m-d', strtotime('-1 day', strtotime($start_date))), 'company_accounts', $work_id,$this->session->userdata('user_role'),$this->session->userdata('user_id'));
        } else {
          $day_op_balance = $this->accounts_management->calculate_trial_balance('', date('Y-m-d', strtotime('-1 day', strtotime($start_date))), 'user_accounts', $work_id,$this->session->userdata('user_role'),$this->session->userdata('user_id'));
        }
      }

      $total_balance += $day_op_balance;
      foreach ($date_period as $key => $value) {
        $transactions = array();
        $i=0;
        $day_total_payable = $day_total_receivable = $day_total_received = $day_total_payment = $day_balance = 0;

        $table_body.="<tr><td></td><td colspan='8'><strong>".date('d F Y',strtotime($value->format('Y-m-d')))."</strong></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td></tr>";
        if($type!='daily_transactions'){
          if($key==0){
            $table_body.="<tr><td></td><td>Opening Balance</td><td></td><td></td><td></td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_op_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
          }else{
            $table_body.="<tr><td></td><td>Opening Balance</td><td></td><td></td><td></td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
          }
        }
        if ($type == 'daily_transactions') {
          $account_where['ah.op_balance_date >='] = $value->format('Y-m-d') . " 00:00:00";
          $account_where['ah.op_balance_date <='] = $value->format('Y-m-d') . " 23:59:59";
          $account_where['ah.op_balance !='] = 0;
          $this->db->where("((ah.account_type=32 or ah.account_type=36))");
          $account_details = $this->Account->select_account_heads("ah.acc_head_id,ah.op_balance,ah.account_type", $account_where)->result();
          foreach ($account_details as $key22 => $value22) {
            $table_body.="<tr><td></td><td>Opening Balance</td><td></td><td></td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($value22->op_balance,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($value22->op_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
            $total_balance+=$value22->op_balance;
            $day_total_received+=$value22->op_balance;
          }
        }
        $supplier_opbalance_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $supplier_advance_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $client_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $user_receipt_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $user_return_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $user_payment_receipt_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $user_payment_return_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $other_income_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $purchase_order_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $purchase_bill_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $labour_wage_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $subcontract_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $vehicle_expense_transaction_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $other_expense_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $loan_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $loan_receipt_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $salary_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $vehicle_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $tool_purchase_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $tool_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $user_commission_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $employee_payment_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $sales_invoice_receipt_where['ats.tr_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $purchase_bill_where['p.purchase_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $client_invoice_where['i.invoice_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $labour_wage_slips_where['lw.work_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $salary_slips_where['ss.month'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $vehicle_work_slips_where['vws.worked_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $vehicle_expense_bills_where['b.bill_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $expense_bills_where['b.bill_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        $subcontract_bills_where['b.bill_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));

        if (!isset($_POST['work']) || $_POST['work'] != 'all') {
          if ($this->session->userdata('user_role') == 'supervisor' || $this->session->userdata('user_role') == 'general_manager') {
            // User Payment Receipts
            $user_receipt_where['ats.tr_type'] = 'user_payment_receipt_transaction';
            $user_receipt_where['ats.reference_type'] = 'user_account_head';
            $user_receipt_where['u3.user_id'] = $this->session->userdata('user_id');
            $user_payments_received = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u3.user_alias as received_user", $user_receipt_where, 'user_payment_receipt')->result();
            foreach ($user_payments_received as $key => $row) {
              $transactions[$i]['transaction_id'] = $row->transaction_id;
              $transactions[$i]['tr_mode'] = 'received';
              $transactions[$i]['tr_details'] = "By ".$row->created_user." [To : ".$row->received_user."](User Payment Receipt)";
              $transactions[$i]['tr_amount'] = $row->tr_amount;
              $i++;
            }

            // User Payment Returns
            $user_return_where['ats.tr_type'] = 'user_payment_return_transaction';
            $user_return_where['ats.reference_type'] = 'user_account_head';
            $user_return_where['u4.user_id'] = $this->session->userdata('user_id');
            $user_payments_returned = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u3.user_alias as received_user,ah2.acc_head_name as credit_account", $user_return_where, 'user_payment_return')->result();
            foreach ($user_payments_returned as $key => $row) {
              $transactions[$i]['transaction_id'] = $row->transaction_id;
              $transactions[$i]['tr_mode'] = 'paid';
              $transactions[$i]['tr_details'] = "To ".$row->credit_account." [From : ".$row->created_user."](User Payment Return)";
              $transactions[$i]['tr_amount'] = $row->tr_amount;
              $i++;
            }
          }
        }

        // Purchase Bills
        $purchase_bills = $this->Purchases->select_purchase("p.*,sl.location_name,s.supplier_name",$purchase_bill_where)->result();
        foreach ($purchase_bills as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->purchase_id;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Purchase Bill #".$row->purchase_id." [Supplier: ".$row->supplier_name."] (Purchased To: ".$row->location_name.")";
          $transactions[$i]['tr_amount'] = $row->bill_payable_amount;
          $i++;
        }
        // Client Invoice
        $client_invoices = $this->Invoices->select_invoices("i.*,c.client_name,wc.work_name",$client_invoice_where)->result();
        foreach ($client_invoices as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->invoice_id ;
          $transactions[$i]['tr_mode'] = 'receivable';
          $transactions[$i]['tr_details'] = "Receivable Client Invoice #".$row->invoice_id." [Client: ".$row->client_name."] (For: ".$row->work_name.")";
          $transactions[$i]['tr_amount'] = $row->invoice_total_amount;
          $i++;
        }
        // Labour Wage SLIPS
        $labour_wage_slips = $this->Labour_wages->select_labour_wages("lw.*,l.labour_name,wc.work_name",$labour_wage_slips_where)->result();
        foreach ($labour_wage_slips as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->wage_id ;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Labour Wage Slip #".$row->wage_id." [Labour: ".$row->labour_name."] (Project : ".$row->work_name.")";
          $transactions[$i]['tr_amount'] = $row->total_wage;
          $i++;
        }
        // Salary SLIPS
        $salary_slips = $this->Salary_slips->select_salary_slips("ss.*,e.employee_name,wc.work_name",$salary_slips_where)->result();
        foreach ($salary_slips as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->salary_id;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Salary Slip #".$row->salary_id." [Employee: ".$row->employee_name."] (For : ".date('F Y',strtotime($row->month)).")";
          if($row->work_id!=0){
            $transactions[$i]['tr_details'] .= " [Project: #".$row->work_id." ".$row->work_name."]";
          }
          $transactions[$i]['tr_amount'] = $row->net_salary_amount;
          $i++;
        }

        // Vehicle Work Slip
        $vehicle_work_slips = $this->Vehicle_work_slips->select_vehicle_work_slips("vws.*,v.vehicle_no,vc.consignee_name,wc.work_name",$vehicle_work_slips_where)->result();
        foreach ($vehicle_work_slips as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->work_slip_id;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Vehicle Work Slip #".$row->work_slip_id." [Vehicle: ".$row->vehicle_no."] (For : ".$row->work_name.")";
          $transactions[$i]['tr_amount'] = $row->total_payable_amount;
          $i++;
        }
        // Vehicle Expense Bill
        $vehicle_expense_bills_where['b.bill_type'] = 'vehicle';
        $vehicle_expense_bills = $this->Bills->select_bills("b.*,ah1.acc_head_id,ah1.acc_head_name,v.vehicle_no",$vehicle_expense_bills_where,'vehicle_bill')->result();
        foreach ($vehicle_expense_bills as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->bill_id;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Vehicle Expense Bill #".$row->bill_id." [Vehicle: ".$row->vehicle_no."] (For : ".$row->acc_head_name.")";
          $transactions[$i]['tr_amount'] = $row->bill_payable_amount;
          $i++;
        }

        // Expense Bill
        $expense_bills_where['b.bill_type'] = 'expense';
        $expense_bills = $this->Bills->select_bills("b.*,ah1.acc_head_id,ah1.acc_head_name,wc.work_name",$expense_bills_where,'expense_bill')->result();
        foreach ($expense_bills as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->bill_id;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Expense Bill #".$row->bill_id." (For : ".$row->acc_head_name.")";
          if($row->work_id!=0){
            $transactions[$i]['tr_details'] .= " [Project: #".$row->work_id." ".$row->work_name."]";
          }
          $transactions[$i]['tr_amount'] = $row->bill_payable_amount;
          $i++;
        }

        // Subcontract Bill
        $subcontract_bills_where['b.bill_type'] = 'subcontract';
        $subcontract_bills = $this->Bills->select_bills("b.*,ah1.acc_head_id,ah1.acc_head_name,wc.work_name,c.contractor_name",$subcontract_bills_where,'subcontract_bill')->result();
        foreach ($subcontract_bills as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->bill_id;
          $transactions[$i]['tr_mode'] = 'payable';
          $transactions[$i]['tr_details'] = "Payable Subcontract Bill #".$row->bill_id." (For : ".$row->work_name.") - (Contractor: ".$row->contractor_name.")";
          $transactions[$i]['tr_amount'] = $row->bill_payable_amount;
          $i++;
        }


        // Client Payment Receipts
        $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
        $client_payment_where['ats.reference_type'] = 'project';
        $client_payments_received  = $this->Account->select_account_transaction("ats.transaction_id,c.client_name,wc.work_name,ats.tr_amount,ats.tr_date,ah2.acc_head_name", $client_payment_where, 'client_payment')->result();
        foreach ($client_payments_received as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'received';
          $transactions[$i]['tr_details'] = "By ".$row->client_name." for Work: ".$row->work_name." [Credited To: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        // Sales Invoice Receipts
        $sales_invoice_receipt_where['ats.tr_type'] = 'sales_invoice_transaction';
        $sales_invoice_receipt_where['ats.reference_type'] = 'sales_invoice';
        $sales_invoice_received = $this->Account->select_account_transaction("ah2.acc_head_name,ats.transaction_id,c.customer_name,ats.reference_id,ats.tr_amount,ats.tr_date,ats.tr_description", $sales_invoice_receipt_where, 'sales_invoice')->result();
        foreach ($sales_invoice_received as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'received';
          $transactions[$i]['tr_details'] = "By ".$row->customer_name." for Ware House Sales Invoice: #".$row->reference_id." [Credited To: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        if (!isset($_POST['work']) || $_POST['work'] == 'all') {
          // Other Income Receipts
          $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
          $this->db->where("(ats.reference_type='income_receipt' or (ats.reference_type='client' and ats.invoice_id!=0))");
          $other_income_received = $this->Account->select_account_transaction("ah2.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,ats.tr_description", $other_income_where, 'income_receipt')->result();
          foreach ($other_income_received as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'received';
            $transactions[$i]['tr_details'] = "By ".$row->tr_description." (Income Receipt) [Credited To: ".$row->acc_head_name."]";
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
        }

        // Loan Receipts
        $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
        $loan_receipt_where['ats.reference_type'] = 'loan';
        $loan_received = $this->Account->select_account_transaction("ah2.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,ats.tr_description,l.loan_title,lp.loan_provider_name", $loan_receipt_where, 'loan')->result();
        foreach ($loan_received as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'received';
          $transactions[$i]['tr_details'] = "By ".$row->loan_title." (Loan Receipt From ".$row->loan_provider_name.")  [Credited To: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }

        // Purchase Order Payment
        $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;
        $purchase_order_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,s.supplier_name,po.order_id,ats.tr_amount,ats.tr_date,sl.location_name,ah1.acc_head_name", $purchase_order_payment_where,'purchase_order')->result();
        foreach ($purchase_order_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = "To ".$row->supplier_name." for Purchase Order: #".$row->order_id." (Location: ".$row->location_name.") [Debited From: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        // Purchase Bill Payment
        $purchase_bill_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
        $purchase_bill_payment_where['ats.reference_type'] = 'purchase_bill';
        $purchase_bill_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,s.supplier_name,p.purchase_id,ats.tr_amount,ats.tr_date,sl.location_name", $purchase_bill_payment_where,'purchase_bill')->result();
        foreach ($purchase_bill_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = "To ".$row->supplier_name." for Purchase Bill: #".$row->purchase_id." (Location: ".$row->location_name.") [Debited From: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        if (!isset($_POST['work']) || $_POST['work'] == 'all') {
          // Supplier Advance
          $supplier_advance_payment_where['ats.reference_type'] = 'supplier';
          $supplier_advance_payment_where['ats.tr_type'] = 'supplier_advance_transaction';
          $supplier_advance_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,s.supplier_name", $supplier_advance_payment_where, 'supplier')->result();
          foreach ($supplier_advance_payments as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'paid';
            $transactions[$i]['tr_details'] = "To " . $row->supplier_name. " (Supplier Advance payment) [Debited From: ".$row->acc_head_name."]";
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
          // Supplier Op Balance
          $supplier_opbalance_payment_where['ats.reference_type'] = 'supplier';
          $supplier_opbalance_payment_where['ats.tr_type'] = 'supplier_opbalance_transaction';
          $supplier_opbalance_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,s.supplier_name", $supplier_opbalance_payment_where, 'supplier')->result();
          foreach ($supplier_opbalance_payments as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'paid';
            $transactions[$i]['tr_details'] = "To " . $row->supplier_name. " (Supplier Op Balance payment) [Debited From: ".$row->acc_head_name."]";
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
        }
        if (!isset($_POST['work']) || $_POST['work'] == 'all') {
          // Labour Wage Payment
          $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
          $labour_wage_payment_where['ats.reference_type'] = 'labour';
          $labour_wage_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,l.labour_name,ats.tr_amount,ats.tr_date", $labour_wage_payment_where, 'labour')->result();
          foreach ($labour_wage_payments as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'paid';
            $transactions[$i]['tr_details'] = "To ".$row->labour_name." for Labour Wage [Debited From: ".$row->acc_head_name."]";
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
        }
        if ($this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin' || ($this->session->userdata('user_role') == 'general_manager' && $this->config->item('User_general_manager_hr_data_enabled') == 1)) {
          // Salary Slip
          $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
          $salary_payment_where['ats.reference_type'] = 'salary_slip';
          $salary_slip_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,e.employee_name,ats.tr_amount,ats.tr_date", $salary_payment_where, 'salary_slip')->result();
          foreach ($salary_slip_payments as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'paid';
            $transactions[$i]['tr_details'] = "To ".$row->employee_name." for Salary Payment [Debited From: ".$row->acc_head_name."]";
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
          // Employee Payments
          $employee_payment_where['ats.reference_type'] = 'employee';
          $this->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction' or ats.tr_type='employee_bata_transaction')");
          $employee_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.tr_type,ats.transaction_id,e2.employee_name,ats.tr_amount,ats.tr_date", $employee_payment_where, 'employee')->result();
          foreach ($employee_payments as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'paid';
            if($row->tr_type=='salary_advance_transaction'){
              $transactions[$i]['tr_details'] = "To ".$row->employee_name." for Salary Advance [Debited From: ".$row->acc_head_name."]";
            }elseif($row->tr_type=='employee_bata_transaction'){
              $transactions[$i]['tr_details'] = "To ".$row->employee_name." for Employee Bata [Debited From: ".$row->acc_head_name."]";
            }else{
              $transactions[$i]['tr_details'] = "To ".$row->employee_name." for Opening Balance Payment [Debited From: ".$row->acc_head_name."]";
            }
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
        }
        // Vehicle Payment
        $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
        $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
        $vehicle_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.tr_amount,v.id,vc.consignee_name,v.vehicle_name,v.vehicle_no,ats.tr_date", $vehicle_payment_where, 'vehicle_work_slip')->result();
        foreach ($vehicle_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = "To ".$row->consignee_name." [ ".$row->vehicle_name." : ".$row->vehicle_no." ]" . " (Vehicle Payment) [Debited From: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }

        // Vehicle Payment
        $vehicle_payment_where['ats.reference_type'] = 'vehicle';
        $vehicle_payment_where['ats.tr_type'] = 'vehicle_renewal_transaction';
        $vehicle_renewal_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.tr_other_data,ats.transaction_id,ats.tr_amount,v.id,vc.consignee_name,v.vehicle_name,v.vehicle_no,ats.tr_date", $vehicle_payment_where, 'vehicle_work_slip')->result();
        foreach ($vehicle_renewal_payments as $key => $row) {
          $tr_other_data = explode(';', $row->tr_other_data);
          $renewal_type = $tr_other_data[0];
          $renewal_date = $tr_other_data[1];

          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = "To ".$row->consignee_name." [ ".$row->vehicle_name." : ".$row->vehicle_no." ]" . " (Vehicle ".$renewal_type." Renewal) [Debited From: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }

        // Vehicle Expense Bill Transaction
        $vehicle_expense_transaction_where['ats.tr_type'] = 'vehicle_bill_transaction';
        $vehicle_expense_transaction_where['ats.reference_type'] = 'vehicle_bill';
        $vehicle_expense_transactions = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ah2.acc_head_name as expense_head_name,ats.tr_amount,ats.tr_date,v.vehicle_no", $vehicle_expense_transaction_where,'vehicle_bill')->result();
        foreach ($vehicle_expense_transactions as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_details'] = "To ".$row->expense_head_name." for Vehicle: ".$row->vehicle_no." [Debited From: ".$row->acc_head_name."]";;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }

        // Subcontract Payment
        if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
          $work_id = $_POST['work'];
          $this->db->where("((ats.reference_type='subcontract_bill' and sc.work_id=$work_id) or (ats.reference_type='subcontract' and sc2.work_id=$work_id))");
        }
        $this->db->where("(ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction')");
        $this->db->where("(ats.reference_type='subcontract_bill' or ats.reference_type='subcontract')");
        $sub_contract_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.reference_id,c.contractor_name,c2.contractor_name as contractor_name_2,ats.transaction_id,sc.contractor,sc.sub_work_name,sc2.contractor as contractor_2,sc2.sub_work_name as sub_work_name_2,ats.tr_amount,ats.tr_date,wc.work_name,ats.tr_type", $subcontract_payment_where,'subcontract_transactions')->result();
        foreach ($sub_contract_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          if($row->tr_type=='subcontract_bill_transaction'){
            if($row->contractor_name!=''){
              $contractor_name = $row->contractor_name;
            } else {
              $contractor_name = $row->contractor;
            }
            $transactions[$i]['tr_details'] = "To ".$contractor_name." for Subcontract Bill #".$row->reference_id." Payment of Work: ".$row->work_name." [Debited From: ".$row->acc_head_name."]";
          }else{
            if($row->contractor_name_2!=''){
              $contractor_name = $row->contractor_name_2;
            } else {
              $contractor_name = $row->contractor_2;
            }
            $transactions[$i]['tr_details'] = "To ".$contractor_name." for Subcontract Advance of Work: ".$row->sub_work_name_2." [Debited From: ".$row->acc_head_name."]";
          }
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        // Other Expenses
        $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
        $other_expense_where['ats.reference_type'] = 'expense_bill';
        $expense_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.reference_id,ats.transaction_id,b.work_id,ah2.acc_head_name as expense_head_name,ats.tr_amount,ats.tr_date,wc.work_name", $other_expense_where,'expense_bill')->result();
        foreach ($expense_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          if($row->work_id!=0){
            $transactions[$i]['tr_details'] = "To ".$row->expense_head_name." (Expense Bill #".$row->reference_id.") for Work: ".$row->work_name." [Debited From: ".$row->acc_head_name."]";;
          }else{
            $transactions[$i]['tr_details'] = "To ".$row->expense_head_name." (Expense Bill #".$row->reference_id.") [Debited From: ".$row->acc_head_name."]";
          }
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        if (!isset($_POST['work']) || $_POST['work'] == 'all') {
          if ($this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
            // User Payment Receipt
            $user_payment_receipt_where['ats.reference_type'] = 'user_account_head';
            $user_payment_receipt_where['ats.tr_type'] = 'user_payment_receipt_transaction';
            $user_payment_receipts = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u3.user_alias as received_user,ah2.acc_head_name as received_user_account,ah1.acc_head_name as debit_account", $user_payment_receipt_where, 'user_payment_receipt')->result();
            foreach ($user_payment_receipts as $key => $row) {
              $transactions[$i]['transaction_id'] = $row->transaction_id;
              $transactions[$i]['tr_mode'] = 'paid';
              $transactions[$i]['tr_details'] = "To ".$row->received_user_account." (User Payment) [Debited From: ".$row->debit_account."]";
              $transactions[$i]['tr_amount'] = $row->tr_amount;
              $i++;

              $transactions[$i."_a"]['transaction_id'] = $row->transaction_id;
              $transactions[$i."_a"]['tr_mode'] = 'received';
              $transactions[$i."_a"]['tr_details'] = "By ".$row->debit_account." (User Payment Receipt - ".$row->received_user_account.")";
              $transactions[$i."_a"]['tr_amount'] = $row->tr_amount;
              $i++;
            }

            // User Payment Return
            $user_payment_return_where['ats.reference_type'] = 'user_account_head';
            $user_payment_return_where['ats.tr_type'] = 'user_payment_return_transaction';
            $user_payment_returns = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u4.user_alias as returned_user,ah1.acc_head_name as returned_user_account,ah2.acc_head_name as credit_account", $user_payment_return_where, 'user_payment_return')->result();
            foreach ($user_payment_returns as $key => $row) {
              $transactions[$i]['transaction_id'] = $row->transaction_id;
              $transactions[$i]['tr_mode'] = 'received';
              $transactions[$i]['tr_details'] = "By ".$row->returned_user_account." (User Payment Return Receipt) [Credited To: ".$row->credit_account."]";
              $transactions[$i]['tr_amount'] = $row->tr_amount;
              $i++;

              $transactions[$i."_a"]['transaction_id'] = $row->transaction_id;
              $transactions[$i."_a"]['tr_mode'] = 'paid';
              $transactions[$i."_a"]['tr_details'] = "To ".$row->credit_account." (User Payment Return - ".$row->returned_user_account.")";
              $transactions[$i."_a"]['tr_amount'] = $row->tr_amount;
              $i++;
            }
          }
        }

        // Loan Payment
        $loan_payment_where['ats.reference_type'] = 'loan';
        $loan_payment_where['ats.tr_type'] = 'loan_transaction';
        $loan_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,l.loan_title,lp.loan_provider_name", $loan_payment_where, 'loan')->result();
        foreach ($loan_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = "To " . $row->loan_provider_name." [ ".$row->loan_title." ] " . " (Loan Payment) [Debited From: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }

        // User Commission Payment
        $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
        $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
        $user_commission_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u3.user_alias as received_user,ah2.acc_head_name as received_user_account,wc.work_name", $user_commission_payment_where, 'user_commission')->result();
        foreach ($user_commission_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = "To " . $row->received_user_account." (User Project Commission Payment For Work :".$row->work_name.") [Debited From: ".$row->acc_head_name."]";
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }

        if (!isset($_POST['work']) || $_POST['work'] == 'all') {
          // Tool Purchase
          $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
          $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
          $tool_purchase = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.tr_amount,ats.tr_date,t.name,tp.purchased_from", $tool_purchase_where, 'tool_purchase')->result();
          foreach ($tool_purchase as $key => $row) {
            $transactions[$i]['transaction_id'] = $row->transaction_id;
            $transactions[$i]['tr_mode'] = 'paid';
            $transactions[$i]['tr_details'] = "To " . $row->purchased_from." [ ".$row->name." ] " . " (Tool Purchase) [Debited From: ".$row->acc_head_name."]";
            $transactions[$i]['tr_amount'] = $row->tr_amount;
            $i++;
          }
        }

        // Tool Payments
        if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
          $work_id = $_POST['work'];
          $this->db->where("((tr.rented_to='$work_id' or tt.transfer_to='$work_id'))");
        }
        $this->db->where('(ats.reference_type="tool_transfer" or ats.reference_type="tool_rent")');
        $tool_payment_where['ats.tr_type'] = 'tool_payment_transaction';
        $tool_payments = $this->Account->select_account_transaction("ah1.acc_head_name,ats.transaction_id,ats.reference_type,ats.tr_amount,ats.tr_date,t.tool_id,t.name,t.description,t.tool_type,tc.cat_name,wc.work_name as rented_to_location,tr.rented_from,t2.tool_id as tool_id_2,t2.name as name_2,t2.description as description_2,t2.tool_type as tool_type_2,tc2.cat_name as cat_name_2,wc2.work_name as transfer_to_location,tt.transfer_from", $tool_payment_where, 'tool')->result();
        foreach ($tool_payments as $key => $row) {
          $transactions[$i]['transaction_id'] = $row->transaction_id;
          if($row->reference_type=='tool_transfer'){
            $description = "To Office For " . $row->name_2." [ ".$row->cat_name_2." ] " . " (Owned Tool Payment) For Work: ".$row->transfer_to_location." [Debited From: ".$row->acc_head_name."]";
          }else{
            $description = "To ".$row->rented_from." For " . $row->name." [ ".$row->cat_name." ] " . " (Rent Tool Payment) For Work: ".$row->rented_to_location." [Debited From: ".$row->acc_head_name."]";
          }
          $transactions[$i]['tr_mode'] = 'paid';
          $transactions[$i]['tr_details'] = $description;
          $transactions[$i]['tr_amount'] = $row->tr_amount;
          $i++;
        }
        //sort array
        ksort($transactions);
        foreach ($transactions as $key => $value2) {
          $sl_no++;
          if($value2['tr_mode']=='payable'){
            // $total_balance+=$value2['tr_amount'];
            $day_total_payable+=$value2['tr_amount'];
            $table_body.="<tr><td>".$sl_no."</td><td>" .$value2['tr_details']."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($value2['tr_amount'],$this->config->item('Currency_currency_shortname'))."</td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_payable-$day_total_payment),$this->config->item('Currency_currency_shortname'))."</td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
          }elseif($value2['tr_mode']=='paid'){
            $total_balance-=$value2['tr_amount'];
            $day_total_payment+=$value2['tr_amount'];
            $table_body.="<tr><td>".$sl_no."</td><td>" .$value2['tr_details']."</td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($value2['tr_amount'],$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_payable-$day_total_payment),$this->config->item('Currency_currency_shortname'))."</td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
          }elseif($value2['tr_mode']=='receivable'){
            // $total_balance+=$value2['tr_amount'];
            $day_total_receivable+=$value2['tr_amount'];
            $table_body.="<tr><td>".$sl_no."</td><td>" .$value2['tr_details']."</td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($value2['tr_amount'],$this->config->item('Currency_currency_shortname'))."</td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_receivable-$day_total_received),$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
          }else{
            $total_balance+=$value2['tr_amount'];
            $day_total_received+=$value2['tr_amount'];
            $table_body.="<tr><td>".$sl_no."</td><td>" .$value2['tr_details']."</td><td></td><td></td><td></td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($value2['tr_amount'],$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_receivable-$day_total_received),$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
          }
        }
        if($type!='daily_transactions'){
          $table_body.="<tr><td></td><td>Day Total & Closing Balance</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_payable,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_payment,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_payable-$day_total_payment),$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_receivable,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_received,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_receivable-$day_total_received),$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
        }else{
          $day_balance = $day_total_received-$day_total_payment;
          $table_body.="<tr><td></td><td>Day Total & Closing Balance</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_payable,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_payment,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_payable-$day_total_payment),$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_receivable,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_received,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(($day_total_receivable-$day_total_received),$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
        }
        $table_body.="<tr><td colspan='9'>&nbsp;</td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td>td style='display:none'></td>td style='display:none'></td></tr>";

        $j++;
      }
    }

    $json_data['table_body'] = $table_body;
    if ($table_body != '') {
      $json_data['count'] = 1;
    }

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

  public function select_project_daybook()
  {

    $json_data = array();
    $table_body = "";
    $j = 0;
    $data = array();
    $total_received = $total_payment = $op_balance = $total_balance = 0;

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));
      // of period 1 day
      $interval = new DateInterval('P1D');
      $realEnd = new DateTime($end_date);
      $realEnd->add($interval);
      $date_period = new DatePeriod(new DateTime($start_date), $interval, $realEnd);

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

      $employee_payment_where = $user_commission_payment_where = $tool_payment_where = $loan_receipt_where = $loan_payment_where = $user_receipt_where = $user_return_where = $salary_payment_where = $other_income_where = $client_payment_where = $purchase_payment_where = $purchase_order_payment_where = $subcontract_payment_where = $other_expense_where = $purchase_bill_payment_where = array();

      if ($this->session->userdata('user_role') != 'auditor' && $this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'madmin') {
        $employee_payment_where['ats.created_by'] = $user_commission_payment_where['ats.created_by'] = $tool_payment_where['ats.created_by'] = $loan_payment_where['ats.created_by'] = $other_income_where['ats.created_by'] = $salary_payment_where['ats.created_by'] = $vehicle_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_payment_where['ats.created_by'] = $subcontract_payment_where['ats.created_by'] = $this->session->userdata('user_id');
      }
      $work_id = '';
      if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
        $work_id = $user_commission_payment_where['ats.tr_work_id'] = $labour_wage_slips_where['lw.work_id'] = $purchase_bill_items_where['sl.work_id'] = $loan_receipt_where['wc.work_id'] = $loan_payment_where['wc.work_id'] = $salary_payment_where['wc.work_id'] = $vehicle_payment_where['wc.work_id'] = $client_payment_where['wc.work_id'] = $other_expense_where['wc.work_id']  = $purchase_order_payment_where["wc.work_id"] = $purchase_payment_where['wc.work_id'] = $purchase_bill_payment_where['wc.work_id']  = $_POST['work'];
      }

      // Calculate Day OP Balance
      if ($this->session->userdata('user_role') == 'auditor' || $this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
        $initial_op_balance = $this->accounts_management->calculate_trial_balance('', date('Y-m-d', strtotime('-1 day', strtotime($start_date))), 'company_accounts', $work_id,$this->session->userdata('user_role'),$this->session->userdata('user_id'));
      } else {
        $initial_op_balance = $this->accounts_management->calculate_trial_balance('', date('Y-m-d', strtotime('-1 day', strtotime($start_date))), 'user_accounts', $work_id,$this->session->userdata('user_role'),$this->session->userdata('user_id'));
      }
      $total_balance += $initial_op_balance;

      foreach ($date_period as $key0 => $value) {
        $day_table_body = '';
        $day_op_balance = $total_balance;
        $day_total_received = $day_total_payment = $day_balance = 0;

        $transactions = array();


        $client_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $user_receipt_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $user_return_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $user_payment_receipt_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $user_payment_return_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $other_income_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $purchase_order_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $purchase_bill_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $labour_wage_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $subcontract_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $other_expense_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $loan_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $loan_receipt_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $salary_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $vehicle_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $purchase_bill_items_where['p.purchase_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $labour_wage_slips_where['lw.work_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $tool_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $user_commission_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $employee_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));

        // Client Payment Receipts
        $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
        $client_payment_where['ats.reference_type'] = 'project';
        $client_payments_received = $this->Account->select_account_transaction("ats.transaction_id,c.client_name,wc.work_name,ats.tr_amount,ats.tr_date", $client_payment_where, 'client_payment')->result();

        foreach ($client_payments_received as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'received';
          $transactions[$row->transaction_id]['tr_details'] = "By " . $row->client_name . " for Work: " . $row->work_name . "";
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // Loan Receipts
        $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
        $loan_receipt_where['ats.reference_type'] = 'loan';
        $loan_received = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,ats.tr_description,l.loan_title,l.total_loan_amount,lp.loan_provider_name", $loan_receipt_where, 'loan')->result();
        foreach ($loan_received as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'received';
          $transactions[$row->transaction_id]['tr_details'] = "By " . $row->loan_title . " (Loan Receipt From " . $row->loan_provider_name . ")";
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // Purchase Order Payment
        $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;
        $purchase_order_payments = $this->Account->select_account_transaction("ats.transaction_id,s.supplier_name,po.order_id,ats.tr_amount,ats.tr_date", $purchase_order_payment_where, 'purchase_order')->result();
        foreach ($purchase_order_payments as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_details'] = "To " . $row->supplier_name . " for Purchase Order: #" . $row->order_id . "";
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // Purchase Bill Payment
        $purchase_bill_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
        $purchase_bill_payment_where['ats.reference_type'] = 'purchase_bill';
        $purchase_bill_payments = $this->Account->select_account_transaction("ats.transaction_id,s.supplier_name,p.purchase_id,ats.tr_amount,ats.tr_date", $purchase_bill_payment_where, 'purchase_bill')->result();
        foreach ($purchase_bill_payments as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_details'] = "To " . $row->supplier_name . " for Purchase Bill: #" . $row->purchase_id;
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }



        if ($this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin' || ($this->session->userdata('user_role') == 'general_manager' && $this->config->item('User_general_manager_hr_data_enabled') == 1)) {
          // Salary Slip
          $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
          $salary_payment_where['ats.reference_type'] = 'salary_slip';
          $labour_wage_payments = $this->Account->select_account_transaction("ats.transaction_id,e.employee_name,ats.tr_amount,ats.tr_date", $salary_payment_where, 'salary_slip')->result();
          foreach ($labour_wage_payments as $key => $row) {
            $transactions[$row->transaction_id]['tr_mode'] = 'paid';
            $transactions[$row->transaction_id]['tr_details'] = "To " . $row->employee_name . " for Salary Payment";
            $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
          }
        }

        // Vehicle Payment
        $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
        $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
        $vehicle_payments = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,v.id,vc.consignee_name,v.vehicle_name,v.vehicle_no,ats.tr_date", $vehicle_payment_where, 'vehicle_work_slip')->result();
        foreach ($vehicle_payments as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_details'] = "To " . $row->consignee_name . " [ " . $row->vehicle_name . " : " . $row->vehicle_no . " ]" . " (Vehicle Payment)";
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // Subcontract Payment
        if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
          $work_id = $_POST['work'];
          $this->db->where("((ats.reference_type='subcontract_bill' and sc.work_id=$work_id) or (ats.reference_type='subcontract' and sc2.work_id=$work_id))");
        }
        $this->db->where("(ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction')");
        $this->db->where("(ats.reference_type='subcontract_bill' or ats.reference_type='subcontract')");
        $sub_contract_payments = $this->Account->select_account_transaction("ats.transaction_id,sc.contractor,sc.sub_work_name,sc2.contractor as contractor_2,sc2.sub_work_name as sub_work_name_2,ats.tr_amount,ats.tr_date,wc.work_name,ats.tr_type", $subcontract_payment_where, 'subcontract_transactions')->result();
        foreach ($sub_contract_payments as $key => $row) {
          if ($row->tr_type == 'subcontract_bill_transaction') {
            $transactions[$row->transaction_id]['tr_details'] = "To " . $row->contractor . " for Subcontract Bill Payment of Work: " . $row->work_name;
          } else {
            $transactions[$row->transaction_id]['tr_details'] = "To " . $row->contractor_2 . " for Subcontract Advance of Work: " . $row->sub_work_name_2;
          }
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // Other Expenses
        $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
        $other_expense_where['ats.reference_type'] = 'expense_bill';
        $expense_payments = $this->Account->select_account_transaction("ats.transaction_id,b.work_id,ah2.acc_head_name as expense_head_name,ats.tr_amount,ats.tr_date,wc.work_name", $other_expense_where, 'expense_bill')->result();
        foreach ($expense_payments as $key => $row) {
          if ($row->work_id != 0) {
            $transactions[$row->transaction_id]['tr_details'] = "To " . $row->expense_head_name . " for Work: " . $row->work_name;
          } else {
            $transactions[$row->transaction_id]['tr_details'] = "To " . $row->expense_head_name;
          }
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }


        // Loan Payment
        $loan_payment_where['ats.reference_type'] = 'loan';
        $loan_payment_where['ats.tr_type'] = 'loan_transaction';
        $loan_payments = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,l.total_loan_amount,l.loan_title,lp.loan_provider_name", $loan_payment_where, 'loan')->result();
        foreach ($loan_payments as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_details'] = "To " . $row->loan_provider_name . " [ " . $row->loan_title . " ] " . " (Loan Payment)";
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // User Commission Payment
        $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
        $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
        $user_commission_payment = $this->Account->select_account_transaction("ats.transaction_id,ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u3.user_alias as received_user,ah2.acc_head_name as received_user_account,wc.work_name", $user_commission_payment_where, 'user_commission')->result();
        foreach ($user_commission_payment as $key => $row) {
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_details'] = "To " . $row->received_user_account . " (User Project Commission Payment For Work :" . $row->work_name . ")";
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // Tool Payments
        $tool_payment_where['ats.tr_type'] = 'tool_payment_transaction';
        if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
          $work_id = $_POST['work'];
          $this->db->where("((tr.rented_to='$work_id' or tt.transfer_to='$work_id'))");
        }
        $this->db->where('((ats.reference_type="tool_transfer" or ats.reference_type="tool_rent"))');
        $tool_payments = $this->Account->select_account_transaction("ats.transaction_id,ats.reference_type,ats.tr_amount,ats.tr_date,t.tool_id,t.name,t.description,t.tool_type,tc.cat_name,wc.work_name as rented_to_location,tr.rented_from,t2.tool_id as tool_id_2,t2.name as name_2,t2.description as description_2,t2.tool_type as tool_type_2,tc2.cat_name as cat_name_2,wc2.work_name as transfer_to_location,tt.transfer_from", $tool_payment_where, 'tool')->result();
        foreach ($tool_payments as $key => $row) {
          if ($row->reference_type == 'tool_transfer') {
            $description = "To Office For " . $row->name_2 . " [ " . $row->cat_name_2 . " ] " . " (Owned Tool Payment) For Work: " . $row->transfer_to_location;
          } else {
            $description = "To " . $row->rented_from . " For " . $row->name . " [ " . $row->cat_name . " ] " . " (Rent Tool Payment) For Work: " . $row->rented_to_location;
          }
          $transactions[$row->transaction_id]['tr_mode'] = 'paid';
          $transactions[$row->transaction_id]['tr_details'] = $description;
          $transactions[$row->transaction_id]['tr_amount'] = $row->tr_amount;
        }

        // $day_table_body.="<tr><td></td><td>Day Total & Closing Balance</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_payment,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($day_total_received,$this->config->item('Currency_currency_shortname'))."</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_balance,$this->config->item('Currency_currency_shortname'))."</td></tr>";
        // $day_table_body.="<tr><td colspan='5'>&nbsp;</td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td><td style='display:none'></td></tr>";
        //sort array
        ksort($transactions);
        foreach ($transactions as $key => $value2) {
          $sl_no++;
          if ($value2['tr_mode'] == 'paid') {
            $total_balance -= $value2['tr_amount'];
            $day_total_payment += $value2['tr_amount'];
            $day_table_body .= "<tr><td>" . $sl_no . "</td><td>" . $value2['tr_details'] . "</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value2['tr_amount'], $this->config->item('Currency_currency_shortname')) . "</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
          } else {
            $total_balance += $value2['tr_amount'];
            $day_total_received += $value2['tr_amount'];
            $day_table_body .= "<tr><td>" . $sl_no . "</td><td>" . $value2['tr_details'] . "</td><td></td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value2['tr_amount'], $this->config->item('Currency_currency_shortname')) . "</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
          }
        }
        //purchase bill items
        $purchase_bill_items = $this->Purchases->select_purchase_items("pi.*,m.material_name,mu.unit_name", $purchase_bill_items_where)->result();
        if (sizeof($purchase_bill_items) > 0) {
          foreach ($purchase_bill_items as $key => $row) {
            $sl_no++;
            $day_table_body .= "<tr><td>" . $sl_no . "</td><td>Material Purchase - " . $row->material_name . " [" . $row->quantity . " " . $row->unit_name . "] (Bill ID:#".$row->purchase_id.")  </td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td><td></td><td></td></tr>";
          }
        }
        // Labour Wage Slip
        $labour_wage_slips  = $this->Labour_wages->select_labour_wages("lw.*,wt.type_name,l.labour_group,l.labour_name,wt.type_name,u.user_alias,wc.work_name", $labour_wage_slips_where, "", "", "", "", "")->result();
        if (sizeof($labour_wage_slips) > 0) {
          foreach ($labour_wage_slips as $key => $row) {
            $sl_no++;
            $day_table_body .= "<tr><td>" . $sl_no . "</td><td>Labour Wage Slip - " . $row->labour_name . " [" . $row->type_name . "]  </td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->total_wage, $this->config->item('Currency_currency_shortname')) . "</td><td></td><td></td><td></td></tr>";
          }
        }
        $j++;

        if ($day_total_payment > 0 || $day_total_received > 0  || $day_table_body != '') {
          $table_body .= "<tr><td></td><td><strong>" . date('d F Y', strtotime($value->format('Y-m-d'))) . "</strong></td><td></td><td></td><td></td><td></td></tr>";
          if($key0==0){
            $table_body .= "<tr><td></td><td>Day Opening Balance</td><td></td><td></td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($initial_op_balance,$this->config->item('Currency_currency_shortname')) . "</td></tr>";
          }else{
            $table_body .= "<tr><td></td><td>Day Opening Balance</td><td></td><td></td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($day_op_balance,$this->config->item('Currency_currency_shortname')) . "</td></tr>";
          }
          $table_body .= $day_table_body;
          $table_body .= "<tr><td></td><td>Day Closing Balance</td><td></td><td></td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        }
      }
    }

    $json_data['table_body'] = $table_body;
    if ($table_body != '') {
      $json_data['count'] = 1;
    }

    echo json_encode($json_data);  // send data as json format
  }
  public function select_purchase_report()
  {

    $json_data = array();
    $table_body = "";
    $j = 0;
    $data = array();
    $total_received = $total_payment = $op_balance = $total_balance = 0;
    $array = array();
    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));
      // of period 1 day
      $interval = new DateInterval('P1D');
      $realEnd = new DateTime($end_date);
      $realEnd->add($interval);
      $date_period = new DatePeriod(new DateTime($start_date), $interval, $realEnd);

      $array = array();
      $sl_no = 1;

      $purchased_material_where = $purchase_payment_where = $purchase_order_payment_where  = array();
      if ($this->session->userdata('user_role') != 'auditor' && $this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'madmin') {
        $purchased_material_where["p.created_by"] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_payment_where['ats.created_by'] = $this->session->userdata('user_id');
      }
      $location_id = $supplier_id = '';
      if (isset($_POST['location']) && $_POST['location'] != '' && $_POST['location'] != 'all') {
        $location_id = $purchased_material_where["p.location_id"] = $purchase_order_payment_where["sl.location_id"] = $purchase_payment_where['sl.location_id'] = $purchase_bill_payment_where['sl.location_id'] = $_POST['location'];
      }
      if (isset($_POST['supplier']) && $_POST['supplier'] != '' && $_POST['supplier'] != 'all') {
        $supplier_id = $purchased_material_where["p.supplier_id"] = $purchase_order_payment_where["po.supplier_id"] = $purchase_payment_where['p.supplier_id'] = $purchase_bill_payment_where['p.supplier_id'] = $_POST['supplier'];
      }

      // Calculate Day OP Balance
      // if($this->session->userdata('user_role')=='admin' || $this->session->userdata('user_role')=='madmin'){
      //   $day_op_balance = $this->accounts_management->calculate_trial_balance('',date('Y-m-d', strtotime('-1 day', strtotime($start_date))),'company_accounts',$work_id,$this->session->userdata('user_role'),$this->session->userdata('user_id'));
      // }else{
      //   $day_op_balance = $this->accounts_management->calculate_trial_balance('',date('Y-m-d', strtotime('-1 day', strtotime($start_date))),'user_accounts',$work_id,$this->session->userdata('user_role'),$this->session->userdata('user_id'));
      // }

      // $total_balance+=$day_op_balance;
      $sl_no = 0;
      foreach ($date_period as $key => $value) {
        // Get Purchased Materials
        $purchased_material_where['p.purchase_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $purchased_materials = $this->Purchases->select_purchase_items("pi.*,s.supplier_id,s.supplier_name,p.purchase_date,m.material_name,mu.unit_name,u.user_alias,p.created_date,p.location_id,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id", $purchased_material_where)->result();
        foreach ($purchased_materials as $row) {
          $sl_no++;
          $array[$j][] = $sl_no;
          $array[$j][] = $value->format('d-m-Y');
          $array[$j][] = "To " . $row->material_name;
          $array[$j][] = "Purchase Bill" . "<br/>" . "For Project #" . $row->work_id . " - " . $row->location_name;
          $array[$j][] = "Bill ID: #" . $row->purchase_id;
          $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][] = '';
          $j++;
        }

        // Purchase Order Payment
        $purchase_order_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $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;
        $purchase_order_payments = $this->Account->select_account_transaction("sl.work_id,wc.work_name,s.supplier_name,po.order_id,ats.tr_amount,ats.tr_date,ats.tr_mode,ah1.acc_head_name as debit_account", $purchase_order_payment_where, 'purchase_order')->result();
        foreach ($purchase_order_payments as $key => $row) {
          $sl_no++;
          $array[$j][] = $sl_no;
          $array[$j][] = $value->format('d-m-Y');
          $array[$j][] = "By " . $row->supplier_name . "<br/>" . $row->debit_account . " - " . $row->tr_mode;
          $array[$j][] = "Purchase Order Payment" . "<br/>" . "For Project #" . $row->work_id . " - " . $row->work_name;
          $array[$j][] = "Order ID: #" . $row->order_id;
          $array[$j][] = '';
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->tr_amount, $this->config->item('Currency_currency_shortname'));
          $j++;
        }
        // Purchase Bill Payment
        $purchase_bill_payment_where['ats.tr_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $purchase_bill_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
        $purchase_bill_payment_where['ats.reference_type'] = 'purchase_bill';
        $purchase_bill_payments = $this->Account->select_account_transaction("sl.work_id,wc.work_name,s.supplier_name,p.purchase_id,ats.tr_amount,ats.tr_date,ats.tr_mode,ah1.acc_head_name as debit_account", $purchase_bill_payment_where, 'purchase_bill')->result();
        foreach ($purchase_bill_payments as $key => $row) {
          $sl_no++;
          $array[$j][] = $sl_no;
          $array[$j][] = $value->format('d-m-Y');
          $array[$j][] = "By " . $row->supplier_name . "<br/>" . $row->debit_account . " - " . $row->tr_mode;
          $array[$j][] = "Purchase Bill Payment" . "<br/>" . "For Project #" . $row->work_id . " - " . $row->work_name;
          $array[$j][] = "Bill ID: #" . $row->purchase_id;
          $array[$j][] = '';
          $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($row->tr_amount, $this->config->item('Currency_currency_shortname'));
          $j++;
        }
      }
    }
    $json_data['data'] = $array;
    $json_data['draw'] = 5;
    $json_data['recordsTotal'] = sizeof($array);
    $json_data['recordsFiltered'] = sizeof($array);

    echo json_encode($json_data);  // send data as json format
  }
  public function select_tax_report()
  {

    $json_data = array();
    $table_body = "";
    $j = $sl_no = 0;
    $purchase_data = $subcontract_bill_data = $expense_bill_data = $client_invoice_data = array();
    $total_received = $total_payment = $op_balance = $total_balance = 0;
    $array = array();
    $report_type = 'all';
    if (isset($_POST['report_type']) && $_POST['report_type'] != '' && $_POST['report_type'] != 'all') {
      $report_type = $_POST['report_type'];
    }
    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));
      $purchase_data["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_data["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') >="] = $start_date;
      $expense_bill_data["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $expense_bill_data["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;
      $subcontract_bill_data["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $subcontract_bill_data["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;
      $client_invoice_data["STR_TO_DATE(i.invoice_date,'%Y-%m-%d') <="] = $end_date;
      $client_invoice_data["STR_TO_DATE(i.invoice_date,'%Y-%m-%d') >="] = $start_date;
    }
    if (isset($_POST['supplier']) && $_POST['supplier'] != '' && $_POST['supplier'] != 'all') {
      $purchase_data['s.supplier_id'] = $_POST['supplier'];
    }
    if (isset($_POST['location']) && $_POST['location'] != '' && $_POST['location'] != 'all') {
      $purchase_data['p.location_id'] = $_POST['location'];
      $location_data = $this->Stock->select_stock_locations('sl.work_id,sl.location_type',array("location_id"=>$_POST['location']));
      if($location_data->num_rows()==1 && $location_data->row()->location_type=='work_site'){
        $expense_bill_data['b.work_id'] = $location_data->row()->work_id;
        $subcontract_bill_data['b.work_id'] = $location_data->row()->work_id;
        $client_invoice_data['i.project_id'] = $location_data->row()->work_id;
      }
    }
    $tax_types = $this->Settings->select_tax_types('','','all')->result();
    $total_discount_amount = $total_round_amount = $total_payable_amount = 0;
    foreach($tax_types as $row3){
      ${'total_taxable_amount_'.$row3->tax_id} = ${'total_tax_amount_'.$row3->tax_id} = ${'total_tax_amount1_'.$row3->tax_id} = ${'total_tax_amount2_'.$row3->tax_id} = ${'total_tax_amount3_'.$row3->tax_id} = 0;
    }
    if($report_type == 'all' || $report_type == 'purchase'){
      $purchase_bills = $this->Purchases->select_purchase("p.*,u.user_alias,s.supplier_id,s.supplier_name,s.supplier_address,s.tax_no,sl.location_name,sl.work_id,sl.location_type,sl.work_id as location_work_id",$purchase_data)->result();
      foreach($purchase_bills as $row):
        $location_name = $row->location_name;
        if ($row->location_type == 'work_site') {
          $location_name = '<a href="' . base_url() . 'work/profile/' . $row->location_work_id . '">' . $row->location_name . '<br/>[Work Site]</a>';
        } elseif ($row->location_type == 'ware_house') {
          $location_name = '<a href="' . base_url() . 'stock/locations/profile/' . $row->location_id . '">' . $row->location_name . '<br/>[Warehouse]</a>';
        }
        $sl_no++;

        $purchased_material_where['p.purchase_id'] = $row->purchase_id;
        $purchased_materials = $this->Purchases->select_purchase_items("COALESCE(SUM(pi.total_amount),0) as total_amount,COALESCE(SUM(pi.tax_amount),0) as tax_amount,pi.tax_percent", $purchased_material_where, false,'pi.tax_percent')->result();
        $purchased_materials_array = array();
        foreach ($purchased_materials as $row2) {
          $purchased_materials_array[$row2->tax_percent]['total_amount'] = $row2->total_amount - $row2->tax_amount;
        }

        $array[$j][] = $sl_no;
        $array[$j][] = date('d-m-Y', strtotime($row->purchase_date));
        $array[$j][] = 'Material Purchase Bill';
        $array[$j][] = $location_name;
        $array[$j][] = '<a href="' . base_url() . 'supplier/profile/' . $row->supplier_id . '">' . $row->supplier_name . '</a>';
        $array[$j][] = $row->supplier_address;
        $array[$j][] = $row->tax_no;
        $array[$j][] = 'Material Purchase';
        $array[$j][] = '<a href="' . base_url() . 'purchases/profile/' . $row->purchase_id . '">' . $row->purchase_id . '</a>';


        foreach($tax_types as $row3){
          if($row3->tax1_percent==0 && $row3->tax2_percent==0 && $row3->tax3_percent==0){
            if(isset($purchased_materials_array[$row3->tax_id])){
              $array[$j][]=$this->config->item('currency_format')->formatCurrency($purchased_materials_array[$row3->tax_id]['total_amount'],$this->config->item('Currency_currency_shortname'));
              $array[$j][]=$this->config->item('currency_format')->formatCurrency($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax_percentage/100,$this->config->item('Currency_currency_shortname'));
              ${'total_taxable_amount_'.$row3->tax_id} += round($purchased_materials_array[$row3->tax_id]['total_amount'],$this->config->item('Currency_amount_decimals_2'));
              ${'total_tax_amount_'.$row3->tax_id} += round($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax_percentage/100,$this->config->item('Currency_amount_decimals_2'));
            }else{
              $array[$j][]='';
              $array[$j][]='';
            }
          }else{
            if(isset($purchased_materials_array[$row3->tax_id])){
              $array[$j][]=$purchased_materials_array[$row3->tax_id]['total_amount'];
              ${'total_taxable_amount_'.$row3->tax_id} += $purchased_materials_array[$row3->tax_id]['total_amount'];
              if($row3->tax1_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax1_percent/100,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount1_'.$row3->tax_id} += round($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax1_percent/100,$this->config->item('Currency_amount_decimals_2'));
              }
              if($row3->tax2_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax2_percent/100,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount2_'.$row3->tax_id} += round($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax2_percent/100,$this->config->item('Currency_amount_decimals_2'));
              }
              if($row3->tax3_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax3_percent/100,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount3_'.$row3->tax_id} += round($purchased_materials_array[$row3->tax_id]['total_amount']*$row3->tax3_percent/100,$this->config->item('Currency_amount_decimals_2'));
              }
            } else {
              $array[$j][] = '';
              if ($row3->tax1_percent != 0) {
                $array[$j][] = '';
              }
              if ($row3->tax2_percent != 0) {
                $array[$j][] = '';
              }
              if ($row3->tax3_percent != 0) {
                $array[$j][] = '';
              }
            }
          }
        }

        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_discount_amount,$this->config->item('Currency_currency_shortname'));
        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_round_amount,$this->config->item('Currency_currency_shortname'));
        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_payable_amount,$this->config->item('Currency_currency_shortname'));

        $total_discount_amount += $row->bill_discount_amount;
        $total_round_amount += $row->bill_round_amount;
        $total_payable_amount += $row->bill_payable_amount;

        $j++;

      endforeach;
    }
    if($report_type == 'all' || $report_type == 'expense'){
      $expense_bill_data['b.bill_type'] = 'expense';
      $expense_bills	= $this->Bills->select_bills("b.*,ah1.acc_head_name as expense_head_name,u.user_alias,wc.work_name,u.user_role,tt.tax_percentage,tt.tax1_percent,tt.tax2_percent,tt.tax3_percent,u.user_role as created_user_role_id",$expense_bill_data)->result();
      foreach ($expense_bills as $key => $row) {
        $sl_no++;
        $bill_taxable_amount = $row->bill_gross_amount;
        $row->bill_tax1_amount = round($row->bill_gross_amount*$row->tax1_percent/100,$this->config->item('Currency_amount_decimals_2'));
        $row->bill_tax2_amount = round($row->bill_gross_amount*$row->tax2_percent/100,$this->config->item('Currency_amount_decimals_2'));
        $row->bill_tax3_amount = round($row->bill_gross_amount*$row->tax3_percent/100,$this->config->item('Currency_amount_decimals_2'));

        $array[$j][] = $sl_no;
        $array[$j][] = date('d-m-Y', strtotime($row->bill_date));
        $array[$j][] = 'Expense Bill';
        $array[$j][] = $row->work_name;
        $array[$j][] = $row->bill_consignee_name;
        $array[$j][] = $row->bill_description;
        $array[$j][] = $row->bill_tax_no;
        $array[$j][] = 'Expense Bill';
        $array[$j][] = $row->bill_id;

        foreach($tax_types as $row3){
          if($row3->tax1_percent==0 && $row3->tax2_percent==0 && $row3->tax3_percent==0){
            if($row->bill_tax_percent==$row3->tax_id){
              $array[$j][]=$this->config->item('currency_format')->formatCurrency($bill_taxable_amount,$this->config->item('Currency_currency_shortname'));
              $array[$j][]=$this->config->item('currency_format')->formatCurrency($bill_taxable_amount*$row->tax_percentage/100,$this->config->item('Currency_currency_shortname'));
              ${'total_taxable_amount_'.$row3->tax_id} += $bill_taxable_amount;
              ${'total_tax_amount_'.$row3->tax_id} += round($bill_taxable_amount*$row->tax_percentage/100,$this->config->item('Currency_amount_decimals_2'));
            }else{
              $array[$j][]='';
              $array[$j][]='';
            }
          }else{
            if($row->bill_tax_percent==$row3->tax_id){
              $array[$j][]=$bill_taxable_amount;
              ${'total_taxable_amount_'.$row3->tax_id} += $bill_taxable_amount;
              if($row3->tax1_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->bill_tax1_amount,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount1_'.$row3->tax_id} += round($row->bill_tax1_amount,$this->config->item('Currency_amount_decimals_2'));
              }
              if($row3->tax2_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->bill_tax2_amount,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount2_'.$row3->tax_id} += round($row->bill_tax2_amount,$this->config->item('Currency_amount_decimals_2'));
              }
              if($row3->tax3_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->bill_tax3_amount,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount3_'.$row3->tax_id} += round($row->bill_tax3_amount,$this->config->item('Currency_amount_decimals_2'));
              }
            } else {
              $array[$j][] = '';
              if ($row3->tax1_percent != 0) {
                $array[$j][] = '';
              }
              if ($row3->tax2_percent != 0) {
                $array[$j][] = '';
              }
              if ($row3->tax3_percent != 0) {
                $array[$j][] = '';
              }
            }
          }
        }

        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_discount_amount,$this->config->item('Currency_currency_shortname'));
        $array[$j][] = 0;
        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_payable_amount,$this->config->item('Currency_currency_shortname'));

        $total_discount_amount += $row->bill_discount_amount;
        $total_round_amount += 0;
        $total_payable_amount += $row->bill_payable_amount;

        $j++;
      }
    }
    if($report_type == 'all' || $report_type == 'subcontract'){
      $subcontract_bill_data['b.bill_type'] = 'subcontract';
      $subcontract_bills	= $this->Bills->select_bills("b.*,c.contractor_name,c.tax_no,ah1.acc_head_name as credtit_account,u.user_alias,wc.work_name,sc.contractor,sc.sub_work_name,sc.contract_id,tt.tax_percentage,tt.tax1_percent,tt.tax2_percent,tt.tax3_percent,u.user_role as created_user_role_id,wc.branch_id",$subcontract_bill_data)->result();
      foreach ($subcontract_bills as $key => $row) {
        $sl_no++;
        $bill_taxable_amount = $row->bill_gross_amount;
        $row->bill_tax1_amount = round($row->bill_gross_amount*$row->tax1_percent/100,$this->config->item('Currency_amount_decimals_2'));
        $row->bill_tax2_amount = round($row->bill_gross_amount*$row->tax2_percent/100,$this->config->item('Currency_amount_decimals_2'));
        $row->bill_tax3_amount = round($row->bill_gross_amount*$row->tax3_percent/100,$this->config->item('Currency_amount_decimals_2'));

        $array[$j][] = $sl_no;
        $array[$j][] = date('d-m-Y', strtotime($row->bill_date));
        $array[$j][] = 'Subcontract Bill';
        $array[$j][] = $row->work_name;
        $array[$j][] = $row->contractor_name;
        $array[$j][] = $row->sub_work_name;
        $array[$j][] = $row->tax_no;
        $array[$j][] = 'Subcontract Bill';
        $array[$j][] = $row->bill_id;

        foreach($tax_types as $row3){
          if($row3->tax1_percent==0 && $row3->tax2_percent==0 && $row3->tax3_percent==0){
            if($row->bill_tax_percent==$row3->tax_id){
              $array[$j][]=$this->config->item('currency_format')->formatCurrency($bill_taxable_amount,$this->config->item('Currency_currency_shortname'));
              $array[$j][]=$this->config->item('currency_format')->formatCurrency($bill_taxable_amount*$row->tax_percentage/100,$this->config->item('Currency_currency_shortname'));
              ${'total_taxable_amount_'.$row3->tax_id} += $bill_taxable_amount;
              ${'total_tax_amount_'.$row3->tax_id} += round($bill_taxable_amount*$row->tax_percentage/100,$this->config->item('Currency_amount_decimals_2'));
            }else{
              $array[$j][]='';
              $array[$j][]='';
            }
          }else{
            if($row->bill_tax_percent==$row3->tax_id){
              $array[$j][]=$bill_taxable_amount;
              ${'total_taxable_amount_'.$row3->tax_id} += $bill_taxable_amount;
              if($row3->tax1_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->bill_tax1_amount,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount1_'.$row3->tax_id} += round($row->bill_tax1_amount,$this->config->item('Currency_amount_decimals_2'));
              }
              if($row3->tax2_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->bill_tax2_amount,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount2_'.$row3->tax_id} += round($row->bill_tax2_amount,$this->config->item('Currency_amount_decimals_2'));
              }
              if($row3->tax3_percent!=0){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->bill_tax3_amount,$this->config->item('Currency_currency_shortname'));
                ${'total_tax_amount3_'.$row3->tax_id} += round($row->bill_tax3_amount,$this->config->item('Currency_amount_decimals_2'));
              }
            } else {
              $array[$j][] = '';
              if ($row3->tax1_percent != 0) {
                $array[$j][] = '';
              }
              if ($row3->tax2_percent != 0) {
                $array[$j][] = '';
              }
              if ($row3->tax3_percent != 0) {
                $array[$j][] = '';
              }
            }
          }
        }

        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_discount_amount,$this->config->item('Currency_currency_shortname'));
        $array[$j][] = 0;
        $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->bill_payable_amount,$this->config->item('Currency_currency_shortname'));

        $total_discount_amount += $row->bill_discount_amount;
        $total_round_amount += 0;
        $total_payable_amount += $row->bill_payable_amount;

        $j++;
      }
    }
    if($report_type == 'all' || $report_type == 'client_invoice'){
      $client_invoice_data['i.invoice_tax_amount >'] = 0;
      $invoices	= $this->Invoices->select_invoices("i.*,wc.lpo_no,wc.work_name,client_name,client_address,client_email,client_contact,c.tax_no,tt.tax_percentage,tt.tax1_percent,tt.tax2_percent,tt.tax3_percent,u1.user_id as i_created_by,u1.user_alias as i_created,v.vehicle_no,ba.*",$client_invoice_data)->result();
      foreach ($invoices as $key => $row) {
        $invoice_no = '';
        if($row->invoice_type=='proforma'){
          $invoice_no .= $this->config->item('client_invoice_proforma_prefix');
        }else{
        if($row->tax_invoice==1){
          if($row->business_type=='B2B'){
            $invoice_no .= $this->config->item('client_invoice_tax_b2b_prefix');
          }else{
            $invoice_no .= $this->config->item('client_invoice_tax_b2c_prefix');
          }
        }else{
          $invoice_no .= $this->config->item('client_invoice_prefix');
        }
      }
        $invoice_no .= sprintf('%03u', $row->invoice_no);
        $invoice_no .= $row->invoice_suffix;

        $sl_no++;
        $array[$j][] = $sl_no;
        $array[$j][] = date('d-m-Y', strtotime($row->invoice_date));
        $array[$j][] = 'Client Invoice';
        $array[$j][] = $row->work_name;
        $array[$j][] = $row->client_name;
        $array[$j][] = $row->client_address;
        $array[$j][] = $row->tax_no;
        $array[$j][] = 'Client Invoice';
        $array[$j][] = $invoice_no;

        if($row->invoice_tax_percent!=0){
          $invoice_taxable_amount = $row->invoice_taxable_amount;
          $row->invoice_tax1_amount = round($row->invoice_taxable_amount*$row->tax1_percent/100,$this->config->item('Currency_amount_decimals_2'));
          $row->invoice_tax2_amount = round($row->invoice_taxable_amount*$row->tax2_percent/100,$this->config->item('Currency_amount_decimals_2'));
          $row->invoice_tax3_amount = round($row->invoice_taxable_amount*$row->tax3_percent/100,$this->config->item('Currency_amount_decimals_2'));
          foreach($tax_types as $row3){
            if($row3->tax1_percent==0 && $row3->tax2_percent==0 && $row3->tax3_percent==0){
              if($row->invoice_tax_percent==$row3->tax_id){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_taxable_amount,$this->config->item('Currency_currency_shortname'));
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_taxable_amount*$row->tax_percentage/100,$this->config->item('Currency_currency_shortname'));
                ${'total_taxable_amount_'.$row3->tax_id} += $invoice_taxable_amount;
                ${'total_tax_amount_'.$row3->tax_id} += round($invoice_taxable_amount*$row->tax_percentage/100,$this->config->item('Currency_amount_decimals_2'));
              }else{
                $array[$j][]='';
                $array[$j][]='';
              }
            }else{
              if($row->invoice_tax_percent==$row3->tax_id){
                $array[$j][]=$invoice_taxable_amount;
                ${'total_taxable_amount_'.$row3->tax_id} += $invoice_taxable_amount;
                if($row3->tax1_percent!=0){
                  $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->invoice_tax1_amount,$this->config->item('Currency_currency_shortname'));
                  ${'total_tax_amount1_'.$row3->tax_id} += round($row->invoice_tax1_amount,$this->config->item('Currency_amount_decimals_2'));
                }
                if($row3->tax2_percent!=0){
                  $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->invoice_tax2_amount,$this->config->item('Currency_currency_shortname'));
                  ${'total_tax_amount2_'.$row3->tax_id} += round($row->invoice_tax2_amount,$this->config->item('Currency_amount_decimals_2'));
                }
                if($row3->tax3_percent!=0){
                  $array[$j][]=$this->config->item('currency_format')->formatCurrency($row->invoice_tax3_amount,$this->config->item('Currency_currency_shortname'));
                  ${'total_tax_amount3_'.$row3->tax_id} += round($row->invoice_tax3_amount,$this->config->item('Currency_amount_decimals_2'));
                }
              } else {
                $array[$j][] = '';
                if ($row3->tax1_percent != 0) {
                  $array[$j][] = '';
                }
                if ($row3->tax2_percent != 0) {
                  $array[$j][] = '';
                }
                if ($row3->tax3_percent != 0) {
                  $array[$j][] = '';
                }
              }
            }
          }

          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->invoice_discount_amount,$this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->invoice_round_amount,$this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->invoice_total_amount,$this->config->item('Currency_currency_shortname'));

          $total_discount_amount += $row->invoice_discount_amount;
          $total_round_amount += $row->invoice_round_amount;
          $total_payable_amount += $row->invoice_total_amount;

          $j++;
        }else{
          // get invoice items have tax
          $invoice_items = $this->Invoices->select_invoice_items("COALESCE(SUM(ii.item_taxable_amount),0) as item_taxable_amount,COALESCE(SUM(ii.item_tax_amount),0) as item_tax_amount,COALESCE(SUM(ii.total_amount),0) as total_amount,ii.item_tax_percent",array("ii.invoice_id"=>$row->invoice_id,"ii.item_tax_percent !="=>0),'','ii.item_tax_percent')->result();
          $invoice_items_array = array();
          foreach ($invoice_items as $row2) {
            $invoice_items_array[$row2->item_tax_percent]['taxable_amount'] = $row2->item_taxable_amount;
          }

          foreach($tax_types as $row3){
            if($row3->tax1_percent==0 && $row3->tax2_percent==0 && $row3->tax3_percent==0){
              if(isset($invoice_items_array[$row3->tax_id])){
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_items_array[$row3->tax_id]['taxable_amount'],$this->config->item('Currency_currency_shortname'));
                $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax_percentage/100,$this->config->item('Currency_currency_shortname'));
                ${'total_taxable_amount_'.$row3->tax_id} += $invoice_items_array[$row3->tax_id]['taxable_amount'];
                ${'total_tax_amount_'.$row3->tax_id} += round($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax_percentage/100,$this->config->item('Currency_amount_decimals_2'));
              }else{
                $array[$j][]='';
                $array[$j][]='';
              }
            }else{
              if(isset($invoice_items_array[$row3->tax_id])){
                $array[$j][]=$invoice_items_array[$row3->tax_id]['taxable_amount'];
                ${'total_taxable_amount_'.$row3->tax_id} += $invoice_items_array[$row3->tax_id]['taxable_amount'];
                if($row3->tax1_percent!=0){
                  $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax1_percent/100,$this->config->item('Currency_currency_shortname'));
                  ${'total_tax_amount1_'.$row3->tax_id} += round($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax1_percent/100,$this->config->item('Currency_amount_decimals_2'));
                }
                if($row3->tax2_percent!=0){
                  $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax2_percent/100,$this->config->item('Currency_currency_shortname'));
                  ${'total_tax_amount2_'.$row3->tax_id} += round($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax2_percent/100,$this->config->item('Currency_amount_decimals_2'));
                }
                if($row3->tax3_percent!=0){
                  $array[$j][]=$this->config->item('currency_format')->formatCurrency($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax3_percent/100,$this->config->item('Currency_currency_shortname'));
                  ${'total_tax_amount3_'.$row3->tax_id} += round($invoice_items_array[$row3->tax_id]['taxable_amount']*$row3->tax3_percent/100,$this->config->item('Currency_amount_decimals_2'));
                }
              } else {
                $array[$j][] = '';
                if ($row3->tax1_percent != 0) {
                  $array[$j][] = '';
                }
                if ($row3->tax2_percent != 0) {
                  $array[$j][] = '';
                }
                if ($row3->tax3_percent != 0) {
                  $array[$j][] = '';
                }
              }
            }
          }

          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->invoice_discount_amount,$this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->invoice_round_amount,$this->config->item('Currency_currency_shortname'));
          $array[$j][] = $this->config->item('currency_format')->formatCurrency($row->invoice_total_amount,$this->config->item('Currency_currency_shortname'));

          $total_discount_amount += $row->invoice_discount_amount;
          $total_round_amount += $row->invoice_round_amount;
          $total_payable_amount += $row->invoice_total_amount;

          $j++;
        }
      }
    }
    if(sizeof($array)>0){
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]='';
      $array[$j][]="<strong>Total</strong>";
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][]='';
      $array[$j][]='';

      foreach($tax_types as $row){
        $array[$j][] = "<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(${'total_taxable_amount_'.$row->tax_id},$this->config->item('Currency_currency_shortname'))."</strong>";
        if($row->tax1_percent==0 && $row->tax2_percent==0 && $row->tax3_percent==0){
          $array[$j][] = "<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(${'total_tax_amount_'.$row->tax_id},$this->config->item('Currency_currency_shortname'))."</strong>";
        }else{
          if($row->tax1_percent!=0){
            $array[$j][] = "<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(${'total_tax_amount1_'.$row->tax_id},$this->config->item('Currency_currency_shortname'))."</strong>";
          }
          if($row->tax2_percent!=0){
            $array[$j][] = "<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(${'total_tax_amount2_'.$row->tax_id},$this->config->item('Currency_currency_shortname'))."</strong>";
          }
          if($row->tax3_percent!=0){
            $array[$j][] = "<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency(${'total_tax_amount3_'.$row->tax_id},$this->config->item('Currency_currency_shortname'))."</strong>";
          }
        }
      }
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_discount_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_round_amount,$this->config->item('Currency_currency_shortname'))."</strong>";
      $array[$j][]="<strong>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($total_payable_amount,$this->config->item('Currency_currency_shortname'))."</strong>";

    }

    $json_data['data']=$array;
    $json_data['draw']=5;
    $json_data['recordsTotal']=sizeof($array);
    $json_data['recordsFiltered']=sizeof($array);

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

  public function select_project_task_report()
  {
    $json_data = array();
    $table_body = "";
    $j = 0;
    $data = array();
    // $total_received = $total_payment = $op_balance = $total_balance = 0;

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));
      // of period 1 day
      $interval = new DateInterval('P1D');
      $realEnd = new DateTime($end_date);
      $realEnd->add($interval);
      $date_period = new DatePeriod(new DateTime($start_date), $interval, $realEnd);

      $array = array();
      $sl_no = 1;

      $activities_create_where = $task_not_started_where = $task_completed_where = $task_no_progress_where = $task_due_where = array();
      if ($this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'auditor' && $this->session->userdata('user_role') != 'madmin') {
        $task_report_where['pta.created_by'] =  $this->session->userdata('user_id');
      }
      $work_id = '';
      if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
        $work_id = $task_report_where['wc.work_id'] = $task_not_started_where['wc.work_id'] = $task_completed_where['wc.work_id'] = $task_create_where['wc.work_id'] = $task_progress_where['wc.work_id'] = $_POST['work'];
      }
      foreach ($date_period as $key => $value) {
        $date = date('d F Y', strtotime($value->format('Y-m-d')));

        //Task Not started
        $task_not_started_where['pt.planned_start_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $task_not_started_where['pt.actual_start_date'] = '0000-00-00';
        $tasks = $this->project_tasks->select_tasks("pt.*,wc.work_name,u1.user_alias,u2.user_alias as assigned_to", $task_not_started_where)->result();

        foreach ($tasks as $key => $row) {
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Task Pending : <strong>" . $row->task_title . "</strong></td><td>" . $row->assigned_to . "</td><td>" . $row->work_name . "</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }

        //Task Due
        $task_due_where['pt.planned_end_date <'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $tasks = $this->project_tasks->select_tasks("pt.*,wc.work_name,u1.user_alias,u2.user_alias as assigned_to", $task_due_where)->result();

        foreach ($tasks as $key => $row) {
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Task Due : <strong><span class='text-danger'>" . $row->task_title . "</span></strong></td><td>" . $row->assigned_to . "</td><td>" . $row->work_name . "</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }

        //Task Completed
        $task_completed_where['DATE(pt.updated_date)'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $task_completed_where['pt.progress'] = '100';
        $tasks_completed = $this->project_tasks->select_tasks("pt.*,u1.user_alias,wc.work_name,u2.user_alias as assigned_to", $task_completed_where)->result();

        foreach ($tasks_completed as $key => $row) {
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Task Completed : <strong>" . $row->task_title . "</strong></td><td>" . $row->assigned_to . "</td><td>" . $row->work_name . "</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }

        //Task created
        $task_create_where['DATE(pt.created_date)'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $tasks_created = $this->project_tasks->select_tasks("pt.*,u1.user_alias,wc.work_name,u2.user_alias as assigned_to", $task_create_where)->result();
        foreach ($tasks_created as $key => $row) {
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Task Created : <strong>" . $row->task_title . "</strong></td><td>" . $row->assigned_to . "</td><td>" . $row->work_name . "</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }

        // Progress Update
        $task_progress_where['DATE(ppl.created_date)'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $tasks_progress = $this->project_tasks->select_project_task_progress_log("ppl.*,pt.task_title,u1.user_alias", $task_progress_where)->result();
        $font_type = '';
        foreach ($tasks_progress as $key => $row) {
          if ($row->variation > 0) {
            $font_type = 'text-success';
          } elseif ($row->variation < 0) {
            $font_type = 'text-danger';
          }
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Task Progress Update : <strong>" . $row->task_title . "</strong> From <strong>" . $row->old_value . "%</strong> to <strong>" . $row->new_value . "% [ <span class=" . $font_type . "> " . $row->variation . "% </span>]</strong></td><td>#assigned_to</td><td>#work</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }

        //Tasks not progressed
        $task_no_progress_where["STR_TO_DATE(pt.planned_start_date,'%Y-%m-%d') <="] = $task_no_progress_where["STR_TO_DATE(pt.planned_end_date,'%Y-%m-%d') >="] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $task_no_progress_where['progress'] = 0;
        $tasks = $this->project_tasks->select_tasks("pt.*,u1.user_alias,wc.work_name,u2.user_alias as assigned_to", $task_no_progress_where)->result();

        foreach ($tasks as $key => $row) {
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Task Progress Update : <strong>" . $row->task_title . " [ <span class='text-danger'>0% updated</span> ]</strong></td>" . $row->assigned_to . "<td></td><td>" . $row->work_name . "</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }
      }
    }

    $json_data['table_body'] = $table_body;
    if ($table_body != '') {
      $json_data['count'] = 1;
    }

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

  public function select_project_task_activity_report()
  {
    $json_data = array();
    $table_body = "";
    $j = 0;
    $data = array();
    // $total_received = $total_payment = $op_balance = $total_balance = 0;

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));
      // of period 1 day
      $interval = new DateInterval('P1D');
      $realEnd = new DateTime($end_date);
      $realEnd->add($interval);
      $date_period = new DatePeriod(new DateTime($start_date), $interval, $realEnd);

      $array = array();
      $sl_no = 1;

      $activities_create_where = array();
      if ($this->session->userdata('user_role') != 'auditor' && $this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'madmin') {
        $activity_report_where['pta.created_by'] =  $this->session->userdata('user_id');
      }
      $work_id = '';
      if (isset($_POST['work']) && $_POST['work'] != '' && $_POST['work'] != 'all') {
        $work_id = $task_report_where['wc.work_id'] = $activities_create_where['wc.work_id'] = $_POST['work'];
      }

      foreach ($date_period as $key => $value) {

        $date = date('d F Y', strtotime($value->format('Y-m-d')));

        //activities created
        $activities_create_where['pta.activity_date'] = date('Y-m-d', strtotime($value->format('Y-m-d')));
        $activities = $this->project_tasks->select_task_activity("pta.*,pt.task_title,u1.user_alias,wc.work_name", $activities_create_where)->result();
        foreach ($activities as $key => $row) {
          $table_body .= "<tr><td>" . ($j + 1) . "</td><td>" . $date . "</td><td>Activity Created : <strong>" . $row->activity_title . "</strong></td><td><strong>" . $row->task_title . "</strong></td><td>" . $row->work_name . "</td><td>" . $row->user_alias . "</td></tr>";
          $j++;
        }

        // // Progress Update
        // $task_progress_where['pt.created_date'] = date('Y-m-d',strtotime($value->format('Y-m-d')));
        // $tasks = $this->project_tasks->select_project_task_log("ppl.*,pt.task_title,u1.user_alias",$task_create_where)->result();
        // $font_type = '';
        // foreach ($tasks as $key => $row) {
        //   if($row->variation>0){
        //     $font_type = 'text-success';
        //   }
        //   elseif($row->variation<0){
        //     $font_type = 'text-danger';
        //   }
        //   $table_body.="<tr><td>".($j+1)."</td><td>".$date."</td><td>Task Progress Update : <strong>".$row->task_title."</strong> From <strong>".$row->old_value."%</strong> to <strong>".$row->new_value."% [ <span class=".$font_type."> ".$row->variation."% </span>]</strong></td><td>".$row->user_alias."</td></tr>";
        //   $j++;
        // }


      }
    }

    $json_data['table_body'] = $table_body;
    if ($table_body != '') {
      $json_data['count'] = 1;
    }

    echo json_encode($json_data);  // send data as json format
  }
  public function select_trial_balance_old()
  {

    $json_data = array();
    $table_body = "";
    $j = 0;
    $data = array();
    $total_debit = $total_credit = 0;

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $end_date = date('Y-m-d', strtotime($_POST['date']));

      $sl_no = 1;
      $supplier_op_balance_payment_where = $supplier_advance_payment_where = $employee_payment_where = $user_commission_payment_where = $tool_payment_where = $tool_purchase_where = $user_payment_return_where = $user_payment_receipt_where = $loan_receipt_where = $loan_payment_where = $salary_payment_where = $labour_wage_payment_where = $client_payment_where = $other_expense_where = $office_expense_where = $purchase_order_payment_where = $purchase_payment_where = $purchase_bill_where = $subcontract_payment_where = $user_payment_released_where = array();
      // if ($this->session->userdata('user_role') != 'accounts_manager' && $this->session->userdata('user_role') != 'general_manager' && $this->session->userdata('user_role') != 'auditor' && $this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'madmin') {
      //   $sales_invoice_receipt_where['ats.created_by'] = $employee_payment_where['ats.created_by'] = $user_commission_payment_where['ats.created_by'] = $tool_payment_where['ats.created_by'] = $tool_purchase_where['ats.created_by'] = $loan_payment_where['ats.created_by'] = $other_income_where['ats.created_by'] = $salary_payment_where['ats.created_by'] = $vehicle_payment_where['ats.created_by'] = $labour_wage_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $office_expense_where['ats.created_by'] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_payment_where['ats.created_by'] = $subcontract_payment_where['ats.created_by'] = $user_payment_receipt_where['ats.created_by'] = $user_payment_return_where['ats.created_by'] = $this->session->userdata('user_id');
      // }

      // Select Cash & Bank Accounts
      if ($this->session->userdata('user_role') == 'accounts_manager' || $this->session->userdata('user_role') != 'general_manager' || $this->session->userdata('user_role') != 'auditor' || $this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
        // $cash_account_where="(ah.account_type=32 or ah.account_type=36) and acc_user_id!=0";
        $cash_account_where = "ah.account_type=32 or ah.account_type=36";
      } else {
        $cash_account_where = "ah.account_type=32 and acc_user_id=" . $this->session->userdata('user_id');
      }
      $cash_accounts  = $this->Account->select_account_heads("acc_head_id,acc_head_name,acc_user_id", $cash_account_where, "", "asc")->result();
      foreach ($cash_accounts as $key => $value) {
        if ($value->acc_user_id != 0) {
          $account_type = 'user_accounts';
        } else {
          $account_type = 'company_accounts';
        }
        $cash_balance_amount = $this->accounts_management->calculate_trial_balance($value->acc_head_id, '', $account_type,'',$this->session->userdata('user_role'),$this->session->userdata('user_id'));
        $table_body .= "<tr><td>" . $value->acc_head_name . "</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($cash_balance_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
        $total_debit += $cash_balance_amount;
      }




      // Client Payment Receipts
      $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
      $client_payment_where['ats.reference_type'] = 'project';
      // $client_payment_where['ats.tr_date <='] = $end_date;
      $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'));
      $table_body .= "<tr><td>Client Payment Receipts</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($client_payments_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $client_payments_received;
      // Sales Invoice Receipts
      $sales_invoice_receipt_where['ats.tr_type'] = 'sales_invoice_transaction';
      $sales_invoice_receipt_where['ats.reference_type'] = 'sales_invoice';
      // $sales_invoice_receipt_where['ats.tr_date <='] = $end_date;
      $sales_invoice_received = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $sales_invoice_receipt_where, 'sales_invoice')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Ware House Sales Invoice Receipts</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($sales_invoice_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $sales_invoice_received;
      // Other Income Receipts
      $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
      $this->db->where("(ats.reference_type='income_receipt' or (ats.reference_type='client' and ats.invoice_id!=0))");
      // $other_income_where['ats.tr_date <='] = $end_date;
      $other_income_received = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $other_income_where, 'income_receipt')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Other Income Receipts</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($other_income_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $other_income_received;
      // Loan Receipts on Payable Loan
      $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
      $loan_receipt_where['ats.reference_type'] = 'loan';
      // $loan_receipt_where['ats.tr_date <='] = $end_date;
      $loan_receipt_where['l.loan_type'] = 'payable';
      $loan_received = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_receipt_where, 'loan')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Loans Received (Payable Loan)</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($loan_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $loan_received;

      // Loan Receipts on Receivable Loan
      $loan_receipt_where2 = $loan_receipt_where;
      $loan_receipt_where2['ats.tr_type'] = 'loan_receipt_transaction';
      $loan_receipt_where2['ats.reference_type'] = 'loan';
      // $loan_receipt_where2['ats.tr_date <='] = $end_date;
      $loan_receipt_where2['l.loan_type'] = 'receivable';
      $loan_received2 = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_receipt_where2, 'loan')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Loans Payments Received (on Receivable Loan)</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($loan_received2, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $loan_received2;

      // Purchase Order Payment
      // $purchase_order_payment_where["po.purchase_bill_id"]= 0;
      // $purchase_order_payment_where['ats.tr_date <='] = $end_date;
      $purchase_order_payment_where['ats.tr_type'] = 'purchase_order_transaction';
      $purchase_order_payment_where['ats.reference_type'] = 'purchase_order';
      $purchase_order_payments = 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'));
      if ($this->config->item('app_module_purchase_order') == 1) {
        $table_body .= "<tr><td>Purchase Order Payments (Advance)</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($purchase_order_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      }
      $total_debit += $purchase_order_payments;
      // Purchase Bill Payment
      // $purchase_payment_where['ats.tr_date <='] = $end_date;
      $purchase_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
      $purchase_payment_where['ats.reference_type'] = 'purchase_bill';
      $purchase_bill_payments = 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'));
      $table_body .= "<tr><td>Purchase Bill Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($purchase_bill_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $purchase_bill_payments;
      // Labour Wage Payment
      // $labour_wage_payment_where['ats.tr_date <='] = $end_date;
      $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
      $labour_wage_payment_where['ats.reference_type'] = 'labour';
      $labour_wage_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $labour_wage_payment_where, 'labour')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Labour Wage Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($labour_wage_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $labour_wage_payments;
      // Salary Payment
      // $salary_payment_where['ats.tr_date <='] = $end_date;
      $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
      $salary_payment_where['ats.reference_type'] = 'salary_slip';
      $salary_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $salary_payment_where, 'salary_slip')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Salary Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($salary_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $salary_payments;
      // Employee Payment
      // $employee_payment_where['ats.tr_date <='] = $end_date;
      $employee_payment_where['ats.reference_type'] = 'employee';
      $this->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction' or ats.tr_type='employee_bata_transaction')");
      $employee_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $employee_payment_where, 'employee')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Salary Advance / Opening Balance Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($employee_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $employee_payments;
      // Vehicle Payment
      // $vehicle_payment_where['ats.tr_date <='] = $end_date;
      $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
      $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
      $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'));
      $table_body .= "<tr><td>Vehicle Work Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($vehicle_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $vehicle_payments;
      // Subcontract Payment
      // $subcontract_payment_where['ats.tr_date <='] = $end_date;
      $this->db->where("(ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction')");
      $this->db->where("(ats.reference_type='subcontract_bill' or ats.reference_type='subcontract')");
      $sub_contract_payments = 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'));
      $table_body .= "<tr><td>Subcontract Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($sub_contract_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $sub_contract_payments;
      // Select Expense
      // $other_expense_where['ats.tr_date <='] = $end_date;
      $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
      $other_expense_where['ats.reference_type'] = 'expense_bill';
      $expense_accounts  = $this->Account->select_account_heads("acc_head_id,acc_head_name", "ah.account_type=11")->result();
      foreach ($expense_accounts as $key => $value) {
        $other_expense_where['ats.tr_credit_acc'] = $value->acc_head_id;
        $expense_payments = 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'));
        $table_body .= "<tr><td>Expense: " . $value->acc_head_name . "</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($expense_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
        $total_debit += $expense_payments;
      }
      // // User Payment Receipts
      // $user_payment_receipt_where['ats.tr_date <='] = $end_date;
      // $user_payment_receipt_where['ats.reference_type'] = 'user_account_head';
      // $user_payment_receipt_where['ats.tr_type'] = 'user_payment_receipt_transaction';
      // $user_payment_receipts = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_receipt_where,'user_payment_receipt')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body.="<tr><td>User Account Payment Receipts</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($user_payment_receipts,$this->config->item('Currency_currency_shortname'))."</td><td></td></tr>";
      // $total_debit+=$user_payment_receipts;
      // // User Payment Returns
      // $user_payment_return_where['ats.tr_date <='] = $end_date;
      // $user_payment_return_where['ats.reference_type'] = 'user_account_head';
      // $user_payment_return_where['ats.tr_type'] = 'user_payment_return_transaction';
      // $user_payment_returned = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_return_where,'user_payment_return')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body.="<tr><td>User Account Payment Returns</td><td></td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($user_payment_returned,$this->config->item('Currency_currency_shortname'))."</td></tr>";
      // $total_credit+=$user_payment_returned;
      // Loan Payment
      // $loan_payment_where['ats.tr_date <='] = $end_date;
      $loan_payment_where['ats.reference_type'] = 'loan';
      $loan_payment_where['ats.tr_type'] = 'loan_transaction';
      $loan_payment_where['l.loan_type'] = 'payable';
      $loan_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_payment_where, 'loan')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Loan Payments on Payable Loan</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($loan_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $loan_payments;
      // Tool Purchase
      // $tool_purchase_where['ats.tr_date <='] = $end_date;
      $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
      $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
      $tool_purchase = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_purchase_where, 'tool_purchase')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Tool Purchase</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($tool_purchase, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $tool_purchase;

      // Tool Payments
      // $tool_payment_where['ats.tr_date <='] = $end_date;
      $tool_payment_where['ats.tr_type'] = 'tool_payment_transaction';
      $this->db->where('(ats.reference_type="tool_transfer" or ats.reference_type="tool_rent")');
      $tool_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_payment_where, 'tool')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Tool Payments (Rent & Own)</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($tool_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $tool_payments;

      // User Commission Payment
      // $user_commission_payment_where['ats.tr_date <='] = $end_date;
      $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
      $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
      $user_commission_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_commission_payment_where, 'user_commission')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>User Project Commission Payment</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($user_commission_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $tool_payments;

      // Supplier Op Balance
      // $supplier_op_balance_payment_where['ats.tr_date <='] = $end_date;
      $supplier_op_balance_payment_where['ats.tr_type'] = 'supplier_opbalance_transaction';
      $supplier_op_balance_payment_where['ats.reference_type'] = 'supplier';
      $supplier_op_balance_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $supplier_op_balance_payment_where, 'supplier')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Supplier OP Balance Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($supplier_op_balance_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $supplier_op_balance_payments;

      // Supplier Op Balance
      // $supplier_advance_payment_where['ats.tr_date <='] = $end_date;
      $supplier_advance_payment_where['ats.tr_type'] = 'supplier_advance_transaction';
      $supplier_advance_payment_where['ats.reference_type'] = 'supplier';
      $supplier_advance_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $supplier_advance_payment_where, 'supplier')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Supplier Advance Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($supplier_advance_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $supplier_advance_payments;

      if ($this->session->userdata('user_role') == 'accounts_manager' || $this->session->userdata('user_role') == 'general_manager' || $this->session->userdata('user_role') == 'auditor' || $this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
        // Select Payable Account
        // 1) Labour Wages
        $total_wage = round($this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.total_wage),0) as total", "")->row()->total,$this->config->item('Currency_amount_decimals_2'));
        $total_labour_op_balance = round($this->Labour->select_labours_2("COALESCE(SUM(l.opening_balance),0) as total", "")->row()->total,$this->config->item('Currency_amount_decimals_2'));
        $total_labour_wage_balance = ($total_wage + $total_labour_op_balance - $labour_wage_payments);
        $table_body .= "<tr><td>Payable Labour Wage</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_labour_wage_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_labour_wage_balance;

        // 2) Purchase Bills
        $total_purchase_bill_balance = round($this->Purchases->select_purchase("COALESCE(SUM(p.balance_amount),0) as total_amount", "")->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $table_body .= "<tr><td>Payable Purchase Bill</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_purchase_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_purchase_bill_balance;

        // 3) Salary
        $total_salary = round($this->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total", "")->row()->total,$this->config->item('Currency_amount_decimals_2'));
        // $total_salary_op_balance = $this->Labour->select_labours("COALESCE(SUM(l.opening_balance),0) as total","")->row()->total;
        $total_salary_balance = ($total_salary - $salary_payments);
        $table_body .= "<tr><td>Payable Salary</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_salary_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_salary_balance;

        // 4) Expense Bills
        $total_expense_bill_balance  = round($this->Bills->select_bills("COALESCE(SUM(bill_balance_amount),0) as total_amount", array("b.bill_type" => 'expense'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $table_body .= "<tr><td>Payable Expense Bill</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_expense_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_expense_bill_balance;

        // 5) Vehicle Payment
        $total_vehicle_bill_balance  = round($this->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(balance),0) as total_amount", array())->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $table_body .= "<tr><td>Payable Vehicle Work Payments</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_vehicle_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_vehicle_bill_balance;

        //6) Subcontract Payments
        $total_subcontract_bill_balance  = round($this->Bills->select_bills("COALESCE(SUM(bill_balance_amount),0) as total_amount", array("b.bill_type" => 'subcontract'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $table_body .= "<tr><td>Payable Subcontract Bill</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_subcontract_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_subcontract_bill_balance;

        //7) Loan Payments
        // $total_payable_loan = round($this->Loan->select_loan("COALESCE(SUM(current_balance_payable),0) as total_amount", array("l.loan_type" => 'payable'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        // $total_balance_to_pay_loan = $total_payable_loan - $loan_payments;
        // $table_body .= "<tr><td>Payable Loan</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance_to_pay_loan, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        // $total_credit += $total_balance_to_pay_loan;

        //8) Supplier Op Balance Payable
        $total_supplier_op_balance  = round($this->Account->select_account_heads("COALESCE(SUM(ah.op_balance),0) as total_amount", array("acc_supplier_id !=" => 0))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $total_supplier_opening_balance_paid = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("ats.tr_type" => 'supplier_opbalance_transaction', "ats.reference_type" => 'supplier'), 'supplier')->row()->total_amount;
        $total_supplier_op_balance_payable = $total_supplier_op_balance - $total_supplier_opening_balance_paid;
        $table_body .= "<tr><td>Payable Supplier OP Balance</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_supplier_op_balance_payable, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_supplier_op_balance_payable;

        // Select Supplier Accounts
        // $this->db->where("acc_supplier_id !=",0);
        // $supplier_accounts	= $this->Account->select_account_heads("op_balance,acc_head_id,acc_head_name,acc_supplier_id",'',"","asc")->result();
        // foreach ($supplier_accounts as $key => $value) {
        //   $supplier_opening_balance_paid = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",array("s.supplier_id" => $value->acc_supplier_id,"ats.tr_type" => 'supplier_opbalance_transaction',"ats.reference_type" => 'supplier'),'supplier')->row()->total_amount;
        //   // $supplier_advance_paid = $this->supplier_management->calculate_advance_amount($value->acc_supplier_id)['advance_paid'];
        //   $supplier_balance = $value->op_balance-$supplier_opening_balance_paid;
        //   $table_body.="<tr><td>".str_replace("'s Account'",'',$value->acc_head_name)." [Supplier - OP Balance]</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($supplier_balance,$this->config->item('Currency_currency_shortname'))."</td><td></td></tr>";
        //   $total_debit+=$supplier_balance;
        // }


        // Receivable Loan
        $total_receivable_loan = round($this->Loan->select_loan("COALESCE(SUM(current_balance_payable),0) as total_amount", array("l.loan_type" => 'receivable'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        // $total_balance_to_receive_loan = $total_receivable_loan - $loan_received2;
        $table_body .= "<tr><td>Receivable Loan</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_receivable_loan, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
        $total_debit += $total_receivable_loan;
      }
      $total_balance = $total_credit - $total_debit;
      $table_body .= "<tr><td><strong>Total</strong></td><td><strong>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_debit, $this->config->item('Currency_currency_shortname')) . "</strong></td><td><strong>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_credit, $this->config->item('Currency_currency_shortname')) . "</strong></td></tr>";
      $table_body .= "<tr><td><strong>Difference</strong></td><td></td><td><strong>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance, $this->config->item('Currency_currency_shortname')) . "</strong></td></tr>";
    }

    $json_data['table_body'] = $table_body;
    if ($table_body != '') {
      $json_data['count'] = 1;
    }

    echo json_encode($json_data);  // send data as json format
  }
  public function select_trial_balance()
  {

    $json_data = array();
    $table_body = "";
    $j = 0;
    $data = array();
    $total_debit = $total_credit = 0;

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $end_date = date('Y-m-d', strtotime($_POST['date']));

      $sl_no = 1;
      $supplier_op_balance_payment_where = $supplier_advance_payment_where = $employee_payment_where = $user_commission_payment_where = $tool_payment_where = $tool_purchase_where = $user_payment_return_where = $user_payment_receipt_where = $loan_receipt_where = $loan_payment_where = $salary_payment_where = $labour_wage_payment_where = $client_payment_where = $other_expense_where = $office_expense_where = $purchase_order_payment_where = $purchase_payment_where = $purchase_bill_where = $subcontract_payment_where = $user_payment_released_where = array();
      // if ($this->session->userdata('user_role') != 'accounts_manager' && $this->session->userdata('user_role') != 'general_manager' && $this->session->userdata('user_role') != 'auditor' && $this->session->userdata('user_role') != 'admin' && $this->session->userdata('user_role') != 'madmin') {
      //   $sales_invoice_receipt_where['ats.created_by'] = $employee_payment_where['ats.created_by'] = $user_commission_payment_where['ats.created_by'] = $tool_payment_where['ats.created_by'] = $tool_purchase_where['ats.created_by'] = $loan_payment_where['ats.created_by'] = $other_income_where['ats.created_by'] = $salary_payment_where['ats.created_by'] = $vehicle_payment_where['ats.created_by'] = $labour_wage_payment_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $office_expense_where['ats.created_by'] = $purchase_order_payment_where["ats.created_by"] = $purchase_payment_where['ats.created_by'] = $purchase_bill_payment_where['ats.created_by'] = $subcontract_payment_where['ats.created_by'] = $user_payment_receipt_where['ats.created_by'] = $user_payment_return_where['ats.created_by'] = $this->session->userdata('user_id');
      // }

      $this->db->where("acc_supplier_id !=",0);
      $total_supplier_op_balance	= round($this->Account->select_account_heads("COALESCE(SUM(ah.op_balance),0) as total",'',"","asc")->row()->total,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Opening Balance for Material Supplier</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_supplier_op_balance, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $total_supplier_op_balance;

      $total_labour_op_balance = round($this->Labour->select_labours_2("COALESCE(SUM(l.opening_balance),0) as total", "")->row()->total,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Opening Balance for Labour</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_labour_op_balance, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $total_labour_op_balance;

      // Select Cash & Bank Accounts
      if ($this->session->userdata('user_role') == 'accounts_manager' || $this->session->userdata('user_role') == 'general_manager' || $this->session->userdata('user_role') == 'auditor' || $this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
        // $cash_account_where="(ah.account_type=32 or ah.account_type=36) and acc_user_id!=0";
        $cash_account_where = "ah.account_type=32 or ah.account_type=36";
      } else {
        $cash_account_where = "ah.account_type=32 and acc_user_id=" . $this->session->userdata('user_id');
      }
      $cash_accounts  = $this->Account->select_account_heads("acc_head_id,acc_head_name,acc_user_id", $cash_account_where, "", "asc",'all')->result();
      foreach ($cash_accounts as $key => $value) {
        if ($value->acc_user_id != 0) {
          $account_type = 'user_accounts';
        } else {
          $account_type = 'company_accounts';
        }
        // $cash_balance_amount = $this->accounts_management->calculate_trial_balance($value->acc_head_id, '', $account_type,'',$this->session->userdata('user_role'),$this->session->userdata('user_id'));
        $cash_balance_amount = $this->accounts_management->get_account_statement('trial_balance',"acchead_".$value->acc_head_id, '', $end_date,$this->session->userdata('user_role'),$this->session->userdata('user_id'),'Petty Cash');
        $table_body .= "<tr><td>" . $value->acc_head_name . "</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($cash_balance_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
        $total_debit += $cash_balance_amount;
      }

      // Client Payment Receipts
      $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
      $client_payment_where['ats.reference_type'] = 'project';
      // $client_payment_where['ats.tr_date <='] = $end_date;
      $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'));
      $table_body .= "<tr><td>Client Payment Receipts</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($client_payments_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $client_payments_received;
      // Sales Invoice Receipts
      // $sales_invoice_receipt_where['ats.tr_type'] = 'sales_invoice_transaction';
      // $sales_invoice_receipt_where['ats.reference_type'] = 'sales_invoice';
      // // $sales_invoice_receipt_where['ats.tr_date <='] = $end_date;
      // $sales_invoice_received = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $sales_invoice_receipt_where, 'sales_invoice')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body .= "<tr><td>Ware House Sales Invoice Receipts</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($sales_invoice_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      // $total_credit += $sales_invoice_received;
      // Other Income Receipts
      $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
      $this->db->where("(ats.reference_type='income_receipt' or (ats.reference_type='client' and ats.invoice_id!=0))");
      // $other_income_where['ats.tr_date <='] = $end_date;
      $other_income_received = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $other_income_where, 'income_receipt')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Other Income Receipts</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($other_income_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      $total_credit += $other_income_received;

      // // Loan Receipts on Payable Loan
      // $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
      // $loan_receipt_where['ats.reference_type'] = 'loan';
      // $loan_receipt_where['ats.tr_date <='] = $end_date;
      // $loan_receipt_where['l.loan_type'] = 'payable';
      // $loan_received = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_receipt_where, 'loan')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body .= "<tr><td>Loans Received (Payable Loan)</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($loan_received, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      // $total_credit += $loan_received;
      //
      // // Loan Receipts on Receivable Loan
      // $loan_receipt_where2 = $loan_receipt_where;
      // $loan_receipt_where2['ats.tr_type'] = 'loan_receipt_transaction';
      // $loan_receipt_where2['ats.reference_type'] = 'loan';
      // $loan_receipt_where2['ats.tr_date <='] = $end_date;
      // $loan_receipt_where2['l.loan_type'] = 'receivable';
      // $loan_received2 = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_receipt_where2, 'loan')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body .= "<tr><td>Loans Payments Received (on Receivable Loan)</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($loan_received2, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      // $total_credit += $loan_received2;

      // Select Expense Bill
      $expense_bill_where['b.bill_type'] = 'expense';
      // $expense_bill_where['b.bill_date <='] = $end_date;
      $expense_accounts  = $this->Account->select_account_heads("acc_head_id,acc_head_name", "ah.account_type=11")->result();
      foreach ($expense_accounts as $key => $value) {
        $expense_bill_where['b.bill_credit_ac'] = $value->acc_head_id;
        $expense_bill_amount = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $table_body .= "<tr><td>Expense Bill: " . $value->acc_head_name . "</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($expense_bill_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
        $total_debit += $expense_bill_amount;
      }

      // Select Subcontract Bill
      $subcontract_bill_where['b.bill_type'] = 'subcontract';
      // $subcontract_bill_where['b.bill_date <='] = $end_date;
      $subcontract_bill_amount = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $subcontract_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Subcontract Bill </td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($subcontract_bill_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $subcontract_bill_amount;

      // $purchase_bill_where['p.purchase_date <='] = $end_date;
      $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'));
      $table_body .= "<tr><td>Material Purchase Bill</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_purchase_bill_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $total_purchase_bill_amount;

      $labour_wage_where = array();
      // $labour_wage_where['lw.work_date <='] = $end_date;
      $total_labour_wage_amount = round($this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.total_wage),0) as total", $labour_wage_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Labour Wage</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_labour_wage_amount, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $total_labour_wage_amount;

      $total_payable_loan = round($this->Loan->select_loan("COALESCE(SUM(current_balance_payable),0) as total_amount", array("l.loan_type" => 'payable'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body .= "<tr><td>Total Payable Loan Taken</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency(($total_payable_loan), $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      // $total_debit += $total_payable_loan;

      $total_receivable_loan = round($this->Loan->select_loan("COALESCE(SUM(current_balance_payable),0) as total_amount", array("l.loan_type" => 'receivable'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body .= "<tr><td>Total Receivable Loan Given</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency(($total_receivable_loan), $this->config->item('Currency_currency_shortname')) . "</td></tr>";
      // $total_credit += $total_receivable_loan;

      // Salary Payment
      // $salary_payment_where['ats.tr_date <='] = $end_date;
      $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
      $salary_payment_where['ats.reference_type'] = 'salary_slip';
      $salary_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $salary_payment_where, 'salary_slip')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

      $total_salary = round($this->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total", "")->row()->total,$this->config->item('Currency_amount_decimals_2'));
      $table_body .= "<tr><td>Salary Slip Amount</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_salary, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      $total_debit += $total_salary;
      // Employee Payment
      // $employee_payment_where['ats.tr_date <='] = $end_date;
      $employee_payment_where['ats.reference_type'] = 'employee';
      $this->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction' or ats.tr_type='employee_bata_transaction')");
      $employee_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $employee_payment_where, 'employee')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
      // $table_body .= "<tr><td>Salary Advance / Opening Balance Payments</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($employee_payments, $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
      // $total_debit += $employee_payments;

      if ($this->session->userdata('user_role') == 'accounts_manager' || $this->session->userdata('user_role') == 'general_manager' || $this->session->userdata('user_role') == 'auditor' || $this->session->userdata('user_role') == 'admin' || $this->session->userdata('user_role') == 'madmin') {
        // // Select Payable Account
        // 1) Labour Wages
        // $labour_wage_payment_where['ats.tr_date <='] = $end_date;
        $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
        $labour_wage_payment_where['ats.reference_type'] = 'labour';
        $labour_wage_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $labour_wage_payment_where, 'labour')->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $total_labour_wage_balance = ($total_labour_wage_amount + $total_labour_op_balance - $labour_wage_payments);
        $table_body .= "<tr><td>Payable Labour Wage</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_labour_wage_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_labour_wage_balance;

        // // 2) Purchase Bills
        // $total_purchase_bill_balance = round($this->Purchases->select_purchase("COALESCE(SUM(p.balance_amount),0) as total_amount", "")->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        // $table_body .= "<tr><td>Payable Purchase Bill</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_purchase_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        // $total_credit += $total_purchase_bill_balance;

        // 3) Salary

        // $total_salary_op_balance = $this->Labour->select_labours("COALESCE(SUM(l.opening_balance),0) as total","")->row()->total;
        $total_salary_balance = ($total_salary - $salary_payments);
        $table_body .= "<tr><td>Payable Salary</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_salary_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_salary_balance;
        //
        // 4) Expense Bills
        $total_expense_bill_balance  = round($this->Bills->select_bills("COALESCE(SUM(bill_balance_amount),0) as total_amount", array("b.bill_type" => 'expense'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        $table_body .= "<tr><td>Payable Expense Bill</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_expense_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_expense_bill_balance;

        // // 5) Vehicle Payment
        // $total_vehicle_bill_balance  = round($this->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(balance),0) as total_amount", array())->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        // $table_body .= "<tr><td>Payable Vehicle Work Payments</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_vehicle_bill_balance, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        // $total_credit += $total_vehicle_bill_balance;

        //6) Subcontract Balance
        $subcontract_op_balance_paid = round($this->Subcontracts->select_subcontract("COALESCE(SUM(sc.op_balance),0) as total",)->row()->total,$this->config->item('Currency_amount_decimals_2'));

        $subcontract_payment_where = array();
        // $subcontract_payment_where['ats.tr_date <='] = $end_date;
        $this->db->where("((ats.reference_type='subcontract_bill' or ats.reference_type='subcontract'))");
        $sub_contract_payments = 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'));
        $subcontract_payable = $subcontract_bill_amount-$subcontract_op_balance_paid-$sub_contract_payments;
        $table_body .= "<tr><td>Payable to Subcontract Work</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($subcontract_payable, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $subcontract_payable;

        // //7) Loan Payments
        // // $total_payable_loan = round($this->Loan->select_loan("COALESCE(SUM(current_balance_payable),0) as total_amount", array("l.loan_type" => 'payable'))->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
        // // $total_balance_to_pay_loan = $total_payable_loan - $loan_payments;
        // // $table_body .= "<tr><td>Payable Loan</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance_to_pay_loan, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        // // $total_credit += $total_balance_to_pay_loan;

        //8) Payable to Supplier
        $total_supplier_balance_payable = 0;
        //Select Supplier Accounts
        $this->db->where("acc_supplier_id !=",0);
        $supplier_accounts	= $this->Account->select_account_heads("op_balance,acc_head_id,acc_head_name,acc_supplier_id",'',"","asc")->result();
        foreach ($supplier_accounts as $key => $value) {
          $supplier_opening_balance_paid = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("s.supplier_id" => $value->acc_supplier_id, "ats.tr_type" => 'supplier_opbalance_transaction', "ats.reference_type" => 'supplier'), 'supplier')->row()->total_amount;
          $supplier_advance_paid = $this->supplier_management->calculate_advance_amount($value->acc_supplier_id)['advance_paid'];
          $purchase_order_payments = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("po.supplier_id" => $value->acc_supplier_id, "ats.tr_type" => 'purchase_order_transaction', "ats.reference_type" => 'purchase_order', "po.delete_status" => 0), 'purchase_order')->row()->total_amount;
          $total_purchase_bill_payment = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", array("p.supplier_id" => $value->acc_supplier_id, "ats.tr_type" => 'purchase_bill_transaction', "ats.reference_type" => 'purchase_bill', "p.delete_status" => 0), 'purchase_bill')->row()->total_amount;
          $total_purchase_bill_amount = $this->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as grand_total", array("p.supplier_id" => $value->acc_supplier_id, "p.delete_status" => 0))->row()->grand_total;
          // $total_purchase_bill_balance = $this->Purchases->select_purchase("COALESCE(SUM(p.balance_amount),0) as total_purchase_balance", array("p.supplier_id" => $value->acc_supplier_id, "p.delete_status" => 0))->row()->total_purchase_balance;
          $supplier_balance = $total_purchase_bill_amount+$value->op_balance-$total_purchase_bill_payment-$purchase_order_payments-$supplier_opening_balance_paid-$supplier_advance_paid;
          // $table_body.="<tr><td>Payable Balance to Material Supplier [".str_replace("'s Account'",'',$value->acc_head_name)."]</td><td>".$this->config->item('Currency_currency_format')." ".$this->config->item('currency_format')->formatCurrency($supplier_balance,$this->config->item('Currency_currency_shortname'))."</td><td></td></tr>";
          // $total_credit+=$supplier_balance;
          $total_supplier_balance_payable += $supplier_balance;
        }
        $table_body .= "<tr><td>Payable to Material Supplier</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_supplier_balance_payable, $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += $total_supplier_balance_payable;

        // Receivable Loan
        $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
        $loan_receipt_where['ats.reference_type'] = 'loan';
        $loan_receipt_where['l.loan_type'] = 'receivable';
        $loan_received_amount = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$loan_receipt_where,'loan')->row()->total_amount;
        $table_body .= "<tr><td>Receivable Loan (Balance to Receive)</td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency(($total_receivable_loan-$loan_received_amount), $this->config->item('Currency_currency_shortname')) . "</td><td></td></tr>";
        $total_debit += ($total_receivable_loan-$loan_received_amount);

        // Payable Loan
        $loan_payment_where['ats.tr_type'] = 'loan_transaction';
        $loan_payment_where['ats.reference_type'] = 'loan';
        $loan_payment_where['l.loan_type'] = 'payable';
        $loan_paid_amount = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$loan_payment_where,'loan')->row()->total_amount;
        $table_body .= "<tr><td>Payable Loan (Balance to Pay)</td><td></td><td>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency(($total_payable_loan-$loan_paid_amount), $this->config->item('Currency_currency_shortname')) . "</td></tr>";
        $total_credit += ($total_payable_loan-$loan_paid_amount);
      }
      $total_balance = $total_credit - $total_debit;
      $table_body .= "<tr><td><strong>Total</strong></td><td><strong>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_debit, $this->config->item('Currency_currency_shortname')) . "</strong></td><td><strong>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_credit, $this->config->item('Currency_currency_shortname')) . "</strong></td></tr>";
      $table_body .= "<tr><td><strong>Difference</strong></td><td></td><td><strong>" . $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($total_balance, $this->config->item('Currency_currency_shortname')) . "</strong></td></tr>";
    }

    $json_data['table_body'] = $table_body;
    if ($table_body != '') {
      $json_data['count'] = 1;
    }

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

  public function select_projectwise_profit()
  {

    $json_data = array();
    $j = 0;
    $data = array();
    $start_date = $end_date = '';
    $project_type='all';
    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));

      $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;
    }
    if (isset($_POST['project_type']) && $_POST['project_type'] != '' && $_POST['project_type'] != 'all') {
      $project_type = $_POST['project_type'];
    }
    if (isset($_POST['category']) && $_POST['category'] != '' && $_POST['category'] != 'all') {
      $data['w.work_type'] = $_POST['category'];
    }
    if(isset($_POST['company_id']) && $_POST['company_id']!='' && $_POST['company_id']!='all'){
      $data['w.company_id'] = $_POST['company_id'];
    }
    if (isset($_POST['branch_id']) && $_POST['branch_id'] != '' && $_POST['branch_id'] != 'all') {
      $data['w.branch_id'] = $_POST['branch_id'];
    }
    if(isset($_POST['client']) && $_POST['client']!='' && $_POST['client']!='all'){
      $data['w.client_id'] = $_POST['client'];
    }
    if (isset($_POST['section']) && $_POST['section'] != '' && $_POST['section'] != 'all') {
      $data['w.project_section'] = $_POST['section'];
    }
    if (isset($_POST['status']) && $_POST['status'] != '' && $_POST['status'] != 'all') {
      $data['w.work_status'] = $_POST['status'];
    }

    $overall_project_budget  = round($this->Work->select_work_2("COALESCE(SUM(w.total_budget_amount),0) as total_amount")->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    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)");
    }
    $result  = $this->Work->select_work("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", $data);

    $result_array = $result->result();
    $array = array();

    $gt_user_commission_amount = $gt_income_tax_amount = $gt_expense_tax_amount = $gt_overall_project_profit = $gt_project_office_expense_included = $gt_total_project_budget = $gt_total_client_payments_received = $gt_total_purchase_bill_amount = $gt_total_labour_wages = $gt_total_salary_slip_amount = $gt_total_expense_bill_amount = $gt_total_subcontract_bill_amount = $gt_total_purchase_order_payment = $gt_total_purchase_bill_payment = $gt_total_salary_payments = $gt_total_other_expense = $gt_total_subcontract_payment = $gt_total_expense_amount  = $gt_balance_to_receive = $gt_total_profit = $gt_total_stock_transfer_in_amount = $gt_total_stock_transfer_out_amount = $gt_total_project_cost = 0;

    foreach ($result_array as $row) :
      $id = $row->work_id;
      $office_expense_bill_where = $office_expense_bill_payment_where =  $subcontract_payment_where = array();
      if ($row->work_status == 1) {
        $work_status = "Completed";
        if ($row->project_completed_date != '0000-00-00') {
          $work_status .= "<br/>On " . date('d-m-Y', strtotime($row->project_completed_date));
          $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $row->project_completed_date;
        }
      }elseif($row->work_status==3){
        $work_status="Freeze";
      } else {
        $work_status = "In Progress";
      }

      $office_expense_bill_where['b.bill_type'] = 'expense';
      $office_expense_bill_where['b.work_id'] = 0;
      $overall_office_expense_bill_amount = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $office_expense_bill_where)->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));
      $overall_office_expense_bill_tax_amount = round($this->Bills->select_bills("COALESCE(SUM(bill_tax_amount),0) as total_amount", $office_expense_bill_where)->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

      // $office_expense_bill_payment_where['b.work_id'] = 0;
      // $office_expense_bill_payment_where['b.bill_type'] = 'expense';
      // $office_expense_bill_payment_where['ats.tr_type'] = 'expense_bill_transaction';
      // $office_expense_bill_payment_where['ats.reference_type'] = 'expense_bill';
      // $overall_office_expense_bill_payments = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$office_expense_bill_payment_where,'expense_bill')->row()->total_amount,$this->config->item('Currency_currency_shortname'));
      $report_data = $this->projects_management->get_project_summary($id,$start_date,$end_date);
      if($report_data!=''){
        $have_data_entry = 1;
        if($report_data['total_client_payments_received']==0 && $report_data['total_expense_bill_amount']==0 && $report_data['total_other_expense']==0 && $report_data['total_salary_slip_amount'] ==0 && $report_data['total_user_commission_payments']==0 && $report_data['total_purchase_bill_payment']==0 && $report_data['total_purchase_order_payment']==0 && $report_data['total_purchase_bill_amount']==0 && $report_data['total_subcontract_bill_amount']==0 && $report_data['total_subcontract_payment']==0 && $report_data['total_labour_wages']==0 && $report_data['total_stock_transfer_in_amount']==0 && $report_data['total_stock_transfer_out_amount']==0 ){
          $have_data_entry = 0;
        }
        if($project_type=='all' || ($project_type!='all' && $have_data_entry==1)){

          $gt_total_client_payments_received += $report_data['total_client_payments_received'];
          $gt_total_expense_bill_amount += $report_data['total_expense_bill_amount'];
          $gt_total_other_expense += $report_data['total_other_expense'];
          $gt_total_salary_slip_amount += $report_data['total_salary_slip_amount'];
          $gt_total_salary_payments += $report_data['total_salary_payments'];
          $gt_user_commission_amount += $report_data['total_user_commission_payments'];
          $gt_total_purchase_bill_payment += $report_data['total_purchase_bill_payment'];
          $gt_total_purchase_order_payment += $report_data['total_purchase_order_payment'];
          $gt_total_purchase_bill_amount += $report_data['total_purchase_bill_amount'];
          $gt_total_subcontract_bill_amount += $report_data['total_subcontract_bill_amount'];
          $gt_total_subcontract_payment += $report_data['total_subcontract_payment'];
          $gt_total_labour_wages += $report_data['total_labour_wages'];
          $gt_total_stock_transfer_in_amount += $report_data['total_stock_transfer_in_amount'];
          $gt_total_stock_transfer_out_amount += $report_data['total_stock_transfer_out_amount'];
          $gt_total_project_budget += $report_data['total_project_budget'];

          $project_budget_percentage = 0;
          $report_data['project_office_expense_included'] = $report_data['project_office_expense_tax_included'] = 0;
          if ($overall_project_budget != 0 && $report_data['total_project_budget'] != 0) {
            $project_budget_percentage = ($report_data['total_project_budget'] * 100) / $overall_project_budget;
            $report_data['project_office_expense_included'] = round((($overall_office_expense_bill_amount * $project_budget_percentage) / 100), $this->config->item('Currency_amount_decimals_2'));
            $report_data['project_office_expense_tax_included'] = round((($overall_office_expense_bill_tax_amount * $project_budget_percentage) / 100), $this->config->item('Currency_amount_decimals_2'));
          }
          $gt_project_office_expense_included += $report_data['project_office_expense_included'];
          $gt_total_project_cost += $report_data['total_project_cost'];
          $gt_balance_to_receive += $report_data['balance_to_receive'];
          $gt_total_expense_amount += $report_data['total_expense_amount'];
          $gt_total_profit += $report_data['total_profit'];

          $report_data['overall_project_profit'] = round(($report_data['total_profit'] - $report_data['project_office_expense_included']), $this->config->item('Currency_amount_decimals_2'));
          $gt_overall_project_profit += $report_data['overall_project_profit'];

          $report_data['total_expense_tax_amount'] = $report_data['total_expense_bill_tax_amount'] + $report_data['total_purchase_bill_tax_amount'] + $report_data['total_subcontract_bill_tax_amount'] + $report_data['project_office_expense_tax_included'];
          $gt_expense_tax_amount += $report_data['total_expense_tax_amount'];
          $gt_income_tax_amount += $report_data['total_client_payment_tax_amount'];

          foreach ($report_data as $key => $value) {
            $report_data[$key] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value, $this->config->item('Currency_currency_shortname'));
          }

          $array[$j][] = "#" . $row->work_id;
          $array[$j][] = '<a href="' . base_url() . 'work/profile/' . $id . '">' . $row->work_name . '</a>';
          $array[$j][]=$row->client_name;
          $array[$j][] = $work_status;
          $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][] = $report_data['total_client_payments_received'];
          $array[$j][] = $report_data['total_purchase_bill_amount'];
          $array[$j][] = $report_data['total_labour_wages'];
          $array[$j][] = $report_data['total_salary_slip_amount'];
          $array[$j][] = $report_data['total_expense_bill_amount'];
          $array[$j][] = $report_data['total_subcontract_bill_amount'];
          $array[$j][] = $report_data['total_purchase_order_payment'];
          $array[$j][] = $report_data['total_purchase_bill_payment'];
          $array[$j][] = $report_data['total_salary_payments'];
          $array[$j][] = $report_data['total_other_expense'];
          $array[$j][] = $report_data['total_subcontract_payment'];
          $array[$j][] = $report_data['total_user_commission_payments'];
          $array[$j][] = $report_data['total_stock_transfer_in_amount'];
          $array[$j][] = $report_data['total_stock_transfer_out_amount'];
          $array[$j][] = $report_data['total_project_cost'];
          $array[$j][] = $report_data['total_expense_amount'];
          $array[$j][] = $report_data['total_client_payments_received'];
          $array[$j][] = $report_data['balance_to_receive'];
          $array[$j][] = $report_data['total_profit'];
          // $array[$j][] = $report_data['project_office_expense_included'];
          // $array[$j][] = $report_data['overall_project_profit'];
          $array[$j][] = $report_data['total_client_payment_tax_amount'];
          $array[$j][] = $report_data['total_expense_tax_amount'];


          $j++;
        }
      }
    endforeach;
    if ($result->num_rows() > 0) {
      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = 'Total';
      $array[$j][] = '';
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_project_budget, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_client_payments_received, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_purchase_bill_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_labour_wages, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_salary_slip_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_expense_bill_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_subcontract_bill_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_purchase_order_payment, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_purchase_bill_payment, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_salary_payments, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_other_expense, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_subcontract_payment, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_user_commission_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_stock_transfer_in_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_stock_transfer_out_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_project_cost, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_expense_amount, $this->config->item('Currency_currency_shortname'));

      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_client_payments_received, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_balance_to_receive, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_total_profit, $this->config->item('Currency_currency_shortname'));
      // $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_project_office_expense_included, $this->config->item('Currency_currency_shortname'));
      // $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_overall_project_profit, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_income_tax_amount, $this->config->item('Currency_currency_shortname'));
      $array[$j][] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($gt_expense_tax_amount, $this->config->item('Currency_currency_shortname'));
    }

    $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 select_office_report()
  {

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

    $work_2_where = array();
    if (isset($_POST['branch_id']) && $_POST['branch_id'] != '' && $_POST['branch_id'] != 'all') {
      $work_2_where['w.branch_id'] = $_POST['branch_id'];
    }
    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));

      $tool_purchase_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $tool_purchase_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      $tool_payments_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $tool_payments_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if (isset($_POST['branch_id']) && $_POST['branch_id'] != '' && $_POST['branch_id'] != 'all') {
        $tool_payments_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $sales_invoice_receipt_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $sales_invoice_receipt_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $sales_invoice_receipt_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_expense_bill_where['b.branch_id'] = $_POST['branch_id'];
      }

      $office_expense_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $office_expense_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_expense_bill_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $office_salary_slip_where["STR_TO_DATE(ss.month,'%Y-%m-%d') <="] = $end_date;
      $office_salary_slip_where["STR_TO_DATE(ss.month,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_salary_slip_where['wc.branch_id'] = $_POST['branch_id'];
      }

      $office_salary_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $office_salary_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_salary_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $purchase_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $purchase_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $purchase_order_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_order_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $purchase_order_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $purchase_bill_where["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_bill_where["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $purchase_bill_where['wc.branch_id'] = $_POST['branch_id'];
      }

      $user_commission_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $user_commission_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $user_commission_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

    }

    $sales_invoice_receipt_where['ats.tr_type'] = 'sales_invoice_transaction';
    $sales_invoice_receipt_where['ats.reference_type'] = 'sales_invoice';
    $report_data['total_sales_invoice_received'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $sales_invoice_receipt_where, 'sales_invoice')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $office_expense_bill_where['b.bill_type'] = 'expense';
    $office_expense_bill_where['b.work_id'] = 0;
    $report_data['total_office_expense_bill_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $office_expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
    $report_data['total_office_expense_bill_tax_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_tax_amount),0) as total_amount", $office_expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $office_expense_bill_payment_where['b.work_id'] = 0;
    $office_expense_bill_payment_where['b.bill_type'] = 'expense';
    $office_expense_bill_payment_where['ats.tr_type'] = 'expense_bill_transaction';
    $office_expense_bill_payment_where['ats.reference_type'] = 'expense_bill';
    $report_data['total_office_expense_bill_payments'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $office_expense_bill_payment_where, 'expense_bill')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $report_data['total_expense_bill_amount'] = $report_data['total_office_expense_bill_amount'];
    $report_data['total_expense_bill_payments'] = $report_data['total_office_expense_bill_payments'];
    $report_data['total_expense_bill_payments_pending'] = $report_data['total_expense_bill_amount'] - $report_data['total_expense_bill_payments'];

    $office_salary_slip_where['ss.work_id'] = 0;
    $office_salary_slip_where['ss.slip_type'] = 'Office';
    $report_data['total_office_salary_slip_amount'] = round($this->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total", $office_salary_slip_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $office_salary_payment_where['ss.work_id'] = 0;
    $office_salary_payment_where['ss.slip_type'] = 'Office';
    $office_salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
    $office_salary_payment_where['ats.reference_type'] = 'salary_slip';
    $report_data['total_office_salary_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $office_salary_payment_where, 'salary_slip')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $report_data['total_salary_slip_amount'] = $report_data['total_office_salary_slip_amount'];
    $report_data['total_salary_payments'] = $report_data['total_office_salary_payments'];
    $report_data['total_salary_payments_pending'] = $report_data['total_salary_slip_amount'] - $report_data['total_salary_payments'];

    $purchase_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
    $purchase_payment_where['ats.reference_type'] = 'purchase_bill';
    $report_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_type'] = 'purchase_order_transaction';
    $purchase_order_payment_where['ats.reference_type'] = 'purchase_order';
    // $purchase_order_payment_where['po.purchase_bill_id'] = 0;
    $report_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'));

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

    $report_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'));
    $report_data['total_purchase_bill_tax_amount'] = round($this->Purchases->select_purchase("COALESCE(SUM(p.bill_tax_amount),0) as grand_total", $purchase_bill_where)->row()->grand_total, $this->config->item('Currency_amount_decimals_2'));
    $report_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'));
    $report_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'));

    $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
    $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
    $report_data['total_tool_purchase_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_purchase_where, 'tool_purchase')->row()->total_amount;

    $tool_payments_where['ats.tr_type'] = 'tool_payment_transaction';
    $this->db->where("(ats.reference_type='tool_rent' or ats.reference_type='tool_transfer')");
    $report_data['total_tool_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_payments_where, 'tool')->row()->total_amount;

    $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
    $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
    $report_data['total_user_commission_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_commission_payment_where, 'user_commission')->row()->total_amount;

    $report_data['total_project_expense_amount'] =   $report_data['total_user_commission_payments'] + $report_data['total_tool_payments'] + $report_data['total_project_expense_bill_amount']  + $report_data['total_project_salary_slip_amount'] +  $report_data['total_purchase_bill_amount'] + $report_data['total_subcontract_bill_amount'] + $report_data['total_labour_wages'];
    $report_data['total_office_expense_amount'] = $report_data['total_office_expense_bill_amount']  + $report_data['total_office_salary_slip_amount'] + $report_data['total_labour_op_balance'] + $report_data['total_tool_purchase_payments'];
    $report_data['total_overall_expense_amount'] = $report_data['total_project_expense_amount']  + $report_data['total_office_expense_amount'];

    $report_data['total_project_expense_payments'] = $report_data['total_user_commission_payments'] + $report_data['total_tool_payments'] + $report_data['total_project_expense_bill_payments']  + $report_data['total_project_salary_payments'] +  $report_data['total_purchase_payment'] + $report_data['total_subcontract_payment'] + $report_data['total_labour_wage_payments'];
    $report_data['total_office_expense_payments'] = $report_data['total_office_expense_bill_payments']  + $report_data['total_office_salary_payments'];
    $report_data['total_overall_expense_payments'] = $report_data['total_project_expense_payments']  + $report_data['total_office_expense_payments'];
    $report_data['total_overall_expense_payments_pending'] = $report_data['total_overall_expense_amount'] - $report_data['total_overall_expense_payments'];

    $report_data['total_amount_to_receive'] = $report_data['total_project_budget'];
    $report_data['balance_to_receive'] = round($report_data['total_project_budget'] - $report_data['total_client_payments_received'], $this->config->item('Currency_amount_decimals_2'));

    $report_data['total_profit'] = round($report_data['total_sales_invoice_received'] + $report_data['total_client_payments_received'] - $report_data['total_overall_expense_payments'], $this->config->item('Currency_amount_decimals_2'));
    $report_data['total_expense_tax_amount'] = $report_data['total_project_expense_bill_tax_amount'] + $report_data['total_office_expense_bill_tax_amount'] + $report_data['total_purchase_bill_tax_amount'] + $report_data['total_subcontract_bill_tax_amount'];

    foreach ($report_data as $key => $value) {
      $report_data[$key] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value, $this->config->item('Currency_currency_shortname'));
    }
    $sl_no = 0;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Ware House Sales Received';
    $array[$j][] = $report_data['total_sales_invoice_received'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Bill Amount';
    $array[$j][] = $report_data['total_purchase_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Bill Payments';
    $array[$j][] = $report_data['total_purchase_bill_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Order (Advance) Payments';
    $array[$j][] = $report_data['total_purchase_order_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Payments Made (Purchase Bill + Purchase Order)';
    $array[$j][] = $report_data['total_purchase_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Pending Purchase Bill Payments';
    $array[$j][] = $report_data['total_purchase_bill_balance_amount'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Bill Amount';
    $array[$j][] = $report_data['total_subcontract_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Bill Payments';
    $array[$j][] = $report_data['total_subcontract_bill_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Advance Payments';
    $array[$j][] = $report_data['total_subcontract_advance_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Payments';
    $array[$j][] = $report_data['total_subcontract_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Pending Subcontract Bill Payments';
    $array[$j][] = $report_data['total_subcontract_bill_balance_amount'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Labour Wage Slip Amount';
    $array[$j][] = $report_data['total_labour_wages'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Labour Opening Balance Amount';
    $array[$j][] = $report_data['total_labour_op_balance'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Labour Wage Slip Payments';
    $array[$j][] = $report_data['total_labour_wage_payments'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Pending Labour Payments';
    $array[$j][] = $report_data['total_labour_payment_pending'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Project Salary Slip Amount';
    $array[$j][] = $report_data['total_project_salary_slip_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Office Salary Slip Amount';
    $array[$j][] = $report_data['total_office_salary_slip_amount'];
    $j++;;
    $sl_no++;
    // $array[$j][]=$sl_no+1;
    // $array[$j][]='Total Employee Opening Balance Amount';
    // $array[$j][]=$report_data['total_employee_op_balance'];
    // $j++;;$sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Salary Slip Payments';
    $array[$j][] = $report_data['total_salary_payments'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Pending Salary Payments';
    $array[$j][] = $report_data['total_salary_payments_pending'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Tool Purchase Amount';
    $array[$j][] = $report_data['total_tool_purchase_payments'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Tool Payments (Rent & Transfer)';
    $array[$j][] = $report_data['total_tool_payments'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    if ($this->config->item('User_project_commission_payment') == 1) {
      $array[$j][] = $sl_no + 1;
      $array[$j][] = 'Total User Commission Payment For Project';
      $array[$j][] = $report_data['total_user_commission_payments'];
      $j++;;
      $sl_no++;

      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $j++;
    }

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Project Expense Bill Amount';
    $array[$j][] = $report_data['total_project_expense_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Office Expense Bill Amount';
    $array[$j][] = $report_data['total_office_expense_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Bill Payments';
    $array[$j][] = $report_data['total_expense_bill_payments'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Pending Expense Bill Payments';
    $array[$j][] = $report_data['total_expense_bill_payments_pending'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = '';
    $array[$j][] = '<strong>SUMMARY</strong>';
    $array[$j][] = '';
    $j++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Amount Payable';
    $array[$j][] = $report_data['total_overall_expense_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Amount Paid';
    $array[$j][] = $report_data['total_overall_expense_payments'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Amount Balance to Pay';
    $array[$j][] = $report_data['total_overall_expense_payments_pending'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Amount Receivable From Client';
    $array[$j][] = $report_data['total_project_budget'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Received';
    $array[$j][] = $report_data['total_client_payments_received'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Balance to Receive';
    $array[$j][] = $report_data['balance_to_receive'];
    $j++;;
    $sl_no++;
    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Profit';
    $array[$j][] = $report_data['total_profit'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Income Tax Amt';
    $array[$j][] = $report_data['total_client_payments_tax_received'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Tax Amount';
    $array[$j][] = $report_data['total_expense_tax_amount'];
    $j++;;
    $sl_no++;

    $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 select_overall_profit()
  {

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

    $work_2_where = array();
    if (isset($_POST['branch_id']) && $_POST['branch_id'] != '' && $_POST['branch_id'] != 'all') {
      $work_2_where['w.branch_id'] = $_POST['branch_id'];
    }

    $report_data['total_project_budget']  = round($this->Work->select_work_2("COALESCE(SUM(w.total_budget_amount),0) as total_amount",$work_2_where)->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    if (isset($_POST['date']) && $_POST['date'] != '') {
      $date = array();
      $date = explode('-', $_POST['date']);
      $start_date = date('Y-m-d', strtotime($date[0]));
      $end_date = date('Y-m-d', strtotime($date[1]));

      $tool_purchase_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $tool_purchase_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      $tool_payments_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $tool_payments_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;


      if (isset($_POST['branch_id']) && $_POST['branch_id'] != '' && $_POST['branch_id'] != 'all') {
        $tool_payments_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $client_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $client_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $client_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $sales_invoice_receipt_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $sales_invoice_receipt_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $sales_invoice_receipt_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $project_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $project_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $project_expense_bill_where['b.branch_id'] = $_POST['branch_id'];
      }

      $project_expense_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $project_expense_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $project_expense_bill_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $office_expense_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_expense_bill_where['b.branch_id'] = $_POST['branch_id'];
      }

      $office_expense_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $office_expense_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_expense_bill_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $project_salary_slip_where["STR_TO_DATE(ss.month,'%Y-%m-%d') <="] = $end_date;
      $project_salary_slip_where["STR_TO_DATE(ss.month,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $project_salary_slip_where['wc.branch_id'] = $_POST['branch_id'];
      }

      $project_salary_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $project_salary_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $project_salary_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $office_salary_slip_where["STR_TO_DATE(ss.month,'%Y-%m-%d') <="] = $end_date;
      $office_salary_slip_where["STR_TO_DATE(ss.month,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_salary_slip_where['wc.branch_id'] = $_POST['branch_id'];
      }

      $office_salary_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $office_salary_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $office_salary_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $purchase_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $purchase_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $purchase_order_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_order_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $purchase_order_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $purchase_bill_where["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') <="] = $end_date;
      $purchase_bill_where["STR_TO_DATE(p.purchase_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $purchase_bill_where['wc.branch_id'] = $_POST['branch_id'];
      }

      $subcontract_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') <="] = $end_date;
      $subcontract_bill_where["STR_TO_DATE(b.bill_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $subcontract_bill_where['b.branch_id'] = $_POST['branch_id'];
      }

      $subcontract_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $subcontract_bill_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $subcontract_bill_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $subcontract_advance_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $subcontract_advance_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $subcontract_advance_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $labour_wage_where_data["STR_TO_DATE(lw.work_date,'%Y-%m-%d') <="] = $end_date;
      $labour_wage_where_data["STR_TO_DATE(lw.work_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $labour_wage_where_data['l.branch_id'] = $_POST['branch_id'];
      }

      $labour_wage_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $labour_wage_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $labour_wage_payment_where['ats.tr_branch_id'] = $_POST['branch_id'];
      }

      $user_commission_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') <="] = $end_date;
      $user_commission_payment_where["STR_TO_DATE(ats.tr_date,'%Y-%m-%d') >="] = $start_date;

      if(isset($_POST['branch_id']) && $_POST['branch_id']!='' && $_POST['branch_id']!='all'){
        $user_commission_payment_where['ats.tr_branch_id'] = $_POST['branch_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');
    }
    $report_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'));
    $report_data['total_client_payments_tax_received'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tax_amount),0) as total_amount", $client_payment_where, 'client_payment')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $sales_invoice_receipt_where['ats.tr_type'] = 'sales_invoice_transaction';
    $sales_invoice_receipt_where['ats.reference_type'] = 'sales_invoice';
    $report_data['total_sales_invoice_received'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $sales_invoice_receipt_where, 'sales_invoice')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $project_expense_bill_where['b.bill_type'] = 'expense';
    $project_expense_bill_where['b.work_id !='] = 0;
    $report_data['total_project_expense_bill_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $project_expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
    $report_data['total_project_expense_bill_tax_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_tax_amount),0) as total_amount", $project_expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $project_expense_bill_payment_where['b.work_id !='] = 0;
    $project_expense_bill_payment_where['ats.tr_type'] = 'expense_bill_transaction';
    $project_expense_bill_payment_where['ats.reference_type'] = 'expense_bill';
    $report_data['total_project_expense_bill_payments'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $project_expense_bill_payment_where, 'expense_bill')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $office_expense_bill_where['b.bill_type'] = 'expense';
    $office_expense_bill_where['b.work_id'] = 0;
    $report_data['total_office_expense_bill_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $office_expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
    $report_data['total_office_expense_bill_tax_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_tax_amount),0) as total_amount", $office_expense_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $office_expense_bill_payment_where['b.work_id'] = 0;
    $office_expense_bill_payment_where['b.bill_type'] = 'expense';
    $office_expense_bill_payment_where['ats.tr_type'] = 'expense_bill_transaction';
    $office_expense_bill_payment_where['ats.reference_type'] = 'expense_bill';
    $report_data['total_office_expense_bill_payments'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $office_expense_bill_payment_where, 'expense_bill')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $report_data['total_expense_bill_amount'] = $report_data['total_project_expense_bill_amount'] + $report_data['total_office_expense_bill_amount'];
    $report_data['total_expense_bill_payments'] = $report_data['total_office_expense_bill_payments'] + $report_data['total_project_expense_bill_payments'];
    $report_data['total_expense_bill_payments_pending'] = $report_data['total_expense_bill_amount'] - $report_data['total_expense_bill_payments'];

    $project_salary_slip_where['ss.work_id !='] = 0;
    $project_salary_slip_where['ss.slip_type'] = 'Project';
    $report_data['total_project_salary_slip_amount'] = round($this->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total", $project_salary_slip_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $project_salary_payment_where['ss.work_id !='] = 0;
    $project_salary_payment_where['ss.slip_type'] = 'Project';
    $project_salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
    $project_salary_payment_where['ats.reference_type'] = 'salary_slip';
    $report_data['total_project_salary_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $project_salary_payment_where, 'salary_slip')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $office_salary_slip_where['ss.work_id'] = 0;
    $office_salary_slip_where['ss.slip_type'] = 'Office';
    $report_data['total_office_salary_slip_amount'] = round($this->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total", $office_salary_slip_where)->row()->total,$this->config->item('Currency_amount_decimals_2'));

    $office_salary_payment_where['ss.work_id'] = 0;
    $office_salary_payment_where['ss.slip_type'] = 'Office';
    $office_salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
    $office_salary_payment_where['ats.reference_type'] = 'salary_slip';
    $report_data['total_office_salary_payments']  = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $office_salary_payment_where, 'salary_slip')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $report_data['total_salary_slip_amount'] = $report_data['total_project_salary_slip_amount'] + $report_data['total_office_salary_slip_amount'];
    $report_data['total_salary_payments'] = $report_data['total_office_salary_payments'] + $report_data['total_project_salary_payments'];
    $report_data['total_salary_payments_pending'] = $report_data['total_salary_slip_amount'] - $report_data['total_salary_payments'];

    $purchase_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
    $purchase_payment_where['ats.reference_type'] = 'purchase_bill';
    $report_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_type'] = 'purchase_order_transaction';
    $purchase_order_payment_where['ats.reference_type'] = 'purchase_order';
    // $purchase_order_payment_where['po.purchase_bill_id'] = 0;
    $report_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'));

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

    $report_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'));
    $report_data['total_purchase_bill_tax_amount'] = round($this->Purchases->select_purchase("COALESCE(SUM(p.bill_tax_amount),0) as grand_total", $purchase_bill_where)->row()->grand_total, $this->config->item('Currency_amount_decimals_2'));
    $report_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'));
    $report_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'));

    $subcontract_bill_where['b.bill_type'] = 'subcontract';
    $report_data['total_subcontract_bill_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_payable_amount),0) as total_amount", $subcontract_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
    $report_data['total_subcontract_bill_tax_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_tax_amount),0) as total_amount", $subcontract_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));
    $report_data['total_subcontract_bill_balance_amount'] = round($this->Bills->select_bills("COALESCE(SUM(bill_balance_amount),0) as total_amount", $subcontract_bill_where)->row()->total_amount,$this->config->item('Currency_amount_decimals_2'));

    $subcontract_bill_payment_where['ats.reference_type'] = 'subcontract_bill';
    $subcontract_bill_payment_where['b.bill_type'] = 'subcontract';
    $subcontract_bill_payment_where['ats.tr_type'] = 'subcontract_bill_transaction';
    $report_data['total_subcontract_bill_payment'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $subcontract_bill_payment_where, 'subcontract_bill')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    $subcontract_advance_payment_where['ats.reference_type'] = 'subcontract';
    $subcontract_advance_payment_where['ats.tr_type'] = 'subcontract_advance_transaction';
    $report_data['total_subcontract_advance_payment'] = round($this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $subcontract_advance_payment_where, 'subcontract_advance')->row()->total_amount, $this->config->item('Currency_amount_decimals_2'));

    // $subcontract_payment_where = array();
    // $this->db->where("((ats.reference_type='subcontract_bill') or (ats.reference_type='subcontract'))");
    // $report_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'));

    $report_data['total_subcontract_payment'] = $report_data['total_subcontract_bill_payment'] + $report_data['total_subcontract_advance_payment'];


    $report_data['total_labour_op_balance'] = round($this->Labour->select_labours_2("COALESCE(SUM(l.opening_balance),0) as total")->row()->total, $this->config->item('Currency_amount_decimals_2'));
    $report_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'));
    $report_data['total_labours_worked'] = round($this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.no_of_labours),0) as total", $labour_wage_where_data)->row()->total, $this->config->item('Currency_amount_decimals_2'));
    $report_data['total_labour_days'] = round($this->Labour_wages->select_labour_wages("COALESCE(SUM(lw.work_days),0) as total", $labour_wage_where_data)->row()->total, $this->config->item('Currency_amount_decimals_2'));
    $report_data['total_labour_payable'] = $report_data['total_labour_op_balance'] + $report_data['total_labour_wages'];

    $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
    $labour_wage_payment_where['ats.reference_type'] = 'labour';
    $report_data['total_labour_wage_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $labour_wage_payment_where, 'labour')->row()->total_amount;
    $report_data['total_labour_payment_pending'] = $report_data['total_labour_payable'] - $report_data['total_labour_wage_payments'];
    // $report_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'));
    // $report_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'));
    // $report_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'));
    // $report_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'));

    $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
    $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
    $report_data['total_tool_purchase_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_purchase_where, 'tool_purchase')->row()->total_amount;

    $tool_payments_where['ats.tr_type'] = 'tool_payment_transaction';
    $this->db->where("(ats.reference_type='tool_rent' or ats.reference_type='tool_transfer')");
    $report_data['total_tool_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_payments_where, 'tool')->row()->total_amount;

    $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
    $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
    $report_data['total_user_commission_payments'] = $this->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_commission_payment_where, 'user_commission')->row()->total_amount;

    $report_data['total_project_expense_amount'] =   $report_data['total_user_commission_payments'] + $report_data['total_tool_payments'] + $report_data['total_project_expense_bill_amount']  + $report_data['total_project_salary_slip_amount'] +  $report_data['total_purchase_bill_amount'] + $report_data['total_subcontract_bill_amount'] + $report_data['total_labour_wages'];
    $report_data['total_office_expense_amount'] = $report_data['total_office_expense_bill_amount']  + $report_data['total_office_salary_slip_amount'] + $report_data['total_labour_op_balance'] + $report_data['total_tool_purchase_payments'];
    $report_data['total_overall_expense_amount'] = $report_data['total_project_expense_amount']  + $report_data['total_office_expense_amount'];

    $report_data['total_project_expense_payments'] = $report_data['total_user_commission_payments'] + $report_data['total_tool_payments'] + $report_data['total_project_expense_bill_payments']  + $report_data['total_project_salary_payments'] +  $report_data['total_purchase_payment'] + $report_data['total_subcontract_payment'] + $report_data['total_labour_wage_payments'];
    $report_data['total_office_expense_payments'] = $report_data['total_office_expense_bill_payments']  + $report_data['total_office_salary_payments'];
    $report_data['total_overall_expense_payments'] = $report_data['total_project_expense_payments']  + $report_data['total_office_expense_payments'];
    $report_data['total_overall_expense_payments_pending'] = $report_data['total_overall_expense_amount'] - $report_data['total_overall_expense_payments'];

    $report_data['total_amount_to_receive'] = $report_data['total_project_budget'];
    $report_data['balance_to_receive'] = round($report_data['total_project_budget'] - $report_data['total_client_payments_received'], $this->config->item('Currency_amount_decimals_2'));

    $report_data['total_profit'] = round($report_data['total_sales_invoice_received'] + $report_data['total_client_payments_received'] - $report_data['total_overall_expense_payments'], $this->config->item('Currency_amount_decimals_2'));
    $report_data['total_expense_tax_amount'] = $report_data['total_project_expense_bill_tax_amount'] + $report_data['total_office_expense_bill_tax_amount'] + $report_data['total_purchase_bill_tax_amount'] + $report_data['total_subcontract_bill_tax_amount'];

    foreach ($report_data as $key => $value) {
      $report_data[$key] = $this->config->item('Currency_currency_format') . " " . $this->config->item('currency_format')->formatCurrency($value, $this->config->item('Currency_currency_shortname'));
    }
    $sl_no = 0;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Total Project Budget';
    // $array[$j][] = $report_data['total_project_budget'];
    // $j++;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Client Payment Received';
    $array[$j][] = $report_data['total_client_payments_received'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

if ($this->config->item('app_module_sales_invoice') == 1){
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Ware House Sales Received';
    $array[$j][] = $report_data['total_sales_invoice_received'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;
  }

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Bill Amount';
    $array[$j][] = $report_data['total_purchase_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Bill Payments';
    $array[$j][] = $report_data['total_purchase_bill_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Order (Advance) Payments';
    $array[$j][] = $report_data['total_purchase_order_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Purchase Payments Made (Purchase Bill + Purchase Order)';
    $array[$j][] = $report_data['total_purchase_payment'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Pending Purchase Bill Payments';
    // $array[$j][] = $report_data['total_purchase_bill_balance_amount'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Bill Amount';
    $array[$j][] = $report_data['total_subcontract_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Bill Payments';
    $array[$j][] = $report_data['total_subcontract_bill_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Advance Payments';
    $array[$j][] = $report_data['total_subcontract_advance_payment'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Subcontract Payments';
    $array[$j][] = $report_data['total_subcontract_payment'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Pending Subcontract Bill Payments';
    // $array[$j][] = $report_data['total_subcontract_bill_balance_amount'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Labour Wage Slip Amount';
    $array[$j][] = $report_data['total_labour_wages'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Total Labour Opening Balance Amount';
    // $array[$j][] = $report_data['total_labour_op_balance'];
    // $j++;;
    // $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Labour Wage Slip Payments';
    $array[$j][] = $report_data['total_labour_wage_payments'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Pending Labour Payments';
    // $array[$j][] = $report_data['total_labour_payment_pending'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Project Salary Slip Amount';
    $array[$j][] = $report_data['total_project_salary_slip_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Office Salary Slip Amount';
    $array[$j][] = $report_data['total_office_salary_slip_amount'];
    $j++;;
    $sl_no++;
    // $array[$j][]=$sl_no+1;
    // $array[$j][]='Total Employee Opening Balance Amount';
    // $array[$j][]=$report_data['total_employee_op_balance'];
    // $j++;;$sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Salary Slip Payments';
    $array[$j][] = $report_data['total_salary_payments'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Pending Salary Payments';
    // $array[$j][] = $report_data['total_salary_payments_pending'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Tool Purchase Amount';
    $array[$j][] = $report_data['total_tool_purchase_payments'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Tool Payments (Rent & Transfer)';
    $array[$j][] = $report_data['total_tool_payments'];
    $j++;;
    $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    if ($this->config->item('User_project_commission_payment') == 1) {
      $array[$j][] = $sl_no + 1;
      $array[$j][] = 'Total User Commission Payment For Project';
      $array[$j][] = $report_data['total_user_commission_payments'];
      $j++;;
      $sl_no++;

      $array[$j][] = '';
      $array[$j][] = '';
      $array[$j][] = '';
      $j++;
    }

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Project Expense Bill Amount';
    $array[$j][] = $report_data['total_project_expense_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Office Expense Bill Amount';
    $array[$j][] = $report_data['total_office_expense_bill_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Bill Payments';
    $array[$j][] = $report_data['total_expense_bill_payments'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Pending Expense Bill Payments';
    // $array[$j][] = $report_data['total_expense_bill_payments_pending'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = '';
    $array[$j][] = '<strong>SUMMARY</strong>';
    $array[$j][] = '';
    $j++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Amount Payable';
    $array[$j][] = $report_data['total_overall_expense_amount'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Amount Paid';
    $array[$j][] = $report_data['total_overall_expense_payments'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Total Expense Amount Balance to Pay';
    // $array[$j][] = $report_data['total_overall_expense_payments_pending'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Total Amount Receivable From Client';
    // $array[$j][] = $report_data['total_project_budget'];
    // $j++;;
    // $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Received From Client';
    $array[$j][] = $report_data['total_client_payments_received'];
    $j++;;
    $sl_no++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Balance to Receive';
    // $array[$j][] = $report_data['balance_to_receive'];
    // $j++;;
    // $sl_no++;
    // $array[$j][] = '';
    // $array[$j][] = '';
    // $array[$j][] = '';
    // $j++;
    // $array[$j][] = $sl_no + 1;
    // $array[$j][] = 'Total Profit';
    // $array[$j][] = $report_data['total_profit'];
    // $j++;;
    // $sl_no++;

    $array[$j][] = '';
    $array[$j][] = '';
    $array[$j][] = '';
    $j++;

    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Income Tax Amt';
    $array[$j][] = $report_data['total_client_payments_tax_received'];
    $j++;;
    $sl_no++;
    $array[$j][] = $sl_no + 1;
    $array[$j][] = 'Total Expense Tax Amount';
    $array[$j][] = $report_data['total_expense_tax_amount'];
    $j++;;
    $sl_no++;

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