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;
}
