/// <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");
// }
//}
}
}