CONTENIDO Forum

Code: Alles auswählen

Changes to add Daily Stats
---------------------------------------------------------------------
** Helpful Stuff
    * Plugin Cronjobs Overview - https://docs.contenido.org/display/COND/Cronjobs

** Further considerations
    * This is not yet visible in Backend Stat View...
    * Now the number of lines in the stat_archive table can increase by factor 30 because ever day 
      of the month, when a article is opend, a new line is added to stat table and afterwards archived
      - Options:
          - Just keep it
          - Remove the not visited '0' entrys -> my cause problems in Backend Stat View?
          - Only keep the sum of visits when archiving the stats
    
** Files Modified:
    * CONTENIDO_4.9.4\contenido\classes\contenido\class.stat.php
    * CONTENIDO_4.9.4\contenido\includes\functions.stat.php
    
** Changes per File
    * CONTENIDO_4.9.4\contenido\classes\contenido\class.stat.php
      - Line 49: Change function call FROM: fetchByCatArtAndLang TO: fetchByCatArtAndLangAndDate
      - Line 68: Add var $visitDate
      - Line 76: Add  $oItem->set('visitdate', $visitDate); to set the column value for the INSERT
      - Line 94 to 108: Add function fetchByCatArtAndLangAndDate

    * CONTENIDO_4.9.4\contenido\includes\functions.stat.php
      - Line 98: changed FROM: (idcatart, idlang, idclient, visited) TO: (idcatart, idlang, idclient, visited, visitdate)
      - Line 103: Added: " . cSecurity::toInteger('0') . ",
      - Line 104: changed FROM: '0000-00-00 00:00:00')"; TO: '" . $visitDate . "')"; to set the current date when recreating the blank statistics

Code: Alles auswählen

<?php
/**
 * This file contains the stat collection and item class.
 *
 * @package Core
 * @subpackage GenericDB_Model
 * @version SVN Revision $Rev:$
 *
 * @author Murat Purc <murat@purc.de>
 * @copyright four for business AG <www.4fb.de>
 * @license http://www.contenido.org/license/LIZENZ.txt
 * @link http://www.4fb.de
 * @link http://www.contenido.org
 */

defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');

/**
 * Statistic collection
 *
 * @package Core
 * @subpackage GenericDB_Model
 */
class cApiStatCollection extends ItemCollection {

    /**
     * Constructor
     */
    public function __construct() {
        global $cfg;
        parent::__construct($cfg['tab']['stat'], 'idstat');
        $this->_setItemClass('cApiStat');

        // set the join partners so that joins can be used via link() method
        $this->_setJoinPartner('cApiCategoryArticleCollection');
        $this->_setJoinPartner('cApiLanguageCollection');
        $this->_setJoinPartner('cApiClientCollection');
    }

    /**
     * Tracks a visit.
     * Increments a existing entry or creates a new one.
     *
     * @param int $iIdCatArt
     * @param int $iIdLang
     * @param int $iIdClient
     */
    public function trackVisit($iIdCatArt, $iIdLang, $iIdClient) {
        $oStat = $this->fetchByCatArtAndLangAndDate($iIdCatArt, $iIdLang); // Change this call from fetchByCatArtAndLang to fetchByCatArtAndLangAndDate
        if (is_object($oStat)) {
            $oStat->increment();
        } else {
            $this->create($iIdCatArt, $iIdLang, $iIdClient);
        }
    }

    /**
     * Creates a stat entry.
     *
     * @param int $iIdCatArt
     * @param int $iIdLang
     * @param int $iIdClient
     * @param int $iVisited
     * @return cApiStat
     */
    // Function edited to add visitdate
    public function create($iIdCatArt, $iIdLang, $iIdClient, $iVisited = 1) {
        $visitDate = date('Ymd') . '000000'; // Added this line
        
        $oItem = parent::createNewItem();

        $oItem->set('visited', (int) $iVisited);
        $oItem->set('idcatart', (int) $iIdCatArt);
        $oItem->set('idlang', (int) $iIdLang);
        $oItem->set('idclient', (int) $iIdClient);
        $oItem->set('visitdate', $visitDate);  // Added this line
        $oItem->store();

        return $oItem;
    }

    /**
     * Returns a stat entry by category article and language.
     *
     * @param int $iIdCatArt
     * @param int $iIdLang
     * @return cApiStat NULL
     */
    public function fetchByCatArtAndLang($iIdCatArt, $iIdLang) {
        $this->select('idcatart=' . (int) $iIdCatArt . ' AND idlang=' . (int) $iIdLang);
        return $this->next();
    }

    /**
     * Returns a stat entry by category article, language and date.
     *
     * @param int $iIdCatArt
     * @param int $iIdLang
     * @return cApiStat NULL
     */
    // The visitdate column in con_stat is varchar(14), the following date format YYYYMMDDHHMMSS fits
    // The time part will always be 000000 because this is an a day base
    // using MySQL date should be prefered
    public function fetchByCatArtAndLangAndDate($iIdCatArt, $iIdLang) {
        $visitDate = date('Ymd') . '000000';
        $this->select('idcatart=' . (int) $iIdCatArt . ' AND idlang=' . (int) $iIdLang . ' AND visitdate=' . $visitDate);
        return $this->next();
    }

    /**
     * Deletes statistics entries by category article id and language id.
     *
     * @param int $idcatart
     * @param int $idlang
     * @return int Number of deleted items
     */
    public function deleteByCategoryArticleAndLanguage($idcatart, $idlang) {
        $where = 'idcatart = ' . (int) $idcatart . ' AND idlang = ' . (int) $idlang;
        return $this->deleteByWhereClause($where);
    }
}

