<?php
/**
 * Created by PhpStorm.
 * User: sunil
 * Date: 10/1/18
 * Time: 2:59 PM
 */

namespace App\Http\Controllers\Admin;


use App\ApplicationEMICalculation;
use App\CallLogs;
use App\Comments;
use App\ConsumerApplication;
use App\Http\Controllers\Controller;
use App\Http\Controllers\TestingController;
use App\Http\Controllers\Couriers\CouriersController;
use App\Lenders;
use App\Mandate;
use App\Merchant;
use App\MerchantCategory;
use App\MerchantCategoryTAT;
use App\MerchantCategoryKYCRules;
use App\CFPLRLogs;
use App\Slack;
use App\MerchantCategoryLegalCharges;
use App\Rating;
use App\Services;
use App\StatusLogs;
use App\User;
use App\UserTypes;
use Carbon\Carbon;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Pagination\LengthAwarePaginator;
use Illuminate\Pagination\Paginator;
use function foo\func;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use App\MerchantCategoryKYCRulesLogs;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Session;
use Firebase\JWT\JWT;

class Dashboard extends Controller
{
    protected $request;
    private $to_date;
    private $from_date;
    private $merchant_id;
    private $date_type = 0;
    private $cumulativeTime = 0;
    private $cumulativeTimePre = 0;
    private $cumulativeTimeAbt = 0;
    private $tatWiseCategories = [
        1 => 'Quick',
        2 => 'Mid',
        3 => 'Slow',
    ];

