JLH/JLHHJSvr/Helper/CommonHelper.cs

using DirectService.Tools;
using JLHHJSvr.BLL;
using JLHHJSvr.Com;
using JLHHJSvr.LJException;
using JLHHJSvr.Tools;
using LJLib.DAL.SQL;
using LJLib.SQLEX;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using PhoneUI.BLL.L1BLL;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Xml;
using static LJLib.DAL.SQL.SqlStrHelper;

namespace JLHHJSvr.Helper
{
    /// <summary>
    /// 通用助手类，提供常用的业务操作方法
    /// </summary>
    internal class CommonHelper : HelperBase
    {
        #region 常量和正则表达式
        private const string _mapperAppend = "_Mapper_";
        private readonly Regex _staticParmReg = new Regex("[\\$]+[\\w]+[\\$]*");
        private readonly Regex _listParmReg = new Regex("@@[\\w]+@@");
        private readonly Dictionary<string, string> _xmlEscapeMap = new Dictionary<string, string>
        {
            {"&", "&amp;"},
            {"'", "&apos;"},
            {"\"", "&quot;"},
            {">", "&gt;"},
            {"<", "&lt;"},
        };
        private readonly Regex _computeRefReg = new Regex("([A-Za-z_][\\w]+)([\\s]*)([(]*)");
        #endregion

        #region 数据结构
        /// <summary>
        /// 查询结果数据结构
        /// </summary>
        private class QueryResult
        {
            public JArray datatable { get; set; }
            public JObject tableinfo { get; set; }
            public int totalcnt { get; set; }
            public int pageindex { get; set; }
            public int pagesize { get; set; }
        }

        /// <summary>
        /// 请求参数数据结构
        /// </summary>
        private class DynamicSelectRequest
        {
            public string token { get; set; }
            public string clienttype { get; set; }
            public string dsname { get; set; }
            public JObject queryparams { get; set; }
            public string orderstr { get; set; }
            public int pageindex { get; set; }
            public int pagesize { get; set; }
            public string dwname { get; set; }
            public string itemname { get; set; }
            public byte ifcompress { get; set; }
        }

        /// <summary>
        /// 响应结果数据结构
        /// </summary>
        public class DynamicSelectResponse
        {
            public string ErrMsg { get; set; }
            public JArray datatable { get; set; }
            public JObject tableinfo { get; set; }
            public int totalcnt { get; set; }
            public int pageindex { get; set; }
            public int pagesize { get; set; }
        }
        #endregion

        #region 主要公共方法

        /// <summary>
        /// 通用动态查询方法
        /// </summary>
        /// <param name="token">用户令牌</param>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <param name="pageIndex">页码，默认为1</param>
        /// <param name="pageSize">页大小，默认为50</param>
        /// <param name="orderStr">排序字符串，可选</param>
        /// <param name="dwName">用户习惯数据窗口名称，可选</param>
        /// <param name="itemName">用户习惯项目名称，可选</param>
        /// <param name="ifCompress">是否压缩用户习惯数据，默认为0</param>
        /// <returns>查询结果</returns>
        public DynamicSelectResponse ExecuteDynamicSelect(
            string dsname,
            JObject queryParams,
            int pageIndex = 1,
            int pageSize = 50,
            string orderStr = null,
            string dwName = null,
            string itemName = null,
            byte ifCompress = 0)
        {
            if (string.IsNullOrEmpty(dsname))
            {
                throw new ArgumentException("dsname不能为空", nameof(dsname));
            }

            if (queryParams == null)
            {
                throw new ArgumentNullException(nameof(queryParams));
            }

            var request = new DynamicSelectRequest
            {
                dsname = dsname,
                queryparams = queryParams,
                orderstr = orderStr,
                pageindex = pageIndex,
                pagesize = pageSize,
                dwname = dwName,
                itemname = itemName,
                ifcompress = ifCompress
            };

            return ExecuteDynamicSelectInternal(request);
        }

