<?php
/**
 * Created by PhpStorm.
 * User: sunil
 * Date: 12/11/18
 * Time: 2:02 PM
 */

namespace App\Imports;

use App\ApplicationEMICalculation;
use App\ApprovedPincodeLists;
use App\Company;
use App\ConsumerApplication;
use App\EmiPayment;
use App\Exports\Exporter;
use App\Http\Controllers\Admin\AccountingController;
use App\Http\Controllers\Admin\ConsumerApplicationController;
use App\Http\Controllers\Admin\ConsumerDocument;
use App\Http\Controllers\Admin\MerchantController;
use App\Http\Controllers\ApiController;
use App\Invoices;
use App\Lenders;
use App\LoanManagement;
use App\Mandate;
use App\Merchant;
use App\MerchantCategory;
use App\MerchantLocation;
use App\Moratorium;
use App\NachSupportedBank;
use App\PaymentEmi;
use App\PaymentTransaction;
use App\Pincode;
use App\ProFormaInvoice;
use App\Settlement;
use App\Slack;
use App\StateModel;
use App\StatusLogs;
use App\Tally\TallyListener;
use App\User;
use App\WhatsApp;
use Carbon\Carbon;
use Illuminate\Http\Request;
use Illuminate\Support\Collection;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Mail;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\WithMultipleSheets;
use Maatwebsite\Excel\Facades\Excel;
use MathPHP\Finance;

class TestImport implements ToCollection
{

    use Importable;
    private $request;

    public function __construct(Request $request){
        $this->request = $request;
    }

    /**
     * @param Collection $collection
     */
    public function collection(Collection $collection)
    {
        $name = $this->request->get('from');
        $this->$name($collection);
    }

    public function setPreApprovedAndDoc($collection){
        unset($collection[0]);
        $response = [];
        foreach ($collection as $item){
            $response = array(
                'id' => $item[0],
                'name' => $item[2],
                'is_auto_doc' => $item[51] ? $item[51] : 0,
                'is_auto_approve' => $item[52] ? $item[52] : 0,
                'pre_approval' => $item[56] ? $item[56] : 0
            );


            $merchant = Merchant::where('id',$response['id'])->first(['grade_id','is_auto_doc','is_auto_approve','pre_approval']);
            if(isset($merchant->grade_id)) {
                $data = [
                    'grade_id' => $merchant ? $merchant->grade_id : 1,
                    'is_auto_doc' => $response['is_auto_doc'],
                    'is_auto_approve' => $response['is_auto_approve'],
                    'pre_approval' => $response['pre_approval']
                ];

                $merchant_s = Merchant::find($response['id']);
                $merchant_s->fill($data);
                $merchant_s->save();
            }
        }
        return $response;
    }

    public function importMerchantCategoryFile($collection){
        $response = [];
        foreach ($collection as $item){
            if ($item[2]) {
                $response[trim($item[1])][] = ['merchant' => $item[0], 'id' => $item[2]];
                $dd[$item[1]] = trim($item[1]);
            }
        }
        $cat = MerchantCategory::whereIn('name',array_keys($dd))->get(['id','name']);
        $cats = [];
        foreach ($cat as $c){
            $cats[$c->name] = $c->id;
        }
        $data = [];
        foreach ($response as $category => $item){
            foreach ($item as $cp) {
                $data[$cp['id']] = $cats[$category];
            }
        }

        foreach ($data as $_id => $s){
            Merchant::where('id',$_id)->update(['category_id' => $s]);
        }

        return $response;
    }

    public function importMerchantCenterVLCCUsers($collection){
        $response = [];
        $merchant = (new MerchantController());
        unset($collection[0]);
        foreach ($collection as $item) {

            $mobile_number = explode('/',$item[4])[0];
            $code = $merchant->getReferralCode();
            $MerchantLocation = MerchantLocation::where('name',$item[2])->first(['id']);
            $response[] = [
                'merchant_id' => 83,
                'name' => $item[3],
                'username' => str_replace(' ','_',strtolower($item[3])),
                'email' => $item[9],
                'mobile_number' => $mobile_number,
                'dob' => null,
                'gender' => 'male',
                'password' => '',
                'user_type' => 3,
                'center_id' => isset($MerchantLocation->id) ? $MerchantLocation->id : '',
                'code' => $code,
                'created_at' => Carbon::now()->toDateTimeString()
            ];
        }

        User::insert($response);
    }

    public function importMerchantCenterVLCC($collection){
        $response = [];
        $states = StateModel::all();
        $_states = [];
        foreach ($states as $state){
            $_states[$state->name] = $state->id;
        }

        foreach ($collection as $item) {
            $response[] = [
                'merchant_id' => 83,
                'name' => $item[2],
                'slug' => $this->random_strings(6),
                'email' => $item[9],
                'phone' => $item[4],
                'location' => $item[7],
                'gst_number' => '',
                'state' => isset($_states[$item[1]]) ? $item[1] : '',
                'created_at' => Carbon::now()->toDateTimeString()
            ];
        }

        MerchantLocation::insert($response);
    }

    public function importMerchantLocation($collection){
        unset($collection[0]);

        $merchant = new MerchantController();
        foreach ($collection as $item) {

            $slug = strtoupper(substr($item[2], 0, 3).substr($item[4], 0, 2));

            $response = [
                'merchant_id' => 6425,
                'name' => $item[2],
                'slug' => $slug,
                'state' => $item[4],
                'location' => $item[5],
                'phone' => $item[7],
                'email' => $item[8]
            ];

            $merchant_location_id = MerchantLocation::insertGetId($response);

            $users[] = [
                'name' => $item[6],
                'mobile_number' => $item[7],
                'email' => $item[8],
                'user_type' => 3,
                'code' => $merchant->getReferralCode(),
                'merchant_id' => 6425,
                'center_id' => $merchant_location_id
            ];
        }

        $d = 4;
        User::insert($users);


    }

    function random_strings($length_of_string) {
        return substr(sha1(time()), 0, $length_of_string);
    }

