zhuishuyun_quickapp/app/Modules/Book/Services/BookConfigService.php

<?php

/**
 * Created by PhpStorm.
 * User: tandunzhao
 * Date: 2017/12/4
 * Time: 下午1:43
 */

namespace App\Modules\Book\Services;

use App\Modules\Book\Models\BookCombinationConfig;
use App\Modules\Book\Models\BookConfig;
use App\Modules\Book\Models\BookCopyright;
use App\Modules\Product\Services\ProductService;
use App\Modules\Book\Models\Book;
use App\Modules\Book\Models\BookKeyword;
use App\Modules\Book\Models\FreeBook;
use App\Modules\Book\Models\FreeBookConfig;
use App\Modules\Book\Models\QappUserSearchBookLog;
use Illuminate\Support\Facades\DB;
use phpDocumentor\Reflection\Types\This;
use Redis;
use Vinkla\Hashids\Facades\Hashids;

class BookConfigService
{

    /**
     * 根据id获取图书
     * @param $bid
     * @return mixed
     */
    public static function getBookById($bid)
    {
        return BookConfig::getBookById($bid);
    }


    /**
     * 获取图书组合信息
     * @param $bid
     * @return array
     */
    public static function getCombinationInfo($bid)
    {
        $info = BookCombinationConfig::where('bid', $bid)->where('is_enable', 1)->select('bid_group')->first();
        if ($info) return explode(',', $info->bid_group);
        return [];
    }

    /**
     * 根据bid数组获取多本图书
     * @param $where
     * @param null $order
     * @return mixed
     */
    public static function getBooksByIds(array $where, $order = [], $is_external_shelf = true)
    {
        if (empty($where)) {
            return (object)array();
        }
        if ($order)
            $res = BookConfig::getBooksByIds($where, $order, $is_external_shelf);
        else
            $res = BookConfig::getBooksByIds($where, [], $is_external_shelf);
        return $res;
    }

    public static function getBookLists(array $where, $order = [], $is_external_shelf = true)
    {
        if (empty($where)) {
            return (object)array();
        }
        if ($order) {
            $res = BookConfig::getBookLists($where, $order, $is_external_shelf);
        } else {
            $res = BookConfig::getBookLists($where, [], $is_external_shelf);
        }

        return $res;
    }

    /**
     *
     * 根据条件获取图书
     * @param array $where ['key'=>'根据关键词查询','category_id'=>'根据分类id查询','is_on_shelf'=>上下架查询,'channel_name'=>'频道查询(男频女频)']
     * @param array $order 排序 默认是bid排序
     * @param int $page_size
     * @return mixed
     */
    public static function getBooks(array $where, array $order = [], $page_size = 15)
    {
        return BookConfig::getBooks($where, $order, $page_size);
    }


    /**
     * 获取阅读完的推荐
     * @param $category_id
     * @param int $num
     * @return mixed
     */
    public static function getRecommendBooks($bid, $category_id, $num = 4)
    {
        return BookConfig::getRecommendBooks($bid, $category_id, $num);
    }

    /**
     * 获取阅读完的推荐(快应用)
     * @param $category_id
     * @param int $num
     * @return mixed
     */

    public static function getQuickAppRecommendBooks($bid, $category_id, $num = 4)
    {
        return BookConfig::getQuickAppRecommendBooks($bid, $category_id, $num);
    }


    public static function getSimpleBooksByIds(array $ids)
    {
        $str = implode(',', $ids);
        $field = 'bid,' . $str;
        return BookConfig::whereIn('bid', $ids)->select('bid', 'book_name')->orderBy(DB::raw('field(' . $field . ')'))->get();
    }


    public static function findBookKeywords(bool $is_all = false)
    {
        $sql = BookKeyword::where('status', 1)->orderBy('sequence');
        if ($is_all) {
            return $sql->get();
        } else {
            return $sql->paginate(10);
        }
    }

    public static function saveUserSearchLog(string $words, int $uid)
    {
        QappUserSearchBookLog::create([
            'uid' => $uid,
            'words' => $words,
        ]);
    }

    /**
     * @return FreeBookConfig
     */
    public static function findFreeBookConfig(int $sex)
    {
        return FreeBookConfig::where(['sex' => $sex, 'is_enabled' => 1])
            ->where('end_time', '>=', now())
            ->orderBy('end_time')
            ->first();
    }

