workHour/app/Service/EmployeeService.php

<?php

namespace App\Service;

use App\Model\CalendarDetails;
use App\Model\Company;
use App\Model\Depart;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\EmployeeFile;
use App\Model\EmployeeRole;
use App\Model\EmployeeWorkRange;
use App\Model\Role;
use App\Model\RoleMenu;
use App\Model\RoleMenuButton;
use App\Model\WorkRangeDetails;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Hash;
use Mockery\Exception;

class EmployeeService extends Service
{
    public function companySet($data, $user)
    {
        // 1. 基础校验
        if (empty($data['title'])) return [false, '公司全称不能为空'];
        if (empty($data['code'])) return [false, '统一社会信用代码不能为空'];

        // 2. 准备查询条件
        $attributes = [
            'top_depart_id' => $user['top_depart_id']
        ];

        // 3. 准备需要更新或插入的数据
        $values = [
            'title'                => $data['title'],
            'code'                 => $data['code'],
            'address'              => $data['address'] ?? '',
            'legal_representative' => $data['legal_representative'] ?? '',
            'mobile'               => $data['mobile'] ?? '',
            'e_mail'               => $data['e_mail'] ?? '',
            'industry'             => $data['industry'] ?? '',
            'start_time'           => $data['start_time'] ?? '',
            'money'                => $data['money'] ?? '',
            'number'               => $data['number'] ?? '',
            'del_time'             => 0, // 建议重置删除标志，防止在更新已软删除的数据时出错
        ];

        try {
            Company::updateOrCreate($attributes, $values);
        } catch (\Exception $e) {
            return [false, '保存失败：' . $e->getMessage()];
        }

        return [true, ''];
    }

