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.');
}
/**
*
* @param Project $project
* @param Request $request
* @return string
*/
public function getAllStatus($project, Request $request)
{
$parameters = ['project_id' => $project->id, 'org_id' => $project->org_id];
$column = DB::select(DB::raw("SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(results.section_id = ',results.section_id,', results.information, NULL)) AS s', results.section_id)) AS sections FROM results WHERE (results.project_id = {$project->id});"));
$query = ['pcode.id', 'pcode.pcode', 'pcode.state', 'pcode.district', 'pcode.township', 'pcode.village'];
if (!empty($column[0]->sections)) {
$query[] = DB::raw($column[0]->sections);
}
$project_id = $project->id;
$org_id = $project->org_id;
$status = PLocation::select($query)->where('pcode.org_id', $org_id)->with(['participants'])->leftjoin('results', function ($pcode) use($project_id) {
$pcode->on('pcode.id', '=', 'results.resultable_id')->where('results.project_id', '=', $project_id);
})->groupBy('pcode.id')->get();
return Datatables::of($status)->filter(function ($instance) use($request) {
if ($request->has('pcode')) {
$code = $request->get('pcode');
$instance->collection = $instance->collection->filter(function ($row) use($request) {
return Str::contains($row['pcode'], $request->get('pcode')) ? true : false;
});
}
if ($request->has('region')) {
$code = $request->get('region');
$instance->collection = $instance->collection->filter(function ($row) use($request) {
return Str::contains($row['state'], $request->get('region')) ? true : false;
});
}
if ($request->has('township')) {
$code = $request->get('township');
$instance->collection = $instance->collection->filter(function ($row) use($request) {
return Str::contains($row['township'], $request->get('township')) ? true : false;
});
}
if ($request->has('station')) {
$code = $request->get('station');
$instance->collection = $instance->collection->filter(function ($row) use($request) {
return Str::contains($row['village'], $request->get('station')) ? true : false;
});
}
if ($request->has('phone')) {
$code = $request->get('phone');
$instance->collection = $instance->collection->filter(function ($row) use($request) {
//dd($row->toArray());
return Str::contains($row['participants'], $request->get('phone')) ? true : false;
});
}
if (!is_null($request->get('section')) && $request->get('section') >= 0) {
$section = "s" . $request->get('section');
// array key will be s0,s1,s2 etc..
$status = $request->get('status');
if ($status == 'missing') {
$instance->collection = $instance->collection->filter(function ($row) use($request, $section) {
return Str::is($row[$section], null) ? true : false;
});
} else {
$instance->collection = $instance->collection->filter(function ($row) use($request, $section, $status) {
return Str::is($row[$section], $status) ? true : false;
});
}
}
})->editColumn('pcode', function ($modal) use($project) {
//if($modal->results){
return $modal->pcode . "<a href='" . route('data.project.results.edit', [$project->id, $modal->id]) . "' title='Edit'> <i class='fa fa-edit'></i></a>";
//}
})->editColumn('state', function ($modal) use($project) {
$state = !is_null($modal->state) ? $modal->state : '';
return _t($state);
})->editColumn('township', function ($modal) use($project) {
$township = !is_null($modal->township) ? $modal->township : '';
return _t($township);
})->editColumn('village', function ($modal) use($project) {
$village = !is_null($modal->village) ? $modal->village : '';
return _t($village);
})->make(true);
}
