<?php
$table = 'towns';


class Mysql
{
    // 查询语句日志
    protected static $queryLogs = array();
    protected $config;
    protected $mode = PDO::ERRMODE_EXCEPTION;
    public $lastSql = '';
    public $lastBindData = array();
    /**
     * @var PDOStatement
     */
    protected $lastStm = null;

    /**
     *
     * @var \PDO
     */
    protected $pdo;

    protected $last_error_code;

    protected $is_no_data_update = true;

    /**
     * 获取连接
     *
     * @param array $config
     *            [
     *            'host' => 127.0.0.1,
     *            'port' => 3306,
     *            'database' => db,
     *            'user' => user
     *            'password' => pass
     *            'charset' => utf8
     *            ]
     * @param array $options
     * @throws Exception
     */
    public function __construct(array $config = array(), array $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION))
    {
        $this->config = $config;
        $this->chkConf();
        $dsn = 'mysql:host=' . $this->getHost() . ';port=' . $this->getPort();

        if (version_compare(PHP_VERSION, '5.3.6', '<')) {
            if (defined('PDO::MYSQL_ATTR_INIT_COMMAND')) {
                $options[PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES ' . $this->getCharset();
            }
        } else {
            $dsn .= ';charset=' . $this->getCharset();
        }

        $links = new PDO ($dsn, $config ['user'], $config ['password'], $options);
        $links->exec("SET sql_mode = ''");
        $this->pdo = $links;
        if ($options[PDO::ATTR_ERRMODE] == PDO::ERRMODE_SILENT) {
            $this->setSilentMode();
        }
        if ($this->getDbName() != null) {
            $this->useDb($this->config['database']);
        }
    }

    /**
     * @param $db
     * @return $this
     */
    public function useDb($db)
    {
        $this->config['database'] = $db;
        $this->pdo->exec("use `$db`");
        return $this;
    }

    /**
     * 异常由MysqlPdoConn对象抛出
     */
    public function setSilentMode()
    {
        $this->mode = PDO::ERRMODE_SILENT;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
    }

    /**
     * 异常由PDO对象抛出
     */
    public function setExceptionMode()
    {
        $this->mode = PDO::ERRMODE_EXCEPTION;
        $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    }

    /**
     * 设置为TRUE。异常由PDO对象抛出
     * 默认为FALSE，异常由MysqlPdoConn对象抛出
     * @param $mode
     */
    public function setDebugMode($mode)
    {
        if ($mode) {
            $this->mode = PDO::ERRMODE_EXCEPTION;
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } else {
            $this->mode = PDO::ERRMODE_SILENT;
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
        }
    }

    /**
     * @throws Exception
     */
    private function chkConf()
    {
        $f = true;
        $f = $f && is_array($this->config);
        $f = $f && array_key_exists('host', $this->config);
        $f = $f && array_key_exists('port', $this->config);
        //$f = $f && array_key_exists('database', $this->config);
        $f = $f && array_key_exists('user', $this->config);
        $f = $f && array_key_exists('password', $this->config);
        $f = $f && array_key_exists('charset', $this->config);
        if (!$f) {
            throw new \Exception ('Malformed config.' . var_export($this->config, true) . ' --- 
				host => 127.0.0.1,
				port => 3306,
				database => db, (optional)
				user => user
				password => pass
				charset => utf8
				');
        }
    }

    public function getDbName()
    {
        return isset($this->config ['database']) ? $this->config ['database'] : null;
    }

    public function getHost()
    {
        return $this->config ['host'];
    }

    public function getPort()
    {
        return $this->config ['port'];
    }

    public function getCharset()
    {
        return $this->config ['charset'];
    }

    public function reset()
    {
        $this->lastStm = null;
        $this->lastBindData = array();
        $this->lastSql = '';
        $this->is_no_data_update = true;
    }

    protected function errno($code)
    {
        if (preg_match("/^\d+$/", $code)) {
            $err = $code;
        } else {
            $err = 500;
        }
        return $err;
    }

    /**
     *
     * 返回插入ID
     *
     * @param string $sql
     * @param array $data
     * @param array $bindType
     *            KEY和DATA一样，值为PDO:PARAM_*
     * @return int
     * @throws Exception
     */
    public function insert($sql, $data = array(), $bindType = array())
    {
        self::$queryLogs [] = $sql . " " . var_export($data, true);
        $this->reset();
        $sth = $this->pdo->prepare($sql);
        if (!$sth) {
            $error = $sth->errorInfo();
            throw new Exception ($sql . " ;BindParams:" . var_export($data, true) . implode(';', $error));
        }
        foreach ($data as $k => $v) {
            $sth->bindValue($k, $v, array_key_exists($k, $bindType) ? $bindType [$k] : \PDO::PARAM_STR);
        }
        if (@$sth->execute()) {
            $id = $this->pdo->lastInsertId();
            $this->lastStm = $sth;
            return $id;
        } else {
            $this->lastSql = $sql;
            $this->lastBindData = $data;
            if ($this->mode == PDO::ERRMODE_SILENT) {
                $error = $sth->errorInfo();
                $this->last_error_code = $error[0];
                throw new Exception (
                    $sql . " ;BindParams:" . var_export($data, true) . implode(';', $error),
                    $this->errno($error[0])
                );
            }
        }
    }

    /**
     * @param $sql
     * @param array $data
     * @param array $bindType
     * @return mixed|null
     * @throws Exception
     */
    public function scalar($sql, $data = array(), $bindType = array())
    {
        $this->reset();
        $sth = $this->pdo->prepare($sql);
        self::$queryLogs [] = $sql . " " . var_export($data, true);
        if (!$sth) {
            $error = $sth->errorInfo();
            throw new Exception ($sql . " ;BindParams:" . var_export($data, true) . implode(';', $error));
        }
        foreach ($data as $k => $v) {
            $sth->bindValue($k, $v, array_key_exists($k, $bindType) ? $bindType [$k] : \PDO::PARAM_STR);
        }
        $sth->setFetchMode(\PDO::FETCH_NUM);
        if (@$sth->execute()) {
            $ret = $sth->fetch();
            $this->lastStm = $sth;
            if (!is_array($ret))
                return null;
            return $ret[0];
        }
        $this->lastSql = $sql;
        $this->lastBindData = $data;
        if ($this->mode == PDO::ERRMODE_SILENT) {
            $error = $sth->errorInfo();
            $this->last_error_code = $error[0];
            throw new Exception (
                $sql . " ;BindParams:" . var_export($data, true) . implode(';', $error),
                $this->errno($error[0])
            );
        }

    }

    /**
     *
     * 返回一维数组,SQL中的结果集中的第一个元组
     *
     * @param string $sql
     * @param array $data
     * @param array $bindType
     * @param int $fetch_mode
     * @return array ;
     * @throws Exception
     */
    public function fetch($sql, $data = array(), $bindType = array(), $fetch_mode = \PDO::FETCH_ASSOC)
    {
        $this->reset();
        $sth = $this->pdo->prepare($sql);
        self::$queryLogs [] = $sql . " " . var_export($data, true);
        if (!$sth) {
            $error = $sth->errorInfo();
            throw new Exception ($sql . " ;BindParams:" . var_export($data, true) . implode(';', $error));
        }
        foreach ($data as $k => $v) {
            $sth->bindValue($k, $v, array_key_exists($k, $bindType) ? $bindType [$k] : \PDO::PARAM_STR);
        }
        $sth->setFetchMode($fetch_mode);
        if (@$sth->execute()) {
            $ret = $sth->fetch();
            $this->lastStm = $sth;
            if (!is_array($ret))
                return array();
            return $ret;
        }
        $this->lastSql = $sql;
        $this->lastBindData = $data;
        if ($this->mode == PDO::ERRMODE_SILENT) {
            $error = $sth->errorInfo();
            $this->last_error_code = $error[0];
            throw new Exception (
                $sql . " ;BindParams:" . var_export($data, true) . implode(';', $error),
                $this->errno($error[0])
            );
        }
    }

    /**
     *
     * 返回二维数组
     *
     * @param string $sql
     * @param array $data
     * @param array $bindType
     * @param int $fetch_mode
     * @return array ;
     * @throws Exception
     */
    public function fetchAll($sql, $data = array(), $bindType = array(), $fetch_mode = \PDO::FETCH_ASSOC)
    {
        $this->reset();
        $sth = $this->pdo->prepare($sql);
        self::$queryLogs [] = $sql . " " . var_export($data, true);
        if (!$sth) {
            $error = $sth->errorInfo();
            throw new Exception ($sql . " ;BindParams:" . var_export($data, true) . implode(';', $error));
        }
        foreach ($data as $k => $v) {
            $sth->bindValue($k, $v, array_key_exists($k, $bindType) ? $bindType [$k] : \PDO::PARAM_STR);
        }
        $sth->setFetchMode($fetch_mode);
        if (@$sth->execute()) {
            $r = $sth->fetchAll();
            $this->lastStm = $sth;
            return $r;
        }
        $this->lastSql = $sql;
        $this->lastBindData = $data;
        $this->lastStm = $sth;
        if ($this->mode == PDO::ERRMODE_SILENT) {
            $error = $sth->errorInfo();
            $this->last_error_code = $error[0];
            throw new Exception (
                $sql . " ;BindParams:" . var_export($data, true) . implode(';', $error),
                $this->errno($error[0])
            );
        }
    }

    /**
     *
     * 返回影响行数
     *
     * @param string $sql
     * @param array $data
     * @param array $bindType
     *            KEY和DATA一样，值为PDO:PARAM_*
     * @return int
     * @throws Exception
     */
    public function exec($sql, $data = array(), $bindType = array())
    {
        $this->reset();
        $sth = $this->pdo->prepare($sql);
        if (!$sth) {
            $error = $sth->errorInfo();
            throw new Exception ($sql . " ;BindParams:" . var_export($data, true) . implode(';', $error));
        }
        foreach ($data as $k => $v) {
            $sth->bindValue($k, $v, array_key_exists($k, $bindType) ? $bindType [$k] : \PDO::PARAM_STR);
        }
        if (@$sth->execute()) {
            $this->lastStm = $sth;
            $r = $sth->rowCount();
            if ($r == 0) {
                $this->is_no_data_update = true;
            } else {
                $this->is_no_data_update = false;
            }
            return $r;
        } else {
            $this->lastSql = $sql;
            $this->lastBindData = $data;
            if ($this->mode == PDO::ERRMODE_SILENT) {
                $error = $sth->errorInfo();
                $this->last_error_code = $error[0];
                throw new Exception (
                    $sql . " ;BindParams:" . var_export($data, true) . implode(';', $error),
                    $this->errno($error[0])
                );
            }
            $this->is_no_data_update = true;
        }
    }

    /**
     * @return bool
     * @throws Exception
     */
    public function isDuplicateEntry()
    {
        if ($this->mode != PDO::ERRMODE_SILENT) {
            throw new Exception('只能在 ERRMODE_SILENT 模式来检测');
        }
        return $this->last_error_code == '23000';
    }

    /**
     * @return bool
     */
    public function isNoDataUpdate()
    {
        return $this->is_no_data_update;
    }

    /**
     * @return bool
     * @throws Exception
     */
    public function isBindParaError()
    {
        if ($this->mode != PDO::ERRMODE_SILENT) {
            throw new Exception('只能在 ERRMODE_SILENT 模式来检测');
        }
        return $this->last_error_code == 'HY093';
    }

    public function closeStm()
    {
        if ($this->lastStm) {
            $this->lastStm->closeCursor();
        }
    }


    /**
     * 执行事务处理
     *
     * @param \Closure $closure
     *
     * @return $this
     */
    public function transaction(\Closure $closure)
    {
        try {
            $this->beginTransaction();
            // 执行事务
            $closure ();
            $this->commit();
        } catch (Exception $e) {
            // 回滚事务
            $this->rollback();
        }
        return $this;
    }

    /**
     * 开启一个事务
     *
     * @return $this
     */
    public function beginTransaction()
    {
        $this->pdo->beginTransaction();
        return $this;
    }

    /**
     * 开启事务
     *
     * @return $this
     */
    public function rollback()
    {
        $this->pdo->rollback();
        return $this;
    }

    /**
     * 开启事务
     *
     * @return $this
     */
    public function commit()
    {
        $this->pdo->commit();
        return $this;
    }

    /**
     * 获得查询SQL语句
     *
     * @return array
     */
    public function getQueryLog()
    {
        return self::$queryLogs;
    }
}

function get_html($url)
{
    if(!is_dir("./.cache"))
    {
        mkdir(".cache");
    }
    if(!is_dir("./.cache"))
    {
        exit("create cache dir failed.");
    }
    
    echo "准备抓取:$url\n";
    $cache = md5($url);
    $p = "./.cache/$cache";
    if(file_exists($p))
    {
        echo "使用缓存\n";
        return file_get_contents($p);
    } 
    else 
    {
        echo "去网上抓取\n";
        $c = file_get_contents("compress.zlib://".$url);
        //http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/
        //这个站使用的是GB2312,我们这里转换成UTF8
        $c = iconv("GBK","utf-8//IGNORE",$c);
        if(strlen($c) < 10)
        {
            exit("数据异常: $url");
        }
        file_put_contents($p,$c);
        return $c;
    }
}
$config = require "config.php";
$pdo = new Mysql($config);

if($pdo->exec("show tables like '$table'"))
{
    echo "数据库连接成功\n";
} else {
    exit("数据库连接失败");
}

fwrite(STDOUT, "清空数据库吗?输入yes清空，其它不清空 ");
// get input
$input = trim(fgets(STDIN));

if(trim($input) == "yes")
{
    $pdo->exec("truncate table $table");
}

$entry = "http://www.stats.gov.cn/tjsj/tjbz/tjyqhdmhcxhfdm/2020/";
fetch_province();

function fetch_province()
{
    global $entry,$pdo,$table;
    $type = "province";
    echo "正在抓取省数据...\n";
    
    $html = get_html($entry);
    
    if(preg_match_all("/<a href='(\d+\.html)'>([\d\D]+?)<\/a>/",$html,$m))
    {
        foreach($m[1] as $i => $u)
        {
            //把省记录写到数据库
            $sql = "
            INSERT INTO $table (`code`, `name`, `pid`, `type`)
                VALUES
                (:code, :name, :pid, :type);
            ";
            $name = strip_tags($m[2][$i]);
            $code = str_replace('.html','',$u);
            $pid = 0;
            $bind = compact('code','name','pid','type');
            if(!$pdo->exec($sql,$bind))
            {
                echo "$name 记录写入数据库失败";
                exit;
            }
            fetch_city($entry.$u,$code,$name);
        }
    }else{
        echo "省列表代码可能在2021-5-29号后更新";
        exit;
    }
    
}

function fetch_city($url,$pcode,$pname)
{
    global $entry,$pdo,$table;
    $type = "city";
    echo "正在抓取 $pname 的城市数据...\n";
    
    $html = get_html($url);
    
    if(preg_match_all("/<td><a href='(\d+\/\d+)\.html'>(\d+)<\/a><\/td><td><a href='(\d+\/\d+)\.html'>(.+?)<\/a><\/td>/",$html,$m))
    {
        // var_dump($m);exit;
        foreach($m[1] as $i => $u)
        {
            //1 => 11/1101
            //2 => 110100000000
            //3 => 11/1101
            //4 => 市辖区
            //把记录写到数据库
            $sql = "
            INSERT INTO $table (`code`, `name`, `pid`, `type`)
                VALUES
                (:code, :name, :pid, :type);
            ";
            $name = $m[4][$i];
            $code = $m[2][$i];
            $pid = $pcode;
            $bind = compact('code','name','pid','type');
            if(!$pdo->exec($sql,$bind))
            {
                echo "$name 记录写入数据库失败";
                exit;
            }
            fetch_district($entry.$u.".html",$code,$pname,$name);
        }
    }else{
        echo "城市列表代码可能在2021-5-29号后更新";
        exit;
    }
}



function fetch_district($url,$citycode,$pname,$cname)
{
    global $entry,$pdo,$table;
    $type = "district";
    echo "正在抓取 $pname / $cname 的城市区域数据...\n";
    
    $html = get_html($url);
    
    if(preg_match_all("/<td><a href='(\d+\/\d+)\.html'>(\d+)<\/a><\/td><td><a href='(\d+\/\d+)\.html'>(.+?)<\/a><\/td>/",$html,$m))
    {
        //广东省 / 东莞市 / 东城街道
        //有的地址是市下直接是镇
        if(strpos($html,'towntr') > 0)
        {
            $type = 'town';
        }

        //var_dump($m);exit;
        //1 => 01/110101
        //2 => 110101000000
        //3 => 01/110101
        //4 => 东城区
        //把记录写到数据库
        foreach($m[1] as $i => $u)
        {
            $sql = "
            INSERT INTO $table (`code`, `name`, `pid`, `type`)
                VALUES
                (:code, :name, :pid, :type);
            ";
            $name = $m[4][$i];
            $code = $m[2][$i];
            $pid = $citycode;
            $bind = compact('code','name','pid','type');
            if(!$pdo->exec($sql,$bind))
            {
                echo "$name 记录写入数据库失败";
                exit;
            }
            //echo dirname($url);exit;
            if($type == 'district')
            {
                fetch_town(dirname($url)."/".$u.".html",$code,$pname,$cname,$name);
            }
        }
    }else{
        echo "城市区域列表代码可能在2021-5-29号后更新";
        exit;
    }
}




function fetch_town($url,$districtcode,$pname,$cname,$dname)
{
    global $entry,$pdo,$table;
    $type = "town";
    echo "正在抓取 $pname / $cname / $dname 的城镇数据...\n";
    
    $html = get_html($url);
    
    if(preg_match_all("/<td><a href='(\d+\/\d+)\.html'>(\d+)<\/a><\/td><td><a href='(\d+\/\d+)\.html'>(.+?)<\/a><\/td>/",$html,$m))
    {
        // var_dump($m);exit;
        //1 => 01/110101
        //2 => 110101000000
        //3 => 01/110101
        //4 => 东城区
        //把记录写到数据库
        foreach($m[1] as $i => $u)
        {
            $sql = "
            INSERT INTO $table (`code`, `name`, `pid`, `type`)
                VALUES
                (:code, :name, :pid, :type);
            ";
            $name = $m[4][$i];
            $code = $m[2][$i];
            $pid = $districtcode;
            $bind = compact('code','name','pid','type');
            if(!$pdo->exec($sql,$bind))
            {
                echo "$name 记录写入数据库失败";
                exit;
            }
        }
    }else{
        echo "城市街道列表代码可能在2021-5-29号后更新";
        exit;
    }
}