luoshanxianjicengzhili/app/Jobs/Manage/ImportMessageJob.php

<?php

/*
 * This file is part of the ZhMead/laravel-logger.
 *
 * (c) Mead <751066209@qql.com>
 *
 * This source file is subject to the MIT license that is bundled
 * with this source code in the file LICENSE.
 */

namespace App\Jobs\Manage;

use App\Repositories\Enums\Manage\DealStatusEnum;
use App\Repositories\Enums\Manage\ImportStatusEnum;
use App\Repositories\Enums\ModelStatusEnum;
use App\Repositories\Models\Base\Admin;
use App\Repositories\Models\Base\Department;
use App\Repositories\Models\Base\Dict;
use App\Repositories\Models\Base\Resource;
use App\Repositories\Models\Base\SourceMatters;
use App\Repositories\Models\Manage\Category;
use App\Repositories\Models\Manage\ImportRecord;
use App\Repositories\Models\Manage\Message;
use App\Repositories\Models\Manage\PreMessage;
use Carbon\Carbon;
use Fukuball\Jieba\Finalseg;
use Fukuball\Jieba\Jieba;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\Storage;
use Illuminate\Support\Str;

class ImportMessageJob implements ShouldQueue
{
    use InteractsWithQueue;
    use Queueable;

    private $importRecordId;
    private $admin;

    /**
     * Create a new job instance.
     */
    public function __construct($importRecordId, $admin)
    {
        $this->importRecordId = $importRecordId;
        $this->admin = $admin;
    }

    /**
     * 确定任务应该超时的时间
     *
     * @return \DateTime
     */
    public function retryUntil()
    {
        return Carbon::now()->addHours(8);
    }

    public $tries = 1;

    public $timeout = 1440;

    /**
     * Execute the job.
     */
    public function handle()
    {
        $importRecord = ImportRecord::query()->find($this->importRecordId);
//        $this->importTemplate3($importRecord);
//        dd(1);
        switch ($importRecord->type) {
            case 1:
                $this->importTemplate1($importRecord);
                break;
            case 2:
                $this->importTemplate2($importRecord);
                break;
            case 3:
                $this->importTemplate3($importRecord);
                break;
        }

    }

    /**
     * 导入数据
     * @return false|void
     */
    public function importTemplate1(&$importRecord)
    {
        ini_set('memory_limit', '1024M');
        $admin = $this->admin;
        $import_department_id = $admin['department_id'];

        $importRecord->status = ImportStatusEnum::IMPORT_IN;
        $importRecord->save();

        $path = Storage::disk('public')->path(Resource::byIdGetPath($importRecord->resource_id));
//        $path = base_path('./1.xlsx');
//        $path = base_path('./2.xlsx');
        if (!file_exists($path)) {
            $importRecord->status = ImportStatusEnum::IMPORT_ERROR;
            $importRecord->message = '找不到文件';
            $importRecord->save();
            Log::error('*************找不到文件***************');
            Log::error($path);
            return false;
        }

        $departments = Department::getNames();
        //获取事项来源列表--id---and---相似名称
        $sourceMatters = SourceMatters::getNames();
        $sourceMatter_luoshan = SourceMatters::query()->where('name', '罗山县')->where('status', ModelStatusEnum::OK)->value('id');

        try {
            $config = ['path' => ''];
            $excel = new \Vtiful\Kernel\Excel($config);
            $excel->openFile($path)
                ->openSheet()
                ->setType([
                    3 => \Vtiful\Kernel\Excel::TYPE_STRING
                ])
                ->setSkipRows(2);

            //处理数据
            $data = [];
            Log::error('*************开始读取数据*****************');
            $total = 0;
            $r_total = 0;
            $w_total = 0;
            while (($row = $excel->nextRow()) !== NULL) {
                $nums = count($row);
                if ($nums >= 11) {
                    $total++;
                    list(
                        $type_name,
                        $category_string,
                        $body,
                        $complain_date,
                        $name,
                        $mobile,
                        $deal_department_name,
                        $deal_limit_day,
                        $deal_status_name,
                        $deal_idea,
                        $deal_evaluation,
                        ) = $row;
                    if (empty($body)) continue;
                    $category_arr = str2arr($category_string, '/');
                    $data[] = [
                        'category' => $category_arr,
                        'body' => $body,
                        'type_name' => $type_name,
                        'name' => $name,
                        'mobile' => $mobile,
                        'complain_date' => Carbon::parse($complain_date)->toDateString(),
                        'deal_department_name' => $deal_department_name ?? '其他',
                        'deal_idea' => $deal_idea,
                        'deal_limit_day' => $deal_limit_day,
                        'deal_status_name' => $deal_status_name,
                        'deal_evaluation' => $deal_evaluation,
                    ];
                }
            }
            Log::error("数据读取完成");
            $n = count($data);
            $r_total = $n;
            Log::error("开始写入数据：共{$n}条");
            //数据写入
            DB::beginTransaction();
            foreach ($data as $i => $item) {
                //教室
                $category_1 = 0;
                $category_2 = 0;
                $category_3 = 0;
                $category_4 = 0;
                $category_5 = 0;
                if (isset($item['category'][0])) {
                    $category_1_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][0],
                    ]);
                    $category_1 = $category_1_model->id;
                }

