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

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

        }

        public List<string> CheckExcel(string filePath)
        {
            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++)
                {
                    var value = sheet.Cells[i, j].Value;
                    if (j == 1)
                    {
                        if (string.IsNullOrEmpty(value?.ToString()))
                        {
                            messages.Add($"{i},{(int)ImportEnum.UserCodeIsEmpty}");
                        }
                    }
                    if (j == 5)
                    {
                        if (string.IsNullOrEmpty(value?.ToString()))
                        {
                            messages.Add($"{i},{(int)ImportEnum.SectionIsEmpty}");
                        }
                    }
                    if (j == 9)
                    {
                        if (string.IsNullOrEmpty(value?.ToString()))
                        {
                            messages.Add($"{i},{(int)ImportEnum.WorkgroupIsEmpty}");
                        }
                    }

                    if (j >= 12)
                    {
                        if (string.IsNullOrEmpty(value?.ToString()))
                        {
                            messages.Add($"{i},{(int)ImportEnum.ShiftIsEmpty}");
                        }
                    }
                }
            }

            return messages;
        }

        public IEnumerable<WorkingGroupList> ConvertSheetToList()
        {
            var list = new List<WorkingGroupList>();
            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 WorkingGroupList();
                for (var j = 1; j <= columnCount; j++)
                {
                    var value = sheet.Cells[i, j].Value?.ToString();
                    if (j == 1) //工号
                    {
                        entity.Code = value;
                    }
                    if (j == 5) //部门
                    {
                        entity.SectionName = value;
                    }
                    if (j == 9) //班组
                    {
                        entity.GroupName = value;
                    }
                    if (j == 12) //开始日期
                    {
                        entity.StartDate = sheet.Cells[1, j].Value?.ToString();
                    }
                    if (j >= 12) //班次
                    {
                        entity.DateList.Add(value);
                    }
                }
                list.Add(entity);
            }

            return list;
        }

        public void GenerateExcel(List<SectionWokingGroupList> rows, IList<string> columnNames, 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 < columnNames.Count; i++)
                {
                    worksheet.Cells[1, i + 1].Value = columnNames[i];
                    worksheet.Column(i + 1).Width = 18;
                }

                worksheet.Cells.Style.ShrinkToFit = true;
                for (var i = 0; i < rows.Count; i++)
                {
                    worksheet.Cells[i + 2, 1].Value = rows[i].GroupName;
                    worksheet.Cells[i + 2, 2].Value = rows[i].UserName;
                    
                    for (var k = 0; k < rows[i].Dates.Count; k++)
                    {
                        worksheet.Cells[i + 2, k + 3].Value = rows[i].Dates[k];
                    }
                }

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

        public void Dispose()
        {
        }
    }
}