    public function companyDetail($data, $user)
    {
        $company = Company::where('del_time',0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->first();
        if(empty($company)) return [true, []];

        return [true, $company->toArray()];
    }

    public function companyWorkArrangeSet($data, $user)
    {
        if (empty($data['work_range'])) return [false, '公司工作时段不能为空'];

        // 用于暂存转换后的分钟区间，格式：[['start' => X, 'end' => Y], ...]
        $timeSegments = [];

        foreach ($data['work_range'] as $key => $value){
            // 1. 基础合法性校验
            $res = $this->checkNumber($value['start_time_hour'], 0, 'non-negative');
            if(!$res['valid']) return [false, "开始点：" . $res['error']];
            if($value['start_time_hour'] > 23) return [false, "开始点不合法（应为0-23）"];

            $res = $this->checkNumber($value['start_time_min'], 0, 'non-negative');
            if(!$res['valid']) return [false, "开始分：" . $res['error']];
            if($value['start_time_min'] > 59) return [false, "开始分不合法（应为0-59）"]; // 修正为59

            $res = $this->checkNumber($value['end_time_hour'], 0, 'non-negative');
            if(!$res['valid']) return [false, "结束点：" . $res['error']];
            if($value['end_time_hour'] > 24) return [false, "结束点不合法（应为0-24）"];

            $res = $this->checkNumber($value['end_time_min'], 0, 'non-negative');
            if(!$res['valid']) return [false, "结束分：" . $res['error']];
            // 如果是24点，分钟必须是0，其余情况应小于60
            if($value['end_time_hour'] == 24 && $value['end_time_min'] > 0) {
                return [false, "结束时间不能超过24:00"];
            }
            if($value['end_time_min'] > 59 && $value['end_time_hour'] != 24) {
                return [false, "结束分不合法（应为0-59）"];
            }

            // 2. 计算绝对分钟数
            $startTotalMin = $value['start_time_hour'] * 60 + $value['start_time_min'];
            $endTotalMin = $value['end_time_hour'] * 60 + $value['end_time_min'];

            // 3. 校验单一时段的逻辑正确性
            if ($endTotalMin <= $startTotalMin) {
                return [false, "第 " . ($key + 1) . " 个时段的结束时间必须大于开始时间"];
            }

            // 4. 【核心新增】：时段冲突/重叠校验
            // 拿当前时段和之前已经校验过的所有时段逐一比对
            foreach ($timeSegments as $index => $existingSegment) {
                // 交叉重叠判别式：S1 < E2 && S2 < E1
                if ($startTotalMin < $existingSegment['end'] && $existingSegment['start'] < $endTotalMin) {
                    $currentPeriod = "{$value['start_time_hour']}:" . str_pad($value['start_time_min'], 2, '0', STR_PAD_LEFT) . "~{$value['end_time_hour']}:" . str_pad($value['end_time_min'], 2, '0', STR_PAD_LEFT);
                    return [false, "时段冲突！当前时段 [{$currentPeriod}] 与其他设置的时段存在重叠"];
                }
            }

            // 5. 校验通过，记录该区间用于后续比对
            $timeSegments[] = [
                'start' => $startTotalMin,
                'end'   => $endTotalMin
            ];

            // 6. 补全写入数据库的隐藏字段
            $data['work_range'][$key]['total_work_min'] = $endTotalMin - $startTotalMin;
            $data['work_range'][$key]['top_depart_id'] = $user['top_depart_id'];
        }

        // 7. 写入数据库（包含事务包裹）
        try {
            $time = time();
            DB::beginTransaction();

            WorkRangeDetails::where('del_time', 0)
                ->where('top_depart_id', $user['top_depart_id'])
                ->update(['del_time' => $time]);

            WorkRangeDetails::insert($data['work_range']);

            DB::commit();
        } catch (\Exception $e) {
            DB::rollBack();
            return [false, '保存失败：' . $e->getMessage()];
        }

        return [true, ''];
    }

    public function companyWorkArrangeDetail($data, $user)
    {
        $company = WorkRangeDetails::where('del_time',0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->get()->toArray();

        return [true, $company];
    }

    public static function getCompanyDetail($user){
        $company = Company::where('del_time',0)
            ->where('top_depart_id', $user['top_depart_id'])
            ->first();
        if(empty($company)){
            //默认名字
            $title = Depart::where('id', $user['top_depart_id'])->value('title');
            $return['title'] = $title;
            return $return;
        }

        return $company->toArray();
    }

    public function employeeEditOther($data,$user){
        list($status,$msg) = $this->employeeOtherRule($data,$user);
        if(!$status) return [$status,$msg];

        try {
            DB::beginTransaction();
            $model = new Employee();
            $model = $model->where('id',$user['id'])->first();
            $model->password = Hash::make($data['new_password']);
            $model->p_version = $model->p_version + 1;
            $model->save();

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false, $exception->getMessage()];
        }

        return [true,''];
    }

    public function employeeOtherRule($data,$user){
        if(! isset($data['old_password'])) return [false,'请输入原密码'];
        if($data['old_password'] == "") return [false,'原密码不能为空'];
        if(! isset($data['new_password'])) return [false,'请输入新密码'];
        if($data['new_password'] == "") return [false,'新密码不能为空'];
        if(! isset($data['re_password'])) return [false,'请输入确认密码'];
        if($data['re_password'] == "") return [false,'确认密码不能为空'];
        if(! Hash::check($data['old_password'], $user['password'])) return [false,'原密码错误'];
        if($data['new_password'] == $data['old_password']) return [false,'原密码与新密码一致'];
        if($data['new_password'] !== $data['re_password']) return [false,'新密码与确认密码不一致'];

        return [true,''];
    }

    public function employeeEdit($data,$user){
        list($status,$msg) = $this->employeeRule($data,$user,false);
        if(!$status) return [$status,$msg];

        try {
            DB::beginTransaction();
            $model = new Employee();
            $model = $model->where('id',$data['id'])->first();
            $model->number = $data['number'] ?? "";
            $model->title = $data['title'] ?? "";
            $model->mobile = $data['mobile'] ?? "";
            $model->sex = $data['sex'] ?? 0;
            $model->education = $data['education'] ?? "";
            $model->id_card = $data['id_card'] ?? "";
            $model->major = $data['major'] ?? "";
            $model->position = $data['position'] ?? "";
            $model->p_title = $data['p_title'] ?? "";
            $model->state = $data['state'] ?? 0;
            $model->employee_type = $data['employee_type'] ?? 0;
            $model->is_admin = $data['is_admin'] ?? 0;
            $model->entrust_type = $data['entrust_type'] ?? 0;
            $model->man_type = $data['man_type'] ?? 0;
            if($model->is_admin && $data['password'] !== '******') {
                $model->password = Hash::make($data['password']);
                $model->p_version = $model->p_version + 1;
            }
            $model->save();

            $time = time();
            EmployeeDepartPermission::where('employee_id',$data['id'])->delete();
            if(isset($data['depart'])){
                $insert = [];
                foreach ($data['depart'] as $value){
                    $insert[] = [
                        'employee_id' => $model->id,
                        'depart_id' => $value,
                        'top_depart_id' => $user['top_depart_id'],
                    ];
                }
                EmployeeDepartPermission::insert($insert);
            }

            EmployeeRole::join('role', 'role.id', '=', 'employee_role.role_id')
                ->where('employee_role.employee_id', $data['id'])
                ->where('role.tree_type', $user['select_tree_type'])
                ->where('employee_role.del_time', 0)
                ->update([
                    'employee_role.del_time' => $time
                ]);

            if(isset($data['role'])){
                $insert = [];
                foreach ($data['role'] as $value){
                    $insert[] = [
                        'employee_id' => $model->id,
                        'role_id' => $value,
                        'crt_time' => $time,
                    ];
                }
                EmployeeRole::insert($insert);
            }

            EmployeeWorkRange::where('employee_id',$data['id'])->delete();
            if(isset($data['work_range'])){
                $insert = [];
                foreach ($data['work_range'] as $value){
                    $insert[] = [
                        'employee_id' => $model->id,
                        'start_time_hour' => $value['start_time_hour'],
                        'start_time_min' => $value['start_time_min'],
                        'end_time_hour' => $value['end_time_hour'],
                        'end_time_min' => $value['end_time_min'],
                        'total_work_min' => $value['total_work_min'],
                        'top_depart_id' => $value['top_depart_id'],
                        'crt_time' => time(),
                    ];
                }
                EmployeeWorkRange::insert($insert);
            }

            $old = EmployeeFile::where('del_time',0)
                ->where('employee_id',$model->id)
                ->pluck('file')
                ->toArray();
            EmployeeFile::where('del_time',0)
                ->where('employee_id',$model->id)
                ->update(['del_time' => $time]);

            $new = [];
            if(! empty($data['img_url'])){
                if(in_array($data['img_url'], $old)) {
                    foreach ($old as $o_k => $o_v){
                        if($o_v == $data['img_url']) unset($old[$o_k]);
                    }
                }else{
                    $new[] = $data['img_url'];
                }
                $insert[] = [
                    'employee_id' => $model->id,
                    'file' => $data['img_url'],
                    'crt_time' => $time,
                    'top_depart_id' => $user['top_depart_id'],
                ];
                EmployeeFile::insert($insert);
            }

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false, $exception->getMessage()];
        }

        return [true, ['file' => ['new' => $new, 'old' => $old]]];
    }

    public function employeeAdd($data,$user){
        list($status,$msg) = $this->employeeRule($data, $user);
        if(!$status) return [$status,$msg];

        try{
            DB::beginTransaction();
            $model = new Employee();
            $model->number = $data['number'] ?? "";
            $model->title = $data['title'] ?? "";
            $model->mobile = $data['mobile'] ?? "";
            $model->sex = $data['sex'] ?? 0;
            $model->education = $data['education'] ?? "";
            $model->id_card = $data['id_card'] ?? "";
            $model->major = $data['major'] ?? "";
            $model->position = $data['position'] ?? "";
            $model->p_title = $data['p_title'] ?? "";
            $model->state = $data['state'] ?? 0;
            $model->employee_type = $data['employee_type'] ?? 0;
            $model->crt_id = $user['id'];
            $model->is_admin = $data['is_admin'] ?? 0;
            $model->account = $data['account'] ?? "";
            $model->entrust_type = $data['entrust_type'] ?? 0;
            $model->man_type = $data['man_type'] ?? 0;
            if($model->is_admin) $model->password = Hash::make($data['password']);
            $model->top_depart_id = $data['top_depart_id'];
            $model->save();

            $time = time();
            if(isset($data['depart'])){
                $insert = [];
                foreach ($data['depart'] as $value){
                    $insert[] = [
                        'employee_id' => $model->id,
                        'depart_id' => $value,
                        'top_depart_id' => $user['top_depart_id'],
                    ];
                }
                EmployeeDepartPermission::insert($insert);
            }

            if(isset($data['role'])){
                $insert = [];
                foreach ($data['role'] as $value){
                    $insert[] = [
                        'employee_id' => $model->id,
                        'role_id' => $value,
                        'crt_time' => $time,
                    ];
                }
                EmployeeRole::insert($insert);
            }

            if(isset($data['work_range'])){
                $insert = [];
                foreach ($data['work_range'] as $value){
                    $insert[] = [
                        'employee_id' => $model->id,
                        'start_time_hour' => $value['start_time_hour'],
                        'start_time_min' => $value['start_time_min'],
                        'end_time_hour' => $value['end_time_hour'],
                        'end_time_min' => $value['end_time_min'],
                        'total_work_min' => $value['total_work_min'],
                        'top_depart_id' => $value['top_depart_id'],
                        'crt_time' => $time,
                    ];
                }
                EmployeeWorkRange::insert($insert);
            }

            $new = [];
            $insert = [];
            if(! empty($data['img_url'])){
                $insert[] = [
                    'employee_id' => $model->id,
                    'file' => $data['img_url'],
                    'top_depart_id' => $value['top_depart_id'],
                    'crt_time' => $time,
                ];
                EmployeeFile::insert($insert);
                $new[] = $data['img_url'];
            }

            DB::commit();
        }catch (Exception $e){
            DB::rollBack();
            return [false, $e->getMessage()];
        }

        return [true, ['file' => ['new' => $new]]];
    }

    public function employeeDel($data){
        if($this->isEmpty($data,'id')) return [false,'请选择删除的数据！'];

        try {
            DB::beginTransaction();
            $time = time();

            Employee::whereIn('id',$data['id'])->update([
                'del_time'=>$time
            ]);
            EmployeeRole::where('del_time',0)->whereIn('employee_id',$data['id'])->update([
                'del_time'=>$time
            ]);
            EmployeeDepartPermission::whereIn('employee_id',$data['id'])->delete();
            EmployeeWorkRange::whereIn('employee_id',$data['id'])->delete();

            $old = EmployeeFile::where('del_time',0)
                ->whereIn('employee_id',$data['id'])
                ->pluck('file')
                ->toArray();

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true,  ['file' => ['old' => $old]]];
    }

    public function employeeDetail($data, $user){
        if(empty($data['id'])) return [false,'人员id不能为空'];
        list($status, $return) = $this->employeeList(['id' => [$data['id']]], $user);
        $user = $return['data'][0] ?? [];
        list($img, $img2) = $this->showUserImg($data['id']);
        $user['img_url'] = $img;
        $user['img_url_show'] = $img2;

        return [true, $user];
    }

    public function getEmployeeImg($data, $user){
        list($img, $img_str) = $this->showUserImg($user['id']);
        return [true, ['img_url' => $img_str]];
    }

    private function showUserImg($id){
        $file = EmployeeFile::where('del_time',0)
            ->where('employee_id',$id)
            ->pluck('file')
            ->toArray();
        $file = $file[0] ?? "";
        $img_str = "";
        $timeStamp = 86400;
        if(! empty($file)){
            $fileUploadService = new FileUploadService();
            $img_str = $fileUploadService->getFileShow($file, $timeStamp);
        }

        return [$file, $img_str];
    }

    public function employeeCommon($data,$user, $field = []){
        if(empty($field)) $field = Employee::$field;

        $model = Employee::TopClear($user,$data);
        $model = $model->where('del_time',0)
            ->where('is_admin', '<=', Employee::IS_ADMIN_ONE)
            ->select($field)
            ->orderBy('id','desc');

        if(! empty($data['id'])) $model->whereIn('id', $data['id']);
        if(! empty($data['number'])) $model->where('number', 'LIKE', '%'.$data['number'].'%');
        if(! empty($data['title'])) $model->where('title', 'LIKE', '%'.$data['title'].'%');
        if(! empty($data['mobile'])) $model->where('mobile', 'LIKE', '%'.$data['mobile'].'%');
        if(isset($data['is_admin'])) $model->where('is_admin', $data['is_admin']);
        if(! empty($data['state'])) $model->where('state', $data['state']);
        if(isset($data['education'])) $model->where('education', $data['education']);
        if(! empty($data['role'])) {
            $emp = EmployeeRole::where('role_id',$data['role'])
                ->where('del_time',0)
                ->select('employee_id')->get()->toArray();
            $model->whereIn('id',array_column($emp,'employee_id'));
        }
        if(! empty($data['depart'])) {
            // 1. 获取所有子部门 ID（包含自身）
            $allDeparts = Depart::where('del_time', 0)->select('id', 'parent_id')->get();
            $departIds = array_merge($this->getAllDescendants($allDeparts->toArray(), $data['depart']), [$data['depart']]);

            // 2. 优化：直接使用子查询，避免拉取巨大的 $employee_id 数组到内存
            $model->whereIn('id', function ($query) use ($departIds) {
                $query->select('employee_id')
                    ->from('employee_depart_permission')
                    ->whereIn('depart_id', $departIds);
            });
        }

        return $model;
    }

    /**
     * 产品列表
     * @param $data
     * @param $user
     * @return array
     */
    public function employeeList($data,$user){
        $model = $this->employeeCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->organizationEmployeeData($list, $data, $user);

        return [true, $list];
    }

    public function organizationEmployeeData($data, $ergs, $user)
    {
        if (empty($data['data'])) return $data;

        // 获取员工ID并查询扩展数据
        $employee_ids = array_column($data['data'], 'id');
        list($status, $extraMap) = $this->getEmployee($employee_ids, $user);

        $map = [];
        if(isset($ergs['search_for_month_work'])) list($status, $map) = $this->getEmployeesMonthStats($employee_ids, $ergs['search_for_month_work'], $user);
        foreach ($data['data'] as &$item) {
            $id = $item['id'];
            $extra = $extraMap[$id] ?? null;

            $item['role']         = $extra['role_ids'] ?? [];
            $item['role_name']    = isset($extra['role_names']) ? implode(',', $extra['role_names']) : '';
            $item['depart']       = $extra['depart_ids'] ?? [];
            $item['depart_title'] = isset($extra['depart_names']) ? implode(',', $extra['depart_names']) : '';

            // 业务状态字段
            $item['is_admin_title'] = Employee::IS_ADMIN_TITLE[$item['is_admin']] ?? "";
            $item['state_title'] = Employee::State_Type[$item['state']] ?? "";
            $item['employee_type_title'] = Employee::E_State_Type[$item['employee_type']] ?? "";
            $item['sex_title'] = Employee::SEX_TYPE[$item['sex']] ?? "";
            $item['man_type_title'] = Employee::Man_Type[$item['man_type']] ?? "";
            $item['entrust_type_title'] = Employee::WT_Type[$item['entrust_type']] ?? "";
            $item['education_title'] = Employee::Education[$item['education']] ?? "";
            $item['crt_time']       = !empty($item['crt_time']) ? date("Y-m-d", $item['crt_time']) : "";
            if (isset($ergs['search_for_month_work'])) {
                $item['month_pw'] = []; // 默认空数组
                if ($status) {
                    $item['month_pw'] = $map[$item['id']] ?? [];
                }
            }
        }

        return $data;
    }

    public function getEmployeesMonthStats($employee_ids, $month, $user)
    {
        $topDepartId = $user['top_depart_id'];

        // 1. 月份初始化
        if(is_numeric($month)){
            $monthStart = $month;
        }else{
            $monthStart = $this->changeDateToDate($month);
            $monthStr = date("Y-m", $monthStart);
        }

        $endTime = strtotime("+1 month", $monthStart) - 1;

        // 2. 获取当月标准工作日天数 (基数)
        $standardWorkDays = DB::table('calendar_details')
            ->where('top_depart_id', $topDepartId)
            ->where('del_time', 0)
            ->where('time', '>=', $monthStart)
            ->where('time', '<=', $endTime)
            ->where('is_work', CalendarDetails::TYPE_ONE)
            ->count();

        if ($standardWorkDays <= 0) return [false, '工作日信息未设置'];

        // 3. 批量获取个性化工时设置
        $empWorkRanges = DB::table('employee_work_range')
            ->whereIn('employee_id', $employee_ids)
            ->where('top_depart_id', $topDepartId)
            ->select('employee_id', DB::raw('SUM(total_work_min) as total_min'))
            ->groupBy('employee_id')
            ->pluck('total_min', 'employee_id')
            ->toArray();

        // 4. 获取公司通用工时设置
        $commonWorkMin = DB::table('work_range_details')
            ->where('top_depart_id', $topDepartId)
            ->where('del_time', 0)
            ->sum('total_work_min');
        if(empty($commonWorkMin)) return [false, '公司工作时段未设置'];

        // 5. 批量获取请假与加班汇总
        $actualStats = DB::table('p_leave_over_order_details as d')
            ->join('p_leave_over_order as m', 'd.main_id', '=', 'm.id')
            ->whereIn('d.employee_id', $employee_ids)
            ->where('m.order_time', '>=', $monthStart)
            ->where('m.order_time', '<=', $endTime)
            ->where('m.del_time', 0)
            ->where('d.del_time', 0)
            ->select(
                'd.employee_id',
                DB::raw("SUM(CASE WHEN m.type = 1 THEN d.total_min ELSE 0 END) as leave_min"),
                DB::raw("SUM(CASE WHEN m.type = 2 THEN d.total_min ELSE 0 END) as overtime_min")
            )
            ->groupBy('d.employee_id')
            ->get()
            ->keyBy('employee_id');

        // 6. 核心逻辑汇总
        $result = [];
        foreach ($employee_ids as $empId) {
            // A. 确定该人员每日标准时长 (基数)
            $dailyStandardMin = isset($empWorkRanges[$empId]) ? (float)$empWorkRanges[$empId] : (float)$commonWorkMin;

            // 如果每日标准时长没设置，跳过该人防止除以0错误
            if ($dailyStandardMin <= 0) continue;

            // B. 获取加班和请假数据
            $empActual = $actualStats->get($empId);
            $leaveMin = $empActual ? (float)$empActual->leave_min : 0;
            $overtimeMin = $empActual ? (float)$empActual->overtime_min : 0;

            // 净增减工时（分）
            $netDiffMin = $overtimeMin - $leaveMin;

            // C. 计算出勤总工时 (标准总分钟 + 净增减)
            $standardMonthMin = $standardWorkDays * $dailyStandardMin;
            $finalWorkMin = $standardMonthMin + $netDiffMin;

            // D. 计算出勤总天数 (标准天数 + 净增减折算天数)
            // 计算公式：出勤天数 = (标准总分钟 + 加班分钟 - 请假分钟) / 每日标准分钟
            $finalAttendanceDays = round($finalWorkMin / $dailyStandardMin, 2);

            $result[$empId] = [
//                'standard_days'      => $standardWorkDays,     // 标准工作天数
                'attendance_days'    => $finalAttendanceDays,  // 出勤总天数 * (结算后)
//                'standard_month_min' => $standardMonthMin,     // 标准总工时 (分钟)
//                'final_work_min'     => $finalWorkMin,         // 出勤总工时 * (结算后分钟)
                'final_work_hour'    => round($finalWorkMin / 60, 2), // 出勤总工时 * (结算后小时)
//                'leave_min'          => $leaveMin,
//                'overtime_min'       => $overtimeMin,
//                'daily_standard_min' => $dailyStandardMin
            ];
        }

        return [true, $result];
    }

    public function getEmployee(array $employee_ids, $user)
    {
        if (empty($employee_ids)) return [false, []];

        // 1. 一次性获取所有角色
        $roles = DB::table('employee_role as a')
            ->join('role as b', 'a.role_id', '=', 'b.id')
            ->where('a.del_time', 0)
            ->where('b.del_time', 0)
            ->where('b.tree_type', $user['select_tree_type'])
            ->whereIn("a.employee_id", $employee_ids)
            ->select('a.employee_id', 'b.title', 'b.id')
            ->get();

        // 2. 一次性获取所有部门
        $departs = DB::table('employee_depart_permission as a')
            ->join('depart as b', 'a.depart_id', '=', 'b.id')
            ->whereIn("a.employee_id", $employee_ids)
            ->select('a.employee_id', 'b.title', 'b.id')
            ->orderBy('b.id')
            ->get();

        $work_range = DB::table('employee_work_range as a')
            ->whereIn("employee_id", $employee_ids)
            ->select('employee_id','start_time_hour', 'start_time_min', 'end_time_hour', 'end_time_min')
            ->get();

        // 3. 结果按员工ID分组归纳
        $resultMap = [];
        foreach ($employee_ids as $id) {
            $resultMap[$id] = [
                'role_ids' => [],
                'role_names' => [],
                'depart_ids' => [],
                'depart_names' => [],
                'work_range' => [],
            ];
        }

        foreach ($roles as $r) {
            $resultMap[$r->employee_id]['role_ids'][] = $r->id;
            $resultMap[$r->employee_id]['role_names'][] = $r->title;
        }

        foreach ($departs as $d) {
            $resultMap[$d->employee_id]['depart_ids'][] = $d->id;
            $resultMap[$d->employee_id]['depart_names'][] = $d->title;
        }

        foreach ($work_range as $d) {
            $resultMap[$d->employee_id]['work_range'][] = $d;
        }

        return [true, $resultMap];
    }

    public function fillDataForExport($data, $column, $user, &$return)
    {
        if (empty($data)) return;

        $dataArray = is_array($data) ? $data : $data->toArray();
        $mainIds = array_column($dataArray, 'id');

        // 1. 获取详情映射 [员工ID => [ [部门编码, 部门名称], [部门编码, 部门名称] ]]
        $detailsMap = $this->getEmployeeDetailsMap($mainIds, $user);

        // 枚举映射
        $sexMap   = Employee::SEX_TYPE;
        $eduMap   = Employee::Education;
        $stateMap = Employee::State_Type;
        $eMap = Employee::E_State_Type;

        foreach ($dataArray as $main) {
            $empId = $main['id'];
            $details = $detailsMap[$empId] ?? [];

            // 2. 格式化主表信息（这些是这一人所有行共有的内容）
            $mainInfo = $main;
            $mainInfo['sex_title']       = $sexMap[$main['sex']] ?? '';
            $mainInfo['education_title'] = $eduMap[$main['education']] ?? '';
            $mainInfo['state_title']     = $stateMap[$main['state']] ?? '';
            $mainInfo['employee_type_title']   = $eMap[$main['employee_type']] ?? '';

            if (empty($details)) {
                // 如果没部门，保底出一行
                $tempRow = [];
                foreach ($column as $col) {
                    $tempRow[] = $mainInfo[$col] ?? '';
                }
                $return[] = $tempRow;
            } else {
                // 3. 核心拆分点：遍历部门明细，每一个部门都产生一个新的 $tempRow
                foreach ($details as $sub) {
                    // 将员工基本信息与“当前这一个”部门信息合并
                    $fullRowData = array_merge($mainInfo, $sub);

                    $tempRow = [];
                    // 严格按配置顺序取值
                    foreach ($column as $col) {
                        $tempRow[] = $fullRowData[$col] ?? '';
                    }

                    // 【重点】这里是追加，不是覆盖！
                    // 如果张三有2个部门，这里会先后 push 两次，Excel 就会多出两行
                    $return[] = $tempRow;
                }
            }
        }
    }

