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