    public function processingFeeEntries($collection){

        unset($collection[0]);

        foreach ($collection as $item) {

            $PaymentEmi = new LoanManagement();

            $application = ConsumerApplication::where('id', $item[0])->first(['lender_id']);
            $bank_id = isset($application->lender_id) && $application->lender_id == 2 ? 1 : 2;

            $data = [
                "payment_type" => "processing_fee",
                "mode" => 'Adjusted In disbursement',
                "paid_by" => "",
                "application_id" => $item[0],
                'collected_by_type' => $item[5],
                "collected_by" => 251,
                "trasanction_id" => "",
                "bank_id" => $bank_id,
                "wallet_name" => "",
                "payment_date" => $item[8],
                "payment_reconciliation_date" => $item[8],
                "fore_closure_date" => null,
                "amount" => $item[11],
                "advance_amount" => 0,
                "trasanction_acknowledgement" => "",
                "online_payment_id" => ""
            ];

            $PaymentEmi->paymentReconcile($data);
        }


        /*foreach ($collection as $item) {
            $payment = PaymentTransaction::where('application_id', $item[0])->where('payment_for','like','%processing_%')->first();
            if (isset($payment->id) && $payment->amount == $item[2]){
                $date[] = ['payment_date' => $item[1],'id' => $item[0]];
                PaymentTransaction::where('id', $payment->id)->update(['payment_date' => $item[1], 'payment_reconciliation_date' => $item[1]]);
            }else{
                $check[] = $item[0];
            }
        }

        $file = 'processing_fee_data_final_c.xlsx';

        $requestData = new Request(['data' => $check, 'sheet' => 'Sheet1']);
        Excel::store((new Exporter($requestData)), $file);

        $file = 'processing_fee_data_final_d.xlsx';

        $requestData = new Request(['data' => $date, 'sheet' => 'Sheet1']);
        Excel::store((new Exporter($requestData)), $file);


return 4;*/

        /*$responseData = [];
        foreach ($collection as $index=>$item){

            $applicationEMICalculation = ApplicationEMICalculation::where('application_id',$application_id)->first(['processing_fee']);

            if (isset($applicationEMICalculation->processing_fee) && $applicationEMICalculation->processing_fee > 0) {
                $responseData[] = [
                    'name' => $item[1],
                    "payment_type" => "processing_fee",
                    "mode" => "Cash",
                    "paid_by" => "",
                    "application_id" => $application_id,
                    "collected_by" => 251,
                    "trasanction_id" => "1",
                    "go_maker_check" => true,
                    "bank_id" => "1",
                    "wallet_name" => "",
                    "payment_date" => date('Y-m-d', strtotime($item[8])),
                    "payment_reconciliation_date" => date('Y-m-d', strtotime($item[8])),
                    "fore_closure_date" => null,
                    "amount" => $applicationEMICalculation->processing_fee,
                    "advance_amount" => 0,
                    "trasanction_acknowledgement" => "",
                    "online_payment_id" => "",
                    'status' => isset($response1[strtolower($item[1])]) ? $response1[strtolower($item[1])] : '',
                ];
            }else{
                $check[] = ['id' => $application_id, 'item' => $item[1]];
            }
        }

        $file = 'processing_fee_data_final5.xlsx';

        $requestData = new Request(['data' => $responseData, 'sheet' => 'Sheet1']);
        Excel::store((new Exporter($requestData)), $file);

        $file = 'processing_fee_data_final6.xlsx';

        $requestData = new Request(['data' => $check, 'sheet' => 'Sheet1']);
        Excel::store((new Exporter($requestData)), $file);*/


    }

    public function uploadPincodeLists($collection){
        $response = [];
        unset($collection[0]);
        foreach ($collection as $item) {
            if($item[0]) {
                $response[] = [
                    'pincode' => $item[0],
                    'taluk' => trim($item[1]) == 'NA' ? trim($item[2]) : trim($item[1]),
                    'districtname' => trim($item[2]),
                    'state' => trim($item[3]),
                    'created_at' => Carbon::now()->toDateTimeString()
                ];
            }
        }

        if ($response) {
            Pincode::insert($response);
        }
    }

    public function uploadBankDetails($collection){

        $response = [];
        unset($collection[0]);
        foreach ($collection as $item) {
            $response[] = [
                'bank_code' => $item[1],
                'bank' => trim($item[2]),
                'type_of_bank' => $item[3] != 'NA' ? $item[3] : '',
                'ifsc' => $item[4] != 'NA' ? $item[4] : '',
                'micr' => $item[5] != 'NA' ? $item[5] : '',
                'iin' => $item[6] != 'NA' ? $item[6] : '',
                'created_at' => Carbon::now()->toDateTimeString()
            ];
        }

        if ($response) {
            NachSupportedBank::insert($response);
        }
    }

    public function importZokoDocuments($collection){
        unset($collection[0]);
        $directory = "/Users/sunilkhorwal/Sites/zoko_backup/ceb767af-f50f-48db-a560-fc448c62b50b/";
        $request = new Request(['test_bot' => true]);

        $files2 = scandir($directory, 1);
        $name = [];
        foreach ($files2 as $item){
            $nameE = explode('.', $item)[0];
            $name[$nameE] = trim($item);
        }


        $document = new ConsumerDocument($request);
        foreach ($collection as $item){
            if (isset($name[$item[0]]) && file_exists($directory.$name[$item[0]])){
                $ids = explode(',', $item[1]);
                foreach ($ids as $_id) {
                    $document->uploadToS3($item[2], $_id, file_get_contents($directory . $name[$item[0]]), '.' . explode('.', $name[$item[0]])[1]);
                }
                $done[] = $item[0];
            }else{
                $d[] = $item[0];
            }
        }

        return $directory;
    }

    public function uploadPendingLotusPayEntries($collection){
        $response = [];
        try {
            unset($collection[0]);
            foreach ($collection as $item) {
                $bank_id = (new ConsumerApplicationController())->getTransactionBank($item[5]);
                $response[] = [
                    'amount' => $item[0],
                    'payment_date' => $item[1],
                    'mode' => "NACH-LOTUS",
                    'paid_by' => "",
                    'collected_by' => 0,
                    'wallet_name' => "",
                    'bank_id' => $bank_id,
                    'trasanction_id' => $item[3],
                    'online_payment_id' => 0,
                    'trasanction_acknowledgement' => "",
                    'status' => 'success',
                    'reason' => '',
                    'umrn' => $item[4],
                    'application_id' => $item[5]
                ];
            }

            $p = [];
            foreach ($response as $item) {
                try {
                    $payment = new LoanManagement();
                    $payment->paymentReconcile($item);
                    $p[] = $item;
                }catch (\Exception $e){
                    $s = $e;
                }
            }
        }catch (\Exception $e){
            (new Slack(new Request(['title' => __FUNCTION__ . ' Error', 'fields' => ['e' => $e->getMessage(),'l'=>$e->getLine(),'t'=>$e->getTraceAsString()], 'group' => Slack::$STAGE_TESTING])))->send();
        }

        return 'done';
    }

