SitePriceHistoryController.cs 8.3 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234
  1. using System;
  2. using System.Linq;
  3. using System.Threading.Tasks;
  4. using FMSAdmin.Data;
  5. using FMSAdmin.Models;
  6. using FMSAdmin.Entities;
  7. using FMSAdmin.Services;
  8. using Microsoft.AspNetCore.Mvc;
  9. using Microsoft.Extensions.Logging;
  10. using Microsoft.AspNetCore.Authorization;
  11. using Microsoft.EntityFrameworkCore;
  12. using FMSAdmin.Helpers;
  13. using System.Data;
  14. using System.IO;
  15. using OfficeOpenXml;
  16. namespace FMSAdmin.Controllers {
  17. [Authorize]
  18. [ApiController]
  19. [ApiVersion("1")]
  20. [Route("api/[controller]")]
  21. public class SitePriceHistoryController : Controller {
  22. private readonly ILogger<SitePriceHistoryController> _logger;
  23. private readonly FMSContext _context;
  24. private readonly SitePriceHistoryService _service;
  25. public SitePriceHistoryController(
  26. ILogger<SitePriceHistoryController> logger,
  27. FMSContext context,
  28. SitePriceHistoryService service
  29. ) {
  30. _logger = logger;
  31. _context = context;
  32. _service = service;
  33. }
  34. /// <summary>
  35. /// 목록
  36. /// </summary>
  37. [HttpGet]
  38. public IActionResult List([FromQuery] PagingRequest req) {
  39. var query = _FilterAndSort(req);
  40. var list = _ListSelect(query);
  41. var paging = PagingList.Pagination(list, req.page, req.limit);
  42. return Ok(paging);
  43. }
  44. private dynamic _ListSelect(IQueryable<BemsSitePriceHistory> query) {
  45. var list = query.Select(x => new {
  46. x.PriceValue,
  47. });
  48. return list;
  49. }
  50. /// <summary>
  51. /// 에너지(고지서 등록/조회, 고지서비교 탭)
  52. /// </summary>
  53. [HttpGet("[action]")]
  54. public IActionResult GetPriceType(int siteId, int fuelTypeId) {
  55. var query = from x in _context.BemsPriceType
  56. join s in _context.BemsSitePrice on new { x.PriceTypeId } equals new { s.PriceTypeId }
  57. where s.SiteId == siteId
  58. where s.FuelTypeId == fuelTypeId
  59. select new { PriceTypeId = x.PriceTypeId, PriceTypeIdDesc = x.PriceTypeIdDesc };
  60. var list = query.GroupBy(x => new { x.PriceTypeId, x.PriceTypeIdDesc }).Select(x => new { x.Key.PriceTypeId, x.Key.PriceTypeIdDesc }).ToList();
  61. return Ok(list);
  62. }
  63. /// <summary>
  64. /// 에너지(고지서 등록/조회 항목별 값)
  65. /// </summary>
  66. [HttpGet("[action]")]
  67. public IActionResult GetPriceData(int siteId, string payDate, int fuelTypeId, int priceTypeId) {
  68. var list = from x in _context.BemsSitePriceHistory
  69. join s in _context.BemsPriceCode on new { x.PriceCode } equals new { s.PriceCode }
  70. where x.SiteId == siteId
  71. where x.PayDate == payDate
  72. where x.FuelTypeId == fuelTypeId
  73. where x.PriceTypeId == priceTypeId
  74. select new { Mod = "M", SiteId = x.SiteId, PayDate = x.PayDate, PriceTypeId = x.PriceTypeId, FuelTypeId = x.FuelTypeId, PriceCode = x.PriceCode, PriceCodeDesc = s.PriceCodeDesc, PriceValue = x.PriceValue, Unit = s.Unit };
  75. if (list.Count() == 0) {
  76. list = from x in _context.BemsSitePrice
  77. join s in _context.BemsPriceCode on new { x.PriceCode } equals new { s.PriceCode }
  78. where x.SiteId == siteId
  79. where x.FuelTypeId == fuelTypeId
  80. where x.PriceTypeId == priceTypeId
  81. where x.UseYn == "Y"
  82. select new { Mod = "W", SiteId = x.SiteId, PayDate = payDate, PriceTypeId = x.PriceTypeId, FuelTypeId = x.FuelTypeId, PriceCode = x.PriceCode, PriceCodeDesc = s.PriceCodeDesc, PriceValue = string.Empty, Unit = s.Unit };
  83. }
  84. return Ok(list);
  85. }
  86. /// <summary>
  87. /// 에너지(고지서비교 차트)
  88. /// </summary>
  89. [HttpGet("[action]")]
  90. public IActionResult GetChartData(int siteId, int fuelTypeId, int priceTypeId, string priceCode, string baseYear, string compareYear) {
  91. var baseData = from x in _context.BemsSitePriceHistory
  92. where x.SiteId == siteId
  93. where x.FuelTypeId == fuelTypeId
  94. where x.PriceTypeId == priceTypeId
  95. where x.PriceCode == priceCode
  96. where x.PayDate.Substring(0, 4) == baseYear
  97. select new { mon = x.PayDate.Substring(4, 2) + "월", baseVal = Convert.ToDecimal(x.PriceValue == "" ? "0" : x.PriceValue), compareVal = Convert.ToDecimal(0) };
  98. var compareData = from x in _context.BemsSitePriceHistory
  99. where x.SiteId == siteId
  100. where x.FuelTypeId == fuelTypeId
  101. where x.PriceTypeId == priceTypeId
  102. where x.PriceCode == priceCode
  103. where x.PayDate.Substring(0, 4) == compareYear
  104. select new { mon = x.PayDate.Substring(4, 2) + "월", baseVal = Convert.ToDecimal(0), compareVal = Convert.ToDecimal(x.PriceValue == "" ? "0" : x.PriceValue) };
  105. var Data = (from x in baseData.Union(compareData)
  106. group x by x.mon into groupResult
  107. select new {
  108. mon = groupResult.Key,
  109. baseVal = groupResult.Sum(c => c.baseVal),
  110. compareVal = groupResult.Sum(p => p.compareVal)
  111. }).ToList();
  112. for (var i = 1; i <= 12; i++) {
  113. if (!Data.Exists(x => x.mon == i.ToString().PadLeft(2, '0') + "월")) {
  114. Data.Add(new { mon = i.ToString().PadLeft(2, '0') + "월", baseVal = Convert.ToDecimal(0), compareVal = Convert.ToDecimal(0) });
  115. }
  116. }
  117. var result = from x in Data
  118. orderby x.mon
  119. select new { x.mon, x.baseVal, x.compareVal };
  120. return Ok(result);
  121. }
  122. /// <summary>
  123. /// 저장
  124. /// </summary>
  125. /// <param name="data"></param>
  126. /// <returns></returns>
  127. [HttpPost("[action]")]
  128. public IActionResult Save([FromBody] BemsSitePriceHistory[] data) {
  129. _logger.LogInformation("Save");
  130. //_logger.LogInformation(data.Dump());
  131. if (ModelState.IsValid) {
  132. _service.Save(data);
  133. } else {
  134. return BadRequest(ModelState);
  135. }
  136. return Ok();
  137. }
  138. /// <summary>
  139. /// 삭제
  140. /// </summary>
  141. [HttpDelete("{siteId}/{fuelTypeId}/{priceTypeId}")]
  142. public IActionResult Delete(int siteId, int fuelTypeId, int priceTypeId) {
  143. _service.Delete(siteId, fuelTypeId, priceTypeId);
  144. return Ok();
  145. }
  146. // 검색 & 정렬 공통
  147. private IQueryable<BemsSitePriceHistory> _FilterAndSort(PagingRequest req) {
  148. var query = _service.GetAll();
  149. // 기본 Entity 검색
  150. query = query.Filter(req.conditions);
  151. // 기본 Entity 정렬
  152. query = query.Sort(req.sort);
  153. return query;
  154. }
  155. /// <summary>
  156. /// 고지서 종류 조회. (요금제가 등록되지 않은 고지서 종류 제외)
  157. /// </summary>
  158. [HttpGet("[action]")]
  159. public IActionResult GetFuelType(int? siteId) {
  160. var query = _service.GetFuelType(siteId);
  161. var list = query.GroupBy(x => new { x.FuelTypeId, x.Name }).Select(x => new {
  162. x.Key.FuelTypeId,
  163. x.Key.Name
  164. }).ToList();
  165. return Ok(list);
  166. }
  167. /// <summary>
  168. /// 비교요소(요금코드)
  169. /// </summary>
  170. [HttpGet("[action]")]
  171. public IActionResult GetPriceCode(int siteId, int fuelTypeId, int priceTypeId) {
  172. var query = _service.GetPriceCode(siteId, fuelTypeId, priceTypeId);
  173. var list = query.Select(x => new {
  174. PriceCode = x.PriceCode,
  175. PriceCodeDesc = x.PriceCodeNavigation.PriceCodeDesc,
  176. }).ToList();
  177. return Ok(list);
  178. }
  179. }
  180. }