|
public function sqlAction($type){
$conn = $this->get('database_connection');
switch ($type) {
case 1://向省市区表插入数据省市区信息
//插入省
$info_sheng = $conn->fetchAll("SELECT * FROM db_yhm_city WHERE class_type = 1");
foreach ($info_sheng as $index => $value) {
$conn->insert("area", array('parent_id' => 0, 'name' => $value['class_name'], 'status' => 1));
$conn->insert('area_path', array('area_id' => $conn->lastInsertId(), 'level' => 0));
}
//插入市
$info_shi = $conn->fetchAll("SELECT * FROM db_yhm_city WHERE class_type = 2");
foreach ($info_shi as $index => $value) {
$name = $conn->fetchColumn("SELECT class_name FROM db_yhm_city WHERE class_id = ?", array($value['class_parent_id']));
$id = $conn->fetchColumn("SELECT id FROM area WHERE name = ?", array($name));
$conn->insert('area', array('parent_id' => $id, 'name' => $value['class_name'], 'status' => 1));
$conn->insert('area_path', array('area_id' => $conn->lastInsertId(), 'path_id' => $id, 'level' => 1));
}
//插入市区县
$info_qu = $conn->fetchAll("SELECT * FROM db_yhm_city WHERE class_type = 3");
foreach ($info_qu as $index => $value) {
$name = $conn->fetchColumn("SELECT class_name FROM db_yhm_city WHERE class_id = ?", array($value['class_parent_id']));
$id = $conn->fetchAll("SELECT id FROM area WHERE name = ?", array($name));
if(count($id) == 2){
$id[0] = $id[1];
}
$conn->insert('area', array('parent_id' => $id[0]['id'], 'name' => $value['class_name'], 'status' => 1));
$conn->insert('area_path', array('area_id' => $conn->lastInsertId(), 'path_id' => $id[0]['id'], 'level' => 2));
}
break;
case 2://向省市区表插入车牌号信息
$conn->executeUpdate("update area set short_name = ''");
// 插入省
$info_sheng = $conn->fetchAll('SELECT * FROM auto_belonging WHERE provinceid = 0');
foreach ($info_sheng as $index => $value) {
$conn->executeUpdate("update area set short_name = '".$value['citycode']."' where parent_id = 0 AND name LIKE '".substr($value['city'],0,6)."%'");
}
// 插入市(对天津上海和北京特殊处理)
echo '
';
echo '
name |
';
echo '
sql |
';
$info_shi_o = $conn->fetchAll("select path_id FROM area_path WHERE level = 1 GROUP BY path_id");
foreach($info_shi_o as $index => $value){
$q_name = $conn->fetchColumn("select short_name FROM area WHERE id = ?",array($value['path_id']));
if($q_name != '京' && $q_name != '津' && $q_name != '沪' && !empty($q_name)){
$q_id = $conn->fetchColumn("SELECT cityid FROM auto_belonging WHERE citycode = ?",array($q_name));
$q_code = $conn->fetchAll("SELECT * FROM auto_belonging WHERE provinceid = ?",array($q_id));
foreach($q_code as $i => $v){
if(!empty($v['city'])&&!empty(substr($v['citycode'], 3))) {
echo '
';
echo '
'.$v['city'].' |
>';
// echo '
'."update area set short_name = '" . mb_substr($v['citycode'], 1, 1, 'utf-8') . "' WHERE parent_id <> 0 and name LIKE '%" . mb_substr($v['city'], 0, 2, 'utf-8') . "%';".' |
>';
echo '
'."update area set short_name = '" . mb_substr($v['citycode'], 1, 1,'utf-8') . "' WHERE parent_id <> 0 and name LIKE '%" . mb_substr($v['city'],0,2,'utf-8') . "%'" . " |
";
$rows = $conn->executeUpdate("update area set short_name = '" . mb_substr($v['citycode'], 1, 1,'utf-8') . "' WHERE parent_id <> 0 and name LIKE '%" . mb_substr($v['city'],0,2,'utf-8') . "%'");
if($rows < 1){ $rows = $conn->executeUpdate("update area set short_name = '" . mb_substr($v['citycode'],1,1, 'utf-8')."' WHERE parent_id <> 0 and name LIKE '%" . mb_substr($v['city'],0,2, 'utf-8') . "%'");
if($rows < 1){
echo '
'."update area set short_name = '" . mb_substr($v['citycode'],1,1,'utf-8') . "' WHERE parent_id <> 0 and name LIKE '%" . mb_substr($v['city'], 0, 2, 'utf-8') . "%';".' |
>';
}
}
for($i = 0; $i<1000 ;$i++){
for($j = 0; $j<1000; $j++){
}
}
echo '
';
}
}
}
}
echo '
';
//说明：香港台湾澳门三个特别区的车牌规则有别于大陆，目前解决方式是有关于这三个地区的车牌信息全部留空
//首先各个省市的车牌号颜色略有差异，但主体为蓝色。
//北京[京]------直辖市
//B（出租车）
//A C E F H J K(市内一般车辆)
//G（远郊区县） 北京地区之间的车辆没有明显区别。“京“字后面的字母不代表地区
//北京的普通牌照一般是蓝色。黑牌白字的是截止今年五一前外外资企业所申请的车牌。
//红色的是使领馆的（第一个字“使”为红字，其余为黑色）。你说的“京B KD264”，这只是一个排列问题
//，先是京+字母十数字，当这个区间号用完后，将采用京+字母+字母+数字，甚至会有京+字母+字母+字母+数字。
//你所说的“北”是北京军区的，白牌，第一个汉字是红色，其余是黑色。各个军区都由军区第一个字开头。
//北京军区的车是“北”，“京”是驻京部队中除了军委（车牌为“军”）、空军（车牌为“空”）、海军（车牌为“海”）
//之外其他部门（比如驻京的研究所、院校、医院等）的车辆。 京A80,81,82,83,88是国务院，国管局，钓鱼台等牛人的车
//处理方法；在第二层存储全部的区别（读取的时候特别处理）
/************************************************************************/
//北京-天津-上海
$berjing_str = $tianjing_str = $shanghai_str = '';
$biejing = $conn->fetchAll("SELECT citycode FROM auto_belonging WHERE provinceid = 397");
$tianjing = $conn->fetchAll("SELECT citycode FROM auto_belonging WHERE provinceid = 411");
$shanghai = $conn->fetchAll("SELECT citycode FROM auto_belonging WHERE provinceid = 420");
foreach($biejing as $index => $value){
$berjing_str = $berjing_str.substr($value['citycode'],3);
}
$conn->update('area',array('short_name' => $berjing_str),array('id' => 51));
foreach($tianjing as $index => $value){
$tianjing_str = $tianjing_str.substr($value['citycode'],3);
}
$conn->update('area',array('short_name' => $tianjing_str),array('id' => 342));
foreach($shanghai as $index => $value){
$shanghai_str = $shanghai_str.substr($value['citycode'],3);
}
$conn->update('area',array('short_name' => $shanghai_str),array('id' => 320));
/*************************************************************************/
// 将县级的清空
$area = $conn->fetchAll('select area_id FROM area_path WHERE level = 2 ');
foreach($area as $index => $value){
$conn ->update('area',array( 'short_name' => ''),array('id' => $value['area_id']));
for($i = 0; $i<1000 ;$i++){
for($j = 0; $j<1000; $j++){ } } } break; } return new JsonResponse(array('data' =>array()));
}
|
