public void Fun()
{
string filename = "人员名单.xlsx";
FileInfo fi = new FileInfo(filename);
StringBuilder errorinfo = new StringBuilder();
using (ExcelPackage package = new ExcelPackage(fi))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets["Sheet1"];
int i = 1;
using (Context db = new Context())
{
//db.Configuration.AutoDetectChangesEnabled = false;
//using (TransactionScope tran = new TransactionScope())
//{
while (i < 100)
{
i++;
string name = ExcelHelper.ReadString(worksheet.Cells[i, 4]);
if (string.IsNullOrEmpty(name)) continue;
if ((from o in db.SystemUsers where o.Name == name select o).FirstOrDefault() != null)
{
Console.WriteLine(name + " 已存在");
continue;
}
string rolename = ExcelHelper.ReadString(worksheet.Cells[i, 2]);
int roleid = (from o in db.Roles where o.Name == rolename select o.Id).FirstOrDefault();
if (roleid == 0)
{
roleid= (from o in db.Roles where o.Name == "销售员" select o.Id).First();
}
SystemUser user = new SystemUser
{
Name = name,
Password = "******",
LoginName = name,
Email = ExcelHelper.ReadString(worksheet.Cells[i, 5])
};
SystemUser u = new SystemUser();
db.SystemUsers.Add(u);
u.Save(db, user);
RoleUser ru = new RoleUser {UserId = u.Id, RoleId = (int) roleid};
db.RoleUsers.Add(ru);
string projname = ExcelHelper.ReadString(worksheet.Cells[i, 1]);
int project =
(from o in db.Departments
where o.Name == projname
select o.Id).First();
DepartmentUser du = new DepartmentUser {UserId = u.Id};
string d3 = ExcelHelper.ReadString(worksheet.Cells[i, 3]);
if (string.IsNullOrEmpty(d3))
{
d3 = "公共客户";
}
else
{
d3 = rolename;
}
Department dept =
(from o in db.Departments where o.PId == project && o.Name == d3 select o).First();
du.DepartmentId = dept.Id;
db.DepartmentUsers.Add(du);
db.SaveChanges();
// tran.Complete();
// }
}
}
}
}
public JsonResult DepartmentAddUser(int id, int userid)
{
if (!UserInfo.CurUser.HasRight("系统管理-部门管理")) return Json(new Result { success = false, obj = "没有权限" });
Models.Result result = new Models.Result();
DepartmentUser du = (from o in db.DepartmentUsers where o.DepartmentId == id && o.UserId == userid select o).FirstOrDefault();
if (du == null)
{
du = new DepartmentUser { UserId = userid, DepartmentId = id };
db.DepartmentUsers.Add(du);
db.SaveChanges();
DepartmentBLL.UpdateDepartments();
}
result.success = true;
return Json(result);
}
public string Syn()
{
OUContext db=new OUContext();
var users = (from o in db.SystemUsers select o).ToList();
string sqlUsers = @" select a.nvarchar8 as company ,a.nvarchar21 as department ,a.nvarchar15 as subdepartment ,a.nvarchar7 as email,a.nvarchar6 as name ,nvarchar20 as loginname,nvarchar10 as title
from AllUserData a where a.tp_ListId ='10236E7F-298D-4247-BEE3-1FEB8738784D' and nvarchar20 is not null and a.nvarchar8 is not null";
System.Data.SqlClient.SqlConnection conn = SqlHelper.Conn();
conn.Open();
var OaUsers = conn.Query(sqlUsers);
foreach (var oaUser in OaUsers)
{
bool found = false;
string loginname = oaUser.loginname;
foreach (var systemUser in users)
{
if(loginname==systemUser.WorkNO)
{
found = true;
systemUser.Email = oaUser.email;
systemUser.Name = oaUser.name;
}
}
if(found==false)
{
db.SystemUsers.Add(new SystemUser
{
Email = oaUser.email,
Name = oaUser.name,
Password = "******",
WorkNO = loginname
});
}
db.SaveChanges();
}
//找不到的账号禁用
users.ForEach(o =>
{
if (o.Id > 1)
{
if ((from u in OaUsers where u.loginname == o.WorkNO select o.Id).ToList().Count == 0)
{
o.State = 1;
}
}
});
db.SaveChanges();
var departments = (from o in db.Departments select o).ToList();
var sqlDepart =@"select a.nvarchar1 as company ,a.nvarchar3 as department ,a.nvarchar4 as subdepartment ,u1.tp_Title as submanager,u2.tp_Title as manager
from AllUserData a
join UserInfo u1 on a.int1=u1.tp_deleted --and u1.tp_Login like 'glcp%'
join UserInfo u2 on a.int2=u2.tp_deleted --and u2.tp_Login like 'glcp%'
where a.tp_ListId ='40394CC4-40A8-4B8F-BF08-068DB9E90FB7' ";
var OaDeparts = conn.Query(sqlUsers);
var q1 = (from o in OaDeparts select o.company).Distinct();
//公司一级
foreach (string o in q1)
{
bool found = false;
foreach (var department in departments)
{
if(department.Name==o)
{
found = true;
}
}
if(!found&&(!string.IsNullOrEmpty(o)))
{
db.Departments.Add(new Department {DepartmentType = "公司", Name = o, PId = 1});
}
}
db.SaveChanges();
//部门一级
departments = (from o in db.Departments select o).ToList();
var q2 = (from o in OaDeparts select new {company = o.company, department = o.department}).Distinct();
foreach (var o in q2)
{
bool found = false;
Department parent = (from d in departments where d.Name == o.company select d).FirstOrDefault();
if(parent!=null)
{
foreach (var department in departments)
{
if(department.Name==o.department && department.PId==parent.Id)
{
found = true;
}
}
}
if(!found)
{
db.Departments.Add(new Department {DepartmentType = "部门", Name = o.department, PId = parent.Id});
}
}
db.SaveChanges();
//子部门一级
departments = (from o in db.Departments select o).ToList();
var q3 =
(from o in OaDeparts
select new {company = o.company, department = o.department, subdepartment = o.subdepartment}).Distinct();
foreach (var o in q3)
{
bool found = false;
Department company = (from d in departments where d.Name == o.company select d).First();
Department department =
(from d in departments where d.Name == o.department && d.PId == company.Id select d).First();
foreach (var dept in departments)
{
if(dept.Name==o.subdepartment )//-- 子部门名称唯一 && dept.PId==department.Id
{
found = true;
}
}
if(!found)
{
db.Departments.Add(new Department
{DepartmentType = "部门", Name = o.subdepartment, PId = department.Id});
}
}
db.SaveChanges();
//部门用户
departments = (from o in db.Departments select o).ToList();
users = (from o in db.SystemUsers select o).ToList();
var departUsers = (from o in db.DepartmentUsers select o).ToList();
foreach (var oaUser in OaUsers)
{
var dept = (from o in departments where o.Name == oaUser.subdepartment select o).First();
var user= (from o in users where o.WorkNO==oaUser.loginname select o).First();
var du =
(from o in departUsers where o.DepartmentId == dept.Id && o.UserId == user.Id select o).
FirstOrDefault();
if(du==null)
{
du=new DepartmentUser{DepartmentId = dept.Id,UserId = user.Id };
db.DepartmentUsers.Add(du);
}
if (oaUser.title != null && ((oaUser.title as string).Contains("经理") || (oaUser.title as string).Contains("领导")))
{
du.IsManager = 1;
}
else
{
du.IsManager = 0;
}
var delList =
(from o in departUsers where o.DepartmentId != dept.Id && o.UserId == user.Id select o).ToList();
delList.ForEach(o=>db.DepartmentUsers.Remove(o));
}
db.SaveChanges();
return "OK";
}