    /**
     * 查找限免书籍
     * @return array
     */
    public static function findFreeBooks(int $sex)
    {
        $config = self::findFreeBookConfig($sex);
        \Log::info('return_empty_free_books:' . $sex);
        \Log::info($config);
        if ($config) {
            $free_books = FreeBook::where('config_id', $config->id)
                ->where('is_enabled', 1)
                ->get();
            $bids = $free_books->pluck('bid')->all();
            $book_configs = BookConfig::whereIn('bid', $bids)
                ->where('is_on_shelf', 2)
                ->select('bid', 'book_name', 'cover')
                ->get();
            $books = Book::whereIn('id', $bids)->select('id', 'intro')->get();
            \Log::info('return_empty_data:');
            \Log::info($books);
            $book_list = $book_configs->transform(function ($item) use ($books) {
                $book = $books->where('id', $item->bid)->first();
                return [
                    'book_id' => Hashids::encode($item->bid),
                    'cover_url' => $item->cover,
                    'book_name' => $item->book_name,
                    'intro' => $book->intro,
                ];
            })->all();
            return [
                'title' => $config->name,
                'end_time' => $config->end_time,
                'list' => $book_list,
            ];
        }
        return [];
    }

    /**
     * 判断书籍是否限免
     * @return bool
     */
    public static function judgeBookIsFree(int $bid)
    {
        $ids = [];
        foreach ([1, 2] as $sex) {
            $config = self::findFreeBookConfig($sex);
            if ($config) {
                $ids[] = $config->id;
            }
        }
        return FreeBook::where('bid', $bid)
            ->whereIn('config_id', $ids)
            ->where('is_enabled', 1)->select('id')->first();
    }


    public static function getByBidNoFilter($bid)
    {
        return FreeBook::join('free_book_config', 'free_book_config.id', '=', 'free_books.config_id')
            ->where('bid', $bid)
            ->select('free_books.id', 'end_time')
            ->where('end_time', '<', date('Y-m-d H:i:s'))
            ->orderBy('free_book_config.end_time', 'desc')
            ->first();
    }

    public static function chargeStats($id, $amount, $uid)
    {
        if (!Redis::Sismember('qapp:free:virtual:uids' . $id, $uid)) {
            return;
        }
        $now = date('Y-m-d');
        $amount = $amount * 100;
        Redis::hincrby('qapp:book:free:charge:' . $id, $now, $amount);
        #Redis::sadd('qapp:free:charge'.$now,$id);
        Redis::sadd('qapp:free:actuality' . $now, $id);
        Redis::sadd('qapp:free:charge:uids' . $now . $id, $uid);
    }

    public static function getBookByField($bids, $field)
    {
        if (!$bids || !$field) return null;
        return BookConfig::join('books', 'books.id', '=', 'book_configs.bid')->
        whereIn('bid', $bids)->select($field)->get();
    }

    /**
     * 根据书籍bid去除无用bid
     * name: getAvailableBIdsbyBids
     * @param $bids
     * @param mixed $channel_id
     * @param mixed $is_external_shelf
     * @return array
     * date 2022/09/20 10:39
     */
    public static function getAvailableBIdsbyBids($bids, $channel_id = 0, $is_external_shelf = true)
    {
        if (empty($bids)) {
            return [];
        }

        $res = BookConfig::whereIn('bid',$bids);
//        if($channel_id == config('qapp_public_package_channel')){
//            $res->whereNotIn('cp_source',getHiddenCp());
//        }else{
//            $res->whereNotIn('cp_source',array_merge(getHiddenCp(),['lianshang']));
//        }
        $res->whereNotIn('cp_source',getHiddenCp($channel_id,2));
        if ($is_external_shelf){
            $res->where('is_on_shelf',2);
        }else{
            $res->whereIn('is_on_shelf',[1,2]);

        }

        return $res->pluck("bid")->toArray();
    }