                if (isset($item['category'][1])) {
                    $category_2_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][1],
                        'parent_id' => $category_1,
                    ]);
                    $category_2 = $category_2_model->id;
                }
                if (isset($item['category'][2])) {
                    $category_3_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][2],
                        'parent_id' => $category_2,
                    ]);
                    $category_3 = $category_3_model->id;
                }
                if (isset($item['category'][3])) {
                    $category_4_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][3],
                        'parent_id' => $category_3,
                    ]);
                    $category_4 = $category_4_model->id;
                }

                if (isset($item['category'][4])) {
                    $category_5_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][4],
                        'parent_id' => $category_4,
                    ]);
                    $category_5 = $category_5_model->id;
                }

                Category::updateLevel();

//                $type = Dict::firstOrCreateItem('MESSAGE_FORM', $item['type_name']);
//                if (!$type) continue;
                //事项来源--获取id
                $type_id = 0;
                foreach ($sourceMatters as $sourceMatter) {
                    if (in_array($item['type_name'], $sourceMatter['names'])) {
                        $type_id = $sourceMatter['id'];
                    }
                }
                if (!$type_id) $type_id = $sourceMatter_luoshan;

                //投诉时间--判断时间
                if (strtotime($item['complain_date']) < strtotime('2020-01-01')) $item['complain_date'] = Carbon::now()->toDateString();

