lachang/app/api/model/user/CommissionsDetail.php

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

namespace app\api\model\user;

use app\api\model\Order;
use app\api\model\OrderGoods;
use app\api\model\OrderRefund;
use app\api\model\shop\Shops;
use app\api\model\User;
use app\common\library\helper;
use app\common\model\user\CommissionsDetail as CommissionsDetailModel;
use Exception;
use think\facade\Log;

/**
 * 待结算分佣明细模型
 * Class Store
 * @package app\store\model
 */
class CommissionsDetail extends CommissionsDetailModel
{


    /**
     *新增待结算分佣明细
     * @param int $userId
     * @param $orderId
     * @param $orderCreateTime
     * @param $clearing_money
     * @param int $shopId
     * @param int $buyerUserId
     * @param int $role
     * @param int $commissionLevel
     * @param $seller_grade
     * @param int $percent
     * @param int $orderSaleVolume
     * @return bool
     */
    public static function addNewCommission(int $userId, $orderId, $orderCreateTime,$clearing_money, int $shopId, $buyerUserId=0, $role=User::SHOP_SELLER, $commissionLevel=1,$seller_grade=0,$percent=0,$orderSaleVolume=0): bool
    {
        try {
            //if($clearing_money > 0 && $user_id > 0){//结算金额是0也写分佣记录，是为了方便查看订单查询
            if($userId > 0){
                $mc = new CommissionsDetail();
                $mc->user_id = $userId;
                $mc->order_id = $orderId;
                $mc->clearing_money = $clearing_money;
                $mc->shop_id = $shopId;
                $mc->role = $role;
                $mc->buyer_user_id = $buyerUserId;
                $mc->commission_level = $commissionLevel;
                $mc->seller_grade = $seller_grade;
                $mc->commission_percent = $percent;

                $mc->order_create_time = $orderCreateTime;
                $mc->order_sale_volume = $orderSaleVolume;
                $mc->save();
            }
        }catch (Exception $e){
            Log::error("addNewCommission::".$orderId.$e->getMessage());
            return false;
        }
        return true;
    }

    /**
     * 佣金结算列表
     * @param $userId
     * @param int $clearingStatus
     * @param false $isShop
     * @return array
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\DbException
     * @throws \think\db\exception\ModelNotFoundException
     */
    public static function getCommsList($userId,$clearingStatus=0,$isShop = false){

        if ($isShop == true){
            return self::field('clearing_money,clearing_status,order_id,order_goods_id,create_time')
                ->with(['order_goods'=>function($query){
                    $query->field('order_goods_id,goods_name');
                },'orders'=>function($query){
                    $query->field('order_id,order_no,order_status,pay_status');
                }])->where('shop_id',$userId)
                ->find()->toArray();
        }

/*        ,IF
        ((create_time + 1296000) > unix_timestamp( now( ) ),
                CEILING( ( ( create_time + 1296000 ) - unix_timestamp( now( ) ) ) / 86400 ),"" ) as  clearing_by*/
        return self::field('id as commission_id,TRUNCATE(clearing_money,2) as clearing_money,clearing_status,order_id,create_time')
            ->with(['order_goods'=>function($query){
                $query->field('order_goods_id,goods_name,image_id,IF(commission_settlement_time>0 && commission_settlement_time>unix_timestamp(),CEILING((commission_settlement_time-unix_timestamp())/86400),"" ) as clearing_by');
            },'orders'=>function($query){
                $query->field('order_id,order_no,order_status,pay_status,delivery_status,create_time,receipt_status,delivery_time');
            }])->where('user_id',$userId)
            ->where('clearing_status',$clearingStatus)
            ->order('id','desc')
            ->paginate(10)->toArray();
    }

    /**
     * 佣金结算列表 按订单
     * @param $userId
     * @param int $clearingStatus
     * @return array
     * @throws \think\db\exception\DbException
     */
    public static function getCommsListOrder($userId,$clearingStatus=0): array
    {
        return self::field('id,TRUNCATE(clearing_money,2) as clearing_money,clearing_status,order_id,create_time,role,commission_level')
            ->with(['orders'=>function($query){
                $query->field('order_id,order_no,create_time,order_status,pay_status,delivery_status,receipt_status,delivery_time,pay_price,rice_card_money');
            },'goods'=>function($query){
                $query->field('order_goods_id,goods_name,image_id');
            }])->where('user_id',$userId)
            ->where('clearing_status',$clearingStatus)
            ->order('id','desc')
            ->paginate(10)->each(function(&$item){
                 $item['orders']['total_pay'] = helper::bcadd($item['orders']['pay_price'],$item['orders']['rice_card_money'],2);

            })->toArray();
    }


