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.
253 lines
9.5 KiB
253 lines
9.5 KiB
5 years ago
|
<?php
|
||
|
|
/**
|
||
|
|
* Created by PhpStorm.
|
||
|
|
* User: zengchaoxin
|
||
|
|
* Date: 2018/3/31
|
||
|
|
* Time: 上午10:48
|
||
|
|
*/
|
||
|
|
namespace console\controllers;
|
||
|
|
|
||
|
|
use common\models\CarT;
|
||
|
|
use common\models\PeerPhoneT;
|
||
|
|
use yii\console\Controller;
|
||
|
|
|
||
|
|
/**
|
||
|
|
* Class 壁虎批量数据处理代码
|
||
|
|
* @package console\controllers
|
||
|
|
*/
|
||
|
|
|
||
|
|
class BihuController extends Controller
|
||
|
|
{
|
||
|
|
/**
|
||
|
|
* 批量去掉车牌前缀其他字母
|
||
|
|
*/
|
||
|
|
public function actionCarno()
|
||
|
|
{
|
||
|
|
set_time_limit(0);
|
||
|
|
$begin_time = time();
|
||
|
|
|
||
|
|
$car_query = CarT::find()
|
||
|
|
->where('car_no like "x%"');
|
||
|
|
$total = $car_query->count();
|
||
|
|
$i = 0;
|
||
|
|
foreach($car_query->each() as $car_item) {
|
||
|
|
$i++;
|
||
|
|
echo $i.'/'.$total.'...';
|
||
|
|
echo $car_item->car_no."...";
|
||
|
|
$car_no = $car_item->car_no;
|
||
|
|
if(substr($car_no,0,1) === 'x') {
|
||
|
|
$car_no = substr($car_no,1);
|
||
|
|
echo $car_no."...";
|
||
|
|
$car_item->car_no = $car_no;
|
||
|
|
$car_item->save();
|
||
|
|
echo "ok";
|
||
|
|
}
|
||
|
|
echo "\r\n";
|
||
|
|
}
|
||
|
|
echo 'total='.$total."\r\n";
|
||
|
|
|
||
|
|
$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";
|
||
|
|
}
|
||
|
|
/**
|
||
|
|
* 批量更新车辆信息（壁虎）
|
||
|
|
*
|
||
|
|
*/
|
||
|
|
public function actionImportBihu()
|
||
|
|
{
|
||
|
|
set_time_limit(0);
|
||
|
|
$begin_time = time();
|
||
|
|
$file_path = \Yii::getAlias('@console').'/controllers/bihu';
|
||
|
|
$dh = opendir($file_path);
|
||
|
|
while($file = readdir($dh)) {
|
||
|
|
$filename = $file_path.'/'.$file;
|
||
|
|
$ext = substr($file,-4);
|
||
|
|
if($ext == 'xlsx' || $ext == '.xls') {
|
||
|
|
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_man = $cell->getValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(2, $i);
|
||
|
|
$factory_model = $cell->getValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(3, $i);
|
||
|
|
$engine_no = $cell->getValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(4, $i);
|
||
|
|
$car_frame_no = $cell->getValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(5, $i);
|
||
|
|
$register_date = $cell->getFormattedValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(6, $i);
|
||
|
|
$company = $cell->getValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(7, $i);
|
||
|
|
$insurer1_date = $cell->getFormattedValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(8, $i);
|
||
|
|
$insurer2_date = $cell->getFormattedValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(9, $i);
|
||
|
|
$id_man = $cell->getValue();
|
||
|
|
$cell = $sheet->getCellByColumnAndRow(10, $i);
|
||
|
|
$id_number = $cell->getValue();
|
||
|
|
// $car_no = sprintf("%s",$item[0]);
|
||
|
|
// $car_man = sprintf("%s",$item[1]);
|
||
|
|
// $factory_model = sprintf("%s",$item[2]);
|
||
|
|
// $engine_no = sprintf("%s",$item[3]);
|
||
|
|
// $car_frame_no = sprintf("%s",$item[4]);
|
||
|
|
// $register_date = sprintf("%s",$item[5]);
|
||
|
|
// $company = $item[6];
|
||
|
|
// $insurer1_date = sprintf("%s",$item[7]);
|
||
|
|
// $insurer2_date = sprintf("%s",$item[8]);
|
||
|
|
// $id_man = sprintf("%s",$item[9]);
|
||
|
|
// $id_number = sprintf("%s",$item[10]);
|
||
|
|
//
|
||
|
|
echo $car_no."...";
|
||
|
|
echo $register_date.'...';
|
||
|
|
echo $insurer1_date.'...';
|
||
|
|
echo $insurer2_date.'...';
|
||
|
|
$car_info = CarT::findOne(['car_no'=>$car_no]);
|
||
|
|
if(isset($car_info)) {
|
||
|
|
if($car_info->register_date > $register_date) {
|
||
|
|
echo "\r\n";
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
if($car_info->insurer1_date >= $insurer1_date || $car_info->insurer2_date >= $insurer2_date) {
|
||
|
|
echo "\r\n";
|
||
|
|
continue;
|
||
|
|
}
|
||
|
|
$car_info->car_man = $car_man;
|
||
|
|
$car_info->car_no = $car_no;
|
||
|
|
$car_info->factory_model = $factory_model;
|
||
|
|
$car_info->engine_no = $engine_no;
|
||
|
|
$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->id_man = $id_man;
|
||
|
|
$car_info->id_number = $id_number;
|
||
|
|
$car_info->company = $company;
|
||
|
|
$car_info->save();
|
||
|
|
|
||
|
|
echo "ok";
|
||
|
|
}
|
||
|
|
echo "\r\n";
|
||
|
|
}
|
||
|
|
unlink($filename);
|
||
|
|
}
|
||
|
|
}
|
||
|
|
closedir($dh);
|
||
|
|
|
||
|
|
$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";
|
||
|
|
}
|
||
|
|
/**
|
||
|
|
* 批量导出待洗车信息
|
||
|
|
*/
|
||
|
|
public function actionSumTotal()
|
||
|
|
{
|
||
|
|
set_time_limit(0);
|
||
|
|
$begin_time = time();
|
||
|
|
|
||
|
|
$car_items = CarT::find()
|
||
|
|
->where('register_date>="2016-08-01" and register_date<="2016-08-31"')
|
||
|
|
->all();
|
||
|
|
|
||
|
|
$total = count($car_items);
|
||
|
|
// $total = $car_items->count();
|
||
|
|
echo $total."\r\n";
|
||
|
|
$index = 0;
|
||
|
|
$g_index = 0;
|
||
|
|
$lines = '';
|
||
|
|
$lines2 = '';
|
||
|
|
foreach($car_items as $item) {
|
||
|
|
//5个号码以上（包括5个号码)）
|
||
|
|
$peer_count = PeerPhoneT::find()
|
||
|
|
->where('phone="'.$item->phone.'"')
|
||
|
|
->count();
|
||
|
|
if($peer_count > 0) continue;
|
||
|
|
// 公安局
|
||
|
|
$str_pos = strpos($item->car_man,'公安');
|
||
|
|
if($str_pos !== FALSE) continue;
|
||
|
|
// 座机
|
||
|
|
if(substr($item->phone,0,1) != '1') continue;
|
||
|
|
// 号码为空
|
||
|
|
if(trim($item->phone) == '') continue;
|
||
|
|
// 京AD
|
||
|
|
$str_pos = strpos($item->car_no,'京AD');
|
||
|
|
if($str_pos !== FALSE) continue;
|
||
|
|
// 京B
|
||
|
|
$str_pos = strpos($item->car_no,'京B');
|
||
|
|
if($str_pos !== FALSE) continue;
|
||
|
|
// 租赁
|
||
|
|
$str_pos = strpos($item->car_man,'租赁');
|
||
|
|
if($str_pos !== FALSE) continue;
|
||
|
|
|
||
|
|
$index++;
|
||
|
|
echo $index.'/'.$total.'...';
|
||
|
|
echo $item->car_no.'...';
|
||
|
|
if($item->insurer1_date == '' || $item->insurer1_date == '0000-00-00')
|
||
|
|
{
|
||
|
|
$g_index++;
|
||
|
|
$str_pos = FALSE;
|
||
|
|
if($item->engine_no != '' && $item->car_no != '')
|
||
|
|
$str_pos = strpos($item->engine_no,$item->car_no);
|
||
|
|
if($str_pos === FALSE && $item->car_no != '') {
|
||
|
|
$lines .= $item->car_no."\t".$item->register_date."\r\n";
|
||
|
|
} else {
|
||
|
|
$lines2 .= $item->car_no."\t".$item->register_date."\t".$item->engine_no."\t".$item->car_frame_no."\t";
|
||
|
|
// $lines2 .= $item->insurer1_date."\t".$item->insurer2_date;
|
||
|
|
$lines2 .= "\r\n";
|
||
|
|
}
|
||
|
|
}
|
||
|
|
echo $g_index.'...';
|
||
|
|
echo "\r\n";
|
||
|
|
}
|
||
|
|
$file_path = \Yii::getAlias('@console').'/controllers/';
|
||
|
|
file_put_contents($file_path.'output.csv',$lines);
|
||
|
|
file_put_contents($file_path.'output2.csv',$lines2);
|
||
|
|
|
||
|
|
$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";
|
||
|
|
}
|
||
|
|
|
||
|
|
}
|