    public function getEmployeeDetailsMap($empIds, $user)
    {
        // 关联查询权限表和部门档案
        $list = DB::table('employee_depart_permission as p')
            ->join('depart as d', 'p.depart_id', '=', 'd.id')
            ->whereIn('p.employee_id', $empIds)
            ->where('p.top_depart_id', $user['top_depart_id'])
            ->where('d.del_time', 0)
            ->select('p.employee_id', 'd.code as depart_code', 'd.title as depart_title')
            ->get();

        $res = [];
        foreach ($list as $item) {
            // 【重点】注意这里的 []，它保证了同一个 employee_id 下可以挂载多个部门数组
            $res[$item->employee_id][] = [
                'depart_code'  => $item->depart_code,
                'depart_title' => $item->depart_title,
            ];
        }
        return $res;
    }

    public function employeeRule(&$data, $user,$is_add = true){
        if(empty($data['number'])) return [false,'工号不存在'];
        if(empty($data['title'])) return [false,'姓名不存在'];
        if(! empty($data['sex']) && ! isset(Employee::SEX_TYPE[$data['sex']])) return [false, '性别不存在'];
        if(empty($data['mobile'])) return [false,'联系电话不能为空'];
//        if(! $this->isValidPhone($data['mobile'])) return [false, '手机号码格式错误'];
//        if(empty($data['major'])) return [false, '专业领域不能为空'];
        if(! empty($data['education']) && ! isset(Employee::Education[$data['education']])) return [false, '学历不存在'];
        if(empty($data['id_card'])) return [false, '身份证号不能为空'];
        if(empty($data['depart'])) return [false,'部门不能为空'];
        if(empty($data['state'])) return [false,'状态不能为空'];
        if(! isset(Employee::State_Type[$data['state']])) return [false,'状态不存在'];
        if(empty($data['employee_type'])) return [false,'聘用类型不能为空'];
        if(! isset(Employee::E_State_Type[$data['employee_type']])) return [false,'聘用类型不存在'];
        if(! empty($data['is_admin'])){
            if(! isset(Employee::IS_ADMIN_TITLE_SIMPLE[$data['is_admin']])) return [false, 'is_admin类型不存在'];
            if(empty($data['password'])) return [false, '密码不能为空'];
            if(mb_strlen($data['password']) < 6) return [false, '密码长度不得小于6位长度'];
            if(empty($data['role'])) return [false, '角色不能为空'];
        }
        if(empty($data['man_type'])) return [false,'是否技术人员不能为空'];
        if(! isset(Employee::Man_Type[$data['man_type']])) return [false,'是否技术人员不存在'];
        if(! isset($data['entrust_type'])) return [false,'委托方式不能为空'];
        if(! isset(Employee::WT_Type[$data['entrust_type']])) return [false,'委托方式不存在'];
        $data['top_depart_id'] = $user['top_depart_id'];

        if(! empty($data['work_range'])){
            foreach ($data['work_range'] as $key => $value){
                $res = $this->checkNumber($value['start_time_hour'], 0, 'non-negative');
                if(!$res['valid']) return [false, "开始点：" . $res['error']];
                if($value['start_time_hour'] > 23) return [false, false, "开始点不合法"];
                $res = $this->checkNumber($value['start_time_min'], 0, 'non-negative');
                if(!$res['valid']) return [false, "开始分：" . $res['error']];
                if($value['start_time_min'] > 60) return [false, false, "开始点不合法"];
                $res = $this->checkNumber($value['end_time_hour'], 0, 'non-negative');
                if(!$res['valid']) return [false, "结束点：" . $res['error']];
                if($value['end_time_hour'] > 24) return [false, false, "结束点不合法"];
                $res = $this->checkNumber($value['end_time_min'], 0, 'non-negative');
                if(!$res['valid']) return [false, "结束分：" . $res['error']];
                if($value['end_time_min'] > 60) return [false, false, "结束分不合法"];
                $data['work_range'][$key]['total_work_min'] = ($value['end_time_hour'] * 60 + $value['end_time_min']) - ($value['start_time_hour'] * 60 + $value['start_time_min']);
                $data['work_range'][$key]['top_depart_id'] = $data['top_depart_id'];
            }
        }
        if(! $is_add){
            if($this->isEmpty($data,'id')) return [false,'ID不能为空'];
            $bool = Employee::where('del_time',0)
                ->where('id','<>',$data['id'])
                ->where('top_depart_id', $data['top_depart_id'])
                ->where('number', $data['number'])
                ->exists();
        }else{
            $code = Depart::where('id', $user['top_depart_id'])->value('code');
            $data['account'] = $code . "_" . $data['number'];

            $bool = Employee::where('del_time',0)
                ->where('top_depart_id', $data['top_depart_id'])
                ->where('number', $data['number'])
                ->exists();
        }
        if($bool) return [false,'人员工号已存在'];

        return [true,''];
    }

