mall/models/OrderCommentFack.php

<?php
/**
 * 厦门云联储网络科技有限公司
 * https://www.baokuaiyun.com
 * Copyright (c) 2023 爆块云 All rights reserved.
 */
namespace app\models;

use app\models\common\admin\log\CommonActionLog;
use Yii;
use yii\behaviors\TimestampBehavior;
use yii\db\ActiveRecord;
use yii\helpers\Json;

/**
 * This is the model class for table "{{%order_comment}}".
 *
 * @property integer $id
 * @property integer $store_id
 * @property integer $order_id
 * @property integer $order_detail_id
 * @property integer $goods_id
 * @property integer $user_id
 * @property integer $mch_id
 * @property string $score
 * @property string $content
 * @property string $pic_list
 * @property integer $is_hide
 * @property integer $is_delete
 * @property integer $created_at
 * @property integer $updated_at
 * @property string $reply_content
 * @property integer $is_virtual
 * @property string $virtual_user
 * @property string $virtual_avatar
 */
class OrderCommentFack extends \yii\db\ActiveRecord
{
    /**
     * 是否删除评论
     */
    const IS_DELETE_FALSE = 0;
    const IS_DELETE_TRUE = 1;

    const COMMENT_CONTENTS = [
        "非常好，性价比高。",
        "东西很好，物流也很快，性价比超高，值得购买！",
        "非常满意的一次购物。",
        "很不错，已经推荐给朋友了！",
        "价格很亲民，超级喜欢！",
        "超出了预期，值得推荐！",
        "值得入手！",
        "非常值得购买，赞一个！",
        "东西很好物流速度很快，好评！",
        "物流很快，昨天下单，今天一早就收到了，惊喜！",
        "很好，会回购。",
        "和描述一样，好评。",
        "性价比高，推荐购买。",
        "不错，挺好的。",
        "绝对五星好评！",
        "和描述一模一样，好评！",
        "物流超快，包装完好。",
        "已经推荐给朋友了。",
        "物超所值，强烈推荐！",
        "可以，没问题。",
        "基本满意，没问题。",
        "还行，符合预期。",
        "东西收到了，挺好。",
        "挺好的，会考虑回购。",
        "物有所值，好评。",
        "没什么问题，好评。",
        "发货快，东西完好。",
        "跟想象中差不多。",
        "总体满意，给个好评。",
        "对得起这个价钱了。",
        "已经推荐给朋友了，都说好！",
        "回购。",
        "挺好。",
        "不错。",
        "很好。",
        "好评",
        "满意",
        "实惠",
    ];

    /**
     * @inheritdoc
     */
    public static function tableName()
    {
        return '{{%order_comment_fack}}';
    }

    /**
     * @inheritdoc
     */
    public function rules()
    {
        return [
            [['store_id', 'order_id', 'order_detail_id', 'goods_id', 'user_id', 'score'], 'required'],
            [['store_id', 'mch_id', 'order_id', 'order_detail_id', 'goods_id', 'user_id', 'is_hide', 'is_delete',
                'created_at', 'updated_at', 'is_virtual'], 'integer'],
            [['score'], 'number'],
            [['pic_list'], 'string'],
            [['content'], 'string', 'max' => 1000],
            [['reply_content', 'virtual_user', 'virtual_avatar'], 'string', 'max' => 255],
        ];
    }

    /**
     * @inheritdoc
     */
    public function attributeLabels()
    {
        return [
            'id' => 'ID',
            'store_id' => 'Store ID',
            'mch_id' => 'Mch ID',
            'order_id' => 'Order ID',
            'order_detail_id' => 'Order Detail ID',
            'goods_id' => 'Goods ID',
            'user_id' => 'User ID',
            'score' => '评分：1=差评，2=中评，3=好',
            'content' => '评价内容',
            'pic_list' => '图片',
            'is_hide' => '是否隐藏：0=不隐藏，1=隐藏',
            'is_delete' => 'Is Delete',
            'created_at' => 'Addtime',
            'updated_at' => 'update time',
            'reply_content' => 'Reply Content',
            'is_virtual' => 'Is Virtual',
            'virtual_user' => 'Virtual User',
            'virtual_avatar' => 'Virtual Image',
        ];
    }

    public function behaviors()
    {
        return [
            [
                'class' => TimestampBehavior::class,
                'attributes' => [
                    ActiveRecord::EVENT_BEFORE_INSERT => ['created_at'],
                    ActiveRecord::EVENT_BEFORE_UPDATE => ['updated_at']
                ]
            ]
        ];
    }

