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\ExpenseClaims;
use App\Model\ExpenseClaimsDetails;
use App\Model\Fee;
use App\Model\Item;
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 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)
    {
        $model = Item::TopClear($user,$data);
        $start_time = $model->where('del_time',0)
            ->orderby('id', 'desc')->value("start_time");
        $year =  date('Y-01-01',$start_time);
        $data['month_start'] = $year;
//        $data['month_start'] = "2024-01-01";
        list($status, $month_start, $month_end) = $this->commonRule($data);
        if (!$status) return [false, $month_start];

        //当年项目总数
        $intersectCount = $model->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
        ]];
    }

    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){
        // 项目相关查询
        $item = $this->ItemAboutSearch($data, $user);
    }

    private function ItemAboutSearch($data, $user){
        $model = Item::TopClear($user,$data);
        $model = $model->where('del_time',0)
            ->select('title','start_time','end_time','state','charge_id','')
            ->orderby('id', 'desc');
    }
}