using System; using System.Linq; using System.Threading.Tasks; using FMSAdmin.Data; using FMSAdmin.Models; using FMSAdmin.Entities; using FMSAdmin.Services; using Microsoft.AspNetCore.Mvc; using Microsoft.Extensions.Logging; using Microsoft.AspNetCore.Authorization; using Microsoft.EntityFrameworkCore; using FMSAdmin.Helpers; using System.Data; using System.IO; using OfficeOpenXml; namespace FMSAdmin.Controllers { [Authorize] [ApiController] [ApiVersion("1")] [Route("api/[controller]")] public class SitePriceHistoryController : Controller { private readonly ILogger _logger; private readonly FMSContext _context; private readonly SitePriceHistoryService _service; public SitePriceHistoryController( ILogger logger, FMSContext context, SitePriceHistoryService service ) { _logger = logger; _context = context; _service = service; } /// /// 목록 /// [HttpGet] public IActionResult List([FromQuery] PagingRequest req) { var query = _FilterAndSort(req); var list = _ListSelect(query); var paging = PagingList.Pagination(list, req.page, req.limit); return Ok(paging); } private dynamic _ListSelect(IQueryable query) { var list = query.Select(x => new { x.PriceValue, }); return list; } /// /// 에너지(고지서 등록/조회, 고지서비교 탭) /// [HttpGet("[action]")] public IActionResult GetPriceType(int siteId, int fuelTypeId) { var query = from x in _context.BemsPriceType join s in _context.BemsSitePrice on new { x.PriceTypeId } equals new { s.PriceTypeId } where s.SiteId == siteId where s.FuelTypeId == fuelTypeId select new { PriceTypeId = x.PriceTypeId, PriceTypeIdDesc = x.PriceTypeIdDesc }; var list = query.GroupBy(x => new { x.PriceTypeId, x.PriceTypeIdDesc }).Select(x => new { x.Key.PriceTypeId, x.Key.PriceTypeIdDesc }).ToList(); return Ok(list); } /// /// 에너지(고지서 등록/조회 항목별 값) /// [HttpGet("[action]")] public IActionResult GetPriceData(int siteId, string payDate, int fuelTypeId, int priceTypeId) { var list = from x in _context.BemsSitePriceHistory join s in _context.BemsPriceCode on new { x.PriceCode } equals new { s.PriceCode } where x.SiteId == siteId where x.PayDate == payDate where x.FuelTypeId == fuelTypeId where x.PriceTypeId == priceTypeId 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 }; if (list.Count() == 0) { list = from x in _context.BemsSitePrice join s in _context.BemsPriceCode on new { x.PriceCode } equals new { s.PriceCode } where x.SiteId == siteId where x.FuelTypeId == fuelTypeId where x.PriceTypeId == priceTypeId where x.UseYn == "Y" 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 }; } return Ok(list); } /// /// 에너지(고지서비교 차트) /// [HttpGet("[action]")] public IActionResult GetChartData(int siteId, int fuelTypeId, int priceTypeId, string priceCode, string baseYear, string compareYear) { var baseData = from x in _context.BemsSitePriceHistory where x.SiteId == siteId where x.FuelTypeId == fuelTypeId where x.PriceTypeId == priceTypeId where x.PriceCode == priceCode where x.PayDate.Substring(0, 4) == baseYear select new { mon = x.PayDate.Substring(4, 2) + "월", baseVal = Convert.ToDecimal(x.PriceValue == "" ? "0" : x.PriceValue), compareVal = Convert.ToDecimal(0) }; var compareData = from x in _context.BemsSitePriceHistory where x.SiteId == siteId where x.FuelTypeId == fuelTypeId where x.PriceTypeId == priceTypeId where x.PriceCode == priceCode where x.PayDate.Substring(0, 4) == compareYear select new { mon = x.PayDate.Substring(4, 2) + "월", baseVal = Convert.ToDecimal(0), compareVal = Convert.ToDecimal(x.PriceValue == "" ? "0" : x.PriceValue) }; var Data = (from x in baseData.Union(compareData) group x by x.mon into groupResult select new { mon = groupResult.Key, baseVal = groupResult.Sum(c => c.baseVal), compareVal = groupResult.Sum(p => p.compareVal) }).ToList(); for (var i = 1; i <= 12; i++) { if (!Data.Exists(x => x.mon == i.ToString().PadLeft(2, '0') + "월")) { Data.Add(new { mon = i.ToString().PadLeft(2, '0') + "월", baseVal = Convert.ToDecimal(0), compareVal = Convert.ToDecimal(0) }); } } var result = from x in Data orderby x.mon select new { x.mon, x.baseVal, x.compareVal }; return Ok(result); } /// /// 저장 /// /// /// [HttpPost("[action]")] public IActionResult Save([FromBody] BemsSitePriceHistory[] data) { _logger.LogInformation("Save"); //_logger.LogInformation(data.Dump()); if (ModelState.IsValid) { _service.Save(data); } else { return BadRequest(ModelState); } return Ok(); } /// /// 삭제 /// [HttpDelete("{siteId}/{fuelTypeId}/{priceTypeId}")] public IActionResult Delete(int siteId, int fuelTypeId, int priceTypeId) { _service.Delete(siteId, fuelTypeId, priceTypeId); return Ok(); } // 검색 & 정렬 공통 private IQueryable _FilterAndSort(PagingRequest req) { var query = _service.GetAll(); // 기본 Entity 검색 query = query.Filter(req.conditions); // 기본 Entity 정렬 query = query.Sort(req.sort); return query; } /// /// 고지서 종류 조회. (요금제가 등록되지 않은 고지서 종류 제외) /// [HttpGet("[action]")] public IActionResult GetFuelType(int? siteId) { var query = _service.GetFuelType(siteId); var list = query.GroupBy(x => new { x.FuelTypeId, x.Name }).Select(x => new { x.Key.FuelTypeId, x.Key.Name }).ToList(); return Ok(list); } /// /// 비교요소(요금코드) /// [HttpGet("[action]")] public IActionResult GetPriceCode(int siteId, int fuelTypeId, int priceTypeId) { var query = _service.GetPriceCode(siteId, fuelTypeId, priceTypeId); var list = query.Select(x => new { PriceCode = x.PriceCode, PriceCodeDesc = x.PriceCodeNavigation.PriceCodeDesc, }).ToList(); return Ok(list); } } }