//                $department = Department::query()->firstOrCreate([
//                    'name' => $item['deal_department_name']
//                ]);
                $department_id = 0;
                foreach ($departments as $department) {
                    if (in_array($item['deal_department_name'], $department['names'])) {
                        $department_id = $department['id'];
                    }
                }
                if (!$department_id) continue;

                $department_admin_id = Admin::query()->where('department_id', $department_id)->where('status', ModelStatusEnum::OK)->value('id') ?? 0;

                $deal_status = DealStatusEnum::WAIT;
                switch ($item['deal_status_name']) {
                    case '未办理':
                        $deal_status = DealStatusEnum::WAIT;
                        break;
                    case '正在办理':
                        $deal_status = DealStatusEnum::IN;
                        break;
                    case '已办结':
                        $deal_status = DealStatusEnum::OK;
                        break;
                }

                //提取字段关键字
                $address_id = 0;
                $address_name = '';
                $address_keys = Dict::byCodeGetDicts('ADDRESS_KEYS');
                foreach ($address_keys as $key) {
                    if (strpos($item['body'], $key['name']) !== false) {
                        $address_id = $key['id'];
                        $address_name = $key['name'];
                        break;
                    }
                }
                if (!$address_id) {
                    $address_id = 72;
                    $address_name = '其他';
                }

                Jieba::init();
                Finalseg::init();
                $seg_list = Jieba::cut(remove_symbol($item['body']));

                $md5 = md5($item['body']);
                $id = Message::query()->where('body_md5', $md5)->where('import_department_id', $import_department_id)->value('id');
                $w_total++;
                if ($id) {
                    $mark_red_day = null;
                    if ($deal_status == DealStatusEnum::OK) $mark_red_day = date('Y-m-d');
                    Message::query()->where('id', $id)->update([
                        'type_id' => $type_id,
                        'category_1_id' => $category_1,
                        'category_2_id' => $category_2,
                        'category_3_id' => $category_3,
                        'category_4_id' => $category_4,
                        'category_5_id' => $category_5,

                        'name' => $item['name'],
                        'mobile' => $item['mobile'],
                        'address_id' => $address_id,
                        'address_name' => $address_name,

                        'complain_date' => $item['complain_date'],
                        'warn_type_id' => 1,
                        'body' => $item['body'],
                        'body_md5' => $md5,
                        'keywords' => $seg_list,

                        'deal_department_id' => $department_id,
                        'deal_admin_id' => $department_admin_id,
                        'deal_evaluation' => $item['deal_evaluation'],

                        'deal_status' => $deal_status,
                        'deal_idea' => $item['deal_idea'],
                        'deal_time' => $item['deal_idea'] ? $item['complain_date'] : null,

                        'assign_deal_time' => $item['complain_date'],
                        'assign_admin_id' => $admin['id'],
                        'is_assign_status' => 1,

                        'deal_limit_day' => $item['deal_limit_day'],
                        'deal_limit_date' => Carbon::parse($item['complain_date'])->addDays($item['deal_limit_day'])->toDateTimeString(),

                        'import_record_id' => $importRecord->id,
                        'import_department_id' => $import_department_id,
                        'import_admin_id' => $admin['id'],
                        'status' => 1,
                        'mark_red_day' => $mark_red_day
                    ]);

                    //替换
                    continue;
                }

                Message::query()->create([
                    'type_id' => $type_id,
                    'category_1_id' => $category_1,
                    'category_2_id' => $category_2,
                    'category_3_id' => $category_3,
                    'category_4_id' => $category_4,
                    'category_5_id' => $category_5,
                    'no' => Str::random(8),

                    'name' => $item['name'],
                    'mobile' => $item['mobile'],
                    'address_id' => $address_id,
                    'address_name' => $address_name,

                    'complain_date' => $item['complain_date'],
                    'warn_type_id' => 1,
                    'body' => $item['body'],
                    'body_md5' => $md5,
                    'keywords' => $seg_list,

                    'deal_department_id' => $department_id,
                    'deal_admin_id' => $department_admin_id,
                    'deal_evaluation' => $item['deal_evaluation'],

                    'deal_status' => $deal_status,
                    'deal_idea' => $item['deal_idea'],
                    'deal_time' => $item['deal_idea'] ? $item['complain_date'] : null,

                    'assign_deal_time' => $item['complain_date'],
                    'assign_admin_id' => $admin['id'],
                    'is_assign_status' => 1,

                    'deal_limit_day' => $item['deal_limit_day'],
                    'deal_limit_date' => Carbon::parse($item['complain_date'])->addDays($item['deal_limit_day'])->toDateTimeString(),

                    'import_record_id' => $importRecord->id,
                    'import_department_id' => $import_department_id,
                    'import_admin_id' => $admin['id'],
                    'status' => 1
                ]);

                Log::error("正在创建：第{$i}条数据已完成");
            }

            DB::commit();

            Category::updateLevel();
            Log::error("-----------完成---------------");
            $importRecord->message = "共{$total}条，读取{$r_total}条，导入{$w_total}条。";
            $importRecord->status = ImportStatusEnum::IMPORT_OK;
            $importRecord->save();
        } catch (\Exception $exception) {
            $importRecord->status = ImportStatusEnum::IMPORT_ERROR;
            $importRecord->message = $exception->getMessage();
            $importRecord->save();
            Log::error($exception);
            DB::rollBack();
        }
    }


    /**
     * 模版二
     * @param $importRecord
     * @return false|void
     */
    public function importTemplate2(&$importRecord)
    {
        ini_set('memory_limit', '1024M');
        $admin = $this->admin;
        $import_department_id = $admin['department_id'];

        $importRecord->status = ImportStatusEnum::IMPORT_IN;
        $importRecord->save();

        $path = Storage::disk('public')->path(Resource::byIdGetPath($importRecord->resource_id));
        if (!file_exists($path)) {
            $importRecord->status = ImportStatusEnum::IMPORT_ERROR;
            $importRecord->message = '找不到文件';
            $importRecord->save();
            Log::error('*************找不到文件***************');
            Log::error($path);
            return false;
        }
//        $path = base_path('2.xlsx');
        $departments = Department::getNames();
        //获取事项来源列表--id---and---相似名称
        $sourceMatters = SourceMatters::getNames();
        $sourceMatter_luoshan = SourceMatters::query()->where('name', '罗山县')->where('status', ModelStatusEnum::OK)->value('id');

        try {
            $config = ['path' => ''];
            $excel = new \Vtiful\Kernel\Excel($config);
            $excel->openFile($path)
                ->openSheet()
                ->setType([
                    14 => \Vtiful\Kernel\Excel::TYPE_STRING
                ])
                ->setSkipRows(2);

            //处理数据
            $data = [];
            Log::error('*************开始读取数据*****************');
            $total = 0;
            $r_total = 0;
            $w_total = 0;
            while (($row = $excel->nextRow()) !== NULL) {
                $nums = count($row);
                if ($nums == 44) {
                    $total++;
                    $type_name = '12345';
                    $category_string = $row[13];
                    $body = $row[11];
                    $complain_date = $row[14];
                    $name = $row[6];
                    $mobile = $row[4];
                    $deal_department_name = $row[19];
                    $deal_limit_day = 0;
                    $deal_idea = $row[26];
                    $deal_evaluation = 100;
                    $deal_status_name = empty($deal_idea) ? '正在办理' : '已办结';

                    if (empty($body)) continue;
                    $category_arr = str2arr($category_string, '/');
                    $data[] = [
                        'category' => $category_arr,
                        'body' => $body,
                        'type_name' => $type_name,
                        'name' => $name,
                        'mobile' => $mobile,
                        'complain_date' => Carbon::parse($complain_date)->toDateString(),
                        'deal_department_name' => $deal_department_name ?? '其他',
                        'deal_idea' => $deal_idea,
                        'deal_limit_day' => $deal_limit_day,
                        'deal_status_name' => $deal_status_name,
                        'deal_evaluation' => $deal_evaluation,
                    ];
                }
            }
            Log::error("数据读取完成");
            $n = count($data);
            Log::error("开始写入数据：共{$n}条");
            $r_total = count($data);
            //数据写入
            DB::beginTransaction();
            foreach ($data as $i => $item) {
                //教室
                $category_1 = 0;
                $category_2 = 0;
                $category_3 = 0;
                $category_4 = 0;
                $category_5 = 0;
                if (isset($item['category'][0])) {
                    $category_1_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][0],
                    ]);
                    $category_1 = $category_1_model->id;
                }

                if (isset($item['category'][1])) {
                    $category_2_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][1],
                        'parent_id' => $category_1,
                    ]);
                    $category_2 = $category_2_model->id;
                }
                if (isset($item['category'][2])) {
                    $category_3_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][2],
                        'parent_id' => $category_2,
                    ]);
                    $category_3 = $category_3_model->id;
                }
                if (isset($item['category'][3])) {
                    $category_4_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][3],
                        'parent_id' => $category_3,
                    ]);
                    $category_4 = $category_4_model->id;
                }

                if (isset($item['category'][4])) {
                    $category_5_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][4],
                        'parent_id' => $category_4,
                    ]);
                    $category_5 = $category_5_model->id;
                }

                Category::updateLevel();


