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 FmsMaterialStockHistoryDailyController : ODataController { private iBemsEntities db = new iBemsEntities(); // TODO : 출고에 따른 StockAmount 계산 수정 필요 // GET: odata/FmsMaterialStockHistoryDaily [Queryable] public List GetFmsMaterialStockHistoryDaily() { List dailiyHistoryList = new List(); var storedList = db.FmsMaterialStored .Where(ms => ms.IsApproval == true) .OrderBy(a => a.StoredDate) .ToList(); var releaseList = db.FmsMaterialRelease .Where(mr => mr.IsConfirmed == true) .OrderBy(a => a.ReleaseDate) .ToList(); //var storedInfoByUnitCost = storedList.GroupBy() // MaterialId / UnitCost / StoredCount // UnitCost 별 카운팅 리스트 //var storedInfoByUnitCost = ( // from c in storedList // group c by new // { // c.MaterialId, // c.UnitCost, // } into gcs // select new // { // MaterialId = gcs.Key.MaterialId, // UnitCost = gcs.Key.UnitCost, // StockCount = gcs.Sum(a=>a.StoredCount), // }).ToList(); // 입고정보로 데이터 생성 foreach (var item in storedList) { var dailyStoredInfo = dailiyHistoryList .Where(d => d.MaterialId == item.MaterialId && d.YearMonthDate.Date == item.StoredDate.Date) .FirstOrDefault(); if (dailyStoredInfo == null) { FmsMaterialStockHistoryDaily d = new FmsMaterialStockHistoryDaily() { 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, 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, }; //d.StockAmount = (item.StoredCount * item.UnitCost) + dailiyHistoryList.Where(b => b.MaterialId == item.MaterialId).Sum(c => c.StockAmount); var tempDailiyInfo = dailiyHistoryList.Where(b => b.MaterialId == item.MaterialId).OrderByDescending(a => a.StoredDate).LastOrDefault(); if (tempDailiyInfo != null) { d.StockAmount = (item.StoredCount * item.UnitCost) + tempDailiyInfo.StockAmount; } else { d.StockAmount = (item.StoredCount * item.UnitCost); } dailiyHistoryList.Add(d); } else { dailyStoredInfo.StoredCount += item.StoredCount; dailyStoredInfo.StockCount = item.StockCount; dailyStoredInfo.StockAmount += (item.StoredCount * item.UnitCost); } } // 출고 정보로 데이터 생성 foreach (var item in releaseList) { var dailyReleasedInfo = dailiyHistoryList .Where(d => d.MaterialId == item.MaterialId && d.YearMonthDate.Date == item.ReleaseDate.Date) .FirstOrDefault(); if (dailyReleasedInfo == null) { FmsMaterialStockHistoryDaily d = new FmsMaterialStockHistoryDaily() { 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, }; // 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; } else if (item.AdjustmentTypeId == 1) { dailyReleasedInfo.ReleaseLossCount += item.MaterialCount.Value; dailyReleasedInfo.StockCount -= item.MaterialCount.Value; dailyReleasedInfo.StockAmount -= item.ReleaseTotalCost.Value; } else if (item.AdjustmentTypeId == 0) { dailyReleasedInfo.ReleaseCount += item.MaterialCount.Value; dailyReleasedInfo.StockCount -= item.MaterialCount.Value; dailyReleasedInfo.StockAmount -= item.ReleaseTotalCost.Value; } } } return dailiyHistoryList; } protected override void Dispose(bool disposing) { if (disposing) { db.Dispose(); } base.Dispose(disposing); } } }