exhibition_service_api/app/Web/Models/BlogModel.php

<?php


namespace App\Web\Models;



use App\Models\BaseModel;
use Illuminate\Support\Facades\DB;


class BlogModel extends BaseModel
{



    /**
     * 状态字段
     */
    const DELETED_AT = 'status';
    /**
     * @var string
     */
    protected $table = 'blog';

    /**
     * 获取文章列表
     * */
    public function getBlogList($params,$fields='a.*'){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $where=[];
        if (isset($params['is_admin'])) {
            $fields='a.id,a.plate_id,a.title,a.pub_date,a.image_url,a.virtual_view,a.description,a.status,a.update_time,a.create_time,a.total_view,a.is_top,a.sort,a.seo_id,c.seo_title,c.seo_keyword,c.seo_describe,c.urla,c.trans_status,a.is_top,a.is_recommend';
        }
        if(isset($params['status'])){
            $where[]=['a.status','=',$params['status']];
        }else{
            $where[]=['a.status','<',2];
        }
        if(!empty($params['plate_id'])){
            $where[]=['a.plate_id','=',$params['plate_id']];
        }
        if(!empty($params['lt_total_view'])){
            $where[]=['a.total_view','<',$params['lt_total_view']];
        }
        $query= $this->newInstance()->alias('a')->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')->where($where);
        if (!empty($params['keyword'])) {
            $keyword = $params['keyword'];
            $query->where('a.title', 'like', "%" . $keyword . "%");
        }

        if(!empty($params['sort'])){
            if($params['sort']=='sort_ascending'){
                $query->orderBy('sort','asc');
            }else if($params['sort']=='sort_descending'){
                $query->orderBy('sort','desc');
            }else if($params['sort']=='pub_date_ascending'){
                $query->orderBy('pub_date','asc');
            }else if($params['sort']=='pub_date_descending'){
                $query->orderBy('pub_date','desc');
            }else if($params['sort']=='create_time_ascending'){
                $query->orderBy('create_time','asc');
            }else if($params['sort']=='create_time_descending'){
                $query->orderBy('create_time','desc');
            }else if($params['sort']=='update_time_ascending'){
                $query->orderBy('update_time','asc');
            }else if($params['sort']=='update_time_descending'){
                $query->orderBy('update_time','desc');
            }
        }else{
            $query->orderBy('is_top','desc')
                ->orderBy('sort');
        }
        $totalCount = $query->count();
        $list= $query->skip($skip)
            ->limit($pageSize)
            ->selectRaw($fields)
            ->get();
        if(!empty($list)){
            $list=$list->toArray();
        }else{
            $list=[];
        }
        $result = $this->buildPaginator($list, $skip, $page, $pageSize, $totalCount);
        return $result;
    }

    /**
     * 获取文章分类未关联的文章
     * */
    public function getUnrelatedBlogByTypeIds($params){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $typeId = $params['type_id'] ?? '';
        $keyword = $params['keyword'] ?? '';
        $where=[];
        if ($keyword) {
            $keyword = addslashes($keyword);
            $where['_string'] = "((a.`title` like '%{$keyword}%' or a.`description` like '%{$keyword}%'))";
        }
        $totalCount= $this->newInstance()->buildQuery($where) ->from($this->getTable().' as a')
            ->leftJoin('blog_type_relation as b', function ($join)use($typeId) {
                $join->on('a.id', '=', 'b.blog_id')
                    ->where('b.status', '=', 0)
                    ->where('b.type_id', '=', $typeId);
            })->where('a.status','<',2)->whereNull('b.id')->count();
        $fields='a.id,a.title,a.pub_date,a.image_url,a.description,a.status,a.update_time,a.create_time';
        $list= $this->newInstance()->buildQuery($where) ->from($this->getTable().' as a')
            ->leftJoin('blog_type_relation as b', function ($join)use($typeId) {
                $join->on('a.id', '=', 'b.blog_id')
                    ->where('b.status', '=', 0)
                    ->where('b.type_id', '=', $typeId);
            })->where('a.status','<',2)->whereNull('b.id')->skip($skip)
            ->limit($pageSize)
            ->selectRaw($fields)
            ->orderBy('sort')
            ->get();
        if(!empty($list)){
            $list=$list->toArray();
        }else{
            $list=[];
        }
        $result = $this->buildPaginator($list, $skip, $page, $pageSize, $totalCount);
        return $result;
    }


