workHour/app/Service/StatisticService.php

<?php

namespace App\Service;

use App\Model\AuxiliaryAccountDetails;
use App\Model\DailyDwOrderDetails;
use App\Model\DailyPwOrderDetails;
use App\Model\Device;
use App\Model\Employee;
use App\Model\EmployeeDepartPermission;
use App\Model\EmployeeRole;
use App\Model\ExpenseClaimsDetails;
use App\Model\Fee;
use App\Model\Item;
use App\Model\ItemDetails;
use App\Model\MonthlyDdOrder;
use App\Model\MonthlyDdOrderDetails;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPsOrderDetails;
use App\Service\Statistic\StatisticCommonService;
use Illuminate\Support\Facades\DB;

class StatisticService extends StatisticCommonService
{
    public function employeeDayHourStatistic($data, $user)
    {
        //传参月份、项目编码、项目名称 不允许跨年查询月份
        //项目编码、项目名称、人员名称、工时、日期
        //传参月份、项目编码、项目名称 不允许跨年查询月份
        //项目编码、项目名称、人员名称、工时、日期
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        $month_employee_list = $month_employee->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m-%d') as order_date"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy(DB::raw("FROM_UNIXTIME(order_time, '%Y-%m-%d')"), "item_id", "employee_id")
            ->orderby("order_date", "asc")->get()->toArray();
//        $month_employee_list = $this->limit($month_employee_list, ['*'], $data);
        $dataCollection = collect($month_employee_list);
        $item_ids = $dataCollection->pluck('item_id')->unique()->values()->all();
        $employee_ids = $dataCollection->pluck('employee_id')->unique()->values()->all();

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

        $item = Item::TopClear($user, $data);
        $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();
        $collect = collect($month_employee_list);

        $employee_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['employee_id'] . '_' . $item['order_date'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();
        $employee_work_count = $collect->groupBy(fn($item) => $item['employee_id'] . '_' . $item['order_date'])
            ->map(function ($group) {
                // 1. 算出小时并保留两位小数（例如 26.08）
                $hours = round($group->sum('total_work') / 60, 2);

                // 2. 乘以 100 并强转为整数（例如 2608）
                // 使用 round 是为了再次修正 26.08 * 100 可能产生的 2607.9999 误差
                return (int)round($hours * 100);
            })
            ->toArray();
        $collection = collect($month_employee_list);
        // 注意：这里使用 transform，参数名建议写准确
        // 关键点：use 后面加了 & 符号
        $month_employee_list = $collection->transform(function ($item, $index) use ($employee_key_list, $item_title_key_list, $item_code_key_list, &$employee_work_count, &$employee_count) {

            $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 = $item['employee_id'] . '_' . $item['order_date'];
            if (--$employee_count[$key] > 0) {
                $current_hours = round($item['total_work'] / 60, 2);
                $item['total_work_hours'] = $current_hours;
                // 关键：递减外部的 $absolute_total_hours
                $employee_work_count[$key] -= $current_hours * 100;

            } else {
                $item['total_work_hours'] = round($employee_work_count[$key] / 100, 2);
            }
            return $item;
        })->all();
        return [true, $month_employee_list];
    }

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

        // 1. 获取工时 (复用逻辑)
        $month_employee_list = DailyPwOrderDetails::Clear($user, $data)
            ->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)->where('del_time', 0)
            ->select("item_id", "employee_id", DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"), DB::raw("SUM(total_work_min) as total_work"))
            ->groupBy("order_month", "item_id", "employee_id")->get();

        // 2. 获取工资 (复用逻辑)
        $ps_query = MonthlyPsOrder::Clear($user, $data)->where('del_time', 0)->where("month", ">=", $month_start)->where("month", "<", $month_end);
        $monthly_ps_order_ids = $ps_query->pluck('id')->toArray();
        $month_map = $ps_query->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))->pluck('month_str', 'id')->toArray();

        $salary_details = MonthlyPsOrderDetails::whereIn('main_id', $monthly_ps_order_ids)
            ->select("employee_id", "main_id", DB::raw("(base_salary + performance_salary + bonus + other) as s_orig"))->get();

        $salary_map = [];
        foreach ($salary_details as $val) {
            $m = $month_map[$val['main_id']] ?? '';
            if ($m) {
                $k = $val['employee_id'] . '_' . $m;
                $salary_map[$k] = ($salary_map[$k] ?? 0) + (int)round($val['s_orig'] * 100);
            }
        }

        // 3. 统计月总工时与项目数
        $emp_total_min = [];
        $emp_item_count = [];
        foreach ($month_employee_list as $row) {
            $k = $row->employee_id . '_' . $row->order_month;
            $emp_total_min[$k] = ($emp_total_min[$k] ?? 0) + $row->total_work;
            $emp_item_count[$k] = ($emp_item_count[$k] ?? 0) + 1;
        }

        // 4. 分摊并生成明细
        $rem_salary_pool = $salary_map;
        $detail_list = [];
        foreach ($month_employee_list as $item) {
            $k = $item->employee_id . '_' . $item->order_month;
            $total_s = $salary_map[$k] ?? 0;
            $total_m = $emp_total_min[$k] ?? 0;

            if ($total_m > 0 && --$emp_item_count[$k] > 0) {
                $allocated = (int)floor(($item->total_work / $total_m) * $total_s);
                $rem_salary_pool[$k] -= $allocated;
            } else {
                $allocated = $rem_salary_pool[$k] ?? 0;
            }

            $detail_list[] = [
                'month' => $item->order_month,
                'item_id' => $item->item_id,
                'employee_id' => $item->employee_id,
                'allocated_salary' => round($allocated / 100, 2),
                'work_hours' => round($item->total_work / 60, 2),
                'total_hours' => round($total_m / 60, 2),
                'total_salary' => round($total_s / 100, 2)
            ];
        }

        // 5. 补充名称信息
        $i_ids = collect($detail_list)->pluck('item_id')->unique()->all();
        $e_ids = collect($detail_list)->pluck('employee_id')->unique()->all();
        $i_info = Item::TopClear($user, $data)->whereIn('id', $i_ids)->get()->keyBy('id');
        $e_info = Employee::TopClear($user, $data)->whereIn('id', $e_ids)->get()->keyBy('id');

        $result = collect($detail_list)->sortBy('month')->transform(function ($v) use ($i_info, $e_info) {
            $v['item_title'] = $i_info[$v['item_id']]->title ?? "未知({$v['item_id']})";
            $v['item_code'] = $i_info[$v['item_id']]->code ?? "-";
            $v['employee_title'] = $e_info[$v['employee_id']]->title ?? "未知({$v['employee_id']})";
            return $v;
        })->values()->all();

        return [true, $result];
    }

    public function employeeMonthSalaryStatistic($data, $user)
    {
        // 1. 基础规则验证
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 2. 获取所有工时明细（按月、项目、人聚合）
        $month_employee_list = DailyPwOrderDetails::Clear($user, $data)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy("order_month", "item_id", "employee_id")
            ->get();

        if ($month_employee_list->isEmpty()) {
            return [true, []];
        }

        // 3. 获取工资数据
        $ps_query = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end);

        $monthly_ps_order_ids = $ps_query->pluck('id')->toArray();
        $month_map = $ps_query->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))
            ->pluck('month_str', 'id')->toArray();

        $salary_details = MonthlyPsOrderDetails::whereIn('main_id', $monthly_ps_order_ids)
            ->select("employee_id", "main_id", DB::raw("(base_salary + performance_salary + bonus + other) as s_orig"))
            ->get();

        // 4. 建立工资映射（统一使用分单位，避免浮点误差）
        $salary_map = [];
        foreach ($salary_details as $val) {
            $m = $month_map[$val['main_id']] ?? '';
            if ($m) {
                $k = $val['employee_id'] . '_' . $m;
                // 采用与 itemDaySalaryStatistic 一致的整数处理
                $salary_map[$k] = ($salary_map[$k] ?? 0) + (int)round($val['s_orig'] * 100);
            }
        }

        // 5. 统计每人每月总工时和参与的项目数（用于精准分摊尾差）
        $emp_total_min = [];
        $emp_item_count = [];
        foreach ($month_employee_list as $row) {
            $k = $row->employee_id . '_' . $row->order_month;
            $emp_total_min[$k] = ($emp_total_min[$k] ?? 0) + $row->total_work;
            $emp_item_count[$k] = ($emp_item_count[$k] ?? 0) + 1;
        }

        // 6. 执行分摊逻辑并按项目汇总
        $item_summary = []; // 存放项目维度的汇总结果
        $rem_salary_pool = $salary_map; // 余额池，按人+月控制

        foreach ($month_employee_list as $item) {
            $k = $item->employee_id . '_' . $item->order_month;
            $total_s = $salary_map[$k] ?? 0;
            $total_m = $emp_total_min[$k] ?? 0;

            // 计算当前员工在该项目上的分摊金额（单位：分）
            if ($total_m > 0 && --$emp_item_count[$k] > 0) {
                // 非该员工的最后一个项目：计算分摊
                $allocated = (int)floor(($item->total_work / $total_m) * $total_s);
                $rem_salary_pool[$k] -= $allocated;
            } else {
                // 该员工的最后一个项目：取余额清零
                $allocated = $rem_salary_pool[$k] ?? 0;
            }

            // 按项目+月份进行累加
            $item_key = $item->order_month . '_' . $item->item_id;
            if (!isset($item_summary[$item_key])) {
                $item_summary[$item_key] = [
                    'month'            => $item->order_month,
                    'item_id'          => $item->item_id,
                    'allocated_salary' => 0,
                    'work_minutes'     => 0,
                ];
            }
            $item_summary[$item_key]['allocated_salary'] += $allocated;
            $item_summary[$item_key]['work_minutes']     += $item['total_work'];
        }

        // 7. 获取项目信息并进行最终格式化
        $i_ids = collect($item_summary)->pluck('item_id')->unique()->all();
        $i_info = Item::TopClear($user, $data)->whereIn('id', $i_ids)->get()->keyBy('id');

        $result = collect($item_summary)->sortBy('month')->transform(function ($v) use ($i_info) {
            $v['item_title'] = $i_info[$v['item_id']]->title ?? "未知项目({$v['item_id']})";
            $v['item_code']  = $i_info[$v['item_id']]->code ?? "-";

            // 将“分”转回“元”
            $v['allocated_salary'] = round($v['allocated_salary'] / 100, 2);
            // 计算天数（按 8 小时/天）
            $v['days'] = round($v['work_minutes'] / 480);

            unset($v['work_minutes']); // 移除中间计算字段
            return $v;
        })->values()->all();

        return [true, $result];
    }


    public function employeeMonthSalaryStatistic1($data, $user)
    {
        //项目编码、项目名称、天数、工资、日期
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //确认所有项目、人员、人员工时
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        $month_employee_list = $month_employee->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy("order_month", "item_id", "employee_id")->get()->toArray();
        //查询所有人员工资
        $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")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $salary_map[$val['employee_id'] . '_' . $month] = $val['salary'];
            }
            if (!isset($all_salary[$month])) $all_salary[$month] = 0;
            $all_salary[$month] += $val['salary'] * 100;
        }
