lachang/app/store/model/User.php

<?php
// +----------------------------------------------------------------------
// | 萤火商城系统 [ 致力于通过产品和服务，帮助商家高效化开拓市场 ]
// +----------------------------------------------------------------------
// | Copyright (c) 2017~2021 https://www.yiovo.com All rights reserved.
// +----------------------------------------------------------------------
// | Licensed 这不是一个自由软件，不允许对程序代码以任何形式任何目的的再发行
// +----------------------------------------------------------------------
// | Author: 萤火科技 <admin@yiovo.com>
// +----------------------------------------------------------------------
declare (strict_types=1);

namespace app\store\model;

use app\common\enum\order\PayStatus as PayStatusEnum;
use app\common\model\User as UserModel;

use app\common\model\user\CommissionsDetail;
use app\store\model\UserOauth as UserOauthModel;
use app\store\model\user\GradeLog as GradeLogModel;
use app\store\model\user\PointsLog as PointsLogModel;
use app\store\model\user\BalanceLog as BalanceLogModel;
use app\store\service\store\User as StoreUserService;
use app\common\enum\user\balanceLog\Scene as SceneEnum;
use app\common\enum\user\grade\log\ChangeType as ChangeTypeEnum;
use app\common\library\helper;
use app\common\model\Shops;
use app\api\model\User as UserApiModel;
use app\store\model\user\BonusHistory;
use think\model\relation\HasMany;

/**
 * 用户模型
 * Class User
 * @package app\store\model
 */
class User extends UserModel
{
    // 充值类型枚举: 余额
    const RECHARGE_TYPE_BALANCE = 'balance';

    // 充值类型枚举: 积分
    const RECHARGE_TYPE_POINTS = 'points';

    // 追加字段
    protected $append = ['role_text'];

    /**
     * 获取当前用户总数
     * @param array $param 查询参数
     * @return int|string
     */
    public function getUserTotal(array $param = [])
    {
        // 检索查询条件
        $filter = $this->getUserTotalFilter($param);
        // 查询结果
        return $this->where($filter)
            ->where('is_delete', '=', '0')
            ->count();
    }

    /**
     * 获取当前用户总数的查询条件
     * @param array $param
     * @return array
     */
    private function getUserTotalFilter(array $param = [])
    {
        // 默认查询参数
        $params = $this->setQueryDefaultValue($param, [
            'date' => null,         // 注册日期 如: 2020-08-01
            'isConsume' => null,    // 是否已消费
        ]);
        // 检索查询条件
        $filter = [];
        if (!is_null($params['date'])) {
            $startTime = strtotime($params['date']);
            $filter[] = ['create_time', '>=', $startTime];
            $filter[] = ['create_time', '<', $startTime + 86400];
        }
        if (is_bool($params['isConsume'])) {
            $filter[] = ['pay_money', $params['isConsume'] ? '>' : '=', 0];
        }
        return $filter;
    }

    /**
     * 获取用户列表
     * @param array $param
     * @return \think\Paginator
     * @throws \think\db\exception\DbException
     */
    public function getList(array $param = [])
    {
        // 检索查询条件
        $filter = $this->getFilter($param);
        $shop_users = [];
        
        // 获取用户列表
        $query = $this->with(['avatar', 'grade', 'shops', 'bindShops', 'upperUser'])
            ->alias('users')
            ->field('users.*')
            // ->leftJoin('shops', 'shops.shop_id = users.shop_id')
            ->leftJoin('user', 'user.user_id = users.upper_user_id')
            ->where($filter)
            ->where('users.is_delete', '=', '0');
        if (!empty($param['shop_name'])) {
            $shop_users = Shops::where('shop_name', 'like', "%{$param['shop_name']}%")->column('shop_id');
            if (!empty($shop_users)) {
                $query->where(function($query) use($shop_users) {
                    $query->whereOr('users.shop_id', 'in', $shop_users)->whereOr('users.bind_shop_id', 'in', $shop_users);
                });
            }
        }
        
        return $query->order(['users.create_time' => 'desc'])
            ->paginate(15)
            ->each(function ($item) {
                $item->order_paid_count = Order::where('user_id',$item->user_id)
                    ->where('pay_status',PayStatusEnum::SUCCESS)->count(); // 消费订单数
                $item->order_paid_amount = Order::where('user_id',$item->user_id)
                    ->where('pay_status',PayStatusEnum::SUCCESS)->sum('pay_price'); // 消费金额
                $item->promote_user_count = UserApiModel::countSubordinates($item->user_id, $item->shop_id, $item->role); // 直推人数
/*                $item->promoter_order_count = OrderGoods::alias('order_goods')
                                ->leftJoin('order','order_goods.order_id=order.order_id')
                                ->where('order_goods.staff_user_id',$item->user_id)
                                ->where('order.pay_status',PayStatusEnum::SUCCESS)->count(); // 推广订单*/

                $item->promoter_order_count = Order::alias('order')
                    ->where('order.staff_user_id',$item->user_id)
                    ->where('order.pay_status',PayStatusEnum::SUCCESS)
                    ->count(); // 推广订单

                //todo 销售额 = 实付+米卡抵扣
/*                $item->promote_sale_sum = (OrderGoods::alias('order_goods')
                    ->leftJoin('order','order_goods.order_id=order.order_id')
                    ->where('order_goods.staff_user_id',$item->user_id)->where('order.pay_status',PayStatusEnum::SUCCESS)
                    ->field('sum(order_goods.total_pay_price+order_goods.rice_card_money) as tm')->find())->tm??0;*/
                $tempSaleSum = Order::alias('order')
                    ->where('order.staff_user_id',$item->user_id)
                    ->where('order.pay_status',PayStatusEnum::SUCCESS)
                    ->field('sum(order.pay_price+order.rice_card_money) as tm')
                    ->find();
                $item->promote_sale_sum = $tempSaleSum->tm??0;

                $item->fc_yj_amount = $item->can_withdraw_money; // 总佣金
                $item->shop_name = $item->shops->shop_name ?? ($item->bindShops->shop_name ?? '');
                $promoter_mobile = '';
                if (in_array($item->role, [2,3])) {
                    $promoter_name = '总店';
                } elseif ($item->role == 4) { // 店员
                    $promoter_name = $item->shops->shop_name ?? '';
                    $promoter_mobile = $item->shops->contact_mobile??'';

                } else {
                    $promoter_name = $item->upperUser->nick_name ?? '';
                    $promoter_mobile = $item->upperUser->mobile??''; 
                }
                $item->promoter_name = $promoter_name;
                $item->promoter_mobile = $promoter_mobile;

                unset($item['shops'],$item['bindShops'],$item['upperUser']);
            });
    }

