<?php

namespace App\Http\Controllers\Administrator;

use App\Classes\AgencyCommission;

use Illuminate\Http\Request;
use App\Http\Controllers\Controller;
use Auth;
use DB;
use App\LoanProcessModel;
use App\CommissionModel;
use App\CommissionItemModel;
use App\CommissionItemReportModel;
use App\ClientRelationshipModel;
use App\SaleStockDetailModel;
use App\ItemModel;
use App\SaleStockModel;
use App\LoanModel;
use App\JournalModel;
use App\ClientModel;
use App\FundsRegisterModel;
use App\FundsTransactionModel;
use App\CancelDepositModel;
use App\AppEditPOS;
use App\ApproveInstalmentModel;
use App\ApproveInstalmentStepModel;
use App\ApprovePaymentModel;
use App\BorderModel;
use App\DepositModel;
use App\Helpers\Branch;
use App\ProjectModel;
use Illuminate\Support\Facades\Crypt;
use App\ProvinceModel;
use App\RelationshipStatus;
use App\User;
use App\BranchModel;
use App\DiscountModel;

class InstallmentController extends Controller
{
    function __construct()
    {
        $this->middleware('permission:Deposit-Cancel', ['only' => ['agree_cancel']]);
        $this->middleware('permission:Deposit-Make-Installment', ['only' => ['create','store']]);
        // $this->middleware('permission:Installment-Edit', ['only' => ['show']]);
        $this->middleware('permission:Deposit-Approve-Reject-Installment', ['only' => ['approve_store_instalment']]);
        $this->middleware('permission:Deposit-Approve-Reject-Cancel-Deposit', ['only' => ['approve_agree_cancel','reject_agree_cancel']]);
    }
    public function index()
    {
    }
    public static function return_payment_detail_id($data_id,$field){
        if(!empty($data_id)){
            $data = DB::table("nso007_payment_policy_detail")->where($field,"LIKE",$data_id)->get();
            $data_key_id = [];
            foreach($data->toArray as $key=>$value){
                $data_explode = explode("/",$value[$field]);
                foreach($data_explode as $i=>$val){
                    if($data_id == $val){
                        $data_key_id[] = $value['id'];
                    }
                }
            }
            return $data_key_id;
        } 
        return 0;
    }
    public function create($client_id = null, $sale_id = null)
    { 
        $client_decrypt_id  = Crypt::decrypt($client_id);
        $sale_decrypt_id    = Crypt::decrypt($sale_id);

        $payment_policies = DB::table('nso007_payment_policy')->pluck("name","id")->prepend("Please Select","");

        $data['client_id']     = $client_decrypt_id;
        $data['sale_id']    = $sale_decrypt_id;
        $sale_policy_payment = DB::table("nso007_sale_policy_payment")
            ->leftjoin('nso007_payment_policy_detail as po_detail', 'po_detail.id', 'nso007_sale_policy_payment.policy_detail_id')
            ->leftjoin('nso007_payment_policy as pa_po', 'pa_po.id', 'nso007_sale_policy_payment.payment_policy_id')
            ->select('po_detail.name as po_detail_name','pa_po.name as pa_po_name','po_detail.id')
            ->where("sale_id",$sale_decrypt_id)
            ->first();

        $client = ClientModel::find($client_decrypt_id);
        $provinces = ProvinceModel::select(
            DB::raw('CONCAT(province_id," ",province_en," ",province_km) AS province_mixed'),
            'province_id'
        )
            ->pluck('province_mixed', 'province_id')
            ->prepend('Please Select', '');

        $data['data']         = DB::select("
    		SELECT sale_discount, sale_amount, sale_remain, sale_receive, agency_id,close_sale_id
    		FROM nso007_sale_stock 
    		WHERE id = $sale_decrypt_id
        ");
        
        $sale_policy_payment = DB::table('nso007_sale_policy_payment')->where("sale_id",$sale_decrypt_id)->first();
        if(!empty($sale_policy_payment)){
            $payment_policy_detail = DB::table('nso007_payment_policy_detail')
                                    ->where('policy_id',$sale_policy_payment->payment_policy_id)
                                    ->pluck('name','id');
        }
        else{
            $payment_policy_detail = [];
        }

        $data['client_name'] = ClientModel::where('id', $client_decrypt_id)->select(DB::raw('concat(client_firstname," ",client_lastname) As client_fullname'))->first();
        $discount = DiscountModel::pluck('name', 'id')->prepend('Select Discount', '');
        $relationship   = ClientRelationshipModel::select('id', DB::raw('CONCAT(clientship_phone1, " | ", clientship_fullname) AS clientship_fullname'))->pluck('clientship_fullname', 'id')->prepend('Please Select', '');
        $parent_id = User::select(DB::Raw('GROUP_CONCAT(id) as ids'))->where([['role', 'Agencies'], ['parent_id', 0]])->first()->ids;
        $agencyLV2 = User::select(DB::raw('GROUP_CONCAT(id) as ids'))->whereIn('parent_id', explode(',', $parent_id))->first()->ids;
        
        $agency         = User::select('id', DB::raw('CONCAT(user_firstname, " ", user_lastname) AS agency_name'))
            ->where([['user_status', 2]])
            ->where(function ($query) use ($agencyLV2) {
                $query->orwhereIn('parent_id', explode(',', $agencyLV2));
                $query->orwhere('agency_type', 1);
            })
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
        
        $closesale = User::where('role', 'Agencies')
            ->select(DB::raw('CONCAT(user_firstname," ",user_lastname) As closesale'), 'id')
            ->pluck('closesale', 'id')->prepend('Select Close Sale', '');

        $deposit_amount = 0;
        $sale_schedule = optional(DB::table('nso007_deposit_schedule')->where('type',0)->where('ds_saleid',$sale_decrypt_id)->select(DB::raw("SUM(ds_amount) as total_deposit"))->first())->total_deposit;
        if(!empty($sale_schedule)) $deposit_amount = $sale_schedule;

        $down_payments = DB::table('nso007_deposit_schedule')->where('type','!=',0)->where('ds_saleid',$sale_decrypt_id)->get();
            
        return view('administrator.installment.create', compact('sale_policy_payment','data', 'discount', 'client', 'provinces', 'agency','closesale'), ['relationship' => $relationship,'payment_policies'=>$payment_policies,'sale_policy_payment'=>$sale_policy_payment,'payment_policy_detail'=>$payment_policy_detail,'deposit_amount'=>$deposit_amount,'down_payments'=>$down_payments]);
    }
    public function getdpsAmountBySaleID($id)
    {
        //return DepositModel::where('deposit_saleid', $id)->select(DB::Raw('SUM(deposit_amount) As amount'))->first()->amount;
        return DB::table('nso007_deposit_schedule')->where('type',0)->where('ds_saleid', $id)->select(DB::Raw('SUM(ds_amount) As amount'))->first()->amount;
    }
    public function store(Request $request)
    {
        // dd($request->all());
        $this->validate($request,[
            'salename_id'=>'required',
            'clientname_id'=>'required',
        ]);

        $sale_stock_id = $request->salename_id;
        DB::table('nso007_sale_stock')->where('id', $sale_stock_id)->update([
            'status_pos_approve_type' => 0
        ]);
        $approve = ApproveInstalmentModel::where('loan_saleid', $sale_stock_id)->first();
        if(!empty($approve)){
            DB::table('nso007_approve_instalment_step')->where('approve_instalment_id', $approve->id)->delete();
            DB::table('nso007_approve_instalment')->where('loan_saleid', $sale_stock_id)->delete();
            DB::table('nso007_loan_installment_change_rate_for_approve')->where('loan_id', $approve->id)->delete();
            DB::table('nso007_loan_installment_with_bank_for_approve')->where('loan_id', $approve->id)->delete();
        }

        // if(!empty($request->itempro_payamount[0])&&$request->itempro_payamount[0]>0) dd("1");
        // if(!empty($request->loan_amount)&&$request->loan_amount>0&&!empty($request->loan_duration)&& $request->loan_duration>0 && !empty($request->loan_interest)&&$request->loan_interest>=0&&$request->first_loan_condition==1) dd("2");
        // if($request->change_rate_condition==1&&!empty($request->loan_amount_second)&&$request->loan_amount_second>0&&!empty($request->loan_duration_second)&&$request->loan_duration_second>0&&!empty($request->loan_interest_second)&&$request->loan_interest_second>=0) dd("3");
        // if((!empty($request->itempro_payamount[0])&&$request->itempro_payamount[0]>0) 
        // ||(!empty($request->loan_amount)&&$request->loan_amount>0&&!empty($request->loan_duration)&& $request->loan_duration>0 && !empty($request->loan_interest)&&$request->loan_interest>=0&&$request->first_loan_condition==1) 
        // ||($request->change_rate_condition==1&&!empty($request->loan_amount_second)&&$request->loan_amount_second>0&&!empty($request->loan_duration_second)&&$request->loan_duration_second>0&&!empty($request->loan_interest_second)&&$request->loan_interest_second>=0)
        // ||($request->itempro_date_bank[0]>0)
        // ) dd("OK");
        // else dd("NO");

            
        if((!empty($request->itempro_payamount[0])&&$request->itempro_payamount[0]>0)
            ||(!empty($request->loan_amount)&&$request->loan_amount>0&&!empty($request->loan_duration)&& $request->loan_duration>0 && isset($request->loan_interest)&&$request->loan_interest>=0&&$request->first_loan_condition==1)
            ||($request->change_rate_condition==1&&!empty($request->loan_amount_second)&&$request->loan_amount_second>0&&!empty($request->loan_duration_second)&&$request->loan_duration_second>0&&isset($request->loan_interest_second)&&$request->loan_interest_second>=0)||($request->itempro_date_bank[0]>0)){

            $data_branch = SaleStockDetailModel::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_project_stock.branch_id") 
                           ->where("nso007_sale_detail_stock.saledetail_saleid",$request->salename_id)
                           ->first();

            $check              = false;
            $saleID             = $request->salename_id;
            $dpsAmount          = $this->getdpsAmountBySaleID($saleID);
            $clientID           = $request->clientname_id;
            $agencyID           = $request->agencyname_id;
            $agnecyCL           = $request->loan_close_saleid;
            $discount           = $request->sale_discount;
            $discountCmm        = $request->discount_commis;
            $totalCost          = $request->item_cost;
            $interest           = $request->loan_interest;
            $duration           = $request->loan_duration;
            $type               = $request->loan_type;
            $disbursement       = date('Y-m-d', strtotime($request->loan_disbursement));
            $loanStartDate      = date('Y-m-d', strtotime($request->loan_firstpaydate));
            $remainingBalance   = $totalCost - $dpsAmount - $discount - $discountCmm;
            //insert installment data
            ApproveInstalmentModel::insert([
                'branch_id'         => (empty($data_branch)?Branch::branch_id():$data_branch->branch_id),
                'loan_close_saleid' => $agnecyCL,
                'agency_id'         => $agencyID,
                'loan_clientid'     => $clientID,
                'loan_amount'       => $totalCost,
                'loan_discount'     => $discount,
                'discount_commis'   => $discountCmm,
                'loan_saleid'       => $saleID,
                'loan_disbursement' => $disbursement,
                'loan_staff'        => Auth::user()->id,
                'loan_createdate'   => date('Y-m-d'),
                'loan_amountos'     => $remainingBalance,
                'loan_firstpaydate' => $loanStartDate,
                'discount_type'     => $request->discount_type,
                'discounttype_amount'    => $request->member_discount_amount,
                'payment_policy_detial_id' => $request->payment_policy_detail
            ]);
            $loanID = ApproveInstalmentModel::where('loan_saleid', $saleID)->orderBy("id","DESC")->first()->id;
            //insert installment data
            //down payment
            $dp_amount  = $request->itempro_payamount_dp;
            $dp_date    = $request->itempro_date_dp;
            if (!empty($dp_amount[0]) && $dp_amount[0] > 0) {
                $check = true;
                foreach ($dp_amount as $key => $amount) {
                    $remainingBalance -= $amount;
                    ApproveInstalmentStepModel::insert([
                        'approve_instalment_id' => $loanID,
                        'itempro_date'          => date('Y-m-d', strtotime($dp_date[$key])),
                        'itempro_payamount'     => $amount,
                        'itempro_status'        => 1
                    ]);
                }
            }
            
            //down payment
            //step payment
            $st_amount = $request->itempro_payamount;
            $st_date   = $request->itempro_date;
            if (!empty($st_amount[0]) && $st_amount[0] > 0) {
                $check = true;
                foreach ($st_amount as $key => $amount) {
                    $remainingBalance -= $amount;
                    ApproveInstalmentStepModel::insert([
                        'approve_instalment_id' => $loanID,
                        'itempro_date'          => date('Y-m-d', strtotime($st_date[$key])),
                        'itempro_payamount'     => $amount,
                        'itempro_status'        => 2
                    ]);
                }
            }
            //step payment
            //loan installment
            // if (($remainingBalance > 1 || $check == false || $remainingBalance < 0) && (empty($duration) || $interest == null || empty($type))) {
            //     ApproveInstalmentStepModel::where('approve_instalment_id', $loanID)->delete();
            //     ApproveInstalmentModel::where('id', $loanID)->delete();
            //     return redirect()->back()->with('danger', 'Incorrect Input Data, Please Try Again!');
            // }
            if ($remainingBalance > 1 && !empty($duration)&& $duration>0 && $interest != null && !empty($type)&&$request->first_loan_condition==1&&$interest>=0) {
                ApproveInstalmentModel::where('id', $loanID)->update([
                    'loan_duration'         => $duration,
                    'loan_interest'         => $interest,
                    'loan_type'             => $type,
                    'loan_amount_payment'   => $request->loan_amount,
                ]);
            }
            //loan installment

            //Insert Loan Change rate
            if($request->change_rate_condition==1&&!empty($request->loan_amount_second)&&$request->loan_amount_second>0&&!empty($request->loan_duration_second)&&$request->loan_duration_second>0&&isset($request->loan_interest_second)&&$request->loan_interest_second>=0){
                // if($request->change_rate_from[0]>0&&$request->change_rate_to[0]>0){
                ApproveInstalmentModel::where('id', $loanID)->update([
                    'change_rate_condition' => 1
                ]);
                foreach ($request->change_rate_from as $key => $value) {
                    DB::table("nso007_loan_installment_change_rate_for_approve")->insert(
                        [
                            "loan_id"=>$loanID,
                            "amount_generate_loan"=>$request->loan_amount_second,
                            "loan_type"=>$request->loan_type_second,
                            "duration"=>$request->loan_duration_second,
                            'first_payment_date'=>date("Y-m-d",strtotime($request->loan_firstpaydate_second)),
                            'first_interest'=>$request->change_rate_condition,
                            'month_from'=>$request->change_rate_from[$key],
                            'month_to'=>$request->change_rate_to[$key],
                            'interest_for_change'=>$request->rate_condition[$key]
                        ]);
                }
                // }
            }
            //End

            //Insert loan bank
            if($request->itempro_date_bank[0]>0){
                ApproveInstalmentModel::where('id', $loanID)->update([
                    'payment_with_bank' => 1
                ]);
                foreach ($request->itempro_date_bank as $key => $value) {
                    DB::table('nso007_loan_installment_with_bank_for_approve')
                        ->insert([
                            'loan_id'=>$loanID,
                            'date_payment'=>date("Y-m-d",strtotime($request->itempro_date_bank[$key])),
                            'amount'=>$request->itempro_payamount_bank[$key]
                        ]);
                }

            }
            //End

            //update sale for approve request
            SaleStockModel::where('id', $saleID)->update([
                'status_pos_approve_type'   => 5
            ]);
            //update sale for approve request



            //relation ship and border contract
            DB::table("nso007_relationship_status")->where("sale_id",$saleID)->update(['loan_id'=>$loanID]);
            // $client_relationship        = $request->ship_id;
            // if (!empty($client_relationship)) {
            //     foreach ($client_relationship as $i => $ship_id) {
            //         RelationshipStatus::insert([
            //             'clientship_id'         => $ship_id,
            //             'relationship_status'   => $request->ship_status[$i],
            //             'loan_id'               => $loanID
            //         ]);
            //     }
            // }

            if (!empty($request->north_border) || !empty($request->south_border) || !empty($request->east_border) || !empty($request->west_border)) {
                DB::table('nso007_border')->insert([
                    'loan_id'       => $loanID,
                    'north_border'  => $request->north_border,
                    'south_border'  => $request->south_border,
                    'east_border'   => $request->east_border,
                    'west_border'   => $request->west_border
                ]);
            }

            DB::table('nso007_deposit_schedule')->where("ds_saleid",$saleID)->where("type","!=",0)->update(['ds_status'=>0]);

            //relation ship and border contract
            \LogActivity::addToLog('Installment has successfully!');
            return redirect()->route('items-deposit.index', 1)
                ->with('success', 'Installment Created successfully!');
        }
        else{
            return \back()->with("warning","Unsuccessfully!");
        }
        
        
    }
    public function getprojectIDBysaleID($sale_id)
    {
        $saleDetail = SaleStockDetailModel::where('saledetail_saleid', $sale_id)->first();
        $projectID = ItemModel::find($saleDetail->saledetail_itemid)->item_projectid;
        return $projectID;
    }
    public function InvContract($sale_id)
    {
        $invoice_no = str_pad($this->getProjectIDBySaleID($sale_id), 3, '0', STR_PAD_LEFT);

        $max = LoanModel::where('loan_saleid', $sale_id)->first()->id;
        $increas = $max + 0;
        (string) $increas;
        $increas = str_pad($increas, 5, "0", STR_PAD_LEFT);
        $inv_contract = $invoice_no . '-' . $increas;
        return $inv_contract;
    }

    public function approve_store_instalment(Request $request, $Digit = 5)
    {
        $this->validate($request, [
            'sale_id' => 'required'
        ]);

        $no                 = 1;
        $sale_id            = $request->sale_id;
        $installment        = ApproveInstalmentModel::where('loan_saleid', $sale_id)->orderBy("id","DESC")->first();
        // dd($installment);

        $downPayment        = ApproveInstalmentStepModel::where([['approve_instalment_id', $installment->id], ['itempro_status', 1]])->orderBy('id', 'asc')->get();
        $stepPayment        = ApproveInstalmentStepModel::where([['approve_instalment_id', $installment->id], ['itempro_status', 2]])->orderBy('id', 'asc')->get();
        $data_change_rate = DB::table('nso007_loan_installment_change_rate_for_approve')->where("loan_id",$installment->id)->orderBy("id")->get();
        $data_loan_bank = DB::table('nso007_loan_installment_with_bank_for_approve')->where("loan_id",$installment->id)->get();

        if(isset($data_loan_bank[0])&&$data_loan_bank[0]->amount>0){
            $data_loan_bank_procedures_setting = DB::table("nso007_bank_loan_procedure")->get();
            if(empty($data_loan_bank_procedures_setting)) return \back()->with("warning","Bank Loan Procedures is required");
            else if(count($data_loan_bank_procedures_setting)<=0) return \back()->with("warning","Bank Loan Procedures is required");
        }

        $loan_data_insert = new LoanModel;
        $loan_data_insert->branch_id = $installment->branch_id;
        $loan_data_insert->loan_clientid = $installment->loan_clientid;
        $loan_data_insert->loan_amount =  $installment->loan_amount;
        $loan_data_insert->loan_discount = $installment->loan_discount;
        $loan_data_insert->loan_amountos = $installment->loan_amountos;
        $loan_data_insert->loan_amount_loan = $installment->loan_amount_loan;
        $loan_data_insert->loan_payment_step = $installment->loan_payment_step;
        $loan_data_insert->loan_disbursement = $installment->loan_disbursement;
        $loan_data_insert->loan_close_saleid = $installment->loan_close_saleid;
        $loan_data_insert->loan_agentid = $installment->agency_id;
        $loan_data_insert->loan_staff = $installment->loan_staff;
        $loan_data_insert->loan_downpayment = $installment->loan_downpayment;
        $loan_data_insert->discount_commis =  $installment->discount_commis;
        
        $loan_data_insert->loan_saleid = $installment->loan_saleid;
        $loan_data_insert->loan_percentpay = $installment->loan_percentpay;
        $loan_data_insert->loan_createdate =  $installment->loan_createdate;
        $loan_data_insert->discount_type = $installment->discount_type;
        $loan_data_insert->discounttype_amount = $installment->discounttype_amount;
        $loan_interest = "";
        if($installment->change_rate_condition==1){
            
            foreach ($data_change_rate as $key => $value) {
                $month_from_r = $value->month_from;$month_to_r=$value->month_to;
                $loan_interest .= $value->interest_for_change."% (from ".$month_from_r."month to ".$month_to_r."month)";
                if(isset($data_change_rate[$key+1])) $loan_interest .= "//"; 
            }
            $loan_data_insert->loan_interest = $loan_interest;
            if(isset($data_change_rate[0])){
                $loan_data_insert->loan_duration = $data_change_rate[0]->duration;
                $loan_data_insert->loan_firstpaydate = $data_change_rate[0]->first_payment_date;
                $loan_data_insert->loan_amount_payment = $data_change_rate[0]->amount_generate_loan;
                $loan_data_insert->loan_type =$data_change_rate[0]->loan_type;
            }
            
            
            
        }
        else{
            $loan_data_insert->loan_interest = $installment->loan_interest."%";
            // $loan_interest = $installment->loan_interest;
            $loan_data_insert->loan_duration = $installment->loan_duration;
            $loan_data_insert->loan_firstpaydate = $installment->loan_firstpaydate;
            $loan_data_insert->loan_amount_payment = $installment->loan_amount_payment;
            $loan_data_insert->loan_type = $installment->loan_type;
            
        }

        $loan_data_insert->save();
        
        $loanID             = LoanModel::where('loan_saleid', $sale_id)->first()->id;
        $client_id          = $installment->loan_clientid;
        $installmentAmount  = $installment->loan_amount - $installment->loan_discount - $installment->discount_commis - $this->getdpsAmountBySaleID($sale_id);
        $remainingBalance   = $installmentAmount;
        $incrementPrincipal = 0;
        $incrementInterest  = 0;
        $decrementPrincipal = $installmentAmount;
        if (count($downPayment) > 0) {
            foreach ($downPayment as $key => $down) {
                $incrementPrincipal += $down->itempro_payamount;
                $remainingBalance   -= $down->itempro_payamount;
                $decrementPrincipal -= $down->itempro_payamount;
                $incrementInterest  += 0;
                LoanProcessModel::insert([
                    'loanprocess_loanid'        => $loanID,
                    'loanprocess_no'            => $no,
                    'loanprocess_datepayment'   => $down->itempro_date,
                    'loanprocess_interest'      => 0,
                    'loanprocess_interestpay'   => 0,
                    'loanprocess_principle'     => $down->itempro_payamount,
                    'loanprocess_principlepay'  => $down->itempro_payamount,
                    'loanprocess_principleos'   => $remainingBalance,
                    'loanprocess_accprinciple'  => $incrementPrincipal,
                    'loanprocess_accinterest'   => $incrementInterest,
                    'loanprocess_amountpaid'    => $incrementPrincipal,
                    'loanprocess_amountcredit'  => $remainingBalance,
                    'loanprocess_owe'           => $down->itempro_payamount,
                    'loanprocess_owepay'        => $down->itempro_payamount,
                    'loanprocess_createdate'    => date('Y-m-d H:i:s'),
                    'loanprocess_status'        => 1
                ]);
                $no++;
            }
        }

        if (count($stepPayment) > 0) {
            foreach ($stepPayment as $key => $step) {
                $incrementPrincipal += $step->itempro_payamount;
                $remainingBalance   -= $step->itempro_payamount;
                $decrementPrincipal -= $step->itempro_payamount;
                $incrementInterest  += 0;
                LoanProcessModel::insert([
                    'loanprocess_loanid'        => $loanID,
                    'loanprocess_no'            => $no,
                    'loanprocess_datepayment'   => $step->itempro_date,
                    'loanprocess_interest'      => 0,
                    'loanprocess_interestpay'   => 0,
                    'loanprocess_principle'     => $step->itempro_payamount,
                    'loanprocess_principlepay'  => $step->itempro_payamount,
                    'loanprocess_principleos'   => $remainingBalance,
                    'loanprocess_accprinciple'  => $incrementPrincipal,
                    'loanprocess_accinterest'   => 0,
                    'loanprocess_amountpaid'    => $incrementPrincipal,
                    'loanprocess_amountcredit'  => $remainingBalance,
                    'loanprocess_owe'           => $step->itempro_payamount,
                    'loanprocess_owepay'        => $step->itempro_payamount,
                    'loanprocess_createdate'    => date('Y-m-d H:i:s'),
                    'loanprocess_status'        => 2
                ]);
                $no++;
            }
        }

        $leftinstallmentAmount = $installment->loan_amount_payment;
        if ($remainingBalance > 0 && !empty($installment->loan_type) && !empty($installment->loan_duration)) {
            $loanInterest = $installment->loan_interest;
            if (empty($loanInterest)) {
                $loanInterest = 0;
            }
            $data_return = $this->LoanprocessGenerator($loanID, $leftinstallmentAmount, $installment->loan_type, $installment->loan_duration, $loanInterest, $installment->loan_firstpaydate, $no, $incrementInterest, $incrementPrincipal, $decrementPrincipal);
             $no=$data_return['no'];
             $incrementPrincipal = $data_return['incrementPrincipal'];
             $incrementInterest = $data_return['incrementInterest'];
             $decrementPrincipal = $data_return['decrementPrincipal'];
        }

        // else{
            if(isset($data_change_rate[0])){
                $remainingBalance = $data_change_rate[0]->amount_generate_loan;
                // dd($remainingBalance);
                $startDate = $data_change_rate[0]->first_payment_date;
                // $decrementPrincipal = $remainingBalance;
                $rate_change=0;
                if ($data_change_rate[0]->loan_type == 2 || ($data_change_rate[0]->loan_type == 1 && $data_change_rate[0]->first_interest <= 0)) {
                    $principal=$remainingBalance / $data_change_rate[0]->duration;
                    $rate = $data_change_rate[0]->first_interest / 100;
                    $rate_change=$rate;

                    for ($i = 1; $i <= $data_change_rate[0]->duration; $i++) {
                        $month_from_r = 0;$month_to_r=0;
                        $interest = $rate * $remainingBalance;
                        $rate_change=$rate;
                        foreach ($data_change_rate as $key => $value) {
                            if($value->month_from<=$i&&$value->month_to>=$i){
                                $interest = $remainingBalance*$value->interest_for_change/100;
                                $rate_change = $value->interest_for_change;
                                $month_from_r = $value->month_from;$month_to_r=$value->month_to;
                            }
                        }
                        $remainingBalance   -= $principal;
                        $payment            = $interest + $principal;
                        $incrementPrincipal += $principal;
                        $incrementInterest  += $interest;
                        $decrementPrincipal -= $principal;
                        LoanProcessModel::insert([
                            'loanprocess_loanid'        => $loanID,
                            'loanprocess_no'            => $no,
                            'loanprocess_datepayment'   => $startDate,
                            'loanprocess_interest'      => $interest,
                            'loanprocess_interestpay'   => $interest,
                            'loanprocess_principle'     => $principal,
                            'loanprocess_principlepay'  => $principal,
                            'loanprocess_principleos'   => $decrementPrincipal,
                            'loanprocess_accprinciple'  => $incrementPrincipal,
                            'loanprocess_accinterest'   => $incrementInterest,
                            'loanprocess_amountpaid'    => $incrementPrincipal,
                            'loanprocess_amountcredit'  => $decrementPrincipal,
                            'loanprocess_owe'           => $payment,
                            'loanprocess_owepay'        => $payment,
                            'loanprocess_status'        => 4,
                            'note_type'                 => 1,
                            'rate_for_change'           => $rate_change,
                            'rate_change_month_from'    => $month_from_r,
                            'rate_change_month_to'      => $month_to_r
                        ]);
                        $no++;
                        $startDate = $this->Loan_date_generator($startDate);
                    }
                } else {
                    $rate = $data_change_rate[0]->first_interest / 100;
                    $loanDuration = $data_change_rate[0]->duration;
                    $remainingBalance = $data_change_rate[0]->amount_generate_loan;
                    $payment = ($remainingBalance * $rate) / (1 - (pow((1 + $rate), -$loanDuration)));
                    $index_count_month = 0;
                    $check_change_rate = $rate;
                    $rate_cp = $rate;

                    for ($i = 1; $i <= $loanDuration; $i++) {
                        $month_from_r = 0;$month_to_r=0;
                        $start = $i + 1;
                        $check_change_rate = $rate;
                        foreach ($data_change_rate as $key => $value) {
                           if($value->month_from<=$i&&$value->month_to>=$i){
                                $check_change_rate =  $value->interest_for_change/100;
                                $month_from_r = $value->month_from;$month_to_r=$value->month_to;
                           }
                        }
                        if($rate_cp!=$check_change_rate){
                            $rate_cp = $check_change_rate;
                            $payment = ($remainingBalance * $check_change_rate) / (1 - (pow((1 + $check_change_rate), -($loanDuration-$index_count_month))));
                        }
                        $index_count_month++;
                        $interest           = $remainingBalance * $check_change_rate;
                        $principal              = $payment - $interest;
                        $remainingBalance   -= $principal;
                        $incrementInterest     += $interest;
                        $incrementPrincipal    += $principal;
                        $decrementPrincipal    -= $principal;
                        LoanProcessModel::insert([
                            'loanprocess_loanid'        => $loanID,
                            'loanprocess_no'            => $no,
                            'loanprocess_datepayment'   => $startDate,
                            'loanprocess_interest'      => $interest,
                            'loanprocess_interestpay'   => $interest,
                            'loanprocess_principle'     => $principal,
                            'loanprocess_principlepay'  => $principal,
                            'loanprocess_principleos'   => $decrementPrincipal,
                            'loanprocess_accprinciple'  => $incrementPrincipal,
                            'loanprocess_accinterest'   => $incrementInterest,
                            'loanprocess_amountpaid'    => $incrementPrincipal,
                            'loanprocess_amountcredit'  => $decrementPrincipal,
                            'loanprocess_owe'           => $payment,
                            'loanprocess_owepay'        => $payment,
                            'loanprocess_status'        => 4,
                            'note_type'                 => 1,
                            'rate_for_change'           => $check_change_rate,
                            'rate_change_month_from'    => $month_from_r,
                            'rate_change_month_to'      => $month_to_r
                        ]);
                        $no++;
                        $startDate = $this->Loan_date_generator($startDate);
                    }
                }
            }
            
        // }

        if(isset($data_loan_bank[0])&&$data_loan_bank[0]->amount>0){
            $incrementPrincipal    += $data_loan_bank[0]->amount;
            $decrementPrincipal    -= $data_loan_bank[0]->amount;
            LoanProcessModel::insert([
                'loanprocess_loanid'        => $loanID,
                'loanprocess_no'            => $no,
                'loanprocess_datepayment'   => date('Y-m-d',strtotime($data_loan_bank[0]->date_payment)),
                'loanprocess_interest'      => 0,
                'loanprocess_interestpay'   => 0,
                'loanprocess_principle'     => $data_loan_bank[0]->amount,
                'loanprocess_principlepay'  => $data_loan_bank[0]->amount,
                'loanprocess_principleos'   => $decrementPrincipal,
                'loanprocess_accprinciple'  => $incrementPrincipal,
                'loanprocess_accinterest'   => $incrementInterest,
                'loanprocess_amountpaid'    => $incrementPrincipal,
                'loanprocess_amountcredit'  => $decrementPrincipal,
                'loanprocess_owe'           => $data_loan_bank[0]->amount,
                'loanprocess_owepay'        => $data_loan_bank[0]->amount,
                'loanprocess_status'        => 4,
                'note_type'                 => 2,
            ]);
            $data_loan_bank_procedures_setting = DB::table("nso007_bank_loan_procedure")->get();
            foreach($data_loan_bank_procedures_setting as $key=>$value){
                DB::table("nso007_sale_bank_loan_procedures")->insert([
                    'bank_loan_procedures_id'=>$value->id,
                    'loan_id'=>$loanID,
                    'bank_loan_procedures_name'=>$value->name,
                    'bank_loan_procedures_description'=>$value->description,
                    'amount'=>$data_loan_bank[0]->amount
                ]);
            }
        }

        

        $this->put_journal_installment($sale_id, $installment->loan_amount, $installment->loan_discount);
        $this->updatePreInstallmentReference($installment->id, $loanID);
        $this->updateSaleAfterApproveInstallment($sale_id);
        SaleStockModel::where('id', $sale_id)->update([
            'sale_discount'=>$installment->loan_discount
        ]);
        $policy_payment_detail = DB::table('nso007_payment_policy_detail')->where("id",$installment->payment_policy_detial_id)->first();
        DB::table('nso007_sale_policy_payment')->where("sale_id",$sale_id)->delete();
        DB::table('nso007_sale_policy_payment')->insert(['sale_id'=>$sale_id,'payment_policy_id'=>$policy_payment_detail->policy_id,'policy_detail_id'=>$policy_payment_detail->id]);
        if(!empty($policy_payment_detail)){
            // dd("OK");
            
        }

        $invContract        = $this->InvContract($sale_id);
        $percentPay         = $this->percentPayBySaleID($sale_id);
        
        LoanModel::where('id', $loanID)->update([
            'loan_inv_contract'     => $invContract,
            'loan_percentpay'       => $percentPay,
            'loan_duration'         => $no-1
        ]);
        \LogActivity::addToLog('Installment has successfully!');
        return redirect()->route('client-payment-list.show', Crypt::encrypt($client_id))
            ->with('success', 'Installment Created successfully!');
    }
    public function LoanprocessGenerator($loanID, $installmentAmount, $loantype, $loanDuration, $loanInterest, $startDate, $loanNumberstart, $incrementInterest, $incrementPrincipal, $decrementPrincipal)
    {
        $rate = $loanInterest / 100;
        $remainingBalance = $installmentAmount;
        if ($loantype == 2 || ($loantype == 1 && $loanInterest <= 0)) {
            $principal = $installmentAmount / $loanDuration;
            for ($i = 1; $i <= $loanDuration; $i++) {
                $interest           = $rate * $remainingBalance;
                $remainingBalance   -= $principal;
                $payment            = $interest + $principal;
                $incrementPrincipal += $principal;
                $incrementInterest  += $interest;
                $decrementPrincipal -= $principal;
                LoanProcessModel::insert([
                    'loanprocess_loanid'        => $loanID,
                    'loanprocess_no'            => $loanNumberstart,
                    'loanprocess_datepayment'   => $startDate,
                    'loanprocess_interest'      => $interest,
                    'loanprocess_interestpay'   => $interest,
                    'loanprocess_principle'     => $principal,
                    'loanprocess_principlepay'  => $principal,
                    'loanprocess_principleos'   => $decrementPrincipal,
                    'loanprocess_accprinciple'  => $incrementPrincipal,
                    'loanprocess_accinterest'   => $incrementInterest,
                    'loanprocess_amountpaid'    => $incrementPrincipal,
                    'loanprocess_amountcredit'  => $decrementPrincipal,
                    'loanprocess_owe'           => $payment,
                    'loanprocess_owepay'        => $payment,
                    'loanprocess_status'        => 3,
                ]);
                $loanNumberstart++;
                $startDate = $this->Loan_date_generator($startDate);
            }
        } else {
            $payment = ($installmentAmount * $rate) / (1 - (pow((1 + $rate), -$loanDuration)));
            for ($i = 1; $i <= $loanDuration; $i++) {
                $start              = $i + 1;
                $interest           = $remainingBalance * $rate;
                $remainingBalance   = ((pow((1 + $rate), $start - 1) * $installmentAmount) - (((pow((1 + $rate), $start - 1) - 1) / $rate) * $payment));

                $principal              = $payment - $interest;
                $incrementInterest     += $interest;
                $incrementPrincipal    += $principal;
                $decrementPrincipal    -= $principal;
                LoanProcessModel::insert([
                    'loanprocess_loanid'        => $loanID,
                    'loanprocess_no'            => $loanNumberstart,
                    'loanprocess_datepayment'   => $startDate,
                    'loanprocess_interest'      => $interest,
                    'loanprocess_interestpay'   => $interest,
                    'loanprocess_principle'     => $principal,
                    'loanprocess_principlepay'  => $principal,
                    'loanprocess_principleos'   => $decrementPrincipal,
                    'loanprocess_accprinciple'  => $incrementPrincipal,
                    'loanprocess_accinterest'   => $incrementInterest,
                    'loanprocess_amountpaid'    => $incrementPrincipal,
                    'loanprocess_amountcredit'  => $decrementPrincipal,
                    'loanprocess_owe'           => $payment,
                    'loanprocess_owepay'        => $payment,
                    'loanprocess_status'        => 3,
                ]);
                $loanNumberstart++;
                $startDate = $this->Loan_date_generator($startDate);
            }
        }
        $data_return = array("no"=>$loanNumberstart,"incrementPrincipal"=>$incrementPrincipal,"incrementInterest"=>$incrementInterest,'decrementPrincipal'=>$decrementPrincipal);
        return  $data_return;
    }
    public function pre_contract($sale_id)
    {
        $contract = DB::table('nso007_approve_instalment as installment')
            ->join('nso007_client as client', 'client.id', 'installment.loan_clientid')
            ->join('nso007_sale_stock as sale', 'sale.id', 'installment.loan_saleid')
            ->join('nso007_sale_detail_stock as detail', 'detail.saledetail_saleid', 'sale.id')
            ->join('nso007_item_stock as stock', 'stock.id', 'detail.saledetail_itemid')
            ->leftJoin('nso007_sub_category_stock','nso007_sub_category_stock.id','stock.item_sub_categoryid')
            ->join('nso007_project_stock as project', 'project.id', 'stock.item_projectid')
            ->leftjoin('nso007_border as border', 'border.loan_id', 'installment.id')
            ->select(
                'installment.*',
                DB::raw('group_concat(stock.item_house) as item_name'),
                DB::raw('concat(client.client_firstname," ",client.client_lastname) as client_name'),
                'project.project_name',
                'project.id as pro_id',
                'client.id as cl_id',
                'client.client_gender as gender',
                'client.client_dob as dob',
                'client.client_nickname as name_in_latin',
                'client.client_national as nation',
                'client.client_phone1 as phone',
                'client.p_village AS village_id',
                'client.client_cardid as cardid',
                'sale.sale_receive',
                'sale.id as sale_id',
                'stock.item_street',
                'sale.sale_amount',
                'sale.sale_discount',
                'sale.sale_remain',
                'sale.sale_receive',
                'sale.sale_date',
                'stock.item_size_land',
                'stock.item_size_house',
                'stock.item_street',
                'stock.item_floor',
                'stock.item_length_house',
                'stock.item_width_house',
                'nso007_sub_category_stock.sub_category_name',
                DB::raw('SUM(stock.item_size_land) as item_size'),
                'border.north_border as north',
                'border.south_border as south',
                'border.east_border as east',
                'border.west_border as west'
            )
            ->where('sale.id', Crypt::decrypt($sale_id))
            ->first();
        $client_address = ClientModel::select("*")
            ->where('id',$contract->cl_id)
            ->first();
        $cl_address='';

        $cl_province_km=optional($client_address->provices)->province_km;
        $cl_district_km=optional($client_address->districts)->district_km;
        $cl_commune_km=optional($client_address->communes)->commune_km;
        $cl_village_km=optional($client_address->villages)->village_km;

        $cl_address='ភូមិ '. $cl_village_km .' ឃុំ/សង្កាត់ '.  $cl_commune_km .' ស្រុក/ខណ្ឌ '. $cl_district_km .' ខេត្ត/ក្រុង '.$cl_province_km;
        $project = ProjectModel::where('id',$contract->pro_id)
            ->first();
        $province_km=optional($project->provices)->province_km;
        $district_km=optional($project->districts)->district_km;
        $commune_km=optional($project->communes)->commune_km;
        $village_km=optional($project->villages)->village_km;

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

        if(!empty($sale_payment_policies))
        {
            $payment_policy_detail.=optional(DB::table("nso007_payment_policy_detail")->where("id",optional($sale_payment_policies)->policy_detail_id)->first())->name;
        }
        $clientship = ClientRelationshipModel::join('nso007_relationship_status as relationship', 'relationship.clientship_id', 'nso007_client_relationship.id')->where('relationship.loan_id', $contract->id)->get();
        return view('administrator.deposit.pre-contract')->with(['village_km' => $village_km,'commune_km' => $commune_km,'district_km' => $district_km,'province_km' => $province_km,'cl_address' => $cl_address,'payment_policy_detail' => $payment_policy_detail,'contract' => $contract,'payment_policy' => $payment_policy, 'clientship' => $clientship]);
    }
    public function percentPayBySaleID($sale_id)
    {
        $depositAmount  = $this->getdpsAmountBySaleID($sale_id);
        $loan           = LoanModel::where('loan_saleid', $sale_id)->first();
        $specificAmount = $loan->loan_amount - $loan->loan_discount - $loan->discount_commis;
        $percent        = $depositAmount * 100 / $specificAmount;
        return $percent;
    }

    public function updateSaleAfterApproveInstallment($sale_id)
    {
        $loan = LoanModel::where('loan_saleid',$sale_id)->first();
        $itemID = $this->getItemIDBySaleID($sale_id);
        ItemModel::whereIn('id', explode(',', $itemID))->update([
            'item_salestatus'           => 2,
            'item_stockstatus'          => 2
        ]);
        SaleStockModel::where('id', $sale_id)->update([
            'sale_deposit'  => 2,
            'status_pos_approve_type'   => 0,
            'close_sale_id' => $loan->loan_close_saleid,
            'agency_id' => $loan->loan_agentid
        ]);
    }
    public function getItemIDBySaleID($sale_id)
    {
        return SaleStockDetailModel::where('saledetail_saleid', $sale_id)->select(DB::Raw('GROUP_CONCAT(saledetail_itemid) as itemID'))->first()->itemID;
    }
    public function updatePreInstallmentReference($id, $loanID)
    {
        BorderModel::where('loan_id', $id)->update([
            'loan_id'   => $loanID
        ]);
        RelationshipStatus::where('loan_id', $id)->update([
            'loan_id'   => $loanID
        ]);
        ApprovePaymentModel::where('id', $id)->delete();
        ApproveInstalmentStepModel::where('approve_instalment_id', $id)->delete();
        DB::table("nso007_loan_installment_change_rate_for_approve")->where("loan_id",$id)->delete();
        DB::table("nso007_loan_installment_with_bank_for_approve")->where("loan_id",$id)->delete();
    }
    public function Loan_date_generator($date)
    {
        return date('Y-m-d', strtotime("$date +1 month"));
    }
    

    /**
     *  Update Sale Stock Status
     */
    public function set_sale_stock_status($sale_id)
    {
        SaleStockModel::where('id', $sale_id)->update([
            'sale_deposit' => 2,
            'sale_purchase' => 1
        ]);
    }
    /**
     *  Update Client Contract Value 0 to 1
     */
    public function set_client_contract_field_status($client_id)
    {
        ClientModel::where('id', $client_id)->update(['client_contract' => 1]);
    }
    /**
     *  Insert Loan Information
     */
    public function put_loan_information($data_loan_information)
    {
        extract($data_loan_information);
        $loan_table     = new LoanModel();
        $loan_table->loan_clientid          = $loan_clientid;
        $loan_table->loan_amount            = $loan_amount;
        $loan_table->loan_discount          = $loan_discount;
        $loan_table->loan_amountos          = $loan_amountos;
        $loan_table->loan_amount_payment    = $loan_amount_payment;
        $loan_table->loan_amount_loan       = $loan_amount_loan;
        $loan_table->loan_interest          = $loan_interest;
        $loan_table->loan_duration          = $loan_duration;
        $loan_table->loan_term              = $loan_term;
        $loan_table->loan_payment_step      = $loan_payment_step;
        $loan_table->loan_disbursement      = $loan_disbursement;
        $loan_table->loan_firstpaydate      = $loan_firstpaydate;
        $loan_table->loan_agentid           = $loan_agentid;
        $loan_table->loan_close_saleid           = $loan_close_saleid;
        $loan_table->loan_staff             = $loan_staff;
        $loan_table->loan_currency          = $loan_currency;
        $loan_table->loan_type              = $loan_type;
        $loan_table->loan_saleid            = $loan_saleid;
        $loan_table->loan_percentpay        = $loan_percentpay;
        $loan_table->loan_commission_status = $loan_commission_status;
        $loan_table->loan_downpayment       = $loan_downpayment;
        $loan_table->discount_commis        = $discount_commis;
        $loan_table->loan_client_relation_id = $loan_client_relation_id;
        $loan_table->loan_inv_contract      = $loan_inv_contract;
        // dd($discount_commis);
        $loan_table->save();
        return $loan_table->id;
    }

    /**
     *  Active Agency Commission When Making Contract
     */
    public function active_agency_commission($sale_id)
    {
        CommissionModel::where('sale_id', $sale_id)->update(['status' => 1]);
        $data_commission = CommissionModel::where('sale_id', $sale_id)->get();
        $commission_id = $data_commission[0]->id;
        CommissionItemModel::where('commission_id', $commission_id)->update(['status' => 1]);
    }

    /**
     *  Insert Client Relationship -- No require
     */
    public function put_client_relationship($loan_id, $data_client_relationship)
    {
        extract($data_client_relationship);

        $client_relation_table                        = new ClientRelationshipModel();
        $client_relation_table->clientship_loanid     = $loan_id;
        $client_relation_table->clientship_fullname   = $relationship_fullname;
        $client_relation_table->clientship_gender     = $relationship_gender;
        $client_relation_table->clientship_dob        = $relationship_dob;
        $client_relation_table->clientship_phone1     = $relationship_phone1;
        $client_relation_table->clientship_phone2     = $relationship_phone2;
        $client_relation_table->clientship_national   = $relationship_national;
        $client_relation_table->clientship_cardid     = $relationship_cardid;
        $client_relation_table->clientship_datecard   = $relationship_datecard;
        $client_relation_table->clientship_cardvalid  = $relationship_cardvalid;
        $client_relation_table->clientship_cardby     = $relationship_cardby;
        $client_relation_table->clientship_job        = $relationship_job;
        $client_relation_table->clientship_is         = $relationship_is;
        return $client_relation_table->save();
    }

    /**
     *  Start update item in stock to out stock after making contract
     */
    public function deactive_item_stock($salevar_id)
    {
        $data_sale_item = SaleStockDetailModel::where('saledetail_saleid', $salevar_id)->get();
        foreach ($data_sale_item as $key => $value) {
            $sale_item_id = $value->saledetail_itemid;
            ItemModel::where('id', $sale_item_id)->update(['item_stockstatus' => 2]);
        }
    }

    /**
     *  Condition Has Discount When Making Contract
     */
    public function put_discount_processing_making_contract($sale_id, $discount_amount)
    {
        SaleStockModel::where('id', $sale_id)->update([
            'sale_discount' => $discount_amount,
            'sale_remain' => DB::raw("sale_remain - $discount_amount")
        ]);
    }

    /**
     *  Get Description Detail For Item
     */
    public function get_item_description($sale_id)
    {
        $item_data = SaleStockDetailModel::where('saledetail_saleid', $sale_id)
            ->leftjoin('nso007_item_stock as stock', 'nso007_sale_detail_stock.saledetail_itemid', '=', 'stock.id')
            ->select('item_house')->get();
        return $item_data[0]->item_house;
    }

    /**
     *  Put installment operation to journal
     */
    public function put_journal_installment($sale_id, $sale_cost, $sale_discount)
    {
        // Item Name
        $item_name  = $this->get_item_description($sale_id);
        // Invoice Number
        $invoice    = str_pad($sale_id, 5, '0', STR_PAD_LEFT);
        // Des
        $des        = $item_name . " (Sale)";

        $journal_table_1 = new JournalModel();
        $journal_table_1->journal_parentid      = 0;
        $journal_table_1->journal_acccode       = 40006;
        $journal_table_1->journal_type          = 4;
        $journal_table_1->journal_credit        = $sale_cost;
        $journal_table_1->journal_invoice       = $invoice;
        $journal_table_1->journal_referenceid   = $sale_id;
        $journal_table_1->journal_paydate       = date('Y-m-d');
        $journal_table_1->journal_des           = $des;
        $journal_table_1->journal_by            = Auth::user()->id;
        $journal_table_1->journal_currency      = 1;
        $journal_table_1->journal_status        = 0;
        $journal_table_1->branch_id        = \Branch::branch_id();
        $journal_table_1->save();

        // Get Last ID
        $inserted_id                            = $journal_table_1->id;

        $journal_table_2 = new JournalModel();
        $journal_table_2->journal_parentid      = $inserted_id;
        $journal_table_2->journal_acccode       = 11000;
        $journal_table_2->journal_type          = 4;
        $journal_table_2->journal_debit         = $sale_cost;
        $journal_table_2->journal_invoice       = $invoice;
        $journal_table_2->journal_paydate       = date('Y-m-d');
        $journal_table_2->journal_referenceid   = $sale_id;
        $journal_table_2->journal_des           = $des;
        $journal_table_2->journal_by            = Auth::user()->id;
        $journal_table_2->journal_currency      = 1;
        $journal_table_2->journal_status        = 0;
        $journal_table_2->branch_id        = \Branch::branch_id();
        $journal_table_2->save();

        if (!empty($sale_discount)) {
            $des           = $item_name . " (Sale Discount)";
            $journal_table = new JournalModel();
            $journal_table->journal_parentid      = $inserted_id;
            $journal_table->journal_acccode       = 50006;
            $journal_table->journal_type          = 13;
            $journal_table->journal_paydate       = date('Y-m-d');
            $journal_table->journal_credit        = $sale_discount;
            $journal_table->journal_invoice       = $invoice;
            $journal_table->journal_referenceid   = $sale_id;
            $journal_table->journal_des           = $des;
            $journal_table->journal_by            = Auth::user()->id;
            $journal_table->journal_currency      = 1;
            $journal_table->branch_id      = \Branch::branch_id();
            $journal_table->save();
        }
    }
    /**
     *  Get Max ID Loan Process After Insert Step I In The Same Loan ID
     */
    public function get_max_loanprocess_no($loan_id)
    {
        $step_one_max_id = LoanProcessModel::where('loanprocess_loanid', $loan_id)
            ->max('loanprocess_no');
        return $step_one_max_id;
    }

    /**
     *  Interest Installment Type == 2
     */
    public function interest_installment($data_loan_information, $loan_id)
    {
        extract($data_loan_information);
        $paymentByStep          = $loan_amount_payment; // Total paid in payment step
        $loanprocess_amountpaid = $paymentByStep; // Total payment step

        $amountLoan             = $loan_amount_loan; // Old Total Price for monthly payment
        $amount                 = $loan_amount; // Old Price
        $duration               = $loan_duration; // Duration pay
        $interest               = $loan_interest; // Interest
        $principlePay           = $amountLoan / $duration; // Payment for monthly
        $amountOS               = $amountLoan; // Old Total Price for monthly payment
        $no                     = $step + 1; // Count up +1 after payment step
        $accPrinciple           = $paymentByStep; // Old total paid in payment step
        $accInterest            = 0; // Old interest
        $amountPaid             = 0; // Old paid amount
        $paid_alone             = 0;

        for ($i = 0; $i < $duration; $i++) {
            $interestPay        = ($amountOS * $interest / 100); // Calculate interest monthly
            $principlePay       = $principlePay; // Monthly payment
            $amountOS          -= $principlePay; // Old Total Price after paid monthly
            $accPrinciple       = ($accPrinciple + $principlePay);
            $accInterest        = ($accInterest + $interestPay);
            $totalAmountCredit  = $amountLoan + $interestPay;
            $payment            = ($principlePay + $interestPay);
            $amountPaid        += $payment;
            $loanprocess_amountpaid += $payment;
            $paid_alone        += $principlePay;
            $amountCredit       = $amountLoan - $paid_alone;
            //echo $principlePay.'<br>';

            $data = array(
                'loanprocess_loanid'        => $loan_id,
                'loanprocess_no'            => $no,
                'loanprocess_datepayment'   => date('Y-m-d', strtotime($loan_firstpaydate . " + $i month")),
                'loanprocess_interest'      => $interestPay,
                'loanprocess_interestpay'   => $interestPay,
                'loanprocess_principle'     => $principlePay,
                'loanprocess_principlepay'  => $principlePay,
                'loanprocess_principleos'   => $amountOS,
                'loanprocess_accprinciple'  => $accPrinciple,
                'loanprocess_accinterest'   => $accInterest,
                'loanprocess_amountpaid'    => $loanprocess_amountpaid,
                'loanprocess_amountcredit'  => $amountCredit,
                'loanprocess_owe'           => $payment,
                'loanprocess_owepay'        => $payment,
                'loanprocess_owepay'        => $payment,
                'loanprocess_status'        => 3,
            );
            LoanProcessModel::insert($data);
            $no++;
        }
    }

    /**
     *  Interest Installment Type == 1
     */
    public function installment_type_interest($data_loan_information, $loan_id)
    {
        extract($data_loan_information);

        $paymentByStep          = $loan_amount_payment; // Total paid in payment step
        $loanprocess_amountpaid = $paymentByStep; // Total payment step

        $amountLoan = $loan_amount_loan; // Old Total Price for monthly payment
        $duration = $loan_duration; // Duration pay
        $interest = $loan_interest; // Interest
        $amountOS = $amountLoan; // Old Total Price for monthly payment
        $no = $step + 1; // Count up +1 after payment step
        $accPrinciple = $paymentByStep; // Old total paid in payment step
        $accInterest = 0;
        $rate = $interest / 100;
        $zero = (1 - pow((1 + $rate), -$duration));
        if (!empty($zero)) {
            $payment1 = ($amountLoan * $rate) / $zero;
        } else {
            $payment1 = 0;
        }
        $newPrincipal = $amountLoan;
        for ($i = 0; $i < $duration; $i++) {
            $newInterest    = $rate * $newPrincipal;
            $principlepay   = $payment1 - $newInterest;
            $newPrincipal   = $newPrincipal - $principlepay;


            $amountOS      -= $principlepay; // Old Total Price after paid monthly

            $accPrinciple   = ($accPrinciple + $payment1);
            $accInterest    = ($accInterest + $newInterest);

            $loanprocess_amountpaid += $payment1;
            $amountCredit   = $newPrincipal - $principlepay;

            $data = array(
                'loanprocess_loanid'        => $loan_id,
                'loanprocess_no'            => $no,
                'loanprocess_datepayment'   => date('Y-m-d', strtotime($loan_firstpaydate . " + $i month")),
                'loanprocess_interest'      => $newInterest,
                'loanprocess_interestpay'   => $newInterest,
                'loanprocess_principle'     => $principlepay,
                'loanprocess_principlepay'  => $principlepay,
                'loanprocess_principleos'   => $amountOS,
                'loanprocess_accprinciple'  => $accPrinciple,
                'loanprocess_accinterest'   => $accInterest,
                'loanprocess_amountpaid'    => $loanprocess_amountpaid,
                'loanprocess_amountcredit'  => $amountOS,
                'loanprocess_owe'           => $payment1,
                'loanprocess_owepay'        => $payment1,
                'loanprocess_status'        => 3,
            );

            LoanProcessModel::insert($data);
            $no++;
        }
    }

    /**
     *  Client cancel deposit
     */
    public function agree_cancel(Request $request)
    {
        $sale_id = $request->ps_id;
        $this->validate($request, [
            'ps_id'     => 'required|numeric',
        ]);
        $app = new AppEditPOS;
        $app->app_sale_id   = $sale_id;
        $app->app_type      = 2;
        $app->deposit_remark= $request->description;
        $app->app_payamount= $request->amount_return;
        $app->save();

        SaleStockModel::where('id', $sale_id)
            ->update(['status_pos_approve_type' => 3]);

        \LogActivity::addToLog('You will be cancel successfully after approving!');
        return \back()->with('success', 'Items Were Successfully cancel after approving');
    }
    public function approve_agree_cancel(Request $request)
    {
        $salevar_id = $request->sale_id;
        $sale_id    = $request->sale_id;

        $data = DB::select("
            SELECT saledetail_itemid FROM nso007_sale_detail_stock WHERE saledetail_saleid = $salevar_id
        ");
        $count_item = count($data);
        // Cancel item status
        for ($i = 0; $i < $count_item; $i++) {
            $itemvar_id = $data[$i]->saledetail_itemid;
            ItemModel::where('id', $itemvar_id)->update(['item_stockstatus' => 1, 'item_salestatus' => 0, 'available' => 1]);
        }
        // Cancel sale stock
        SaleStockModel::where('id', $salevar_id)->update(['sale_deposit' => 10]);
        // Record to cancel deposit
        $data_cancel = array(
            'cancel_sale'   => $salevar_id,
            'cancel_date'   => date('Y-m-d'),
            'cancel_des'    => AppEditPOS::where('app_sale_id',$sale_id)->first()->deposit_remark,
            'cancel_amount'    => AppEditPOS::where('app_sale_id',$sale_id)->first()->app_payamount
        );
        CancelDepositModel::insert($data_cancel);

        SaleStockModel::where('id', $sale_id)
            ->update(['status_pos_approve_type' => 0]);



        $amount_return   = AppEditPOS::where('app_sale_id',$sale_id)->first()->app_payamount;
        $item_stock         = ItemModel::find($itemvar_id);
        $sale           = SaleStockModel::where('id', $sale_id)->first();
        $amount_revenue   = $sale->sale_amount-$sale->sale_discount-$sale->sale_receive+$amount_return;
        $amount_revenue_ar   = $sale->sale_amount-$sale->sale_discount-$sale->sale_receive;
        if ($amount_return > 0){
            $j=JournalModel::select(DB::raw("Max(journal_tran_id) as last_num"))->first();

            $journal=new JournalModel;
            $journal->branch_id=\Branch::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::DiscountBySaleID("", $sale_id).' Cancel Deposit';
            $journal->journal_debit=0;
            $journal->journal_credit=$amount_revenue_ar;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d");
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$sale_id;
            $journal->journal_by_project=$item_stock->item_projectid;
            $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=\Branch::branch_id();
            $journal->journal_parentid=$jj->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::DiscountBySaleID("", $sale_id).' Cancel Deposit';
            $journal->journal_debit=$amount_revenue;
            $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");
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$sale_id;
            $journal->journal_by_project=$item_stock->item_projectid;
            $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=\Branch::branch_id();
            $journal->journal_parentid=$jj1->last_id;
            $journal->journal_type=1;
            $journal->journal_acccode=11001;
            $journal->voucher_type=1;
            $journal->voucher_ref="V-".\Globals::generate_voucher_number(($j->last_num+1),8);
            $journal->journal_des=\Globals::DiscountBySaleID("", $sale_id).' Cancel Deposit';
            $journal->journal_debit=0;
            $journal->journal_credit=$amount_return;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d");
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$sale_id;
            $journal->journal_by_project=$item_stock->item_projectid;
            $journal->journal_by=auth()->user()->id;
            $journal->save();

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

            $journal=new JournalModel;
            $journal->branch_id=\Branch::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::DiscountBySaleID("", $sale_id).' Cancel Deposit';
            $journal->journal_debit=0;
            $journal->journal_credit=$amount_revenue_ar;
            $journal->journal_tran_id=$j->last_num+1;
            $journal->journal_transactiondate=date("Y-m-d");
            $journal->journal_paydate=date("Y-m-d");
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$sale_id;
            $journal->journal_by_project=$item_stock->item_projectid;
            $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=\Branch::branch_id();
            $journal->journal_parentid=$jj->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::DiscountBySaleID("", $sale_id).' Cancel Deposit';
            $journal->journal_debit=$amount_revenue;
            $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");
            $journal->journal_currency=1;
            $journal->transaction_type='Deposit';
            $journal->journal_referenceid=$sale_id;
            $journal->journal_by_project=$item_stock->item_projectid;
            $journal->journal_by=auth()->user()->id;
            $journal->save();

        }

        $app_id = AppEditPOS::select("id")->where("app_sale_id", $sale_id)->first();
        AppEditPOS::find($app_id->id)->delete();
        \LogActivity::addToLog('Client cancel deposit successfully!');

        return \back()->with('success', 'Client cancel deposit successfully!');
    }

    public function reject_agree_cancel(Request $request)
    {
        $this->validate($request, ['sale_id' => 'required']);

        $sale_id = $request->input("sale_id");

        AppEditPOS::where("app_sale_id", $sale_id)->delete();

        SaleStockModel::where('id', $sale_id)
            ->update(['status_pos_approve_type' => 0]);

        \LogActivity::addToLog('You will be reject cancellation successfully!');
        return \back()->with('success', 'Items Were Successfully rejected');
    }
    /**
     *  Approve deposit
     */
    public function agree_approve(Request $request)
    {

        $salevar_id = $request->app_id;
        SaleStockModel::where('id', $salevar_id)->update(['approve_status' => 1]);
        \LogActivity::addToLog('Approve deposit successfully!');
        return redirect()->route('external-deposit-list.index')
            ->with('success', 'Approve deposit successfully!');
    }
    public function get_installment_info($id)
    {
        $data = DB::table('nso007_approve_instalment as approve')
            ->join('nso007_sale_stock as sale', 'sale.id', 'approve.loan_saleid')
            ->select('approve.*')
            ->where('sale.id', $id)->first();
        $div = '';
        $div .=   '     <div class="col-sm-4">Depreciation​ Duration</div>
                        <div class="col-sm-3"><strong>' . $data->loan_duration . ' Months</strong></div>
                        <div class="col-sm-3">Interest</div>
                        <div class="col-sm-2"><strong>' . $data->loan_interest . ' %</strong></div>
                        <br/>
                        <br/>
                        <div class="col-sm-4">First Depreciation​ On</div>  
                        <div class="col-sm-3"><strong>' . date('Y-m-d', strtotime($data->loan_firstpaydate)) . '</strong></div> 
                        <div class="col-sm-3">Payment Step</div>
                        <div class="col-sm-2"><strong>' . $data->loan_payment_step . '</strong></div>';
        return $div;
    }
    public function UpdateClient(Request $request)
    {
        $client_firstname = $request->client_firstname;
        $client_id = $request->client_id;
        $client_lastname = $request->client_lastname;
        $client_gender = $request->client_gender;
        $clientDOB = $request->clientDOB;
        $clientJOB = $request->clientJOB;
        $clientworkplace = $request->clientworkplace;
        $client_nate = $request->client_nate;
        $client_p1 = $request->client_p1;
        $client_p2 = $request->client_p2;
        $client_idcard = $request->client_idcard;
        $client_date_card = $request->client_date_card;
        $client_validcard = $request->client_validcard;
        $client_address = $request->client_address;

        $client = ClientModel::find($client_id);
        $client->client_firstname = $client_firstname;
        $client->client_lastname = $client_lastname;
        $client->client_gender = $client_gender;
        $client->client_dob = date("Y-m-d", strtotime($clientDOB));
        $client->client_job = $clientJOB;
        $client->client_workplace = $clientworkplace;
        $client->client_national = $client_nate;
        $client->client_phone1 = $client_p1;
        $client->client_phone2 = $client_p2;
        $client->client_cardid = $client_idcard;
        $client->client_datecard = date("Y-m-d", strtotime($client_date_card));
        $client->client_cardvalid = date("Y-m-d", strtotime($client_validcard));
        $client->client_address = $client_address;
        $client->save();

        return $client_firstname . " " . $client_lastname;
    }
    public function client_info($id)
    {
        $client = ClientModel::find($id);
        echo \json_encode($client);
    }
    public function show($client_id,$sale_id)
    {
        $sale_decrypt_id           = Crypt::decrypt($sale_id);
        $client_decrypt_id           =  Crypt::decrypt($client_id);
        $sale_id = $sale_decrypt_id;
        $discount = DiscountModel::pluck('name', 'id')->prepend('Select Discount', '');

        $installment        = ApproveInstalmentModel::where('loan_saleid', $sale_id)->first();
        // dd($installment);

        // $downPayment        = ApproveInstalmentStepModel::where([['approve_instalment_id', $installment->id], ['itempro_status', 1]])->orderBy('id', 'asc')->get();
        $stepPayment        = ApproveInstalmentStepModel::where([['approve_instalment_id', $installment->id], ['itempro_status', 2]])->orderBy('id', 'asc')->get();
        $data_change_rate = DB::table('nso007_loan_installment_change_rate_for_approve')->where("loan_id",$installment->id)->orderBy("id")->get();
        $data_loan_bank = DB::table('nso007_loan_installment_with_bank_for_approve')->where("loan_id",$installment->id)->get();

        //For Create 
        $payment_policies = DB::table('nso007_payment_policy')->pluck("name","id")->prepend("Please Select","");

        $data['client_id']     = $client_decrypt_id;
        $data['sale_id']    = $sale_decrypt_id;
        $sale_policy_payment = DB::table("nso007_sale_policy_payment")
            ->leftjoin('nso007_payment_policy_detail as po_detail', 'po_detail.id', 'nso007_sale_policy_payment.policy_detail_id')
            ->leftjoin('nso007_payment_policy as pa_po', 'pa_po.id', 'nso007_sale_policy_payment.payment_policy_id')
            ->select('po_detail.name as po_detail_name','pa_po.name as pa_po_name','po_detail.id')
            ->where("sale_id",$sale_decrypt_id)
            ->first();

        $client = ClientModel::find($client_decrypt_id);
        $provinces = ProvinceModel::select(
            DB::raw('CONCAT(province_id," ",province_en," ",province_km) AS province_mixed'),
            'province_id'
        )
            ->pluck('province_mixed', 'province_id')
            ->prepend('Please Select', '');

        $data['data']         = DB::select("
    		SELECT sale_discount, sale_amount, sale_remain, sale_receive, agency_id,close_sale_id
    		FROM nso007_sale_stock 
    		WHERE id = $sale_decrypt_id
        ");

        $sale_policy_id = optional(DB::table("nso007_payment_policy_detail")->where("id",$installment->payment_policy_detial_id)->first())->policy_id;
        if(!empty($sale_policy_id)){
            $payment_policy_detail = DB::table('nso007_payment_policy_detail')
                                    ->where('policy_id',$sale_policy_id)
                                    ->pluck('name','id');
        }
        else{
            $payment_policy_detail = [];
        }

        $data['client_name'] = ClientModel::where('id', $client_decrypt_id)->select(DB::raw('concat(client_firstname," ",client_lastname) As client_fullname'))->first();

        $discount = DiscountModel::pluck('name', 'id')->prepend('Select Discount', '');
        $relationship   = ClientRelationshipModel::select('id', DB::raw('CONCAT(clientship_phone1, " | ", clientship_fullname) AS clientship_fullname'))->pluck('clientship_fullname', 'id')->prepend('Please Select', '');
        $parent_id = User::select(DB::Raw('GROUP_CONCAT(id) as ids'))->where([['role', 'Agencies'], ['parent_id', 0]])->first()->ids;
        $agencyLV2 = User::select(DB::raw('GROUP_CONCAT(id) as ids'))->whereIn('parent_id', explode(',', $parent_id))->first()->ids;
        
        $agency         = User::select('id', DB::raw('CONCAT(user_firstname, " ", user_lastname) AS agency_name'))
            ->where([['user_status', 2]])
            ->where(function ($query) use ($agencyLV2) {
                $query->orwhereIn('parent_id', explode(',', $agencyLV2));
                $query->orwhere('agency_type', 1);
            })
            ->pluck('agency_name', 'id')
            ->prepend('Please Select', '');
        
        $closesale = User::where('role', 'Agencies')
            ->select(DB::raw('CONCAT(user_firstname," ",user_lastname) As closesale'), 'id')
            ->pluck('closesale', 'id')->prepend('Select Close Sale', '');
        //End

        $deposit_amount = 0;
        $sale_schedule = optional(DB::table('nso007_deposit_schedule')->where('type',0)->where('ds_saleid',$sale_decrypt_id)->select(DB::raw("SUM(ds_amount) as total_deposit"))->first())->total_deposit;
        if(!empty($sale_schedule)) $deposit_amount = $sale_schedule;

        $down_payments = DB::table('nso007_deposit_schedule')->where('type','!=',0)->where('ds_saleid',$sale_decrypt_id)->get();
        
        return view('administrator.installment.show',compact('sale_policy_payment','data', 'discount', 'client', 'provinces', 'agency','closesale','stepPayment','installment','data_change_rate'), ['relationship' => $relationship,'payment_policies'=>$payment_policies,'sale_policy_id'=>$sale_policy_id,'payment_policy_detail'=>$payment_policy_detail,'payment_policy_detial_id'=>$installment->payment_policy_detial_id,'deposit_amount'=>$deposit_amount,'down_payments'=>$down_payments]);
    }
    public function view_client_installment($id)
    {
        $id           = Crypt::decrypt($id);
        $installment  = DB::table('nso007_approve_instalment as install')
            ->join('nso007_client as client', 'client.id', 'install.loan_clientid')
            ->select('install.*', DB::raw('CONCAT(client_firstname," ",client_lastname) As client_fullname'))
            ->where('install.loan_saleid', $id)->orderBy("id","DESC")->first();

        $deposit      = DepositModel::where('deposit_saleid', $id)
            ->select(DB::raw('SUM(deposit_amount) as amount'))
            ->first();
        $installment_step   = ApproveInstalmentStepModel::where('approve_instalment_id', $installment->id)->orderby('id')->get();
        $installment_change_rate = DB::table("nso007_loan_installment_change_rate_for_approve")->where("loan_id",$installment->id)->get();
        $installment_loan_bank = DB::table("nso007_loan_installment_with_bank_for_approve")->where("loan_id",$installment->id)->get();

        return view('administrator.report-client-payment.view-schedule', compact('installment', 'installment_step','installment_change_rate','installment_loan_bank','id'));
    }
    public function schedulePreview($id)
    {
        $installment  = DB::table('nso007_approve_instalment as install')
            ->join('nso007_client as client', 'client.id', 'install.loan_clientid')
            ->select('install.*', DB::raw('CONCAT(client_firstname," ",client_lastname) As client_fullname'))
            ->where('install.loan_saleid', $id)->first();

        $deposit      = DepositModel::where('deposit_saleid', $id)
            ->select(DB::raw('SUM(deposit_amount) as amount'))
            ->first();
        $installment_step   = ApproveInstalmentStepModel::where('approve_instalment_id', $installment->id)->orderby('id')->get();
        $installment_change_rate = DB::table("nso007_loan_installment_change_rate_for_approve")->where("loan_id",$installment->id)->get();
        $installment_loan_bank = DB::table("nso007_loan_installment_with_bank_for_approve")->where("loan_id",$installment->id)->get();
        $branch = BranchModel::where('id', \Branch::branch_id())->first();
        return view('administrator.report-client-payment.print-schedule', compact('installment', 'installment_step', 'branch','installment_change_rate','installment_loan_bank'));
    }
    public function update_installment(Request $rq, $id)
    {
        //member_discount_amount
        $check              = false;
        $saleID             = $rq->salename_id;
        $dpsAmount          = $this->getdpsAmountBySaleID($saleID);
        $clientID           = $rq->clientname_id;
        $agencyID           = $rq->agencyname_id;
        $agnecyCL           = $rq->loan_close_saleid;
        $discount           = $rq->sale_discount;
        $discountCmm        = $rq->discount_commis;
        $totalCost          = $rq->item_cost;
        $interest           = $rq->loan_interest;
        $duration           = $rq->loan_duration;
        $type               = $rq->loan_type;
        $disbursement       = date('Y-m-d', strtotime($rq->loan_disbursement));
        $loanStartDate      = date('Y-m-d', strtotime($rq->loan_firstpaydate));
        $remainingBalance   = $totalCost - $dpsAmount - $discount - $discountCmm;
        $relationship       = $rq->clientship_id;
        $shipStatus         = $rq->ship_status;
        $dp_amount          = $rq->itempro_payamount_dp;
        $dp_date            = $rq->itempro_date_dp;
        $st_amount          = $rq->itempro_payamount;
        $st_date            = $rq->itempro_date;

        foreach ($dp_amount as $dp) {
            $remainingBalance -= $dp;
        }
        foreach ($st_amount as $st) {
            $remainingBalance -= $st;
        }
        if (($remainingBalance > 0 || $remainingBalance < 0) && (empty($duration) || $interest == null || empty($type))) {
            return redirect()->back()->with('danger', 'Incorrect Data, Please try again!!!');
        }
        $remainingBalance = $totalCost - $dpsAmount - $discount - $discountCmm;
        //update installment info
        ApproveInstalmentModel::where('id', $id)->update([
            'branch_id'         => Branch::branch_id(),
            'loan_close_saleid' => $agnecyCL,
            'agency_id'         => $agencyID,
            'loan_clientid'     => $clientID,
            'loan_amount'       => $totalCost,
            'loan_discount'     => $discount,
            'discount_commis'   => $discountCmm,
            'loan_saleid'       => $saleID,
            'loan_disbursement' => $disbursement,
            'loan_staff'        => Auth::user()->id,
            'loan_createdate'   => date('Y-m-d'),
            'loan_amountos'     => $remainingBalance,
            'deposit_amount'    => $dpsAmount,
            'loan_duration'     => null,
            'loan_interest'     => null,
            'loan_type'         => null,
            'discount_type'     => $rq->discount_type,
            'discounttype_amount' => $rq->member_discount_amount
        ]);
        //update installment info
        ApproveInstalmentStepModel::where('approve_instalment_id', $id)->delete();
        //down payment

        if (!empty($dp_amount[0]) && $dp_amount[0] > 0) {
            $check = true;
            foreach ($dp_amount as $key => $amount) {
                $remainingBalance -= $amount;
                ApproveInstalmentStepModel::insert([
                    'approve_instalment_id' => $id,
                    'itempro_date'          => date('Y-m-d', strtotime($dp_date[$key])),
                    'itempro_payamount'     => $amount,
                    'itempro_status'        => 1
                ]);
            }
        }
        //down payment

        //step payment

        if (!empty($st_amount[0]) && $st_amount[0] > 0) {
            $check = true;
            foreach ($st_amount as $key => $amount) {
                $remainingBalance -= $amount;
                ApproveInstalmentStepModel::insert([
                    'approve_instalment_id' => $id,
                    'itempro_date'          => date('Y-m-d', strtotime($st_date[$key])),
                    'itempro_payamount'     => $amount,
                    'itempro_status'        => 2
                ]);
            }
        }
        //step payment
        if ($remainingBalance > 0) {
            ApproveInstalmentModel::where('id', $id)->update([
                'loan_duration'     => $duration,
                'loan_interest'     => $interest,
                'loan_type'         => $type,
                'loan_firstpaydate' => $loanStartDate
            ]);
        }
        if (!empty($rq->north_border) || !empty($rq->south_border) || !empty($rq->east_border) || !empty($rq->west_border)) {
            $border = BorderModel::updateOrCreate(['loan_id' => $id], [
                'loan_id'       => $id,
                'north_border'  => $rq->north_border,
                'south_border'  => $rq->south_border,
                'east_border'   => $rq->east_border,
                'west_border'   => $rq->west_border
            ]);
            $border->loan_id        = $id;
            $border->north_border   = $rq->north_border;
            $border->south_border   = $rq->south_border;
            $border->east_border    = $rq->east_border;
            $border->west_border    = $rq->west_border;
            $border->save();
        }
        if (!empty($rq->clientship_id)) {
            $client_relationid = implode(',', $rq->clientship_id);
            RelationshipStatus::where('loan_id', $id)->delete();
            foreach ($relationship as $j => $shipID) {
                RelationshipStatus::insert([
                    'loan_id'               => $id,
                    'clientship_id'         => $shipID,
                    'relationship_status'   => $shipStatus[$j]
                ]);
            }
        }

        \LogActivity::addToLog('Installment has updated successfully!');
        return redirect()->route('items-deposit.index', 1)
            ->with('success', 'Installment Updated successfully!');
    }
    public function get_payment_policy_detail($id){
        $payment_policy_detail = DB::table('nso007_payment_policy_detail')->where("id",$id)->first();
        if($payment_policy_detail->loan_generate_type==2){
            return [
                'loan_generate_type'=>2,
                'data'=>DB::table("nso007_payment_policy_detail_loan_with_interest")->where("payment_policy_detail_id",$id)->first()
            ];
        }
        else if($payment_policy_detail->loan_generate_type==3){
            return [
                'loan_generate_type'=>3,
                'data'=>DB::table("nso007_payment_policy_detail_loan_with_interest")->where("payment_policy_detail_id",$id)->first(),
                'data_change_interest'=>DB::table("nso007_payment_policy_detail_loan_change_interest")->where("payment_policy_detail_id",$id)->orderBy('id','ASC')->get()
            ];
        }
        else if($payment_policy_detail->loan_generate_type==4 || $payment_policy_detail->loan_generate_type==5){
            return [
                'loan_generate_type'=> $payment_policy_detail->loan_generate_type,
                'data'=>DB::table("nso007_payment_policy_detail_loan_without_interest")->where("payment_policy_detail_id",$id)->get(),
                'data_loan'=>DB::table("nso007_payment_policy_detail_loan_with_interest")->where("payment_policy_detail_id",$id)->first()
            ];
        }
        else{
            return [
                'loan_generate_type'=>1,
                'data'=>DB::table("nso007_payment_policy_detail_loan_without_interest")->where("payment_policy_detail_id",$id)->get()
            ];
        }
    }
}
