public static Ui.DataGrid GetCKDepartmentGridByKeywords(string Keywords, string orderBy)
{
var list = CKDepartment.GetCKDepartments().ToArray();
if (list.Length > 0)
{
foreach (var item in list)
{
var myParentData = list.FirstOrDefault(p => p.ID == item.ParentID);
if (myParentData != null)
{
item.ParentName = myParentData.DepartmentName;
}
}
}
#region 关键字查询
string cmd = string.Empty;
CKDepartment[] dataList = new CKDepartment[] { };
if (!string.IsNullOrEmpty(Keywords))
{
dataList = list.Where(p => p.DepartmentName.Contains(Keywords)).ToArray();
}
#endregion
dataList = dataList.OrderBy(p => p.SortOrder).ThenBy(p => p.ID).ToArray();
var items = list.Select(p =>
{
var type = "department";
var dic = new Dictionary <string, object>();
dic["type"] = type;
dic["ID"] = p.ID;
dic["ParentID"] = p.ParentID;
dic["CompanyID"] = p.CompanyID > 0 ? p.CompanyID : 0;
dic["id"] = type + "_" + p.ID;
dic["name"] = p.DepartmentName;
dic["_parentId"] = p.ParentID <= 1 ? "company_" + p.CompanyID : p.ParentID.ToString();
var childItems = list.Where(q => q.ParentID == p.ID).ToArray();
if (childItems.Length > 0)
{
dic["state"] = "closed";
}
return(dic);
}).ToList();
var companys = Company.GetCompanies().ToArray();
var companyItems = companys.Select(p =>
{
var type = "company";
var dic = new Dictionary <string, object>();
dic["type"] = type;
dic["ID"] = p.CompanyID;
dic["ParentID"] = 0;
dic["CompanyID"] = p.CompanyID > 0 ? p.CompanyID : 0;
dic["id"] = type + "_" + p.CompanyID;
dic["name"] = p.CompanyName;
dic["_parentId"] = 0;
return(dic);
}).ToList();
items.AddRange(companyItems);
Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = items;
dg.total = items.Count;
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, 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 Company GetCompanyBySystenNumber(string SystenNumber)
{
var list = Company.GetCompanies().ToArray();
return(list.FirstOrDefault(p => p.SystemNumber.Equals(SystenNumber)));
}