private static void CreateInventory(IncomingFormEntity form, Database db, DbTransaction trans)
{
var goodsSerial = GoodsSerialRepository.Get(form.SerialId, db, trans);
var barcodes = GoodsSerialRepository.GetBarcodes(form.SerialId, db, trans);
var product = ProductRepository.Get(goodsSerial.ProductId);
var inventory = new GoodsInventoryEntity
{
SerialId = goodsSerial.Id,
BatchNo = goodsSerial.BatchNo,
ProductId = goodsSerial.ProductId,
StoreroomId = form.StoreroomId,
ExpiredDate = goodsSerial.ExpiredDate.Value,
HospitalId = goodsSerial.HospitalId,
VendorId = goodsSerial.VendorId,
OriginalCount = form.IncomingCount,
SplitCount = 0,
UsableCount = form.IncomingCount, //goodsSerial.NeedSplit ? 0 : form.IncomingCount,
GrantedCount = 0,
CreatedId = form.ConfirmedId,
CreatedTime = form.ConfirmedTime
};
inventory.ApplyCount = inventory.UsableCount * product.MiniPackageCount;
GoodsInventoryRepository.Create(inventory, db, trans);
AllocateBarcodes(barcodes, inventory, product, db, trans);
}
public static IList <GoodsInventoryEntity> Query(DateRangeCondition condition)
{
var sql = string.Format(GetBaseQuerySql(condition), "a.id,a.product_id,a.hospital_id,a.original_count,a.usable_count,a.granted_count,a.split_count,b.name,b.full_name");
sql += " order by name";
var list = new List <GoodsInventoryEntity>();
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
AddParameter(dc, db, condition);
using (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsInventoryEntity();
entity.Init(reader);
//entity.ProductName = reader["name"].ToString();
//entity.ProductFullName = reader["full_name"].ToString();
list.Add(entity);
}
}
return(list);
}
private static void AdjustInventory(GoodsEntity goods, string serialId, int count, int packageCount, string userId,
Database db, DbTransaction trans)
{
var inventroy = new GoodsInventoryEntity
{
SerialId = serialId,
BatchNo = goods.BatchNo,
ProductId = goods.ProductId,
StoreroomId = goods.StoreroomId,
ExpiredDate = goods.ExpiredDate,
HospitalId = goods.HospitalId,
VendorId = goods.VendorId,
OriginalCount = count,
SplitCount = 0,
UsableCount = count,
ApplyCount = packageCount * count,
GrantedCount = 0,
CreatedId = userId,
CreatedTime = DateTime.Now
};
GoodsInventoryRepository.Create(inventroy, db, trans);
Reduce(goods.Barcode, 1, goods.HospitalId, db, trans);
GoodsInventoryRepository.Reduce(goods.SerialId, goods.HospitalId, 1, db, trans);
if (goods.PackageCount == goods.GrantedCount + 1)
{
Complete(goods.Id, db, trans);
}
}
public static IList <GoodsInventoryEntity> Get(IList <string> productIds, string hospitalId, Database db, DbTransaction trans)
{
var sql = string.Format("select {0} from goods_inventory {2} where product_id in ('{1}') and hospital_id=@p_hospital_id", COLUMN_SQL, string.Join("','", productIds), TransHelper.UpdateLock(trans));
if (db == null)
{
db = DatabaseFactory.CreateDatabase();
}
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId);
var list = new List <GoodsInventoryEntity>();
var reader = trans == null?db.ExecuteReader(dc) : db.ExecuteReader(dc, trans);
try
{
while (reader.Read())
{
var entity = new GoodsInventoryEntity();
entity.Init(reader);
list.Add(entity);
}
}
finally
{
reader.Close();
}
return(list);
}
internal static void Create(GoodsInventoryEntity entity, Database db, DbTransaction trans)
{
var sql = string.Format(@"insert into goods_inventory({0})
values(
@p_id,@p_serial_id,@p_batch_no,@p_product_id,@p_storeroom_id,@p_expired_date,@p_hospital_id,@p_vendor_id,
@p_original_count,@p_split_count,@p_usable_count,@p_apply_count,@p_granted_count,@p_created_id,@p_created_time
)", COLUMN_SQL);
var cmd = db.GetSqlStringCommand(sql);
entity.Id = Guid.NewGuid().ToString();
db.AddInParameter(cmd, "p_id", DbType.String, entity.Id);
db.AddInParameter(cmd, "p_serial_id", DbType.String, entity.SerialId);
db.AddInParameter(cmd, "p_batch_no", DbType.String, entity.BatchNo);
db.AddInParameter(cmd, "p_product_id", DbType.String, entity.ProductId);
db.AddInParameter(cmd, "p_storeroom_id", DbType.String, entity.StoreroomId);
db.AddInParameter(cmd, "p_expired_date", DbType.DateTime, entity.ExpiredDate);
db.AddInParameter(cmd, "p_hospital_id", DbType.String, entity.HospitalId);
db.AddInParameter(cmd, "p_vendor_id", DbType.String, entity.VendorId);
db.AddInParameter(cmd, "p_original_count", DbType.Int32, entity.OriginalCount);
db.AddInParameter(cmd, "p_split_count", DbType.Int32, entity.SplitCount);
db.AddInParameter(cmd, "p_usable_count", DbType.Int32, entity.UsableCount);
db.AddInParameter(cmd, "p_apply_count", DbType.Int32, entity.ApplyCount);
db.AddInParameter(cmd, "p_granted_count", DbType.Int32, entity.GrantedCount);
db.AddInParameter(cmd, "p_created_id", DbType.String, entity.CreatedId);
db.AddInParameter(cmd, "p_created_time", DbType.DateTime, entity.CreatedTime);
db.ExecuteNonQuery(cmd, trans);
}
public static GoodsInventoryEntity QueryEarlyExpiredDateInventory(string productId, string storeroomId, DateTime expiredDate, string hospitalId)
{
var sql = string.Format(@"select top 1 {0} from goods_inventory
where hospital_id=@p_hospital_id and product_id=@p_product_id and storeroom_id=@p_storeroom_id
and expired_date<@p_expired_date and expired_date>@p_today
order by expired_date
", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "p_hospital_id", DbType.String, hospitalId);
db.AddInParameter(cmd, "p_product_id", DbType.String, productId);
db.AddInParameter(cmd, "p_storeroom_id", DbType.String, storeroomId);
db.AddInParameter(cmd, "p_expired_date", DbType.DateTime, expiredDate);
db.AddInParameter(cmd, "p_today", DbType.DateTime, DateTime.Today);
using (var reader = db.ExecuteReader(cmd))
{
if (reader.Read())
{
var entity = new GoodsInventoryEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
public static IList <GoodsInventoryEntity> Query(DateRangeCondition condition, PagerInfo pager)
{
pager.ComputePageCount(QueryCount(condition));
var list = new List <GoodsInventoryEntity>();
var orderSql = " ORDER BY ";
if (pager.OrderFields.Count > 0)
{
foreach (var field in pager.OrderFields)
{
orderSql += field.Field + (field.Desc ? " DESC" : "") + ",";
}
}
else
{
orderSql += "name";
}
var sql = string.Format(GetBaseQuerySql(condition), "a.id,a.product_id,a.hospital_id,a.original_count,a.usable_count,a.granted_count,a.split_count,b.name,b.full_name");
sql = @"SELECT * FROM
(
SELECT ROW_NUMBER() OVER(" + orderSql + @") pid,*
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 (var reader = db.ExecuteReader(dc))
{
while (reader.Read())
{
var entity = new GoodsInventoryEntity();
entity.Init(reader);
//entity.ProductName = reader["name"].ToString();
//entity.ProductFullName = reader["full_name"].ToString();
list.Add(entity);
}
}
return(list);
}
public static GoodsInventoryEntity Get(string productId, string hospitalId)
{
var sql = string.Format("select {0} from goods_inventory where product_id=@p_product_id and hospital_id=@p_hospital_id", COLUMN_SQL);
var db = DatabaseFactory.CreateDatabase();
var dc = db.GetSqlStringCommand(sql);
db.AddInParameter(dc, "p_product_id", DbType.String, productId);
db.AddInParameter(dc, "p_hospital_id", DbType.String, hospitalId);
using (var reader = db.ExecuteReader(dc))
{
if (reader.Read())
{
var entity = new GoodsInventoryEntity();
entity.Init(reader);
return(entity);
}
}
return(null);
}
private static void AllocateBarcodes(IList <GoodsSerialBarcodeEntity> barcodes, GoodsInventoryEntity inventory, ProductEntity product, Database db, DbTransaction trans)
{
foreach (var item in barcodes)
{
var goods = new GoodsEntity();
goods.Name = product.Name;
goods.Barcode = item.Barcode;
goods.SerialId = item.SerialId;
goods.HospitalId = inventory.HospitalId;
goods.VendorId = inventory.VendorId;
goods.ProductId = inventory.ProductId;
goods.BatchNo = inventory.BatchNo;
goods.ExpiredDate = inventory.ExpiredDate;
goods.StoreroomId = inventory.StoreroomId;
goods.PackageCapacity = product.MiniPackageSpec;
goods.PackageCount = product.MiniPackageCount;
goods.MeasuringUnit = product.PackageUnit;
goods.Status = GoodsStatus.Usable;
goods.CreatedId = inventory.CreatedId;
goods.CreatedTime = inventory.CreatedTime;
GoodsRepsitory.Create(goods, db, trans);
}
}
