vue+element+net ef开发利于运维人员的定制功能

3 篇文章 0 订阅

业务场景:往往大多数时候业务人员需要特定业务场景下的数据 这个时候会找开发人员 然后开发人员去数据库查询 加大了对开发人员的依赖 如果做一个能开发人员配置 然后业务人员根据自己需求去自己查询导出数据 大大缩短沟通成本 提高工作效率

此处为原创作品 未经允许不可转载

首先数据库表设计
图片太多拆开上传
在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
此次为功能表 日志表 用户授权表 及用户可保存上次输入参数记忆功能
最后呈现大致效果如下
在这里插入图片描述

在这里插入图片描述
在这里插入图片描述
在这里插入图片描述
附上核心代码

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层大家根据表结构自己创建就好 此处不做展示

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值