TenantController.cs 15 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369
  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 FMSAdmin.Helpers;
  12. using System.Data;
  13. using System.IO;
  14. using OfficeOpenXml;
  15. using Microsoft.AspNetCore.Http;
  16. using System.Collections.Generic;
  17. namespace FMSAdmin.Controllers {
  18. [Authorize]
  19. [ApiController]
  20. [ApiVersion("1")]
  21. [Route("api/[controller]")]
  22. public class TenantController : Controller {
  23. private readonly ILogger<TenantController> _logger;
  24. private readonly FMSContext _context;
  25. private readonly TenantService _service;
  26. private readonly StorageHelper _storage;
  27. public TenantController(
  28. ILogger<TenantController> logger,
  29. FMSContext context,
  30. TenantService service,
  31. StorageHelper storage
  32. ) {
  33. _logger = logger;
  34. _context = context;
  35. _service = service;
  36. _storage = storage;
  37. }
  38. /// <summary>
  39. /// 검색 & 정렬
  40. /// </summary>
  41. /// <param name="req"></param>
  42. /// <returns></returns>
  43. private IQueryable<CmTenant> _FilterAndSort(PagingRequest req) {
  44. var query = _service.GetAll();
  45. query = query.Filter(req.conditions);
  46. if (req.siteId != null) {
  47. query = query.Where(x => x.SiteId == Util.ToInt(req.siteId));
  48. }
  49. if (req.siteIds != null && req.siteIds.Length > 0) {
  50. query = query.Where(x => req.siteIds.Contains(x.SiteId));
  51. }
  52. // 고정정렬 (사옥 준공순)
  53. var sortQuery = query.OrderBy(x => x.CmSite.SortOrderNo);
  54. // 추가 정렬
  55. sortQuery = sortQuery.ThenSort(req.sort);
  56. return sortQuery;
  57. }
  58. /// <summary>
  59. /// 셀렉트 공통
  60. /// </summary>
  61. /// <param name="query"></param>
  62. /// <returns></returns>
  63. private dynamic _ListSelect(IQueryable<CmTenant> query) {
  64. var list = query.Select(x => new {
  65. x.SiteId,
  66. SiteName = x.CmSite.Name,
  67. x.TenantId,
  68. x.FloorId,
  69. FloorName = x.CmFloor.Name,
  70. SortNum = x.CmFloor.CmFloorCode.SortNum,
  71. x.Name,
  72. AreaSize = AreaParse(x.AreaSize),
  73. ExclusiveArea = AreaParse(x.ExclusiveArea),
  74. x.RepresentativePhone,
  75. x.CompanyTypeId,
  76. CompanyTypeName = x.CmCompanyType.Name,
  77. x.RepresentativeName,
  78. x.Fax,
  79. x.ChargeName,
  80. x.ChargePhone,
  81. x.AddressZip,
  82. x.Address1,
  83. x.Address2,
  84. x.Homepage,
  85. x.Comment,
  86. x.ChargeEmail,
  87. IsUse = x.IsUse.GetValueOrDefault() ? "Y" : "N",
  88. x.BuildingId,
  89. BuildingName = x.CmBuilding.Name
  90. });
  91. return list;
  92. }
  93. private static string AreaParse(String areaVal) {
  94. string returnVal = "";
  95. float val;
  96. if (!Util.IsNullOrEmpty(areaVal) && float.TryParse(areaVal, out val)) {
  97. returnVal = val.ToString("#,##0.00") + "㎡ (" + (val / 3.3058).ToString("#,##0.00") + "평)";
  98. }
  99. return returnVal;
  100. }
  101. /// <summary>
  102. /// 목록
  103. /// </summary>
  104. /// <param name="req"></param>
  105. /// <returns></returns>
  106. [HttpGet]
  107. public IActionResult List([FromQuery] PagingRequest req) {
  108. var query = _FilterAndSort(req);
  109. var list = _ListSelect(query);
  110. var paging = PagingList.Pagination(list, req.page, req.limit);
  111. return Ok(paging);
  112. }
  113. // 엑셀 다운로드
  114. [AllowAnonymous]
  115. [HttpGet("excel")]
  116. public IActionResult ExportExcel([FromQuery] PagingRequest req, string filename) {
  117. var query = _FilterAndSort(req);
  118. var list = _ListSelect(query);
  119. var stream = new MemoryStream();
  120. using (var package = new ExcelPackage(stream)) {
  121. var workSheet = package.Workbook.Worksheets.Add("Sheet1");
  122. workSheet.Cells.LoadFromCollection(list, true);
  123. if (req.columns != null && req.columns.Length > 0) {
  124. workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename);
  125. }
  126. package.Save();
  127. }
  128. stream.Position = 0;
  129. string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
  130. return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
  131. }
  132. /// <summary>
  133. /// 조회
  134. /// </summary>
  135. [HttpGet("{siteId}/{id}")]
  136. public IActionResult Get(int siteId, int id) {
  137. var data = _service.Get(id, siteId);
  138. if (data.Count() == 0) {
  139. return NotFound("사이트 정보를 찾을 수 없습니다.");
  140. }
  141. var list = data.Select(x => new {
  142. x.SiteId,
  143. x.TenantId,
  144. x.CompanyTypeId,
  145. x.BuildingId,
  146. x.FloorId,
  147. x.Name,
  148. x.RepresentativeName,
  149. x.RepresentativePhone,
  150. x.Fax,
  151. AreaSize = x.AreaSize,
  152. x.ChargeName,
  153. x.ChargePhone,
  154. x.AddressZip,
  155. x.Address1,
  156. x.Address2,
  157. x.Comment,
  158. x.Homepage,
  159. x.IsUse,
  160. x.ChargeEmail,
  161. ExclusiveArea = x.ExclusiveArea,
  162. CmCompanyType = new {
  163. x.CmCompanyType.Name
  164. },
  165. CmSite = new {
  166. x.CmSite.Name,
  167. },
  168. CmBuilding = new {
  169. x.CmBuilding.Name
  170. },
  171. CmFloor = new {
  172. x.CmFloor.Name
  173. },
  174. });
  175. return Ok(list.First());
  176. }
  177. /// <summary>
  178. /// 수정
  179. /// </summary>
  180. [HttpPost("{siteId}/{id}")]
  181. public IActionResult Edit(int siteId, int id, [FromBody] CmTenant data) {
  182. _logger.LogInformation("Edit");
  183. _logger.LogInformation("id:" + id);
  184. _logger.LogInformation("siteId:" + siteId);
  185. //_logger.LogInformation(data.Dump());
  186. if (id != data.TenantId) {
  187. return BadRequest("잘못된 접근입니다.>>");
  188. }
  189. if (ModelState.IsValid) {
  190. _service.Edit(id, siteId, data);
  191. } else {
  192. return BadRequest(ModelState);
  193. }
  194. return Ok();
  195. }
  196. /// <summary>
  197. /// 등록
  198. /// </summary>
  199. [HttpPut]
  200. public IActionResult Create([FromBody] CmTenant data) {
  201. _logger.LogInformation("Create");
  202. //_logger.LogInformation(data.Dump());
  203. if (ModelState.IsValid) {
  204. _service.Create(data);
  205. } else {
  206. return BadRequest(ModelState);
  207. }
  208. return Ok();
  209. }
  210. [HttpPost]
  211. [Route("[action]")]
  212. public IActionResult ImportExcelAdmin(IFormFile excelFile) {
  213. return (ImportExcel(excelFile, true));
  214. }
  215. /// <summary>
  216. /// 엑셀파일 임포트
  217. /// </summary>
  218. /// <param name="file"></param>
  219. /// <returns></returns>
  220. [HttpPost]
  221. [Route("[action]")]
  222. public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false) {
  223. IList<CmTenant> list = new List<CmTenant>();
  224. var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault();
  225. using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) {
  226. var worksheet = excelPackage.Workbook.Worksheets[0];
  227. int colCnt = isAdmin ? 18 : 17;
  228. ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
  229. //헤더세팅
  230. excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);
  231. for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) {
  232. if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
  233. var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사옥(*)", i, true) : "";
  234. var floor = excelUploadHelper.GetValue(worksheet, "층 명칭(*)", i, true);
  235. var name = excelUploadHelper.GetValue(worksheet, "회사 명칭(*)", i, true);
  236. var areaSize = excelUploadHelper.GetValue(worksheet, "임대면적", i);
  237. var exclusiveArea = excelUploadHelper.GetValue(worksheet, "전용면적", i);
  238. var representativeName = excelUploadHelper.GetValue(worksheet, "회사 대표(*)", i, true);
  239. var representativePhone = excelUploadHelper.GetValue(worksheet, "대표 번호(*)", i, true);
  240. var companyType = excelUploadHelper.GetValue(worksheet, "회사 구분(*)", i, true);
  241. var fax = excelUploadHelper.GetValue(worksheet, "팩스 번호", i);
  242. var chargeName = excelUploadHelper.GetValue(worksheet, "담당자 명", i);
  243. var chargePhone = excelUploadHelper.GetValue(worksheet, "담당자 전화", i);
  244. var chargeEmail = excelUploadHelper.GetValue(worksheet, "담당자 이메일", i);
  245. var addresszip = excelUploadHelper.GetValue(worksheet, "우편 번호", i);
  246. var address1 = excelUploadHelper.GetValue(worksheet, "회사주소1", i);
  247. var address2 = excelUploadHelper.GetValue(worksheet, "회사주소2", i);
  248. var homepage = excelUploadHelper.GetValue(worksheet, "홈페이지", i);
  249. var comment = excelUploadHelper.GetValue(worksheet, "비고", i);
  250. var building = excelUploadHelper.GetValue(worksheet, "빌딩 명칭(*)", i, true);
  251. _logger.LogInformation("site " + site);
  252. _logger.LogInformation("type " + companyType);
  253. _logger.LogInformation("codnamee " + name);
  254. var rowName = "(" + (i) + "번 행)";
  255. var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
  256. var buildingId = _context.CmBuilding.Where(x => x.Name == building && x.SiteId == siteId).Select(x => x.BuildingId).SingleOrDefault();
  257. var floorId = _context.CmFloor.Where(x => x.Name == floor && x.BuildingId == buildingId && x.SiteId == siteId).Select(x => x.FloorId).SingleOrDefault();
  258. var companyTypeId = _context.CmCompanyType.Where(x => x.Name == companyType/* && x.SiteId == siteId*/).Select(x => x.CompanyTypeId).SingleOrDefault();
  259. if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥", rowName);
  260. excelUploadHelper.EntityIdCheck(buildingId, "빌딩명칭", rowName);
  261. excelUploadHelper.EntityIdCheck(floorId, "층명칭", rowName);
  262. excelUploadHelper.EntityIdCheck(companyTypeId, "회사구분", rowName);
  263. if (!Util.IsNullOrEmpty(areaSize)) excelUploadHelper.FloatCheck(areaSize, "임대면적", rowName);
  264. if (!Util.IsNullOrEmpty(exclusiveArea)) excelUploadHelper.FloatCheck(exclusiveArea, "전용면적", rowName);
  265. var tenant = new CmTenant {
  266. SiteId = siteId,
  267. BuildingId = buildingId,
  268. FloorId = floorId,
  269. CompanyTypeId = companyTypeId,
  270. Name = name,
  271. RepresentativeName = representativeName,
  272. RepresentativePhone = representativePhone,
  273. IsUse = true,
  274. Fax = fax,
  275. ChargeName = chargeName,
  276. ChargePhone = chargePhone,
  277. ChargeEmail = chargeEmail,
  278. AreaSize = areaSize,
  279. ExclusiveArea = exclusiveArea,
  280. AddressZip = addresszip,
  281. Address1 = address1,
  282. Address2 = address2,
  283. Homepage = homepage,
  284. Comment = comment,
  285. ExcelRowNum = i
  286. };
  287. // 글자수 체크
  288. TryValidateModel(tenant);
  289. if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
  290. ModelState.Values
  291. .SelectMany(x => x.Errors)
  292. .Select(x => x.ErrorMessage)) + rowName);
  293. //중복 체크
  294. var overlapRowNum = list.Where(x => x.SiteId == tenant.SiteId
  295. && x.BuildingId == tenant.BuildingId
  296. && x.FloorId == tenant.FloorId
  297. && x.Name == tenant.Name).Select(x => x.ExcelRowNum).SingleOrDefault();
  298. if (overlapRowNum != 0) {
  299. throw new ServiceException("파일 내 동일 정보가 존재합니다.[빌딩명칭-층명칭-회사명칭]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");
  300. }
  301. if (_service.checkName(tenant, true) > 0) {
  302. throw new ServiceException("기존 입주사와 동일 정보가 존재합니다.[빌딩명칭-층명칭-회사명칭]" + rowName);
  303. }
  304. // 데이터 값 설정
  305. list.Add(tenant);
  306. }
  307. }
  308. }
  309. if (list.Count > 0) {
  310. _service.ImportExcel(list);
  311. return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
  312. } else {
  313. throw new ServiceException("등록할 데이터가 없습니다.");
  314. }
  315. }
  316. /// <summary>
  317. /// 삭제
  318. /// </summary>
  319. [HttpDelete("{siteId}/{id}")]
  320. public IActionResult Delete(int siteId, int id) {
  321. _service.Delete(id, siteId);
  322. return Ok();
  323. }
  324. }
  325. }