/// <summary>
/// 生成拼接sql参数列表
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List <SqlParameter> ParseToSqlParameters(EventItemGroupSearchEntity entity)
{
List <SqlParameter> paraList = new List <SqlParameter>();
//table
paraList.Add(CPTable(entity));
//fields
paraList.Add(CPFields(entity));
//filter_SqlWhere
paraList.Add(CPWhere(entity));
//order
paraList.Add(CPOrder(entity));
//pagesize
paraList.Add(new SqlParameter("@pageSize", entity.PageSize));
//pageindex
paraList.Add(new SqlParameter("@pageIndex", entity.PageIndex));
paraList.Add(new SqlParameter()
{
ParameterName = "@Records", Value = 0, Direction = ParameterDirection.Output
});
return(paraList);
}
/// <summary>
/// 设置表关联
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPTable(EventItemGroupSearchEntity entity)
{
StringBuilder sbtable = new StringBuilder();
//基本表
sbtable.Append(" M_EventItemGroups ");
return(new SqlParameter("@TableName", sbtable.ToString()));
}
/// <summary>
/// 设置显示字段
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPFields(EventItemGroupSearchEntity entity)
{
StringBuilder sbfileds = new StringBuilder();
if (entity.UseDBPagination)
{
sbfileds.Append(" GroupEventID,GroupEventName,CONVERT(VARCHAR(10),CreatedTime,121) AS CreatedTime,CONVERT(VARCHAR(16),PublishTime,121) AS PublishTime,GroupState");
}
else
{
throw new NotImplementedException();
}
return(new SqlParameter("@Fields", sbfileds.ToString()));
}
/// <summary>
/// 设置排序
/// </summary>
/// <param name="enity"></param>
/// <returns></returns>
private SqlParameter CPOrder(EventItemGroupSearchEntity enity)
{
StringBuilder sborder = new StringBuilder();
if (enity.OrderfieldType == OrderFieldType.Desc)
{
sborder.AppendFormat(" GroupEventID DESC");
}
else
{
sborder.AppendFormat(" GroupEventID ASC");
}
return(new SqlParameter("@OrderField", sborder.ToString()));
}
private DataTable GetQueryData(bool isDownload)
{
int totalcnt = 0;
DataTable table = null;
BCtrl_EventItemGroup bll = new BCtrl_EventItemGroup();
EventItemGroupSearchEntity entity = new EventItemGroupSearchEntity();
entity.GroupEventID = GroupEventID;
// entity.EventItemIDs = ArticleIds;
entity.PageSize = base.PageSize;
entity.PageIndex = base.PageIndex;
table = bll.QueryGroupRelViewList(entity, out totalcnt);
base.TotalRecords = totalcnt;
return(table);
}
public DataTable QueryGroupTable(EventItemGroupSearchEntity searchEntity, out int totalcnt)
{
DataTable table = null;
totalcnt = 0;
try
{
SqlParameter[] prms = ParseToSqlParameters(searchEntity).ToArray();
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = Convert.ToInt32(prms[prms.Length - 1].Value);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(null);
}
return(table);
}
private DataTable GetQueryData(bool isDownload)
{
BCtrl_EventItemGroup bll = new BCtrl_EventItemGroup();
int totalcnt = 0;
EventItemGroupSearchEntity entity = new EventItemGroupSearchEntity();
entity.GroupEventName = GroupEventName;
entity.GroupState = GroupState;
entity.PageSize = base.PageSize;
entity.PageIndex = base.PageIndex;
entity.UseDBPagination = !isDownload;
DataTable table = bll.QueryGroupTable(entity, out totalcnt);
base.TotalRecords = totalcnt;
return(table);
}
public DataTable QueryGroupRelViewList(EventItemGroupSearchEntity searchEntity, out int totalcnt)
{
DataTable table = null;
totalcnt = 0;
try
{
List <SqlParameter> prmslist = new List <SqlParameter>();
prmslist.Add(new SqlParameter("@TableName", " M_V_EventItemGroupRel "));
string fields = @" Title, EventGroupID,EventItemID,DisplayOrder,FirstTypName,SecondTypeName,GroupState,CarouselPictureID, IsSingleGroupState,PictureID";
prmslist.Add(new SqlParameter("@Fields", fields));
prmslist.Add(new SqlParameter("@OrderField", " DisplayOrder ASC"));
string wheresql = " EventGroupID=" + searchEntity.GroupEventID;
prmslist.Add(new SqlParameter("@sqlWhere", wheresql));
prmslist.Add(new SqlParameter("@pageIndex", searchEntity.PageIndex));
prmslist.Add(new SqlParameter("@pageSize", searchEntity.PageSize));
prmslist.Add(new SqlParameter()
{
ParameterName = "@Records", Value = 0, Direction = ParameterDirection.Output
});
SqlParameter[] prms = prmslist.ToArray();
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = Convert.ToInt32(prms[prms.Length - 1].Value);
}
catch (Exception ex)
{
LogUtil.WriteLog(ex);
return(null);
}
return(table);
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPWhere(EventItemGroupSearchEntity entity)
{
StringBuilder sbwhere = new StringBuilder(" 1=1 ");
sbwhere.Append(" AND GroupState=" + entity.GroupState);
if (!string.IsNullOrEmpty(entity.GroupEventName))
{
sbwhere.Append(" AND GroupEventName like '" + entity.GroupEventName.Trim() + "%'");
}
if (entity.PublishStartTime.HasValue)
{
sbwhere.Append(" AND PublishTime >= '" + entity.PublishStartTime.Value.Date + "'");
}
if (entity.PublishEndTime.HasValue)
{
sbwhere.Append(" AND PublishTime <= '" + entity.PublishEndTime.Value.AddDays(1).Date.AddSeconds(-1) + "' ");
}
return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
}
public DataTable QueryGroupRelViewList(EventItemGroupSearchEntity searchEntity, out int totalcnt)
{
return(dao.QueryGroupRelViewList(searchEntity, out totalcnt));
}
