public static IList <ReturnFormEntity> Query(ReturnQueryCondition condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <ReturnFormEntity>();
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 return_form WHERE {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 ReturnFormEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public JsonNetResult QueryReturn(ReturnQueryCondition condition, PagerInfo pager)
{
try
{
condition.HospitalId = this.UserContext.CurrentHospital;
var list = new ReturnFormService().Query(condition, pager);
return(JsonNet(new ResponseResult(true, list, pager)));
}
catch (Exception e)
{
return(JsonNet(new ResponseResult(false, e)));
}
}
private static void AddParameter(DbCommand dc, Database db, ReturnQueryCondition condition)
{
db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId);
int formNo;
if (!string.IsNullOrEmpty(condition.Content) && int.TryParse(condition.Content, out formNo))
{
db.AddInParameter(dc, "p_form_no", DbType.Int32, formNo);
}
if (condition.Status != ReturnFormStatus.None)
{
db.AddInParameter(dc, "p_status", DbType.Int32, condition.Status);
}
}
private static string GetConditionSql(ReturnQueryCondition condition)
{
var conditionSql = @" 1=1 and hospital_id = @p_hospital_id";
int formNo;
if (!string.IsNullOrEmpty(condition.Content) && int.TryParse(condition.Content, out formNo))
{
conditionSql += " and form_no = @p_form_no";
}
if (condition.Status != ReturnFormStatus.None)
{
conditionSql += " and status = @p_status";
}
return(conditionSql);
}
private static int QueryCount(ReturnQueryCondition condition)
{
var sql = "SELECT COUNT(id) FROM return_form WHERE ";
sql += GetConditionSql(condition);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
AddParameter(dc, db, condition);
using (IDataReader reader = db.ExecuteReader(dc))
{
reader.Read();
return(reader.GetInt32(0));
}
}
public IList <ReturnFormEntity> Query(ReturnQueryCondition condition, PagerInfo pager)
{
return(ReturnFormRepository.Query(condition, pager));
}
