JLH/JLHHJSvr/Excutor/CommonDynamicSelectExcutor.cs

using DirectService.Tools;
using JLHHJSvr.BLL;
using JLHHJSvr.Com;
using JLHHJSvr.LJException;
using LJLib.DAL.SQL;
using LJLib.Net.SPI.Server;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
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;

namespace JLHHJSvr.Excutor
{
    internal class CommonDynamicSelectExcutor : ExcutorBase<CommonDynamicSelectRequest, CommonDynamicSelectResponse>
    {
        protected override void ExcuteInternal(CommonDynamicSelectRequest request, object state, CommonDynamicSelectResponse rslt)
        {
            var tokendata = BllHelper.GetToken(request.token);
            if (tokendata == null)
            {
                rslt.ErrMsg = "会话已经中断";
                return;
            }
            if (request.queryparams == null)
            {
                throw new ArgumentNullException("queryparams");
            }
            if (string.IsNullOrEmpty(request.dsname))
            {
                throw new ArgumentNullException("dsname");
            }

            using (var con = new SqlConnection(GlobalVar.ConnectionString))
            using (var cmd = con.CreateCommand())
            {
                con.Open();

                var rsltgroup = GetXmlResult(cmd, request.dsname, request, tokendata, false);
                rslt.datatable = rsltgroup.datatable;
                rslt.tableinfo = rsltgroup.tableinfo;
                rslt.totalcnt = rsltgroup.totalcnt;
                rslt.pageindex = rsltgroup.pageindex;
                rslt.pagesize = rsltgroup.pagesize;
            }
        }
        private const string _mapperAppend = "_Mapper_";
        private Regex _staticParmReg = new Regex("[\\$]+[\\w]+[\\$]*");
        private Regex _listParmReg = new Regex("@@[\\w]+@@");
        private Dictionary<string, string> _xmlEscapeMap = new Dictionary<string, string>
        {
            {"&", "&amp;"},
            {"'", "&apos;"},
            {"\"", "&quot;"},
            {">", "&gt;"},
            {"<", "&lt;"},
        };
        private Regex _computeRefReg = new Regex("([A-Za-z_][\\w]+)([\\s]*)([(]*)");
        private QueryResult GetXmlResult(SqlCommand cmd, string dsname, CommonDynamicSelectRequest request, TokenData tokendata, 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 (var match in staticMatches)
                    {
                        var matchstr = match.ToString();
                        staticMatchesStr.Add(matchstr);
                        if (matchstr.StartsWith("$$"))
                        {

                        }
                        else if (matchstr.StartsWith("$"))
                        {
                            var newname = "@" + matchstr.Substring(1);
                        }
                    }
                    //$$开头$$结束为全局数组变量，直接作字符串替换
                    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")
                            {
                                var getPower = UserHelper.getPowerDept(cmd, tokendata.empid);
                                if (getPower.Count == 0) getPower.Add(-1);
                                staticVal = ListEx.getString(getPower);
                            }else if (staticParm == "user_outrepstr")
                            {
                                var getPower = UserHelper.getPowerOutRep(cmd, tokendata.empid);
                                if (getPower.Count == 0) getPower.Add("");
                                staticVal = ListEx.getString(getPower);
                            }

                            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", 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 (var match in listParmMatches)
                        {
                            var matchstr = match.ToString();
                            var pname = matchstr.Trim('@');
                            if (!queryParams.ContainsKey(pname))
                            {
                                continue;
                                //throw new LJCommonException($"未提供参数" + pname);
                            }
                            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);
            }

            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)
                {
                }
                else
                {
                    parmDic[item.Key] = item.Value.ToString();
                }
            }

            var displayfields = selectNode.SelectSingleNode("displayfields");
            var uncomputefields = new Dictionary<string, string>();
            string rownumfield = null;
            foreach (XmlNode fieldNode in displayfields)
            {
                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)}]");
                }
            }
            if (!string.IsNullOrEmpty(request.orderstr?.Trim()))
            {
                var orderArr = request.orderstr.Split(',');
                var orderlist = new List<string>();
                foreach (var orderitem in orderArr)
                {
                    var orderfield = orderitem.Trim();
                    var descIndex = orderfield.IndexOf(" desc");
                    if (descIndex > 0)
                    {
                        orderfield = orderfield.Substring(0, descIndex).Trim();
                    }
                    var ascIndex = orderfield.IndexOf(" asc");
                    if (ascIndex > 0)
                    {
                        orderfield = orderfield.Substring(0, ascIndex).Trim();
                    }
                    if (!parseResult.selectFieldsDic.ContainsKey(orderfield))
                    {
                        throw new LJCommonException($"排序失败，数据列中不包含[{orderfield}]");
                    }
                    var dbfield = parseResult.selectFieldsDic[orderfield];
                    dbfield = RemoveAS(dbfield);
                    if (descIndex > 0)
                    {
                        dbfield += " desc";
                    }
                    orderlist.Add(dbfield);
                }
                orderstr = string.Join(",", orderlist);
            }
            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 dbcolnames = parseResult.selectFieldsDic.Keys.ToList();
            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 < dbcolnames.Count; i++)
                    {
                        var p = dbcolnames[i];
                        var dbval = reader[p];
                        if (ifmapper)
                        {
                            if (i == 0)
                            {
                                p = "value";
                            }
                            else if (i == 1)
                            {
                                p = "label";
                            }
                        }
                        if (dbval == DBNull.Value)
                        {
                            row.Add(p, null);
                        }
                        else if (dbval is string)
                        {
                            row.Add(p, (dbval as string).TrimEnd());
                        }
                        else if (dbval is DateTime)
                        {
                            row.Add(p, (DateTime)dbval);
                        }
                        else
                        {
                            row.Add(p, 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)
            {
                var preferenceobj = new JObject();
                var preferencejson = BllHelper.GetValue(cmd,tokendata.userid, request.dwname, request.itemname, string.Empty, request.ifcompress == 1 ? true : false);
                if (string.IsNullOrEmpty(preferencejson))//如果没有自己的布局方案，尝试获取系统的布局方案
                {
                    preferencejson = BllHelper.GetValue(cmd,-1, request.dwname, request.itemname, string.Empty, request.ifcompress == 1 ? true : false);
                }
                //preferencejson = "{tableprop:{enabled:false,oSize:0,mode:\"default\"},columns:[{field:\"printid\",search:{order:1,labelposition:\"left\"}}]}";
                if (!string.IsNullOrEmpty(preferencejson))
                {
                    try
                    {
                        preferenceobj = JsonConvert.DeserializeObject<JObject>(preferencejson);
                    }
                    catch (Exception e)
                    {
                        Trace.Write("解析json失败" + preferenceobj);
                    }
                }
                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 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 == rownumfield)
                    {
                        dbField = string.Empty;
                    }
                    else if (parseResult.selectFieldsDic.ContainsKey(field))
                    {
                        dbField = parseResult.selectFieldsDic[field];
                    }
                    else
                    {
                        throw new LJCommonException("语法错误：" + field + ":" + title + "，输出项无法匹配对应sql数据");
                    }
                    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 CommonDynamicSelectRequest
                        {
                            queryparams = queryParams
                        };
                        var mapperrslt = GetXmlResult(cmd, _mapperAppend + mappername, mapperreq, tokendata, 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;
            }
            return rslt;
        }
        private void handleCustomTitle(SqlCommand cmd, TokenData tokenData, JObject col, string dbfield)
        {
            var field = col.GetValue("field")?.ToString();
            var title = col.GetValue("title")?.ToString();
            var custitle = title;
            var visibleLimited = false;
            // 处理标题文本
            /*if (field.Contains("status") && title.Contains("配置"))
            {
                var opTitle = OptionHelper.GetOpString(cmd, "029");
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = custitle.Replace("配置", opTitle);
                }
            }
            else if (field.Contains("woodcode") && title.Contains("配置1"))
            {
                var opTitle = OptionHelper.GetOpString(cmd, "027");
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = custitle.Replace("配置1", opTitle);
                }
            }
            else if (field.Contains("pcode") && title.Contains("配置2"))
            {
                var opTitle = OptionHelper.GetOpString(cmd, "028");
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = custitle.Replace("配置2", opTitle);
                }
            }
            else if (field.Contains("mtrlsectype"))
            {
                var opTitle = OptionHelper.GetOpString(cmd, "041");
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = opTitle;
                }
            }
            else if (field.Contains("zxmtrlmode"))
            {
                var opTitle = OptionHelper.GetOpString(cmd, "042");
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = opTitle;
                }
            }
            else if (field.Contains("usermtrlmode"))
            {
                var opTitle = OptionHelper.GetOpString(cmd, "128");
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = opTitle;
                }
            }
            else if (field.Contains("otheramt"))
            {
                string opTitle = null;
                if (dbfield.Contains("u_outware.otheramt") || dbfield.Contains("u_saletask.otheramt"))
                {
                    opTitle = OptionHelper.GetOpString(cmd, "050");
                }
                else if (dbfield.Contains("u_inware.otheramt"))
                {
                    opTitle = OptionHelper.GetOpString(cmd, "136");
                }
                if (!string.IsNullOrEmpty(opTitle))
                {
                    custitle = opTitle;
                }
            }
            else if (field.Contains("excolumn"))
            {
                var dbcolname = field;
                var inputType = 0;
                dbcolname = RemoveAS(dbcolname);
                if (dbcolname.Contains("."))
                {
                    dbcolname = dbcolname.Substring(dbcolname.LastIndexOf(".") + 1);
                }
                cmd.CommandText = @"SELECT replacename,inputtype,ifuse FROM u_ext_billcolumn where ifuse = 1 AND columnname = @columnname";
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@columnname", dbcolname);
                using (var reader = cmd.ExecuteReader())
                {
                    if (reader.Read() && Convert.ToInt32(reader["ifuse"]) == 1)
                    {
                        custitle = reader["replacename"].ToString().Trim();
                        inputType = Convert.ToInt32(reader["inputtype"]);
                    }
                    else
                    {
                        visibleLimited = true;
                    }
                }
                switch (inputType)
                {
                    case 0: // 未指定
                        break;
                    case 1: // 数值
                        col["datatype"] = "number";
                        break;
                    case 2: // 文本
                        break;
                    case 3: // 日期
                        col["datatype"] = "date";
                        break;
                    case 4: // 复合
                        break;
                    default:
                        break;
                }
            }*/
            if (!string.Equals(title, custitle))
            {
                col["title"] = custitle;
            }
            //处理显示权限
            if (!col.ContainsKey("funcid"))
            {
                if (dbfield.Contains("u_spt.name") || dbfield.Contains("sptname"))
                {
                    if (!title.Contains("甲方名称"))
                    {
                        col.Add("funcid", 6535);
                    }
                }
            }
            if (col.ContainsKey("funcid"))
            {
                var funcid = Convert.ToInt32(col.GetValue("funcid").ToString());
                if (!FuncPowerHelper.CheckFuncPower(cmd, tokenData.userid, funcid))
                {
                    visibleLimited = true;
                }
            }
            if (col.ContainsKey("funcid_notequals"))
            {
                var funcid = Convert.ToInt32(col.GetValue("funcid_notequals").ToString());
                if (FuncPowerHelper.CheckFuncPower(cmd, tokenData.userid, funcid))
                {
                    visibleLimited = true;
                }
            }
            if (col.ContainsKey("onlyusermode"))
            {
                var usermode = Convert.ToInt32(col.GetValue("onlyusermode").ToString());
                if (tokenData.usermode != usermode)
                {
                    visibleLimited = true;
                }
            }
            if (col.ContainsKey("usermode_notequals"))
            {
                var usermode = Convert.ToInt32(col.GetValue("usermode_notequals").ToString());
                if (tokenData.usermode == usermode)
                {
                    visibleLimited = true;
                }
            }
            if (visibleLimited)
            {
                col.Add("limited", true);
            }
        }
        private string RemoveAS(string oristr)
        {
            var asindex = oristr.IndexOf(" as ", StringComparison.OrdinalIgnoreCase);
            if (asindex > 0)
            {
                return oristr.Substring(0, asindex);
            }
            return oristr;
        }
        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; }
        }
    }
}