    /***
     *  推荐位书籍检测和补齐
     * name: getCheckBooks
     * @param $bid_list
     * @param $channel
     * @param $package
     * @param $is_author
     * @return mixed
     * date 2022/10/26 10:11
     */
    public static function getCheckBooks($bid_list, $channel, $package, $is_author)
    {

        $hidden_cp = getHiddenCp($package);
//        if(!is_public_package($package)){
//            $hidden_cp = array_merge($hidden_cp,['lianshang']);
//        }
        //获取书本数量
        $count = count($bid_list);
        if (!$is_author) {
            $where = [
                ['book_configs.charge_type', '!=', 'BOOK'],
                ['book_configs.cp_source', '=', 'ycsd'],
            ];
        } else {
            $where = [
                ['book_configs.charge_type', '!=', 'BOOK'],
            ];
        }

        //获取书籍交集bid，过滤掉不符合要求的书
        $bid_list = BookConfig::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->whereIn('book_configs.bid', $bid_list)
            ->where('book_configs.is_on_shelf', 2)
            ->where($where)
            ->whereNotIn('book_configs.cp_source', $hidden_cp)
            ->where('book_categories.pid', $channel)
            ->pluck('book_configs.bid')->all();

        $book_count = count($bid_list);
        if ($book_count === $count) {
            return $bid_list;
        }
        $supplement_count = $count - $book_count;
        //获取随机的有效的书籍bid
        $rand_bid = BookConfig::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->where('book_configs.is_on_shelf', 2)
            ->where($where)
            ->whereNotIn('book_configs.cp_source', $hidden_cp)
            // ->where('book_configs.is_high_quality',1)
            ->where('book_categories.pid', $channel)
            ->orderBy('book_configs.is_high_quality', 'desc')
            // ->inRandomOrder()
            ->orderBy('book_configs.recommend_index', 'desc')
            ->limit($supplement_count)
            ->pluck('book_configs.bid')->all();
        return array_filter(array_merge($bid_list, $rand_bid));
    }

    public static function getRecommendBids($package = '', $channel = 1, $no_in_bid = [], $limit = 36,$is_on_shelf = true)
    {
        DB::enableQueryLog();
        $where = [
            ['book_configs.charge_type', '!=', 'BOOK'],
        ];
        //获取书籍交集bid，过滤掉不符合要求的书
        $bid_list = BookConfig::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->distinct("books.name")
            ->where($where)
            ->whereNotIn('book_configs.cp_source', getHiddenCp($package))
            ->where('book_categories.pid', $channel);
        if ($is_on_shelf){
            $bid_list->where('book_configs.is_on_shelf', 2);
        }else{
            $bid_list->whereIn('book_configs.is_on_shelf', [1,2]);
        }
        if (!empty($no_in_bid)) {
             $bid_list->whereNotIn('book_configs.bid', $no_in_bid);
        }
        return $bid_list->orderBy('recommend_index' ,  'desc')->orderBy("book_configs.is_high_quality",'desc')->orderBy('books.id' ,  'asc')->limit($limit)->pluck('book_configs.bid')->toArray();
    }

    /***
     * 书籍授权情况 2黑名单 1白名单 其他 无特殊添加
     * name: bookCopyright
     * @param mixed $bid
     * @param mixed $distribution_channel_id
     * date 2022/11/14 17:16
     */
    public static function bookCopyright($bid, $distribution_channel_id = 0)
    {
        if ($distribution_channel_id < 1){
            return 0;
        }
        return BookCopyright::where('distribution_channel_id',$distribution_channel_id)
                ->where('bid',$bid)
                ->where('is_enabled',1)
                ->where('platform',"qapp")
                ->value('type');

    }

    public static function getNewRecommendBids($package, $channel, $bids = [], $limit=12,$is_on_shelf = true)
    {
        $begin_time = date('Y-m-d',strtotime('-2 month'));
        $end_time =  date('Y-m-d');
        $where = "  and  book_configs.charge_type = 'CHAPTER' and book_categories.pid= {$channel}";
        $hide = getHiddenCp($package);

        if ($hide){
            $where .= " and book_configs.cp_source not in ( '" . implode("','",$hide) . "')";
        }
        if ($is_on_shelf){
            $where .= " and  book_configs.is_on_shelf = 2";
        }else{
            $where .= " and  book_configs.is_on_shelf in (1,2)";
        }

        if (!empty($bids)){
            $where .= " and  books.id not in (" . implode(",",$bids) .  ")";
        }

        $sql = "select book_configs.bid from book_promotion_day_stats
                    INNER JOIN books on books.id=book_promotion_day_stats.bid
                   INNER JOIN book_configs on book_configs.bid = books.id
                    INNER JOIN book_categories ON book_categories.id=books.category_id
                     where date >='{$begin_time}' and date <= '{$end_time}' {$where}  group by bid order by sum(rechareg_amount) desc limit {$limit}";
        // $bids = DB::select($sql);
        // echo "<pre><hr>";
        // var_export($bids);
        // echo  "<hr>";
        // var_export($sql);
        // die();

    }

