/*public static int GetTotalEmployees(int shopID)
* {
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* if (shopID > 0)
* {
* return (from m in db.Employee
* where m.status == 1 && m.shop_id.Equals(shopID)
* select m).Count();
* }
* else
* {
* return (from m in db.Employee
* where m.status == 1 && m.id>1
* select m).Count();
* }
*
* }
* }
* public static EmployeeIO GetEmployee(int id)
* {
* EmployeeIO retValue = null;
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* string rawSql = String.Format("SELECT A.shop_id,A.role_id,B.name AS shop_name FROM Employee AS A" +
* " LEFT OUTER JOIN Shop AS B ON A.shop_id=B.id" +
* " WHERE A.id={0}", id);
* retValue = db.Database.SqlQuery<EmployeeIO>(rawSql).FirstOrDefault();
* }
* return retValue;
* }
* //分页查询语句需要调整
* public static EmployeeIO[] GetEmployees(string nameFilter, int state, int department,
* int pageCount, int pageIndex, ref int totalCount)
* {
* //dynamic性能肯定好于Dictionory,低于var,如何dynamic可以直接转化为Json还是强烈建议用的，况且这里返回的数据层次单一
* //如果存储过程raw query出来的结果可以直接转化为dynamic还是直接用存储过程, 至少不用去学linq过于复杂的语法
* EmployeeIO[] retValue = null;
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* string rawSql = String.Format("EXEC dbo.P_SH_GetEmployees '{0}',{1},{2},{3},{4}",
* nameFilter, state, department, pageCount, pageIndex);
* retValue = db.Database.SqlQuery<EmployeeIO>(rawSql).ToArray();
* rawSql = String.Format("EXEC dbo.P_SH_GetEmployeesCount '{0}',{1},{2}",
* nameFilter, state, department);
* totalCount = db.Database.SqlQuery<int>(rawSql).FirstOrDefault();
* }
* return retValue;
* }
*
* public static int EnableEmployee(int id, int operatorID)
* {
* int retValue = 0;
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* Employee employee = (from m in db.Employee
* where (m.id == id && (m.status & 1) == 0)
* select m).FirstOrDefault();
* if (employee != null)
* {
* employee.status = 1;
* db.Entry<Employee>(employee).Property("status").IsModified = true;
* employee.update_by = operatorID;
* db.Entry<Employee>(employee).Property("update_by").IsModified = true;
* employee.update_date = DateTime.Now;
* db.Entry<Employee>(employee).Property("update_date").IsModified = true;
* retValue = db.SaveChanges();
*
* if (retValue > 0)
* {
* //添加操作日志
* EmployeeAction employeeAction = new EmployeeAction();
* employeeAction.action = 4;
* employeeAction.employee_id = operatorID;
* employeeAction.menu_id = 21;
* employeeAction.operation_date = DateTime.Now;
* employeeAction.remark = "id: " + employee.id;
* db.EmployeeAction.Add(employeeAction);
* db.SaveChanges();
* }
* }
* }
* return retValue;
* }
*
* public static int DisableEmployee(int id, int operatorID)
* {
* int retValue = 0;
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* Employee employee = (from m in db.Employee
* where (m.id == id && (m.status & 1) > 0)
* select m).FirstOrDefault();
* if (employee != null)
* {
* employee.status = 0;
* db.Entry<Employee>(employee).Property("status").IsModified = true;
* employee.update_by = operatorID;
* db.Entry<Employee>(employee).Property("update_by").IsModified = true;
* employee.update_date = DateTime.Now;
* db.Entry<Employee>(employee).Property("update_date").IsModified = true;
* retValue = db.SaveChanges();
*
* if (retValue > 0)
* {
* //添加操作日志
* EmployeeAction employeeAction = new EmployeeAction();
* employeeAction.action = 5;
* employeeAction.employee_id = operatorID;
* employeeAction.menu_id = 21;
* employeeAction.operation_date = DateTime.Now;
* employeeAction.remark = "id: " + employee.id;
* db.EmployeeAction.Add(employeeAction);
* db.SaveChanges();
* }
* }
* }
* return retValue;
* }
*
* public static int AddEmployee(string name, string mobile_no, string password, int role_id, int department_id, int shop_id,
* string telephone, string email, string birth_day,string entry_date, string note, int operatorID)
* {
* int retValue = 0;
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* int exsistedCount = (from m in db.Employee
* where (m.mobile_no.Trim().Equals(mobile_no.Trim()))
* select m).Count();
* if (exsistedCount > 0)
* {
* retValue = -2;
* }
* else
* {
* Employee employee = new Employee();
* employee.name = name.Trim();
* employee.mobile_no = mobile_no.Trim();
* if (password != null && password.Length > 0)
* {
* employee.password = password;
* }
* else
* {
* employee.password = "******";
* }
*
* employee.role_id = role_id;
* employee.department_id = department_id;
* employee.shop_id = shop_id;
* employee.telephone = telephone;
* employee.email = email;
* if (birth_day!=null && birth_day.Trim().Length>0) {
* employee.birth_day = Convert.ToDateTime(birth_day);
* } else {
* employee.birth_day = null;
* }
* if (entry_date!=null && entry_date.Trim().Length>0) {
* employee.entry_date = Convert.ToDateTime(entry_date);
* } else {
* employee.entry_date = null;
* }
* employee.note = note.Trim();
* employee.status = 1;
* employee.create_by = operatorID;
* employee.create_date = DateTime.Now;
* employee.update_by = operatorID;
* employee.update_date = DateTime.Now;
* db.Employee.Add(employee);
* db.SaveChanges();
*
* retValue = employee.id;
*
* if (retValue > 0)
* {
* //添加操作日志
* EmployeeAction employeeAction = new EmployeeAction();
* employeeAction.action = 2;
* employeeAction.employee_id = operatorID;
* employeeAction.menu_id = 21;
* employeeAction.operation_date = DateTime.Now;
* employeeAction.remark = "id: " + employee.id;
* db.EmployeeAction.Add(employeeAction);
* db.SaveChanges();
* }
* }
* }
* return retValue;
* }
*
* public static int ModifyEmployee(int id, string name, string mobile_no, string password, int role_id, int department_id, int shop_id,
* string telephone, string email, string birth_day, string entry_date, string note, int operatorID)
* {
* int retValue = 0;
* using (MySqlConnection conn = DBUtility.OpenConnection())
* {
* int exsistedCount = (from m in db.Employee
* where (m.mobile_no.Trim().Equals(mobile_no.Trim()) &&
* (!m.id.Equals(id)))
* select m).Count();
* if (exsistedCount > 0)
* {
* retValue = -2;
* }
* else
* {
* Employee employee = EmployeeDal.GetEmployee(id, conn);
* if (employee != null)
* {
* employee.name = name;
* db.Entry<Employee>(employee).Property("name").IsModified = true;
* employee.mobile_no = mobile_no.Trim();
* db.Entry<Employee>(employee).Property("mobile_no").IsModified = true;
* if (password != null && password.Length > 0)
* {
* employee.password = password;
* db.Entry<Employee>(employee).Property("password").IsModified = true;
* }
* employee.role_id = role_id;
* db.Entry<Employee>(employee).Property("role_id").IsModified = true;
* employee.department_id = department_id;
* db.Entry<Employee>(employee).Property("department_id").IsModified = true;
* employee.shop_id = shop_id;
* db.Entry<Employee>(employee).Property("shop_id").IsModified = true;
* employee.telephone = telephone;
* db.Entry<Employee>(employee).Property("telephone").IsModified = true;
* employee.email = email;
* db.Entry<Employee>(employee).Property("email").IsModified = true;
* if (birth_day != null && birth_day.Trim().Length > 0)
* {
* employee.birth_day = Convert.ToDateTime(birth_day);
* }
* else
* {
* employee.birth_day = null;
* }
* db.Entry<Employee>(employee).Property("birth_day").IsModified = true;
* if (entry_date != null && entry_date.Trim().Length > 0)
* {
* employee.entry_date = Convert.ToDateTime(entry_date);
* }
* else
* {
* employee.entry_date = null;
* }
* db.Entry<Employee>(employee).Property("entry_date").IsModified = true;
* employee.note = note.Trim();
* db.Entry<Employee>(employee).Property("note").IsModified = true;
*
* employee.update_by = operatorID;
* db.Entry<Employee>(employee).Property("update_by").IsModified = true;
* employee.update_date = DateTime.Now;
* db.Entry<Employee>(employee).Property("update_date").IsModified = true;
* retValue = db.SaveChanges();
*
* if (retValue > 0)
* {
* //添加操作日志
* EmployeeAction employeeAction = new EmployeeAction();
* employeeAction.action = 3;
* employeeAction.employee_id = operatorID;
* employeeAction.menu_id = 21;
* employeeAction.operation_date = DateTime.Now;
* employeeAction.remark = "id: " + employee.id;
* db.EmployeeAction.Add(employeeAction);
* db.SaveChanges();
* }
* }
* }
* }
* return retValue;
* }*/
public static JArray GetMenusByRoles(Dictionary <string, string> roles)
{
JArray retValue = new JArray();
using (MySqlConnection conn = DBUtility.OpenConnection())
{
try
{
Menu[] dbMenus = null;
if (roles.ContainsKey("0"))
{
//admin
dbMenus = MenuDal.GetAllMenus(conn);
}
else
{
string menuIDs = string.Join(",", roles.Keys.ToArray());
dbMenus = MenuDal.GetMenus(menuIDs, conn);
}
if (dbMenus != null && dbMenus.Length > 0)
{
//dbMenus为单级权限菜单,需要组装成二级菜单
Dictionary <int, List <Menu> > menuDict = new Dictionary <int, List <Menu> >();
foreach (Menu dbmenu in dbMenus)
{
List <Menu> menuGroup = null;
if (menuDict.ContainsKey(dbmenu.parent_id))
{
menuGroup = menuDict[dbmenu.parent_id];
//选择合适位置插入
int insertIndex = 0;
for (; insertIndex < menuGroup.Count; insertIndex++)
{
if (string.Compare(menuGroup[insertIndex].menu_no, dbmenu.menu_no, StringComparison.OrdinalIgnoreCase) > 0)
{
break;
}
}
menuGroup.Insert(insertIndex, dbmenu);
}
else
{
Menu parent = MenuDal.GetMenu(dbmenu.parent_id, conn);
if (parent != null)
{
menuGroup = new List <Menu>()
{
parent, dbmenu
};
menuDict.Add(dbmenu.parent_id, menuGroup);
}
}
}
//生成json数组
int[] keys = menuDict.Keys.ToArray();
for (int i = 0; i < keys.Length; i++)
{
Menu parent = menuDict[keys[i]][0];
JArray jChildren = new JArray();
for (int j = 1; j < menuDict[keys[i]].Count; j++)
{
Menu child = menuDict[keys[i]][j];
jChildren.Add(new JObject(new JProperty("id", child.menu_no),
new JProperty("name", child.name),
new JProperty("icon", child.icon_class),
new JProperty("url", child.action_url)));
}
JObject jMenuGroup = new JObject(new JProperty("id", parent.menu_no),
new JProperty("icon", parent.icon_class),
new JProperty("name", parent.name),
new JProperty("children", jChildren));
//父级菜单还需一次排序
int insertIndex = 0;
for (; insertIndex < retValue.Count; insertIndex++)
{
JObject item = (JObject)retValue[insertIndex];
if (string.Compare(item["id"].ToString(), jMenuGroup["id"].ToString(), StringComparison.OrdinalIgnoreCase) > 0)
{
break;
}
}
retValue.Insert(insertIndex, jMenuGroup);
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return(retValue);
}
