private static function create($r) { $relation_attributes = $r['attributes']; $inserts = array(); $args = array(); foreach (EntityRelation::$columns as $i => $k) { if (empty($r[$k])) { if ($k == 'end_time') { // end_time may be null continue; } else { throw new Exception("Missing entityrelations table column '{$k}'"); } } $inserts[] = "{$k}=?"; $args[] = $r[$k]; } // create it Dal::query("INSERT INTO {entityrelations} SET " . implode(",", $inserts), $args); // get the id $id = Dal::query_first("SELECT id FROM {entityrelations} \n \tWHERE " . implode(" AND ", $inserts), $args); // deal with attributes $relation_attributes['created'] = time(); $relation_attributes['updated'] = time(); foreach ($relation_attributes as $ak => $d) { if (!is_array($d)) { $ad = array('value' => $d); } else { $ad = (array) $d; } // create it Dal::query("INSERT INTO {entityrelationattributes} \n\t\t\tSET id=?, attribute_name=?, attribute_value=?, attribute_permission=?", array($id, $ak, $ad['value'], @$ad['permission'])); } return $id; }
function save() { $ct = Dal::query_first("SELECT COUNT(*) FROM {fans} WHERE is_active=1 AND user_id=? AND subject_type=? AND subject_id=?", array($this->user_id, $this->subject_type, $this->subject_id)); $is_new = $ct ? 0 : 1; if ($is_new) { if (!isset($this->is_active)) { $this->is_active = 1; } } $sql = $is_new ? "INSERT INTO {fans} SET " : "UPDATE {fans} SET "; $args = array(); $set_fragments = array(); foreach (Fan::$columns as $col) { switch ($col) { case 'fan_id': // never set continue; case 'updated': // always set automatically $set_fragments[] = "{$col}=NOW()"; break; case 'created': // set automatically if new, otherwise copy if ($is_new) { $set_fragments[] = "{$col}=NOW()"; break; } // else fallthrough // else fallthrough default: $set_fragments[] = "{$col}=?"; $args[] = $this->{$col}; } } $sql .= implode(", ", $set_fragments); if (!$is_new) { $sql .= " WHERE fan_id=?"; $args[] = $this->fan_id; } Dal::query($sql, $args); if ($is_new) { $this->fan_id = Dal::insert_id(); } /* // update denormalization Dal::query("UPDATE {items} SET fan_count=( SELECT COUNT(*) FROM {fans} WHERE is_active=1 AND subject_type=? AND subject_id=?) WHERE is_active=1 AND subject_type=? AND subject_id=?", array( $this->subject_type, $this->subject_id, $this->subject_type, $this->subject_id)); Dal::query("UPDATE {users} SET fan_count=( SELECT COUNT(*) FROM {fans} WHERE is_active=1 AND user_id=?) WHERE is_active=1 AND user_id=?", array( $this->user_id, $this->user_id)); */ return $this->fan_id; }
public static function get_recent_by_user($uid, $per_page = 10, $page = 1, $is_active_users = 1) { $per_page = (int) $per_page; $page = (int) $page; $sql = "SELECT COUNT(*) FROM {reviews} AS R, {users} AS U WHERE U.user_id=R.author_id AND R.is_active=1 AND U.is_active=? AND author_id=?"; $ct = Dal::query_first($sql, array($is_active_users, $uid)); $n_pages = (int) ceil($ct / $per_page); $start = ($page - 1) * $per_page; $sql = "SELECT *, R.created as created FROM {reviews} AS R, {users} AS U WHERE U.user_id=R.author_id AND R.is_active=1 AND U.is_active=? AND author_id=? ORDER BY R.created DESC LIMIT {$start}, {$per_page}"; $reviews = self::load_many_from_query("Review", $sql, array($is_active_users, $uid)); return array($reviews, $ct, $n_pages, $page, $per_page); }
/** Function to convert the slug to a unique channel_id. **/ public static function convert_slug_to_channel_id($slug) { Logger::log("Enter: function ChannelComment::convert_slug_to_channel_id"); $sql = "SELECT channel_id FROM {channel_comment} WHERE slug = ? AND is_active = ?"; $data = array($slug, ACTIVE); $channel_id = Dal::query_first($sql, $data); if (empty($channel_id)) { $sql = "SELECT max(channel_id) FROM {channel_comment} "; $res = Dal::query_first($sql); if (empty($res)) { $channel_id = 1; } else { $channel_id = $res + 1; } } return $channel_id; Logger::log("Exit: function ChannelComment::convert_slug_to_channel_id"); }
public static function get_recent_by_user($uid, $per_page = 10, $page = 1, $is_active_users = 1) { $per_page = (int) $per_page; $page = (int) $page; /* "SELECT COUNT(*) FROM {comments2} WHERE is_active=1 AND author_id=?" */ $sql = "SELECT COUNT(*) FROM {comments2} AS C2, {users} AS U WHERE U.user_id=C2.author_id AND C2.is_active=1 AND U.is_active=? AND author_id=?"; $ct = Dal::query_first($sql, array($is_active_users, $uid)); $n_pages = (int) ceil($ct / $per_page); $start = ($page - 1) * $per_page; /* "SELECT * FROM {comments2} WHERE is_active=1 AND author_id=? ORDER BY created DESC LIMIT $start, $per_page" */ $sql = "SELECT *, C2.created as created FROM {comments2} AS C2, {users} AS U WHERE U.user_id=C2.author_id AND C2.is_active=1 AND U.is_active=? AND author_id=? ORDER BY C2.created DESC LIMIT {$start}, {$per_page}"; $comments = self::load_many_from_query("Comment2", $sql, array($is_active_users, $uid)); return array($comments, $ct, $n_pages, $page, $per_page); }
public static function is_network_valid($network_name) { return Dal::query_first("SHOW TABLES LIKE '" . Dal::quote($network_name) . "_comments'") ? TRUE : FALSE; }
/** * loads array of content-id in decreasing order of their edition. * @access public * @param int user id * @param int count * @param string sort factor. */ public static function load_content_id_array($user_id = 0, $type = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'created', $direction = 'DESC') { Logger::log("Enter: Content::load_content_id_array() | Args: \$user_id = {$user_id}"); $order_by = $sort_by . ' ' . $direction; if ($show == 'ALL' || $cnt == TRUE) { $limit = ''; } else { $start = ($page - 1) * $show; $limit = 'LIMIT ' . $start . ',' . $show; } switch ($type) { case SBMICROCONTENT_EVENT: $sb_pattern = 'event%'; break; case SBMICROCONTENT_REVIEW: $sb_pattern = 'review%'; break; case SBMICROCONTENT_PEOPLE: $sb_pattern = 'showcase/person%'; break; case SBMICROCONTENT_GROUP: $sb_pattern = 'showcase/group%'; break; case VIDEO: $sb_pattern = 'media/video%'; break; case AUDIO: $sb_pattern = 'media/audio%'; break; case IMAGE: $sb_pattern = 'media/image%'; break; default: $sb_pattern = NULL; break; } if ($sb_pattern) { if ($user_id == 0) { $author = " AND C.display_on = " . DISPLAY_ON_HOMEPAGE; } else { $author = " AND C.author_id = {$user_id}"; } $sql = "SELECT C.content_id As content_id, C.title As title, C.body As body, C.author_id As author_id, C.type as type, C.changed As changed, C.created as created FROM {contents} As C, {contents_sbmicrocontents} As CM, {sbmicrocontent_types} As SM WHERE C.collection_id < 0 AND C.is_active = ? {$author} AND C.type = ? AND CM.content_id = C.content_id AND SM.sbtype_id = CM.microcontent_id AND SM.name LIKE ? ORDER BY {$order_by} {$limit}"; $data = array(1, 7, $sb_pattern); } else { if ($type != NULL) { $with_type = " AND type={$type}"; } else { $with_type = ""; } if ($user_id == 0) { if ($cnt) { return Dal::query_first("SELECT COUNT(*) FROM {contents} WHERE collection_id=-1 AND is_active=1 AND display_on=? {$with_type}", array(DISPLAY_ON_HOMEPAGE)); } $sql = "SELECT content_id, title, body, type, author_id, changed, created FROM {contents} WHERE collection_id = -1 AND is_active = 1 AND display_on = ? {$with_type} ORDER BY {$order_by} {$limit}"; $data = array(DISPLAY_ON_HOMEPAGE); // navigate to blank page when we click on recent post "Full view" } else { $sql = "SELECT content_id, title, body, author_id, type, changed, name, created FROM {contents} As C LEFT JOIN {content_types} As CT ON C.type=CT.type_id WHERE C.collection_id = -1 AND C.author_id = ? AND C.is_active = 1 {$with_type} ORDER BY {$order_by} {$limit}"; //$sql = "SELECT content_id, title, body, author_id, changed FROM {contents} WHERE collection_id < 0 AND author_id = ? AND is_active = ?$with_type ORDER BY $order_by $limit"; $data = array($user_id); } } $res = Dal::query($sql, $data); // print '<pre>'; print_r($res); exit; if ($cnt) { return $res->numRows(); } $content_id = array(); if ($res->numRows()) { $i = 0; while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { $content_id[$i] = array('content_id' => $row['content_id'], 'title' => $row['title'], 'body' => $row['body'], 'author_id' => $row['author_id'], 'type' => $row['type'], 'changed' => $row['changed'], 'created' => $row['created'], 'type_name' => @$row['name']); $i++; } } Logger::log("Exit: Content::load_content_id_array()"); return $content_id; }
public function get_profile_field($information_type, $field_name) { $cache_key = "profile:{$this->user_id}:{$information_type}:{$field_name}"; $data = Cache::getValue($cache_key); if ($data === NULL) { $data = Dal::query_first("SELECT field_value FROM {user_profile_data} WHERE user_id=? AND field_type=? AND field_name=? AND seq IS NULL LIMIT 1", array($this->user_id, $information_type, $field_name)); Cache::setValue($cache_key, $data); } return $data; }
public static function get_id_from_task_value($task_value) { return Dal::query_first("SELECT id FROM {tasks} WHERE task_value=?", array($task_value)); }
public static function count_comment($parent_id, $parent_type) { return Dal::query_first("SELECT COUNT(*) FROM {comments} WHERE is_active=1 AND parent_id=? AND parent_type=? ", array($parent_id, $parent_type)); }
public function migrateLegacyFiles($dry_run = TRUE) { $this->dry_run = $dry_run; require_once PA::$path . "/db/Dal/DbUpdate.php"; echo "Migrating legacy files to new storage system\n"; $this->all_files = array(); if (!($h = opendir(PA::$path . '/web/files'))) { throw new PAException(GENERAL_SOME_ERROR, "Unable to open web/files directory"); } while (false !== ($f = readdir($h))) { if ($f[0] == '.') { continue; } $this->all_files[$f] = TRUE; } closedir($h); $this->unmatched = count($this->all_files); $this->unmatchable = 0; $this->matched = 0; $this->dupes = 0; echo "{$this->unmatched} files found\n"; echo "Matching with user images ...\n"; $sql = Dal::validate_sql("SELECT user_id,picture FROM {users}", $network); $sth = Dal::query($sql); while ($r = Dal::row($sth)) { list($uid, $pic) = $r; // user avatar $this->_matchLegacyFile($pic, array("role" => "avatar", "user" => $uid)); //TODO: user header image } $this->_dumpMatchResults(); $networks = DbUpdate::get_valid_networks(); echo "Processing " . count($networks) . " networks\n"; foreach ($networks as $network) { echo " Network: {$network}\n"; // network level stuff list($network_id, $act, $logo, $extra) = Dal::query_one("SELECT network_id, is_active, inner_logo_image, extra FROM networks WHERE address=?", array($network)); assert($act); // sanity check $extra = unserialize($extra); // network avatar $this->_matchLegacyFile($logo, array("role" => "avatar", "network" => $network_id)); // network header image $header_image = @$extra["basic"]["header_image"]["name"]; if (!empty($header_image)) { $this->_matchLegacyFile($header_image, array("role" => "header", "network" => $network_id)); } // emblems foreach (unserialize(Dal::query_first(Dal::validate_sql("SELECT data FROM {moduledata} WHERE modulename='LogoModule'"))) as $emblem) { $this->_matchLegacyFile($emblem["file_name"], array("role" => "emblem", "network" => $network_id)); } // group pictures $sth = Dal::query(Dal::validate_sql("SELECT collection_id, picture FROM {contentcollections} WHERE type=1 AND is_active=1", $network)); while ($r = Dal::row($sth)) { list($cid, $pic) = $r; $this->_matchLegacyFile($pic, array("role" => "avatar", "network" => $network_id, "group" => $cid)); $header = Dal::query_first(Dal::validate_sql("SELECT header_image FROM groups WHERE group_id=?", $network), array($cid)); $this->_matchLegacyFile($header, array("role" => "header", "network" => $network_id, "group" => $cid)); } /* disabled until we update peopleaggregator.net $sth = Dal::query(Dal::validate_sql("SELECT group_id, header_image FROM {groups}", $network)); while ($r = Dal::row($sth)) { list ($gid, $pic) = $r; $this->_matchLegacyFile($network, "group", $gid, $pic); } */ //TODO: advertisements // images, audio, video foreach (array("image", "audio", "video") as $table) { $sth = Dal::query(Dal::validate_sql('SELECT mc.content_id, mc.' . $table . '_file, c.author_id, c.collection_id, c.is_active FROM {' . $table . 's} mc LEFT JOIN {contents} c ON mc.content_id=c.content_id HAVING c.is_active=1', $network)); while ($r = Dal::row($sth)) { list($cid, $fn, $uid, $ccid, $act) = $r; $this->_matchLegacyFile($fn, array("role" => "media", "network" => $network_id, "content" => $cid)); } } } $this->_dumpMatchResults(); foreach ($this->all_files as $fn => $v) { if ($v === TRUE) { echo " * unmatchable: {$fn}\n"; } } echo "Overall results from web/files: "; $this->_dumpMatchResults(); }
public static function delete($entity_service, $entity_type, $entity_id) { // get the DB key for the entity $id = Dal::query_first("SELECT id FROM {entities} \n \tWHERE entity_service=? AND entity_type=? AND entity_id=?", array($entity_service, $entity_type, $entity_id)); if (empty($id)) { return false; } // delete the entity Dal::query("DELETE FROM {entities} \n \tWHERE entity_service=? AND entity_type=? AND entity_id=?", array($entity_service, $entity_type, $entity_id)); // delete entity attributes Dal::query("DELETE FROM {entityattributes} \n \tWHERE id=?", array($id)); // delete cache for entity AND entity_attribute Cache::flushExtCache(0, "entity:{$entity_service}:{$entity_type}:{$entity_id}"); Cache::flushExtCache(0, "entity_attributes:{$id}"); return true; }
/** * Function to get the next Video in the album, if exists, from the Video which is currently under view. */ public static function get_next_video($album_id, $video_id, $condition = '') { Logger::log("Enter: TekVideo::get_next_video"); if (empty($album_id) || empty($video_id)) { throw new PAException(BAD_PARAMETER, 'Invalid arguments'); Logger::log("Exit: TekVideo::get_next_video"); } $sql = 'SELECT MIN(MV.content_id) FROM {contents} AS C, {media_videos} AS MV WHERE C.collection_id = ? AND C.content_id = MV.content_id AND MV.content_id > ? AND is_active = 1' . $condition . ' ORDER BY created'; $data = array($album_id, $video_id); $content_id = Dal::query_first($sql, $data); Logger::log("Exit: TekVideo::get_next_video"); return $content_id; }
public static function count_testimonials($user_id, $status) { return Dal::query_first("SELECT COUNT(*) FROM {testimonials} WHERE is_active=1 AND recipient_id=? AND status=? ", array($user_id, $status)); }
/** get the list of the networks based on number of members in the network * @access public. */ public function get_largest_networks($cnt = FALSE, $show = 'ALL', $page = 1, $sort_by = 'created', $direction = 'DESC') { Logger::log("Enter: Network::get_largest_networks() "); if ($cnt) { $ct = Dal::query_first("SELECT COUNT(*) FROM networks WHERE is_active=1 AND type=" . REGULAR_NETWORK_TYPE); Logger::log("Exit: Network::get_largest_networks() (counted: count={$ct})"); return $ct; } $order_by = $sort_by . ' ' . $direction; if ($show == 'ALL' || $cnt == TRUE) { $limit = ''; } else { $start = ($page - 1) * $show; $limit = 'LIMIT ' . $start . ',' . $show; } $sql = "SELECT N.member_count AS members, N.*, N.owner_id AS owner_id, N.name as network_name FROM {networks} AS N WHERE N.type = " . REGULAR_NETWORK_TYPE . " AND N.is_active = 1 ORDER BY {$order_by} {$limit}"; $res = Dal::query($sql); $network_description = array(); if ($res->numRows()) { while ($row = $res->fetchRow(DB_FETCHMODE_OBJECT)) { $network_description[] = $row; } } Logger::log("Exit: Network::get_largest_networks() "); return $network_description; }
public static function admin_paged($namespace, $per_page = 10, $page = 1) { $per_page = (int) $per_page; $page = (int) $page; $sql = "SELECT COUNT(*) FROM {users}"; $ct = Dal::query_first($sql, array()); $n_pages = (int) ceil($ct / $per_page); $start = ($page - 1) * $per_page; $sql = "SELECT user_id, is_active, comment_count, review_count, rating_count FROM {users} ORDER BY created DESC LIMIT {$start}, {$per_page}"; $uids = self::load_many_from_query("User", $sql, array()); $shUsers = array(); foreach ($uids as $i => $u) { $su = new ShadowUser($namespace); $su->load_by_pa((int) $u->user_id); $su->is_active = $u->is_active; $su->comment_count = $u->comment_count; $su->review_count = $u->review_count; $su->rating_count = $u->rating_count; $shUsers[] = $su; } return array($shUsers, $ct, $n_pages, $page, $per_page); }
/** * loads array of content-id in decreasing order of their edition. * @access public * @param int user id * @param int count * @param string sort factor. */ public static function load_content_id_array($user_id = 0, $type = NULL, $cnt = FALSE, $show = 'ALL', $page = 0, $sort_by = 'created', $direction = 'DESC', $only_homepage = true) { Logger::log("Enter: Content::load_content_id_array() | Args: \$user_id = {$user_id}"); $order_by = $sort_by . ' ' . $direction; if ($show == 'ALL' || $cnt == TRUE) { $limit = ''; } else { $start = ($page - 1) * $show; $limit = 'LIMIT ' . $start . ',' . $show; } switch ($type) { case VIDEO: $sb_pattern = 'media/video%'; break; case AUDIO: $sb_pattern = 'media/audio%'; break; case IMAGE: $sb_pattern = 'media/image%'; break; default: $sb_pattern = NULL; break; } if ($sb_pattern) { if ($user_id == 0) { $author = " AND C.display_on = " . DISPLAY_ON_HOMEPAGE; } else { $author = " AND C.author_id = {$user_id}"; } $sql = "SELECT C.content_id As content_id, C.collection_id As collection_id, C.title As title, C.body As body, C.author_id As author_id, C.type as type, C.changed As changed, C.created as created FROM {contents} As C, {contents_sbmicrocontents} As CM, {sbmicrocontent_types} As SM WHERE C.collection_id < 0 AND C.is_active = ? {$author} AND C.type = ? AND CM.content_id = C.content_id AND SM.sbtype_id = CM.microcontent_id AND SM.name LIKE ? ORDER BY {$order_by} {$limit}"; $data = array(1, 7, $sb_pattern); } else { if ($type != NULL) { $with_type = " AND type={$type}"; } else { $with_type = ""; } if ($user_id == 0) { if ($cnt) { return Dal::query_first("SELECT COUNT(*) FROM {contents} WHERE is_active=1 AND display_on=? {$with_type}", array(DISPLAY_ON_HOMEPAGE)); } if ($only_homepage) { $homepage = " AND display_on = " . DISPLAY_ON_HOMEPAGE; } else { $homepage = " AND display_on IS NOT NULL"; } $sql = "SELECT content_id, collection_id, title, body, type, author_id, changed, created FROM {contents} WHERE is_active = 1 {$homepage} {$with_type} ORDER BY {$order_by} {$limit}"; $data = array(); } else { $sql = "SELECT content_id, collection_id, title, body, author_id, type, changed, name, created FROM {contents} As C LEFT JOIN {content_types} As CT ON C.type=CT.type_id WHERE C.collection_id = -1 AND C.author_id = ? AND C.is_active = 1 {$with_type} ORDER BY {$order_by} {$limit}"; //$sql = "SELECT content_id, title, body, author_id, changed FROM {contents} WHERE collection_id < 0 AND author_id = ? AND is_active = ?$with_type ORDER BY $order_by $limit"; $data = array($user_id); } } $res = Dal::query($sql, $data); if ($cnt) { return $res->numRows(); } $content_id = array(); if ($res->numRows()) { $i = 0; while ($row = $res->fetchRow(DB_FETCHMODE_ASSOC)) { $content_id[$i] = array('content_id' => $row['content_id'], 'collection_id' => $row['collection_id'], 'title' => $row['title'], 'body' => $row['body'], 'author_id' => $row['author_id'], 'type' => $row['type'], 'changed' => $row['changed'], 'created' => $row['created'], 'type_name' => isset($row['name']) ? $row['name'] : ''); $i++; } } $contents = array(); foreach ($content_id as &$_content) { if (!Content::check_content_privacy($_content)) { // check is content from a private Group $contents[] = $_content; } } // echo '<pre>'.print_r($cont,1).'</pre>'; Logger::log("Exit: Content::load_content_id_array()"); return $contents; }