guoyao-jiankang/application/admin/controller/AiStatistics.php

<?php

namespace app\admin\controller;

use app\common\util\AppMsg;
use think\Db;
use think\Exception;
use think\exception\DbException;

class AiStatistics extends Auth
{
    static $types = [
        1 =>'报修',
        2 =>'保洁',
        3 =>'运送',
        4 =>'安保',
    ];
    static $colorMap = [
        1 => '#0FB36A',
        2 => '#168BFF',
        3 => '#F79B10',
        4 => '#6F3DFF',
    ];


    public function statisticsData()
    {
        $today = date('Ymd');
        $records = Db::name('ai_send_success')
            ->where('org_id', $this->orgId)
            ->field('mode')
            ->group('mode')
            ->column('COUNT(*)', 'mode');
        $done = [
            'total' => array_sum($records),
            'bx' => $records[1] ?? 0,
            'bj' => $records[2] ?? 0,
            'ys' => $records[3] ?? 0,
            'ab' => $records[4] ?? 0
        ];
        $orders = Db::name('orders')
            ->where('org_id',$this->orgId)
            ->where('del',0)
            ->where('create_yyyymmdd',$today)
            ->where('order_mode',1)
            ->whereNotExists(function ($query) {
                $query->table('ai_send_fail')
                    ->whereColumn('ai_send_fail.order_id', 'orders.id');
            })
            ->field('work_type_mode')
            ->group('work_type_mode')
            ->column('COUNT(*)', 'work_type_mode');
        $undo = [
            'total' => array_sum($orders),
            'bx' => $orders[1] ?? 0,
            'bj' => $orders[2] ?? 0,
            'ys' => $orders[3] ?? 0,
            'ab' => $orders[4] ?? 0,
        ];
        $data = [
            'done'=>$done,
            'undo'=>$undo,
        ];
        return json($data);
    }


    public function chartData()
    {
        $type = input('type', 'day', 'trim');
        $today = date('Ymd');

        // 根据类型设置查询条件
        switch ($type) {
            case 'month':
                $startDate = date('Ymd', strtotime('first day of this month'));
                $endDate = $today;
                $dateFormat = 'Ymd';
                $displayFormat = 'm-d';
                $dateRange = $this->getDateRange($startDate, $endDate, $dateFormat);
                break;
            case 'year':
                $startDate = date('Ymd', strtotime('first day of January'));
                $endDate = $today;
                $displayFormat = 'Y-m';
                $dateRange = $this->getMonthRange($startDate, $endDate);
                break;

            case 'day':
            default:
                $startDate = date('Ymd', strtotime('-7 days'));
                $endDate = $today;
                $dateFormat = 'Ymd';
                $displayFormat = 'm-d';
                $dateRange = $this->getDateRange($startDate, $endDate, $dateFormat);
                break;
        }

        $echart = Db::name('todo')
            ->where('org_id', $this->orgId)
            ->where('del', 0)
            ->where('create_yyyymmdd', '>=', $startDate)
            ->where('create_yyyymmdd', '<=', $endDate)
            ->field("create_yyyymmdd, SUM(create_user_id = -1) as ai, SUM(create_user_id != -1) as user")
            ->group('create_yyyymmdd')
            ->order('create_yyyymmdd')
            ->select();

        $statsByDate = [];
        foreach ($echart as $item) {
            if ($type == 'year') {
                $key = substr($item['create_yyyymmdd'], 0, 6);
            } else {
                $key = $item['create_yyyymmdd'];
            }

            if (!isset($statsByDate[$key])) {
                $statsByDate[$key] = ['ai' => 0, 'user' => 0];
            }
            $statsByDate[$key]['ai'] += (int)$item['ai'];
            $statsByDate[$key]['user'] += (int)$item['user'];
        }

        $aiData = [];
        $userData = [];
        $dates = [];

        foreach ($dateRange as $date) {
            $key = $type == 'year' ? substr($date, 0, 6) : $date;
            $aiData[] = isset($statsByDate[$key]) ? (int)$statsByDate[$key]['ai'] : 0;
            $userData[] = isset($statsByDate[$key]) ? (int)$statsByDate[$key]['user'] : 0;

            if ($type == 'year') {
                $dates[] = date($displayFormat, strtotime($key . '01'));
            } else {
                $dates[] = date($displayFormat, strtotime($date));
            }
        }

        $data = [
            'ai' => $aiData,
            'user' => $userData,
            'date' => $dates,
            'type' => $type
        ];

        return json($data);
    }

