using System;
using System.Collections.Generic;
using System.Collections;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using System.Data;
using System.Windows.Forms;
using WMS.Attirubte;
using System.ComponentModel;
 
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Record;

namespace WMS.Common
{
    public class NPOIHelper<T> where T : class, new()
    {
        /// <summary>
        /// 根据Excel列类型获取列的值
        /// </summary>
        /// <param name="cell">Excel列</param>
        /// <returns></returns>
        private static string GetCellValue(ICell cell)
        {
            if (cell == null)
                return string.Empty;
            switch (cell.CellType)
            {
                case CellType.Blank:
                    return string.Empty;
                case CellType.Boolean:
                    return cell.BooleanCellValue.ToString();
                case CellType.Error:
                    return cell.ErrorCellValue.ToString();
                case CellType.Numeric:
                case CellType.Unknown:
                default:
                    return cell.ToString();//This is a trick to get the correct value of the cell. NumericCellValue will return a numeric value no matter the cell value is a date or a number
                case CellType.String:
                    return cell.StringCellValue;
                case CellType.Formula:
                    try
                    {
                        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(cell.Sheet.Workbook);
                        e.EvaluateInCell(cell);
                        return cell.ToString();
                    }
                    catch
                    {
                        return cell.NumericCellValue.ToString();
                    }
            }
        }

        ///// <summary>
        ///// 自动设置Excel列宽
        ///// </summary>
        ///// <param name="sheet">Excel表</param>
        //private static void AutoSizeColumns(ISheet sheet)
        //{
        //    if (sheet.PhysicalNumberOfRows > 0)
        //    {
        //        IRow headerRow = sheet.GetRow(0);

        //        for (int i = 0, l = headerRow.LastCellNum; i < l; i++)
        //        {
        //            sheet.AutoSizeColumn(i);
        //        }
        //    }
        //}

        public static List<T> Import(OpenFileDialog fileDialog, Hashtable ht)
        {
            DataTable table = new DataTable();
            Stream excelFileStream;
            try
            {
                excelFileStream = fileDialog.OpenFile();
            }
            catch (IOException e)
            {
                if (e.ToString().Contains("正由另一进程使用"))
                {
                    SystemCommon.ShowErrorMessageBox("所选Excel文件处于打开Status，请关闭后重试");
                }
                else {
                    SystemCommon.ShowErrorMessageBox("文件读取失败");
                }
                return null;
            }

            if (excelFileStream == null)
            {
                excelFileStream.Close();
                SystemCommon.ShowErrorMessageBox("文件读取失败");
                return null;
            }
            //读取excel
            using (excelFileStream)
            {
                IWorkbook workbook = new HSSFWorkbook();

                if (fileDialog.SafeFileName.Substring(fileDialog.SafeFileName.Length - 1, 1) == "s")
                {
                    try
                    {
                        workbook = new HSSFWorkbook(excelFileStream);//xls
                    }
                    catch (LeftoverDataException)
                    {
                        SystemCommon.ShowErrorMessageBox("导入失败，请将所选Excel文件另存为一个新的Excel文件后再次导入");
                        return null;
                    }
                    catch (IOException)
                    {
                        SystemCommon.ShowErrorMessageBox("导入失败，请将所选Excel文件另存为一个新的Excel文件后再次导入");
                        return null;
                    }
                    catch (Exception)
                    {
                        SystemCommon.ShowErrorMessageBox("导入失败，读取Excel文件时遇到未知错误");
                        return null;
                    }
                    
                }
                else
                {
                    workbook = new XSSFWorkbook(excelFileStream);//xlsx
                }

                ISheet sheet = workbook.GetSheetAt(0);//取第一个表
                    
                IRow headerRow = sheet.GetRow(0);//第一行为标题行
                if (headerRow == null) {
                    SystemCommon.ShowErrorMessageBox("导入失败，所选Excel没有数据");
                    return null;
                }
                int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
                int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1

                //handling header.
                for (int i = headerRow.FirstCellNum; i < cellCount; i++)
                {
                 
                    DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
                    table.Columns.Add(column);
                }

                for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++)
                {
                    IRow row = sheet.GetRow(i);
                    DataRow dataRow = table.NewRow();

                    if (row != null)
                    {
                        for (int j = row.FirstCellNum; j < cellCount; j++)
                        {
                            if (row.GetCell(j) != null){
                                dataRow[j] = GetCellValue(row.GetCell(j));
                            }
                        }
                    }

                    table.Rows.Add(dataRow);
                }
            }

            Type classType = typeof(T);
            if (classType == null)
            {
                SystemCommon.ShowErrorMessageBox("导入失败，获取Model类型失败，请联系我们");
                return null;
            }

