workHour/app/Service/Statistic/StatisticCommonService.php

<?php

namespace App\Service\Statistic;


use App\Model\DailyDwOrderDetails;
use App\Model\DailyPwOrderDetails;
use App\Model\MonthlyDdOrder;
use App\Model\MonthlyDdOrderDetails;
use App\Model\MonthlyPsOrder;
use App\Model\MonthlyPsOrderDetails;
use App\Service\Service;
use Illuminate\Support\Facades\DB;

class StatisticCommonService extends Service
{
    /**
     * 传参相关、时间数据自动拼接
     * @param $data
     * @return array
     */
    public function commonRule($data)
    {
        if (!empty($data['year'])) {
            $return = $this->getYearRangeInfo($data['year']);
            if (is_null($return)) return [false, '年度格式错误'];
            list($data['month_start'], $data['month_end']) = $return;
        } else {
            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');
        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)];
    }

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

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

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

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

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

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


    /**
     * 用于拉取对应项目人员日维度的对应工时信息
     * @param $user
     * @param $data
     * @param $month_start
     * @param $month_end
     * @return array
     */
    public function getItemEmployeeDayWorkList($user, $data, $month_start, $month_end)
    {
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        return $month_employee->where("order_time", ">=", $month_start)
            ->where("order_time", "<", $month_end)
            ->where('del_time', 0)
            ->select(
                "item_id",
                "employee_id",
                // 保持别名输出
                DB::raw("FROM_UNIXTIME(order_time, '%Y-%m-%d') as order_date"),
                DB::raw("SUM(total_work_min) as total_work")
            )
            // 2. 优化：不加索引时，把离散度高的字段（id）放前面，
            // 让 MySQL 在临时表里 hash 归类时能快一点点
            ->groupBy("item_id", "employee_id", DB::raw("FROM_UNIXTIME(order_time, '%Y-%m-%d')"))
            ->orderBy("order_date", "asc")
            ->get()
            ->toArray();
    }

    /**
     * 用于拉取对应项目设备日维度的对应工时信息
     * @param $user
     * @param $data
     * @param $month_start
     * @param $month_end
     * @return array
     */
    public function getItemDeviceMonthWorkList($user, $data, $month_start, $month_end)
    {
        $month_device = DailyDwOrderDetails::Clear($user, $data);
        return $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();
    }

    /**
     * 用于拉取对应项目人员月维度的对应工时信息
     * @param $user
     * @param $data
     * @param $month_start
     * @param $month_end
     * @return array
     */
    public function getItemEmployeeMonthWorkList($user, $data, $month_start, $month_end)
    {
        $month_employee = DailyPwOrderDetails::Clear($user, $data);
        return $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();
    }

    /**
     * 统计对应条件的人员月度工资
     * @param $user
     * @param $data
     * @param $month_start
     * @param $month_end
     * @return array
     */
    public function getEmployeeSalary($user, $data, $month_start, $month_end){

        $monthly_ps_order_ids = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)->where("month", ">=", $month_start)->where("month", "<", $month_end)
            ->pluck('id')->toArray();
        $monthly_ps_order_key_list = MonthlyPsOrder::Clear($user, $data)
            ->where('del_time', 0)
            ->where("month", ">=", $month_start)
            ->where("month", "<", $month_end)
            ->select('id', DB::raw("FROM_UNIXTIME(month, '%Y-%m') as month_str"))
            ->pluck('month_str', 'id')
            ->toArray();
        $month_employee_salary = MonthlyPsOrderDetails::wherein('main_id', $monthly_ps_order_ids)
            ->where('del_time',0)->select("employee_id",DB::raw("(base_salary + performance_salary + bonus + other) as salary"), "main_id")
            ->get()->toArray();
        return collect($month_employee_salary)->mapWithKeys(function ($val) use ($monthly_ps_order_key_list) {
            $month = $monthly_ps_order_key_list[$val['main_id']] ?? null;
            // 如果没有找到月份，返回空数组，mapWithKeys 会自动忽略它
            if (!$month) {
                return [];
            }
            return [$val['employee_id'] . '_' . $month => (int)round($val['salary'] * 100)];
        })->toArray();
    }

    /**
     * 统计对应条件的设备月度费用
     * @param $user
     * @param $data
     * @param $month_start
     * @param $month_end
     * @return array
     */
    public function getDeviceAmount($user, $data, $month_start, $month_end){

        $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();
        return collect($month_device_salary)->mapWithKeys(function ($val) use ($monthly_dd_order_key_list) {
            $month = $monthly_dd_order_key_list[$val['main_id']] ?? null;
            // 如果没有找到月份，返回空数组，mapWithKeys 会自动忽略它
            if (!$month) {
                return [];
            }
            return [$val['device_id'] . '_' . $month => (int)round($val['depreciation_amount'] * 100)];
        })->toArray();
    }

    /**
     * 基于key用于统计信息的总数量
     * @param $list
     * @param $key
     * @return array
     */
    public function calculateCount($list, $key)
    {
        //统计人员相关的合计信息，用于补全计算差额
        $collect = collect($list);

        return  $collect->groupBy(function ($item) use ($key) {
            return collect($key)->map(function ($k) use ($item) {
                return $item[$k] ?? '';
            })->implode('_');
        })->map(function ($group) {
            return $group->count();
        })->toArray();
    }
    /**
     * 基于用于统计信息的汇总信息(时)
     * @param $list
     * @param $key
     * @param $sum
     * @return array
     */
    public function calculateSumForHour($list, $key, $sum)
    {
        //统计人员相关的合计信息，用于补全计算差额
        $collect = collect($list);
        return $collect->groupBy(function ($item) use ($key) {
            // 动态拼接分组 Key：例如 "101_2023-10-01"
            return collect($key)->map(fn($k) => $item[$k] ?? '')->implode('_');
        })->map(function ($group) use ($sum) {
            // 1. 动态对指定字段求和，算出小时并保留两位小数
            $hours = round($group->sum($sum) / 60, 2);

            // 2. 乘以 100 并转为整数，存储为分位（解决浮点数精度问题）
            return (int)round($hours * 100);
        })->toArray();

    }
    /**
     * 基于用于统计信息的汇总信息
     * @param $list
     * @param $key
     * @param $sum
     * @return array
     */
    public function calculateSum($list, $key, $sum)
    {
        //统计人员相关的合计信息，用于补全计算差额
        $collect = collect($list);
        return $collect->groupBy(function ($item) use ($key) {
            // 动态拼接分组 Key：例如 "101_2023-10-01"
            return collect($key)->map(fn($k) => $item[$k] ?? '')->implode('_');
        })->map(function ($group) use ($sum) {
            return $group->sum($sum);
        })->toArray();

    }

    /**
     * 计算key的比例和统计分种维度的总计和平均工资
     * @param $month_employee_list
     * @return array
     */
    public function calculateRatioForMonth($month_employee_list,$employee_monthly_total_min,$salary_map,$key1,$key2){
        $item_month_list = [];
        $all_salary = [];
        $all_key_salary = [];
        foreach ($month_employee_list as $item) {

            $key = collect($key1)->map(fn($k) => $item[$k] ?? '')->implode('_');
            $item_key = collect($key2)->map(fn($k) => $item[$k] ?? '')->implode('_');
            if (!isset($item_month_list[$item_key])) {
                $item_month_list[$item_key] = [
                    "month" => $item['order_month'],
                    "total_work" => $item['total_work'],
                    "work_minutes" => 0,
                    "ratio" => 0,
                    "total_min" => 0,
                    "total_salary" => 0,
                    "item_id" => $item['item_id'],
                    "employee_id" => $item['employee_id'],
                    "allocated_salary" => 0,
                ];
            }
            $total_min = $employee_monthly_total_min[$key] ?? 0;
            $total_salary = $salary_map[$key] ?? 0;
            if(!isset($all_key_salary[$key])) {
                if(!isset($all_salary[$item['order_month']])) $all_salary[$item['order_month']] = 0;
                $all_salary[$item['order_month']] += $total_salary;
                $all_key_salary[$key] = 1;
            }
            $item_month_list[$item_key]['total_salary'] = $total_salary;
            $item_month_list[$item_key]['total_hours'] = round($total_min/60,2);
            $item_month_list[$item_key]['total_min'] += $total_salary;
            // B. 计算工资分摊：(项目工时 / 月总工时) * 月工资
            if ($total_min > 0) {
//                var_dump(round($item['total_work']/60,2));
//                var_dump( $item_month_list[$item_key]['total_hours']);die;
//                var_dump();
                $ratio = round(round($item['total_work']/60,2) / $item_month_list[$item_key]['total_hours'],6);
                $allocated_salary = round($ratio * $total_salary);
            } else {
                $allocated_salary = 0;
                $ratio = 0;
            }
            $item_month_list[$item_key]['ratio'] += round($ratio,2);
            $item_month_list[$item_key]['allocated_salary'] += $allocated_salary;
            $item_month_list[$item_key]['work_minutes'] += $item['total_work'];
        }
        return [$item_month_list,$all_salary];
    }


    /**
     * 计算设备的的比例和统计分种维度的总计和平均工资
     * 并且返回总计金额和总计工时
     * @param $month_employee_list
     * @return array
     */
    public function calculateDeviceRatioForMonth($month_device_list,$device_monthly_total_min,$salary_map,$key1,$key2){
        $item_month_list = [];
        $device_total_depreciation = [];
        foreach ($month_device_list as $item) {
            $key = collect($key1)->map(fn($k) => $item[$k] ?? '')->implode('_');
            $item_key = collect($key2)->map(fn($k) => $item[$k] ?? '')->implode('_');
            $total_depreciation = $salary_map[$key] ?? 0;
            $total_min = $device_monthly_total_min[$key] ?? 0;
            if (!isset($item_month_list[$item_key])) {
                $item_month_list[$item_key] = [
                    "month" => $item['order_month'],
                    "allocated_depreciation" => 0,
                    "work_minutes" => 0,
                    "total_min" => $total_min,
                    "item_id" => $item['item_id'],
                    "device_id" => $item['device_id'],
                    "total_depreciation" => $total_depreciation,
                ];
                $device_total_depreciation['total_hours'][$key] =  round($total_min/60,2)*100;
                $device_total_depreciation['total_depreciation'][$key] =  $total_depreciation;
            }


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

        }
        return [$item_month_list,$device_total_depreciation];
    }

    /**
     * 闭包中调用计算余数方法
     * @param $key
     * @param $item
     * @param $count
     * @param $sum
     * @param $word_keys
     * @return void
     */
    public function calculateClosure($key, &$item, &$count, &$sum, $word_keys)
    {
//        数据格式
//        $word_keys = [
//            "employee_work_count" =>
//                [
//                    "key" => "total_work",
//                    "value" => "total_work_hours",
//                    "type" => "hour",
//                ]
//        ];
        foreach ($word_keys as $k => $v) {
            if($v['type'] == 'ratio'&&!isset($sum[$k][$key])) $sum[$k][$key] = 100;
            if( !isset($sum[$k][$key]))  $sum[$k][$key] = 0;
            if (--$count[$k][$key] > 0) {
                if($v['type'] == "hour") $current_hours = round($item[$v['key']] / 60, 2);
                elseif($v['type'] == "money") {
                    $current_hours = round($item[$v['key']] / 100, 2);
                }
                else $current_hours = $item[$v['key']];

                if($v['type'] == "100b") {
                    $sum[$k][$key] -= $current_hours ;
                    $item[$v['value']] = $current_hours/100;
                }
                else{
                    $sum[$k][$key] -= ($current_hours * 100);
                    $item[$v['value']] = $current_hours;
                }
            } else {
                if($v['type'] == "day")   $item[$v['value']] = $sum[$k][$key];
                if($v['type'] == "ratio")   $item[$v['value']] =round($sum[$k][$key] / 100, 4);
                else $item[$v['value']] = round($sum[$k][$key] / 100, 2);
            }

        }

    }


}