public KeyValuePair <string, SugarParameter[]> ConditionalModelToSql(List <IConditionalModel> models, int beginIndex = 0)
{
if (models.IsNullOrEmpty())
{
return(new KeyValuePair <string, SugarParameter[]>());
}
StringBuilder builder = new StringBuilder();
List <SugarParameter> parameters = new List <SugarParameter>();
var sqlBuilder = InstanceFactory.GetSqlbuilder(this.Context.CurrentConnectionConfig);
var mainIndex = 0;
var indexTree = 0;
foreach (var model in models)
{
if (model is ConditionalModel)
{
var item = model as ConditionalModel;
if (item.FieldName == $"[value=sql{UtilConstants.ReplaceKey}]")
{
builder.Append(item.FieldValue);
continue;
}
var index = mainIndex + beginIndex;
var type = index == 0 ? "" : "AND";
if (beginIndex > 0)
{
type = null;
}
string temp = " {0} {1} {2} {3} ";
string parameterName = string.Format("{0}Condit{1}{2}", sqlBuilder.SqlParameterKeyWord, item.FieldName, index);
if (parameterName.Contains("."))
{
parameterName = parameterName.Replace(".", "_");
}
if (parameterName.Contains("["))
{
parameterName = parameterName.Replace("[", "_");
}
if (parameterName.Contains("]"))
{
parameterName = parameterName.Replace("]", "_");
}
if (parameterName.Contains(this.SqlTranslationLeft))
{
parameterName = parameterName.Replace(this.SqlTranslationLeft, "_");
}
string oldName = item.FieldName;
item.FieldName = GetTranslationColumnName(item.FieldName);
switch (item.ConditionalType)
{
case ConditionalType.Equal:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.Like:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.GreaterThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.GreaterThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), ">=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.LessThan:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.LessThanOrEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
break;
case ConditionalType.In:
if (item.FieldValue == null)
{
item.FieldValue = string.Empty;
}
var inValue1 = ("(" + item.FieldValue.Split(',').ToJoinSqlInVals() + ")");
if (item.CSharpTypeName.HasValue() && UtilMethods.IsNumber(item.CSharpTypeName))
{
inValue1 = inValue1.Replace("'", "");
}
else if (inValue1.Contains("'null'"))
{
inValue1 = inValue1.Replace("'null'", "null");
}
else if (inValue1.Contains("[null]"))
{
inValue1 = inValue1.Replace("[null]", "null");
}
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "IN", inValue1);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.NotIn:
if (item.FieldValue == null)
{
item.FieldValue = string.Empty;
}
var inValue2 = ("(" + item.FieldValue.Split(',').ToJoinSqlInVals() + ")");
if (item.CSharpTypeName.HasValue() && UtilMethods.IsNumber(item.CSharpTypeName))
{
inValue2 = inValue2.Replace("'", "");
}
else if (inValue2.Contains("'null'"))
{
inValue2 = inValue2.Replace("'null'", "null");
}
else if (inValue2.Contains("[null]"))
{
inValue2 = inValue2.Replace("[null]", "null");
}
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "NOT IN", inValue2);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.LikeLeft:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue + "%"));
break;
case ConditionalType.NoLike:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " NOT LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue + "%"));
break;
case ConditionalType.LikeRight:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "LIKE", parameterName);
parameters.Add(new SugarParameter(parameterName, "%" + item.FieldValue));
break;
case ConditionalType.NoEqual:
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<>", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.IsNullOrEmpty:
builder.AppendFormat(" {0} (({1}) OR ({2})) ", type, item.FieldName.ToSqlFilter() + " IS NULL ", item.FieldName.ToSqlFilter() + " = '' ");
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
break;
case ConditionalType.IsNot:
if (item.FieldValue == null)
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " IS NOT ", "NULL");
}
else
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "<>", parameterName);
parameters.Add(new SugarParameter(parameterName, item.FieldValue));
}
break;
case ConditionalType.EqualNull:
if (GetFieldValue(item) == null)
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), " IS ", " NULL ");
}
else
{
builder.AppendFormat(temp, type, item.FieldName.ToSqlFilter(), "=", parameterName);
parameters.Add(new SugarParameter(parameterName, GetFieldValue(item)));
}
break;
case ConditionalType.InLike:
var array = (item.FieldValue + "").Split(',').ToList();
List <string> sqls = new List <string>();
int i = 0;
foreach (var val in array)
{
var itemParameterName = $"{ parameterName}{index}{i}";
sqls.Add(item.FieldName.ToSqlFilter() + " LIKE " + itemParameterName);
parameters.Add(new SugarParameter(itemParameterName, "%" + val + "%"));
i++;
}
builder.Append($" {type} ({string.Join(" OR ", sqls)}) ");
break;
default:
break;
}
item.FieldName = oldName;
}
else if (model is ConditionalCollections)
{
var item = model as ConditionalCollections;
if (item != null && item.ConditionalList.HasValue())
{
foreach (var con in item.ConditionalList)
{
var index = item.ConditionalList.IndexOf(con);
var isFirst = index == 0;
var isLast = index == (item.ConditionalList.Count - 1);
if (models.IndexOf(item) == 0 && index == 0 && beginIndex == 0)
{
builder.AppendFormat(" ( ");
}
else if (isFirst)
{
builder.AppendFormat(" {0} ( ", con.Key.ToString().ToUpper());
}
List <IConditionalModel> conModels = new List <IConditionalModel>();
conModels.Add(con.Value);
var childSqlInfo = ConditionalModelToSql(conModels, 1000 * (1 + index) + models.IndexOf(item));
if (!isFirst && con.Value.FieldName != $"[value=sql{UtilConstants.ReplaceKey}]")
{
builder.AppendFormat(" {0} ", con.Key.ToString().ToUpper());
}
builder.Append(childSqlInfo.Key);
parameters.AddRange(childSqlInfo.Value);
if (isLast)
{
builder.Append(" ) ");
}
else
{
}
}
}
}
else
{
var item = model as ConditionalTree;
BuilderTree(builder, item, ref indexTree, parameters, ref mainIndex);
}
mainIndex++;
}
return(new KeyValuePair <string, SugarParameter[]>(builder.ToString(), parameters.ToArray()));
}