    public function uploadNachData($collection){
        $errors = [];
        unset($collection[0]);
        foreach ($collection as $item) {
            $Mandate = Mandate::where('umrn', trim($item[0]))->get(['id']);
            if (count($Mandate) == 1){
                Mandate::where('id', $Mandate[0]->id)->update(['expiry_date' => trim($item[1])]);
            }else{
                $errors[] = $item[0];
            }
        }
        return $errors;
    }

    public function uploadPendingCollectionReport($collection){
        $response = [];
        foreach ($collection as $item) {
            if($item[0]) {
                $response[] = $item[0];
            }
        }

        if ($response) {
            Merchant::whereIn('id', $response)->update(['pending_collection_report' => 1]);
        }
    }

    public function uploadPincodeDataForApprover($collection){
        $response = [];
        foreach ($collection as $item) {
            $response[] = ['pin_codes' => $item[0]];
        }

        ApprovedPincodeLists::insert($response);
    }

    public function rejectBulkV1($collection){
        unset($collection[0]);
        foreach ($collection as $item) {
            $applicationIds[] = $item[0];
        }


        $repeat = [];

        $applications = ConsumerApplication::whereIn('id', $applicationIds)->get();

        $updateIds = [];
        $ddp = [];

        foreach ($applications as $item){

            /*if($item->email) {

                $data['loan_amount_'] = $item->loan_amount;
                $data['name'] = $item->name;

                $response = ['to' => $item->email, 'subject' => 'Thanks for your loan application'];

                \Mail::send('emails.customer_loan_reject', [
                    'data' => $data,
                ], function ($message) use ($response) {
                    $message->to($response['to'])->subject($response['subject']);
                    $message->cc(_env('NOTIFY_EMAIL', ''));
                });
            }*/
            $updateIds[] = $item->id;

            $ddp[] = [
                'application_id' => $item->id,
                'user_id' => 107,
                'status' => 'rejected',
                'reason' => 'Not Serviceable',
                'created_at' => Carbon::now()->toDateTimeString()
            ];


        }


        ConsumerApplication::whereIn('id', $updateIds)
            ->update(['state' => 'rejected']);

        StatusLogs::insert($ddp);

        return $repeat;
    }

    public function rejectBulk($collection){
        $response = [];
        unset($collection[0]);
        foreach ($collection as $item) {
            $applicationIds[] = $item[0];
        }


        $repeat = [];

        $applications = ConsumerApplication::whereIn('id', $applicationIds)->get();
        $apiController = new ApiController();

        $updateIds = [];
        $ddp = [];
        foreach ($applications as $item){

        $existing = $apiController->isExistingCustomer(new Request([
            'pan_card' => $item->pan_card,
            'mobile_number' => $item->mobile_number,
            'email' => $item->email,
        ]));


        $d = $existing->getData();

        if(!$d->data) {
            $existing = $apiController->isExistingCustomer(new Request([
                'pan_card' => $item->co_applicant_pan_card,
                'mobile_number' => $item->co_applicant_mobile_number,
                'email' => $item->co_applicant_email,
            ]));

         $d = $existing->getData();
        }


        if($d->data && count($d->data) >= 2){

            $pp = [];
            foreach ($d->data as $dd){
                if(!in_array($dd->id, $pp) && $dd->id != $item->id) {
                    $pp[] = $dd->id;
                }
            }

            if($pp) {
                $repeat[] = $d->data;
            }else{

                $updateIds[] = $item->id;

                $ddp[] = [
                    'application_id' => $item->id,
                    'user_id' => 107,
                    'status' => 'rejected',
                    'reason' => 'Not Serviceable',
                    'created_at' => Carbon::now()->toDateTimeString()
                ];
            }
        }else{

            $updateIds[] = $item->id;

            $ddp[] = [
                'application_id' => $item->id,
                'user_id' => 107,
                'status' => 'rejected',
                'reason' => 'Not Serviceable',
                'created_at' => Carbon::now()->toDateTimeString()
            ];

        }

        }


        ConsumerApplication::whereIn('id', $updateIds)
            ->update(['state' => 'rejected']);

        StatusLogs::insert($ddp);

        return $repeat;
    }

    public function downloadMoratoriumSheet($collection){

        $response = [];
        unset($collection[0]);
        foreach ($collection as $item) {
            $id = $item[0];

            $application = ConsumerApplication::with('merchant', 'emi', 'application_element', 'emi.offer')->where('id', $id)->whereNull('is_deleted')->first();
            $loanamount = ((isset($application->approved_amount) && $application->approved_amount != 0) ? $application->approved_amount : $application->loan_amount) - $application->emi->upfront_from_customer;


            $payment_details = array();
            $payment_details_data = EmiPayment::with('transaction')->where('application_id', $id)->get();
            foreach ($payment_details_data as $payments) {
                $payment_details[$payments['emi_date']] = $payments;
            }

            $subvention_amount = (isset($application->emi->subvention) && $application->emi->subvention > 0) ? ((((isset($application->approved_amount) && $application->approved_amount != 0) ? $application->approved_amount : $application->loan_amount) * ($application->emi->subvention)) / 100) : 0;
            $amount_disbursed = $loanamount - $subvention_amount;
            $loan_amount_subvention = $loanamount - $subvention_amount;
            $payment = new PaymentEmi();
            $emi_dues = $payment->generateDues($application->id, 1);

            $payment_frequency_offer = [1 => 2, 3 => 0.5, 4 => 0.07];

            if(!in_array($application->emi->payment_frequency, [1])){
                $d = 4;
                continue;
            }

            $payment_frequency = $payment_frequency_offer[$application->emi->payment_frequency];

            if (true) {
                $emis_array = array();
                $emi_irr_array = array();
                array_push($emis_array, -$loanamount);
                array_push($emi_irr_array, -($amount_disbursed));

                if (isset($emi_dues['payload'])) {
                    foreach ($emi_dues['payload'] as $emis) {
                        array_push($emis_array, $emis['amount']);
                        array_push($emi_irr_array, $emis['amount']);

                    }
                }
                $rate = (Finance::irr($emis_array)) * 100;
                if ($rate < 0) {
                    $rate = 0;
                }
                $irr_rate = (Finance::irr($emi_irr_array)) * 100;
                if ($irr_rate < 0) {
                    $irr_rate = 0;
                }

            }
            $i = 0;
            if (isset($emi_dues['payload'])) {
                $principal_oustanding = $loanamount;
                $principal_oustanding_subvention = $loan_amount_subvention;
                $subvention_amount_balance = $subvention_amount;
                foreach ($emi_dues['payload'] as $emis) {
                    if ($emis['emi_date'] <= Carbon::now()->endOfMonth()->toDateString()) {
                        $emi_dues['payload'][$i]['interest'] = $principal_oustanding * ($rate / 100);
                        $emi_dues['payload'][$i]['principal'] = $emis['amount'] - $emi_dues['payload'][$i]['interest'];
                        $emi_dues['payload'][$i]['irr'] = $principal_oustanding_subvention * ($irr_rate / 100);
                        $emi_dues['payload'][$i]['subvention'] = $emi_dues['payload'][$i]['irr'] - $emi_dues['payload'][$i]['interest'];
                        $subvention_amount_balance = $subvention_amount_balance - $emi_dues['payload'][$i]['subvention'];
                        $emi_dues['payload'][$i]['subvention_amount_balance'] = $subvention_amount_balance;
                        $principal_oustanding = $principal_oustanding - $emi_dues['payload'][$i]['principal'];
                        $principal_oustanding_subvention = $principal_oustanding_subvention - $emi_dues['payload'][$i]['principal'] + $emi_dues['payload'][$i]['subvention'];
                        $emi_dues['payload'][$i]['principal_outstanding'] = $principal_oustanding;
                        $emi_dues['payload'][$i]['principal_outstanding_subvention'] = $principal_oustanding_subvention;
                        $i++;
                    }
                }
            }


            try {
                $moratorium_amount = round(($emi_dues['payload'][$i - 1]['principal_outstanding'] + $emi_dues['payload'][$i - 1]['amount']) * $payment_frequency / 100);
            }catch (\Exception $e){
                $moratorium_amount = 0;
                $llll[] = $item[0];
            }

            $response[] = ['id' => $id, 'moratorium_amount' => $moratorium_amount];
        }

        $file = 'moratorium_with_amount16.xlsx';

        $requestData = new Request(['data' => $response, 'sheet' => 'Sheet1']);
        Excel::store((new Exporter($requestData)), $file);
    }


