public Address(int addressId)
{
Hashtable ht = BewebData.GetValues("SELECT * FROM Address WHERE AddressId=@AddressId",
new Parameter("AddressId", TypeCode.Int32, addressId.ToString()));
if (ht.Count > 0)
{
AddressId = Convert.ToInt32(ht["AddressId"]);
Specified = ht["Specified"].ToString();
Latitude = Convert.ToDouble(ht["Latitude"]);
Longitude = Convert.ToDouble(ht["Longitude"]);
Altitude = Convert.ToDouble(ht["Altitude"]);
Zoom = Convert.ToInt32(ht["Zoom"]);
Proper = ht["Proper"].ToString();
Accuracy = Convert.ToInt32(ht["Accuracy"]);
Thoroughfare = ht["Thoroughfare"].ToString();
Locality = ht["Locality"].ToString();
SubAdministrativeArea = ht["SubAdministrativeArea"].ToString();
AdministrativeArea = ht["AdministrativeArea"].ToString();
PostalCode = ht["PostalCode"].ToString();
CountryName = ht["CountryName"].ToString();
CountryCode = ht["CountryCode"].ToString();
AddressType = ht["AddressType"].ToString();
}
}
/// <summary>
///
/// </summary>
/// <param name="depth">any integer >= 1</param>
/// <param name="showInvisible">show invisible tree items</param>
/// <param name="parentID">the parentID at the top of the returned tree - the parent is not returned. 0 = all nodes</param>
/// <returns></returns>
public static DataSet GetData(int depth, bool showInvisible, int parentID)
{
string sql = "SELECT * FROM NavTree";
Parameter parentIdParam = new Parameter("ParentID", TypeCode.String, parentID.ToString());
ParameterCollection pc = new ParameterCollection();
if (!showInvisible)
{
sql = BewebData.AppendWhereClause(sql, "AND", "IsVisible=1");
}
int lowerDepth = 0;
if (parentID > 0)
{
sql = BewebData.AppendWhereClause(sql, "AND", "Lineage LIKE '%/' + @ParentID + '/%'");
pc.Add(parentIdParam);
// get the parent node's depth
lowerDepth = Convert.ToInt32(BewebData.GetValue("SELECT Depth FROM NavTree WHERE NodeID=@ParentID", parentIdParam));
sql = BewebData.AppendWhereClause(sql, "AND", "Depth > @LowerDepth");
pc.Add("LowerDepth", TypeCode.Int32, lowerDepth.ToString());
}
if (depth > 0)
{
int higherDepth = lowerDepth + depth;
sql = BewebData.AppendWhereClause(sql, "AND", "Depth < @HigherDepth");
pc.Add("HigherDepth", TypeCode.Int32, higherDepth.ToString());
}
sql += " ORDER BY Sorting ASC";
return(BewebData.GetDataSet(sql, pc));
}
// 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);
}
//-------------------------------------------------------------------------------
protected static string GetRenderPageIterator(int?id, int depth)
{
int? parentID; //, str, title;
string result = "";
string sql;
if (id != null)
{
sql = "select RenderPage from template inner join Page on template.templateid=page.templateid where historypageid is null and PageID=";
result = BewebData.GetValue(new Sql(sql, id.Value));
if ("" + result == "")
{
parentID = BewebData.GetInt("select ParentPageID from Page where PageID=" + id);
if ((parentID != null) && parentID + "" != "" && parentID + "" != "0")
//if(parentID!=null)
{
result = GetRenderPageIterator(parentID, depth + 1);
}
else
{
result = "standardpage.aspx";
}
}
}
return(result);
}
/// <summary>
/// Given a sql statement where the first column is the data field and the secodn is the description, draw a set of option tags (call this within a select tag)
/// </summary>
/// <param name="cma">Savvy Page containing database connection to work with</param>
/// <param name="sql">SQL statement where the first column is the data field and the secodn is the description</param>
/// <param name="selectedValue">value to select if in the list</param>
/// <returns></returns>
public static string WriteDropDownOptions(string sql, string selectedValue)
{
string result = "";
DataView rs;
string value = "";
string description = "";
selectedValue = selectedValue + ""; // convert to string
DataBlock dataObject = new DataBlock(BewebData.GetConnectionString());
dataObject.OpenDB();
rs = dataObject.CreateDataSource(sql);
for (int scan = 0; scan < dataObject.RecordCount; scan++)
{
value = dataObject.GetValue(scan, 0) + ""; // convert to string
description = dataObject.GetValue(scan, 1);
result += "<option value=\"" + value + "\"";
if (value == selectedValue)
{
result += " selected";
}
result += ">" + description + "</option>" + VB.crlf;
}
dataObject.CloseDB();
return(result);
}
//
// GET: /Admin/AdminMenu/
public ActionResult Index()
{
if (Util.GetSettingBool("AdminBreadcrumbIncludeHome", true))
{
Breadcrumbs.Current.SetBreadcrumb(0, "Home", Web.Root);
}
Breadcrumbs.Current.AddBreadcrumb(1, "Admin Menu");
Security.ResetLoginLoopChecker();
var data = new ViewModel();
//data.UserName = Session[Beweb.Util.GetSiteCodeName() + "_AdminFirstName"] + " " + Session[Beweb.Util.GetSiteCodeName() + "_AdminLastName"];
Person person = Models.Person.LoadID(Security.LoggedInUserID);
if (person == null)
{
return(Redirect(Web.Root + "security/login?msg=dbusermissing"));;
}
data.LoggedInPerson = person;
data.UserName = person.FirstName + " " + person.LastName;
data.IsDevAccess = Beweb.Util.IsDevAccess();
data.LastLogin = person.LastLoginDate.FmtDateTime();
//EmailToAddress.Text = ConfigurationManager.AppSettings["EmailToAddress"];
data.Role = person.Role;
SqlConnection sc = new SqlConnection(BewebData.GetConnectionString());
//ConnectionStringDetails.Text = String.Format("Datasource: {1}, Database: {0}", sc.DataSource, sc.Database);
data.ConnectionStringDetails = String.Format("Database: {1}, Server: {0}", sc.DataSource, sc.Database);
// MN 20130214 - removed SavvyAdmin default add, no longer needed and always sets default colour to white!
return(View("AdminMenu", data));
}
private static void CheckTable()
{
if (!BewebData.TableExists("DynamicImage"))
{
new Sql("CREATE TABLE [dbo].[DynamicImage]([DynamicImageID] [int] IDENTITY(1,1) NOT NULL, [ImageUrl] [nvarchar](250) NULL, [UniqueKey] [nvarchar](150) NULL, [Version] [int] NULL,[Width] [int] NULL,[Height] [int] NULL, [CropStyle] [nvarchar](10) NULL, [OriginalFilename] [nvarchar](250) NULL, [ImageModDate] [datetime] NULL, [LastModified] [datetime] NULL, CONSTRAINT [DynamicImage_PK] PRIMARY KEY NONCLUSTERED ([DynamicImageID] ASC))").Execute();
ActiveRecordGenerator.Run();
}
}
protected void Page_Load(object sender, EventArgs e)
{
msg.Text += "Start<br/>";
string cs = BewebData.GetConnectionString();
//msg.Text+="cs ["+cs+"]<br/>";
Beweb.DataBlock db = new Beweb.DataBlock(cs);
db.OpenDB();
msg.Text += "select count(*) as cnt from Person: [" + db.FetchValue("select count(*) as cnt from Person") + "]<br/>";
db.CloseDB();
msg.Text += "Done<br/>";
}
private string WriteHierarchyOptions(
string tablename,
string primaryKeyColName,
string displayTextColName,
string parentcolname,
string SortColName,
int parentID,
int level,
string fieldName,
string fieldValue
)
{
DataBlock rs;
string sql, optionValue, description, result = "";
sql = "select * from " + tablename + "";
if (parentID != 0)
{
sql = sql + " where " + parentcolname + "=" + parentID;
}
else
{
sql = sql + " where " + parentcolname + " is null or " + parentcolname + "=0";
}
sql = sql + " order by " + SortColName;
rs = (new DataBlock(BewebData.GetConnectionString())).execute(sql);
while (!rs.eof())
{
optionValue = rs[primaryKeyColName];
description = rs[displayTextColName];
for (int i = 1; i < level; i++)
{
description = "" + VB.chr(183) + VB.chr(183) + description;
}
result += "<option ";
if (fieldValue + "" == "" + optionValue && optionValue + "" != "0")
{
result += "selected ";
}
result += "value=\"" + optionValue + "\">" + description + "</option>";
result += WriteHierarchyOptions(tablename, primaryKeyColName, displayTextColName, parentcolname, SortColName, rs.GetValueInt(0), level + 1, fieldName, fieldValue);
rs.movenext();
}
rs.close();
return(result);
}
private void Fetch(string sql)
{
OleDbDataReader reader = null;
try {
var db = new DataBlock();
db.OpenDB(BewebData.GetConnectionString());
var conn = new OleDbConnection(db.connString);
conn.Open();
var exec = new OleDbCommand(sql, conn);
var sw = Stopwatch.StartNew();
sw.Start();
reader = exec.ExecuteReader();
sw.Stop();
var fields = new List <Dictionary <string, string> >();
var data = new List <List <string> >();
if (reader.FieldCount > 0)
{
for (int i = 0; i < reader.FieldCount; i++)
{
var f = new Dictionary <string, string>();
f["name"] = reader.GetName(i);
f["type"] = reader.GetFieldType(i).ToString().Replace("System.", "");
fields.Add(f);
}
while (reader.Read())
{
var row = fields.Select(field => reader[field["name"]].ToString()).ToList();
data.Add(row);
}
}
var jsonSerialiser = new JavaScriptSerializer();
Web.Write("{\"success\": true, \"type\":\"query\", \"time\": " + sw.ElapsedMilliseconds + ", \"rows\": " + data.Count + ", \"fields\": " + jsonSerialiser.Serialize(fields) + ", \"data\": " + jsonSerialiser.Serialize(data) + "}");
} catch (Exception ex) {
Web.Write("{\"success\": false, \"error\":\"" + ex.Message + "\"}");
} finally {
if (reader != null && !reader.IsClosed)
{
reader.Close();
}
}
}
private void Init(string dropboxFullPath, string dropboxProcessedFullPath, string dropboxRejectsFullPath)
{
if (NoFilePermissionsMode)
{
if (!BewebData.TableExists("DataImport"))
{
new Sql("create table DataImport (DataImportID int identity (1,1), FileName nvarchar(100), DateImported datetime, ImportedBy nvarchar(50), constraint PK_DataImport PRIMARY KEY CLUSTERED (DataImportID))").Execute();
}
}
else
{
// create drop folder if doesn't exist
FileSystem.CreateFolder(dropboxFullPath);
FileSystem.CreateFolder(dropboxProcessedFullPath);
FileSystem.CreateFolder(dropboxRejectsFullPath);
}
}
protected void Page_Load(object sender, EventArgs e)
{
Security.RequireLogin(SecurityRolesCore.Roles.DEVELOPER);
// only show the form if there are no users in the system!
int countUsers = Convert.ToInt32(BewebData.GetValue(new Sql("SELECT COUNT(PersonId) FROM Person")));
if (countUsers == 0)
{
ShowForm.Visible = true;
DoneMessage.Visible = false;
}
else
{
ShowForm.Visible = false;
DoneMessage.Visible = true;
}
}
private string Save(Models.Comment record, bool isNew)
{
bool moveOnToNextItem = false;
// send emails (only if DeclineReason has been changed):
if (record.Fields.DeclineReason.IsDirty && (record.Status == Comment.CommentStatus.Approved.ToString() || record.Status == Comment.CommentStatus.Declined.ToString()))
{
if (record.Status == Comment.CommentStatus.Approved.ToString())
{
// mark as Approved
record.ApprovedByPersonID = Security.LoggedInUserID;
record.ApprovedDate = DateTime.Now;
}
// user email:
//Beweb.SiteCustom.Emails.CommentApproveDeclineToPerson(record);
moveOnToNextItem = true;
}
// add any code to update other fields/tables here
record.Save();
//record.RebuildCachedComments();
// save subform or related checkboxes here eg record.Lines.Save();
// regardless of what button they clicked - move on to the next item if they declined or approved something
if (moveOnToNextItem)
{
// find the next item
var nextCommentId = BewebData.GetValue(new Sql("SELECT TOP(1) CommentId FROM Comment WHERE Status=", Comment.CommentStatus.Submitted.ToString().SqlizeText()
, "AND CommentID < ", record.CommentID, " ORDER BY CommentID DESC"));
if (nextCommentId.IsBlank())
{
nextCommentId = BewebData.GetValue(new Sql("SELECT TOP(1) CommentId FROM Comment WHERE Status=", Comment.CommentStatus.Submitted.ToString().SqlizeText()
, "AND CommentID < ", record.CommentID, " ORDER BY CommentID ASC"));
if (nextCommentId.IsBlank())
{
return("~/Admin/CommentAdmin/Moderate");
}
}
return("~/Admin/CommentAdmin/Edit/" + nextCommentId);
}
return("");
}
public static void Update(string addressId, string latitude, string longitude, string altitude, string zoom)
{
if (String.IsNullOrEmpty(addressId))
{
// can't update if we don't have an addressId
}
else
{
var pc = new ParameterCollection();
pc.Add("Latitude", TypeCode.Decimal, latitude);
pc.Add("Longitude", TypeCode.Decimal, longitude);
pc.Add("Altitude", TypeCode.Decimal, altitude);
pc.Add("Zoom", TypeCode.Int32, zoom);
pc.Add("AddressId", TypeCode.Int32, addressId);
BewebData.UpdateRecord("UPDATE Address SET Latitude=@Latitude, Longitude=@Longitude, Altitude=@Altitude, Zoom=@Zoom WHERE AddressId=@AddressId", pc);
}
}
/// <summary>
/// get a url to a page given an ID (also does urlrewrite conversion and shortcuts)
/// </summary>
/// <param name="id"></param>
/// <returns></returns>
/// use Nav.LinkPage
/*
* public static string GetRenderPageURL(int? idn)
* {
* int id=(idn==null)?0:(int)idn;
* string result="";
* bool useNormal = true;
* string sql = "select title,PageIsALink,URLRewriteTitle from Page where historypageid is null and PageID=" + id;
* DataBlock rs = new DataBlock(BewebData.GetConnectionString());
* rs.OpenDB();
* rs.open(sql);
* string title = rs["title"];
* bool PageIsALink = VB.cbool(rs["PageIsALink"]);
* if(PageIsALink)
* {
* useNormal = false;
* int ?targetID = BewebData.GetInt("select URL from Page where PageID=" + id);
* if(targetID==null)
* {
* //throw new Exception(" ERROR: pageid ["+id+"] page is a link, but url is blank");useNormal
* useNormal = true;
* }else
* {
* result = GetRenderPageURL(targetID);
* }
* //result = BewebData.GetValue("select URL from Page where PageID=" + id);
* }
* rs.CloseDB();
*
*
* if(useNormal)
* {
* string rewriteURL = rs["URLRewriteTitle"];
* if (rewriteURL=="")
* {
* result = GetRenderPage(id) + "?page=" + Crypto.EncryptID(id) ;
* if(title!="")result+= "§ion=" + HttpContext.Current.Server.UrlEncode(title);
* }else
* {
* result = "~/"+rewriteURL+".aspx";
* }
* }
*
* //result = result.Replace("../admin/../","");
* return result;
* }
*/
/// <summary>
/// get a pipe sep list of parents of the given id
/// </summary>
/// <example>
/// string ancPages = currCMA.GetAncestorPages(pageID);
/// dout("ancPages["+ancPages+"]");
/// string []anc = ancPages.Split(new Char[] {'|'}, 6);
/// topid = VB.cint(anc[anc.Length-3]); //anc[anc.Length] overflow, anc[anc.Length-1] blank, anc[anc.Length-2] home, anc[anc.Length-3] below home
/// dout("topid["+topid+"]");
/// </example>
/// <param name="id">id to find parents of</param>
/// <returns>pipe sep list of parents of the given id</returns>
public static string GetAncestorPages(int?id)
{
string result = "";
int? parentID;
if (id != -1)
{
parentID = BewebData.GetInt("select ParentPageID from Page where PageID=" + id);
if (parentID == 0)
{
result = "";
}
else
{
result = parentID + "|" + GetAncestorPages(parentID);
}
//response.Write("GetAncestorPages["&GetAncestorPages&"]")
}
return(result);
}
public static string GetRenderPageSectionURL(string sectionCode, bool encrypt)
{
string result = "";
string linkValue = BewebData.GetValue(new Sql("select pageid from page where pagecode=", sectionCode.SqlizeText(), " and historypageid is null"));
if (linkValue != "")
{
result = GetRenderPage(linkValue) + "?page=" + ((encrypt)?Crypto.EncryptID(linkValue):linkValue);
//'Response.Write("<font color=""red"" size=""1"" face=""sans-serif"">DEBUG: linkValue["&linkValue&"]</font><br>"+vbcrlf)
}
else
{
Logging.eout("ERROR: failed to find page code [" + sectionCode + "]");
HttpContext.Current.Response.End();
}
if (result.StartsWith("admin/../"))
{
result = result.Replace("admin/../", "");
}
return(result);
}
public ActionResult RunDailyTasks()
{
Logging.dlog("RunDailyTasks");
Web.CacheClearAll();
// if there is anything to do every day, do it here
#if AutocompletePhrase
// re-do autocomplete phrases for expired content (assumes content tends to expire daily)
AutocompletePhrase.AutocompletePhraseCleanup();
#endif
#if ModificationLog
//daily remove old mod logs
if (BewebData.GetValueInt(new Sql("select count(*) from sys.objects where name like ", "ModificationLogTemp".SqlizeLike()), 0) > 0)
{
// remove the temp table if it exists
new Sql("drop table ModificationLogTemp").Execute();
}
var s = @"
select * into ModificationLogTemp from ModificationLog where UpdateDate > getdate()-90;
truncate table ModificationLog;
set identity_insert ModificationLog ON;
insert into ModificationLog ([ModificationLogID],[UpdateDate],[PersonID],[TableName],[RecordID],[ActionType],[UserName],[ChangeDescription],[RecordIDChar]) select [ModificationLogID],[UpdateDate],[PersonID],[TableName],[RecordID],[ActionType],[UserName],[ChangeDescription],[RecordIDChar] from ModificationLogTemp;
set identity_insert ModificationLog OFF;
drop table ModificationLogTemp";
new Sql(s).Execute();
#endif
Logging.dlog("RunDailyTasks start");
//TASKS here
//update the run time in the database
Settings.All.ScheduledTaskLastDailyRunTime = DateTime.Now;
Settings.All.Save();
//SendEMail.SendDeadLetter("RunDailyTasks",30);
Logging.dlog("RunDailyTasks done");
return(Content("OK"));
}
private string OKToImport(string otherServerEnvironment)
{
/* This can expanded upon as we think of more ways to prevent a bad import */
string response = "OK";
var referer = Web.ServerVars["HTTP_REFERER"] + "";
var host = Web.ServerVars["HTTP_HOST"] + "";
string localConnectionString = BewebData.GetConnectionString("ConnectionString" + Util.ServerIs());
string remoteConnectionString = BewebData.GetConnectionString("ConnectionString" + otherServerEnvironment);
if (!referer.Contains(host))
{
response = "<p><strong>Did not import.</strong></p><p>You cannot import data by directly navigating to this page.</p>";
}
else if (localConnectionString == remoteConnectionString)
{
response = "<p><strong>Did not import.</strong></p><p>You cannot do an import where both connection strings are the same.</p>";
}
else if (BewebData.TableExists("Settings"))
{
// TO DISCUSS, THERE ARE LOTS OF WAYS THIS COULD BECOME PROBLEMATIC.
/*
* Settings settings = Settings.Load(new Sql("Select top 1 * from Settings"));
* if (settings.FieldExists("ServerEnvironment")) {
* if (settings["ServerEnvironment"].ToString() == otherServerEnvironment){
* response = "<p><strong>Did not import.</strong></p><p>The 'ServerEnvironment' setting is the same as the current server. Please make sure you are on the correct server, the 'ServerEnvironment' setting is correct and try again.</p>";
* }
* } else {
* new Sql("ALTER TABLE settings ADD ServerEnvironment nvarchar(3);").Execute();
* settings["ServerEnvironment"].ValueObject = Util.ServerIs();
* settings.Save();
* // setting doesnt exisit so dont assum its ok. Let the user try again
* response = "<p><strong>Did not import.</strong></p><p>The 'ServerEnvironment' setting was missing from the settings. This setting has been added and set to '" + Util.ServerIs() + "'. If this is incorrect please correct the setting and try again.</p>";
* }
*/
}
return(response);
}
public string WriteHierarchySelector(
string tablename,
string primaryKeyColName,
string displayTextColName,
string parentcolname,
string SortColName,
string fieldName,
string fieldValue
)
{
DataBlock rs;
string result = "";
string sql;
sql = "select * from " + tablename + " where " + parentcolname + " is null or " + parentcolname + " =0 ";
sql = sql + " order by " + SortColName;
rs = (new DataBlock(BewebData.GetConnectionString())).execute(sql);
if (rs.eof())
{
result += "<input type=hidden name=\"" + parentcolname + "\" value='0'>";
result += "None";
}
else
{
result += "<select size=1 name=\"" + fieldName + "\">";
result += "<option value=\"\">-- please select --</option>";
if (primaryKeyColName != fieldName)
{
//result += WriteOptionWithDescription("0", fieldValue, "--Top Level--");
}
result += WriteHierarchyOptions(tablename, primaryKeyColName, displayTextColName, parentcolname, SortColName, 0, 0, fieldName, fieldValue);
result += "</select>";
}
rs.close();
return(result);
}
public void Insert()
{
// save the new details
var pc = new ParameterCollection();
pc.Add("Specified", TypeCode.String, Specified);
pc.Add("Latitude", TypeCode.Decimal, Latitude.ToString());
pc.Add("Longitude", TypeCode.Decimal, Longitude.ToString());
pc.Add("Altitude", TypeCode.Decimal, Altitude.ToString());
pc.Add("Zoom", TypeCode.Int32, Zoom.ToString());
pc.Add("Proper", TypeCode.String, Proper);
pc.Add("Accuracy", TypeCode.Int32, Accuracy.ToString());
pc.Add("Thoroughfare", TypeCode.String, Thoroughfare);
pc.Add("Locality", TypeCode.String, Locality);
pc.Add("SubAdministrativeArea", TypeCode.String, SubAdministrativeArea);
pc.Add("AdministrativeArea", TypeCode.String, AdministrativeArea);
pc.Add("PostalCode", TypeCode.String, PostalCode);
pc.Add("CountryName", TypeCode.String, CountryName);
pc.Add("CountryCode", TypeCode.String, CountryCode);
pc.Add("AddressType", TypeCode.String, AddressType);
AddressId = BewebData.InsertRecord("INSERT INTO Address (Specified, Latitude, Longitude, Altitude, Zoom, Proper, Accuracy, Thoroughfare, Locality, SubAdministrativeArea, AdministrativeArea, PostalCode, CountryName, CountryCode, AddressType) " +
"VALUES (@Specified, @Latitude, @Longitude, @Altitude, @Zoom, @Proper, @Accuracy, @Thoroughfare, @Locality, @SubAdministrativeArea, @AdministrativeArea, @PostalCode, @CountryName, @CountryCode, @AddressType)", pc);
}
public static void Refresh(string tableName)
{
string refreshSql =
@"
DELETE FROM NavTree;
INSERT INTO NavTree (TableName, NodeID, ParentID, Title, URL, SortPosition, IsVisible, IsJustALink, ItemCode, RoleAllowed) SELECT 'Page', PageID, ParentPageID, CASE WHEN NavTitle='' or NavTitle is null THEN Title ELSE NavTitle END, CASE WHEN LinkUrl='' or IsLinkUrl is null THEN URLRewriteTitle ELSE IsLinkUrl END, SortPosition, ShowInNav, CASE WHEN IsLinkUrl='' or IsLinkUrl is null THEN 0 ELSE 1 END, PageCode, RoleAllowed FROM Page ORDER BY SortPosition ASC;
UPDATE NavTree SET
Lineage='/' + CONVERT(nvarchar, NodeID) + '/',
Sorting='/' + REPLACE(STR(SortPosition*NavTreeID,12,0),' ','0') + '/',
Depth=0
WHERE ParentID = 0;
WHILE EXISTS (SELECT * FROM NavTree WHERE Depth Is Null)
UPDATE T SET T.Depth = P.Depth + 1,
T.Lineage = P.Lineage + CONVERT(nvarchar, T.NodeID) + '/',
T.Sorting = P.Sorting + REPLACE(STR(T.SortPosition*T.NavTreeID,12,0),' ','0') + '/'
FROM NavTree AS T
INNER JOIN NavTree AS P ON T.ParentID = P.NodeID
WHERE P.Depth >=0 AND P.Lineage IS NOT NULL AND T.Depth IS NULL;";
BewebData.ExecuteSQL(refreshSql, true);
}
// Overrides of default functionality go here (eg SortOrder, GetUrl, IsActive, Save)
// Hint: to override methods using Resharper, hit Alt-Insert
// You can put any business logic associated with this entity here
public static int DownloadCount(int documentID)
{
int count = BewebData.GetValueInt(new Sql("Select Count (*) from DocumentDownload where DocumentID=", documentID.SqlizeNumber())).ToInt();
return(count);
}
protected void Page_Load(object sender, EventArgs e)
{
return;
var excludedExtensions = new[] { ".config", ".txt" };
var directory = new DirectoryInfo(Web.MapPath(Web.Attachments));
var diskAttachments = directory.GetFiles("*.*", SearchOption.AllDirectories).Where(f => (f.Attributes & FileAttributes.Hidden) == 0 && !excludedExtensions.Contains(f.Extension)).OrderByDescending(f => f.Length).Select(file => file.FullName.RightFrom(Web.MapPath(Web.Attachments)).ToLower().Replace("\\", "/")).ToList();
var dbAttachments = new HashSet <string>();
foreach (var tableName in BewebData.GetTableNames())
{
var sql = new Sql("select top 1 * from ", tableName.SqlizeName());
var sb = new StringBuilder("");
using (var reader = sql.GetReader()) {
if (reader.HasRows)
{
int visibleFieldCount = reader.VisibleFieldCount;
for (int i = 0; i < visibleFieldCount; i++) // for each column
{
string dataType = reader.GetDataTypeName(i).ToLower();
string fieldName = reader.GetName(i).ToLower();
bool isAttach = (dataType.Contains("varchar") && (fieldName.Contains("attachment") || fieldName.Contains("picture")));
bool isRich = ((dataType.Contains("varchar") || dataType.Contains("text")) && (fieldName.Contains("html") || fieldName.Contains("body") || fieldName.Contains("text")));
if (isAttach || isRich)
{
sb.Append(fieldName).Append(",");
}
}
}
}
var fields = sb.ToString();
fields = fields.IsNotBlank() ? fields.Substring(0, fields.Length - 1) : ""; // remove last comma
if (fields.IsNotBlank())
{
sql = new Sql("select " + fields + " from ", tableName.SqlizeName());
using (var reader = sql.GetReader()) {
foreach (DbDataRecord record in reader)
{
for (int i = 0; i < reader.VisibleFieldCount; i++) // for each column
{
string fieldName = record.GetName(i).ToLower();
bool isAttach = ((fieldName.Contains("attachment") || fieldName.Contains("picture")));
if (!record.IsDBNull(i))
{
string fieldValue = record.GetString(i);
if (fieldValue.IsNotBlank())
{
if (isAttach)
{
dbAttachments.Add(fieldValue);
}
else
{
// @todo: Regex against the value to extract the images
}
}
}
}
}
}
}
}
diskAttachments.RemoveAll(dbAttachments.Contains);
foreach (var att in diskAttachments)
{
Web.WriteLine(att);
}
}
private void PrecacheAttachmentsDB()
{
try {
var dbAttachments = new HashSet <string>();
foreach (var tableName in BewebData.GetTableNames())
{
var sql = new Sql("select top 1 * from ", tableName.SqlizeName());
var sb = new StringBuilder("");
using (var reader = sql.GetReader()) {
if (reader.HasRows)
{
int visibleFieldCount = reader.VisibleFieldCount;
for (int i = 0; i < visibleFieldCount; i++) // for each column
{
string dataType = reader.GetDataTypeName(i).ToLower();
string fieldName = reader.GetName(i).ToLower();
bool isAttach = (dataType.Contains("varchar") && (fieldName.Contains("attachment") || fieldName.Contains("picture")));
bool isRich = ((dataType.Contains("varchar") || dataType.Contains("text")) && (fieldName.Contains("html") || fieldName.Contains("body") || fieldName.Contains("text")));
if (isAttach || isRich)
{
sb.Append(fieldName).Append(",");
}
}
}
}
var fields = sb.ToString();
fields = fields.IsNotBlank() ? fields.Substring(0, fields.Length - 1) : ""; // remove last comma
if (fields.IsNotBlank())
{
sql = new Sql("select " + fields + " from ", tableName.SqlizeName());
using (var reader = sql.GetReader()) {
foreach (DbDataRecord record in reader)
{
for (int i = 0; i < reader.VisibleFieldCount; i++) // for each column
{
string fieldName = record.GetName(i).ToLower();
bool isAttach = ((fieldName.Contains("attachment") || fieldName.Contains("picture")));
if (!record.IsDBNull(i))
{
string fieldValue = record.GetString(i);
if (fieldValue.IsNotBlank())
{
if (isAttach)
{
dbAttachments.Add(MakeFilePathRelativeToAttachments(fieldValue));
}
else
{
foreach (var extractedImage in ExtractImagesFromHTML(fieldValue))
{
dbAttachments.Add(MakeFilePathRelativeToAttachments(extractedImage));
}
foreach (var extractedDocument in ExtractDocumentsFromHTML(fieldValue))
{
dbAttachments.Add(MakeFilePathRelativeToAttachments(extractedDocument));
}
}
}
}
}
}
}
}
}
Session["dbAttachments"] = dbAttachments;
Web.Write("{\"success\": true, \"result\":\"" + dbAttachments.Count + " references found\"}");
} catch (Exception ex) {
Web.Write("{\"success\": false, \"error\":\"" + ex.Message + "\"}");
}
}
public ActionResult SSODotNet()
{
// to use this call:
// sso_classic.asp?ssoDotNet=Utils%2fsso_dotnet.aspx&success=PartSale.aspx&fail=login.asp
// params:
// success: page to redirect to if cookie is there and GUID matches, i.e. successful sso. Remember to UrlEncode this value ESPECIALLY if it contains query string stuff
// fail: fail page
string AdminId = String.Empty;
//if(Beweb.Util.ServerIs() == "DEV")
//{
// // don't bother checking - auto log in for dev
// //AdminId = Request.QueryString["administratorId"]; // 161 for matt
// AdminId = Beweb.BewebData.GetValue(
// "SELECT AdministratorID FROM Administrator WHERE Email=@Email",
// new Parameter("Email", TypeCode.String, "matt"),
// BewebData.GetConnectionString("ExtranetConnectionString"));
//}
//else
//{
if (String.IsNullOrEmpty(Request.QueryString["sso"]))
{
return(RedirectFail());
}
string ssoGuid = Request.QueryString["sso"];
// output.Text += String.Format("<br /><br />[{0}]", ssoGuid);
// check the database for the ssoGuid - make sure it was just set (with some leeway)
AdminId = BewebData.GetValue(
"SELECT AdministratorId FROM Administrator WHERE SsoGuid=@SsoGuid AND SsoSetTime>DATEADD(minute, -2, GETDATE())",
new Parameter("SsoGuid", TypeCode.String, ssoGuid),
BewebData.GetConnectionString("ExtranetConnectionString"));
//}
if (String.IsNullOrEmpty(AdminId))
{
return(RedirectFail());
}
string securityRoles = BewebData.GetValue(new Sql("select role from Administrator WHERE AdministratorID=", AdminId.SqlizeNumber()), BewebData.GetConnectionString("ExtranetConnectionString"));
string name = BewebData.GetValue(new Sql().AddRawSqlString("select firstname+' ' +lastname as name from Administrator").Add(" WHERE AdministratorID=", AdminId.SqlizeNumber()), BewebData.GetConnectionString("ExtranetConnectionString"));
//FormsAuthentication.Initialize();
//FormsAuthentication.HashPasswordForStoringInConfigFile(AdminId, "sha1");
//// Create a new ticket used for authentication
//FormsAuthenticationTicket ticket = new FormsAuthenticationTicket(
// 1, // Ticket version
// AdminId, // Username to be associated with this ticket
// DateTime.Now, // Date/time issued
// DateTime.Now.AddMinutes(60), // Date/time for login to expire (web.config setting is ignored)
// true, // "true" for a persistent user cookie (could be a checkbox on form)
// securityRoles, // User-data (the roles from this user record in our database)
// FormsAuthentication.FormsCookiePath); // Path cookie is valid for
//// Hash the cookie for transport over the wire
//string hash = FormsAuthentication.Encrypt(ticket);
//HttpCookie cookie = new HttpCookie(FormsAuthentication.FormsCookieName, hash);
//Response.Cookies.Add(cookie);
Security.SetSecurityCookies(name, false, AdminId);
Security.CreateAuthCookie(securityRoles, AdminId);
//UserSession.Person = Models.Person.LoadID();
//var login = new Security().Login(AdminId);
//Security.LoggedInUserID = AdminId
//UserSession.Person = Models.Person.LoadID(AdminId.ToIntOrDie());
return(RedirectSuccess());
}
private void AttachmentExistsOnDB()
{
// @todo: Get a list of all attachments (disk) and put in a string array
// @todo: Get a list of all attachments (db) and put in a string array
// @todo: Using linq, merge (remove)
var foundOnTable = "";
try {
var file = Request["filename"].Base64Decode();
var attachmentName = file.RightFrom(Web.MapPath(Web.Attachments)).ToLower().Replace("\\", "/");
foreach (var tableName in BewebData.GetTableNames())
{
if (foundOnTable.IsNotBlank())
{
break;
}
var fields = Cache[tableName + "Fields"] != null ? (string)Cache[tableName + "Fields"] : null;
if (fields == null)
{
var sql = new Sql("select top 1 * from ", tableName.SqlizeName());
var sb = new StringBuilder("");
using (var reader = sql.GetReader()) {
if (reader.HasRows)
{
int visibleFieldCount = reader.VisibleFieldCount;
for (int i = 0; i < visibleFieldCount; i++) // for each column
{
string dataType = reader.GetDataTypeName(i).ToLower();
string fieldName = reader.GetName(i).ToLower();
bool isAttach = (dataType.Contains("varchar") && (fieldName.Contains("attachment") || fieldName.Contains("picture")));
bool isRich = ((dataType.Contains("varchar") || dataType.Contains("text")) && (fieldName.Contains("html") || fieldName.Contains("body") || fieldName.Contains("text")));
if (isAttach || isRich)
{
sb.Append(fieldName).Append(",");
}
}
}
}
fields = sb.ToString();
fields = fields.IsNotBlank() ? fields.Substring(0, fields.Length - 1) : ""; // remove last comma
Cache[tableName + "Fields"] = fields;
}
if (fields.IsNotBlank())
{
var sql = new Sql("select " + fields + " from ", tableName.SqlizeName());
using (var reader = sql.GetReader()) {
foreach (DbDataRecord record in reader)
{
if (foundOnTable.IsNotBlank())
{
break;
}
for (int i = 0; i < reader.VisibleFieldCount; i++) // for each column
{
string fieldName = record.GetName(i).ToLower();
bool isAttach = ((fieldName.Contains("attachment") || fieldName.Contains("picture")));
if (!record.IsDBNull(i))
{
string fieldValue = record.GetString(i);
if (fieldValue.IsNotBlank())
{
if (fieldValue == attachmentName || (!isAttach && fieldValue.ToLower().Contains(attachmentName)))
{
foundOnTable = tableName;
break;
}
}
}
}
}
}
}
}
} catch {
Web.Write("{\"success\":false}");
return;
}
Web.Write("{\"success\":true, \"foundOnTable\": \"" + foundOnTable + "\"}");
}
public void DeleteUnusedAttachments()
{
bool verbose = true;
var files = Directory.GetFiles(Web.MapPath(Web.Attachments), "*.*", SearchOption.AllDirectories);
Web.Flush("Indexing files...<br>");
var attachments = new List <string>();
foreach (var file in files)
{
var attachmentName = file.RightFrom(Web.MapPath(Web.Attachments)).ToLower();
if (!attachmentName.Contains("todelete\\"))
{
attachments.Add(attachmentName.Replace("\\", "/"));
}
}
Web.Flush("Total " + attachments.Count + " files...<br>");
foreach (var tableName in BewebData.GetTableNames())
{
Web.Flush("Table: " + tableName + "<br>");
var sql = new Sql("select top 1 * from ", tableName.SqlizeName());
var fields = new DelimitedString(",");
using (var reader = sql.GetReader()) {
if (verbose)
{
Web.Flush("Checking structure...<br>");
}
int rec = 0;
if (reader.HasRows)
{
int visibleFieldCount = reader.VisibleFieldCount;
for (int i = 0; i < visibleFieldCount; i++) // for each column
{
string dataType = reader.GetDataTypeName(i).ToLower();
string fieldName = reader.GetName(i).ToLower();
bool isAttach = (dataType.Contains("varchar") && (fieldName.Contains("attachment") || fieldName.Contains("picture")));
bool isRich = ((dataType.Contains("varchar") || dataType.Contains("text")) && (fieldName.Contains("html") || fieldName.Contains("body") || fieldName.Contains("text")));
if (isAttach || isRich)
{
fields += fieldName;
}
}
}
}
if (fields.IsBlank)
{
Web.Flush("Skipping table as no relevant field names<br>");
}
else
{
sql = new Sql("select " + fields.ToString() + " from ", tableName.SqlizeName());
Web.Flush("Searching table... " + sql.Value + "<br>");
using (var reader = sql.GetReader()) {
Web.Flush("Scanning records...<br>");
int rec = 0;
foreach (DbDataRecord record in reader)
{
rec++;
var foundAttachments = new List <string>();
int visibleFieldCount = reader.VisibleFieldCount;
for (int i = 0; i < visibleFieldCount; i++) // for each column
{
string fieldName = record.GetName(i).ToLower();
bool isAttach = ((fieldName.Contains("attachment") || fieldName.Contains("picture")));
if (!record.IsDBNull(i))
{
string fieldValue = record.GetString(i);
if (fieldValue.IsNotBlank())
{
foreach (var attachmentName in attachments)
{
if (fieldValue == attachmentName || (!isAttach && fieldValue.ToLower().Contains(attachmentName)))
{
if (verbose)
{
Web.WriteLine(" Found: " + attachmentName + " in " + tableName);
}
foundAttachments.Add(attachmentName);
}
}
}
attachments.RemoveAll(a => foundAttachments.Contains(a));
}
}
if (rec % 100 == 0)
{
Web.Flush("Scanned: " + rec + " records<br>");
}
}
}
}
}
Web.Flush("Finished checking. Located " + attachments.Count + " unused attachments<br>");
int totalSize = 0;
int cnt = 0;
foreach (var attachmentName in attachments)
{
var size = FileSystem.GetFileSizeBytes(Web.Attachments + attachmentName);
totalSize += size;
Web.WriteLine("Not found: " + attachmentName + " " + Fmt.FileSize(size, 2));
if (Request["doit"] == "1")
{
FileSystem.Move(Web.Attachments + attachmentName, Web.Attachments + "todelete", false);
}
cnt++;
if (cnt % 100 == 0)
{
Web.Flush("Archived: " + cnt + " files<br>");
}
}
Web.WriteLine("Total size: " + Fmt.FileSize(totalSize, 2));
//DirectoryInfo di = new DirectoryInfo(Server.MapPath(Web.Attachments+"trademe/"));
////read all files into a list
//var files = new List<string>();
//foreach (var file in di.EnumerateFiles()) {
// Web.Write(file.Name);
// files.Add(file.Name);
//}
////read db records, remove from files list if they exist in the database
//var sql = new Sql("select * from trademelisting");
//foreach (DbDataRecord row in sql.GetReader()) {
// //int pageid = (int)row["pageID"];
// //C:\data\dev\web\Honda\PublicServices\attachments\trademe\HAS1001_1709195_9_tn.jpg
// //C:\data\dev\web\Honda\PublicServices\attachments\trademe\HAS1001_1709181_8.jpg
// //
// for(int scanIndex=0;scanIndex<20;scanIndex++){
// var name = row["DealerCode"]+"_"+row["ID"]+"_"+scanIndex;
// var nameThumb = name+"_tn.jpg";
// if(files.Contains(nameThumb))files.Remove(nameThumb);
// name = name+".jpg";
// if(files.Contains(name))files.Remove(name);
// }
//}
////delete all files remaining in the list
//foreach(var file in files) {
// string filename = Server.MapPath(Web.Attachments+"trademe/")+file;
// FileSystem.Delete(filename);
//}
Web.InfoMessage = "Deleted Unused Images and Attachment Files";
}
/// <summary>
///
/// </summary>
/// <param name="sectionCode">sectionCode or pagecode in page table</param>
/// <returns></returns>
public static int GetRenderPageID(string sectionCode)
{
string sql = "select pageid from page where historypageid is null and pagecode='" + sectionCode + "'";
return((new DataBlock(BewebData.GetConnectionString(), true)).open(sql).GetValueInt(0)); //dont call execute (no close)
}
public SavvySearch()
{
db = new DataBlock(BewebData.GetConnectionString());
db.OpenDB(BewebData.GetConnectionString());
}
// You can put any business logic associated with this entity here
public bool EmailAlreadyInUse()
{
var check = BewebData.GetValue(new Sql("SELECT TOP 1 PersonID FROM Person WHERE Email=", Email.SqlizeText()));
return(check.IsNotBlank());
}
