d9fc282985c4a894b35606d3abbe1c180db2c8f9.svn-base 13 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283
  1. using System;
  2. using System.Collections.Generic;
  3. using System.ComponentModel.DataAnnotations;
  4. using System.Data;
  5. using System.Data.Entity;
  6. using System.Data.Entity.Infrastructure;
  7. using System.Linq;
  8. using System.Net;
  9. using System.Net.Http;
  10. using System.Web.Http;
  11. using System.Web.Http.Description;
  12. using System.Web.Http.OData;
  13. using iBemsDataService;
  14. using iBemsDataService.Model;
  15. namespace iBemsDataService.Controllers
  16. {
  17. public class FmsMaterialExController : ODataController
  18. {
  19. private iBemsEntities db = new iBemsEntities();
  20. // GET: odata/FmsMaterialEx
  21. [Queryable]
  22. public List<FmsMaterialEx> GetFmsMaterialEx()
  23. {
  24. var mterials = (
  25. from m in db.FmsMaterial
  26. where m.IsUse == true
  27. select new FmsMaterialEx
  28. {
  29. BusinessFieldId = m.BusinessFieldId,
  30. CmBusinessField = m.CmBusinessField,
  31. CmFile = m.CmFile,
  32. CmSite = m.CmSite,
  33. DurableYears = m.DurableYears,
  34. FinalPrice = m.FinalPrice,
  35. ThirdClassId = m.ThirdClassId,
  36. FirstClassId = m.FirstClassId,
  37. FmsMaterialCodeClass = m.FmsMaterialCodeClass,
  38. FmsMaterialCodeClass1 = m.FmsMaterialCodeClass1,
  39. FmsMaterialCodeClass2 = m.FmsMaterialCodeClass2,
  40. FmsMaterialCodeLocation = m.FmsMaterialCodeLocation,
  41. FmsMaterialCodeType = m.FmsMaterialCodeType,
  42. ImageFileId = m.ImageFileId,
  43. IsDiscontinued = m.IsDiscontinued,
  44. MaterialCode = m.MaterialCode,
  45. MaterialId = m.MaterialId,
  46. MaterialTypeId = m.MaterialTypeId,
  47. Name = m.Name,
  48. Note = m.Note,
  49. ReasonableStockCount = m.ReasonableStockCount,
  50. Unit = m.Unit,
  51. Rfid = m.Rfid,
  52. SecondClassId = m.SecondClassId,
  53. SiteId = m.SiteId,
  54. Standard = m.Standard,
  55. FmsMaterialStored = m.FmsMaterialStored,
  56. FmsMaterialRelease = m.FmsMaterialRelease,
  57. //hcLee 2015 11 16
  58. WarehouseId = m.WarehouseId,
  59. Manufacturer = m.Manufacturer,
  60. }
  61. ).ToList();
  62. var historyAllList = GetFmsMaterialStockHistory();
  63. foreach (var material in mterials)
  64. {
  65. var temp = historyAllList.Where(m => m.MaterialId == material.MaterialId).FirstOrDefault();
  66. if (temp != null)
  67. {
  68. material.StockCount = temp.StockCount;
  69. material.StockAmount = temp.StockAmount;
  70. //hcLee 2015 11 11
  71. material.WarehouseId = temp.WarehouseId;
  72. material.WHouseName = GetWHouseName(temp.WarehouseId);
  73. if (material.ReasonableStockCount == null) material.NeedCount = 0;
  74. else material.NeedCount = (int)material.ReasonableStockCount - material.StockCount;
  75. }
  76. }
  77. #region === Obslote ===
  78. //foreach (var item in mterials)
  79. //{
  80. // var lastestStockInfo = item.FmsMaterialStored
  81. // .Where(ms=>ms.IsApproval == true)
  82. // .OrderByDescending(ms => ms.MaterialStoredId)
  83. // .Take(1).FirstOrDefault();
  84. // if (lastestStockInfo == null) // Sotred 정보가 없는 경우
  85. // {
  86. // item.StockCount = 0;
  87. // item.StockAmount = 0;
  88. // continue;
  89. // }
  90. // var releasedMaterials = item.FmsMaterialRelease
  91. // .Where(mr=>mr.IsConfirmed == true )
  92. // .ToList();
  93. // if (releasedMaterials.Count == 0) // Release 정보가 없는 경우
  94. // {
  95. // item.StockCount = lastestStockInfo.StockCount;
  96. // item.StockAmount = lastestStockInfo.StockCount * lastestStockInfo.UnitCost;
  97. // continue;
  98. // }
  99. // var releaseCount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId != 2).Sum(r2 => r2.MaterialCount).Value;
  100. // var releaseTotalAmount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId != 2).Sum(r2 => r2.ReleaseTotalCost).Value;
  101. // var releaseReturnCount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId == 2).Sum(r2 => r2.MaterialCount).Value;
  102. // var releaseReturnTotalAmount = releasedMaterials.Where(r2 => r2.AdjustmentTypeId == 2).Sum(r2 => r2.ReleaseTotalCost).Value;
  103. // item.StockCount = lastestStockInfo.StockCount - releaseCount + releaseReturnCount;
  104. // item.StockAmount = (lastestStockInfo.StockCount * lastestStockInfo.UnitCost) - releaseTotalAmount + releaseReturnTotalAmount;
  105. //}
  106. #endregion
  107. return mterials.ToList();
  108. }
  109. private string GetWHouseName(int id)
  110. {
  111. var W = db.FmsMaterialWarehouse
  112. .Where(mr => mr.WarehouseId == id)
  113. .FirstOrDefault();
  114. if (W == null) return "";
  115. else return W.Name;
  116. }
  117. private List<FmsMaterialStockHistoryAll> GetFmsMaterialStockHistory()
  118. {
  119. List<FmsMaterialStockHistoryAll> dailiyHistoryList = new List<FmsMaterialStockHistoryAll>();
  120. var storedList = db.FmsMaterialStored
  121. .Where(ms => ms.IsApproval == true)
  122. .ToList();
  123. var releaseList = db.FmsMaterialRelease
  124. .Where(mr => mr.IsConfirmed == true)
  125. .ToList();
  126. // 입고정보로 데이터 생성
  127. foreach (var item in storedList)
  128. {
  129. var dailyStoredInfo = dailiyHistoryList
  130. .Where(d => d.MaterialId == item.MaterialId)
  131. .FirstOrDefault();
  132. if (dailyStoredInfo == null)
  133. {
  134. FmsMaterialStockHistoryAll d = new FmsMaterialStockHistoryAll()
  135. {
  136. SiteId = item.SiteId,
  137. BusinessFieldId = item.BusinessFieldId,
  138. BusinessFieldName = item.CmBusinessField.Name,
  139. Unit = item.FmsMaterial.Unit,
  140. Standard = item.FmsMaterial.Standard,
  141. YearMonthDate = new DateTime(item.StoredDate.Year, item.StoredDate.Month, item.StoredDate.Day),
  142. YearMonth = new DateTime(item.StoredDate.Year, item.StoredDate.Month, 1),
  143. MaterialId = item.MaterialId,
  144. MaterialName = item.FmsMaterial.Name,
  145. MaterialCode = item.FmsMaterial.MaterialCode,
  146. StoredCount = item.StoredCount,
  147. StockCount = item.StockCount,
  148. StockAmount = (item.StoredCount * item.UnitCost),//+ dailiyHistoryList.Where(b => b.MaterialId == item.MaterialId).Sum(c => c.StockAmount),
  149. FirstClassId = item.FmsMaterial.FirstClassId != null ? item.FmsMaterial.FirstClassId.Value : 0,
  150. SecondClassId = item.FmsMaterial.SecondClassId != null ? item.FmsMaterial.SecondClassId.Value : 0,
  151. ThirdClassId = item.FmsMaterial.ThirdClassId != null ? item.FmsMaterial.ThirdClassId.Value : 0,
  152. //hcLee 2015 11 16
  153. WarehouseId = item.WarehouseId,
  154. //WHouseName = GetWHouseName(item.WarehouseId),
  155. };
  156. dailiyHistoryList.Add(d);
  157. }
  158. else
  159. {
  160. dailyStoredInfo.StoredCount += item.StoredCount;
  161. dailyStoredInfo.StockCount = item.StockCount;
  162. dailyStoredInfo.StockAmount += (item.StoredCount * item.UnitCost); // + dailiyHistoryList.Where(b => b.MaterialId == item.MaterialId).Sum(c => c.StockAmount);
  163. //hcLee 2015 11 16
  164. dailyStoredInfo.WarehouseId = item.WarehouseId;
  165. //WHouseName = GetWHouseName(item.WarehouseId),
  166. }
  167. }
  168. // 출고 정보로 데이터 생성
  169. foreach (var item in releaseList)
  170. {
  171. var dailyReleasedInfo = dailiyHistoryList
  172. .Where(d => d.MaterialId == item.MaterialId)
  173. .FirstOrDefault();
  174. if (dailyReleasedInfo == null)
  175. {
  176. FmsMaterialStockHistoryAll d = new FmsMaterialStockHistoryAll()
  177. {
  178. SiteId = item.SiteId,
  179. BusinessFieldId = item.BusinessFieldId,
  180. BusinessFieldName = item.CmBusinessField.Name,
  181. Unit = item.FmsMaterial.Unit,
  182. Standard = item.FmsMaterial.Standard,
  183. YearMonthDate = new DateTime(item.ReleaseDate.Year, item.ReleaseDate.Month, item.ReleaseDate.Day),
  184. YearMonth = new DateTime(item.ReleaseDate.Year, item.ReleaseDate.Month, 1),
  185. MaterialId = item.MaterialId.Value,
  186. MaterialName = item.FmsMaterial.Name,
  187. MaterialCode = item.FmsMaterial.MaterialCode,
  188. StoredCount = 0,
  189. ReleaseCount = item.AdjustmentTypeId == 0 ? item.MaterialCount.Value : 0,
  190. ReleaseLossCount = item.AdjustmentTypeId == 1 ? item.MaterialCount.Value : 0,
  191. ReleaseReturnCount = item.AdjustmentTypeId == 2 ? item.MaterialCount.Value : 0,
  192. FirstClassId = item.FmsMaterial.FirstClassId != null ? item.FmsMaterial.FirstClassId.Value : 0,
  193. SecondClassId = item.FmsMaterial.SecondClassId != null ? item.FmsMaterial.SecondClassId.Value : 0,
  194. ThirdClassId = item.FmsMaterial.ThirdClassId != null ? item.FmsMaterial.ThirdClassId.Value : 0,
  195. //hcLee 2015 11 16
  196. //WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0,
  197. };
  198. // StockCount 를 체크하기 위한 코드
  199. var tempStoredInfo = dailiyHistoryList
  200. .Where(h => h.MaterialId == item.MaterialId)
  201. .OrderByDescending(m => m.YearMonthDate)
  202. .Take(1).FirstOrDefault();
  203. if (tempStoredInfo == null) { continue; }
  204. d.StockCount = tempStoredInfo.StockCount - d.ReleaseCount - d.ReleaseLossCount + d.ReleaseReturnCount;
  205. if (item.AdjustmentTypeId == 2)
  206. {
  207. d.StockAmount = tempStoredInfo.StockAmount + item.ReleaseTotalCost.Value;
  208. }
  209. else
  210. {
  211. d.StockAmount = tempStoredInfo.StockAmount - item.ReleaseTotalCost.Value;
  212. }
  213. dailiyHistoryList.Add(d);
  214. }
  215. else
  216. {
  217. if (item.AdjustmentTypeId == 2)
  218. {
  219. dailyReleasedInfo.ReleaseReturnCount += item.MaterialCount.Value;
  220. dailyReleasedInfo.StockCount += item.MaterialCount.Value;
  221. dailyReleasedInfo.StockAmount += item.ReleaseTotalCost.Value;
  222. //hcLee 2015 11 16
  223. //dailyReleasedInfo.WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0;
  224. }
  225. else if (item.AdjustmentTypeId == 1)
  226. {
  227. dailyReleasedInfo.ReleaseLossCount += item.MaterialCount.Value;
  228. dailyReleasedInfo.StockCount -= item.MaterialCount.Value;
  229. dailyReleasedInfo.StockAmount -= item.ReleaseTotalCost.Value;
  230. //hcLee 2015 11 16
  231. //dailyReleasedInfo.WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0;
  232. }
  233. else if (item.AdjustmentTypeId == 0)
  234. {
  235. dailyReleasedInfo.ReleaseCount += item.MaterialCount.Value;
  236. dailyReleasedInfo.StockCount -= item.MaterialCount.Value;
  237. dailyReleasedInfo.StockAmount -= item.ReleaseTotalCost.Value;
  238. //hcLee 2015 11 16
  239. //dailyReleasedInfo.WarehouseId = item.WarehouseId != null ? (int)item.WarehouseId : 0;
  240. }
  241. }
  242. }
  243. return dailiyHistoryList;
  244. }
  245. protected override void Dispose(bool disposing)
  246. {
  247. if (disposing)
  248. {
  249. db.Dispose();
  250. }
  251. base.Dispose(disposing);
  252. }
  253. }
  254. }