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 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 * 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 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 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 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);
}
private void AddBreakdown(ReportLine h3, string title, Sql where, string fieldName, string breakdownBy)
{
string fieldAggregate = "sum(" + fieldName + ")";
if (fieldName == "COUNT")
{
fieldAggregate = "count(*)";
}
else if (fieldName.StartsWith("COUNT"))
{
fieldAggregate = fieldName;
}
Sql sql = new Sql("select " + fieldAggregate + " as Amt from ").AddSql(where);
var norm = AddLine(h3, title, sql.FetchNumber().ToInt());
if (breakdownBy != null)
{
sql = new Sql().AddRawSqlString("select " + breakdownBy + " as Title, " + fieldAggregate + " as Amt from ").AddSql(where);
if (fieldName.DoesntContain("COUNT"))
{
sql.Add("and " + fieldName + " is not null and " + fieldName + " <> 0");
}
sql.AddRawSqlString("group by " + breakdownBy + " order by " + fieldAggregate + " desc");
AddSubLines(norm, "By Contractor", 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 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 Sql GetOrderBySql()
{
Sql sql = new Sql();
if (SortBy.IsBlank() || SortBy == "SortPosition") // if person sorts by sortposition, we assume they really want to sort by default sort order
{
if (DefaultSortBy.ToLower().Contains("order by"))
{
sql.AddRawSqlString(DefaultSortBy);
}
else
{
// old behaviour for backwards compatibility with generated code
sql.Add("order by ", DefaultSortBy.SqlizeName(), (DefaultSortDesc ? "desc" : ""));
}
}
else
{
sql.Add("order by ", SortBy.SqlizeName(), (SortDesc ? "desc" : ""));
}
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);
}
public ActionResult Search(string searchText)
{
var data = new ViewModel();
data.ContentPage = Models.Page.LoadOrCreatePageCode("Search");
if (data.ContentPage == null)
{
throw new Exception("Search page not found");
}
data.SearchText = searchText;
data.SearchArea = String.IsNullOrEmpty(Web.Request["SearchArea"]) ? "All" : Web.Request["SearchArea"];
int itemsPerPage;
if (data.SearchArea == "All")
{
itemsPerPage = 5;
}
else
{
itemsPerPage = 25;
}
string keywords = searchText;
var search = new KeywordSearch(keywords);
//#if pages
if (searchText.IsNotBlank())
{
/* if (data.SearchArea == "All" || data.SearchArea == "Pages") {
* Sql sql = new Sql("select * from Page");
* sql.AddSql(search.FullTextJoin("page"));
* sql.WhereIsActive<Page>();
* //sql.Add("and (rolesallowed is null)"); // if using page user access control
* sql.Add("order by Rank desc,Title");
* sql.Paging(itemsPerPage);
* data.PageResults = PageList.Load(sql);
* data.Counter += sql.FetchCount();
* }
* if (data.SearchArea == "All" || data.SearchArea == "Faqs") {
* Sql sql = new Sql("select * from FAQItem");
* sql.AddSql(search.FullTextJoin("FAQItem"));
* sql.WhereIsActive<FAQItem>();
* sql.Add("order by Rank,FAQTitle");
* sql.Paging(itemsPerPage);
* data.FaqItems = FAQItemList.Load(sql);
* data.Counter += sql.FetchCount();
* }*/
//if (data.SearchArea == "All" || data.SearchArea == "News") {
// Sql sql = new Sql("select * from News");
// sql.AddSql(search.FullTextJoin("News"));
// //sql.WhereIsActive<News>();
// sql.Add("order by Rank,Source");
// sql.Paging(itemsPerPage);
// //data.NewsItems = NewsList.Load(sql);
// data.Counter += sql.FetchCount();
//}
//if (data.SearchArea == "All" || data.SearchArea == "Products") {
// Sql sql = new Sql("select * from Product");
// sql.AddSql(search.FullTextJoin("Product"));
// //sql.WhereIsActive<Product>();
// sql.Add("order by Rank desc,Title");
// sql.Paging(itemsPerPage);
// //data.ProductItems = ProductList.Load(sql);
// data.Counter += sql.FetchCount();
//}
}
data.NumPages = Html.CalcPageCount(data.Counter, itemsPerPage);
// note: for full text search, instead use
var keywordSearch = new Beweb.KeywordSearch(searchText);
var columns = keywordSearch.GetSqlFullTextWhereForTable("Page");
if (columns.IsNotBlank())
{
var sql = new Sql("select * from Page where 1=1");
sql.Add("and historypageid is null"); // if page revisions
sql.AddRawSqlString(columns);
sql.AndIsActive <Page>();
data.PageResults = PageList.Load(sql);
}
columns = keywordSearch.GetSqlFullTextWhereForTable("Event");
if (columns.IsNotBlank())
{
var sql = new Sql("select * from event where 1=1");
sql.AddRawSqlString(columns);
sql.AndIsActive <Event>();
data.EventResults = EventList.Load(sql);
}
columns = keywordSearch.GetSqlFullTextWhereForTable("News");
if (columns.IsNotBlank())
{
var sql = new Sql("select * from News where 1=1");
sql.AddRawSqlString(keywordSearch.GetSqlFullTextWhereForTable("News"));
sql.AndIsActive <News>();
data.NewsResults = NewsList.Load(sql);
}
//#endif
return(View("SearchResults", data));
}
