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