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;
}
/**
* Initializes the connection and sets it to object.
*
* @return void
*/
public function connect()
{
$this->connection = $this->_doctrineConnection->getDbh();
}
/**
* DOCUMENT ME
* @param Doctrine_Connection $connection
* @param mixed $params
*/
public function __construct(Doctrine_Connection $connection, $params = array())
{
$this->connection = $connection;
$this->sql = new aSql($connection->getDbh());
$this->initialize($params);
}
