PageRenderTime 50ms CodeModel.GetById 8ms RepoModel.GetById 0ms app.codeStats 1ms

/lib/magirc/denora/Denora.class.php

https://github.com/CerberusStyle/magirc
PHP | 1037 lines | 773 code | 52 blank | 212 comment | 158 complexity | 02d72096252479506f7ddaeb46e5ddd2 MD5 | raw file
Possible License(s): GPL-3.0, LGPL-2.1, MPL-2.0-no-copyleft-exception
  1. <?php
  2. // Database configuration
  3. class Denora_DB extends DB {
  4. private static $instance = NULL;
  5. public static function getInstance() {
  6. if (is_null(self::$instance) === true) {
  7. // Check the database configuration
  8. $db = null;
  9. $error = false;
  10. $config_file = PATH_ROOT . 'conf/denora.cfg.php';
  11. if (file_exists($config_file)) {
  12. include($config_file);
  13. } else {
  14. $error = true;
  15. }
  16. if ($error || !is_array($db)) {
  17. die('<strong>MagIRC</strong> is not properly configured<br />Please configure the Denora database in the <a href="admin/">Admin Panel</a>');
  18. }
  19. $dsn = "mysql:dbname={$db['database']};host={$db['hostname']}";
  20. $args = array();
  21. if (isset($db['ssl']) && $db['ssl_key']) $args[PDO::MYSQL_ATTR_SSL_KEY] = $db['ssl_key'];
  22. if (isset($db['ssl']) && $db['ssl_cert']) $args[PDO::MYSQL_ATTR_SSL_CERT] = $db['ssl_cert'];
  23. if (isset($db['ssl']) && $db['ssl_ca']) $args[PDO::MYSQL_ATTR_SSL_CA] = $db['ssl_ca'];
  24. self::$instance = new DB($dsn, $db['username'], $db['password'], $args);
  25. if (self::$instance->error) die('Error opening the Denora database<br />' . self::$instance->error);
  26. }
  27. return self::$instance;
  28. }
  29. }
  30. class Denora {
  31. private $db;
  32. private $cfg;
  33. function __construct() {
  34. // Get the ircd
  35. $ircd_file = PATH_ROOT . "lib/magirc/denora/protocol/" . IRCD . ".inc.php";
  36. if (file_exists($ircd_file)) {
  37. require_once($ircd_file);
  38. } else {
  39. die('<strong>MagIRC</strong> is not properly configured<br />Please configure the ircd in the <a href="admin/">Admin Panel</a>');
  40. }
  41. // Load the required classes
  42. $this->db = Denora_db::getInstance();
  43. $this->cfg = new Config();
  44. require_once(__DIR__ . '/Objects.class.php');
  45. }
  46. /**
  47. * Returns the current status
  48. * @return array of arrays (int val, int time)
  49. */
  50. function getCurrentStatus() {
  51. $query = "SELECT type, val, FROM_UNIXTIME(time) AS time FROM current";
  52. $this->db->query($query, SQL_ALL, SQL_ASSOC);
  53. $result = $this->db->record;
  54. $data = array();
  55. foreach ($result as $row) {
  56. $data[$row["type"]] = array('val' => (int) $row["val"], 'time' => $row['time']);
  57. }
  58. return $data;
  59. }
  60. /**
  61. * Returns the ma values
  62. * @return array of arrays (int val, int time)
  63. */
  64. function getMaxValues() {
  65. $query = "SELECT type, val, time FROM maxvalues";
  66. $this->db->query($query, SQL_ALL, SQL_ASSOC);
  67. $result = $this->db->record;
  68. $data = array();
  69. foreach ($result as $row) {
  70. $data[$row["type"]] = array('val' => (int) $row["val"], 'time' => $row['time']);
  71. }
  72. return $data;
  73. }
  74. /**
  75. * Return the mode formatted for SQL
  76. * Example: o -> mode_lo, C -> mode_uc
  77. * @param string $mode Mode
  78. * @return string SQL Mode
  79. */
  80. public static function getSqlMode($mode) {
  81. if (!$mode) {
  82. return null;
  83. } elseif (strtoupper($mode) === $mode) {
  84. return "mode_u" . strtolower($mode);
  85. } else {
  86. return "mode_l" . strtolower($mode);
  87. }
  88. }
  89. /**
  90. * Return the mode data formatted for SQL
  91. * Example: o -> mode_lo_data, C -> mode_uc_data
  92. * @param string $mode Mode
  93. * @return string SQL Mode data
  94. */
  95. public static function getSqlModeData($mode) {
  96. $sql_mode = Denora::getSqlMode($mode);
  97. return $sql_mode ? $sql_mode . "_data" : null;
  98. }
  99. /**
  100. * Get the global or channel-specific user count
  101. * @param string $mode Mode ('server', 'channel', null: global)
  102. * @param string $target Target (channel or server name, depends on $mode)
  103. * @return int User count
  104. */
  105. function getUserCount($mode = null, $target = null) {
  106. $query = "SELECT COUNT(*) FROM user
  107. JOIN server ON server.servid = user.servid";
  108. if ($mode == 'channel' && $target) {
  109. $query .= " JOIN ison ON ison.nickid = user.nickid
  110. JOIN chan ON chan.chanid = ison.chanid
  111. WHERE LOWER(chan.channel)=LOWER(:chan) AND user.online = 'Y'";
  112. } elseif ($mode == 'server' && $target) {
  113. $query .= " WHERE LOWER(user.server)=LOWER(:server)
  114. AND user.online='Y'";
  115. } else {
  116. $query .= " WHERE user.online = 'Y'";
  117. }
  118. if ($this->cfg->hide_ulined) $query .= " AND server.uline = 0";
  119. if (Protocol::services_protection_mode) {
  120. $query .= sprintf(" AND user.%s='N'", Denora::getSqlMode(Protocol::services_protection_mode));
  121. }
  122. $stmt = $this->db->prepare($query);
  123. if ($mode == 'channel' && $target) $stmt->bindParam(':chan', $target, PDO::PARAM_STR);
  124. if ($mode == 'server' && $target) $stmt->bindParam(':server', $target, PDO::PARAM_STR);
  125. $stmt->execute();
  126. return $stmt->fetch(PDO::FETCH_COLUMN);
  127. }
  128. /**
  129. * Get CTCP/GeoIP statistics for use by pie charts
  130. * @param string $type Type ('clients': client stats, 'countries': country stats)
  131. * @param string $mode Mode ('server', 'channel', null: global)
  132. * @param string $target Target (channel or server name, depends on $mode)
  133. * @return array Data
  134. */
  135. private function getPieStats($type, $mode = null, $target = null) {
  136. $query = "SELECT ";
  137. if ($type == 'clients') {
  138. $type = 'ctcpversion';
  139. $query .= " user.ctcpversion AS client, ";
  140. } else {
  141. $type = 'country';
  142. $query .= " user.country, user.countrycode AS country_code, ";
  143. }
  144. $query .= "COUNT(*) AS count FROM user
  145. JOIN server ON server.servid = user.servid";
  146. if ($mode == 'channel' && $target) {
  147. $query .= " JOIN ison ON ison.nickid = user.nickid
  148. JOIN chan ON chan.chanid = ison.chanid
  149. WHERE LOWER(chan.channel)=LOWER(:chan)
  150. AND user.online='Y'";
  151. } elseif ($mode == 'server' && $target) {
  152. $query .= " WHERE LOWER(user.server)=LOWER(:server)
  153. AND user.online='Y'";
  154. } else {
  155. $query .= " WHERE user.online='Y'";
  156. }
  157. if ($this->cfg->hide_ulined) $query .= " AND server.uline = 0";
  158. if (Protocol::services_protection_mode) {
  159. $query .= sprintf(" AND user.%s='N'", Denora::getSqlMode(Protocol::services_protection_mode));
  160. }
  161. $query .= " GROUP by user.$type ORDER BY count DESC";
  162. $stmt = $this->db->prepare($query);
  163. if ($mode == 'channel' && $target) $stmt->bindParam(':chan', $target, PDO::PARAM_STR);
  164. if ($mode == 'server' && $target) $stmt->bindParam(':server', $target, PDO::PARAM_STR);
  165. $stmt->execute();
  166. return $stmt->fetchAll(PDO::FETCH_ASSOC);
  167. }
  168. /**
  169. * Get CTCP client statistics
  170. * @param string $mode Mode ('server', 'channel', null: global)
  171. * @param string $target Target
  172. * @return array Data
  173. */
  174. function getClientStats($mode = null, $target = null) {
  175. return $this->getPieStats('clients', $mode, $target);
  176. }
  177. /**
  178. * Get GeoIP country statistics
  179. * @param string $mode Mode ('server', 'channel', null: global)
  180. * @param string $target Target
  181. * @return array Data
  182. */
  183. function getCountryStats($mode = null, $target = null) {
  184. return $this->getPieStats('countries', $mode, $target);
  185. }
  186. /**
  187. * Prepare data for use by country pie charts
  188. * @param array $result Array of data
  189. * @param type $sum user count
  190. * @return array of arrays (string 'name', int 'count', double 'y')
  191. */
  192. function makeCountryPieData($result, $sum) {
  193. $data = array();
  194. $unknown = 0;
  195. $other = 0;
  196. foreach ($result as $val) {
  197. $percent = round($val["count"] / $sum * 100, 2);
  198. if ($percent < 2) {
  199. $other += $val["count"];
  200. } elseif (in_array ($val['country'], array(null, "", "Unknown", "localhost"))) {
  201. $unknown += $val["count"];
  202. } else {
  203. $data[] = array('name' => $val['country'], 'count' => $val["count"], 'y' => $percent);
  204. }
  205. }
  206. if ($unknown > 0) {
  207. $data[] = array('name' => T_gettext('Unknown'), 'count' => $unknown, 'y' => round($unknown / $sum * 100, 2));
  208. }
  209. if ($other > 0) {
  210. $data[] = array('name' => T_gettext('Other'), 'count' => $other, 'y' => round($other / $sum * 100, 2));
  211. }
  212. return $data;
  213. }
  214. /**
  215. * Prepare data for use by client pie charts
  216. * @param array $result Array of data
  217. * @param type $sum user count
  218. * @return array (clients => (name, count, y), versions (name, version, cat, count, y))
  219. */
  220. function makeClientPieData($result, $sum) {
  221. $clients = array();
  222. foreach ($result as $client) {
  223. // Determine client name and version
  224. $matches = array();
  225. preg_match('/^(.*?)\s*(\S*\d\S*)/', str_replace(array('(',')','[',']','{','}'), '', $client['client']), $matches);
  226. if (count($matches) == 3) {
  227. $name = $matches[1];
  228. $version = $matches[2][0] == 'v' ? substr($matches[2], 1) : $matches[2];
  229. } else {
  230. $name = $client['client'] ? $client['client'] : T_gettext('Unknown');
  231. $version = '';
  232. }
  233. $name = trim($name);
  234. $version = trim($version);
  235. // Categorize the versions
  236. if (!array_key_exists($name, $clients)) {
  237. $clients[$name] = array('count' => $client['count'], 'versions' => array());
  238. } else {
  239. $clients[$name]['count'] += $client['count'];
  240. }
  241. if (isset($clients[$name]['versions'][$version])) {
  242. $clients[$name]['versions'][$version] += $client['count'];
  243. } else {
  244. $clients[$name]['versions'][$version] = $client['count'];
  245. }
  246. }
  247. // Sort by count descending
  248. uasort($clients, function($a, $b) {
  249. return $a['count'] < $b['count'];
  250. });
  251. foreach ($clients as $key => $val) {
  252. arsort($clients[$key]['versions']);
  253. unset($val);
  254. }
  255. // Prepare data for output
  256. $min_count = ceil($sum / 300);
  257. $data = array('clients' => array(), 'versions' => array());
  258. $other = array('count' => 0, 'versions' => array());
  259. $other_various = 0;
  260. foreach ($clients as $name => $client) {
  261. $percent = round($client['count'] / $sum * 100, 2);
  262. if ($percent < 2 || $name == T_gettext('Unknown')) { // Too small or unknown
  263. $other['count'] += $client['count'];
  264. foreach ($client['versions'] as $version => $count) {
  265. if ($count < $min_count) {
  266. $other_various += $count;
  267. } else {
  268. $other['versions'][] = array('name' => $name, 'version' => $version, 'cat' => T_gettext('Other'), 'count' => (int) $count, 'y' => (double) round($count / $sum * 100, 2));
  269. }
  270. }
  271. } else {
  272. $data_various = 0;
  273. $data['clients'][] = array('name' => $name, 'count' => (int) $client['count'], 'y' => (double) $percent);
  274. foreach ($client['versions'] as $version => $count) {
  275. if ($count < $min_count) {
  276. $data_various += $count;
  277. } else {
  278. $data['versions'][] = array('name' => $name, 'version' => $version, 'cat' => $name, 'count' => (int) $count, 'y' => (double) round($count / $sum * 100, 2));
  279. }
  280. }
  281. if ($data_various) {
  282. $data['versions'][] = array('name' => $name, 'version' => '('.T_gettext('various').')', 'cat' => $name, 'count' => (int) $data_various, 'y' => (double) round($data_various / $sum * 100, 2));
  283. }
  284. }
  285. }
  286. if ($other_various) {
  287. $other['versions'][] = array('name' => T_gettext('Various'), 'version' => '', 'cat' => T_gettext('Other'), 'count' => (int) $other_various, 'y' => (double) round($other_various / $sum * 100, 2));;
  288. }
  289. // Append other slices
  290. if ($other['count'] > 0) {
  291. $other['percent'] = round($other['count'] / $sum * 100, 2);
  292. $data['clients'][] = array('name' => T_gettext('Other'), 'count' => (int) $other['count'], 'y' => (double) $other['percent']);
  293. $data['versions'] = array_merge($data['versions'], $other['versions']);
  294. }
  295. #echo "<pre>"; print_r($data); exit;
  296. return $data;
  297. }
  298. /**
  299. * Get hourly user/channel/server stats
  300. * @param string $table 'users', 'channels', 'servers'
  301. * @return array of arrays (int milliseconds, int value)
  302. */
  303. function getHourlyStats($table) {
  304. switch ($table) {
  305. case 'users': $table = 'stats'; break;
  306. case 'channels': $table = 'channelstats'; break;
  307. case 'servers': $table = 'serverstats'; break;
  308. default: return null;
  309. }
  310. $query = "SELECT * FROM {$table} ORDER BY year ASC, month ASC, day ASC";
  311. $stmt = $this->db->prepare($query);
  312. $stmt->execute();
  313. $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
  314. $data = array();
  315. foreach ($result as $val) {
  316. $date = "{$val['year']}-{$val['month']}-{$val['day']}";
  317. for ($i = 0; $i < 24; $i++) {
  318. $data[] = array(strtotime("{$date} {$i}:00:00") * 1000, $val["time_" . $i] ? (int) $val["time_" . $i] : null);
  319. }
  320. }
  321. return $data;
  322. }
  323. /**
  324. * Gets a list of servers
  325. * @return array of Server
  326. */
  327. function getServerList() {
  328. $sWhere = "";
  329. $hide_servers = $this->cfg->hide_servers;
  330. if ($hide_servers) {
  331. $hide_servers = explode(",", $hide_servers);
  332. foreach ($hide_servers as $key => $server) {
  333. $hide_servers[$key] = $this->db->escape(trim($server));
  334. }
  335. $sWhere .= sprintf("WHERE server NOT IN(%s)", implode(",", $hide_servers));
  336. }
  337. if ($this->cfg->hide_ulined) {
  338. $sWhere .= $sWhere ? " AND uline = 0" : "WHERE uline = 0";
  339. }
  340. $query = "SELECT server, online, comment AS description, currentusers AS users, opers";
  341. if ($this->cfg->denora_version > '1.4') {
  342. $query .= ", country, countrycode AS country_code";
  343. }
  344. $query .= " FROM server $sWhere";
  345. $stmt = $this->db->prepare($query);
  346. $stmt->execute();
  347. return $stmt->fetchAll(PDO::FETCH_CLASS, 'Server');
  348. }
  349. /**
  350. * Gets a server
  351. * @param string $server Server name
  352. * @return Server
  353. */
  354. function getServer($server) {
  355. $query = "SELECT server, online, comment AS description, connecttime AS connect_time, lastsplit AS split_time, version,
  356. uptime, motd, currentusers AS users, maxusers AS users_max, FROM_UNIXTIME(maxusertime) AS users_max_time, ping, highestping AS ping_max,
  357. FROM_UNIXTIME(maxpingtime) AS ping_max_time, opers, maxopers AS opers_max, FROM_UNIXTIME(maxopertime) AS opers_max_time";
  358. if ($this->cfg->denora_version > '1.4') {
  359. $query .= ", country, countrycode AS country_code";
  360. }
  361. $query .= " FROM server WHERE server = :server";
  362. $stmt = $this->db->prepare($query);
  363. $stmt->bindParam(':server', $server, PDO::PARAM_STR);
  364. $stmt->execute();
  365. return $stmt->fetchObject('Server');
  366. }
  367. /**
  368. * Get the list of Operators currently online
  369. * @return array of User
  370. */
  371. function getOperatorList() {
  372. $query = sprintf("SELECT u.nick AS nickname, u.realname, u.hostname, u.hiddenhostname AS hostname_cloaked, u.swhois,
  373. u.username, u.connecttime AS connect_time, u.server, u.away, u.awaymsg AS away_msg, u.ctcpversion AS client, u.online,
  374. u.lastquit AS quit_time, u.lastquitmsg AS quit_msg, u.countrycode AS country_code, u.country, s.uline AS service, %s",
  375. implode(',', array_map(array('Denora', 'getSqlMode'), str_split(Protocol::user_modes))));
  376. if ($this->cfg->denora_version > '1.4') {
  377. $query .= ", s.country AS server_country, s.countrycode AS server_country_code";
  378. }
  379. $query .= " FROM user u LEFT JOIN server s ON s.servid = u.servid WHERE";
  380. $levels = Protocol::$oper_levels;
  381. if (!empty($levels)) {
  382. $i = 1;
  383. $query .= " (";
  384. foreach ($levels as $mode => $level) {
  385. $mode = Denora::getSqlMode($mode);
  386. $query .= "u.$mode = 'Y'";
  387. if ($i < count($levels)) {
  388. $query .= " OR ";
  389. }
  390. $i++;
  391. }
  392. $query .= ")";
  393. } else {
  394. $query .= " u.mode_lo = 'Y'";
  395. }
  396. $query .= " AND u.online = 'Y'";
  397. if (Protocol::oper_hidden_mode) $query .= " AND u." . Denora::getSqlMode(Protocol::oper_hidden_mode) . " = 'N'";
  398. if (Protocol::services_protection_mode) $query .= " AND u." . Denora::getSqlMode(Protocol::services_protection_mode) . " = 'N'";
  399. $query .= " AND u.server = s.server";
  400. if ($this->cfg->hide_ulined) $query .= " AND s.uline = '0'";
  401. $query .= " ORDER BY u.nick ASC";
  402. $stmt = $this->db->prepare($query);
  403. $stmt->execute();
  404. return $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
  405. }
  406. /**
  407. * Gets the list of current channels
  408. * @param boolean $datatables Set true to enable server-side datatables functionality
  409. * @return array of Channel
  410. */
  411. function getChannelList($datatables = false) {
  412. $secret_mode = Protocol::chan_secret_mode;
  413. $private_mode = Protocol::chan_private_mode;
  414. $sWhere = "currentusers > 0";
  415. if ($secret_mode) {
  416. $sWhere .= sprintf(" AND %s='N'", Denora::getSqlMode($secret_mode));
  417. }
  418. if ($private_mode) {
  419. $sWhere .= sprintf(" AND %s='N'", Denora::getSqlMode($private_mode));
  420. }
  421. $hide_channels = $this->cfg->hide_chans;
  422. if ($hide_channels) {
  423. $hide_channels = explode(",", $hide_channels);
  424. foreach ($hide_channels as $key => $channel) {
  425. $hide_channels[$key] = $this->db->escape(trim(strtolower($channel)));
  426. }
  427. $sWhere .= sprintf("%s LOWER(channel) NOT IN(%s)", $sWhere ? " AND " : "WHERE ", implode(",", $hide_channels));
  428. }
  429. $sQuery = sprintf("SELECT SQL_CALC_FOUND_ROWS channel, currentusers AS users, maxusers AS users_max, maxusertime AS users_max_time,
  430. topic, topicauthor AS topic_author, topictime AS topic_time, kickcount AS kicks, %s, %s FROM chan WHERE %s",
  431. implode(',', array_map(array('Denora', 'getSqlMode'), str_split(Protocol::chan_modes))),
  432. implode(',', array_map(array('Denora', 'getSqlModeData'), str_split(Protocol::chan_modes_data))), $sWhere);
  433. if ($datatables) {
  434. $iTotal = $this->db->datatablesTotal($sQuery);
  435. $sFiltering = $this->db->datatablesFiltering(array('channel', 'topic'));
  436. $sOrdering = $this->db->datatablesOrdering(array('channel', 'currentusers', 'maxusers'));
  437. $sPaging = $this->db->datatablesPaging();
  438. $sQuery .= sprintf(" %s %s %s", $sFiltering ? "AND " . $sFiltering : "", $sOrdering, $sPaging);
  439. } else {
  440. $sQuery .= " ORDER BY `channel` ASC";
  441. }
  442. $ps = $this->db->prepare($sQuery);
  443. $ps->execute();
  444. $aaData = $ps->fetchAll(PDO::FETCH_CLASS, 'Channel');
  445. if ($datatables) {
  446. $iFilteredTotal = $this->db->foundRows();
  447. return $this->db->datatablesOutput($iTotal, $iFilteredTotal, $aaData);
  448. }
  449. return $aaData;
  450. }
  451. /**
  452. * Get the biggest current channels
  453. * @param int $limit
  454. * @return array of Channel
  455. */
  456. function getChannelBiggest($limit = 10) {
  457. $secret_mode = Protocol::chan_secret_mode;
  458. $private_mode = Protocol::chan_private_mode;
  459. $query = "SELECT channel, currentusers AS users, maxusers AS users_max, maxusertime AS users_max_time FROM chan WHERE currentusers > 0";
  460. if ($secret_mode) {
  461. $query .= sprintf(" AND %s='N'", Denora::getSqlMode($secret_mode));
  462. }
  463. if ($private_mode) {
  464. $query .= sprintf(" AND %s='N'", Denora::getSqlMode($private_mode));
  465. }
  466. $hide_chans = explode(",", $this->cfg->hide_chans);
  467. for ($i = 0; $i < count($hide_chans); $i++) {
  468. $query .= " AND LOWER(channel) NOT LIKE " . $this->db->escape(strtolower($hide_chans[$i]));
  469. }
  470. $query .= " ORDER BY currentusers DESC LIMIT :limit";
  471. $ps = $this->db->prepare($query);
  472. $ps->bindParam(':limit', $limit, PDO::PARAM_INT);
  473. $ps->execute();
  474. return $ps->fetchAll(PDO::FETCH_CLASS, 'Channel');
  475. }
  476. /**
  477. * Get the most active current channels
  478. * @param int $limit
  479. * @return array of channel stats
  480. */
  481. function getChannelTop($limit = 10) {
  482. $secret_mode = Protocol::chan_secret_mode;
  483. $private_mode = Protocol::chan_private_mode;
  484. $query = "SELECT chan AS channel, line AS 'lines' FROM cstats, chan WHERE BINARY LOWER(cstats.chan)=LOWER(chan.channel) AND cstats.type=1 AND cstats.line >= 1";
  485. if ($secret_mode) {
  486. $query .= sprintf(" AND chan.%s='N'", Denora::getSqlMode($secret_mode));
  487. }
  488. if ($private_mode) {
  489. $query .= sprintf(" AND chan.%s='N'", Denora::getSqlMode($private_mode));
  490. }
  491. $hide_chans = explode(",", $this->cfg->hide_chans);
  492. for ($i = 0; $i < count($hide_chans); $i++) {
  493. $query .= " AND cstats.chan NOT LIKE " . $this->db->escape(strtolower($hide_chans[$i]));
  494. }
  495. $query .= " ORDER BY cstats.line DESC LIMIT :limit";
  496. $ps = $this->db->prepare($query);
  497. $ps->bindParam(':limit', $limit, PDO::PARAM_INT);
  498. $ps->execute();
  499. return $ps->fetchAll(PDO::FETCH_ASSOC);
  500. }
  501. /**
  502. * Get the most active current users
  503. * @param int $limit
  504. * @return array of user stats
  505. */
  506. function getUsersTop($limit = 10) {
  507. $aaData = array();
  508. $ps = $this->db->prepare("SELECT uname, line AS 'lines' FROM ustats WHERE type = 1 AND chan='global' AND line >= 1 ORDER BY line DESC LIMIT :limit");
  509. $ps->bindParam(':limit', $limit, PDO::PARAM_INT);
  510. $ps->execute();
  511. $data = $ps->fetchAll(PDO::FETCH_ASSOC);
  512. if (is_array($data)) {
  513. foreach ($data as $row) {
  514. $user = $this->getUser('stats', $row['uname']);
  515. if (!$user) $user = new User();
  516. $user->uname = $row['uname'];
  517. $user->lines = $row['lines'];
  518. $aaData[] = $user;
  519. }
  520. }
  521. return $aaData;
  522. }
  523. /**
  524. * Get the specified channel
  525. * @param string $chan Channel
  526. * @return Channel
  527. */
  528. function getChannel($chan) {
  529. $sQuery = sprintf("SELECT channel, currentusers AS users, maxusers AS users_max, maxusertime AS users_max_time,
  530. topic, topicauthor AS topic_author, topictime AS topic_time, kickcount AS kicks, %s, %s
  531. FROM chan WHERE BINARY LOWER(channel) = LOWER(:chan)",
  532. implode(',', array_map(array('Denora', 'getSqlMode'), str_split(Protocol::chan_modes))),
  533. implode(',', array_map(array('Denora', 'getSqlModeData'), str_split(Protocol::chan_modes_data))));
  534. $ps = $this->db->prepare($sQuery);
  535. $ps->bindParam(':chan', $chan, PDO::PARAM_STR);
  536. $ps->execute();
  537. return $ps->fetchObject('Channel');
  538. }
  539. /**
  540. * Checks if given channel can be displayed
  541. * @param string $chan
  542. * @return int code (200: OK, 404: not existing, 403: denied)
  543. */
  544. function checkChannel($chan) {
  545. $noshow = array();
  546. $no = explode(",", $this->cfg->hide_chans);
  547. for ($i = 0; $i < count($no); $i++) {
  548. $noshow[$i] = strtolower($no[$i]);
  549. }
  550. if (in_array(strtolower($chan), $noshow))
  551. return 403;
  552. $stmt = $this->db->prepare("SELECT * FROM `chan` WHERE BINARY LOWER(`channel`) = LOWER(:channel)");
  553. $stmt->bindParam(':channel', $chan, SQL_STR);
  554. $stmt->execute();
  555. $data = $stmt->fetch();
  556. if (!$data) {
  557. return 404;
  558. } else {
  559. if ($this->cfg->block_spchans) {
  560. if (Protocol::chan_secret_mode && @$data[Denora::getSqlMode(Protocol::chan_secret_mode)] == 'Y' ) return 403;
  561. if (Protocol::chan_private_mode && @$data[Denora::getSqlMode(Protocol::chan_private_mode)] == 'Y' ) return 403;
  562. }
  563. if (@$data['mode_li'] == "Y" || @$data['mode_lk'] == "Y" || @$data['mode_uo'] == "Y") {
  564. return 403;
  565. } else {
  566. return 200;
  567. }
  568. }
  569. }
  570. /**
  571. * Checks if the given channel is being monitored by chanstats
  572. * @param string $chan Channel
  573. * @return boolean true: yes, false: no
  574. */
  575. function checkChannelStats($chan) {
  576. $sQuery = "SELECT COUNT(*) FROM cstats WHERE chan=:channel";
  577. $ps = $this->db->prepare($sQuery);
  578. $ps->bindParam(':channel', $chan, PDO::PARAM_STR);
  579. $ps->execute();
  580. return $ps->fetch(PDO::FETCH_COLUMN) ? true : false;
  581. }
  582. /**
  583. * Get the users currently in the specified channel
  584. * @todo implement server-side datatables support
  585. * @param string $chan Channel
  586. * @return array of User
  587. */
  588. function getChannelUsers($chan) {
  589. if ($this->checkChannel($chan) != 200) {
  590. return null;
  591. }
  592. $query = "SELECT u.nick AS nickname, u.realname, u.hostname, u.hiddenhostname AS hostname_cloaked, u.swhois,
  593. u.username, u.connecttime AS connect_time, u.server, u.away, u.awaymsg AS away_msg, u.ctcpversion AS client, u.online,
  594. u.lastquit AS quit_time, u.lastquitmsg AS quit_msg, u.countrycode AS country_code, u.country, s.uline AS service,
  595. i.mode_lq AS cmode_lq, i.mode_la AS cmode_la, i.mode_lo AS cmode_lo, i.mode_lh AS cmode_lh, i.mode_lv AS cmode_lv";
  596. if ($this->cfg->denora_version > '1.4') {
  597. $query .= ", s.country AS server_country, s.countrycode AS server_country_code";
  598. }
  599. $query .= " FROM ison i, chan c, user u, server s
  600. WHERE LOWER(c.channel) = LOWER(:channel)
  601. AND i.chanid = c.chanid
  602. AND i.nickid = u.nickid
  603. AND u.server = s.server
  604. ORDER BY u.nick ASC";
  605. $stmt = $this->db->prepare($query);
  606. $stmt->bindParam(':channel', $chan, SQL_STR);
  607. $stmt->execute();
  608. return $stmt->fetchAll(PDO::FETCH_CLASS, 'User');
  609. }
  610. /**
  611. * Gets the global channel activity
  612. * @param int $type 0: total, 1: day, 2: week, 3: month, 4: year
  613. * @param boolean $datatables true: datatables format, false: standard format
  614. * @return array Data
  615. * @todo refactor
  616. */
  617. function getChannelGlobalActivity($type, $datatables = false) {
  618. $aaData = array();
  619. $secret_mode = Protocol::chan_secret_mode;
  620. $private_mode = Protocol::chan_private_mode;
  621. $sWhere = "cstats.letters>0";
  622. if ($secret_mode) {
  623. $sWhere .= sprintf(" AND chan.%s='N'", Denora::getSqlMode($secret_mode));
  624. }
  625. if ($private_mode) {
  626. $sWhere .= sprintf(" AND chan.%s='N'", Denora::getSqlMode($private_mode));
  627. }
  628. $hide_channels = $this->cfg->hide_chans;
  629. if ($hide_channels) {
  630. $hide_channels = explode(",", $hide_channels);
  631. foreach ($hide_channels as $key => $channel) {
  632. $hide_channels[$key] = $this->db->escape(trim(strtolower($channel)));
  633. }
  634. $sWhere .= sprintf(" AND LOWER(cstats.chan) NOT IN(%s)", implode(',', $hide_channels));
  635. }
  636. $sQuery = sprintf("SELECT SQL_CALC_FOUND_ROWS chan AS name,letters,words,line AS 'lines',actions,smileys,kicks,modes,topics FROM cstats
  637. JOIN chan ON BINARY LOWER(cstats.chan)=LOWER(chan.channel) WHERE cstats.type=:type AND %s", $sWhere);
  638. if ($datatables) {
  639. $iTotal = $this->db->datatablesTotal($sQuery, array(':type' => (int) $type));
  640. $sFiltering = $this->db->datatablesFiltering(array('cstats.chan', 'chan.topic'));
  641. $sOrdering = $this->db->datatablesOrdering(array('chan', 'letters', 'words', 'line', 'actions', 'smileys', 'kicks', 'modes', 'topics'));
  642. $sPaging = $this->db->datatablesPaging();
  643. $sQuery .= sprintf("%s %s %s", $sFiltering ? " AND " . $sFiltering : "", $sOrdering, $sPaging);
  644. }
  645. $ps = $this->db->prepare($sQuery);
  646. $ps->bindParam(':type', $type, PDO::PARAM_INT);
  647. $ps->execute();
  648. foreach ($ps->fetchAll(PDO::FETCH_ASSOC) as $row) {
  649. if ($datatables) {
  650. $row["DT_RowId"] = $row['name'];
  651. }
  652. $aaData[] = $row;
  653. }
  654. if ($datatables) {
  655. $iFilteredTotal = $this->db->foundRows();
  656. return $this->db->datatablesOutput($iTotal, $iFilteredTotal, $aaData);
  657. }
  658. return $aaData;
  659. }
  660. /**
  661. * Gets the channel activity for the given channel
  662. * @param string $chan Channel
  663. * @param int $type 0: total, 1: day, 2: week, 3: month, 4: year
  664. * @param boolean $datatables true: datatables format, false: standard format
  665. * @return User
  666. * @todo refactor
  667. */
  668. function getChannelActivity($chan, $type, $datatables = false) {
  669. $aaData = array();
  670. $sQuery = "SELECT SQL_CALC_FOUND_ROWS uname,letters,words,line AS 'lines',actions,smileys,kicks,modes,topics FROM ustats WHERE chan=:channel AND type=:type AND letters > 0 ";
  671. if ($datatables) {
  672. $iTotal = $this->db->datatablesTotal($sQuery, array(':type' => (int) $type, ':channel' => $chan));
  673. $sFiltering = $this->db->datatablesFiltering(array('uname'));
  674. $sOrdering = $this->db->datatablesOrdering(array('uname', 'letters', 'words', 'line', 'actions', 'smileys', 'kicks', 'modes', 'topics'));
  675. $sPaging = $this->db->datatablesPaging();
  676. $sQuery .= sprintf("%s %s %s", $sFiltering ? " AND " . $sFiltering : "", $sOrdering, $sPaging);
  677. }
  678. $ps = $this->db->prepare($sQuery);
  679. $ps->bindParam(':type', $type, PDO::PARAM_INT);
  680. $ps->bindParam(':channel', $chan, PDO::PARAM_STR);
  681. $ps->execute();
  682. $data = $ps->fetchAll(PDO::FETCH_ASSOC);
  683. if ($datatables) {
  684. $iFilteredTotal = $this->db->foundRows();
  685. }
  686. foreach ($data as $row) {
  687. if ($datatables) {
  688. $row["DT_RowId"] = $row['uname'];
  689. }
  690. // Get country code and online status
  691. $user = $this->getUser('stats', $row['uname']);
  692. if (!$user) $user = new User();
  693. foreach ($row as $key => $val) {
  694. $user->$key = $val;
  695. }
  696. $aaData[] = $user;
  697. }
  698. if ($datatables) {
  699. return $this->db->datatablesOutput($iTotal, $iFilteredTotal, $aaData);
  700. }
  701. return $aaData;
  702. }
  703. /**
  704. * Get the hourly average activity for the given channel
  705. * @param string $chan Channel
  706. * @param int $type int $type 0: total, 1: day, 2: week, 3: month, 4: year
  707. * @return mixed
  708. */
  709. function getChannelHourlyActivity($chan, $type) {
  710. $sQuery = "SELECT time0,time1,time2,time3,time4,time5,time6,time7,time8,time9,time10,time11,time12,time13,time14,time15,time16,time17,time18,time19,time20,time21,time22,time23
  711. FROM cstats WHERE chan=:channel AND type=:type";
  712. $ps = $this->db->prepare($sQuery);
  713. $ps->bindParam(':type', $type, PDO::PARAM_INT);
  714. $ps->bindParam(':channel', $chan, PDO::PARAM_STR);
  715. $ps->execute();
  716. $result = $ps->fetch(PDO::FETCH_NUM);
  717. if (is_array($result)) {
  718. foreach ($result as $key => $val) {
  719. $result[$key] = (int) $val;
  720. }
  721. return $result;
  722. } else {
  723. return null;
  724. }
  725. }
  726. /**
  727. * Get the global user activity
  728. * @param int $type int $type 0: total, 1: day, 2: week, 3: month, 4: year
  729. * @param boolean $datatables true: datatables format, false: standard format
  730. * @return array
  731. * @todo refactor
  732. */
  733. function getUserGlobalActivity($type, $datatables = false) {
  734. $aaData = array();
  735. $sQuery = "SELECT SQL_CALC_FOUND_ROWS uname,letters,words,line AS 'lines',
  736. actions,smileys,kicks,modes,topics FROM ustats
  737. WHERE type=:type AND letters>0 and chan='global'";
  738. if ($datatables) {
  739. $iTotal = $this->db->datatablesTotal($sQuery, array(':type' => $type));
  740. $sFiltering = $this->db->datatablesFiltering(array('uname'));
  741. $sOrdering = $this->db->datatablesOrdering(array('uname', 'letters', 'words', 'line', 'actions', 'smileys', 'kicks', 'modes', 'topics'));
  742. $sPaging = $this->db->datatablesPaging();
  743. $sQuery .= sprintf("%s %s %s", $sFiltering ? " AND " . $sFiltering : "", $sOrdering, $sPaging);
  744. }
  745. $ps = $this->db->prepare($sQuery);
  746. $ps->bindParam(':type', $type, PDO::PARAM_INT);
  747. $ps->execute();
  748. $data = $ps->fetchAll(PDO::FETCH_ASSOC);
  749. if ($datatables) {
  750. $iFilteredTotal = $this->db->foundRows();
  751. }
  752. if (is_array($data)) {
  753. foreach ($data as $row) {
  754. if ($datatables) {
  755. $row["DT_RowId"] = $row['uname'];
  756. }
  757. $user = $this->getUser('stats', $row['uname']);
  758. if (!$user) {
  759. $user = new User();
  760. $user->nickname = $row['uname'];
  761. $user->country = 'Unknown';
  762. $user->country_code = '';
  763. $user->online = false;
  764. $user->away = false;
  765. $user->bot = false;
  766. $user->service = false;
  767. $user->operator = false;
  768. $user->helper = false;
  769. }
  770. foreach ($row as $key => $val) {
  771. $user->$key = $val;
  772. }
  773. $aaData[] = $user;
  774. }
  775. }
  776. return $datatables ? $this->db->datatablesOutput($iTotal, $iFilteredTotal, $aaData) : $aaData;
  777. }
  778. /**
  779. * Get the average hourly activity for the given user
  780. * @param string $mode stats: user is treated as stats user, nick: user is treated as nickname
  781. * @param string $user User
  782. * @param string $chan Channel
  783. * @param int $type int $type 0: total, 1: day, 2: week, 3: month, 4: year
  784. * @return mixed
  785. * @todo refactor
  786. */
  787. function getUserHourlyActivity($mode, $user, $chan, $type) {
  788. $info = $this->getUserData($mode, $user);
  789. $sQuery = "SELECT time0,time1,time2,time3,time4,time5,time6,time7,time8,time9,time10,time11,time12,time13,time14,time15,time16,time17,time18,time19,time20,time21,time22,time23
  790. FROM ustats WHERE uname=:uname AND chan=:channel AND type=:type";
  791. $ps = $this->db->prepare($sQuery);
  792. $ps->bindParam(':type', $type, PDO::PARAM_INT);
  793. $ps->bindParam(':channel', $chan, PDO::PARAM_STR);
  794. $ps->bindParam(':uname', $info['uname'], PDO::PARAM_STR);
  795. $ps->execute();
  796. $result = $ps->fetch(PDO::FETCH_NUM);
  797. if (is_array($result)) {
  798. foreach ($result as $key => $val) {
  799. $result[$key] = (int) $val;
  800. }
  801. return $result;
  802. } else {
  803. return null;
  804. }
  805. }
  806. /**
  807. * Checks if the given user exists
  808. * @param string $user User
  809. * @param string $mode ('stats': $user is a stats user, 'nick': $user is a nickname)
  810. * @return boolean true: yes, false: no
  811. */
  812. function checkUser($user, $mode) {
  813. if ($mode == "stats") {
  814. $query = "SELECT uname FROM ustats WHERE LOWER(uname) = LOWER(:user)";
  815. } else {
  816. $query = "SELECT nick FROM user WHERE LOWER(nick) = LOWER(:user)";
  817. }
  818. $stmt = $this->db->prepare($query);
  819. $stmt->bindParam(':user', $user, SQL_STR);
  820. $stmt->execute();
  821. return $stmt->fetch(PDO::FETCH_COLUMN) ? true : false;
  822. }
  823. /**
  824. * Checks if the given user is being monitored by chanstats
  825. * @param string $user User
  826. * @param string $mode ('stats': $user is a stats user, 'nick': $user is a nickname)
  827. * @return boolean true: yes, false: no
  828. */
  829. function checkUserStats($user, $mode) {
  830. if ($mode != 'stats') {
  831. $user = $this->getUnameFromNick($user);
  832. }
  833. $sQuery = "SELECT COUNT(*) FROM ustats WHERE uname=:user";
  834. $ps = $this->db->prepare($sQuery);
  835. $ps->bindParam(':user', $user, PDO::PARAM_STR);
  836. $ps->execute();
  837. return $ps->fetch(PDO::FETCH_COLUMN) ? true : false;
  838. }
  839. /**
  840. * Returns the stats username and all aliases of a user
  841. * @param string $mode ('stats': $user is a stats user, 'nick': $user is a nickname)
  842. * @param string $user Nickname or Stats username
  843. * @return array ('nick' => nickname, 'uname' => stats username, 'aliases' => array of aliases)
  844. */
  845. private function getUserData($mode, $user) {
  846. $uname = ($mode == "stats") ? $user : $this->getUnameFromNick($user);
  847. $aliases = $this->getUnameAliases($uname);
  848. if (!$aliases) {
  849. $aliases = array($uname ? $uname : $user);
  850. }
  851. $nick = ($mode == "stats") ? $aliases[0] : $user;
  852. array_shift($aliases);
  853. return array('nick' => $nick, 'uname' => $uname, 'aliases' => $aliases);
  854. }
  855. /**
  856. * Get a user based on its nickname or stats user
  857. * @param string $mode 'nick': nickname, 'stats': chanstats user
  858. * @param string $user
  859. * @return User
  860. */
  861. function getUser($mode, $user) {
  862. $info = $this->getUserData($mode, $user);
  863. $query = sprintf("SELECT u.nick AS nickname, u.realname, u.hostname, u.hiddenhostname AS hostname_cloaked, u.swhois,
  864. u.username, u.connecttime AS connect_time, u.server, u.away, u.awaymsg AS away_msg, u.ctcpversion AS client, u.online,
  865. u.lastquit AS quit_time, u.lastquitmsg AS quit_msg, u.countrycode AS country_code, u.country, s.uline AS service, %s",
  866. implode(',', array_map(array('Denora', 'getSqlMode'), str_split(Protocol::user_modes))));
  867. if ($this->cfg->denora_version > '1.4') {
  868. $query .= ", s.country AS server_country, s.countrycode AS server_country_code";
  869. }
  870. $query .= " FROM user u LEFT JOIN server s ON s.servid = u.servid WHERE u.nick = :nickname";
  871. $ps = $this->db->prepare($query);
  872. $ps->bindParam(':nickname', $info['nick'], PDO::PARAM_INT);
  873. $ps->execute();
  874. $user = $ps->fetchObject('User');
  875. if ($user) {
  876. $user->uname = $info['uname'];
  877. $user->aliases = $info['aliases'];
  878. return $user;
  879. } else {
  880. return null;
  881. }
  882. }
  883. /**
  884. * Get a list of channels monitored for a specific user
  885. * @param string $mode 'nick': nickname, 'stats': chanstats user
  886. * @param string $user
  887. * @return array of channel names
  888. */
  889. function getUserChannels($mode, $user) {
  890. $info = $this->getUserData($mode, $user);
  891. $secret_mode = Protocol::chan_secret_mode;
  892. $private_mode = Protocol::chan_private_mode;
  893. $sWhere = "";
  894. if ($secret_mode) {
  895. $sWhere .= sprintf(" AND chan.%s='N'", Denora::getSqlMode($secret_mode));
  896. }
  897. if ($private_mode) {
  898. $sWhere .= sprintf(" AND chan.%s='N'", Denora::getSqlMode($private_mode));
  899. }
  900. $hide_channels = $this->cfg->hide_chans;
  901. if ($hide_channels) {
  902. $hide_channels = explode(",", $hide_channels);
  903. foreach ($hide_channels as $key => $channel) {
  904. $hide_channels[$key] = $this->db->escape(trim(strtolower($channel)));
  905. }
  906. $sWhere .= sprintf(" AND LOWER(channel) NOT IN(%s)", implode(',', $hide_channels));
  907. }
  908. $query = sprintf("SELECT DISTINCT chan FROM ustats, chan, user WHERE ustats.uname=:uname
  909. AND ustats.type=0 AND BINARY LOWER(ustats.chan)=LOWER(chan.channel)
  910. AND user.nick=:nick %s", $sWhere);
  911. $ps = $this->db->prepare($query);
  912. $ps->bindParam(':uname', $info['uname'], PDO::PARAM_STR);
  913. $ps->bindParam(':nick', $info['nick'], PDO::PARAM_STR);
  914. $ps->execute();
  915. return $ps->fetchAll(PDO::FETCH_COLUMN);
  916. }
  917. /**
  918. * Get the user activity of the given user
  919. * @param string $mode stats: user is treated as stats user, nick: user is treated as nickname
  920. * @param string $user User
  921. * @param string $chan Channel
  922. * @return mixed
  923. * @todo refactor
  924. */
  925. function getUserActivity($mode, $user, $chan) {
  926. $info = $this->getUserData($mode, $user);
  927. if ($chan == 'global') {
  928. $sQuery = "SELECT type,letters,words,line AS 'lines',actions,smileys,kicks,modes,topics
  929. FROM ustats WHERE uname=:uname AND chan=:chan ORDER BY ustats.letters DESC";
  930. } else {
  931. $sWhere = "";
  932. $hide_channels = $this->cfg->hide_chans;
  933. if ($hide_channels) {
  934. $hide_channels = explode(",", $hide_channels);
  935. foreach ($hide_channels as $key => $channel) {
  936. $hide_channels[$key] = $this->db->escape(trim(strtolower($channel)));
  937. }
  938. $sWhere .= sprintf(" AND LOWER(channel) NOT IN(%s)", implode(',', $hide_channels));
  939. }
  940. $sQuery = sprintf("SELECT type,letters,words,line AS 'lines',actions,smileys,kicks,modes,topics
  941. FROM ustats, chan WHERE ustats.uname=:uname AND ustats.chan=:chan
  942. AND BINARY LOWER(ustats.chan)=LOWER(chan.channel) %s ORDER BY ustats.letters DESC", $sWhere);
  943. }
  944. $ps = $this->db->prepare($sQuery);
  945. $ps->bindParam(':uname', $info['uname'], PDO::PARAM_STR);
  946. $ps->bindParam(':chan', $chan, PDO::PARAM_STR);
  947. $ps->execute();
  948. $data = $ps->fetchAll(PDO::FETCH_ASSOC);
  949. if (is_array($data)) {
  950. foreach ($data as $key => $type) {
  951. foreach ($type as $field => $val) {
  952. $data[$key][$field] = (int) $val;
  953. }
  954. }
  955. return $data;
  956. } else {
  957. return null;
  958. }
  959. }
  960. /**
  961. * Get the chanstats username assigned to a nick, if available
  962. * @param string $nick nickname
  963. * @return string chanstats username
  964. */
  965. private function getUnameFromNick($nick) {
  966. $ps = $this->db->prepare("SELECT uname FROM aliases WHERE nick = :nickname");
  967. $ps->bindParam(':nickname', $nick, PDO::PARAM_STR);
  968. $ps->execute();
  969. return $ps->fetch(PDO::FETCH_COLUMN);
  970. }
  971. /**
  972. * Get all nicknames linked to a chanstats user
  973. * @param string $uname chanstats username
  974. * @return array of nicknames
  975. */
  976. private function getUnameAliases($uname) {
  977. $ps = $this->db->prepare("SELECT a.nick FROM aliases a LEFT JOIN user u ON a.nick = u.nick
  978. WHERE a.uname = :uname ORDER BY CASE WHEN u.online IS NULL THEN 1 ELSE 0 END,
  979. u.online DESC, u.lastquit DESC, u.connecttime ASC");
  980. $ps->bindParam(':uname', $uname, PDO::PARAM_STR);
  981. $ps->execute();
  982. return $ps->fetchAll(PDO::FETCH_COLUMN);
  983. }
  984. }
  985. ?>