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>();
            Dictionary<string, Dictionary<string, string>> dicValsJson = new Dictionary<string, 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)
                    {
                        if(!string.IsNullOrEmpty(sub.DataKey))
                        {
                            string[] TextFields = sub.DataText.Split(',');
                            Dictionary<string, string> vals = new Dictionary<string, string>();
                            foreach(DataRow dr in dtsub.Rows)
                            {
                                foreach(string key in TextFields)
                                {
                                    vals.Add(key, dr[key].ToString());
                                }
                            }
                            dicValsJson.Add(sub.Alias + "." + sub.DataKey, vals);
                        }
                        else
                        {
                            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
            {
                if(item.ExcuteFlag == 2 || item.ExcuteFlag == 3)
                {
                    ConnectStr = MysqlConn.RedisConnStr;
                }
                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);
            }
            List<Dictionary<string, string>> diclist = new List<Dictionary<string, string>>();
            if(item.ExcuteFlag == 0)
            {
                if(page > 1)
                {
                    int skip = (page - 1) * limit;
                    sql += " limit " + skip + "," + limit;
                }
                else
                {
                    sql += " limit " + limit;
                }
                DataTable dt = CustomerSqlConn.dtable(sql, ConnectStr);
                foreach(DataRow dr in dt.Rows)
                {
                    Dictionary<string, string> row = new Dictionary<string, string>();
                    foreach(DataColumn dc in dt.Columns)
                    {
                        Match m = Regex.Match(dr[dc.ColumnName].ToString(), @"\#\{.*?\}\#");
                        if(m.Success)
                        {
                            string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                            Dictionary<string, string> fieldVals = dicValsJson[fieldName];
                            foreach(string field in fieldVals.Keys)
                            {
                                row.Add(dc.ColumnName + field, fieldVals[field]);
                            }
                        }
                        else
                        {
                            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);
                }
            }
            else if(item.ExcuteFlag == 2)
            {
                string redisCommand = sql;
                string[] redisCommandList = redisCommand.Split(' ');
                string kind = redisCommandList[0];
                string key = redisCommandList[1];
                if(kind == "lrange_json")
                {
                    List<Dictionary<string, string>> list = new CustomRedisDbconn(ConnectStr).GetList<Dictionary<string, string>>(key, page, limit);
                    foreach(Dictionary<string, string> dr in list)
                    {
                        Dictionary<string, string> row = new Dictionary<string, string>();
                        foreach(string ColumnName in dr.Keys)
                        {
                            Match m = Regex.Match(dr[ColumnName], @"\#\{.*?\}\#");
                            if(m.Success)
                            {
                                string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                                Dictionary<string, string> fieldVals = dicValsJson[fieldName];
                                foreach(string field in fieldVals.Keys)
                                {
                                    row.Add(ColumnName + field, fieldVals[field]);
                                }
                            }
                            else
                            {
                                row.Add(ColumnName, dr[ColumnName]);
                            }
                        }
                        diclist.Add(row);
                    }
                }
                else if(kind == "lrange_str")
                {
                    List<string> list = new CustomRedisDbconn(ConnectStr).GetList<string>(key, page, limit);
                    foreach(string val in list)
                    {
                        Dictionary<string, string> row = new Dictionary<string, string>();
                        Match m = Regex.Match(val, @"\#\{.*?\}\#");
                        if(m.Success)
                        {
                            string fieldName = m.Value.Replace("#{", "").Replace("}#", "");
                            Dictionary<string, string> fieldVals = dicValsJson[fieldName];
                            foreach(string field in fieldVals.Keys)
                            {
                                row.Add(field, fieldVals[field]);
                            }
                        }
                        else
                        {
                            row.Add("值", val);
                        }
                        diclist.Add(row);
                    }
                }
                else if (kind == "get")
                {
                    Dictionary<string, string> row = new Dictionary<string, string>();
                    row.Add("值", new CustomRedisDbconn(ConnectStr).Get<string>(key));
                    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<Dictionary<string, string>> subJson = new List<Dictionary<string, string>>();
            List<CustomQuerySub> subItems = db.CustomQuerySub.Where(m => m.ParentId == Id).ToList();
            foreach(CustomQuerySub sub in subItems)
            {
                Dictionary<string, string> subJsonItem = new Dictionary<string, string>();

                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());
                        }
                    }
                }

                subJsonItem.Add("DatabaseConnect", sub.DatabaseConnect);
                subJsonItem.Add("SqlContent", sqlstr);
                subJsonItem.Add("DataKey", sub.DataKey);
                subJsonItem.Add("DataText", sub.DataText);
                subJsonItem.Add("Alias", sub.Alias);
                subJson.Add(subJsonItem);
            }
            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 SubSqlString = "";
            if(subJson.Count > 0)
            {
                SubSqlString = ",\"SubSqlString\":" + Newtonsoft.Json.JsonConvert.SerializeObject(subJson);
            }
            string SendData = "{\"Operater\":\"" + sysAdmin.Id + "\",\"SqlString\":\"" + sql + "\",\"FileName\":\"" + FileName + "\",\"MaxCount\":\"0\"" + SubSqlString + "}";
            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);
        //     string ConnectStr = "";
        //     Dictionary<string, string> dicVals = new Dictionary<string, string>();
        //     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]);
        //             }
        //         }

        //         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, @"\$\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 + "\",\"ConnectStr\":\"" + ConnectStr + "\"}";
        //     RedisDbconn.Instance.AddList("ExportQueue", SendData);
        //     return "success";
        // }

        #endregion

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

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

        #endregion

    }
}