/// <summary>
/// 获取行数据
/// </summary>
/// <param name="ids"></param>
/// <returns></returns>
public DataTable GetRowsData(int[] ids)
{
string rowRange = String.Empty;
int i = 0;
Array.ForEach(ids, a =>
{
if (++i != 1)
{
rowRange += ",";
}
rowRange += a.ToString();
});
string sql = base.OptimizeSql(DbSql.table_GetRowData);
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "range": return(rowRange);
}
return(null);
});
return(base.GetDataSet(new SqlQuery(sql)).Tables[0]);
}
/************ 分页 ****************/
/// <summary>
/// 获取栏目分页文档
/// </summary>
/// <param name="siteId"></param>
/// <param name="lft"></param>
/// <param name="pageSize"></param>
/// <param name="skipSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="rgt"></param>
/// <param name="recordCount"></param>
/// <param name="pages"></param>
/// <returns></returns>
public DataTable GetPagedArchives(
int siteId, int[] catIdArray,
int pageSize, int skipSize, ref int currentPageIndex,
out int recordCount, out int pages)
{
object[,] data = new object[, ] {
{ "@siteId", siteId },
};
String catIdArrayString = this.IntArrayToString(catIdArray);
String sql = SQLRegex.Replace(DbSql.ArchiveGetPagedArchivesCountSqlPagerqurey, m =>
{
switch (m.Groups[1].Value)
{
case "catIdArray": return(catIdArrayString);
}
return(String.Empty);
});
//获取记录条数
recordCount = int.Parse(base.ExecuteScalar(SqlQueryHelper.Format(sql, data)).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
sql = DbSql.ArchiveGetPagedArchivesByCategoryIdPagerquery;
sql = SQLRegex.Replace(sql, m =>
{
switch (m.Groups[1].Value)
{
case "catIdArray": return(catIdArrayString);
case "skipsize": return((skipCount + skipSize).ToString());
case "pagesize": return(pageSize.ToString());
}
return(null);
});
return(base.GetDataSet(SqlQueryHelper.Format(sql, data)).Tables[0]);
}
public DataTable GetPagedAvailableOperationList(bool available, int pageSize, int currentPageIndex,
out int recordCount, out int pageCount)
{
const string sql1 = "SELECT TOP $[pagesize] * FROM $PREFIX_operation WHERE $[condition]";
string condition = available ? "available" : "available=false";
recordCount = int.Parse(
base.ExecuteScalar(
new SqlQuery(
String.Format(base.OptimizeSql(DbSql.Operation_GetOperationsCountByAvailable), condition), DalBase.EmptyParameter)
).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
? base.OptimizeSql(sql1)
: base.OptimizeSql(DbSql.Archive_GetPagedOperationsByAvialble);
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "pagesize":
return(pageSize.ToString());
case "skipsize":
return((pageSize * (currentPageIndex - 1)).ToString());
case "condition":
return(condition);
}
return(null);
});
return(base.GetDataSet(new SqlQuery(sql, DalBase.EmptyParameter)).Tables[0]);
}
public DataTable GetPagedOperationList(int pageSize, int currentPageIndex, out int recordCount,
out int pageCount)
{
const string sql1 = "SELECT TOP $[pagesize] * FROM $PREFIX_operation";
//计算页码
recordCount =
int.Parse(
base.ExecuteScalar(base.NewQuery(DbSql.Operation_GetOperationCount, DalBase.EmptyParameter))
.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
? base.OptimizeSql(sql1)
: base.OptimizeSql(DbSql.Archive_GetPagedOperations);
sql = SQLRegex.Replace(sql,
(match) =>
{
switch (match.Groups[1].Value)
{
case "pagesize":
return(pageSize.ToString());
case "skipsize":
return(skipCount.ToString());
}
return(null);
});
return(base.GetDataSet(
new SqlQuery(sql, DalBase.EmptyParameter)
).Tables[0]);
}
/// <summary>
/// 获取分页消息
/// </summary>
/// <param name="uid">用户ID</param>
/// <param name="type">消息类型</param>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pageCount"></param>
/// <returns></returns>
public DataTable GetPagedMessage(int uid, int typeID, int pageSize, ref int currentPageIndex, out int recordCount, out int pageCount)
{
const string sql1 = "SELECT TOP $[pagesize] * FROM $PREFIX_Message WHERE Recycle=0 AND $[condition]";
string condition = String.Format(typeID == 1 ? "[receiveuid]={0}" : "[senduid]={0}", uid);
recordCount = int.Parse(base.ExecuteScalar(new SqlQuery(
String.Format(base.OptimizeSql(DbSql.Message_GetPagedMessagesCount), condition), DalBase.EmptyParameter)
).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 ?
base.OptimizeSql(sql1) :
base.OptimizeSql(DbSql.Message_GetPagedMessages);
sql = SQLRegex.Replace(sql, match =>
{
switch (match.Groups[1].Value)
{
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "condition": return(condition.ToString());
}
return(null);
});
return(base.GetDataSet(new SqlQuery(sql)).Tables[0]);
}
/// <summary>
/// 获取会员列表
/// </summary>
/// <param name="pageSize"></param>
/// <param name="currentPageIndex"></param>
/// <param name="recordCount"></param>
/// <param name="pageCount"></param>
/// <param name="time"></param>
/// <returns></returns>
public DataTable GetPagedMembers(int pageSize, ref int currentPageIndex, out int recordCount, out int pages, DateTime?time)
{
//ACCESS时候第一页执行
const string sql1 = @"SELECT TOP $[pagesize] [id],[username],[avatar],[nickname],[RegIp],[RegTime],[LastLoginTime] FROM $PREFIX_member INNER JOIN $PREFIX_MemberDetails ON $PREFIX_member.[ID]=$PREFIX_MemberDetails.[UID]";
recordCount = int.Parse(
base.ExecuteScalar(base.NewQuery(DbSql.Member_GetMemberCount, 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 ?
base.OptimizeSql(sql1) :
base.OptimizeSql(DbSql.Member_GetPagedMembers);
sql = SQLRegex.Replace(sql, match =>
{
switch (match.Groups[1].Value)
{
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
}
return(null);
});
return(base.GetDataSet(
new SqlQuery(sql)
).Tables[0]);
}
/// <summary>
/// 获取指定数量和栏目的特殊文档
/// </summary>
/// <param name="siteId"></param>
/// <param name="catIdArray"></param>
/// <param name="number"></param>
/// <param name="skipSize"></param>
/// <param name="func"></param>
public void GetSpecialArchives(int siteId, int[] catIdArray, int number, int skipSize, DataReaderFunc func)
{
String sql = DbSql.Archive_GetSpecialArchiveList;
sql = SQLRegex.Replace(sql, m =>
{
switch (m.Groups[1].Value)
{
case "catIdArray":
return(this.IntArrayToString(catIdArray));
}
return(null);
});
IDictionary <String, object> paramters = new Dictionary <string, object>();
paramters.Add("@siteId", siteId);
SqlQuery query = new SqlQuery(String.Format(base.OptimizeSql(sql), skipSize, number), paramters);
base.ExecuteReader(query, func);
}
/// <summary>
/// 在指定模块下搜索关键词相关的内容
/// </summary>
/// <param name="moduleId"></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>
/// <returns></returns>
public DataTable SearchByModule(int moduleId, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount, string orderby)
{
string condition = SqlConst.Archive_NotSystemAndHidden;
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY $PREFIX_archive.sort_number DESC");
}
//为第一页时
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.id AS ID,* FROM $PREFIX_archive INNER JOIN $PREFIX_category ON $PREFIX_archive.[CID]=$PREFIX_category.id
WHERE $[condition] AND $PREFIX_category.[ModuleID]=$[module_id] AND ([Title] LIKE '%$[keyword]%' OR [Outline] LIKE '%$[keyword]%' OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%') $[orderby],$PREFIX_archive.id";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
base.NewQuery(String.Format(DbSql.ArchiveGetSearchRecordCountByModuleId, moduleId,
keyword, condition), DalBase.EmptyParameter)
).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 :
DbSql.ArchiveGetPagedSearchArchivesByModuleId;
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "condition": return(condition);
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "keyword": return(keyword);
case "module_id": return(moduleId.ToString());
case "orderby": return(orderby);
}
return(null);
});
return(base.GetDataSet(base.NewQuery(sql, null)).Tables[0]);
}
/// <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)
{
string condition = SqlConst.Archive_NotSystemAndHidden;
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY $PREFIX_archive.sort_number DESC");
}
object[,] data = new object[, ]
{
{ "@siteId", siteId },
{ "@lft", categoryLft },
{ "@rgt", categoryRgt }
};
//为第一页时
const string sql1 = @"SELECT TOP $[pagesize] $PREFIX_archive.id AS ID,*
FROM $PREFIX_archive INNER JOIN $PREFIX_category
ON $PREFIX_archive.[CgID]=$PREFIX_category.id
WHERE $[condition] AND $PREFIX_category.site_id=@siteId AND ($PREFIX_category.lft>=@lft
AND $PREFIX_category.rgt<=@rgt) AND ([Title] LIKE
'%$[keyword]%' OR [Outline] LIKE '%$[keyword]%'
OR [Content] LIKE '%$[keyword]%' OR [Tags] LIKE '%$[keyword]%')
$[orderby],$PREFIX_archive.id";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
SqlQueryHelper.Format(DbSql.ArchiveGetSearchRecordCountByCategoryId,
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 :
DbSql.ArchiveGetPagedSearchArchivesByCategoryId;
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="siteId"></param>
/// <param name="categoryRgt"></param>
/// <param name="onlyMatchTitle"></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="func"></param>
/// <param name="categoryLft"></param>
/// <returns></returns>
public void SearchArchives(int siteId, int categoryLft, int categoryRgt, bool onlyMatchTitle,
string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount,
string orderby, DataReaderFunc func)
{
base.CheckSqlInject(keyword, orderby);
StringBuilder sb = new StringBuilder(SqlConst.Archive_NotSystemAndHidden);
if (siteId > 0)
{
sb.Append(" AND $PREFIX_category.site_id=").Append(siteId.ToString());
}
if (categoryLft > 0 && categoryRgt > 0)
{
sb.Append(" AND ($PREFIX_category.lft>=").Append(categoryLft.ToString())
.Append(" AND $PREFIX_category.rgt<=").Append(categoryRgt).Append(")");
}
if (onlyMatchTitle)
{
sb.Append(" AND title LIKE '%").Append(keyword).Append("%'");
}
else
{
sb.Append(" AND ( title LIKE '%").Append(keyword).Append("%' OR outline LIKE '%").Append(keyword).Append("%' OR content LIKE '%").Append(keyword).Append("%')");
}
string condition = sb.ToString();
//排序规则
if (String.IsNullOrEmpty(orderby))
{
orderby = String.Intern("ORDER BY $PREFIX_archive.sort_number DESC");
}
//记录数
recordCount = int.Parse(base.ExecuteScalar(SqlQueryHelper.Format(DbSql.Archive_GetSearchRecordCount, 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 = DbSql.Archive_GetPagedSearchArchives;
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "condition": return(condition);
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
case "orderby": return(orderby);
}
return(null);
});
base.ExecuteReader(base.NewQuery(sql, null), func);
}
/// <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="flag"></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,
int flag, 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]$[flag]$[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
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 "flag": return(flag > 0? "$PREFIX_archive.flag & " + 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]);
}
public int GetCategoryArchivesCount(string id)
{
return(int.Parse(base.ExecuteScalar(
base.NewQuery(SQLRegex.Replace(DbSql.Archive_GetCategoryArchivesCount, (c) => { return id; }), null)).ToString()));
}
public DataTable GetPagedRows(int tableID, string keyword, int pageSize, int currentPageIndex, out int recordCount, out int pageCount)
{
/*
* string condition = ArchiveFlag.GetSQLString(new string[,]{
* {"st","0"},
* {"v","1"}
* });
*/
// string condition = String.Empty ;
// if (!String.IsNullOrEmpty(keyword))
// {
// condition = String.Format(" AND value LIKE '%{0}%'", keyword.Replace("'", "''"));
// }
//数据库为OLEDB,且为第一页时
const string sql1 = @"SELECT TOP $[pagesize] * FROM $PREFIX_table_row WHERE tableid=$[tableid] ORDER BY submittime DESC";
//记录数
recordCount = int.Parse(base.ExecuteScalar(
base.NewQuery(DbSql.Table_GetRowsCount,
base.Db.CreateParametersFromArray(
new object[, ] {
{ "@tableId", tableID }
}))
).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 ?
base.OptimizeSql(sql1) :
base.OptimizeSql(DbSql.TableGetPagedRows);
sql = SQLRegex.Replace(sql, (match) =>
{
switch (match.Groups[1].Value)
{
case "tableid": return(tableID.ToString());
case "pagesize": return(pageSize.ToString());
case "skipsize": return(skipCount.ToString());
// case "keyword": return keyword;
}
return(null);
});
return(base.GetDataSet(new SqlQuery(sql)).Tables[0]);
}