exhibition_service_api/app/Web/Models/MeetingModel.php

<?php


namespace App\Web\Models;


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


class MeetingModel extends BaseModel
{


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

    /**
     * 获取产品列表
     * */
    public function getMeetingList($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'] == 'score_ascending') {
                $query->orderBy('score', 'asc');
            } else if ($params['sort'] == 'score_descending') {
                $query->orderBy('score', '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 getUnrelatedMeetingByTypeIds($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('meeting_type_relation as b', function ($join) use ($typeId) {
                $join->on('a.id', '=', 'b.meeting_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('meeting_type_relation as b', function ($join) use ($typeId) {
                $join->on('a.id', '=', 'b.meeting_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 getUnrelatedMeetingByTagIds($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('meeting_tag_relation as b', function ($join) use ($tagId) {
                $join->on('a.id', '=', 'b.meeting_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('meeting_tag_relation as b', function ($join) use ($tagId) {
                $join->on('a.id', '=', 'b.meeting_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 saveMeetingData($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 getMeetingInfo($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 getPublishMeetingList($params, $fields = 'a.*')
    {
        $where = [];
        $where['a.status'] = 0;
        $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['sort']['start_date'])) {
                $query = $query->orderBy('a.start_date', 'asc');
                $query = $query->orderBy('a.end_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 . '%');
            });
        }
        if (!empty($params['country'])) {
            $query->where('a.country', $params['country']);
        }
        if (!empty($params['ids'])) {
            $query->whereIn('a.id', $params['ids']);
        }

        if(!empty($params['expo_date'])) {
            $dateArr = $params['expo_date'];
            $dateArr = explode(' ', $dateArr);
            if(!empty($dateArr[0]) && !empty($dateArr[1])) {
                $startDate = $dateArr[0].'-01';
                $date = new \DateTime($dateArr[1]);
                $date->modify('last day of this month');
                $endDate = $date->format('Y-m-d');
                //有日期落在选定区间内的
                $query = $query->where(function ($queryStr) use ($startDate, $endDate) {
                    $queryStr->where('a.start_date', '<=', $endDate)
                        ->where('a.end_date', '>=', $startDate);
                });
            }
        }
        if(!empty($params['is_paginate'])){
            $list= $query->selectRaw($fields)->paginate($pageSize)
                ->toArray();
        }else{
            $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;
        $pageSize = $this->getPageSize($params);
        $sql = '';
        foreach ($typeIds as $item) {
            $sqlStr = $this->newInstance()->alias('a')
                ->leftJoin('meeting_type_relation as b', 'a.id', '=', 'b.meeting_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 getPublishMeetingListByTypeId($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;
        }
        $typeId = empty($params['type_id']) ? 0 : $params['type_id'];

        $list = $this->newInstance()->alias('a')->selectRaw($fields)
            ->leftJoin('meeting_type_relation as b', 'a.id', '=', 'b.meeting_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 getPublishMeetingListByTagId($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;
        }
        $totalCount = $this->newInstance()->alias('a')
            ->leftJoin('meeting_tag_relation as b', 'a.id', '=', 'b.meeting_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)->count();
        $list = $this->newInstance()->alias('a')
            ->leftJoin('meeting_tag_relation as b', 'a.id', '=', 'b.meeting_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;
        $pageSize = $this->getPageSize($params);
        $sql = '';
        foreach ($tagIds as $item) {
            $sqlStr = $this->newInstance()->alias('a')
                ->leftJoin('meeting_tag_relation as b', 'a.id', '=', 'b.meeting_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 updatePv($id)
    {
        DB::update('update meeting set votes = 1');
    }

    public function getLocalCountryList()
    {
        $where = [];
        $where['a.status'] = 0;
        $list = $this->newInstance()->alias('a')
            ->where($where)
            ->selectRaw('DISTINCT country')
            ->get();
        $country = [];
        if (!empty($list)) {
            $list = $list->toArray();
            $country = array_column($list, 'country');
        }
        return $country;
    }

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

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

    public function getAreaDataWithTagId($tagId)
    {
        $where = ['a.status' => 0];
        $where[] = ['a.country', '<>', ''];
        $where['b.tag_id'] = $tagId;
        return $this->alias('a')
            ->leftJoin('meeting_tag_relation as b', 'a.id', '=', 'b.meeting_id')
            ->selectRaw('a.country AS id,a.country AS text')
            ->where($where)
            ->groupBy('a.country')
            ->limit(999)
            ->get()
            ->toArray();
    }

    public function getTypesWithTagId($tagId)
    {
        $where = ['a.status' => 0];
        $where['b.status'] = 0;
        $where['c.status'] = 0;
        $where['d.status'] = 0;
        $where['b.tag_id'] = $tagId;
        return $this->alias('a')
            ->leftJoin('meeting_tag_relation as b', 'a.id', '=', 'b.meeting_id')
            ->leftJoin('meeting_type_relation as c', 'b.meeting_id', '=', 'c.meeting_id')
            ->leftJoin('meeting_type as d', 'd.id', '=', 'c.type_id')
            ->selectRaw('d.id,d.type_name AS text')
            ->where($where)
            ->groupBy('d.type_name')
            ->limit(999)
            ->get()->toArray();
    }
}