        /// <summary>
        /// 通用动态查询方法（简化版，使用当前用户上下文）
        /// </summary>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <param name="pageIndex">页码，默认为1</param>
        /// <param name="pageSize">页大小，默认为50</param>
        /// <param name="orderStr">排序字符串，可选</param>
        /// <param name="dwName">用户习惯数据窗口名称，可选</param>
        /// <param name="itemName">用户习惯项目名称，可选</param>
        /// <param name="ifCompress">是否压缩用户习惯数据，默认为0</param>
        /// <returns>查询结果</returns>
        public DynamicSelectResponse ExecuteDynamicSelectWithContext(
            string dsname,
            JObject queryParams,
            int pageIndex = 1,
            int pageSize = 50,
            string orderStr = null,
            string dwName = null,
            string itemName = null,
            byte ifCompress = 0)
        {
            if (context?.tokendata == null)
            {
                throw new InvalidOperationException("用户上下文或token为空");
            }

            return ExecuteDynamicSelect(
                dsname,
                queryParams,
                pageIndex,
                pageSize,
                orderStr,
                dwName,
                itemName,
                ifCompress
            );
        }

        /// <summary>
        /// 执行动态查询并返回指定类型的列表
        /// </summary>
        /// <typeparam name="T">返回数据类型</typeparam>
        /// <param name="token">用户令牌</param>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <param name="pageIndex">页码，默认为1</param>
        /// <param name="pageSize">页大小，默认为50</param>
        /// <param name="orderStr">排序字符串，可选</param>
        /// <param name="dwName">用户习惯数据窗口名称，可选</param>
        /// <param name="itemName">用户习惯项目名称，可选</param>
        /// <param name="ifCompress">是否压缩用户习惯数据，默认为0</param>
        /// <returns>查询结果列表</returns>
        public List<T> ExecuteDynamicSelectToList<T>(
            string dsname,
            JObject queryParams,
            int pageIndex = 1,
            int pageSize = 50,
            string orderStr = null,
            string dwName = null,
            string itemName = null,
            byte ifCompress = 0)
        {
            var response = ExecuteDynamicSelect(dsname, queryParams, pageIndex, pageSize, orderStr, dwName, itemName, ifCompress);

            if (!string.IsNullOrEmpty(response.ErrMsg)) { throw new Exception(response.ErrMsg); }

            if (response?.datatable != null)
            {
                return response.datatable.ToObject<List<T>>();
            }

            return new List<T>();
        }

        /// <summary>
        /// 执行动态查询并返回指定类型的列表（简化版，使用当前用户上下文）
        /// </summary>
        /// <typeparam name="T">返回数据类型</typeparam>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <param name="pageIndex">页码，默认为1</param>
        /// <param name="pageSize">页大小，默认为50</param>
        /// <param name="orderStr">排序字符串，可选</param>
        /// <param name="dwName">用户习惯数据窗口名称，可选</param>
        /// <param name="itemName">用户习惯项目名称，可选</param>
        /// <param name="ifCompress">是否压缩用户习惯数据，默认为0</param>
        /// <returns>查询结果列表</returns>
        public List<T> ExecuteDynamicSelectToListWithContext<T>(
            string dsname,
            JObject queryParams,
            int pageIndex = 1,
            int pageSize = 50,
            string orderStr = null,
            string dwName = null,
            string itemName = null,
            byte ifCompress = 0)
        {
            var response = ExecuteDynamicSelectWithContext(dsname, queryParams, pageIndex, pageSize, orderStr, dwName, itemName, ifCompress);

            if (response?.datatable != null)
            {
                return response.datatable.ToObject<List<T>>();
            }

            return new List<T>();
        }

        /// <summary>
        /// 执行分页查询获取数据字典（用于下拉框等控件）
        /// </summary>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <param name="valueField">值字段名，默认为"value"</param>
        /// <param name="labelField">显示字段名，默认为"label"</param>
        /// <param name="pageSize">最大返回条数，默认为1000</param>
        /// <returns>数据字典列表</returns>
        public List<KeyValuePair<string, string>> GetDictionaryData(
            string dsname,
            JObject queryParams = null,
            string valueField = "value",
            string labelField = "label",
            int pageSize = 1000)
        {
            queryParams = queryParams ?? new JObject();
            var response = ExecuteDynamicSelectWithContext(dsname, queryParams, 1, pageSize);

            var result = new List<KeyValuePair<string, string>>();

            if (response?.datatable != null)
            {
                foreach (var item in response.datatable)
                {
                    var value = item[valueField]?.ToString() ?? string.Empty;
                    var label = item[labelField]?.ToString() ?? string.Empty;

                    if (!string.IsNullOrEmpty(value))
                    {
                        result.Add(new KeyValuePair<string, string>(value, label));
                    }
                }
            }

            return result;
        }

