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