Customer Wise Revenue Calculation

Month Wise Revenue Calculation - Pride Financial Services Private Limited

Month Wise Revenue Calculation - Blacksoil Capital Private Limited

Customer Wise Revenue Calculation

Month Wise Revenue Calculation - Pride Financial Services Private Limited

Month Wise Revenue Calculation - K. M. Global Credit Private Limited

Customer Wise Revenue Calculation

Month Wise Revenue Calculation - Pride Financial Services Private Limited

Month Wise Revenue Calculation - K. M. Global Credit Private Limited

";print_r($payment['finReference']);die;
        $noc_payload = ['id' => $id, 'fin_reference_id'=>$payment['finReference'],'type'=>'NOC'];
        $noc_response = (new AvanseProcesses(new Request($noc_payload)))->getStatement();
        if($noc_response == $payment['finReference']){
            $s3 = Storage::disk('s3');
            $file_path = ConsumerApplicationFiles::where('consumer_application_id',$id)->where('file_type','NOC')->first(['file_path']);
            if($s3->exists($file_path['file_path'])){
                $noc_doc = $s3->get($file_path['file_path']);
                $request = new Request();
                $request->request->add([
                    'type' => "Closures Letters",
                    'bot' => true
                ]);
                $document = (new ConsumerDocument($request));
                $documentDetails = $document->getDocument($id);
                $email = $documentDetails['user']['customer_email'];
                \Mail::send('emails.reminders.basic_reminder',
                ['body' => view('emails.reminders.closure_letter', $documentDetails)]
                , function ($message) use ($email, $noc_doc,$documentDetails) {
                    $message->to($email)->subject('Closure of Your Loan (Agreement Number - ' . $documentDetails['user']['application_id'] . ')');
                    $message->cc('centralops@creditfair.in');
                    if($noc_doc){
                        $message->attachData($noc_doc, 'NOC');
                    }
                });
                AvanseNoc::where('application_id',$id)->update(['execution_status'=>'success']);
            }
            $data = array('id'=>$id, 'status' => 'success');
        }else{
            $data = array('id'=>$id, 'status' => 'failed');
        }
        echo json_encode($data);
    }


    public function getPayoutReport(Request $request){
        $from_date = $request->get('from_date') ?? Carbon::now()->startOfMonth()->toDateString();
        $to_date = $request->get('to_date') ?? Carbon::now()->endOfMonth()->toDateString();
        $lender_id = $request->get('lender_id') ?? '';
        $merchant_id = $request->get('merchant_id') ?? '';

        if($request->get('export')){
            $lender_condition = $condition = $joiner = $merchant_condition = '';

            if(!empty($lender_id)){
                $lender_condition = "consumer_application.lender_id = $lender_id";
            }
            if(!empty($merchant_id)){
                $merchant_condition = "consumer_application.merchant_id = $merchant_id";
            }
            if(!empty($lender_id) || !empty($merchant_id)){
                $condition = 'WHERE';
            }
            if(!empty($lender_id) && !empty($merchant_id)){
                $joiner = 'AND';
            }

            $db_data = DB::select("SELECT DISTINCT 
                consumer_application.id, 
                pro_forma_invoice.loan_application_no, 
                avanse_finance.finReference, 
                consumer_application.name as customer_name, 
                consumer_application.co_applicant_name, 
                consumer_application.co_applicant_mobile_number, 
                merchant.merchant_name, 
                merchant.gst_number, 
                lenders.name, 
                consumer_application.email, 
                consumer_application.mobile_number, 
                consumer_application.approved_amount, 
                i.amount as subvention_taxable, 
                emi_calculation.tenure, 
                consumer_application_element.reference_number,
                emi_calculation.advance_emi, 
                emi_calculation.interest_rate, 
                emi_calculation.subvention, 
                emi_calculation.processing_fee, 
                emi_calculation.emi, 
                emi_calculation.subvention_igst, 
                emi_calculation.subvention_cgst, 
                emi_calculation.subvention_sgst, 
                ((emi_calculation.upfront_from_credit_fair - emi_calculation.subvention_igst - emi_calculation.subvention_cgst - emi_calculation.subvention_sgst) * (100 - COALESCE(ll.liability_percent, 0)))/ 100 as net_disbursment,
                (SELECT umrn FROM mandate WHERE mandate.consumer_application_id = consumer_application.id AND MODE != 'lotuspay' AND umrn != '' LIMIT 1) AS nach_status,
                users.name AS advisor, 
                collection_executive.name AS collection_executive, 
                consumer_application.current_address_city, 
                consumer_application.current_address_state, 
                consumer_application.state, 
                dl.disbAmount AS paid_amount, 
                log.created_at AS disbursed_date
                FROM
                    consumer_application
                LEFT JOIN
                    (SELECT application_id, loan_application_no FROM pro_forma_invoice) pro_forma_invoice ON pro_forma_invoice.application_id = consumer_application.id
                LEFT JOIN
                    (SELECT application_id, disbAmount FROM avanse_disbursement) dl ON dl.application_id = consumer_application.id
                LEFT JOIN
                    (SELECT collection_advisor, application_id,reference_number FROM consumer_application_element) consumer_application_element ON consumer_application_element.application_id = consumer_application.id
                LEFT JOIN
                    (SELECT id, name FROM users) AS collection_executive ON collection_executive.id = consumer_application_element.collection_advisor
                LEFT JOIN
                    (SELECT name,id FROM users) users ON users.id = consumer_application.created_by
                LEFT JOIN
                    (SELECT id, merchant_name, gst_number FROM merchant) merchant ON merchant.id = consumer_application.merchant_id
                LEFT JOIN
                    (SELECT name, id FROM lenders) lenders ON lenders.id = consumer_application.lender_id
                LEFT JOIN
                    (select application_id , liability_percent from liability_log) ll on ll.application_id = consumer_application.id
                LEFT JOIN
                    (SELECT application_id, tenure, advance_emi, interest_rate, subvention, processing_fee, emi, subvention_igst, subvention_cgst, subvention_sgst, upfront_from_credit_fair FROM emi_calculation) emi_calculation ON emi_calculation.application_id = consumer_application.id
                LEFT JOIN
                    (SELECT finReference, application_id FROM avanse_finance) avanse_finance ON avanse_finance.application_id = consumer_application.id
                LEFT JOIN
                    (SELECT i.application_id, i.amount FROM invoices i WHERE TYPE = 'subvention_invoice' and deleted_at is null) i ON i.application_id = consumer_application.id
                INNER JOIN
                    (SELECT created_at, application_id FROM status_logs log WHERE status = 'disbursed' and created_at >= '".$from_date."' and created_at <= '".$to_date."') log on log.application_id = consumer_application.id
                ".$condition." ".$lender_condition." ".$joiner." ".$merchant_condition." order by disbursed_date desc");

            $exporter[] = ['ID','Loan Application Number','Fin Reference', 'Customer Name', 'Co-Applicant Name', 'Co-Applicant Number',
                'Merchant Name', 'GST number', 'Name', 'Email', 'Mobile Number', 'Approved Amount', 'Subvention taxable', 'Tenure', 'UTR',
                'Advance Emi', 'Interest Rate', 'Subvention', 'Processing Fee', 'EMI', 'Subvention IGST', 'Subvention CGST', 'Subvention SGST',
                'Net Disbursement', 'Nach Status', 'Advisor', 'Collection Executive', 'Current Address City', 'Current Address State', 'State',
                'Paid Amount', 'Disbursed Date'];
            foreach ($db_data as $value){
                $exporter[] = [
                    'id' => $value->id,
                    'loan_application_number' => $value->loan_application_no,
                    'finReference' => $value->finReference,
                    'customer_name' => $value->customer_name,
                    'co_applicant_name' => $value->co_applicant_name,
                    'co_applicant_mobile_number' => wall_decrypt($value->co_applicant_mobile_number),
                    'merchant_name' => $value->merchant_name,
                    'gst_number' => wall_decrypt($value->gst_number),
                    'name' => $value->name,
                    'email' => wall_decrypt($value->email),
                    'mobile_number' => wall_decrypt($value->mobile_number),
                    'approved_amount' => $value->approved_amount,
                    'subvention_taxable' => $value->subvention_taxable,
                    'tenure' => $value->tenure,
                    'reference_number' => $value->reference_number,
                    'advance_emi' => $value->advance_emi,
                    'interest_rate' => $value->interest_rate,
                    'subvention' => $value->subvention,
                    'processing_fee' => $value->processing_fee,
                    'emi' => $value->emi,
                    'subvention_igst' => $value->subvention_igst,
                    'subvention_cgst' => $value->subvention_cgst,
                    'subvention_sgst' => $value->subvention_sgst,
                    'net_disbursment' => $value->net_disbursment,
                    'nach_status' => $value->nach_status,
                    'advisor' => $value->advisor,
                    'collection_executive' => $value->collection_executive,
                    'current_address_city' => $value->current_address_city,
                    'current_address_state' => $value->current_address_state,
                    'state' => $value->state,
                    'paid_amount' => $value->paid_amount,
                    'disbursed_date' => $value->disbursed_date,
                ];
            }

            $request = new Request(['data'=>$exporter,'sheet'=>'Sheet1']);
            $file = 'Payout_Report_'.$from_date.'_to_'.$to_date.'.xls';
//            return Excel::download((new Exporter($request)), 'Payout_Report_'.$from_date.'_to_'.$to_date.'.xls');
            Excel::store((new Exporter($request)), $file);

            Mail::send('emails.reminders.basic_reminder', [
                'body' => 'Payout Report',
            ], function ($message) use ($file) {
                $message->to(['surbhi@creditfair.in','ravi.mehta@creditfair.in'])
                    ->cc(['rohit@creditfair.in', 'vikas@creditfair.in', 'aditya@creditfair.in', 'sarah@creditfair.in', 'sunil@creditfair.in'])
                    ->subject('Payout Report');
                $message->attachData(file_get_contents(storage_path() . '/app/' . $file), $file);
            });
            unlink(storage_path() . '/app/' . $file);
            \Session::flash('success', 'You\'ll will receive the report on email');
        }

        $data['from_date'] = $from_date;
        $data['to_date'] = $to_date;
        $data['merchants'] = Merchant::where('status','active')->where('is_guest_active',1)->get(['id','merchant_name']);
        $data['lenders'] = Lenders::all();

        return view('admin.reports.payouts', $data);
    }


    public function getMonthlySubvention($send_mail = false){
        $inv_start_date = Carbon::now()->subMonth(1)->startOfMonth();
        $inv_end_date = Carbon::now()->subMonth(1)->endOfMonth();
        $invoice_date = Carbon::now()->subMonth(1);

        $lender = Lenders::with('bank_details')->where('id', 2)->first();

        $disb_logs = DisbursementLogs::where('phase', '>', 1)
            ->where('created_at', '>=', $inv_start_date)
            ->where('created_at', '<=', $inv_end_date)->whereNull('deleted_at')->get(['application_id']);

        $status_logs = StatusLogs::where('status', 'disbursed')->where('created_at', '>=', $inv_start_date)
            ->where('created_at', '<=', $inv_end_date)->where('is_reversed',0)->get(['application_id']);

        $applications=[];
        $common_apps=[];
        foreach($disb_logs as $log){
            if(!in_array($log->application_id, $applications))
                $applications[] = $log->application_id;
        }
        foreach($status_logs as $log){
            if(!in_array($log->application_id, $applications))
                $applications[] = $log->application_id;
            else
                $common_apps[] = $log->application_id;
        }


        $data = ConsumerApplication::with('proFormaInvoice','lender','merchant', 'emi', 'offer', 'lender.bank_details', 'application_element.merchant_center', 'application_emi_details')
            ->whereIn('id', $applications)->where('lender_id', 2)
            ->whereNotIn('merchant_id', [73,6478,7516])->whereIn('state', ['disbursed', 'nach registered', 'closed', 'cancelled'])
            ->whereNull('is_deleted')->get()->groupBy(['merchant.id', 'application_element.merchant_center.id']);

//        DB::statement("SET sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));");
//
//        $applications = DB::select("select * from status_logs
//                  left join consumer_application on status_logs.application_id=consumer_application.id
//                  left join consumer_application_element on consumer_application_element.application_id=consumer_application.id
//                  left join pro_forma_invoice on pro_forma_invoice.application_id=consumer_application.id
//                  left join lenders on lenders.id=consumer_application.lender_id
//                  left join merchant on merchant.id=consumer_application.merchant_id
//                  left join emi_calculation on emi_calculation.id=emi_calculation.application_id
//                  left join merchant_offers on merchant_offers.id=consumer_application.offer_id
//where consumer_application.id = status_logs.application_id and status_logs.status = 'disbursed' and consumer_application.state in ('disbursed', 'nach registered')
//  and consumer_application.merchant_id=26 and consumer_application.lender_id=2 and status_logs.created_at >= '2022-09-01 00:00:00'
//  and status_logs.created_at <= '2022-11-09 23:59:59' and status_logs.is_reversed = 0 group by status_logs.application_id;");
//
//        DB::statement("SET sql_mode=(SELECT CONCAT(@@sql_mode, ',ONLY_FULL_GROUP_BY'));");
//
//
//        return $data;
//
//        $data = ConsumerApplication::with('proFormaInvoice','lender','merchant', 'emi', 'offer', 'lender.bank_details', 'disbursement_log')
//            ->where(function ($query) {
////                $query->where(function ($x) {
//                $query->whereHas('logs', function ($q) use ($query){
//                    $query->where('status', 'disbursed')->where('created_at', '>=', '2022-11-01 00:00:00')
//                            ->where('created_at', '<=', '2022-11-30 23:59:59')->where('is_reversed', 0);
//                    })
////                })->orWhere(function ($y) {
//                    ->orWhereHas('disbursement_log', function ($z) use ($query) {
//                        $query->where('phase', 1)->where('created_at', '>=', '2022-11-01 00:00:00')
//                            ->where('created_at', '<=', '2022-11-30 23:59:59')->whereNull('deleted_at');
//                    });
////                });
//            })->where('lender_id', 2)->whereIn('state', ['disbursed', 'nach registered'])->whereNull('is_deleted')
//            ->get()->groupBy(['merchant.id']);

        $merchant_count = 0;
        foreach($data as $merchant => $m_applications) {
            $merchant_details = Merchant::with('centers')->where('id', $merchant)->first();
            if (!in_array($merchant, _env('APP_CONNECTION') ? [6478, 73] : []) && !in_array($merchant, [])) {

                $allItems = new \Illuminate\Database\Eloquent\Collection;
                $items = new \Illuminate\Database\Eloquent\Collection;

                if ($merchant_details->subv_inv_on_center == 1) {
                    $allItems = $m_applications;
                }else{
                    foreach ($m_applications as $center => $applications) {
                        $items = $items->concat($applications);
                    }
                    $allItems = $allItems->push($items);
                }

                foreach ($allItems as $center => $applications) {
                    $center = $center != "" ? $center : 0;

                    $center_details = MerchantLocation::where('id', $center)->first();
                    $type = 'subvention_invoice';

                    if ($merchant_details->subv_inv_on_center == 1) {
                        $invoiceExists = Invoices::where('application_id', 0)->where('merchant_id', $merchant)->where('center_id', $center)
                            ->where('invoice_date', $inv_end_date->toDateTimeString())->where('type', $type)->first(['id']);
                    } else {
                        $invoiceExists = Invoices::where('application_id', 0)->where('merchant_id', $merchant)
                            ->where('invoice_date', $inv_end_date->toDateTimeString())->where('type', $type)->first(['id']);
                    }

                    if (!isset($invoiceExists->id)) {
                        $request = new Request();
                        $request->request->add([
                            'type' => 'Merchant Disbursed',
                            'bot' => true,
                            'test_bot' => 1
                        ]);

                        $document = (new ConsumerDocument($request));
                        $documentDetails = [];
                        $inv_applications = [];
                        $net_total = 0;
                        $igst_total = 0;
                        $cgst_total = 0;
                        $sgst_total = 0;
                        $amount_total = 0;

                        $merchant_gst_number = $merchant_details->gst_number ?? '';
                        $center_gst_number = $center_details->gst_number ?? '';
                        $official_merchant_name = $merchant_details->merchant_name ?? '';
                        $center_name = $center_details->name ?? $official_merchant_name;
                        $merchant_address = isset($merchant_details->address) ? ucwords(strtolower($merchant_details->address)) : '';
                        $center_address = isset($center_details->location) ? ucwords(strtolower($center_details->location)) : $merchant_address;
                        $merchant_state = isset($merchant_details->state) && !empty($merchant_details->state) ? $merchant_details->state : '';
                        $center_state = isset($center_details->state) && !empty($center_details->state) ? $center_details->state : $merchant_state;

                        foreach ($applications as $key => $application) {
                            if (isset($application->emi->subvention) && !empty($application->emi->subvention) && isset($application->merchant->is_gst) && $application->merchant->is_gst == 1 && isset($application->merchant->state) && !empty($application->merchant->state) && isset($application->proFormaInvoice->loan_application_no)) {
                                if(isset($application->application_emi_details) && isset($application->application_emi_details->data)) {
                                    $app_log = json_decode($application->application_emi_details->data, 1);
                                    $application->merchant->charge_subvention_remaining_amount = $app_log['merchant']['charge_subvention_remaining_amount'];
                                }

                                $downPayment = 0;

                                if ($application->merchant->charge_subvention_remaining_amount) {
                                    $downPayment = $application->emi->upfront_from_customer;
                                }

                                $subvention_amount = (($application->approved_amount - $downPayment) * $application->emi->subvention) / 100;

                                if(!$application->offer){
                                    $application->offer = MerchantOffers::where('id', $application->offer_id)->withTrashed()->first();
                                }

//                                if (in_array($application->id, $common_apps)) {
//                                    unset($common_apps[$application->id]);
//                                } else {
                                    $multiStage = new MultiStageDisbursement();
                                    if ($multiStage->canPartialDisbursement($application->emi->offer_id, $application)) {
                                        $disbursement_phase = $multiStage->isPartialDisbursementforMonth($application, $inv_start_date, $inv_end_date);
                                        if (is_bool($disbursement_phase))
                                            return $subvention_amount;


                                        if(count($disbursement_phase) == 1) {
                                            $disbursementOffers = new DisbursementOffers();
                                            $disbursementOffersData = $disbursementOffers->getCurrentOfferDisbursement($application->emi->offer_id, $disbursement_phase[0]->phase, $application->id);
                                            if (!isset($disbursementOffersData->per_disbursement_amount)) {
                                                (new Slack(new Request(['title' => 'Disbursement log missing', 'fields' => ['app' => $application->id], 'group' => Slack::$STAGE_TESTING])))->send();
                                            }
                                            if ($application->merchant && $application->merchant->charge_subvention_remaining_amount /*&& count($disbursement_phase) == 1*/) {
                                                if ($disbursement_phase[0]->phase == 1) {
                                                    $multiStageSubventionAmount = ((($application->approved_amount * $disbursementOffersData->per_disbursement_amount) / 100) - $application->emi->upfront_from_customer) * $application->emi->subvention / 100;
                                                    $subventionAmountOLD = (($application->approved_amount - $application->emi->upfront_from_customer) * $application->emi->subvention) / 100;
                                                    if (round($multiStage->upFrontFromCreditFair($application, true)) == round($application->emi->upfront_from_credit_fair)) {
                                                        $newUpFrontFrontCreditFair = (($application->emi->upfront_from_credit_fair + $subventionAmountOLD) - $multiStageSubventionAmount);
                                                        ApplicationEMICalculation::where('application_id', $application->id)->update(['upfront_from_credit_fair' => $newUpFrontFrontCreditFair]);
                                                    }
                                                } else {
                                                    $multiStageSubventionAmount = (($application->approved_amount * $disbursementOffersData->per_disbursement_amount) / 100) * $application->emi->subvention / 100;
                                                }
                                            } else {
//                                                $downPayment = 0;
//                                                if ($application->merchant->charge_subvention_remaining_amount && count($disbursement_phase) == 1) {
//                                                    $downPayment = $application->emi->upfront_from_customer;
//                                                }
//                                                $subvention_amount = (($application->approved_amount - $downPayment) * $application->emi->subvention) / 100;
                                                $multiStageSubventionAmount = $multiStage->getMultiStageSubventionAmount($subvention_amount, $disbursementOffersData);
                                            }
                                            $subvention_amount = $multiStageSubventionAmount;
                                        }
                                    }
//                                }

                                $gstDetails = $document->getGSTDetails(
                                    [
                                        'amount' => $subvention_amount,
                                        'state' => $merchant_details->subv_inv_on_center == 1 ? $center_state : $merchant_state,
                                    ], 0
                                );

                                $amt = (isset($gstDetails[0])) ? $gstDetails[0]['amount'] : 0;
                                if ($amt) {
                                    if ($gstDetails) {
                                        $response = array();
                                        foreach ($gstDetails as $gst) {
                                            if (isset($gst['taxes']) && $gst['taxes'] == "CGST") {
                                                $response['subvention_cgst'] = $gst['gst_amount'];
                                            } elseif (isset($gst['taxes']) && $gst['taxes'] == "SGST") {
                                                $response['subvention_sgst'] = $gst['gst_amount'];
                                            } else {
                                                $response['subvention_igst'] = $gst['gst_amount'];
                                            }
                                        }
                                    }

                                    $igst = (isset($response['subvention_igst'])) ? $response['subvention_igst'] : 0;
                                    $cgst = (isset($response['subvention_cgst'])) ? $response['subvention_cgst'] : 0;
                                    $sgst = (isset($response['subvention_sgst'])) ? $response['subvention_sgst'] : 0;

                                    $inv_applications[$key] = [
                                        'disbursement_date' => $document->getDisbursedDate($application->id),
                                        'customer_name' => $application->name,
                                        'id' => $application->proFormaInvoice->loan_application_no,
                                        'net_amount' => (isset($gstDetails[0])) ? $gstDetails[0]['net_amount'] : 0,
                                        'igst' => $igst,
                                        'cgst' => $cgst,
                                        'sgst' => $sgst,
                                        'amount' => (isset($gstDetails[0])) ? $gstDetails[0]['net_amount']+$igst+$cgst+$sgst : 0,
                                    ];

                                    $net_total += (isset($gstDetails[0])) ? $gstDetails[0]['net_amount'] : 0;
                                    $igst_total += $igst;
                                    $cgst_total += $cgst;
                                    $sgst_total += $sgst;
                                    $amount_total += (isset($gstDetails[0])) ? $gstDetails[0]['net_amount']+$igst+$cgst+$sgst : 0;
                                }
                            }
                        }

                        if ($amount_total > 0 && $net_total > 0) {
                            try {

                                $created_at = Carbon::now()->toDateTimeString();
                                sleep(5);
                                $invoiceId = (new ConsumerApplicationController())->getInvoiceID($merchant, $type, true);
                                $invoiceDetails = [
                                    'application_id' => 0,
                                    'merchant_id' => $merchant,
                                    'center_id' => $center,
                                    'invoice_id' => $invoiceId,
                                    'amount' => $amount_total,
                                    'net_amount' => $net_total,
                                    'igst' => $igst_total,
                                    'cgst' => $cgst_total,
                                    'sgst' => $sgst_total,
                                    'lender_id' => 2 /*KM cases only*/,
                                    'type' => $type,
                                    'phase_id' => $multiStageDisbursement['phase_id'] ?? null,
                                    'status' => 1,
                                    'created_at' => $created_at,
                                    'invoice_date' => $inv_end_date
                                ];

                                Invoices::insert($invoiceDetails);

                                $getGSTDetailsSubvention = [];

                                if ($invoiceDetails['igst']) {
                                    $getGSTDetailsSubvention[] = ['tax_type' => 18, 'taxes' => 'IGST',
                                        'gst_amount' => $invoiceDetails['igst'] > 0 ? $invoiceDetails['igst'] : ($invoiceDetails['amount'] * 0.18),
                                        'net_amount' => $invoiceDetails['net_amount']];
                                }
                                if ($invoiceDetails['cgst']) {
                                    $getGSTDetailsSubvention[] = ['tax_type' => 9, 'taxes' => 'CGST',
                                        'gst_amount' => $invoiceDetails['cgst'] > 0 ? $invoiceDetails['cgst'] : ($invoiceDetails['amount'] * 0.09),
                                        'net_amount' => $invoiceDetails['net_amount']];
                                }
                                if ($invoiceDetails['sgst']) {
                                    $getGSTDetailsSubvention[] = ['tax_type' => 9, 'taxes' => 'SGST',
                                        'gst_amount' => $invoiceDetails['sgst'] > 0 ? $invoiceDetails['sgst'] : ($invoiceDetails['amount'] * 0.09),
                                        'net_amount' => $invoiceDetails['net_amount']];
                                }

                                $documentDetails = [
                                    'invoice_number' => $invoiceId,
                                    'total_subvention' => (isset($gstDetails[0])) ? $gstDetails[0]['amount'] : 0,
                                    'invoice_date' => $invoice_date->endOfMonth(),
                                    'net_amount' => (isset($gstDetails[0])) ? $gstDetails[0]['net_amount'] : 0,
                                    'lender' => $lender,
                                    'lender_bank' => $lender->bank_details,
                                    'getGSTDetailsSubvention' => $getGSTDetailsSubvention,
                                    'applications' => $inv_applications,
                                    'invoice' => $invoiceDetails,
                                    'merchant_name' => $merchant_details->subv_inv_on_center == 1 ? $center_name : $official_merchant_name,
                                    'merchant_address' => $merchant_details->subv_inv_on_center == 1 ? $center_address : $merchant_address,
                                    'merchant_gst_number' => $merchant_details->subv_inv_on_center == 1 ? $center_gst_number : $merchant_gst_number,
                                ];

                            } catch (\Exception $e) {
                                (new \App\Slack(new Request(['title' => 'Monthly Invoice Creation Error', 'fields' => [
                                    'error' => $e->getMessage(), 'line' => $e->getLine(), 'trace' => $e->getTraceAsString(),
                                    'application_id' => $application->id, 'id_type' => 'application_id']])))->send();
                                $cwFactory = new CloudWatchLoggerFactory();
                                $_data = array(
                                    'exception' => 'Monthly Invoice Creation Error',
                                    'message' => $e->getMessage(),
                                    'line' => $e->getLine(),
                                    'trace' => $e->getTraceAsString(),
                                    'application_id' => $application->id, 'id_type' => 'application_id'
                                );
                                $cwFactory->logger(json_encode($_data));
                            }

                            if (!empty($documentDetails)) {
                                $data = [
                                    'txt' => view('admin.documents.merchant_subvention_fee_invoice', $documentDetails),
                                    'test_bot' => 1
                                ];

                                $request->request->add($data);
                                $document->isMerchantRequest = 1;


                                $merchant_email = explode(',', trim($merchant_details->email));
                                if($center > 0 && $center_details->email){
                                    $center_email = explode(',', trim($center_details->email));
                                }

                                $to = $merchant_email;
                                $cc = ['surbhi@creditfair.in', 'ravi.mehta@creditfair.in'];
//                                $cc = ['sarah@creditfair.in'];
                                if($center > 0 && isset($center_email)){
                                    $to = $center_email;
                                    $cc = array_merge($cc, $merchant_email);
                                }

                                // return $documentDetails;

                                $pdf = App::make('snappy.pdf.wrapper');
                                $pdf->loadHTML(view('admin.documents.merchant_subvention_fee_invoice', $documentDetails)
                                )->setOption('encoding', 'UTF-8')->setPaper('a4')->setOrientation('portrait')
                                    ->setTemporaryFolder(storage_path() . '/')->setOption('enable-local-file-access', true)->setOption('margin-bottom', 20);
                                $invoice = $pdf->setOption('footer-html', view('admin.editor.footer', ['type' => 'Merchant Disbursed',
                                    'lender' => $lender])->render());


                                // return $invoice->inline();

                                $file_name = str_replace(' ', '_', strtolower($request->get('type'))) . '_' . $invoice_date->format('M') . '_' . $invoice_date->format('Y') . '_' . $center . '.pdf';

                                if($send_mail) {
                                    $subject = $merchant_details->merchant_name . ': Subvention Invoice for ' . $invoice_date->format('M') . '-' . $invoice_date->format('Y');

                                    try {
                                        Mail::send('emails.reminders.basic_reminder', [
                                            'body' => view('emails.reminders.monthly_subvention_invoice_mail', ['merchant_name' => $official_merchant_name, 'date' => $invoice_date->format('M') . ' ' . $invoice_date->format('Y')]),
                                            'lender_id' => $lender->id,
                                        ], function ($message) use ($file_name, $to, $cc, $invoice, $subject) {
                                            $message->to($to)
                                                ->cc($cc)->bcc(['admin@creditfare.in', 'sarah@creditfair.in'])
                                                ->subject($subject);
                                            $message->attachData($invoice->output(), $file_name);
                                        });
                                    } catch (\Exception $e) {
                                        (new \App\Slack(new Request(['title' => 'Email id failed', 'fields' => ['Merchant ID' => $merchant_details->id, 'Merchant Name' => $merchant_details->merchant_name], 'group' => Slack::$STAGE_TESTING])))->send();
                                    }
                                }


                                $file_path = '/merchant/' . $merchant . '/subvention_invoices/' . $invoice_date->format('M') . '_' . $invoice_date->format('Y') . '/' . $file_name;
                                $s3 = Storage::disk('s3');
                                $s3->put($file_path, $invoice->output());
                                $merchant_count += 1;
                            }
                        }
                    }
                }
            }
        }
        (new \App\Slack(new Request(['title' => 'Monthly Invoice Generated and Sent', 'fields' => ['InvoiceCount' => $merchant_count], 'group' => Slack::$STAGE_TESTING])))->send();
        $cwFactory = new CloudWatchLoggerFactory();
        $_data = array(
            'message' => 'Monthly Invoice Generated and Sent',
            'InvoiceCount' => $merchant_count,
        );
        $cwFactory->logger(json_encode($_data));
        return true;
    }

    public function exportSubventionInvoice(Request $request){
        try {
            $invoice_date = $request->get('invoice_date');
            $date = Carbon::createFromDate($request->get('year'), $request->get('month'))->endOfMonth();
            $merchant_id = $request->get('merchant_id');
            $user_id = $request->get('user_id');
            $type = $request->get('type');


            $query = Invoices::with('merchant', 'center')->where('invoice_date', $invoice_date)->where('application_id', 0);
            if (!is_null($merchant_id) && $merchant_id != "") {
                $query = $query->where('merchant_id', $merchant_id);
            }
            $invoices = $query->get();

            $time = time();

            $user = User::where('id', $user_id)->first(['email']);

            if ($type == 'zip') {
                $zip = new \ZipArchive();
                $zipFileName = storage_path() . '/app/Subv_invoices_' . $date->format('M') . '_' . $date->format('Y') . '_' . $time . '.zip';
                $res = $zip->open($zipFileName, \ZipArchive::CREATE);

                $s3 = Storage::disk('s3');
                $hasData = false;
                $type = 'merchant_disbursed';
                $filesPaths = [];
                foreach ($invoices as $invoice) {
                    if ($res === TRUE) {
//                        $file_name = str_replace(' ', '_', strtolower($type)) . '_' . $date->format('M') . '_' . $date->format('Y');
                        $file_path = '/merchant/' . $invoice->merchant->id . '/subvention_invoices/' . $date->format('M') . '_' . $date->format('Y');
                        $allfiles = Storage::disk('s3')->allFiles($file_path);

                        foreach ($allfiles as $key => $_file) {
                            if ($s3->exists($_file)) {
                                $s3_file = $s3->get($_file);
                                $path = storage_path() . '/' . $time . '_' . uniqid() . '.pdf';
                                $file = fopen($path, "w");
                                fwrite($file, $s3_file);
                                fclose($file);
                                if ($file) {
                                    //$zip->addFile($path, $file_name);
                                    $hasData = true;
                                }
                                //$s3_file = null;
                                //unlink($path);
                                $name = $invoice->merchant->merchant_name . '_' . $date->format('M') . '_' . $date->format('Y') . '_' . $key . '.pdf';
                                $filesPaths[$name] = $path;
                            }
                        }
                    } else {
                        return false;
                    }
                }

                foreach ($filesPaths as $file_name => $filesPath) {
                    $zip->addFile($filesPath, $file_name);
                }


                $zip->close();

                Mail::send('emails.reminders.basic_reminder',
                    ['body' => 'Please find the below ZIP file of Subvention Invoices ']
                    , function ($message) use ($zipFileName, $user) {
                        $message->to([$user->email])->bcc(['sarah@creditfair.in'])->subject('Exported Subvention Invoice');
                        $message->attachData(file_get_contents($zipFileName), 'Monthly_Invoice.zip');
                    });

                if (file_exists($zipFileName)) {
                    unlink($zipFileName);
                }
                foreach ($filesPaths as $file_name) {
                    unlink($file_name);
                }
                return true;
            } else if ($type == 'excel') {
                $response[] = ['DATE', 'INVOICE NO.', 'GSTN', 'MERCHANT NAME', 'CENTER NAME', 'TAXABLE AMOUNT', 'CGST', 'SGST', 'IGST', 'TOTAL'];
                foreach ($invoices as $invoice) {
                    $response[] = [
                        'date' => date('Y-m-d', strtotime($invoice->invoice_date)),
                        'invoice_id' => $invoice->invoice_id,
                        'gst' => $invoice->center->gst_number ?? $invoice->merchant->gst_number,
                        'name_on_invoice' => $invoice->merchant->merchant_name,
                        'center_on_invoice' => $invoice->center->name ?? '',
                        'taxable_value' => $invoice->net_amount,
                        'cgst' => $invoice->cgst,
                        'sgst' => $invoice->sgst,
                        'igst' => $invoice->igst,
                        'amount' => $invoice->amount,
                    ];
                }
                $request = new Request(['data' => $response, 'sheet' => 'Sheet1']);

                $name = 'Subv_invoices_' . $date->format('M') . '_' . $date->format('Y') . '.xls';
                Excel::store((new Exporter($request)), $name);

                Mail::send('emails.reminders.basic_reminder',
                    ['body' => 'Please find the below Excel file of Subvention Invoices ']
                    , function ($message) use ($name, $user) {
                        $message->to([$user->email])->bcc(['sarah@creditfair.in'])->subject('Exported Subvention Invoice');
                        $message->attachData(file_get_contents(storage_path() . '/app/' . $name), $name);
                    });

                if (!file_exists(storage_path() . '/app/' . $name)) {
                    unlink(storage_path() . '/app/' . $name);
                }

                return true;
            }
            return false;
        }catch (\Exception $e){
            return false;
        }
    }

    public function getAccountingReports(Request $request){
        $from_date = $request->get('from_date') ?? '';
        $month = $request->get('month') ?? '';
        $to_date = $request->get('to_date') ?? '';
        $year = $request->get('year') ?? '';
        $lender_id = $request->get('lender_id') ?? '';
        $selectedReportType = $request->get('selectedReportType') ?? '';
        $reportTypes = ['DPD Report', 'Book Debt Report', 'Accrued Revenue', 'Subvention Interest'];
        $_date = Carbon::now()->toDateTimeString();

        if($selectedReportType != ''){
            if((in_array($selectedReportType, ['Subvention Interest', 'Accrued Revenue']) && $month != "" && $year != "") || !in_array($selectedReportType, ['Subvention Interest', 'Accrued Revenue'])){
                $jobs = new CampaignsJobProvider();
                if ($selectedReportType == 'Subvention Interest') {
                    $jobs->from = 'getSubventionInterestReport';
                    $jobs->payload = json_encode(['year' => $year, 'month' => $month, 'lender_id' => $lender_id, 'user_email' => Auth::user()->email]);
                } else if ($selectedReportType == 'Accrued Revenue') {
                    $jobs->from = 'accrued_revenue';
                    $jobs->payload = json_encode(['year' => $year, 'month' => $month, 'lender_id' => $lender_id, 'user_email' => Auth::user()->email]);
//                    $jobs->payload = json_encode(['from_date' => $from_date, 'to_date' => $to_date, 'lender_id' => $lender_id, 'user_email' => Auth::user()->email]);
                } else {
                    $jobs->from = 'exportAccountingReport';
                    $jobs->payload = json_encode(array_merge($request->input(), ['user_email' => Auth::user()->email]));
                }
                $jobs->available_at = $_date;
                $jobs->application_id = 181;
                $jobs->created_at = $_date;
                $jobs->is_available = 0;
                $jobs->save();

                \Session::flash('success', 'The report will be sent to your email address');
            }else{
                \Session::flash('success', 'Please select the date range for generating the report');
            }
        }
        $request->merge(['user_email' =>Auth::user()->email]);
        $data['lenders'] = Lenders::get();
        $data['reportTypes'] = $reportTypes;
        $data['lender_id'] = $data['from_date'] = $data['month'] = $data['to_date'] = $data['year'] = $data['selectedReportType'] = '';
        return view('admin.accounting.accounting-reports', $data);
    }

    public function exportAccountingReport(Request $request){
        $from_date = $request->get('from_date') ?? '';
        $to_date = $request->get('to_date') ?? '';
        $lender_id = $request->get('lender_id') ?? '';
        $selectedReportType = $request->get('selectedReportType') ?? '';
        $time = time();
        $data_array = array();
        $user_email = $request->get('user_email');
        $_date = Carbon::now()->toDateTimeString();
        $exporter[] = ['Lender Name', 'Customer No', 'Name', 'purpose', 'State', 'Tenure', 'Loan Amount', 'Score', 'Profile', 'Income', 'Principal Overdue', 'Total DPD'];
        $exporter_debt[] = ['State', 'District', 'Branch', 'Application Id', 'Client Name', 'Account No.', 'Loan Purpose', 'Legal Form', 'Type Of Business', 'Monthly Income', 'Age', 'Gender', 'Credit Bureau Score', 'Sanction Amount', 'Loan Amount', 'ROI(%)', 'Disbursement Date', 'Disbursement Amount', 'Disbursement Phase', 'Payment Mode', 'Tenor (Months)', 'Maturity Date', 'Opening Principal O/s', 'Overdue Principal o/s', 'Principal Due during the month', 'Principal Paid during the month', 'Principal Overdue', 'DPD', 'Closing Principal O/s', 'Interest O/S beginning OF Month', 'Interest due during Month', 'Interest paid during Month', 'Interest overdue', 'Prepayment s', 'Outstanding Amount', 'Tagged Hypothicate to', 'Phone Number'];
        switch ($selectedReportType){
            case 'DPD Report':

                $data = EmiPayment::with('application_element','application.proFormaInvoice','application.emi', 'application.category','application.merchant','application.logs','application.lender','application.creditReport',
                    'moratoriums', 'application.last_payment_date', 'application.checkmandate')
                    ->whereHas('application', function ($q) {
                        $q->whereIn('state', ['disbursed','nach registered']);
                    });

                if($lender_id){
                    $data = $data->whereHas('application', function ($q) use ($lender_id) {
                        $q->where('lender_id', $lender_id);
                    });
                }
                if($from_date && $to_date) {
                    $data = $data->whereHas('application.logs', function ($q) use ($from_date, $to_date) {
                        $disbursement_date_from = Carbon::createFromFormat('Y-m-d', $from_date)->startOfDay()->toDateTimeString();
                        $disbursement_date_to = Carbon::createFromFormat('Y-m-d', $to_date)->endOfDay()->toDateTimeString();
                        $q->where('created_at', '>=', $disbursement_date_from)->where('created_at', '<=', $disbursement_date_to)->where('status','disbursed');
                    });
                }
                $data = $data->where('payment_status','!=','success')->orderBy('application_id', 'desc')->get();


                foreach($data as $val){
                    $data_array[$val->application->id]['application_data'] = $val;
                }
                foreach($data_array as $application){
                    //echo "
";print_r($application['application_data']->application->id);die;
                    $pos_data = (new TestingController())->getPrincipalsOutstandingAdityaV2($_date, true, $application['application_data']->application->id);
                    /*$EMIPayment = new PaymentEmi();
                    $payments = $EMIPayment->getPaymentDueHistory($application['application_data']->application->id);
                    $payments = isset($payments[$application['application_data']->application->id]) ? $payments[$application['application_data']->application->id] : [];
                    $delay_penalty = 0;
                    if ($payments && isset($payments['payment'])) {
                        foreach ($payments['payment'] as $payment) {
                            if (isset($payment['delay_penalty']) && $payment['delay_penalty']) {
                                $delay_penalty++;
                            }
                        }
                    }*/
                    $exporter[] = [
                        'lender_name' => $application['application_data']->application->lender->name ?? '',
                        'customer_no' => $application['application_data']->application->id ?? '',
                        'name' => $application['application_data']->application->name ?? '',
                        'purpose' => $application['application_data']->application->category->name ?? '',
                        'state' => $application['application_data']->application->current_address_state ?? '',
                        'tenure' => $application['application_data']->application->emi->remaining_emi ?? '',
                        'loan_amount' => $application['application_data']->application->approved_amount ?? '',
                        'score' => $application['application_data']->application->creditReport[0]->Score ?? '',
                        'profile' => $application['application_data']->application->employee_status ?? '',
                        'income' => $application['application_data']->application->monthly_income ?? '',
                        'principal_overdue' => $pos_data[0]['after foreclosure p os'] ?? 0,
                        'total_dpd' => $application['application_data']->delay_days ?? '',
                    ];

                }

                $request = new Request(['data'=>$exporter,'sheet'=>'Sheet1']);
                //return Excel::download((new Exporter($request)), 'DPD_report_'.$from_date.'.xls');
                (new Slack(new Request(['title' => 'Accounting DPD Report Sending File', 'fields' => ['DPD Data Count' => count($exporter)], 'group' => Slack::$STAGE_TESTING])))->send();
                    try {
                        $filePath = $time.'_DPD_Data_'.$from_date.'.xls';
                        Excel::store((new Exporter($request)), 'exported/' . $filePath);
                        // Storage::disk('s3')->put('/collection/exported/'.$filePath, file_get_contents(storage_path('app/exported/' . $filePath)));
                        $dpd_file = file_get_contents(storage_path('app/exported/' . $filePath));
                        $name = time().'_DPD_Data_'.$from_date.'.xls';
                        Mail::send('emails.reminders.basic_reminder',
                        ['body' => "DPD Report attached below, Please find"]
                        , function ($message) use ($user_email, $dpd_file, $name) {
                            $message->to([$user_email])->subject('DPD_Report');
                            $message->cc('sarah@creditfair.in');
                            $message->attachData($dpd_file, $name);
                        });
                        (new Slack(new Request(['title' => 'DPD Report Sent', 'fields' => ['DPD Data Count' => count($exporter)], 'group' => Slack::$STAGE_TESTING])))->send();
                    }catch (\Exception $exception){
                        (new Slack(new Request(['title' => 'DPD Report Sent Error', 'fields' => ['e' => $exception->getMessage(),'l' => $exception->getLine(),'t' => $exception->getTraceAsString()], 'group' => Slack::$STAGE_TESTING])))->send();
                    }
                    return true;

                break;
            case 'Book Debt Report':

                $data = EmiPayment::with('application_element','application.proFormaInvoice','application.emi', 'application.category','application.merchant','application.logs','application.lender','application.creditReport',
                    'moratoriums', 'application.last_payment_date', 'application.checkmandate', 'application.disbursed', 'application.disbursement_log', 'application.disbursementV1','application.hypothecation_logs')
                    ->whereHas('application', function ($q) {
                        $q->whereIn('state', ['disbursed','nach registered']);
                    });

                if($lender_id){
                    $data = $data->whereHas('application', function ($q) use ($lender_id) {
                        $q->where('lender_id', $lender_id)->whereNull('deleted_at');
                    });
                }
                /*if($from_date && $to_date) {
                    $data = $data->whereHas('application.logs', function ($q) use ($from_date, $to_date) {
                        $disbursement_date_from = Carbon::createFromFormat('Y-m-d', $from_date)->startOfDay()->toDateTimeString();
                        $disbursement_date_to = Carbon::createFromFormat('Y-m-d', $to_date)->endOfDay()->toDateTimeString();
                        $q->where('created_at', '>=', $disbursement_date_from)->where('created_at', '<=', $disbursement_date_to)->where('status','disbursed');
                    });
                }*/
                if($to_date) {
                    $data = $data->whereHas('application.logs', function ($q) use ($to_date) {
                        $disbursement_date_to = Carbon::createFromFormat('Y-m-d', $to_date)->endOfDay()->toDateTimeString();
                        $q->where('created_at', '<=', $disbursement_date_to)->where('status','disbursed');
                    });
                }
                $data = $data->where('payment_status','!=','success')->orderBy('application_id', 'desc')->get();


                foreach($data as $val){
                    $data_array[$val->application->id]['application_data'] = $val;
                }
                $FDateE = '';//'2021-03-31 23:59:59';
                $FDateS = '';
                $FDate1E = '';
                $FDate1S = '';
                if(!empty($to_date) && !empty($from_date)){
                    $FDateE = Carbon::createFromFormat('Y-m-d', $to_date)->endOfDay();//'2021-03-31 23:59:59';
                    $FDateS = Carbon::createFromFormat('Y-m-d', $from_date)->startOfDay();
                    $FDate1E = Carbon::createFromFormat('Y-m-d', $to_date)->subMonth(1)->endOfDay();
                    $FDate1S = Carbon::createFromFormat('Y-m-d', $from_date)->subMonth(1)->startOfDay();
                }//'2021-03-01 00:00:00';
                foreach($data_array as $application){
                    //echo "
";print_r($application['application_data']->application->id);die;
                    //$pos_data = (new TestingController())->getPrincipalsOutstandingAdityaV2($_date, true, $application['application_data']->application->id);
                    /*$EMIPayment = new PaymentEmi();
                    $payments = $EMIPayment->getPaymentDueHistory($application['application_data']->application->id);
                    $payments = isset($payments[$application['application_data']->application->id]) ? $payments[$application['application_data']->application->id] : [];
                    $delay_penalty = 0;
                    if ($payments && isset($payments['payment'])) {
                        foreach ($payments['payment'] as $payment) {
                            if (isset($payment['delay_penalty']) && $payment['delay_penalty']) {
                                $delay_penalty++;
                            }
                        }
                    }*/
                    $statusLog = StatusLogs::where('application_id', $application['application_data']->application->id)->whereIn('status', ['closed', 'cancelled'])->orderBy('created_at','desc')->first();
                    $isClosed = 0;
                    if($statusLog && $statusLog->created_at <= $FDateE){
                        $isClosed = 1;
                    }
                    $v = $this->getLoanScheduleDataV1($application['application_data']->application->id);
                    $principalToBeReceivedTotal = 0;
                    $principalToBeReceivedMonth = 0;
                    $monthWiseData = [];
                    $principalReceivedTotal = 0;
                    $no_of_Installments_remaining = 0;
                    $OverduePrincipalo_sE2 = 0;
                    $OverduePrincipalo_sD2 = 0;
                    $interest_OS_beginning_OF_MonthK2 = 0;
                    $PrincipalDueduringthemonthF2 = 0;
                    $PrincipalDueduringthemonthJ2 = 0;
                    $interest_due_during_MonthL2 = 0;
                    $interest_due_during_MonthM2 = 0;
                    $no_of_Installments_remainingC2 = 0;
                    $principal_Paid_during_the_monthG2 = 0;
                    $interestToBeReceivedTotal = 0;
                    $interestToBeReceivedMonth = 0;
                    $interest_overdueN2 = 0;
                    $principal_Overdue_H2 = 0;
                    $dueAfterData = 0;
                    $interest_paid_during_MonthM2 = 0;

                    foreach($v['schedule'] as $vp => $c) {
                        $receivedOnMonth = 0;
                        if(isset($c['transaction'])){
                            foreach ($c['transaction'] as $transaction) {
                                if ($transaction->payment_date <= $FDateE && !$transaction->refunded_at) {
                                    $receivedOnMonth += $transaction->amount;
                                }
                            }
                        }
                        $principalToBeReceivedTotal += $c['principal'];
                        $principalToBeReceivedMonth = $c['principal'];

                        $interestToBeReceivedTotal += $c['interest'];
                        $interestToBeReceivedMonth = $c['interest'];
                        //}

                        if($principalToBeReceivedMonth <= $receivedOnMonth){
                            $monthWiseData[$c['emi_date']]['principalReceived'] = $principalToBeReceivedMonth;
                        }else{
                            $monthWiseData[$c['emi_date']]['principalReceived'] = $receivedOnMonth;//$receivedOnMonth ? $principalToBeReceivedMonth - $receivedOnMonth : 0;
                        }

                        if($principalToBeReceivedMonth <= $receivedOnMonth) {
                            if (round($interestToBeReceivedMonth) <= round($receivedOnMonth - $principalToBeReceivedMonth)) {
                                $monthWiseData[$c['emi_date']]['interestReceived'] = $interestToBeReceivedMonth;
                            } else {
                                $monthWiseData[$c['emi_date']]['interestReceived'] = ($receivedOnMonth - $principalToBeReceivedMonth);//$receivedOnMonth ? $principalToBeReceivedMonth - $receivedOnMonth : 0;
                            }
                        }
                        $principalReceivedTotal += $monthWiseData[$c['emi_date']]['principalReceived'];

                        if ($c['emi_date'] >= $FDateS && $c['emi_date'] <= $FDateE){
                            $PrincipalDueduringthemonthF2 = $principalToBeReceivedMonth;
                            $principal_Paid_during_the_monthG2 = $monthWiseData[$c['emi_date']]['principalReceived'];
                            $interest_due_during_MonthL2 = $interestToBeReceivedMonth;
                            $interest_paid_during_MonthM2 = $monthWiseData[$c['emi_date']]['interestReceived'] ?? 0;
                            $PrincipalDueduringthemonthJ2 = $c['principal_outstanding'];//$v['schedule'][$vp + 1]['principal'] ?? 0;
                            $dueAfterData = isset($v['schedule'][$vp + 1]['principal_outstanding']) ? $v['schedule'][$vp + 1]['principal_outstanding'] : 0;
                            $interest_overdueN2 = round($interest_due_during_MonthL2) - round($interest_due_during_MonthM2);
                            $principal_Overdue_H2 = $principalToBeReceivedTotal - $principalReceivedTotal;
                        }
                        if($c['emi_date'] >= $FDate1S && $c['emi_date'] <= $FDate1E && !$OverduePrincipalo_sE2){
                            $OverduePrincipalo_sE2 = $principalToBeReceivedTotal - $principalReceivedTotal;
                            $OverduePrincipalo_sD2 = $c['principal_outstanding'];//$principalReceivedTotal;
                            $interest_OS_beginning_OF_MonthK2 = $interestToBeReceivedMonth - ($monthWiseData[$c['emi_date']]['interestReceived'] ?? 0);
                        }
                    }
                    $t = new TestingController();
                    $dpdDaysCount = $isClosed ? 0 : $t->getDPDReport($application['application_data']->application->id, $FDateE);
                    $foreclosureAmtReceived = 0;
                    if(isset($v['foreclosureData'])) {
                        foreach ($v['foreclosureData'] as $foreclosureAmt) {
                            if ($foreclosureAmt['payment_date'] <= $FDateE) {
                                $foreclosureAmtReceived += $foreclosureAmt['amount'];
                            }
                        }
                    }

                    if($v['schedule'][0]['emi_date'] >= $FDate1S && !$OverduePrincipalo_sD2){
                        $OverduePrincipalo_sD2 = $principalToBeReceivedTotal;//$principalReceivedTotal;
                    }

                    if($v['schedule'][0]['emi_date'] > $FDateE){
                        $PrincipalDueduringthemonthJ2 = $principalToBeReceivedTotal;//$v['schedule'][0]['principal_outstanding'];
                        //$PrincipalDueduringthemonthJ2 = $v['schedule'][0]['principal_outstanding'];
                    }

                    if($foreclosureAmtReceived && $dueAfterData){
                        $PrincipalDueduringthemonthJ2 = $foreclosureAmtReceived >= $dueAfterData ? 0 : $dueAfterData - $foreclosureAmtReceived;
                    }
                    $net_tenure = $application['application_data']->application->emi->tenure;
                    if ($application['application_data']->application->emi->advance_emi == 1){
                        $net_tenure = $application['application_data']->application->emi->tenure - $application['application_data']->application->emi->advance_emi;
                    }
                    if(count($application['application_data']->application->disbursement_log) > 0){
                        foreach($application['application_data']->application->disbursement_log as $val){
                            $exporter_debt[] = [
                                'state' => $application['application_data']->application->current_address_state ?? '',
                                'district' => $application['application_data']->application->current_address_city ?? '',
                                'branch' => 'Mumbai',
                                'application_id' => $application['application_data']->application->id ?? '',
                                'client_name' => $application['application_data']->application->name ?? '',
                                'loan_agreement_number' => $application['application_data']->application->proFormaInvoice->loan_application_no ?? '',
                                'loan_purpose' => $application['application_data']->application->category->name ?? '',
                                'legal_form' => '',
                                'type_of_business' => $application['application_data']->application->employee_status ?? '',
                                'monthly_income' => $application['application_data']->application->monthly_income ?? '',
                                'age' => (date('Y', strtotime($application['application_data']->application->created_at)) - date('Y', strtotime($application['application_data']->application->dob))) ?? 0,
                                'gender' => $application['application_data']->application->gender ?? '',
                                'credit_bureau_score' => isset($application['application_data']->application->creditReport[0]->Score) ? $application['application_data']->application->creditReport[0]->Score : '',
                                'sanction_amount' => $application['application_data']->application->approved_amount ?? '',
                                'loan_amount' => $application['application_data']->application->loan_amount ?? '',
                                'ROI' => ($application['application_data']->application->emi->interest_rate == "0.00") ? '0.00' : $application['application_data']->application->emi->interest_rate,
                                'disbursement_date' => $val->created_at ?? '',
                                'disbursed_amount' => $val->disbursement_amount ?? '',
                                'disbursement_phase' => $val->phase ?? '',
                                'payment_mode' => ($application['application_data']->application_element->is_payout == 1) ? 'Cashfree' : 'NEFT/RTGS/Online Payment',
                                'tenure' => $net_tenure ?? '',
                                'maturity_date' => $application['application_data']->application_element->last_emi_date ?? '',
                                'opening_principal_outstanding' => $OverduePrincipalo_sD2 ?? '',
                                'overdue_principal_outstanding' => $OverduePrincipalo_sE2 ?? '',
                                'principal_due_during_the_month' => $PrincipalDueduringthemonthF2 ?? '',
                                'principal_paid_during_the_month' => $principal_Paid_during_the_monthG2 ?? '',
                                'principal_overdue' => $principal_Overdue_H2 ?? '',
                                'dpd_days' => $dpdDaysCount ?? '',
                                'closing_principal_outstanding' => $PrincipalDueduringthemonthJ2 ?? '',
                                'interest_outstanding_begenning_of_month' => $interest_OS_beginning_OF_MonthK2 ?? '',
                                'interest_due_during_month' => $interest_due_during_MonthL2 ?? '',
                                'interest_paid_during_month' => $interest_paid_during_MonthM2 ?? '',
                                'interest_overdue' => $interest_overdueN2 ?? '',
                                'prepayments' => $foreclosureAmtReceived ?? '',
                                'outstanding_amount' => ($isClosed ? 0 : ($foreclosureAmtReceived > ($principalToBeReceivedTotal - $principalReceivedTotal) ? 0 : ($principalToBeReceivedTotal - ($principalReceivedTotal + $foreclosureAmtReceived)))) ?? '',
                                'tagged_hypothicate_to' => $application['application_data']->application->hypothecation_logs[0]->lender ?? '',
                                'phone_number' => $application['application_data']->application->mobile_number ?? ''
                            ];
                        }
                    }else{
                        if($application['application_data']->application->lender_id == Services::$lender_id){
                            $lender = new LenderServices(new Request());
                            $d_amt =  $lender->getPayloadValueByID($application['application_data']->application->id, 'actual_disbursement_amt');
                        }else{
                            $d_amt = $application['application_data']->application->emi->upfront_from_credit_fair - ($application['application_data']->application->emi->subvention_igst + $application['application_data']->application->emi->subvention_cgst + $application['application_data']->application->emi->subvention_sgst);
                            if (isset($application['application_data']->application->proFormaInvoice->processing_fee_by_merchant) && $application['application_data']->application->proFormaInvoice->processing_fee_by_merchant == 1) {
                                $d_amt = $d_amt - $application['application_data']->application->emi->processing_fee;
                            }

                            $insurance_premium_amount = isset($application['application_data']->application->proFormaInvoice->insurance_premium_amount) && $application['application_data']->application->proFormaInvoice->insurance_premium_applicable == 1 && $application['application_data']->application->proFormaInvoice->insurance_premium_by_merchant == 1 ? $application['application_data']->application->proFormaInvoice->insurance_premium_amount : 0;
                            if($insurance_premium_amount){
                                $d_amt = $d_amt - $insurance_premium_amount;
                            }

                            $upfront_from_customer = isset($application['application_data']->application->proFormaInvoice->merchant_collected_advance_amount) && $application['application_data']->application->proFormaInvoice->merchant_collected_advance_amount == 1 ? $application['application_data']->application->emi->upfront_from_customer : 0;
                            if($upfront_from_customer){
                                $d_amt = $d_amt + $upfront_from_customer;
                            }
                            if(isset($application['application_data']->application->disbursementV1->disbursed)){
                                $d_amt = $application['application_data']->application->disbursementV1->disbursed;
                            }
                            //$d_amt = (isset($application['application_data']->application->disbursementV1) && isset($application['application_data']->application->disbursementV1->disbursed) && $application['application_data']->application->disbursementV1->disbursed == 0) ? $application['application_data']->application->disbursementV1->upfront_from_credit_fair : (isset($application['application_data']->application->disbursementV1) && !empty($application['application_data']->application->disbursementV1) && isset($application['application_data']->application->disbursementV1->disbursed) ? $application['application_data']->application->disbursementV1->disbursed : '' );
                        }
                        $exporter_debt[] = [
                            'state' => isset($application['application_data']->application->current_address_state) ? $application['application_data']->application->current_address_state: '',
                            'district' => isset($application['application_data']->application->current_address_city) ? $application['application_data']->application->current_address_city : '',
                            'branch' => 'Mumbai',
                            'application_id' => $application['application_data']->application->id ?? '',
                            'client_name' => $application['application_data']->application->name ?? '',
                            'loan_agreement_number' => $application['application_data']->application->proFormaInvoice->loan_application_no ?? '',
                            'loan_purpose' => $application['application_data']->application->category->name ?? '',
                            'legal_form' => '',
                            'type_of_business' => $application['application_data']->application->employee_status ?? '',
                            'monthly_income' => $application['application_data']->application->monthly_income ?? '',
                            'age' => (date('Y', strtotime($application['application_data']->application->created_at)) - date('Y', strtotime($application['application_data']->application->dob))) ?? 0,
                            'gender' => $application['application_data']->application->gender ?? '',
                            'credit_bureau_score' => $application['application_data']->application->creditReport[0]->Score ?? '',
                            'sanction_amount' => $application['application_data']->application->approved_amount ?? '',
                            'loan_amount' => $application['application_data']->application->loan_amount ?? '',
                            'ROI' => ($application['application_data']->application->emi->interest_rate == "0.00") ? '0.00' : $application['application_data']->application->emi->interest_rate,
                            'disbursement_date' => $application['application_data']->application->disbursed->created_at ?? '',
                            'disbursed_amount' => $d_amt,
                            'disbursement_phase' => 'NA',
                            'payment_mode' => ($application['application_data']->application_element->is_payout == 1) ? 'Cashfree' : 'NEFT/RTGS/Online Payment',
                            'tenure' => $net_tenure ?? '',
                            'maturity_date' => $application['application_data']->application_element->last_emi_date ?? '',
                            'opening_principal_outstanding' => $OverduePrincipalo_sD2 ?? '',
                            'overdue_principal_outstanding' => $OverduePrincipalo_sE2 ?? '',
                            'principal_due_during_the_month' => $PrincipalDueduringthemonthF2 ?? '',
                            'principal_paid_during_the_month' => $principal_Paid_during_the_monthG2 ?? '',
                            'principal_overdue' => $principal_Overdue_H2 ?? '',
                            'dpd_days' => $dpdDaysCount ?? '',
                            'closing_principal_outstanding' => $PrincipalDueduringthemonthJ2 ?? '',
                            'interest_outstanding_begenning_of_month' => $interest_OS_beginning_OF_MonthK2 ?? '',
                            'interest_due_during_month' => $interest_due_during_MonthL2 ?? '',
                            'interest_paid_during_month' => $interest_paid_during_MonthM2 ?? '',
                            'interest_overdue' => $interest_overdueN2 ?? '',
                            'prepayments' => $foreclosureAmtReceived ?? '',
                            'outstanding_amount' => ($isClosed ? 0 : ($foreclosureAmtReceived > ($principalToBeReceivedTotal - $principalReceivedTotal) ? 0 : ($principalToBeReceivedTotal - ($principalReceivedTotal + $foreclosureAmtReceived)))) ?? '',
                            'tagged_hypothicate_to' => $application['application_data']->application->hypothecation_logs[0]->lender ?? '',
                            'phone_number' => $application['application_data']->application->mobile_number ?? ''
                        ];
                    }



                }

                $request = new Request(['data'=>$exporter_debt,'sheet'=>'Sheet1']);
                //return Excel::download((new Exporter($request)), 'DPD_report_'.$from_date.'.xls');
                (new Slack(new Request(['title' => 'Accounting Book Debt Report Sending File', 'fields' => ['Book Debt Data Count' => count($exporter_debt)], 'group' => Slack::$STAGE_TESTING])))->send();
                    try {
                        $filePath = $time.'_Book_Debt_Data_'.$from_date.'.xls';
                        Excel::store((new Exporter($request)), 'exported/' . $filePath);
                        // Storage::disk('s3')->put('/collection/exported/'.$filePath, file_get_contents(storage_path('app/exported/' . $filePath)));
                        $dpd_file = file_get_contents(storage_path('app/exported/' . $filePath));
                        $name = time().'_Book_Debt_Data_'.$from_date.'.xls';
                        Mail::send('emails.reminders.basic_reminder',
                        ['body' => "Book Debt Report attached below, Please find"]
                        , function ($message) use ($user_email, $dpd_file, $name) {
                            //$message->to([$user_email])->subject('Book_Debt_Report');
                            $message->to([$user_email])->subject('Book_Debt_Report');
                            $message->cc('sarah@creditfair.in');
                            $message->attachData($dpd_file, $name);
                        });
                        unlink(storage_path().'/app/exported/' . $filePath);
                        (new Slack(new Request(['title' => 'Book Debt Report Sent', 'fields' => ['DPD Data Count' => count($exporter)], 'group' => Slack::$STAGE_TESTING])))->send();
                    }catch (\Exception $exception){
                        (new Slack(new Request(['title' => 'Book Debt Report Sent Error', 'fields' => ['e' => $exception->getMessage(),'l' => $exception->getLine(),'t' => $exception->getTraceAsString()], 'group' => Slack::$STAGE_TESTING])))->send();
                    }
                    return true;

                break;
        }

        return true;
    }

    public function writeOffAmount(Request $request){
        if($request->has('id') && $request->has('write_off_amount') && $request->get('write_off_amount') > 0 && $request->has('user_id')){

            $paymentTranscation = PaymentTransaction::where('application_id', $request->get('id'))->whereNull('deleted_at')->where('payment_for', 'Write-off')->first(['id']);
            if(!isset($paymentTranscation->id)) {

                $consumerApplicationElement = ConsumerApplicationElement::where('application_id', $request->get('id'))->first(['total_amount_due']);
                if(isset($consumerApplicationElement->total_amount_due) && $request->get('write_off_amount') != $consumerApplicationElement->total_amount_due){
                    return response()->json(['status' => 'FAIL', 'message' => 'Total loan outstanding is '.$consumerApplicationElement->total_amount_due.'. Mismatch write-off amount.']);
                }else if(isset($consumerApplicationElement->total_amount_due) && $consumerApplicationElement->total_amount_due == 0){
                    return response()->json(['status' => 'FAIL', 'message' => 'You cant write-off the loan outstanding '.$consumerApplicationElement->total_amount_due.'. No Amount to write-off.']);
                }

                $date = Carbon::now();

                $data = [
                    'amount_received' => $request->get('write_off_amount'),
                    'mode' => 'Written-off',
                    'paid_by' => '',
                    'payment_reconciliation_date' => $date->toDateString(),
                    'umrn' => '',
                    'collected_by' => $request->get('user_id'),
                    'collected_by_type' => '1',
                    'adjust_with_disbursement' => 0,
                    'wallet_name' => '',
                    'reason' => $request->has('write_off_reason') && $request->get('write_off_reason') ? $request->get('write_off_reason') : '',
                    'trasanction_id' => 0,
                    'trasanction_acknowledgement' => '',
                    'application_id' => $request->get('id'),
                    'payment_type' => 'Write-off',
                    'payment_date' => $date->toDateString(),
                    'cf_transaction_id' => '',
                    'bank_id' => 5,
                    'status' => 'success',
                ];

                $this->makePaymentTransaction($data);

                return response()->json(['status' => 'OK', 'message' => 'Successfully Written-off the amount '.$request->get('write_off_amount')]);

            }
        }
        return response()->json(['status' => 'FAIL', 'message' => 'Failed to write-off the amount']);
    }

    private function makePaymentTransaction($data){
        $payment_transaction = new PaymentTransaction();
        $payment_transaction->emi_payment_id = 0;
        $payment_transaction->amount = $data['amount_received'];
        $payment_transaction->mode = $data['mode'];
        $payment_transaction->paid_by = $data['paid_by'];
        $payment_transaction->payment_reconciliation_date = $data['payment_reconciliation_date'];
        $payment_transaction->umrn = isset($data['umrn']) ? $data['umrn'] : '';
        $payment_transaction->collected_by = $data['collected_by'];
        $payment_transaction->collected_by_type = $data['collected_by_type'];
        $payment_transaction->adjust_with_disbursement = $data['adjust_with_disbursement'];
        $payment_transaction->wallet_name = $data['wallet_name'];
        $payment_transaction->reason = isset($data['reason']) ? $data['reason'] : '';
        $payment_transaction->trasanction_id = $data['trasanction_id'];
        $payment_transaction->trasanction_acknowledgement = $data['trasanction_acknowledgement'];
        $payment_transaction->application_id = $data['application_id'];
        $payment_transaction->payment_for = $data['payment_type'];
        $payment_transaction->payment_date = \Illuminate\Support\Carbon::parse($data['payment_date'])->format('Y-m-d');;
        $payment_transaction->cf_transaction_id = $data['cf_transaction_id'];
        $payment_transaction->bank_id = $data['bank_id'];
        $payment_transaction->status = isset($data['status']) ? $data['status'] : 'success';
        $payment_transaction->save();
    }

    public function generateInsuranceInvoice(){

        $inv_start_date = Carbon::now()->subMonth(1)->startOfMonth();
        $inv_end_date = Carbon::now()->subMonth(1)->endOfMonth();
        $applications = [];

        $status_logs = StatusLogs::where('status', 'disbursed')->where('created_at', '>=', $inv_start_date)
            ->where('created_at', '<=', $inv_end_date)->where('is_reversed',0)->get(['application_id']);


        foreach($status_logs as $log){
            if(!in_array($log->application_id, $applications)) {
                $applications[] = $log->application_id;
            }
        }

        $proforma = ProFormaInvoice::where('insurance_premium_amount', '>', 0)->whereIn('application_id', $applications)->get(['application_id','insurance_premium_amount']);

        $applicationController= new ConsumerApplicationController();

        foreach($proforma as $application){
            $data = ConsumerApplication::where('id', $application->id)->first(
                [
                    'permanent_address_state', 'current_address_state'
                ]
            );

            $gstDetails = (new ConsumerDocument(new Request()))->getGSTDetails(
                [
                    'amount' => $application->insurance_premium_amount,
                    'state' => (isset($data->permanent_address_state) && !empty($data->permanent_address_state)) ? $data->permanent_address_state : (isset($data->current_address_state) && !empty($data->current_address_state) ? $data->current_address_state : '')
                ]
            );
            $amt = (isset($gstDetails[0])) ? $gstDetails[0]['amount'] : 0;
            if ($amt) {
                if ($gstDetails) {
                    $response = array();
                    foreach ($gstDetails as $gst) {
                        if (isset($gst['taxes']) && $gst['taxes'] == "CGST") {
                            $response['cgst'] = $gst['gst_amount'];
                        } elseif (isset($gst['taxes']) && $gst['taxes'] == "SGST") {
                            $response['sgst'] = $gst['gst_amount'];
                        } else {
                            $response['igst'] = $gst['gst_amount'];
                        }
                    }
                }
                try {
                    $type = 'insurance_invoice';


                    $invoice = Invoices::where('application_id', $application->id)->where('type', $type)->whereNull('deleted_at')->first(['id']);
                    $_data = [
                        'application_id' => $application->id,
                        'invoice_id' => $applicationController->getInvoiceID($data, $type),
                        'amount' => (isset($gstDetails[0])) ? $gstDetails[0]['amount'] : 0,
                        'net_amount' => (isset($gstDetails[0])) ? $gstDetails[0]['net_amount'] : 0,
                        'igst' => (isset($response['igst'])) ? $response['igst'] : 0,
                        'cgst' => (isset($response['cgst'])) ? $response['cgst'] : 0,
                        'sgst' => (isset($response['sgst'])) ? $response['sgst'] : 0,
                        'lender_id' => $data->lender_id,
                        'type' => $type,
                        'status' => 1,
                        'created_at' => Carbon::now()->toDateTimeString()
                    ];

                    if (!isset($invoice->id)) {
                        Invoices::insert(
                            $_data
                        );
                    }
                } catch (\Exception $e) {

                }
            }
        }
    }

    public function getMerchantInsuranceConfig($id){
        $application = ConsumerApplication::where('id', $id)->first(['merchant_id', 'lender_id']);
        $merchant = Merchant::where('id', $application->merchant_id)->first(['insurance_premium', 'insurance_collection_method', 'is_collected']);
        if(isset($application->lender_id) && $application->lender_id) {
            $lender = Lenders::where('id', $application->lender_id)->first(['activate_insurance']);
            if (isset($lender->activate_insurance) && $lender->activate_insurance) {
                $application_element = ConsumerApplicationElement::where('application_id', $id)->first(['type_of_customer']);
                $insurance_coverage = in_array($application_element->type_of_customer, [1, 2]) ? 5 : 1;
                return [
                    'insurance_premium_applicable' => $merchant->insurance_premium ?? 0,
                    'insurance_premium_by_merchant' => $merchant->insurance_collection_method ?? 0,
                    'insurance_coverage' => $insurance_coverage,
                    'processing_fee_by_merchant' => $merchant->is_collected
                ];
            }
        }
        return [
            'insurance_premium_applicable' => 0,
            'insurance_premium_by_merchant' => 0,
            'insurance_coverage' => 0,
            'processing_fee_by_merchant' => $merchant->is_collected
        ];
    }

    public function downloadPrePaymentRevisedRPS(Request $request){
        $response = $this->getPrePaymentRevisedRPS($request);
        if($response->getData()->status == 'OK'){
            $data = $response->getData()->data;
            $pdf = App::make('snappy.pdf.wrapper');
            $proFormaInvoice = ProFormaInvoice::where('application_id', $request->get('application_id'))->first(['loan_application_no']);
            $revisedRPS = $pdf->loadHTML(view('front.sample-revised-rps', ['table' => $data, 'summary' => $request->all(), 'loan_agreement_number' => $proFormaInvoice->loan_application_no ?? '-']));
            return $revisedRPS->download('Sample Revised RPS.pdf');
        }else{
            \Session::flash('error', $response->getData()->message);
        }
    }

    public function getPrePaymentRevisedRPS(Request $request){
        if($request->get('amount') > 0){
            try {
                $repaymentController = new RepaymentController();
                $payload = $request->all();

                if (!$request->has('is_preview_flag')){
                    $payload = [
                        'application_id' => $request->get('application_id'),
                        'amount' => $request->get('amount'),
                        'payment_date' => $request->has('payment_date') ? $request->get('payment_date') : date('Y-m-d'),
                        'type' => $request->get('type') == 1 ? 'emi': 'tenure',
                        'is_preview_flag' => true,
                        'user_id' => Auth::check() ? Auth::user()->id : 107
                    ];
                }
                $response = $repaymentController->getRevisedRPS($payload);

                if(isset($response['status']) && $response['status'] == 'FAILED'){
                    return response()->json(['status' => 'FAIL', 'message' => $response['message']]);
                }


                $body = $this->generateTabularFormat($response);

                return response()->json(['status' => 'OK', 'message' => !$request->get('is_preview_flag') ? 'Pre Payment amount reconclied successfully' : 'Pre payment revised RPS fetched successfully', 'data' => $body]);
            }catch (\Exception $e){
                return response()->json(['status' => 'FAIL', 'message' => 'Something went wrong while preparing the RPS.']);
            }
        }

        return response()->json(['status' => 'FAIL', 'message' => 'Amount should be greater than zero']);
    }

    public function generateTabularFormat($response){
        $table = '';
        if(isset($response['data'])) {
            foreach ($response['data'] as $item) {
                $table .= '';
            }
        }else{
            $table .= '';
        }

        $table .= '
Installment Number
EMI Date
Pre-EMI Amount
Interest Due
Principal Due
EMI Amount
Principal Outstanding
Foreclosure Amount
' . $item['installment_number'] . '
' . $item['emi_date'] . '
₹' . str_replace('.00', '', money_format('%!i', $item['pre_emi_amount'])) . '
₹' . str_replace('.00', '', money_format('%!i', $item['interest_due'])) . '
₹' . str_replace('.00', '', money_format('%!i', $item['principal_due'])) . '
₹' . str_replace('.00', '', money_format('%!i', $item['emi_amount'])) . '
₹' . str_replace('.00', '', money_format('%!i', $item['principal_outstanding'])) . '
₹' . str_replace('.00', '', money_format('%!i', $item['foreclosure_amount'])) . '
No Data
';

        return $table;
    }
}