            foreach (var temp in classType.GetProperties())
            {
                object[] TypeobjType = temp.GetCustomAttributes(typeof(TableClmAttribute), false);
                if (TypeobjType.Length > 0)
                {
                    if ((TypeobjType[0] as TableClmAttribute).NoSelect == false) {
                        continue;
                    }
                }

                string name = temp.Name;
                if (ht.ContainsKey(name))
                {
                    continue;
                }

                string disPlayName = string.Empty;
                object[] disPlay = temp.GetCustomAttributes(typeof(DisplayNameAttribute), false);
                if (disPlay.Length > 0)
                {
                    disPlayName = (disPlay[0] as DisplayNameAttribute).DisplayName;
                    if (string.IsNullOrEmpty(disPlayName)) {
                        continue;
                    }
                }

                ht.Add(name, disPlayName);

            }

            //如果是商品，重量UNIT的g转为克
            if (classType.FullName == "WMS.Model.Base.GoodsInfoModel")
            {
                if (table.Columns.Contains("重量UNIT"))
                {
                    foreach (DataRow tableRow in table.Rows)
                    {
                        if (tableRow["重量UNIT"].ToString() == "g")
                        {
                            tableRow["重量UNIT"] = "克";
                        }
                    }
                }
            } 

            //读取的table转成当前项目中的Table
            List<string> remove = new List<string>();
            foreach (DataColumn item in table.Columns)
            {
                if (!ht.ContainsValue(item.ColumnName)) {
                    remove.Add(item.ColumnName);  
                }
            }
            foreach (string item in remove)
            {
                table.Columns.Remove(item);
            }
            foreach (DictionaryEntry item in ht)
	        {
                string value = item.Value.ToString();

                if (value.Length > 6) {
                    if (value.Substring(value.Length - 6, 6) == "ISNAME")
                    {
                        if (table.Columns.Contains(value.Substring(0, value.Length - 6)))
                        {
                            //增加一列
                            DataColumn column = new DataColumn(item.Key.ToString());
                            table.Columns.Add(column);
                            foreach (DataRow tableRow in table.Rows)
                            {
                            
                                tableRow[item.Key.ToString()] = tableRow[value.Substring(0, value.Length - 6)].ToString();
                            }
                            
                        }
                    }
                }
            }
            foreach (DictionaryEntry item in ht)
            {
                if (table.Columns.Contains(item.Value.ToString()))
                {
                    table.Columns[item.Value.ToString()].ColumnName = item.Key.ToString();
                }
            }
            #region 名称转为编号
            foreach (DictionaryEntry item in ht)
            {
                string member = item.Key.ToString();
                if (classType.GetProperty(member) == null) {
                    continue;
                }

                object[] TypeobjType = classType.GetProperty(member).GetCustomAttributes(typeof(TableClmAttribute), false);
                if (TypeobjType.Length == 0)
                {
                    continue;
                }

                //object[] disPlay = classType.GetProperty(member).GetCustomAttributes(typeof(DisplayNameAttribute), false);
                //if (disPlay.Length == 0)
                //{
                //    return null;
                //}
                //string captionName = (disPlay[0] as DisplayNameAttribute).DisplayName;
                string tableName = (TypeobjType[0] as TableClmAttribute).ClmJoinTable;
                string clmValue = (TypeobjType[0] as TableClmAttribute).JoinTableClm;
                string clmDis = (TypeobjType[0] as TableClmAttribute).JoinTableClmName;
                string clmWhere = (TypeobjType[0] as TableClmAttribute).JoinTableWhere;
                string columnData = (TypeobjType[0] as TableClmAttribute).ColumnData;
                DataTable dt = new DataTable();
                if (string.IsNullOrEmpty(columnData))
                {
                    dt = WMS.Business.IBussFactory<WMS.Business.Common.BussCommon>.Instance().GetCustomCtrlData(tableName, clmValue, clmDis, clmWhere, "");
                }
                else {
                    //columnData.Substring(1,1)

                    DataColumn column = new DataColumn("value");
                    dt.Columns.Add(column); 
                    column = new DataColumn("string");
                    dt.Columns.Add(column);

                    DataRow dataRow = dt.NewRow();
                    dataRow[0] = columnData.Substring(2, 1);
                    dataRow[1] = columnData.Substring(0, 1);
                    dt.Rows.Add(dataRow);

                    dataRow = dt.NewRow();
                    dataRow[0] = columnData.Substring(6, 1);
                    dataRow[1] = columnData.Substring(4, 1);
                    dt.Rows.Add(dataRow);
                }
                foreach (DataRow tableRow in table.Rows)
                { 
                    foreach (DataRow row in dt.Rows)
                    {
                        if (table.Columns.Contains(item.Key.ToString()))
                        {
                            if (row[1].ToString() == tableRow[item.Key.ToString()].ToString())
                            {
                                tableRow[item.Key.ToString()] = string.Empty;
                                tableRow[item.Key.ToString()] = row[0].ToString();
                                break;
                            }
                        }
                        
                    }
                    
                }
            }
            
            #endregion

            if (table.Columns.Count == 0) {
                SystemCommon.ShowErrorMessageBox("导入失败，所选Excel没有符合要求的数据");
                return null;
            }

            return ConvertHelper<T>.ConvertToList(table);
        }

    }
}