";print_r($agencyApplication);die;
            AgencyApplication::whereNotNull('id')->delete();
            if (isset($agencyApplications) && $agencyApplications) {
                //$aapDetails = new AgencyApplication;
                //$aapDetails->fill($agencyApplication);
                //$aapDetails->save();
                foreach(array_chunk($agencyApplications,2000) as $agencyApplication){
                    AgencyApplication::insert($agencyApplication);
                }
            }

            $sms_id_arr = array_unique($sms_id_arr);
            //print_r($sms_id_arr);die;
            // if(Session::has('Agency_Allotment_View|4-13-0')){
            $user = User::where('id',$fileUploaderId)->first(['name','email']);
            if($mailtocf){
                (new Slack(new Request(['title'=>'Agency Uploaded Excel File','fields'=> ['Message' => 'Dear Collection team, New excel file is uploaded by agency '.$user->name.'. You can find uploaded excel file in respective mails'],'group' => 'payment_collection'])))->send();

                $emails = _env('APP_CONNECTION') ? ['imran@creditfair', 'amol@creditfair.in', 'ketan@creditfair.in'] : [$user->email];
                Mail::send('emails.reminders.basic_reminder', [
                    'body' => 'Please check the attached excel that is uploaded by '.$user->name.'.'], function ($message) use ($localFileName,$emails) {
                    $message->to($emails)->cc(['sarah@creditfair.in'])->subject('Excel File From Agency');
                    // $message->attach($Excelfile->getRealPath(), array(
                    //     'as' => 'Agency Report_'.Carbon::now()->toDateString(),
                    //     'mime' => $Excelfile->getMimeType())
                    // );
                    $message->attachData(file_get_contents(storage_path() . '/app/' . $localFileName), $localFileName);
                });
            }
            if(!empty($sms_id_arr)){
                event(new EventAgencyCommunication($sms_id_arr));
            }
            // remove the file from local stoarge
            Storage::disk('local')->delete($localFileName);

            $data['status'] = 'SUCCESS';
            $data['message'] = 'Excel Data Imported Successfully';
            return $data;
        }
        Storage::disk('local')->delete($localFileName);
        $data['status'] = 'FAIL';
        $data['message'] = 'Something went wrong while uploading file';
        // return $data;
        throw new \Exception($data['message']);
    }

    public function sendCommToCustomerOnAgencyChange($ids = []){
        if(!empty($ids)){
            $data = ConsumerApplication::with(['agency'=>function($query){ $query->with('agency_name');},'proFormaInvoice'=>function($query){ $query->select('application_id','loan_application_no');}])->whereIn('id', $ids)->get(['id','name','email','mobile_number','current_address_line_1','current_address_state','current_address_city','current_address_pincode']);
            $entity = ['templateid' => '1307166730667574368', 'entityid' => Templates::$entityid];
            //echo "
";print_r($data);die;
            foreach($data as $key => $value){
            try{
                $msg_email = 'To, 
 Application ID:- '.$value->id.'
 Loan ID:- '.$value->proFormaInvoice->loan_application_no.'
 Customer Name :- '.$value->name.' 
 Address :- '.$value->current_address_line_1.' '.$value->current_address_city.' '.$value->current_address_state.' - '.$value->current_address_pincode.' 
 
 Subject - Enrolment of collection Agency. 
 
Dear '.$value->name. ', 
 This is to informed you that we have authorised '.$value->agency->agency_name->name.' as our Collection Partner to collect the dues on behalf of Credit Fair.';
                //$msg = 'Dear '.$value->name. ',This is to informed you that we have authorised '.$value->agency->agency_name->name.' as our Collection Partner to collect the dues on behalf of Credit Fair.';
                $msg = 'Dear '.$value->name.', This is to informed you that we have authorised '.$value->agency->agency_name->name.' as our Collection Partner to collect the dues on behalf of Credit Fair. Regards, Team Credit Fair.';

                if(isset($value->email)){
                    $email = $value->email;
                    Mail::send('emails.reminders.agency_communication', [
                        'body' => $msg_email], function ($message) use ($email) {
                        $message->to($email)->subject('Enrolment of Collection Agency');
                    });
                }else{
                    $aId = array();
                    array_push($aId,$value->id);
                }
                if(isset($value->mobile_number)){
                    $mobile = '91' .$value->mobile_number;
                    send_sms($mobile, ['message' => $msg, 'id' => $value->id, 'id_type' => 'application_id', 'message_type'=>"payment", 'templateDetails' => $entity]);
                }else{
                    $aId = array();
                    array_push($aId,$value->id);
                }
            }catch(\Exception $e){
                $aId = array();
                array_push($aId,$value->id);
            }
            }
            if(isset($aId[0])){
                $aId = array_unique($aId);
                (new Slack(new Request(['title'=>'SMS/Email Send Failed/Error for Agency Applications','fields'=>['application_ids'=>json_encode($aId)]])))->send();
            }
        }
    }

    public function getCancellationCalculatorIndex(){
        return view('admin.collection.cancellation_calculator');
    }

    public function getCancellationCalculator($id){


        if($id){

            $queryData = DB::select("select ca.id, ca.approved_amount, m.is_collected, mo.processing_fee_first_emi, pfi.processing_fee_by_merchant, ec.processing_fee, cae.emi_paid, cae.principal_due, cae.total_amount_due, (select DATE_FORMAT(status_logs.created_at, '%Y-%m-%d') from status_logs where status_logs.application_id = ca.id and status_logs.status = 'disbursed' order by id desc limit 1) as disbursed_date  from consumer_application as ca
left join emi_calculation as ec on ec.application_id = ca.id
left join pro_forma_invoice as pfi on pfi.application_id = ca.id
left join merchant as m on m.id = ca.merchant_id
left join merchant_offers as mo on mo.id = ca.offer_id
left join consumer_application_element as cae on cae.application_id = ca.id
where ca.id=".$id);

            foreach ($queryData as $query) {
                if (isset($query->id)) {

                    $diffInDays = Carbon::parse($query->disbursed_date)->diffInDays(Carbon::now());

                    //$diffInDays = $date1->diffInDays(Carbon::now());

                    $loan_disbursed_amount = (new ConsumerApplicationController())->getDisbursementAmount($id);

                    $merchant_processing_collected = $query->is_collected == 1 ? $query->is_collected : ($query->processing_fee_by_merchant == 1 ? $query->processing_fee_by_merchant : 0);


                    $total_paid_emi = PaymentTransaction::where('application_id', $id)->where('payment_for', 'EMI')->whereNull('deleted_at')->whereNull('is_deleted_at')->sum('amount');

                    /*foreach ($data['paymentsData'] as $payment){
                        if($payment->payment_status == 'success') {
                            $total_paid_emi += $payment->emi_amount;
                        }else if ($payment->payment_status == 'partial'){
                            $total_paid_emi += $total_paid_emi + ($payment->total_payable - $payment->amount_due);
                        }
                    }*/

                    $processing_fee = $merchant_processing_collected ? $query->processing_fee : 0;

                    if(!$processing_fee && $query->processing_fee_first_emi){
                        $isProcessingFeePaid = PaymentTransaction::where('application_id', $id)->where('payment_for', 'processing_fee')->whereNull('deleted_at')->whereNull('is_deleted_at')->first(['id']);
                        if (!isset($isProcessingFeePaid->id)) {
                            $processing_fee = $query->processing_fee;
                        }
                    }

                    $principal_outstanding = $query->approved_amount;//$query->approved_amount - $total_paid_emi;
                    $cancellation_fee = ($principal_outstanding * 1.4 * ceil($diffInDays/30)) / 100;
                    $total_payable = ($loan_disbursed_amount + $cancellation_fee + $processing_fee);

                    $data = [
                        'disbursed_date' => $query->disbursed_date,
                        'total_days' => $diffInDays,
                        'loan_disbursed_amount' => $loan_disbursed_amount,
                        'loan_amount' => $query->approved_amount,
                        'total_paid_emi' => $total_paid_emi,
                        'principal_outstanding' => $principal_outstanding,//$query->principal_due,
                        'processing_fee' => $processing_fee,
                        'total_payable' => round($total_payable),
                        'cancellation_fee' => round($cancellation_fee),
                        'final_payable' => round($total_payable)
                    ];

                    return response()->json(['status' => 'OK', 'message' => 'getCancellationCalculator', 'data' => $data]);
                }
            }
        }

        return response()->json(['status' => 'FAIL', 'message' => 'getCancellationCalculator']);
    }

    public function getAccountAggregatorReport($id){

        $data = [];
        $accountAggregatorMonitoring = AccountAggregatorMonitoring::where('status', 1)->whereNull('is_subsidy_received')->orWhere('is_subsidy_received', 2)->get(['id', 'account_aggregator_id']);
        foreach($accountAggregatorMonitoring as $accountAggregatorMonitoringItem) {
            $accountAggregator = AccountAggregatorModel::where('id', $accountAggregatorMonitoringItem->account_aggregator_id)->first(['id', 'application_id', 'created_at', 'updated_at', 'consent_status']);
            $application = ConsumerApplication::where('id', $accountAggregator->application_id)->first(['id', 'name']);

            $job = JobProvider::where('application_id', $accountAggregator->id)->where('from', 'create_monitoring_request')->first(['id', 'available_at', 'is_available', 'updated_at', 'created_at']);

            if (isset($accountAggregator->consent_status) && $accountAggregator->consent_status == 1) {

                if($job->is_available){
                    if ($job->available_at < date('Y-m-d')){
                        $available_at_new = now()->addDays((new AccountAggregator($this->request))->getMonitoringFrequency())->toDateTimeString();
                        JobProvider::where('id',$job->id)->update(['is_available' => 0, 'available_at' => $available_at_new]);
                    }
                }

                $data[] = [
                    'id' => $application->id,
                    'account_aggregator_id' => $accountAggregatorMonitoringItem->account_aggregator_id,
                    'account_aggregator_monitoring_id' => $accountAggregatorMonitoringItem->id,
                    'name' => $application->name,
                    'fetched_at' => $job->updated_at != '' && $job->updated_at != null && $job->updated_at != '1970-01-01'  ? date('Y-m-d', strtotime($job->updated_at)) :  date('Y-m-d', strtotime($job->created_at)),
                    'status' => 'Success',
                    'next_fetch_date' => isset($job->available_at) ? date('Y-m-d', strtotime($job->available_at)) : '',
                    'reports' => $this->getAAFetchedData($application->id, $accountAggregatorMonitoringItem->account_aggregator_id)
                ];
            }
        }

        $default_frequency  = (new AccountAggregator(new Request()))->getMonitoringFrequency();

        return view('admin.collection.account_aggregator_report', ['data' => $data, 'default_frequency' => $default_frequency]);
    }

    private function getAAFetchedData($id, $account_aggregator_id){
        $s3 = Storage::disk('s3');
        $dataObjects = [];

        $filePath = $id.'/'.$account_aggregator_id.'_account_aggregator.json';
        if ($s3->exists($filePath)) {
            $data = $s3->get($filePath);
            $accounts = json_decode($data, 1);

            foreach($accounts['account'] as $account){

                $period = $account['data']['Transactions']['startDate'].'-'.$account['data']['Transactions']['endDate'];
                $transactions = [];
                $subsidy_flag = 'No';
                if(isset($account['data']['Transactions']['Transaction']) && $account['data']['Transactions']['Transaction']) {
                    foreach ($account['data']['Transactions']['Transaction'] as $transaction) {
                        if ($transaction['type'] == 'CREDIT') {
                            if ((strpos(strtolower($transaction['narration']), strtolower('subsidy')) !== false || strpos(strtolower($transaction['narration']), strtolower('Credit Through PFMS')) !== false) && ($transaction['amount'] >= 30000 && $transaction['amount'] <= 150000)) {
                                $subsidy_flag = 'Yes';
                            }
                            $transactions[] = [
                                "type" => $transaction['type'],
                                "mode" => $transaction['mode'],
                                "amount" => $transaction['amount'],
                                "transactionTimestamp" => $transaction['transactionTimestamp'],
                                "txnId" => $transaction['txnId'],
                                "narration" => $transaction['narration']
                            ];
                        }
                    }
                }

                $dataObjects[] = [
                    'bank_name' => $account['data']['fipName'] ?? 'No Bank',
                    'account_number' => $account['data']['maskedAccountNumber'] ?? ($account['data']['maskedAccNumber'] ?? '-'),
                    'accountType' => $account['data']['accountType'] ?? '-',
                    'period' => $period,
                    'subsidy_flag' => $subsidy_flag,
                    'transactions' => $transactions
                ];
            }
        }

        return $dataObjects;
    }
}