<?php
/**
 * 数据库备份控制器
 * @Copyright (C) 2017 汉潮 All rights reserved.
 * @License http://www.hanchao9999.com
 * @Author xiaogg <xiaogg@sina.cn>
 */

namespace Admin\Controller;
use Think\Controller;
class DatabaseController extends BaseController{
    protected $db = '', $datadir = '' , $startrow=0,$startfrom=0, $complete=true;
    function _initialize()
    {
		parent::_initialize();		
		$this->datadir = RUNTIME_PATH.C('DATA_BACKUP_PATH');
		if(!is_dir($this->datadir))mkdir($this->datadir,0755,true);
		$this->db=$db=D('');		
    }
    public function index()
    {
        $total=0;
		$dataList = $this->db->query("SHOW TABLE STATUS LIKE '".C('DB_PREFIX')."%'");
        foreach ($dataList as $row){
            $total += $row['data_length'];
        }
        $this->assign('totalSize', $total);
        $this->assign("dataList", $dataList);
        $this->display();
    }
    public function excuteQuery($sql='')
    {
        if(empty($sql)) {$this->error(L('do_empty'));}
        $queryType = 'INSERT|UPDATE|DELETE|REPLACE|CREATE|DROP|LOAD DATA|SELECT .* INTO|COPY|ALTER|GRANT|TRUNCATE|REVOKE|LOCK|UNLOCK';
        if (preg_match('/^\s*"?(' . $queryType . ')\s+/i', $sql)) {
            $data['result'] = $this->db->execute($sql);
            $data['type'] = 'execute';
        }else {
            $data['result'] = $this->db->query($sql);
            $data['type'] = 'query';
        }
        return $data;
    }
    public function query(){$this->display();}
    public function doquery(){
		$sqls=explode("\n",stripcslashes($_POST['sql']));        
		foreach ((array)$sqls as $sql){
			if($sql)$r =$this->excuteQuery($sql); 
		}
		if(!empty($r['result']) || $r['result']==0){
			$this->success(L('do_ok'));	
		}else{
			if($r['dberror']) $this->error(L($r['dberror']));				 
		}
    }
	public function recover(){
		$dir = new \Common\Vendor\Dir;
		$files=array();
		if(I('get.do')=='delete'){
			foreach ((array)$_POST['files'] as $r){
				unlink($this->datadir.$r);
			}$this->success(L('do_ok'));	
		}elseif(I('get.do')=='import'){
			header('Content-Type: text/html; charset=UTF-8');
			$filename = $_GET['filename'];
			$filelist = $dir->getList($this->datadir);
			foreach ((array)$filelist as $r){
				if($r=='.' || $r=='..')continue;
				$file = explode('-',basename($r));
				if($file[0] ==$filename){
					$files[]  = $r;
				}
			}
			foreach((array)$files as $file){
				//读取数据文件
				$sqldata = file_get_contents($this->datadir .$file);
				$sqlFormat = $this->sql_split($sqldata, C('DB_PREFIX'));
				foreach ((array)$sqlFormat as $sql){
						$sql = trim($sql);
						if (strstr($sql, 'CREATE TABLE')){
							preg_match('/CREATE TABLE `([^ ]*)`/', $sql, $matches);
							$ret =$this->excuteQuery($sql);
						}else{
							$ret = $this->excuteQuery($sql);
						}
				}
			$this->success(L('do_ok'));
			}
		}else{
			$filelist = $dir->getList($this->datadir);
			foreach ((array)$filelist as $r){
				if($r=='.' || $r=='..')continue;
				$filename = explode('-',basename($r));
				$files[] = array('path'=> $r,'file'=>basename($r),'name' => $filename[0], 'size' => filesize($this->datadir.$r), 'time' => filemtime($this->datadir.$r));
			}
			$this->assign('filespath',$this->datadir);
			$this->assign('files',$files);
			$this->display();
		}
	}
	public function docommand()
    {
        $tables = $_POST['tables'];
        $do= trim($_GET['do']);
		if(empty($do) || empty($tables)) $this->error(L('do_empty'));
		if($do=='show'){
			 foreach ((array)$tables as $t){
				$this->db->execute("SHOW COLUMNS FROM {$t}");
			}
		}else{
			$tables = implode(',',$tables);
			$r=$this->db->execute($do.' TABLE '.$tables);
			if(false != $r){ $this->success(L('do_ok'));}else{ $this->error($r['dbError']);}
		}
    }
	public function backup(){
		$tableid = intval($_GET['tableid']);
		$this->startfrom = intval($_GET['startfrom']);
		$sizelimit = intval($_REQUEST['sizelimit']);
		$volume = intval($_GET['volume']) + 1;
		$dataList = $this->db->query("SHOW TABLE STATUS LIKE '".C('DB_PREFIX')."%'");
		foreach ($dataList as $row){
				$table_info[$row['name']]=$row;
		}		
		$tables = S('backuptables');
		if(empty( $_POST['tables']) && empty($tables)) {	
			foreach ($dataList as $row){
				$tables[]= $row['name'];
			}
		}else{
			$tables = array();
			if(!$tableid) {
				$tables=$_POST['tables'];
				S('backuptables',$tables);
			} else {
				$tables = S('backuptables');
			}
			if( !is_array($tables) || empty($tables)) {
				$this->success(L('do_empty'));	
			}
		}
		unset($dataList);
		$sql='';
		if(!$tableid) {
				$sql .= "-- HANCHAO SQL Backup\n-- Time:".date("Y-m-d H:i:s")."\n-- http://www.hanchao9999.com \n\n";
				foreach($tables as $key=>$table) {
					$sql .= "--\n-- HANCHAO Table `$table`\n-- \n";
					$sql .= "DROP TABLE IF EXISTS `$table`;\n";
					$info = $this->db->query("SHOW CREATE TABLE  $table");					
					$sql .= str_replace(array('USING BTREE','ROW_FORMAT=DYNAMIC'),'',$info[0]['create table']).";\n";
				}
		}
		for(; $this->complete && $tableid < count($tables) && strlen($sql) + 500 < $sizelimit * 1000; $tableid++) {
			if($table_info[$tables[$tableid]]['rows']>0){
				$sql .=  $this->dumptablesql($tables[$tableid], $this->startfrom, strlen($sql),$table_info[$tables[$tableid]]['auto_increment']);
				if($this->complete) {
					$this->startfrom = 0;
				}
			}
		}
		!$this->complete && $tableid--;
		$filename = htmlspecialchars(strip_tags($_GET['filename']));
		$filename = !$filename ? 'HANCHAO_'.randStr(10).'_'.date('YmdH') : $filename;
		$filename_valume = sprintf($filename."-%s".'.sql', $volume);
		if(trim($sql)){
			$putfile=$this->datadir . $filename_valume;
			$r= file_put_contents($putfile , trim($sql));
		}
		if($tableid < count($tables) || $r){
			$this->assign ( 'waitSecond', 0);
			$urlarray=array(
						'tableid'   => $tableid,
						'startfrom' => $this->startfrom,
						'sizelimit' => $sizelimit,
						'volume'	=> $volume,
						'filename'  => $filename,
					);
			$message = $filename_valume.' File Create Ok!';
			$forward = U(CONTROLLER_NAME."/backup",$urlarray);
			$this->assign ( 'jumpUrl', $forward);
			$this->success($message);
		}else{ 
			S('backuptables',null);
			$this->assign ( 'jumpUrl', U(CONTROLLER_NAME.'/index') );
			$this->success(L('do_ok'));	
		}
	}
	public function dumptablesql($table, $startfrom = 0, $currsize = 0,$auto_increment=0) {
		$offset = 300;
		$insertsql = '';
		$sizelimit = intval($_REQUEST['sizelimit']);
		if(C('DB_PREFIX').'online'==$tbale)return '';
		$modelname=str_replace(C('DB_PREFIX'),'',$table);
		$model = M($modelname);
		$keyfield=strtolower($model->getPk());
		$rows = $offset;
		while($currsize + strlen($insertsql) + 500 < $sizelimit * 1000 && $rows == $offset) {
				if($auto_increment) {
					$selectsql = "SELECT * FROM $table WHERE $keyfield > $startfrom ORDER BY $keyfield LIMIT $offset";
				} else {
					$selectsql = "SELECT * FROM $table LIMIT $startfrom, $offset";
				}
				$tabledumped = 1;
				$row = $this->db->query($selectsql);
				$rows = count($row);
				foreach($row as $key=>$val) {
					foreach ($val as $k=>$field){
						if(is_string($field)) {
							$val[$k] = '\''. addslashes($field).'\'';
						}elseif(empty($field)){
							$val[$k] = 'NULL';
						}$val[$k]=str_replace(";","; ",$val[$k]);
					}
					if($currsize + strlen($insertsql) + 500 < $sizelimit * 1000) {
						if($auto_increment) {
							$startfrom = $row[$key][$keyfield];
						} else {
							$startfrom++;
						}
						$insertsql .= "INSERT INTO `$table` VALUES (".implode(',', $val).");\n";
					} else {
						$this->complete=false;
						break 2;
					}
				}
		}
		$this->startfrom= $startfrom;
		return $insertsql;
	}
    private function sql_split($sql, $tablepre) {
    	if ($tablepre != "hanchao_") {
    		$sql = str_replace("hanchao_", $tablepre, $sql);
    	}
    	$sql = preg_replace("/TYPE=(InnoDB|MyISAM|MEMORY)( DEFAULT CHARSET=[^; ]+)?/", "ENGINE=\\1 DEFAULT CHARSET=utf8", $sql);
    	$sql = str_replace("ENGINE=InnoDB", "", $sql);
    	if ($r_tablepre != $s_tablepre) {
    		$sql = str_replace($s_tablepre, $r_tablepre, $sql);
    	}
    	$sql = str_replace("\r", "\n", $sql);
    	$ret = array();
    	$num = 0;
    	$queriesarray = explode(";\n", trim($sql));
    	unset($sql);
    	foreach ($queriesarray as $query) {
    		$ret[$num] = '';
    		$queries = explode("\n", trim($query));
    		$queries = array_filter($queries);
    		foreach ($queries as $query) {
    			$str1 = substr($query, 0, 1);
    			if ($str1 != '#' && $str1 != '-') {
    				$ret[$num] .= $query;
    			}
    		}
    		$num++;
    	}
    	return $ret;
    }
}
?>