public static function getUsers($data = array())
{
$key = md5(serialize($data));
static $result = array();
if (isset($result[$key])) {
return $result[$key];
}
$db = JO_Db::getDefaultAdapter();
$query = $db->select()->from('users', array('users.*', 'fullname' => "CONCAT(firstname,' ',lastname)"));
if (isset($data['start']) && isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
$query->limit($data['limit'], $data['start']);
}
if (isset($data['sort']) && strtolower($data['sort']) == 'desc') {
$sort = ' DESC';
} else {
$sort = ' ASC';
}
$allow_sort = array('users.user_id', 'users.username', 'users.firstname', 'users.status', 'users.likers');
if (isset($data['filter_like_pin_id'])) {
$allow_sort[] = 'pins_likes.like_id';
}
if (isset($data['order']) && in_array($data['order'], $allow_sort)) {
$query->order($data['order'] . $sort);
} else {
$query->order('firstname' . $sort);
}
////////////filter
/*if(isset($data['filter_welcome']) && is_array($data['filter_welcome'])) {
$query->joinLeft('boards', 'boards.user_id = users.user_id', array())
->where('boards.category_id IN (?)', new JO_Db_Expr(implode(',',$data['filter_welcome'])))
->group('users.user_id');
}*/
if (isset($data['filter_welcome']) && is_array($data['filter_welcome'])) {
$query->where('users.user_id IN (SELECT user_id FROM boards WHERE category_id IN (?) OR category_id IN (SELECT category_id FROM category WHERE parent_id IN (?))) ', new JO_Db_Expr(implode(',', $data['filter_welcome'])));
}
if (isset($data['filter_followers_user_id']) && $data['filter_followers_user_id']) {
$query->joinLeft('users_following_user', 'users.user_id = users_following_user.following_id', array())->where('users_following_user.user_id = ?', (string) $data['filter_followers_user_id']);
}
if (isset($data['filter_following_user_id']) && $data['filter_following_user_id']) {
$query->joinLeft('users_following_user', 'users.user_id = users_following_user.user_id', array())->where('users_following_user.following_id = ?', (string) $data['filter_following_user_id']);
}
if (isset($data['filter_likers_user_id']) && $data['filter_likers_user_id']) {
$query->joinLeft('users_likes', 'users.user_id = users_likes.user_like_id', array())->where('users_likes.user_id = ?', (string) $data['filter_likers_user_id']);
}
if (isset($data['filter_liking_user_id']) && $data['filter_liking_user_id']) {
$query->joinLeft('users_likes', 'users.user_id = users_likes.user_id', array())->where('users_likes.user_like_id = ?', (string) $data['filter_liking_user_id']);
}
if (isset($data['filter_profile_top_10']) && !is_null($data['filter_profile_top_10'])) {
$query->where('users.likers > 0 ');
//$ignore_in = true;
}
if (isset($data['filter_profile_top_10_7']) && !is_null($data['filter_profile_top_10_7'])) {
$query->where('users.likers > 0 AND DATEDIFF(curdate(), date_likers) < ? ', (int) $data['filter_profile_top_10_7']);
//$ignore_in = true;
}
if (isset($data['filter_like_pin_id']) && $data['filter_like_pin_id']) {
$query->joinLeft('pins_likes', 'users.user_id = pins_likes.user_id')->where('pins_likes.pin_id = ?', (string) $data['filter_like_pin_id']);
}
if (isset($data['filter_user_id']) && $data['filter_user_id']) {
$query->where('users.user_id = ?', (string) $data['filter_user_id']);
}
if (isset($data['filter_firstname']) && $data['filter_firstname']) {
$query->where('users.firstname = ?', (string) $data['filter_firstname']);
}
if (isset($data['filter_location']) && $data['filter_location']) {
if ($data['filter_location'] != "Introduce una ubicación") {
$query->where('users.location LIKE ?', "%" . str_replace(",", "%", (string) $data['filter_location']) . "%");
}
}
if (isset($data['filter_gender']) && $data['filter_gender']) {
$query->where('users.gender = ?', (string) $data['filter_gender']);
}
if (isset($data['filter_sport_category']) && $data['filter_sport_category']) {
$query->where('(users.sport_category_1 = ? OR users.sport_category_2 = ? OR users.sport_category_3 = ?)', $data['filter_sport_category']);
}
if (isset($data['filter_sport_category_1']) && $data['filter_sport_category_1']) {
//$query->where('users.sport_category_1 = ? ', $data['filter_sport_category_1']);
$query->where('users.sport_category_1 in (select category_id FROM category where parent_id IN (?) or category.category_id IN (?))', new JO_Db_Expr($data['filter_sport_category_1']));
}
if (isset($data['filter_typeuser']) && $data['filter_typeuser']) {
$datos = 0;
foreach ($data['filter_typeuser'] as $type_user) {
if ($type_user != "") {
$query->where('users.type_user = ?', (string) $type_user);
$datos = 1;
}
}
if ($datos == 0) {
if (isset($data['filter_typeuser_profesional']) && $data['filter_typeuser_profesional']) {
$query->where('users.type_user in (2, 3, 4, 6, 7, 8, 9, 10, 11)');
}
}
}
if (isset($data['filter_username']) && $data['filter_username']) {
if (isset($data['filter_sport_category_1']) && $data['filter_sport_category_1']) {
if (Model_Categories::getCategoryFromTitle($data['filter_username'])) {
$query->where('sport_category_1 in (select category_id FROM category where category.title = "' . (string) $data['filter_username'] . '")');
}
} else {
if (!Model_Categories::getCategoryFromTitle($data['filter_username'])) {
$query->where('CONCAT(users.firstname,users.lastname) LIKE ? OR users.firstname LIKE ? OR users.lastname LIKE ? OR users.username LIKE ?', '%' . str_replace(' ', '%', $data['filter_username']) . '%');
} else {
$query->where('sport_category_1 in (select category_id FROM category where category.title = "' . (string) $data['filter_username'] . '")');
//$query->where('CONCAT(users.firstname,users.lastname) LIKE ? OR users.firstname LIKE ? OR users.lastname LIKE ? OR users.username LIKE ?', '%' . str_replace(' ', '%', $data['filter_username']) . '%');
}
}
}
//error_log("query" . $query);
$results = $db->fetchAll($query);
$result[$key] = array();
if ($results) {
foreach ($results as $data) {
$data['pins_array'] = array();
if (trim($data['latest_pins'])) {
$data['pins_array'] = $db->fetchAll($db->select()->from('pins')->where("pin_id IN ('?')", new JO_Db_Expr(implode("','", explode(',', $data['latest_pins']))))->order('pin_id DESC')->limit(15));
}
$result[$key][] = $data;
}
}
return $result[$key];
}
public static function getBoards($data = array())
{
$key = md5(serialize($data));
static $result = array();
// if(isset($result[$key])) { return $result[$key]; }
$db = JO_Db::getDefaultAdapter();
$query = $db->select()->from('boards');
/*if(isset($data['friendly']) && $data['friendly']) {
$query->joinLeft('users_boards', 'boards.board_id=users_boards.board_id AND is_author = 1', '')
->group('boards.board_id');
} elseif(isset($data['filter_user_id']) && !is_null($data['filter_user_id'])) {
} else {
$query->joinLeft('users_boards', 'boards.board_id=users_boards.board_id AND is_author = 1', '')
->group('boards.board_id');
}*/
if (isset($data['where']) && $data['where'] instanceof JO_Db_Expr) {
$query->where($data['where']);
}
if (isset($data['delete_request']) && $data['delete_request'] === true) {
$query->where('boards.delete_request = 1');
}
if (isset($data['filter_id_in']) && $data['filter_id_in']) {
$query->where('board_id = ?', (string) $data['filter_id_in']);
}
if (isset($data['filter_category_id']) && $data['filter_category_id']) {
//$query->where('category_id = ?', (string)$data['filter_category_id']);
if (!isset($data['filter_description'])) {
$query->where('category_id in (select category_id FROM category where parent_id IN (?) or category.category_id IN (?))', new JO_Db_Expr($data['filter_category_id']));
} else {
$query->where('category_id in (select category_id FROM category where parent_id IN (?) or category.category_id IN (?) or category.title = "' . (string) $data['filter_title'] . '")', new JO_Db_Expr($data['filter_category_id']));
}
}
if (isset($data['filter_title']) && $data['filter_title']) {
if (isset($data['filter_category_id']) && $data['filter_category_id']) {
if (Model_Categories::getCategoryFromTitle($data['filter_title'])) {
$query->where('category_id in (select category_id FROM category where category.title = "' . (string) $data['filter_title'] . '")');
}
} else {
if (!Model_Categories::getCategoryFromTitle($data['filter_title'])) {
$query->where('boards.title LIKE "%' . (string) $data['filter_title'] . '%"');
} else {
$query->where('category_id in (select category_id FROM category where category.title = "' . (string) $data['filter_title'] . '")');
//$query->where('boards.title LIKE "%'. (string)$data['filter_title'] . '%"');
}
}
}
// if(isset($data['friendly']) && $data['friendly']) {
// if(isset($data['filter_user_id']) && !is_null($data['filter_user_id'])) {
// $query->where("(user_id = '".(string)$data['filter_user_id']."' OR board_id IN (SELECT DISTINCT board_id FROM users_boards WHERE user_id = ? AND allow = 1))", (string)$data['friendly']);
// } else {
// $query->where('board_id IN (SELECT DISTINCT board_id FROM users_boards WHERE user_id = ? AND allow = 1)', (string)$data['friendly']);
// }
// }
if (isset($data['friendly']) && $data['friendly']) {
if (isset($data['filter_user_id']) && !is_null($data['filter_user_id'])) {
$query->where("(boards.user_id = '" . (string) $data['filter_user_id'] . "' OR boards.board_id IN (SELECT DISTINCT board_id FROM users_boards WHERE user_id = ? AND allow = 1))", (string) $data['friendly']);
} else {
$query->where('boards.board_id IN (SELECT DISTINCT board_id FROM users_boards WHERE user_id = ? AND allow = 1)', (string) $data['friendly']);
}
} elseif (isset($data['filter_user_id']) && !is_null($data['filter_user_id'])) {
if (isset($data['friendly']) && $data['friendly']) {
$query->where("(boards.user_id = '" . (string) $data['filter_user_id'] . "' OR boards.board_id IN (SELECT DISTINCT board_id FROM users_boards WHERE user_id = ? AND allow = 1))", (string) $data['friendly']);
} else {
$query->where('boards.user_id = ?', (string) $data['filter_user_id']);
}
}
if (isset($data['start']) && isset($data['limit'])) {
if ($data['start'] < 0) {
$data['start'] = 0;
}
$query->limit($data['limit'], $data['start']);
}
if (isset($data['sort']) && strtolower($data['sort']) == 'asc') {
$sort = ' ASC';
} else {
$sort = ' DESC';
}
$allow_sort = array('boards.board_id', 'boards.title', 'boards.sort_order', 'boards.total_views', 'users_boards.sort_order');
if (isset($data['order']) && in_array($data['order'], $allow_sort)) {
$query->order($data['order'] . $sort);
} else {
$query->order('boards.board_id' . $sort);
}
//error_log($query);
$results = $db->fetchAll($query);
$result[$key] = array();
if ($results) {
foreach ($results as $data) {
$data['pins_array'] = array();
if (trim($data['latest_pins'])) {
$data['pins_array'] = $db->fetchAll($db->select()->from('pins')->where("pin_id IN ('?')", new JO_Db_Expr(implode("','", explode(',', $data['latest_pins']))))->order($data['cover'] ? 'FIELD(pin_id, "' . $data['cover'] . '") DESC, pin_id DESC' : 'pin_id DESC')->limit(15));
}
$result[$key][] = $data;
}
}
return $result[$key];
}
/**
* @param JO_Db_Select $query
* @param array $data
* @return JO_Db_Select
*/
private static function FilterBuilder(JO_Db_Select $query, $data = array())
{
$db = JO_Db::getDefaultAdapter();
$ignore_in = false;
$query->where('pins.store != ""');
if (isset($data['filter_likes']) && $data['filter_likes']) {
$ignore_in = true;
}
// if(isset($data['filter_likes']) && $data['filter_likes'] ) {
//// $query->where("pins.pin_id IN ( SELECT DISTINCT pin_id FROM pins_likes WHERE user_id = '".$data['filter_likes']."' )");
// $query->joinLeft('pins_likes', 'pins.pin_id = pins_likes.pin_id', array())
// ->where('pins_likes.user_id = ?', $data['filter_likes'])
// /*->group('pins.pin_id')*/;
// $ignore_in = true;
// }
if (isset($data['filter_pin_id']) && $data['filter_pin_id']) {
$query->where('pins.pin_id = ?', (string) $data['filter_pin_id']);
}
if (isset($data['filter_like_repin_comment']) && $data['filter_like_repin_comment'] === true) {
$query->where('pins.likes > 0')->where('pins.repins > 0')->where('pins.comments > 0');
}
if (isset($data['delete_request']) && $data['delete_request'] === true) {
$query->where('pins.delete_request = 1');
}
if (isset($data['filter_price_from']) && (int) $data['filter_price_from']) {
$query->where('pins.price >= ?', (int) $data['filter_price_from']);
$ignore_in = true;
} elseif (isset($data['allow_gifts'])) {
$query->where('pins.price > 0.0000');
$ignore_in = true;
}
if (isset($data['filter_id_in']) && $data['filter_id_in']) {
$query->where('pins.pin_id IN (?)', new JO_Db_Expr($data['filter_id_in']));
$ignore_in = true;
}
if (isset($data['filter_id_not']) && $data['filter_id_not']) {
$query->where('pins.pin_id NOT IN (?)', new JO_Db_Expr($data['filter_id_not']));
$ignore_in = true;
}
if (isset($data['filter_price_to']) && (int) $data['filter_price_to']) {
$query->where('pins.price <= ?', (int) $data['filter_price_to']);
$ignore_in = true;
}
if (isset($data['filter_marker']) && (string) $data['filter_marker']) {
$query->where('pins.pin_id <= ?', (string) $data['filter_marker']);
$ignore_in = true;
}
if (isset($data['filter_repin_from']) && !is_null($data['filter_repin_from'])) {
$query->where('pins.repin_from = ?', (string) $data['filter_repin_from']);
$ignore_in = true;
}
if (isset($data['filter_source_id']) && !is_null($data['filter_source_id'])) {
$query->where('pins.source_id = ?', (string) $data['filter_source_id']);
$ignore_in = true;
}
if (isset($data['filter_from']) && !is_null($data['filter_from'])) {
$query->where('pins.from = ?', $data['filter_from']);
$ignore_in = true;
}
if (isset($data['filter_from_md5']) && !is_null($data['filter_from_md5'])) {
$query->where('pins.from_md5 = ?', $data['filter_from_md5']);
$ignore_in = true;
}
if (isset($data['filter_categoria_id']) && !is_null($data['filter_categoria_id'])) {
$query->where('pins.category_id in (select category_id FROM category where parent_id IN (?) or category.category_id IN (?))', new JO_Db_Expr($data['filter_categoria_id']));
$ignore_in = true;
}
if (isset($data['filter_category_id']) && !is_null($data['filter_category_id'])) {
//$query->where('pins.category_id in (?)', new JO_Db_Expr($data['filter_category_id']));
if (!isset($data['filter_description'])) {
$query->where('pins.category_id in (select category_id FROM category where parent_id IN (?) or category.category_id IN (?))', new JO_Db_Expr($data['filter_category_id']));
} else {
$query->where('pins.category_id in (select category_id FROM category where parent_id IN (?) or category.category_id IN (?) or category.title = "' . (string) $data['filter_description'] . '")', new JO_Db_Expr($data['filter_category_id']));
}
$ignore_in = true;
}
if (isset($data['filter_is_image']) && !is_null($data['filter_is_image'])) {
$query->where('pins.is_video = 0');
$query->where('pins.is_article = 0');
$query->where('pins.price = 0');
$ignore_in = true;
}
if (isset($data['filter_is_video']) && !is_null($data['filter_is_video'])) {
$query->where('pins.is_video = ?', (int) $data['filter_is_video']);
$ignore_in = true;
}
if (isset($data['filter_is_article']) && !is_null($data['filter_is_article'])) {
$query->where('pins.is_article = ?', (int) $data['filter_is_article']);
$ignore_in = true;
}
if (isset($data['filter_pin_top_10']) && !is_null($data['filter_pin_top_10'])) {
$query->where('pins.likes > 0 ');
$ignore_in = true;
}
if (isset($data['filter_pin_top_10_7']) && !is_null($data['filter_pin_top_10_7'])) {
$query->where('pins.likes > 0 AND DATEDIFF(curdate(), date_likes) < ? ', (int) $data['filter_pin_top_10_7']);
$ignore_in = true;
}
if (isset($data['filter_board_id']) && !is_null($data['filter_board_id'])) {
$query->where('pins.board_id = ?', (string) $data['filter_board_id']);
$ignore_in = true;
}
// if(isset($data['filter_ub_id']) && !is_null($data['filter_ub_id'])) {
// $query->where('pins.ub_id = ?', (string)$data['filter_ub_id']);
// $ignore_in = true;
// }
if (isset($data['filter_user_id']) && !is_null($data['filter_user_id'])) {
$query->where('pins.user_id = ?', (string) $data['filter_user_id']);
$ignore_in = true;
}
if (isset($data['filter_description'])) {
if (is_null($data['filter_category_id'])) {
if (Model_Categories::getCategoryFromTitle($data['filter_description'])) {
$query->where('pins.category_id in (select category_id FROM category where category.title = "' . (string) $data['filter_description'] . '")');
}
} else {
if (!Model_Categories::getCategoryFromTitle($data['filter_description'])) {
$words = JO_Utf8::str_word_split(mb_strtolower($data['filter_description'], 'utf-8'), self::$searchWordLenght);
if (count($words) > 0) {
/*$sub = "SELECT `i`.`pin_id` FROM `pins_invert` `i`, `pins_dictionary` `d` WHERE `i`.`dic_id` = `d`.`dic_id` AND ( ";
foreach($words AS $key => $word) {
if($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$query->where('pins.pin_id IN (' . $sub . ')');
$sub = "SELECT `dic_id`, `dic_id` FROM `pins_dictionary` `d` WHERE ( ";
foreach($words AS $key => $word) {
if($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$dicts = $db->fetchPairs($sub);
$tmp_dic_ids = array();
if(COUNT($dicts) > 0) {
$pins = $db->fetchPairs("SELECT `pin_id`, `pin_id` FROM `pins_invert` `i` WHERE `i`.`dic_id` IN (" . implode(',', $dicts) . ")");
if(count($pins) > 0) {
$query->where('pins.pin_id IN (' . implode(',', $pins) . ')');
} else {
$query->where('pins.pin_id = 0');
}
} else {
$query->where('pins.pin_id = 0');
}*/
$sub = "SELECT `dic_id`, `dic_id` FROM `pins_dictionary` `d` WHERE ( ";
foreach ($words as $key => $word) {
if ($key) {
$sub .= ' OR ';
}
$sub .= "`d`.`word` = " . $db->quote($word) . " OR MATCH(`d`.`word`) AGAINST (" . $db->quote($word) . ")";
}
$sub .= ')';
$dicts = $db->fetchPairs($sub);
$tmp_dic_ids = array();
if (COUNT($dicts) > 0) {
// $pins = $db->fetchPairs("SELECT `pin_id`, `pin_id` FROM `pins_invert` `i` WHERE `i`.`dic_id` IN (" . implode(',', $dicts) . ")");
//$query->where('pins.pin_id IN ( SELECT DISTINCT `pin_id` FROM `pins_invert` `i` WHERE `i`.`dic_id` IN (' . implode(',', $dicts) . ') )');
$query->joinLeft('pins_invert', 'pins.pin_id = pins_invert.pin_id', 'dic_id')->where('pins_invert.`dic_id` IN (' . implode(',', $dicts) . ')')->group('pins.pin_id');
$ignore_in = true;
} else {
$query->where('pins.pin_id = 0');
}
} else {
$query->where('pins.pin_id = 0');
}
}
}
}
if (isset($data['following_users_from_user_id']) && (string) $data['following_users_from_user_id']) {
if (JO_Session::get('user[following]')) {
$sql1 = "SELECT following_id FROM users_following_user WHERE user_id = ?";
$sql = "SELECT p.pin_id FROM `users_following` a, pins p WHERE a.following_id NOT IN(" . $sql1 . ") AND a.`board_id` = p.`board_id`";
$query->where("((pins.pin_id IN (" . $sql . ") OR pins.user_id IN (" . $sql1 . ")) AND (`board_id` NOT IN (SELECT board_id FROM `users_following_ignore` WHERE following_id IN(" . $sql1 . ") AND user_id = ?))) OR pins.user_id = ?", (string) JO_Session::get('user[user_id]'));
}
/*$sql1 = "SELECT following_id FROM users_following_user WHERE user_id = ?";
// $sql = "SELECT p.pin_id FROM `users_following` a, `users_boards` b, pins p WHERE
// a.following_id NOT IN(" . $sql1 . ") AND a.`board_id`=b.`board_id` AND b.`public`=1 AND a.`board_id` = p.`board_id` AND
// (p.pin_id IN (SELECT pin_id FROM pins WHERE public = 1) OR p.user_id=".(string)JO_Session::get('user[user_id]').")";
// $sql = "SELECT p.pin_id FROM `users_following` a, `users_following_ignore` c, `boards` b, pins p WHERE
// a.following_id NOT IN(" . $sql1 . ") AND a.`board_id`=b.`board_id` AND b.`public`=1 AND a.`board_id` = p.`board_id` AND
// (p.pin_id IN (SELECT pin_id FROM pins WHERE public = 1) OR p.user_id=?)";
$sql = "SELECT p.pin_id FROM `users_following` a, pins p WHERE a.following_id NOT IN(" . $sql1 . ") AND a.`board_id` = p.`board_id`";
$query->where("((pins.pin_id IN (" . $sql . ") OR pins.user_id IN (" . $sql1 . ")) AND (`board_id` NOT IN (SELECT board_id FROM `users_following_ignore` WHERE following_id IN(" . $sql1 . ") AND user_id = ?))) OR pins.user_id = ?", (string)JO_Session::get('user[user_id]'));
// $query->where("pins.board_id IN (SELECT board_id FROM users_following WHERE user_id = '" . (string)$data['following_users_from_user_id'] . "')");
/*$sql = "SELECT board_id FROM users_boards WHERE (
user_id IN (
SELECT following_id FROM users_following_user WHERE user_id = ?
)
OR
board_id IN (
SELECT board_id FROM users_following WHERE user_id = ?
)
)
AND
board_id NOT IN (
SELECT board_id FROM users_following_ignore WHERE user_id = ?
)";
$query->where("pins.board_id IN (" . $sql . ") OR pins.user_id = ?", (string)JO_Session::get('user[user_id]'));
*/
} else {
// if($ignore_in) {
// if((string)JO_Session::get('user[user_id]')) {
// $query->where("(pins.public = 1 OR user_id = ".(string)JO_Session::get('user[user_id]').")");
// } else {
// $query->where("pins.public = 1");
// }
// } else {
// if((string)JO_Session::get('user[user_id]')) {
// $query->where("(pins.pin_id IN (SELECT pin_id FROM pins WHERE public = 1)) OR (pins.user_id IN (SELECT DISTINCT user_id FROM pins WHERE user_id = ".(string)JO_Session::get('user[user_id]')."))");
// } else {
// $query->where("pins.pin_id IN (SELECT pin_id FROM pins WHERE public = 1)");
// }
// }
}
return $query;
}
