/**
* Create/update schema in database.
*/
public static function update_schema()
{
global $wpdb;
$sql = "\n\n CREATE TABLE " . Track::table_name() . " (\n id int(11) NOT NULL AUTO_INCREMENT,\n stream_title varchar(200) NOT NULL,\n track_key varchar(200) NOT NULL,\n artist varchar(100) NOT NULL,\n title varchar(100) NOT NULL,\n play_count int(11) NOT NULL,\n vote_count int(11) NOT NULL,\n vote_total int(11) DEFAULT NULL,\n vote_average double DEFAULT NULL,\n PRIMARY KEY (id),\n UNIQUE KEY (track_key),\n KEY (vote_average),\n KEY (play_count),\n KEY (vote_count),\n KEY (vote_total)\n ) CHARACTER SET utf8;\n\n CREATE TABLE " . Play::table_name() . " (\n id int(11) NOT NULL AUTO_INCREMENT,\n time_utc datetime NOT NULL,\n track_id int(11) NOT NULL,\n stream_title varchar(200) NOT NULL,\n PRIMARY KEY (id),\n KEY (track_id),\n KEY (time_utc),\n CONSTRAINT " . Play::table_name() . "_ibfk_1 FOREIGN KEY (track_id) REFERENCES " . Track::table_name() . " (id)\n ) CHARACTER SET utf8;\n\n CREATE TABLE " . Vote::table_name() . " (\n id int(11) NOT NULL AUTO_INCREMENT,\n time_utc datetime NOT NULL,\n track_id int(11) NOT NULL,\n stream_title varchar(200) NOT NULL,\n value tinyint(4) NOT NULL,\n nick varchar(30) NOT NULL,\n user_id varchar(150) NOT NULL,\n is_authed bit(1) NOT NULL,\n deleted tinyint(4) NOT NULL DEFAULT '0',\n comment varchar(200) NULL,\n PRIMARY KEY (id),\n KEY (track_id),\n KEY (time_utc),\n KEY (nick),\n CONSTRAINT " . Vote::table_name() . "_ibfk_1 FOREIGN KEY (track_id) REFERENCES " . Track::table_name() . " (id)\n ) CHARACTER SET utf8;\n\n ";
require_once ABSPATH . 'wp-admin/includes/upgrade.php';
dbDelta($sql);
}
public static function playlist($start_time, $end_time)
{
global $wpdb;
if (!$start_time && !$end_time) {
return NULL;
}
$cond_parm = array();
if ($start_time) {
$cond[] = 'p.time_utc >= %s';
$cond_parm[] = $start_time;
}
if ($end_time) {
$cond[] = 'p.time_utc < %s';
$cond_parm[] = $end_time;
}
/* echo "<pre>" . $wpdb->prepare (
"
SELECT p.time_utc, t.artist, t.title, (
SELECT SUM(value) FROM ".Vote::table_name()." pv
WHERE pv.deleted=0
AND pv.time_utc >= p.time_utc AND pv.time_utc < p.time_utc + INTERVAL 30 MINUTE
) vote_total
FROM ".Play::table_name()." p
LEFT JOIN ".Track::table_name()." t on t.id=p.track_id
WHERE " . implode( ' AND ', $cond ) . "
ORDER BY p.time_utc ASC
"
, $cond_parm
); exit; */
return $wpdb->get_results($wpdb->prepare("\n SELECT p.time_utc, t.artist, t.title, (\n SELECT SUM(value) FROM " . Vote::table_name() . " pv\n WHERE pv.deleted=0 AND pv.track_id=p.track_id\n AND pv.time_utc >= p.time_utc AND pv.time_utc < p.time_utc + INTERVAL 30 MINUTE\n ) vote_total\n FROM " . Play::table_name() . " p\n LEFT JOIN " . Track::table_name() . " t on t.id=p.track_id\n WHERE " . implode(' AND ', $cond) . "\n ORDER BY p.time_utc ASC\n ", $cond_parm), ARRAY_A);
}
/**
* Get top 100 tracks by vote.
* @return object[]
*/
public static function top_hundred_by_vote($start_time = NULL, $end_time = NULL)
{
global $wpdb;
if (!$start_time && !$end_time) {
return $wpdb->get_results("\n SELECT stream_title, artist, title, vote_total\n FROM " . Track::table_name() . "\n WHERE vote_total IS NOT NULL\n ORDER BY vote_total DESC LIMIT 100\n ", ARRAY_A);
}
$cond = array('v.deleted = 0');
$cond_parm = array();
if ($start_time) {
$cond[] = 'v.time_utc >= %s';
$cond_parm[] = $start_time;
}
if ($end_time) {
$cond[] = 'v.time_utc < %s';
$cond_parm[] = $end_time;
}
return $wpdb->get_results($wpdb->prepare("\n SELECT t.stream_title, t.artist, t.title, SUM(v.value) vote_total\n FROM " . Track::table_name() . " t\n LEFT JOIN " . Vote::table_name() . " v on v.track_id=t.id\n WHERE " . implode(' AND ', $cond) . "\n GROUP BY t.id\n ORDER BY vote_total DESC, t.artist, t.title\n ", $cond_parm), ARRAY_A);
}
/**
* Get last 1000 votes by nick
* @param string $nick
* @return int
*/
public static function get_votes_by_nick($nick, $start_date = NULL, $end_date = NULL)
{
global $wpdb;
if ($start_date) {
$start_date = $wpdb->prepare("AND v.time_utc >= %s", $start_date);
}
if ($end_date) {
$end_date = $wpdb->prepare("AND time_utc < DATE_ADD(STR_TO_DATE(%s, %s), INTERVAL 1 DAY)", array($end_date, '%m/%d/%Y'));
}
$sql = $wpdb->prepare("\n SELECT v.time_utc, v.stream_title, t.title, t.artist, v.track_id, v.value\n FROM " . Vote::table_name() . " v\n LEFT JOIN " . Track::table_name() . " t ON t.id = v.track_id\n WHERE nick=%s\n AND deleted=0\n {$start_date}\n {$end_date}\n ORDER BY time_utc DESC\n LIMIT 1000\n ", substr($nick, 0, DB_NICK_LEN));
return $wpdb->get_results($sql, ARRAY_A);
}
