exhibition_service_api/app/Web/Models/ProductModel.php

<?php


namespace App\Web\Models;



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


class ProductModel extends BaseModel
{



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

    /**
     * 获取产品列表
     * */
    public function getProductList($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 getUnrelatedProductByTypeIds($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('product_type_relation as b', function ($join)use($typeId) {
                $join->on('a.id', '=', 'b.product_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('product_type_relation as b', function ($join)use($typeId) {
                $join->on('a.id', '=', 'b.product_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;
    }

    /**
     * 后台根据类型id 获取产品列表
     * */

    public function getAdminProductData($params) {
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $typeId = $params['type_id'] ?? '';
        $where=[];
        $keyword='';
        if (!empty($params['keyword'])) {
            $keyword = $params['keyword'];
            $where[] = ['b.title', 'like', "%" . $keyword . "%"];
        }
        if(isset($params['status'])) {
            $where[]=['b.status','=',$params['status']];
        } else {
            $where[]=['b.status','<',2];
        }
        if(!empty($typeId)) {
            $where[]=['a.status','=',0];
            if(is_array($typeId)){
                $query= $this->newInstance()->alias('b')
                    ->leftJoin('product_type_relation as a','a.product_id','=','b.id')
                    ->leftJoin('web_seo as s', 's.id', '=', 'b.seo_id')
                    ->where($where)
                    ->whereIn('a.type_id',$typeId);
            } else {
                $where[]=['a.type_id','=',$typeId];
                $query= $this->newInstance()->alias('b')
                    ->leftJoin('product_type_relation as a','a.product_id','=','b.id')
                    ->leftJoin('web_seo as s', 's.id', '=', 'b.seo_id')
                    ->where($where);
            }
        } else {
            $query= $this->newInstance()->alias('b')
                ->leftJoin('web_seo as s', 's.id', '=', 'b.seo_id')
                ->where($where);
        }
        $fields='b.id,b.title,b.pub_date,b.image_url,b.is_top,b.is_recommend,b.description,b.status,b.update_time,b.create_time,s.urla,s.seo_title,s.seo_keyword,s.seo_describe,s.trans_status';

        $list= $query->skip($skip)
            ->limit($pageSize)->groupBy('b.id')
            ->selectRaw($fields)
            ->orderBy('sort')->paginate($pageSize)->toArray();
        return $list;
    }



    public function getProductByTypeIds($params){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $typeId = $params['type_id'] ?? '';
        $where=[];
        $keyword='';
        if (!empty($params['keyword'])) {
            $keyword = $params['keyword'];
            $where[] = ['b.title', 'like', "%" . $keyword . "%"];
        }
        if(isset($params['status'])){
            $where[]=['b.status','=',$params['status']];
        }else{
            $where[]=['b.status','<',2];
        }
        if(!empty($typeId)){
            $where[]=['a.status','=',0];
            if(is_array($typeId)){
                $query= $this->newInstance()->alias('b')
                    ->leftJoin('product_type_relation as a','a.product_id','=','b.id')
                    ->leftJoin('web_seo as s', 's.id', '=', 'b.seo_id')
                    ->where($where)
                    ->whereIn('a.type_id',$typeId);
            } else {
                $where[]=['a.type_id','=',$typeId];
                $query= $this->newInstance()->alias('b')
                    ->leftJoin('product_type_relation as a','a.product_id','=','b.id')
                    ->leftJoin('web_seo as s', 's.id', '=', 'b.seo_id')
                    ->where($where);
            }
        } else {
            $query= $this->newInstance()->alias('b')
                ->leftJoin('web_seo as s', 's.id', '=', 'b.seo_id')
                ->where($where);
        }
        $fields='b.id,b.title,b.pub_date,b.image_url,b.description,b.status,b.update_time,b.create_time,s.urla,s.seo_title,s.seo_keyword,s.seo_describe';
        $totalCount = $query->count();
        $list= $query->skip($skip)
            ->limit($pageSize)
            ->groupBy('b.id')
            ->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 getUnrelatedProductByTagId($params) {
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $tagId = $params['tag_id'] ?? '';
        $keyword = $params['keyword'] ?? '';
        $excludeTagId = $params['exclude_tag_id'] ?? [];

        // 确保excludeTagId不包含当前tagId
        if (in_array($tagId, $excludeTagId)) {
            $excludeTagId = array_diff($excludeTagId, [$tagId]);
        }

        $where = [];
        if ($keyword) {
            $where[] = function($query) use ($keyword) {
                $query->where('a.title', 'like', "%{$keyword}%")
                    ->orWhere('a.description', 'like', "%{$keyword}%");
            };
        }

        $fields = 'a.id,a.title,a.pub_date,a.image_url,a.description,a.status,a.update_time,a.create_time';

        // 子查询：查找关联了排除标签的产品ID
        $excludedProductIds = DB::table('product_tag_relation')
            ->whereIn('tag_id', $excludeTagId)
            ->where('status', 0)
            ->pluck('product_id')
            ->toArray();
        if (!empty($tagId)) {
            $query = $this->newInstance()->alias('a')
                ->leftJoin('product_tag_relation as b', 'a.id', '=', 'b.product_id')
                ->selectRaw($fields)
                ->where('b.status', '=', 0)
                ->where('a.status', '<', 2)
                ->where('b.tag_id', '=', $tagId)
                ->groupBy('a.id')
                ->whereNotIn('a.id', $excludedProductIds);
        } else {
            $query = $this->newInstance()->alias('a')
                ->selectRaw($fields)
                ->where('a.status', '<', 2)
                ->groupBy('a.id')
                ->whereNotIn('a.id', $excludedProductIds);
        }
        // 主查询：查找包含指定标签但不包含排除标签的产品


        if (!empty($where)) {
            $query->where($where);
        }

        $list = $query->paginate($pageSize, ['*'], 'page', $page)->toArray();

        return $list;
    }
    public function getUnrelatedProductByTagIds($params){
        list($pageSize, $page, $skip) = $this->getPaginatorParams($params);
        $tagId = $params['tag_id'] ?? '';
        $keyword = $params['keyword'] ?? '';
        $excludeTagId = $params['exclude_tag_id'] ?? null; // 假设传入的过滤标签 ID 参数名
        $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('product_tag_relation as b', function ($join)use($tagId,$excludeTagId) {
                $join->on('a.id', '=', 'b.product_id')
                    ->where('b.status', '=', 0)
                    ->where('b.tag_id', '=', $tagId)
                    ->whereNotIn('a.tag_id', $excludeTagId);
            })->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('product_tag_relation as b', function ($join)use($tagId,$excludeTagId) {
                $join->on('a.id', '=', 'b.product_id')
                    ->where('b.status', '=', 0)
                    ->where('b.tag_id', '=', $tagId)
                    ->whereNotIn('a.tag_id', $excludeTagId);
            })->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 saveProductData($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 getProductInfo($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 getPublishProductList($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'])) {
            $result = [];
            if (isset($params['sort']) && is_array($params['sort'])) {
                // 遍历sort数组中的每个元素
                foreach ($params['sort'] as $field) {
                    // 为每个字段创建关联数组，值为1
                    $result[$field] = 1;
                }
                $params['sort'] = $result;
            } else if (is_string($params['sort'])) {
                $params['sort'] = [$params['sort']=>1];
            }
            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']['update_time'])) {
                $query=$query->orderBy('a.update_time','desc');
            }
            if(!empty($params['sort']['create_time'])) {
                $query=$query->orderBy('a.create_time','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['ids'])){
            $query->whereIn('a.id',$params['ids']);
        }
        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('product_type_relation as b', 'a.id', '=', 'b.product_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 getPublishProductListByTypeId($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('product_type_relation as b', 'a.id', '=', 'b.product_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 getPublishProductListByTagId($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('product_tag_relation as b', 'a.id', '=', 'b.product_id')
            ->leftJoin('web_seo as c', 'a.seo_id', '=', 'c.id')
            ->where($where)->count();
        $list=  $this->newInstance()->alias('a')
            ->leftJoin('product_tag_relation as b', 'a.id', '=', 'b.product_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('product_tag_relation as b', 'a.id', '=', 'b.product_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 product set votes = 1');
    }

    /**
     * 根据id数组获取产品列表
     *
     * @param array $ids 产品 ID 数组
     * @return array 包含产品信息、类型和标签的数组
     */
    public function getProductByIds($ids){
        $list = $this->newInstance()
            ->alias('a')
            ->leftJoin('product_type_relation as b', 'a.id', '=', 'b.product_id')
            ->leftJoin('product_tag_relation as d', 'a.id', '=', 'd.product_id')
            ->whereIn('a.id', $ids)
            ->selectRaw("a.*, GROUP_CONCAT(b.type_id) as type_ids, GROUP_CONCAT(d.tag_id) as tag_ids")
            ->groupBy('a.id')
            ->get()
            ->toArray();
        foreach ($list as &$item) {
            if (isset($item['type_ids'])) {
                $item['type_ids'] = explode(',', $item['type_ids']);
                $item['type_ids'] = array_unique($item['type_ids']);
            }
            if (isset($item['tag_ids'])) {
                $item['tag_ids'] = explode(',', $item['tag_ids']);
                $item['tag_ids'] = array_unique($item['tag_ids']);
            }
            if (isset($item['spec'])) {
                $item['spec'] = json_decode($item['spec'], true);
            }
        }
        return $list;
    }
}