public static Ui.DataGrid GetDepositSummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, int CompanyID, string ChargeMan, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); string cmdNow = string.Empty; string cmdWhere = string.Empty; if (HistoryIDList != null && HistoryIDList.Count > 0) { if (HistoryIDList.Count > 2) { ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID); cmdNow += " and EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")"; } else if (HistoryIDList.Count > 0) { cmdNow += " and rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")"; } } if (RoomFeeOrderID > 0) { cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=@RoomFeeOrderID)"; parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID)); } else if (IsRoomFeeSearch) { cmdNow += " and [PrintID] in (select [ID] from [PrintRoomFeeHistory] where isnull([RoomFeeOrderID],0)=0)"; cmdNow += " and ([ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)"; if (StartChargeTime > DateTime.MinValue) { cmdNow += " and [ChargeTime]>=@StartChargeTime"; parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { cmdNow += " and [ChargeTime]<=@EndChargeTime"; parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } else { if (StartChargeTime > DateTime.MinValue) { cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)>=@StartChargeTime"; parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { cmdNow += " and Convert(nvarchar(10),[ChargeTime],120)<=@EndChargeTime"; parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } #region 收款人查询 string cmd = string.Empty; if (!string.IsNullOrEmpty(ChargeMan)) { string[] keywords = ChargeMan.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 ([ChargeMan] like '%" + keywords[i] + "%')"; } else { cmd += " ([ChargeMan] like '%" + keywords[i] + "%'))"; } } else if (i == 0) { cmd += " and (([ChargeMan] like '%" + keywords[i] + "%') or"; } else { cmd += " ([ChargeMan] like '%" + keywords[i] + "%') or "; } } } cmdNow += cmd; #endregion //if (!string.IsNullOrEmpty(ChargeMan)) //{ // cmdNow += " and [ChargeMan]=@ChargeMan"; // parameters.Add(new SqlParameter("@ChargeMan", ChargeMan)); //} if (CompanyID != 1 && CompanyID > 0) { cmdWhere += " and [CompanyID]=@CompanyID"; parameters.Add(new SqlParameter("@CompanyID", CompanyID)); } if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IsContractFee: true); cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")"; } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IsContractFee: true, UserID: UserID); cmdNow += " and (" + string.Join(" or ", cmdlist.ToArray()) + ")"; } var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList(); var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealMoneyCost2,0)) as RealCost,ChageType2 as ChargeTypeID from PrintRoomFeeHistory where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @") group by ChageType2", parameters).ToArray(); var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(RealMoneyCost2,0)) as RealCost,ChageType1 as ChargeTypeID from PrintRoomFeeHistory where [ID] in (select [PrintID] from RoomFeeHistory rfh where ChargeState in(3,6,7) " + cmdNow + @") group by ChageType1", parameters).ToArray(); decimal RealCost1 = 0; decimal RealCost2 = 0; var finalList = new List <ChargeMoneyTypeDetails>(); foreach (var item in type_list) { RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { item.RealCost = RealCost1 + RealCost2; finalList.Add(item); } } var ChargeTypeIDList = type_list.Select(p => p.ChargeTypeID).ToList(); RealCost1 = history1.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); RealCost2 = history2.Where(p => !ChargeTypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { var item = new ChargeMoneyTypeDetails(); item.ChargeTypeID = 0; item.ChargeTypeName = "其他"; item.RealCost = (RealCost1 + RealCost2); finalList.Add(item); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = finalList.ToArray(); dg.total = finalList.Count; dg.page = 1; return(dg); }
public static Ui.DataGrid GetHistorySummaryGroupByTypeName(List <int> RoomIDList, List <int> ProjectIDList, DateTime StartChargeTime, DateTime EndChargeTime, string ChargeMan, List <int> ChargeSummaryIDList, List <int> ChargeTypeIDList, List <int> ChargeStateList, int RoomFeeOrderID, bool IsRoomFeeSearch, string OpenID, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0) { List <SqlParameter> parameters = new List <SqlParameter>(); List <string> conditions = new List <string>(); conditions.Add("1=1"); List <string> conditions2 = new List <string>(); conditions2.Add("1=1"); List <string> conditions3 = new List <string>(); conditions3.Add("1=1"); if (HistoryIDList != null && HistoryIDList.Count > 0) { if (HistoryIDList.Count > 2) { ViewRoomFeeHistory.CreateTempTable(HistoryIDList, DeleteTempHistoryIDList, UserID: UserID); conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id=rfh.HistoryID and UserID=" + UserID + ")"); conditions2.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id in (select HistoryID from RoomFeeHistory where PrintID=prh.ID) and UserID=" + UserID + ")"); } else if (HistoryIDList.Count > 0) { conditions.Add("rfh.HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + ")"); conditions2.Add("prh.ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))"); } } if (!string.IsNullOrEmpty(OpenID)) { conditions.Add("rfh.[RoomID] in (select [ProjectID] from [WechatUser_Project] where [OpenID]=@OpenID)"); parameters.Add(new SqlParameter("@OpenID", OpenID)); } if (ChargeStateList.Count > 0) { if (ChargeStateList.Contains(1) || ChargeStateList.Contains(4)) { conditions2.Add("prh.[IsCancel]=0"); } conditions2.Add("prh.ID in (select PrintID from RoomFeeHistory where ChargeState in (" + string.Join(",", ChargeStateList.ToArray()) + "))"); } if (RoomFeeOrderID > 0) { conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=@RoomFeeOrderID"); parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID)); } else if (IsRoomFeeSearch) { conditions2.Add("isnull(prh.[RoomFeeOrderID],0)=0"); conditions2.Add("prh.[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)"); if (StartChargeTime > DateTime.MinValue) { conditions.Add("rfh.[ChargeTime]>=@StartChargeTime"); parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { conditions.Add("rfh.[ChargeTime]<=@EndChargeTime"); parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } else { if (StartChargeTime > DateTime.MinValue) { conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)>=@StartChargeTime"); parameters.Add(new SqlParameter("@StartChargeTime", StartChargeTime)); } if (EndChargeTime > DateTime.MinValue) { conditions.Add("Convert(nvarchar(10),rfh.[ChargeTime],120)<=@EndChargeTime"); parameters.Add(new SqlParameter("@EndChargeTime", EndChargeTime)); } } if (RoomIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, RoomIDName: "rfh.[RoomID]"); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } if (ProjectIDList.Count > 0) { List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "rfh.[RoomID]", UserID: UserID); conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")"); } #region 收款人查询 string cmd = string.Empty; if (!string.IsNullOrEmpty(ChargeMan)) { string[] keywords = ChargeMan.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 (prh.[ChargeMan] like '%" + keywords[i] + "%')"; } else { cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%'))"; } } else if (i == 0) { cmd += " and ((prh.[ChargeMan] like '%" + keywords[i] + "%') or"; } else { cmd += " (prh.[ChargeMan] like '%" + keywords[i] + "%') or "; } } } #endregion //if (!string.IsNullOrEmpty(ChargeMan)) //{ // conditions2.Add("prh.[ChargeMan] like '%" + ChargeMan + "%'"); //} if (ChargeSummaryIDList.Count > 0) { conditions.Add("rfh.[ChargeID] in (" + string.Join(",", ChargeSummaryIDList.ToArray()) + ")"); } if (ChargeTypeIDList.Count > 0) { conditions2.Add("(prh.[ChageType1] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ") or prh.[ChageType2] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + "))"); conditions3.Add("cmt.[ChargeTypeID] in (" + string.Join(",", ChargeTypeIDList.ToArray()) + ")"); } var type_list = GetList <ChargeMoneyTypeDetails>(@"select * from ChargeMoneyType", parameters).ToList(); var history1 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(RealCost,0)-isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType1 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @") Group by prh.ChageType1", parameters).ToArray(); var history2 = GetList <ChargeMoneyTypeDetails>(@"select sum(isnull(prh.RealMoneyCost2,0)) as RealCost,prh.ChageType2 as ChargeTypeID from PrintRoomFeeHistory prh where " + string.Join(" and ", conditions2.ToArray()) + cmd + @" and prh.[ID] in (select [PrintID] from RoomFeeHistory rfh where " + string.Join(" and ", conditions.ToArray()) + @") Group by prh.ChageType2", parameters).ToArray(); decimal RealCost1 = 0; decimal RealCost2 = 0; var finalList = new List <ChargeMoneyTypeDetails>(); foreach (var item in type_list) { RealCost1 = history1.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); RealCost2 = history2.Where(p => p.ChargeTypeID == item.ChargeTypeID).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { item.RealCost = RealCost1 + RealCost2; finalList.Add(item); } } var TypeIDList = type_list.Select(p => p.ChargeTypeID).ToList(); RealCost1 = history1.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); RealCost2 = history2.Where(p => !TypeIDList.Contains(p.ChargeTypeID)).Sum(p => p.RealCost); if ((RealCost1 + RealCost2) > 0) { var item = new ChargeMoneyTypeDetails(); item.ChargeTypeID = 0; item.ChargeTypeName = "其他"; item.RealCost = (RealCost1 + RealCost2); finalList.Add(item); } DataAccess.Ui.DataGrid dg = new Ui.DataGrid(); dg.rows = finalList.ToArray(); dg.total = finalList.Count; dg.page = 1; return(dg); }