using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using OfficeOpenXml;
using Siger.Middlelayer.Utility.ExcelImport;

namespace Siger.Middlelayer.Utility.Helpers
{
    public class EpPlusForMachineHelper: IDisposable
    {
        private ExcelWorksheet sheet { get; set; }
        public EpPlusForMachineHelper()
        {

        }

        public void GenerateExcel(List<string> columns, List<string> levels,string filePath)
        {
            var file = new FileInfo(filePath);
            var package = new ExcelPackage(file);
            var worksheet = package.Workbook.Worksheets.Add("sheet1");
            try
            {
                for (var i = 0; i < columns.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columns[i];
                    worksheet.Column(i + 1).Width = 18;
                }

                worksheet.Cells.Style.ShrinkToFit = true;
                //插入产线层级
                for (int i = 0; i < levels.Count; i++)
                {
                    var level = levels[i].Split(',');
                    for (var j = 0; j < level.Length; j++)
                    {
                        worksheet.Cells[i + 2, j + 1].Value = level[j];
                    }
                }

                package.Save();
            }
            finally
            {
                package.Dispose();
            }
        }

        public List<string> CheckExcel(string filePath, int levelCount)
        {
            if (!File.Exists(filePath))
            {
                throw new Exception("excel file not found.");
            }
            var file = new FileInfo(filePath);
            var excelPackage = new ExcelPackage(file);
            if (excelPackage.Workbook == null || excelPackage.Workbook.Worksheets.Count <=0)
            {
                throw new Exception("read excel failed.");
            }
            var messages = new List<string>();

            sheet = excelPackage.Workbook.Worksheets.First();
            var columnCount = sheet.Dimension.End.Column;

            var endRow = sheet.Dimension.Rows;
            for (var row = 2; row <= sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columnCount; i++)
                {
                    var val = sheet.Cells[row, i];
                    if (val != null)
                    {
                        columnValues.Add(val.Text);
                    }
                }

                if (!columnValues.All(string.IsNullOrWhiteSpace))
                {
                    continue;
                }
                endRow = row - 1;
                break;
            }

            for (var i = 2; i <= endRow; i++)
            {
                for (var j = 1; j <= columnCount; j++)
                {
                    if (j <= levelCount)
                    {
                        var value = sheet.Cells[i, j].Value;
                        if (string.IsNullOrEmpty(value?.ToString()))
                        {
                            messages.Add($"{i},{(int)ImportEnum.MachineLevelIsEmpty}");
                        }
                    }
                    else
                    {
                        if (j == levelCount + 1) //ID
                        {
                            var value = sheet.Cells[i, j].Value;
                            if (string.IsNullOrEmpty(value?.ToString()))
                            {
                                messages.Add($"{i},{(int)ImportEnum.IdIsEmpty}");
                            }
                            else
                            {
                                var success = int.TryParse(value.ToString(), out int mid);
                                if (!success)
                                {
                                    messages.Add($"{i},{(int)ImportEnum.IdIsInVald}");
                                }
                            }
                        }

                        if (j == levelCount + 2) //父级类型
                        {
                            var value = sheet.Cells[i, j].Value;
                            if (string.IsNullOrEmpty(value?.ToString()))
                            {
                                messages.Add($"{i},{(int) ImportEnum.MachineParentTypeIsEmpty}");
                            }
                        }

                        if (j == levelCount + 4) //设备名称
                        {
                            var value = sheet.Cells[i, j].Value;
                            if (string.IsNullOrEmpty(value?.ToString()))
                            {
                                messages.Add($"{i},{(int) ImportEnum.MachineNameIsEmpty}");
                            }
                        }

                        if (j == levelCount + 5) //固定资产编号
                        {
                            var value = sheet.Cells[i, j].Value;
                            if (string.IsNullOrEmpty(value?.ToString()))
                            {
                                messages.Add($"{i},{(int) ImportEnum.MachineCodeIsEmpty}");
                            }
                        }
                    }
                }
            }

            return messages;
        }

        public IEnumerable<MachineEntityList> ConvertSheetToListSkf(int levelCount)
        {
            var list = new List<MachineEntityList>();
            if (sheet == null)
            {
                throw new Exception("read excel failed.");
            }
            var columnCount = sheet.Dimension.End.Column;

            var endRow = 2;
            for (var row = 2; row <= sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columnCount; i++)
                {
                    var val = sheet.Cells[row, i];
                    if (val != null)
                    {
                        columnValues.Add(val.Text);
                    }
                }

                if (columnValues.All(string.IsNullOrWhiteSpace))
                {
                    endRow = row - 1;
                    break;
                }

                endRow = row;
            }

