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