/// <summary>
/// 获取栏目分页文档
/// </summary>
/// <param name="lft"></param>
/// <param name="rgt"></param>
/// <param name="siteId"></param>
/// <param name="moduleId">参数暂时不使用为-1</param>
/// <param name="publisherId"></param>
/// <param name="includeChild"></param>
/// <param name="flags"></param>
/// <param name="keyword"></param>
/// <param name="orderByField"></param>
/// <param name="orderAsc"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pages"></param>
/// <returns></returns>
public DataTable GetPagedArchives(int siteId, int moduleId,
int[] catIdArray, int publisherId, bool includeChild,
string[,] flags, string keyword, string orderByField, bool orderAsc,
int pageSize, int currentPageIndex,
out int recordCount, out int pages)
{
//SQL Condition Template
const string conditionTpl = "$[siteid]$[module]$[category]$[author_id]$[flags]$[keyword]";
string condition, //SQL where condition
order = String.IsNullOrEmpty(orderByField) ? "a.sort_number" : orderByField, //Order filed ( CreateDate | ViewCount | Agree | Disagree )
orderType = orderAsc ? "ASC" : "DESC"; //ASC or DESC
string flag = ArchiveFlag.GetSQLString(flags);
condition = SQLRegex.Replace(conditionTpl, match =>
{
switch (match.Groups[1].Value)
{
case "siteid": return(String.Format(" c.site_id={0}", siteId.ToString()));
case "category":
if (catIdArray.Length > 0)
{
if (includeChild && catIdArray.Length > 1)
{
return(String.Format(" AND cat_id IN({0})", this.IntArrayToString(catIdArray)));
}
return(String.Format(" AND cat_id = {0}", catIdArray[0]));
}
return(String.Empty);
case "module":
return(moduleId <= 0 ? ""
: String.Format(" AND m.id={0}", moduleId.ToString()));
case "author_id":
return(publisherId == 0 ? null
: String.Format(" AND author_id='{0}'", publisherId));
case "flags": return(String.IsNullOrEmpty(flag) ? "" : " AND " + flag);
case "keyword":
if (String.IsNullOrEmpty(keyword))
{
return("");
}
return(String.Format(" AND (title LIKE '%{0}%' OR Content LIKE '%{0}%')", keyword));
}
return(null);
});
// throw new Exception(base.OptimizeSql(String.Format(DbSql.Archive_GetpagedArchivesCountSql, condition)));
//获取记录条数
recordCount = int.Parse(base.ExecuteScalar(
base.NewQuery(String.Format(DbSql.ArchiveGetpagedArchivesCountSql, condition), DalBase.EmptyParameter)).ToString());
pages = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pages++;
}
//验证当前页数
if (currentPageIndex > pages && currentPageIndex != 1)
{
currentPageIndex = pages;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//计算分页
int skipCount = pageSize * (currentPageIndex - 1);
string sql = DbSql.Archive_GetPagedArchivesByCategoryId;
sql = SQLRegex.Replace(sql, m =>
{
switch (m.Groups[1].Value)
{
case "skipsize": return(skipCount.ToString());
case "pagesize": return(pageSize.ToString());
case "condition": return(condition);
case "orderByField": return(order);
case "orderASC": return(orderType);
}
return(null);
});
//throw new Exception(new SqlQuery(base.OptimizeSQL(sql));
//throw new Exception(sql+"-"+DbHelper.DbType.ToString()+"-"+new SqlQuery(base.OptimizeSQL(SP.ToString());
//System.Web.HttpContext.Current.Response.Write(sql);
//throw new Exception(sql);
return(base.GetDataSet(base.NewQuery(sql, null)).Tables[0]);
}
/// <summary>
/// 获取栏目分页文档
/// </summary>
/// <param name="lft"></param>
/// <param name="rgt"></param>
/// <param name="author">指定作者，NULL表示不限，可显示所有作者发布的文档</param>
/// <param name="siteId"></param>
/// <param name="moduleId">参数暂时不使用为-1</param>
/// <param name="flags"></param>
/// <param name="orderByField"></param>
/// <param name="orderAsc"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pages"></param>
/// <returns></returns>
public DataTable GetPagedArchives(int siteId, int moduleId,
int lft, int rgt, string author,
string[,] flags, string orderByField, bool orderAsc,
int pageSize, int currentPageIndex,
out int recordCount, out int pages)
{
//SQL Condition Template
const string conditionTpl = "$[siteid]$[module]$[category]$[author]$[flags]";
//Get records count
//{0}==$[condition]
const string sql1 = @"SELECT TOP $[pagesize] a.id AS id,alias,title,
c.name as CategoryName,cid,flags,author,content,source,
createdate,viewcount FROM $PREFIX_archive a
INNER JOIN $PREFIX_category c ON a.cid=c.id
WHERE $[condition] ORDER BY $[orderByField] $[orderASC],a.id";
string condition, //SQL where condition
order = String.IsNullOrEmpty(orderByField) ? "CreateDate" : orderByField, //Order filed ( CreateDate | ViewCount | Agree | Disagree )
orderType = orderAsc ? "ASC" : "DESC"; //ASC or DESC
string flag = ArchiveFlag.GetSQLString(flags);
condition = SQLRegex.Replace(conditionTpl, match =>
{
switch (match.Groups[1].Value)
{
case "siteid": return(String.Format(" c.siteid={0}", siteId.ToString()));
case "category": return(lft <= 0 || rgt <= 0 ? ""
: String.Format(" AND lft>={0} AND rgt<={1}", lft.ToString(), rgt.ToString()));
case "module": return(moduleId <= 0 ? ""
:String.Format(" AND m.id={0}", moduleId.ToString()));
case "author": return(String.IsNullOrEmpty(author) ? null
: String.Format(" AND author='{0}'", author));
case "flags": return(String.IsNullOrEmpty(flag)?"": " AND " + flag);
}
return(null);
});
// throw new Exception(new SqlQuery(base.OptimizeSQL(String.Format(SP.Archive_GetpagedArchivesCountSql, condition)));
//获取记录条数
recordCount = int.Parse(base.ExecuteScalar(
new SqlQuery(base.OptimizeSQL(String.Format(SP.Archive_GetpagedArchivesCountSql, condition)), null)).ToString());
pages = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pages++;
}
//验证当前页数
if (currentPageIndex > pages && currentPageIndex != 1)
{
currentPageIndex = pages;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//计算分页
int skipCount = pageSize * (currentPageIndex - 1);
//如果调过记录为0条，且为OLEDB时候，则用sql1
string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ?
sql1 :
SP.Archive_GetPagedArchivesByCategoryId;
sql = SQLRegex.Replace(sql, m =>
{
switch (m.Groups[1].Value)
{
case "skipsize": return(skipCount.ToString());
case "pagesize": return(pageSize.ToString());
case "condition": return(condition);
case "orderByField": return(order);
case "orderASC": return(orderType);
}
return(null);
});
//throw new Exception(new SqlQuery(base.OptimizeSQL(sql));
//throw new Exception(sql+"-"+DbHelper.DbType.ToString()+"-"+new SqlQuery(base.OptimizeSQL(SP.ToString());
//System.Web.HttpContext.Current.Response.Write(sql);
//throw new Exception(sql);
return(base.GetDataSet(new SqlQuery(base.OptimizeSQL(sql))).Tables[0]);
}