    public static function generateVirtualComments($goodsId, $store_id, $count = null)
    {
        if ($count === null) {
            $count = rand(5, 20); // 随机5-20条
        }

        // 先搜索该商品是否已有虚拟评论
        $existingComments = self::find()
            ->where(['is_virtual' => 1, 'is_delete' => 0, 'goods_id' => $goodsId])
            ->all();

        // 如果没有虚拟评论，创建新的
        if (empty($existingComments)) {
            $currentTime = time();

            for ($i = 0; $i < $count; $i++) {
                $score = self::generateRandomScore();
                $user = User::find()->where(['store_id' => $store_id])->orderBy('RAND()')->one();

                // 所有评论都在10天内随机
                $commentTime = $currentTime - rand(0, 864000); // 0-10天

                $comment = new self();
                $comment->goods_id = $goodsId;
                $comment->store_id = $store_id;
                $comment->order_id = rand(1000, 9999);
                $comment->order_detail_id = rand(1000, 9999);
                $comment->user_id = rand(100, 999);
                $comment->score = $score;
                $comment->content = self::COMMENT_CONTENTS[array_rand(self::COMMENT_CONTENTS)];
                $comment->pic_list = null;
                $comment->is_hide = 0;
                $comment->is_delete = 0;
                $comment->created_at = $commentTime;
                $comment->is_virtual = 1;
                $comment->virtual_user = $user->username;
                $comment->virtual_avatar = $user->avatar_url;
                $comment->mch_id = 0;
                $comment->updated_at = $currentTime;
                $comment->save();
            }
        } else {
            // 如果有虚拟评论，修改日期为10天内
            $currentTime = time();
            foreach ($existingComments as $comment) {
                $commentTime = $currentTime - rand(0, 864000); // 0-10天
                $comment->created_at = $commentTime;
                $comment->updated_at =$currentTime;
                $comment->save();
            }
        }

        return true;
    }


    /**
     * 生成随机评分（4-5星）
     */
    private static function generateRandomScore()
    {
        $scores = [4.0, 4.5, 5.0];
        $weights = [20, 30, 50]; // 权重：5星50%，4.5星30%，4星20%

        $random = rand(1, 100);
        $cumulative = 0;

        foreach ($weights as $index => $weight) {
            $cumulative += $weight;
            if ($random <= $cumulative) {
                return $scores[$index];
            }
        }

        return 5.0; // 默认返回5星
    }

    public static function getMixedCommentsCount($goodsId)
    {
        // 真实评价数量
        $realCount = OrderComment::find()
            ->where(['goods_id' => $goodsId, 'is_delete' => 0, 'is_hide' => 0])
            ->count();

        // 虚拟评价数量
        $virtualCount = OrderCommentFack::find()
            ->where(['is_delete' => 0, 'is_hide' => 0,'goods_id' => $goodsId])
            ->count();

        return $realCount + $virtualCount;
    }

