diff options
-rw-r--r-- | lib/VNWeb/Misc/Reports.pm | 2 | ||||
-rw-r--r-- | sql/func.sql | 12 |
2 files changed, 13 insertions, 1 deletions
diff --git a/lib/VNWeb/Misc/Reports.pm b/lib/VNWeb/Misc/Reports.pm index 9e2589e0..a031c76e 100644 --- a/lib/VNWeb/Misc/Reports.pm +++ b/lib/VNWeb/Misc/Reports.pm @@ -148,7 +148,7 @@ TUWF::get qr{/report/list}, sub { my $cnt = tuwf->dbVali('SELECT count(*) FROM reports r WHERE', $where); my $lst = tuwf->dbPagei({results => 25, page => $opt->{p}}, - 'SELECT r.id,', sql_totime('r.date'), 'as date, r.uid, ur.username, r.ip, r.reason, r.status, r.message, r.log + 'SELECT r.id,', sql_totime('r.date'), 'as date, r.uid, ur.username, fmtip(r.ip) as ip, r.reason, r.status, r.message, r.log , r.object, r.objectnum, x.title, x.uid as by_uid,', sql_user('uo'), ' FROM reports r LEFT JOIN item_info(r.object, r.objectnum) x ON true diff --git a/sql/func.sql b/sql/func.sql index 842d477b..af51303a 100644 --- a/sql/func.sql +++ b/sql/func.sql @@ -10,6 +10,18 @@ -- with that, either. +-- Handy function to format an ipinfo type for human consumption. +CREATE OR REPLACE FUNCTION fmtip(n ipinfo) RETURNS text AS $$ + SELECT COALESCE(COALESCE((n).country, 'X')||':'||(n).asn||COALESCE(':'||(n).as_name,'')||'/', (n).country||'/', '') + || abbrev((n).ip) + || CASE WHEN (n).anonymous_proxy THEN ' ANON' ELSE '' END + || CASE WHEN (n).sattelite_provider THEN ' SAT' ELSE '' END + || CASE WHEN (n).anycast THEN ' ANY' ELSE '' END + || CASE WHEN (n).drop THEN ' DROP' ELSE '' END +$$ LANGUAGE SQL IMMUTABLE; + + + CREATE OR REPLACE FUNCTION search_gen_vn(vnid vndbid) RETURNS text AS $$ SELECT coalesce(string_agg(t, ' '), '') FROM ( SELECT t FROM ( |