anmo/server/app/adminapi/lists/coach/CoachLists.php

<?php
namespace app\adminapi\lists\coach;
use app\adminapi\lists\BaseAdminDataLists;
use app\common\enum\coach\CoachEnum;
use app\common\enum\OrderEnum;
use app\common\model\coach\Coach;
use app\common\model\deposit\DepositPackage;
use app\common\model\order\Order;
use app\common\model\skill\Skill;

class CoachLists extends BaseAdminDataLists
{

    public function setWhere()
    {
        $where = [];
        $auditStatus = $this->params['audit_status'] ?? CoachEnum::AUDIT_STATUS_PASS;
        if($auditStatus){
            $where[] = ['audit_status','=',$auditStatus];
        }
        if(isset($this->params['staff_info']) && $this->params['staff_info']){
            $where[] = ['C.sn|name|mobile','like',$this->params['staff_info']];
        }
//        if(isset($this->params['coach_keyword']) && $this->params['coach_keyword']){
//            $where[] = ['C.sn|account','like',$this->params['coach_keyword']];
//        }
        if(isset($this->params['skill_id']) && '' != $this->params['skill_id']){
            $where[] = ['skill_id','=',$this->params['skill_id']];
        }
        if(isset($this->params['city_id']) && $this->params['city_id']){
            $where[] = ['city_id','=',$this->params['city_id']];
        }
        if(isset($this->params['work_status']) && '' != $this->params['work_status']){
            $where[] = ['work_status','=',$this->params['work_status']];
        }
        if(isset($this->params['status']) && '' != $this->params['status']){
            $where[] = ['server_status','=',$this->params['status']];
        }
        if(isset($this->params['start_time']) && $this->params['start_time']){
            $where[] = ['C.create_time','>=',strtotime($this->params['start_time'])];
        }
        if(isset($this->params['end_time']) && $this->params['end_time']){
            $where[] = ['C.create_time','<=',strtotime($this->params['end_time'])];
        }
        return $where;
    }

    // public function lists(): array
    // {
    //     $lists = Coach::alias('C')
    //         ->join('coach_user UC','UC.id = C.coach_user_id')
    //         ->field('UC.sn,C.id,C.province_id,C.city_id,C.region_id,C.mobile,C.name,C.work_photo,C.server_status,C.deposit,C.work_status,C.create_time,C.money')
    //         ->append(['work_status_desc','server_status_desc','province_name','city_name','region_name'])
    //         ->order('id desc')
    //         ->where($this->setWhere())
    //         ->limit($this->limitOffset, $this->limitLength)
    //         ->select()->toArray();
    //     $coachIds = array_column($lists,'id');
    //     $depositLists = DepositPackage::where(['type'=>1])->column('order_limit,money');
    //     $orderLists = Order::where(['coach_id'=>$coachIds])
    //         ->where('order_status','>',OrderEnum::ORDER_STATUS_WAIT_RECEIVING)
    //         ->field('count(id) as num,coach_id')
    //         ->select()->toArray();
    //     $orderLists = array_column($orderLists,null,'coach_id');
    //     foreach ($lists as $key => $coach){
    //         $depositPackage = [];
    //         foreach ($depositLists as $deposit){
    //             if($coach['deposit'] >= $deposit['money']){
    //                 $depositPackage = $deposit;
    //                 break;
    //             }
    //         }
    //         $lists[$key]['work_info'] = [
    //             'take_order'        => $depositPackage['order_limit'] ?? 0,
    //             'total_order'      => $orderLists[$coach['id']]['num'] ?? 0,
    //         ];
    //     }
    //     return $lists;
    // }
    public function lists(): array
    {
        $lists = Coach::alias('C')
            ->join('coach_user UC', 'UC.id = C.coach_user_id')
            ->field('UC.sn,C.id,C.province_id,C.city_id,C.region_id,C.mobile,C.name,C.work_photo,C.server_status,C.deposit,C.work_status,C.create_time,C.money')
            ->append(['work_status_desc','server_status_desc','province_name','city_name','region_name'])
            ->order('id desc')
            ->where($this->setWhere())
            ->limit($this->limitOffset, $this->limitLength)
            ->select()
            ->toArray();
        
        $coachIds = array_column($lists, 'id');
        
        // 获取保证金套餐
        $depositLists = DepositPackage::where(['type' => 1])
            ->column('order_limit,money');
        
        // 修复后的订单统计查询
        $orderLists = [];
        if (!empty($coachIds)) {
            $orderLists = Order::where('coach_id', 'in', $coachIds)
                ->where('order_status', '>', OrderEnum::ORDER_STATUS_WAIT_RECEIVING)
                ->field('coach_id, count(id) as num')
                ->group('coach_id') // 按教练分组统计
                ->select()
                ->toArray();
        }
        
        // 转换为以 coach_id 为键的数组
        $orderLists = array_column($orderLists, null, 'coach_id');
        
        foreach ($lists as $key => $coach) {
            $depositPackage = [];
            
            // 匹配保证金套餐
            foreach ($depositLists as $deposit) {
                if ($coach['deposit'] >= $deposit['money']) {
                    $depositPackage = $deposit;
                    break;
                }
            }
            
            $lists[$key]['work_info'] = [
                'take_order' => $depositPackage['order_limit'] ?? 0,
                'total_order' => $orderLists[$coach['id']]['num'] ?? 0,
            ];
        }
        
        return $lists;
    }

    /**
     * @notes 统计人数
     * @return int
     * @author cjhao
     * @date 2024/8/21 17:53
     */
    public function count(): int
    {
        return Coach::alias('C')
            ->join('coach_user UC','UC.id = C.coach_user_id')
            ->where($this->setWhere())
            ->count();
    }
}