public static int CountBannerConfig(SqlConnection conn, BannerFilterQuery queryModel)
{
string sql = @"SELECT Count(0) FROM Configuration.dbo.PersonalCenterConfig WITH ( NOLOCK ) WHERE IsNew=1";
if (queryModel.IDCriterion != null)
{
sql += @" AND Id=" + queryModel.IDCriterion.Value;
}
if (!string.IsNullOrWhiteSpace(queryModel.DescriptionCriterion))
{
sql += @" AND Description LIKE N'%" + queryModel.DescriptionCriterion + @"%'";
}
if (queryModel.ChannelCriterion != 0)
{
sql += @" AND Channel=" + queryModel.ChannelCriterion;
}
if (queryModel.LocationCriterion != 0)
{
sql += @" AND Location=" + queryModel.LocationCriterion;
}
if (queryModel.StatusCriterion != null)
{
sql += (queryModel.StatusCriterion.Value ? @" AND Status=1" : @" AND Status=0");
}
if (!string.IsNullOrWhiteSpace(queryModel.StartVersionCriterion))
{
sql += @" AND StartVersion='" + queryModel.StartVersionCriterion + @"'";
}
if (!string.IsNullOrWhiteSpace(queryModel.EndVersionCriterion))
{
sql += @" AND EndVersion='" + queryModel.EndVersionCriterion + @"'";
}
if (!string.IsNullOrWhiteSpace(queryModel.TargetGroupsCriterion))
{
sql += @" AND N'" + queryModel.TargetGroupsCriterion + @"' IN ( SELECT * FROM Configuration.dbo.SplitString(TargetGroups, ',', 1))";
}
if (queryModel.SortCriterion != null)
{
sql += @" AND Sort=" + queryModel.SortCriterion.Value;
}
if (queryModel.StartDateCriterion != null)
{
sql += @" AND CreateTime>='" + queryModel.StartDateCriterion.Value + @"'";
}
if (queryModel.EndDateCriterion != null)
{
sql += @" AND CreateTime<='" + queryModel.EndDateCriterion.Value + @"'";
}
if (!string.IsNullOrWhiteSpace(queryModel.CreatorCriterion))
{
sql += @" AND Creator LIKE N'%" + queryModel.CreatorCriterion + @"%'";
}
var obj = SqlHelper.ExecuteScalar(conn, CommandType.Text, sql);
if (obj != null)
{
return(Convert.ToInt32(obj));
}
return(0);
}
public ViewResult Index(BannerFilterQuery bannerfilterQuery, int page = 1)
{
var bannerConfigs = DownloadAppManager.QueryBannerConfig(bannerfilterQuery, page);
if (bannerConfigs != null && bannerConfigs.Any())
{
foreach (var banner in bannerConfigs)
{
if (!string.IsNullOrWhiteSpace(banner.Image))
{
banner.Image = WebConfigurationManager.AppSettings["DoMain_image"] + banner.Image;
}
}
}
ViewBag.NoticeChannel = DownloadAppManager.QueryNoticeChannel() ?? new List <NoticeChannel>();
ViewBag.AllTargetGroups = GetTargetGroupDic();
ViewBag.RuleList = VIPAuthorizationRuleConfigManager.GetVIPAuthorizationRuleAndId();
ViewBag.NumOfBanners = DownloadAppManager.CountBannerConfig(bannerfilterQuery);
ViewBag.Page = page;
#region 拉列表
ViewBag.ChannelDLL = ChannelDropDownList();
ViewBag.LocationDLL = LocationDropDownList();
ViewBag.TargetSmallAppIdDLL = TargetSmallAppIdDropDownList();
#endregion
return(View(bannerConfigs ?? new List <BannerConfig>()));
}
public int CountBannerConfig(BannerFilterQuery queryModel)
{
try
{
return(handler.CountBannerConfig(queryModel));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new DownloadAppException(1, "CountBannerConfig", ex);
Logger.Log(Level.Error, exception, "CountBannerConfig");
throw ex;
}
}
public List <BannerConfig> QueryBannerConfig(BannerFilterQuery model, int page)
{
try
{
return(handler.QueryBannerConfig(model, page));
}
catch (TuhuBizException)
{
throw;
}
catch (Exception ex)
{
var exception = new DownloadAppException(1, "QueryBannerConfig", ex);
Logger.Log(Level.Error, exception, "QueryBannerConfig");
throw ex;
}
}
public static List <BannerConfig> QueryBannerConfig(SqlConnection conn, BannerFilterQuery queryModel, int page)
{
string sql = @"SELECT * FROM Configuration.dbo.PersonalCenterConfig WITH ( NOLOCK ) WHERE IsNew=1";
if (queryModel.IDCriterion != null)
{
sql += @" AND Id=" + queryModel.IDCriterion.Value;
}
if (!string.IsNullOrWhiteSpace(queryModel.DescriptionCriterion))
{
sql += @" AND Description LIKE N'%" + queryModel.DescriptionCriterion + @"%'";
}
if (queryModel.ChannelCriterion != 0)
{
sql += @" AND Channel=" + queryModel.ChannelCriterion;
}
if (queryModel.LocationCriterion != 0)
{
sql += @" AND Location=" + queryModel.LocationCriterion;
}
if (queryModel.StatusCriterion != null)
{
sql += (queryModel.StatusCriterion.Value ? @" AND Status=1" : @" AND Status=0");
}
if (!string.IsNullOrWhiteSpace(queryModel.StartVersionCriterion))
{
sql += @" AND StartVersion='" + queryModel.StartVersionCriterion + @"'";
}
if (!string.IsNullOrWhiteSpace(queryModel.EndVersionCriterion))
{
sql += @" AND EndVersion='" + queryModel.EndVersionCriterion + @"'";
}
if (!string.IsNullOrWhiteSpace(queryModel.TargetGroupsCriterion))
{
sql += @" AND N'" + queryModel.TargetGroupsCriterion + @"' IN ( SELECT * FROM Configuration.dbo.SplitString(TargetGroups, ',', 1))";
}
if (queryModel.SortCriterion != null)
{
sql += @" AND Sort=" + queryModel.SortCriterion.Value;
}
if (queryModel.StartDateCriterion != null)
{
sql += @" AND CreateTime>='" + queryModel.StartDateCriterion.Value + @"'";
}
if (queryModel.EndDateCriterion != null)
{
sql += @" AND CreateTime<='" + queryModel.EndDateCriterion.Value + @"'";
}
if (!string.IsNullOrWhiteSpace(queryModel.CreatorCriterion))
{
sql += @" AND Creator LIKE N'%" + queryModel.CreatorCriterion + @"%'";
}
if (!string.IsNullOrWhiteSpace(queryModel.TargetSmallAppIdCriterion))
{
sql += $" AND TargetSmallAppId = N'{queryModel.TargetSmallAppIdCriterion}'";
}
sql += @"ORDER BY Id desc OFFSET (@page - 1) * 50 ROWS FETCH NEXT 50 ROWS ONLY";
var sqlParam = new[] { new SqlParameter("@page", page) };
return(SqlHelper.ExecuteDataTable(conn, CommandType.Text, sql, sqlParam).ConvertTo <BannerConfig>().ToList());
}
public int CountBannerConfig(BannerFilterQuery queryModel)
{
return(DalBannerConfig.CountBannerConfig(conn, queryModel));
}
public List <BannerConfig> QueryBannerConfig(BannerFilterQuery queryModel, int page)
{
return(DalBannerConfig.QueryBannerConfig(conn, queryModel, page));
}