//        var_dump($salary_map);die;
        // 2. 计算每个员工在每个月的全月总工时
        $employee_monthly_total_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;
            }
            $employee_monthly_total_min[$key] += $row['total_work'];
        }

        // 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'];
            if (!isset($item_month_list[$item_key])) {
                $item_month_list[$item_key] = [
                    "month" => $item['order_month'],
                    "allocated_salary" => 0,
                    "work_minutes" => 0,
                    "item_id" => $item['item_id'],
                ];
            }
            $total_salary = $salary_map[$key] ?? 0;
            $total_min = $employee_monthly_total_min[$key] ?? 0;


            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = $item['total_work'] / $total_min;
                $allocated_salary = round($ratio * $total_salary, 2);
            } else {
                $allocated_salary = 0;
            }
            $item_month_list[$item_key]['allocated_salary'] += $allocated_salary;
            $item_month_list[$item_key]['work_minutes'] += $item['total_work'];
        }

        foreach ($item_month_list as $k => $v) {
            $item_month_list[$k]['days'] = round($v['work_minutes'] / 8 / 60);
        }
        $collect = collect($item_month_list);
        $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))
            ->toArray();

        $item_month_list = collect($item_month_list)->transform(function ($item, $index) use ($item_title_key_list, $item_code_key_list, &$all_salary, &$item_day_count, $item_count) {
            $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['allocated_salary'] = round($item['allocated_salary'], 2);
            // 如果不是最后一条
            $key = $item['month'];
            if (--$item_count[$key] > 0) {
                // 关键：递减外部的 $absolute_total_hours
                $item_day_count[$key] -= $item['days'];
                $all_salary[$key] -= $item['allocated_salary'] * 100;

            } else {
                $item['days'] = $item_day_count[$key];
                $item['allocated_salary'] = round($all_salary[$key] / 100, 2);
            }


//            if ($index < $count - 1) {
//                $total_day -= $item['days'];
//                // 关键：递减外部的 $absolute_total_hours
//                $all_salary -= $item['allocated_salary'];
//            } else {
//                // 最后一条：直接拿剩下的“余额”
//                $item['days'] = $total_day;
//                $item['allocated_salary'] = $all_salary;
//            }
            return $item;
        })->all();
        return [true, $item_month_list];
    }

    public function itemDaySalaryStatistic1($data, $user)
    {
        //项目编码、项目名称、人员名称、研发工时、研发工资、当月总工时、总计工资、年月
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //确认所有项目、人员、人员工时
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        $month_employee_list = $month_employee->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy(DB::raw("FROM_UNIXTIME(order_time, '%Y-%m')"), "item_id", "employee_id")->get()->toArray();
        //查询所有人员工资
        $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")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $salary_map[$val['employee_id'] . '_' . $month] = $val['salary'];
            }
            $key = $val['employee_id'] . '_' . $month;
            if (!isset($all_salary[$key])) {
                $all_salary[$key] = 0;
            }
            $all_salary[$key] += $val['salary'] * 100;

        }
        // 2. 计算每个员工在每个月的全月总工时
        $employee_monthly_total_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;
            }
            $employee_monthly_total_min[$key] += $row['total_work'];
        }

        // 3. 计算分摊天数与工资
        $item_month_list = [];
        $total_work_min = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
            $employee_key = $item['order_month'] . '_' . $item['item_id'] . '_' . $item['employee_id'];
            $total_salary = $salary_map[$key] ?? 0;
            $total_min = $employee_monthly_total_min[$key] ?? 0;
            if (!isset($item_month_list[$employee_key])) {
                $item_month_list[$employee_key] = [
                    "month" => $item['order_month'],
                    "allocated_salary" => 0,
                    "work_minutes" => 0,
                    "total_min" => $total_min,
                    "total_salary" => $total_salary,
                    "item_id" => $item['item_id'],
                    "employee_id" => $item['employee_id'],
                ];
            }


            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = $item['total_work'] / $total_min;
                $allocated_salary = round($ratio * $total_salary, 2);
            } else {
                $allocated_salary = 0;
            }
            $item_month_list[$employee_key]['allocated_salary'] += $allocated_salary;
            $item_month_list[$employee_key]['work_minutes'] += $item['total_work'];

            $key = $item['employee_id'] . '_' . $item['order_month'];
            if (!isset($total_work_min[$key])) $total_work_min[$key] = 0;
            $total_work_min[$key] += $item['total_work'];
        }
        foreach ($total_work_min as $k => $v) {
            $total_work_min[$k] = round($v / 60, 2) * 100;
        }

        $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();

