/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
//Check.Require(fromSection.OrderByClip, "query.OrderByClip", Check.NotNullOrEmpty);
//OrderByClip orderBy = fromSection.OrderByClip;
fromSection.TableName = string.Concat("(", fromSection.SqlString, ") tmpi_table");
fromSection.Select(new Field("tmpi_table.*"));
fromSection.AddSelect(new Field("rownum AS rn"));
fromSection.OrderBy(OrderByClip.None);
fromSection.DistinctString = string.Empty;
fromSection.PrefixString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Parameters = fromSection.Parameters;
fromSection.Where(new WhereClip("rownum <=" + endIndex.ToString()));
if (startIndex > 1)
{
fromSection.TableName = string.Concat("(", fromSection.SqlString, ")");
fromSection.Select(Field.All);
fromSection.Where(new WhereClip(string.Concat("rn>=", startIndex.ToString())));
}
return(fromSection);
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual<int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual<int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
//Check.Require(fromSection.OrderByClip, "query.OrderByClip", Check.NotNullOrEmpty);
//OrderByClip orderBy = fromSection.OrderByClip;
fromSection.TableName = string.Concat("(", fromSection.SqlString, ") tmpi_table");
fromSection.Select(new Field("tmpi_table.*"));
fromSection.AddSelect(new Field("rownum AS rn"));
fromSection.OrderBy(OrderByClip.None);
fromSection.DistinctString = string.Empty;
fromSection.PrefixString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Parameters = fromSection.Parameters;
fromSection.Where(new WhereClip("rownum <=" + endIndex.ToString()));
if (startIndex > 1)
{
fromSection.TableName = string.Concat("(", fromSection.SqlString, ")");
fromSection.Select(Field.All);
fromSection.Where(new WhereClip(string.Concat("rn>=", startIndex.ToString())));
}
return fromSection;
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
if (startIndex == 1)
{
return(base.CreatePageFromSection(fromSection, startIndex, endIndex));
}
if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip))
{
foreach (Field f in fromSection.Fields)
{
if (!f.PropertyName.Equals("*"))
{
fromSection.OrderBy(f.Asc);
break;
}
}
}
Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!");
if (fromSection.Fields.Count == 0)
{
fromSection.Select(Field.All);
}
fromSection.AddSelect(new Field(string.Concat("row_number() over(", fromSection.OrderByString, ") AS tmp_rowid")));
//OrderByClip tempOrderBy = fromSection.OrderByClip;
fromSection.OrderBy(OrderByClip.None);
fromSection.TableName = string.Concat("(", fromSection.SqlString, ") AS tmp_table");
fromSection.Parameters = fromSection.Parameters;
fromSection.DistinctString = string.Empty;
fromSection.PrefixString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
//fromSection.OrderBy(tempOrderBy);
fromSection.Where(new WhereClip(string.Concat("tmp_rowid BETWEEN ", startIndex.ToString(), " AND ", endIndex.ToString())));
return(fromSection);
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public override FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual<int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual<int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
if (startIndex == 1)
{
return base.CreatePageFromSection(fromSection, startIndex, endIndex);
}
if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip))
{
foreach (Field f in fromSection.Fields)
{
if (!f.PropertyName.Equals("*"))
{
fromSection.OrderBy(f.Asc);
break;
}
}
}
Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!");
if (fromSection.Fields.Count == 0)
{
fromSection.Select(Field.All);
}
fromSection.AddSelect(new Field(string.Concat("row_number() over(", fromSection.OrderByString, ") AS tmp_rowid")));
//OrderByClip tempOrderBy = fromSection.OrderByClip;
fromSection.OrderBy(OrderByClip.None);
fromSection.TableName = string.Concat("(", fromSection.SqlString, ") AS tmp_table");
fromSection.Parameters = fromSection.Parameters;
fromSection.DistinctString = string.Empty;
fromSection.PrefixString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
//fromSection.OrderBy(tempOrderBy);
fromSection.Where(new WhereClip(string.Concat("tmp_rowid BETWEEN ", startIndex.ToString(), " AND ", endIndex.ToString())));
return fromSection;
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public virtual FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual <int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
int pageSize = endIndex - startIndex + 1;
if (startIndex == 1)
{
fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString());
}
else
{
if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip))
{
foreach (Field f in fromSection.Fields)
{
if (!f.PropertyName.Equals("*") && f.PropertyName.IndexOf('(') == -1)
{
fromSection.OrderBy(f.Asc);
break;
}
}
}
Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!");
int count = fromSection.Count(fromSection);
List <Parameter> list = fromSection.Parameters;
if (endIndex > count)
{
int lastnumber = count - startIndex + 1;
if (startIndex > count)
{
lastnumber = count % pageSize;
}
fromSection.PrefixString = string.Concat(" TOP ", lastnumber.ToString());
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
//
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS temp_table ");
fromSection.PrefixString = string.Empty;
fromSection.DistinctString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.Where(WhereClip.All);
}
else
{
if (startIndex < count / 2)
{
fromSection.PrefixString = string.Concat(" TOP ", endIndex.ToString());
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable ");
fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString());
fromSection.DistinctString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.Where(WhereClip.All);
//
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempOuttable ");
fromSection.PrefixString = string.Empty;
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
}
else
{
fromSection.PrefixString = string.Concat(" TOP ", (count - startIndex + 1).ToString());
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable ");
fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString());
fromSection.DistinctString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.Where(WhereClip.All);
}
}
fromSection.Parameters = list;
}
return(fromSection);
}
/// <summary>
/// 根据条件取得下级所有文章
/// </summary>
/// <param name="nodecode"></param>
/// <param name="title"></param>
/// <param name="source"></param>
/// <param name="author"></param>
/// <param name="tags"></param>
/// <param name="iIndex"></param>
/// <param name="iTop"></param>
/// <param name="iAudit"></param>
/// <param name="dtAdd1"></param>
/// <param name="dtAdd2"></param>
/// <param name="dtEdit1"></param>
/// <param name="dtEdit2"></param>
/// <param name="userId"></param>
/// <param name="isAdmin"></param>
/// <param name="pageIndex"></param>
/// <param name="pageSize"></param>
/// <param name="recordCount"></param>
/// <returns></returns>
public DataTable GetListByFilter2(
string nodecode,
string title,
string source,
string author,
string tags,
int iIndex,
int iTop,
int iAudit,
string dtAdd1,
string dtAdd2,
string dtEdit1,
string dtEdit2,
int userId,
bool isAdmin,
int pageIndex,
int pageSize,
ref int recordCount
)
{
WhereClipBuilder wcb = new WhereClipBuilder();
if (!string.IsNullOrEmpty(nodecode))
{
wcb.And(cmsNews._.NodeCode == nodecode); // 当前栏目
}
if (!string.IsNullOrEmpty(title.Trim()))
{
wcb.And(cmsNews._.Title.Contain(title.Trim()));
}
if (!string.IsNullOrEmpty(source.Trim()))
{
wcb.And(cmsNews._.Source.Contain(source.Trim()));
}
if (!string.IsNullOrEmpty(author.Trim()))
{
wcb.And(cmsNews._.Author.Contain(author.Trim()));
}
if (!string.IsNullOrEmpty(tags.Trim()))
{
wcb.And(cmsNews._.Tags.Contain(tags.Trim()));
}
if (iIndex >= 0)
{
wcb.And(cmsNews._.IsIndex == iIndex);
}
if (iTop >= 0)
{
wcb.And(cmsNews._.IsTop == iTop);
}
if (iAudit >= 0)
{
wcb.And(cmsNews._.IsAudit == iAudit);
}
if (!isAdmin && userId > 0)
{
//wcb.And(cmsNews._.AddUser == userId);
wcb.And(cmsNews._.AddUser.SubQueryIn(
DBSession.CurrentSession.From <cmsUserMap>()
.Select(cmsUserMap._.UserId)
.Where(cmsUserMap._.UpUserId == userId)
)
);
}
DateTime dt;
if (DateTime.TryParse(dtAdd1.Trim(), out dt))
{
wcb.And(cmsNews._.AddTime >= dt);
}
if (DateTime.TryParse(dtAdd2.Trim(), out dt))
{
wcb.And(cmsNews._.AddTime <= dt);
}
if (DateTime.TryParse(dtEdit1.Trim(), out dt))
{
wcb.And(cmsNews._.EditTime >= dt);
}
if (DateTime.TryParse(dtEdit2.Trim(), out dt))
{
wcb.And(cmsNews._.EditTime <= dt);
}
FromSection fs = GetFromSection(wcb.ToWhereClip(), cmsNews._.EditTime.Desc);
recordCount = fs.Count();
return(fs
.Select(cmsNews._.All)
.Page(pageSize, pageIndex)
.ToDataTable());
}
/// <summary>
/// 创建分页查询
/// </summary>
/// <param name="fromSection"></param>
/// <param name="startIndex"></param>
/// <param name="endIndex"></param>
/// <returns></returns>
public virtual FromSection CreatePageFromSection(FromSection fromSection, int startIndex, int endIndex)
{
Check.Require(startIndex, "startIndex", Check.GreaterThanOrEqual<int>(1));
Check.Require(endIndex, "endIndex", Check.GreaterThanOrEqual<int>(1));
Check.Require(startIndex <= endIndex, "startIndex must be less than endIndex!");
Check.Require(fromSection, "fromSection", Check.NotNullOrEmpty);
int pageSize = endIndex - startIndex + 1;
if (startIndex == 1)
{
fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString());
}
else
{
if (OrderByClip.IsNullOrEmpty(fromSection.OrderByClip))
{
foreach (Field f in fromSection.Fields)
{
if (!f.PropertyName.Equals("*") && f.PropertyName.IndexOf('(') == -1)
{
fromSection.OrderBy(f.Asc);
break;
}
}
}
Check.Require(!OrderByClip.IsNullOrEmpty(fromSection.OrderByClip), "query.OrderByClip could not be null or empty!");
int count = fromSection.Count(fromSection);
List<Parameter> list = fromSection.Parameters;
if (endIndex > count)
{
int lastnumber = count - startIndex + 1;
if (startIndex > count)
lastnumber = count % pageSize;
fromSection.PrefixString = string.Concat(" TOP ", lastnumber.ToString());
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
//
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS temp_table ");
fromSection.PrefixString = string.Empty;
fromSection.DistinctString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.Where(WhereClip.All);
}
else
{
if (startIndex < count / 2)
{
fromSection.PrefixString = string.Concat(" TOP ", endIndex.ToString());
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable ");
fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString());
fromSection.DistinctString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.Where(WhereClip.All);
//
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempOuttable ");
fromSection.PrefixString = string.Empty;
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
}
else
{
fromSection.PrefixString = string.Concat(" TOP ", (count - startIndex + 1).ToString());
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.TableName = string.Concat(" (", fromSection.SqlString, ") AS tempIntable ");
fromSection.PrefixString = string.Concat(" TOP ", pageSize.ToString());
fromSection.DistinctString = string.Empty;
fromSection.GroupBy(GroupByClip.None);
fromSection.Select(Field.All);
fromSection.OrderBy(fromSection.OrderByClip.ReverseOrderByClip);
fromSection.Where(WhereClip.All);
}
}
fromSection.Parameters = list;
}
return fromSection;
}
/// <summary>
/// 进行查询操作
/// </summary>
/// <param name="fields"></param>
/// <returns></returns>
public TableRelation <T> Select(params Field[] fields)
{
section.Select(fields);
return(this);
}
