kxs-admin/Areas/Admin/Controllers/MainServer/CustomQueryDoController.cs

/*
 * 自定义查询
 */

using System;
using System.Web;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Data;
using System.Threading.Tasks;
using System.Text.RegularExpressions;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Http;
using Microsoft.Extensions.Logging;
using Microsoft.Extensions.Options;
using MySystem.Models;
using Library;
using LitJson;
using MySystemLib;

namespace MySystem.Areas.Admin.Controllers
{
    [Area("Admin")]
    [Route("Admin/[controller]/[action]")]
    public class CustomQueryDoController : BaseController
    {
        public CustomQueryDoController(IHttpContextAccessor accessor, ILogger<BaseController> logger, IOptions<Setting> setting) : base(accessor, logger, setting)
        {
            OtherMySqlConn.connstr = ConfigurationManager.AppSettings["SqlConnStr"].ToString();
        }

        #region 自定义查询列表

        /// <summary>
        /// 根据条件查询自定义查询列表
        /// </summary>
        /// <returns></returns>
        public IActionResult Index(int Id, string right)
        {
            ViewBag.RightInfo = RightInfo;
            ViewBag.right = right;
            ViewBag.Id = Id.ToString();
            Dictionary<string, string> query = new Dictionary<string, string>();
            CustomQuery item = db.CustomQuery.FirstOrDefault(m => m.Id == Id) ?? new CustomQuery();
            if(SysUserName != "admin")
            {
                string checkString = "," + item.AdminNames + ",";
                if(!checkString.Contains("," + SysUserName + ","))
                {
                    return Content("无权限");
                }
            }
            string sql = item.SqlContent;
            MatchCollection mc = Regex.Matches(sql, @"\$\{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                string fieldName = fieldData[0];
                string Title = fieldData[1];
                if(!query.ContainsKey(fieldName))
                {
                    query.Add(fieldName, Title);
                }
            }
            List<CustomQuerySub> subItems = db.CustomQuerySub.Where(m => m.ParentId == Id).ToList();
            foreach(CustomQuerySub sub in subItems)
            {
                sql = sub.SqlContent;
                mc = Regex.Matches(sql, @"\$\{.*?\}\$");
                foreach(Match m in mc)
                {
                    string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                    string fieldName = fieldData[0];
                    string Title = fieldData[1];
                    if(!query.ContainsKey(fieldName))
                    {
                        query.Add(fieldName, Title);
                    }
                }
            }
            ViewBag.query = query;

            string[] fieldList = item.SeoDescription.Split(',');
            string cols = "";
            foreach(string Title in fieldList)
            {
                cols += ",{field:'" + Title + "', width: 200, title:'" + Title + "'}";
            }
            ViewBag.cols = cols;

            return View();
        }

        #endregion

        #region 根据条件查询自定义查询列表

