Sjzs_admin/application/service/queue/ImportTable2Queue.php

<?php

// +----------------------------------------------------------------------
// | ThinkAdmin
// +----------------------------------------------------------------------
// | 版权所有 2014~2019 广州楚才信息科技有限公司 [ http://www.cuci.cc ]
// +----------------------------------------------------------------------
// | 官方网站: http://demo.thinkadmin.top
// +----------------------------------------------------------------------
// | 开源协议 ( https://mit-license.org )
// +----------------------------------------------------------------------
// | gitee 代码仓库：https://gitee.com/zoujingli/ThinkAdmin
// | github 代码仓库：https://github.com/zoujingli/ThinkAdmin
// +----------------------------------------------------------------------

namespace app\service\queue;

use think\console\Input;
use think\console\Output;
use think\Db;

/**
 * Class Jobs
 */
class ImportTable2Queue
{
    /**
     * 当前类名
     * @var string
     */
    const URI = self::class;


    /**
     * 执行任务
     * @param Input $input
     * @param Output $output
     * @param array $data
     * @throws \think\exception\PDOException
     */
    public function execute(Input $input, Output $output, array $data = [])
    {
        try {
            set_time_limit(0);
            ini_set ("memory_limit","-1");
            $info = Db::name('system_queue')
                ->where('type',$data['type'])
                ->where('import_name',$data['import_name'])
                ->order('id desc')
                ->limit(1)
                ->find();
            $import_path = $info['local_url'];
            $objPHPExcel= \PHPExcel_IOFactory::load($import_path);
            $currentSheet=$objPHPExcel->getSheet(0);//当前页
            //$maxCol = $objPHPExcel->getSheet(0)->getHighestColumn();#总列数
            $maxRow = $objPHPExcel->getSheet(0)->getHighestRow();#总行数
            Db::name('system_queue')->where('id',$info['id'])->update(['maxrow'=>($maxRow-1)]);
            $kd_array = Db::name('system_kd')->column('name');
            //循环从第二行开始，第一行往往是表头
            $insert_array = array();
            $count = 0;
            for($i=2;$i<=$maxRow;$i++)
            {
                $a = [];
                $name =$currentSheet->getCell("B".$i)->getFormattedValue();
                $dm=$currentSheet->getCell("F".$i)->getFormattedValue();
                $gb_time=$currentSheet->getCell("R".$i)->getFormattedValue();
                if (gettype($gb_time)=='double'){
                    $gb_time = gmdate("Y-m-d H:i:s", \PHPExcel_Shared_Date::ExcelToPHP($gb_time));
                }
                $date = date('Y-m-d',strtotime($gb_time));
                if ($i==2){
                    Db::name('system_queue')->where('id',$info['id'])->update(['date'=>$date]);
                    $is = Db::name('system_table2')->where('date',$date)->count();
                    if ($is){
                        Db::name('system_queue')->where('id',$info['id'])->update(
                            [
                                'status' => 4, 'end_at' => date('Y-m-d H:i:s'), 'desc' => '表中日期'.$date.'数据已存在,无法导入',
                            ]
                        );
                        die;
                    }
                }

                if (!in_array($name,$kd_array)){
                    if (!empty($name)){
                        array_push($kd_array,$name);
                        Db::name('system_kd')->insert(['name'=>$name]);
                    }
                }
                $a['import_log_id'] = $info['id'];
                $a['name'] = $name;
                $a['dm'] = $dm;
                $a['gb_time'] = $gb_time;
                $a['date'] = $date;
                array_push($insert_array,$a);
                $count = count($insert_array);
                if ($count>='5000'){
                    Db::name('system_table2')->insertAll($insert_array);
                    Db::name('system_queue')->where('id',$info['id'])->setInc('deal_count',$count);
                    $insert_array = array();
                    $count = 0;
                }
            }
            Db::name('system_table2')->insertAll($insert_array);
            Db::name('system_queue')->where('id',$info['id'])->setInc('deal_count',$count);

            $names = Db::name('system_table2')
                ->where('import_log_id',$info['id'])
                ->where('name','neq','')
                ->group('name')->column('name');
            if ($names){
                $insert_array = array();
                foreach ($names as &$v){
                    $b = array();
                    $b['import_log_id'] = $info['id'];
                    $b['name'] = $v;
                    $b['value'] = Db::name('system_table2')->where('import_log_id',$info['id'])->where('name',$v)->count();
                    $b['date'] = Db::name('system_table2')->where('import_log_id',$info['id'])->where('name',$v)->order('id asc')
                        ->limit(1)->value('date');
                    array_push($insert_array,$b);
                }
                Db::name('system_values2')->insertAll($insert_array);
            }

            $dms = Db::name('system_table2')->where('import_log_id',$info['id'])
                ->whereNotNull('dm')
                ->group('dm')
                ->column('dm');
            if ($dms){
                $insertarray = array();
                foreach ($dms as &$v){
                    $c = array();
                    $c['import_log_id'] = $info['id'];
                    $c['name'] = Db::name('system_table2')->where('import_log_id',$info['id'])->where('dm',$v)->value('name');
                    $c['dm'] = $v;
                    $c['value'] = Db::name('system_table2')->where('import_log_id',$info['id'])->where('dm',$v)->count();
                    $c['date'] = Db::name('system_queue')->where('id',$info['id'])->value('date');
                    array_push($insertarray,$c);
                }
                Db::name('system_values3')->insertAll($insertarray);
            }
        }catch (\think\exception\ValidateException $e) {
            file_put_contents("error2.txt", $e. "\n" . "\n", FILE_APPEND);
        }

    }

}