function xt_shareandtags($cid, $tags, $sortOrder)
{
global $wpdb;
$tags = array_reverse($tags);
$today_time = xt_get_todaytime();
$fields = "wp_xt_share.id,wp_xt_share.title,wp_xt_share.pic_url,wp_xt_share.from_type";
$order = "";
$join = "";
$groupby = "GROUP BY score";
$where = " WHERE 1=1 ";
if (!empty($cid) && $cid > 0) {
$xt_catalog = xt_get_catalog($cid);
if (!empty($xt_catalog)) {
if (isset($xt_catalog->children) && !empty($xt_catalog->children)) {
$join .= " INNER JOIN " . XT_TABLE_SHARE_CATALOG . " ON " . XT_TABLE_SHARE_CATALOG . ".id = " . XT_TABLE_SHARE . ".id ";
$where .= " AND " . XT_TABLE_SHARE_CATALOG . ".cid in(" . $wpdb->escape($xt_catalog->children) . "," . $cid . ") ";
} else {
$join .= " INNER JOIN " . XT_TABLE_SHARE_CATALOG . " ON " . XT_TABLE_SHARE_CATALOG . ".id = " . XT_TABLE_SHARE . ".id ";
$where .= $wpdb->prepare(" AND " . XT_TABLE_SHARE_CATALOG . ".cid=%d ", $cid);
}
}
}
switch ($sortOrder) {
case 'newest':
default:
$order = " ORDER BY " . XT_TABLE_SHARE . ".create_date DESC";
break;
case 'popular':
$day7_time = $today_time - 604800;
//7 days
$fields .= ",(UNIX_TIMESTAMP(" . XT_TABLE_SHARE . ".create_date) > {$day7_time}) AS time_sort ";
$order = " ORDER BY time_sort DESC," . XT_TABLE_SHARE . ".fav_count DESC";
break;
case 'hot':
$day30_time = $today_time - 2592000;
//30 days
$fields .= ",(UNIX_TIMESTAMP(" . XT_TABLE_SHARE . ".create_date) > {$day30_time}) AS time_sort ";
$order = " ORDER BY time_sort DESC," . XT_TABLE_SHARE . ".fav_count DESC";
break;
}
$againsts = array();
$results = array();
$whenthen = array();
if (!empty($tags)) {
$length = count($tags);
for ($i = 0; $i < $length; $i++) {
$tag = $tags[$i];
$segment = xt_segment_unicode($wpdb->escape($tag->title), '+');
$againsts[] = "({$segment})";
$results[$i] = array('tag' => $tag->title, 'share' => false);
$whenthen[] = " when (match(gm.content_match) against('{$segment}' IN BOOLEAN MODE)) = 1 then {$i} ";
}
$fields .= ",case " . implode('', $whenthen) . " END AS score ";
$against = implode(' ', $againsts);
$join .= " INNER JOIN wp_xt_share_match gm ON match(gm.content_match) against('{$against}' IN BOOLEAN MODE) AND gm.share_id=wp_xt_share.id ";
$sql = "SELECT * FROM (SELECT {$fields} FROM wp_xt_share {$join} {$where} {$order}) AS temp {$groupby}";
$shares = $wpdb->get_results($sql);
unset($sql);
if (!empty($shares)) {
foreach ($shares as $share) {
for ($j = 0; $j < 12; $j++) {
if ($share->score == $j) {
$results[$j]['share'] = $share;
continue;
}
}
}
}
}
return array_reverse($results);
}
function _get_shares($args)
{
global $wpdb;
$id = isset($args['id']) && absint($args['id']) ? absint($args['id']) : 0;
$album_id = isset($args['album_id']) && absint($args['album_id']) ? absint($args['album_id']) : 0;
if ($id > 0) {
//single
$result = $wpdb->get_row($wpdb->prepare("SELECT * FROM " . XT_TABLE_SHARE . " g WHERE g.id = %d", $id));
if (!empty($result)) {
return array('share' => array($result), 'total' => 1);
} else {
return array('share' => array(), 'total' => 0);
}
}
$user_id = $args['user_id'];
$sortOrder = $args['sortOrder'];
$cid = $args['cid'];
$share_per_page = $args['share_per_page'];
$page = $args['page'];
$s = $args['s'];
$price = $args['price'];
$isFavorite = $args['isFavorite'];
$isHome = $args['isHome'];
$fields = $args['fields'];
$table = $album_id > 0 ? XT_TABLE_SHARE_ALBUM : ($isFavorite ? XT_TABLE_FAVORITE : XT_TABLE_SHARE);
$join = '';
$where = '';
$groupby = '';
$order = '';
$pagination = '';
$found_rows = '';
if (!empty($cid) && $cid > 0) {
global $xt_catalog;
if (empty($xt_catalog) || $xt_catalog->id != $cid) {
$xt_catalog = xt_get_catalog($cid);
}
if (!empty($xt_catalog)) {
if (isset($xt_catalog->children) && !empty($xt_catalog->children)) {
$join .= " INNER JOIN " . XT_TABLE_SHARE_CATALOG . " ON " . XT_TABLE_SHARE_CATALOG . ".id = " . XT_TABLE_SHARE . ".id ";
$where .= " AND " . XT_TABLE_SHARE_CATALOG . ".cid in(" . $wpdb->escape($xt_catalog->children) . "," . $cid . ") ";
$groupby .= "GROUP BY " . XT_TABLE_SHARE . ".id";
} else {
$join .= " INNER JOIN " . XT_TABLE_SHARE_CATALOG . " ON " . XT_TABLE_SHARE_CATALOG . ".id = " . XT_TABLE_SHARE . ".id ";
$where .= $wpdb->prepare(" AND " . XT_TABLE_SHARE_CATALOG . ".cid=%d ", $cid);
}
}
} elseif ($cid == -1) {
$join = '';
$where = ' AND ' . XT_TABLE_SHARE . '.id NOT IN (SELECT ID FROM ' . XT_TABLE_SHARE_CATALOG . ')';
}
if (!empty($user_id) && $user_id > 0) {
if ($isHome) {
global $xt_pageuser_follows;
if (!empty($xt_pageuser_follows) && is_array($xt_pageuser_follows)) {
$followIds = implode(',', $xt_pageuser_follows);
$where .= " AND " . XT_TABLE_SHARE . ".user_id in (" . $followIds . ") ";
}
} elseif ($isFavorite) {
$where .= $wpdb->prepare(" AND " . XT_TABLE_FAVORITE . ".user_id = %d AND " . XT_TABLE_FAVORITE . ".type=1", $user_id);
} elseif ($album_id > 0) {
$where .= $wpdb->prepare(" AND " . XT_TABLE_SHARE_ALBUM . ".user_id = %d ", $user_id);
} else {
$where .= $wpdb->prepare(" AND " . XT_TABLE_SHARE . ".user_id = %d ", $user_id);
}
}
if ($isFavorite) {
$join .= " INNER JOIN " . XT_TABLE_SHARE . " ON " . XT_TABLE_FAVORITE . ".id=" . XT_TABLE_SHARE . ".id ";
} elseif (!empty($s)) {
$match_key = xt_segment_unicode($wpdb->escape($s), '+');
$join .= " INNER JOIN " . XT_TABLE_SHARE_MATCH . " gm ON match(gm.content_match) against('" . $match_key . "' IN BOOLEAN MODE) AND gm.share_id=" . XT_TABLE_SHARE . ".id ";
} elseif ($album_id > 0) {
$where .= $wpdb->prepare(" AND " . XT_TABLE_SHARE_ALBUM . ".album_id = %d ", $album_id);
$join .= " INNER JOIN " . XT_TABLE_SHARE . " ON " . XT_TABLE_SHARE_ALBUM . ".id=" . XT_TABLE_SHARE . ".id ";
}
$fields .= " ,{$wpdb->usermeta}.meta_value as user_avatar";
$join .= " LEFT JOIN {$wpdb->usermeta} ON {$wpdb->usermeta}.user_id = " . XT_TABLE_SHARE . ".user_id AND {$wpdb->usermeta}.meta_key='" . XT_USER_AVATAR . "' ";
if (!empty($price)) {
$prices = xt_prices();
switch ($price) {
case 'low':
$where .= " AND " . XT_TABLE_SHARE . ".price <= " . $prices['low']['end'];
break;
case 'medium':
$where .= " AND " . XT_TABLE_SHARE . ".price >= " . $prices['medium']['start'] . " AND " . XT_TABLE_SHARE . ".price <= " . $prices['medium']['end'];
break;
case 'high':
$where .= " AND " . XT_TABLE_SHARE . ".price >= " . $prices['high']['start'] . " AND " . XT_TABLE_SHARE . ".price <= " . $prices['high']['end'];
break;
case 'higher':
$where .= " AND " . XT_TABLE_SHARE . ".price >= " . $prices['higher']['start'];
break;
}
}
$today_time = xt_get_todaytime();
if ($isHome) {
$order = " ORDER BY " . XT_TABLE_SHARE . ".create_date DESC";
} elseif ($isFavorite) {
$order = " ORDER BY " . XT_TABLE_FAVORITE . ".create_date DESC";
} else {
switch ($sortOrder) {
case 'newest':
default:
$order = " ORDER BY " . XT_TABLE_SHARE . ".create_date DESC";
break;
case 'popular':
$day7_time = $today_time - 604800;
//7 days
$fields .= ",(UNIX_TIMESTAMP(" . XT_TABLE_SHARE . ".create_date) > {$day7_time}) AS time_sort ";
$order = " ORDER BY time_sort DESC," . XT_TABLE_SHARE . ".fav_count DESC";
break;
case 'hot':
$day30_time = $today_time - 2592000;
//30 days
$fields .= ",(UNIX_TIMESTAMP(" . XT_TABLE_SHARE . ".create_date) > {$day30_time}) AS time_sort ";
$order = " ORDER BY time_sort DESC," . XT_TABLE_SHARE . ".fav_count DESC";
break;
}
}
if ($share_per_page && $page) {
$pagination = $wpdb->prepare("LIMIT %d, %d", intval(($page - 1) * $share_per_page), intval($share_per_page));
}
if (!empty($where)) {
$where = ' WHERE 1=1 ' . $where;
}
if (!$args['no_found_rows']) {
$found_rows = 'SQL_CALC_FOUND_ROWS';
}
$sql = "SELECT {$found_rows} {$fields} FROM {$table} {$join} {$where} {$groupby} {$order} {$pagination}";
$paged_share_sql = apply_filters('xt_share_get_paged_share_sql', $sql, $sql);
$paged_share = $wpdb->get_results($paged_share_sql);
$total_share = -1;
if (!$args['no_found_rows']) {
$total_share = $wpdb->get_var('SELECT FOUND_ROWS()');
$max_num_pages = ceil($total_share / $args['share_per_page']);
}
unset($sql);
return array('share' => $paged_share, 'total' => $total_share);
}
