public function exportCSV($slug, Request $request)
{
$chart = Chart::where('slug', $slug)->orWhere('id', $slug)->first();
if (!$chart) {
return App::abort(404, "No such chart");
}
$config = json_decode($chart->config);
// Allow url parameters to override the chart's default
// selected countries configuration. We need to use the raw
// query string for this because we want to distinguish between
// %20 and +.
preg_match("/country=([^&]+)/", $_SERVER['QUERY_STRING'], $matches);
if ($matches) {
$countryCodes = array_map(function ($code) {
return urldecode($code);
}, explode("+", $matches[1]));
$query = DB::table('entities')->select('id', 'name')->whereIn('code', $countryCodes)->orWhere(function ($query) use($countryCodes) {
$query->whereIn('name', $countryCodes);
});
$config->{"selected-countries"} = $query->get();
}
$dims = json_decode($config->{"chart-dimensions"});
$varIds = array_map(function ($dim) {
return $dim->variableId;
}, $dims);
// Grab the variable names for the header row
$variableNameById = DB::table('variables')->whereIn('id', $varIds)->select('id', 'name')->lists('name', 'id');
$entityNames = array_map(function ($entity) {
return $entity->name;
}, $config->{"selected-countries"});
$entityIds = DB::table('entities')->whereIn('name', $entityNames)->lists('id');
$rows = [];
$headerRow = ['Country', 'Year'];
foreach ($varIds as $id) {
$headerRow[] = $variableNameById[$id];
}
$rows[] = $headerRow;
$currentRow = null;
// Now we pull out all the actual data
$dataQuery = DB::table('data_values')->whereIn('data_values.fk_var_id', $varIds);
if ($entityIds) {
$dataQuery = $dataQuery->whereIn('data_values.fk_ent_id', $entityIds);
}
$dataQuery = $dataQuery->select('value', 'year', 'data_values.fk_var_id as var_id', 'entities.id as entity_id', 'entities.name as entity_name', 'entities.code as entity_code')->join('entities', 'data_values.fk_ent_id', '=', 'entities.id')->orderBy('entities.name', 'DESC')->orderBy('year', 'ASC')->orderBy('fk_var_id', 'ASC');
foreach ($dataQuery->get() as $result) {
if (!$currentRow || $currentRow[0] != $result->entity_name || $currentRow[1] != $result->year) {
if ($currentRow) {
$rows[] = $currentRow;
}
// New row
$currentRow = [$result->entity_name, $result->year];
for ($i = 0; $i < sizeof($varIds); $i++) {
$currentRow[] = "";
}
}
$index = 2 + array_search($result->var_id, $varIds);
$currentRow[$index] = $result->value;
}
// Add the final row
if ($currentRow) {
$rows[] = $currentRow;
}
// Use memory file pointer so we can have fputcsv escape for us
$fp = fopen('php://memory', 'w+');
foreach ($rows as $row) {
fputcsv($fp, $row);
}
rewind($fp);
$csv = stream_get_contents($fp);
fclose($fp);
return response($csv, 200)->header('Content-Type', 'text/csv')->header('Content-Disposition', 'attachment; filename="' . $chart->slug . '.csv' . '"');
}
/**
* Update the specified resource in storage.
*
* @param int $id
* @return Response
*/
public function update(Request $request, $id)
{
$chart = Chart::where('project_id', $id)->first();
$input = Request::all();
$chart->update(['project_sponsor' => $input['project_sponsor'], 'product_owner' => $input['product_owner'], 'project_director' => $input['project_director'], 'project_manager' => $input['project_manager'], 'audit' => $input['audit'], 'group_compliance' => $input['group_compliance'], 'business_project_manager' => $input['business_project_manager'], 'business_analyst' => $input['business_analyst'], 'quality_management' => $input['quality_management'], 'it_security' => $input['it_security'], 'enterprise_architecture' => $input['enterprise_architecture'], 'strategic_procurement' => $input['strategic_procurement'], 'technical_project_manager' => $input['technical_project_manager']]);
flash()->success('Chart has been successfully updated!');
return redirect()->action('ChartsController@show', [$id]);
}
