stats/compute_mail_stats.php

<?php

require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection_dashboard_rw.class.php";
require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection.class.php";
require_once "config.php";
require_once "groups.class.php";
require_once "mailinglists.class.php";
require_once "hostname.php";
require_once "utils.php";

function get_last_data_date($db_handle){
  $query = "SELECT summary_date FROM mailing_list_stats ORDER BY summary_date ASC LIMIT 1";
  $result = mysql_query($query,$db_handle);
  $row = mysql_fetch_assoc($result);

  $tdate = $row['summary_date'];
  $old_date = substr($tdate,0,4).substr($tdate,5,2).substr($tdate,8,2);

  return getTimeDiff($old_date,date('Ymd'));
}

///////////////////////////////////////////////////////////////////////////////
// function valid_list($list)                                                //
//                                                                           //
// Compatibility function to determine if the mailing list in the groups.txt //
// file is valid compared to the information available from the Eclipse      //
// database.                                                                 //
///////////////////////////////////////////////////////////////////////////////
function valid_list($db_handle,$list){
  $query = "SELECT count(*) FROM mailing_lists WHERE list_name = \"";
  $query .= $list."\" AND is_private <> 1";
  $result = mysql_query($query,$db_handle);
  $row = mysql_fetch_row($result);

  return $row[0];
}

///////////////////////////////////////////////////////////////////////////////
// function mailing_list_query($mlists)                                      //
//                                                                           //
// Function that creates the last part of the queries that will be used to   // 
// fetch the statistical information from the database. It will add all      //
// mailing lists for a particular project in the following format:           //
//                                                                           // 
//  (list_name = "list1" OR list_name = "list2" OR ... list_name = "listN")  //
///////////////////////////////////////////////////////////////////////////////
function mailing_list_query($db_handle,$mlists){
  $num_lists = $mlists->numberOfMailingLists();
  if ($num_lists == 0)   # No mailing lists defined
    return $tmp_query;

  $tmp_query = "(";

  for($i=0;$i<$num_lists;){
    $curr_list = $mlists->mailingListAt($i);
    $i++;
    if (!valid_list($db_handle,$curr_list))
      continue;
    $tmp_query .= "list_name = \"".$curr_list."\"";
    if ($i != $num_lists)
      $tmp_query .= " OR ";
    else
      $tmp_query .= ")";
  }

  if ($tmp_query == "(")
    return "";
  else
    return $tmp_query;
}

function number_posts_since($db_handle,$mysql_date,$day,$tmp_query){
  if ($tmp_query == "")   # No mailing list data available
    return 0;

  $query = "SELECT SUM(daily_post_count) FROM mailing_list_stats WHERE ";
  $query .= "summary_date > DATE_SUB(\"".$mysql_date."\", INTERVAL ".$day." DAY) AND ";
  $query .= $tmp_query;
  $result = mysql_query($query,$db_handle); 
  $row = mysql_fetch_row($result);

  return $row[0]; // count(number_messages)
}

function average_number_subscribers($db_handle,$mysql_date,$day,$tmp_query){
  if ($tmp_query == "")   # No mailing list data available
    return 0;

  $query = "SELECT SUM(total_member_count) FROM mailing_list_stats WHERE ";
  $query .= "summary_date > DATE_SUB(\"".$mysql_date."\", INTERVAL ";
  $query .= $day." DAY) AND ".$tmp_query;
  $result = mysql_query($query,$db_handle);
  $row = mysql_fetch_row($result);

  return ($row[0] / $day);
}

function update_database($db_handle,$project,$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers){
  $query = "SELECT * FROM ".stats_table()." WHERE project = \"".$project."\"";
  $query .= " AND stats_date = \"".$mysql_date."\"";
  $result = mysql_query($query,$db_handle);
  if ($result && mysql_num_rows($result))
    updateMail($db_handle,$project,$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers);
  else
    insertMail($db_handle,$project,$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers);
}

function updateMail($db_handle,$project,$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers){
  $query = "UPDATE ".stats_table()." SET mail_".$day."_number_posts = ";
  $query .= $num_posts.", mail_".$day."_average_posts = ".$avg_posts;
  $query .= ", mail_".$day."_average_subscribers = ".$avg_subscribers;
  $query .= " WHERE project = \"".$project."\" AND stats_date = \"".$mysql_date."\"";

echo "QRY: $query<br>\n";

  mysql_query($query,$db_handle);
}