    /**
     * 获取查询条件
     * @param array $param
     * @return array
     */
    private function getFilter(array $param = [])
    {
        // 默认查询条件
        $params = $this->setQueryDefaultValue($param, [
            'search' => '',     // 微信昵称
            'mobile' => '',     // 手机号
            'gender' => -1,     // 用户性别
            'grade' => 0,       // 用户等级
            'shop_name' => '',  // 门店名称
            'upper_user' => '',// 推荐人手机号
        ]);
        // 检索查询条件
        $filter = [];
        // 微信昵称
        !empty($params['search']) && $filter[] = ['users.nick_name', 'like', "%{$params['search']}%"];
        // 手机号
        !empty($params['mobile']) && $filter[] = ['users.mobile', 'like', "%{$params['mobile']}%"];
        // 门店名称
        // !empty($params['shop_name']) && $filter[] = ['shops.shop_name', 'like', "%{$params['shop_name']}%"];
        // 推荐人手机号
        !empty($params['upper_user']) && $filter[] = ['user.mobile', 'like', "%{$params['upper_user']}%"];
        // 用户性别
        $params['gender'] > -1 && $filter[] = ['users.gender', '=', (int)$params['gender']];
        // 用户等级
        $params['grade'] > 0 && $filter[] = ['users.grade_id', '=', (int)$params['grade']];
        // 起止时间
        if (!empty($params['betweenTime'])) {
            $times = between_time($params['betweenTime']);
            $filter[] = ['users.create_time', '>=', $times['start_time']];
            $filter[] = ['users.create_time', '<', $times['end_time'] + 86400];
        }
        return $filter;
    }

    /**
     * 获取用户信息
     * @param $where
     * @param array $with
     * @return array|null|static
     */
    public static function detail($where, $with = [])
    {
        $filter = ['is_delete' => 0];
        if (is_array($where)) {
            $filter = array_merge($filter, $where);
        } else {
            $filter['user_id'] = (int)$where;
        }
        return static::get($filter, $with);
    }

    /**
     * 删除用户
     * @return bool|mixed
     */
    public function setDelete()
    {
        return $this->transaction(function () {
            // 将第三方用户信息记录标记删除
            UserOauthModel::updateBase(['is_delete' => 1], [
                ['user_id', '=', $this['user_id']]
            ]);
            // 标记为已删除
            return $this->save(['is_delete' => 1]);
        });
    }

    /**
     * 用户充值
     * @param string $target 充值类型
     * @param array $data 表单数据
     * @return bool
     */
    public function recharge(string $target, array $data)
    {
        // 当前操作人用户名
        $storeUserName = StoreUserService::getLoginInfo()['user']['user_name'];
        if ($target === self::RECHARGE_TYPE_BALANCE) {
            return $this->rechargeToBalance($storeUserName, $data['balance']);
        } elseif ($target === self::RECHARGE_TYPE_POINTS) {
            return $this->rechargeToPoints($storeUserName, $data['points']);
        }
        return false;
    }

