namespace Yw.WinFrmUI
{
    /// <summary>
    /// 
    /// </summary>
    public class PhartExcelHelper
    {
        #region Export

        public static void ExportUniversalTemplate(string file_path, Yw.Ahart.eCurveType curve_type)
        {
            var title = PhartAxisTitleHelper.Get(curve_type);
            string col_x_title = title.AxisXTile;
            string col_y_title = title.AxisYTitle;

            NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            var theSheet1 = theBook.CreateSheet("Sheet1");

            NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
            rowTile.CreateCell(0).SetCellValue(col_x_title);
            rowTile.CreateCell(1).SetCellValue(col_y_title);


            //强制Excel在打开时重新计算所有公式
            theSheet1.ForceFormulaRecalculation = true;
            using FileStream fs = File.OpenWrite(file_path);
            theBook.Write(fs);

        }

        public static void ExportPumpTemplate(string file_path)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();
            var theSheet1 = theBook.CreateSheet("Sheet1");

            NPOI.SS.UserModel.IRow rowTile = theSheet1.CreateRow(0);
            rowTile.CreateCell(0).SetCellValue("流量");
            rowTile.CreateCell(1).SetCellValue("扬程");
            rowTile.CreateCell(2).SetCellValue("效率");
            rowTile.CreateCell(3).SetCellValue("功率");


            theSheet1.ForceFormulaRecalculation = true;
            using FileStream fs = File.OpenWrite(file_path);
            theBook.Write(fs);

        }

        public static void ExportValveTemplate(string file_path)
        {
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = new NPOI.HSSF.UserModel.HSSFWorkbook();

            var theSheet1 = theBook.CreateSheet("流量损失");
            NPOI.SS.UserModel.IRow rowTile1 = theSheet1.CreateRow(0);
            rowTile1.CreateCell(0).SetCellValue("开度 %");
            rowTile1.CreateCell(1).SetCellValue("流量 m³/h");
            rowTile1.CreateCell(2).SetCellValue("水头损失 m");

            var theSheet2 = theBook.CreateSheet("开度损失");
            NPOI.SS.UserModel.IRow rowTile2 = theSheet2.CreateRow(0);
            rowTile2.CreateCell(0).SetCellValue("开度 %");
            rowTile2.CreateCell(1).SetCellValue("损失系数 k");


            theSheet1.ForceFormulaRecalculation = true;
            theSheet2.ForceFormulaRecalculation = true;

            using FileStream fs = File.OpenWrite(file_path);
            theBook.Write(fs);
        }


        #endregion

        #region Parse

        public static string ParseUniversalExcel(string file_path, out List<Yw.Geometry.Point2d> list)
        {
            list = new List<Geometry.Point2d>(); ;
            if (!File.Exists(file_path))
                return "文件不存在";

            //初始化文件
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
            using (FileStream file = new FileStream(file_path, FileMode.Open, FileAccess.Read))
            {
                theBook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
            }

            //检查表格是否符合
            NPOI.SS.UserModel.ISheet sheet1 = theBook.GetSheet("Sheet1");
            if (sheet1 == null)
            {
                sheet1 = theBook.GetSheetAt(0);
                if (sheet1 == null)
                    return ("无Sheet数据");
            }

            //标题行
            int title_line_index = 0;
            //x 列
            int col_index_x = 1;
            //y 列
            int col_index_y = 2;

            var row_title = sheet1.GetRow(title_line_index);
            if (row_title == null)
            {
                return ("第一行第一列不能空");
            }

            //开始读取的行
            int start_line = title_line_index + 1;
            var cell_0 = row_title.GetCell(0);
            if (cell_0 == null)
            {
                return ("无法读取表头文件");
            }
            else if (cell_0.StringCellValue.Contains("序号"))
            {
                col_index_x = 1;
                col_index_y = 2;
            }
            else if (cell_0.StringCellValue.Contains("流量"))
            {
                col_index_x = 0;
                col_index_y = 1;
            }

            NPOI.SS.UserModel.IRow row_temp;
            NPOI.SS.UserModel.ICell cell;

            int line = 0;
            for (line = start_line; line < 1000; line++)
            {
                row_temp = sheet1.GetRow(line);
                if (row_temp == null)
                    break;

                cell = row_temp.GetCell(col_index_x);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double x))
                    break;

