simple-yewu/console/controllers/CarController.php

<?php
/**
 * Created by PhpStorm.
 * User: zengchaoxin
 * Date: 2018/3/31
 * Time: 上午10:48
 */
namespace console\controllers;

use common\models\Brand2T;
use common\models\BrandT;
use common\models\CarT;
use common\models\FactoryT;
use common\models\ImportLogT;
use common\models\PeerPhoneT;
use common\models\Series2T;
use yii\console\Controller;

/**
 * Class 车辆批量数据处理代码
 * @package console\controllers
 */

class CarController extends Controller
{
    /**
     * 批量导入车辆信息
     *
     */
    public function actionImportCarinfo()
    {
        set_time_limit(0);
        $begin_time = time();
        $file_path = \Yii::getAlias('@console').'/controllers/car';
        $file = 'car_20180407.xlsx';
        $filename = $file_path.'/'.$file;
        $ext = substr($file,-4);
        $total = 0;
        $insert_total = 0;
        $total_a = 0;
        $total_b = 0;
        $total_c = 0;
        $total_d = 0;
        $total_e = 0;
        if($ext == 'xlsx') {
            echo $filename."\r\n";
            if(file_exists($filename)) {
                echo 'OK'."\r\n";
            } else {
                echo "error"."\r\n";
                exit;
            }
            $reader = new \PHPExcel_Reader_Excel2007();
            if(!$reader->canRead($filename)) {
                $reader = new \PHPExcel_Reader_Excel5();
                if(!$reader->canRead($filename)) {
                    $errorMessage = "Can not read file.";
                    echo $errorMessage;
                    return;
                }
            }
            $excel = $reader->load($filename);
            $sheet = $excel->getSheet(0);
            $rowCount = $sheet->getHighestRow();

//                $data = $sheet->toArray('', true, true);
//                $total = count($data);
            echo '总行数:'.$rowCount."\r\n";
            $j = 0;
            for ($i = 2; $i <= $rowCount; $i++) {
                $j++;
//                    if($j == 1) continue;
                echo $j.'/'.$rowCount."...";
                flush();
                $cell = $sheet->getCellByColumnAndRow(0, $i);
                $car_no = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(1, $i);
                $car_frame_no = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(2, $i);
                $register_date = $cell->getFormattedValue();
                $cell = $sheet->getCellByColumnAndRow(3, $i);
                $id_man = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(4, $i);
                $phone = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(5, $i);
                $factory_name = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(6, $i);
                $brand_name = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(7, $i);
                $series_name = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(8, $i);
                $company = $cell->getValue();
                $cell = $sheet->getCellByColumnAndRow(10, $i);
                $insurer1_date = $cell->getFormattedValue();
                $cell = $sheet->getCellByColumnAndRow(10, $i);
                $insurer2_date = $cell->getFormattedValue();

                echo $car_no."...";
                echo $register_date.'...';
                echo $insurer1_date.'...';

                $brand_info = Brand2T::findOne(['name'=>$brand_name]);
                if(!$brand_info) {
                    $brand_info = new Brand2T();
                    $brand_info->name = $brand_name;
                    $brand_info->save();
                }
                echo $brand_info->name.'('.$brand_info->id.')';

                $factory_info = FactoryT::findOne(['name'=>$factory_name]);
                if(!$factory_info) {
                    $factory_info = new FactoryT();
                    $factory_info->name = $factory_name;
                    $factory_info->save();
                }
                echo $factory_info->name.'('.$factory_info->id.')';

                $series_info = Series2T::findOne(['name'=>$series_name]);
                if(!$series_info) {
                    $series_info = new Series2T();
                    $series_info->name = $series_name;
                    $series_info->brand_id = $brand_info->id;
                    $series_info->factory_id = $factory_info->id;
                    $series_info->save();
                }
                echo $series_info->name.'('.$series_info->id.')';
                echo '...';
//                echo "\r\n";exit;
                $car_info = CarT::findOne(['car_no'=>$car_no]);
                if(!$car_info) {
                    $car_info = new CarT();

                    $car_info->user_id = 0;
                    $car_info->car_use_id = 0;
                    $car_info->car_type_id = 0;
                    $car_info->location = 1;
                    $car_info->is_track = 0;
                    $car_info->times = 1;
                    $car_info->invalid_flag = 0;
                    $car_info->invalid_id = 0;
                    $insert_total++;
                }
                $car_info->car_man = $id_man;
                $car_info->id_man = $id_man;
                $car_info->car_no = $car_no;
                $car_info->phone = $phone;
                $car_info->car_frame_no = $car_frame_no;
                $car_info->register_date = $register_date;
                $car_info->insurer1_date = $insurer1_date;
                $car_info->insurer2_date = $insurer2_date;
                $car_info->company = $company;
                $car_info->brand_id = $brand_info->id;
                $car_info->factory_id = $factory_info->id;
                $car_info->series_id = $series_info->id;
                $car_info->from_src = $file;
                $car_info->save();

                switch($car_info->location) {
                    case 1:
                        $total_a++;
                        break;
                    case 2:
                        $total_b++;
                        break;
                    case 3:
                        $total_c++;
                        break;
                    case 4:
                        $total_d++;
                        break;
                    case 5:
                        $total_e++;
                }
                echo "ok";
                echo "\r\n";
            }
            $total = $j;
        }
        //记录导入Log
        $log = new ImportLogT();
        $log->filename = $file;
        $log->total = $total;
        $log->success = $insert_total;
        $log->success_rate = ceil($insert_total/$total * 10000)/100;
        $log->save();

        $end_time = time();
        $total_time = $end_time - $begin_time;
        $h = floor($total_time/3600);
        $m = floor(($total_time - $h * 3600)/60);
        $s = $total_time - $h * 3600 - $m * 60;
        echo '花费时间: '.$h.'小时'.$m.'分钟'.$s.'秒';
        echo "OK\r\n";
        echo 'total='.$total."\r\n";
        echo 'insert='.$insert_total."\r\n";
        echo 'a='.$total_a."\r\n";
        echo 'b='.$total_b."\r\n";
        echo 'c='.$total_c."\r\n";
        echo 'd='.$total_d."\r\n";
        echo 'e='.$total_e."\r\n";
    }

    public function actionCarALib()
    {
        set_time_limit(0);
        $begin_time = time();

        $car_query = CarT::find()
            ->where('register_date>="2016-07-01" and register_date<="2016-07-31" and phone<>""');

        $total = $car_query->count();
//        $total = $car_items->count();
        echo $total."\r\n";
//        exit;
        $index = 0;
        $lines = '';
        foreach($car_query->each() as $item) {
            $index++;
            echo $index.'/'.$total.'...';
            $pos = strpos($item->insurer1_date,'-07-');
            if($pos === false) {
                echo $item->car_no.'...';
                echo $item->register_date.'...';
                echo $item->insurer1_date.'...';
                $lines .= $item->car_no."\t";
                $lines .= $item->register_date."\t";
                $lines .= $item->insurer1_date."\r\n";

                echo 'ok';
            }
            echo "\r\n";
        }
        $file_path = \Yii::getAlias('@console').'/controllers/';
        file_put_contents($file_path.'output3.csv',$lines);

        $end_time = time();
        $total_time = $end_time - $begin_time;
        $h = floor($total_time/3600);
        $m = floor(($total_time - $h * 3600)/60);
        $s = $total_time - $h * 3600 - $m * 60;
        echo '花费时间: '.$h.'小时'.$m.'分钟'.$s.'秒';
        echo "OK\r\n";
    }
}