public static IList <MoveFormEntity> QueryMoveForms(string hospitalId) { var sql = @"select a.id, b.form_no, b.new_storeroom_id from movein_form a, moveout_form b where a.moveout_id=b.id and a.scan_over=0 and b.hospital_id=@p_hospital_id"; var db = DatabaseFactory.CreateDatabase(); var dc = db.GetSqlStringCommand(sql); db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId); var list = new List <MoveFormEntity>(); using (var reader = db.ExecuteReader(dc)) { while (reader.Read()) { var entity = new MoveFormEntity(); entity.Id = reader["id"].ToString(); entity.FormNo = reader.GetInt32(reader.GetOrdinal("form_no")); entity.ToStoreroomId = reader["new_storeroom_id"].ToString(); list.Add(entity); } } return(list); }
public List <MoveFormEntity> FindAll(DateRangeCondition condition, PagerInfo pager) { pager.ComputePageCount(QueryCount(condition)); var list = new List <MoveFormEntity>(); 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 move_form WHERE status != 'deleted' and {1}", MOVE_COLUMNS, GetConditionSql(condition)); sql = @"SELECT * FROM ( SELECT ROW_NUMBER() OVER(" + orderSql + @") pid," + MOVE_COLUMNS + @" FROM (" + sql + @") t ) t1 WHERE t1.pid BETWEEN @p_pageNo * @p_pageSize + 1 AND (@p_pageNo + 1) * @p_pageSize "; var dc = Db.GetSqlStringCommand(sql); AddParameter(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 MoveFormEntity(); entity.Init(reader); list.Add(entity); } } return(list); }
public MoveFormEntity FindOne(string id) { var sql = string.Format("select {0} from move_form where id = @p_id", MOVE_COLUMNS); var dc = Db.GetSqlStringCommand(sql); Db.AddInParameter(dc, "p_id", DbType.String, id); using (var reader = Db.ExecuteReader(dc)) { if (reader.Read()) { var entity = new MoveFormEntity(); entity.Init(reader); return(entity); } } return(null); }
public MoveFormEntity Save(MoveFormEntity move) { string sql = string.Empty; if (string.IsNullOrEmpty(move.Id)) { sql = string.Format(@"insert into move_form({0}) values( @p_id ,@p_hospital_id ,@p_form_no ,@p_requested_by ,@p_requested_time ,@p_from_storeroom ,@p_to_storeroom ,@p_status ,@p_created_id ,@p_created_time ,@p_updated_id ,@p_updated_time ,@p_moveout_by ,@p_moveout_time ,@p_movein_by ,@p_movein_time)", MOVE_COLUMNS); move.Id = Guid.NewGuid().ToString(); move.Status = MoveFormStatus.Saved; move.UpdatedId = string.Empty; move.UpdatedTime = DateTime.MaxValue; move.MoveoutBy = string.Empty; move.MoveoutTime = null; move.MoveinBy = string.Empty; move.MoveinTime = null; } else { sql = @"update move_form set hospital_id = @p_hospital_id ,form_no = @p_form_no ,requested_by = @p_requested_by ,requested_time = @p_requested_time ,from_storeroom = @p_from_storeroom ,to_storeroom = @p_to_storeroom ,status = @p_status ,created_id = @p_created_id ,created_time = @p_created_time ,updated_id = @p_updated_id ,updated_time = @p_updated_time ,moveout_by = @p_moveout_by ,moveout_time = @p_moveout_time ,movein_by = @p_movein_by ,movein_time = @p_movein_time where id = @p_id "; } DbCommand dc = Db.GetSqlStringCommand(sql); Db.AddInParameter(dc, "p_id", DbType.String, move.Id); Db.AddInParameter(dc, "p_hospital_id", DbType.String, move.HospitalId); Db.AddInParameter(dc, "p_form_no", DbType.Int32, move.FormNo); Db.AddInParameter(dc, "p_requested_by", DbType.String, move.RequestedBy); Db.AddInParameter(dc, "p_requested_time", DbType.DateTime, move.RequestedTime); Db.AddInParameter(dc, "p_from_storeroom", DbType.String, move.FromStoreroomId); Db.AddInParameter(dc, "p_to_storeroom", DbType.String, move.ToStoreroomId); Db.AddInParameter(dc, "p_status", DbType.String, move.Status); Db.AddInParameter(dc, "p_created_id", DbType.String, move.CreatedId); Db.AddInParameter(dc, "p_created_time", DbType.DateTime, move.CreatedTime); Db.AddInParameter(dc, "p_updated_id", DbType.String, move.UpdatedId); Db.AddInParameter(dc, "p_updated_time", DbType.DateTime, move.UpdatedTime); Db.AddInParameter(dc, "p_moveout_by", DbType.String, move.MoveoutBy); Db.AddInParameter(dc, "p_moveout_time", DbType.DateTime, move.MoveoutTime); Db.AddInParameter(dc, "p_movein_by", DbType.String, move.MoveinBy); Db.AddInParameter(dc, "p_movein_time", DbType.DateTime, move.MoveinTime); Db.ExecuteNonQuery(dc, DbTrans); return(move); }