<?php
/*******************************************************************************
 * Copyright (c) 2007-2013 Eclipse Foundation and others.
* All rights reserved. This program and the accompanying materials
* are made available under the terms of the Eclipse Public License v1.0
* which accompanies this distribution, and is available at
* http://www.eclipse.org/legal/epl-v10.html
*
* Contributors:
*    Denis Roy (Eclipse Foundation)- initial API and implementation
*******************************************************************************/

	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/app.class.php");
	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/nav.class.php"); 
	require_once($_SERVER['DOCUMENT_ROOT'] . "/eclipse.org-common/system/menu.class.php");
	require_once "/home/data/httpd/eclipse-php-classes/system/dbconnection.class.php";
	$App 	= new App();	$Menu 	= new Menu();
	$Session = $App->useSession(true);
	include("_projectCommon.php");

	# Begin buffered output
	ob_start();

	$pageTitle 		= "Eclipse Download Stats";
	$pageKeywords	= "";
	$pageAuthor		= "Eclipse Foundation, Inc.";
	header("Content-type: text/html; charset=utf-8");
	header("Cache-Control: no-cache, must-revalidate");

	$_FILENAME 	= $App->getHTTPParameter("filename", "");
	$_VIEW_DATE = $App->getHTTPParameter("view_date", "");
	$_VIEW 		= $App->getHTTPParameter("view", "");
	$_GROUP 	= $App->getHTTPParameter("group", "");
	$_DEBUG 	= $App->getHTTPParameter("debug", "");
	$_DATEFROM	= $App->getHTTPParameter("datefrom", "");
	$_DATETO 	= $App->getHTTPParameter("dateto", "");
	$_EMAIL 	= $App->getHTTPParameter("email", "");
	
	# These tags added by Donald on Oct31/06 for special formatting KPI Contraints
	$_FILETAG 	= $App->getHTTPParameter("filetag", "");
	$_VERTAG 	= $App->getHTTPParameter("vertag", "");
	$_EMERGENCY	= $App->getHTTPParameter("emergency", "");
	$_COUNT 	= $App->getHTTPParameter("count", "");

	# This tag holds the Loads of the Server if it was OK, to be displayed in en_stats_queued.php so user knows how loaded server is
	$_LOAD_IS = "unknown";
	
	# Bypass entire script if main is too busy
	$fileName = "/home/data/common/monitor/loadavg/dbslave";
	$threshold = "16.00";
	
	if($h = @fopen($fileName, "r")) {
		$line = fread($h, filesize($fileName));
		@fclose($h);
		$parts = explode(" ", $line);
		
		if($parts[0] > $threshold) {
			echo "<html><head></head><body><h2>Server too busy ($parts[0]>$threshold) to run live stats. Please try later.</h2></body></html>";
			exit;
		} else {
			$_LOAD_IS = $parts[0];
		}
	}
				
	# echo "<html><head></head><body><h2>Live stats are currently disabled as one of our database servers is down.</h2></body></html>";
	# exit;
	
	
	/*
	 * Build query filter criteria
	 */
	
	# We track date-based queries differently as MySQL 4.0.x doesn't support subselects
	# and in our tests, joining the index with the downloads AND using a where for the dates
	# resulted in mysql using a tablesort on the entire downloads table, which took minutes.
	# if dateQuery = true, then we fetch the resultset in two steps: one select to get the
	# file_id's matching the file pattern (trivial), and another select where IN(1,2,3,4) with the file_id
	$dateQuery 		= false;
	
	
	# Default date-query adds overhead vs. searching all
	#if($_VIEW_DATE == "") {
	#	$_VIEW_DATE = "L30";
	#	$dateQuery = true;
	#}
	
	if($_VIEW == "") {
		$_VIEW = "sum";
	}
	
	$WHERE 		= "";
	$GROUPBY 	= "";
	$FILESPEC  	= "IDX.file_name";
	
	if($_VIEW_DATE == "today") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= " LEFT(DOW.download_date,10) = CURDATE()";
		
		$dateQuery = true; 
	}
	if($_VIEW_DATE == "yesterday") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= " LEFT(DOW.download_date,10) = DATE_SUB(CURDATE(), INTERVAL 1 DAY)";
		
		$dateQuery = true; 
	}
	if($_VIEW_DATE == "L7") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= " DOW.download_date > DATE_SUB(CURDATE(), INTERVAL 8 day)";
		
		$dateQuery = true; 
	}
	if($_VIEW_DATE == "L30") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= " DOW.download_date > DATE_SUB(CURDATE(), INTERVAL 31 day)";
		
		$dateQuery = true; 
	}

	if($_DATEFROM != "" && $_DATETO != "") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= "DOW.download_date BETWEEN \"$_DATEFROM\" AND \"$_DATETO\"";
		
		$dateQuery = true;
	}
	
	if($_DATEFROM != "" && $_DATETO == "") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= "DOW.download_date >= \"$_DATEFROM\"";
		
		$dateQuery = true;
	}
	
	if($_DATEFROM == "" && $_DATETO != "") {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= "DOW.download_date <= \"$_DATETO\"";
		
		$dateQuery = true;
	}


	if($_GROUP != 1) {
		$GROUPBY = "IDX.file_name";
	}
	else {
		$FILESPEC =" \"File Group\" AS file_name";
	}
	
	if($_VIEW == "daily") {
		if($GROUPBY != "") {
			$GROUPBY .= ",";
		}
		$GROUPBY .= "LEFT(DOW.download_date,10)";
	}
	if($_VIEW == "ccode") {
		if($GROUPBY != "") {
			$GROUPBY .= ",";
		}
		$GROUPBY .= "DOW.ccode";
	}
	
	
	# Connect to database
	$dbc 	= new DBConnection();
	$now = microtime_float();
	$dbh 	= $dbc->connect();
	$connect = microtime_float() - $now;

	# fetch oldest stat and total records
	$sql_info = "SELECT LEFT(MIN(download_date),10) as OldestDate, MAX(download_date) AS NewestDate, COUNT(*) AS RecordCount FROM downloads";
	$rs_info 		= mysql_query($sql_info, $dbh);
	$myrow_info 	= mysql_fetch_assoc($rs_info);
	$intTotalStats 	= number_format($myrow_info['RecordCount']);
	$sinceDate 		= $myrow_info['OldestDate'];
	$toDate 		= $myrow_info['NewestDate'];

	
	# Fetch the ID's if it's a date-based query
	$aFileID = array();
	$file_id_csv = "";
	
	if($_FILENAME != "") {
		$fileCount = 0;
		
		$sql = "SELECT IDX.file_id FROM download_file_index AS IDX WHERE IDX.file_name LIKE " . $App->returnQuotedString("%" . $_FILENAME . "%");
		$rs 	= mysql_query($sql, $dbh);
		while($myrow = mysql_fetch_assoc($rs)) {
			array_push($aFileID, $myrow['file_id']);
			$fileCount++;
		}
		$file_id_csv = implode(",", $aFileID);
		
		if(($fileCount > 5000 && $dateQuery) || ($fileCount > 10000 && !$dateQuery)) {
			die ("Your search includes too many files ($fileCount).  Please refine your search.");
		}
	}

	if($_FILENAME != "" && !$dateQuery) {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= " IDX.file_name LIKE " . $App->returnQuotedString("%" . $_FILENAME . "%");
	}
	if($dateQuery) {
		$WHERE = $App->addAndIfNotNull($WHERE);
		$WHERE .= " IDX.file_id IN ($file_id_csv)"; 
	}
	
	if($WHERE != "") {
		$WHERE = " WHERE " . $WHERE;
	}
	if($GROUPBY != "") {
		$GROUPBY = " GROUP BY " . $GROUPBY;
	}

	/*
	 * End: Build query filter criteria
	 */
	

	/*
	 * Choose the appropriate query, based on the view
	 * 
	 */
	
	if($_VIEW == "sum") {
		if(!$dateQuery) {
			# simplified query based on the download_file_index only
			if($_GROUP == 1) {
				$sql = "SELECT $FILESPEC, SUM(download_count) AS file_count FROM download_file_index AS IDX " . $WHERE;
			}
			else {
				$sql = "SELECT $FILESPEC, download_count AS file_count FROM download_file_index AS IDX " . $WHERE . " " . $GROUPBY . " ORDER BY file_count DESC";
			}
		}
		else {
			$sql = "SELECT $FILESPEC, COUNT(DOW.file_id) AS file_count FROM	download_file_index AS IDX LEFT JOIN downloads AS DOW ON DOW.file_id = IDX.file_id
				" . $WHERE . " 
				" . $GROUPBY . " ORDER BY file_count DESC";
		}	
		$inc_file = "inc/en_stats.php";
					
	}
	if($_VIEW == "daily" ) {
		$sql = "SELECT $FILESPEC, LEFT(DOW.download_date,10) AS download_date, COUNT(DOW.file_id) AS file_count	FROM download_file_index AS IDX
						LEFT JOIN downloads AS DOW ON DOW.file_id = IDX.file_id	" . $WHERE . " " . $GROUPBY . "	ORDER BY IDX.file_name, DOW.download_date DESC";
				
		$inc_file = "inc/en_stats_daily.php";
	}
	
	
	if($_VIEW == "ccode") {
		
		$ORDERBY = "ORDER BY IDX.file_name, file_count DESC";
		if($_GROUP == 1) {
			$ORDERBY = "ORDER BY file_count DESC";
		}
		
		$sql = "SELECT $FILESPEC, DOW.ccode, COU.en_description AS CountryDescription, COUNT(DOW.file_id) AS file_count	FROM download_file_index AS IDX LEFT JOIN downloads AS DOW ON DOW.file_id = IDX.file_id
LEFT JOIN SYS_countries AS COU ON COU.ccode = DOW.ccode	" . $WHERE . " 
				" . $GROUPBY . "
				" . $ORDERBY;
				
	# These tags and this IF Logic added by Donald on Oct31/06 
	# for special formatting KPI Contraints
		if ($_FILETAG != "" && $_VERTAG != "") {
			$inc_file = "inc/en_stats_ccode_special.php";
		} else {
			$inc_file = "inc/en_stats_ccode.php";
		}
	}	
	
	# Bypass query if no filename specified
	if($_FILENAME == "") {
		$sql = "SELECT 'Please specify a file' AS file_name, '' AS file_count";
		
	}
	
	$rs;
	if($_EMAIL != "") {
		# terminate the http request and defer the query
		session_write_close();
		
		$inc_file = "inc/en_stats_queued.php"; 
		include($inc_file);
		ob_flush();
		flush();
		
		$rs 	= mysql_query($sql, $dbh);
		$message = "Your query results:\n\n";
		$message .= "Filename: " . $_FILENAME . "\n";
		$message .= "Date from: " . $_DATEFROM . "\n"; 
		$message .= "Date to: " . $_DATETO . "\n\n";
		
		$i 			= 0;
		$dl_count 	= 0;
	
	# These tags and this IF Logic added by Donald on Oct31/06 
	# for special formatting for Don's KPI Contraints
		if ($_FILETAG != "" && $_VERTAG != "") {
		$message .= "KPISTART\n";
			while($myrow = mysql_fetch_assoc($rs)) {
				$i++;
				$CountryDescription = $myrow['CountryDescription'];
				if($CountryDescription == "") {
					$CountryDescription = "Unknown";
				}
				$CountryDescription = str_replace(",", "", $CountryDescription);
				
				$message .= $_FILETAG . "," . $_VERTAG . "," . $_DATEFROM . "," . $myrow['ccode'] . "," . $CountryDescription . "," . $myrow['file_count'] . "\n";								
			}
		$message .= "KPIEND\n";
		
		mail($_EMAIL, $_COUNT . " v12:" . $_FILETAG . "," . $_VERTAG . "," . $_DATEFROM, $message, "From: webmaster@eclipse.org");

		} else {
		while($myrow = mysql_fetch_assoc($rs)) {
			$i++;
			$dl_count +=  $myrow['file_count'];

			if ( $_VIEW == "ccode"){
              $message .= sprintf("%15s", number_format($myrow['file_count'])) . " :  ";
              if ( $myrow['CountryDescription'] == "" )
			    $message .= "Unkown ";
			  else
			    $message .= $myrow['CountryDescription'];  
			  $message .= "(". $myrow['ccode'] .")\n";
			} else {
			  $message .= sprintf("%15s", number_format($myrow['file_count'])) . ": " . $myrow['download_date'] . " " . $myrow['file_name'] . "\n";
			}
		}
		mail($_EMAIL, "Live download stats query results", $message, "From: webmaster@eclipse.org");

		}
	
		$dbc->disconnect();
		exit;
	}
	else {
		$rs 	= mysql_query($sql, $dbh);
		$querytime = microtime_float() - $now;
		if($_DEBUG) {
			echo $sql;
		}
	}	
	include($inc_file);

	
	$dbc->disconnect();
	
	$rs 		= null;
	$rs_info	= null;
	$dbh 		= null;
	$dbc 		= null;
	
	$html = ob_get_contents();
	ob_end_clean();
	$App->generatePage($theme, $Menu, null, $pageAuthor, $pageKeywords, $pageTitle, $html);

	
	function microtime_float()
	{
 		list($usec, $sec) = explode(" ", microtime());
		return ((float)$usec + (float)$sec);
	}

?>