/// <summary> /// 生成SQL语句 /// </summary> /// <param name="entity"></param> /// <param name="dicInputValues">输入项的值</param> /// <param name="paramList">参数列表</param> /// <returns></returns> public static string GenerateSQL(Entity entity, Dictionary <string, string> dicInputValues, out List <object> paramList) { paramList = new List <object>(); if (entity == null || string.IsNullOrWhiteSpace(entity.SQL_CODE)) { return(string.Empty); } List <BF_CHART_REPORT_FILTER.Entity> filterList = new List <BF_CHART_REPORT_FILTER.Entity>(); if (dicInputValues != null && dicInputValues.Count > 0) { filterList = BF_CHART_REPORT_FILTER.Instance.GetFilterList(entity.ID); } //执行函数 string sql = Common.FW.Functions.Instance.ExecuteFunction(entity.SQL_CODE); //替换SESSION变量 sql = SystemSession.TransParams(sql); //替换输入项 Regex regex = new Regex(@"@\((?<name>[a-z_0-9\-]+)\)", RegexOptions.IgnoreCase); Match match = regex.Match(sql); while (match != null && match.Success) { string name = match.Result("${name}"); if (dicInputValues != null && dicInputValues.ContainsKey(name)) { sql = sql.Replace("@(" + name + ")", dicInputValues[name]); } else { sql = sql.Replace("@(" + name + ")", "0"); } match = match.NextMatch(); } //替换筛选项 StringBuilder sbWhere = new StringBuilder(); sbWhere.AppendLine("WHERE 1=1"); int fc = 0; foreach (BF_CHART_REPORT_FILTER.Entity fe in filterList) { if (dicInputValues.ContainsKey(fe.FIELD_NAME) == false) { continue; } if (string.IsNullOrWhiteSpace(dicInputValues[fe.FIELD_NAME])) { continue; } fc++; string v1 = dicInputValues[fe.FIELD_NAME].Trim(); string v2 = string.Empty; Enums.FilterOperator oper = (Enums.FilterOperator)fe.FILTER_OPERATOR; if (oper == Enums.FilterOperator.介于) { string[] vs = v1.Split(new string[] { " - " }, StringSplitOptions.RemoveEmptyEntries); v1 = vs[0].Trim(); if (vs.Length > 1) { v2 = vs[1].Trim(); } else { v2 = v1; } } else { v2 = v1; } Enums.FieldDataType dataType = (Enums.FieldDataType)fe.FIELD_DATA_TYPE; switch (dataType) { case Enums.FieldDataType.数值: if (oper == Enums.FilterOperator.介于) { sbWhere.AppendLine("AND " + fe.FIELD_NAME + ">=?"); sbWhere.AppendLine("AND " + fe.FIELD_NAME + "<=?"); paramList.Add(Convert.ToDecimal(v1)); paramList.Add(Convert.ToDecimal(v2)); } else { sbWhere.AppendLine("AND " + fe.FIELD_NAME + "=?"); paramList.Add(Convert.ToDecimal(v1)); } break; case Enums.FieldDataType.日期: sbWhere.AppendLine("AND " + fe.FIELD_NAME + ">=?"); sbWhere.AppendLine("AND " + fe.FIELD_NAME + "<?"); paramList.Add(Convert.ToDateTime(v1).Date); paramList.Add(Convert.ToDateTime(v2).AddDays(1).Date); break; case Enums.FieldDataType.文本: if (oper == Enums.FilterOperator.包含) { sbWhere.AppendLine("AND " + fe.FIELD_NAME + " LIKE '%" + v1.Replace('\'', '"') + "%'"); } else { sbWhere.AppendLine("AND " + fe.FIELD_NAME + "=?"); paramList.Add(v1); } break; } } if (fc > 0) { sql = "SELECT * FROM (\r\n" + sql + "\r\n)\r\n" + sbWhere.ToString(); } return(sql); }
/// <summary> /// 转换SQL语句 /// </summary> /// <param name="sqlCode">原始SQL语句</param> /// <param name="queryString">URL中GET参数</param> /// <param name="inputList">自定义输入项(JSON串)</param> /// <param name="filterList">筛选项</param> /// <param name="paramList">参数列表(输出)</param> /// <returns>SQL语句</returns> private static void TransSQL(string sqlCode, string queryString, List <InputValueItem> inputList, List <QueryFilterItem> filterList, out string sql, out List <object> paramList, Order order = null) { sql = string.Empty; paramList = new List <object>(); if (string.IsNullOrWhiteSpace(sqlCode)) { return; } //执行函数 sql = Functions.Instance.ExecuteFunction(sqlCode); //替换SESSION变量 sql = SystemSession.TransParams(sql); //自定义输入参数 Dictionary <string, string> dicInputValues = new Dictionary <string, string>(); if (inputList != null) { foreach (InputValueItem input in inputList) { if (dicInputValues.ContainsKey(input.Name) == false) { dicInputValues.Add(input.Name, input.Value); } } } //替换输入项 Regex regex = new Regex(@"@\((?<name>[a-z_0-9\-]+)\)", RegexOptions.IgnoreCase); Match match = regex.Match(sql); while (match != null && match.Success) { string name = match.Result("${name}"); if (dicInputValues.ContainsKey(name) == true) { if (dicInputValues[name] == null) { sql = sql.Replace("@(" + name + ")", ""); } else { sql = sql.Replace("@(" + name + ")", dicInputValues[name]); } } else { Regex reg = new Regex("&" + name + @"=(?<value>[^\?&=]*)", RegexOptions.IgnoreCase); Match m = reg.Match("&" + queryString); if (m.Success == true) { sql = sql.Replace("@(" + name + ")", m.Result("${value}")); } else { throw new Exception("SQL语句中,@(" + name + ") 未找到定义"); } } match = match.NextMatch(); } //替换筛选项 if (filterList != null) { StringBuilder sbWhere = new StringBuilder(); sbWhere.AppendLine("WHERE 1=1"); int filterCount = 0; string fieldName = string.Empty; try { foreach (QueryFilterItem filter in filterList) { fieldName = filter.Field; if (string.IsNullOrWhiteSpace(filter.Value)) { continue; } filterCount++; Enums.FieldDataType dataType = (Enums.FieldDataType)filter.DataType; Enums.FilterOperator oper = (Enums.FilterOperator)filter.Operator; switch (dataType) { case Enums.FieldDataType.数值: switch (oper) { case Enums.FilterOperator.小于: sbWhere.AppendLine("AND " + filter.Field + "<?"); paramList.Add(Convert.ToDecimal(filter.Value)); break; case Enums.FilterOperator.小于等于: sbWhere.AppendLine("AND " + filter.Field + "<=?"); paramList.Add(Convert.ToDecimal(filter.Value)); break; case Enums.FilterOperator.大于: sbWhere.AppendLine("AND " + filter.Field + ">?"); paramList.Add(Convert.ToDecimal(filter.Value)); break; case Enums.FilterOperator.大于等于: sbWhere.AppendLine("AND " + filter.Field + ">=?"); paramList.Add(Convert.ToDecimal(filter.Value)); break; case Enums.FilterOperator.等于: sbWhere.AppendLine("AND " + filter.Field + ">=?"); paramList.Add(Convert.ToDecimal(filter.Value)); break; case Enums.FilterOperator.介于: sbWhere.AppendLine("AND " + filter.Field + ">? AND " + filter.Field + "<?"); string[] vdecs = filter.Value.Split(new string[] { " - " }, StringSplitOptions.RemoveEmptyEntries); paramList.Add(Convert.ToDecimal(vdecs[0])); paramList.Add(Convert.ToDecimal(vdecs[1])); break; case Enums.FilterOperator.集合: string[] vdecl = filter.Value.Split(new char[] { ',', ',', '\r', '\n', ' ' }, StringSplitOptions.RemoveEmptyEntries); foreach (string v in vdecl) { Convert.ToDecimal(v); } sbWhere.AppendLine("AND " + filter.Field + " IN (" + string.Join(",", vdecl) + ")"); break; default: sbWhere.AppendLine("AND " + filter.Field + "=?"); paramList.Add(Convert.ToDecimal(filter.Value)); break; } break; case Enums.FieldDataType.日期: string[] vdates = filter.Value.Split(new string[] { " - " }, StringSplitOptions.RemoveEmptyEntries); DateTime begin = Convert.ToDateTime(vdates[0]); DateTime end = begin.AddDays(1); if (vdates.Length > 1) { end = Convert.ToDateTime(vdates[1]).AddDays(1); } sbWhere.AppendLine("AND " + filter.Field + ">? AND " + filter.Field + "<?"); paramList.Add(begin); paramList.Add(end); break; case Enums.FieldDataType.文本: switch (oper) { case Enums.FilterOperator.包含: sbWhere.AppendLine("AND " + filter.Field + " LIKE '%" + filter.Value.Replace('\'', '"') + "%'"); break; case Enums.FilterOperator.集合: string[] vstrs = filter.Value.Split(new char[] { ',', ',', '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries); List <string> vstrl = new List <string>(); foreach (var s in vstrs) { vstrl.Add("'" + s.Replace('\'', '"') + "'"); } sbWhere.AppendLine("AND " + filter.Field + " IN (" + string.Join(",", vstrl) + ")"); break; default: sbWhere.AppendLine("AND " + filter.Field + "=?"); paramList.Add(Convert.ToString(filter.Value)); break; } break; } } } catch (Exception ex) { throw new Exception("筛选项:" + fieldName + "出错:" + ex.ToString()); } //if (filterCount > 0) //为了性能,在没有筛选项的情况下直接添加排序子句,但原SQL中有ORDER BY的话会有BUG //{ sql = "SELECT * FROM (\r\n" + sql + "\r\n)\r\n" + sbWhere.ToString(); if (order != null && string.IsNullOrWhiteSpace(order.Field) == false) { sql += " ORDER BY " + order.Field + (order.IsDesc ? " DESC" : " ASC"); } //} //else //{ // if (order != null && string.IsNullOrWhiteSpace(order.Field) == false) // { // sql += "\r\nORDER BY " + order.Field + (order.IsDesc ? " DESC" : " ASC"); // } //} } }