public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM ModificationLog where 1=1");
if (SearchText != "")
{
//sql.Add("and (1=0"); // custom sql
//sql.Add("or ([--Field--] like ", SearchText.SqlizeLike(), ")"); // custom sql
//sql.Add(")"); // custom sql
//sql.AddKeywordSearch(SearchText, "FirstName,LastName,Email", true); // search more than one field
sql.AddKeywordSearch(SearchText, new Models.ModificationLog().GetNameField().Name, true); // just search by name
}
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
var weekEnding = WeekBeginning.AddDays(7);
sql.Add("and UpdateDate >= ", WeekBeginning.SqlizeDate(), "and UpdateDate < ", weekEnding.SqlizeDate());
if (SelectedTable != "(all)")
{
sql.Add("and TableName = ", SelectedTable.SqlizeText());
}
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.ModificationLog().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM GenTestHasCat where 1=1");
if (SearchText != "")
{
//sql.Add("and (1=0"); // custom sql
//sql.Add("or ([--Field--] like ", SearchText.SqlizeLike(), ")"); // custom sql
//sql.Add(")"); // custom sql
//sql.AddKeywordSearch(SearchText, "FirstName,LastName,Email", true); // search more than one field
sql.AddKeywordSearch(SearchText, new Models.GenTestHasCat().GetNameField().Name, true); // just search by name
}
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.GenTestHasCat().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
//Sql sql = new Sql("SELECT TextBlock.* FROM TextBlock where 1=1"); //left join textblockpage on textblock.textblockpageid=textblockpage.textblockpageid
Sql sql = new Sql("SELECT TextBlock.*, groupname FROM TextBlock left join textblockgroup on textblock.textblockgroupid=textblockgroup.textblockgroupid where 1=1"); //
if (SearchText != "")
{
sql.Add(" and (sectioncode like ", SearchText.SqlizeLike(), " or title like ", SearchText.SqlizeLike(), " or bodytexthtml like ", SearchText.SqlizeLike(), ")");
//sql.AddKeywordSearch(SearchText, new Models.TextBlock().GetNameField().Name, true);
}
//Logging.dout(sql);
// handle an fk (rename fkid, then uncomment)
if (Web.Request["TextBlockGroupID"].IsNotBlank())
{
sql.Add("and TextBlockGroup.TextBlockGroupID=", Web.Request["TextBlockGroupID"].SqlizeNumber());
}
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
//sql.AddRawSqlString(new Models.TextBlock().GetDefaultOrderBy());
sql.Add(" order by SortPosition, GroupName, sectionCode");
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM ClientContactUsPerson where 1=1");
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
sql.AddKeywordSearch(SearchText, new Models.ClientContactUsPerson().GetNameField().Name, true);
}
// handle an fk (rename fkid, then uncomment)
var reg = Web.Request["region"];
if (reg.IsNotBlank())
{
sql.Add("and ClientContactUsRegionID=", reg.SqlizeNumber());
}
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.ClientContactUsPerson().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM ContactUs where 1=1");
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
//sql.AddKeywordSearch(SearchText, new Models.ContactUs().GetNameField().Name, true);
sql.Add(" and FirstName like ", SearchText.SqlizeLike());
sql.Add(" or LastName like ", SearchText.SqlizeLike());
sql.Add(" or Email like ", SearchText.SqlizeLike());
sql.Add(" or Company like ", SearchText.SqlizeLike());
sql.Add(" or Comments like ", SearchText.SqlizeLike());
}
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.ContactUs().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM Comment where 1=1");
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
sql.AddKeywordSearch(SearchText, "CommentText,CommenterName,CommenterEmail", true);
}
if (Web.Request["auctionId"].IsNotBlank())
{
// click link on auction list to get here - so filter by that auction
sql.Add(" and AuctionID=", Web.Request["auctionId"].SqlizeNumber());
}
if (StatusFilter.IsNotBlank())
{
sql.Add("AND Status=", StatusFilter.SqlizeText());
}
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.Comment().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM NewsRSS where 1=1");
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
sql.AddKeywordSearch(SearchText, new Models.NewsRSS().GetNameField().Name, true);
}
// handle an fk (rename fkid, then uncomment)
if (ShowActiveOnly)
{
sql.Add("and ispublished=", true);
}
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.NewsRSS().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
/*
* Sql sql = new Sql("SELECT * FROM Article where 1=1");
* if (SearchText != "") {
* sql.AddKeywordSearch(SearchText, new Models.Article().GetTextFieldNames(), true); // search on all text fields - change this if slow
* }
* sql.AddSql(FilterSql);
#region custom filter examples
* //if (StatusFilter.IsNotBlank()) {
* // FilterSql.Add("and x < y");
* //}
* //FilterSql.Add("and x <= ", MinDateFilter);
#endregion
* sql.AddSql(base.GetOrderBySql());
* return sql;
*/
Sql sql = new Sql("SELECT a.*, p.Title as ParentTitle FROM Article a left join Page p on a.pageid=p.pageid where 1=1");
sql.ResultSetPagingType = Sql.PagingType.sql2000; //!askMike do I have to use this now? :)
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
sql.AddKeywordSearch(SearchText, "a.Title, p.Title, a.BodyTextHtml", true);
}
// handle an fk (rename fkid, then uncomment)
if (PageID != null)
{
sql.Add("and a.PageID=", PageID.Value);
}
//sql.AddRawSqlString("and [extra raw sql here]");
if (PageID != null && SortBy.IsBlank())
{
if (pageRecord != null)
{
if (pageRecord.DisplayOrder == "Most Recent")
{
sql.AddRawSqlString("order by PublishDate desc, ArticleID desc");
}
else if (pageRecord.DisplayOrder == "Sort Order")
{
sql.AddRawSqlString("order by SortPosition asc");
}
}
}
else if (SortBy.IsBlank())
{
sql.AddRawSqlString("order by SortPosition, PublishDate");
}
else
{
sql.AddSql(GetOrderBySql());
sql.AddRawSqlString(", SortPosition, PublishDate");
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM FAQSection where 1=1");
if (SearchText != "")
{
sql.AddKeywordSearch(SearchText, new Models.FAQSection().GetNameField().Name, true);
}
if (SortBy.IsBlank())
{
sql.AddRawSqlString(new Models.FAQSection().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM ShoppingCartOrder where 1=1");
if (SearchText != "")
{
sql.Add(" and (");
sql.Add(" (CustomerOrderReference like ", SearchText.SqlizeLike(), ")");
sql.Add(" or (OrderRef like ", SearchText.SqlizeLike(), ")");
sql.Add(" or (Email like ", SearchText.SqlizeLike(), ")");
sql.Add(" or (FirstName like ", SearchText.SqlizeLike(), ")");
sql.Add(" or (LastName like ", SearchText.SqlizeLike(), ")");
try
{
var searchDate = System.DateTime.Parse(SearchText);
sql.Add(" or (DateOrdered between ", searchDate.SqlizeDate(), " and ", searchDate.AddDays(1).SqlizeDate(), " )");
}
catch (Exception)
{
}
sql.Add(" )");
//sql.AddKeywordSearch(SearchText, new Models.ShoppingCartOrder().GetNameField().Name, true);
}
//Logging.dout(sql.ToString());
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.ShoppingCartOrder().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}
public override Sql GetSql()
{
//Sql sql = new Sql("SELECT p.* FROM Page p left join Page pp on p.parentpageid=pp.pageid where 1=1");
Sql sql = new Sql("SELECT p.* FROM Page p where 1=1");
sql.ResultSetPagingType = Sql.PagingType.sql2000;
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
sql.AddKeywordSearch(SearchText, "Title,NavTitle,PageCode,UrlRewriteTitle", true);
}
#if PageRevisions
if (PageIDForRevisions.HasValue)
{
sql.Add("and historypageid=" + PageIDForRevisions);
}
#endif
// handle an fk (rename fkid, then uncomment)
if (Web.Request["code"].IsNotBlank())
{
var parent = Models.Page.LoadByPageCode(Web.Request["code"]);
sql.Add("and parentpageid=", parent.ID);
}
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
//sql.AddRawSqlString("order by isnull(pp.SortPosition,p.SortPosition), isnull(pp.PageID,p.PageID)");
sql.AddRawSqlString("order by PublishDate desc");
}
else
{
sql.AddSql(GetOrderBySql());
}
//Logging.dout(sql.ToString());
return(sql);
return(null);
}
public override Sql GetSql()
{
Sql sql = new Sql("SELECT * FROM Person where 1=1");
if (Web.Request["admin"].ToBool())
{
if (!Security.IsDevAccess)
{
sql.Add("and role not like", SecurityRolesCore.Roles.DEVELOPER.SqlizeLike());
}
sql.Add("and (role like", SecurityRolesCore.Roles.ADMINISTRATOR.SqlizeLike());
sql.Add("or role like", SecurityRolesCore.Roles.SUPERADMIN.SqlizeLike(), ")");
ShowRole = true;
//}else { // only show staff
// sql.Add("and role not like", SecurityRolesCore.Roles.DEVELOPER.SqlizeLike());
// sql.Add("and role like ", SecurityRoles.Roles.STAFF.SqlizeLike());
}
if (SearchText != "")
{
//sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")");
sql.AddKeywordSearch(SearchText, "FirstName,LastName,Email", true);
}
// handle an fk (rename fkid, then uncomment)
//sql.Add("and landingPageID=", landingPageID);
//sql.AddRawSqlString("and [extra raw sql here]");
if (SortBy.IsBlank())
{
// hint: to change the default order by for both admin and front end, override Destination.GetDefaultOrderBy in model partial
sql.AddRawSqlString(new Models.Person().GetDefaultOrderBy());
}
else
{
sql.AddSql(GetOrderBySql());
}
return(sql);
}