/// <summary>
/// 分页查询集合
/// </summary>
public List <BS_ORDER> GetListPage(ref PagerModel pager, int?status, string remark, DateTime?startTime, DateTime?endTime)
{
using (var session = DBHelper.GetSession())
{
SqlString sql = new SqlString(@"
select t.*, u.real_name as OrderUserRealName
from bs_order t
left join sys_user u on t.order_userid=u.id
where 1=1");
if (status != null)
{
sql.AppendSql(" and t.status=@status", status);
}
if (!string.IsNullOrWhiteSpace(remark))
{
sql.AppendSql(" and t.remark like concat('%',@roomNo,'%')", remark);
}
if (startTime != null)
{
sql.AppendSql(" and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (endTime != null)
{
sql.AppendSql(" and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
string orderby = " order by t.order_time desc, t.id asc ";
pager = session.FindPageBySql <BS_ORDER>(sql.SQL, orderby, pager.PageSize, pager.CurrentPage, sql.Params);
return(pager.Result as List <BS_ORDER>);
}
}
/// <summary>
/// 获取集合
/// </summary>
public List <TWO_ORDER> GetList(string name, DateTime startTime, DateTime endTime, int?status)
{
SqlString sql = new SqlString(@"
select *
from two_order t
where 1=1
and DEL_FLAG=@delFlag", "0");
sql.AppendSql(@"
and (
ORDER_TIME>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s')
and ORDER_TIME<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s')
)", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"));
if (!string.IsNullOrWhiteSpace(name))
{
sql.AppendSql(" and PRISONER_NAME like @name", "%" + name + "%");
}
if (status != null)
{
sql.AppendSql(" and T_STATUS = @status", status);
}
sql.AppendSql(" and 1=1"); //测试没有参数
sql.AppendSql(" order by ORDER_TIME desc, ID asc"); //测试排序
List <TWO_ORDER> result = DBHelper.FindListBySql <TWO_ORDER>(sql.SQL, sql.Params);
return(result);
}
/// <summary>
/// 获取集合
/// </summary>
public List <utils_test> GetList3(string name, DateTime startTime, DateTime endTime)
{
SqlString sql = new SqlString(@"
select *
from utils_test t
where 1=1 ");
sql.AppendSql(" and name like @name", "%" + name + "%");
sql.AppendSql(" and (add_time between @startTime and @endTime)", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"));
List <utils_test> result = DBHelper.FindListBySql <utils_test>(sql.SQL, sql.Params);
return(result);
}
/// <summary>
/// 查询集合
/// </summary>
public async Task <List <BS_ORDER> > GetListAsync(int?status, string remark, DateTime?startTime, DateTime?endTime)
{
using (var session = await DBHelper.GetSessionAsync())
{
SqlString sql = new SqlString(@"
select t.*, u.real_name as OrderUserRealName
from bs_order t
left join sys_user u on t.order_userid=u.id
where 1=1");
if (status != null)
{
sql.AppendSql(" and t.status=@status", status);
}
if (!string.IsNullOrWhiteSpace(remark))
{
sql.AppendSql(" and t.remark like concat('%',@roomNo,'%')", remark);
}
if (startTime != null)
{
sql.AppendSql(" and t.order_time>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s') ", startTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
if (endTime != null)
{
sql.AppendSql(" and t.order_time<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s') ", endTime.Value.ToString("yyyy-MM-dd HH:mm:ss"));
}
sql.AppendSql(" order by t.order_time desc, t.id asc ");
List <BS_ORDER> list = await session.FindListBySqlAsync <BS_ORDER>(sql.SQL, sql.Params);
return(list);
}
}
/// <summary>
/// 分页获取集合
/// </summary>
public List <TWO_ORDER> GetListPage(ref PagerModel pager, string name, DateTime startTime, DateTime endTime, int?status)
{
SqlString sql = new SqlString(string.Format(@"
select *
from two_order t
where 1=1
and DEL_FLAG='{0}'", "0"));
sql.AppendSql(@"
and (
ORDER_TIME>=STR_TO_DATE(@startTime, '%Y-%m-%d %H:%i:%s')
and ORDER_TIME<=STR_TO_DATE(@endTime, '%Y-%m-%d %H:%i:%s')
)", startTime.ToString("yyyy-MM-dd HH:mm:ss"), endTime.ToString("yyyy-MM-dd HH:mm:ss"));
if (!string.IsNullOrWhiteSpace(name))
{
sql.AppendSql(" and PRISONER_NAME like @name", "%" + name + "%");
}
if (status != null)
{
sql.AppendSql(" and T_STATUS = @status", status);
}
if (status != null)
{
sql.AppendFormat(" and T_STATUS = '{0}'", status); //测试追加非参数化SQL
}
sql.AppendSql(" and 1=1"); //测试没有参数
string orderby = "order by ORDER_TIME desc, ID asc"; //测试排序
pager = DBHelper.FindPageBySql <TWO_ORDER>(sql.SQL, orderby, pager.rows, pager.page, sql.Params);
return(pager.result as List <TWO_ORDER>);
}