workHour/app/Service/StatisticService.php

<?php

namespace App\Service;

use App\Model\AuxiliaryAccount;
use App\Model\AuxiliaryAccountDetails;
use App\Model\CalendarDetails;
use App\Model\DailyDwOrderDetails;
use App\Model\DailyPwOrderDetails;
use App\Model\Device;
use App\Model\Employee;
use App\Model\ExpenseClaims;
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\Model\MonthlyPwOrderDetails;
use App\Model\RuleSet;
use App\Model\RuleSetDetails;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class StatisticService extends Service
{
    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::Clear($user, $data);
        $employee_key_list = $employee->wherein('id', $employee_ids)->pluck("title", "id")->toArray();

        $item = Item::Clear($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();
        $month_employee_list = collect($month_employee_list)->transform(function ($item) use ($employee_key_list, $item_title_key_list, $item_code_key_list) {
            $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']})";
            $item['total_work_hours'] = round($item['total_work'] / 60, 2);
            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 = 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", "salary", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        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'];
            }
        }
//        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);
            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::Clear($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();
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_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']})";
            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 = 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", "salary", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        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'];
            }
        }
        // 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'];
            $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'];
        }

        foreach ($item_month_list as $k => $v) {
            $item_month_list[$k]['work_hours'] = round($v['work_minutes']  / 60,2);
            $item_month_list[$k]['total_hours'] = round($v['total_min']  / 60);
            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::Clear($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::Clear($user, $data);
        $employee_key_list = $employee->wherein('id', $employee_ids)->pluck("title", "id")->toArray();

        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list, $employee_key_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['employee_title'] = $employee_key_list[$item['employee_id']] ?? "未知人员({$item['employee_id']})";
            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 = 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 = [];
        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,
                ];
            }


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

        $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) {
            $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']})";
            return $item;
        })->all();
        return [true, $item_month_list];
    }

    private function commonRule($data)
    {
        if(isset($data['time_range'])){
            $data['month_start'] = $data['time_range'][0] ?? null;
            $data['month_end'] = $data['time_range'][1] ?? null;
        }
        if (!isset($data['month_start'])) $month_start = date('Y-01-01');
        else $month_start = date('Y-m-01', strtotime($data['month_start']));
        if (!isset($data['month_end'])) $month_end = date('Y-01-01', strtotime('+1 year', strtotime($month_start)));
        else {
            $start_year = date('Y', strtotime($month_start));
            $end_year = date('Y', strtotime($data['month_end']));
            if ($start_year != $end_year) return [false, "查询不得跨年!", ""];
            $month_end = date('Y-m-01', strtotime($data['month_end'] . ' +1 month'));
        }
        return [true, strtotime($month_start), strtotime($month_end)];
    }

    public function employeeAttendanceMonthStatistic($data, $user)
    {
        //项目编码、项目名称、项目状态、支出类型、允许加计扣除金额合计、人员人工费用、折旧费用、其他费用、前N项小计、其他相关费用合计、委内费用、委外费用、
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) {
            return [false, $month_start];
        }
        //第一步确定项目
        $item = Item::Clear($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);
            })->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" => $item_employee_list[$v['id']]['salary']??0,
                "device_depreciation" => $item_device_list[$v['id']]['depreciation']??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", "salary", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        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'];
            }
        }

        // 2. 计算分摊天数与工资
        $item_list = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
            $item_key = $item['item_id'];
            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);
            } else {
                $allocated_salary = 0;
            }
            $item_list[$item_key]['salary'] += $allocated_salary;
        }
        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 = [];
        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_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);
            } else {
                $allocated_salary = 0;
            }
            $item_list[$device_key]['depreciation'] += $allocated_salary;
        }
        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::Clear($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']
            ];
        }

        // 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'];
            $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, //委外

                ];
            }
            if($item['entrust_type'] == 1){
                $item_key_list[$key][$rootId]['entrust1_amount'] += $item['amount'];
            }elseif ($item['entrust_type'] == 2){
                $item_key_list[$key][$rootId]['entrust2_amount'] += $item['amount'];
            }
            $item_key_list[$key][$rootId]['total_amount'] += $item['amount'];
            //这边需要拿到头部所有的一级费用类型
            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 [$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::Clear($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);
            })->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::Clear($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",
                "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(!isset($item_key_list[$v['item_id']])) continue;
            if(!isset($item_salary[$v['item_id']])) continue;
            if(!isset($device_depreciation[$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'] == 0 ? "进行中":"已完成" ,
                "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 ?  $item_salary[$v['item_id']]['allocated_salary'] : ($v['type'] ==  2 ? $device_depreciation[$v['item_id']]['allocated_depreciation'] : $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) {
                unset($list[$k]);
                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", "salary", "main_id")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        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'];
            }
        }
//        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 = [];
        foreach ($month_employee_list as $item) {
            $key = $item['employee_id'] . '_' . $item['order_month'];
            $item_key = $item['item_id'];
            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);
            } else {
                $allocated_salary = 0;
            }
            $item_year_list[$item_key]['allocated_salary'] += $allocated_salary;
        }

        return $item_year_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 = [];
        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_year_list = [];
        foreach ($month_device_list as $item) {
            $key = $item['device_id'] . '_' . $row['order_month'] ;
            $device_key = $item['item_id'];
            $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);
            } else {
                $allocated_salary = 0;
            }
            $item_year_list[$device_key]['allocated_depreciation'] += $allocated_salary;
        }

        return $item_year_list;
    }

    public function itemEmployeeSalaryStatistic($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", "salary", "main_id","social_insurance","public_housing_fund")
            ->get()->toArray();
        //查询所有项目人员的工时比例
        //汇总每个人每个月工资
        $salary_map = [];
        foreach ($month_employee_salary as $val) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? '';
            if ($month) {
                $salary_map[$val['employee_id'] . '_' . $month] = $val;
            }
        }
//        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'].'_'.$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::Clear($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::Clear($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();
        //项目编码、项目名称、姓名、人员类别、应出勤工时、研发出勤工时、研发工时占比、归集工资总额、归集社保金额、归集公积金、研发工资总额、研发社保金额、研发公积金
        $item_month_list = collect($item_month_list)->transform(function ($item) use ($item_title_key_list, $item_code_key_list,$employee_key_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['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);
            $item['work_minutes'] = round($item['work_minutes']/60,2);
            $item['work_salary'] = round($item['salary']*$item['radio'],2);
            $item['work_social_insurance'] = round($item['social_insurance']*$item['radio'],2);
            $item['work_public_housing_fund'] = round($item['public_housing_fund']*$item['radio'],2);
            return $item;
        })->all();
        return [true, $item_month_list];
    }


}