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