public function export2($project) { // increase group_concat maxinum length in mysql \DB::statement(\DB::raw("SET SESSION group_concat_max_len = 120000;")); //$anscolumns = \DB::select(\DB::raw("SELECT GROUP_CONCAT(CONCAT('GROUP_CONCAT(IF(ans.akey=\"',qa.slug,'\",ans.value,NULL)) AS ',QUOTE(`qa`.`akey`))) AS ans FROM questions qs,qanswers qa WHERE project_id=$project->id AND qa.qid=qs.id;")); $anscolumns = \DB::select(\DB::raw("SELECT GROUP_CONCAT(DISTINCT CASE WHEN (qa.type='radio') THEN qs.qnum ELSE qa.akey END) AS qkey, GROUP_CONCAT(CONCAT('ans.akey=\"',qa.slug,'\"') SEPARATOR ' OR ') AS ans FROM questions qs,qanswers qa WHERE project_id={$project->id} AND qa.qid=qs.id GROUP BY CASE WHEN (qa.type='radio') THEN qs.id ELSE qa.id END;")); //dd($anscolumns); $query = ['pcode.id', 'pcode.pcode', 'pcode.state', 'pcode.district', 'pcode.township', 'pcode.village', 'results.incident_id as response']; foreach ($anscolumns as $anscol) { $query[] = \DB::raw("GROUP_CONCAT(IF({$anscol->ans},ans.value,NULL)) AS '" . $anscol->qkey . "'"); } //dd($query); $project_id = $project->id; $org_id = $project->org_id; $status = \App\PLocation::select($query)->where('pcode.org_id', '=', "{$project->org_id}")->leftjoin('results', function ($results) use($project) { $results->on('pcode.id', '=', 'results.resultable_id')->where('results.project_id', '=', $project->id); })->leftjoin('questions as qs', function ($questions) use($project) { $questions->where('qs.project_id', '=', $project->id); })->leftjoin('answers as ans', function ($answers) { $answers->on('ans.status_id', '=', 'results.id')->on('ans.qid', '=', 'qs.id'); })->groupBy('pcode', 'response')->orderBy('pcode', 'ASC')->orderBy('response', 'ASC')->get(); $status = $status->toArray(); //dd($status); $filename = preg_filter('/[^\\d\\w\\s\\.]/', ' ', $project->name . Carbon::now()); $file = Excel::create(str_slug($filename), function ($excel) use($status) { $excel->sheet('Sheetname', function ($sheet) use($status) { $sheet->fromArray($status); }); }); $store = $file->store('xls', false, true); // dd($store); //$file->setUseBOM(true); $storecsv = $file->store('csv', false, true); $media = Media::firstOrNew(['filename' => $store['title'], 'filedir' => $store['full']]); $media->filename = $store['title']; $media->filedir = $store['full']; $media->file = json_encode($store); $media->status = 1; $current_user = auth()->user(); $media->owner()->associate($current_user); $media2 = Media::firstOrNew(['filename' => $storecsv['title'], 'filedir' => $storecsv['full']]); $media2->filename = $storecsv['title']; $media2->filedir = $storecsv['full']; $media2->file = json_encode($storecsv); $media2->status = 1; $media2->owner()->associate($current_user); $media2->save(); if ($media->save()) { //return $file->download('xls'); return true; } throw new GeneralException('There was a problem creating export data. Please try again.'); }