public static ViewRoomBasicDetails[] GetRoomBasicDetailsListByKeywords(List <int> RoomIDList, List <int> ProjectIDList, string WriteDate, int ChargeID)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[isParent]=0");
if (RoomIDList.Count > 0)
{
conditions.Add("[RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("[AllParentID] like '%," + ProjectID + ",%'");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
ViewRoomBasicDetails[] list = new ViewRoomBasicDetails[] { };
parameters.Add(new SqlParameter("@ChargeID", ChargeID));
parameters.Add(new SqlParameter("@WriteDate", WriteDate));
list = GetList <ViewRoomBasicDetails>("select *,[Project_Biao].[ID] as ProjectBiao_ID,[Project_Biao].[BiaoID] as ProjectBiao_BiaoID,[Project_Biao].[BiaoCategory] as ProjectBiao_BiaoCategory,[Project_Biao].[BiaoName] as ProjectBiao_BiaoName,(select top 1 [EndPoint] from [ImportFee] where ChargeID=@ChargeID and isnull(ProjectBiaoID,0)=isnull([Project_Biao].ID,0) and CONVERT(varchar(10), [WriteDate], 120)<@WriteDate and RoomID=[ViewRoomBasic].RoomID order by WriteDate desc) as LastEndPoint,(select top 1 [EndTime] from [ImportFee] where ChargeID=@ChargeID and isnull(ProjectBiaoID,0)=isnull([Project_Biao].ID,0) and CONVERT(varchar(10), [WriteDate], 120)<@WriteDate and RoomID=[ViewRoomBasic].RoomID order by WriteDate desc) as LastEndTime,(select top 1 [UnitPrice] from [ImportFee] where ChargeID=@ChargeID and isnull(ProjectBiaoID,0)=isnull([Project_Biao].ID,0) and CONVERT(varchar(10), [WriteDate], 120)<@WriteDate and RoomID=[ViewRoomBasic].RoomID order by WriteDate desc) as LastUnitPrice,(select top 1 [ImportCoefficient] from [ImportFee] where ChargeID=@ChargeID and isnull(ProjectBiaoID,0)=isnull([Project_Biao].ID,0) and CONVERT(varchar(10), [WriteDate], 120)<@WriteDate and RoomID=[ViewRoomBasic].RoomID order by WriteDate desc) as LastCoefficient,(select sum(isnull([TotalPoint],0)) from [ImportFee] where ChargeID=@ChargeID and isnull(ProjectBiaoID,0)=isnull([Project_Biao].ID,0) and CONVERT(varchar(10), [WriteDate], 120)<@WriteDate and RoomID=[ViewRoomBasic].RoomID) as TotalUseCount from [ViewRoomBasic] left join (select * from [Project_Biao] where [BiaoID] in (select [ChargeBiaoID] from [ChargeSummary_Biao] where [ChargeID]=@ChargeID)) as [Project_Biao] on [Project_Biao].ProjectID=[ViewRoomBasic].RoomID where " + string.Join(" and ", conditions.ToArray()) + " and isnull([Project_Biao].[IsActive],1)=1", parameters).ToArray();
var ProjectBiaoIDList = list.Where(p => p.ProjectBiao_ID > 0).Select(p => p.ProjectBiao_ID).Distinct().ToList();
return(list);
}
public static ViewRoomBasicDetails[] GetRoomBasicDetailsListByBiaoID(List <int> RoomIDList, List <int> ProjectIDList, int BiaoID, string BiaoCategory, string BiaoName, string BiaoGuiGe)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("[isParent]=0");
if (RoomIDList.Count > 0)
{
conditions.Add("[RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("[AllParentID] like '%," + ProjectID + ",%'");
}
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
ViewRoomBasicDetails[] list = new ViewRoomBasicDetails[] { };
parameters.Add(new SqlParameter("@BiaoID", BiaoID));
string cmdwhere = string.Empty;
if (!string.IsNullOrEmpty(BiaoCategory))
{
parameters.Add(new SqlParameter("@BiaoCategory", BiaoCategory));
cmdwhere += " and [BiaoCategory]=@BiaoCategory";
}
if (!string.IsNullOrEmpty(BiaoName))
{
parameters.Add(new SqlParameter("@BiaoName", BiaoName));
cmdwhere += " and [BiaoName]=@BiaoName";
}
if (!string.IsNullOrEmpty(BiaoGuiGe))
{
parameters.Add(new SqlParameter("@BiaoGuiGe", BiaoGuiGe));
cmdwhere += " and [BiaoGuiGe]=@BiaoGuiGe";
}
list = GetList <ViewRoomBasicDetails>("select *,(select top 1 ID from [Project_Biao] where [ProjectID]=[ViewRoomBasic].RoomID and [BiaoID]=@BiaoID " + cmdwhere + ") as [ProjectBiao_ID] from [ViewRoomBasic] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
return(list);
}