1<?php
2require_once dirname(__FILE__).'/accesscheck.php';
3if (!defined('PHPLISTINIT')) {
4    exit;
5}
6if (!$_GET['id']) {
7    Fatal_Error(s('no such User'));
8
9    return;
10} else {
11    $id = (int)sprintf('%d', $_GET['id']);
12}
13
14$result = Sql_query("SELECT * FROM {$tables['user']} where id = $id");
15if (!Sql_Affected_Rows()) {
16    Fatal_Error(s('no such User'));
17
18    return;
19}
20$user = sql_fetch_array($result);
21
22ob_end_clean();
23header('Content-Type: text/csv; charset=utf-8');
24header('Content-Disposition: attachment; filename=subscriberdata.csv');
25// create a file pointer connected to the output stream
26ob_start();
27$output = fopen('php://output', 'w');
28
29$csvColumnDelimiter = "\t";
30if (EXPORT_EXCEL) {
31    $csvColumnDelimiter = ',';
32}
33
34// output the column headings
35fputcsv($output, array('','General Subscriber Info'), $csvColumnDelimiter);
36fputcsv($output, array('Email', 'Confirmed','Blacklisted', 'Opted in', 'Bounce count','Entered','Modified','Html email','Subscribe Page','rssfrequency','disabled','extradata'), $csvColumnDelimiter);
37
38
39$userrows = Sql_Query(
40    sprintf(
41        'select email, confirmed, blacklisted, optedin, bouncecount, entered, modified, htmlemail, subscribepage, rssfrequency, disabled, extradata
42                from %s where id = %d'
43
44
45        , $GLOBALS['tables']['user'], $user['id'])
46);
47
48
49// loop over the rows, outputting them
50while ($row = Sql_Fetch_Assoc($userrows))
51    fputcsv($output, $row, $csvColumnDelimiter);
52
53fputcsv($output, array(' '), $csvColumnDelimiter);
54// output the column headings
55fputcsv($output, array('','User History Info'), $csvColumnDelimiter);
56fputcsv($output, array('ip address', 'Summary','Date', 'Details', 'System Information'), $csvColumnDelimiter);
57$userhistoryrows = Sql_Query(
58    sprintf(
59        'select ip, summary,date, detail, systeminfo
60                from %s where userid = %d'
61
62
63        , $GLOBALS['tables']['user_history'], $user['id'])
64);
65
66
67// loop over the rows, outputting them
68while ($row = Sql_Fetch_Assoc($userhistoryrows))
69    fputcsv($output, $row, $csvColumnDelimiter);
70fputcsv($output, array(' '), $csvColumnDelimiter);
71fputcsv($output, array('','Campaign Info'), $csvColumnDelimiter);
72fputcsv($output, array('Message ID', 'Entered','Viewed', 'Response time'), $csvColumnDelimiter);
73$msgsrows = Sql_Query(sprintf('select messageid,entered,viewed,(viewed = 0 or viewed is null) as notviewed,
74    abs(unix_timestamp(entered) - unix_timestamp(viewed)) as responsetime from %s where userid = %d and status = "sent" order by entered desc',
75    $GLOBALS['tables']['usermessage'], $user['id']));
76
77
78// loop over the rows, outputting them
79while ($row = Sql_Fetch_Assoc($msgsrows))
80    fputcsv($output, $row, $csvColumnDelimiter);
81fputcsv($output, array(''), $csvColumnDelimiter);
82
83fputcsv($output, array('','Bounces Info'), $csvColumnDelimiter);
84fputcsv($output, array('Bounce ID', 'Bounce message','Time', 'Bounce','F time'), $csvColumnDelimiter);
85$bouncesrows = Sql_Query(sprintf('
86select
87    message_bounce.id
88    , message_bounce.message
89    , time
90    , bounce
91    , date_format(time,"%%e %%b %%Y %%T") as ftime
92from
93    %s as message_bounce
94where
95    user = %d', $GLOBALS['tables']['user_message_bounce'], $user['id']));
96
97while ($row = Sql_Fetch_Assoc($bouncesrows))
98    fputcsv($output, $row, $csvColumnDelimiter);
99
100fputcsv($output, array(''), $csvColumnDelimiter);
101
102fputcsv($output, array('','Blacklist Info'), $csvColumnDelimiter);
103fputcsv($output, array('Email', 'Name','Data','Added'), $csvColumnDelimiter);
104$blacklistdata = $GLOBALS['tables']['user_blacklist_data'];
105$blacklist = $GLOBALS['tables']['user_blacklist'];
106$emailaddress = sql_escape($user['email']);
107
108$blacklistinforows = Sql_Query("select d.email, d.name, d.data, b.added from $blacklistdata as d
109left join $blacklist as b on d.email = b.email
110where b.email = '$emailaddress';
111");
112
113while ($row = Sql_Fetch_Assoc($blacklistinforows))
114    fputcsv($output, $row, $csvColumnDelimiter);
115
116fputcsv($output, array(''), $csvColumnDelimiter);
117fputcsv($output, array('','Subscriber Attribute Info'), $csvColumnDelimiter);
118fputcsv($output, array('value','name', 'type','tablename'), $csvColumnDelimiter);
119
120$userattribute = $GLOBALS['tables']['user_attribute'];
121$attribute = $GLOBALS['tables']['attribute'];
122$userid = (int)$user['id'];
123
124$attributesrows = Sql_Query(
125    "select u.value, a.name, a.type, a.tablename from $userattribute as u
126left join $attribute as a on u.attributeid = a.id
127where u.userid = '$userid';
128
129");
130
131
132while ($row = Sql_Fetch_Assoc($attributesrows))
133    fputcsv($output, $row, $csvColumnDelimiter);
134
135$list = $GLOBALS['tables']['list'];
136$listuser = $GLOBALS['tables']['listuser'];
137
138fputcsv($output, array(''), $csvColumnDelimiter);
139
140
141fputcsv($output, array('','Lists Membership'), $csvColumnDelimiter);
142fputcsv($output, array('List name','description', 'entered','modified'), $csvColumnDelimiter);
143
144$listrows = Sql_Query(
145    "select l.name, l.description, u.entered, u.modified from $list as l
146left join $listuser as u on l.id = u.listid
147where u.userid = '$userid';
148
149");
150
151
152while ($row = Sql_Fetch_Assoc($listrows))
153    fputcsv($output, $row, $csvColumnDelimiter);
154
155
156fputcsv($output, array(' '), $csvColumnDelimiter);
157// output the column headings
158fputcsv($output, array('','Links tracking Info'), $csvColumnDelimiter);
159fputcsv($output, array('URL', 'Forward','First Clicked', 'Latest Clicked', 'clicked','Campaign ID'), $csvColumnDelimiter);
160$linkrows = Sql_Query(
161    sprintf(
162        'select url, forward, firstclick, latestclick, clicked, messageid
163                from %s where userid = %d'
164
165
166        , $GLOBALS['tables']['linktrack'], $user['id'])
167);
168
169while ($row = Sql_Fetch_Assoc($linkrows))
170    fputcsv($output, $row, $csvColumnDelimiter);
171
172fputcsv($output, array(' '), $csvColumnDelimiter);
173// output the column headings
174fputcsv($output, array('','UML clicks Info'), $csvColumnDelimiter);
175fputcsv($output, array('Message ID', 'Forward ID','First Click', 'Latest Click', 'clicked','Html Clicked', 'Text Clicked'), $csvColumnDelimiter);
176$umlrows = Sql_Query(
177    sprintf(
178        'select messageid, forwardid, firstclick, latestclick, clicked, htmlclicked, textclicked
179                from %s where userid = %d'
180
181
182        , $GLOBALS['tables']['linktrack_uml_click'], $user['id'])
183);
184
185while ($row = Sql_Fetch_Assoc($umlrows))
186    fputcsv($output, $row, $csvColumnDelimiter);
187
188fputcsv($output, array(' '), $csvColumnDelimiter);
189// output the column headings
190fputcsv($output, array(' ', s('Subscriber click statistics')), $csvColumnDelimiter);
191fputcsv($output, array( s('URL'), s('Link ID'), s('Campaign ID'), s('First click'), s('Last click'), s('Total clicks') ), $csvColumnDelimiter );
192
193// Query to get subscriber click data (all clicks from subscriber); from userclicks.php
194$userclickrows = Sql_Query('
195    SELECT
196        url,
197        forwardid,
198        uml_click.messageid,
199        MIN(firstclick) AS firstclick,
200        MAX(latestclick) AS latestclick,
201        SUM(clicked) AS clicked,
202        GROUP_CONCAT(
203                messageid
204            ORDER BY
205                messageid SEPARATOR \' \') AS messageid
206    FROM
207        '.$GLOBALS['tables']['linktrack_uml_click'].' AS uml_click
208    JOIN
209        '.$GLOBALS['tables']['user'].' AS user ON uml_click.userid = user.id
210    JOIN
211        '.$GLOBALS['tables']['linktrack_forward'].' AS forward ON forward.id = uml_click.forwardid
212    WHERE
213        uml_click.userid = '.sprintf('%d', $user['id']).'
214    GROUP BY
215        forwardid
216    ORDER BY
217        clicked DESC,
218        url
219');
220
221while ($row = Sql_Fetch_Assoc($userclickrows))
222    fputcsv($output, $row, $csvColumnDelimiter);
223
224fputcsv($output, array(' '), $csvColumnDelimiter);
225
226// Query to fetch forwarded messages data
227$forwardrows = Sql_Query('
228SELECT
229    message,
230    forward,
231    status,
232    time
233FROM
234    '.$GLOBALS['tables']['user_message_forward'].'
235WHERE
236    USER = '.sprintf('%d', $user['id'])
237);
238
239$totalForwards = Sql_Num_Rows( $forwardrows );
240
241// print table heading
242fputcsv($output, array(' ','Message Forward Info'), $csvColumnDelimiter);
243
244if ( $totalForwards < 1) {
245    fputcsv( $output, array(' ', s('No forwarded campaign data')), $csvColumnDelimiter );
246} else {
247
248    // print column headings
249    fputcsv($output, array( s('Message ID'), s('Forward address'), s('Status'), s('Time') ), $csvColumnDelimiter);
250
251    while ($row = Sql_Fetch_Assoc($forwardrows))
252        fputcsv($output, $row, $csvColumnDelimiter);
253}
254
255fclose($output);
256exit;
257
258
259
260
261