                cell = row_temp.GetCell(col_index_y);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double y))
                    break;

                list.Add(new(x, y));
            }

            return "";
        }

        public static string ParsePumpExcel(string fileName, out List<Yw.Geometry.Point2d> qh, out List<Yw.Geometry.Point2d> qe, out List<Yw.Geometry.Point2d> qp)
        {
            qh = new List<Geometry.Point2d>();
            qe = new List<Geometry.Point2d>();
            qp = new List<Geometry.Point2d>();

            if (!File.Exists(fileName))
                return "文件不存在";
            int line = 0;

            //初始化文件
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
            using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                theBook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);
            }

            //检查表格是否符合
            NPOI.SS.UserModel.ISheet sheet1 = theBook.GetSheet("Sheet1");
            if (sheet1 == null)
            {
                sheet1 = theBook.GetSheetAt(0);
                if (sheet1 == null)
                    return ("无Sheet数据");
            }

            //标题行
            int title_line_index = 0;
            //流量列
            int col_index_q = 1;
            //扬程列
            int col_index_h = 2;
            //效率列
            int col_index_e = 3;
            //功率列
            int col_index_p = 4;

            var row_title = sheet1.GetRow(title_line_index);
            if (row_title == null)
            {
                return ("第一行第一列不能空");
            }

            //开始读取的行
            int start_line = title_line_index + 1;
            var cell_0 = row_title.GetCell(0);
            if (cell_0 == null)
            {
                return ("无法读取表头文件");
            }
            else if (cell_0.StringCellValue.Contains("序号"))
            {
                col_index_q = 1;
                col_index_h = 2;
                col_index_e = 3;
                col_index_p = 4;
            }
            else if (cell_0.StringCellValue.Contains("流量"))
            {
                col_index_q = 0;
                col_index_h = 1;
                col_index_e = 2;
                col_index_p = 3;
            }


            NPOI.SS.UserModel.IRow row_temp = null;
            NPOI.SS.UserModel.ICell cell;

            var list = new List<(double Q, double H, double P, double E)>();
            for (line = start_line; line < 1000; line++)
            {

                row_temp = sheet1.GetRow(line);
                if (row_temp == null)
                    break;

                cell = row_temp.GetCell(col_index_q);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double flow))
                    break;
                if (flow < 0)
                    break;

                cell = row_temp.GetCell(col_index_h);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double head))
                    break;
                if (head < 0)
                    break;

                cell = row_temp.GetCell(col_index_e);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double eff))
                {
                    eff = -1;
                }


                cell = row_temp.GetCell(col_index_p);
                if (cell == null)
                    break;
                if (!ParseCellValue(cell, out double power))
                {
                    power = -1;
                }

                if (eff > 0)
                {
                    power = Yw.Pump.CalculationHelper.CalcuP(flow, head, eff);
                }
                else
                {
                    eff = Yw.Pump.CalculationHelper.CalcuE(flow, head, power);
                }

                if (eff > 99)
                {
                    throw new Exception("效率大于100%");
                }

                qh.Add(new Geometry.Point2d(flow, head));
                qe.Add(new Geometry.Point2d(flow, eff));
                qp.Add(new Geometry.Point2d(flow, power));
            }

            return "";
        }

        public static string ParseValveExcel(string fileName, out List<(Yw.Ahart.eCurveType CurveType, List<Yw.Geometry.Point2d> DefPointList, int Opening)> list)
        {
            list = new List<(Ahart.eCurveType CurveType, List<Geometry.Point2d> DefPointList, int Opening)>();
            if (!File.Exists(fileName))
                return "文件不存在";
            int line = 0;

            //初始化文件
            NPOI.HSSF.UserModel.HSSFWorkbook theBook = null;
            using (FileStream file = new(fileName, FileMode.Open, FileAccess.Read))
                theBook = new NPOI.HSSF.UserModel.HSSFWorkbook(file);


            //检查表格是否符合
            NPOI.SS.UserModel.ISheet sheet_ql = theBook.GetSheet("流量损失");
            if (sheet_ql != null)
            {
                //标题行
                int title_line_index = 0;
                //开度列
                int col_index_opening = 0;
                //流量列
                int col_index_flow = 1;
                //水头损失列
                int col_index_head_loss = 2;

                var row_title = sheet_ql.GetRow(title_line_index);
                if (row_title == null)
                {
                    return ("第一行第一列不能空");
                }

                //开始读取的行
                int start_line = title_line_index + 1;
                var cell_0 = row_title.GetCell(0);
                if (cell_0 == null)
                {
                    return ("无法读取表头文件");
                }
            


                NPOI.SS.UserModel.IRow row_temp = null;
                NPOI.SS.UserModel.ICell cell;

                var cell_value_list = new List<(double Opening, double Flow, double HeadLoss)>();
                for (line = start_line; line < 1000; line++)
                {
                    row_temp = sheet_ql.GetRow(line);
                    if (row_temp == null)
                        break;

                    cell = row_temp.GetCell(col_index_opening);
                    if (cell == null)
                        break;
                    if (!ParseCellValue(cell, out double opening))
                        break;
                    if (opening < 0 || opening > 100)
                        break;

                    cell = row_temp.GetCell(col_index_flow);
                    if (cell == null)
                        break;
                    if (!ParseCellValue(cell, out double flow))
                        break;
                    if (flow < 0)
                        break;

                    cell = row_temp.GetCell(col_index_head_loss);
                    if (cell == null)
                        break;
                    if (!ParseCellValue(cell, out double head_loss))
                        break;
                    if (head_loss < 0)
                        break;

                    cell_value_list.Add((opening, flow, head_loss));
                }

                var opening_group = cell_value_list.GroupBy(x => x.Opening);
                foreach (var item in opening_group)
                {
                    var pt_list = item.Select(x => new Yw.Geometry.Point2d(x.Flow, x.HeadLoss));
                    list.Add((Yw.Ahart.eCurveType.QL, pt_list.ToList(), (int)item.Key));
                }
            }


            //检查表格是否符合
            NPOI.SS.UserModel.ISheet sheet_ol = theBook.GetSheet("开度损失");
            if (sheet_ol != null)
            {
                //标题行
                int title_line_index = 0;
                //开度列
                int col_index_opening = 0;
                //损失系数列
                int col_index_k = 1;

                var row_title = sheet_ol.GetRow(title_line_index);
                if (row_title == null)
                {
                    return ("第一行第一列不能空");
                }

                //开始读取的行
                int start_line = title_line_index + 1;
                var cell_0 = row_title.GetCell(0);
                if (cell_0 == null)
                {
                    return ("无法读取表头文件");
                }
              


                NPOI.SS.UserModel.IRow row_temp = null;
                NPOI.SS.UserModel.ICell cell;

                var pt_list = new List<Yw.Geometry.Point2d>();
                for (line = start_line; line < 1000; line++)
                {
                    row_temp = sheet_ol.GetRow(line);
                    if (row_temp == null)
                        break;

                    cell = row_temp.GetCell(col_index_opening);
                    if (cell == null)
                        break;
                    if (!ParseCellValue(cell, out double opening))
                        break;
                    if (opening < 0 || opening > 100)
                        break;

                    cell = row_temp.GetCell(col_index_k);
                    if (cell == null)
                        break;
                    if (!ParseCellValue(cell, out double k))
                        break;
                    if (k < 0)
                        break;

                    pt_list.Add(new Geometry.Point2d(opening, k));
                }

                list.Add((Yw.Ahart.eCurveType.OL, pt_list.ToList(), 100));
            }

            if (list == null || !list.Any())
            {
                return ("表格不符合格式");
            }

            return "";
        }

        private static bool ParseCellValue(NPOI.SS.UserModel.ICell cell, out double cell_value)
        {
            cell_value = 0;
            if (cell.CellType == NPOI.SS.UserModel.CellType.Numeric)
            {
                cell_value = cell.NumericCellValue;
                return true;
            }

            if (cell.CellType == NPOI.SS.UserModel.CellType.String)
            {
                if (double.TryParse(cell.StringCellValue, out double value))
                {
                    cell_value = value;
                    return true;
                }
            }

            return false;
        }

        #endregion
    }
}