<?php
	$con = NULL;	// $con = mysqli_connect();
	require("__include-common.php");
	require("../account/__logincheck.php");
	if ($_SESSION['システム管理者'] <> "YES") {
		exit;
	}
	if (DB_TOOL_ID_PASSWORD_USE == 'YES') {
		require("../id-manager/__include-im-login.php");
		require("../id-manager/im-logincheck.php");
	}
	define("PAGE_LINE_SELECT", "5,10,20,50,100,1000");	//頁内に表示する行数
	define("PAGE_LINE_DEFAULT", "10");			//頁内に表示する行数（デフォルト）
?>
<!DOCTYPE html>
<html lang="ja">
<head>
<meta charset="utf-8">
<meta name="author" content="ok.2nd">
<title>MySQL管理ツール：テーブル データ表示</title>
<link rel="stylesheet" href="<?= _STYLE_SHEET_FOLDER ?>/tools_common.css?20120406">
<link rel="stylesheet" href="<?= _STYLE_SHEET_FOLDER ?>/db_tools.css?20131020">
<style>
td.right {text-align:right;}
</style>
<script src="../scripts/jquery.js"></script>
<script src="../scripts/jquery.cookie.js"></script>
<script src="../scripts/ok2nd.js"></script>
</head>
<body>
<?php
	if (($dbname = $_GET['db']) == '') {
		error_exit('データベース名なし');
	}
	if (($tblname = $_GET['tb']) == '') {
		$tblname = get_first_table_name($dbname);
	}
?>
<p><?php
	page_header_return_index();
?>
<span id="tool_title">MySQL管理ツール：テーブル データ表示</span>
<a class="a_cancel_back navi_back" href='mysql-database.php'>データベース一覧に戻る</a>
<a class="a_cancel_back navi_back" href='mysql-database.php?db=<?= $dbname ?>'>テーブル一覧に戻る</a>
</p>
<?php
	$fields = get_table_field($dbname, $tblname);
	print_table_list($dbname, $tblname, $fields);
