public IList <ProductInventoryEntity> QueryProductInventory(ProductInventoryCondition condition)
{
return(GoodsInventoryRepository.QueryProductInventory(condition));
}
public static IList <ProductInventoryEntity> QueryProductInventory(ProductInventoryCondition condition)
{
var sql = string.Format(@"select *
from (
select
sum(original_count) original_count, sum(split_count) split_count,
sum(apply_count) apply_count, sum(granted_count) granted_count,
a.storeroom_id,a.vendor_id,a.product_id,b.need_split,a.expired_date,ri.received_date
from goods_inventory a
join goods_serial b
on a.serial_id = b.id and a.hospital_id = b.hospital_id and a.hospital_id = @p_hospital_id
join(select rf.hospital_id
, case when rfi.receive_id is null then rf.serial_id else rfi.serial_id end serial_id
, case when rfi.receive_id is null then rf.confirmed_time else rfi.confirmed_time end received_date
from receive_form rf left join receive_form_items rfi on rf.id = rfi.receive_id
where rf.is_confirmed = 1 and rf.hospital_id = @p_hospital_id) ri
on a.serial_id = ri.serial_id and a.hospital_id = b.hospital_id
where 1=1 {0} {1}
group by b.need_split,a.storeroom_id,a.product_id,a.vendor_id,a.expired_date,ri.received_date) ap",
string.IsNullOrEmpty(condition.StoreroomId) ? string.Empty : "and a.storeroom_id=@p_storeroom_id",
string.IsNullOrEmpty(condition.ProductId) ? string.Empty : "and a.product_id=@p_product_id");
sql += (string.IsNullOrEmpty(condition.NotIncludeZero) ? string.Empty : " where (need_split>0 and original_count-split_count>0) or apply_count-granted_count>0");
sql += " order by storeroom_id,expired_date,product_id,need_split";
var db = DatabaseFactory.CreateDatabase();
var cmd = db.GetSqlStringCommand(sql);
db.AddInParameter(cmd, "p_hospital_id", DbType.String, condition.HospitalId);
if (!string.IsNullOrEmpty(condition.StoreroomId))
{
db.AddInParameter(cmd, "p_storeroom_id", DbType.String, condition.StoreroomId);
}
if (!string.IsNullOrEmpty(condition.ProductId))
{
db.AddInParameter(cmd, "p_product_id", DbType.String, condition.ProductId);
}
var list = new List <ProductInventoryEntity>();
using (var reader = db.ExecuteReader(cmd))
{
while (reader.Read())
{
var originalCount = ReadItem(reader, "original_count");
var splitCount = ReadItem(reader, "split_count");
var applyCount = ReadItem(reader, "apply_count");
var grantedCount = ReadItem(reader, "granted_count");
var entity = new ProductInventoryEntity();
entity.NeedSplit = reader.GetBoolean(reader.GetOrdinal("need_split"));
entity.ProductId = reader["product_id"].ToString();
entity.StoreroomId = reader["storeroom_id"].ToString();
entity.VendorID = reader["vendor_id"].ToString();
entity.ExpiredDate = reader.GetDateTime(reader.GetOrdinal("expired_date"));
entity.ReceivedDate = reader.GetDateTime(reader.GetOrdinal("received_date"));
entity.SplitableCount = entity.NeedSplit ? originalCount - splitCount : 0;
entity.ApplyCount = applyCount - grantedCount;
list.Add(entity);
}
}
return(list);
}
