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