public static int GetCustomerServiceCountByStatus(int UserID, int Status, int Type = 0) { ResetCache(); List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); Project.GetMyProjectListByUserID(UserID, out EqualIDList, out InIDList); 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)); if (Status != 3 && Type == 0) { conditions.Add("REPLACE(REPLACE([ServiceAccpetManID],'[',','),']',',') like '%," + UserID.ToString() + ",%'"); } if (Type == 0) { conditions.Add("([DepartmentID] in (select DepartmentID from [UserDepartment] where [UserID]=@UserID) or DepartmentID is null or DepartmentID=0)"); parameters.Add(new SqlParameter("@UserID", UserID)); } int count = 0; using (SqlHelper helper = new SqlHelper()) { string cmdtext = "select count(1) from [CustomerService] where " + string.Join(" and ", conditions.ToArray()); var result = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters); if (result != null) { count = Convert.ToInt32(result); } } return(count); }
public static ProjectTree[] GetProjectTreeListByOrderNumberID(int ID, string Keywords, int OrderNumberID, int Level, int UserID) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("[IconID]<=" + Level); if (UserID > 0) { List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); Project.GetMyProjectListByUserID(UserID, out EqualIDList, out InIDList); 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 + ")"); } } string cmdwhere = string.Empty; if (cmdlist.Count > 0) { conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } } if (!string.IsNullOrEmpty(Keywords)) { parameters.Add(new SqlParameter("@Keywords", Keywords)); parameters.Add(new SqlParameter("@LikeKeywords", "%" + Keywords + "%")); conditions.Add("[isParent]=0"); conditions.Add("([Name] like @LikeKeywords or [ID] in (select [RoomID] from [RoomBasic] where [RoomType] like @LikeKeywords or [RoomOwner] like @LikeKeywords or [OwnerPhone] like @LikeKeywords or [RentName] like @LikeKeywords or [RentPhoneNumber] like @LikeKeywords))"); } else if (ID > 0) { conditions.Add("[ParentID]=@ID"); parameters.Add(new SqlParameter("@ID", ID)); } parameters.Add(new SqlParameter("@OrderNumberID", OrderNumberID)); ProjectTree[] list = GetList <ProjectTree>("select " + ProjectColumns + ",(select [OrderNumberID] from [ProjectOrderNumber] where [ProjectOrderNumber].[ProjectID]=[Project].[ID] and [OrderNumberID]=@OrderNumberID) as OrderNumberID from [Project] where " + string.Join(" and ", conditions.ToArray()) + " order by [OrderBy],[Level],[ID]", parameters).ToArray(); return(list); }
public static Project[] GetProjectListByUserID(int UserID, List <int> ProjectIDList) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); List <int> EqualIDList = null; List <int> InIDList = null; Project.GetMyProjectListByUserID(UserID, out EqualIDList, out InIDList, ProjectIDList: ProjectIDList); if (InIDList.Count > 0) { conditions.Add("ID in (" + string.Join(",", InIDList.ToArray()) + ")"); return(GetList <Project>("select * from [Project] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray()); } else { return(new Project[] { }); } }
public static ProjectTree[] GetProjectTreeListByContactID(int CompanyID, int ContactID, int UserID) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); if (UserID > 0) { List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); Project.GetMyProjectListByUserID(UserID, out EqualIDList, out InIDList); 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 + ")"); } } string cmdwhere = string.Empty; if (cmdlist.Count > 0) { conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } } conditions.Add("([ID]=1 or [ParentID]=1)"); parameters.Add(new SqlParameter("@ContactID", ContactID)); ProjectTree[] list = GetList <ProjectTree>("select " + ProjectColumns + ",(select [WechatContactID] from [Wechat_ContactProject] where [Wechat_ContactProject].[ProjectID]=[Project].[ID] and [WechatContactID]=@ContactID) as MsgID from [Project] where " + string.Join(" and ", conditions.ToArray()) + " order by [DefaultOrder]", parameters).ToArray(); return(list); }
public static int[] GetProjectIDListbyIDList(List <int> ProjectIDList = null, List <int> EqualProjectIDList = null, List <int> InProjectIDList = null, int UserID = 0) { if (EqualProjectIDList == null || InProjectIDList == null) { if (UserID > 0) { Project.GetMyProjectListByUserID(UserID, out EqualProjectIDList, out InProjectIDList, ProjectIDList: ProjectIDList); } else if (ProjectIDList != null) { Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualProjectIDList, out InProjectIDList); } } var cmdlist = new List <string>(); if (InProjectIDList != null && InProjectIDList.Count > 0) { foreach (var InID in InProjectIDList) { cmdlist.Add("([Project].AllParentID like '%," + InID + ",%' or ID=" + InID + ")"); } } if (EqualProjectIDList.Count > 0) { foreach (var EqualID in EqualProjectIDList) { cmdlist.Add("([Project].ID=" + EqualID + ")"); } } if (cmdlist.Count > 0) { var list = GetList <Project>("select ID from [Project] where " + string.Join(" or ", cmdlist.ToArray()), new List <SqlParameter>()); return(list.Select(p => p.ID).ToArray()); } return(new int[] { }); }
public static void GetHomeDataCount(int UserID, out int NormalSeatCount, out int InvalidSeatCount, out int ServiceCount, out int SuggestionCount, out int TotalInCallCount, out int TotalOutCallCount, out decimal TotalInCallMin, out decimal TotalOutCallMin) { NormalSeatCount = 0; InvalidSeatCount = 0; ServiceCount = 0; SuggestionCount = 0; TotalInCallCount = 0; TotalOutCallCount = 0; TotalInCallMin = 0; TotalOutCallMin = 0; string cmdtext = "select [PickUpTime],[HangUpTime],[PhoneType] from [PhoneRecord]"; var recordList = GetList <PhoneRecord>(cmdtext, new List <SqlParameter>()).ToArray(); using (SqlHelper helper = new SqlHelper()) { var parameters = new List <SqlParameter>(); cmdtext = "select count(1) from [Seat] where [DriverStatus]=1"; var result = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters); if (result != null) { int.TryParse(result.ToString(), out NormalSeatCount); } cmdtext = "select count(1) from [Seat] where [DriverStatus]=2"; result = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters); if (result != null) { int.TryParse(result.ToString(), out InvalidSeatCount); } string cmdwhere = " and (exists(select 1 from [UserServiceType] where [ServiceTypeID]=CustomerService.ServiceType1ID and ([UserID]=@UserID or exists(select 1 from [UserRoles] where [RoleID]=[UserServiceType].RoleID and [UserID]=@UserID))) or CustomerService.ServiceType1ID=0)"; parameters.Add(new SqlParameter("@UserID", UserID)); int BaoXiuServiceID = new Utility.SiteConfig().BaoXiuServiceID; cmdtext = "select ProjectID,ServiceType1ID from [CustomerService] where 1=1" + cmdwhere; var serviceList = GetList <CustomerService>(cmdtext, parameters).ToArray(); //result = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters); //if (result != null) //{ // int.TryParse(result.ToString(), out SuggestionCount); //} var EqualProjectIDList = new List <int>(); var InProjectIDList = new List <int>(); Project.GetMyProjectListByUserID(UserID, out EqualProjectIDList, out InProjectIDList); if (InProjectIDList.Count > 0 || EqualProjectIDList.Count > 0) { var myProjectIDList = Project.GetProjectIDListbyIDList(InProjectIDList: InProjectIDList, EqualProjectIDList: EqualProjectIDList); if (myProjectIDList.Length > 0) { serviceList = serviceList.Where(p => myProjectIDList.Contains(p.ProjectID)).ToArray(); } } SuggestionCount = serviceList.Where(p => p.ServiceType1ID != BaoXiuServiceID).ToArray().Length; ServiceCount = serviceList.Where(p => p.ServiceType1ID == BaoXiuServiceID).ToArray().Length; var totalInRecordList = recordList.Where(p => p.PhoneType == 1).ToArray(); TotalInCallCount = totalInRecordList.Length; TotalInCallMin = totalInRecordList.Sum(p => p.TotalCallHour); TotalInCallMin = Math.Round(TotalInCallMin, 2, MidpointRounding.AwayFromZero); var totalOutnRecordList = recordList.Where(p => p.PhoneType == 2).ToArray(); TotalOutCallCount = totalOutnRecordList.Length; TotalOutCallMin = totalOutnRecordList.Sum(p => p.TotalCallHour); TotalOutCallMin = Math.Round(TotalOutCallMin, 2, MidpointRounding.AwayFromZero); } }
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 ProjectTree[] GetProjectTreeListByID(int ID, string Keywords, int UserID, int IconID = 0, bool OnlyXiaoqu = false, bool ExceptRoom = false, int CompanyID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("[ID]>1"); if (ExceptRoom) { conditions.Add("[isParent]=1"); } if (CompanyID > 0) { conditions.Add("[CompanyID]=@CompanyID"); parameters.Add(new SqlParameter("@CompanyID", CompanyID)); } string sortorder = string.Empty; if (UserID > 0) { List <int> EqualIDList = new List <int>(); List <int> InIDList = new List <int>(); Project.GetMyProjectListByUserID(UserID, out EqualIDList, out InIDList); 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 + ")"); } } string cmdwhere = string.Empty; if (cmdlist.Count > 0) { conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } } if (OnlyXiaoqu) { conditions.Add("[ParentID]=1"); if (!string.IsNullOrEmpty(Keywords)) { parameters.Add(new SqlParameter("@LikeKeywords", "%" + Keywords + "%")); conditions.Add("([Name] like @LikeKeywords)"); } } else { if (!string.IsNullOrEmpty(Keywords)) { parameters.Add(new SqlParameter("@Keywords", Keywords)); parameters.Add(new SqlParameter("@LikeKeywords", "%" + Keywords + "%")); conditions.Add("[isParent]=0"); conditions.Add("([Name] like @LikeKeywords or [ID] in (select [RoomID] from [RoomBasic] where [RoomType] like @LikeKeywords) or [ID] in (select [RoomID] from [RoomPhoneRelation] where [RelationName] like @LikeKeywords or [RelatePhoneNumber] like @LikeKeywords))"); } else if (ID > 0) { conditions.Add("[ParentID]=@ID"); parameters.Add(new SqlParameter("@ID", ID)); } else { conditions.Add("[IconID]<=2"); } if (IconID > 0) { conditions.Add("[IconID]=@IconID"); parameters.Add(new SqlParameter("@IconID", IconID)); } } ProjectTree[] list = GetList <ProjectTree>("select " + ProjectColumns + " from [Project] where " + string.Join(" and ", conditions.ToArray()) + " order by DefaultOrder asc", parameters).ToArray(); return(list); }