//        $collect = collect($item_month_list);
//        $employee_count = $collect->groupBy('employee_id')
//            ->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();

        $item_month_list = collect($item_month_list)->transform(function ($item, $index) use ($item_title_key_list, $item_code_key_list, $employee_key_list, &$total_work_min, &$all_salary, &$employee_count) {
            $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['work_hours'] = round($item['work_minutes'] / 60, 2);
            $item['total_hours'] = round($item['total_min'] / 60);
            $key = $item['employee_id'] . '_' . $item['month'];
            if (--$employee_count[$key] > 0) {
                $all_salary[$key] -= $item['allocated_salary'] * 100;
                $total_work_min[$key] -= $item['work_hours'] * 100;

            } else {
                $item['allocated_salary'] = round($all_salary[$key] / 100, 2);
                $item['work_hours'] = round($total_work_min[$key] / 100, 2);
            }
            return $item;
        })->all();
        return [true, $item_month_list];
    }

    public function itemDeviceMonthStatistic($data, $user)
    {
        // 1. 基础校验与时间初始化
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 2. 获取设备工时数据 (项目+设备+月)
        $month_device_list = DailyDwOrderDetails::Clear($user, $data)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "device_id",
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy(DB::raw("FROM_UNIXTIME(order_time, '%Y-%m')"), "item_id", "device_id")->get()->toArray();

        // 3. 获取折旧费主表及详情
        $monthly_dd_query = MonthlyDdOrder::Clear($user, $data)->where('del_time', 0)
            ->where("month", ">=", $month_start)->where("month", "<", $month_end);

        $monthly_dd_ids = $monthly_dd_query->pluck('id')->toArray();
        $monthly_dd_months = $monthly_dd_query->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))
            ->pluck("month_str", 'id')->toArray();

        $device_salary_details = MonthlyDdOrderDetails::wherein('main_id', $monthly_dd_ids)
            ->select("device_id", "depreciation_amount", "main_id")->get()->toArray();

        // 4. 构建高精度折旧池 (单位：分)
        $depreciation_pool = [];
        foreach ($device_salary_details as $val) {
            $m = $monthly_dd_months[$val['main_id']] ?? '';
            if ($m) {
                $key = $val['device_id'] . '_' . $m;
                // 同一个设备在同一个月可能存在多条折旧记录，需累加
                $depreciation_pool[$key] = ($depreciation_pool[$key] ?? 0) + (int)round($val['depreciation_amount'] * 100);
            }
        }

        // 5. 计算全月总工时及初始化余额池
        $device_monthly_total_min = [];
        foreach ($month_device_list as $row) {
            $key = $row['device_id'] . '_' . $row['order_month'];
            $device_monthly_total_min[$key] = ($device_monthly_total_min[$key] ?? 0) + $row['total_work'];
        }

        $rem_depreciation_cents = $depreciation_pool; // 折旧余额池
        $rem_hours_cents = []; // 工时余额池 (小时*100)
        foreach ($device_monthly_total_min as $key => $min) {
            $rem_hours_cents[$key] = (int)round(($min / 60), 2) * 100;
        }

        // 6. 统计项目数用于判断“最后一条”
        $device_count = collect($month_device_list)->groupBy(fn($i) => $i['device_id'].'_'.$i['order_month'])
            ->map(fn($g) => $g->count())->toArray();

        // 7. 预加载设备与项目基础信息
        $items_ids = collect($month_device_list)->pluck('item_id')->unique()->all();
        $device_ids = collect($month_device_list)->pluck('device_id')->unique()->all();

        $item_info = Item::TopClear($user, $data)->whereIn('id', $items_ids)->get()->keyBy('id');
        $device_info = Device::TopClear($user, $data)->whereIn('id', $device_ids)->get()->keyBy('id');

        // 8. 核心转换逻辑
        $item_month_list = collect($month_device_list)->sortBy('order_month')->values()->transform(function ($item) use (
            $item_info, $device_info, $depreciation_pool, $device_monthly_total_min,
            &$device_count, &$rem_depreciation_cents, &$rem_hours_cents
        ) {
            $key = $item['device_id'] . '_' . $item['order_month'];
            $total_min = $device_monthly_total_min[$key] ?? 0;

            $item['item_title'] = $item_info[$item['item_id']]->title ?? "未知项目";
            $item['item_code'] = $item_info[$item['item_id']]->code ?? "-";
            $item['device_title'] = $device_info[$item['device_id']]->title ?? "未知设备";
            $item['device_original'] = $device_info[$item['device_id']]->original_value ?? 0;

            $item['month'] = $item['order_month'];
            $item['total_depreciatio'] = ($depreciation_pool[$key] ?? 0) / 100;
            $item['total_hours'] = round($total_min / 60, 2);

            // 分摊比例计算
            $ratio = $total_min > 0 ? ($item['total_work'] / $total_min) : 0;
            $item['ratio'] = round($ratio, 4);

            if (--$device_count[$key] > 0) {
                // 非最后一条项目
                $cur_hours = round($item['total_work'] / 60, 2);
                $item['hours'] = $cur_hours;
                $rem_hours_cents[$key] -= (int)round($cur_hours * 100);

                $cur_depre = round($item['total_depreciatio'] * $ratio, 2);
                $item['allocated_depreciatio'] = $cur_depre;
                $rem_depreciation_cents[$key] -= (int)round($cur_depre * 100);
            } else {
                // 最后一条项目，余额补平
                $item['hours'] = round(($rem_hours_cents[$key] ?? 0) / 100, 2);
                $item['allocated_depreciatio'] = round(($rem_depreciation_cents[$key] ?? 0) / 100, 2);
                // 比例也做个平账展示
                $item['ratio'] = round($item['hours'] / ($item['total_hours'] ?: 1), 4);
            }

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

        return [true, $item_month_list];
    }

    public function itemDeviceMonthStatistic1($data, $user)
    {
        //项目编码、项目名称、设备名称、项目工时、研发工时占比、设备原值、设备折旧额、本项目帐面归集的折旧额、确定的本项目折旧额、加计调整金额、当月工时、日期
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //确认所有项目、设备、设备工时
        $month_device = DailyDwOrderDetails::Clear($user, $data);
        $month_device_list = $month_device->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "device_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy(DB::raw("FROM_UNIXTIME(order_time, '%Y-%m')"), "item_id", "device_id")->get()->toArray();
        //查询所有人员工资
        $monthly_dd_order_ids = MonthlyDdOrder::Clear($user, $data)->where('del_time', 0)->where("month", ">=", $month_start)->where("month", "<", $month_end)
            ->pluck('id')->toArray();
        $monthly_dd_order_key_list = MonthlyDdOrder::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_device_salary = MonthlyDdOrderDetails::wherein('main_id', $monthly_dd_order_ids)
            ->select("device_id", "depreciation_amount", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $depreciatio_map = [];
        foreach ($month_device_salary as $val) {
            $month = $monthly_dd_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $depreciatio_map[$val['device_id'] . '_' . $month] = $val['depreciation_amount'];
            }
        }
        // 2. 计算每个员工在每个月的全月总工时
        $device_monthly_total_min = [];
        foreach ($month_device_list as $row) {
            $key = $row['device_id'] . '_' . $row['order_month'];
            if (!isset($device_monthly_total_min[$key])) {
                $device_monthly_total_min[$key] = 0;
            }
            $device_monthly_total_min[$key] += $row['total_work'];
        }

        // 3. 计算分摊天数与工资
        $item_month_list = [];
        $device_total_depreciatio = [];
        foreach ($month_device_list as $item) {
            $key = $item['device_id'] . '_' . $item['order_month'];
            $device_key = $item['order_month'] . '_' . $item['item_id'] . '_' . $item['device_id'];
            $total_depreciatio = $depreciatio_map[$key] ?? 0;
            $total_min = $device_monthly_total_min[$key] ?? 0;
            if (!isset($item_month_list[$device_key])) {
                $item_month_list[$device_key] = [
                    "month" => $item['order_month'],
                    "allocated_depreciatio" => 0,
                    "work_minutes" => 0,
                    "total_min" => $total_min,
                    "item_id" => $item['item_id'],
                    "device_id" => $item['device_id'],
                    "total_depreciatio" => $total_depreciatio,
                ];
                $key = $item['device_id'] . '_' . $item['order_month'];
                $device_total_depreciatio[$key] = [
                    'total_hours' => round($total_min/60,2)*100,
                    'total_depreciatio' => $total_depreciatio*100
                ];
            }


            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = round($item['total_work'] / $total_min, 3);
                $allocated_salary = round($ratio * $total_depreciatio, 2);
            } else {
                $ratio = 0;
                $allocated_salary = 0;
            }
            $item_month_list[$device_key]['allocated_depreciatio'] += $allocated_salary;
            $item_month_list[$device_key]['work_minutes'] += $item['total_work'];
            $item_month_list[$device_key]['ratio'] = $ratio;

        }

        foreach ($item_month_list as $k => $v) {
            $item_month_list[$k]['total_hours'] = round($v['total_min'] / 60, 1);
            $item_month_list[$k]['hours'] = round($v['work_minutes'] / 60, 1);
            unset($item_month_list[$k]['work_minutes']);
        }
        $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);
        $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);

        $device_count = $collect->groupBy(function ($item) {
            // 这里的 $item 是集合中的每一行数据
            return $item['device_id'] . '_' . $item['month'];
        })->map(function ($group) {
            return $group->count();
        })->toArray();

        $rate_list = [];
        $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_depreciatio, &$device_count,&$rate_list) {
            $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['device_title'] = $device_key_list[$item['device_id']] ?? "未知人员({$item['device_id']})";
            $item['device_original'] = $device_original_value_key_list[$item['device_id']] ?? "未知人员({$item['device_id']})";
            $key = $item['device_id'] . '_' . $item['month'];
            if(!isset($rate_list[$key])) $rate_list[$key] = 100;
            if (--$device_count[$key] > 0) {
                $rate_list[$key] -= $item['ratio'] * 100;
                $device_total_depreciatio[$key]['total_hours'] -= $item['hours'] * 100;
                $device_total_depreciatio[$key]['total_depreciatio'] -= $item['allocated_depreciatio'] * 100;

            } else {
                $item['ratio'] = round($rate_list[$key] / 100, 2);
                $item['hours'] = round( $device_total_depreciatio[$key]['total_hours']/ 100, 2);
                $item['allocated_depreciatio'] = round( $device_total_depreciatio[$key]['total_depreciatio']/ 100, 2);
            }
            return $item;
        })->all();
        return [true, $item_month_list];
    }


    /**
     * 根据前端 ISO 时间字符串获取该年份的起止日期和时间戳
     * 适配：2019-12-31T16:00:00.000Z 这种带时区的数据
     *
     * @param string $isoStr 前端传来的时间字符串
     * @return array|null
     */
    public function getYearRangeInfo($isoStr)
    {
        if (empty($isoStr)) return null;

        try {
            // 1. 解析 ISO 8601 字符串
            $date = new \DateTime($isoStr);

            // 2. 强制转为中国时区（PRC），处理 16:00:00Z 这种 UTC 偏移
            $date->setTimezone(new \DateTimeZone('PRC'));

            // 3. 提取年份
            $year = $date->format('Y');

            // 4. 构造日期字符串
            $startDate = $year . "-01-01";
            $endDate = $year . "-12-31";

            return [
                 $startDate,
                 $endDate,
            ];
        } catch (\Exception $e) {
//            var_dump($e->getMessage());die;
            return null;
        }
    }

    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_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);

        //组合所有数据
        $return = [];
        foreach ($item_key_list as $v) {

            //其他费用是个数组
            $item_value = [
                "code" => $v['code'],
                "title" => $v['title'],
                "state" => $v['state'] == 3 ? "完结" : "进行中",
                "employee_salary" => isset($item_employee_list[$v['id']]['salary']) ?  round($item_employee_list[$v['id']]['salary']/100,2) : 0,
                "device_depreciation" => isset($item_device_list[$v['id']]['depreciation']) ? round($item_device_list[$v['id']]['depreciation']/100,2): 0,
                "expense_type" => "费用化支出",
                "fee_list" => collect($item_fee_list[$v['id']] ?? [])->values()->all(),
            ];
            $return[] = $item_value;
        }
        return [true, ["list" => $return, "fee_type_list" => $fee_type_list]];

    }

    private function getEmployeeItemSalary($month_start, $month_end, $data, $user)
    {
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        $month_employee_list = $month_employee->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy("order_month", "item_id", "employee_id")->get()->toArray();
        //查询所有人员工资
        $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")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $salary_map[$val['employee_id'] . '_' . $month] = $val['salary'];
                $all_salary[$val['employee_id'] . '_' . $month] = $val['salary']*100;
            }
        }
        // 2. 计算每个员工在每个月的全月总工时
        $employee_monthly_total_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;
            }
            $employee_monthly_total_min[$key] += $row['total_work'];
        }

        // 2. 计算分摊天数与工资
        $item_list = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
