private static void AddParameters(Database db, DbCommand dc, AuditQueryCondition condition) { db.AddInParameter(dc, "p_approver_id", DbType.String, condition.UserId); db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId); db.AddInParameter(dc, "p_status", DbType.String, condition.Status); if (condition.FormNo.HasValue) { db.AddInParameter(dc, "p_form_no", DbType.String, condition.FormNo); } }
private static string GetConditionSql(AuditQueryCondition condition) { var conditionSql = @" and hospital_id = @p_hospital_id and approver_id = @p_approver_id and status = @p_status"; if (condition.FormNo.HasValue) { conditionSql += " and form_no = @p_form_no"; } return(conditionSql); }
public static IList <FormApproverEntity> Query(AuditQueryCondition condition, PagerInfo pager) { pager.ComputePageCount(QueryCount(condition)); var list = new List <FormApproverEntity>(); 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"; } var sql = string.Format(@"SELECT {0} FROM form_approvers WHERE 1=1{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); AddParameters(db, dc, 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 FormApproverEntity(); entity.Init(reader); list.Add(entity); } } return(list); }
private static int QueryCount(AuditQueryCondition condition) { var sql = string.Format(@"SELECT COUNT(id) FROM form_approvers WHERE 1=1{1}", COLUMN_SQL, GetConditionSql(condition)); var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); AddParameters(db, dc, condition); using (IDataReader reader = db.ExecuteReader(dc)) { reader.Read(); return(reader.GetInt32(0)); } }
public IList <FormApproverEntity> Query(AuditQueryCondition condition, PagerInfo pager) { return(FormApproversRepository.Query(condition, pager)); }