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);
}