    public function sendMoratoriumCommunications($collection){

        $response = [];
        unset($collection[0]);
        foreach ($collection as $item) {

            try {
                if($item[0] && $item[1]) {
                    $id = $item[0];
                    $amount = $this->moneyFormatIndia($item[1]);

                    $application = ConsumerApplication::where('id', $id)->first(['id', 'email', 'name','mobile_number']);

                    if($application->mobile_number || $application->email) {
/*                        $body = "Dear " . ucfirst(strtolower($application->name)) . ",<br/>
We assume that you want to defer your April month EMI. Your total moratorium interest for the month of april is Rs " . $amount . " which you have to pay.<br/><br/>
If you want to pay your EMI on a regular basis contact immediately on 8850077108 or 8928769161 you can also email us on payments@creditfair.in<br/>";*/

                        /*$body = "Dear " . ucfirst(strtolower($application->name)) . ",<br/>
You can now save your moratorium interest of Rs " . $amount . " by paying your EMI amount on regular basis.<br/>
There will be no additional charges or late penalties for EMI due on ".$date."<br/><br/>
If you want to pay your EMI on a regular basis immediately contact at payments@creditfair.in or call us at 8850077108 or 8928769161<br/>";*/

                        $body = "Dear " . ucfirst(strtolower($application->name)) . ",<br/><br>
We assume that you want to defer your May month EMI. Your total moratorium interest for the month of april is Rs ".$amount." which you have to pay.<br/><br/>

If you want to pay your EMI on a regular basis contact immediately on 8850077108 or 8928769161 & 7900125682 you can also email us on payments@creditfair.in<br/><br/>";

                        if ($application->email) {
                            /*Mail::send('emails.reminders.basic_reminder',
                                ['body' => $body]
                                , function ($message) use ($amount, $application) {
                                    $message->to($application->email)->subject('Last chance to save Rs '.$amount);*/
                                    /*$message->attach(storage_path() . '/covid-19-donating-essentials.jpeg', ['as' => 'COVID-19 Donating Essentials']);
                                    $message->attach(storage_path() . '/covid-19-donating-essentials-1.jpeg', ['as' => 'COVID-19 Donating Essentials']);
                                    $message->attach(storage_path() . '/covid-19-donating-essentials-2.jpeg', ['as' => 'COVID-19 Donating Essentials']);
                                    $message->attach(storage_path() . '/covid-19-donating-essentials-3.jpeg', ['as' => 'COVID-19 Donating Essentials']);
                                    $message->attach(storage_path() . '/covid-19-donating-essentials-4.mp4', ['as' => 'COVID-19 Donating Essentials']);*/
                                /*});*/
                        }

                        $body .= "Regards,<br>Team Credit Fair";
                        send_sms($application->mobile_number, $body);
                        //break;
                        /*$request = new Request([
                            'templateId' => '',
                            'templateArgs' => array(),
                            'templateType' => 'text',
                            'messageString' => $body,
                            'to' => '919920820531'
                        ]);

                        $whatsApp = new WhatsApp($request);
                        return $whatsApp->sendMessage();*/
                    }
                    //sendWhatsAppMessage();
                }
            }catch (\Exception $e){
                $response[] = $id;
            }
        }

    }

    public function saveMoratoriumData($collection){
        $response = [];
        foreach ($collection as $item) {


            $isExist = EmiPayment::where('application_id', $item[0])->where('emi_date', $item[1])->first(['id','payment_status']);

            $moratorium = Moratorium::where('application_id',$item[0])->where('emi_date', $item[1])->first(['id']);

            if(isset($isExist->id) && $isExist->payment_status == 'rejected'){
                EmiPayment::where('id', $isExist->id)->update(['payment_status' => 'pending']);
            }

            if(isset($isExist->id) && (in_array($isExist->payment_status, ['pending','rejected'])) && !isset($moratorium->id)) {
                $response[] = [
                    'application_id' => $item[0],
                    'emi_date' => $item[1],
                    'interest_rate' => 2,
                    'status' => 1,
                    'user_id' => 107,
                    'started_date' => Carbon::now()->toDateTimeString(),
                    'created_at' => Carbon::now()->toDateTimeString(),
                ];
            }else{
                $d[] = $item[0];
            }
        }

        Moratorium::insert($response);
    }

    public function waiveOffData($collection){

        foreach ($collection as $index => $item) {

            $payment = EmiPayment::where('application_id', $item[0])->where('emi_date', $item[1])->first();

            if(isset($payment->id)) {
                $amount = $payment->bounce_charges + $payment->delay_penalty;
                if($amount) {
                    $done[] = $item[0];
                }else{
                    $doneNoBP[] = $item[0];
                }
            }else{
                $notDone[] = $item[0];
            }
        }

        return 4;
    }