//            $item_key = $item['item_id'].'_'.$item['order_month'];
//            if (!isset($item_list[$item_key])) {
//                $item_list[$item_key] = [
//                    "salary" => 0,
//                ];
//            }
            $total_salary = $salary_map[$key] ?? 0;
            $total_min = $employee_monthly_total_min[$key] ?? 0;

            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = $item['total_work'] / $total_min;
                $allocated_salary = round($ratio * $total_salary, 2)*100;
            } else {
                $allocated_salary = 0;
            }
            $item_list[] = [
                'employee_id' => $item['employee_id'],
                'allocated_salary' => $allocated_salary,
                'month' => $item['order_month'],
                'item_id' => $item['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();

        $month_employee_list = $collect->transform(function ($item, $index) use (&$employee_count,&$all_salary) {

            // 如果不是最后一条

            $key = $item['employee_id'] . '_' . $item['month'];
            if(!isset($all_salary[$key])) $item['allocated_salary'] = 0;
            else{
                if (--$employee_count[$key] > 0) {
                    $all_salary[$key] -= $item['allocated_salary'];
                } else {
                    $item['allocated_salary'] = $all_salary[$key];
                }
            }

            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 = DailyDwOrderDetails::Clear($user, $data);
        $month_device_list = $month_device->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "device_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy(DB::raw("FROM_UNIXTIME(order_time, '%Y-%m')"), "item_id", "device_id")->get()->toArray();
        //查询所有人员工资
        $monthly_dd_order_ids = MonthlyDdOrder::Clear($user, $data)->where('del_time', 0)->where("month", ">=", $month_start)->where("month", "<", $month_end)
            ->pluck('id')->toArray();
        $monthly_dd_order_key_list = MonthlyDdOrder::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_device_salary = MonthlyDdOrderDetails::wherein('main_id', $monthly_dd_order_ids)
            ->select("device_id", "depreciation_amount", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $depreciatio_map = [];
        $all_depreciatio = [];
        foreach ($month_device_salary as $val) {
            $month = $monthly_dd_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $depreciatio_map[$val['device_id'] . '_' . $month] = $val['depreciation_amount'];
                $all_depreciatio[$val['device_id'] . '_' . $month] = $val['depreciation_amount']*100;
            }
        }
        // 2. 计算每个员工在每个月的全月总工时
        $device_monthly_total_min = [];
        foreach ($month_device_list as $row) {
            $key = $row['device_id'] . '_' . $row['order_month'];
            if (!isset($device_monthly_total_min[$key])) {
                $device_monthly_total_min[$key] = 0;
            }
            $device_monthly_total_min[$key] += $row['total_work'];
        }

        // 3. 计算分摊天数与工资
        $item_list = [];
        foreach ($month_device_list as $item) {
            $key = $item['device_id'] . '_' . $item['order_month'];
//            $device_key = $item['item_id'];
            $total_depreciatio = $depreciatio_map[$key] ?? 0;
            $total_min = $device_monthly_total_min[$key] ?? 0;
//            if (!isset($item_list[$device_key])) {
//                $item_list[$device_key] = [
//                    "depreciation" => 0,
//                ];
//            }
            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = round($item['total_work'] / $total_min, 3);
                $allocated_salary = round($ratio * $total_depreciatio, 2)*100;
            } else {
                $allocated_salary = 0;
            }
            $item_list[] = [
                'device_id' => $item['device_id'],
                'depreciation' => $allocated_salary,
                'month' => $item['order_month'],
                'item_id' => $item['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();

        $month_employee_list = $collect->transform(function ($item) use (&$device_count,&$all_depreciatio) {

            // 如果不是最后一条
            $key = $item['device_id'] . '_' . $item['month'];
            if (--$device_count[$key] > 0) {
                $all_depreciatio[$key] -= $item['depreciation'];
            } else {
                $item['depreciation'] = $all_depreciatio[$key];
            }
            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]['depreciation'] = 0;
            $return_item_list[$key]['depreciation'] += $v['depreciation'];
        }
//        var_dump($return_item_list);die;
        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' => $type['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'];
            $is_other = $childToRoot[$feeId]['is_other'];
            $title = $childToRoot[$feeId]['title'];
            $sort = $childToRoot[$feeId]['sort'];
            $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' => $is_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;
            }
            $item_key_list[$key][$rootId]['total_amount'] += $item['amount']*100;
            //这边需要拿到头部所有的一级费用类型
            if (!isset($type_list[$rootId])) {
                $type_list[$rootId] = [
                    'sort' => $sort,
                    'id' => $rootId,
                    'title' => $title,
                ];
            }

        }
//        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)
                    ->where('end_time', '<', $month_end);
            })->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);

        //找到项目和设备的费用然后进行比例计算

        $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']/100,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']/100,2) : 0),
                        "fee_id" => $v['fee_id'],
                        "entrust1_amount" => $v['entrust1_amount'],
                        "entrust2_amount" => $v['entrust2_amount'],
                        "type" => $v['type'],
                    ];
                    $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'],
                ];
                $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 = DailyPwOrderDetails::Clear($user, $data);
        $month_employee_list = $month_employee->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy("order_month", "item_id", "employee_id")->get()->toArray();
        //查询所有人员工资
        $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")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $salary_map[$val['employee_id'] . '_' . $month] = $val['salary'];
                $all_salary[$val['employee_id'] . '_' . $month] = $val['salary']*100;
            }
        }