/**
 * Statistic item
 *
 * @package Core
 * @subpackage GenericDB_Model
 */
class cApiStat extends Item {

    /**
     * Constructor Function
     *
     * @param mixed $mId Specifies the ID of item to load
     */
    public function __construct($mId = false) {
        global $cfg;
        parent::__construct($cfg['tab']['stat'], 'idstat');
        $this->setFilters(array(), array());
        if ($mId !== false) {
            $this->loadByPrimaryKey($mId);
        }
    }

    /**
     * Increment and store property 'visited'.
     */
    public function increment() {
        $this->set('visited', $this->get('visited') + 1);
        $this->store();
    }
    
	/**
     * Userdefined setter for stat fields.
     *
     * @param string $name
     * @param mixed $value
     * @param bool $bSafe Flag to run defined inFilter on passed value
     */
    public function setField($name, $value, $bSafe = true) {
        switch ($name) {
            case 'visited':
                $value = (int) $value;
                break;
			case 'idcatart':
                $value = (int) $value;
                break;
			case 'idlang':
                $value = (int) $value;
                break;
			case 'idclient':
                $value = (int) $value;
                break;
        }

        return parent::setField($name, $value, $bSafe);
    }
    
}

Code: Alles auswählen

<?php
/**
 * This file contains the CONTENIDO statistic functions.
 *
 * @package          Core
 * @subpackage       Backend
 * @version          SVN Revision $Rev:$
 *
 * @author           Olaf Niemann
 * @copyright        four for business AG <www.4fb.de>
 * @license          http://www.contenido.org/license/LIZENZ.txt
 * @link             http://www.4fb.de
 * @link             http://www.contenido.org
 */

defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');

cInclude("includes", "functions.database.php");

/**
 * Displays statistic information layer (a div Tag)
 *
 * @param   int     $id    Either article or directory id
 * @param   string  $type  The type
 * @param   int     $x     Style top position
 * @param   int     $y     Style left position
 * @param   int     $w     Style width
 * @param   int     $h     Style height
 * @return  string  Composed info layer
 */
function statsDisplayInfo($id, $type, $x, $y, $w, $h) {
    if (strcmp($type, "article" == 0)) {
        $text = i18n("Info about article") . " " . $id;
    } else {
        $text = i18n("Info about directory") . " " . $id;
    }

    $div = new cHTMLDiv($text, "text_medium", "idElement14");
    $div->appenStyleDefinition("border", "1px solid #e8e8ee");
    $div->appendStyleDefinition("position", "absolute");
    $div->appendStyleDefinition("top", $x . "px");
    $div->appendStyleDefinition("left", $y . "px");
    $div->appendStyleDefinition("width", $w . "px");
    $div->appendStyleDefinition("height", $h . "px");

    return $div->toHTML();
}

/**
 * Archives the current statistics
 *
 * @param $yearmonth String with the desired archive date (YYYYMM)
 */
function statsArchive($yearmonth) {
    global $cfg;

    $yearmonth = preg_replace('/\s/', '0', $yearmonth);

    $db = cRegistry::getDb();
    $db2 = cRegistry::getDb();

    $sql = "SELECT idcatart, idlang, idclient, visited, visitdate FROM " . $cfg["tab"]["stat"];

    $db->query($sql);

    while ($db->nextRecord()) {
        $insertSQL = "INSERT INTO
                          " . $cfg["tab"]["stat_archive"] . "
                          (archived, idcatart, idlang, idclient, visited, visitdate)
                      VALUES
                          (
                           " . $yearmonth . ",
                           " . cSecurity::toInteger($db->f(0)) . ",
                           " . cSecurity::toInteger($db->f(1)) . ",
                           " . cSecurity::toInteger($db->f(2)) . ",
                           " . cSecurity::toInteger($db->f(3)) . ",
                           '" . $db2->escape($db->f(4)) . "')";

        $db2->query($insertSQL);
    }

    $sql = "DELETE FROM " . $cfg["tab"]["stat"];
    $db->query($sql);

    // Recreate empty stats
    $sql = "SELECT
                A.idcatart, B.idclient, C.idlang
            FROM
                " . $cfg["tab"]["cat_art"] . " AS A INNER JOIN
                " . $cfg["tab"]["cat"] . " AS B ON A.idcat = B.idcat INNER JOIN
                " . $cfg["tab"]["cat_lang"] . " AS C ON A.idcat = C.idcat ";

    $db->query($sql);
    $visitDate = date('Ymd') . '000000'; // Added this line
    while ($db->nextRecord()) {
        $insertSQL = "INSERT INTO
                          " . $cfg["tab"]["stat"] . "
                          (idcatart, idlang, idclient, visited, visitdate)
                      VALUES (
                          " . cSecurity::toInteger($db->f(0)) . ",
                          " . cSecurity::toInteger($db->f(2)) . ",
                          " . cSecurity::toInteger($db->f(1)) . ",
                          " . cSecurity::toInteger('0') . ",
                          '" . $visitDate . "')"; // changed this Line from: '0000-00-00 00:00:00')"; to: '" . $visitDate . "')";

        $db2->query($insertSQL);
    }
}

/**
 * Generates a statistics page
 *
 * @param $yearmonth  Specifies the year and month from which to retrieve the
 *                    statistics, specify "current" to retrieve the current
 *                    entries
 */
