public static List <string> GetPublicParentIDListConditions(List <int> ParentIDList, string RoomIDName = "[PublicProjectID]", int UserID = 0)
{
List <string> cmdlist = new List <string>();
ParentIDList = ParentIDList.Where(p => p != 0).ToList();
if (ParentIDList.Count == 0)
{
return(cmdlist);
}
List <string> cmdwhere = new List <string>();
if (ParentIDList.Count > 100)
{
ViewRoomFeeHistory.CreateTempTable(ParentIDList, UserID: UserID, InsertProjectID: true, DeleteTempHistoryIDList: false);
cmdwhere.Add("exists (select 1 from [TempIDs] where [UserID]=" + UserID + " and ([Project_Public].[AllParentID] like '%,'+[TempIDs].[ProjectID]+',%' or [Project_Public].ID=[TempIDs].[ProjectID]))");
}
else
{
foreach (var ProjectID in ParentIDList)
{
cmdwhere.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID]=" + ProjectID + ")");
}
}
if (RoomIDName.Equals("[ID]"))
{
cmdlist.Add("(" + string.Join(" or ", cmdwhere) + ")");
}
else
{
cmdlist.Add("(" + RoomIDName + " in (select ID from [Project_Public] where " + string.Join(" or ", cmdwhere) + "))");
}
return(cmdlist);
}
public static List <string> GetProjectIDListConditions(List <int> ProjectIDList, bool IncludeRelation = true, bool IsContractFee = false, string RoomIDName = "[RoomID]", int UserID = 0, string ContractIDName = "[ContractID]")
{
List <string> cmdlist = new List <string>();
ProjectIDList = ProjectIDList.Where(p => p != 0).ToList();
if (ProjectIDList.Count == 0)
{
cmdlist.Add("1=1");
return(cmdlist);
}
List <string> cmdwhere = new List <string>();
if (ProjectIDList.Count > 100)
{
ViewRoomFeeHistory.CreateTempTable(ProjectIDList, UserID: UserID, InsertProjectID: true, DeleteTempHistoryIDList: false);
cmdwhere.Add("exists (select 1 from [TempIDs] where [UserID]=" + UserID + " and ([Project].[AllParentID] like '%,'+[TempIDs].[ProjectID]+',%' or [Project].ID=[TempIDs].[ProjectID]))");
}
else
{
foreach (var ProjectID in ProjectIDList)
{
cmdwhere.Add("([AllParentID] like '%," + ProjectID + ",%' or [ID] =" + ProjectID + ")");
}
}
cmdlist.Add("(" + RoomIDName + " in (select ID from [Project] where " + string.Join(" or ", cmdwhere) + "))");
if (IncludeRelation)
{
cmdlist.Add("(" + RoomIDName + " in (select [RoomID] from [RoomRelation] where [GUID] in (select [GUID] from [RoomRelation] where [RoomID] in (select ID from [Project] where " + string.Join(" or ", cmdwhere) + "))))");
}
if (IsContractFee)
{
cmdlist.Add("(" + RoomIDName + "=0 and isnull(" + ContractIDName + ",0)>0 and " + ContractIDName + " in (select ContractID from [Contract_Room] where [RoomID] in (select ID from [Project] where " + string.Join(" or ", cmdwhere) + ")))");
}
return(cmdlist);
}
public static void UpdateRoomFeeOrderNumber(int RoomFeeOrderID, SqlHelper helper, List <int> HistoryIDList, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
conditions.Add("isnull([RoomFeeOrderID],0)=0");
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeState] in (1,2,3,4,6,7))");
if (HistoryIDList.Count <= 0)
{
throw new Exception("请选择历史单据");
}
if (HistoryIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(HistoryIDList, UserID: UserID);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where exists (select 1 from [TempIDs] where [TempIDs].id=[RoomFeeHistory].[HistoryID] and UserID=" + UserID + "))");
}
else if (HistoryIDList.Count > 0)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [HistoryID] in (" + string.Join(",", HistoryIDList.ToArray()) + "))");
}
string cmdtext = "update [PrintRoomFeeHistory] set [RoomFeeOrderID]=@RoomFeeOrderID where " + string.Join(" and ", conditions.ToArray());
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
int total = helper.Execute(cmdtext, CommandType.Text, parameters);
if (total <= 0)
{
throw new Exception("请选择历史单据");
}
}
public static Mall_UserProject[] GetMall_UserProjectListByUserIDList(List <int> UserIDList, int UserID = 0)
{
if (UserIDList.Count == 0)
{
return(new Mall_UserProject[] { });
}
List <SqlParameter> parameters = new List <SqlParameter>();
ViewRoomFeeHistory.CreateTempTable(UserIDList, UserID: UserID);
string sqlText = "select * from [Mall_UserProject] where EXISTS (SELECT 1 FROM [TempIDs] WHERE id=[Mall_UserProject].[UserID] and UserID=" + UserID + ") and isnull([IsDisable],0)=0";
return(GetList <Mall_UserProject>(sqlText, parameters).ToArray());
}
public static RoomFee[] GetRoomFeeListByImportFeeIDList(List <int> ImportFeeIDList, int UserID = 0)
{
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (ImportFeeIDList.Count == 0)
{
return(new RoomFee[] { });
}
if (ImportFeeIDList.Count > 2)
{
ViewRoomFeeHistory.CreateTempTable(ImportFeeIDList, UserID: UserID);
conditions.Add("EXISTS (SELECT 1 FROM [TempIDs] WHERE id=[RoomFee].ImportFeeID and [UserID]=" + UserID + ")");
}
else if (ImportFeeIDList.Count > 0)
{
conditions.Add("[ImportFeeID] in (" + string.Join(",", ImportFeeIDList.ToArray()) + ")");
}
return(GetList <RoomFee>("select * from [RoomFee] where " + string.Join(" and ", conditions.ToArray()), parameters).ToArray());
}
public static decimal GetSumWeiShuByChargeState(DateTime StartTime, DateTime EndTime, string ChargeMan, List <int> ChargeStateList, List <int> ProjectIDList, List <int> RoomIDList, int RoomFeeOrderID, bool IsRoomFeeSearch, List <int> HistoryIDList = null, bool DeleteTempHistoryIDList = true, int UserID = 0)
{
decimal total = 0;
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.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 in (select [HistoryID] from [RoomFeeHistory] where [PrintID]=[PrintRoomFeeHistory].[ID]) and [UserID]=" + UserID + ")");
}
else if (HistoryIDList.Count > 0)
{
conditions.Add("[PrintRoomFeeHistory].ID in (select PrintID from [RoomFeeHistory] where HistoryID in (" + string.Join(",", HistoryIDList.ToArray()) + "))");
}
}
if (RoomFeeOrderID > 0)
{
conditions.Add("isnull([RoomFeeOrderID],0)=@RoomFeeOrderID");
parameters.Add(new SqlParameter("@RoomFeeOrderID", RoomFeeOrderID));
}
else if (IsRoomFeeSearch)
{
conditions.Add("isnull([RoomFeeOrderID],0)=0");
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeID] in (select [ID] from [ChargeSummary] where isnull([IsOrderFeeOn],0)=1) or isnull(ChargeID,0)=0)");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, IncludeRelation: false, UserID: UserID);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where (" + string.Join(" or ", cmdlist.ToArray()) + "))");
}
if (RoomIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetRoomIDListConditions(RoomIDList, IncludeRelation: false);
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where (" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeTime]>@StartTime)");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeTime]<@EndTime)");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
#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 ";
}
}
}
#endregion
//if (!string.IsNullOrEmpty(ChargeMan))
//{
// conditions.Add("[ChargeMan]=@ChargeMan");
// parameters.Add(new SqlParameter("@ChargeMan", ChargeMan));
//}
if (ChargeStateList.Count > 0)
{
conditions.Add("[ID] in (select [PrintID] from [RoomFeeHistory] where [ChargeState] in (" + string.Join(",", ChargeStateList.ToArray()) + "))");
}
string cmdtext = "select sum(WeiShuMore)-sum(WeiShuConsume) from [PrintRoomFeeHistory] where " + string.Join(" and ", conditions.ToArray()) + cmd;
using (SqlHelper helper = new SqlHelper())
{
object obj = helper.ExecuteScalar(cmdtext, CommandType.Text, parameters);
if (obj != null)
{
decimal.TryParse(obj.ToString(), out total);
}
}
return(total);
}
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);
}
