aklast/common/models/BaiduRegion.php

<?php

namespace common\models;

use common\models\Region;
use sunmoon\phpspreadsheet\Excel;

class BaiduRegion extends \common\components\ActiveRecord
{
    /**
     * {@inheritdoc}
     */
    public static function tableName()
    {
        return '{{%BAIDU_REGION}}';
    }

    public static function importXls($startRow, $limit, $offset=0){
//        $offset = 0;
        $inData = BaiduRegion::getXlsData($startRow, $limit);
        if($inData){
            $offset += $limit;
//            print_r($inData);
//            print_r("插入数据");
            BaiduRegion::batchInsert($inData);
            unset($inData);
            self::importXls($startRow+$offset, $limit);
        }
    }

    public static function getXlsData($startRow, $limit) {
        $filePath = \Yii::getAlias('@common/runtime/uploads/area.xlsx');
        $tempFileName = \Yii::getAlias('@common/runtime/uploads/' . 'importTemp.txt');
        $xlsData = Excel::import($filePath, [
            'setFirstRecordAsKeys' => true,
            'readStartRow' => $startRow + 1,
            'readEndRow' => $startRow + $limit,
            'storeFile' => $tempFileName,
            'dropKeysRow' => $startRow == 1 ? false : true,
        ]);
        $dataArray = [];
        foreach ($xlsData as $dl) {
            if ($dl['乡镇代码']) {
                $dataArray[] = [
                    'PROV_NAME' => $dl['省份名称'],
                    'PROV_CODE' => $dl['省份代码'],
                    'CITY_NAME' => $dl['城市名称'],
                    'CITY_CODE' => $dl['城市代码'],
                    'COUNTY_NAME' => $dl['区县名称'],
                    'COUNTY_CODE' => $dl['区县代码'],
                    'TOWN_NAME' => $dl['乡镇名称'],
                    'TOWN_CODE' => $dl['乡镇代码'],
                ];
            }else{
                break;
            }
        }
        return $dataArray;
    }

    public static function _getAllNcRegion() {
        $ncRegion = Region::find()->select("REGION_CODE, REGION_NAME, PID, DEEP")
            ->where("STATUS = 1 AND DEEP <= 5")
            ->orderBy("REGION_CODE")
            ->asArray()->all();
        return $ncRegion;
    }

    public static function _getBdProv() {
        $bdProv = self::find()->select('PROV_CODE,PROV_NAME')
//            ->where("CITY_CODE like :CITY_CODE", [':CITY_CODE'=>'%00'])
//            ->andWhere("PROV_CODE !=:PROV_CODE", [':PROV_CODE'=>'710000'])
            ->groupBy('PROV_CODE')
            ->asArray()->all();
        return $bdProv;
    }

    public static function _getBdCity() {
        $bdCity = self::find()->select('CITY_CODE,CITY_NAME,PROV_CODE')
            ->where("PROV_CODE <=:PROV_CODE", [':PROV_CODE'=>800000])
            ->groupBy('CITY_CODE')
            ->asArray()->all();
        return $bdCity;
    }

    public static function _getBdCounty() {
        $bdCounty = self::find()->select('COUNTY_CODE,COUNTY_NAME,CITY_CODE')
            ->where("PROV_CODE <=:PROV_CODE", [':PROV_CODE'=>800000])
            ->groupBy('COUNTY_CODE')
            ->asArray()->all();
        return $bdCounty;
    }

    public static function _getBdTown() {
        $bdTown = self::find()->select('TOWN_CODE,TOWN_NAME,COUNTY_CODE')
            ->where(["in","CITY_CODE",[
                '419001', '429004', '429005', '429006', '429021', // 济源、仙天潜神
                '441900', '442000', '460400', '469001', '620200', // 东莞、中山、儋州、嘉峪关
                '469001','469002','469005','469006','469007','469021','469022','469023','469024','469025','469026','469027','469028','469029','469030', // 海南
                '659001', '659002', '659003', '659004', '659005', '659006', '659007', '659008', '659009', // 石河子、阿拉尔、图木舒克、五家渠、北屯、铁门关、双河、可克达拉、昆玉
                '659010', '659011', // 胡杨河、新星市
            ]])
            ->asArray()->all();
        return $bdTown;
    }

    public static function _checkInNc($region_code){
        $p = Region::find()->select('REGION_CODE, REGION_NAME')
            ->where('STATUS = 1 AND REGION_CODE = :REGION_CODE', [':REGION_CODE'=>$region_code])
            ->asArray()->one();
        return $p;
    }

    public static function addNcRegion($region_code, $region_name, $pid, $deep){
        $newRegion = new Region();
        $newRegion->REGION_CODE = $region_code;
        $newRegion->REGION_NAME = $region_name;
        $newRegion->PID = $pid;
        $newRegion->DEEP = $deep;
        $newRegion->STATUS = 1;
        return $newRegion->save();
    }