    /**
     * 用户充值：余额
     * @param string $storeUserName
     * @param array $data
     * @return bool
     */
    private function rechargeToBalance(string $storeUserName, array $data)
    {
        if (!isset($data['money']) || $data['money'] === '' || $data['money'] < 0) {
            $this->error = '请输入正确的金额';
            return false;
        }
        // 判断充值方式，计算最终金额
        if ($data['mode'] === 'inc') {
            $diffMoney = $data['money'];
        } elseif ($data['mode'] === 'dec') {
            $diffMoney = -$data['money'];
        } else {
            $diffMoney = helper::bcsub($data['money'], $this['balance']);
        }
        // 更新记录
        $this->transaction(function () use ($storeUserName, $data, $diffMoney) {
            // 更新账户余额
            static::setIncBalance((int)$this['user_id'], (float)$diffMoney);
            // 新增余额变动记录
            BalanceLogModel::add(SceneEnum::ADMIN, [
                'user_id' => $this['user_id'],
                'money' => (float)$diffMoney,
                'remark' => $data['remark'],
            ], [$storeUserName]);
        });
        return true;
    }

    /**
     * 用户充值：积分
     * @param string $storeUserName
     * @param array $data
     * @return bool
     */
    private function rechargeToPoints(string $storeUserName, array $data)
    {
        if (!isset($data['value']) || $data['value'] === '' || $data['value'] < 0) {
            $this->error = '请输入正确的积分数量';
            return false;
        }
        // 判断充值方式，计算最终积分
        if ($data['mode'] === 'inc') {
            $diffMoney = $data['value'];
        } elseif ($data['mode'] === 'dec') {
            $diffMoney = -$data['value'];
        } else {
            $diffMoney = $data['value'] - $this['points'];
        }
        // 更新记录
        $this->transaction(function () use ($storeUserName, $data, $diffMoney) {
            // 更新账户积分
            $this->setInc($this['user_id'], 'points', $diffMoney);
            // 新增积分变动记录
            PointsLogModel::add([
                'user_id' => $this['user_id'],
                'value' => $diffMoney,
                'describe' => "后台管理员 [{$storeUserName}] 操作",
                'remark' => $data['remark'],
            ]);
        });
        return true;
    }

    /**
     * 修改用户等级
     * @param array $data
     * @return mixed
     */
    public function updateGrade(array $data)
    {
        // 变更前的等级id
        $oldGradeId = $this['grade_id'];
        return $this->transaction(function () use ($oldGradeId, $data) {
            // 更新用户的等级
            $status = $this->save(['grade_id' => $data['grade_id']]);
            // 新增用户等级修改记录
            if ($status) {
                (new GradeLogModel)->record([
                    'user_id' => $this['user_id'],
                    'old_grade_id' => $oldGradeId,
                    'new_grade_id' => $data['grade_id'],
                    'change_type' => ChangeTypeEnum::ADMIN_USER,
                    'remark' => $data['remark']
                ]);
            }
            return $status !== false;
        });
    }

    /**
     * 消减用户的实际消费金额
     * @param int $userId
     * @param float $expendMoney
     * @return mixed
     */
    public function setDecUserExpend(int $userId, float $expendMoney)
    {
        return $this->setDec(['user_id' => $userId], 'expend_money', $expendMoney);
    }

    /**
     * 员工业绩统计
     * @param $shopId
     * @param $from
     * @param $to
     * @return array
     * @throws \think\db\exception\DbException
     */
    public function staffPerformanceList($shopId,$from,$to): array
    {
        $res =  self::field('user_id,role,nick_name,mobile,avatar_id,bind_shop_status,create_time')
            ->with('avatar')
            ->withCount(['straightUsersCnt'=>function($query) use ($from,$to){
                $query->whereBetweenTime('create_time',$from,$to);
            }])
            ->withSum(['commissionTotal'=>function($query) use ($from,$to){
                $query->where('clearing_status',1)->whereBetweenTime('order_create_time',$from,$to);
            }],'clearing_money')
            ->where('role', '>', User::SHOP_BOSS)//11-27除去店老板
            ->where('role', '<', User::COMMISSION_USER)
            ->where('shop_id',$shopId)->paginate(10)->toArray();


        foreach ($res['data'] as $key=>&$item){
            $item['rec_orders_cnt_sum'] = "0.00";
            $item['rec_orders_cnt_count'] = CommissionsDetail::where('user_id',$item['user_id'])
                ->where('shop_id',$shopId)
                ->whereBetweenTime('order_create_time',$from,$to)
                ->count();

            $item['rec_orders_cnt_sum'] = OrderGoods::sumShopGiveOutOrder($shopId,$from,$to,$item['user_id']);

            //todo 后面奖励金需要细化到门店 达量奖励金
            $bonusAmount = BonusHistory::where('clearing_status', 1)
                ->where('shop_id', $shopId)
                ->where('user_id', $item['user_id'])
                ->sum('bonus_money');
            $item['bonus_amount'] = helper::bcsub($bonusAmount, 0, 2);

            $is_virtual = Shops::where('shop_id', $shopId)->value('is_virtual');
            $qrcode = '';
            if ($is_virtual == Shops::SHOP_TYPE_VIRTUAL && $item['role'] == User::SHOP_SELLER) {
                // 虚拟门店小程序码
                if ($item['user_id']) {
                    $path = "/pages/tabBar/index/index?apple_s_id={$item['user_id']}&shaer=xcx&scan=1";
                    $qrcode = request()->domain()."/api/mp_wx/qrcode?path=".urlencode($path);
                }
            }
            $item['qrcode'] = $qrcode;
        }
        return $res;
    }