//                $type = Dict::firstOrCreateItem('MESSAGE_FORM', $item['type_name']);
//                if (!$type) continue;
                //事项来源--获取id
                $type_id = 0;
                foreach ($sourceMatters as $sourceMatter) {
                    if (in_array($item['type_name'], $sourceMatter['names'])) {
                        $type_id = $sourceMatter['id'];
                    }
                }
                if (!$type_id) $type_id = $sourceMatter_luoshan;

                //投诉时间--判断时间
                if (strtotime($item['complain_date']) < strtotime('2020-01-01')) $item['complain_date'] = Carbon::now()->toDateString();

//                $department = Department::query()->firstOrCreate([
//                    'name' => $item['deal_department_name']
//                ]);

                $department_id = 0;
                foreach ($departments as $department) {
                    if (in_array($item['deal_department_name'], $department['names'])) {
                        $department_id = $department['id'];
                    }
                }
                if (!$department_id) continue;
                $department_admin_id = Admin::query()->where('department_id', $department_id)->where('status', ModelStatusEnum::OK)->value('id') ?? 0;

                $deal_status = DealStatusEnum::WAIT;
                switch ($item['deal_status_name']) {
                    case '未办理':
                        $deal_status = DealStatusEnum::WAIT;
                        break;
                    case '正在办理':
                        $deal_status = DealStatusEnum::IN;
                        break;
                    case '已办结':
                        $deal_status = DealStatusEnum::OK;
                        break;
                }

                //提取字段关键字
                $address_id = 0;
                $address_name = '';
                $address_keys = Dict::byCodeGetDicts('ADDRESS_KEYS');
                foreach ($address_keys as $key) {
                    if (strpos($item['body'], $key['name']) !== false) {
                        $address_id = $key['id'];
                        $address_name = $key['name'];
                        break;
                    }
                }

                Jieba::init();
                Finalseg::init();
                $seg_list = Jieba::cut(remove_symbol($item['body']));

                $md5 = md5($item['body']);
                $id = Message::query()->where('body_md5', $md5)->where('import_department_id', $import_department_id)->value('id');
                $w_total++;
                if ($id) {
                    $mark_red_day = null;
                    if ($deal_status == DealStatusEnum::OK) $mark_red_day = date('Y-m-d');
                    Message::query()->where('id', $id)->update([
                        'type_id' => $type_id,
                        'category_1_id' => $category_1,
                        'category_2_id' => $category_2,
                        'category_3_id' => $category_3,
                        'category_4_id' => $category_4,
                        'category_5_id' => $category_5,

                        'name' => $item['name'],
                        'mobile' => $item['mobile'],
                        'address_id' => $address_id,
                        'address_name' => $address_name,

                        'complain_date' => $item['complain_date'],
                        'warn_type_id' => 1,
                        'body' => $item['body'],
                        'body_md5' => $md5,
                        'keywords' => $seg_list,

                        'deal_department_id' => $department_id,
                        'deal_admin_id' => $department_admin_id,
                        'deal_evaluation' => $item['deal_evaluation'],

                        'deal_status' => $deal_status,
                        'deal_idea' => $item['deal_idea'],
                        'deal_time' => $item['deal_idea'] ? $item['complain_date'] : null,

                        'assign_deal_time' => $item['complain_date'],
                        'assign_admin_id' => $admin['id'],
                        'is_assign_status' => 1,

                        'deal_limit_day' => $item['deal_limit_day'],
                        'deal_limit_date' => Carbon::parse($item['complain_date'])->addDays($item['deal_limit_day'])->toDateTimeString(),

                        'import_record_id' => $importRecord->id,
                        'import_department_id' => $import_department_id,
                        'import_admin_id' => $admin['id'],
                        'status' => 1,
                        'mark_red_day' => $mark_red_day
                    ]);

                    //替换
                    continue;
                }

                Message::query()->create([
                    'type_id' => $type_id,
                    'category_1_id' => $category_1,
                    'category_2_id' => $category_2,
                    'category_3_id' => $category_3,
                    'category_4_id' => $category_4,
                    'category_5_id' => $category_5,
                    'no' => Str::random(8),

                    'name' => $item['name'],
                    'mobile' => $item['mobile'],
                    'address_id' => $address_id,
                    'address_name' => $address_name,

                    'complain_date' => $item['complain_date'],
                    'warn_type_id' => 1,
                    'body' => $item['body'],
                    'body_md5' => $md5,
                    'keywords' => $seg_list,

                    'deal_department_id' => $department_id,
                    'deal_admin_id' => $department_admin_id,
                    'deal_evaluation' => $item['deal_evaluation'],

                    'deal_status' => $deal_status,
                    'deal_idea' => $item['deal_idea'],
                    'deal_time' => $item['deal_idea'] ? $item['complain_date'] : null,

                    'assign_deal_time' => $item['complain_date'],
                    'assign_admin_id' => $admin['id'],
                    'is_assign_status' => 1,

                    'deal_limit_day' => $item['deal_limit_day'],
                    'deal_limit_date' => Carbon::parse($item['complain_date'])->addDays($item['deal_limit_day'])->toDateTimeString(),

                    'import_record_id' => $importRecord->id,
                    'import_department_id' => $import_department_id,
                    'import_admin_id' => $admin['id'],
                    'status' => 1
                ]);

                Log::error("正在创建：第{$i}条数据已完成");
            }

            DB::commit();

            Category::updateLevel();
            Log::error("-----------完成---------------");
            $importRecord->message = "共{$total}条，读取{$r_total}条，导入{$w_total}条。";
            $importRecord->status = ImportStatusEnum::IMPORT_OK;
            $importRecord->save();
        } catch (\Exception $exception) {
            $importRecord->status = ImportStatusEnum::IMPORT_ERROR;
            $importRecord->message = $exception->getMessage();
            $importRecord->save();
            Log::error($exception);
            DB::rollBack();
        }
    }

    /**
     * 模版二
     * @param $importRecord
     * @return false|void
     */
    public function importTemplate3(&$importRecord)
    {
        ini_set('memory_limit', '1024M');
        $admin = $this->admin;
        $import_department_id = $admin['department_id'];

        $importRecord->status = ImportStatusEnum::IMPORT_IN;
        $importRecord->save();

        $path = Storage::disk('public')->path(Resource::byIdGetPath($importRecord->resource_id));
        if (!file_exists($path)) {
            $importRecord->status = ImportStatusEnum::IMPORT_ERROR;
            $importRecord->message = '找不到文件';
            $importRecord->save();
            Log::error('*************找不到文件***************');
            Log::error($path);
            return false;
        }
//        $path = base_path('2.xlsx');
        $departments = Department::getNames();
        //获取事项来源列表--id---and---相似名称
        $sourceMatters = SourceMatters::getNames();
        $sourceMatter_luoshan = SourceMatters::query()->where('name', '罗山县')->where('status', ModelStatusEnum::OK)->value('id');

        try {
            $config = ['path' => ''];
            $excel = new \Vtiful\Kernel\Excel($config);
            $excel->openFile($path)
                ->openSheet()
                ->setType([
                    0 => \Vtiful\Kernel\Excel::TYPE_STRING
                ])
                ->setSkipRows(2);

            //处理数据
            $data = [];
            Log::error('*************开始读取数据*****************');
            $total = 0;
            $r_total = 0;
            $w_total = 0;

            while (($row = $excel->nextRow()) !== NULL) {
                $nums = count($row);
                if ($nums == 15) {
                    $total++;
                    $type_name = '信访办';
                    $category_string = $row[7];
                    $body = $row[14];
                    $complain_date = $row[0];
                    $name = $row[4];
                    $mobile = '';
                    $deal_department_name = $row[10];
                    $deal_limit_day = 0;
                    $deal_idea = null;
                    $deal_evaluation = 100;
                    $deal_status_name = empty($deal_idea) ? '正在办理' : '已办结';

                    if (empty($body)) continue;
                    $category_arr = str2arr($category_string, '/');
                    $data[] = [
                        'category' => $category_arr,
                        'body' => $body,
                        'type_name' => $type_name,
                        'name' => $name,
                        'mobile' => $mobile,
                        'complain_date' => Carbon::parse($complain_date)->toDateString(),
                        'deal_department_name' => $deal_department_name ?? '其他',
                        'deal_idea' => $deal_idea,
                        'deal_limit_day' => $deal_limit_day,
                        'deal_status_name' => $deal_status_name,
                        'deal_evaluation' => $deal_evaluation,
                    ];
                }
            }
            Log::error("数据读取完成");
            $n = count($data);
            $r_total = $n;
            Log::error("开始写入数据：共{$n}条");
            //数据写入
            DB::beginTransaction();
            foreach ($data as $i => $item) {
                //教室
                $category_1 = 0;
                $category_2 = 0;
                $category_3 = 0;
                $category_4 = 0;
                $category_5 = 0;
                if (isset($item['category'][0])) {
                    $category_1_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][0],
                    ]);
                    $category_1 = $category_1_model->id;
                }

                if (isset($item['category'][1])) {
                    $category_2_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][1],
                        'parent_id' => $category_1,
                    ]);
                    $category_2 = $category_2_model->id;
                }
                if (isset($item['category'][2])) {
                    $category_3_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][2],
                        'parent_id' => $category_2,
                    ]);
                    $category_3 = $category_3_model->id;
                }
                if (isset($item['category'][3])) {
                    $category_4_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][3],
                        'parent_id' => $category_3,
                    ]);
                    $category_4 = $category_4_model->id;
                }

                if (isset($item['category'][4])) {
                    $category_5_model = Category::query()->firstOrCreate([
                        'name' => $item['category'][4],
                        'parent_id' => $category_4,
                    ]);
                    $category_5 = $category_5_model->id;
                }

                Category::updateLevel();


