/**
* 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;
}