    function moneyFormatIndia($num) {
        $explrestunits = "" ;
        if(strlen($num)>3) {
            $lastthree = substr($num, strlen($num)-3, strlen($num));
            $restunits = substr($num, 0, strlen($num)-3); // extracts the last three digits
            $restunits = (strlen($restunits)%2 == 1)?"0".$restunits:$restunits; // explodes the remaining digits in 2's formats, adds a zero in the beginning to maintain the 2's grouping.
            $expunit = str_split($restunits, 2);
            for($i=0; $i<sizeof($expunit); $i++) {
                // creates each of the 2's group and adds a comma to the end
                if($i==0) {
                    $explrestunits .= (int)$expunit[$i].","; // if is first value , convert into integer
                } else {
                    $explrestunits .= $expunit[$i].",";
                }
            }
            $thecash = $explrestunits.$lastthree;
        } else {
            $thecash = $num;
        }
        return $thecash; // writes the final format where $currency is the currency symbol.
    }

    public function uploadInvoiceData($collection){
        foreach ($collection as $index => $item) {
            $type = $item[2] == 'Subvention' ? 'subvention_invoice' : 'processing_invoice';
            /*if($type == 'subvention_invoice'){
                (new ConsumerApplicationController())->invokeCommunication($item[0], true);
            }*/


            //Invoices::where('application_id', $item[0])->where('type',$type)->update(['invoice_id' => $item[1]]);

        }


        $invoices = DB::select('select * from invoices where created_at >= "2020-07-01 00:00:00" order by created_at asc');

        $number = 2000243;
        foreach ($invoices as $invoice){

            $invoice_id = 'CF-'.$number.'-20-21';

            Invoices::where('id', $invoice->id)->update(['invoice_id' => $invoice_id]);
            $number = $number + 1;
        }

        return 4;
    }

    public function uploadExGratia($collections){

        (new Slack(new Request(['title' => __FUNCTION__ . ' Started', 'fields' => ['count' => count($collections)], 'group' => Slack::$STAGE_TESTING])))->send();
        $i = 0;
        foreach ($collections as $key => $collection) {

            try {
                $amount = $collection[2];
                $application_id = $collection[0];

                $application = ConsumerApplication::find($application_id);

                $payload = [
                    "amount_due" => "0",
                    "max_payable" => "0",
                    "amount_pending" => "0",
                    "over_payment" => "0",
                    "processing_fee" => "0",
                    "insurance_fee_due" => "0",
                    "redirect_link" => null,
                    "foreclosure_date" => null,
                    "only_restructure" => null,
                    "bank_id" => "1",
                    "payment" => "refund",
                    "payment_mode" => "NEFT",
                    "pay_by_type" => null,
                    "paid_by" => $application->name,
                    "paid_by_value" => "107",
                    "paid_by_other" => null,
                    "collected_by_merchant" => "1",
                    "collected_by" => null,
                    "collected_by_value" => null,
                    "transaction_id" => 'GRATIA' . time() . $key,
                    "wallet_name" => null,
                    "payment_date" => "2020-11-11",
                    "payment_reconciliation_date" => "2020-11-11",
                    "amount_collected" => $amount
                ];


                $accountingController = new LoanManagement();
                $accountingController->paymentReconcile([
                    'payment_type' => $payload['payment'],
                    'mode' => $payload['payment_mode'],
                    'paid_by' => $payload['paid_by'],
                    'application_id' => $application_id,
                    'collected_by' => $payload['collected_by_value'],
                    'collected_by_type' => $payload['collected_by_merchant'],
                    'adjust_with_disbursement' => 0,
                    'trasanction_id' => $payload['transaction_id'],
                    //'go_maker_check' => true,
                    'bank_id' => $payload['bank_id'],
                    'wallet_name' => '',
                    'payment_date' => $payload['payment_date'],
                    'payment_reconciliation_date' => $payload['payment_reconciliation_date'],
                    'fore_closure_date' => $payload['foreclosure_date'],
                    'amount' => $payload['amount_collected'],
                    'advance_amount' => 0,
                    'trasanction_acknowledgement' => '',
                    'online_payment_id' => ''
                ]);
                $i++;
            }catch (\Exception $e){
                $p[] = ['id', $application_id, 'e' => $e->getMessage(), 'l' => $e->getLine(), 't' => $e->getTraceAsString()];
            }
        }

        if(isset($p)) {
            (new Slack(new Request(['title' => __FUNCTION__ . ' Error', 'fields' => ['data' => json_encode($p)], 'group' => Slack::$STAGE_TESTING])))->send();
        }

        (new Slack(new Request(['title' => __FUNCTION__ . ' Done', 'fields' => ['count' => $i], 'group' => Slack::$STAGE_TESTING])))->send();
    }

    public function tallyImportData($collections){


        $reasons = [
            1 => 'Account Closed',
            2 => 'No Such Account',
            4 => 'Balance Insufficient',
            5 => 'Not Arranged For/Amount Exceeding Limit set by A/c Holder',
            6 => 'Payment Stopped by Drawer',
            7 => 'Payment Stopped Under Court Order/Account Under Litigation',
            11 => 'Invalid IFSC/MICR code',
            12 => 'Mismatch in mandate frequency',
            13 => 'Duplicate transaction - transaction already debited either under ACh or NACH debit(ECS)',
            14 => 'Mandate Expired',
            15 => 'Incorrect amount-Mismatch between mandate & transaction',
            16 => 'Customer Name Mismatch',
            17 => 'Returned as per customer request',
            51 => 'Miscellaneous - KYC Documents Pending',
            52 => 'Miscellaneous - Documents Pending for Account Holder Turning Major',
            53 => 'Account inoperative',
            54 => 'Dormant account',
            55 => 'Miscellaneous - A/c in Zero Balance/No Transactions have Happened, First Transaction in Cash or Self Cheque',
            56 => 'Small Account, first transaction to be from Base Branch',
            57 => 'Miscellaneous - Amount exceeds limit set on account by bank for debit per transaction',
            58 => 'Miscellaneous - Account reached maximum debit limit set on account by bank',
            59 => 'Miscellaneous - Network Failure (CBS)',
            60 => 'Account Holder Expired',
            61 => 'Mandate Cancelled',
            68 => 'Account Blocked / Frozen',
            69 => 'Customer Insolvent / Insane',
            70 => 'Customer to refer to the Branch',
            95 => 'INITIAL REJECT',
            98 => 'Pending' // manually added
        ];

        $payment_date = '2019-04-08';
        $totalAmountReceived = 0;
        $paymentReconcileData = [];

        unset($collections[0]);

        foreach ($collections as $item) {

            $status = ($item[13] == 1) ? 'success' : (($item[13] == 3) ? 'pending' : (($item[14] == 95) ? 'initial_reject' : 'rejected'));
            if($status == 'success') {
                $application_id = $this->getApplicationID($item[22], $item[19]);
                $execution_status['status'] = $status;

                $reason = isset($reasons[$item[14]]) ? $reasons[$item[14]] : '';
                $paymentReconcileData[] = [
                    'mode' => 7,
                    'paid_by' => '',
                    'application_id' => $application_id,
                    'bank_id' => 1,
                    'umrn' => $item[22],
                    'collected_by' => 107,
                    'trasanction_id' => $item[19],
                    'wallet_name' => '',
                    'payment_date' => $payment_date,
                    'payment_reconciliation_date' => $payment_date,
                    'amount' => $item[10],
                    'status' => $status,
                    'reason' => $reason,
                    'trasanction_acknowledgement' => '',
                    'online_payment_id' => ''
                ];

                $totalAmountReceived += $item[10];
            }
        }

        return 4;
        //Settlement::import(['data' => $paymentReconcileData, 'settled_amount' => $totalAmountReceived, 'payment_date' => $payment_date, 'reconciliation_date' => $payment_date, 'type' => 'nach']);
    }

