crm/app/command/Crmresimport.php

<?php
namespace app\command;

use think\console\Command;
use think\console\input\Argument;
use think\console\Input;
use think\console\Output;
use app\model\Customer;
use app\model\CrmImportLog;
use PhpOffice\PhpSpreadsheet\IOFactory;

class Crmresimport extends Command
{
    protected function configure()
    {
        $this->setName('crmresimport')
            ->addArgument('employee_id', Argument::OPTIONAL, "employee id")
            ->addArgument('root_id', Argument::OPTIONAL, "root id")
            ->addArgument('org_id', Argument::OPTIONAL, "org id")
            ->setDescription('crm resource import');
    }

    protected function execute(Input $input, Output $output)
    {
        $employee_id = $input->getArgument('employee_id');
        $root_id = $input->getArgument('root_id');
        $org_id = $input->getArgument('org_id');
        $fileExtendName = substr(strrchr($_FILES['file']["name"], '.'), 1);

        $filename = $_FILES['file']['tmp_name'];
        if ($fileExtendName == 'xlsx') {
            $objReader = IOFactory::createReader('Xlsx');
        } else {
            $objReader = IOFactory::createReader('Xls');
        }


        $objPHPExcel = $objReader->load($filename);  //$filename可以是上传的表格，或者是指定的表格
        $sheet       = $objPHPExcel->getSheet(0);   //excel中的第一张sheet
        //$data = $sheet->toArray();
        //$highestRow  = $sheet->getHighestRow();
        $highestRow = $sheet->getHighestDataRow();
        $highestColumn = $sheet->getHighestDataColumn();
        ///

        //// to make crm import log record
        $fileoriname = $_FILES['file']['name'];
        $log = new CrmImportLog();
        $insertData = [
            'name' => $fileoriname . '-' . date('Y-m-d H:i:s', time()),
            'employee_id' => $employee_id,
            'org_id' => $root_id,
            'count' => $highestRow - 1,
            'left_num' => $highestRow - 1
        ];
        $log->save($insertData);
        ///
        $a           = 0;
        $realnum = 0;
        $badphonenum = 0;
        for ($i = 2; $i <= $highestRow; $i++) {
            $rowData = $sheet->rangeToArray('A' . $i . ':' . $highestColumn . $i, NULL, TRUE, FALSE);
            if ($this->isEmptyRow(reset($rowData))) {
                continue;
            } else {
                $realnum++;
            } // skip empty row

            //$data[$a]['name'] = (string) $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getValue();
            //$data[$a]['sex'] = (int) $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getValue();
            //$data[$a]['level'] = (string) $objPHPExcel->getActiveSheet()->getCell('C' . $i)->getValue();
            //$data[$a]['tag'] = (string) $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getValue();
            //$data[$a]['phone']       = (string) $objPHPExcel->getActiveSheet()->getCell('E' . $i)->getValue();
            //$data[$a]['email'] = (string) $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getValue();
            //$data[$a]['from'] = (string) $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getValue();
            //$data[$a]['enterprise'] = (string) $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getValue();
            //$data[$a]['position'] = (string) $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getValue();
            //$data[$a]['birthday'] = (string) $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getValue();
            //$data[$a]['pcd'] = (string) $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getValue();
            //$data[$a]['loc'] = (string) $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getValue();
            //$data[$a]['desc'] = (string) $objPHPExcel->getActiveSheet()->getCell('M' . $i)->getValue();
            //$data[$a]['age'] = (string) $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getValue();
            //$data[$a]['nation'] = (string) $objPHPExcel->getActiveSheet()->getCell('O' . $i)->getValue();
            //$data[$a]['citizenship'] = (string) $objPHPExcel->getActiveSheet()->getCell('P' . $i)->getValue();
            //$data[$a]['grade'] = (string) $objPHPExcel->getActiveSheet()->getCell('Q' . $i)->getValue();
            //$data[$a]['major'] = (string) $objPHPExcel->getActiveSheet()->getCell('R' . $i)->getValue();
            //$data[$a]['idcode'] = (string) $objPHPExcel->getActiveSheet()->getCell('S' . $i)->getValue();
            //$data[$a]['wx'] = (string) $objPHPExcel->getActiveSheet()->getCell('T' . $i)->getValue();
            //$data[$a]['qq'] = (string) $objPHPExcel->getActiveSheet()->getCell('U' . $i)->getValue();
            //$data[$a]['community_name'] = (string) $objPHPExcel->getActiveSheet()->getCell('V' . $i)->getValue();
            //$data[$a]['building_unit'] = (string) $objPHPExcel->getActiveSheet()->getCell('W' . $i)->getValue();
            //$data[$a]['house_delivery_time'] = (string) $objPHPExcel->getActiveSheet()->getCell('X' . $i)->getValue();
            //$data[$a]['square'] = (string) $objPHPExcel->getActiveSheet()->getCell('Y' . $i)->getValue();
            //$data[$a]['phone_two']       = (string) $objPHPExcel->getActiveSheet()->getCell('Z' . $i)->getValue();

            $data[$a]['name'] = (string) $objPHPExcel->getActiveSheet()->getCell('A' . $i)->getValue();
            $data[$a]['sex'] = (string) $objPHPExcel->getActiveSheet()->getCell('B' . $i)->getValue();
            $data[$a]['phone']       = (string) $objPHPExcel->getActiveSheet()->getCell('C' . $i)->getValue();
            $data[$a]['level'] = (string) $objPHPExcel->getActiveSheet()->getCell('D' . $i)->getValue();
            $data[$a]['community_name'] = (string) $objPHPExcel->getActiveSheet()->getCell('E' . $i)->getValue();
            $data[$a]['square'] = (int) $objPHPExcel->getActiveSheet()->getCell('F' . $i)->getValue();
            $data[$a]['house_type'] = (string) $objPHPExcel->getActiveSheet()->getCell('G' . $i)->getValue();
            $data[$a]['loc'] = (string) $objPHPExcel->getActiveSheet()->getCell('H' . $i)->getValue();
            $data[$a]['from'] = (string) $objPHPExcel->getActiveSheet()->getCell('I' . $i)->getValue();
            $data[$a]['focus'] = (string) $objPHPExcel->getActiveSheet()->getCell('J' . $i)->getValue();
            $data[$a]['house_delivery_time'] = (string) $objPHPExcel->getActiveSheet()->getCell('K' . $i)->getValue();
            $data[$a]['age'] = (string) $objPHPExcel->getActiveSheet()->getCell('L' . $i)->getValue();
            //$data[$a]['wx'] = (string) $objPHPExcel->getActiveSheet()->getCell('M' . $i)->getValue();
            //$data[$a]['qq'] = (string) $objPHPExcel->getActiveSheet()->getCell('N' . $i)->getValue();
            $data[$a]['apply_time'] = (string) $objPHPExcel->getActiveSheet()->getCell('M' . $i)->getValue();


            $t = [
                'phone'          => cypherphone($data[$a]['phone']),
                'name'           => $data[$a]['name'],
                'level'          => $data[$a]['level'],
                'state'          => '待确认',
                'community_name' => $data[$a]['community_name'],
                'house_type' => $data[$a]['house_type'],
                'house_delivery_time' => $data[$a]['house_delivery_time'],
                //'plan_deco_time' => $data[$a]['plan_deco_time'],
                'square'         => $data[$a]['square'],
                //'deco_style' => $data[$a]['deco_style'],
                //'budget' => $data[$a]['budget'],
                'sex'            => ($data[$a]['sex'] == '男') ? 1 : 2,
                'org_id'         => $root_id,
                'crm_res_id'     => $log->id,
                'is_resource'    => 1,
                'bad_phone'      => ($this->checkphone($data[$a]['phone'])) ? 0 : 1
            ];
            $obj = new Customer();
            $obj->insert($t);

            if (!$this->checkphone($data[$a]['phone'])) {
                $badphonenum++;
            }
        }
        ////
        $avaliable_count = $realnum - $badphonenum;
        $updateData = [
            'count' => $realnum,
            'avaliable_count' =>  $avaliable_count,
            'bad_phone_num' =>  $badphonenum,
            'left_num' => $avaliable_count
        ];
        $log->save($updateData);

        $output->writeln("true");
    }

    private function isEmptyRow($row)
    {
        foreach ($row as $cell) {
            if (null !== $cell) return false;
        }
        return true;
    }

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

}