<?php

namespace App\Http\Controllers\Admin;
use App\ABFL\Services\Services;
use App\Avanse\Processes\AvanseProcesses;
use App\BankValidation;
use App\CallLogs;
use App\CampaignsJobProvider;
use App\Centers;
use App\Comments;
use App\CommunicationMapping;
use App\CommunicationService;
use App\ConsumerApplication;
use App\ApplicationEMICalculation;
use App\ConsumerApplicationElement;
use App\CoronaHotspot;
use App\CustomGrades;
use App\CustomGradesMain;
use App\DigitalSignDetails;
use App\DisbursementLogs;
use App\DisbursementOffersData;
use App\Exotel\ExotelServices;
use App\Exports\Exporter;
use App\Exports\ExporterMultipleSheet;
use App\FinBitData;
use App\Grades;
use App\GradesV1;
use App\DecisionTable;
use App\Http\Controllers\Admin\Dashboard;
use App\Http\Controllers\Controller;
use App\Http\Controllers\Couriers\CouriersController;
use App\Http\Controllers\MerchantApiController;
use App\Http\Requests\AddMerchantLocationRequest;
use App\Http\Requests\AddMerchantRequest;
use App\Http\Requests\EditMerchantLocationRequest;
use App\Http\Requests\EditMerchantRequest;
use App\Http\Requests\MerchantOffersRequest;
use App\JobProvider;
use App\KarzaAPI;
use App\Leads;
use App\LendBox;
use App\LendBoxLSP;
use App\Lenders;
use \App\Merchant;
use App\MerchantBank;
use App\MerchantBankCenter;
use App\MerchantBankLogs;
use App\MerchantCategory;
use App\MerchantCategoryScripts;
use App\Http\Controllers\Grade\GradeController;
use App\MerchantCategoryScriptsLogs;
use App\MerchantFiles;
use App\MerchantGstFillingData;
use \App\MerchantLocation;
use App\MerchantLogs;
use App\MerchantModule;
use App\MerchantOffers;
use App\MerchantOffersLock;
use App\MerchantRelationshipAnalytics;
use App\MerchantWhitelistLender;
use App\MerchantCategoryCommonMail;
use App\Pincode;
use App\ProductOffers;
use App\Products;
use App\QRCode;
use App\Slack;
use App\StateModel;
use App\StatusLogs;
use App\Templates;
use App\TradeAdvanceDisbursement;
use App\TradeAdvanceLogs;
use App\User;
use App\UserEmails;
use App\UserTypes;
use Bugsnag\BugsnagLaravel\Facades\Bugsnag;
use Carbon\Carbon;
use Illuminate\Http\RedirectResponse;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Input;
use Illuminate\Support\Facades\Mail;
use Illuminate\Support\Facades\Session;
use Hashids\Hashids;
use Illuminate\Support\Facades\App;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Facades\URL;
use Maatwebsite\Excel\Facades\Excel;
use App\Equifax;
use App\KMGP2P;
use Illuminate\Support\Facades\Validator;
use MathPHP\Finance;
use App\Logging\CloudWatchLoggerFactory;
use App\LoanDecisionApprovedAmount;

class MerchantController extends Controller
{
    public static $interest_types  = [1 => 'Flat Balance Amount (Annualized)', 2 => 'Reducing', 3 => 'Flat Fixed Amount (Annualized)',4 => 'Flat (Non-Annualized)'];
    public static $subvention_types = [1 => 'Full', 2 => 'Partial', 3 => 'Zero'];
    private $internal = false;
    private $cwFactory;

    public function __construct(){
        $this->cwFactory = new CloudWatchLoggerFactory();
    }

    public function index(Request $request)
    {
        if (_env('MERCHANT_MIGRATION_ACTIVE')) {
            if (!$request->has('token')) {
                $user  = Auth::user();
                $email = $user->email ?? 'admin@creditfair.in';
                $role  = $user->user_type;
                $token = generateCreditFairToken($email, $role);
                $baseUrl = _env('MIGRATED_ADMIN_APP_URL', 'https://admin.creditfair.in');

                $data['iframeUrl'] = $baseUrl . '/#/admin/merchants?token=' . urlencode($token); 
                    // 3. Keep the sidebar active state
                $data['sidebar']   = 'merchant';
                
                return view('admin.merchant.index_migrated', $data);
                //return redirect()->route('admin.creditfair.in/#/admin/merchants', ['token' => $token]);
            }
        }

        $data['sidebar']   = 'merchant';
        $data['merchants'] = Merchant::with('digital_sign','bank_details','offer','module','active_offers','moduleAll','merchant_category','merchant_files')->where('is_guest_active', 1)->orderBy('merchant_name','asc')->paginate(20);        $data['categories'] = MerchantCategory::all();
        $i = 0;
        $category_name = array();
        $signed = array();

        foreach($data['merchants'] as $merchant){
            if($merchant->slug == "") {
                $hashids = new Hashids('Creditfair by CS', 20);
                $merchant_hash_id = $hashids->encode($merchant['id']);
                $merchant_url = asset("application?txn=" . $merchant_hash_id);
                $checkout_url = $merchant_hash_id;
            }else{
                $checkout_url = $merchant->slug;
                $merchant_url = asset("application/".$merchant->slug);
            }
            $data['merchants'][$i]['website_url'] = $merchant_url;
            $data['merchants'][$i]['emi_check_out_url'] = _env('EMI_CHECKOUT_BASE_URL').$checkout_url;
            $data['merchants'][$i]['pendingDoc'] = $this->getHelperTxt($merchant);
            if($merchant->gst_is_any_delay == 0) {
                array_merge($data['merchants'][$i]['pendingDoc'], array('GST Delay' => false));
            }
            if($merchant->gst_is_defaulter == 0) {
                array_merge($data['merchants'][$i]['pendingDoc'], array('GST Default' => false));
            }

            if($merchant->months_gst_filled < 1) {
                array_merge($data['merchants'][$i]['pendingDoc'], array('No GST Filled' => false));
            }
            //$filename = $merchant->id.'/'.$merchant->id.'_response_merchant.json';
            $filename = 'merchant/'.$merchant->id.'/'.$merchant->id.'_response_merchant.json';
            $is_exists = (int) Equifax::isFileExist($filename);
            $data['merchants'][$i]['is_equifax'] = $is_exists;
            $data['merchants'][$i]['call_list'] = $this->getCallerLists($merchant);
            if(isset($merchant->merchant_category))
            {
                $category_name[$merchant->category_id]['name'] = $merchant->merchant_category->name;
            }else{
                $category_name[$merchant->category_id]['name'] = null;
            }
            if(isset($merchant->merchant_files))
            {
                $signed[$merchant->id]['id'] = $merchant->merchant_files->id;
            }else{
                $signed[$merchant->id]['id'] = null;
            }
            $i++;
        }

        $data['kw_cappings'] = DB::table('kw_installation_records')->pluck('max_approved_value', 'KW');

        $data['signed_agreement'] = $signed;
        $data['category_names'] = $category_name;
        $data['category_mail_list'] = MerchantCategoryCommonMail::whereNull('deleted_at')->get(['id','email']);

        $data['search_text'] = '';
        $data['isMerchantSection'] = true;
        return view('admin.merchant.index', $data);
    }

    public function updateKWValues(Request $request)
    {
        $maxApprovedValues = $request->input('max_approved_values');
        $userId = $request->input('user_id');

        foreach ($maxApprovedValues as $kw => $value) {
            // Assuming 'KW' is an identifier in your table
            DB::table('kw_installation_records')
                ->where('KW', $kw)
                ->update([
                    'max_approved_value' => $value,
                    'updated_by' => $userId,
                    'updated_at' => now()
                ]);
        }

        return response()->json(['status' => 'OK']);
    }


    public function updateMerchantCategoryMail(Request $r){

        $validation = Validator::make($r->all(),[
            "categorymailid" => "string|required",
            "newcategorymail" => "nullable|string",
            "categorylist" => "array|required",
            "user_id" => "string|required",
        ],[
            "string" => ":attribute is not string",
            "required" => ":attribute is required",
        ]);
        if($validation->messages()->all()){
            $data = [
                "status" => "FAIL",
                "message" => $validation->messages()->all(),
                "error" => [
                    "code" => "400",
                    "message" => "Bad Request"
                ]
            ];
            return response()->json($data);
        }

        $c_list_id = $r->get('categorymailid');
        $list = is_array($r->get('categorylist')) ? implode(',',$r->get('categorylist')) : $r->get('categorylist');
        $eml = $r->get('newcategorymail');
        $u_id = $r->get('user_id');

        try{
            $mccmexist = MerchantCategoryCommonMail::where('id',$c_list_id)->first();

            if(isset($mccmexist->id)){
                $mccmexist->merchant_category_list = $list;
                $mccmexist->updated_by = $u_id;
                $mccmexist->save();

                return response()->json(['status'=>'OK','message'=>'Existing Data Saved Successfully']);
            }else{
                MerchantCategoryCommonMail::insert([
                    'merchant_category_list' => $list,
                    'email' => $eml,
                    'created_by' => $u_id,
                    'created_at' => Carbon::now()->toDateTimeString()
                ]);

                return response()->json(['status'=>'OK','message'=>'New Data Saved Successfully']);
            }
        }catch(\Exception $e){
            return response()->json(['error'=>$e->getMessage(),'line'=>$e->getLine(),'trace'=>$e->getTraceAsString(),'status'=>'FAIL','message'=>'Something went wrong']);
        }

    }

    public function downloadCategorySpecifCreative($id){
        return false;
        $merchant = Merchant::find($id);
        $qr_path = QRCode::getQRCodeFile($id);

        $creative = [
            4 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            7 => ['creative' => ['path' => 'ffae994f-ce4a-40d6-b14d-79746dc622c6.jpg']],
            14 => ['creative' => ['path' => 'fdd8b4f3-edcd-45a3-af24-8c5a397ac1a2.jpg']],
            16 => ['creative' => ['path' => '6843e260-e7f7-4dab-a084-caa04ec844e1.jpg']],
            19 => ['creative' => ['path' => '6843e260-e7f7-4dab-a084-caa04ec844e1.jpg']],
            20 => ['creative' => ['path' => '6843e260-e7f7-4dab-a084-caa04ec844e1.jpg']],
            21 => ['creative' => ['path' => '6843e260-e7f7-4dab-a084-caa04ec844e1.jpg']],
            22 => ['creative' => ['path' => 'fdd8b4f3-edcd-45a3-af24-8c5a397ac1a2.jpg']],
            23 => ['creative' => ['path' => 'fdd8b4f3-edcd-45a3-af24-8c5a397ac1a2.jpg']],
            24 => ['creative' => ['path' => 'fdd8b4f3-edcd-45a3-af24-8c5a397ac1a2.jpg']],
            25 => ['creative' => ['path' => '6843e260-e7f7-4dab-a084-caa04ec844e1.jpg']],
            33 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            34 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            35 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            36 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            37 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            38 => ['creative' => ['path' => '2e8012a2-0c06-47aa-a8eb-1e04a48b5443.jpg']],
            40 => ['creative' => ['path' => 'fdd8b4f3-edcd-45a3-af24-8c5a397ac1a2.jpg']],
        ];

        $creativeData = isset($creative[$merchant->category_id]) ? $creative[$merchant->category_id] : '';

        if(!$creativeData && $merchant->category_id > 40){
            $creativeData = $creative[7];
        }

        if (!$creativeData) {
            \Session::flash('error', 'Creative Won\'t be applied for this category');
            return redirect()->back();
        }

        //return view('admin.merchant.creative', ['merchant' => $merchant, 'qr_path' => $qr_path, 'creativeData' => $creativeData]);
        $pdf = App::make('snappy.pdf.wrapper');
        $creativePDF = $pdf->loadView('admin.merchant.creative', ['merchant' => $merchant, 'qr_path' => $qr_path, 'creativeData' => $creativeData])->setOption('encoding', 'UTF-8')->setPaper('a4')->setOrientation('portrait')
            ->setOption('margin-bottom', 0)
            ->setOption('margin-top', 0)
            ->setOption('margin-left', 0)
            ->setOption('margin-right', 0);

        $document = (new ConsumerDocument(new Request()));
        $document->isMerchantRequest = 1;
        $document->uploadToS3('Bar Code Creative', $id, $creativePDF->output());
        if (Input::has('send') || $this->internal){
            $this->sendCreativeToMerchant($merchant, $creativePDF);
            \Session::flash('success', 'Creative sent to merchant');
            return redirect()->back();
        }

        return $creativePDF->download($merchant->merchant_name.' Creative.pdf');
    }

    public function sendCreativeToMerchant($merchant, $creativePDF){

        $message = "Dear ". $merchant['owner_name'].",<br/>

We are glad to inform you that now your customers can themselves check the eligibility with Credit Fair by following the small 5 minutes process mentioned in the below video:<br/><br/>

Link fo Video: 

Please find your unique bar code attached to this mail.<br/><br/>

Reach us on bd@creditfair.in in case of any issues<br/><br/>

Regards";

        if($merchant->email) {
            $emails = explode(',', $merchant->email);
            Mail::send('emails.reminders.basic_reminder',
                ['body' => $message]
                , function ($message) use ($creativePDF, $emails) {
                    $message->to($emails)->subject('Bar Code Creative');
                    $message->cc(['bd@creditfair.in']);
                    $message->attachData($creativePDF->output(), 'Bar Code Creative.pdf');
            });
        }
    }

    public function getCallerLists($merchant, $user_type = '', $isApp = false)
    {
        $lists = [];


        $discardNumber = (Auth::user()) ? Auth::user()->mobile_number : 0;

        $mobileNumber = [];

        if (isset($merchant->contact_number)) {
            if ($merchant->contact_number && $merchant->contact_number != $discardNumber && !in_array($merchant->contact_number, $mobileNumber)) {
                $lists[] = ['name' => $merchant->merchant_name, 'phone' => $merchant->contact_number, 'type' => 'Merchant'];
                array_push($mobileNumber, $merchant->contact_number);
            }
        }
        if (isset($merchant->rcp_mobile) && $merchant->rcp_mobile != $discardNumber) {
            if ($merchant->rcp_mobile && !in_array($merchant->rcp_mobile, $mobileNumber)) {
                $lists[] = ['name' => $merchant->rcp, 'phone' => $merchant->rcp_mobile, 'type' => 'Regular Contact Person'];
                array_push($mobileNumber, $merchant->rcp_mobile);
            }
        }

        if (isset($merchant->sales_head_mobile) && $merchant->sales_head_mobile != $discardNumber) {
            if ($merchant->sales_head_mobile && !in_array($merchant->sales_head_mobile, $mobileNumber)) {
                $lists[] = ['name' => $merchant->sales_head, 'phone' => $merchant->sales_head_mobile, 'type' => 'Sales Head'];
                array_push($mobileNumber, $merchant->sales_head_mobile);
            }
        }

        if (isset($merchant->finance_head_mobile) && $merchant->finance_head_mobile != $discardNumber) {
            if ($merchant->finance_head_mobile && !in_array($merchant->finance_head_mobile, $mobileNumber)) {
                $lists[] = ['name' => $merchant->finance_head, 'phone' => $merchant->finance_head_mobile, 'type' => 'Finance Head'];
                array_push($mobileNumber, $merchant->finance_head_mobile);
            }
        }

        if ($isApp) {
            return $lists;
        }

        return ($lists) ? '<ul>' . implode('', array_map(function ($item) use ($merchant) {
                return "<li class='exotel-call' data-type='application_id' data-user-type=" . $item['type'] . "  data-id=" . $merchant->id . " data-contact=" . $item['phone'] . ">" . $item['name'] . ", " . $item['type'] . "</li>";
            }, $lists)) . '</ul>' : '';
    }

    public function getHelperTxt($merchant){
        $agreement_signed = ['Pan Card' => 'pan_card','Bureau Report' => 'equifax_report','Business Reg' => 'gst_registration'];
        $data = $this->getDocumentPendingData($agreement_signed, $merchant->id);
        $status = true;
        foreach ($data as $item){
            if ($item){
                $status = false;
            }
        }

        if ($status){
            return $data;
        }else if(!$merchant->moduleAll || ($merchant->moduleAll && $merchant->moduleAll->count() == 0)){
            return ['Module' => false];
        }else if(!$merchant->digital_sign || ($merchant->digital_sign && $merchant->digital_sign->isagreementsigned != 1)){
            return ['Signed Agreement' => false];
        }else if(!$merchant->offer || ($merchant->offer && $merchant->offer->count() == 0)){
            return ['Offers' => false];
        }else if($merchant->offer && $this->checkOffer($merchant)){
            return ['Maker Checker' => false];
        }else if(!$merchant->bank_details){
            return ['Bank Details' => false];
        }else if($merchant->status == 'inactive'){
            return ['Status' => false];
        }
        return array();
    }

    public function checkOffer($merchant){
        foreach ($merchant->offer as $offer){
            if ($offer->status == 0){
                return true;
            }
        }
        return false;
    }

    public function getDocumentPendingData($documents, $id){
        $response = [];
        $merchantFiles = MerchantFiles::where('merchant_id',$id)->whereNull('type')->whereIn('file_type',array_values($documents))->get(['file_type']);

        $files = [];
        foreach ($merchantFiles as $file){
            $files[] = $file->file_type;
        }

        foreach ($documents as $key=>$document){
            switch ($document){
                case 'pan_card':
                    $response[$key] = in_array($document, $files) ? true : false;
                    break;
                case 'equifax_report':
                    $response[$key] = in_array($document, $files) ? true : false;
                    break;
                case 'gst_registration':
                    $response[$key] = in_array($document, $files) ? true : false;
                    break;
                case 'signed_agreement':
                    $response[$key] = in_array($document, $files) ? true : false;
                    break;
            }
        }

        return $response;
    }

    public function getAddMerchant()
    {
        $data['sidebar'] = 'merchant';
        $data['categories'] = MerchantCategory::get(['name','id']);
        $data['grades'] = GradesV1::all();
        $data['state_list'] = StateModel::get(['name','code','id']);
        $data['users']  = User::where('user_type','>=',5)->whereNull('deleted_at')->get(['id','name']);

        return view('admin.merchant.add', $data);
    }

    public function getResults(Request $request){
        $dashboard_filter = ($request->has('filter') &&  !empty($request->get('filter'))) ?  $request->get('filter') : "";
        if($dashboard_filter != ""){
            $dashboard_filter_array = json_decode(base64_decode($dashboard_filter));

            if(isset($dashboard_filter_array[0])){
                $merchant_id_data = ConsumerApplication::whereIn('id', $dashboard_filter_array)->where('merchant_id','!=',21)->distinct()->get(['merchant_id']);
                $merchant_id = array();
                foreach ($merchant_id_data as $id){
                    array_push($merchant_id,$id->merchant_id);
                }
            }
        }
        $data['sidebar']   = 'merchant';
        $keyword = Input::has('search_text') ? Input::get('search_text') : '';

        $merchants = Merchant::with('digital_sign','bank_details','offer','module','active_offers','moduleAll')->where(function ($query) use ($keyword) {
            $query->where(DB::raw("REGEXP_REPLACE(merchant_name, ' {2,}', ' ')"), 'LIKE', '%' . strtolower($keyword) . '%')
                ->orWhere('email', 'like', '%' . $keyword . '%')
                ->orWhere('contact_number', 'like', '%' . $keyword . '%');
        });

        $data['search_text'] = $keyword;
        $data['filters'] = $dashboard_filter;
        if(Input::has('category_id') && !empty(Input::get('category_id'))) {
            $merchants = $merchants->where('category_id',Input::get('category_id'));
            $data['category_id'] = Input::get('category_id');
        }
        if(isset($merchant_id[0])){
            $merchants = $merchants->whereIn('id', $merchant_id);
        }
        if($request->has('is_guest_login')){
            $merchants = $merchants->where('is_guest_active', 0);
        }else{
            $merchants = $merchants->where('is_guest_active', 1);
        }
        $merchants = $merchants->orderBy('merchant_name','asc')->paginate(20);

        $i = 0;
        $cat_ids = array();
        $merchant_ids = array();
        $cat_name = array(array());
        $signed = array(array());

        foreach($merchants as $merchant){
            if($merchant->slug == "") {
                $hashids = new Hashids('Creditfair by CS', 20);
                $merchant_hash_id = $hashids->encode($merchant['id']);
                $merchant_url = asset("application?txn=" . $merchant_hash_id);
                $checkout_url = $merchant_hash_id;
            }else{
                $checkout_url = $merchant->slug;
                $merchant_url = asset("application/".$merchant->slug);
            }
            $merchants[$i]['website_url'] = $merchant_url;
            $merchants[$i]['emi_check_out_url'] = _env('EMI_CHECKOUT_BASE_URL').$checkout_url;
            $merchants[$i]['pendingDoc'] = $this->getHelperTxt($merchant);
            if($merchant->gst_is_any_delay == 0) {
                $merchants[$i]['pendingDoc'] = array_merge($merchants[$i]['pendingDoc'], array('GST Delay' => false));
            }
            if($merchant->gst_is_defaulter == 0) {
                $merchants[$i]['pendingDoc'] = array_merge($merchants[$i]['pendingDoc'], array('GST Default' => false));
            }

            if($merchant->months_gst_filled < 1) {
                $merchants[$i]['pendingDoc'] = array_merge($merchants[$i]['pendingDoc'], array('No GST Filled' => false));
            }
            $filePath = 'merchant/'.$merchant->id.'/'.$merchant->id.'_response_merchant.json';
            $merchants[$i]['is_equifax'] = (int) Equifax::isFileExist($filePath);
            $merchants[$i]['call_list'] = $this->getCallerLists($merchant);
            $cat_ids[] = $merchant->category_id;
            $merchant_ids[] = $merchant->id;
            $i++;
        }

        $names = \App\MerchantCategory::whereIn('id',$cat_ids)->get(['name','id']);
        foreach($cat_ids as $id){

            foreach($names as $name){

                if($id == $name->id){
                    $cat_name[$id]['name'] = $name->name;
                }
            }
            if(!isset($cat_name[$id]['name'])){
                $cat_name[$id]['name'] = null;
            }
        }

        // -----------------------------------
        $signed_agreement = \App\MerchantFiles::whereIn('merchant_id',$merchant_ids)->whereNull('type')->where('file_type','signed_agreement')->get(['id','merchant_id']);

        foreach($merchant_ids as $id){
            foreach($signed_agreement as $sign_agree){
                if($id == $sign_agree->merchant_id){
                    $signed[$id]['id'] = $sign_agree->id;
                }
            }
            if(!isset($signed[$id]['id'])){
                $signed[$id]['id'] = null;
            }
        }

        $data['signed_agreement'] = $signed ;
        $data['category_names'] = $cat_name;
        $data['category_mail_list'] = MerchantCategoryCommonMail::whereNull('deleted_at')->get(['id','email']);
        $data['kw_cappings'] = DB::table('kw_installation_records')->pluck('max_approved_value', 'KW');

        $data['merchants'] = $merchants;
        $data['isSearch'] = 1;
        $data['categories'] = MerchantCategory::all();
        if($request->has('is_guest_login')){
            $data['is_guest_login'] = 1;
        }
        return view('admin.merchant.index', $data);
    }

    public function getAddPostMerchant(AddMerchantRequest $request)
    {
        if ($request->has('slug')){
            $duplicateSlug = Merchant::where('slug',$request->get('slug'))->first(['id']);
            if (isset($duplicateSlug->id)){
                \Session::flash('error', 'Can\'t add the merchant because of duplicate slug');
                return redirect()->back();
            }
        }

        $merchant = new Merchant();
        $merchant->fill($request->input());

        if($request->has('u_id')){
            $merchant->source = $request->get('source').'-'.$request->get('u_id');
        }

        if($request->has('is_collected')){
            $merchant->is_collected = 1;
        }

        if($request->has('insurance_premium')){
            $merchant->insurance_premium = 1;
        }

        if($request->has('pending_collection_report')){
            $merchant->pending_collection_report = 1;
        }

        $merchant->is_gst = 1;
        $merchant->pay_data_sharing = 1;
        $merchant->receive_application = 1;

        if($request->has('co_applicant')){
            $merchant->co_applicant = 1;
        }

        if($request->has('referred_by')){
            $merchant->referred_by = strtolower($request->get('referred_by'));
        }

        $year_in_business = $request->get('year_in_business') ? $request->get('year_in_business') : 0;
        $month_in_business = $request->get('month_in_business') ? $request->get('month_in_business') : 0;

        $merchant->year_in_business = $month_in_business.'|'.$year_in_business;

        $merchant->is_physical_verification = 1;
        $merchant->is_auto_approve = 1;
        $merchant->is_auto_doc = 1;
        $merchant->pre_approval = 1;
        $merchant->created_by = Auth::user()->id;

        $merchantslug = Merchant::where('slug',$request->get('slug'))->where('status','active')->get();
        if(isset($merchantslug[0]->id)){
            \Session::flash('error', 'Alias Already used');
            return redirect()->back()->withInput();
        }
        if($request->has('is_auto_abort')){
            $merchant->is_auto_abort = 1;
        }else{
            $merchant->is_auto_abort = 0;
        }
        if($request->has('is_auto_abort_approved')){
            $merchant->is_auto_abort_approved = 1;
        }else{
            $merchant->is_auto_abort_approved = 0;
        }
        if($request->has('guest_merchant')){
            $merchant->is_guest_active=0;
        }
        if($request->has('guest_merchant')) {
            $merchant->status = 'Active';
            $merchant->status_changed_by = Auth::user()->id;
            $user = User::where('id', $merchant->status_changed_by)->first(['name']);
            MerchantLogs::insert([
                'merchant_id' => $merchant->id,
                'user_id' => Auth::user()->id,
                'data' => 'merchant activated by '.$user->name,
                'created_at' => Carbon::now()->toDateTimeString()

            ]);
        }

//        if($request->has('hold_liability') &&  !empty($request->hold_percentage)) {
//            $merchant->liability_percent = $request->hold_percentage;
//        }
        $merchant->save();
        $newMerchantId = $merchant->id;

        if ($request->hasfile('merchant_logo')) {

            $image = $request->file('merchant_logo');
            $name  = str_slug($merchant->merchant_name) . '_' . uniqid() . '.' . $image->getClientOriginalExtension();

            /*$relativePath    = '/uploads/images/merchant';
            $destinationPath = public_path($relativePath);

            $imagePath = $relativePath . "/" . $name;
            $image->move($destinationPath, $name);*/

            $file_path = '/merchant/' . $newMerchantId . '/logo/uploads/images/merchant/' . $name;
            $s3 = Storage::disk('s3');
            $s3->put($file_path, $image);

            $list = $s3->listContents($file_path);
            if($list) {
                foreach ($list as $s3_files) {
                    $file_path = $s3_files['path'];
                }
            }

            $merchant->merchant_logo = $file_path;
            $merchant->save();
        }

        if (isset($newMerchantId) && $request->get('gst_authentication')){
            $karzaAPI = KarzaAPI::where('merchant_id', $newMerchantId)->where('type','merchant_id')->first(['id']);
            if (!isset($karzaAPI->id)){
                $karzaAPI = new KarzaAPI();
                $karzaAPI->data = $request->get('gst_authentication');
                $karzaAPI->merchant_id = $newMerchantId;
                $karzaAPI->type = 'merchant_id';
                $karzaAPI->gstin = $request->get('gst_number');
                $karzaAPI->created_at = Carbon::now()->toDateTimeString();
                $karzaAPI->user_id = Auth::user()->id;
                $karzaAPI->save();
            }
        }

        // $user = User::where('id', Auth::user()->id)->first(['name']);
        MerchantLogs::insert([
            'merchant_id' => $newMerchantId,
            'user_id' => Auth::user()->id,
            'data' => 'merchant created by'.Auth::user()->name,
            'created_at' => Carbon::now()->toDateTimeString()

        ]);

        $user_exists = $this->createMerchantUser($request, $merchant);
        if($user_exists == false){
            \Session::flash('error', 'User already exists');
            return redirect()->back();
        }

        //UserEmails::validates(['emails' => [$request->get('email'),$request->get('rcp_email'),$request->get('sales_head_email'),$request->get('finance_head_email')],'type' => 'merchant', '_id' => $merchant->id]);

        if ($request->has('referred_by')) {
            (new MerchantApiController())->sendCommunicationToRefer($merchant, 1);
        }

        if($request->has('comment_box') && !empty($request->get('comment_box'))){
            if(isset($newMerchantId)) {
                $showForMerchant = ($request->has('show_for_merchant')) ? 1 : 0;
                $comment = new Comments();
                $comment->application_id = $newMerchantId;
                $comment->user_id = Auth::user()->id;
                $comment->comment = $request->get('comment_box');
                $comment->type = 'merchant';

                $comment->show_merchant = $showForMerchant;
                $comment->show_consumer = 0;
                $comment->save();
            }
        }

        if($merchant->gst_number && !empty($merchant->gst_number)){
            $this->getKarzaGSTData($merchant->gst_number,$newMerchantId);
        }
        \Session::flash('success', 'Merchant successfully added');

        if ($request->has('is_redirect')){
            if ($request->get('is_redirect') == 'module'){
                return redirect()->to('/admin/merchant/module/'.$newMerchantId);
            }else if ($request->get('is_redirect') == 'bank'){
                return redirect()->to('/admin/merchant/add-bank-details/'.$newMerchantId);
            }
        }

        return redirect('admin/merchant/edit/'.$newMerchantId);
    }

    public function merchantModule($id){
        $merchantModule = new MerchantModule();
        $merchantModule = $merchantModule->getMerchantModule($id);
        $merchant = Merchant::where('id',$id)->first();
        $data['merchantModule'] = $merchantModule;
        $data['id'] = $id;
        $data['merchant'] = $merchant;
        return view('admin.merchant.module.index',$data);
    }

    public function createMerchantModule($id, Request $request){
        $data['id'] = $id;
        if($request->has('type')){
            $data = $this->sendAgreement($id);
            $response = $data->getData();
            if($response->status == 'OK'){
                MerchantModule::where('status','>',0)->where('merchant_id',$id)->update(['status'=>3]);
                // $user = User::where('id', Auth::user()->id)->first(['name']);
                MerchantLogs::insert([
                    'merchant_id' => $id,
                    'user_id' => Auth::user()->id,
                    'data' => 'merchant module agreement sent by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()
                ]);

            }
            \Session::flash($response->status, $response->message);
            return redirect()->to('/admin/merchant/module/'.$id);
        }
        return view('admin.merchant.module.edit',['data'=>$data,'id'=>$id]);
    }

    public function editMerchantModule($module_id){
        $merchantModule = new MerchantModule();
        $merchantModule = $merchantModule->getModule($module_id);
        $data['merchantModule'] = $merchantModule;
        $data['id'] = $merchantModule->merchant_id;
        return view('admin.merchant.module.edit',$data);
    }

    public function saveModule($id, Request $request){
        $existingModule = MerchantModule::where('merchant_id',$id)->where('tenure',$request->get('tenure'))->first(['id']);
        $message = 'You can\'t create multiple module with same tenure';
        $status = 'error';
        if(!isset($existingModule->id)){
            $merchantModule = new MerchantModule();
            $merchantModule->merchant_id = $id;
            $merchantModule->tenure = $request->get('tenure');
            $merchantModule->subvention = $request->get('subvention');
            $merchantModule->status = $request->get('status');
            $merchantModule->save();
            $returnModuleId = $merchantModule->id;
            $message = 'Module successfully added';
            $status = 'success';
            // $current_module = MerchantModule::where('merchant_id',$id)->where('tenure',$request->get('tenure'))->first(['id']);

            // $user = User::where('id', Auth::user()->id)->first(['name']);
            MerchantLogs::insert([
                'merchant_id' => $id,
                'user_id' => Auth::user()->id,
                'merchant_module_id' => $returnModuleId ,
                'data' => 'merchant module created by '.Auth::user()->name,
                'created_at' => Carbon::now()->toDateTimeString()
            ]);

        }
        if ($request->has('ajax')){
            return response()->json(['status' => $status, 'message' => $message]);
        }

        \Session::flash($status, $message);
        return redirect()->to('/admin/merchant/module/'.$id);
    }


