
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;
using Newtonsoft.Json;
using Microsoft.AspNetCore.Http;
using System.Collections.Generic;
using System.Collections;
using System.IO.Compression;

namespace FMSAdmin.Controllers {
    [Authorize]
    [ApiController]
    [ApiVersion("1")]
    [Route("api/[controller]")]

    public class FacilityController : Controller {
        private readonly ILogger<FacilityController> _logger;
        private readonly FMSContext _context;
        private readonly FacilityService _service;
        private readonly StorageHelper _storage;
        private readonly QrCodeHelper _QrCode;
        public FacilityController(
            ILogger<FacilityController> logger,
            FMSContext context,
            FacilityService service,
            StorageHelper storage,
            QrCodeHelper QrCode
            ) {
            _logger = logger;
            _context = context;
            _service = service;
            _storage = storage;
            _QrCode = QrCode;
        }

        /// <summary>
        /// 목록
        /// </summary>
        [HttpGet]
        public IActionResult List([FromQuery] PagingRequest req, [FromQuery] string siteId) {
            var query = _FilterAndSort(req);

            query = query.Where(x => !string.IsNullOrEmpty(x.Name));
            //query = query.Where(x => !string.IsNullOrEmpty(x.Nickname));

            var list = _ListSelect(query);

            var paging = PagingList.Pagination(list, req.page, req.limit);
            return Ok(paging);
        }


        private dynamic _ListSelect(IQueryable<CmFacility> query) {

            var list = query.Select(x => new {
                x.SiteId,
                SiteName = x.CmSite.Name,
                SiteSort = x.CmSite.SortOrderNo,
                x.FacilityCode,
                FirstClassId = x.FmsFacilityCodeClass.FacilityClassId,
                FirstClassName = x.FmsFacilityCodeClass.Name,
                SecondClassId = x.FmsFacilityCodeClass1.FacilityClassId,
                SecondClassName = x.FmsFacilityCodeClass1.Name,
                x.FacilityTypeId,
                FmsFacilityCodeType = x.BemsFacilityType.Name,
                ThirdClassId = x.FmsFacilityCodeClass2.FacilityClassId,
                ThirdClassName = x.FmsFacilityCodeClass2.Name,
                x.IsUse,
                FacilityName = x.Name,
                x.ManufactureModel,
                x.FacilityCapacity,
                x.Rfid,
                x.DeviceType,
                x.Nickname,
                x.FacilityUsage,
                x.Manufacturer,
                x.ManufactureDate,
                x.RatedPowerConsumption,
                x.RatedCop,
                x.ManufactureSerial,
                x.Supplier,
                x.SupplierPhoneNo,
                FirstdFacilityLoc = x.CmBuilding.Name,
                SecondFacilityLoc = x.CmFloor.Name,
                ThirdFacilityLoc = x.CmZone.Name,
                x.InstallDate,
                x.OperationStartDate,
                MainContactorUser = x.MainContactor.Name,
                SubContactorUser = x.SubContactor.Name,
                x.FacilityCost,
                x.FacilityCount,
                FuelType = x.BemsFuelType.Name,
                ContractType = x.BemsNoticePriceContractType.Name,
                x.Note,
                x.Sort1,
                x.Sort2,
                FacilityLoc = x.CmBuilding.Name + "-"  + x.CmFloor.Name + "-" + x.CmZone.Name
            }).AsEnumerable().OrderBy(x => x.SiteSort).ThenBy(x => x.FacilityTypeId).ThenBy(x => x.Sort1).ThenBy(x => x.Sort2).AsQueryable();
            //설비명 Sort 때문에 임시. 후에 수정해야 함.
            return list;
        }

