DataMigrateController

using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using PT.BLL;
using PT.Common;
using PT.Model;
using PT.Web.Mvc.App_Start;

namespace PT.Web.Mvc.Controllers
{
    [LoginAuthorizationFilter]
    public class DataMigrateController : Controller
    {

        IPListBll iPListBll = new IPListBll();
        SegmentListBll segmentListBll = new SegmentListBll();
        PurposeListBll purposeListBll = new PurposeListBll();

        private SessionHelper sessionHelper;


        public IActionResult Index()
        {
            return View();
        }

        /// <summary>
        /// IP旧数据迁移
        /// </summary>
        /// <returns></returns>
        public IActionResult IPListDataMigrate()
        {

            ResponseResult result = new ResponseResult()
            {
                code = 0,
                msg = "成功",
                data = 0

            };
            try
            {
                sessionHelper = new SessionHelper(HttpContext.Session);
                UserList user = sessionHelper.GetUserInfoString();

                DataTable dataTable = ExcelHelper.ExcelToTable(@"D:\Work\项目资料\ESL IP Address Managment System\正式库0602\IP_USAGE.xlsx");
                List<IPList> ts = new List<IPList>();
                IPList t = null;
                foreach (DataRow item in dataTable.Rows)
                {

                    string vlan = item["vlan"].ToString();
                    string ip = item["ip"].ToString();
                    string PURID = item["PURID"].ToString();
                    string device = item["device"].ToString();
                    string macaddress = item["macaddress"].ToString();
                    string nodename = item["nodename"].ToString();
                    string location = item["location"].ToString();
                    string username = item["username"].ToString();
                    string maintby = item["maintby"].ToString();
                    string maintdate = item["maintdate"].ToString();
                    string expdate = item["expdate"].ToString();
       

                    t = new IPList();
                    t.IP = vlan + "." + ip;
                    t.MacAddress = macaddress;
                    t.NodeName = nodename;
                    t.Location = unicode2String(location);
                    t.UserName = unicode2String(username);
                    t.PurposeId = PURID;
                    t.Device = unicode2String(device);
                    t.Assgn = maintby;
                    t.Date = DateTime.Now;
                    if (maintdate.Length == 8)
                    {
                        maintdate = maintdate.Insert(4, "-").Insert(7, "-");
                        t.Date = Convert.ToDateTime(maintdate);
                    }
                    if (expdate.Length == 8)
                    {
                        expdate = expdate.Insert(4, "-").Insert(7, "-");
                        t.Expired = Convert.ToDateTime(expdate);
                    }

                    ts.Add(t);
                }



                string s = dataTable.ToJson();

                ts[0].ModifyBy = user.Id + "|" + user.UserID + "|" + user.UserName;
                result.data = iPListBll.ImportUpdate(ts);

            }
            catch (Exception ex)
            {

                result.code = -1;
                result.msg = ex.Message;
            }

            var res = new JsonResult(result);
            return res;
        }


        /// <summary>
        /// IPSegmentList数据迁移
        /// </summary>
        /// <returns></returns>
        public IActionResult SegmentListDataMigrate()
        {

            ResponseResult result = new ResponseResult()
            {
                code = 0,
                msg = "成功",
                data = 0

            };
            try
            {
                sessionHelper = new SessionHelper(HttpContext.Session);
                UserList user = sessionHelper.GetUserInfoString();

                DataTable dataTable = ExcelHelper.ExcelToTable(@"D:\Work\项目资料\ESL IP Address Managment System\正式库0602\IP_SEGMENT.xlsx");
                List<SegmentList> ts = new List<SegmentList>();
                SegmentList t = null;
                int s = 0, f = 0;
                foreach (DataRow item in dataTable.Rows)
                {

                    string vlan = item["vlan"].ToString();
                    string floor = item["floor"].ToString();
                    string ipmax = item["ipmax"].ToString();
                    string ipmin = item["ipmin"].ToString();
                    string remark = item["remark"].ToString();
                    string maintby = item["maintby"].ToString();


                    t = new SegmentList();
                    t.Floor = floor;
                    t.IPSegment = vlan;
                    t.Available = ipmin + " - " + ipmax;
                    t.Start = Convert.ToInt32(ipmin);
                    t.End = Convert.ToInt32(ipmax);
                    t.Remark = remark;
                    t.CreatBy = maintby;
                    t.CreatDate = DateTime.Now;


                    ts.Add(t);

                    if (segmentListBll.IsExist(t))
                    {
                        f += 1;
                    }
                    else
                    {

                        int n = iPListBll.GenerateIP(t.IPSegment, t.Start, t.End, t.Floor, t.CreatBy);
                        segmentListBll.InsertableIgnoreColumns(t);
                        s += 1;
                    }
                }

                result.data = "s:" + s + ",f:" + f;

            }
            catch (Exception ex)
            {

                result.code = -1;
                result.msg = ex.Message;
            }

            var res = new JsonResult(result);
            return res;
        }


