业务场景:往往大多数时候业务人员需要特定业务场景下的数据 这个时候会找开发人员 然后开发人员去数据库查询 加大了对开发人员的依赖 如果做一个能开发人员配置 然后业务人员根据自己需求去自己查询导出数据 大大缩短沟通成本 提高工作效率
此处为原创作品 未经允许不可转载
首先数据库表设计
图片太多拆开上传
此次为功能表 日志表 用户授权表 及用户可保存上次输入参数记忆功能
最后呈现大致效果如下
附上核心代码
web前端部分
功能配置页面
<%@ Page Title="" Language="C#" AutoEventWireup="true" Inherits="ZhuBao.DiaOrderWeb.Admin.AdminBasePage" %>
<script runat="server">
public override string UrlCode { get { return "sysdatamanage"; } }
</script>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>数据管理配置</title>
<!-- 引入样式 -->
<link rel="stylesheet" href="https://unpkg.com/element-ui/lib/theme-chalk/index.css" />
<link rel="stylesheet" href="https://cdn.quilljs.com/1.3.6/quill.snow.css" />
</head>
<body>
<div id="app">
<el-row>
<el-button type="success" size="small" @click="create()" >新增</el-button>
<el-button type="primary" size="small" @click="modify()" :disabled="selected.length!=1">修改</el-button>
<el-button type="primary" size="small" @click="permission()" :disabled="selected.length!=1">功能授权</el-button>
</el-row>
<br />
<el-table :data="tableData" v-loading="loading" @selection-change="tableSelectionChange" @row-click="clickRow" @select-all="onSelectAll" ref="Table" style="width: 100%">
<el-table-column type="selection" width="55"></el-table-column>
<el-table-column prop="name" label="功能名称" align="center"></el-table-column>
<el-table-column prop="caption" label="功能描述" align="center">
<template slot-scope="scope">
<div v-html="scope.row.caption"></div>
</template>
</el-table-column>
<el-table-column prop="operatorTime" label="创建时间" align="center" :formatter="dateFormats"></el-table-column>
<el-table-column prop="isDelete"
label="启用状态"
width="100"
filter-placement="bottom-end">
<template slot-scope="scope">
<el-switch
v-model="scope.row.isDelete"
active-color="#ff4949"
inactive-color="#13ce66"
@change="changeStatus(scope.row)">
</el-switch>
</template>
</el-table-column>
</el-table>
<el-dialog title="功能配置" :visible.sync="dialogCreateVisible" width="70%" :close-on-click-modal="false">
<el-form :model="form" ref="create" :rules="createRules">
<el-form-item label="功能名称:" prop="Name" :label-width="formLabelWidth">
<el-input v-model="form.Name" autocomplete="off"></el-input>
</el-form-item>
<el-form-item label="功能描述:" :label-width="formLabelWidth">
<!-- 创建文本编辑器 -->
<div id="editor"></div>
</el-form-item>
<el-form-item label="功能类型:" prop="Type" :label-width="formLabelWidth">
<el-select v-model="form.Type" placeholder="请选择功能类型">
<%--<el-option label="新增" :value="0"></el-option>
<el-option label="删除" :value="1"></el-option>
<el-option label="修改" :value="2"></el-option>--%>
<el-option label="查询" :value="3"></el-option>
</el-select>
</el-form-item>
<el-form-item label="功能脚本:" prop="Sql" :label-width="formLabelWidth">
<el-input v-model="form.Sql" type="textarea" autocomplete="off" maxlength="1000" @blur.prevent="checkParamter()"
show-word-limit :autosize="{ minRows: 6, maxRows: 12}" placeholder="参数前面请加@前缀,例如@name,@age"></el-input>
</el-form-item>
<el-form-item label="配置参数:" :label-width="formLabelWidth" style="margin-top:50px">
<el-table :data="formParam" style="width: 100%;margin-top:-15px">
<el-table-column prop="name" label="参数名" align="center"></el-table-column>
<el-table-column prop="desc" label="参数描述" align="center">
<template slot-scope="scope">
<el-input v-model="scope.row.desc" autocomplete="off"></el-input>
</template>
</el-table-column>
<el-table-column prop="type" label="参数类型" align="center">
<template slot-scope="scope">
<el-select v-model="scope.row.type" placeholder="请选择" @change="clearInput(scope.row)">
<el-option label="String" value="String"></el-option>
<el-option label="Int" value="Int"></el-option>
<el-option label="Float" value="Float"></el-option>
<el-option label="Datetime" value="Datetime"></el-option>
<el-option label="Boolean" value="Boolean"></el-option>
<%--<el-option label="Enum" value="Enum"></el-option>--%>
</el-select>
</template>
</el-table-column>
<el-table-column prop="default" label="默认值" align="center" width="230">
<template slot-scope="scope">
<el-input v-model="scope.row.default" v-if="scope.row.type == 'String' " autocomplete="off"></el-input>
<el-input v-model="scope.row.default" v-if="scope.row.type == 'Int' " maxlength='16' show-word-limit oninput="value=value.replace(/[^0-9]/g,'')" autocomplete="off"></el-input>
<el-input v-model="scope.row.default" v-if="scope.row.type == 'Float' " maxlength='16' show-word-limit oninput="value=value.replace(/[^0-9.]/g,'')" autocomplete="off"></el-input>
<el-date-picker
v-model="scope.row.default"
align="right"
type="date"
placeholder="选择日期"
:picker-options="pickerOptions"
format="yyyy 年 MM 月 dd 日"
value-format="yyyy-MM-dd"
v-if="scope.row.type == 'Datetime' ">
</el-date-picker>
<el-checkbox v-model="scope.row.default" v-if="scope.row.type == 'Boolean' " >True</el-checkbox>
</template>
</el-table-column>
<el-table-column prop="default" label="参数长度" align="center">
<template slot-scope="scope">
<el-input v-model="scope.row.length" v-if="['String'].includes(scope.row.type)" maxlength='6' show-word-limit oninput="value=value.replace(/[^0-9]/g,'')" autocomplete="off"></el-input>
</template>
</el-table-column>
<el-table-column prop="isnull" label="可空" align="center">
<template slot-scope="scope">
<el-checkbox v-model="scope.row.isnull" v-if="scope.row.type != 'Boolean' "></el-checkbox>
</template>
</el-table-column>
</el-table>
</el-form-item>
</el-form>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogCreateVisible = false">取 消</el-button>
<el-button type="primary" @click="saveOption()">确 定</el-button>
</div>
</el-dialog>
<el-dialog title="用户授权" :visible.sync="dialogPermissionVisible" width="600" :close-on-click-modal="false">
<el-transfer
filterable
:filter-method="filterMethod"
filter-placeholder="请输入用户名称"
:titles="['未授权用户', '已授权用户']"
:right-default-checked="permissionValue"
v-model="permissionValue"
:data="permissionData">
<span slot-scope="{ option }">{{ option.loginName }}</span>
</el-transfer>
<div slot="footer" class="dialog-footer">
<el-button @click="dialogPermissionVisible = false">关 闭</el-button>
<el-button type="primary" @click="savePermission()">保 存</el-button>
</div>
</el-dialog>
</div>
<!-- 引入组件库 -->
<script src="https://unpkg.com/vue/dist/vue.js"></script>
<script src="https://unpkg.com/element-ui/lib/index.js"></script>
<script src="https://unpkg.com/axios/dist/axios.min.js"></script>
<script src="https://cdn.bootcss.com/qs/6.5.1/qs.min.js"></script>
<script src="https://unpkg.com/moment"></script>
<script src="https://cdn.quilljs.com/1.3.6/quill.js"></script>
<script>
//把axios挂载到vue上
Vue.prototype.$axios = axios;
const vm = new Vue({
el: "#app",
data() {
return {
pickerOptions: {
disabledDate(time) {
return time.getTime() > Date.now();
},
shortcuts: [{
text: '今天',
onClick(picker) {
picker.$emit('pick', new Date());
}
}, {
text: '昨天',
onClick(picker) {
const date = new Date();
date.setTime(date.getTime() - 3600 * 1000 * 24);
picker.$emit('pick', date);
}
}, {
text: '一周前',
onClick(picker) {
const date = new Date();
date.setTime(date.getTime() - 3600 * 1000 * 24 * 7);
picker.$emit('pick', date);
}
}]
},
tableData: [],
permissionForm: {},
permissionData: [],
permissionValue: [],
filterMethod(query, item) {
return item.loginName.indexOf(query) > -1;
},
selected: [],
dialogPermissionVisible: false,
dialogCreateVisible: false,
form: {
Id: 0,
Type: 3,
Sql: '',
Name: '',
Caption: ''
},
formLabelWidth: "120px",
formParam: [],
createRules: {
Name: [
{ required: true, message: '请输入功能名称', trigger: 'blur' },
{ min: 1, max: 50, message: '长度在 1 到 50 个字符' }
],
Caption: [
{ required: true, message: '请输入功能描述', trigger: 'blur' },
{ min: 1, max: 400, message: '长度在 1 到 400 个字符' }
],
Type: [
{ required: true, message: '请选择功能类型', trigger: 'blur' }
],
Sql: [
{ required: true, message: '请输入sql脚本', trigger: 'blur' }
]
},
loading: true,
options: {
theme: 'snow',
modules: {
toolbar: [
['bold', 'italic', 'underline', 'strike'],
['blockquote', 'code-block'],
[{ 'header': 1 }, { 'header': 2 }],
[{ 'list': 'ordered' }, { 'list': 'bullet' }],
[{ 'script': 'sub' }, { 'script': 'super' }],
[{ 'indent': '-1' }, { 'indent': '+1' }],
[{ 'direction': 'rtl' }],
[{ 'size': ['small', false, 'large', 'huge'] }],
[{ 'header': [1, 2, 3, 4, 5, 6, false] }],
[{ 'color': [] }, { 'background': [] }],
[{ 'font': [] }],
[{ 'align': [] }],
['clean'],
['link', 'image', 'video']
]
},
placeholder: 'Insert text here ...'
}
}
},
methods: {
tableSelectionChange(val) {
this.selected = val;
if (val.length > 1) {
this.$refs.Table.clearSelection()
this.$refs.Table.toggleRowSelection(val.pop())
}
},
query() {
let self = this;
self.$axios.get('/tools/System/SysCustomFeature/Manage.ashx?action=Gets')
.then(function (res) {
if (res.status == 200) {
if (res.data.status === 1) {
self.$message.success('查询成功');
//赋值列表数据源
self.tableData = JSON.parse(res.data.msgdata);
self.loading = false
} else {
self.$message.success('查询失败');
}
}
}).catch(res => {
self.$message.error('查询失败');
});
},
create() {
let self = this
self.reset()
self.dialogCreateVisible = true;
self.initQuill("");
},
modify() {
let self = this;
self.dialogCreateVisible = true
//赋值列表数据源
let modifyData = this.selected[0];
self.form.Id = modifyData.id
self.form.Type = modifyData.type
self.form.Sql = modifyData.sql
self.form.Name = modifyData.name
self.formParam = JSON.parse(modifyData.parameter)
self.initQuill(modifyData.caption);
},
checkParamter() {
//匹配参数
let sqlStr = this.form.Sql + " ";
let paraResult = sqlStr.match(/[@||%@][A-Za-z0-9]+(\s||%)/g);
//去除空格和@符号
if ((paraResult || '') === '') {
//this.$message({
// message: '未检测到有效的参数!',
// type: 'warning'
//});
return;
}
let tempParamList = []
paraResult.forEach(item => {
//如果存在相同参数 则不推送入数组
let replaceValue = item.replace(" ", "").replace("@", "")
if (!this.formParam.map(x => x.name).includes(replaceValue)) {
this.formParam.push({
name: replaceValue,
type: '',
desc: '',
default: '',
length: 0,
isnull: false
})
}
tempParamList.push(replaceValue)
})
this.formParam = this.formParam.filter(x => { return tempParamList.includes(x.name) })
},
saveOption() {
let self = this;
self.$refs.create.validate((valid) => {
if (valid) {
//整合保存数据模型
let saveObj = {
Id: self.form.Id,
Type: self.form.Type,
Sql: self.form.Sql,
Parameter: JSON.stringify(self.formParam),
Name: self.form.Name,
Caption: encodeURI(self.quill.container.firstChild.innerHTML)
}
//保存数据
self.$axios.post('/tools/System/SysCustomFeature/Manage.ashx?action=OperateOptionData', Qs.stringify({ json: JSON.stringify(saveObj) }))
.then(function (res) {
if (res.status == 200) {
if (res.data.status == 1) {
self.$message.success("操作成功");
//清空数据
self.reset()
//关闭弹窗
self.dialogCreateVisible = false;
//刷新数据
self.query();
}
else {
self.$message.warning(res.data.message);
}
}
}).catch(res => {
self.$message.error('操作失败');
});
}
})
},
reset() {
let self = this;
self.form = {
Id: 0,
Type: 3,
Sql: '',
Name: '',
Caption: ''
}
self.formParam = []
},
clearInput(item) {
item.default = ""
},
changeStatus(row) {
let self = this
let saveObj = {
Id: row.id,
IsDelete: row.isDelete
};
self.$axios.post('/tools/System/SysCustomFeature/Manage.ashx?action=ChangeOptionDataStatus', Qs.stringify({ json: JSON.stringify(saveObj) }))
.then(function (res) {
if (res.status == 200) {
if (res.data.status == 1) {
self.$message.success("操作成功");
}
else {
self.$message.success("操作失败");
}
}
}).catch(res => {
self.$message.error('操作失败');
});
},
permission() {
let self = this;
//显示弹窗
self.dialogPermissionVisible = true;
//获取已经授权和未授权用户数据
self.$axios.post('/tools/System/SysCustomFeature/Authorization.ashx?action=Gets', Qs.stringify({ id: self.selected[0].id }))
.then(function (res) {
if (res.status == 200) {
if (res.data.status == 1) {
let item = JSON.parse(res.data.msgdata);
let data = [];
//授权用户
item.forEach((item, index) => {
data.push({
label: item.displayName,
key: item.userId,
loginName: `${item.displayName}(${item.loginName})`
});
});
self.permissionData = data;
// 已授权用户
self.permissionValue = item.filter(item => item.hasAuthorized == true).map(x => x.userId);
self.$message.success("查询成功");
}
else {
self.$message.success("查询失败");
}
}
}).catch(res => {
self.$message.error('查询失败');
});
},
savePermission() {
let self = this;
self.$axios.post('/tools/System/SysCustomFeature/Authorization.ashx?action=PermissionFeatureToUser', Qs.stringify({ id: self.selected[0].id, userIds: self.permissionValue.join(",") }))
.then(function (res) {
if (res.status == 200) {
if (res.data.status == 1) {
self.$message.success("授权成功");
self.dialogPermissionVisible = false;
}
else {
self.$message.success("授权失败");
}
}
}).catch(res => {
self.$message.error('授权失败');
});
},
clickRow(row) {
this.$refs.Table.toggleRowSelection(row);
},
onSelectAll() {
this.$refs.Table.clearSelection()//这个是清除所有选择项,当选择全部时,清除所有选择项
},
dateFormats(row, column, style) {
let date = row[column.property];
if (date === undefined) {
return '';
}
let code = '';
switch (style) {
case 110:
code = 'YYYY-MM-DD';
break;
case 120:
code = 'YYYY-MM-DD HH:mm:ss';
break;
default:
code = 'YYYY-MM-DD';
break;
}
return moment(date).format(code);
},
initQuill(content) {
this.$nextTick(function () {
if (!this.$el.querySelector('.ql-toolbar')) {
let dom = this.$el.querySelector('#editor')
this.quill = new Quill(dom, this.options);
}
this.quill.container.firstChild.innerHTML = content
})
}
},
mounted() {
this.query();
}
})
</script>
</body>
</html>
用户执行功能页面
后端代码部分
接口+BLL层 DAL层映射的T4 EF模型
```csharp
using System;
using ZhuBao.Common;
using ZhuBao.BLL.DiaOrder;
using ZhuBao.Model.DiaOrder;
using Zhubao.Log;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using System.Collections.Generic;
using System.Linq;
using System.Data;
namespace ZhuBao.DiaOrderWeb.Tools.System.SysCustomFeature
{
///
/// 数据配置 授权相关
///
public class Authorization : AdminHandler
{
private readonly SysCustomFeatureBll sysCustomFeatureBll = new SysCustomFeatureBll();
private readonly SysCustomFeatureUserBll sysCustomFeatureUserBll = new SysCustomFeatureUserBll();
/// <summary>
/// 查询用户数据
/// </summary>
public void Gets()
{
try
{
var id = DTRequest.GetInt("id", 0);
var modelList = sysCustomFeatureUserBll.Gets(id);
Ok("查询用户数据成功", JsonConvert.SerializeObject(
modelList,
new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
}));
}
catch (Exception ex)
{
Error("查询用户数据出错", "", LogLevel.Error, ex);
}
}
/// <summary>
/// 配置用户授权
/// </summary>Split
public void PermissionFeatureToUser()
{
try
{
var id = DTRequest.GetInt("id", 0);
var userIds = DTRequest.GetString("userIds");
if (sysCustomFeatureUserBll.PermissionFeatureToUser(id, userIds.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries).Select(p => Convert.ToInt32(p)).ToList()))
{
Ok("配置用户授权成功", "");
}
else
{
Error("配置用户授权失败", "", LogLevel.Info);
}
}
catch (Exception ex)
{
Error("配置用户授权出错", "", LogLevel.Error, ex);
}
}
/// <summary>
/// 查询当前用户具有权限配置功能的数据
/// </summary>
public void GetOptionDataByUserid()
{
try
{
var modelList = sysCustomFeatureBll.GetFeatureInfoByUserid(CurrentUser.UserID);
Ok("查询当前用户具有权限配置功能的数据成功", JsonConvert.SerializeObject(
modelList,
new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
}));
}
catch (Exception ex)
{
Error("查询当前用户具有权限配置功能的数据出错", "", LogLevel.Error, ex);
}
}
}
}
using System;
using ZhuBao.Common;
using ZhuBao.BLL.DiaOrder;
using ZhuBao.Model.DiaOrder;
using Zhubao.Log;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Web;
namespace ZhuBao.DiaOrderWeb.Tools.System.SysCustomFeature
{
/// <summary>
/// 数据管理
/// </summary>
public class Manage : AdminHandler
{
private readonly SysCustomFeatureBll sysCustomFeatureBll = new SysCustomFeatureBll();
private readonly SysCustomFeatureUserBll sysCustomFeatureUserBll = new SysCustomFeatureUserBll();
/// <summary>
/// 查询配置管理数据
/// </summary>
public void Gets()
{
try
{
var modelList = sysCustomFeatureBll.Gets();
Ok("查询数据配置成功", JsonConvert.SerializeObject(
modelList,
new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
}));
}
catch (Exception ex)
{
Error("查询数据配置出错", "", LogLevel.Error, ex);
}
}
/// <summary>
/// 根据当前用户和功能id查询数据
/// </summary>
public void GetOptionDataByUserAndFeature()
{
try
{
var id = DTRequest.GetInt("id", 0);
var modelList = new SysCutomOptionDataModel()
{
OptionData = sysCustomFeatureBll.Get(id),
UserData = sysCustomFeatureUserBll.GetByUserAndFeature(id, CurrentUser.UserID)
};
Ok("根据当前用户和功能id查询数据成功", JsonConvert.SerializeObject(
modelList,
new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
}));
}
catch (Exception ex)
{
Error("根据当前用户和功能id查询数据出错", "", LogLevel.Error, ex);
}
}
/// <summary>
/// 添加/修改数据配置
/// </summary>
public void OperateOptionData()
{
try
{
var json = DTRequest.GetString("json");
var model = JsonConvert.DeserializeObject<SysCustomFeatureModel>(json);
model.Caption= HttpUtility.UrlDecode(model.Caption);
model.OperatorSysUserId = CurrentUser.UserID;
model.OperatorTime = DateTime.Now;
if (model.Id > 0)
{
if (sysCustomFeatureBll.Update(model))
{
Ok("修改数据配置成功", "");
}
else
{
Error("修改数据配置出错", "", LogLevel.Info);
}
}
else
{
var message = sysCustomFeatureBll.Add(model);
if (message.IsSuccess)
{
Ok(message.Message, "", LogLevel.Info);
}
else
{
Error(message.Message, "", LogLevel.Info);
}
}
}
catch (Exception ex)
{
Error("数据配置出错", "", LogLevel.Error, ex);
}
}
/// <summary>
/// 更改数据状态
/// </summary>
public void ChangeOptionDataStatus()
{
try
{
var json = DTRequest.GetString("json");
var model = JsonConvert.DeserializeObject<SysCustomFeatureModel>(json);
if (sysCustomFeatureBll.UpdateStatus(model))
{
Ok("更改数据状态成功", "");
}
else
{
Error("更改数据状态失败", "", LogLevel.Info, "");
}
}
catch (Exception ex)
{
Error("更改数据状态出错", "", LogLevel.Error, ex);
}
}
}
}
using System;
using ZhuBao.Common;
using ZhuBao.BLL.DiaOrder;
using ZhuBao.Model.DiaOrder;
using Zhubao.Log;
using Newtonsoft.Json;
using Newtonsoft.Json.Serialization;
using System.Collections.Generic;
using System.Linq;
using System.Data;
using System.Data.SqlClient;
namespace ZhuBao.DiaOrderWeb.Tools.System.SysCustomFeature
{
/// <summary>
/// 用户执行脚本操作相关
/// </summary>
public class PerformParameter : AdminHandler
{
private readonly SysCustomFeatureLogBll logBll = new SysCustomFeatureLogBll();
private readonly SysCustomFeatureBll sysCustomFeatureBll = new SysCustomFeatureBll();
/// <summary>
/// 执行sql脚本查询数据
/// </summary>
public void Excute()
{
int id = 0;
try
{
id = DTRequest.GetInt("id", 0);
var parameters = this.GetParameterFormRequest();
DataTable dt = sysCustomFeatureBll.Query(CurrentUser.UserID, id, parameters);
//取前50条数据
if (dt?.Rows.Count > 0)
{
dt = dt.AsEnumerable().Take(50).CopyToDataTable();
}
Ok("查询成功", JsonConvert.SerializeObject(
dt,
new JsonSerializerSettings
{
ContractResolver = new CamelCasePropertyNamesContractResolver()
}), LogLevel.Info, "执行自定义脚本" + id.ToString());
}
catch (Exception ex)
{
Error("查询出错", "", LogLevel.Error, id.ToString(), ex);
}
}
/// <summary>
/// 导出数据
/// </summary>
public void ExportExcel()
{
int id = 0;
try
{
id = DTRequest.GetInt("id", 0);
var parameters = this.GetParameterFormRequest();
DataTable dt = sysCustomFeatureBll.Query(CurrentUser.UserID, id, parameters);
string fileName = string.Format("数据导出_{0:yyyyMMddHHmmssfff}.xls", DateTime.Now);
WebPublicMethod.ExportXls(dt, fileName);
WriteLog($"执行sql脚本导出数据成功:{id}_{fileName}", LogLevel.Info);
}
catch (Exception ex)
{
Error("导出出错", "", LogLevel.Error, id.ToString(), ex);
}
}
private List<SysCustomParameterModel> GetParameterFormRequest()
{
var parameters = DTRequest.GetString("parameters");
if (string.IsNullOrEmpty(parameters))
{
return new List<SysCustomParameterModel>();
}
return JsonConvert.DeserializeObject<List<SysCustomParameterModel>>(parameters);
}
}
}
BLL层
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using ZhuBao.Common;
using ZhuBao.DAL.DiaOrder;
using ZhuBao.DAL.DiaOrder.Extensions;
using ZhuBao.Model.DiaOrder;
namespace ZhuBao.BLL.DiaOrder
{
public class SysCustomFeatureBll
{
//引用数据层
private readonly SysCustomFeatureEfDal dal = new SysCustomFeatureEfDal();
//引用业务逻辑层
private readonly SysCustomFeatureUserBll customFetureUserBll = new SysCustomFeatureUserBll();
/// <summary>
/// 根据主键获取单个配置数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public SysCustomFeatureModel Get(int id)
{
return dal.Get(id);
}
/// <summary>
/// 获取配置数据列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<SysCustomFeatureModel> Gets()
{
var predicate = Expressionable.Create<SysCustomFeatureModel>()
.ToExpression();
return dal.GetList(predicate);
}
/// <summary>
/// 根据主键获取配置数据列表
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public List<SysCustomFeatureModel> GetsById(int Id)
{
var predicate = Expressionable.Create<SysCustomFeatureModel>()
.AndIF(Id > 0, it => it.Id == Id)
.ToExpression();
return dal.GetList(predicate);
}
/// <summary>
/// 新增配置
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public SysCustomMessageModel Add(SysCustomFeatureModel model)
{
//如果数据库存在相同脚本和参数的配置 不允许添加
var message = new SysCustomMessageModel();
var dataModel = this.Gets();
dataModel.ForEach(x =>
{
if (x.Parameter == model.Parameter && x.Sql == model.Sql)
{
message.Message = "存在相同脚本和参数的配置!";
message.IsSuccess = false;
}
});
if (string.IsNullOrEmpty(message.Message))
{
message.IsSuccess = dal.Insert(model, true);
}
return message;
}
/// <summary>
/// 修改配置
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Update(SysCustomFeatureModel model)
{
return dal.Update(new SysCustomFeatureModel
{
Id = model.Id,
Type = model.Type,
Sql = model.Sql,
Parameter = model.Parameter,
Name = model.Name,
Caption = model.Caption,
OperatorSysUserId = model.OperatorSysUserId,
OperatorTime = model.OperatorTime
});
}
/// <summary>
/// 更改数据状态
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool UpdateStatus(SysCustomFeatureModel model)
{
return dal.Update(model, true, it => it.IsDelete);
}
/// <summary>
/// 根据用户id 获取具有权限的菜单详情信息
/// </summary>
/// <param name="userId"></param>
/// <returns></returns>
public List<SysCustomFeatureModel> GetFeatureInfoByUserid(int userId)
{
var fetureId = customFetureUserBll.GetFeatureByUserid(userId).Select(x => x.SysCustomFeatureId);
if (!fetureId.Any())
{
return new List<SysCustomFeatureModel>();
}
var predicate = Expressionable.Create<SysCustomFeatureModel>()
.AndIF(fetureId.Any(), it => fetureId.Contains(it.Id))
.AndIF(fetureId.Any(), it => it.IsDelete == false)
.ToExpression();
return dal.GetList(predicate);
}
/// <summary>
/// 通过参数获取实际执行的脚本
/// </summary>
/// <param name="id"></param>
/// <param name="parms"></param>
/// <param name="top"></param>
/// <param name="userId"></param>
/// <returns></returns>
public DataTable Query(int userId, int id, List<SysCustomParameterModel> parms)
{
var model = this.Get(id);
if (model == null)
{
return null;
}
var sql = this.AnalysisQuerySql(model);
var sqlParms = this.AnalysisSqlParameter(parms);
var dt = new SqlQueryBll().GetTableBySqlParameter(sql, sqlParms);
this.WriteLog(userId, model, parms, true);
return dt;
}
private string AnalysisQuerySql(SysCustomFeatureModel model)
{
return model.Sql;
}
private SqlParameter[] AnalysisSqlParameter(List<SysCustomParameterModel> parms)
{
return parms.Select(it => new SqlParameter($"@{it.Name}", it.Default)).ToArray();
}
/// <summary>
/// 记录日志
/// </summary>
/// <param name="id"></param>
private void WriteLog(int userId, SysCustomFeatureModel sysCustomFeaturemodel, List<SysCustomParameterModel> parms, bool isSuccess = true)
{
var parmsJson = JsonConvert.SerializeObject(parms);
var logModel = new SysCustomFeatureLogModel()
{
SysCustomFeatureId = sysCustomFeaturemodel.Id,
Sql = sysCustomFeaturemodel.Sql,
ParameterValue = parmsJson,
OperatorSysUserId = userId,
OperatorTime = DateTime.Now,
Remark = $"执行功能:{sysCustomFeaturemodel.Name}{(isSuccess ? "成功" : "失败")}"
};
var customFeatureUserModel = customFetureUserBll.GetCustomFetureUserModel(sysCustomFeaturemodel.Id, userId);
customFeatureUserModel.LastParameterTime = sysCustomFeaturemodel.OperatorTime;
customFeatureUserModel.LastParameterValue = parmsJson;
// false:不提交到数据库
new SysCustomFeatureLogEfDal().Insert(logModel, false);
new SysCustomFeatureUserEfDal().Update(customFeatureUserModel);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhuBao.DAL.DiaOrder;
using ZhuBao.Model.DiaOrder;
namespace ZhuBao.BLL.DiaOrder
{
public class SysCustomFeatureLogBll
{
//引用数据层
private readonly SysCustomFeatureLogEfDal dal = new SysCustomFeatureLogEfDal();
/// <summary>
/// 新增日志
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool Add(SysCustomFeatureLogModel model)
{
return dal.Insert(model, true);
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhuBao.DAL.DiaOrder;
using ZhuBao.DAL.DiaOrder.Extensions;
using ZhuBao.Model.DiaOrder;
namespace ZhuBao.BLL.DiaOrder
{
public class SysCustomFeatureUserBll
{
//引用数据层
private readonly SysCustomFeatureUserEfDal dal = new SysCustomFeatureUserEfDal();
/// <summary>
/// 根据用户id和功能id获取配置数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public SysCustomFeatureUserModel GetByUserAndFeature(int id, int userId)
{
var predicate = Expressionable.Create<SysCustomFeatureUserModel>()
.AndIF(id > 0, it => it.SysCustomFeatureId == id)
.AndIF(userId > 0, it => it.SysUserId == userId)
.ToExpression();
return dal.Get(predicate);
}
/// <summary>
/// 根据用户id和功能id获取用户配置数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public SysCustomFeatureUserModel GetCustomFetureUserModel(int id, int userId)
{
var predicate = Expressionable.Create<SysCustomFeatureUserModel>()
.AndIF(id > 0, it => it.SysCustomFeatureId == id)
.AndIF(userId > 0, it => it.SysUserId == userId)
.ToExpression();
return dal.GetList(predicate).FirstOrDefault();
}
/// <summary>
/// 获取功能模块下已经授权的用户信息
/// </summary>
public List<SysCustomFeatureUserModel> GetCustomFeatureUserInfo(int sysCustomFeatureId)
{
var predicate = Expressionable.Create<SysCustomFeatureUserModel>()
.AndIF(sysCustomFeatureId > 0, it => it.SysCustomFeatureId == sysCustomFeatureId)
.ToExpression();
return dal.GetList(predicate);
}
/// <summary>
/// 取消授权
/// </summary>
/// <param name="sysCustomFeatureId"></param>
/// <param name="userIds"></param>
/// <returns></returns>
private bool CancelPermission(int sysCustomFeatureId, List<int> userIds)
{
var list = new List<SysCustomFeatureUserModel>();
if (userIds.Any())
{
list = this.GetCustomFeatureUserInfo(sysCustomFeatureId).Where(x => userIds.Contains(x.SysUserId)).ToList();
}
else
{
list = this.GetCustomFeatureUserInfo(sysCustomFeatureId);
}
return dal.Delete(list);
}
/// <summary>
/// 新增授权
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public bool AddPermission(int sysCustomFeatureId, List<int> userIds)
{
var list = new List<SysCustomFeatureUserModel>();
userIds.ForEach(x =>
{
list.Add(new SysCustomFeatureUserModel()
{
SysCustomFeatureId = sysCustomFeatureId,
SysUserId = x
});
});
return dal.Insert(list);
}
/// <summary>
/// 根据用户id 获取具有权限的菜单
/// </summary>
/// <returns></returns>
public List<SysCustomFeatureUserModel> GetFeatureByUserid(int userId)
{
var predicate = Expressionable.Create<SysCustomFeatureUserModel>()
.AndIF(userId > 0, it => it.SysUserId == userId)
.ToExpression();
return dal.GetList(predicate);
}
/// <summary>
/// 更新用户配置功能表 备份最近一次的配置数据
/// </summary>
/// <returns></returns>
/// <summary>
public bool Update(SysCustomFeatureUserModel model)
{
return dal.Update(model, true,
it => it.LastParameterTime,
it => it.LastParameterValue);
}
/// <summary>
/// 获取未授权/已授权用户信息
/// </summary>
/// <param name="sysCustomFeatureId"></param>
/// <returns></returns>
public List<SysCustomUserModel> Gets(int sysCustomFeatureId)
{
//引用业务逻辑层
var customUserBll = new SysCustomUserBll();
var NotAuthorizedList = customUserBll.GetNotAuthorizedUser(sysCustomFeatureId);
NotAuthorizedList.ForEach(x => x.HasAuthorized = false);
var HasAuthorizedList = customUserBll.GetHasAuthorizedUser(sysCustomFeatureId);
HasAuthorizedList.ForEach(x => x.HasAuthorized = true);
return NotAuthorizedList.Union(HasAuthorizedList).ToList();
}
/// <summary>
/// 获取功能模块下已经授权的用户Id集合
/// </summary>
public List<int> GetCustomFeatureUser(int sysCustomFeatureId)
{
return this.GetCustomFeatureUserInfo(sysCustomFeatureId).Select(x => x.SysUserId).ToList();
}
/// <summary>
/// 授权功能给用户
/// </summary>
/// <returns></returns>
public bool PermissionFeatureToUser(int sysCustomFeatureId, List<int> userIds)
{
bool isSuccess = true;
//获取该模块下已授权用户
var hasAuthorizedUsers = this.GetCustomFeatureUser(sysCustomFeatureId);
//如果没有授权用户检查数据库当前模块下是否有已授权用户 如存在则取消授权
if (!userIds.Any() && hasAuthorizedUsers.Any())
{
// 取消该模块下所有用户的授权
isSuccess = this.CancelPermission(sysCustomFeatureId, new List<int>());
}
//如果参数有授权用户
if (userIds.Any())
{
//如果数据库没有任何授权用户 则全部加入
if (!hasAuthorizedUsers.Any())
{
isSuccess = this.AddPermission(sysCustomFeatureId, userIds);
}
//如果数据库已存在部分授权用户 则将新增的加入数据库 不在参数集合中的用户移除授权
else
{
//以数据库用户id集合为主体 查出参数中用户id差集添加授权
var except = hasAuthorizedUsers.Except(userIds).ToList();
//以参数用户id集合为主体 查出数据库中用户id差集移除授权
var insert = userIds.Except(hasAuthorizedUsers).ToList();
if (except.Any()) _ = this.CancelPermission(sysCustomFeatureId, except);
if (insert.Any()) isSuccess = this.AddPermission(sysCustomFeatureId, insert);
}
}
return isSuccess;
}
}
}
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZhuBao.DAL.DiaOrder;
using ZhuBao.DAL.DiaOrder.Extensions;
using ZhuBao.Model.DiaOrder;
namespace ZhuBao.BLL.DiaOrder
{
public class SysCustomUserBll
{
//引用数据层
private readonly SysCustomUserEfDal dal = new SysCustomUserEfDal();
/// <summary>
/// 获取功能模块下已授权用户
/// </summary>
/// <returns></returns>
public List<SysCustomUserModel> GetHasAuthorizedUser(int sysCustomFeatureId)
{
//获取已经授权用户用户id集合
var hasAuthorizedUsers = new SysCustomFeatureUserBll().GetCustomFeatureUser(sysCustomFeatureId);
//最终返回的数据集合
var returnList = new List<SysCustomUserModel>();
//获取已授权用户
if (hasAuthorizedUsers.Any())
{
var predicate = Expressionable.Create<SysCustomUserModel>()
.AndIF(hasAuthorizedUsers.Any(), it => hasAuthorizedUsers.Contains(it.UserId) && it.State.Equals("2"))
.ToExpression();
returnList.AddRange(dal.GetList(predicate));
}
return returnList;
}
/// <summary>
/// 获取功能模块下未授权用户
/// </summary>
/// <returns></returns>
public List<SysCustomUserModel> GetNotAuthorizedUser(int sysCustomFeatureId)
{
//获取已经授权用户用户id集合
var hasAuthorizedUsers = new SysCustomFeatureUserBll().GetCustomFeatureUser(sysCustomFeatureId);
//获取未授权用户
var predicate = Expressionable.Create<SysCustomUserModel>()
.AndIF(hasAuthorizedUsers.Any(), it => !hasAuthorizedUsers.Contains(it.UserId) && it.State.Equals("2"))
.ToExpression();
return dal.GetList(predicate, "", false);
}
}
}
Model层大家根据表结构自己创建就好 此处不做展示