hqyt/app/Service/StatisticsService.php

<?php

namespace App\Service;

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\RdDetails;
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('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,
        ]);
        return [true, [$model, $time]];
    }

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

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

    public function statisticsEmployeeFillData($data, $ergs){
        $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;
            $employee[$key]['set_total_hours'] = $set_total_hours;
            //汇总研发工时单里的总工时
            $rd_total_hours = $employee_map[$value['id']] ?? 0;
            $rd_total_hours = bcdiv($rd_total_hours,60,3);
            $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,3);
                    }
                    $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'] = "专职";
            $employee[$key]['rate_one'] = "100%";
            $employee[$key]['rate_two'] = "100%";
            $employee[$key]['rate_three'] = "100%";
        }

        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('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,3);
            $device[$key]['rd_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,3);
                    }
                    $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]['position'] = "研发人员";
            $device[$key]['type_title'] = Device::$type[$value['type']] ?? "";
            $device[$key]['type_2_title'] = Device::$type_2[$value['type_2']] ?? "";
            $device[$key]['rate_one'] = "100%";
            $device[$key]['rate_two'] = "100%";
            $device[$key]['rate_three'] = "100%";
        }

        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;
    }
}