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