    /**
     * 获取文章标签未关联的文章
     * */
    public function getUnrelatedBlogByTagIds($params){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $tagId = $params['tag_id'] ?? '';
        $keyword = $params['keyword'] ?? '';
        $where=[];
        if ($keyword) {
            $keyword = addslashes($keyword);
            $where['_string'] = "((a.`title` like '%{$keyword}%' or a.`description` like '%{$keyword}%'))";
        }
        $totalCount= $this->newInstance()->buildQuery($where) ->from($this->getTable().' as a')
            ->leftJoin('blog_tag_relation as b', function ($join)use($tagId) {
                $join->on('a.id', '=', 'b.blog_id')
                    ->where('b.status', '=', 0)
                    ->where('b.tag_id', '=', $tagId);
            })->where('a.status','<',2)->whereNull('b.id')->count();
        $fields='a.id,a.title,a.pub_date,a.image_url,a.description,a.status,a.update_time,a.create_time';
        $list= $this->newInstance()->buildQuery($where) ->from($this->getTable().' as a')
            ->leftJoin('blog_tag_relation as b', function ($join)use($tagId) {
                $join->on('a.id', '=', 'b.blog_id')
                    ->where('b.status', '=', 0)
                    ->where('b.tag_id', '=', $tagId);
            })->where('a.status','<',2)->whereNull('b.id')->skip($skip)
            ->limit($pageSize)
            ->selectRaw($fields)
            ->orderBy('sort')
            ->get();
        if(!empty($list)){
            $list=$list->toArray();
        }else{
            $list=[];
        }
        $result = $this->buildPaginator($list, $skip, $page, $pageSize, $totalCount);
        return $result;
    }



    /**
     * 文章保存
     * @param array $data
     * */
    public function saveBlogData($data){
        if (!empty($data['id'])) {
            $id = $data['id'];
            $this->newInstance()->where('id', $data['id'])->update($data);
        } else {
            $id = $this->newInstance()->insertGetId($data);
        }
        return $id;
    }

    /**
     * 检查 标签是否唯一
     * */
    public function checkNameUnique($name,$id=0){
        $where=[];
        if(!empty($id)){
            $where[]=['id', '<>', $id];
        }
        $where[]=['status', '<', 2];
        $where[]=['title', '=', trim($name)];
        return $this->checkFieldUnique('title',$where);
    }


    /**
     * 获取文章详情
     * @param array $params
     * */
    public function getBlogInfo($params=[]){
        if(empty($params)){
            return [];
        }
        $where=[];
        $where[]=['status','<',2];
        if(!empty($params['id'])){
            $where['id']=$params['id'];
        }
        if(isset($params['status'])) {
            $where['status'] = $params['status'];
        }
        $info= $this->newInstance()->where($where)->first();
        if(!empty($info)){
            return $info->toArray();
        }else{
            return [];
        }
    }

    /**
     * 获取文章基本列表
     * */
    public function getPublishBlogList($params,$fields='a.*'){
        $where=[];
        $where['a.status']=0;
        if(!empty($params['plate_id'])){
            $where['a.plate_id']=$params['plate_id'];
        }
        $query= $this->newInstance()->alias('a')
            ->leftJoin('web_seo as b', 'a.seo_id', '=', 'b.id')
            ->where($where);
        $pageSize = $this->getPageSize($params);
        if(!empty($params['sort'])){
            if(!empty($params['sort']['is_hot'])){
                $query=$query->orderBy('a.total_view','desc');
            }
            if(!empty($params['sort']['is_top'])){
                $query=$query->orderBy('a.is_top','desc');
            }
            if(!empty($params['sort']['is_recommend'])){
                $query=$query->orderBy('a.is_recommend','desc');
            }
            if(!empty($params['sort']['pub_date'])){
                $query=$query->orderBy('a.pub_date','desc');
            }
        }
        if (!empty($params['keyword'])) {
            $keyword = $params['keyword'];
            $query=$query->where(function ($queryStr) use ($keyword) {
                $queryStr->where('a.title', 'like', "%" . $keyword . "%")
                    ->orWhere('a.description', 'like', '%' . $keyword . '%');
            });
        }
        $list= $query->limit($pageSize)->selectRaw($fields)->get();
        if(!empty($list)){
            $list=$list->toArray();
        }else{
            $list=[];
        }
        return $list;
    }

