1: <?php
   2: 
   3: /**
   4:  * This file contains the CONTENIDO statistic functions.
   5:  *
   6:  * @package          Core
   7:  * @subpackage       Backend
   8:  * @version          SVN Revision $Rev:$
   9:  *
  10:  * @author           Olaf Niemann
  11:  * @copyright        four for business AG <www.4fb.de>
  12:  * @license          http://www.contenido.org/license/LIZENZ.txt
  13:  * @link             http://www.4fb.de
  14:  * @link             http://www.contenido.org
  15:  */
  16: 
  17: defined('CON_FRAMEWORK') || die('Illegal call: Missing framework initialization - request aborted.');
  18: 
  19: cInclude("includes", "functions.database.php");
  20: 
  21: /**
  22:  * Displays statistic information layer (a div Tag)
  23:  *
  24:  * @deprecated [2015-05-21]
  25:  *         This method is no longer supported (no replacement)
  26:  * @param int $id
  27:  *         Either article or directory id
  28:  * @param string $type
  29:  *         The type
  30:  * @param int $x
  31:  *         Style top position
  32:  * @param int $y
  33:  *         Style left position
  34:  * @param int $w
  35:  *         Style width
  36:  * @param int $h
  37:  *         Style height
  38:  * @return string
  39:  *         Composed info layer
  40:  */
  41: function statsDisplayInfo($id, $type, $x, $y, $w, $h) {
  42:     cDeprecated('This method is deprecated and is not needed any longer');
  43: 
  44:     if (strcmp($type, "article" == 0)) {
  45:         $text = i18n("Info about article") . " " . $id;
  46:     } else {
  47:         $text = i18n("Info about directory") . " " . $id;
  48:     }
  49: 
  50:     $div = new cHTMLDiv($text, "text_medium", "idElement14");
  51:     $div->appendStyleDefinition("border", "1px solid #e8e8ee");
  52:     $div->appendStyleDefinition("position", "absolute");
  53:     $div->appendStyleDefinition("top", $x . "px");
  54:     $div->appendStyleDefinition("left", $y . "px");
  55:     $div->appendStyleDefinition("width", $w . "px");
  56:     $div->appendStyleDefinition("height", $h . "px");
  57: 
  58:     return $div->toHTML();
  59: }
  60: 
  61: /**
  62:  * Archives the current statistics
  63:  *
  64:  * @param unknown_type $yearmonth
  65:  *         String with the desired archive date (YYYYMM)
  66:  */
  67: function statsArchive($yearmonth) {
  68:     global $cfg;
  69: 
  70:     $yearmonth = preg_replace('/\s/', '0', $yearmonth);
  71: 
  72:     $db = cRegistry::getDb();
  73:     $db2 = cRegistry::getDb();
  74: 
  75:     $sql = "SELECT idcatart, idlang, idclient, visited, visitdate FROM " . $cfg["tab"]["stat"];
  76: 
  77:     $db->query($sql);
  78: 
  79:     while ($db->nextRecord()) {
  80:         $insertSQL = "INSERT INTO
  81:                           " . $cfg["tab"]["stat_archive"] . "
  82:                           (archived, idcatart, idlang, idclient, visited, visitdate)
  83:                       VALUES
  84:                           (
  85:                            " . $yearmonth . ",
  86:                            " . cSecurity::toInteger($db->f(0)) . ",
  87:                            " . cSecurity::toInteger($db->f(1)) . ",
  88:                            " . cSecurity::toInteger($db->f(2)) . ",
  89:                            " . cSecurity::toInteger($db->f(3)) . ",
  90:                            '" . $db2->escape($db->f(4)) . "')";
  91: 
  92:         $db2->query($insertSQL);
  93:     }
  94: 
  95:     $sql = "DELETE FROM " . $cfg["tab"]["stat"];
  96:     $db->query($sql);
  97: 
  98:     // Recreate empty stats
  99:     $sql = "SELECT
 100:                 A.idcatart, B.idclient, C.idlang
 101:             FROM
 102:                 " . $cfg["tab"]["cat_art"] . " AS A INNER JOIN
 103:                 " . $cfg["tab"]["cat"] . " AS B ON A.idcat = B.idcat INNER JOIN
 104:                 " . $cfg["tab"]["cat_lang"] . " AS C ON A.idcat = C.idcat ";
 105: 
 106:     $db->query($sql);
 107: 
 108:     while ($db->nextRecord()) {
 109:         $insertSQL = "INSERT INTO
 110:                           " . $cfg["tab"]["stat"] . "
 111:                           (idcatart, idlang, idclient, visited)
 112:                       VALUES (
 113:                           " . cSecurity::toInteger($db->f(0)) . ",
 114:                           " . cSecurity::toInteger($db->f(2)) . ",
 115:                           " . cSecurity::toInteger($db->f(1)) . ",
 116:                           '0000-00-00 00:00:00')";
 117: 
 118:         $db2->query($insertSQL);
 119:     }
 120: }
 121: 
 122: /**
 123:  * Generates a statistics page
 124:  *
 125:  * @param unknown_type $yearmonth
 126:  *         Specifies the year and month from which to retrieve the statistics,
 127:  *         specify "current" to retrieve the current entries.
 128:  */
 129: function statsOverviewAll($yearmonth) {
 130:     global $cfg, $db, $tpl, $client, $lang, $cfgClient;
 131: 
 132:     $sDisplay = 'table-row';
 133: 
 134:     $bUseHeapTable = $cfg["statistics_heap_table"];
 135: 
 136:     $sHeapTable = $cfg['tab']['stat_heap_table'];
 137: 
 138:     if ($bUseHeapTable) {
 139:         if (!dbTableExists($db, $sHeapTable)) {
 140:             buildHeapTable($sHeapTable, $db);
 141:         }
 142:     }
 143: 
 144:     if (preg_match('/MSIE/', $_SERVER['HTTP_USER_AGENT'])) {
 145:         $sDisplay = 'block';
 146:     }
 147: 
 148:     $sql = "SELECT
 149:                     idtree, A.idcat, level, preid, C.name, visible
 150:                 FROM
 151:                     " . $cfg["tab"]["cat_tree"] . " AS A,
 152:                     " . $cfg["tab"]["cat"] . " AS B,
 153:                     " . $cfg["tab"]["cat_lang"] . " AS C
 154:                 WHERE
 155:                     A.idcat=B.idcat AND
 156:                     B.idcat=C.idcat AND
 157:                     C.idlang=" . cSecurity::toInteger($lang) . " AND
 158:                     B.idclient=" . cSecurity::toInteger($client) . "
 159:                 ORDER BY idtree";
 160: 
 161:     $db->query($sql);
 162: 
 163:     $currentRow = 2;
 164: 
 165:     $aRowname = array();
 166:     $iLevel = 0;
 167:     $backendUrl = cRegistry::getBackendUrl();
 168:     $tpl->set('s', 'IMG_EXPAND', $backendUrl . $cfg['path']['images'] . 'open_all.gif');
 169:     $tpl->set('s', 'IMG_COLLAPSE', $backendUrl . $cfg['path']['images'] . 'close_all.gif');
 170: 
 171:     $sumNumberOfArticles = 0;
 172: 
 173:     while ($db->nextRecord()) {
 174:         if ($db->f("level") == 0 && $db->f("preid") != 0) {
 175:             $tpl->set('d', 'PADDING_LEFT', '10');
 176:             $tpl->set('d', 'TEXT', '&nbsp;');
 177:             $tpl->set('d', 'NUMBEROFARTICLES', '');
 178:             $tpl->set('d', 'TOTAL', '');
 179:             $tpl->set('d', 'ICON', '');
 180:             $tpl->set('d', 'STATUS', '');
 181:             $tpl->set('d', 'ONCLICK', '');
 182:             $tpl->set('d', 'ROWNAME', '');
 183:             $tpl->set('d', 'INTHISLANGUAGE', '');
 184:             $tpl->set('d', 'EXPAND', '');
 185:             $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
 186:             $tpl->set('d', 'PATH', '');
 187:             $tpl->set('d', 'ULR_TO_PAGE', '');
 188: 
 189:             $tpl->next();
 190:             $currentRow++;
 191:         }
 192: 
 193:         $padding_left = 10 + (15 * $db->f("level"));
 194:         $text = $db->f(4);
 195:         $idcat = $db->f("idcat");
 196:         $bCatVisible = $db->f("visible");
 197: 
 198:         if ($db->f("level") < $iLevel) {
 199:             $iDistance = $iLevel - $db->f("level");
 200: 
 201:             for ($i = 0; $i < $iDistance; $i++) {
 202:                 array_pop($aRowname);
 203:             }
 204:             $iLevel = $db->f("level");
 205:         }
 206: 
 207:         if ($db->f("level") >= $iLevel) {
 208:             if ($db->f("level") == $iLevel) {
 209:                 array_pop($aRowname);
 210:             } else {
 211:                 $iLevel = $db->f("level");
 212:             }
 213:             array_push($aRowname, $idcat);
 214:         }
 215: 
 216:         /*
 217:          * number of arts
 218:          */
 219:         $sql = "SELECT COUNT(*) FROM " . $cfg["tab"]["cat_art"] . " WHERE idcat=" . cSecurity::toInteger($idcat);
 220:         $db2 = cRegistry::getDb();
 221:         $db2->query($sql);
 222:         $db2->nextRecord();
 223:         $numberOfArticles = $db2->f(0);
 224:         $sumNumberOfArticles += $numberOfArticles;
 225: 
 226:         /*
 227:          * hits of category total
 228:          */
 229:         if (strcmp($yearmonth, "current") == 0) {
 230:             $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);
 231:         } else {
 232:             if (!$bUseHeapTable) {
 233:                 $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) . "
 234:                         AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db2->escape($yearmonth) . "'";
 235:             } else {
 236:                 $sql = "SELECT SUM(visited) FROM " . $db2->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . "
 237:                         AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db2->escape($yearmonth) . "'";
 238:             }
 239:         }
 240:         $db2->query($sql);
 241:         $db2->nextRecord();
 242:         $total = $db2->f(0);
 243: 
 244:         /*
 245:          * hits of category in this language
 246:          */
 247:         if (strcmp($yearmonth, "current") == 0) {
 248:             $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) . "
 249:                     AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client);
 250:         } else {
 251:             if (!$bUseHeapTable) {
 252:                 $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) . "
 253:                         AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db2->escape($yearmonth) . "'";
 254:             } else {
 255:                 $sql = "SELECT SUM(visited) FROM " . $db2->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . " AND idlang=" . cSecurity::toInteger($lang) . "
 256:                         AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db2->escape($yearmonth) . "'";
 257:             }
 258:         }
 259:         $db2->query($sql);
 260:         $db2->nextRecord();
 261:         $inThisLanguage = $db2->f(0);
 262: 
 263:         $icon = '<img alt="" src="' . $cfg['path']['images'] . 'folder.gif" class="vAlignMiddle">';
 264: 
 265:         /*
 266:          * art
 267:          */
 268:         $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) . "
 269:                 AND A.idart=B.idart AND B.idart=C.idart AND C.idlang=" . cSecurity::toInteger($lang) . " ORDER BY B.idart";
 270:         $db2->query($sql);
 271: 
 272:         $numrows = $db2->numRows();
 273:         $onclick = "";
 274: 
 275:         $online = $db->f("visible");
 276:         if ($bCatVisible == 1) {
 277:             $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Category is online") . '" title="' . i18n("Category is online") . '">';
 278:         } else {
 279:             $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Category is offline") . '" title="' . i18n("Category is offline") . '">';
 280:         }
 281: 
 282:         /*
 283:          * check if there are subcategories
 284:          */
 285:         $iSumSubCategories = 0;
 286:         $sSql = "SELECT COUNT(*) AS cat_count FROM " . $cfg["tab"]["cat"] . " WHERE parentid=" . cSecurity::toInteger($idcat) . ";";
 287:         $db3 = cRegistry::getDb();
 288:         $db3->query($sSql);
 289:         if ($db3->nextRecord()) {
 290:             $iSumSubCategories = $db3->f('cat_count');
 291:         }
 292:         $db3->free();
 293: 
 294:         $tpl->set('d', 'PADDING_LEFT', $padding_left);
 295:         $tpl->set('d', 'TEXT', conHtmlSpecialChars($text) . ' (idcat: ' . cSecurity::toInteger($db->f('idcat')) . ')');
 296:         $tpl->set('d', 'ONCLICK', $onclick);
 297:         $tpl->set('d', 'ICON', $icon);
 298:         $tpl->set('d', 'STATUS', $offonline);
 299:         $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
 300:         $tpl->set('d', 'TOTAL', $total);
 301:         $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
 302:         if ($numrows > 0 || $iSumSubCategories > 0) {
 303:             $tpl->set('d', 'EXPAND', '<a href="javascript:changeVisibility(\'' . implode('_', $aRowname) . '\', ' . $db->f("level") . ', ' . $idcat . ')">
 304:                                           <img src="' . $cfg['path']['images'] . 'open_all.gif"
 305:                                                alt="' . i18n("Open category") . '"
 306:                                                title="' . i18n("Open category") . '"
 307:                                                id="' . implode('_', $aRowname) . '_img"
 308:                                                class="vAlignMiddle">
 309:                                       </a>');
 310:         } else {
 311:             $tpl->set('d', 'EXPAND', '<img alt="" src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
 312:         }
 313:         $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
 314:         if ($db->f("level") != 0) {
 315:             $tpl->set('d', 'DISPLAY_ROW', 'none');
 316:         } else {
 317:             $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
 318:         }
 319:         $frontendURL = cRegistry::getFrontendUrl();
 320:         $cat_name = "";
 321:         statCreateLocationString($db->f('idcat'), "&nbsp;/&nbsp;", $cat_name);
 322:         $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
 323:         $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idcat=' . $db->f('idcat'));
 324: 
 325:         $tpl->next();
 326:         $currentRow++;
 327: 
 328:         $onclick = "";
 329:         $text = "";
 330:         $numberOfArticles = "";
 331:         $total = "";
 332:         $inThisLanguage = "";
 333: 
 334:         while ($db2->nextRecord()) {
 335:             $idart = $db2->f("idart");
 336: 
 337:             array_push($aRowname, $idart);
 338: 
 339:             $text = "";
 340:             $numberOfArticles = "";
 341:             $total = "";
 342:             $inThisLanguage = "";
 343: 
 344:             $padding_left = 10 + (15 * ($db->f("level") + 1));
 345: 
 346:             $text = $db2->f("title");
 347:             $online = $db2->f("online");
 348: 
 349:             /*
 350:              * number of arts
 351:              */
 352:             $db3 = cRegistry::getDb();
 353: 
 354:             /*
 355:              * hits of art total
 356:              */
 357:             if (strcmp($yearmonth, "current") == 0) {
 358:                 $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) . "
 359:                      AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idclient=" . cSecurity::toInteger($client);
 360:             } else {
 361:                 if (!$bUseHeapTable) {
 362:                     $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) . "
 363:                             AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db3->escape($yearmonth) . "'";
 364:                 } else {
 365:                     $sql = "SELECT SUM(visited) FROM " . $db3->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . " AND idart=" . cSecurity::toInteger($idart) . "
 366:                             AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db3->escape($yearmonth) . "'";
 367:                 }
 368:             }
 369: 
 370:             $db3->query($sql);
 371:             $db3->nextRecord();
 372: 
 373:             $total = $db3->f(0);
 374: 
 375:             /*
 376:              * hits of art in this language
 377:              */
 378:             if (strcmp($yearmonth, "current") == 0) {
 379:                 $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) . "
 380:                         AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client);
 381:             } else {
 382:                 if (!$bUseHeapTable) {
 383:                     $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) . "
 384:                             AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . "
 385:                             AND B.archived='" . $db3->escape($yearmonth) . "'";
 386:                 } else {
 387:                     $sql = "SELECT visited, idart FROM " . $db3->escape($sHeapTable) . " WHERE idcat=" . cSecurity::toInteger($idcat) . " AND idart=" . cSecurity::toInteger($idart) . "
 388:                             AND idlang=" . cSecurity::toInteger($lang) . " AND idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db3->escape($yearmonth) . "'";
 389:                 }
 390:             }
 391: 
 392:             $db3->query($sql);
 393:             $db3->nextRecord();
 394: 
 395:             $inThisLanguage = $db3->f(0);
 396: 
 397:             if ($online == 0) {
 398:                 $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Article is offline") . '" title="' . i18n("Article is offline") . '">';
 399:             } else {
 400:                 $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Article is online") . '" title="' . i18n("Article is online") . '">';
 401:             }
 402: 
 403:             $icon = '<img alt="" src="' . $cfg['path']['images'] . 'article.gif"  class="vAlignMiddle">';
 404:             $tpl->set('d', 'PADDING_LEFT', $padding_left);
 405:             $tpl->set('d', 'TEXT', conHtmlSpecialChars($text) . ' (idart: ' . cSecurity::toInteger($db3->f('idart')) . ')');
 406:             $tpl->set('d', 'ONCLICK', "");
 407:             $tpl->set('d', 'ICON', $icon);
 408:             $tpl->set('d', 'STATUS', $offonline);
 409:             $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
 410:             //$tpl->set('d', 'ROWNAME', "HIDE".($db->f("level")+1));
 411:             $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
 412:             $tpl->set('d', 'TOTAL', $total);
 413:             $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
 414:             $tpl->set('d', 'EXPAND', '<img alt="" src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
 415:             $tpl->set('d', 'DISPLAY_ROW', 'none');
 416:             $cat_name = "";
 417:             statCreateLocationString($db3->f('idart'), "&nbsp;/&nbsp;", $cat_name);
 418:             $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
 419:             $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $db3->f('idart'));
 420:             $tpl->next();
 421:             $currentRow++;
 422: 
 423:             array_pop($aRowname);
 424:         }
 425:     }
 426: 
 427:     /*
 428:      * hits total
 429:      */
 430:     if (strcmp($yearmonth, "current") == 0) {
 431:         $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);
 432:     } else {
 433:         if (!$bUseHeapTable) {
 434:             $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) . "
 435:                     AND B.archived='" . $db->escape($yearmonth) . "'";
 436:         } else {
 437:             $sql = "SELECT SUM(visited) FROM " . $db->escape($sHeapTable) . " WHERE idclient=" . cSecurity::toInteger($client) . " AND archived='" . $db->escape($yearmonth) . "'";
 438:         }
 439:     }
 440: 
 441:     $db->query($sql);
 442:     $db->nextRecord();
 443: 
 444:     $total = $db->f(0);
 445: 
 446:     /*
 447:      * hits total on this language
 448:      */
 449:     if (strcmp($yearmonth, "current") == 0) {
 450:         $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) . "
 451:                 AND B.idclient=" . cSecurity::toInteger($client);
 452:     } else {
 453:         if (!$bUseHeapTable) {
 454:             $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) . "
 455:                     AND B.idclient=" . cSecurity::toInteger($client) . " AND B.archived='" . $db->escape($yearmonth) . "'";
 456:         } else {
 457:             $sql = "SELECT SUM(visited) FROM " . $db->escape($sHeapTable) . " WHERE idlang=" . cSecurity::toInteger($lang) . " AND idclient=" . cSecurity::toInteger($client) . "
 458:                     AND archived='" . $db->escape($yearmonth) . "'";
 459:         }
 460:     }
 461: 
 462:     $db->query($sql);
 463:     $db->nextRecord();
 464: 
 465:     $inThisLanguage = $db->f(0);
 466: 
 467:     $tpl->set('d', 'TEXT', '&nbsp;');
 468:     $tpl->set('d', 'ICON', '');
 469:     $tpl->set('d', 'STATUS', '');
 470:     $tpl->set('d', 'PADDING_LEFT', '10');
 471:     $tpl->set('d', 'NUMBEROFARTICLES', '');
 472:     $tpl->set('d', 'TOTAL', '');
 473:     $tpl->set('d', 'INTHISLANGUAGE', '');
 474:     $tpl->set('d', 'EXPAND', '');
 475:     $tpl->set('d', 'ROWNAME', '');
 476:     $tpl->set('d', 'ONCLICK', '');
 477:     $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
 478: 
 479:     $tpl->set('s', 'SUMTEXT', i18n("Sum"));
 480:     $tpl->set('s', 'SUMNUMBEROFARTICLES', $sumNumberOfArticles);
 481:     $tpl->set('s', 'SUMTOTAL', $total);
 482:     $tpl->set('s', 'SUMINTHISLANGUAGE', $inThisLanguage);
 483:     $tpl->next();
 484: }
 485: 
 486: /**
 487:  * Generates a statistics page for a given year
 488:  *
 489:  * @param unknown_type $year
 490:  *         Specifies the year to retrieve the statistics for
 491:  */
 492: function statsOverviewYear($year) {
 493:     global $cfg, $db, $tpl, $client, $lang;
 494: 
 495:     $sDisplay = 'table-row';
 496: 
 497:     if (preg_match('/MSIE/', $_SERVER['HTTP_USER_AGENT'])) {
 498:         $sDisplay = 'block';
 499:     }
 500: 
 501:     $sql = "SELECT
 502:                 idtree, A.idcat, level, preid, C.name, visible
 503:             FROM
 504:                 " . $cfg["tab"]["cat_tree"] . " AS A,
 505:                 " . $cfg["tab"]["cat"] . " AS B,
 506:                 " . $cfg["tab"]["cat_lang"] . " AS C
 507:             WHERE
 508:                 A.idcat=B.idcat AND
 509:                 B.idcat=C.idcat AND
 510:                 C.idlang=" . cSecurity::toInteger($lang) . " AND
 511:                 B.idclient=" . cSecurity::toInteger($client) . "
 512:             ORDER BY idtree";
 513: 
 514:     $db->query($sql);
 515: 
 516:     $currentRow = 2;
 517: 
 518:     $aRowname = array();
 519:     $iLevel = 0;
 520:     $backendUrl = cRegistry::getBackendUrl();
 521:     $tpl->set('s', 'IMG_EXPAND', $backendUrl . $cfg['path']['images'] . 'open_all.gif');
 522:     $tpl->set('s', 'IMG_COLLAPSE', $backendUrl . $cfg['path']['images'] . 'close_all.gif');
 523: 
 524:     $sumNumberOfArticles = 0;
 525: 
 526:     while ($db->nextRecord()) {
 527:         if ($db->f("level") == 0 && $db->f("preid") != 0) {
 528:             $tpl->set('d', 'PADDING_LEFT', '10');
 529:             $tpl->set('d', 'TEXT', '&nbsp;');
 530:             $tpl->set('d', 'NUMBEROFARTICLES', '');
 531:             $tpl->set('d', 'TOTAL', '');
 532:             $tpl->set('d', 'STATUS', '');
 533:             $tpl->set('d', 'ONCLICK', '');
 534:             $tpl->set('d', 'ICON', '');
 535:             $tpl->set('d', 'INTHISLANGUAGE', '');
 536:             $tpl->set('d', 'EXPAND', '');
 537:             $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
 538:             $tpl->set('d', 'ROWNAME', '');
 539:             $tpl->next();
 540:             $currentRow++;
 541:         }
 542: 
 543:         $padding_left = 10 + (15 * $db->f("level"));
 544:         $text = $db->f(4);
 545:         $idcat = $db->f("idcat");
 546:         $bCatVisible = $db->f("visible");
 547: 
 548:         if ($db->f("level") < $iLevel) {
 549:             $iDistance = $iLevel - $db->f("level");
 550: 
 551:             for ($i = 0; $i < $iDistance; $i++) {
 552:                 array_pop($aRowname);
 553:             }
 554:             $iLevel = $db->f("level");
 555:         }
 556: 
 557:         if ($db->f("level") >= $iLevel) {
 558:             if ($db->f("level") == $iLevel) {
 559:                 array_pop($aRowname);
 560:             } else {
 561:                 $iLevel = $db->f("level");
 562:             }
 563:             array_push($aRowname, $idcat);
 564:         }
 565: 
 566:         $db2 = cRegistry::getDb();
 567:         /*
 568:          * number of arts
 569:          */
 570:         $sql = "SELECT COUNT(*) FROM " . $cfg["tab"]["cat_art"] . " WHERE idcat=" . cSecurity::toInteger($idcat);
 571:         $db2->query($sql);
 572:         $db2->nextRecord();
 573: 
 574:         $numberOfArticles = $db2->f(0);
 575:         $sumNumberOfArticles += $numberOfArticles;
 576:         $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) . "
 577:                 AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)=" . cSecurity::toInteger($year, $db2) . " GROUP BY SUBSTRING(B.archived,1,4)";
 578:         $db2->query($sql);
 579:         $db2->nextRecord();
 580: 
 581:         $total = $db2->f(0);
 582: 
 583:         /*
 584:          * hits of category in this language
 585:          */
 586:         $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) . "
 587:                 AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)=" . $db2->escape($year) . "
 588:                 GROUP BY SUBSTRING(B.archived,1,4)";
 589:         $db2->query($sql);
 590:         $db2->nextRecord();
 591: 
 592:         $inThisLanguage = $db2->f(0);
 593: 
 594:         $icon = '<img alt="" src="' . $cfg['path']['images'] . 'folder.gif" class="vAlignMiddle">';
 595: 
 596:         /*
 597:          * art
 598:          */
 599:         $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
 600:                 AND C.idlang=" . cSecurity::toInteger($lang) . " ORDER BY B.idart";
 601:         $db2->query($sql);
 602: 
 603:         $numrows = $db2->numRows();
 604:         $onclick = "";
 605: 
 606:         if ($bCatVisible == 0) {
 607:             $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Category is offline") . '" title="' . i18n("Category is offline") . '">';
 608:         } else {
 609:             $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Category is online") . '" title="' . i18n("Category is online") . '">';
 610:         }
 611: 
 612:         /*
 613:          * check if there are subcategories
 614:          */
 615:         $iSumSubCategories = 0;
 616:         $sSql = "SELECT count(*) as cat_count from " . $cfg["tab"]["cat"] . " WHERE parentid=" . cSecurity::toInteger($idcat) . ";";
 617:         $db3 = cRegistry::getDb();
 618:         $db3->query($sSql);
 619:         if ($db3->nextRecord()) {
 620:             $iSumSubCategories = $db3->f('cat_count');
 621:         }
 622:         $db3->free();
 623: 
 624:         $tpl->set('d', 'PADDING_LEFT', $padding_left);
 625:         $tpl->set('d', 'TEXT', conHtmlSpecialChars($text) . ' (idcat: ' . cSecurity::toInteger($db->f('idcat')) . ')');
 626:         $tpl->set('d', 'ONCLICK', $onclick);
 627:         $tpl->set('d', 'ICON', $icon);
 628:         $tpl->set('d', 'STATUS', $offonline);
 629:         $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
 630:         $tpl->set('d', 'TOTAL', $total);
 631:         $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
 632:         $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
 633: 
 634:         if ($numrows > 0 || $iSumSubCategories > 0) {
 635:             $tpl->set('d', 'EXPAND', '<a href="javascript:changeVisibility(\'' . implode('_', $aRowname) . '\', ' . $db->f("level") . ', ' . $idcat . ')">
 636:                                           <img src="' . $cfg['path']['images'] . 'open_all.gif"
 637:                                                alt="' . i18n("Open category") . '"
 638:                                                title="' . i18n("Open category") . '"
 639:                                                id="' . implode('_', $aRowname) . '_img"
 640:                                                class="vAlignMiddle">
 641:                                       </a>');
 642:         } else {
 643:             $tpl->set('d', 'EXPAND', '<img alt="" src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
 644:         }
 645: 
 646:         if ($db->f("level") != 0) {
 647:             $tpl->set('d', 'DISPLAY_ROW', 'none');
 648:         } else {
 649:             $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
 650:         }
 651:         $frontendURL = cRegistry::getFrontendUrl();
 652:         $cat_name = "";
 653:         statCreateLocationString($db->f('idcat'), "&nbsp;/&nbsp;", $cat_name);
 654:         $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
 655:         $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idcat=' . $db->f('idcat'));
 656: 
 657:         $tpl->next();
 658:         $currentRow++;
 659: 
 660:         $onclick = "";
 661:         $text = "";
 662:         $numberOfArticles = "";
 663:         $total = "";
 664:         $inThisLanguage = "";
 665: 
 666:         while ($db2->nextRecord()) {
 667:             $idart = $db2->f("idart");
 668: 
 669:             array_push($aRowname, $idart);
 670: 
 671:             $text = "";
 672:             $numberOfArticles = "";
 673:             $total = "";
 674:             $inThisLanguage = "";
 675: 
 676:             $padding_left = 10 + (15 * ($db->f("level") + 1));
 677: 
 678:             $text = $db2->f("title");
 679:             $online = $db2->f("online");
 680: 
 681:             /*
 682:              * number of arts
 683:              */
 684:             $db3 = cRegistry::getDb();
 685: 
 686:             /*
 687:              * hits of art total
 688:              */
 689:             $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) . "
 690:                     AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)=" . $db3->escape($year) . "
 691:                     GROUP BY SUBSTRING(B.archived,1,4)";
 692:             $db3->query($sql);
 693:             $db3->nextRecord();
 694: 
 695:             $total = $db3->f(0);
 696: 
 697:             /*
 698:              * hits of art in this language
 699:              */
 700:             $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) . "
 701:                     AND A.idart=" . cSecurity::toInteger($idart) . " AND B.idlang=" . cSecurity::toInteger($lang) . " AND B.idclient=" . cSecurity::toInteger($client) . "
 702:                     AND SUBSTRING(B.archived,1,4)=" . $db3->escape($year) . " GROUP BY SUBSTRING(B.archived,1,4)";
 703:             $db3->query($sql);
 704:             $db3->nextRecord();
 705: 
 706:             $inThisLanguage = $db3->f(0);
 707: 
 708:             if ($online == 0) {
 709:                 $offonline = '<img src="' . $cfg['path']['images'] . 'offline_off.gif" alt="' . i18n("Article is offline") . '" title="' . i18n("Article is offline") . '">';
 710:             } else {
 711:                 $offonline = '<img src="' . $cfg['path']['images'] . 'online_off.gif" alt="' . i18n("Category is online") . '" title="' . i18n("Category is online") . '">';
 712:             }
 713: 
 714:             $icon = '<img alt="" src="' . $cfg['path']['images'] . 'article.gif" class="vAlignMiddle">';
 715:             $tpl->set('d', 'PADDING_LEFT', $padding_left);
 716:             $tpl->set('d', 'TEXT', conHtmlSpecialChars($text) . ' (idart: ' . cSecurity::toInteger($idart) . ')');
 717:             $tpl->set('d', 'ONCLICK', "");
 718:             $tpl->set('d', 'ICON', $icon);
 719:             $tpl->set('d', 'STATUS', $offonline);
 720:             $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
 721:             $tpl->set('d', 'NUMBEROFARTICLES', $numberOfArticles);
 722:             $tpl->set('d', 'TOTAL', $total);
 723:             $tpl->set('d', 'ROWNAME', implode('_', $aRowname));
 724:             $tpl->set('d', 'EXPAND', '<img alt="" src="' . $cfg['path']['images'] . 'spacer.gif" width="7">');
 725:             $tpl->set('d', 'INTHISLANGUAGE', $inThisLanguage);
 726:             $tpl->set('d', 'DISPLAY_ROW', 'none');
 727:             $cat_name = "";
 728:             statCreateLocationString($idart, "&nbsp;/&nbsp;", $cat_name);
 729:             $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
 730:             $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $idart);
 731: 
 732:             $tpl->next();
 733:             $currentRow++;
 734: 
 735:             array_pop($aRowname);
 736:         }
 737:     }
 738: 
 739:     /*
 740:      * hits total
 741:      */
 742:     $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) . "
 743:             AND SUBSTRING(B.archived,1,4)='" . $db->escape($year) . "' GROUP BY SUBSTRING(B.archived,1,4)";
 744:     $db->query($sql);
 745:     $db->nextRecord();
 746: 
 747:     $total = $db->f(0);
 748: 
 749:     /*
 750:      * hits total on this language
 751:      */
 752:     $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) . "
 753:             AND B.idclient=" . cSecurity::toInteger($client) . " AND SUBSTRING(B.archived,1,4)='" . $db->escape($year) . "' GROUP BY SUBSTRING(B.archived,1,4)";
 754:     $db->query($sql);
 755:     $db->nextRecord();
 756: 
 757:     $inThisLanguage = $db->f(0);
 758: 
 759:     $tpl->set('d', 'TEXT', '&nbsp;');
 760:     $tpl->set('d', 'ICON', '');
 761:     $tpl->set('d', 'STATUS', '');
 762:     $tpl->set('d', 'PADDING_LEFT', '10');
 763:     $tpl->set('d', 'NUMBEROFARTICLES', '');
 764:     $tpl->set('d', 'TOTAL', '');
 765:     $tpl->set('d', 'ONCLICK', '');
 766:     $tpl->set('d', 'EXPAND', '');
 767:     $tpl->set('d', 'ROWNAME', '');
 768:     $tpl->set('d', 'INTHISLANGUAGE', '');
 769:     $tpl->set('d', 'DISPLAY_ROW', $sDisplay);
 770:     $tpl->set('s', 'SUMTEXT', "Summe");
 771:     $tpl->set('s', 'SUMNUMBEROFARTICLES', $sumNumberOfArticles);
 772:     $tpl->set('s', 'SUMTOTAL', $total);
 773:     $tpl->set('s', 'SUMINTHISLANGUAGE', $inThisLanguage);
 774:     $tpl->next();
 775: }
 776: 
 777: /**
 778:  * Generates a top<n> statistics page
 779:  *
 780:  * @param unknown_type $yearmonth
 781:  *         Specifies the year and month from which to retrieve the statistics,
 782:  *         specify "current" to retrieve the current entries.
 783:  * @param unknown_type $top
 784:  *         Specifies the amount of pages to display
 785:  */
 786: function statsOverviewTop($yearmonth, $top) {
 787:     global $cfg, $db, $tpl, $client, $cfgClient, $lang;
 788: 
 789:     if (strcmp($yearmonth, "current") == 0) {
 790:         $sql = "SELECT DISTINCT
 791:                     C.title, A.visited, C.idart
 792:                 FROM
 793:                     " . $cfg["tab"]["stat"] . " AS A,
 794:                     " . $cfg["tab"]["cat_art"] . " AS B,
 795:                     " . $cfg["tab"]["art_lang"] . " AS C
 796:                 WHERE
 797:                     C.idart = B.idart AND
 798:                     C.idlang = A.idlang AND
 799:                     B.idcatart = A.idcatart AND
 800:                     A.idclient = " . cSecurity::toInteger($client) . " AND
 801:                     A.idlang = " . cSecurity::toInteger($lang) . "
 802:                 ORDER BY A.visited DESC
 803:                 LIMIT " . $db->escape($top);
 804:     } else {
 805:         $sql = "SELECT DISTINCT
 806:                     C.title, A.visited, B.idcat, C.idart
 807:                 FROM
 808:                     " . $cfg["tab"]["stat_archive"] . " AS A,
 809:                     " . $cfg["tab"]["cat_art"] . " AS B,
 810:                     " . $cfg["tab"]["art_lang"] . " AS C
 811:                 WHERE
 812:                     C.idart = B.idart AND
 813:                     C.idlang = A.idlang AND
 814:                     B.idcatart = A.idcatart AND
 815:                     A.idclient = " . cSecurity::toInteger($client) . " AND
 816:                     A.archived = '" . $db->escape($yearmonth) . "' AND
 817:                     A.idlang = " . cSecurity::toInteger($lang) . " ORDER BY
 818:                     A.visited DESC
 819:                 LIMIT " . $db->escape($top);
 820:     }
 821: 
 822:     $db->query($sql);
 823: 
 824:     $frontendURL = cRegistry::getFrontendUrl();
 825:     while ($db->nextRecord()) {
 826:         $cat_name = "";
 827:         statCreateLocationString($db->f(2), "&nbsp;/&nbsp;", $cat_name);
 828:         $tpl->set('d', 'PADDING_LEFT', '5');
 829:         $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
 830:         $tpl->set('d', 'TEXT', conHtmlSpecialChars($db->f(0)) . ' (idart: ' . cSecurity::toInteger($db->f('idart')) . ')');
 831:         $tpl->set('d', 'TOTAL', $db->f(1));
 832:         $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $db->f('idart'));
 833:         $tpl->next();
 834:     }
 835: }
 836: 
 837: /**
 838:  * Generates the location string for passed category id.
 839:  *
 840:  * Performs a recursive call, if parent category doesn't matches to 0
 841:  *
 842:  * @param int $idcat
 843:  *         The category id
 844:  * @param string $seperator
 845:  *         Separator for location string
 846:  * @param string $cat_str
 847:  *         The location string variable (reference)
 848:  */
 849: function statCreateLocationString($idcat, $seperator, &$cat_str) {
 850:     $cats = array();
 851: 
 852:     // get category path
 853:     $helper = cCategoryHelper::getInstance();
 854:     foreach ($helper->getCategoryPath($idcat) as $categoryLang) {
 855:         $cats[] = $categoryLang->getField('name');
 856:     }
 857: 
 858:     $cat_str = implode($seperator, $cats);
 859: }
 860: 
 861: /**
 862:  * Generates a top<n> statistics page
 863:  *
 864:  * @param int $year
 865:  *         Specifies the year from which to retrieve the statistics
 866:  * @param int $top
 867:  *         Specifies the amount of pages to display
 868:  */
 869: function statsOverviewTopYear($year, $top) {
 870:     global $cfg, $db, $tpl, $client, $lang, $cfgClient;
 871: 
 872:     $sql = "SELECT
 873:                 C.title, SUM(A.visited) as visited, B.idcat AS idcat, C.idart AS idart
 874:             FROM
 875:                 " . $cfg["tab"]["stat_archive"] . " AS A,
 876:                 " . $cfg["tab"]["cat_art"] . " AS B,
 877:                 " . $cfg["tab"]["art_lang"] . " AS C
 878:             WHERE
 879:                 C.idart = B.idart AND
 880:                 C.idlang = A.idlang AND
 881:                 B.idcatart = A.idcatart AND
 882:                 A.idclient = " . cSecurity::toInteger($client) . " AND
 883:                 A.archived LIKE '" . $db->escape($year) . "%' AND
 884:                 A.idlang = " . cSecurity::toInteger($lang) . "
 885:             GROUP BY A.idcatart
 886:             ORDER BY visited DESC
 887:             LIMIT " . $db->escape($top);
 888: 
 889:     $db->query($sql);
 890:     $frontendURL = cRegistry::getFrontendUrl();
 891:     while ($db->nextRecord()) {
 892:         $cat_name = '';
 893:         statCreateLocationString($db->f('idcat'), "&nbsp;/&nbsp;", $cat_name);
 894: 
 895:         $tpl->set('d', 'PADDING_LEFT', '0');
 896:         $tpl->set('d', 'PATH', i18n("Path") . ":&nbsp;/&nbsp;" . $cat_name);
 897:         $tpl->set('d', 'TEXT', conHtmlSpecialChars($db->f(0)) . ' (idart: ' . cSecurity::toInteger($db->f('idart')) . ')');
 898:         $tpl->set('d', 'TOTAL', $db->f(1));
 899:         $tpl->set('d', 'ULR_TO_PAGE', $frontendURL . 'front_content.php?idart=' . $db->f('idart'));
 900:         $tpl->next();
 901:     }
 902: }
 903: 
 904: /**
 905:  * Returns a drop down to choose the stats to display
 906:  *
 907:  * @param string $default
 908:  * @return string
 909:  *         Returns a drop down string
 910:  */
 911: function statDisplayTopChooser($default) {
 912:     if ($default == "top10") {
 913:         $defaultTop10 = "selected";
 914:     }
 915:     if ($default == "top20") {
 916:         $defaultTop20 = "selected";
 917:     }
 918:     if ($default == "top30") {
 919:         $defaultTop30 = "selected";
 920:     }
 921:     if ($default == "all") {
 922:         $defaultAll = "selected";
 923:     }
 924: 
 925:     return ("<form name=\"name\">" .
 926:             "  <select class=\"text_medium\" onchange=\"top10Action(this)\">" .
 927:             "    <option value=\"top10\" $defaultTop10>" . i18n("Top 10") . "</option>" .
 928:             "    <option value=\"top20\" $defaultTop20>" . i18n("Top 20") . "</option>" .
 929:             "    <option value=\"top30\" $defaultTop30>" . i18n("Top 30") . "</option>" .
 930:             "    <option value=\"all\" $defaultAll>" . i18n("All") . "</option>" .
 931:             "  </select>" .
 932:             "</form>");
 933: }
 934: 
 935: /**
 936:  * Returns a drop down to choose the stats to display for yearly summary pages
 937:  *
 938:  * @param string $default
 939:  * @return string
 940:  *         Returns a drop down string
 941:  */
 942: function statDisplayYearlyTopChooser($default) {
 943:     if ($default == "top10") {
 944:         $defaultTop10 = "selected";
 945:     }
 946:     if ($default == "top20") {
 947:         $defaultTop20 = "selected";
 948:     }
 949:     if ($default == "top30") {
 950:         $defaultTop30 = "selected";
 951:     }
 952:     if ($default == "all") {
 953:         $defaultAll = "selected";
 954:     }
 955: 
 956:     return ("<form name=\"name\">" .
 957:             "  <select class=\"text_medium\" onchange=\"top10ActionYearly(this)\">" .
 958:             "    <option value=\"top10\" $defaultTop10>" . i18n("Top 10") . "</option>" .
 959:             "    <option value=\"top20\" $defaultTop20>" . i18n("Top 20") . "</option>" .
 960:             "    <option value=\"top30\" $defaultTop30>" . i18n("Top 30") . "</option>" .
 961:             "    <option value=\"all\" $defaultAll>" . i18n("All") . "</option>" .
 962:             "  </select>" .
 963:             "</form>");
 964: }
 965: 
 966: /**
 967:  * Return an array with all years which are available as stat files.
 968:  *
 969:  * @param int $client
 970:  * @param int $lang
 971:  * @return array
 972:  *         Array of strings with years.
 973:  * @return array
 974:  */
 975: function statGetAvailableYears($client, $lang) {
 976:     global $cfg, $db;
 977: 
 978:     $sql = "SELECT
 979:                 SUBSTRING(`archived`, 1, 4)
 980:             FROM
 981:                 " . $cfg["tab"]["stat_archive"] . "
 982:             WHERE
 983:                 idlang = " . cSecurity::toInteger($lang) . " AND
 984:                 idclient = " . cSecurity::toInteger($client) . "
 985:             GROUP BY
 986:                 SUBSTRING(`archived`, 1, 4)
 987:             ORDER BY
 988:                 SUBSTRING(`archived`, 1, 4) DESC";
 989:     $db->query($sql);
 990: 
 991:     $availableYears = array();
 992:     while ($db->nextRecord()) {
 993:         $availableYears[] = $db->f(0);
 994:     }
 995: 
 996:     return $availableYears;
 997: }
 998: 
 999: /**
1000:  * Return an array with all months for a specific year which are available
1001:  * as stat files.
1002:  *
1003:  * @param string $year
1004:  * @param int $client
1005:  * @param int $lang
1006:  * @return array
1007:  *         Array of strings with months.
1008:  */
1009: function statGetAvailableMonths($year, $client, $lang) {
1010:     global $cfg, $db;
1011: 
1012:     $availableYears = array();
1013: 
1014:     $sql = "SELECT
1015:                 SUBSTRING(`archived`, 5, 2)
1016:             FROM
1017:                 " . $cfg["tab"]["stat_archive"] . "
1018:             WHERE
1019:                 idlang = " . cSecurity::toInteger($lang) . " AND
1020:                 idclient = " . cSecurity::toInteger($client) . " AND
1021:                 SUBSTRING(`archived`, 1, 4) = '" . $db->escape($year) . "'
1022:             GROUP BY
1023:                 SUBSTRING(`archived`, 5, 2)
1024:             ORDER BY
1025:                 SUBSTRING(`archived`, 5, 2) DESC";
1026: 
1027:     $db->query($sql);
1028:     while ($db->nextRecord()) {
1029:         $availableYears[] = $db->f(0);
1030:     }
1031: 
1032:     return $availableYears;
1033: }
1034: 
1035: /**
1036:  * Resets the statistic for passed client
1037:  *
1038:  * @param int $client
1039:  *         Id of client
1040:  */
1041: function statResetStatistic($client) {
1042:     global $db, $cfg;
1043:     $sql = "UPDATE " . $cfg["tab"]["stat"] . " SET visited=0 WHERE idclient=" . cSecurity::toInteger($client);
1044:     $db->query($sql);
1045: }
1046: 
1047: /**
1048:  * Deletes existing heap table (table in memory) and creates it.
1049:  *
1050:  * @param string $sHeapTable
1051:  *         Table name
1052:  * @param cDb $db
1053:  *         Database object
1054:  */
1055: function buildHeapTable($sHeapTable, $db) {
1056:     global $cfg;
1057: 
1058:     $sql = "DROP TABLE IF EXISTS " . $db->escape($sHeapTable) . ";";
1059:     $db->query($sql);
1060: 
1061:     $sql = "CREATE TABLE " . $db->escape($sHeapTable) . " TYPE=HEAP
1062:                 SELECT
1063:                     A.idcatart,
1064:                     A.idcat,
1065:                     A.idart,
1066:                     B.idstatarch,
1067:                     B.archived,
1068:                     B.idlang,
1069:                     B.idclient,
1070:                     B.visited
1071:                 FROM
1072:                     " . $cfg['tab']['cat_art'] . " AS A, " . $cfg['tab']['stat_archive'] . " AS B
1073:                 WHERE
1074:                     A.idcatart = B.idcatart;";
1075:     $db->query($sql);
1076: 
1077:     $sql = "ALTER TABLE `" . $db->escape($sHeapTable) . "` ADD PRIMARY KEY (`idcatart`,`idcat` ,`idart`,`idstatarch` ,`archived`,`idlang`,`idclient` ,`visited`);";
1078:     $db->query($sql);
1079: }
1080: