hqyt/app/Service/StatisticsService.php

<?php

namespace App\Service;

use App\Model\CalendarDetails;
use App\Model\Device;
use App\Model\DeviceDetails;
use App\Model\Employee;
use App\Model\EmployeeDetails;
use App\Model\EmployeeRole;
use App\Model\Item;
use App\Model\RD;
use App\Model\RdDetails;
use App\Model\RdLock;
use Carbon\Carbon;
use Illuminate\Support\Facades\DB;

class StatisticsService extends Service
{
    public function statisticsEmployeeCommon($data,$user){
        if(empty($data['order_time'][0]) || empty($data['order_time'][1])) return [false, '时间不能为空'];

        $return = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
        $start_time = $return[0];
        $end_time = $return[1];
        if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "时间的区间无效"];

        $day = $this->returnDays([
            0 => $start_time,
            1 => $end_time,
        ]);
        if($day > 365) return [false, '查询时间仅支持范围区间在365天内'];

        $model = RdDetails::from('rd_details as a')
            ->leftJoin('rd as b','b.id','a.rd_id')
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->where('b.order_time', '>=', $start_time)
            ->where('b.order_time', '<=', $end_time)
            ->where('a.type', RdDetails::type_one)
            ->select('b.item_id','b.total_hours','a.data_id')
            ->orderby('b.order_time', 'desc');

        if(! empty($data['item_code'])) {
            $item_id = Item::where('code', 'LIKE', '%'.$data['item_code'].'%')
                ->where('del_time',0)
                ->pluck('id')
                ->toArray();
            $model->whereIn('b.item_id', $item_id);
        }

        if(! empty($data['id'])){
            $model->whereIn('a.data_id', $data['id']);
        }

        if(! empty($data['employee_title'])) {
            $data_id = Employee::where('emp_name', 'LIKE', '%'.$data['employee_title'].'%')
                ->where('del_time',0)
                ->pluck('id')
                ->toArray();
            $model->whereIn('a.data_id', $data_id);
        }

        $time = $this->getMonthsFirstDayTimestamps([
            0 => $start_time,
            1 => $end_time,
        ]);

        $day = CalendarDetails::where('del_time',0)
            ->where('time', '>=', $start_time)
            ->where('time', '<=', $end_time)
            ->where('is_work', 1)
            ->count();

