实际工作中，用下拉列表实现数据编辑功能可以减少很多工作量，减少错误率。
原型是参考这一篇：?EasyUI 数据表格的行编辑(Row Editing in DataGrid)_easyui demo
这里继续记录一下本机实现细节，用于后面使用cat自动生成数据编辑表格中的下拉列表。
这里以“雇员”表为中的“尊称”字段为例。
1、数据库表中，要有尊称中文字段和尊称代码字段。生成数据表的时候，需要提前制作好。这在cat中就可以弄好。“尊称ID”字段名称自己定义就行。
Excel中：
?数据库中：
数据库sys_values表中：
?2、生成下拉列表json格式数据的一组代码。
入口文件暂时放在根目录下：D:\discuz_study\www220302\cat_get_values.php
<?php
/**
* [Discuz!] (C)2001-2099 Comsenz Inc.
* This is NOT a freeware, use is subject to license terms
*
* $Id: cat_sys_values_crud.php cuibq $
*/
define('APPTYPEID', 101);
define('CURSCRIPT', 'cat_sys_values');
require './source/class/class_core.php';
$discuz = C::app();
$discuz->reject_robot();
$modarray = array('cat_sys_values', 'cat_sys_values_crud');
$mod = getgpc('mod');
$mod = (empty($mod) || !in_array($mod, $modarray)) ? 'error' : $mod;
$discuz->init();
define('CURMODULE', $mod);
require DISCUZ_ROOT.'./source/module/cat/cat_sys_values_crud_list_show.php';
?>
类文件?table_cat_sys_values.php
<?php
/**
* [Discuz!] (C)2001-2099 Comsenz Inc.
* This is NOT a freeware, use is subject to license terms
* $Id: table_cat_sys_values.php
*/
if(!defined('IN_DISCUZ')) {
exit('Access Denied');
}
class table_cat_sys_values extends discuz_table
{
public function __construct() {
$this->_table = 'cat_sys_values';
$this->_pk = 'id';
parent::__construct();
}
public function fetch_all_by_displayorder() {
return DB::fetch_all('SELECT * FROM %t ORDER BY id ASC', array($this->_table), $this->_pk);
}
public function fetch_all_by_sql($start, $limit) {
return DB::fetch_all('SELECT * FROM %t ORDER BY id ASC'.DB::limit($start, $limit), array($this->_table), $this->_pk);
}
//返回json格式数据
public function fetch_json_by_sql($start, $limit, $m_conds) {
$query = DB::query('SELECT * FROM %t WHERE 1=1 '.$m_conds.' ORDER BY id ASC'.DB::limit($start, $limit), array($this->_table), $this->_pk);
$result = array();
// 记录数量
$row = DB::fetch_first('SELECT count(*) as cnt FROM %t WHERE 1=1 '.$m_conds, array($this->_table), $this->_pk); //所有记录数量
$result["total"] = intval($row['cnt']);
while($data = DB::fetch($query)){
$list[] = array(
'list_name_en' => urlencode($data['list_name_en']),
'list_name_cn' => urlencode($data['list_name_cn']),
'list_value_en' => urlencode($data['list_value_en']),
'parent_value_en' => urlencode($data['parent_value_en']),
'value_order' => urlencode($data['value_order'])
);
}
$result["rows"] = $list;
return urldecode(json_encode($result));
}
//返回用于下拉列表所用的json格式数据，不分页，
public function fetch_json_for_combobox($list_name) {
$query = DB::query('SELECT * FROM %t WHERE list_name_en=\''.$list_name.'\' ORDER BY value_order ASC', array($this->_table), $this->_pk);
$result = array();
// 记录数量
$row = DB::fetch_first('SELECT count(*) as cnt FROM %t WHERE list_name_en=\''.$list_name.'\'', array($this->_table), $this->_pk); //所有记录数量
$result["total"] = intval($row['cnt']);
while($data = DB::fetch($query)){
$list[] = array(
//'list_value_en' => urlencode($data['list_value_en']),
//'list_value_cn' => urlencode($data['list_value_cn']),
'c_zcid' => urlencode($data['list_value_en']),
'c_zc' => urlencode($data['list_value_cn']),
);
}
$result["rows"] = $list;
//return urldecode(json_encode($result));
return urldecode(json_encode($list));
}
//public function fetch_by_id_username($id, $username, $adminid = 1) {
//return DB::fetch_first('SELECT * FROM %t WHERE id=%d', array($this->_table, $id, $adminid, $username));
//}
// 根据一个id删除一条记录
public function delete_by_id($id) {
$delcnt = DB::delete($this->_table, DB::field($this->_pk, $id)); // 返回删除记录数量，若为0表示删除失败
if ($delcnt == 0) {
$arr = array ('msg' => 'wrong!', 'success' => false);
}
else {
$arr = array ('msg' => 'ok!', 'success' => true);
}
return urldecode(json_encode($arr));
}
// 根据多个id删除多条记录
// 多个待删除id字符串的格式是： __6__78
public function delete_by_mult_id($mult_del_id) {
//从第2个字符开始取至最后
$mult_del_id = substr($mult_del_id, 2);
$id_arr = explode('__', $mult_del_id);
foreach($id_arr as $value){
// 返回删除记录数量，若为0表示删除失败
$delcnt = DB::delete($this->_table, DB::field($this->_pk, $value));
}
if ($delcnt == 0) {
$res_arr = array ('msg' => 'wrong!', 'success' => false);
}
else {
$res_arr = array ('msg' => 'ok!', 'success' => true);
}
return urldecode(json_encode($res_arr));
}
// 根据id 编辑一条数据
// 是否可以换种方式？
// 参考 https://www.cnblogs.com/yehuisir/p/12830858.html
public function update_by_id($ID, $form_data) {
$ret = DB::update($this->_table, $form_data, "ID=$ID");
$logfile = fopen("log.txt", "w") or die("Unable to open file!");
fwrite($logfile, 'ret:'.$ret);
fclose($logfile);
if ($ret == 0) {
$arr = array ('msg' => 'wrong!', 'success' => false);
}
else {
$arr = array ('msg' => 'ok!', 'success' => true);
}
return urldecode(json_encode($arr));
}
// 编辑多条记录
// 此处传来的 $upd_data 是多行数据转为json格式字符串
// 参考 https://www.cnblogs.com/yehuisir/p/12830858.html
public function update_mult_rows($upd_data) {
// json字符串转为对象数组
$arr_rows = json_decode($upd_data);
//$ret = DB::update($this->_table, $arr_rows[0], "ID=$ID");
foreach($arr_rows as $obj){
//$res_str = "id:".$obj->ID." HZDQ:".$obj->C_HZDQ;
$ID = $obj->ID;
$ret = DB::update($this->_table, $obj, "ID=$ID");
}
//$ret = DB::update($this->_table, $form_data, "ID=$ID");
//$logfile = fopen("log.txt", "w") or die("Unable to open file!");
//fwrite($logfile, 'ret:'.$ret);
//fclose($logfile);
if ($ret == 0) {
$res_arr = array ('msg' => 'wrong!', 'success' => false);
}
else {
$res_arr = array ('msg' => 'ok!', 'success' => true);
}
return urldecode(json_encode($res_arr));
//return urldecode('update_mult_rows ok:'.$res_str) ;//+ strlen($upd_data);
}
// 插入一条数据
public function insert_data($form_data, $return_insert_id, $replace, $silent) {
$ret = DB::insert($this->_table, $form_data, $return_insert_id, $replace, $silent);
if ($ret == 0) {
$arr = array ('msg' => 'wrong!', 'success' => false);
}
else {
$arr = array ('msg' => 'ok!', 'success' => true);
}
return urldecode(json_encode($arr));
}
// 插入多条数据
// 传入参数 ins_data 是json字符串
public function insert_mult_rows($ins_data) {
//$ret = DB::insert($this->_table, $form_data, $return_insert_id, $replace=False, $silent=True);
// json字符串转为对象
$arr_rows = json_decode($ins_data);
foreach($arr_rows as $obj){
//$res_str = "C_HZDQ:".$obj->C_HZDQ;
//$res_str = json_encode($obj);
$ret = DB::insert($this->_table, $obj, true);
}
json字符串转为数组
//$arr_rows = json_decode($ins_data, true);
//$i = 0;
//foreach($arr_rows as $obj){
//$i = $i + 1;
数组转字符串： implode('',$obj)
//$res_str = implode('',$obj);
//$ret = DB::insert($this->_table, $obj, $return_insert_id=True, $replace=False, $silent=True);
//}
if ($ret == 0) {
$arr = array ('msg' => 'wrong!', 'success' => false);
}
else {
$arr = array ('msg' => 'ok!', 'success' => true);
}
return urldecode(json_encode($arr));
}
}
?>
模块文件?cat_sys_values_crud_list_show.php
<?php
// mod文件只能被入口文件引用，不能直接访问
if(!defined('IN_DISCUZ')) {
exit('Access Denied');
}
if ($_GET['list_name_en']){
// 文本型字段
if (trim($_GET['list_name_en']) != ''){
$list_name_en = trim($_GET['list_name_en']);
}
$get_json = C::t('cat_sys_values')->fetch_json_for_combobox($list_name_en);
echo $get_json;
exit;
}
else{
include_once template("cat/cat_sys_values_crud_list_show");
}
?>
3、表格编辑html文件
<!DOCTYPE html>
<html>
<head>
<meta charset="UTF-8">
<title>编辑：雇员_可编辑数据表格</title>
<link rel="stylesheet" type="text/css" href="../../../template/default/style/cat/themes/default/easyui.css">
<link rel="stylesheet" type="text/css" href="../../../template/default/style/cat/themes/icon.css">
<link rel="stylesheet" type="text/css" href="../../../template/default/style/cat/themes/demo.css">
<script type="text/javascript" src="../../../template/default/style/cat/jquery.min.js"></script>
<script type="text/javascript" src="../../../template/default/style/cat/jquery.easyui.min.js"></script>
</head>
<body class="easyui-layout">
<div data-options="region:'north',border:false" style="height:60px;background:#B3DFDA;padding:10px"></div>
<div data-options="region:'west',split:true,title:'查询方法举例'" style="width:150px;padding:10px;">数字指标:<br>5<br>>5<br><br>文字指标:<br>北<br>北京</div>
<div data-options="region:'south',border:false" style="height:50px;background:#A9FACD;padding:10px;">2021年3月</div>
<div data-options="region:'center',border:false" title="" >
<table id="tt" class="easyui-datagrid" title="编辑：雇员_可编辑数据表格" style="width: 100%;height: 100%"
data-options="singleSelect:true,collapsible:true,url:'gy_kbjsjbg_list_edit_entr.php?page=1',
toolbar: '#tb',
rownumbers:'true',
pagination:'true',
method: 'get',
onClickRow: onClickRow,
onLoadSuccess: function (data) {
//隐藏列
//$('#tt').datagrid('hideColumn','c_hzdq');
$('#tt').datagrid('hideColumn','c_zc');
}
">
<thead>
<tr>
<th data-options="field:'id', sortable:'true', width:100, editor:'textbox'">编号</th>
<th data-options="field:'c_zcid', sortable:'true', width:100,
formatter:function(value,row){
return row.c_zc;
},
editor:{
type:'combobox',
options:{
valueField:'c_zcid',
textField:'c_zc',
method:'get',
url:'/cat_get_values.php?list_name_en=zcid',
required:true
}
}">尊称id</th>
<th data-options="field:'c_csrq', sortable:'true', width:100, editor:'textbox'">出生日期</th>
<th data-options="field:'username', sortable:'true', width:100, editor:'textbox'">用户名</th>
</tr>
</thead>
</table>
<div id="tb" style="height:auto">
<span>编号:</span><input id="id" style="line-height:26px;border:1px solid #ccc">
<span>尊称:</span><input id="c_zc" style="line-height:26px;border:1px solid #ccc">
<span>尊称id:</span><input id="c_zcid" style="line-height:26px;border:1px solid #ccc">
<span>出生日期:</span><input id="c_csrq" style="line-height:26px;border:1px solid #ccc">
<span>用户名:</span><input id="username" style="line-height:26px;border:1px solid #ccc">
<a href="#" class="easyui-linkbutton" plain="true" onclick="doSearch()">搜索</a><br>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-add',plain:true" onclick="append()">增加</a>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-remove',plain:true" onclick="removeit()">删除</a>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-save',plain:true" onclick="accept()">保存</a>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-undo',plain:true" onclick="reject()">取消</a>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-search',plain:true" onclick="getChanges()">信息</a>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-exceldown',plain:true" onclick="exceldown()">导出</a>
<a href="javascript:void(0)" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" rel="external nofollow" class="easyui-linkbutton" data-options="iconCls:'icon-excelup',plain:true" onclick="javascript:$('#win').window('open')">导入</a>
</div>
<div id="win" class="easyui-window" closed="true" title="文件" style="width:400px; height:120px;" >
<form id="importFileForm" method="post" enctype="multipart/form-data" style="display:">
<table style="margin:5px;height:70px;">
<tr>
<td></td>
<td width="5px;"></td>
<td><input class="easyui-filebox" id="fileimport" data-options="buttonText:'选择',prompt:'请选择文件...'" name="fileimport" style="width:260px;">
</td>
<td><a id="uploadFile" class="easyui-linkbutton" data-options="iconCls:'icon-ok'" href="javascript:void(0)">上传</a></td>
</tr>
<tr>
<td colspan="4">
<label id="uploadInfo" />
</td>
</tr>
</table>
</form>
</div>
<script>
//导入文件
$("#uploadFile").click(function () {
var formData = new FormData($("#importFileForm")[0]);
//调用apicontroller后台action方法，将form数据传递给后台处理。contentType必须设置为false,否则chrome和firefox不兼容
$.ajax({
url: "gy_kbjsjbg_list_edit_entr.php?ac=imp",
type: 'POST',
data: formData,
async: false,
cache: false,
contentType: false,
processData: false,
success: function (returnInfo) {
//上传成功后将控件内容清空，并显示上传成功信息
document.getElementById('fileimport').value = null;
document.getElementById('uploadInfo').innerHTML = "<span style='color:Red'>" + returnInfo + "</span>";
},
error: function (returnInfo) {
//上传失败时显示上传失败信息
document.getElementById('uploadInfo').innerHTML = "<span style='color:Red'>" + returnInfo + "</span>";
}
});
})
</script>
<script type="text/javascript">
//编辑的行
var editIndex = undefined;
function endEditing() {
if (editIndex == undefined){return true}
if ($('#tt').datagrid('validateRow', editIndex)){
var ed = $('#tt').datagrid('getEditor', {index:editIndex,field:'c_zcid'});
var c_zc = $(ed.target).combobox('getText');
$('#tt').datagrid('getRows')[editIndex]['c_zc'] = c_zc;
$('#tt').datagrid('endEdit', editIndex);
editIndex = undefined;
return true;
} else {
return false;
}
}
//function onClickCell(index, field){
//if (editIndex != index) {
//if (endEditing()) {
//$('#tt').datagrid('selectRow', index)
//.datagrid('beginEdit', index);
//var ed = $('#tt').datagrid('getEditor', { index: index, field: field });
//if (ed) {
//($(ed.target).data('textbox') ? $(ed.target).textbox('textbox') : $(ed.target)).focus();
//}
//editIndex = index;
//} else {
//setTimeout(function () {
//$('#tt').datagrid('selectRow', editIndex);
//}, 0);
//}
//}
//}
//function onEndEdit(index, row){
//var ed = $(this).datagrid('getEditor', {
//index: index,
//field: 'productid'
//});
//row.productname = $(ed.target).combobox('getText');
//}
function onClickRow(index){
if (editIndex != index){
if (endEditing()){
$('#tt').datagrid('selectRow', index)
.datagrid('beginEdit', index);
editIndex = index;
} else {
$('#tt').datagrid('selectRow', editIndex);
}
}
}
// 导出 excel http://localhost/cat_cat_dd_crud_entr_edit.php?ac=exc
function exceldown(){
window.location.href = 'http://localhost/cat_entr/gy_kbjsjbg_list_edit_entr.php?ac=exc';
}
function append(){
var index = $('#tt').datagrid('getRowIndex', $('#tt').datagrid('getSelected'));
if (index == -1)
index = 0;
$("#tt").datagrid("insertRow", {
index: index+1,
row: {}
//row: {oper: "<a href='javascript:append()'>+<a> <a href='javascript:removeit()'>-<a>",status:'P'}
});
}
function removeit(){
if (editIndex != undefined){
$('#tt').datagrid('selectRow', editIndex);
$('#tt').datagrid('cancelEdit', editIndex)
.datagrid('deleteRow', editIndex);
editIndex = undefined;
}
}
function accept(){
if (endEditing()){
var dg = $('#tt');
var rows = dg.datagrid('getChanges');
if (rows.length) {
var inserted = dg.datagrid('getChanges', "inserted");
var deleted = dg.datagrid('getChanges', "deleted");
var updated = dg.datagrid('getChanges', "updated");
var effectRow = new Object();
if (inserted.length) {
effectRow["inserted"] = JSON.stringify(inserted);
}
if (deleted.length) {
effectRow["deleted"] = JSON.stringify(deleted);
}
if (updated.length) {
effectRow["updated"] = JSON.stringify(updated);
}
//console.log(inserted);
//console.log(deleted);
//console.log(updated);
// 删除多行 ----
if (deleted.length>0){
$.messager.confirm('请确认','您确定要删除记录?',function(r){
if (r){
// 取得待删除多行的ID字符串 ---
//jsonDataStr = JSON.stringify(deleted);
//len = jsonDataStr.length;
len = deleted.length;
str_id = '';
//arr_id = []
for (var i = 0; i < len; i++) {
str_id = str_id + '__' + deleted[i]['id'];
//arr_id[i] = deleted[i]['ID'];
};
//json_del_str = JSON.stringify(arr_id);
//console.log(str_id)
// 取得待删除多行数据的ID字符串 ===
//$.post('/cat_cat_gy_crud_entrance.php?ac=de', {"data": str_id},function(result){
$.post('gy_kbjsjbg_list_edit_entr.php?ac=del', {"del_id": str_id},function(result){
if (result.success){
$('#tt').datagrid('reload');
} else {
$.messager.show({
title: '出错啦',
msg: result.errorMsg
});
}
},'json');
}
});
}
// 删除多行 ====
// 编辑多行 ----
if (updated.length>0){
$.messager.confirm('请确认','您确定要保存编辑的记录?',function(r){
if (r){
// 取得待编辑的多行记录 ---
jsonDataStr = JSON.stringify(updated);
//len = jsonDataStr.length;
//len = deleted.length;
//str_id = '';
//arr_id = []
//for (var i = 0; i < len; i++) {
//str_id = str_id + '__' + deleted[i]['ID'];
//arr_id[i] = deleted[i]['ID'];
//};
//json_del_str = JSON.stringify(arr_id);
//console.log('aaa:' + jsonDataStr)
// 取得待编辑的多行记录 ===
//$.post('/cat_cat_gy_crud_entrance.php?ac=de', {"data": str_id},function(result){
$.post('gy_kbjsjbg_list_edit_entr.php?ac=upd', {"upd_data": jsonDataStr},function(result){
if (result.success){
$('#tt').datagrid('reload');
} else {
$.messager.show({
title: '出错啦',
msg: result.errorMsg
});
}
},'json');
}
});
}
// 编辑多行 ====
// 插入多行 ----
if (inserted.length>0){ // inserted updated
$.messager.confirm('请确认','您确定要保存新增的记录?',function(r){
if (r){
// 取得待新增的多行记录 ---
jsonDataStr = JSON.stringify(inserted);
//len = jsonDataStr.length;
//len = deleted.length;
//str_id = '';
//arr_id = []
//for (var i = 0; i < len; i++) {
//str_id = str_id + '__' + deleted[i]['ID'];
//arr_id[i] = deleted[i]['ID'];
//};
//json_del_str = JSON.stringify(arr_id);
//console.log('ins:' + jsonDataStr)
// 取得待编辑的多行记录 ===
//$.post('/cat_cat_gy_crud_entrance.php?ac=de', {"data": str_id},function(result){
$.post('gy_kbjsjbg_list_edit_entr.php?ac=ins', {"ins_data": jsonDataStr},function(result){
if (result.success){
$('#tt').datagrid('reload');
} else {
$.messager.show({
title: '出错啦',
msg: result.errorMsg
});
}
},'json');
}
});
}
// 插入多行 ====
}
}
}
function reject(){
$('#tt').datagrid('rejectChanges');
editIndex = undefined;
}
function getChanges(){
var rows = $('#tt').datagrid('getChanges');
alert(rows.length+' 行数据待保存!');
}
function contains(arr, obj) {
var i = arr.length;
while (i--) {
if (arr[i] === obj) {
return true;
}
}
return false;
}
//function updateActions(index){
//$('#tt').datagrid('updateRow',{
//index: index,
//row:{}
//});
//}
function doSearch(){
$('#tt').datagrid('load',{
id: $('#id').val(),
c_zc: $('#c_zc').val(),
c_zcid: $('#c_zcid').val(),
c_csrq: $('#c_csrq').val(),
username: $('#username').val(),
});
}
// 计算列
</script>
</div>
</body>
</html>
4、注意自动生成的时候，尊称和尊称ID两个字段都要选中。
但是尊称字段的属性要设置为以下：
?
?后面将修改cat，让它自动生成与下拉相关的部分代码。