public static IList <UserEntity> Query(string condition, string rootId, string unitId, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition, rootId, unitId));
var list = new List <UserEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "name";
}
var sql = string.Format(@"SELECT {0} FROM users WHERE 1=1{1}", COLUMN_SQL, GetConditionSql(condition, rootId, unitId));
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + COLUMN_SQL + @"
FROM (" + sql + @") t
) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize ";
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_condition", DbType.String, "%" + condition + "%");
if (string.IsNullOrEmpty(unitId))
{
db.AddInParameter(dc, "p_root_id", DbType.String, rootId);
}
else
{
db.AddInParameter(dc, "p_unit_id", DbType.String, unitId);
}
db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex);
db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize);
using (IDataReader reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new UserEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public static IList <DispatchFormEntity> Query(DispatchQueryCondition condition, PagerInfo pager)
{
var baseSql = @"select {0} from dispatch_form where 1=1 {1}";
var conditionSql = GetConditionSql(condition);
var db = DatabaseFactory.CreateDatabase();
pager.ComputePageCount(QueryCount(baseSql, conditionSql, condition, db));
var orderSql = " order by ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += " order_form_no desc,product_id,created_time desc,status ";
}
var sql = string.Format(baseSql, COLUMN_SQL, conditionSql);
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + COLUMN_SQL + @"
FROM (" + sql + @") t
) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize ";
var cmd = db.GetSqlStringCommand(sql);
AddParameters(condition, db, cmd);
db.AddInParameter(cmd, "p_pageNo", DbType.Int32, pager.PageIndex);
db.AddInParameter(cmd, "p_pageSize", DbType.Int32, pager.PageSize);
var list = new List <DispatchFormEntity>();
using (var reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
var entity = new DispatchFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public static IList <MoveoutFormEntity> Query(DateRangeCondition condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <MoveoutFormEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "form_no DESC";
}
var sql = string.Format(@"SELECT {0} FROM moveout_form WHERE is_active = 1 and {1}", COLUMN_SQL, GetConditionSql(condition));
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + COLUMN_SQL + @"
FROM (" + sql + @") t
) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize ";
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
AddParameter(dc, db, condition);
db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex);
db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize);
using (IDataReader reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new MoveoutFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public static IList <GoodsInventoryEntity> Query(DateRangeCondition condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <GoodsInventoryEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "name";
}
var sql = string.Format(GetBaseQuerySql(condition), "a.id,a.product_id,a.hospital_id,a.original_count,a.usable_count,a.granted_count,a.split_count,b.name,b.full_name");
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid,*
FROM (" + sql + @") t
) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize";
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
AddParameter(dc, db, condition);
db.AddInParameter(dc, "p_pageNo", DbType.Int32, pager.PageIndex);
db.AddInParameter(dc, "p_pageSize", DbType.Int32, pager.PageSize);
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsInventoryEntity();
entity.Init(reader);
//entity.ProductName = reader["name"].ToString();
//entity.ProductFullName = reader["full_name"].ToString();
list.Add(entity);
}
}
return(list);
}