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)); }