    /***
     *  获取排行榜数据
     * name: getRankList
     * @param $package
     * @param mixed $sex 1:男频 2:女频
     * @param int $limit 条数默认30
     * date 2022/11/15 14:36
     */
    public static function getRankList($package, $sex = 1, $limit = 30)
    {

        if ($sex == 1){
            return [14928,14500,61020,14927,14016,14021,19409,14423,61596,62297,16451,15103,14022,14793,14351,17652,63721,63417,62781,63482,13300,14297,14072,13928,13879,16438,14312,16712,13929,14408,19993,14299,59580,60623,62666,19675,65187,15978,59784,58687,61944,13840,13286,16236,64284,58969,14811,14006,13254,13275,14000,9744,13266,15251,62875,6644,16434,13291,16431,59687,13994,16256,14272,19958,16435,13271,61564,13878,14302,61945,3483,13281,16453,59723,16443,13992,16455,9753,11408,62461,59389,13260,13257,17954,13280,11457,13284,13282,7680,16448,64225,21910,2939,22376,16442,13287,16440,16427,60827,21700,13361,59688,2079,20416,7956,16545,10418,13930,13867,64325,14300,16445,17242,14308,13288,19449,10537,12393,13250,13888,16454,14926,16484,13278,21139,61941,10417,21757,64239,13265,13272,13253,60175,8461,21376,2941,22056,13269,14806,14097,14303,59388,22148,16447,2053,13258,16433,16452,16476,2936,59677,20027,22157,16566,16441,17689,16251,13285,63989,2928,14925,2942,13931,13264,2945,20236,21999,46203,5618,16842,64340,59385,14295,64346,13884,61684,12480,59728,16798,94,20711,20072,20368,8253,2915,16822,20046,2993,12103,17268,12494,22153,32802,3542,61567,20837,16436,62355,64329,16542];
        }else{
            return  [11316,11672,11679,11680,11681,11999,12331,12462,12464,12468,12949,13421,14220,14349,14620,15221,15537,15720,16301,17055,19382,58195,58593,59004,59579,61012,61425,61699,62175,62538,62865,64729,1389,2061,4938,6043,8141,8295,8476,9893,10682,10721,11063,11632,11648,11653,11658,11660,11661,11662,11928,12224,12281,12283,12470,12471,12689,12701,12739,13023,13038,13081,13088,13095,13113,13125,13154,13155,13157,13169,13173,13175,13176,13177,13183,13189,13190,13192,13193,13201,13207,13397,13439,13520,13593,13608,13853,14083,14318,14417,14684,14697,14709,14744,14892,14924,15255,15281,15368,15431,15779,15997,16005,16035,16112,16392,16395,16731,16951,17379,18817,19217,19249,19411,19430,58223,58236,58281,58344,58469,58481,58483,58731,58763,58776,58790,58869,58946,58950,59075,59214,59242,59296,59378,59512,60135,60610,60902,61079,61554,62202,62878,63333,63884,64027,1,11663,11664,11665,13182,3588,3697,6063,9525,10823,10961,11670,12474,13082,13092,13093,13104,13160,13166,13324,13374,13761,13842,14421,14561,14664,14890,15145,15282,15806,16053,16411,19381,24366,58216,58396,58864,58906,59549,60636,60672,60736,62473,62537,62864,62935,63413,64808,11671,12367,16289,19220,61958,61979,59546,11471];
        }


        $begin_time = date('Y-m-d H:i:s',strtotime('-3 month'));
        $end_time =  date('Y-m-d H:i:s');
        $where = "  and orders.from_bid >  0  and  book_configs.charge_type = 'CHAPTER' and book_categories.pid= {$sex} and  book_configs.is_on_shelf = 2";
        $hide = getHiddenCp($package);
        if ($hide){
            $where .= " and book_configs.cp_source not in ( '" . implode("','",$hide) . "')";
        }

        $sql = " select orders.from_bid from orders INNER JOIN books  ON orders.from_bid = books.id INNER JOIN book_configs  ON book_configs.bid = books.id INNER JOIN book_categories  ON book_categories.id=books.category_id WHERE orders.created_at >= '{$begin_time}' and  orders.created_at <= '{$end_time}' and orders.status = 'PAID'  {$where} group by  orders.from_bid order by sum(orders.price) desc ";
        return  DB::select($sql);

    }
}