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 query($query_vars) { global $wpdb, $xt; $this->init(); $defaults = array('cid' => '', 'page' => 1, 'album_per_page' => xt_albumperpage(), 'user_id' => 0, 'no_found_rows' => 0, 's' => '', 'isFavorite' => 0, 'isShare' => 0, 'sortOrder' => '', 'album__in' => array()); $this->query_vars = wp_parse_args($query_vars, $defaults); do_action_ref_array('xt_pre_get_albums', array(&$this)); extract($this->query_vars, EXTR_SKIP); $page = absint($page); $album_per_page = absint($album_per_page); $table = $isFavorite ? XT_TABLE_FAVORITE : XT_TABLE_ALBUM; $fields = '*'; $join = ''; $where = ''; $order = "ORDER BY " . XT_TABLE_ALBUM . ".update_date_gmt DESC"; $groupby = ''; 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_ALBUM_CATALOG . " ON " . XT_TABLE_ALBUM_CATALOG . ".id = " . XT_TABLE_ALBUM . ".id "; $where .= " AND " . XT_TABLE_ALBUM_CATALOG . ".cid in(" . $wpdb->escape($xt_catalog->children) . "," . $cid . ") "; $groupby .= "GROUP BY " . XT_TABLE_ALBUM . ".id"; } else { $join .= " INNER JOIN " . XT_TABLE_ALBUM_CATALOG . " ON " . XT_TABLE_ALBUM_CATALOG . ".id = " . XT_TABLE_ALBUM . ".id "; $where .= $wpdb->prepare(" AND " . XT_TABLE_ALBUM_CATALOG . ".cid=%d ", $cid); } } } elseif ($cid == -1) { $join = ''; $where = ' AND ' . XT_TABLE_ALBUM . '.id NOT IN (SELECT ID FROM ' . XT_TABLE_ALBUM_CATALOG . ')'; } if ($isFavorite) { $join .= " INNER JOIN " . XT_TABLE_ALBUM . " ON " . XT_TABLE_FAVORITE . ".id=" . XT_TABLE_ALBUM . ".id "; } if ($user_id > 0) { if ($isFavorite) { $where .= $wpdb->prepare(" AND " . XT_TABLE_FAVORITE . ".user_id = %d AND " . XT_TABLE_FAVORITE . ".type=2", $user_id); } else { $where .= $wpdb->prepare(" AND " . XT_TABLE_ALBUM . ".user_id = %d ", $user_id); } } elseif (!empty($s)) { $where .= " AND (" . XT_TABLE_ALBUM . ".title like '%" . $wpdb->escape($s) . "%' OR " . XT_TABLE_ALBUM . ".user_name like '%" . $wpdb->escape($s) . "%') "; } if (!empty($album__in)) { $album__in = implode(',', array_map('absint', $album__in)); $where .= " AND " . XT_TABLE_ALBUM . ".id IN ({$album__in})"; } $today_time = xt_get_todaytime(); switch ($sortOrder) { case 'newest': default: $order = " ORDER BY " . XT_TABLE_ALBUM . ".update_date DESC"; break; case 'popular': $day7_time = $today_time - 604800; //7 days $fields .= ",(" . XT_TABLE_ALBUM . ".create_date > {$day7_time}) AS time_sort "; $order = " ORDER BY time_sort DESC," . XT_TABLE_ALBUM . ".fav_count DESC"; break; case 'hot': $day30_time = $today_time - 2592000; //30 days $fields .= ",(" . XT_TABLE_ALBUM . ".create_date > {$day30_time}) AS time_sort "; $order = " ORDER BY time_sort DESC," . XT_TABLE_ALBUM . ".fav_count DESC"; break; } if (!$no_found_rows && $page && $album_per_page) { $limits = $wpdb->prepare("LIMIT %d, %d", ($page - 1) * $album_per_page, $album_per_page); } else { $limits = ''; } if ($isFavorite) { $order = " ORDER BY " . XT_TABLE_FAVORITE . ".create_date DESC"; } $found_rows = ''; if (!$no_found_rows) { $found_rows = 'SQL_CALC_FOUND_ROWS'; } $sql = "SELECT {$found_rows} {$fields} FROM {$table} {$join} WHERE 1=1 {$where} {$order} {$limits}"; $paged_albums = $wpdb->get_results($sql); $paged_albums = apply_filters_ref_array('xt_the_albums', array($paged_albums, &$this)); $total_albums = -1; if (!$no_found_rows) { $total_albums = $wpdb->get_var('SELECT FOUND_ROWS()'); } unset($sql); $this->found_albums = $total_albums; $this->albums = $paged_albums; $this->album_count = count($paged_albums); if ($total_albums > 1) { $total_page = ceil($total_albums / $album_per_page); $_base = '#%#%'; if (isset($xt->is_albums) && $xt->is_albums) { $_base = xt_get_albums_search_url(array_merge($this->query_vars, array('page' => '%#%'))); } else { if (isset($_GET['page']) && $_GET['page'] == 'xt_menu_share') { $_base = add_query_arg('paged', '%#%', $_SERVER['HTTP_HOST'] . $_SERVER['REQUEST_URI']); } } $this->paginate_links = paginate_links(array('base' => $_base, 'format' => '', 'end_size' => 3, 'total' => $total_page, 'current' => $page, 'prev_text' => '上一页', 'next_text' => '下一页', 'mid_size' => 1, 'type' => isset($_GET['page']) && $_GET['page'] == 'xt_menu_share' ? 'plain' : 'list')); } return array('albums' => $paged_albums, 'total' => $total_albums); }
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); }