    public function countData()
    {
        $dispatched = Db::name('ai_send_success')
            ->where('org_id', $this->orgId)
            ->whereTime('create_time', 'today')
            ->count();
        $error = Db::name('ai_send_fail')
            ->where('org_id', $this->orgId)
            ->where('status', 0)
            ->whereTime('create_time', 'today')
            ->count();

        $order = Db::name('orders')
            ->where('org_id',$this->orgId)
            ->where('order_mode',1)
            ->where('del',0)
            ->whereIn('work_type_mode',[1,2,3,4])
            ->where('create_yyyymmdd',date('Ymd'))
            ->whereNotExists(function ($query) {
                $query->table('ai_send_fail')
                    ->whereColumn('ai_send_fail.order_id', 'orders.id');
            })
            ->count();

        $data = [
            'today'    => $dispatched,
            'error'    =>  $error,
            'wait'     =>  $order
        ];
        return json($data);
    }


    public function dispatchedData()
    {
        $size = input('size',10,'intval');
        $page = input('page',1,'intval');
        $mode= input('mode',0,'intval');
        $query = Db::name('ai_send_success')
            ->alias('a')
            ->join('orders o','a.order_id = o.id')
            ->where('a.org_id', $this->orgId)
            ->whereTime('a.create_time', 'today')
            ->when($mode > 0,function ($query) use($mode){
                $query->where('a.mode', $mode);
            })
            ->field('a.user_id,a.todo_id,a.mode,a.create_time,o.sn,o.user_id as uid,o.id');
        $todoList = $query
            ->order('a.create_time desc')
            ->limit(($page -1) * $size,$size)
            ->select();
        $total = $query->removeOption('limit')->count();
        $userIds = array_column($todoList,'uid');
        $toUserIds = array_column($todoList,'user_id');
        $allIds = array_unique(array_merge($toUserIds,$userIds));
        $users = Db::name('user')->whereIn('id',$allIds)->column('real_name','id');
        foreach ($todoList as &$v){
            $v['user'] = $users[$v['uid']]??'';
            $v['toUser'] = $users[$v['user_id']]??'';
            $v['type'] = self::$types[$v['mode']]??'';
        }
        unset($v);
        return json(['data'=>$todoList,'total'=>$total]);
    }

    public function unDispatchedData()
    {
        $size = input('size',10,'intval');
        $page = input('page',1,'intval');
        $type = input('type','','trim');
        if ($type == 'wait'){
            $query = Db::name('orders')
                ->where('org_id',$this->orgId)
                ->where('order_mode',1)
                ->where('del',0)
                ->whereIn('work_type_mode',[1,2,3,4])
                ->where('create_yyyymmdd',date('Ymd'))
                ->whereNotExists(function ($q) {
                    $q->table('ai_send_fail')
                        ->whereColumn('ai_send_fail.order_id', 'orders.id');
                })
                ->field('id,sn,work_type_mode,user_id');
        }else{
            $query = Db::name('ai_send_fail')
                ->alias('a')
                ->join('orders o','a.order_id = o.id')
                ->where('a.org_id', $this->orgId)
                ->where('a.status', 0)
                ->whereTime('a.create_time', 'today')
                ->field('o.id,o.sn,o.work_type_mode,o.user_id');
        }

        $orderList = $query
            ->limit(($page -1) * $size,$size)
            ->select();
        $total = $query->removeOption('limit')->count();
        $users = Db::name('user') ->whereIn('id',array_unique(array_column($orderList,'user_id'))) ->column('real_name','id');
        $i = 1;
        foreach ($orderList as &$v2){
            $v2['user'] = $users[$v2['user_id']]??'';
            $v2['type'] = self::$types[$v2['work_type_mode']]??'';
            $v2['flag'] = 0;
            if ($i <= 20){
                $v2['flag'] = 1;
                $i ++;
            }
        }
        unset($v2);
        return json(['data'=>$orderList,'total'=>$total]);
    }

    public function staffData()
    {
        $result = $this->getBatchModeStats([1,2,3,4], $this->orgId);
        $list = $result['list'];
        $summary = $result['summary'];
        $chartData = [];
        foreach ($list as $modeId => $data) {
            $chartData[] = [
                'value' => $data['active'],
                'total' => $data['total'],
                'name' => $data['mode_name'],
                'itemStyle' => [
                    'color' => self::$colorMap[$modeId] ?? '#999'
                ]
            ];
        }
        $data = [
            'bxInfo'    => $list[1],
            'bjInfo'    => $list[2],
            'ysInfo'    => $list[3],
            'abInfo'    => $list[4],
            'staffData' =>$chartData,
            'staffSummary' =>$summary
        ];
        return json($data);
    }


