/**
* Logs aggregation
*
* @param int $std_agg
* @param int $avg_agg
* @param int $sup_agg
* @param bool $dry_run
*/
static function aggregate($std_agg = 10, $avg_agg = 60, $sup_agg = 1440, $dry_run = true)
{
$al = new static();
$table = $al->_spec->table;
$ds = $al->getDS();
$ds->exec("SET SESSION group_concat_max_len = 100000;");
$last_month = CMbDT::transform("- 1 MONTH", CMbDT::dateTime(), "%Y-%m-%d 00:00:00");
$last_year = CMbDT::transform("- 1 YEAR", CMbDT::dateTime(), "%Y-%m-%d 00:00:00");
// Get the oldest log to aggregate
$query = "SELECT `period`\n FROM {$table}\n WHERE `period` <= '{$last_month}'\n AND `aggregate` <= IF (`period` <= '{$last_year}', '{$avg_agg}', '{$std_agg}')\n ORDER BY `period` LIMIT 1;";
$oldest_from = $ds->loadResult($query);
if (!$oldest_from) {
CAppUI::setMsg("No log to aggregate", UI_MSG_OK);
return;
}
// Take the 1 month period to aggregate
$oldest_to = min(CMbDT::transform("+ 1 MONTH", $oldest_from, "%Y-%m-%d 00:00:00"), $last_month);
// Dry run mode, just compute the number of logs to aggregate
if ($dry_run) {
// Récupération des IDs de journaux à supprimer
$query = "SELECT count(`accesslog_id`) AS count\n FROM {$table}\n WHERE `period` BETWEEN '{$oldest_from}' AND '{$oldest_to}'\n AND `aggregate` <= IF (`period` <= '{$last_year}', '{$avg_agg}', '{$std_agg}');";
$count = $ds->loadResult($query);
$msg = "%d access logs to aggregate from %s to %s";
CAppUI::setMsg($msg, UI_MSG_OK, $count, CMbDT::date($oldest_from), CMbDT::date($oldest_to));
return;
}
// Récupération des IDs de journaux à agréger à l'heure
$query = "SELECT\n CAST(GROUP_CONCAT(`accesslog_id` SEPARATOR ',') AS CHAR) AS ids,\n `module_action_id`,\n `period`,\n `bot`\n FROM {$table}\n WHERE `period` BETWEEN '{$oldest_from}' AND '{$oldest_to}'\n AND `period` <= '{$last_month}'\n AND `period` > '{$last_year}'\n AND `aggregate` < '{$avg_agg}'\n GROUP BY `module_action_id`, date_format(`period`, '%Y-%m-%d %H:00:00'), `bot`";
$month_IDs_to_aggregate = $ds->loadList($query);
if ($month_IDs_to_aggregate) {
foreach ($month_IDs_to_aggregate as $_aggregate) {
$query = "INSERT INTO `access_log_archive` (\n `module_action_id`,\n `period`,\n `aggregate`,\n `bot`,\n `hits`,\n `duration`,\n `request`,\n `nb_requests`,\n `size`,\n `errors`,\n `warnings`,\n `notices`,\n `processus`,\n `processor`,\n `peak_memory`\n )\n SELECT\n `module_action_id`,\n date_format(`period`, '%Y-%m-%d %H:00:00'),\n '{$avg_agg}',\n `bot`,\n @hits := SUM(`hits`),\n @duration := SUM(`duration`),\n @request := SUM(`request`),\n @nb_requests := SUM(`nb_requests`),\n @size := SUM(`size`),\n @errors := SUM(`errors`),\n @warnings := SUM(`warnings`),\n @notices := SUM(`notices`),\n @processus := SUM(`processus`),\n @processor := SUM(`processor`),\n @peak_memory := SUM(`peak_memory`)\n FROM {$table}\n WHERE `accesslog_id` IN (" . $_aggregate['ids'] . ")\n GROUP BY `module_action_id`, DATE_FORMAT(`period`, '%Y-%m-%d %H:00:00'), `bot`\n ON DUPLICATE KEY UPDATE\n `hits` = `hits` + @hits,\n `duration` = `duration` + @duration,\n `request` = `request` + @request,\n `nb_requests` = `nb_requests` + @nb_requests,\n `size` = `size` + @size,\n `errors` = `errors` + @errors,\n `warnings` = `warnings` + @warnings,\n `notices` = `notices` + @notices,\n `processus` = `processus` + @processus,\n `processor` = `processor` + @processor,\n `peak_memory` = `peak_memory` + @peak_memory";
if (!$ds->exec($query)) {
CAppUI::setMsg("Failed to insert aggregated access logs", UI_MSG_ERROR);
return;
}
// Delete previous logs
$query = "DELETE\n FROM {$table}\n WHERE `accesslog_id` IN (" . $_aggregate['ids'] . ")";
$ds->exec($query);
}
}
// Récupération des IDs de journaux à agréger à la journée
$query = "SELECT\n CAST(GROUP_CONCAT(`accesslog_id` SEPARATOR ',') AS CHAR) AS ids,\n `module_action_id`,\n `period`,\n `bot`\n FROM {$table}\n WHERE `period` BETWEEN '{$oldest_from}' AND '{$oldest_to}'\n AND `period` <= '{$last_year}'\n AND `aggregate` < '{$sup_agg}'\n GROUP BY `module_action_id`, date_format(`period`, '%Y-%m-%d 00:00:00'), `bot`";
$year_IDs_to_aggregate = $ds->loadList($query);
if ($year_IDs_to_aggregate) {
foreach ($year_IDs_to_aggregate as $_aggregate) {
$query = "INSERT INTO `access_log_archive` (\n `module_action_id`,\n `period`,\n `aggregate`,\n `bot`,\n `hits`,\n `duration`,\n `request`,\n `nb_requests`,\n `size`,\n `errors`,\n `warnings`,\n `notices`,\n `processus`,\n `processor`,\n `peak_memory`\n )\n SELECT\n `module_action_id`,\n date_format(`period`, '%Y-%m-%d 00:00:00'),\n '{$sup_agg}',\n `bot`,\n @hits := SUM(`hits`),\n @duration := SUM(`duration`),\n @request := SUM(`request`),\n @nb_requests := SUM(`nb_requests`),\n @size := SUM(`size`),\n @errors := SUM(`errors`),\n @warnings := SUM(`warnings`),\n @notices := SUM(`notices`),\n @processus := SUM(`processus`),\n @processor := SUM(`processor`),\n @peak_memory := SUM(`peak_memory`)\n FROM {$table}\n WHERE `accesslog_id` IN (" . $_aggregate['ids'] . ")\n GROUP BY `module_action_id`, DATE_FORMAT(`period`, '%Y-%m-%d 00:00:00'), `bot`\n ON DUPLICATE KEY UPDATE\n `hits` = `hits` + @hits,\n `duration` = `duration` + @duration,\n `request` = `request` + @request,\n `nb_requests` = `nb_requests` + @nb_requests,\n `size` = `size` + @size,\n `errors` = `errors` + @errors,\n `warnings` = `warnings` + @warnings,\n `notices` = `notices` + @notices,\n `processus` = `processus` + @processus,\n `processor` = `processor` + @processor,\n `peak_memory` = `peak_memory` + @peak_memory";
if (!$ds->exec($query)) {
CAppUI::setMsg("Failed to insert aggregated access logs", UI_MSG_ERROR);
return;
}
// Delete previous logs
$query = "DELETE\n FROM {$table}\n WHERE `accesslog_id` IN (" . $_aggregate['ids'] . ")";
$ds->exec($query);
}
}
$IDs_to_aggregate = array_merge($year_IDs_to_aggregate, $month_IDs_to_aggregate);
$msg = "%d access logs inserted from %s to %s";
CAppUI::setMsg($msg, UI_MSG_OK, count($IDs_to_aggregate), CMbDT::date($oldest_from), CMbDT::date($oldest_to));
}
static function importByClass($class, $start = null, $count = null, $reimport = false, $chir_id = null, $order = null, $date = null, $id = null)
{
if (!is_subclass_of($class, "CExternalDBImport")) {
CAppUI::stepAjax("Classe invalide", UI_MSG_ERROR);
}
/** @var self $self */
$self = new static();
$ds = $self->getDS();
/** @var self $object */
$object = new $class();
$query = $object->getSelectQuery();
$order_by = $object->getOrderBy();
$key_name = $object->_key;
$key_multi = strpos($key_name, "|") !== false;
if ($key_multi) {
$key_multi = explode("|", $key_name);
}
if ($date || $id) {
if ($object->getSqlRestriction()) {
$query .= " AND ";
} else {
$query .= " WHERE ";
}
if ($order) {
$date_min = CMbDT::date("-6 MONTH", $date);
$date_max = $date;
} else {
$date_min = $date;
$date_max = CMbDT::date("+6 MONTH", $date);
}
if ($date_min) {
$query .= " {$order_by} BETWEEN '{$date_min}' AND '{$date_max}'";
}
if ($id) {
$query .= $key_multi ? " CONCAT(TRIM(" . implode("),'|',TRIM(", $key_multi) . ")) > '{$id}'" : " {$object->_key} > TRIM('{$id}')";
}
}
if ($order && $order_by || $id) {
if ($id) {
if ($key_multi) {
$query .= " ORDER BY CONCAT(TRIM(" . implode("),'|',TRIM(", $key_multi) . ")) ASC";
} else {
if ($self->_key_is_numeric) {
$query .= " ORDER BY {$object->_key} ASC";
} else {
$query .= " ORDER BY TRIM({$object->_key}) ASC";
}
}
} else {
$query .= " ORDER BY {$order_by} DESC";
}
}
if (!$reimport) {
$ids = array_flip($object->getDbIds($id));
}
$oracle = $ds instanceof COracleDataSource;
//echo $query;
$res = $ds->exec($query);
$last_id = null;
while ($count && ($hash = $ds->fetchAssoc($res, false))) {
if ($key_multi) {
$_values = array();
foreach ($key_multi as $_col) {
$_values[] = trim($hash[$_col]);
}
$hash[$key_name] = implode("|", $_values);
}
if (!$reimport && isset($ids[$hash[$key_name]])) {
continue;
}
if ($oracle) {
$hash = $ds->readLOB($hash);
}
/** @var self $import_object */
$import_object = new $class();
$import_object->storeMbObject($hash);
if (!$import_object->_mb_object || isset($import_object->_mb_object->_failed)) {
continue;
}
if ($count-- == 1) {
$last_id = $import_object->getId($hash);
}
}
$ds->freeResult($res);
if ($last_id) {
return $last_id;
}
return $date;
}