        /// <summary>
        /// 检查指定数据是否存在
        /// </summary>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <returns>是否存在数据</returns>
        public bool CheckDataExists(string dsname, JObject queryParams)
        {
            var response = ExecuteDynamicSelectWithContext(dsname, queryParams, 1, 1);

            return response?.datatable != null && response.datatable.Count > 0;
        }

        /// <summary>
        /// 获取数据总数
        /// </summary>
        /// <param name="dsname">数据源XML名称</param>
        /// <param name="queryParams">查询参数</param>
        /// <returns>数据总数</returns>
        public int GetDataCount(string dsname, JObject queryParams)
        {
            var response = ExecuteDynamicSelectWithContext(dsname, queryParams, 1, 1);

            return response?.totalcnt ?? 0;
        }

        #endregion

        #region 核心实现逻辑

        /// <summary>
        /// 执行动态查询的核心逻辑
        /// </summary>
        /// <param name="request">请求参数</param>
        /// <returns>响应结果</returns>
        private DynamicSelectResponse ExecuteDynamicSelectInternal(DynamicSelectRequest request)
        {
            var response = new DynamicSelectResponse();

            try
            {
                if (request.queryparams == null)
                {
                    throw new ArgumentNullException("queryparams");
                }

                if (string.IsNullOrEmpty(request.dsname))
                {
                    throw new ArgumentNullException("dsname");
                }

                var rsltGroup = GetXmlResult(request.dsname, request, false);
                response.datatable = rsltGroup.datatable;
                response.tableinfo = rsltGroup.tableinfo;
                response.totalcnt = rsltGroup.totalcnt;
                response.pageindex = rsltGroup.pageindex;
                response.pagesize = rsltGroup.pagesize;
            }
            catch (Exception ex)
            {
                response.ErrMsg = ex.Message;
            }

            return response;
        }