    public static function getCommOrderId($userId,$from,$to){
        $model =  self::field( 'distinct order_id')
            ->where('user_id',$userId);
        return $model->whereBetweenTime('order_create_time', $from, $to)
        ->column('order_id');
    }

    /**
     * 我的收益金-退款订单
     */
    public static function getRefundOrderList($userId) {
        $from = 1577808001;$to=time();
        $orderGoodsId = self::getCommOrderId($userId,$from,$to);
        //整单退款
        $refundOrderId =  OrderRefund::whereIn('order_id',$orderGoodsId)->where('order_goods_id',0)
            ->column('order_id');
        //非整单退款
        $refundOrderGoodsId =  OrderRefund::whereIn('order_id',$orderGoodsId)->where('order_goods_id','>',0)
            ->column('order_goods_id');
        if (!count($refundOrderId) && !count($refundOrderGoodsId)){
            return  [];
        }

        return CommissionsDetail::alias('cd')
            ->field('cd.*,order.order_no')
            ->with(['order_goods'=>function($query){
                $query->field('order_goods_id,goods_name,image_id,goods_price,total_num,total_price');
            },'order_refund'=>function($query){
                $query->field('*');
            },'order_refund_all'=>function($query){
                $query->field('*');
            }])
            ->leftJoin('order', 'order.order_id=cd.order_id')
            ->where('cd.user_id', $userId)
            ->where(function ($query) use ($refundOrderId,$refundOrderGoodsId){
                if (count($refundOrderId) && count($refundOrderGoodsId)){
                    $query->whereIn('cd.order_id',$refundOrderId)->whereOr('cd.order_goods_id','in',$refundOrderGoodsId);
                }

                if (count($refundOrderId) && !count($refundOrderGoodsId)){
                    $query->whereIn('cd.order_id',$refundOrderId);
                }

                if (count($refundOrderGoodsId) && !count($refundOrderId)){
                    $query->whereIn('cd.order_goods_id',$refundOrderGoodsId);
                }

            })
            ->order('cd.update_time', 'desc')
            ->paginate(100)
            ->each(function($item) {
                if (empty($item->order_refund)) {
                    // 整单退款
                    $orderRefund = $item->order_refund_all[0]??null;
                    $item->refund_goods_amount = $item->order_goods->total_price;
                } else {
                    $orderRefund = $item->order_refund;
                    $item->refund_goods_amount = helper::bcmul($item->order_goods->goods_price, $orderRefund->goods_num, 2);
                }
                
                unset($item->order_refund, $item->order_refund_all);
                $item->order_refund = $orderRefund;
            })
            ->toArray();
    }

    /**
     * 关联单个订单商品表
     * @return \think\model\relation\BelongsTo
     */
    public function orderGoods()
    {
        return $this->belongsTo(OrderGoods::class,'order_goods_id','order_goods_id')->withoutField(['content']);
    }

    /**
     * 关联全部订单商品
     */
    public function orderGoodsAll()
    {
        return $this->hasMany(OrderGoods::class, 'order_id', 'order_id')->withoutField(['content']);
    }

    /**
     * 关联单个退款
     */
    public function orderRefund(){
        return $this->belongsTo(OrderRefund::class,'order_goods_id','order_goods_id');
    }

    /**
     * 关联整单退款
     */
    public function orderRefundAll()
    {
        return $this->hasMany(OrderRefund::class, 'order_id', 'order_id');
    }

    public function orders(){
        return $this->belongsTo(Order::class,'order_id','order_id');
    }

    /**
     * Notes:获取用户今日收益
     * Author: zhangs
     * DateTime: 2021/10/9 14:21
     * @param $userId
     * @return float
     */
    public static function getUserTodayProfits($userId)
    {
        return self::where('user_id', $userId)
            ->where('clearing_status', '<>', self::CLEARING_STATUS_REFUND)
            ->whereDay('create_time')
            ->sum('clearing_money');
    }