    public function orderData()
    {
        $type = input('type', 'day', 'trim');
        $today = date('Ymd');

        // 根据类型设置查询条件
        switch ($type) {
            case 'month':
                $startDate = date('Ymd', strtotime('first day of this month'));
                $endDate = $today;
                break;
            case 'year':
                $startDate = date('Ymd', strtotime('first day of January'));
                $endDate = $today;
                break;
            case 'day':
            default:
                $startDate = date('Ymd', strtotime('-7 days'));
                $endDate = $today;
                break;
        }

        $modeMap = self::$types;
        $modeIds = array_keys($modeMap);

        // 查询数据，按mode、来源分组统计总数
        $echart = Db::name('todo')
            ->where('org_id', $this->orgId)
            ->where('del', 0)
            ->where('create_yyyymmdd', '>=', $startDate)
            ->where('create_yyyymmdd', '<=', $endDate)
            ->whereIn('work_type_mode', $modeIds)
            ->field("work_type_mode, SUM(create_user_id = -1) as ai, SUM(create_user_id != -1) as user")
            ->group('work_type_mode')
            ->order('work_type_mode')
            ->select();

        // 将查询结果转为以mode为key的数组
        $statsByMode = [];
        foreach ($echart as $item) {
            $statsByMode[$item['work_type_mode']] = [
                'ai' => (int)$item['ai'],
                'user' => (int)$item['user']
            ];
        }

        // 按mode顺序构建一维数组
        $aiData = [];
        $userData = [];
        $modeNames = [];

        foreach ($modeMap as $modeId => $modeName) {
            $aiData[] = isset($statsByMode[$modeId]) ? $statsByMode[$modeId]['ai'] : 0;
            $userData[] = isset($statsByMode[$modeId]) ? $statsByMode[$modeId]['user'] : 0;
            $modeNames[] = $modeName;
        }

        $data = [
            'ai' => $aiData,
            'user' => $userData,
            'modes' => $modeNames,
        ];

        return json($data);
    }

    public function userData()
    {
        $size = input('size',7,'intval');
        $page = input('page',1,'intval');
        $start = ($page - 1) * $size;

        $mode= input('mode',0,'intval');
        $roleData = $mode > 0 ? $this->getRoleIdsByModeIds([$mode],$this->orgId) : $this->getRoleIdsByModeIds([1,2,3,4],$this->orgId);
        $map[] = ['u.del','=',0];
        $map[] = ['u.enable','=',1];
        $map[] = ['u.type','=',0];
        $map[] = ['ur.roles_id','in',$roleData['all_role_ids']];
        $query = Db::name('user')
            ->field('u.id,u.real_name,u.work,ur.roles_id')
            ->alias('u')
            ->join('user_roles ur','u.id=ur.user_id')
            ->where($map);

        $lists = $query->select();

        $total = count($lists);
        $roleIds = array_unique(array_column($lists, 'roles_id'));
        $roleNames = Db::name('roles')
            ->where('id', 'in', $roleIds)
            ->column('name', 'id');

        $userIds = array_column($lists, 'id');
        $todoCounts = Db::name('todo')
            ->field("CONCAT(to_user_id, '_', todo_mode) as mode_key, COUNT(*) as count")
            ->where('del', 0)
            ->whereIn('to_user_id', $userIds)
            ->where('todo_mode', 'in', [1, 2])
            ->group('to_user_id, todo_mode')
            ->select();

        $todoCountMap = [];
        foreach ($todoCounts as $item) {
            $todoCountMap[$item['mode_key']] = (int)$item['count'];
        }

        $avgTimes = Db::name('todo')
            ->field('to_user_id, AVG(xy_time) as receive, AVG(wc_time) as finish')
            ->where('del', 0)
            ->whereIn('to_user_id', $userIds)
//            ->where('todo_mode', 1)
            ->group('to_user_id')
            ->select();
        $avgTimeMap = [];
        foreach ($avgTimes as $item) {
            $avgTimeMap[$item['to_user_id']] = $item;
        }

        foreach ($lists as &$list) {
            $list['role_name'] = $roleNames[$list['roles_id']] ?? '未知角色';
            $list['work'] = (int)$list['work'];
            $list['doing'] = $todoCountMap[$list['id'] . '_2'] ?? 0;
            $list['wait'] = $todoCountMap[$list['id'] . '_1'] ?? 0;

            $avgData = $avgTimeMap[$list['id']] ?? null;
            $list['receive'] = $avgData ? $this->formatDuration($avgData['receive']) : '--';
            $list['finish'] = $avgData ? $this->formatDuration($avgData['finish']) : '--';
        }
        unset($list);
        usort($lists, function($a, $b) {
            if ($a['doing'] != $b['doing']) {
                return $b['doing'] - $a['doing'];
            }
            return $b['wait'] - $a['wait'];
        });
        $lists = array_slice($lists, $start, $size);
        return json(['data'=>$lists,'total'=>$total]);
    }