        /// <summary>
        /// 解析XML并执行查询
        /// </summary>
        /// <param name="cmd">SQL命令对象</param>
        /// <param name="dsname">数据源名称</param>
        /// <param name="request">请求参数</param>
        /// <param name="tokendata">用户令牌数据</param>
        /// <param name="ifmapper">是否为mapper模式</param>
        /// <returns>查询结果</returns>
        private QueryResult GetXmlResult(string dsname, DynamicSelectRequest request, bool ifmapper)
        {
            var rslt = new QueryResult();
            var rootPath = GlobalVar.App_Data + "\\DataStore\\";
#if DEBUG
            rootPath = rootPath.Substring(0, rootPath.IndexOf("\\bin\\")) + "\\DataStore\\";
#endif
            var filePath = rootPath + dsname + ".xml";
            if (!File.Exists(filePath))
            {
                throw new LJCommonException("缺失接口文件：" + dsname + ".xml");
            }

            var queryParams = request.queryparams;
            var wholexml = File.ReadAllText(filePath);
            var treatedsb = new StringBuilder();

            // 逐行扫描，检查全局变量与转义符
            var staticParmDic = new Dictionary<string, string>();
            using (StringReader reader = new StringReader(wholexml))
            {
                var incomment = false;
                string line;
                while ((line = reader.ReadLine()) != null)
                {
                    var lineTrim = line.Trim();
                    if (line.Contains("<!--"))
                    {
                        incomment = true;
                        var commentStartIndex = line.IndexOf("<!--");
                        if (commentStartIndex > 0)
                        {
                            treatedsb.AppendLine(line.Substring(0, commentStartIndex));
                        }
                    }
                    if (incomment)
                    {
                        if (line.Contains("-->"))
                        {
                            incomment = false;
                            var commenttail = line.Substring(line.IndexOf("-->") + 3);
                            if (!string.IsNullOrEmpty(commenttail))
                            {
                                treatedsb.AppendLine(commenttail);
                            }
                        }
                        continue;
                    }

                    // 处理$标记的全局变量
                    var staticMatches = _staticParmReg.Matches(line);
                    var staticMatchesStr = new HashSet<string>();
                    foreach (Match match in staticMatches)
                    {
                        var matchstr = match.ToString();
                        staticMatchesStr.Add(matchstr);
                    }

                    // $$开头$$结束为全局数组变量，直接作字符串替换
                    foreach (var item in staticMatchesStr.Where(x => x.StartsWith("$$")))
                    {
                        if (!staticParmDic.ContainsKey(item))
                        {
                            var staticParm = item.Trim('$');
                            var staticVal = string.Empty;

                            if (staticParm == "user_deptstr")
                            {
                                // 用户部门权限 - 需要根据实际业务实现
                                staticVal = "1"; // 简化处理
                            }
                            else if (staticParm == "user_outrepstr")
                            {
                                // 用户外勤权限 - 需要根据实际业务实现
                                staticVal = ""; // 简化处理
                            }

                            staticVal = staticVal ?? string.Empty;
                            staticParmDic[item] = staticVal.Trim(new[] { '(', ')' });
                        }

                        if (lineTrim.StartsWith("<") && lineTrim.EndsWith(">"))
                        {
                            // xml标签
                        }
                        else
                        {
                            var staticVal = staticParmDic[item];
                            line = line.Replace(item, staticVal);
                        }
                    }

                    // $开头的全局变量转换为@开头的变量
                    foreach (var item in staticMatchesStr.Where(x => x.StartsWith("$") && !x.StartsWith("$$")))
                    {
                        var staticParm = item.Trim('$');
                        if (!queryParams.ContainsKey(staticParm))
                        {
                            if (staticParm.StartsWith("user_"))
                            {
                                // 查询用户权限值
                                var keyStr = staticParm.Substring("user_".Length);
                                var userKeyStr = "0";

                                cmd.CommandText = @"select " + keyStr + " from u_user_jlhprice where empid = @empid";
                                cmd.Parameters.Clear();
                                cmd.Parameters.AddWithValue("@empid", context.tokendata.userid);
                                using (var UserReader = cmd.ExecuteReader())
                                {
                                    if (UserReader.Read())
                                    {
                                        userKeyStr = Convert.ToString(UserReader[keyStr]).Trim();
                                    }
                                }
                                queryParams[staticParm] = userKeyStr;
                            }
                            else
                            {
                                throw new NotImplementedException(staticParm);
                            }
                        }
                        line = line.Replace(item, "@" + item.Substring(1));
                    }

                    if (lineTrim.StartsWith("<") && lineTrim.EndsWith(">"))
                    {
                        // xml标签
                        line = line.Replace(" $", " ");
                        line = line.Replace(" @", " ");
                        line = line.Replace("!=\"", "_notequals=\"");
                    }
                    else
                    {
                        // 处理@@标记的数组变量
                        var listParmMatches = _listParmReg.Matches(line);
                        foreach (Match match in listParmMatches)
                        {
                            var matchstr = match.ToString();
                            var pname = matchstr.Trim('@');
                            if (!queryParams.ContainsKey(pname))
                            {
                                continue;
                            }
                            if (queryParams.GetValue(pname).Type != JTokenType.Array)
                            {
                                continue;
                            }
                            var listval = queryParams.Value<JArray>(pname).Select(x => "'" + x + "'");
                            var pval = string.Join(",", listval);
                            line = line.Replace(matchstr, pval);
                        }

                        // 处理转义字符
                        foreach (var escapeItem in _xmlEscapeMap)
                        {
                            line = line.Replace(escapeItem.Key, escapeItem.Value);
                        }
                    }

                    treatedsb.AppendLine(line);
                }
            }

            var treatedxml = treatedsb.ToString();
            var xmlDoc = new XmlDocument();
            xmlDoc.LoadXml(treatedxml);

            XmlNode selectNode = null;
            XmlNode rootNode = null;
            foreach (XmlNode rchild in xmlDoc.ChildNodes)
            {
                if (rchild.Name != "xml")
                {
                    rootNode = rchild;
                    break;
                }
            }

            if (rootNode == null)
            {
                throw new LJCommonException("格式错误，不存在根节点");
            }

            if (rootNode.Name == "data")
            {
                var dataChild = rootNode.FirstChild;
                if (dataChild == null)
                {
                    throw new LJCommonException("格式错误，data不存在根节点");
                }
                if (dataChild.Name == "json")
                {
                    var dataJson = dataChild.InnerText;
                    rslt.datatable = JsonConvert.DeserializeObject<JArray>(dataJson);
                    return rslt;
                }
                else
                {
                    throw new LJCommonException("未支持的data节点：" + dataChild.Name);
                }
            }
            else if (rootNode.Name == "select")
            {
                selectNode = rootNode;
            }
            else
            {
                throw new LJCommonException("未支持的根节点：" + rootNode.Name);
            }

            // 处理select节点
            var selectbase = string.Empty;
            var selectstr = selectNode.SelectSingleNode("selectstr")?.InnerText;
            var orderstr = selectNode.SelectSingleNode("orderstr")?.InnerText;
            var whereList = new List<string>();
            var whereNode = selectNode.SelectSingleNode("where");

            if (whereNode != null)
            {
                foreach (XmlNode whereChild in whereNode.ChildNodes)
                {
                    if (whereChild.Name == "when")
                    {
                        var match = true;
                        foreach (XmlAttribute attr in whereChild.Attributes)
                        {
                            var attrname = attr.Name;
                            var attrval = attr.Value;
                            JToken valjtoken = null;
                            if (attrval.StartsWith("@"))
                            {
                                var pname = attrval.Replace("@", "");
                                attrval = null;
                                if (queryParams.ContainsKey(pname))
                                {
                                    valjtoken = queryParams[pname];
                                    switch (valjtoken.Type)
                                    {
                                        case JTokenType.Null:
                                        case JTokenType.None:
                                        case JTokenType.Undefined:
                                            attrval = null;
                                            break;
                                        case JTokenType.Object:
                                        case JTokenType.Array:
                                            attrval = valjtoken.ToString();
                                            break;
                                        default:
                                            attrval = valjtoken.ToString();
                                            break;
                                    }
                                }
                            }
                            if (attrname == "notnull")
                            {
                                if (attrval == null)
                                {
                                    match = false;
                                    break;
                                }
                            }
                            else if (attrname == "notempty")
                            {
                                if (valjtoken == null || string.IsNullOrEmpty(valjtoken.ToString()))
                                {
                                    match = false;
                                    break;
                                }
                                if (valjtoken.Type == JTokenType.Array)
                                {
                                    if ((valjtoken as JArray).Count == 0)
                                    {
                                        match = false;
                                        break;
                                    }
                                }
                            }
                            else
                            {
                                var ifnot = false;
                                var attrnameVal = attrname.TrimStart('@');
                                if (attrnameVal.EndsWith("_notequals"))
                                {
                                    ifnot = true;
                                    attrnameVal = attrnameVal.Substring(0, attrnameVal.IndexOf("_notequals"));
                                }
                                if (!queryParams.ContainsKey(attrnameVal))
                                {
                                    throw new ArgumentException("queryparams." + attrnameVal);
                                }
                                attrnameVal = queryParams[attrnameVal].ToString();
                                if (string.Equals(attrnameVal, attrval) != !ifnot)
                                {
                                    match = false;
                                    break;
                                }
                            }
                        }
                        if (match)
                        {
                            whereList.Add(whereChild.InnerText.Trim());
                        }
                    }
                    else
                    {
                        throw new NotImplementedException();
                    }
                }
            }

            var wherestr = ListEx.GetWhereStr(whereList);
            var parmDic = new Dictionary<string, object>();
            foreach (var item in queryParams)
            {
                if (item.Value is JArray)
                {
                    continue;
                }
                else
                {
                    parmDic[item.Key] = item.Value.ToString();
                }
            }

            var displayfields = selectNode.SelectSingleNode("displayfields");
            var uncomputefields = new Dictionary<string, string>();
            string rownumfield = null;

            if (displayfields != null)
            {
                foreach (XmlNode fieldNode in displayfields.ChildNodes)
                {
                    string field = null;
                    XmlAttribute computeAttr = null;
                    foreach (XmlAttribute attr in fieldNode.Attributes)
                    {
                        if (string.Equals(attr.Name, "field", StringComparison.OrdinalIgnoreCase))
                        {
                            field = attr.Value;
                        }
                        else if (string.Equals(attr.Name, "compute", StringComparison.OrdinalIgnoreCase))
                        {
                            computeAttr = attr;
                        }
                    }
                    if (computeAttr != null)
                    {
                        var computeexpr = computeAttr.Value;
                        if (!string.IsNullOrEmpty(computeexpr))
                        {
                            if (computeexpr.IndexOf("getrow(", StringComparison.OrdinalIgnoreCase) >= 0)
                            {
                                rownumfield = field;
                            }
                            else
                            {
                                uncomputefields[field] = computeexpr;
                            }
                            fieldNode.Attributes.Remove(computeAttr);
                        }
                    }
                }
            }

            // 处理计算字段
            var parseResult = SqlStrHelper.ParseSelectStr(selectstr);
            if (parseResult.selectStr.ToUpper().Contains("DISTINCT"))
            {
                throw new NotImplementedException("未支持DISTINCT");
            }
            if (uncomputefields.Count > 0)
            {
                // 多次循环，防止因嵌套解析失败
                var checkcnt = uncomputefields.Count;
                for (var i = 0; i < checkcnt; i++)
                {
                    if (uncomputefields.Count == 0)
                    {
                        break;
                    }
                    var flist = uncomputefields.Keys.ToList();
                    foreach (var compf in flist)
                    {
                        if (!uncomputefields.ContainsKey(compf))
                        {
                            continue;
                        }
                        var waitnext = false;
                        var computeexpr = uncomputefields[compf];
                        var refMatches = _computeRefReg.Matches(computeexpr);
                        var refFields = new HashSet<string>();
                        foreach (Match refmatch in refMatches)
                        {
                            if (string.IsNullOrEmpty(refmatch.Groups[3].ToString()))
                            {
                                var refField = refmatch.Groups[1].ToString();
                                if (!parseResult.selectFieldsDic.ContainsKey(refField))
                                {
                                    waitnext = true;
                                    break;
                                }
                                refFields.Add(refField);
                            }
                        }
                        if (waitnext)
                        {
                            continue;
                        }
                        var refSorts = refFields.OrderByDescending(x => x.Length).ToList();
                        var holderid = -1;
                        foreach (var reff in refSorts)
                        {
                            holderid++;
                            computeexpr = computeexpr.Replace(reff, "{" + holderid.ToString("000") + "}");
                        }
                        for (var hindex = 0; hindex <= holderid; hindex++)
                        {
                            var reff = refSorts[hindex];
                            var refselect = parseResult.selectFieldsDic[reff];
                            var asIndex = refselect.LastIndexOf(" as ", StringComparison.OrdinalIgnoreCase);
                            if (asIndex > 0)
                            {
                                var asname = refselect.Substring(asIndex + " as ".Length).Trim();
                                if (string.Equals(asname, reff, StringComparison.OrdinalIgnoreCase))
                                {
                                    refselect = refselect.Substring(0, asIndex).Trim();
                                }
                            }
                            computeexpr = computeexpr.Replace("{" + hindex.ToString("000") + "}", "(" + refselect + ")");
                        }
                        parseResult.selectFieldsDic[compf] = computeexpr + " AS " + compf;
                        uncomputefields.Remove(compf);
                    }
                }
                if (uncomputefields.Count > 0)
                {
                    throw new LJCommonException($"解析计算列失败：[{string.Join(",", uncomputefields.Keys)}]");
                }
            }

            // 构建SQL并执行
            var mergesql = SqlStrHelper.BuildSelectStrL1(parseResult, string.Join(",", parseResult.selectFieldsDic.Keys), wherestr, orderstr, request.pageindex, request.pagesize);
            cmd.CommandText = mergesql;
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.Clear();

            if (queryParams != null)
            {
                foreach (var item in queryParams)
                {
                    if (item.Value == null)
                    {
                        cmd.Parameters.AddWithValue("@" + item.Key, DBNull.Value);
                    }
                    else
                    {
                        object pval = null;
                        switch (item.Value.Type)
                        {
                            case JTokenType.Array:
                                continue;
                            case JTokenType.Integer:
                                pval = Convert.ToInt64(item.Value);
                                break;
                            case JTokenType.Float:
                                pval = Convert.ToDecimal(item.Value);
                                break;
                            case JTokenType.Boolean:
                                pval = Convert.ToBoolean(item.Value);
                                break;
                            default:
                                pval = item.Value.ToString();
                                break;
                        }
                        cmd.Parameters.AddWithValue("@" + item.Key, pval);
                    }
                }
            }

            rslt.datatable = new JArray();
            var rownum = (request.pageindex - 1) * request.pagesize;
            if (rownum < 0)
            {
                rownum = 0;
            }

            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    rownum++;
                    var row = new JObject();
                    if (rownumfield != null)
                    {
                        row.Add(rownumfield, rownum);
                    }

                    for (var i = 0; i < reader.FieldCount; i++)
                    {
                        var fieldName = reader.GetName(i);
                        var dbval = reader[fieldName];

                        if (ifmapper)
                        {
                            if (i == 0)
                            {
                                fieldName = "value";
                            }
                            else if (i == 1)
                            {
                                fieldName = "label";
                            }
                        }

                        if (dbval == DBNull.Value)
                        {
                            row.Add(fieldName, null);
                        }
                        else if (dbval is string)
                        {
                            row.Add(fieldName, (dbval as string).TrimEnd());
                        }
                        else if (dbval is DateTime)
                        {
                            row.Add(fieldName, (DateTime)dbval);
                        }
                        else
                        {
                            row.Add(fieldName, JToken.FromObject(dbval));
                        }
                    }
                    rslt.datatable.Add(row);
                }
            }