    public static function getCommissionOrders($userId,$dataType='',$type=0,$from=null,$to=null,$keyword = null,$shopId=null){
        $user = User::find($userId);
        $shopId = $shopId?:$user->shop_id;
        $sellerUser = null;
        $buyers = null;
        if ($keyword){
            $buyers = User::where('nick_name','like',$keyword)->whereOr('mobile','like',$keyword)->select()->column('user_id');
        }

        //$pool = $orderGoods['total_price'] - $orderGoods['distributor_total_money'];//实付金额加现金米卡抵扣金额
        $m = self::field('commission_id,commissions_detail.user_id,commissions_detail.order_goods_id,round(commissions_detail.clearing_money,2) as clearing_money,commissions_detail.order_id,commissions_detail.commission_level')
            ->withJoin([
                'orderGoods'=>['order_goods_id','goods_name','total_num','total_pay_price','image_id','user_id','frozen_status','total_price','distributor_total_money','coupon_money','rice_card_money'],
                'orders'=>['order_id','order_no','order_status','pay_status','delivery_status','create_time','receipt_status','delivery_time'],
                'recommender'=>['user_id','nick_name'],
            ]);

        $m = $m->where('commissions_detail.user_id',$userId)->where('commissions_detail.shop_id',$shopId);
        if ($dataType){
            $order = new Order();
            $filters =$order->getFilter($dataType);
            if (count($filters)){
                foreach ($filters as $key =>$filter){
                    //$field = 'orders.'.$key;
                    $m = $m->where('orders.'.$key,$filter);
                }
            }
        }

        if ($type == 1 && $from && $to) {
            $m = $m->whereBetweenTime('orders.create_time', $from, $to);
        }
        if ($type == 2) {
            $m = $m->whereDay('orders.create_time', $from);
        }
        if ($type == 3) {
            $m = $m->whereMonth('orders.create_time', $from);
        }

        if ($buyers && count($buyers)){
            $m = $m->whereIn('orders.user_id',$buyers);
        }
        return $m->order('commission_id','desc')->paginate(10)->toArray();
    }


    public static function getCommissionOrdersNew($userId,$dataType='',$type=0,$from=null,$to=null,$keyword = null,$shopId=null,$isDown=0): array
    {
        $user = User::find($userId);
        $shopId = $shopId?:$user->shop_id;
        $sellerUser = null;
        $buyers = null;
        if ($keyword){
            $buyers = User::where('nick_name','like',$keyword)->whereOr('mobile','like',$keyword)->select()->column('user_id');
        }

        $m = CommissionsDetail::field('id,commissions_detail.user_id,round(commissions_detail.clearing_money,2) as clearing_money,commissions_detail.order_id,commissions_detail.commission_level,buyer_user_id,clearing_status')
            ->withJoin([
                'orders'=>['order_id','order_no','order_status','pay_status','delivery_status','create_time','receipt_status','delivery_time','pay_price','rice_card_money','user_id'],
                //'recommender'=>['user_id','nick_name'],
            ])
            ->with(['goods'=>function($query){
                $query->field('order_id','order_goods_id','goods_name','total_num','total_pay_price','image_id','user_id','frozen_status','total_price','distributor_total_money','coupon_money');
            }]);

        $isCommer = ($user->role == User::COMMISSION_USER);

        //下级分销员推广订单
        if ($isDown == 1 && $isCommer){
            $m = $m->leftJoin('user','commissions_detail.user_id=user.user_id')->where('user.upper_user_id',$userId);
            //$m = $m->where('commissions_detail.commission_level',2);

        }elseif ($isCommer){//分销员自己推广的一级订单
            $m = $m->where('commissions_detail.user_id',$userId)
                ->where('commissions_detail.commission_level',1);
        }else{//店员，店长
            $m = $m->where('commissions_detail.user_id',$userId);
        }

/*          $m = $m->where('commissions_detail.commission_level',1)
                 ->where('commissions_detail.shop_id',$shopId);*/
        $m =     $m->where('commissions_detail.shop_id',$shopId);

        if ($dataType){
            $order = new Order();
            $filters =$order->getFilter($dataType);
            if (count($filters)){
                foreach ($filters as $key =>$filter){
                    //$field = 'orders.'.$key;
                    $m = $m->where('orders.'.$key,$filter);
                }
            }
        }

        if ($type == 1 && $from && $to) {
            $m = $m->whereBetweenTime('orders.create_time', $from, $to);
        }
        if ($type == 2) {
            $m = $m->whereDay('orders.create_time', $from);
        }
        if ($type == 3) {
            $m = $m->whereMonth('orders.create_time', $from);
        }

        if ($buyers && count($buyers)){
            $m = $m->whereIn('orders.user_id',$buyers);
        }
        return $m->order('id','desc')
            ->paginate(10)
            ->each(function(&$item) use ($isDown,$isCommer){
                if ($isDown == 1 && $isCommer){//当前推荐官用户的下级推荐官数据都是二级佣金
                    $item['commission_level'] = 2;
                    $levelOneCom = CommissionsDetail::where('order_id',$item['order_id'])
                        ->where('commission_level',2)->where('role',99)->field('clearing_money')
                        ->find();
                    $item['clearing_money'] = helper::bcadd($levelOneCom->clearing_money??0,0,2);
                }
                $staffUserId = Order::where('order_id',$item['order_id'])->field('staff_user_id')->find();
                $item['recommender'] = User::where('user_id',$staffUserId->staff_user_id)->field('user_id,nick_name')->find();
                $item['orders']['total_pay'] = helper::bcadd($item['orders']['pay_price'],$item['orders']['rice_card_money'],2);
                $buyer = User::with(['avatar'])->field('user_id,nick_name,avatar_id')->find($item['orders']['user_id']);
                $item['orders']['buyer'] = ['nick_name'=>$buyer['nick_name']??'',
                                            'avatar'=>$buyer['avatar']['ali_url']??''
                                        ]; 
        })->toArray();
    }

