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 migration12($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()->getPdo();
59
60//        $db->beginTransaction();   // translation already started
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();  // commit will be done inside SQLiteDB
71
72        return true;
73    }
74
75    protected function migration3($data) {
76        global $INFO;
77
78        $file = $data['file'];
79        /** @var helper_plugin_sqlite $sqlite */
80        $sqlite = $data['sqlite'];
81
82        $sql = file_get_contents($file);
83        if($sql === false) {
84            throw new Exception('cannot open file ' . $file);
85        }
86
87        $matches = array();
88        preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches);
89        $queries = $matches[0];
90
91        $db = $sqlite->getAdapter()->getPdo();
92
93//        $db->beginTransaction();  // translation already started
94        foreach($queries as $query) {
95            $res = $db->query($query);
96            if($res === false) {
97                $err = $db->errorInfo();
98                msg($err[0] . ' ' . $err[1] . ' ' . $err[2] . ':<br /><pre>' . hsc($query) . '</pre>', -1);
99                $db->rollBack();
100                return false;
101            }
102        }
103//        $db->commit();  // commit will be done inside SQLiteDB
104
105        return true;
106    }
107
108    /**
109     * Executes Migration 1
110     *
111     * Add a latest column to all existing multi tables
112     *
113     * @param helper_plugin_sqlite $sqlite
114     * @return bool
115     */
116    protected function migration1($data) {
117        global $INFO;
118
119        $file = $data['file'];
120        /** @var helper_plugin_sqlite $sqlite */
121        $sqlite = $data['sqlite'];
122
123        $sql = file_get_contents($file);
124        if ($sql === false) {
125            throw new Exception('cannot open file '.$file);
126        }
127
128        $matches = array();
129        preg_match_all('/.*?(?(?=BEGIN)BEGIN.*?END)\s*;/is', $sql, $matches);
130        $queries = $matches[0];
131
132        $db = $sqlite->getAdapter()->getPdo();
133
134//        $db->beginTransaction(); // translation already started
135        foreach ($queries as $query) {
136            $res = $db->query($query);
137            if($res === false) {
138                $err = $db->errorInfo();
139                msg($err[0].' '.$err[1].' '.$err[2].':<br /><pre>'.hsc($query).'</pre>', -1);
140                $db->rollBack();
141                return false;
142            }
143        }
144
145        $bez_file = DOKU_INC . 'data/meta/bez.sqlite3';
146        if (!file_exists($bez_file)) {
147//        $db->commit();  // commit will be done inside SQLiteDB
148            return true;
149        }
150
151        //import from bez
152        $bez = new \PDO('sqlite:' . $bez_file);
153
154        $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
155
156        $stmt = $bez->query('SELECT * FROM issuetypes');
157        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
158            $sqlite->storeEntry('label', array('id' => $row['id'],
159                                                            'name' => $row['pl'],
160                                                            'added_by' => $INFO['client'],
161                                                            'added_date' => date('c')));
162        }
163
164        $stmt = $bez->query('SELECT * FROM tasktypes');
165        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
166            $sqlite->storeEntry('task_program', array('id' => $row['id'],
167                                                             'name' => $row['pl'],
168                                                             'added_by' => $INFO['client'],
169                                                             'added_date' => date('c')));
170        }
171
172        $stmt = $bez->query('SELECT *, (SELECT COUNT(*) FROM tasks
173								WHERE tasks.cause = commcauses.id) AS task_count FROM commcauses');
174        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
175            if ($row['type'] == '0') {
176                $type = 'comment';
177            } elseif ($row['type'] == '1') {
178                $type = 'cause_real';
179            } elseif ($row['type'] == '2') {
180                $type = 'cause_potential';
181            }
182            $sqlite->storeEntry('thread_comment',
183                                       array('id' => $row['id'],
184                                             'thread_id' => $row['issue'],
185                                             'type' => $type,
186                                             'author' => $row['reporter'],
187                                             'create_date' => date('c', strtotime($row['datetime'])),
188                                             'last_modification_date' => date('c', strtotime($row['datetime'])),
189                                             'content' => $row['content'],
190                                             'content_html' => $row['content_cache'],
191                                             'task_count' => $row['task_count']));
192        }
193
194        $stmt = $bez->query('SELECT tasks.*, commcauses.type AS cause_type
195                                          FROM tasks
196                                          LEFT JOIN commcauses ON tasks.cause = commcauses.id');
197        //thread_id => array('user_id' => 'user_id')
198        $task_assignee = array();
199        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
200            if ($row['close_date'] != '') {
201                $last_mod = date('c', (int) $row['close_date']);
202            } else {
203                $last_mod = date('c', (int) $row['date']);
204            }
205
206            $data =  array('id' => $row['id'],
207                           'original_poster' => $row['reporter'],
208                           'assignee' => $row['executor'],
209                           'create_date' => date('c', (int) $row['date']),
210                           'last_activity_date' => $last_mod,
211                           'last_modification_date' => $last_mod,
212                           'plan_date' => $row['plan_date'],
213                           'all_day_event' => $row['all_day_event'],
214                           'start_time' => $row['start_time'],
215                           'finish_time' => $row['finish_time'],
216                           'content' => $row['task'],
217                           'content_html' => $row['task_cache'],
218                           'thread_id' => $row['issue'],
219                           'thread_comment_id' => $row['cause'],
220                           'task_program_id' => $row['tasktype']
221            );
222
223            if ($data['thread_id'] != '') {
224                if (!is_array($task_assignee[$data['thread_id']])) {
225                    $task_assignee[$data['thread_id']] = array();
226                }
227                $task_assignee[$data['thread_id']][$row['executor']] = $row['executor'];
228            }
229
230            if ($row['cost'] != '0') {
231                $data['cost'] = $row['cost'];
232            }
233
234            if ($row['issue'] == '') {
235                $data['type'] = 'program';
236            } elseif ($row['cause'] == '') {
237                $data['type'] = 'correction';
238            } elseif ($row['cause_type'] == '1') {
239                $data['type'] = 'corrective';
240            } else {
241                $data['type'] = 'preventive';
242            }
243
244            if ($row['state'] == '0') {
245                $data['state'] = 'opened';
246            } elseif ($row['state'] == '1' || $row['state'] == '2') {
247                $data['state'] = 'done';
248                $data['closed_by'] = $row['executor'];
249                $data['close_date'] = date('c', (int) $row['close_date']);
250
251                if ($row['reason'] != '') {
252                    $sqlite->storeEntry('task_comment',
253                                               array('task_id' => $row['id'],
254                                                     'author' => $row['executor'],
255                                                     'create_date' => $data['close_date'],
256                                                     'last_modification_date' => $data['close_date'],
257                                                     'content' => $row['reason'],
258                                                     'content_html' => $row['reason_cache']));
259                }
260            }
261
262            //user_id => array()
263            $participants = array();
264            $subscribents = explode(',', $row['subscribents']);
265            foreach ($subscribents as $user_id) {
266                $participants[$user_id] = array('user_id' => $user_id, 'subscribent' => '1');
267            }
268
269            $op = $data['original_poster'];
270            if (!isset($participants[$op])) {
271                $participants[$op] = array('user_id' => $op, 'original_poster' => '1');
272            } else {
273                $participants[$op]['original_poster'] = '1';
274            }
275
276            $as = $data['assignee'];
277            if (!isset($participants[$as])) {
278                $participants[$as] = array('user_id' => $as, 'assignee' => '1');
279            } else {
280                $participants[$as]['assignee'] = '1';
281            }
282
283            foreach($participants as $part) {
284                $part['task_id'] = $row['id'];
285                $part['added_by'] = $INFO['client'];
286                $part['added_date'] = date('c');
287
288                $res = $sqlite->storeEntry('task_participant', $part);
289                if ($res === false) {
290                    throw new Exception($db->errorInfo());
291                }
292            }
293
294            $sqlite->storeEntry('task', $data);
295        }
296
297
298
299        $stmt = $bez->query('SELECT *,
300                                (SELECT COUNT(*) FROM tasks
301								WHERE tasks.issue = issues.id) AS task_count,
302								(SELECT COUNT(*) FROM tasks
303								WHERE tasks.issue = issues.id AND tasks.state != 0) AS task_closed_count,
304								(SELECT SUM(cost) FROM tasks
305								WHERE tasks.issue = issues.id) AS task_sum_cost
306								FROM issues');
307
308        while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)) {
309
310            $data =  array('id' => $row['id'],
311                           'original_poster' => $row['reporter'],
312                           'coordinator' => $row['coordinator'],
313                           'create_date' => date('c', (int) $row['date']),
314                           'last_activity_date' => date('c', strtotime($row['last_activity'])),
315                           'last_modification_date' => date('c', (int) $row['last_mod']),
316                           'title' => $row['title'],
317                           'content' => $row['description'],
318                           'content_html' => $row['description_cache'],
319                           'task_count' => $row['task_count'],
320                           'task_count_closed' => $row['task_closed_count']
321            );
322
323            if ($row['task_sum_cost'] != '0') {
324                $data['task_sum_cost'] = $row['task_sum_cost'];
325            }
326
327            if ($row['coordinator'] == '') {
328                $data['state'] = 'proposal';
329            } elseif ($row['state'] == '1') {
330                $data['closed_by'] = $row['coordinator'];
331                $data['state'] = 'closed';
332                $data['close_date'] = $data['last_modification_date'];
333
334                $sqlite->storeEntry('thread_comment',
335                                           array('thread_id' => $row['id'],
336                                                 'type' => 'comment',
337                                                 'author' => $row['coordinator'],
338                                                 'create_date' => $data['close_date'],
339                                                 'last_modification_date' => $data['close_date'],
340                                                 'content' => $row['opinion'],
341                                                 'content_html' => $row['opinion_cache']));
342
343
344            } elseif ($row['state'] == '2') {
345                $data['closed_by'] = $row['coordinator'];
346                $data['state'] = 'rejected';
347                $data['close_date'] = $data['last_modification_date'];
348
349                if ($row['opinion'] != '') {
350                    $res = $sqlite->storeEntry('thread_comment',
351                                               array('thread_id' => $row['id'],
352                                                     'type' => 'comment',
353                                                     'author' => $row['coordinator'],
354                                                     'create_date' => $data['close_date'],
355                                                     'last_modification_date' => $data['close_date'],
356                                                     'content' => $row['opinion'],
357                                                     'content_html' => $row['opinion_cache']));
358                    if ($res === false) {
359                        throw new Exception($db->errorInfo());
360                    }
361                }
362            } else {
363                $data['state'] = 'opened';
364            }
365
366            $sqlite->storeEntry('thread', $data);
367
368            $sqlite->storeEntry('thread_label',
369                                       array('thread_id' => $row['id'],
370                                             'label_id' => $row['type']));
371
372            //participants
373            //user_id => array()
374            $participants = array();
375            $org_participants = array_filter(explode(',', $row['participants']));
376            foreach ($org_participants as $user_id) {
377                $participants[$user_id] = array('user_id' => $user_id);
378            }
379
380            $subscribents = array_filter(explode(',', $row['subscribents']));
381            foreach ($subscribents as $user_id) {
382                if (!isset($participants[$user_id])) {
383                    $participants[$user_id] = array('user_id' => $user_id);
384                }
385                $participants[$user_id]['subscribent'] = '1';
386            }
387
388            $stmt_i = $bez->query('SELECT reporter FROM commcauses WHERE issue=' . $row['id']);
389            while ($commcause = $stmt_i->fetch(\PDO::FETCH_ASSOC)) {
390                $user_id = $commcause['reporter'];
391                if (!isset($participants[$user_id])) {
392                    $participants[$user_id] = array('user_id' => $user_id);
393                }
394                $participants[$user_id]['commentator'] = '1';
395            }
396
397            if (is_array($task_assignee[$row['id']])) foreach ($task_assignee[$row['id']] as $user_id) {
398                if (!isset($participants[$user_id])) {
399                    $participants[$user_id] = array('user_id' => $user_id);
400                }
401                $participants[$user_id]['task_assignee'] = '1';
402            }
403
404            $op = $data['original_poster'];
405            if (!isset($participants[$op])) {
406                $participants[$op] = array('user_id' => $op, 'original_poster' => '1');
407            } else {
408                $participants[$op]['original_poster'] = '1';
409            }
410
411            $cor = $data['coordinator'];
412            if (!isset($participants[$cor])) {
413                $participants[$cor] = array('user_id' => $cor, 'coordinator' => '1');
414            } else {
415                $participants[$cor]['coordinator'] = '1';
416            }
417
418            foreach($participants as $part) {
419                $part['thread_id'] = $row['id'];
420                $part['added_by'] = $INFO['client'];
421                $part['added_date'] = date('c');
422
423                $sqlite->storeEntry('thread_participant', $part);
424            }
425
426        }
427
428//        $db->commit();  // commit will be done inside SQLiteDB
429
430        return true;
431    }
432
433    protected function migration8($data) {
434        /** @var helper_plugin_sqlite $sqlite */
435        $sqlite = $data['sqlite'];
436
437        $db = $sqlite->getAdapter()->getPdo();
438        $db->commit(); // stop current transatction
439        $db->query('PRAGMA journal_mode=WAL');
440        $db->beginTransaction(); // recreate transaction
441        return true;
442    }
443
444}
445