omega_logic/Util/Logic/LogicDatabaseNodeHelper.cs

using Infrastructure;
using LitJson;
using Model;
using Model.Custom;
using Model.Source;

namespace Util.Logic
{
    public class LogicDatabaseNodeHelper
    {
        /// <summary>
        /// 数据库查询节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="tables">数据库表列表</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbQuery(NodeList node, List<DatabaseInfo> databaseInfos, List<DatabaseTable> tables, List<ParamList> paramList)
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData queryField = nodeData["data"]["formData"]["output"]["value"]; // 查询字段
            JsonData conditions = nodeData["data"]["formData"]["ifelse"]["value"][0]["conditions"]; // 条件
            JsonData sortConditions = nodeData["data"]["formData"]["sort"]["value"]; // 条件
            string queryLimit = nodeData["data"]["formData"]["queryLimit"]["value"].ToString(); // 查询上限
            string logicTag = nodeData["data"]["formData"]["ifelse"]["value"][0]["logic"].ToString(); // 逻辑
            string queryFieldString = "";
            foreach (JsonData field in queryField)
            {
                string fieldName = field["label"].ToString(); // 字段名
                queryFieldString += fieldName + ",";
            }
            if(string.IsNullOrEmpty(queryFieldString)) queryFieldString = "*";
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            int dbTableId = int.Parse(dbInfo["dbTableId"].ToString()); // 数据库表ID
            var queryTable = tables.FirstOrDefault(x => x.id == dbTableId);
            if (queryTable != null)
            {
                string tableName = queryTable.tableName ?? string.Empty; // 数据库表名
                string sql = "select " + queryFieldString.TrimEnd(',') + " from " + tableName;
                if (conditions.Count > 0)
                {
                    sql += " where ";
                }
                foreach (JsonData condition in conditions)
                {
                    string oper = condition["operator"].ToString(); // 运算符
                    string value = condition["value"].ToString(); // 值
                    string variable = condition["variable"].ToString(); // 变量
                    if (variable.Contains(".") && variable.Contains("output"))
                    {
                        variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        var param = paramList.FirstOrDefault(x => x.paramName == variable) ?? new ParamList();
                        string paramValue = param.paramValue;
                        sql += operatorTransferForSql(param, oper, paramValue, variable);
                        sql += " " + logicTag + " ";
                    }
                }
                if (sql.EndsWith(" " + logicTag + " "))
                {
                    sql = sql.Substring(0, sql.Length - logicTag.Length - 2);
                }
                if (sortConditions.Count > 0)
                {
                    sql += " order by ";
                    foreach (JsonData sortCondition in sortConditions)
                    {
                        string variable = sortCondition["variable"].ToString(); // 变量
                        if (variable.Contains("."))
                        {
                            variable = variable.Substring(variable.LastIndexOf(".") + 1);
                        }
                        string sort = sortCondition["sort"].ToString(); // 排序
                        sql += variable + " " + sort + ",";
                    }
                    sql = sql.TrimEnd(',');
                }
                if (!string.IsNullOrEmpty(queryLimit))
                {
                    sql += " limit " + queryLimit;
                }
                SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo());
                var items = db.Ado.GetDataTable(sql);
                if (items.Rows.Count > 0)
                { 
                    foreach (JsonData field in queryField)
                    {
                        string fieldName = field["label"].ToString(); // 字段名
                        if (nodeData.ToJson().Contains("\"parentNode\""))
                        { 
                            var parentParam = paramList.FirstOrDefault(x => x.paramName == fieldName && x.paramAttribute == "iterationInputField");
                            if(parentParam != null)
                            {
                                parentParam.paramValue = items.Rows[0][fieldName]?.ToString() ?? string.Empty;
                            }
                        }
                        else
                        {
                            paramList.Add(new ParamList()
                            {
                                nodeId = node.nodeId,
                                paramName = fieldName,
                                paramValue = items.Rows[0][fieldName]?.ToString() ?? string.Empty,
                                paramType = field["type"][0].ToString(),
                            });
                        }
                    }
                }
            }
        }

        /// <summary>
        /// 数据库SQL查询节点
        /// </summary>
        /// <param name="node">节点数据</param>
        /// <param name="dbData">数据库信息</param>
        /// <param name="paramList">参数列表</param>
        /// <param name="parentNodeParamList">父节点参数列表</param>
        public static void dbSqlQuery(NodeList node, List<DatabaseInfo> databaseInfos, List<ParamList> paramList)
        { 
            JsonData nodeData = node.nodeData;
            JsonData dbInfo = nodeData["data"]["formData"]["dbLabel"]["value"]; // 数据库查询信息
            JsonData queryField = nodeData["data"]["formData"]["output"]["value"]; // 查询字段
            string sql = nodeData["data"]["formData"]["sql"]["value"]["code"]["content"].ToString(); // SQL查询语句
            int dbId = int.Parse(dbInfo["dbId"].ToString()); // 数据库ID
            SqlSugarClient db = initDb(databaseInfos.FirstOrDefault(x => x.id == dbId) ?? new DatabaseInfo());
            var items = db.Ado.GetDataTable(sql);
            if (items.Rows.Count > 0)
            {  
                foreach (JsonData field in queryField)
                {
                    string fieldName = field["label"].ToString(); // 字段名
                    if (nodeData.ToJson().Contains("\"parentNode\""))
                    { 
                        var parentParam = paramList.FirstOrDefault(x => x.paramName == fieldName && x.paramAttribute == "iterationInputField");
                        if(parentParam != null)
                        {
                            parentParam.paramValue = items.Rows[0][fieldName]?.ToString() ?? string.Empty;
                        }
                    }
                    else
                    {
                        paramList.Add(new ParamList()
                        {
                            nodeId = node.nodeId,
                            paramName = fieldName,
                            paramValue = items.Rows[0][fieldName]?.ToString() ?? string.Empty,
                            paramType = field["type"][0].ToString(),
                        });
                    }
                }
            }
        }

        public static SqlSugarClient initDb(DatabaseInfo dbInfo)
        {
            string server = dbInfo.hostAddress ?? string.Empty;
            int port = dbInfo.port;
            string user = dbInfo.username ?? string.Empty;
            string password = dbInfo.pwd ?? string.Empty;
            string database = dbInfo.dbName ?? string.Empty;
            DbType dbType = DbType.MySql;
            var db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = "server=" + server + ";port=" + port + ";user=" + user + ";password=" + password + ";database=" + database + ";charset=utf8;",
                DbType = dbType,
                IsAutoCloseConnection = true,
            });
            return db;
        }

        /// <summary>
        /// 运算符转换（SQL）
        /// </summary>
        /// <param name="oper">运算符</param>
        /// <returns>转换后的运算符</returns>
        public static string operatorTransferForSql(ParamList param, string oper, string paramValue, string fieldName)
        {
            if (oper == "equal") //等于
            { 
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return fieldName + "='" + paramValue + "'";
                }
                return fieldName + "=" + paramValue;
            }
            else if (oper == "not_equal") //不等于
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    return fieldName + "!='" + paramValue + "'";
                }
                return fieldName + "!=" + paramValue;
            }
            else if (oper == "greater") //大于
            {
                return fieldName + ">" + paramValue;
            }
            else if (oper == "greater_equal") //大于等于
            {
                return fieldName + " >= " + paramValue;
            }
            else if (oper == "less") //小于
            {
                return fieldName + " < " + paramValue;
            }
            else if (oper == "less_equal") //小于等于
            {
                return fieldName + " <= " + paramValue;
            }
            else if (oper == "between") //在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return fieldName + " >= " + betweenValues[0] + " and " + fieldName + " <= " + betweenValues[1];
            }
            else if (oper == "not_between") //不在之间
            {
                string[] betweenValues = paramValue.Split(',');
                return "(" + fieldName + " < " + betweenValues[0] + " or " + fieldName + " > " + betweenValues[1] + ")";
            }
            else if (oper == "contains") //包含
            {
                return fieldName + " like '%" + paramValue + "%'";
            }
            else if (oper == "not_contains") //不包含
            {
                return fieldName + " not like '%" + paramValue + "%'";
            }
            else if (oper == "starts_with") //以...开头
            {
                return fieldName + " like '" + paramValue + "%'";
            }
            else if (oper == "ends_with") //以...结尾
            {
                return fieldName + " like '%" + paramValue + "'";
            }
            else if (oper == "is_empty")
            {
                return fieldName + " is null";
            }
            else if (oper == "is_not_empty") //不为空
            {
                return fieldName + " is not null";
            }
            else if (oper == "is_null") //为空
            {
                return fieldName + " is null";
            }
            else if (oper == "is_not_null") //不为空
            {
                return fieldName + " is not null";
            }
            else if (oper == "in") //在...中
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " in (" + paramValue + ")";
            }
            else if (oper == "not_in") //不在...中
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " not in (" + paramValue + ")";
            }
            else if (oper == "array_contains") //数组包含
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " in ('" + paramValue + "')";
                }
                return fieldName + " in (" + paramValue + ")";
            }
            else if (oper == "array_not_contains") //数组不包含 
            {
                if(param.paramType == "string" || param.paramType == "time")
                {
                    paramValue = paramValue.Replace(",", "','");
                    return fieldName + " not in ('" + paramValue + "')";
                }
                return fieldName + " not in (" + paramValue + ")";
            }
            else if (oper == "array_length_equal") //数组长度等于
            {
                return "Length(" + fieldName + ")=" + paramValue;
            }
            else if (oper == "array_length_greater")
            {
                return "Length(" + fieldName + ") > " + paramValue;
            }
            else if (oper == "array_length_less") //数组长度小于
            {
                return "Length(" + fieldName + ") < " + paramValue;
            }
            else if (oper == "is_true") //为真
            {
                return fieldName + "=true";
            }
            else if (oper == "is_false") //为假
            {
                return fieldName + "=false";
            }
            else if (oper == "date_equal") //日期等于
            {
                return fieldName + "='" + paramValue + "'";
            }
            else if (oper == "date_before") //日期之前
            {
                return fieldName + " < '" + paramValue + "'";
            }
            else if (oper == "date_after") //日期之后
            {
                return fieldName + " > '" + paramValue + "'";
            }
            else if (oper == "date_between") //日期之间
            {
                string[] betweenValues = paramValue.Split(" - ", StringSplitOptions.None);
                return fieldName + " >= '" + betweenValues[0] + "' and " + fieldName + " <= '" + betweenValues[1] + "'";
            }
            return "";
        }
    
    }
}