/// <summary>
/// 获取文章记录的Top版本方法,含期刊Id,文章类别信息
/// </summary>
/// <param name="parms"></param>
/// <param name="OrderBy"></param>
/// <param name="Top"></param>
/// <returns></returns>
public DataTable GetArticleDataTable(List <SqlDbParameter> parms, string OrderBy, string Top = null)
{
string selectStr = Top == null ? "SELECT " :"SELECT TOP(" + Top + ") ";
string sql =
selectStr + @"
A.[Id]
,[ArticleTitle]
,[ArticleTitleAlias]
,[IsPublic]
,[CreateTime]
,[CreateBy]
,[IsDelete]
,[NoOfJournal]
,[NoOfCategory] --该分类下的序号
,[ArticlePropertyId]
,[CategoryId] --文章类别
,'' CategoryName
,B.JournalId
,C.HrefTpl
FROM [Article] A LEFT JOIN JournalArticleRelation B ON A.Id=B.ArticleID
LEFT JOIN ArticleCategory C ON A.CategoryId = C.Id
WHERE 1 = 1 {1} ORDER BY " + OrderBy;
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
return(this.GetSqlData(sqlString, parms));
}
/// <summary>
/// 统计期刊的文章数量
/// </summary>
/// <param name="parms">期刊Id 或 类别Id (CategoryId=2 AND JournalId = 3)</param>
/// <returns></returns>
public DataTable GetArticleTotal(List <SqlDbParameter> parms)
{
string sql = @"SELECT
ISNULL(COUNT(1),0) TOTAL
FROM Article A LEFT JOIN JournalArticleRelation B
ON A.Id = B.ArticleID
WHERE 1 = 1 {1} ";
sql = SqlDbParameter.BuildSqlString(sql, parms);
return(this.GetSqlData(sql, parms));
}
public DataTable GetJournalDataTable(List <SqlDbParameter> parms, string OrderBy, string Top = null)
{
string selectStr = Top == null ? "SELECT " : "SELECT TOP(" + Top + ") ";
string sql =
selectStr + @"
[JournalId]
,[JournalName]
,[PropertyName]
FROM [ArticleJournal]
WHERE 1 = 1 {1} ORDER BY " + OrderBy;
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
return(this.GetSqlData(sqlString, parms));
}
/// <summary>
/// 返回期刊列表
/// </summary>
/// <param name="parms"></param>
/// <param name="orderBy"></param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetJournalDataTablePage(List <SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
{
RecordCount = 0;
string sql = @" SELECT {0} * FROM (
SELECT [JournalId]
,[JournalName]
,[PropertyName]
FROM [ArticleJournal]
) t WHERE 1 = 1 {1} ";
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
DataTable dt = this.GetAll(sqlString, parms, OrderBy, PageSize, PageIndex, out RecordCount);
return(dt);
}
public DataTable GetArticleCategoryDataTablePage(List <SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
{
RecordCount = 0;
string sql = @" SELECT {0} * FROM (
SELECT [Id]
,[PId]
,[CategoryName]
,[Level]
,[HrefTpl]
,[Order]
FROM [ArticleCategory]
) t WHERE 1 = 1 {1} ";
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
DataTable dt = this.GetAll(sqlString, parms, OrderBy, PageSize, PageIndex, out RecordCount);
return(dt);
}
/// <summary>
/// Order BY A.[Order]
/// </summary>
/// <param name="parms"></param>
/// <param name="OrderBy"></param>
/// <returns></returns>
public DataTable GetArticleCategoryDataTable(List <SqlDbParameter> parms, string OrderBy)
{
string sql = @" SELECT {0} * FROM (
SELECT A.[Id]
,A.[PId]
,B.[CategoryName] AS PCategoryName
,A.[CategoryName]
,A.[Level]
,A.[HrefTpl]
,A.[Order]
FROM [ArticleCategory] A LEFT JOIN [ArticleCategory] B
ON A.PID = B.ID
) t WHERE 1 = 1 {1} ORDER BY " + OrderBy;
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
DataTable dt = this.GetSqlData(sqlString, parms);
return(dt);
}
//获取详细内容[ArticleContent]
public DataTable GetArticleDataLeftJournalAndCategoryTable(List <SqlDbParameter> parms)
{
string sql =
@"SELECT A.[Id]
,[ArticleTitle]
,[ArticleContent]
,[IsPublic]
,[CreateTime]
,[CreateBy]
,[IsDelete]
,[NoOfJournal]
,[NoOfCategory]
,[ArticlePropertyId]
,[CategoryId] --文章类别
,B.JournalId
,C.HrefTpl
FROM [Article] A LEFT JOIN JournalArticleRelation B ON A.Id=B.ArticleID
LEFT JOIN ArticleCategory C ON A.CategoryId = C.Id
WHERE 1 = 1 {1} ";
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
return(this.GetSqlData(sqlString, parms));
}
public DataTable GetArticleDataTablePage(List <SqlDbParameter> parms, string OrderBy, int PageSize, int PageIndex, out int RecordCount)
{
RecordCount = 0;
string sql = @" SELECT {0} * FROM (
SELECT A.[Id]
,[ArticleTitle]
,[IsPublic]
,[CreateTime]
,[CreateBy]
,[IsDelete]
,[NoOfJournal]
,[NoOfCategory]
,[ArticlePropertyId]
,[CategoryId] --文章类别
,'' CategoryName
,B.JournalId
FROM [Article] A LEFT JOIN JournalArticleRelation B ON A.Id=B.ArticleID
) t WHERE 1 = 1 {1} ";
string sqlString = SqlDbParameter.BuildSqlString(sql, parms);
DataTable dt = this.GetAll(sqlString, parms, OrderBy, PageSize, PageIndex, out RecordCount);
return(dt);
}
