mardi 29 septembre 2020

Fetching report taking lot of time laravel

I have project which contains questions and answer about their vehicle buying experience and generate complaints before and after purchasing vehicle from the showroom,So they call customer and ask set of question regards to vehicle experience, This is outline of the project it's a old project with laravel 5.3 or 5.6 something,Now the problem is

When they taking 1 month report that contains details of cutomer details and vehicle details along with question and their answer to the questions,but if i take 1 month report it taking long time i mean long time,i tried to clear cache and stuff,still problem occurs Can any one help me on this,the data contains about 2000+ data

   public function excelquerep24(Request $request)
    {

        //getting all questions
        $questions = Question::where([['question_schedul', 2], ['status', 0]])->orderBy('order_no', 'asc')->get();

        //get answered customers;
        $answeredcalls =  Registration::
        join('ssi_tracks', 'ssi_tracks.ssi_track_id','=','registrations.registration_id')
        ->select('registrations.customerid', 'registrations.customername', 'registrations.phone', 'registrations.chassis', 'registrations.model', 'registrations.invoicedate', 'registrations.delivery', 'registrations.dealership', 'registrations.zone', 'registrations.branchcode', 'registrations.dh','registrations.zsm', 'registrations.branch', 'registrations.dse',  'ssi_tracks.ssi_track_id')
        ->where('ssi_tracks.track_first_status', '>','0')
        ->whereDate('registrations.delivery', '>=', $request->input('datefrom'))
        ->whereDate('registrations.delivery', '<=', $request->input('dateto'))
        ->distinct('registrations.customerid')
        ->get();
     
            $compsArray = [];


            // Define the Excel spreadsheet headers
        $compsArray[] = ['sl_no', 'Customer ID', 'Customer Name', 'Mobile', 'Chassis No', 'Vehicle Model', 'Invoice Date', 'Delivery Date', 'Dealership','Zone','Branch Code', 'DH','ZSM/SM',  'Branch', 'DSE',   'Status','PSF Date'];
        // return $compsArray;
         foreach ($questions as $question) {
            array_push($compsArray[0], $question->question);
            }
        // Convert each member of the returned collection into an array,
        // and append it to the payments array.

        $array = [];
        $i = 1;
        foreach ($answeredcalls as $call) {
            $newDateFormat3 = date('d-m-Y', strtotime((string)$call->delivery));
            $newDateFormat4 = date('d-m-Y', strtotime((string)$call->invoicedate));
            $call->delivery = $newDateFormat3;
            $call->invoicedate=$newDateFormat4;
            $psf24LastCallDate='';
            if ($call->zsm == '') {
                $sm = Registration::select('sm')->where('customerid', $call->customerid)->first();
                $call->zsm = $sm->sm;
            }
            $ssi = Ssi_track::where('ssi_track_id', $call->ssi_track_id)->first();
            if ($ssi->track_first_status == 1)
                $status = 'Partilly Called';
            if ($ssi->track_first_status == 2)
            {
               $lastcall= Call_track::where([['ssi_track_id', $call->ssi_track_id], ['call_schedule', 1]])->orderBy('call_track_id', 'desc')->first();
               if($lastcall)
               {
                
               
                if ($lastcall->call_responce == 1)
                   $status = 'Call Attended';
               if ($lastcall->call_responce == 2)
                   $status = 'Switched Off';
               if ($lastcall->call_responce == 3)
                   $status = 'Not Responding' ;
               if ($lastcall->call_responce == 4)
                   $status = 'Network Busy';
               if ($lastcall->call_responce == 5)
                   $status = 'Out of Coverage';
                 }  }      
            if ($ssi->track_first_status == 3)
                $status = 'Black listed call';
            if ($ssi->track_first_status == 4)
                $status = 'Call Completed';
            if ($ssi->track_first_status ==5 )
                $status = 'Call Closed';
            $tempArray = json_decode($call, true);
            array_unshift($tempArray, $i);
            $answeredArray = $tempArray;
            array_push($answeredArray,$status);
           $lastcall= Call_track::where([['ssi_track_id', $call->ssi_track_id], ['call_schedule', 1]])->orderBy('call_track_id', 'desc')->first();
           if($lastcall)
           $psf24LastCallDate = date('d-m-Y', strtotime((string)$lastcall->created_at));
                array_push($answeredArray,$psf24LastCallDate );
            
        
            foreach ($questions as $question) {
                $answer = Customer_answer:: join('questions', 'customer_answers.question_id', '=', 'questions.question_id')
                ->select('customer_answers.answer')->where([['customer_answers.ssi_track_id',$call->ssi_track_id ], ['questions.question_schedul', 2],['questions.status', 0],['customer_answers.question_id',$question->question_id]])
                ->orderBy('questions.order_no', 'asc')
                ->first();
                if($answer)
                    array_push($answeredArray, $answer->answer);
                else
                    array_push($answeredArray, " ");
            }
            
            unset($answeredArray['ssi_track_id']);

            $compsArray[] = $answeredArray;

            $i++;
        }

        // Generate and return the spreadsheet
        Excel::create('questionwise report 24 hours', function ($excel) use ($compsArray) {

            // Set the spreadsheet title, creator, and description
            $excel->setTitle('Questionwise');
            $excel->setCreator('Laravel')->setCompany('Beegains, LLC');
            $excel->setDescription('payments file');

            // Build the spreadsheet, passing in the payments array
            $excel->sheet('sheet1', function ($sheet) use ($compsArray) {
                $sheet->fromArray($compsArray, null, 'A1', false, false)
                    ->getStyle('A1')
                    ->getAlignment()
                    ->setWrapText(true);
            });

        })->download('xlsx');
    }


via Chebli Mohamed

Aucun commentaire:

Enregistrer un commentaire