stats/compute_mail_stats.php - www.eclipse.org/projects - Git at Google

 <?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 $_SERVER['DOCUMENT_ROOT'] . "/projects/stats/config.php";
 require_once $_SERVER['DOCUMENT_ROOT'] . "/projects/stats/hostname.php";
 require_once $_SERVER['DOCUMENT_ROOT'] . "/projects/stats/utils.php";
 require_once $_SERVER['DOCUMENT_ROOT'] . "/projects/common/projects-info.class.php";

 // function get_last_data_date($db_handle)
 //
 // Obtain the last date for which there is information in the foundation's
 // mailing lists table
 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 number_posts_since($db_handle,$mysql_date,$day,$project,$text)
 //
 // Obtain from all mailing lists for $project the number of posts since
 // the specified date ($mysql_date) and return their added value.
 function number_posts_since($db_handle,$mysql_date,$day,$project,$text){
   $total = 0;

   for($mindex=0;$mindex<$project->hasLists();$mindex++){
     $mailing_list = "\"".$project->getList($mindex)."\"";
     $query = "SELECT SUM(daily_post_count) FROM mailing_list_stats WHERE ";
     $query .= "summary_date > DATE_SUB(\"".$mysql_date."\", INTERVAL ".$day;
     $query .= " DAY) AND list_name = ".$mailing_list;
     $result = mysql_query($query,$db_handle) or die("NPS MySQL Error: ".mysql_error());
     $row = mysql_fetch_row($result);
     if ($row[0] != ""){
       $total += $row[0];
       $nump = $row[0];
     }else
       $nump = 0;
     $text .= "Number of posts for list ".$mailing_list." for the last ".$day;
     $text .= " days: ".$nump."\r\n";
   }
   $text .= "\r\n";

   return $total;
 }

 // function average_number_subscribers($db_handle,$mysql_date,$day,$project,$text)
 //
 // Obtain from all mailing lists for $project the average number of
 // subscribers between the specified date ($mysql_date) and today.
 // Add all mailing lists values and determine the average.
 function average_number_subscribers($db_handle,$mysql_date,$day,$project,$text){
   $total = 0;

   for($mindex=0;$mindex<$project->hasLists();$mindex++){
     $mailing_list = "\"".$project->getList($mindex)."\"";
     $query = "SELECT SUM(total_member_count) FROM mailing_list_stats WHERE ";
     $query .= "summary_date > DATE_SUB(\"".$mysql_date."\", INTERVAL ";
     $query .= $day." DAY) AND list_name = ".$mailing_list;
     $result = mysql_query($query,$db_handle) or die("MySQL Error: ".mysql_error());
     $row = mysql_fetch_row($result);
     if ($row[0] != 0){
       $total = $row[0];
       $nums = $row[0];
     }else
       $nums = 0;
     $text .= "Number of subscribers for list ".$mailing_list." for the last ";
     $text .= $day." days: ".$nums."\r\n";
   }
   $text .= "\r\n";

   return ($total / $day);
 }

 // function update_database($db_handle,$project,$mysql_date,$day,$num_posts,
 //                          $avg_posts,$avg_subscribers)
 //
 // Update the mail stats in the DB. Perform a query to determine if
 // there is already information in the table as we only allow one stat per
 // project per day. Also prevents getting errors if a different stats program
 // (news, bugs, articles, etc) created the row.
 function update_database($db_handle,$project,$mysql_date,$day,$num_posts,
                          $avg_posts,$avg_subscribers){
   $query = "SELECT * FROM ".stats_table()." WHERE project_id = \"".$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)
 //
 // This will perform an update on an alreay existing row for a project on
 // the stats table.
 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_id = \"".$project."\" AND stats_date = \"".$mysql_date."\"";
   flush_print("QRY: $query");

   mysql_query($query,$db_handle) or die("MySQL Error: ".mysql_error());
 }

 // function insertMail($db_handle,$project,$mysql_date,$day,$num_posts,
 //                     $avg_posts,$avg_subscribers)
 //
 // This will perform a single project/row insert on the stats table.
 function insertMail($db_handle,$project,$mysql_date,$day,$num_posts,
                     $avg_posts,$avg_subscribers){
   $query = "INSERT INTO ".stats_table()." (project_id,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.")";
   flush_print("QRY: $query");

   mysql_query($query,$db_handle) or die("MySQL Error: ".mysql_error());
 }

 // function updateLog($dbh,$project,$text)
 //
 // Update the DB log for users to retrive how processing was done.
 // The log will be downloaded by
 // $_SERVER['DOCUMENT_ROOT'] . "projects/dashboard/index.php"
 // and
 // $_SERVER['DOCUMENT_ROOT'] . "projects/dashboard/dashboard_detail.php"
 function updateLog($db_handle,$project,$text){
   $text = str_replace("\"","'",$text); // mysql can't handle
   $query = "SELECT * FROM ".log_table()." WHERE project_id = \"".$project."\"";
   $result = mysql_query($query,$db_handle);
   if ($result && mysql_num_rows($result)){
     $query = "UPDATE ".log_table()." SET mail_text = \"".$text."\"";
     $query .= " WHERE project_id = \"".$project."\"";
   }else{
     $query = "INSERT INTO ".log_table()." (project_id, mail_text) ";
     $query .= "VALUES(\"".$project."\",\"".$text."\")";
   }

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

 // function flush_pring($text)
 //
 // Will force the browser to do a flush print and not wait for more input.
 function flush_print($text){
   echo $text."<br>\n";
   ob_flush();
   flush();
 }

 // 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);

 # List of projects
 $projects = New ProjectsInfo();

 # For every project available
 $project_count = $projects->hasProjects();
 flush_print("Total number of projects to process: ".$project_count);
 for($pindex=0;$pindex<$project_count;$pindex++){
   $project = $projects->getProject($pindex);
   if ($project->exclude_from_dashboard())    // Don't process projects with
     continue;                                // this XML tag
   flush_print("<b>Processing project: ".$project->getName()."</b>");
   $days = explode(",",MAIL_DAYS);
   $data_date = get_last_data_date($db_handle);  // How old is the data
   # For each day specified compute stats
   while(count($days)){
     $day = array_pop($days);
     if ($project->hasLists() == 0){ // If the project has no mailing lists
       update_database($dbd_handle,$project->getProjectID(),$mysql_date,$day,-1,-1,-1);
 $text = "No mailing lists found for project ".$project->getName();
       updateLog($dbd_handle,$project->getProjectID(),$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,$project,$text);
     $avg_posts = round(($num_posts / $divisor),1);
     $avg_subscribers = round(average_number_subscribers($db_handle,$old_date,$divisor,$project,$text));

 $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->getProjectID(),$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers);
   }
   if ($project != -1)  // Valid mailing list
     updateLog($dbd_handle,$project->getProjectID(),$text);
 }

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

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

 ?>
	<?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 $_SERVER['DOCUMENT_ROOT'] . "/projects/stats/config.php";
	require_once $_SERVER['DOCUMENT_ROOT'] . "/projects/stats/hostname.php";
	require_once $_SERVER['DOCUMENT_ROOT'] . "/projects/stats/utils.php";
	require_once $_SERVER['DOCUMENT_ROOT'] . "/projects/common/projects-info.class.php";

	// function get_last_data_date($db_handle)
	//
	// Obtain the last date for which there is information in the foundation's
	// mailing lists table
	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 number_posts_since($db_handle,$mysql_date,$day,$project,$text)
	//
	// Obtain from all mailing lists for $project the number of posts since
	// the specified date ($mysql_date) and return their added value.
	function number_posts_since($db_handle,$mysql_date,$day,$project,$text){
	$total = 0;

	for($mindex=0;$mindex<$project->hasLists();$mindex++){
	$mailing_list = "\"".$project->getList($mindex)."\"";
	$query = "SELECT SUM(daily_post_count) FROM mailing_list_stats WHERE ";
	$query .= "summary_date > DATE_SUB(\"".$mysql_date."\", INTERVAL ".$day;
	$query .= " DAY) AND list_name = ".$mailing_list;
	$result = mysql_query($query,$db_handle) or die("NPS MySQL Error: ".mysql_error());
	$row = mysql_fetch_row($result);
	if ($row[0] != ""){
	$total += $row[0];
	$nump = $row[0];
	}else
	$nump = 0;
	$text .= "Number of posts for list ".$mailing_list." for the last ".$day;
	$text .= " days: ".$nump."\r\n";
	}
	$text .= "\r\n";

	return $total;
	}

	// function average_number_subscribers($db_handle,$mysql_date,$day,$project,$text)
	//
	// Obtain from all mailing lists for $project the average number of
	// subscribers between the specified date ($mysql_date) and today.
	// Add all mailing lists values and determine the average.
	function average_number_subscribers($db_handle,$mysql_date,$day,$project,$text){
	$total = 0;

	for($mindex=0;$mindex<$project->hasLists();$mindex++){
	$mailing_list = "\"".$project->getList($mindex)."\"";
	$query = "SELECT SUM(total_member_count) FROM mailing_list_stats WHERE ";
	$query .= "summary_date > DATE_SUB(\"".$mysql_date."\", INTERVAL ";
	$query .= $day." DAY) AND list_name = ".$mailing_list;
	$result = mysql_query($query,$db_handle) or die("MySQL Error: ".mysql_error());
	$row = mysql_fetch_row($result);
	if ($row[0] != 0){
	$total = $row[0];
	$nums = $row[0];
	}else
	$nums = 0;
	$text .= "Number of subscribers for list ".$mailing_list." for the last ";
	$text .= $day." days: ".$nums."\r\n";
	}
	$text .= "\r\n";

	return ($total / $day);
	}

	// function update_database($db_handle,$project,$mysql_date,$day,$num_posts,
	// $avg_posts,$avg_subscribers)
	//
	// Update the mail stats in the DB. Perform a query to determine if
	// there is already information in the table as we only allow one stat per
	// project per day. Also prevents getting errors if a different stats program
	// (news, bugs, articles, etc) created the row.
	function update_database($db_handle,$project,$mysql_date,$day,$num_posts,
	$avg_posts,$avg_subscribers){
	$query = "SELECT * FROM ".stats_table()." WHERE project_id = \"".$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)
	//
	// This will perform an update on an alreay existing row for a project on
	// the stats table.
	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_id = \"".$project."\" AND stats_date = \"".$mysql_date."\"";
	flush_print("QRY: $query");

	mysql_query($query,$db_handle) or die("MySQL Error: ".mysql_error());
	}

	// function insertMail($db_handle,$project,$mysql_date,$day,$num_posts,
	// $avg_posts,$avg_subscribers)
	//
	// This will perform a single project/row insert on the stats table.
	function insertMail($db_handle,$project,$mysql_date,$day,$num_posts,
	$avg_posts,$avg_subscribers){
	$query = "INSERT INTO ".stats_table()." (project_id,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.")";
	flush_print("QRY: $query");

	mysql_query($query,$db_handle) or die("MySQL Error: ".mysql_error());
	}

	// function updateLog($dbh,$project,$text)
	//
	// Update the DB log for users to retrive how processing was done.
	// The log will be downloaded by
	// $_SERVER['DOCUMENT_ROOT'] . "projects/dashboard/index.php"
	// and
	// $_SERVER['DOCUMENT_ROOT'] . "projects/dashboard/dashboard_detail.php"
	function updateLog($db_handle,$project,$text){
	$text = str_replace("\"","'",$text); // mysql can't handle
	$query = "SELECT * FROM ".log_table()." WHERE project_id = \"".$project."\"";
	$result = mysql_query($query,$db_handle);
	if ($result && mysql_num_rows($result)){
	$query = "UPDATE ".log_table()." SET mail_text = \"".$text."\"";
	$query .= " WHERE project_id = \"".$project."\"";
	}else{
	$query = "INSERT INTO ".log_table()." (project_id, mail_text) ";
	$query .= "VALUES(\"".$project."\",\"".$text."\")";
	}

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

	// function flush_pring($text)
	//
	// Will force the browser to do a flush print and not wait for more input.
	function flush_print($text){
	echo $text."<br>\n";
	ob_flush();
	flush();
	}

	// 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);

	# List of projects
	$projects = New ProjectsInfo();

	# For every project available
	$project_count = $projects->hasProjects();
	flush_print("Total number of projects to process: ".$project_count);
	for($pindex=0;$pindex<$project_count;$pindex++){
	$project = $projects->getProject($pindex);
	if ($project->exclude_from_dashboard()) // Don't process projects with
	continue; // this XML tag
	flush_print("<b>Processing project: ".$project->getName()."</b>");
	$days = explode(",",MAIL_DAYS);
	$data_date = get_last_data_date($db_handle); // How old is the data
	# For each day specified compute stats
	while(count($days)){
	$day = array_pop($days);
	if ($project->hasLists() == 0){ // If the project has no mailing lists
	update_database($dbd_handle,$project->getProjectID(),$mysql_date,$day,-1,-1,-1);
	$text = "No mailing lists found for project ".$project->getName();
	updateLog($dbd_handle,$project->getProjectID(),$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,$project,$text);
	$avg_posts = round(($num_posts / $divisor),1);
	$avg_subscribers = round(average_number_subscribers($db_handle,$old_date,$divisor,$project,$text));

	$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->getProjectID(),$mysql_date,$day,$num_posts,$avg_posts,$avg_subscribers);
	}
	if ($project != -1) // Valid mailing list
	updateLog($dbd_handle,$project->getProjectID(),$text);
	}

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

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

	?>