    public static function getMixedCommentsUnion($goodsId, $limit = 20, $page = 1, $score = -1, $orderBy = 'created_at_formatted DESC')
    {
        OrderCommentFack::generateVirtualComments($goodsId,get_store_id());
        $offset = ($page - 1) * $limit;
        $sql = "
    (SELECT 
        c.id, c.order_id, c.goods_id, c.user_id, c.score, c.content, c.pic_list, 
        FROM_UNIXTIME(c.created_at, '%m-%d') as created_at_formatted,
        u.nickname,
        u.avatar_url,
        od.attr,
        (SELECT COUNT(*) FROM cyy_order_detail o WHERE o.user_id = c.user_id AND o.goods_id = c.goods_id) as purchase_count
    FROM cyy_order_comment c
    LEFT JOIN cyy_user u ON c.user_id = u.id
    LEFT JOIN cyy_order_detail od ON c.order_id = od.order_id AND c.goods_id = od.goods_id
    WHERE c.goods_id = :goodsId AND c.is_delete = 0 AND c.is_hide = 0";

        if ($score > -1) {
            switch ($score) {
                case 1:
                    $sql .= " AND c.score >= 4";
                    break;
                case 2:
                    $sql .= " AND c.score >= 2 AND c.score < 4";
                    break;
                case 3:
                    $sql .= " AND c.score < 2";
                    break;
                default:
                    break;
            }
        }

        $sql .= ")
    
    UNION ALL
    
    (SELECT 
        cf.id, cf.order_id, cf.goods_id, cf.user_id, cf.score, cf.content, cf.pic_list, 
        FROM_UNIXTIME(cf.created_at, '%m-%d') as created_at_formatted,
        u.nickname,
        u.avatar_url,
        od.attr,
        (SELECT COUNT(*) FROM cyy_order_detail o WHERE o.user_id = cf.user_id AND o.goods_id = cf.goods_id) as purchase_count
    FROM cyy_order_comment_fack cf
    LEFT JOIN cyy_user u ON cf.user_id = u.id
    LEFT JOIN cyy_order_detail od ON cf.order_id = od.order_id AND cf.goods_id = od.goods_id
    WHERE cf.is_delete = 0 AND cf.is_hide = 0 AND cf.goods_id = :goodsId";

        if ($score > -1) {
            switch ($score) {
                case 1:
                    $sql .= " AND cf.score < 2";
                    break;
                case 2:
                    $sql .= " AND cf.score >= 2 AND cf.score < 4";
                    break;
                case 3:
                    $sql .= " AND cf.score >= 4";
                    break;
                default:
                    break;
            }
        }

        $sql .= ")
    
    ORDER BY {$orderBy}
    LIMIT :limit OFFSET :offset
";

        $data['list'] = Yii::$app->db->createCommand($sql, [
            ':goodsId' => $goodsId,
            ':limit' => $limit,
            ':offset' => $offset
        ])->queryAll();
        $goods = Goods::findOne($goodsId);
        $attr_f = Json::decode($goods->attr);
        $attr_f = $attr_f[0]['attr_list'];
        $attr_name = '';
        foreach ($attr_f as $a){
            $attr_name.= $attr_name==''?$a['attr_name']:'-'.$a['attr_name'];
        }

// 确保购买次数至少为1
        foreach ($data['list'] as &$item) {
            $item['purchase_count'] = max(1, intval($item['purchase_count']));
            $item['pic_list'] = empty($item['pic_list']) ? [] : Json::decode($item['pic_list']);
            if (!empty($item['attr'])) {
                $attr = Json::decode($item['attr']);
                $item['attr_name'] = implode('-', array_column($attr, 'attr_name'));
            }else{
                $item['attr'] = [];
                $item['attr_name'] = $attr_name;
            }
        }
        unset($item);

        $data['pageSize'] = $limit;
        $data['pageNo'] = $page;
        $data['totalCount'] = self::getMixedCommentsCount($goodsId);

        $data['amazing_rate'] = OrderCommentFack::calculateAmazingRate($goodsId);
        $data['satisfaction_rate'] = OrderCommentFack::calculateSatisfactionRate($goodsId);
        $key = 'order_comment_tags_' . $goodsId;
        $tags = Yii::$app->cache->get($key);
        if (!$tags){
            $tags = OrderComment::distributeTags($data['totalCount']);
            Yii::$app->cache->set($key, $tags, 60*5);
        }
        $data['tags'] = $tags;


        return $data;
    }

    public static function calculateAmazingRate($goodsId)
    {
        // 真实评价中4星及以上的数量
        $realHighScoreCount = OrderComment::find()
            ->where([
                'goods_id' => $goodsId,
                'is_delete' => 0,
                'is_hide' => 0
            ])
            ->andWhere(['>=', 'score', 4.0])
            ->count();

        // 虚拟评价中4星及以上的数量
        $virtualHighScoreCount = OrderCommentFack::find()
            ->where([
                'goods_id' => $goodsId,
                'is_delete' => 0,
                'is_hide' => 0
            ])
            ->andWhere(['>=', 'score', 4.0])
            ->count();

        // 总评价数量
        $totalCount = self::getMixedCommentsCount($goodsId);

        if ($totalCount == 0) {
            return 0;
        }

        $highScoreCount = $realHighScoreCount + $virtualHighScoreCount;
        return round(($highScoreCount / $totalCount) * 100, 1);
    }

    /**
     * 计算满意度（3星及以上评分占比）
     */
    public static function calculateSatisfactionRate($goodsId)
    {
        // 真实评价中3星及以上的数量
        $realSatisfiedCount = OrderComment::find()
            ->where([
                'goods_id' => $goodsId,
                'is_delete' => 0,
                'is_hide' => 0
            ])
            ->andWhere(['>=', 'score', 3.0])
            ->count();

        // 虚拟评价中3星及以上的数量
        $virtualSatisfiedCount = OrderCommentFack::find()
            ->where([
                'goods_id' => $goodsId,
                'is_delete' => 0,
                'is_hide' => 0
            ])
            ->andWhere(['>=', 'score', 3.0])
            ->count();

        // 总评价数量
        $totalCount = self::getMixedCommentsCount($goodsId);

        if ($totalCount == 0) {
            return 0;
        }

        $satisfiedCount = $realSatisfiedCount + $virtualSatisfiedCount;
        return round(($satisfiedCount / $totalCount) * 100, 1);
    }


}