        /// <summary>
        /// 엑셀파일 익스포트
        /// </summary>
        /// <param name="req"></param>
        /// <returns></returns>
        [HttpGet("excel")]
        public IActionResult ExportExcel([FromQuery] PagingRequest req, string filename) {
            req.page = 1;
            req.limit = 0;
            var query = _FilterAndSort(req);

            var list = query.Select(x => new {
                // SiteName = x.CmSite.Name,
                // FirstClassName = x.FmsFacilityCodeClass.Name,
                // SecondClassName = x.FmsFacilityCodeClass1.Name,
                // ThirdClassName = x.FmsFacilityCodeClass2.Name,
                // FacilityName = x.Name + "/" + x.Nickname,
                // x.ManufactureModel,
                // x.FacilityCapacity,
                // FacilityLoc = x.CmFloor.Name,
                x.SiteId,
                SiteName = x.CmSite.Name,
                x.FacilityCode,
                FmsFacilityCodeType = x.BemsFacilityType.Name,
                x.DeviceType,
                FacilityName = x.Name,
                x.Nickname,
                x.FacilityUsage,
                x.Manufacturer,
                ManufactureDate = x.ManufactureDate != null ? Convert.ToDateTime(x.ManufactureDate).ToString("yyyyMMdd") : "",
                //FirstClassId = x.FmsFacilityCodeClass.FacilityClassId,
                FirstClassName = x.FmsFacilityCodeClass.Name,
                //SecondClassId = x.FmsFacilityCodeClass1.FacilityClassId,
                SecondClassName = x.FmsFacilityCodeClass1.Name,
                //ThirdClassId = x.FmsFacilityCodeClass2.FacilityClassId,
                ThirdClassName = x.FmsFacilityCodeClass2.Name,
                x.FacilityCapacity,
                x.Rfid,
                x.RatedPowerConsumption,
                x.RatedCop,
                x.ManufactureSerial,
                x.ManufactureModel,
                x.Supplier,
                x.SupplierPhoneNo,
                FirstdFacilityLoc = x.CmBuilding.Name,
                SecondFacilityLoc = x.CmFloor.Name,
                ThirdFacilityLoc = x.CmZone.Name,
                InstallDate = x.InstallDate != null ? Convert.ToDateTime(x.InstallDate).ToString("yyyyMMdd") : "",
                OperationStartDate = x.OperationStartDate != null ? Convert.ToDateTime(x.OperationStartDate).ToString("yyyyMMdd") : "",
                MainContactorUser = x.MainContactor.Name,
                SubContactorUser = x.SubContactor.Name,
                x.FacilityCost,
                x.FacilityCount,
                FuelType = x.BemsFuelType.Name,
                ContractType = x.BemsNoticePriceContractType.Name,
                x.Note,
                IsUse = Util.ToBool(x.IsUse) ? "활성화" : "취소",
                x.FacilityTypeId,
                SiteSort = x.CmSite.SortOrderNo,
                x.Sort1,
                x.Sort2,
            }).AsEnumerable().OrderBy(x => x.SiteSort).ThenBy(x => x.FacilityTypeId).ThenBy(x => x.Sort1).ThenBy(x => x.Sort2).AsQueryable();
            //설비명 Sort 때문에 임시. 후에 수정해야 함.

            var stream = new MemoryStream();
            using (var package = new ExcelPackage(stream)) {
                var workSheet = package.Workbook.Worksheets.Add("Sheet1");

                workSheet.Cells.LoadFromCollection(list, true);

                if (req.columns != null && req.columns.Length > 0) {
                    workSheet.AddStyle(req.columns, req.sort?.order?.ToLower() == "asc", filename);
                }
                package.Save();
            }
            stream.Position = 0;
            string excelName = $"excel-{DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xlsx";
            return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", excelName);
        }



        [HttpGet("qr")]
        public IActionResult QRDown([FromQuery] PagingRequest req) {

            req.page = 1;
            req.limit = 0;
            var query = _FilterAndSort(req);

            var folder = DateTime.Now.ToString("yyyyMMddhhmmsss");
            var fileName = $"QR코드_{folder}.zip";
            var list = query.Where(x => x.Rfid != null && x.Rfid != "").Select(x => new {
                x.SiteId,
                x.FacilityCode,
                x.Rfid,
            });

            if (list.Count() == 0) {
                return BadRequest("다운로드 가능한 QR코드가 없습니다.");
            }

            using (MemoryStream newZip = new MemoryStream()) {
                //using (FileStream newZip = new FileStream(@"c:\users\exampleuser\release.zip", FileMode.CreateNew)) {
                using (ZipArchive archive = new ZipArchive(newZip, ZipArchiveMode.Create)) {
                    foreach (var item in list) {
                        archive.CreateEntryFromFile(_storage.GetLocalPath($"/qr/facility/{item.SiteId}_{item.FacilityCode}.jpg"), $"{item.SiteId}_{item.FacilityCode}.jpg");
                    }
                }
                return File(newZip.ToArray(), "application/zip", fileName);
            }
        }