function statsOverviewAll($yearmonth) {
    global $cfg, $db, $tpl, $client, $lang, $cfgClient;

    $sDisplay = 'table-row';

    $bUseHeapTable = $cfg["statistics_heap_table"];

    $sHeapTable = $cfg['tab']['stat_heap_table'];

    if ($bUseHeapTable) {
        if (!dbTableExists($db, $sHeapTable)) {
            buildHeapTable($sHeapTable, $db);
        }
    }

    if (preg_match('/MSIE/', $_SERVER['HTTP_USER_AGENT'])) {
        $sDisplay = 'block';
    }

    $sql = "SELECT
                    idtree, A.idcat, level, preid, C.name, visible
                FROM
                    " . $cfg["tab"]["cat_tree"] . " AS A,
                    " . $cfg["tab"]["cat"] . " AS B,
                    " . $cfg["tab"]["cat_lang"] . " AS C
                WHERE
                    A.idcat=B.idcat AND
                    B.idcat=C.idcat AND
                    C.idlang=" . cSecurity::toInteger($lang) . " AND
                    B.idclient=" . cSecurity::toInteger($client) . "
                ORDER BY idtree";

    $db->query($sql);

    $currentRow = 2;

    $aRowname = array();
    $iLevel = 0;
    $backendUrl = cRegistry::getBackendUrl();
    $tpl->set('s', 'IMG_EXPAND', $backendUrl . $cfg['path']['images'] . 'open_all.gif');
    $tpl->set('s', 'IMG_COLLAPSE', $backendUrl . $cfg['path']['images'] . 'close_all.gif');

    $sumNumberOfArticles = 0;

    while ($db->nextRecord()) {
        if ($db->f("level") == 0 && $db->f("preid") != 0) {
            $tpl->set('d', 'PADDING_LEFT', '10');
            $tpl->set('d', 'TEXT', '&nbsp;');
            $tpl->set('d', 'NUMBEROFARTICLES', '');
            $tpl->set('d', 'TOTAL', '');
            $tpl->set('d', 'ICON', '');
            $tpl->set('d', 'STATUS', '');
            $tpl->set('d', 'ONCLICK', '');
            $tpl->set('d', 'ROWNAME', '');
            $tpl->set('d', 'INTHISLANGUAGE', '');
            $tpl->set('d', 'EXPAND', '');
            $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
            $tpl->set('d', 'PATH', '');
            $tpl->set('d', 'ULR_TO_PAGE', '');

            $tpl->next();
            $currentRow++;
        }

        $padding_left = 10 + (15 * $db->f("level"));
        $text = $db->f(4);
        $idcat = $db->f("idcat");
        $bCatVisible = $db->f("visible");

        if ($db->f("level") < $iLevel) {
            $iDistance = $iLevel - $db->f("level");

            for ($i = 0; $i < $iDistance; $i++) {
                array_pop($aRowname);
            }
            $iLevel = $db->f("level");
        }

        if ($db->f("level") >= $iLevel) {
            if ($db->f("level") == $iLevel) {
                array_pop($aRowname);
            } else {
                $iLevel = $db->f("level");
            }
            array_push($aRowname, $idcat);
        }

        $db2 = cRegistry::getDb();
        //************** number of arts **************
        $sql = "SELECT COUNT(*) FROM " . $cfg["tab"]["cat_art"] . " WHERE idcat=" . cSecurity::toInteger($idcat);
        $db2->query($sql);
        $db2->nextRecord();

        $numberOfArticles = $db2->f(0);
        $sumNumberOfArticles += $numberOfArticles;
        //************** hits of category total**************
        if (strcmp($yearmonth, "current") == 0) {
            $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . " AND B.idclient=" . cSecurity::toInteger($client);
        } else {
            if (!$bUseHeapTable) {
                $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                        AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db2->escape($yearmonth) . "'";
            } else {
                $sql = "SELECT SUM(visited) FROM " . $db2->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . "
                        AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db2->escape($yearmonth) . "'";
            }
        }
        $db2->query($sql);
        $db2->nextRecord();

        $total = $db2->f(0);

        //************** hits of category in this language ***************
        if (strcmp($yearmonth, "current") == 0) {
            $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                    AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client);
        } else {
            if (!$bUseHeapTable) {
                $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                        AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db2->escape($yearmonth) . "'";
            } else {
                $sql = "SELECT SUM(visited) FROM " . $db2->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . " AND idlang=" . cSecurity::toInteger($lang) . "
                        AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db2->escape($yearmonth) . "'";
            }
        }

        $db2->query($sql);
        $db2->nextRecord();

        $inThisLanguage = $db2->f(0);

        $icon = '<img src="' . $cfg['path']['images'] . 'folder.gif" class="vAlignMiddle">';

        //************ art ********************************
        $sql = "SELECT * FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["art"] . " AS B, " . $cfg["tab"]["art_lang"] . " AS C WHERE A.idcat=" . cSecurity::toInteger($idcat) . "
                AND A.idart=B.idart AND B.idart=C.idart AND C.idlang=" . cSecurity::toInteger($lang) . " ORDER BY B.idart";
        $db2->query($sql);

        $numrows = $db2->numRows();
        $onclick = "";

        $online = $db->f("visible");
        if ($bCatVisible == 1) {
            $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Category is online") . '" title="' . i18n("Category is online") . '">';
        } else {
            $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Category is offline") . '" title="' . i18n("Category is offline") . '">';
        }

        //************check if there are subcategories ******************
        $iSumSubCategories = 0;
        $sSql = "SELECT COUNT(*) AS cat_count FROM " . $cfg["tab"]["cat"] . " WHERE parentid=" . cSecurity::toInteger($idcat) . ";";
        $db3 = cRegistry::getDb();
        $db3->query($sSql);
        if ($db3->nextRecord()) {
            $iSumSubCategories = $db3->f('cat_count');
        }
        $db3->free();

        $tpl->set('d', 'PADDING_LEFT', $padding_left);
        $tpl->set('d', 'TEXT', $text . ' (idcat: ' . cSecurity::toInteger($db->f('idcat')) . ')');
        $tpl->set('d', 'ONCLICK', $onclick);
        $tpl->set('d', 'ICON', $icon);
        $tpl->set('d', 'STATUS', $offonline);
        $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
        $tpl->set('d', 'TOTAL', $total);
        $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
        if ($numrows > 0 || $iSumSubCategories > 0) {
            $tpl->set('d', 'EXPAND', '<a href="javascript:changeVisibility(\'' . implode('_', $aRowname) . '\', ' . $db->f("level") . ', ' . $idcat . ')">
                                          <img src="' . $cfg['path']['images'] . 'open_all.gif"
                                               alt="' . i18n("Open category") . '"
                                               title="' . i18n("Open category") . '"
                                               id="' . implode('_', $aRowname) . '_img"
                                               class="vAlignMiddle">
                                      </a>');
        } else {
            $tpl->set('d', 'EXPAND', '<img src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
        }
        $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
        if ($db->f("level") != 0) {
            $tpl->set('d', 'DISPLAY_ROW', 'none');
        } else {
            $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
        }
        $frontendURL = cRegistry::getFrontendUrl();
        $cat_name = "";
        statCreateLocationString($db->f('idcat'), "&nbsp;/&nbsp;", $cat_name);
        $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
        $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idcat=' . $db->f('idcat'));

        $tpl->next();
        $currentRow++;

        $onclick = "";
        $text = "";
        $numberOfArticles = "";
        $total = "";
        $inThisLanguage = "";

        while ($db2->nextRecord()) {
            $idart = $db2->f("idart");

            array_push($aRowname, $idart);

            $text = "";
            $numberOfArticles = "";
            $total = "";
            $inThisLanguage = "";

            $padding_left = 10 + (15 * ($db->f("level") + 1));

            $text = $db2->f("title");
            $online = $db2->f("online");

            //************** number of arts **************
            $db3 = cRegistry::getDb();

            //************** hits of art total **************
            if (strcmp($yearmonth, "current") == 0) {
                $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                     AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idclient=" . cSecurity::toInteger($client);
            } else {
                if (!$bUseHeapTable) {
                    $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                            AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db3->escape($yearmonth) . "'";
                } else {
                    $sql = "SELECT SUM(visited) FROM " . $db3->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . " AND idart=" . cSecurity::toInteger($idart) . "
                            AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db3->escape($yearmonth) . "'";
                }
            }

            $db3->query($sql);
            $db3->nextRecord();

            $total = $db3->f(0);

            //************** hits of art in this language ***************
            if (strcmp($yearmonth, "current") == 0) {
                $sql = "SELECT visited, idart FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                        AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client);
            } else {
                if (!$bUseHeapTable) {
                    $sql = "SELECT visited, idart FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                            AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . "
                            AND B.archived='" . $db3->escape($yearmonth) . "'";
                } else {
                    $sql = "SELECT visited, idart FROM " . $db3->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . " AND idart=" . cSecurity::toInteger($idart) . "
                            AND idlang=" . cSecurity::toInteger($lang) . " AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db3->escape($yearmonth) . "'";
                }
            }

            $db3->query($sql);
            $db3->nextRecord();

            $inThisLanguage = $db3->f(0);

            if ($online == 0) {
                $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Article is offline") . '" title="' . i18n("Article is offline") . '">';
            } else {
                $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Article is online") . '" title="' . i18n("Article is online") . '">';
            }

            $icon = '<img src="' . $cfg['path']['images'] . 'article.gif"  class="vAlignMiddle">';
            $tpl->set('d', 'PADDING_LEFT', $padding_left);
            $tpl->set('d', 'TEXT', $text . ' (idart: ' . cSecurity::toInteger($db3->f('idart')) . ')');
            $tpl->set('d', 'ONCLICK', "");
            $tpl->set('d', 'ICON', $icon);
            $tpl->set('d', 'STATUS', $offonline);
            $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
            //$tpl->set('d', 'ROWNAME', "HIDE".($db->f("level")+1));
            $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
            $tpl->set('d', 'TOTAL', $total);
            $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
            $tpl->set('d', 'EXPAND', '<img src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
            $tpl->set('d', 'DISPLAY_ROW', 'none');
            $cat_name = "";
            statCreateLocationString($db3->f('idart'), "&nbsp;/&nbsp;", $cat_name);
            $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
            $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $db3->f('idart'));
            $tpl->next();
            $currentRow++;

            array_pop($aRowname);
        }
    }

    //************** hits total**************
    if (strcmp($yearmonth, "current") == 0) {
        $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat"] . " AS B WHERE A.idcatart=B.idcatart AND B.idclient=" . cSecurity::toInteger($client);
    } else {
        if (!$bUseHeapTable) {
            $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND B.idclient=" . cSecurity::toInteger($client) . "
                    AND B.archived='" . $db->escape($yearmonth) . "'";
        } else {
            $sql = "SELECT SUM(visited) FROM " . $db->escape($sHeapTable) . " WHERE idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db->escape($yearmonth) . "'";
        }
    }

    $db->query($sql);
    $db->nextRecord();

    $total = $db->f(0);

    //************** hits total on this language ***************
    if (strcmp($yearmonth, "current") == 0) {
        $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat"] . " AS B WHERE A.idcatart=B.idcatart AND B.idlang=" . cSecurity::toInteger($lang) . "
                AND B.idclient=" . cSecurity::toInteger($client);
    } else {
        if (!$bUseHeapTable) {
            $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND B.idlang=" . cSecurity::toInteger($lang) . "
                    AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db->escape($yearmonth) . "'";
        } else {
            $sql = "SELECT SUM(visited) FROM " . $db->escape($sHeapTable) . " WHERE idlang=" . cSecurity::toInteger($lang) . " AND idclient=" . cSecurity::toInteger($client) . "
                    AND archived='" . $db->escape($yearmonth) . "'";
        }
    }

    $db->query($sql);
    $db->nextRecord();

    $inThisLanguage = $db->f(0);

    $tpl->set('d', 'TEXT', '&nbsp;');
    $tpl->set('d', 'ICON', '');
    $tpl->set('d', 'STATUS', '');
    $tpl->set('d', 'PADDING_LEFT', '10');
    $tpl->set('d', 'NUMBEROFARTICLES', '');
    $tpl->set('d', 'TOTAL', '');
    $tpl->set('d', 'INTHISLANGUAGE', '');
    $tpl->set('d', 'EXPAND', '');
    $tpl->set('d', 'ROWNAME', '');
    $tpl->set('d', 'ONCLICK', '');
    $tpl->set('d', 'DISPLAY_ROW', $sDisplay);

    $tpl->set('s', 'SUMTEXT', i18n("Sum"));
    $tpl->set('s', 'SUMNUMBEROFARTICLES', $sumNumberOfArticles);
    $tpl->set('s', 'SUMTOTAL', $total);
    $tpl->set('s', 'SUMINTHISLANGUAGE', $inThisLanguage);
    $tpl->next();
}

/**
 * Generates a statistics page for a given year
 *
 * @param $year Specifies the year to retrieve the statistics for
 */
function statsOverviewYear($year) {
    global $cfg, $db, $tpl, $client, $lang;

    $sDisplay = 'table-row';

    if (preg_match('/MSIE/', $_SERVER['HTTP_USER_AGENT'])) {
        $sDisplay = 'block';
    }

    $sql = "SELECT
                idtree, A.idcat, level, preid, C.name, visible
            FROM
                " . $cfg["tab"]["cat_tree"] . " AS A,
                " . $cfg["tab"]["cat"] . " AS B,
                " . $cfg["tab"]["cat_lang"] . " AS C
            WHERE
                A.idcat=B.idcat AND
                B.idcat=C.idcat AND
                C.idlang=" . cSecurity::toInteger($lang) . " AND
                B.idclient=" . cSecurity::toInteger($client) . "
            ORDER BY idtree";

    $db->query($sql);

    $currentRow = 2;

    $aRowname = array();
    $iLevel = 0;
    $backendUrl = cRegistry::getBackendUrl();
    $tpl->set('s', 'IMG_EXPAND', $backendUrl . $cfg['path']['images'] . 'open_all.gif');
    $tpl->set('s', 'IMG_COLLAPSE', $backendUrl . $cfg['path']['images'] . 'close_all.gif');

    $sumNumberOfArticles = 0;

    while ($db->nextRecord()) {
        if ($db->f("level") == 0 && $db->f("preid") != 0) {
            $tpl->set('d', 'PADDING_LEFT', '10');
            $tpl->set('d', 'TEXT', '&nbsp;');
            $tpl->set('d', 'NUMBEROFARTICLES', '');
            $tpl->set('d', 'TOTAL', '');
            $tpl->set('d', 'STATUS', '');
            $tpl->set('d', 'ONCLICK', '');
            $tpl->set('d', 'ICON', '');
            $tpl->set('d', 'INTHISLANGUAGE', '');
            $tpl->set('d', 'EXPAND', '');
            $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
            $tpl->set('d', 'ROWNAME', '');
            $tpl->next();
            $currentRow++;
        }

        $padding_left = 10 + (15 * $db->f("level"));
        $text = $db->f(4);
        $idcat = $db->f("idcat");
        $bCatVisible = $db->f("visible");

        if ($db->f("level") < $iLevel) {
            $iDistance = $iLevel - $db->f("level");

            for ($i = 0; $i < $iDistance; $i++) {
                array_pop($aRowname);
            }
            $iLevel = $db->f("level");
        }

        if ($db->f("level") >= $iLevel) {
            if ($db->f("level") == $iLevel) {
                array_pop($aRowname);
            } else {
                $iLevel = $db->f("level");
            }
            array_push($aRowname, $idcat);
        }

        $db2 = cRegistry::getDb();
        //************** number of arts **************
        $sql = "SELECT COUNT(*) FROM " . $cfg["tab"]["cat_art"] . " WHERE idcat=" . cSecurity::toInteger($idcat);
        $db2->query($sql);
        $db2->nextRecord();

        $numberOfArticles = $db2->f(0);
        $sumNumberOfArticles += $numberOfArticles;
        $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)=" . cSecurity::toInteger($year, $db2) . " GROUP BY SUBSTRING(B.archived,1,4)";
        $db2->query($sql);
        $db2->nextRecord();

        $total = $db2->f(0);

        //************** hits of category in this language ***************
        $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)=" . $db2->escape($year) . "
                GROUP BY SUBSTRING(B.archived,1,4)";
        $db2->query($sql);
        $db2->nextRecord();

        $inThisLanguage = $db2->f(0);

        $icon = '<img src="' . $cfg['path']['images'] . 'folder.gif" class="vAlignMiddle">';

        //************ art ********************************
        $sql = "SELECT * FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["art"] . " AS B, " . $cfg["tab"]["art_lang"] . " AS C WHERE A.idcat=" . cSecurity::toInteger($idcat) . " AND A.idart=B.idart AND B.idart=C.idart
                AND C.idlang=" . cSecurity::toInteger($lang) . " ORDER BY B.idart";
        $db2->query($sql);

        $numrows = $db2->numRows();
        $onclick = "";

        if ($bCatVisible == 0) {
            $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Category is offline") . '" title="' . i18n("Category is offline") . '">';
        } else {
            $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Category is online") . '" title="' . i18n("Category is online") . '">';
        }

        //************check if there are subcategories ******************
        $iSumSubCategories = 0;
        $sSql = "SELECT count(*) as cat_count from " . $cfg["tab"]["cat"] . " WHERE parentid=" . cSecurity::toInteger($idcat) . ";";
        $db3 = cRegistry::getDb();
        $db3->query($sSql);
        if ($db3->nextRecord()) {
            $iSumSubCategories = $db3->f('cat_count');
        }
        $db3->free();

        $tpl->set('d', 'PADDING_LEFT', $padding_left);
        $tpl->set('d', 'TEXT', $text . ' (idcat: ' . cSecurity::toInteger($db->f('idcat')) . ')');
        $tpl->set('d', 'ONCLICK', $onclick);
        $tpl->set('d', 'ICON', $icon);
        $tpl->set('d', 'STATUS', $offonline);
        $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
        $tpl->set('d', 'TOTAL', $total);
        $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
        $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);

        if ($numrows > 0 || $iSumSubCategories > 0) {
            $tpl->set('d', 'EXPAND', '<a href="javascript:changeVisibility(\'' . implode('_', $aRowname) . '\', ' . $db->f("level") . ', ' . $idcat . ')">
                                          <img src="' . $cfg['path']['images'] . 'open_all.gif"
                                               alt="' . i18n("Open category") . '"
                                               title="' . i18n("Open category") . '"
                                               id="' . implode('_', $aRowname) . '_img"
                                               class="vAlignMiddle">
                                      </a>');
        } else {
            $tpl->set('d', 'EXPAND', '<img src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
        }

        if ($db->f("level") != 0) {
            $tpl->set('d', 'DISPLAY_ROW', 'none');
        } else {
            $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
        }
        $frontendURL = cRegistry::getFrontendUrl();
        $cat_name = "";
        statCreateLocationString($db->f('idcat'), "&nbsp;/&nbsp;", $cat_name);
        $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
        $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idcat=' . $db->f('idcat'));

        $tpl->next();
        $currentRow++;

        $onclick = "";
        $text = "";
        $numberOfArticles = "";
        $total = "";
        $inThisLanguage = "";

        while ($db2->nextRecord()) {
            $idart = $db2->f("idart");

            array_push($aRowname, $idart);

            $text = "";
            $numberOfArticles = "";
            $total = "";
            $inThisLanguage = "";

            $padding_left = 10 + (15 * ($db->f("level") + 1));

            $text = $db2->f("title");
            $online = $db2->f("online");

            //************** number of arts **************
            $db3 = cRegistry::getDb();

            //************** hits of art total **************
            $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                    AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)=" . $db3->escape($year) . "
                    GROUP BY SUBSTRING(B.archived,1,4)";
            $db3->query($sql);
            $db3->nextRecord();

            $total = $db3->f(0);

            //************** hits of art in this language ***************
            $sql = "SELECT visited FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND A.idcat=" . cSecurity::toInteger($idcat) . "
                    AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . "
                    AND SUBSTRING(B.archived,1,4)=" . $db3->escape($year) . " GROUP BY SUBSTRING(B.archived,1,4)";
            $db3->query($sql);
            $db3->nextRecord();

            $inThisLanguage = $db3->f(0);

            if ($online == 0) {
                $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Article is offline") . '" title="' . i18n("Article is offline") . '">';
            } else {
                $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Category is online") . '" title="' . i18n("Category is online") . '">';
            }

            $icon = '<img src="' . $cfg['path']['images'] . 'article.gif" class="vAlignMiddle">';
            $tpl->set('d', 'PADDING_LEFT', $padding_left);
            $tpl->set('d', 'TEXT', $text . ' (idart: ' . cSecurity::toInteger($idart) . ')');
            $tpl->set('d', 'ONCLICK', "");
            $tpl->set('d', 'ICON', $icon);
            $tpl->set('d', 'STATUS', $offonline);
            $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
            $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
            $tpl->set('d', 'TOTAL', $total);
            $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
            $tpl->set('d', 'EXPAND', '<img src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
            $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
            $tpl->set('d', 'DISPLAY_ROW', 'none');
            $cat_name = "";
            statCreateLocationString($idart, "&nbsp;/&nbsp;", $cat_name);
            $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
            $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $idart);

            $tpl->next();
            $currentRow++;

            array_pop($aRowname);
        }
    }

    //************** hits total**************
    $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND B.idclient=" . cSecurity::toInteger($client) . "
            AND SUBSTRING(B.archived,1,4)='" . $db->escape($year) . "' GROUP BY SUBSTRING(B.archived,1,4)";
    $db->query($sql);
    $db->nextRecord();

    $total = $db->f(0);

    //************** hits total on this language ***************
    $sql = "SELECT SUM(visited) FROM " . $cfg["tab"]["cat_art"] . " AS A, " . $cfg["tab"]["stat_archive"] . " AS B WHERE A.idcatart=B.idcatart AND B.idlang=" . cSecurity::toInteger($lang) . "
            AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)='" . $db->escape($year) . "' GROUP BY SUBSTRING(B.archived,1,4)";
    $db->query($sql);
    $db->nextRecord();

    $inThisLanguage = $db->f(0);

    $tpl->set('d', 'TEXT', '&nbsp;');
    $tpl->set('d', 'ICON', '');
    $tpl->set('d', 'STATUS', '');
    $tpl->set('d', 'PADDING_LEFT', '10');
    $tpl->set('d', 'NUMBEROFARTICLES', '');
    $tpl->set('d', 'TOTAL', '');
    $tpl->set('d', 'ONCLICK', '');
    $tpl->set('d', 'EXPAND', '');
    $tpl->set('d', 'ROWNAME', '');
    $tpl->set('d', 'INTHISLANGUAGE', '');
    $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
    $tpl->set('s', 'SUMTEXT', "Summe");
    $tpl->set('s', 'SUMNUMBEROFARTICLES', $sumNumberOfArticles);
    $tpl->set('s', 'SUMTOTAL', $total);
    $tpl->set('s', 'SUMINTHISLANGUAGE', $inThisLanguage);
    $tpl->next();
}