            for (var i = 2; i <= endRow; i++)
            {
                var entity = new MachineEntityList();
                for (var j = 1; j <= columnCount; j++)
                {
                    var value = sheet.Cells[i, j].Value?.ToString();
                    if (j <= levelCount)
                    {
                        entity.Levels.Add(value);
                    }
                    else
                    {
                        if (j == levelCount + 1) //ID
                        {
                            entity.ID = value ?? "";
                        }
                        if (j == levelCount + 2) //父级类型
                        {
                            entity.ParentType = value ?? "";
                        }
                        if (j == levelCount + 3) //子级类型
                        {
                            entity.ChildType = value ?? "";
                        }
                        if (j == levelCount + 4) //设备名称
                        {
                            entity.Name = value ?? "";
                        }
                        if (j == levelCount + 5) //固定资产编号
                        {
                            entity.Code = value ?? "";
                        }
                        if (j == levelCount + 6) //厂商
                        {
                            entity.Factory = value ?? "";
                        }
                        if (j == levelCount + 7) //型号
                        {
                            entity.Model = value ?? "";
                        }
                        if (j == levelCount + 8) //入固日期
                        {
                            entity.MadeDate = value ?? "";
                        }
                        if (j == levelCount + 9) //出厂编号
                        {
                            entity.FactoryCode = value ?? "";
                        }
                        if (j == levelCount + 10) //MachineName
                        {
                            entity.machine_name = value ?? "";
                        }
                        if (j == levelCount + 11) //设备简称
                        {
                            entity.machine_shortname = value ?? "";
                        }
                        if (j == levelCount + 12) //负责人
                        {
                            entity.responsor = value ?? "";
                        }
                        if (j == levelCount + 13) //测试应用产品
                        {
                            entity.typical_application = value ?? "";
                        }
                        if (j == levelCount + 14) //尺寸-内径
                        {
                            entity.inside_diameter = value ?? "";
                        }
                        if (j == levelCount + 15) //尺寸-外径
                        {
                            entity.outside_diameter = value ?? "";
                        }
                        if (j == levelCount + 16) //径向载荷
                        {
                            entity.radial_load = value ?? "";
                        }
                        if (j == levelCount + 17) //轴向载荷
                        {
                            entity.axial_load = value ?? "";
                        }
                        if (j == levelCount + 18) //压力
                        {
                            entity.pressure = value ?? "";
                        }
                        if (j == levelCount + 19) //转速-数字
                        {
                            entity.speed = value ?? "";
                        }
                        if (j == levelCount + 20) //温度
                        {
                            entity.temperature = value ?? "";
                        }
                        if (j == levelCount + 21) //转速-文本
                        {
                            entity.rotation = value ?? "";
                        }
                        if (j == levelCount + 22) //样本润滑
                        {
                            entity.sample_lubrication = value ?? "";
                        }
                        if (j == levelCount + 23) //设备用油-润滑
                        {
                            entity.oil_lubrication = value ?? "";
                        }
                        if (j == levelCount + 24) //设备用油-液压
                        {
                            entity.oil_hydraulic = value ?? "";
                        }
                        if (j == levelCount + 25) //信号采集
                        {
                            entity.signal_monitor = value ?? "";
                        }
                        if (j == levelCount + 26) //测试工位
                        {
                            entity.position = value ?? "";
                        }
                        if (j == levelCount + 27) //设备尺寸
                        {
                            entity.machine_size = value ?? "";
                        }
                    }
                }
                list.Add(entity);
            }

            return list;
        }

        public IEnumerable<MachineEntity> ConvertSheetToList(int levelCount)
        {
            var list = new List<MachineEntity>();
            if (sheet == null)
            {
                throw new Exception("read excel failed.");
            }
            var columnCount = sheet.Dimension.End.Column;

            var endRow = 2;
            for (var row = 2; row <= sheet.Dimension.Rows; row++)
            {
                var columnValues = new List<string>();
                for (var i = 1; i <= columnCount; i++)
                {
                    var val = sheet.Cells[row, i];
                    if (val != null)
                    {
                        columnValues.Add(val.Text);
                    }
                }

                if (columnValues.All(string.IsNullOrWhiteSpace))
                {
                    endRow = row - 1;
                    break;
                }

                endRow = row;
            }

            for (var i = 2; i <= endRow; i++)
            {
                var entity = new MachineEntity();
                for (var j = 1; j <= columnCount; j++)
                {
                    var value = sheet.Cells[i, j].Value?.ToString();
                    if (j <= levelCount)
                    {
                        entity.Levels.Add(value);
                    }
                    else
                    {
                        if (j == levelCount + 1) //ID
                        {
                            entity.ID = value ?? "";
                        }
                        if (j == levelCount + 2) //父级类型
                        {
                            entity.ParentType = value ?? "";
                        }
                        if (j == levelCount + 3) //子级类型
                        {
                            entity.ChildType = value ?? "";
                        }
                        if (j == levelCount + 4) //设备名称
                        {
                            entity.Name = value ?? "";
                        }
                        if (j == levelCount + 5) //固定资产编号
                        {
                            entity.Code = value ?? "";
                        }
                        if (j == levelCount + 6) //厂商
                        {
                            entity.Factory = value ?? "";
                        }
                        if (j == levelCount + 7) //型号
                        {
                            entity.Model = value ?? "";
                        }
                        if (j == levelCount + 8) //入固日期
                        {
                            entity.MadeDate = value ?? "";
                        }
                        if (j == levelCount + 9) //出厂编号
                        {
                            entity.FactoryCode = value ?? "";
                        }
                    }
                }
                list.Add(entity);
            }

            return list;
        }

        public void Dispose()
        {
            sheet?.Dispose();
        }
    }
}
