/** * Execute the console command. * * @return mixed */ public function handle() { list($rootFolder, $current, $folder) = $this->setFolder(); $counter = 1; $measurementsCounter = 0; $file = fopen($folder . 'file' . $counter . '.csv', "w"); $maxRows = self::MAX_ROWS - 500; Measurement::with('station')->where('time', '>', Carbon::now()->subMonths(3))->orderBy('time', 'asc')->chunk(500, function ($measurements) use($folder, &$file, &$measurementsCounter, &$counter, $maxRows) { if ($measurementsCounter > $maxRows) { echo "measurements: " . $measurementsCounter . "\n"; echo "file created: " . $counter . ".csv\n"; $measurementsCounter = 0; if (is_resource($file)) { fclose($file); } $counter++; $file = fopen($folder . 'file' . $counter . '.csv', "w"); } foreach ($measurements as $measurement) { $array = $measurement->toArray(); unset($array['station_id'], $array['station']['id']); fputcsv($file, array_flatten($array)); } $measurementsCounter += 500; }); File::put($rootFolder . 'finished.txt', $current); }
/** * Shows all the weatherdata of stations on the same * longitude as Kyoto. The data displayed is one week * old. The data should only be shown if the temperature * is below 20 degrees Celsius. */ public function kyotoLongitude(Request $request) { $longitude = 135.733; $measurements = Measurement::with('station')->where('time', '>=', Carbon::now()->subWeek())->where('temperature', '<', 20)->whereHas('station', function ($sql) use($longitude) { $sql->where('longitude', '=', $longitude); })->orderBy('time', 'desc'); return view('measurement.overview', ['measurements' => $measurements->paginate(50)]); }
public function country($country) { $GLOBALS['country'] = $country; // $this->printHeaders(); $measurements = Measurement::where('timestamp', '>', $this->getStartDate())->whereHas('station', function ($query) { $query->where('country', $GLOBALS['country']); }); $measurements->chunk(1000, function ($measurements) { foreach ($measurements as $measurement) { echo implode(',', $measurement->toArray()) . PHP_EOL; } }); }
/** * Returns stationName, stationId, longitude, latitude and most recent precipitation * per location. */ public function showHeatMapJson(Request $request) { $property = $request->input('property'); $maxTime = Measurement::select('station_id', DB::raw('MAX(time) as time'))->groupBy('station_id'); $measurements = Measurement::join(DB::raw('(' . $maxTime->toSql() . ') "maxTime"'), function ($join) { $join->on('maxTime.station_id', '=', 'measurements.station_id'); $join->on('maxTime.time', '=', 'measurements.time'); })->with('station')->get(); foreach ($measurements as $measurement) { $station = $measurement->station; $data[$station->id] = (double) $measurement->{$property}; } return $data; }
/** * Generate a query in which the showings are stored * @param $show - which fields need to be shown * @return mixed $query - the generated query */ private function setShow($show) { $query = Measurement::with(['station' => function ($query) use($show) { $query->select($show['stations'])->addSelect('id'); }])->select($show['measurements'])->addSelect('station_id'); return $query; }