mall/modules/admin/models/AnalysisDataForm.php

<?php
/**
 * 重庆赤晓店信息科技有限公司
 * https://www.chixiaodian.com
 * Copyright (c) 2023 赤店商城 All rights reserved.
 */
namespace app\modules\admin\models;


use app\models\Cart;
use app\models\CommonOperation;
use app\models\Goods;
use app\models\Mch;
use app\models\Order;
use app\models\OrderDetail;
use app\models\User;
use app\models\UserViews;
use yii\base\Model;
use yii\helpers\Json;

class AnalysisDataForm extends Model
{
    public $store_id;
    public $user_id;
    public $mch_id;

    /**
     * @var CommonOperation $model
     */
    public $model;

    public $text;

    public $start_time;
    public $end_time;


    /**
     * @return array
     */
    public function getData()
    {
        // 当前时间 2021-05-14 09:07:38
        $current_time = time();
        // 截止到当前时间的一周 2021-05-07 09:07:38～2021-05-14 09:07:38
        $current_before_week_time = strtotime("-1 week", $current_time);
        // 再往前推一周
        $far_before_week_time = strtotime("-1 week", $current_before_week_time);
        // 截止到当前时间到一天
        $current_before_day_time = strtotime("-1 day", $current_time);
        // 再往前推一天
        $far_before_day_time = strtotime("-1 day", $current_before_day_time);
        \Yii::error([
            'current_time ++++++ ' . date('Y-m-d H:i:s', $current_time),
            'current_before_week_time ++++++ ' . date('Y-m-d H:i:s', $current_before_week_time),
            'far_before_week_time ++++++ ' . date('Y-m-d H:i:s', $far_before_week_time),
            'current_before_day_time ++++++ ' . date('Y-m-d H:i:s', $current_before_day_time),
            'far_before_day_time ++++++ ' . date('Y-m-d H:i:s', $far_before_day_time)
        ]);
        /***** 销售额 START *****/
        // 所有订单的总销售额
        $total_sell_money = Order::find()->where(['store_id' => $this->store_id, 'is_pay' => Order::IS_PAY_TRUE, 'mch_id' => $this->mch_id])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->sum('pay_price');
        // 截止到当前时间的本周销售额 开始截止时间为：$current_before_week_time ～ $current_time
        $this_week_sell_money = Order::find()->where(['store_id' => $this->store_id, 'is_pay' => Order::IS_PAY_TRUE, 'mch_id' => $this->mch_id])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $current_before_week_time])->andWhere(['<=', 'pay_time', $current_time])->sum('pay_price');
        // 截止到当前时间前一周时间点的上周销售额 开始截止时间为：$far_before_week_time ～ $current_before_week_time
        $before_week_sell_money = Order::find()->where(['store_id' => $this->store_id, 'is_pay' => Order::IS_PAY_TRUE, 'mch_id' => $this->mch_id])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $far_before_week_time])->andWhere(['<=', 'pay_time', $current_before_week_time])->sum('pay_price');
        if ($before_week_sell_money) {
            // 销售额周环比（与上一周相比）当前一周：$current_before_week_time ～ $current_time，上一周：$far_before_week_time ～ $current_before_week_time
            $this_week_sell_percentage = ($this_week_sell_money - $before_week_sell_money) / $before_week_sell_money;
            // 销售额周环比百分数
            $this_week_sell_basis_percent = sprintf("%01.2f", $this_week_sell_percentage * 100).'%';
        } else {
            $this_week_sell_basis_percent = '-';
        }


        // 日销售额 当天00:00:00 ~ 当前时间
        $this_time_sell_money = Order::find()->where(['store_id' => $this->store_id, 'is_pay' => Order::IS_PAY_TRUE, 'mch_id' => $this->mch_id])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', strtotime(date('Y-m-d 00:00:00', time()))])->andWhere(['<=', 'pay_time', $current_time])->sum('pay_price');
        // 截止到当前时间的本天销售额 开始截止时间为：$current_before_day_time ～ $current_time
        $this_day_sell_money = Order::find()->where(['store_id' => $this->store_id, 'is_pay' => Order::IS_PAY_TRUE, 'mch_id' => $this->mch_id])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $current_before_day_time])->andWhere(['<=', 'pay_time', $current_time])->sum('pay_price');
        // 截止到当前时间前一天时间点的上天销售额 开始截止时间为：$far_before_day_time ～ $current_before_day_time
        $before_day_sell_money = Order::find()->where(['store_id' => $this->store_id, 'is_pay' => Order::IS_PAY_TRUE, 'mch_id' => $this->mch_id])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $far_before_day_time])->andWhere(['<=', 'pay_time', $current_before_day_time])->sum('pay_price');
        if ($before_day_sell_money) {
            // 销售额日环比（与上一天相比）当前一天：$current_before_day_time ～ $current_time，上一周：$far_before_day_time ～ $current_before_day_time
            $this_day_sell_percentage = ($this_day_sell_money - $before_day_sell_money) / $before_day_sell_money;
            // 销售额日环比百分数
            $this_day_sell_basis_percent = sprintf("%01.2f", $this_day_sell_percentage * 100).'%';
        } else {
            $this_day_sell_basis_percent = '-';
        }

        /***** 销售额 END *****/


        /***** 商品总数 START *****/
        // 截止到目前商品的总数量（已上架和未删除）
        $goods_count = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL, 'is_delete' => 0])->count(1);
        // 截止到当前时间的本周商品数量 开始截止时间为：$current_before_week_time ～ $current_time
        $this_week_sell_goods = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL, 'is_delete' => 0])->andWhere(['>=', 'created_at', $current_before_week_time])->andWhere(['<=', 'created_at', $current_time])->count(1);
        // 截止到当前时间前一周时间点的上周商品数量 开始截止时间为：$far_before_week_time ～ $current_before_week_time
        $before_week_sell_goods = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL, 'is_delete' => 0])->andWhere(['>=', 'created_at', $far_before_week_time])->andWhere(['<=', 'created_at', $current_before_week_time])->count(1);
        if ($before_week_sell_goods) {
            // 商品数量周环比（与上一周相比）当前一周：$current_before_week_time ～ $current_time，上一周：$far_before_week_time ～ $current_before_week_time
            $this_week_goods_percentage = ($this_week_sell_goods - $before_week_sell_goods) / $before_week_sell_goods;
            // 销售额周环比百分数
            $this_week_goods_basis_percent = sprintf("%01.2f", $this_week_goods_percentage * 100).'%';
        } else {
            $this_week_goods_basis_percent = '-';
        }


        // 日新增 当天00:00:00 ~ 当前时间
        $this_time_goods = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL, 'is_delete' => 0])->andWhere(['>=', 'created_at', strtotime(date('Y-m-d 00:00:00', time()))])->andWhere(['<=', 'created_at', $current_time])->count(1);
        // 截止到当前时间的本天商品数量 开始截止时间为：$current_before_day_time ～ $current_time
        $this_day_sell_goods = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL, 'is_delete' => 0])->andWhere(['>=', 'created_at', $current_before_day_time])->andWhere(['<=', 'created_at', $current_time])->count(1);
        // 截止到当前时间前一天时间点的上天商品数量 开始截止时间为：$far_before_day_time ～ $current_before_day_time
        $before_day_sell_goods = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL, 'is_delete' => 0])->andWhere(['>=', 'created_at', $far_before_day_time])->andWhere(['<=', 'created_at', $current_before_day_time])->count(1);
        if ($before_day_sell_goods) {
            // 商品数量日环比（与上一天相比）当前一天：$current_before_day_time ～ $current_time，上一周：$far_before_day_time ～ $current_before_day_time
            $this_day_goods_percentage = ($this_day_sell_goods - $before_day_sell_goods) / $before_day_sell_goods;
            // 销售额日环比百分数
            $this_day_goods_basis_percent = sprintf("%01.2f", $this_day_goods_percentage * 100).'%';
        } else {
            $this_day_goods_basis_percent = '-';
        }
        /***** 商品总数 END *****/
        
        if (get_mch_id() == 0) {
            // 用户总量
            $user_total_count = User::find()->where(['store_id' => $this->store_id, 'is_delete' => User::USER_NOT_DELETE])->count(1);
            // 用户今日新增
            $this_time_user_count = User::find()->where(['store_id' => $this->store_id, 'is_delete' => User::USER_NOT_DELETE])->andWhere(['>=', 'created_at', strtotime(date('Y-m-d 00:00:00', time()))])->andWhere(['<=', 'created_at', $current_time])->count(1);

        }
        // 订单总数
        $order_total_count = Order::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'is_pay' => Order::IS_PAY_TRUE])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->count(1);
        // 今日新增
        $this_time_order_count = Order::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'is_pay' => Order::IS_PAY_TRUE])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', strtotime(date('Y-m-d 00:00:00', time()))])->andWhere(['<=', 'pay_time', $current_time])->count(1);


        // 最近10天的新增数量
        $user_day_data = [];
        $order_day_data = [];
        $mch_order_money = [];
        for ($i = 1; $i <= 10; $i++) {
            $date_start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-". $i ." day", $current_time)));
            $date_end_time = strtotime(date('Y-m-d 23:59:59', strtotime("-". $i ." day", $current_time)));
            /***** 商户订单金额走势 *****/
            if (get_mch_id() > 0) {
                $money = Order::find()->alias('o')->where(['o.is_pay' => 1, 'mch_id' => $this->mch_id])->andWhere(['<>', 'o.trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'o.created_at', $date_start_time])->andWhere(['<=', 'o.created_at', $date_end_time]);
                $money->leftJoin(['od' => OrderDetail::tableName()], 'o.id = od.order_id');
                $day_money = $money->SUM('od.total_price');
                if (!$day_money) {
                    $day_money = '0.00';
                }
                $mch_order_money[] = [
                    'x' => date('Y-m-d', $date_start_time),
                    'y' => $day_money
                ];
            }
            /***** 用户和订单总量 START *****/
            if (get_mch_id() == 0) {
                // 用户
                $user_count = User::find()->where(['store_id' => $this->store_id, 'is_delete' => User::USER_NOT_DELETE])->andWhere(['>=', 'created_at', $date_start_time])->andWhere(['<=', 'created_at', $date_end_time])->count(1);
                $user_day_data[] = [
                    'x' => date('Y-m-d', $date_start_time),
                    'y' => $user_count
                ];
            }
            // 订单
            $order_count = Order::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'is_pay' => Order::IS_PAY_TRUE])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $date_start_time])->andWhere(['<=', 'pay_time', $date_end_time])->count(1);
            $order_day_data[] = [
                'x' => date('Y-m-d', $date_start_time),
                'y' => $order_count
            ];
        }
        /***** 用户和订单总量 END *****/


        $result = [
            'code' => 0,
            'msg' => 'success',
            'data' => [
                'sell' => [
                    'total_money' => $total_sell_money ? $total_sell_money : 0,
                    'today_sell_money' => $this_time_sell_money ? $this_time_sell_money : 0,
                    'week_sell_percent' => $this_week_sell_basis_percent,
                    'day_sell_percent' => $this_day_sell_basis_percent
                ],
                'goods' => [
                    'total_goods' => $goods_count,
                    'today_goods' => $this_time_goods,
                    'week_goods_percent' => $this_week_goods_basis_percent,
                    'day_goods_percent' => $this_day_goods_basis_percent
                ],
                'user' => [
                    'total' => $user_total_count ? $user_total_count : 0,
                    'today_user' => $this_time_user_count ? $this_time_user_count : 0,
                    'data' => $user_day_data
                ],
                'order' => [
                    'total' => $order_total_count,
                    'today_order' => $this_time_order_count,
                    'data' => $order_day_data
                ],
                'mch_order_money' => [
                    'total_money' => $total_sell_money ? $total_sell_money : 0,
                    'today_sell_money' => $this_time_sell_money ? $this_time_sell_money : 0,
                    'mch_order_money' => $mch_order_money
                ]
            ]
        ];

        return $result;

    }

    public function getOrderData() {
        $start_time = $this->start_time;
        $end_time = $this->end_time;
        $condition = false; // 天
        if ((strtotime($end_time) - strtotime($start_time)) < 24 * 3600) {
            $condition = true; // 小时
        }
        $order_day_data = [];
        if ($condition) {
            for ($i = 1; $i <= 24; $i++) {
                $date_start_time = strtotime(date('Y-m-d H:00:00', strtotime("-". $i ." hour")));
                $date_end_time = strtotime(date('Y-m-d H:59:59', strtotime("-". $i ." hour")));
                // 订单
                $order_count = Order::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'is_pay' => Order::IS_PAY_TRUE])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $date_start_time])->andWhere(['<=', 'pay_time', $date_end_time])->count(1);
                $order_day_data[] = [
                    'x' => date('Y-m-d H:i:s', $date_start_time) . "-" . date('Y-m-d H:i:s', $date_end_time),
                    'y' => $order_count
                ];
            }

        } else {
            $date = floor((strtotime($end_time) - strtotime($start_time)) / 86400);
            for ($i = 1; $i <= $date; $i++) {
                $date_start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-". $i ." day")));
                $date_end_time = strtotime(date('Y-m-d 23:59:59', strtotime("-". $i ." day")));
                // 订单
                $order_count = Order::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'is_pay' => Order::IS_PAY_TRUE])->andWhere(['<>', 'trade_status', Order::ORDER_FLOW_CANCEL])->andWhere(['>=', 'pay_time', $date_start_time])->andWhere(['<=', 'pay_time', $date_end_time])->count(1);
                $order_day_data[] = [
                    'x' => date('Y-m-d H:i:s', $date_start_time) . "-" . date('Y-m-d H:i:s', $date_end_time),
                    'y' => $order_count
                ];
            }
        }
        $list = [];
        if (get_mch_id() == 0) {
            $o_table = Order::tableName();
            $m_table = Mch::tableName();
            $sql = "select {$m_table}.name, (SELECT count(*) as count FROM  {$o_table} where {$o_table}.mch_id = {$m_table}.id and {$o_table}.store_id = {$this->store_id} and {$o_table}.is_pay = 1 and {$o_table}.trade_status != 3) as count  FROM {$m_table} ORDER BY count desc LIMIT 7";
            $list = \Yii::$app->db->createCommand($sql)->queryAll();
        }
        return [
            'code' => 0,
            'msg' => 'success',
            'data' => $order_day_data,
            'shop_list' => $list
        ];
    }


    /**
     * 商品销量列表
     * @return array
     */
    public function GoodsSearch()
    {
        $query = Goods::find()->where(['store_id' => $this->store_id, 'mch_id' => $this->mch_id, 'status' => Goods::STATUS_NORMAL])->select('id, name');

        $list = pagination_make($query);
        $listArray = $list['list'];
        foreach ($listArray as $k => $v) {
            // 访客数
            $fangke = UserViews::find()->where(['goods_id' => $v['id']]);
            $fangke = $fangke->SUM('visitors');
            if (!$fangke) {
                $fangke = 0;
            }

            // 浏览量
            $views = UserViews::find()->where(['goods_id' => $v['id']]);
            $views = $views->SUM('views');
            if (!$views) {
                $views = 0;
            }

            // 加购数
            $cart = Cart::find()->where(['goods_id' => $v['id'], 'mch_id' => $this->mch_id]);
            $cart = $cart->count();
            if (!$cart) {
                $cart = 0;
            }

            // 付款人数
            $paynum = Order::find()->alias('o')->where(['o.is_pay' => 1, 'mch_id' => $this->mch_id])->andWhere(['<>', 'o.trade_status', Order::ORDER_FLOW_CANCEL]);
            $paynum->leftJoin(['od' => OrderDetail::tableName()], 'o.id = od.order_id');
            $paynum->andWhere(['od.goods_id' => $v['id']]);
            $paynum = $paynum->count();
            if (!$paynum) {
                $paynum = 0;
            }

            // 付款金额
            $jine = Order::find()->alias('o')->where(['o.is_pay' => 1, 'mch_id' => $this->mch_id])->andWhere(['<>', 'o.trade_status', Order::ORDER_FLOW_CANCEL]);
            $jine->leftJoin(['od' => OrderDetail::tableName()], 'o.id = od.order_id');
            $jine->andWhere(['od.goods_id' => $v['id']]);
            $jine = $jine->SUM('od.total_price');
            if (!$jine) {
                $jine = '0.00';
            }

            $listArray[$k]['cart_data'] = [];
            // 近7天加购数
            for ($i = 1; $i <= 7; $i++) {
                $date_start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-". $i ." day")));
                $date_end_time = strtotime(date('Y-m-d 23:59:59', strtotime("-". $i ." day")));
                $cart_count = Cart::find()->where(['goods_id' => $v['id'], 'mch_id' => $this->mch_id])->andWhere(['>=', 'created_at', $date_start_time])->andWhere(['<=', 'created_at', $date_end_time])->count(1);
                $listArray[$k]['cart_data'][] = [
                    'x' => date('Y-m-d H:i:s', $date_start_time) . "-" . date('Y-m-d H:i:s', $date_end_time),
                    'y' => $cart_count
                ];
            }

            $listArray[$k]['sell_data'] = [];
            // 近7天销量
            for ($i = 1; $i <= 7; $i++) {
                $date_start_time = strtotime(date('Y-m-d 00:00:00', strtotime("-". $i ." day")));
                $date_end_time = strtotime(date('Y-m-d 23:59:59', strtotime("-". $i ." day")));
                $query = Order::find()->alias('o')->where(['o.is_pay' => 1, 'mch_id' => $this->mch_id])->andWhere(['<>', 'o.trade_status', Order::ORDER_FLOW_CANCEL]);
                $query->leftJoin(['od' => OrderDetail::tableName()], 'o.id = od.order_id');
                $query->andWhere(['od.goods_id' => $v['id']]);
                $order_count = $query->andWhere(['>=', 'o.created_at', $date_start_time])->andWhere(['<=', 'o.created_at', $date_end_time])->count(1);
                $listArray[$k]['sell_data'][] = [
                    'x' => date('Y-m-d H:i:s', $date_start_time) . "-" . date('Y-m-d H:i:s', $date_end_time),
                    'y' => $order_count
                ];
            }
            $listArray[$k]['fangke'] = $fangke;
            $listArray[$k]['views'] = $views;
            $listArray[$k]['cart'] = $cart;
            $listArray[$k]['paynum'] = $paynum;
            $listArray[$k]['jine'] = $jine;
        }
        $list['totalCount'] = count($listArray);
        $list['data'] = $listArray;
        unset($list['list']);
        return [
            'code' => 0,
            'msg' => 'success',
            'data' => $list
        ];
    }

    public function operations() {
        $this->model->store_id = $this->store_id;
        $this->model->mch_id = $this->mch_id;
        $this->model->text = !$this->text ? '{}' : Json::encode($this->text);
        if ($this->model->isNewRecord) {
            $this->model->created_at = time();
        }
        if (!$this->model->save()) {
            return [
                'code' => 1,
                'msg' => $this->model->errors[0]
            ];
        } else {
            return [
                'code' => 0,
                'msg' => '保存成功'
            ];
        }
    }
}