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 GetRoomPhoneRelationGridByRoomID(List <int> ProjectIDList, List <int> RoomIDList, string RelationType, string orderBy, long startRowIndex, int pageSize, bool IsContractUser, bool IsProjectUser)
{
long totalRows = 0;
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (IsContractUser)
{
conditions.Add("[ContractID]>0");
}
else if (IsProjectUser)
{
conditions.Add("([ContractID]=0 or [ContractID] is null)");
}
else
{
dg.rows = new int[] { };
dg.total = 0;
dg.page = pageSize;
return(dg);
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [RoomID] =" + ProjectID + ")");
}
conditions.Add("exists(select 1 from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + ") and ([Project].ID=[RoomPhoneRelation].[RoomID] or exists(select 1 from [RoomPhoneRelation_Connect] where RoomPhoneRelation_Connect.[RoomPhoneRelationID]=[RoomPhoneRelation].ID and [Project].ID=RoomPhoneRelation_Connect.RoomID)))");
}
if (RoomIDList.Count > 0)
{
conditions.Add("([RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + ") or exists(select 1 from [RoomPhoneRelation_Connect] where RoomPhoneRelationID=RoomPhoneRelation.ID and [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + ")))");
}
if (!string.IsNullOrEmpty(RelationType))
{
conditions.Add("([RelationType]=@RelationType or [RelationType] is null)");
parameters.Add(new SqlParameter("@RelationType", RelationType));
}
string fieldList = "[RoomPhoneRelation].* ";
string Statement = " from [RoomPhoneRelation] where " + string.Join(" and ", conditions.ToArray());
RoomPhoneRelationDetail[] list = new RoomPhoneRelationDetail[] { };
list = GetList <RoomPhoneRelationDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
int MinRoomID = list.Min(p => p.RoomID);
int MaxRoomID = list.Max(p => p.RoomID);
var projectList = Project.GetProjectListByMinMaxID(MinRoomID, MaxRoomID);
int MinContracID = list.Min(p => p.ContractID);
int MaxContractID = list.Max(p => p.ContractID);
var contractList = Contract.GetContractListByMinMaxID(MinContracID, MaxContractID);
foreach (var item in list)
{
var myProject = projectList.FirstOrDefault(p => p.ID == item.RoomID);
if (myProject != null)
{
item.FullName = myProject.FullName;
item.RoomName = myProject.Name;
}
var myContract = contractList.FirstOrDefault(p => p.ID == item.ContractID);
if (myContract != null)
{
item.ContractName = myContract.ContractName;
item.ContractNo = myContract.ContractNo;
}
}
}
dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetMall_DoorRemoteUserDetailGridByKeywords(string keywords, long startRowIndex, int pageSize)
{
long totalRows = 0;
string OrderBy = " order by [AddTime] desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (!string.IsNullOrEmpty(keywords))
{
conditions.Add("([Title] like @keywords or [UserID] in (select [UserID] from [User] where [NickName] like @keywords or [PhoneNumber] like @keywords) or [ProjectName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + keywords + "%"));
}
string fieldList = "[Mall_DoorRemoteUser].*";
string Statement = " from [Mall_DoorRemoteUser] where " + string.Join(" and ", conditions.ToArray());
Mall_DoorRemoteUserDetail[] list = GetList <Mall_DoorRemoteUserDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
if (list.Length > 0)
{
List <int> remote_idlist = list.Select(p => p.ID).ToList();
var remote_device_list = Mall_DoorRemoteUserDevice.GetMall_DoorRemoteUserDevices().Where(p => remote_idlist.Contains(p.DoorRemoteID)).ToArray();
int MinRelationID = list.Min(p => p.RoomPhoneRelationID);
int MaxRelationID = list.Max(p => p.RoomPhoneRelationID);
var device_list = Mall_DoorDevice.GetALLActiveMall_DoorDeviceList().ToArray();
var user_list = Foresight.DataAccess.RoomPhoneRelation.GetRoomPhoneRelationListByMinMaxID(MinRelationID, MaxRelationID);
int MinRoomID = 0;
int MaxRoomID = 0;
if (user_list.Length > 0)
{
MinRoomID = user_list.Min(p => p.RoomID);
MaxRoomID = user_list.Max(p => p.RoomID);
}
var projectList = Project.GetProjectListByMinMaxID(MinRoomID, MaxRoomID);
foreach (var item in list)
{
var my_remote_device_list = remote_device_list.Where(p => p.DoorRemoteID == item.ID).ToArray();
List <int> my_deviceidlist = my_remote_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 my_user = user_list.FirstOrDefault(p => p.ID == item.RoomPhoneRelationID);
if (my_user != null)
{
item.NickName = my_user.RelationName;
item.PhoneNumber = my_user.RelatePhoneNumber;
var myProject = projectList.FirstOrDefault(p => p.ID == my_user.RoomID);
if (myProject != null)
{
item.FullName = myProject.FullName + "-" + myProject.Name;
item.RoomName = myProject.Name;
}
}
}
dg.rows = list;
}
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetMall_UserBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int AmountType, long startRowIndex, int pageSize, int IncomingType, int OutcomingType)
{
long totalRows = 0;
string OrderBy = " order by AddTime desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (AmountType > 0)
{
conditions.Add("[BalanceType]=@BalanceType");
parameters.Add(new SqlParameter("@BalanceType", AmountType));
}
if (IncomingType > 0)
{
conditions.Add("[CategoryType]=@IncomingType");
parameters.Add(new SqlParameter("@IncomingType", IncomingType));
}
if (OutcomingType > 0)
{
conditions.Add("[CategoryType]=@OutcomingType");
parameters.Add(new SqlParameter("@OutcomingType", OutcomingType));
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("Convert(varchar(100),[AddTime],23)>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("Convert(varchar(100),[AddTime],23)<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("[UserID] in (select [UserID] from [User] where [LoginName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select [Mall_UserBalance].*,(select LoginName from [User] where [UserID]=Mall_UserBalance.UserID) as CustomerName from [Mall_UserBalance])A where " + string.Join(" and ", conditions.ToArray());
Mall_UserBalanceDetail[] list = GetList <Mall_UserBalanceDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
if (list.Length > 0)
{
int MinUserID = list.Min(p => p.UserID);
int MaxUserID = list.Max(p => p.UserID);
var userList = User.GetAPPCustomerUserList(MinUserID: MinUserID, MaxUserID: MaxUserID);
var userProjectList = Mall_UserProject.GetMall_UserProjectListByMinMaxUserID(MinUserID, MaxUserID);
int MinRoomID = 0;
int MaxRoomID = 0;
if (userProjectList.Length > 0)
{
MinRoomID = userProjectList.Min(p => p.ProjectID);
MaxRoomID = userProjectList.Max(p => p.ProjectID);
}
var projectList = Project.GetProjectListByMinMaxID(MinRoomID, MaxRoomID);
foreach (var item in list)
{
var myUser = userList.FirstOrDefault(p => p.UserID == item.UserID);
if (myUser != null)
{
item.CustomerInfo = myUser.FinalRealName;
var myUserProjectList = userProjectList.Where(p => p.UserID == item.UserID).ToArray();
var myProjectList = projectList.Where(p => myUserProjectList.Select(q => q.ProjectID).ToList().Contains(p.ID)).ToArray();
if (myProjectList.Length > 0)
{
item.CustomerInfo += "【" + myProjectList[0].FullName + "-" + myProjectList[0].Name + "】";
}
}
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}