    /**
     * 获取日期范围（按天）
     */
    private function getDateRange($startDate, $endDate, $format = 'Ymd')
    {
        $dates = [];
        $current = strtotime($startDate);
        $end = strtotime($endDate);

        while ($current <= $end) {
            $dates[] = date($format, $current);
            $current = strtotime('+1 day', $current);
        }
        return $dates;
    }

    /**
     * 获取月份范围（按月）
     */
    private function getMonthRange($startDate, $endDate)
    {
        $dates = [];
        $current = strtotime($startDate);
        $end = strtotime($endDate);

        // 调整到月初
        $current = strtotime(date('Y-m-01', $current));

        while ($current <= $end) {
            $dates[] = date('Ymd', $current);
            $current = strtotime('+1 month', $current);
        }

        return $dates;
    }

    /**
     * 批量统计多个工作类型模式的用户数量（包含汇总）
     * @param array $modeIds 工作类型模式ID列表
     * @param int $orgId 组织ID
     * @return array
     */
    public function getBatchModeStats($modeIds, $orgId)
    {
        if (empty($modeIds)) {
            return [
                'list' => [],
                'summary' => [
                    'total' => 0,
                    'active' => 0,
                    'offline' => 0,
                    'active_rate' => 0,
                ]
            ];
        }

        // 1. 获取模式名称映射
        $modeNames = [];
        $modeConfigs = Db::name('work_type_mode')
            ->where('id', 'in', $modeIds)
            ->field('id, name')
            ->select();
        foreach ($modeConfigs as $config) {
            $modeNames[$config['id']] = $config['name'];
        }

        // 2. 获取所有角色ID和模式角色映射（使用优化后的方法）
        $roleData = $this->getRoleIdsByModeIds($modeIds, $orgId);
        $allRoleIds = $roleData['all_role_ids'];
        $modeRolesMap = $roleData['mode_roles_map'];

        // 如果没有角色，返回空数据
        $emptyResult = [
            'list' => [],
            'summary' => [
                'total' => 0,
                'active' => 0,
                'offline' => 0,
                'active_rate' => 0,
            ]
        ];

        if (empty($allRoleIds)) {
            foreach ($modeIds as $modeId) {
                $emptyResult['list'][$modeId] = [
                    'mode_id' => $modeId,
                    'mode_name' => $modeNames[$modeId] ?? '未知模式',
                    'total' => 0,
                    'active' => 0,
                    'offline' => 0,
                    'active_rate' => 0,
                ];
            }
            return $emptyResult;
        }

        // 3. 一次性查询所有用户
        $users = Db::name('user_roles')
            ->alias('ur')
            ->join('user u', 'u.id = ur.user_id')
            ->where('u.del', '=', 0)
            ->where('u.enable', '=', 1)
            ->where('ur.roles_id', 'in', $allRoleIds)
            ->field('u.id, u.work, ur.roles_id')
            ->select();

        // 4. 构建角色用户映射
        $roleUserMap = [];
        foreach ($users as $user) {
            $roleId = $user['roles_id'];
            $userId = $user['id'];
            $work = $user['work'];

            if (!isset($roleUserMap[$roleId])) {
                $roleUserMap[$roleId] = [
                    'total' => [],
                    'active' => [],
                ];
            }
            $roleUserMap[$roleId]['total'][$userId] = true;
            if ($work == 1) {
                $roleUserMap[$roleId]['active'][$userId] = true;
            }
        }

        // 5. 计算每个模式的统计数据（直接使用 $modeRolesMap）
        $result = [];
        foreach ($modeIds as $modeId) {
            $roleIds = $modeRolesMap[$modeId] ?? [];
            $totalUsers = [];
            $activeUsers = [];

            foreach ($roleIds as $roleId) {
                if (isset($roleUserMap[$roleId])) {
                    $totalUsers += $roleUserMap[$roleId]['total'];
                    $activeUsers += $roleUserMap[$roleId]['active'];
                }
            }

            $total = count($totalUsers);
            $active = count($activeUsers);

            $result[$modeId] = [
                'mode_id' => $modeId,
                'mode_name' => $modeNames[$modeId] ?? '未知模式',
                'total' => $total,
                'active' => $active,
                'offline' => $total - $active,
                'active_rate' => $total > 0 ? round(($active / $total) * 100, 2) : 0,
            ];

        }

        // 6. 计算汇总数据
        $summaryTotal = count($users);
        $summaryActive = count(array_filter($users, function($user) {
            return $user['work'] == 1;
        }));

        return [
            'list' => $result,
            'summary' => [
                'total' => $summaryTotal,
                'active' => $summaryActive,
                'offline' => $summaryTotal - $summaryActive,
                'active_rate' => $summaryTotal > 0 ? round(($summaryActive / $summaryTotal) * 100, 2) : 0,
            ]
        ];
    }


