mp-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();
            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);
                }
            }
            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)
        {
            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());
            }
            if(page > 1)
            {
                int skip = (page - 1) * limit;
                sql += " limit " + skip + "," + limit;
            }
            else
            {
                sql += " limit " + limit;
            }
            DataTable dt = CustomerSqlConn.dtable(sql, MysqlConn.connstr);
            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);
            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());
            }
            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

    }
}