/// <summary>
/// 获取指定位置的广告信息集合
/// </summary>
/// <param name="advType">广告投放类型</param>
/// <param name="relationId">关联编号(城市或单位类型编号)</param>
/// <param name="position">广告位置</param>
/// <param name="date">日期</param>
/// <param name="expression">指定返回行数的数值表达式</param>
/// <param name="isPlatformAd">是否取广告类别为同业114广告</param>
/// <returns></returns>
public virtual IList <EyouSoft.Model.AdvStructure.AdvInfo> GetAdvs(EyouSoft.Model.AdvStructure.AdvType advType, int relationId, EyouSoft.Model.AdvStructure.AdvPosition position, DateTime date, int expression, bool isPlatformAd)
{
IList <EyouSoft.Model.AdvStructure.AdvInfo> advs = new List <EyouSoft.Model.AdvStructure.AdvInfo>();
StringBuilder cmdText = new StringBuilder();
cmdText.Append(" SELECT TOP (@Expression) A.Id,A.AdvDescript,A.AdvLink,B.AdvImg,B.AdvThumbnail,A.CompanyId,A.CompanyName,A.IssueTime,A.AdvRemark,D.MQ FROM tbl_SysAdv AS A ");
cmdText.Append(" LEFT OUTER JOIN tbl_SysAdvImg AS B ON A.Id=B.AdvId ");
cmdText.Append(" INNER JOIN tbl_SysAdvAreaControl AS C ON A.Id=C.AdvId AND C.AreaType=@AdvType AND C.AreaId=@RelationId ");
cmdText.Append(" LEFT OUTER JOIN tbl_CompanyUser AS D ON A.CompanyId=D.CompanyId AND IsAdmin='1' ");
cmdText.Append(" WHERE A.AreaId=@PositionId AND @Date BETWEEN A.StartDate AND A.EndDate ");
if (isPlatformAd)
{
cmdText.Append(" AND A.ClassId=3 ");
}
else
{
cmdText.Append(" AND A.ClassId<3 ");
}
cmdText.Append(" ORDER BY C.SortId ASC,A.IssueTime ASC ");
DbCommand cmd = base.SystemStore.GetSqlStringCommand(cmdText.ToString());
base.SystemStore.AddInParameter(cmd, "Expression", DbType.Int32, expression);
base.SystemStore.AddInParameter(cmd, "AdvType", DbType.Byte, advType);
base.SystemStore.AddInParameter(cmd, "RelationId", DbType.Int32, relationId);
base.SystemStore.AddInParameter(cmd, "PositionId", DbType.Int32, position);
base.SystemStore.AddInParameter(cmd, "Date", DbType.DateTime, date);
using (IDataReader rdr = DbHelper.ExecuteReader(cmd, base.SystemStore))
{
while (rdr.Read())
{
EyouSoft.Model.AdvStructure.AdvInfo tmp = new EyouSoft.Model.AdvStructure.AdvInfo();
tmp.AdvId = rdr.GetInt32(0);
tmp.Title = rdr[1].ToString();
tmp.RedirectURL = rdr[2].ToString();
tmp.ImgPath = rdr[3].ToString();
tmp.AdvThumb = rdr[4].ToString();
tmp.CompanyId = rdr.GetString(5);
tmp.CompanyName = rdr[6].ToString();
tmp.IssueTime = rdr.GetDateTime(7);
tmp.Remark = rdr[8].ToString();
tmp.ContactMQ = rdr[9].ToString();
advs.Add(tmp);
}
}
return(advs);
}
/// <summary>
/// 设置广告排序
/// </summary>
/// <param name="advId">广告编号</param>
/// <param name="advType">广告投放类型</param>
/// <param name="relationId">关联编号</param>
/// <param name="sortId">排序编号</param>
/// <returns></returns>
public virtual bool SetAdvSort(int advId, EyouSoft.Model.AdvStructure.AdvType advType, int relationId, int sortId)
{
DbCommand cmd = base.SystemStore.GetSqlStringCommand(SQL_UPDATE_SetAdvSort);
base.SystemStore.AddInParameter(cmd, "SortId", DbType.Int32, sortId);
base.SystemStore.AddInParameter(cmd, "AdvId", DbType.Int32, advId);
base.SystemStore.AddInParameter(cmd, "RelationId", DbType.Int32, relationId);
base.SystemStore.AddInParameter(cmd, "AdvType", DbType.Byte, advType);
return(DbHelper.ExecuteSql(cmd, base.SystemStore) == 1 ? true : false);
}
/// <summary>
/// 按照指定条件获取广告信息集合
/// </summary>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="position">广告位置 为空时不做为查询条件</param>
/// <param name="advType">广告投放类型</param>
/// <param name="relationId">关联编号(城市或单位类型编号)</param>
/// <param name="companyName">公司名称 为null时不做为查询条件</param>
/// <param name="category">广告类别 为null时不做为查询条件</param>
/// <param name="startDate">有效期起始时间 为null时不做为查询条件</param>
/// <param name="endDate">有效期截止时间 为null时不做为查询条件</param>
/// <param name="catalog">广告栏目(频道)</param>
/// <param name="title">广告标题 为null时不做为查询条件</param>
/// <returns></returns>
public virtual IList <EyouSoft.Model.AdvStructure.AdvInfo> GetAdvs(int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.AdvStructure.AdvPosition?position
, EyouSoft.Model.AdvStructure.AdvType advType, int relationId
, string companyName, EyouSoft.Model.AdvStructure.AdvCategory?category, DateTime?startDate, DateTime?endDate
, EyouSoft.Model.AdvStructure.AdvCatalog?catalog, string title)
{
IList <EyouSoft.Model.AdvStructure.AdvInfo> advs = new List <EyouSoft.Model.AdvStructure.AdvInfo>();
StringBuilder cmdQuery = new StringBuilder();
string tableName = "tbl_SysAdv";
string primaryKey = "Id";
string orderByString = "IssueTime ASC";
StringBuilder fields = new StringBuilder();
fields.Append("*");
fields.Append(",(SELECT AdvImg FROM tbl_SysAdvImg WHERE AdvId=tbl_SysAdv.Id) AS AdvImg");
fields.Append(",(SELECT AdvThumbnail FROM tbl_SysAdvImg WHERE AdvId=tbl_SysAdv.Id) AS AdvThumb");
fields.AppendFormat(",(SELECT SortId FROM tbl_SysAdvAreaControl WHERE AdvId=tbl_SysAdv.Id AND AreaType={0} AND AreaId={1}) AS SortId ", (int)advType, relationId);
fields.Append(",(SELECT MQ FROM tbl_CompanyUser WHERE CompanyId=tbl_SysAdv.CompanyId AND IsAdmin='1' ) AS ContactMQ");
#region 拼接查询条件
cmdQuery.Append(" 1=1 ");
if (relationId > 0)
{
cmdQuery.AppendFormat(" AND EXISTS(SELECT 1 FROM tbl_SysAdvAreaControl WHERE AdvId=tbl_SysAdv.Id AND AreaType={0} AND AreaId={1}) ", (int)advType, relationId);
}
if (position.HasValue)
{
cmdQuery.AppendFormat(" AND AreaId={0} ", (int)position.Value);
}
if (!string.IsNullOrEmpty(companyName))
{
cmdQuery.AppendFormat(" AND CompanyName LIKE '%{0}%' ", companyName);
}
if (category.HasValue)
{
cmdQuery.AppendFormat(" AND ClassId={0} ", (int)category.Value);
}
if (startDate.HasValue || endDate.HasValue)
{
/*cmdQuery.Append(" AND(1=0 ");
* if (startDate.HasValue)
* {
* cmdQuery.AppendFormat(" OR '{0}' BETWEEN StartDate AND EndDate ", startDate.Value);
* }
* if (endDate.HasValue)
* {
* cmdQuery.AppendFormat(" OR '{0}' BETWEEN StartDate AND EndDate ", endDate.Value);
* }
* cmdQuery.Append(")");*/
cmdQuery.Append(" AND (EndDate>='9999-12-31' ");
cmdQuery.Append(" OR (1=1 ");
if (startDate.HasValue)
{
cmdQuery.AppendFormat(" AND StartDate>='{0}' ", startDate.Value);
}
if (endDate.HasValue)
{
cmdQuery.AppendFormat(" AND EndDate<='{0}' ", endDate.Value);
}
cmdQuery.Append(" ) ");
cmdQuery.Append(" ) ");
}
if (catalog.HasValue)
{
cmdQuery.AppendFormat(" AND AreaId IN(SELECT AreaId FROM tbl_SysAdvArea WHERE CatalogId={0}) ", (int)catalog.Value);
}
if (!string.IsNullOrEmpty(title))
{
cmdQuery.AppendFormat(" AND AdvDescript LIKE '%{0}%' ", title);
}
#endregion
using (IDataReader rdr = DbHelper.ExecuteReader(base.SystemStore, pageSize, pageIndex, ref recordCount, tableName, primaryKey, fields.ToString(), cmdQuery.ToString(), orderByString))
{
while (rdr.Read())
{
EyouSoft.Model.AdvStructure.AdvInfo tmp = new EyouSoft.Model.AdvStructure.AdvInfo();
tmp.AdvId = rdr.GetInt32(rdr.GetOrdinal("Id"));
tmp.AdvType = advType;
tmp.Category = (EyouSoft.Model.AdvStructure.AdvCategory)rdr.GetByte(rdr.GetOrdinal("ClassId"));
tmp.CompanyId = rdr.GetString(rdr.GetOrdinal("CompanyId"));
tmp.CompanyName = rdr["CompanyName"].ToString();
tmp.ContactInfo = rdr["ContactInfo"].ToString();
tmp.EndDate = rdr.GetDateTime(rdr.GetOrdinal("EndDate"));
tmp.ImgPath = rdr["AdvImg"].ToString();
tmp.AdvThumb = rdr["AdvThumb"].ToString();
tmp.IssueTime = rdr.GetDateTime(rdr.GetOrdinal("IssueTime"));
tmp.OperatorId = rdr.GetInt32(rdr.GetOrdinal("OperatorId"));
tmp.OperatorName = rdr["OperatorName"].ToString();
tmp.Position = (EyouSoft.Model.AdvStructure.AdvPosition)rdr.GetInt32(rdr.GetOrdinal("AreaId"));
tmp.Range = (EyouSoft.Model.AdvStructure.AdvRange)rdr.GetByte(rdr.GetOrdinal("AdvArea"));
tmp.RedirectURL = rdr["AdvLink"].ToString();
//tmp.Relation
tmp.Remark = rdr.IsDBNull(rdr.GetOrdinal("AdvRemark"))
? string.Empty
: rdr.GetString(rdr.GetOrdinal("AdvRemark"));
if (!rdr.IsDBNull(rdr.GetOrdinal("SortId")))
{
tmp.SortId = rdr.GetInt32(rdr.GetOrdinal("SortId"));
}
tmp.StartDate = rdr.GetDateTime(rdr.GetOrdinal("StartDate"));
tmp.Title = rdr["AdvDescript"].ToString();
tmp.ContactMQ = rdr["ContactMQ"].ToString();
advs.Add(tmp);
}
}
return(advs);
}
/// <summary>
/// 获取快到期广告信息集合
/// </summary>
/// <param name="pageSize">每页显示记录数</param>
/// <param name="pageIndex">当前页索引</param>
/// <param name="recordCount">总记录数</param>
/// <param name="advType">广告投放类型</param>
/// <param name="relationId">关联编号(城市或单位类型编号)</param>
/// <param name="catelog">栏目编号 为null时不做为查询条件</param>
/// <param name="companyName">公司名称 为null时不做为查询条件</param>
/// <returns></returns>
public IList <EyouSoft.Model.AdvStructure.AdvInfo> GetComingExpireAdvs(int pageSize, int pageIndex, ref int recordCount, EyouSoft.Model.AdvStructure.AdvType advType
, int relationId, EyouSoft.Model.AdvStructure.AdvCatalog?catelog, string companyName)
{
DateTime?startDate = DateTime.Today;
DateTime?endDate = startDate.Value.AddMonths(1);
return(dal.GetAdvs(pageSize, pageIndex, ref recordCount, null, advType,
relationId, companyName, null, startDate, endDate, catelog, null));
}
