using System.Data;
using System.IO;
using Kreta.Core.ConnectionType;
using Kreta.DataAccessManual;
using Kreta.Resources;
using OfficeOpenXml;

namespace Kreta.BusinessLogic.Classes.ExcelHelpers
{
    public class EslRiportExportHelper : LogicBase
    {
        public EslRiportExportHelper(IConnectionType connectionType) : base(connectionType) { }

        public MemoryStream GetLemorzsolodasEslRiportExport(int? feladatEllatasiHelyId, bool isFelevi)
        {
            DataSet dataSet = Dal.CustomConnection.Run(ConnectionType, h =>
            {
                DataSet result = h.AdatszolgaltatasokDAL().GetLemorzsolodasEslRiportExport(IntezmenyId, TanevId, feladatEllatasiHelyId, isFelevi);
                return result;
            });
            var stream = new MemoryStream();
            if (dataSet.Tables.Count > 0)
            {
                DataTable dataTable = dataSet.Tables[0];
                DataRowCollection dataRowCollection = dataTable.Rows;
                for (int columnIndex = 0; columnIndex < dataTable.Columns.Count; columnIndex++)
                {
                    string columnName = dataTable.Columns[columnIndex].ColumnName;

                    if (columnName != "Leírás" && columnName != "Összesen" && columnName != "Lány")
                    {
                        for (int rowIndex = 0; rowIndex < dataTable.Rows.Count; rowIndex++)
                        {
                            dataRowCollection[rowIndex][columnIndex] = dataRowCollection[rowIndex][columnIndex].ToString().Replace("¤", System.Environment.NewLine);
                        }
                    }
                }

                using (var excel = new ExcelPackage(stream))
                {
                    var workSheet = excel.Workbook.Worksheets.Add(AdatszolgaltatasokResource.ReszletesAdatok);
                    workSheet.Cells[1, 1].LoadFromDataTable(dataTable, true);
                    int sectionStartIndex = 2;
                    string sectioName = dataTable.Rows[0].Field<string>("Leírás");
                    int lastColumn = dataTable.Columns.Count;
                    for (int i = 1; i < dataTable.Rows.Count; i++)
                    {
                        if (dataTable.Rows[i].Field<string>("Leírás") != sectioName || i == dataTable.Rows.Count - 1)
                        {
                            workSheet.Cells[sectionStartIndex, 1, i + 1, 1].Merge = true; //Leírás
                            workSheet.Cells[i + 1, 1, i + 1, lastColumn].Style.Border.Bottom.Style = OfficeOpenXml.Style.ExcelBorderStyle.Thin;
                            workSheet.Cells[sectionStartIndex, lastColumn - 1, i + 1, lastColumn - 1].Merge = true; // Összesen db
                            workSheet.Cells[sectionStartIndex, lastColumn, i + 1, lastColumn].Merge = true; // Lány db
                            sectionStartIndex = i + 2;
                            sectioName = dataTable.Rows[i].Field<string>("Leírás");
                        }
                    }

                    workSheet.View.FreezePanes(2, 2);

                    for (int columnIndex = workSheet.Dimension.Start.Column; columnIndex <= workSheet.Dimension.End.Column; columnIndex++)
                    {
                        workSheet.Column(columnIndex).Width = 45;
                        workSheet.Cells[1, columnIndex].Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Center;

                        for (int rowIndex = workSheet.Dimension.Start.Row; rowIndex <= workSheet.Dimension.End.Row; rowIndex++)
                        {
                            if (columnIndex < lastColumn - 1)
                            {
                                workSheet.Cells[rowIndex, columnIndex].Style.VerticalAlignment = OfficeOpenXml.Style.ExcelVerticalAlignment.Center;
                                workSheet.Cells[rowIndex, columnIndex].Style.WrapText = true;
                            }
                        }
                    }
                    excel.Save();
                }
            }
            else
            {
                using (var excel = new ExcelPackage(stream))
                {
                    var workSheet = excel.Workbook.Worksheets.Add(AdatszolgaltatasokResource.ReszletesAdatok);
                    excel.Save();
                }
            }
            stream.Position = 0;
            return stream;
        }
    }
}