/**
 * Generates a top<n> statistics page
 *
 * @param $yearmonth  Specifies the year and month from which to retrieve the
 *                    statistics, specify "current" to retrieve the current
 *                    entries
 * @param $top        Specifies the amount of pages to display
 */
function statsOverviewTop($yearmonth, $top) {
    global $cfg, $db, $tpl, $client, $cfgClient, $lang;

    if (strcmp($yearmonth, "current") == 0) {
        $sql = "SELECT DISTINCT
                    C.title, A.visited, C.idart
                FROM
                    " . $cfg["tab"]["stat"] . " AS A,
                    " . $cfg["tab"]["cat_art"] . " AS B,
                    " . $cfg["tab"]["art_lang"] . " AS C
                WHERE
                    C.idart = B.idart AND
                    C.idlang = A.idlang AND
                    B.idcatart = A.idcatart AND
                    A.idclient = " . cSecurity::toInteger($client) . " AND
                    A.idlang = " . cSecurity::toInteger($lang) . "
                ORDER BY A.visited DESC
                LIMIT " . $db->escape($top);
    } else {
        $sql = "SELECT DISTINCT
                    C.title, A.visited, B.idcat, C.idart
                FROM
                    " . $cfg["tab"]["stat_archive"] . " AS A,
                    " . $cfg["tab"]["cat_art"] . " AS B,
                    " . $cfg["tab"]["art_lang"] . " AS C
                WHERE
                    C.idart = B.idart AND
                    C.idlang = A.idlang AND
                    B.idcatart = A.idcatart AND
                    A.idclient = " . cSecurity::toInteger($client) . " AND
                    A.archived = '" . $db->escape($yearmonth) . "' AND
                    A.idlang = " . cSecurity::toInteger($lang) . " ORDER BY
                    A.visited DESC
                LIMIT " . $db->escape($top);
    }

    $db->query($sql);

    $frontendURL = cRegistry::getFrontendUrl();
    while ($db->nextRecord()) {
        $cat_name = "";
        statCreateLocationString($db->f(2), "&nbsp;/&nbsp;", $cat_name);
        $tpl->set('d', 'PADDING_LEFT', '5');
        $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
        $tpl->set('d', 'TEXT', $db->f(0) . ' (idart: ' . cSecurity::toInteger($db->f('idart')) . ')');
        $tpl->set('d', 'TOTAL', $db->f(1));
        $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $db->f('idart'));
        $tpl->next();
    }
}

