zhuishuyun_quickapp/app/Modules/Book/Models/BookConfig.php

<?php

namespace App\Modules\Book\Models;

use App\Modules\Book\Services\BookRoleService;
use App\Modules\Book\Services\BookTagsService;
use App\Modules\User\Services\ReadRecordService;
use App\Modules\Book\Services\BookConfigService;
use App\Modules\Channel\Services\ChannelService;
use App\Modules\OfficialAccount\Services\ForceSubscribeService;
use DB;
use Hashids;
use Illuminate\Database\Eloquent\Model;
use App\Modules\User\Models\User;
use Log;
use Redis;

class BookConfig extends Model
{
    protected $table = 'book_configs';
    protected $fillable = [
        'bid', 'force_subscribe_chapter_seq', 'price', 'cover', 'book_name',
        'copyright', 'charge_type', 'hot', 'is_on_shelf', 'source_domain', 'recommend_index', 'roles', 'test_status', 'plan_push_user_num', 'test_update_time',
        'is_show_index_content', 'click_count', 'promotion_domain', 'copyright_limit_data', 'recommend_cid', 'is_high_quality', 'vip_seq', 'editor_recommend', 'is_current_week_promotion'
    ];

    /**
     * 根据条件获取图书
     * @param array $where
     * @param array $order
     * @param int $page_size
     * @return mixed
     */
    public static function getBooks(array $where = [], array $order = [], $page_size = 15)
    {

        if (!$order) {
            $order = ['recommend_index', 'desc'];
        }
        $res = self::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->select(
                'book_configs.bid',
                'book_configs.roles',
                'book_configs.force_subscribe_chapter_seq',
                'book_configs.cp_source',
                'book_configs.vip_seq',
                'book_configs.price',
                'book_configs.cover',
                'book_configs.book_name',
                'book_configs.copyright',
                'book_configs.charge_type',
                'book_configs.is_on_shelf',
                'books.author',
                'books.intro',
                'book_categories.category_name',
                'category_id',
                'status',
                'chapter_count',
                'book_configs.click_count',
                'first_cid',
                'last_cid',
                'books.size',
                'last_chapter',
                'books.keyword',
                'book_configs.recommend_index',
                'book_configs.is_show_index_content',
                'book_configs.product_id',
                'book_categories.channel_name',
                'books.last_cid',
                'books.last_chapter',
                'book_configs.product_id',
                'book_configs.copyright_limit_data',
                'books.updated_at as last_update_time',
                'book_configs.promotion_domain',
                'books.name as old_name',
                'book_configs.recommend_cid',
                'book_configs.is_high_quality',
                'is_current_week_promotion'
            );
        if ($where) {
            foreach ($where as $key => $v) {
                //关键词查询
                if ($key == 'key' && $v) {
                    $res = $res->where(function ($query) use ($v) {
                        $query->where('book_configs.book_name', 'like', '%' . $v . '%');
                        if (mb_strlen($v) <= 5) {
                            $roles_bids = BookRoleService::getBidsByRole($v);
                            if (count($roles_bids) > 0) {
                                $query->orWhereIn('book_configs.bid', $roles_bids);
                            }
                        }
                    });
                }
                //分类id查询
                if ($key == 'category_id' && $v) {
                    $res = $res->where('books.category_id', '=', $v);
                }
                //上架查询
                if ($key == 'is_on_shelf' && $v != '') {
                    if (is_array($v)) {
                        $res = $res->whereIn('is_on_shelf', $v);
                    } else {
                        $res = $res->where('is_on_shelf', '=', $v);
                    }
                }
                //频道查询
                if ($key == 'channel_name' && $v) {
                    $res = $res->where('book_categories.channel_name', '=', $v);
                }
                if ($key == 'status') {
                    $res = $res->where('books.status', '=', $v);
                }
                if ($key == 'author') {
                    $res = $res->where('books.author', 'like', '%' . $v . '%');
                }
                if ($key == 'roles') {
                    $res = $res->where('book_configs.roles', 'like', '%' . $v . '%');
                }
                //旧书名查询
                if ($key == 'old_name') {
                    $res = $res->where('books.name', 'like', '%' . $v . '%');
                }
                //版权日期查询
                if ($key == 'copy_right_date') {
                    if (is_array($v)) {
                        $res = $res->whereBetween('book_configs.copyright_limit_data', $v);
                    } else {
                        $res = $res->where('book_configs.copyright_limit_data', '<=', $v);
                    }
                }

                if ($key == 'domain') {
                    $res = $res->where('book_configs.promotion_domain', 'like', '%' . $v . '%');
                }
                if ($key == 'bid') {
                    $res = $res->where('book_configs.bid', '=', $v);
                }

                if ($key == 'is_high_quality') {
                    $res = $res->where('book_configs.is_high_quality', '=', $v);
                }

                if ($key == 'charge_type') {
                    $res = $res->where('book_configs.charge_type', '=', $v);
                }

                if ($key == 'firstChapterContent') {
                    $res = $res->join('chapters', function ($query) use ($v) {
                        $query->on('book_configs.bid', '=', 'chapters.bid')
                            ->where('chapters.sequence', 1);
                    })->where('chapters.content', 'like', '%' . $v . '%');
                }

                if ($key == 'tags') {
                    $tags_filter = BookTagsService::getSearchBooks($v);
                    $res->whereIn('book_configs.bid', $tags_filter);
                }
                if ($key == 'is_current_week_promotion') {

                    $res->where('book_configs.is_current_week_promotion', $v);
                }
            }
        }
        if(isset($where['channel_id']) && $where['channel_id'] == 7477){
            $res->whereNotIn('book_configs.cp_source',getHiddenCp());
        }else{
            $res->whereNotIn('book_configs.cp_source',array_merge(getHiddenCp(),['lianshang']));
        }

        return $res->orderBy($order[0], $order[1])->orderBy('book_configs.updated_at', 'desc')->paginate($page_size);
    }


