/// <summary>
/// 返回指定时间段的总收入
/// </summary>
/// <param name="dt1"></param>
/// <param name="dt2"></param>
/// <returns></returns>
public MODEL.Cost GetCountForPay(DateTime dt1, DateTime dt2)
{
string sql = "SELECT * from " + strTable + " WHERE cDate BETWEEN '" + dt1.ToString("yyyy-MM-dd") + "' AND '" + dt2.ToString("yyyy-MM-dd") + "'";
System.Data.DataTable dt = SqlHelper.GetTable(sql);
MODEL.Cost model = new MODEL.Cost();
if (dt.Rows.Count > 0)
{
BuildSql.LoadModelData(dt.Rows[0], model, strTable);
}
return(model);
}
/// <summary>
///
/// </summary>
/// <typeparam name="T">返回数据的类型</typeparam>
/// <param name="table">数据表对象</param>
/// <param name="paseSize">页容量</param>
/// <param name="currentPage">当前页数</param>
/// <param name="sqlWhere">分页条件</param>
/// <returns></returns>
public static List <T> SimplePager <T>(string table, string paseSize, string currentPage, string sqlWhere = "") where T : new()
{
string columnParas = string.Empty;
string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true);
string strSql = "select top " + paseSize + " * from ( select ROW_NUMBER() OVER (order by " + columnNames.Split(',')[0] + " desc) as RowNumber,* from " + table + " " + sqlWhere + " " + ") A where RowNumber>" + paseSize + "*(" + currentPage + "-1) ";
DataTable dt = SqlHelper.GetTable(strSql);
List <T> list = new List <T>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
T obs = new T();
BuildSql.LoadModelData(dr, obs, table);
list.Add(obs);
}
}
return(list);
}
/// <summary>
/// 返回泛型集合对象
/// </summary>
/// <typeparam name="T">返回数据的类型</typeparam>
/// <param name="table">数据表</param>
/// <param name="desc">排序方法</param>
/// <param name="sqlWhere">条件，默认为空</param>
/// <returns></returns>
public static List <T> GetListByWhere <T>(string table, string desc = "desc", string sqlWhere = "") where T : new()
{
string columnParas = string.Empty;
string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true);
string strSql = "SELECT " + columnNames + " FROM " + table + " " + sqlWhere + " " + " order by " + columnNames.Split(',')[0] + " " + desc;
DataTable dt = SqlHelper.GetTable(strSql);
List <T> list = new List <T>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
T obs = new T();
BuildSql.LoadModelData(dr, obs, table);
list.Add(obs);
}
}
return(list);
}
/// <summary>
/// 返回泛型集合对象
/// </summary>
/// <param name="table">数据表</param>
/// <param name="obj">实体对象</param>
/// <param name="desc">排序方式</param>
/// <param name="sqlWhere">条件，默认为空 </param>
/// <returns>List<object>集合</object></objct></returns>
// public List<MODEL.User> GetAllList()
//{
// MODEL.User model = new MODEL.User();
// List<object> objs = BuildSql.GetListByWhere("dbo.[User]", model);
// List<MODEL.User> list = new List<MODEL.User>();
// foreach (object item in objs)
// {
// if (item is MODEL.User)
// {
// MODEL.User mol = (MODEL.User)item;
// list.Add(mol);
// }
// }
// return list;
//}
public static List <object> GetListByWhere(string table, object obj, string desc = "desc", string sqlWhere = "")
{
string columnParas = string.Empty;
string columnNames = BuildSql.GetSqlColumnAndSqlParams(table, out columnParas, true);
string strSql = "SELECT " + columnNames + " FROM " + table + " " + sqlWhere + " " + " order by " + columnNames.Split(',')[0] + " " + desc;
DataTable dt = SqlHelper.GetTable(strSql);
Type t = obj.GetType();
List <object> list = new List <object>();
if (dt.Rows.Count > 0)
{
foreach (DataRow dr in dt.Rows)
{
object obs = Activator.CreateInstance(t);
BuildSql.LoadModelData(dr, obs, table);
list.Add(obs);
}
}
return(list);
}