    public function getApplicationID($umrn, $reference_id = null){
        if ($reference_id) {
            $ProFormaInvoice = ProFormaInvoice::where('loan_application_no', $reference_id)->get(['id', 'loan_application_no','application_id']);
        }
        if (isset($ProFormaInvoice) && $ProFormaInvoice && count($ProFormaInvoice) == 1){
            $mandate = Mandate::where('consumer_application_id', $ProFormaInvoice[0]->application_id)->where('umrn',$umrn)->first();
        }else{
            $mandate = Mandate::where('umrn',$umrn)->first();
        }

        return isset($mandate->consumer_application_id) ? $mandate->consumer_application_id : 0;
    }

    public function lotuspayTransactionIdsUpdate($collections){

        unset($collections[0]);


        $no = [];
        foreach ($collections as $collection){
            $payment = PaymentTransaction::where('application_id', $collection[5])->where('umrn', $collection[4])->where('amount', $collection[0])->where('payment_date', $collection[1])->first(['id']);
            if (isset($payment->id)){
                PaymentTransaction::where('id', $payment->id)->update(['trasanction_id' => $collection[3]]);
            }else{
                $no[] = $collection[5];
            }
        }

        return $no;
    }

    public function uploadCompanyData($collections){

        $lists = Company::get();
        $data = [];
        foreach ($lists as $list){
            $e = $list->company;
            $data[strtolower($list->company)] = $list->id;
        }

        $response = [];
        $response1 = [];
        $companies = [];
        $p = [];
        foreach ($collections as $collection){
            if (!in_array(trim(strtolower($collection[0])), $companies)) {
                if (isset($data[trim(strtolower($collection[0]))])) {
                    $category = strpos(trim($collection[2]), 'CAT') !== false ? strtoupper(trim($collection[2])) : trim($collection[2]);
                    $response1[] = ['company' => trim($collection[0]), 'category' => $category, 'id' => $data[trim(strtolower($collection[0]))]];
                    $companies[] = trim(strtolower($collection[0]));

                } else {
                    $category = strpos(trim($collection[2]), 'CAT') !== false ? strtoupper(trim($collection[2])) : trim($collection[2]);
                    $response[] = ['company' => trim($collection[0]), 'category' => $category];
                    $companies[] = trim(strtolower($collection[0]));
                }
            }else{
                $p[] = trim(strtolower($collection[0]));
            }
        }

        $t = true;
        $i = 0;
        $c = 1;
        while ($t) {
            $c_data = array_slice($response, $i, 5000);
            if($c_data) {

                Company::insert($c_data);

                $i = 5000 * $c;
                $c++;
            }else{
                $t = false;
            }
        }


        $t = true;
        $i = 0;
        $c = 1;
        while ($t) {
            $c_data = array_slice($response1, $i, 5000);
            if($c_data) {
                foreach ($c_data as $item) {
                    Company::where('id', $item['id'])->update(['company' => $item['company'], 'category' => $item['category']]);
                }

                $i = 5000 * $c;
                $c++;
            }else{
                $t = false;
            }
        }
    }

    public function tallyRepaymentUpload($collections){

        unset($collections[0]);
        $paymentsData = [];
        $narration = '';
        $type = 'RAZORPAY';
        $key = 0;
        $i = 0;
        $final_amount = 0;
        foreach ($collections as $item) {
            $final_amount += $item[9];
        }
        foreach ($collections as $item) {

            $date = $item[0];
            $payment_id = $item[1];
            $invoice_id = $item[2];
            $email = $item[3];
            $phone = $item[4];
            $name = $item[5];
            $application_id = $item[6];
            $amount = $item[7];
            $fee = $item[8];
            $total_amount = $item[9];


            $consumerApplication = ConsumerApplication::where('id', $application_id)->where('lender_id', 2)->first(['id', 'name', 'lender_id']);
            if(isset($consumerApplication->id) && $total_amount) {

                $customerLedger = $consumerApplication->name . '-' . $consumerApplication->id;

                $narration .= !$i ? $customerLedger : ', '.$customerLedger;

                $voucher = (object)[
                    'id' => $consumerApplication->id,
                    'ledger_id' => $consumerApplication->id,
                    'name' => $consumerApplication->name,
                    'narration' => $narration,
                    'date' => date('Ymd', strtotime($date)),
                    'lender_id' => $consumerApplication->lender_id
                ];

                if (!$key) {
                    TallyListener::setCreditors('EMI CREDIT THROUGH '.$type, $final_amount);
                }

                TallyListener::setDebtors($customerLedger, $total_amount, ['voucher' => $voucher]);

                $paymentsData = array_merge($paymentsData, TallyListener::$creditorAndDebtors);
                TallyListener::$creditorAndDebtors = [];
                $i++;
            }
            $key++;
        }

        if($paymentsData) {
            $request = new Request(['subvention' => $paymentsData, 'voucher' => $voucher]);
            (new TallyListener($request))->subventionEntry();
        }
    }

