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.');
}
