| 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283 | 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<FmsMaterialEx> 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<FmsMaterialStockHistoryAll> GetFmsMaterialStockHistory()        {            List<FmsMaterialStockHistoryAll> dailiyHistoryList = new List<FmsMaterialStockHistoryAll>();            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);        }    }}
 |