public static IList <GoodsEntity> GetByParent(string parentId, string hospitalId)
{
var sql = string.Format("select {0} from goods where parent_id = @p_parent_id and hospital_id = @p_hospital_id", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_parent_id", DbType.String, parentId);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId);
var list = new List <GoodsEntity>();
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
// public static IList<ProductInventoryEntity> QueryProductInventory(ProductInventoryCondition condition)
// {
// var list = new List<ProductInventoryEntity>();
// SplittingProductInventory(condition, list);
// UsableProductInventory(condition, list);
// return list;
// }
// private static void SplittingProductInventory(ProductInventoryCondition condition, IList<ProductInventoryEntity> list)
// {
// var conditionSql = "";
// if (!string.IsNullOrEmpty(condition.StoreroomId))
// {
// conditionSql += "and a.storeroom_id=@p_storeroom_id";
// }
// if (!string.IsNullOrEmpty(condition.ProductId))
// {
// conditionSql += " and a.product_id=@p_product_id";
// }
// var sql = string.Format(@"select
// a.storeroom_id,a.product_id,a.expired_date,count(*) number
//from goods a join goods_extra b on a.extra_id=b.id and b.need_split=1
//where hospital_id=@p_hospital_id {0}
//group by a.storeroom_id, a.product_id, a.expired_date", conditionSql);
// var db = DatabaseFactory.CreateDatabase();
// var dc = db.GetSqlStringCommand(sql);
// db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId);
// if (!string.IsNullOrEmpty(condition.StoreroomId))
// {
// db.AddInParameter(dc, "p_storeroom_id", DbType.String, condition.StoreroomId);
// }
// if (!string.IsNullOrEmpty(condition.ProductId))
// {
// db.AddInParameter(dc, "p_product_id", DbType.String, condition.ProductId);
// }
// using (var reader = db.ExecuteReader(dc))
// {
// while (reader.Read())
// {
// var entity = new ProductInventoryEntity();
// entity.Init(reader, true);
// list.Add(entity);
// }
// }
// }
// private static void UsableProductInventory(ProductInventoryCondition condition, IList<ProductInventoryEntity> list)
// {
// var conditionSql = "";
// if (!string.IsNullOrEmpty(condition.StoreroomId))
// {
// conditionSql += "and a.storeroom_id=@p_storeroom_id";
// }
// if (!string.IsNullOrEmpty(condition.ProductId))
// {
// conditionSql += " and a.product_id=@p_product_id";
// }
// var sql = string.Format(@"select
// a.storeroom_id,a.product_id,a.expired_date,count(*) number
//from goods a join goods_extra b on a.extra_id=b.id and b.need_split=0
//where hospital_id=@p_hospital_id {0}
//group by a.storeroom_id, a.product_id, a.expired_date", conditionSql);
// var db = DatabaseFactory.CreateDatabase();
// var dc = db.GetSqlStringCommand(sql);
// db.AddInParameter(dc, "p_hospital_id", DbType.String, condition.HospitalId);
// if (!string.IsNullOrEmpty(condition.StoreroomId))
// {
// db.AddInParameter(dc, "p_storeroom_id", DbType.String, condition.StoreroomId);
// }
// if (!string.IsNullOrEmpty(condition.ProductId))
// {
// db.AddInParameter(dc, "p_product_id", DbType.String, condition.ProductId);
// }
// using (var reader = db.ExecuteReader(dc))
// {
// while (reader.Read())
// {
// var entity = new ProductInventoryEntity();
// entity.Init(reader, false);
// list.Add(entity);
// }
// }
// }
#endregion
public static List <GoodsEntity> GetMovableGoods(string hospitalId, string storeroomId, string productId)
{
var sql = string.Format(@"select {0} from goods g
where hospital_id = @p_hospital_id
and storeroom_id = @p_storeroom_id
and product_id = @p_product_id
and status = 1
and granted_count = 0
and not exists(select 1 from goods_runtime gr where g.serial_id = gr.serial_id and g.barcode = gr.barcode)
and not exists(select 1 from goods_runtime gr, move_form_serial mfs where gr.apply_id = mfs.move_id and gr.serial_id = mfs.to_serial and g.serial_id = mfs.from_serial and g.barcode = gr.barcode)
order by expired_date, created_time ", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId);
db.AddInParameter(dc, "p_storeroom_id", DbType.String, storeroomId);
db.AddInParameter(dc, "p_product_id", DbType.String, productId);
var list = new List <GoodsEntity>();
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsEntity();
entity.Init(reader);
list.Add(entity);
}
}
return(list);
}
public static GoodsEntity GetOneBySerial(string serialId, string hospitalId)
{
var sql = string.Format(@"
select top 1 {0} from goods
where serial_id = @p_serial_id and hospital_id = @p_hospital_id
and package_count > granted_count and status = @p_status", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_serial_id", DbType.String, serialId);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId);
db.AddInParameter(dc, "p_status", DbType.Int32, (int)GoodsStatus.Usable);
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsEntity();
entity.Init(reader);
return(entity);
}
return(null);
}
}
public static GoodsEntity Get(string barcode, string hospitalId)
{
var sql = string.Format("select {0} from goods where barcode = @p_barcode and hospital_id = @p_hospital_id", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_barcode", DbType.String, barcode);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId);
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsEntity();
entity.Init(reader);
return(entity);
}
return(null);
}
}