//        var_dump($salary_map);die;
        // 2. 计算每个员工在每个月的全月总工时
        $employee_monthly_total_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;
            }
            $employee_monthly_total_min[$key] += $row['total_work'];
        }

        // 3. 计算分摊天数与工资
//        $item_year_list = [];
        $item_list = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
//            $item_key = $item['item_id'] . '_' . $item['order_month'];
//            if (!isset($item_year_list[$item_key])) {
//                $item_year_list[$item_key] = [
//                    "allocated_salary" => 0,
//                    "item_id" => $item['item_id'],
//                ];
//            }
            $total_salary = $salary_map[$key] ?? 0;
            $total_min = $employee_monthly_total_min[$key] ?? 0;


            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = $item['total_work'] / $total_min;
                $allocated_salary = round($ratio * $total_salary, 2)*100;
            } else {
                $allocated_salary = 0;
            }
            $item_list[] = [
                'employee_id' => $item['employee_id'],
                'allocated_salary' => $allocated_salary,
                'month' => $item['order_month'],
                'item_id' => $item['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();

        $month_employee_list = $collect->transform(function ($item, $index) use (&$employee_count,&$all_salary) {

            // 如果不是最后一条
            $key = $item['employee_id'] . '_' . $item['month'];
            if(!isset($all_salary[$key]))  $item['allocated_salary'] = 0;
            else {
                if (--$employee_count[$key] > 0) {
                    $all_salary[$key] -= $item['allocated_salary'];
                } else {
                    $item['allocated_salary'] = $all_salary[$key];
                }
            }

            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 = DailyDwOrderDetails::Clear($user, $data);
        $month_device_list = $month_device->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "device_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy(DB::raw("FROM_UNIXTIME(order_time, '%Y-%m')"), "item_id", "device_id")->get()->toArray();
        //查询所有人员工资
        $monthly_dd_order_ids = MonthlyDdOrder::Clear($user, $data)->where('del_time', 0)->where("month", ">=", $month_start)->where("month", "<", $month_end)
            ->pluck('id')->toArray();
        $monthly_dd_order_key_list = MonthlyDdOrder::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_device_salary = MonthlyDdOrderDetails::wherein('main_id', $monthly_dd_order_ids)
            ->select("device_id", "depreciation_amount", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $depreciatio_map = [];
        $all_depreciatio = [];
        foreach ($month_device_salary as $val) {
            $month = $monthly_dd_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $depreciatio_map[$val['device_id'] . '_' . $month] = $val['depreciation_amount'];
                $all_depreciatio[$val['device_id'] . '_' . $month] = $val['depreciation_amount']*100;
            }
        }
        // 2. 计算每个员工在每个月的全月总工时
        $device_monthly_total_min = [];
        foreach ($month_device_list as $row) {
            $key = $row['device_id'] . '_' . $row['order_month'];
            if (!isset($device_monthly_total_min[$key])) {
                $device_monthly_total_min[$key] = 0;
            }
            $device_monthly_total_min[$key] += $row['total_work'];
        }

        // 3. 计算分摊天数与工资
        $item_year_list = [];
        $item_list = [];
        foreach ($month_device_list as $item) {
            $key = $item['device_id'] . '_' . $item['order_month'];
//            $device_key = $item['item_id'] . '_' . $item['order_month'];
            $total_depreciatio = $depreciatio_map[$key] ?? 0;
            $total_min = $device_monthly_total_min[$key] ?? 0;
//            if (!isset($item_year_list[$device_key])) {
//                $item_year_list[$device_key] = [
//                    "allocated_depreciation" => 0,
//                ];
//            }


            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
                $ratio = round($item['total_work'] / $total_min, 3);
                $allocated_salary = round($ratio * $total_depreciatio, 2)*100;
            } else {
                $allocated_salary = 0;
            }
//            $item_year_list[$device_key]['allocated_depreciation'] += $allocated_salary;
            $item_list[] = [
                'device_id' => $item['device_id'],
                'depreciation' => $allocated_salary,
                'month' => $item['order_month'],
                'item_id' => $item['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();

        $month_employee_list = $collect->transform(function ($item) use (&$device_count,&$all_depreciatio) {

            // 如果不是最后一条
            $key = $item['device_id'] . '_' . $item['month'];
            if (--$device_count[$key] > 0) {
                $all_depreciatio[$key] -= $item['depreciation'];
            } else {
                $item['depreciation'] = $all_depreciatio[$key];
            }
            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 itemEmployeeSalaryStatistic($data, $user)
    {
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        // 1. 获取人员工时记录
        $month_employee_list = DailyPwOrderDetails::Clear($user, $data)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy("order_month", "item_id", "employee_id")->get()->toArray();

        // 2. 获取工资及配置
        $monthly_ps_order_query = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end);

        $monthly_ps_order_ids = $monthly_ps_order_query->pluck('id')->toArray();
        $monthly_ps_order_key_list = $monthly_ps_order_query
            ->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();

        // 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" => (int)round($val['salary'] * 100),
                    "social_insurance" => (int)round($val['social_insurance'] * 100),
                    "public_housing_fund" => (int)round($val['public_housing_fund'] * 100),
                ];
            }
        }

        // 4. 计算月总工时 (分钟) 及 余额池初始化 (小时*100)
        $employee_monthly_total_min = [];
        foreach ($month_employee_list as $row) {
            $key = $row['employee_id'] . '_' . $row['order_month'];
            $employee_monthly_total_min[$key] = ($employee_monthly_total_min[$key] ?? 0) + $row['total_work'];
        }

        $all_min = [];
        foreach ($employee_monthly_total_min as $key => $min) {
            // 重要修复：以总分钟数计算出全月总小时数，作为余额池的起点
            $all_min[$key] = (int)round($min / 60, 2) * 100;
        }

        // 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'];

            $total_min = $employee_monthly_total_min[$key] ?? 0;
            $ratio = $total_min > 0 ? ($item['total_work'] / $total_min) : 0;

            $item_month_list[$item_key] = [
                "month" => $item['order_month'],
                "employee_id" => $item['employee_id'],
                "item_id" => $item['item_id'],
                "salary" => $salary_map[$key]['salary'] ?? 0,
                "social_insurance" => $salary_map[$key]['social_insurance'] ?? 0,
                "public_housing_fund" => $salary_map[$key]['public_housing_fund'] ?? 0,
                "radio" => round($ratio, 4),
                "total_min_raw" => $total_min, // 保留原始分钟，不在这里修改
                "work_min_raw" => $item['total_work'],
            ];
        }

        // 6. 获取项目和人员信息
        $items = collect($item_month_list)->pluck('item_id')->unique()->all();
        $employee_ids = collect($item_month_list)->pluck('employee_id')->unique()->all();

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

        // 7. 统计每人项目数用于平账
        $employee_count = collect($item_month_list)->groupBy(fn($i) => $i['employee_id'].'_'.$i['month'])
            ->map(fn($g) => $g->count())->toArray();

        // 8. 核心转换
        $result = collect($item_month_list)->sortBy('month')->values()->transform(function ($item) use ($item_info, $employee_key_list, &$employee_count, &$all_salary, &$all_min) {
            $key = $item['employee_id'] . '_' . $item['month'];

            $item['item_title'] = $item_info[$item['item_id']]['title'] ?? "未知项目";
            $item['item_code'] = $item_info[$item['item_id']]['code'] ?? "-";
            $item['employee_title'] = $employee_key_list[$item['employee_id']]['title'] ?? "未知人员";
            $item['major'] = $employee_key_list[$item['employee_id']]['major'] ?? "-";

            // 应出勤工时：固定展示，不参与递减
            $item['total_min'] = round($item['total_min_raw'] / 60, 2);

            if (isset($employee_count[$key]) && --$employee_count[$key] > 0) {
                // 非最后一条项目
                $work_h = round($item['work_min_raw'] / 60, 2);
                $item['work_minutes'] = $work_h;
                $all_min[$key] -= ($work_h * 100);

                // 分摊工资
                $ws = round($item['salary'] * ($item['work_min_raw'] / $item['total_min_raw']), 2);
                $item['work_salary'] = $ws;
                if (isset($all_salary[$key])) $all_salary[$key]['salary'] -= ($ws * 100);

                // 分摊社保
                $wsi = round($item['social_insurance'] * ($item['work_min_raw'] / $item['total_min_raw']), 2);
                $item['work_social_insurance'] = $wsi;
                if (isset($all_salary[$key])) $all_salary[$key]['social_insurance'] -= ($wsi * 100);

                // 分摊公积金
                $wphf = round($item['public_housing_fund'] * ($item['work_min_raw'] / $item['total_min_raw']), 2);
                $item['work_public_housing_fund'] = $wphf;
                if (isset($all_salary[$key])) $all_salary[$key]['public_housing_fund'] -= ($wphf * 100);

            } else {
                // 最后一条，从余额池取值（解决精度）
                $item['work_salary'] = isset($all_salary[$key]) ? round($all_salary[$key]['salary'] / 100, 2) : 0;
                $item['work_social_insurance'] = isset($all_salary[$key]) ? round($all_salary[$key]['social_insurance'] / 100, 2) : 0;
                $item['work_public_housing_fund'] = isset($all_salary[$key]) ? round($all_salary[$key]['public_housing_fund'] / 100, 2) : 0;
                $item['work_minutes'] = isset($all_min[$key]) ? round($all_min[$key] / 100, 2) : 0;
            }

            unset($item['total_min_raw'], $item['work_min_raw']);
            return $item;
        })->all();

        return [true, $result];
    }

    public function itemEmployeeSalaryStatistic1($data, $user)
    {
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];
        //项目编码、项目名称、姓名、人员类别、应出勤工时、研发出勤工时、研发工时占比、归集工资总额、归集社保金额、归集公积金、研发工资总额、研发社保金额、研发公积金
        //获取人员工资项目相关分组数据
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        $month_employee_list = $month_employee->where("order_time", ">=", $month_start)->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 将时间戳转为 Y-m-d 格式并起别名
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m') as order_month"),
                // 聚合求和
                DB::raw("SUM(total_work_min) as total_work")
            )
            ->groupBy("order_month", "item_id", "employee_id")->get()->toArray();
        //查询所有人员工资
        $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();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        $all_salary = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $salary_map[$val['employee_id'] . '_' . $month] = $val;
                $all_salary[$val['employee_id'] . '_' . $month] = [
                    "salary" => $val['salary']*100,
                    "social_insurance" => $val['social_insurance']*100,
                    "public_housing_fund" => $val['public_housing_fund']*100,
                ];
            }
        }
