public static Ui.DataGrid GetViewRoomSourceFeeGridByKeywords(string Keywords, List <int> RoomIDList, List <int> ProjectIDList, int ChargeID, bool IsRoomFee, bool IsContractFee, string RoomProperty, string orderBy, long startRowIndex, int pageSize, int UserID = 0)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[IsStart]=1");
if (!string.IsNullOrEmpty(RoomProperty))
{
conditions.Add("[RoomID] in (select [RoomID] from [RoomBasic] where isnull([RoomProperty],'')=@RoomProperty)");
parameters.Add(new SqlParameter("@RoomProperty", RoomProperty));
}
if (IsRoomFee && !IsContractFee)
{
conditions.Add("isnull([ContractID],0)=0");
}
if (IsContractFee && !IsRoomFee)
{
conditions.Add("isnull([ContractID],0)>0");
}
conditions.Add("([RoomID] in (select [RoomID] from [RoomBasic] where isnull([IsLocked],0)=0) or not exists (select * from [RoomBasic] where isnull([IsLocked],0)=0 and [RoomID]=[ViewRoomSourceFee].[RoomID]))");
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([Name] like @Keywords or [FullName] like @Keywords or [ChargeName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
string fieldList = "[ViewRoomSourceFee].*,(select top 1 [RelationName] from [RoomPhoneRelation] where isnull([IsDefault],0)=1 and [RoomID]=[ViewRoomSourceFee].[RoomID]) as [RelationName]";
string Statement = " from [ViewRoomSourceFee] where " + string.Join(" and ", conditions.ToArray());
ViewRoomSourceFee[] list = new ViewRoomSourceFee[] { };
list = GetList <ViewRoomSourceFee>(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 ViewRoomSourceFee[] GetViewRoomSourceFeeListByKeywords(string Keywords, List <int> RoomID, int ChargeID)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[IsStart]=1");
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([Name] like @Keywords or [FullName] like @Keywords or [ChargeName] like @Keywords)");
parameters.Add(new SqlParameter("@Keywords", "%" + Keywords + "%"));
}
if (RoomID.Count > 0)
{
conditions.Add("([RoomID] in (" + string.Join(",", RoomID.ToArray()) + ") or [RoomID] in (select [RoomID] from [RoomRelation] where [GUID] in (select [GUID] from [RoomRelation] where [RoomID] in (" + string.Join(",", RoomID.ToArray()) + "))))");
}
if (ChargeID > 0)
{
conditions.Add("[ChargeID]=@ChargeID");
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
}
ViewRoomSourceFee[] list = new ViewRoomSourceFee[] { };
list = GetList <ViewRoomSourceFee>("select * from [ViewRoomSourceFee] where " + string.Join(" and ", conditions.ToArray()) + " order by [RoomID] asc", parameters).ToArray();
return(list);
}