    /**
     * 根据模式ID列表获取所有关联的角色ID（包含每个模式的角色映射）
     * @param array $modeIds 模式ID列表
     * @param int $orgId 组织ID
     * @return array 返回 [
     *     'all_role_ids' => 所有角色ID（已去重）,
     *     'mode_roles_map' => [模式ID => 角色ID列表]
     * ]
     */
    public function getRoleIdsByModeIds($modeIds, $orgId)
    {
        if (empty($modeIds)) {
            return [
                'all_role_ids' => [],
                'mode_roles_map' => []
            ];
        }

        $allRoleIds = [];
        $modeRolesMap = [];

        // 1. 获取模式配置
        $modeConfigs = Db::name('work_type_mode')
            ->where('id', 'in', $modeIds)
            ->field('id, roles')
            ->select();

        // 2. 获取组织配置
        $orgConfigs = Db::name('work_type_mode_org')
            ->where('org_id', $orgId)
            ->where('value', 'in', $modeIds)
            ->select();

        $orgConfigMap = [];
        foreach ($orgConfigs as $config) {
            $orgConfigMap[$config['value']] = $config;
        }

        // 3. 遍历模式配置，收集所有角色ID
        foreach ($modeConfigs as $config) {
            $modeId = $config['id'];

            // 确定角色ID列表（优先使用组织配置）
            $roleIdsStr = '';
            if (isset($orgConfigMap[$modeId])) {
                $orgConfig = $orgConfigMap[$modeId];
                $roleIdsStr = !empty($orgConfig['org_roles'])
                    ? $orgConfig['org_roles']
                    : ($orgConfig['roles'] ?? '');
            } else {
                $roleIdsStr = $config['roles'] ?? '';
            }

            // 解析角色ID
            $roleIds = $roleIdsStr ? array_filter(array_map('trim', explode(',', $roleIdsStr))) : [];
            if (empty($roleIds)) {
                $modeRolesMap[$modeId] = [];
                continue;
            }

            // 获取每个角色的所有子角色
            $allChildrenIds = [];
            foreach ($roleIds as $roleId) {
                $children = model('Roles')->getChildrenIds($roleId, $orgId) ?? [];
                $allChildrenIds = array_merge($allChildrenIds, $children, [$roleId]);
            }
            $allChildrenIds = array_unique($allChildrenIds);

            $modeRolesMap[$modeId] = $allChildrenIds;
            $allRoleIds = array_merge($allRoleIds, $allChildrenIds);
        }

        // 去重返回
        return [
            'all_role_ids' => array_unique($allRoleIds),
            'mode_roles_map' => $modeRolesMap
        ];
    }



/**
 * 格式化时长（秒数转为 x时x分x秒 格式）
 * @param float|int|null $seconds 秒数
 * @return string 格式化后的时长，如 "1h2min30s" 或 "--"
 */
    private function formatDuration($seconds)
    {
        if (empty($seconds) || $seconds <= 0) {
            return '--';
        }

        $seconds = (int)round($seconds);

        $hours = floor($seconds / 3600);
        $minutes = floor(($seconds % 3600) / 60);
        $remainingSeconds = $seconds % 60;

        $parts = [];
        if ($hours > 0) {
            $parts[] = $hours . 'h';
        }
        if ($minutes > 0) {
            $parts[] = $minutes . 'min';
        }
        if ($remainingSeconds > 0 || empty($parts)) {
            $parts[] = $remainingSeconds . 's';
        }

        return implode('', $parts);
    }

}