workHour/app/Service/BIService.php

<?php

namespace App\Service;

use App\Model\DailyDwOrder;
use App\Model\DailyDwOrderDetails;
use App\Model\DailyPwOrder;
use App\Model\DailyPwOrderDetails;
use App\Model\Employee;
use App\Model\ExpenseClaims;
use App\Model\ExpenseClaimsDetails;
use App\Model\Fee;
use App\Model\Item;
use App\Model\ItemNode;
use App\Model\ItemNodeMission;
use App\Model\ItemNodeMissionContent;
use App\Model\MonthlyDdOrder;
use App\Model\MonthlyDdOrderDetails;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPsOrderDetails;
use App\Model\PLeaveOverOrder;
use App\Model\PLeaveOverOrderDetails;
use App\Model\WorkFlowInstances;
use App\Model\WorkFlowInstancesNodes;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class BIService extends Service
{
    private function commonRule($data)
    {
        if (isset($data['time']) && !empty($data['time'])) {
            $start = $this->changeDateToDate($data['time'][0]);
            $end = $this->changeDateToDate($data['time'][1], true);
            $data['month_start'] = date("Y-m-d", $start);
            $data['month_end'] = date("Y-m-d", $end);
        }

        if (!isset($data['month_start'])) $month_start = date('Y-01-01', strtotime('-1 year'));
        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 homePageData($data, $user)
    {
        // mark 存在需求冲突 如果补录的项目 比如 2015年 那么一开始展示的年份就是2015 和2026年6月10号需求冲突
        // 先注释原逻辑 换新
//        $model = Item::TopClear($user,$data);
//        $start_time = $model->where('del_time',0)
//            ->orderby('id', 'desc')->value("start_time");
        if(isset($data['year'])){
            $start_time = strtotime($data['year']. '-01-01');
        }else{
            $start_time = time();
        }
        $year =  date('Y-01-01', $start_time);
        $data['month_start'] = $year;
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        //当年项目总数
        $model = Item::TopClear($user,$data);
        $intersectCount = $model->where('del_time',0)
            ->where('start_time', '<=', $month_end)   // 项目开始时间 <= 区间结束时间
            ->where('end_time', '>=', $month_start)   // 项目结束时间 >= 区间开始时间
            ->count();

        //人员费用
        list($total_man, $salary_map) = $this->getEmployeeSalary($month_start, $month_end, $data, $user);

        //折旧费用
        list($total_zj, $zj_map) = $this->getDeviceSalary($month_start, $month_end, $data, $user);

        //费用报销
        list($total_other, $other_map, $return_fee) = $this->getFeeItemSalary($month_start, $month_end, $data, $user);

        //研发费用总额
        $total = bcadd(bcadd($total_man, $total_zj,3), $total_other,3);

        //研发费用结构

        $rd = [
            0 => [
                'title' => '人工费用',
                'rate' => $total == 0 ? 0 : bcmul(bcdiv($total_man,$total,4),100,2),
                'rate_unit' => '%',
                'total' => $total_man,
                'total_unit' => '￥',
            ],
            1 => [
                'title' => '折旧费用',
                'rate' => $total == 0 ? 0 :bcmul(bcdiv($total_zj,$total,4),100,2),
                'rate_unit' => '%',
                'total' => $total_zj,
                'total_unit' => '￥',
            ],
            2 => [
                'title' => '费用报销',
                'rate' => $total == 0 ? 0 :bcmul(bcdiv($total_other,$total,4),100,2),
                'rate_unit' => '%',
                'total' => $total_other,
                'total_unit' => '￥',
            ],
        ];

        //研发费用趋势
        $rd_trend = $this->makeTrend($month_start, $salary_map, $zj_map, $other_map);

        //研发费用报销占比
        $rd_rate = $return_fee;

        //加班 请假 总时长
        list($over_time, $leave_time) = $this->overLeave($month_start, $month_end, $data, $user);

        // 项目人员工时汇总
        $man_work = $this->manWork($month_start, $month_end, $data, $user);

        //加计扣除金额
        $statisticService = new StatisticService();
        $attendance = $statisticService->employeeAttendanceMonthStatistic(["year"=>$year, "s" => "/api/employeeAttendanceMonthStatistic"],$user);
        $discount = 0;
        foreach ($attendance[1]['list'] as $v){
            $discount += $v['jj_total_amount']*100;
        }



        return [true, [
            'rd_total' => $total,
            'rd_unit' => '￥',
            'rd_title' => '研发费用总额', //-----
            'discount_total' => round($discount/100,2),
            'discount_unit' => '￥',
            'discount_title' => '加计扣除金额', //------
            'over_time' => $over_time, // 加班
            'leave_time' => $leave_time, // 请假
            'rd' => $rd, //研发费用结构
            'rd_trend' => $rd_trend, //研发费用趋势
            'rd_rate' => $rd_rate, // 研发费用报销占比
            'man_work' => $man_work, //项目人员工时汇总
            'year' => date('Y',strtotime($year)),
            'item_num' => $intersectCount,
            'start_year' => date('Y',time())
        ]];
    }

    private function getEmployeeSalary($month_start, $month_end, $data, $user)
    {
        $monthly_ps_order = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('month','id')
            ->toArray();
        $monthly_ps_order_ids = array_keys($monthly_ps_order);

        $month_employee_salary = MonthlyPsOrderDetails::whereIn('main_id', $monthly_ps_order_ids)
            ->select("base_salary","performance_salary","other","bonus", "main_id")
            ->get()
            ->toArray();
        $total = 0;
        $salary_map = [];
        foreach ($month_employee_salary as $value) {
            $currentAmount = bcadd($value['base_salary'], $value['performance_salary'],3);
            $currentAmount = bcadd($currentAmount, $value['other'],3);
            $currentAmount = bcadd($currentAmount, $value['bonus'],3);

            if(isset($monthly_ps_order[$value['main_id']])){
                $month = $monthly_ps_order[$value['main_id']];
                if(isset($salary_map[$month])){
                    $salary_map[$month] = bcadd($salary_map[$month], $currentAmount,3);
                }else{
                    $salary_map[$month] = $currentAmount;
                }
            }

            $total = bcadd($total, $currentAmount,3);
        }

        return [$total, $salary_map];
    }

    private function getDeviceSalary($month_start, $month_end, $data, $user)
    {
        $monthly_dd_order= MonthlyDdOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('month','id')
            ->toArray();
        $monthly_dd_order_ids = array_keys($monthly_dd_order);

        $month_device_salary = MonthlyDdOrderDetails::whereIn('main_id', $monthly_dd_order_ids)
            ->select("depreciation_amount as amount", "main_id")
            ->get()
            ->toArray();

        $total = 0;
        $amount_map = [];
        foreach ($month_device_salary as $value) {
            $currentAmount = (string)$value['amount'];

            if(isset($monthly_dd_order[$value['main_id']])){
                $month = $monthly_dd_order[$value['main_id']];
                if(isset($amount_map[$month])){
                    $amount_map[$month] = bcadd($amount_map[$month], $currentAmount,3);
                }else{
                    $amount_map[$month] = $currentAmount;
                }
            }

            $total = bcadd($total, $currentAmount,3);
        }

        return [$total, $amount_map];
    }

    private function getFeeItemSalary($month_start, $month_end, $data, $user)
    {
        $e_order = ExpenseClaims::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->pluck('month','id')
            ->toArray();
        $e_order_ids = array_keys($e_order);

        $e_order_detail = ExpenseClaimsDetails::whereIn('expense_claims_id', $e_order_ids)
            ->select("amount", "expense_claims_id as main_id", "fee_id")
            ->get()
            ->toArray();

        $fee = Fee::TopClear($user, $data);
        $fee_map = $fee->whereIn('id', array_unique(array_column($e_order_detail,'fee_id')))->pluck('title','id')->toArray();

        $total = 0;
        $amount_map = [];
        $fee_amount_map = [];
        foreach ($e_order_detail as $value) {
            $currentAmount = (string)$value['amount'];

            if(isset($e_order[$value['main_id']])){
                $month = $e_order[$value['main_id']];
                if(isset($amount_map[$month])){
                    $amount_map[$month] = bcadd($amount_map[$month], $currentAmount,3);
                }else{
                    $amount_map[$month] = $currentAmount;
                }
            }

            $fee_tmp = $fee_map[$value['fee_id']] ?? "";
            if(! empty($fee_tmp)){
                if(isset($fee_amount_map[$fee_tmp])){
                    $fee_amount_map[$fee_tmp] = bcadd($fee_amount_map[$fee_tmp], $currentAmount,3);
                }else{
                    $fee_amount_map[$fee_tmp] = $currentAmount;
                }
            }

            $total = bcadd($total, $currentAmount,3);
        }

        $return_fee = [];
        foreach ($fee_amount_map as $key => $value){
            $amount = (string)$value;
            $return_fee[] = [
                "title" => $key,
                "total" => $amount,
                "total_unit" => "元",
                "rate" => bcmul(bcdiv($amount,$total,4),100,2),
                "rate_unit" => "%"
            ];
        }unset($fee_amount_map);

        return [$total, $amount_map, $return_fee];
    }

    private function makeTrend($month_start, $salary_map, $zj_map, $other_map)
    {
        $trend = [];

        for ($i = 0; $i < 12; $i++) {
            // 计算每个月第一天的时间戳作为 Key
            $timestamp = strtotime("+$i month", $month_start);

            $monthName = ($i + 1) . '月';

            $trend[$timestamp] = [
                'month' => $monthName,
//                'salary' => "0.000",
//                'zj' => "0.000",
//                'other' => "0.000",
                'total' => "0.000",
            ];
        }

        // 2. 将传入的 Map 数据填充进去
        foreach ($trend as $timestamp => &$item) {
            // 使用 ?? "0" 容错，并强制转为 string 保证 bcadd 精度
            $s = $salary_map[$timestamp] ?? "0";
            $z = $zj_map[$timestamp] ?? "0";
            $o = $other_map[$timestamp] ?? "0";

//            $item['salary'] = bcadd($s, "0", 3);
//            $item['zj'] = bcadd($z, "0", 3);
//            $item['other'] = bcadd($o, "0", 3);

            // 计算该月总计
            $item['total'] = bcadd(bcadd($s, $z, 3), $o, 3);
        }

        return array_values($trend);
    }

    private function overLeave($month_start, $month_end, $data, $user)
    {
        $id = PLeaveOverOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->pluck('id')
            ->toArray();

        $p = PLeaveOverOrderDetails::whereIn('main_id', $id)
            ->select("type", "total_min")
            ->get()
            ->toArray();
        $total = $total_1 = 0;
        foreach ($p as $value) {
            $total_min = (string)$value['total_min'];

            if($value['type'] == PLeaveOverOrderDetails::TYPE_ONE){
                $total = bcadd($total, $total_min,2);
            }else{
                $total_1 = bcadd($total_1, $total_min,2);
            }
        }

        // 最终返回或输出前转换
        $total_hours = bcdiv($total, 60, 2);
        $total_1_hours = bcdiv($total_1, 60, 2);

        return [$total_1_hours, $total_hours];
    }

    private function manWork($month_start, $month_end, $data, $user)
    {
        $daily_pw = DailyPwOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->select('id','item_id')
            ->get()->toArray();

        $item_map = Item::whereIn('id',array_unique(array_column($daily_pw,'item_id')))
            ->pluck('title','id')
            ->toArray();
        $p = DailyPwOrderDetails::whereIn('main_id', array_column($daily_pw,'id'))
            ->select("item_id", "total_work_min")
            ->get()
            ->toArray();
        $total = 0;
        $map = [];
        foreach ($p as $value) {
            $total_min = (string)$value['total_work_min'];
            if(isset($map[$value['item_id']])){
                $map[$value['item_id']] = bcadd($map[$value['item_id']], $total_min,2);
            }else{
                $map[$value['item_id']] = $total_min;
            }
            $total = bcadd($total, $total_min,2);
        }
        $return = [];
        foreach ($map as $key => $value){
            $rate = bcmul(bcdiv($value, $total,4),100,2);
            $total_hours = bcdiv($value, 60, 2);

            $return[] = [
                'title' => $item_map[$key] ?? "",
                'total_work_hour' => $total_hours,
                'total_work_hour_unit' => "小时",
                "rate" => $rate,
                "rate_unit" => "%",
            ];
        }

        return $return;
    }

    public function cockpit($data, $user)
    {
        // 1. 声明时区对象
        $utcZone   = new \DateTimeZone('UTC');
        $localZone = new \DateTimeZone('Asia/Shanghai'); // 你的数据库/服务器本地时区（北京时间）

        if (!empty($data['time'][0]) && !empty($data['time'][1])) {
            // 【A. 满足 employeeSalarySummary 的 UTC 干净时间】
            // 提取前端 ISO 串中的纯日期，强制转为 UTC 视角的开始和结束
            $rawStartUtc = new \DateTime($data['time'][0], $utcZone);
            $rawEndUtc   = new \DateTime($data['time'][1], $utcZone);

            $startDateTimeUtc = new \DateTime($rawStartUtc->format('Y-m-d') . ' 00:00:00', $utcZone);
            $endDateTimeUtc   = new \DateTime($rawEndUtc->format('Y-m-d') . ' 23:59:59', $utcZone);

            // 【B. 满足 getManWork 的本地 00点和23点 时间戳】
            $startTimestampLocal = (new \DateTime($rawStartUtc->format('Y-m-d') . ' 00:00:00', $localZone))->getTimestamp();
            $endTimestampLocal   = (new \DateTime($rawEndUtc->format('Y-m-d') . ' 23:59:59', $localZone))->getTimestamp();

        } else {
            // 【没传参时的默认兜底逻辑】
            // UTC 视角的当月起止
            $startDateTimeUtc = new \DateTime('first day of this month 00:00:00', $utcZone);
            $endDateTimeUtc   = new \DateTime('last day of this month 23:59:59', $utcZone);

            // 本地视角的当月起止时间戳
            $startTimestampLocal = (new \DateTime('first day of this month 00:00:00', $localZone))->getTimestamp();
            $endTimestampLocal   = (new \DateTime('last day of this month 23:59:59', $localZone))->getTimestamp();
        }

        $data['start_time'] = $startTimestampLocal;
        $data['end_time']   = $endTimestampLocal;

        $array = $this->getManWork($data, $user);
        // 当月设备工时信息
        $array2 = $this->getDeviceWork($data, $user);
        $array = array_merge_recursive($array, $array2);
        $array3 = $this->getDeviceOldWork($data, $user);
        $array = array_merge_recursive($array, $array3);
        $array3 = $this->getFeeOrder($data, $user);
        $array = array_merge_recursive($array, $array3);

        $passTime = [
            $startDateTimeUtc->format('Y-m-d'),
            $endDateTimeUtc->format('Y-m-d')
        ];

        list($status, $return) = (new StatisticService())->employeeSalarySummary(['time' => $passTime], $user);
        if(! $status) return [false, $return];
        $total = array_sum(array_column($return,'money'));
        $array['rd_total_money'] = $total;

        return [true, $array];
    }

    private function getManWork($data, $user){
        $model = DailyPwOrder::Clear($user, $data);
        $id = $model->where('del_time', 0)
            ->where('order_time', '>=', $data['start_time'])
            ->where('order_time', '<=', $data['end_time'])
            ->pluck('id')
            ->toArray();

        $details = DailyPwOrderDetails::where('del_time', 0)
            ->whereIn('main_id', $id) // 💡 避坑提示：原代码如果是数组 $id，这里建议用 whereIn
            ->select('employee_id', 'total_work_min')
            ->get()->toArray();
        // 1. 计算当月总工时（将二维数组中所有 'total_work_min' 字段求和）
        $totalWorkMin = array_sum(array_column($details, 'total_work_min'));

        // 2. 如果你需要把分钟数转换为“小时”，可以除以 60（保留两位小数，根据业务需要决定是否转换）
        $totalWorkHours = round($totalWorkMin / 60, 2);

        // 3. 计算参与人员总数（先提取所有员工ID，再通过 array_unique 去重，最后 count 计算数量）
        $employeeIds = array_column($details, 'employee_id');
        $uniqueEmployeeIds = array_unique($employeeIds);
        $totalEmployees = count($uniqueEmployeeIds);


        return [
            'man_total_work_hours' => $totalWorkHours, // 当月总工时
            'man_total_employees'  => $totalEmployees,  // 参与人员总数
        ];
    }

    private function getDeviceWork($data, $user){
        $model = DailyDwOrder::Clear($user, $data);
        $id = $model->where('del_time', 0)
            ->where('order_time', '>=', $data['start_time'])
            ->where('order_time', '<=', $data['end_time'])
            ->pluck('id')
            ->toArray();

        $details = DailyDwOrderDetails::where('del_time', 0)
            ->whereIn('main_id', $id) // 💡 避坑提示：原代码如果是数组 $id，这里建议用 whereIn
            ->select('device_id', 'total_work_min')
            ->get()->toArray();
        // 1. 计算当月总工时（将二维数组中所有 'total_work_min' 字段求和）
        $totalWorkMin = array_sum(array_column($details, 'total_work_min'));

        // 2. 如果你需要把分钟数转换为“小时”，可以除以 60（保留两位小数，根据业务需要决定是否转换）
        $totalWorkHours = round($totalWorkMin / 60, 2);

        $employeeIds = array_column($details, 'device_id');
        $uniqueEmployeeIds = array_unique($employeeIds);
        $totalEmployees = count($uniqueEmployeeIds);

        return [
            'device_total_work_hours' => $totalWorkHours, // 当月总工时
            'device_total_num'  => $totalEmployees,  // 参与人员总数
        ];
    }

    private function getDeviceOldWork($data, $user){
        $model = MonthlyDdOrder::Clear($user, $data);
        $id = $model->where('del_time', 0)
            ->where('month', '>=', $data['start_time'])
            ->where('month', '<=', $data['end_time'])
            ->pluck('id')
            ->toArray();

        $details = MonthlyDdOrderDetails::where('del_time', 0)
            ->whereIn('main_id', $id)
            ->select('device_id', 'depreciation_amount')
            ->get()->toArray();
        $totalWorkMin = array_sum(array_column($details, 'depreciation_amount'));

        return [
            'device_total_money' => $totalWorkMin,
        ];
    }

    private function getFeeOrder($data, $user){
        $model = ExpenseClaims::Clear($user, $data);
        $id = $model->where('del_time', 0)
            ->where('month', '>=', $data['start_time'])
            ->where('month', '<=', $data['end_time'])
            ->pluck('id')
            ->toArray();

        $details = ExpenseClaimsDetails::where('del_time', 0)
            ->whereIn('expense_claims_id', $id)
            ->select('item_id', 'amount')
            ->get()->toArray();
        $totalWorkMin = array_sum(array_column($details, 'amount'));

        $employeeIds = array_column($details, 'item_id');
        $uniqueEmployeeIds = array_unique($employeeIds);
        $totalEmployees = count($uniqueEmployeeIds);
        return [
            'fee_total_money' => $totalWorkMin,
            'fee_total_num' => $totalEmployees,
        ];
    }

    public function homePageItemData($data, $user){
        $startOfMonth = Carbon::now()->startOfMonth()->timestamp;
        $endOfMonth   = Carbon::now()->endOfMonth()->timestamp;
        $data['start_now_month'] = $startOfMonth;
        $data['end_now_month'] = $endOfMonth;

        // 进行中项目数量
        $item_count = $this->ItemAboutSearch($data, $user);
        // 进行中节点数量
        $item_node_count = $this->ItemNodeAboutSearch($data, $user);
        // 待审批数量(任务)
        $item_node_mission_wait_count = $this->ItemNodeMissionWaitAboutSearch($data, $user);
        // 超期任务数量  本月已完成任务数量 符合条件的当月任务
        list($num1, $num2, $mission) = $this->ItemNodeMissionAboutSearch($data, $user);
        // 🌟人效分析 人员工时对比（这里传入了当月筛选出的任务集）
        $man_about = $this->ItemNodeMissionAboutManSearch($data, $user, $mission);
        // 近六个月工时比较
        $last_six_about = $this->ItemNodeMissionAboutLastSixMonthSearch($data, $user);
        // 项目工时与人效排名
        $item_man_about = $this->ItemManSearch($data, $user);

        return [true, [
            'item_count' => $item_count,
            'item_node_count' => $item_node_count,
            'item_node_mission_wait_count' => $item_node_mission_wait_count,
            'cq_num' => $num1,
            'wc_num' => $num2,
            'rx' => $man_about['sub'],
            'employee_work_list' => $man_about['employee_work_list'],
            'last_six_month' => $last_six_about,
            'item_man_abount' => $item_man_about
        ]];
    }

    private function ItemAboutSearch($data, $user){
        $model = Item::TopAndEmployeeClear($user, $data);
        return $model->where('del_time', 0)
            ->where('state', Item::TYPE_TWO)
            ->where('start_time', '<=', $data['end_now_month'])
            ->where('end_time', '>=', $data['start_now_month'])
            ->count();
    }

    private function ItemNodeAboutSearch($data, $user){
        $model = ItemNode::TopAndEmployeeClear($user, $data);
        return $model->where('del_time', 0)
            ->where('state', ItemNode::TYPE_TWO)
            ->where('start_time', '<=', $data['end_now_month'])
            ->where('end_time', '>=', $data['start_now_month'])
//            ->where('start_time', '<=', $data['start_now_month'])   // 开始时间早于或等于当月结束
//            ->where('end_time', '>=', $data['end_now_month'])   // 结束时间晚于或等于当月开始
            ->count();
    }

    private function ItemNodeMissionWaitAboutSearch($data, $user){
//        return WorkFlowInstances::where('del_time', 0)
//            ->where('document_type', 'item_node_mission')
//            ->where('crt_time', '>=', $data['start_now_month'])
//            ->where('crt_time', '<=', $data['end_now_month'])
//            ->where('status', WorkFlowInstances::status_two)
//            ->where('top_depart_id', $user['top_depart_id'])
//            ->count();

        return WorkFlowInstancesNodes::from('workflow_instance_nodes as nodes') // 给主表起个别名
        ->join('workflow_instances as instances', 'nodes.instance_id', '=', 'instances.id') // 直接连表，注意：如果你的外键不是 instance_id，请在这里修改
        ->where('nodes.del_time', 0)
            ->where('nodes.top_depart_id', $user['top_depart_id'])
            ->where('nodes.status', 1)
            ->whereRaw('JSON_CONTAINS(nodes.assignees, JSON_OBJECT("id", ?))', [$user['id']])
            ->whereRaw('NOT JSON_CONTAINS(nodes.assignees, JSON_OBJECT("id", ?, "is_pass", 1))', [$user['id']])
            ->where('instances.del_time', 0)
            ->where('instances.document_type', 'item_node_mission')
            ->where('instances.crt_time', '>=', $data['start_now_month'])
            ->where('instances.crt_time', '<=', $data['end_now_month'])
            ->where('instances.status', 'pending')
            ->count();
    }

    private function ItemNodeMissionAboutSearch($data, $user){
        $model = ItemNodeMission::TopAndEmployeeClear($user,$data);

        // 🌟 核心确保：不仅卡死当月区间，同时 select 出分摊所必需的 'start_time' 和 'end_time'
        $list =  $model->where('del_time',0)
            ->where('start_time', '<=', $data['end_now_month'])
            ->where('end_time', '>=', $data['start_now_month'])
            ->select('state', 'end_time', 'id', 'due_work_hour', 'start_time')
            ->get()->toArray();

        $overdueCount = 0;   // 已超期数量
        $completedCount = 0; // 已完成数量
        $now = time();       // 获取当前时间戳

        foreach ($list as $item) {
            if ($item['state'] == ItemNodeMission::TYPE_THREE) {
                $completedCount++;
            }

            if ($item['state'] == ItemNodeMission::TYPE_MINUS_TWO || $item['end_time'] < $now) {
                $overdueCount++;
            }
        }

        return [
            $overdueCount,
            $completedCount,
            $list
        ];
    }

    private function ItemNodeMissionAboutManSearch($data, $user, $mission){
        $mission_id = array_column($mission, 'id');

        // 🌟 严格限制：只查询当前月份内、且属于这批任务的实际工时单据 (防止把历史月份工时带入当前人员统计)
        $day_work_list = ItemNodeMissionContent::where('del_time',0)
            ->whereIn('item_node_mission_id', $mission_id)
            ->where('order_time', '>=', $data['start_now_month'])
            ->where('order_time', '<=', $data['end_now_month'])
            ->select('data_id as employee_id', 'total_work_min', 'item_id', 'item_node_mission_id')
            ->get()->toArray();

        $emp_map = Employee::whereIn('id', array_unique(array_column($day_work_list,'employee_id')))
            ->pluck('title','id')
            ->toArray();

        $total_work_minute = 0;
        $employee_keys = [];

        // 规整当前筛选月份的时间戳边界
        $start_timestamp = is_numeric($data['start_now_month']) ? (int)$data['start_now_month'] : strtotime($data['start_now_month']);
        $end_timestamp   = is_numeric($data['end_now_month']) ? (int)$data['end_now_month'] : strtotime($data['end_now_month']);

        // 1. 预先计算好每个任务在【查询当月】的生命周期交集与分摊预计工时
        $yj_map = [];
        foreach ($mission as $m) {
            $m_id = $m['id'];

            // 🌟 强力兼容：不论数据库存的是 Timestamp 还是 Y-m-d 字符串，一律规整为标准时间戳计算天数
            $m_start = is_numeric($m['start_time']) ? (int)$m['start_time'] : strtotime($m['start_time']);
            $m_end   = is_numeric($m['end_time']) ? (int)$m['end_time'] : strtotime($m['end_time']);

            if ($m_start && $m_end && $m_start <= $end_timestamp && $m_end >= $start_timestamp) {
                // 计算任务总天数
                $total_days = ceil(($m_end - $m_start) / 86400);
                if ($total_days <= 0) $total_days = 1;

                // 计算当月重叠区间
                $overlap_start = max($m_start, $start_timestamp);
                $overlap_end   = min($m_end, $end_timestamp);
                $overlap_days  = ceil(($overlap_end - $overlap_start) / 86400);
                if ($overlap_days < 0) $overlap_days = 0;

                // 算出该任务在当月应该分摊到的工时
                $yj_map[$m_id] = ($m['due_work_hour'] / $total_days) * $overlap_days;
            } else {
                $yj_map[$m_id] = 0;
            }
        }

        $employee_array = [];

        // 2. 第一步：纯粹统计当月实际工时，并将任务 ID 归并到人头上
        foreach ($day_work_list as $work) {
            $emp_id = $work['employee_id'];
            if (empty($emp_id)) continue;

            $total_work_minute += $work['total_work_min'];
            $employee_keys[$emp_id] = true;

            if (isset($employee_array[$emp_id])) {
                $employee_array[$emp_id]['actual_work'] += $work['total_work_min'];
                if (!in_array($work['item_node_mission_id'], $employee_array[$emp_id]['mission_id'])) {
                    $employee_array[$emp_id]['mission_id'][] = $work['item_node_mission_id'];
                }
            } else {
                $employee_array[$emp_id] = [
                    'employee_id'    => $emp_id,
                    'employee_title' => $emp_map[$emp_id] ?? '',
                    'actual_work'    => $work['total_work_min'],
                    'mission_id'     => [$work['item_node_mission_id']],
                    'yj_work'        => 0, // 先占位
                ];
            }
        }

        // 3. 第二步：按人头独立参与的任务集合，精准分配分摊预计工时，同时记录已被触发的有效任务
        $valid_mission_ids = [];
        foreach ($employee_array as $emp_id => $emp_info) {
            $emp_yj_work = 0;
            foreach ($emp_info['mission_id'] as $m_id) {
                $emp_yj_work += $yj_map[$m_id] ?? 0;
                $valid_mission_ids[$m_id] = true; // 记录当月实际有人填报的任务
            }

            $employee_array[$emp_id]['actual_work'] = round($emp_info['actual_work'] / 60, 2);
            $employee_array[$emp_id]['yj_work']     = round($emp_yj_work, 2);
        }

        // 根据实际工时降序
        usort($employee_array, function($a, $b) {
            return $b['actual_work'] <=> $a['actual_work'];
        });

        $total_work_hours = round($total_work_minute / 60, 2);
        $employee_count   = count($employee_keys);

        // 🌟 核心对齐：总预计工时【只计算当前出现在有效人员列表里的分摊工时总和】，确保和大盘数据指标完全对齐闭环
        $total_yj_work_hours = 0;
        foreach (array_keys($valid_mission_ids) as $m_id) {
            $total_yj_work_hours += $yj_map[$m_id] ?? 0;
        }

        $num        = '0.00';
        $num_yj     = '0.00';
        $num_rate   = '0.00';
        $num_rate_2 = '0.00';
        $num_rate_3 = '0.00';
        $num_rate_4 = '0.00';
        $num_rate_5 = '0.00';

        if ($employee_count > 0) {
            $num = bcdiv($total_work_hours, $employee_count, 2);
            $num_yj = bcdiv($total_yj_work_hours, $employee_count, 2);
        }

        if (bccomp($num_yj, '0.00', 2) > 0) {
            $num_rate = bcmul(bcdiv($num, $num_yj, 4), 100, 2);
        }

        if ($total_yj_work_hours > 0 && $employee_count > 0) {
            $num_rate_2 = bcmul(bcdiv($total_work_hours, bcmul($total_yj_work_hours, $employee_count, 4), 4), 100, 2);
        }

        if ($total_yj_work_hours > 0) {
            $num_rate_3 = bcmul(bcdiv($total_work_hours, $total_yj_work_hours, 4), 100, 2);
        }

        // 成本人效比计算保持原有逻辑
        $total = Item::where('del_time', 0)->whereIn('id', array_unique(array_column($day_work_list, 'item_id')))->sum('budget');
        $monthly_ps_order = MonthlyPsOrder::Clear($user, $data)->where('del_time', 0)->where("month", ">=", $data['start_now_month'])
            ->where("month", "<", $data['end_now_month'])->pluck('id')->toArray();
        $month_employee_salary = MonthlyPsOrderDetails::whereIn('main_id', $monthly_ps_order)->select("base_salary", "performance_salary", "other", "bonus", "main_id")->get()->toArray();

        $man_total = 0;
        foreach ($month_employee_salary as $value) {
            $currentAmount = bcadd($value['base_salary'], $value['performance_salary'], 3);
            $currentAmount = bcadd($currentAmount, $value['other'], 3);
            $currentAmount = bcadd($currentAmount, $value['bonus'], 3);
            $man_total     = bcadd($man_total, $currentAmount, 3);
        }

        if (bccomp($man_total, '0.000', 3) > 0) {
            $num_rate_4 = bcmul(bcdiv($total, $man_total, 4), 100, 2);
            $num_rate_5 = bcmul(bcdiv(bcdiv($total, $man_total, 4), 4, 4), 100, 2);
        }

        $man_final = round((float)$num_rate_3 * 0.6 + (float)$num_rate_4 * 0.4, 2);

        return [
            'employee_work_list'        => $employee_array,
            'sub' => [
                'average_actual_hours'      => $num,
                'average_du_hours'          => $num_yj,
                'average_actual_hours_rate' => $num_rate,
                'load_rate'                 => $num_rate_2,
                'achievement_rate'          => $num_rate_3,
                'man_rate'                  => $num_rate_4,
                'man_rate_2'                => $num_rate_5,
                'man_final'                 => $man_final
            ]
        ];
    }

    private function ItemNodeMissionAboutLastSixMonthSearch($data, $user)
    {
        // 1. 先计算出近 6 个月的大总区间（最远月份的月初 到 当月的月末）
        $oldestMonthStart = date('Y-m-01 00:00:00', strtotime("-5 month"));
        $currentMonthEnd  = date('Y-m-t 23:59:59'); // 当月月末

        $all_start_timestamp = strtotime($oldestMonthStart);
        $all_end_timestamp   = strtotime($currentMonthEnd);

        // 2. 一次性捞出近 6 个月内所有可能相交的任务
        $mission_model = ItemNodeMission::TopAndEmployeeClear($user, $data);
        $all_missions = $mission_model->where('del_time', 0)
            ->where('start_time', '<=', $all_end_timestamp)
            ->where('end_time', '>=', $all_start_timestamp)
            ->select('id', 'due_work_hour', 'start_time', 'end_time')
            ->get()
            ->toArray();

        $all_mission_ids = array_column($all_missions, 'id');

        // 3. 一次性查出这些任务所有的实际工时记录（把 crt_time 换成你表里的 order_time）
        $all_work_contents = [];
        if (!empty($all_mission_ids)) {
            $all_work_contents = ItemNodeMissionContent::where('del_time', 0)
                ->whereIn('item_node_mission_id', $all_mission_ids)
                ->select('item_node_mission_id', 'total_work_min', 'order_time') // 👈 查出单据日期
                ->get()
                ->toArray();
        }

        $result = [];

        // 4. 以时间为驱动循环 6 次
        for ($i = 5; $i >= 0; $i--) {
            $monthStart = date('Y-m-01 00:00:00', strtotime("-$i month"));
            $monthEnd   = date('Y-m-t 23:59:59', strtotime("-$i month"));

            $start_timestamp = strtotime($monthStart);
            $end_timestamp   = strtotime($monthEnd);
            $month_name      = date('Y-m', $start_timestamp);

            $month_due_hours   = 0;
            $month_actual_mins = 0;

            // 5. 计算预计工时：按任务生命周期交集 + 天数均摊
            if (!empty($all_missions)) {
                foreach ($all_missions as $mission) {
                    // 判断当前任务是否与当前月份有交集
                    if ($mission['start_time'] <= $end_timestamp && $mission['end_time'] >= $start_timestamp) {

                        // A. 计算任务总生命周期的总天数
                        $total_days = ceil(($mission['end_time'] - $mission['start_time']) / 86400);
                        if ($total_days <= 0) $total_days = 1;

                        // B. 计算当前月份与该任务的【重叠区间】
                        $overlap_start = max($mission['start_time'], $start_timestamp);
                        $overlap_end   = min($mission['end_time'], $end_timestamp);

                        // C. 计算这个月里，该任务占用了多少天
                        $overlap_days = ceil(($overlap_end - $overlap_start) / 86400);
                        if ($overlap_days < 0) $overlap_days = 0;

                        // D. 按比例分摊预计工时，并累加到当前月份
                        $share_due_hour = ($mission['due_work_hour'] / $total_days) * $overlap_days;
                        $month_due_hours += $share_due_hour;
                    }
                }
            }

            // 6. ✨ 计算实际工时：严格根据单据日期 (order_time) 划归到具体的自然月
            if (!empty($all_work_contents)) {
                foreach ($all_work_contents as $work) {
                    // 只有当工时记录的单据日期在当前月份区间内，才计入该月
                    if ($work['order_time'] >= $start_timestamp && $work['order_time'] <= $end_timestamp) {
                        $month_actual_mins += $work['total_work_min'];
                    }
                }
            }

            // 7. 组装当前月份的数据结果
            $result[] = [
                'month'              => $month_name,
                'total_due_hours'    => round($month_due_hours, 2),
                'total_actual_hours' => round($month_actual_mins / 60, 2)
            ];
        }

        return $result;
    }

    private function ItemManSearch($data, $user)
    {
        // 1. 获取当月进行中的项目
        $model = Item::TopAndEmployeeClear($user, $data);
        $item_list = $model->where('del_time', 0)
            ->whereIn('state', [Item::TYPE_TWO, Item::TYPE_THREE])
            ->where('start_time', '<=', $data['end_now_month'])
            ->where('end_time', '>=', $data['start_now_month'])
            ->select('id', 'title', 'budget') // 👈 增加选出项目总金额 budget 用于计算产值/成本
            ->get()->toArray();

        if (empty($item_list)) {
            return [];
        }

        $item_ids = array_column($item_list, 'id');
        $start_timestamp = (int)$data['start_now_month'];
        $end_timestamp   = (int)$data['end_now_month'];

        // ==========================================
        // 🛠️ 核心步骤一：计算本月全员总实际工时与总工资
        // ==========================================
        // A. 本月全员总实际工时 (分钟)
        $all_employee_actual_mins = ItemNodeMissionContent::where('del_time', 0)
            ->where('order_time', '>=', $start_timestamp)
            ->where('order_time', '<=', $end_timestamp)
            ->sum('total_work_min');
        $all_employee_actual_hours = round($all_employee_actual_mins / 60, 2);

        // B. 本月全员总工资额
        $monthly_ps_order = MonthlyPsOrder::Clear($user, $data)->where('del_time', 0)
            ->where("month", ">=", $data['start_now_month'])
            ->where("month", "<", $data['end_now_month'])
            ->pluck('id')->toArray();
        $month_employee_salary = MonthlyPsOrderDetails::whereIn('main_id', $monthly_ps_order)
            ->select("base_salary", "performance_salary", "other", "bonus")
            ->get()->toArray();

        $total_salary = 0;
        foreach ($month_employee_salary as $value) {
            $currentAmount = bcadd($value['base_salary'], $value['performance_salary'], 3);
            $currentAmount = bcadd($currentAmount, $value['other'], 3);
            $currentAmount = bcadd($currentAmount, $value['bonus'], 3);
            $total_salary  = bcadd($total_salary, $currentAmount, 3);
        }

        // ==========================================
        // 🛠️ 核心步骤二：获取项目下当月的任务并均摊预计工时
        // ==========================================
        // 获取这批项目下的所有任务
        $missions = ItemNodeMission::where('del_time', 0)
            ->whereIn('item_id', $item_ids)
            ->where('start_time', '<=', $end_timestamp)
            ->where('end_time', '>=', $start_timestamp)
            ->select('id', 'item_id', 'due_work_hour', 'start_time', 'end_time')
            ->get()->toArray();

        // 按项目归总分摊后的预计工时
        $item_yj_work_map = [];
        foreach ($missions as $m) {
            $proj_id = $m['item_id'];
            $m_start = is_numeric($m['start_time']) ? (int)$m['start_time'] : strtotime($m['start_time']);
            $m_end   = is_numeric($m['end_time']) ? (int)$m['end_time'] : strtotime($m['end_time']);

            if ($m_start && $m_end && $m_start <= $end_timestamp && $m_end >= $start_timestamp) {
                // 任务总寿命天数
                $total_days = ceil(($m_end - $m_start) / 86400);
                if ($total_days <= 0) $total_days = 1;

                // 当月重叠天数
                $overlap_start = max($m_start, $start_timestamp);
                $overlap_end   = min($m_end, $end_timestamp);
                $overlap_days  = ceil(($overlap_end - $overlap_start) / 86400);
                if ($overlap_days < 0) $overlap_days = 0;

                // 分摊到当月的预计工时
                $share_yj_hour = ($m['due_work_hour'] / $total_days) * $overlap_days;

                if (!isset($item_yj_work_map[$proj_id])) {
                    $item_yj_work_map[$proj_id] = 0;
                }
                $item_yj_work_map[$proj_id] += $share_yj_hour;
            }
        }

        // ==========================================
        // 🛠️ 核心步骤三：获取项目下当月的实际工时
        // ==========================================
        $work_contents = ItemNodeMissionContent::where('del_time', 0)
            ->whereIn('item_id', $item_ids)
            ->where('order_time', '>=', $start_timestamp)
            ->where('order_time', '<=', $end_timestamp)
            ->select('item_id', 'total_work_min')
            ->get()->toArray();

        // 按项目归总实际工时（分钟转换为小时）
        $item_actual_work_map = [];
        foreach ($work_contents as $work) {
            $proj_id = $work['item_id'];
            if (!isset($item_actual_work_map[$proj_id])) {
                $item_actual_work_map[$proj_id] = 0;
            }
            $item_actual_work_map[$proj_id] += $work['total_work_min'];
        }

        // ==========================================
        // 🛠️ 核心步骤四：组装数据并计算各项人效指标
        // ==========================================
        foreach ($item_list as &$item) {
            $id = $item['id'];

            // 1. 预计工时与实际工时
            $yj_work     = isset($item_yj_work_map[$id]) ? round($item_yj_work_map[$id], 2) : 0.00;
            $actual_work = isset($item_actual_work_map[$id]) ? round($item_actual_work_map[$id] / 60, 2) : 0.00;

            $item['yj_work']     = $yj_work;
            $item['actual_work'] = $actual_work;

            // 2. 计算工时达成率 = 实际 ÷ 预计 × 100%
            $item['achievement_rate'] = '0.00';
            if ($yj_work > 0) {
                $item['achievement_rate'] = bcmul(bcdiv($actual_work, $yj_work, 4), 100, 2);
            }

            // 3. 计算项目人力成本 = 项目实际工时 × (本月工资总额 ÷ 本月全员实际工时)
            $item_cost = '0.00';
            if ($all_employee_actual_hours > 0) {
                // 平均时薪 = 工资总额 ÷ 全员实际工时
                $hourly_wage = bcdiv($total_salary, $all_employee_actual_hours, 4);
                // 项目人力成本 = 项目实际工时 × 平均时薪
                $item_cost = bcmul($actual_work, $hourly_wage, 2);
            }
            $item['item_cost'] = $item_cost;

            // 4. 计算产值/成本 = 项目总金额 ÷ 项目人力成本
            $item['production_cost_rate'] = '0.00';
            if (bccomp($item_cost, '0.00', 2) > 0) {
                $item['production_cost_rate'] = bcdiv($item['budget'], $item_cost, 2);
            }
        }

        return $item_list;
    }
}