public static List <Dictionary <string, object> > GetCustomerServiceCountGroupByProjectStatus(int UserID, int Status) { ResetCache(); var results = new List <Dictionary <string, object> >(); List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); var ProjectList = Project.GetXiaoQuProjectListBySystemUserID(UserID); conditions.Add("[ServiceStatus]=@Status"); parameters.Add(new SqlParameter("@Status", Status)); string cmdtext = "select *,(select AllParentID from [Project] where ID=[CustomerService].ProjectID) as AllParentID from [CustomerService] where " + string.Join(" and ", conditions.ToArray()); var list = GetList <CustomerServiceDetail>(cmdtext, parameters).ToArray(); var my_list = new CustomerServiceDetail[] { }; var dic = new Dictionary <string, object>(); foreach (var project in ProjectList) { my_list = list.Where(p => !string.IsNullOrEmpty(p.AllParentID) && p.AllParentID.Contains("," + project.ID.ToString() + ",")).ToArray(); dic = new Dictionary <string, object>(); dic["ProjectID"] = project.ID; dic["ProjectName"] = project.Name; dic["TotalCount"] = my_list.Length; results.Add(dic); } results = results.OrderByDescending(p => Convert.ToInt32(p["TotalCount"])).ToList(); return(results); }
public static List <Dictionary <string, object> > GetCustomerServiceCountGroupByUserStatus(int UserID, int Status, int ProjectID) { ResetCache(); var results = new List <Dictionary <string, object> >(); List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); var ProjectIDList = new List <int>() { ProjectID }; List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); Project.GetMyProjectListByUserID(UserID, out EqualIDList, out InIDList, ProjectIDList: ProjectIDList); List <string> cmdlist = new List <string>(); if (InIDList.Count > 0) { foreach (var InID in InIDList) { cmdlist.Add("([Project].AllParentID like '%," + InID + ",%' or [ID]=" + InID + ")"); } } if (EqualIDList.Count > 0) { foreach (var EqualID in EqualIDList) { cmdlist.Add("([Project].ID=" + EqualID + ")"); } } if (cmdlist.Count > 0) { conditions.Add("[ProjectID] in (select ID from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + "))"); } conditions.Add("[ServiceStatus]=@Status"); parameters.Add(new SqlParameter("@Status", Status)); string cmdtext = "select * from [CustomerService] where " + string.Join(" and ", conditions.ToArray()); Utility.LogHelper.WriteInfo("GetCustomerServiceCountGroupByUserStatus", cmdtext); var list = GetList <CustomerServiceDetail>(cmdtext, parameters).ToArray(); var UserList = User.GetAPPUserList(); CustomerServiceDetail[] my_list = new CustomerServiceDetail[] { }; var dic = new Dictionary <string, object>(); var UserIDList = UserList.Select(p => p.UserID).ToList(); foreach (var user in UserList) { //conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + UserID.ToString() + ",%'"); my_list = list.Where(p => !string.IsNullOrEmpty(p.ServiceAccpetManID) && p.ServiceAccpetManID.Replace("[", ",").Replace("]", ",").Contains("," + user.UserID.ToString() + ",")).ToArray(); if (my_list.Length > 0) { dic = new Dictionary <string, object>(); dic["UserID"] = user.UserID; dic["UserName"] = string.IsNullOrEmpty(user.RealName) ? user.LoginName : user.RealName; dic["TotalCount"] = my_list.Length; results.Add(dic); } } my_list = list.Where(p => { if (string.IsNullOrEmpty(p.ServiceAccpetManID)) { return(true); } string[] ServiceAccpetManIDArray = p.ServiceAccpetManID.Replace("[", ",").Replace("]", ",").Split(','); foreach (var MyUserID in UserIDList) { foreach (var item in ServiceAccpetManIDArray) { if (MyUserID.ToString().Equals(item)) { return(false); } } } return(true); }).ToArray(); if (my_list.Length > 0) { dic = new Dictionary <string, object>(); dic["UserID"] = -1; dic["UserName"] = "******"; dic["TotalCount"] = my_list.Length; results.Add(dic); } results = results.OrderByDescending(p => Convert.ToInt32(p["TotalCount"])).ToList(); return(results); }
public static List <Dictionary <string, object> > GetCustomerServiceCountGroupByUserStatus(int UserID, int Status, int ProjectID) { ResetCache(); var results = new List <Dictionary <string, object> >(); List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); var ProjectIDList = new List <int>() { ProjectID }; var myProjectIDList = Project.GetProjectIDListbyIDList(ProjectIDList: ProjectIDList, UserID: UserID); conditions.Add("[ServiceStatus]=@Status"); parameters.Add(new SqlParameter("@Status", Status)); string cmdtext = "select [ID],[ProjectID] from [CustomerService] where " + string.Join(" and ", conditions.ToArray()); var list = GetList <CustomerServiceDetail>(cmdtext, parameters).ToArray(); if (myProjectIDList.Length > 0) { list = list.Where(p => myProjectIDList.Contains(p.ProjectID)).ToArray(); } if (list.Length == 0) { return(new List <Dictionary <string, object> >()); } int MinServiceID = list.Min(p => p.ID); int MaxServiceID = list.Max(p => p.ID); var UserList = User.GetAPPUserList(); CustomerServiceDetail[] my_list = new CustomerServiceDetail[] { }; var dic = new Dictionary <string, object>(); var UserIDList = UserList.Select(p => p.UserID).ToList(); var serviceAccpetList = CustomerService_Accpet.GetCustomerService_AccpetListByMinMaxServiceID(MinServiceID, MaxServiceID); foreach (var user in UserList) { var myAccpetList = serviceAccpetList.Where(p => p.AccpetManID == user.UserID && p.AccpetStatus == 1).ToArray(); //conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + UserID.ToString() + ",%'"); my_list = list.Where(p => myAccpetList.Select(q => q.ServiceID).Contains(p.ID)).ToArray(); if (my_list.Length > 0) { dic = new Dictionary <string, object>(); dic["UserID"] = user.UserID; dic["UserName"] = user.FinalRealName; dic["TotalCount"] = my_list.Length; results.Add(dic); } } my_list = list.Where(p => { var myAccpetList = serviceAccpetList.Where(q => q.ServiceID == p.ID).ToArray(); var myUserList = UserList.Where(q => myAccpetList.Select(m => m.AccpetManID).Contains(q.UserID)).ToArray(); if (myUserList.Length == 0) { return(true); } return(false); }).ToArray(); if (my_list.Length > 0) { dic = new Dictionary <string, object>(); dic["UserID"] = -1; dic["UserName"] = "******"; dic["TotalCount"] = my_list.Length; results.Add(dic); } results = results.OrderByDescending(p => Convert.ToInt32(p["TotalCount"])).ToList(); return(results); }