/// <summary>
/// 在指定栏目下搜索关键词相关的内容
/// </summary>
/// <param name="categoryRgt"></param>
/// <param name="keyword"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pageCount"></param>
/// <param name="orderby"></param>
/// <param name="siteId"></param>
/// <param name="categoryLft"></param>
/// <param name="func"></param>
/// <returns></returns>
public void SearchArchivesByCategory(int siteId,
int categoryLft, int categoryRgt, string keyword,
int pageSize, int currentPageIndex,
out int recordCount, out int pageCount,
string orderby, DataReaderFunc func)
{
const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' ";
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY CreateDate DESC");
}
object[,] data = new object[, ]
{
{ "@siteId", siteId },
{ "@lft", categoryLft },
{ "@rgt", categoryRgt }
};
//为第一页时
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_Archives.[ID] AS ID,*
FROM $PREFIX_Archives INNER JOIN $PREFIX_categories
ON $PREFIX_Archives.[CgID]=$PREFIX_categories.[ID]
WHERE $[condition] AND $PREFIX_categories.siteid=@siteId AND ($PREFIX_categories.lft>=@lft
AND $PREFIX_categories.rgt<=@rgt) AND ([Title] LIKE
'%$[keyword]%' OR [Outline] LIKE '%$[keyword]%'
OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%')
$[orderby],$PREFIX_Archives.[ID]";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
SqlQueryHelper.Format(SP.Archive_GetSearchRecordCountByCategoryID,
data,
keyword,
condition
)).ToString());
//页数
pageCount = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pageCount++;
}
//对当前页数进行验证
if (currentPageIndex > pageCount && currentPageIndex != 1)
{
currentPageIndex = pageCount;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//跳过记录数
int skipCount = pageSize * (currentPageIndex - 1);
string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB ?
sql1:
SP.Archive_GetPagedSearchArchivesByCategoryID;
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "siteid": return(siteId.ToString());
case "condition": return(condition);
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "keyword": return(keyword);
case "orderby": return(orderby);
}
return(null);
});
base.ExecuteReader(SqlQueryHelper.Format(sql, data), func);
}
/// <summary>
/// 搜索关键词相关的内容
/// </summary>
/// <param name="keyword"></param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pageCount"></param>
/// <param name="orderby"></param>
/// <returns></returns>
public void SearchArchives(int siteID, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby, DataReaderFunc func)
{
/*
* string condition = ArchiveFlag.GetSQLString(new string[,]{
* {"st","0"},
* {"v","1"}
* });
*/
const string condition = " flags LIKE '%st:''0''%'AND flags LIKE '%v:''1''%' ";
//排序规则
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY Createdate DESC");
}
//数据库为OLEDB,且为第一页时
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_Archives.[ID] AS ID,* FROM $PREFIX_Archives INNER JOIN $PREFIX_categories ON $PREFIX_Archives.[CID]=$PREFIX_categories.[ID]
WHERE $[condition] AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_Archives.[ID]";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
SqlQueryHelper.Format(SP.Archive_GetSearchRecordCount, keyword, siteID.ToString(), condition)
).ToString());
//页数
pageCount = recordCount / pageSize;
if (recordCount % pageSize != 0)
{
pageCount++;
}
//对当前页数进行验证
if (currentPageIndex > pageCount && currentPageIndex != 1)
{
currentPageIndex = pageCount;
}
if (currentPageIndex < 1)
{
currentPageIndex = 1;
}
//跳过记录数
int skipCount = pageSize * (currentPageIndex - 1);
//如果调过记录为0条，且为OLEDB时候，则用sql1
string sql = skipCount == 0 && base.DbType == DataBaseType.OLEDB?
sql1:
SP.Archive_GetPagedSearchArchives;
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "condition": return(condition);
case "siteid": return(siteID.ToString());
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "keyword": return(keyword);
case "orderby": return(orderby);
}
return(null);
});
base.ExecuteReader(new SqlQuery(base.OptimizeSQL(sql)), func);
}
