<?php

namespace app\admin\controller;

use think\Lang;
use think\Loader;
use think\Config;
use think\DB;
use dbmanage\DbManage;

class Dbmanager extends BaseController {

    protected $db;
    protected $config = array(
        'path' => './../databases/', //数据库备份路径
        'part' => 20971520, //数据库备份卷大小
        'compress' => 0, //数据库备份文件是否启用压缩 0不压缩 1 压缩
        'level' => 9 //数据库备份文件压缩级别 1普通 4 一般  9最高
    );

    // 初始化
    protected function _initialize() {
        parent::_initialize();
        $this->db = new DbManage($this->config);
        $this->db->setTimeout(3600);
        //ini_set('memory_limit', '512M');
    }

    public function index() {

        return $this->fetch();
    }

    public function lists() {
        $dbtables = $this->db->dataList();
        $total = 0;
        foreach ($dbtables as $k => $v) {
            $dbtables[$k]['size'] = format_bytes($v['data_length'] + $v['index_length'], '', 2);
            $total += $v['data_length'] + $v['index_length'];
        }
        $this->assign('list', $dbtables);
        $this->assign('total', format_bytes($total, '', 2));
        $this->assign('tablenums', count($dbtables));
        return $this->fetch();
    }

    //备份数据库
    public function backupall() {
        if ($this->request->isAjax() && $this->request->isPost()) {
            $dbtables = $this->db->dataList();
            if (!empty($dbtables)) {//备份数据表
                $file = ['name' => date('Ymd-His'), 'part' => 1]; //date('Ymd-His');
                foreach ($dbtables as $table) {
                    $this->db->setFile($file)->backup($table['name'], 0);
                }
                $this->success(Lang::get('operation successed'), url('admin/dbmanager/lists'));
            }
        }
        $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
    }

    //备份文件列表
    public function databaklist() {
        $this->assign('databaklist', $this->db->fileList());
        return $this->fetch();
    }

    //还原数据库
    public function restore() {
        if ($this->request->isAjax()) {
            $tablename = $this->request->post('tablename', '');
            if (!empty($tablename)) {
                $this->db->setFile(['1' => './../databases/' . date('Ymd-His', $tablename) . '-1.sql', 'name' => date('Ymd-His', $tablename), 'part' => 1])->import(0);
                $this->success(Lang::get('operation successed'), url('admin/dbmanager/lists'));
            }
        }
        $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
    }

    //备份下载
    public function download() {
        $tablename = $this->request->param('tablename', '');
        if (!empty($tablename)) {
            $this->db->downloadFile($tablename);
        } else {
            $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
        }
    }

    //删除备份文件
    public function delete() {
        $tablename = $this->request->param('tablename', '');
        if (!empty($tablename)) {
            $res = $this->db->delFile($tablename);
            if ($res) {
                $this->success(Lang::get('operation successed'), url('admin/dbmanager/lists'));
            } else {
                $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
            }
        } else {
            $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
        }
    }

    //备份数据表
    public function backup() {
        if ($this->request->isAjax()) {
            $tablename = $this->request->post('tablename', '');
            if (!empty($tablename)) {
                $file = ['name' => date('Ymd-His'), 'part' => 1]; //$tablename . date('Ymd-His');
                $this->db->setFile($file)->backup($tablename, 0);
                $this->success(Lang::get('operation successed'), url('admin/dbmanager/lists'));
            }
        }
        $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
    }

    //数据表优化
    public function optimize() {
        $tables = $this->request->param('tablename');
        if (!empty($tables)) {
            $this->db->optimize($tables);
            $this->success(Lang::get('operation successed'), url('admin/dbmanager/lists'));
        } else {
            $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
        }
    }

    //数据表修复
    public function repair() {
        $tables = $this->request->param('tablename');
        if (!empty($tables)) {
            $this->db->repair($tables);
            $this->success(Lang::get('operation successed'), url('admin/dbmanager/lists'));
        } else {
            $this->error(Lang::get('operation failed'), url('admin/dbmanager/lists'));
        }
    }

    public function sqlquery() {
        if ($this->request->isAjax() && $this->request->isPost()) {
            $querytype = $this->request->post('querytype');
            $sqlquery = $this->request->post('sqlquery');
            $sqlquery = trim(stripslashes($sqlquery));
            if (preg_match("#drop(.*)table#i", $sqlquery) || preg_match("#drop(.*)database#", $sqlquery) || empty($sqlquery)) {
                $this->result([], 2, "<span style='font-size:10pt'>删除 '数据表' '数据库' 或空的语句不允许在这里执行。</span>");
            }
            //运行查询语句
            if (preg_match("#^select #i", $sqlquery)) {
                $sqlqueryarr = explode(';', $sqlquery);
                if (!preg_match("#limit#i", $sqlqueryarr[0])) {
                    $sqlquery = $sqlqueryarr[0] . ' limit 0,10;';
                } else {
                    $sqlquery = $sqlqueryarr[0];
                }
                $result = DbManage::connect()->query($sqlquery);
                if (DbManage::connect()->getNumRows() <= 0) {
                    $this->result([], 2, "运行SQL：{$sqlquery}，无返回记录！");
                } else {
                    $this->result($result, 1, "运行SQL：{$sqlquery}，共有" . DbManage::connect()->getNumRows() . "条记录，最大返回10条！");
                }
            }
            if ($querytype == 2) {
                //普通的SQL语句
                $sqlquery = str_replace("\r", "", $sqlquery);
                $sqls = preg_split("#;[ \t]{0,}\n#", $sqlquery);
                $nerrCode = "";
                $i = 0;
                foreach ($sqls as $q) {
                    $q = trim($q);
                    if ($q == "") {
                        continue;
                    }
                    $number = DbManage::connect()->execute($q);
                    $errCode = trim(DbManage::connect()->getError());
                    if ($number) {
                        $i++;
                    } else {
                        $nerrCode .= "执行： <font color='blue'>$q</font> 出错，错误提示：<font color='red'>" . $errCode . "</font><br>";
                    }
                }
                $this->result([], 2, "成功执行{$i}个SQL语句！<br><br>" . $nerrCode);
            } else {
                $sqlqueryarr = explode(';', $sqlquery);
                $sqlquery = $sqlqueryarr[0];
                //$dsql->ExecuteNoneQuery($sqlquery);
                DbManage::connect()->execute($sqlquery);
                $nerrCode = trim(DbManage::connect()->getError());
                $this->result([], 2, "成功执行1个SQL语句！<br><br>" . $nerrCode);
            }
        } else {
            $this->result([], false, "数据有误,或者不存在，请检查后再操作");
        }
    }

}