        [HttpGet("QrCodeRegen")]
        [AllowAnonymous]
        public IActionResult QrCodeRegen() {
            var query = _service.GetAll();
            var list = query.Where(x => x.Rfid != null && x.Rfid != "").Select(x => new {
                x.SiteId,
                x.FacilityCode,
                x.Rfid,
            });

            foreach (var item in list) {
                _QrCode.Save(item.Rfid, $"{item.SiteId}_{item.FacilityCode}", "Facility");
            }

            return Content(":)");
        }

        /// <summary>
        /// 수정
        /// </summary>
        [HttpPost("{siteId}/{id}")]
        public IActionResult Edit(int siteId, int id, [FromBody] CmFacility data) {
            //_logger.LogInformation(data.Dump());

            if (siteId != data.SiteId && id != data.FacilityCode) {
                return NotFound();
            }

            //ModelState.Remove("Password"); // 이 수정에서는 비밀번호 제외
            ModelState.RemoveStartOf("CmFile");
            if (ModelState.IsValid) {
                _service.Save(data);
            } else {
                foreach (var ms in ModelState.ToArray()) {
                    _logger.LogInformation(ms.Key);
                }
                return BadRequest(ModelState);
            }

            return Ok();
        }

        [HttpGet("{siteId}/{id}")]
        public IActionResult Get(int siteId, int id) {
            var data = _service.Get(id, siteId);
            if (data.Count() == 0) {
                return NotFound("정보를 찾을 수 없습니다.");
            }
            var list = data.Select(x => new {
                x.SiteId,
                CmSite = new {
                    x.CmSite.Name,
                },
                x.FacilityTypeId,
                FmsFacilityCodeType = new {
                    x.BemsFacilityType.Name,
                },
                x.FacilityCode,
                x.FirstClassId,
                FmsFacilityCodeClass = new {
                    x.FmsFacilityCodeClass.FacilityClassId,
                    x.FmsFacilityCodeClass.Name,
                },

                x.SecondClassId,
                FmsFacilityCodeClass1 = new {
                    x.FmsFacilityCodeClass1.FacilityClassId,
                    x.FmsFacilityCodeClass1.Name,
                },
                x.ThirdClassId,
                FmsFacilityCodeClass2 = new {
                    x.FmsFacilityCodeClass2.FacilityClassId,
                    x.FmsFacilityCodeClass2.Name,
                },
                x.DeviceType,
                x.ParentFacilityCode,
                x.Name,
                x.Nickname,
                x.Rfid,
                x.Manufacturer,
                x.ManufactureDate,
                x.ManufactureSerial,
                x.ManufactureModel,
                x.Supplier,
                x.SupplierPhoneNo,
                x.BuildingId,
                CmBuilding = new {
                    x.CmBuilding.Name,
                },
                x.FloorId,
                CmFloor = new {
                    x.CmFloor.Name,
                },
                x.ZoneId,
                CmZone = new {
                    x.CmZone.Name,
                },
                x.InstallDate,
                x.OperationStartDate,
                x.FacilityCapacity,
                x.FacilityCost,
                x.FacilityCount,
                x.FacilitySeviceLifeYear,
                x.FacilityUsage,
                x.RatedPowerConsumption,
                x.RatedCop,
                x.MainContactorUserId,
                MainContactorUserName = new {
                    x.MainContactor.Name,
                },
                x.SubContactorUserId,
                SubContactorUserName = new {
                    x.SubContactor.Name,
                },
                x.Note,
                x.Reserved1,
                x.Reserved2,
                x.IsUse,
                //x.FileId,
                CmFile = _storage.ParseFile(x.CmFile),
                x.IsVirtualFacility,
                x.FuelTypeId,
                FuelTypeName = new {
                    x.BemsFuelType.Name
                },
                x.ContractType,
                PriceContractType = new {
                    x.BemsNoticePriceContractType.Name,

                },
                x.Multiple
            });

            return Ok(list.First());
        }

        private void ExcelHeaderError() {
            throw new Exception("엑셀 형식이 일치하지 않습니다");
        }