    public function roleEdit($data,$user){
        list($status,$msg) = $this->roleRule($data,$user, false);
        if(!$status) return [$status,$msg];

        $model = new Role();
        $model = $model->where('id',$data['id'])->first();
        $model->title = $data['title'];
        $model->tree_type = $user['select_tree_type'];
        $model->save();

        return [true,''];
    }

    public function roleAdd($data,$user){
        list($status,$msg) = $this->roleRule($data,$user);
        if(!$status) return [$status,$msg];

        $model = new Role();
        $model->title = $data['title'] ;
        $model->top_depart_id = $data['top_depart_id'];
        $model->tree_type = $user['select_tree_type'];
        $model->save();

        return [true,''];
    }

    public function roleDel($data){
        if($this->isEmpty($data,'id')) return [false,'ID必须！'];

        $bool = EmployeeRole::where('del_time',0)
            ->whereIn('role_id',$data['id'])
            ->exists();
        if($bool) return [false,'角色已绑定人员'];

        try {
            DB::beginTransaction();
            $time = time();

            Role::where('id',$data['id'])->update([
                'del_time' => $time
            ]);

            RoleMenu::where('del_time',0)->where('role_id',$data['id'])->update([
                'del_time' => $time
            ]);

            RoleMenuButton::where('del_time',0)->where('role_id',$data['id'])->update([
                'del_time' => $time
            ]);

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true, ''];
    }

    public function roleList($data,$user){
        $select_tree_type = $user['select_tree_type'];
        $model = Role::TopClear($user,$data);
        $model = $model->where('del_time',0)
            ->where('tree_type', $select_tree_type)
            ->select('title','crt_time','id','upd_time')
            ->orderBy('id','desc');
        if(! empty($data['title'])) $model->where('title', 'LIKE', '%' . $data['title'] . '%');

        $list = $this->limit($model,'',$data);

        return [true, $list];
    }

    public function roleRule(&$data,$user, $is_check = true){
        if($this->isEmpty($data,'title')) return [false,'名称不能为空'];

        $data['top_depart_id'] = $user['top_depart_id'];

        if($is_check){
            $bool = Role::where('title',$data['title'])
                ->where('top_depart_id', $data['top_depart_id'])
                ->where('del_time',0)
                ->exists();
            if($bool) return [false,'角色名称已存在'];
        }else{
            if($this->isEmpty($data,'id')) return [false,'ID不能为空'];
            $top_depart_id = Role::where('id',$data['id'])->value('top_depart_id');
            $bool = Role::where('title',$data['title'])
                ->where('top_depart_id',$top_depart_id)
                ->where('id','<>',$data['id'])
                ->where('del_time',0)
                ->exists();
            if($bool) return [false,'角色名称已存在'];
        }

        return [true, ''];
    }

    public function roleMenu($data){
        if(empty($data['role_id']))  return [false,'角色不能为空！'];
        if(empty($data['menu'])) return [false,'菜单数据不能为空！'];

        DB::beginTransaction();
        try {
            RoleMenu::where('del_time',0)->where('role_id',$data['role_id'])->update(['del_time' => time()]);
            RoleMenuButton::where('del_time',0)->where('role_id',$data['role_id'])->update(['del_time' => time()]);

            $insert = $insert2 = [];
            foreach ($data['menu'] as $t){
                $insert[] = [
                    'role_id' => $data['role_id'],
                    'menu_id' => $t['menu_id'],
                    'type' => $t['type'],
                    'crt_time' => time()
                ];
                if(! empty($t['button'])){
                    foreach ($t['button'] as $b){
                        $insert2[] = [
                            'role_id' => $data['role_id'],
                            'menu_id' => $t['menu_id'],
                            'button_id' => $b,
                            'crt_time' => time()
                        ];
                    }
                RoleMenuButton::insert($insert2);
                }
            }
            RoleMenu::insert($insert);

            DB::commit();
        }catch (\Throwable $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true, ''];
    }

    public function roleDetail($data){
        if(empty($data['role_id'])) return [false,'请选择角色'];

        $role = Role::where('id',$data['role_id'])
            ->where('del_time',0)
            ->select('id','title')
            ->first();
        if(empty($role)) return [false,'角色不存在或已被删除'];
        $role = $role->toArray();

        $menu = RoleMenu::where('role_id',$data['role_id'])
            ->where('del_time',0)
            ->select('menu_id','type')
            ->get()->toArray();
        $button = $this->fillRoleButton([$data['role_id']]);
        foreach ($menu as $key => $value){
            $menu[$key]['button'] = $button[$value['menu_id']] ?? [];
        }
        $role['menu'] = $menu;

        return [true, $role];
    }

    public function departEdit($data, $user){
        list($status,$msg) = $this->departRule($data,$user,false);
        if(!$status) return [$status,$msg];

        $update = $msg['data'][0];
        $model = new Depart();
        $model->where('id',$data['id'])->update([
            'title' => $update['title'],
            'code' => $update['code'],
        ]);

        return [true, ''];
    }

    public function departAdd($data,$user){
        list($status,$msg) = $this->departRule($data,$user);
        if(!$status) return [$status,$msg];

        try {
            DB::beginTransaction();

            foreach ($msg['data'] as $value){
                $model = new Depart();
                $model->parent_id = $value['parent_id'];
                $model->title = $value['title'];
                $model->code = $value['code'];
                $model->top_depart_id = $value['top_depart_id'];
                $model->save();
            }

            DB::commit();
        }catch (\Exception $exception){
            DB::rollBack();
            return [false,$exception->getMessage()];
        }

        return [true,''];
    }

