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); }