funcs.php 3.4 KB
Newer Older
Simon Welsh's avatar
Simon Welsh committed
1
<?hh
Simon Welsh's avatar
Simon Welsh committed
2

3 4
$withAd = true;

5
require 'xhp/init.php';
6

7
require 'tags/calendar.php';
8
require 'tags/document.php';
9 10 11 12 13 14 15
require 'tags/foot.php';
require 'tags/form.php';
require 'tags/head.php';
require 'tags/info.php';
require 'tags/log.php';
require 'tags/logs.php';
require 'tags/month.php';
Simon Welsh's avatar
Simon Welsh committed
16
require 'tags/sponsor.php';
17
require 'tags/timezone.php';
18 19
require 'tags/year.php';
require 'tags/years.php';
Simon Welsh's avatar
Simon Welsh committed
20

Simon Welsh's avatar
Simon Welsh committed
21 22
final class DB {
	private $con;
23
	private $inst;
Simon Welsh's avatar
Simon Welsh committed
24 25 26 27

	public static function inst() {
		static $inst = null;
		if(!$inst) {
Simon Welsh's avatar
Simon Welsh committed
28
			$inst = new self();
Simon Welsh's avatar
Simon Welsh committed
29 30 31 32 33
		}
		return $inst;
	}

	private function __construct() {
Simon Welsh's avatar
Simon Welsh committed
34
		$this->con = pg_connect('user=simon password=pass dbname=logbot host=127.0.0.1');
Simon Welsh's avatar
Simon Welsh committed
35 36 37 38 39 40
	}

	public function query($sql) {
		return pg_query($this->con, $sql);
	}

Simon Welsh's avatar
Simon Welsh committed
41
	public function search($value, $order = 'Relevance') {
Simon Welsh's avatar
Simon Welsh committed
42
		$value = pg_escape_literal($this->con, $value);
Simon Welsh's avatar
Simon Welsh committed
43 44 45
		if(!in_array($order, ['Relevance', 'Time'])) {
			$order = 'Relevance';
		}
46
		$sql = 'SELECT "Time", "Text", "Command", "Target", "Nick", "ID", ts_rank_cd("Search", query, 16 | 32) AS "Relevance" FROM "Log", plainto_tsquery(\'english\', ' . $value  . ') query WHERE "Search" @@ query ORDER BY "' . $order . '" DESC LIMIT 5000';
Simon Welsh's avatar
Simon Welsh committed
47 48 49 50 51 52 53 54 55
		$res = $this->query($sql);
		$results = array();
		while($row = pg_fetch_assoc($res)) {
			$results[] = $row;
		}
		return $results;
	}
}

56
function build_dates() {
Simon Welsh's avatar
Simon Welsh committed
57 58
	static $dates = array();
	if(!$dates) {
Simon Welsh's avatar
Simon Welsh committed
59
		$r = new Redis();
Simon Welsh's avatar
Simon Welsh committed
60 61 62 63 64 65
		if($r->connect('localhost')) {
			$r->setOption(Redis::OPT_SERIALIZER, Redis::SERIALIZER_PHP);
			$dates = $r->get('log:dates');
		} else {
			$r = null;
		}
66 67 68 69 70 71 72 73 74
		if(!$dates) {
			$res = DB::inst()->query('SELECT DISTINCT DATE("Time") FROM "Log" WHERE DATE("Time") > \'2000-01-01\' ORDER BY DATE("Time") DESC');
			while($row = pg_fetch_array($res)) {
				$time = strtotime($row[0]);
				$year = date('Y', $time);
				$month = date('F', $time);
				$day = date('j', $time);
				$dates[$year][$month][$day] = array($day, $row[0]);
			}
Simon Welsh's avatar
Simon Welsh committed
75 76 77 78 79 80
			if($r) {
				$ty = date('Y');
				$tm = date('F');
				$td = date('j');
				if(isset($dates[$ty][$tm][$td])) {
					$r->set('log:dates', $dates);
Simon Welsh's avatar
Simon Welsh committed
81
					$d = new DateTime('midnight tomorrow', new DateTimeZone('UTC'));
Simon Welsh's avatar
Simon Welsh committed
82 83
					$r->expireAt('log:dates', $d->format('U'));
				}
84
			}
Simon Welsh's avatar
Simon Welsh committed
85 86 87 88
		}
	}
	return $dates;
}
Simon Welsh's avatar
Simon Welsh committed
89 90

function get_stats() {
Simon Welsh's avatar
Simon Welsh committed
91
	$r = new Redis();
Simon Welsh's avatar
Simon Welsh committed
92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107
	if($r->connect('localhost')) {
		$r->setOption(Redis::OPT_SERIALIZER, Redis::SERIALIZER_PHP);
		$stats = $r->get('log:stats');
	} else {
		return [];
	}
	if(!$stats) {
		build_stats($r);
		$stats = $r->get('log:stats');
	}
	return $stats;
}

function build_stats(Redis $r) {
	// SELECT COUNT(*) AS "Bitches", "Nick" FROM "Log" WHERE "Text" ILIKE '%cunt%' GROUP BY "Nick" ORDER BY "Bitches" DESC;
	$stats = [];
Simon Welsh's avatar
Simon Welsh committed
108
	$swears = array_filter(array_map(fun('trim'), file(__DIR__ . '/swears.txt')));
Simon Welsh's avatar
Simon Welsh committed
109
	natcasesort($swears);
Simon Welsh's avatar
Simon Welsh committed
110
	foreach($swears as $name) {
Simon Welsh's avatar
Simon Welsh committed
111
		$res = DB::inst()->query(sprintf('SELECT COUNT(*) AS "Number", "Nick" FROM "Log" WHERE "Text" ILIKE \'%%%s%%\' GROUP BY "Nick" ORDER BY "Number" DESC LIMIT 20;', $name));
Simon Welsh's avatar
Simon Welsh committed
112 113
		$stats[$name] = [];
		while($row = pg_fetch_array($res)) {
Simon Welsh's avatar
Simon Welsh committed
114
			$stats[$name][$row[1]] = $row[0];
Simon Welsh's avatar
Simon Welsh committed
115 116 117
		}
	}
	$stats = array_filter($stats);
Simon Welsh's avatar
Simon Welsh committed
118
	$full = [];
119
	$query = 'SELECT COUNT(*) AS "Number", "Nick" FROM "Log" WHERE "Command" = \'ACTION\' OR "Command" = \'PRIVMSG\' GROUP BY "Nick" ORDER BY "Number" DESC LIMIT 20;';
Simon Welsh's avatar
Simon Welsh committed
120 121 122 123 124
	$res = DB::inst()->query($query);
	while($row = pg_fetch_array($res)) {
		$full[$row[1]] = $row[0];
	}
	$r->set('log:stats', [$full, $stats], 3600);
Simon Welsh's avatar
Simon Welsh committed
125
}