    /**
     * Notes:获取用户佣金明细列表
     * Author: zhangs
     * DateTime: 2021/10/11 11:49
     * @param $userId
     * @return mixed
     */
    public function userCommissionDetailList($userId)
    {
        return CommissionsDetail::alias('cd')
            ->field('cd.*,order.order_no')
            ->leftJoin('order', 'order.order_id=cd.order_id')
            ->where('cd.user_id', $userId)
            ->where('cd.clearing_status', 1) // 已结算
            ->order('cd.update_time', 'desc')
            ->paginate();
    }

    public function straightUsersCnt(){
        return $this->hasMany(User::class,'upper_user_id','user_id');
    }

    public function recOrdersCnt(){
        return $this->hasMany(OrderGoods::class,'staff_user_id','user_id');
    }

    public function commissionTotal(){
        return $this->hasMany(CommissionsDetail::class,'user_id','user_id');
    }



    public static function getUserByMobile($mobile){
        $m =  User::field('role')->where('mobile',$mobile)->find();
        return $m?$m->getAttr('role'):0;
    }

    /**
     * 员工列表
     * @param $shopId
     * @return array
     * @throws \think\db\exception\DbException
     */
    public static function staffList($shopId){
        return User::field('user_id,nick_name,mobile,role')->where('shop_id',$shopId)
            ->where('role',User::SHOP_SELLER)->paginate(15)->toArray();
    }

    /**
     * 获取店老板
     * @param $shopId
     * @return array|null
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function getBoss($shopId){
        $bossUserId = \app\store\model\Shops::where('shop_id',$shopId)->find();
        $bossUserId = $bossUserId->boss_user_id??0;
        $boss =  User::field('user_id,nick_name,mobile,role')->where('user_id',$bossUserId)->find();
        return $boss?$boss->toArray():null;
    }

    public static function getBossCommission($shopId){
        $bossUserId = \app\store\model\Shops::where('shop_id',$shopId)->find();
        $bossUserId = $bossUserId->boss_user_id??0;
        $boss =  User::field('user_id,nick_name,mobile,role,can_withdraw_money')->where('user_id',$bossUserId)->find();
        return $boss?$boss->can_withdraw_money:null;
    }

    /**
     * 分销员佣金统计
     * @return \think\Paginator
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function financeCommerStat($params){
        if (!empty($params['betweenTime'])){
            $times = between_time($params['betweenTime']);
        }else{
/*            $times['end_time'] = strtotime(date('Y-m-01 00:00:00')) - 1 ;//上月的最后一秒
            $times['start_time'] =strtotime(date('Y-m-01 00:00:00',strtotime('-1 month')));*/