            // 处理总行数
            if (!ifmapper)
            {
                string countstr = parseResult.selectStr + " COUNT(0) " + parseResult.fromStr;
                if (!string.IsNullOrEmpty(wherestr))
                {
                    countstr += " WHERE " + wherestr;
                }
                cmd.CommandText = countstr;
                try
                {
                    var totalcnt = Convert.ToInt32(cmd.ExecuteScalar());
                    rslt.totalcnt = totalcnt;
                    rslt.pageindex = request.pageindex;
                    rslt.pagesize = request.pagesize;
                }
                catch (Exception ex)
                {
                    throw new Exception(string.Format("ex:{0}\r\ncmd:{1}", ex, cmd.CommandText));
                }
            }

            // 处理用户习惯配置
            if (request.pageindex <= 1 && !ifmapper)
            {
                try
                {
                    var ufs = HelperBase.GetHelper<SysUserFileString>(cmd, context);
                    var preferenceobj = new JObject();
                    var preferencejson = BllHelper.GetValue(cmd, context.tokendata.userid, request.dwname, request.itemname, string.Empty, request.ifcompress == 1);
                    if (string.IsNullOrEmpty(preferencejson))//如果没有自己的布局方案，尝试获取系统的布局方案
                    {
                        preferencejson = ufs.getSystemLayout(request.dwname, request.itemname, request.ifcompress);
                    }
                    if (!string.IsNullOrEmpty(preferencejson))
                    {
                        try
                        {
                            preferenceobj = JsonConvert.DeserializeObject<JObject>(preferencejson);
                        }
                        catch (Exception e)
                        {
                            Trace.Write("解析json失败" + preferencejson);
                        }
                    }
                    var oldcolDic = new Dictionary<string, JObject>(StringComparer.OrdinalIgnoreCase);
                    var colSortDic = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
                    var sortid = 0;
                    if (preferenceobj.ContainsKey("columns"))
                    {
                        var oldcols = preferenceobj.GetValue("columns") as JArray;
                        foreach (JObject col in oldcols)
                        {
                            var field = col["field"].ToString();
                            oldcolDic[field] = col;
                            sortid++;
                            colSortDic[field] = sortid;
                        }
                    }
                    var cols = new List<JObject>();
                    var colSortDicDefault = new Dictionary<string, int>(StringComparer.OrdinalIgnoreCase);
                    sortid = 0;
                    foreach (XmlNode fieldNode in selectNode.SelectSingleNode("displayfields").ChildNodes)
                    {
                        var col = new JObject();
                        string field = null;
                        string mapper = null;
                        foreach (XmlAttribute attr in fieldNode.Attributes)
                        {
                            if (string.Equals(attr.Name, "mapper", StringComparison.OrdinalIgnoreCase))
                            {
                                mapper = attr.Value;
                                continue;
                            }
                            col.Add(attr.Name, attr.Value);
                            if (string.Equals(attr.Name, "field", StringComparison.OrdinalIgnoreCase))
                            {
                                field = attr.Value;
                            }
                        }
                        string dbField = null;
                        var title = fieldNode.InnerText.Trim();
                        if (field == "getrow(")
                        {
                            dbField = string.Empty;
                        }
                        else
                        {
                            dbField = parseResult.selectFieldsDic[field];
                        }
                        col.Add("title", title);
                        //handleCustomTitle(cmd, tokendata, col, dbField);
                        if (!string.IsNullOrEmpty(field) && oldcolDic.ContainsKey(field))
                        {
                            var oldInfo = oldcolDic[field];
                            foreach (var prop in oldInfo.Properties())
                            {
                                if (string.Equals(prop.Name, "field", StringComparison.OrdinalIgnoreCase))
                                {
                                    continue;
                                }
                                if (string.Equals(prop.Name, "title", StringComparison.OrdinalIgnoreCase))
                                {
                                    continue;
                                }
                                //col.Add(prop.Name, prop.Value);
                                col[prop.Name] = prop.Value;
                            }
                        }
                        if (mapper != null)
                        {
                            if (string.IsNullOrEmpty(mapper))
                            {
                                mapper = RemoveAS(dbField);
                                if (mapper.StartsWith("'") || !Regex.IsMatch(mapper, "[a-zA-Z]"))
                                {
                                    throw new LJCommonException($"列{col["field"]}对于常量字段，无法自动匹配Mapper，请直接指定具体Mapper");
                                }
                            }
                            var mappername = mapper;
                            if (mappername.Contains("."))
                            {
                                mappername = mappername.Substring(mappername.IndexOf(".") + 1);
                            }
                            var mapperMatched = false;
                            mapperMatched = File.Exists(rootPath + _mapperAppend + mappername + ".xml");
                            if (!mapperMatched)
                            {
                                mappername = mapper.Replace(".", "_");
                                mapperMatched = File.Exists(rootPath + _mapperAppend + mappername + ".xml");
                            }
                            if (!mapperMatched && col.ContainsKey("table"))
                            {
                                mappername = mapper;
                                if (mappername.Contains("."))
                                {
                                    mappername = mappername.Substring(mappername.IndexOf(".") + 1);
                                }
                                mappername = col["table"] + "_" + mappername;
                                mapperMatched = File.Exists(rootPath + _mapperAppend + mappername + ".xml");
                            }
                            if (!mapperMatched)
                            {
                                throw new LJCommonException($"列{col["field"]}未匹配到mapper对应的xml:{mapper}");
                            }
                            var mapperreq = new DynamicSelectRequest
                            {
                                queryparams = queryParams
                            };
                            var mapperrslt = GetXmlResult(_mapperAppend + mappername, mapperreq, true);
                            col.Add("enum", mapperrslt.datatable);
                        }
                        cols.Add(col);
                        sortid++;
                        colSortDicDefault[field] = sortid;
                    }
                    var colSort = cols.OrderBy(x =>
                    {
                        var field = x.GetValue("field")?.ToString();
                        if (colSortDic.ContainsKey(field))
                        {
                            return colSortDic[field];
                        }
                        else
                        {
                            return int.MaxValue;
                        }
                    }).ThenBy(x =>
                    {
                        var field = x.GetValue("field")?.ToString();
                        if (colSortDicDefault.ContainsKey(field))
                        {
                            return colSortDicDefault[field];
                        }
                        else
                        {
                            return int.MaxValue;
                        }
                    });
                    var colSortJArr = new JArray();
                    foreach (var col in colSort)
                    {
                        colSortJArr.Add(col);
                    }
                    preferenceobj["columns"] = colSortJArr;
                    rslt.tableinfo = preferenceobj;
                }
                catch (Exception ex)
                {
                    Trace.Write("处理用户习惯配置失败: " + ex.Message);
                    rslt.tableinfo = new JObject();
                }
            }

            return rslt;
        }
        /// <summary>
        /// 移除AS关键字
        /// </summary>
        /// <param name="oristr">原始字符串</param>
        /// <returns>处理后的字符串</returns>
        private string RemoveAS(string oristr)
        {
            var asindex = oristr.IndexOf(" as ", StringComparison.OrdinalIgnoreCase);
            if (asindex > 0)
            {
                return oristr.Substring(0, asindex);
            }
            return oristr;
        }

        #endregion
    }
}