public virtual QueryResult Query(NewsInfoQueryFilter msg)
{
int totalCount;
DataSet ds = ObjectFactory <INewsQueryDA> .Instance.QueryNews(msg, out totalCount);
DataTable dtNews = ds.Tables[0];
DataTable dtBannerArea = ds.Tables[1];
foreach (DataRow drBanner in dtNews.Rows)
{
string areaInfo = "";
foreach (DataRow drArea in dtBannerArea.Rows)
{
if (drBanner["SysNo"].ToString() == drArea["RefSysNo"].ToString())
{
//用逗号分隔主要投放区域
areaInfo += drArea["AreaSysNo"] + ",";
}
}
drBanner["AreaShow"] = areaInfo.TrimEnd(',');
}
return(new QueryResult()
{
Data = dtNews,
TotalCount = totalCount
});
}
public override void OnPageLoad(object sender, EventArgs e)
{
base.OnPageLoad(sender, e);
facade = new NewsFacade(this);
viewModel = new NewsInfoQueryVM();
QuerySection.DataContext = viewModel;
filter = new NewsInfoQueryFilter();
InitPageControlsData();
}
/// <summary>
/// 查询新闻公告
/// </summary>
/// <param name="filter"></param>
/// <param name="callback"></param>
public void QueryNews(NewsInfoQueryFilter filter, EventHandler <RestClientEventArgs <dynamic> > callback)
{
string relativeUrl = "/MKTService/NewsInfo/QueryNews";
restClient.QueryDynamicData(relativeUrl, filter, callback);
}
public DataSet QueryNews(NewsInfoQueryFilter filter, out int totalCount)
{
PagingInfoEntity pagingEntity = new PagingInfoEntity();
pagingEntity.SortField = filter.PagingInfo.SortBy;
pagingEntity.MaximumRows = filter.PagingInfo.PageSize;
pagingEntity.StartRowIndex = filter.PagingInfo.PageIndex * filter.PagingInfo.PageSize;
var cmd = DataCommandManager.CreateCustomDataCommandFromConfig("GetNewsList");
using (var sqlBuilder = new DynamicQuerySqlBuilder(cmd.CommandText, cmd, pagingEntity, "a.SysNo DESC"))
{
//sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND, "ChannelID", DbType.String, "@ChannelID", QueryConditionOperatorType.Equal, filter.ChannelID);
if (filter.SysNo.HasValue && filter.SysNo.Value > 0)
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.SysNo", DbType.Int32, "@SysNo",
QueryConditionOperatorType.Equal, filter.SysNo);
}
else
{
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Title", DbType.String, "@Title",
QueryConditionOperatorType.Like, filter.Title);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"Subtitle", DbType.String, "@Subtitle",
QueryConditionOperatorType.Like, filter.Subtitle);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"TopMost", DbType.Int32, "@TopMost",
QueryConditionOperatorType.Equal, filter.IsSetTop);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CreateUserSysNo", DbType.Int32, "@CreateUserSysNo",
QueryConditionOperatorType.Equal, filter.InUser);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CreateDate", DbType.DateTime, "@CreateDateTo",
QueryConditionOperatorType.LessThanOrEqual,
filter.InDateFromTo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"CreateDate", DbType.DateTime, "@CreateDateFrom",
QueryConditionOperatorType.MoreThanOrEqual,
filter.InDateFrom);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"NewsType", DbType.Int32, "@NewsType",
QueryConditionOperatorType.Equal, filter.NewsType);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"ReferenceSysNo", DbType.Int32, "@ReferenceSysNo",
QueryConditionOperatorType.Equal, filter.ReferenceSysNo);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.Status", DbType.Int32, "@Status",
QueryConditionOperatorType.Equal, filter.Status);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"EnableComment", DbType.Int32, "@EnableComment",
QueryConditionOperatorType.Equal, filter.IsShow);
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"IsRed", DbType.Int32, "@IsRed",
QueryConditionOperatorType.Equal, filter.IsRed);
}
sqlBuilder.ConditionConstructor.AddCondition(QueryConditionRelationType.AND,
"a.CompanyCode", DbType.String, "@CompanyCode",
QueryConditionOperatorType.Equal, filter.CompanyCode);
if (!string.IsNullOrEmpty(filter.SelectedArea))
{
foreach (string area in filter.SelectedArea.Split(','))
{
sqlBuilder.ConditionConstructor.AddSubQueryCondition(QueryConditionRelationType.AND, null, QueryConditionOperatorType.Exist, "SELECT TOP 1 1 FROM OverseaECommerceManagement.dbo.AreaRelation_Website WITH(NOLOCK) WHERE AreaSysNo = " + area + " AND RefSysNo=A.SysNo AND [Type]='N' AND [Status]='A'");
}
}
cmd.CommandText = sqlBuilder.BuildQuerySql();
var ds = cmd.ExecuteDataSet();
EnumColumnList enumConfigNews = new EnumColumnList();
enumConfigNews.Add(13, typeof(NewsStatus));
enumConfigNews.Add(15, typeof(CustomerRank));
cmd.ConvertEnumColumn(ds.Tables[0], enumConfigNews);
cmd.ConvertCodeNamePairColumn(ds.Tables[1], 0, "MKT", string.Format("Area-{0}-{1}-NewsAdv", filter.CompanyCode, filter.ChannelID ?? "0"));
totalCount = Convert.ToInt32(cmd.GetParameterValue("@TotalCount"));
return(ds);
}
}
