public static Ui.DataGrid GetMall_BusinessBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int BalanceStatus, long startRowIndex, int pageSize)
{
long totalRows = 0;
string OrderBy = " order by AddTime desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (BalanceStatus == 23)
{
conditions.Add("[BalanceStatus] in (2,3)");
}
else if (BalanceStatus > 0)
{
conditions.Add("[BalanceStatus]=@BalanceStatus");
parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus));
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[AddTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[AddTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([BusinessID] in (select [ID] from [Mall_Business] where [BusinessName] like @keywords))");
parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select [Mall_BusinessBalance].*,[Mall_Business].BusinessName,[Mall_Business].ContactName as BusinessContactMan from [Mall_BusinessBalance] left join [Mall_Business] on [Mall_Business].ID=[Mall_BusinessBalance].BusinessID)A where " + string.Join(" and ", conditions.ToArray());
Mall_BusinessBalanceDetail[] list = GetList <Mall_BusinessBalanceDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
var rule_list = Mall_BalanceRule.GetMall_BalanceRules().ToArray();
foreach (var item in list)
{
var my_rule = rule_list.FirstOrDefault(p => p.ID == item.BalanceRuleID);
if (my_rule != null)
{
item.BalanceRuleName = my_rule.Title;
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}
public static Ui.DataGrid GetMall_RoomOwnerBalanceDetailGridByKeywords(string Keywords, DateTime StartTime, DateTime EndTime, int BalanceStatus, long startRowIndex, int pageSize, List <int> RoomIDList, List <int> ProjectIDList, int UserID = 0)
{
long totalRows = 0;
string OrderBy = " order by AddTime desc";
List <SqlParameter> parameters = new List <SqlParameter>();
List <string> conditions = new List <string>();
conditions.Add("1=1");
if (RoomIDList.Count > 0)
{
conditions.Add("[ProjectID] in (" + string.Join(",", RoomIDList.ToArray()) + ")");
}
if (ProjectIDList.Count > 0)
{
List <string> cmdlist = ViewRoomFeeHistory.GetProjectIDListConditions(ProjectIDList, RoomIDName: "[ProjectID]", UserID: UserID);
conditions.Add("(" + string.Join(" or ", cmdlist.ToArray()) + ")");
}
if (BalanceStatus == 23)
{
conditions.Add("[BalanceStatus] in (2,3)");
}
else if (BalanceStatus > 0)
{
conditions.Add("[BalanceStatus]=@BalanceStatus");
parameters.Add(new SqlParameter("@BalanceStatus", BalanceStatus));
}
if (StartTime > DateTime.MinValue)
{
conditions.Add("[AddTime]>=@StartTime");
parameters.Add(new SqlParameter("@StartTime", StartTime));
}
if (EndTime > DateTime.MinValue)
{
conditions.Add("[AddTime]<=@EndTime");
parameters.Add(new SqlParameter("@EndTime", EndTime));
}
if (!string.IsNullOrEmpty(Keywords))
{
conditions.Add("([RoomOwnerName] like @keywords)");
parameters.Add(new SqlParameter("@keywords", "%" + Keywords + "%"));
}
string fieldList = "A.*";
string Statement = " from (select [Mall_RoomOwnerBalance].*,[RoomPhoneRelation].RelationName as RoomOwnerName,[Project].FullName,[Project].Name as RoomName from [Mall_RoomOwnerBalance] left join [RoomPhoneRelation] on [RoomPhoneRelation].ID=[Mall_RoomOwnerBalance].RoomPhoneRelationID left join [Project] on [Project].ID=[RoomPhoneRelation].RoomID)A where " + string.Join(" and ", conditions.ToArray());
Mall_RoomOwnerBalanceDetail[] list = GetList <Mall_RoomOwnerBalanceDetail>(fieldList, Statement, parameters, OrderBy, startRowIndex, pageSize, out totalRows).ToArray();
var rule_list = Mall_BalanceRule.GetMall_BalanceRules().ToArray();
foreach (var item in list)
{
var my_rule = rule_list.FirstOrDefault(p => p.ID == item.BalanceRuleID);
if (my_rule != null)
{
item.BalanceRuleName = my_rule.Title;
}
}
DataAccess.Ui.DataGrid dg = new Ui.DataGrid();
dg.rows = list;
dg.total = totalRows;
dg.page = pageSize;
return(dg);
}