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