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

3
require 'xhp/init.php';
4

5
require 'tags/calendar.php';
6
require 'tags/document.php';
7 8 9 10 11 12 13
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';
14
require 'tags/timezone.php';
15 16
require 'tags/year.php';
require 'tags/years.php';
Simon Welsh's avatar
Simon Welsh committed
17

Simon Welsh's avatar
Simon Welsh committed
18 19
final class DB {
	private $con;
20
	private $inst;
Simon Welsh's avatar
Simon Welsh committed
21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

	public static function inst() {
		static $inst = null;
		if(!$inst) {
			$inst = new self;
		}
		return $inst;
	}

	private function __construct() {
		$this->con = pg_connect('user=simon password=pass dbname=logbot');
	}

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

Simon Welsh's avatar
Simon Welsh committed
38
	public function search($value, $order = 'Relevance') {
Simon Welsh's avatar
Simon Welsh committed
39
		$value = pg_escape_literal($this->con, $value);
Simon Welsh's avatar
Simon Welsh committed
40 41 42
		if(!in_array($order, ['Relevance', 'Time'])) {
			$order = 'Relevance';
		}
43
		$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
44 45 46 47 48 49 50 51 52
		$res = $this->query($sql);
		$results = array();
		while($row = pg_fetch_assoc($res)) {
			$results[] = $row;
		}
		return $results;
	}
}

53
function build_dates() {
Simon Welsh's avatar
Simon Welsh committed
54 55
	static $dates = array();
	if(!$dates) {
56
		$r = new Redis;
Simon Welsh's avatar
Simon Welsh committed
57 58 59 60 61 62
		if($r->connect('localhost')) {
			$r->setOption(Redis::OPT_SERIALIZER, Redis::SERIALIZER_PHP);
			$dates = $r->get('log:dates');
		} else {
			$r = null;
		}
63 64 65 66 67 68 69 70 71
		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
72 73 74 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);
					$d = new DateTime('midnight tomorrow', new DateTimeZone('Pacific/Auckland'));
					$r->expireAt('log:dates', $d->format('U'));
				}
81
			}
Simon Welsh's avatar
Simon Welsh committed
82 83 84 85
		}
	}
	return $dates;
}
Simon Welsh's avatar
Simon Welsh committed
86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104

function get_stats() {
	$r = new Redis;
	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
105 106 107 108 109 110 111 112 113 114 115 116 117 118
	$swears = [
		'Bitch',
		'Cunt',
		'Fuck',
		'Shit',
		'Joomla',
		'WordPress',
		'Dick',
		'Slut',
		'Crap',
		'Synergy',
		'Drupal',
		'Bastard',
	];
Simon Welsh's avatar
Simon Welsh committed
119
	$query = 'SELECT COUNT(*) AS "Number", "Nick" FROM "Log" WHERE "Text" ILIKE \'%%%s%%\' GROUP BY "Nick" HAVING COUNT(*) >= 10 ORDER BY "Number" DESC;';
Simon Welsh's avatar
Simon Welsh committed
120
	foreach($swears as $name) {
Simon Welsh's avatar
Simon Welsh committed
121 122 123
		$res = DB::inst()->query(sprintf($query, $name));
		$stats[$name] = [];
		while($row = pg_fetch_array($res)) {
Simon Welsh's avatar
Simon Welsh committed
124
			$stats[$name][$row[1]] = $row[0];
Simon Welsh's avatar
Simon Welsh committed
125 126 127 128 129 130 131 132
		}
	}
	$stats = array_filter($stats);
	uasort($stats, function($b, $a) {
		return count($a) - count($b);
	});
	$r->set('log:stats', $stats, 3600);
}