workHour/app/Service/StatisticService.php

<?php

namespace App\Service;

use App\Model\AuxiliaryAccountDetails;
use App\Model\Depart;
use App\Model\Device;
use App\Model\DeviceDepartDetails;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\ExpenseClaimsDetails;
use App\Model\Fee;
use App\Model\Item;
use App\Model\ItemDetails;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPsOrderDetails;
use App\Model\MonthlyPwOrder;
use App\Model\MonthlyPwOrderDetails;
use App\Service\Statistic\StatisticCommonService;
use Illuminate\Support\Facades\DB;

class StatisticService extends StatisticCommonService
{
    public function employeeDayHourStatistic($data, $user)
    {
        // 1. 验证并获取月份边界
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 2. 获取原始数据
        $dayEmployeeList = $this->getItemEmployeeDayWorkList($user, $data, $month_start, $month_end);
        if (empty($dayEmployeeList)) {
            return [true, []];
        }

        // 3. 提取 ID 批量查询关联数据（合并 Item 查询）
        $itemIds = array_unique(array_filter(array_column($dayEmployeeList, 'item_id')));
        $employeeIds = array_unique(array_filter(array_column($dayEmployeeList, 'employee_id')));

        $items = Item::whereIn('id', $itemIds)->get(['id', 'title', 'code'])->keyBy('id');
        $employeeKeyList = Employee::whereIn('id', $employeeIds)->pluck('title', 'id')->toArray();

        // 4. 【核心优化】单次遍历：同时计算 count、sum 并补全基础信息
        $employeeCounts = [];
        $sums = [];

        foreach ($dayEmployeeList as &$item) {
            $employeeId = $item['employee_id'] ?? null;
            $itemId = $item['item_id'] ?? null;
            $orderDate = $item['order_date'] ?? '';

            // 补全名称（避免在 map 里重复调用集合）
            $item['employee_name'] = $employeeKeyList[$employeeId] ?? "未知员工({$employeeId})";
            $itemProject = $items->get($itemId);
            $item['item_title'] = $itemProject->title ?? "未知项目({$itemId})";
            $item['item_code'] = $itemProject->code ?? "未知项目({$itemId})";

            // 组装唯一 Key: "employeeId_orderDate"
            $groupKey = "{$employeeId}_{$orderDate}";

            // 累加计数
            $employeeCounts['employee_work_count'][$groupKey] = ($employeeCounts['employee_work_count'][$groupKey] ?? 0) + 1;

            // 累加小时工时（转为分位整数存储，防止浮点数失真）
            $hours = round(($item['total_work'] ?? 0) / 60, 2);
            $sums['employee_work_count'][$groupKey] = ($sums['employee_work_count'][$groupKey] ?? 0) + (int)round($hours * 100);
        }
        unset($item); // 释放引用避免后续污染

        $wordKeys = [
            "employee_work_count" => [
                "key" => "total_work",
                "value" => "total_work_hours",
                "type" => "hour",
            ]
        ];

        // 5. 第二次遍历：倒扣尾差计算
        // 因为这里需要倒扣，必须按顺序执行 calculateClosure
        foreach ($dayEmployeeList as &$item) {
            $groupKey = "{$item['employee_id']}_{$item['order_date']}";
            $this->calculateClosure($groupKey, $item, $employeeCounts, $sums, $wordKeys);
        }
        unset($item);

        return [true, $dayEmployeeList];
    }

    public function employeeDayHourStatistic1($data, $user)
    {
        //传参月份、项目编码、项目名称 不允许跨年查询月份
        //项目编码、项目名称、人员名称、工时、日期
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        $day_employee_list = $this->getItemEmployeeDayWorkList($user,$data,$month_start,$month_end);
        $dataCollection = collect($day_employee_list);
        $item_ids = $dataCollection->pluck('item_id')->unique()->values()->all();
        $employee_ids = $dataCollection->pluck('employee_id')->unique()->values()->all();
        $employee_key_list = Employee::wherein('id', $employee_ids)->pluck("title", "id")->toArray();
        $item_title_key_list = Item::wherein('id', $item_ids)->pluck("title", "id")->toArray();
        $item_code_key_list = Item::wherein('id', $item_ids)->pluck("code", "id")->toArray();
        $keys = ["employee_id","order_date"];
        $employee_count = $this->calculateCount($day_employee_list,$keys);
        $employee_work_count = $this->calculateSumForHour($day_employee_list,$keys,"total_work");
        $collection = collect($day_employee_list);
        $sums =  ["employee_work_count"=>$employee_work_count];
        $word_keys = [
            "employee_work_count" =>
                [
                    "key" => "total_work",
                    "value" => "total_work_hours",
                    "type" => "hour",
                ]
        ];
        $employee_counts = ['employee_work_count' => $employee_count];
        $month_employee_list = $collection->transform(function ($item) use ($employee_key_list, $item_title_key_list, $item_code_key_list, &$employee_work_count, &$employee_counts,$keys,$word_keys,&$sums) {
            $item['employee_name'] = $employee_key_list[$item['employee_id']] ?? "未知员工({$item['employee_id']})";
            $item['item_title'] = $item_title_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['item_code'] = $item_code_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            // 如果不是最后一条
            $key = collect($keys)->map(fn($k) => $item[$k] ?? '')->implode('_');

            $this->calculateClosure($key,$item,$employee_counts,$sums,$word_keys);
            return $item;
        })->all();
        return [true, $month_employee_list];
    }

