public static function getAggregation($device_ids) { $result = array(); foreach ($device_ids as $device_id) { $l_measurements_history_daily = \Model_L_Measurements_History_Daily::query()->where("device_id", $device_id)->where("captured_date", date("Y-m-d 00:00:00", strtotime("-1 day")))->get_one(); $result[$device_id] = $l_measurements_history_daily; } return $result; }
public function executeAggregation($device_id, $captured_before, $captured_after) { echo "--------start {$device_id}--------\n"; $captured_before_timestamp = strtotime($captured_before); $captured_after_timestamp = strtotime($captured_after); $current_timestamp = $captured_after_timestamp; do { $target_date = date("Y-m-d", $current_timestamp); $sql = "SELECT max(value) as peak_value, avg(value) as avg_value "; $sql .= "FROM l_measurements_history "; $sql .= "WHERE device_id = {$device_id} AND captured_at >= '{$target_date} 00:00:00' AND captured_at <= '{$target_date} 23:59:59'"; var_dump($target_date); $result = \DB::query($sql)->execute()->as_array(); if (empty($result) || !is_array($result)) { // can't executeAggregation } else { if (empty($result[0]['peak_value']) || empty($result[0]['avg_value'])) { // can't executeAggregation } else { $result = current($result); if (empty($result['peak_value']) || empty($result['avg_value'])) { continue; } // regist or update $captured_date = date("Y-m-d 00:00:00", $current_timestamp); $l_measurements_history_daily = \Model_L_Measurements_History_Daily::query()->where("captured_date", $captured_date)->where("device_id", $device_id)->get_one(); if (empty($l_measurements_history_daily)) { $l_measurements_history_daily = \Model_L_Measurements_History_Daily::forge(); $l_measurements_history_daily->captured_date = $captured_date; $l_measurements_history_daily->device_id = $device_id; } $l_measurements_history_daily->average_value = $result['avg_value']; $l_measurements_history_daily->peak_value = $result['peak_value']; $l_measurements_history_daily->save(); var_dump($sql); var_dump($result); } } // next day $current_timestamp += 86400; } while ($current_timestamp <= $captured_before_timestamp); echo "--------end {$device_id}--------\n"; // $sql = "SELECT * FROM users WHERE"; // $result = DB::query('SELECT * FROM `users`')->execute(); // strlen($captured_before); // var_dump($device_id); // var_dump($captured_before); // var_dump($captured_after); }