1<?php
2
3namespace dokuwiki\plugin\bez\mdl;
4
5use Assetic\Exception\Exception;
6
7class ThreadFactory extends Factory {
8
9    public function get_table_view() {
10        return 'thread_view';
11    }
12
13    public function get_years_scope() {
14        $r = $this->model->sqlite->query('SELECT create_date FROM thread ORDER BY id LIMIT 1');
15        $date = $this->model->sqlite->res2single($r);
16
17        //get only year
18		$first =  (int) substr($date, 0, strpos($date, '-'));
19        $last = (int) date('Y');
20
21		$years = array();
22		for ($year = $first; $year <= $last; $year++) {
23			$years[] = (string) $year;
24        }
25		return $years;
26    }
27
28    public function users_involvement(\DatePeriod $period=NULL) {
29        if ($period) {
30            $from = $period->getStartDate()->format(\DateTime::ISO8601);
31            $to = $period->getEndDate()->format(\DateTime::ISO8601);
32
33            $sql = "SELECT thread_participant.user_id,
34                       SUM(thread_participant.original_poster) AS original_poster_sum,
35                       SUM(thread_participant.coordinator) AS coordinator_sum,
36                       SUM(thread_participant.commentator) AS commentator_sum,
37                       SUM(thread_participant.task_assignee) AS task_assignee_sum
38                       FROM thread_participant JOIN thread ON thread_participant.thread_id = thread.id
39                       WHERE thread.create_date BETWEEN ? AND ?
40                       GROUP BY user_id
41                       ORDER BY user_id";
42            $r = $this->model->sqlite->query($sql, $from, $to);
43        } else {
44            $sql = "SELECT user_id,
45                           SUM(original_poster) AS original_poster_sum,
46                           SUM(coordinator) AS coordinator_sum,
47                           SUM(commentator) AS commentator_sum,
48                           SUM(task_assignee) AS task_assignee_sum
49                           FROM thread_participant
50                           GROUP BY user_id
51                           ORDER BY user_id";
52
53            $r = $this->model->sqlite->query($sql);
54        }
55        return $r;
56    }
57
58    public function kpi(\DatePeriod $period=NULL) {
59        if ($period) {
60            $from = $period->getStartDate()->format(\DateTime::ISO8601);
61            $to = $period->getEndDate()->format(\DateTime::ISO8601);
62
63            $sql = "SELECT COUNT(*)*1.0/COUNT(DISTINCT thread_id) AS kpi
64                       FROM thread_participant JOIN thread ON thread_participant.thread_id = thread.id
65                       WHERE thread.create_date BETWEEN ? AND ?";
66            $r = $this->model->sqlite->query($sql, $from, $to);
67        } else {
68            $sql = "SELECT COUNT(*)*1.0/COUNT(DISTINCT thread_id) AS kpi
69                      FROM thread_participant";
70
71            $r = $this->model->sqlite->query($sql);
72        }
73
74        return $r->fetchColumn();
75    }
76
77    public function bez_activity(\DatePeriod $period=NULL) {
78        if ($period) {
79            $from = $period->getStartDate()->format(\DateTime::ISO8601);
80            $to = $period->getEndDate()->format(\DateTime::ISO8601);
81
82            $sql = "SELECT COUNT(DISTINCT user_id)
83                      FROM (SELECT user_id
84                              FROM thread_participant JOIN thread ON thread_participant.thread_id = thread.id
85                              WHERE create_date BETWEEN ? AND ?
86                            UNION
87                            SELECT user_id
88                              FROM task_participant JOIN task ON task_participant.task_id = task.id
89                              WHERE create_date BETWEEN ? AND ?)";
90            $r = $this->model->sqlite->query($sql, $from, $to, $from, $to);
91        } else {
92            $sql = "SELECT COUNT(DISTINCT user_id)
93                      FROM (SELECT user_id FROM thread_participant
94                            UNION
95                            SELECT user_id FROM task_participant)";
96
97            $r = $this->model->sqlite->query($sql);
98        }
99        $active_users = $r->fetchColumn();
100        $wiki_users = count($this->model->userFactory->get_all());
101
102        return $active_users/$wiki_users * 100;
103    }
104
105
106    public function report_issue(\DatePeriod $period=NULL) {
107        if ($period) {
108            $from = $period->getStartDate()->format(\DateTime::ISO8601);
109            $to = $period->getEndDate()->format(\DateTime::ISO8601);
110
111            $sql = "SELECT label_name,
112                           COUNT(CASE WHEN state = 'proposal' THEN 1 END) AS proposal,
113                           COUNT(CASE WHEN state = 'opened' THEN 1 END) AS opened,
114                           COUNT(CASE WHEN state = 'done' THEN 1 END) AS done,
115                           COUNT(CASE WHEN state = 'closed' THEN 1 END) AS closed,
116                           COUNT(CASE WHEN state = 'rejected' THEN 1 END) AS rejected,
117                           COUNT(*) AS count_all,
118                           SUM(task_sum_cost) AS sum_all,
119                           SUM(CASE WHEN state = 'closed' THEN task_sum_cost END) AS sum_closed,
120                           (CASE WHEN state = 'closed' THEN
121                            AVG(julianday(close_date) - julianday(create_date))
122                            END) AS avg_closed
123                      FROM thread_view
124                      WHERE type = 'issue' AND create_date BETWEEN ? AND ?
125                      GROUP BY label_name
126                      ORDER BY label_name";
127
128            $r = $this->model->sqlite->query($sql, $from, $to);
129        } else {
130            $sql = "SELECT label_name,
131                           COUNT(CASE WHEN state = 'proposal' THEN 1 END) AS proposal,
132                           COUNT(CASE WHEN state = 'opened' THEN 1 END) AS opened,
133                           COUNT(CASE WHEN state = 'done' THEN 1 END) AS done,
134                           COUNT(CASE WHEN state = 'closed' THEN 1 END) AS closed,
135                           COUNT(CASE WHEN state = 'rejected' THEN 1 END) AS rejected,
136                           COUNT(*) AS count_all,
137                           SUM(task_sum_cost) AS sum_all,
138                           SUM(CASE WHEN state = 'closed' THEN task_sum_cost END) AS sum_closed,
139                           (CASE WHEN state = 'closed' THEN
140                            AVG(julianday(close_date) - julianday(create_date))
141                            END) AS avg_closed
142                      FROM thread_view
143                      WHERE type = 'issue'
144                      GROUP BY label_name
145                      ORDER BY label_name";
146
147            $r = $this->model->sqlite->query($sql);
148        }
149        return $r;
150    }
151
152    public function report_project(\DatePeriod $period=NULL) {
153        if ($period) {
154            $from = $period->getStartDate()->format(\DateTime::ISO8601);
155            $to = $period->getEndDate()->format(\DateTime::ISO8601);
156
157            $sql = "SELECT label_name,
158                           COUNT(CASE WHEN state = 'proposal' THEN 1 END) AS proposal,
159                           COUNT(CASE WHEN state = 'opened' THEN 1 END) AS opened,
160                           COUNT(CASE WHEN state = 'done' THEN 1 END) AS done,
161                           COUNT(CASE WHEN state = 'closed' THEN 1 END) AS closed,
162                           COUNT(CASE WHEN state = 'rejected' THEN 1 END) AS rejected,
163                           COUNT(*) AS count_all,
164                           SUM(task_sum_cost) AS sum_all,
165                           SUM(CASE WHEN state = 'closed' THEN task_sum_cost END) AS sum_closed,
166                           (CASE WHEN state = 'closed' THEN
167                            AVG(julianday(close_date) - julianday(create_date))
168                            END) AS avg_closed
169                      FROM thread_view
170                      WHERE type = 'project' AND create_date BETWEEN ? AND ?";
171
172            $r = $this->model->sqlite->query($sql, $from, $to);
173        } else {
174            $sql = "SELECT label_name,
175                           COUNT(CASE WHEN state = 'proposal' THEN 1 END) AS proposal,
176                           COUNT(CASE WHEN state = 'opened' THEN 1 END) AS opened,
177                           COUNT(CASE WHEN state = 'done' THEN 1 END) AS done,
178                           COUNT(CASE WHEN state = 'closed' THEN 1 END) AS closed,
179                           COUNT(CASE WHEN state = 'rejected' THEN 1 END) AS rejected,
180                           COUNT(*) AS count_all,
181                           SUM(task_sum_cost) AS sum_all,
182                           SUM(CASE WHEN state = 'closed' THEN task_sum_cost END) AS sum_closed,
183                           (CASE WHEN state = 'closed' THEN
184                            AVG(julianday(close_date) - julianday(create_date))
185                            END) AS avg_closed
186                      FROM thread_view
187                      WHERE type = 'project'";
188
189            $r = $this->model->sqlite->query($sql);
190        }
191        return $r;
192    }
193
194    public function initial_save(Entity $thread, $data) {
195        $label_ids = array();
196        if (isset($data['label_id']) && $data['label_id'] != '') {
197            $label_ids[] = $data['label_id'];
198        }
199        try {
200            $this->beginTransaction();
201
202            parent::initial_save($thread, $data);
203
204            foreach($label_ids as $label_id) {
205                $thread->add_label($label_id);
206            }
207
208            $thread->set_participant_flags($thread->original_poster, array('original_poster', 'subscribent'));
209            if($thread->coordinator != null) {
210                $thread->set_participant_flags($thread->coordinator, array('coordinator', 'subscribent'));
211            }
212
213            if ($this->model->get_level() >= BEZ_AUTH_LEADER) {
214                $private = false;
215                if (isset($data['private'])) {
216                    $private = true;
217                }
218                $thread->set_private_flag($private);
219            }
220
221            $this->commitTransaction();
222
223            if ($thread->state != 'proposal' && $this->model->user_nick != $thread->coordinator) {
224                $thread->mail_inform_coordinator();
225            } elseif ($thread->state == 'proposal') {
226                $thread->mail_inform_admins();
227            }
228
229        } catch(Exception $exception) {
230            $this->rollbackTransaction();
231        }
232    }
233
234    protected function update(Entity $obj) {
235        if ($obj->state == 'done') {
236            $prev_state = $obj->state;
237            $reflectionClass = new \ReflectionClass('dokuwiki\plugin\bez\mdl\Thread');
238            $reflectionProperty = $reflectionClass->getProperty('state');
239            $reflectionProperty->setAccessible(true);
240            $reflectionProperty->setValue($obj, 'opened');
241        }
242        try {
243            parent::update($obj);
244        } finally {
245            if (isset($prev_state)) {
246                $reflectionProperty->setValue($obj, $prev_state);
247            }
248        }
249    }
250
251    public function update_save(Entity $thread, $data) {
252        $prev_coordinator = $thread->coordinator;
253
254        $label_ids = array();
255        if (isset($data['label_id']) && $data['label_id'] != '') {
256            $label_ids[] = $data['label_id'];
257        }
258        try {
259            $this->beginTransaction();
260            parent::update_save($thread, $data);
261
262            $cur_label_ids = array_keys($thread->get_labels());
263            $labels_to_add = array_diff($label_ids, $cur_label_ids);
264            $labels_to_rem = array_diff($cur_label_ids, $label_ids);
265
266            foreach($labels_to_add as $label_id) {
267                $thread->add_label($label_id);
268            }
269
270            foreach($labels_to_rem as $label_id) {
271                $thread->remove_label($label_id);
272            }
273
274            if ($thread->coordinator != null && $thread->coordinator != $prev_coordinator) {
275                if ($prev_coordinator != null) {
276                    $thread->remove_participant_flags($prev_coordinator, array('coordinator'));
277                }
278                $thread->set_participant_flags($thread->coordinator, array('subscribent', 'coordinator'));
279            }
280
281            if ($thread->acl_of('private') >= BEZ_PERMISSION_CHANGE) {
282                $private = false;
283                if (isset($data['private'])) {
284                    $private = true;
285                }
286                $thread->set_private_flag($private);
287            }
288
289            $this->commitTransaction();
290        } catch(Exception $exception) {
291            $this->rollbackTransaction();
292        }
293
294        if ($thread->state != 'proposal' && $this->model->user_nick != $thread->coordinator) {
295            $thread->mail_inform_coordinator();
296        }
297    }
298
299    public function delete(Entity $obj) {
300        if ($obj->can_be_removed()) {
301            $this->model->sqlite->query('DELETE FROM thread_label WHERE thread_id=?', $obj->id);
302            $this->model->sqlite->query('DELETE FROM thread_participant WHERE thread_id=?', $obj->id);
303            parent::delete($obj);
304        }
305    }
306}
307