I have project which contains questions and answer about their vehicle buying experience and generate complaints after purchasing vehicle from the showroom,So they call customer and ask set of question regards to vehicle buying experience from showroom, 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('test, 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');
}
Thanks for @Zoran Stankovic idea,Creating Indexes did the trick incresed speed like lot so i had to index data in models,refer creating-indexes
Import
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
Sample of code what i did on model
For Registration Model
public function regindex()
{
Schema::table('registrations', function (Blueprint $table) {
$table->index('registration_id');
});
}
For Customer answer
public function customerindex()
{
Schema::table('customer_answers', function (Blueprint $table) {
$table->index(['ssi_track_id','question_id']);
});
}
For ssi_track model
public function ssiindex()
{
Schema::table('ssi_tracks', function (Blueprint $table) {
$table->index(['ssi_track_id','registration_id']);
});
}