HYTOS

using ID2.Manager.Data.Models;

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using GemBox.Spreadsheet;

using ID2.Manager.Controller.Controllers;

using System.IO;

using System.IO.Compression;

namespace ID2.Manager.Common.Helpers

{

    public class ID2Excel :IDisposable

    {

        private List<UserInfo> UserlList = new List<UserInfo>();

        public void Dispose()

        {

            try

            {

            }

            catch (Exception)

            {

                throw;

            }

            finally

            {

                GC.Collect(2);

                GC.Collect(2);

            }

        }

        public ID2Excel()

        {

            UserlList = new UserController().GetAllUserInfo().ToList();

        }

        private UserInfo GetUser(string user)

        {

            UserInfo userInfo = UserlList.Where(x => x.ID.Equals(user)).FirstOrDefault();

            if (userInfo != null) return userInfo;

            userInfo = UserlList.Where(x => x.Name.Equals(user)).FirstOrDefault();

            if (userInfo != null) return userInfo;

            return userInfo ?? new UserInfo();

        }

        private string GetColumnName(int column)

        {

            int dividend = column;

            string columnName = string.Empty;

            while (dividend > 0)

            {

                int modulo = (dividend - 1) % 26;

                columnName = Convert.ToChar(65 + modulo) + columnName;

                dividend = (dividend - modulo) / 26;

            }

            return columnName;

        }

        private System.Drawing.Image GetImage(string base64String)

        {

            System.Drawing.Image result = null;

            var str =  CompressHelper.DecompressString(base64String);

            byte[] imageBytes = Convert.FromBase64String(str);

            using (MemoryStream ms = new MemoryStream(imageBytes))

            {

                result = System.Drawing.Image.FromStream(ms);

            }

            return result;

        }

        public ImportResult ExcelDataImport(List<ExcelData> ExcelData)

