report/app/job/Import.php

<?php

namespace app\job;

use app\common\api\BatchApi;
use app\common\api\TalentLogApi;
use app\common\model\TalentChecklog;
use app\common\state\ProjectState;
use think\queue\Job;
use think\facade\Log;
use think\facade\Db;
use app\common\api\ChuanglanSmsApi;
// 给类文件的命名空间起个别名
use PhpOffice\PhpSpreadsheet\Spreadsheet;
//Xlsx类  保存文件功能类
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class Import {

    public function fire(Job $job, $data) {
        if ($this->deal($data)) {
            Log::info(json_encode($data));
            $job->delete();
            return true;
        }
        Log::error(json_encode($data));
        if ($job->attempts() >= 3) {
            $job->delete();
            return false;
        }
        $job->release(10); //10秒后重试
    }

    /**
     * 处理业务逻辑
     * @param type $data
     * @return bool
     */
    public function deal($data): bool {
        if ($data['type'] == 1) {//企业导入
            $id = $data['id'];
            $res = Db::table("new_talent_zhiren")->where('id', $id)->findOrEmpty();
            if ($res) {
                if ($res['task_status'] != -1) {
                    return false;
                }
                try {
                    $path = dirname(dirname(dirname(__FILE__)));
                    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path . "/public/storage/" . $res['task_file']);
                    $sheet = $spreadsheet->getSheet(0);
                    $datas = $sheet->toArray();
                    $datas = array_slice($datas, 1); //去标题
                    $street = Db::table("sys_dict")->where("pid", 1655)->column("code", "name");

                    for ($index = 0; $index < count($datas); $index++) {
                        $row = $datas[$index];
                        $msg = [];
                        $item = [];
                        for ($i = 0; $i < 11; $i++) {
                            switch ($i) {
                                case 0:
                                    if (in_array($row[$i], ['企业用户', '民办非企业', '事业单位'])) {
                                        if ($row[$i] == '事业单位') {
                                            $item['special'] = 1;
                                        } else {
                                            $item['special'] = 0;
                                        }
                                    } else {
                                        array_push($msg, "导入的机构类型不正确");
                                    }
                                    break;
                                case 1:
                                    if (empty($row[$i])) {
                                        array_push($msg, "机构名称不能为空");
                                    } else {
                                        $info = Db::table("un_enterprise")->where('name', $row[$i])->findOrEmpty();
                                        if ($info) {
                                            array_push($msg, "机构名称已存在，请检查");
                                        } else {

                                            $isMatched = preg_match('/^[\x{4e00}-\x{9fa5}\(\)（）\da-zA-Z&]{2,50}$/u', $row[$i], $matches);
                                            if ($isMatched) {
                                                $item['name'] = $row[$i];
                                            } else {
                                                array_push($msg, "机构名称只能是中文或数字");
                                            }
                                        }
                                    }
                                    break;
                                case 2:
                                    $info = Db::table("un_enterprise")->where('idCard', $row[$i])->where('delete',0)->findOrEmpty();
                                    if ($info) {
                                        array_push($msg, "机构代码已存在，请检查");
                                    } else {
                                        $isMatched = preg_match('/^([0-9A-HJ-NPQRTUWXY]{2}\d{6}[0-9A-HJ-NPQRTUWXY]{10}|[1-9]\d{14})$/', $row[$i], $matches);
                                        if ($isMatched) {
                                            $item['idCard'] = $row[$i];
                                        } else {
                                            array_push($msg, "组织机构代码证不正确");
                                        }
                                    }
                                    break;
                                case 3:
                                    $item['agentName'] = $row[$i];
                                    break;
                                case 4:
                                    $isMatched = preg_match('/^1[3456789]\d{9}$/', $row[$i], $matches);
                                    if ($isMatched) {
                                        $item['agentPhone'] = $row[$i];
                                    } else {
                                        array_push($msg, "联系电话不正确");
                                    }
                                    break;
                                case 5:
                                    $item['street'] = array_key_exists($row[$i], $street) ? $street[$row[$i]] : '00000';
                                    break;
                                case 6:
                                    $item['address'] = $row[$i];
                                    break;
                                case 7:
                                    $item['ephone'] = $row[$i];
                                    break;
                                case 8:
                                    $item['bankCard'] = $row[$i];
                                    break;
                                case 9:
                                    $item['bank'] = $row[$i];
                                    break;
                                case 10:
                                    $item['bankNetwork'] = $row[$i];
                                    break;
                            }
                        }
                        if (count($msg) >= 1) {
                            $sheet->setCellValue('L' . ($index + 2), '导入失败，失败原因：' . implode(";", $msg));
                        } else {
                            $item['id'] = getStringId();
                            $item['type'] = 1;
                            $item['source'] = 4;
                            $item['username'] = $item['idCard'];
                            $password = generate_password(8);
                            $item['password'] = hash('md5', $password);
                            $item['active'] = 1;
                            $item['checkState'] = 3;
                            $item['createTime'] = date("Y-m-d H:i:s", time());
                            Db::table("un_enterprise")->insert($item);
                            $sheet->setCellValue('L' . ($index + 2), '导入成功，初始账号为：' . $item['idCard'] . '，初始密码为：' . $password);
                            $sender = new ChuanglanSmsApi();
                            $sender->sendSMS($item['agentPhone'], "您好，您的机构信息仍需完善，请登录晋江市人才综合服务申报平台进行完善，您的用户名为：{$item['idCard']}，密码为：{$password}。");
                            TalentChecklog::create([
                                'id' => getStringId(),
                                'mainId' => $item['id'],
                                'type' => 10,
                                'typeFileId' => null,
                                'active' => 1,
                                'state' => 1,
                                'step' => 100,
                                'stateChange' => '直认导入',
                                'description' => '机构注册',
                                'createTime' => date("Y-m-d H:i:s", time()),
                                'createUser' => '系统'
                            ]);
                        }
                    }

                    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
                    $writer->save($path . "/public/storage/" . $res['task_file']);
                    Db::table("new_talent_zhiren")->where('id', $id)->update(['task_result' => $res['task_file'], 'task_status' => 1, 'updateTime' => date("Y-m-d H:i:s", time())]);
                    return true;
                } catch (\Exception $e) {
                    var_dump($e->getMessage());
                    Db::table("new_talent_zhiren")->where('id', $data['id'])->update(['task_result' => $e->getMessage(), 'status' => 0, 'updateTime' => date("Y-m-d H:i:s", time())]);
                    return false;
                }
            } else {
                return false;
            }
        }
        elseif ($data['type'] == 2) {//省级人才导入
            $id = $data['id'];
            $res = Db::table("new_talent_zhiren")->where('id', $id)->findOrEmpty();
            if ($res) {
                if ($res['task_status'] != -1) {
                    return false;
                }
                try {
                    $path = dirname(dirname(dirname(__FILE__)));
                    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path ."/public/storage/" . $res['task_file']);
                    $sheet = $spreadsheet->getSheet(0);
                    $datas = $sheet->toArray();
                    $datas = array_slice($datas, 1); //去标题
                    $country = Db::table("sys_dict")->where("pid", 35)->column("code", "name");
                    $batch = BatchApi::getValidBatch(ProjectState::TALENT, 1);
                    for ($index = 0; $index < count($datas); $index++) {
                        $row = $datas[$index];
                        $msg = [];
                        $item = [];
                        $enterprise_name = '';
                        $enterprise_id = '';
                        $action = 'insert';
                        $str = "";
                        for ($i = 0; $i < 37; $i++) {
                            switch ($i) {
                                case 0:
                                    if (empty($row[$i])) {
                                        array_push($msg, "机构名称不能为空");
                                    } else {
                                        $enterprise_name = $row[$i];
                                    }
                                    break;
                                case 1:
                                    $info = Db::table("un_enterprise")->where('idCard', $row[$i])->where('delete',0)->findOrEmpty();
                                    if (!$info) {
                                        array_push($msg, "机构代码不存在，请检查");
                                    } else {
                                        $info_name = str_replace('(','（',$info['name']);
                                        $info_name = str_replace(')','）',$info_name);
                                        $enterprise_name = str_replace('(','（',$enterprise_name);
                                        $enterprise_name = str_replace(')','）',$enterprise_name);
                                        if (trim($info_name) != trim($enterprise_name)) {
                                            array_push($msg, "机构代码与企业名称无法对应，请检查");
                                        } else {
                                            $item['enterprise_id'] = $enterprise_id = $info['id'];
                                        }
                                    }
                                    break;
                                case 5:
                                    if (empty($row[$i])) {
                                        array_push($msg, "人才姓名不能为空");
                                    } else {
                                        $item['name'] = $row[$i];
                                    }
                                    break;
                                case 6:
                                    if (in_array($row[$i], ['身份证', '通行证', '护照'])) {
                                        $key = array_search($row[$i], ['身份证', '通行证', '护照']);
                                        $item['card_type'] = $key + 1;
                                    } else {
                                        array_push($msg, "人才的证件类型不正确");
                                    }

                                    break;
                                case 7:
                                    if (empty($row[$i])) {
                                        array_push($msg, "证件号码不能为空");
                                    } else {
                                        if (!isCreditNo($row[$i]) && $item['card_type'] == 1) {
                                            array_push($msg, "证件号码不正确");
                                        }
                                        $talent_info = Db::table("new_talent_info")->where("card_number", $row[$i])->where('apply_year',$batch["batch"])->findOrEmpty();
                                        if ($talent_info) {
                                            $action = 'update';
                                        }
                                        $item['card_number'] = $row[$i];
                                    }
                                    break;
                                case 8:
                                    if($row[$i] == '女'){
                                        $item['sex'] = 2;
                                    }else{
                                        $item['sex'] = 1;
                                    }
                                    break;
//                                case 9:
//                                    $item['nationality'] = array_key_exists($row[$i], $country) ? $country[$row[$i]] : 'other';
//                                    break;
                                case 15:
                                    if(!empty($row[$i])){
                                        $item['phone'] = $row[$i];
                                    }
                                    break;
                                case 16:
                                    if(!empty($row[$i])){
                                        $item['email'] = $row[$i];
                                    }
                                    break;
                                case 25:
                                    $item['position'] = $row[$i];
                                    break;
                                case 31:
                                    $item['fj_talent_level'] = $row[$i];
                                    break;
                                case 32:
                                    $item['fj_talent_condition_text'] = $row[$i];
                                    break;
                                case 33:
                                    $str = "认定时间：".$row[$i];
                                    break;
                                case 34:
                                    $str .= "；省级人才证书编号：" . $row[$i];
                                    break;
                                case 35:
                                    $str .= "；有效期：" . $row[$i];
                                    break;
                            }
                        }
                        //$item['source'] = 1;
                        $item['fj_talent_info'] = $str;
                        $item["checkState"] = \app\common\api\TalentState::SCND_SAVE; //保存未提交
                        $item["isImport"] = 1;//导入人才的标志
                        $item['fjImport'] = 1;
                        if (count($msg) >= 1) {
                            $sheet->setCellValue('AL' . ($index + 2), '导入失败，失败原因：' . implode(";", $msg));
                        } else {
                            if ($action == 'update') {
                                $item['updateTime'] = date("Y-m-d H:i:s", time());
                                Db::table("new_talent_info")->where('id', $talent_info['id'])->update($item);
                                $sheet->setCellValue('AL' . ($index + 2), '更新成功');

                            } else {
                                $item['createTime'] = date("Y-m-d H:i:s", time());
                                $item['apply_year'] = $batch["batch"];
                                Db::table("new_talent_info")->insert($item);
                                $sheet->setCellValue('AL' . ($index + 2), '导入成功');

                            }
                            $log = TalentLogApi::system(1, $talent_info["id"],8,"直认数据导入",1);
                        }
                    }


                    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
                    $writer->save($path . "/public/storage/" . $res['task_file']);
                    Db::table("new_talent_zhiren")->where('id', $id)->update(['task_result' => $res['task_file'], 'task_status' => 1, 'updateTime' => date("Y-m-d H:i:s", time())]);
                    return true;
                } catch (\Exception $e) {
                    var_dump($e->getMessage());
                    Db::table("new_talent_zhiren")->where('id', $id)->update(['task_result' => $e->getMessage(), 'task_status' => 0, 'updateTime' => date("Y-m-d H:i:s", time())]);
                    return false;
                }
            } else {
                return false;
            }
        }
        elseif ($data['type'] == 3){//泉州人才导入
            $id = $data['id'];
            $res = Db::table("new_talent_zhiren")->where('id', $id)->findOrEmpty();
            if ($res) {
                if ($res['task_status'] != -1) {
                    return false;
                }
                try {

                    $path = dirname(dirname(dirname(__FILE__)));
                    $spreadsheet = \PhpOffice\PhpSpreadsheet\IOFactory::load($path ."/public/storage/" . $res['task_file']);
                    $sheet = $spreadsheet->getSheet(0);
                    $datas = $sheet->toArray();
                    $datas = array_slice($datas, 1); //去标题
                    $country = Db::table("sys_dict")->where("pid", 35)->column("code", "name");
                    $batch = BatchApi::getValidBatch(ProjectState::TALENT, 1);
                    for ($index = 0; $index < count($datas); $index++) {
                        $row = $datas[$index];
                        $msg = [];
                        $item = [];
                        $enterprise_name = '';
                        $enterprise_id = '';
                        $action = 'insert';
                        $str = '';
                        for ($i = 0; $i < 28; $i++) {
                            switch ($i) {
                                case 0:
                                    if (empty($row[$i])) {
                                        array_push($msg, "机构名称不能为空");
                                    } else {
                                        $enterprise_name = $row[$i];
                                    }
                                    break;
                                case 1:
                                    $info = Db::table("un_enterprise")->where('idCard', $row[$i])->where('delete',0)->findOrEmpty();
                                    if (!$info) {
                                        array_push($msg, "机构代码不存在，请检查");
                                    } else {
                                        $info_name = str_replace('(','（',$info['name']);
                                        $info_name = str_replace(')','）',$info_name);
                                        $enterprise_name = str_replace('(','（',$enterprise_name);
                                        $enterprise_name = str_replace(')','）',$enterprise_name);
                                        if (trim($info_name) != trim($enterprise_name)) {
                                            array_push($msg, "机构代码与企业名称无法对应，请检查");
                                        } else {
                                            $item['enterprise_id'] = $enterprise_id = $info['id'];
                                        }
                                    }
                                    break;
                                case 2:
                                    if (empty($row[$i])) {
                                        array_push($msg, "人才姓名不能为空");
                                    } else {
                                        $item['name'] = $row[$i];
                                    }
                                    break;
                                case 3:
                                    if($row[$i] == '女'){
                                        $item['sex'] = 2;
                                    }else{
                                        $item['sex'] = 1;
                                    }
                                    break;
//                                case 4:
//                                    $item['nationality'] = array_key_exists($row[$i], $country) ? $country[$row[$i]] : 'other';
//                                    break;
                                case 5:
                                    if (in_array($row[$i], ['身份证', '通行证', '护照'])) {
                                        $key = array_search($row[$i], ['身份证', '通行证', '护照']);
                                        $item['card_type'] = $key + 1;
                                    } else {
                                        array_push($msg, "人才的证件类型不正确");
                                    }
                                    break;
                                case 6:
                                    if (empty($row[$i])) {
                                        array_push($msg, "证件号码不能为空");
                                    } else {

                                        if (!isCreditNo($row[$i]) && $item['card_type'] == 1) {
                                            array_push($msg, "证件号码不正确");
                                        }
                                        $talent_info = Db::table("new_talent_info")->where("card_number", $row[$i])->where('apply_year',$batch["batch"])->where('delete',0)->findOrEmpty();
                                        if ($talent_info) {
                                            $action = 'update';
                                        }
                                        $item['card_number'] = $row[$i];
                                    }
                                    break;
                                case 7:
                                    $item['birthday'] = $row[$i];
                                    break;
                                case 9:
                                    $item['position'] = $row[$i];
                                    break;
                                case 12:
                                    $item['professional'] = $row[$i];
                                    break;
                                case 13:
                                    $item['pro_qua'] = $row[$i];
                                    break;
                                case 16:
                                    if(!empty($row[$i])){
                                        $item['phone'] = $row[$i];
                                    }
                                    break;
                                case 17:
                                    if(!empty($row[$i])){
                                        $item['email'] = $row[$i];
                                    }
                                    break;
                                case 18:
                                    $item['qz_talent_level'] = $row[$i];
                                    break;
                                case 19:
                                    $item['qz_talent_condition_text'] = $row[$i];
                                    break;
                                case 25:
                                    $str = "人才证书号码：".$row[$i];
                                    break;
                                case 26:
                                    $str .= "；发证日期：".$row[$i];
                                    break;
                                case 27:
                                    $str .= "；人才证有效期：".$row[$i];
                                    break;
                            }
                        }
                        //$item['source'] = 2;
                        $item['qz_talent_info'] = $str;
                        $item["checkState"] = \app\common\api\TalentState::SCND_SAVE; //保存未提交
                        $item["isImport"] = 1;//导入人才的标志
                        $item['qzImport'] = 1;
                        if (count($msg) >= 1) {
                            $sheet->setCellValue('AC' . ($index + 2), '导入失败，失败原因：' . implode(";", $msg));
                        } else {
                            if ($action == 'update') {
                                $item['updateTime'] = date("Y-m-d H:i:s", time());
                                Db::table("new_talent_info")->where('id', $talent_info['id'])->update($item);
                                $sheet->setCellValue('AC' . ($index + 2), '更新成功');
                                $log = TalentLogApi::system(1, $talent_info["id"],8,"直认数据导入",1);
                            } else {
                                $item['apply_year'] = $batch["batch"];
                                $item['createTime'] = date("Y-m-d H:i:s", time());
                                $talent_info_id = Db::table("new_talent_info")->insertGetId($item);
                                $sheet->setCellValue('AC' . ($index + 2), '导入成功');
                                $log = TalentLogApi::system(1, $talent_info_id,8,"直认数据导入",1);
                            }

                        }
                    }

                    $writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
                    $writer->save($path . "/public/storage/" . $res['task_file']);
                    Db::table("new_talent_zhiren")->where('id', $id)->update(['task_result' => $res['task_file'], 'task_status' => 1, 'updateTime' => date("Y-m-d H:i:s", time())]);
                    return true;
                } catch (\Exception $e) {
                    var_dump($e->getMessage());
                    Db::table("new_talent_zhiren")->where('id', $id)->update(['task_result' => $e->getMessage(), 'task_status' => 0, 'updateTime' => date("Y-m-d H:i:s", time())]);
                    return false;
                }
            } else {
                return false;
            }
        }
    }

}