public static Wechat_ContactDetail[] GetWechat_ContactDetailListByMsgPhoneType(string PhoneType, List <int> ProjectIDList = null, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> conditions2 = new List <string>();
conditions.Add("1=1");
conditions2.Add("1=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
if (EqualIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
cmdlist.Add("exists (select 1 from [Wechat_ContactProject] where [WechatContactID]=Wechat_Contact.ID and [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + "))");
cmdlist.Add("exists (select 1 from [Wechat_ContactProject] where [WechatContactID]=Wechat_Contact.ID and not exists (select 1 from Project where ID=Wechat_ContactProject.[ProjectID] and ID!=1))");
cmdlist.Add("not exists (select 1 from [Wechat_ContactProject] where [WechatContactID]=Wechat_Contact.ID and [ProjectID]!=1)");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
}
if (!string.IsNullOrEmpty(PhoneType))
{
conditions.Add("[PhoneType]=@PhoneType");
conditions2.Add("[PhoneType]=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
}
else
{
conditions.Add("isnull([PhoneType],'')!=@PhoneType");
conditions2.Add("isnull([PhoneType],'')!=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", Utility.EnumModel.WechatContactType.hujiaowuye.ToString()));
}
string cmdtext = "select * from [Wechat_Contact] where " + string.Join(" and ", conditions.ToArray()) + " order by ID desc";
var list = GetList <Wechat_ContactDetail>(cmdtext, parameters).ToArray();
if (list.Length == 0)
{
list = GetList <Wechat_ContactDetail>("select * from [Wechat_Contact] where " + string.Join(" and ", conditions2.ToArray()) + " order by ID desc", parameters).ToArray();
}
if (list.Length > 0)
{
var categorys = Wechat_ContactCategory.GetWechat_ContactCategories();
foreach (var item in list)
{
if (item.PhoneType.Equals(Utility.EnumModel.WechatContactType.hujiaowuye.ToString()))
{
item.CategoryName = "呼叫物业";
}
else
{
var my_category = categorys.FirstOrDefault(p => p.ID == item.CategoryID);
item.CategoryName = my_category != null ? my_category.Name : string.Empty;
}
}
}
return(list);
}
public static Wechat_Msg[] GetWechat_MsgListByMsgType(string MsgType, long startRowIndex, int pageSize, out long totalRows, bool IsWechatSend = false, bool IsUserAPPSend = false, bool IsCustomerAPPSend = false, bool IsBusinessAPPSend = false, int CategoryID = 0, List <int> ProjectIDList = null, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
string orderby = "order by [IsTopShow] desc ,isnull([SortOrder],999999) asc,[PublishTime] desc";
conditions.Add("[IsShow]=1");
conditions.Add("(isnull([StartTime],getdate())<=getdate() and isnull([EndTime],getdate())>=getdate())");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
if (EqualIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
cmdlist.Add("exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + "))");
cmdlist.Add("exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and not exists (select 1 from Project where ID=Wechat_MsgProject.[ProjectID] and ID!=1))");
cmdlist.Add("not exists (select 1 from [Wechat_MsgProject] where [MsgID]=Wechat_Msg.ID and [ProjectID]!=1)");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
}
if (CategoryID > 0)
{
conditions.Add("[CategoryID]=@CategoryID");
parameters.Add(new SqlParameter("@CategoryID", CategoryID));
}
if (!string.IsNullOrEmpty(MsgType))
{
conditions.Add("[MsgType]=@MsgType");
parameters.Add(new SqlParameter("@MsgType", MsgType));
}
if (IsWechatSend)
{
conditions.Add("[IsWechatSend]=1");
}
if (IsUserAPPSend)
{
conditions.Add("[IsUserAPPSend]=1");
}
if (IsCustomerAPPSend)
{
conditions.Add("[IsCustomerAPPSend]=1");
}
if (IsBusinessAPPSend)
{
conditions.Add("[IsBusinessAPPSend]=1");
}
string fieldList = "[Wechat_Msg].*";
string Statement = " from [Wechat_Msg] where " + string.Join(" and ", conditions.ToArray());
var list = GetList <Wechat_Msg>(fieldList, Statement, parameters, orderby, startRowIndex, pageSize, out totalRows).ToArray();
return(list);
}
public static User[] GetUserListByIDListByServiceProjectID(List <int> UserIDList, int ProjectID, bool CheckDeviceID = true, int DepartmentID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
if (DepartmentID > 0)
{
conditions.Add("[UserID] in (select [UserID] from [UserDepartment] where [DepartmentID]=@DepartmentID)");
parameters.Add(new SqlParameter("@DepartmentID", DepartmentID));
}
if (CheckDeviceID)
{
conditions.Add("isnull([APPUserDeviceID],'')!=''");
}
conditions.Add("([Type]=@Type or [IsAllowAPPUserLogin]=1)");
parameters.Add(new SqlParameter("@Type", UserTypeDefine.APPUser.ToString()));
if (UserIDList.Count > 0)
{
conditions.Add("[UserID] in (" + string.Join(",", UserIDList.ToArray()) + ")");
}
if (ProjectID > 0)
{
List <int> ProjectIDList = new List <int>();
ProjectIDList.Add(ProjectID);
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
Project.GetMyProjectListByProjectIDList(ProjectIDList, 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("([UserID] in (select [UserID] from [UserRoles] where [RoleID] in (select [RoleID] from [RoleProject] where [ProjectID] in (select ID from [Project] where " + string.Join(" or ", cmdlist.ToArray()) + "))) or [UserID] in (select [UserID] from [RoleProject] where [ProjectID] in (select ID from [Project] where " + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
}
string sqlText = "select * from [User] where " + string.Join(" and ", conditions.ToArray());
return(GetList <User>(sqlText, parameters).ToArray());
}
public static Wechat_Contact[] GetWechat_ContactListByMsgPhoneType(string PhoneType, List <int> ProjectIDList = null, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
List <string> conditions2 = new List <string>();
conditions.Add("1=1");
conditions2.Add("1=1");
if (ProjectIDList != null && ProjectIDList.Count > 0)
{
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
if (EqualIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(EqualIDList, IncludeRelation: false, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("([ID] in (select [WechatContactID] from [Wechat_ContactProject] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
}
if (!string.IsNullOrEmpty(PhoneType))
{
conditions.Add("[PhoneType]=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
conditions2.Add("[PhoneType]=@PhoneType");
}
else
{
conditions.Add("isnull([PhoneType],'')!=@PhoneType");
parameters.Add(new SqlParameter("@PhoneType", PhoneType));
conditions2.Add("isnull([PhoneType],'')!=@PhoneType");
}
string cmdtext = "select * from [Wechat_Contact] where " + string.Join(" and ", conditions.ToArray()) + " order by ID desc";
var list = GetList <Wechat_Contact>(cmdtext, parameters).ToArray();
if (list.Length > 0)
{
return(list);
}
return(GetList <Wechat_Contact>("select * from [Wechat_Contact] where " + string.Join(" and ", conditions2.ToArray()) + " order by ID desc", parameters).ToArray());
}
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 Ui.DataGrid GetMall_DoorCardAnalysisGridByKeywords(string keywords, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime)
{
long totalRows = 0;
string OrderBy = " order by [Title] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[AddTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[AddTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([CardName] like @keywords or [CardSummary] like @keywords or [CardUID] like @keywords or [DoorNumber] like @keywords or ([ID] in (select [DoorCardID] from [Mall_DoorCardDevice] where [DoorDeviceID] in (select [DeviceID] from [Mall_DoorDevice] where [DeviceName] like @keywords))))");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
if (ProjectIDList.Count > 0 && RoomIDList.Count == 0)
{
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
}
List <string> cmdlist = new List <string>();
if (RoomIDList.Count > 0)
{
conditions.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].[RoomPhoneRelationID] and [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
}
if (EqualIDList.Count > 0)
{
cmdlist.Add("[ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ")");
cmdlist.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].[RoomPhoneRelationID] and exists(select 1 from [Project] where ID=[RoomPhoneRelation].[RoomID] and [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ")))");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (InIDList.Count > 0)
{
cmdlist = new List <string>();
foreach (var InID in InIDList)
{
cmdlist.Add("([ProjectID] in (select ID from [Project] where AllParentID like '%," + InID.ToString() + ",%'))");
cmdlist.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].[RoomPhoneRelationID] and exists(select 1 from [Project] where ID=[RoomPhoneRelation].[RoomID] and AllParentID like '%," + InID.ToString() + ",%'))");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string fieldList = "A.*";
string Statement = " from (select Convert(nvarchar(10),AddTime,120) as Title,count(1) as Total from Mall_DoorCard where " + string.Join(" and ", conditions.ToArray()) + " group by Convert(nvarchar(10),AddTime,120))A";
Mall_DoorCardAnalysis[] list = GetList <Mall_DoorCardAnalysis>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetMall_DoorCardDetailGridByKeywords(string keywords, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime)
{
long totalRows = 0;
string OrderBy = " order by [CardName] asc, [SortOrder] asc, [AddTime] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[AddTime],120)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(nvarchar(10),[AddTime],120)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([CardName] like @keywords or [CardSummary] like @keywords or [CardUID] like @keywords or [DoorNumber] like @keywords or ([ID] in (select [DoorCardID] from [Mall_DoorCardDevice] where [DoorDeviceID] in (select [DeviceID] from [Mall_DoorDevice] where [DeviceName] like @keywords))))");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
if (ProjectIDList.Count > 0 && RoomIDList.Count == 0)
{
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
}
List <string> cmdlist = new List <string>();
if (RoomIDList.Count > 0)
{
conditions.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[A].[RoomPhoneRelationID] and[RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
}
if (EqualIDList.Count > 0)
{
cmdlist.Add("[ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ")");
cmdlist.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[A].[RoomPhoneRelationID] and exists(select 1 from [Project] where ID=[RoomPhoneRelation].[RoomID] and [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ")))");
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (InIDList.Count > 0)
{
cmdlist = new List <string>();
foreach (var InID in InIDList)
{
cmdlist.Add("([ProjectID] in (select ID from [Project] where AllParentID like '%," + InID.ToString() + ",%'))");
cmdlist.Add("exists(select 1 from [RoomPhoneRelation] where [ID]=[A].[RoomPhoneRelationID] and exists(select 1 from [Project] where ID=[RoomPhoneRelation].[RoomID] and AllParentID like '%," + InID.ToString() + ",%'))");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string fieldList = "[A].*";
string Statement = " from (select *,(select top 1 [StartTime] from [RoomFeeHistory] where [ChargeID] in (select ID from [ChargeSummary] where [ChargeFeeType]=1) and ChargeState in (1,4) and [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].RoomPhoneRelationID) order by ChargeTime desc) as StartTime,(select top 1 [EndTime] from [RoomFeeHistory] where [ChargeID] in (select ID from [ChargeSummary] where [ChargeFeeType]=1) and ChargeState in (1,4) and [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].RoomPhoneRelationID) order by ChargeTime desc) as EndTime,(select top 1 [StartTime] from [RoomFee] where [ChargeID] in (select ID from [ChargeSummary] where [ChargeFeeType]=1) and [RoomID] in (select [RoomID] from [RoomPhoneRelation] where [ID]=[Mall_DoorCard].RoomPhoneRelationID) and StartTime is not null order by StartTime asc) as FeeStartTime from [Mall_DoorCard])A where " + string.Join(" and ", conditions.ToArray());
Mall_DoorCardDetail[] list = GetList <Mall_DoorCardDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
var mList = list.Where(p => p.UserID > 0).ToArray();
int MinUserID = 0;
int MaxUserID = 0;
if (mList.Length > 0)
{
MinUserID = mList.Min(p => p.UserID);
MaxUserID = mList.Max(p => p.UserID);
}
var userList = User.GetSysUserList(IncludeLock: false, MinUserID: MinUserID, MaxUserID: MaxUserID);
List <int> card_idlist = list.Select(p => p.ID).ToList();
var card_device_list = Mall_DoorCardDevice.GetMall_DoorCardDevices().Where(p => card_idlist.Contains(p.DoorCardID)).ToArray();
var device_list = Mall_DoorDevice.GetALLActiveMall_DoorDeviceList().ToArray();
mList = list.Where(p => p.RoomPhoneRelationID > 0).ToArray();
int MinID = 0;
int MaxID = 0;
if (mList.Length > 0)
{
MinID = mList.Min(p => p.RoomPhoneRelationID);
MaxID = mList.Max(p => p.RoomPhoneRelationID);
}
var relationList = RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxID(MinID, MaxID);
int MinRoomID = 0;
int MaxRoomID = 0;
if (relationList.Length > 0)
{
MinRoomID = relationList.Min(p => p.RoomID);
MaxRoomID = relationList.Max(p => p.RoomID);
}
var projectList = Project.GetProjectListByMinMaxID(MinRoomID, MaxRoomID);
foreach (var item in list)
{
var my_card_device_list = card_device_list.Where(p => p.DoorCardID == item.ID).ToArray();
List <int> my_deviceidlist = my_card_device_list.Select(p => p.DoorDeviceID).ToList();
var my_device_list = device_list.Where(p => my_deviceidlist.Contains(p.DeviceID)).ToArray();
if (my_device_list.Length > 0)
{
item.DeviceInfo = string.Join(",", my_device_list.Select(p =>
{
return("【" + p.DeviceID + "," + p.DeviceName + "】");
}).ToArray());
}
var myRelation = relationList.FirstOrDefault(p => p.ID == item.RoomPhoneRelationID);
if (myRelation != null)
{
var myProject = projectList.FirstOrDefault(p => p.ID == myRelation.RoomID);
item.UserInfo = myRelation.RelationName;
if (myProject != null)
{
item.UserInfo += "【" + myProject.FullName + "-" + myProject.Name + "】";
}
}
var myUser = userList.FirstOrDefault(q => q.LoginName == item.AddUserName);
if (myUser != null)
{
item.AddUserName = myUser.FinalRealName;
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetMall_DoorDeviceGridByKeywords(string keywords, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList)
{
UpdateMall_DoorDeviceOnLineStatus();
long totalRows = 0;
string OrderBy = " order by [SortOrder] asc, AddTime desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("isnull([IsDelete],0)=0");
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([DeviceName] like @keywords or [DeviceCode] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
if (RoomIDList.Count > 0)
{
Project.GetMyProjectListByProjectIDList(RoomIDList, out EqualIDList, out InIDList);
}
else if (ProjectIDList.Count > 0)
{
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
}
List <string> cmdlist = new List <string>();
if (EqualIDList.Count > 0)
{
EqualIDList.Add(0);
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ") and [IsUseAll]=1)");
cmdlist.Add("[ID] in (select [DoorDeviceID] from [Mall_DoorDeviceProject] where [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + "))");
}
if (InIDList.Count > 0)
{
List <string> subcmdlist = new List <string>();
foreach (var InID in InIDList)
{
subcmdlist.Add("(ID=" + InID + " or AllParentID like '%," + InID.ToString() + ",%')");
}
cmdlist.Add("([ProjectID] in (select ID from [Project] where " + string.Join(" or ", subcmdlist.ToArray()) + ") and [IsUseAll]=1)");
cmdlist.Add("[ID] in (select [DoorDeviceID] from [Mall_DoorDeviceProject] where [ProjectID] in (select ID from [Project] where " + string.Join(" or ", subcmdlist.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
string fieldList = "[Mall_DoorDevice].*";
string Statement = " from [Mall_DoorDevice] where " + string.Join(" and ", conditions.ToArray());
Mall_DoorDevice[] list = GetList <Mall_DoorDevice>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
var device_project_list = Mall_DoorDeviceProject.GetMall_DoorDeviceProjectListByDeviceIDList(list.Select(p => p.ID).ToList());
var project_list = Project.GetMall_DoorDeviceProjectListByDoorDeviceIDList(list.Select(p => p.ID).ToList());
foreach (var item in list)
{
if (item.IsUseAll)
{
item.BindDeviceName = "所有楼栋";
}
else
{
var my_device_project_list = device_project_list.Where(p => p.DoorDeviceID == item.ID).ToArray();
List <int> device_project_idlist = my_device_project_list.Select(p => p.ProjectID).ToList();
var my_project_list = project_list.Where(p => device_project_idlist.Contains(p.ID));
List <string> my_project_names = my_project_list.Select(p =>
{
string result = string.Empty;
if (p.isParent)
{
result = p.FullName;
}
else
{
result = p.FullName + p.Name;
}
return(result);
}).ToList();
if (my_project_names.Count > 0)
{
item.BindDeviceName = string.Join(",", my_project_names.ToArray());
}
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetMall_DoorOpenLogDataGrid(string keywords, long startRowIndex, int pageSize, List <int> ProjectIDList, List <int> RoomIDList, DateTime StartTime, DateTime EndTime, bool canexport = false)
{
if (startRowIndex <= 1 && !canexport)
{
GetRemoteMallDoorLog(pageSize);
}
long totalRows = 0;
string OrderBy = " order by [OpenTime] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("[DeviceName] like @keywords");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
List <string> cmdlist = new List <string>();
List <int> EqualIDList = new List <int>();
List <int> InIDList = new List <int>();
if (RoomIDList.Count > 0)
{
Project.GetMyProjectListByProjectIDList(RoomIDList, out EqualIDList, out InIDList);
}
else if (ProjectIDList.Count > 0)
{
Project.GetMyProjectListByProjectIDList(ProjectIDList, out EqualIDList, out InIDList);
}
if (EqualIDList.Count > 0)
{
EqualIDList.Add(0);
cmdlist.Add("([ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + ") and [IsUseAll]=1)");
cmdlist.Add("[Device_ID] in (select [DoorDeviceID] from [Mall_DoorDeviceProject] where [ProjectID] in (" + string.Join(",", EqualIDList.ToArray()) + "))");
}
if (InIDList.Count > 0)
{
List <string> subcmdlist = new List <string>();
foreach (var InID in InIDList)
{
subcmdlist.Add("AllParentID like '%," + InID.ToString() + ",%'");
}
cmdlist.Add("([ProjectID] in (select ID from [Project] where " + string.Join(" or ", subcmdlist.ToArray()) + ") and [IsUseAll]=1)");
cmdlist.Add("[Device_ID] in (select [DoorDeviceID] from [Mall_DoorDeviceProject] where [ProjectID] in (select ID from [Project] where " + string.Join(" or ", subcmdlist.ToArray()) + "))");
}
if (cmdlist.Count > 0)
{
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("OpenTime>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("OpenTime<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
string fieldList = "A.*";
string Statement = " from (select Mall_DoorOpenLog.*,Mall_DoorDevice.[DeviceName],Mall_DoorDevice.ID as Device_ID,[Mall_DoorDevice].ProjectID,[Mall_DoorDevice].IsUseAll from [Mall_DoorOpenLog] left join [Mall_DoorDevice] on Mall_DoorDevice.DeviceID=Mall_DoorOpenLog.DeviceID)A where " + string.Join(" and ", conditions.ToArray());
Mall_DoorOpenLogDetail[] list = new Mall_DoorOpenLogDetail[] { };;
if (canexport)
{
list = GetList <Mall_DoorOpenLogDetail>("select " + fieldList + Statement + " " + OrderBy, parameters).ToArray();
totalRows = list.Length;
}
else
{
list = GetList <Mall_DoorOpenLogDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}