public virtual Tuple <SqlQuery, SqlQuery> GetSearch(SearchArgs <TEntity> searchArgs)
{
var sqlQuery = InitBuilderSelect(false);
//var joinsBuilder = AppendJoinToSelect(sqlQuery);
sqlQuery.SqlBuilder
.Append(" FROM ")
.Append(TableName)
.Append(" ");
//if (includes.Any())
// sqlQuery.SqlBuilder.Append(joinsBuilder);
var parameters = new DynamicParameters();
parameters.Add(nameof(searchArgs.Keyword), searchArgs.Keyword);
parameters.Add(nameof(searchArgs.PageIndex), searchArgs.PageIndex);
parameters.Add(nameof(searchArgs.PageSize), searchArgs.PageSize);
var fuzzyConditions = new List <string>(50);
var conditions = new List <string>(50);
var dbConcatChar = GetDbConcatChar();
if (searchArgs.Model != null || !string.IsNullOrWhiteSpace(searchArgs.Keyword))
{
foreach (var propertyMetadata in SqlProperties)
{
//模糊搜索
if (!string.IsNullOrWhiteSpace(searchArgs.Keyword) && propertyMetadata.PropertyInfo.PropertyType == typeof(string))
{
fuzzyConditions.Add($"{propertyMetadata.ColumnName} LIKE '%'{dbConcatChar}@{nameof(searchArgs.Keyword)}{dbConcatChar}'%'");
}
if (searchArgs.Model != null)
{
var value = propertyMetadata.PropertyInfo.GetValue(searchArgs.Model);
if (!value.IsNullOrEmptyString())
{
conditions.Add($"{propertyMetadata.ColumnName} = @{propertyMetadata.PropertyName}");
parameters.Add(propertyMetadata.PropertyName, value);
}
}
}
}
var appendWhere = false;
if (conditions.Count > 0)
{
sqlQuery.SqlBuilder.Append("WHERE ").Append(string.Join(" AND ", conditions));
appendWhere = true;
}
if (fuzzyConditions.Count > 0)
{
sqlQuery.SqlBuilder.Append(appendWhere ? " AND " : " WHERE ");
sqlQuery.SqlBuilder.Append("(").Append(string.Join(" OR ", fuzzyConditions)).Append(")");
appendWhere = true;
}
if (searchArgs.CustomConditions != null && searchArgs.CustomConditions.Count > 0)
{
sqlQuery.SqlBuilder.Append(appendWhere ? " AND " : " WHERE ");
for (int i = 0; i < searchArgs.CustomConditions.Count; i++)
{
var condition = searchArgs.CustomConditions[i];
sqlQuery.SqlBuilder.Append(GetCustomConditionSql(condition, parameters));
if (i != searchArgs.CustomConditions.Count - 1)
{
sqlQuery.SqlBuilder.Append(" AND ");
}
}
}
if (searchArgs.OrderConditions != null && searchArgs.OrderConditions.Count > 0)
{
sqlQuery.SqlBuilder.Append(GetOrderByClause(searchArgs.OrderConditions));
}
else //没有自定义排序默认按主键排序 升序
{
var pkColumn = KeySqlProperties.FirstOrDefault();
if (pkColumn != null)
{
sqlQuery.SqlBuilder.Append($" ORDER BY {pkColumn.ColumnName} ");
}
}
var sql = sqlQuery.SqlBuilder.ToString();
var countSql = $"SELECT COUNT(1) FROM ({sql}) T";
if (searchArgs.Pagination && (Config.SqlProvider == SqlProvider.MySQL || Config.SqlProvider == SqlProvider.PostgreSQL))
{
sql += $" LIMIT {searchArgs.PageSize} OFFSET { (searchArgs.PageIndex - 1) * searchArgs.PageSize}";
}
else if (Config.SqlProvider == SqlProvider.MSSQL)
{
var minRow = (searchArgs.PageIndex - 1) * searchArgs.PageSize;
var maxRow = searchArgs.PageIndex * searchArgs.PageSize;
sql = $@"SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY Id) AS __ROWINDEX,T.* FROM ({sql}) T ) D
WHERE __ROWINDEX>{minRow} AND __ROWINDEX<={maxRow}";
}
var dataQuery = SqlQuery.FromSql(sql, parameters);
var countQuery = SqlQuery.FromSql(countSql, parameters);
return(new Tuple <SqlQuery, SqlQuery>(dataQuery, countQuery));
}
