/// <summary>
/// 使用SearchParamerters 自动追加 sql 的 where 条件, 并用占位符处理value 例: `ORDER_ID`=@OrderId
/// 并根据PageInfo信息生成sql分页内容
/// </summary>
/// <param name="sql">sqlkey</param>
/// <param name="searchParameters">查询条件</param>
/// <returns>返回一个组织好的sql语句</returns>
private string GetSqlBySearchParameter(string sql, SearchParameters searchParameters)
{
StringBuilder sb = new StringBuilder(sql);
if (searchParameters.QueryModel.Items.Count > 0)
{
sb.Append(" where " + ConvertToSqlWhere(searchParameters, DbType.MySql));
}
if (string.IsNullOrEmpty(searchParameters.PageInfo.SortDirection))
{
if (!string.IsNullOrEmpty(searchParameters.PageInfo.SortField))
{
sb.Append(" Order by ");
//如果是多字段排序，则会把排序字段和排序方式记录到sort上，dir为空
char[] delimiters = { ',' };
string[] sorts =
searchParameters.PageInfo.SortField.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);
var mapDic = DbColumnMaps.GetDbContextFiledMaps();
for (int i = 0; i < sorts.Length; i++)
{
string[] values = sorts[i].Trim().Split(' ');
string sortName = values[0];
if (mapDic.ContainsKey(sortName))
{
sortName = mapDic[sortName];
}
string sortDir = values.Length == 2 ? values[1] : "ASC";
if (i == sorts.Length - 1)
{
sb.Append(" " + sortName + " " + sortDir + " ");
}
else
{
sb.Append(" " + sortName + " " + sortDir + ", ");
}
}
}
}
else
{
var mapDic = DbColumnMaps.GetDbContextFiledMaps();
var sortName = searchParameters.PageInfo.SortField;
if (mapDic.ContainsKey(sortName))
{
sortName = mapDic[sortName];
}
sb.Append(" Order by " + sortName + " " + searchParameters.PageInfo.SortDirection + " ");
}
return(sb.ToString());
}
/// <summary>
/// 根据QueryModel组织sqlWhere语句,如果有字段前缀的话,需要提前增加进来
/// 如果是Dapper 需要转换成
/// </summary>
/// <returns>Where 语句</returns>
public string ConvertToSqlWhere(SearchParameters searchParameters, DbType dbType)
{
if (searchParameters == null)
{
return("");
}
//处理sql查询时，查询null值问题
searchParameters.BuildEmptySearch();
// 复制一个,避免修改的时候影响外部数据
var copyCondition = JsonEx.JsonConvert.CopyObject(searchParameters.QueryModel.Items);
copyCondition.Sort((a, b) =>
{
return(a.Field.CompareTo(b.Field));
});
var sb = new StringBuilder();
List <string> groups = new List <string>();
// 如果是Dapper 则把Condition 的 Field 和 Value 替换掉
var mapDic = DbColumnMaps.GetDbContextFiledMaps();
for (int i = 0; i < copyCondition.Count; i++)
{
var item = copyCondition[i];
if (dbType == DbType.Oracle)
{
// Oracle 字段需要前后双引号
item.Value = "\"" + Prefix + item.Field + i + "\"";
}
else if (item.Value != null)
{
item.Value = Prefix + item.Field + i;
}
if (mapDic.ContainsKey(item.Field))
{
item.Field = mapDic[item.Field];
}
}
foreach (var conditionItem in copyCondition)
{
if (!string.IsNullOrEmpty(conditionItem.OrGroup))
{
if (!groups.Contains(conditionItem.OrGroup))
{
var sbChild = new StringBuilder();
foreach (var senItem in copyCondition)
{
if (senItem.OrGroup == conditionItem.OrGroup)
{
if (sbChild.Length > 0)
{
sbChild.Append(" or ");
}
sbChild.Append(GetQueryCloumn(senItem) + " " + ConvertMethodToSql(senItem.Method, senItem.Value));
}
}
if (sb.Length > 0)
{
sb.Append(" and ");
}
sb.Append("(" + sbChild.ToString() + ")");
groups.Add(conditionItem.OrGroup);
}
}
else
{
if (sb.Length > 0)
{
sb.Append(" and ");
}
sb.Append((string.IsNullOrEmpty(conditionItem.Prefix) ? "" : (conditionItem.Prefix + ".")) + conditionItem.Field + " " + ConvertMethodToSql(conditionItem.Method, conditionItem.Value));
}
}
return(sb.ToString());
}
