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 FmsMaterialExController : ODataController { private iBemsEntities db = new iBemsEntities(); // GET: odata/FmsMaterialEx [Queryable] public List GetFmsMaterialEx() { var mterials = ( from m in db.FmsMaterial where m.IsUse == true select new FmsMaterialEx { BusinessFieldId = m.BusinessFieldId, CmBusinessField = m.CmBusinessField, CmFile = m.CmFile, CmSite = m.CmSite, DurableYears = m.DurableYears, FinalPrice = m.FinalPrice, ThirdClassId = m.ThirdClassId, FirstClassId = m.FirstClassId, FmsMaterialCodeClass = m.FmsMaterialCodeClass, FmsMaterialCodeClass1 = m.FmsMaterialCodeClass1, FmsMaterialCodeClass2 = m.FmsMaterialCodeClass2, FmsMaterialCodeLocation = m.FmsMaterialCodeLocation, FmsMaterialCodeType = m.FmsMaterialCodeType, ImageFileId = m.ImageFileId, IsDiscontinued = m.IsDiscontinued, MaterialCode = m.MaterialCode, MaterialId = m.MaterialId, MaterialTypeId = m.MaterialTypeId, Name = m.Name, Note = m.Note, ReasonableStockCount = m.ReasonableStockCount, Unit = m.Unit, Rfid = m.Rfid, SecondClassId = m.SecondClassId, SiteId = m.SiteId, Standard = m.Standard, FmsMaterialStored = m.FmsMaterialStored, FmsMaterialRelease = m.FmsMaterialRelease, //hcLee 2015 11 16 WarehouseId = m.WarehouseId, Manufacturer = m.Manufacturer, } ).ToList(); var historyAllList = GetFmsMaterialStockHistory(); foreach (var material in mterials) { var temp = historyAllList.Where(m => m.MaterialId == material.MaterialId).FirstOrDefault(); if (temp != null) { material.StockCount = temp.StockCount; material.StockAmount = temp.StockAmount; //hcLee 2015 11 11 material.WarehouseId = temp.WarehouseId; material.WHouseName = GetWHouseName(temp.WarehouseId); if (material.ReasonableStockCount == null) material.NeedCount = 0; else material.NeedCount = (int)material.ReasonableStockCount - material.StockCount; } } #region === Obslote === //foreach (var item in mterials) //{ // var lastestStockInfo = item.FmsMaterialStored // .Where(ms=>ms.IsApproval == true) // .OrderByDescending(ms => ms.MaterialStoredId) // .Take(1).FirstOrDefault(); // if (lastestStockInfo == null) // Sotred 정보가 없는 경우 // { // item.StockCount = 0; // item.StockAmount = 0; // continue; // } // var releasedMaterials = item.FmsMaterialRelease // .Where(mr=>mr.IsConfirmed == true ) // .ToList(); // if (releasedMaterials.Count == 0) // Release 정보가 없는 경우 // { // item.StockCount = lastestStockInfo.StockCount; // item.StockAmount = lastestStockInfo.StockCount * lastestStockInfo.UnitCost; // continue; // } // var releaseCount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId != 2).Sum(r2 => r2.MaterialCount).Value; // var releaseTotalAmount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId != 2).Sum(r2 => r2.ReleaseTotalCost).Value; // var releaseReturnCount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId == 2).Sum(r2 => r2.MaterialCount).Value; // var releaseReturnTotalAmount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId == 2).Sum(r2 => r2.ReleaseTotalCost).Value; // item.StockCount = lastestStockInfo.StockCount - releaseCount + releaseReturnCount; // item.StockAmount = (lastestStockInfo.StockCount * lastestStockInfo.UnitCost) - releaseTotalAmount + releaseReturnTotalAmount; //} #endregion return mterials.ToList(); } private string GetWHouseName(int id) { var W = db.FmsMaterialWarehouse .Where(mr => mr.WarehouseId == id) .FirstOrDefault(); if (W == null) return ""; else return W.Name; } private List GetFmsMaterialStockHistory() { List dailiyHistoryList = new List(); var storedList = db.FmsMaterialStored .Where(ms => ms.IsApproval == true) .ToList(); var releaseList = db.FmsMaterialRelease .Where(mr => mr.IsConfirmed == true) .ToList(); // 입고정보로 데이터 생성 foreach (var item in storedList) { var dailyStoredInfo = dailiyHistoryList .Where(d => d.MaterialId == item.MaterialId) .FirstOrDefault(); if (dailyStoredInfo == null) { FmsMaterialStockHistoryAll d = new FmsMaterialStockHistoryAll() { SiteId = item.SiteId, BusinessFieldId = item.BusinessFieldId, BusinessFieldName = item.CmBusinessField.Name, Unit = item.FmsMaterial.Unit, Standard = item.FmsMaterial.Standard, YearMonthDate = new DateTime(item.StoredDate.Year, item.StoredDate.Month, item.StoredDate.Day), YearMonth = new DateTime(item.StoredDate.Year, item.StoredDate.Month, 1), MaterialId = item.MaterialId, MaterialName = item.FmsMaterial.Name, MaterialCode = item.FmsMaterial.MaterialCode, StoredCount = item.StoredCount, StockCount = item.StockCount, StockAmount = (item.StoredCount * item.UnitCost),//+ dailiyHistoryList.Where(b => b.MaterialId == item.MaterialId).Sum(c => c.StockAmount), FirstClassId = item.FmsMaterial.FirstClassId != null ? item.FmsMaterial.FirstClassId.Value : 0, SecondClassId = item.FmsMaterial.SecondClassId != null ? item.FmsMaterial.SecondClassId.Value : 0, ThirdClassId = item.FmsMaterial.ThirdClassId != null ? item.FmsMaterial.ThirdClassId.Value : 0, //hcLee 2015 11 16 WarehouseId = item.WarehouseId, //WHouseName = GetWHouseName(item.WarehouseId), }; dailiyHistoryList.Add(d); } else { dailyStoredInfo.StoredCount += item.StoredCount; dailyStoredInfo.StockCount = item.StockCount; dailyStoredInfo.StockAmount += (item.StoredCount * item.UnitCost); // + dailiyHistoryList.Where(b => b.MaterialId == item.MaterialId).Sum(c => c.StockAmount); //hcLee 2015 11 16 dailyStoredInfo.WarehouseId = item.WarehouseId; //WHouseName = GetWHouseName(item.WarehouseId), } } // 출고 정보로 데이터 생성 foreach (var item in releaseList) { var dailyReleasedInfo = dailiyHistoryList .Where(d => d.MaterialId == item.MaterialId) .FirstOrDefault(); if (dailyReleasedInfo == null) { FmsMaterialStockHistoryAll d = new FmsMaterialStockHistoryAll() { SiteId = item.SiteId, BusinessFieldId = item.BusinessFieldId, BusinessFieldName = item.CmBusinessField.Name, Unit = item.FmsMaterial.Unit, Standard = item.FmsMaterial.Standard, YearMonthDate = new DateTime(item.ReleaseDate.Year, item.ReleaseDate.Month, item.ReleaseDate.Day), YearMonth = new DateTime(item.ReleaseDate.Year, item.ReleaseDate.Month, 1), MaterialId = item.MaterialId.Value, MaterialName = item.FmsMaterial.Name, MaterialCode = item.FmsMaterial.MaterialCode, StoredCount = 0, ReleaseCount = item.AdjustmentTypeId == 0 ? item.MaterialCount.Value : 0, ReleaseLossCount = item.AdjustmentTypeId == 1 ? item.MaterialCount.Value : 0, ReleaseReturnCount = item.AdjustmentTypeId == 2 ? item.MaterialCount.Value : 0, FirstClassId = item.FmsMaterial.FirstClassId != null ? item.FmsMaterial.FirstClassId.Value : 0, SecondClassId = item.FmsMaterial.SecondClassId != null ? item.FmsMaterial.SecondClassId.Value : 0, ThirdClassId = item.FmsMaterial.ThirdClassId != null ? item.FmsMaterial.ThirdClassId.Value : 0, //hcLee 2015 11 16 //WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0, }; // StockCount 를 체크하기 위한 코드 var tempStoredInfo = dailiyHistoryList .Where(h => h.MaterialId == item.MaterialId) .OrderByDescending(m => m.YearMonthDate) .Take(1).FirstOrDefault(); if (tempStoredInfo == null) { continue; } d.StockCount = tempStoredInfo.StockCount - d.ReleaseCount - d.ReleaseLossCount + d.ReleaseReturnCount; if (item.AdjustmentTypeId == 2) { d.StockAmount = tempStoredInfo.StockAmount + item.ReleaseTotalCost.Value; } else { d.StockAmount = tempStoredInfo.StockAmount - item.ReleaseTotalCost.Value; } dailiyHistoryList.Add(d); } else { if (item.AdjustmentTypeId == 2) { dailyReleasedInfo.ReleaseReturnCount += item.MaterialCount.Value; dailyReleasedInfo.StockCount += item.MaterialCount.Value; dailyReleasedInfo.StockAmount += item.ReleaseTotalCost.Value; //hcLee 2015 11 16 //dailyReleasedInfo.WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0; } else if (item.AdjustmentTypeId == 1) { dailyReleasedInfo.ReleaseLossCount += item.MaterialCount.Value; dailyReleasedInfo.StockCount -= item.MaterialCount.Value; dailyReleasedInfo.StockAmount -= item.ReleaseTotalCost.Value; //hcLee 2015 11 16 //dailyReleasedInfo.WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0; } else if (item.AdjustmentTypeId == 0) { dailyReleasedInfo.ReleaseCount += item.MaterialCount.Value; dailyReleasedInfo.StockCount -= item.MaterialCount.Value; dailyReleasedInfo.StockAmount -= item.ReleaseTotalCost.Value; //hcLee 2015 11 16 //dailyReleasedInfo.WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0; } } } return dailiyHistoryList; } protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); } } }