<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Accounts_management {

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


    $CI =& get_instance();
    $CI->CI =& get_instance();
    $CI->CI->load->library('session');

    $CI->load->model('User_model', 'User');
    $CI->load->model('Work_model', 'Work');
    $CI->load->model('Client_model', 'Client');
    $CI->load->model('Supplier_model', 'Supplier');
    $CI->load->model('Purchase_bill_model', 'Purchases');
    $CI->load->model('Purchase_order_model', 'Purchase_order');
    $CI->load->model('Material_model', 'Material');
    $CI->load->model('Subcontracts_model', 'Subcontracts');
    $CI->load->model('Labour_model', 'Labour');
    $CI->load->model('Labour_wages_model', 'Labour_wages');
    $CI->load->model('Account_model', 'Account');
    $CI->load->model('Settings_model', 'Settings');
    $CI->load->model('Bills_model', 'Bills');
    $CI->load->model('Loan_model', 'Loan');
    $CI->load->model('Vehicle_model', 'Vehicle');
    $CI->load->model('Employees_model', 'Employee');
    $CI->load->model('Vehicle_work_slips_model', 'Vehicle_work_slips');
    $CI->load->model('Invoices_model', 'Invoices');
  }


  public function calculate_trial_balance($account_head_id='',$end_date="",$account_type="",$work_id='',$user_role='',$user_id=''){

    $CI =& get_instance();
    $current_balance = 0;
    $account_where = array();
    if($account_head_id!=''){
      $account_where['acc_head_id'] = $account_head_id;
    }
    $account_type2=$additional_columns='';
    if($work_id=='' || $work_id=='all'){
      if($account_type=='company_accounts'){
        $account_where['acc_employee_id'] = 0;
      }elseif($account_type=='user_accounts'){
        $account_where['acc_employee_id !='] = 0;
        $account_type2='user';
        $additional_columns.="";
      }elseif($account_type=='supplier'){
        $account_where['acc_supplier_id !='] = 0;
        $account_type2='supplier';
      }
    }else{
      $account_where['account_type !='] = 11;
      $account_where['account_type !='] = 12;
    }
    $account_details = $CI->Account->select_account_heads("ah.acc_head_id,ah.op_balance,ah.account_type,acc_employee_id,acc_supplier_id".$additional_columns,$account_where,$account_type2)->result();
    foreach ($account_details as $key => $value) {
      $account_id = $value->acc_head_id;
      $travel_allowance_where = $bills_where = $labour_wage_slip_where = $salary_slip_where = $purchase_bill_where = $employee_payment_where = $user_commission_payment_where = $tool_payment_where = $tool_purchase_where = $loan_receipt_where = $loan_payment_where = $user_receipt_where = $salary_payment_where = $other_income_where = $user_payment_receipt_where = $user_payment_return_where = $user_payment_return_receipt_where = $client_payment_where = $purchase_payment_where = $purchase_order_payment_where = $subcontract_payment_where = $other_expense_where = $office_expense_where = $labour_wage_payment_where = $purchase_bill_payment_where = array();
      if($user_role!='auditor' && $user_role!='accounts_manager' && $user_role!='general_manager' && $user_role!='admin' && $user_role!='madmin'){
        // $travel_allowance_where['ta.created_by'] = $labour_wage_slip_where['lws.created_by'] = $vehicle_expense_where['ats.created_by'] = $vehicle_opbalance_payment_where['ats.created_by'] =  $employee_payment_where['ats.created_by'] = $user_commission_payment_where['ats.created_by'] = $tool_payment_where['ats.created_by'] = $account_transfer_where_credit['ats.created_by'] = $account_transfer_where_debit['ats.created_by'] = $supplier_advance_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'] = $user_payment_return_receipt_where['ats.created_by'] = $user_payment_released_where['ats.created_by'] = $user_id;
      }
      if($work_id!='' && $work_id!='all'){
        $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'] = $work_id;
      }
      $total_credit = $total_debit = 0;
      if($end_date!=''){
        $user_receipt_where['ats.tr_date <='] = $end_date;
        $client_payment_where['ats.tr_date <='] = $end_date;
        $other_income_where['ats.tr_date <='] = $end_date;
        $purchase_order_payment_where['ats.tr_date <='] = $end_date;
        $purchase_bill_payment_where['ats.tr_date <='] = $end_date;
        $labour_wage_payment_where['ats.tr_date <='] = $end_date;
        $salary_payment_where['ats.tr_date <='] = $end_date;
        $vehicle_payment_where['ats.tr_date <='] = $end_date;
        $vehicle_expense_where['ats.tr_date <='] = $end_date;
        $vehicle_opbalance_payment_where['ats.tr_date <='] = $end_date;
        $subcontract_payment_where['ats.tr_date <='] = $end_date;
        $other_expense_where['ats.tr_date <='] = $end_date;
        $user_payment_receipt_where['ats.tr_date <='] = $end_date;
        $user_payment_return_where['ats.tr_date <='] = $end_date;
        $user_payment_return_receipt_where['ats.tr_date <='] = $end_date;
        $user_payment_released_where['ats.tr_date <='] = $end_date;
        $loan_payment_where['ats.tr_date <='] = $end_date;
        $loan_receipt_where['ats.tr_date <='] = $end_date;
        $tool_purchase_where['ats.tr_date <='] = $end_date;
        $supplier_advance_payment_where['ats.tr_date <='] = $end_date;
        $supplier_opbalance_payment_where['ats.tr_date <='] = $end_date;
        $account_transfer_where_credit['ats.tr_date <='] = $end_date;
        $account_transfer_where_debit['ats.tr_date <='] = $end_date;
        $tool_payment_where['ats.tr_date <='] = $end_date;
        $user_commission_payment_where['ats.tr_date <='] = $end_date;
        $employee_payment_where['ats.tr_date <='] = $end_date;
        $purchase_bill_where['p.purchase_date <='] = $end_date;
        $salary_slip_where['ss.month <='] = $end_date;
        $labour_wage_slip_where['lw.work_date <='] = $end_date;
        $bills_where['b.bill_date <='] = $end_date;
        $travel_allowance_where['ats.tr_date <='] = $end_date;
      }

      // if($value->account_type==11){//For Expense Heads
      //   $bills_where['b.bill_credit_ac'] = $account_id;
      //   $expense_bills_amount = round($CI->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as total_amount",$bills_where)->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      //   $total_debit+=$expense_bills_amount;
      // }

      // Select Expense
      if($value->account_type==11){
        $other_expense_where['ats.tr_credit_acc'] = $account_id;
      }else{
        $other_expense_where['ats.tr_debit_acc'] = $account_id;
      }
      $other_expense_where['ats.reference_type'] = 'expense_bill';
      $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
      $expense_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $other_expense_where,'expense_bill')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      if($value->account_type==11){
        $total_credit+=$expense_payments;
      }else{
        $total_debit+=$expense_payments;
      }

      // Client Payment Receipts
      $client_payment_where['ats.tr_credit_acc'] = $account_id;
      $client_payment_where['ats.reference_type'] = 'project';
      $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
      $client_payments_received	= round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $client_payment_where,'client_payment')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_credit+=$client_payments_received;

      if($work_id=='' || $work_id=='all'){
        // Other Income Receipts
        $other_income_where['ats.tr_credit_acc'] = $account_id;
        $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
        // $other_income_where['ats.reference_type'] = 'income_receipt';
        $CI->db->where("(ats.reference_type='income_receipt' or (ats.reference_type='client' and ats.invoice_id!=0))");
        $other_income_received = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$other_income_where,'income_receipt')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_credit+=$other_income_received;
      }
      // Loan Receipts
      $loan_receipt_where['ats.tr_credit_acc'] = $account_id;
      $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
      $loan_receipt_where['ats.reference_type'] = 'loan';
      $loan_received = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$loan_receipt_where,'loan')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_credit+=$loan_received;

      if($work_id=='' || $work_id=='all'){
        // Advance Payments
        if($account_type=='supplier'){
          $supplier_advance_payment_where['ats.reference_id'] = $value->acc_supplier_id;
        }else{
          $supplier_advance_payment_where['ats.tr_debit_acc'] = $account_id;
        }
        $supplier_advance_payment_where['ats.tr_type'] = 'supplier_advance_transaction';
        $supplier_advance_payment_where['ats.reference_type'] = 'supplier';
        $supplier_advance_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $supplier_advance_payment_where,'supplier')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        if($account_type=='supplier'){
          $total_credit+=$supplier_advance_payments;
        }else{
          $total_debit+=$supplier_advance_payments;
        }

        // Op Balance Payments
        if($account_type=='supplier'){
          $supplier_opbalance_payment_where['ats.reference_id'] = $value->acc_supplier_id;
        }else{
          $supplier_opbalance_payment_where['ats.tr_debit_acc'] = $account_id;
        }
        $supplier_opbalance_payment_where['ats.tr_type'] = 'supplier_opbalance_transaction';
        $supplier_opbalance_payment_where['ats.reference_type'] = 'supplier';
        $supplier_opbalance_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $supplier_opbalance_payment_where,'supplier')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        if($account_type=='supplier'){
          $total_credit+=$supplier_opbalance_payments;
        }else{
          $total_debit+=$supplier_opbalance_payments;
        }
      }


      // Purchase Order Payment
      // $purchase_order_payment_where["po.purchase_bill_id"]= 0;
      if($account_type=='supplier'){
        $purchase_order_payment_where["po.supplier_id"]= $value->acc_supplier_id;
      }else{
        $purchase_order_payment_where["ats.tr_debit_acc"]= $account_id;
      }
      $purchase_order_payment_where['ats.reference_type'] = 'purchase_order';
      $purchase_order_payment_where['ats.tr_type'] = 'purchase_order_transaction';
      $purchase_order_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $purchase_order_payment_where,'purchase_order')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      if($account_type=='supplier'){
        $total_credit+=$purchase_order_payments;
      }else{
        $total_debit+=$purchase_order_payments;
      }

      // Purchase Bill Payment
      if($account_type=='supplier'){
        $purchase_bill_payment_where["p.supplier_id"]= $value->acc_supplier_id;
      }else{
        $purchase_bill_payment_where["ats.tr_debit_acc"]= $account_id;
      }
      $purchase_bill_payment_where['ats.reference_type'] = 'purchase_bill';
      $purchase_bill_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
      $purchase_bill_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $purchase_bill_payment_where,'purchase_bill')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      if($account_type=='supplier'){
        $total_credit+=$purchase_bill_payments;
      }else{
        $total_debit+=$purchase_bill_payments;
      }

      if($account_type=='supplier'){
        $purchase_bill_where["p.supplier_id"] = $value->acc_supplier_id;
        $purchase_bills = round($CI->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as total_amount",$purchase_bill_where)->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$purchase_bills;
      }



      if($account_type=='labour'){
        if($account_details->labour_type==1 && $account_details->group_position==1){
          $labour_wage_slip_where['l.labour_group'] = $account_details->labour_group;
          $labour_wage_payment_where['ats.reference_id'] = $account_details->labour_id;
        }else{
          $labour_wage_slip_where['lw.labour_id'] = $account_details->labour_id;
          $labour_wage_payment_where['ats.reference_id'] = $account_details->labour_id;
        }
        // Labour Wage Slips
        $labour_wage_slips = $CI->Labour_wages->select_labour_wages("lw.*,wt.type_name,l.labour_group,l.labour_name,wt.type_name,u.user_alias,wc.work_name,u.user_role as created_user_role_id", $labour_wage_slip_where)->result();

        $labour_wage_payment_where["ats.reference_id"]= $account_head_id;
        $labour_wage_payment_where['ats.reference_type'] = 'labour';
        $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
        $labour_wage_payments = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $labour_wage_payment_where,'labour')->row()->total_amount;
        $total_credit+=$labour_wage_payments;

      }elseif($account_type=='company_accounts' || $account_type=='user_accounts'){
        if($work_id=='' || $work_id=='all'){
          // Labour Wage Payment
          $labour_wage_payment_where['ats.tr_debit_acc'] = $account_id;
          $labour_wage_payment_where['ats.reference_type'] = 'labour';
          $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
          $labour_wage_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $labour_wage_payment_where,'labour')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
          $total_debit+=$labour_wage_payments;
        }
      }

      // Salary Payment
      $salary_payment_where['ats.tr_debit_acc'] = $account_id;
      $salary_payment_where['ats.reference_type'] = 'salary_slip';
      $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
      $salary_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $salary_payment_where,'salary_slip')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_debit+=$salary_payments;
      if($work_id=='' || $work_id=='all'){
        // Employee Payment
        $employee_payment_where['ats.tr_debit_acc'] = $account_id;
        $employee_payment_where['ats.reference_type'] = 'employee';
        $CI->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($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $employee_payment_where,'employee')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$employee_payments;
      }

      // if($account_type=='user_accounts'){
      //   $salary_payment_where2= $salary_payment_where;
      //   unset($salary_payment_where2['ats.tr_debit_acc']);
      //   $salary_payment_where2['ss.employee_id'] = $value->acc_employee_id;
      //   $salary_payment_where2['ats.reference_type'] = 'salary_slip';
      //   $salary_payment_where2['ats.tr_type'] = 'salary_slip_transaction';
      //   $salary_payments_received = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $salary_payment_where2,'salary_slip')->row()->total_amount;
      //   $total_credit+=$salary_payments_received;
      //
      //   $employee_payment_where2 = $employee_payment_where;
      //   unset($employee_payment_where2['ats.tr_debit_acc']);
      //   $employee_payment_where2['ats.reference_id'] = $value->acc_employee_id;
      //   $employee_payment_where2['ats.reference_type'] = 'employee';
      //   $CI->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction')");
      //   $employee_payments_received = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $employee_payment_where2,'employee')->row()->total_amount;
      //   $total_credit+=$employee_payments_received;
      //
      //   $salary_slip_where['ss.employee_id'] = $value->acc_employee_id;
      //   $salary_slips_receivable = $CI->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total_amount",$salary_slip_where)->row()->total_amount;
      //   $total_debit+=$salary_slips_receivable;
      // }
      // Vehicle Payment
      $vehicle_payment_where['ats.tr_debit_acc'] = $account_id;
      $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
      $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
      $vehicle_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_payment_where,'vehicle_work_slip')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_debit+=$vehicle_payments;

      if($work_id=='' || $work_id=='all'){
        // Vehicle OP Balance
        $vehicle_op_balance_payment_where['ats.tr_debit_acc'] = $account_id;
        $vehicle_op_balance_payment_where['ats.tr_type'] = 'vehicle_opbalance_transaction';
        $vehicle_op_balance_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$vehicle_op_balance_payment_where,'vehicle_payment')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$vehicle_op_balance_payments;

        // Vehicle Expense Bill
        $vehicle_expense_where['ats.tr_debit_acc'] = $account_id;
        $vehicle_expense_where['ats.reference_type'] = 'vehicle_bill';
        $vehicle_expense_where['ats.tr_type'] = 'vehicle_bill_transaction';
        $vehicle_expense_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_expense_where,'vehicle_bill')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$vehicle_expense_payments;

        // Vehicle Renewal
        $vehicle_payment_where['ats.tr_debit_acc'] = $account_id;
        $vehicle_payment_where['ats.reference_type'] = 'vehicle';
        $vehicle_payment_where['ats.tr_type'] = 'vehicle_renewal_transaction';
        $vehicle_renewal_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_payment_where,'vehicle_work_slip')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$vehicle_renewal_payments;
      }
      // Subc
      // Subcontract Payment
      $subcontract_payment_where['ats.tr_debit_acc'] = $account_id;
      if($work_id!='' && $work_id!='all'){
        $CI->db->where("((ats.reference_type='subcontract_bill' and sc.work_id=$work_id) or (ats.reference_type='subcontract' and sc2.work_id=$work_id))");
      }
      $CI->db->where("((ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction'))");
      $CI->db->where("((ats.reference_type='subcontract_bill' or ats.reference_type='subcontract'))");
      $sub_contract_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $subcontract_payment_where,'subcontract_transactions')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_debit+=$sub_contract_payments;


      if($work_id=='' || $work_id=='all'){
        if($account_type=='user_accounts'){
          $user_payment_receipt_where['ats.tr_credit_acc'] = $account_id;
          $user_payment_return_where['ats.tr_debit_acc'] = $account_id;
          $user_payment_return_receipt_where['ats.tr_credit_acc'] = $account_id;
          $user_payment_released_where['ats.tr_debit_acc'] = $account_id;
        }else{
          $user_payment_return_receipt_where['ats.tr_debit_acc'] = $account_id;
          $user_payment_receipt_where['ats.tr_debit_acc'] = $account_id;
          $user_payment_return_where['ats.tr_credit_acc'] = $account_id;
          $user_payment_released_where['ats.tr_credit_acc'] = $account_id;
        }
        // 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 = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_receipt_where,'user_payment_receipt')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        if($account_type=='user_accounts'){
          $total_credit+=$user_payment_receipts;
        }else{
          $total_debit+=$user_payment_receipts;
        }

        $user_payment_released_where['ats.tr_type'] = 'user_payment_receipt_transaction';
        $user_payment_released_where['ats.reference_type'] = 'user_account_head';
        $user_payment_released = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_released_where,'user_payment_receipt')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        if($account_type=='user_accounts'){
          $total_debit+=$user_payment_released;
        }else{
          $total_credit+=$user_payment_released;
        }

        // 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 = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_return_where,'user_payment_return')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        if($account_type=='user_accounts'){
          $total_debit+=$user_payment_returns;
        }else{
          $total_credit+=$user_payment_returns;
        }
        // User Payment Return Receipt
        $user_payment_return_receipt_where['ats.reference_type'] = 'user_account_head';
        $user_payment_return_receipt_where['ats.tr_type'] = 'user_payment_return_transaction';
        $user_payment_return_receipt = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_payment_return_receipt_where,'user_payment_return')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        if($account_type=='user_accounts'){
          $total_credit+=$user_payment_return_receipt;
        }else{
          $total_debit+=$user_payment_return_receipt;
        }

      }
      // User Commission Payment
      $user_commission_payment_where['ats.tr_debit_acc'] = $account_id;
      $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
      $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
      $user_commission_payment = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $user_commission_payment_where,'user_commission')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_debit+=$user_commission_payment;

      // Loan Payment
      $loan_payment_where['ats.tr_debit_acc'] = $account_id;
      $loan_payment_where['ats.reference_type'] = 'loan';
      $loan_payment_where['ats.tr_type'] = 'loan_transaction';
      $loan_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_payment_where,'loan')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_debit+=$loan_payments;

      if($work_id=='' || $work_id=='all'){
        //Tool Purchase
        $tool_purchase_where['ats.tr_debit_acc'] = $account_id;
        $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
        $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
        $tool_purchase = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_purchase_where,'tool_purchase')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$tool_purchase;

        // Travel Allowance
        if($CI->config->item('User_include_travel_allowance_in_ledger')==1){
          $travel_allowance_where['ats.tr_debit_acc'] = $account_id;
          $travel_allowance_where['ats.reference_type'] = 'travel_allowance';
          $travel_allowance_where['ats.tr_type'] = 'travel_allowance_transaction';
          $travel_allowance = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $travel_allowance_where,'travel_allowance')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
          $total_debit+=$travel_allowance;
        }


        // Account transfer credit
        $account_transfer_where_credit['ats.tr_credit_acc'] = $account_id;
        $account_transfer_where_credit['ats.tr_type'] = 'account_transfer';
        $account_transfer_result_credit = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $account_transfer_where_credit)->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_credit+=$account_transfer_result_credit;

        // Account transfer Debit
        $account_transfer_where_debit['ats.tr_debit_acc'] = $account_id;
        $account_transfer_where_debit['ats.tr_type'] = 'account_transfer';
        $account_transfer_result_debit = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $account_transfer_where_debit)->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
        $total_debit+=$account_transfer_result_debit;

      }

      //Tool Payment
      $tool_payment_where['ats.tr_debit_acc'] = $account_id;
      $tool_payment_where['ats.tr_type'] = 'tool_payment_transaction';
      if($work_id!='' && $work_id!='all'){
        $CI->db->where("((tr.rented_to='$work_id' or tt.transfer_to='$work_id'))");
      }
      $CI->db->where('((ats.reference_type="tool_transfer" or ats.reference_type="tool_rent"))');
      $tool_payments = round($CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $tool_payment_where,'tool')->row()->total_amount,$CI->config->item('Currency_amount_decimals_2'));
      $total_debit+=$tool_payments;


      if($work_id!='' && $work_id!='all'){
        $balance = ($total_credit-$total_debit);
      }else{
        $balance = ($total_credit-$total_debit);
        if($value->account_type==12){
          $balance -= $value->op_balance;
        }else{
          $balance += $value->op_balance;
        }
      }
      $current_balance += $balance;
    }
    return round($current_balance,$CI->config->item('Currency_amount_decimals_2'));
  }
  public function calculate_user_summary($user_account_id,$start_date='',$end_date='',$data_type="user_account_transactions",$user_id=''){
    $CI =& get_instance();
    if($data_type=='user_created_transactions'){
      $vehicle_opbalance_payment_where['ats.created_by'] =$vehicle_renewal_payment_where['ats.created_by'] = $vehicle_expense_where['ats.created_by'] = $supplier_opbalance_payment_where['ats.created_by'] = $user_payment_released_where['ats.created_by'] = $transferred_out_where['ats.created_by'] = $transferred_in_where['ats.created_by'] = $travel_allowance_where['ats.created_by'] = $tool_payment_where['ats.created_by'] = $loan_payment_where['ats.created_by'] = $salary_payment_where['ats.created_by'] = $employee_payment_where['ats.created_by'] = $vehicle_payment_where['ats.created_by'] = $supplier_advance_payment_where['ats.created_by'] = $user_commission_payment_where['ats.created_by'] = $other_income_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'] = $subcontract_payment_where['ats.created_by'] = $user_payment_released_where['ats.created_by'] = $user_payment_returned_where['ats.created_by'] = $user_payment_received_where['ats.created_by'] = $user_id;
    }else{
      $vehicle_opbalance_payment_where['ats.tr_debit_acc'] = $vehicle_renewal_payment_where['ats.tr_debit_acc'] = $vehicle_expense_where['ats.tr_debit_acc'] = $supplier_opbalance_payment_where['ats.tr_debit_acc'] = $user_payment_released_where['ats.tr_debit_acc'] = $transferred_out_where['ats.tr_debit_acc'] = $transferred_in_where['ats.tr_credit_acc'] = $travel_allowance_where['ats.tr_debit_acc'] = $tool_payment_where['ats.tr_debit_acc'] = $loan_payment_where['ats.tr_debit_acc'] = $salary_payment_where['ats.tr_debit_acc'] = $employee_payment_where['ats.tr_debit_acc'] = $vehicle_payment_where['ats.tr_debit_acc'] = $supplier_advance_payment_where['ats.tr_debit_acc'] = $user_commission_payment_where['ats.tr_credit_acc'] = $other_income_where['ats.tr_credit_acc'] = $labour_wage_payment_where['ats.tr_debit_acc'] = $client_payment_where['ats.tr_credit_acc'] = $other_expense_where['ats.tr_debit_acc'] = $office_expense_where['ats.tr_debit_acc'] = $purchase_order_payment_where["ats.tr_debit_acc"] = $purchase_payment_where['ats.tr_debit_acc'] = $subcontract_payment_where['ats.tr_debit_acc'] = $user_payment_released_where['ats.tr_debit_acc'] = $user_payment_returned_where['ats.tr_debit_acc'] = $user_payment_received_where['ats.tr_credit_acc'] = $user_account_id;
    }

    if($start_date!=''){
      $client_payment_where['ats.tr_date >='] = $start_date;
      $purchase_order_payment_where['ats.tr_date >='] = $start_date;
      $purchase_payment_where['ats.tr_date >='] = $start_date;
      $subcontract_payment_where['ats.tr_date >='] = $start_date;
      $other_expense_where['ats.tr_date >='] = $start_date;
      $vehicle_expense_where['ats.tr_date >='] = $start_date;
      $vehicle_opbalance_payment_where['ats.tr_date >='] = $start_date;
      $labour_wage_payment_where['ats.tr_date >='] = $start_date;
      $user_payment_received_where['ats.tr_date >='] = $start_date;
      $user_payment_released_where['ats.tr_date >='] = $start_date;
      $user_payment_returned_where['ats.tr_date >='] = $start_date;
      $user_commission_payment_where['ats.tr_date >='] = $start_date;
      $other_income_where['ats.tr_date >='] = $start_date;
      $supplier_advance_payment_where['ats.tr_date >='] = $start_date;
      $loan_payment_where['ats.tr_date >='] = $start_date;
      $salary_payment_where['ats.tr_date >='] = $start_date;
      $employee_payment_where['ats.tr_date >='] = $start_date;
      $vehicle_payment_where['ats.tr_date >='] = $start_date;
      $vehicle_renewal_payment_where['ats.tr_date >='] = $start_date;
      $tool_payment_where['ats.tr_date >='] = $start_date;
      $travel_allowance_where['ats.tr_date >='] = $start_date;
      $transferred_in_where['ats.tr_date >='] = $start_date;
      $transferred_out_where['ats.tr_date >='] = $start_date;
      $supplier_opbalance_payment_where['ats.tr_date >='] = $start_date;
    }
    if($end_date!=''){
      $client_payment_where['ats.tr_date <='] = $end_date;
      $purchase_order_payment_where['ats.tr_date <='] = $end_date;
      $purchase_payment_where['ats.tr_date <='] = $end_date;
      $subcontract_payment_where['ats.tr_date <='] = $end_date;
      $other_expense_where['ats.tr_date <='] = $end_date;
      $vehicle_expense_where['ats.tr_date <='] = $end_date;
      $vehicle_opbalance_payment_where['ats.tr_date <='] = $end_date;
      $labour_wage_payment_where['ats.tr_date <='] = $end_date;
      $user_payment_received_where['ats.tr_date <='] = $end_date;
      $user_payment_released_where['ats.tr_date <='] = $end_date;
      $user_payment_returned_where['ats.tr_date <='] = $end_date;
      $user_commission_payment_where['ats.tr_date <='] = $end_date;
      $other_income_where['ats.tr_date <='] = $end_date;
      $supplier_advance_payment_where['ats.tr_date <='] = $end_date;
      $loan_payment_where['ats.tr_date <='] = $end_date;
      $salary_payment_where['ats.tr_date <='] = $end_date;
      $employee_payment_where['ats.tr_date <='] = $end_date;
      $vehicle_payment_where['ats.tr_date <='] = $end_date;
      $vehicle_renewal_payment_where['ats.tr_date <='] = $end_date;
      $tool_payment_where['ats.tr_date <='] = $end_date;
      $travel_allowance_where['ats.tr_date <='] = $end_date;
      $transferred_in_where['ats.tr_date <='] = $end_date;
      $transferred_out_where['ats.tr_date <='] = $end_date;
      $supplier_opbalance_payment_where['ats.tr_date <='] = $end_date;
    }
    $json_data['total_opbalance'] = $CI->Account->select_account_heads("ah.op_balance", array("acc_head_id"=>$user_account_id), 'user')->row()->op_balance;

    //      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'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$purchase_order_payment_where,'purchase_order')->row()->total_amount;

    //      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'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$purchase_payment_where,'purchase_bill')->row()->total_amount;

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

    //      Select Subcontract Payment
    $CI->db->where("(ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction')");
    $CI->db->where("(ats.reference_type='subcontract_bill' or ats.reference_type='subcontract')");
    $json_data['total_subcontract_payment'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$subcontract_payment_where,'subcontract_transactions')->row()->total_amount;

    //      Select Work Expense Payment
    $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
    $other_expense_where['ats.reference_type'] = 'expense_bill';
    $json_data['total_other_expense'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$other_expense_where,'expense_bill')->row()->total_amount;

    //      Select Vehicle Expense Payment
    $vehicle_expense_where['ats.tr_type'] = 'vehicle_bill_transaction';
    $vehicle_expense_where['ats.reference_type'] = 'vehicle_bill';
    $json_data['total_vehicle_expense'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$vehicle_expense_where,'vehicle_bill')->row()->total_amount;

    //      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']	= $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$labour_wage_payment_where,'labour')->row()->total_amount;

    $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
    $client_payment_where['ats.reference_type'] = 'project';
    $json_data['total_client_payments_received'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$client_payment_where,'client_payment')->row()->total_amount;

    $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_petty_payments_received'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$user_payment_received_where,'user_payment_receipt')->row()->total_amount;

    $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_petty_payments_released'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$user_payment_released_where,'user_payment_receipt')->row()->total_amount;

    $user_payment_returned_where['ats.tr_type'] = 'user_payment_return_transaction';
    $user_payment_returned_where['ats.reference_type'] = 'user_account_head';
    $json_data['total_user_payments_returned'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$user_payment_returned_where,'user_payment_return')->row()->total_amount;

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

    $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
    $CI->db->where("(ats.reference_type='income_receipt' or (ats.reference_type='client' and ats.invoice_id!=0))");
    $json_data['other_income_received'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$other_income_where,'income_receipt')->row()->total_amount;

    $supplier_advance_payment_where['ats.tr_type'] = 'supplier_advance_transaction';
    $supplier_advance_payment_where['ats.reference_type'] = 'supplier';
    $json_data['supplier_advance_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $supplier_advance_payment_where,'supplier')->row()->total_amount;

    $supplier_opbalance_payment_where['ats.tr_type'] = 'supplier_opbalance_transaction';
    $supplier_opbalance_payment_where['ats.reference_type'] = 'supplier';
    $json_data['supplier_opbalance_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $supplier_opbalance_payment_where,'supplier')->row()->total_amount;

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

    $loan_payment_where['ats.reference_type'] = 'loan';
    $loan_payment_where['ats.tr_type'] = 'loan_transaction';
    $json_data['loan_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $loan_payment_where,'loan')->row()->total_amount;

    $travel_allowance_where['ats.reference_type'] = 'travel_allowance';
    $travel_allowance_where['ats.tr_type'] = 'travel_allowance_transaction';
    $json_data['travel_allowance'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $travel_allowance_where,'travel_allowance')->row()->total_amount;

    // Account transfer In
    // $transferred_in_where['ats.tr_credit_acc'] = $user_account_id;
    $transferred_in_where['ats.tr_type'] = 'account_transfer';
    $json_data['total_transferred_in_amount'] = $CI->Account->select_account_transfer("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transferred_in_where)->row()->total_amount;

    // Account transfer Out
    // $transferred_out_where['ats.tr_debit_acc'] = $user_account_id;
    $transferred_out_where['ats.tr_type'] = 'account_transfer';
    $json_data['total_transferred_out_amount'] = $CI->Account->select_account_transfer("COALESCE(SUM(ats.tr_amount),0) as total_amount",$transferred_out_where)->row()->total_amount;


    // Salary Payment
    $salary_payment_where['ats.reference_type'] = 'salary_slip';
    $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
    $json_data['salary_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $salary_payment_where,'salary_slip')->row()->total_amount;

    // Employee Payment
    $employee_payment_where['ats.reference_type'] = 'employee';
    $CI->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction' or ats.tr_type='employee_bata_transaction')");
    $json_data['employee_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $employee_payment_where,'employee')->row()->total_amount;

    // Vehicle Work Slip Payment
    $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
    $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
    $json_data['vehicle_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_payment_where,'vehicle_work_slip')->row()->total_amount;

    // Vehicle OP Balance Payment
    // $vehicle_opbalance_payment_where['ats.reference_type'] = 'vehicle';
    // $vehicle_opbalance_payment_where['ats.tr_type'] = 'vehicle_renewal_transaction';
    // $json_data['vehicle_opbalance_payment'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_opbalance_payment_where,'vehicle')->row()->total_amount;

    // Vehicle Renewal Payment
    // $vehicle_renewal_payment_where['ats.reference_type'] = 'vehicle';
    // $vehicle_renewal_payment_where['ats.tr_type'] = 'vehicle_opbalance_transaction';
    // $json_data['vehicle_renewal_payments'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount", $vehicle_renewal_payment_where,'vehicle')->row()->total_amount;

    $json_data['total_user_payments_received'] = round($json_data['total_client_payments_received']+$json_data['other_income_received']+$json_data['total_user_petty_payments_received']+$json_data['total_transferred_in_amount'],0);
    $json_data['total_expense_amount']=round($json_data['total_vehicle_expense']+$json_data['supplier_opbalance_payments']+$json_data['total_user_petty_payments_released']+$json_data['total_transferred_out_amount']+$json_data['tool_payments']+$json_data['loan_payments']+$json_data['salary_payments']+$json_data['employee_payments']+$json_data['vehicle_payments']+$json_data['supplier_advance_payments']+$json_data['total_subcontract_payment']+$json_data['total_purchase_payment']+$json_data['total_other_expense']+$json_data['total_labour_wage_payments'],0);
    if($CI->config->item('User_include_travel_allowance_in_ledger')==1){
      $json_data['total_expense_amount'] += $json_data['travel_allowance'];
    }
    foreach ($json_data as $key => $value) {
      $json_data[$key] = round((float)$value,0);
    }
    $total_balance = (float)$json_data['total_opbalance']+(float)$json_data['total_user_payments_received']-(float)$json_data['total_expense_amount']-(float)$json_data['total_user_payments_returned'];
    // $json_data['total_balance'] =  sprintf("%+f",round($total_balance,0));
    $json_data['total_balance'] =  round($total_balance,0);
    return $json_data;
  }

  public function get_account_statement($data_return_type,$account,$start_date='',$end_date='',$user_role='',$user_id='',$employee_account_type='',$account_category=''){
    $CI =& get_instance();
    $table_body="";
    $j=$i=0;$sl_no=1;
    $json_data = $json_data['transactions'] = $json_data['selected_account_data'] = $data = $transactions = $transactions_summary = array();

    $op_balance = $total_debit = $total_credit = $total_balance = 0;
    if (isset($account) && $account != '') {
      $json_data['selected_account'] = $account;
      $account1 = explode('_',$account);
      $json_data['selected_account_prefix'] = $account_prefix = $account1[0];
      $account_id = $account1[1];
      if($account_prefix=='acchead'){
        if($user_role=='supervisor'){
          $acc_head_where['ah.acc_user_id'] = $user_id;
        }
        $acc_head_where['ah.acc_head_id'] = $account_id;
        $account_details = $CI->Account->select_account_heads("*",$acc_head_where);
      }elseif($account_prefix=='expensetype'){
        $acc_head_where['ah.acc_head_id'] = $account_id;
        $account_details = $CI->Account->select_account_heads("*",$acc_head_where);
      }elseif($account_prefix=='paymentmode'){
        $account_id = str_replace('&',' ',$account_id);
        $acc_head_where['ah.acc_head_id'] = 31;//Payment Mode Head
        $account_details = $CI->Account->select_account_heads("*",$acc_head_where);
      }elseif($account_prefix=='vehicleconsignee'){
        $consignee_where['consignee_id'] = $account_id;
        $account_details = $CI->Vehicle->select_consignee("",$consignee_where);
      }elseif($account_prefix=='vehicle'){
        $vehicle_where['v.id'] = $account_id;
        $account_details = $CI->Vehicle->select_vehicle("",$vehicle_where);
      }elseif($account_prefix=='labour'){
        $labour_where['labour_id'] = $account_id;
        $account_details = $CI->Labour->select_labours("l.*,wt.type_name",$labour_where);
      }elseif($account_prefix=='client'){
        $client_where['client_id'] = $account_id;
        $account_details = $CI->Client->select_client("",$client_where);
      }elseif($account_prefix=='contractor'){
        $contractor_where['contractor_id'] = $account_id;
        $account_details = $CI->Subcontracts->select_contractors("",$contractor_where);
      }elseif($account_prefix=='employee'){
        // if($user_role=='supervisor'){
        //   $employee_where['e.user_id'] = $user_id;
        // }
        // $employee_where['employee_id'] = $account_id;
        // $account_details = $CI->Employee->select_employee("e.*",$employee_where);
      }
      if($account_details->num_rows()==1){
        $account_details = $account_details->row();
        $account_details->current_op_balance = 0;
        $account_type = 'company_accounts';
        $account_type1 = '';
        if($account_prefix=='acchead'){
          if($account_details->acc_employee_id!=0){
            $account_type = 'user_accounts';
            $account_type1 = 'user';
            $account_details->acc_head_name = $account_details->acc_head_name." - Employee ".$employee_account_type." A/C";
            if($employee_account_type=='Salary'){
              $account_details->op_balance = $CI->Account->select_account_heads("e.op_balance",$acc_head_where,$account_type1)->row()->op_balance;
            }
          }elseif($account_details->acc_supplier_id!=0){
            $account_type = 'supplier';
            $op_balance = -$account_details->op_balance;
          }
        }elseif($account_prefix=='expensetype'){
          $account_details->op_balance = 0;
          $account_type = 'expense_type';
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
        }elseif($account_prefix=='paymentmode'){
          $account_details->acc_head_name = $account_id;
          $account_details->op_balance = 0;
          $account_type = 'payment_mode';
          $account_details->account_type = 32;
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
        }elseif($account_prefix=='vehicleconsignee'){
          $account_details->op_balance = 0;
          $account_details->acc_head_name = $account_details->consignee_name;
          $account_type = 'vehicle_consignee';
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
        }elseif($account_prefix=='vehicle'){
          $account_details->op_balance = 0;
          $account_details->acc_head_name = $account_details->vehicle_no;
          $account_type = 'vehicle';
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
        }elseif($account_prefix=='labour'){
          $account_details->op_balance = $account_details->opening_balance;
          $account_details->acc_head_name = ($account_details->labour_name)." (Labour)";
          $account_type = 'labour';
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
          $op_balance = -$account_details->op_balance;
        }elseif($account_prefix=='client'){
          $account_details->op_balance = 0;
          $account_details->acc_head_name = $account_details->client_name;
          $account_type = 'client';
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
        }elseif($account_prefix=='contractor'){
          $account_details->op_balance = 0;
          $account_details->acc_head_name = $account_details->contractor_name;
          $account_type = 'contractor';
          $account_details->acc_supplier_id = 0;
          $account_details->acc_employee_id = 0;
        }elseif($account_prefix=='employee'){
          // $account_details->acc_head_name = $account_details->employee_name." - Employee ".$employee_account_type." A/C";
          // $account_type = 'employee';
          // $account_details->acc_supplier_id = 0;
          // $account_details->acc_employee_id = 0;
        }
        $op_balance = $account_details->op_balance;
        $json_data['selected_account_type'] = $account_type;
        $json_data['selected_account_id'] = $account_id;

        $subcontract_bills_where = $travel_allowance_where = $client_invoice_where = $vehicle_op_balance_payment_where = $bills_where = $labour_wage_slip_where=$salary_slip_where=$purchase_bill_where=$sales_invoice_receipt_where = $employee_payment_where = $user_commission_payment_where = $tool_payment_where = $tool_purchase_where = $account_transfer_where_credit = $account_transfer_where_debit = $opening_balance_payment_where = $advance_payment_where = $loan_receipt_where = $loan_payment_where = $user_receipt_where = $user_return_where = $salary_payment_where = $other_income_where = $user_payment_receipts_where = $user_payment_returns_where = $client_payment_where = $purchase_payment_where = $purchase_order_payment_where = $subcontract_payment_where = $other_expense_where = $office_expense_where = $labour_wage_payment_where = $purchase_bill_payment_where = array();
        if($CI->config->item('user_data_access_view_all')!=1 && $account_prefix!='acchead'){
          $vehicle_op_balance_payment_where['ats.created_by'] = $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'] = $travel_allowance_where['ats.created_by'] = $client_payment_where['ats.created_by'] = $other_expense_where['ats.created_by'] = $vehicle_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_receipts_where['ats.created_by'] = $user_payment_returns_where['ats.created_by'] = $advance_payment_where['ats.created_by'] =  $opening_balance_payment_where['ats.created_by'] = $account_transfer_where_credit['ats.created_by'] = $account_transfer_where_debit['ats.created_by'] = $user_id;
        }
        if (isset($start_date) && $start_date != '') {
          $start_date = date('Y-m-d', strtotime($start_date));
          $travel_allowance_where['ats.tr_date >='] = $start_date;
          $client_payment_where['ats.tr_date >='] = $start_date;
          $user_receipt_where['ats.tr_date >='] = $start_date;
          $user_return_where['ats.tr_date >='] = $start_date;
          $user_payment_receipts_where['ats.tr_date >='] = $start_date;
          $user_payment_returns_where['ats.tr_date >='] = $start_date;
          $other_income_where['ats.tr_date >='] = $start_date;
          $purchase_order_payment_where['ats.tr_date >='] = $start_date;
          $purchase_bill_payment_where['ats.tr_date >='] = $start_date;
          $labour_wage_payment_where['ats.tr_date >='] = $start_date;
          $subcontract_payment_where['ats.tr_date >='] = $start_date;
          $other_expense_where['ats.tr_date >='] = $start_date;
          $vehicle_expense_where['ats.tr_date >='] = $start_date;
          $loan_payment_where['ats.tr_date >='] = $start_date;
          $advance_payment_where['ats.tr_date >='] = $start_date;
          $loan_receipt_where['ats.tr_date >='] = $start_date;
          $salary_payment_where['ats.tr_date >='] = $start_date;
          $vehicle_payment_where['ats.tr_date >='] = $start_date;
          $vehicle_op_balance_payment_where['ats.tr_date >='] = $start_date;
          $opening_balance_payment_where['ats.tr_date >='] = $start_date;
          $account_transfer_where_credit['ats.tr_date >='] = $start_date;
          $account_transfer_where_debit['ats.tr_date >='] = $start_date;
          $tool_purchase_where['ats.tr_date >='] = $start_date;
          $tool_payment_where['ats.tr_date >='] = $start_date;
          $user_commission_payment_where['ats.tr_date >='] = $start_date;
          $employee_payment_where['ats.tr_date >='] = $start_date;
          $sales_invoice_receipt_where['ats.tr_date >='] = $start_date;
          $purchase_bill_where['p.purchase_date >='] = $start_date;
          $salary_slip_where['ss.month >='] = $start_date;
          $labour_wage_slip_where['lw.work_date >='] = $start_date;
          $bills_where['b.bill_date >='] = $start_date;
          $subcontract_bills_where['b.bill_date >='] = $start_date;
          $client_invoice_where['i.invoice_date >='] = $start_date;
          // Get Opening Balance till this Date
          if($data_return_type!='trial_balance'){
            $day_op_balance_end_date = date ("Y-m-d", strtotime("-1 day", strtotime($start_date)));
            $current_op_balance = round($CI->accounts_management->get_account_statement('trial_balance',$account, '',$day_op_balance_end_date,$user_role,$user_id,$employee_account_type),$CI->config->item('Currency_amount_decimals_2'));
            if($data_return_type=='ledger'){
              $transactions[$i]['date'] = date('Y-m-d', strtotime($start_date));
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Opening Balance on ".date('d-m-Y',strtotime($start_date));
              if($current_op_balance<0 && $account_type=='client'){
                $transactions[$i]['debit'] = abs(round($current_op_balance,$CI->config->item('Currency_amount_decimals_2')));
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
              }else{
                $transactions[$i]['debit'] = '';
                $transactions[$i]['credit'] = round($current_op_balance,$CI->config->item('Currency_amount_decimals_2'));
                $transactions[$i]['type'] = 'credit';
              }
              $i++;
            }else{
              $transactions_summary[$j]['description'] = "Opening Balance on ".date('d-m-Y',strtotime($start_date));
              $transactions_summary[$j]['amount'] = round($current_op_balance,$CI->config->item('Currency_amount_decimals_2'));
              if($current_op_balance<0 && $account_type=='client'){
                $transactions_summary[$j]['type'] = 'debit';
              }else{
                $transactions_summary[$j]['type'] = 'credit';
              }
              $j++;
            }
          }
        }else{
          $op_balance = $account_details->op_balance;
          if($account_prefix=='acchead'){
            $op_balance_date = date('Y-m-d',strtotime($account_details->op_balance_date));
          }else{
            if(isset($account_details->created_date)){
              $op_balance_date = date('Y-m-d',strtotime($account_details->created_date));
            }
          }
          if(isset($op_balance_date)){
            if($data_return_type=='ledger'){
              $transactions[$i]['date'] = $op_balance_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Account Opening Balance [on A/C Creation - ".date('d-m-Y',strtotime($op_balance_date))."]";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $op_balance;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }else{
              $transactions_summary[$j]['description'] = "Account Opening Balance [on A/C Creation - ".date('d-m-Y',strtotime($op_balance_date))."]";
              $transactions_summary[$j]['amount'] = $op_balance;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }
          }
        }
        if (isset($end_date) && $end_date != '') {
          $end_date = date('Y-m-d', strtotime($end_date));
          $travel_allowance_where['ats.tr_date <='] = $end_date;
          $client_payment_where['ats.tr_date <='] = $end_date;
          $user_receipt_where['ats.tr_date <='] = $end_date;
          $user_return_where['ats.tr_date <='] = $end_date;
          $user_payment_receipts_where['ats.tr_date <='] = $end_date;
          $user_payment_returns_where['ats.tr_date <='] = $end_date;
          $other_income_where['ats.tr_date <='] = $end_date;
          $purchase_order_payment_where['ats.tr_date <='] = $end_date;
          $purchase_bill_payment_where['ats.tr_date <='] = $end_date;
          $labour_wage_payment_where['ats.tr_date <='] = $end_date;
          $subcontract_payment_where['ats.tr_date <='] = $end_date;
          $other_expense_where['ats.tr_date <='] = $end_date;
          $vehicle_expense_where['ats.tr_date <='] = $end_date;
          $loan_payment_where['ats.tr_date <='] = $end_date;
          $loan_receipt_where['ats.tr_date <='] = $end_date;
          $salary_payment_where['ats.tr_date <='] = $end_date;
          $vehicle_payment_where['ats.tr_date <='] = $end_date;
          $vehicle_op_balance_payment_where['ats.tr_date <='] = $end_date;
          $advance_payment_where['ats.tr_date <='] = $end_date;
          $opening_balance_payment_where['ats.tr_date <='] = $end_date;
          $account_transfer_where_credit['ats.tr_date <='] = $end_date;
          $account_transfer_where_debit['ats.tr_date <='] = $end_date;
          $tool_purchase_where['ats.tr_date <='] = $end_date;
          $tool_payment_where['ats.tr_date <='] = $end_date;
          $user_commission_payment_where['ats.tr_date <='] = $end_date;
          $employee_payment_where['ats.tr_date <='] = $end_date;
          $sales_invoice_receipt_where['ats.tr_date <='] = $end_date;
          $purchase_bill_where['p.purchase_date <='] = $end_date;
          $salary_slip_where['ss.month <='] = $end_date;
          $labour_wage_slip_where['lw.work_date <='] = $end_date;
          $bills_where['b.bill_date <='] = $end_date;
          $subcontract_bills_where['b.bill_date <='] = $end_date;
          $client_invoice_where['i.invoice_date <='] = $end_date;
        }

        if(($account_prefix=='acchead' || $account_prefix=='paymentmode') && ($account_details->acc_employee_id==0 || ($account_details->acc_employee_id!=0 && $employee_account_type=='Petty Cash')) && ($account_details->account_type==32 || $account_details->account_type==36 || $account_details->account_type==12)){ //For Cash/Bank Accounts
          // User Payment Receipts
          if($account_prefix=='paymentmode'){
            $user_receipt_where['ats.tr_mode'] = $account_id;
          }else{
            $user_receipt_where['ats.tr_credit_acc'] = $account_id;
          }
          $user_receipt_where['ats.tr_type'] = 'user_payment_receipt_transaction';
          $user_receipt_where['ats.reference_type'] = 'user_account_head';
          if($data_return_type=='ledger'){
            $user_payments_received = $CI->Account->select_account_transaction("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 => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By ".$value->created_user." (User Payment Receipt)";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "User Payment Receipt";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$user_receipt_where,'user_payment_receipt')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }


          // User Payment Returns
          if($account_prefix=='paymentmode'){
            $user_return_where['ats.tr_mode'] = $account_id;
          }else{
            $user_return_where['ats.tr_debit_acc'] = $account_id;
          }
          $user_return_where['ats.tr_type'] = 'user_payment_return_transaction';
          $user_return_where['ats.reference_type'] = 'user_account_head';
          if($data_return_type=='ledger'){
            $user_payments_received = $CI->Account->select_account_transaction("ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u4.user_alias as returned_user,ah2.acc_head_name as credited_account",$user_return_where,'user_payment_return')->result();
            foreach ($user_payments_received as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->credited_account." (User Payment Return)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "User Payment Return";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$user_return_where,'user_payment_return')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Client Payment Receipts
          if($account_prefix=='paymentmode'){
            $client_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $client_payment_where['ats.tr_credit_acc'] = $account_id;
          }
          $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
          $client_payment_where['ats.reference_type'] = 'project';
          if($data_return_type=='ledger'){
            $client_payments_received = $CI->Account->select_account_transaction("ats.invoice_id,ats.tr_amount,ats.tr_date,c.client_name,wc.work_name",$client_payment_where,'client_payment')->result();
            foreach ($client_payments_received as $key => $value) {
              if($value->invoice_id!=0){
                $payment_type=" (Invoice Receipt on Invoice #".$value->invoice_id.")";
              }else{
                $payment_type=" (Advance Receipt)";
              }
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By ".$value->client_name." For Work :".($value->work_name).$payment_type;
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Client Payment Receipt (Advance & Invoice)";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$client_payment_where,'client_payment')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
          // Other Income Receipts
          if($account_prefix=='paymentmode'){
            $other_income_where['ats.tr_mode'] = $account_id;
          }else{
            $other_income_where['ats.tr_credit_acc'] = $account_id;
          }
          $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
          $CI->db->where("(ats.reference_type='income_receipt' or (ats.reference_type='client' and ats.invoice_id!=0))");
          if($data_return_type=='ledger'){
            $other_income_received = $CI->Account->select_account_transaction("c.client_name,ats.invoice_id,ats.tr_amount,ats.tr_date,ats.tr_description",$other_income_where,'income_receipt')->result();
            foreach ($other_income_received as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($value->invoice_id!=0){
                $transactions[$i]['description'] = "By ".$value->client_name." (Other Income Invoice #".$value->invoice_id.")";
              }else{
                $transactions[$i]['description'] = "By ".$value->tr_description." (Income Receipt)";
              }
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Other Income Receipt";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$other_income_where,'income_receipt')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
          // Sales Invoice Receipts
          if($account_prefix=='paymentmode'){
            $sales_invoice_receipt_where['ats.tr_mode'] = $account_id;
          }else{
            $sales_invoice_receipt_where['ats.tr_credit_acc'] = $account_id;
          }
          $sales_invoice_receipt_where['ats.tr_type'] = 'sales_invoice_transaction';
          $sales_invoice_receipt_where['ats.reference_type'] = 'sales_invoice';
          if($data_return_type=='ledger'){
            $sales_invoice_received = $CI->Account->select_account_transaction("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 => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By Invoice #".$value->reference_id." [".$value->customer_name."] (Ware House Sales)";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Sales Invoice Receipt";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$sales_invoice_receipt_where,'sales_invoice')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
          // Loan Receipts
          if($account_prefix=='paymentmode'){
            $loan_receipt_where['ats.tr_mode'] = $account_id;
          }else{
            $loan_receipt_where['ats.tr_credit_acc'] = $account_id;
          }
          $loan_receipt_where['ats.tr_type'] = 'loan_receipt_transaction';
          $loan_receipt_where['ats.reference_type'] = 'loan';
          if($data_return_type=='ledger'){
            $loan_received = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ats.tr_description",$loan_receipt_where,'loan')->result();
            foreach ($loan_received as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By ".$value->tr_description." (Loan Receipt - Loan #".$value->reference_id.")";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Loan Receipt";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$loan_receipt_where,'loan')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }


          // Opening Balance Payments
          if($account_prefix=='paymentmode'){
            $opening_balance_payment_where["ats.tr_mode"]= $account_id;
          }else{
            if($account_details->acc_supplier_id!=0){
              $opening_balance_payment_where["ats.reference_id"]= $account_details->acc_supplier_id;
            }else{
              $opening_balance_payment_where["ats.tr_debit_acc"]= $account_id;
            }
          }
          $opening_balance_payment_where['ats.tr_type'] = 'supplier_opbalance_transaction';
          $opening_balance_payment_where['ats.reference_type'] = 'supplier';

          if($account_details->acc_supplier_id!=0){
            $purchase_bill_where["p.supplier_id"] = $account_details->acc_supplier_id;
            if($data_return_type=='ledger'){
              $purchase_bills = $CI->Purchases->select_purchase("p.*,sl.location_name",$purchase_bill_where)->result();
              foreach ($purchase_bills as $key => $value) {
                $transactions[$i]['date'] = $value->purchase_date;
                $transactions[$i]['account'] = 'Purchase Bill';
                $transactions[$i]['description'] = "Payable Purchase Bill #".$value->purchase_id." For :".$value->location_name;
                $transactions[$i]['debit'] = '';
                $transactions[$i]['credit'] = $value->bill_payable_amount;
                $transactions[$i]['type'] = 'credit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Payable Purchase Bill";
              $transactions_summary[$j]['amount'] = $CI->Purchases->select_purchase("COALESCE(SUM(p.bill_payable_amount),0) as total",$purchase_bill_where)->row()->total;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }
          }

          if($data_return_type=='ledger'){
            $opening_balance_payments = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,ats.tr_date,ats.tr_description,supplier_name",$opening_balance_payment_where,'supplier')->result();
            foreach ($opening_balance_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($account_details->acc_supplier_id!=0){
                $transactions[$i]['description'] = "By ".$value->debit_account." For Supplier Opening Balance Payment";
              }else{
                $transactions[$i]['description'] = "To Supplier Opening Balance (".$value->supplier_name.")";
              }
              // if ($account_details->acc_supplier_id != 0) {
              //   $transactions[$i]['debit'] = '';
              //   $transactions[$i]['credit'] = $value->tr_amount;
              // } else {
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              // }
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Supplier Opening Balance Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$opening_balance_payment_where,'supplier')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // Advance Payments
          if($account_prefix=='paymentmode'){
            $advance_payment_where["ats.tr_mode"]= $account_id;
          }else{
            if($account_details->acc_supplier_id!=0){
              $advance_payment_where["ats.reference_id"]= $account_details->acc_supplier_id;
            }else{
              $advance_payment_where["ats.tr_debit_acc"]= $account_id;
            }
          }
          $advance_payment_where['ats.tr_type'] = 'supplier_advance_transaction';
          $advance_payment_where['ats.reference_type'] = 'supplier';
          if($data_return_type=='ledger'){
            $advance_payments = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,ats.tr_date,ats.tr_description,supplier_name",$advance_payment_where,'supplier')->result();
            foreach ($advance_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($account_details->acc_supplier_id!=0){
                $transactions[$i]['description'] = "By ".$value->debit_account." For Supplier Advance Payment";
              }else{
                $transactions[$i]['description'] = "To Supplier Advance Payment (".$value->supplier_name.")";
              }
              // if($account_details->acc_supplier_id!=0){
              //   $transactions[$i]['debit'] = '';
              //   $transactions[$i]['credit'] = $value->tr_amount;
              // }else{
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              // }
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Supplier Advance Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$advance_payment_where,'supplier')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // Purchase Order Payment
          // $purchase_order_payment_where["po.purchase_bill_id"]= 0;
          if($account_prefix=='paymentmode'){
            $purchase_order_payment_where["ats.tr_mode"]= $account_id;
          }else{
            if($account_details->acc_supplier_id!=0){
              $purchase_order_payment_where["po.supplier_id"]= $account_details->acc_supplier_id;
            }else{
              $purchase_order_payment_where["ats.tr_debit_acc"]= $account_id;
            }
          }
          $purchase_order_payment_where['ats.reference_type'] = 'purchase_order';
          $purchase_order_payment_where['ats.tr_type'] = 'purchase_order_transaction';
          if($data_return_type=='ledger'){
            $purchase_order_payments = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,ats.tr_date,s.supplier_name,wc.work_name,sl.location_name", $purchase_order_payment_where,'purchase_order')->result();
            foreach ($purchase_order_payments as $key => $value) {
              if($value->tr_amount > 0){
                $transactions[$i]['date'] = $value->tr_date;
                $transactions[$i]['account'] = $account_details->acc_head_name;
                if($account_details->acc_supplier_id!=0){
                  $transactions[$i]['description'] = "By ".$value->debit_account." (Purchase Order Payment #".$value->reference_id." ) For :".$value->location_name;
                }else{
                  $transactions[$i]['description'] = "To ".$value->supplier_name." (Purchase Order Payment #".$value->reference_id.") For :".$value->location_name;
                }
                // if($account_details->acc_supplier_id!=0){
                //   $transactions[$i]['debit'] = '';
                //   $transactions[$i]['credit'] = $value->tr_amount;
                // }else{
                $transactions[$i]['debit'] = $value->tr_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                // }
                $i++;
              }
            }
          }else{
            $transactions_summary[$j]['description'] = "Purchase Order Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $purchase_order_payment_where,'purchase_order')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Purchase Bill Payment
          if($account_prefix=='paymentmode'){
            $purchase_bill_payment_where['ats.tr_mode'] = $account_id;
          }else{
            if($account_details->acc_supplier_id!=0){
              $purchase_bill_payment_where["p.supplier_id"]= $account_details->acc_supplier_id;
            }else{
              $purchase_bill_payment_where['ats.tr_debit_acc'] = $account_id;
            }
          }
          $purchase_bill_payment_where['ats.reference_type'] = 'purchase_bill';
          $purchase_bill_payment_where['ats.tr_type'] = 'purchase_bill_transaction';
          if($data_return_type=='ledger'){
            $purchase_bill_payments = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,ats.tr_date,s.supplier_name,wc.work_name,sl.location_name", $purchase_bill_payment_where,'purchase_bill')->result();
            foreach ($purchase_bill_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($account_details->acc_supplier_id!=0){
                $transactions[$i]['description'] = "By ".$value->debit_account." (Purchase Bill Payment #".$value->reference_id." ) For :".$value->location_name;
              }else{
                $transactions[$i]['description'] = "To ".$value->supplier_name." (Purchase Bill Payment #".$value->reference_id.") For :".$value->location_name;
              }
              // if($account_details->acc_supplier_id!=0){
              //   $transactions[$i]['debit'] = '';
              //   $transactions[$i]['credit'] = $value->tr_amount;
              // }else{
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              // }
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Purchase Bill Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $purchase_bill_payment_where,'purchase_bill')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }


          // Labour Wage Payment
          if($account_prefix=='paymentmode'){
            $labour_wage_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $labour_wage_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $labour_wage_payment_where['ats.reference_type'] = 'labour';
          $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
          if($data_return_type=='ledger'){
            $labour_wage_payments = $CI->Account->select_account_transaction("ats.tr_amount,l.labour_name,ats.tr_date", $labour_wage_payment_where,'labour')->result();
            foreach ($labour_wage_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->labour_name." (Labour Wage)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Labour Wage Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $labour_wage_payment_where,'labour')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          if($CI->config->item('User_include_travel_allowance_in_ledger')==1){
            if($account_prefix=='paymentmode'){
              $travel_allowance_where['ats.tr_mode'] = $account_id;
            }else{
              $travel_allowance_where['ats.tr_debit_acc'] = $account_id;
            }
            $travel_allowance_where['ats.reference_type'] = 'travel_allowance';
            $travel_allowance_where['ats.tr_type'] = 'travel_allowance_transaction';
            if($data_return_type=='ledger'){
              $travel_allowances = $CI->Account->select_account_transaction("ats.tr_amount,ta.vehicle_type,ats.tr_date", $travel_allowance_where,'travel_allowance')->result();
              foreach ($travel_allowances as $key => $value) {
                $transactions[$i]['date'] = $value->tr_date;
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "Travel Allowance Spent - ".$value->vehicle_type;
                $transactions[$i]['debit'] = $value->tr_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Travel Allowance Payment";
              $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $travel_allowance_where,'travel_allowance')->row()->total;
              $transactions_summary[$j]['type'] = 'debit';
              $j++;
            }
          }


          // if($user_role=='admin' || $user_role=='madmin' || ($user_role=='general_manager' && $CI->config->item('User_general_manager_hr_data_enabled')==1)) {
          // Salary Slip Payment
          if($account_prefix=='paymentmode'){
            $salary_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $salary_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $salary_payment_where['ats.reference_type'] = 'salary_slip';
          $salary_payment_where['ats.tr_type'] = 'salary_slip_transaction';
          if($data_return_type=='ledger'){
            $salary_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,e.employee_name,ats.tr_date", $salary_payment_where,'salary_slip')->result();
            foreach ($salary_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->employee_name." (Salary Payment for Slip #".$value->reference_id.")";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Salary Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $salary_payment_where,'salary_slip')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Employee Payment
          if($account_prefix=='paymentmode'){
            $employee_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $employee_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $employee_payment_where['ats.reference_type'] = 'employee';
          $CI->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction' or ats.tr_type='employee_bata_transaction')");
          if($data_return_type=='ledger'){
            $employee_payments = $CI->Account->select_account_transaction("ats.tr_type,ats.tr_amount,e2.employee_name,ats.tr_date", $employee_payment_where,'employee')->result();
            foreach ($employee_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($value->tr_type=='salary_advance_transaction'){
                $transactions[$i]['description'] = "To ".$value->employee_name." (Advance Salary Payment)";
              }elseif($value->tr_type=='employee_bata_transaction'){
                $transactions[$i]['description'] = "To ".$value->employee_name." (Employee Bata Payment)";
              }else{
                $transactions[$i]['description'] = "To ".$value->employee_name." (Opening Balance Payment)";
              }
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Employee Opening Balance Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $employee_payment_where,'employee')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // }

          // Vehicle Payment
          if($account_prefix=='paymentmode'){
            $vehicle_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $vehicle_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $vehicle_payment_where['ats.reference_type'] = 'vehicle_work_slip';
          $vehicle_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
          if($data_return_type=='ledger'){
            $vehicle_payments = $CI->Account->select_account_transaction("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 => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To " . $value->consignee_name." [ ".$value->vehicle_name." : ".$value->vehicle_no." ]" . " (Vehicle Payment)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Vehicle Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $vehicle_payment_where,'vehicle_work_slip')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Vehicle OP Balance
          if($account_prefix=='paymentmode'){
            $vehicle_op_balance_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $vehicle_op_balance_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $vehicle_op_balance_payment_where['ats.tr_type'] = 'vehicle_opbalance_transaction';
          if($data_return_type=='ledger'){
            $vehicle_op_balance_payments = $CI->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user,u1.user_role as created_user_role_id,v.vehicle_no,vc.consignee_name",$vehicle_op_balance_payment_where,'vehicle_payment')->result();
            foreach ($vehicle_op_balance_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->consignee_name." For Vehicle: " . $value->vehicle_no." (Vehicle OP Balance Payment) ";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Vehicle OP Balance Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$vehicle_op_balance_payment_where,'vehicle_payment')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // Vehicle Renewal
          if($account_prefix=='paymentmode'){
            $vehicle_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $vehicle_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $vehicle_payment_where['ats.reference_type'] = 'vehicle';
          $vehicle_payment_where['ats.tr_type'] = 'vehicle_renewal_transaction';
          if($data_return_type=='ledger'){
            $vehicle_renewal_payments = $CI->Account->select_account_transaction("ats.tr_other_data,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 => $value) {
              $tr_other_data = explode(';',$value->tr_other_data);
              $renewal_type = $tr_other_data[0];
              $renewal_date = $tr_other_data[1];

              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To " . $value->consignee_name." [ ".$value->vehicle_name." : ".$value->vehicle_no." ]" . " (Vehicle ".$renewal_type." Renewal)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Vehicle Renewal Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $vehicle_payment_where,'vehicle_work_slip')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Vehicle Expense Bill
          if($account_prefix=='paymentmode'){
            $vehicle_expense_where['ats.tr_mode'] = $account_id;
          }else{
            if($account_details->account_type==11){
              $vehicle_expense_where['ats.tr_credit_acc'] = $account_id;
            }else{
              $vehicle_expense_where['ats.tr_debit_acc'] = $account_id;
            }
          }
          $vehicle_expense_where['ats.reference_type'] = 'vehicle_bill';
          $vehicle_expense_where['ats.tr_type'] = 'vehicle_bill_transaction';
          if($data_return_type=='ledger'){
            $vehicle_expense_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ah2.acc_head_name as expense_head_name,b.vehicle_id,v.vehicle_no,v.vehicle_name", $vehicle_expense_where,'vehicle_bill')->result();
            foreach ($vehicle_expense_payments as $key => $value) {
              $expense_type=" (Vehicle Expense Bill #".$value->reference_id." Payment For: ".$value->vehicle_name." - ".$value->vehicle_no.")";

              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->expense_head_name.$expense_type;
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Vehicle Expense Bill Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $vehicle_expense_where,'vehicle_bill')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Subcontract Payment
          if($account_prefix=='paymentmode'){
            $subcontract_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $subcontract_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $CI->db->where("(ats.tr_type='subcontract_bill_transaction' or ats.tr_type='subcontract_advance_transaction')");
          $CI->db->where("(ats.reference_type='subcontract_bill' or ats.reference_type='subcontract')");
          if($data_return_type=='ledger'){
            $sub_contract_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,sc.contractor,c.contractor_name,wc.work_name,ats.tr_type,sc2.contractor as contractor_2,c2.contractor_name as contractor_name_2,wc2.work_name as work_name_2", $subcontract_payment_where,'subcontract_transactions')->result();
            foreach ($sub_contract_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($value->tr_type=='subcontract_bill_transaction'){
                $transactions[$i]['description'] = "To ".$value->contractor_name." (Subcontract Bill #".$value->reference_id." Payment) For Work :".$value->work_name;
              }else{
                $transactions[$i]['description'] = "To ".$value->contractor_name_2." (Subcontract Advance Payment) For Work :".$value->work_name_2;
              }
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Subcontract Bill & Advance Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $subcontract_payment_where,'subcontract_transactions')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Select Expense
          if($account_prefix=='paymentmode'){
            $other_expense_where['ats.tr_mode'] = $account_id;
          }else{
            if($account_details->account_type==11){
              $other_expense_where['ats.tr_credit_acc'] = $account_id;
            }else{
              $other_expense_where['ats.tr_debit_acc'] = $account_id;
            }
          }
          $other_expense_where['ats.reference_type'] = 'expense_bill';
          $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
          if($data_return_type=='ledger'){
            $expense_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ah2.acc_head_name as expense_head_name,b.work_id,wc.work_name", $other_expense_where,'expense_bill')->result();
            foreach ($expense_payments as $key => $value) {
              if($value->work_id!=0){
                $expense_type=" (Project Expense Bill #".$value->reference_id." Payment For: ".$value->work_name.")";
              }else{
                $expense_type=" (Office Expense Bill #".$value->reference_id." Payment)";
              }
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->expense_head_name.$expense_type;
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Expense Bill Payments";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $other_expense_where,'expense_bill')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }


          // User Payment Receipt
          if($account_prefix=='paymentmode'){
            $user_payment_receipts_where['ats.tr_mode'] = $account_id;
          }else{
            $user_payment_receipts_where['ats.tr_debit_acc'] = $account_id;
          }
          $user_payment_receipts_where['ats.reference_type'] = 'user_account_head';
          $user_payment_receipts_where['ats.tr_type'] = 'user_payment_receipt_transaction';
          if($data_return_type=='ledger'){
            $user_payment_receipts = $CI->Account->select_account_transaction("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", $user_payment_receipts_where,'user_payment_receipt')->result();
            foreach ($user_payment_receipts as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->received_user_account." (User Payment Receipt)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "User Payment Receipt";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $user_payment_receipts_where,'user_payment_receipt')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // User Payment Return
          if($account_prefix=='paymentmode'){
            $user_payment_returns_where['ats.tr_mode'] = $account_id;
          }else{
            $user_payment_returns_where['ats.tr_credit_acc'] = $account_id;
          }
          $user_payment_returns_where['ats.reference_type'] = 'user_account_head';
          $user_payment_returns_where['ats.tr_type'] = 'user_payment_return_transaction';
          if($data_return_type=='ledger'){
            $user_payment_returns = $CI->Account->select_account_transaction("ats.tr_amount,ats.tr_date,u1.user_alias as created_user,u4.user_alias as returned_user,ah1.acc_head_name as debit_account", $user_payment_returns_where,'user_payment_return')->result();
            foreach ($user_payment_returns as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By ".$value->debit_account." (User Payment Return)";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "User Payment Return";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $user_payment_returns_where,'user_payment_return')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
          // User Commission Payment
          if($account_prefix=='paymentmode'){
            $user_commission_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $user_commission_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $user_commission_payment_where['ats.reference_type'] = 'user_account_head';
          $user_commission_payment_where['ats.tr_type'] = 'user_commission_transaction';
          if($data_return_type=='ledger'){
            $user_commission_payment = $CI->Account->select_account_transaction("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 => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To ".$value->received_user_account." (User Project Commission Payment For Work :".$value->work_name.")";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "User Project Commission Payment ";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $user_commission_payment_where,'user_commission')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Account transfer credit
          if($account_prefix=='paymentmode'){
            $account_transfer_where_credit['ats.tr_mode'] = $account_id;
          }else{
            $account_transfer_where_credit['ats.tr_credit_acc'] = $account_id;
          }
          $account_transfer_where_credit['ats.tr_type'] = 'account_transfer';
          if($data_return_type=='ledger'){
            $account_transfer_result_credit = $CI->Account->select_account_transfer("*,ah1.acc_head_name AS transfer_from,ah1.acc_head_id AS acc_from_id,ah2.acc_head_name AS transfer_to,ah2.acc_head_id AS acc_to_id,u1.user_alias AS created_by,ats.created_date AS t_created_date",$account_transfer_where_credit)->result();
            foreach ($account_transfer_result_credit as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Transferred From ".$value->transfer_from." ( Account Transfer )";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Account Transfer Credited";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transfer("COALESCE(SUM(ats.tr_amount),0) as total",$account_transfer_where_credit)->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
          // Account transfer Debit
          if($account_prefix=='paymentmode'){
            $account_transfer_where_debit['ats.tr_mode'] = $account_id;
          }else{
            $account_transfer_where_debit['ats.tr_debit_acc'] = $account_id;
          }
          $account_transfer_where_debit['ats.tr_type'] = 'account_transfer';
          if($data_return_type=='ledger'){
            $account_transfer_result_debit = $CI->Account->select_account_transfer("*,ah1.acc_head_name AS transfer_from,ah1.acc_head_id AS acc_from_id,ah2.acc_head_name AS transfer_to,ah2.acc_head_id AS acc_to_id,u1.user_alias AS created_by,ats.created_date AS t_created_date",$account_transfer_where_debit)->result();
            foreach ($account_transfer_result_debit as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Transferred To ".$value->transfer_to." ( Account Transfer )";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Account Transfer Debited";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transfer("COALESCE(SUM(ats.tr_amount),0) as total",$account_transfer_where_debit)->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
          // Loan Payment
          if($account_prefix=='paymentmode'){
            $loan_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $loan_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $loan_payment_where['ats.reference_type'] = 'loan';
          $loan_payment_where['ats.tr_type'] = 'loan_transaction';
          if($data_return_type=='ledger'){
            $loan_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,l.loan_title,lp.loan_provider_name", $loan_payment_where,'loan')->result();
            foreach ($loan_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To " . $value->loan_provider_name." [ ".$value->loan_title." ] " . " (Loan Payment - Loan ID #".$value->reference_id.")";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Loan Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $loan_payment_where,'loan')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // Tool Purchase
          if($account_prefix=='paymentmode'){
            $tool_purchase_where['ats.tr_mode'] = $account_id;
          }else{
            $tool_purchase_where['ats.tr_debit_acc'] = $account_id;
          }
          $tool_purchase_where['ats.reference_type'] = 'tool_purchase';
          $tool_purchase_where['ats.tr_type'] = 'tool_purchase_transaction';
          if($data_return_type=='ledger'){
            $tool_purchase = $CI->Account->select_account_transaction("ats.tr_amount,ats.tr_date,t.name,tp.purchased_from", $tool_purchase_where,'tool_purchase')->result();
            foreach ($tool_purchase as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "To " . $value->purchased_from." [ ".$value->name." ] " . " (Tool Purchase)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Tool Purchase";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $tool_purchase_where,'tool_purchase')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // Tool Payment
          if($account_prefix=='paymentmode'){
            $tool_payment_where['ats.tr_mode'] = $account_id;
          }else{
            $tool_payment_where['ats.tr_debit_acc'] = $account_id;
          }
          $tool_payment_where['ats.tr_type'] = 'tool_payment_transaction';
          $CI->db->where('(ats.reference_type="tool_transfer" or ats.reference_type="tool_rent")');
          if($data_return_type=='ledger'){
            $tool_payments = $CI->Account->select_account_transaction("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 => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($value->reference_type=='tool_transfer'){
                $transactions[$i]['description'] = "To Office For " . $value->name_2." [ ".$value->cat_name_2." ] " . " (Owned Tool Payment) For Work: ".$value->transfer_to_location;
              }else{
                $transactions[$i]['description'] = "To ".$value->rented_from." For " . $value->name." [ ".$value->cat_name." ] " . " (Rent Tool Payment) For Work: ".$value->rented_to_location;
              }
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Tool Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $tool_payment_where,'tool')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          if($account_details->account_type==12){ //Supplier
            // Vehicle Expense Bills
            $bills_where['b.bill_supplier_id'] = $account_details->acc_supplier_id;
            if($data_return_type=='ledger'){
              $expense_bills = $CI->Bills->select_bills("",$bills_where)->result();
              foreach ($expense_bills as $key => $value) {
                $expense_type=ucwords($value->bill_expense_type)." Expense";
                $transactions[$i]['date'] = date('Y-m-d',strtotime($value->bill_date));
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "Payable Expense Bill #".$value->bill_id." (".$expense_type.")";
                $transactions[$i]['debit'] = '';
                $transactions[$i]['credit'] = $value->bill_payable_amount;
                $transactions[$i]['type'] = 'credit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Payable Vehicle Expense Bill";
              $transactions_summary[$j]['amount'] = $CI->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as total",$bills_where)->row()->total;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }
            // Transactions
            unset($other_expense_where['ats.tr_credit_acc']);
            unset($other_expense_where['ats.tr_debit_acc']);
            $other_expense_where['b.bill_supplier_id'] = $account_details->acc_supplier_id;
            $other_expense_where['ats.reference_type'] = 'vehicle_bill';
            $other_expense_where['ats.tr_type'] = 'vehicle_bill_transaction';
            if($data_return_type=='ledger'){
              $expense_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ah2.acc_head_name as expense_head_name,b.work_id,wc.work_name,ah1.acc_head_name as debit_account", $other_expense_where,'vehicle_bill')->result();
              foreach ($expense_payments as $key => $value) {
                $transactions[$i]['date'] = $value->tr_date;
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "By ".$value->debit_account." (Vehicle Expense Bill #".$value->reference_id." Payment)";
                $transactions[$i]['debit'] = $value->tr_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Vehicle Expense Bill Payment";
              $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $other_expense_where,'vehicle_bill')->row()->total;
              $transactions_summary[$j]['type'] = 'debit';
              $j++;
            }
          }

        }elseif($account_prefix=='acchead' && ($account_details->account_type==11)){//Expense Heads
          // Bills
          $bills_where['b.bill_credit_ac'] = $account_id;
          $expense_bills = $CI->Bills->select_bills("b.*,wc.work_name",$bills_where)->result();
          if($data_return_type=='ledger'){
            foreach ($expense_bills as $key => $value) {
              $expense_type=ucwords($value->bill_expense_type)." Expense";
              if($value->bill_expense_type=='work'){
                $expense_type = 'Project Expense - #'.$value->work_id." ".$value->work_name;
              }
              $transactions[$i]['date'] = date('Y-m-d',strtotime($value->bill_date));
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Payable Expense Bill #".$value->bill_id." (".$expense_type.")";
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->bill_payable_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Payable Expense Bill";
            $transactions_summary[$j]['amount'] = $CI->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as total",$bills_where)->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }


          // Transactions
          $other_expense_where['ats.tr_credit_acc'] = $account_id;
          $other_expense_where['ats.reference_type'] = 'expense_bill';
          $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
          if($data_return_type=='ledger'){
            $expense_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ah2.acc_head_name as expense_head_name,b.work_id,wc.work_name,ah1.acc_head_name as debit_account", $other_expense_where,'expense_bill')->result();
            foreach ($expense_payments as $key => $value) {
              if($value->work_id!=0){
                $expense_type=" (Project Expense Bill #".$value->reference_id." Payment For: ".$value->work_name.")";
              }else{
                $expense_type=" (Office Expense Bill #".$value->reference_id." Payment)";
              }
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By ".$value->debit_account.$expense_type;
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Expense Bill Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $other_expense_where,'expense_bill')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

        }elseif($account_prefix=='acchead' && $account_details->acc_employee_id!=0){
          if($employee_account_type=='Salary'){
            // Salary Slip Payment
            $salary_payment_where2 = $salary_payment_where;
            unset($salary_payment_where2['ats.tr_debit_acc']);
            $salary_payment_where2['ss.employee_id'] = $account_details->acc_employee_id;
            $salary_payment_where2['ats.reference_type'] = 'salary_slip';
            $salary_payment_where2['ats.tr_type'] = 'salary_slip_transaction';
            if($data_return_type=='ledger'){
              $salary_payments_received_for_employee = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,e.employee_name,ats.tr_date", $salary_payment_where2,'salary_slip')->result();
              foreach ($salary_payments_received_for_employee as $key => $value) {
                $transactions[$i]['date'] = $value->tr_date;
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "By ".$value->debit_account." (Salary Slip Payment #".$value->reference_id.")";
                $transactions[$i]['debit'] = $value->tr_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Salary Slip Payment";
              $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $salary_payment_where2,'salary_slip')->row()->total;
              $transactions_summary[$j]['type'] = 'debit';
              $j++;
            }
            // Employee Payment

            $employee_payment_where2 = $employee_payment_where;
            unset($employee_payment_where2['ats.tr_debit_acc']);
            $employee_payment_where2['ats.reference_id'] = $account_details->acc_employee_id;
            $employee_payment_where2['ats.reference_type'] = 'employee';
            $CI->db->where("(ats.tr_type='employee_opbalance_transaction' or ats.tr_type='salary_advance_transaction')");
            if($data_return_type=='ledger'){
              $employee_payments_received_for_employee = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_type,ats.tr_amount,e2.employee_name,ats.tr_date", $employee_payment_where2,'employee')->result();
              foreach ($employee_payments_received_for_employee as $key => $value) {
                $transactions[$i]['date'] = $value->tr_date;
                $transactions[$i]['account'] = $account_details->acc_head_name;
                if($value->tr_type=='salary_advance_transaction'){
                  $transactions[$i]['description'] = "By ".$value->debit_account." (Advance Salary Payment)";
                }else{
                  $transactions[$i]['description'] = "By ".$value->debit_account." (Salary Opening Balance Payment)";
                }
                $transactions[$i]['debit'] = $value->tr_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Salary Advance/Opening Balance Payment";
              $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $employee_payment_where2,'employee')->row()->total;
              $transactions_summary[$j]['type'] = 'debit';
              $j++;
            }

            $salary_slip_where['ss.employee_id'] = $account_details->acc_employee_id;
            if($data_return_type=='ledger'){
              $salary_slips = $CI->Salary_slips->select_salary_slips("e.employee_name,e.civil_id,e.phone_no,e.user_designation,ss.*,ss.basic_wage_type as basic_wage_type,ss.created_by as ss_created_by",$salary_slip_where)->result();
              foreach ($salary_slips as $key => $value) {
                $transactions[$i]['date'] = date('Y-m-t',strtotime($value->month));
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "Payable Salary Slip #".$value->salary_id." (".date('F Y',strtotime($value->month)).")";
                $transactions[$i]['debit'] = '';
                $transactions[$i]['credit'] = $value->net_salary_amount;
                $transactions[$i]['type'] = 'credit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Payable Salary Slip";
              $transactions_summary[$j]['amount'] = $CI->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total",$salary_slip_where)->row()->total;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }
          }elseif($employee_account_type=='Bata'){

            $employee_payment_where2['ats.reference_id'] = $account_details->acc_employee_id;
            $employee_payment_where2['ats.reference_type'] = 'employee';
            $CI->db->where("(ats.tr_type='employee_bata_transaction')");
            if($data_return_type=='ledger'){
              $employee_payments_received_for_employee = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_type,ats.tr_amount,e2.employee_name,ats.tr_date", $employee_payment_where2,'employee')->result();
              foreach ($employee_payments_received_for_employee as $key => $value) {
                $transactions[$i]['date'] = $value->tr_date;
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "By ".$value->debit_account." (Employee Bata Payment)";
                $transactions[$i]['debit'] = $value->tr_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Employee Bata Balance Payment";
              $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $employee_payment_where2,'employee')->row()->total;
              $transactions_summary[$j]['type'] = 'debit';
              $j++;
            }

            // Get Driver Bata
            $vehicle_work_slip_where['vws.driver_id'] = $account_details->acc_employee_id;
            if($start_date!=''){
              // Get Op Balance on Start Date
              $vehicle_work_slip_where_op = $vehicle_work_slip_where;
              // $vehicle_work_slip_payment_where_op = $vehicle_work_slip_payment_where;

              $vehicle_work_slip_where_op['vws.worked_date <'] = date('Y-m-d', strtotime($start_date));
              // $vehicle_work_slip_payment_where_op['ats.tr_date <'] = date('Y-m-d', strtotime($start_date));

              // $vehicle_work_slip_payments_op = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$vehicle_work_slip_payment_where_op,'vehicle_work_slip')->row()->total_amount;
              $vehicle_work_slips_op = $CI->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(vws.driver_bata),0) as total_amount", $vehicle_work_slip_where_op)->row()->total_amount;
              $json_data['op_balance']['amount'] = $vehicle_work_slips_op;
              $json_data['op_balance']['date'] = $start_date;

              $vehicle_work_slip_where['vws.worked_date >='] = date('Y-m-d', strtotime($start_date));
              // $vehicle_work_slip_payment_where['ats.tr_date >='] = date('Y-m-d', strtotime($start_date));
            }
            if($end_date!=''){
              $vehicle_work_slip_where['vws.worked_date <='] = date('Y-m-d', strtotime($end_date));
              // $vehicle_work_slip_payment_where['ats.tr_date <='] = date('Y-m-d', strtotime($end_date));
            }
            if($data_return_type=='ledger'){
              // $vehicle_work_slip_payments = $CI->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user,u1.user_role as created_user_role_id,v.vehicle_no",$vehicle_work_slip_payment_where,'vehicle_work_slip')->result();
              $vehicle_work_slips = $CI->Vehicle_work_slips->select_vehicle_work_slips("vws.*,v.vehicle_no", $vehicle_work_slip_where)->result();

              // foreach ($vehicle_work_slip_payments as $key => $value) {
              //   $transactions[$i]['date'] = $value->tr_date;
              //   $transactions[$i]['data'] = $value;
              //   $transactions[$i]['data_type'] = 'payment';
              //   $transactions[$i]['data_type_2'] = 'workslip_payment';
              //   $i++;
              // }
              foreach ($vehicle_work_slips as $key => $value) {
                $transactions[$i]['date'] = date('Y-m-d',strtotime($value->worked_date));
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "Payable Driver Bata for Vehicle Work Slip #".$value->work_slip_id;
                if($value->driver_bata_include==1){
                  $transactions[$i]['description'] .= '&nbsp;<i class="fa fa-check-circle"></i> Included';
                }else{
                  $transactions[$i]['description'] .= '&nbsp;<i class="fa fa-minus-circle"></i> Excluded';
                }
                $transactions[$i]['debit'] = '';
                $transactions[$i]['credit'] = $value->driver_bata;
                $transactions[$i]['type'] = 'credit';
                $i++;
              }
              // function date_compare($element1, $element2) {
              //   $datetime1 = strtotime($element1['date']);
              //   $datetime2 = strtotime($element2['date']);
              //   // if($element1['date']===$element2['date']){
              //   //   return 0;
              //   // }
              //   return $datetime1 <=> $datetime2;
              // }
            }else{
              // $vehicle_work_slip_payments = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$vehicle_work_slip_payment_where,'vehicle_work_slip')->row()->total;
              $vehicle_work_slips = $CI->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(vws.driver_bata),0) as total", $vehicle_work_slip_where)->row()->total;

              // $transactions_summary[$j]['description'] = "Vehicle Work Slip Payment";
              // $transactions_summary[$j]['amount'] = $vehicle_work_slip_payments;
              // $transactions_summary[$j]['type'] = 'credit';
              // $j++;

              $transactions_summary[$j]['description'] = "Driver Bata - Vehicle Work Slip";
              $transactions_summary[$j]['amount'] = $vehicle_work_slips;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }
          }
        }elseif($account_prefix=='labour'){

          if($account_details->labour_type==1 && $account_details->group_position==1){
            $labour_wage_slip_where['l.labour_group'] = $labour_where['l.labour_group'] = $account_details->labour_group;
            $labour_wage_payment_where['ats.reference_id'] = $account_details->labour_id;
          }else{
            $labour_wage_slip_where['lw.labour_id'] = $labour_where['l.labour_id'] = $account_details->labour_id;
            $labour_wage_payment_where['ats.reference_id'] = $account_details->labour_id;
          }

          // Labour Wage Slips
          if($data_return_type=='ledger'){
            $labour_wage_slips = $CI->Labour_wages->select_labour_wages("lw.*,wt.type_name,l.labour_group,l.labour_name,wt.type_name,u.user_alias,wc.work_name,u.user_role as created_user_role_id", $labour_wage_slip_where)->result();
            foreach ($labour_wage_slips as $key => $value) {
              $transactions[$i]['date'] = $value->work_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Payable Labour Wage Slip #".$value->wage_id." - For Project :".$value->work_name;
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->total_wage;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Payable Labour Wage Slip";
            $transactions_summary[$j]['amount'] = $CI->Labour_wages->select_labour_wages("COALESCE(SUM(lw.total_wage),0) as total", $labour_wage_slip_where)->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
          // Labour Wage Payment
          $labour_wage_payment_where['ats.reference_type'] = 'labour';
          $labour_wage_payment_where['ats.tr_type'] = 'labour_transaction';
          if($data_return_type=='ledger'){
            $labour_wage_payments = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_type,ats.tr_amount,ats.tr_date", $labour_wage_payment_where,'labour')->result();
            foreach ($labour_wage_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "By ".$value->debit_account." (Labour Wage)";
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Labour Wage Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $labour_wage_payment_where,'labour')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
        }elseif($account_prefix=='contractor'){
          $subcontract_where["sc.contractor"] = $account_details->contractor_id;
          $subcontract_where["sc.op_balance !="] = 0;
          if($data_return_type=='ledger'){
            $subcontracts = $CI->Subcontracts->select_subcontract("sc.op_balance,sc.created_date,sc.sub_work_name,wc.work_name", $subcontract_where)->result();
            foreach ($subcontracts as $key => $value) {
              $transactions[$i]['date'] = $value->created_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Opening Balance Paid Before A/C Creation (".$value->sub_work_name.") - For Project :".$value->work_name;
              $transactions[$i]['debit'] = $value->op_balance;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Subcontractor Opening Balance Paid Before A/C Creation";
            $transactions_summary[$j]['amount'] = $CI->Subcontracts->select_subcontract_2("COALESCE(SUM(sc.op_balance),0) as total", $subcontract_where)->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          $subcontract_bills_where["sc.contractor"] = $account_details->contractor_id;
          $subcontract_bills_where["b.bill_type"] = 'subcontract';
          if($data_return_type=='ledger'){
            $subcontract_bills = $CI->Bills->select_bills("b.bill_date,wc.work_name,b.bill_id,b.bill_payable_amount",$subcontract_bills_where)->result();
            foreach ($subcontract_bills as $key => $value) {
              $transactions[$i]['date'] = $value->bill_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Payable Subcontract Bill #".$value->bill_id." - For Project :".$value->work_name;
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->bill_payable_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Payable Subcontract Bill";
            $transactions_summary[$j]['amount'] = $CI->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as total",$subcontract_bills_where)->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }

          $CI->db->where("((sc.contractor=".$account_details->contractor_id." and ats.tr_type='subcontract_bill_transaction') or (sc2.contractor=".$account_details->contractor_id." and  ats.tr_type='subcontract_advance_transaction'))");
          $CI->db->where("((ats.reference_type='subcontract_bill' or ats.reference_type='subcontract'))");
          if($data_return_type=='ledger'){
            $sub_contract_payments = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_type,ats.tr_amount,ats.tr_date,sc.sub_work_name as bill_sub_work_name,sc2.sub_work_name as advance_sub_work_name,wc.work_name as bill_payment_work_name,wc2.work_name as advance_payment_work_name", $subcontract_payment_where,'subcontract_transactions')->result();
            foreach ($sub_contract_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($value->tr_type=='subcontract_bill_transaction'){
                $transactions[$i]['description'] = "By ".$value->debit_account." (Subcontract Bill Payment #".$value->reference_id." - ".$value->bill_sub_work_name.") - For Project :".$value->bill_payment_work_name;
              }else{
                $transactions[$i]['description'] = "By ".$value->debit_account." (Subcontract Advance Payment - ".$value->advance_sub_work_name.") - For Project :".$value->advance_payment_work_name;
              }
              $transactions[$i]['debit'] = $value->tr_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Subcontract Advance/Bill Payment";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $subcontract_payment_where,'subcontract_transactions')->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
        }elseif($account_prefix=='client'){
          // Client Payment Receipts
          $client_payment_where['wc.client_id'] = $account_details->client_id;
          $client_payment_where['ats.tr_type'] = 'client_payment_transaction';
          $client_payment_where['ats.reference_type'] = 'project';
          if($data_return_type=='ledger'){
            $client_payments_received = $CI->Account->select_account_transaction("ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,ats.tr_date,c.client_name,wc.work_name",$client_payment_where,'client_payment')->result();
            foreach ($client_payments_received as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Received To ".$value->credit_account." For Work :".$value->work_name;
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Client Payment Receipt (Advance/Invoice)";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$client_payment_where,'client_payment')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }

          // Invoices
          $client_invoice_where['i.client_id'] = $account_details->client_id;
          if($data_return_type=='ledger'){
            $client_invoices = $CI->Invoices->select_invoices("i.invoice_date,i.invoice_id,i.invoice_total_amount,i.invoice_type,wc.work_id,wc.work_name",$client_invoice_where)->result();
            foreach ($client_invoices as $key => $value) {
              $transactions[$i]['date'] = $value->invoice_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              if($value->invoice_type=='project_invoice'){
                $invoice_details=" (Project Invoice For: #".$value->work_id." - ".$value->work_name.")";
              }else{
                $invoice_details=" (Other Income Invoice)";
              }
              $transactions[$i]['description'] = "Receivable Invoice #".$value->invoice_id.$invoice_details;
              $transactions[$i]['debit'] = $value->invoice_total_amount;
              $transactions[$i]['credit'] = '';
              $transactions[$i]['type'] = 'debit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Receivable Client Invoice ";
            $transactions_summary[$j]['amount'] = $CI->Invoices->select_invoices("COALESCE(SUM(i.invoice_total_amount),0) as total",$client_invoice_where)->row()->total;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }

          // Other Income Receipt
          $other_income_where['ci.client_id'] = $account_details->client_id;
          $other_income_where['ats.tr_type'] = 'income_receipt_transaction';
          $CI->db->where("ats.reference_type='client' and ats.invoice_id!=0");
          if($data_return_type=='ledger'){
            $other_income_received = $CI->Account->select_account_transaction("ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,c.client_name,ats.invoice_id,ats.tr_amount,ats.tr_date,ats.tr_description",$other_income_where,'income_receipt')->result();
            foreach ($other_income_received as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['account'] = $account_details->acc_head_name;
              $transactions[$i]['description'] = "Received To ".$value->credit_account." For Other Income Invoice #".$value->invoice_id;
              $transactions[$i]['debit'] = '';
              $transactions[$i]['credit'] = $value->tr_amount;
              $transactions[$i]['type'] = 'credit';
              $i++;
            }
          }else{
            $transactions_summary[$j]['description'] = "Other Income Receipt";
            $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$other_income_where,'income_receipt')->row()->total;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;
          }
        }elseif($account_prefix=='vehicleconsignee' || $account_prefix=='vehicle'){
          if($account_prefix=='vehicleconsignee'){
            $vehicle_op_balance_payment_where['v.consignee'] = $vehicle_work_slip_payment_where['v.consignee'] = $vehicle_where['v.consignee'] = $vehicle_work_slip_where['v.consignee'] = $json_data['selected_account_id'];
          }else{
            $vehicle_op_balance_payment_where['v.id'] = $vehicle_work_slip_payment_where['v.id'] = $vehicle_where['v.id'] = $vehicle_work_slip_where['v.id'] = $json_data['selected_account_id'];
          }
          $vehicle_work_slip_payment_where['ats.tr_type'] = 'vehicle_work_slip_transaction';
          $vehicle_work_slip_payment_where['ats.reference_type'] = 'vehicle_work_slip';
          $vehicle_op_balance_payment_where['ats.tr_type'] = 'vehicle_opbalance_transaction';
          $vehicle_op_balance_payment_where['ats.reference_type'] = 'vehicle';
          $vehicle_where['v.vehicle_op_balance >'] = 0;

          if($start_date!=''){
            // Get Op Balance on Start Date
            $vehicle_work_slip_where_op = $vehicle_work_slip_where;
            $vehicle_work_slip_payment_where_op = $vehicle_work_slip_payment_where;
            $vehicle_op_balance_payment_where_op = $vehicle_op_balance_payment_where;
            $vehicle_where_op = $vehicle_where;

            $vehicle_work_slip_where_op['vws.worked_date <'] = date('Y-m-d', strtotime($start_date));
            $vehicle_work_slip_payment_where_op['ats.tr_date <'] = date('Y-m-d', strtotime($start_date));
            $vehicle_op_balance_payment_where_op['ats.tr_date <'] = date('Y-m-d', strtotime($start_date));
            $vehicle_where_op['v.created_date <'] = date('Y-m-d', strtotime($start_date));

            $vehicle_op_balance_payment_op = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$vehicle_op_balance_payment_where_op,'vehicle_payment')->row()->total_amount;
            $vehicle_work_slip_payments_op = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total_amount",$vehicle_work_slip_payment_where_op,'vehicle_work_slip')->row()->total_amount;
            $vehicle_work_slips_op = $CI->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(vws.total_payable_amount),0) as total_amount", $vehicle_work_slip_where_op)->row()->total_amount;
            $vehicle_op = $CI->Vehicle->select_vehicle("COALESCE(SUM(v.vehicle_op_balance),0) as total_amount", $vehicle_where_op)->row()->total_amount;
            $json_data['op_balance']['amount'] = $vehicle_op+$vehicle_work_slips_op-$vehicle_work_slip_payments_op-$vehicle_op_balance_payment_op;
            $json_data['op_balance']['date'] = $start_date;

            $vehicle_work_slip_where['vws.worked_date >='] = date('Y-m-d', strtotime($start_date));
            $vehicle_work_slip_payment_where['ats.tr_date >='] = date('Y-m-d', strtotime($start_date));
            $vehicle_op_balance_payment_where['ats.tr_date >='] = date('Y-m-d', strtotime($start_date));
            $vehicle_where['v.created_date <='] = date('Y-m-d', strtotime($start_date));
          }
          if($end_date!=''){
            $vehicle_work_slip_where['vws.worked_date <='] = date('Y-m-d', strtotime($end_date));
            $vehicle_work_slip_payment_where['ats.tr_date <='] = date('Y-m-d', strtotime($end_date));
            $vehicle_op_balance_payment_where['ats.tr_date <='] = date('Y-m-d', strtotime($end_date));
            $vehicle_where['v.created_date >='] = date('Y-m-d', strtotime($end_date));
          }
          if($data_return_type=='ledger'){
            $vehicle_op_balances = $CI->Vehicle->select_vehicle("v.*",$vehicle_where)->result();
            $vehicle_work_slip_payments = $CI->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user,u1.user_role as created_user_role_id,v.vehicle_no",$vehicle_work_slip_payment_where,'vehicle_work_slip')->result();
            $vehicle_work_slips = $CI->Vehicle_work_slips->select_vehicle_work_slips("vws.*,v.vehicle_no", $vehicle_work_slip_where)->result();
            $vehicle_op_balance_payments = $CI->Account->select_account_transaction("ats.*,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,u1.user_alias as created_user,u1.user_role as created_user_role_id,v.vehicle_no",$vehicle_op_balance_payment_where,'vehicle_payment')->result();

            foreach ($vehicle_op_balance_payments as $key3 => $value3) {
              $transactions[$i]['date'] = $value3->tr_date;
              $transactions[$i]['data'] = $value3;
              $transactions[$i]['data_type'] = 'payment';
              $transactions[$i]['data_type_2'] = 'op_balance_payment';
              $i++;
            }
            foreach ($vehicle_work_slip_payments as $key => $value) {
              $transactions[$i]['date'] = $value->tr_date;
              $transactions[$i]['data'] = $value;
              $transactions[$i]['data_type'] = 'payment';
              $transactions[$i]['data_type_2'] = 'workslip_payment';
              $i++;
            }
            foreach ($vehicle_op_balances as $key11 => $value11) {
              $transactions[$i]['date'] = date('Y-m-d',strtotime($value11->created_date));
              $transactions[$i]['data'] = $value11;
              $transactions[$i]['data_type'] = 'op_balance';
              $transactions[$i]['data_type_2'] = '';
              $i++;
            }
            foreach ($vehicle_work_slips as $key2 => $value2) {
              $transactions[$i]['date'] = $value2->worked_date;
              $transactions[$i]['data'] = $value2;
              $transactions[$i]['data_type'] = 'work_slip';
              $transactions[$i]['data_type_2'] = '';
              $i++;
            }
            // function date_compare($element1, $element2) {
            //   $datetime1 = strtotime($element1['date']);
            //   $datetime2 = strtotime($element2['date']);
            //   // if($element1['date']===$element2['date']){
            //   //   return 0;
            //   // }
            //   return $datetime1 <=> $datetime2;
            // }
          }else{
            $vehicle_op_balances = $CI->Vehicle->select_vehicle("COALESCE(SUM(v.vehicle_op_balance),0) as total",$vehicle_where)->row()->total;
            $vehicle_work_slip_payments = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$vehicle_work_slip_payment_where,'vehicle_work_slip')->row()->total;
            $vehicle_work_slips = $CI->Vehicle_work_slips->select_vehicle_work_slips("COALESCE(SUM(vws.total_payable_amount),0) as total", $vehicle_work_slip_where)->row()->total;
            $vehicle_op_balance_payments = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total",$vehicle_op_balance_payment_where,'vehicle_payment')->row()->total;

            $transactions_summary[$j]['description'] = "Vehicle Opening Balance";
            $transactions_summary[$j]['amount'] = $vehicle_op_balances;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;

            $transactions_summary[$j]['description'] = "Vehicle Opening Balance Payment";
            $transactions_summary[$j]['amount'] = $vehicle_op_balance_payments;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;

            $transactions_summary[$j]['description'] = "Vehicle Work Slip Payment";
            $transactions_summary[$j]['amount'] = $vehicle_work_slip_payments;
            $transactions_summary[$j]['type'] = 'credit';
            $j++;

            $transactions_summary[$j]['description'] = "Vehicle Work Slip";
            $transactions_summary[$j]['amount'] = $vehicle_work_slips;
            $transactions_summary[$j]['type'] = 'debit';
            $j++;
          }
        }elseif($account_prefix=='expensetype'){
          if($account_details->acc_head_id==28 || $account_details->acc_head_id==30){

            // Bills
            if($account_details->acc_head_id==28){
              $bills_where['b.bill_expense_type'] = 'office';
            }elseif($account_details->acc_head_id==29){
              $bills_where['b.bill_expense_type'] = 'marketing';
            }else{
              $bills_where['b.bill_expense_type'] = 'work';
            }
            $expense_bills = $CI->Bills->select_bills("b.*,wc.work_name,ah1.acc_head_name",$bills_where)->result();
            if($data_return_type=='ledger'){
              foreach ($expense_bills as $key => $value) {
                $expense_type=ucwords($value->bill_expense_type)." Expense";
                if($value->bill_expense_type=='work'){
                  $expense_type = 'Project Expense - #'.$value->work_id." ".$value->work_name;
                }
                $transactions[$i]['date'] = date('Y-m-d',strtotime($value->bill_date));
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "To Expense Bill #".$value->bill_id." (".$expense_type.")[".$value->acc_head_name."]";
                $transactions[$i]['debit'] = $value->bill_payable_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Payable Expense Bill";
              $transactions_summary[$j]['amount'] = $CI->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as total",$bills_where)->row()->total;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }


            // if($account_details->acc_head_id==28){
            //   $other_expense_where['b.bill_expense_type'] = 'office';
            // }elseif($account_details->acc_head_id==29){
            //   $other_expense_where['b.bill_expense_type'] = 'marketing';
            // }else{
            //   $other_expense_where['b.bill_expense_type'] = 'work';
            // }
            // $other_expense_where['ats.reference_type'] = 'expense_bill';
            // $other_expense_where['ats.tr_type'] = 'expense_bill_transaction';
            // if($data_return_type=='ledger'){
            //   $expense_payments = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ah2.acc_head_name as expense_head_name,b.work_id,wc.work_name", $other_expense_where,'expense_bill')->result();
            //   foreach ($expense_payments as $key => $value) {
            //     if($value->work_id!=0){
            //       $expense_type=" (Project Expense Bill #".$value->reference_id." Payment For: ".$value->work_name.")";
            //     }else{
            //       $expense_type=" (Office Expense Bill #".$value->reference_id." Payment)";
            //     }
            //     $transactions[$i]['date'] = $value->tr_date;
            //     $transactions[$i]['account'] = $account_details->acc_head_name;
            //     $transactions[$i]['description'] = "To ".$value->expense_head_name.$expense_type;
            //     $transactions[$i]['debit'] = $value->tr_amount;
            //     $transactions[$i]['credit'] = '';
            //     $transactions[$i]['type'] = 'debit';
            //     $i++;
            //   }
            // }else{
            //   $transactions_summary[$j]['description'] = "Expense Bill Payments";
            //   $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $other_expense_where,'expense_bill')->row()->total;
            //   $transactions_summary[$j]['type'] = 'debit';
            //   $j++;
            // }

            // Project Over Head
            $bills_where_2 = $bills_where;
            unset($bills_where_2['b.bill_expense_type']);
            $bills_where_2['b.bill_credit_ac'] = 32;
            $expense_bills_2 = $CI->Bills->select_bills("b.*,wc.work_name,ah1.acc_head_name",$bills_where_2)->result();
            if($data_return_type=='ledger'){
              foreach ($expense_bills_2 as $key => $value) {
                $expense_type=ucwords($value->bill_expense_type)." Expense";
                if($value->bill_expense_type=='work'){
                  $expense_type = 'Project Expense - #'.$value->work_id." ".$value->work_name;
                }
                $transactions[$i]['date'] = date('Y-m-d',strtotime($value->bill_date));
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "From Expense Bill #".$value->bill_id." (".$expense_type.")[".$value->acc_head_name."]";
                $transactions[$i]['debit'] = '';
                $transactions[$i]['credit'] = $value->bill_payable_amount;
                $transactions[$i]['type'] = 'credit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Receivable Expense Bill";
              $transactions_summary[$j]['amount'] = $CI->Bills->select_bills("COALESCE(SUM(b.bill_payable_amount),0) as total",$bills_where_2)->row()->total;
              $transactions_summary[$j]['type'] = 'debit';
              $j++;
            }

            // Project Over Head
            // $other_expense_where_2 = $other_expense_where;
            // unset($other_expense_where_2['b.bill_expense_type']);
            // $other_expense_where_2['b.bill_credit_ac'] = 32;
            // if($data_return_type=='ledger'){
            //   $expense_payments_2 = $CI->Account->select_account_transaction("ats.reference_id,ats.tr_amount,ats.tr_date,ah2.acc_head_name as expense_head_name,b.work_id,wc.work_name", $other_expense_where_2,'expense_bill')->result();
            //   foreach ($expense_payments_2 as $key => $value) {
            //     if($value->work_id!=0){
            //       $expense_type=" (Project Expense Bill #".$value->reference_id." Receipt For: ".$value->work_name.")";
            //     }else{
            //       $expense_type=" (Office Expense Bill #".$value->reference_id." Receipt)";
            //     }
            //     $transactions[$i]['date'] = $value->tr_date;
            //     $transactions[$i]['account'] = $account_details->acc_head_name;
            //     $transactions[$i]['description'] = "To ".$value->expense_head_name.$expense_type;
            //     $transactions[$i]['debit'] = '';
            //     $transactions[$i]['credit'] = $value->tr_amount;
            //     $transactions[$i]['type'] = 'credit';
            //     $i++;
            //   }
            // }else{
            //   $transactions_summary[$j]['description'] = "Expense Bill Receipt";
            //   $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $other_expense_where_2,'expense_bill')->row()->total;
            //   $transactions_summary[$j]['type'] = 'credit';
            //   $j++;
            // }

            if($account_details->acc_head_id==28){
              $salary_slip_where['ss.slip_type'] = 'Office';
            }else{
              $salary_slip_where['ss.slip_type'] = 'Project';
            }
            if($data_return_type=='ledger'){
              $salary_slips = $CI->Salary_slips->select_salary_slips("e.employee_name,e.civil_id,e.phone_no,e.user_designation,ss.*,ss.basic_wage_type as basic_wage_type,ss.created_by as ss_created_by",$salary_slip_where)->result();
              foreach ($salary_slips as $key => $value) {
                $transactions[$i]['date'] = date('Y-m-t',strtotime($value->month));
                $transactions[$i]['account'] = $account_details->acc_head_name;
                $transactions[$i]['description'] = "To Salary Slip #".$value->salary_id." (".date('F Y',strtotime($value->month)).") - ".$value->employee_name;
                $transactions[$i]['debit'] = $value->net_salary_amount;
                $transactions[$i]['credit'] = '';
                $transactions[$i]['type'] = 'debit';
                $i++;
              }
            }else{
              $transactions_summary[$j]['description'] = "Payable Salary Slip";
              $transactions_summary[$j]['amount'] = $CI->Salary_slips->select_salary_slips("COALESCE(SUM(ss.net_salary_amount),0) as total",$salary_slip_where)->row()->total;
              $transactions_summary[$j]['type'] = 'credit';
              $j++;
            }

            // Salary Slip Payment
            // $salary_payment_where2 = $salary_payment_where;
            // unset($salary_payment_where2['ats.tr_debit_acc']);
            // if($account_details->acc_head_id==28){
            //   $salary_payment_where2['ss.slip_type'] = 'Office';
            // }else{
            //   $salary_payment_where2['ss.slip_type'] = 'Project';
            // }
            // $salary_payment_where2['ats.reference_type'] = 'salary_slip';
            // $salary_payment_where2['ats.tr_type'] = 'salary_slip_transaction';
            // if($data_return_type=='ledger'){
            //   $salary_payments_received_for_employee = $CI->Account->select_account_transaction("ats.reference_id,ah1.acc_head_name as debit_account,ah2.acc_head_name as credit_account,ats.tr_amount,e.employee_name,ats.tr_date", $salary_payment_where2,'salary_slip')->result();
            //   foreach ($salary_payments_received_for_employee as $key => $value) {
            //     $transactions[$i]['date'] = $value->tr_date;
            //     $transactions[$i]['account'] = $account_details->acc_head_name;
            //     $transactions[$i]['description'] = "By ".$value->debit_account." (Salary Slip Payment #".$value->reference_id.")";
            //     $transactions[$i]['debit'] = $value->tr_amount;
            //     $transactions[$i]['credit'] = '';
            //     $transactions[$i]['type'] = 'debit';
            //     $i++;
            //   }
            // }else{
            //   $transactions_summary[$j]['description'] = "Salary Slip Payment";
            //   $transactions_summary[$j]['amount'] = $CI->Account->select_account_transaction("COALESCE(SUM(ats.tr_amount),0) as total", $salary_payment_where2,'salary_slip')->row()->total;
            //   $transactions_summary[$j]['type'] = 'debit';
            //   $j++;
            // }
          }
        }

        if($data_return_type=='ledger'){
          usort($transactions, function($a, $b) {
            // return $a['date'] <=> $b['date'];
            if ($a['date'] == $b['date']) return 0;
            return $a['date'] < $b['date'] ? -1 : 1;
          });
        }else{
          foreach ($transactions_summary as $key => $value) {
            if($value['type']=='debit'){
              $total_debit += $value['amount'];
            }elseif($value['type']=='credit'){
              $total_credit += $value['amount'];
            }
          }
          // if($account_type=='client'){
          $total_balance = $total_credit-$total_debit;
          // }else{
          //   $total_balance = $total_debit-$total_credit;
          // }
        }
        $json_data['transactions'] = $transactions;
        $json_data['selected_account_data'] = $account_details;
      }
    }
    if($data_return_type=='ledger'){
      return $json_data;
    }else{
      return $total_balance;
    }

  }
  // function amount_in_words_3($number) {
  //
  //   $hyphen      = '-';
  //   $conjunction = ' and ';
  //   $separator   = ' ';
  //   $negative    = 'negative ';
  //   $decimal     = ' point ';
  //   $dictionary  = array(
  //     0                   => 'zero',
  //     1                   => 'one',
  //     2                   => 'two',
  //     3                   => 'three',
  //     4                   => 'four',
  //     5                   => 'five',
  //     6                   => 'six',
  //     7                   => 'seven',
  //     8                   => 'eight',
  //     9                   => 'nine',
  //     10                  => 'ten',
  //     11                  => 'eleven',
  //     12                  => 'twelve',
  //     13                  => 'thirteen',
  //     14                  => 'fourteen',
  //     15                  => 'fifteen',
  //     16                  => 'sixteen',
  //     17                  => 'seventeen',
  //     18                  => 'eighteen',
  //     19                  => 'nineteen',
  //     20                  => 'twenty',
  //     30                  => 'thirty',
  //     40                  => 'fourty',
  //     50                  => 'fifty',
  //     60                  => 'sixty',
  //     70                  => 'seventy',
  //     80                  => 'eighty',
  //     90                  => 'ninety',
  //     100                 => 'hundred',
  //     1000                => 'thousand',
  //     100000             => 'lakh',
  //     10000000          => 'crore'
  //   );
  //
  //   if (!is_numeric($number)) {
  //     return false;
  //   }
  //
  //   if (($number >= 0 && (int) $number < 0) || (int) $number < 0 - PHP_INT_MAX) {
  //     // overflow
  //     trigger_error(
  //       'amount_in_words_3 only accepts numbers between -' . PHP_INT_MAX . ' and ' . PHP_INT_MAX,
  //       E_USER_WARNING
  //     );
  //     return false;
  //   }
  //
  //   if ($number < 0) {
  //     return $negative . $this->amount_in_words_3(abs($number));
  //   }
  //
  //   $string = $fraction = null;
  //
  //   if (strpos($number, '.') !== false) {
  //     list($number, $fraction) = explode('.', $number);
  //   }
  //
  //   switch (true) {
  //     case $number < 21:
  //     $string = $dictionary[$number];
  //     break;
  //     case $number < 100:
  //     $tens   = ((int) ($number / 10)) * 10;
  //     $units  = $number % 10;
  //     $string = $dictionary[$tens];
  //     if ($units) {
  //       $string .= $hyphen . $dictionary[$units];
  //     }
  //     break;
  //     case $number < 1000:
  //     $hundreds  = $number / 100;
  //     $remainder = $number % 100;
  //     $string = $dictionary[$hundreds] . ' ' . $dictionary[100];
  //     if ($remainder) {
  //       $string .= $conjunction . $this->amount_in_words_3($remainder);
  //     }
  //     break;
  //     case $number < 100000:
  //     $thousands   = ((int) ($number / 1000));
  //     $remainder = $number % 1000;
  //
  //     $thousands = $this->amount_in_words_3($thousands);
  //
  //     $string .= $thousands . ' ' . $dictionary[1000];
  //     if ($remainder) {
  //       $string .= $separator . $this->amount_in_words_3($remainder);
  //     }
  //     break;
  //     case $number < 10000000:
  //     $lakhs   = ((int) ($number / 100000));
  //     $remainder = $number % 100000;
  //
  //     $lakhs = $this->amount_in_words_3($lakhs);
  //
  //     $string = $lakhs . ' ' . $dictionary[100000];
  //     if ($remainder) {
  //       $string .= $separator . $this->amount_in_words_3($remainder);
  //     }
  //     break;
  //     case $number < 1000000000:
  //     $crores   = ((int) ($number / 10000000));
  //     $remainder = $number % 10000000;
  //
  //     $crores = $this->amount_in_words_3($crores);
  //
  //     $string = $crores . ' ' . $dictionary[10000000];
  //     if ($remainder) {
  //       $string .= $separator . $this->amount_in_words_3($remainder);
  //     }
  //     break;
  //     default:
  //     $baseUnit = pow(1000, floor(log($number, 1000)));
  //     $numBaseUnits = (int) ($number / $baseUnit);
  //     $remainder = $number % $baseUnit;
  //     $string = $this->amount_in_words_3($numBaseUnits) . ' ' . $dictionary[$baseUnit];
  //     if ($remainder) {
  //       $string .= $remainder < 100 ? $conjunction : $separator;
  //       $string .= $this->amount_in_words_3($remainder);
  //     }
  //     break;
  //   }
  //
  //   if (null !== $fraction && is_numeric($fraction)) {
  //     $string .= $decimal;
  //     $words = array();
  //     foreach (str_split((string) $fraction) as $number) {
  //       $words[] = $dictionary[$number];
  //     }
  //     $string .= implode(' ', $words);
  //   }
  //
  //   return $string;
  // }
  public function amount_in_words($number=""){

    $CI =& get_instance();
    // $no = floor($number);
    // $point = round($number - $no, 2) * 100;
    if($number!=0){
      $value = explode('.',$number);
      $no = $value[0];
      // Check have decimal
      if(isset($value[1])){
        $point = $value[1];
      }else{
        $point = '';
      }

      $hundred = null;
      $digits_1 = strlen($no);
      $i = 0;
      $str = array();
      $words = array('0' => '', '1' => 'One', '2' => 'Two',
      '3' => 'Three', '4' => 'Four', '5' => 'Five', '6' => 'Six',
      '7' => 'Seven', '8' => 'Eight', '9' => 'Nine',
      '10' => 'Ten', '11' => 'Eleven', '12' => 'Twelve',
      '13' => 'Thirteen', '14' => 'Fourteen',
      '15' => 'Fifteen', '16' => 'Sixteen', '17' => 'Seventeen',
      '18' => 'Eighteen', '19' =>'Nineteen', '20' => 'Twenty',
      '30' => 'Thirty', '40' => 'Forty', '50' => 'Fifty',
      '60' => 'Sixty', '70' => 'Seventy',
      '80' => 'Eighty', '90' => 'Ninety');
      $digits = array('', 'Hundred', 'Thousand', 'Lakh', 'Crore');
      while ($i < $digits_1) {
        $divider = ($i == 2) ? 10 : 100;
        $number = floor($no % $divider);
        $no = floor($no / $divider);
        $i += ($divider == 10) ? 1 : 2;
        if ($number) {
          $plural = (($counter = count($str)) && $number > 9) ? 's' : null;
          $hundred = ($counter == 1 && $str[0]) ? ' ' : null;
          $str [] = ($number < 21) ? $words[$number] .
          " " . $digits[$counter] . $plural . " " . $hundred
          :
          $words[floor($number / 10) * 10]
          . " " . $words[$number % 10] . " "
          . $digits[$counter] . $plural . " " . $hundred;
        } else $str[] = null;
      }
      $str = array_reverse($str);
      $result = implode('', $str);

      // $points = ($point) ?
      // " and " . $words[$point / 10] . " " .
      // $words[$point = $point % 10] : '';
      $points ='';
      if($point){
        if(isset($value[0]) && $value[0]!=0 && $point!=0){
          $points .= " and ";
        }
        $no = floor($point);
        $hundred = null;
        $digits_1 = strlen($no);
        $i = 0;
        $str = array();
        while ($i < $digits_1) {
          $divider = ($i == 2) ? 10 : 100;
          $number = floor($no % $divider);
          $no = floor($no / $divider);
          $i += ($divider == 10) ? 1 : 2;
          if ($number) {
            $plural = (($counter = count($str)) && $number > 9) ? 's' : null;
            $hundred = ($counter == 1 && $str[0]) ? ' and ' : null;
            $str [] = ($number < 21) ? $words[$number] .
            " " . $digits[$counter] . $plural . " " . $hundred
            :
            $words[floor($number / 10) * 10]
            . " " . $words[$number % 10] . " "
            . $digits[$counter] . $plural . " " . $hundred;
          } else $str[] = null;
        }
        $str = array_reverse($str);
        $point_result = implode('', $str);
        $points .= $point_result;
      }
      if($points!=''){
        $points .= " ".$CI->config->item('Currency_currency_subname');
      }
    }else{
      $result='Zero';
      $points='';
    }
    return $CI->config->item('Currency_currency_fullname')." ".$result.$points. " Only";
  }
  public function test(){
    // echo date_default_timezone_get();
  }
}
?>