        public IActionResult PurposeListDataMigrate()
        {

            ResponseResult result = new ResponseResult()
            {
                code = 0,
                msg = "成功",
                data = 0

            };
            try
            {
                sessionHelper = new SessionHelper(HttpContext.Session);
                UserList user = sessionHelper.GetUserInfoString();

                DataTable dataTable = ExcelHelper.ExcelToTable(@"D:\Work\项目资料\ESL IP Address Managment System\正式库0602\IP_PURPOSE.xlsx");
                List<PurposeList> ts = new List<PurposeList>();
                PurposeList t = null;
                int s = 0, f = 0;
                foreach (DataRow item in dataTable.Rows)
                {

                    string PURID = item["PURID"].ToString();
                    string PURNAME = item["PURNAME"].ToString();
                    string PURCOLOR = item["PURCOLOR"].ToString();
                    string maintby = item["maintby"].ToString();


                    t = new PurposeList();
                    t.PID = PURID;
                    t.Name = PURNAME;
                    t.Color = PURCOLOR;
                    t.CreatBy = maintby;
                    t.CreatDate = DateTime.Now;


                    ts.Add(t);

                    if (purposeListBll.IsExist(t))
                    {

                        f += 1;
                    }
                    else
                    {

                        purposeListBll.InsertableIgnoreColumns(t);
                        s += 1;
                    }
                }

                result.data = "s:" + s + ",f:" + f;

            }
            catch (Exception ex)
            {

                result.code = -1;
                result.msg = ex.Message;
            }

            var res = new JsonResult(result);
            return res;
        }

        public static String unicode2String(String unicode)
        {
            StringBuilder str = new StringBuilder();
            if (unicode.IndexOf("&#") < 0)
            {
                return unicode;
            }

            string s = unicode.Substring(0, unicode.IndexOf("&#"));
            string e = unicode.Substring(unicode.LastIndexOf("&#") + 8);

            str.Append(s);

            unicode = unicode.Substring(unicode.IndexOf("&#"));
            unicode = unicode.Substring(0, unicode.LastIndexOf("&#") + 7);

            Regex myRegex = new Regex("^[0-9]*$");

            if (unicode.StartsWith("&#x"))
            {
                String[] hex = unicode.Replace("&#x", "").Split(";");
                for (int i = 0; i < hex.Length; i++)
                {
                    if (!string.IsNullOrWhiteSpace(hex[i]))
                    {
                        int data = Convert.ToInt32(hex[i]);
                        str.Append((char)data);
                    }

                }
            }
            else if (unicode.StartsWith("&#"))
            {
                String[] hex = unicode.Replace("&#", "").Split(";");
                for (int i = 0; i < hex.Length; i++)
                {
                    if (!string.IsNullOrWhiteSpace(hex[i]) && myRegex.IsMatch(hex[i]))
                    {

                        int data = Convert.ToInt32(hex[i]);// (, 10);
                        str.Append((char)data);
                    }



                }
            }

            str.Append(e);

            return str.ToString();
        }
    }
}