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