    /**
     * 根据id数组获取图书信息
     * @param array $bid_arr
     * @param array $order
     * @return mixed
     */
    public static function getBooksByIds(array $bid_arr, array $order = [], $is_external_shelf = true)
    {
        $res = self::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->select(
                'book_configs.bid',
                'book_configs.force_subscribe_chapter_seq',
                'book_configs.vip_seq',
                'book_configs.price',
                'book_configs.is_on_shelf',
                'book_configs.cover',
                'book_configs.book_name',
                'book_configs.copyright',
                'book_configs.charge_type',
                'book_configs.is_on_shelf',
                'books.author',
                'books.intro',
                'book_categories.category_name',
                'category_id',
                'status',
                'chapter_count',
                'book_configs.click_count',
                'first_cid',
                'last_cid',
                'size',
                'last_chapter',
                'books.keyword',
                'book_configs.recommend_index',
                'book_configs.is_show_index_content',
                'book_configs.product_id',
                'book_categories.channel_name',
                'books.last_cid',
                'books.last_chapter',
                'book_configs.product_id',
                'books.updated_at as last_update_time',
                'book_configs.copyright_limit_data',
                'book_configs.promotion_domain',
                'books.name as old_name',
                'book_configs.recommend_cid'
            )
            ->whereIn('book_configs.bid', $bid_arr);

        if($is_external_shelf) $res->where('is_on_shelf',2);// 默认外部上架

        if ($order) {
            $res->orderBy($order[0], $order[1]);
        } else {
            $str = implode(',', $bid_arr);
            $field = 'bid,' . $str;
            $res->orderBy(DB::raw('field(' . $field . ')'));
        }
        $res->whereNotIn('book_configs.cp_source',getHiddenCp());

        return $res->limit(30)->get();
    }

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

        $res = self::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->select(
                'book_configs.bid',
                'book_configs.force_subscribe_chapter_seq',
                'book_configs.vip_seq',
                'book_configs.price',
                'book_configs.is_on_shelf',
                'book_configs.cover',
                'book_configs.book_name',
                'book_configs.copyright',
                'book_configs.charge_type',
                'book_configs.is_on_shelf',
                'books.author',
                'books.intro',
                'book_categories.category_name',
                'category_id',
                'status',
                'chapter_count',
                'book_configs.click_count',
                'first_cid',
                'last_cid',
                'size',
                'last_chapter',
                'books.keyword',
                'book_configs.recommend_index',
                'book_configs.is_show_index_content',
                'book_configs.product_id',
                'book_categories.channel_name',
                'books.last_cid',
                'books.last_chapter',
                'book_configs.product_id',
                'books.updated_at as last_update_time',
                'book_configs.copyright_limit_data',
                'book_configs.promotion_domain',
                'books.name as old_name',
                'book_configs.recommend_cid'
            );
        if(isset($where['bids']) && !empty($where['bids'])){
            $res->whereIn('book_configs.bid', $where['bids']);
        }

        if($is_external_shelf) $res->where('is_on_shelf',2);// 默认外部上架