    public function departDel($data){
        list($status,$msg) = $this->checkDepartDel($data);
        if(! $status) return [false, $msg];

        Depart::whereIn('id',$data['id'])->update([
            'del_time'=>time()
        ]);

        return [true,''];
    }

    public function checkDepartDel($data){
        if($this->isEmpty($data,'id')) return [false,'ID不能为空'];

        $bool = Depart::whereIn('parent_id',$data['id'])->where('del_time',0)->exists();
        if($bool) return [false,'部门下有子部门'];

        if($this->checkDepartHasPerson($data['id'])) return [false,'部门下有人员档案'];

        return [true, ''];
    }

    public function departCommon($data, $user, $field = []){
        if(empty($field)) $field = Depart::$field;

        $model = Depart::TopClear($user,$data);
        $model = $model->where('del_time',0)
            ->select($field)
            ->orderby('id', 'asc');

        if(isset($data['parent_id'])) $model->where('parent_id', $data['parent_id']);
        if(! empty($data['title'])) $model->where('title', 'LIKE', '%'.$data['title'].'%');
        if(! empty($data['code'])) $model->where('code', 'LIKE', '%'.$data['code'].'%');

        return $model;
    }

    public function departList($data, $user){
        $model = $this->departCommon($data, $user);

        $list = $model->get()->toArray();
        $list = $this->fillDepartList($list, $user, true);
        $list_tree = $list;
        if(! empty($list_tree)) {
            $minParentId = min(array_column($list_tree, 'parent_id'));
            $list_tree = $this->makeTree($minParentId,$list_tree);
            $list_tree = $this->set_sort_circle($list_tree);
        }

        return [true,['data' => $list,'tree' => $list_tree]];
    }

    public function fillDepartList($list,$user, $is_export = false){
        if(isset($list['data'])){
            $data = $list['data'];
        }else{
            $data = $list;
        }
        if($is_export){
            $map = Depart::where('del_time',0)
                ->whereIn('id', array_column($data, 'parent_id'))
                ->select('code','id', 'title')
                ->get()->toArray();
            $map = array_column($map,null,'id');
            foreach ($data as $key => $value){
                $tmp = $map[$value['parent_id']] ?? "";
                if($tmp['code'] == $user['top_depart_code']) {
                    $code = $title = "";
                }else{
                    $code = $tmp['code'];
                    $title = $tmp['title'];
                }
                $data[$key]['parent_code'] = $code;
                $data[$key]['parent_title'] = $title;
            }
        }

        return $data;
    }


    public function departRule($data,$user, $is_check = true){
        if(empty($data['data'])) return [false,'数据不能为空！'];

        $code = array_column($data['data'],'code');
        $title = array_column($data['data'],'title');
        $code = array_map(function($val) {
            return $val !== null ? $val : 0;
        }, $code);
        $title = array_map(function($val) {
            return $val !== null ? $val : 0;
        }, $title);
        $code_count = array_count_values($code);
        $title_count = array_count_values($title);
        foreach ($code as $value){
            if(empty($value)) return [false,'编码不能为空！'];
            if($code_count[$value] > 1) return [false,'编码不能重复'];
        }
        foreach ($title as $value){
            if(empty($value)) return [false,'名称不能为空！'];
            if($title_count[$value] > 1) return [false,'名称不能重复'];
        }

        foreach ($data['data'] as $key => $value){
            $top_depart_id = $user['top_depart_id'];
            if(empty($value['parent_id'])) {
                $parent_id = $top_depart_id;
                $data['data'][$key]['parent_id'] = $parent_id;
            }

            $data['data'][$key]['top_depart_id'] = $top_depart_id;
            $data['data'][$key]['upd_time'] = time();

            if($is_check){
                $data['data'][$key]['crt_time'] = time();
                $bool = Depart::whereRaw("binary code = '{$value['code']}'")
                    ->where('top_depart_id', $top_depart_id)
                    ->where('del_time',0)
                    ->exists();
            }else{
                if($this->isEmpty($data,'id')) return [false,'id不能为空！'];
                $bool = Depart::whereRaw("binary code = '{$value['code']}'")
                    ->where('top_depart_id', $top_depart_id)
                    ->where('id','<>',$data['id'])
                    ->where('del_time',0)
                    ->exists();
            }
            if($bool) return [false,'部门编码已存在'];
        }

        return [true, $data];
    }

    public function checkDepartHasPerson($depart_id = []){
        if(empty($depart_id)) return false;

        $bool = EmployeeDepartPermission::from('employee_depart_permission as a')
            ->leftJoin('employee as b','b.id','a.employee_id')
            ->where('b.del_time',0)
            ->whereIn('a.depart_id',$depart_id)
            ->exists();

        return $bool;
    }

    public function fillRoleButton($role_id){
        $button = RoleMenuButton::whereIn('role_id',$role_id)
            ->where('del_time',0)
            ->select('menu_id','button_id')
            ->get()->toArray();
        $button_map = [];
        foreach ($button as $value){
            if(! isset($button_map[$value['menu_id']])){
                $button_map[$value['menu_id']][] = $value['button_id'];
            }else{
                if(! in_array($value['button_id'], $button_map[$value['menu_id']])) $button_map[$value['menu_id']][] = $value['button_id'];
            }
        }

        return $button_map;
    }

    public function getEmployeeMap($employee_ids){
        $employee_ids = array_filter((array)$employee_ids);
        if (empty($employee_ids)) return [];

        return Employee::whereIn('id', $employee_ids)
            ->pluck('title', 'id')
            ->toArray();
    }
}