/// <summary>
/// 分页-同PagedEntity<T2> GetPagedEntity
/// 多出一个功能：为某一列做统计信息，统计信息需为能够被double覆盖的类型
/// </summary>
/// <param name="statisticalFiled">统计字段</param>
/// <returns></returns>
public virtual PagedEntityWithStatistical <T2> GetPagedEntityWithStatistical <T2>(int pageIndex, int pageSize, string orderBy, ICollection <Tuple <string, string, object> > parameters, string statisticalFiled)
{
var baseSelectSql = GetBaseSelectSqlForNoStatus(typeof(T2));
var tableName = SqlMapperExtensions.GetTableName(typeof(T2));
var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T2));
var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(typeof(T2));
var allPropertiesExceptComputed = allProperties.Except(computedProperties);
bool status = false;
var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status");
if (hasStatus != null)
{
status = !SqlMapperExtensions.ExcludeStatus(hasStatus);
}
var countsql = string.Format("select count(1) from {0} where 1 = 1", tableName);
var statisticalSql = string.Format("select sum({0}) from {1} where 1 = 1", statisticalFiled, tableName);
var sql = new StringBuilder();
var dynamicParameters = new DynamicParameters();
if (parameters != null)
{
for (var i = 0; i < parameters.Count; i++)
{
var parameter = parameters.ElementAt(i);
var parameterName = string.Format("{0}{1}", parameter.Item1, i);
sql.AppendFormat(" and {0} {1} @{2}", parameter.Item1, parameter.Item2, parameterName);
dynamicParameters.Add(parameterName, parameter.Item3);
}
}
if (status)
{
sql.Append(" and Status = 1");
}
var entities = new PagedEntityWithStatistical <T2>();
entities.Items = new List <T2>();
OpenSlaveConnection(conn =>
{
entities.TotalItems = conn.Query <int>(string.Format("{0}{1}", countsql, sql.ToString()), dynamicParameters).FirstOrDefault();
if (entities.TotalItems == 0)
{
return;
}
entities.TotalStatistical = conn.Query <double>(string.Format("{0}{1}", statisticalSql, sql.ToString()), dynamicParameters).FirstOrDefault();
sql.AppendFormat(" order by {0} limit @pageIndex,@pageSize", orderBy);
dynamicParameters.Add("pageIndex", pageIndex * pageSize);
dynamicParameters.Add("pageSize", pageSize);
entities.Items = conn.Query <T2>(string.Format("{0}{1}", baseSelectSql, sql.ToString()), dynamicParameters).ToList();
});
return(entities);
}
public virtual PagedEntity <T2> GetPagedEntityByDefine <T2>(int start, int pageSize, string orderBy, ICollection <Tuple <string, string, object> > parameters)
{
var baseSelectSql = GetBaseSelectSql(typeof(T2));
var tableName = SqlMapperExtensions.GetTableName(typeof(T2));
var allProperties = SqlMapperExtensions.TypePropertiesCache(typeof(T2));
var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(typeof(T2));
var allPropertiesExceptComputed = allProperties.Except(computedProperties);
bool status = false;
var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status");
if (hasStatus != null)
{
status = !SqlMapperExtensions.ExcludeStatus(hasStatus);
}
var countsql = $"select count(1) from {tableName} where {(status ? "Status" : "1")} = 1";
var sql = new StringBuilder();
var dynamicParameters = new DynamicParameters();
if (parameters != null)
{
for (var i = 0; i < parameters.Count; i++)
{
var parameter = parameters.ElementAt(i);
var parameterName = $"{parameter.Item1}{i}";
sql.AppendFormat(" and {0} {1} @{2}", parameter.Item1, parameter.Item2, parameterName);
dynamicParameters.Add(parameterName, parameter.Item3);
}
}
var entities = new PagedEntity <T2>();
entities.Items = new List <T2>();
OpenSlaveConnection(conn =>
{
entities.TotalItems = conn.Query <int>($"{countsql}{sql.ToString()}", dynamicParameters).FirstOrDefault();
if (entities.TotalItems == 0)
{
return;
}
sql.AppendFormat(" order by {0} limit @pageIndex,@pageSize", orderBy);
dynamicParameters.Add("pageIndex", start);
dynamicParameters.Add("pageSize", pageSize);
entities.Items = conn.Query <T2>($"{baseSelectSql}{sql.ToString()}", dynamicParameters).ToList();
});
return(entities);
}
protected static string GetBaseSelectSql(Type type)
{
string baseSelectSql;
if (!SqlMapperExtensions.SelectQueries.TryGetValue(type.TypeHandle, out baseSelectSql))
{
var tableName = SqlMapperExtensions.GetTableName(type);
var allProperties = SqlMapperExtensions.TypePropertiesCache(type);
var computedProperties = SqlMapperExtensions.ComputedPropertiesCache(type);
var allPropertiesExceptComputed = allProperties.Except(computedProperties);
var status = false;
var hasStatus = allPropertiesExceptComputed.FirstOrDefault(o => o.Name == "Status");
if (hasStatus != null)
{
status = !SqlMapperExtensions.ExcludeStatus(hasStatus);
}
baseSelectSql = string.Format("select {0} from {1} where {2} = 1",
string.Join(",", allPropertiesExceptComputed.Select(o => string.Format("`{0}`", o.Name))),
tableName, status ? "Status" : "1");
SqlMapperExtensions.SelectQueries[type.TypeHandle] = baseSelectSql;
}
return(baseSelectSql);
}