/**
 * Generates the location string for passed category id.
 *
 * Performs a recursive call, if parent category doesn't matches to 0
 *
 * @param   int  $idcat  The category id
 * @param   string  $seperator  Separator for location string
 * @param   string  $cat_str    The location string variable (reference)
 */
function statCreateLocationString($idcat, $seperator, &$cat_str) {
    $cats = array();

    // get category path
    $helper = cCategoryHelper::getInstance();
    foreach ($helper->getCategoryPath($idcat) as $categoryLang) {
        $cats[] = $categoryLang->getField('name');
    }

    $cat_str = implode($seperator, $cats);
}

/**
 * Generates a top<n> statistics page
 *
 * @param  int  $year  Specifies the year from which to retrieve the statistics
 * @param  int  $top  Specifies the amount of pages to display
 */
function statsOverviewTopYear($year, $top) {
    global $cfg, $db, $tpl, $client, $lang, $cfgClient;

    $sql = "SELECT
                C.title, SUM(A.visited) as visited, B.idcat AS idcat, C.idart AS idart
            FROM
                " . $cfg["tab"]["stat_archive"] . " AS A,
                " . $cfg["tab"]["cat_art"] . " AS B,
                " . $cfg["tab"]["art_lang"] . " AS C
            WHERE
                C.idart = B.idart AND
                C.idlang = A.idlang AND
                B.idcatart = A.idcatart AND
                A.idclient = " . cSecurity::toInteger($client) . " AND
                A.archived LIKE '" . $db->escape($year) . "%' AND
                A.idlang = " . cSecurity::toInteger($lang) . "
            GROUP BY A.idcatart
            ORDER BY visited DESC
            LIMIT " . $db->escape($top);

    $db->query($sql);
    $frontendURL = cRegistry::getFrontendUrl();
    while ($db->nextRecord()) {
        $cat_name = '';
        statCreateLocationString($db->f('idcat'), "&nbsp;/&nbsp;", $cat_name);

        $tpl->set('d', 'PADDING_LEFT', '0');
        $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
        $tpl->set('d', 'TEXT', $db->f(0) . ' (idart: ' . cSecurity::toInteger($db->f('idart')) . ')');
        $tpl->set('d', 'TOTAL', $db->f(1));
        $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $db->f('idart'));
        $tpl->next();
    }
}