        return [true, [$model, $time, $day]];
    }

    public function statisticsEmployee($data,$user){
        list($status, $return) = $this->statisticsEmployeeCommon($data, $user);
        if(! $status) return [false, $return];
        list($model, $ergs, $day) = $return;
        $list = $model->get()->toArray();
        $list = $this->statisticsEmployeeFillData($list, $ergs, $day);

//        $list['count'] = $this->countTotal($list, $user['header_default']);
        return [true, $list];
    }

    public function statisticsEmployeeFillData($data, $ergs, $day){
        $employee_id = EmployeeRole::where('del_time',0)
            ->where('role_id', 87)
            ->pluck('employee_id')
            ->toArray();

        $employee_hours_map = EmployeeDetails::where('del_time', 0)
            ->whereIn('time', $ergs)
            ->whereIn('employee_id',$employee_id)
            ->selectRaw('employee_id, SUM(total_hours) as total_hours')
            ->groupBy('employee_id')
            ->pluck('total_hours', 'employee_id')
            ->toArray();
        $employee = Employee::where('del_time',0)
            ->whereIn('id',$employee_id)
            ->where('id','<>',Employee::SPECIAL_ADMIN)
            ->select('id','emp_name as name')
            ->get()->toArray();

        $employee_map = $employee_map_2 = $item_id = [];
        foreach ($data as $value){
            if(isset($employee_map[$value['data_id']])){
                $total_hours = bcadd($employee_map[$value['data_id']], $value['total_hours'],2);
                $employee_map[$value['data_id']] = $total_hours;
            }else{
                $employee_map[$value['data_id']] = $value['total_hours'];
            }
            $key = $value['data_id'];
            if(isset($employee_map_2[$key][$value['item_id']])){
                $total_hours_2 = bcadd($employee_map_2[$key][$value['item_id']], $value['total_hours'],2);
                $employee_map_2[$key][$value['item_id']] = $total_hours_2;
            }else{
                $employee_map_2[$key][$value['item_id']] = $value['total_hours'];
            }

            if(! in_array($value['item_id'], $item_id)) $item_id[] = $value['item_id'];
        }
        unset($data);

        $item_total = Item::where('del_time',0)->select('code','id')->get()->toArray();
        $item_map = array_column($item_total,'code','id');

        $tmp = [];
        foreach ($item_total as $value){
            $tmp[$value['id']] = 0;
        }
        foreach ($employee as $key => $value){
            //设置的人员在时间段内的总工时
            $set_total_hours = $employee_hours_map[$value['id']] ?? 0;
            $hours = bcmul($day,8);
            $employee[$key]['set_total_hours'] = $hours;
            //汇总研发工时单里的总工时
            $rd_total_hours = $employee_map[$value['id']] ?? 0;
            $rd_total_hours = bcdiv($rd_total_hours,60,2);
            $employee[$key]['rd_total_hours'] = $rd_total_hours;
            //每个项目的工时
            $every_item_hours = $employee_map_2[$value['id']] ?? [];
            $details = $tmp;
            $my_item = "";
            if(! empty($every_item_hours)){
                foreach ($every_item_hours as $item => $item_hour){
                    if(isset($details[$item])) {
                        $details[$item] = bcdiv($item_hour,60,2);
                    }
                    $code = $item_map[$item] ?? "";
                    if(! empty($code)) $my_item .= $code . ",";
                }
                $my_item = rtrim($my_item,',');
            }
            foreach ($details as $it => $d_v){
                $employee[$key]['extra_' . $it] = $d_v;
            }
            //自己研发的项目
            $employee[$key]['my_item'] = $my_item;
            $employee[$key]['position'] = "研发人员";
            $employee[$key]['type_title'] = "专职";
            $rate_one = "100";
            $employee[$key]['rate_one'] = $rate_one . "%";
            $rate_two = floatval($hours) > 0.0 ? bcdiv($rd_total_hours, $hours,2) : 0;
            $rate_two = bcmul($rate_two, 100,2);
            $employee[$key]['rate_two'] = $rate_two . "%";
            $employee[$key]['rate_three'] = $rate_two . "%";
        }

        return $employee;
    }

    public function statisticsDeviceCommon($data,$user, $field = []){
        if(empty($data['order_time'][0]) || empty($data['order_time'][1])) return [false, '时间不能为空'];

        $return = $this->changeDateToTimeStampAboutRange($data['order_time'],false);
        $start_time = $return[0];
        $end_time = $return[1];
        if ($start_time === null || $end_time === null || $start_time > $end_time) return [false, "时间的区间无效"];

        $day = $this->returnDays([
            0 => $start_time,
            1 => $end_time,
        ]);
        if($day > 365) return [false, '查询时间仅支持范围区间在365天内'];

        $model = RdDetails::from('rd_details as a')
            ->leftJoin('rd as b','b.id','a.rd_id')
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->where('b.order_time', '>=', $start_time)
            ->where('b.order_time', '<=', $end_time)
            ->where('a.type', RdDetails::type_two)
            ->select('b.item_id','b.total_hours','a.data_id')
            ->orderby('b.order_time', 'desc');

        if(! empty($data['item_code'])) {
            $item_id = Item::where('code', 'LIKE', '%'.$data['item_code'].'%')
                ->where('del_time',0)
                ->pluck('id')
                ->toArray();
            $model->whereIn('b.item_id', $item_id);
        }

        if(! empty($data['id'])){
            $model->whereIn('a.data_id', $data['id']);
        }

        if(! empty($data['device_title'])) {
            $data_id = Device::where('title', 'LIKE', '%'.$data['device_title'].'%')
                ->where('del_time',0)
                ->pluck('id')
                ->toArray();
            $model->whereIn('a.data_id', $data_id);
        }

        $time = $this->getMonthsFirstDayTimestamps([
            0 => $start_time,
            1 => $end_time,
        ]);
        return [true, [$model, $time]];
    }

    public function statisticsDevice($data,$user){
        list($status, $return) = $this->statisticsDeviceCommon($data, $user);
        if(! $status) return [false, $return];
        list($model, $ergs) = $return;
        $list = $model->get()->toArray();
        $list = $this->statisticsDeviceFillData($list,$ergs);

//        $list['count'] = $this->countTotal($list['data'], $user['header_default']);

        return [true, $list];
    }

    public function statisticsDeviceFillData($data, $ergs){
        $device_hours_map = DeviceDetails::where('del_time', 0)
            ->whereIn('time', $ergs)
            ->selectRaw('device_id, SUM(total_hours) as total_hours')
            ->groupBy('device_id')
            ->pluck('total_hours', 'device_id')
            ->toArray();
        $device = Device::where('del_time',0)
            ->select('id','title as name','code','type','type_2','power','in_time','number')
            ->get()->toArray();

        $device_map = $device_map_2 = $item_id = [];
        foreach ($data as $value){
            if(isset($device_map[$value['data_id']])){
                $total_hours = bcadd($device_map[$value['data_id']], $value['total_hours'],2);
                $device_map[$value['data_id']] = $total_hours;
            }else{
                $device_map[$value['data_id']] = $value['total_hours'];
            }
            $key = $value['data_id'];
            if(isset($device_map_2[$key][$value['item_id']])){
                $total_hours_2 = bcadd($device_map_2[$key][$value['item_id']], $value['total_hours'],2);
                $device_map_2[$key][$value['item_id']] = $total_hours_2;
            }else{
                $device_map_2[$key][$value['item_id']] = $value['total_hours'];
            }

            if(! in_array($value['item_id'], $item_id)) $item_id[] = $value['item_id'];
        }
        unset($data);

        $item_total = Item::where('del_time',0)->select('code','id')->get()->toArray();
        $item_map = array_column($item_total,'code','id');

        $tmp = [];
        foreach ($item_total as $value){
            $tmp[$value['id']] = 0;
        }
        foreach ($device as $key => $value){
            //设置的设备在时间段内的总工时
            $set_total_hours = $device_hours_map[$value['id']] ?? 0;
            $device[$key]['set_total_hours'] = $set_total_hours;
            //汇总研发工时单里的总工时
            $rd_total_hours = $device_map[$value['id']] ?? 0;
            $rd_total_hours = bcdiv($rd_total_hours,60,2);
            $device[$key]['rd_total_hours'] = $rd_total_hours;
//            $device[$key]['set_total_hours'] = $rd_total_hours;
            //每个项目的工时
            $every_item_hours = $device_map_2[$value['id']] ?? [];

            $details = $tmp;
            $my_item = "";
            if(! empty($every_item_hours)){
                foreach ($every_item_hours as $item => $item_hour){
                    if(isset($details[$item])) {
                        $details[$item] = bcdiv($item_hour,60,2);
                    }
                    $code = $item_map[$item] ?? "";
                    if(! empty($code)) $my_item .= $code . ",";
                }
                $my_item = rtrim($my_item,',');
            }
            foreach ($details as $it => $d_v){
                $device[$key]['extra_' . $it] = $d_v;
            }

            $device[$key]['my_item'] = $my_item;

            $device[$key]['type_title'] = Device::$type[$value['type']] ?? "";
            $device[$key]['type_2_title'] = Device::$type_2[$value['type_2']] ?? "";

            //应有占比
            if($value['type_2'] == Device::type_one){
                $rate_one = "100";
            } else{
                $rate_one = "10";
            }
            $device[$key]['rate_one'] = $rate_one . "%";
            $rate_two = floatval($rd_total_hours) > 0.0 ? bcdiv($rd_total_hours, $set_total_hours,2) : 0;
            $rate_two = bcmul($rate_two, 100,2);
            $device[$key]['rate_two'] = $rate_two . "%";
            $rate_three = bcmul(bcdiv($rate_two, $rate_one,2),100,2);
            $device[$key]['rate_three'] = $rate_three . "%";
        }

        return $device;
    }

    function getMonthsFirstDayTimestamps(array $ergs)
    {
        if (empty($ergs) || count($ergs) < 2) return [];

        $start = (int)$ergs[0];
        $end   = (int)$ergs[1];

        $timestamps = [];

        // 从开始时间创建 DateTime 对象
        $current = (new \DateTime())->setTimestamp($start)->modify('first day of this month')->setTime(0, 0, 0);

        // 结束时间
        $endDate = (new \DateTime())->setTimestamp($end)->modify('first day of this month')->setTime(0, 0, 0);

        while ($current <= $endDate) {
            $timestamps[] = $current->getTimestamp();
            $current->modify('+1 month'); // 下个月第一天
        }

        return $timestamps;
    }

    public function statisticsEmployeeExportCommon($data, $user){
        if(empty($data['month'])) return [false, '请选择导出的月份'];
        $month = $data['month'];

        $start_time = Carbon::createFromFormat('Y-m', $month)->startOfMonth()->timestamp;
        $end_time   = Carbon::createFromFormat('Y-m', $month)->endOfMonth()->timestamp;

        $model = RdDetails::from('rd_details as a')
            ->leftJoin('rd as b','b.id','a.rd_id')
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->where('b.order_time', '>=', $start_time)
            ->where('b.order_time', '<=', $end_time)
            ->where('a.type', RdDetails::type_one)
            ->select('b.item_id','b.total_hours','a.data_id','b.order_time')
            ->orderby('b.order_time', 'asc');

        $time = $this->getMonthsFirstDayTimestamps([
            0 => $start_time,
            1 => $end_time,
        ]);

        $day = CalendarDetails::where('del_time',0)
            ->where('time', '>=', $start_time)
            ->where('time', '<=', $end_time)
            ->where('is_work', 1)
            ->count();

        return [true, [$model, $time, $day]];
    }

    public function statisticsEmployeeFillExportData($data, $ergs, $day,$employee,$item_total){
        // 3. 统计传入数据中的工时（可能是月度，也可能是某一天）
        $employee_map = [];
        $employee_map_2 = [];
        foreach ($data as $value) {
            $eid = $value['data_id'];
            $iid = $value['item_id'];
            $hours = $value['total_hours'];

            $employee_map[$eid] = bcadd($employee_map[$eid] ?? 0, $hours, 2);
            $employee_map_2[$eid][$iid] = bcadd($employee_map_2[$eid][$iid] ?? 0, $hours, 2);
        }

        $item_map = array_column($item_total, 'code', 'id');
        $empty_item_structure = array_fill_keys(array_keys($item_map), 0);

        // 4. 组装最终行数据
        foreach ($employee as $key => $value) {
            $curr_id = $value['id'];

            // 计算额定工时
            $hours = bcmul($day, 8);
            $employee[$key]['set_total_hours'] = $hours;

            // 计算研发工时
            $rd_total_minutes = $employee_map[$curr_id] ?? 0;
            $rd_total_hours = bcdiv($rd_total_minutes, 60, 2);
            $employee[$key]['rd_total_hours'] = $rd_total_hours;

            // 项目明细列
            $every_item_hours = $employee_map_2[$curr_id] ?? [];
            $details = $empty_item_structure;
            $my_items = [];

            foreach ($every_item_hours as $item_id => $item_minutes) {
                $details[$item_id] = bcdiv($item_minutes, 60, 2);
                if (isset($item_map[$item_id])) {
                    $my_items[] = $item_map[$item_id];
                }
            }

            foreach ($details as $it => $d_v) {
                $employee[$key]['extra_' . $it] = $d_v;
            }

            $employee[$key]['my_item'] = implode(',', $my_items);
            $employee[$key]['position'] = "研发人员";
            $employee[$key]['type_title'] = "专职";

            // 计算比率
            $rate_val = (floatval($hours) > 0) ? bcdiv($rd_total_hours, $hours, 2) : 0;
            $rate_percent = bcmul($rate_val, 100, 2) . "%";

            $employee[$key]['rate_one'] = "100%";
            $employee[$key]['rate_two'] = $rate_percent;
            $employee[$key]['rate_three'] = $rate_percent;
        }

        return $employee;
    }

    public function statisticsDeviceExportCommon($data,$user){
        if(empty($data['month'])) return [false, '请选择导出的月份'];
        $month = $data['month'];

        $start_time = Carbon::createFromFormat('Y-m', $month)->startOfMonth()->timestamp;
        $end_time   = Carbon::createFromFormat('Y-m', $month)->endOfMonth()->timestamp;

        $model = RdDetails::from('rd_details as a')
            ->leftJoin('rd as b','b.id','a.rd_id')
            ->where('a.del_time',0)
            ->where('b.del_time',0)
            ->where('b.order_time', '>=', $start_time)
            ->where('b.order_time', '<=', $end_time)
            ->where('a.type', RdDetails::type_two)
            ->select('b.item_id','b.total_hours','a.data_id','b.order_time')
            ->orderby('b.order_time', 'asc');

        $time = $this->getMonthsFirstDayTimestamps([
            0 => $start_time,
            1 => $end_time,
        ]);

        return [true, [$model, $time]];
    }

    public function statisticsDeviceFillExportData($data, $ergs, $device, $item_total, $is_count = false, $set_map = []) {
        $device_map = $device_map_2 = [];
        foreach ($data as $value) {
            $device_map[$value['data_id']] = bcadd($device_map[$value['data_id']] ?? 0, $value['total_hours'], 2);
            $device_map_2[$value['data_id']][$value['item_id']] = bcadd($device_map_2[$value['data_id']][$value['item_id']] ?? 0, $value['total_hours'], 2);
        }
        unset($data);

        $tmp = [];
        foreach ($item_total as $value) {
            $tmp[$value['id']] = 0;
        }

        foreach ($device as $key => $value) {
            $device[$key]['in_time'] = $value['in_time'] ? date("Y-m-d", $value['in_time']) : '';

            $rd_total_hours = bcdiv($device_map[$value['id']] ?? 0, 60, 2);
            $device[$key]['rd_total_hours'] = $rd_total_hours;
            $set_total_hours = $set_map[$value['id']] ?? 0;
            $device[$key]['set_total_hours'] = $set_total_hours;

            $every_item_hours = $device_map_2[$value['id']] ?? [];
            $details = $tmp;
            if (!empty($every_item_hours)) {
                foreach ($every_item_hours as $item => $item_hour) {
                    if (isset($details[$item])) {
                        $details[$item] = bcdiv($item_hour, 60, 2);
                    }
                }
            }

            foreach ($details as $it => $d_v) {
                $device[$key]['extra_' . $it] = $d_v;
            }

            // 基础信息补充
            $device[$key]['my_item'] = "";
            $device[$key]['type_title'] = Device::$type[$value['type']] ?? "";
            $device[$key]['type_2_title'] = Device::$type_2[$value['type_2']] ?? "";

            // 百分比计算
            //应有占比
            if($value['type_2'] == Device::type_one){
                $rate_one = "100";
            } else{
                $rate_one = "10";
            }
            $device[$key]['rate_one'] = $rate_one . "%";
            $rate_two = floatval($rd_total_hours) > 0.0 ? bcdiv($rd_total_hours, $set_total_hours,2) : 0;
            $rate_two = bcmul($rate_two, 100,2);
            $device[$key]['rate_two'] = $rate_two . "%";
            $rate_three = bcmul(bcdiv($rate_two, $rate_one,2),100,2);
            $device[$key]['rate_three'] = $rate_three . "%";
        }

        // --- 合计逻辑 ---
        if ($is_count && !empty($device)) {
            //取第一行作为模板，所有值初始化为空字符串
            $summaryRow = array_map(fn() => '', $device[array_key_first($device)]);

            foreach ($device as $row) {
                // 动态累加所有 extra_ 开头的列
                foreach ($row as $k => $v) {
                    if (strpos($k, 'extra_') === 0) {
                        $summaryRow[$k] = bcadd($summaryRow[$k] ?: 0, $v, 2);
                    }
                }
            }

            $device[] = $summaryRow;
        }

        return $device;
    }

    public function rdLockList($data,$user){
        $model = $this->rdLockListCommon($data, $user);
        $list = $this->limit($model,'',$data);
        $list = $this->rdLockListFillData($list,$data);

        return [true, $list];
    }

    public function rdLockListCommon($data,$user){
        if(empty($field)) $field = RD::$field_2;

        $model = RD::from('rd as a')
            ->leftJoin('rd_lock as b', function ($join) {
                $join->on('a.type', '=', 'b.type')
                    ->on('a.belong_month', '=', 'b.belong_month')
                    ->where('b.del_time', '=', 0);
            })
            ->where('a.del_time', 0)
            // 分开写，就不会混淆参数了
            ->select($field)
            ->groupBy('a.belong_month', 'a.type')
            ->orderByRaw("a.belong_month DESC");

        if(! empty($data['type'])) $model->where('a.type', $data['type']);
        if(! empty($data['belong_month'])) {
            $time = strtotime($data['belong_month'] . '-01');
            $model->where('a.belong_month', $time);
        }
        if (isset($data['is_fc'])) {
            if ($data['is_fc'] == 1) {
                // 匹配成功：b.id 必须存在
                $model->whereNotNull('b.id');
            } else {
                // 匹配失败：b.id 必须为空
                $model->whereNull('b.id');
            }
        }

        return $model;
    }

    public function rdLockListFillData($data, $ergs){
        if(empty($data['data'])) return $data;

        foreach ($data['data'] as $key => $value){
            $data['data'][$key]['type_title'] = RdLock::$type_name[$value['type']] ?? "";
            $data['data'][$key]['belong_month_show'] = $value['belong_month'] ? date('Y-m', $value['belong_month']) : '';
            $data['data'][$key]['id'] = $value['id'] ?? 0;
            $data['data'][$key]['id_show'] = $value['id'] ? '已封存' : '未封存';
        }

        return $data;
    }

    public function rdLock($data,$user){
        if(empty($data['lock_array'])) return [false, '请选择数据'];
        if(empty($data['lock_type'])) return [false, 'lock_type不能为空'];

        $insert = [];$time = time();
        foreach ($data['lock_array'] as $value){
            if(empty($value['belong_month'])) return [false, 'belong_month不能为空'];
            if(empty($value['type'])) return [false, 'type不能为空'];
            $insert[] = [
                'belong_month' => $value['belong_month'],
                'type' => $value['type'],
                'crt_time' => $time
            ];
        }

        $count = $this->checkRdExists($data);

        try {
            if($data['lock_type'] == 1){//操作封存
                if($count != 0) return [false, '含有已封存数据，开启封存失败'];
                RdLock::insert($insert);
            }elseif ($data['lock_type'] == 2){//解除封存
                if($count != count($data['lock_array'])) return [false, '含有未封存数据，解除封存失败'];
                $this->delRdExists($data, $time);
            }else{
                return [false, '操作类型错误'];
            }
        }catch (\Exception $exception){
            return [false, $exception->getMessage()];
        }

        return [true, ''];
    }

    public function checkRdExists($data) {
        $lockArray = $data['lock_array'];

        $query = RdLock::where('del_time', 0);

        // 1. 构造批量查询条件
        $query->where(function($q) use ($lockArray) {
            foreach ($lockArray as $item) {
                $q->orWhere(function($sub) use ($item) {
                    $sub->where('belong_month', $item['belong_month'])
                        ->where('type', $item['type']);
                });
            }
        });

        // 2. 获取数据库中实际存在的【唯一组合】数量
        return $query->selectRaw('belong_month, type')
            ->groupBy('belong_month', 'type')
            ->get()
            ->count();
    }

    public function delRdExists($data, $now) {
        $lockArray = $data['lock_array'];

        $query = RdLock::where('del_time', 0);

        // 1. 构造批量查询条件
        $query->where(function($q) use ($lockArray) {
            foreach ($lockArray as $item) {
                $q->orWhere(function($sub) use ($item) {
                    $sub->where('belong_month', $item['belong_month'])
                        ->where('type', $item['type']);
                });
            }
        });

        $query->update(['del_time' => $now]);
    }
}