public List <Model.InternalUser> GetInternalUserList()
{
List <Model.InternalUser> list = new List <Model.InternalUser>();
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
list = (from c in vdc.internal_users
select new Model.InternalUser
{
InternalUserId = c.internal_user_id,
WorkNum = c.work_num,
Pwd = c.pwd,
Name = c.name,
Gender = c.gender,
RegDate = c.reg_date,
DepartmentName = c.department_name,
Jobs = c.jobs,
UserGroup = c.user_group,
FkUserGroupId = c.fk_user_group_id,
AllocateDate = c.allocate_date,
Operater = c.operater,
UseStatus = c.use_status
}).ToList <Model.InternalUser>();
}
return(list);
}
public string GetCusSvrMode()
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
return("");
}
}
//获得用户的二级权限菜单
public string IsPermited(int internalUserId, string parentMenuCode)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var linqData1 = from c in vdc.internal_users
where c.internal_user_id == internalUserId
select c.fk_user_group_id;
var linqData2 = linqData1.FirstOrDefault();
if (linqData2 == null)
{
return("[]");
}
int userGroupId = Convert.ToInt32(linqData2);
var linqData3 = (from c in vdc.menus
from cc in c.user_group_menus
where c.parent_menu_code == parentMenuCode &&
cc.fk_user_group_id == userGroupId &&
cc.delete_date == null
orderby c.sequence
select new
{
ID = c.menu_code,
Name = c.menu_title
}).ToList();
return(Helper.Serialize(linqData3));
}
}
//获取一个用户组的用户以及未分配用户组的用户
public List <Model.InternalUser> GetGroupAndUnAllocateUser(int userGroupId)
{
List <Model.InternalUser> resList = new List <Model.InternalUser>();
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
resList = (from c in vdc.internal_users
where c.fk_user_group_id == userGroupId ||
(c.fk_user_group_id == null && c.use_status == '5')
orderby c.fk_user_group_id descending
select new Model.InternalUser
{
InternalUserId = c.internal_user_id,
WorkNum = c.work_num,
//Pwd = c.pwd,
Name = c.name,
Gender = c.gender,
//RegDate = c.reg_date,
DepartmentName = c.department_name,
Jobs = c.jobs,
UserGroup = c.user_group,
//FkUserGroupId = c.fk_user_group_id,
//AllocateDate = c.allocate_date,
//Operater = c.operater,
//UseStatus = c.use_status
}).ToList <Model.InternalUser>();
}
return(resList);
}
/// <summary>
/// Get client name by client number
/// </summary>
public string GetClientName(string clientNum)
{
//模拟数据，需从数据库获得
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
return(vdc.U000s.Where(c => c.userSN == clientNum).First().phone);
}
}
//get config data
public DAL.config GetConfigData()
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
config data = vdc.configs.First();
return(data);
}
}
//删除一个内部用户
public void DeleteInternalUser(int internalUserId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
internal_user inteUser = vdc.internal_users.First(c => c.internal_user_id == internalUserId) as internal_user;
vdc.internal_users.DeleteOnSubmit(inteUser);
vdc.SubmitChanges();
}
}
//save M091103
public void SaveM091103(int countSizeLevel, int showCountDownSizeLevel)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
config linqData = vdc.configs.First();
linqData.countSizeLevel = countSizeLevel;
linqData.showCountDownSizeLevel = showCountDownSizeLevel;
vdc.SubmitChanges();
}
}
//启用或者停用一个内部用户
//0：待用;1：暂停；5：启用
public void EnableOrDisableInternalUser(int internalUserId, char status)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
internal_user inteUser = vdc.internal_users.First <internal_user>(c => c.internal_user_id == internalUserId);
inteUser.use_status = status;
vdc.SubmitChanges();
}
}
//save M091101
public void SaveM091101(int mode, int levelSeconds)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
config linqData = vdc.configs.First();
linqData.cusSvrMode = mode;
linqData.levelSeconds = levelSeconds;
vdc.SubmitChanges();
}
}
//修改密码
public void ChangePassword(int internalUserId, string newPassword)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
internal_user inteUser = vdc.internal_users.FirstOrDefault <internal_user>(c => c.internal_user_id == internalUserId);
inteUser.pwd = newPassword;
vdc.SubmitChanges();
}
}
//save M091102
public void SaveM091102(int webDelay, int maxCusSvrConnLevel, int maxUserConnNum, int cusSvrUserMaxAmount)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
config linqData = vdc.configs.First();
linqData.webDelay = webDelay;
linqData.maxCusSvrConnLevel = maxCusSvrConnLevel;
linqData.maxUserConnNum = maxUserConnNum;
linqData.cusSvrUserMaxAmount = cusSvrUserMaxAmount;
vdc.SubmitChanges();
}
}
//添加一个用户组
public int AddUserGroup(string userGroupName)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
user_group userGroupObj = new user_group();
userGroupObj.user_group_name = userGroupName;
vdc.user_groups.InsertOnSubmit(userGroupObj);
vdc.SubmitChanges();
return(userGroupObj.user_group_id);
}
}
//在一个用户组中添加一个用户
public string AddUserToGroup(int userGroupId, string userGroupName, int internalUserId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
internal_user inteUser = vdc.internal_users.FirstOrDefault <internal_user>(c => c.internal_user_id == internalUserId);
inteUser.fk_user_group_id = userGroupId;
inteUser.user_group = userGroupName;
vdc.SubmitChanges();
return(GetWorkNum(vdc, userGroupId, internalUserId));
}
}
//从一个用户组中删除一个用户
public string DelUserFromGroup(int internalUserId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
internal_user inteUser = vdc.internal_users.FirstOrDefault <internal_user>(c => c.internal_user_id == internalUserId);
int userGroupId = Convert.ToInt32(inteUser.fk_user_group_id);
inteUser.fk_user_group_id = null;
inteUser.user_group = null;
vdc.SubmitChanges();
return(GetWorkNum(vdc, userGroupId, internalUserId));
}
}
//修改一个内部用户信息
public void EditInternalUserInfo(internal_user dataObj)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
internal_user sqlData = vdc.internal_users.First <internal_user>(c => c.internal_user_id == dataObj.internal_user_id);
sqlData.work_num = dataObj.work_num;
sqlData.name = dataObj.name;
sqlData.gender = dataObj.gender;
sqlData.department_name = dataObj.department_name;
sqlData.jobs = dataObj.jobs;
vdc.SubmitChanges();
}
}
public List <string> GetCusSvrWorkNum()
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
int cusSvrMenuId = vdc.menus.Where(c => c.menu_code == "M01").First().menu_id;
List <string> cusSvrWorkNumList = (from c in vdc.user_groups
from o in c.internal_users
from p in c.user_group_menus
where p.fk_menu_id == cusSvrMenuId &&
p.delete_date == null
select o.work_num).ToList();
return(cusSvrWorkNumList);
}
}
public void SaveChatRecords(DataTable dt)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var linq = (from c in dt.AsEnumerable()
select new chat_record
{
user_num = c.Field <string>("userNum"),
cus_svr_num = c.Field <string>("cusSvrNum"),
user_send_msg = c.Field <string>("userSendMsg"),
cus_svr_send_msg = c.Field <string>("cusSvrSendMsg"),
date_time = c.Field <DateTime>("dateTime")
}).ToList();
vdc.chat_records.InsertAllOnSubmit(linq);
vdc.SubmitChanges();
}
}
//查找用户的客户经理工号
public string GetClientManagerWorkNum(string userSN)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var data = (from c in vdc.B005s
from o in vdc.internal_users
where c.userSN == userSN &&
c.internalUserSN == o.internal_user_id
select o).FirstOrDefault();
if (data == null)
{
return(null);
}
return(data.work_num);
}
}
//get config data
public Dictionary <string, int> GetConfigDataG()
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
config data = vdc.configs.First();
Dictionary <string, int> configData = new Dictionary <string, int>();
configData.Add("cusSvrMode", Convert.ToInt32(data.cusSvrMode));
configData.Add("cusSvrUserMaxAmount", Convert.ToInt32(data.cusSvrUserMaxAmount));
configData.Add("cusSvrUserDelLevel", Convert.ToInt32(data.cusSvrUserDelLevel));
configData.Add("maxCusSvrConnLevel", Convert.ToInt32(data.maxCusSvrConnLevel));
configData.Add("maxUserConnNum", Convert.ToInt32(data.maxUserConnNum));
configData.Add("countSizeLevel", Convert.ToInt32(data.countSizeLevel));
configData.Add("showCountDownSizeLevel", Convert.ToInt32(data.showCountDownSizeLevel));
//configData.Add("branchCountDownAjaxSizeLevel", Convert.ToInt32(data.branchCountDownAjaxSizeLevel));
configData.Add("levelSeconds", Convert.ToInt32(data.levelSeconds));
return(configData);
}
}
//修改密码
//true-修改成功；false-原密码错误
public bool ModifyPwd(int internalUserId, string oldPwd, string newPwd)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var linqData1 = vdc.internal_users.Where(c => c.internal_user_id == internalUserId && c.pwd == oldPwd).FirstOrDefault();
//原密码错误
if (linqData1 == null)
{
return(false);
}
linqData1.pwd = newPwd;
vdc.SubmitChanges();
return(true);
}
}
//获得某用户组的权限菜单
public string GetGroupPermissionMenu(int userGroupId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var linqData = (from c in vdc.menus
select c).ToList();
var dd = (from c in vdc.user_group_menus
where c.fk_user_group_id == userGroupId && c.delete_date == null
select c.fk_menu_id).ToList <int>();
foreach (var data in linqData)
{
if (dd.Contains(data.menu_id))
{
data.parent_menu_code = "true";
}
else
{
data.parent_menu_code = "false";
}
}
var jsonObj = (from c in linqData
orderby c.parent_menu_code descending
select new
{
menuId = c.menu_id,
menuCode = c.menu_code,
menuParentTitle = c.menu_parent_title,
menuTitle = c.menu_title,
moduleCode = c.module_code,
elementVersion = c.element_version,
controlVersion = c.control_version,
isAdd = c.parent_menu_code
}).ToList();
string jsonStr = Helper.Serialize(jsonObj);
return(jsonStr);
}
}
//get config page init config data
public string GetInitData()
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var jsonObj = from c in vdc.configs
select new
{
c.cusSvrMode,
c.cusSvrUserMaxAmount,
c.webDelay,
c.maxCusSvrConnLevel,
c.maxUserConnNum,
c.countSizeLevel,
c.showCountDownSizeLevel,
c.levelSeconds
};
return(Helper.Serialize(jsonObj));
}
}
//删除一个用户组
public void DelUserGroup(int userGroupId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
user_group userGrp = vdc.user_groups.First <user_group>(c => c.user_group_id == userGroupId);
userGrp.delete_date = DateTime.Now;
//vdc.user_groups.DeleteOnSubmit(userGrp);
var linqUserGrpMenu = from c in vdc.user_group_menus
where c.fk_user_group_id == userGroupId
select c;
foreach (user_group_menu userGrpMenu in linqUserGrpMenu)
{
userGrpMenu.delete_date = DateTime.Now;
}
vdc.SubmitChanges();
}
}
//登录
public int IsLogin(string workNum, string pwd)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var sqlData = from c in vdc.internal_users
where c.work_num == workNum && c.pwd == pwd && c.use_status == '5'
select c;
internal_user data = sqlData.FirstOrDefault <internal_user>();
if (data == null)
{
return(0);
}
else
{
return(data.internal_user_id);
}
}
}
//如果该用户所在的用户组具有在线客服的权限菜单，则返回用户的工号
public string GetWorkNum(VanchBgDataContext vdc, int userGroupId, int internalUserId)
{
int cusSvrMenuId = 4;
var data = from c in vdc.user_groups
from o in c.user_group_menus
where c.user_group_id == userGroupId &&
o.fk_menu_id == cusSvrMenuId &&
o.delete_date == null
select c;
if (data.Count() != 0)
{
var linqData = vdc.internal_users.Where(c => c.internal_user_id == internalUserId).First();
return(linqData.work_num);
}
return(null);
}
//添加一个内部用户
public string AddNewInternalUser(internal_user dataObj)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
if (dataObj.operater != null)
{
var operatorNameData = from c in vdc.internal_users
where c.internal_user_id == Convert.ToInt32(dataObj.operater)
select c.name;
dataObj.operater = operatorNameData.FirstOrDefault <string>().ToString();
}
vdc.internal_users.InsertOnSubmit(dataObj);
vdc.SubmitChanges();
}
string jsonStr = string.Format("{{'internalUserId':'{0}','operator':'{1}'}}", dataObj.internal_user_id.ToString(), dataObj.operater);
return(jsonStr);
}
//从一个用户组中删除一个权限菜单
public List <string> DelPermissionMenuFromGroup(int menuId, int groupId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
user_group_menu data = vdc.user_group_menus.FirstOrDefault <user_group_menu>(c => c.fk_menu_id == menuId && c.fk_user_group_id == groupId && c.delete_date == null);
data.delete_date = DateTime.Now;
vdc.SubmitChanges();
//假如删除的是在线客服权限菜单，返回该用户组中的用户编号
List <string> userWorkNumList = null;
if (menuId == 4)
{
userWorkNumList = (from c in vdc.internal_users
where c.fk_user_group_id == groupId
select c.work_num).ToList();
}
return(userWorkNumList);
}
}
//添加一个权限菜单到一个用户组中
public List <string> AddPermissionMenuToGroup(int menuId, int groupId)
{
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
user_group_menu data = new user_group_menu();
data.fk_menu_id = menuId;
data.fk_user_group_id = groupId;
vdc.user_group_menus.InsertOnSubmit(data);
vdc.SubmitChanges();
//假如添加的是在线客服权限菜单，返回该用户组中的用户编号
List <string> userWorkNumList = null;
if (menuId == 4)
{
userWorkNumList = (from c in vdc.internal_users
where c.fk_user_group_id == groupId
select c.work_num).ToList();
}
return(userWorkNumList);
}
}
//获取用户组的统计信息
public List <Model.UserGroupStatisticsInfo> GetUserGroupStatisticsInfo()
{
List <Model.UserGroupStatisticsInfo> resList = new List <Model.UserGroupStatisticsInfo>();
System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
stopwatch.Start();
using (VanchBgDataContext vdc = new VanchBgDataContext())
{
var userGroupList = from c in vdc.user_groups
where c.delete_date == null
select new
{
userGroupId = c.user_group_id,
userGroupName = c.user_group_name
};
Dictionary <int?, int> userAmount = (from user in vdc.internal_users
where user.fk_user_group_id != null
group user by user.fk_user_group_id into g
select new
{
fk_user_group_id = g.Key,
amount = g.Count()
}).ToDictionary(c => c.fk_user_group_id, c => c.amount);
var firstMenuLinq = from c in vdc.menus
from cc in c.user_group_menus
where c.parent_menu_code == null && cc.delete_date == null
select cc;
Dictionary <int, int> firstMenuAmount = (from c in firstMenuLinq
group c by c.fk_user_group_id into g
select new
{
fk_user_group_id = g.Key,
amount = g.Count()
}).ToDictionary(c => c.fk_user_group_id, c => c.amount);
var secondMenuLinq = from c in vdc.menus
from cc in c.user_group_menus
where c.parent_menu_code != null && cc.delete_date == null
select cc;
Dictionary <int, int> secondMenuAmount = (from c in secondMenuLinq
group c by c.fk_user_group_id into g
select new
{
fk_user_group_id = g.Key,
amount = g.Count()
}).ToDictionary(c => c.fk_user_group_id, c => c.amount);
foreach (var userGroup in userGroupList)
{
Model.UserGroupStatisticsInfo data = new Model.UserGroupStatisticsInfo();
#region 老的写法，速度大概在4秒左右
//data.UserGroupId = userGroupId;
//data.UserGroupName = vdc.user_groups.FirstOrDefault<user_group>(c => c.user_group_id == userGroupId).user_group_name;
//var linqUser = from user in vdc.internal_users
// where user.fk_user_group_id == userGroupId
// select user;
//data.UserAmount = linqUser.Count();
//var linqFirstMenu = from firstMenu in vdc.menus
// where (from menuId in vdc.user_group_menus where menuId.fk_user_group_id == userGroupId select menuId.fk_menu_id).Contains(firstMenu.menu_id)
// && firstMenu.parent_menu_code == null
// select firstMenu;
//data.FirstMenuAmount = linqFirstMenu.Count();
//var linqSecondMenu = from secondMenu in vdc.menus
// where (from menuId in vdc.user_group_menus where menuId.fk_user_group_id == userGroupId select menuId.fk_menu_id).Contains(secondMenu.menu_id)
// && secondMenu.parent_menu_code != null
// select secondMenu;
//data.SecondMenuAmount = linqSecondMenu.Count();
#endregion
data.UserGroupId = userGroup.userGroupId;
data.UserGroupName = userGroup.userGroupName;
if (userAmount.ContainsKey(data.UserGroupId))
{
data.UserAmount = userAmount[data.UserGroupId];
}
if (firstMenuAmount.ContainsKey(data.UserGroupId))
{
data.FirstMenuAmount = firstMenuAmount[data.UserGroupId];
}
if (secondMenuAmount.ContainsKey(data.UserGroupId))
{
data.SecondMenuAmount = secondMenuAmount[data.UserGroupId];
}
resList.Add(data);
}
}
stopwatch.Stop();
TimeSpan s = stopwatch.Elapsed;
return(resList);
}
