using Community.DAL;
using Community.Model.Build;
//using NPOI.CS;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Community.Excel.Common
{
    public class XlsTools
    {
        //public static readonly ExcelHelper _excel = new ExcelHelper();

        /// <summary>
        /// 读取文件至字节数组
        /// </summary>
        /// <param name="fileName">文件名</param>
        /// <returns>字节数组</returns>
        public static byte[] FileToBytes(string fileName)
        {
            byte[] bytes = null;
            FileStream fs = null;

            try
            {
                FileInfo file = new FileInfo(fileName);
                if (file != null)
                {
                    fs = file.OpenRead();
                    bytes = new Byte[fs.Length];
                    fs.Read(bytes, 0, Convert.ToInt32(fs.Length));
                    fs.Flush();
                }
            }
            catch
            {
                bytes = null;
            }
            finally
            {
                if (fs != null)
                {
                    fs.Close();
                }
            }

            return bytes;
        }

        //public static DataTable ReadXls(string xls)
        //{
        //    if (string.IsNullOrEmpty(xls) || !File.Exists(xls))
        //    {
        //        MessageBox.Show("Excel文件不存在，请重新选择！");
        //        return null;
        //    }

        //    byte[] bytes = XlsTools.FileToBytes(xls);
        //    if (null == bytes || bytes.Length == 0)
        //    {
        //        MessageBox.Show("无法读取Excel文件！");
        //        return null;
        //    }

        //    return _excel.ExcelToTable(bytes);
        //}

        public static DataTable ReadXls(string xls, int sheet)
        {
            if (string.IsNullOrEmpty(xls) || !File.Exists(xls))
            {
                MessageBox.Show("Excel文件不存在，请重新选择！");
                return null;
            }

            return ReadExcel.ReadXls(xls, sheet - 1);
        }

        public static List<House> ProcessData(string[] splits, string name, DataTable dt)
        {
            int bid = HouseDAL.GetBuildingId(name);
            if (bid == -1)
            {
                MessageBox.Show("没有读取到楼宇Id！");
                return null;
            }

            House h = null;
            List<House> list = new List<House>();
            for (int i = 0, c = dt.Rows.Count; i < c; i++)
            {
                DataRow dr = dt.Rows[i];

                string title = GetStr(dr, 1);
                if (title.IndexOf("房屋地址") > -1)
                {
                    h = new House(bid);
                    SetHouse(i + 1, h, dr, 2, splits);
                    list.Add(h);
                }

                if (title.IndexOf("房屋状态") > -1)
                {
                    SetStatus(h, dr, 2);
                }

                if (title.IndexOf("房主信息") > -1)
                {
                    SetHousehold(h, dr, 1);
                }
                if (title.IndexOf("房主家庭成员") > -1)
                {
                    SetHousehold(h, dr, 2);
                }
                if (title.IndexOf("承租人") > -1)
                {
                    SetHousehold(h, dr, 3);
                }
            }

            return list;
        }

        public static string GetStr(DataRow dr, int col)
        {
            if (col >= dr.Table.Columns.Count) return string.Empty;

            object obj = dr[col];
            if (obj == null || obj == DBNull.Value)
            {
                return string.Empty;
            }

            return obj.ToString().Trim();
        }

        public static void SetHouse(int i, House h, DataRow dr, int col, string[] splits)
        {
            h.FullName = GetStr(dr, col);

            try
            {
                string[] strs = h.FullName.Split(splits, StringSplitOptions.RemoveEmptyEntries);

                h.BuildNum = strs[0];
                if (strs.Length > 1) h.Unit = strs[1];
                if (strs.Length > 2) h.HouseNum = strs[2];
            }
            catch //(Exception ex)
            {
                MessageBox.Show("行号：" + i + "\r\n" + h.FullName + "：门牌号异常！");
            }
        }

        // 自住\出租\闲置
        public static void SetStatus(House h, DataRow dr, int col)
        {
            string str = GetStr(dr, col);
            if (str.Length == 0)
            {
                h.HouseState = 1;
                return;
            }

            string status = str.Substring(0, 2);
            switch (status)
            {
                case "自住":
                    h.HouseState = 1;
                    break;
                case "出租":
                    h.HouseState = 2;
                    break;
                case "闲置":
                    h.HouseState = 3;
                    break;
            }
            //h.HouseStatus = str.Substring(0, 2);
            h.Remark = str.Replace(status, "").Replace("（", "").Replace("）", "").Trim();
        }

        public static void SetHousehold(House h, DataRow dr, int houseType)
        {
            string name = GetStr(dr, 2);
            if (string.IsNullOrEmpty(name)) return;

            Household hh = new Household();
            hh.Name = name;
            hh.HoldType = houseType;
            hh.Gender = GetStr(dr, 3);
            hh.Nation = GetStr(dr, 4);
            hh.Political = GetStr(dr, 5);
            hh.IdNum = GetStr(dr, 6).ToUpper();
            hh.Addr = GetStr(dr, 7);
            hh.Phone = GetStr(dr, 8);
            hh.Remark = GetStr(dr, 9);

            h.Holds.Add(hh);
        }

        public static int InsertHouse(List<House> list)
        {
            try
            {
                int rs = 0;
                foreach (House h in list)
                {
                    h.Id = HouseDAL.GetHouseMaxId();
                    int count = HouseDAL.InsertHouse(h);
                    if (count == 0) continue;

                    foreach (Household hh in h.Holds)
                    {
                        hh.House = h.Id;
                        count = HouseDAL.InsertHousehold(hh);
                    }

                    rs++;
                }

                return rs;
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace);
            }

            return 0;
        }
    }
}