        if ($order) {
            $res->orderBy($order[0], $order[1]);
        } else {
            $str = implode(',', $where['bids']);
            $field = 'bid,' . $str;
            $res->orderBy(DB::raw('field(' . $field . ')'));
        }
        if(isset($where['channel_id']) && $where['channel_id'] == 7477){
            $res->whereNotIn('book_configs.cp_source',getHiddenCp());
        }else{
            $res->whereNotIn('book_configs.cp_source',array_merge(getHiddenCp(),['lianshang']));
        }


        return $res->limit(30)->get();
    }


    /**
     * 根据bid获取图书信息
     * @param $bid
     * @return mixed
     */
    public static function getBookById($bid)
    {
        if (empty($bid)) return null;
        return self::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->select(
                'book_configs.bid',
                'book_configs.is_on_shelf',
                'book_configs.force_subscribe_chapter_seq',
                'book_configs.vip_seq',
                'book_configs.cp_source',
                'book_configs.price',
                'book_configs.cover',
                'book_configs.book_name',
                'book_configs.copyright',
                'book_configs.created_at',
                'book_configs.charge_type',
                'book_configs.is_on_shelf',
                'books.author',
                'books.intro',
                'book_categories.category_name',
                'category_id',
                'status',
                'chapter_count',
                'first_cid',
                'last_cid',
                'size',
                'last_chapter',
                'books.keyword',
                'book_configs.recommend_index',
                'book_configs.test_status',
                'book_configs.is_show_index_content',
                'book_configs.click_count',
                'book_configs.product_id',
                'book_categories.channel_name',
                'books.last_cid',
                'books.updated_at as last_update_time',
                'books.last_chapter',
                'book_configs.product_id',
                'book_configs.copyright_limit_data',
                'book_configs.promotion_domain',
                'books.name as old_name',
                'book_configs.recommend_cid',
                'book_configs.is_high_quality',
		        'book_configs.unit_price',
		        'book_configs.calculate_price_type'
            )->where('book_configs.bid', $bid)->first();
    }


    /*
     * 获取渠道，用户的全部书籍列表
    */
    public static function getLeftRecommendBook($channel_name, $is_high_quality, $force_update = false): array
    {
        if ($force_update) {
            \Log::info('force_set_full_book_channel_name:' . $channel_name);
            Redis::set('full_book_channel_name:' . $channel_name, null);
        }

        // 存redis里面
        $full_book_bids = Redis::get('full_book_channel_name:' . $channel_name);
        $full_book_bids = json_decode($full_book_bids);
        if (!empty($full_book_bids)) {
            \Log::info('direct_get_full_book_bids_from_redis:' . $channel_name);
        } else {
            // 获取全集
            $full_book_bids = self::join('books', 'book_configs.bid', '=', 'books.id')
                ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
                ->select('book_configs.bid')
                ->where('book_categories.channel_name', $channel_name)
                ->where('book_configs.is_high_quality', $is_high_quality)
                ->where('book_configs.test_status', 0) // 不含测书
                ->whereIn('book_configs.is_on_shelf', [2])
                ->orderBy('book_configs.id', 'desc')
                ->get()->pluck('bid')->all();
            if (!empty($full_book_bids)) {
                \Log::info('set_full_book_channel_name:' . $channel_name . ' data:' . json_encode($full_book_bids));
                Redis::set('full_book_channel_name:' . $channel_name, json_encode($full_book_bids));
            }
        }

        //     	\Log::info('$full_book_bids:'.json_encode($full_book_bids));
        return $full_book_bids;
    }




    /*
     * 获取用户的曾经推荐过的书籍列表
    */
    public static function getUserRecommendRecords($uid)
    {
        $recommend_bids = Redis::smembers('userRecommendBids:' . $uid);
        return $recommend_bids;
    }

    /*
     * 添加用户的曾经推荐过的书籍列表
    */
    public static function addUserRecommendRecords($uid, $bids)
    {
        foreach ($bids as $bid) {
            Redis::sadd('userRecommendBids:' . $uid, $bid);
        }
    }

    /*
     * 清楚用户的曾经推荐过的书籍列表
    */
    public static function truncateUserRecommendRecords($uid)
    {
        \Log::info('truncateUserRecommendRecords:' . $uid);
        Redis::del('userRecommendBids:' . $uid);
    }


    /*
    *快应用专用，用户阅读完推荐
    * 获取相同推荐
    */
    public static function getQuickAppRecommendBooks($bid, $categories_id, $num = 4)
    {
        $res = self::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->select(
                'book_configs.bid',
                'book_configs.force_subscribe_chapter_seq',
                'book_configs.vip_seq',
                'book_configs.price',
                'book_configs.cover',
                'book_configs.book_name',
                'book_configs.copyright',
                'book_configs.charge_type',
                'book_configs.is_on_shelf',
                'books.author',
                'books.intro',
                'book_categories.category_name',
                'category_id',
                'status',
                'chapter_count',
                'first_cid',
                'last_cid',
                'size',
                'last_chapter',
                'books.keyword',
                'book_configs.recommend_index',
                'book_configs.is_show_index_content',
                'book_configs.click_count',
                'book_configs.product_id',
                'book_categories.channel_name',
                'books.last_cid',
                'books.last_chapter',
                'book_configs.product_id',
                'books.name as old_name',
                'book_configs.recommend_cid',
                'book_configs.is_high_quality',
                'books.updated_at as last_update_time'
            )
            ->where('book_categories.id', $categories_id)
            ->where('book_configs.bid', '!=', $bid)
            ->where('book_configs.is_high_quality', 1)
            ->where('book_configs.is_on_shelf', 2)
            ->whereNotIn('book_configs.cp_source',getHiddenCp())
            ->orderBy('recommend_index', 'desc')->get();
        $count = $res->count() >= $num ? $num : $res->count();
        return $res->random($count);
    }


    /*
    * H5专用，用户阅读完推荐
    * 获取相同推荐
    */
    public static function getRecommendBooks($bid, $channel_name, $num = 4)
    {
        $res = self::join('books', 'book_configs.bid', '=', 'books.id')
            ->leftjoin('book_categories', 'books.category_id', 'book_categories.id')
            ->select(
                'book_configs.bid',
                'book_configs.force_subscribe_chapter_seq',
                'book_configs.vip_seq',
                'book_configs.price',
                'book_configs.cover',
                'book_configs.book_name',
                'book_configs.copyright',
                'book_configs.charge_type',
                'book_configs.is_on_shelf',
                'books.author',
                'books.intro',
                'book_categories.category_name',
                'category_id',
                'status',
                'chapter_count',
                'first_cid',
                'last_cid',
                'size',
                'last_chapter',
                'books.keyword',
                'book_configs.recommend_index',
                'book_configs.is_show_index_content',
                'book_configs.click_count',
                'book_configs.product_id',
                'book_categories.channel_name',
                'books.last_cid',
                'books.last_chapter',
                'book_configs.product_id',
                'books.name as old_name',
                'book_configs.recommend_cid',
                'book_configs.is_high_quality',
                'books.updated_at as last_update_time'
            )
            ->where('book_categories.channel_name', $channel_name)
            ->where('book_configs.bid', '!=', $bid)
            ->where('book_configs.is_high_quality', 1)
            ->where('book_configs.is_on_shelf', 2)
            ->whereNotIn('book_configs.cp_source',getHiddenCp())
            ->orderBy('recommend_index', 'desc')->get();
        $count = $res->count() >= $num ? $num : $res->count();
        return $res->random($count);
    }




    public static function getAllCps()
    {
        $cps = self::select('cp_source')->groupBy('cp_source')->get();
        $result = array();
        foreach ($cps as $cp) {
            if (!empty($cp['cp_source'])) {
                $result[] = $cp['cp_source'];
            }
        }
        return $result;
    }

    public static function getAllCpBooks()
    {
        $result = array();
        $cp_books = self::select('cp_source', 'bid')->groupBy('cp_source')->groupBy('bid')->get();
        foreach ($cp_books as $cp_book) {
            $result[$cp_book['cp_source']][] = $cp_book['bid'];
        }
        return $result;
    }

    /*
     * 得到cp某段时间某本书的消费
    */
    public static function getAllCpBookConsume($start_date, $end_date, $cps)
    {
        $result = self::leftjoin('book_order_statistical', 'book_order_statistical.bid', '=', 'book_configs.bid')
            ->select(
                'book_order_statistical.bid',
                DB::raw('sum(book_order_statistical.charge_balance) as charge_balance'),
                'book_configs.book_name',
                'book_configs.is_on_shelf',
                'book_configs.cp_source'
            )
            ->whereIn('book_configs.cp_source', $cps)
            ->where('book_order_statistical.day', '>=', $start_date)
            ->where('book_order_statistical.day', '<=', $end_date)
            ->groupBy('book_configs.cp_source')
            ->groupBy('book_order_statistical.bid')
            ->get();
        return $result;
    }



}