/// <summary>
/// 获取Where比较条件
/// </summary>
/// <param name="whereCompare"></param>
/// <param name="sampleRow"></param>
/// <returns></returns>
private string GetWhereCompare(string whereCompare, List <OrderByDictionary> orderByTypes, DataRow sampleRow, string unqueField, string unqueValue, bool isReverse = false, bool isEqual = false)
{
List <string> reval = new List <string>();
var ordersCount = orderByTypes.Count;
/*
* 排序筛选逻辑:
* 例如: num desc, createTime asc
* num>x
* num=x and createTime<xx
* num=x and createTime=xx and unqueField<unqueValue
* 以此类推
*/
//算出没有unqueField的部分
for (int i = 0; i < ordersCount; i++)
{
List <string> revalChild = new List <string>();
for (int j = 0; j <= i; j++)
{
var isLastEach = j == i;
OrderByDictionary currentOrderType = orderByTypes[j];
var currentSymbol = isReverse ? currentOrderType.SymbolReverse : currentOrderType.Symbol;
var fieldValue = sampleRow[currentOrderType.OrderByField];
fieldValue = FormatTimeByDataBase(fieldValue);
if (isLastEach)
{
revalChild.Add(string.Format(" {0}{1}'{2}' ", currentOrderType.OrderByField, currentSymbol, fieldValue));
}
else
{
revalChild.Add(string.Format(" {0}='{1}' ", currentOrderType.OrderByField, fieldValue));
}
}
reval.Add(" ( " + string.Join(" AND ", revalChild) + " ) ");
}
//算出unqueField的部分
List <string> revalChildLast = new List <string>();
for (int i = 0; i < ordersCount; i++)
{
OrderByDictionary currentOrderType = orderByTypes[i];
var fieldValue = sampleRow[currentOrderType.OrderByField];
fieldValue = FormatTimeByDataBase(fieldValue);
revalChildLast.Add(string.Format(" {0}='{1}' ", currentOrderType.OrderByField, fieldValue));
}
var isSymbol = isReverse ? ">" : "<";
if (isEqual)
{
isSymbol = isReverse ? ">=" : "<=";
}
revalChildLast.Add(string.Format("{0}{1}'{2}' ", unqueField, isSymbol, unqueValue));
reval.Add(" ( " + string.Join(" AND ", revalChildLast) + " ) ");
//将unqueField部分和非unqueField部分进行OR拼接
return(string.Join(" OR ", reval));
}
/// <summary>
/// 排序
/// </summary>
/// <param name="list"></param>
/// <param name="orderByTypes"></param>
/// <param name="orderByUnqueField"></param>
/// <returns></returns>
public static List <DataRow> OrderByDataRow(this IEnumerable <DataRow> list, List <OrderByDictionary> orderByTypes, OrderByDictionary orderByUnqueField)
{
orderByTypes.Add(orderByUnqueField);
var dt = list.AsEnumerable().CopyToDataTable();
var guidType = typeof(Guid);
var sqlGuidType = typeof(SqlGuid);
System.Data.DataTable dtByConvertGuidToSqlGuid = new System.Data.DataTable();
foreach (DataColumn it in dt.Columns)
{
var isGuid = it.DataType == guidType;
if (isGuid)
{
dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, sqlGuidType);
}
else
{
dtByConvertGuidToSqlGuid.Columns.Add(it.ColumnName, it.DataType);
}
}
//将dataTable中guid换转成sqlguid，这样排序才会和SQL一致
dtByConvertGuidToSqlGuid.Load(dt.CreateDataReader(), System.Data.LoadOption.OverwriteChanges);
var view = dtByConvertGuidToSqlGuid.AsDataView();
view.Sort = string.Join(",", orderByTypes.Select(it => string.Format(" {0} {1} ", it.OrderByField, it.IsAsc ? "ASC" : "DESC")));
var reval = view.ToTable().AsEnumerable().ToList();
orderByTypes.Remove(orderByUnqueField);
return(reval);
}
