public function surveyIndex($project, Request $request)
{
$sections = count($project->sections);
if ($project->validate == 'person') {
$resultable_type = 'App\\Participant';
} else {
$resultable_type = 'App\\PLocation';
}
$total = $project->organization->pcode->count() * 10 * $sections;
if ($total !== $project->results->count()) {
$current_user = auth()->user();
for ($i = 0; $i < $sections; $i++) {
for ($j = 1; $j <= 10; $j++) {
foreach ($project->organization->pcode as $pcode) {
$result = Result::firstOrNew(['section_id' => $i, 'incident_id' => $j, 'resultable_id' => $pcode->id, 'resultable_type' => $resultable_type]);
if (is_null($result->information)) {
$result->information = 'missing';
}
$result->user()->associate($current_user);
$result->project()->associate($project);
$result->resultable()->associate($pcode);
$result->save();
$final[] = $result;
}
}
}
}
$alocations = PLocation::where('org_id', $project->organization->id)->get();
return view('frontend.result.survey-index')->withAllLoc($alocations)->withProject($project)->withRequest($request);
}
/**
* Define your route model bindings, pattern filters, etc.
*
* @param \Illuminate\Routing\Router $router
* @return void
*/
public function boot(Router $router)
{
//
parent::boot($router);
$router->model('project', 'App\\Project');
$router->model('participants', 'App\\Participant');
$router->model('projects', 'App\\Project');
$router->model('questions', 'App\\Question');
$router->model('organizations', 'App\\Organization');
$router->model('result', 'App\\Result');
$router->model('code', 'App\\PLocation');
$router->bind('pcode', function ($value, $route) {
if ($route->project->validate == 'person') {
$pcode = \App\Participant::where('org_id', $route->project->organization->id)->where('participant_code', $value)->first();
} elseif ($route->project->validate == 'pcode') {
$pcode = \App\PLocation::where('org_id', $route->project->organization->id)->where('pcode', $value)->first();
}
if (!is_null($pcode)) {
return $pcode;
} else {
\App::abort(404, 'Not Found.');
}
});
$router->bind('person', function ($value, $route) {
//dd($route->project);
$person = \App\Participant::where('participant_code', $value)->where('org_id', $route->project->org_id)->first();
if (!is_null($person)) {
return $person;
} else {
\App::abort(404, 'Not Found.');
}
});
}
public function iresponse($project, Request $request)
{
$iresponseCol = Question::where('project_id', $project->id)->where('qnum', config('aio.iresponse'))->first();
$dbraw = DB::select(DB::raw("SELECT pcode.state,answers.*,q.* \n FROM pcode INNER JOIN results ON results.resultable_id = pcode.primaryid \n INNER JOIN answers ON answers.status_id = results.id \n INNER JOIN ( SELECT id,qnum FROM questions where id = '{$iresponseCol->id}') q ON q.id = answers.qid"));
$dbGroupBy = DB::select(DB::raw("SELECT pcode.state,answers.*,q.* \n FROM pcode INNER JOIN results ON results.resultable_id = pcode.primaryid \n INNER JOIN answers ON answers.status_id = results.id \n INNER JOIN ( SELECT id,qnum FROM questions where id = '{$iresponseCol->id}') q ON q.id = answers.qid GROUP BY results.id"));
$incidents = Result::where('project_id', $project->id);
$locations = PLocation::where('org_id', $project->org_id)->groupBy('state');
return view('frontend.result.response-incident')->withProject($project)->withRequest($request)->withQuestion($iresponseCol)->withIncidents($incidents)->withLocations($locations)->withDbraw(collect($dbraw))->withDbGroup(collect($dbGroupBy));
}
public static function boot()
{
parent::boot();
// Attach event handler, on deleting of the plocation
PLocation::deleting(function ($plocation) {
// Delete all tricks that belong to this plocation
foreach ($plocation->participants as $participant) {
$participant->forceDelete();
}
});
}
/**
* Temporary function to migrate old data
* @param type $pid
*/
public function migrate($pid, $pcode, $org)
{
//echo $org;
$pcode = substr($pcode, 0, -1);
// get participant
$participant = \App\Participant::find($pid);
// attach participant to pcode
if (!empty($pcode)) {
$location = \App\PLocation::where('org_id', $org)->where('pcode', $pcode)->first();
if (!empty($location)) {
$participant->pcode()->attach($location->primaryid);
$participant->save();
} else {
return false;
}
}
return;
}
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.');
}
private function makeTreeFromInput($location, $pcode, $org_id)
{
$trees = PLocation::firstOrNew(['org_id' => $org_id, 'pcode' => $pcode]);
//dd($location);
//$trees->primaryid = $org_id.$pcode;
if (isset($location->isocode)) {
$trees->isocode = $location->isocode;
} else {
$trees->isocode = 'MM';
}
if (isset($location->stateregion_english)) {
$trees->state = $location->stateregion_english;
} elseif (isset($location->state_region)) {
$trees->state = $location->state_region;
} elseif (isset($location->stateregion)) {
$trees->state = $location->stateregion;
} else {
}
if (isset($location->district_english)) {
$trees->district = $location->district_english;
} elseif (isset($location->district)) {
$trees->district = $location->district;
} elseif (isset($location->district_burmese)) {
$trees->district = $location->district_burmese;
} else {
}
if (isset($location->township_english)) {
$trees->township = $location->township_english;
} elseif (isset($location->township)) {
$trees->township = $location->township;
} elseif (isset($location->township_burmese)) {
$trees->township = $location->township_burmese;
} else {
}
//dd($location);
if (isset($location->village_tract)) {
$trees->village_tract = $location->village_tract;
} elseif (isset($location->village_tract_burmese)) {
$trees->village_tract = $location->village_tract_burmese;
} elseif (isset($location->village_tractward_burmese)) {
$trees->village_tract = $location->village_tractward_burmese;
} elseif (isset($location->town_village_tract_name)) {
$trees->village_tract = $location->town_village_tract_name;
} else {
}
if (isset($location->villageward)) {
$trees->village = $location->villageward;
} elseif (isset($location->village_mya_mmr3)) {
$trees->village = $location->village_mya_mmr3;
} elseif (isset($location->polling_station_location_burmese)) {
$trees->village = $location->polling_station_location_burmese;
} elseif (isset($location->ward_village_name_eng)) {
$trees->village = $location->ward_village_name_eng;
} else {
}
return $trees;
}
private function makeTreeFromInput($location, $pcode, $org_id)
{
$trees = PLocation::firstOrNew(['primaryid' => $pcode . '-' . $org_id]);
//dd($location);
$trees->primaryid = $pcode . '-' . $org_id;
if (isset($location->stateregion_english)) {
$trees->state = $location->stateregion_english;
} elseif (isset($location->state_region)) {
$trees->state = $location->state_region;
} elseif (isset($location->stateregion)) {
$trees->state = $location->stateregion;
} else {
}
if (isset($location->district_english)) {
$trees->district = $location->district_english;
} elseif (isset($location->district)) {
$trees->district = $location->district;
} elseif (isset($location->district_burmese)) {
$trees->district = $location->district_burmese;
} else {
}
if (isset($location->township_english)) {
$trees->township = $location->township_english;
} elseif (isset($location->township)) {
$trees->township = $location->township;
} elseif (isset($location->township_burmese)) {
$trees->township = $location->township_burmese;
} else {
}
if (isset($location->village_tract)) {
$trees->village_tract = $location->village_tract;
} elseif (isset($location->village_tract_burmese)) {
$trees->village_tract = $location->village_tract_burmese;
} elseif (isset($location->village_tracttown)) {
$trees->village_tract = $location->village_tracttown;
} else {
}
if (isset($location->villageward)) {
$trees->village = $location->villageward;
} elseif (isset($location->village_mya_mmr3)) {
$trees->village = $location->village_mya_mmr3;
} elseif (isset($location->polling_station_location_burmese)) {
$trees->village = $location->polling_station_location_burmese;
} else {
}
return $trees;
}
/**
*
* @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);
}
public function analysis($project)
{
$questions = $project->questions;
//dd($project->answersQ);
$results = $this->results->getAllResults($project->id);
/**
return $results->each(function($item, $key) use ($questions){
$qnum = $questions->each(function($qs, $qk) use ($item) {
foreach($item as $q => $a){
if($q == $qs->qnum){
//dd($a);
}
}
});
//dd($qnum);
});
$sections = $project->results->groupBy('section_id');
foreach ($sections as $section){
// dd($section);
}
*
*/
$located = \App\PLocation::where('org_id', $project->organization->id);
return view('backend.project.analysis')->withProject($project)->withQuestions($questions)->withLocations($located);
}
public function participantsDataSet($p, $org, \App\PLocation $location)
{
//dd($p);
/**
$obpair = [];
foreach ($p as $keys => $val){
preg_match_all('/([[:alnum:]]+)(?:_)?/', $keys, $match);
if(isset($match[1][1])){
if(preg_match('/^[a-zA-Z]$/',$match[1][1])){
$obpair[$match[1][0].$match[1][1]]['type'] = $match[1][0];
$obpair[$match[1][0].$match[1][1]]['key'] = $match[1][1];
}else{
$obpair[$match[1][0]]['type'] = $match[1][0];
$obpair[$match[1][0]]['key'] = $match[1][1];
}
}
}
print_r($obpair);
die();
*
*/
if (isset($p->supervisor_name)) {
$spv['name'] = $p->supervisor_name;
$spv['id'] = $p->supervisor_id;
$spvrole = $this->role->findRoleByName('Supervisor');
if (is_null($spvrole)) {
$sinput['name'] = 'Supervisor';
$sinput['level'] = 4;
$spvrole = $this->role->create($sinput);
}
$spv['role_id'] = $spvrole->id;
$spv['org_id'] = $org;
$sattr = ['participant_code' => $p->supervisor_id, 'org_id' => $org, 'name' => $p->supervisor_name];
$supervisor = \App\Participant::updateOrCreate($sattr, $spv);
}
if (isset($p->pcode)) {
$pcode = (string) $p->pcode;
//$observer['B']['pcode'] = (string) $p->pcode;
//$observer['A']['uec_code'] = $p->uec_polling_station_code;
//$observer['B']['uec_code'] = $p->uec_polling_station_code;
} else {
throw new GeneralException('No valid location code found! Check your upload file!');
}
if (isset($p->enumerator_name_english)) {
$observer['name'] = $p->enumerator_name_english;
} else {
$observer['name'] = 'No Name';
}
if (isset($p->phone_no_primary)) {
$observer['phones']['primary'] = (string) $p->phone_no_primary;
}
if (isset($p->phone_no_primary)) {
$observer['phones']['secondary'] = (string) $p->phone_no_secondary;
}
if (isset($p->enumerators_nrc_card)) {
$observer['nrc_id'] = $p->enumerators_nrc_card;
} else {
$observer['nrc_id'] = null;
}
if (isset($p->dob)) {
$observer['dob'] = $p->dob;
}
if (isset($p->gender)) {
$observer['gender'] = is_null($p->gender) ? 'Not Specified' : $p->gender;
}
if (isset($p->address)) {
$observer['address'] = $p->address;
}
if (isset($p->email)) {
$observer['email'] = $p->email;
}
if (isset($p->enumerator_id)) {
$role = $this->role->findRoleByName('Enumerator');
if (is_null($role)) {
$input['name'] = 'Enumerator';
$input['level'] = 0;
$role = $this->role->create($input);
}
//foreach($observer as $key => $person){
$participant_code = (string) $p->enumerator_id;
$observer['role_id'] = $role->id;
$observer['org_id'] = $org;
//$place = $this->pcode->findOrThrowException($observer['pcode'].'-'.$org);
$attr = ['participant_code' => $participant_code, 'org_id' => $org, 'nrc_id' => $observer['nrc_id']];
$participant = \App\Participant::updateOrCreate($attr, $observer);
if (isset($supervisor)) {
$participant->supervisor()->associate($supervisor);
$participant->save();
}
// detach first to avoid duplicate
$location->participants()->detach($participant);
$location->participants()->attach($participant);
$location->save();
//}
} else {
throw new GeneralException('No valid participant found!');
}
}