1<?php
2
3@include 'gchartphp/gChart.php';
4
5if (class_exists('utility')) {
6    $util = new utility();
7}
8if (!function_exists('monthName')) {
9    function monthName($month, $short = 0)
10    {
11        $months = array(
12            '',
13            $GLOBALS['I18N']->get('January'),
14            $GLOBALS['I18N']->get('February'),
15            $GLOBALS['I18N']->get('March'),
16            $GLOBALS['I18N']->get('April'),
17            $GLOBALS['I18N']->get('May'),
18            $GLOBALS['I18N']->get('June'),
19            $GLOBALS['I18N']->get('July'),
20            $GLOBALS['I18N']->get('August'),
21            $GLOBALS['I18N']->get('September'),
22            $GLOBALS['I18N']->get('October'),
23            $GLOBALS['I18N']->get('November'),
24            $GLOBALS['I18N']->get('December'),
25        );
26        $shortmonths = array(
27            '',
28            $GLOBALS['I18N']->get('Jan'),
29            $GLOBALS['I18N']->get('Feb'),
30            $GLOBALS['I18N']->get('Mar'),
31            $GLOBALS['I18N']->get('Apr'),
32            $GLOBALS['I18N']->get('May'),
33            $GLOBALS['I18N']->get('Jun'),
34            $GLOBALS['I18N']->get('Jul'),
35            $GLOBALS['I18N']->get('Aug'),
36            $GLOBALS['I18N']->get('Sep'),
37            $GLOBALS['I18N']->get('Oct'),
38            $GLOBALS['I18N']->get('Nov'),
39            $GLOBALS['I18N']->get('Dec'),
40        );
41        if ($short) {
42            return $shortmonths[intval($month)];
43        } else {
44            return $months[intval($month)];
45        }
46    }
47}
48
49$systemstats = array(
50    array(
51        'name'  => 'New Subscribers',
52        'query' => sprintf('select count(id) as num,year(entered) year,month(entered) month from %s group by year(entered), month(entered) order by entered desc',
53            $GLOBALS['tables']['user']),
54    ),
55    array(
56        'name'  => 'Total Subscribers',
57        'query' => sprintf('select count(id) as num,year(entered) year,month(entered) month from %s group by year(entered), month(entered) order by entered asc',
58            $GLOBALS['tables']['user']),
59        'collate' => true,
60    ),
61    array(
62        'name'  => 'Current Subscribers',
63        'query' => sprintf('select count(id) as num,year(now()) year,month(now()) month from %s',
64            $GLOBALS['tables']['user']),
65    ),
66    array(
67        'name'  => 'Sent Messages by month',
68        'query' => sprintf('select count(entered) as num,year(entered) as year,month(entered) month from %s where status = "sent" group by year(entered), month(entered) order by entered desc',
69            $GLOBALS['tables']['usermessage']),
70    ),
71    array(
72        'name'  => 'Sent Messages by year',
73        'query' => sprintf('select count(entered) as num,year(entered) as year from %s where status = "sent" group by year(entered) order by entered desc',
74            $GLOBALS['tables']['usermessage']),
75        'range' => 'year',
76    ),
77    array(
78        'name'  => 'Opened Messages',
79        'query' => sprintf('select count(entered) as num,year(entered) as year,month(entered) month from %s where viewed is not null and status = "sent" group by year(entered), month(entered) order by entered desc',
80            $GLOBALS['tables']['usermessage']),
81    ),
82    array(
83        'name'  => 'Campaigns',
84        'query' => sprintf('select count(entered) as num,year(entered) as year,month(entered) month from %s where status = "sent" group by year(entered), month(entered) order by entered desc',
85            $GLOBALS['tables']['message']),
86    ),
87    array(
88        'name'  => 'Campaigns by year',
89        'query' => sprintf('select count(entered) as num,year(entered) as year from %s where status = "sent" group by year(entered) order by entered desc',
90            $GLOBALS['tables']['message']),
91        'range' => 'year',
92    ),
93    array(
94        'name'  => 'Bounces',
95        'query' => sprintf('select count(id) as num,year(date) year,month(date) month from %s group by year(date), month(date) order by date desc',
96            $GLOBALS['tables']['bounce']),
97    ),
98    array(
99        'name'  => 'Blacklist Additions',
100        'query' => sprintf('select count(email) as num,year(added) as year,month(added) month from %s group by year(added), month(added) order by added desc',
101            $GLOBALS['tables']['user_blacklist']),
102    ),
103    array(
104        'name'  => 'Spam Complaints',
105        'query' => sprintf('select count(bl.email) as num,year(added) as year,month(added) month from %s bl,%s bldata where bl.email = bldata.email and bldata.name = "reason" and bldata.data = "blacklisted due to spam complaints" group by year(added), month(added) order by added desc',
106            $GLOBALS['tables']['user_blacklist'], $GLOBALS['tables']['user_blacklist_data']),
107    ),
108    array(
109        'name'  => 'User Clicks',
110        'query' => sprintf('select count(distinct(userid)) as num ,year(firstclick) as year,month(firstclick) month  from %s where forwardid not in (select id from %s where url like "%%/lists/?p=unsubscribe") group by year(firstclick), month(firstclick) order by firstclick desc',
111            $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['linktrack_forward']),
112    ),
113    array(
114        'name'  => 'Unsubscribe Clicks',
115        'query' => sprintf('select count(distinct(userid)) as num ,year(firstclick) as year,month(firstclick) month  from %s where forwardid in (select id from %s where url like "%%/lists/?p=unsubscribe") group by year(firstclick), month(firstclick) order by firstclick desc',
116            $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['linktrack_forward']),
117    ),
118    array(
119        'name'  => 'Next subscriberid',
120        'query' => sprintf('select Auto_increment as num, year(now()) as year, month(now()) as month FROM information_schema.tables where table_name="%s" AND table_schema="%s"',
121            $GLOBALS['tables']['user'], $GLOBALS['database_name']),
122    ),
123);
124
125$chartCount = 0;
126foreach ($systemstats as $item) {
127    ++$chartCount;
128    if (!isset($item['range'])) {
129        $item['range'] = 'month';
130    }
131    if (!isset($item['collate'])) {
132        $item['collate'] = false;
133    }
134
135    $req = Sql_Query($item['query']);
136    $ls = new WebblerListing('');
137    $chartData = array();
138    $collation = 0;
139    while ($row = Sql_Fetch_Assoc($req)) {
140        if (!isset($chartData[$row['year']]) || !is_array($chartData[$row['year']])) {
141            $chartData[$row['year']] = array();
142        }
143        if ($item['collate']) {
144            $collation = $collation + $row['num'];
145            $row['num'] = $collation;
146        }
147        if ($item['range'] != 'year') {
148            $ls->addElement($row['year'].' '.monthName($row['month']));
149            $ls->addColumn($row['year'].' '.monthName($row['month']), '#', $row['num']);
150            $chartData[$row['year']][$row['month']] = $row['num'];
151        } else {
152            $ls->addElement($row['year']);
153            $ls->addColumn($row['year'], '#', $row['num']);
154            $chartData[$row['year']][''] = $row['num'];
155        }
156        if (!empty($row['year']) && !empty($row['month']) && !empty($row['num'])) {
157            cl_output($item['name'].'|'.$row['year'].'|'.$row['month'].'|'.$row['num']);
158        }
159    }
160
161    unset($chartData['2000']);
162    unset($chartData['2001']);
163    unset($chartData['2002']);
164    unset($chartData['2003']);
165    unset($chartData['2004']);
166    unset($chartData['2005']);
167    unset($chartData['2006']);
168    //unset($chartData['2007']);
169    //unset($chartData['2008']);
170    //unset($chartData['2009']);
171    //unset($chartData['2011']);
172
173    //var_dump($chartData);
174    if (class_exists('gBarChart')) {
175        $Chart = new gBarChart(800, 350);
176        $max = 0;
177        $min = 99999;
178        $nummonths = 0;
179        $chartData = array_reverse($chartData, true);
180        foreach ($chartData as $year => $months) {
181            /*
182        print "<h3>$year</h3>";
183        var_dump($months);
184      */
185            ksort($months);
186            $Chart->addDataSet(array_values($months));
187            $monthmax = $util->getMaxOfArray($months);
188            if ($monthmax > $max) {
189                $max = $monthmax;
190            }
191            $nummonths = count($months);
192        }
193        $Chart->setLegend(array_keys($chartData));
194        //$Chart->setBarWidth(4,1,3);
195        $Chart->setAutoBarWidth();
196        $Chart->setColors(array('ff3344', '11ff11', '22aacc', '3333aa'));
197        $Chart->setVisibleAxes(array('x', 'y'));
198        $Chart->setDataRange(0, $max);
199        $Chart->addAxisRange(0, 1, $nummonths);
200        $Chart->addAxisRange(1, 0, $max);
201        //$lineChart->addBackgroundFill('bg', 'EFEFEF');
202        //$lineChart->addBackgroundFill('c', '000000');
203    }
204
205    echo '<div class="tabbed">';
206    echo '<h3>'.$GLOBALS['I18N']->get($item['name']).'</h3>';
207    if (!empty($Chart)) {
208        echo '<ul>';
209        echo '<li><a href="#graph'.$chartCount.'">Graph</a></li>';
210        echo '<li><a href="#numbers'.$chartCount.'">Numbers</a></li>';
211        echo '</ul>';
212    }
213
214    if (!empty($Chart)) {
215        echo '<div id="graph'.$chartCount.'">';
216        //  print $Chart->getUrl();
217        echo '<img src="./?page=gchart&url='.urlencode($Chart->getUrl()).'" />';
218        echo '</div>';
219    }
220    echo '<div id="numbers'.$chartCount.'">';
221    echo $ls->display();
222    echo '</div>';
223    echo '</div>';
224}
225