StaffPerformance/UpdateUserDepartment/Form1.cs

using NPOI.SS.Formula.Functions;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using wispro.sp.api;
using wispro.sp.entity;

namespace UpdateUserDepartment
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var ofd = new System.Windows.Forms.OpenFileDialog();

            if(ofd.ShowDialog() == DialogResult.OK)
            {
                string strPath = ofd.FileName;
                var dt = wispro.sp.utility.NPOIExcel.ExcelToTable(strPath);

                wispro.sp.api.spDbContext db = new wispro.sp.api.spDbContext();
                var depList = db.Departments.ToList();

                foreach ( DataRow row in dt.Rows )
                {
                    string strId = row[0].ToString();

                    
                    string strDepart = row["部门"].ToString();

                    System.Diagnostics.Debug.WriteLine(string.Format( "部门：{0}",strDepart));

                    if(!string.IsNullOrEmpty(strDepart))
                    {
                        string[] deps = GetDepartmentPath(strDepart);

                        if (deps != null && deps.Length > 0)
                        {
                            Department department = GetUserDepartment(deps, db);
                            UpdateDepartmentPosition(db, strId, department);
                        }

                    }

                }

            }

            static Department GetUserDepartment(string[] deps, wispro.sp.api.spDbContext db)
            {
                Department temDept = null;
                for (int i=0;i<deps.Length;i++)
                {
                    Department dep = null;
                    if(temDept == null)
                    {
                        dep = db.Departments.Where<Department>(d => d.Name == deps[i].Trim()).FirstOrDefault();
                    }
                    else
                    {
                        dep = db.Departments.Where<Department>(d => d.Name == deps[i].Trim() && d.parentId == temDept.Id).FirstOrDefault();
                    }

                    if (dep == null)
                    {
                        dep = new Department()
                        {
                            Name = deps[i].Trim(),
                            parentId = temDept == null ? null : temDept.Id,
                            ancestors = temDept.ancestors + "," + temDept.Id.ToString()
                        };

                        db.Departments.Add(dep);
                        db.SaveChanges();
                    }

                    temDept = dep;

                }

                return temDept;
            }

            static string[] GetDepartmentPath(string strDepart)
            {
                if(string.IsNullOrEmpty(strDepart) || strDepart =="NULL")
                {
                    return null;
                }
                string[] deps = strDepart.Trim().Split(new char[] { ',' });


                if (deps.Length > 0)
                {
                    System.Text.RegularExpressions.Regex r = new System.Text.RegularExpressions.Regex("(.*)\\((.*)\\)");
                    System.Text.RegularExpressions.Match m = r.Match(deps[0]);
                    if (m.Success)
                    {
                        string[] temDeps = new string[deps.Length + 1];
                        temDeps[0] = m.Groups[1].Value;
                        temDeps[1] = m.Groups[2].Value;

                        if (deps.Length > 1)
                        {
                            for (int i = 1; i < deps.Length; i++)
                            {
                                temDeps[i + 1] = deps[i];
                            }
                        }

                        deps = temDeps;
                    }


                }

                return deps;
            }
        }

        private static void UpdateDepartmentPosition(spDbContext db, string strId, Department department)
        {
            var dpList = db.DepartmentPositions.Where(dp => dp.StaffId == int.Parse(strId)).ToList<DepartmentPosition>();

            bool isExist = false;
            foreach (var dp in dpList)
            {
                if (dp.departmentId != department.Id)
                {
                    db.DepartmentPositions.Remove(dp);
                }
                else
                {
                    isExist = true;
                }

            }

            if (!isExist)
            {
                db.DepartmentPositions.Add(
                    new DepartmentPosition()
                    {
                        departmentId = department.Id,
                        PositionId = 5,
                        StaffId = int.Parse(strId)
                    }
                    );
            }

            db.SaveChanges();
        }
    }
}