<?php

namespace App\Http\Controllers\Administrator;

use App\Classes\Reschedule;
use App\Classes\Commission;
use App\Classes\AgencyCommission;
use App\JournalAccountingModel;
use App\JournalModel;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use DB;
use DataTables;
use Illuminate\Support\Facades\Crypt;
use Session;
use Auth;
use App\ClientModel;
use App\ItemModel;
use App\LoanModel;
use App\LoanProcessModel;
use App\CommissionConditionModel;
use App\LoanPaidModel;
use App\BranchModel;
use App\SaleStockModel;
use App\CommissionModel;
use App\CommissionItemModel;
use App\CommissionItemReportModel;
use App\ClientRelationshipModel;
use App\SaleStockDetailModel;
use App\FundsRegisterModel;
use App\FundsTransactionModel;
use App\BankModel;
use App\AccountChartModel;
use App\AgencyLevelModel;
use App\AppEditPOS;
use App\DepositModel;
use App\Http\Controllers\Agencies\AdminAgency;
use App\User;
use App\AgencyTypeModel;
use App\AgencyGroupModel;
use App\AgencyCloseSaleModel;
use App\ApprovePaymentModel;
use App\BankAgencyModel;
use App\CancelPaidModel;
use App\SetCommissionCompanyModel;
use App\CommissionAdminReportModel;
use App\CommissionAdminModel;
use App\Helpers\Globals;
use App\RequestPayoffModel;
use Illuminate\Support\Carbon;
use Gate;
use App\ProjectModel;
use App\CategoryModel;
use App\DepositScheduleModel;
use App\SubCategoryModel;
use App\LoanChangeClientModel;
use Illuminate\Support\Arr;
use App\LoanPaidNextModel;
use PDO;