function insertMail($db_handle,$project,$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers){
  $query = "INSERT INTO ".stats_table()." (project,stats_date, mail_".$day;
  $query .= "_number_posts, mail_".$day."_average_posts";
  $query .= ", mail_".$day."_average_subscribers) VALUES(\"";
  $query .= $project."\",\"".$mysql_date."\",".$num_posts.",".$avg_posts;
  $query .= ",".$avg_subscribers.")";

echo "QRY: $query<br>\n";

  mysql_query($query,$db_handle);
}

function updateLog($db_handle,$project,$text){
  $text = str_replace("\"","'",$text); // mysql can't handle
  $query = "SELECT * FROM ".log_table()." WHERE project = \"".$project."\"";
  $result = mysql_query($query,$db_handle);
  if ($result && mysql_num_rows($result)){
    $query = "UPDATE ".log_table()." SET mail_text = \"".$text."\"";
    $query .= " WHERE project = \"".$project."\"";
  }else{
    $query = "INSERT INTO ".log_table()." (project, mail_text) ";
    $query .= "VALUES(\"".$project."\",\"".$text."\")";
  }

#echo "<pre>".$text."</pre><br>\n";
  mysql_query($query,$db_handle) or die("Error: ".mysql_error());
}

// Dashboard connection
$dbd_connection = new DBConnectionDashboard();
$dbd_handle     = $dbd_connection->connect();

// Eclipse connection
$db_connection = new DBConnection();
$db_handle     = $db_connection->connect();


echo "<html>\n<head>\n<title>Compute Mail Stats</title>\n</head>\n<body>\n";

# Date to process info from
#
# IMPORTANT: since the mail data is always one day behind we have to 
#            use yesterday's date to get information but the current
#            date to store it 
#
$mysql_date = date('Y-m-d');
$date = getDateBefore(1);
$old_date = substr($date,0,4)."-".substr($date,4,2)."-".substr($date,6,2);

# Load groups from file
$groups = new Groups();
$groups->loadGroups();                        # Get group info from file
$grouplists = $groups->getMailingLists();
$projectlist = $groups->getProjects();        # Need for empty groups

while(count($grouplists)){
  $list = array_pop($grouplists);
  $pr = array_pop($projectlist);
  $project = $groups->getProjectFromMailingList($list);
  if ($project != -1){ // No project name found
    echo "<b>Processing project: $project</b><br>\n";
    $mlquery = mailing_list_query($db_handle,$list);
  }else
    echo "<b>Processing project: $pr</b><br>\n";
$tmp = implode(", ",$list->getMailingLists());
$s = ($list->numberOfMailingLists()>1)?"s":"";
$text = "Processing ".$tmp." mailing list".$s." for project ".$project."\r\n\r\n";
  $days = explode(",",MAIL_DAYS);
  $data_date = get_last_data_date($db_handle);  // How old is the data
  while(count($days)){                          // Compute stats
    $day = array_pop($days);                    // for every day
    if ($project == -1){
      update_database($dbd_handle,$pr,$mysql_date,$day,-1,-1,-1);
      $text = "No mailing lists found for project ".$pr;
      updateLog($dbd_handle,$pr,$text);
      continue;
    }
$text .= "INFORMATION FOR THE LAST ".$day." DAYS\r\n\r\n";
    if ($data_date < $day){                      // Not enough info
      $divisor = $data_date;                     // so divide by available
$text .= "Could not find $day days worth of information in the mailing list table.\r\nGetting information for the last $divisor days\r\n\r\n";
    }else
      $divisor = $day;
    $num_posts = number_posts_since($db_handle,$old_date,$divisor,$mlquery);  
    $avg_posts = round(($num_posts / $divisor),1);
    $avg_subscribers = round(average_number_subscribers($db_handle,$old_date,$divisor,$mlquery));
 
$text .= "Total number of posts for all lists: ".$num_posts."\r\n";
$text .= "Average number of posts for all lists: ".$avg_posts."\r\n";
$text .= "Average number of subscribers for all lists: ".$avg_subscribers."\r\n\r\n";

    update_database($dbd_handle,$project,$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers);
  }
  if ($project != -1)  // Valid mailing list
    updateLog($dbd_handle,$project,$text);
}

echo "\n</body>\n</html>\n";

$result         = null;
$db_handle      = null;
$db_connection  = null;
$dbd_handle     = null;
$dbd_connection = null;

?>