        [HttpPost]
        [Route("[action]")]
        public IActionResult ImportExcelAdmin(IFormFile excelFile) {
            return (ImportExcel(excelFile, true));
        }

        /// <summary>
        /// 엑셀파일 임포트
        /// </summary>
        /// <param name="file"></param>
        /// <returns></returns>
        [HttpPost]
        [Route("[action]")]
        public IActionResult ImportExcel(IFormFile excelFile, bool isAdmin = false) {

            IList<CmFacility> list = new List<CmFacility>();
            var userInfo = _context.CmUser.Where(x => x.UserId == User.Identity.Name).SingleOrDefault();

            using (ExcelPackage excelPackage = new ExcelPackage(excelFile.OpenReadStream())) {
                var worksheet = excelPackage.Workbook.Worksheets[0];
                int colCnt = isAdmin ? 31 : 30;
                ExcelUploadHepler excelUploadHelper = new ExcelUploadHepler();
                //헤더세팅
                excelUploadHelper.GenerateColumns(worksheet, 1, colCnt);

                for (int i = worksheet.Dimension.Start.Row + 1; i <= worksheet.Dimension.End.Row; i++) {
                    if (!excelUploadHelper.IsRowEmpty(worksheet, i, colCnt)) {
                        var site = isAdmin ? excelUploadHelper.GetValue(worksheet, "사옥명(*)", i, true) : "";

                        var facilityType = excelUploadHelper.GetValue(worksheet, "시설유형(*)", i, true);
                        var deviceType = excelUploadHelper.GetValue(worksheet, "종류", i);
                        var name = excelUploadHelper.GetValue(worksheet, "시설명칭(*)", i, true);
                        //var nickName = excelUploadHelper.GetValue(worksheet, "시설별칭(*)", i, true);
                        var facilityUsage = excelUploadHelper.GetValue(worksheet, "시설용도(*)", i);
                        var manufacturer = excelUploadHelper.GetValue(worksheet, "제조업체", i);
                        var manufactureDt = excelUploadHelper.GetValue(worksheet, "제조일자", i);
                        var isUse = excelUploadHelper.GetValue(worksheet, "사용여부(*)[활성화 or 취소]", i, true);
                        var fristClass = excelUploadHelper.GetValue(worksheet, "시설 대분류(*)", i, true);
                        var secondClass = excelUploadHelper.GetValue(worksheet, "시설 중분류", i);
                        var thirdClass = excelUploadHelper.GetValue(worksheet, "시설 소분류", i);
                        var facilityCapacity = excelUploadHelper.GetValue(worksheet, "시설용량", i);
                        var qrCode = excelUploadHelper.GetValue(worksheet, "QR코드", i);
                        var ratedPowerConsumption = excelUploadHelper.GetValue(worksheet, "정격전력소비량", i);
                        var rateCop = excelUploadHelper.GetValue(worksheet, "정격COP", i);
                        var manufactureSerial = excelUploadHelper.GetValue(worksheet, "제조번호", i);
                        var manufactureModel = excelUploadHelper.GetValue(worksheet, "제조모델", i);
                        var supplier = excelUploadHelper.GetValue(worksheet, "납품업체", i);
                        var supplierPhoneNo = excelUploadHelper.GetValue(worksheet, "업체전화", i);
                        var building = excelUploadHelper.GetValue(worksheet, "설치위치(대)", i);
                        var floor = excelUploadHelper.GetValue(worksheet, "설치위치(중)", i);
                        var zone = excelUploadHelper.GetValue(worksheet, "설치위치(소)", i);
                        var installDt = excelUploadHelper.GetValue(worksheet, "설치일자", i);
                        var operStartDt = excelUploadHelper.GetValue(worksheet, "운전시작일", i);
                        var mainContactorUser = excelUploadHelper.GetValue(worksheet, "정담당자", i);
                        var subContactorUser = excelUploadHelper.GetValue(worksheet, "부담당자", i);
                        var facilityCost = excelUploadHelper.GetValue(worksheet, "시설비용", i);
                        var facilityCount = excelUploadHelper.GetValue(worksheet, "시설수량", i);
                        var fuelType = excelUploadHelper.GetValue(worksheet, "연료종류", i);
                        var contractType = excelUploadHelper.GetValue(worksheet, "계약종류", i);
                        var note = excelUploadHelper.GetValue(worksheet, "Note", i);


                        _logger.LogInformation("site " + site);
                        _logger.LogInformation("codnamee " + name);

                        var rowName = "(" + (i) + "번 행)";

                        var siteId = isAdmin ? _context.CmSite.Where(x => x.Name == site && x.IsUse == true).Select(x => x.SiteId).SingleOrDefault() : userInfo.SiteId;
                        int[] virtualFacility = new int[] { 99, 100, 101, 102, 103 };
                        var facilityTypeId = _context.BemsFacilityType.Where(x => x.Name == facilityType && !virtualFacility.Contains(x.FacilityTypeId)).Select(x => x.FacilityTypeId).SingleOrDefault();

                        var fristClassId = _context.FmsFacilityCodeClass.Where(x => x.IsUse == true && x.Name == fristClass && x.ParentFacilityClassId == null).Select(x => x.FacilityClassId).SingleOrDefault();
                        var secondClassId = _context.FmsFacilityCodeClass.Where(x => x.IsUse == true && x.ParentFacilityClassId == fristClassId && x.Name == secondClass).Select(x => x.FacilityClassId).SingleOrDefault();
                        var thirdClassId = _context.FmsFacilityCodeClass.Where(x => x.IsUse == true && x.ParentFacilityClassId == secondClassId && x.Name == thirdClass).Select(x => x.FacilityClassId).SingleOrDefault();

                        var buildingId = _context.CmBuilding.Where(x => x.SiteId == siteId && x.IsUse == true && x.Name == building).Select(x => x.BuildingId).SingleOrDefault();
                        var floorId = _context.CmFloor.Where(x => x.SiteId == siteId && x.BuildingId == buildingId && x.Name == floor).Select(x => x.FloorId).SingleOrDefault();
                        var zoneId = _context.CmZone.Where(x => x.SiteId == siteId && x.FloorId == floorId && x.Name == zone).Select(x => x.ZoneId).SingleOrDefault();

                        var fuelTypeId = _context.BemsFuelType.Where(x => x.Name == fuelType).Select(x => x.FuelTypeId).SingleOrDefault();
                        var contractTypeId = _context.BemsNoticePriceContractType.Where(x => x.FuelTypeId == fuelTypeId && x.Name == contractType).Select(x => x.ContractType).SingleOrDefault();

                        var mainContactorUserId = _context.CmUser.Where(x => x.SiteId == siteId && x.IsUse == true && x.Name == mainContactorUser).Select(x => x.UserId).SingleOrDefault();
                        var subContactorUserId = _context.CmUser.Where(x => x.SiteId == siteId && x.IsUse == true && x.Name == subContactorUser).Select(x => x.UserId).SingleOrDefault();

                        if (isAdmin) excelUploadHelper.EntityIdCheck(siteId, "사옥명", rowName);
                        if (!isUse.Equals("활성화") && !isUse.Equals("취소"))
                            throw new Exception($"사용여부를 정확히 입력해야합니다.[활성화 or 취소] {rowName}");

                        excelUploadHelper.EntityIdCheck(facilityTypeId, "시설유형(*)", rowName);
                        excelUploadHelper.EntityIdCheck(fristClassId, "시설 대분류", rowName);

                        if (secondClass != null) excelUploadHelper.EntityIdCheck(secondClassId, "시설 중분류", rowName);
                        if (thirdClass != null) excelUploadHelper.EntityIdCheck(thirdClassId, "시설 소분류", rowName);

                        manufactureDt = excelUploadHelper.DateTimeCheck(manufactureDt, "제조일자", rowName);
                        installDt = excelUploadHelper.DateTimeCheck(installDt, "설치일자", rowName);
                        operStartDt = excelUploadHelper.DateTimeCheck(operStartDt, "운전시작일", rowName);

                        if (!Util.IsNullOrEmpty(building)) excelUploadHelper.EntityIdCheck(buildingId, "설치위치[대]", rowName);
                        if (!Util.IsNullOrEmpty(floor)) excelUploadHelper.EntityIdCheck(floorId, "설치위치[중]", rowName);
                        if (!Util.IsNullOrEmpty(zone)) excelUploadHelper.EntityIdCheck(zoneId, "설치위치[소]", rowName);

                        if (!Util.IsNullOrEmpty(fuelType)) excelUploadHelper.EntityIdCheck(fuelTypeId, "연료종류", rowName);
                        if (!Util.IsNullOrEmpty(mainContactorUser)) excelUploadHelper.EntityIdCheck(mainContactorUserId, "정담당자", rowName);
                        if (!Util.IsNullOrEmpty(subContactorUser)) excelUploadHelper.EntityIdCheck(subContactorUserId, "부담당자", rowName);
                        if (!Util.IsNullOrEmpty(contractType)) excelUploadHelper.EntityIdCheck(contractTypeId, "계약종류", rowName);

                        excelUploadHelper.NumericCheck(rateCop, "정격COP", rowName);
                        excelUploadHelper.NumericCheck(facilityCost, "시설비용", rowName);
                        excelUploadHelper.NumericCheck(facilityCount, "시설수량", rowName);


                        var facility = new CmFacility {
                            SiteId = siteId,
                            FacilityTypeId = Util.ToInt(facilityTypeId),
                            DeviceType = deviceType,
                            Name = name,
                            //Nickname = nickName,
                            FacilityUsage = facilityUsage,
                            Manufacturer = manufacturer,
                            IsUse = isUse.Equals("활성화") ? true : false,
                            FirstClassId = fristClassId,
                            SecondClassId = secondClassId,
                            ThirdClassId = thirdClassId,
                            FacilityCapacity = facilityCapacity,
                            Rfid = qrCode.Trim(),
                            RatedPowerConsumption = ratedPowerConsumption,
                            ManufactureSerial = manufactureSerial,
                            ManufactureModel = manufactureModel,
                            Supplier = supplier,
                            SupplierPhoneNo = supplierPhoneNo,
                            BuildingId = buildingId,
                            FloorId = floorId,
                            ZoneId = zoneId,
                            MainContactorUserId = mainContactorUserId,
                            SubContactorUserId = subContactorUserId,
                            FuelTypeId = fuelTypeId,
                            ContractType = contractTypeId,
                            Note = note,
                            IsVirtualFacility = Util.ToInt(facilityTypeId) > 99 ? true : false,
                            ExcelRowNum = i,
                        };
                        if (manufactureDt != null) facility.ManufactureDate = DateTime.Parse(manufactureDt);
                        if (installDt != null) facility.InstallDate = DateTime.Parse(installDt);
                        if (operStartDt != null) facility.OperationStartDate = DateTime.Parse(operStartDt);
                        if (rateCop != null) facility.RatedCop = Util.ToInt(rateCop);
                        if (facilityCost != null) facility.FacilityCost = Util.ToInt(facilityCost);
                        if (facilityCount != null) facility.FacilityCount = Util.ToInt(facilityCount);

                        //글자수 체크
                        TryValidateModel(facility);

                        if (!ModelState.IsValid) throw new ServiceException(string.Join(" ",
                                        ModelState.Values
                                        .SelectMany(x => x.Errors)
                                        .Select(x => x.ErrorMessage)) + rowName);

                        //중복체크 
                        //var overlapRowNum = list.Where(x => x.SiteId == facility.SiteId && x.Name == facility.Name && x.Nickname == facility.Nickname).Select(x => x.ExcelRowNum).SingleOrDefault();
                        var overlapRowNum = list.Where(x => x.SiteId == facility.SiteId && x.Name == facility.Name).Select(x => x.ExcelRowNum).SingleOrDefault();

                        if (overlapRowNum != 0)
                            throw new ServiceException("파일 내 동일 정보가 존재합니다.[시설명칭]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");
                        //throw new ServiceException("파일 내 동일 정보가 존재합니다.[시설명칭-시설별칭]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");

                        overlapRowNum = list.Where(x => x.Rfid == facility.Rfid && Util.IsNullOrEmpty(facility.Rfid) == false).Select(x => x.ExcelRowNum).SingleOrDefault();

                        if (overlapRowNum != 0)
                            throw new ServiceException("파일 내 동일 정보가 존재합니다.[QR코드]" + "(" + (overlapRowNum) + "번 행 - " + (i) + "번 행)");

                        if (_service.checkName(facility, true) > 0)
                            throw new ServiceException("기존 시설과 동일 정보가 존재합니다.[시설명칭]" + rowName);
                        //throw new ServiceException("기존 시설과 동일 정보가 존재합니다.[시설명칭-시설별칭]" + rowName);

                        if (!Util.IsNullOrEmpty(facility.Rfid) && _service.checkRfid(facility, true) > 0)
                            throw new ServiceException("기존 시설과 동일 정보가 존재합니다.[QR코드]" + rowName);


                        // 데이터 값 설정
                        list.Add(facility);
                    }
                }
            }

            if (list.Count > 0) {
                _service.ImportExcel(list);
                return Ok($"엑셀업로드를 성공하였습니다. {list.Count}건의 데이터가 등록되었습니다.");
            } else {
                throw new ServiceException("등록할 데이터가 없습니다.");
            }
        }

