<?php


namespace App\Web\Models;


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


class HelpModel extends BaseModel
{


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

    /**
     * 获取文章列表
     * */
    public function getHelpList($params,$fields='a.*'){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $where=[];
        if(isset($params['status'])){
            $where[]=['a.status','=',$params['status']];
        }else{
            $where[]=['a.status','<',2];
        }
        if(!empty($params['lt_total_view'])){
            $where[]=['a.total_view','<',$params['lt_total_view']];
        }
        $query= $this->newInstance()->alias('a')->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('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 getUnrelatedHelpByTypeIds($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('help_type_relation as b', function ($join)use($typeId) {
                $join->on('a.id', '=', 'b.help_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('help_type_relation as b', function ($join)use($typeId) {
                $join->on('a.id', '=', 'b.help_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 getUnrelatedHelpByTagIds($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('help_tag_relation as b', function ($join)use($tagId) {
                $join->on('a.id', '=', 'b.help_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('help_tag_relation as b', function ($join)use($tagId) {
                $join->on('a.id', '=', 'b.help_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 saveHelpData($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 getHelpInfo($params=[]){
        if(empty($params)){
            return [];
        }
        $where=[];
        $where[]=['status','<',2];
        if(!empty($params['id'])){
            $where['id']=$params['id'];
        }
        $info= $this->newInstance()->where($where)->first();
        if(!empty($info)){
            return $info->toArray();
        }else{
            return [];
        }
    }

    /**
     * 获取文章基本列表
     * */
    public function getPublishHelpList($params,$fields='a.*'){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $where=[];
        $where['a.status']=0;
        $query= $this->newInstance()->alias('a')
            ->leftJoin('web_seo as b', 'a.seo_id', '=', 'b.id')
            ->where($where);
        if(!empty($params['sort'])){
            if(!empty($params['sort']['is_hot'])){
                $query=$query->orderBy('a.total_view','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 . '%');
            });
        }
        if(!empty($params['only_List'])){
            $list= $query->orderBy('a.id','desc')->selectRaw($fields)->limit($pageSize)->get()->toArray();
        }else{
            $list= $query->orderBy('a.id','desc')->selectRaw($fields)->paginate($pageSize)->toArray();;
        }

        if(empty($list)){
            $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('help_type_relation as b', 'a.id', '=', 'b.help_id')
                ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
                ->where($where)
                ->where('b.type_id','=',DB::raw($item))
                ->orderBy('a.pub_date','desc')
                ->orderBy('a.id','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 getPublishHelpListByTypeId($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;
        }
        $list =  $this->newInstance()->alias('a')->selectRaw($fields)
            ->leftJoin('help_type_relation as b', 'a.id', '=', 'b.help_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)
            ->groupBy('a.id')
            ->orderBy('a.pub_date','desc')
            ->orderBy('a.id','desc')
            ->paginate($pageSize)
            ->toArray();
        return $list;
    }

    /**
     * 根据标签id获取文章
     * */
    public function getPublishHelpListByTagId($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('help_tag_relation as b', 'a.id', '=', 'b.help_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)->count();
        $list=  $this->newInstance()->alias('a')
            ->leftJoin('help_tag_relation as b', 'a.id', '=', 'b.help_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)
            ->orderBy('a.pub_date','desc')
            ->orderBy('a.id','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('help_tag_relation as b', 'a.id', '=', 'b.help_id')
                ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
                ->where($where)
                ->where('b.tag_id','=',DB::raw($item))
                ->orderBy('a.pub_date','desc')
                ->orderBy('a.id','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 updatePv($id){
        DB::update('update help set votes = 1');
    }


}