前端代码——动态数据加载
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>修改日志查询报表</title>
<style>
body, html {
width: 100%;
height: 100%;
padding: 0px;
margin: 0px;
background: #eef7fa;
}
.report_title {
height: 45px;
font-weight: bold;
font-size: 25px;
line-height: 45px;
text-align: center;
}
.layui-table thead tr {
background: #91d3f9;
color: white;
}
.layui-table-cell {
padding: 0px 3px;
}
.layui-form-select dl {
max-height: 150px;
}
</style>
</head>
<body>
<div id="app">
<!--头部标题-->
<div class="report_title">
<font color="black">修改日志查询报表</font>
</div>
<!--选择框-->
<form class="layui-form" action="">
<div class="layui-form-item">
<label class="layui-form-label">查询类型：</label>
<div class="layui-input-inline">
<select id="queryType" lay-filter="test">
<option value="1" selected="selected">常规查询</option>
<option value="2">批量导入</option>
</select>
</div>
<button type="button" id="search" class="layui-btn layui-btn-normal">搜 索</button>
<button type="button" id="export" class="layui-btn layui-btn-warm">导 出</button>
<button type="button" id="reset" class="layui-btn layui-btn-danger">清 空</button>
<span style="color:red;">* 若导出数据超100万，请分时间段导出、合并</span>
</div>
</form>
<hr style="height:10px;border:none;border-top:10px groove skyblue;">
<!--常规查询输入框-->
<form class="layui-form" id="mytable">
<div class="layui-form-item">
<label class="layui-form-label" style="margin-left:-30px;">模块：</label>
<div class="layui-input-inline">
<select id="model" class="layui-form-select dl" lay-filter="model">
<option value="">请选择一个值</option>
</select>
</div>
<label class="layui-form-label">表名称：</label>
<div class="layui-input-inline" style="width:150px;">
<select id="tableName" class="layui-form-select dl">
<option value="">请选择一个值</option>
</select>
</div>
<label class="layui-form-label" style="margin-left:-20px;">修改人：</label>
<div class="layui-input-inline" style="width:150px;">
<input type="text" id="modifier" placeholder="修改人" class="layui-input">
</div>
<label class="layui-form-label">开始时间：</label>
<div class="layui-input-inline">
<input class="layui-input" id="startTime" placeholder="开始时间" onfocus="WdatePicker({lang:'zh-cn', dateFmt:'yyyy/MM/dd HH:mm:ss'})" readonly />
</div>
<label class="layui-form-label">结束时间：</label>
<div class="layui-input-inline">
<input class="layui-input" id="endTime" placeholder="结束时间" onfocus="WdatePicker({lang:'zh-cn', dateFmt:'yyyy/MM/dd HH:mm:ss'})" readonly />
</div>
</div>
</form>
<!--批量导入输入框-->
<div id="impTable" style="display:none;">
<div class="layui-upload">
<label class="layui-form-label" style="width:110px;">批量导入:</label>
<button type="button" class="layui-btn layui-btn-normal" id="chooseFile">选择文件</button>
<button type="button" class="layui-btn" id="uploadFile">开始上传</button>
</div>
</div>
<!--表格显示-->
<table class="layui-hide" id="test"></table>
</div>
<script>
var guid = "undefined";
//加载表格table
layui.use(['table', 'form', 'upload'], function () {
var form = layui.form;
var table = layui.table;
var columns0 = [[
{ width: 50, title: '序号', type: "numbers" },
{ field: 'MODIFY_USER', width: 150, title: '修改人' },
{ field: 'MODIFY_DATE', width: 150, title: '修改时间' }
]];
table.render({
elem: '#test',
title: '修改日志查询报表',
data: [],
cols: columns0,
page: true
});
//添加
$.get("/Log/getTypes", {},
function (res) {
var response = JSON.parse(res);
if (response.code == "0" && response.count != "0") {
var resdata = new Array(response.data);
console.log(resdata)
if ((resdata[0] != null) && (resdata[0].length != 0)) {
for (var i = 0; i < resdata[0][0].length; i++) {
$("#model").append("<option value='" + i + "'>" + resdata[0][0][i].MODELTYPE + "</option>");
}
//for (var i = 0; i < resdata[0][1].length; i++) {
// $("#tableName").append("<option value='" + i + "'>" + resdata[0][1][i].TABLENAME + "</option>");
//}
form.render(); //更新全部
}
} else {
alert("无查询结果");
return;
}
}).fail(function (err) {
alert("服务器请求失败");
return;
}
);
form.on('select(model)',function (data) {
console.log(data.value);
$("#tableName").empty();
form.render("select");
var index1 = document.getElementById("model").selectedIndex;
var model1 = document.getElementById("model").options[index1].text == "请选择一个值" ? "" : document.getElementById("model").options[index1].text;
$.get("/Log/getTableTypes", { model: model1 },
function (res) {
var response = JSON.parse(res);
if (response.code == "0" && response.count != "0") {
var resdata = new Array(response.data);
console.log(resdata)
if ((resdata[0] != null) && (resdata[0][0].length != 0)) {
for (var i = 0; i < resdata[0][0].length; i++) {
$("#tableName").append("<option value='" + i + "'>" + resdata[0][0][i].TABLENAME + "</option>");
}
form.render(); //更新全部
}
} else {
alert("无查询结果");
return;
}
}).fail(function (err) {
alert("服务器请求失败");
return;
}
);
});
//select选择器的change事件
form.on('select(test)', function (data) {
if (data.value == 1) {
$("#impTable").hide();
$("#mytable").show();
guid = "undefined";
} else {
$("#impTable").show();
$("#mytable").hide();
}
});
//查询按钮点击事件
$("#search").on("click", searchData)
//导出按钮点击事件
$("#export").on("click", function () {
var index1 = document.getElementById("model").selectedIndex;
var model = document.getElementById("model").options[index1].text == "请选择一个值" ? "" : document.getElementById("model").options[index1].text;
var index2 = document.getElementById("tableName").selectedIndex;
var tableName = document.getElementById("tableName").options[index2].text == "请选择一个值" ? "" : document.getElementById("tableName").options[index2].text;
//var model = $("#model").val();
//var tableName = $("#tableName").val();
var modifier = $("#modifier").val();
var startTime = $("#startTime").val();
var endTime = $("#endTime").val();
if (guid == "undefined") {
if (model.length == 0 && tableName.length == 0 && modifier.length == 0 && startTime.length == 0 && endTime.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>请输入条件！</div>"
});
return;
}
if (tableName.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>表名称必选，不能为空！</div>"
});
return;
}
if (startTime.length == 0 && endTime.length != 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>开始时间不能为空！</div>"
});
return;
}
if (startTime.length != 0 && endTime.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>结束时间不能为空！</div>"
});
return;
}
}
layer.load(1);
$.get("/Log/getLogReport",
{
"page": "1",
"limit": "1000000",
"model": model,
"tableName": tableName,
"modifier": modifier,
"startTime": startTime,
"endTime": endTime,
"guid": guid,
"sign": new Date()
}, function (res) {
var response = JSON.parse(res)
if (response.code == "0") {
if (response.filePath == "") {
JsonToExcel(response.data, '修改日志查询报表', '', response.sheetHeader);
} else {
window.open("/common/exportFile?filePath=" + response.filePath + "&fileName=修改日志查询报表")
}
layer.closeAll('loading');
} else {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>数据导出失败</div>"
});
layer.closeAll('loading');
return;
}
}).fail(function (err) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>服务器请求失败</div>"
});
layer.closeAll('loading');
return;
});
})
});
//清空按钮点击事件
$("#reset").on("click", function () {
//$("#model").val("");
//$("#tableName").val("");
var x = document.getElementById("model");
x.options[0].selected = true;//索引从0开始
var y = document.getElementById("tableName");
y.options[0].selected = true;//索引从0开始
$("#modifier").val("");
$("#startTime").val("");
$("#endTime").val("");
guid = "undefined";
layui.form.render('select');
})
//查询事件
function searchData() {
var index1 = document.getElementById("model").selectedIndex;
var model = document.getElementById("model").options[index1].text == "请选择一个值" ? "" : document.getElementById("model").options[index1].text;
var index2 = document.getElementById("tableName").selectedIndex;
var tableName = document.getElementById("tableName").options[index2].text == "请选择一个值" ? "" : document.getElementById("tableName").options[index2].text;
//var model = $("#model").val();
//var tableName = $("#tableName").val();
var modifier = $("#modifier").val();
var startTime = $("#startTime").val();
var endTime = $("#endTime").val();
if (guid == "undefined") {
if (model.length == 0 && tableName.length == 0 && modifier.length == 0 && startTime.length == 0 && endTime.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>请输入条件</div>"
});
return;
}
if (tableName.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>表名称必选，不能为空！</div>"
});
return;
}
if (model.length != 0 && tableName.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>请选择【模块+表名称】或者单选【表名称】</div>"
});
return;
}
if (startTime.length == 0 && endTime.length != 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>开始时间不能为空</div>"
});
return;
}
if (startTime.length != 0 && endTime.length == 0) {
layer.open({
type: 1,
time: 2000,
closeBtn: 0,
skin: 'layui-layer-molv',
content: "<div style='padding:20px;'>结束时间不能为空</div>"
});
return;
}
}
layer.load(1);
$.get("/Log/getZhColumnName",
{
"model": model,
"tableName": tableName,
},
function (res) {
var response = JSON.parse(res);
if (response.code == "0" && response.count != "0") {
var resdata = new Array(response.data);
if ((resdata[0] != null) && (resdata[0].length != 0)) {
var colzharray = resdata[0][0].COLUMNZH.toString().split(",");
var colenarray = resdata[0][0].COLUMNEN.toString().split(",");
if (colzharray.length == colenarray.length) {
//需要表字段名及对应的中文名
var columns = [[
{ width: 50, title: '序号', type: "numbers" },
]];
for (var i = 0; i < colenarray.length; i++) {
//{ field: 'MODELTYPE', width: 150, title: '模块类型' },
var col = { field: colenarray[i].toString().trim().toUpperCase(), width: 150, title: colzharray[i].toString().trim() };
columns[0].push(col);
}
layui.table.render({
elem: '#test',
title: '修改日志查询报表',
data: [],
cols: columns,
page: true
});
var searchObj = {};
searchObj.model = model;
searchObj.tableName = tableName;
searchObj.modifier = modifier;
searchObj.startTime = startTime;
searchObj.endTime = endTime;
searchObj.guid = guid;
searchObj.sign = new Date();
layui.table.reload('test', {
url: '/log/getLogReport',
page: { curr: 1 },
where: searchObj,
done: function (res) {
layer.closeAll('loading');
}
});
}else {
alert("英文字段与中文字段的个数不相等，需维护");
return;
}
}
} else {
alert("无查询结果");
return;
}
}).fail(function (err) {
alert("服务器请求失败");
return;
});
}
</script>
</body>
</html>
后端代码
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using Newtonsoft.Json.Linq;
using ReportSystem.Util;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Mvc;
namespace ReportSystem.Controllers
{
public class LogController : Controller
{
OraHelper oracle = new OraHelper();
OraHelper oraSecondary = new OraHelper("secondary"); //创建备库的链接对象（用于查询数据）
// GET: Log
public ActionResult QueryLogReport()
{
return View();
}
//查询日志报表
public string getLogReport()
{
try
{
int page = (Convert.ToInt32(Request.QueryString["page"].ToString()) - 1) * Convert.ToInt32(Request.QueryString["limit"].ToString());
string limit = Request.QueryString["limit"].ToString();
string model = Request.QueryString["model"].ToString();
string tableName = Request.QueryString["tableName"].ToString();
string modifier = Request.QueryString["modifier"].ToString();
string startTime = Request.QueryString["startTime"].ToString();
string endTime = Request.QueryString["endTime"].ToString();
string guid = Request.QueryString["guid"].ToString();
StringBuilder condition0 = new StringBuilder();
condition0.Append(MyStringBuilder.toString(model, $@" and MODELTYPE='{model}' "));
condition0.Append(MyStringBuilder.toString(tableName, $@" and TABLENAME='{tableName}' "));
StringBuilder condition = new StringBuilder();
//condition.Append(MyStringBuilder.toString(model, $@" and MODELTYPE='{model}' "));
//condition.Append(MyStringBuilder.toString(tableName, $@" and TABLENAME='{tableName}' "));
condition.Append(MyStringBuilder.toString(modifier, $@" and MODIFY_USER='{modifier}' "));
condition.Append(MyStringBuilder.toString(startTime, $@" and MODIFY_DATE between to_date('{startTime}','yyyy-mm-dd hh24:mi:ss') and to_date('{endTime}','yyyy-mm-dd hh24:mi:ss') "));
//表头
string[] sheetHeader = new string[] { };
List<string> sheetHeader_list = new List<string>(sheetHeader);
//常规查询
if (guid.Equals("undefined"))
{
//查询表明和列明
string tableSql = $@"select logtable,columnzh from log_table_detail where 1 = 1 {condition0.ToString()}";
DataSet ds0 = oracle.GetDataSet(tableSql);
foreach (string col in ds0.Tables[0].Rows[0]["COLUMNZH"].ToString().Split(','))
{
sheetHeader_list.Add(col);
}
sheetHeader = sheetHeader_list.ToArray();
string rawSql = $@"select * from {ds0.Tables[0].Rows[0]["LOGTABLE"].ToString()} where 1 = 1 {condition.ToString()}";
//获取总数
string count = oracle.GetDataScalar<string>($@"SELECT COUNT(*) NUMS FROM ({rawSql})");
//获取需要的数据
DataSet ds = oracle.GetDataSet($@"SELECT * FROM ({rawSql} ORDER BY MODIFY_DATE) OFFSET {page} ROWS FETCH FIRST {limit} ROWS WITH TIES");
JObject json = new JObject();
json.Add("code", "0");
json.Add("msg", "");
json.Add("count", count);
//如果导出的数量超过3万，则使用服务器导出，否则使用浏览器导出
string filePath = string.Empty;
JArray jArray = new JArray();
if (ds.Tables[0].Rows.Count > 30000)
{
filePath = myNpoi.DataTableToExcel(ds.Tables[0], sheetHeader);
}
else
{
IsoDateTimeConverter timeFormat = new IsoDateTimeConverter();
timeFormat.DateTimeFormat = "yyyy-MM-dd HH:mm:ss";
jArray = (JArray)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ds.Tables[0], Formatting.Indented, timeFormat));
}
json.Add("filePath", filePath);
json.Add("data", jArray);
json.Add(new JProperty("sheetHeader", sheetHeader));
return json.ToString();
}
else
{
JObject json = new JObject();
json.Add("code", "1");
json.Add("count", "0");
return json.ToString();
}
}
catch (Exception ex)
{
JObject json = new JObject();
json.Add("code", "1");
json.Add("count", "0");
return json.ToString();
}
}
//得到报表的表头
public string getZhColumnName()
{
string model = Request.QueryString["model"].ToString();
string tableName = Request.QueryString["tableName"].ToString();
StringBuilder condition = new StringBuilder();
condition.Append(MyStringBuilder.toString(model, $@" and MODELTYPE='{model}' "));
condition.Append(MyStringBuilder.toString(tableName, $@" and TABLENAME='{tableName}' "));
string colSql = $@"select columnzh,columnen from log_table_detail where 1 = 1 {condition.ToString()}";
string count = oraSecondary.GetDataScalar<string>($@"SELECT COUNT(*) NUMS FROM ({colSql})");
DataSet ds = oraSecondary.GetDataSet($@"SELECT * FROM ({colSql})");
JObject json = new JObject();
json.Add("code", "0");
json.Add("count", count);
JArray jArray = new JArray();
jArray = (JArray)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ds.Tables[0]));
json.Add("data", jArray);
return json.ToString();
}
//得到模块和表类型
public string getTypes()
{
string typeSql = $@"select distinct modeltype from log_table_detail";
string count = oracle.GetDataScalar<string>($@"SELECT COUNT(*) NUMS FROM ({typeSql})");
DataSet ds = oracle.GetDataSet($@"SELECT * FROM ({typeSql})");
string typeSql2 = $@"select distinct tablename from log_table_detail";
string count2 = oracle.GetDataScalar<string>($@"SELECT COUNT(*) NUMS FROM ({typeSql2})");
DataSet ds2 = oracle.GetDataSet($@"SELECT * FROM ({typeSql2})");
JObject json = new JObject();
json.Add("code", "0");
json.Add("count", count);
JArray jArray = new JArray();
jArray.Add((JArray)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ds.Tables[0])));
jArray.Add((JArray)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ds2.Tables[0])));
json.Add("data", jArray);
return json.ToString();
}
public string getTableTypes(string model = "")
{
string typeSql = $@"select distinct tablename from log_table_detail where modeltype = '{model}'";
string count = oracle.GetDataScalar<string>($@"SELECT COUNT(*) NUMS FROM ({typeSql})");
DataSet ds = oracle.GetDataSet($@"SELECT * FROM ({typeSql})");
JObject json = new JObject();
json.Add("code", "0");
json.Add("count", count);
JArray jArray = new JArray();
jArray.Add((JArray)JsonConvert.DeserializeObject(JsonConvert.SerializeObject(ds.Tables[0])));
json.Add("data", jArray);
return json.ToString();
}
}
}
_ViewStart.cshtml
@{
//Layout = "~/Views/Shared/_Layout.cshtml";
}
<link href="~/Resources/layui/css/layui.css" rel="stylesheet" />
<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Resources/layui/layui.js"></script>
<script src="~/Resources/My97DatePicker/WdatePicker.js"></script>
<script src="~/Resources/js/JsonExportExcel.min.js"></script>
<script>
//导出数据
function JsonToExcel(jsonData, fileName, sheetName, sheetHeader) {
var option = {};
option.fileName = fileName;
option.datas = [
{
sheetData: jsonData,
sheetName: sheetName,
sheetHeader: sheetHeader,
}
];
var toExcel = new ExportJsonExcel(option);
toExcel.saveExcel();
}
function JsonToExcelNew(fileName, jsonDataOne, sheetNameOne, sheetHeaderOne, jsonDataTwo, sheetNameTwo, sheetHeaderTwo) {
var option = {};
option.fileName = fileName;
option.datas = [
{
sheetData: jsonDataOne,
sheetName: sheetNameOne,
sheetHeader: sheetHeaderOne,
},
{
sheetData: jsonDataTwo,
sheetName: sheetNameTwo,
sheetHeader: sheetHeaderTwo,
}
];
var toExcel = new ExportJsonExcel(option);
toExcel.saveExcel();
}
</script>
1458
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
