ILIAS  trunk Revision v11.0_alpha-3011-gc6b235a2e85
class.NoteDBRepository.php
Go to the documentation of this file.
1<?php
2
19declare(strict_types=1);
20
21namespace ILIAS\Notes;
22
27{
28 protected \ilDBInterface $db;
30
31 public function __construct(
34 ) {
35 $this->db = $db;
36 $this->data = $data;
37 }
38
39 public function createNote(
40 Note $note
41 ): Note {
42 $db = $this->db;
43
44 $id = $db->nextId("note");
45 $context = $note->getContext();
46 $db->insert("note", array(
47 "id" => array("integer", $id),
48 "rep_obj_id" => array("integer", $context->getObjId()),
49 "obj_id" => array("integer", $context->getSubObjId()),
50 "obj_type" => array("text", $context->getType()),
51 "news_id" => array("integer", $context->getNewsId()),
52 "type" => array("integer", $note->getType()),
53 "author" => array("integer", $note->getAuthor()),
54 "note_text" => array("clob", $note->getText()),
55 "creation_date" => array("timestamp", $note->getCreationDate()),
56 "no_repository" => array("integer", (int) !$context->getInRepository()),
57 "recipient" => array("integer", $note->getRecipient()),
58 ));
59 return $this->getById($id);
60 }
61
62 public function deleteNote(int $id): void
63 {
64 $db = $this->db;
65 $q = "DELETE FROM note WHERE id = " .
66 $db->quote($id, "integer");
67 $db->manipulate($q);
68 }
69
70 public function updateNoteText(
71 int $id,
72 string $text
73 ): void {
74 $db = $this->db;
75
76 $update_date = \ilUtil::now();
77 $db->update("note", array(
78 "note_text" => array("clob", $text),
79 "update_date" => array("timestamp", $update_date),
80 ), array(
81 "id" => array("integer", $id)
82 ));
83 }
84
89 public function getById(
90 int $id
91 ): Note {
92 $db = $this->db;
93
94 $set = $db->queryF(
95 "SELECT * FROM note " .
96 " WHERE id = %s ",
97 ["integer"],
98 [$id]
99 );
100 if ($rec = $db->fetchAssoc($set)) {
101 return $this->getNoteFromRecord($rec);
102 }
103 throw new NoteNotFoundException("Note with ID $id not found.");
104 }
105
106 protected function getNoteFromRecord(array $rec): Note
107 {
108 return $this->data->note(
109 (int) $rec["id"],
110 $this->data->context(
111 (int) $rec["rep_obj_id"],
112 (int) $rec["obj_id"],
113 $rec["obj_type"],
114 (int) $rec["news_id"],
115 !$rec["no_repository"]
116 ),
117 (string) $rec["note_text"],
118 (int) $rec["author"],
119 (int) $rec["type"],
120 $rec["creation_date"],
121 $rec["update_date"],
122 (int) $rec["recipient"]
123 );
124 }
125
130 protected function getQuery(
132 int $type = Note::PRIVATE,
133 bool $incl_sub = false,
134 int $author = 0,
135 bool $ascending = false,
136 bool $count = false,
137 string $since = "",
138 array $obj_ids = [],
139 string $search_text = ""
140 ): string {
141 $db = $this->db;
142
143 $author_where = ($author > 0)
144 ? " AND author = " . $db->quote($author, "integer")
145 : "";
146
147 $sub_where = ($context)
148 ? " rep_obj_id = " . $db->quote($context->getObjId(), "integer")
149 : " " . $db->in("rep_obj_id", $obj_ids, false, "integer");
150
151 $sub_where .= ($context && !$incl_sub)
152 ? " AND note.obj_id = " . $db->quote($context->getSubObjId(), "integer") .
153 " AND note.obj_type = " . $db->quote($context->getType(), "text")
154 : "";
155
156 if ($since !== "") {
157 $sub_where .= " AND creation_date > " . $db->quote($since, "timestamp");
158 }
159
160 $news_where = "";
161 if ($context) {
162 $news_where =
163 " AND news_id = " . $db->quote($context->getNewsId(), "integer");
164
165 $sub_where .= " AND no_repository = " . $db->quote(!$context->getInRepository(), "integer");
166 }
167
168 // search text
169 $join = "";
170 if ($search_text !== "") {
171 $sub_where .= " AND (" . $db->like("note_text", "text", "%" . $search_text . "%");
172 $join = " JOIN usr_data ud ON (author = ud.usr_id)";
173 $join .= " LEFT JOIN object_data od ON (rep_obj_id = od.obj_id)";
174 $sub_where .= " OR " . $db->like("ud.lastname", "text", "%" . $search_text . "%");
175 $sub_where .= " OR " . $db->like("ud.firstname", "text", "%" . $search_text . "%");
176 $sub_where .= " OR " . $db->like("ud.login", "text", "%" . $search_text . "%");
177 $sub_where .= " OR " . $db->like("od.title", "text", "%" . $search_text . "%");
178 $sub_where .= ")";
179 }
180
181 $fields = $count ? "count(*) cnt" : "note.*";
182 $query = "SELECT $fields FROM note $join WHERE " .
183 $sub_where .
184 " AND note.type = " . $db->quote($type, "integer") .
185 $author_where .
186 $news_where .
187 " ORDER BY creation_date ";
188 $query .= ($ascending) ? "ASC" : "DESC";
189 return $query;
190 }
191
196 public function getNotesForContext(
198 int $type = Note::PRIVATE,
199 bool $incl_sub = false,
200 int $author = 0,
201 bool $ascending = false,
202 string $since = "",
203 string $search_text = ""
204 ): array {
205 $db = $this->db;
206
207 $query = $this->getQuery(
208 $context,
209 $type,
210 $incl_sub,
211 $author,
212 $ascending,
213 false,
214 $since,
215 [],
216 $search_text
217 );
218
219 $set = $db->query($query);
220 $notes = [];
221 while ($note_rec = $db->fetchAssoc($set)) {
222 $notes[] = $this->getNoteFromRecord($note_rec);
223 }
224 return $notes;
225 }
226
231 public function getNotesForObjIds(
232 array $obj_ids,
233 int $type = Note::PRIVATE,
234 bool $incl_sub = false,
235 int $author = 0,
236 bool $ascending = false,
237 string $since = "",
238 string $search_text = ""
239 ): array {
240 $db = $this->db;
241
242 $query = $this->getQuery(
243 null,
244 $type,
245 $incl_sub,
246 $author,
247 $ascending,
248 false,
249 $since,
250 $obj_ids,
251 $search_text
252 );
253
254 $set = $db->query($query);
255 $notes = [];
256 while ($note_rec = $db->fetchAssoc($set)) {
257 $notes[] = $this->getNoteFromRecord($note_rec);
258 }
259 return $notes;
260 }
261
262 public function getNrOfNotesForContext(
264 int $type = Note::PRIVATE,
265 bool $incl_sub = false,
266 int $author = 0
267 ): int {
268 $db = $this->db;
269
270 $query = $this->getQuery(
271 $context,
272 $type,
273 $incl_sub,
274 $author,
275 false,
276 true
277 );
278
279 $set = $db->query($query);
280 $rec = $db->fetchAssoc($set);
281 return (int) $rec["cnt"];
282 }
283
287 public function getRelatedObjIdsOfUser(
288 int $user_id,
289 int $type
290 ): array {
291 $db = $this->db;
292
293 $q = "SELECT DISTINCT rep_obj_id FROM note WHERE " .
294 " type = " . $db->quote($type, "integer") .
295 " AND author = " . $db->quote($user_id, "integer") .
296 " AND (no_repository IS NULL OR no_repository < " . $db->quote(1, "integer") . ")";
297
298 $set = $db->query($q);
299 $ids = [];
300 while ($rec = $db->fetchAssoc($set)) {
301 $ids[] = (int) $rec["rep_obj_id"];
302 }
303 return $ids;
304 }
305
310 public function filterObjectsWithNotes(array $obj_ids, int $type): array
311 {
312 $db = $this->db;
313
314 $q = "SELECT DISTINCT rep_obj_id FROM note WHERE " .
315 $db->in("rep_obj_id", $obj_ids, false, "integer") .
316 " AND type = " . $db->quote($type, "integer") .
317 " AND (no_repository IS NULL OR no_repository < " . $db->quote(1, "integer") . ")";
318
319 $set = $db->query($q);
320 $ids = [];
321 while ($rec = $db->fetchAssoc($set)) {
322 $ids[] = (int) $rec["rep_obj_id"];
323 }
324 return $ids;
325 }
326
330 public function getUserCount(
331 int $obj_id,
332 int $sub_obj_id,
333 string $obj_type
334 ): int {
335 $db = $this->db;
336
337 $set = $db->queryF(
338 "SELECT count(DISTINCT author) cnt FROM note WHERE " .
339 "rep_obj_id = %s AND obj_id = %s AND obj_type = %s",
340 array("integer", "integer", "text"),
341 array($obj_id, $sub_obj_id, $obj_type)
342 );
343 $rec = $db->fetchAssoc($set);
344 return (int) $rec["cnt"];
345 }
346
353 array $obj_ids,
354 int $user_id,
355 bool $no_sub_objs = false
356 ): array {
357 $db = $this->db;
358
359 $q = "SELECT count(id) c, rep_obj_id, type FROM note WHERE " .
360 " ((type = " . $db->quote(Note::PRIVATE, "integer") . " AND " .
361 "author = " . $db->quote($user_id, "integer") . ") OR " .
362 " type = " . $db->quote(Note::PUBLIC, "integer") . ") AND " .
363 $db->in("rep_obj_id", $obj_ids, false, "integer");
364
365 if ($no_sub_objs) {
366 $q .= " AND obj_id = " . $db->quote(0, "integer");
367 }
368
369 $q .= " GROUP BY rep_obj_id, type ";
370
371 $cnt = array();
372 $set = $db->query($q);
373 while ($rec = $db->fetchAssoc($set)) {
374 $cnt[$rec["rep_obj_id"]][$rec["type"]] = $rec["c"];
375 }
376 return $cnt;
377 }
378}
$id
plugin.php for ilComponentBuildPluginInfoObjectiveTest::testAddPlugins
Definition: plugin.php:23
getNrOfNotesForContext(Context $context, int $type=Note::PRIVATE, bool $incl_sub=false, int $author=0)
countNotesAndCommentsMultipleObjects(array $obj_ids, int $user_id, bool $no_sub_objs=false)
Get all notes related to multiple repository objects.
filterObjectsWithNotes(array $obj_ids, int $type)
__construct(InternalDataService $data, \ilDBInterface $db)
getRelatedObjIdsOfUser(int $user_id, int $type)
getNotesForObjIds(array $obj_ids, int $type=Note::PRIVATE, bool $incl_sub=false, int $author=0, bool $ascending=false, string $since="", string $search_text="")
Get all notes related to a specific object.
updateNoteText(int $id, string $text)
getUserCount(int $obj_id, int $sub_obj_id, string $obj_type)
How many users have attached a note/comment to a given object?
getNotesForContext(Context $context, int $type=Note::PRIVATE, bool $incl_sub=false, int $author=0, bool $ascending=false, string $since="", string $search_text="")
Get all notes related to a specific object.
getQuery(?Context $context, int $type=Note::PRIVATE, bool $incl_sub=false, int $author=0, bool $ascending=false, bool $count=false, string $since="", array $obj_ids=[], string $search_text="")
Get query.
static now()
Return current timestamp in Y-m-d H:i:s format.
Interface ilDBInterface.
insert(string $table_name, array $values)
nextId(string $table_name)
This file is part of ILIAS, a powerful learning management system published by ILIAS open source e-Le...
$q
Definition: shib_logout.php:23
$context
Definition: webdav.php:31