<?php

# click stats per message
require_once dirname(__FILE__).'/accesscheck.php';

if (isset($_GET['id'])) {
    $id = sprintf('%d', $_GET['id']);
} else {
    $id = 0;
}
if (isset($_GET['start'])) {
    $start = sprintf('%d', $_GET['start']);
} else {
    $start = 0;
}

$addcomparison = 0;
$access = accessLevel('mviews');
#print "Access level: $access";
switch ($access) {
  case 'owner':
    $subselect = ' and owner = '.$_SESSION['logindetails']['id'];
    if ($id) {
        $allow = Sql_Fetch_Row_query(sprintf('select owner from %s where id = %d %s', $GLOBALS['tables']['message'], $id, $subselect));
        if ($allow[0] != $_SESSION['logindetails']['id']) {
            print $GLOBALS['I18N']->get('You do not have access to this page');

            return;
        }
    }
    $addcomparison = 1;
    break;
  case 'all':
    $subselect = '';
    break;
  case 'none':
  default:
    $subselect = ' where id = 0';
    print $GLOBALS['I18N']->get('You do not have access to this page');

    return;
    break;
}

$download = !empty($_GET['dl']);

if (!$id) {
    if ($download) {
        ob_end_clean();
  #  header("Content-type: text/plain");
    header('Content-type: text/csv');
        header('Content-disposition:  attachment; filename="phpList Message open statistics.csv"');
        ob_start();
    }
    print '<p>'.PageLinkButton('mviews&dl=true', $GLOBALS['I18N']->get('Download as CSV file')).'</p>';
#  print '<p>'.$GLOBALS['I18N']->get('Select Message to view').'</p>';
  $timerange = ' and msg.entered  > date_sub(now(),interval 12 month)';
    $timerange = '';
    $limit = 'limit 10';

    $req = Sql_Query(sprintf('select msg.id as messageid,count(um.viewed) as views, count(um.status) as total,
    subject,date_format(sent,"%%e %%b %%Y") as sent,bouncecount as bounced from %s um,%s msg
    where um.messageid = msg.id and um.status = "sent" %s %s
    group by msg.id order by msg.entered desc limit 50', $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['message'], $subselect, $timerange));
    if (!Sql_Affected_Rows()) {
        print '<p class="information">'.$GLOBALS['I18N']->get('There are currently no messages to view').'</p>';
    }

    $ls = new WebblerListing($GLOBALS['I18N']->get('Available Messages'));
    while ($row = Sql_Fetch_Array($req)) {
        #  $element = $row['messageid'].' '.substr($row['subject'],0,50);
    $messagedata = loadMessageData($row['messageid']);
        if (!$download) {
            if ($messagedata['subject'] != $messagedata['campaigntitle']) {
                $element = '<!--'.$row['messageid'].'-->'.stripslashes($messagedata['campaigntitle']).'<br/><strong>'.shortenTextDisplay($messagedata['subject'], 30).'</strong>';
            } else {
                $element = '<!--'.$row['messageid'].'-->'.shortenTextDisplay($messagedata['subject'], 30);
            }
        } else {
            $element = $messagedata['subject'];
        }
        $ls->addElement($element, PageUrl2('mviews&amp;id='.$row['messageid']));
        $ls->setClass($element, 'row1');
        if (!empty($row['sent'])) {
            $ls->addRow($element, '<div class="listingsmall gray">'.$GLOBALS['I18N']->get('date').': '.$row['sent'].'</div>', '');
        } else {
            $ls->addRow($element, '<div class="listingsmall gray">'.$GLOBALS['I18N']->get('date').': '.$GLOBALS['I18N']->get('in progress').'</div>', '');
        }
        $ls->addColumn($element, $GLOBALS['I18N']->get('sent'), $row['total']);
 #   $ls->addColumn($element,$GLOBALS['I18N']->get('bounced'),$row['bounced']);
    $ls->addColumn($element, $GLOBALS['I18N']->get('views'), $row['views'], $row['views'] ? PageURL2('mviews&amp;id='.$row['messageid']) : '');
        $openrate = sprintf('%0.2f', ($row['views'] / $row['total'] * 100));
        $ls->addColumn($element, $GLOBALS['I18N']->get('rate'), $openrate.' %');
/*
    $bouncerate = sprintf('%0.2f',($row['bounced'] / $row['total'] * 100));
    $ls->addColumn($element,$GLOBALS['I18N']->get('bounce rate'),$bouncerate.' %');
    
*/
    }
    if ($addcomparison) {
        $total = Sql_Fetch_Array_Query(sprintf('select count(entered) as total from %s um where um.status = "sent"', $GLOBALS['tables']['usermessage']));
        $viewed = Sql_Fetch_Array_Query(sprintf('select count(viewed) as viewed from %s um where um.status = "sent"', $GLOBALS['tables']['usermessage']));
        $overall = $GLOBALS['I18N']->get('Comparison to other admins');
        $ls->addElement($overall);
        $ls->addColumn($overall, $GLOBALS['I18N']->get('views'), $viewed['viewed']);
        $perc = sprintf('%0.2f', ($viewed['viewed'] / $total['total'] * 100));
        $ls->addColumn($overall, $GLOBALS['I18N']->get('rate'), $perc.' %');
    }
    if ($download) {
        ob_end_clean();
        print $ls->tabDelimited();
    }

    print $ls->display();

    return;
}

if ($download) {
    ob_end_clean();
#  header("Content-type: text/plain");
  header('Content-type: text/csv');
    ob_start();
}
if (empty($start)) {
    print '<p>'.PageLinkButton('mviews&dl=true&id='.$id.'&start='.$start, $GLOBALS['I18N']->get('Download as CSV file')).'</p>';
}

#print '<h3>'.$GLOBALS['I18N']->get('View Details for a Message').'</h3>';
$messagedata = Sql_Fetch_Array_query("SELECT * FROM {$tables['message']} where id = $id $subselect");
print '<table class="mviewsDetails">
<tr><td>'.$GLOBALS['I18N']->get('Subject').'<td><td>'.$messagedata['subject'].'</td></tr>
<tr><td>'.$GLOBALS['I18N']->get('Entered').'<td><td>'.$messagedata['entered'].'</td></tr>
<tr><td>'.$GLOBALS['I18N']->get('Sent').'<td><td>'.$messagedata['sent'].'</td></tr>
</table><hr/>';

if ($download) {
    header('Content-disposition:  attachment; filename="phpList Message open statistics for '.$messagedata['subject'].'.csv"');
}

$ls = new WebblerListing(ucfirst($GLOBALS['I18N']->get('Open statistics')));

$req = Sql_Query(sprintf('select um.userid
    from %s um,%s msg where um.messageid = %d and um.messageid = msg.id and um.viewed is not null %s
    group by userid',
    $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['message'], $id, $subselect));

$total = Sql_Affected_Rows();
if (isset($start) && $start > 0) {
    $listing = sprintf($GLOBALS['I18N']->get('Listing user %d to %d'), $start, $start + MAX_USER_PP);
    $limit = "limit $start,".MAX_USER_PP;
} else {
    $listing =  sprintf($GLOBALS['I18N']->get('Listing user %d to %d'), 1, MAX_USER_PP);
    $limit = 'limit 0,'.MAX_USER_PP;
    $start = 0;
    $limit = 'limit 0,'.MAX_USER_PP;
}

## hmm, this needs more work, as it'll run out of memory, because it's building the entire
## listing before pushing it out.
## would be best to not have a limit, but putting one to avoid that
if ($download) {
    $limit = ' limit 100000';
}

if ($id) {
    $url_keep = '&amp;id='.$id;
} else {
    $url_keep = '';
}

if ($total) {
    $paging = simplePaging("mviews$url_keep", $start, $total, MAX_USER_PP, $GLOBALS['I18N']->get('Entries'));
    $ls->usePanel($paging);
}

$req = Sql_Query(sprintf('select userid,email,um.entered as sent,min(um.viewed) as firstview,
    max(um.viewed) as lastview, count(um.viewed) as viewcount,
    abs(unix_timestamp(um.entered) - unix_timestamp(um.viewed)) as responsetime
    from %s um, %s user, %s msg where um.messageid = %d and um.messageid = msg.id and um.userid = user.id and um.status = "sent" and um.viewed is not null %s
    group by userid %s',
    $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['user'], $GLOBALS['tables']['message'], $id, $subselect, $limit));

$summary = array();
while ($row = Sql_Fetch_Array($req)) {
    if ($download) {
        ## with download, the 50 per page limit is not there.
    set_time_limit(60);
        $element = $row['email'];
    } else {
        $element = shortenTextDisplay($row['email'], 15);
    }
    $ls->addElement($element, PageUrl2('userhistory&amp;id='.$row['userid']));
    $ls->setClass($element, 'row1');
    $ls->addRow($element, '<div class="listingsmall gray">'.$GLOBALS['I18N']->get('sent').': '.formatDateTime($row['sent'], 1).'</div>', '');
    if ($row['viewcount'] > 1) {
        $ls->addColumn($element, $GLOBALS['I18N']->get('firstview'), formatDateTime($row['firstview'], 1));
        $ls->addColumn($element, $GLOBALS['I18N']->get('lastview'), formatDateTime($row['lastview']));
        $ls->addColumn($element, $GLOBALS['I18N']->get('views'), $row['viewcount']);
    } else {
        $ls->addColumn($element, $GLOBALS['I18N']->get('firstview'), formatDateTime($row['firstview'], 1));
        $ls->addColumn($element, $GLOBALS['I18N']->get('responsetime'), secs2time($row['responsetime']));
    }
}
if ($download) {
    ob_end_clean();
    print $ls->tabDelimited();
} else {
    print $ls->display();
}