class ClientPaymentController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:External-Client-Payment-List', ['only' => ['get_index_company']]);
        $this->middleware('permission:Add-Commission', ['only' => ['index']]);
        $this->middleware('permission:Client-Payment-View', ['only' => ['show']]);
        $this->middleware('permission:Client-Payment-PayOff', ['only' => ['client_payment_payoff']]);
        $this->middleware('permission:Payment-Reject-Report-List', ['only' => ['rejectClientPayment']]);
        $this->middleware('permission:Client-Payment-View', ['only' => ['show']]);
        $this->middleware('permission:Client-Payment-Cancel-Loan',['only'=>['request_cancel']]);
        $this->middleware('permission:Client-Payment-Change-Client', ['only' => ['change_client']]);
        $this->middleware('permission:Client-Payment-PayOff', ['only' => ['client_payment_payoff']]);
        $this->middleware('permission:Client-Payment-View-Cancel-Loan',['only'=>['get_view_cancel_client_payment']]);
        $this->middleware('permission:Client-Payment-Approve-Cancel-Loan',['only'=>['approve_cancel']]);
        $this->middleware('permission:Client-Payment-Invoice-Print',['only'=>['client_purchase_list_print_invoice']]);
        $this->middleware('permission:Client-Payment-Pay',['only'=>['payment']]);
        $this->middleware('permission:Client-Loan-Bank-Follow-up-List',['only'=>['get_data_client_loan_bank']]);
        $this->middleware('permission:Approval-Payment-List',['only'=>['client_payment_index']]);
        $this->middleware('permission:Approval-Payment-Action-Approve',['only'=>['approve_client_payment']]);
        $this->middleware('permission:Approval-Payment-Action-Approve',['only'=>['rejectPayment']]);
        $this->middleware('permission:Payment-Reject-Report-Print',['only'=>['printrejectedClientPayment']]);
    }

    public function index()
    {
        $bank_data = AccountChartModel::select('id', 'acc_name')
            ->whereIn('id', [11001, 11002, 11012])
            ->pluck('acc_name', 'id')
            ->prepend('Please Select', '');
        // dd($bank_data);
        $agents = DB::table('users')->join('nso007_loan', 'users.id', 'nso007_loan.loan_agentid')
            ->select('users.id', DB::raw('CONCAT(users.user_firstname, " ", users.user_lastname) AS agent_name'))
            ->get();
        $agency = User::select('id', DB::raw('CONCAT(user_firstname, " ", user_lastname) AS agency_name'))
            ->where('user_status', 2)
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
        $condition = CommissionConditionModel::select('condition', 'id')->from('nso007_commission_condition')
            ->pluck('condition', 'id')
            ->prepend('Please Select', '');
        $commission = CommissionConditionModel::select('commission')->from('nso007_commission_condition')
            ->pluck('commission', 'commission')
            ->prepend('Please Select', '');
        return view('administrator.client-payment.list.index', compact('bank_data', 'agents', 'agency', 'condition', 'commission'));
    }
    public function get_client_payment_list()
    {
        $client = DB::table('nso007_loan')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->join('users as agensale', 'agensale.id', 'nso007_loan.loan_agentid')
            ->leftjoin('users as agenclose', 'agenclose.id', 'nso007_loan.loan_close_saleid')
            ->select(
                'nso007_category_stock.category_name',
                'nso007_project_stock.project_name',
                'nso007_sale_stock.id as sale_id',
                DB::raw('CONCAT(nso007_client.client_firstname," ",nso007_client.client_lastname) As client_fullname'),
                DB::raw('CONCAT(agensale.user_firstname," ",agensale.user_lastname) as sale_name'),
                DB::raw('CONCAT(agenclose.user_firstname," ",agenclose.user_lastname) as close_name'),
                'nso007_client.client_status',
                'nso007_client.client_contract',
                'nso007_client.id',
                'nso007_sale_stock.commission_request',
                'nso007_loan.loan_commission_status',
                DB::raw('GROUP_CONCAT(nso007_item_stock.item_house) As item_name'),
                DB::raw("lPAD(nso007_client.id,6,0) AS client_code")
            )
            ->Where('nso007_sale_stock.status_pos_approve_type', '!=', 5)
            ->where('nso007_loan.loan_cancel_status', '!=', 2)
            ->orWhereNull('nso007_loan.loan_cancel_status')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->groupBy('nso007_loan.id')
            ->get();

        return Datatables::of($client)
            ->addIndexColumn()
            ->editColumn('commission_amount', function ($client) {
                return "$ " . number_format($this->getTotalCommissionBySaleID($client->sale_id), 2);
            })
            ->editColumn('close_commission', function ($client) {
                $commission = CommissionModel::where([['sale_id',$client->sale_id],['agency_status','Close Sale']])->first();
                if(empty($commission)){
                    return "$ " . number_format($this->getTotalCloseCommissionForCloseSaleBySaleID($client->sale_id), 2);
                }
                return '$ '.number_format($commission->commission,2);
            })
            ->editColumn('detail_url', function ($client) {
                return route('commission-info.detail', $client->sale_id);
            })
            ->addColumn('action', function ($client) {
                $btn_commission = '';
                $btn_request_commission = '';
                if (!empty(\Branch::branch_key())) {

                    if ($client->loan_commission_status == 0) {
                        if ($client->commission_request == 2) {

                            $btn_request_commission = '<div class="dropdown">
                                    <button class="btn btn-flat btn-info dropdown-toggle btn-sm" type="button" id="dropdownMenu1" name="action" data-toggle="dropdown">
                                      Actions
                                        <span class="caret"></span>
                                    </button>
                                    <ul class="dropdown-menu dropdown-menu-right" role="menu" aria-labelledby="dropdownMenu1">
                                        ';

                            if (Gate::check('Approve-Add-Commission'))
                                $btn_request_commission .= '
                                        <li><a style="cursor:pointer" class="btn-circle btn-commission-info" title="Pay" data-id= "' . Crypt::encrypt($client->sale_id) . '"><strong class="text-warning"><span class="glyphicon glyphicon-repeat"></span> View</strong></a></li>
                                        <li><a style="color:white;margin-left:0;" href="#approve-commission" type="a" data-id= "' . Crypt::encrypt($client->sale_id) . '" class="btn-circle btn-approve-commission" title="Approve Commission"><strong class="text-warning"><span class="glyphicon glyphicon-saved"></span> Approve</strong></a></li>
                                        <li><a style="color:white;margin-left:0;" href="#approve-commission" type="a" data-id= "' . Crypt::encrypt($client->sale_id) . '" class="btn-circle btn-reject-commission" title="Reject Commission"><strong class="text-warning"><span class="fa fa-cut"></span> Reject</strong></a></li>';

                            $btn_request_commission .= '</ul></div>';
                            $btn_commission = $btn_request_commission;
                        } else {
                            if (Gate::check('Add-Commission')) {
                                $btn_commission = '<a style="color:white;margin-left:0;" href="#add-commission" type="a" data-id= "' . Crypt::encrypt($client->sale_id) . '" class="button_warning btn-circle btn-add-commission"><i class="fa fa-percent" title="Add Commission"></i></a>';
                            }
                        }
                    } else {
                        $btn_commission = '<a style="color:white;margin-left:0;" href="#add-commission" type="a" class="button_success btn-circle" title="Commission Paid"><i class="fa fa-check"></i></a>';
                    }
                }
                return $btn_commission;
            })
            ->rawColumns(['action' => 'action'])
            ->make(true);
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        $client_id = Crypt::decrypt($id);
        
        $bank_data = AccountChartModel::select('id', 'acc_name')
            ->whereIn('id', [11001, 11002, 11012])
            ->pluck('acc_name', 'id')
            ->prepend('Please Select', '');
            
        $method = AccountChartModel::select('id', 'acc_name')
            ->whereIn('id', [11001, 11002,11012])
            ->pluck('acc_name', 'id')
            ->prepend('Please Select', '');
        // dd($bank_data);
        $agents = DB::table('users')->join('nso007_loan', 'users.id', 'nso007_loan.loan_agentid')
            ->select('users.id', DB::raw('CONCAT(users.user_firstname, " ", users.user_lastname) AS agent_name'))
            ->get();
            
        $agency = User::select('id', DB::raw('CONCAT(user_firstname, " ", user_lastname) AS agency_name'))
            ->where('user_status', 2)
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
            
        $condition = CommissionConditionModel::select('condition', 'id')->from('nso007_commission_condition')
            ->pluck('condition', 'id')
            ->prepend('Please Select', '');
            
        $commission = CommissionConditionModel::select('commission')->from('nso007_commission_condition')
            ->pluck('commission', 'commission')
            ->prepend('Please Select', '');
        Session::put('client_id', $client_id);
        $client = ClientModel::select('id','client_fullname as client_name')
            ->pluck('client_name', 'id')
            ->prepend('Please Select', '');
            
        $data = DB::select("
            SELECT nso007_client.id,
            nso007_loan.id AS loan_id,
            nso007_loan.loan_amount,
            nso007_loan.loan_discount,
            nso007_sale_stock.sale_receive,
            nso007_loan.loan_amountos,
            detail.saledetail_itemid,
            item.item_name,
            item.item_street,
	        item.item_house,
	        ((nso007_sale_stock.sale_receive*100)/nso007_loan.loan_amount) as percentage,
            project.project_name,
            category.category_name,
            nso007_client.client_fullname,
            nso007_client.client_gender,
            nso007_client.client_phone1,
            nso007_client.client_cardid,
            nso007_client.client_img,
            nso007_client.client_dob,
            nso007_loan.loan_duration,
            nso007_loan.loan_interest
            FROM nso007_client
            LEFT JOIN nso007_loan ON nso007_loan.loan_clientid = nso007_client.id
            LEFT JOIN nso007_sale_stock ON nso007_sale_stock.id = nso007_loan.loan_saleid
            LEFT JOIN nso007_sale_detail_stock AS detail ON detail.saledetail_saleid = nso007_sale_stock.id
            LEFT JOIN nso007_item_stock AS item ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_project_stock AS project ON project.id = item.item_projectid
            LEFT JOIN nso007_category_stock AS category ON category.id = item.item_categoryid
            WHERE nso007_client.id = $client_id
        ");

        $total_saleamount = $data[0]->loan_amount - $data[0]->loan_amountos - $data[0]->loan_discount;
        $percentage = ($total_saleamount * 100) / (!empty($data[0]->loan_amount)?$data[0]->loan_amount:1);
        $banks = DB::table("nso007_bank")->pluck("name","id")->prepend("Please Select","");
        
        return view('administrator.client-payment.payment.index', compact('client','data', 'bank_data', 'percentage', 'agents', 'agency', 'condition', 'commission', 'method','banks'));
    }

    public function get_client_purchase_list()
    {
        $client_id = Session::get('client_id');
        $data = LoanModel::join('nso007_client as client', 'client.id', 'nso007_loan.loan_clientid')
            ->join('nso007_sale_stock as sale_stock', 'sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock as sale', 'sale.saledetail_saleid', 'sale_stock.id')
            ->join('nso007_item_stock as item', 'item.id', 'sale.saledetail_itemid')
            ->join('nso007_project_stock as project', 'project.id', 'item.item_projectid')
            ->join('nso007_category_stock as category', 'category.id', 'item.item_categoryid')
            ->select(
                'sale_stock.id',
                'nso007_loan.id AS loan_id',
                'nso007_loan.loan_amount',
                'nso007_loan.loan_cancel_status',
                DB::raw('(nso007_loan.loan_amount-nso007_loan.loan_discount) as salling_price'),
                'nso007_loan.loan_discount',
                'sale_stock.sale_receive',
                'sale_stock.commission_request',
                'sale_stock.status_pos_approve_type',
                'nso007_loan.loan_percentpay',
                'nso007_loan.request_payoff',
                'nso007_loan.loan_amountos',
                'nso007_loan.loan_status',
                'nso007_loan.loan_duration',
                DB::raw('GROUP_CONCAT(item.item_house) as item_name'),
                'client.id AS client_id',
                'project.project_name',
                'category.category_name',
                'nso007_loan.loan_interest',
                'nso007_loan.loan_commission_status',
                'nso007_loan.discount_commis',
                'nso007_loan.transfer_ownership_status',
                'nso007_loan.date_transfer_ownership'
            )->where([['client.id', $client_id]])
            ->where(function ($query) {
                $query->where('nso007_loan.loan_cancel_status', '!=', 2);
                $query->orwhere('nso007_loan.loan_cancel_status', null);
            })
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('project.branch_id', \Branch::branch_id());
            })
            ->groupBy('sale_stock.id')->get();
        return Datatables::of($data)
            ->addIndexColumn()
            ->setRowId('loan_id')
            ->addColumn('action', function ($data) {
                $btn_cancel = '';
                $btn_reschedule = '';
                $btn_payoff = '';
                $btn_schedule = '';
                $btn_contract = '';
                $change_item =  '';
                $btn_change_client='';
                $btn_contract_maintenance='';
                $btn_contract_step='';
                // $change_item .='<a style="color:white;margin-left:0;" href="'.route('change_item',Crypt::encrypt($data->loan_id)).'" class="button_primary btn-circle" title="Change Item"><i class="glyphicon glyphicon-transfer"></i></a>';
                //default button
                $status_check = $this->check_set_payment_complete_status($data->loan_id);
                if(Gate::check('Client-Payment-Cancel-Loan')){
                    if($status_check==0){//Payment don't completed yet
                        $btn_cancel = '<span class="button_danger btn-circle btn_cancel_payment" style="cursor: pointer;" data-id="' . Crypt::encrypt($data->loan_id) . '" title="Cancel Payment"><i class="fa fa-times" aria-hidden="true"></i></span>';
                    }
                }
                if(Gate::check('Client-Payment-Change-Client'))
                    $btn_change_client = '<a style="color:white;margin-left:0;" href="#change_client" type="a" data-id= "' . $data->loan_id . '" class="button_primary btn-circle btn-change-client"><i class="fa fa-user-plus" title="Change Client"></i></a>';

                if(Gate::check('Client-Payment-Reschedule')){
                    if($status_check==0){//Payment don't completed yet
                        $btn_reschedule = ' <a style="color:white;margin-left:0;" onclick = "performce_reschedule(this)" data-loan_id = "'.$data->loan_id.'" data-href="' . Route("client_payment_reschedule", array(Crypt::encrypt($data->loan_id), Crypt::encrypt($data->id))) . '" type="a" class="button_warning btn-circle"><i class="glyphicon glyphicon-edit" title="Reschedule"></i></a>';
                    }
                    
                }

                if(Gate::check('Client-Payment-PayOff')){
                    if($status_check==0){
                        $btn_payoff = '<a style="color:white;margin-left:0;" href="#payoff" onclick="payoff(this)" data-toggle="modal" class="button_danger btn-circle" title="Pay Off"><i class="glyphicon glyphicon-scissors"></i></a>';
                    }
                }
                if(Gate::check('Client-Payment-Schedule')){
                    $btn_schedule = '<a style="color:white;margin-left:0;" href="' . Route("schedule-client-payment.schedule", Crypt::encrypt($data->loan_id)) . '" target="_blank" class="button_primary btn-circle" title="Schedule"><i class="glyphicon glyphicon-list"></i></a>';
                }

                if(Gate::check('Client-Payment-Contract')){
                    $btn_contract = '<a style="color:white;margin-left:0;" href="' . Route("contract-client-payment.contract", Crypt::encrypt($data->loan_id)) . '" target="_blank" class="button_info btn-circle" title="កិច្ចសន្យាបង់រំលស់"><i class="glyphicon glyphicon-list-alt"></i></a>';
                    $btn_contract_step = '<a style="color:white;margin-left:0;" href="' . Route("contract-client-payment-step.contract", Crypt::encrypt($data->loan_id)) . '" target="_blank" class="button_info btn-circle" title="កិច្ចសន្យាបង់ផ្តាច់"><i class="glyphicon glyphicon-list-alt"></i></a>';
                }

               if(Gate::check('Client-Payment-Contract-maintenance')){
                $btn_contract_maintenance = '<a style="color:white;margin-left:0;" href="' . Route("contract-maintenance-client-fee", Crypt::encrypt($data->loan_id)) . '" class="button_success btn-circle" title="ឧបសម្ព័ន្ធ2"><i class="glyphicon glyphicon-list-alt"></i></a>';
               }
           
                //default button
                //if has cancel request
                if($data->loan_cancel_status == 1){
                    $btn_cancel = '';
                    $btn_payoff= '';
                    $btn_reschedule = '';
                    if(Gate::check('Client-Payment-View-Cancel-Loan')){
                        $btn_cancel .= '<a style="color:white;margin-left:0;" href="' . Route("datatable.get_view_cancel_client_payment", Crypt::encrypt($data->loan_id)) . '" target="_blank" class="button_primary btn-circle" title="View Cancel Payment"><i class="fa fa-eye"></i></a>';
                    }
                    if(Gate::check('Client-Payment-Approve-Cancel-Loan')){
                        $btn_cancel .= '<span class="button_info btn-circle btn_approve_cancel" style="cursor: pointer;" data-id="' . Crypt::encrypt($data->loan_id) . '" title="Approve Cancel Payment">approve</i></span>';
                    }
                    if(Gate::check('Client-Payment-Reject-Cancel-Loan')){
                        $btn_cancel .= '<span class="button_danger btn-circle btn_reject_cancel" style="cursor: pointer;" data-id="' . Crypt::encrypt($data->loan_id) . '" title="Reject Cancel Payment">reject</i></span>';
                    }
                }
                //if has cancel request

                //if has payoff request
                if($data->request_payoff==1){
                    $btn_payoff = '';
                    $btn_cancel = '';
                    $btn_reschedule = '';
                    if(Gate::check('Client-Payment-Approve-Payoff')){
                        $btn_payoff .= '<span class="button_info btn-sm btn-approve-payoff" data-id="'.Crypt::encrypt($data->loan_id).'" title="Approve Payoff"><i class="fa fa-check-circle"></i></span>';
                    }
                    if(Gate::check('Client-Payment-Reject-Payoff')){
                        $btn_payoff .= '<span class="button_danger btn-sm btn-reject-payoff" data-id="'.Crypt::encrypt($data->loan_id).'"  title="Reject Payoff"><i class="fa fa-times-circle"></i></span>';
                    }
                }
                //if has payoff request
                // if ($data->status_pos_approve_type == 7) {

                //     $action = '<div class="dropdown">
                //                 <button class="btn btn-flat btn-info dropdown-toggle btn-sm" type="button" id="dropdownMenu1" name="action" data-toggle="dropdown">
                //                   Actions
                //                     <span class="caret"></span>
                //                 </button>
                //                 <ul class="dropdown-menu dropdown-menu-right" role="menu" aria-labelledby="dropdownMenu1">
                //                     <li><a style="color:white;margin-left:0;" href="' . route('reschedule-client-payment.show', $data->id) . '" data-toggle="modal" title="View-Reschedule" class="btn-xs"><strong class="text-warning"><span class="fa fa-eye"></span> view reschedule</strong></a></li>
                //                      <li><a style="color:white;margin-left:0;" href="#approve-reschedule" onclick="approvedReschedule(' . $data->id . ')" data-toggle="modal" title="Approve Instalment" class="btn-xs"><strong class="text-warning"><span class="glyphicon glyphicon-saved"></span> Approve reschedule</strong></a></li>
                //                      <li><a style="color:white;margin-left:0;" onclick="rejectReschedule(' . $data->id . ')" title="Reject Reschedule" class="btn-xs btn"><strong class="text-warning"><span class="fa fa-cut"></span> Reject reschedule</strong></a></li>
                //                 </ul>
                //              </div>';
                //     return $action;
                // }
                $completed_status_payment = "";
                $transfer_ownership = "";
                $date_transfer = "";
                if($status_check==1){
                    $completed_status_payment = '<span class="btn-success btn-sm" title="Payment is completed"><i class="fa fa-check"></i></span>';
                }
                if($data->transfer_ownership_status==0&&!empty($data->date_transfer_ownership)){
                    $transfer_ownership = '&nbsp;<span class="btn-primary btn-sm" title="Transfer ownership" data-loan_id = "'.$data->loan_id.'" data-client_id = "'.$data->client_id.'" id="transfer-owner-ship"><i class="glyphicon glyphicon-transfer"></i></span>';
                    
                }
                elseif($data->transfer_ownership_status==1){
                    $transfer_ownership = '&nbsp;<span class="btn-success btn-sm" title="Transfer ownership completely"><i class="glyphicon glyphicon-transfer"></i></span>';
                }
                else{
                    $date_transfer = '&nbsp;<span class="btn-primary btn-sm" title="Set Date To Transfer ownership" data-loan_id = "'.$data->loan_id.'" data-client_id = "'.$data->client_id.'" id="set-date-to-transfer-owner-ship"><i class="glyphicon glyphicon-pencil"></i></span>';;
                }
                if(Gate::check('Client-Payment-Transfer-Ownership')){

                }
                else{
                    $transfer_ownership = "";
                    $date_transfer = '';
                }
                
                //if client want to pay off, please reschedule for one month and then make payment; 

                return $btn_schedule.$btn_contract.$btn_contract_step.$btn_contract_maintenance.$btn_cancel.$btn_reschedule.$btn_change_client   .$change_item.$completed_status_payment.$transfer_ownership.$date_transfer;
            })
            ->addColumn('details_url', function ($data) {
                if ($data->status_pos_approve_type == 7) return 0;
                return route('datatable.get_client_payment_process', $data->loan_id);
            })
            ->editColumn('salling_price', function ($data) {
                return "$" . number_format($data->salling_price, 2);
            })
            ->editColumn('amountOS', function ($data) {
                $remain = $data->loan_amountos;
                if ($remain < 0) {
                    $remain = 0;
                }
                return "$" . number_format($remain, 2);
            })
            ->editColumn('deposit', function ($data) {
                /*return "$ " . number_format(DepositModel::select(DB::raw('SUM(deposit_amount) as amount'))->where('deposit_saleid', $data->id)->first()->amount, 2);*/
                $deposit = DepositScheduleModel::select(DB::raw('SUM(ds_amount) as amount'))->where("ds_saleid",$data->id)->where("type",0)->first();
                return "$".number_format((!empty(@$deposit->amount)?@$deposit->amount:0),2);
            })
            ->editColumn('paid_deposit', function ($data) {
                $deposit = DepositScheduleModel::select(DB::raw('SUM(paid_amount) as amount'))->where("ds_saleid",$data->id)->where("type",0)->first();
                return "$".number_format((!empty(@$deposit->amount)?@$deposit->amount:0),2);
            })
            ->editColumn('down_payment', function ($data) {
                $deposit = DepositScheduleModel::select(DB::raw('SUM(ds_amount) as amount'))->where("ds_saleid",$data->id)->whereIn("type",[1,2])->first();
                return "$".number_format((!empty(@$deposit->amount)?@$deposit->amount:0),2);
            })
            ->editColumn('paid_down_payment', function ($data) {
                $deposit = DepositScheduleModel::select(DB::raw('SUM(paid_amount) as amount'))->where("ds_saleid",$data->id)->whereIn("type",[1,2])->first();
                return "$".number_format((!empty(@$deposit->amount)?@$deposit->amount:0),2);
            })
            ->editColumn('loan_percentpay', function ($data) {
                return number_format($data->loan_percentpay, 2) . " %";
            })
            ->editColumn('loan_duration', function ($data) {
                return $data->loan_duration;
            })
            ->rawColumns(['details_url' => 'details_url', 'action' => 'action'])
            ->make(true);
    }
    public function check_nonepayment_approve_cash($loan_id){

        $data_check = LoanProcessModel::join("nso007_loan_paid","nso007_loan_paid.loanpaid_processid","nso007_loan_process.id")
            ->where('nso007_loan_paid.loanpaid_approved', null)
            ->where("loanprocess_loanid",$loan_id)
            ->count();
        $check_paynotenght = LoanProcessModel::where("loanprocess_loanid",$loan_id)
                            ->whereNotNull('loanprocess_paid')
                            ->where("loanprocess_owepay",">",0)
                            ->count();
        if($data_check>0) return response()->json(['message'=>'Please approve payment !','status'=>false]);
        else if($check_paynotenght>0) return response()->json(['message'=>'Please make full payment for each month !','status'=>false]);
        else return response()->json(['message'=>'','status'=>true]);
    }

    public function get_index_company($id)
    {
        $client_id = Crypt::decrypt($id);
        $bank_data = AccountChartModel::select('id', 'acc_name')
            ->whereIn('id', [11001, 11002])
            ->pluck('acc_name', 'id')
            ->prepend('Please Select', '');
        // dd($bank_data);
        $agents = DB::table('users')->join('nso007_loan', 'users.id', 'nso007_loan.loan_agentid')
            ->select('users.id', DB::raw('CONCAT(users.user_firstname, " ", users.user_lastname) AS agent_name'))
            ->get();
        $condition = CommissionConditionModel::select('condition', 'id')->from('nso007_commission_condition')
            ->pluck('condition', 'id')
            ->prepend('Please Select', '');
        $commission = CommissionConditionModel::select('commission')->from('nso007_commission_condition')
            ->pluck('commission', 'commission')
            ->prepend('Please Select', '');
        Session::put('client_id', $client_id);
        $agency = User::select('users.id', DB::raw('CONCAT(users.user_firstname, " ", users.user_lastname) AS agency_name'))
            ->join('nso007_funds', 'nso007_funds.user_id', 'users.id')
            ->where('users.user_status', 2)
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
        $data = DB::select("
            SELECT nso007_client.id,
            nso007_loan.id AS loan_id,
            nso007_loan.loan_amount,
            nso007_loan.loan_discount,
            nso007_sale_stock.sale_receive,
            nso007_loan.loan_amountos,
            detail.saledetail_itemid,
            item.item_name,
            item.item_street,
	        item.item_house,
	        ((nso007_sale_stock.sale_receive*100)/nso007_loan.loan_amount) as percentage,
            project.project_name,
            category.category_name,
            nso007_client.client_fullname,
            nso007_client.client_gender,
            nso007_client.client_img,
            nso007_client.client_phone1,
            nso007_client.client_cardid,
            nso007_client.client_dob,
            nso007_loan.loan_duration,
            nso007_loan.loan_interest
            FROM nso007_client
            LEFT JOIN nso007_loan ON nso007_loan.loan_clientid = nso007_client.id
            LEFT JOIN nso007_sale_stock ON nso007_sale_stock.id = nso007_loan.loan_saleid
            LEFT JOIN nso007_sale_detail_stock AS detail ON detail.saledetail_saleid = nso007_sale_stock.id
            LEFT JOIN nso007_item_stock AS item ON item.id = detail.saledetail_itemid
            LEFT JOIN nso007_project_stock AS project ON project.id = item.item_projectid
            LEFT JOIN nso007_category_stock AS category ON category.id = item.item_categoryid
            WHERE nso007_client.id = $client_id
        ");
        $total_saleamount = $data[0]->loan_amount - $data[0]->loan_amountos - $data[0]->loan_discount;
        $percentage = ($total_saleamount * 100) / $data[0]->loan_amount;
        return view('administrator.client-payment.payment.index_company', compact('agency', 'data', 'bank_data', 'percentage', 'agents', 'condition', 'commission'));
    }

    public function get_index_company_list()
    {
        $client_id = Session::get('client_id');
        $data = DB::select("
            SELECT nso007_sale_stock.id,
            nso007_loan.id AS loan_id,
            nso007_loan.loan_amount,
            nso007_loan.loan_cancel_status,
            (nso007_loan.loan_amount-nso007_loan.loan_discount-nso007_loan.discount_commis) as salling_price,
            nso007_loan.loan_discount,
            nso007_sale_stock.sale_receive,
            nso007_sale_stock.commission_request,
            nso007_sale_stock.status_pos_approve_type,
            nso007_loan.loan_percentpay,
            nso007_loan.loan_amountos,
            nso007_loan.loan_status,
            detail.saledetail_itemid,
            GROUP_CONCAT(item.item_house) as item_name,
            nso007_client.id AS client_id,
            project.project_name,
            category.category_name,
            nso007_loan.loan_duration,
            nso007_loan.loan_discount,
            nso007_loan.discount_commis,
            nso007_loan.loan_duration,
            nso007_loan.loan_interest,
            nso007_loan.loan_commission_status,
            nso007_loan.loan_downpayment,
            nso007_loan.discount_commis,
            users.user_firstname,
            users.user_lastname
            FROM nso007_client
            INNER JOIN nso007_loan ON nso007_loan.loan_clientid = nso007_client.id
            INNER JOIN nso007_sale_stock ON nso007_sale_stock.id = nso007_loan.loan_saleid
            INNER JOIN nso007_sale_detail_stock AS detail ON detail.saledetail_saleid = nso007_sale_stock.id
            INNER JOIN nso007_item_stock AS item ON item.id = detail.saledetail_itemid
            INNER JOIN nso007_project_stock AS project ON project.id = item.item_projectid
            INNER JOIN nso007_category_stock AS category ON category.id = item.item_categoryid
            INNER JOIN users ON users.id = nso007_loan.loan_agentid
            WHERE nso007_loan.loan_cancel_status !=2 OR nso007_loan.loan_cancel_status IS NULL AND nso007_client.id = $client_id
            GROUP BY nso007_sale_stock.id
        ");
        return Datatables::of($data)
            ->addIndexColumn()
            ->setRowId('loan_id')
            ->addColumn('action', function ($data) {
                $btn_commission = '';
                if (!empty(\Branch::branch_key())) {

                    if ($data->loan_commission_status == 0) {
                        if ($data->commission_request == 2) {
                            $btn_request_commission = '<div class="dropdown">
                                <button class="btn btn-flat btn-info dropdown-toggle btn-sm" type="button" id="dropdownMenu1" name="action" data-toggle="dropdown">
                                  Actions
                                    <span class="caret"></span>
                                </button>
                                <ul class="dropdown-menu dropdown-menu-right" role="menu" aria-labelledby="dropdownMenu1">
                                    <li><a style="cursor:pointer" class="btn-circle btn-commission-info" title="Pay" data-id= "' . Crypt::encrypt($data->id) . '"><strong class="text-warning"><span class="glyphicon glyphicon-repeat"></span> View</strong></a></li> 
                                    <li><a style="color:white;margin-left:0;" href="#approve-commission" type="a" data-id= "' . Crypt::encrypt($data->id) . '" class="btn-circle btn-approve-commission" title="Approve Commission"><strong class="text-warning"><span class="glyphicon glyphicon-saved"></span> Approve</strong></a></li>
                                    <li><a style="color:white;margin-left:0;" href="#approve-commission" type="a" data-id= "' . Crypt::encrypt($data->id) . '" class="btn-circle btn-reject-commission" title="Reject Commission"><strong class="text-warning"><span class="fa fa-cut"></span> Reject</strong></a></li>
                                </ul>
                             </div>';
                            // $btn_request_commission = '<a data-toggle="modal" href="#commission_info" class="button_warning btn-circle"  title="Pay" >Pending</a> 
                            //                            <a style="color:white;margin-left:0;" href="#approve-commission" type="a" data-id= "' . Crypt::encrypt($data->id) . '" class="button_info btn-circle btn-approve-commission" title="Approve Commission">Approve</a>
                            //                            <a style="color:white;margin-left:0;" href="#approve-commission" type="a" data-id= "' . Crypt::encrypt($data->id) . '" class="button_danger btn-circle btn-reject-commission" title="Reject Commission">reject</a>';
                            $btn_commission = $btn_request_commission;
                        } else {
                            $btn_commission = '<a style="color:white;margin-left:0;" href="#add-commission" type="a" data-id= "' . Crypt::encrypt($data->id) . '" class="button_warning btn-circle btn-add-commission"><i class="fa fa-percent" title="Add Commission"></i></a>';
                        }
                    } else {
                        $btn_commission = '<a style="color:white;margin-left:0;" href="#add-commission" type="a" class="button_success btn-circle" title="Commission Paid"><i class="fa fa-check"></i></a>';
                    }
                }

                return $btn_commission;
            })
            ->addColumn('details_url', function ($data) {
                return route('datatable.get_client_payment_process', $data->loan_id);
            })
            ->editColumn('agency', function ($data) {
                return $data->user_firstname . " " . $data->user_lastname;
            })
            ->editColumn('price', function ($data) {
                return "$ " . number_format($data->loan_amount, 2);
            })
            ->editColumn('discount', function ($data) {
                return "$ " . number_format($data->loan_discount, 2);
            })
            ->editColumn('discount_commission', function ($data) {
                return "$ " . number_format($data->discount_commis, 2);
            })
            ->editColumn('salling_price', function ($data) {
                return "$ " . number_format($data->salling_price, 2);
            })
            ->editColumn('loan_percentpay', function ($data) {
                return number_format($data->loan_percentpay, 2) . " %";
            })
            ->addColumn('total_commission', function ($data) {
                $id = $data->id;
                $sale_detail    = SaleStockDetailModel::where('saledetail_saleid', $id)->get();
                $sale_comm      = LoanModel::where('loan_saleid', $id)->first();
                $agency         = User::select(DB::raw("CONCAT(user_firstname ,' ',user_lastname) AS agency_name"), 'id', 'agency_type', 'group_id')->where('id', $sale_comm->loan_agentid)->get();
                $agency_type    = AgencyTypeModel::where('id', $agency[0]->agency_type)->get();
                $group_type     = AgencyGroupModel::where('id', $agency[0]->group_id)->get();


                if ($agency_type[0]->agency_type_name == 'Agent Network') {
                    $agency_type_amount_get = $agency_type[0]->agency_type_amount_get;
                } else {
                    $agency_type_amount_get = $group_type[0]->agency_group_amount_get;
                }

                $commission_amount  = 0;

                foreach ($sale_detail as $detail) {
                    $item       = ItemModel::find($detail->saledetail_itemid);
                    $commission = $item->commission_amount;
                    if ($commission == 0) {
                        $commission = floatval($item->item_price) * floatval($item->commission) / 100;
                    }
                    $commission_amount += $commission;
                }
                $total_comm1 = $commission_amount;
                $total_comm = (($total_comm1 * $agency_type_amount_get) / 100) - $sale_comm->discount_commis;
                return "$ " . number_format($total_comm, 2);
            })
            ->rawColumns(['details_url' => 'details_url', 'action' => 'action'])
            ->make(true);
    }


    public function get_commission_amount($id)
    {
        $id             = Crypt::decrypt($id);
        $sale_detail    = SaleStockDetailModel::where('saledetail_saleid', $id)->get();
        $sale_comm      = LoanModel::where('loan_saleid', $id)->first();
        $agency         = User::select(DB::raw("CONCAT(user_firstname ,' ',user_lastname) AS agency_name"), 'id', 'agency_type', 'group_id')->where('id', $sale_comm->loan_agentid)->get();

        $close_saleid   = User::select(DB::raw("CONCAT(user_firstname ,' ',user_lastname) AS agency_name"), 'id', 'agency_type', 'group_id')->where('id', $sale_comm->loan_close_saleid)->get();
        $agency_type    = AgencyTypeModel::where('id', $agency[0]->agency_type)->get();
        $group_type     = AgencyGroupModel::where('id', $agency[0]->group_id)->get();
        $close_sale_type = AgencyCloseSaleModel::get();
        $comm_company   = SetCommissionCompanyModel::get();

        $agency_id          = $agency[0]->id;
        $agency_name        = $agency[0]->agency_name;
        $close_sale_id      = $close_saleid[0]->id;
        $close_sale_name    = $close_saleid[0]->agency_name;

        if ($agency_type[0]->agency_type_name == 'Agent Network') {
            $agency_type_amount_get = $agency_type[0]->agency_type_amount_get;
            $agency_type_condition  = $agency_type[0]->agency_type_condition;
            $agency_type_percentage = $agency_type[0]->agency_type_percentage;
        } else {
            $agency_type_amount_get = $group_type[0]->agency_group_amount_get;
            $agency_type_condition  = $group_type[0]->agency_group_condition;
            $agency_type_percentage = $group_type[0]->agency_group_percentage;
        }

        $agency_type_id     = $agency_type[0]->id;
        $agency_type_name   = $agency_type[0]->agency_type_name;
        $commission_amount  = 0;

        foreach ($sale_detail as $detail) {
            $item       = ItemModel::find($detail->saledetail_itemid);
            $commission = $item->commission_amount;
            if ($commission == 0) {
                $commission = floatval($item->item_price) * floatval($item->commission) / 100;
            }
            $commission_amount += $commission;
        }
        $total_comm1    = $commission_amount;
        $total_comm     = (($total_comm1 * $agency_type_amount_get) / 100) - $sale_comm->discount_commis;

        $close_sale         = ($commission_amount * $close_sale_type[0]->agency_close_sale_amount_get) / 100;
        $close_sale_condi   = $close_sale_type[0]->agency_close_sale_condition;
        $close_sale_commis  = $close_sale_type[0]->agency_close_sale_percentage;

        $company_amount = ($commission_amount * $comm_company[0]->agency_company_amount_get) / 100;
        $company_condi  = $comm_company[0]->agency_company_condition;
        $company_commis = $comm_company[0]->agency_company_percentage;

        $data = array(
            'total_comm'                => $total_comm,
            'agency_id'                 => $agency_id,
            'agency_type_id'            => $agency_type_id,
            'close_sale_id'             => $close_sale_id,
            'close_sale_name'           => $close_sale_name,
            'agency_type_name'          => $agency_type_name,
            'agency_type_amount_get'    => $agency_type_amount_get,
            'agency_type_condition'     => $agency_type_condition,
            'agency_type_percentage'    => $agency_type_percentage,
            'company_amount'            => $company_amount,
            'company_condi'             => $company_condi,
            'company_commis'            => $company_commis,
            'close_sale'                => $close_sale,
            'close_sale_condi'          => $close_sale_condi,
            'close_sale_commis'         => $close_sale_commis,
            'agency_name'               => $agency_name
        );
        echo json_encode($data);
    }
    //update comission here
    public function getAgencyLevel($agency_id)
    {
        $level          = AgencyLevelModel::all();
        $countLevel     = count($level);
        $agency         = User::find($agency_id);
        $arrayAgency    = [$agency];
        $item           = $agency;
        $index          = 1;
        if ($item->agency_type == 1) { //network
            while ($item->parent_id != 0 && !empty($item->parent_id) && $index <= $countLevel) {
                $item = User::find($item->parent_id);
                array_push($arrayAgency, $item);
                $index++;
            }
        } else { //group
            while ($item->parent_id != 0 && !empty($item->parent_id) && $index <= 3) {
                $item = User::find($item->parent_id);
                array_push($arrayAgency, $item);
                $index++;
            }
        }
        return $arrayAgency;
    }
    public function validateAccountFunds($arrayAgency)
    {
        $status = true;
        $error  = '';
        foreach ($arrayAgency as $key => $agency) {
            $account = FundsRegisterModel::where('user_id', $agency->id)->first();
            if (empty($account)) {
                $error = "Agency Name : " . $agency->user_firstname . " " . $agency->user_lastname . " has not registered fund yet!";
                $status = false;
            }
        }
        return ['error' => $error, 'status' => $status];
    }
    public function getMainSaleAgency($sale_id)
    {
        $loan = LoanModel::where('loan_saleid', $sale_id)->first();
        $arrayMainAgency = [];
        if (!empty($loan->loan_agentid)) {
            array_push($arrayMainAgency, User::find($loan->loan_agentid));
        }
        if (!empty($loan->loan_close_saleid)) {
            array_push($arrayMainAgency, User::find($loan->loan_close_saleid));
        }
        return $arrayMainAgency; //return agency sale and close sale
    }
    public function getTotalCommissionBySaleID($sale_id)
    {
        $items = DB::table('nso007_item_stock as item')
            ->join('nso007_sale_detail_stock as detail', 'detail.saledetail_itemid', 'item.id')
            ->select('item.*')
            ->where('detail.saledetail_saleid', $sale_id)
            ->get();
        $TotalCommission = 0;
        foreach ($items as $item) {
            if ($item->commission_amount > 0) {
                $TotalCommission += $item->commission_amount;
            } else {
                $commission = $item->item_price * $item->commission / 100;
                $TotalCommission += $commission;
            }
        }
        $loan = LoanModel::where('loan_saleid', $sale_id)->first();
        if (User::find($loan->loan_agentid)->agency_type == 1) {
            $TotalCommission = AgencyTypeModel::find(1)->agency_type_amount_get * $TotalCommission / 100;
        }
        return floatval($TotalCommission);
    }
    public function getItemBysaleID($sale_id)
    {
        return SaleStockDetailModel::where('saledetail_saleid', $sale_id)->select(DB::Raw('GROUP_CONCAT(saledetail_itemid) As itemID'))->first()->itemID;
    }
    public function getItemCostBySaleID($sale_id)
    {
        return DB::table('nso007_item_stock as stock')->join('nso007_sale_detail_stock as detail', 'detail.saledetail_itemid', 'stock.id')
            ->where('saledetail_saleid', $sale_id)->select(DB::Raw('SUM(stock.item_cost) as cost'))->first()->cost;
    }
    public function ConditionCommission($agency_id)
    {
        $agency = User::find($agency_id);
        $conditioncommission = [];
        if ($agency->agency_type == 1) {
            $conditioncommission['condition']   = AgencyTypeModel::find(1)->agency_type_condition;
            $conditioncommission['commission']  = AgencyTypeModel::find(1)->agency_type_percentage;
        } else {
            $conditioncommission['condition']   = AgencyGroupModel::find($agency->group_id)->agency_group_condition;
            $conditioncommission['commission']  = AgencyGroupModel::find($agency->group_id)->agency_group_percentage;
        }
        return $conditioncommission;
    }
    public function addMainAgencyCommission($sale_id)
    {
        $loan                   = LoanModel::where('loan_saleid', $sale_id)->first();
        $agencySale             = $loan->loan_agentid;
        $agencyClose            = $loan->loan_close_saleid;
        $totalCommission        = $this->getTotalCommissionBySaleID($sale_id);
        $ConditionCommission    = $this->ConditionCommission($agencySale);

        $ConditionCloseSale     = AgencyCloseSaleModel::whereNotNull('id')->first();
        $CloseCommission        = $totalCommission * $ConditionCloseSale->agency_close_sale_amount_get / 100;
        CommissionModel::insert([
            'sale_id'           => $sale_id,
            'agency_id'         => $agencySale,
            'agency_type'       => User::find($agencySale)->agency_type,
            'client_id'         => SaleStockModel::find($sale_id)->sale_clientid,
            'item_id'           => $this->getItemBysaleID($sale_id),
            'item_cost'         => $this->getItemCostBySaleID($sale_id),
            'item_price'        => $loan->loan_amount,
            'commission'        => $totalCommission,
            'condition'         => $ConditionCommission['condition'],
            'commis_condition'  => $ConditionCommission['commission'],
            'agency_status'     => 'Sale',
            'status'            => 1
        ]);
        CommissionModel::insert([
            'sale_id'           => $sale_id,
            'agency_id'         => $agencyClose,
            'agency_type'       => User::find($agencyClose)->agency_type,
            'client_id'         => SaleStockModel::find($sale_id)->sale_clientid,
            'item_id'           => $this->getItemBysaleID($sale_id),
            'item_cost'         => $this->getItemCostBySaleID($sale_id),
            'item_price'        => $loan->loan_amount,
            'commission'        => $CloseCommission,
            'condition'         => $ConditionCloseSale->agency_close_sale_condition,
            'commis_condition'  => $ConditionCloseSale->agency_close_sale_percentage,
            'agency_status'     => 'Close Sale',
            'status'            => 1
        ]);
        $commissionSetForAdmin = SetCommissionCompanyModel::whereNotNull('id')->first();
        CommissionAdminModel::insert([
            'sale_id'               => $sale_id,
            'agent_id'              => $agencySale,
            'agent_type'            => User::find($agencySale)->agency_type,
            'client_id'             => SaleStockModel::find($sale_id)->sale_clientid,
            'item_id'               => $this->getItemBysaleID($sale_id),
            'item_cost'             => $this->getItemCostBySaleID($sale_id),
            'item_price'            => $loan->loan_amount,
            'commission'            => $this->getTotalCommissionBySaleID($sale_id),
            'condition'             => $commissionSetForAdmin->agency_company_condition,
            'commis_condition'      => $commissionSetForAdmin->agency_company_percentage,
            'commission_amount'     => $this->getTotalCommissionForCloseSaleBySaleID($sale_id) * $commissionSetForAdmin->agency_company_amount_get / 100
        ]);
        SaleStockModel::where('id', $sale_id)
            ->update([
                'commission_request'    => 2
            ]);
    }
    public function addCommissionList($sale_id)
    {
        $commission = CommissionModel::where([['sale_id', $sale_id], ['agency_status', 'Sale']])->first();
        $arrayRelatedAgencyList = $this->arrayRelatedAgencyList($sale_id);
        foreach ($arrayRelatedAgencyList as $key => $agency) {
            CommissionItemModel::insert([
                'com_list_sale_id'  => $sale_id,
                'commission_id'     => $commission->id,
                'agency_id'         => $agency['agency_id'],
                'agency_level'      => $agency['level'],
                'commission'        => $agency['percent'],
                'commis_condition'  => $agency['commission'],
                'condition'         => $agency['condition'],
                'amount'            => $agency['amount']
            ]);
        }
    }
    public function commissionInfoBasedAgencyNetwork($totalCommission, $ag, $level)
    {
        $commissionInfo                 = [];
        $commissionInfo['amount']       = (float) $totalCommission * $level->commission_level / 100;
        $commissionInfo['condition']    = AgencyTypeModel::find(1)->agency_type_condition;
        $commissionInfo['commission']   = AgencyTypeModel::find(1)->agency_type_percentage;
        $commissionInfo['percent']      = $level->commission_level;
        return $commissionInfo;
    }
    public function commissionInfoBasedAgencyGroup($totalCommission, $ag)
    {
        $commissionInfo = [];
        $group = AgencyGroupModel::find($ag->group_id);
        $commissionInfo['amount']       = $totalCommission * $group->agency_group_amount_get / 100;
        $commissionInfo['condition']    = $group->agency_group_condition;
        $commissionInfo['commission']   = $group->agency_group_percentage;
        $commissionInfo['percent']      = $group->agency_group_amount_get;
        // dd($group->agency_group_amount_get);
        return $commissionInfo;
    }
    public function arrayRelatedAgencyList($sale_id)
    {
        $loan                   = LoanModel::where('loan_saleid', $sale_id)->first();
        $agency                 = User::find($loan->loan_agentid);
        $arrayRelatedAgencyList = [];
        $arrayAgency            = $this->getAgencyLevel($loan->loan_agentid);
        array_push($arrayAgency, User::find($loan->loan_close_saleid));
        $level = count($arrayAgency);
        $totalLevel = $level;
        if ($agency->agency_type == 1) { //network

            $networkLevel = AgencyLevelModel::orderBy('agency_level', 'desc')->get();
            foreach ($arrayAgency as $key => $ag) {
                if ($key == $totalLevel - 1) { //last item is close sale agency
                    $commissionInfoBasedAgencyNetwork = ['percent'=>AgencyCloseSaleModel::whereNotNull('id')->first()->agency_close_sale_amount_get,'amount' => $this->closeSaleCommissionAmount($sale_id), 'condition' => AgencyCloseSaleModel::whereNotNull('id')->first()->agency_close_sale_condition, 'commission' => AgencyCloseSaleModel::whereNotNull('id')->first()->agency_close_sale_percentage];
                } else {
                    $commissionInfoBasedAgencyNetwork = $this->commissionInfoBasedAgencyNetwork($this->getTotalCommissionBySaleID($sale_id), $ag, $networkLevel[$key]);
                }
                $data = [
                    'agency_id'     => $ag->id,
                    'level'         => ($key == $totalLevel - 1) ? "Close Sale" : ($level - 1),
                    'amount'        => $commissionInfoBasedAgencyNetwork['amount'],
                    'condition'     => $commissionInfoBasedAgencyNetwork['condition'],
                    'commission'    => $commissionInfoBasedAgencyNetwork['commission'],
                    'percent'       => $commissionInfoBasedAgencyNetwork['percent'],
                ];
                array_push($arrayRelatedAgencyList, $data);
                $level--;
            }
        } else {
            foreach ($arrayAgency as $key => $ag) {

                if ($key == $totalLevel - 1) {
                    $commissionInfoBasedAgencyGroup = ['amount' => $this->closeSaleCommissionAmount($sale_id), 'condition' => AgencyCloseSaleModel::whereNotNull('id')->first()->agency_close_sale_condition, 'commission' => AgencyCloseSaleModel::whereNotNull('id')->first()->agency_close_sale_percentage];
                } else {
                    $commissionInfoBasedAgencyGroup = $this->commissionInfoBasedAgencyGroup($this->getTotalCommissionBySaleID($sale_id), $ag);
                }
                $data = [
                    'agency_id'     => $ag->id,
                    'level'         => ($key == $totalLevel - 1) ? "Close Sale" : ($level - 1),
                    'amount'        => $commissionInfoBasedAgencyGroup['amount'],
                    'condition'     => $commissionInfoBasedAgencyGroup['condition'],
                    'commission'    => $commissionInfoBasedAgencyGroup['commission'],
                    'percent'       => $commissionInfoBasedAgencyGroup['percent']
                ];
                array_push($arrayRelatedAgencyList, $data);
                $level--;
            }
        }
        return $arrayRelatedAgencyList;
    }
    public function getTotalCommissionForCloseSaleBySaleID($sale_id)
    {
        $items = DB::table('nso007_item_stock as item')
            ->join('nso007_sale_detail_stock as detail', 'detail.saledetail_itemid', 'item.id')
            ->select('item.*')
            ->where('detail.saledetail_saleid', $sale_id)
            ->get();
        $TotalCommission = 0;
        foreach ($items as $item) {
            if ($item->commission_amount > 0) {
                $TotalCommission += $item->commission_amount;
            } else {
                $commission = $item->item_price * $item->commission / 100;
                $TotalCommission += $commission;
            }
        }
        return floatval($TotalCommission);
    }
    public function getTotalCloseCommissionForCloseSaleBySaleID($sale_id)
    {
        $totalDefaultCommission = $this->getTotalCommissionForCloseSaleBySaleID($sale_id);
        $closeSale  = AgencyCloseSaleModel::whereNotNull('id')->first();
        $commission = $totalDefaultCommission * $closeSale->agency_close_sale_amount_get / 100;
        return $commission;
    }
    public function closeSaleCommissionAmount($sale_id)
    {
        $loan               = LoanModel::where('loan_saleid', $sale_id)->first();
        $closeSale          = AgencyCloseSaleModel::wherenotnull('id')->first();
        $totalCommission    = $this->getTotalCommissionForCloseSaleBySaleID($sale_id);
        $closecommission    = $totalCommission * $closeSale->agency_close_sale_amount_get / 100;
        return $closecommission;
    }
    public function updateAgencyCommissionDirectSale($sale_id)
    {
        $closeSaleCommission    = $this->closeSaleCommissionAmount($sale_id);
        $discountCommission     = LoanModel::where('loan_saleid', $sale_id)->first()->discount_commis;
        $decrementamount        = $closeSaleCommission + $discountCommission;
        CommissionItemModel::where([['com_list_sale_id', $sale_id], ['agency_level', '!=', 'Close Sale'], ['agency_id', LoanModel::where('loan_saleid', $sale_id)->first()->loan_agentid]])->decrement('amount', $decrementamount);
    }
    public function updateCloseSaleCommission($sale_id,$amount){
        CommissionModel::where([['sale_id',$sale_id],['agency_status','Close Sale']])->update([
            'commission'=> $amount
        ]);
        CommissionItemModel::where([['com_list_sale_id',$sale_id],['agency_level','Close Sale']])->update([
            'amount'    => $amount,
            'amount'    => $amount
        ]);
    }
    public function add_agent_commission(Request $request)
    {
        $sale_id                = $request->sale_id;
        $sale_id                = Crypt::decrypt($sale_id);
        $Loan                   = LoanModel::where('loan_saleid', $sale_id)->first();
        $SaleAgency             = $Loan->loan_agentid;
        $CloseAgency            = User::find($Loan->loan_close_saleid);
        $AgencyLevel            = $this->getAgencyLevel($SaleAgency);
        array_push($AgencyLevel, $CloseAgency);
        $checkAccountFunds      = $this->validateAccountFunds($AgencyLevel);
        if ($checkAccountFunds['status'] == false) {
            return redirect()->back()->with('danger', $checkAccountFunds['error']);
        }
        $this->addMainAgencyCommission($sale_id);
        $this->addCommissionList($sale_id);
        $this->updateCloseSaleCommission($sale_id,$request->commission_close_sale);
        $this->updateAgencyCommissionDirectSale($sale_id);
        \LogActivity::addToLog('Add Commission Requested Successfully !');
        return back()->with('success', 'Commission Requested Successfully');
    }
    public function addCommissionReportBySaleID($sale_id)
    {
        $commissionList = CommissionItemModel::where('com_list_sale_id', $sale_id)->get();
        foreach ($commissionList as $key => $list) {
            CommissionItemReportModel::insert([
                'commission_item_id'    => $list->id,
                'amount'                => $list->amount
            ]);
        }
    }
    //   approve commission
    public function approve_commission(Request $request)
    {
        $company_agency = DB::table('users')->where('company_agent', 1)->first();
        $sale_id        = $request->sale_id;
        $sale_id        = Crypt::decrypt($sale_id);

        DB::table('nso007_sale_stock')->where('id', $sale_id)->update([
            'commission_request' => 3
        ]);
        DB::table('nso007_loan')->where('loan_saleid', $sale_id)->update([
            'loan_commission_status' => 1
        ]);
        CommissionItemModel::where('com_list_sale_id',$sale_id)->update(['status'=>1]);
        DB::table('nso007_request_commission')->where('sale_id', $sale_id)->delete();
        $this->updateAgencyCommissionBySaleID($sale_id);
        $this->updateAdminCommissionBySaleID($sale_id);
        return back()->with('success', 'Requested Commission was successfully approved');
        //here to update//
    }

    public function increment_admin_commission_receive($id, $commission)
    {

        CommissionAdminModel::where('sale_id', $id)->increment('commission_received', (float) $commission);

        $commission_data = CommissionAdminModel::where('sale_id', $id)->select('id')->get();

        $commission_id = $commission_data[0]->id;
        $data = array(
            'commission_id'     => $commission_id,
            'commission_amount' => $commission
        );
        CommissionAdminReportModel::insert($data);
    }

    public function check_commission($percentage, $commission_amount, $condit, $commis)
    {
        //dd($percentage.'/'.$commission_amount.'/'.$condit.'/'.$commis);
        $result = ['status' => false, 'balance' => 0];
        $condition_commission = [];
        $condition  = explode('|', $condit);
        $commission = explode('|', $commis);
        //push array to array
        foreach ($condition as $key => $condi) {
            $array1 = [$condi, $commission[$key]];
            //dd($array1);
            array_push($condition_commission, $array1);
        }
        // dd($condition_commission);

        $smallest_array_value   = floatval($condition_commission[0][0]);

        $smallest_array         = $condition_commission[0];
        // dd($condition_commission[0][0][0]);
        $set_of_smallest_array  = [];
        array_push($set_of_smallest_array, $condition_commission[0]);
        // dd($set_of_smallest_array);
        //define an array to catch smallest set of condition
        // array_push($set_of_smallest_array,$condition_commission[0][0]);

        foreach ($condition_commission as $condi_commi) {

            if (floatval($condi_commi[0]) < $smallest_array_value) {
                $smallest_array_value = floatval($condi_commi[0]);
                $smallest_array = $condi_commi;
            }

            if (floatval($condi_commi[0]) <= $percentage) {
                array_push($set_of_smallest_array, $condi_commi);
            }
        }

        // dd($smallest_array);
        if ($percentage >= floatval($smallest_array[0])) {
            // dd(floatval($smallest_array[0]));
            $result['status'] = true;

            //check available amount
            $biggest_smallest_array_value   = $set_of_smallest_array[0][0];

            $biggest_smallest_array         = $set_of_smallest_array[0];

            // dd($biggest_smallest_array);

            foreach ($set_of_smallest_array as $small_set) {

                if ($small_set[0] >= floatval($biggest_smallest_array_value)) {
                    $biggest_smallest_array = [];
                    $biggest_smallest_array = $small_set;
                }
            }

            //check avialable commission amount
            $balance_condition  = floatval($biggest_smallest_array[0]);
            $balance_commission = floatval($biggest_smallest_array[1]);
            $max_condition      = floatval(max($condition));

            if ($balance_commission == 100) {

                $balance = $commission_amount;
            } else {

                $balance = ($commission_amount * $balance_commission) / 100;
            }
            $result['balance'] = $balance;
        }
        // dd($balance);
        return $result;
    }
    public function check_commission_payment($percentage, $commission_amount, $condit, $commis)
    {
        //dd($percentage.'/'.$commission_amount.'/'.$condit.'/'.$commis);
        $result = ['status' => false, 'balance' => 0];
        $condition_commission = [];
        $condition  = explode('|', $condit);
        $commission = explode('|', $commis);
        //push array to array
        foreach ($condition as $key => $condi) {
            $array1 = [$condi, $commission[$key]];
            //dd($array1);
            array_push($condition_commission, $array1);
        }
        // dd($condition_commission);

        $smallest_array_value   = floatval($condition_commission[0][0]);

        $smallest_array         = $condition_commission[0];
        // dd($condition_commission[0][0][0]);
        $set_of_smallest_array  = [];
        array_push($set_of_smallest_array, $condition_commission[0]);
        // dd($set_of_smallest_array);
        //define an array to catch smallest set of condition
        // array_push($set_of_smallest_array,$condition_commission[0][0]);

        foreach ($condition_commission as $condi_commi) {

            if (floatval($condi_commi[0]) < $smallest_array_value) {
                $smallest_array_value   = $condi_commi[0];
                $smallest_array         = $condi_commi;
            }

            if (floatval($condi_commi[0]) <= $percentage) {
                array_push($set_of_smallest_array, $condi_commi);
            }
        }

        // dd($smallest_array);
        if ($percentage >= floatval($smallest_array[0])) {
            // dd(floatval($smallest_array[0]));
            $result['status'] = true;

            //check available amount
            $biggest_smallest_array_value   = $set_of_smallest_array[0][0];

            $biggest_smallest_array         = $set_of_smallest_array[0];

            // dd($biggest_smallest_array);

            foreach ($set_of_smallest_array as $small_set) {

                if ($small_set[0] >= floatval($biggest_smallest_array_value)) {
                    $biggest_smallest_array_value = $small_set[0];
                    $biggest_smallest_array = [];
                    $biggest_smallest_array = $small_set;
                }
            }

            //check avialable commission amount
            $balance_condition  = floatval($biggest_smallest_array[0]);
            $balance_commission = floatval($biggest_smallest_array[1]);
            $max_condition      = floatval(max($condition));

            if ($balance_commission == 100) {
                $balance = $commission_amount;
            } else {
                $balance = ($commission_amount * $balance_commission) / 100;
            }
            $result['balance'] = $balance;
        }

        return $result;
    }
    public function check_commission_payment_company($percentage, $commission_amount, $condit, $commis)
    {
        //dd($percentage.'/'.$commission_amount.'/'.$condit.'/'.$commis);
        $result = ['status' => false, 'balance' => 0];
        $condition_commission = [];
        $condition  = explode('|', $condit);
        $commission = explode('|', $commis);
        // dd($condition,$commission);
        //push array to array
        foreach ($condition as $key => $condi) {
            $array1 = [$condi, $commission[$key]];
            //dd($array1);
            array_push($condition_commission, $array1);
        }
        // dd($condition_commission);

        $smallest_array_value   = floatval($condition_commission[0][0]);

        $smallest_array         = $condition_commission[0];
        // dd($condition_commission[0][0][0]);
        $set_of_smallest_array  = [];
        // array_push($set_of_smallest_array, $condition_commission[0]);
        // dd($set_of_smallest_array);
        //define an array to catch smallest set of condition
        // array_push($set_of_smallest_array,$condition_commission[0][0]);

        foreach ($condition_commission as $condi_commi) {

            if (floatval($condi_commi[0]) <= $smallest_array_value) {
                $smallest_array_value   = $condi_commi[0];
                $smallest_array         = $condi_commi;
            }

            if (floatval($condi_commi[0]) <= floatval($percentage)) {
                array_push($set_of_smallest_array, $condi_commi);
            }
        }

        // dd($percentage,$condition_commission,$set_of_smallest_array);
        if ($percentage >= floatval($smallest_array[0])) {
            // dd(floatval($smallest_array[0]));
            $result['status'] = true;

            //check available amount
            $biggest_smallest_array_value   = $set_of_smallest_array[0][0];

            $biggest_smallest_array         = $set_of_smallest_array[0];

            // dd($biggest_smallest_array);

            foreach ($set_of_smallest_array as $small_set) {
                if ($small_set[0] >= floatval($biggest_smallest_array_value)) {
                    $biggest_smallest_array_value = $small_set[0];
                    $biggest_smallest_array = [];
                    $biggest_smallest_array = $small_set;
                }
            }

            //check avialable commission amount
            $balance_condition  = floatval($biggest_smallest_array[0]);
            $balance_commission = floatval($biggest_smallest_array[1]);
            $max_condition      = floatval(max($condition));
            if ($balance_commission == 100) {
                $balance = $commission_amount;
            } else {
                $balance = ($commission_amount * $balance_commission) / 100;
            }
            $result['balance'] = $balance;
        }

        return $result;
    }
    public function reject_commission(Request $request)
    {
        $sale_id    = $request->sale_id;
        $sale_id    = Crypt::decrypt($sale_id);
        DB::table('nso007_sale_stock')->where('id', $sale_id)->update([
            'commission_request' => 1
        ]);
        CommissionAdminModel::where('sale_id', $sale_id)->delete();
        CommissionModel::where('sale_id', $sale_id)->delete();
        CommissionItemModel::where('com_list_sale_id', $sale_id)->delete();
        return back()->with('success', 'Requested Commission was rejected');
    }
    public function get_downpayment($id)
    {
        $decrypt_id = Crypt::decrypt($id);
        // dd($decrypt_id);
        $loan = LoanModel::find($decrypt_id);
        // dd($loan);
        echo \json_encode($loan);
    }
    public function approve_deposite($id)
    {
        $sale_id        = Crypt::decrypt($id);
        $pre_deposite   = AppEditPOS::where('app_sale_id', $sale_id)->first();
        $status_num = 1;
        $deposit = DepositModel::where('deposit_saleid', $sale_id)->first();
        if ($deposit) {
            $status_num = $deposit->status_num + 1;
        }
        $sale_stock = SaleStockModel::find($sale_id);
        $item_cost = $sale_stock->sale_amount;
        $payment_percentage = floatval($pre_deposite->app_payamount) * 100 / floatval($item_cost);
        DB::table('nso007_loan')->where('loan_saleid', $sale_id)
            ->update([
                'loan_downpayment' => DB::raw("loan_downpayment-$pre_deposite->app_payamount"),
                'loan_percentpay' => DB::raw("loan_percentpay+$payment_percentage")
            ]);
        // ->decrement('loan_downpayment',$pre_deposite->app_payamount)->increment('loan_percentpay',floatval($payment_percentage));
        DB::table('nso007_sale_stock')->where('id', $sale_id)->update([
            'status_pos_approve_type' => 0
        ]);
        DB::table('nso007_deposit')->insert(
            [
                'deposit_saleid'    => $sale_id,
                'deposit_date'      => $pre_deposite->app_date,
                'deposit_amount'    => $pre_deposite->app_payamount,
                'status_num'        => $status_num,
                'date_contract'     => $pre_deposite->app_date_contract
            ]
        );
        DB::table('nso007_approve_pos_edit')->where('app_sale_id', $sale_id)->delete();
        $commission = CommissionModel::where('sale_id', $sale_id)->first();
        $item_stock = ItemModel::where('id', $commission->item_id)->first();
        $commission_list = CommissionItemModel::where('commission_id', $commission->id)->get();

        if ($commission_list != null) {
            foreach ($commission_list as $val) {
                $commission_pay = $this->calculate_payment_commission($commission->commission, $item_cost, $sale_stock->sale_receive, $pre_deposite->app_payamount, $val->commission, $item_stock->condition);
                $agency_acc = $this->find_agency_account_number($val->agency_id);

                if (!empty($commission_pay)) {
                    $this->put_commission_to_report($item_stock->id, $commission_pay);
                    $this->increment_funds_balance($val->agency_id, $commission_pay, $item_stock->item_name, $agency_acc);
                    $this->increment_commission_receive($val->agency_id, $commission_pay, $val->id);
                }
            }
        }

        \LogActivity::addToLog('Approve Deposit on Downpayment successfully!');
        return back()->with('success', 'Deposite was successfully approved');
    }
    public function reject_deposite($id)
    {
        $sale_id        = Crypt::decrypt($id);
        DB::table('nso007_sale_stock')->where('id', $sale_id)->update([
            'status_pos_approve_type' => 0
        ]);

        DB::table('nso007_approve_pos_edit')->where('app_sale_id', $sale_id)->delete();
        \LogActivity::addToLog('Reject Deposit on Downpayment successfully!');
        return back()->with('success', 'Deposite was successfully rejected');
    }
    public function get_commission()
    {
        $commissions = AgencyLevelModel::all();
        $div = '';
        foreach ($commissions as $key => $commis) {
            $div .= '<label class="control-label col-md-5">' . $commis->agency_level . '</label>
                     <div class="col-md-7">
                        <input type="text" name="commission_level[]" class="form-control" value="' . $commis->commission_level . '" readonly/>
                     </div>
                    ';
        }
        return $div;
    }
    public function get_commission_condition(Request $request)
    {
        $commission = CommissionConditionModel::where('id', $request->id)->select('id', 'condition', 'commission')->get();

        return json_encode($commission);
    }
    public function get_client_payment_process($id)
    {
        $clientbooking = DB::select("
            SELECT 
                loanprocess_no,
                loanprocess_datepayment,
                loanprocess_paid,
                nso007_loan_process.id,
                nso007_loan.id as loanid,
                nso007_loan.loan_status,
                loanprocess_paid,
                loanprocess_approve,
                nso007_loan_process.updated_at,
                loanprocess_interest AS loanprocess_interest,
                loanprocess_principle AS loanprocess_principle,
                loanprocess_accprinciple AS loanprocess_accprinciple,
                loanprocess_amountpaid AS loanprocess_amountpaid,
                loanprocess_amountcredit AS loanprocess_amountcredit,
                loanprocess_accinterest AS loanprocess_accinterest,
                loanprocess_owe AS loanprocess_owe,
                loanprocess_owepay AS loanprocess_owepay,
                loanprocess_principleos AS loanprocess_principleos,
                nso007_loan.loan_commission_status AS commission_paid
            FROM nso007_loan_process
            INNER JOIN nso007_loan ON nso007_loan_process.loanprocess_loanid = nso007_loan.id
            WHERE nso007_loan.id = $id AND nso007_loan_process.loanprocess_status != 0
            ORDER BY nso007_loan_process.id ASC
        ");
        return Datatables::of($clientbooking)
            ->addIndexColumn()
            ->setRowId('id')
            ->editColumn('loanprocess_datepayment', function ($clientbooking) {
                return date('d-m-Y', strtotime($clientbooking->loanprocess_datepayment));
            })
            ->addColumn('status', function ($clientbooking) {
                $btnStatus          = '';
                $btnPay             = '';
                $btnInvoice         = '';
                $btnDelete          = '';
                $NextLoanProcess    = LoanProcessModel::where([['loanprocess_loanid', $clientbooking->loanid], ['loanprocess_no', $clientbooking->loanprocess_no + 1]])->first();
                // if (!empty($NextLoanProcess) && $NextLoanProcess->loanprocess_paid == NULL && $clientbooking->loanprocess_paid != null && $clientbooking->loanprocess_approve != 1) {
                //     $btnDelete = '<span class="btn btn-xs btn-outline-danger" title="Delete Payment" data-id="' . Crypt::encrypt($clientbooking->id) . '"><i class="fa fa-trash"></i></span>';
                // }

                $PreviousLoanProcess = LoanProcessModel::where([['loanprocess_loanid', $clientbooking->loanid], ['loanprocess_no', $clientbooking->loanprocess_no - 1]])->first();
                if (!empty($PreviousLoanProcess)) { //has previous loanprocess
                    if ($PreviousLoanProcess->loanprocess_paid == 1) { //and it is paid
                        if ($clientbooking->loanprocess_paid == null || $clientbooking->loanprocess_paid == 2) { //current loan process unpaid or partially paid

                            /*$btnPay = '<a data-toggle="modal" href="#form-pay" onclick="pay(this)" class="btn btn-xs btn-outline-primary"  title="Pay" ><i class="fa fa-check-circle"></i></a> Pending';*/
                        }
                        if ($clientbooking->loanprocess_paid != null) { //loan process completely paid or partially paid
                            $Invoices = LoanPaidModel::where('loanpaid_processid', $clientbooking->id)->get();
                            foreach ($Invoices as $key => $invoice) {
                                if($invoice->paid_type == 0 ||  ($invoice->paid_type == 1 && $invoice->parent_id == $invoice->id)){
                                    $btnInvoice .= '<a target="_blank" href="' . route('get_client_purchase_prince_invooice', $invoice->id) . '" type="button" class="btn btn-xs btn-outline-success"  title="Print Invoice" ><i class="fa fa-print"></i></a> Paid: ' . date('d-m-Y', strtotime($invoice->loanpaid_payment));
                                }
                                /*$btnInvoice .= '<a target="_blank" href="' . route('get_client_purchase_prince_invooice', $invoice->id) . '" type="button" class="btn btn-xs btn-outline-success"  title="Print Invoice" ><i class="fa fa-print"></i></a> Paid: ' . date('d-m-Y', strtotime($invoice->loanpaid_payment));*/
                            }
                        }
                    } else { //previous loanprocess unpaid or partially paid
                        /*$btnPay = '<button data-toggle="modal" disabled href="#form-pay" class="btn btn-xs btn-outline-primary btn-disable" ><i class="fa fa-check-circle"></i></button> Pending';*/
                        /*$btnPay = '<a data-toggle="modal" href="#form-pay" onclick="pay(this)" class="btn btn-xs btn-outline-primary"  title="Pay" ><i class="fa fa-check-circle"></i></a> Pending';*/
                    }
                } else { //no previous data
                    if ($clientbooking->loanprocess_paid == null || $clientbooking->loanprocess_paid == 2) {
                        /*$btnPay = '<a data-toggle="modal" href="#form-pay" onclick="pay(this)" class="btn btn-xs btn-outline-primary"  title="Pay" ><i class="fa fa-check-circle"></i></a> Pending';*/
                        /*$btnPay = '<a data-toggle="modal" href="#form-pay" onclick="pay(this)" class="btn btn-xs btn-outline-primary"  title="Pay" ><i class="fa fa-check-circle"></i></a> Pending';*/
                    }
                    if (!empty($clientbooking->loanprocess_paid)) {
                        $Invoices = LoanPaidModel::where('loanpaid_processid', $clientbooking->id)->get();
                        foreach ($Invoices as $key => $invoice ) {
                            if($invoice->paid_type == 0 || ($invoice->paid_type == 1 && $invoice->parent_id == $invoice->id)){
                                $btnInvoice .= '<a target="_blank" href="' . route('get_client_purchase_prince_invooice', $invoice->id) . '" type="button" class="btn btn-xs btn-outline-success"  title="Print Invoice" ><i class="fa fa-print"></i></a> Paid: ' . date('d-m-Y', strtotime($invoice->loanpaid_payment));
                            }
                            /*$btnInvoice .= '<a target="_blank" href="' . route('get_client_purchase_prince_invooice', $invoice->id) . '" type="button" class="btn btn-xs btn-outline-success"  title="Print Invoice" ><i class="fa fa-print"></i></a> Paid: ' . date('d-m-Y', strtotime($invoice->loanpaid_payment));*/
                        }
                    }
                }

                $btnInvoice = '';
                $Invoices = LoanPaidModel::where('loanpaid_processid', $clientbooking->id)->get();
                foreach ($Invoices as $key => $invoice ) {
                    if($invoice->paid_type == 0 || ($invoice->paid_type == 1 && $invoice->parent_id == $invoice->id)){
                        $btnInvoice .= '<a target="_blank" href="' . route('get_client_purchase_prince_invooice', $invoice->id) . '" type="button" class="btn btn-xs btn-outline-success"  title="Print Invoice" ><i class="fa fa-print"></i></a> Paid: ' . date('d-m-Y', strtotime($invoice->loanpaid_payment));
                    }
                    /*$btnInvoice .= '<a target="_blank" href="' . route('get_client_purchase_prince_invooice', $invoice->id) . '" type="button" class="btn btn-xs btn-outline-success"  title="Print Invoice" ><i class="fa fa-print"></i></a> Paid: ' . date('d-m-Y', strtotime($invoice->loanpaid_payment));*/
                }

                $loanprocess = LoanProcessModel::where('id',$clientbooking->id)->first();
                if($loanprocess->loanprocess_owepay>0)
                    $btnPay = '<a data-toggle="modal" href="#form-pay" onclick="pay(this)" class="btn btn-xs btn-outline-primary"  title="Pay" ><i class="fa fa-check-circle"></i></a> Pending';

                if (!Gate::check('Client-Payment-Pay'))
                    $btnPay = "";
                if (!Gate::check('Client-Payment-Invoice-Print'))
                    $btnInvoice = "";

                return $btnStatus = $btnDelete . $btnInvoice . $btnPay.$this->find_loanprocess_paid_next($clientbooking->id);
            })
            ->addColumn('loanprocess_principle', function ($clientbooking) {
                return '$ ' . number_format($clientbooking->loanprocess_principle, 2);
            })
            ->addColumn('loanprocess_owe', function ($clientbooking) {
                return '$ ' . number_format($clientbooking->loanprocess_owe, 2);
            })
            ->editColumn('loanprocess_owepay', function ($clientbooking) {
                return '$ ' . number_format($clientbooking->loanprocess_owepay, 2);
            })
            ->addColumn('loanprocess_principleos', function ($clientbooking) {
                return '$ ' . number_format($clientbooking->loanprocess_principleos, 2);
            })
            ->addColumn('loanprocess_interest', function ($clientbooking) {
                return '$ ' . number_format($clientbooking->loanprocess_interest, 2);
            })
            ->editColumn('penalty', function ($clientbooking) {
                return "$ " . number_format($this->penaltyofPayment($clientbooking->id), 4);
            })
            ->rawColumns(['status' => 'status'])
            ->make(true);
    }

    public function find_loanprocess_paid_next($loanprocess_id){

        $find = LoanPaidModel::where("loanpaid_processid",$loanprocess_id)->orderBy("id","ASC")->get();
        if(empty(@$find)) return "";

        $parent_id = "";$loanprocess_parent_id = "";
        foreach($find as $key=>$value){
            if($value->parent_id){
                $parent_id = $value->parent_id;
                $loanprocess_parent_id = LoanPaidModel::find($parent_id)->loanpaid_processid;
                break;
            }
        }

        $first_amount = 0;$total_paid = 0;$paid_next_amount = 0;
        $paid_next = DB::table("nso007_loan_paid_next")->where("parent_loanpaid_id",$parent_id)->orderBy("id","ASC")->get();
        if(!empty($paid_next)&&count($paid_next)>0){
            foreach($paid_next as $key=>$value){
                if($key==0) $first_amount = $value->total_amount_paid;
                $total_paid += $value->total_amount_paid;
            }
            $paid_next_amount = $total_paid - $first_amount;
        }
        if($paid_next_amount > 0 && $loanprocess_parent_id == $loanprocess_id)
            return " ( បង់ប្រាក់លើស : $".$paid_next_amount." )";
        else return "";

    }

    public function client_purchase_list_print_invoice($id)
    {
        $print_step = DB::table('nso007_loan_paid')
            ->join('nso007_loan_process', 'nso007_loan_paid.loanpaid_processid', 'nso007_loan_process.id')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
            ->join('nso007_category_stock', 'nso007_category_stock.id', 'nso007_item_stock.item_categoryid')
            ->join('nso007_sub_category_stock', 'nso007_sub_category_stock.id', 'nso007_item_stock.item_sub_categoryid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('nso007_client', 'nso007_client.id', 'nso007_loan.loan_clientid')
            ->select(
                'nso007_loan_process.loanprocess_no',
                'nso007_loan.loan_amountos',
                'nso007_loan.id as loan_id',
                'nso007_loan_process.loanprocess_datepayment',
                'nso007_loan_process.loanprocess_principle',
                'nso007_loan_process.loanprocess_interest',
                'nso007_project_stock.project_name',
                'nso007_sale_stock.id as sale_id',
                'nso007_sale_stock.sale_date',
                'nso007_sale_stock.sale_amount',
                'nso007_sale_stock.sale_discount',
                'nso007_item_stock.item_name',
                'nso007_item_stock.item_street',
                'nso007_client.client_fullname',
                'nso007_client.client_phone1',
                'nso007_client.client_phone2',
                'nso007_loan_paid.loanpaid_total',
                'nso007_loan_paid.loanpaid_invoice',
                'nso007_loan_paid.loanpaid_remark',
                'nso007_loan_paid.loanpaid_payment',
                'nso007_loan_paid.loanpaid_penalty',
                'nso007_loan_paid.loanpaid_createdate as paid_date',
                'nso007_loan_paid.from_bank',
                'nso007_loan_paid.from_account_bank',
                'nso007_loan_paid.to_bank',
                'nso007_loan_paid.to_account_bank',
                'nso007_loan_paid.bank_cheque',
                'nso007_loan_paid.cheque_number',
                'nso007_loan_paid.loandpaid_bankacc',
                'nso007_loan_paid.parent_id',
                'nso007_loan_paid.paid_type',
                'loanpaid_penalty_option',
                'nso007_loan_process.loanprocess_owe',
                "nso007_loan_process.loanprocess_principleos",
                'nso007_sub_category_stock.sub_category_name',
                'nso007_sub_category_stock.sub_category_code',
                'nso007_item_stock.item_house'
            )
            ->where('nso007_loan_paid.id', $id)
            ->first();
        $first_date = DB::table('nso007_loan_process')
            ->select(
                'nso007_loan_process.loanprocess_no',
                'nso007_loan_process.loanprocess_datepayment'
            )
            ->where('nso007_loan_process.loanprocess_loanid', $print_step->loan_id)
            ->first();
        $last_date = DB::table('nso007_loan_process')
            ->select(
                'nso007_loan_process.loanprocess_no',
                DB::raw("Max(nso007_loan_process.id) as last_id"),
                DB::raw("Max(nso007_loan_process.loanprocess_datepayment) as loanprocess_datepayment")
            )
            ->where('nso007_loan_process.loanprocess_loanid', $print_step->loan_id)
            ->first();

        $sale_id=$print_step->sale_id;
        $sale_payment_policies = DB::table('nso007_sale_policy_payment')->where("sale_id",$sale_id)->first();
        $payment_policy='';
        $payment_policy_detail='';
        if(!empty($sale_payment_policies->payment_policy_id))
        {
            $payment_policy.=DB::table("nso007_payment_policy")->where("id",$sale_payment_policies->payment_policy_id)->first()->name;

        }
        if(!empty($sale_payment_policies->policy_detail_id))
        {

            $payment_policy_detail.=DB::table("nso007_payment_policy_detail")->where("id",$sale_payment_policies->policy_detail_id)->first()->name;

        }

        $client_relationship_get = DB::table('nso007_relationship_status')->where("sale_id",$sale_id)->get();
        $client_relationship_name = "";
        foreach($client_relationship_get as $key=>$value)
        {
            $client_relationship_data = ClientRelationshipModel::find(optional($value)->clientship_id);
            $client_relationship_name .=" / ".$client_relationship_data->clientship_fullname;
        }

        $branch = BranchModel::where('id', \Branch::branch_id())->first();
        
        // echo json_encode($branch);exit();
        return view('administrator.client-payment.payment.invoice-payment-step', compact('last_date','first_date','payment_policy_detail','payment_policy','print_step', 'branch','client_relationship_name'));
    }

    /**
     *  Ajax get payment process
     */
    public function ajax_get_payment_process($id)
    {
        $data['loan_process'] = LoanProcessModel::select(
            'id',
            'loanprocess_loanid',
            'loanprocess_interestpay',
            'loanprocess_principlepay',
            'loanprocess_owepay',
            'loanprocess_datepayment',
            'penalty_paid'
        )->find($id);
        $data['penalty'] = $this->penaltyofPayment($id);
        echo json_encode($data);
    }
    /**
     *  Submit Payment
     */
    public function diffdays($start, $end)
    {
        $now = strtotime($end); // or your date as well
        $your_date = strtotime($start);
        $datediff = $now - $your_date;
        return round($datediff / (60 * 60 * 24));
    }
    public function penaltyofPayment($loan_processid)
    {
        $loan_processid = (int) $loan_processid;
        $penalty = LoanProcessModel::select('loanprocess_owepay AS loan_pay', 'loanprocess_datepayment','paid_only_penalty','date_paid_only_penalty', 'loanprocess_datepaid', 'penalty_paid', DB::raw('TIMESTAMPDIFF(DAY,loanprocess_datepayment,"' . date('Y-m-d') . '") AS day_late'))

            ->where('id', $loan_processid)
            ->whereDate('loanprocess_datepayment', "<", Carbon::now())
            ->where(function ($query) {
                $query->whereNull('loanprocess_paid');
                $query->orwhere('loanprocess_paid', 2);
            })

            ->first();
        $total_day      = 0;
        $penaltyAmount  = 0;
        if (!empty($penalty)) {
            if (!empty($penalty->loanprocess_datepaid)) {
                if($penalty->paid_only_penalty==1){
                    $total_day     = $this->diffdays($penalty->date_paid_only_penalty, date('Y-m-d'));
                }
                else{
                    $total_day     = $this->diffdays($penalty->loanprocess_datepaid, date('Y-m-d'));
                }
            } else {
                $total_day = $penalty->day_late - 5;
            }
            if ($total_day > 0) {
                $penaltyAmount = ($penalty->penalty_paid) + ($penalty->loan_pay * 0.05 * $total_day) / 30;
            } else {
                $penaltyAmount = $penalty->penalty_paid;
            }
        } else {
            $penaltyAmount = LoanProcessModel::find($loan_processid)->penalty_paid;
        }
        return (float) $penaltyAmount;
    }
    public function payment(Request $request)
    {
        $pid        = $request->pid;
        $principle  = $request->principle;
        $interest   = $request->interest;
        $payment    = $request->payment;
        $pay        = $request->pay;
        $condition  = $request->condition;
        $penalty    = $request->penalty;
        $reference  = $request->reference;
        $date       = $request->date;
        $remark     = $request->remark;
        $loan_id    = $request->loan_id;
        $bank_list  = $request->bank_list;
        $extra_amount    = $request->extra_amount;
        $option     = $request->penalty_condition;
        $do_date    = date('Y-m-d H:i:s');
        $bank_from_id = $request->bank_from_id; 
        $bank_from_account = $request->bank_from_account;
        $bank_to_id = $request->bank_to_id;
        $bank_to_account = $request->bank_to_account;
        $bank_of_cheque = $request->bank_of_cheque;
        $cheque_no = $request->cheque_no;
        // dd($option);
        $data = array(
            'id'            => $pid,
            'payment'       => $payment,
            'pay'           => $pay,
            'penalty'       => $penalty,
            'datepayment'   => $date,
            'by'            => Auth::user()->id,
            'reference'     => $reference,
            'principle'     => $principle,
            'interest'      => $interest,
            'remark'        => $remark,
            'loan_id'       => $loan_id,
            'do_date'       => $do_date,
            'bank_list'     => $bank_list,
            'extra_amount'  => $extra_amount,
            'fee_charge'    => 0,
            'option'        => $option,
            'bank_from_id'  => $bank_from_id,
            'bank_from_account' => $bank_from_account,
            'bank_to_id'    => $bank_to_id,
            'bank_to_account' => $bank_to_account,
            'bank_of_cheque' => $bank_of_cheque,
            'cheque_no'     => $cheque_no
        );
        $response = ['success' => true, 'msg' => 'Successfully Paid'];
        try {
            if ($condition == 1) {
                $this->none_payment_condition($data);
            } elseif ($condition == 2) {
                $this->next_payment_condition($data);
            }
            $status_check = $this->check_set_payment_complete_status($loan_id);
            if($status_check == 0){
                DB::table('nso007_loan')->where("id",$loan_id)->update(['loan_enddate'=>date("Y-m-d",strtotime($date))]);
            }
        } catch (\Exception $e) {
            $msg = $e->getMessage();
            $response = ['success' => false, 'msg' => $msg];
        }
        return response()->json($response);
    }
    public function check_set_payment_complete_status($loan_id)
    {
        $check_status = DB::table('nso007_loan_process')->select(DB::raw('SUM(loanprocess_owepay) as total_owepay'))->where("loanprocess_loanid",$loan_id)->first();
        if($check_status->total_owepay<=0){
            $check_loan = DB::table('nso007_loan')->select("loan_enddate")->where("id",$loan_id)->first();
            if(empty($check_loan->loan_enddate) || $check_loan->loan_enddate==null){
                return 0;
            }
            else return 1;
        }
        else return 0;
    }
    /**
     *  Condition None Payment
     */
    public static function get_check_loand_paid_invoice($branch_id)
    {
        $lastinvoice = DB::table('nso007_loan_paid as paid')
            ->join('nso007_loan_process as process', 'process.id', 'paid.loanpaid_processid')
            ->join('nso007_loan as loan', 'loan.id', 'process.loanprocess_loanid')
            ->where([['loan.branch_id', $branch_id],['paid.loanpaid_invoice','LIKE',date('Y')."%"]])->orderBy('paid.id','DESC')->first();
        if(empty($lastinvoice)){
            $invoice_id = 1;
        }else{
            $invoice_id = explode('-',$lastinvoice->loanpaid_invoice);
            $invoice_id = (int)$invoice_id[1]+1;
        }
        $invoice = date('Y').'-'.str_pad($invoice_id, 6, "0", STR_PAD_LEFT);
        return $invoice;
    }
    public function none_payment_condition($data)
    {

        $branch_id              = \Branch::branch_id();
        extract($data);
        
        $insert_invoice = $this::get_check_loand_paid_invoice(DB::table("nso007_loan")->where("id",$loan_id)->first()->branch_id);
        
        $loan_process       = LoanProcessModel::find($id);
        $generatedPenalty   = $penalty;

        if ($payment < $loan_process->loanprocess_owepay) {
            if ($payment >= $loan_process->loanprocess_interestpay) {
                if($generatedPenalty > 0 && $option == 1){
                    LoanProcessModel::where('id', $loan_process->id)->update([
                        'loanprocess_interestpay'   => 0,
                        'loanprocess_principlepay'  => DB::raw("loanprocess_principlepay-$payment+$loan_process->loanprocess_interestpay"),
                        'loanprocess_owepay'        => DB::raw("loanprocess_owepay-$payment"),
                        'loanprocess_datepaid'      => date('Y-m-d'),
                        'loanprocess_by'            => Auth::user()->id,
                        'loanprocess_activity'      => 2,
                        'loanprocess_paid'          => 2,
                        'paid_only_penalty'         => 1,
                        'date_paid_only_penalty'    => date('Y-m-d', strtotime($datepayment))

                    ]);
                }
                else{
                    LoanProcessModel::where('id', $loan_process->id)->update([
                        'loanprocess_interestpay'   => 0,
                        'loanprocess_principlepay'  => DB::raw("loanprocess_principlepay-$payment+$loan_process->loanprocess_interestpay"),
                        'loanprocess_owepay'        => DB::raw("loanprocess_owepay-$payment"),
                        'loanprocess_datepaid'      => date('Y-m-d'),
                        'loanprocess_by'            => Auth::user()->id,
                        'loanprocess_activity'      => 2,
                        'loanprocess_paid'          => 2
                    ]);
                }

                LoanPaidModel::insert([
                    'loanpaid_processid'    => $loan_process->id,
                    'loanpaid_payment'      => date('Y-m-d', strtotime($datepayment)),
                    'loanpaid_interest'     => $loan_process->loanprocess_interestpay,
                    'loanpaid_principle'    => $payment - $loan_process->loanprocess_interestpay,
                    'loanpaid_penalty'      => $generatedPenalty,
                    'loanpaid_total'        => $payment,
                    'loanpaid_by'           => Auth::user()->id,
                    'loanpaid_reference'    => $reference,
                    'loanpaid_invoice'      => $insert_invoice,
                    'loanpaid_type'         => 1,
                    'loanpaid_remark'       => $remark,
                    'loanpaid_createdate'   => date('Y-m-d H:i:s'),
                    'loandpaid_bankacc'     => $bank_list,
                    'loanpaid_penalty_option'   => $option,
                    'payment_fee'           => $fee_charge,
                    'loanpaid_des'          => \Globals::paymentDesByLoanProcessID("", $loan_process->id),
                    'from_bank'             => $bank_from_id,
                    'from_account_bank'     => $bank_from_account,
                    'to_bank'               => $bank_to_id,
                    'to_account_bank'       => $bank_to_account,
                    'bank_cheque'           => $bank_of_cheque,
                    'cheque_number'         => $cheque_no
                ]);

            } else {
                if($payment > 0 || ($payment == 0 && $generatedPenalty > 0 && $option == 1))
                {
                    if($generatedPenalty > 0 && $option == 1){
                        LoanProcessModel::where('id', $loan_process->id)->update([
                            'loanprocess_interestpay'   => DB::raw("loanprocess_interestpay-$payment"),
                            'loanprocess_owepay'        => DB::raw("loanprocess_owepay-$payment"),
                            'loanprocess_datepaid'      => date('Y-m-d'),
                            'loanprocess_by'            => Auth::user()->id,
                            'loanprocess_activity'      => 2,
                            'loanprocess_paid'          => 2,
                            'paid_only_penalty'         => 1,
                            'date_paid_only_penalty'    => date('Y-m-d', strtotime($datepayment))
                        ]);

                    }else{
                        LoanProcessModel::where('id', $loan_process->id)->update([
                            'loanprocess_interestpay'   => DB::raw("loanprocess_interestpay-$payment"),
                            'loanprocess_owepay'        => DB::raw("loanprocess_owepay-$payment"),
                            'loanprocess_datepaid'      => date('Y-m-d'),
                            'loanprocess_by'            => Auth::user()->id,
                            'loanprocess_activity'      => 2,
                            'loanprocess_paid'          => 2
                        ]);
                    }
                    LoanPaidModel::insert([
                        'loanpaid_processid'    => $loan_process->id,
                        'loanpaid_payment'      => date('Y-m-d', strtotime($datepayment)),
                        'loanpaid_interest'     => $payment,
                        'loanpaid_principle'    => 0,
                        'loanpaid_penalty'      => $generatedPenalty,
                        'loanpaid_total'        => $payment,
                        'loanpaid_by'           => Auth::user()->id,
                        'loanpaid_reference'    => $reference,
                        'loanpaid_invoice'      => $insert_invoice,
                        'loanpaid_type'         => 1,
                        'loanpaid_remark'       => $remark,
                        'loanpaid_createdate'   => date('Y-m-d H:i:s'),
                        'loandpaid_bankacc'     => $bank_list,
                        'loanpaid_penalty_option'   => $option,
                        'payment_fee'           => $fee_charge,
                        'loanpaid_des'          => \Globals::paymentDesByLoanProcessID("", $loan_process->id),
                        'from_bank'             => $bank_from_id,
                        'from_account_bank'     => $bank_from_account,
                        'to_bank'               => $bank_to_id,
                        'to_account_bank'       => $bank_to_account,
                        'bank_cheque'           => $bank_of_cheque,
                        'cheque_number'         => $cheque_no
                    ]);
                }
            }
            if ($generatedPenalty > 0 && $option == 2) { //if it is paid less than current payment and penalty step to next, so crement current loan process
                LoanProcessModel::where('id', $loan_process->id)->update([
                    'penalty_paid' => $generatedPenalty
                ]);
            }
        } else {
            LoanProcessModel::where('id', $loan_process->id)->update([
                'loanprocess_interestpay'   => 0,
                'loanprocess_principlepay'  => 0,
                'loanprocess_owepay'        => 0,
                'loanprocess_datepaid'      => date('Y-m-d'),
                'loanprocess_by'            => Auth::user()->id,
                'loanprocess_activity'      => 1,
                'loanprocess_paid'          => 1
            ]);
            LoanPaidModel::insert([
                'loanpaid_processid'    => $loan_process->id,
                'loanpaid_payment'      => date('Y-m-d', strtotime($datepayment)),
                'loanpaid_interest'     => $loan_process->loanprocess_interestpay,
                'loanpaid_principle'    => $loan_process->loanprocess_principlepay,
                'loanpaid_penalty'      => $generatedPenalty,
                'loanpaid_total'        => $loan_process->loanprocess_owepay,
                'loanpaid_by'           => Auth::user()->id,
                'loanpaid_reference'    => $reference,
                'loanpaid_invoice'      => $insert_invoice,
                'loanpaid_type'         => 1,
                'loanpaid_des'          => \Globals::paymentDesByLoanProcessID("", $loan_process->id),
                'loanpaid_remark'       => $remark,
                'loanpaid_createdate'   => date('Y-m-d H:i:s'),
                'loandpaid_bankacc'     => $bank_list,
                'loanpaid_penalty_option'   => $option,
                'payment_fee'           => $fee_charge,
                'from_bank'             => $bank_from_id,
                'from_account_bank'     => $bank_from_account,
                'to_bank'               => $bank_to_id,
                'to_account_bank'       => $bank_to_account,
                'bank_cheque'           => $bank_of_cheque,
                'cheque_number'         => $cheque_no
            ]);
            LoanProcessModel::where('id', $loan_process->id)->update([
                'penalty_paid'  => 0
            ]);
            if ($generatedPenalty > 0 && $option == 2) { //update penalty loanprocess of next step
                LoanProcessModel::where([['loanprocess_loanid', $loan_process->loanprocess_loanid], ['loanprocess_no', $loan_process->loanprocess_no + 1]])->increment('penalty_paid', $generatedPenalty);
            }
            LoanProcessModel::where('id', $loan_process->id)->update([
                'penalty_paid'  => 0
            ]);
        }
    }

    /**
     *  Condition Next Payment
     */
    public function next_payment_condition($data)
    {
        extract($data);
        $branch_id      = \Branch::branch_id();
        $des            = $this->set_payment_description($id);
        $loan_process   = LoanProcessModel::find($id);
        $loan           = LoanModel::find($loan_id);
        $payment        = $payment;
        $index          = 0;
        $generatedPenalty = $penalty;

        if (empty($penalty) || $penalty == 0) {
            $index_count = 0;$parent_loanpaid_id = "";
            while ($payment > 0 && !empty($loan_process)) {
                if ($index != 0) {
                    $fee_charge = 0;
                }

                $insert_invoice = $this::get_check_loand_paid_invoice(DB::table("nso007_loan")->where("id",$loan_id)->first()->branch_id);

                if ($payment >= $loan_process->loanprocess_owepay) { //incase total amount is greater than amount to be paid
                    LoanProcessModel::where('id', $loan_process->id)->update([
                        'loanprocess_interestpay'   => 0,
                        'loanprocess_principlepay'  => 0,
                        'loanprocess_owepay'        => 0,
                        'loanprocess_datepaid'      => date('Y-m-d'),
                        'loanprocess_by'            => Auth::user()->id,
                        'loanprocess_activity'      => 1,
                        'loanprocess_paid'          => 1
                    ]);
                    LoanPaidModel::insert([
                        'loanpaid_processid'    => $loan_process->id,
                        'loanpaid_payment'      => date('Y-m-d', strtotime($datepayment)),
                        'loanpaid_interest'     => $loan_process->loanprocess_interestpay,
                        'loanpaid_principle'    => $loan_process->loanprocess_principlepay,
                        'loanpaid_penalty'      => $generatedPenalty,
                        'loanpaid_total'        => $loan_process->loanprocess_owepay,
                        'loanpaid_by'           => Auth::user()->id,
                        'loanpaid_reference'    => $reference,
                        'loanpaid_invoice'      => $insert_invoice,
                        'loanpaid_type'         => 1,
                        'loanpaid_remark'       => $remark,
                        'loanpaid_createdate'   => date('Y-m-d H:i:s'),
                        'loandpaid_bankacc'     => $bank_list,
                        'loanpaid_penalty_option'   => $option,
                        'payment_fee'           => $fee_charge,
                        'loanpaid_des'          => \Globals::paymentDesByLoanProcessID("", $loan_process->id),
                        'from_bank'             => $bank_from_id,
                        'from_account_bank'     => $bank_from_account,
                        'to_bank'               => $bank_to_id,
                        'to_account_bank'       => $bank_to_account,
                        'bank_cheque'           => $bank_of_cheque,
                        'cheque_number'         => $cheque_no,
                        'paid_type'             => 1,
                    ]);

                    if ($generatedPenalty > 0 && $option == 2) {
                        LoanProcessModel::where([['loanprocess_loanid', $loan_process->loanprocess_loanid], ['loanprocess_no', $loan_process->loanprocess_no + 1]])->increment('penalty_paid', $generatedPenalty);
                    }
                    LoanProcessModel::where('id', $loan_process->id)->update([
                        'penalty_paid'  => 0
                    ]);
                } else {
                    if ($payment > $loan_process->loanprocess_interestpay) {
                        // dd("OK");
                        $principalPay = $payment - $loan_process->loanprocess_interestpay;
                        LoanProcessModel::where('id', $loan_process->id)->update([
                            'loanprocess_interestpay'   => 0,
                            'loanprocess_principlepay'  => DB::raw("loanprocess_principlepay-$principalPay"),
                            'loanprocess_owepay'        => DB::raw("loanprocess_owepay-$payment"),
                            'loanprocess_datepaid'      => date('Y-m-d'),
                            'loanprocess_by'            => Auth::user()->id,
                            'loanprocess_activity'      => 2,
                            'loanprocess_paid'          => 2
                        ]);

                        LoanPaidModel::insert([
                            'loanpaid_processid'    => $loan_process->id,
                            'loanpaid_payment'      => date('Y-m-d', strtotime($datepayment)),
                            'loanpaid_interest'     => $loan_process->loanprocess_interestpay,
                            'loanpaid_principle'    => $principalPay,
                            'loanpaid_penalty'      => $generatedPenalty,
                            'loanpaid_total'        => $payment,
                            'loanpaid_by'           => Auth::user()->id,
                            'loanpaid_reference'    => $reference,
                            'loanpaid_invoice'      => $insert_invoice,
                            'loanpaid_type'         => 1,
                            'loanpaid_remark'       => $remark,
                            'loanpaid_createdate'   => date('Y-m-d H:i:s'),
                            'loandpaid_bankacc'     => $bank_list,
                            'loanpaid_penalty_option'   => $option,
                            'payment_fee'           => $fee_charge,
                            'loanpaid_des'          => \Globals::paymentDesByLoanProcessID("", $loan_process->id),
                            'from_bank'             => $bank_from_id,
                            'from_account_bank'     => $bank_from_account,
                            'to_bank'               => $bank_to_id,
                            'to_account_bank'       => $bank_to_account,
                            'bank_cheque'           => $bank_of_cheque,
                            'cheque_number'         => $cheque_no,
                            'paid_type'             => 1
                        ]);
                    } else {
                        LoanProcessModel::where('id', $loan_process->id)->update([
                            'loanprocess_interestpay'   => DB::raw("loanprocess_interestpay-$payment"),
                            'loanprocess_owepay'        => DB::raw("loanprocess_owepay-$payment"),
                            'loanprocess_datepaid'      => date('Y-m-d'),
                            'loanprocess_by'            => Auth::user()->id,
                            'loanprocess_activity'      => 2,
                            'loanprocess_paid'          => 2
                        ]);
                        LoanPaidModel::insert([
                            'loanpaid_processid'    => $loan_process->id,
                            'loanpaid_payment'      => date('Y-m-d', strtotime($datepayment)),
                            'loanpaid_interest'     => $payment,
                            'loanpaid_principle'    => 0,
                            'loanpaid_penalty'      => $generatedPenalty,
                            'loanpaid_total'        => $payment,
                            'loanpaid_by'           => Auth::user()->id,
                            'loanpaid_reference'    => $reference,
                            'loanpaid_invoice'      => $insert_invoice,
                            'loanpaid_type'         => 1,
                            'loanpaid_remark'       => $remark,
                            'loanpaid_createdate'   => date('Y-m-d H:i:s'),
                            'loandpaid_bankacc'     => $bank_list,
                            'loanpaid_penalty_option'   => $option,
                            'payment_fee'           => $fee_charge,
                            'loanpaid_des'          => \Globals::paymentDesByLoanProcessID("", $loan_process->id),
                            'from_bank'             => $bank_from_id,
                            'from_account_bank'     => $bank_from_account,
                            'to_bank'               => $bank_to_id,
                            'to_account_bank'       => $bank_to_account,
                            'bank_cheque'           => $bank_of_cheque,
                            'cheque_number'         => $cheque_no,
                            'paid_type'             => 1
                        ]);
                    }
                    if ($generatedPenalty > 0 && $option == 2) {
                        LoanProcessModel::where('id', $loan_process->id)->increment('penalty_paid', $generatedPenalty);
                    }
                }
                $index++;
                $payment -= $loan_process->loanprocess_owepay;

                $max_no = LoanProcessModel::where('loanprocess_loanid',$loan_process->loanprocess_loanid)
                    ->where('loanprocess_owepay','>',0)
                    ->max("loanprocess_no");

                $loan_process = LoanProcessModel::where([['loanprocess_loanid', $loan_process->loanprocess_loanid], ['loanprocess_no',$max_no]])->where("loanprocess_owepay",">",0)->first();

                if($index_count==0){
                    $parent_loanpaid_id = LoanPaidModel::max("id");
                }
                $child_loanpaid_id = LoanPaidModel::max("id");
                $last_paid = optional(LoanPaidModel::find($child_loanpaid_id));
                LoanPaidModel::where("id",$child_loanpaid_id)->update(['parent_id'=>$parent_loanpaid_id]);
                LoanPaidNextModel::insert(
                    [
                        'parent_loanpaid_id'=>$parent_loanpaid_id,
                        'child_loanpaid_id'=>$child_loanpaid_id,
                        'total_amount_paid'=>$last_paid->loanpaid_total,
                        'paid_date'=>date("Y-m-d",strtotime($last_paid->loanpaid_payment))
                    ]);

                $index_count++;

                /*$loan_process = LoanProcessModel::where([['loanprocess_loanid', $loan_process->loanprocess_loanid], ['loanprocess_no', $loan_process->loanprocess_no + 1]])->where("loanprocess_owepay",">",0)->first();*/

            }
            
        } else {
            echo json_encode($data["message"] = 'error');
        }
    }
    /**
     *  Update status loan completed
     */
    public function set_status_loan_payment_completed($id)
    {
        $amount_os = LoanModel::select('loan_amountos')->find($id);
        if (empty($amount_os->loan_amountos) || $amount_os->loan_amountos == 0 || $amount_os->loan_amountos < 0) {
            LoanModel::where('id', $id)->update(['loan_status' => 2, 'loan_enddate' => date('Y-m-d')]);
        }
    }
    /**
     *  Create description for payment
     */
    public function set_payment_description($id)
    {
        $data = DB::select("
            SELECT
            item.item_name AS item_des, 
            item.item_house AS item_code,
            lprocess.loanprocess_no AS lpro_des,
            project.project_name AS project_code,
            item_group.item_group_name AS group_name,
            item_zone.item_zone_name AS zone_name
            FROM nso007_loan_process AS lprocess
            LEFT JOIN nso007_loan AS loan ON lprocess.loanprocess_loanid = loan.id
            LEFT JOIN nso007_sale_detail_stock AS stock ON loan.loan_saleid = stock.saledetail_saleid
            LEFT JOIN nso007_item_stock AS item ON stock.saledetail_itemid = item.id
            LEFT JOIN nso007_project_stock AS project ON item.item_projectid = project.id
            LEFT JOIN nso007_item_group AS item_group ON item.item_groupid = item_group.id
            LEFT JOIN nso007_item_zone AS item_zone ON item.item_zone_id = item_zone.id
            WHERE lprocess.id = $id
        ");
        $description = $data[0]->project_code . "-" . $data[0]->group_name . "-" . $data[0]->zone_name . "(" . $data[0]->item_code . ")" . ', Step: ' . $data[0]->lpro_des;
        return $description;
    }
    /**
     *  Put commission when making payment
     */
    public function put_commission_payment($proccess_id, $process_payment, $loan_id)
    {
        $obj_agency_commis  = new AgencyCommission;
        // MENG PAY
        $amount_payment     = $process_payment;
        $loan_process_id    = $proccess_id;
        $amount             = $this->get_loan_paid_amount($loan_id);
        // MENG COST
        $amount_loan_cost   = $amount->loan_amount;
        $amount_due         = $amount->loan_amountos;
        // MENG PAID
        $amount_paid        = $amount_loan_cost - $amount->loan_discount - $amount_due;
        // MENG PAID + PAYMENT
        $sum_paid_payment   = $amount_paid + $amount_payment;

        $paid_amount_percent = ($amount_paid * 100 / $amount_loan_cost); //paid %

        $amount_payment_percentage = ($amount_payment * 100 / $amount_loan_cost); //pay %

        $sum_percentage = $paid_amount_percent + $amount_payment_percentage; //sum pay and paid

        $laon_id_1 = $loan_id;
        $data = LoanModel::where('id', $laon_id_1)->first();

        $sale_stock = SaleStockModel::where('id', $data->loan_saleid)->first();
        $items_price = ($sale_stock->sale_amount - $sale_stock->sale_discount);
        //        $sale_amount = $sale_stock->sale_amount;
        //        $agency_id = $sale_stock->agency_id;
        $commission = CommissionModel::where('sale_id', $sale_stock->id)->where('agency_status', 'Sale')->first();
        $item_stock = ItemModel::where('id', $commission->item_id)->first();

        $commission_list = CommissionItemModel::where('com_list_sale_id', $commission->sale_id)->get();
        if ($commission_list != null) {
            foreach ($commission_list as $val) {
                $commission_pay = $this->calculate_payment_commission($commission->commission, $items_price, $amount_paid, $amount_payment, $val->amount, $commission->commis_condition, $commission->condition);
                $agency_acc = $this->find_agency_account_number($val->agency_id);
                $commis_pust = number_format($commission_pay['balance'] - $val->receive, 4);

                if ($commis_pust > 0) {
                    $this->put_commission_to_report($item_stock->id, $commis_pust);
                    $this->increment_funds_balance($val->agency_id, $commis_pust, $item_stock->item_name, $agency_acc);
                    $this->increment_commission_receive($val->agency_id, $commis_pust, $val->id);
                }
            }
        }
    }
    /**
     *  Put commission when making payment
     */
    public function put_commission_payment_group($proccess_id, $process_payment, $loan_id)
    {
        $obj_agency_commis  = new AgencyCommission;
        // MENG PAY
        $amount_payment     = $process_payment;
        $loan_process_id    = $proccess_id;
        $amount             = $this->get_loan_paid_amount($loan_id);
        // MENG COST
        $amount_loan_cost   = $amount->loan_amount;
        $amount_due         = $amount->loan_amountos;
        // MENG PAID
        $amount_paid        = $amount_loan_cost - $amount->loan_discount - $amount_due;
        // MENG PAID + PAYMENT
        $sum_paid_payment   = $amount_paid + $amount_payment;

        $paid_amount_percent = ($amount_paid * 100 / $amount_loan_cost); //paid %

        $amount_payment_percentage = ($amount_payment * 100 / $amount_loan_cost); //pay %

        $sum_percentage = $paid_amount_percent + $amount_payment_percentage; //sum pay and paid

        $laon_id_1 = $loan_id;
        $data = LoanModel::where('id', $laon_id_1)->first();

        $sale_stock = SaleStockModel::where('id', $data->loan_saleid)->first();
        $items_price = ($sale_stock->sale_amount - $sale_stock->sale_discount);
        //        $sale_amount = $sale_stock->sale_amount;
        //        $agency_id = $sale_stock->agency_id;
        $commission = CommissionModel::where('sale_id', $sale_stock->id)->where('agency_status', 'Sale')->first();
        $item_stock = ItemModel::where('id', $commission->item_id)->first();

        $commission_list = CommissionItemModel::where('com_list_sale_id', $commission->sale_id)->get();
        if ($commission_list != null) {
            foreach ($commission_list as $val) {
                $commission_pay = $this->calculate_payment_commission($commission->commission, $items_price, $amount_paid, $amount_payment, $val->amount, $val->commis_condition, $val->condition);
                $agency_acc = $this->find_agency_account_number($val->agency_id);
                $commis_pust = number_format($commission_pay['balance'] - $val->receive, 4);

                if ($commis_pust > 0) {
                    $this->put_commission_to_report($item_stock->id, $commis_pust);
                    $this->increment_funds_balance($val->agency_id, $commis_pust, $item_stock->item_name, $agency_acc);
                    $this->increment_commission_receive($val->agency_id, $commis_pust, $val->id);
                }
            }
        }
    }

    public function put_commission_when_making_payment($process_id, $process_payment, $loan_id)
    {
        $obj_agency_commis  = new AgencyCommission;
        // MENG PAY
        $amount_payment     = $process_payment;
        $loan_process_id    = $process_id;
        $amount             = $this->get_loan_paid_amount($loan_id);
        // MENG COST
        $amount_loan_cost   = $amount->loan_amount;
        $amount_due         = $amount->loan_amountos;
        // MENG PAID
        $amount_paid        = $amount_loan_cost - $amount->loan_discount - $amount_due;

        // MENG PAID + PAYMENT
        $sum_paid_payment   = $amount_paid + $amount_payment;

        $paid_amount_percent = ($amount_paid * 100 / $amount_loan_cost); //paid %

        $amount_payment_percentage = ($amount_payment * 100 / $amount_loan_cost); //pay %

        $sum_percentage = $paid_amount_percent + $amount_payment_percentage; //sum pay and paid

        $laon_id_1 = $loan_id;

        $data_2 = DB::select("SELECT loan_saleid FROM nso007_loan WHERE id = $laon_id_1");
        $sale_id_1 = $data_2[0]->loan_saleid;

        $data_3 = DB::select("SELECT sale_amount, agency_id FROM nso007_sale_stock WHERE id = $sale_id_1");
        $sale_amount_1      = $data_3[0]->sale_amount;
        $agency_id_1        = $data_3[0]->agency_id;

        $data_4 = DB::select("SELECT id FROM nso007_agency_commission WHERE sale_id = $sale_id_1");
        $commission_id = $data_4[0]->id;

        $data_5 = DB::select("SELECT saledetail_itemid FROM nso007_sale_detail_stock WHERE saledetail_saleid = $sale_id_1");
        $item_id = $data_5[0]->saledetail_itemid;

        $data_6 = DB::select("SELECT item_name FROM nso007_item_stock WHERE id = $item_id");
        $item_name = $data_6[0]->item_name;

        $get_parent = DB::select("
            SELECT
            level1.id AS level_1_id,
            level2.id AS level_2_id,
            level3.id AS level_3_id,
            level4.id AS level_4_id,
            level5.id AS level_5_id,
            level6.id AS level_6_id
            
            FROM users level1
            
            LEFT JOIN users level2 ON level1.parent_id = level2.id
            LEFT JOIN users level3 ON level2.parent_id = level3.id
            LEFT JOIN users level4 ON level3.parent_id = level4.id
            LEFT JOIN users level5 ON level4.parent_id = level5.id
            LEFT JOIN users level6 ON level5.parent_id = level6.id
            WHERE level1.id = $agency_id_1
        ");

        $parent = [];

        empty($get_parent[0]->level_1_id) ?: $parent['parent_lvl_1_id'] = $get_parent[0]->level_1_id;
        empty($get_parent[0]->level_2_id) ?: $parent['parent_lvl_2_id'] = $get_parent[0]->level_2_id;
        empty($get_parent[0]->level_3_id) ?: $parent['parent_lvl_3_id'] = $get_parent[0]->level_3_id;
        empty($get_parent[0]->level_4_id) ?: $parent['parent_lvl_4_id'] = $get_parent[0]->level_4_id;
        empty($get_parent[0]->level_5_id) ?: $parent['parent_lvl_5_id'] = $get_parent[0]->level_5_id;
        empty($get_parent[0]->level_6_id) ?: $parent['parent_lvl_6_id'] = $get_parent[0]->level_6_id;

        $count_parent = count($parent);

        if ($count_parent == 6) {

            $lvl1__id = $parent['parent_lvl_1_id'];
            $lvl2__id = $parent['parent_lvl_2_id'];
            $lvl3__id = $parent['parent_lvl_3_id'];
            $lvl4__id = $parent['parent_lvl_4_id'];
            $lvl5__id = $parent['parent_lvl_5_id'];
            $lvl6__id = $parent['parent_lvl_6_id'];

            $lvl1_item_id       = $this->get_id_commission_list($lvl1__id, $commission_id);
            $lvl1_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl1_item_id);
            $lvl1_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl1_item_id);

            $lvl2_item_id       = $this->get_id_commission_list($lvl2__id, $commission_id);
            $lvl2_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl2_item_id);
            $lvl2_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl2_item_id);

            $lvl3_item_id       = $this->get_id_commission_list($lvl3__id, $commission_id);
            $lvl3_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl3_item_id);
            $lvl3_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl3_item_id);

            $lvl4_item_id       = $this->get_id_commission_list($lvl4__id, $commission_id);
            $lvl4_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl4_item_id);
            $lvl4_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl4_item_id);

            $lvl5_item_id       = $this->get_id_commission_list($lvl5__id, $commission_id);
            $lvl5_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl5_item_id);
            $lvl5_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl5_item_id);

            $lvl6_item_id       = $this->get_id_commission_list($lvl6__id, $commission_id);
            $lvl6_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl6_item_id);
            $lvl6_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl6_item_id);

            $commission_level1 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 1, $lvl1_commission, $lvl1_condition);
            $commission_level2 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 2, $lvl2_commission, $lvl2_condition);
            $commission_level3 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 3, $lvl3_commission, $lvl3_condition);
            $commission_level4 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 4, $lvl4_commission, $lvl4_condition);
            $commission_level5 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 5, $lvl5_commission, $lvl5_condition);
            $commission_level6 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 6, $lvl6_commission, $lvl6_condition);

            $lvl1_account_number = $this->get_funds_account_number($lvl1__id);
            $lvl2_account_number = $this->get_funds_account_number($lvl2__id);
            $lvl3_account_number = $this->get_funds_account_number($lvl3__id);
            $lvl4_account_number = $this->get_funds_account_number($lvl4__id);
            $lvl5_account_number = $this->get_funds_account_number($lvl5__id);
            $lvl6_account_number = $this->get_funds_account_number($lvl6__id);

            $lvl1 = $this->put_commission_to_report($lvl1_item_id, $commission_level1);
            if (!empty($commission_level1)) {
                $this->increment_funds_balance($lvl1__id, $commission_level1, $item_name, $lvl1_account_number);
                $this->increment_commission_receive($lvl1__id, $commission_level1, $commission_id);
            }

            $lvl2 = $this->put_commission_to_report($lvl2_item_id, $commission_level2);
            if (!empty($commission_level2)) {
                $this->increment_funds_balance($lvl2__id, $commission_level2, $item_name, $lvl2_account_number);
                $this->increment_commission_receive($lvl2__id, $commission_level2, $commission_id);
            }

            $lvl3 = $this->put_commission_to_report($lvl3_item_id, $commission_level3);
            if (!empty($commission_level3)) {
                $this->increment_funds_balance($lvl3__id, $commission_level3, $item_name, $lvl3_account_number);
                $this->increment_commission_receive($lvl3__id, $commission_level3, $commission_id);
            }

            $lvl4 = $this->put_commission_to_report($lvl4_item_id, $commission_level4);
            if (!empty($commission_level4)) {
                $this->increment_funds_balance($lvl4__id, $commission_level4, $item_name, $lvl4_account_number);
                $this->increment_commission_receive($lvl4__id, $commission_level4, $commission_id);
            }

            $lvl5 = $this->put_commission_to_report($lvl5_item_id, $commission_level5);
            if (!empty($commission_level5)) {
                $this->increment_funds_balance($lvl5__id, $commission_level5, $item_name, $lvl5_account_number);
                $this->increment_commission_receive($lvl5__id, $commission_level5, $commission_id);
            }

            $lvl6 = $this->put_commission_to_report($lvl6_item_id, $commission_level6);
            if (!empty($commission_level6)) {
                $this->increment_funds_balance($lvl6__id, $commission_level6, $item_name, $lvl6_account_number);
                $this->increment_commission_receive($lvl6__id, $commission_level6, $commission_id);
            }
        } elseif ($count_parent == 5) {
            $lvl1__id = $parent['parent_lvl_1_id'];
            $lvl2__id = $parent['parent_lvl_2_id'];
            $lvl3__id = $parent['parent_lvl_3_id'];
            $lvl4__id = $parent['parent_lvl_4_id'];
            $lvl5__id = $parent['parent_lvl_5_id'];

            $lvl1_item_id       = $this->get_id_commission_list($lvl1__id, $commission_id);
            $lvl1_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl1_item_id);
            $lvl1_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl1_item_id);

            $lvl2_item_id       = $this->get_id_commission_list($lvl2__id, $commission_id);
            $lvl2_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl2_item_id);
            $lvl2_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl2_item_id);

            $lvl3_item_id       = $this->get_id_commission_list($lvl3__id, $commission_id);
            $lvl3_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl3_item_id);
            $lvl3_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl3_item_id);

            $lvl4_item_id       = $this->get_id_commission_list($lvl4__id, $commission_id);
            $lvl4_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl4_item_id);
            $lvl4_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl4_item_id);

            $lvl5_item_id       = $this->get_id_commission_list($lvl5__id, $commission_id);
            $lvl5_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl5_item_id);
            $lvl5_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl5_item_id);

            $commission_level1 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 1, $lvl1_commission, $lvl1_condition);
            $commission_level2 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 2, $lvl2_commission, $lvl2_condition);
            $commission_level3 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 3, $lvl3_commission, $lvl3_condition);
            $commission_level4 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 4, $lvl4_commission, $lvl4_condition);
            $commission_level5 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 5, $lvl5_commission, $lvl5_condition);

            $lvl1_account_number = $this->get_funds_account_number($lvl1__id);
            $lvl2_account_number = $this->get_funds_account_number($lvl2__id);
            $lvl3_account_number = $this->get_funds_account_number($lvl3__id);
            $lvl4_account_number = $this->get_funds_account_number($lvl4__id);
            $lvl5_account_number = $this->get_funds_account_number($lvl5__id);

            $lvl1 = $this->put_commission_to_report($lvl1_item_id, $commission_level1);
            if (!empty($commission_level1)) {
                $this->increment_funds_balance($lvl1__id, $commission_level1, $item_name, $lvl1_account_number);
                $this->increment_commission_receive($lvl1__id, $commission_level1, $commission_id);
            }

            $lvl2 = $this->put_commission_to_report($lvl2_item_id, $commission_level2);
            if (!empty($commission_level2)) {
                $this->increment_funds_balance($lvl2__id, $commission_level2, $item_name, $lvl2_account_number);
                $this->increment_commission_receive($lvl2__id, $commission_level2, $commission_id);
            }

            $lvl3 = $this->put_commission_to_report($lvl3_item_id, $commission_level3);
            if (!empty($commission_level3)) {
                $this->increment_funds_balance($lvl3__id, $commission_level3, $item_name, $lvl3_account_number);
                $this->increment_commission_receive($lvl3__id, $commission_level3, $commission_id);
            }

            $lvl4 = $this->put_commission_to_report($lvl4_item_id, $commission_level4);
            if (!empty($commission_level4)) {
                $this->increment_funds_balance($lvl4__id, $commission_level4, $item_name, $lvl4_account_number);
                $this->increment_commission_receive($lvl4__id, $commission_level4, $commission_id);
            }

            $lvl5 = $this->put_commission_to_report($lvl5_item_id, $commission_level5);
            if (!empty($commission_level5)) {
                $this->increment_funds_balance($lvl5__id, $commission_level5, $item_name, $lvl5_account_number);
                $this->increment_commission_receive($lvl5__id, $commission_level5, $commission_id);
            }
        } elseif ($count_parent == 4) {
            $lvl1__id = $parent['parent_lvl_1_id'];
            $lvl2__id = $parent['parent_lvl_2_id'];
            $lvl3__id = $parent['parent_lvl_3_id'];
            $lvl4__id = $parent['parent_lvl_4_id'];

            $lvl1_item_id       = $this->get_id_commission_list($lvl1__id, $commission_id);
            $lvl1_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl1_item_id);
            $lvl1_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl1_item_id);

            $lvl2_item_id       = $this->get_id_commission_list($lvl2__id, $commission_id);
            $lvl2_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl2_item_id);
            $lvl2_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl2_item_id);

            $lvl3_item_id       = $this->get_id_commission_list($lvl3__id, $commission_id);
            $lvl3_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl3_item_id);
            $lvl3_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl3_item_id);

            $lvl4_item_id       = $this->get_id_commission_list($lvl4__id, $commission_id);
            $lvl4_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl4_item_id);
            $lvl4_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl4_item_id);

            $commission_level1 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 1, $lvl1_commission, $lvl1_condition);
            $commission_level2 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 2, $lvl2_commission, $lvl2_condition);
            $commission_level3 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 3, $lvl3_commission, $lvl3_condition);
            $commission_level4 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 4, $lvl4_commission, $lvl4_condition);

            $lvl1_account_number = $this->get_funds_account_number($lvl1__id);
            $lvl2_account_number = $this->get_funds_account_number($lvl2__id);
            $lvl3_account_number = $this->get_funds_account_number($lvl3__id);
            $lvl4_account_number = $this->get_funds_account_number($lvl4__id);

            $lvl1 = $this->put_commission_to_report($lvl1_item_id, $commission_level1);
            if (!empty($commission_level1)) {
                $this->increment_funds_balance($lvl1__id, $commission_level1, $item_name, $lvl1_account_number);
                $this->increment_commission_receive($lvl1__id, $commission_level1, $commission_id);
            }

            $lvl2 = $this->put_commission_to_report($lvl2_item_id, $commission_level2);
            if (!empty($commission_level2)) {
                $this->increment_funds_balance($lvl2__id, $commission_level2, $item_name, $lvl2_account_number);
                $this->increment_commission_receive($lvl2__id, $commission_level2, $commission_id);
            }

            $lvl3 = $this->put_commission_to_report($lvl3_item_id, $commission_level3);
            if (!empty($commission_level3)) {
                $this->increment_funds_balance($lvl3__id, $commission_level3, $item_name, $lvl3_account_number);
                $this->increment_commission_receive($lvl3__id, $commission_level3, $commission_id);
            }

            $lvl4 = $this->put_commission_to_report($lvl4_item_id, $commission_level4);
            if (!empty($commission_level4)) {
                $this->increment_funds_balance($lvl4__id, $commission_level4, $item_name, $lvl4_account_number);
                $this->increment_commission_receive($lvl4__id, $commission_level4, $commission_id);
            }
        } elseif ($count_parent == 3) {
            $lvl1__id = $parent['parent_lvl_1_id'];
            $lvl2__id = $parent['parent_lvl_2_id'];
            $lvl3__id = $parent['parent_lvl_3_id'];

            $lvl1_item_id       = $this->get_id_commission_list($lvl1__id, $commission_id);
            $lvl1_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl1_item_id);
            $lvl1_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl1_item_id);

            $lvl2_item_id       = $this->get_id_commission_list($lvl2__id, $commission_id);
            $lvl2_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl2_item_id);
            $lvl2_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl2_item_id);

            $lvl3_item_id       = $this->get_id_commission_list($lvl3__id, $commission_id);
            $lvl3_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl3_item_id);
            $lvl3_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl3_item_id);

            $commission_level1 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 1, $lvl1_commission, $lvl1_condition);
            $commission_level2 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 2, $lvl2_commission, $lvl2_condition);
            $commission_level3 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 3, $lvl3_commission, $lvl3_condition);

            $lvl1_account_number = $this->get_funds_account_number($lvl1__id);
            $lvl2_account_number = $this->get_funds_account_number($lvl2__id);
            $lvl3_account_number = $this->get_funds_account_number($lvl3__id);

            $lvl1 = $this->put_commission_to_report($lvl1_item_id, $commission_level1);
            if (!empty($commission_level1)) {
                $this->increment_funds_balance($lvl1__id, $commission_level1, $item_name, $lvl1_account_number);
                $this->increment_commission_receive($lvl1__id, $commission_level1, $commission_id);
            }

            $lvl2 = $this->put_commission_to_report($lvl2_item_id, $commission_level2);
            if (!empty($commission_level2)) {
                $this->increment_funds_balance($lvl2__id, $commission_level2, $item_name, $lvl2_account_number);
                $this->increment_commission_receive($lvl2__id, $commission_level2, $commission_id);
            }

            $lvl3 = $this->put_commission_to_report($lvl3_item_id, $commission_level3);
            if (!empty($commission_level3)) {
                $this->increment_funds_balance($lvl3__id, $commission_level3, $item_name, $lvl3_account_number);
                $this->increment_commission_receive($lvl3__id, $commission_level3, $commission_id);
            }
        } elseif ($count_parent == 2) {
            $lvl1__id = $parent['parent_lvl_1_id'];
            $lvl2__id = $parent['parent_lvl_2_id'];

            $lvl1_item_id       = $this->get_id_commission_list($lvl1__id, $commission_id);
            $lvl1_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl1_item_id);
            $lvl1_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl1_item_id);

            $lvl2_item_id       = $this->get_id_commission_list($lvl2__id, $commission_id);
            $lvl2_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl2_item_id);
            $lvl2_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl2_item_id);

            $commission_level1 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 1, $lvl1_commission, $lvl1_condition);
            $commission_level2 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 2, $lvl2_commission, $lvl2_condition);

            $lvl1_account_number = $this->get_funds_account_number($lvl1__id);
            $lvl2_account_number = $this->get_funds_account_number($lvl2__id);

            $lvl1 = $this->put_commission_to_report($lvl1_item_id, $commission_level1);
            if (!empty($commission_level1)) {
                $this->increment_funds_balance($lvl1__id, $commission_level1, $item_name, $lvl1_account_number);
                $this->increment_commission_receive($lvl1__id, $commission_level1, $commission_id);
            }

            $lvl2 = $this->put_commission_to_report($lvl2_item_id, $commission_level2);
            if (!empty($commission_level2)) {
                $this->increment_funds_balance($lvl2__id, $commission_level2, $item_name, $lvl2_account_number);
                $this->increment_commission_receive($lvl2__id, $commission_level2, $commission_id);
            }
        } elseif ($count_parent == 1) {
            $lvl1__id = $parent['parent_lvl_1_id'];

            $lvl1_item_id       = $this->get_id_commission_list($lvl1__id, $commission_id);
            $lvl1_commission    = $obj_agency_commis->get_commission_in_commission_list($lvl1_item_id);
            $lvl1_condition     = $obj_agency_commis->get_condition_in_commission_list($lvl1_item_id);

            $commission_level1 = $this->calculate_commission_payment($amount_loan_cost, $amount_paid, $amount_payment, 1, $lvl1_commission, $lvl1_condition);

            $lvl1_account_number = $this->get_funds_account_number($lvl1__id);

            $lvl1 = $this->put_commission_to_report($lvl1_item_id, $commission_level1);
            if (!empty($commission_level1)) {
                $this->increment_funds_balance($lvl1__id, $commission_level1, $item_name, $lvl1_account_number);
                $this->increment_commission_receive($lvl1__id, $commission_level1, $commission_id);
            }
        }
    }
    /**
     *  Get loan paid
     */
    public function get_loan_paid_amount($loan_id)
    {
        $data = LoanModel::select('loan_amount', 'loan_discount', 'loan_amountos')->find($loan_id);
        return $data;
    }
    /**
     *  Calculate commission
     */
    public function calculate_commission_payment($cost, $paid, $payment, $level, $commission, $condition)
    {
        $lvl_no = $level;
        $get_deposit_amount = 0;
        $condition_commission   = $cost * $condition / 100;

        if ($lvl_no == 1) {
            $get_commission         = $cost * $commission / 100;
        } elseif ($lvl_no == 2) {
            $get_commission         = $cost * 0.1 / 100;
        } elseif ($lvl_no == 3) {
            $get_commission         = $cost * 0.1 / 100;
        } elseif ($lvl_no == 4) {
            $get_commission         = $cost * 0.1 / 100;
        } elseif ($lvl_no == 5) {
            $get_commission         = $cost * 0.1 / 100;
        } elseif ($lvl_no == 6) {
            $get_commission         = $cost * 0.1 / 100;
        }
        if ($paid < $condition_commission) {
            if (($paid + $payment) <= $condition_commission) {
                $get_deposit = $payment / $condition_commission;
                $get_deposit_amount = $get_commission * $get_deposit;
            } elseif (($paid + $payment) > $condition_commission) {
                $get_deposit = ($condition_commission - $paid) / $condition_commission;
                $get_deposit_amount = $get_commission * $get_deposit;
            }
        }
        return $get_deposit_amount;
    }
    /**
     *  Get funds account number
     */
    public function get_funds_account_number($agency_id)
    {
        $data = DB::select("SELECT account_number FROM nso007_funds WHERE user_id = $agency_id");
        $account_number = $data[0]->account_number;
        return $account_number;
    }
    /**
     *  Get ID commission list
     */
    public function get_id_commission_list($agency_id, $commission_id)
    {
        $data = DB::select("SELECT id FROM nso007_agency_commission_list WHERE agency_id = $agency_id
            AND commission_id = $commission_id");
        $id = $data[0]->id;
        return $id;
    }
    /**
     *  Put commission to report
     */
    public function put_commission_to_report($lvl1_item_id, $commission_level1)
    {
        $data = array(
            'commission_item_id' => $lvl1_item_id,
            'amount' => $commission_level1
        );
        CommissionItemReportModel::insert($data);
    }
    /**
     *  Increment balance funds
     */
    public function increment_funds_balance($agency_id, $commission_amount, $item_name, $account_number)
    {
        $agency_commission = new AgencyCommission;
        $agency_mix = $agency_commission->get_agency_info_in_getcommission($item_name);
        FundsRegisterModel::where('user_id', $agency_id)->increment('balance', $commission_amount);
        FundsRegisterModel::where('user_id', $agency_id)->increment('received​_balance', $commission_amount);
        $balance = FundsRegisterModel::where('user_id', $agency_id)->first()->balance;
        $revenue = new FundsTransactionModel;
        $revenue->operation_type    = 3;
        $revenue->account_number    = $account_number;
        $revenue->amount            = $commission_amount;
        $revenue->balance           = $balance;
        $revenue->request_date      = date('Y-m-d H:i:s A');
        $revenue->remark            = "Get commission from " . $agency_mix . "/" . $item_name;
        $revenue->user_log          = Auth::user()->id;
        $revenue->status            = 1;
        $revenue->save();
    }
    /**
     *  Increment commision agency recieve
     */
    public function increment_commission_receive($agency_id, $commission_amount, $commissionid)
    {
        CommissionItemModel::where('id', $commissionid)
            ->where('agency_id', $agency_id)
            ->increment('receive', $commission_amount);
    }

    /**
     *  Get loan record by id
     */
    public function get_loan_info_record($id)
    {
        $data = LoanModel::find($id);
        return $data;
    }
    /**
     *  Pay Off
     */
    public function client_payment_payoff(Request $request)
    {
        LoanModel::where('id',$request->payoff_loan_id)->update([
            'request_payoff'    => 1,
            'loan_enddate'      => date('Y-m-d',strtotime($request->payoff_date))
        ]);
        
        RequestPayoffModel::insert([
            'loan_id'   => $request->payoff_loan_id,
            'bank_id'   => $request->bank_id,
            'amount'    => $request->payoff_amount,
            'date'      => date('Y-m-d',strtotime($request->payoff_date))
        ]);

        \LogActivity::addToLog('Request pay off!');
        return redirect()->route('client-payment-list.show',Crypt::encrypt(LoanModel::find($request->payoff_loan_id)->loan_clientid))
            ->with('success', 'Payment paid off successfully!');
    }


    public function calculate_approve_commission($cost, $item, $paid, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_commission         = $cost * $commission / 100;
        $get_paid_per = ($paid * 100) / $item;
        $condition_commission   = $this->check_commission($get_paid_per, $get_commission, $condition, $commis_condition);
        //        if ($paid <= $condition_commission) {
        //            $get_deposit = $paid / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        } elseif ($paid > $condition_commission) {
        //            $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        }
        return $condition_commission;
    }

    public function calculate_approve_commission_group($cost, $item, $paid, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_commission         = $cost * $commission / 100;
        $get_paid_per = ($paid * 100) / $item;
        $condition_commission   = $this->check_commission($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid <= $condition_commission) {
        //            $get_deposit = $paid / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        } elseif ($paid > $condition_commission) {
        //            $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        }
        return $condition_commission;
    }
    public function calculate_approve_commission_group1($cost, $item, $paid, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_commission         = $cost * $commission / 100;
        $get_paid_per = ($paid * 100) / $item;
        $condition_commission   = $this->check_commission($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid <= $condition_commission) {
        //            $get_deposit = $paid / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        } elseif ($paid > $condition_commission) {
        //            $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        }
        return $condition_commission;
    }
    public function calculate_approve_commission_group2($cost, $item, $paid, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_commission         = $cost * $commission / 100;
        $get_paid_per = ($paid * 100) / $item;
        $condition_commission   = $this->check_commission($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid <= $condition_commission) {
        //            $get_deposit = $paid / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        } elseif ($paid > $condition_commission) {
        //            $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        }
        return $condition_commission;
    }

    public function calculate_approve_commission_group3($cost, $item, $paid, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_commission         = $cost * $commission / 100;
        $get_paid_per = ($paid * 100) / $item;
        $condition_commission   = $this->check_commission($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid <= $condition_commission) {
        //            $get_deposit = $paid / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        } elseif ($paid > $condition_commission) {
        //            $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        }
        return $condition_commission;
    }
    public function calculate_approve_commission_group4($cost, $item, $paid, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_commission         = $cost * $commission / 100;
        $get_paid_per = ($paid * 100) / $item;
        $condition_commission   = $this->check_commission($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid <= $condition_commission) {
        //            $get_deposit = $paid / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        } elseif ($paid > $condition_commission) {
        //            $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //            $get_deposit_amount = $get_commission * $get_deposit;
        //        }
        return $condition_commission;
    }

    public function calculate_payment_commission($cost, $item, $paid, $payment, $commission, $commis_condition, $condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_paid_per = (($paid + $payment) * 100) / $item;
        $condition_commission   = $this->check_commission_payment($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid < $condition_commission) {
        //            if (($paid + $payment) <= $condition_commission) {
        //                $get_deposit = $payment / $condition_commission;
        //                $get_deposit_amount = $get_commission * $get_deposit;
        //            } elseif (($paid + $payment) > $condition_commission) {
        //                $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //                $get_deposit_amount = $get_commission * $get_deposit;
        //            }
        //        }
        return $condition_commission;
    }


    public function calculate_payment_commission_company($cost, $item, $paid, $payment, $commission, $condition, $commis_condition)
    {
        //        $get_deposit_amount = 0;
        //        $condition_commission   = $item * $condition / 100;
        $get_paid_per = (($paid + $payment) * 100) / $item;
        $condition_commission   = $this->check_commission_payment_company($get_paid_per, $commission, $condition, $commis_condition);
        //        if ($paid < $condition_commission) {
        //            if (($paid + $payment) <= $condition_commission) {
        //                $get_deposit = $payment / $condition_commission;
        //                $get_deposit_amount = $get_commission * $get_deposit;
        //            } elseif (($paid + $payment) > $condition_commission) {
        //                $get_deposit = ($condition_commission - $paid) / $condition_commission;
        //                $get_deposit_amount = $get_commission * $get_deposit;
        //            }
        //        }
        return $condition_commission;
    }

    public function find_agency_account_number($agency_id)
    {
        $data = FundsRegisterModel::where('user_id', $agency_id)->first();
        return $data->account_number;
    }
    public function request_cancel($id)
    {
        $id = Crypt::decrypt($id);
        DB::table('nso007_loan')->where('id', $id)->update([
            'loan_cancel_status' => 1
        ]);
        return back()->with('success', 'Your Request to cancel this payment is being processing!');
    }
    public function reject_cancel($id)
    {
        $id = Crypt::decrypt($id);
        DB::table('nso007_loan')->where('id', $id)->update([
            'loan_cancel_status' => null
        ]);
        return back()->with('success', 'Cancel Payment Was Rejected');
    }
    public function approve_cancel($id)
    {
        $id = Crypt::decrypt($id);
        DB::table('nso007_loan')->where('id', $id)->update([
            'loan_cancel_status' => 2
        ]);
        $loan = LoanModel::find($id);
        $sale_id        = $loan->loan_saleid;

        $sale_detail    = SaleStockDetailModel::where('saledetail_saleid', $sale_id)->get();
        foreach ($sale_detail as $detail) {
            DB::table('nso007_item_stock')->where('id', $detail->saledetail_itemid)->update([
                'item_stockstatus' => 1, 'item_salestatus' => 0, 'available' => 1
            ]);
        }
        SaleStockModel::where('id', $sale_id)->update([
            'status'    => 0
        ]);
        //update loan process

        DB::table('nso007_loan_process')
            ->where('loanprocess_loanid', $id)
            ->whereNull('loanprocess_paid')->update([
                'loanprocess_status'    => 0
            ]);
        $loan_process = DB::table('nso007_loan_process')->where('loanprocess_loanid', $id)->get();


        \LogActivity::addToLog('Approve Cancel Client Payment');
        return back()->with('success', 'Cancel Payment was Successfully Approved');
    }
    public function delete_loan_process($id)
    {
        $id             = Crypt::decrypt($id);
        $loan_process   = LoanProcessModel::find($id);
        $paid_amount    = $loan_process->loanprocess_owe - $loan_process->loanprocess_owepay;
        DB::table('nso007_loan_process')->where('id', $id)
            ->update([
                'loanprocess_activity'      => null,
                'loanprocess_paid'          => null,
                'loanprocess_datepaid'      => null,
                'loanprocess_by'            => null,
                'loanprocess_interestpay'   => ($loan_process->loanprocess_interest - $loan_process->loanprocess_interestpay),
                'loanprocess_principlepay'  => ($loan_process->loanprocess_principle - $loan_process->loanprocess_principlepay),
                'loanprocess_owepay'        => ($loan_process->loanprocess_owe - $loan_process->loanprocess_owepay)
            ]);
        DB::table('nso007_loan_process')->where('id', $id + 1)
            ->update([
                'penalty_paid'      => 0,
            ]);
        $LoanPaidForDelete  = LoanPaidModel::where('loanpaid_processid', $id)->first();
        $maxID              = CancelPaidModel::max('id') + 1;
        DB::table('nso007_loan_paid_cancel')->insert([
            'loanprocess_id'    => $LoanPaidForDelete->id,
            'principal'         => $LoanPaidForDelete->loanpaid_principle,
            'interest'          => $LoanPaidForDelete->loanpaid_interest,
            'payment'           => $LoanPaidForDelete->loanpaid_total,
            'penalty'           => $LoanPaidForDelete->loanpaid_penalty,
            'datepayment'       => $LoanPaidForDelete->loanpaid_payment,
            'date'              => date('Y-m-d'),
            'user_id'           => Auth::user()->id,
            'acc_code'          => $LoanPaidForDelete->loandpaid_bankacc,
            'reference'         => 'Tr-' . str_pad($maxID, 7, "0", STR_PAD_LEFT),
            'receipt_no'        => $LoanPaidForDelete->loanpaid_invoice
        ]);
        DB::table('nso007_loan_paid')->where('loanpaid_processid', $id)->delete();

        $loan       = LoanModel::find($loan_process->loanprocess_loanid);
        $item_cost  = $loan->loan_amount - $loan->loan_discount;
        $percentage = (100 * $paid_amount) / $item_cost;

        //update loan percentage
        DB::table('nso007_loan')->where('id', $loan->id)->update([
            'loan_percentpay' => DB::raw("loan_percentpay-$percentage")
        ]);

        //delete payment from journal
        DB::table('nso007_journal')->where([['journal_referenceid', $id], ['journal_type', 12]])->delete();
        //delete penalty and interest from journal
        DB::table('nso007_journal')->where([['journal_referenceid', $id], ['journal_type', 40]])->whereIn('journal_acccode', [40002, 11001, 40000])->delete();
        \LogActivity::addToLog('Delete Client Payment on loan Process!');
        return back()->with('success', 'Payment Successfully Deleted');
    }

    public function client_payment_index()
    {
        $cashier = User::where([['branch_id', \Branch::branch_id()], ['role', 'Administrator']])->select(DB::raw('CONCAT(user_firstname," ",user_lastname) As cashier'), 'id')->pluck('cashier', 'id')->prepend('Select Cashier', '');
        // $method = AccountChartModel::whereIn('id', [11001, 11002, 11012])->pluck('acc_name', 'id')->prepend('Select Payment Method', '');
        $method = BankAgencyModel::pluck('name', 'id')->prepend('Select Payment', '');
        return view('administrator.client-payment.payment.payment-pending', compact('cashier', 'method'));
    }
    public function client_payment_pending()
    {
        $paid_pending = LoanPaidModel::join('nso007_loan_process', 'nso007_loan_process.id', 'nso007_loan_paid.loanpaid_processid')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('users', 'users.id', 'nso007_loan_paid.loanpaid_by')
            // ->join('nso007_bank_agency as bank', 'bank.id', 'nso007_loan_paid.loandpaid_bankacc')
            ->select(
                'nso007_loan_paid.id as paid_id',
                'nso007_loan_paid.loanpaid_approved',
                'nso007_loan_paid.loanpaid_invoice',
                'nso007_loan_paid.loanpaid_reference',
                'nso007_loan_paid.loanpaid_remark',
                'nso007_loan_paid.loanpaid_des',
                'nso007_loan_paid.loanpaid_createdate',
                'nso007_loan_paid.loanpaid_principle',
                'nso007_loan_paid.loanpaid_total',
                'nso007_loan_paid.loanpaid_penalty',
                'nso007_loan_paid.loanpaid_penalty_option',
                'nso007_loan_paid.created_at',
                'nso007_project_stock.project_name',
                'nso007_client.client_fullname',
                'nso007_loan_paid.loandpaid_bankacc',
                'nso007_loan_paid.loanpaid_payment',
                // 'bank.name as method',
                DB::raw('CONCAT(users.user_firstname," ",users.user_lastname) As cashier')
            )
            ->selectRaw('GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as item_house')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->where('nso007_loan_paid.loanpaid_approved', null)
            ->groupBy('nso007_loan_paid.id')
            ->get();
        return Datatables::of($paid_pending)
            ->addIndexColumn()
            ->addColumn('status', function ($paid_pending) {
                return '<span class="label label-info fa fa-spinner">Pending</span>';
            })
            ->addColumn('loanpaid_principle', function ($paid_pending) {
                return '$ ' . number_format($paid_pending->loanpaid_principle, 2);
            })
            ->addColumn('loanpaid_total', function ($paid_pending) {
                return '$ ' . number_format($paid_pending->loanpaid_total, 2);
            })
            ->editColumn('penalty', function ($paid_pending) {
                return '$ ' . number_format($paid_pending->loanpaid_penalty, 2);
            })
            ->editColumn('option', function ($paid_pending) {
                $status = '';
                if (!empty($paid_pending->loanpaid_penalty_option)) {
                    if ($paid_pending->loanpaid_penalty_option == 1) {
                        $status = '<span class="label label-success">Pay Now</span>';
                    } elseif ($paid_pending->loanpaid_penalty_option == 2) {
                        $status = '<span class="label label-primary">Pass to Next Step</span>';
                    } else {
                        $status = '<span class="label label-default">Wave</span>';
                    }
                }
                return $status;
            })
            ->addColumn('method',function($paid_pending){
                $method = "";
                if($paid_pending->loandpaid_bankacc == 11002) $method = "Cash In Bank";
                else if($paid_pending->loandpaid_bankacc == 11001) $method = "Cash On Hand";
                else{}
                return $method;
            })
            ->addColumn('loanpaid_payment', function ($paid_pending) {
                return date('d-m-Y', strtotime($paid_pending->loanpaid_payment));
            })
            ->addColumn('check_id', function ($paid_pending) {
                return '<label class="checkbox-list">
                          <input type="checkbox" class="checkbox" data-id="' . $paid_pending->paid_id . '">
                          <span class="checkmark"></span>
                    </label>';
            })
            ->rawColumns(['check_id' => 'check_id', 'option' => 'option', 'status' => 'status'])->make(true);
    }

    public function approve_booking_pending()
    {
        $cashier = User::where([['branch_id', \Branch::branch_id()], ['role', 'Administrator']])->select(DB::raw('CONCAT(user_firstname," ",user_lastname) As cashier'), 'id')->pluck('cashier', 'id')->prepend('Select Cashier', '');
        $method = BankAgencyModel::pluck('name', 'id')->prepend('Select Payment', '');
        return view('administrator.client-payment.payment.payment-booking', compact('cashier', 'method'));
    }

    public function client_payment_booking()
    {
        $paid_pending = SaleStockModel::join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->select(
                'nso007_sale_stock.*',
                'nso007_project_stock.project_name',
                'nso007_client.client_fullname'
            )
            ->selectRaw('GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as item_house')
            ->groupBy('nso007_sale_stock.id')
            ->get();
        return Datatables::of($paid_pending)
            ->addIndexColumn()

            ->addColumn('sale_amount', function ($paid_pending) {
                return '$ ' . number_format($paid_pending->sale_amount, 2);
            })
            ->addColumn('sale_discount', function ($paid_pending) {
                return '$ ' . number_format($paid_pending->sale_discount, 2);
            })

            ->addColumn('sale_date', function ($paid_pending) {
                return date('d-m-Y', strtotime($paid_pending->sale_date));
            })
            ->addColumn('check_id', function ($paid_pending) {
                return '<label class="checkbox-list">
                          <input type="checkbox" class="checkbox" data-id="' . $paid_pending->id . '">
                          <span class="checkmark"></span>
                    </label>';
            })
            ->rawColumns(['check_id' => 'check_id'])->make(true);
    }

    public function approve_client_payment_booking(Request $request)
    {
        $loanPaidIDs = $request->paid_id;
        $Sale = SaleStockModel::whereIn('id', $loanPaidIDs)->get();
        foreach ($Sale as $paid) {

            $remain_amount_ar=$paid->sale_amount-$paid->sale_discount;
            $j=JournalModel::select(DB::raw("Max(journal_tran_id) as last_num"))->first();

            $journal=new JournalModel;
            $journal->branch_id=\Globals::get_branch_and_project_id($paid->id,'branch_id');
            $journal->journal_parentid=0;
            $journal->journal_type=1;
            $journal->journal_acccode=11003;
            $journal->voucher_type=1;
            $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
            $journal->journal_des=\Globals::BookingBySaleID("", $paid->id);
            $journal->journal_debit=$remain_amount_ar;
            $journal->journal_credit=0;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d",strtotime($paid->sale_date));
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$paid->id;
            $journal->journal_by_project=\Globals::get_branch_and_project_id($paid->id,'project_id');
            $journal->journal_by=auth()->user()->id;
            $journal->save();
            $jj1=JournalModel::select(DB::raw("Max(id) as last_id"))->first();
            if (!empty($paid->sale_discount) || $paid->sale_discount > 0){
                $journal=new JournalModel;
                $journal->branch_id=\Globals::get_branch_and_project_id($paid->id,'branch_id');
                $journal->journal_parentid=$jj1->last_id;
                $journal->journal_type=4;
                $journal->journal_acccode=41002;
                $journal->voucher_type=1;
                $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
                $journal->journal_des=\Globals::BookingBySaleID("", $paid->id);
                $journal->journal_debit=$paid->sale_discount;
                $journal->journal_credit=0;
                $journal->journal_tran_id=$j->last_num+1;
                $journal->journal_transactiondate=date("Y-m-d");
                $journal->journal_paydate=date("Y-m-d",strtotime($paid->sale_date));
                $journal->journal_currency=1;
                $journal->transaction_type='Deposit Discount';
                $journal->journal_referenceid=$paid->id;
                $journal->journal_by_project=\Globals::get_branch_and_project_id($paid->id,'project_id');
                $journal->journal_by=auth()->user()->id;
                $journal->save();
            }
            $journal=new JournalModel;
            $journal->branch_id=\Globals::get_branch_and_project_id($paid->id,'branch_id');
            $journal->journal_parentid=$jj1->last_id;
            $journal->journal_type=4;
            $journal->journal_acccode=41000;
            $journal->voucher_type=1;
            $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
            $journal->journal_des=\Globals::BookingBySaleID("", $paid->id);
            $journal->journal_debit=0;
            $journal->journal_credit=$paid->sale_amount;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d",strtotime($paid->sale_date));
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$paid->id;
            $journal->journal_by_project=\Globals::get_branch_and_project_id($paid->id,'project_id');
            $journal->journal_by=auth()->user()->id;
            $journal->save();
            //End

        }
        return response()->json(['success' => true]);
    }

    public function approve_deposit_pending()
    {
        $cashier = User::where([['branch_id', \Branch::branch_id()], ['role', 'Administrator']])->select(DB::raw('CONCAT(user_firstname," ",user_lastname) As cashier'), 'id')->pluck('cashier', 'id')->prepend('Select Cashier', '');
        $method = BankAgencyModel::pluck('name', 'id')->prepend('Select Payment', '');
        return view('administrator.client-payment.payment.payment-deposit', compact('cashier', 'method'));
    }
    public function client_payment_deposit()
    {
        $paid_pending = DepositModel::join('nso007_deposit_schedule', 'nso007_deposit_schedule.id', 'nso007_deposit.deposit_schedule_id')
            ->join('nso007_sale_stock', 'nso007_deposit.deposit_saleid', 'nso007_sale_stock.id')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->select(
                'nso007_deposit.*',
                'nso007_deposit_schedule.type',
                'nso007_project_stock.project_name',
                'nso007_client.client_fullname'
            )
            ->selectRaw('GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as item_house')
            ->groupBy('nso007_deposit.id')
            ->get();
        return Datatables::of($paid_pending)
            ->addIndexColumn()

            ->addColumn('sale_amount', function ($paid_pending) {
                return '$ ' . number_format($paid_pending->deposit_amount, 2);
            })
            ->addColumn('type', function ($paid_pending) {
                if ($paid_pending->type==0){
                    $type='ប្រាក់កក់លើកដំបូង';
                }else if($paid_pending->type==1){
                    $type='ប្រាក់ត្រូវបង្គ្រប់ខែទី១';
                }else{
                    $type='ប្រាក់ត្រូវបង់ខែទី១';
                }
                return $type;
            })

            ->addColumn('sale_date', function ($paid_pending) {
                return date('d-m-Y', strtotime($paid_pending->deposit_date));
            })
            ->addColumn('check_id', function ($paid_pending) {
                return '<label class="checkbox-list">
                          <input type="checkbox" class="checkbox" data-id="' . $paid_pending->id . '">
                          <span class="checkmark"></span>
                    </label>';
            })
            ->rawColumns(['check_id' => 'check_id'])->make(true);
    }

    public function approve_client_payment_deposit(Request $request)
    {
        $loanPaidIDs = $request->paid_id;
        $Sale = DepositModel::whereIn('id', $loanPaidIDs)->get();
        foreach ($Sale as $paid) {

            $Sale_s = DepositScheduleModel::where('id', $paid->deposit_schedule_id)->first();
            if ($Sale_s->type==0){
                $type='ប្រាក់កក់លើកដំបូង';
            }else if($Sale_s->type==1){
                $type='ប្រាក់ត្រូវបង្គ្រប់ខែទី១';
            }else{
                $type='ប្រាក់ត្រូវបង់ខែទី១';
            }

            $j=JournalModel::select(DB::raw("Max(journal_tran_id) as last_num"))->first();

            $journal=new JournalModel;
            $journal->branch_id=\Globals::get_branch_and_project_id($paid->deposit_saleid,'branch_id');
            $journal->journal_parentid=0;
            $journal->journal_type=1;
            $journal->journal_acccode= $paid->account_code;
            $journal->voucher_type=1;
            $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
            $journal->journal_des=\Globals::DepositBySaleID("", $paid->deposit_saleid).'('.$type.')';
            $journal->journal_debit=$paid->deposit_amount;
            $journal->journal_credit=0;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d",strtotime($paid->deposit_date));
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit Payment';
            $journal->journal_referenceid=$paid->deposit_saleid;
            $journal->journal_by_project=\Globals::get_branch_and_project_id($paid->deposit_saleid,'project_id');
            $journal->journal_by=auth()->user()->id;
            $journal->save();

            $jj1=JournalModel::select(DB::raw("Max(id) as last_id"))->first();

            $journal=new JournalModel;
            $journal->branch_id=\Globals::get_branch_and_project_id($paid->deposit_saleid,'branch_id');
            $journal->journal_parentid=$jj1->last_id;
            $journal->journal_type=1;
            $journal->journal_acccode=11003;
            $journal->voucher_type=1;
            $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
            $journal->journal_des=\Globals::DepositBySaleID("", $paid->deposit_saleid).'('.$type.')';
            $journal->journal_debit= 0;
            $journal->journal_credit=$paid->deposit_amount;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d",strtotime($paid->deposit_date));
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit Payment';
            $journal->journal_referenceid=$paid->deposit_saleid;
            $journal->journal_by_project=\Globals::get_branch_and_project_id($paid->deposit_saleid,'project_id');
            $journal->journal_by=auth()->user()->id;
            $journal->save();

        }
        return response()->json(['success' => true]);
    }


    public function add_journal_payment($process_id, $bank_list, $payment, $datepayment, $branch_id, $penalty, $do_date)
    {
        $des = $this->set_payment_description($process_id);
        $by = auth()->user()->id;
        $journal = new JournalAccountingModel();
        $journal->journal_parentid          = 0;
        $journal->journal_acccode           = $bank_list;
        $journal->journal_type              = 12;
        $journal->journal_debit             = $payment;
        $journal->journal_transactiondate   = $do_date;
        $journal->journal_paydate           = date('Y-m-d', strtotime($datepayment));
        $journal->journal_des               = ($des . " (Payment)");
        $journal->journal_by                = $by;
        $journal->journal_currency          = 1;
        $journal->journal_referenceid       = $process_id;
        $journal->branch_id                 = $branch_id;
        $journal->save();
        $parent_id = JournalAccountingModel::max('id');
        $data_penalty = array(
            'journal_parentid'          => $parent_id,
            'journal_acccode'           => $bank_list,
            'journal_type'              => 16,
            'journal_debit'             => $penalty,
            'journal_transactiondate'   => $do_date,
            'journal_paydate'           => date('Y-m-d', strtotime($datepayment)),
            'journal_des'               => ($des . " (Penalty)"),
            'journal_by'                => $by,
            'journal_currency'          => 1,
            'journal_referenceid'       => $process_id
        );
        JournalModel::insert($data_penalty);
    }
    public function add_journal_payment_without_penalty($process_id, $bank_list, $payment, $datepayment, $branch_id, $do_date)
    {
        $des = $this->set_payment_description($process_id);
        $by = auth()->user()->id;
        $journal = new JournalModel();
        $journal->journal_parentid          = 0;
        $journal->journal_acccode           = $bank_list;
        $journal->journal_type              = 12;
        $journal->journal_debit             = $payment;
        $journal->journal_transactiondate   = $do_date;
        $journal->journal_paydate           = date('Y-m-d', strtotime($datepayment));
        $journal->journal_des               = ($des . " (Payment)");
        $journal->journal_by                = $by;
        $journal->journal_currency          = 1;
        $journal->journal_referenceid       = $process_id;
        $journal->branch_id                 = $branch_id;
        $journal->save();
    }
    public function add_journal_with_interest($process_id, $bank_list, $interest, $datepayment, $branch_id, $do_date)
    {
        $des = $this->set_payment_description($process_id);
        $data3 = array(

            'journal_parentid'          => 0,
            'journal_acccode'           => $bank_list,
            'journal_type'              => 40,
            'journal_credit'            => $interest,
            'journal_transactiondate'   => $do_date,
            'journal_paydate'           => date('Y-m-d', strtotime($datepayment)),
            'journal_des'               => ($des . " (Interest)"),
            'journal_by'                => auth()->user()->id,
            'journal_currency'          => 1,
            'journal_referenceid'       => $process_id,
            'branch_id'       => $branch_id
        );
        JournalModel::insert($data3);
    }

    //  approve client payment
    public function updateLoanPaymentByLoanPaidID($loanPaidID)
    {
        $loanPaid   = LoanPaidModel::find($loanPaidID);
        $loan       = LoanModel::find(LoanProcessModel::find($loanPaid->loanpaid_processid)->loanprocess_loanid);
        $percent    = $loanPaid->loanpaid_principle * 100 / $loan->loan_amount;
        LoanModel::where('id', $loan->id)
            ->update([
                'loan_amountos'     => DB::raw("loan_amountos-$loanPaid->loanpaid_principle"),
                'loan_percentpay'   => DB::raw("loan_percentpay+$percent")
            ]);
        $loan = LoanModel::find($loan->id);
        if ($loan->loan_amountos <= 0 || $loan->loan_percentpay >= 100) {
            $loan->loan_status = 2;
            $loan->loan_amountos = 0;
            $loan->loan_percentpay = 100;
            $loan->update();
        }
    }
    public function updateCommissionByLoanPaidID($loanPaidID)
    {
        $loanPaid   = LoanPaidModel::find($loanPaidID);
        $loan       = LoanModel::find(LoanProcessModel::find($loanPaid->loanpaid_processid)->loanprocess_loanid);
        $agencySale = CommissionModel::where([['sale_id', $loan->loan_saleid], ['agency_status', 'Sale']])->first();
        if (!empty($agencySale)) {
            $this->updateAgencyCommissionBySaleID($loan->loan_saleid);
            $this->updateAdminCommissionBySaleID($loan->loan_saleid);
        }
    }
    public function updateAgencyCommissionBySaleID($sale_id)
    {
        $commission     = CommissionModel::where('sale_id', $sale_id)->first();
        $loan           = LoanModel::where('loan_saleid', $sale_id)->first();
        if (!empty($commission)) {
            $commissionList = CommissionItemModel::where('commission_id', $commission->id)->get();
            foreach ($commissionList as $key => $AgentComm) {
                $this->updateAgencyCommissionByCommissionListID($AgentComm->id, $AgentComm->amount, $AgentComm->condition, $AgentComm->commis_condition, $loan->loan_percentpay);
            }
        }
    }
    public function updateAdminCommissionBySaleID($sale_id)
    {
        $admincommission = CommissionAdminModel::where('sale_id', $sale_id)->first();
        if (!empty($admincommission)) {
            $loan           = LoanModel::where('loan_saleid', $sale_id)->first();
            $percentage     = $loan->loan_percentpay;
            $amount         = $admincommission->commission_amount;
            $condition      = $admincommission->condition;
            $commission     = $admincommission->commis_condition;
            $autoCommission = $this->auto_commission($percentage, $amount, $condition, $commission);
            $balance        = $autoCommission['balance'];
            $status         = $autoCommission['status'];
            if ($status == true && $balance > $admincommission->commission_received) {
                $incrementCommission = $balance - $admincommission->commission_received;
                CommissionAdminModel::where('sale_id', $sale_id)
                    ->update([
                        'commission_received'   => $balance
                    ]);
                CommissionAdminReportModel::insert([
                    'commission_id'     => $admincommission->id,
                    'commission_amount' => $incrementCommission
                ]);
            }
        }
    }
    public function updateAgencyCommissionByCommissionListID($listID, $amount, $condition, $commission, $percentage)
    {
        $autoCommission = $this->auto_commission($percentage, $amount, $condition, $commission);
        $balance        = (float) $autoCommission['balance'];
        $status         = $autoCommission['status'];
        $commissionList = CommissionItemModel::where('id', $listID)->first();
        if ($status == true && $commissionList->receive < $balance) {
            $incrementCommission = $balance - $commissionList->receive;
            CommissionItemModel::where('id', $listID)->update([
                'receive'   => $balance
            ]);
            FundsRegisterModel::where('user_id', CommissionItemModel::find($listID)->agency_id)
                ->update([
                    'balance'           => DB::raw("balance+$incrementCommission"),
                    'received​_balance' => DB::raw("received​_balance+$incrementCommission")
                ]);
            FundsTransactionModel::insert([
                'transaction_code'  => 'TS-' . date('dmYHis'),
                'operation_type'    => 3,
                'account_number'    => FundsRegisterModel::where('user_id', CommissionItemModel::find($listID)->agency_id)->first()->account_number,
                'amount'            => $incrementCommission,
                'balance'           => FundsRegisterModel::where('user_id', CommissionItemModel::find($listID)->agency_id)->first()->balance,
                'remark'            => \Globals::operationalDes($commissionList->com_list_sale_id),
                'user_log'          => Auth::user()->id,
                'status'            => 1,
                'request_date'      => date('Y-m-d H:i:s A'),
                'app_rej_date'      => date('Y-m-d H:i:s A')
            ]);
            CommissionItemReportModel::insert([
                'commission_item_id'    => $commissionList->id,
                'amount'                => $incrementCommission
            ]);
        }
    }
    public function approve_client_payment(Request $request)
    {
        $loanPaidIDs = $request->paid_id;
        $loanPaids = LoanPaidModel::whereIn('id', $loanPaidIDs)->get();
        foreach ($loanPaids as $paid) {
            ApprovePaymentModel::insert([
                'loanpaid_id'       => $paid->id,
                'loanprocess_id'    => $paid->loanpaid_processid,
                'payment'           => $paid->loanpaid_total,
                'principal'         => $paid->loanpaid_principle,
                'interest'          => $paid->loanpaid_interest,
                'penalty'           => $paid->loanpaid_penalty,
                'payment_fee'       => $paid->payment_fee,
                'invoice'           => $paid->loanpaid_invoice,
                'reference'         => $paid->loanpaid_reference,
                'paymentdate'       => $paid->loanpaid_payment,
                'date'              => date('Y-m-d'),
                'by_user_approve'   => Auth::user()->id,
                'by_user'           => $paid->loanpaid_by,
                'penalty_status'    => $paid->loanpaid_penalty_option,
                'bank_code'         => $paid->loandpaid_bankacc,
                'remark'            => $paid->loanpaid_remark,
                'des'               => $paid->loanpaid_des
            ]);
            $this->updateLoanPaymentByLoanPaidID($paid->id);
            $this->updateCommissionByLoanPaidID($paid->id);

            //Add Accounting

            $sale = LoanPaidModel::join("nso007_loan_process","nso007_loan_process.id","nso007_loan_paid.loanpaid_processid")
                ->join("nso007_loan","nso007_loan.id","nso007_loan_process.loanprocess_loanid")->select("nso007_loan.loan_saleid","nso007_loan_process.loanprocess_no","nso007_loan_paid.*")->where("nso007_loan_paid.id",$paid->id)->first();

            if($paid->loanpaid_principle>0){
                $j=JournalModel::select(DB::raw("Max(journal_tran_id) as last_num"))->first();
                $journal=new JournalModel;
                $journal->branch_id=\Globals::get_branch_and_project_id($sale->loan_saleid,'branch_id');
                $journal->journal_parentid=0;
                $journal->journal_type=1;
                $journal->journal_acccode=$paid->loandpaid_bankacc;
                $journal->voucher_type=1;
                $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
                $journal->journal_des=\Globals::PaymentBySaleID("", $sale->loan_saleid).$sale->loanprocess_no;
                $journal->journal_debit=$paid->loanpaid_principle+$paid->loanpaid_interest+$paid->loanpaid_penalty;
                $journal->journal_credit=0;
                $journal->journal_tran_id=$j->last_num+1;
                $journal->journal_transactiondate=date("Y-m-d",strtotime($sale->created_at));
                $journal->journal_paydate=date("Y-m-d",strtotime($sale->loanpaid_payment));
                $journal->journal_currency=1;
                $journal->transaction_type='Payment';
                $journal->journal_referenceid=$paid->id;
                $journal->journal_by_project=\Globals::get_branch_and_project_id($sale->loan_saleid,'project_id');
                $journal->journal_by=auth()->user()->id;
                $journal->save();

                $jj=JournalModel::select(DB::raw("Max(id) as last_id"))->first();

                $journal=new JournalModel;
                $journal->branch_id=\Globals::get_branch_and_project_id($sale->loan_saleid,'branch_id');
                $journal->journal_parentid=$jj->last_id;
                $journal->journal_type=1;
                $journal->journal_acccode=11003;
                $journal->voucher_type=1;
                $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
                $journal->journal_des=\Globals::PaymentBySaleID("", $sale->loan_saleid).$sale->loanprocess_no;
                $journal->journal_debit=0;
                $journal->journal_credit=$paid->loanpaid_principle;
                $journal->journal_tran_id=$j->last_num+1;
                $journal->journal_transactiondate=date("Y-m-d",strtotime($sale->created_at));
                $journal->journal_paydate=date("Y-m-d",strtotime($sale->loanpaid_payment));
                $journal->journal_currency=1;
                $journal->transaction_type='Payment';
                $journal->journal_referenceid=$paid->id;
                $journal->journal_by_project=\Globals::get_branch_and_project_id($sale->loan_saleid,'project_id');
                $journal->journal_by=auth()->user()->id;
                $journal->save();

                if($paid->loanpaid_interest>0){

                    $jj=JournalModel::select(DB::raw("Max(id) as last_id"))->first();

                    $journal=new JournalModel;
                    $journal->branch_id=\Globals::get_branch_and_project_id($sale->loan_saleid,'branch_id');
                    $journal->journal_parentid=$jj->last_id;
                    $journal->journal_type=4;
                    $journal->journal_acccode=41004;
                    $journal->voucher_type=1;
                    $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
                    $journal->journal_des=\Globals::PaymentBySaleID("", $sale->loan_saleid).$sale->loanprocess_no;
                    $journal->journal_debit=0;
                    $journal->journal_credit=$paid->loanpaid_interest;
                    $journal->journal_tran_id=$j->last_num+1;
                    $journal->journal_transactiondate=date("Y-m-d",strtotime($sale->created_at));
                    $journal->journal_paydate=date("Y-m-d",strtotime($sale->loanpaid_payment));
                    $journal->journal_currency=1;
                    $journal->transaction_type='Interest Payment';
                    $journal->journal_referenceid=$paid->id;
                    $journal->journal_by_project=\Globals::get_branch_and_project_id($sale->loan_saleid,'project_id');
                    $journal->journal_by=auth()->user()->id;
                    $journal->save();
                }

                if($paid->loanpaid_penalty>0){

                    $jj=JournalModel::select(DB::raw("Max(id) as last_id"))->first();

                    $journal=new JournalModel;
                    $journal->branch_id=\Globals::get_branch_and_project_id($sale->loan_saleid,'branch_id');
                    $journal->journal_parentid=$jj->last_id;
                    $journal->journal_type=4;
                    $journal->journal_acccode=400018;
                    $journal->voucher_type=1;
                    $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
                    $journal->journal_des=\Globals::PaymentBySaleID("", $sale->loan_saleid).$sale->loanprocess_no;
                    $journal->journal_debit=0;
                    $journal->journal_credit=$paid->loanpaid_penalty;
                    $journal->journal_tran_id=$j->last_num+1;
                    $journal->journal_transactiondate=date("Y-m-d",strtotime($sale->created_at));
                    $journal->journal_paydate=date("Y-m-d",strtotime($sale->loanpaid_payment));
                    $journal->journal_currency=1;
                    $journal->transaction_type='Penalty Payment';
                    $journal->journal_referenceid=$paid->id;
                    $journal->journal_by_project=\Globals::get_branch_and_project_id($sale->loan_saleid,'project_id');
                    $journal->journal_by=auth()->user()->id;
                    $journal->save();
                }
            }
            //End

        }
        LoanPaidModel::whereIn('id', $loanPaidIDs)->update(['loanpaid_approved' => 1]);

        return response()->json(['success' => true]);
    }





    public function get_view_cancel_client_payment($id)
    {

        $loan_id    = Crypt::decrypt($id);
        $branch_id  = \Branch::branch_id();
        $branch     = DB::select("SELECT * FROM nso007_branches where id = $branch_id;");

        $clientbooking = DB::select("
            SELECT 
                loanprocess_no,
                loanprocess_datepayment,
                nso007_loan_process.id,
                nso007_loan.id as loanid,
                loanprocess_paid,
                nso007_loan_process.updated_at,
                loanprocess_interest AS loanprocess_interest,
                loanprocess_principle AS loanprocess_principle,
                loanprocess_accprinciple AS loanprocess_accprinciple,
                loanprocess_amountpaid AS loanprocess_amountpaid,
                loanprocess_amountcredit AS loanprocess_amountcredit,
                loanprocess_accinterest AS loanprocess_accinterest,
                loanprocess_owe AS loanprocess_owe,
                loanprocess_owepay AS loanprocess_owepay,
                loanprocess_principleos AS loanprocess_principleos,
                nso007_loan.loan_commission_status AS commission_paid
            FROM nso007_loan_process
            INNER JOIN nso007_loan ON nso007_loan_process.loanprocess_loanid = nso007_loan.id
            
            WHERE nso007_loan.id = $loan_id 
            AND loanprocess_paid = 1
            AND nso007_loan_process.loanprocess_des IS NULL
            ORDER BY nso007_loan_process.id ASC
        ");
        $load = DB::select("SELECT  
                        nso007_project_stock.project_name,
                        nso007_item_stock.item_house,
                        nso007_item_stock.item_size_land,
                        nso007_loan.loan_amount,
                        nso007_client.client_fullname,
                        nso007_client.client_phone1,
                        nso007_sale_stock.sale_receive,
                        nso007_sale_stock.sale_discount,
                        CONCAT(users.user_firstname ,' ',users.user_lastname) AS agency,
                        date_format(nso007_loan.loan_firstpaydate, '%Y-%m-%d') AS do_date
                        FROM nso007_loan
                        LEFT JOIN nso007_sale_detail_stock ON nso007_loan.loan_saleid = nso007_sale_detail_stock.saledetail_saleid
                        LEFT JOIN nso007_sale_stock ON nso007_sale_detail_stock.saledetail_saleid = nso007_sale_stock.id
                        LEFT JOIN nso007_item_stock ON nso007_item_stock.id = nso007_sale_detail_stock.saledetail_itemid
                        LEFT JOIN nso007_project_stock ON nso007_project_stock.id = nso007_item_stock.item_projectid
                        LEFT JOIN nso007_client ON nso007_client.id = nso007_sale_stock.sale_clientid
                        LEFT JOIN users on users.id = nso007_sale_stock.agency_id
                        WHERE nso007_loan.id = $loan_id
                        GROUP BY nso007_sale_stock.id");
        //dd($clientbooking, $id);
        return view('administrator.client-payment.payment.view-cancel-payment', compact('branch', 'clientbooking', 'load'));
    }
    public function checkBeforeReject($arrayPaidID)
    {
        $paidToApprove = LoanPaidModel::whereIn('id', $arrayPaidID)->get();
        $arrayPaidCheck = [];
        foreach ($paidToApprove as $key => $paid) {
            $NextPaid = LoanPaidModel::where(function ($query) use ($arrayPaidID, $paid) {
                $query->whereNotIn('id', $arrayPaidID);
                $query->where('id', '>', $paid->id);
            })->first();
            if (!empty($NextPaid)) {
                array_push($arrayPaidCheck, $NextPaid);
            }
        }
        if (count($arrayPaidCheck) > 0) {
            return false;
        } else {
            return true;
        }
    }
    public function rejectPayment(Request $request)
    {
        $paidIDs = $request->paid_id;
        arsort($paidIDs);
        $check = $this->checkBeforeReject($paidIDs);
        $loanPaidtoReject = LoanPaidModel::whereIn('id', $paidIDs)->whereNull('loanpaid_approved')->orderBy('id', 'desc')->get();
        foreach ($loanPaidtoReject as $key => $loanPaidForDelete) {
            $maxID = CancelPaidModel::max('id') + 1;
            DB::table('nso007_loan_paid_cancel')->insert([
                'loanprocess_id'    => $loanPaidForDelete->loanpaid_processid,
                'principal'         => $loanPaidForDelete->loanpaid_principle,
                'interest'          => $loanPaidForDelete->loanpaid_interest,
                'payment'           => $loanPaidForDelete->loanpaid_total,
                'penalty'           => $loanPaidForDelete->loanpaid_penalty,
                'datepayment'       => $loanPaidForDelete->loanpaid_payment,
                'date'              => date('Y-m-d'),
                'acc_code'          => $loanPaidForDelete->loandpaid_bankacc,
                'user_id'           => Auth::user()->id,
                'reference'         => $loanPaidForDelete->loanpaid_reference,
                'receipt_no'        => $loanPaidForDelete->loanpaid_invoice,
                'description'       => $loanPaidForDelete->loanpaid_des
            ]);
            LoanProcessModel::where('id', $loanPaidForDelete->loanpaid_processid)->update([
                'loanprocess_interestpay'   => DB::raw("loanprocess_interestpay+$loanPaidForDelete->loanpaid_interest"),
                'loanprocess_principlepay'  => DB::raw("loanprocess_principlepay+$loanPaidForDelete->loanpaid_principle"),
                'loanprocess_owepay'        => DB::raw("loanprocess_owepay+$loanPaidForDelete->loanpaid_total"),
                'loanprocess_paid'          => null,
                'loanprocess_datepaid'      => null,
                'loanprocess_activity'      => null,
                'loanprocess_by'            => null
            ]);
            $this->checkMultiplePayByLoanProcessID($loanPaidForDelete->loanpaid_processid);
            $this->checkPenaltyofPaymentByLoanPaidID($loanPaidForDelete->id);
            LoanPaidModel::destroy($loanPaidForDelete->id);
        }
        LoanPaidModel::whereIn('id', $paidIDs)->delete();
        echo json_encode("success");
        /*if ($check == false) {
            $return = "error";
            echo json_encode($return);
        } else {
            
        }*/
    }
    public function checkPenaltyofPaymentByLoanPaidID($id)
    {
        $loanPaid       = LoanPaidModel::find($id);
        $loanProcess    = LoanProcessModel::find($loanPaid->loanpaid_processid);
        LoanProcessModel::where([['loanprocess_loanid', $loanProcess->loanprocess_loanid], ['loanprocess_no', $loanProcess->loanprocess_no + 1]])->update([
            'penalty_paid'  => 0
        ]);
        if ($loanProcess->loanprocess_paid == 2) {
            $previousPaid = LoanPaidModel::where([['loanpaid_processid', $loanProcess->id], ['id', '<', $id]])->orderBy('id', 'desc')->first();
            if (!empty($previousPaid) && $previousPaid->loanpaid_penalty_option == 2) {
                LoanProcessModel::where('id', $loanPaid->loanpaid_processid)->update([
                    'penalty_paid'  => $previousPaid->loanpaid_penalty
                ]);
            } else {
                LoanProcessModel::where('id', $loanPaid->loanpaid_processid)->update([
                    'penalty_paid'  => 0
                ]);
            }
        } else {
            $previousProcess = LoanProcessModel::where([['loanprocess_loanid', $loanProcess->loanprocess_loanid], ['loanprocess_no', $loanProcess->loanprocess_no - 1]])->first();
            if (!empty($previousProcess)) {
                $paidPrevious       = LoanPaidModel::where('loanpaid_processid', $previousProcess->id)->orderBy('id', 'desc')->first();

                if (!empty($paidPrevious) && $paidPrevious->loanpaid_penalty_option == 2) {

                    LoanProcessModel::where('id', $loanPaid->loanpaid_processid)->update([
                        'penalty_paid'  => $paidPrevious->loanpaid_penalty
                    ]);
                }
            } else {
                LoanProcessModel::where('id', $loanPaid->loanpaid_processid)->update([
                    'penalty_paid'  => 0
                ]);
            }
        }
    }
    public function rollbackPenalty($paid_id)
    {
    }
    public function checkMultiplePayByLoanProcessID($id)
    {
        $loanPaid   = LoanPaidModel::where('loanpaid_processid', $id)->orderBy('id', 'desc')->first();
        $datePaid   = null;
        $byUser     = null;
        if (!empty($loanPaid)) {
            $datePaid    =  $loanPaid->loanpaid_createdate;
            $byUser      = $loanPaid->loanpaid_by;
        }
        LoanProcessModel::where([['id', $id], ['loanprocess_owepay', '!=', DB::raw('loanprocess_owe')]])
            ->update([
                'loanprocess_paid'      => 2,
                'loanprocess_datepaid'  => $datePaid,
                'loanprocess_activity'  => 2,
                'loanprocess_by'        => $byUser
            ]);
    }
    public function rejectClientPayment(Request $request)
    {
        if ($request->ajax()) {
            $paid_pending = DB::table('nso007_loan_paid_cancel')->join('nso007_loan_process', 'nso007_loan_process.id', 'nso007_loan_paid_cancel.loanprocess_id')
                ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
                ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
                ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
                ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
                ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
                ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
                ->join('users', 'users.id', 'nso007_loan_paid_cancel.user_id')
                // ->join('nso007_bank_agency as chart', 'chart.id', 'nso007_loan_paid_cancel.acc_code')
                ->select(
                    'nso007_loan_paid_cancel.id as paid_id',
                    'nso007_loan_paid_cancel.principal',
                    'nso007_loan_paid_cancel.interest',
                    'nso007_loan_paid_cancel.payment',
                    'nso007_loan_paid_cancel.receipt_no',
                    'nso007_loan_paid_cancel.reference',
                    'nso007_loan_paid_cancel.created_at',
                    'nso007_loan_paid_cancel.description',
                    'nso007_loan_paid_cancel.acc_code',
                    'nso007_loan_paid_cancel.datepayment',
                    'nso007_project_stock.project_name',
                    'nso007_client.client_fullname',
                    // 'chart.name as bank',
                    DB::raw('CONCAT(users.user_firstname," ",users.user_lastname) As cashier')
                )
                ->selectRaw('GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as item_house')
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->groupBy('nso007_loan_paid_cancel.id')
                ->get();
            return Datatables::of($paid_pending)
                ->addIndexColumn()
                ->editColumn('payment', function ($paid_pending) {
                    return '$' . number_format($paid_pending->payment, 2);
                })
                ->addColumn('loanpaid_principle', function ($paid_pending) {
                    return '$ ' . number_format($paid_pending->principal, 2);
                })
                ->addColumn('loanpaid_total', function ($paid_pending) {
                    return '$ ' . number_format($paid_pending->payment, 2);
                })
                ->addColumn('datepayment', function ($paid_pending) {
                    return date('d-m-Y', strtotime($paid_pending->datepayment));
                })
                ->addColumn('check_id', function ($paid_pending) {
                    return '<label class="checkbox-list">
                          <input type="checkbox" class="checkbox" data-id="' . $paid_pending->paid_id . '">
                          <span class="checkmark"></span>
                    </label>';
                })
                ->addColumn('bank',function($paid_pending)
                {
                    if($paid_pending->acc_code=="11002") return "Cash In Bank";
                    if($paid_pending->acc_code =="11001") return "Cash On Hand";
                })
                ->rawColumns(['check_id' => 'check_id'])->make(true);
        }
        return view('administrator.client-payment.payment.reject-client-payment');
    }
    public function printrejectedClientPayment()
    {
        $paid_rejected = DB::table('nso007_loan_paid_cancel')
            ->join('nso007_loan_process', 'nso007_loan_process.id', 'nso007_loan_paid_cancel.loanprocess_id')
            ->join('nso007_loan', 'nso007_loan.id', 'nso007_loan_process.loanprocess_loanid')
            ->join('nso007_sale_stock', 'nso007_sale_stock.id', 'nso007_loan.loan_saleid')
            ->join('nso007_client', 'nso007_client.id', 'nso007_sale_stock.sale_clientid')
            ->join('nso007_sale_detail_stock', 'nso007_sale_detail_stock.saledetail_saleid', 'nso007_sale_stock.id')
            ->join('nso007_item_stock', 'nso007_item_stock.id', 'nso007_sale_detail_stock.saledetail_itemid')
            ->join('nso007_project_stock', 'nso007_project_stock.id', 'nso007_item_stock.item_projectid')
            ->join('users', 'users.id', 'nso007_loan_paid_cancel.user_id')
            // ->join('nso007_bank_agency as chart', 'chart.id', 'nso007_loan_paid_cancel.acc_code')
            ->select(
                'nso007_loan_paid_cancel.id as paid_id',
                'nso007_loan_paid_cancel.principal',
                'nso007_loan_paid_cancel.reference',
                'nso007_loan_paid_cancel.interest',
                'nso007_loan_paid_cancel.payment',
                'nso007_loan_paid_cancel.receipt_no',
                'nso007_loan_paid_cancel.description',
                'nso007_loan_paid_cancel.created_at as date',
                'nso007_project_stock.project_name',
                'nso007_client.client_fullname',
                // 'chart.name as bank',
                'nso007_loan_paid_cancel.acc_code',
                'nso007_loan_paid_cancel.datepayment',
                DB::raw('CONCAT(users.user_firstname," ",users.user_lastname) As cashier')
            )
            ->selectRaw('GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as itemname')
            ->when(empty(\Branch::branch_key()), function ($query) {
                $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
            })
            ->groupBy('nso007_loan_paid_cancel.id')
            ->get();

        $branch = BranchModel::where('id', \Branch::branch_id())->first();
        return view('administrator.client-payment.report.reject-client-payment', compact('paid_rejected', 'branch'));
    }
    public function commission_detail($sale_id)
    {
        $commission = DB::table('nso007_agency_commission_list as commission')
            ->join('users', 'users.id', 'commission.agency_id')
            ->select(DB::raw('CONCAT(user_firstname," ",user_lastname) as agency'), 'users.agency_type', 'commission.*')
            ->where('com_list_sale_id', $sale_id)
            ->orderBy('commission.agency_level', 'asc')
            ->get();
        return DataTables::of($commission)
            ->addIndexColumn()
            ->editColumn('commission', function ($commission) {
                return "$ " . number_format($commission->amount, 2);
            })
            ->editColumn('receive', function ($commission) {
                return "$ " . number_format($commission->receive, 2);
            })
            ->editColumn('level', function ($c) {
                return $c->agency_level;
            })
            ->make(true);
    }
    public function multiexplode($delimiters, $string)
    {

        $ready = str_replace($delimiters, $delimiters[0], $string);
        $launch = explode($delimiters[0], $ready);
        return  $launch;
    }
    public function auto_commission($percentage, $commission_amount, $condit, $commis)
    {
        $result = ['status' => false, 'balance' => 0];
        $condition_commission = [];
        $condition  = explode('/', $condit);
        $commission = explode('/', $commis);
        foreach ($condition as $key => $condi) {
            $array1 = [$condi, $commission[$key]];
            array_push($condition_commission, $array1);
        }

        $smallest_array_value   = floatval($condition_commission[0][0]);
        $smallest_array         = $condition_commission[0];
        $set_of_smallest_array  = [];
        array_push($set_of_smallest_array, $condition_commission[0]);

        foreach ($condition_commission as $condi_commi) {

            if (floatval($condi_commi[0]) < $smallest_array_value) {
                $smallest_array_value   = floatval($condi_commi[0]);
                $smallest_array         = $condi_commi;
            }
            if (floatval($condi_commi[0]) <= $percentage) {
                array_push($set_of_smallest_array, $condi_commi);
            }
        }

        if ($percentage >= floatval($smallest_array[0])) {
            $result['status'] = true;

            $biggest_smallest_array_value   = $set_of_smallest_array[0][0];

            $biggest_smallest_array         = $set_of_smallest_array[0];

            foreach ($set_of_smallest_array as $small_set) {

                if ($small_set[0] >= floatval($biggest_smallest_array_value)) {
                    $biggest_smallest_array_value = $small_set[0];
                    $biggest_smallest_array = $small_set;
                }
            }

            $balance_condition  = floatval($biggest_smallest_array[0]);
            $balance_commission = floatval($biggest_smallest_array[1]);
            $max_condition      = floatval(max($condition));

            if ($balance_commission == 100) {

                $balance = $commission_amount;
            } else {
                $balance = ($commission_amount * $balance_commission) / 100;
            }
            $result['balance'] = $balance;
        }
        return $result;
    }
    public function change_item($loan_id='')
    {

            $loan_id = Crypt::decrypt($loan_id);

            $data_loan = DB::table("nso007_loan")->where("id",$loan_id)->first();

            $data_detail_sale = DB::table('nso007_sale_detail_stock')->where("saledetail_saleid",$data_loan->loan_saleid)->get();

            $cart=array();

            foreach ($data_detail_sale as $key=>$value){
                $data_item = DB::table('nso007_item_stock')->where("id",$value->saledetail_itemid)->first();
                $cart[$data_item->id] = [
                    'item_id'=>$data_item->id,
                    'item_name'=>$data_item->item_house,
                    'item_cost'=>$data_item->item_cost,
                    'item_price'=>$data_item->item_price,
                    'project'=>optional(ProjectModel::find($data_item->item_projectid))->project_name,
                    'category'=>optional(CategoryModel::find($data_item->item_categoryid))->category_name,
                    'subcategory'=>optional(SubCategoryModel::find($data_item->item_sub_categoryid))->sub_category_name
                ];
            }

            Session::put("change_item_cart",$cart);

        return view('administrator.client-payment.list.change-item');
    }
    public function get_item_for_change(Request $request){
            $item_data = ItemModel::where("item_stockstatus",1)->where("available",1)->get();
            return Datatables::of($item_data)
            ->addIndexColumn()
            ->setRowId('id')
            ->editColumn('item_name',function($item_data){
                return $item_data->item_house;
            })
            ->editColumn('item_cost',function($item_data){
                return "$".$item_data->item_cost;
            })
            ->editColumn('item_price',function($item_data){
                return "$".$item_data->item_price;
            })
            ->editColumn('project',function($item_data){
                return optional(ProjectModel::find($item_data->item_projectid))->project_name;
            })
            ->editColumn('category',function($item_data){
                return optional(CategoryModel::find($item_data->item_categoryid))->category_name;
            })
            ->editColumn('sub_category',function($item_data){
                return optional(SubCategoryModel::find($item_data->item_sub_categoryid))->sub_category_name;
            })
            ->addColumn('action', function ($item_data){
                $button ="";
                if(Session::has('change_item_cart')){
                    $cart = Session::get('change_item_cart');
                    foreach ($cart as $key => $value) {
                        if(isset($cart[$item_data->id])){

                            $button="<button class='btn btn-danger btn-xs' disabled type='button' title='Add item to cart'><i class='glyphicon glyphicon-shopping-cart'></i></button>";
                        }
                        else{
                             $button="<button class='btn btn-info btn-xs' onclick='Item_change_add_to_cart(this,".$item_data->id.")' type='button' title='Add item to cart'><i class='glyphicon glyphicon-shopping-cart'></i></button>";
                        }
                    }
                    
                }
                else{
                    $button.= "<button class='btn btn-info btn-xs' onclick='Item_change_add_to_cart(this,".$item_data->id.")' type='button' title='Add item to cart'><i class='glyphicon glyphicon-shopping-cart'></i></button>";
                }
                return $button;
                
            })
            ->rawColumns(["action"=>"action"])
            ->make(true);
    }
    public function change_item_add_to_cart(Request $request)
    {
        $item_id = $request->item_id;
        $data_item = ItemModel::find($item_id);
        $cart=array();

        if(Session::has('change_item_cart')){
            $cart = Session::get('change_item_cart');
        }

        $cart[$data_item->id] = [
            'item_id'=>$data_item->id,
            'item_name'=>$data_item->item_house,
            'item_cost'=>$data_item->item_cost,
            'item_price'=>$data_item->item_price,
            'project'=>optional(ProjectModel::find($data_item->item_projectid))->project_name,
            'category'=>optional(CategoryModel::find($data_item->item_categoryid))->category_name,
            'subcategory'=>optional(SubCategoryModel::find($data_item->item_sub_categoryid))->sub_category_name
        ];

        Session::put('change_item_cart',$cart);

        return json_encode($cart);
    }
    public function change_item_remove_to_cart(Request $request)
    {
        $item_id = $request->item_id;
        $data_item = ItemModel::find($item_id);
        $cart=array();
        $cart = Session::get('change_item_cart');
        Arr::forget($cart,$item_id);
        Session::put('change_item_cart',$cart);
        return json_encode($cart);
    }
    public function change_client(Request $request)
    {
        $change_client_id = $request->client_fullname;
        $loan_id = $request->loan_id;
        $data=LoanModel::where('id',$loan_id)->first();

        $sale_id=$data->loan_saleid;

        $change_client = new  LoanChangeClientModel;

        $change_client->change_loan_createdate = date('Y-m-d',strtotime($request->date));
        $change_client->change_loan_clientid = $data->loan_clientid;
        $change_client->change_loan_new_clientid =$change_client_id;
        $change_client->change_loan_client_relation_id = $data->loan_client_relation_id;
        $change_client->change_loan_amount = $data->loan_amount;
        $change_client->change_loan_discount = $data->loan_discount;
        $change_client->change_loan_amountos = $data->loan_amountos;
        $change_client->change_loan_amount_payment = $data->loan_amount_payment;
        $change_client->change_loan_amount_loan = $data->loan_amount_loan;
        $change_client->change_loan_interest = $data->loan_interest;
        $change_client->change_loan_duration = $data->loan_duration;
        $change_client->change_loan_term = $data->loan_term;
        $change_client->change_loan_payment_step = $data->loan_payment_step;
        $change_client->change_loan_disbursement = $data->loan_disbursement;
        $change_client->change_loan_firstpaydate = $data->loan_firstpaydate;
        $change_client->change_loan_agentid = $data->loan_agentid;
        $change_client->change_loan_staff =Auth::user()->id;
        $change_client->change_loan_for = $data->loan_for;
        $change_client->change_loan_downpayment = $data->loan_downpayment;
        $change_client->change_discount_commis = $data->discount_commis;
        $change_client->change_loan_type = $data->loan_type;
        $change_client->change_loan_status = $data->loan_status;
        $change_client->change_loan_saleid = $data->loan_saleid;
        $change_client->loan_inv_contract = $data->loan_inv_contract;

        $change_client->save();

        DB::table('nso007_sale_stock')
            ->where('nso007_sale_stock.id',$sale_id)
            ->update([
                'nso007_sale_stock.sale_clientid'  => $change_client_id,
            ]);
        DB::table('nso007_loan')
            ->where('nso007_loan.id',$loan_id)
            ->update([
                'nso007_loan.loan_clientid'  => $change_client_id,
            ]);
        DB::table('nso007_agency_commission')
            ->where('nso007_agency_commission.sale_id',$sale_id)
            ->update([
                'nso007_agency_commission.client_id'  => $change_client_id,
            ]);
        DB::table('nso007_client')
            ->where('nso007_client.id',$data->loan_clientid)
            ->update([
                'nso007_client.client_contract'  => 1,
            ]);
        DB::commit();
        \LogActivity::addToLog('Change Client successfully!');
        return redirect()->route('sale-list.index')->with('success','Change Client successfully!');

    }
    public  function get_data_client_loan_bank(Request $request)
    {
        if($request->ajax()){
            $data = DB::table('nso007_sale_bank_loan_procedures')
                ->join("nso007_loan","nso007_sale_bank_loan_procedures.loan_id","nso007_loan.id")
                ->join('nso007_client',"nso007_client.id","nso007_loan.loan_clientid")
                ->join("nso007_sale_detail_stock","nso007_sale_detail_stock.saledetail_saleid","nso007_loan.loan_saleid")
                ->join("nso007_item_stock","nso007_sale_detail_stock.saledetail_itemid","nso007_item_stock.id")
                ->join("nso007_project_stock","nso007_project_stock.id","nso007_item_stock.item_projectid")
                ->join("nso007_category_stock","nso007_category_stock.id","nso007_item_stock.item_categoryid")
                ->join("nso007_sub_category_stock","nso007_sub_category_stock.id","nso007_item_stock.item_sub_categoryid")
                ->select(
                    "nso007_loan.id as loan_id",
                    "nso007_client.client_firstname",
                    "nso007_client.client_lastname",
                    "nso007_client.client_gender",
                    "nso007_client.client_dob",
                    "nso007_client.client_phone1",
                    "nso007_sale_bank_loan_procedures.amount",
                    "nso007_project_stock.project_name",
                    "nso007_category_stock.category_name",
                    "nso007_sub_category_stock.sub_category_name",
                    DB::raw("GROUP_CONCAT(DISTINCT nso007_item_stock.item_house) as item_names")
                )
                ->groupBy("loan_id")
                ->when(empty(\Branch::branch_key()), function ($query) {
                    $query->where('nso007_project_stock.branch_id', \Branch::branch_id());
                })
                ->get();
            return Datatables::of($data)
                ->addIndexColumn()
                ->editColumn('client_name',function ($data){
                    return $data->client_firstname." ".$data->client_lastname;
                })
                ->editColumn("client_gender",function ($data){
                    return $data->client_gender=="Male"?"ប្រុស":"ស្រី";
                })
                ->editColumn("client_dob",function ($data){
                    return !empty($data->client_dob)?date("d-m-Y",strtotime($data->client_dob)):"";
                })
                ->editColumn("client_phone",function ($data){
                    return $data->client_phone1;
                })
                ->editColumn("amount_bank",function ($data){
                    return "$".number_format($data->amount,2);
                })
                ->editColumn("item_names",function ($data){
                    return $data->item_names;
                })
                ->editColumn("project_name",function ($data){
                    return $data->project_name;
                })
                ->editColumn("category_name",function ($data){
                    return $data->category_name;
                })
                ->editColumn("sub_category_name",function ($data){
                    return $data->sub_category_name;
                })
                ->editColumn("action",function ($data){
                    $button ="";
                    if(Gate::check('Client-Loan-Bank-Upload-Document'))
                    {
                        $button .='<a style="color:white;margin-left:0;" href="javascript:void(0)" class="button_success btn-circle" onclick="show_upload('.$data->loan_id.')" title="Upload Document">
                                    <i class="glyphicon glyphicon-open"></i>
                                </a>';
                    }
                    if(Gate::check('Client-Loan-Bank-Download-Document'))
                    {
                        $button .= '<a style="color:white;margin-left:0;" href="javascript:void(0)" onclick="show_document('.$data->loan_id.')" class="button_info btn-circle" title="Download Document">
                                    <i class="glyphicon glyphicon-save"></i>
                                </a>';
                    }
                    if(Gate::check('Client-Loan-Bank-Check-Follow-up'))
                    {
                        $button .= '<a style="color:white;margin-left:0;" href="javascript:void(0)" onclick="edit_follow_up('.$data->loan_id.')" class="button_success btn-circle" title=" Check Follow up">
                                    <i class="glyphicon glyphicon-check"></i>
                                </a>';
                    }
                    if(Gate::check('Client-Loan-Bank-Show-Follow-up'))
                    {
                        $button .= '<a style="color:white;margin-left:0;" href="javascript:void(0)" onclick="show_follow_up('.$data->loan_id.')" class="button_success btn-circle" title="Show Follow up">
                                    <i class="fa fa-eye"></i>
                                </a>';
                    }
                    
                    return $button;
                })
                ->make(true);
        }
        \LogActivity::addToLog('client_loan_with_bank index!');
        return view("administrator.client_loan_with_bank.index");
    }
    public  function get_follow_up(Request $request)
    {
        $data = DB::table('nso007_sale_bank_loan_procedures')->where("loan_id",$request->loan_id)->get();
        return $data;
    }
    public function save_follow_up_status(Request $request){
//        dd($request->all());
        if(isset($request->follow_id_data)){
            foreach ($request->follow_id_data as $key=>$value){
                if($request->type[$key]=="checked"){
                    DB::table('nso007_sale_bank_loan_procedures')->where("id",$value)->update(['status'=>1]);
                }else{
                    DB::table('nso007_sale_bank_loan_procedures')->where("id",$value)->update(['status'=>0]);
                }
            }
        }
        \LogActivity::addToLog('client_loan_with_bank update status successfully!');
        return \back()->with("successfully !");
    }
    public function save_upload_image(Request $request)
    {
        $this->validate($request, [
            'file_name' => 'required',
            'loan_id'=>'required'
        ]);

        // menyimpan data file yang diupload ke variabel $file

        $file_name = "";
        if($request->hasFile('file_name')){
            $file = $request->file('file_name');
            foreach ($file as $key=>$value){
                $tujuan_upload = public_path().'\assets\document';
                $value->move($tujuan_upload,$value->getClientOriginalName());
                $file_name = $value->getClientOriginalName();
                DB::table('nso007_loan_file_document')->insert(['loan_id'=>$request->loan_id,'type'=>"Bank-Procedures",'file_name'=>$file_name]);
            }
        }

        \LogActivity::addToLog('Upload Image successfully!');
        return \back()->with("success","successfully");
    }
    public function show_upload_image($loan_id)
    {
        $data = DB::table('nso007_loan_file_document')->where("loan_id",$loan_id)->where('type','Bank-Procedures')->get();
        return json_encode($data);
    }
    public function transfer_ownership(Request $request)
    {
        $client_id = $request->transfer_client_id;
        $loan_id = $request->transfer_loan_id;
        $date = $request->date_transfer;
        $remark = $request->remark;
        DB::table("nso007_loan")->where("id",$loan_id)->update(['transfer_ownership_status'=>1,'date_transfered_ownership'=>date("Y-m-d",strtotime($date)),'remark_transfered_ownership'=>$remark]);
        return redirect()->route('client-payment-list.show',Crypt::encrypt($client_id))->with("success","Transfer successfully!");
    }
    public function set_date_transfer_ownership(Request $request)
    {
        $client_id = $request->transfer_client_id;
        $loan_id = $request->transfer_loan_id;
        $date = $request->date_transfer;
        $remark = $request->remark;
        DB::table("nso007_loan")->where("id",$loan_id)->update(['transfer_ownership_status'=>0,'date_transfer_ownership'=>date("Y-m-d",strtotime($date)),'remark_transfer_ownership'=>$remark]);
        return redirect()->route('client-payment-list.show',Crypt::encrypt($client_id))->with("success","Transfer successfully!");
    }
}
