crm/app/jobs/CrmImport.php

<?php
namespace app\jobs;

use app\model\CrmImportLog;
use app\model\CrmImportRecord;
use app\model\Customer;
use app\model\CustomerPortraitField;
use cutExcel\MyreadFilter;
use OSS\Core\OssException;
use PHPExcel_Shared_Date;
use PhpOffice\PhpSpreadsheet\IOFactory;
use think\facade\Db;
use think\facade\Log;
use think\queue\Job;
use think\facade\Queue;
use toolkits\Aec;

class CrmImport
{
    public function fire(Job $job, $queuedata)
    {     
        $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 = CrmImportLog::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'];
        $log = CrmImportLog::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('-------crm import error------')->save();
            Log::record('file canRead false')->save();
            Log::record('-------crm 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;
                        }
                        if ($v !== "I") {
                            $date_value[$v] = gmdate('Y-m-d H:i:s', PHPExcel_Shared_Date::ExcelToPHP($get_value));
                        } else {
                            $date_value[$v] = gmdate('Y-m-d', PHPExcel_Shared_Date::ExcelToPHP($get_value));
                        }
                    } catch (\Exception $e) {
                        $date_value[$v] = '';
                    }
                }

                $datas[] = [
                    'name' => trim($objPHPExcel->getActiveSheet()->getCell("A" . $j)->getValue()),
                    'sex' => trim($objPHPExcel->getActiveSheet()->getCell("B" . $j)->getValue()),
                    'phone' => trim($objPHPExcel->getActiveSheet()->getCell("C" . $j)->getValue()),
                    'level' => trim($objPHPExcel->getActiveSheet()->getCell("D" . $j)->getValue()),
                    'community_name' => trim($objPHPExcel->getActiveSheet()->getCell("E" . $j)->getValue()),
                    'square' => trim($objPHPExcel->getActiveSheet()->getCell("F" . $j)->getValue()),
                    'housetype_arrow' => trim($objPHPExcel->getActiveSheet()->getCell("G" . $j)->getValue()),
                    'follow' => trim($objPHPExcel->getActiveSheet()->getCell("H" . $j)->getValue()),
                    'house_delivery_time' => !empty($date_value["I"]) ? $date_value["I"] : date('Y-m-d'),
                    'age_range' => trim($objPHPExcel->getActiveSheet()->getCell("J" . $j)->getValue()),
                    'unit_number' => trim($objPHPExcel->getActiveSheet()->getCell("K" . $j)->getValue()),
                    'house_location' => trim($objPHPExcel->getActiveSheet()->getCell("L" . $j)->getValue()),                    
                    'row'=> $j
                ];
            }

            //error 手机号错误数量   repeat 手机号重复数量
            $rs = ['ok' => 0, 'error' => 0, 'repeat' => 0];
            $error = [];
            $portrait_field = CustomerPortraitField::with('select')->where([['root_id', '=', $root_id], ['keyname', 'in', ['housetype_arrow', 'follow', 'age_range', 'unit_number', 'house_location']]])->select()->toArray();
            $save_data = [];

            $have_phone = []; //已经导入过的手机号
            $have_phone_search = Customer::where([['crm_res_id', '=', $log_id]])->column('phone,phone1,phone2');
            if (!empty($have_phone_search)) {
                foreach ($have_phone_search as $k => $v) {
                    if (!empty($v['phone'])) {
                        $have_phone[] = $v['phone'];
                    }
                    if (!empty($v['phone1'])) {
                        $have_phone[] = $v['phone1'];
                    }
                    if (!empty($v['phone2'])) {
                        $have_phone[] = $v['phone2'];
                    }
                }
            }

            $aec = new Aec(config('app.aec_key'), config('app.aec_iv'));
            foreach ($datas as $k => $v) {
                $one_cus = [];
                $one_cus['phone1'] = $one_cus['phone2'] = '';
                ////2023-02-15 修改手机号可以输入多个
                $s = true;
                $ls_phone = explode('、', $v['phone']);
                if(!empty($ls_phone) && is_array($ls_phone)){
                    $p = 0;
                    foreach ($ls_phone as $k2 => $v2) {
                        if ($this->checkphone($v2)) {
                            $field = $p ? 'phone'.$p : 'phone';
                            $one_cus[$field] = $aec->encrypt($v2);
                            $p+=1;
                        }
                        if($p>2) break;//只取三个有效手机号
                    }
                    if($p>0) $s = false;
                }
                
                if($s){
                    $rs['error']++;
                    $one_error['rows'] = $v['row'];
                    $one_error['type'] = 1; // 1、手机号格式错误 2、手机号重复
                    $one_error['phone'] = $v['phone'];
                    $error[] = $one_error;

                    continue;
                }
                // 手机号验重
                $one_phone = array_values(array_filter([$one_cus['phone'], $one_cus['phone1'], $one_cus['phone2']]));

                if (!empty($have_phone)) {
                    $is_repeat = array_intersect($have_phone, $one_phone);
                    if (!empty($is_repeat)) {
                        $rs['error']++;
                        $one_error['rows'] = $v['row'];
                        $one_error['type'] = 2; // 1、手机号格式错误 2、手机号重复
                        $one_error['phone'] = $v['phone'];
                        $error[] = $one_error;

                        continue;
                    }
                    $have_phone = array_merge($have_phone, $one_phone);
                } else {
                    $have_phone = $one_phone;
                }

                // 数据处理
                $one_cus['name'] = $v['name'];
                $one_cus['sex'] = ($v['sex'] == '男') ? 1 : 2;
                $one_cus['level'] = in_array($v['level'], ['A', 'B', 'C', 'D']) ? $v['level'] : '';
                $one_cus['square'] = $v['square'];
                $one_cus['community_name'] = $v['community_name'];
                $one_cus['state'] = 0;
                $one_cus['org_id'] = $log['org_id'];
                $one_cus['bad_phone'] = 0;
                $one_cus['source_id'] = $log['source'];
                $one_cus['house_type'] = mb_strlen($v['housetype_arrow']) > 20 ? mb_substr($v['housetype_arrow'], 0, 20) : trim($v['housetype_arrow']);
                $one_cus['is_resource'] = 1;
                $one_cus['crm_res_id'] = $log_id;

                $ext = [];
                // 扩展字段 户型、装修关注点、是否加微
                foreach ($portrait_field as $kk => $vv) {
                    switch ($vv['keyname']) {
                        case 'housetype_arrow':
                            $house_type_val = 0;
                            if (!empty($vv['select'])) {
                                foreach ($vv['select'] as $vvv) {
                                    if ($vvv['name'] == $v['housetype_arrow']) {
                                        $house_type_val = $vvv['id'];
                                    }
                                }
                            }
                            if ($house_type_val) {
                                $ext[] = ['id'=> $vv['id'], 'keyname'=> 'housetype_arrow', 'value'=> (string)$house_type_val];
                            }
                            break;
                        case 'age_range':
                            if (!empty($vv['select'])) {
                                foreach ($vv['select'] as $vvv) {
                                    if ($vvv['name'] == $v['age_range']) {
                                        $ext[] = ['id'=> $vv['id'], 'keyname'=> 'age_range', 'value'=> $vvv['id']];
                                        $one_cus['age_range'] = $vvv['id'];
                                    }
                                }
                            }
                            break;
                        case 'follow':
                            $follow_list = explode('、', $v['follow']);
                            $follow_ids = [];
                            if (!empty($vv['select'])) {
                                foreach ($vv['select'] as $vvv) {
                                    if (in_array($vvv['name'], $follow_list)) {
                                        $follow_ids[] = $vvv['id'];
                                    }
                                }
                            }
                            if (!empty($follow_ids)) {
                                $ext[] = ['id'=> $vv['id'], 'keyname'=> 'follow', 'value'=> implode(',', $follow_ids)];
                            }
                            break;
                        case 'unit_number':
                            if (!empty($v['unit_number'])) {
                                $ext[] = ['id'=> $vv['id'], 'keyname'=> 'unit_number', 'value'=> (string)$v['unit_number']];
                            }
                            break;
                        case 'house_location':
                            if (!empty($v['house_location'])) {
                                $ext[] = ['id'=> $vv['id'], 'keyname'=> 'house_location', 'value'=> (string)$v['house_location']];
                            }
                            break;
                        default:
                        break;
                    }
                }
    
                $one_cus['ext'] = $ext ? json_encode($ext) : NULL;
                $save_data[] = $one_cus;
                $rs['ok']++;
            }

            Db::startTrans();
            try {
                foreach ($save_data as $k => $v) {
                    Customer::insert($v);
                }
                Db::commit();
            } catch (\Exception $e) {
                trace($e->getMessage(), 'error');
                // 回滚事务
                Db::rollback();
                return false;
            }

            // 执行记录
            $excute_log['log_id'] = $log_id;
            $excute_log['employee_id'] = $log['employee_id'];
            $excute_log['start_rows'] = $start_rows;
            $excute_log['end_rows'] = $end_rows;
            $excute_log['error_count'] = count($error);
            $excute_log['content'] = $error ? json_encode($error) : '';
            $excute_log['root_id'] = $root_id;

            CrmImportRecord::create($excute_log);
            
            if ($log['status'] == 0) {
                $log->status = 1;
            }
            $log->left_num = $log['left_num'] + $rs['ok'];
            $log->count = $log['count'] + count($datas);
            $log->avaliable_count = $log['avaliable_count'] + $rs['ok'];
            $log->bad_phone_num = $log['bad_phone_num'] + $rs['error'];
            $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\CrmImport';
            $jobQueueName = 'crm_import';
            $orderData = ['id'=> $log_id];  //这个是需要传到消费者的数据
            Queue::later(0, $jobHandlerClassName, $orderData, $jobQueueName);
        }

        return true;
    }

    private function checkphone($value)
    {
        if (preg_match("/^1[356789]\d{9}$/", $value)) {
            return true;
        } else {
            return false;
        }
    }
}