/// <summary>
/// 生成拼接sql参数列表
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
public List <SqlParameter> ParseToSqlParameters(searchFestivalEntity 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>
/// <param name="totalcnt"></param>
/// <returns></returns>
public DataTable GetFestivalList(searchFestivalEntity entity, out int totalcnt)
{
DataTable table = null;
totalcnt = 0;
try
{
SqlParameter[] prms = ParseToSqlParameters(entity).ToArray();
if (entity.UseDBPagination)
{
table = SQlHelper.ExecuteDataset(SQlHelper.MyConnectStr, CommandType.StoredProcedure, "sp_pager06", prms).Tables[0];
totalcnt = int.Parse(prms[prms.Length - 1].Value.ToString());
}
else
{
throw new NotImplementedException();
}
}
catch (Exception ex)
{
totalcnt = -1;
LogUtil.WriteLog(ex);
}
return(table);
}
/// <summary>
/// 设置表关联
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPTable(searchFestivalEntity entity)
{
StringBuilder sbtable = new StringBuilder();
//基本表
sbtable.Append(@" M_Festival AS f
LEFT JOIN dbo.M_EventItem AS e ON e.FestivalID = f.FestivalID");
return(new SqlParameter("@TableName", sbtable.ToString()));
}
/// <summary>
/// 查询条件
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPWhere(searchFestivalEntity entity)
{
StringBuilder sbwhere = new StringBuilder("1=1");
if (!string.IsNullOrEmpty(entity.StartTime))
{
sbwhere.Append("and f.StartTime>='" + entity.StartTime + " 00:00:00'");
}
if (!string.IsNullOrEmpty(entity.EndTime))
{
sbwhere.Append("and f.EndTime<='" + entity.EndTime + " 23:59:59'");
}
return(new SqlParameter("@sqlWhere", sbwhere.ToString()));
}
/// <summary>
/// 设置排序
/// </summary>
/// <param name="enity"></param>
/// <returns></returns>
private SqlParameter CPOrder(searchFestivalEntity enity)
{
StringBuilder sborder = new StringBuilder();
if (enity.OrderfieldType == OrderFieldType.Desc)
{
sborder.Append(" f.EndTime DESC ");
}
else
{
sborder.Append(" f.EndTime ASC ");
}
return(new SqlParameter("@OrderField", sborder.ToString()));
}
/// <summary>
/// 设置显示字段
/// </summary>
/// <param name="entity"></param>
/// <returns></returns>
private SqlParameter CPFields(searchFestivalEntity entity)
{
StringBuilder sbfileds = new StringBuilder();
if (entity.UseDBPagination)
{
sbfileds.Append(@" f.FestivalID, f.FestivalName,f.FestivalShortName,convert(varchar(11),f.StartTime,120) as StartTime
,convert(varchar(11),f.EndTime,120) as EndTime,f.FestivalType,f.FestivalWeight
,e.EventItemGUID,e.Title");
}
else
{
throw new NotImplementedException();
}
return(new SqlParameter("@Fields", sbfileds.ToString()));
}
private DataTable GetQueryData(bool isDownload)
{
int totalcnt = 0;
BCtrl_Festival bll = new BCtrl_Festival();
searchFestivalEntity entity = new searchFestivalEntity();
if (!string.IsNullOrEmpty(txtStartTime))
{
entity.StartTime = txtStartTime;
}
if (!string.IsNullOrEmpty(txtEndTime))
{
entity.EndTime = txtEndTime;
}
entity.PageSize = base.PageSize;
entity.PageIndex = base.PageIndex;
entity.UseDBPagination = !isDownload;
entity.OrderfieldType = OrderFieldType.Desc;
DataTable db = bll.GetFestivalList(entity, out totalcnt);
base.TotalRecords = totalcnt;
return(db);
}
public DataTable GetFestivalList(searchFestivalEntity entity, out int totalcnt)
{
return(fesobj.GetFestivalList(entity, out totalcnt));
}
