using System; using System.Collections.Generic; using System.ComponentModel.DataAnnotations; using System.Data; using System.Data.Entity; using System.Data.Entity.Infrastructure; using System.Linq; using System.Net; using System.Net.Http; using System.Web.Http; using System.Web.Http.Description; using System.Web.Http.OData; using iBemsDataService; using iBemsDataService.Model; namespace iBemsDataService.Controllers { public class FmsEquipmentExController : ODataController { private iBemsEntities db = new iBemsEntities(); // GET: odata/FmsEquipmentEx [Queryable] public List GetFmsEquipmentEx() { var query = ( from fe in db.FmsEquipment from cu in db.CmUser from fec in db.FmsEquipmentCodeType join fw in db.FmsMaterialWarehouse on fe.WarehouseId equals fw.WarehouseId into gw from subWarehouse in gw.DefaultIfEmpty() join eh in db.FmsEquipmentHistory on fe.EquipmentId equals eh.EquipmentId into gh from subHistory in gh.DefaultIfEmpty().OrderByDescending(a => a.EquipmentHistoryId).Take(1) where fe.RegisterUserId == cu.UserId && fe.EquipmentTypeId == fec.EquipmentTypeId && fe.SiteId == cu.SiteId select new FmsEquipmentEx { AddDate = fe.AddDate, EquipmentId = fe.EquipmentId, EquipmentTypeId = fe.EquipmentTypeId, Name = fe.Name, RegisterUserId = fe.RegisterUserId, RegisterUserName = cu.Name, SiteId = fe.SiteId, Standard = fe.Standard, SupplierName = fe.SupplierName, SupplierPhoneNo = fe.SupplierPhoneNo, Unit = fe.Unit, UpdateDate = fe.UpdateDate, WarehouseId = fe.WarehouseId, WarehouseName = subWarehouse.Name, ImageFileId = fe.ImageFileId, EquipmentCodeTypeName = fec.Name, TotalStockCount = subHistory.TotalStockCount == null ? 0 : subHistory.TotalStockCount, CurrentStockCount = subHistory.CurrentStockCount == null ? 0 : subHistory.CurrentStockCount, //CurrentStoredCount = subHistory.StoredCount == null ? 0 : subHistory.StoredCount, //CurrentReturnCount = subHistory.ReturnCount == null ? 0 : subHistory.ReturnCount, //CurrentLossCount = subHistory.LossCount == null ? 0 : subHistory.LossCount, CurrentStoredCount = db.FmsEquipmentHistory.Where(a => a.EquipmentId == fe.EquipmentId).Sum(a => a.StoredCount), CurrentReturnCount = db.FmsEquipmentHistory.Where(a => a.EquipmentId == fe.EquipmentId).Sum(a => a.ReturnCount), CurrentLossCount = db.FmsEquipmentHistory.Where(a => a.EquipmentId == fe.EquipmentId).Sum(a => a.LossCount) }); var list = query.ToList(); foreach (var item in list) { item.CurrentRentCount = item.TotalStockCount.Value - item.CurrentStockCount.Value; } return list; } protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); } } }