/**
 * Returns a drop down to choose the stats to display
 *
 * @param  string  $default
 * @return string Returns a drop down string
 */
function statDisplayTopChooser($default) {
    if ($default == "top10") {
        $defaultTop10 = "selected";
    }
    if ($default == "top20") {
        $defaultTop20 = "selected";
    }
    if ($default == "top30") {
        $defaultTop30 = "selected";
    }
    if ($default == "all") {
        $defaultAll = "selected";
    }

    return ("<form name=\"name\">" .
            "  <select class=\"text_medium\" onchange=\"top10Action(this)\">" .
            "    <option value=\"top10\" $defaultTop10>" . i18n("Top 10") . "</option>" .
            "    <option value=\"top20\" $defaultTop20>" . i18n("Top 20") . "</option>" .
            "    <option value=\"top30\" $defaultTop30>" . i18n("Top 30") . "</option>" .
            "    <option value=\"all\" $defaultAll>" . i18n("All") . "</option>" .
            "  </select>" .
            "</form>");
}

/**
 * Returns a drop down to choose the stats to display for yearly summary pages
 *
 * @param string  $default
 * @return string Returns a drop down string
 */
function statDisplayYearlyTopChooser($default) {
    if ($default == "top10") {
        $defaultTop10 = "selected";
    }
    if ($default == "top20") {
        $defaultTop20 = "selected";
    }
    if ($default == "top30") {
        $defaultTop30 = "selected";
    }
    if ($default == "all") {
        $defaultAll = "selected";
    }

    return ("<form name=\"name\">" .
            "  <select class=\"text_medium\" onchange=\"top10ActionYearly(this)\">" .
            "    <option value=\"top10\" $defaultTop10>" . i18n("Top 10") . "</option>" .
            "    <option value=\"top20\" $defaultTop20>" . i18n("Top 20") . "</option>" .
            "    <option value=\"top30\" $defaultTop30>" . i18n("Top 30") . "</option>" .
            "    <option value=\"all\" $defaultAll>" . i18n("All") . "</option>" .
            "  </select>" .
            "</form>");
}