    /**
     * 获取推荐人
     * @return \think\model\relation\BelongsTo
     */
    public function recommender(): \think\model\relation\BelongsTo
    {
        return $this->belongsTo(User::class,'user_id','user_id');
    }


    public static function getUserWaitCommission($userId,$state=0){
        return self::where('user_id',$userId)->where('clearing_status',$state)->sum('clearing_money');
    }

    /**
     * 累加门店某角色在时间范围内的已结算佣金
     * @param $shopId
     * @param $role
     * @param $from
     * @param $to
     * @return float
     */
    public static function sumUserCommission($shopId,$role,$from,$to): float
    {
        return self::alias('commissions_detail')
            ->where('commissions_detail.shop_id',$shopId)
            ->where('commissions_detail.role',$role)
            ->where('commissions_detail.clearing_status',1)
            ->whereBetweenTime('commissions_detail.order_create_time',$from,$to)
            ->sum('clearing_money');
    }

    /**
     * 用户的下级分销员的销售额
     * @param $userId
     * @return string
     */
    public static function sumUserCommSalesVolume($userId){
        $res = self::alias('commissions_detail')
            ->leftJoin('user','commissions_detail.user_id=user.user_id')
            ->where('user.upper_user_id',$userId)
            ->where('user.role',User::COMMISSION_USER)
            ->where('commissions_detail.commission_level',1)
            ->where('commissions_detail.clearing_status','<',2)
            ->field('commissions_detail.order_id')
            ->select()->column('order_id');

        $orderIds = array_unique($res);
        $volume = Order::sumSalesVolume($orderIds);
        $refund = OrderRefund::sumRefundPriceByOrder($orderIds);
        return helper::bcsub($volume,$refund,2);

    }

     /**
     * 用户的下级分销员的销售额
     * @param $userId
     * @return array
      */
    public static function sumUserSalesVolume($userId){
        $res = self::alias('commissions_detail_order')
            ->where('commissions_detail_order.user_id',$userId)
            ->where('commissions_detail_order.commission_level',1)
            ->where('commissions_detail_order.clearing_status','<',2)
            ->select()->column('order_id');
        $orderIds = array_unique($res);
        $orderCnt = count($orderIds);
        $volume = Order::sumSalesVolume($orderIds);
        $refund = OrderRefund::sumRefundPriceByOrder($orderIds);
        $amount = helper::bcsub($volume,$refund,2);
        return ['order_cnt'=>$orderCnt,'order_amount'=>$amount];
    }


}