<?php

@include 'gchartphp/gChart.php';

if (class_exists('utility')) {
    $util = new utility();
}
if (!function_exists('monthName')) {
    function monthName($month, $short = 0)
    {
        $months = array(
            '',
            $GLOBALS['I18N']->get('January'),
            $GLOBALS['I18N']->get('February'),
            $GLOBALS['I18N']->get('March'),
            $GLOBALS['I18N']->get('April'),
            $GLOBALS['I18N']->get('May'),
            $GLOBALS['I18N']->get('June'),
            $GLOBALS['I18N']->get('July'),
            $GLOBALS['I18N']->get('August'),
            $GLOBALS['I18N']->get('September'),
            $GLOBALS['I18N']->get('October'),
            $GLOBALS['I18N']->get('November'),
            $GLOBALS['I18N']->get('December')
        );
        $shortmonths = array(
            '',
            $GLOBALS['I18N']->get('Jan'),
            $GLOBALS['I18N']->get('Feb'),
            $GLOBALS['I18N']->get('Mar'),
            $GLOBALS['I18N']->get('Apr'),
            $GLOBALS['I18N']->get('May'),
            $GLOBALS['I18N']->get('Jun'),
            $GLOBALS['I18N']->get('Jul'),
            $GLOBALS['I18N']->get('Aug'),
            $GLOBALS['I18N']->get('Sep'),
            $GLOBALS['I18N']->get('Oct'),
            $GLOBALS['I18N']->get('Nov'),
            $GLOBALS['I18N']->get('Dec')
        );
        if ($short) {
            return $shortmonths[intval($month)];
        } else {
            return $months[intval($month)];
        }
    }
}

$systemstats = array(
    array(
        'name' => 'New Subscribers',
        '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',
            $GLOBALS['tables']['user']),
    ),
    array(
        'name' => 'Total Subscribers',
        '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',
            $GLOBALS['tables']['user']),
        'collate' => true,
    ),
    array(
        'name' => 'Current Subscribers',
        'query' => sprintf('select count(id) as num,year(now()) year,month(now()) month from %s',
            $GLOBALS['tables']['user']),
    ),
    array(
        'name' => 'Sent Messages by month',
        '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',
            $GLOBALS['tables']['usermessage']),
    ),
    array(
        'name' => 'Sent Messages by year',
        'query' => sprintf('select count(entered) as num,year(entered) as year from %s where status = "sent" group by year(entered) order by entered desc',
            $GLOBALS['tables']['usermessage']),
        'range' => 'year',
    ),
    array(
        'name' => 'Opened Messages',
        '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',
            $GLOBALS['tables']['usermessage']),
    ),
    array(
        'name' => 'Campaigns',
        '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',
            $GLOBALS['tables']['message']),
    ),
    array(
        'name' => 'Campaigns by year',
        'query' => sprintf('select count(entered) as num,year(entered) as year from %s where status = "sent" group by year(entered) order by entered desc',
            $GLOBALS['tables']['message']),
        'range' => 'year',
    ),
    array(
        'name' => 'Bounces',
        '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',
            $GLOBALS['tables']['bounce']),
    ),
    array(
        'name' => 'Blacklist Additions',
        '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',
            $GLOBALS['tables']['user_blacklist']),
    ),
    array(
        'name' => 'Spam Complaints',
        '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',
            $GLOBALS['tables']['user_blacklist'], $GLOBALS['tables']['user_blacklist_data']),
    ),
    array(
        'name' => 'User Clicks',
        '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',
            $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['linktrack_forward']),
    ),
    array(
        'name' => 'Unsubscribe Clicks',
        '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',
            $GLOBALS['tables']['linktrack_uml_click'], $GLOBALS['tables']['linktrack_forward']),
    ),
    array(
        'name' => 'Next subscriberid',
        '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"',
            $GLOBALS['tables']['user'], $GLOBALS['database_name']),
    ),
);

$chartCount = 0;
foreach ($systemstats as $item) {
    ++$chartCount;
    if (!isset($item['range'])) {
        $item['range'] = 'month';
    }
    if (!isset($item['collate'])) {
        $item['collate'] = false;
    }

    $req = Sql_Query($item['query']);
    $ls = new WebblerListing('');
    $chartData = array();
    $collation = 0;
    while ($row = Sql_Fetch_Assoc($req)) {
        if (!isset($chartData[$row['year']]) || !is_array($chartData[$row['year']])) {
            $chartData[$row['year']] = array();
        }
        if ($item['collate']) {
            $collation = $collation + $row['num'];
            $row['num'] = $collation;
        }
        if ($item['range'] != 'year') {
            $ls->addElement($row['year'] . ' ' . monthName($row['month']));
            $ls->addColumn($row['year'] . ' ' . monthName($row['month']), '#', $row['num']);
            $chartData[$row['year']][$row['month']] = $row['num'];
        } else {
            $ls->addElement($row['year']);
            $ls->addColumn($row['year'], '#', $row['num']);
            $chartData[$row['year']][''] = $row['num'];
        }
        if (!empty($row['year']) && !empty($row['month']) && !empty($row['num'])) {
            cl_output($item['name'] . '|' . $row['year'] . '|' . $row['month'] . '|' . $row['num']);
        }
    }

    unset($chartData['2000']);
    unset($chartData['2001']);
    unset($chartData['2002']);
    unset($chartData['2003']);
    unset($chartData['2004']);
    unset($chartData['2005']);
    unset($chartData['2006']);
    #unset($chartData['2007']);
    #unset($chartData['2008']);
    #unset($chartData['2009']);
    #unset($chartData['2011']);

    #var_dump($chartData);
    if (class_exists('gBarChart')) {
        $Chart = new gBarChart(800, 350);
        $max = 0;
        $min = 99999;
        $nummonths = 0;
        $chartData = array_reverse($chartData, true);
        foreach ($chartData as $year => $months) {
            /*
        print "<h3>$year</h3>";
        var_dump($months);
      */
            ksort($months);
            $Chart->addDataSet(array_values($months));
            $monthmax = $util->getMaxOfArray($months);
            if ($monthmax > $max) {
                $max = $monthmax;
            }
            $nummonths = count($months);
        }
        $Chart->setLegend(array_keys($chartData));
        #$Chart->setBarWidth(4,1,3);
        $Chart->setAutoBarWidth();
        $Chart->setColors(array('ff3344', '11ff11', '22aacc', '3333aa'));
        $Chart->setVisibleAxes(array('x', 'y'));
        $Chart->setDataRange(0, $max);
        $Chart->addAxisRange(0, 1, $nummonths);
        $Chart->addAxisRange(1, 0, $max);
        #$lineChart->addBackgroundFill('bg', 'EFEFEF');
        #$lineChart->addBackgroundFill('c', '000000');
    }

    print '<div class="tabbed">';
    print '<h3>' . $GLOBALS['I18N']->get($item['name']) . '</h3>';
    if (!empty($Chart)) {
        print '<ul>';
        print '<li><a href="#graph' . $chartCount . '">Graph</a></li>';
        print '<li><a href="#numbers' . $chartCount . '">Numbers</a></li>';
        print '</ul>';
    }

    if (!empty($Chart)) {
        print '<div id="graph' . $chartCount . '">';
        #  print $Chart->getUrl();
        print '<img src="./?page=gchart&url=' . urlencode($Chart->getUrl()) . '" />';
        print '</div>';
    }
    print '<div id="numbers' . $chartCount . '">';
    print $ls->display();
    print '</div>';
    print '</div>';
}