/**
 * Return an array with all years which are available as stat files
 *
 * @param mixed many
 * @return array  Array of strings with years.
 */
function statGetAvailableYears($client, $lang) {
    global $cfg, $db;

    $availableYears = array();

    $sql = "SELECT SUBSTRING(`archived`,1,4)
            FROM
                " . $cfg["tab"]["stat_archive"] . "
            WHERE
                idlang = " . cSecurity::toInteger($lang) . " AND
                idclient = " . cSecurity::toInteger($client) . "
            GROUP BY
                SUBSTRING(`archived`,1,4)
            ORDER BY
                SUBSTRING(`archived`,1,4) DESC";

    $db->query($sql);
    while ($db->nextRecord()) {
        $availableYears[] = $db->f(0);
    }

    return($availableYears);
}

/**
 * Return an array with all months for a specific year which are available
 * as stat files
 *
 * @param mixed many
 * @return array  Array of strings with months.
 */
function statGetAvailableMonths($year, $client, $lang) {
    global $cfg, $db;

    $availableYears = array();

    $sql = "SELECT SUBSTRING(`archived`,5,2)
            FROM
                " . $cfg["tab"]["stat_archive"] . "
            WHERE
                idlang = " . cSecurity::toInteger($lang) . " AND
                idclient = " . cSecurity::toInteger($client) . " AND
                SUBSTRING(`archived`,1,4) = '" . $db->escape($year) . "'
            GROUP BY
                SUBSTRING(`archived`,5,2)
            ORDER BY SUBSTRING(`archived`,5,2) DESC";

    $db->query($sql);
    while ($db->nextRecord()) {
        $availableYears[] = $db->f(0);
    }

    return($availableYears);
}