        {

            ImportResult result = new ImportResult();

            StringBuilder sbErrMsg = new StringBuilder();

            try

            {

                int rowCount = ExcelData.Max(x=>x.ROW_INDEX);

                int columnCount = ExcelData.Max(x => x.COUMMN_INDEX);

                int exRow = 9;

                #region Excel 유효성검사

                //Excel 포멧체크

                if (rowCount < 10 || columnCount != 45)

                {

                    result.Error = "Please, check the excel.";

                    return result;

                }

                #region 엑셀 필수값 체크(도면 : 이름,담당자, 난이도, Typical)

                ExcelData.Where(col => col.ROW_INDEX > exRow)

                        .Where(col => col.COUMMN_INDEX > 5 && col.COUMMN_INDEX < 11 && col.ROW_INDEX > exRow && string.IsNullOrEmpty(col.VALUE))

                       .ToList()

                       .ForEach(p => sbErrMsg.Append(", " + p.TopLeftCell));

                if (sbErrMsg.Length > 0)

                {

                    string errMsg = sbErrMsg.ToString().Substring(2);

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check null value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #region 엑셀 도명명 중복 값 체크

                ExcelData.Where(col => col.COUMMN_INDEX == 7 && col.ROW_INDEX > exRow)

                        .GroupBy(g => g.ROW_INDEX)

                        .Select(p => new

                        {

                            rowIndex = p.Key,

                            docNo = p.Select(x => x.VALUE.ToString()).FirstOrDefault()

                        })

                        .GroupBy(g => g.docNo)

                        .Where(p => p.Count() > 1)

                        .Select(p => p.Select(x => (x.rowIndex + 1).ToString())

                                                                .Aggregate((x, y) => x.ToString() + "," + y.ToString())

                                                                .ToString())

                        .ToList().ForEach(p => sbErrMsg.Append("\n" + p.ToString()));

                if (sbErrMsg.Length > 0)

                {

                    sbErrMsg.Insert(0, "\n중복 된 도면명 Excel row : ");

                    string errMsg = sbErrMsg.ToString();

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check the duplicate value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #endregion

                result.documents = new List<Documents>();

                result.Images = new List<System.Drawing.Image>();

                foreach (var row in ExcelData.Where(row => row.ROW_INDEX > exRow).GroupBy(x => x.ROW_INDEX))

                {

                    var document = new Documents();

                    foreach (var cell in row)

                    {

                        var value = cell.VALUE.DefalutValue();

                        switch (cell.COUMMN_INDEX)

                        {

                            case 6:

                                document.RefProjectCode = value;

                                break;

                            case 7:

                                document.DocumentNo = value;

                                break;

                            case 8:

                                document.PersonInCharge = this.GetUser(value).ID;

                                break;

                            case 9:

                                document.JobLevel = value;

                                break;

                            case 10:

                                document.IsTypical = value;

                                break;

                            case 11:

                                document.RevisonNo = value;

                                break;

                            case 12:

                                document.ToIsDiscussion = value;

                                break;

                            case 13:

                                document.ToRemarks = value;

                                break;

                            case 14:

                                document.ToCreator = this.GetUser(value).ID;

                                break;

                            case 15:

                                result.Images.Add(GetImage(value));

                                break;

                            case 17:

                                document.FrReviewStatus = value;

                                break;

                            case 18:

                                document.FrRemarks = value;

                                break;

                            case 19:

                                document.FrCreator = this.GetUser(value).ID;

                                break;

                            case 22:

                                document.IsID2Work = value;

                                break;

                            case 24:

                                document.ID2StartDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 25:

                                document.ID2EndDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 27:

                                document.ID2Status = value;

                                break;

                            case 28:

                                document.ID2Issues = value;

                                break;

                            case 30:

                                document.AVEVAConvertDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 31:

                                document.AVEVAReviewDate = string.IsNullOrEmpty(value) ? (DateTime?)null : Convert.ToDateTime(value);

                                break;

                            case 32:

                                document.AVEVAStatus = value;

                                break;

                            case 33:

                                document.AVEVAIssues = value;

                                break;

                            case 36:

                                document.ProdReviewer = this.GetUser(value).ID;

                                break;

                            case 37:

                                document.ProdIsResult = value;

                                break;

                            case 38:

                                document.ProdRemarks = value;

                                break;

                            case 39:

                                document.ClientReviewer = this.GetUser(value).ID;

                                break;

                            case 40:

                                document.ClientIsResult = value;

                                break;

                            case 41:

                                document.ClientRemarks = value;

                                break;

                            case 42:

                                document.DTIsGateWay = value;

                                break;

                            case 43:

                                document.DTIsImport = value;

                                break;

                            case 44:

                                document.DTIsRegSystem = value;

                                break;

                            case 45:

                                document.DTRemarks = value;

                                break;

                        }

                    }

                    result.documents.Add(document);

                }

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

        public ImportResult GemboxImport(string fileName)

        {

            ImportResult result = new ImportResult();

            StringBuilder sbErrMsg = new StringBuilder();

            try

            {

                var exFile = ExcelFile.Load(fileName);

                var ws = exFile.Worksheets[0];

                int rowCount = ws.Rows.Count;

                int columnCount = ws.CalculateMaxUsedColumns();

                int exRow = 8;

                #region Excel 유효성검사

                //Excel 포멧체크

                if (rowCount < 10 || columnCount != 45)

                {

                    result.Error = "Please, check the excel.";

                    return result;

                }

                #region 엑셀 필수값 체크(도면 : 이름,담당자, 난이도, Typical)

                ws.Rows.SelectMany(row => row.AllocatedCells)

                       .Where(col => col.Column.Index > 5 && col.Column.Index < 10 && col.Row.Index > exRow && col.Value == null)

                       .ToList()

                       .ForEach(p => sbErrMsg.Append(", " + p.Column.Name + p.Row.Name));

                if (sbErrMsg.Length > 0)

                {

                    string errMsg = sbErrMsg.ToString().Substring(2);

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check null value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #region 엑셀 도명명 중복 값 체크

                ws.Rows.SelectMany(row => row.AllocatedCells)

                                             .Where(col => col.Column.Index == 6 && col.Row.Index > exRow)

                                             .GroupBy(g => g.Row.Index)

                                             .Select(p => new

                                             {

                                                 rowIndex = p.Key,

                                                 docNo = p.Select(x => x.Value.ToString()).FirstOrDefault()

                                             })

                                             .GroupBy(g => g.docNo)

                                             .Where(p => p.Count() > 1)

                                             .Select(p => p.Select(x => (x.rowIndex + 1).ToString())

                                                                                        .Aggregate((x, y) => x.ToString() + "," + y.ToString())

                                                                                        .ToString())

                                             .ToList().ForEach(p => sbErrMsg.Append("\n" + p.ToString()));

                if (sbErrMsg.Length > 0)

                {

                    sbErrMsg.Insert(0, "\n중복 된 도면명 Excel row : ");

                    string errMsg = sbErrMsg.ToString();

                    if (errMsg.Length > 100)

                    {

                        errMsg = $"{errMsg.Substring(0, 100)}...";

                    }

                    result.Error = $"Please, check the duplicate value in excel.\n{errMsg}";

                    return result;

                }

                #endregion

                #endregion

                result.documents = new List<Documents>();

                ws.Rows.Where(row => row.Index > exRow)

                       .ToList()

                       .ForEach(p =>

                       {

                           try

                           {

                               result.documents.Add(new Documents()

                               {

                                   //UID = string.Empty,

                                   //Type = this.radTextBoxInsulationType.Text,

                                   //TempFrom = ws.Rows[exRow].Cells[p.Column.Index].IsNullOrEmpty() ? 0 : Convert.ToSingle(ws.Rows[exRow].Cells[p.Column.Index].Value),

                                   //TempTo = ws.Rows[exRow + 2].Cells[p.Column.Index].IsNullOrEmpty() ? 0 : Convert.ToSingle(ws.Rows[exRow + 2].Cells[p.Column.Index].Value),

                                   //NPS = ws.Rows[p.Row.Index].Cells[0].IsNullOrEmpty() ? 0 : Convert.ToSingle(ws.Rows[p.Row.Index].Cells[0].Value),

                                   //Thickness = p.IsNullOrEmpty() ? 0 : Convert.ToSingle(p.Value)

                                   RefProjectCode = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   DocumentNo = ws.Rows[p.Index].Cells[6].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[6].Value.ToString(),

                                   PersonInCharge = ws.Rows[p.Index].Cells[7].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[7].Value.ToString()).ID,

                                   JobLevel = ws.Rows[p.Index].Cells[8].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[8].Value.ToString(),

                                   IsTypical = ws.Rows[p.Index].Cells[9].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[9].Value.ToString(),

                                   RevisonNo = ws.Rows[p.Index].Cells[10].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[10].Value.ToString(),

                                   ToIsDiscussion = ws.Rows[p.Index].Cells[11].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[11].Value.ToString(),

                                   ToRemarks = ws.Rows[p.Index].Cells[12].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[12].Value.ToString(),

                                   ToCreator = ws.Rows[p.Index].Cells[13].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[13].Value.ToString()).ID,

                                   //ToCapture = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   //ToIsMarkup = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   FrReviewStatus = ws.Rows[p.Index].Cells[16].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[16].Value.ToString(),

                                   FrRemarks = ws.Rows[p.Index].Cells[17].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[17].Value.ToString(),

                                   FrCreator = ws.Rows[p.Index].Cells[18].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[18].Value.ToString()).ID,

                                   //FrCapture = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   //FrIsMarkup = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   IsID2Work = ws.Rows[p.Index].Cells[21].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[21].Value.ToString(),

                                   //ID2Connection = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   ID2StartDate = ws.Rows[p.Index].Cells[23].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[23].Value?.ToString()),

                                   ID2EndDate = ws.Rows[p.Index].Cells[24].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[24].Value?.ToString()),

                                   //ID2JobTime = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   ID2Status = ws.Rows[p.Index].Cells[26].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[26].Value.ToString(),

                                   ID2Issues = ws.Rows[p.Index].Cells[27].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[27].Value.ToString(),

                                   //AVEVAConnection = ws.Rows[p.Index].Cells[5].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[5].Value.ToString(),

                                   AVEVAConvertDate = ws.Rows[p.Index].Cells[29].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[29].Value.ToString()),

                                   AVEVAReviewDate = ws.Rows[p.Index].Cells[30].IsNullOrEmpty() ? (DateTime?)null : Convert.ToDateTime(ws.Rows[p.Index].Cells[30].Value.ToString()),

                                   AVEVAStatus = ws.Rows[p.Index].Cells[31].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[31].Value.ToString(),

                                   AVEVAIssues = ws.Rows[p.Index].Cells[32].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[32].Value.ToString(),

                                   ProdReviewer = ws.Rows[p.Index].Cells[35].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[35].Value.ToString()).ID,

                                   ProdIsResult = ws.Rows[p.Index].Cells[36].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[36].Value.ToString(),

                                   ProdRemarks = ws.Rows[p.Index].Cells[37].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[37].Value.ToString(),

                                   ClientReviewer = ws.Rows[p.Index].Cells[38].IsNullOrEmpty() ? string.Empty : this.GetUser(ws.Rows[p.Index].Cells[38].Value.ToString()).ID,

                                   ClientIsResult = ws.Rows[p.Index].Cells[39].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[39].Value.ToString(),

                                   ClientRemarks = ws.Rows[p.Index].Cells[40].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[40].Value.ToString(),

                                   DTIsGateWay = ws.Rows[p.Index].Cells[41].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[41].Value.ToString(),

                                   DTIsImport = ws.Rows[p.Index].Cells[42].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[42].Value.ToString(),

                                   DTIsRegSystem = ws.Rows[p.Index].Cells[43].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[43].Value.ToString(),

                                   DTRemarks = ws.Rows[p.Index].Cells[44].IsNullOrEmpty() ? string.Empty : ws.Rows[p.Index].Cells[44].Value.ToString()

                               });

                            }

                           catch (Exception ex)

                           {

                               throw new Exception($"Excel Import Row :{p.Index} Error.",ex);

                           }

                       });

            }

            catch (Exception)

            {

                throw;

            }

            return result;

        }

    }

}