    public function __construct(Request $request){
        $this->request = $request;
        $this->from_date = ($this->request->has('from_date')) ? Carbon::createFromFormat('Y-m-d',$this->request->get('from_date'))->startOfDay()->toDateTimeString() : Carbon::now()->startOfDay()->toDateTimeString();
        $this->to_date = ($this->request->has('to_date')) ? Carbon::createFromFormat('Y-m-d',$this->request->get('to_date'))->endOfDay()->toDateTimeString() : Carbon::now()->endOfDay()->toDateTimeString();
        $this->merchant_id = ($this->request->has('merchant_id')) ? $this->request->get('merchant_id') : 0;
        $this->lender_id = ($this->request->has('lender_id')) ? $this->request->get('lender_id') : 0;
    }
   
    
    public function getMetabaseReport($data){
        $METABASE_SITE_URL = _env('METABASE_SITE_URL');
        $METABASE_SECRET_KEY = _env('METABASE_SECRET_KEY');

        $issuedAt   = time();
        $expire     = $issuedAt + (60 * 60 * 24 * 30); //60 or (60 * 4)

        $payload = [
            "resource" => [$data['type'] => $data['resource']['report_id']],
            "params" => (object)$data['params'],
            "exp" => $expire
        ];

        $token = JWT::encode($payload, $METABASE_SECRET_KEY, 'HS256');

        $iframeUrl = $METABASE_SITE_URL . "/embed/".$data['type']."/" . $token . "#bordered=true&titled=true";

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

    public function dashboard(){

        $merchant = [];
        $anchorMerchant = [];
        if(Auth::user()->user_type == UserTypes::$merchant_v2) {
            $merchant = Auth::user()->merchant_id;
            $checkIfAnchorMerchant = DB::select("SELECT id from merchant where anchor_merchant_id = ?", [$merchant]);
            if (!empty($checkIfAnchorMerchant)) {
                $anchorMerchant = $merchant;
                $merchant= [];
            }
        }
        $data = $this->getMetabaseReport(['resource' => ["report_id" => 277], 'type'=> 'dashboard', 'params' => ['merchant_id' => $merchant, 'anchor_merchant_id' => $anchorMerchant]]);
        return view('admin.dashboard_metabase', (array)$data->getData());

        //echo "hello";
        //$merchantdashboard = new TestingController;
        //echo "<pre>";print_r($merchantdashboard->getdashboard());die;

        ini_set('max_execution_time', -1);
        set_time_limit(0);
        ini_set('memory_limit', '-1');

        $this->date_type = $this->request->has('date_type') ? (int)$this->request->get('date_type') : 2;
        //$applicationsDecisionToDisbursement = $this->getTimeFromDecisionToDisbursement();
        $applicationsProcessed = $this->getLoanApplicationProcessed();
        $applicationsApproved = $this->getLoanApplicationByStatusV1('approved');
        $applicationsAbort = $this->getLoanApplicationByStatusV1('aborted');
        $applicationsRejected = $this->getLoanApplicationByStatusV1('rejected');
        //$applicationsTime = $this->getLoanDecisionTime();
        $applicationsDisbursementTime = $this->getDisbursementV1();
        $applicationReceivedFromMerchant = $this->getLoanApplicationProcessed('merchant');

        setlocale(LC_MONETARY, 'en_IN.UTF-8');
        //setlocale(LC_ALL, '');
        $dashboard = [
            array(
                'title' => '# of Merchants sending Applications',
                'description' => '',
                'count' => str_replace('.00','',money_format('%!i',$applicationReceivedFromMerchant['processed_application'])),
                'applications' => base64_encode(json_encode($applicationsProcessed['application_ids']))
            ),
            array(
                'title' => 'Value of loan applications received',
                'description' => '',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsProcessed['value_processed_application']),
                'applications' => base64_encode(json_encode($applicationsProcessed['application_ids']))
            ),
            array(
                'title' => 'Net Value of Loans Disbursed',
                'description' => '(approved - advance)',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsDisbursementTime[5]),
                'applications' => base64_encode(json_encode($applicationsDisbursementTime[4]))
            ),
            array(
                'title' => 'Loan Application Received',
                'description' => '',
                'count' => str_replace('.00','',money_format('%!i',$applicationsProcessed['processed_application'])),
                'applications' => base64_encode(json_encode($applicationsProcessed['application_ids']))
            ),array(
                'title' => 'Value of loans approved',
                'description' => '',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsApproved[1]),
                'applications' => base64_encode(json_encode($applicationsApproved[2]))
            ),
            array(
                'title' => '',
                'description' => '',
                'count' => ''
            ),
            array(
                'title' => 'Loan Application Approved',
                'description' => '',
                'count' => str_replace('.00','',money_format('%!i',$applicationsApproved[0])),
                'applications' => base64_encode(json_encode($applicationsApproved[2]))
            ),
            array(
                'title' => 'Total Value of loans Disbursed',
                'description' => 'Value of loan including advance EMI',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsDisbursementTime[2]),
                'applications' => base64_encode(json_encode($applicationsDisbursementTime[4]))
            ),
            array(
                'title' => '',
                'description' => '',
                'count' => ''
            ),
            array(
                'title' => 'Loan Application Disbursed',
                'description' => '',
                'count' => str_replace('.00','',money_format('%!i',$applicationsDisbursementTime[1])),
                'applications' => base64_encode(json_encode($applicationsDisbursementTime[4]))
            ),
            array(
                'title' => 'Value of loans rejected',
                'description' => '',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsRejected[1]),
                'applications' => base64_encode(json_encode($applicationsRejected[2]))
            ),
            array(
                'title' => '',
                'description' => '',
                'count' => ''
            ),
            array(
                'title' => 'Loan Application Rejected',
                'description' => '',
                'count' => str_replace('.00','',money_format('%!i',$applicationsRejected[0])),
                'applications' => base64_encode(json_encode($applicationsRejected[2]))
            ),
            array(
                'title' => 'Value of loans aborted',
                'description' => '',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsAbort[1]),
                'applications' => base64_encode(json_encode($applicationsAbort[2]))
            ),
            array(
                'title' => '',
                'description' => '',
                'count' => ''
            ),
            array(
                'title' => 'Loan Application Aborted',
                'description' => '',
                'count' => str_replace('.00','',money_format('%!i',$applicationsAbort[0])),
                'applications' => base64_encode(json_encode($applicationsAbort[2]))
            ),
            array(
                'title' => 'Value of loans Disbursed',
                'description' => 'Net Value of Loans Disbursed',
                'count' => '&#8377;'.$this->getAmountInLac($applicationsDisbursementTime[3]),
                'applications' => base64_encode(json_encode($applicationsDisbursementTime[4]))
            ),
            array(
                'title' => '',
                'description' => '',
                'count' => ''
            ),
            array(
                'title' => '',
                'description' => '',
                'count' => ''
            ),
        ];


        $users = User::where('user_type','>',4)->get(['id', 'name']);
        $lenders = Lenders::get();
        $merchants = Merchant::where('status','active')->where('is_guest_active',1)->orderBy('merchant_name', 'asc')->get(['id','merchant_name']);
        $categories = MerchantCategory::whereNotNull('name')->orderBy('name', 'asc')->get(['id','name']);

        $data = ['date_type'=>$this->date_type,'from_date'=>$this->from_date,'to_date'=>$this->to_date,'merchants'=>$merchants, 'categories' => $categories,'users'=>$users,'user_id'=>$this->request->has('user_id') ? $this->request->get('user_id') : 0, 'merchant_id' =>$this->request->has('merchant_id') ? $this->request->get('merchant_id') : 0 ,'category_id' =>$this->request->has('category_id') ? $this->request->get('category_id') : 0 ,'lender_id' => $this->lender_id, 'lenders' => $lenders];
        return view('admin.dashboard', ['dashboard' => $dashboard], $data);
    }

    public function getTimeFromDecisionToDisbursementV1($merchant_id = 0){
        $applications =  StatusLogs::whereHas('application',function($query) use ($merchant_id){
            $query->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
            if($this->request->has('user_id') && $this->request->get('user_id') > 0){
                $query->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?',$this->request->get('user_id'));
            }
            if($this->merchant_id){
                $query->where('merchant_id',$this->merchant_id);
            }
            if ($this->date_type == 1) {
                $query->where('consumer_application.created_at','>=',$this->from_date)->where('consumer_application.created_at','<=',$this->to_date);
            }
            $query->where('consumer_application.state','disbursed')->orderBy('consumer_application.created_at','desc')->whereNull('is_deleted');
        });
        if ($this->date_type == 2) {
            $applications = $applications->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date);
        }
        $applications = $applications->orderBy('created_at', 'desc')->where('is_reversed',0)->get(['application_id as id'])->toArray();

        $data = array_map(function ($item){
            return $item['id'];
        },$applications);

        $ids = array_diff($data,['NA']);


        $statusLogs = StatusLogs::whereIn('application_id', $ids)->whereIn('status',['approved','disbursed'])->where('is_reversed',0)->get();
        $logIds = [];
        foreach ($statusLogs as $log){
            $logIds[$log->application_id][] = $log;
        }

        //$applicationsApproved =  ConsumerApplication::whereIn('id',$logIds)->whereNull('is_deleted')->get(['id','created_at'])->toArray();

        /*$applicationsApproved =  ConsumerApplication::with(['logs'=>function($query) use ($ids){
            $query->whereIn('status',['approved','disbursed'])->where('is_reversed',0);
        }])->whereIn('id',$ids)->whereNull('is_deleted')->get(['id','created_at'])->toArray();*/

        $response = [];
        /*foreach ($applicationsApproved as $item){
            if(count($item['logs']) == 2) {
                $response[] = (isset($item['logs'][0]) && $item['logs'][1]) ? (strtotime($item['logs'][1]['created_at']) - strtotime($item['logs'][0]['created_at'])) : 'NA';
            }
        }*/

        foreach ($logIds as $item){
            if(count($item) == 2) {
                $response[] = (isset($item[0]) && $item[1]) ? (strtotime($item[1]->created_at) - strtotime($item[0]->created_at)) : 'NA';
            }
        }

        $response = array_values(array_diff($response,['NA']));


        $time = $this->secondsToDays(count($response) > 0 ? round(array_sum($response)/count($response)) : 0);
        return $time;
    }

    public function getTimeFromDecisionToDisbursement($merchant_id = 0){
        $applications =  ConsumerApplication::whereHas('logs',function($query){
            $query->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->where('is_reversed',0)->orderBy('created_at','desc');
        });

        $applications = $applications->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
        if($this->request->has('user_id') && $this->request->get('user_id') > 0){
            $applications = $applications->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?',$this->request->get('user_id'));
        }
        if($this->merchant_id){
            $applications = $applications->where('merchant_id',$this->merchant_id);
        }else{
            $applications = $applications->where('merchant_id',$merchant_id);
        }
        $applications = $applications->where('consumer_application.state','disbursed')->orderBy('consumer_application.created_at','desc')->whereNull('is_deleted')->get(['consumer_application.id','consumer_application.created_at'])->toArray();

        $data = array_map(function ($item){
            return $item['id'];
        },$applications);

        $ids = array_diff($data,['NA']);

        $applicationsApproved =  ConsumerApplication::with(['logs'=>function($query) use ($ids){
            $query->whereIn('status',['approved','disbursed'])->where('is_reversed',0);
        }])->whereIn('id',$ids)->whereNull('is_deleted')->get(['id','created_at'])->toArray();

        $response = [];
        foreach ($applicationsApproved as $item){
            if(count($item['logs']) == 2) {
                $response[] = (isset($item['logs'][0]) && $item['logs'][1]) ? (strtotime($item['logs'][1]['created_at']) - strtotime($item['logs'][0]['created_at'])) : 'NA';
            }
        }

        $response = array_values(array_diff($response,['NA']));


        $time = $this->secondsToDays(count($response) > 0 ? round(array_sum($response)/count($response)) : 0);
        return $time;
    }

    public function getLoanApplicationProcessed($type = false){
        $application_ids = array();
            if($type){
                $applicationsCount = ConsumerApplication::with('application_element')->whereNull('is_deleted');
                if($this->request->has('center_id') && $this->request->get('center_id') > 0){
                    $applicationsCount = $applicationsCount->whereHas('application_element',function($query){
                        $query->where('center_id','=',$this->request->get('center_id'));
                    });
                }
                $applicationsCount = $applicationsCount->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
                if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
                    $applicationsCount = $applicationsCount->where('src_representative_name',$this->request->get('executive_id'));
                }
                if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
                    $applicationsCount = $applicationsCount->where('lender_id',$this->request->get('lender_id'));
                }
                if($this->request->has('category_id') && $this->request->get('category_id') > 0){
                    $applicationsCount = $applicationsCount->where('purpose',$this->request->get('category_id'));
                }
                if($this->request->has('user_id') && $this->request->get('user_id') > 0){
                    $applicationsCount = $applicationsCount->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?',$this->request->get('user_id'));

                }
                if(is_array($this->merchant_id)){
                    $applicationsCount = $applicationsCount->whereIn('merchant_id',$this->merchant_id);
                }else if($this->merchant_id){
                    $applicationsCount = $applicationsCount->where('merchant_id',$this->merchant_id);
                }
                $applicationsCount = $applicationsCount->where('consumer_application.created_at','>=',$this->from_date)->where('consumer_application.created_at','<=',$this->to_date)->where('merchant_id','!=',21)->get(['merchant_id'])->toArray();//distinct('merchant_id')->count('merchant_id');

                $data = array_map(function($item){return ($item && isset($item['application_element']['center_id']) && $item['application_element']['center_id']) ? $item['application_element']['center_id'] : (isset($item['merchant_id']) ? $item['merchant_id'] : '');},$applicationsCount);
                $applicationsCount = count(array_unique($data));
                $applicationsSum = 0;
            }else{
                $applications = ConsumerApplication::with('application_element')->whereNull('is_deleted');
                /*if($this->request->has('center_id') && $this->request->get('center_id') > 0){
                    $applications = $applications->whereHas('application_element',function($query){
                        $query->where('center_id','=',$this->request->get('center_id'));
                    });
                }*/
                $applications = $applications->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
                if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
                    $applications = $applications->where('src_representative_name',$this->request->get('executive_id'));
                }
                if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
                    $applications = $applications->where('lender_id',$this->request->get('lender_id'));
                }
                if($this->request->has('user_id') && $this->request->get('user_id') > 0){
                    $applications = $applications->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?',$this->request->get('user_id'));
                }
                if($this->request->has('category_id') && $this->request->get('category_id') > 0){
                    $applications = $applications->where('purpose',$this->request->get('category_id'));
                }
                if(is_array($this->merchant_id)){
                    $applications = $applications->whereIn('merchant_id',$this->merchant_id);
                }else if($this->merchant_id){
                    $applications = $applications->where('merchant_id',$this->merchant_id);
                }
                $applications = $applications->where('consumer_application.created_at','>=',$this->from_date)->where('consumer_application.created_at','<=',$this->to_date)->get(['consumer_application.id','consumer_application.loan_amount']);

                $applicationsCount = 0;
                $applicationsSum = 0;
                foreach ($applications as $application){
                    $isCenter = 0;
                    if($this->request->has('center_id') && $this->request->get('center_id') > 0){
                        $isCenter = $this->request->get('center_id');
                    }
                    if($isCenter){
                        if($application->application_element && $application->application_element->center_id == $isCenter) {
                            array_push($application_ids, $application->id);
                            $applicationsCount++;
                            $applicationsSum += preg_replace('/[^\p{L}\p{N}\s]/u', '', trim($application->loan_amount));
                        }
                    }else{
                        if(isset($application->loan_amount)) {
                            array_push($application_ids, $application->id);
                            $applicationsCount++;
                            $applicationsSum += preg_replace('/[^\p{L}\p{N}\s]/u', '', trim($application->loan_amount));
                        }
                    }
                }
                //$applicationsCount = $applications->count();
                //$applicationsSum = $applications->sum('loan_amount');

            }
        //$applications = $applications->get();
        return [
            'processed_application' => $applicationsCount,
            'value_processed_application' =>$applicationsSum,
            'application_ids'=>$application_ids
        ];
    }


    public function getLoanApplicationByStatusV2($status){

        if($status == 'approved'){
            $statusNew = [$status, 'cf approved'];
        }

        if(!$this->date_type){
            $this->date_type = 2;
        }

        $appIds = [];
        if ($this->date_type == 2) {
            $applications = StatusLogs::where('created_at', '>=', $this->from_date)->where('created_at', '<=', $this->to_date)->whereIn('status',isset($statusNew) ? $statusNew : [$status])->where('is_reversed',0)->get();
            foreach ($applications as $application){
                if(!in_array($application->application_id, $appIds)) {
                    $appIds[] = $application->application_id;
                }
            }
        }

        if($this->date_type == 1 || $appIds) {
            $applications = ConsumerApplication::whereNull('is_deleted');
            if ($this->date_type == 1) {
                $applications = $applications->where('consumer_application.created_at', '>=', $this->from_date)->where('consumer_application.created_at', '<=', $this->to_date);
            }
            if ($this->request->has('user_id') && $this->request->get('user_id') > 0) {
                $applications = $applications->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?', $this->request->get('user_id'));
            }
            //$applications = $applications->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
            if ($this->request->has('center_id') && $this->request->get('center_id') > 0) {
                $applications = $applications->leftJoin('consumer_application_element as cae', 'cae.application_id', '=', 'consumer_application.id')->where('center_id', $this->request->get('center_id'));
            }
            if ($this->request->has('executive_id') && $this->request->get('executive_id') > 0) {
                $applications = $applications->where('src_representative_name', $this->request->get('executive_id'));
            }
            if ($this->request->has('lender_id') && $this->request->get('lender_id') > 0) {
                $applications = $applications->where('lender_id', $this->request->get('lender_id'));
            }
            if ($this->request->has('category_id') && $this->request->get('category_id') > 0) {
                $applications = $applications->where('purpose', $this->request->get('category_id'));
            }
            if (is_array($this->merchant_id)) {
                $applications = $applications->whereIn('merchant_id', $this->merchant_id);
            } else if ($this->merchant_id) {
                $applications = $applications->where('merchant_id', $this->merchant_id);
            }
            if ($status == 'aborted' || $status == 'rejected') {
                $applications = $applications->where('consumer_application.state', $status);
            } else {
                $applications = $applications->whereNotIn('consumer_application.state', ['aborted', 'rejected']);
            }

            if ($appIds) {
                $applications = $applications->whereIn('consumer_application.id', $appIds);
            }

            $applications = $applications->groupBy('consumer_application.id')->get(['consumer_application.id', 'consumer_application.merchant_id']);
        }else if($appIds){
            $applications = ConsumerApplication::whereIn('id', $appIds)->groupBy('consumer_application.id')->get(['consumer_application.id', 'consumer_application.merchant_id']);
        }

        $application_ids = array();
        $merchants_ids = array();
        $merchantData = array();
        foreach ($applications as $application){
            if(!in_array($application->id, $application_ids)) {
                //array_push($application_ids, $application->id);
                $merchantData[$application->merchant_id][] = $application->id;
            }
            if(!in_array($application->merchant_id, $merchants_ids)) {
                array_push($merchants_ids, $application->merchant_id);
            }
        }

        $ids = Merchant::whereIn('id', $merchants_ids)->where('is_guest_active',1)->get();
        foreach ($ids as $dd){
            $application_ids = array_merge($application_ids, $merchantData[$dd->id]);
        }

        if ($status == 'approved') {
            $applications = ConsumerApplication::whereIn('id', $application_ids)->get(['approved_amount as loan_amount'])->toArray();
        }else{
            $applications = ConsumerApplication::whereIn('id',$application_ids)->get(['loan_amount'])->toArray();
        }

        $app =  array_map(function($item){
            return $item['loan_amount'];
        },$applications);

        return [count($app),array_sum($app),$application_ids];
    }

    public function getLoanApplicationByStatusV1($status){

        return $this->getLoanApplicationByStatusV2($status);

        $applications =  StatusLogs::whereHas('application', function($query) use ($status){
            $query->whereNull('is_deleted');
            if($this->request->has('user_id') && $this->request->get('user_id') > 0){
                $query->whereRaw('IF(pre_created_by != "", pre_created_by, created_by) = ?',$this->request->get('user_id'));
            }
            $query->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
            if($this->request->has('center_id') && $this->request->get('center_id') > 0){
                $query->whereHas('application_element',function($query){
                    $query->where('center_id','=',$this->request->get('center_id'));
                });
            }
            if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
                $query->where('src_representative_name',$this->request->get('executive_id'));
            }
            if($this->request->has('category_id') && $this->request->get('category_id') > 0){
                $query->where('purpose',$this->request->get('category_id'));
            }
            if(is_array($this->merchant_id)){
                $query->whereIn('merchant_id',$this->merchant_id);
            }else if($this->merchant_id){
                $query->where('merchant_id',$this->merchant_id);
            }
            if($status == 'aborted' || $status == 'rejected'){
                $query->where('consumer_application.state',$status);
            }else{
                $query->where('consumer_application.state','!=','aborted');
                $query->where('consumer_application.state','!=','rejected');
            }

            if ($this->date_type == 1) {
                $query->where('consumer_application.created_at', '>=', $this->from_date)->where('consumer_application.created_at', '<=', $this->to_date);
            }
        });
        if ($this->date_type == 2) {
            $applications = $applications->where('created_at', '>=', $this->from_date)->where('created_at', '<=', $this->to_date);
        }
        $applications = $applications->where('status',$status)->where('is_reversed',0)->groupBy('id')->get();
        $application_ids = array();
        foreach ($applications as $application){
            array_push($application_ids,$application['application_id']);
        }

        if ($status == 'approved') {
            $applications = ConsumerApplication::whereIn('id', $application_ids)->get(['approved_amount as loan_amount'])->toArray();
        }else{
            $applications = ConsumerApplication::whereIn('id',$application_ids)->get(['loan_amount'])->toArray();
        }

        $app =  array_map(function($item){
            return $item['loan_amount'];
        },$applications);

        return [count($app),array_sum($app),$application_ids];
    }

    public function getLoanApplicationByStatus($status){
        $applications =  ConsumerApplication::whereHas('logs',function($query) use ($status){
            $query->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->where('status',$status)->where('is_reversed',0);
        })->whereNull('is_deleted')->groupBy('consumer_application.id');
        if($this->request->has('user_id') && $this->request->get('user_id') > 0){
            $applications = $applications->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?',$this->request->get('user_id'));
        }
        $applications = $applications->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
        if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            $applications = $applications->whereHas('application_element',function($query){
                $query->where('center_id','=',$this->request->get('center_id'));
            });
        }
        if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            $applications = $applications->where('src_representative_name',$this->request->get('executive_id'));
        }
        if(is_array($this->merchant_id)){
            $applications = $applications->whereIn('merchant_id',$this->merchant_id);
        }else if($this->merchant_id){
            $applications = $applications->where('merchant_id',$this->merchant_id);
        }
        if($status == 'aborted' || $status == 'rejected'){
            $applications = $applications->where('consumer_application.state',$status);
        }else{
            $applications = $applications->where('consumer_application.state','!=','aborted');
            $applications = $applications->where('consumer_application.state','!=','rejected');
        }
        if($status == 'approved'){
            $applications = $applications->get(['consumer_application.id',DB::raw('SUM(approved_amount) as loan_amount')])->toArray();
        }else{
            $applications = $applications->get(['consumer_application.id',DB::raw('SUM(loan_amount) as loan_amount')])->toArray();
        }
        $application_ids = array();
        foreach ($applications as $application){
            array_push($application_ids,$application['id']);
        }
        $app =  array_map(function($item){
            return $item['loan_amount'];
        },$applications);

        return [count($app),array_sum($app),$application_ids];
    }

    public function getLoanDecisionTimeV1(){
        $status = ['rejected','approved'];
        $data = $this->getTimeTakenV1($status,0);
        $time = $this->secondsToDays(count($data[0]) > 0 ? round(array_sum($data[0])/count($data[0])) : 0);
        return $time;
    }

    public function getLoanDecisionTime(){
        $status = ['rejected','approved'];
        $data = $this->getTimeTaken($status,0);
        $time = $this->secondsToDays(count($data[0]) > 0 ? round(array_sum($data[0])/count($data[0])) : 0);
        return $time;
    }

    public function getDisbursementV1(){
        $status = ['disbursed'];
        $data = $this->getTimeTakenV1($status,1);
        $time = $this->secondsToDays(count($data[0]) > 0 ? round(array_sum($data[0])/count($data[0])) : 0);
        $applications = $data[3];
        $ids = array_map(function ($item) use ($status) {
            return ($item['application'] && in_array($item['status'],$status)) ? $item['application_id'] : 'NA';
        },$applications);

        $disbursementAmountSql = ConsumerApplication::with('emi')->whereIn('consumer_application.id',array_values(array_diff($ids,['NA'])));
        if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->whereHas('application_element',function($query){
                $query->where('center_id','=',$this->request->get('center_id'));
            });
        }
        $disbursementAmountSql = $disbursementAmountSql->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
        if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('src_representative_name',$this->request->get('executive_id'));
        }
        if($this->request->has('category_id') && $this->request->get('category_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('purpose',$this->request->get('category_id'));
        }
        if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('lender_id',$this->request->get('lender_id'));
        }
        $disbursementAmountSql = $disbursementAmountSql->get(['consumer_application.id','consumer_application.approved_amount','m.charge_subvention_remaining_amount']);
        $disbursementAmount = 0;
        $disbursementAmountWithoutSubvention = 0;
        $application_ids = array();
        foreach ($disbursementAmountSql as $item){
            if($item->emi && $item->approved_amount > 0) {
                array_push($application_ids,$item->id);

                $downPayment = 0;
                if(isset($item->charge_subvention_remaining_amount) && $item->charge_subvention_remaining_amount){
                    $downPayment = $item->emi->upfront_from_customer;
                }

                $disbursementAmount += ($item->approved_amount - ($item->emi->upfront_from_customer + (($item->approved_amount - $downPayment) * $item->emi->subvention / 100)));
                $disbursementAmountWithoutSubvention += ($item->approved_amount - $item->emi->upfront_from_customer);
            }
        }
        return [$time, count($application_ids),$data[1],$disbursementAmount,$application_ids,$disbursementAmountWithoutSubvention];
    }

    public function getDisbursementV2(){
        $status = ['disbursed'];
        $data = $this->getTimeTakenV2($status,1);
        // $time = $this->secondsToDays(count($data[0]) > 0 ? round(array_sum($data[0])/count($data[0])) : 0);
        $applications = $data[3];
        $ids = array_map(function ($item) use ($status) { 
            return ($item['logs'] && in_array($item['logs'][0]['status'],$status)) ? $item['id'] : 'NA';
        },$applications);
        $disbursementAmountSql = ConsumerApplication::with('emi')->whereIn('consumer_application.id',array_values(array_diff($ids,['NA'])));
        if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->whereHas('application_element',function($query){
                $query->where('center_id','=',$this->request->get('center_id'));
            });
        }
        $disbursementAmountSql = $disbursementAmountSql->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
        if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('src_representative_name',$this->request->get('executive_id'));
        }
        if($this->request->has('category_id') && $this->request->get('category_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('purpose',$this->request->get('category_id'));
        }
        if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('lender_id',$this->request->get('lender_id'));
        }
        $disbursementAmountSql = $disbursementAmountSql->get(['consumer_application.id','consumer_application.approved_amount','m.charge_subvention_remaining_amount']);
        $disbursementAmount = 0;
        $disbursementAmountWithoutSubvention = 0;
        $application_ids = array();
        foreach ($disbursementAmountSql as $item){
            if($item->emi && $item->approved_amount > 0) {
                array_push($application_ids,$item->id);

                $downPayment = 0;
                if(isset($item->charge_subvention_remaining_amount) && $item->charge_subvention_remaining_amount){
                    $downPayment = $item->emi->upfront_from_customer;
                }

                $disbursementAmount += ($item->approved_amount - ($item->emi->upfront_from_customer + (($item->approved_amount - $downPayment) * $item->emi->subvention / 100)));
                $disbursementAmountWithoutSubvention += ($item->approved_amount - $item->emi->upfront_from_customer);
            }
        }
        return [0, count($application_ids),$data[1],$disbursementAmount,$application_ids,$disbursementAmountWithoutSubvention];
    }    
    public function getDisbursement(){
        $status = ['disbursed'];
        $data = $this->getTimeTaken($status,1);
        //$time = $this->secondsToDays(count($data[0]) > 0 ? round(array_sum($data[0])/count($data[0])) : 0);
        $applications = $data[3];
        $ids = array_map(function ($item) use ($status) {
            return ($item['logs'] && in_array($item['logs'][0]['status'],$status)) ? $item['id'] : 'NA';
        },$applications);

        $disbursementAmountSql = ConsumerApplication::with('emi')->whereIn('consumer_application.id',array_values(array_diff($ids,['NA'])));
        if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->whereHas('application_element',function($query){
                $query->where('center_id','=',$this->request->get('center_id'));
            });
        }
        $disbursementAmountSql = $disbursementAmountSql->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
        if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('src_representative_name',$this->request->get('executive_id'));
        }
        if($this->request->has('category_id') && $this->request->get('category_id') > 0){
            $disbursementAmountSql = $disbursementAmountSql->where('purpose',$this->request->get('category_id'));
        }
        $disbursementAmountSql = $disbursementAmountSql->get(['consumer_application.id','consumer_application.approved_amount','m.charge_subvention_remaining_amount']);
        $disbursementAmount = 0;
        $application_ids = array();
        foreach ($disbursementAmountSql as $item){
            if($item->emi && $item->approved_amount > 0) {
                array_push($application_ids,$item->id);


                $downPayment = 0;
                if(isset($item->charge_subvention_remaining_amount) && $item->charge_subvention_remaining_amount){
                    $downPayment = $item->emi->upfront_from_customer;
                }

                $disbursementAmount += ($item->approved_amount - ($item->emi->upfront_from_customer + (($item->approved_amount - $downPayment) * $item->emi->subvention / 100)));
            }
        }
        return [0, count($application_ids),$data[1],$disbursementAmount,$application_ids];
    }

    public function getTimeTaken($status, $type){
        $applications =  ConsumerApplication::with(['logs'=>function($query) use ($status, $type){
            //if($type == 0){
                $query = $query->whereIn('status',$status)->where('is_reversed',0);
            //}
            if ($this->date_type == 2) {
                $query->where('created_at', '>=', $this->from_date)->where('created_at', '<=', $this->to_date)->orderBy('created_at', 'desc');
            }
        }]);
        $applications = $applications->leftJoin('merchant as m','m.id','=','consumer_application.merchant_id')->where('is_guest_active',1);
        if($this->request->has('user_id') && $this->request->get('user_id') > 0){
            $applications = $applications->whereRaw('IF(consumer_application.pre_created_by != "", consumer_application.pre_created_by, consumer_application.created_by) = ?',$this->request->get('user_id'));
        }
        if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            $applications = $applications->leftJoin('consumer_application_element as cae','cae.application_id','=','consumer_application.id')->where('center_id',$this->request->get('center_id'));
            /*$applications = $applications->whereHas('application_element',function($query){
                //$query->where('center_id','=',$this->request->get('center_id'));
            });*/
        }
        if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            $applications = $applications->where('src_representative_name',$this->request->get('executive_id'));
        }
        if($this->request->has('category_id') && $this->request->get('category_id') > 0){
            $applications = $applications->where('purpose',$this->request->get('category_id'));
        }
        if(is_array($this->merchant_id)){
            $applications = $applications->whereIn('merchant_id',$this->merchant_id);
        }elseif($this->merchant_id){
            $applications = $applications->where('merchant_id',$this->merchant_id);
        }

        if ($this->date_type == 1){
            $applications = $applications->where('consumer_application.created_at', '>=', $this->from_date)->where('consumer_application.created_at', '<=', $this->to_date);
        }

        $applications = $applications->whereNull('is_deleted')->get(['consumer_application.id','consumer_application.created_at','consumer_application.approved_amount'])->toArray();

        $rmNachRegistered = ($type) ? ['disbursed'] : $status;
        $time = array_map(function ($item) use ($rmNachRegistered) {
            return ($item['logs'] && in_array($item['logs'][0]['status'],$rmNachRegistered)) ? (strtotime($item['logs'][0]['created_at']) - strtotime($item['created_at'])) : 'NA';
        },$applications);

        $rmNachRegistered = ($type) ? ['disbursed'] : $status;
        $amount = array_map(function ($item) use ($rmNachRegistered){
            return ($item['logs'] && in_array($item['logs'][0]['status'],$rmNachRegistered)) ? $item['approved_amount'] : 'NA';
        },$applications);

        return [array_diff($time,['NA']), array_sum(array_diff($amount,['NA'])), $time,$applications];
    }

    public function getTimeTakenV1($status, $type){
        $applications =  StatusLogs::with(['application'=>function($query) use ($status, $type){
            //$query->leftJoin('merchant as m','m.id','=','merchant_id')->where('is_guest_active',1);
            if($this->request->has('user_id') && $this->request->get('user_id') > 0){
                $query->whereRaw('IF(created_by != "", created_by, pre_created_by) = ?',$this->request->get('user_id'));
            }
            if($this->request->has('center_id') && $this->request->get('center_id') > 0){
                $query->whereHas('application_element',function($query1){
                    $query1->where('center_id','=',$this->request->get('center_id'));
                });
            }
            if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
                $query->where('src_representative_name',$this->request->get('executive_id'));
            }
            if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
                $query->where('lender_id',$this->request->get('lender_id'));
            }
            if(is_array($this->merchant_id)){
                $query->whereIn('merchant_id',$this->merchant_id);
            }elseif($this->merchant_id){
                $query->where('merchant_id',$this->merchant_id);
            }
            if ($this->date_type == 1) {
                $query->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date);
            }
            $query->whereNull('is_deleted');//->get(['consumer_application.id','consumer_application.created_at','consumer_application.approved_amount'])->toArray();
        }]);
        if ($this->date_type == 2) {
            $applications = $applications->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date);
        }
        $applications = $applications->where('is_reversed',0)->whereIn('status',$status)->orderBy('created_at','desc')->get(['application_id','created_at','status'])->toArray();


        $rmNachRegistered = ($type) ? ['disbursed'] : $status;
        $time = array_map(function ($item) use ($rmNachRegistered) {
            return ($item['application'] && in_array($item['status'],$rmNachRegistered)) ? (strtotime($item['created_at']) - strtotime($item['application']['created_at'])) : 'NA';
        },$applications);

        $rmNachRegistered = ($type) ? ['disbursed'] : $status;
        $amount = array_map(function ($item) use ($rmNachRegistered){
            return ($item['application'] && in_array($item['status'],$rmNachRegistered)) ? $item['application']['approved_amount'] : 'NA';
        },$applications);

        return [array_diff($time,['NA']), array_sum(array_diff($amount,['NA'])), $time,$applications];
    }

    public function getTimeTakenV2($status, $type){
        $applications =  ConsumerApplication::with(['logs'=>function($query) use ($status, $type){
            //$query->leftJoin('merchant as m','m.id','=','merchant_id')->where('is_guest_active',1);
            // if($this->request->has('user_id') && $this->request->get('user_id') > 0){
            //     $query->whereRaw('IF(pre_created_by != "", pre_created_by, created_by) = ?',$this->request->get('user_id'));
            // }
            // if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            //     $query->whereHas('application_element',function($query1){
            //         $query1->where('center_id','=',$this->request->get('center_id'));
            //     });
            // }
            // if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            //     $query->where('src_representative_name',$this->request->get('executive_id'));
            // }
            // if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
            //     $query->where('lender_id',$this->request->get('lender_id'));
            // }
            // if(is_array($this->merchant_id)){
            //     $query->whereIn('merchant_id',$this->merchant_id);
            // }elseif($this->merchant_id){
            //     $query->where('merchant_id',$this->merchant_id);
            // }
           
            // if ($this->date_type == 1) {
            //     $query->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date);
            // }
            $query->select(['status_logs.application_id','status_logs.created_at','status_logs.status']);
            if ($this->date_type == 2) {
             $query->where('status_logs.created_at','>=', $this->from_date)->where('status_logs.created_at','<=', $this->to_date);
            }
            $query->where('status_logs.is_reversed',0)->where('status_logs.status','disbursed')->orderBy('status_logs.created_at', 'DESC');
        }]);
        if($this->request->has('user_id') && $this->request->get('user_id') > 0){
            $applications = $applications->whereRaw('IF(pre_created_by != "", pre_created_by, created_by) = ?',$this->request->get('user_id'));
        }
        if($this->request->has('center_id') && $this->request->get('center_id') > 0){
            $applications = $applications->whereHas('application_element',function($query1){
                $query1->where('center_id','=',$this->request->get('center_id'));
            });
        }
        if($this->request->has('executive_id') && $this->request->get('executive_id') > 0){
            $applications = $applications->where('src_representative_name',$this->request->get('executive_id'));
        }
        if($this->request->has('lender_id') && $this->request->get('lender_id') > 0){
            $applications = $applications->where('lender_id',$this->request->get('lender_id'));
        }
        if(is_array($this->merchant_id)){
            $applications = $applications->whereIn('merchant_id',$this->merchant_id);
        }elseif($this->merchant_id){
            $applications = $applications->where('merchant_id',$this->merchant_id);
        }
        if ($this->date_type == 1){
            $applications = $applications->where('created_at','>=', $this->from_date)->where('created_at','<=', $this->to_date);
        }
        // if ($this->date_type == 2) {
        //     $applications = $applications->where('created_at','>=', $this->from_date)->where('created_at','<=', $this->to_date);
        // }

        $applications = $applications->whereNull('is_deleted')->get(['id','state','approved_amount'])->toArray();
 
        // $rmNachRegistered = ($type) ? ['disbursed'] : $status;
        // $time = array_map(function ($item) use ($rmNachRegistered) {
        //     return ($item['id'] && in_array($item['state'],$rmNachRegistered)) ? (strtotime($item['created_at']) - strtotime($item['created_at'])) : 'NA';
        // },$applications);

        $rmNachRegistered = ($type) ? ['disbursed'] : $status;
        $amount = array_map(function ($item) use ($rmNachRegistered){
            return ($item['id'] && !empty($item['logs']) && in_array($item['logs'][0]['status'],$rmNachRegistered)) ? $item['approved_amount'] : 'NA';
        },$applications);
        return [0, array_sum(array_diff($amount,['NA'])), 0 , $applications];
    }

    function secondsToDays($seconds, $isSec = 0)
    {
        $ret = "";

        /*** get the days ***/
        $days = intval(intval($seconds) / (3600*24));
        if($days> 0)
        {
            $ret .= "$days Days ";
        }

        /*** get the hours ***/
        $hours = (intval($seconds) / 3600) % 24;
        if($hours > 0)
        {
            $ret .= "$hours Hours ";
        }

        /*** get the minutes ***/
        $minutes = (intval($seconds) / 60) % 60;
        if($minutes > 0)
        {
            $ret .= "$minutes minutes ";
        }

        if ($isSec) {
            /*** get the seconds ***/
            $seconds = intval($seconds) % 60;
            if ($seconds > 0) {
                $ret .= "$seconds seconds";
            }
        }

        return $ret;
    }

    public function getAmountInLac($amount){
        $ext="";//thousand,lac, crore
        $number_of_digits = $this->count_digit(round($amount)); //this is call :)
        if($number_of_digits>3)
        {
            if($number_of_digits%2!=0)
                $divider = $this->divider($number_of_digits-1);
            else
                $divider = $this->divider($number_of_digits);
        }
        else
            $divider=1;

        $fraction=$amount/$divider;
        $fraction=number_format($fraction,2);
        if($number_of_digits==4 ||$number_of_digits==5)
            $ext="k";
        if($number_of_digits==6 ||$number_of_digits==7)
            $ext="Lac";
        if($number_of_digits==8 ||$number_of_digits==9)
            $ext="Cr";
        return $fraction." ".$ext;
    }

    public function count_digit($number) {
        return strlen($number);
    }

    public function divider($number_of_digits) {
        $tens="1";

        if($number_of_digits>8)
            return 10000000;

        while(($number_of_digits-1)>0)
        {
            $tens.="0";
            $number_of_digits--;
        }
        return $tens;
    }

    public function getWeeklyMerchantPerformance(){
        $this->from_date = ($this->request->has('from_date')) ? Carbon::createFromFormat('Y-m-d',$this->request->get('from_date'))->startOfDay()->toDateTimeString() : Carbon::now()->startOfDay()->toDateTimeString();
        $this->to_date = ($this->request->has('to_date')) ? Carbon::createFromFormat('Y-m-d',$this->request->get('to_date'))->endOfDay()->toDateTimeString() : Carbon::now()->endOfDay()->toDateTimeString();
        $this->date_type = $this->request->has('date_type') ? (int)$this->request->get('date_type') : 2;
        $this->user_id = $this->request->has('user_id') ? $this->request->get('user_id') : 0;

        //$merchantdashboard = new TestingController;
        //echo "<pre>";print_r($merchantdashboard->getdashboard());die;

        if($this->date_type == 1){
            $applications = ConsumerApplication::where('created_at', '>=', $this->from_date)->where('created_at', '<=', $this->to_date)->get();
        }else{
            $applications = StatusLogs::where('created_at', '>=', $this->from_date)->where('created_at', '<=', $this->to_date)->get();

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

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

        $merchantsIds = [];
        foreach ($applications as $application){
            if(!in_array($application->merchant_id, $merchantsIds)) {
                $merchantsIds[] = $application->merchant_id;
            }
        }
        $merchants = Merchant::whereNull('deleted_at')->where('is_guest_active',1)->whereIn('id',$merchantsIds)->where('status','active')->get();

        $response = [];
        foreach ($merchants as $merchant){
            if ($merchant->applications) {
                $this->merchant_id = $merchant->id;
                $applicationsProcessed = $this->getLoanApplicationProcessed();
                $time_from_decision_to_disbursement = $this->getTimeFromDecisionToDisbursementV1();
                $time_from_login_to_decision = $this->getLoanDecisionTimeV1();
                $time_from_login_to_disbursement = $this->getDisbursementV1();

                $response[$merchant->merchant_name] = [
                    'applications' => $applicationsProcessed['processed_application'],
                    'applications_count' => base64_encode(json_encode($applicationsProcessed['application_ids'])),
                    'disbursement' => $time_from_login_to_disbursement[1],
                    'dis_applications_count' => base64_encode(json_encode($time_from_login_to_disbursement[4])),
                    'time_from_decision_to_disbursement' => $time_from_decision_to_disbursement,
                    'time_from_login_to_decision' => $time_from_login_to_decision,
                    'time_from_login_to_disbursement' => $time_from_login_to_disbursement[0],
                    'time_from_login_to_disbursement_ids' => $time_from_login_to_disbursement[4]
                ];
            }
        }

        $page = $this->request->has('page') && $this->request->get('page') > 1 ? $this->request->get('page') : 0;
        $data['merchantLists'] = $this->paginate($response, 10, $page, ['path' => $this->request->url()]);
        $data['date_type'] = $this->date_type;
        $data['from_date'] = date('Y-m-d',strtotime($this->from_date));
        $data['to_date'] = date('Y-m-d',strtotime($this->to_date));
        $data['users'] = User::where('user_type','>',4)->get(['id', 'name']);
        $data['user_id']= $this->request->has('user_id') ? $this->request->get('user_id') : 0;
        return view('admin.weekly_merchant_performance', $data);
    }

    /**
     * The attributes that are mass assignable.
     *
     * @var array
     */
    public function paginate($items, $perPage = 10, $page = null, $options = [])
    {
        $page = $page ?: (Paginator::resolveCurrentPage() ?: 1);
        $items = $items instanceof Collection ? $items : Collection::make($items);
        return new LengthAwarePaginator($items->forPage($page, $perPage), $items->count(), $perPage, $page, $options);
    }

    public function advisorActivity(){
        $user_id = $this->request->has('user_id') && $this->request->get('user_id') ? $this->request->get('user_id') : Auth::user()->id;
        $this->request->merge(['user_id' => $user_id]);
        $this->from_date = ($this->request->has('from_date')) ? Carbon::createFromFormat('Y-m-d',$this->request->get('from_date'))->startOfDay()->toDateTimeString() : Carbon::now()->startOfDay()->toDateTimeString();
        $this->to_date = ($this->request->has('to_date')) ? Carbon::createFromFormat('Y-m-d',$this->request->get('to_date'))->endOfDay()->toDateTimeString() : Carbon::now()->endOfDay()->toDateTimeString();

        $user = User::find($user_id);

        $comments = ConsumerApplication::whereHas('comments', function($query) use($user_id){
            $query->where('user_id',$user_id)->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date);
        })->whereNull('is_deleted')->get(['id as application_id']);

        $status = ConsumerApplication::whereHas('logs', function($query) use($user_id){
                $query->where('user_id',$user_id)->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->where('is_reversed',0);
        })->whereNull('is_deleted')->get(['id as application_id']);

        $commentsAdded = Comments::where('comment','like','%'.$user->name.'%')->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->groupBy('application_id')->get(['application_id'])->toArray();

        $applications = ConsumerApplication::where('created_by',$user_id)->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->whereNull('is_deleted')->get(['id','name','state']);
        $merchants = ConsumerApplication::where('created_by',$user_id)->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->whereNull('is_deleted')->groupBy('merchant_id')->get(['merchant_id']);

        $calls = CallLogs::with('consumer')->where('user_id', $user_id)->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->where('id_type', 'application_id')->get(['id','_id','id_type','direction','status','duration','created_at']);

        $cards = [];
        $disbursed = 0;
        foreach ($applications as $application){
            if ($application->state == 'in-process')
                $cards['in_process'][] = $application;
            if ($application->state == 'approved')
                $cards['approved'][] = $application;
            if ($application->state == 'disbursed' || $application->state == 'nach registered')
                $disbursed++;
            if ($application->state == 'waiting for documents')
                $cards['waiting_for_documents'][] = $application;
        }

        $cards['Recent Comments'] = ConsumerApplication::whereIn('id',array_values($commentsAdded))->whereNull('is_deleted')->get();


        $alreadyMade = [];
        $unique_customer_contacted = [];
        $connected = [];
        $call_dialed = [];
        $totalDuration = [];
        $totalDurationList = [];
        foreach ($calls as $call){
            if (!in_array($call->_id, $alreadyMade)) {
                $unique_customer_contacted[] = $call;
                $alreadyMade[] = $call->_id;
            }
            if ($call->duration > 0 && $call->status == 'completed') {
                $totalDuration[] = $call->duration / 60;
                $totalDurationList[] = $call;
            }
            if (in_array($call->status, ['completed','in-progress'])){
                $connected[] = $call;
            }
            if ($call->status != 400){
                $call_dialed[] = $call;
            }
        }

        $date = Carbon::parse($this->from_date);
        $now = Carbon::parse($this->to_date);

        $diffDays = $date->diffInDays($now);
        $diffDays = $diffDays + 1;

        $daysInMonth = Carbon::now()->daysInMonth;
        if($user->monthly_application) {
            $getDailyTargetApplication = $user->monthly_application / $daysInMonth;
        }else{
            $getDailyTargetApplication = 0;
        }
        if($user->monthly_disbursement_value) {
            $getDailyTargetDisbursement = $user->monthly_disbursement_value / $daysInMonth;
        }else{
            $getDailyTargetDisbursement = 0;
        }

        $data['application_target'] = round($getDailyTargetApplication * $diffDays);
        $data['disbursement_target'] = round($getDailyTargetDisbursement * $diffDays, 2);

        $this->date_type = 2;
        $time_from_login_to_decision = $this->getLoanDecisionTimeV1();
        $time_from_decision_to_disbursement = $this->getTimeFromDecisionToDisbursementV1();
        $data['time_from_login_to_decision'] = !empty($time_from_login_to_decision) ? $time_from_login_to_decision : 0;
        $data['time_from_decision_to_disbursement'] = !empty($time_from_decision_to_disbursement) ? $time_from_decision_to_disbursement : 0;

        $callLogs['lists']['total_call_made_list'] = $calls;
        $callLogs['lists']['unique_customer_contacted_list'] = $unique_customer_contacted;
        $callLogs['lists']['call_dialed_list'] = $call_dialed;
        $callLogs['lists']['connected_list'] = $connected;
        $callLogs['lists']['average_minutes_spoken_list'] = $totalDurationList;

        $callLogs['total_call_made'] = count($calls);
        $callLogs['unique_customer_contacted'] = count($unique_customer_contacted);
        $callLogs['call_dialed'] = count($call_dialed);
        $callLogs['connected'] = count($connected);
        $callLogs['average_minutes_spoken'] = $totalDuration ? round(array_sum($totalDuration) / count($totalDuration),2) : 0;

        $data['cards'] = $cards;
        $data['comments'] = count($comments);
        $data['status'] = count($status);
        $data['unique_merchant'] = count($merchants);
        $data['calls'] = $callLogs;

        $ratings = Rating::where('user_id',$user_id)->where('rating', '>' ,0)->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->get(['rating']);

        $ratingCount = 0;
        foreach ($ratings as $rating){
            if ($rating->rating > 0) {
                $ratingCount += $rating->rating;
            }
        }

        $data['ratings'] = ($ratingCount) ? $ratingCount/count($ratings) : 0;
        $data['new_application'] = count($applications);
        $data['new_disbursement'] = $disbursed;
        $application = new ConsumerApplicationController();
        $data['tracker'] = $application->tracker($this->request->merge(['bot' => true]));

        $data['tracker']['is_advisor_activity'] = 1;
        $trackerHTML = view('admin.tracker', $data['tracker'])->render();

        $data['center_lng'] = $data['tracker']['center_lng'];
        $data['center_lat'] = $data['tracker']['center_lat'];
        $data['locations'] = $data['tracker']['locations'];
        $data['stops'] = $data['tracker']['stops'];
        $data['users'] = User::where('user_type','>',4)->get(['id', 'name']);
        $data['user_id'] = $user_id;
        $data['from_date'] = date('Y-m-d',strtotime($this->from_date));
        $data['to_date'] = date('Y-m-d',strtotime($this->to_date));
        $data['trackerHTML'] = $trackerHTML;
        $data['categories'] = MerchantCategory::with('tat')->get(['id','name']);
        $data['tat_wise_data'] = $this->getTATWiseData($data['categories'], $user_id);
        $data['category_status'] = $this->tatWiseCategories;
        return view('admin.sales_location', $data);
    }

    public function getTATWiseData($categories, $user_id = 0){

        $applications = ConsumerApplication::whereHas('logs', function($query) use($user_id){
            $query->whereIn('status',['approved', 'rejected', 'aborted', 'form completed', 'incomplete form','disbursed'])->where('created_at','>=',$this->from_date)->where('created_at','<=',$this->to_date)->where('is_reversed',0)->select(['id','status','created_at']);
        })->whereNull('is_deleted')->where('created_by',$user_id)->get(['id','purpose','created_by']);

        $categoriesData = [];
        foreach ($categories as $category){
            if ($category->tat) {
                $categoriesData[$category->tat->status][] = $category->id;
            }
        }

        $ids = [];
        foreach ($applications as $application){
            if ($application->purpose){
                $d[] =  $application->purpose;
            }
            $ids[] = $application->id;
        }

        $statusLogs = StatusLogs::whereIn('application_id', $ids)->orderBy('created_at','desc')->get();

        $statusLogsData = [];
        foreach ($statusLogs as $log){
            $statusLogsData[$log->application_id][] = $log;
        }
        $statusLogsSingle = [];
        foreach ($statusLogsData as $appID => $logsData){

            $status = [];
            foreach ($logsData as $logsDataItem){
                $status[] = $logsDataItem->status;
            }

            $statusLogsSingle[$appID] = $status;
        }

        $decisionStatus = $this->getDecisionStatus($applications, $statusLogsData, $statusLogsSingle, $categoriesData);
        $disbursementStatus = $this->getDisbursementStatus($applications, $statusLogsData, $statusLogsSingle, $categoriesData);

        $decisionResponse = $decisionStatus['response'];
        $decisionTotal = $decisionStatus['total'];

        $disbursementResponse = $disbursementStatus['response'];
        $disbursementTotal = $disbursementStatus['total'];

        $responseData = [];
        foreach ($decisionResponse as $tatKey => $tatObj){
            foreach ($tatObj as $created_by => $item) {
                $responseData[$created_by][$tatKey] = [
                    'case_tat_to_decision' => isset($decisionTotal[$tatKey][$created_by]) ? $decisionTotal[$tatKey][$created_by] : 0,
                    'avg_tat_to_decision' => isset($decisionResponse[$tatKey][$created_by]) && isset($decisionTotal[$tatKey][$created_by]) ? $this->secondsToDays($decisionResponse[$tatKey][$created_by] / $decisionTotal[$tatKey][$created_by], 1) : 0,
                    'case_tat_to_disbursement' => isset($disbursementTotal[$tatKey][$created_by]) ? $disbursementTotal[$tatKey][$created_by] : 0,
                    'avg_tat_to_disbursement' => isset($disbursementResponse[$tatKey][$created_by]) && isset($disbursementTotal[$tatKey][$created_by]) ? $this->secondsToDays($disbursementResponse[$tatKey][$created_by]/$disbursementTotal[$tatKey][$created_by],1) : 0,
                ];
            }
        }

        $keys = array_values(array_keys($this->tatWiseCategories));
        foreach ($responseData as $created_by => $tatStatus){
            foreach ($tatStatus as $status => $statusItem){
                if (($key = array_search($status, $keys)) !== false) {
                    unset($keys[$key]);
                }
            }

            foreach ($keys as $keyItem){
                $responseData[$created_by][$keyItem] = [];
            }
            ksort($responseData[$created_by]);
        }

        return $responseData;
    }

    public function getDecisionStatus($applications, $statusLogsData, $statusLogsSingle, $categoriesData){
        $response = [];
        $total = [];
        foreach ($applications as $application){
            $statusLogs = $statusLogsData[$application->id];
            if (!empty(array_intersect(['form completed', 'incomplete form'], array_values($statusLogsSingle[$application->id])))) {

                $from_status = 'form completed';
                if (in_array($statusLogsSingle[$application->id], ['incomplete form'])){
                    $from_status = 'incomplete form';
                }

                $timeTaken = $this->getTimeTakenBetweenLatest($from_status, 'approved', $statusLogs);
                if (!$timeTaken) {
                    $timeTaken = $this->getTimeTakenBetweenLatest($from_status, 'rejected', $statusLogs);
                    if (!$timeTaken) {
                        $timeTaken = $this->getTimeTakenBetweenLatest($from_status, 'aborted', $statusLogs);
                    }
                }
                if ($timeTaken) {

                    $tatStatus = $this->getTATStatus($categoriesData, $application->purpose);
                    if ($tatStatus) {
                        if (!isset($response[$tatStatus][$application->created_by])) {
                            $response[$tatStatus][$application->created_by] = $timeTaken['time_diff'];
                        } else {
                            $response[$tatStatus][$application->created_by] += $timeTaken['time_diff'];
                        }

                        if (!isset($total[$tatStatus][$application->created_by])){
                            $total[$tatStatus][$application->created_by] = 1;
                        }else {
                            $total[$tatStatus][$application->created_by]++;
                        }
                    }
                }
            }
        }

        return ['response' => $response, 'total' => $total];
    }

    public function getDisbursementStatus($applications, $statusLogsData, $statusLogsSingle, $categoriesData){
        $response = [];
        $total = [];
        foreach ($applications as $application){
            $statusLogs = $statusLogsData[$application->id];
            if (!empty(array_intersect(['approved'], array_values($statusLogsSingle[$application->id])))) {

                $from_status = 'approved';

                $timeTaken = $this->getTimeTakenBetweenLatest($from_status, 'disbursed', $statusLogs);

                if ($timeTaken) {

                    $tatStatus = $this->getTATStatus($categoriesData, $application->purpose);
                    if ($tatStatus) {
                        if (!isset($response[$tatStatus][$application->created_by])) {
                            $response[$tatStatus][$application->created_by] = $timeTaken['time_diff'];
                        } else {
                            $response[$tatStatus][$application->created_by] += $timeTaken['time_diff'];
                        }

                        if (!isset($total[$tatStatus][$application->created_by])){
                            $total[$tatStatus][$application->created_by] = 1;
                        }else {
                            $total[$tatStatus][$application->created_by]++;
                        }
                    }
                }
            }
        }

        return ['response' => $response, 'total' => $total];
    }

    public function getTATStatus($categoriesData, $purpose){
        if ($purpose == 21){
            $d =4;
        }
        foreach ($this->tatWiseCategories as $key => $value) {
            if (isset($categoriesData[$key]) && in_array($purpose, $categoriesData[$key])) {
                return $key;
            }
        }
        return false;
    }

    public function getTimeTakenBetweenLatest($from_status, $to_status, $status){
        $to_date = null;
        $from_date = null;
        foreach ($status as $item) {
            if (in_array($item->status, [$from_status, $to_status])){
                if (!$from_date && $from_status == $item->status){
                    $from_date = $item->created_at;
                }elseif(!$to_date){
                    $to_date = $item->created_at;
                }

                if ($to_date && $from_date){
                    break;
                }
            }
        }

        if ($to_date && $from_date){
            $timeDiff = strtotime($to_date) - strtotime($from_date);
            return ['time' => $this->secondsToDays($timeDiff, 1), 'time_diff' => $timeDiff];
        }

        return false;
    }

    public function saveCfplrLogs(Request $request)
    {
        $data = $request->all();
        $lender_id = (isset($data['lender_id']) && $data['lender_id'] != '') ? $data['lender_id'] : null;
    
        try {
            if (isset($lender_id)) {
                // Fetch the existing active CFPLR log
                $existingActiveLog = CFPLRLogs::where('lender_id', $lender_id)
                    ->where('status', 'ACTIVE')
                    ->whereNull('deleted_at')
                    ->first();
    
                if ($existingActiveLog) {
                    // Update the expiry date of the existing active log to one day before the effective date of the new rule
                    $effectiveDate = Carbon::parse($data['effective_date'])->startOfDay();
                    $expiryDate = $effectiveDate->copy()->format('Y-m-d');
                    $existingActiveLog->expiry_date = $expiryDate;
                    $existingActiveLog->save();
                }
    
                // Determine the status of the new CFPLR rule
                $status = Carbon::parse($data['effective_date'])->startOfDay()->equalTo(Carbon::now()->startOfDay()) ? 'ACTIVE' : 'INACTIVE';
    
                // Save the new CFPLR rule
                CFPLRLogs::create([
                    'lender_id' => $lender_id,
                    'cfplr_value' => isset($data['cfplr']) ? $data['cfplr'] : 0,
                    'effective_date' => isset($data['effective_date']) ? $data['effective_date'] : '',
                    'status' => $status,
                    'created_by' => isset(Auth::user()->id) ? Auth::user()->id : 0,
                    'updated_by' => isset(Auth::user()->id) ? Auth::user()->id : 0,
                ]);
    
                // If the new CFPLR rule is set to ACTIVE, deactivate the existing active rule
                if ($status === 'ACTIVE' && $existingActiveLog) {
                    $existingActiveLog->status = 'INACTIVE';
                    $existingActiveLog->save();
                }
            }
        } catch (\Exception $e) {
            (new Slack(new Request(['title' => 'CFPLR Update Failed', 'fields' => [
                'error' => $e->getMessage(),
                'line' => $e->getLine(),
                'trace' => $e->getTraceAsString(),
                'lender_id' => $data['lender_id']
            ], 'group' => Slack::$STAGE_TESTING])))->send();
        }
    
        return response()->json(['success' => true]);
    }
    
    public function getInitialCFPLRRules(Request $request)
    {                                           
        $data = $request->all();
        $lender_id = (isset($data['lender_id']) && $data['lender_id'] != '') ? $data['lender_id'] : null;
        // dd($lender_id);
        $data = [];
        try{
            if(isset($lender_id)){
                   // Retrieve the active CFPLR rule
                $activeRule = CFPLRLogs::where('lender_id', $lender_id)
                ->where('status', 'ACTIVE')
                ->whereNull('deleted_at')
                ->first();

                // Retrieve the instructed CFPLR rule
                $instructedRule = CFPLRLogs::where('lender_id', $lender_id)
                    ->where('effective_date', '>', now())
                    ->whereNull('deleted_at')
                    ->orderBy('created_at', 'DESC')
                    ->first();

                // Ensure the instructed rule is not the same as the active rule
                if ($instructedRule && $activeRule && $instructedRule->id == $activeRule->id) {
                    $instructedRule = null;
                }
                $data['activeRule'] = $activeRule;
                $data['instructedRule'] = $instructedRule;

                return response()->json(['success' => true, 'data' => $data]);
            }
        }catch (\Exception $e){
            (new Slack(new Request(['title' => 'CFPLR Update Failed', 'fields' => ['error' => $e->getMessage(), 'line' => $e->getLine(), 'trace' => $e->getTraceAsString(), 'lender_id' => $data['lender_id']], 'group' => Slack::$STAGE_TESTING])))->send();
            return response()->json(['success' => false, 'data' => $data]);
        }
        return response()->json(['success' => true, 'data' => $data]);
    }

    public function saveKYCRules(Request $request)
    {
        // For saving and creating logs
        $data = $request->all();

        foreach ($data as $item) {
            if (isset($item['category_id']) && isset($item['lender_id'])) {
                $existingRules = MerchantCategoryKYCRules::where('category_id', $item['category_id'])
                                ->where('lender_id', $item['lender_id'])
                                ->get()
                                ->keyBy('kyc_type');
    
                $kycRule1 = $existingRules->get($item['kyc_rule_1']['kyc_type']);
                $kycRule2 = $existingRules->get($item['kyc_rule_2']['kyc_type']);
    
                if ($kycRule1 && (
                    $kycRule1->condition != $item['kyc_rule_1']['condition'] || 
                    $kycRule1->net_loan_amount != $item['kyc_rule_1']['net_loan_amount'] || 
                    $kycRule1->kyc_type != $item['kyc_rule_1']['kyc_type']
                )) {
                    MerchantCategoryKYCRulesLogs::create([
                        'lender_id' => $item['lender_id'],
                        'category_id' => $item['category_id'],
                        'kyc_type_old' => $kycRule1->kyc_type,
                        'kyc_type_new' => $item['kyc_rule_1']['kyc_type'],
                        'condition_old' => $kycRule1->condition,
                        'condition_new' => $item['kyc_rule_1']['condition'],
                        'net_loan_amount_old' => $kycRule1->net_loan_amount,
                        'net_loan_amount_new' => $item['kyc_rule_1']['net_loan_amount'],
                        'created_by' => Auth::user()->id,
                    ]);
                }
    
                if ($kycRule2 && (
                    $kycRule2->condition != $item['kyc_rule_2']['condition'] || 
                    $kycRule2->net_loan_amount != $item['kyc_rule_2']['net_loan_amount'] || 
                    $kycRule2->kyc_type != $item['kyc_rule_2']['kyc_type']
                )) {
                    MerchantCategoryKYCRulesLogs::create([
                        'lender_id' => $item['lender_id'],
                        'category_id' => $item['category_id'],
                        'kyc_type_old' => $kycRule2->kyc_type,
                        'kyc_type_new' => $item['kyc_rule_2']['kyc_type'],
                        'condition_old' => $kycRule2->condition,
                        'condition_new' => $item['kyc_rule_2']['condition'],
                        'net_loan_amount_old' => $kycRule2->net_loan_amount,
                        'net_loan_amount_new' => $item['kyc_rule_2']['net_loan_amount'],
                        'created_by' => Auth::user()->id,
                    ]);
                }
    
                // Delete existing records for this category to avoid duplicates
                MerchantCategoryKYCRules::where('category_id', $item['category_id'])->where('lender_id', $item['lender_id'])->delete();

                // Save first KYC rule
                MerchantCategoryKYCRules::create([
                    'lender_id' => $item['lender_id'],
                    'category_id' => $item['category_id'],
                    'kyc_type' => $item['kyc_rule_1']['kyc_type'],
                    'condition' => $item['kyc_rule_1']['condition'],
                    'created_by' => Auth::user()->id,
                    'updated_by' => Auth::user()->id,
                    'net_loan_amount' => $item['kyc_rule_1']['net_loan_amount'],
                ]);

                // Save second KYC rule
                MerchantCategoryKYCRules::create([
                    'lender_id' => $item['lender_id'],
                    'category_id' => $item['category_id'],
                    'kyc_type' => $item['kyc_rule_2']['kyc_type'],
                    'condition' => $item['kyc_rule_2']['condition'],
                    'created_by' => Auth::user()->id,
                    'updated_by' => Auth::user()->id,
                    'net_loan_amount' => $item['kyc_rule_2']['net_loan_amount'],
                ]);
            }
        }

        return response()->json(['success' => true]);
    }    

    public function getKYCRulesLogs(Request $request)
    {
        $data = $request->all();
        $lender_id = (isset($data['lender_id']) && $data['lender_id'] != '') ? $data['lender_id'] : null;
        $logs = MerchantCategoryKYCRulesLogs::with('category')->with('user')->where('lender_id', $lender_id)->orderBy('created_at','desc')->get();
        return response()->json($logs);
    }

    public function getInitialKYCRules(Request $request)
    {                                           
        $data = $request->all();
        $lender_id = (isset($data['lender_id']) && $data['lender_id'] != '') ? $data['lender_id'] : null;
        $data = [];

        if(isset($lender_id)){
            $rules = MerchantCategoryKYCRules::all()->where('lender_id', $lender_id)->groupBy('category_id');
        
            foreach ($rules as $categoryId => $categoryRules) {
                $categoryData = [
                    'category_id' => $categoryId,
                    'kyc_rule_1' => [
                        'kyc_type' => $categoryRules[0]->kyc_type ?? '',
                        'condition' => $categoryRules[0]->condition ?? '',
                        'net_loan_amount' => $categoryRules[0]->net_loan_amount ?? 0
                    ],
                    'kyc_rule_2' => [
                        'kyc_type' => $categoryRules[1]->kyc_type ?? '',
                        'condition' => $categoryRules[1]->condition ?? '',
                        'net_loan_amount' => $categoryRules[1]->net_loan_amount ?? 0
                    ]
                ];
                $data[] = $categoryData;
            }
        }
    
        return response()->json(['success' => true, 'data' => $data]);
    }


    public function getInitialLegalCharges(Request $request)
    {                                           
        $data = $request->all();
        $data = [];

        $rules = MerchantCategoryLegalCharges::all()->groupBy('category_id');
    
        foreach ($rules as $categoryId => $categoryRules) {
            $categoryData = [
                'category_id' => $categoryId,
                'legal_charges' => [
                    'condition' => $categoryRules[0]->condition ?? '',
                    'net_loan_amount' => $categoryRules[0]->net_loan_amount ?? 0,
                    'legal_charge' => $categoryRules[0]->legal_charges ?? 0
                ]
            ];
            $data[] = $categoryData;
        }
    
        return response()->json(['success' => true, 'data' => $data]);
    }

    public function saveLegalCharges(Request $request)
    {
        // For saving and creating logs
        $data = $request->all();

        foreach ($data as $item) {
            if (isset($item['category_id'])) {
    
                $existingRules = MerchantCategoryLegalCharges::where('category_id', $item['category_id'])->orderBy('created_at','desc')->first();

                if ((!isset($existingRules->id)) || ($existingRules && (
                    $existingRules->condition != $item['legal_charges']['condition'] || 
                    $existingRules->net_loan_amount != $item['legal_charges']['net_loan_amount'] || 
                    $existingRules->legal_charges != $item['legal_charges']['legal_charge']
                ))){
                    if(isset($existingRules->id)) {
                        // Delete existing records for this category to avoid duplicates
                        MerchantCategoryLegalCharges::where('category_id', $item['category_id'])->delete();
                    }

                    // Save legal charges rule
                    MerchantCategoryLegalCharges::create([
                        'category_id' => $item['category_id'],
                        'condition' => $item['legal_charges']['condition'],
                        'net_loan_amount' => $item['legal_charges']['net_loan_amount'],
                        'legal_charges' => $item['legal_charges']['legal_charge'],
                        'created_by' => Auth::user()->id,
                        'updated_by' => Auth::user()->id,
                    ]);

                }else{
                    continue;
                }
              
            }
        }

        return response()->json(['success' => true]);
    }

    public function setCategoryStatus(){

        $category = MerchantCategoryTAT::where('category_id', $this->request->get('category_id'))->first(['id']);

        if (isset($category->id)){
            $merchantCategoryTAT = MerchantCategoryTAT::find($category->id);
            $merchantCategoryTAT->fill(['status' => $this->request->get('status'), 'updated_by' => $this->request->get('user_id'), 'updated_at' => Carbon::now()->toDateTimeString()]);
            $merchantCategoryTAT->save();
        }else {
            $merchantCategoryTAT = new MerchantCategoryTAT();
            $merchantCategoryTAT->fill(['category_id' => $this->request->get('category_id'), 'status' => $this->request->get('status'), 'created_by' => $this->request->get('user_id'), 'created_at' => Carbon::now()->toDateTimeString()]);
            $merchantCategoryTAT->save();
        }

        if (isset($merchantCategoryTAT->id)){
            return response()->json(['status' => 'OK']);
        }

        return response()->json(['status' => 'FAIL', 'message' => 'Please try again later']);
    }

    public function getRunningService(){
        $services = Services::where('created_at','>=',Carbon::now()->subDays(7)->toDateTimeString())->where('created_at','<=',Carbon::now()->toDateTimeString())->orderBy('created_at','desc')->distinct('name')->get();
        $response = [];
        $savedService = [];
        foreach ($services as $service){

            $status = 'Inactive';
            $date = Carbon::parse($service->created_at);

            if ($date->isToday()){
                $status = 'Active';
            }else if ($date->isYesterday()){
                $status = 'Pending';
            }

            $serviceTerm = $this->getServiceTerms($service->name);

            $job_executed = $service->service_count ? $service->service_count : 0;

            if ($service->start_time) {
                $start_time = Carbon::now()->parse(date('Y-m-d H:i:s',$service->start_time));
                $created_at = Carbon::now()->parse($service->created_at);
                $time_taken = $created_at->diffInMinutes($start_time);
            }else{
                $time_taken = 0;
            }

            if (!in_array($serviceTerm['name'], $savedService)) {
                $response[] = [
                    'name' => $serviceTerm['name'],
                    'description' => $serviceTerm['description'],
                    'status' => $status,
                    'time_taken' => $time_taken,
                    'job_executed' => $job_executed,
                    'last_seen' => $service->created_at
                ];
                $savedService[] = $serviceTerm['name'];
            }
        }

        return view('admin.services', ['data' => $response]);
    }

    public function getServiceTerms($name){
        switch ($name){
            case 'tally:connect':
                return [
                    'name' => 'Tally',
                    'description' => 'Tally-Payment Integration'
                ];
                break;
            case 'emi:dueReminder':
                return [
                    'name' => 'EMI Due Reminder',
                    'description' => 'Due Reminders'
                ];
                break;
            case 'adviser:application_reminder':
                return [
                    'name' => 'Advisor Application Reminder',
                    'description' => 'Advisor Application Integration'
                ];
                break;
            case 'monthly_account_statement':
                return [
                    'name' => 'Monthly Account Statement',
                    'description' => 'Monthly Customer Account Statement'
                ];
                break;
            case 'weekly_update_merchant':
                return [
                    'name' => 'Merchant Report',
                    'description' => 'Merchant Weekly Report'
                ];
                break;
            case 'rewards':
                return [
                    'name' => 'Rewards Communication',
                    'description' => 'Reward Communication to user'
                ];
                break;
            default :
                return [
                    'name' => $name,
                    'description' => $name
                ];
                break;
        }
    }

    public function getDataInDateRange($isDateRange = false){

        if ($isDateRange == 2) {
            ConsumerApplication::$isMandateManyFilter = ['from_date' => $this->from_date,'to_date' => $this->to_date];
        }

        $applications = ConsumerApplication::whereHas('logs', function($query) use ($isDateRange){
            $query->where('status','disbursed')->where('is_reversed',0);
            if ($isDateRange == 1) {
                $query->where('created_at', '>=', $this->from_date)->where('created_at', '<=', $this->to_date);
            }
        });
        if ($isDateRange == 3) {
            $applications = $applications->whereHas('emi', function ($query) {
                $query->where('emi_date', '<', Carbon::now()->toDateTimeString());
            });
        }
        $applications = $applications->with(['documents'=> function($query){
            $query->where('file_type','security_cheques');
        }])->with('mandateMany')->whereNull('is_deleted')->get();

        return $applications;
    }

    public function getNachData($applications, $isAction = false, $reValidate = false){
        $consumerApplication = new ConsumerApplicationController();
        $courier = new CouriersController($this->request);
        $courier->isNachStatus = true;
        $courier->isActionDateFilter = $isAction;
        $nach_couriered = 0;
        $security_cheques = 0;
        $nach_active = 0;
        $nach_submitted = 0;

        $nach_couriered_id = [];
        $security_cheques_id = [];
        $nach_active_id = [];
        $nach_submitted_id = [];

        foreach ($applications as $application){
            $courierStatus = $courier->getCourier($application->id, 2);
            $appStatus = true;
            $appStatusSubmitted = true;
            if (isset($courierStatus['courier_sent'])){
                if ($reValidate){
                    $mandateCheck = Mandate::where('consumer_application_id', $application->id)->where('status', 'rejected')->first(['id']);
                    if (!isset($mandateCheck->id)){
                        $appStatus = false;
                    }
                }
                if ($appStatus) {
                    $nach_couriered++;
                    $nach_couriered_id[] = $application->id;
                }
            }

            if (count($application->documents)){
                $security_cheques++;
                $security_cheques_id[] = $application->id;
            }

            $mandate = $consumerApplication->isMandateActive($application);
            if ($mandate){
                $nach_active++;
                $nach_active_id[] = $application->id;
            }
            $mandate = $consumerApplication->isMandateActive($application,'submitted');
            if ($mandate){
                if ($reValidate){
                    $mandateCheck = Mandate::where('consumer_application_id', $application->id)->where('status', 'submitted')->get(['id']);
                    if ($mandateCheck && count($mandateCheck) <= 1){
                        $appStatusSubmitted = false;
                    }
                }
                if ($appStatusSubmitted) {
                    $nach_submitted++;
                    $nach_submitted_id[] = $application->id;
                }
            }
        }

        return ['nach_couriered' => $nach_couriered, 'security_cheques' => $security_cheques, 'nach_active' => $nach_active, 'nach_submitted' => $nach_submitted, 'nach_couriered_id' => $nach_couriered_id, 'security_cheques_id' => $security_cheques_id, 'nach_active_id' => $nach_active_id, 'nach_submitted_id' => $nach_submitted_id];
    }

    public function getWeeklyNachDashboard(){
        $data = [];

        $this->from_date = Carbon::now()->startOfWeek()->toDateTimeString();
        $this->to_date = Carbon::now()->endOfWeek()->toDateTimeString();

        if(!$this->request->has('from_date')){
            $this->request->merge(['from_date' => $this->from_date,'to_date' => $this->to_date]);
        }
        $applications = $this->getDataInDateRange(1);
        $data['nachInDateRangeDisbursed'] = $this->getNachData($applications);
        $applications = $this->getDataInDateRange();
        $data['nachFreshRejected'] = $this->getNachData($applications, false, true);
        $applications = $this->getDataInDateRange(2);
        $data['nachInDateRangeAction'] = $this->getNachData($applications, true);
        $applications = $this->getDataInDateRange(3);
        $data['nachIneEMIComeDue'] = $this->getNachData($applications);

        setlocale(LC_MONETARY, 'en_IN.UTF-8');
        $dashboard = [
            array(
                'title' => '<b>Nach Data Disbursed In this Week</b>',
                'description' => '',
                'count' => '',
                'applications' => ''
            ),
            array(
                'title' => '<b>Nach Data Fresh Rejected</b>',
                'description' => '',
                'count' => '',
                'applications' => ''
            ),
            array(
                'title' => '<b>Nach Data on Action</b>',
                'description' => '',
                'count' => '',
                'applications' => ''
            ),
            array(
                'title' => '<b>One EMI has Come Due</b>',
                'description' => '',
                'count' => '',
                'applications' => ''
            ),


            array(
                'title' => 'Nach Couriered',
                'description' => '',
                'count' => $data['nachInDateRangeDisbursed']['nach_couriered'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeDisbursed']['nach_couriered_id']))
            ),
            array(
                'title' => 'Nach Couriered',
                'description' => '',
                'count' => $data['nachFreshRejected']['nach_couriered'],
                'applications' => base64_encode(json_encode($data['nachFreshRejected']['nach_couriered_id']))
            ),
            array(
                'title' => 'Nach Couriered',
                'description' => '',
                'count' => $data['nachInDateRangeAction']['nach_couriered'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeAction']['nach_couriered_id']))
            ),
            array(
                'title' => 'Nach Couriered',
                'description' => '',
                'count' => $data['nachIneEMIComeDue']['nach_couriered'],
                'applications' => base64_encode(json_encode($data['nachIneEMIComeDue']['nach_couriered_id']))
            ),

            array(
                'title' => 'Security Cheques',
                'description' => '',
                'count' => $data['nachInDateRangeDisbursed']['security_cheques'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeDisbursed']['security_cheques_id']))
            ),
            array(
                'title' => 'Security Cheques',
                'description' => '',
                'count' => $data['nachFreshRejected']['security_cheques'],
                'applications' => base64_encode(json_encode($data['nachFreshRejected']['security_cheques_id']))
            ),
            array(
                'title' => 'Security Cheques',
                'description' => '',
                'count' => $data['nachInDateRangeAction']['security_cheques'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeAction']['security_cheques_id']))
            ),
            array(
                'title' => 'Security Cheques',
                'description' => '',
                'count' => $data['nachIneEMIComeDue']['security_cheques'],
                'applications' => base64_encode(json_encode($data['nachIneEMIComeDue']['security_cheques_id']))
            ),


            array(
                'title' => 'Nach Active',
                'description' => '',
                'count' => $data['nachInDateRangeDisbursed']['nach_active'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeDisbursed']['nach_active_id']))
            ),
            array(
                'title' => 'Nach Active',
                'description' => '',
                'count' => $data['nachFreshRejected']['nach_active'],
                'applications' => base64_encode(json_encode($data['nachFreshRejected']['nach_active_id']))
            ),
            array(
                'title' => 'Nach Active',
                'description' => '',
                'count' => $data['nachInDateRangeAction']['nach_active'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeAction']['nach_active_id']))
            ),
            array(
                'title' => 'Nach Active',
                'description' => '',
                'count' => $data['nachIneEMIComeDue']['nach_active'],
                'applications' => base64_encode(json_encode($data['nachIneEMIComeDue']['nach_active_id']))
            ),


            array(
                'title' => 'Nach Submitted',
                'description' => '',
                'count' => $data['nachInDateRangeDisbursed']['nach_submitted'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeDisbursed']['nach_submitted_id']))
            ),
            array(
                'title' => 'Nach Submitted',
                'description' => '',
                'count' => $data['nachFreshRejected']['nach_submitted'],
                'applications' => base64_encode(json_encode($data['nachFreshRejected']['nach_submitted_id']))
            ),
            array(
                'title' => 'Nach Submitted',
                'description' => '',
                'count' => $data['nachInDateRangeAction']['nach_submitted'],
                'applications' => base64_encode(json_encode($data['nachInDateRangeAction']['nach_submitted_id']))
            ),
            array(
                'title' => 'Nach Submitted',
                'description' => '',
                'count' => $data['nachIneEMIComeDue']['nach_submitted'],
                'applications' => base64_encode(json_encode($data['nachIneEMIComeDue']['nach_submitted_id']))
            ),
        ];

        $isMobile = $this->isMobile();

        return view('admin.weekly_nach', ['dashboard' => $dashboard,'isMobile' => $isMobile]);
    }

    function isMobile() {
        $aMobileUA = array(
            '/iphone/i' => 'iPhone',
            '/ipod/i' => 'iPod',
            '/ipad/i' => 'iPad',
            '/android/i' => 'Android',
            '/blackberry/i' => 'BlackBerry',
            '/webos/i' => 'Mobile'
        );

        //Return true if Mobile User Agent is detected
        foreach($aMobileUA as $sMobileKey => $sMobileOS){
            if(isset($_SERVER['HTTP_USER_AGENT'])) {
                if (preg_match($sMobileKey, $_SERVER['HTTP_USER_AGENT'])) {
                    return true;
                }
            }
        }
        //Otherwise return false..
        return false;
    }

    public function getMissedCalls(){

        $exotel_api_key = _env('EXOTEL_API_KEY');
        $exotel_sid = _env('EXOTEL_SID');
        $exotel_token = _env('EXOTEL_TOKEN');
        $from_date = substr_replace($this->from_date,'+','10',-8);
        $to_date = substr_replace($this->to_date,'+','10', -8);

        $url = "https://".$exotel_api_key.":".$exotel_token."@api.exotel.com/v1/Accounts/".$exotel_sid."/Calls.json?DateCreated=gte:".$from_date."%3Blte:".$to_date."&Direction=inbound&Leg2Status=no-answer,busy,failed";
        // $payload = json_encode(array('PageSize'=>'10'));
        $curl = curl_init();
        curl_setopt_array($curl, array(
            CURLOPT_URL => $url,
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_ENCODING => "",
            CURLOPT_MAXREDIRS => 10,
            CURLOPT_TIMEOUT => 30,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => "GET",
            // CURLOPT_POSTFIELDS => "payload = ".$payload,
            CURLOPT_HTTPHEADER => array(
                "Content-Type: application/json",
            ),
        ));
        $response = curl_exec($curl);
        $err = curl_error($curl);
        curl_close($curl);
        if ($err) {
            echo "cURL Error #:" . $err;
        } 
        $data['response'] = json_decode($response);
        $data['err'] = json_decode($err);
        $data['from_date'] = date('Y-m-d',strtotime($this->from_date));
        $data['to_date'] = date('Y-m-d',strtotime($this->to_date));
        $page = $this->request->has('page') && $this->request->get('page') > 1 ? $this->request->get('page') : 0;
        $callss = $data['response']->Calls;
        $exist = property_exists($data['response'],'RestException');
        if(!$exist)
        {
            if(!empty($callss))
            {
                foreach($callss as $records)
                {
                    $number = ltrim($records->From,'0');
                    $num2[] = wall_encrypt($number);
                    $num[] = $number;
                    $called_at[$number]['start'] = $records->DateCreated;
                }
            $infos[] = ConsumerApplication::whereIn('mobile_number',$num2)->get();
            $logss[] = CallLogs::whereIn('to',$num)->get();
            $datas = array(array());
            foreach($num as $loops)
            {
                foreach($logss[0] as $logs){
                        if(!empty($logs)){
                                
                            if($logs->to == $loops){
                            $time = $logs->created_at;
                                if($time > $called_at[$loops]['start']){
                                    $flag = true;
                                    $datas[$loops]['status'] = 'called';
                                    goto called_back;
                                }
                            }else{
                                continue;
                            }
                        }
                }
                foreach($infos[0] as $info){

                 if(!empty($info))
                 {

                    if($info->mobile_number == $loops)
                    {
                    $datas[$loops]['id'] = $info->id;
                    $datas[$loops]['name'] = $info->name;
                    $datas[$loops]['status'] = 'Missed Call';
                    $datas[$loops]['message'] = 'consumer';
                    $datas[$loops]['purpose'] = 'Incoming';
                    goto called_back;
                    }else{
                    continue;
                    }
                }
            }
                
                    $datas[$loops]['id'] = 'Unknown';
                    $datas[$loops]['name'] = 'Unknown';
                    $datas[$loops]['status'] = 'Missed Call';
                    $datas[$loops]['message'] = 'Not-registered';
                    $datas[$loops]['purpose'] = 'Incoming';
            
            called_back:continue;

            }

                $data['info'] = $datas;
                $data['calls'] = $this->paginate($callss, 10, $page, ['path' => $this->request->url()]);
                $data['type'] = Auth::user()->user_type;
                return view('admin.reports.missed_call_dashboard', $data);
        
            }else{

                $data['calls'] = null;
                return view('admin.reports.missed_call_dashboard', $data);
            }
        }else{
            $data['err'] = $data['response']->RestException[0]->Description;
            \Session::flash('message',$data['err']);
            $data['calls'] = null ;
            return view('admin.reports.missed_call_dashboard', $data);
        }

    }

    public function creditPerformance(){

        $data['merchants'] = Merchant::where('status','active')->where('is_guest_active', 1)->where('pre_status', '!=', 1)->whereNull('deleted_at')->get(['id', 'merchant_name']);

        $data['users'] = User::where('user_type', 6)->whereNull('deleted_at')->get(['id','name']);
        $data['merchant_id'] = $this->request->get('merchant_id');
        $data['user_ids'] = $this->request->get('underwriter_id') ? array_values(array_filter($this->request->get('underwriter_id'))) : [];
        $data['from_date'] = $this->from_date;
        $data['to_date'] = $this->to_date;

        $merchantIds = [];
        $merchantList = [];
        if($this->request->get('merchant_id')) {
            $merchantIds[] = $this->request->get('merchant_id');
            $merchantName = Merchant::where('id', $this->request->get('merchant_id'))->first(['merchant_name']);
            $merchantList[$this->request->get('merchant_id')] = isset($merchantName->merchant_name) ? $merchantName->merchant_name : '';
        }else{
            foreach ($data['merchants'] as $merchant) {
                $merchantIds[] = $merchant->id;
                $merchantList[$merchant->id] = $merchant->merchant_name;
            }
        }

        $applications = ConsumerApplication::with('advisor')->whereIn('merchant_id', $merchantIds);
        if($data['user_ids']){
            $applications = $applications->whereIn('created_by', $data['user_ids']);
        }
        $applications = $applications->whereNotIn('state', ['form completed', 'incomplete form'])->where('created_at','>=', $this->from_date)->where('created_at','<=', $this->to_date)->whereNull('is_deleted')->get(['id','name','loan_amount','merchant_id','created_by', 'state' ,'created_at']);
        //$applications = ConsumerApplication::with('advisor')->whereIn('id', [5287, 5284, 5277, 5229, 5274, 5256, 5255])->whereNull('is_deleted')->get(['id','name','loan_amount','merchant_id','created_by', 'state' ,'created_at']);

        foreach ($applications as $application) {
            $statusLogs = StatusLogs::where('application_id', $application->id)->orderBy('created_at','asc')->get();
            $waiting_documents = $this->getTimeTakenBetween('waiting for decision', 'waiting for documents', $statusLogs);
            $waiting_pre_approved = $this->getTimeTakenBetween('waiting for decision', 'pre approved', $statusLogs, 1);
            $waiting_approved = $this->getTimeTakenBetween('waiting for decision', 'approved', $statusLogs, 1);
            $waiting_rejected = $this->getTimeTakenBetween('waiting for decision', 'rejected', $statusLogs, 1);
            $waiting_in_process = $this->getTimeTakenBetween('waiting for decision', 'in-process', $statusLogs, 1);
            $waiting_aborted = $this->getTimeTakenBetween('waiting for decision', 'aborted', $statusLogs, 1);

            if ($this->eligible($application->loan_amount, ($waiting_documents['minutes'] + $waiting_pre_approved['minutes'] + $waiting_approved['minutes'] + $waiting_rejected['minutes'] + $waiting_in_process['minutes'] + $waiting_aborted['minutes']))) {

                $data['reports'][] = [
                    'id' => $application->id,
                    'advisor_name' => isset($application->advisor->name) ? $application->advisor->name : '-',
                    'merchant_name' => isset($merchantList[$application->merchant_id]) ? $merchantList[$application->merchant_id] : '-',
                    'name' => $application->name,
                    'loan_amount' => $application->loan_amount,
                    'waiting_documents' => isset($waiting_documents['time']) ? $waiting_documents['time'] : '-',
                    'waiting_pre_approved' => isset($waiting_pre_approved['time']) ? $waiting_pre_approved['time'] : '-',
                    'waiting_approved' => isset($waiting_approved['time']) ? $waiting_approved['time'] : '-',
                    'waiting_rejected' => isset($waiting_rejected['time']) ? $waiting_rejected['time'] : '-',
                    'waiting_in_process' => isset($waiting_in_process['time']) ? $waiting_in_process['time'] : '-',
                    'waiting_aborted' => isset($waiting_aborted['time']) ? $waiting_aborted['time'] : '-',
                    'total_time' => $this->secondsToDays($this->cumulativeTime, 1),
                    'created_at' => $application->created_at,
                    'status' => $application->state,
                ];
            }

            $this->cumulativeTime = 0;
            $this->cumulativeTimePre = 0;
            $this->cumulativeTimeAbt = 0;
        }

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

    public function eligible($amount, $timeTaken){

        if ($timeTaken) {
            if ($amount <= 75000) {
                return $timeTaken > 10 ? true : false;
            } elseif ($amount > 75001 && $amount <= 200000) {
                return $timeTaken > 20 ? true : false;
            } elseif ($amount > 200001 && $amount <= 500000) {
                return $timeTaken > 60 ? true : false;
            } elseif ($amount > 500000) {
                return $timeTaken > 180 ? true : false;
            }
        }

        return false;
    }

    public function getTimeTakenBetween($from_status, $to_status, $statusLogs, $total = 0){

        $timeData = [];
        if (count($statusLogs) > 1) {
            $timeTaken = [];
            foreach ($statusLogs as $key => $log) {
                if($log->status == $from_status && isset($statusLogs[$key + 1]) && $statusLogs[$key + 1]->status == $to_status) {
                    $timeTaken[$log->status] = $log->created_at;
                }else if(isset($timeTaken[$from_status]) && $log->status == $to_status){
                    $timeTaken[$log->status] = $log->created_at;
                    $timeDiff = strtotime($timeTaken[$to_status]) - strtotime($timeTaken[$from_status]);

                    if ($timeDiff > 0) {
                        $timeData[] = $timeDiff;
                    }

                    $timeTaken = [];
                }
            }

            $timeDiff = array_sum($timeData);

            if($total) {
                $this->cumulativeTime += $timeDiff;
            }

            return ['time' => $this->secondsToDays($timeDiff, 1), 'minutes' => floor($timeDiff / 60)];
        }

        return false;
    }
}