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