?>
<div id="footer">
<a href="http://ok2nd.web.fc2.com/" target="_blank" style="color:#8080ff;">Powered by ok.2nd</a>
</div>
</body>
</html>
<?php
function get_first_table_name($dbname) {
	global $con;
	$con = @mysqli_connect(_DB_SERVER, _DB_SCHEMA_USERNAME, _DB_SCHEMA_PASSWORD);
	if ($con == False) {
		error_exit('データベース接続エラー');
	}
	mysqli_set_charset($con, 'utf8');
	if (!mysqli_select_db($con, $dbname)) {
		error_exit('データベース選択エラー');
	}
	$sql = 'select TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES';
	$sql .= ' where TABLE_SCHEMA="'.$dbname.'" order by table_type, table_name';
	$rs = mysqli_query($con, $sql);
	if (!$rs) {
		error_exit($dbname.'：テーブルなし');
	}
	$rec = mysqli_fetch_array($rs);
	$tblname = $rec['TABLE_NAME'];
	mysqli_close($con);
	return 	$tblname;
}
function get_table_field($dbname, $tblname) {
	global $con;
	$con = @mysqli_connect(_DB_SERVER, _DB_SCHEMA_USERNAME, _DB_SCHEMA_PASSWORD);
	if ($con == False) {
		error_exit('データベース接続エラー');
	}
	mysqli_set_charset($con, 'utf8');
	if (!mysqli_select_db($con, $dbname)) {
		error_exit('データベース選択エラー');
	}
	$rs = mysqli_query($con, 'desc '.$tblname);
	if (!$rs) {
		error_exit($tblname.'：テーブル選択エラー');
	}
	$fields = array();
	while ($rec = mysqli_fetch_array($rs)) {
		$fields[] = array($rec['Field'], $rec['Type'], $rec['Key'], $rec['Extra']);
	}
	mysqli_close($con);
	return $fields;
}
function print_table_list($dbname, $tblname, $fields) {
	global $con;
	$colname = '';
	$c_delete_exist = False;
	$id_account_exist = False;
	$id_category_exist = False;
	$auto_increment = '';
	foreach ($fields as $field) {
		if ($colname <> '') $colname .= ',';
		$colname .= $field[0];
		if ($field[0] == 'c_delete') {
			$c_delete_exist = True;
		}
		if ($field[0] == 'id_account') {
			$id_account_exist = True;
		}
		if ($field[0] == 'id_category') {
			$id_category_exist = True;
		}
		if ($field[3] == 'auto_increment') {
			$auto_increment = $field[0];
		}
	}
	$sql = 'select ' . $colname . ' from ' . $tblname;
	if ($_GET['acc'] <> '') {
		$sql .= " where id_account = '".$_GET['acc']."'";
	}
	if ($_GET['cate'] <> '') {
		if ($_GET['acc'] <> '') {
			$sql .= ' and';
		} else {
			$sql .= ' where';
		}
		$sql .= " id_category = '".$_GET['cate']."'";
	}
	if ($_GET['ad'] == 'c') {
		if ($_SESSION['dbtool_table_sort_order'] == 'desc') {
			$_SESSION['dbtool_table_sort_order'] = 'asc';
		} else {
			$_SESSION['dbtool_table_sort_order'] = 'desc';
		}
	}
	if ($_GET['so'] <> '') {
		$sql .= ' order by '.$_GET['so'].' '.$_SESSION['dbtool_table_sort_order'];
	}
	$con = @mysqli_connect(_DB_SERVER, _DB_SCHEMA_USERNAME, _DB_SCHEMA_PASSWORD);
	if ($con == False) {
		error_exit('データベース接続エラー');
	}
	mysqli_set_charset($con, 'utf8');
	if (!mysqli_select_db($con, $dbname)) {
		error_exit('データベース選択エラー');
	}
	$sql_tbl = 'select TABLE_TYPE from INFORMATION_SCHEMA.TABLES';
	$sql_tbl .= ' where TABLE_SCHEMA="'.$dbname.'" and TABLE_NAME="'.$tblname.'"';
	$rs_tbl = mysqli_query($con, $sql_tbl);
	if (!$rs_tbl) {
		error_exit($dbname.'：テーブルなし');
	}
	$rec_tbl = mysqli_fetch_array($rs_tbl);
	$table_type = $rec_tbl['TABLE_TYPE'];
	if ($table_type == 'BASE TABLE') {
		$table_type_name = 'テーブル';
	} else {
		$table_type_name = 'ビュー';
	}
?>
	<p class="subtitle">データベース：<span class="db_name"><?= $dbname ?></span>
	<select onChange="chgFilterSelect(this, 'db', '<?= query_string_strip_multi('db,tb,so,ad,page,acc,cate') ?>')">
<?php
	$rs = mysqli_query($con, 'show databases');
	while ($rec = mysqli_fetch_array($rs)) {
		if ($rec['Database'] <> 'information_schema') {
?>
		<option value="<?= $rec['Database'] ?>"<?= $_GET['db']==$rec['Database'] ? ' selected' : '' ?>><?= $rec['Database'] ?>
<?php
		}
	}
?>
		</select>
	<span style="margin-left:6px;"><?= $table_type_name ?>：<span class="tbl_name"><?= $tblname ?></span></span>
	<select onChange="chgFilterSelect(this, 'tb', '<?= query_string_strip_multi('tb,so,ad,page,acc,cate') ?>')">
<?php
	$sqlsel = 'select TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES';
	$sqlsel .= ' where TABLE_SCHEMA="'.$dbname.'" order by table_type, table_name';
	$rssel = mysqli_query($con, $sqlsel);
	while ($rec = mysqli_fetch_array($rssel)) {
?>
		<option value="<?= $rec['TABLE_NAME'] ?>"<?= $_GET['tb']==$rec['TABLE_NAME'] ? ' selected' : '' ?>><?= $rec['TABLE_NAME'] ?>
<?php
	}
?>
		</select>
	<a href="mysql-database.php?db=<?= $dbname ?>#<?= $tblname ?>" style="margin-left:8px;">テーブル情報</a>
<?php	if ($table_type == 'BASE TABLE') { ?>
	<a href="excel-import.php?db=<?= $dbname ?>&tb=<?= $tblname?>" style="margin-left:8px;">Excelインポート</a>
	<a href="csv-import.php?db=<?= $dbname ?>&tb=<?= $tblname?>" style="margin-left:8px;">CSVインポート</a>
<?php	} ?>
	</p>
<?php	if ($id_account_exist) { ?>
	<span class="opt_filter">id_account</span>：<select onChange="chgFilterSelect(this, 'acc', '<?= query_string_strip_multi('page,acc') ?>')">
		<option value="">(全て)
<?php
	$sqlsel = 'select distinct id_account from '.$tblname.' order by id_account';
	$rssel = mysqli_query($con, $sqlsel);
	while ($rec = mysqli_fetch_array($rssel)) {
?>
		<option value="<?= $rec['id_account'] ?>"<?= $_GET['acc']==$rec['id_account'] ? ' selected' : '' ?>><?= $rec['id_account'] ?>
<?php
	}
?>
		</select>
<?	} ?>
<?php	if ($id_category_exist) { ?>
	<span class="opt_filter">id_category</span>：<select onChange="chgFilterSelect(this, 'cate', '<?= query_string_strip_multi('page,cate') ?>')">
		<option value="">(全て)
<?php
	$sqlsel = 'select distinct id_category from '.$tblname.' order by id_category';
	$rssel = mysqli_query($con, $sqlsel);
	while ($rec = mysqli_fetch_array($rssel)) {
?>
		<option value="<?= $rec['id_category'] ?>"<?= $_GET['cate']==$rec['id_category'] ? ' selected' : '' ?>><?= $rec['id_category'] ?>
<?php
	}
?>
		</select>
<?	} ?>
<script>
function csv_download(col) {
	if ($('#sjis').attr('checked')) {
		var opt = '&conv=sjis';
	} else {
		var opt = '';
	}
	location.href = "csv-download.php?db=<?= $dbname ?>&tb=<?= $tblname ?>&col="+col+opt+"&acc=<?= $_GET['acc'] ?>&cate=<?= $_GET['cate'] ?>&so=<?= $_GET['so'] ?>";
	return false;
}
</script>
<button onClick="csv_download('')" style="margin-left:10px;">CSVダウンロード</button>
<?php	if ($auto_increment <> '') { ?>
	<button onClick="csv_download('<?= $auto_increment ?>')" style="margin-left:10px;">CSVダウンロード(auto_increment列除)</button>
<?php	} ?>
	<label style="margin-left:10px;"><input id="sjis" type="checkbox" value="on" onClick="chgCookieCheckOnOff(this, 'mysql_csv_download_sjis')"<?= $_COOKIE['mysql_csv_download_sjis'] == 'on' ? ' checked' : '' ?>>CSVファイル→Shift-JIS変換</label>
<?php
	$rs = my_mysqli_query_debug_print($sql);
	$rowcnt = mysqli_num_rows($rs);
	if ($rowcnt > 0) {
		if ($table_type == 'BASE TABLE' and $c_delete_exist) {
			$c_delete_999 = True;
		} else {
			$c_delete_999 = False;
		}
		table_list_view($dbname, $tblname, $fields, $rs, $rowcnt, $c_delete_999);
	} else {
		error_msg('このテーブルにデータはありません。');
	}
	mysqli_close($con);
}
function table_list_view($dbname, $tblname, $fields, $rs, $rowcnt, $c_delete_999) {
	global $con;
	if ($_GET['pl'] <> '') {
		$_SESSION['my_sql_table_list_pageline'] = $_GET['pl'];
	}
	if ($_SESSION['my_sql_table_list_pageline'] == '') {
		$_SESSION['my_sql_table_list_pageline'] = PAGE_LINE_DEFAULT;
	}
	$pageline = $_SESSION['my_sql_table_list_pageline'];
	if ($_GET['page'] <> '') {
		$page = $_GET['page'];
		if ($page < 1) {
			$page = 1;
		}
	} else {
		$page = 1;
	}
	$startline = ($page - 1) * $pageline;				//開始行を算出
	$endline = $page * $pageline -1;				//終了行を算出
	$pagemax = ceil($rowcnt / $pageline);				//取得した行数から最終頁を算出
	mysqli_data_seek($rs, $startline);
	list_page_control($rowcnt, $page, $pagemax, $pageline, $c_delete_999, $dbname, $tblname);
?>
	<table class="db_table" cellspacing=1>
		<tr>
<?php
		foreach ($fields as $field) {
			if ($field[2] == 'PRI') {
				$style = ' style="background-color:#ff8000;"';
			} else {
				$style = '';
			}
?>
			<th<?= $style ?>><a class="a_field_name" href="?db=<?= $dbname ?>&tb=<?= $tblname ?>&so=<?= $field[0] ?>&ad=c&acc=<?= $_GET['acc'] ?>&cate=<?= $_GET['cate'] ?>"><?= $field[0] ?></a></th>
<?php
		}
?>
		</tr>
<?php
	$line = $startline;
	while ($rec=mysqli_fetch_array($rs) and $line++<=$endline) {
?>
		<tr>
<?php
		foreach ($fields as $field) {
?>
			<td<? if (stripos($field[1],'int') !== False or stripos($field[1],'decimal') !== False) echo ' class="right"' ?>><?= my_htmlspecialchars($rec[$field[0]]) ?></td>
<?php		}
?>
		</tr>
<?php
	}
?>
	</table>
<?php
}
function list_page_control($rowcnt, $page, $pagemax, $pageline, $c_delete_999, $dbname, $tblname) {
	$arg = 'db='.$_GET['db'].'&tb='.$_GET['tb'].'&so='.$_GET['so'].'&acc='.$_GET['acc'].'&cate='.$_GET['cate'];
?>
<div class="mp_list_page_control">
<span class="mp_list_page_control_info">
	<span class="choice">件数：<span class="mp_rowcnt"><?= $rowcnt ?></span>件</span>
	<span class="choice">頁：<span class="mp_page"><?= $page ?></span>/<span class="mp_pagemax"><?= $pagemax ?></span></span>
</span>
<span class="mp_list_page_control_move">
	<?php if ($page > 1) { ?>
		<span><a href="?<?= $arg ?>&page=1&">[←先頭]</a></span>
		<span><a href="?<?= $arg ?>&page=<?= $page-1 ?>">[←前]</a></span>
	<?php } else { ?>
		<span class="link_off">[←先頭]</span>
		<span class="link_off">[←前]</span>
	<?php } ?>
	<?php if ($page < $pagemax) { ?>
		<span><a href="?<?= $arg ?>&page=<?= $page+1 ?>">[→次]</a></span>
		<span><a href="?<?= $arg ?>&page=<?= $pagemax ?>">[→最後]</a></span>
	<?php } else { ?>
		<span class="link_off">[→次]</span>
		<span class="link_off">[→最後]</span>
	<?php } ?>
</span>
<script>
function SelectionPageLine(form, sel) {
	for (i = 0; i < sel.options.length; i++) {
		if (sel.options[i].selected == true) {
			window.location.href = '<?= $_SERVER['SCRIPT_NAME'] ?>' + '?db=<?= $_GET['db'] ?>&tb=<?= $_GET['tb'] ?>&so=<?= $_GET['so'] ?>&acc=<?= $_GET['acc'] ?>&cate=<?= $_GET['cate'] ?>' + '&pl=' + escape(sel.options[i].value);
		}
	}
}
</script>
<span style="margin-left:10px;">
頁行数：<select name="" onchange="SelectionPageLine(this.form, this)">
<?php
	$keyary = explode(",", PAGE_LINE_SELECT);
	foreach ($keyary as $keytmp) {
?>
	<option value="<?= $keytmp ?>"<?= $pageline == $keytmp ? " selected" : "" ?>><?= $keytmp ?>
<?php
	}
?>
</select>
<?php	if ($c_delete_999) { ?>
<script>
function delete999() {
	if (window.confirm('c_delete=999のデータを完全削除しますか？')) {
		location.href = "db_delete999.php?db=<?= $dbname ?>&tb=<?= $tblname ?>&so=<?= $_GET['so'] ?>&acc=<?= $_GET['acc'] ?>&cate=<?= $_GET['cate'] ?>";
	}
	return false;
}
</script>
<button onClick="delete999()" style="margin-left:10px;">c_delete=999 完全削除</button>
<?	} ?>
</span>
</div>
<?php
}
?>