            $times['end_time']  = time();
            $times['start_time'] = 1590385853;
        }
        $where[] = ['role','=',User::COMMISSION_USER];
        if (!empty($params['userId']) && $params['userId']>0)$where[] = ['user_id','=', $params['userId']];
        if (!empty($params['sellerGrade']) && $params['sellerGrade']>0)$where[] = ['seller_grade','=', $params['sellerGrade']];
        if (!empty($params['nickName']))$where[] = ['nick_name', 'like','%'.$params['nickName'].'%'];
        if (!empty($params['mobile']))$where[] = ['mobile', 'like','%'.$params['mobile'].'%'];

        return User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->paginate(15)
            ->each(function(&$item) use ($times){
               $details =  CommissionsDetail::where('user_id',$item['user_id'])
                   ->whereBetweenTime('order_create_time',$times['start_time'],$times['end_time'])
                   ->where('clearing_status','<',2)
                   ->select();
               $clearingMoneySum  = $clearingMoneyFirstSum = $clearingMoneySecondSum//佣金
                   = $clearedMoney = $unclearedMoney //已结未结佣金
                   = $orderCount = $orderFirstCount = $orderSecondCount //订单数
                   = $orderSaleVolume = $orderSaleVolumeFirst = $orderSaleVolumeSecond//销售额
                   = 0;
               if (!$details->isEmpty()){
                   $orderCount = count($details);
                   foreach ($details as $d){
                       $clearingMoneySum += $d->clearing_money;
                       $orderSaleVolume += $d->order_sale_volume;
                        if ($d->commission_level == 1){
                            $clearingMoneyFirstSum += $d->clearing_money;
                            $orderFirstCount += 1;
                            $orderSaleVolumeFirst += $d->order_sale_volume;
                        }
                       if ($d->commission_level == 2){
                           $clearingMoneySecondSum += $d->clearing_money;
                           $orderSecondCount += 1;
                           $orderSaleVolumeSecond += $d->order_sale_volume;
                       }
                       if ($d->clearing_status == 0){
                           $unclearedMoney += $d->clearing_money;
                       }
                       if ($d->clearing_status == 1){
                           $clearedMoney += $d->clearing_money;
                       }
                   }
               }
                $item['clearingMoneySum']       = helper::bcadd($clearingMoneySum,0,2);
                $item['clearingMoneyFirstSum']  = helper::bcadd($clearingMoneyFirstSum,0,2);
                $item['clearingMoneySecondSum'] = helper::bcadd($clearingMoneySecondSum,0,2);
                $item['clearedMoney']           = helper::bcadd($clearedMoney,0,2);
                $item['unclearedMoney']         = helper::bcadd($unclearedMoney,0,2);
                $item['orderCount']             = $orderCount;
                $item['orderFirstCount']        = $orderFirstCount;
                $item['orderSecondCount']       = $orderSecondCount;
                $item['orderSaleVolume']        = helper::bcadd($orderSaleVolume,0,2);
                $item['orderSaleVolumeFirst']   = helper::bcadd($orderSaleVolumeFirst,0,2);
                $item['orderSaleVolumeSecond']  = helper::bcadd($orderSaleVolumeSecond,0,2);
        });
    }

    /**
     * 分销员佣金统计
     * @return \think\Paginator
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function commerStat($params){
        if (!empty($params['betweenTime'])){
            $times = between_time($params['betweenTime']);
        }else{
            $times['end_time']   = strtotime(date('Y-m-01 00:00:00',time())) - 1;
            $times['start_time'] = strtotime(date('Y-m-01 00:00:00',strtotime('-1 month')));
        }
        $where[] = ['role','=',User::COMMISSION_USER];
        //if (!empty($params['userId']) && $params['userId']>0)$where[] = ['user_id','=', $params['userId']];
        if (!empty($params['nickName'])){
            $where[] = ['nick_name|user_id', 'like','%'.$params['nickName'].'%'];
        }
        if (!empty($params['sellerGrade']) && $params['sellerGrade']>0)$where[] = ['seller_grade','=', $params['sellerGrade']];
        if (!empty($params['mobile']))$where[] = ['mobile', 'like','%'.$params['mobile'].'%'];

        return User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->paginate(15)
            ->each(function(&$item) use ($times){
                $details =  CommissionsDetail::where('user_id',$item['user_id'])
                    ->whereBetweenTime('order_create_time',$times['start_time'],$times['end_time'])
                    ->where('clearing_status','<',2)
                    ->select();
                $clearedMoneySum//总基本佣金
                    = $clearedMoneyFirstSum//一级佣金
                    = $clearedMoneySecondSum//二级佣金
                    = $orderCount//总订单数
                    = $orderFirstCount //一级订单数
                    = $orderSecondCount //二级订单数
                    = $orderTotalPrices//订单总金额
                    = $orderGoodsPrices//订单商品总金额
                    = $expressPrices//运费
                    = $couponPrices//平台优惠券
                    = $actDiscountPrices//活动优惠金额
                    = $payPrices//实付金额
                    = 0;
                if (!$details->isEmpty()){
                    $details = $details->toArray();
                    $orderCount = count($details);
                    $orderIds = array_column($details,'order_id');

                    $orders = Order::whereIn('order_id',$orderIds)
                        ->field('order_price,total_price,express_price,coupon_money,pay_price,rice_card_money,update_price')
                        ->select()->toArray();

                    //$orderPriceArr = array_column($orders,'order_price');
                    //$orderTotalPrices = array_sum($orderPriceArr);

                    $orderGoodsPricesArr = array_column($orders,'total_price');
                    $orderGoodsPrices = array_sum($orderGoodsPricesArr);

                    $expressPricesArr = array_column($orders,'express_price');
                    $expressPrices = array_sum($expressPricesArr);

                    $couponPricesArr = array_column($orders,'coupon_money');
                    $couponPrices = array_sum($couponPricesArr);

                    $payPricesArr = array_column($orders,'pay_price');
                    $payPrices = array_sum($payPricesArr);

                    $riceCardPricesArr = array_column($orders,'rice_card_money');
                    $riceCardPrices = array_sum($riceCardPricesArr);

                    $actDiscountPrices = $orderGoodsPrices - $payPrices - $riceCardPrices;

                    //$orderTotalPrices = $payPrices + $riceCardPrices;
                    $orderTotalPrices = $orderGoodsPrices + $expressPrices;


                    foreach ($details as $d){
                        $clearedMoneySum += $d['clearing_money'];

                        if ($d['commission_level'] == 1){
                            $clearedMoneyFirstSum += $d['clearing_money'];
                            $orderFirstCount += 1;
                        }
                        if ($d['commission_level'] == 2){
                            $clearedMoneySecondSum += $d['clearing_money'];
                            $orderSecondCount += 1;
                        }

                    }
                }
                $item['orderCount']             = $orderCount;
                $item['orderFirstCount']        = $orderFirstCount;
                $item['orderSecondCount']       = $orderSecondCount;

                $item['orderTotalPrices']       = $orderTotalPrices;
                $item['orderGoodsPrices']       = $orderGoodsPrices;
                $item['expressPrices']          = $expressPrices;
                $item['couponPrices']           = $couponPrices;
                $item['payPrices']              = $payPrices;
                $item['actDiscountPrices']      = helper::bcadd($actDiscountPrices,0,2);

                $item['clearedMoneySum']        = helper::bcadd($clearedMoneySum,0,2);
                $item['clearedMoneySecondSum']  = helper::bcadd($clearedMoneySecondSum,0,2);
                $item['clearedMoneyFirstSum']   = helper::bcadd($clearedMoneyFirstSum,0,2);

            });
    }

    /**
     * 分销员佣金统计
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function exportCommerStat($params){
        if (!empty($params['betweenTime'])){
            $times = between_time($params['betweenTime']);
        }else{
            $times['end_time']   = strtotime(date('Y-m-01 00:00:00',time())) - 1;
            $times['start_time'] = strtotime(date('Y-m-01 00:00:00',strtotime('-1 month')));
        }
        $where[] = ['role','=',User::COMMISSION_USER];
        //if (!empty($params['userId']) && $params['userId']>0)$where[] = ['user_id','=', $params['userId']];
        if (!empty($params['nickName'])){
            $where[] = ['nick_name|user_id', 'like','%'.$params['nickName'].'%'];
        }
        if (!empty($params['sellerGrade']) && $params['sellerGrade']>0)$where[] = ['seller_grade','=', $params['sellerGrade']];
        if (!empty($params['mobile']))$where[] = ['mobile', 'like','%'.$params['mobile'].'%'];

        $lists = User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->paginate(15)
            ->each(function(&$item) use ($times){
                $details =  CommissionsDetail::where('user_id',$item['user_id'])
                    ->whereBetweenTime('order_create_time',$times['start_time'],$times['end_time'])
                    ->where('clearing_status','=',1)
                    ->select();
                $clearedMoneySum//总基本佣金
                    = $clearedMoneyFirstSum//一级佣金
                    = $clearedMoneySecondSum//二级佣金
                    = $orderCount//总订单数
                    = $orderFirstCount //一级订单数
                    = $orderSecondCount //二级订单数
                    = $orderTotalPrices//订单总金额
                    = $orderGoodsPrices//订单商品总金额
                    = $expressPrices//运费
                    = $couponPrices//平台优惠券
                    = $actDiscountPrices//活动优惠金额
                    = $payPrices//实付金额
                    = 0;
                if (!$details->isEmpty()){
                    $details = $details->toArray();
                    $orderCount = count($details);
                    $orderIds = array_column($details,'order_id');

                    $orders = Order::whereIn('order_id',$orderIds)
                        ->field('order_price,total_price,express_price,coupon_money,pay_price,rice_card_money,update_price')
                        ->select()->toArray();

                    //$orderPriceArr = array_column($orders,'order_price');
                    //$orderTotalPrices = array_sum($orderPriceArr);

                    $orderGoodsPricesArr = array_column($orders,'total_price');
                    $orderGoodsPrices = array_sum($orderGoodsPricesArr);

                    $expressPricesArr = array_column($orders,'express_price');
                    $expressPrices = array_sum($expressPricesArr);

                    $couponPricesArr = array_column($orders,'coupon_money');
                    $couponPrices = array_sum($couponPricesArr);

                    $payPricesArr = array_column($orders,'pay_price');
                    $payPrices = array_sum($payPricesArr);

                    $riceCardPricesArr = array_column($orders,'rice_card_money');
                    $riceCardPrices = array_sum($riceCardPricesArr);

                    $actDiscountPrices = $orderGoodsPrices - $payPrices - $riceCardPrices;

                    //$orderTotalPrices = $payPrices + $riceCardPrices;
                    $orderTotalPrices = $orderGoodsPrices + $expressPrices;

                    foreach ($details as $d){
                        $clearedMoneySum += $d['clearing_money'];

                        if ($d['commission_level'] == 1){
                            $clearedMoneyFirstSum += $d['clearing_money'];
                            $orderFirstCount += 1;
                        }
                        if ($d['commission_level'] == 2){
                            $clearedMoneySecondSum += $d['clearing_money'];
                            $orderSecondCount += 1;
                        }

                    }
                }
                $item['orderCount']             = $orderCount;
                $item['orderFirstCount']        = $orderFirstCount;
                $item['orderSecondCount']       = $orderSecondCount;

                $item['orderTotalPrices']       = $orderTotalPrices;
                $item['orderGoodsPrices']       = $orderGoodsPrices;
                $item['expressPrices']          = $expressPrices;
                $item['couponPrices']           = $couponPrices;
                $item['payPrices']              = $payPrices;
                $item['actDiscountPrices']      = helper::bcadd($actDiscountPrices,0,2);

                $item['clearedMoneySum']        = helper::bcadd($clearedMoneySum,0,2);
                $item['clearedMoneySecondSum']  = helper::bcadd($clearedMoneySecondSum,0,2);
                $item['clearedMoneyFirstSum']   = helper::bcadd($clearedMoneyFirstSum,0,2);

            });

        $data['header'] = ['用户ID', '推荐官昵称', '推荐官手机号','总订单数','一级订单数','二级订单数','订单总金额（含运费）',
                           '订单商品总金额','运费','平台优惠券','活动优惠','实付金额（商品+运费）','一级佣金','二级佣金','总基本佣金'];
        $data['filename'] = '推荐官基础数据';
        $data['data'] = [];

        foreach ($lists as $k=>$arr){
            $new_list['user_id']                = $arr['user_id'];
            $new_list['nick_name']              = $arr['nick_name'];
            $new_list['mobile']                 = $arr['mobile'];
            $new_list['orderCount']             = $arr['orderCount'];
            $new_list['orderFirstCount']        = $arr['orderFirstCount'];
            $new_list['orderSecondCount']       = $arr['orderSecondCount'];

            $new_list['orderTotalPrices']       = $arr['orderTotalPrices'];
            $new_list['orderGoodsPrices']       = $arr['orderGoodsPrices'];
            $new_list['expressPrices']          = $arr['expressPrices'];
            $new_list['couponPrices']           = $arr['couponPrices'];
            $new_list['actDiscountPrices']      = $arr['actDiscountPrices'];
            $new_list['payPrices']              = $arr['payPrices'];

            $new_list['clearedMoneySum']        = $arr['clearedMoneySum'];
            $new_list['clearedMoneySecondSum']  = $arr['clearedMoneySecondSum'];
            $new_list['clearedMoneyFirstSum']   = $arr['clearedMoneyFirstSum'];
            $data['data'][] = $new_list;
        }
        return $data;
    }


    /**
     * 分销员佣金统计
     * @param $params
     * @param bool $dataCenter
     * @return \think\Paginator
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function financeCommerBonusStat($params,$dataCenter = false){

        list($where,$times) =$this->getBonusFilter($params,$dataCenter);

        return User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->paginate(15)
            ->each(function(&$item) use ($times){
                $details =  BonusHistory::where('user_id',$item['user_id'])
                    ->where('clearing_status',1)
                    ->whereBetweenTime('year_month',$times['start_time'],$times['end_time'])
                    ->field('sum(sales_volume) as sales_volumes,sum(order_count) as order_cnt,sum(bonus_money) as bonus_money')
                    ->find();
                $item['sales_volumes'] = $details->sales_volumes??0;
                $item['order_cnt'] = $details->order_cnt??0;
                $item['bonus_money'] = $details->bonus_money??0;
            });
    }

    private function getBonusFilter($params,$dataCenter = false){
        if (!empty($params['betweenTime'])){
            $times = between_time($params['betweenTime']);
            $times['start_time'] = intval(date('Ym',$times['start_time']));
            $times['end_time'] = intval(date('Ym',$times['end_time']));

        }else{
            $times['end_time'] = intval(date('Ym')) ;//上月的最后一秒
            $times['start_time'] =intval(date('Ym',strtotime('-1 month')));
        }
        $where[] = ['role','=',User::COMMISSION_USER];
        if ($dataCenter === true && !empty($params['nickName'])){
            $where[] = ['nick_name|user_id', 'like','%'.$params['nickName'].'%'];
        }else{
            if (!empty($params['userId']) && $params['userId']>0)$where[] = ['user_id','=', $params['userId']];
            if (!empty($params['nickName']))$where[] = ['nick_name', 'like','%'.$params['nickName'].'%'];
        }
        if (!empty($params['sellerGrade']) && $params['sellerGrade']>0)$where[] = ['seller_grade','=', $params['sellerGrade']];
        if (!empty($params['mobile']))$where[] = ['mobile', 'like','%'.$params['mobile'].'%'];
        return [$where,$times];
    }

    /**
     * 奖励金统计导出
     * @param $params
     * @param false $dataCenter
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function exportCommerBonusStat($params,$dataCenter = false){
        list($where,$times) =$this->getBonusFilter($params,$dataCenter);

        $lists = User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->select()
            ->each(function(&$item) use ($times){
                $details =  BonusHistory::where('user_id',$item['user_id'])
                    ->where('clearing_status',1)
                    ->whereBetweenTime('year_month',$times['start_time'],$times['end_time'])
                    ->field('sum(sales_volume) as sales_volumes,sum(order_count) as order_cnt,sum(bonus_money) as bonus_money')
                    ->find();
                $item['sales_volumes'] = $details->sales_volumes??0;
                $item['order_cnt'] = $details->order_cnt??0;
                $item['bonus_money'] = $details->bonus_money??0;
            });

        $data['header'] = ['用户ID', '推荐官昵称', '推荐官手机号','推广订单数','商品销售额','奖励金'];
        $data['filename'] = '推荐官基础数据';
        $data['data'] = [];

        foreach ($lists as $k=>$arr){
            $new_list['user_id']                = $arr['user_id'];
            $new_list['nick_name']              = $arr['nick_name'];
            $new_list['mobile']                 = $arr['mobile'];

            $new_list['order_cnt']             = $arr['order_cnt'];
            $new_list['sales_volumes']        = $arr['sales_volumes'];
            $new_list['bonus_money']       = $arr['bonus_money'];

            $data['data'][] = $new_list;
        }
        return $data;
    }

    public function commDetails()
    {
        return $this->hasMany(CommissionsDetail::class,'user_id','user_id');
    }

    public function commDetailsCnt()
    {
        return $this->hasMany(CommissionsDetail::class,'user_id','user_id');
    }

    public function commerVolumeRank($params){
        if (!empty($params['betweenTime'])){
            $times = between_time($params['betweenTime']);
        }else{
            $times['end_time']   = strtotime(date('Y-m-01 00:00:00',time())) - 1;
            $times['start_time'] = strtotime(date('Y-m-01 00:00:00',strtotime('-1 month')));
        }
        $where[] = ['role','=',User::COMMISSION_USER];
        if (!empty($params['nickName'])){
            $where[] = ['nick_name|user_id', 'like','%'.$params['nickName'].'%'];
        }
        if (!empty($params['mobile']))$where[] = ['mobile', 'like','%'.$params['mobile'].'%'];

        $from = $times['start_time'];
        $to = $times['end_time'];

        return User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->withSum(['commDetails'=>function($query) use ($from,$to){//订单总金额（含运费）
                $query->where('commission_level',1)->where('clearing_status','<',2)
                    ->whereBetweenTime('order_create_time',$from,$to);
            }],'order_sale_volume')
            ->withCount(['commDetailsCnt'=>function($query) use ($from,$to){//订单总金额（含运费）
                $query->where('commission_level',1)->where('clearing_status','<',2)
                    ->whereBetweenTime('order_create_time',$from,$to);
            }])
            ->order('comm_details_sum desc')
            ->paginate(15);
    }


    /**
     * 推荐官业绩排行
     * @param $params
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public function exportCommerRank($params){
        if (!empty($params['betweenTime'])){
            $times = between_time($params['betweenTime']);
        }else{
            $times['end_time']   = strtotime(date('Y-m-01 00:00:00',time())) - 1;
            $times['start_time'] = strtotime(date('Y-m-01 00:00:00',strtotime('-1 month')));
        }
        $where[] = ['role','=',User::COMMISSION_USER];
        if (!empty($params['nickName'])){
            $where[] = ['nick_name|user_id', 'like','%'.$params['nickName'].'%'];
        }
        if (!empty($params['mobile']))$where[] = ['mobile', 'like','%'.$params['mobile'].'%'];

        $from = $times['start_time'];
        $to = $times['end_time'];

        $lists = User::where($where)->field('user_id,nick_name,mobile,role,seller_grade')
            ->withSum(['commDetails'=>function($query) use ($from,$to){//订单总金额（含运费）
                $query->where('commission_level',1)->where('clearing_status','<',2)
                    ->whereBetweenTime('order_create_time',$from,$to);
            }],'order_sale_volume')
            ->withCount(['commDetailsCnt'=>function($query) use ($from,$to){//订单总金额（含运费）
                $query->where('commission_level',1)->where('clearing_status','<',2)
                    ->whereBetweenTime('order_create_time',$from,$to);
            }])
            ->order('comm_details_sum desc')
            ->select();

        $data['header'] = ['排名','用户ID', '推荐官昵称', '推荐官手机号','推广订单数','销售额'];
        $data['filename'] = '推荐官业绩排行';
        $data['data'] = [];

        foreach ($lists as $k=>$arr){
            $new_list['rank']                   = $k+1;
            $new_list['user_id']                = $arr['user_id'];
            $new_list['nick_name']              = $arr['nick_name'];
            $new_list['mobile']                 = $arr['mobile'];

            $new_list['comm_details_cnt_count'] = $arr['comm_details_cnt_count'];
            $new_list['comm_details_sum']       = $arr['comm_details_sum']?:0;

            $data['data'][] = $new_list;
        }
        return $data;
    }


    public  function getMemberLists($params){
        $time = time();
        $filter[] = ['is_delete','=',0];
        // 微信昵称
        //!empty($params['nickName']) && $filter[] = ['nick_name', 'like', "%{$params['nickName']}%"];
        // 手机号
        if (!empty($params['memStatus']) && $params['memStatus'] >= 1){
            if ($params['memStatus'] == 1){
                $filter[] = ['member_expire_time', '<', $time];
            }else{
                $filter[] = ['member_expire_time', '>', $time];
            }
        }

        if (isset($params['memberStartTime']) && is_array($params['memberStartTime'])){
            $btes = between_time_format($params['memberStartTime']);
            $bt['start_time'] = strtotime(date('Y-m-d 00:00:00',$btes['start_time']));
            $bt['end_time'] = strtotime(date('Y-m-d 23:59:59',$btes['end_time']));

            $filter[] = ['member_start_time','>=',$bt['start_time']] ;
            $filter[] = ['member_start_time','<=',$bt['end_time']] ;
        }

        $list = self::where('member_expire_time','>',0)
            ->where($filter);
            if (!empty($params['nickName'])){
                $list =    $list->where('nick_name|user_id','like',"%{$params['nickName']}%");
            }
          return $list->field('user_id,nick_name,member_start_time,member_expire_time,gold_rice,growth_value')
            ->paginate(15)->each(function (&$item) use ($time){
                $item['mem_status'] = $item['member_expire_time']>$time?2:1;//1已过期，2：生效中
                $item['mem_status_text'] = ($item['mem_status'] ==1)?'已过期':'生效中';
                $item['member_start_time'] = date('Y-m-d H:i',$item['member_start_time']);//1已过期，2：生效中
                $item['member_expire_time'] = date('Y-m-d H:i',$item['member_expire_time']);//1已过期，2：生效中
            });
    }



}