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);
}
