table = Common::prefixTable(self::TABLE_NAME); } /** * @param array $annotation */ public function createAnnotation(array $annotation): int { $db = $this->getDb(); $db->insert($this->table, $annotation); return (int) $db->lastInsertId(); } /** * @return Annotation|array{} */ public function getAnnotation(int $annotationId, int $idSite): array { $db = $this->getDb(); $query = "SELECT * FROM $this->table WHERE id = ? AND idsite = ?"; $bind = [$annotationId, $idSite]; return $db->fetchRow($query, $bind) ?: []; } /** * @return list */ public function getAllAnnotationsForSiteInRange(int $idSite, ?string $startDate = null, ?string $endDate = null, ?int $limit = null): array { $db = $this->getDb(); $query = "SELECT * FROM $this->table WHERE idsite = ?"; $bind = [ $idSite, ]; if (null !== $startDate) { $query .= " AND date >= ?"; $bind[] = $startDate; } if (null !== $endDate) { $query .= " AND date <= ?"; $bind[] = $endDate; } if (null !== $limit) { $query .= " LIMIT $limit"; } return $db->fetchAll($query, $bind); } /** * @return array{int, int} */ public function getCountAnnotationsForSiteInRange(int $idSite, string $startDate, string $endDate): array { $db = $this->getDb(); $query = "SELECT SUM(1) AS cnt_total, SUM(CASE WHEN starred = 1 THEN 1 ELSE 0 END) AS cnt_starred FROM $this->table WHERE idsite = ? AND date >= ? AND date < ?"; $bind = [ $idSite, $startDate, $endDate, ]; $result = $db->fetchRow($query, $bind); return [intval($result['cnt_total'] ?? 0), intval($result['cnt_starred'] ?? 0)]; } /** * Update existing annotation with provided data * * @param int $annotationId id of the annotation being updated * @param int $idSite the site of the annotation * @param array $updatedColumns an associative array containing columns to update, * only columns matching $this->getEditableColumns() are used. * @return Annotation the updated annotation */ public function updateAnnotation(int $annotationId, int $idSite, array $updatedColumns): array { $db = $this->getDb(); $query = "UPDATE $this->table SET"; $bind = []; foreach (self::EDITABLE_COLS as $columnName) { if (isset($updatedColumns[$columnName])) { $query .= " $columnName = ?,"; $bind[] = $updatedColumns[$columnName]; } } $query = rtrim($query, ','); $query .= " WHERE id = ? AND idsite = ?"; $bind[] = $annotationId; $bind[] = $idSite; $db->query($query, $bind); return $this->getAnnotation($annotationId, $idSite); } public function deleteAnnotation(int $annotationId, int $idsite): void { $db = $this->getDb(); $query = "DELETE FROM $this->table WHERE id = ? AND idsite = ?"; $bind = [$annotationId, $idsite]; $db->query($query, $bind); } public function deleteAllAnnotationsForSite(int $idSite): void { $db = $this->getDb(); $query = "DELETE FROM $this->table WHERE idsite = ?"; $bind = [$idSite]; $db->query($query, $bind); } /** * @return Db|Db\AdapterInterface|\Zend_Db_Adapter_Abstract */ private function getDb() { return Db::get(); } }