You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
244 lines
8.6 KiB
244 lines
8.6 KiB
<?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";
|
|
}
|
|
} |