        /// <summary>
        /// 등록
        /// </summary>
        [HttpPut]
        public IActionResult Create([FromBody] CmFacility data) {
            // if (!User.IsInRole("Admin"))
            //     return Forbid();

            if (ModelState.IsValid) {
                _service.Save(data);
            } else {
                return BadRequest(ModelState);
            }
            return Ok();
        }

        /// <summary>
        /// 삭제
        /// </summary>
        /// <param name="siteId"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        [HttpDelete("{siteId}/{id}")]
        public IActionResult Delete(int id, int siteId) {
            _service.Delete(id, siteId);

            return Ok();
        }

        // 검색 & 정렬 공통
        private IQueryable<CmFacility> _FilterAndSort(PagingRequest req) {
            var query = _service.GetAll();

            // 기본 Entity 검색
            query = query.Filter(req.conditions);

            int[] virtualFacility = new int[] { 99, 100, 101, 102, 103 };
            query = query.Where(x => !virtualFacility.Contains(x.FacilityTypeId));


            if (req.siteId != null) {
                query = query.Where(x => x.SiteId == Util.ToInt(req.siteId));
            }
            if (req.siteIds != null && req.siteIds.Length > 0) {
                query = query.Where(x => req.siteIds.Contains(x.SiteId));
            }

            // 기본 Entity 정렬
            //query = query.Sort(req.sort);

            // 설비명 Sort 때문에 임시. _ListSelect 에서 Sort 함. 후에 수정해야 함.
            // 고정정렬 (사옥 준공순)
            var sortQuery = query.OrderBy(x => x.CmSite.SortOrderNo);

            // 추가 정렬
            if (req.sort != null) {
                if (req.sort.field == "Custom") { // 기본 커스텀 정렬 예제 (입고역순,승인일순)
                    sortQuery = sortQuery.ThenBy(x => x.FmsFacilityCodeClass.Name);
                    sortQuery = sortQuery.ThenBy(x => x.FmsFacilityCodeClass1.Name);
                    sortQuery = sortQuery.ThenBy(x => x.FmsFacilityCodeClass2.Name);
                    sortQuery = sortQuery.ThenBy(x => x.FacilityUsage);
                    sortQuery = sortQuery.ThenBy(x => x.FacilityCapacity);
                    sortQuery = sortQuery.ThenBy(x => x.Manufacturer);
                    sortQuery = sortQuery.ThenBy(x => x.ManufactureDate);
                } else if (req.sort.field == "Custom2") {
                    sortQuery = sortQuery.ThenBy(x => x.FacilityTypeId);
                } else {
                    sortQuery = sortQuery.ThenSort(req.sort);
                }
            }

            // if (req.sort.field == "StoredAndAproval") { // 기본 커스텀 정렬 예제 (입고역순,승인일순)
            //     query = query.ThenBy(x => x.FmsFacilityCodeClass.Name);
            // .ThenBy(x => x.FmsFacilityCodeClass1.Name)
            // .ThenBy(x => x.FmsFacilityCodeClass2.Name)
            // .ThenBy(x => x.FacilityUsage)
            // .ThenBy(x => x.FacilityCapacity)
            // .ThenBy(x => x.Manufacturer)
            // .ThenBy(x => x.ManufactureDate);
            // } else {
            //     query = query.ThenSort(req.sort);
            // }

            return sortQuery;
        }

    }
}