// +---------------------------------------------------------------------- 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:生效中 }); } }