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