public static Ui.DataGrid GetUserDetailGridByKeywords(string Keywords, int CompanyID, string UserType, string orderBy, long startRowIndex, int pageSize, int UserRoomType, bool IsAPPCustomer, bool IsAPPCustomerFamily, bool IsAPPBusiness, int BusinessID, int ParentUserID, bool IsAPPUser, Company company, bool can_export = false, int UserID = 0, int DepartmentID = 0, bool IsAPPCustomerAndUser = false, int DepartmentCompanyID = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (DepartmentCompanyID > 0)
{
conditions.Add("exists(select 1 from [UserDepartment] where [UserID]=A.UserID and exists(select 1 from [CKDepartment] where [CompanyID]=@DepartmentCompanyID and [ID]=[UserDepartment].DepartmentID))");
parameters.Add(new SqlParameter("@DepartmentCompanyID", DepartmentCompanyID));
}
if (DepartmentID > 0)
{
conditions.Add("exists(select 1 from [UserDepartment] where [UserID]=A.[UserID] and DepartmentID=@DepartmentID)");
parameters.Add(new SqlParameter("@DepartmentID", DepartmentID));
}
if (ParentUserID > 0)
{
conditions.Add("[ParentUserID]=@ParentUserID");
parameters.Add(new SqlParameter("@ParentUserID", ParentUserID));
}
if (UserRoomType == 1)
{
conditions.Add("[UserID] in (select [UserID] from [Mall_UserProject] where isnull([IsDisable],0)=0)");
conditions.Add(" [Type]=@UserType");
parameters.Add(new SqlParameter("@UserType", UserTypeDefine.APPCustomer.ToString()));
}
else if (UserRoomType == 2)
{
conditions.Add("[UserID] not in (select [UserID] from [Mall_UserProject] where isnull([IsDisable],0)=0)");
conditions.Add("[Type]=@UserType");
parameters.Add(new SqlParameter("@UserType", UserTypeDefine.APPCustomer.ToString()));
}
else if (UserRoomType == 3)
{
conditions.Add("[Type]=@UserType");
parameters.Add(new SqlParameter("@UserType", UserTypeDefine.APPUser.ToString()));
}
if (IsAPPCustomer)
{
conditions.Add("[Type]=@UserType1");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPCustomer.ToString()));
}
else if (IsAPPCustomerFamily)
{
conditions.Add("[Type]=@UserType1");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPCustomerFamily.ToString()));
}
else if (IsAPPUser)
{
conditions.Add("([Type]=@UserType1 or [IsAllowAPPUserLogin]=1)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPUser.ToString()));
}
else if (IsAPPBusiness)
{
conditions.Add("[Type]=@UserType1");
conditions.Add("([UserID] in (select [UserID] from [Mall_BusinessUser] where BusinessID=@BusinessID) or [UserID] in (select [UserID] from [Mall_Business] where ID=@BusinessID))");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPBusiness.ToString()));
parameters.Add(new SqlParameter("@BusinessID", BusinessID));
}
else if (IsAPPCustomerAndUser)
{
conditions.Add("([Type]=@UserType1 or [Type]=@UserType2)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPCustomer.ToString()));
parameters.Add(new SqlParameter("@UserType2", UserTypeDefine.APPUser.ToString()));
}
else if (!string.IsNullOrEmpty(UserType))
{
conditions.Add("[Type]=@UserType1");
parameters.Add(new SqlParameter("@UserType1", UserType));
}
else
{
conditions.Add("([Type]=@UserType1 or [Type]=@UserType2)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.SystemUser.ToString()));
parameters.Add(new SqlParameter("@UserType2", UserTypeDefine.APPUser.ToString()));
}
if (!string.IsNullOrEmpty(Keywords))
{
if (IsAPPCustomer)
{
conditions.Add("([LoginName] like @Keywords or [PhoneNumber] like @Keywords or [RealName] like @Keywords or [UserID] in (select [ParentUserID] from [User] where [LoginName] like @Keywords))");
}
else
{
conditions.Add("([LoginName] like @Keywords or [PhoneNumber] like @Keywords or [RealName] like @Keywords)");
}
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string Statement_Part2 = "";
if (IsAPPCustomer || IsAPPCustomerFamily || IsAPPUser)
{
Statement_Part2 += ",(select count(1) from [Mall_UserProject] where [UserID]=[User].[UserID] and isnull([IsDisable],0)=0) as UserRoomType";
}
if (CompanyID > 0)
{
conditions.Add("[UserID] in (select [UserID] from [UserCompany] where [CompanyID]=@CompanyID)");
parameters.Add(new SqlParameter("@CompanyID", CompanyID));
Statement_Part2 += ",(select top 1 [CompanyName] from [Company] where [CompanyID] in (select [CompanyID] from [UserCompany] where [UserID]=[User].[UserID])) as CompanyName,(select top 1 [BaseURL] from [Company] where [CompanyID] in (select [CompanyID] from [UserCompany] where [UserID]=[User].[UserID])) as BaseURL";
}
string fieldList = "A.*";
string Statement = " from (select [User].*" + Statement_Part2 + " from [User]) A where " + string.Join(" and ", conditions.ToArray());
UserDetail[] list = new UserDetail[] { };
if (can_export)
{
list = GetList <UserDetail>("select * " + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <UserDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
if (list.Length > 0)
{
var companys = Company.GetCompanies().ToArray();
var departmentList = CKDepartment.GetCKDepartments().ToArray();
int MinUserID = list.Min(p => p.UserID);
int MaxUserID = list.Max(p => p.UserID);
var userDepartmentList = UserDepartment.GetUserDepartmentListByMinMaxUserID(MinUserID, MaxUserID);
foreach (var item in list)
{
var myUserDepartmentIDList = userDepartmentList.Where(p => p.UserID == item.UserID).Select(p => p.DepartmentID).ToArray();
var my_department = departmentList.FirstOrDefault(p => myUserDepartmentIDList.Contains(p.ID));
if (my_department != null)
{
item.DepartmentName = my_department.DepartmentName;
var myCompany = companys.FirstOrDefault(p => p.CompanyID == my_department.CompanyID);
if (myCompany != null)
{
item.CompanyName = myCompany.CompanyName;
}
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetUserDetailGridByKeywords(string Keywords, int CompanyID, string UserType, string orderBy, long startRowIndex, int pageSize, int UserRoomType, bool IsAPPCustomer, bool IsAPPCustomerFamily, bool IsAPPBusiness, int BusinessID, int ParentUserID, bool IsAPPUser, Company company, bool can_export = false, int UserID = 0, int DepartmentID = 0, bool IsAPPCustomerAndUser = false, List <int> ProjectIDList = null, List <int> RoomIDList = null)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (DepartmentID > 0)
{
conditions.Add("exists(select 1 from [UserDepartment] where [UserID]=A.[UserID] and DepartmentID=@DepartmentID)");
parameters.Add(new SqlParameter("@DepartmentID", DepartmentID));
}
if (ParentUserID > 0)
{
conditions.Add("[ParentUserID]=@ParentUserID");
parameters.Add(new SqlParameter("@ParentUserID", ParentUserID));
}
if (UserRoomType == 1)
{
conditions.Add("[UserID] in (select [UserID] from [Mall_UserProject] where isnull([IsDisable],0)=0)");
conditions.Add(" [Type]=@UserType");
parameters.Add(new SqlParameter("@UserType", UserTypeDefine.APPCustomer.ToString()));
}
else if (UserRoomType == 2)
{
conditions.Add("[UserID] not in (select [UserID] from [Mall_UserProject] where isnull([IsDisable],0)=0)");
conditions.Add("[Type]=@UserType");
parameters.Add(new SqlParameter("@UserType", UserTypeDefine.APPCustomer.ToString()));
}
else if (UserRoomType == 3)
{
conditions.Add("[Type]=@UserType");
parameters.Add(new SqlParameter("@UserType", UserTypeDefine.APPUser.ToString()));
}
if (IsAPPCustomer)
{
conditions.Add("[Type]=@UserType1");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPCustomer.ToString()));
}
else if (IsAPPCustomerFamily)
{
conditions.Add("[Type]=@UserType1");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPCustomerFamily.ToString()));
}
else if (IsAPPUser)
{
conditions.Add("([Type]=@UserType1 or [IsAllowAPPUserLogin]=1)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPUser.ToString()));
}
else if (IsAPPBusiness)
{
conditions.Add("[Type]=@UserType1");
conditions.Add("([UserID] in (select [UserID] from [Mall_BusinessUser] where BusinessID=@BusinessID) or [UserID] in (select [UserID] from [Mall_Business] where ID=@BusinessID))");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPBusiness.ToString()));
parameters.Add(new SqlParameter("@BusinessID", BusinessID));
}
else if (IsAPPCustomerAndUser)
{
conditions.Add("([Type]=@UserType1 or [Type]=@UserType2)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.APPCustomer.ToString()));
parameters.Add(new SqlParameter("@UserType2", UserTypeDefine.APPUser.ToString()));
}
else if (!string.IsNullOrEmpty(UserType))
{
conditions.Add("[Type]=@UserType1");
parameters.Add(new SqlParameter("@UserType1", UserType));
}
else
{
conditions.Add("([Type]=@UserType1 or [Type]=@UserType2)");
parameters.Add(new SqlParameter("@UserType1", UserTypeDefine.SystemUser.ToString()));
parameters.Add(new SqlParameter("@UserType2", UserTypeDefine.APPUser.ToString()));
}
if (!string.IsNullOrEmpty(Keywords))
{
if (IsAPPCustomer)
{
conditions.Add("([LoginName] like @Keywords or [PhoneNumber] like @Keywords or [RealName] like @Keywords or [UserID] in (select [ParentUserID] from [User] where [LoginName] like @Keywords))");
}
else
{
conditions.Add("([LoginName] like @Keywords or [PhoneNumber] like @Keywords or [RealName] like @Keywords)");
}
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string Statement_Part2 = "";
if (IsAPPCustomer || IsAPPCustomerFamily || IsAPPUser)
{
Statement_Part2 += ",(select count(1) from [Mall_UserProject] where [UserID]=[User].[UserID] and isnull([IsDisable],0)=0) as UserRoomType";
}
if (CompanyID > 0)
{
conditions.Add("[UserID] in (select [UserID] from [UserCompany] where [CompanyID]=@CompanyID)");
parameters.Add(new SqlParameter("@CompanyID", CompanyID));
Statement_Part2 += ",(select top 1 [CompanyName] from [Company] where [CompanyID] in (select [CompanyID] from [UserCompany] where [UserID]=[User].[UserID])) as CompanyName,(select top 1 [BaseURL] from [Company] where [CompanyID] in (select [CompanyID] from [UserCompany] where [UserID]=[User].[UserID])) as BaseURL";
}
if (IsAPPCustomer || IsAPPCustomerFamily || IsAPPCustomerAndUser)
{
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
var cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("[AllParentID] like '%," + ProjectID + ",%' or ID=" + ProjectID);
}
conditions.Add("exists(select 1 from [Mall_UserProject] where [UserID]=A.UserID and exists(select 1 from [Project] where [ID]=[Mall_UserProject].[ProjectID] and (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
if (RoomIDList != null && RoomIDList.Count > 0)
{
conditions.Add("exists(select 1 from [Mall_UserProject] where [UserID]=A.UserID and [ProjectID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
}
}
string fieldList = "A.*";
string Statement = " from (select [User].*" + Statement_Part2 + " from [User]) A where " + string.Join(" and ", conditions.ToArray());
UserDetail[] list = new UserDetail[] { };
if (can_export)
{
list = GetList <UserDetail>("select * " + Statement + " " + orderBy, parameters).ToArray();
}
else
{
list = GetList <UserDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
if (list.Length > 0)
{
var departmentList = CKDepartment.GetCKDepartments().ToArray();
int MinUserID = list.Min(p => p.UserID);
int MaxUserID = list.Max(p => p.UserID);
var userDepartmentList = UserDepartment.GetUserDepartmentListByMinMaxUserID(MinUserID, MaxUserID);
if (IsAPPCustomer || IsAPPCustomerFamily || IsAPPCustomerAndUser)
{
List <int> UserIDList = list.Select(p => p.UserID).ToList();
var project_list = Foresight.DataAccess.Project.GetProjectListByAPPUserIDList(UserIDList, UserID: UserID);
var user_project_list = Foresight.DataAccess.Mall_UserProject.GetMall_UserProjectListByUserIDList(UserIDList, UserID: UserID);
var point_list = Mall_UserAccountDetail.GetMall_UserPointBalanceList();
var balance_list = Mall_UserAccountDetail.GetMall_UserAmountBalanceList();
foreach (var item in list)
{
var my_user_projectid_list = user_project_list.Where(p => p.UserID == item.UserID).Select(p => p.ProjectID).ToList();
var my_project_list = project_list.Where(p => my_user_projectid_list.Contains(p.ID)).ToArray();
if (my_project_list.Length > 0)
{
item.UserRoomDesc = my_project_list.Select(p => p.FullName + "-" + p.Name).ToList();
}
item.HeadImg = string.IsNullOrEmpty(item.HeadImg) ? "../styles/images/error-img.png" : Utility.Tools.GetContextPath() + item.HeadImg;
var my_department = departmentList.FirstOrDefault(p => p.ID == item.DepartmentID);
if (my_department != null)
{
item.DepartmentName = my_department.DepartmentName;
}
item.AmountBanalce = balance_list.Where(p => p.UserID == item.UserID).Sum(p => p.Total);
item.PointBalance = point_list.Where(p => p.UserID == item.UserID).Sum(p => p.Total);
}
}
else
{
foreach (var item in list)
{
var myUserDepartmentIDList = userDepartmentList.Where(p => p.UserID == item.UserID).Select(p => p.DepartmentID).ToArray();
var my_department = departmentList.FirstOrDefault(p => myUserDepartmentIDList.Contains(p.ID));
if (my_department != null)
{
item.DepartmentName = my_department.DepartmentName;
}
if (company != null)
{
item.SystemNo = company.SystemNo;
if (string.IsNullOrEmpty(item.BaseURL) || string.IsNullOrEmpty(company.BaseURL))
{
continue;
}
if (item.BaseURL.ToLower().Equals(company.BaseURL.ToLower()))
{
item.CompanyName = company.CompanyName;
}
}
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}