#!/usr/bin/php
<?php
/*
	Analyse log from web server to count rpm usage

	Version: 0.6.1
	Author:  Remi Collet
	License: GPL
	URL:     http://blog.famillecollet.com/

	Usage:   comptage --help

	=== Changelog === 

	23/05/2010	0.6.1	- generate stats.csv
				- bug when access_log have vhost column
				- add --comment option for CSV file
	29/04/2010	0.6	- generate stats.xml
	24/09/2008	0.5	- post %{dist} number
	14/09/2008	0.4	- ppc64
	14/08/2008	0.3	- minor fixes
	13/02/2008	0.2	- enhanced regex for kwizart
	10/02/2008	0.1	- initial
*/
define ("VERSION", "0.6.1");
@include 'Console/Getargs.php';
if (!class_exists("Console_Getargs")) {
	echo "PEAR Console_Getargs extension is required\n";
	die ("=> yum install php-pear-Console-Getargs\n");
}

function TraiteFile ($db, $table, $path, $Verb) {
	$fic=fopen($path, "r");
	if ($fic) {
		while ($tab=fgetcsv($fic, 4000, ' ', '"')) {
                   	if (count($tab)>=10 && $tab[6]==200) {
				# 0:IP, 3:Date, 5:URL, 6:Status, 7:Size, 9:Agent
				list($ordre,$name,$prot)=explode(' ', $tab[5]);
				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
				$time=date("Y-m-d H:i:s", strtotime($time));
				$size=$tab[7];
		   	} else if (count($tab)==11 && $tab[7]==200) {
				# 0:IP, 3:Date, 5:Host, 6:URL, 7:Status, 8:Size, 10:Agent
				list($ordre,$name,$prot)=explode(' ', $tab[6]);
				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
				$time=date("Y-m-d H:i:s", strtotime($time));
				$size=$tab[8];
		   	} else if (count($tab)==8 && $tab[6]==200) {
				# 0:IP, 3:Date, 5:URL, 6:Status, 7:Size
				list($ordre,$name,$prot)=explode(' ', $tab[5]);
				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
				$time=date("Y-m-d H:i:s", strtotime($time));
				$size=$tab[7];
		   	} else if (count($tab)==10 && $tab[7]==200) { 
				# http://mirror.cedia.org.ec/.remilogs/
				# 0:IP, 3:Date, 5:URL, 6:Size, 7:Status
				list($ordre,$name,$prot)=explode(' ', $tab[5]);
				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
				$time=date("Y-m-d H:i:s", strtotime($time));
				$size=$tab[6];
			} else if (count($tab)==9 && $tab[7]==200) {
				# http://ftp.arnes.si/mirrors/remi/.mirrorlogs/
				# 0:CLIENTIP, 4:Date, 6:URL, 8:Size, 7:Status
				list($ordre,$name,$prot)=explode(' ', $tab[6]);
				$time=substr($tab[4],1)." ".substr($tab[5],0,-1);
				$time=date("Y-m-d H:i:s", strtotime($time));
				$size=$tab[8];
			} else if (count($tab)==9 && $tab[6]==200) {
				# http://remi.mirrors.cu.be/.logs/
				# 0:CLIENTIP, 3:Date, 5:URL, 7:Size, 6:Status
				list($ordre,$name,$prot)=explode(' ', $tab[5]);
				$time=substr($tab[3],1)." ".substr($tab[4],0,-1);
				$time=date("Y-m-d H:i:s", strtotime($time));
				$size=$tab[7];
		   	} else {
				if ($Verb>3) {
					echo "Ignored (".count($tab)."):";
					print_r($tab);
				}
				continue;
                   	} 
			if ($Verb>3 && substr($name,-4)==".rpm") echo "+ $ordre: ".$name."\n";

			# 1:name, 2:version, 3:release, 4:distro, 6:repotag 7:arch
			/// if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.([a-z]*)\.(i386|x86_64|ppc|noarch)\.rpm$@',$name,$regs)) {

			$ok=0;
			if ($ordre != "GET") {
				if ($verb>3) echo "+ Ignored (!GET)\n";
				$ok=0;
			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.[a-z\.0-9]*\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
			        $ok=1;
			        $rpm=$regs[1];
			        $ver=$regs[2];
			        $rel=$regs[3];
			        $dis=$regs[4];
			        $arc=$regs[6];
			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.[a-z]*\.((fc|el)[0-9]*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
			        $ok=2;
			        $rpm=$regs[1];
			        $ver=$regs[2];
			        $rel=$regs[3];
			        $dis=$regs[4];
			        $arc=$regs[6];
			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.((fc|el)[0-9]*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
			        $ok=3;
			        $rpm=$regs[1];
			        $ver=$regs[2];
			        $rel=$regs[3];
			        $dis=$regs[4];
			        $arc=$regs[6];
			} else if (preg_match('@^.*/(.*)-(.*)-(.*)\.(i.86|x86_64|ppc64|ppc|noarch|src)\.rpm$@',$name,$regs)) {
			        $ok=4;
			        $rpm=$regs[1];
			        $ver=$regs[2];
			        $rel=$regs[3];
			        $dis="all";
			        $arc=$regs[4];
			} else if ($Verb>3  && substr($name,-4)==".rpm") 
				echo "+ rejected \n";

			if ($ok && $Verb<4) {
				if (strpos($rpm, '%')) $rpm = urldecode($rpm); // ++ in name
				if (strpos($ver, '%')) $ver = urldecode($ver); // ~ in version

				$sql = sprintf("SELECT ref FROM %s WHERE `name`='%s' AND `version`='%s' AND `release`='%s' AND `distro`='%s' AND `arch`='%s'", 
					$table, $rpm, $ver, $rel, $dis, $arc);
				if ($Verb>3) echo "+ SQL: $sql\n";
				$Res=$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
				
				if ($Row=$Res->fetch_object()) {
					$sql = sprintf("UPDATE %s SET cpt=cpt+1, last='%s', size=size+%d WHERE ref=%d", $table, $time, $size, $Row->ref);
					if ($Verb>3) echo "+ SQL: $sql\n";
					$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
				} else {
					$sql = sprintf("INSERT INTO %s SET `name`='%s', `version`='%s', `release`='%s', distro='%s', arch='%s', cpt=1, first='%s', last='%s', size=%d", 
						$table, $rpm, $ver, $rel, $dis, $arc, $time, $time, $tab[7]);
					if ($Verb>3) echo "+ SQL: $sql\n";
					$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
				}
			} else if ($ok)  echo "+ Accepted\n";
		}
		fclose($fic);
	} else {
		echo "Cannot read $path !\n";
	}
}
if (isset($_SERVER["USERNAME"])) $defuser=$_SERVER["USERNAME"]; else $defuser='root';
$config = array(
     "database" => array('short' => 'b', 'max' =>  1, 'min' => 1, 'desc' => "Database name", 'default' => "stats"),
     "comment"  => array('short' => 'x', 'max' =>  1, 'min' => 1, 'desc' => "Comment for CSV file", 'default' => ""),
     "create"   => array('short' => 'c', 'max' =>  0, 'desc' => "Create table"),
     "debug"    => array('short' => 'd', 'max' =>  0, 'desc' => "Very-verbose output"),
     "password" => array('short' => 'p', 'max' =>  1, 'min' => 1, 'desc' => "Password for user on server", 'default' => ""),
     "quiet"    => array('short' => 'q', 'max' =>  0, 'desc' => "Minimal output"),
     "server"   => array('short' => 's', 'max' =>  1, 'min' => 1, 'desc' => "MySQL server name", 'default' => "localhost"),
     "table"    => array('short' => 't', 'max' =>  1, 'min' => 1, 'desc' => "Table name", 'default' => "rpms"),
     "user"     => array('short' => 'u', 'max' =>  1, 'min' => 1, 'desc' => "User login on server", 'default' => $defuser),
     "verbose"  => array('short' => 'v', 'max' =>  0, 'desc' => "Verbose output"),
     CONSOLE_GETARGS_PARAMS => array('min' => 1, 'max' => -1, 'desc' => 'files to analyse.', 'default' => "php://stdin")
     );
$args =& Console_Getargs::factory($config);

if (PEAR::isError($args)) {
	echo "comptage version " . VERSION . "\n";
	if ($args->getCode() === CONSOLE_GETARGS_ERROR_USER)
		echo Console_Getargs::getHelp($config, null, $args->getMessage())."\n";
	else if ($args->getCode() === CONSOLE_GETARGS_HELP)
		echo Console_Getargs::getHelp($config)."\n";
	exit;
}
$Verb=1;
if ($args->getValue('quiet'))   $Verb=0;
if ($args->getValue('verbose')) $Verb=2;
if ($args->getValue('debug'))   $Verb=4;

$Server  = $args->getValue('server');
$User    = $args->getValue('user');
$Pass    = $args->getValue('password');
$Base    = $args->getValue('database');
$Table   = $args->getValue('table');
$Comment = $args->getValue('comment');

if ($Verb>1) {
	echo "comptage version " . VERSION . "\n";
	echo "+ Server   = $Server\n";
	echo "+ User     = $User\n";
	echo "+ Database = $Base\n";
	echo "+ Table    = $Table\n";
}

$db=new mysqli($Server, $User, $Pass, $Base);
if (mysqli_connect_errno()) {
	die ("** Connection failed\n** ".mysqli_connect_error()."\n");
}
if ($args->getValue('create')){
	if ($Verb>1) echo "+ Create table $Base.$Table\n";
	
	$sql="CREATE TABLE IF NOT EXISTS `rpms` (
	  `ref` int(11) NOT NULL AUTO_INCREMENT,
	  `name` varchar(100) COLLATE utf8_unicode_ci NOT NULL,
	  `version` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
	  `release` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
	  `distro` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
	  `arch` varchar(10) COLLATE utf8_unicode_ci NOT NULL,
	  `first` datetime DEFAULT NULL,
	  `last` datetime DEFAULT NULL,
	  `cpt` int(11) NOT NULL DEFAULT '0',
	  `size` int(11) NOT NULL DEFAULT '0',
	  PRIMARY KEY (`ref`),
	  UNIQUE KEY `name` (`name`,`version`,`release`,`distro`,`arch`),
	  KEY `first` (`first`),
	  KEY `last` (`last`),
	  KEY `distro` (`distro`,`arch`),
	  KEY `arch` (`arch`)
	) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;";
	$db->query($sql) or die ("** SQL error\n** ".$db->error."\n");	
}

$db->autocommit(TRUE);

$files=$args->getValue('parameters');
if (is_array($files)) foreach ($files as $file) {
	if ($Verb>1) echo "+ File     = $file\n";
	TraiteFile($db, $Table, $file, $Verb);
} else {
	if ($Verb>1) echo "+ File     = $files\n";
	TraiteFile($db, $Table, $files, $Verb);
}

// Compute total number of RPM download
if ($res = $db->query("SELECT SUM( cpt ) AS RPM, NOW( ) AS Date FROM `rpms`")) {
	$msg ="<?xml version='1.0' standalone='yes'?>\n<stats>\n";
	if ($row = $res->fetch_assoc()) {
		$msg .= "\t<RPM type='counter' subtype='small'>".$row['RPM']."</RPM>\n";
		$msg .= "\t<Date type='datetime'>".$row['Date']."</Date>\n";
	}
	$msg .= "</stats>\n";

	// Record last value to XML file
	file_put_contents('stats.xml', $msg);

	// Record value to CSV file (history)
	error_log($row['Date'].';'.$row['RPM'].";$Comment\n",3,'stats.csv');
}
$db->close();
?>