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