/// <summary>
/// if the HelpText doesn't exist, create it with the default value supplied
/// </summary>
/// <param name="helpTextCode"></param>
/// <param name="defaultTitle"></param>
/// <param name="defaultBodyText"></param>
protected void init(string helpTextCode, string defaultTitle, string defaultBodyText)
{
this.HelpTextCode = helpTextCode;
DataBlock db = new DataBlock();
db.OpenDB();
if (Web.Request["dropHelpTexts"] != null && Util.IsBewebOffice)
{
db.execute("delete from HelpText where HelpTextCode='" + Fmt.SqlString(helpTextCode) + "'");
}
DataBlock rs = db.execute("select * from HelpText where HelpTextCode='" + Fmt.SqlString(helpTextCode) + "'");
if (rs.eof())
{
defaultTitle = (defaultTitle.IsBlank()) ? helpTextCode + " Help" : defaultTitle;
//autocreate empty HelpText
db.execute("insert into HelpText(HelpTextCode,title,bodytexthtml,dateadded)values('" + Fmt.SqlString(helpTextCode) + "','" + Fmt.SqlString(defaultTitle) + "','" + Fmt.SqlString(defaultBodyText) + "', " + Fmt.SqlDate(DateTime.Now) + ")");
rs.close(); //close previous rs
//reopen newly created block
rs = db.open("select * from HelpText where HelpTextCode='" + Fmt.SqlString(helpTextCode) + "'");
}
_ID = rs.GetValueInt("HelpTextID");
_BodyTextHTML = rs.GetValue("BodyTextHTML");
_Title = rs.GetValue("Title");
rs.close();
db.CloseDB();
}
private string RemoveCommonWords(string keywordstr)
{
//' returns the given string with common words removed - common words are ignored in the search
string sql, newStr;
string [] keywords;
newStr = "";
keywords = keywordstr.Split(new Char[] { ' ' });
foreach (string keyword in keywords)
{
sql = "select * from CommonWord where CommonWord = '" + Fmt.SqlString(keyword.ToLower()) + "'";
//if (db.execute(sql).eof())
{
//' keyword is not in the common words list, so keep it
newStr += keyword + " ";
}
}
return(newStr.Trim());
}
/// <summary>
/// note: if you pass SearchArea on the querystring, filter the search area
/// </summary>
/// <param name="originalKeywords"></param>
/// <returns></returns>
public string BuildHitString(string originalKeywords)
{
string [] keywords;
string hitstr;
DataBlock rs;
//int score, scorestr,id;
string sql, scrubbedKeywords;
int searchAreaID;
int sortPosition;
scrubbedKeywords = ScrubKeywords(originalKeywords);
keywords = scrubbedKeywords.Split(new Char[] { ' ' });
hitstr = "";
//' search chosen areas of database
sql = "select * from SearchArea ";
string useArea = "none";
if (HttpContext.Current.Request["SearchArea"] != null) //then
{
useArea = HttpContext.Current.Request["SearchArea"];
sql = sql + " where AreaTitle='" + Fmt.SqlString(useArea) + "'";
}
sql = sql + " order by sortorder";
rs = db.execute(sql);
//rs = BewebData.GetDataSet(sql);//todo: convert to dataset usage or similar
if (rs.eof())
{
throw new Exception("missing search area data, area [" + useArea + "] not found");
}
while (!rs.eof())
{
sql = GetAreaSql(rs);
//' add a where clause, just to reduce the amount of data we need to trawl through
//' GetKeywordSearchSqlWhere is in codelib (added 27 Jun 07)
sql += " where 1=1 " + GetKeywordSearchSqlWhere(originalKeywords, rs.GetValue("TitleField") + "," + rs.GetValue("TextFields"), "stem");
// MK change: removed TitleField - so that you can write "StockCode + ' ' + Heading AS sch" for the search results
// does mean that you have to specify it again in the fields though
//sql += " where 1=1" + GetKeywordSearchSqlWhere(originalKeywords,rs.GetValue("TextFields")+""+ rs.GetValue("TextFields"), "stem");
string whereClauseFromDatabase = rs.GetValue("WhereClause") + "";
if (whereClauseFromDatabase != "")
{
whereClauseFromDatabase = CustomWhereClauseReplace(whereClauseFromDatabase);
sql += " and (" + whereClauseFromDatabase + ")";
}
if (rs.GetValue("DateField") != "")
{
sql = sql + " order by " + rs.GetValue("DateField") + " desc";
}
//Response.Write(sql + "<br>");
searchAreaID = rs.GetValueInt("searchAreaID");
sortPosition = rs.GetValueInt("SortOrder");
if (sortPosition == 0)
{
sortPosition = 50;
}
sortPosition = (int)((100.0 - (double)sortPosition) * 0.2);
if (VB.IsNull(rs.GetValue("Tablename")))
{
throw new Exception("Savvy Search: Tablename is blank. You need to specify a table name or the word 'file' to search.");
}
else if (rs.GetValue("Tablename") == "file")
{
hitstr = hitstr + BuildFileHitString(searchAreaID, rs.GetValue("Page"), rs.GetValue("PageTitle"), keywords, originalKeywords, sortPosition);
}
else //page?
{
Trace.Write("Area hit string[" + sql + "]");
hitstr = hitstr + BuildAreaHitString(searchAreaID, sql, keywords, originalKeywords, sortPosition);
}
rs.movenext();
} //loop
//rs.close
//set rs = nothing
// search html files
hitstr = hitstr + SearchHTMLFiles(keywords, originalKeywords);
//' sort the hits
hitstr = VB.trim(hitstr);
hitstr = SortDelimitedString(hitstr, ' ');
return(hitstr);
}
/// <summary>
/// if the textblock doesn't exist, create it with the default value supplied
/// </summary>
/// <param name="sectionName"></param>
/// <param name="defaultBodyText"></param>
/// <param name="isPlainText"></param>
protected void init(string sectionName, string defaultTitle, string defaultBodyText, bool isPlainText)
{
this.SectionCode = sectionName;
DataBlock db = new DataBlock();
db.OpenDB();
if (Web.Request["droptextblocks"] != null && Util.IsBewebOffice)
{
db.execute("delete from TextBlock where SectionCode='" + Fmt.SqlString(sectionName) + "'");
}
DataBlock rs = db.execute("select * from TextBlock where SectionCode='" + Fmt.SqlString(sectionName) + "'");
if (rs.eof())
{
//autocreate empty textblock
if (defaultTitle != null)
{
db.execute("insert into TextBlock(sectioncode,title,bodytexthtml,isbodyplaintext,isurlavailable,istitleavailable,ispictureavailable)values('" + Fmt.SqlString(sectionName) + "','" + Fmt.SqlString(defaultTitle) + "','" + Fmt.SqlString(defaultBodyText) + "'," + Fmt.SqlBoolean(isPlainText) + ",0,1,0)");
}
else
{
db.execute("insert into TextBlock(sectioncode,bodytexthtml,isbodyplaintext,isurlavailable,istitleavailable,ispictureavailable)values('" + Fmt.SqlString(sectionName) + "','" + Fmt.SqlString(defaultBodyText) + "'," + Fmt.SqlBoolean(isPlainText) + ",0,0,0)");
}
rs.close(); //close previous rs
//reopen newly created block
rs = db.open("select * from TextBlock where SectionCode='" + Fmt.SqlString(sectionName) + "'");
}
_ID = rs.GetValueInt("TextBlockID");
RawBody = rs.GetValue("BodyTextHTML");
_BodyText = Fmt.FmtText(RawBody);
// todo: add fmttext back (abstract?)
_BodyTextHTML = RawBody; //currCMA.FmtHtmlText();
_Title = rs.GetValue("Title");
if (!rs.FieldExists("SubTitle"))
{
new Sql("alter table textblock add SubTitle nvarchar(250);");
}
else
{
SubTitle = rs.GetValue("SubTitle");
}
if (rs.GetValueIsTrue("IsPictureAvailable"))
{
if (rs.GetValue("Picture").IsNotBlank())
{
_Picture = Util.GetAttachmentVPath() + rs.GetValue("Picture");
if (rs.FieldExists("PictureCaption"))
{
_PictureCaption = rs.GetValue("PictureCaption");
}
if (rs.FieldExists("PictureWidth"))
{
_PictureWidth = rs.GetValue("PictureWidth").ToInt(0);
_PictureHeight = rs.GetValue("PictureHeight").ToInt(0);
}
}
}
_URL = rs.GetValue("URL");
_URLCaption = rs.GetValue("URLCaption");
//}else
//{
// //bugger
// //throw new Exception("not found sectionName[" + sectionName + "]count[" + rs.RecordCount+ "]");
// rs.execute("insert into textblock(sectioncode)values()")
//}
rs.close();
db.CloseDB();
}