        /// <summary>
        /// 自定义查询列表
        /// </summary>
        /// <returns></returns>
        public JsonResult IndexData(int Id, int page = 1, int limit = 30)
        {
            Dictionary<string, string> dicVals = new Dictionary<string, string>();
            string ConnectStr = "";
            List<CustomQuerySub> subItems = db.CustomQuerySub.Where(m => m.ParentId == Id).ToList();
            foreach(CustomQuerySub sub in subItems)
            {
                ConnectStr = sub.DatabaseConnect;
                if(!string.IsNullOrEmpty(ConnectStr))
                {
                    ConnectStr = GetConnectStr(sub.DatabaseConnect);
                }
                else
                {
                    ConnectStr = MysqlConn.ReadSqlConnStr;
                }
                string sqlstr = sub.SqlContent;
                MatchCollection mcsub = Regex.Matches(sqlstr, @"\$\{.*?\}\$");
                foreach(Match m in mcsub)
                {
                    string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                    string fieldName = fieldData[0];
                    sqlstr = sqlstr.Replace(m.Value, Request.Query[fieldName].ToString());
                }

                mcsub = Regex.Matches(sqlstr, @"\#\{.*?\}\#");
                foreach(Match m in mcsub)
                {
                    string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                    if(dicVals.ContainsKey(fieldName))
                    {
                        sqlstr = sqlstr.Replace(m.Value, dicVals[fieldName]);
                    }
                    else
                    {
                        sqlstr = sqlstr.Replace(m.Value, "");
                    }
                }

                DataTable dtsub = CustomerSqlConn.dtable(sqlstr, ConnectStr);
                if(dtsub.Rows.Count > 0)
                {
                    if(dtsub.Rows.Count > 1)
                    {
                        string val = "";
                        foreach(DataRow dr in dtsub.Rows)
                        {
                            val += dr[0].ToString() + ",";
                        }
                        val = val.TrimEnd(',');
                        dicVals.Add(sub.Alias + "_" + dtsub.Columns[0].ColumnName, val);
                    }
                    else
                    {
                        DataRow dr = dtsub.Rows[0];
                        foreach(DataColumn dc in dtsub.Columns)
                        {
                            dicVals.Add(sub.Alias + "_" + dc.ColumnName, dr[dc.ColumnName].ToString());
                        }
                    }
                }
            }
            CustomQuery item = db.CustomQuery.FirstOrDefault(m => m.Id == Id) ?? new CustomQuery();
            ConnectStr = item.DatabaseConnect;
            if(!string.IsNullOrEmpty(ConnectStr))
            {
                ConnectStr = GetConnectStr(item.DatabaseConnect);
            }
            else
            {
                ConnectStr = MysqlConn.ReadSqlConnStr;
            }
            string sql = item.SqlContent;
            MatchCollection mc = Regex.Matches(sql, @"\$\{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                string fieldName = fieldData[0];
                sql = sql.Replace(m.Value, Request.Query[fieldName].ToString());
            }
            mc = Regex.Matches(sql, @"\#\{.*?\}\#");
            foreach(Match m in mc)
            {
                string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                if(dicVals.ContainsKey(fieldName))
                {
                    sql = sql.Replace(m.Value, dicVals[fieldName]);
                }
                else
                {
                    sql = sql.Replace(m.Value, "");
                }
            }
            mc = Regex.Matches(sql, @"\$\replace{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("$replace{", "").Replace("}$", "").Split('|');
                string str = fieldData[0];
                string oldStr = fieldData[1];
                string newStr = fieldData[2];
                str = str.Replace(oldStr, newStr);
                sql = sql.Replace(m.Value, str);
            }
            if(page > 1)
            {
                int skip = (page - 1) * limit;
                sql += " limit " + skip + "," + limit;
            }
            else
            {
                sql += " limit " + limit;
            }
            DataTable dt = CustomerSqlConn.dtable(sql, ConnectStr);
            List<Dictionary<string, string>> diclist = new List<Dictionary<string, string>>();
            foreach(DataRow dr in dt.Rows)
            {
                Dictionary<string, string> row = new Dictionary<string, string>();
                foreach(DataColumn dc in dt.Columns)
                {                 
                    if (dc.DataType == typeof(DateTime))
                    {
                        row.Add(dc.ColumnName, string.IsNullOrEmpty(dr[dc.ColumnName].ToString()) ? "" : DateTime.Parse(dr[dc.ColumnName].ToString()).ToString("yyyy-MM-dd HH:mm:ss"));
                    }
                    else
                    {
                        row.Add(dc.ColumnName, dr[dc.ColumnName].ToString());
                    }
                }
                diclist.Add(row);
            }
            Dictionary<string, object> obj = new Dictionary<string, object>();
            obj.Add("code", 0);
            obj.Add("msg", "");
            obj.Add("count", 300);
            obj.Add("data", diclist);
            return Json(obj);
        }

        #endregion

        #region 导出Excel

        /// <summary>
        /// 导出Excel
        /// </summary>
        /// <returns></returns>
        public string ExportExcel(int Id)
        {
            var sysAdmin = bsdb.SysAdmin.FirstOrDefault(m => m.AdminName == SysUserName && m.Status > -1);

            Dictionary<string, string> dicVals = new Dictionary<string, string>();
            List<CustomQuerySub> subItems = db.CustomQuerySub.Where(m => m.ParentId == Id).ToList();
            foreach(CustomQuerySub sub in subItems)
            {
                string ConnectStr = sub.DatabaseConnect;
                if(!string.IsNullOrEmpty(ConnectStr))
                {
                    ConnectStr = GetConnectStr(sub.DatabaseConnect);
                }
                else
                {
                    ConnectStr = MysqlConn.ReadSqlConnStr;
                }

                string sqlstr = sub.SqlContent;
                MatchCollection mcsub = Regex.Matches(sqlstr, @"\$\{.*?\}\$");
                foreach(Match m in mcsub)
                {
                    string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                    string fieldName = fieldData[0];
                    sqlstr = sqlstr.Replace(m.Value, Request.Query[fieldName].ToString());
                }

                mcsub = Regex.Matches(sqlstr, @"\#\{.*?\}\#");
                foreach(Match m in mcsub)
                {
                    string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                    if(dicVals.ContainsKey(fieldName))
                    {
                        sqlstr = sqlstr.Replace(m.Value, dicVals[fieldName]);
                    }
                }

                DataTable dtsub = CustomerSqlConn.dtable(sqlstr, MysqlConn.ReadSqlConnStr);
                if(dtsub.Rows.Count > 0)
                {
                    if(dtsub.Rows.Count > 1)
                    {
                        string val = "";
                        foreach(DataRow dr in dtsub.Rows)
                        {
                            val += dr[0].ToString() + ",";
                        }
                        val = val.TrimEnd(',');
                        dicVals.Add(sub.Alias + "_" + dtsub.Columns[0].ColumnName, val);
                    }
                    else
                    {
                        DataRow dr = dtsub.Rows[0];
                        foreach(DataColumn dc in dtsub.Columns)
                        {
                            dicVals.Add(sub.Alias + "_" + dc.ColumnName, dr[dc.ColumnName].ToString());
                        }
                    }
                }
            }
            CustomQuery item = db.CustomQuery.FirstOrDefault(m => m.Id == Id) ?? new CustomQuery();
            string sql = item.SqlContent;
            MatchCollection mc = Regex.Matches(sql, @"\$\{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                string fieldName = fieldData[0];
                sql = sql.Replace(m.Value, Request.Query[fieldName].ToString());
            }
            mc = Regex.Matches(sql, @"\$\replace{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("$replace{", "").Replace("}$", "").Split('|');
                string str = fieldData[0];
                string oldStr = fieldData[1];
                string newStr = fieldData[2];
                str = str.Replace(oldStr, newStr);
                sql = sql.Replace(m.Value, str);
            }
            var FileName = item.Title + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string SendData = "{\"Operater\":\"" + sysAdmin.Id + "\",\"SqlString\":\"" + sql + "\",\"FileName\":\"" + FileName + "\",\"MaxCount\":\"0\"}";
            RedisDbconn.Instance.AddList("ExportQueue", SendData);
            return "success";
        }

