Revision: 339 http://opencsw.svn.sourceforge.net/opencsw/?rev=339&view=rev Author: wbonnet Date: 2011-02-27 16:44:57 +0000 (Sun, 27 Feb 2011)
Log Message: ----------- Add Statistics generation under web tree Added Paths: ----------- web/wordpresscsw/tools/statistics/db-common.php web/wordpresscsw/tools/statistics/db-config.php.sample web/wordpresscsw/tools/statistics/monthlyPackageCount.php web/wordpresscsw/tools/statistics/monthlyPackageCreationUpdate.php Copied: web/wordpresscsw/tools/statistics/db-common.php (from rev 321, utilities/packagesStatistics/tools/db-common.php) =================================================================== --- web/wordpresscsw/tools/statistics/db-common.php (rev 0) +++ web/wordpresscsw/tools/statistics/db-common.php 2011-02-27 16:44:57 UTC (rev 339) @@ -0,0 +1,310 @@ +<?php +/* + * The contents of this file are subject to the COMMON DEVELOPMENT AND + * DISTRIBUTION LICENSE (CDDL) (the "License"); you may not use this + * file except in compliance with the License. + * + * Software distributed under the License is distributed on an "AS IS" basis, + * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License + * for the specific language governing rights and limitations under the + * License. + * + * Alternatively, the contents of this file may be used under the terms of + * either the GNU General Public License Version 3 or later (the "GPL"), + * in which case the provisions of the GPL are applicable instead + * of those above. If you wish to allow use of your version of this file only + * under the terms of either the GPL, and not to allow others to + * use your version of this file under the terms of the CDDL, indicate your + * decision by deleting the provisions above and replace them with the notice + * and other provisions required by the GPL. If you do not delete + * the provisions above, a recipient may use your version of this file under + * the terms of any one of the CDDL, or the GPL. + + * Copyright 2009 OpenCSW (http://www.opencsw.org). All rights reserved. + * Use is subject to license terms. + * + * + * Contributors list : + * + * William Bonnet wbon...@opencsw.org + * + * + * Version : + * + * $Id: $ + * + */ + +// Includes db connection parameters (username, password, etc.) +require("db-config.php"); + +// +// Class mysqlDAO +// +// provides basic DAO functions for mySQL +// +class mysqlDAO { + + // Members variables + var $dbName; + var $dbHost; + var $dbUser; + var $dbPassword; + var $dbCnx; + var $linkDB; + var $result; + + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Constructeur de la classe. + // Appelé automatiquement à l'instanciation de la classe. + // + // ----------------------------------------------------------------------------------------------------------------------- + function mysqlDAO() { + $this->dbName = DB_SCHEMA; + $this->dbHost = DB_HOST; + $this->dbUser = DB_USER; + $this->dbPassword = DB_PASSWORD; + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Connect to database + // + // ----------------------------------------------------------------------------------------------------------------------- + function connect() { + + // Connect to database + $this->linkDB = mysql_connect($this->dbHost, $this->dbUser, $this->dbPassword) + or die("Unable to connect to db : " . mysql_error()); + + // Select default database + $this->dbCnx = mysql_select_db($this->dbName, $this->linkDB); + if (!$this->dbCnx) { + die ('Unable to use database ' . $this->dbName . ' : ' . mysql_error()); + } + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Disconnect from database + // + // ----------------------------------------------------------------------------------------------------------------------- + function disconnect() { + mysql_close($this->linkDB); + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Query execution + // + // ----------------------------------------------------------------------------------------------------------------------- + function execQuery($sqlQuery) { + + // Execute the query + $this->result = mysql_query(utf8_encode($sqlQuery), $this->linkDB); + + // Check results. Display query and error + if (!$this->result) { + $message = 'Invalid query : ' . mysql_error() . "\n"; + $message .= 'Query used : ' . $sqlQuery; + die($message); + } + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Increase the number of download for a given package + // + // ----------------------------------------------------------------------------------------------------------------------- + function increasePackageDownloadCounter($packageName, $packageOsVersion, $packageArch, $targetOsVersion, $packageSource, $columnName) { + + // Get the current date components + $currentDay = date('d'); // Retrieve current day + $currentMonth = date('m'); // Retrieve current month + $currentYear = date('Y'); // Retrieve current year + + // Check that the package exist in the catalog. This is used to prevent from being polluted by non existing packages + $sqlQuery = "select ID_PKG from PKG_STATS_PACKAGES where PKG_CATALOGNAME = '" . mysql_real_escape_string($packageName) . "'" ; + + // Execute the query to retrieve the row to update + $this->execQuery($sqlQuery); + + // Check if there is a result, if no return because this package does not exist + if (! $row = mysql_fetch_array($this->result)) { +echo "Package $packageName does not exist<br>"; + return; + } + + // Define the query used to retrieve the packages from the database + $sqlQuery = "select ID_DWN, $columnName from PKG_STATS_DOWNLOADS where DWN_YEAR = '$currentYear' and DWN_MONTH = '$currentMonth' " ; + $sqlQuery .= "and DWN_DAY = '$currentDay' and DWN_NAME = '" . mysql_real_escape_string($packageName) . "' " ; + $sqlQuery .= "and DWN_ARCH = '" . mysql_real_escape_string($packageArch) . "' " ; + $sqlQuery .= "and DWN_PKG_OS_VERSION = '" . mysql_real_escape_string($packageOsVersion) . "' " ; + $sqlQuery .= "and DWN_TARGET_OS_VERSION = '" . mysql_real_escape_string($targetOsVersion) . "' " ; + $sqlQuery .= "and DWN_SOURCE = '" . mysql_real_escape_string($packageSource) . "' " ; + + // Execute the query to retrieve the row to update + $this->execQuery($sqlQuery); + + // Check if there is a result + if ($row = mysql_fetch_array($this->result)) { + // Yes there is a record, thus update it + $id = $row[0]; // Retrieve the id of the record + $downloadCount = $row[1]; // Retrieve the current counter value + + $downloadCount++; // Increment download count + + // Generate the query used to update the record + $sqlQuery = "update PKG_STATS_DOWNLOADS set $columnName = '$downloadCount' where ID_DWN = '$id'"; + + // Execute the query + $this->execQuery($sqlQuery); + + } else { + + // No row means no resultats, thus we need to insert a record in the database + $sqlQuery = "insert into PKG_STATS_DOWNLOADS (DWN_NAME, DWN_PKG_OS_VERSION, DWN_TARGET_OS_VERSION, DWN_SOURCE, DWN_ARCH, DWN_YEAR, DWN_MONTH, DWN_DAY, $columnName ) "; + $sqlQuery .= " values ('" . mysql_real_escape_string($packageName) . "', '" ; + $sqlQuery .= mysql_real_escape_string($packageOsVersion) . "', '" ; + $sqlQuery .= mysql_real_escape_string($targetOsVersion) . "', '" ; + $sqlQuery .= mysql_real_escape_string($packageSource) . "', '" ; + $sqlQuery .= mysql_real_escape_string($packageArch) . "', " ; + $sqlQuery .= "'$currentYear', '$currentMonth', '$currentDay', 1)" ; + + // Execute the query + $this->execQuery($sqlQuery); + } + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Increase the number of installation for a given package "because it was directly downloaded" + // + // ----------------------------------------------------------------------------------------------------------------------- + function increasePackageSelectCounter($packageName, $packageOsVersion, $packageArch, $targetOsVersion, $packageSource) { + + // Call the function that will modify data in the db with the name of the column to inc + // Define the query used to retrieve the packages from the database + $this->increasePackageDownloadCounter($packageName, $packageOsVersion, $packageArch, $targetOsVersion, $packageSource, "DWN_SELECT_COUNT"); + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Increase the number of installation for a given package "because it was a dependency" + // + // ----------------------------------------------------------------------------------------------------------------------- + function increasePackageFetchCounter($packageName, $packageOsVersion, $packageArch, $targetOsVersion, $packageSource) { + + // Call the function that will modify data in the db with the name of the column to inc + // Define the query used to retrieve the packages from the database + $this->increasePackageDownloadCounter($packageName, $packageOsVersion, $packageArch, $targetOsVersion, $packageSource, "DWN_FETCH_COUNT"); + } + + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Increase the number of download from a given mirror + // + // ----------------------------------------------------------------------------------------------------------------------- + function increaseMirrorDownloadCounter($mirrorName, $mirrorArch, $mirrorOsVersion, $mirrorSource) { + + // Get the current date components + $currentDay = date('d'); // Retrieve current day + $currentMonth = date('m'); // Retrieve current month + $currentYear = date('Y'); // Retrieve current year + + // Define the query used to retrieve the packages from the database + $sqlQuery = "select ID_MIR, MIR_COUNT from PKG_STATS_MIRRORS where MIR_YEAR = '$currentYear' "; + $sqlQuery .= "and MIR_MONTH = '$currentMonth' " ; + $sqlQuery .= "and MIR_DAY = '$currentDay' " ; + $sqlQuery .= "and MIR_URL = '" . mysql_real_escape_string($mirrorName) . "' " ; + $sqlQuery .= "and MIR_ARCH = '" . mysql_real_escape_string($mirrorArch) . "' " ; + $sqlQuery .= "and MIR_SOURCE = '" . mysql_real_escape_string($mirrorSource) . "' " ; + $sqlQuery .= "and MIR_OS_VERSION = '" . mysql_real_escape_string($mirrorOsVersion) . "' " ; + + // Execute the query to retrieve the row to update + $this->execQuery($sqlQuery); + + // Check if there is a result + if ($row = mysql_fetch_array($this->result)) { + // Yes there is a record, thus update it + $id = $row[0]; // Retrieve the id of the record + $downloadCount = $row[1]; // Retrieve the current counter value + + $downloadCount++; // Increment download count + + // Generate the query used to update the record + $sqlQuery = "update PKG_STATS_MIRRORS set MIR_COUNT = '$downloadCount' where ID_MIR = '$id'"; + + // Execute the query + $this->execQuery($sqlQuery); + + } else { + + // No row means no resultats, thus we need to insert a record in the database + $sqlQuery = "insert into PKG_STATS_MIRRORS (MIR_URL, MIR_YEAR, MIR_MONTH, MIR_DAY, MIR_COUNT, "; + $sqlQuery .= "MIR_ARCH, MIR_SOURCE, MIR_OS_VERSION ) "; + $sqlQuery .= " values ('" . mysql_real_escape_string($mirrorName) ."', "; + $sqlQuery .= "'$currentYear', '$currentMonth', '$currentDay', 1," ; + $sqlQuery .= "'" . mysql_real_escape_string($mirrorArch) ."'," ; + $sqlQuery .= "'" . mysql_real_escape_string($mirrorSource) ."'," ; + $sqlQuery .= "'" . mysql_real_escape_string($mirrorOsVersion) ."')" ; + + // Execute the query + $this->execQuery($sqlQuery); + } + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Get the monthly number of packages creations and updates for the last 12 rolling months + // + // ----------------------------------------------------------------------------------------------------------------------- + function getMonthlyPackageCreationAndUpdateOverLastYear() { + + // Initialize the variables + $statCreationUpdate = array(); // array of data that will be returned to caller + $statIndex = 0; // current index + + // Define the query used to retrieve the packages from the database + $sqlQuery = "select STAT_CREATION_COUNT, STAT_UPDATE_COUNT, STAT_MONTH, STAT_YEAR from PKG_STATS_STATISTICS order by id_stat desc limit 0,12;" ; + + // Execute the query to retrieve the row to update + $this->execQuery($sqlQuery); + + // Check if there is a result + while ($row = mysql_fetch_array($this->result)) { + $statCreationUpdate[$statIndex++] = $row; // Yes there is a record, thus copy it to the array + } + + return $statCreationUpdate ; + } + + // ----------------------------------------------------------------------------------------------------------------------- + // + // Get the monthly number of available packages for the last 12 rolling months + // + // ----------------------------------------------------------------------------------------------------------------------- + function getMonthlyPackageCountOverLastYear() { + + // Initialize the variables + $statCreationUpdate = array(); // array of data that will be returned to caller + $statIndex = 0; // current index + + // Define the query used to retrieve the packages from the database + $sqlQuery = "select STAT_PACKAGE_COUNT, STAT_MONTH, STAT_YEAR from PKG_STATS_STATISTICS order by id_stat desc limit 0,12;" ; + + // Execute the query to retrieve the row to update + $this->execQuery($sqlQuery); + + // Check if there is a result + while ($row = mysql_fetch_array($this->result)) { + $statCreationUpdate[$statIndex++] = $row; // Yes there is a record, thus copy it to the array + } + + return $statCreationUpdate ; + } +} +?> Copied: web/wordpresscsw/tools/statistics/db-config.php.sample (from rev 321, utilities/packagesStatistics/tools/db-config.php) =================================================================== --- web/wordpresscsw/tools/statistics/db-config.php.sample (rev 0) +++ web/wordpresscsw/tools/statistics/db-config.php.sample 2011-02-27 16:44:57 UTC (rev 339) @@ -0,0 +1,42 @@ +<?php + /* + * The contents of this file are subject to the COMMON DEVELOPMENT AND + * DISTRIBUTION LICENSE (CDDL) (the "License"); you may not use this + * file except in compliance with the License. + * + * Software distributed under the License is distributed on an "AS IS" basis, + * WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License + * for the specific language governing rights and limitations under the + * License. + * + * Alternatively, the contents of this file may be used under the terms of + * either the GNU General Public License Version 3 or later (the "GPL"), + * in which case the provisions of the GPL are applicable instead + * of those above. If you wish to allow use of your version of this file only + * under the terms of either the GPL, and not to allow others to + * use your version of this file under the terms of the CDDL, indicate your + * decision by deleting the provisions above and replace them with the notice + * and other provisions required by the GPL. If you do not delete + * the provisions above, a recipient may use your version of this file under + * the terms of any one of the CDDL, or the GPL. + * + * Copyright 2009 OpenCSW (http://www.opencsw.org). All rights reserved. + * Use is subject to license terms. + * + * + * Contributors list : + * + * William Bonnet wbon...@opencsw.org + * + * + * Version : + * + * $Id: $ + * + */ + + define('DB_SCHEMA', 'schema'); // Defines the database name + define('DB_USER', 'user'); // Defines the user name + define('DB_PASSWORD', 'password'); // Defines the password + define('DB_HOST', 'localhost'); // Defines the hostnameof the database +?> Copied: web/wordpresscsw/tools/statistics/monthlyPackageCount.php (from rev 321, utilities/packagesStatistics/tools/monthlyPackageCount.php) =================================================================== --- web/wordpresscsw/tools/statistics/monthlyPackageCount.php (rev 0) +++ web/wordpresscsw/tools/statistics/monthlyPackageCount.php 2011-02-27 16:44:57 UTC (rev 339) @@ -0,0 +1,88 @@ +<?php + + // Includes the graphics library + require_once "Artichow/LinePlot.class.php"; + + // Include the database parameters + require("db-common.php"); + + // Defines the function to call for label output + function setMonth($value) { + global $stats; + + date_default_timezone_set('Europe/Paris'); + $str = date('M', mktime(0,0,0, $stats[11 - $value][1], 1)) . " '"; + $str .= date('y', mktime(0,0,0,0,1, $stats[11 - $value][2] + 1)); + + return $str; + } + + function setNumber($value) { + return round($value); + } + + function labelFormat($value) { + return $value; + } + + // Initialization of the graphics + $graph = new Graph(800, 480); + $graph->setAntiAliasing(FALSE); + + $group = new PlotGroup; + $group->grid->setType(Line::DASHED); + + $group->setPadding(40, NULL, 20, NULL); + + $group->axis->left->setLabelNumber(8); + $group->axis->left->setLabelPrecision(1); + $group->axis->left->setTickStyle(Tick::OUT); + + // Statistics array population + $statPackage = array(); // Initialize the number of packages array + + // Create the DAO object + $statDAO = new mysqlDAO(); + + // Connect to database + $statDAO->connect(); + + // Retrieve the statistics from the database + $stats = $statDAO->getMonthlyPackageCountOverLastYear(); + + // Disconnect from database + $statDAO->disconnect(); + + for ($idxCounter = 11 ; $idxCounter >= 0 ; $idxCounter--) { // Loop used to copy data + $statPackage[$idxCounter] = $stats[11 - $idxCounter][0]; // into the creation array + } + + $plot = new LinePlot($statPackage); + + $plot->label->setFont(new Tuffy(7)); + $plot->label->setPadding(1, 1, 1, 1); + $plot->label->setCallbackFunction("labelFormat"); + $plot->label->setBackgroundColor( + new Color(227, 223, 241, 15) + ); + $plot->setColor(new Green()); + $plot->setFillColor(new LightGreen(80)); + + $plot->mark->setType(Mark::CIRCLE); + $plot->mark->setFill(new MidGreen); + $plot->mark->setSize(6); + + $group->legend->add($plot, "Number of available packages", Legend::MARK); + $group->legend->setPosition(0.35, 0.15); + $group->add($plot); + + $x = array(NULL, NULL, NULL, 10, 12, 14, 18, 26, 42); + + $group->axis->bottom->label->setCallbackFunction('setMonth'); + $group->axis->left->label->setCallbackFunction('setNumber'); + + $graph->add($group); + + $graph->draw(); + +?> Copied: web/wordpresscsw/tools/statistics/monthlyPackageCreationUpdate.php (from rev 321, utilities/packagesStatistics/tools/monthlyPackageCreationUpdate.php) =================================================================== --- web/wordpresscsw/tools/statistics/monthlyPackageCreationUpdate.php (rev 0) +++ web/wordpresscsw/tools/statistics/monthlyPackageCreationUpdate.php 2011-02-27 16:44:57 UTC (rev 339) @@ -0,0 +1,139 @@ +<?php + + // Includes the graphics library + require_once "Artichow/BarPlot.class.php"; + + // Include the database parameters + require("db-common.php"); + + // Defines the function to call for label output + function labelFormat($value) { + return $value; + } + + function setMonth($value) { + global $stats; + + date_default_timezone_set('Europe/Paris'); + $str = date('M', mktime(0,0,0, $stats[11 - $value][2], 1)) . " '"; + $str .= date('y', mktime(0,0,0,0,1, $stats[11 - $value][3] + 1)); + + return $str; + } + + // Initialization of the graphics + $graph = new Graph(800, 480); + $graph->setAntiAliasing(FALSE); + + $group = new PlotGroup; + $group->setSpace(5, 5, 5, 0); + $group->setPadding(40, 20); + + $group->axis->left->setLabelPrecision(0); + + $colors = array( + new Color(80, 105, 190, 10), + new Color(105, 190, 80, 10) + ); + + $darkColor = array( + new Color(40, 55, 120, 10), + new Color(55, 120, 40, 10) + ); + + $axis = array( + Plot::LEFT, + ); + + $group->axis->left->setColor($darkColor[0]); + $group->axis->left->label->setColor($darkColor[0]); + + $group->setBackgroundGradient( + new LinearGradient( + new Color(225, 225, 225), + new Color(255, 255, 255), + 0 + ) + ); + + // Statistics array population + $statCreation = array(); // Initialize the creation data array + $statUpdate = array(); // Initialize the update data array + + // Create the DAO object + $statDAO = new mysqlDAO(); + + // Connect to database + $statDAO->connect(); + + // Retrieve the statistics from the database + $stats = $statDAO->getMonthlyPackageCreationAndUpdateOverLastYear(); + + // Disconnect from database + $statDAO->disconnect(); + + for ($idxCounter = 11 ; $idxCounter >= 0 ; $idxCounter--) { // Loop used to copy data + $statCreation[$idxCounter] = $stats[11 - $idxCounter][0]; // into the creation array + $statUpdate[$idxCounter] = $stats[11 - $idxCounter][1]; // into the update array + } + + // Add the "creations" serie on the graphic + $plot = new BarPlot($statCreation, 1, 2); + $plot->barBorder->setColor(new Color(0, 0, 0, 30)); + + $plot->setBarPadding(0.1, 0.1); + $plot->setBarSpace(5); + + $plot->barShadow->setSize(2); + $plot->barShadow->setPosition(Shadow::RIGHT_TOP); + $plot->barShadow->setColor(new Color(180, 180, 180, 10)); + $plot->barShadow->smooth(TRUE); + + $plot->label->set($statCreation); + $plot->label->move(0, -6); + $plot->label->setFont(new Tuffy(7)); + $plot->label->setAngle(0); + $plot->label->setAlign(NULL, Label::TOP); + $plot->label->setPadding(3, 1, 0, 6); + $plot->label->setCallbackFunction("labelFormat"); + + $plot->setBarColor($colors[0]); + $plot->setYAxis($axis[0]); + + $group->legend->add($plot, "Packages creations", Legend::BACKGROUND); + $group->legend->setPosition(0.30, 0.15); + $group->legend->shadow->smooth(TRUE); + + $group->add($plot); + + // Add the "updates" serie on the graphic + $plot = new BarPlot($statUpdate, 2, 2); + $plot->barBorder->setColor(new Color(0, 0, 0, 30)); + + $plot->setBarPadding(0.1, 0.1); + $plot->setBarSpace(5); + + $plot->barShadow->setSize(2); + $plot->barShadow->setPosition(Shadow::RIGHT_TOP); + $plot->barShadow->setColor(new Color(180, 180, 180, 10)); + $plot->barShadow->smooth(TRUE); + + $plot->label->set($statUpdate); + $plot->label->move(0, -6); + $plot->label->setFont(new Tuffy(7)); + $plot->label->setAngle(0); + $plot->label->setAlign(NULL, Label::TOP); + $plot->label->setPadding(3, 1, 0, 6); + $plot->label->setCallbackFunction("labelFormat"); + + $plot->setBarColor($colors[1]); + $plot->setYAxis($axis[0]); + + $group->legend->add($plot, "Packages updates", Legend::BACKGROUND); + $group->add($plot); + + $group->axis->bottom->label->setCallbackFunction('setMonth'); + + $graph->add($group); + $graph->draw(); +?> This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. _______________________________________________ devel mailing list devel@lists.opencsw.org https://lists.opencsw.org/mailman/listinfo/devel