public function gc($max)
{
$q = "DELETE FROM #prefix#sessions WHERE updated < DATE_SUB(NOW(), INTERVAL :max SECOND)";
$vars = array(':max' => $max);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return true;
}
public function insertCompanyDetails($branch_data)
{
$ret = $this->makeInsertQueryArray($branch_data);
$q = "INSERT INTO #prefix#user SET " . $ret['q'];
$q .= ", added_date=NOW();";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $ret['vars']);
return $this->getUpdateCount($ps);
}
public function updateMapNotification($id)
{
$q = "UPDATE #prefix#map_notify SET read_at=NOW(), seen=1 ";
$q .= "WHERE id=:id;";
$vars = array(':id' => $id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$result = $this->getUpdateCount($ps);
return $result > 0;
}
public function getRecentClickStats(Instance $instance, $limit = 10)
{
$q = "SELECT p.post_text, l.expanded_url, ls.short_url, ls.click_count ";
$q .= "FROM #prefix#links_short ls INNER JOIN #prefix#links l ";
$q .= "ON l.id = ls.link_id INNER JOIN #prefix#posts p ON p.id = l.post_key ";
$q .= "WHERE p.author_username=:author_username AND p.network=:network ";
$q .= "AND ls.click_count > 0 AND p.in_retweet_of_post_id IS NULL ";
$q .= "GROUP BY short_url ORDER BY p.pub_date DESC LIMIT :limit";
$vars = array(':author_username' => $instance->network_username, ':network' => $instance->network, ':limit' => (int) $limit);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getDataRowsAsArrays($ps);
}
public function getLinkByUrl($url)
{
$q = "SELECT l.* ";
$q .= "FROM #prefix#links AS l ";
$q .= "WHERE l.url=:url ";
$vars = array(':url' => $url);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getDataRowAsObject($ps, "Link");
}
public function setTimezone($email, $timezone)
{
$q = "UPDATE #prefix#owners\n SET timezone=:timezone\n WHERE email=:email";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$stmt = $this->execute($q, array(':timezone' => $timezone, ':email' => $email));
return $this->getUpdateCount($stmt);
}
public function updateUsername($id, $network_username)
{
$q = "UPDATE " . $this->getTableName() . " SET network_username = :network_username WHERE id = :id LIMIT 1";
$vars = array(':id' => $id, ':network_username' => $network_username);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getUpdateCount($ps);
}
public function getOAuthTokens($id)
{
$q = "SELECT\n oauth_access_token, oauth_access_token_secret, auth_error\n FROM\n #prefix#owner_instances\n WHERE\n instance_id = :instance_id ORDER BY id ASC LIMIT 1";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$stmt = $this->execute($q, array(':instance_id' => $id));
$tokens = $this->getDataRowAsArray($stmt);
return $tokens;
}
/**
* Get all the favorited posts of a user.
* @TODO Use $order_by parameter to customize sort order.
* @param int $author_user_id
* @param str $network
* @param int $count
* @param str $order_by
* @param int $page
* @param bool $iterator Whether or not to return an iterator
* @returns array Post objects
*/
private function getAllFavoritedPostsForUserID($author_user_id, $network, $count, $order_by = "pub_date", $page = 1, $iterator = false)
{
// $direction = $direction=="DESC" ? "DESC": "ASC";
$start_on_record = ($page - 1) * $count;
// order-by information currently hardwired; this will probably change
// if ( !in_array($order_by, $this->REQUIRED_FIELDS) && !in_array($orderaa_by, $this->OPTIONAL_FIELDS )) {
// $order_by="pub_date";
// }
$q = "SELECT p.*, pub_date - interval #gmt_offset# hour AS adj_pub_date, ";
//TODO: Store favlike_count_cache during Twitter crawl so we don't do this dynamic GROUP BY fakeout
$q .= "count(*) AS favlike_count_cache ";
$q .= "FROM (#prefix#posts p INNER JOIN #prefix#favorites f on f.post_id = p.post_id) ";
$q .= "WHERE p.author_user_id = :author_user_id AND p.network = :network ";
$q .= "GROUP BY p.post_text ORDER BY YEARWEEK(p.pub_date) DESC, favlike_count_cache DESC, p.pub_date DESC ";
$q .= "LIMIT :start_on_record, :limit";
$vars = array(':author_user_id' => (string) $author_user_id, ':network' => $network, ':limit' => $count, ':start_on_record' => (int) $start_on_record);
$ps = $this->execute($q, $vars);
if ($iterator) {
return new PostIterator($ps);
}
$all_post_rows = $this->getDataRowsAsArrays($ps);
$posts = array();
if ($all_post_rows) {
$post_keys_array = array();
foreach ($all_post_rows as $row) {
$post_keys_array[] = $row['id'];
}
// Get links
$q = "SELECT * FROM #prefix#links WHERE post_key in (" . implode(',', $post_keys_array) . ")";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q);
$all_link_rows = $this->getDataRowsAsArrays($ps);
// Combine posts and links
$posts = array();
foreach ($all_post_rows as $post_row) {
$post = new Post($post_row);
foreach ($all_link_rows as $link_row) {
if ($link_row['post_key'] == $post->id) {
$post->addLink(new Link($link_row));
}
}
$posts[] = $post;
}
}
return $posts;
}
public function searchFollowers(array $keywords, $network, $user_id, $page_number = 1, $page_count = 20)
{
//parse advanced operators
$name_keywords = array();
$description_keywords = array();
foreach ($keywords as $keyword) {
if (substr($keyword, 0, strlen('name:')) == 'name:') {
$name_keywords[] = substr($keyword, strlen('name:'), strlen($keyword));
} else {
$description_keywords[] = $keyword;
}
}
$vars = array(':user_id' => (string) $user_id, ':network' => $network);
$q = "SELECT u.*, " . $this->getAverageTweetCount() . " ";
$q .= "FROM #prefix#users u ";
$q .= "INNER JOIN #prefix#follows f ON f.follower_id = u.user_id AND f.network = u.network ";
$q .= "WHERE f.user_id=:user_id AND u.network=:network AND (";
if (count($name_keywords) > 0 && count($description_keywords) > 0) {
$q .= "(";
$counter = 0;
foreach ($description_keywords as $keyword) {
$q .= " u.description LIKE :keyword_d" . $counter . " ";
if ($keyword != end($description_keywords)) {
$q .= "AND";
}
$counter++;
}
$q .= ") AND ( ";
$counter = 0;
foreach ($name_keywords as $keyword) {
$q .= " u.full_name LIKE :keyword_n" . $counter . " ";
if ($keyword != end($name_keywords)) {
$q .= "AND";
}
$counter++;
}
$q .= ")) ";
$counter = 0;
foreach ($description_keywords as $keyword) {
$vars[':keyword_d' . $counter] = '%' . $keyword . '%';
$counter++;
}
$counter = 0;
foreach ($name_keywords as $keyword) {
$vars[':keyword_n' . $counter] = '%' . $keyword . '%';
$counter++;
}
} elseif (count($name_keywords) > 0) {
$counter = 0;
foreach ($name_keywords as $keyword) {
$q .= " u.full_name LIKE :keyword_n" . $counter . " ";
if ($keyword != end($name_keywords)) {
$q .= "AND";
}
$counter++;
}
$q .= ") ";
$counter = 0;
foreach ($name_keywords as $keyword) {
$vars[':keyword_n' . $counter] = '%' . $keyword . '%';
$counter++;
}
} elseif (count($description_keywords) > 0) {
$counter = 0;
foreach ($description_keywords as $keyword) {
$q .= " u.description LIKE :keyword_d" . $counter . " ";
if ($keyword != end($description_keywords)) {
$q .= "AND";
}
$counter++;
}
$q .= ") ";
$counter = 0;
foreach ($description_keywords as $keyword) {
$vars[':keyword_d' . $counter] = '%' . $keyword . '%';
$counter++;
}
}
$q .= "ORDER BY first_seen DESC ";
if ($page_count > 0) {
$q .= "LIMIT :start_on_record, :limit;";
} else {
$q .= ';';
}
if ($page_count > 0) {
$vars[':limit'] = (int) $page_count;
$vars[':start_on_record'] = (int) $start_on_record;
}
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getDataRowsAsObjects($ps, 'User');
}
public function getHistory($network_user_id, $network, $units, $limit = 10, $before_date = null)
{
if ($before_date == date('Y-m-d')) {
$before_date = null;
}
if ($units != "DAY" && $units != 'WEEK' && $units != 'MONTH') {
$units = 'DAY';
}
if ($units == 'DAY') {
$group_by = 'fc.date';
$date_format = "DATE_FORMAT(date, '%m/%d/%Y')";
} else {
if ($units == 'WEEK') {
$group_by = 'YEAR(fc.date), WEEK(fc.date)';
$date_format = "DATE_FORMAT(date, '%m/%e')";
} else {
if ($units == 'MONTH') {
$group_by = 'YEAR(fc.date), MONTH(fc.date)';
$date_format = "DATE_FORMAT(date,'%m/01/%Y')";
}
}
}
$vars = array(':network_user_id' => (string) $network_user_id, ':network' => $network, ':limit' => (int) $limit);
$q = "SELECT network_user_id, network, count, date, full_date FROM ";
$q .= "(SELECT network_user_id, network, count, " . $date_format . " as date, date as full_date ";
$q .= "FROM #prefix#follower_count AS fc ";
$q .= "WHERE fc.network_user_id = :network_user_id AND fc.network=:network ";
if ($before_date != null) {
$q .= "AND date <= :before_date ";
$vars[':before_date'] = $before_date;
}
$q .= "GROUP BY " . $group_by . " ORDER BY full_date DESC LIMIT :limit ) as history_counts ";
$q .= "ORDER BY history_counts.full_date ASC";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$history_rows = $this->getDataRowsAsArrays($ps);
$resultset = array();
switch ($network) {
case 'facebook':
$follower_description = 'Friends';
break;
case 'facebook page':
$follower_description = 'Fans';
break;
case 'twitter':
default:
$follower_description = 'Followers';
break;
}
foreach ($history_rows as $row) {
$timestamp = strtotime($row['full_date']);
$resultset[] = array('c' => array(array('v' => sprintf('new Date(%d,%d,%d)', date('Y', $timestamp), date('n', $timestamp) - 1, date('j', $timestamp)), 'f' => $row['date']), array('v' => intval($row['count']))));
}
$metadata = array(array('type' => 'date', 'label' => 'Date'), array('type' => 'number', 'label' => $follower_description));
$vis_data = json_encode(array('rows' => $resultset, 'cols' => $metadata));
// Google Chart docs say that a string of the form "Date(Y,m,d)" should
// work, but chrome throws an error if we don't use an actual Date
// object.
$vis_data = preg_replace('/"(new Date[^"]+)"/', '$1', $vis_data);
if (sizeof($history_rows) > 1) {
//break down rows into a simpler date=>count assoc array
$simplified_history = array();
foreach ($history_rows as $history_row) {
$simplified_history[$history_row["date"]] = $history_row["count"];
}
$trend = false;
if (sizeof($history_rows) == $limit) {
//we have a complete data set
//calculate the trend
$first_follower_count = reset($simplified_history);
$last_follower_count = end($simplified_history);
$trend = ($last_follower_count - $first_follower_count) / sizeof($simplified_history);
$trend = intval(round($trend));
//complete data set
$history = $simplified_history;
} else {
//there are dates with missing data
//set up an array of all the dates to show in the chart
$dates_to_display = array();
$format = 'n/j';
$date = date($format);
$i = $limit;
while ($i > 0) {
if ($units == "DAY") {
$format = 'm/d/Y';
$date_ago = date($format, strtotime('-' . $i . ' ' . $units . $date));
} else {
if ($units == "WEEK") {
if ($i == $limit) {
$last_saturday = Utils::getLastSaturday();
}
$date_ago = date($format, strtotime('-' . $i . ' ' . $units . $last_saturday));
} else {
$first_day_of_this_month = date('n/1');
$format = 'm/d/Y';
$date_ago = date($format, strtotime('-' . $i . ' ' . $units . $first_day_of_this_month));
}
}
$dates_to_display[$date_ago] = "no data";
$i--;
}
//merge the data we do have with the dates we want
$history = array_merge($dates_to_display, $simplified_history);
//cut down oversized array
if (sizeof($history) > $limit) {
$history = array_slice($history, sizeof($history) - $limit);
}
if ($units == "DAY") {
ksort($history);
}
}
$history = $simplified_history;
$milestone = Utils::predictNextMilestoneDate(intval($history_rows[sizeof($history_rows) - 1]['count']), $trend);
//If $before_date set, add difference between then and now to how long it will take
if (isset($before_date)) {
if ($units == 'DAY') {
$current_day_of_year = date('z');
$before_date_day_of_year = date('z', strtotime($before_date));
if (date('Y') == date('Y', strtotime($before_date))) {
$difference = $current_day_of_year - $before_date_day_of_year;
if ($milestone['will_take'] > $difference) {
$milestone['will_take'] = $milestone['will_take'] + $difference;
}
}
} elseif ($units == 'WEEK') {
$current_week_of_year = date('W');
$before_date_week_of_year = date('W', strtotime($before_date));
if (date('Y') == date('Y', strtotime($before_date))) {
$difference = $current_week_of_year - $before_date_week_of_year;
if ($milestone['will_take'] > $difference) {
$milestone['will_take'] = $milestone['will_take'] + $difference;
}
}
} elseif ($units == 'MONTH') {
$current_month_of_year = date('n');
$before_date_month_of_year = date('n', strtotime($before_date));
if (date('Y') == date('Y', strtotime($before_date))) {
$difference = $current_month_of_year - $before_date_month_of_year;
if ($milestone['will_take'] > $difference) {
$milestone['will_take'] = $milestone['will_take'] + $difference;
}
}
}
}
if (isset($milestone)) {
$milestone['units_of_time'] = $units;
}
//only set milestone if it's within 10 to avoid "954 weeks until you reach 1000 followers" messaging
if ($milestone['will_take'] > 50) {
$milestone = null;
}
} else {
$history = false;
$trend = false;
$milestone = false;
}
return array('history' => $history, 'trend' => $trend, 'milestone' => $milestone, 'vis_data' => $vis_data);
}
public function resetID()
{
/**
* Modified this ALTER to TRUNCATE due to not being able to reset auto_increment to 1. As per:
* http://dev.mysql.com/doc/refman/5.6/en/alter-table.html
* "You cannot reset the counter to a value less than or equal to to the value that is currently in use. For both
* InnoDB and MyISAM, if the value is less than or equal to the maximum value currently in the AUTO_INCREMENT
* column, the value is reset to the current maximum AUTO_INCREMENT column value plus one."
*/
//$q = "ALTER TABLE #prefix#stream_data auto_increment = 1";
$q = "TRUNCATE TABLE #prefix#stream_data";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q);
}
public function deleteUsersByHashtagId($hashtag_id)
{
$q = "DELETE u.* ";
$q .= "FROM #prefix#users u INNER JOIN #prefix#posts t ON u.user_id=t.author_user_id ";
$q .= "INNER JOIN #prefix#hashtags_posts hp ON t.post_id = hp.post_id ";
$q .= "WHERE hp.hashtag_id= :hashtag_id;";
$vars = array(':hashtag_id' => $hashtag_id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getDeleteCount($ps);
}
public function updateTitle($id, $title)
{
$q = "UPDATE #prefix#links SET title=:title WHERE id=:id;";
$vars = array(':title' => $title, ':id' => $id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getUpdateCount($ps);
}
public function getAllOwnerInstanceInsightsSince($owner_id, $since)
{
$q = "SELECT i.*, i.id as insight_key, su.*, u.avatar FROM #prefix#insights i ";
$q .= "INNER JOIN #prefix#instances su ON i.instance_id = su.id ";
$q .= "INNER JOIN #prefix#owner_instances oi ON su.id = oi.instance_id ";
$q .= "LEFT JOIN #prefix#users u ON (su.network_user_id = u.user_id AND su.network = u.network) ";
$q .= "WHERE su.is_active = 1 AND oi.owner_id = :owner_id AND time_generated > :since ";
$q .= "AND i.filename != 'dashboard' ORDER BY date DESC, emphasis DESC, i.id;";
$vars = array(":owner_id" => (int) $owner_id, ':since' => $since);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$rows = $this->getDataRowsAsArrays($ps);
$insights = array();
foreach ($rows as $row) {
$insight = new Insight($row);
$insight->instance = new Instance($row);
$insight->instance->avatar = $row['avatar'];
$insights[] = $insight;
}
return $insights;
}
public function updateGroupMembershipCount($network_user_id, $network)
{
$q = "INSERT INTO #prefix#count_history ";
$q .= "(network_user_id, network, type, date, count) ";
$q .= "SELECT :network_user_id, :network, 'group_memberships', NOW(), COUNT(group_id) ";
$q .= "FROM #prefix#group_members WHERE member_user_id = :network_user_id ";
$q .= "AND network = :network AND is_active = 1";
$vars = array(':network_user_id' => (string) $network_user_id, ':network' => $network);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getInsertCount($ps);
}
public function addPhoto($vals)
{
if (self::hasAllRequiredFields($vals)) {
// Insert values into the post table
$post_key = parent::addPost($vals);
// If the post insertion went fine insert the values that go into the photos table
if ($post_key) {
//SQL variables to bind
$vars = array();
//SQL query
$q = "INSERT IGNORE INTO #prefix#photos SET ";
//Set up required fields
foreach ($this->REQUIRED_PHOTO_FIELDS as $field) {
$q .= $field . "=:" . $field . ", ";
$vars[':' . $field] = $vals[$field];
}
//Set up any optional fields
foreach ($this->OPTIONAL_PHOTO_FIELDS as $field) {
if (isset($vals[$field]) && $vals[$field] != '') {
$q .= $field . "=:" . $field . ", ";
$vars[':' . $field] = $vals[$field];
}
}
// Append the internal post ID
$q .= 'post_key=:post_key;';
$vars[':post_key'] = $post_key;
// Insert the photo in the database
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$res = $this->getInsertId($ps);
return $res;
}
} else {
$status_message = "Could not insert photo ID " . $vals["post_id"] . ", missing values";
$this->logger->logError($status_message, __METHOD__ . ',' . __LINE__);
//doesn't have all req'd values
return false;
}
}
public function getPostPlace($post_id, $network = 'twitter')
{
$q = "SELECT id, AsText(longlat) AS longlat, post_id, place_id, network FROM #prefix#places_posts " . "WHERE post_id = :post_id AND network = :network";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, array(':post_id' => (string) $post_id, ':network' => $network));
$row = $this->getDataRowAsArray($ps);
if ($row) {
return $row;
} else {
return null;
}
}
public function getNewMembershipsByDate($network, $member_user_id, $from_date = null)
{
$vars = array(':member_user_id' => strval($member_user_id), ':network' => $network);
if (!isset($from_date)) {
$from_date = 'CURRENT_DATE()';
} else {
$vars[':from_date'] = $from_date;
$from_date = ':from_date';
}
$q = "SELECT g.* FROM #prefix#group_members gm INNER JOIN #prefix#groups g on g.group_id = gm.group_id ";
$q .= "WHERE gm.member_user_id=:member_user_id AND g.network=:network AND gm.is_active=1 ";
$q .= "AND (YEAR(gm.first_seen)=YEAR({$from_date})) ";
$q .= "AND (DAYOFMONTH(gm.first_seen)=DAYOFMONTH({$from_date})) AND (MONTH(gm.first_seen)=MONTH({$from_date})) ";
$q .= "ORDER BY gm.first_seen DESC;";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$groups = $this->getDataRowsAsObjects($ps, "Group");
return $groups;
}
public function findStalestMemberships($user_id, $network)
{
$q = "SELECT g.id, g.group_id, g.group_name, g.network, DATEDIFF(NOW(), m.last_seen) AS days_old ";
$q .= "FROM #prefix#groups AS g ";
$q .= "INNER JOIN #prefix#group_members AS m ON m.group_id = g.group_id AND g.network = m.network ";
$q .= "WHERE m.member_user_id = :user_id and m.network = :network AND m.is_active=1 ";
$q .= "AND m.last_seen < DATE_SUB(NOW(), INTERVAL 1 DAY) ";
$q .= "ORDER BY days_old DESC LIMIT 1";
$vars = array(':user_id' => (string) $user_id, ':network' => $network);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getDataRowAsObject($ps, "Group");
}
public function resetID()
{
$q = "ALTER TABLE #prefix#stream_data auto_increment = 1";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q);
}
public function deleteProcess($process_id)
{
$q = "DELETE FROM #prefix#stream_procs ";
$q .= "WHERE process_id = :process_id";
$vars = array(':process_id' => $process_id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$res = $this->getUpdateCount($ps);
if (!$res) {
throw new StreamingException("Could not delete pid {$process_id}");
}
}
public function insert($group_id, $group_name, $network)
{
$q = "INSERT INTO #prefix#groups ";
$q .= "(group_id, group_name, first_seen, last_seen, network) ";
$q .= "VALUES ( :group_id, :group_name, NOW(), NOW(), :network );";
$vars = array(':group_id' => (string) $group_id, ':group_name' => $group_name, ':network' => $network);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getInsertId($ps);
}
public function getVersionBeforeDay($user_key, $before_day, $field)
{
$vars = array(':user_key' => $user_key, ':before_day' => $before_day, ':field_name' => $field);
$q = "SELECT user_key, field_name, field_value, crawl_time FROM #prefix#user_versions as uv ";
$q .= "WHERE uv.user_key = :user_key AND date(crawl_time) < :before_day ";
$q .= " AND field_name = :field_name ";
$q .= " ORDER BY crawl_time DESC LIMIT 1";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$rows = $this->getDataRowsAsArrays($ps);
if (count($rows)) {
return $rows[0];
} else {
return null;
}
}
public function isValidPluginId($plugin_id)
{
$q = 'SELECT id FROM #prefix#plugins where id = :id';
$data = array(':id' => $plugin_id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$stmt = $this->execute($q, $data);
return $this->getDataIsReturned($stmt);
}
public function deleteCountry($country_id)
{
$q = "DELETE from #prefix#country WHERE id = :country_id LIMIT 1";
$vars = array(':country_id' => $country_id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
return $this->getUpdateCount($ps);
}
private function getRepliedToPostIDs($username, $network, $page, $page_size)
{
$page = $page - 1;
$start_on = $page * $page_size;
$q = "SELECT in_reply_to_post_id as post_id FROM #prefix#posts WHERE ";
$q .= "author_username = :author_username AND network=:network AND in_reply_to_post_id IS NOT null ";
$q .= "LIMIT " . $start_on . ", " . $page_size;
$vars = array(':author_username' => $username, ':network' => $network);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$stmt = $this->execute($q, $vars);
return $this->getDataRowsAsArrays($stmt);
}
public function getPostCountForYear($author_username, $network, $year)
{
$vars = array(':author_username' => $author_username, ':network' => $network, ':year' => $year);
$q = "SELECT YEAR(pub_date) as YEAR, COUNT(*) AS post_count FROM #prefix#posts p ";
$q .= "WHERE author_username = :author_username AND p.network = :network ";
$q .= "AND YEAR(pub_date) = :year";
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$all_rows = $this->getDataRowsAsArrays($ps);
return $all_rows[0];
}
public function getHighestClickCountByLinkID($link_id)
{
$q = "SELECT ls.click_count ";
$q .= "FROM #prefix#links_short ls ";
$q .= "WHERE ls.link_id = :link_id ";
$q .= "ORDER BY ls.click_count DESC LIMIT 1;";
$vars = array(':link_id' => $link_id);
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$result = $this->getDataRowsAsArrays($ps);
if (isset($result[0]["click_count"])) {
return $result[0]["click_count"];
} else {
return 0;
}
}
public function getLinksByUserSinceDaysAgo($user_id, $network, $limit = 0, $days_ago = 0)
{
$vars = array(':user_id' => $user_id, ':network' => $network);
$q = "SELECT l.*, p.in_retweet_of_post_id FROM #prefix#links AS l ";
$q .= "INNER JOIN #prefix#posts AS p ON p.id = l.post_key ";
$q .= "WHERE p.author_user_id=:user_id AND p.network=:network ";
$q .= "AND p.in_reply_to_user_id IS NULL ";
if ($days_ago != 0) {
$q .= "AND p.pub_date>=DATE_SUB(CURDATE(), INTERVAL :days_ago DAY) ";
$q .= "ORDER BY p.pub_date DESC ";
$vars[':days_ago'] = $days_ago;
}
if ($limit != 0) {
$q .= "ORDER BY p.pub_date DESC ";
$q .= "LIMIT :limit ";
$vars[':limit'] = $limit;
}
if ($this->profiler_enabled) {
Profiler::setDAOMethod(__METHOD__);
}
$ps = $this->execute($q, $vars);
$all_rows = $this->getDataRowsAsArrays($ps);
return $all_rows;
}