    /**
     * 根据类型ids获取个类型文章
     * */
    public function getRenderListByTypeIds($typeIds,$params,$fields='a.*'){
        $where=[];
        $where['a.status']=0;
        $where['b.status']=0;
        if(!empty($params['plate_id'])){
            $where['a.plate_id']=$params['plate_id'];
        }
        $pageSize = $this->getPageSize($params);
        $sql = '';
        foreach ($typeIds as $item){
            $sqlStr= $this->newInstance()->alias('a')
                ->leftJoin('blog_type_relation as b', 'a.id', '=', 'b.blog_id')
                ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
                ->where($where)
                ->where('b.type_id','=',DB::raw($item))
                ->orderBy('a.is_top','desc')
                ->orderBy('a.pub_date','desc')
                ->limit($pageSize)->selectRaw($fields);
            if (empty($sql)) {
                $sql = $sqlStr;
            } else {
                $sql->unionAll($sqlStr);
            }
        }
        $list=[];
        if(!empty($sql)){
            $list = $sql->get();
            if($list){
                $list=$list->toArray();
            }
        }
        return $list;
    }

    /**
     * 根据类型id获取文章
     * */
    public function getPublishBlogListByTypeId($params,$fields='a.*'){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $where=[];
        $where['a.status']=0;

        if(!empty($params['type_id'])){
            $where['b.type_id']= $params['type_id'];
            $where['b.status']=0;
        }
        if (!empty($params['plate_id'])) {
            $where['a.plate_id'] = $params['plate_id'];
        }
        $typeId = empty($params['type_id'])?0:$params['type_id'];

       $list =  $this->newInstance()->alias('a')->selectRaw($fields)
            ->leftJoin('blog_type_relation as b', 'a.id', '=', 'b.blog_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)
            ->groupBy('a.id')
            ->orderBy('a.is_top','desc')
            ->orderBy('a.pub_date','desc')
            ->paginate($pageSize)
            ->toArray();
        return $list;
    }

    /**
     * 根据标签id获取文章
     * */
    public function getPublishBlogListByTagId($params,$fields='a.*'){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $where=[];
        $where['a.status']=0;

        if(!empty($params['tag_id'])){
            $where['b.tag_id']= $params['tag_id'];
            $where['b.status']=0;
        }
        if (!empty($params['plate_id'])) {
            $where['a.plate_id'] = $params['plate_id'];
        }
        $totalCount=  $this->newInstance()->alias('a')
            ->leftJoin('blog_tag_relation as b', 'a.id', '=', 'b.blog_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)->count();
        $list=  $this->newInstance()->alias('a')
            ->leftJoin('blog_tag_relation as b', 'a.id', '=', 'b.blog_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)
            ->orderBy('a.is_top','desc')
            ->orderBy('a.pub_date','desc')
            ->limit($pageSize)->selectRaw($fields)->get();
        if($list){
            $list=$list->toArray();
        }else{
            $list=[];
        }
        $result = $this->buildPaginator($list, $skip, $page, $pageSize, $totalCount);
        return $result;
    }

    /**
     * 根据类型ids获取标签文章
     * */
    public function getRenderListByTagIds($tagIds,$params,$fields='a.*'){
        $where=[];
        $where['a.status']=0;
        $where['b.status']=0;
        if(!empty($params['plate_id'])){
            $where['a.plate_id']=$params['plate_id'];
        }
        $pageSize = $this->getPageSize($params);
        $sql = '';
        foreach ($tagIds as $item){
            $sqlStr= $this->newInstance()->alias('a')
                ->leftJoin('blog_tag_relation as b', 'a.id', '=', 'b.blog_id')
                ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
                ->where($where)
                ->where('b.tag_id','=',DB::raw($item))
                ->orderBy('a.is_top','desc')
                ->orderBy('a.pub_date','desc')
                ->limit($pageSize)->selectRaw($fields);
            if (empty($sql)) {
                $sql = $sqlStr;
            } else {
                $sql->unionAll($sqlStr);
            }
        }
        $list=[];
        if(!empty($sql)){
            $list = $sql->get();
            if($list){
                $list=$list->toArray();
            }
        }
        return $list;
    }

    /**
     * 获取板块文章
     * */
    public function getNewestPlateList($plateIds,$params,$fields='a.*'){
        $where=[];
        $where['a.status']=0;
        $pageSize = $this->getPageSize($params);
        $sql = '';
        foreach ($plateIds as $item){
            $sqlStr= $this->newInstance()->alias('a')
                ->leftJoin('web_seo as b', 'a.seo_id', '=', 'b.id')
                ->where($where)
                ->where('a.plate_id','=',DB::raw($item))
                ->orderBy('a.is_top','desc')
                ->orderBy('a.sort','asc')
                ->limit($pageSize)->selectRaw($fields);
            if (empty($sql)) {
                $sql = $sqlStr;
            } else {
                $sql->unionAll($sqlStr);
            }
        }
        $list=[];
        if(!empty($sql)){
            $list = $sql->get();
            if($list){
                $list=$list->toArray();
            }
        }
        return $list;
    }


    public function updatePv($id){
        DB::update('update blog set votes = 1');
    }

    /**
     * 获取文章基本列表 分页
     * @param $params
     * @param string $fields
     * @return array
     */
    public function getPublishBlogListByPage($params, string $fields = 'a.*')
    {
        $where = [];
        $where['a.status'] = 0;
//        $where['c.status'] = 0;
        /*if ($params['plate_id'] === 12) {
            dd($where);
        }*/
        $query = $this->newInstance()->alias('a')
            ->leftJoin('web_seo as b', 'a.seo_id', '=', 'b.id')
            ->leftJoin('blog_type_relation as c', 'a.id', '=', 'c.blog_id')
            ->leftJoin('blog_tag_relation as d', 'a.id', '=', 'd.blog_id');
        if (!empty($params['plate_id'])) {
            $where['a.plate_id'] = $params['plate_id'];
        }
        if (!empty($params['type_id'])) {
            if (is_array($params['type_id'])) {
                $query = $query->whereIn('c.type_id', $params['type_id']);
            } else {
                $where['c.type_id'] = $params['type_id'];
            }
            $where['c.status'] = 0;
        }
        if (!empty($params['tag_id'])) {
            if (is_array($params['tag_id'])) {
                $query = $query->whereIn('d.tag_id', $params['tag_id']);
            } else {
                $where['d.tag_id'] = $params['tag_id'];
            }
            $where['d.status'] = 0;
        }
        $query = $query->where($where);
        $pageSize = $this->getPageSize($params);
        if (!empty($params['sort'])) {
            if (!empty($params['sort']['is_hot'])) {
                $query = $query->orderBy('a.total_view', 'desc');
            }
            if (!empty($params['sort']['is_top'])) {
                $query = $query->orderBy('a.is_top', 'desc');
            }
            if (!empty($params['sort']['is_recommend'])) {
                $query = $query->orderBy('a.is_recommend', 'desc');
            }
            if (!empty($params['sort']['pub_date'])) {
                $query = $query->orderBy('a.pub_date', 'desc');
            }
        } else {
            $query = $query->orderBy('a.sort');
        }
        if (!empty($params['keyword'])) {
            $keyword = $params['keyword'];
            $query = $query->where(function ($queryStr) use ($keyword) {
                $queryStr->where('a.title', 'like', "%" . $keyword . "%")
                    ->orWhere('a.description', 'like', '%' . $keyword . '%');
            });
        }
        $list = $query->selectRaw($fields)->groupBy('a.id')->paginate($pageSize);
        if (!empty($list)) {
            $list = $list->toArray();
        } else {
            $list = [];
        }
        return $list;
    }

    public function getPrevBlog($plateId, $blogId)
    {
        $data = $this->alias('a')
            ->leftJoin('web_seo as b', 'a.seo_id', '=', 'b.id')
            ->where('a.plate_id', $plateId)
            ->where('a.id', '<', $blogId)
            ->where('a.status', 0)
            ->selectRaw('a.id,b.urla,a.title,a.image_url,a.image_alt,a.description')
            ->orderBy('a.id', 'asc')
            ->first();
        return $data ? $data->toArray(): [];
    }

    public function getNextBlog($plateId, $blogId)
    {
        $data = $this->alias('a')
            ->leftJoin('web_seo as b', 'a.seo_id', '=', 'b.id')
            ->where('a.plate_id', $plateId)
            ->where('a.id', '>', $blogId)
            ->where('a.status', 0)
            ->selectRaw('a.id,b.urla,a.title,a.image_url,a.image_alt,a.description')
            ->orderBy('a.id', 'asc')
            ->first();
        return $data ? $data->toArray() : [];
    }



}