    public static function _checkInBd($region_code, $region_name, $pid, $deep) {
        $ncOtherRegion = [
            '419001', '429004', '429005', '429006', '429021', // 济源，仙天潜神
//            '441900', '442000', '460400', '469001', '620200', // 东莞、中山、儋州、嘉峪关
            '469001','469002','469005','469006','469007','469021','469022','469023','469024','469025','469026','469027','469028','469029','469030', // 海南
            '659001', '659002', '659003', '659004', '659005', '659006', '659007', '659008', '659009', // 石河子、阿拉尔、图木舒克、五家渠、北屯、铁门关、双河、可克达拉、昆玉
            '659010', '659011', // 胡杨河、新星市
        ];
        $ret = [];
        if ($deep==2){ // 省级
            $t = BaiduRegion::find()->select('PROV_CODE, PROV_NAME')
                ->where("PROV_CODE = :PROV_CODE", [':PROV_CODE'=>$region_code])
//                ->orderBy('PROV_CODE')->groupBy('PROV_CODE')
                ->asArray()->one();
            if($t){
                $ret = [
                    'REGION_CODE' => $region_code
                ];
                $ret['REGION_NAME'] = $t['PROV_NAME'];
                $ret['PID'] = '1';
            }

        }else if($deep==3){ // 地级
            $t = BaiduRegion::find()->select('CITY_CODE, CITY_NAME, PROV_CODE')
                ->where("CITY_CODE = :CITY_CODE", [':CITY_CODE'=>$region_code])
//                ->orderBy('CITY_CODE')->groupBy('CITY_CODE')
                ->asArray()->one();
            if($t){
                $ret = [
                    'REGION_CODE' => $region_code
                ];
                $ret['REGION_NAME'] = $t['CITY_NAME'];
                $ret['PID'] = $t['PROV_CODE'];
            }

        }else if($deep==4){ // 县级
            $t = BaiduRegion::find()->select('COUNTY_CODE, COUNTY_NAME, CITY_CODE, PROV_CODE')
                ->where("COUNTY_CODE = :COUNTY_CODE", [':COUNTY_CODE'=>$region_code])
//                ->orderBy('COUNTY_CODE')->groupBy('COUNTY_CODE')
                ->asArray()->one();
            if($t){
                $ret = [
                    'REGION_CODE' => $region_code
                ];
                $ret['REGION_NAME'] = $t['COUNTY_NAME'];
                $ret['PID'] = $t['CITY_CODE'];
                if(in_array($region_code, $ncOtherRegion)){
                    $ret['PID'] = $t['PROV_CODE'];
                }
            }

        }else if($deep==5){ // 乡镇
            $t = BaiduRegion::find()->select('TOWN_CODE, TOWN_NAME, COUNTY_CODE')
                ->where("TOWN_CODE = :TOWN_CODE", [':TOWN_CODE'=>$region_code])
//                ->orderBy('COUNTY_CODE')->groupBy('COUNTY_CODE')
                ->asArray()->one();
            if($t){
                $ret = [
                    'REGION_CODE' => $region_code
                ];
                $ret['REGION_NAME'] = $t['TOWN_NAME'];
                $ret['PID'] = $t['COUNTY_CODE'];
            }

        }
        if(!$t){
            $ret = null;
        }
        return $ret;
    }

    // 构建js用到：

    public static function _getAllNcProv() {
        $ncRegion = Region::find()->select("REGION_CODE, REGION_NAME, PID, DEEP")
            ->where("STATUS = 1 AND DEEP = 2")
            ->orderBy("REGION_CODE")
            ->asArray()->all();
        return $ncRegion;
    }

    public static function _getAllNcCity() {
        $ncRegion = Region::find()->select("REGION_CODE, REGION_NAME, PID, DEEP")
            ->where("STATUS = 1 AND DEEP = 3 AND REGION_CODE!='999900'")
            ->orderBy("REGION_CODE")
            ->asArray()->all();
        return $ncRegion;
    }

    public static function _getAllNcCounty() {
        $ncRegion = Region::find()->select("REGION_CODE, REGION_NAME, PID, DEEP")
            ->where(['in', 'REGION_CODE', [
                '419001', '429004', '429005', '429006', '429021', // 济源、仙天潜神
//                '441900', '442000', '460400', '469001', '620200', // 东莞、中山、儋州、嘉峪关
                '469001','469002','469005','469006','469007','469021','469022','469023','469024','469025','469026','469027','469028','469029','469030', // 海南
                '659001', '659002', '659003', '659004', '659005', '659006', '659007', '659008', '659009', // 石河子、阿拉尔、图木舒克、五家渠、北屯、铁门关、双河、可克达拉、昆玉
                '659010', '659011', // 胡杨河、新星市
            ]])
            ->andWhere("STATUS = 1 AND DEEP = 4")
            ->orderBy("REGION_CODE")
            ->asArray()->all();
        return $ncRegion;
    }

    public static function _getAllNcCountyByCity($pid) {
        $ncRegion = Region::find()->select("REGION_CODE, REGION_NAME, PID, DEEP")
            ->where("STATUS = 1 AND PID = :PID", [':PID'=>$pid])
            ->orderBy("REGION_CODE")
            ->asArray()->all();
        return $ncRegion;
    }

    public static function _getAllNcTownByCity($pid) {
        $ncRegion = Region::find()->select("REGION_CODE, REGION_NAME, PID, DEEP")
            ->where("STATUS = 1 AND DEEP = 5 AND PID = :PID", [':PID'=>$pid])
            ->orderBy("REGION_CODE")
            ->asArray()->all();
        return $ncRegion;
    }


}