<commit_before><?php

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

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

$addcomparison = 0;
$access = accessLevel('statsoverview');
$and = '';
$subselect = '';
$paging = '';
$and_params = array();
#print "Access Level: $access";
switch ($access) {
  case 'owner':
    $and = ' and owner = ?';
    $and_params[] = $_SESSION['logindetails']['id'];
    if ($id) {
      $query = sprintf('select owner from %s where id = ? and owner = ?', $GLOBALS['tables']['message']);
      $rs = Sql_Query_Params($query, array($id, $_SESSION['logindetails']['id']));
      $allow = Sql_Fetch_Row($rs);
      if ($allow[0] != $_SESSION["logindetails"]["id"]) {
        print $GLOBALS['I18N']->get('You do not have access to this page');
        return;
      }
    }
    $addcomparison = 1;
    break;
  case 'all':
    break;
  case 'none':
  default:
    $and = ' and id = ?';
    $and_params[] = 0;
    print $GLOBALS['I18N']->get('You do not have access to this page');
    return;
    break;
}

$download = !empty($_GET['dl']);
if ($download) {
  ob_end_clean();
#  header("Content-type: text/plain");
  header('Content-type: text/csv');
  if (!$id) {
    header('Content-disposition:  attachment; filename="phpList Campaign statistics.csv"');
  }
  ob_start();
}  