/**
 * Resets the statistic for passed client
 *
 * @param   int  $client  Id of client
 */
function statResetStatistic($client) {
    global $db, $cfg;
    $sql = "UPDATE " . $cfg["tab"]["stat"] . " SET visited=0 WHERE idclient=" . cSecurity::toInteger($client);
    $db->query($sql);
}

/**
 * Deletes existing heap table (table in memory) and creates it.
 *
 * @param   string  $sHeapTable  Table name
 * @param   cDb  $db  Database object
 */
function buildHeapTable($sHeapTable, $db) {
    global $cfg;

    $sql = "DROP TABLE IF EXISTS " . $db->escape($sHeapTable) . ";";
    $db->query($sql);

    $sql = "CREATE TABLE " . $db->escape($sHeapTable) . " TYPE=HEAP
                SELECT
                    A.idcatart,
                    A.idcat,
                    A.idart,
                    B.idstatarch,
                    B.archived,
                    B.idlang,
                    B.idclient,
                    B.visited
                FROM
                    " . $cfg['tab']['cat_art'] . " AS A, " . $cfg['tab']['stat_archive'] . " AS B
                WHERE
                    A.idcatart = B.idcatart;";
    $db->query($sql);

    $sql = "ALTER TABLE `" . $db->escape($sHeapTable) . "` ADD PRIMARY KEY (`idcatart`,`idcat` ,`idart`,`idstatarch` ,`archived`,`idlang`,`idclient` ,`visited`);";
    $db->query($sql);
}

?>