//                $type = Dict::firstOrCreateItem('MESSAGE_FORM', $item['type_name']);
//                if (!$type) continue;
                //事项来源--获取id
                $type_id = 0;
                foreach ($sourceMatters as $sourceMatter) {
                    if (in_array($item['type_name'], $sourceMatter['names'])) {
                        $type_id = $sourceMatter['id'];
                    }
                }
                if (!$type_id) $type_id = $sourceMatter_luoshan;

                //投诉时间--判断时间
                if (strtotime($item['complain_date']) < strtotime('2020-01-01')) $item['complain_date'] = Carbon::now()->toDateString();

//                $department = Department::query()->firstOrCreate([
//                    'name' => $item['deal_department_name']
//                ]);

                $department_id = 0;
                foreach ($departments as $department) {
                    if (in_array($item['deal_department_name'], $department['names'])) {
                        $department_id = $department['id'];
                    }
                }
                if (!$department_id) continue;
                $department_admin_id = Admin::query()->where('department_id', $department_id)->where('status', ModelStatusEnum::OK)->value('id') ?? 0;

                $deal_status = DealStatusEnum::WAIT;
                switch ($item['deal_status_name']) {
                    case '未办理':
                        $deal_status = DealStatusEnum::WAIT;
                        break;
                    case '正在办理':
                        $deal_status = DealStatusEnum::IN;
                        break;
                    case '已办结':
                        $deal_status = DealStatusEnum::OK;
                        break;
                }

                //提取字段关键字
                $address_id = 0;
                $address_name = '';
                $address_keys = Dict::byCodeGetDicts('ADDRESS_KEYS');
                foreach ($address_keys as $key) {
                    if (strpos($item['body'], $key['name']) !== false) {
                        $address_id = $key['id'];
                        $address_name = $key['name'];
                        break;
                    }
                }

                Jieba::init();
                Finalseg::init();
                $seg_list = Jieba::cut(remove_symbol($item['body']));

                $md5 = md5($item['body']);
                $id = Message::query()->where('body_md5', $md5)->where('import_department_id', $import_department_id)->value('id');
                $w_total++;
                if ($id) {
                    $mark_red_day = null;
                    if ($deal_status == DealStatusEnum::OK) $mark_red_day = date('Y-m-d');
                    Message::query()->where('id', $id)->update([
                        'type_id' => $type_id,
                        'category_1_id' => $category_1,
                        'category_2_id' => $category_2,
                        'category_3_id' => $category_3,
                        'category_4_id' => $category_4,
                        'category_5_id' => $category_5,

                        'name' => $item['name'],
                        'mobile' => $item['mobile'],
                        'address_id' => $address_id,
                        'address_name' => $address_name,

                        'complain_date' => $item['complain_date'],
                        'warn_type_id' => 1,
                        'body' => $item['body'],
                        'body_md5' => $md5,
                        'keywords' => $seg_list,

                        'deal_department_id' => $department_id,
                        'deal_admin_id' => $department_admin_id,
                        'deal_evaluation' => $item['deal_evaluation'],

                        'deal_status' => $deal_status,
                        'deal_idea' => $item['deal_idea'],
                        'deal_time' => $item['deal_idea'] ? $item['complain_date'] : null,

                        'assign_deal_time' => $item['complain_date'],
                        'assign_admin_id' => $admin['id'],
                        'is_assign_status' => 1,

                        'deal_limit_day' => $item['deal_limit_day'],
                        'deal_limit_date' => Carbon::parse($item['complain_date'])->addDays($item['deal_limit_day'])->toDateTimeString(),

                        'import_record_id' => $importRecord->id,
                        'import_department_id' => $import_department_id,
                        'import_admin_id' => $admin['id'],
                        'status' => 1,
                        'mark_red_day' => $mark_red_day
                    ]);

                    //替换
                    continue;
                }

                Message::query()->create([
                    'type_id' => $type_id,
                    'category_1_id' => $category_1,
                    'category_2_id' => $category_2,
                    'category_3_id' => $category_3,
                    'category_4_id' => $category_4,
                    'category_5_id' => $category_5,
                    'no' => Str::random(8),

                    'name' => $item['name'],
                    'mobile' => $item['mobile'],
                    'address_id' => $address_id,
                    'address_name' => $address_name,

                    'complain_date' => $item['complain_date'],
                    'warn_type_id' => 1,
                    'body' => $item['body'],
                    'body_md5' => $md5,
                    'keywords' => $seg_list,

                    'deal_department_id' => $department_id,
                    'deal_admin_id' => $department_admin_id,
                    'deal_evaluation' => $item['deal_evaluation'],

                    'deal_status' => $deal_status,
                    'deal_idea' => $item['deal_idea'],
                    'deal_time' => $item['deal_idea'] ? $item['complain_date'] : null,

                    'assign_deal_time' => $item['complain_date'],
                    'assign_admin_id' => $admin['id'],
                    'is_assign_status' => 1,

                    'deal_limit_day' => $item['deal_limit_day'],
                    'deal_limit_date' => Carbon::parse($item['complain_date'])->addDays($item['deal_limit_day'])->toDateTimeString(),

                    'import_record_id' => $importRecord->id,
                    'import_department_id' => $import_department_id,
                    'import_admin_id' => $admin['id'],
                    'status' => 1
                ]);

                Log::error("正在创建：第{$i}条数据已完成");
            }

            DB::commit();

            Category::updateLevel();
            Log::error("-----------完成---------------");
            $importRecord->status = ImportStatusEnum::IMPORT_OK;
            $importRecord->message = "共{$total}条，读取{$r_total}条，导入{$w_total}条。";
            $importRecord->save();
        } catch (\Exception $exception) {
            $importRecord->status = ImportStatusEnum::IMPORT_ERROR;
            $importRecord->message = $exception->getMessage();
            $importRecord->save();
            Log::error($exception);
            DB::rollBack();
        }
    }
}