1<?php
2
3//#
4if (!$GLOBALS['commandline']) {
5    @ob_end_flush();
6    echo '<p class="information">'.$GLOBALS['I18N']->get('Hint: this page also works from commandline').'</p>';
7    $limit = 10000;
8} else {
9    //# when on cl, doit immediately
10    $_GET['doit'] = 'yes';
11    //# on commandline handle more
12    $limit = 50000;
13}
14
15function output($message)
16{
17    if ($GLOBALS['commandline']) {
18        @ob_end_clean();
19        echo strip_tags($message)."\n";
20        ob_start();
21    } else {
22        echo $message."\n";
23        flushbuffer();
24        flush();
25    }
26    flush();
27}
28
29function flushbuffer()
30{
31    for ($i = 0; $i < 10000; ++$i) {
32        echo " \n";
33    }
34    flush();
35}
36
37include dirname(__FILE__).'/structure.php';
38set_time_limit(60000);
39if (!Sql_Table_exists($GLOBALS['tables']['linktrack_forward']) ||
40    !Sql_Table_exists($GLOBALS['tables']['linktrack_ml']) ||
41    !Sql_Table_exists($GLOBALS['tables']['linktrack_uml_click'])
42) {
43    output('creating tables');
44    Sql_Query('drop table '.$GLOBALS['tables']['linktrack_forward']);
45    Sql_Query('drop table '.$GLOBALS['tables']['linktrack_ml']);
46    Sql_Query('drop table '.$GLOBALS['tables']['linktrack_uml_click']);
47
48    Sql_Create_Table($GLOBALS['tables']['linktrack_ml'], $DBstruct['linktrack_ml']);
49    Sql_Create_Table($GLOBALS['tables']['linktrack_forward'], $DBstruct['linktrack_forward']);
50    Sql_Create_Table($GLOBALS['tables']['linktrack_uml_click'], $DBstruct['linktrack_uml_click']);
51    output(s('creating tables done'));
52}
53
54$process_id = getPageLock();
55if (empty($process_id)) {
56    return;
57}
58
59//# only convert up to a week ago.
60$lastweek = date('Y-m-d', time() - 24 * 7 * 3600);
61cl_output(sprintf('select count(*) from %s lt, %s m where lt.messageid = m.id and m.entered < "%s"',
62    $GLOBALS['tables']['linktrack'], $GLOBALS['tables']['message'], $lastweek));
63$num = Sql_Fetch_Row_Query(sprintf('select count(*) from %s lt, %s m where lt.messageid = m.id and m.entered < "%s"',
64    $GLOBALS['tables']['linktrack'], $GLOBALS['tables']['message'], $lastweek));
65output(s('%d entries still to convert', $num[0]).'<br/>');
66
67$c = 0;
68$req = Sql_Query(sprintf('select lt.* from %s lt, %s m where lt.messageid = m.id and m.entered < "%s" limit %d',
69    $GLOBALS['tables']['linktrack'], $GLOBALS['tables']['message'], $lastweek, $limit));
70$total = Sql_Affected_Rows();
71if ($total) {
72    output(s('converting data').'<br/>');
73}
74
75while ($row = Sql_Fetch_Array($req)) {
76    if (checkLock($process_id)) {
77        keepLock($process_id);
78    } else {
79        output(s('processing cancelled'));
80        break;
81    }
82
83    $exists = Sql_Fetch_Row_Query(sprintf('select id from %s where urlhash = "%s"', $GLOBALS['tables']['linktrack_forward'],
84        md5($row['url'])));
85    if (!$exists[0]) {
86        $personalise = preg_match('/uid=/', $row['forward']);
87        Sql_Query(sprintf('insert into %s (url, urlhash, personalise) values("%s","%s", %d)', $GLOBALS['tables']['linktrack_forward'],
88            $row['url'], md5($row['url']), $personalise));
89        $fwdid = Sql_Insert_id();
90    } else {
91        $fwdid = $exists[0];
92    }
93    set_time_limit(600);
94    $messageid = $row['messageid'];
95    $userid = $row['userid'];
96
97    Sql_query(sprintf(
98        'insert into %s
99    set total = 1, forwardid = %d, messageid = %d
100    ON DUPLICATE KEY UPDATE total = total + 1',
101        $GLOBALS['tables']['linktrack_ml'], $fwdid, $messageid
102    ));
103
104    if (!empty($row['firstclick'])) {
105        $result = Sql_Query(sprintf(
106            'select data, count(*) as count
107        from %s
108        where name = "Message Type" and linkid = %d
109        group by data',
110            $GLOBALS['tables']['linktrack_userclick'], $row['linkid']
111        ));
112
113        while ($ucRow = Sql_Fetch_Array($result)) {
114            $count = $ucRow['count'];
115
116            if ($ucRow['data'] == 'HTML') {
117                $updateFormatClicked = ", htmlclicked = htmlclicked + $count";
118                $setFormatClicked = ", htmlclicked = $count";
119            } elseif ($ucRow['data'] == 'Text') {
120                $updateFormatClicked = ", textclicked = textclicked + $count";
121                $setFormatClicked = ", textclicked = $count";
122            } else {
123                $updateFormatClicked = '';
124                $setFormatClicked = '';
125            }
126
127            Sql_Query(sprintf(
128                'update %s
129          set clicked = clicked + %d %s,
130          firstclick = COALESCE(LEAST(firstclick, "%s"), "%s"),
131          latestclick = COALESCE(GREATEST(latestclick, "%s"), "%s")
132          where forwardid = %d and messageid = %d',
133                $GLOBALS['tables']['linktrack_ml'], $count, $updateFormatClicked,
134                $row['firstclick'], $row['firstclick'], $row['latestclick'], $row['latestclick'], $fwdid, $messageid
135            ));
136
137            Sql_Query(sprintf(
138                'insert into %s
139          set forwardid = %d, messageid = %d, userid = %d,
140          firstclick = "%s", latestclick = "%s",
141          clicked = %d %s
142          ON DUPLICATE KEY UPDATE clicked = clicked + %d %s',
143                $GLOBALS['tables']['linktrack_uml_click'], $fwdid, $messageid, $userid,
144                $row['firstclick'], $row['latestclick'],
145                $count, $setFormatClicked, $count, $updateFormatClicked
146            ));
147        }
148    }
149
150    ++$c;
151    if ($c % 100 == 0) {
152        echo ". \n";
153        flushbuffer();
154    }
155    if ($c % 1000 == 0) {
156        output("$c/$total<br/> ");
157        flushbuffer();
158    }
159
160    flush();
161    Sql_Query(sprintf('delete from %s where linkid = %d', $GLOBALS['tables']['linktrack'], $row['linkid']));
162}
163set_time_limit(6000);
164
165output($GLOBALS['I18N']->get('Optimizing table to recover space').'.<br/>');
166Sql_Query(sprintf('optimize table %s', $GLOBALS['tables']['linktrack']));
167output($GLOBALS['I18N']->get('Finished').'.<br/>');
168
169if (!$GLOBALS['commandline']) {
170    echo PageLink2('convertstats', $GLOBALS['I18N']->get('Convert some more'));
171}
172releaseLock($process_id);
173
174return;
175