if (!$id) {
#  print '<p>'.$GLOBALS['I18N']->get('Select Message to view').'</p>';
  print '<div class="actions">'.PageLinkButton('statsoverview&dl=true',$GLOBALS['I18N']->get('Download as CSV file')).'</div>';

  /* broken Adodb conversion by Brian_252 */
/*  $timerange = ' and msg.entered + interval \'6 months\' > current_timestamp';
  #$timerange = '';

  // TODO Use join syntax.
  $query
  = ' select msg.owner, msg.id as messageid'
  . '   , count(um.viewed) as views'
  . '   , count(um.status) as total'
  . '   , subject'
  . '   , to_char(sent, \'dd Mon YYYY\') as sent'
  . '   , bouncecount as bounced'
  . ' from %s um, %s msg'
  . ' where um.messageid = msg.id'
  . ' %s'
  . ' %s'
  . ' group by msg.id, msg.owner, subject, sent, bounced, msg.entered'
  . ' order by msg.entered desc'
  . ' limit 10';
  $query = sprintf($query, $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['message'], $and, $timerange);
  $params = array_merge(array(), $and_params);
  $req = Sql_Query_Params($query, $params);*/

  $timerange = ' and msg.entered > date_sub(current_timestamp,interval 12 month)';
  #$timerange = '';
  $limit = ' limit 10';
  $start = 0;
  if (isset($_GET['start'])) {
    $start = sprintf('%d',$_GET['start']);
    $limit = ' limit '.$start. ', 10';
  }

  $query = sprintf('select msg.owner,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 %s %s
    group by msg.id order by msg.entered desc',
    $GLOBALS['tables']['usermessage'],$GLOBALS['tables']['message'],$subselect,$timerange);
  $req = Sql_Query($query);
  $total = Sql_Num_Rows($req);
  if ($total > 10 && !$download) {
    #print Paging(PageUrl2('statsoverview'),$start,$total,10);
    $paging = simplePaging('statsoverview',$start,$total,10);
    $query .= $limit;
    $req = Sql_Query($query);
  }

  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('Campaigns in the last year'));
  $ls->usePanel($paging);
  while ($row = Sql_Fetch_Array($req)) {
    $element = $row['messageid'].' '.substr($row['subject'],0,50);

    $fwded = Sql_Fetch_Row_Query(sprintf('select count(id) from %s where message = %d',$GLOBALS['tables']['user_message_forward'],$row['messageid']));
    
    $ls->addElement($element,PageURL2('mviews&amp;id='.$row['messageid']));#,PageURL2('message&amp;id='.$row['messageid']));
 #   $ls->addColumn($element,$GLOBALS['I18N']->get('owner'),$row['owner']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('date'),$row['sent']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('sent'),$row['total']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('bounced'),$row['bounced']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('forwarded'),sprintf('%d',$fwded[0]));
    $ls->addColumn($element,$GLOBALS['I18N']->get('views'),$row['views'],$row['views'] ? PageURL2('mviews&amp;id='.$row['messageid']):'');
    $perc = sprintf('%0.2f',($row['views'] / $row['total'] * 100));
    $ls->addColumn($element,$GLOBALS['I18N']->get('rate'),$perc.' %');
  }
  if ($addcomparison) {
    $total = Sql_Fetch_Array_Query(sprintf('select count(entered) as total from %s um', $GLOBALS['tables']['usermessage']));
    $viewed = Sql_Fetch_Array_Query(sprintf('select count(viewed) as viewed from %s um', $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();<commit_msg>only count messages with status sent<commit_after><?php

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

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

$addcomparison = 0;
$access = accessLevel('statsoverview');
$and = '';
$subselect = '';
$paging = '';
$and_params = array();
#print "Access Level: $access";
switch ($access) {
  case 'owner':
    $and = ' and owner = ?';
    $and_params[] = $_SESSION['logindetails']['id'];
    if ($id) {
      $query = sprintf('select owner from %s where id = ? and owner = ?', $GLOBALS['tables']['message']);
      $rs = Sql_Query_Params($query, array($id, $_SESSION['logindetails']['id']));
      $allow = Sql_Fetch_Row($rs);
      if ($allow[0] != $_SESSION["logindetails"]["id"]) {
        print $GLOBALS['I18N']->get('You do not have access to this page');
        return;
      }
    }
    $addcomparison = 1;
    break;
  case 'all':
    break;
  case 'none':
  default:
    $and = ' and id = ?';
    $and_params[] = 0;
    print $GLOBALS['I18N']->get('You do not have access to this page');
    return;
    break;
}

$download = !empty($_GET['dl']);
if ($download) {
  ob_end_clean();
#  header("Content-type: text/plain");
  header('Content-type: text/csv');
  if (!$id) {
    header('Content-disposition:  attachment; filename="phpList Campaign statistics.csv"');
  }
  ob_start();
}  

if (!$id) {
#  print '<p>'.$GLOBALS['I18N']->get('Select Message to view').'</p>';
  print '<div class="actions">'.PageLinkButton('statsoverview&dl=true',$GLOBALS['I18N']->get('Download as CSV file')).'</div>';

  /* broken Adodb conversion by Brian_252 */
/*  $timerange = ' and msg.entered + interval \'6 months\' > current_timestamp';
  #$timerange = '';

  // TODO Use join syntax.
  $query
  = ' select msg.owner, msg.id as messageid'
  . '   , count(um.viewed) as views'
  . '   , count(um.status) as total'
  . '   , subject'
  . '   , to_char(sent, \'dd Mon YYYY\') as sent'
  . '   , bouncecount as bounced'
  . ' from %s um, %s msg'
  . ' where um.messageid = msg.id'
  . ' %s'
  . ' %s'
  . ' group by msg.id, msg.owner, subject, sent, bounced, msg.entered'
  . ' order by msg.entered desc'
  . ' limit 10';
  $query = sprintf($query, $GLOBALS['tables']['usermessage'], $GLOBALS['tables']['message'], $and, $timerange);
  $params = array_merge(array(), $and_params);
  $req = Sql_Query_Params($query, $params);*/

  $timerange = ' and msg.entered > date_sub(current_timestamp,interval 12 month)';
  #$timerange = '';
  $limit = ' limit 10';
  $start = 0;
  if (isset($_GET['start'])) {
    $start = sprintf('%d',$_GET['start']);
    $limit = ' limit '.$start. ', 10';
  }

  $query = sprintf('select msg.owner,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',
    $GLOBALS['tables']['usermessage'],$GLOBALS['tables']['message'],$subselect,$timerange);
  $req = Sql_Query($query);
  $total = Sql_Num_Rows($req);
  if ($total > 10 && !$download) {
    #print Paging(PageUrl2('statsoverview'),$start,$total,10);
    $paging = simplePaging('statsoverview',$start,$total,10);
    $query .= $limit;
    $req = Sql_Query($query);
  }

  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('Campaigns in the last year'));
  $ls->usePanel($paging);
  while ($row = Sql_Fetch_Array($req)) {
    $element = $row['messageid'].' '.substr($row['subject'],0,50);

    $fwded = Sql_Fetch_Row_Query(sprintf('select count(id) from %s where message = %d',$GLOBALS['tables']['user_message_forward'],$row['messageid']));
    
    $ls->addElement($element,PageURL2('mviews&amp;id='.$row['messageid']));#,PageURL2('message&amp;id='.$row['messageid']));
 #   $ls->addColumn($element,$GLOBALS['I18N']->get('owner'),$row['owner']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('date'),$row['sent']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('sent'),$row['total']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('bounced'),$row['bounced']);
    $ls->addColumn($element,$GLOBALS['I18N']->get('forwarded'),sprintf('%d',$fwded[0]));
    $ls->addColumn($element,$GLOBALS['I18N']->get('views'),$row['views'],$row['views'] ? PageURL2('mviews&amp;id='.$row['messageid']):'');
    $perc = sprintf('%0.2f',($row['views'] / $row['total'] * 100));
    $ls->addColumn($element,$GLOBALS['I18N']->get('rate'),$perc.' %');
  }
  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();
{'fraction_non_alphanumeric': 0.23094297007340486, 'fraction_numerical': 0.013928100884622624, 'mean_word_length': 4.596103212216956, 'pattern_counts': {'":': 0, '<': 17, '<?xml version=': 0, '>': 77, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 10, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6601397', 'n_tokens_mistral': 3914, 'n_tokens_neox': 3799, 'n_words': 707}