private PetaPoco.Sql GetSql_CommentPageIndexInParentComments(long parentId, SortBy_Comment sortBy)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Where("parentId=@0", parentId);
if (sortBy == SortBy_Comment.DateCreated)
{
sql.OrderBy("id");
}
else
{
sql.OrderBy("id desc");
}
return(sql);
}
private PetaPoco.Sql GetSql_CommentPageIndex(string tenantType, long commentedObjectId, SortBy_Comment sortBy)
{
PetaPoco.Sql sql = PetaPoco.Sql.Builder;
sql.Where("tenantTypeId=@0", tenantType);
sql.Where("commentedObjectId=@0", commentedObjectId);
sql.Where("parentId=0");
if (sortBy == SortBy_Comment.DateCreated)
{
sql.OrderBy("id");
}
else
{
sql.OrderBy("id desc");
}
return(sql);
}
/// <summary>
/// 主要查詢方法
/// </summary>
/// <param name="page">頁數（預設為 1）</param>
/// <param name="ExportMode">匯出模式。（預設為 false）若為真，則取消分頁。</param>
private void Query(long page = 1, bool ExportMode = false)
{
sql.Append(String.Format("SELECT * FROM {0} WHERE 1=1", TableName));
#region 將搜尋控制項的值轉換為 SQL Statement
BackendSearchControl.ConvertControlToSQL(sql, SearchItems, plSearch, this.ViewState);
#endregion
sql.OrderBy("sID DESC");
if (!ExportMode)
{
var data = db.Page <DataModel_a12SupauCheckin>(page, PageSize, sql);
patwGridView1.DataSource = data.Items;
patwGridView1.DataBind();
AspNetPager1.PageSize = (int)data.ItemsPerPage;
AspNetPager1.RecordCount = (int)data.TotalItems;
lbTotal.Text = "依據條件，目前共有 " + data.TotalItems.ToString() + " 筆";
}
else
{
var data = db.Query <DataModel_a12SupauCheckin>(sql);
patwGridView1.DataSource = data;
patwGridView1.DataBind();
AspNetPager1.Visible = false;
lbTotal.Text = "依據條件，目前共有 " + data.Count() + " 筆";
}
}
/// <summary>
/// 从MicroblogQuery构建PetaPoco.Sql的where条件
/// </summary>
/// <param name="query">MicroblogQuery查询条件</param>
/// <param name="sql">PetaPoco.Sql对象</param>
private void BuildSqlWhere_MicroblogQuery(MicroblogQuery query, ref PetaPoco.Sql sql)
{
if (sql == null)
{
sql = PetaPoco.Sql.Builder;
}
if (query.UserId.HasValue)
{
sql.Where("UserId = @0", query.UserId);
}
if (!string.IsNullOrEmpty(query.TenantTypeId))
{
sql.Where("TenantTypeId = @0", query.TenantTypeId);
}
if (query.OwnerId.HasValue)
{
sql.Where("OwnerId = @0", query.OwnerId);
}
if (query.isOriginal.HasValue && query.isOriginal.Value)
{
sql.Where("OriginalMicroblogId = 0");
}
else if (query.MediaType.HasValue)
{
switch (query.MediaType)
{
case MediaType.Image:
sql.Where("HasPhoto = 1");
break;
case MediaType.Video:
sql.Where("HasVideo = 1");
break;
case MediaType.Audio:
sql.Where("HasMusic = 1");
break;
}
}
if (!string.IsNullOrEmpty(query.Keyword))
{
sql.Where("Body like @0", "%" + StringUtility.StripSQLInjection(query.Keyword) + "%");
}
if (query.StartDate.HasValue)
{
sql.Where("DateCreated >= @0", query.StartDate);
}
//reply:已修改
if (query.EndDate.HasValue)
{
sql.Where("DateCreated < @0", query.EndDate.Value.AddDays(1));
}
if (query.AuditStatus.HasValue)
{
sql.Where("AuditStatus = @0", (int)query.AuditStatus);
}
sql.OrderBy("MicroblogId desc");
}