    public function merchantUserCreation($collections){


        unset($collections[0]);

        $date = Carbon::now()->toDateTimeString();
        $query = '';
        foreach ($collections as $item) {
                $query .= "INSERT INTO users (name, email, mobile_number, merchant_id, user_type, created_at) VALUES('".$item[0]."', '".$item[1]."', '".$item[2]."', 4646, 2, '".$date."');"."<br/>";
        }

        return $query;
    }

    public function tallySubventionUpload($collections){


        /*$d = json_decode('[{"ledgerName":"Subvention (Advance)","amount":333,"sundry":"DR","voucher":{"id":53770,"ledger_id":53770,"name":"BHOSALE VIJAY KUMAR D","narration":", BHOSALE VIJAY KUMAR D-53770","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":333,"sundry":"CR","voucher":{"id":53770,"ledger_id":53770,"name":"BHOSALE VIJAY KUMAR D","narration":", BHOSALE VIJAY KUMAR D-53770","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":153,"sundry":"DR","voucher":{"id":53779,"ledger_id":53779,"name":"AMALA SUTHESH","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":153,"sundry":"CR","voucher":{"id":53779,"ledger_id":53779,"name":"AMALA SUTHESH","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":153,"sundry":"DR","voucher":{"id":53885,"ledger_id":53885,"name":"Archana B R","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":153,"sundry":"CR","voucher":{"id":53885,"ledger_id":53885,"name":"Archana B R","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":148,"sundry":"DR","voucher":{"id":54017,"ledger_id":54017,"name":"Apoorva bhimashankar kadlag","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":148,"sundry":"CR","voucher":{"id":54017,"ledger_id":54017,"name":"Apoorva bhimashankar kadlag","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":442,"sundry":"DR","voucher":{"id":54040,"ledger_id":54040,"name":"NOWSATH AHMED ALLAPITCHAI","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":442,"sundry":"CR","voucher":{"id":54040,"ledger_id":54040,"name":"NOWSATH AHMED ALLAPITCHAI","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":681,"sundry":"DR","voucher":{"id":54274,"ledger_id":54274,"name":"SUNNY B PATIL","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":681,"sundry":"CR","voucher":{"id":54274,"ledger_id":54274,"name":"SUNNY B PATIL","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":141,"sundry":"DR","voucher":{"id":60715,"ledger_id":60715,"name":"ABHISEKH KUNDU","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":141,"sundry":"CR","voucher":{"id":60715,"ledger_id":60715,"name":"ABHISEKH KUNDU","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":580,"sundry":"DR","voucher":{"id":64051,"ledger_id":64051,"name":"SACHIN B KULKARNI","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":580,"sundry":"CR","voucher":{"id":64051,"ledger_id":64051,"name":"SACHIN B KULKARNI","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":760,"sundry":"DR","voucher":{"id":64847,"ledger_id":64847,"name":"PRATIKSHA GANPAT BHOSALE","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":760,"sundry":"CR","voucher":{"id":64847,"ledger_id":64847,"name":"PRATIKSHA GANPAT BHOSALE","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":264,"sundry":"DR","voucher":{"id":73845,"ledger_id":73845,"name":"MOHAMED AYUB","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845","date":"20220418","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":264,"sundry":"CR","voucher":{"id":73845,"ledger_id":73845,"name":"MOHAMED AYUB","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845","date":"20220418","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":195,"sundry":"DR","voucher":{"id":74095,"ledger_id":74095,"name":"D Murugan","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":195,"sundry":"CR","voucher":{"id":74095,"ledger_id":74095,"name":"D Murugan","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":508,"sundry":"DR","voucher":{"id":75618,"ledger_id":75618,"name":"Purohit Ghivar Singh","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":508,"sundry":"CR","voucher":{"id":75618,"ledger_id":75618,"name":"Purohit Ghivar Singh","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":599,"sundry":"DR","voucher":{"id":76136,"ledger_id":76136,"name":"RAJDEEP SINGH RATHORE","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":599,"sundry":"CR","voucher":{"id":76136,"ledger_id":76136,"name":"RAJDEEP SINGH RATHORE","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":195,"sundry":"DR","voucher":{"id":76369,"ledger_id":76369,"name":"JONITA BALID","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":195,"sundry":"CR","voucher":{"id":76369,"ledger_id":76369,"name":"JONITA BALID","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":274,"sundry":"DR","voucher":{"id":76822,"ledger_id":76822,"name":"Sahil Sharma","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822","date":"20220418","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":274,"sundry":"CR","voucher":{"id":76822,"ledger_id":76822,"name":"Sahil Sharma","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822","date":"20220418","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":148,"sundry":"DR","voucher":{"id":77517,"ledger_id":77517,"name":"KAZI habib hassan","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822, KAZI habib hassan-77517","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":148,"sundry":"CR","voucher":{"id":77517,"ledger_id":77517,"name":"KAZI habib hassan","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822, KAZI habib hassan-77517","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":342,"sundry":"DR","voucher":{"id":77776,"ledger_id":77776,"name":"SAKIL KHAN","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822, KAZI habib hassan-77517, SAKIL KHAN-77776","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":342,"sundry":"CR","voucher":{"id":77776,"ledger_id":77776,"name":"SAKIL KHAN","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822, KAZI habib hassan-77517, SAKIL KHAN-77776","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Advance)","amount":421,"sundry":"DR","voucher":{"id":87195,"ledger_id":87195,"name":"PRAVIN VILAS SHINDE","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822, KAZI habib hassan-77517, SAKIL KHAN-77776, PRAVIN VILAS SHINDE-87195","date":"20220405","lender_id":2}},{"ledgerName":"Subvention (Income)","amount":421,"sundry":"CR","voucher":{"id":87195,"ledger_id":87195,"name":"PRAVIN VILAS SHINDE","narration":", BHOSALE VIJAY KUMAR D-53770, AMALA SUTHESH-53779, Archana B R-53885, Apoorva bhimashankar kadlag-54017, NOWSATH AHMED ALLAPITCHAI-54040, SUNNY B PATIL-54274, ABHISEKH KUNDU-60715, SACHIN B KULKARNI-64051, PRATIKSHA GANPAT BHOSALE-64847, MOHAMED AYUB-73845, D Murugan-74095, Purohit Ghivar Singh-75618, RAJDEEP SINGH RATHORE-76136, JONITA BALID-76369, Sahil Sharma-76822, KAZI habib hassan-77517, SAKIL KHAN-77776, PRAVIN VILAS SHINDE-87195","date":"20220405","lender_id":2}}]');

        $narration = [];
        foreach ($d as $item){
            if ($item->ledgerName != 'Interest (Received)') {
                if($item->ledgerName != 'Subvention (Advance)'){
                    if($item->ledgerName != 'Subvention (Income)') {
                        $narration[] = $item->ledgerName;
                    }
                }
            }
        }

        $voucher = (object)[
            'id' => 53770,
            'ledger_id' => 53770,
            'name' => 'BHOSALE VIJAY KUMAR D',
            'narration' => implode(',',$narration),
            'date' => date('Ymd', strtotime($collections[1][1])),
            'lender_id' => 2
        ];

        $request = new Request(['subvention' => $d, 'voucher' => $voucher]);
        (new TallyListener($request))->subventionEntry();
        return 4;*/

        unset($collections[0]);
        $paymentsData = [];
        $i = 0;
        $narration = '';
        $k = 0;
        foreach ($collections as $item) {

            $date = $item[1];
            $consumerApplication = ConsumerApplication::where('id', $item[0])->where('lender_id', 2)->first(['id', 'name', 'lender_id']);
            if(isset($consumerApplication->id) && ($item[2] || $item[3])) {

                $customerLedger = $consumerApplication->name . '-' . $consumerApplication->id;

                $narration .= !$i ? $customerLedger : ', '.$customerLedger;

                $voucher = (object)[
                    'id' => $consumerApplication->id,
                    'ledger_id' => $consumerApplication->id,
                    'name' => $consumerApplication->name,
                    'narration' => $narration,
                    'date' => date('Ymd', strtotime($date)),
                    'lender_id' => $consumerApplication->lender_id
                ];

                if ($item[2]) {
                    TallyListener::setCreditors('Interest (Received)', round($item[2]));
                    TallyListener::setDebtors($customerLedger, round($item[2]), ['voucher' => $voucher]);
                }
                if ($item[3] > 0) {
                    TallyListener::setDebtors('Subvention (Advance)', round($item[3]), ['voucher' => $voucher]);
                    TallyListener::setCreditors('Subvention (Income)', round($item[3]), ['voucher' => $voucher]);
                }

                $paymentsData = array_merge($paymentsData, TallyListener::$creditorAndDebtors);
                TallyListener::$creditorAndDebtors = [];
                $i++;

                if (count($paymentsData) >= 50 && count($paymentsData) <= 90){
                    $request = new Request(['subvention' => $paymentsData, 'voucher' => $voucher]);
                    (new TallyListener($request))->subventionEntry();
                    $paymentsData = [];
                    $narration = '';
                }else if(!isset($collections[$k + 1])){
                    $request = new Request(['subvention' => $paymentsData, 'voucher' => $voucher]);
                    (new TallyListener($request))->subventionEntry();
                    $paymentsData = [];
                    $narration = '';
                }else if (count($paymentsData) < 50 && count($paymentsData) > 70){
                    $d = 4;
                }

                $k++;
            }
        }

        if(isset($paymentsData)) {
            $request = new Request(['subvention' => $paymentsData, 'voucher' => $voucher]);
            (new TallyListener($request))->subventionEntry();
        }
    }

