1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
<?php
namespace JsonApi\Models;
/*
* @property string category_id string primary key
* @property string seminar_id string foreign key
* @property string entry_name string database_column
* @property string pos int
*/
class ForumEntry extends \SimpleORMap
{
public static function getCatFromEntry($topicId)
{
$targetEntry = ForumEntry::find($topicId);
$parentEntries = ForumEntry::findBySQL(
'forum_entries.lft <= ? AND forum_entries.rgt >= ? AND forum_entries.seminar_id = ? AND forum_entries.depth = 1 ORDER BY forum_entries.lft ASC',
[(int) $targetEntry['lft'], (int) $targetEntry['rgt'], $targetEntry['seminar_id']]
);
$category = ForumCat::findBySQL(
'LEFT JOIN forum_categories_entries AS fce USING (category_id)
WHERE fce.topic_id = ?',
[$parentEntries[0]->id]
);
if (empty($category)) {
$category = ForumCat::findBySql(
"seminar_id = ? AND entry_name = 'Allgemein'",
[$targetEntry->seminar_id]
);
}
return $category;
}
public static function getCategories($seminarId, $asObjects = false)
{
$stmt = \DBManager::get()->prepare('SELECT * FROM forum_categories
WHERE seminar_id = ? ORDER BY pos ASC');
$stmt->execute([$seminarId]);
$ret = $stmt->fetchGrouped(\PDO::FETCH_ASSOC);
return $asObjects ? ForumCat::getCatObjects($ret) : $ret;
}
public static function getEntriesFromCat(ForumCat $targetCategory)
{
return ForumEntry::findBySQL(
'LEFT JOIN forum_categories_entries '
.'ON forum_categories_entries.topic_id = forum_entries.topic_id '
.'WHERE forum_categories_entries.category_id = ? '
.'ORDER BY forum_entries.lft DESC',
[$targetCategory->id]
);
}
public static function getChildEntries($topicId)
{
$targetEntry = ForumEntry::find($topicId);
return ForumEntry::findBySQL(
'lft > ? AND rgt < ? AND seminar_id = ? ',
[$targetEntry->lft, $targetEntry->rgt, $targetEntry->seminar_id]
);
}
public function storeWith(\SimpleORMap $parent, ForumEntry $entry)
{
if ($this->is_new) {
if ($parent instanceof \JsonApi\Models\ForumCat) {
$stmt = \DBManager::get()->prepare('INSERT INTO forum_categories_entries
(category_id, topic_id)
VALUES (?, ?)');
$stmt->execute([$parent->id, $this->id]);
$constraint = $entry;
} elseif ($parent instanceof \JsonApi\Models\ForumEntry) {
$constraint = ForumEntry::find($parent->id);
}
if (is_null($constraint)) {
throw new \InvalidArgumentException('There must be a parent');
}
\DBManager::get()->exec('UPDATE forum_entries SET lft = lft + 2
WHERE lft > '.$constraint['rgt']." AND seminar_id = '".$constraint['seminar_id']."'");
\DBManager::get()->exec('UPDATE forum_entries SET rgt = rgt + 2
WHERE rgt >= '.$constraint['rgt']." AND seminar_id = '".$constraint['seminar_id']."'");
$this->lft = $constraint['rgt'];
$this->rgt = $constraint['rgt'] + 1;
$this->depth = $constraint['depth'] + 1;
\NotificationCenter::postNotification('ForumAfterInsert', $this->topic_id, $this->toArray());
// update "latest_chdate" for easier sorting of actual threads
\DBManager::get()->exec("UPDATE forum_entries SET latest_chdate = UNIX_TIMESTAMP()
WHERE topic_id = '".$constraint['topic_id']."'");
}
return $this->store();
}
public function deleteEntry($topicId)
{
$targetEntry = ForumEntry::find($topicId);
$childEntries = ForumEntry::getChildEntries($topicId);
//delete an entry and his appended entries...
$stmt_one = \DBManager::get()->prepare('DELETE FROM forum_entries
WHERE seminar_id = ? AND lft >= ? AND rgt <= ?');
$stmt_one->execute([$targetEntry->seminar_id, $targetEntry->lft, $targetEntry->rgt]);
//...and update affected entries
$diff = $targetEntry->lft - $targetEntry->rgt;
$stmt_two = \DBManager::get()->prepare("UPDATE forum_entries SET lft = lft - ${diff}
WHERE lft > ? AND seminar_id = ?");
$stmt_two->execute([$targetEntry->rgt, $targetEntry->seminar_id]);
$stmt_three = \DBManager::get()->prepare("UPDATE forum_entries SET rgt = rgt - ${diff}
WHERE rgt > ? AND seminar_id = ?");
$stmt_three->execute([$targetEntry->rgt, $targetEntry->seminar_id]);
//... delete categories_entries-row if exists
$stmt_four = \DBManager::get()->prepare('DELETE FROM forum_categories_entries
WHERE topic_id = ?');
$stmt_four->execute([$topicId]);
return $stmt_one && $stmt_two && $stmt_three && $stmt_four;
}
protected static function configure($config = [])
{
$config['db_table'] = 'forum_entries';
$config['belongs_to']['category'] = [
'class_name' => '\JsonApi\Models\ForumEntry',
'assoc_func' => 'getCatFromEntry',
];
parent::configure($config);
}
}
|
