php-jiesuan/app/command/qrdupload.php

<?php
declare (strict_types = 1);

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use think\facade\Cache;
use think\facade\Db;

class qrdupload extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('qrdupload')
            ->setDescription('the qrdupload command');
    }

    protected function execute(Input $input, Output $output)
    {
        $redis = Cache::store('redis');
        $isqrd = $redis->get("qrdupload");
        if($isqrd==0){
            $redis->set("qrdupload",1);
        }else{
            return;
        }
        $dirs =  root_path() ."/public/storage/topic/QRD";
        if(!is_dir($dirs)){
            $redis->set("qrdupload",0);
            return ;
        }
        $ls =scandir($dirs);
        $files="";
        $extend="";
        foreach ($ls as $value){
            if($value=="." || $value==".."){
                continue;
            }

            if(is_file($dirs."/". $value)){
                $extend =pathinfo($dirs."/". $value)['extension'];
                if($extend=="xls"||$extend=="xlsx"){
                    $files = $dirs."/". $value;
                    break;
                }
            }
        }
      //  $extend =pathinfo($files)['extension'];
        //  $PHPEXCEL =new \PHPExcel()
        if ($extend == 'xlsx') {
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        } else {
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }
        $spreadsheet = $objReader->load($files);
        $sheet = $spreadsheet->getActiveSheet();
        $sheetData = $sheet->toArray();
        if (empty($sheetData) || !is_array($sheetData)) {
            echo "数据不能为空";
            $redis->set("qrdupload",0);
            return ;
        }
        Db::startTrans();
        try{
            foreach ($sheetData as $key=>$value){
                if($key==0){
                    continue;
                }
                $customer= Db::name("customer_info")->where("companyName","=",$value[11])->find();
                $companyNo = isset($customer['companyNo'])?$customer['companyNo']:$value['12'];
                $data=[
                    'name'=>$value[0],
                    'createdTime'=>$value[5],
                    'startTime'=>$value[7],
                    'finishTime'=>$value[7],
                    'ownerName'=>$value[3],
                    'departmentName'=>$value[2],
                    'sequenceNo'=>'QRDCP-'.date("YmdHis").$key,
                    'sequenceStatus'=>$value[9],
                    'Date1617081795606'=>$value[24],
                    'RelevanceForm1617022738190'=>$value[12],
                    'ShortText1617081712980'=>'',
                    'ShortText1617022967356'=>$value[19],
                    'ShortText1617023369580'=>$value[21],
                    'ShortText1617111725596'=>$value[25],
                    'ShortText1617365292699'=>$value[26],
                    'text1617499162303'=>$value[27],
                    'ShortText1617365495029'=>$value[28],
                    'ShortText1617499192065'=>$value[29],
                    'ShortText1617499210157'=>$value[30],
                    'text1617365578699'=>$value[31],
                    'text1617365594353'=>$value[32],
                    'text1617365597522'=>$value[33],
                    'text1617365646297'=>$value[34],
                    'text1617365950353'=>$value[35],
                    'ShortText1617366563255'=>$value[36],
                    'number1617366111666'=>$value[37],
                    'number1617366110639'=>$value[38],
                    'ShortText1617366186330'=>$value[39],
                    'ShortText1617367958909'=>$value[40],
                    'Number1617365688048'=>$value[41],
                    'Number1617366579767'=>$value[42],
                    'Number1617366626104'=>$value[43],
                    'ShortText1617650669915'=>$value[44],
                    'ShortText1617650701648'=>$value[45],
                    'ShortText1617670912205'=>$value[46],
                    'ShortText1617874567622'=>$value[47],
                    'Number1618248810624'=>$value[48],
                    'Number1618248813613'=>$value[49],
                    'Number1618249015661'=>$value[50],
                    'Number1618249019294'=>$value[51],
                    'Number1618249146997'=>$value[52],
                    'number1618249149738'=>$value[53],
                    'Number1618249202608'=>$value[54],
                    'Number1618249205231'=>$value[55],
                    'number1618249258956'=>$value[56],
                    'ShortText1618267589049'=>$value[57],
                    'StaffSelector1618445844046'=>$value[58],
                    'StaffSelector1618445850209'=>$value[59],
                    'ShortText1618446328311'=>$value[60],
                    'ShortText1618446949933'=>$value[61],
                    'ShortText1618446973813'=>$value[62],
                    'LongText1618446982973'=>$value[63],
                    'Date1618447068290'=>$value[64],
                    'ShortText1618447165317'=>$value[65],
                    'ShortText1618559007040'=>$value[66],
                    'ShortText1618559043560'=>$value[53]==0  ?3 :($value[52]==0 ? 1 :2),
                    'ShortText1618559274859'=>$value[68],
                    'ShortText1618887148862'=>$value[69],
                    'ShortText1619146965777'=>$value[70],
                    'ShortText1619164078168'=>$value[71],
                    'Date1619401771986'=>$value[72],
                    'Date1619402124793'=>$value[73],
                    'Number1619403905454'=>$value[74],
                    'Number1619403908740'=>$value[75],
                    'ShortText1619413388848'=>$value[15],
                    'ShortText1619413394855'=>$value[13],
                    'ShortText1619413401534'=>$value[14],
                    'ShortText1619487657199'=>$value[77],
                    'StaffSelector1619488013389'=>$value[79],
                    'ShortText1619523689586'=>$value[80],
                    'ShortText1619523693355'=>$value[85],
                    'ShortText1619523892833'=>$value[11],
                    'Date1619542686640'=>$value[82],
                    'RelevanceForm1619542896677'=>$value[83],
                    'ShortText1619542951283'=>$companyNo,
                    'ShortText1619543001392'=>$value[85],
                    'ShortText1619610909619'=>$value[86],
                    'Date1619626098213'=>$value[87],
                    'ShortText1619626536464'=>$value[88],
                    'Number1619626656082'=>$value[89],
                    'Number1619626666450'=>$value[90],
                    'Number1619632062547'=>$value[91],
                    'ShortText1619743751825'=>'',
                    'text1619743754479'=>"",
                    'text1619743755335'=>"",
                    'text1619743756377'=>"",
                    'Date1620370378620'=>$value[92],
                    'ShortText1620750298602'=>$value[93],
                    'ShortText1620750301301'=>$value[94],
                    'RelevanceForm1620750481892'=>$value[95],
                    'Number1620751537737'=>$value[96],
                    'Date1620751562219'=>$value[97],
                    'status'=>'',
                    'paying_fee'=>'',
                    'inving_fee'=>''
                ];
                $in=  Db::name("qrd_info")->insert($data);
                if($in){
                    $qrd = Db::name("qrd")->where([['sequenceNo',"=",$value[45]]])->find();
                    if($qrd){
                         $qrd['total_fee'] += round($value['49'],2);
                         $qrd['apay_fee'] += round($value['52'],2);
                         $qrd['wpay_fee'] += round($value['53'],2);
                         $qrd['ainv_fee'] += round($value['54'],2);
                         $qrd['winv_fee'] += round($value['55'],2);
                         $qrd['inv_status'] = $value['68']==1 ? 1 : $qrd['inv_status'];
                         $qrd['pay_status'] =  $value['67']==1 ? 1 : $qrd['pay_status'];
                       $up= Db::name("qrd")->save($qrd);
                        if(!$up){
                            unlink($files);
                            Db::rollback();
                            $redis->set("qrdupload",0);
                            return;
                        }
                    }else{
                        $qrd=[
                                "name"=>'Excel'.$value[45],
                                "createdTime"=>$value[5],
                                "sequenceNo"=>$value[45],
                                "sales_name"=>$value[58],
                                "sales_depart"=>$value[2],
                                "customer"=>$value[11],
                                "total_fee"=> round($value['49'],2),
                                "mobile"=>"",
                                "contactor"=>"",
                                "qrddate"=>$value[5],
                                "companyNo"=>$companyNo,
                                "supplerName"=>$value[10],
                                "addr"=>"",
                                "depart"=>$value[2],
                                "type"=>1,
                                "apay_fee"=>round($value['52'],2),
                                "pay_fee"=>0,
                                "wpay_fee"=>round($value['53'],2),
                                "ainv_fee"=>round($value['54'],2),
                                "inv_fee"=>0,
                                "winv_fee"=>round($value['55'],2),
                                "inv_status"=>$value['67'],
                                "pay_status"=>$value['68'],
                                "status"=>1,
                                "addtime"=>$value[5],
                                "updatetime"=>date("Y-m-d H:i:s")
                            ];
                        $link=Db::name("qrd")->insert($qrd);
                        if(!$link){
                            unlink($files);
                            Db::rollback();
                            $redis->set("qrdupload",0);
                            return;
                        }
                    }

                }
                if(!$in){
                    unlink($files);
                    Db::rollback();
                    $redis->set("qrdupload",0);
                    echo $value[0];
                    return;
                }
            }
            Db::commit();
        }catch (\Exception $e){
            Db::rollback();
            echo $e->getMessage();
            $redis->set("qrdupload",0);
            return ;
        }

        unlink($files);
        $redis->set("qrdupload",0);
        $output->writeln('qrdupload');
    }
}