public static function userAvatars($user)
{
$thumb_sizes = Model_Upload_Abstract::userThumbSizes();
$avatars = array();
foreach ($thumb_sizes as $size => $prefix) {
if (isset($user['user_avatar' . strtolower($prefix)]) && $user['user_avatar' . strtolower($prefix)] && count($thumb = explode('|||', $user['user_avatar' . strtolower($prefix)])) == 5) {
$avatars['avatar_image' . strtolower($prefix)] = $thumb[0];
$avatars['avatar_original' . strtolower($prefix)] = $thumb[3];
$avatars['avatar_width' . strtolower($prefix)] = $thumb[1];
$avatars['avatar_height' . strtolower($prefix)] = $thumb[2];
$avatars['avatar_mime' . strtolower($prefix)] = $thumb[4];
$avatars['avatar_size' . strtolower($prefix)] = $prefix;
$avatars['avatar_user_id' . strtolower($prefix)] = $user['user_id'];
} else {
$avatar = Helper_Uploadimages::avatar($user, $prefix);
if ($avatar) {
foreach ($avatar as $k => $v) {
$avatars['avatar_' . $k . strtolower($prefix)] = $v;
}
} else {
}
}
}
return $avatars;
}
/**
* @return Ambigous <JO_Db_Select, JO_Db_Select>
*/
public static function getListBoardsQuery()
{
$db = JO_Db::getDefaultAdapter();
$rows_users = self::describeTable('users', 'user_');
$rows_boards = self::describeTable('boards', 'board_');
/////other rows
//$rows_boards['board_url'] = new JO_Db_Expr('('.$db->select()->from('url_alias', 'IF(`path`,`path`,`keyword`)')->where('query = CONCAT(\'board_id=\',boards.board_id)')->limit(1).')');
$rows_boards['board_users_all'] = new JO_Db_Expr('(' . $db->select()->from('users_boards', 'GROUP_CONCAT(user_id)')->where("board_id = boards.board_id AND user_id != boards.user_id")->limit(1) . ')');
$rows_boards['board_users_allow'] = new JO_Db_Expr('(' . $db->select()->from('users_boards', 'GROUP_CONCAT(user_id)')->where("board_id = boards.board_id AND user_id != boards.user_id AND allow = 1")->limit(1) . ')');
$rows_boards['board_users_not_allow'] = new JO_Db_Expr('(' . $db->select()->from('users_boards', 'GROUP_CONCAT(user_id)')->where("board_id = boards.board_id AND user_id != boards.user_id AND allow = 0")->limit(1) . ')');
switch (Helper_Config::get('config_user_view')) {
case 'username':
$rows_users['user_fullname'] = new JO_Db_Expr('users.username');
break;
case 'firstname':
$rows_users['user_fullname'] = new JO_Db_Expr('users.firstname');
break;
case 'fullname':
default:
$rows_users['user_fullname'] = new JO_Db_Expr('CONCAT(users.firstname, " ", users.lastname)');
break;
}
if (JO_Session::get('user[user_id]')) {
$rows_boards['following_board'] = new JO_Db_Expr('((' . $db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = boards.user_id')->limit(1) . ')+(' . $db->select()->from('users_following', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = boards.user_id')->where('board_id = boards.board_id')->limit(1) . ') - (' . $db->select()->from('users_following_ignore', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = boards.user_id')->where('board_id = boards.board_id')->limit(1) . '))');
$rows_boards['following_user'] = new JO_Db_Expr('((' . $db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = boards.user_id')->limit(1) . ')+(' . $db->select()->from('users_following', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = boards.user_id')->where('board_id = boards.board_id')->limit(1) . '))');
} else {
$rows_boards['following_board'] = new JO_Db_Expr("0");
$rows_boards['following_user'] = new JO_Db_Expr("0");
}
$thumbs = Model_Upload_Abstract::userThumbSizes();
foreach ($thumbs as $size => $prefix) {
$rows_users['user_avatar' . strtolower($prefix)] = new JO_Db_Expr('(' . $db->select()->from('users_avatars', 'CONCAT_WS(\'|||\',image,width,height,original,mime)')->where('user_id = users.user_id')->where('size = ?', $prefix)->limit(1) . ')');
}
$rows_boards['latest_pins'] = new JO_Db_Expr("SUBSTRING_INDEX((" . $db->select()->from('pins', 'GROUP_CONCAT(pin_id ORDER BY `pin_id` DESC)')->where('board_id = boards.board_id')->limit(15) . "),',',15)");
$query = $db->select()->from('boards', $rows_boards)->joinLeft('users', 'boards.user_id = users.user_id', $rows_users);
//for public boards
if (Helper_Config::get('config_private_boards')) {
if (JO_Session::get('user[user_id]')) {
$query->where('boards.user_id = ? OR IF(boards.user_id = ?, 1, boards.public) = 1', JO_Session::get('user[user_id]'));
} else {
$query->where('boards.public = 1');
}
}
return $query;
}
/**
* @return Ambigous <JO_Db_Select, JO_Db_Select>
*/
public static function getListUsersQuery()
{
$db = JO_Db::getDefaultAdapter();
//$rows_users = self::describeTable('users','user_');
$rows_users = array('*');
switch (Helper_Config::get('config_user_view')) {
case 'username':
$rows_users['fullname'] = new JO_Db_Expr('users.username');
break;
case 'firstname':
$rows_users['fullname'] = new JO_Db_Expr('users.firstname');
break;
case 'fullname':
default:
$rows_users['fullname'] = new JO_Db_Expr('CONCAT(users.firstname, " ", users.lastname)');
break;
}
$thumbs = Model_Upload_Abstract::userThumbSizes();
foreach ($thumbs as $size => $prefix) {
$rows_users['user_avatar' . strtolower($prefix)] = new JO_Db_Expr('(' . $db->select()->from('users_avatars', 'CONCAT_WS(\'|||\',image,width,height,original,mime)')->where('user_id = users.user_id')->where('size = ?', $prefix)->limit(1) . ')');
}
if (JO_Session::get('user[user_id]')) {
$rows_users['following_user'] = new JO_Db_Expr('((' . $db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = users.user_id')->limit(1) . ') + (' . $db->select()->from('users_following', 'COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = users.user_id')->limit(1) . '))');
} else {
$rows_users['following_user'] = new JO_Db_Expr("0");
}
/*if(JO_Session::get('user[user_id]')) {
$rows_pins['pin_is_liked'] = new JO_Db_Expr('('.$db->select()->from('pins_likes', 'COUNT(like_id)')->where('pin_id = pins.pin_id')->where('user_id = ?', JO_Session::get('user[user_id]'))->limit(1).')');
} else {
$rows_pins['pin_is_liked'] = new JO_Db_Expr("'login'");
}
$thumbs = Model_Upload_Abstract::pinThumbSizes();
foreach($thumbs AS $size => $prefix) {
$rows_pins['pin_thumb'.strtolower($prefix)] = new JO_Db_Expr('('.$db->select()->from('pins_images','CONCAT_WS(\'|||\',image,width,height,original,mime)')->where('pin_id = pins.pin_id')->where('size = ?', $prefix)->limit(1).')');
}*/
$rows_users['latest_pins'] = new JO_Db_Expr("SUBSTRING_INDEX((" . $db->select()->from('pins', 'GROUP_CONCAT(pin_id ORDER BY `pin_id` DESC)')->where('user_id = users.user_id')->limit(15) . "),',',15)");
$query = $db->select()->from('users', $rows_users);
return $query;
}
/**
* @return Ambigous <JO_Db_Select, JO_Db_Select>
*/
public static function getListPinsQuery()
{
$db = JO_Db::getDefaultAdapter();
$rows_pins = self::describeTable('pins', 'pin_');
$rows_users = self::describeTable('users', 'user_');
$rows_via = self::describeTable('users', 'via_');
$rows_boards = self::describeTable('boards', 'board_');
/////other rows
$rows_pins['pin_gift'] = new JO_Db_Expr('pins.price > 0.0000');
//$rows_boards['board_url'] = new JO_Db_Expr('('.$db->select()->from('url_alias', 'IF(`path`,`path`,`keyword`)')->where('query = CONCAT(\'board_id=\',boards.board_id)')->limit(1).')');
switch (Helper_Config::get('config_user_view')) {
case 'username':
$rows_users['user_fullname'] = new JO_Db_Expr('users.username');
$rows_via['via_fullname'] = new JO_Db_Expr('via.username');
break;
case 'firstname':
$rows_users['user_fullname'] = new JO_Db_Expr('users.firstname');
$rows_via['via_fullname'] = new JO_Db_Expr('via.firstname');
break;
case 'fullname':
default:
$rows_users['user_fullname'] = new JO_Db_Expr('CONCAT(users.firstname, " ", users.lastname)');
$rows_via['via_fullname'] = new JO_Db_Expr('CONCAT(via.firstname, " ", via.lastname)');
break;
}
/*if(JO_Session::get('user[user_id]')) {
$rows_pins['following_board'] = new JO_Db_Expr('('.$db->select()->from('users_following','COUNT(users_following_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->where('board_id = pins.board_id')->limit(1) .')');
$rows_pins['following_user'] = new JO_Db_Expr('('.$db->select()->from('users_following_user', 'COUNT(ufu_id)')->where('user_id = ?', JO_Session::get('user[user_id]'))->where('following_id = pins.user_id')->limit(1).')');
} else {
$rows_pins['following_board'] = new JO_Db_Expr("'login'");
$rows_pins['following_user'] = new JO_Db_Expr("'login'");
}*/
if (JO_Session::get('user[user_id]')) {
$rows_pins['pin_is_liked'] = new JO_Db_Expr('(' . $db->select()->from('pins_likes', 'COUNT(like_id)')->where('pin_id = pins.pin_id')->where('user_id = ?', JO_Session::get('user[user_id]'))->limit(1) . ')');
} else {
$rows_pins['pin_is_liked'] = new JO_Db_Expr("0");
}
/* is reported */
$query_is_reported = $db->select()->from('pins_reports', 'COUNT(pr_id)')->where('pin_id = pins.pin_id')->where('checked = 0')->limit(1);
if ((string) JO_Session::get('user[user_id]')) {
$query_is_reported->where("user_id = '" . (string) JO_Session::get('user[user_id]') . "' OR user_ip = '" . JO_Request_Server::encode_ip(JO_Request::getInstance()->getClientIp()) . "'");
} else {
$query_is_reported->where("user_ip = ?", JO_Request_Server::encode_ip(JO_Request::getInstance()->getClientIp()));
}
$rows_pins['pin_is_reported'] = new JO_Db_Expr("(" . $query_is_reported . ")");
$thumbs = Model_Upload_Abstract::pinThumbSizes();
foreach ($thumbs as $size => $prefix) {
$rows_pins['pin_thumb' . strtolower($prefix)] = new JO_Db_Expr('(' . $db->select()->from('pins_images', 'CONCAT_WS(\'|||\',image,width,height,original,mime)')->where('pin_id = pins.pin_id')->where('size = ?', $prefix)->limit(1) . ')');
}
$thumbs = Model_Upload_Abstract::userThumbSizes();
foreach ($thumbs as $size => $prefix) {
$rows_users['user_avatar' . strtolower($prefix)] = new JO_Db_Expr('(' . $db->select()->from('users_avatars', 'CONCAT_WS(\'|||\',image,width,height,original,mime)')->where('user_id = users.user_id')->where('size = ?', $prefix)->limit(1) . ')');
}
$query = $db->select()->from('pins', $rows_pins)->joinLeft('users', 'pins.user_id = users.user_id', $rows_users)->joinLeft('boards', 'pins.board_id = boards.board_id', $rows_boards)->joinLeft(array('via' => 'users'), 'pins.via = via.user_id', $rows_via);
//for public boards
if (Helper_Config::get('config_private_boards')) {
if (JO_Session::get('user[user_id]')) {
$query->where('pins.user_id = ? OR IF(pins.user_id = ?, 1, pins.public) = 1', JO_Session::get('user[user_id]'));
} else {
$query->where('pins.public = 1');
}
}
return $query;
}