public static Ui.DataGrid GetMall_UserPointGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, string orderBy, long startRowIndex, int pageSize)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("([UserID] in (select [UserID] from [Mall_UserPoint]) or [UserID] in (select [UserID] from [Mall_UserBalance]) or [UserID] in (select [UserID] from [Mall_CouponUser]))");
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([NickName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
string fieldList = "[User].* ";
string Statement = " from [User] where " + string.Join(" and ", conditions.ToArray());
var user_list = GetList <User>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
var user_level_list = Mall_UserLevel.GetMall_UserLevels();
List <int> UserIDList = user_list.Select(p => p.UserID).ToList();
var point_list = Mall_UserAccountDetail.GetMall_UserPointBalanceList(StartTime, EndTime);
var balance_list = Mall_UserAccountDetail.GetMall_UserAmountBalanceList(StartTime, EndTime);
var coupon_list = Mall_CouponUser.GetMall_CouponUserListByUserIDList(UserIDList);
if (StartTime > DateTime.MinValue)
{
coupon_list = coupon_list.Where(p => p.AddTime >= StartTime).ToArray();
}
if (EndTime > DateTime.MinValue)
{
coupon_list = coupon_list.Where(p => p.AddTime <= EndTime).ToArray();
}
var list = new List <Dictionary <string, object> >();
foreach (var user in user_list)
{
var my_user_level = user_level_list.FirstOrDefault(p => p.ID == user.UserLevelID);
var my_point_list = point_list.Where(p => p.UserID == user.UserID).ToArray();
var my_balance_list = balance_list.Where(p => p.UserID == user.UserID).ToArray();
var my_coupon_list = coupon_list.Where(p => p.UserID == user.UserID).ToArray();
decimal CurrentConsumeAmount = my_balance_list.Sum(p => p.Total);
decimal AllConsumeAmount = my_balance_list.Where(p => p.IncomeType == 1).Sum(p => p.Total);
decimal CurrentPoint = my_point_list.Sum(p => p.Total);
decimal AllPoint = my_point_list.Where(p => p.IncomeType == 1).Sum(p => p.Total);
decimal CurrentFuShunQuan = coupon_list.Where(p => !p.IsUsed).ToArray().Length;
decimal AllFuShunQuan = coupon_list.ToArray().Length;
var dic = new Dictionary <string, object>();
dic["NickName"] = string.IsNullOrEmpty(user.NickName) ? user.LoginName : user.NickName;
dic["MemberLevel"] = my_user_level == null ? "普通用户" : "合伙人用户(" + my_user_level.Name + ")";
dic["CurrentConsumeAmount"] = CurrentConsumeAmount;
dic["AllConsumeAmount"] = AllConsumeAmount;
dic["CurrentPoint"] = CurrentPoint;
dic["AllPoint"] = AllPoint;
dic["CurrentFuShunQuan"] = CurrentFuShunQuan;
dic["AllFuShunQuan"] = AllFuShunQuan;
list.Add(dic);
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = user_list.Length;
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);
}