        #endregion

        #region 执行

        /// <summary>
        /// 执行
        /// </summary>
        /// <returns></returns>
        public string Excute(int Id)
        {
            var sysAdmin = bsdb.SysAdmin.FirstOrDefault(m => m.AdminName == SysUserName && m.Status > -1);

            Dictionary<string, string> dicVals = new Dictionary<string, string>();
            List<CustomQuerySub> subItems = db.CustomQuerySub.Where(m => m.ParentId == Id).ToList();
            foreach(CustomQuerySub sub in subItems)
            {
                string ConnectStr = sub.DatabaseConnect;
                if(!string.IsNullOrEmpty(ConnectStr))
                {
                    ConnectStr = GetConnectStr(sub.DatabaseConnect);
                }
                else
                {
                    ConnectStr = MysqlConn.ReadSqlConnStr;
                }

                string sqlstr = sub.SqlContent;
                MatchCollection mcsub = Regex.Matches(sqlstr, @"\$\{.*?\}\$");
                foreach(Match m in mcsub)
                {
                    string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                    string fieldName = fieldData[0];
                    sqlstr = sqlstr.Replace(m.Value, Request.Query[fieldName].ToString());
                }

                mcsub = Regex.Matches(sqlstr, @"\#\{.*?\}\#");
                foreach(Match m in mcsub)
                {
                    string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                    if(dicVals.ContainsKey(fieldName))
                    {
                        sqlstr = sqlstr.Replace(m.Value, dicVals[fieldName]);
                    }
                }

                DataTable dtsub = CustomerSqlConn.dtable(sqlstr, ConnectStr);
                if(dtsub.Rows.Count > 0)
                {
                    if(dtsub.Rows.Count > 1)
                    {
                        string val = "";
                        foreach(DataRow dr in dtsub.Rows)
                        {
                            val += dr[0].ToString() + ",";
                        }
                        val = val.TrimEnd(',');
                        dicVals.Add(sub.Alias + "_" + dtsub.Columns[0].ColumnName, val);
                    }
                    else
                    {
                        DataRow dr = dtsub.Rows[0];
                        foreach(DataColumn dc in dtsub.Columns)
                        {
                            dicVals.Add(sub.Alias + "_" + dc.ColumnName, dr[dc.ColumnName].ToString());
                        }
                    }
                }
            }
            CustomQuery item = db.CustomQuery.FirstOrDefault(m => m.Id == Id) ?? new CustomQuery();
            string sql = item.SqlContent;
            MatchCollection mc = Regex.Matches(sql, @"\$\{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("${", "").Replace("}$", "").Split('|');
                string fieldName = fieldData[0];
                sql = sql.Replace(m.Value, Request.Query[fieldName].ToString());
            }
            mc = Regex.Matches(sql, @"\$\replace{.*?\}\$");
            foreach(Match m in mc)
            {
                string[] fieldData = m.Value.Replace("$replace{", "").Replace("}$", "").Split('|');
                string str = fieldData[0];
                string oldStr = fieldData[1];
                string newStr = fieldData[2];
                str = str.Replace(oldStr, newStr);
                sql = sql.Replace(m.Value, str);
            }
            var FileName = item.Title + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string SendData = "{\"Operater\":\"" + sysAdmin.Id + "\",\"SqlString\":\"" + sql + "\",\"FileName\":\"执行\",\"MaxCount\":\"" + item.ExcuteFlag + "\"}";
            RedisDbconn.Instance.AddList("ExportQueue", SendData);
            return "success";
        }

        #endregion

        #region 获取链接数据库字符串

        public string GetConnectStr(string key)
        {
            return Library.ConfigurationManager.AppSettings[key].ToString();
        }

        #endregion

    }
}