    public function getListOfMerchants(){
        return [
            'status' => 'OK', 'data' => [
                'abfl' => Services::getMerchant(),
                'avanse' => AvanseProcesses::activatedMerchants(),
                'lendbox' => LendBox::activatedMerchants(),
                'lendboxLsp' => LendBoxLSP::activatedMerchants(),
                'kmgp2p' => KMGP2P::activatedMerchants(),
            ]
        ];
    }

    public function isPanValidated($merchant_id){

        $file_path = $merchant_id."/".$merchant_id."_response_pan_valid_merchant.json";
        $file = Equifax::readFile($file_path);
        if($file !== false) {
            $file =json_decode($file,TRUE);
        } else {
            return ['message' => 'PAN Validation Pending', 'status' => false];
        }
        if(isset($file['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData'])) {
            $data = $file['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData'];

            if (isset($data['sch:VerifyIDResponse']['sch:vidNsdlResponses'])) {
                $data = $file['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData']['sch:VerifyIDResponse']['sch:vidNsdlResponses'];
                foreach ($data as $key => $item) {
                    if ($key == 'sch:nsdlResponse') {
                        if (isset($item['sch:returnCode']) && $item['sch:returnCode'] == 'E') {
                            return ['message' => $item['sch:returnCodeDesc'], 'status' => true];
                        }
                    }
                }
            }
        }

        return ['message' => 'PAN is not validated', 'status' => false];
    }

    public function getMerchantModuleValidation($id, Request $request){
        $modules = MerchantModule::where('merchant_id',$id)->where('status',1)->orderBy('tenure','asc')->get();
        $isError = true;
        if(isset($modules[0])) {
            $existTenure = [];
            $existSubvention = [];
            foreach ($modules as $module) {
                $existTenure[] = $module->tenure;
                $existSubvention[$module->tenure] = $module->subvention;
            }

            $maxTenure = max($existTenure);

            if ($request->get('tenure') > $maxTenure) {
                $maxSubvention = $existSubvention[$maxTenure];
                if ($request->get('subvention') <= $maxSubvention) {
                    $isError = false;
                }
            }

            if ($isError) {
                foreach ($modules as $module) {
                    if ($request->get('tenure') <= $module->tenure) {
                        if ($request->get('subvention') <= $module->subvention) {
                            $isError = false;
                        }
                        break;
                    }
                }
            }
        }else{
            $isError = false;
        }
        return response()->json(['status' => ($isError) ? 'FAIL' : 'OK']);
    }


    public function getLocationByAddress(Request $request){

        try {
            if ($request->has('address')) {
                $consumerApplicationController = new ConsumerApplicationController();
                $locations = $consumerApplicationController->getGeocodingDataFromAddress(['address' => $request->get('address'), 'pincode' => true]);
                return response()->json(['status' => 'OK', 'message' => 'Successfully fetched location', 'data' => $locations], 200);
            }
        }catch (\Exception $exception){
            return response()->json(['status' => 'FAIL', 'message' => 'Failed to fetch location.']);
        }

        return response()->json(['status' => 'FAIL', 'message' => 'Failed to fetch location']);
    }

    public function deleteModule($id){
        $merchantModule = MerchantModule::find($id);
        $merchant_id = $merchantModule->merchant_id;
        $merchantModule->delete();
        \Session::flash('success', 'Module successfully deleted');
        return redirect()->to('/admin/merchant/module/'.$merchant_id);
    }

    public function updateModule($id, Request $request){
        $merchantModule = MerchantModule::find($id);
        $existingTenure = $merchantModule->tenure;
        $existingsubvention = $merchantModule->subvention;
        $merchant_id = $merchantModule->merchant_id;
        $merchantModule->tenure = $request->get('tenure');
        $merchantModule->subvention = $request->get('subvention');
        $merchantModule->status = ($existingTenure != $request->get('tenure') || $existingsubvention != $request->get('subvention')) ? 2 : $request->get('status');
        $merchantModule->save();
        \Session::flash('success', 'Module successfully updated');
        return redirect()->to('/admin/merchant/module/'.$merchant_id);
    }

    public function getReferralCode(){
        $i = 0;
        do{
            $code = substr(md5(microtime()),rand(0,26),5);
            $user = User::where('code',$code)->first(['id']);
            if(!isset($user->id)){
                $i = 1;
            }
            $merchant = Merchant::where('code',$code)->first(['id']);
            if(!isset($merchant->id)){
                $i = 1;
            }
        }while($i == 0);

        return $code;
    }

    public function getEditMerchant($id)
    {
        $data['sidebar']  = 'merchant';
        $data['merchant'] = Merchant::with('bank_details','merchant_category','centers','grade', 'custom_grade', 'calls','gst_filling_data','digital_sign','offer','module','active_offers','moduleAll','merchant_logs')->where('id',$id)->first();
        if(!isset($data['merchant']->id)){
            \Session::flash('error', 'Merchant not found');
            return redirect()->to('/admin/merchants');
        }
        $merchant = Merchant::where('code',strtolower($data['merchant']->referred_by))->first(['merchant_name as name','code']);
        if (!isset($merchant->name)) {
            $merchant = User::where('code', strtolower($data['merchant']->referred_by))->first(['name','code']);
        }
        if (isset($merchant->code) && !empty($merchant->code)) {
            $data['referred_by'] = $merchant;
        }else{
            $code = $this->getReferralCode();
            Merchant::where('id', $id)->update(['code' => $code]);
        }
        $data['files'] = MerchantFiles::with('user')->where('merchant_id', $id)->where(function ($query) {
            $query->whereNull('location_id')
                ->orWhere('location_id',0);
        })->whereNull('type')->get();


        $callsLogs = CallLogs::with('picked_by')->where('_id',$id)->where('id_type','merchant_id')->whereNotNull('recording')->where('recording','!=','')->get(['id','_id','user_id','recording','purpose','created_at']);
        foreach ($callsLogs as $indexCall=>$callData){
            $data['files'][] = $callData;
        }

        if(count($data['merchant']->calls)){
            foreach ($data['merchant']->calls as $call){
                if (isset($call->sid)) {
                    $request = new Request(['sid' => $call->sid, 'id' => $call->id]);
                    $exotelService = new ExotelServices($request);
                    $exotelService->getCall();
                }
            }
        }

        $merchant_offer = MerchantOffers::where('merchant_id',$id)->where('status',1)->first(['status']);

        $data['offer'] = $merchant_offer;
        $data['grades'] = GradesV1::all();
        $data['customGrades'] = CustomGradesMain::get(['id','rule']);
        //$merchantsData = Merchant::with('digital_sign','bank_details','offer','module','active_offers','moduleAll','gst_filling_data')->first();
        $data['pendingDoc'] = $this->getHelperTxt($data['merchant']);
        if(!is_null($data['merchant']->gst_is_any_delay) || $data['merchant']->gst_is_any_delay != "") {
            if ($data['merchant']->gst_is_any_delay == 0) {
                $data['pendingDoc'] = array_merge($data['pendingDoc'], array('GST Delay' => false));
            }else{
                $data['pendingDoc'] = array_merge($data['pendingDoc'], array('GST Delay' => true));

            }
        }
        if(!is_null($data['merchant']->gst_is_defaulter) || $data['merchant']->gst_is_defaulter != "") {
            if ($data['merchant']->gst_is_defaulter == 0) {
                $data['pendingDoc'] = array_merge($data['pendingDoc'], array('GST Default' => false));
            }else{
                $data['pendingDoc'] = array_merge($data['pendingDoc'], array('GST Default' => true));
            }
        }

        if(!is_null($data['merchant']->months_gst_filled) || $data['merchant']->months_gst_filled != "") {
            if ($data['merchant']->months_gst_filled < 1) {
                $data['pendingDoc'] = array_merge($data['pendingDoc'], array('GST Filled' => false));
            }else{
                $data['pendingDoc'] = array_merge($data['pendingDoc'], array('GST Filled' => true));
            }
        }
        $mis_frequency = array('mis_not_app'=>true,'mis_once'=>false,'mis_twice'=>false);
        if(isset($data['merchant']->mis_frequency)){
            if($data['merchant']->mis_frequency == 2){
                $mis_frequency['mis_twice'] = true;
            }elseif($data['merchant']->mis_frequency == 1){
                $mis_frequency['mis_once'] = true;
            }else{
                $mis_frequency['mis_not_app'] = true;
            }
        }
        //corona
        $data['hotspotted_area'] = 0;
        $is_hotspot = CoronaHotspot::where('pincode', $data['merchant']->pincode)->first(['id']);

        if(isset($is_hotspot) && !empty($is_hotspot)) {
            $data['hotspotted_area'] = 1;
        }

        $data['categories'] = MerchantCategory::all();
        $data['state_list'] = StateModel::get(['name','code','id']);
        $data['couriers'] = (new CouriersController(new Request()))->getCourier($id, 1);
        $data['merchants'] = Merchant::where('is_guest_active', 1)->get();
        $data['advisors'] = User::where('user_type', '>=', 5)
            ->whereNotIn('user_type', [48, 49])
            ->whereNull('deleted_at')
            ->get(['id', 'name']);
        $data['pan_validation'] = $this->isPanValidated($id);
        $data['users']  = $data['advisors'];

        if((!$this->getBankValidation($data['merchant']) && empty($data['merchant']->user_banking_reason)) || empty($data['merchant']->location_serviceable)){
            $data['cannotActive'] = true;
        }

        // $DigitalSignDetails = DigitalSignDetails::where('application_id', $id)->where('type','merchant')->first(['id']);
        if(!isset($data['merchant']->digital_sign) && (empty($data['merchant']->agreement_signed_with) || empty($data['merchant']->merchant_reg_type) || empty($data['merchant']->designation_of_sign_auth) || empty($data['merchant']->agreement_signed_by))){
            $data['cannotActive'] = true;
        }

        $data['is_pin_set'] = 1;
        $merchant_address = Pincode::where('pincode', $data['merchant']->pincode )->first(['state']);
        if(isset($merchant_address->state) && !empty($merchant_address->state)) {
            $state_name = ucwords(strtolower($merchant_address->state));

            if ($state_name != $data['merchant']->state) {
                $data['is_pin_set'] = 0;

            }
        }

        $gstAuth = KarzaAPI::where('merchant_id', $id)->where('type','merchant_id')->first(['id']);
        if(isset($gstAuth->id)){
            $data['gst_data_pulled'] = 1;
        }

        if ($data['merchant']['type_of_firm'] == 'private limited') {
            $data['merchant']['type_of_firm'] = 'company';
        } else if ($data['merchant']['type_of_firm'] == 'sole prop') {
            $data['merchant']['type_of_firm'] = 'proprietorship';
        } else if ($data['merchant']['type_of_firm'] == 'unregistered') {
            $data['merchant']['type_of_firm'] = 'partnership';
        }  else if ($data['merchant']['type_of_firm'] == 'registered') {
            $data['merchant']['type_of_firm'] = 'partnership';
        } else if ($data['merchant']['type_of_firm'] == 'huf/aop') {
            $data['merchant']['type_of_firm'] = null;
        } else if ($data['merchant']['type_of_firm'] == 'upl') {
            $data['merchant']['type_of_firm'] = 'company';
        } else if ($data['merchant']['type_of_firm'] == 'lpl') {
            $data['merchant']['type_of_firm'] = 'company';
        } else if ($data['merchant']['type_of_firm'] == 'llp') {
            $data['merchant']['type_of_firm'] = 'llp';
        }

        $data['tradeAdvanceDisbursement'] = TradeAdvanceDisbursement::with('application.disbursed')->where('merchant_id', $id)->get();
        $data['tradeLogs'] = TradeAdvanceLogs::with('user')->where('merchant_id', $id)->get();
        $data['merchant_log_base'] = $this->getMerchantLogBase64($id, $data['merchant']->merchant_logo);
        $data['comm_settings'] = (new CommunicationService())->getCommunicationSetting($id);
        $data['mis_frequency'] = $mis_frequency;
        $data['activeLenders'] = Lenders::with(['merchant_whitelist' => function ($query) use ($id) {
            $query->select('merchant_id','lender_id');
            $query->where('merchant_id',$id);
            $query->whereNull('deleted_at');
        }])->where('status','active')->get(['id','name']);

        $data['isSolarMerchant'] = false;
        if($data['merchant']->category_id == _env('SOLAR_CATEGORY_ID')){
            $data['isSolarMerchant'] = true;
        }

        return view('admin.merchant.edit', $data);
    }

    public function getMerchantLogBase64($id, $merchant_logo = ''){
        $s3 = Storage::disk('s3');

        $base64 = '';
        if(empty($merchant_logo)){
            $merchant = Merchant::where('id', $id)->first(['merchant_logo']);
            $merchant_logo = $merchant->merchant_logo;
        }

        if($merchant_logo) {
            $merchant_merchant_logo = $s3->get(
                $merchant_logo
            );
            $lPath = storage_path() . '/' . time() . '' . $id;
            $file = fopen($lPath, "w");
            fwrite($file, $merchant_merchant_logo);
            fclose($file);


            $type = pathinfo($lPath, PATHINFO_EXTENSION);

            $data = file_get_contents($lPath);
            $base64 = 'data:image/' . $type . ';base64,' . base64_encode($data);
            unlink($lPath);
        }

        return $base64;

    }

    public function getQRCode(Request $request){
        return response(['status' => 'OK', 'link' => URL::to(\App\QRCode::getQRCodeFile($request->get('merchant_id')))]);
    }

    public function saveCenter($id, Request $request){
        try {
            $status = 'OK';
            $center = new Centers();
            $center->merchant_id = $id;
            $center->name = $request->get('name');
            $center->city = $request->get('city');;
            $center->area = $request->get('area');
            $center->pincode = $request->get('pincode');
            $center->status = 1;
            $center->save();
        }catch (\Exception $exception){
            $status = 'FAIL';
        }

        return response()->json(['status'=>$status],200);
    }

    public function getEditPostMerchant(EditMerchantRequest $request, $id)
    {
        if ($request->has('documents') && count(array_diff($request->get('documents'), [null]))) {
            if(isset($request->fileNamePDF)){
                foreach($request->fileNamePDF as $_key => $fileArray){
                    foreach ($fileArray as $__key => $file) {
                        if(filesize($file) >18874368){
                            Session::flash('error', 'Uploaded file is too large');
                            return redirect()->back();
                        }
                    }
                }
            }
        }

        $merchant = Merchant::find($id);

        if ($request->has('is_redirect') && $request->get('is_redirect') == 'fldg-category'){
            return redirect()->to('/admin/merchant/fldg/'.$merchant->id.'/categories/');
        }

        if($request->get('whitelist_lender_list')){
            $merchantwhitelistinfo = MerchantWhitelistLender::where('merchant_id',$id)->whereNull('deleted_at')->pluck('lender_id')->toArray();
            if(count($merchantwhitelistinfo) > 0){
                foreach($request->get('whitelist_lender_list') as $w_lender){
                    if(in_array($w_lender, $merchantwhitelistinfo)){
                        $merchantwhitelistinfo = array_diff($merchantwhitelistinfo,[$w_lender]);
                    }else{
                        $lenderwhitelisting = new MerchantWhitelistLender();
                        $lenderwhitelisting->merchant_id = $id;
                        $lenderwhitelisting->lender_id = $w_lender;
                        $lenderwhitelisting->user_id = $request->get('user_id');
                        $lenderwhitelisting->save();
                    }
                }
                if(count($merchantwhitelistinfo) > 0){
                    foreach($merchantwhitelistinfo as $changeexistingwhitelistmerchant){
                        $deletedatetime = date('Y-m-d H:i:s');
                        $data = array('deleted_at' => $deletedatetime, 'deleted_by' => $request->get('user_id'));
                        DB::table('merchant_whitelist_lender')->where('merchant_id',$id)->where('lender_id',$changeexistingwhitelistmerchant)->update($data);
                    }
                }
            }else{
                foreach($request->get('whitelist_lender_list') as $w_lender){
                    $lenderwhitelisting = new MerchantWhitelistLender();
                    $lenderwhitelisting->merchant_id = $id;
                    $lenderwhitelisting->lender_id = $w_lender;
                    $lenderwhitelisting->user_id = $request->get('user_id');
                    $lenderwhitelisting->save();
                }
            }
        }

        $isChargingSubvention = $merchant->charge_subvention_remaining_amount ? 1 : 0;
        $max_approval_limit_previous = isset($merchant->max_approval_value) ? $merchant->max_approval_value : 0;
        $ltv_previous = isset($merchant->loan_to_value_limit) ? $merchant->loan_to_value_limit : null;
        $kw_capping_previous = isset($merchant->kw_capping) ? $merchant->kw_capping : 0;

        $anchor_merchant_id = $merchant->anchor_merchant_id;
        $is_guest_active_check = $merchant->is_guest_active;

        if ($request->has('slug')){
            $duplicateSlug = Merchant::where('slug',$request->get('slug'))->where('id','!=',$id)->first(['id']);
            if (isset($duplicateSlug->id)){
                \Session::flash('error', 'Can\'t update the merchant because of duplicate slug');
                return redirect('admin/merchant/edit/'.$merchant->id);
            }
        }

        $status = $merchant->status;
        $preStatus = $merchant->pre_status;
        $reGenerateGST = $request->has('re_generate_gst_data') && $request->get('re_generate_gst_data') == 'on' ? true : false;


        $merchant->fill($request->input());

        if($request->has('sales_person_number')){
            // Normalise the comma-separated numbers: keep only digit groups, trim spaces, drop blanks
            $salesNumbers = array_filter(array_map('trim', explode(',', $request->get('sales_person_number'))), function($num){
                return $num !== '' && ctype_digit($num);
            });
            $merchant->sales_person_number = implode(',', $salesNumbers);
        }

        if($request->has('u_id')){
            $merchant->source = $request->get('source').'-'.$request->get('u_id');
        }

        if($request->has('is_collected')){
            $merchant->is_collected = 1;
        }else{
            $merchant->is_collected = 0;
        }

        if($request->has('insurance_premium')){
            $merchant->insurance_premium = 1;
        }else{
            $merchant->insurance_premium = 0;
        }

        if($request->has('charge_subvention_remaining_amount')){
            $merchant->charge_subvention_remaining_amount = 1;
        }else{
            $merchant->charge_subvention_remaining_amount = 0;
        }

        if($request->has('send_da_on_checklist')){
            $merchant->send_da_on_checklist = 1;
        }else{
            $merchant->send_da_on_checklist = 0;
        }

        if($request->has('table_of_offers')){
            $merchant->table_of_offers = 1;
        }else{
            $merchant->table_of_offers = 0;
        }

        if($request->has('remove_esign_otp')){
            $merchant->remove_esign_otp = 1;
        }else{
            $merchant->remove_esign_otp = 0;
        }

        if($request->has('skip_appl_otp')){
            $merchant->skip_appl_otp = 1;
        }else{
            $merchant->skip_appl_otp = 0;
        }

        if($request->has('combined_sl_la')){
            $merchant->combined_sl_la = 1;
        }else{
            $merchant->combined_sl_la = 0;
        }

        if($request->has('pending_collection_report')){
            $merchant->pending_collection_report = 1;
        }else{
            $merchant->pending_collection_report = 0;
        }

        if($request->has('co_applicant')){
            $merchant->co_applicant = 1;
        }else{
            $merchant->co_applicant = 0;
        }

        if($request->has('pay_data_sharing')){
            $merchant->pay_data_sharing = 1;
        }else{
            $merchant->pay_data_sharing = 0;
        }

        if($request->has('is_fldg_active')){
            $merchant->is_fldg_active = 1;
        }else{
            $merchant->is_fldg_active = 0;
        }

        if($request->has('hold_liability')){
            $merchant->hold_liability = 1;
        }else{
            $merchant->hold_liability = 0;
        }

        if($request->has('is_trade_advance')){
            $merchant->is_trade_advance = 1;
        }else{
            $merchant->is_trade_advance = 0;
        }

        if($request->has('is_gst')){
            $merchant->is_gst = 1;
        }else{
            $merchant->is_gst = 0;
        }

        if($request->has('high_risk')){
            $merchant->high_risk = 1;
        }else{
            $merchant->high_risk = 0;
        }
        if($request->has('collect_advance_amount')){
            $merchant->collect_advance_amount = 1;
        }else{
            $merchant->collect_advance_amount = 0;
        }

        if($request->has('is_processing_fees')){
            $merchant->is_processing_fees = 1;
        }else{
            $merchant->is_processing_fees = 0;
        }

        if($request->has('is_hypothecation_active')){
            $merchant->is_hypothecation_active = 1;
        }else{
            $merchant->is_hypothecation_active = 0;
            $merchant->hypothecation_charges = null;
        }

        if($request->has('disb_post_installation')){
            $merchant->disb_post_installation = 1;
        }else{
            $merchant->disb_post_installation = 0;
        }

        if($request->has('guest_merchant')){
            $merchant->is_guest_active = 0;
        }

        if($request->has('vulnerable_merchant')){
            $merchant->vulnerable_merchant = 1;
            $merchant->vulnerable_merchant_date = Carbon::now()->toDateTimeString();
        }else{
            $merchant->vulnerable_merchant = 0;
            $merchant->vulnerable_merchant_date = null;
        }

        if($request->has('mis_frequency')){
            if($request->mis_frequency == 'twice'){
                $merchant->mis_frequency = 2;
            }elseif($request->mis_frequency == 'once'){
                $merchant->mis_frequency = 1;
            }else{
                $merchant->mis_frequency = 0;
            }
        }

        if($request->has('is_auto_abort')){
            $merchant->is_auto_abort = 1;
        }else{
            $merchant->is_auto_abort = 0;
        }
        if($request->has('is_auto_abort_approved')){
            $merchant->is_auto_abort_approved = 1;
        }else{
            $merchant->is_auto_abort_approved = 0;
        }
        if($request->has('processing_fee_on_advance_amount')){
            $merchant->processing_fee_on_advance_amount = 1;
        }else{
            $merchant->processing_fee_on_advance_amount = 0;
        }
        if($request->has('subv_inv_on_center')){
            $merchant->subv_inv_on_center = 1;
        }else{
            $merchant->subv_inv_on_center = 0;
        }
        if($request->has('kw_capping')){
            $kw_capping_now = 1;
        }else{
            $kw_capping_now = 0;
        }

        $user = User::where('id', Auth::user()->id)->first(['name']);
        $gradeInfo = Merchant::where('id', $id)->first(['is_auto_approve','is_auto_doc','pre_approval','table_of_offers', 'grade_id']);

        $max_approval_value_now = $request->has('max_approval_value') ? (float)$request->get('max_approval_value') : 0;

        $merchant->grade_id = $request->has('grade_id') ? $request->get('grade_id') : $gradeInfo->grade_id;

        $merchant->create_app_lead_on_map = $request->has('create_app_lead') ? 1 : 0;
        $merchant->is_auto_approve = $request->has('is_auto_approve') ? 1 : 0;
        // $is_auto_approved = Merchant::where('id', $id)->first(['is_auto_approve']);
        $is_auto_approved_num = ($gradeInfo->is_auto_approve) ? true : false;

        $merchant->is_auto_doc = $request->has('is_auto_doc') ? 1 : 0;
        // $is_auto_doc = Merchant::where('id', $id)->first(['is_auto_doc']);
        $is_auto_doc_num = ($gradeInfo->is_auto_doc) ? true : false;

        $merchant->pre_approval = $request->has('pre_approval') ? 1 : 0;
        // $pre_approval = Merchant::where('id', $id)->first(['pre_approval']);
        $pre_approval_num = ($gradeInfo->pre_approval) ? true : false;


        // $table_of_offers_merchant = Merchant::where('id', $id)->first(['table_of_offers']);
        $table_of_offers_num = ($gradeInfo->table_of_offers) ? true : false;

        $merchant->pf_surcharge_active = $request->has('pf_surcharge_active') ? 1 : 0;

        if($kw_capping_previous !== $kw_capping_now){
            $msg = 'KW Capping changed to '. $kw_capping_now .' by '. $user->name;
            $data = [
                'merchant_id' => $merchant->id,
                'user_id' => Auth::user()->id,
                'data' => $msg,
                'created_at' => Carbon::now()->toDateTimeString()
            ];
            MerchantLogs::insert($data);
        }

        if($max_approval_limit_previous !== $max_approval_value_now){
            $msg = 'Merchant max approval limit changed to '. $max_approval_value_now .' by '. $user->name;
            $data = [
                'merchant_id' => $merchant->id,
                'user_id' => Auth::user()->id,
                'data' => $msg,
                'created_at' => Carbon::now()->toDateTimeString()
            ];
            MerchantLogs::insert($data);
        }

        $ltv_now = $request->has('loan_to_value_limit') && $request->get('loan_to_value_limit') !== '' ? (int)$request->get('loan_to_value_limit') : null;
        if($ltv_previous !== $ltv_now){
            $msg = 'Merchant LTV changed to '. $ltv_now .' by '. $user->name;
            $data = [
                'merchant_id' => $merchant->id,
                'user_id' => Auth::user()->id,
                'data' => $msg,
                'created_at' => Carbon::now()->toDateTimeString()
            ];
            MerchantLogs::insert($data);
        }

        if($table_of_offers_num !== $request->has('table_of_offers')){
            $msg = ($request->has('table_of_offers')) ? 'Table of offers ticked by '.$user->name : 'Table of offers unticked by '.$user->name;
            $data = [
                'merchant_id' => $merchant->id,
                'user_id' => Auth::user()->id,
                'data' => $msg,
                'created_at' => Carbon::now()->toDateTimeString()
            ];
            MerchantLogs::insert($data);
        }

        if($is_auto_approved_num !== $request->has('is_auto_approve') && $is_auto_doc_num !== $request->has('is_auto_doc') && $pre_approval_num !== $request->has('pre_approval')){
            $msg = ($request->has('is_auto_approve') && $request->has('is_auto_doc') && $request->has('pre_approval')) ? 'Is Auto Approved, Auto Doc and Pre Approved by '.$user->name : 'Unticked Auto Approved, Auto Doc and Pre Approved by '.$user->name;
            $data = [
                'merchant_id' => $merchant->id,
                'user_id' => Auth::user()->id,
                'data' => $msg,
                'created_at' => Carbon::now()->toDateTimeString()
            ];
            MerchantLogs::insert($data);
        }else{
            if($is_auto_approved_num !== $request->has('is_auto_approve')){
                $msg = ($request->has('is_auto_approve')) ? 'Is Auto Approved by '.$user->name : 'Unticked Auto Approved by '.$user->name;
                $data = [
                    'merchant_id' => $merchant->id,
                    'user_id' => Auth::user()->id,
                    'data' => $msg,
                    'created_at' => Carbon::now()->toDateTimeString()
                ];
                MerchantLogs::insert($data);
            }

            if($is_auto_doc_num !== $request->has('is_auto_doc')){
                $msg = ($request->has('is_auto_doc')) ? 'Is Auto Doc by '.$user->name : 'Unticked Auto Doc by '.$user->name;
                $data = [
                    'merchant_id' => $merchant->id,
                    'user_id' => Auth::user()->id,
                    'data' => $msg,
                    'created_at' => Carbon::now()->toDateTimeString()
                ];
                MerchantLogs::insert($data);
            }


            if($pre_approval_num !== $request->has('pre_approval')){
                $msg = ($request->has('pre_approval')) ? 'Is Pre Approved by '.$user->name : 'Unticked Pre Approved by '.$user->name;
                $data = [
                    'merchant_id' => $merchant->id,
                    'user_id' => Auth::user()->id,
                    'data' => $msg,
                    'created_at' => Carbon::now()->toDateTimeString()
                ];
                MerchantLogs::insert($data);
            }
        }


        $merchant->created_by = Auth::user()->id;
        $current_grade = (int)$merchant->grade_id;

        // $user = User::where('id', Auth::user()->id)->first(['name']);

        if($request->has('grade_id') && !empty($request->get('grade_id'))){
            $grade = GradesV1::where('id', $request->get('grade_id') )->first(['grade_alias']);
            $log_exists = MerchantLogs::where('merchant_id', $id)->where('grade_id', $current_grade)->first(['id']);
            if(!isset($log_exists->id) && empty($log_exists->id)){
                $data = [
                    'merchant_id' => $merchant->id,
                    'user_id' => Auth::user()->id,
                    'grade_id' => $request->get('grade_id'),
                    'data' => 'grade set to '.$grade->grade_alias.' by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()

                ];
                MerchantLogs::insert($data);
            }
        }


        if($isChargingSubvention && !$request->has('charge_subvention_remaining_amount')){
            Merchant::where('anchor_merchant_id', $id)->update(['charge_subvention_remaining_amount' => 0]);
            MerchantOffers::where('merchant_id', $id)->update(['status' => 0]);
        }

        $merchantslug = Merchant::where('slug',$request->get('slug'))->where('status','active')->get();
        if(isset($merchantslug[0]->id) && $merchantslug[0]->id != $id){
            \Session::flash('error', 'Alias Already used');
            return redirect('admin/merchant/edit/'.$id);
        }
        if ($request->hasfile('merchant_logo')) {

            $image = $request->file('merchant_logo');
            $name  = str_slug($merchant->merchant_name) . '_' . uniqid() . '.' . $image->getClientOriginalExtension();

            /*$relativePath    = '/uploads/images/merchant';
            $destinationPath = public_path($relativePath);

            $imagePath = $relativePath . "/" . $name;
            $image->move($destinationPath, $name);*/

            $file_path = '/merchant/' . $merchant->id . '/logo/uploads/images/merchant/' . $name;
            $s3 = Storage::disk('s3');
            $s3->put($file_path, $image);

            $list = $s3->listContents($file_path);
            if($list) {
                foreach ($list as $s3_files) {
                    $file_path = $s3_files['path'];
                }
            }

            $merchant->merchant_logo = $file_path;
        }

        if($request->has('documents') && count(array_diff($request->get('documents'),[null]))){
            (new ConsumerApplicationController())->uploadConsumerDocuments($request, $id, 'merchant');
        }

        $result = [];
        if ($request->has('status') && ($request->get('status') == 'enable' || ($status == 'inactive' && $request->get('status') == 'active'))){
            if($request->has('status') && ($request->get('status') == 'enable')){
                MerchantOffers::where('merchant_id', $id)->update(['status' => 0]);
                MerchantModule::where('merchant_id', $id)->update(['status' => 0]);
                $result = [
                    'merchant_id' => $id,
                    'user_id'=>Auth::user()->id,
                    'data'=>'merchant enabled guest merchant by '.$user->name,
                    'created_at'=>Carbon::now()->toDateTimeString()
                ];
            }else{
                $result = [
                    'merchant_id' => $id,
                    'user_id'=>Auth::user()->id,
                    'data'=>'merchant activated for disbursement by '.$user->name,
                    'created_at'=>Carbon::now()->toDateTimeString()
                ];
            }
            $merchant->status = 'active';
            Leads::where('merchant_id', $id)->update(['status' => '9']);
            $merchant->is_guest_active = 1;
            $merchant->pre_status = 0;
            $merchant->status_changed_by = Auth::user()->id;
            (new MerchantApiController())->sendCommunicationToRefer($merchant, 2);

        }else if($status == 'active' && $preStatus == 1 && $request->get('status') == 'active'){
            $merchant->is_guest_active = 1;
            $merchant->pre_status = 0;
            $merchant->status = 'active';
            $merchant->status_changed_by = Auth::user()->id;
            $result = [
                'merchant_id' => $id,
                'user_id'=>Auth::user()->id,
                'data'=>'merchant activated for disbursement by '.Auth::user()->name,
                'created_at'=>Carbon::now()->toDateTimeString()
            ];

            $this->internal = true;
            $this->downloadCategorySpecifCreative($id);

        }else if($request->has('status') && $request->get('status') == 'inactive' && $status != 'inactive'){
            // $user = User::where('id', Auth::user()->id)->first(['name']);
            $result = [
                'merchant_id' => $id,
                'user_id'=>Auth::user()->id,
                'data'=>'merchant deactivated by '.Auth::user()->name,
                'created_at'=>Carbon::now()->toDateTimeString()
            ];
        }


        if(!$is_guest_active_check && ((!$anchor_merchant_id && $request->get('anchor_merchant_id')) || ($request->get('anchor_merchant_id') != $anchor_merchant_id))) {
            $this->resetOfferAccordingToAnchorMerchant($id, $anchor_merchant_id, $request);
        }


        $year_in_business = $request->get('year_in_business') ? $request->get('year_in_business') : 0;
        $month_in_business = $request->get('month_in_business') ? $request->get('month_in_business') : 0;

        $merchant->year_in_business = $month_in_business.'|'.$year_in_business;

//        $merchant->liability_percent = $request->hold_percentage;
        $merchant->kw_capping = $kw_capping_now;

        if($request->has('status') && $request->get('status') == 'training'){
            $merchant->pre_status = 1;
            $merchant->is_guest_active = 1;
            $merchant->status = 'active';
            $latest_entry = MerchantLogs::where('merchant_id', $id)->orderBy('created_at', 'desc')->first(['data']);

            if($status!='active' || (isset($latest_entry->data) && !empty($latest_entry->data) && (strpos($latest_entry->data, 'training') === false)) || (!isset($latest_entry->data) && empty($latest_entry->data))){
                $result = [
                    'merchant_id' => $id,
                    'user_id'=>Auth::user()->id,
                    'data'=>'merchant activated for login/training by '.$user->name,
                    'created_at'=>Carbon::now()->toDateTimeString()
                ];

                $this->internal = true;
                $this->downloadCategorySpecifCreative($id);
            }
        }

        $merchant->save();
        MerchantLogs::insert($result);
        if($request->has('communication_setting')){
            (new CommunicationService())->saveCommunicationSetting($id, $request->get('communication_setting'), $request->get('communication'));
        }else{
            $Comm_data = (new CommunicationService())->getCommunicationSetting($id);
            if(!empty($Comm_data)) {
                (new CommunicationService())->deleteCommunication($id);
            }
        }

        if($request->get('inform_rm_change')) {
            $this->informMerchantOnRMChange($merchant->id);
        }
        if($merchant->gst_number && !empty($merchant->gst_number) && (empty($merchant->gst_data) || $reGenerateGST)){
            $this->getKarzaGSTData($merchant->gst_number,$merchant->id);
        }
        \Session::flash('success', 'Product successfully updated');
        if ($request->has('is_redirect')){
            if ($request->get('is_redirect') == 'module'){
                return redirect()->to('/admin/merchant/module/'.$merchant->id);
            }else if ($request->get('is_redirect') == 'bank'){
                return redirect()->to('/admin/merchant/add-bank-details/'.$merchant->id);
            }else if ($request->get('is_redirect') == 'offer'){
                return redirect()->to('/admin/merchant/offers/'.$merchant->id);
            }
        }
        return redirect('admin/merchant/edit/'.$merchant->id);
    }