//        var_dump($salary_map);die;
        // 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;
            }
            $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;


            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            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 = Employee::TopClear($user, $data);
        $employee_list = $employee->wherein('id', $employee_ids)->select("major", "title", "id")->get()->toArray();
        $employee_key_list = [];
        foreach ($employee_list as $v) {
            $employee_key_list[$v['id']] = $v;
        }
        $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);
        $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)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, $employee_key_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_key_list[$item['employee_id']]['title'] ?? "未知人员({$item['employee_id']})";
            $item['major'] = $employee_key_list[$item['employee_id']]['major'] ?? "未知人员({$item['employee_id']})";
            $item['total_min'] = round($item['total_min'] / 60, 2);
            $key = $item['employee_id'] . '_' . $item['month'];
            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 {
                $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']);

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

        return [true, $list];
    }

    private function fillEnterpriseRdStatistic($list, $data, $user){
        //项目实际支出 项目费用单
        $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();

        foreach ($list as $key => $value){
            $list[$key]['actual_expenditure'] = $expense_map[$value['id']] ?? 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->from('item_details as i')
            ->where('i.del_time', 0)
            ->where('i.type', ItemDetails::type_one);
        $model = $model->leftJoin('employee as e', 'i.data_id', '=', 'e.id');

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

        $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'];
            }
        }

        $item_map = Item::whereIn('id',array_unique(array_column($list,'item_id')))
            ->pluck('title','id')
            ->toArray();
        foreach ($list as $key => $value){
            $depart_title = $man_map[$value['id']] ?? "";
            $list[$key]['depart_title'] = $depart_title;
            $item_title = $item_map[$value['item_id']] ?? "";
            $list[$key]['item_title'] = $item_title;
            $list[$key]['education'] = Employee::Education[$value['education']] ?? '';
        }

        return $list;
    }
}