public static int GetALLWaringingContractsCount(List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
List <string> conditions = new List <string>();
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, RoomIDName: "[RoomID]", IsContractFee: false, UserID: UserID);
conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false, RoomIDName: "[RoomID]");
conditions.Add("ID in (select ContractID from Contract_Room where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
List <SqlParameter> parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter("@ContractStatus", Utility.EnumModel.ContractStatus.tongguo.ToString()));
conditions.Add("[ContractStatus]=@ContractStatus");
var list = GetList <ContractDetail>("select [ID],[ContractStatus],[RentEndTime] from [Contract] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray();
ContractDetail.SetContractWarningCount(list);
list = list.Where(p => p.IsRentExpire).ToArray();
return(list.Length);
}
public static Ui.DataGrid GetContractGridByKeywords(string Keywords, string ContractStatus, List <int> RoomIDList, List <int> ProjectIDList, bool ShowALL, DateTime StartTime, DateTime EndTime, DateTime RentStartTime, DateTime RentEndTime, string orderBy, long startRowIndex, int pageSize, bool IsDivideOn = false)
{
long totalRows = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
#region 关键字查询
string cmd = string.Empty;
if (!string.IsNullOrEmpty(Keywords))
{
string[] keywords = Keywords.Trim().Split(' ');
for (int i = 0; i < keywords.Length; i++)
{
if (string.IsNullOrEmpty(keywords[i].ToString()))
{
continue;
}
if (i + 1 == keywords.Length)
{
if (keywords.Length == 1)
{
cmd += " and ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%')";
}
else
{
cmd += " ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%'))";
}
}
else if (i == 0)
{
cmd += " and (([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%') or";
}
else
{
cmd += " ([RentName] like '%" + keywords[i] + "%' or [RoomLocation] like '%" + keywords[i] + "%' or [ContractNo] like '%" + keywords[i] + "%' or [ContractName] like '%" + keywords[i] + "%' or [ContractSummary] like '%" + keywords[i] + "%') or ";
}
}
}
#endregion
if (RentStartTime > DateTime.MinValue)
{
conditions.Add("[RentEndTime]>=@RentStartTime");
parameters.Add(new SqlParameter("@RentStartTime", RentStartTime));
}
if (RentEndTime > DateTime.MinValue)
{
conditions.Add("[RentEndTime]<=@RentEndTime");
parameters.Add(new SqlParameter("@RentEndTime", RentEndTime));
}
if (!string.IsNullOrEmpty(ContractStatus))
{
conditions.Add("[ContractStatus]=@ContractStatus");
parameters.Add(new SqlParameter("@ContractStatus", ContractStatus));
}
//else
//{
// conditions.Add("[ContractStatus]<>@ContractStatus");
// parameters.Add(new SqlParameter("@ContractStatus", Utility.EnumModel.ContractStatus.deleted.ToString()));
//}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID] =" + ProjectID + ")");
}
if (ShowALL)
{
conditions.Add("([ID] in (select [ContractID] from [Contract_Room] where [RoomID] in (select [ID] from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + "))) or not exists (select * from [Contract_Room] where ContractID=[Contract].ID))");
}
else
{
conditions.Add("[ID] in (select [ContractID] from [Contract_Room] where [RoomID] in (select [ID] from [Project] where (" + string.Join(" or ", cmdlist.ToArray()) + ")))");
}
}
if (RoomIDList.Count > 0)
{
conditions.Add("[ID] in (select [ContractID] from [Contract_Room] where [RoomID] in (" + string.Join(",", RoomIDList.ToArray()) + "))");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[SignTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[SignTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (IsDivideOn)
{
conditions.Add("[IsDivideOn]=1");
}
string fieldList = "[Contract].*";
string Statement = " from [Contract] where " + string.Join(" and ", conditions.ToArray()) + cmd;
var list = GetList <ContractDetail>(fieldList, Statement, parameters, orderBy, startRowIndex, pageSize, out totalRows).ToArray();
ContractDetail.SetContractWarningCount(list);
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}