    public function bounce_panel_tally_entries($collections)
    {


        unset($collections[0]);

        foreach ($collections as $item) {
            if ($item[2] || $item[3]) {

                if ($item[0]) {
                    $consumerApplication = ConsumerApplication::with('merchant', 'emi', 'lender.bank_details')->where('id', $item[0])->first();

                    if (isset($consumerApplication->id)) {

                        $bounce_charges = $item[2];
                        $penal_interest = $item[3];
                        $bounce_gst = 0;
                        $penal_gst = 0;
                        $gstAttributes = ['IGST' => 0, 'CGST' => 0, 'SGST' => 0];
                        $delay_penalty = 0;

                        $customerLedger = $consumerApplication->name . '-' . $consumerApplication->id;

                        $date = $item[1];
                        //$narration .= !$i ? $customerLedger : ', ' . $customerLedger;

                        $voucher = (object)[
                            'id' => $consumerApplication->id,
                            'ledger_id' => $consumerApplication->id,
                            'name' => $consumerApplication->name,
                            'narration' => $customerLedger,
                            'date' => date('Ymd', strtotime($date)),
                            'lender_id' => $consumerApplication->lender_id
                        ];


                        if ($bounce_charges > 0) {
                            $bounce_charges = round($bounce_charges);
                            $bounce_charge_gst = $this->getGSTDetails($bounce_charges, $consumerApplication);
                            if ($bounce_charge_gst) {
                                foreach ($bounce_charge_gst as $gst) {
                                    $bounce_gst += round($gst['gst_amount']);
                                    $gstAttributes[$gst['taxes']] += round($gst['gst_amount']);
                                }
                            }
                        }
                        if ($penal_interest > 0) {
                            $delay_penalty = round($penal_interest);
                            $delay_penalty_gst = $this->getGSTDetails($delay_penalty, $consumerApplication);
                            if ($delay_penalty_gst) {
                                foreach ($delay_penalty_gst as $gst) {
                                    //$penal_gst += round($gst['gst_amount']);
                                    //$gstAttributes[$gst['taxes']] += round($gst['gst_amount']);
                                }
                            }
                        }

                        foreach ($gstAttributes as $tax_type => $amount) {
                            if ($amount) {
                                TallyListener::setCreditors($tax_type, $amount);
                            }
                        }

                        if (($bounce_charges - $bounce_gst) > 0) {
                            TallyListener::setCreditors('Bank Charges', round(($bounce_charges - $bounce_gst)));
                        }
                        if (($delay_penalty - $penal_gst) > 0) {
                            TallyListener::setCreditors('Interest (Late Payment)', round(($delay_penalty - $penal_gst)));
                        }

                        if (($bounce_charges + $delay_penalty) > 0) {
                            TallyListener::setDebtors($customerLedger, ($bounce_charges + $delay_penalty));
                        }

                        $paymentsData = TallyListener::$creditorAndDebtors;
                        TallyListener::$creditorAndDebtors = [];

                        $request = new Request(['subvention' => $paymentsData, 'voucher' => $voucher]);
                        (new TallyListener($request))->subventionEntry();
                    }
                }
            }
        }
    }

    public function getGSTDetails($amount, $consumerApplication, $type = 1){
        return (new ConsumerDocument(new Request()))->getGSTDetails(
            [
                'amount' => $amount,
                'state' => (isset($consumerApplication->permanent_address_state) && !empty($consumerApplication->permanent_address_state)) ? $consumerApplication->permanent_address_state : (isset($consumerApplication->current_address_state) && !empty($consumerApplication->current_address_state) ? $consumerApplication->current_address_state : '')
            ], $type
        );
    }
}