Skip to content

Instantly share code, notes, and snippets.

View nerestaren's full-sized avatar

Antoni Oliver nerestaren

2 followers · 1 following
View GitHub Profile
@nerestaren
nerestaren / select usuaris.sql
Created December 6, 2016 23:58
SELECT username,
user_posts AS sempre,
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time < UNIX_TIMESTAMP() - 7776000) AS 'des de fa més de tres mesos',
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time BETWEEN UNIX_TIMESTAMP() - 7776000 AND UNIX_TIMESTAMP() - 5184000) AS 'des de fa tres mesos fins fa dos',
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time BETWEEN UNIX_TIMESTAMP() - 5184000 AND UNIX_TIMESTAMP() - 2592000) AS 'des de fa dos mesos fins fa un',
(SELECT COUNT(post_id) FROM posts WHERE poster_id = user_id AND post_time >= UNIX_TIMESTAMP() - 2592000) AS 'des de fa un mes fins avui',
(SELECT pf_padri FROM profile_fields_data WHERE profile_fields_data.user_id = users.user_id) AS 'padri'
FROM users WHERE user_posts > 0
@nerestaren
nerestaren / filtra.js
Created December 8, 2016 22:02
function filtra(tipus, criteri) {
var valor = filtre[tipus][criteri].val;
if (criteri == 'Tot') {
Object.keys(filtre[tipus]).forEach(function(c) {
if (filtre[tipus][c].val == valor) {
filtre[tipus][c].val = !valor;
$(filtre[tipus][c].btn).toggleClass('off', !filtre[tipus][c].val);
}
});
} else {
@nerestaren
nerestaren / rols.sql
Last active December 20, 2016 17:16
Els rols que s'han escrit a Tadaima
SELECT DATE_FORMAT(FROM_UNIXTIME(post_time), '%d/%m/%Y %T'), username, forum_name, topic_title, (
SELECT COUNT(topic_id)
FROM posts as b
WHERE topic_id = posts.topic_id AND post_time <= posts.post_time
), CAST(CONCAT('https://tadaima.cat/topic', posts.topic_id, '.html#p', posts.post_id) AS CHAR) AS url
FROM posts
LEFT JOIN users on poster_id = user_id
LEFT JOIN topics on posts.topic_id = topics.topic_id
LEFT JOIN forums on topics.forum_id = forums.forum_id
WHERE posts.forum_id IN (39, 40, 41, 42)
@nerestaren
nerestaren / test.json
Created March 27, 2017 10:53
prova
{"name": "test"}
@nerestaren
nerestaren / patata.json
Created March 27, 2017 11:23
{"asd": "asd"}
@nerestaren
nerestaren / .htaccess
Last active May 14, 2017 23:27 — forked from Ereza/.htaccess
htaccess for seourls
RewriteRule ^(.+)-t([0-9])-([0-9]).html viewtopic.php?t=$2&start=$3&%{QUERY_STRING} [L]
RewriteRule ^(.+)-t([0-9]).html viewtopic.php?t=$2&%{QUERY_STRING} [L]
RewriteRule ^(.+)-f([0-9])-([0-9]).html viewforum.php?f=$2&start=$3&%{QUERY_STRING} [L]
RewriteRule ^(.+)-f([0-9]).html viewforum.php?f=$2&%{QUERY_STRING} [L]
@nerestaren
nerestaren / ping.sh
Created May 20, 2017 13:38
#/bin/bash
while true
do
ping -c 1 www.uib.es > /dev/null 2>&1
echo -e `date '+%Y-%m-%d %H:%M:%S'` '\t' $?
# sleep 10
done
@nerestaren
nerestaren / Encoding.java
Last active September 28, 2017 22:06
Java System.in System.out encoding playground
/*
* To change this license header, choose License Headers in Project Properties.
* To change this template file, choose Tools | Templates
* and open the template in the editor.
*/
package encoding;
import java.io.*;
/**
@nerestaren
nerestaren / imgur_direct_link.user.js
Last active November 13, 2018 09:51
Imgur direct link userscript
@nerestaren
nerestaren / query,sql
Created January 3, 2018 18:11
Select public forums
SELECT f.forum_id
FROM forums f
LEFT JOIN acl_groups ag ON ag.forum_id = f.forum_id
LEFT JOIN acl_options ao ON ag.auth_option_id = ao.auth_option_id
LEFT JOIN acl_roles_data ard ON ag.auth_role_id = ard.role_id
LEFT JOIN acl_options ao2 ON ard.auth_option_id = ao2.auth_option_id
WHERE ag.group_id = 2 AND ((ag.auth_option_id = 20 AND ag.auth_setting = 1) OR (ard.auth_option_id = 20 AND ard.auth_setting = 1))