public static void GetContractFeeSummary(ContractDetail[] list, out ContractDetail footeritem, bool outfooter = false)
{
List <int> ContractIDList = list.Select(p => p.ID).ToList();
var fee_list = RoomFeeAnalysis.GetRoomFeeAnalysisListByContractIDList(ContractIDList);
var feehistory_list = ViewRoomFeeHistory.GetViewRoomFeeHistoryListByContractIDList(ContractIDList);
var fee_weiyue_list = RoomFeeAnalysis.GetContractFeeWeiYueListByContractIDList(ContractIDList);
decimal totalRestCost = 0;
decimal totalChargedCost = 0;
decimal totalPreCost = 0;
decimal totalDepositCost = 0;
decimal totalBreakCost = 0;
decimal totalTotalCost = 0;
foreach (var item in list)
{
item.RestCost = fee_list.Where(p => p.ContractID == item.ID && p.TotalCost > 0).Sum(p => p.TotalCost);
item.ChargedCost = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0).Sum(p => p.RealCost);
item.PreCost = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0 && p.CategoryID == 4).Sum(p => p.RealCost);
item.DepositCost = feehistory_list.Where(p => p.ContractID == item.ID && p.RealCost > 0 && p.CategoryID == 3).Sum(p => p.RealCost);
item.TotalCost = item.RestCost + item.ChargedCost;
item.BreakCost = 0;
totalRestCost += item.RestCost;
totalChargedCost += item.ChargedCost;
totalPreCost += item.PreCost;
totalDepositCost += item.DepositCost;
totalBreakCost += fee_weiyue_list.Where(p => p.RelatedFeeID == item.ID && p.TotalCost > 0).Sum(p => p.TotalCost);
totalTotalCost += item.TotalCost;
}
footeritem = new ContractDetail();
footeritem.ContractNo = "合计";
footeritem.RestCost = totalRestCost;
footeritem.ChargedCost = totalChargedCost;
footeritem.PreCost = totalPreCost;
footeritem.DepositCost = totalDepositCost;
footeritem.TotalCost = totalTotalCost;
footeritem.BreakCost = totalBreakCost;
}
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 GetContractDetailGridByKeywords(string Keywords, string ContractStatus, List <int> RoomIDList, List <int> ProjectIDList, DateTime StartTime, DateTime EndTime, DateTime RentStartTime, DateTime RentEndTime, string orderBy, long startRowIndex, int pageSize, int onlyexpired = 0, bool canexport = 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));
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = new List <string>();
foreach (var ProjectID in ProjectIDList)
{
cmdlist.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID] =" + ProjectID + ")");
}
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));
}
string fieldList = "A.*";
string Statement = @" from (select *,(select sum(isnull(ContractArea,0)) from [RoomBasic] where ([RoomID]
in (select [RoomID] from [Contract_RoomCharge] where [ContractID]=[Contract].ID)
or ([RoomID] in (select [RoomID] from [Contract_Room] where [ContractID]=[Contract].ID
and exists(select 1 from [Contract_RoomCharge] where [ContractID]=[Contract].ID and [RoomID]=0)
))
)) as TotalContractArea from [Contract]) A where " + string.Join(" and ", conditions.ToArray()) + cmd;
var list = GetList <ContractDetail>("select " + fieldList + Statement + " " + orderBy, parameters).ToArray();
SetContractWarningCount(list);
if (onlyexpired == 1)
{
string ContractStatusDesc = Utility.EnumModel.ContractStatus.tongguo.ToString();
list = list.Where(p => p.IsRentExpire).ToArray();
}
if (!canexport)
{
list = list.Skip((int)startRowIndex).Take(pageSize).ToArray();
}
totalRows = list.Length;
var footeritem = new ContractDetail();
GetContractFeeSummary(list, out footeritem, outfooter: true);
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.footer = new ContractDetail[] { footeritem };
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static void GetContractFeeSummary(ContractDetail[] list)
{
ContractDetail footeritem = null;
GetContractFeeSummary(list, out footeritem, outfooter: true);
}
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);
}
