crm/app/jobs/SmartScreenImport.php

<?php
namespace app\jobs;

use app\model\ConstructionStep;
use app\model\Employee;
use app\model\SmartScreenConstruction;
use app\model\SmartScreenImportLog;
use app\model\SmartScreenProject;
use cutExcel\MyreadFilter;
use OSS\Core\OssException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;
use think\facade\Log;
use think\queue\Job;
use think\facade\Queue;

class SmartScreenImport
{
    public function fire(Job $job, $queuedata)
    {     
        Log::close(NULL);
        $delete = false; // 失败
        try {
            if ($job->attempts() > 2) {
                $job->delete();
                $delete = true;
            }
            
            $is_done = $this->jobDone($queuedata);
            if($is_done){
                $job->delete();
            } else {
                $job->delete();

                $delete = true;
            }
        } catch (OssException $e) {
            //报错直接结束
            $job->delete();
            $delete = true;
        }

        if ($delete) {
            // 导入过程失败
            $log = SmartScreenImportLog::where('id', $queuedata['id'])->find();
            $tmp_file = $log['tmp_path'];
            if (file_exists($tmp_file)) {
                unlink($tmp_file);
            }
            $log->tmp_path = '';
            $log->status = 3;
            $log->save();
        }
    }

    public function failed($queuedata)
    {
        // ...任务达到最大重试次数后，失败了
    }

    // 导入
    private function jobDone($data){
        $log_id = $data['id'];
        $type = $data['type'];
        $log = SmartScreenImportLog::where('id', $log_id)->findOrEmpty();
        if ($log->isEmpty()) {
            return false;
        }
        if ($log['status'] == 2) {
            return true;
        }

        if (empty($log['tmp_path'])) {
            // 开始导入
            $ali_oss_bindurl = config('app.ali_oss_bindurl');
            $path = 'https://' . $ali_oss_bindurl . '/' . $log['path'];
    
            // 抓取远程Excel文件的内容
            try {
                $content = file_get_contents($path);
                if (!$content) {
                    return false;
                }
            } catch (\Exception $e) {
                return false;
            }

            // 保存Excel文件到本地临时文件
            $run_path = runtime_path();
            $tmp_file = tempnam($run_path, 'PHPExcel');
            file_put_contents($tmp_file, $content);

            $log->tmp_path = $tmp_file;
            $log->save();
        } else {
            $tmp_file = $log['tmp_path'];
        }

        //实例化PHPExcel类
        $reader = IOFactory::createReader('Xlsx');
        if (!$reader->canRead($tmp_file)) {
            Log::record('-------screen import error------')->save();
            Log::record('file canRead false')->save();
            Log::record('-------screen import error------')->save();
            return false;
        }

        $rows = 100;
        if ($log['execute_rows'] == 0) {
            $start_rows = 1;
        } else {
            $start_rows = $log['execute_rows'];
        }

        $end_rows = $start_rows + $rows;
        $MyReadFilter = new MyreadFilter($start_rows, $end_rows);
        $reader->setReadFilter($MyReadFilter);

        $reader->setReadDataOnly(true);

        $objPHPExcel = $reader->load($tmp_file);
        $sheet = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
        $highestRow = $sheet->getHighestRow();       // 取得总行数
        $highestColumn = $sheet->getHighestColumn();   // 取得总列数

        \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($highestColumn);
        $lines = $highestRow - 1;

        $end = true;
        $root_id = $log['root_id'];
        if ($lines > 0) {
            $datas = [];
            $j = $start_rows + 1;

            for ($j; $j <= $highestRow; $j++) {
                $date_check = ['I'];
                $date_value = [];
                foreach ($date_check as $v) {
                    try {
                        $get_value = $objPHPExcel->getActiveSheet()->getCell($v . $j)->getValue();
                        if (empty($get_value)) {
                            $date_value[$v] = '';
                            continue;
                        }
                    } catch (\Exception $e) {
                        $date_value[$v] = '';
                    }
                }
                if ($type == 1) {
                    $datas[] = [
                        'owner_name' => trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
                        'address' => trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
                        'designer' => trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()),
                        'project_manager' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
                        'inspection' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
                        'status' => trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()),
                        'row'=> $j
                    ];
                } elseif ($type == 2) {
                    $datas[] = [
                        'category' => trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
                        'name' => trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
                        'unit' => trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()),
                        'price' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
                        'content' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
                        'row'=> $j
                    ];
                }
            }

            $save_data = [];

            foreach ($datas as $k => $v) {
                $one_cus = [];
                if ($type == 1) {
                    $designer_id = 0;
                    if (!empty($v['designer'])) {
                        $designer_id = Employee::where([['name', '=', $v['designer']], ['root_id', '=', $root_id]])->value('id');
                    }
                    $status = 0;
                    if (!empty($v['status'])) {
                        $status = ConstructionStep::where([['root_id', '=', $root_id], ['name', '=', $v['status']]])->value('id');
                    }

                    $one_cus['owner_name'] = $v['owner_name'];
                    $one_cus['address'] = $v['address'];
                    $one_cus['designer_id'] = $designer_id ? $designer_id : 0;
                    $one_cus['project_manager'] = $v['project_manager'];
                    $one_cus['inspection'] = $v['inspection'];
                    $one_cus['status'] = $status;
                    $one_cus['employee_id'] = $log['employee_id'];
                    $one_cus['root_id'] = $root_id;
                } elseif ($type == 2) {
                    $one_cus['category'] = $v['category'];
                    $one_cus['name'] = $v['name'];
                    $one_cus['unit'] = $v['unit'];
                    $one_cus['price'] = $v['price'];
                    $one_cus['content'] = $v['content'];
                    $one_cus['employee_id'] = $log['employee_id'];
                    $one_cus['root_id'] = $root_id;
                }
                $save_data[] = $one_cus;
            }

            Db::startTrans();
            try {
                foreach ($save_data as $k => $v) {
                    if ($type == 1) {
                        $result = SmartScreenConstruction::create($v);
                    } elseif ($type == 2) {
                        SmartScreenProject::create($v);
                    }
                }
                Db::commit();
            } catch (\Exception $e) {
                trace($e->getMessage(), 'error');
                // 回滚事务
                Db::rollback();
                return false;
            }
            if ($log['status'] == 0) {
                $log->status = 1;
            }
            $log->count = $log['count'] + count($datas);
            $log->valid_count = $log['valid_count'] + count($datas);
            $log->execute_rows = $highestRow;
            $log->save();
            if (count($datas) > 0) {
                $end = false;
            } else {
                $log->tmp_path = '';
                $log->status = 2;
                $log->save();

                if (file_exists($tmp_file)) {
                    unlink($tmp_file);
                }
            }
        } else {
            $log->tmp_path = '';
            $log->status = 2;
            $log->save();
            if (file_exists($tmp_file)) {
                unlink($tmp_file);
            }
        }
        if($lines == $rows) {
            $end = false;
        }
        if (!$end) {
            $jobHandlerClassName = 'app\jobs\SmartScreenImport';
            $jobQueueName = 'smart_screen_import';
            $orderData = ['id'=> $log_id, 'type'=> $type];  //这个是需要传到消费者的数据
            Queue::later(0, $jobHandlerClassName, $orderData, $jobQueueName);
        }

        return true;
    }
}