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