    public function saveTradeAdvance(Request $request){
        if($request->get('merchant_id')) {
            $merchant = Merchant::find($request->get('merchant_id'));
            $trade_advance_logs = new TradeAdvanceLogs();
            $trade_advance_logs->merchant_id = $request->get('merchant_id');
            $trade_advance_logs->trade_advance_amount = $request->get('trade_advance_amount');
            $trade_advance_logs->user_id = $request->get('user_id');
            $trade_advance_logs->save();
            $merchant->trade_advance_amount = $request->get('trade_advance_amount');
            $merchant->is_trade_advance = 1;
            $merchant->save();
            return response()->json(['status' => 'OK']);
        }

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

    public function deleteDocument(Request $request){
        if($request->get('id')){
            $user = User::where('id',$request->get('user_id'))->first(['name']);
            $merchant = Merchant::find($request->get('merchant_id'));
            $file = MerchantFiles::where('id', $request->get('id'))->first();

            // $deleted_name = explode('/', $file->file_path);

            // $filePath = 'https://'._env('AWS_BUCKET')._env('AWS_ORIGIN') . '/' .$file->file_path;
            // $file_path = '/merchant/' . $request->get('merchant_id') . '/deleted/' . $deleted_name[count($deleted_name) - 1];
            // $s3 = Storage::disk('s3');
            // $s3->put($file_path, $filePath);
            // $s3->delete($file->file_path);

//            MerchantFiles::where('id', $request->get('id'))->update(['deleted_by' => $request->get('user_id')]);
            MerchantFiles::where('id', $request->get('id'))->delete();
            // (new Slack(new Request(['title' => 'Document Deleted', 'fields' =>
            //     ['Deleted By' => $user->name, 'Merchant Name' => $merchant->merchant_name,
            //         'Document Name' => ucwords(str_replace('_',' ',$file->file_type))], 'group' => 'customer_documents'])))->send();
            return response()->json(['status' => 'OK']);
        }
        return response()->json(['status' => 'FAIL']);
    }

    public function resetOfferAccordingToAnchorMerchant($merchant_id, $anchor_merchant_id, Request $request){

        if($request->get('anchor_merchant_id')){
            $merchant = Merchant::find($merchant_id);
            $merchantOffers = MerchantOffers::where('merchant_id', $request->get('anchor_merchant_id'))->get();

            MerchantOffers::where('merchant_id', $merchant_id)->delete();
            foreach ($merchantOffers as $offer){
                $data = $offer->toArray();
                unset($data['id']);
                $offers = new MerchantOffers();
                $offers->fill($data);
                $offers->created_by = 107;
                $offers->category_id = $merchant->category_id;
                $offers->checked_by = 107;
                $offers->status = 1;
                $offers->created_at = Carbon::now()->toDateTimeString();
                $offers->merchant_id = $merchant_id;
                $offers->save();
            }

            //update module
            $anchorMerchantModule = MerchantModule::where('merchant_id', $request->get('anchor_merchant_id'))->get();
            MerchantModule::where('merchant_id', $merchant_id)->delete();

            foreach ($anchorMerchantModule as $module) {
                $merchantModule = new MerchantModule();
                $merchantModule->merchant_id = $merchant_id;
                $merchantModule->tenure = $module->tenure;
                $merchantModule->subvention = $module->subvention;
                $merchantModule->status = $module->status;
                $merchantModule->save();
            }
        }else if ($anchor_merchant_id){
            MerchantOffers::where('merchant_id', $merchant_id)->update(['status' => 0]);
            MerchantModule::where('merchant_id', $merchant_id)->update(['status' => 0]);
        }
    }

    public function merchantDestroy($id)
    {
        $merchant = Merchant::find($id);
        if ($merchant) {
            $merchant->delete();
            \Session::flash('success', 'Merchant successfully deleted');
            return redirect()->back();
        } else {
            \Session::flash('error', 'Merchant not deleted');
            return redirect()->back();
        }
    }

    /*Merchant Location*/
    public function getMerchantLocation($merchant_id)
    {   $keyword = Input::has('search_text') ? Input::get('search_text') : '';
        $data['sidebar']            = 'merchant';
        $data['merchant_id']        = $merchant_id;
        $data['merchant_locations'] = MerchantLocation::with('merchant')->withTrashed()->where('merchant_id', $merchant_id)->where(function ($query) use ($keyword) {
            $query->where('name', 'like', '%' . $keyword . '%')
                ->orWhere('email', 'like', '%' . $keyword . '%')
                ->orWhere('phone', 'like', '%' . $keyword . '%')
            ;
        })->paginate(20);

        $data['search_text'] = $keyword;
        $data['isMerchantSection'] = true;
        return view('admin.merchant.merchant_locations.index', $data);
    }

    public function getAddMerchantLocation($merchant_id)
    {
        $data['sidebar']     = 'merchant';
        $data['merchant_id'] = $merchant_id;
        $data['state_list'] = StateModel::get(['name','code','id']);
        $data['users']  = User::where('user_type','>=',5)->whereNull('deleted_at')->get(['id','name']);

        return view('admin.merchant.merchant_locations.add', $data);
    }

    public function getAddPostMerchantLocation(AddMerchantLocationRequest $request, $merchant_id)
    {
        $data['sidebar']                = 'merchant';
        $merchant_location              = new MerchantLocation;
        $merchant_location->merchant_id = $merchant_id;
        $merchant_location->fill($request->all());
        $merchant_location->is_physical_verification = 1;
        $merchant_location->save();

        UserEmails::validates(['emails' => [$request->get('email')],'type' => 'merchant_location', 'id' => $merchant_location->id]);

        $reGenerateGST = $request->has('re_generate_gst_data') && $request->get('re_generate_gst_data') == 'on' ? true : false;

        if($merchant_location->gst_number && !empty($merchant_location->gst_number) && (empty($merchant_location->gst_data) || $reGenerateGST)){
            $this->getKarzaGSTData($merchant_location->gst_number,$merchant_location->id, 'merchant_location_id');
        }

        return redirect('admin/merchant/location/' . $merchant_id);
    }

    public function generateEquifaxReport($id){
        $application = new ConsumerApplicationController();
        $application->isRoot = false;
        return $application->generateEqifaxReport($id, 'merchant');
        // return $application->generateSoftBureauReport($id, 'merchant');
    }

    public function getEditMerchantLocation($merchant_loc_id, $merchant_id)
    {
        $data['sidebar']     = 'merchant';
        $data['files'] = MerchantFiles::where('merchant_id', $merchant_id)->whereNull('type')->where('location_id', $merchant_loc_id)->get();
        $data['mer_loc']     = MerchantLocation::withTrashed()->with('calls','gst_filling_data')->where('id', $merchant_loc_id)->where('merchant_id', $merchant_id)->first();
        $data['merchant_id'] = $merchant_id;
        $data['state_list'] = StateModel::get(['name','code','id']);
        $data['merchant'] = Merchant::where('id', $merchant_id)->first();
        $data['users']  = User::where('user_type','>=',5)->whereNull('deleted_at')->get(['id','name']);

        if(count($data['mer_loc']->calls)){
            foreach ($data['mer_loc']->calls as $call){
                if (isset($call->sid)) {
                    $request = new Request(['sid' => $call->sid, 'id' => $call->id]);
                    $exotelService = new ExotelServices($request);
                    $exotelService->getCall();
                }
            }
        }

        $callsLogs = CallLogs::where('_id',$merchant_loc_id)->where('id_type','merchant_location_id')->whereNotNull('recording')->where('recording','!=','')->get(['id','_id','recording','purpose','created_at']);
        foreach ($callsLogs as $indexCall=>$callData){
            $data['files'][] = $callData;
        }

        $gstAuth = KarzaAPI::where('merchant_id', $merchant_loc_id)->where('type', 'merchant_location_id')->first(['id']);
        if(isset($gstAuth->id)){
            $data['gst_data_pulled'] = 1;
        }

        return view('admin.merchant.merchant_locations.edit', $data);
    }

    public function getEditPostMerchantLocation(EditMerchantLocationRequest $request, $merchant_id, $merchant_loc_id)
    {
        $data['sidebar'] = 'merchant';
        $mer_loc         = MerchantLocation::withTrashed()->where('id', $merchant_loc_id)->where('merchant_id', $merchant_id)->first();
        $mer_loc->fill($request->all());
        if ($request->has('status') && $request->get('status') == 'active'){
            $mer_loc->deleted_at = null;
        }
        $mer_loc->save();

        if($request->get('inform_rm_change')) {
            $this->informMerchantCenterOnRMChange($mer_loc->id);
        }

        if($request->has('documents') && count(array_diff($request->get('documents'),[null]))){
            (new ConsumerApplicationController())->uploadConsumerDocuments($request, $merchant_loc_id, 'merchant_location');
        }

        $reGenerateGST = $request->has('re_generate_gst_data') && $request->get('re_generate_gst_data') == 'on' ? true : false;

        if($mer_loc->gst_number && !empty($mer_loc->gst_number) && (empty($mer_loc->gst_data) || $reGenerateGST)){
            $this->getKarzaGSTData($mer_loc->gst_number,$mer_loc->id, 'merchant_location_id');
        }

        \Session::flash('success', 'Merchant edited successfully');
        return redirect('admin/merchant/location/' . $merchant_id);
    }

    public function destroyMerchantLocation($merchant_loc_id, $merchant_id)
    {
        $merchant_loc = MerchantLocation::where('id', $merchant_loc_id)->where('merchant_id', $merchant_id)->first();
        if ($merchant_loc) {
            $merchant_loc->delete();
            \Session::flash('success', 'Merchant Location successfully deleted');
            return redirect()->back();
        } else {
            \Session::flash('error', 'Merchant Location not deleted');
            return redirect()->back();
        }
    }

    public function getMerchants(){
        $merchants = Merchant::where('status','active')->where('is_guest_active', 1)->get();
        return response()->json(['merchants'=>$merchants],200);
    }

    public function createMerchantCategory(){
        $category = Input::get('category');
        $script = Input::get('script');
        $user_id = Input::get('user_id');


        $exists = MerchantCategory::where('name',trim($category))->first(['id']);

        if(!isset($category) && !empty($category)){
            $message = 'Fill the field(s)!';
        }
        elseif(isset($exists->id)){
            $message = '* Category exists! Please use a different one.';
        }else{
            $category_id = MerchantCategory::insertGetId(['name'=>trim($category),'status'=>1,'created_at' => Carbon::now()->toDateTimeString()]);
            MerchantCategoryScripts::insert([
                'category_id' => $category_id,
                'script_content' => $script,
                'user_id' => $user_id,
                'created_at' => Carbon::now()->toDateTimeString()
            ]);
            $message = '* Category '.$category.' successfully created!';
        }
        return response()->json(['status'=>'OK','message'=>$message],200);
    }

    public function getMerchantCategory(){
        $merchantCategoryScripts = MerchantCategoryScripts::where('category_id', Input::get('category_id'))->first(['script_content']);

        return response()->json(['status'=>'OK','script_content'=>isset($merchantCategoryScripts->script_content) ? $merchantCategoryScripts->script_content : ''],200);
    }

    public function editVerificationScript()
    {
        $category_id = Input::get('category');
        $updated_script = Input::get('script');
        $user_id = Input::get('user_id');
        $script_exist = MerchantCategoryScripts::where('category_id', $category_id)->first();

        if (empty($category_id) || empty($updated_script)) {
            $message = 'Fill both the fields!';
        } else {
            if (isset($script_exist->id)) {
                MerchantCategoryScriptsLogs::insert([
                    'script_id' => $script_exist->id,
                    'script_content' => $script_exist->script_content,
                    'user_id' => $script_exist->user_id,
                    'created_at' => Carbon::now()->toDateTimeString()
                ]);
                MerchantCategoryScripts::where('id', $script_exist->id)->update(['script_content' => $updated_script, 'user_id' => $user_id]);
                $message = "Verification Script updated successfully";
            } else {
                MerchantCategoryScripts::insert([
                    'category_id' => $category_id,
                    'script_content' => $updated_script,
                    'user_id' => $user_id
                ]);
                $message = "Script Added for the category";
            }
        }

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

    public function informMerchantCenterOnRMChange($id){

        $mer_loc = MerchantLocation::with('rmDetails','merchant')->where('id',$id)->first();

        $name = ucwords($mer_loc->rmDetails->name);
        $mobile_number = $mer_loc->rmDetails->mobile_number;
        $email = $mer_loc->rmDetails->email;
        $merchant_location_name = isset($mer_loc->name) ? 'Merchant Center:'.ucwords($mer_loc->name) : '';

        $message = "Dear ".$mer_loc->merchant->merchant_name.",<br/><br/>
Your respective merchant center Credit Fair's relationship manager has been changed, please find the contact details below to connect for any further problems.<br/><br/>
$merchant_location_name<br>
Name of Relationship Manager: $name<br/>
Mobile Number of Relationship Manager: $mobile_number<br/>
Email ID of Relationship Manager: $email<br/><br/>

Best,<br/>
Credit Fair - Business Development Team
";

        if($mer_loc->merchant->contact_number){
            $args = [$mer_loc->merchant->merchant_name, $name, $mobile_number, $email];
            sendWhatsAppMessage($mer_loc->merchant->contact_number, ['id' => $id, 'templateId' => Templates::$merchant_relation_manager_change, 'args' => $args]);
        }

        if($mer_loc->merchant->email) {
            $emails = explode(',', $mer_loc->merchant->email);
            Mail::send('emails.reminders.basic_reminder',
                ['body' => $message]
                , function ($message) use ($emails) {
                    $message->to($emails)->subject('Respective Merchant Center Relationship Manager Changed - Credit Fair');
                    $message->cc(['ops@creditfair.in','bd@creditfair.in']);
                });

            \Session::flash('success', 'A email has been sent to merchant about the relationship manager change in merchant center');
        }else{
            \Session::flash('error', 'No email specified in the merchant');
        }
    }

    public function informMerchantOnRMChange($id){

        $merchant = Merchant::with('rmDetails')->where('id',$id)->first();

        $name = ucwords($merchant->rmDetails->name);
        $mobile_number = $merchant->rmDetails->mobile_number;
        $email = $merchant->rmDetails->email;

        $message = "Dear ".$merchant->merchant_name.",<br/><br/>
Your Credit Fair's relationship manager has been changed, please find the contact details below to connect for any further problems.<br/><br/>
Name of Relationship Manager: $name<br/>
Mobile Number of Relationship Manager: $mobile_number<br/>
Email ID of Relationship Manager: $email<br/><br/>

Best,<br/>
Credit Fair - Business Development Team
";

        if($merchant->contact_number){
            $args = [$merchant->merchant_name, $name, $mobile_number, $email];
            sendWhatsAppMessage($merchant->contact_number, ['id' => $id, 'templateId' => Templates::$merchant_relation_manager_change, 'args' => $args]);
        }

        if($merchant->email) {
            $emails = explode(',', $merchant->email);
            Mail::send('emails.reminders.basic_reminder',
                ['body' => $message]
                , function ($message) use ($emails) {
                    $message->to($emails)->subject('Your Relationship Manager Changed - Credit Fair');
                    $message->cc(['ops@creditfair.in','bd@creditfair.in']);
                });

            \Session::flash('success', 'A email has been sent to merchant about the relationship manager change');
        }else{
            \Session::flash('error', 'No email specified in the merchant');
        }
    }

    public function offersDetails($id){

        $data['sidebar']  = 'merchant';
        $data['merchant'] = Merchant::find($id);
        $data['merchant_id'] = $id;
        $data['merchant_offers'] =  MerchantOffers::with('offerLog.user')->where('merchant_id',$id)->get(['id','merchant_id','interest_type_id', 'payment_frequency', 'tenure', 'advance_emi', 'interest_rate', 'subvention','processing_fee','processing_fee_type','advance_type','status','reason','disable_offer_status']);
        $data['interest_types'] = self::$interest_types;
        return view('admin.merchant.offers.index', $data);
    }

    public function getOffersDetails($merchant_id){

        $data['sidebar']  = 'merchant';
        $data['merchant_id'] = $merchant_id;
        $data['interest_types'] = static::$interest_types;
        $data['subvention_types'] = static::$subvention_types;
        $data['merchant'] = Merchant::where('id',$merchant_id)->with('merchant_category_many')->first(['merchant_name','category_id','id']);
        $data['disbursement_offers'] = DisbursementOffersData::getOffers(0);
        return view('admin.merchant.offers.edit', $data);
    }

    public function getOffersByMerchant(){

        $disabledOffers = [];
        if(Input::has('product_id') && Input::get('product_id') && (is_array(Input::get('product_id')) && is_numeric(Input::get('product_id')[0]))){
            $products = ProductOffers::whereIn('product_id', Input::get('product_id'));
                if(Input::has('application_id') && Input::get('application_id')){
                    $log = StatusLogs::where('application_id',Input::get('application_id'))->whereNotIn('status',array('incomplete form','in-process','waiting for documents','waiting for decision','form completed','rejected','aborted'))->first(['id']);
                    if(isset($log->id)) {
                        $products = $products->withTrashed();
                    }
                }
                $products = $products->get();
            $ids = [];
            foreach ($products as $product){
                if(!$product->deleted_at || ($product->deleted_at && $product->offer_id == Input::get('offer_id'))){
                    $ids[] = $product->offer_id;
                }
                if($product->deleted_at){
                    $disabledOffers[] = $product->offer_id;
                }
            }
        }

        if(isset($ids) && $ids){
            $merchant_offers = MerchantOffers::with('disbursementOffersData')->whereIn('id', $ids);
        }else{
            $merchant_offers = MerchantOffers::with('disbursementOffersData')->where('merchant_id',Input::get('merchant_id'));
        }
        $merchant_offers = $merchant_offers->where('category_id', Input::get('category_id'))->orderBy('tenure', 'ASC');
        $log = StatusLogs::where('application_id',Input::get('application_id'))->whereIn('status',array('disbursed','nach registered', 'closed'))->where('is_reversed',0)->first(['id']);
        if(!Input::has('application_id') || Input::get('application_id') == "0" || (!isset($log->id) && Input::has('application_id'))) {
//            $merchant_offers = $merchant_offers->withTrashed();
//        } else{
            $merchant_offers = $merchant_offers->where('status',1);
        }
        $merchant_offers = $merchant_offers->get(['id', 'merchant_id', 'tenure', 'advance_emi', 'interest_rate', 'subvention', 'slug', 'advance_type', 'bnpl_repayment', 'subvention_type_id']);
        $products = Products::where('merchant_id', Input::get('merchant_id'));
        if(Input::has('application_id') && Input::get('application_id') && isset($log->id)){
            $products = $products->withTrashed();
        }else{
            $products = $products->whereHas('productOffer');
        }
        $products = $products->get();
        return response()->json(['merchants' => $merchant_offers, 'products' => isset($products) ? $products : [], 'disabledOffers' => $disabledOffers],200);
    }

    public function getOffersByMerchantV1()
    {
        $disabledOffers = [];
        $offerLoanMapping = [];

        if (Input::has('product_id') && Input::get('product_id') && (is_array(Input::get('product_id')) && is_numeric(Input::get('product_id')[0]))) {
            $products = ProductOffers::whereIn('product_id', Input::get('product_id'));

            if (Input::has('application_id') && Input::get('application_id')) {
                $log = StatusLogs::where('application_id', Input::get('application_id'))
                    ->whereNotIn('status', ['incomplete form', 'in-process', 'waiting for documents', 'waiting for decision', 'form completed', 'rejected', 'aborted'])
                    ->first(['id']);
                if (isset($log->id)) {
                    $products = $products->withTrashed();
                }
            }

            $products = $products->get();
            // product_offers can hold multiple rows for the same offer_id (an offer removed and
            // mapped again), so only the latest row per offer_id decides if it is disabled.
            $latestProductOffers = $products->sortByDesc('id')->unique('offer_id');
            $ids = [];
            foreach ($latestProductOffers as $product) {
                if (!$product->deleted_at || ($product->deleted_at && $product->offer_id == Input::get('offer_id'))) {
                    $ids[] = $product->offer_id;
                }
                if ($product->deleted_at) {
                    $disabledOffers[] = $product->offer_id;
                }
            }
        }

        if (isset($ids) && $ids) {
            $merchant_offers = MerchantOffers::with('disbursementOffersData')->whereIn('id', $ids);
        } else {
            $merchant_offers = MerchantOffers::with('disbursementOffersData')->where('merchant_id', Input::get('merchant_id'));
        }

        $merchant_offers = $merchant_offers->where('category_id', Input::get('category_id'))->orderBy('tenure', 'ASC');

        $log = StatusLogs::where('application_id', Input::get('application_id'))
            ->whereIn('status', ['disbursed', 'nach registered', 'closed'])
            ->where('is_reversed', 0)
            ->first(['id']);

        if (!Input::has('application_id') || Input::get('application_id') == "0" || (!isset($log->id) && Input::has('application_id'))) {
            $merchant_offers = $merchant_offers->where('status', 1);
        }

        $merchant_offers = $merchant_offers->get(['id', 'merchant_id', 'tenure', 'advance_emi', 'interest_rate', 'subvention', 'slug', 'advance_type', 'bnpl_repayment', 'subvention_type_id', 'disable_offer_status']);

        // Fetch products
        $products = Products::where('merchant_id', Input::get('merchant_id'));

        if (Input::has('application_id') && Input::get('application_id') && isset($log->id)) {
            $products = $products->withTrashed();
        } else {
            $products = $products->whereHas('productOffer');
        }

        $products = $products->get();

        if (Input::has('application_id') && Input::get('application_id') != "0") {
            $applicationId = (int) Input::get('application_id');

            // Fetch related data
            $tofOffers = DecisionTable::with('eligibleOffers')
                ->where('application_id', $applicationId)
                ->whereNull('deleted_at')
                ->get();

            $previousOffer = ConsumerApplicationElement::where('application_id', $applicationId)
                ->value('previous_applied_offer');

            $currentOfferId = ConsumerApplication::where('id', $applicationId)
                ->value('offer_id');

            $emiCalculation = ApplicationEmiCalculation::where('application_id', $applicationId)
                ->first(['table_of_offers_logic', 'residual_emi']);

            $residualFoirLogicApplied = isset($emiCalculation->table_of_offers_logic) && (in_array($emiCalculation->table_of_offers_logic, ['residual_emi', 'bureau_surrogate_limit', 'power_saving', 'banking_abb']));
            $emiDetails = [];

            if (isset($emiCalculation->table_of_offers_logic) && $emiCalculation->table_of_offers_logic === 'residual_emi') {
                $grade = new GradeController(new Request());
                $emiDetails = $grade->getNextBasedAmountOffer($applicationId);
            } elseif (isset($emiCalculation->table_of_offers_logic) && in_array($emiCalculation->table_of_offers_logic, ['power_saving', 'banking_abb'])) {
                $grade = new GradeController(new Request());
                $type = $emiCalculation->table_of_offers_logic === 'power_saving' ? 'POWER-SAVING' : 'BANKING-ABB';
                $emiDetails = $grade->getOffersFromLoanDecisionApprovedAmount($applicationId, $type);
            } elseif (isset($emiCalculation->table_of_offers_logic) && $emiCalculation->table_of_offers_logic === 'bureau_surrogate_limit') {
                $grade = new GradeController(new Request());
                $emiDetails = $grade->getBureauSurrogateDetails($applicationId);
            }

            $eligibleOfferIds = [];
            $eligibleOfferIdsOnFoir = [];
            $onlyApplied = false;

            // Include current offer if present and valid
            if (!empty($currentOfferId) && $merchant_offers->contains('id', $currentOfferId)) {
                $eligibleOfferIds[] = $currentOfferId;
                $onlyApplied = true;
            }

            // Include previous offer if valid
            if (!empty($previousOffer) && $merchant_offers->contains('id', $previousOffer)) {
                $eligibleOfferIds[] = $previousOffer;
            }

            // Include eligible offers from TOF
            foreach ($tofOffers as $offer) {
                $eligible = $offer->eligibleOffers;
                if ($offer->status == 0 && !empty($eligible) && $merchant_offers->contains('id', $eligible->id)) {
                    $eligibleOfferIds[] = $eligible->id;
                    $onlyApplied = false;
                }
            }

            $eligibleOfferIds = array_unique($eligibleOfferIds);

            // Residual FOIR logic override
            if ($residualFoirLogicApplied && !empty($emiDetails) && ((!empty($eligibleOfferIds) && $onlyApplied) || empty($eligibleOfferIds))
            ) {
                foreach ($emiDetails as $emiDetail) {
                    if ($merchant_offers->contains('id', $emiDetail->id)) {
                        $offerLoanMapping[$emiDetail->id]['loan_amount'] = $emiDetail->loan_amount;
                        $eligibleOfferIdsOnFoir[] = $emiDetail->id;
                    }
                }
            }

            // Final merchant offers list
            if (!empty($eligibleOfferIds) && !$onlyApplied) {
                $merchant_offers = $merchant_offers->whereIn('id', $eligibleOfferIds)->values();
            } elseif (!empty($eligibleOfferIdsOnFoir)) {
                $merchant_offers = $merchant_offers->whereIn('id', $eligibleOfferIdsOnFoir)->values();
            }
        }

        if (Input::has('application_id') && Input::get('application_id') != "0") {
            $applicationId = (int) Input::get('application_id');
            $appliedOfferId = ConsumerApplication::where('id', $applicationId)->value('offer_id');

            $appliedOfferPresent = $appliedOfferId && $merchant_offers->contains('id', $appliedOfferId);

            // Keep only active offers
            $merchant_offers = $merchant_offers->where('disable_offer_status', 1)->values();

            // If applied offer exists but not in the filtered list, fetch and append it
            if ($appliedOfferId && !$merchant_offers->contains('id', $appliedOfferId) && $appliedOfferPresent) {
                $foundOffer = MerchantOffers::with('disbursementOffersData')->find($appliedOfferId);
                if ($foundOffer) {
                    $merchant_offers->push($foundOffer);
                }
            }

        } else {
            // For other cases, show offers with disable_offer_status = 0
            $merchant_offers = $merchant_offers->where('disable_offer_status', 1)->values();
        }


         return response()->json([
            'merchants' => $merchant_offers,
            'products' => $products,
            'disabledOffers' => $disabledOffers,
            'previous_applied_offer' => $previousOffer ?? 0,
            'offer_loan_mapping' => $offerLoanMapping
        ], 200);

    }

    public function getMailByMerchantCategory(){
        $mailCategoryList = MerchantCategoryCommonMail::where('id',Input::get('merchant_category_mail_id'))->whereNull('deleted_at')->first(['merchant_category_list']);
        $categoryList = explode(',',$mailCategoryList->merchant_category_list);
        return response()->json(['categoryMailList' => $categoryList],200);
    }

    public function editOffersDetails($id){
        $data['sidebar']  = 'merchant';
        $offer = MerchantOffers::find($id);

        $data['merchant'] = Merchant::where('id',$offer->merchant_id)->with('merchant_category_many')->first(['merchant_name','category_id','avg_ticket_size']);
        if(!isset($offer->monthly_irr) || empty($offer->monthly_irr)){
            $irr_data = $this->calculateIRR($offer,$data['merchant']->avg_ticket_size);
            $offer->monthly_irr = isset($irr_data['irr']) ? round($irr_data['irr'],2) : 0;
            $offer->annualised_irr = isset($irr_data['annualised']) ? round($irr_data['annualised'],2) : 0;
        }
        $data['offer'] = $offer;
        $data['interest_types'] = static::$interest_types;
        $data['subvention_types'] = static::$subvention_types;
        $data['disbursement_offers'] = DisbursementOffersData::getOffers($id);
        return view('admin.merchant.offers.edit', $data);
    }

    public function lockOffer($id){
        $newOffer = Input::all();
        if(isset($newOffer['processing_fee_first_emi'])){
            $newOffer['processing_fee_first_emi'] = 1;
        }else{
            $newOffer['processing_fee_first_emi'] = 0;
        }

        $offer = MerchantOffers::where('id',$id)->first()->toArray();
        $locked = false;
        unset($newOffer['_token']);
        unset($newOffer['status']);
        unset($newOffer['is_already_active']);
        foreach ($offer as $key=>$item){
            if(isset($newOffer[$key]) && $newOffer[$key] != (string)$item && $key != "disable_offer_status"){
                $merchantOffersLock = new MerchantOffersLock();
                $merchantOffersLock->fill($offer);
                $merchantOffersLock->status = 0;
                $merchantOffersLock->offer_id = $id;
                $merchantOffersLock->merchant_id = $offer['merchant_id'];
                $merchantOffersLock->is_already_active = 0;
                $merchantOffersLock->created_at = Carbon::now()->toDateTimeString();
                $merchantOffersLock->archived_by = Auth::user()->id;
                $merchantOffersLock->save();
                $locked = true;
                break;
            }
        }

        return $locked;
    }

    public function downloadMerchantExcel(){
        $data = Merchant::with('bank_details')->where('status','active')->where('is_guest_active', 1)->where('pay_data_sharing', 1)->get();

        $response[] = ['Payment Type', 'Cust Ref Number', 'Source Account Number', 'Source Narration', 'Destination Account Number', 'Currency', 'Amount', 'Destination Narration',	'Destination bank',	'Destination Bank IFS Code', 'Beneficiary Name', 'Beneficiary Account Type', 'Email'];
        foreach($data as $item) {
            if($item->bank_details) {
                $response [] = [
                    'type' => 'NFT',
                    'cust_ref_number' => 1,
                    'account_number' => '\'409000977396',
                    'narration' => 'To NEFT',
                    'des_account' => '\''.$item->bank_details->account_number,
                    'currency' => 'INR',
                    'amount' => '1',
                    'destination_narration' => 'From KMGLOBAL CREDIT',
                    'destination_bank' => $item->bank_details->bank_name,
                    'ifsc_code' => $item->bank_details->ifsc_code,
                    'name' => $item->bank_details->account_holder,
                    'account_type' => 'Current',
                    'email' => 'adityadamani@gmail.com',
                ];
            }
        }
        if(count($response) > 1) {
            $request = new Request(['data' => $response, 'sheet' => 'Sheet1']);
            return Excel::download((new Exporter($request)), 'Merchant_1Rs_Transfer_Sheet.xls');
        }else{
            \Session::flash('error', 'No data found');
            return redirect()->back();
        }
    }

    public function updateOffersDetails($id){
        $data['sidebar']  = 'merchant';
        $offer = MerchantOffers::find($id);
        $created_by = 0;
        if($this->lockOffer($id)){
            $created_by = Auth::user()->id;
        }
        $offerData = Input::all();

        // $user = User::where('id', Auth::user()->id)->first(['name']);
        if(Input::has('status') && Input::get('status') == 1 && $offer->status == 0){
            MerchantLogs::insert([
                'merchant_id' => $offer->merchant_id,
                'user_id' => Auth::user()->id,
                'offer_id' => $id,
                'data' => 'offer activated by '.Auth::user()->name,
                'created_at' => Carbon::now()->toDateTimeString()
            ]);
        }elseif(Input::has('status') && Input::get('status') == 0 && $offer->status == 1){
            MerchantLogs::insert([
                'merchant_id' => $offer->merchant_id,
                'user_id' => Auth::user()->id,
                'offer_id' => $id,
                'data' => 'offer deactivated '.Auth::user()->name,
                'created_at' => Carbon::now()->toDateTimeString()
            ]);
        }

        if ($offer->payment_frequency != $offerData['payment_frequency']){
            $created_by = Auth::user()->id;
        }

        if ($offer->disbursement_method != $offerData['disbursement_method']){
            $created_by = Auth::user()->id;
        }

        $offer->fill($offerData);

        if (Input::get('disbursement_at') == 'others' && Input::get('disbursement_at_other')){
            $offer->disbursement_at = Input::get('disbursement_at_other');
        }

        $merchant = Merchant::where('id',$offer->merchant_id)->first(['avg_ticket_size','status']);
        $irr_data = $this->calculateIRR($offer,$merchant->avg_ticket_size);
        $offer->monthly_irr = isset($irr_data['irr']) ? round($irr_data['irr'],2) : 0;
        $offer->annualised_irr = isset($irr_data['annualised']) ? round($irr_data['annualised'],2) : 0;

        if(Input::get('status') == 1 && !Input::has('is_already_active')){
            $offer->checked_by = Auth::user()->id;
        }else{
            $offer->checked_by = 0;
        }

        if(Input::has('processing_fee_first_emi')){
            $offer->processing_fee_first_emi = 1;
        }else{
            $offer->processing_fee_first_emi = 0;
        }
        $offer->disable_offer_status = Input::has('disable_offer_status') ? 1 : 0;
        $disbursementOffer = new DisbursementOffersData();
        $disbursementOfferStatus = $disbursementOffer->saveOffers($offerData);

        if($created_by || $disbursementOfferStatus){
            if ($disbursementOfferStatus){
                $created_by = Auth::user()->id;
            }
            $offer->created_by = $created_by;
            $offer->checked_by = 0;
            $offer->status = 0;
        }

        $offer->save();


        $existingOffers = MerchantOffers::where('merchant_id',$offer->merchant_id)->where('status',1)->whereNull('deleted_at')->first(['id']);
        if (!isset($existingOffers->id) && $merchant->status == 'active'){
            Merchant::where('id',$offer->merchant_id)->update(['status' => 'inactive']);
            // $user = User::where('id', Auth::user()->id)->first(['name']);
            MerchantLogs::insert([
                'merchant_id' => $offer->merchant_id,
                'user_id' => Auth::user()->id,
                'data' => 'merchant deactivated '.Auth::user()->name,
                'created_at' => Carbon::now()->toDateTimeString()

            ]);
        }

        \Session::flash('success', 'Offer updated successfully');
        return redirect()->to('/admin/merchant/offers/'.$offer->merchant_id);
    }

    public function deleteOffersDetails($id){
        $offer = MerchantOffers::find($id);
        if ($offer->delete()) {
            Session::flash('success', 'Offer successfully deleted');
            return redirect()->back();
        } else {
            Session::flash('error', 'Something went wrong');
            return redirect()->back();
        }
    }

    public function addOffersDetails(MerchantOffersRequest $request){
        $request->all();
        $offers = new MerchantOffers();
        $offerData = $request->all();
        $offers->fill($request->all());
        $merchant = Merchant::where('id',$offers->merchant_id)->first(['avg_ticket_size']);
        $irr_data = $this->calculateIRR($offers,$merchant->avg_ticket_size);
        $offers->monthly_irr = isset($irr_data['irr']) ? round($irr_data['irr'],2) : 0;
        $offers->annualised_irr = isset($irr_data['annualised']) ? round($irr_data['annualised'],2) : 0;
        $offers->created_by = Auth::user()->id;
        $offers->checked_by = 0;
        $offers->status = 0;
        if(Input::has('processing_fee_first_emi')){
            $offers->processing_fee_first_emi = 1;
        }else{
            $offers->processing_fee_first_emi = 0;
        }
        $offers->save();

        // $user = User::where('id', Auth::user()->id)->first(['name']);
        MerchantLogs::insert([
            'merchant_id' => $offers->merchant_id,
            'user_id' => Auth::user()->id,
            'offer_id' => $offers->id,
            'data' => 'offer added by '.Auth::user()->name,
            'created_at' => Carbon::now()->toDateTimeString()

        ]);

        $offerData['offer_id'] = $offers->id;
        $disbursementOffer = new DisbursementOffersData();
        $disbursementOffer->saveOffers($offerData);

        \Session::flash('success', 'Merchant offers added');
        return redirect()->to('/admin/merchant/offers/'.$request->get('merchant_id'));
    }

    public function getCategoriesByMerchant(){
        $id = Input::get('merchant_id');
        $categories = Merchant::with(['merchant_category_many' => function($query){
            $query->select('id', 'name');
        }])->get(['category_id']);
        return response()->json($categories,200);
    }

    public function storeBankDetailsCenter($id){
        $bankDetails = MerchantBankCenter::where('location_id',$id)->first(['id']);
        if(isset($bankDetails->id)){
            $bankDetails->fill(Input::all());

            if(Input::has('re_validate') && Input::get('re_validate') == 'on'){
                $request['consent'] = "Y";
                $request['ifsc'] = Input::get('ifsc_code');
                $request['accountNumber'] =  Input::get('account_number');
                $request = new Request(['data' => $request]);
                $validation_response = (new BankValidation($request))->get();
                if ((_env('KARZA_BANK_VERSION', '')) == '3') {

                    $validation_response1['result'] = isset($validation_response['result']['data']['source'][0]['data']) ? $validation_response['result']['data']['source'][0]['data'] : $validation_response;
                    $validation_response = $validation_response1;
                }
                $bankDetails->karza_response = json_encode($validation_response);
            }

            $bankDetails->save();

            \Session::flash('success', 'Bank details are updated!');
            return redirect()->to('admin/merchant/locations/add-bank-details/'.$id);
        }
        $bank = new MerchantBankCenter();
        $bank->fill(Input::all());
        //$bank->save();

        $request['consent'] = "Y";
        $request['ifsc'] = $bank->ifsc_code;
        $request['accountNumber'] = $bank->account_number;
        $request = new Request(['data' => $request]);
        $validation_response = (new BankValidation($request))->get();
        //$bank->karza_response = json_encode($validation_response);
        //$bank->save();
        if ((_env('KARZA_BANK_VERSION', '')) == '3') {

            $validation_response1['result'] = isset($validation_response['result']['data']['source'][0]['data']) ? $validation_response['result']['data']['source'][0]['data'] : $validation_response;
            $validation_response = $validation_response1;
        }
        if(isset($validation_response['result']['bankTxnStatus']) && $validation_response['result']['bankTxnStatus'] == true) {
            $bank->karza_response = json_encode($validation_response);
            $bank->save();
            \Session::flash('success', 'Bank details are added!');
            return redirect()->back();
        }else{
            $bank->karza_response = json_encode($validation_response);
            $bank->save();
            if(isset($validation_response['result'])) {
                \Session::flash('error', isset($validation_response['result']['bankResponse']) ? $validation_response['result']['bankResponse'] : 'Can\'t validate at this time.');
                return redirect()->back();
            }
            \Session::flash('error', 'Bank Validation Failed. Please check details again');
            return redirect()->back();
        }
        \Session::flash('success', 'Bank details are added!');
        return redirect()->to('admin/merchant/locations/add-bank-details/'.$id);

    }

    public function createBankDetailsCenter($id){
        $data['merchant'] = MerchantLocation::with('bank_details')->find($id);
        $data['sidebar']           = 'merchant';
        $data['location_id']       = $id;
        $data['merchants']         = Merchant::all();
        return view('admin.merchant.merchant_locations.bank.add',$data);
    }

    public function updateComment($id){
        $comment = new Comments();
        $comment->application_id = $id;
        $comment->user_id = 107;
        $comment->comment = "New Bank Details are updated, Please check the verification checklist and activate the merchant";
        $comment->type = 'merchant';
        $comment->show_merchant = 0;
        $comment->show_consumer = 0;
        $comment->save();

        Merchant::where('id', $id)->update(['pre_status' => 1]);
    }

    public function storeBankDetails($id){
        $merchant = Merchant::find($id);
        $bankDetails = MerchantBank::where('merchant_id',$id)->first();
        if(isset($bankDetails->id)){
            $merchantBankLog = new MerchantBankLogs();
            $merchantBankLog->fill($bankDetails->toArray());
            $merchantBankLog->merchant_id = $id;
            $merchantBankLog->account_number = wall_decrypt($bankDetails->account_number);
            $merchantBankLog->user_id = Auth::user()->id;
            $merchantBankLog->save();
            $bankDetails->fill(Input::all());

            if($merchant->status == 'active' && $merchant->pre_status == 0) {
                $this->updateComment($id);
            }


            if(Input::has('re_validate') && Input::get('re_validate') == 'on'){
                $request['consent'] = "Y";
                $request['ifsc'] = Input::get('ifsc_code');
                $request['accountNumber'] =  Input::get('account_number');
                $request = new Request(['data' => $request]);
                $validation_response = (new BankValidation($request))->get();
                if ((_env('KARZA_BANK_VERSION', '')) == '3') {

                    $validation_response1['result'] = isset($validation_response['result']['data']['source'][0]['data']) ? $validation_response['result']['data']['source'][0]['data'] : $validation_response;
                    $validation_response = $validation_response1;
                }
                $bankDetails->karza_response = json_encode($validation_response);
            }

            $bankDetails->save();

            \Session::flash('success', 'Bank details are updated!');
            return redirect()->to('admin/merchant/edit/'.$id);
        }
        $bank = new MerchantBank();
        $bank->fill(Input::all());
        // $user = User::where('id', Auth::user()->id)->first(['name']);
        MerchantLogs::insert([
            'merchant_id' => $id,
            'user_id' => Auth::user()->id,
            'data' => 'bank added by'.Auth::user()->name,
            'created_at' => Carbon::now()->toDateTimeString()

        ]);
        //$bank->save();

        $request['consent'] = "Y";
        $request['ifsc'] = $bank->ifsc_code;
        $request['accountNumber'] = $bank->account_number;
        $request = new Request(['data' => $request]);
        $validation_response = (new BankValidation($request))->get();
        //$bank->karza_response = json_encode($validation_response);
        //$bank->save();
        if ((_env('KARZA_BANK_VERSION', '')) == '3') {

            $validation_response1['result'] = isset($validation_response['result']['data']['source'][0]['data']) ? $validation_response['result']['data']['source'][0]['data'] : $validation_response;
            $validation_response = $validation_response1;
        }
        if(isset($validation_response['result']['bankTxnStatus']) && $validation_response['result']['bankTxnStatus'] == true) {
            $bank->karza_response = json_encode($validation_response);
            $bank->save();
            \Session::flash('success', 'Bank details are added!');
            return redirect()->back();
        }else{
            $bank->karza_response = json_encode($validation_response);
            $bank->save();
            if(isset($validation_response['result'])) {
                \Session::flash('error', isset($validation_response['result']['bankResponse']) ? $validation_response['result']['bankResponse'] : 'Can\'t validate at this time.');
                return redirect()->back();
            }
            \Session::flash('error', 'Bank Validation Failed. Please check details again');
            return redirect()->back();
        }
        \Session::flash('success', 'Bank details are added!');
        return redirect()->to('admin/merchant/edit/'.$id);

    }

    public function createBankDetails($id){
        $data['merchant'] = Merchant::with('bank_details')->find($id);
        $data['merchantBankLogs'] = MerchantBankLogs::with('user')->where('merchant_id', $id)->get();
        $data['sidebar']           = 'merchant';
        $data['merchant_id']       = $id;
        $data['merchants']         = Merchant::all();
        $data['merchant_location'] = MerchantLocation::all();
        return view('admin.merchant.bank.add',$data);
    }

    public function getCategories(){
        $id = Input::get('merchant_id');
        $data = Merchant::with('merchant_category_many','centers')
            ->where('id',$id)->first(['id','category_id']);

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

    public function getCategoriesforMultipleMerchants(){
        $_merchant_id = (null != Input::get('_merchant_id')) ? Input::get('_merchant_id') : [];
        $data = Merchant::with(['merchant_category_many', 'centers'])
            ->whereIn('id', $_merchant_id)->get(['id', 'category_id']);
        return response()->json(['status'=>'OK','data'=>$data],200);
    }

    public function moduleStatusChange(Request $request){

        if ($request->has('status') && $request->has('module_id')){
            $module = MerchantModule::find($request->get('module_id'));
            $module->status = $request->get('status');
            $module->save();
            // $user = User::where('id', Auth::user()->id)->first(['name']);
            if($module->status == 1){
                MerchantLogs::insert([
                    'merchant_id' => $module->merchant_id,
                    'user_id' => Auth::user()->id,
                    'merchant_module_id' => $module->id,
                    'data' => 'module activated by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()

                ]);
            }elseif($module->status == 0){
                MerchantLogs::insert([
                    'merchant_id' => $module->merchant_id,
                    'user_id' => Auth::user()->id,
                    'merchant_module_id' => $module->id,
                    'data' => 'module deactivated by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()

                ]);

            }
            return response()->json(['status' => 'OK','message' => 'Status changed successfully']);
        }

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

    public function sendAgreement($id){

        $loan_agreement = "";
        $subject = "";
        try {
            $data = Merchant::with('offer','module','rmDetails')->where('id', $id)->first();
            $lender = Lenders::where('id', 2)->first();

            $hashids = new Hashids('Creditfair by CS', 20);
            $data['esignlink'] = asset("merchant/esign/id/".$hashids->encode($data['id']));
            $blade = '';
            $fileName = '';
            if((Input::get('type') == 'Merchant Agreement') || (Input::get('type') == 'Merchant ReAgreement')){
                if($data->category_id!=6){
                    $blade = 'merchant_agreement_v1';
                }else{
                    $blade = 'merchant_agreement_solar';
                }

                $fileName = 'Merchant Agreement.pdf';
            }

            if(Input::get('type') == 'Merchant ReAgreement'){
                DigitalSignDetails::where('application_id', $id)->where('type','merchant')->delete();
                MerchantFiles::where('merchant_id',$id)->whereNull('type')->whereIn('file_type',['merchant_cam_image','merchant_agreement_esign'])->delete();
            }

            if(Input::get('type') == 'Merchant Agreement' || Input::get('type') == 'Merchant ReAgreement') {
                $pdf = App::make('snappy.pdf.wrapper');
                $pdf->loadView('admin.documents.' . $blade, ['merchant' => $data, 'pdf' => 1, 'signed_data' => "", 'lender' => $lender])->setOption('encoding', 'UTF-8')->setPaper('a4')->setOrientation('portrait');
                $header = '<HTML
                    <!DOCTYPE html>
                    <html>
                    <head>
                    <style type="text/css">
                        body { margin: 0;}
                    </style>
                    </head>
                    <body style="float:right">
                    <img src="' . $lender->logo . '" style="width: 57px;float: right;">
                    </body>
                    </html>';
                $pdf->setOption('header-html',$header);
                $pdf->setOption('margin-top', 20);
                $pdf->setOption("footer-right", "Page [page] from [topage]");
                $pdf->setOption('footer-font-size', 10);
                $pdf->setOption('margin-bottom', 30);

                $loan_agreement = $pdf->setOption('footer-html', view('admin.editor.footer', ['type' => "Merchant Agreement", 'lender' => $lender])->render());
            }
            $email = explode(',', str_replace(' ','',$data->email));
            if(Input::get('type') == 'Merchant Agreement' || Input::get('type') == 'Merchant ReAgreement'){
                $bodyblade = 'merchant_agreement_email';
                $subject = 'Merchant Agreement';
            }else if(Input::get('type') == 'Welcome Letter'){
                $bodyblade = 'welcome_email';
                $subject = 'Welcome Letter';
            }
            $body = View('admin.documents.' . $bodyblade, ['merchant' => $data, 'pdf' => 1, 'signed_data' => "", 'lender' => $lender])->render();
            //$subject = 'test';
            $mailData = ['fileName' => $fileName, 'pdf' => $loan_agreement, 'email' => $email, 'subject' => $subject];


            $cc_emails = ['bd@creditfair.in'];
            if($data->sales_head_email){
                array_push($cc_emails, $data->sales_head_email);
            }
            if($data->rcp_email){
                array_push($cc_emails, $data->rcp_email);
            }
            if($data->finance_head_email){
                array_push($cc_emails, $data->finance_head_email);
            }


            $send_attachment = Input::get('send_attachment') ? 1 : 0;

            Mail::send('emails.blank', [
                'body' => $body,
            ], function ($message) use ($mailData, $cc_emails, $send_attachment) {
                $message->to($mailData['email'])->subject($mailData['subject']);
                $message->cc($cc_emails);
                if(Input::get('type') == 'Merchant Agreement' || Input::get('type') == 'Merchant ReAgreement') {
                    $message->attachData($mailData['pdf']->output(), $mailData['fileName']);
                }
                if($send_attachment){
                    $message->attach(storage_path() . '/CREDIT FAIR EMI PROCESS.pdf', ['as' => 'CREDIT FAIR EMI PROCESS']);
                }
            });
            if(Input::get('type') == 'Welcome Letter') {
                Merchant::where('id', $id)->update(['welcome_letter_sent' => 1]);
            }
            $response = ['message' => ucwords(str_replace('_',' ',$blade)).' sent successfully', 'status' => 'OK'];
            // $user = User::where('id', Auth::user()->id)->first(['name']);
            if(Input::get('type') == 'Merchant Agreement'){
                MerchantLogs::insert([
                    'merchant_id' => $id,
                    'user_id' => Auth::user()->id,
                    'data' => 'merchant agreement sent by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()

                ]);
            }elseif(Input::get('type') == 'Merchant ReAgreement') {
                MerchantLogs::insert([
                    'merchant_id' => $id,
                    'user_id' => Auth::user()->id,
                    'data' => 'merchant agreement for resigning sent by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()
                ]);
            }elseif(Input::get('type') == 'Welcome Letter'){
                MerchantLogs::insert([
                    'merchant_id' => $id,
                    'user_id' => Auth::user()->id,
                    'data' => 'merchant Welcome sent by '.Auth::user()->name,
                    'created_at' => Carbon::now()->toDateTimeString()
                ]);
            }
        }catch (\Exception $e){
            $response = ['message' => 'Something went wrong', 'status' => 'FAIL', 'error'=>$e->getMessage(),'line'=>$e->getLine(),'trace'=>$e->getTraceAsString(), 'file' => $e->getFile()];
            (new Slack(new Request(['title'=>'Merchant Agreement Error','fields'=>['error'=>$e->getMessage(),'line'=>$e->getLine(),'trace'=>$e->getTraceAsString()], 'group' => Slack::$STAGE_TESTING])))->send();
        }

        return response()->json($response,200);


    }

    public function calculateIRR($offer,$avg_ticket_size,$data = null,$include_processing_fees = true){
        if($data == null) {
            if (empty($avg_ticket_size)) {
                $avg_ticket_size = 50000;
            }

            $charge_subvention_remaining_amount = 0;

            if($offer->merchant_id){
                $merchant = Merchant::where('id', $offer->merchant_id)->first(['charge_subvention_remaining_amount','processing_fee_on_advance_amount']);
                if (isset($merchant->charge_subvention_remaining_amount)){
                    $charge_subvention_remaining_amount = 1;
                }
            }
            $processingfeeOnRemainingAmount = $merchant->processing_fee_on_advance_amount ?? 0;


            $emiCalculation = new \App\EMICalculation();
            $emiCalculation
                ->setLoanAmount((float)$avg_ticket_size)
                ->setEmi((float)0)
                ->setTenure($offer->tenure)
                ->setSlug($offer->slug)
                ->setAdvanceEMI($offer->advance_emi)
                ->setInterestRate($offer->interest_rate)
                ->setSubvention($offer->subvention)
                ->setChargeSubventionRemainingAmount($charge_subvention_remaining_amount)
                ->setSubventionType(MerchantController::$subvention_types[$offer->subvention_type_id])
                ->setAdvanceType($offer->advance_type)
                ->setProcessingFeeType($offer->processing_fee_type)
                ->setProcessingFeeCollected($offer->processing_fee_collected)
                ->setProcessingFee($offer->processing_fee)
                ->setProcessingFeesOnAdvanceAmount($processingfeeOnRemainingAmount,$offer->processing_fee)
                // ->setProcessingFeesOnAdvanceAmount($offer->processing_fee_on_advance_amount)
                ->setInterestType($offer->interest_type_id)
                ->setApplicationId(0);
            $data = $emiCalculation->calculateEMI();
        }
        $processfess = 0;
        if($include_processing_fees == true) {
            if (!empty($data['processing_fee']) && $data['processing_fee'] > 0) {
                $gstDetails = (new ConsumerDocument(new Request()))->getGSTDetails(
                    [
                        'amount' => $data['processing_fee'],
                        'state' => ''
                    ]
                );
                $processfess = isset($gstDetails['0']['net_amount']) ? $gstDetails['0']['net_amount'] : 0;
            }
        }
        $emi_irr_array = array();
        $emi = $data['emi'];
        $period = (isset($data['emi_remaining'])? $data['emi_remaining'] : $data['remaining_emi']) -1;
        $subvention_amount = (isset($data['subvention']) && $data['subvention'] > 0) ?(($avg_ticket_size* ($data['subvention']))/100) :0;
        $outflow = $avg_ticket_size - $subvention_amount;
        //$outflow = (isset($data['subvention']) && $data['subvention'] > 0) ? ($avg_ticket_size - (($avg_ticket_size* ((int)$data['subvention']))/100)) : $avg_ticket_size;
        $amount_disbursed = $outflow - ((isset($data['up_front_from_customer']) ? ($data['up_front_from_customer'] + $processfess): ($data['upfront_from_customer']) + $processfess));
        if(!empty($emi) && !empty($period) && !empty($amount_disbursed)){

            array_push($emi_irr_array, -($amount_disbursed));

            foreach ($data['emis'] as $emi) {
                array_push($emi_irr_array, $emi);
            }

            $irr_rate_new = (Finance::irr($emi_irr_array)) * 100;
            $irr_data = $this->IRR($amount_disbursed, $emi, $period);
            return ['irr'=>$irr_data['irr'],'annualised'=>round($irr_rate_new * 12, 2)];
            //return $irr_data;
        }else{
            return ['irr'=>0,'annualised'=>0];
        }
    }
    public function IRR($amount_disbursed, $emi, $period) {
        $precision = 0.000001;
        $min = 0;
        $max = 1;
        $net_present_value = 1;
        $guess = 0;
        while(abs($net_present_value - $amount_disbursed) > $precision) {
            $net_present_value = 0;
            $guess = ($min + $max) / 2;
            $i = 0;
            while ($i <= $period) {
                $net_present_value += $emi / (1 + $guess) ** ($i + 1);
                $i++;
            }
            if ($net_present_value - $amount_disbursed > 0) {
                $min = $guess;
            } else {
                $max = $guess;
            }
        }
        $irr = $guess * 100;
        $annualised = (pow((1+$guess),12)-1)*100;
        return ['irr'=>$irr,'annualised'=>$annualised];
    }

    public function getKarzaGSTData($gst_no,$merchant_id, $type = 'merchant_id'){
        if(!empty($gst_no)){
            $karza_key = (_env('KARZAKEY', ''));
            //$karza_key = "XVC3ByYPMj1gTS0O";
            $data = json_encode(array('gstin'=>$gst_no,'consent' => 'Y'));
            if(!empty($karza_key)){
                $curl = curl_init();
                curl_setopt_array($curl, array(
                    CURLOPT_URL => "https://gst.karza.in/prod/v1/gst-return-status",
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_ENCODING => "",
                    CURLOPT_MAXREDIRS => 10,
                    CURLOPT_TIMEOUT => 30,
                    CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
                    CURLOPT_CUSTOMREQUEST => "POST",
                    CURLOPT_POSTFIELDS => $data,
                    CURLOPT_HTTPHEADER => array(
                        "content-type: application/json",
                        "x-karza-key: ".$karza_key
                    ),
                ));

                $response = curl_exec($curl);
                $err = curl_error($curl);

                curl_close($curl);

                if ($err) {
                    if($type == 'merchant_location_id') {
                        $merchant = MerchantLocation::find($merchant_id);
                    }else{
                        $merchant = Merchant::find($merchant_id);
                    }
                    $merchant->gst_data = $response;
                    $merchant->save();
                    return json_encode(array('status'=>'failed','message'=>$err));

                } else {
                    $months_filled = 0;
                    $response_array = json_decode($response);
                    if(isset($response_array->result)){
                        if(isset($response_array->result->result)) {
                            foreach ($response_array->result->result as $years) {
                                if (isset($years->EFiledlist)) {
                                    foreach ($years->EFiledlist as $fillings) {
                                        $gst_data = new MerchantGstFillingData();
                                        $gst_data->merchant_id = $merchant_id;
                                        $gst_data->filing_frequency = isset($years->filing_frequency) ? $years->filing_frequency : "";
                                        $gst_data->financial_year = isset($years->financial_year) ? $years->financial_year : "";
                                        $gst_data->status = isset($fillings->status) ? $fillings->status : "";
                                        $gst_data->dof = isset($fillings->dof) ? $fillings->dof : "";
                                        $gst_data->ret_prd = isset($fillings->ret_prd) ? $fillings->ret_prd : "";
                                        $gst_data->is_delay = isset($fillings->is_delay) ? $fillings->is_delay : 0;
                                        $gst_data->valid = isset($fillings->valid) ? $fillings->valid : "";
                                        $gst_data->rtntype = isset($fillings->rtntype) ? $fillings->rtntype : "";
                                        $gst_data->mof = isset($fillings->mof) ? $fillings->mof : "";
                                        $gst_data->arn = isset($fillings->arn) ? $fillings->arn : "";
                                        $gst_data->type = $type;
                                        $gst_data->save();
                                        $months_filled = $months_filled + 1;
                                    }
                                }
                            }
                        }
                    }
                    if($type == 'merchant_location_id') {
                        $merchant = MerchantLocation::find($merchant_id);
                    }else{
                        $merchant = Merchant::find($merchant_id);
                    }
                    $merchant->gst_is_any_delay = (isset($response_array->result->compliance_status->is_any_delay) && !empty($response_array->result->compliance_status->is_any_delay)) ? $response_array->result->compliance_status->is_any_delay : false;
                    $merchant->gst_is_defaulter = (isset($response_array->result->compliance_status->is_defaulter) && !empty($response_array->result->compliance_status->is_defaulter)) ? $response_array->result->compliance_status->is_defaulter : false;
                    $merchant->months_gst_filled = $months_filled;
                    $merchant->gst_data = $response;
                    $merchant->save();
                    return json_encode(array('status'=>'success','message'=>'GST Data Saved'));

                }


            }

        }
        return json_encode(array('status'=>'failed','message'=>'Please check GST Number'));
    }

    public function getPanValidation($id)
    {

        $file_path = $id."/".$id."_response_pan_valid_merchant.json";
        $file = Equifax::readFile($file_path);
        if ($file !== false) {
            $report_data = json_decode($file, true);
            if(isset($report_data['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData']['sch:VerifyIDResponse']['sch:vidNsdlResponses']['sch:nsdlResponse'])){
                return $report_data['soapenv:Envelope']['soapenv:Body']['sch:InquiryResponse']['sch:ReportData']['sch:VerifyIDResponse']['sch:vidNsdlResponses']['sch:nsdlResponse'] ?? '';
            }else{
                return $report_data;
            }
        }
        return false;
    }

    public function getGSTDetails($merchant_id, Request $request){
        if($merchant_id) {
            $type = $request->has('type') ? $request->get('type') : 'merchant_id';
            $gstAuth = KarzaAPI::where('merchant_id', $merchant_id)->where('type', $type)->first(['id', 'data']);
            if (!isset($gstAuth->id)) {
                $gstData = $this->generateGSTAuthenticationReport($merchant_id);
                if ($gstData->getData()->status == 'OK') {
                    $gstAuth = KarzaAPI::where('merchant_id', $merchant_id)->where('type', $type)->first(['id', 'data']);
                } else {
                    return $gstData;
                }
            }
            $gstAuthData = isset($gstAuth->id) ? json_decode($gstAuth->data, 1) : '';
        }else{
            $gstData = $this->generateGSTAuthenticationReportWithMerchant($request);
            if ($gstData->getData()->status == 'FAIL') {
                return $gstData;
            }
            $gstAuthData = $gstData->getData()->status == 'OK' ? json_decode(json_encode($gstData->getData()->data),1) : '';
        }

        $stateName = '';
        $pincode = '';
        if ($gstAuthData){
            $gst = $request->has('gst') ? $request->get('gst') : ($request->has('data') ? $request->get('data')->gst : '');
            $code = substr($gst,0, 2);
            $stateName = StateModel::where('code_number',$code)->first(['name']);
            if (isset($stateName->name)){
                $stateName = $stateName->name;
            }


            if(isset($gstAuthData['result']['pradr']) && $gstAuthData['result']['pradr']['adr']){
                preg_match_all('!\d+!', strtolower(trim($gstAuthData['result']['pradr']['adr'])), $matches);
                if($matches){
                    foreach ($matches[0] as $match){
                        if(strlen($match) == 6) {
                            $pincode = $match;
                        }
                    }
                }

            }

        }

        return response()->json(['status' => 'OK', 'message' => 'Details fetched', 'data' => $gstAuthData, 'data_enc' => json_encode($gstAuthData), 'stateName' => $stateName, 'pincode' => $pincode]);
    }

    public function generateGSTAuthenticationReportWithMerchant(Request $request){
        $status = 'FAIL';
        $message = '';
        $gst = $request->has('gst') ? $request->get('gst') : ($request->has('data') ? $request->get('data')->gst : '');
        $karzaAPI = (new KarzaAPI())->getGSTData(0, $gst);
        if($karzaAPI && !isset($karzaAPI['error'])) {
            $status = 'OK';
            $message = 'GST authentication report generated successfully';
        }else if($karzaAPI['error']){
            $status = 'FAIL';
            $message = $karzaAPI['error'];
        }

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

    public function generateGSTAuthenticationReport($merchant_id){
        $status = 'FAIL';
        $message = 'Can\'t pull the gst authentication report';

        $type = Input::has('type') ? Input::get('type') : 'merchant_id';

        if($merchant_id){
            if($type == 'merchant_location_id') {
                $merchant = MerchantLocation::on('mysql_readonly')->where('id', $merchant_id)->first(['id', 'gst_number']);
            }else{
                $merchant = Merchant::on('mysql_readonly')->where('id', $merchant_id)->first(['id', 'gst_number']);
            }
            if((!isset($merchant->gst_number) && Input::get('gst')) || (isset($merchant->gst_number) && Input::get('gst') && $merchant->gst_number != Input::get('gst'))){
                if($type == 'merchant_location_id') {
                    MerchantLocation::where('id', $merchant_id)->update(['gst_number' => Input::get('gst')]);
                    $merchant = MerchantLocation::where('id', $merchant_id)->first(['id','gst_number']);
                }else{
                    Merchant::where('id', $merchant_id)->update(['gst_number' => Input::get('gst')]);
                    $merchant = Merchant::where('id', $merchant_id)->first(['id','gst_number']);
                }
            }
            if($merchant->gst_number && !empty($merchant->gst_number)){
                $karzaAPI = (new KarzaAPI())->getGSTData($merchant_id, ['gst_number' => $merchant->gst_number, 'type' => $type]);
                if($karzaAPI && !isset($karzaAPI['error'])) {
                    $status = 'OK';
                    $message = 'GST authentication report generated successfully';
                }else if($karzaAPI['error']){
                    $message = $karzaAPI['error'];
                }
            }else{
                $message = 'GST number not exist';
            }
        }

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

    public function getBankValidation($merchant){
        if(isset($merchant->bank_details) && isset($merchant->bank_details->karza_response)) {
            $bank_validation = json_decode($merchant->bank_details->karza_response);
            if (isset($bank_validation->result->bankResponse)) {
                return $bank_validation->result->bankTxnStatus;
            }
        }
        return false;
    }

    public function onboardingChecklist($id){
        $merchant = Merchant::with('digital_sign','bank_details','gst_authentication','products','products.productOffer')->where('id', $id)->first();

        $bankStatement = false;
        $itr = false;
        $signed_agreement = false;

        $docs = MerchantFiles::where('merchant_id', $id)->whereNull('type')->whereIn('file_type', ['bank_statement','itr'])->get(['file_type']);
        foreach ($docs as $doc){
            if($doc->file_type == 'bank_statement'){
                $bankStatement = true;
            }else if($doc->file_type == 'itr'){
                $itr = true;
            }else if($doc->file_type == 'signed_agreement'){
                $signed_agreement = true;
            }
        }

        $bank_validation_status = $this->getBankValidation($merchant);

        $panValidation = $this->getPanValidation($id);
        $applicationController = new ConsumerApplicationController();
        $applicationController->isInternal = true;
        $equifax = $applicationController->getCibilReport($id, 1, '_merchant');

        $dpd = false;
        $writtenOff = false;
        $score = 'No Equifax';

        if(!$equifax instanceof RedirectResponse && isset($equifax['report_data']['sch:ReportData'])) {
            $reportData = $equifax['report_data']['sch:ReportData'];
            $score = isset($reportData['sch:Score']) ? $reportData['sch:Score']['sch:Value'] : 'No Score';
            if (isset($reportData['sch:Score'])) {
                $summary = isset($reportData['sch:AccountSummary']) ? $reportData['sch:AccountSummary'] : [];
                $accountDetails = isset($reportData['sch:AccountDetails']) ? $reportData['sch:AccountDetails'] : [];

                $historyData = [];
                if($accountDetails) {
                    foreach ($accountDetails['sch:Account'] as $accountDetail) {
                        $return = false;
                        if (!is_array($accountDetail)) {
                            $accountDetail = $accountDetails['sch:Account'];
                            $return = true;
                        }

                        foreach ($accountDetail['sch:History48Months'] as $months) {

                            if(isset($months['@key'])) {
                                $date = explode('-', $months['@key']);
                                $date = date('Y-m-d', strtotime('01-' . $date[0] . '-20' . $date[1]));

                                if ($date >= Carbon::now()->subMonth(6)->toDateString()) {
                                    if (!in_array($months['sch:PaymentStatus'], ['*', '000', 'CLSD', 'STD', 'NEW'])) {
                                        $historyData[] = $months['sch:PaymentStatus'];
                                    }
                                }
                            }else{
                                foreach ($months as $history) {
                                    $date = explode('-', $history['@key']);
                                    $date = date('Y-m-d', strtotime('01-' . $date[0] . '-20' . $date[1]));

                                    if ($date >= Carbon::now()->subMonth(6)->toDateString()) {
                                        if (!in_array($history['sch:PaymentStatus'], ['*', '000', 'CLSD', 'STD', 'NEW'])) {
                                            $historyData[] = $history['sch:PaymentStatus'];
                                        }
                                    }
                                }
                            }
                        }

                        if ($return)
                            break;
                    }
                }

                if (count($historyData) > 0) {
                    $dpd = true;
                }

                if (isset($summary['sch:NoOfWriteOffs']) && $summary['sch:NoOfWriteOffs'] > 0) {
                    $writtenOff = true;
                }
            }
        }elseif(!$equifax instanceof RedirectResponse && isset($equifax['score']->BureauScore)) {
            $score = $equifax['score']->BureauScore ?? 'No Score';
        }

        $gst_last = false;
        if($merchant->months_gst_filled >= 6){
            $gst_last = true;
        }

        $gstFiling = false;
        if($merchant->gst_data){
            $gst_data = json_decode($merchant->gst_data);
            if(isset($gst_data->statusCode) && $gst_data->statusCode == 101){
                $gstFiling = true;
            }
        }

        $gstAuth = KarzaAPI::where('merchant_id', $merchant->id)->where('type','merchant_id')->first(['id','data']);
        $gst_verified = false;
        $trade_name = false;
        $addressMatch = false;
        $addressCompare = 0;
        $pincode = '';
        $address = $merchant->address.', '.$merchant->city.', '.$merchant->state.', '.$merchant->pincode;
        if(isset($gstAuth->id)){
            $gst_verified = true;
            $gstAuthData = json_decode($gstAuth->data, 1);
            if(isset($gstAuthData['result']['tradeNam']) && strtolower(trim($gstAuthData['result']['tradeNam'])) == strtolower(trim($merchant->merchant_name))){
                $trade_name = true;
            }
            if(isset($gstAuthData['result']['pradr']) && strtolower(trim($gstAuthData['result']['pradr']['adr'])) == strtolower(trim($address))){
                $addressMatch = true;
                similar_text(strtolower($address),strtolower(trim($gstAuthData['result']['pradr']['adr'])),$addressCompare);

                preg_match_all('!\d+!', strtolower(trim($gstAuthData['result']['pradr']['adr'])), $matches);
                if($matches){
                    foreach ($matches[0] as $match){
                        if(strlen($match) == 6)
                            $pincode = $match;
                    }
                }

            }

            if(!$pincode){
                if($merchant->pincode && isset($gstAuthData['result']['pradr']) && strpos(strtolower(trim($gstAuthData['result']['pradr']['adr'])), $merchant->pincode) !== false){
                    $pincode = $merchant->pincode;
                }
            }
        }

        $is_account_holder = MerchantBank::with('merchant')->where('account_holder',$merchant->merchant_name)->first(['id']);
        $is_account_holder = isset($is_account_holder->id) ? $is_account_holder->id : false;

        $karzaDetails = [
            'GST Verified'=> $gst_verified,
            'Trade name' => $trade_name,
            'Bank Validation' => $bank_validation_status,
            'GST filing' => $gstFiling,
            'GST last 6months' => $gst_last,
            'Bank account Holder'=> $is_account_holder
        ];

        $signing = [
            'Pan card' => $panValidation && (isset($panValidation['sch:PANStatus']) || isset($panValidation['kycStatus'])) && ($panValidation['sch:PANStatus'] == 'Success' || $panValidation['kycStatus'] == 'SUCCESS') ? true : false,
            'DPD in last 6 months' => $dpd,
            'Written off in the last 6 months' => $writtenOff,
        ];

        $addressVerified = [
            'Address Match with GST' => [
                'status' => $addressMatch,
                'percentageMatch' => '<code>(Percentage Match : '.$addressCompare.'%)</code>'
            ],
            'PIN Code Match' => $pincode == $merchant->pincode ? true : false,
            'Location serviceable by' => [
                'text' => [
                    'status' => true,
                    'value' => $merchant->location_serviceable
                ]
            ],
        ];

        $productsCount = Products::with('productOffer')->where('merchant_id', $id)->get();

        $productHasOffers = 0;
        foreach ($productsCount as $product){
            if(count($product->productOffer)){
                $productHasOffers++;
            }
        }

        $productVerified = [
            'No. of Product/ Services added' => $productsCount ? count($productsCount) : 0,
            'No. of Products linked with offers' => $productHasOffers,
        ];


        $data['fraud_report'] = (new ConsumerApplicationController())->isFraud('merchant/'.$id, 'fraud_report.json');

        $bankingVerified = [
            'Bank account verified' => [
                'status' => $bank_validation_status,
                'reason' => $merchant->user_banking_reason,
            ],
            'Download fraud report' => '',
            'Fraud Report successful' => $data['fraud_report'] ? false : true,
            'Finbit Account Details' => ''
        ];


        $data['offers'] = MerchantOffers::where('merchant_id',$id)->where('status', 1)->get(['id','advance_type','advance_emi','tenure','merchant_id','interest_rate', 'subvention','monthly_irr','annualised_irr','status']);
        $data['finbit_data'] = FinBitData::where('id_type','merchant')->where('application_id', $id)->get();
        $data['id'] = $id;
        $data['name'] = $merchant->merchant_name;
        $data['bank_statement'] = $bankStatement;
        $data['itr'] = $itr;
        $data['karza'] = $karzaDetails;
        $data['score'] = $score;
        $data['signing'] = $signing;
        $data['addressVerified'] = $addressVerified;
        $data['productVerified'] = $productVerified;
        $data['bankingVerified'] = $bankingVerified;
        $data['grade_set'] = $merchant->grade_id;
        $data['location_audit'] = $merchant->location_audit;
        $data['merchant'] = $merchant;
        $data['signed_agreement'] = $signed_agreement;
        $business = explode('|', $merchant->year_in_business);
        $year_in_business = isset($business[1]) ? $business[1] : 0;
        $month_in_business = isset($business[0]) ? $business[0]: 0;
        $data['years'] = $year_in_business.' Year, '.$month_in_business.' Month';
        return view('admin.merchant.verification_checklist', $data);
    }

    public function saveAgreementData(Request $request){
        try {
            $merchant = Merchant::find($request->get('merchant_id'));
            $merchant->agreement_signed_with = $request->get('agreement_signed_with');
            $merchant->agreement_signed_by = $request->get('agreement_signed_by');
            $merchant->merchant_reg_type = $request->get('merchant_reg_type');
            $merchant->designation_of_sign_auth = $request->get('designation_of_sign_auth');
            $merchant->save();
            return response()->json(['status' => 'OK', 'message' => 'Data saved successfully']);
        }catch (\Exception $e){
            return response()->json(['status' => 'FAIL', 'message' => 'Exception occurred while saving agreement details']);
        }
    }

    public function merchantLocationAudit(Request $request){
        if($request->get('merchant_id') && $request->get('location_audit')){
            $status = Merchant::where('id', $request->get('merchant_id'))->update(['location_audit' => $request->get('location_audit')]);
            if($status){
                return response()->json(['status' => 'OK', 'message' => 'Location Audit Successfully Updated'], 200);
            }
        }

        return response()->json(['status' => 'FAIL','message' => 'Failed to update audit'], 200);
    }

    public function merchantLocationServiceable(Request $request){
        if($request->get('merchant_id') && $request->get('location_serviceable')){
            $status = Merchant::where('id', $request->get('merchant_id'))->update(['location_serviceable' => $request->get('location_serviceable')]);
            if($status){
                return response()->json(['status' => 'OK', 'message' => 'Location Serviceable Successfully Updated'], 200);
            }
        }

        return response()->json(['status' => 'FAIL','message' => 'Failed to update location serviceable'], 200);
    }

    public function merchantBankingReason(Request $request){
        if($request->get('merchant_id') && $request->get('banking_reason')){
            $status = Merchant::where('id', $request->get('merchant_id'))->update(['user_banking_reason' => $request->get('banking_reason')]);
            if($status){
                return response()->json(['status' => 'OK', 'message' => 'Reason Successfully Updated'], 200);
            }
        }

        return response()->json(['status' => 'FAIL','message' => 'Failed to update reason'], 200);
    }

    public function createMerchantUser(Request $request, $merchant){
        $user = new \App\User();
        $email = $request->has('email') && $request->get('email') ? $request->get('email') : '';
        $existing_user = User::where(function ($query) use ($request) {
            $query->where('name', $request->get('merchant_name'))
                ->orWhere('mobile_number', $request->get('contact_number'));
        })->first(['id']);

        if(!empty($email) && $existing_user === null){
            $existing_user = User::where('email', $email)->first(['id']);
        }

        if ($existing_user === null) {

            $user->name = $request->get('merchant_name');

            $user->username = str_replace(' ', '', strtolower($request->get('merchant_name')));
            $user->email = $request->has('email') && $request->get('email') ? $request->get('email') : null;
            $user->mobile_number = $request->get('contact_number');
            $user->user_type = 2;
            $user->merchant_id = $merchant->id;
            $user->center_id = 0;
            $user->code = $this->getReferralCode();
            $user->password = bcrypt(123456);
            $user->save();

            return true;
        }else{

            return false;
        }

    }

    public function getMISReport($merchant_id, Request $request, $source = false, $center_id = ""){
        try {

            if(Auth::check() && Auth::user()->user_id == UserTypes::$merchant_v2){ //Merchant 2.0
                $merchant_id = Auth::user()->merchant_id;
            }

            ini_set('max_execution_time', -1);
            set_time_limit(0);
            ini_set('memory_limit', '-1');
            $request_data = $request->request->has('data') ? $request->request->get('data') : ($request->has('data') ? $request->get('data') : '');
            $from_date_clean = $request->has('from_date') ? $request->get('from_date') : ($request_data !== '' ? $request_data->from_date : '');
            $to_date_clean = $request->has('to_date') ? $request->get('to_date') : ($request_data !== '' ? $request_data->to_date : '');
            $user_id = $request->has('user_id') ? $request->get('user_id') : (isset($request_data->user_id) ? $request_data->user_id : '');
            $auth_email = $request->get('auth_email');
            $email = $request_data !== '' ? $request_data->email : '';
            $source = $request->has('source') ? $request->get('source') : $source;
            $is_daily = $request->has('is_daily') ? $request->get('is_daily') : null;
            $merchant_name = $request->has('name') ? $request->get('name') : null;
            $from_date = $from_date_clean !== '' ? $from_date_clean . ' 00:00:00' : '';
            $to_date = $to_date_clean !== '' ? $to_date_clean . ' 23:59:59' : '';
            $records = [];
            $records1 = [];
            $merchant = Merchant::where('id', $merchant_id)->first(['merchant_name', 'id']);
            if ($user_id != '') {
                $user = User::where('id', $user_id)->first();
            }

            if (($request->has('from_date') && $request->has('to_date')) || ($from_date !== '' && $to_date !== '')) {

                $gstDetails = new ConsumerDocument(new Request());

                if (in_array($merchant->id, [6601, 23])) {
                    $solarComment = ', (select comment from comments where comments.show_merchant = 1 and comments.application_id = ca.id order by created_at desc limit 1) as comment';
                } else {
                    $solarComment = '';
                }

            if($request->has('queue')){
                if($source != false && ($center_id !== "" && $center_id != null && $center_id != 0)){
                    // $records = "select consumer_application.id as application_id, merchant.merchant_name, consumer_application.permanent_address_state, consumer_application.current_address_state, consumer_application.current_address_city,lenders.name as lender_name, users.name as loginned_by, users_ads.name as loginned_by_ads, consumer_application.approved_amount, consumer_application.email, consumer_application.mobile_number, consumer_application.tenure, DATE_FORMAT(consumer_application.created_at, '%Y-%m-%d') as application_date, merchant_location.name as center, pro_forma_invoice.loan_application_no, consumer_application.name as customer_name, consumer_application.loan_amount, emi_calculation.upfront_from_customer as advance_amount, merchant_offers.slug as emi_plan, emi_calculation.upfront_from_credit_fair as disbursal_amount, emi_calculation.processing_fee as processing_fee, pro_forma_invoice.processing_fee_by_merchant as processing_fee_by_merchant, merchant_offers.processing_fee_first_emi as processing_fee_first_emi, consumer_application.state,(select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'disbursed' limit 1) as disbursed_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'in-process' limit 1) as inprocess_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'cf approved' limit 1) as cf_approved_date,(select created_at from status_logs where status_logs.application_id = consumer_application.id and (status_logs.status = 'approved' OR status_logs.status = 'cf approved') limit 1) as approved_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'ready for disbursement' limit 1) as ready_for_disb_date, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'rejected' OR status_logs.status = 'cf approved')) as total_decision_cases, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'cf approved')) as total_approved_cases, consumer_application.approved_amount * (emi_calculation.subvention/100) as subvention_amount, emi_calculation.subvention_igst + emi_calculation.subvention_cgst + emi_calculation.subvention_sgst as gst_amount,(select JSON_EXTRACT(payout_preserve.response,\"$.result.rrn\") from payout_preserve where payout_preserve.payout_status_id = payout_status.id and payout_preserve.api = 'getDisbursementStatus' ORDER by created_at desc limit 1) as upi_number, consumer_application_element.reference_number, consumer_application_element.unique_id_number, (select response from avanse_disbursement_status where avanse_disbursement_status.application_id = consumer_application.id order by created_at desc limit 1) as avanse_disbursement_status, (select utr from cf_payout_direct_transfer where cf_payout_direct_transfer.application_id = consumer_application.id order by created_at desc limit 1) as cashfree_disbursement,(select adjusted_amount from disbursements where disbursements.application_id = consumer_application.id order by created_at desc limit 1) as adjust, liability_log.liability_percent as lib, pro_forma_invoice.fldg_applied_category, consumer_application_element.product_name $solarComment from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id LEFT JOIN lenders on lenders.id = consumer_application.lender_id LEFT JOIN users on users.id = consumer_application.src_representative_name LEFT JOIN users as users_ads on users_ads.id = consumer_application.created_by left join consumer_application_element on consumer_application_element.application_id = consumer_application.id left join merchant_location on merchant_location.id = consumer_application_element.center_id left join emi_calculation on emi_calculation.application_id = consumer_application.id left join merchant_offers on merchant_offers.merchant_id = consumer_application.merchant_id left join pro_forma_invoice on pro_forma_invoice.application_id = consumer_application.id left join payout_status on payout_status.application_id = consumer_application.id left join liability_log on liability_log.application_id = consumer_application.id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.merchant_location_id = $center_id and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                    $records = "SELECT ca.id AS application_id, m.merchant_name, ca.permanent_address_state, ca.current_address_state, ca.current_address_city, l.name AS lender_name, u.name AS loginned_by, ua.name AS loginned_by_ads, ca.approved_amount, ca.email, ca.mobile_number, ca.tenure, DATE_FORMAT(ca.created_at, '%Y-%m-%d') AS application_date, ml.name AS center, pfi.loan_application_no, ca.name AS customer_name, ca.loan_amount, ec.upfront_from_customer AS advance_amount, mo.slug AS emi_plan, ec.upfront_from_credit_fair AS disbursal_amount, ec.processing_fee AS processing_fee, pfi.processing_fee_by_merchant AS processing_fee_by_merchant, mo.processing_fee_first_emi AS processing_fee_first_emi, ca.state, sl_disbursed.created_at AS disbursed_date, sl_in_process.created_at AS inprocess_date, sl_cf_approved.created_at AS cf_approved_date, sl_approved.created_at AS approved_date, sl_ready_for_disb.created_at AS ready_for_disb_date, COALESCE(decision_cases.total_decision_cases, 0) AS total_decision_cases, COALESCE(approved_cases.total_approved_cases, 0) AS total_approved_cases, ca.approved_amount * (ec.subvention / 100) AS subvention_amount, ec.subvention_igst + ec.subvention_cgst + ec.subvention_sgst AS gst_amount, lp.upi_number, cae.reference_number, cae.unique_id_number, las.response AS avanse_disbursement_status, lcd.utr AS cashfree_disbursement, ld.adjusted_amount AS adjust, ll.liability_percent AS lib, pfi.fldg_applied_category, cae.product_name $solarComment FROM consumer_application ca LEFT JOIN merchant m ON m.id = ca.merchant_id LEFT JOIN lenders l ON l.id = ca.lender_id LEFT JOIN users u ON u.id = ca.src_representative_name LEFT JOIN users AS ua ON ua.id = ca.created_by LEFT JOIN consumer_application_element cae ON cae.application_id = ca.id LEFT JOIN merchant_location ml ON ml.id = cae.center_id LEFT JOIN emi_calculation ec ON ec.application_id = ca.id LEFT JOIN merchant_offers mo ON mo.merchant_id = ca.merchant_id LEFT JOIN pro_forma_invoice pfi ON pfi.application_id = ca.id LEFT JOIN payout_status ps ON ps.application_id = ca.id LEFT JOIN liability_log ll ON ll.application_id = ca.id LEFT JOIN status_logs sl_disbursed ON sl_disbursed.application_id = ca.id AND sl_disbursed.status = 'disbursed' LEFT JOIN status_logs sl_in_process ON sl_in_process.application_id = ca.id AND sl_in_process.status = 'in-process' LEFT JOIN status_logs sl_cf_approved ON sl_cf_approved.application_id = ca.id AND sl_cf_approved.status = 'cf approved' LEFT JOIN status_logs sl_approved ON sl_approved.application_id = ca.id AND (sl_approved.status = 'approved' OR sl_approved.status = 'cf approved') LEFT JOIN status_logs sl_ready_for_disb ON sl_ready_for_disb.application_id = ca.id AND sl_ready_for_disb.status = 'ready for disbursement' LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_decision_cases FROM status_logs WHERE status IN ('approved', 'rejected', 'cf approved') GROUP BY application_id) AS decision_cases ON decision_cases.application_id = ca.id LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_approved_cases FROM status_logs WHERE status IN ('approved', 'cf approved') GROUP BY application_id) AS approved_cases ON approved_cases.application_id = ca.id LEFT JOIN (SELECT payout_status_id, JSON_EXTRACT(response, '$.result.rrn') AS upi_number FROM payout_preserve WHERE payout_preserve.api = 'getDisbursementStatus' ORDER BY created_at DESC LIMIT 1) AS lp ON lp.payout_status_id = ps.id LEFT JOIN (SELECT application_id, response FROM avanse_disbursement_status ORDER BY created_at DESC LIMIT 1) AS las ON las.application_id = ca.id LEFT JOIN (SELECT application_id, utr, ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY created_at DESC) AS row_num FROM cf_payout_direct_transfer) AS lcd ON lcd.application_id = ca.id AND lcd.row_num = 1 LEFT JOIN (SELECT application_id, adjusted_amount FROM disbursements ORDER BY created_at DESC LIMIT 1) AS ld ON ld.application_id = ca.id WHERE ca.is_deleted IS NULL AND (m.id = $merchant_id OR m.anchor_merchant_id = $merchant_id) AND ca.merchant_location_id = $center_id AND ca.created_at >= '" . $from_date . "' AND ca.created_at <= '" . $to_date . "';";
                }else {
                    // $records = "select consumer_application.id as application_id, merchant.merchant_name, consumer_application.permanent_address_state, consumer_application.current_address_state, consumer_application.current_address_city,lenders.name as lender_name, users.name as loginned_by, users_ads.name as loginned_by_ads, consumer_application.approved_amount, consumer_application.email, consumer_application.mobile_number, consumer_application.tenure, DATE_FORMAT(consumer_application.created_at, '%Y-%m-%d') as application_date, merchant_location.name as center, pro_forma_invoice.loan_application_no, consumer_application.name as customer_name, consumer_application.loan_amount, emi_calculation.upfront_from_customer as advance_amount, merchant_offers.slug as emi_plan, emi_calculation.upfront_from_credit_fair as disbursal_amount, emi_calculation.processing_fee as processing_fee, pro_forma_invoice.processing_fee_by_merchant as processing_fee_by_merchant, merchant_offers.processing_fee_first_emi as processing_fee_first_emi, consumer_application.state,(select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'disbursed' limit 1) as disbursed_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'in-process' limit 1) as inprocess_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'cf approved' limit 1) as cf_approved_date,(select created_at from status_logs where status_logs.application_id = consumer_application.id and (status_logs.status = 'approved' OR status_logs.status = 'cf approved') limit 1) as approved_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'ready for disbursement' limit 1) as ready_for_disb_date, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'rejected' OR status_logs.status = 'cf approved')) as total_decision_cases, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'cf approved')) as total_approved_cases, consumer_application.approved_amount * (emi_calculation.subvention/100) as subvention_amount, emi_calculation.subvention_igst + emi_calculation.subvention_cgst + emi_calculation.subvention_sgst as gst_amount,(select JSON_EXTRACT(payout_preserve.response,\"$.result.rrn\") from payout_preserve where payout_preserve.payout_status_id = payout_status.id and payout_preserve.api = 'getDisbursementStatus' ORDER by created_at desc limit 1) as upi_number, consumer_application_element.reference_number, consumer_application_element.unique_id_number, (select response from avanse_disbursement_status where avanse_disbursement_status.application_id = consumer_application.id order by created_at desc limit 1) as avanse_disbursement_status, (select utr from cf_payout_direct_transfer where cf_payout_direct_transfer.application_id = consumer_application.id order by created_at desc limit 1) as cashfree_disbursement,(select adjusted_amount from disbursements where disbursements.application_id = consumer_application.id order by created_at desc limit 1) as adjust, liability_log.liability_percent as lib, pro_forma_invoice.fldg_applied_category, consumer_application_element.product_name $solarComment from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id LEFT JOIN lenders on lenders.id = consumer_application.lender_id LEFT JOIN users on users.id = consumer_application.src_representative_name LEFT JOIN users as users_ads on users_ads.id = consumer_application.created_by left join consumer_application_element on consumer_application_element.application_id = consumer_application.id left join merchant_location on merchant_location.id = consumer_application_element.center_id left join emi_calculation on emi_calculation.application_id = consumer_application.id left join merchant_offers on merchant_offers.merchant_id = consumer_application.merchant_id left join pro_forma_invoice on pro_forma_invoice.application_id = consumer_application.id left join payout_status on payout_status.application_id = consumer_application.id left join liability_log on liability_log.application_id = consumer_application.id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                    // $records = "SELECT ca.id AS application_id, m.merchant_name, ca.permanent_address_state, ca.current_address_state, ca.current_address_city, l.name AS lender_name, u.name AS loginned_by, ua.name AS loginned_by_ads, ca.approved_amount, ca.email, ca.mobile_number, ca.tenure, DATE_FORMAT(ca.created_at, '%Y-%m-%d') AS application_date, ml.name AS center, pfi.loan_application_no, ca.name AS customer_name, ca.loan_amount, ec.upfront_from_customer AS advance_amount, mo.slug AS emi_plan, ec.upfront_from_credit_fair AS disbursal_amount, ec.processing_fee AS processing_fee, pfi.processing_fee_by_merchant AS processing_fee_by_merchant, mo.processing_fee_first_emi AS processing_fee_first_emi, ca.state, sl_disbursed.created_at AS disbursed_date, sl_in_process.created_at AS inprocess_date, sl_cf_approved.created_at AS cf_approved_date, sl_approved.created_at AS approved_date, sl_ready_for_disb.created_at AS ready_for_disb_date, COALESCE(decision_cases.total_decision_cases, 0) AS total_decision_cases, COALESCE(approved_cases.total_approved_cases, 0) AS total_approved_cases, ca.approved_amount * (ec.subvention / 100) AS subvention_amount, ec.subvention_igst + ec.subvention_cgst + ec.subvention_sgst AS gst_amount, lp.upi_number, cae.reference_number, cae.unique_id_number, las.response AS avanse_disbursement_status, lcd.utr AS cashfree_disbursement, ld.adjusted_amount AS adjust, ll.liability_percent AS lib, pfi.fldg_applied_category, cae.product_name $solarComment FROM consumer_application ca LEFT JOIN merchant m ON m.id = ca.merchant_id LEFT JOIN lenders l ON l.id = ca.lender_id LEFT JOIN users u ON u.id = ca.src_representative_name LEFT JOIN users AS ua ON ua.id = ca.created_by LEFT JOIN consumer_application_element cae ON cae.application_id = ca.id LEFT JOIN merchant_location ml ON ml.id = cae.center_id LEFT JOIN emi_calculation ec ON ec.application_id = ca.id LEFT JOIN merchant_offers mo ON mo.merchant_id = ca.merchant_id LEFT JOIN pro_forma_invoice pfi ON pfi.application_id = ca.id LEFT JOIN payout_status ps ON ps.application_id = ca.id LEFT JOIN liability_log ll ON ll.application_id = ca.id LEFT JOIN status_logs sl_disbursed ON sl_disbursed.application_id = ca.id AND sl_disbursed.status = 'disbursed' LEFT JOIN status_logs sl_in_process ON sl_in_process.application_id = ca.id AND sl_in_process.status = 'in-process' LEFT JOIN status_logs sl_cf_approved ON sl_cf_approved.application_id = ca.id AND sl_cf_approved.status = 'cf approved' LEFT JOIN status_logs sl_approved ON sl_approved.application_id = ca.id AND (sl_approved.status = 'approved' OR sl_approved.status = 'cf approved') LEFT JOIN status_logs sl_ready_for_disb ON sl_ready_for_disb.application_id = ca.id AND sl_ready_for_disb.status = 'ready for disbursement' LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_decision_cases FROM status_logs WHERE status IN ('approved', 'rejected', 'cf approved') GROUP BY application_id) AS decision_cases ON decision_cases.application_id = ca.id LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_approved_cases FROM status_logs WHERE status IN ('approved', 'cf approved') GROUP BY application_id) AS approved_cases ON approved_cases.application_id = ca.id LEFT JOIN (SELECT payout_status_id, JSON_EXTRACT(response, '$.result.rrn') AS upi_number FROM payout_preserve WHERE payout_preserve.api = 'getDisbursementStatus' ORDER BY created_at DESC LIMIT 1) AS lp ON lp.payout_status_id = ps.id LEFT JOIN (SELECT application_id, response FROM avanse_disbursement_status ORDER BY created_at DESC LIMIT 1) AS las ON las.application_id = ca.id LEFT JOIN (SELECT application_id, utr, ROW_NUMBER() OVER (PARTITION BY application_id ORDER BY created_at DESC) AS row_num FROM cf_payout_direct_transfer) AS lcd ON lcd.application_id = ca.id AND lcd.row_num = 1 LEFT JOIN (SELECT application_id, adjusted_amount FROM disbursements ORDER BY created_at DESC LIMIT 1) AS ld ON ld.application_id = ca.id WHERE ca.is_deleted IS NULL AND (m.id = $merchant_id OR m.anchor_merchant_id = $merchant_id) AND ca.created_at >= '" . $from_date . "' AND ca.created_at <= '" . $to_date . "';";
                    $records = "select consumer_application.id application_id, merchant.merchant_name, consumer_application.permanent_address_state, consumer_application.current_address_state,
                    consumer_application.current_address_city, lenders.name lender_name, u.name loginned_by, advisor.name loginned_by_ads,
                    consumer_application.approved_amount, consumer_application.email, consumer_application.mobile_number, consumer_application.tenure, consumer_application.updated_by,
                    date_format(consumer_application.created_at, '%Y-%m-%d') application_date, merchant_location.name center, 
                    pro_forma_invoice.loan_application_no, consumer_application.name customer_name, cast(consumer_application.loan_amount as unsigned) loan_amount, 
                    emi_calculation.upfront_from_customer advance_amount, merchant_offers.slug emi_plan, 
                    emi_calculation.upfront_from_credit_fair disbursal_amount, emi_calculation.processing_fee processing_fee, pro_forma_invoice.processing_fee_by_merchant, 
                    merchant_offers.processing_fee_first_emi, consumer_application.state, sl_disbursed.created_at disbursed_date, sl_in_process.created_at inprocess_date, 
                    sl_cf_approved.created_at cf_approved_date, sl_approved.created_at approved_date, sl_rfd.created_at ready_for_disb_date, 
                    if(sl_cf_approved.id is not null or sl_approved.id is not null or sl_rejected.id is not null, 0, 1) total_decision_cases, -- flag to check whether the application ever reached a final decision
                    if(sl_cf_approved.id is not null or sl_approved.id is not null, 1, 0) total_approved_cases, -- Whether the application was approved
                    consumer_application.approved_amount * (emi_calculation.subvention / 100) subvention_amount, 
                    emi_calculation.subvention_igst + emi_calculation.subvention_cgst + emi_calculation.subvention_sgst as gst_amount, 
                    lp.upi_number, consumer_application_element.reference_number, consumer_application_element.unique_id_number, las.response AS avanse_disbursement_status, 
                    cf_payout.cashfree_utr cashfree_disbursement, disbursements.adjusted_amount adjust, 
                    liability_log.liability_percent lib, pro_forma_invoice.fldg_applied_category, consumer_application_element.product_name
                    from consumer_application
                    left join merchant on merchant.id = consumer_application.merchant_id
                    left join consumer_application_element on consumer_application_element.application_id = consumer_application.id
                    left join emi_calculation on emi_calculation.id = (
                        select max(ec.id) from emi_calculation ec where ec.application_id = consumer_application.id and ec.offer_id = consumer_application.offer_id)
                    left join status_logs sl_in_process on sl_in_process.id = (
                        select min(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.status = 'in-process' and sl.deleted_at is null)
                    left join status_logs sl_cf_approved on sl_cf_approved.id = (
                        select min(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.status = 'cf approved' and sl.deleted_at is null)
                    left join status_logs sl_approved on sl_approved.id = (
                        select max(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.status = 'approved' and sl.deleted_at is null)
                    left join status_logs sl_rejected on sl_rejected.id = (
                        select max(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.status = 'rejected' and sl.deleted_at is null)
                    left join status_logs sl_rfd on sl_rfd.id = (
                        select min(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.status = 'ready for disbursement' and sl.deleted_at is null)
                    left join status_logs sl_disbursed on sl_disbursed.id = (
                        select min(sl.id) from status_logs sl where sl.application_id = consumer_application.id and sl.status in ('disbursed','nach registered') and sl.deleted_at is null)
                    left join pro_forma_invoice on pro_forma_invoice.id = (
                        select max(pfi.id) from pro_forma_invoice pfi where pfi.application_id = consumer_application.id and pfi.deleted_at is null)
                    left join merchant_location on merchant_location.id  = consumer_application_element.center_id
                    left join merchant_offers on merchant_offers.id = consumer_application.offer_id
                    left join users advisor on advisor.id = consumer_application.created_by -- advisor
                    left join users u on u.id = consumer_application.src_representative_name 
                    left join lenders on lenders.id = consumer_application.lender_id
                    left join liability_log on liability_log.application_id = consumer_application.id and liability_log.deleted_at is null
                    left join payout_status on payout_status.application_id = consumer_application.id
                    left join (
                        select payout_status_id, json_extract(response, '$.result.rrn') AS upi_number from payout_preserve where payout_preserve.api = 'getDisbursementStatus' 
                        order by created_at desc limit 1)lp on lp.payout_status_id = payout_status.id
                    left join avanse_disbursement_status las on las.id = 
                    (select max(id) from avanse_disbursement_status ads where ads.application_id = consumer_application.id)
                    left join (
                        select application_id, group_concat(utr) as cashfree_utr
                        from (
                            select *, row_number() over(partition by application_id, transferId, utr order by id desc) row_no
                            from cf_payout_direct_transfer
                            where message = 'SUCCESS'
                        ) cf_payout_direct_transfer where row_no=1
                        group by application_id
                    )cf_payout on cf_payout.application_id = consumer_application.id
                    left join disbursements on disbursements.id = 
                    (select min(d.id) from disbursements d where d.application_id = consumer_application.id and d.merchant_id = coalesce(merchant.id,merchant.anchor_merchant_id))
                    where (consumer_application.deleted_at is null and consumer_application.is_deleted is null and consumer_application.merchant_id not in (73,9421))
                    and (consumer_application.created_at >= '".$from_date."' and consumer_application.created_at < '".$to_date."')
                    and (consumer_application.merchant_id = $merchant_id || merchant.anchor_merchant_id = $merchant_id)";
                }
                $records = DB::select($records);
            }else{
                if($source != false && ($center_id !== "" && $center_id != null && $center_id != 0)){
                    $records1 = "Select count(consumer_application.id) from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.merchant_location_id = $center_id and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                }else{
                    $records1 = "Select count(consumer_application.id) from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                }
                $records1 = DB::select($records1);
            }


                if (count($records1) || $source == "merchant_app") {
                    //if (!$source && !$request->has('queue')){
                    if (!$request->has('queue')) {
                        $jobProvider = JobProvider::where('application_id', $merchant_id)->where('from', 'getMISReport')->where('is_available', 0)->first(['id']);

                        if (isset($jobProvider->id)) {
                            \Session::flash('error', 'There is an active queue for the MIS report. Please try again after sometime');
                            return redirect()->back();
                        }
                        if ((Auth::check() && $userAccEmail = Auth::user()->email) || $email != '') {
                            $auth_user_id = $user_id != '' ? $user_id : (Auth::check() ? Auth::user()->id : Input::get('auth_user_id'));
                            $auth_email = $userAccEmail ?? $email;

                            $payload = [
                                'payload' => json_encode($request->merge(['queue' => true, 'merchant_id' => $merchant_id, 'auth_user_id' => $auth_user_id, 'auth_email' => $auth_email, 'source' => $source])->all()),
                                'from' => 'getMISReport',
                                'application_id' => $merchant_id,
                                'available_at' => Carbon::now()->toDateTimeString(),
                                'created_at' => Carbon::now()->toDateTimeString()
                            ];
                            JobProvider::insert($payload);

                            if ($source == "merchant_app") {
                                $data = array();
                                $data['status'] = 'OK';
                                $data['message'] = 'You will receive the MIS data shortly over the email';
                                return $data;
                            } else {
                                \Session::flash('error', 'You will received the MIS data shortly over the email');
                                return redirect()->back();
                            }

                        }
                        \Session::flash('error', 'Please add the email id to your account to receive the mail');
                        return redirect()->back();
                    }
                } else {
                    if (!$request->has('queue')) {
                        if ($source != false) {
                            $data = array();
                            $data['status'] = 'Fail';
                            $data['message'] = 'No data found in selected period';
                            return $data;
                        } else {
                            \Session::flash('error', 'No data found in selected period');
                            return redirect()->back();
                        }
                    }
                }


                $applications = [];
                $total_decision_cases = 0;
                $total_approved_cases = 0;
                $tat_avg = [];
                $tat_disb_appr_avg = [];
                $tat_utr_avg = [];
                $disb_24hrs = 0;
                $dash_obj = new Dashboard($request);
                $states = ['disbursed', 'nach registered', 'closed'];
                if ($source) {
                    $response[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date', 'Merchant Name', 'Merchant Location Name', 'Lender Name', 'Customer Name', 'City', 'State', 'Login By', 'Mobile', 'Email', 'Product Name', 'Loan Amount', 'Tenure', 'Processing Fee', 'Advance Amount', 'Gross Disbursal amount (without GST)', 'Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Status', 'Disbursed Date', 'Subvention Amount', 'GST Amount', 'UTR Number', 'Adjusted', 'FLDG', 'Liability', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR', 'Comments'];
                    $partialDisbursement[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date', 'Merchant Name', 'Customer Name', 'Customer Email', 'City', 'State', 'Login By', 'Product Name', 'Procesing Fee', 'Net disbursement Amount', 'Approved Amount', 'Advance Amount', 'Disbursal Amount Phase 1', 'Subvention Amount	Phase 1', 'GST Amount Phase 1', 'Disbursal Amount Phase 2', 'Subvention Amount Phase 2', 'Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Disbursement Date Phase 2', 'GST Phase 2', 'Disbursal Amount Phase 3', 'Subvention Amount Phase 3', 'Disbursement Date Phase 3', 'GST Phase 3', 'Status', 'Disbursed Date', 'UTR Number', 'Adjusted', 'FLDG', 'Liability', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR'];
                    $performanceSummary[] = ['Total Cases', 'Total Cases where decision was given', 'Total Approved Cases', 'Approval %', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR', 'No. of cases disbursed within 24 hours'];
                } else {
                    $response[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date', 'Merchant Name', 'Merchant Location Name', 'Lender Name', 'Customer Name', 'City', 'State', 'Login By', 'Mobile', 'Email', 'Product Name', 'Loan Amount', 'Tenure', 'Processing Fee', 'Advance Amount', 'Gross Disbursal amount (without GST)', 'Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Status', 'Disbursed Date', 'Subvention Amount', 'GST Amount', 'UTR Number', 'Adjusted', 'FLDG', 'Liability', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR', 'Comments'];
                    $partialDisbursement[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date', 'Merchant Name', 'Customer Name', 'Customer Email', 'City', 'State', 'Login By', 'Product Name', 'Procesing Fee', 'Net disbursement Amount', 'Approved Amount', 'Advance Amount', 'Disbursal Amount Phase 1', 'Subvention Amount	Phase 1', 'GST Amount Phase 1', 'Disbursal Amount Phase 2', 'Subvention Amount Phase 2', 'Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Disbursement Date Phase 2', 'GST Phase 2', 'Disbursal Amount Phase 3', 'Subvention Amount Phase 3', 'Disbursement Date Phase 3', 'GST Phase 3', 'Status', 'Disbursed Date', 'UTR Number', 'Adjusted', 'FLDG', 'Liability', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR', 'Customer Mobile Number'];
                    $performanceSummary[] = ['Total Cases', 'Total Cases where decision was given', 'Total Approved Cases', 'Approval %', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR', 'No. of cases disbursed within 24 hours'];
                }
                foreach ($records as $record) {
                    if (!in_array($record->application_id, $applications)) {

                        $av_transactionRef = '';
                        if ($record->avanse_disbursement_status) {
                            $avanseUTRStatusResponse = json_decode($record->avanse_disbursement_status);
                            foreach ($avanseUTRStatusResponse->disbResponse as $disbResponse) {
                                $av_transactionRef = $disbResponse->transactionRef ?? '';
                            }
                        }

                        if (empty($av_transactionRef) && $record->cashfree_disbursement) {
                            $av_transactionRef = $record->cashfree_disbursement;
                        }

                        $applications[] = $record->application_id;
                        $total_decision_cases += $record->total_decision_cases;
                        $total_approved_cases += $record->total_approved_cases;
                        $responseData[$record->application_id] = $record;
                        $tat = '';
                        if ($record->approved_date != NULL && $record->inprocess_date != NULL) {
                            if ($record->cf_approved_date) {
                                $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $record->cf_approved_date);
                            } else {
                                $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $record->approved_date);
                            }
                            $date_from = Carbon::createFromFormat('Y-m-d H:i:s', $record->inprocess_date);
                            $tat = $dash_obj->secondsToDays($date_to->diffInSeconds($date_from));
                            $tat_avg[] = $date_to->diffInSeconds($date_from);
                        } else {
                            $tat = '-';
                        }
                        if ($record->approved_date != NULL && $record->ready_for_disb_date != NULL) {
                            $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $record->approved_date);
                            $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $record->ready_for_disb_date);
                            $tat_disb_appr = $dash_obj->secondsToDays($date_ready_for_disb->diffInSeconds($date_to));
                            $tat_disb_appr_avg[] = $date_ready_for_disb->diffInSeconds($date_to);
                        } else {
                            $tat_disb_appr = '-';
                        }
                        if ($record->ready_for_disb_date != NULL && $record->disbursed_date != NULL) {
                            $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $record->ready_for_disb_date);
                            $disb_date = Carbon::createFromFormat('Y-m-d H:i:s', $record->disbursed_date);
                            $tat_utr = $dash_obj->secondsToDays($disb_date->diffInSeconds($date_ready_for_disb));
                            $tat_utr_avg[] = $disb_date->diffInSeconds($date_ready_for_disb);
                        } else {
                            $tat_utr = '-';
                        }
                        if ($record->ready_for_disb_date != NULL && $record->inprocess_date != NULL) {
                            $date_ready_for_disb1 = Carbon::createFromFormat('Y-m-d H:i:s', $record->ready_for_disb_date);
                            $date_from1 = Carbon::createFromFormat('Y-m-d H:i:s', $record->inprocess_date);
                            $date_diff = $date_ready_for_disb1->diffInHours($date_from1);
                            if ($date_diff <= 24) {
                                $disb_24hrs++;
                            }
                        }

                        if (isset($record->adjust) && ($record->adjust) != 0) {
                            $adjusted = true;
                            $utr_no = $record->upi_number ? $record->upi_number : ($record->reference_number ? $record->reference_number : $av_transactionRef);

                        } else {
                            $adjusted = false;
                            $utr_no = $record->upi_number ? $record->upi_number : ($record->reference_number ? $record->reference_number : $av_transactionRef);
                        }

                        $amount = in_array($record->state, $states) ? ($record->disbursal_amount - $record->gst_amount) : null;

                        if ($record->processing_fee && $record->processing_fee_by_merchant == 1) {
                            $amount -= $record->processing_fee;
                        }

                        if ($record->processing_fee_first_emi) {
                            $amount += $record->processing_fee;
                        }
                        $product_name = isset($record->product_name) ? $record->product_name : '';
                        $applicationController = new ConsumerApplicationController();
                        $product_name = $applicationController->getProductName($product_name);
                        $itemData = [
                            'id' => $record->application_id,
                            'unique_id' => $record->unique_id_number,
                            'loan_application_number' => $record->loan_application_no,
                            'application_date' => $record->application_date,
                            'merchant_name' => $record->merchant_name,
                            'center' => $record->center,
                            'lender_name' => $record->lender_name,
                            'customer_name' => $record->customer_name,
                            'current_city' => $record->current_address_city,
                            'current_state' => $record->current_address_state,
                            'login_by_user' => $record->loginned_by ? $record->loginned_by : $record->loginned_by_ads,
                            'customer number' => wall_decrypt($record->mobile_number),
                            'email' => wall_decrypt($record->email),
                            'product_name' => $product_name,
                            'loan_amount' => $record->loan_amount,
                            'tenure' => $record->tenure,
                            'processing_fee' => $record->processing_fee,
                            'advance_amount' => in_array($record->state, $states) ? $record->advance_amount : null,
                            // 'emi_plan' => $record->emi_plan,
                            'disbursal_amount' => in_array($record->state, $states) ? $record->disbursal_amount : null,
                            'net_disb_amount' => in_array($record->state, $states) ? $amount : null,
                            'state' => $record->state,
                            'disbursal_date' => $record->disbursed_date,
                            'subvention_amount' => in_array($record->state, $states) ? $record->subvention_amount : null,
                            'gst_amount' => in_array($record->state, $states) ? $record->gst_amount : null,
                            // 'upi_number' => $record->upi_number ? $record->upi_number : ($record->reference_number ? $record->reference_number : $av_transactionRef),
                            'UTR_number' => !empty($utr_no) ? "'" . $utr_no . "'" : null,
                            'Adjusted' => ($adjusted) ? 'Yes' : 'No',
                            // 'UTR_number' =>  in_array($record->state, $states) ? $utr_no : null,
                            'fldg' => $record->fldg_applied_category ? 'FLDG' : 'Without FLDG',
                            'hold' => $record->lib ? 'Liability' : 'Without Liability',
                            'tat_of_approval' => empty($tat) ? '0 Minutes' : $tat,
                            'tat_of_approval_disbursement' => empty($tat_disb_appr) ? '0 Minutes' : $tat_disb_appr,
                            'tat_of_sharing_utr' => empty($tat_utr) ? '0 Minutes' : $tat_utr,
                            'comments' => $record->comment ?? ''
                        ];
                        if (!$source) {
                            /*$itemData['phone'] = wall_decrypt($record->mobile_number);*/
                        }
                        $response [] = $itemData;
                    }
                }


                if ($applications) {

                    $disbursementLogs = DisbursementLogs::whereIn('application_id', $applications)->get();

                    $responseDataPartial = [];
                    foreach ($disbursementLogs as $disbursementLog) {
                        $responseDataPartial[$disbursementLog->application_id][] = $disbursementLog;
                    }

                    foreach ($responseDataPartial as $log) {


                        $phase1_disb_amount = 0;
                        $phase1_sub_amount = 0;
                        $phase1_date = '';
                        $phase1_gst = 0;
                        $phase2_disb_amount = 0;
                        $phase2_sub_amount = 0;
                        $phase2_date = '';
                        $phase2_gst = 0;
                        $phase3_disb_amount = 0;
                        $phase3_sub_amount = 0;
                        $phase3_date = '';
                        $phase3_gst = 0;
                        $phase4_disb_amount = 0;
                        $phase4_sub_amount = 0;
                        $phase4_date = '';
                        $phase4_gst = 0;

                        foreach ($log as $item) {
                            if ($item->phase == 1) {
                                $phase1_disb_amount = $item->disbursement_amount;
                                $phase1_sub_amount = $item->partial_subvention_amount;
                                $phase1_date = $item->created_at;


                                $gstDetailsData = $gstDetails->getGSTDetails(
                                    [
                                        'amount' => $phase1_sub_amount,
                                        'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                    ]
                                );


                                foreach ($gstDetailsData as $gstItem) {
                                    $phase1_gst += $gstItem['gst_amount'];
                                }

                            } elseif ($item->phase == 2) {
                                $phase2_disb_amount = $item->disbursement_amount;
                                $phase2_sub_amount = $item->partial_subvention_amount;
                                $phase2_date = $item->created_at;


                                $gstDetailsData = $gstDetails->getGSTDetails(
                                    [
                                        'amount' => $phase2_sub_amount,
                                        'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                    ]
                                );

                                foreach ($gstDetailsData as $gstItem) {
                                    $phase2_gst += $gstItem['gst_amount'];
                                }

                            } elseif ($item->phase == 3) {
                                $phase3_disb_amount = $item->disbursement_amount;
                                $phase3_sub_amount = $item->partial_subvention_amount;
                                $phase3_date = $item->created_at;


                                $gstDetailsData = $gstDetails->getGSTDetails(
                                    [
                                        'amount' => $phase3_sub_amount,
                                        'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                    ]
                                );

                                foreach ($gstDetailsData as $gstItem) {
                                    $phase2_gst += $gstItem['gst_amount'];
                                }

                            } elseif ($item->phase == 4) {
                                $phase4_disb_amount = $item->disbursement_amount;
                                $phase4_sub_amount = $item->partial_subvention_amount;
                                $phase4_date = $item->created_at;


                                $gstDetailsData = $gstDetails->getGSTDetails(
                                    [
                                        'amount' => $phase4_sub_amount,
                                        'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                    ]
                                );

                                foreach ($gstDetailsData as $gstItem) {
                                    $phase4_gst += $gstItem['gst_amount'];
                                }

                            }
                        }

                        $log = $log[0];

                        $av_transactionRef = '';
                        if ($responseData[$log->application_id]->avanse_disbursement_status) {
                            $avanseUTRStatusResponse = json_decode($responseData[$log->application_id]->avanse_disbursement_status);
                            foreach ($avanseUTRStatusResponse->disbResponse as $disbResponse) {
                                $av_transactionRef = $disbResponse->transactionRef ?? '';
                            }
                        }

                        if (empty($av_transactionRef) && $responseData[$log->application_id]->cashfree_disbursement) {
                            $av_transactionRef = $responseData[$log->application_id]->cashfree_disbursement;
                        }

                        if ($responseData[$log->application_id]->approved_date != NULL && $responseData[$log->application_id]->inprocess_date != NULL) {
                            $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->approved_date);
                            $date_from = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->inprocess_date);
                            $tat = $dash_obj->secondsToDays($date_to->diffInSeconds($date_from));
                        } else {
                            $tat = '-';
                        }
                        if ($responseData[$log->application_id]->approved_date != NULL && $responseData[$log->application_id]->ready_for_disb_date != NULL) {
                            $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->approved_date);
                            $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->ready_for_disb_date);
                            $tat_disb_appr = $dash_obj->secondsToDays($date_ready_for_disb->diffInSeconds($date_to));
                        } else {
                            $tat_disb_appr = '-';
                        }
                        if ($responseData[$log->application_id]->ready_for_disb_date != NULL && $phase1_date != NULL) {
                            $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->ready_for_disb_date);
                            $disb_date = Carbon::createFromFormat('Y-m-d H:i:s', $phase1_date);
                            $tat_utr = $dash_obj->secondsToDays($disb_date->diffInSeconds($date_ready_for_disb));
                            $tat_utr_avg[] = $disb_date->diffInSeconds($date_ready_for_disb);
                        } else {
                            $tat_utr = '-';
                        }

                        if (isset($responseData[$log->application_id]->adjust) && $responseData[$log->application_id]->adjust != 0) {
                            $adjusted = true;
                            $utr_no = $responseData[$log->application_id]->upi_number ? $responseData[$log->application_id]->upi_number : ($responseData[$log->application_id]->reference_number ? $responseData[$log->application_id]->reference_number : $av_transactionRef);


                        } else {
                            $adjusted = false;
                            $utr_no = $responseData[$log->application_id]->upi_number ? $responseData[$log->application_id]->upi_number : ($responseData[$log->application_id]->reference_number ? $responseData[$log->application_id]->reference_number : $av_transactionRef);
                            // $utr_no = $responseData[$log->application_id]->cashfree_disbursement;
                        }

                        $disbamount = in_array($responseData[$log->application_id]->state, $states) ? $responseData[$log->application_id]->disbursal_amount - $responseData[$log->application_id]->gst_amount : null;

                        if ($responseData[$log->application_id]->processing_fee && $responseData[$log->application_id]->processing_fee_by_merchant == 1) {
                            $disbamount -= $responseData[$log->application_id]->processing_fee;
                        }

                        if ($responseData[$log->application_id]->processing_fee_first_emi) {
                            $disbamount += $responseData[$log->application_id]->processing_fee;
                        }

                        $product_name1 = !empty($responseData[$log->application_id]->product_name) ? $responseData[$log->application_id]->product_name : '';
                        $applicationController1 = new ConsumerApplicationController();
                        $product_name1 = $applicationController1->getProductName($product_name1);

                        $itemDataP = [
                            'id' => $log->application_id,
                            'unique_id' => $responseData[$log->application_id]->unique_id_number,
                            'loan Application Number' => $responseData[$log->application_id]->loan_application_no,
                            'application Date' => $responseData[$log->application_id]->application_date,
                            'merchant_name' => $responseData[$log->application_id]->merchant_name,
                            'customer_name' => $responseData[$log->application_id]->customer_name,
                            'customer email' => wall_decrypt($responseData[$log->application_id]->email),
                            'current_city' => $responseData[$log->application_id]->current_address_city,
                            'current_state' => $responseData[$log->application_id]->current_address_state,
                            'login_by_user' => $responseData[$log->application_id]->loginned_by ? $responseData[$log->application_id]->loginned_by : $responseData[$log->application_id]->loginned_by_ads,
                            'product_name' => $product_name1,
                            'processing_fee' => $responseData[$log->application_id]->processing_fee,
                            // 'net_disb_amount' => in_array($responseData[$log->application_id]->state, $states) ? ($responseData[$log->application_id]->disbursal_amount - $responseData[$log->application_id]->gst_amount) : null,
                            'net_disb_amount' => in_array($responseData[$log->application_id]->state, $states) ? $disbamount : null,
                            'approved Amount' => $responseData[$log->application_id]->approved_amount,
                            'advance Amount' => in_array($responseData[$log->application_id]->state, $states) ? $responseData[$log->application_id]->advance_amount : null,
                            'disbursal Amount Phase 1' => $phase1_disb_amount,
                            'subvention Amount	Phase 1' => $phase1_sub_amount,
                            'gst Amount Phase 1' => $phase1_gst,
                            'disbursal Amount Phase 2' => $phase2_disb_amount,
                            'subvention_amount_phase 2' => $phase2_sub_amount,
                            'disbursement date phase 2' => $phase2_date,
                            'gst_phase 2' => $phase2_gst,
                            'disbursal Amount Phase 3' => $phase3_disb_amount,
                            'subvention_amount_phase 3' => $phase3_sub_amount,
                            'disbursement date phase 3' => $phase3_date,
                            'gst_phase 3' => $phase3_gst,
                            'state' => $responseData[$log->application_id]->state,
                            'disbursed Date' => $responseData[$log->application_id]->disbursed_date,
                            // 'upi_number' => $responseData[$log->application_id]->upi_number ? $responseData[$log->application_id]->upi_number : ($responseData[$log->application_id]->reference_number ? $responseData[$log->application_id]->reference_number : $av_transactionRef),
                            'UTR_number' => !empty($utr_no) ? "'" . $utr_no . "'" : null,
                            'Adjusted' => ($adjusted) ? 'Yes' : 'No',
                            // 'UTR_number' =>  in_array($responseData[$log->application_id]->state, $states) ? $utr_no : null,
                            'fldg' => $responseData[$log->application_id]->fldg_applied_category ? 'FLDG' : 'Without FLDG',
                            'hold' => $responseData[$log->application_id]->lib ? 'Liability' : 'Without Liability',
                            'tat_of_approval' => empty($tat) ? '0 Minutes' : $tat,
                            'tat_of_approval_disbursement' => empty($tat_disb_appr) ? '0 Minutes' : $tat_disb_appr,
                            'tat_of_sharing_utr' => empty($tat_utr) ? '0 Minutes' : $tat_utr
                        ];
                        /*if(!$source){
                            $itemDataP['customer_mobile_number'] =  wall_decrypt($responseData[$log->application_id]->mobile_number);
                        }

                        $partialDisbursement[] = $itemDataP;*/

                        if (!$source) {
                            $itemDataP['customer number'] = wall_decrypt($responseData[$log->application_id]->mobile_number);
                        }

                        $partialDisbursement[] = $itemDataP;
                    }
                    if (count($tat_avg)) {
                        $avg_tat = $dash_obj->secondsToDays(array_sum($tat_avg) / count($tat_avg));
                    } else {
                        $avg_tat = 0;
                    }
                    if (count($tat_disb_appr_avg)) {
                        $avg_tat_disb = $dash_obj->secondsToDays(array_sum($tat_disb_appr_avg) / count($tat_disb_appr_avg));
                    } else {
                        $avg_tat_disb = 0;
                    }
                    if (count($tat_utr_avg)) {
                        $avg_tat_utr = $dash_obj->secondsToDays(array_sum($tat_utr_avg) / count($tat_utr_avg));
                    } else {
                        $avg_tat_utr = 0;
                    }
                    $itemDataPSummary = [
                        'total_cases' => count($applications),
                        'total_cases_decision' => $total_decision_cases,
                        'total_approved_cases' => $total_approved_cases,
                        'approval_percentage' => $total_decision_cases > 0 ? round(($total_approved_cases / $total_decision_cases) * 100, 2) . '%' : '0%',
                        'tat_approval' => $avg_tat,
                        'tat_approval_disb' => $avg_tat_disb,
                        'tat_utr' => $avg_tat_utr,
                        'cases_disb_in_24hrs' => $disb_24hrs
                    ];
                    $performanceSummary[] = $itemDataPSummary;


                }
                if (count($records)) {
                    $queue = $request->get('queue');
                    $request = new Request(['data' => [$response, $partialDisbursement, $performanceSummary], 'sheet' => ['MIS Report', 'Partial Disbursement MIS Report', 'Performance Summary']]);
                    if ($source != false) {
                        $file = $filename = 'MIS_Report_From_' . $from_date_clean . '_To_' . $to_date_clean . '.xls';

                        if ($user->user_type == 2 || (!isset($user) && empty($user))) {
                            $details = " Dear " . $merchant->merchant_name . ",<br>
As requested, please find the below attached MIS report from " . $from_date_clean . " to " . $to_date_clean . ".<br>Please reach out on bd@creditfair.in in case you have any issues<br>";
                        } else {
                            $details = " Dear " . $user->name . ",<br>
As requested, please find the below attached MIS report from " . $from_date_clean . " to " . $to_date_clean . ".<br>Please reach out on bd@creditfair.in in case you have any issues<br>";
                        }

                        if ($email !== '' && filter_var($email, FILTER_VALIDATE_EMAIL)) {
                            Excel::store((new ExporterMultipleSheet($request)), $file);
                            Mail::send('emails.reminders.basic_reminder',
                                ['body' => $details, 'isBD' => true, 'footer_phone_number' => 9173131148]
                                , function ($message) use ($filename, $to_date_clean, $from_date_clean, $file, $email) {
                                    $message->to($email)->subject('Credit Fair :MIS Report from ' . $from_date_clean . ' to ' . $to_date_clean);
                                    $message->attachData(file_get_contents(storage_path() . '/app/' . $file), $filename);
                                });
                            unlink(storage_path() . '/app/' . $file);
                            $data = array();
                            $data['status'] = 'OK';
                            $data['message'] = 'Your MIS report is successfully sent to your registered mail id';
                            return $data;
                        } else {
                            $data = array();
                            $data['status'] = 'Fail';
                            $data['message'] = 'No email id is registered with your name, please reach out to bd@creditfair.in to update your email id';
                            return $data;
                        }
                    } else {
                        //return Excel::download((new ExporterMultipleSheet($request)), 'MIS_Report_From_' . $from_date . '_To_' . $to_date . '.xls');
                        $file = $filename = 'MIS_Report_From_' . $from_date_clean . '_To_' . $to_date_clean . '.xls';
                        Excel::store((new ExporterMultipleSheet($request)), $file);

                        $details = "As requested, please find the below attached MIS report from " . $from_date_clean . " to " . $to_date_clean . ".<br>";
                        if (isset($is_daily)) {
                            $details = "Dear " . $merchant_name . ",<br>
                        We are glad to have you as our merchant partner.Please find the daily MIS attached with this email.<br>
                        Disclaimer: This is system generated mail. Please reach out your respective relationship manager if you have any issues about the same <br><br><br><br><br>Regards";
                            Mail::send('emails.reminders.basic_reminder',
                                ['body' => $details, 'isBD' => true, 'footer_phone_number' => 9173131148]
                                , function ($message) use ($filename, $to_date_clean, $from_date_clean, $file, $auth_email) {
                                    $message->to($auth_email)->subject('Credit Fair :MIS Report from ' . $from_date_clean . ' to ' . $to_date_clean);
                                    $message->cc('dailymis@creditfair.in');
                                    $message->attachData(file_get_contents(storage_path() . '/app/' . $file), $filename);
                                });
                            unlink(storage_path() . '/app/' . $file);
                        } else {
                            Mail::send('emails.reminders.basic_reminder',
                                ['body' => $details, 'isBD' => true, 'footer_phone_number' => 9173131148]
                                , function ($message) use ($filename, $to_date_clean, $from_date_clean, $file, $auth_email) {
                                    $message->to($auth_email)->subject('Credit Fair :MIS Report from ' . $from_date_clean . ' to ' . $to_date_clean);
                                    $message->attachData(file_get_contents(storage_path() . '/app/' . $file), $filename);
                                });
                            unlink(storage_path() . '/app/' . $file);
                        }
                    }
                } else {
                    if ($source != false) {
                        $data = array();
                        $data['status'] = 'Fail';
                        $data['message'] = 'No data found in selected period';
                        return $data;
                    } else {
                        \Session::flash('error', 'No data found in selected period');
                        return redirect()->back();
                    }
                }
            }
            $data['merchant_id'] = $merchant_id;
            $data['from_date'] = $from_date;
            $data['to_date'] = $to_date;
            $data['merchant_name'] = $merchant->merchant_name;
            if (isset($queue) && $queue == true) {
                return 1;
            }
        }catch (\Exception $e){
            $this->cwFactory->logger(json_encode(['title'=> 'MIS report', 'merchant_id'=> $merchant_id, 'error'=>$e->getMessage(), 'line' => $e->getLine(), 'trace' => $e->getTraceAsString()]));
        }
        return view('admin.merchant.mis_report', $data);
    }

    public function merchantRelationshipAnalytics($event = array()){

        $merchantRAData = MerchantRelationshipAnalytics::get(['merchant_id']);
        $ids = [];
        foreach ($merchantRAData as $RADatum){
            $ids[] = $RADatum->merchant_id;
        }
        $merchants = Merchant::with('applications.logs','applications.emi','applications.emi_payment.transaction','applications.principal_outstanding','applications.creditReport');
        if($ids){
            $merchants = $merchants->whereNotIn('id', $ids);
        }
        $merchants = $merchants->where('status','active')->where('is_guest_active',1)->limit(1)->get();

        ConsumerApplication::isAccountOpened(true);

        $data = [];

        foreach ($merchants as $merchant) {

            try {
                if ($event) {
                    $event->comment('Processing ' . $merchant->merchant_name);
                }

                $casesApproved = 0;
                $casesDisbursed = 0;
                $disbursementAmount = 0;
                $cancelled = 0;
                $disbursedCasesNumber = 0;
                $NachRegisteredCasesNumber = 0;
                $processingFeeAmount = 0;
                $principal_outstanding = 0;
                $totalAmountCollected = 0;
                $TotalCasesWithDPDGrt7 = 0;
                $AverageCreditScore = 0;
                $availableCredit = 0;
                $NewToCredit = 0;
                $NAP90Days = 0;
                $totalForeClosureAmount = 0;
                $totalAmountReceived = 0;
                $overDue = 0;


                $accounting = new AccountingController();
                foreach ($merchant->applications as $application) {

                    if (!$application->is_deleted && $application->emi) {

                        $foreclosureAmount = 0;
                        $amountReceived = 0;
                        $loanSchedule = $accounting->viewloanschedule($application->id, 1);

                        if ($loanSchedule['schedule']) {
                            $_schedule_id = 0;
                            foreach ($loanSchedule['schedule'] as $schedule_id => $schedule) {
                                if ($schedule['emi_date'] <= Carbon::now()->endOfMonth()->toDateString()) {
                                    $_schedule_id = $schedule_id;
                                }

                                if (isset($schedule['transaction'])) {
                                    foreach ($schedule['transaction'] as $transaction) {
                                        $amountReceived += $transaction->amount;
                                    }
                                }
                            }

                            $foreclosureAmount = !is_nan($loanSchedule['schedule'][$_schedule_id]['fore_closure_amount']) ? $loanSchedule['schedule'][$_schedule_id]['fore_closure_amount'] : 0;

                        }

                        if ($loanSchedule['summary']) {
                            $overDue += $loanSchedule['summary']['loan_outstanding'];
                        }

                        $totalForeClosureAmount += $foreclosureAmount;
                        $totalAmountReceived += $amountReceived;

                        if ($application->logs) {
                            foreach ($application->logs as $log) {
                                if ($log->status == 'approved' && $application->status != 'rejected') {
                                    $casesApproved++;
                                    break;
                                }
                            }
                        }

                        if (in_array($application->status, ['disbursed', 'closed', 'nach registered'])) {
                            $casesDisbursed++;
                        }

                        if ($application->emi && $application->approved_amount > 0) {

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

                            $disbursementAmount += ($application->approved_amount - ($application->emi->upfront_from_customer + (($application->approved_amount - $downPayment) * $application->emi->subvention / 100)));
                        }

                        if ($application->status == 'disbursed') {
                            $disbursedCasesNumber++;
                        }

                        if ($application->status == 'nach registered') {
                            $NachRegisteredCasesNumber++;
                        }

                        if ($application->status == 'cancelled') {
                            $cancelled++;
                        }

                        $casesWithDPDGrt7 = 0;
                        $NAP90DaysTrigger = 0;
                        if ($application->emi_payment) {
                            foreach ($application->emi_payment as $emiPayment) {
                                foreach ($emiPayment->transaction as $transaction) {
                                    if (str_replace(' ', '_', strtolower($transaction->payment_for)) == 'processing_fee') {
                                        $processingFeeAmount += $transaction->amount;
                                    }

                                    if ($transaction->amount) {
                                        $totalAmountCollected += $transaction->amount;
                                    }
                                }

                                if ($totalAmountCollected && $emiPayment->charges_waived) {
                                    $totalAmountCollected -= $emiPayment->charges_waived;
                                }

                                if ($emiPayment->delay_days > 7) {
                                    $casesWithDPDGrt7 = $emiPayment->delay_days;
                                }
                                if ($emiPayment->delay_days > 90) {
                                    $NAP90DaysTrigger = $emiPayment->delay_days;
                                }
                            }
                        }

                        if ($casesWithDPDGrt7) {
                            $TotalCasesWithDPDGrt7++;
                        }
                        if ($NAP90DaysTrigger) {
                            $NAP90Days++;
                        }

                        if ($application->principal_outstanding && $application->principal_outstanding->principal_outstanding) {
                            $principal_outstanding += $application->principal_outstanding->principal_outstanding;
                        }

                        foreach ($application->creditReport as $creditReport) {
                            if ($creditReport->Score >= 300 && $creditReport->Score <= 900) {
                                $AverageCreditScore++;
                            } elseif ($creditReport->Score >= -1 && $creditReport->Score <= 6) {
                                $NewToCredit++;
                            }
                            $availableCredit++;
                        }
                    }

                }

                if ($event) {
                    $event->comment('Processed');
                }


                $data[] = [
                    'merchant_id' => $merchant->id,
                    'application_received' => $merchant->applications ? count($merchant->applications) : 0,
                    'case_approved' => $casesApproved,
                    'case_disbursed' => $casesDisbursed,
                    'value_disbursed' => round($disbursementAmount,2),
                    'case_cancelled' => $cancelled,
                    'nach_registered' => $disbursedCasesNumber ? round(($NachRegisteredCasesNumber / $disbursedCasesNumber) * 100, 2) : 0,
                    'processing_fee_collected' => $processingFeeAmount,
                    'principal_outstanding' => $principal_outstanding,
                    'cash_collected' => $totalAmountCollected,
                    'percentage_due' => 0,
                    'over_due_amount' => $overDue,
                    'case_with_dpd' => $TotalCasesWithDPDGrt7,
                    'percentage_dpd' => $disbursedCasesNumber ? round(($TotalCasesWithDPDGrt7 / $disbursedCasesNumber) * 100, 2) : 0,
                    'average_credit_score' => count($merchant->applications) ? round(($AverageCreditScore / count($merchant->applications)) * 100, 2) : 0,
                    'percentage_new_to_credit' => $availableCredit ? round(($NewToCredit / $availableCredit) * 100, 2) : 0,
                    'percentage_npa' => $disbursedCasesNumber ? round(($NAP90Days / $disbursedCasesNumber) * 100, 2) : 0,
                    'percentage_lgd' => $totalForeClosureAmount ? round(($totalAmountReceived / $totalForeClosureAmount) * 100, 2) : 0,
                    'created_at' => Carbon::now()->toDateTimeString()
                ];
            }catch (\Exception $e){
                $d = isset($data[0]) ? $data[0] : [];
                $d['totalForeClosureAmount'] = isset($totalForeClosureAmount) ? $totalForeClosureAmount : 0;
                $d['totalAmountReceived'] = isset($totalAmountReceived) ? $totalAmountReceived : 0;
                $d['e'] = $e->getMessage();
                $d['l'] = $e->getLine();
                $d['f'] = $e->getFile();
                (new Slack(new Request(['title' => 'Testing (Merchant Analytics Fixes)', 'fields' => $d])))->send();
            }
        }

        if($event){
            $event->comment('Finished');
        }
        MerchantRelationshipAnalytics::insert($data);

        CampaignsJobProvider::where('from', 'merchantRelationshipAnalytics')->update(['is_available' => 0]);
    }

    public function productList($merchant_id){
        $products = Products::with('merchant','productOffer')->where('merchant_id',$merchant_id)->get();

        $selectedOffers = [];

        foreach ($products as $product){
            $offersData = [];
            foreach ($product->productOffer as $offer){
                $offersData[] = $offer->offer_id;
            }
            $selectedOffers[$product->id] = $offersData;
        }

        $offers = MerchantOffers::with('merchant')->where('merchant_id',$merchant_id)->where('status',1)->Where('disable_offer_status', 1)->get();

        $data['products'] = $products;
        $data['offers'] = $offers;
        $data['merchant_id'] = $merchant_id;
        $data['centers'] = MerchantLocation::where('merchant_id', $merchant_id)->get(['id','name']);
        if($selectedOffers) {
            $data['selectedOffers'] = $selectedOffers;
        }
        return view('admin.merchant.product_list', $data);
    }

    public function addProduct(Request $request){
        if($request->get('product_id')){
            $product = Products::find($request->get('product_id'));

        }else{
            $product = new Products();
        }

        $message = 'Product updated successfully';
        $product->merchant_id = $request->has('merchant_id') ? $request->get('merchant_id'):'';
        $product->name = $request->get('product_name') ? $request->get('product_name'):'';
        $product->code = $request->get('product_code') ? $request->get('product_code'):'';
        $product->warranty = $request->get('product_warranty') ? $request->get('product_warranty'):'';
        $product->description = $request->get('product_description') ? $request->get('product_description'):'';
        $product->amount = $request->get('product_amount') ? $request->get('product_amount'):'';
        $product->updated_by = $request->get('user_id') ? $request->get('user_id'):'';
        $product->center_id = $request->get('center_id') ? $request->get('center_id') : 0;

        $product->save();
        if (isset($product->id)){
            \Session::flash('success', $message);
            return redirect()->back();
        }

        \Session::flash('error', 'Can\'t create product');
        return redirect()->back();
    }

    public function productDelete(Request $request){
        if($request->get('product_id')){
            $product = Products::where('id', $request->get('product_id'))->delete();

            if($product){
                return response()->json(['status' => 'OK', 'message' => 'Product deleted successfully'], 200);
            }
        }

        return response()->json(['status' => 'FAIL', 'message' => 'Can\'t delete Product'], 200);
    }

    public function linkOffers(Request $request){
        ProductOffers::where('product_id', $request->get('product_id'))->delete();

        if ($request->get('product_id') && $request->get('offer_selected')) {

            $offersLink = [];
            foreach ($request->get('offer_selected') as $offer_id) {
                $offersLink[] = ['product_id' => $request->get('product_id'), 'offer_id' => $offer_id, 'user_id' => $request->get('user_id'), 'created_at' => Carbon::now()->toDateTimeString()];
            }

            ProductOffers::insert($offersLink);

            return response()->json(['status' => 'OK', 'message' => 'Offers Linked Successfully'], 200);
        }

        return response()->json(['status'=>'Fail','message'=>'Offers are unlinked']);

    }


    public function copyAnchorProducts(Request $request, $id){
        $anchor_merchant_products = Products::where('merchant_id', $request->get('anchor_merchant_id'))->get();
        Products::where('merchant_id', $id)->delete();
        $result = [];
        foreach ($anchor_merchant_products as $product) {
            $result [] = [
                'merchant_id' => $id,
                'name' => $product->name,
                'code' => $product->code,
                'warranty' => $product->warranty,
                'description' => $product->description,
                'amount' => $product->amount,
                'updated_by' => $product->updated_by,
                'updated_at' => $product->updated_at
            ];
        }

        if ($result && $anchor_merchant_products) {
            Products::insert($result);
            return response()->json(['status' => 'OK', 'message' => 'Product(s) Copied!']);
        }else{
            return response()->json(['status' => 'FAIL', 'message' => 'No products available! Add products in Anchor merchant and try again.']);
        }
    }

    public function copyAnchorOffer(Request $request, $id){
        $merchantId = $request->get('anchor_merchant_id');
        if(isset($merchantId)){
            $anchorMerchantProducts = MerchantOffers::where('merchant_id', $merchantId)->where('status',1)->get()->toArray();
            MerchantOffers::where('merchant_id', $id)->delete();
            $created_by = $request->get('user_id');
            $date = Carbon::now()->toDateTimeString();
            try{
                if ($anchorMerchantProducts) {
                    // This is Yuvraj's Code kept commented for future refrence //
                    // $date = Carbon::now()->toDateTimeString();
                    // $anchor_merchant_products = array_map(function($offer) use ($id, $date, $created_by){
                    //     unset($offer['id']);
                    //     $offer['merchant_id'] = $id;
                    //     $offer['created_at'] = $date;
                    //     $offer['status'] = 0;
                    //     $offer['checked_by'] = 0;
                    //     $offer['created_by'] = $created_by;
                    //     $offer['updated_at'] = null;
                    //     return $offer;
                    // }, $anchor_merchant_products);
                    // MerchantOffers::insert($anchor_merchant_products);

                    foreach ($anchorMerchantProducts as $offer){
                        $data = $offer;
                        unset($data['id']);
                        $offers = new MerchantOffers();
                        $offers->fill($data);
                        $offers['merchant_id'] = $id;
                        $offers['created_at'] = $date;
                        $offers['status'] = 0;
                        $offers['checked_by'] = 0;
                        $offers['created_by'] = $created_by;
                        $offers['updated_at'] = null;
                        $offers->save();
                        $newOfferId = $offers->id;

                        $date = Carbon::now()->toDateTimeString();
                        $copyDisbursedOffers = DisbursementOffersData::where('offer_id', $offer['id'])->where('status',1)->get()->toArray();
                        foreach($copyDisbursedOffers as $copiedOffer){
                            unset($copiedOffer['id']);
                            $copiedOffer['offer_id'] = $newOfferId;
                            $copiedOffer['created_by'] = $created_by;
                            $copiedOffer['created_at'] = $date;
                            DisbursementOffersData::insert($copiedOffer);
                        }
                    }
                    return response()->json(['status' => 'OK', 'message' => 'Offer(s) Copied!']);
                }else{
                    return response()->json(['status' => 'FAIL', 'message' => 'No Offer available! Add Offer in Anchor merchant and try again.']);
                }
           }catch(Exception $e){
                return response()->json(['status' => 'FAIL', 'message' => 'Exception occurred while copying anchored offers of merchant']);
           }
        }else{
            return response()->json(['status' => 'FAIL', 'message' => 'Invalid Merchant ID']);
        }
    }

    public function downloadMISReport(Request $request){

        $from_date_clean = $request->has('from_date') ? $request->get('from_date') : '';
        $to_date_clean = $request->has('to_date') ? $request->get('to_date') : '';
        $user_id = $request->has('auth_user_id') ? $request->get('auth_user_id') : '';
        $auth_email = $request->get('auth_email');
        $source = $request->get('source');

        $from_date = $from_date_clean !== '' ? $from_date_clean.' 00:00:00' : '';
        $to_date = $to_date_clean !== '' ? $to_date_clean.' 23:59:59' : '';
        $records = [];
        $records1 = [];
        if($user_id != ''){
            $user = User::where('id',$user_id)->first();
            $merchant = Merchant::where('id',$user->merchant_id)->first(['merchant_name']);
        }
        $merchant_id = $user->merchant_id ?? Auth::user()->merchant_id;
        $center_id = $user->center_id ?? 0;
        $exective_id = $user->id ?? 0;

        if(($request->has('from_date') && $request->has('to_date')) || ($from_date !== '' && $to_date !== '')){

            $gstDetails = new ConsumerDocument(new Request());
            if($request->has('queue')){
                if($user->user_type == 3){
                    // $records = "select consumer_application.id as application_id, merchant.merchant_name, consumer_application.permanent_address_state, consumer_application.current_address_state, consumer_application.current_address_city, users.name as loginned_by, users_ads.name as loginned_by_ads, consumer_application.approved_amount, consumer_application.email, consumer_application.mobile_number, consumer_application.tenure, DATE_FORMAT(consumer_application.created_at, '%Y-%m-%d') as application_date, merchant_location.name as center, pro_forma_invoice.loan_application_no, consumer_application.name as customer_name, consumer_application.loan_amount, emi_calculation.upfront_from_customer as advance_amount, merchant_offers.slug as emi_plan, emi_calculation.upfront_from_credit_fair as disbursal_amount, emi_calculation.processing_fee as processing_fee, consumer_application.state,(select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'disbursed' limit 1) as disbursed_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'in-process' limit 1) as inprocess_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'cf approved' limit 1) as cf_approved_date,(select created_at from status_logs where status_logs.application_id = consumer_application.id and (status_logs.status = 'approved' OR status_logs.status = 'cf approved') limit 1) as approved_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'ready for disbursement' limit 1) as ready_for_disb_date, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'rejected' OR status_logs.status = 'cf approved')) as total_decision_cases, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'cf approved')) as total_approved_cases, consumer_application.approved_amount * (emi_calculation.subvention/100) as subvention_amount, emi_calculation.subvention_igst + emi_calculation.subvention_cgst + emi_calculation.subvention_sgst as gst_amount,(select JSON_EXTRACT(payout_preserve.response,\"$.result.rrn\") from payout_preserve where payout_preserve.payout_status_id = payout_status.id and payout_preserve.api = 'getDisbursementStatus' ORDER by created_at desc limit 1) as upi_number, consumer_application_element.reference_number, consumer_application_element.unique_id_number, (select response from avanse_disbursement_status where avanse_disbursement_status.application_id = consumer_application.id order by created_at desc limit 1) as avanse_disbursement_status, (select utr from cf_payout_direct_transfer where cf_payout_direct_transfer.application_id = consumer_application.id order by created_at desc limit 1) as cashfree_disbursement, (select adjusted_amount from disbursements where disbursements.application_id = consumer_application.id order by created_at desc limit 1) as adjust, liability_log.liability_percent as lib, merchant_offers.processing_fee_first_emi as processing_fee_first_emi, pro_forma_invoice.processing_fee_by_merchant as processing_fee_by_merchant, pro_forma_invoice.fldg_applied_category, consumer_application_element.product_name from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id LEFT JOIN users on users.id = consumer_application.src_representative_name LEFT JOIN users as users_ads on users_ads.id = consumer_application.created_by left join consumer_application_element on consumer_application_element.application_id = consumer_application.id left join merchant_location on merchant_location.id = consumer_application_element.center_id left join emi_calculation on emi_calculation.application_id = consumer_application.id left join merchant_offers on merchant_offers.merchant_id = consumer_application.merchant_id left join pro_forma_invoice on pro_forma_invoice.application_id = consumer_application.id left join payout_status on payout_status.application_id = consumer_application.id left join liability_log on liability_log.application_id = consumer_application.id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.merchant_location_id = $center_id and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                    $records = "SELECT ca.id AS application_id, m.merchant_name, ca.permanent_address_state, ca.current_address_state, ca.current_address_city, u.name AS loginned_by, ua.name AS loginned_by_ads, ca.approved_amount, ca.email, ca.mobile_number, ca.tenure, DATE_FORMAT(ca.created_at, '%Y-%m-%d') AS application_date, ml.name AS center, pfi.loan_application_no, ca.name AS customer_name, ca.loan_amount, ec.upfront_from_customer AS advance_amount, mo.slug AS emi_plan, ec.upfront_from_credit_fair AS disbursal_amount, ec.processing_fee AS processing_fee, ca.state, sl_disbursed.created_at AS disbursed_date, sl_in_process.created_at AS inprocess_date, sl_cf_approved.created_at AS cf_approved_date, sl_approved.created_at AS approved_date, sl_ready_for_disb.created_at AS ready_for_disb_date, COALESCE(decision_cases.total_decision_cases, 0) AS total_decision_cases, COALESCE(approved_cases.total_approved_cases, 0) AS total_approved_cases, ca.approved_amount * (ec.subvention / 100) AS subvention_amount, ec.subvention_igst + ec.subvention_cgst + ec.subvention_sgst AS gst_amount, lp.upi_number, cae.reference_number, cae.unique_id_number, las.response AS avanse_disbursement_status, lcd.utr AS cashfree_disbursement, ld.adjusted_amount AS adjust, ll.liability_percent AS lib, mo.processing_fee_first_emi AS processing_fee_first_emi, pfi.processing_fee_by_merchant AS processing_fee_by_merchant, pfi.fldg_applied_category, cae.product_name FROM consumer_application ca LEFT JOIN merchant m ON m.id = ca.merchant_id LEFT JOIN users u ON u.id = ca.src_representative_name LEFT JOIN users ua ON ua.id = ca.created_by LEFT JOIN consumer_application_element cae ON cae.application_id = ca.id LEFT JOIN merchant_location ml ON ml.id = cae.center_id LEFT JOIN emi_calculation ec ON ec.application_id = ca.id LEFT JOIN merchant_offers mo ON mo.merchant_id = ca.merchant_id LEFT JOIN pro_forma_invoice pfi ON pfi.application_id = ca.id LEFT JOIN payout_status ps ON ps.application_id = ca.id LEFT JOIN liability_log ll ON ll.application_id = ca.id LEFT JOIN status_logs sl_disbursed ON sl_disbursed.application_id = ca.id AND sl_disbursed.status = 'disbursed' LEFT JOIN status_logs sl_in_process ON sl_in_process.application_id = ca.id AND sl_in_process.status = 'in-process' LEFT JOIN status_logs sl_cf_approved ON sl_cf_approved.application_id = ca.id AND sl_cf_approved.status = 'cf approved' LEFT JOIN status_logs sl_approved ON sl_approved.application_id = ca.id AND (sl_approved.status = 'approved' OR sl_approved.status = 'cf approved') LEFT JOIN status_logs sl_ready_for_disb ON sl_ready_for_disb.application_id = ca.id AND sl_ready_for_disb.status = 'ready for disbursement' LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_decision_cases FROM status_logs WHERE status IN ('approved', 'rejected', 'cf approved') GROUP BY application_id) AS decision_cases ON decision_cases.application_id = ca.id LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_approved_cases FROM status_logs WHERE status IN ('approved', 'cf approved') GROUP BY application_id) AS approved_cases ON approved_cases.application_id = ca.id LEFT JOIN (SELECT payout_status_id, JSON_EXTRACT(response, '$.result.rrn') AS upi_number FROM payout_preserve WHERE payout_preserve.api = 'getDisbursementStatus' ORDER BY created_at DESC LIMIT 1) AS lp ON lp.payout_status_id = ps.id LEFT JOIN (SELECT application_id, response FROM avanse_disbursement_status ORDER BY created_at DESC LIMIT 1) AS las ON las.application_id = ca.id LEFT JOIN (SELECT application_id, utr FROM cf_payout_direct_transfer ORDER BY created_at DESC LIMIT 1) AS lcd ON lcd.application_id = ca.id LEFT JOIN (SELECT application_id, adjusted_amount FROM disbursements ORDER BY created_at DESC LIMIT 1) AS ld ON ld.application_id = ca.id WHERE ca.is_deleted IS NULL AND (m.id = $merchant_id OR m.anchor_merchant_id = $merchant_id) AND ca.merchant_location_id = $center_id AND ca.created_at >= '" . $from_date . "' AND ca.created_at <= '" . $to_date . "';";
                }

                if($user->user_type == 2){
                    // $records = "select consumer_application.id as application_id, merchant.merchant_name, consumer_application.permanent_address_state, consumer_application.current_address_state, consumer_application.current_address_city, lenders.name as lender_name, users.name as loginned_by, users_ads.name as loginned_by_ads, consumer_application.approved_amount, consumer_application.email, consumer_application.mobile_number, consumer_application.tenure, DATE_FORMAT(consumer_application.created_at, '%Y-%m-%d') as application_date, merchant_location.name as center, pro_forma_invoice.loan_application_no, consumer_application.name as customer_name, consumer_application.loan_amount, emi_calculation.upfront_from_customer as advance_amount, merchant_offers.slug as emi_plan, emi_calculation.upfront_from_credit_fair as disbursal_amount, emi_calculation.processing_fee as processing_fee, consumer_application.state,(select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'disbursed' limit 1) as disbursed_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'in-process' limit 1) as inprocess_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'cf approved' limit 1) as cf_approved_date,(select created_at from status_logs where status_logs.application_id = consumer_application.id and (status_logs.status = 'approved' OR status_logs.status = 'cf approved') limit 1) as approved_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'ready for disbursement' limit 1) as ready_for_disb_date, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'rejected' OR status_logs.status = 'cf approved')) as total_decision_cases, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'cf approved')) as total_approved_cases, consumer_application.approved_amount * (emi_calculation.subvention/100) as subvention_amount, emi_calculation.subvention_igst + emi_calculation.subvention_cgst + emi_calculation.subvention_sgst as gst_amount,(select JSON_EXTRACT(payout_preserve.response,\"$.result.rrn\") from payout_preserve where payout_preserve.payout_status_id = payout_status.id and payout_preserve.api = 'getDisbursementStatus' ORDER by created_at desc limit 1) as upi_number, consumer_application_element.reference_number, consumer_application_element.unique_id_number, (select response from avanse_disbursement_status where avanse_disbursement_status.application_id = consumer_application.id order by created_at desc limit 1) as avanse_disbursement_status, (select utr from cf_payout_direct_transfer where cf_payout_direct_transfer.application_id = consumer_application.id order by created_at desc limit 1) as cashfree_disbursement, (select adjusted_amount from disbursements where disbursements.application_id = consumer_application.id order by created_at desc limit 1) as adjust, liability_log.liability_percent as lib, merchant_offers.processing_fee_first_emi as processing_fee_first_emi, pro_forma_invoice.processing_fee_by_merchant as processing_fee_by_merchant, pro_forma_invoice.fldg_applied_category, consumer_application_element.product_name from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id LEFT JOIN lenders on lenders.id = consumer_application.lender_id LEFT JOIN users on users.id = consumer_application.src_representative_name LEFT JOIN users as users_ads on users_ads.id = consumer_application.created_by left join consumer_application_element on consumer_application_element.application_id = consumer_application.id left join merchant_location on merchant_location.id = consumer_application_element.center_id left join emi_calculation on emi_calculation.application_id = consumer_application.id left join merchant_offers on merchant_offers.id = consumer_application.offer_id left join pro_forma_invoice on pro_forma_invoice.application_id = consumer_application.id left join payout_status on payout_status.application_id = consumer_application.id left join liability_log on liability_log.application_id = consumer_application.id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                    $records = "SELECT ca.id AS application_id, m.merchant_name, ca.permanent_address_state, ca.current_address_state, ca.current_address_city, l.name AS lender_name, u.name AS loginned_by, ua.name AS loginned_by_ads, ca.approved_amount, ca.email, ca.mobile_number, ca.tenure, DATE_FORMAT(ca.created_at, '%Y-%m-%d') AS application_date, ml.name AS center, pfi.loan_application_no, ca.name AS customer_name, ca.loan_amount, ec.upfront_from_customer AS advance_amount, mo.slug AS emi_plan, ec.upfront_from_credit_fair AS disbursal_amount, ec.processing_fee AS processing_fee, ca.state, sl_disbursed.created_at AS disbursed_date, sl_in_process.created_at AS inprocess_date, sl_cf_approved.created_at AS cf_approved_date, sl_approved.created_at AS approved_date, sl_ready_for_disb.created_at AS ready_for_disb_date, COALESCE(decision_cases.total_decision_cases, 0) AS total_decision_cases, COALESCE(approved_cases.total_approved_cases, 0) AS total_approved_cases, ca.approved_amount * (ec.subvention / 100) AS subvention_amount, ec.subvention_igst + ec.subvention_cgst + ec.subvention_sgst AS gst_amount, lp.upi_number, cae.reference_number, cae.unique_id_number, las.response AS avanse_disbursement_status, lcd.utr AS cashfree_disbursement, ld.adjusted_amount AS adjust, ll.liability_percent AS lib, mo.processing_fee_first_emi AS processing_fee_first_emi, pfi.processing_fee_by_merchant AS processing_fee_by_merchant, pfi.fldg_applied_category, cae.product_name FROM consumer_application ca LEFT JOIN merchant m ON m.id = ca.merchant_id LEFT JOIN lenders l ON l.id = ca.lender_id LEFT JOIN users u ON u.id = ca.src_representative_name LEFT JOIN users ua ON ua.id = ca.created_by LEFT JOIN consumer_application_element cae ON cae.application_id = ca.id LEFT JOIN merchant_location ml ON ml.id = cae.center_id LEFT JOIN emi_calculation ec ON ec.application_id = ca.id LEFT JOIN merchant_offers mo ON mo.id = ca.offer_id LEFT JOIN pro_forma_invoice pfi ON pfi.application_id = ca.id LEFT JOIN payout_status ps ON ps.application_id = ca.id LEFT JOIN liability_log ll ON ll.application_id = ca.id LEFT JOIN status_logs sl_disbursed ON sl_disbursed.application_id = ca.id AND sl_disbursed.status = 'disbursed' LEFT JOIN status_logs sl_in_process ON sl_in_process.application_id = ca.id AND sl_in_process.status = 'in-process' LEFT JOIN status_logs sl_cf_approved ON sl_cf_approved.application_id = ca.id AND sl_cf_approved.status = 'cf approved' LEFT JOIN status_logs sl_approved ON sl_approved.application_id = ca.id AND (sl_approved.status = 'approved' OR sl_approved.status = 'cf approved') LEFT JOIN status_logs sl_ready_for_disb ON sl_ready_for_disb.application_id = ca.id AND sl_ready_for_disb.status = 'ready for disbursement' LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_decision_cases FROM status_logs WHERE status IN ('approved', 'rejected', 'cf approved') GROUP BY application_id) AS decision_cases ON decision_cases.application_id = ca.id LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_approved_cases FROM status_logs WHERE status IN ('approved', 'cf approved') GROUP BY application_id) AS approved_cases ON approved_cases.application_id = ca.id LEFT JOIN (SELECT payout_status_id, JSON_EXTRACT(response, '$.result.rrn') AS upi_number FROM payout_preserve WHERE payout_preserve.api = 'getDisbursementStatus' ORDER BY created_at DESC LIMIT 1) AS lp ON lp.payout_status_id = ps.id LEFT JOIN (SELECT application_id, response FROM avanse_disbursement_status ORDER BY created_at DESC LIMIT 1) AS las ON las.application_id = ca.id LEFT JOIN (SELECT application_id, utr FROM cf_payout_direct_transfer ORDER BY created_at DESC LIMIT 1) AS lcd ON lcd.application_id = ca.id LEFT JOIN (SELECT application_id, adjusted_amount FROM disbursements ORDER BY created_at DESC LIMIT 1) AS ld ON ld.application_id = ca.id WHERE ca.is_deleted IS NULL AND (m.id = $merchant_id OR m.anchor_merchant_id = $merchant_id) AND ca.created_at >= '" . $from_date . "' AND ca.created_at <= '" . $to_date . "';";
                }

                if($user->user_type == 4){
                    // $records = "select consumer_application.id as application_id, merchant.merchant_name, consumer_application.permanent_address_state, consumer_application.current_address_state, consumer_application.current_address_city, users.name as loginned_by, users_ads.name as loginned_by_ads, consumer_application.approved_amount, consumer_application.email, consumer_application.mobile_number, consumer_application.tenure, DATE_FORMAT(consumer_application.created_at, '%Y-%m-%d') as application_date, merchant_location.name as center, pro_forma_invoice.loan_application_no, consumer_application.name as customer_name, consumer_application.loan_amount, emi_calculation.upfront_from_customer as advance_amount, merchant_offers.slug as emi_plan, emi_calculation.upfront_from_credit_fair as disbursal_amount, emi_calculation.processing_fee as processing_fee, consumer_application.state,(select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'disbursed' limit 1) as disbursed_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'in-process' limit 1) as inprocess_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'cf approved' limit 1) as cf_approved_date,(select created_at from status_logs where status_logs.application_id = consumer_application.id and (status_logs.status = 'approved' OR status_logs.status = 'cf approved') limit 1) as approved_date, (select created_at from status_logs where status_logs.application_id = consumer_application.id and status_logs.status = 'ready for disbursement' limit 1) as ready_for_disb_date, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'rejected' OR status_logs.status = 'cf approved')) as total_decision_cases, (select count(DISTINCT application_id) from status_logs where status_logs.application_id = consumer_application.id AND (status_logs.status = 'approved' OR status_logs.status = 'cf approved')) as total_approved_cases, consumer_application.approved_amount * (emi_calculation.subvention/100) as subvention_amount, emi_calculation.subvention_igst + emi_calculation.subvention_cgst + emi_calculation.subvention_sgst as gst_amount,(select JSON_EXTRACT(payout_preserve.response,\"$.result.rrn\") from payout_preserve where payout_preserve.payout_status_id = payout_status.id and payout_preserve.api = 'getDisbursementStatus' ORDER by created_at desc limit 1) as upi_number, consumer_application_element.reference_number, consumer_application_element.unique_id_number, (select response from avanse_disbursement_status where avanse_disbursement_status.application_id = consumer_application.id order by created_at desc limit 1) as avanse_disbursement_status, (select utr from cf_payout_direct_transfer where cf_payout_direct_transfer.application_id = consumer_application.id order by created_at desc limit 1) as cashfree_disbursement, (select adjusted_amount from disbursements where disbursements.application_id = consumer_application.id order by created_at desc limit 1) as adjust, liability_log.liability_percent as lib, merchant_offers.processing_fee_first_emi as processing_fee_first_emi, pro_forma_invoice.processing_fee_by_merchant as processing_fee_by_merchant, pro_forma_invoice.fldg_applied_category, consumer_application_element.product_name from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id LEFT JOIN users on users.id = consumer_application.src_representative_name LEFT JOIN users as users_ads on users_ads.id = consumer_application.created_by left join consumer_application_element on consumer_application_element.application_id = consumer_application.id left join merchant_location on merchant_location.id = consumer_application_element.center_id left join emi_calculation on emi_calculation.application_id = consumer_application.id left join merchant_offers on merchant_offers.id = consumer_application.offer_id left join pro_forma_invoice on pro_forma_invoice.application_id = consumer_application.id left join payout_status on payout_status.application_id = consumer_application.id left join liability_log on liability_log.application_id = consumer_application.id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.src_representative_name = $exective_id and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                    $records = "SELECT ca.id AS application_id, m.merchant_name, ca.permanent_address_state, ca.current_address_state, ca.current_address_city, u.name AS loginned_by, ua.name AS loginned_by_ads, ca.approved_amount, ca.email, ca.mobile_number, ca.tenure, DATE_FORMAT(ca.created_at, '%Y-%m-%d') AS application_date, ml.name AS center, pfi.loan_application_no, ca.name AS customer_name, ca.loan_amount, ec.upfront_from_customer AS advance_amount, mo.slug AS emi_plan, ec.upfront_from_credit_fair AS disbursal_amount, ec.processing_fee AS processing_fee, ca.state, sl_disbursed.created_at AS disbursed_date, sl_in_process.created_at AS inprocess_date, sl_cf_approved.created_at AS cf_approved_date, sl_approved.created_at AS approved_date, sl_ready_for_disb.created_at AS ready_for_disb_date, COALESCE(decision_cases.total_decision_cases, 0) AS total_decision_cases, COALESCE(approved_cases.total_approved_cases, 0) AS total_approved_cases, ca.approved_amount * (ec.subvention / 100) AS subvention_amount, ec.subvention_igst + ec.subvention_cgst + ec.subvention_sgst AS gst_amount, lp.upi_number, cae.reference_number, cae.unique_id_number, las.response AS avanse_disbursement_status, lcd.utr AS cashfree_disbursement, ld.adjusted_amount AS adjust, ll.liability_percent AS lib, mo.processing_fee_first_emi AS processing_fee_first_emi, pfi.processing_fee_by_merchant AS processing_fee_by_merchant, pfi.fldg_applied_category, cae.product_name FROM consumer_application ca LEFT JOIN merchant m ON m.id = ca.merchant_id LEFT JOIN users u ON u.id = ca.src_representative_name LEFT JOIN users ua ON ua.id = ca.created_by LEFT JOIN consumer_application_element cae ON cae.application_id = ca.id LEFT JOIN merchant_location ml ON ml.id = cae.center_id LEFT JOIN emi_calculation ec ON ec.application_id = ca.id LEFT JOIN merchant_offers mo ON mo.id = ca.offer_id LEFT JOIN pro_forma_invoice pfi ON pfi.application_id = ca.id LEFT JOIN payout_status ps ON ps.application_id = ca.id LEFT JOIN liability_log ll ON ll.application_id = ca.id LEFT JOIN status_logs sl_disbursed ON sl_disbursed.application_id = ca.id AND sl_disbursed.status = 'disbursed' LEFT JOIN status_logs sl_in_process ON sl_in_process.application_id = ca.id AND sl_in_process.status = 'in-process' LEFT JOIN status_logs sl_cf_approved ON sl_cf_approved.application_id = ca.id AND sl_cf_approved.status = 'cf approved' LEFT JOIN status_logs sl_approved ON sl_approved.application_id = ca.id AND (sl_approved.status = 'approved' OR sl_approved.status = 'cf approved') LEFT JOIN status_logs sl_ready_for_disb ON sl_ready_for_disb.application_id = ca.id AND sl_ready_for_disb.status = 'ready for disbursement' LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_decision_cases FROM status_logs WHERE status IN ('approved', 'rejected', 'cf approved') GROUP BY application_id) AS decision_cases ON decision_cases.application_id = ca.id LEFT JOIN (SELECT application_id, COUNT(DISTINCT application_id) AS total_approved_cases FROM status_logs WHERE status IN ('approved', 'cf approved') GROUP BY application_id) AS approved_cases ON approved_cases.application_id = ca.id LEFT JOIN (SELECT payout_status_id, JSON_EXTRACT(response, '$.result.rrn') AS upi_number FROM payout_preserve WHERE payout_preserve.api = 'getDisbursementStatus' ORDER BY created_at DESC LIMIT 1) AS lp ON lp.payout_status_id = ps.id LEFT JOIN (SELECT application_id, response FROM avanse_disbursement_status ORDER BY created_at DESC LIMIT 1) AS las ON las.application_id = ca.id LEFT JOIN (SELECT application_id, utr FROM cf_payout_direct_transfer ORDER BY created_at DESC LIMIT 1) AS lcd ON lcd.application_id = ca.id LEFT JOIN (SELECT application_id, adjusted_amount FROM disbursements ORDER BY created_at DESC LIMIT 1) AS ld ON ld.application_id = ca.id WHERE ca.is_deleted IS NULL AND (m.id = $merchant_id OR m.anchor_merchant_id = $merchant_id) AND ca.src_representative_name = $exective_id AND ca.created_at >= '" . $from_date . "' AND ca.created_at <= '" . $to_date . "';";
                }
                $records = DB::select($records);
            }else{
                if(Auth::user()->user_type == 3){
                    $records1 = "Select count(consumer_application.id) from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.merchant_location_id = $center_id and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                }
                if(Auth::user()->user_type == 2){
                    $records1 = "Select count(consumer_application.id) from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                }
                if(Auth::user()->user_type == 4){
                    $records1 = "Select count(consumer_application.id) from consumer_application LEFT JOIN merchant on merchant.id = consumer_application.merchant_id where consumer_application.is_deleted is null and (merchant.id = $merchant_id || merchant.anchor_merchant_id = $merchant_id) and consumer_application.src_representative_name = $exective_id and consumer_application.created_at >= '" . $from_date . "' and consumer_application.created_at <= '" . $to_date . "';";
                }
                $records1 = DB::select($records1);
                $count_records1 = count(array($records1[0]));
                $array = json_decode(json_encode($records1), true);
            }
            if(isset($array) && $array[0]['count(consumer_application.id)'] > 0) {
                if (!$request->has('queue')){
                    $jobProvider = JobProvider::where('application_id', $merchant_id)->where('from', 'getMISReport')->where('is_available', 0)->first(['id']);

                    if (isset($jobProvider->id)){
                        \Session::flash('error', 'There is an active queue for the MIS report. Please try again after sometime');
                        return redirect()->back();
                    }
                    if ((Auth::check() && $userAccEmail = Auth::user()->email)){
                        $auth_user_id = $user_id != '' ? $user_id : (Auth::check() ? Auth::user()->id : Input::get('auth_user_id'));
                        $auth_email = $userAccEmail;

                        $payload = [
                            'payload' => json_encode($request->merge(['source' => 'merchantAdminPanel','queue' => true, 'merchant_id' => $merchant_id, 'auth_user_id' => $auth_user_id, 'auth_email' => $auth_email])->all()),
                            'from' => 'downloadMISReport',
                            'application_id' => $merchant_id,
                            'available_at' => Carbon::now()->toDateTimeString(),
                            'created_at' => Carbon::now()->toDateTimeString()
                        ];
                        JobProvider::insert($payload);

                        \Session::flash('error', 'You will received the MIS data shortly over the email');
                        return redirect()->back();
                    }
                    \Session::flash('error', 'Please add the email id to your account to receive the mail');
                    return redirect()->back();
                }
            }else{
                if(!$request->has('queue')){
                    \Session::flash('error', 'Data for the provided Duration is not present in the System, please change the Start and End Date');
                    return redirect()->back();
                }
            }

            $applications = [];
            $total_decision_cases = 0;
            $total_approved_cases = 0;
            $tat_avg = [];
            $tat_disb_appr_avg = [];
            $tat_utr_avg = [];
            $disb_24hrs = 0;
            $dash_obj = new Dashboard($request);
            $states = ['disbursed','nach registered', 'closed'];
            if ($user->user_type == 2) {
                $response[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date','Merchant Name', 'Merchant Location Name', "Lender Name", 'Customer Name', 'City', 'State', 'Login By', 'Mobile', 'Email', 'Product Name', 'Loan Amount', 'Tenure', 'Processing Fee', 'Advance Amount', 'Gross Disbursal amount (without GST)','Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Status', 'Disbursed Date', 'Subvention Amount', 'GST Amount', 'UTR Number', 'Adjusted', 'FLDG', 'Liability','TAT of approval','TAT of approval - Disbursement','TAT of sharing the UTR'];
            }else{
                $response[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date','Merchant Name', 'Merchant Location Name', 'Customer Name', 'City', 'State', 'Login By', 'Mobile', 'Email', 'Product Name', 'Loan Amount', 'Tenure', 'Processing Fee', 'Advance Amount', 'Gross Disbursal amount (without GST)','Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Status', 'Disbursed Date', 'Subvention Amount', 'GST Amount', 'UTR Number', 'Adjusted', 'FLDG', 'Liability','TAT of approval','TAT of approval - Disbursement','TAT of sharing the UTR'];
            }

            $partialDisbursement[] = ['ID', 'Unique ID', 'Loan Application Number', 'Application Date', 'Merchant Name', 'Customer Name', 'Customer Email', 'City', 'State', 'Login By', 'Product Name', 'Procesing Fee','Approved Amount', 'Advance Amount', 'Disbursal Amount Phase 1', 'Subvention Amount	Phase 1', 'GST Amount Phase 1', 'Disbursal Amount Phase 2', 'Subvention Amount Phase 2','Amount transferred after adjusting for GST & Processing Fees (If any collected by merchant)', 'Disbursement Date Phase 2', 'GST Phase 2', 'Disbursal Amount Phase 3', 'Subvention Amount Phase 3', 'Disbursement Date Phase 3', 'GST Phase 3', 'Status', 'Disbursed Date', 'UTR Number', 'Adjusted', 'FLDG', 'Liability','TAT of approval','TAT of approval - Disbursement','TAT of sharing the UTR','Customer Mobile Number'];
            $performanceSummary[] = ['Total Cases', 'Total Cases where decision was given', 'Total Approved Cases', 'Approval %', 'TAT of approval', 'TAT of approval - Disbursement', 'TAT of sharing the UTR', 'No. of cases disbursed within 24 hours'];
            foreach($records as $record) {
                if(!in_array($record->application_id, $applications)) {

                    $av_transactionRef = '';
                    if($record->avanse_disbursement_status){
                        $avanseUTRStatusResponse = json_decode($record->avanse_disbursement_status);
                        foreach ($avanseUTRStatusResponse->disbResponse as $disbResponse){
                            $av_transactionRef = $disbResponse->transactionRef ?? '';
                        }
                    }

                    if (empty($av_transactionRef) && $record->cashfree_disbursement){
                        $av_transactionRef = $record->cashfree_disbursement;
                    }

                    $applications[] = $record->application_id;
                    $total_decision_cases += $record->total_decision_cases;
                    $total_approved_cases += $record->total_approved_cases;
                    $responseData[$record->application_id] = $record;
                    $tat = '';
                    if($record->approved_date != NULL && $record->inprocess_date != NULL){
                        if($record->cf_approved_date){
                            $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $record->cf_approved_date);
                        }else{
                            $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $record->approved_date);
                        }
                        $date_from = Carbon::createFromFormat('Y-m-d H:i:s', $record->inprocess_date);
                        $tat = $dash_obj->secondsToDays($date_to->diffInSeconds($date_from));
                        $tat_avg[] = $date_to->diffInSeconds($date_from);
                    }else{
                        $tat = '-';
                    }
                    if($record->approved_date != NULL && $record->ready_for_disb_date != NULL){
                        $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $record->approved_date);
                        $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $record->ready_for_disb_date);
                        $tat_disb_appr = $dash_obj->secondsToDays($date_ready_for_disb->diffInSeconds($date_to));
                        $tat_disb_appr_avg[] = $date_ready_for_disb->diffInSeconds($date_to);
                    }else{
                        $tat_disb_appr = '-';
                    }
                    if($record->ready_for_disb_date != NULL && $record->disbursed_date != NULL){
                        $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $record->ready_for_disb_date);
                        $disb_date = Carbon::createFromFormat('Y-m-d H:i:s', $record->disbursed_date);
                        $tat_utr = $dash_obj->secondsToDays($disb_date->diffInSeconds($date_ready_for_disb));
                        $tat_utr_avg[] = $disb_date->diffInSeconds($date_ready_for_disb);
                    }else{
                        $tat_utr = '-';
                    }
                    if($record->ready_for_disb_date != NULL && $record->inprocess_date != NULL){
                        $date_ready_for_disb1 = Carbon::createFromFormat('Y-m-d H:i:s', $record->ready_for_disb_date);
                        $date_from1 = Carbon::createFromFormat('Y-m-d H:i:s', $record->inprocess_date);
                        $date_diff = $date_ready_for_disb1->diffInHours($date_from1);
                        if($date_diff <= 24){
                            $disb_24hrs++;
                        }
                    }
                    if(isset($record->adjust) && ($record->adjust) != 0){
                        $adjusted = true;
                        $utr_no = $record->upi_number ? $record->upi_number : ($record->reference_number ? $record->reference_number : $av_transactionRef);

                    } else {
                        $adjusted = false;
                        $utr_no = $record->upi_number ? $record->upi_number : ($record->reference_number ? $record->reference_number : $av_transactionRef);
                    }

                    $amount = in_array($record->state, $states) ? ($record->disbursal_amount - $record->gst_amount) : null;

                    if ($record->processing_fee && $record->processing_fee_by_merchant == 1){
                        $amount -= $record->processing_fee;
                    }

                    if($record->processing_fee_first_emi){
                        $amount += $record->processing_fee;
                    }
                    $product_name = $record->product_name ? $record->product_name : '';
                    $applicationController = new ConsumerApplicationController();
                    $product_name = $applicationController->getProductName($product_name);
                    $itemData = [
                        'id' => $record->application_id,
                        'unique_id' => $record->unique_id_number,
                        'loan_application_number' => $record->loan_application_no,
                        'application_date' => $record->application_date,
                        'merchant_name' => $record->merchant_name,
                        'center' => $record->center,
                        'customer_name' => $record->customer_name,
                        'current_city' => $record->current_address_city,
                        'current_state' => $record->current_address_state,
                        'login_by_user' => $record->loginned_by ? $record->loginned_by : $record->loginned_by_ads,
                        'customer number' => wall_decrypt($record->mobile_number),
                        'email' => wall_decrypt($record->email),
                        'product_name' =>$product_name,
                        'loan_amount' => $record->loan_amount,
                        'tenure' => $record->tenure,
                        'processing_fee' => $record->processing_fee,
                        'advance_amount' => in_array($record->state, $states) ? $record->advance_amount : null,
                        // 'emi_plan' => $record->emi_plan,
                        'disbursal_amount' => in_array($record->state, $states) ? $record->disbursal_amount : null,
                        'net_disb_amount' => in_array($record->state, $states) ? $amount : null,
                        'state' => $record->state,
                        'disbursal_date' => $record->disbursed_date,
                        'subvention_amount' => in_array($record->state, $states) ? $record->subvention_amount : null,
                        'gst_amount' => in_array($record->state, $states) ? $record->gst_amount : null,
                        // 'upi_number' => $record->upi_number ? $record->upi_number : ($record->reference_number ? $record->reference_number : $av_transactionRef),
                        'UTR_number' => !empty($utr_no) ? "'".$utr_no."'" : null,
                        'Adjusted' => ($adjusted) ? 'Yes' : 'No',
                        // 'UTR_number' =>  in_array($record->state, $states) ? $utr_no : null,
                        'fldg' => $record->fldg_applied_category ? 'FLDG' : 'Without FLDG',
                        'hold' => $record->lib ? 'Liability' : 'Without Liability',
                        'tat_of_approval' => empty($tat) ? '0 Minutes' : $tat,
                        'tat_of_approval_disbursement' => empty($tat_disb_appr) ? '0 Minutes' :$tat_disb_appr,
                        'tat_of_sharing_utr' => empty($tat_utr) ? '0 Minutes' : $tat_utr
                    ];
                    if ($user->user_type == 2) {
                        // Append a new key-value pair to the $itemData array
                        $newKeyValuePair = ['lender_name' => $record->lender_name];
                        array_splice($itemData, 6, 0, $newKeyValuePair);
                    }
                    $response [] = $itemData;
                }
            }

            if ($applications){

                $disbursementLogs = DisbursementLogs::whereIn('application_id', $applications)->get();
                $responseDataPartial = [];
                foreach ($disbursementLogs as $disbursementLog){
                    $responseDataPartial[$disbursementLog->application_id][] = $disbursementLog;
                }

                foreach ($responseDataPartial as $log) {
                    $phase1_disb_amount = 0; $phase1_sub_amount = 0; $phase1_date = ''; $phase1_gst = 0;
                    $phase2_disb_amount = 0; $phase2_sub_amount = 0; $phase2_date = ''; $phase2_gst = 0;
                    $phase3_disb_amount = 0; $phase3_sub_amount = 0; $phase3_date = ''; $phase3_gst = 0;
                    $phase4_disb_amount = 0; $phase4_sub_amount = 0; $phase4_date = ''; $phase4_gst = 0;

                    foreach ($log as $item){
                        if ($item->phase == 1){
                            $phase1_disb_amount = $item->disbursement_amount;
                            $phase1_sub_amount = $item->partial_subvention_amount;
                            $phase1_date = $item->created_at;

                            $gstDetailsData = $gstDetails->getGSTDetails(
                                [
                                    'amount' => $phase1_sub_amount,
                                    'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                ]
                            );

                            foreach ($gstDetailsData as $gstItem){
                                $phase1_gst += $gstItem['gst_amount'];
                            }

                        }elseif ($item->phase == 2){
                            $phase2_disb_amount = $item->disbursement_amount;
                            $phase2_sub_amount = $item->partial_subvention_amount;
                            $phase2_date = $item->created_at;

                            $gstDetailsData = $gstDetails->getGSTDetails(
                                [
                                    'amount' => $phase2_sub_amount,
                                    'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                ]
                            );

                            foreach ($gstDetailsData as $gstItem){
                                $phase2_gst += $gstItem['gst_amount'];
                            }

                        }elseif ($item->phase == 3){
                            $phase3_disb_amount = $item->disbursement_amount;
                            $phase3_sub_amount = $item->partial_subvention_amount;
                            $phase3_date = $item->created_at;

                            $gstDetailsData = $gstDetails->getGSTDetails(
                                [
                                    'amount' => $phase3_sub_amount,
                                    'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                ]
                            );

                            foreach ($gstDetailsData as $gstItem){
                                $phase2_gst += $gstItem['gst_amount'];
                            }

                        }elseif ($item->phase == 4){
                            $phase4_disb_amount = $item->disbursement_amount;
                            $phase4_sub_amount = $item->partial_subvention_amount;
                            $phase4_date = $item->created_at;

                            $gstDetailsData = $gstDetails->getGSTDetails(
                                [
                                    'amount' => $phase4_sub_amount,
                                    'state' => (isset($responseData[$item->application_id]->permanent_address_state) && !empty($responseData[$item->application_id]->permanent_address_state)) ? $responseData[$item->application_id]->permanent_address_state : (isset($responseData[$item->application_id]->current_address_state) && !empty($responseData[$item->application_id]->current_address_state) ? $responseData[$item->application_id]->current_address_state : '')
                                ]
                            );

                            foreach ($gstDetailsData as $gstItem){
                                $phase4_gst += $gstItem['gst_amount'];
                            }
                        }
                    }

                    $log = $log[0];

                    $av_transactionRef = '';
                    if($responseData[$log->application_id]->avanse_disbursement_status){
                        $avanseUTRStatusResponse = json_decode($responseData[$log->application_id]->avanse_disbursement_status);
                        foreach ($avanseUTRStatusResponse->disbResponse as $disbResponse){
                            $av_transactionRef = $disbResponse->transactionRef ?? '';
                        }
                    }

                    if (empty($av_transactionRef) && $responseData[$log->application_id]->cashfree_disbursement){
                        $av_transactionRef = $responseData[$log->application_id]->cashfree_disbursement;
                    }

                    if($responseData[$log->application_id]->approved_date != NULL && $responseData[$log->application_id]->inprocess_date != NULL){
                        $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->approved_date);
                        $date_from = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->inprocess_date);
                        $tat = $dash_obj->secondsToDays($date_to->diffInSeconds($date_from));
                    }else{
                        $tat = '-';
                    }
                    if($responseData[$log->application_id]->approved_date != NULL && $responseData[$log->application_id]->ready_for_disb_date != NULL){
                        $date_to = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->approved_date);
                        $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->ready_for_disb_date);
                        $tat_disb_appr = $dash_obj->secondsToDays($date_ready_for_disb->diffInSeconds($date_to));
                    }else{
                        $tat_disb_appr = '-';
                    }
                    if($responseData[$log->application_id]->ready_for_disb_date != NULL && $phase1_date != NULL){
                        $date_ready_for_disb = Carbon::createFromFormat('Y-m-d H:i:s', $responseData[$log->application_id]->ready_for_disb_date);
                        $disb_date = Carbon::createFromFormat('Y-m-d H:i:s', $phase1_date);
                        $tat_utr = $dash_obj->secondsToDays($disb_date->diffInSeconds($date_ready_for_disb));
                        $tat_utr_avg[] = $disb_date->diffInSeconds($date_ready_for_disb);
                    }else{
                        $tat_utr = '-';
                    }
                    if(isset($responseData[$log->application_id]->adjust) && $responseData[$log->application_id]->adjust != 0){
                        $adjusted = true;
                        $utr_no = $responseData[$log->application_id]->upi_number ? $responseData[$log->application_id]->upi_number : ($responseData[$log->application_id]->reference_number ? $responseData[$log->application_id]->reference_number : $av_transactionRef);
                    } else {
                        $adjusted = false;
                        $utr_no = $responseData[$log->application_id]->upi_number ? $responseData[$log->application_id]->upi_number : ($responseData[$log->application_id]->reference_number ? $responseData[$log->application_id]->reference_number : $av_transactionRef);
                        // $utr_no = $responseData[$log->application_id]->cashfree_disbursement;
                    }

                    $disbamount = in_array($responseData[$log->application_id]->state, $states) ? $responseData[$log->application_id]->disbursal_amount - $responseData[$log->application_id]->gst_amount : null;

                    if ($responseData[$log->application_id]->processing_fee && $responseData[$log->application_id]->processing_fee_by_merchant == 1){
                        $disbamount -= $responseData[$log->application_id]->processing_fee;
                    }

                    if ($responseData[$log->application_id]->processing_fee_first_emi){
                        $disbamount += $responseData[$log->application_id]->processing_fee;
                    }
                    $product_name1 = $responseData[$log->application_id]->product_name ? $responseData[$log->application_id]->product_name : '';
                    $applicationController1 = new ConsumerApplicationController();
                    $product_name1 = $applicationController1->getProductName($product_name1);

                    $itemDataP = [
                        'id' => $log->application_id,
                        'unique_id' => $responseData[$log->application_id]->unique_id_number,
                        'loan Application Number' => $responseData[$log->application_id]->loan_application_no,
                        'application Date' => $responseData[$log->application_id]->application_date,
                        'merchant_name' => $responseData[$log->application_id]->merchant_name,
                        'customer_name' => $responseData[$log->application_id]->customer_name,
                        'customer email' => wall_decrypt($responseData[$log->application_id]->email),
                        'current_city' => $responseData[$log->application_id]->current_address_city,
                        'current_state' => $responseData[$log->application_id]->current_address_state,
                        'login_by_user' => $responseData[$log->application_id]->loginned_by ? $responseData[$log->application_id]->loginned_by : $responseData[$log->application_id]->loginned_by_ads,
                        'product_name' => $product_name1,
                        'processing_fee' => $responseData[$log->application_id]->processing_fee,
                        // 'net_disb_amount' => in_array($responseData[$log->application_id]->state, $states) ? ($responseData[$log->application_id]->disbursal_amount - $responseData[$log->application_id]->gst_amount) : null,
                        'net_disb_amount' => in_array($responseData[$log->application_id]->state, $states) ? $disbamount : null,
                        'approved Amount' => $responseData[$log->application_id]->approved_amount,
                        'advance Amount' => in_array($responseData[$log->application_id]->state, $states) ? $responseData[$log->application_id]->advance_amount : null,
                        'disbursal Amount Phase 1' => $phase1_disb_amount,
                        'subvention Amount	Phase 1' => $phase1_sub_amount,
                        'gst Amount Phase 1' => $phase1_gst,
                        'disbursal Amount Phase 2' => $phase2_disb_amount,
                        'subvention_amount_phase 2' => $phase2_sub_amount,
                        'disbursement date phase 2' => $phase2_date,
                        'gst_phase 2' => $phase2_gst,
                        'disbursal Amount Phase 3' => $phase3_disb_amount,
                        'subvention_amount_phase 3' => $phase3_sub_amount,
                        'disbursement date phase 3' => $phase3_date,
                        'gst_phase 3' => $phase3_gst,
                        'state' => $responseData[$log->application_id]->state,
                        'disbursed Date' => $responseData[$log->application_id]->disbursed_date,
                        // 'upi_number' => $responseData[$log->application_id]->upi_number ? $responseData[$log->application_id]->upi_number : ($responseData[$log->application_id]->reference_number ? $responseData[$log->application_id]->reference_number : $av_transactionRef),
                        'UTR_number' => !empty($utr_no) ? "'".$utr_no."'" : null,
                        'Adjusted' => ($adjusted) ? 'Yes' : 'No',
                        // 'UTR_number' =>  in_array($responseData[$log->application_id]->state, $states) ? $utr_no : null,
                        'fldg' => $responseData[$log->application_id]->fldg_applied_category ? 'FLDG' : 'Without FLDG',
                        'hold' => $responseData[$log->application_id]->lib ? 'Liability' : 'Without Liability',
                        'tat_of_approval' => empty($tat) ? '0 Minutes' : $tat,
                        'tat_of_approval_disbursement' => empty($tat_disb_appr) ? '0 Minutes' :$tat_disb_appr,
                        'tat_of_sharing_utr' => empty($tat_utr) ? '0 Minutes' : $tat_utr
                    ];
                    /*if(!$source){
                        $itemDataP['customer_mobile_number'] =  wall_decrypt($responseData[$log->application_id]->mobile_number);
                    }

                    $partialDisbursement[] = $itemDataP;*/
                    if (!$source){
                        $itemDataP['customer number'] = wall_decrypt($responseData[$log->application_id]->mobile_number);
                    }
                    $partialDisbursement[] = $itemDataP;
                }
                if(count($tat_avg)){
                    $avg_tat = $dash_obj->secondsToDays(array_sum($tat_avg) / count($tat_avg));
                }else{
                    $avg_tat = 0;
                }
                if(count($tat_disb_appr_avg)){
                    $avg_tat_disb = $dash_obj->secondsToDays(array_sum($tat_disb_appr_avg) / count($tat_disb_appr_avg));
                }else{
                    $avg_tat_disb = 0;
                }
                if(count($tat_utr_avg)){
                    $avg_tat_utr = $dash_obj->secondsToDays(array_sum($tat_utr_avg) / count($tat_utr_avg));
                }else{
                    $avg_tat_utr = 0;
                }
                $itemDataPSummary = [
                    'total_cases' => count($applications),
                    'total_cases_decision' =>$total_decision_cases,
                    'total_approved_cases'=> $total_approved_cases,
                    'approval_percentage' => (($total_approved_cases/$total_decision_cases)*100).'%',
                    'tat_approval' => $avg_tat,
                    'tat_approval_disb' => $avg_tat_disb,
                    'tat_utr' => $avg_tat_utr,
                    'cases_disb_in_24hrs' => $disb_24hrs
                ];
                $performanceSummary[] = $itemDataPSummary;
            }
            if(count($records)) {
                $queue = $request->get('queue');
                $request = new Request(['data' => [$response, $partialDisbursement,$performanceSummary], 'sheet' => ['MIS Report', 'Partial Disbursement MIS Report','Performance Summary']]);

                $file = $filename = 'MIS_Report_From_' . $from_date_clean . '_To_' . $to_date_clean . '.xls';

                if($user->user_type == 2){
                    $details = " Dear ".$merchant->merchant_name.",<br>As requested, please find the below attached MIS report from ".$from_date_clean." to ".$to_date_clean.".<br>Please reach out on bd@creditfair.in in case you have any issues<br>";
                }else{
                    $details = " Dear ".$user->name.",<br>As requested, please find the below attached MIS report from ".$from_date_clean." to ".$to_date_clean.".<br>Please reach out on bd@creditfair.in in case you have any issues<br>";
                }

                //return Excel::download((new ExporterMultipleSheet($request)), 'MIS_Report_From_' . $from_date . '_To_' . $to_date . '.xls');
                $file = $filename = 'MIS_Report_From_' . $from_date_clean . '_To_' . $to_date_clean . '.xls';
                Excel::store((new ExporterMultipleSheet($request)), $file);

                Mail::send('emails.reminders.basic_reminder',
                    ['body' => $details, 'isBD' => true, 'footer_phone_number' => 9173131148]
                    , function ($message) use ($filename, $to_date_clean, $from_date_clean, $file, $auth_email) {
                        $message->to($auth_email)->subject('Credit Fair :MIS Report from '.$from_date_clean.' to '.$to_date_clean);
                        $message->attachData(file_get_contents(storage_path() . '/app/' . $file), $filename);
                    });
                unlink(storage_path() . '/app/' . $file);
            }else{
                \Session::flash('error', 'No data found in selected period');
                return redirect()->back();
            }
        }

        $data['from_date'] = $from_date;
        $data['to_date'] = $to_date;
        // $data['merchant_name'] = $merchant->merchant_name;
        if(isset($queue) && $queue == true){
            return 1;
        }
        return view('admin.merchant.mis_report', $data);
    }

}