/** * Archive les données de history dans historyArch */ public static function archive() { $sql = 'DELETE FROM history WHERE `datetime` <= "2000-01-01 01:00:00" OR `datetime` >= "2020-01-01 01:00:00"'; DB::Prepare($sql, array()); $sql = 'DELETE FROM historyArch WHERE `datetime` <= "2000-01-01 01:00:00" OR `datetime` >= "2020-01-01 01:00:00"'; DB::Prepare($sql, array()); $sql = 'DELETE FROM history WHERE `value` IS NULL'; DB::Prepare($sql, array()); $sql = 'DELETE FROM historyArch WHERE `value` IS NULL'; DB::Prepare($sql, array()); if (config::byKey('historyArchivePackage') >= config::byKey('historyArchiveTime')) { config::save('historyArchivePackage', config::byKey('historyArchiveTime') - 1); } if (config::byKey('historyArchiveTime') < 1) { $archiveDatetime = date('Y-m-d H:i:s', strtotime('- 1 hours')); } else { $archiveDatetime = date('Y-m-d H:i:s', strtotime('- ' . config::byKey('historyArchiveTime', 'core', 1) . ' hours')); } if (config::byKey('historyArchivePackage') < 1) { $archivePackage = '00:' . config::byKey('historyArchivePackage') * 60 . ':00'; } else { $archivePackage = config::byKey('historyArchivePackage') . ':00:00'; if (strlen($archivePackage) < 8) { $archivePackage = '0' . $archivePackage; } } $values = array('archiveDatetime' => $archiveDatetime); $sql = 'SELECT DISTINCT(cmd_id) FROM history WHERE `datetime`<:archiveDatetime'; $list_sensors = DB::Prepare($sql, $values, DB::FETCH_TYPE_ALL); foreach ($list_sensors as $sensors) { $cmd = cmd::byId($sensors['cmd_id']); if (is_object($cmd) && $cmd->getType() == 'info' && $cmd->getIsHistorized() == 1) { if ($cmd->getConfiguration('historyPurge', '') != '') { $purgeTime = date('Y-m-d H:i:s', strtotime($cmd->getConfiguration('historyPurge', ''))); $values = array('cmd_id' => $cmd->getId(), 'datetime' => $purgeTime); $sql = 'DELETE FROM historyArch WHERE cmd_id=:cmd_id AND `datetime` < :datetime'; DB::Prepare($sql, $values, DB::FETCH_TYPE_ROW); } if ($cmd->getSubType() == 'binary' || $cmd->getConfiguration('historizeMode', 'avg') == 'none') { $values = array('cmd_id' => $cmd->getId()); $sql = 'SELECT ' . DB::buildField(__CLASS__) . ' FROM history WHERE cmd_id=:cmd_id ORDER BY `datetime` ASC'; $history = DB::Prepare($sql, $values, DB::FETCH_TYPE_ALL, PDO::FETCH_CLASS, __CLASS__); for ($i = 1; $i < count($history); $i++) { if ($history[$i]->getValue() != $history[$i - 1]->getValue()) { $history[$i]->setTableName('historyArch'); $history[$i]->save(); $history[$i]->setTableName('history'); } $history[$i]->remove(); } $history[0]->setTableName('historyArch'); $history[0]->save(); $history[0]->setTableName('history'); $history[0]->remove(); $values = array('cmd_id' => $cmd->getId()); $sql = 'SELECT ' . DB::buildField(__CLASS__) . ' FROM historyArch WHERE cmd_id=:cmd_id ORDER BY datetime ASC'; $history = DB::Prepare($sql, $values, DB::FETCH_TYPE_ALL, PDO::FETCH_CLASS, __CLASS__); for ($i = 1; $i < count($history); $i++) { if ($history[$i]->getValue() == $history[$i - 1]->getValue()) { $history[$i]->setTableName('historyArch'); $history[$i]->remove(); } } } else { $values = array('cmd_id' => $sensors['cmd_id'], 'archiveDatetime' => $archiveDatetime); $sql = 'SELECT MIN(`datetime`) as oldest FROM history WHERE `datetime`<:archiveDatetime AND cmd_id=:cmd_id'; $oldest = DB::Prepare($sql, $values, DB::FETCH_TYPE_ROW); $mode = $cmd->getConfiguration('historizeMode', 'avg'); while ($oldest['oldest'] != null) { $values = array('cmd_id' => $sensors['cmd_id'], 'oldest' => $oldest['oldest'], 'archivePackage' => $archivePackage); $sql = 'SELECT ' . $mode . '(value) as value, FROM_UNIXTIME(AVG(UNIX_TIMESTAMP(`datetime`))) as datetime FROM history WHERE TIMEDIFF(`datetime`,:oldest)<:archivePackage AND cmd_id=:cmd_id'; $avg = DB::Prepare($sql, $values, DB::FETCH_TYPE_ROW); $history = new self(); $history->setCmd_id($sensors['cmd_id']); $history->setValue($avg['value']); $history->setDatetime($avg['datetime']); $history->setTableName('historyArch'); $history->save(); $values = array('cmd_id' => $sensors['cmd_id'], 'oldest' => $oldest['oldest'], 'archivePackage' => $archivePackage); $sql = 'DELETE FROM history WHERE TIMEDIFF(`datetime`,:oldest)<:archivePackage AND cmd_id=:cmd_id'; DB::Prepare($sql, $values, DB::FETCH_TYPE_ROW); $values = array('cmd_id' => $sensors['cmd_id'], 'archiveDatetime' => $archiveDatetime); $sql = 'SELECT MIN(`datetime`) as oldest FROM history WHERE `datetime`<:archiveDatetime AND cmd_id=:cmd_id'; $oldest = DB::Prepare($sql, $values, DB::FETCH_TYPE_ROW); } } } } }