    public function employeeMonthSalaryStatistic($data, $user)
    {
        //项目编码、项目名称、天数、工资、日期
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //确认所有项目、人员、人员工时
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有人员工资
        $salary_map = $this->getEmployeeSalary($user, $data, $month_start, $month_end);
        //查询所有项目人员的工时比例
        // 2. 计算每个员工在每个月的全月总工时
        $keys = ["employee_id","order_month"];
        $employee_monthly_total_min = $this->calculateSum($month_employee_list,$keys,"total_work");

        // 3. 计算分摊比例
        list($item_month_list,$all_salary) = $this->calculateRatioForMonth($month_employee_list,$employee_monthly_total_min,$salary_map,["employee_id","order_month"],["order_month","item_id"]);
        //计算天数
        foreach ($item_month_list as $k => $v) {
            $item_month_list[$k]['days'] = round($v['work_minutes'] / 8 / 60);
        }

        $item_month_list = collect($item_month_list)->sortBy('month')->values()->all();
        $items = collect($item_month_list)->pluck('item_id')->unique()->values()->all();
        $item = Item::TopClear($user, $data);
        $item_title_key_list = $item->wherein('id', $items)->pluck("title", "id")->toArray();
        $item_code_key_list = $item->wherein('id', $items)->pluck("code", "id")->toArray();

        $collect = collect($item_month_list);

        $item_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();
        $item_day_count = $collect->groupBy(fn($item) => $item['month'])
            ->map(fn($group) => round($group->sum('work_minutes') / 8 / 60)*100)
            ->toArray();
//        $all_salary = collect($salary_map)
//            ->groupBy(function ($value, $key) {
//                // 1. 提取下划线后面的内容 (例如: 2024-02)
//                return explode('_', $key)[1];
//            })
//            ->map(function ($group) {
//                // 2. 对每个分组内的数值进行求和
//                return $group->sum();
//            })
//            ->toArray();
        $item_counts = [
            "all_salary" => $item_count,
            "item_day_sum" => $item_count,
        ];
        $sums =  ["item_day_sum"=>$item_day_count,"all_salary"=>$all_salary];
        $word_keys = [
            "all_salary" =>
                [
                    "key" => "allocated_salary",
                    "value" => "allocated_salary",
                    "type" => "money",
                ],
            "item_day_sum" =>
                [
                    "key" => "days",
                    "value" => "days",
                    "type" => "",
                ],
        ];
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, &$item_counts,&$sums,&$word_keys) {
            $item['item_title'] = $item_title_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['item_code'] = $item_code_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $key = $item['month'];
            $this->calculateClosure($key,$item,$item_counts,$sums,$word_keys);
            return $item;
        })->all();
        return [true, $item_month_list];
    }

    public function itemDaySalaryStatistic($data, $user)
    {
        //项目编码、项目名称、人员名称、研发工时、研发工资、当月总工时、总计工资、年月
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //确认所有项目、人员、人员工时
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有人员工资
        $salary_map = $this->getEmployeeSalary($user, $data, $month_start, $month_end);
        // 2. 计算每个员工在每个月的全月总工时
        $keys = ["employee_id","order_month"];
        $employee_monthly_total_min = $this->calculateSum($month_employee_list,$keys,"total_work");
        //查询所有项目人员的工时比例
        // 3. 计算分摊比例
        list($item_month_list,) = $this->calculateRatioForMonth($month_employee_list,$employee_monthly_total_min,$salary_map,["employee_id","order_month"],["order_month","item_id","employee_id"]);
        $collect = collect($item_month_list);
        $employee_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['employee_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();

        $item_month_list = collect($item_month_list)->sortBy('month')->values()->all();
        $items = collect($item_month_list)->pluck('item_id')->unique()->values()->all();
        $item = Item::TopClear($user, $data);
        $item_title_key_list = $item->wherein('id', $items)->pluck("title", "id")->toArray();
        $item_code_key_list = $item->wherein('id', $items)->pluck("code", "id")->toArray();

        $employee_ids = collect($item_month_list)->pluck('employee_id')->unique()->values()->all();

        $employee = Employee::TopClear($user, $data);
        $employee_key_list = $employee->wherein('id', $employee_ids)->pluck("title", "id")->toArray();

        $month_total_hour = collect($employee_monthly_total_min)->map(function ($value) {
            // 逻辑：除以 60，取两位小数，乘以 100
            // 注意：round 会根据你的精度要求处理小数
            return round($value / 60) * 100;
        })->toArray();
        $sums =  ["work_hours"=>$month_total_hour,"allocated_salary"=>$salary_map];
        $word_keys = [
            "allocated_salary" =>
                [
                    "key" => "allocated_salary",
                    "value" => "allocated_salary",
                    "type" => "100b",
                ],
            "work_hours" =>
                [
                    "key" => "work_minutes",
                    "value" => "work_hours",
                    "type" => "hour",
                ],
        ];
        $counts = [];
        foreach ($word_keys as $k=>$v){
            $counts[$k] = $employee_count;
        }
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, $employee_key_list, &$sums, &$counts,$word_keys) {
            $item['item_title'] = $item_title_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['item_code'] = $item_code_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['employee_title'] = $employee_key_list[$item['employee_id']] ?? "未知人员({$item['employee_id']})";
//            $item['total_hours'] = round($item['work_minutes'] / 60,2);
            $item['total_salary'] = round($item['total_salary'] / 100,2);
            $key = $item['employee_id'] . '_' . $item['month'];
//            // 如果不是最后一条

            $this->calculateClosure($key,$item,$counts,$sums,$word_keys);
            return $item;
        })->all();
        return [true, $item_month_list];
    }

    public function itemDeviceMonthStatistic($data, $user)
    {
        //项目编码、项目名称、设备名称、项目工时、研发工时占比、设备原值、设备折旧额、本项目帐面归集的折旧额、确定的本项目折旧额、加计调整金额、当月工时、日期
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //确认所有项目、设备、设备工时
        $month_device_list = $this->getItemDeviceMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有设备工资
        $depreciation_map = $this->getDeviceAmount($user, $data, $month_start, $month_end);
        // 2. 计算每个设备在每个月的全月总工时
        $keys = ["device_id","order_month"];
        $device_monthly_total_min = $this->calculateSum($month_device_list,$keys,"total_work");
        // 3. 计算设备分摊天数与工资
        list($item_month_list,$device_total_depreciation) = $this->calculateDeviceRatioForMonth($month_device_list,$device_monthly_total_min,$depreciation_map,["device_id","order_month"],["order_month","item_id","device_id"]);
        $item_month_list = collect($item_month_list)->sortBy('month')->values()->all();
        $items = collect($item_month_list)->pluck('item_id')->unique()->values()->all();
        $item = Item::TopClear($user, $data);
        $item_title_key_list = $item->wherein('id', $items)->pluck("title", "id")->toArray();
        $item_code_key_list = $item->wherein('id', $items)->pluck("code", "id")->toArray();
        $device_ids = collect($item_month_list)->pluck('device_id')->unique()->values()->all();

        $device = Device::TopClear($user, $data);
        $devices = $device->whereIn('id', $device_ids)
            ->get(['id', 'title', 'total_depreciation', 'in_time', 'device_type']);
        $timestamps = $devices->pluck('in_time')->unique()->toArray();
        $yearsMap = $this->getBatchDiffYears($timestamps);
        $depart_map = $this->getDepart($devices->pluck('id')->unique()->toArray());

        $device_key_list = [];
        foreach ($devices as $d) {
            $useYear = $d->in_time ? $yearsMap[$d->in_time] : 0;
            $depart_title = $depart_map[$d->id] ?? "";

            $device_key_list[$d->id] = [
                'id'           => $d->id,
                'title'        => $d->title,
                'now_depreciation' => $d->total_depreciation,
                'in_time'      => $d->in_time,
                'device_type_title'  => Device::$device_type[$d->device_type] ?? "",
                'use_year'     => $useYear,
                'depart_title'     => $depart_title,
            ];
        }
//        $device_key_list = $device->wherein('id', $device_ids)->pluck("title", "id")->toArray();
        $device_original_value_key_list = $device->wherein('id', $device_ids)->pluck("original_value", "id")->toArray();

        $collect = collect($item_month_list);

        $word_keys = [
            "total_depreciation" =>
                [
                    "key" => "allocated_depreciatio",
                    "value" => "allocated_depreciatio",
                    "type" => "money",
                ],
            "total_hours" =>
                [
                    "key" => "hours",
                    "value" => "hours",
                    "type" => "",
                ],
            "ratio" =>
                [
                    "key" => "ratio",
                    "value" => "ratio",
                    "type" => "ratio",
                ],
        ];
        $device_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['device_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();
        $device_counts = [];
        foreach ($word_keys as  $k=>$v){
            $device_counts[$k] = $device_count;
        }
        $device_total_depreciation['ratio'] = [];
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, $device_key_list, $device_original_value_key_list, &$device_total_depreciation, &$device_counts,&$rate_list,$word_keys) {
            $item['item_title'] = $item_title_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['item_code'] = $item_code_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $tmp = $device_key_list[$item['device_id']] ?? [];
            $item['device_title'] = $tmp['title'] ?? "";
            $item['device_type_title'] = $tmp['device_type_title'] ?? "";
            $item['use_year'] = $tmp['use_year'] ?? 0;
            $item['depart_title'] = $tmp['depart_title'] ?? "";
            $item['now_depreciation'] = $tmp['now_depreciation'] ?? 0;
            $item['allocated_depreciatio'] = $item['allocated_depreciation'];
            $item['total_depreciatio'] =  round($item['total_depreciation']/100,2);
            $item['device_original'] = $device_original_value_key_list[$item['device_id']] ?? "未知人员({$item['device_id']})";
            $item['hours'] = round($item['work_minutes'] / 60, 1);
            $item['total_hours'] = round($item['total_min'] / 60, 1);

            $key = $item['device_id'] . '_' . $item['month'];
            $this->calculateClosure($key,$item,$device_counts,$device_total_depreciation,$word_keys);
            return $item;
        })->all();
        return [true, $item_month_list];
    }

    public function getDepart($id){
        $details = DeviceDepartDetails::where('del_time', 0)
            ->whereIn('main_id', $id)
            ->select(['main_id as device_id', 'depart_id'])
            ->get();
        $departIds = $details->pluck('depart_id')->unique()->toArray();
        $departMap = Depart::whereIn('id', $departIds)
            ->pluck('title', 'id')
            ->toArray();

        $result = [];

        foreach ($details as $value) {
            $deviceId = $value['device_id'];
            $departId = $value['depart_id'];

            $t = $departMap[$departId] ?? '';
            if(empty($t)) continue;

            if(isset($result[$deviceId])){
                $result[$deviceId] .= ',' . $t;
            }else{
                $result[$deviceId] = $t;
            }
        }

        return $result;
    }

    public function employeeAttendanceMonthStatistic($data, $user)
    {
        //项目编码、项目名称、项目状态、支出类型、允许加计扣除金额合计、人员人工费用、折旧费用、其他费用、前N项小计、其他相关费用合计、委内费用、委外费用、
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) {
            return [false, $month_start];
        }
        //第一步确定项目
        $item = Item::TopClear($user, $data);
//        $item_list = $item->where('del_time', 0)
//            ->where(function ($query) use ($month_start, $month_end) {
//                $query->where('start_time', '>=', $month_start)
//                    ->where('end_time', '<', $month_end);
//            })->select("code", "title", "start_time", "end_time", "id", "state")->orderby("start_time", "asc")->get()->toArray();

        $item_list = $item->where('del_time', 0)
            /*->where(function ($query) use ($month_start, $month_end) {
                $query->where('start_time', '>=', $month_start)
                    ->orWhere('end_time', '<', $month_end);
            })*/->where('end_time', '<', $month_end)->where('end_time', '>=', $month_start)->select("code", "title", "start_time", "end_time", "id", "state")->orderby("start_time", "asc")->get()->toArray();
        $item_key_list = [];
        foreach ($item_list as $v) {
            $item_key_list[$v['id']] = $v;
        }
        //第二步确定人员费用
        $item_employee_list = $this->getEmployeeItemSalary($month_start, $month_end, $data, $user);
        //第三步确定折旧费用
        $item_device_list = $this->getDeviceItemSalary($month_start, $month_end, $data, $user);
        //第四步其他费用
        list($item_fee_list, $fee_type_list) = $this->getFeeItemSalary($month_start, $month_end, $data, $user);
//        dd($item_device_list);
        //组合所有数据
        $return = [];
        foreach ($item_key_list as $v) {
            $other =  collect($item_fee_list[$v['id']] ?? [])->values()->all();
            $employee_salary = isset($item_employee_list[$v['id']]['salary']) ?  round($item_employee_list[$v['id']]['salary'],2) : 0;
            $device_depreciation = isset($item_device_list[$v['id']]['allocated_depreciation']) ? round($item_device_list[$v['id']]['allocated_depreciation'],2): 0;
            $other_amount = 0;
            $total_amount = $employee_salary + $device_depreciation;
            $jj_total_amount = $employee_salary + $device_depreciation;
            foreach ($other as $vv){
                if($vv['is_other'] == 1) $other_amount += $vv['total_amount'];
                else {
                    $total_amount += $vv['total_amount'];
                    $jj_total_amount += $vv['total_amount'];
                }
            }
            //限额调整其他费用
            $jj_other_amount = round(($total_amount+$other_amount)*0.2);
            if($other_amount <=  $jj_other_amount)  $jj_other_amount = $other_amount;

            //加计其他费用
            $jj_amount = ($total_amount+$other_amount)*0.1;
            if($other_amount <=  $jj_amount)  $jj_amount = $other_amount;
            //允许加计扣除项 ： 其他费用 <= （其他费用+费用）*10% +费用
            // 经限额调整后的其他相关费用 ： 其他费用 <= (其他费用+费用)*20%
            //其他费用是个数组
            $item_value = [
                "code" => $v['code'],
                "title" => $v['title'],
                "state" => $v['state'] == 3 ? "完结" : "进行中",
                "employee_salary" => $employee_salary,
                "device_depreciation" => $device_depreciation,
                "expense_type" => "费用化支出",
                "fee_list" => $other,
                "other_amount" => $other_amount,
                "jj_other_amount" => $jj_other_amount,
                "jj_total_amount" => round($jj_amount+$jj_total_amount,2),
                "start_time" => date('Y-m-d', $v['start_time']),
                "end_time" => date('Y-m-d', $v['end_time']),
            ];
            $return[] = $item_value;
        }

        return [true, ["list" => $return, "fee_type_list" => array_values($fee_type_list)]];

    }

    private function getEmployeeItemSalary($month_start, $month_end, $data, $user)
    {
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有人员工资
        $salary_map = $this->getEmployeeSalary($user, $data, $month_start, $month_end);
        // 2. 计算每个员工在每个月的全月总工时
        $employee_monthly_total_min = $this->calculateSum($month_employee_list,["employee_id","order_month"],"total_work");
        // 2. 计算分摊天数与工资
        list($item_list,) = $this->calculateRatioForMonth($month_employee_list,$employee_monthly_total_min,$salary_map,["employee_id","order_month"],["order_month","item_id","employee_id"]);
        $collect = collect($item_list);
        $employee_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['employee_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();

        $sums = [
            "salary_map" => $salary_map
        ];
        $employee_counts = [
            "salary_map" => $employee_count
        ];
        $word_keys = [
            "salary_map" => [
                "key" => "allocated_salary",
                "value" => "allocated_salary",
                "type" => "money",
            ]
        ];

        $month_employee_list = $collect->transform(function ($item) use (&$employee_counts,&$sums,$word_keys) {

            // 如果不是最后一条
            $key = $item['employee_id'] . '_' . $item['month'];
            $this->calculateClosure($key,$item,$employee_counts,$sums,$word_keys);
            return $item;
        })->all();

        $return_item_list = [];
        foreach ($month_employee_list as $v){
            $key = $v['item_id'];
            if(!isset($return_item_list[$key])) $return_item_list[$key]['salary'] = 0;
            $return_item_list[$key]['salary'] += $v['allocated_salary'];
        }
        return $return_item_list;
    }

    private function getDeviceItemSalary($month_start, $month_end, $data, $user)
    {
        //确认所有项目、设备、设备工时
        $month_device_list = $this->getItemDeviceMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有设备工资
        $month_device_salary = $this->getDeviceAmount($user, $data, $month_start, $month_end);
        //查询所有项目人员的工时比例
        // 2. 计算每个设备在每个月的全月总工时
        $keys = ["device_id","order_month"];
        $device_monthly_total_min = $this->calculateSum($month_device_list,$keys,"total_work");
        //汇总设备每个月损耗金额
        list($item_list,) = $this->calculateDeviceRatioForMonth($month_device_list,$device_monthly_total_min,$month_device_salary,["device_id","order_month"],["order_month","item_id","device_id"]);
        $collect = collect($item_list);
        $device_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['device_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();

        $sums = [
            "salary_map" => $month_device_salary
        ];
        $word_keys = [
            "salary_map" => [
                "key" => "allocated_depreciation",
                "value" => "allocated_depreciation",
                "type" => "money",
            ]
        ];
        $device_counts = [
            "salary_map" => $device_count
        ];
        $month_employee_list = $collect->transform(function ($item) use (&$device_counts,&$sums,$word_keys) {

            // 如果不是最后一条
            $key = $item['device_id'] . '_' . $item['month'];

            $this->calculateClosure($key,$item,$device_counts,$sums,$word_keys);
            return $item;
        })->all();
        $return_item_list = [];
        foreach ($month_employee_list as $v){
            $key = $v['item_id'];
            if(!isset($return_item_list[$key])) $return_item_list[$key]['allocated_depreciation'] = 0;
            $return_item_list[$key]['allocated_depreciation'] += $v['allocated_depreciation'];
        }
        return $return_item_list;
    }

    private function getFeeItemSalary($month_start, $month_end, $data, $user)
    {
        //确认所有项目、费用
        $expense = ExpenseClaimsDetails::Clear($user, $data);
        $expense_list = $expense->where("claim_date", ">=", $month_start)->where("claim_date", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "fee_id",
                "amount",
                "item_id",
                "entrust_type"
            )->get()->toArray();
        //需要根据分类去汇总
        $fee = Fee::TopClear($user, $data);
        $fee = $fee->where('del_time', 0)->orderBy("sort", 'desc')->get()->toArray();
        return $this->groupListByRoot($expense_list, $fee);
    }

    /**
     * 将报销明细按照一级费用和项目分类进行分组
     * * @param array $list 报销明细列表 (含 fee_id, amount 等)
     * @param array $fee_type_list 费用类型树 (含 id, parent_id, title)
     * @return array
     */
    public function groupListByRoot(array $list, array $fee_type_list)
    {
        // 1. 建立 ID 索引，方便快速查找
        $idMap = array_column($fee_type_list, null, 'id');

        // 2. 预处理映射表：让所有子 ID 直接指向它的最顶层“祖宗” ID
        $childToRoot = [];
        foreach ($fee_type_list as $type) {
            $current = $type;
            // 向上追溯直到 parent_id 为 0，即找到一级分类
            while ($current['parent_id'] != 0) {
                $current = $idMap[$current['parent_id']];
            }
            $childToRoot[$type['id']] = [
                'id' => $current['id'],
                'title' => $current['title'],
                'sort' => $current['sort'],
                'is_other' => $current['is_other'],
            ];
        }

        // 3. 遍历明细数据进行分组
        $item_key_list = [];
        $type_list = [];
        foreach ($list as $item) {
            $feeId = $item['fee_id'];
            // 获取该费用对应的一级分类信息
            if (!isset($childToRoot[$feeId])) continue;
            $rootId = $childToRoot[$feeId]['id'];
            $title = $childToRoot[$feeId]['title'];
            $sort = $childToRoot[$feeId]['sort'];
            $other = $childToRoot[$feeId]['is_other'];
            $key = $item['item_id'];

            if (!isset($item_key_list[$key][$rootId])) {
                $item_key_list[$key][$rootId] = [
                    'id' => $rootId,
                    'total_amount' => 0,
                    'entrust1_amount' => 0, //委内
                    'entrust2_amount' => 0, //委外
                    'is_other' => $other,

                ];
            }
            if ($item['entrust_type'] == 1) {
                $item_key_list[$key][$rootId]['entrust1_amount'] += $item['amount']*100;
            } elseif ($item['entrust_type'] == 2) {
                $item_key_list[$key][$rootId]['entrust2_amount'] += $item['amount']*100;
            }else{
                $item_key_list[$key][$rootId]['total_amount'] += $item['amount']*100;
            }
            //这边需要拿到头部所有的一级费用类型
            if (!isset($type_list[$rootId])) {
                $type_list[$rootId] = [
                    'sort' => $sort,
                    'id' => $rootId,
                    'title' => $title,
                    'is_after' => $other,
                ];
            }

        }
//        var_dump($item_key_list);die;
        foreach ($item_key_list as $k=>$v){
            foreach ($v as $kk=>$vv){
                $item_key_list[$k][$kk]['entrust1_amount'] = round( $vv['entrust1_amount']/100,2);
                $item_key_list[$k][$kk]['entrust2_amount'] = round( $vv['entrust2_amount']/100,2);
                $item_key_list[$k][$kk]['total_amount'] = round( $vv['total_amount']/100,2);
            }
        }
//        $item_key_list = collect($item_key_list)->transform(function ($item) {
//           $item['entrust1_amount'] = round( $item['entrust1_amount']/100,2);
//           $item['entrust2_amount'] = round( $item['entrust2_amount']/100,2);
//           $item['total_amount'] = round( $item['total_amount']/100,2);
//            return $item;
//        })->all();
        // 使用 values() 丢弃原始键名，重新从 0 开始建立索引
        $type_list = collect($type_list)->sortBy('sort')->values()->all();
        // 4. 重置数组索引并返回
        return [$item_key_list, $type_list];
    }

    public function auxiliaryStatistic($data, $user)
    {
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //项目编码、项目名称、项目状态、凭证日期、凭证种类、凭证号数、凭证摘要、会计凭证归集金额、N个一级费用类型、委内、委外

        //确认所有项目
        $item = Item::TopClear($user, $data);
        $item_list = $item->where('del_time', 0)
            /*->where(function ($query) use ($month_start, $month_end) {
                $query->where('start_time', '>=', $month_start)
                    ->orWhere('end_time', '<', $month_end);
            })*/->where('end_time', '<', $month_end)->where('end_time', '>=', $month_start)->select("code", "title", "start_time", "end_time", "id", "state")->orderby("start_time", "asc")->get()->toArray();
        $item_key_list = [];
        foreach ($item_list as $v) {
            $item_key_list[$v['id']] = $v;
        }
        //获取该区间内所有项目人工费、折旧费
        $item_salary = $this->auxiliaryEmployee($user, $data, $month_start, $month_end);
        $device_depreciation = $this->auxiliaryDevice($user, $data, $month_start, $month_end);
        $fee = Fee::TopClear($user, $data);
        $fee = $fee->where('del_time', 0)->orderBy("sort", 'desc')->get()->toArray();
        $auxiliary = AuxiliaryAccountDetails::Clear($user, $data);
        $auxiliary_list = $auxiliary->where("voucher_date", ">=", $month_start)->where("voucher_date", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "voucher_date",
                "voucher_type",
                "voucher_no",
                "voucher_remark",
                "voucher_amount",
                "aggregation_amount",
                "entrust_type",
                "entrust1_amount",
                "entrust2_amount",
                "entrust2_amount",
                "total_amount",
                "fee_id",
                "type"
            )->get()->toArray();
        list($fee_amount, $fee_type_list) = $this->auxiliaryGroupListByRoot($auxiliary_list, $fee);
//        dd($item_key_list);
        //找到项目和设备的费用然后进行比例计算

        $return = [];
        foreach ($fee_amount as $v) {
            //人工费用
            if ($v['type'] == 1 || $v['type'] == 2) {
//                var_dump();
                foreach ($item_key_list as $vv) {
                    if(!isset($item_salary[$vv['id'] . "_" . date("Y-m", $v['voucher_date'])]['allocated_salary'])) continue;
                    $detail = [
                        "code" => $vv['code'],
                        "title" => $vv['title'],
                        "state" => $vv['state'] == 3 ? "进行中" : "已完成",
                        "voucher_date" => date("Y-m-d", $v['voucher_date']),
                        "voucher_type" => $v['voucher_type'],
                        "voucher_no" => $v['voucher_no'],
                        "voucher_remark" => $v['voucher_remark'],
                        "voucher_amount" => $v['voucher_amount'],
                        "aggregation_amount" => $v['aggregation_amount'],
                        "total_amount" => $v['type'] == 1 ?round( $item_salary[$vv['id'] . "_" . date("Y-m", $v['voucher_date'])]['allocated_salary'],2) : (isset($device_depreciation[$vv['id'] . "_" . date("Y-m", $v['voucher_date'])]['depreciation']) ?round($device_depreciation[$vv['id'] . "_" . date("Y-m", $v['voucher_date'])]['depreciation'],2) : 0),
                        "fee_id" => $v['fee_id'],
                        "entrust1_amount" => $v['entrust1_amount'],
                        "entrust2_amount" => $v['entrust2_amount'],
                        "type" => $v['type'],
                        "start_time" => date('Y-m-d', $vv['start_time']),
                        "end_time" => date('Y-m-d', $vv['end_time']),
                    ];
                    $return[] = $detail;
                }

            } else {
                if (!isset($item_key_list[$v['item_id']])) continue;
                $item_value = $item_key_list[$v['item_id']];
                $detail = [
                    "code" => $item_value['code'],
                    "title" => $item_value['title'],
                    "state" => $item_value['state'] == 3 ? "进行中" : "已完成",
                    "voucher_date" => date("Y-m-d", $v['voucher_date']),
                    "voucher_type" => $v['voucher_type'],
                    "voucher_no" => $v['voucher_no'],
                    "voucher_remark" => $v['voucher_remark'],
                    "voucher_amount" => $v['voucher_amount'],
                    "aggregation_amount" => $v['aggregation_amount'],
                    "total_amount" => $v['total_amount'],
                    "fee_id" => $v['fee_id'],
                    "entrust1_amount" => $v['entrust1_amount'],
                    "entrust2_amount" => $v['entrust2_amount'],
                    "type" => $v['type'],
                    "start_time" => date('Y-m-d', $item_value['start_time']),
                    "end_time" => date('Y-m-d', $item_value['end_time']),
                ];
                $return[] = $detail;
            }


        }

        return [true, [
            'fee_type_list' => $fee_type_list,
            'list' => $return,
        ]];


    }


    /**
     * 将报销明细按照一级费用和项目分类进行分组
     * * @param array $list 报销明细列表 (含 fee_id, amount 等)
     * @param array $fee_type_list 费用类型树 (含 id, parent_id, title)
     * @return array
     */
    public function auxiliaryGroupListByRoot(array $list, array $fee_type_list)
    {
        // 1. 建立 ID 索引，方便快速查找
        $idMap = array_column($fee_type_list, null, 'id');

        // 2. 预处理映射表：让所有子 ID 直接指向它的最顶层“祖宗” ID
        $childToRoot = [];
        foreach ($fee_type_list as $type) {
            $current = $type;
            // 向上追溯直到 parent_id 为 0，即找到一级分类
            while ($current['parent_id'] != 0) {
                $current = $idMap[$current['parent_id']];
            }
            $childToRoot[$type['id']] = [
                'id' => $current['id'],
                'title' => $current['title'],
                'sort' => $current['sort']
            ];
        }

        // 3. 遍历明细数据进行分组
        $type_list = [];
        foreach ($list as $k => $item) {
            if ($item['item_id'] == 0 || $item['fee_id'] == 0) {
                continue;
            }
            $feeId = $item['fee_id'];
            // 获取该费用对应的一级分类信息
            if (!isset($childToRoot[$feeId])) continue;
            $rootId = $childToRoot[$feeId]['id'];
            $title = $childToRoot[$feeId]['title'];
            $sort = $childToRoot[$feeId]['sort'];
            $item['fee_id'] = $rootId;
            $list[$k] = $item;

            //这边需要拿到头部所有的一级费用类型
            if (!isset($type_list[$rootId])) {
                $type_list[$rootId] = [
                    'sort' => $sort,
                    'id' => $rootId,
                    'title' => $title,
                ];
            }
        }
        // 使用 values() 丢弃原始键名，重新从 0 开始建立索引
        $type_list = collect($type_list)->sortBy('sort')->values()->all();
        // 4. 重置数组索引并返回
        return [$list, $type_list];
    }


    public function auxiliaryEmployee($user, $data, $month_start, $month_end)
    {
        //确认所有项目、人员、人员工时
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有人员工资
        $salary_map = $this->getEmployeeSalary($user, $data, $month_start, $month_end);
        // 2. 计算每个员工在每个月的全月总工时
        $key = ["employee_id","order_month"];
        $employee_monthly_total_min = $this->calculateSum($month_employee_list,$key,"total_work");
        // 3. 计算分摊天数与工资
        list($item_list,) = $this->calculateRatioForMonth($month_employee_list,$employee_monthly_total_min,$salary_map,$key,["employee_id","month","item_id"]);
        $collect = collect($item_list);
        $employee_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['employee_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();
        $sums = [
            "salary_map" => $salary_map
        ];
        $word_keys = [
            "salary_map" => [
                "key" => "allocated_salary",
                "value" => "allocated_salary",
                "type" => "money",
            ]
        ];
        $employee_count = [
            'salary_map' => $employee_count
        ];
        $month_employee_list = $collect->transform(function ($item) use (&$employee_count,&$sums,$word_keys) {
            // 如果不是最后一条
            $key = $item['employee_id'] . '_' . $item['month'];

            $this->calculateClosure($key,$item,$employee_count,$sums,$word_keys);
            return $item;
        })->all();
        $return_item_list = [];
        foreach ($month_employee_list as $v){
            $key = $v['item_id'] . '_' . $v['month'];
            if(!isset($return_item_list[$key])) $return_item_list[$key]['allocated_salary'] = 0;
            $return_item_list[$key]['allocated_salary'] += $v['allocated_salary'];
        }

        return $return_item_list;
    }

    public function auxiliaryDevice($user, $data, $month_start, $month_end)
    {
        $month_device_list = $this->getItemDeviceMonthWorkList($user, $data, $month_start, $month_end);
        //查询所有设备工资
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $depreciation_map = $this->getDeviceAmount($user, $data, $month_start, $month_end);
        // 2. 计算每个员工在每个月的全月总工时
        $key = ["device_id","order_month"];
        $device_monthly_total_min = $this->calculateSum($month_device_list,$key,"total_work");
        // 3. 计算分摊天数与工资
        list($item_list,) = $this->calculateDeviceRatioForMonth($month_device_list,$device_monthly_total_min,$depreciation_map,$key,["device_id","month","item_id"]);
        $collect = collect($item_list);
        $device_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['device_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();
        $sums = [
            "salary_map" => $depreciation_map
        ];
        $word_keys = [
            "salary_map" => [
                "key" => "allocated_depreciation",
                "value" => "depreciation",
                "type" => "money",
            ]
        ];
        $device_count = [
            'salary_map' => $device_count
        ];
        $month_employee_list = $collect->transform(function ($item) use (&$device_count,&$sums,$word_keys) {

            $key = $item['device_id'] . '_' . $item['month'];

            $this->calculateClosure($key,$item,$device_count,$sums,$word_keys);
            return $item;
        })->all();
        $return_item_list = [];
        foreach ($month_employee_list as $v){
            $key = $v['item_id']. '_' . $v['month'];
            if(!isset($return_item_list[$key])) $return_item_list[$key]['depreciation'] = 0;
            $return_item_list[$key]['depreciation'] += $v['depreciation'];
        }
//        dd($return_item_list);
        return $return_item_list;
    }

    public function itemEmployeeSalaryStatisticold($data, $user)
    {
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 获取人员工资项目相关分组数据
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);

        // 查询所有人员工资
        $monthly_ps_order_ids = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('id')->toArray();

        $monthly_ps_order_key_list = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))
            ->pluck('month_str', 'id')
            ->toArray();

        $month_employee_salary = MonthlyPsOrderDetails::wherein('main_id', $monthly_ps_order_ids)
            ->select("employee_id", DB::raw("(base_salary + performance_salary + bonus + other) as salary"), "main_id", "social_insurance", "public_housing_fund")
            ->get()->toArray();

        // 1. 汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $key = $val['employee_id'] . '_' . $month;
                $salary_map[$key] = $val;
                $all_salary[$key] = [
                    "salary" => (float)$val['salary'] * 100,
                    "social_insurance" => (float)$val['social_insurance'] * 100,
                    "public_housing_fund" => (float)$val['public_housing_fund'] * 100,
                ];
            }
        }

        // 2. 计算每个员工在每个月的全月总工时
        $employee_monthly_total_min = [];
        $all_min = [];
        foreach ($month_employee_list as $row) {
            $key = $row['employee_id'] . '_' . $row['order_month'];
            if (!isset($employee_monthly_total_min[$key])) {
                $employee_monthly_total_min[$key] = 0;
                $all_min[$key] = 0;
            }
            // 如果此员工该月无工资记录，初始化默认全 0 结构，防止报错
            if (!isset($all_salary[$key])) {
                $all_salary[$key] = [
                    "salary" => 0,
                    "social_insurance" => 0,
                    "public_housing_fund" => 0,
                ];
            }

            $employee_monthly_total_min[$key] += $row['total_work'];
            $all_min[$key] += round($row['total_work'] / 60, 2) * 100;
        }

        // 3. 计算分摊天数与工资
        $item_month_list = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
            $item_key = $item['order_month'] . '_' . $item['item_id'] . '_' . $item['employee_id'];

            if (!isset($item_month_list[$item_key])) {
                $item_month_list[$item_key] = [
                    "month" => $item['order_month'],
                    "allocated_salary" => 0,
                    "employee_id" => $item['employee_id'],
                    "work_minutes" => 0,
                    "salary" => ($salary_map[$key]['salary'] ?? 0),
                    "social_insurance" => ($salary_map[$key]['social_insurance'] ?? 0),
                    "public_housing_fund" => ($salary_map[$key]['public_housing_fund'] ?? 0),
                    "item_id" => $item['item_id'],
                ];
            }

            $total_min = $employee_monthly_total_min[$key] ?? 0;
            // 计算工资分摊比例
            if ($total_min > 0) {
//                $ratio = round((round($item['total_work'] / 60, 2) / round($total_min / 60, 2)), 4);
                $ratio = round($item['total_work'] / $total_min, 4);
            } else {
                $ratio = 0;
            }

            $item_month_list[$item_key]['radio'] = $ratio;
            $item_month_list[$item_key]['total_min'] = $total_min;
            $item_month_list[$item_key]['work_minutes'] += $item['total_work'];
        }

        $item_month_list = collect($item_month_list)->sortBy('month')->values()->all();
        $items = collect($item_month_list)->pluck('item_id')->unique()->values()->all();
        $employee_ids = collect($item_month_list)->pluck('employee_id')->unique()->values()->all();

        $employee_list = Employee::TopClear($user, $data)
            ->wherein('id', $employee_ids)
            ->select("major", "title", "id")
            ->get()
            ->keyBy('id')
            ->toArray();

        $item_query = Item::TopClear($user, $data)->wherein('id', $items);
        $item_title_key_list = (clone $item_query)->pluck("title", "id")->toArray();
        $item_code_key_list = (clone $item_query)->pluck("code", "id")->toArray();

        $collect = collect($item_month_list);
        $employee_count = $collect->groupBy(function ($item) {
            return $item['employee_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();

        // 组装最终结果
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, $employee_list, &$employee_count, &$all_salary, &$all_min) {
            $item['item_title'] = $item_title_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['item_code'] = $item_code_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['employee_title'] = $employee_list[$item['employee_id']]['title'] ?? "未知人员({$item['employee_id']})";
            $item['major'] = $employee_list[$item['employee_id']]['major'] ?? "未知人员({$item['employee_id']})";
            $item['total_min'] = round($item['total_min'] / 60, 2);

            $key = $item['employee_id'] . '_' . $item['month'];

            // 容错处理：确保 $all_salary[$key] 和 $all_min[$key] 一定存在
            if (!isset($all_salary[$key])) {
                $all_salary[$key] = ['salary' => 0, 'social_insurance' => 0, 'public_housing_fund' => 0];
            }
            if (!isset($all_min[$key])) {
                $all_min[$key] = 0;
            }

            if (--$employee_count[$key] > 0) {
                $work_minutes = round($item['work_minutes'] / 60, 2);
                $item['work_minutes'] = $work_minutes;
                $all_min[$key] -= ($work_minutes * 100);

                $work_salary = round($item['salary'] * $item['radio'], 2);
                $all_salary[$key]['salary'] -= ($work_salary * 100);
                $item['work_salary'] = $work_salary;

                $social_insurance = round($item['social_insurance'] * $item['radio'], 2);
                $all_salary[$key]['social_insurance'] -= ($social_insurance * 100);
                $item['work_social_insurance'] = $social_insurance;

                $work_public_housing_fund = round($item['public_housing_fund'] * $item['radio'], 2);
                $all_salary[$key]['public_housing_fund'] -= ($work_public_housing_fund * 100);
                $item['work_public_housing_fund'] = $work_public_housing_fund;
            } else {
                // 最后一笔记录通过反扣差额尾差，保证总和 100% 精确对齐
                $item['work_salary'] = round($all_salary[$key]['salary'] / 100, 2);
                $item['work_social_insurance'] = round($all_salary[$key]['social_insurance'] / 100, 2);
                $item['work_public_housing_fund'] = round($all_salary[$key]['public_housing_fund'] / 100, 2);
                $item['work_minutes'] = round($all_min[$key] / 100, 2);
            }

            return $item;
        })->all();

        return [true, $item_month_list];
    }

    public function itemEmployeeSalaryStatistic($data, $user)
    {
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 获取人员工资项目相关分组数据
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);

        // 1. 查询所有人员工资单
        $monthly_ps_order_ids = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('id')->toArray();

        $monthly_ps_order_key_list = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))
            ->pluck('month_str', 'id')
            ->toArray();

        $month_employee_salary = MonthlyPsOrderDetails::wherein('main_id', $monthly_ps_order_ids)
            ->select("employee_id", DB::raw("(base_salary + performance_salary + bonus + other) as salary"), "main_id", "social_insurance", "public_housing_fund")
            ->get()->toArray();

        // 2. 查询所有人员月度工时单 (新增逻辑)
        $monthly_pw_order_key_list = MonthlyPwOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))
            ->pluck('month_str', 'id')
            ->toArray();

        $monthly_pw_order_ids = array_keys($monthly_pw_order_key_list);

        $month_employee_pw = MonthlyPwOrderDetails::whereIn('main_id', $monthly_pw_order_ids)
            ->where('del_time', 0)
            ->select('employee_id', 'main_id', 'total_hours')
            ->get()->toArray();

        $pw_hours_map = [];
        foreach ($month_employee_pw as $pw_val) {
            $pw_month = $monthly_pw_order_key_list[$pw_val['main_id']] ?? '';
            if ($pw_month) {
                $pw_key = $pw_val['employee_id'] . '_' . $pw_month;
                $pw_hours_map[$pw_key] = (float)$pw_val['total_hours'];
            }
        }

        // 3. 汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $key = $val['employee_id'] . '_' . $month;
                $salary_map[$key] = $val;
                $all_salary[$key] = [
                    "salary" => (float)$val['salary'] * 100,
                    "social_insurance" => (float)$val['social_insurance'] * 100,
                    "public_housing_fund" => (float)$val['public_housing_fund'] * 100,
                ];
            }
        }

        // 4. 获取每个员工在每个月的全月总工时（改自月度工时单）
        $employee_monthly_total_min = [];
        $all_min = [];
        foreach ($month_employee_list as $row) {
            $key = $row['employee_id'] . '_' . $row['order_month'];

            if (!isset($employee_monthly_total_min[$key])) {
                // 直接从工时单映射中获取小时数，乘以 60 转为分钟供后续比例计算
                $total_hours = $pw_hours_map[$key] ?? 0;
                $employee_monthly_total_min[$key] = $total_hours * 60;
                $all_min[$key] = round($total_hours * 100, 2); // 放大100倍用于尾差倒扣
            }

            // 如果此员工该月无工资记录，初始化默认全 0 结构，防止报错
            if (!isset($all_salary[$key])) {
                $all_salary[$key] = [
                    "salary" => 0,
                    "social_insurance" => 0,
                    "public_housing_fund" => 0,
                ];
            }
        }

        // 5. 计算分摊天数与工资
        $item_month_list = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
            $item_key = $item['order_month'] . '_' . $item['item_id'] . '_' . $item['employee_id'];

            if (!isset($item_month_list[$item_key])) {
                $item_month_list[$item_key] = [
                    "month" => $item['order_month'],
                    "allocated_salary" => 0,
                    "employee_id" => $item['employee_id'],
                    "work_minutes" => 0,
                    "salary" => ($salary_map[$key]['salary'] ?? 0),
                    "social_insurance" => ($salary_map[$key]['social_insurance'] ?? 0),
                    "public_housing_fund" => ($salary_map[$key]['public_housing_fund'] ?? 0),
                    "item_id" => $item['item_id'],
                ];
            }

            $total_min = $employee_monthly_total_min[$key] ?? 0;
            // 计算工资分摊比例（项目实际工作分钟数 / 工时单月度总分钟数）
            if ($total_min > 0) {
                $ratio = round($item['total_work'] / $total_min, 4);
            } else {
                $ratio = 0;
            }

            $item_month_list[$item_key]['radio'] = $ratio;
            $item_month_list[$item_key]['total_min'] = $total_min;
            $item_month_list[$item_key]['work_minutes'] += $item['total_work'];
        }

        $item_month_list = collect($item_month_list)->sortBy('month')->values()->all();
        $items = collect($item_month_list)->pluck('item_id')->unique()->values()->all();
        $employee_ids = collect($item_month_list)->pluck('employee_id')->unique()->values()->all();

        $employee_list = Employee::TopClear($user, $data)
            ->wherein('id', $employee_ids)
            ->select("major", "title", "id")
            ->get()
            ->keyBy('id')
            ->toArray();

        $item_query = Item::TopClear($user, $data)->wherein('id', $items);
        $item_title_key_list = (clone $item_query)->pluck("title", "id")->toArray();
        $item_code_key_list = (clone $item_query)->pluck("code", "id")->toArray();

        $collect = collect($item_month_list);
        $employee_count = $collect->groupBy(function ($item) {
            return $item['employee_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();

        // 6. 组装最终结果
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, $employee_list, &$employee_count, &$all_salary, &$all_min) {
            $item['item_title'] = $item_title_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['item_code'] = $item_code_key_list[$item['item_id']] ?? "未知项目({$item['item_id']})";
            $item['employee_title'] = $employee_list[$item['employee_id']]['title'] ?? "未知人员({$item['employee_id']})";
            $item['major'] = $employee_list[$item['employee_id']]['major'] ?? "未知人员({$item['employee_id']})";
            $item['total_min'] = round($item['total_min'] / 60, 2);

            $key = $item['employee_id'] . '_' . $item['month'];

            // 容错处理：确保 $all_salary[$key] 和 $all_min[$key] 一定存在
            if (!isset($all_salary[$key])) {
                $all_salary[$key] = ['salary' => 0, 'social_insurance' => 0, 'public_housing_fund' => 0];
            }
            if (!isset($all_min[$key])) {
                $all_min[$key] = 0;
            }

            if (--$employee_count[$key] > 0) {
                $work_minutes = round($item['work_minutes'] / 60, 2);
                $item['work_minutes'] = $work_minutes;
                $all_min[$key] -= ($work_minutes * 100);

                $work_salary = round($item['salary'] * $item['radio'], 2);
                $all_salary[$key]['salary'] -= ($work_salary * 100);
                $item['work_salary'] = $work_salary;

                $social_insurance = round($item['social_insurance'] * $item['radio'], 2);
                $all_salary[$key]['social_insurance'] -= ($social_insurance * 100);
                $item['work_social_insurance'] = $social_insurance;

                $work_public_housing_fund = round($item['public_housing_fund'] * $item['radio'], 2);
                $all_salary[$key]['public_housing_fund'] -= ($work_public_housing_fund * 100);
                $item['work_public_housing_fund'] = $work_public_housing_fund;
            } else {
                // 最后一笔记录通过反扣差额尾差，保证总和 100% 精确对齐
                $item['work_salary'] = round($all_salary[$key]['salary'] / 100, 2);
                $item['work_social_insurance'] = round($all_salary[$key]['social_insurance'] / 100, 2);
                $item['work_public_housing_fund'] = round($all_salary[$key]['public_housing_fund'] / 100, 2);
                $item['work_minutes'] = round($all_min[$key] / 100, 2);
            }

            return $item;
        })->all();

        return [true, $item_month_list];
    }

    public function enterpriseRdStatistic($data, $user){
        $model = Item::TopClear($user,$data);
        $model = $model->where('del_time',0)
            ->select(Item::$report_field_1)
            ->orderby('id', 'desc');

        if(! empty($data['title'])) $model->where('title', 'LIKE', '%'.$data['title'].'%');
        if(! empty($data['code'])) $model->where('code', 'LIKE', '%'.$data['code'].'%');
        if(! empty($data['state'])) $model->where('state', $data['state']);
        if(! empty($data['year'])) {
            $return = $this->getYearRangeInfo($data['year']);
            if (is_null($return)) return [false, '年度格式错误'];
            list($month_start, $month_end) = $return;
            $model->where("start_time", "<", strtotime($month_end)) // 项目开始得比月份结束早
            ->where("end_time", ">", strtotime($month_start)); // 项目结束得比月份开始晚
        }

        $list = $model->get()->toArray();
        if(empty($list)) return [true,[]];
        $datetime = $list[0]['start_time'];
        $list = $this->fillEnterpriseRdStatistic($list, $data, $user,$datetime);

        return [true, $list];
    }

    private function fillEnterpriseRdStatistic($list, $data, $user,$time){
        //项目实际支出 项目费用单
//        $expense = ExpenseClaimsDetails::Clear($user, $data);
//        $expense_map = $expense->where('del_time', 0)
//            ->whereIn('item_id', array_unique(array_column($list,'id')))
//            ->selectRaw('item_id, SUM(amount) as total_amount')
//            ->groupBy('item_id')
//            ->pluck('total_amount', 'item_id') // 这一步直接生成 item_id => total_amount 结构
//            ->toArray();
        $attendance = $this->employeeAttendanceMonthStatistic(["year"=>date("Y-m-d",$time), "s" => "/api/employeeAttendanceMonthStatistic"],$user);
        $attendance_key_list = [];
        foreach ($attendance[1]['list'] as $v){
            $attendance_key_list[$v['code']] = $v['employee_salary']*100+$v['device_depreciation']*100;
            foreach ($v['fee_list'] as $vv){
                $attendance_key_list[$v['code']] += $vv['total_amount']*100;
                $attendance_key_list[$v['code']] += $vv['entrust1_amount']*100;
                $attendance_key_list[$v['code']] += $vv['entrust2_amount']*100;
            }
        }
//        dd($attendance_key_list);
        foreach ($list as $key => $value){
            $list[$key]['actual_expenditure'] = isset($attendance_key_list[$value['code']]) ? round($attendance_key_list[$value['code']]/100,2) : 0;
            $start_time = $value['start_time'] ? date('Y-m-d', $value['start_time']) : '';
            $end_time = $value['end_time'] ? date('Y-m-d', $value['end_time']) : '';
            $list[$key]['time_range'] = $start_time . ' ' . $end_time;
        }

        return $list;
    }

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

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

        $list = $model->get()->toArray();
        $list = $this->fillEnterpriseRdManStatistic($list, $data, $user);

        return [true, $list];
    }

    private function fillEnterpriseRdManStatistic($list, $data, $user){
        $man = EmployeeDepartPermission::from('employee_depart_permission as a')
            ->join('depart as b', 'b.id', '=', 'a.depart_id')
            ->whereIn('a.employee_id', array_unique(array_column($list,'id')))
            ->select('a.employee_id', 'b.title')
            ->get()->toArray();
        $man_map = [];
        foreach ($man as $value){
            if(isset($man_map[$value['employee_id']])){
                $man_map[$value['employee_id']] .= ',' . $value['title'];
            }else{
                $man_map[$value['employee_id']] = $value['title'];
            }
        }

        foreach ($list as $key => $value){
            $depart_title = $man_map[$value['id']] ?? "";
            $list[$key]['position_new'] = $depart_title . '/' . $value['position'];
            $list[$key]['employee_type_title'] = Employee::E_State_Type[$value['employee_type']] ?? '';
            $list[$key]['education'] = Employee::Education[$value['education']] ?? '';
        }

        return $list;
    }

    // 取别名
    public function enterpriseRdItemStatistic($data, $user){
        // 1. 先声明表名和别名
        $model = ItemDetails::from('item_details as i');
        // 2. 再调用 TopClear
        $model = $model->TopClear($user, $data);
        $model = $model->where('i.del_time', 0)
            ->where('i.type', ItemDetails::type_one);
        $model = $model->leftJoin('employee as e', 'i.data_id', '=', 'e.id');
        $model = $model->leftJoin('item as it', 'it.id', '=', 'i.item_id');

        if(! empty($data['title'])) $model->where('e.title', 'LIKE', '%'.$data['title'].'%');
        if(! empty($data['item_title'])) $model->where('it.title', 'LIKE', '%'.$data['item_title'].'%');

        $fields = ['e.id', 'e.title', 'e.education', 'e.major','e.p_title','i.item_id','it.title as item_title'];

        $list = $model->select($fields)
            ->orderBy('i.id', 'desc')
            ->get()
            ->toArray();

        $list = $this->fillEnterpriseRdItemStatistic($list, $data, $user);

        return [true, $list];
    }

    private function fillEnterpriseRdItemStatistic($list, $data, $user){
        $man = EmployeeDepartPermission::from('employee_depart_permission as a')
            ->join('depart as b', 'b.id', '=', 'a.depart_id')
            ->whereIn('a.employee_id', array_unique(array_column($list,'id')))
            ->select('a.employee_id', 'b.title')
            ->get()->toArray();
        $man_map = [];
        foreach ($man as $value){
            if(isset($man_map[$value['employee_id']])){
                $man_map[$value['employee_id']] .= ',' . $value['title'];
            }else{
                $man_map[$value['employee_id']] = $value['title'];
            }
        }

        foreach ($list as $key => $value){
            $depart_title = $man_map[$value['id']] ?? "";
            $list[$key]['depart_title'] = $depart_title;
            $list[$key]['education'] = Employee::Education[$value['education']] ?? '';
        }

        return $list;
    }

    public function employeeMonthSalaryStatisticCountCommon($data,$user){
        $model = MonthlyPsOrder::Clear($user,$data);
        $model->leftJoin('monthly_ps_order_details as b', 'b.main_id', 'monthly_ps_order.id')
            ->where('monthly_ps_order.del_time',0)
            ->where('b.del_time',0)
            ->select('monthly_ps_order.month', 'b.employee_id', 'b.base_salary','b.performance_salary','b.bonus','b.other')
            ->orderby('monthly_ps_order.month','desc');

        if(! empty($data['time'][0]) && ! empty($data['time'][1])) {
            $return = $this->changeDateToTimeStampAboutRange($data['time']);
            $model->where('monthly_ps_order.month','>=',$return[0]);
            $model->where('monthly_ps_order.month','<=',$return[1]);
        }

        if(! empty($data['employee_title'])){
            $model_2 = Employee::TopClear($user,$data);
            $id = $model_2->where('del_time',0)
                ->where('title', 'LIKE', '%'.$data['employee_title'].'%')
                ->pluck('id')
                ->toArray();
            $model->whereIn('b.employee_id', $id);
        }

        return $model;
    }

    public function employeeMonthSalaryStatisticCount($data, $user){
        $model = $this->employeeMonthSalaryStatisticCountCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->fillEmployeeMonthSalaryStatisticCount($list, $data, $user);

        return [true, $list];
    }

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

        $emp = (new EmployeeService())->getEmployeeMap(array_unique(array_column($data['data'],'employee_id')));
        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['month'] = $value['month'] ? date('Y-m',$value['month']) : '';
            $data['data'][$key]['employee_title'] = $emp[$value['employee_id']] ?? '';
            $totalSalary = (float)($value['base_salary'] ?? 0)
                + (float)($value['performance_salary'] ?? 0)
                + (float)($value['bonus'] ?? 0)
                + (float)($value['other'] ?? 0);
            $data['data'][$key]['total_salary'] = number_format($totalSalary, 2, '.', '');
        }

        return $data;
    }

    /**
     * 获取人员维度的研发工资及工时汇总报表
     * * @param array $data 包含时间范围或年份的请求数据
     * @param object $user 当前用户上下文
     * @return array
     */
    public function employeeSalarySummary($data, $user)
    {
        // 1. 复用时间解析和校验规则
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 2. 复用原始数据拉取：项目人员工时、人员全额原始工资
        $month_employee_list = $this->getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end);
        $salary_map = $this->getEmployeeSalary($user, $data, $month_start, $month_end);

        // 3. 计算每个员工的月总工时（作为分母）
        $keys = ["employee_id", "order_month"];
        $employee_monthly_total_min = $this->calculateSum($month_employee_list, $keys, "total_work");

        // 4. 调用核心分摊算法（拿到每一项明细的 allocated_salary）
        list($item_month_list, ) = $this->calculateRatioForMonth(
            $month_employee_list,
            $employee_monthly_total_min,
            $salary_map,
            ["employee_id", "order_month"],
            ["order_month", "item_id", "employee_id"]
        );

        // 5. 将分摊数据转化为集合，纯粹根据【员工ID + 月份】进行分组汇总
        $collect = collect($item_month_list);

        $employee_ids = $collect->pluck('employee_id')->unique()->values()->all();

        // 6. 仅拉取人员档案名称，不再拉取项目档案（省去不必要的查询，提升性能）
        $employeeModel = Employee::TopClear($user, $data);
        $employee_title_map = $employeeModel->wherein('id', $employee_ids)->pluck("title", "id")->toArray();

        // 7. 开始聚合
        $result = $collect->groupBy(function ($item) {
            // 根据 员工ID_月份 分组，支持跨月查询时的正确归类
            return $item['employee_id'] . '_' . $item['month'];
        })->map(function ($group) use ($employee_title_map) {
            $first = $group->first();

            // 汇总这个人当月在所有项目里的研发工资（分），然后除以 100 变成元
            $total_allocated_salary = round($group->sum('allocated_salary') / 100, 2);

            // 汇总这个人当月的研发总工时（分钟转小时）
            $total_work_hours = round($group->sum('work_minutes') / 60, 2);

            return [
                'month'          => $first['month'],
                'employee_id'    => $first['employee_id'],
                'employee_title' => $employee_title_map[$first['employee_id']] ?? "未知人员({$first['employee_id']})",
//                '研发总工时(小时)' => $total_work_hours,
                'money'    => $total_allocated_salary // 这里就是您要的纯人员维度的研发工资总额
            ];
        })->values()->all();

        return [true, $result];
    }
}