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); }
// http://www.dol.govt.nz/er/holidaysandleave/publicholidays/publicholidaydates/ical/public-holidays-all.ics // http://www.dol.govt.nz/er/holidaysandleave/publicholidays/publicholidaydates/current.asp public static DateTime GetWorkingDay(DateTime date, int holidayRegionID) { if (_hasCalendarHolidayTable == null) { _hasCalendarHolidayTable = BewebData.TableExists("CalendarHoliday"); } DateTime nextWorkDate = new DateTime(date.Ticks); bool isHoliday; do { isHoliday = nextWorkDate.DayOfWeek == DayOfWeek.Sunday || nextWorkDate.DayOfWeek == DayOfWeek.Saturday; if (_hasCalendarHolidayTable.Value && !isHoliday) { Sql sql = new Sql("select * from CalendarHoliday where HolidayDate=", nextWorkDate); if (holidayRegionID > 0) { // return National and Regional Holidays sql.Add("and (HolidayRegionID is null or HolidayRegionID = ", holidayRegionID.SqlizeNumber(), ")"); } else if (BewebData.FieldExists("CalendarHoliday", "HolidayRegionID")) { // return Holidays without regions if regions exist (e.g. National Holidays) sql.Add("and HolidayRegionID is null"); } isHoliday = sql.RecordExists(); } if (isHoliday) { nextWorkDate = nextWorkDate.AddDays(1); } } while (isHoliday); return(nextWorkDate); }
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 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); }
/// <summary> /// write the lg for a given record, or null if log is disabled /// </summary> /// <param name="obj"></param> /// <returns></returns> public static string ShowModificationLog(ActiveRecord obj) { #if ModificationLog if (!Util.GetSettingBool("UseModificationLog", false)) { return(null); } int pkvalue = obj.ID_Field.ToInt(0); string tableName = obj.GetTableName(); string ModLogTableName = "ModificationLog"; var sql = new Sql("select top 100 * from ", ModLogTableName.SqlizeName(), " where TableName=", tableName.Sqlize_Text(), " "); sql.Add(" and RecordID=", pkvalue, ""); sql.Add(" order by updatedate desc"); string res = @"<tr class=""dontprint""> <td class=label>Change History</td> <td class=field><a href="""" onclick=""$('.modlog').show();$(this).hide();return false;"">show</a><div class=""modlog"" style=""display:none"">" ; int scan = 0; using (DbDataReader dbDataReader = sql.GetReader()) { foreach (DbDataRecord rsLog in dbDataReader) { var person = Person.LoadID((rsLog["PersonID"] + "").ToInt(0)); var UserName = (person != null) ? person.FullName : "not available"; string descr = rsLog["ChangeDescription"].ToString().FmtPlainTextAsHtml(); if (!(descr.Contains("Date Modified changed") && descr.Length < 94)) //eg skip Date Modified changed from "29 Oct 2013 19:49am" to "29 Oct 2013 10:51am" //descr = descr.Replace("<br>", " | "); // removed and applied nice formatting below (JVB) { res += "<b>" + rsLog["ActionType"] + "</b> on " + Fmt.DateTime(rsLog["UpdateDate"] + "") + " by " + UserName + ":<br/> - " + descr + "<br/><br/>"; scan++; if (scan > 100) { res += "more..."; break; } } } } res += @"</div> </td> </tr>" ; //todo: remove any old mod logs when drawing a mod log page. return(res); #else return("Not available."); #endif }
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 void Init() { Sql mainlist = new Sql("select top 25 * from blog where ispublished=1 order by dateadded desc"); if (Web.Request["d"] != null) { mainlist = new Sql(); mainlist.SuppressQuoteChecking = true; var startDate = Convert.ToDateTime(Web.Request["d"] + "-1"); var endDate = startDate.AddMonths(1).AddDays(-1); mainlist.Add("select top 25 * from blog where ispublished=1 AND DateAdded >= ", startDate, " AND DateAdded <= ", endDate, " order by dateadded desc"); //Logging.dout(mainlist.ToString()); } blogList = mainlist.GetDataTable(); Sql archivelist = new Sql(@"SELECT TOP (100) PERCENT CONVERT(CHAR(5), DateAdded, 120) + CONVERT(CHAR(4), DateAdded, 100) AS ArchiveDate, COUNT(BlogID) AS NumBlogs FROM dbo.Blog WHERE (IsPublished = 1) and dateadded<getdate()-30"); //var d = Web.Request["d"] + ""; /* * if(d!=""){ * archivelist.Add("AND ArchiveDate = ", d.SqlizeText); * }*/ archivelist.Add("GROUP BY CONVERT(CHAR(5), DateAdded, 120) + CONVERT(CHAR(4), DateAdded, 100) ORDER BY ArchiveDate DESC"); blogArchiveList = archivelist.GetDataTable(); }
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); }
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); } }
private void Validate(Models.Page record) { // add any code to check for validity //ModelState.AddModelError("Record", "Suchandsuch cannot be whatever."); string url = record.URLRewriteTitle; //if (!IsRouteFree(url)) { // ModelState.AddModelError("URLRewriteTitle", "URL is already in use."); //} if (url.IsNotBlank()) { #if PageRevisions var sql = new Sql("select * from page where URLRewriteTitle=", url.Sqlize_Text(), " and RevisionStatus = ", "Live".SqlizeText(), ""); #else var sql = new Sql("select * from page where URLRewriteTitle=", url.Sqlize_Text()); #endif if (!record.IsNewRecord) { sql.Add("and pageid<>", record.PageID); } if (PageList.Load(sql).Active.Count > 0) { ModelState.AddModelError("URLRewriteTitle", "URL is already in use."); } } }
public override Sql GetSql() { Sql sql = new Sql("SELECT * FROM MailLog where 1=1"); if (SearchText != "") { //sql.Add("and (1=0"); // custom sql sql.Add(" and (EmailSubject like ", SearchText.SqlizeLike(), ")"); //sql.Add(")"); // custom sql //sql.AddKeywordSearch(SearchText, "FirstName,LastName,Email", true); // search more than one field //sql.AddKeywordSearch(SearchText, new Models.MailLog().GetNameField().Name, 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.MailLog().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); }
// // GET: /Admin/Video/ public ActionResult Approval() { Breadcrumbs.Current.AddBreadcrumb(2, "Video Approval"); Util.SetReturnPage(2); //var dataList = VideoList.LoadByStatus("New"); Sql sql = new Sql("SELECT video.* FROM Video where 1=1"); sql.Add("and status =", "New".Sqlize_Text()); var dataList = VideoList.Load(sql); return(View("VideoApproval", dataList)); }
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 ActionResult SubNav(string sectionCode, Models.Page page) { var data = new NavViewModel(); //var sql = new Sql("where ParentPageID=", page.ID); var sql = new Sql(); if (page.ParentPageID != null) { sql.Add("where ParentPageID=", page.ParentPageID.Value); } else { sql.Add("where ParentPageID=", page.ID); } sql.AndIsActive <Page>(); var pages = PageList.Load(sql); foreach (var childPage in pages) { var item = new NavItem(childPage); if (childPage.ID == page.ID) { item.IsSelected = true; } data.NavItems.Add(item); } int selectedItemIndex = data.NavItems.FindIndex(item => item.IsSelected); if (selectedItemIndex > -1) { data.NavItems[selectedItemIndex].CssClass = "active"; } return(View(data)); }
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 video.* FROM Video where 1=1"); if (SearchText != "") { //sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike() ")"); sql.AddKeywordSearch(SearchText, "video.title", true); } sql.Add("and status =", StatusFilter.Sqlize_Text()); // 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.Video().GetDefaultOrderBy()); //} else { // sql.AddSql(GetOrderBySql()); //} sql.Add("ORDER BY VideoPostedDate DESC"); return(sql); }
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 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); }
protected void GetFiltersFromQueryString() { string parentFieldName = Web.Request.QueryString["parent"]; var dummy = new TActiveRecord(); foreach (var field in dummy.GetFields()) { string filterParam = Web.Request.QueryString[field.Name]; if (filterParam.IsNotBlank()) { field.FromString(filterParam); FilterSql.Add("and", field.Name.SqlizeName(), "=", field.Sqlize()); //SubTitle += " - " + field.ToString(); // this will display an ID as a number, upgrade to ToStringNice to do the lookup and show the title SubTitle += " - " + field.ToStringNice(); // MN2014: if you aren't using this and don't want to upgrade ActiveField just replace this with the line above } //if (parentFieldName==filterParam) { // // also set subtitle and go down a breadcrumb // BreadcrumbLevel++; //} } }
public override Sql GetSql() { Sql sql = new Sql("SELECT * FROM Document where 1=1"); if (Web.Request["DocumentCategoryID"] + "" != "") { sql.Add(" and DocumentCategoryID=", Web.Request["DocumentCategoryID"].ToInt().SqlizeNumber()); } if (SearchText != "") { sql.AddKeywordSearch(SearchText, new Models.Document().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); }
public override Sql GetSql() { Sql sql = new Sql("SELECT * FROM BlogComment where 1=1"); if (SearchText != "") { //sql.Add(" and ([--Field--] like ", SearchText.SqlizeLike(), ")"); // custom sql //sql.AddKeywordSearch(SearchText, "FirstName,LastName,Email", true); // search more than one field sql.AddKeywordSearch(SearchText, new Models.BlogComment().GetNameField().Name, true); // just search by name } // handle an fk (rename fkid, then uncomment) sql.Add("and blogid=", blogid); //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.BlogComment().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)); }
public void Init() { int blogid = Crypto.DecryptID(Web.Request["id"]); var blogSql = new Sql("select * from blog where blogid=", blogid, " and ispublished=1"); var rec = blogSql.GetHashtable(); BlogTitle = rec["Title"] + ""; BlogBody = rec["BodyText"] + ""; BlogDate = rec["DateAdded"] + ""; Sql mainlist = new Sql("select top 25 * from blogcomment where blogid=", blogid, " and ispublished=1 order by dateadded desc"); blogCommentList = mainlist.GetDataTable(); Url = Web.Server.UrlEncode(Web.ResolveUrlFull("~") + "blogdetail.aspx?page=" + Web.Request["page"] + "&id=" + Web.Request["id"]); bool autopublish = false; if (Web.Request["go"] != null) { int id = (Web.Session["CurrentUserID"] + "").ToInt(-1); if (id > 0) { autopublish = true; } var sql = new Sql(@"insert into blogcomment(title,bodytext ,[Company] ,[FirstName] ,[LastName] ,[Email],CommentByPersonID, blogid, dateadded, ispublished) values(" ); sql.Add("", (Web.Request["title"] + "").SqlizeText(), ",", (Web.Request["body"] + "").SqlizeText(), ","); sql.Add("", (Web.Request["company"] + "").SqlizeText(), ",", (Web.Request["firstname"] + "").SqlizeText(), ",", (Web.Request["lastname"] + "").SqlizeText(), ",", (Web.Request["email"] + "").SqlizeText(), ","); sql.Add("", (id + "").SqlizeNumber(), ",", blogid.SqlizeNumber(), ", getdate(),", autopublish.SqlizeBool(), ");select @@identity;"); decimal savedNewID = (sql).FetchDecimalOrZero(); //send email //Response.Write("ok"); string adminurl = Web.ResolveUrlFull("~/admin/") + "BlogCommentAdmin/EditEnc?encID=" + Crypto.EncryptID(savedNewID.ToInt()); //string EmailToAddress = Util.GetSetting("BlogEmailToAddress"); string EmailToAddress = Util.GetSetting("EmailToAddress"); //fromemail = "*****@*****.**"; //EmailToAddress = "*****@*****.**"; string msg = "" + "Site Admin,\n" + "\n" + " A new comment has been posted to your blog."+ "\n" + " Link to admin: "+ adminurl + "" + "\n"; if (!autopublish) { msg += " Note that you will to review the data and publish it if it has appropriate content."+ "\n"; } else { msg += " This was auto-published, as it was created by a logged in user."+ "\n"; } SendEMail.SimpleSendEmail(EmailToAddress, Util.GetSiteName() + " : New Blog Comment", msg); //reload page string url = Web.Request.RawUrl.ToString(); //Response.Redirect(url + (url.Contains("&post") ? "" : "&post=1")); } }
/// <summary> /// Example of how to create migration scripts from an old legacy databse /// </summary> /// <param name="newServerDbName"></param> /// <param name="doCreate"></param> /// <returns></returns> public static List <Sql> GenerateDataMigrationScripts(string newServerDbName, bool doCreate) { // generate models off old database with underscorized naming (connstr = guru) // newServerDbName = thatcher.rinnai var result = new List <Sql>(); var models = ActiveRecordDatabaseGenerator.GetModelInstances(); foreach (ActiveRecord model in models) { var tableName = model.GetTableName().SqlizeName().value; if (!tableName.Contains("rn_")) { continue; } if (tableName.Contains("homebanner")) { continue; } if (tableName.Contains("filetracking")) { continue; } if (tableName.Contains("rn_tracking")) { continue; } if (tableName.Contains("rn_emailtemplate")) { continue; } if (tableName.Contains("rn_categorybanner")) { continue; } if (tableName.Contains("rn_pagecontent")) { continue; } if (tableName.Contains("rn_promo")) { continue; } if (tableName.Contains("rn_pagesection")) { continue; } if (tableName.Contains("banner")) { continue; } string sourceTable, destTable; sourceTable = tableName; destTable = model.GetType().Name; destTable = destTable.Replace("tradesmart", "_tradesmart_"); destTable = destTable.Replace("centre", "_centre_"); destTable = destTable.Replace("subcategory", "_subcategory_"); destTable = destTable.Replace("training", "_training_"); destTable = destTable.Replace("file", "_file_"); destTable = destTable.Replace("association", "_association_"); destTable = destTable.Replace("group", "_group_"); destTable = destTable.Replace("manuals", "_manuals_"); destTable = destTable.Replace("archive", "_archive_"); destTable = destTable.Replace("rn_tn", "rn_trade_"); destTable = destTable.Remove("rn_").PascalCase(); bool hasID = false; var fields = new DelimitedString(","); var destfields = new DelimitedString(","); var creates = "" + destTable + "ID [int] IDENTITY(1,1) NOT NULL, "; foreach (var field in model.GetFields()) { fields += field.Name.SqlizeName().value; var newFieldName = field.Name; foreach (var repl in "meta,centre,areas,covered,subcategory,training,retailer,tradesmart,consumer,accessory,group,price,email,size,section,path,name,manuals,product,banner,studies,performance,compare,text,study,archive,video,type,manuals,content,outlet".Split(',')) { newFieldName = newFieldName.Replace(repl, "_" + repl + "_"); } newFieldName = newFieldName.Replace("dateandtime", "date_and_time"); newFieldName = Fmt.PascalCase(newFieldName); newFieldName = newFieldName.Replace("Id", "ID"); if (newFieldName == "ID" && destTable == "Country") { newFieldName = "Code"; } else if (newFieldName == "ID") { newFieldName = destTable + "ID"; hasID = true; } if (newFieldName == "Priority") { newFieldName = "SortPosition"; } if (newFieldName == "Image") { newFieldName = "Picture"; } if (newFieldName == "Created") { newFieldName = "DateAdded"; } destfields += newFieldName; if (field.Name != "id" || !hasID) { creates += newFieldName + " " + field.GetSqlDataTypeDeclaration().Replace("tinyint", "bit").Replace("smallint", "int").Replace("char", "varchar").Replace("varchar", "nvarchar").Replace("varnvarchar", "nvarchar").Replace("text", "nvarchar(max)").Replace("NOT NULL", "") + ", "; } } if (doCreate) { var sql2 = new Sql("create table " + destTable + " (<b>" + creates + "</b> CONSTRAINT [pk_" + destTable + "] PRIMARY KEY CLUSTERED (" + destTable + "ID ASC));"); result.Add(sql2); } var sql = new Sql("delete from " + destTable + ";"); // maybe use trunc table if (hasID) { sql.Add("set identity_insert " + destTable + " on;"); } sql.Add("insert into " + destTable + " (" + destfields + ") select " + fields + " from RinnaiOld.dbo." + sourceTable + ";"); if (hasID) { sql.Add("set identity_insert " + destTable + " off;"); } result.Add(sql); } foreach (var migration in result) { Web.WriteLine(migration.ToString()); } return(result); }