1<?php 2/** 3 * DokuWiki Plugin bez (Action Component) 4 * 5 */ 6 7// must be run within Dokuwiki 8 9if(!defined('DOKU_INC')) die(); 10 11/** 12 * Class action_plugin_bez_migration 13 * 14 * Handle migrations that need more than just SQL 15 */ 16class action_plugin_bez_migration extends DokuWiki_Action_Plugin { 17 /** 18 * @inheritDoc 19 */ 20 public function register(Doku_Event_Handler $controller) { 21 $controller->register_hook('PLUGIN_SQLITE_DATABASE_UPGRADE', 'BEFORE', $this, 'handle_migrations'); 22 } 23 24 /** 25 * Call our custom migrations when defined 26 * 27 * @param Doku_Event $event 28 * @param $param 29 */ 30 public function handle_migrations(Doku_Event $event, $param) { 31 if ($event->data['sqlite']->getAdapter()->getDbname() !== 'b3p') { 32 return; 33 } 34 $to = $event->data['to']; 35 36 if(is_callable(array($this, "migration$to"))) { 37 $event->preventDefault(); 38 $event->result = call_user_func(array($this, "migration$to"), $event->data); 39 } 40 } 41 42 protected function migration3($data) { 43 global $INFO; 44 45 $file = $data['file']; 46 /** @var helper_plugin_sqlite $sqlite */ 47 $sqlite = $data['sqlite']; 48 49 $sql = file_get_contents($file); 50 if($sql === false) { 51 throw new Exception('cannot open file ' . $file); 52 } 53 54 $matches = array(); 55 preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches); 56 $queries = $matches[0]; 57 58 $db = $sqlite->getAdapter()->getDb(); 59 60 $db->beginTransaction(); 61 foreach($queries as $query) { 62 $res = $db->query($query); 63 if($res === false) { 64 $err = $db->errorInfo(); 65 msg($err[0] . ' ' . $err[1] . ' ' . $err[2] . ':<br /><pre>' . hsc($query) . '</pre>', -1); 66 $db->rollBack(); 67 return false; 68 } 69 } 70 $db->commit(); 71 72 return true; 73 } 74 75 /** 76 * Executes Migration 1 77 * 78 * Add a latest column to all existing multi tables 79 * 80 * @param helper_plugin_sqlite $sqlite 81 * @return bool 82 */ 83 protected function migration1($data) { 84 global $INFO; 85 86 $file = $data['file']; 87 /** @var helper_plugin_sqlite $sqlite */ 88 $sqlite = $data['sqlite']; 89 90 $sql = file_get_contents($file); 91 if ($sql === false) { 92 throw new Exception('cannot open file '.$file); 93 } 94 95 $matches = array(); 96 preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches); 97 $queries = $matches[0]; 98 99 $db = $sqlite->getAdapter()->getDb(); 100 101 $db->beginTransaction(); 102 foreach ($queries as $query) { 103 $res = $db->query($query); 104 if($res === false) { 105 $err = $db->errorInfo(); 106 msg($err[0].' '.$err[1].' '.$err[2].':<br /><pre>'.hsc($query).'</pre>', -1); 107 $db->rollBack(); 108 return false; 109 } 110 } 111 112 $bez_file = DOKU_INC . 'data/meta/bez.sqlite3'; 113 if (!file_exists($bez_file)) { 114 $db->commit(); 115 return true; 116 } 117 118 //import from bez 119 $bez = new \PDO('sqlite:' . $bez_file); 120 121 $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 122 123 $stmt = $bez->query('SELECT * FROM issuetypes'); 124 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 125 $sqlite->storeEntry('label', array('id' => $row['id'], 126 'name' => $row['pl'], 127 'added_by' => $INFO['client'], 128 'added_date' => date('c'))); 129 } 130 131 $stmt = $bez->query('SELECT * FROM tasktypes'); 132 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 133 $sqlite->storeEntry('task_program', array('id' => $row['id'], 134 'name' => $row['pl'], 135 'added_by' => $INFO['client'], 136 'added_date' => date('c'))); 137 } 138 139 $stmt = $bez->query('SELECT *, (SELECT COUNT(*) FROM tasks 140 WHERE tasks.cause = commcauses.id) AS task_count FROM commcauses'); 141 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 142 if ($row['type'] == '0') { 143 $type = 'comment'; 144 } elseif ($row['type'] == '1') { 145 $type = 'cause_real'; 146 } elseif ($row['type'] == '2') { 147 $type = 'cause_potential'; 148 } 149 $sqlite->storeEntry('thread_comment', 150 array('id' => $row['id'], 151 'thread_id' => $row['issue'], 152 'type' => $type, 153 'author' => $row['reporter'], 154 'create_date' => date('c', strtotime($row['datetime'])), 155 'last_modification_date' => date('c', strtotime($row['datetime'])), 156 'content' => $row['content'], 157 'content_html' => $row['content_cache'], 158 'task_count' => $row['task_count'])); 159 } 160 161 $stmt = $bez->query('SELECT tasks.*, commcauses.type AS cause_type 162 FROM tasks 163 LEFT JOIN commcauses ON tasks.cause = commcauses.id'); 164 //thread_id => array('user_id' => 'user_id') 165 $task_assignee = array(); 166 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 167 if ($row['close_date'] != '') { 168 $last_mod = date('c', (int) $row['close_date']); 169 } else { 170 $last_mod = date('c', (int) $row['date']); 171 } 172 173 $data = array('id' => $row['id'], 174 'original_poster' => $row['reporter'], 175 'assignee' => $row['executor'], 176 'create_date' => date('c', (int) $row['date']), 177 'last_activity_date' => $last_mod, 178 'last_modification_date' => $last_mod, 179 'plan_date' => $row['plan_date'], 180 'all_day_event' => $row['all_day_event'], 181 'start_time' => $row['start_time'], 182 'finish_time' => $row['finish_time'], 183 'content' => $row['task'], 184 'content_html' => $row['task_cache'], 185 'thread_id' => $row['issue'], 186 'thread_comment_id' => $row['cause'], 187 'task_program_id' => $row['tasktype'] 188 ); 189 190 if ($data['thread_id'] != '') { 191 if (!is_array($task_assignee[$data['thread_id']])) { 192 $task_assignee[$data['thread_id']] = array(); 193 } 194 $task_assignee[$data['thread_id']][$row['executor']] = $row['executor']; 195 } 196 197 if ($row['cost'] != '0') { 198 $data['cost'] = $row['cost']; 199 } 200 201 if ($row['issue'] == '') { 202 $data['type'] = 'program'; 203 } elseif ($row['cause'] == '') { 204 $data['type'] = 'correction'; 205 } elseif ($row['cause_type'] == '1') { 206 $data['type'] = 'corrective'; 207 } else { 208 $data['type'] = 'preventive'; 209 } 210 211 if ($row['state'] == '0') { 212 $data['state'] = 'opened'; 213 } elseif ($row['state'] == '1' || $row['state'] == '2') { 214 $data['state'] = 'done'; 215 $data['closed_by'] = $row['executor']; 216 $data['close_date'] = date('c', (int) $row['close_date']); 217 218 if ($row['reason'] != '') { 219 $sqlite->storeEntry('task_comment', 220 array('task_id' => $row['id'], 221 'author' => $row['executor'], 222 'create_date' => $data['close_date'], 223 'last_modification_date' => $data['close_date'], 224 'content' => $row['reason'], 225 'content_html' => $row['reason_cache'])); 226 } 227 } 228 229 //user_id => array() 230 $participants = array(); 231 $subscribents = explode(',', $row['subscribents']); 232 foreach ($subscribents as $user_id) { 233 $participants[$user_id] = array('user_id' => $user_id, 'subscribent' => '1'); 234 } 235 236 $op = $data['original_poster']; 237 if (!isset($participants[$op])) { 238 $participants[$op] = array('user_id' => $op, 'original_poster' => '1'); 239 } else { 240 $participants[$op]['original_poster'] = '1'; 241 } 242 243 $as = $data['assignee']; 244 if (!isset($participants[$as])) { 245 $participants[$as] = array('user_id' => $as, 'assignee' => '1'); 246 } else { 247 $participants[$as]['assignee'] = '1'; 248 } 249 250 foreach($participants as $part) { 251 $part['task_id'] = $row['id']; 252 $part['added_by'] = $INFO['client']; 253 $part['added_date'] = date('c'); 254 255 $res = $sqlite->storeEntry('task_participant', $part); 256 if ($res === false) { 257 throw new Exception($db->errorInfo()); 258 } 259 } 260 261 $sqlite->storeEntry('task', $data); 262 } 263 264 265 266 $stmt = $bez->query('SELECT *, 267 (SELECT COUNT(*) FROM tasks 268 WHERE tasks.issue = issues.id) AS task_count, 269 (SELECT COUNT(*) FROM tasks 270 WHERE tasks.issue = issues.id AND tasks.state != 0) AS task_closed_count, 271 (SELECT SUM(cost) FROM tasks 272 WHERE tasks.issue = issues.id) AS task_sum_cost 273 FROM issues'); 274 275 while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) { 276 277 $data = array('id' => $row['id'], 278 'original_poster' => $row['reporter'], 279 'coordinator' => $row['coordinator'], 280 'create_date' => date('c', (int) $row['date']), 281 'last_activity_date' => date('c', strtotime($row['last_activity'])), 282 'last_modification_date' => date('c', (int) $row['last_mod']), 283 'title' => $row['title'], 284 'content' => $row['description'], 285 'content_html' => $row['description_cache'], 286 'task_count' => $row['task_count'], 287 'task_count_closed' => $row['task_closed_count'] 288 ); 289 290 if ($row['task_sum_cost'] != '0') { 291 $data['task_sum_cost'] = $row['task_sum_cost']; 292 } 293 294 if ($row['coordinator'] == '') { 295 $data['state'] = 'proposal'; 296 } elseif ($row['state'] == '1') { 297 $data['closed_by'] = $row['coordinator']; 298 $data['state'] = 'closed'; 299 $data['close_date'] = $data['last_modification_date']; 300 301 $sqlite->storeEntry('thread_comment', 302 array('thread_id' => $row['id'], 303 'type' => 'comment', 304 'author' => $row['coordinator'], 305 'create_date' => $data['close_date'], 306 'last_modification_date' => $data['close_date'], 307 'content' => $row['opinion'], 308 'content_html' => $row['opinion_cache'])); 309 310 311 } elseif ($row['state'] == '2') { 312 $data['closed_by'] = $row['coordinator']; 313 $data['state'] = 'rejected'; 314 $data['close_date'] = $data['last_modification_date']; 315 316 if ($row['opinion'] != '') { 317 $res = $sqlite->storeEntry('thread_comment', 318 array('thread_id' => $row['id'], 319 'type' => 'comment', 320 'author' => $row['coordinator'], 321 'create_date' => $data['close_date'], 322 'last_modification_date' => $data['close_date'], 323 'content' => $row['opinion'], 324 'content_html' => $row['opinion_cache'])); 325 if ($res === false) { 326 throw new Exception($db->errorInfo()); 327 } 328 } 329 } else { 330 $data['state'] = 'opened'; 331 } 332 333 $sqlite->storeEntry('thread', $data); 334 335 $sqlite->storeEntry('thread_label', 336 array('thread_id' => $row['id'], 337 'label_id' => $row['type'])); 338 339 //participants 340 //user_id => array() 341 $participants = array(); 342 $org_participants = array_filter(explode(',', $row['participants'])); 343 foreach ($org_participants as $user_id) { 344 $participants[$user_id] = array('user_id' => $user_id); 345 } 346 347 $subscribents = array_filter(explode(',', $row['subscribents'])); 348 foreach ($subscribents as $user_id) { 349 if (!isset($participants[$user_id])) { 350 $participants[$user_id] = array('user_id' => $user_id); 351 } 352 $participants[$user_id]['subscribent'] = '1'; 353 } 354 355 $stmt_i = $bez->query('SELECT reporter FROM commcauses WHERE issue=' . $row['id']); 356 while ($commcause = $stmt_i->fetch(\PDO::FETCH_ASSOC)) { 357 $user_id = $commcause['reporter']; 358 if (!isset($participants[$user_id])) { 359 $participants[$user_id] = array('user_id' => $user_id); 360 } 361 $participants[$user_id]['commentator'] = '1'; 362 } 363 364 if (is_array($task_assignee[$row['id']])) foreach ($task_assignee[$row['id']] as $user_id) { 365 if (!isset($participants[$user_id])) { 366 $participants[$user_id] = array('user_id' => $user_id); 367 } 368 $participants[$user_id]['task_assignee'] = '1'; 369 } 370 371 $op = $data['original_poster']; 372 if (!isset($participants[$op])) { 373 $participants[$op] = array('user_id' => $op, 'original_poster' => '1'); 374 } else { 375 $participants[$op]['original_poster'] = '1'; 376 } 377 378 $cor = $data['coordinator']; 379 if (!isset($participants[$cor])) { 380 $participants[$cor] = array('user_id' => $cor, 'coordinator' => '1'); 381 } else { 382 $participants[$cor]['coordinator'] = '1'; 383 } 384 385 foreach($participants as $part) { 386 $part['thread_id'] = $row['id']; 387 $part['added_by'] = $INFO['client']; 388 $part['added_date'] = date('c'); 389 390 $sqlite->storeEntry('thread_participant', $part); 391 } 392 393 } 394 395 $db->commit(); 396 397 return true; 398 } 399 400} 401