private function replaceTokens($query)
{
$tokens = $this->getQueryTokens($query);
foreach ($tokens as $token) {
switch (strtolower($token)) {
case 'prefix':
$query = str_replace('${prefix}', $this->connection->getPrefix(), $query);
AppKitLogger::verbose("Replaced prefix %s", $query);
break;
case 'username':
$query = str_replace('${username}', $this->user->user_name, $query);
break;
case 'retained_flag':
$query = str_replace('${retained_flag}', $this->useRetained, $query);
break;
case 'active_flag':
$query = str_replace('${active_flag}', 1, $query);
break;
default:
$query = $this->resolveReferenceToken($token, $query);
}
}
return $query;
}
private static function getMySQLSummaryQuery(Doctrine_Connection $c, $filter = null)
{
$dbh = $c->getDbh();
$prefix = $c->getPrefix();
$timeQuery = self::getTimeRangeQuery($c, $filter);
// MYSQL has no with clause, so we need two queries
$mainQuery = "(SELECT\n s.object_id,\n state*(scheduled_downtime-1)*-1 as sla_state,\n obj.objecttype_id,\n SUM(\n COALESCE(\n UNIX_TIMESTAMP(acknowledgement_time)-UNIX_TIMESTAMP(start_time),\n UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time),\n UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(start_time)\n )\n ) as duration\n FROM ({$timeQuery}) s\n INNER JOIN " . $prefix . "objects obj ON obj.object_id = s.object_id ";
if ($filter) {
$filterParts = self::buildWherePart($c, $filter);
$mainQuery .= $filterParts["wherePart"];
}
$mainQuery .= "\n GROUP BY\n state*(scheduled_downtime-1)*-1,\n object_id,\n objecttype_id\n ) slahistory_main";
$stmt = $dbh->prepare($query = "\n\n SELECT\n slahistory_main.sla_state AS SLA_STATE,\n slahistory_main.object_id AS OBJECT_ID,\n slahistory_main.objecttype_id AS OBJECTTYPE_ID,\n SUM(slahistory_main.duration/s.complete*100) AS PERCENTAGE\n FROM\n {$mainQuery}\n INNER JOIN (\n SELECT t1.object_id,\n SUM(\n COALESCE(\n UNIX_TIMESTAMP(acknowledgement_time)-UNIX_TIMESTAMP(start_time),\n UNIX_TIMESTAMP(end_time)-UNIX_TIMESTAMP(start_time),\n UNIX_TIMESTAMP(now())-UNIX_TIMESTAMP(start_time)\n )\n ) as complete FROM ({$timeQuery}) t1\n GROUP BY\n object_id\n ) as s ON slahistory_main.object_id = s.object_id\n GROUP BY\n slahistory_main.object_id,\n slahistory_main.sla_state,\n slahistory_main.objecttype_id;\n ");
foreach ($filterParts["params"] as $param => $value) {
$stmt->bindValue($param, $value);
}
return $stmt;
}
