public bool Update(out string Err, MySqlConnection ConX = null) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"UPDATE telesoftware SET `Key`=@Key, Contents=@Contents, FileName=@FileName, OwnerID=@OwnerID WHERE TeleSoftwareID=@TeleSoftwareID; SELECT ROW_COUNT();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("TeleSoftwareID", TeleSoftwareID); cmd.Parameters.AddWithValue("Key", Key); cmd.Parameters.AddWithValue("Contents", Contents); cmd.Parameters.AddWithValue("FileName", FileName); int?ownerID = OwnerID <= 0 ? null : (int?)OwnerID; cmd.Parameters.AddWithValue("OwnerID", ownerID); int rv = cmd.ExecuteScalarInt32(); if (rv <= 0) { Err = "The file could not be saved."; } return(rv > 0); } catch (Exception ex) { if (ex.Message.ToLower().Contains("duplicate entry")) { Err = "File '" + (Key ?? "") + "' already exists."; } else { Err = ex.Message; } return(false); } finally { if (openConX) { ConX.Close(); } } }
public bool Create(out string Err, MySqlConnection ConX = null) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"INSERT INTO telesoftware (`Key`,Contents,FileName,OwnerID) VALUES(@Key,@Contents,@FileName,@OwnerID); SELECT LAST_INSERT_ID();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("Key", Key); cmd.Parameters.AddWithValue("Contents", Contents); cmd.Parameters.AddWithValue("FileName", FileName); int?ownerID = OwnerID <= 0 ? null : (int?)OwnerID; cmd.Parameters.AddWithValue("OwnerID", ownerID); int rv = cmd.ExecuteScalarInt32(); if (rv > 0) { TeleSoftwareID = rv; } if (TeleSoftwareID <= 0) { Err = "The file could not be saved."; } return(TeleSoftwareID > 0); } catch (Exception ex) { if (ex.Message.ToLower().Contains("duplicate entry")) { Err = "File '" + (Key ?? "") + "' already exists."; } else { Err = ex.Message; } return(false); } finally { if (openConX) { ConX.Close(); } } }
public bool Update(out string Err) { Err = ""; try { using (var con = new MySqlConnection(DBOps.ConnectionString)) { con.Open(); string sql = @"UPDATE aspnetusers SET email=@email, emailconfirmed=@emailconfirmed, mailbox=@mailbox, FirstName=@FirstName, LastName=@LastName WHERE id=@id; SELECT ROW_COUNT();"; var cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("email", (Email ?? "").Trim()); cmd.Parameters.AddWithValue("emailconfirmed", EmailConfirmed); cmd.Parameters.AddWithValue("mailbox", (Mailbox ?? "").Trim()); cmd.Parameters.AddWithValue("FirstName", (FirstName ?? "").Trim()); cmd.Parameters.AddWithValue("LastName", (LastName ?? "").Trim()); cmd.Parameters.AddWithValue("id", (ID ?? "").Trim()); int rv = cmd.ExecuteScalarInt32(); if (rv <= 0) { Err = "The user could not be saved."; } if (!string.IsNullOrWhiteSpace(ID)) { NXtelData.Roles.SaveForUser(ID, Roles, out Err, con); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } Permissions.Save(ID, out Err, con); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } } return(rv > 0); } } catch (Exception ex) { Err = ex.Message; return(false); } }
public static int GetPageID(int PageNo, int FrameNo) { using (var con = new MySqlConnection(DBOps.ConnectionString)) { con.Open(); string sql = @"SELECT IFNULL(MIN(PageID),-1) AS PageID FROM `page` WHERE PageNo=" + PageNo + @" AND FrameNo=" + FrameNo; var cmd = new MySqlCommand(sql, con); int rv = cmd.ExecuteScalarInt32(); return(rv > 0 ? rv : -1); } }
public bool Create(out string Err, MySqlConnection ConX = null) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"INSERT INTO zone (Description) VALUES(@Description); SELECT LAST_INSERT_ID();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("Description", (Description ?? "").Trim()); int rv = cmd.ExecuteScalarInt32(); if (rv > 0) { ID = rv; } if (ID <= 0) { Err = "The zone could not be saved."; } return(ID > 0); } catch (Exception ex) { if (ex.Message.ToLower().Contains("duplicate entry")) { Err = "Zone " + ID + " already exists."; } else { Err = ex.Message; } return(false); } finally { if (openConX) { ConX.Close(); } } }
public bool Update(out string Err, MySqlConnection ConX = null) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"UPDATE zone SET Description=@Description WHERE ZoneID=@ZoneID; SELECT ROW_COUNT();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("Description", (Description ?? "").Trim()); cmd.Parameters.AddWithValue("ZoneID", ID); int rv = cmd.ExecuteScalarInt32(); if (rv <= 0) { Err = "The zone could not be saved."; } return(rv > 0); } catch (Exception ex) { if (ex.Message.ToLower().Contains("duplicate entry")) { Err = "Zone " + ID + " already exists."; } else { Err = ex.Message; } return(false); } finally { if (openConX) { ConX.Close(); } } }
public bool IsPageRangeValid() { using (var con = new MySqlConnection(DBOps.GetConnectionString(Environment))) { con.Open(); string sql = @"SELECT COUNT(*) AS cnt FROM `page` WHERE PageID<>@PageID AND FromPageFrameNo<@ToPageFrameNo AND @FromPageFrameNo<ToPageFrameNo;"; var cmd = new MySqlCommand(sql, con); cmd.Parameters.AddWithValue("FromPageFrameNo", NormalisedFromPageFrameNo); cmd.Parameters.AddWithValue("ToPageFrameNo", NormalisedToPageFrameNo); cmd.Parameters.AddWithValue("PageID", PageID); return(cmd.ExecuteScalarInt32() == 0); } }
public bool Update(out string Err, MySqlConnection ConX = null, bool SaveChildren = true) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"UPDATE template SET Description=@Description,X=@X,Y=@Y,Width=@Width,Height=@Height,Expression=@Expression,URL=@URL, Contents=@Contents,IsContainer=@IsContainer,IsRepeatingItem=@IsRepeatingItem,CanExpand=@CanExpand, StickToTop=@StickToTop,StickToBottom=@StickToBottom,ContinuedOver=@ContinuedOver, ContinuedFrom=@ContinuedFrom,NotContinuedOver=@NotContinuedOver,NotContinuedFrom=@NotContinuedFrom, KeepTogether=@KeepTogether,MinOrphanWidowRows=@MinOrphanWidowRows,OwnerID=@OwnerID WHERE TemplateID=@TemplateID; SELECT ROW_COUNT();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("TemplateID", TemplateID); cmd.Parameters.AddWithValue("Description", (Description ?? "").Trim()); cmd.Parameters.AddWithValue("X", X); cmd.Parameters.AddWithValue("Y", Y); cmd.Parameters.AddWithValue("Width", Width); cmd.Parameters.AddWithValue("Height", Height); cmd.Parameters.AddWithValue("Expression", (Expression ?? "").Trim()); cmd.Parameters.AddWithValue("URL", (URL ?? "").Trim()); cmd.Parameters.AddWithValue("Contents", Contents); cmd.Parameters.AddWithValue("IsContainer", IsContainer); cmd.Parameters.AddWithValue("IsRepeatingItem", IsRepeatingItem); cmd.Parameters.AddWithValue("CanExpand", CanExpand); cmd.Parameters.AddWithValue("StickToTop", StickToTop); cmd.Parameters.AddWithValue("StickToBottom", StickToBottom); cmd.Parameters.AddWithValue("ContinuedOver", ContinuedOver); cmd.Parameters.AddWithValue("ContinuedFrom", ContinuedFrom); cmd.Parameters.AddWithValue("NotContinuedOver", NotContinuedOver); cmd.Parameters.AddWithValue("NotContinuedFrom", NotContinuedFrom); cmd.Parameters.AddWithValue("KeepTogether", KeepTogether); cmd.Parameters.AddWithValue("MinOrphanWidowRows", MinOrphanWidowRows); int?ownerID = OwnerID <= 0 ? null : (int?)OwnerID; cmd.Parameters.AddWithValue("OwnerID", ownerID); int rv = cmd.ExecuteScalarInt32(); if (rv <= 0) { Err = "The template could not be saved."; } if (TemplateID > 0 && SaveChildren) { ChildTemplates.SaveChildenForTemplate(TemplateID, out Err, ConX); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } } return(TemplateID > 0); } catch (Exception ex) { Err = ex.Message; return(false); } finally { if (openConX) { ConX.Close(); } } }
public bool Create(out string Err, MySqlConnection ConX = null) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"INSERT INTO template (Description,X,Y,Width,Height,Expression,URL,Contents,IsContainer,IsRepeatingItem,CanExpand, StickToTop,StickToBottom,ContinuedOver,ContinuedFrom,NotContinuedOver,NotContinuedFrom,KeepTogether, MinOrphanWidowRows,OwnerID) VALUES(@Description,@X,@Y,@Width,@Height,@Expression,@URL,@Contents,@IsContainer,@IsRepeatingItem,@CanExpand, @StickToTop,@StickToBottom,@ContinuedOver,@ContinuedFrom,@NotContinuedOver,@NotContinuedFrom,@KeepTogether, @MinOrphanWidowRows,@OwnerID); SELECT LAST_INSERT_ID();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("Description", (Description ?? "").Trim()); cmd.Parameters.AddWithValue("X", X); cmd.Parameters.AddWithValue("Y", Y); cmd.Parameters.AddWithValue("Width", Width); cmd.Parameters.AddWithValue("Height", Height); cmd.Parameters.AddWithValue("Expression", (Expression ?? "").Trim()); cmd.Parameters.AddWithValue("URL", (URL ?? "").Trim()); cmd.Parameters.AddWithValue("Contents", Contents); cmd.Parameters.AddWithValue("IsContainer", IsContainer); cmd.Parameters.AddWithValue("IsRepeatingItem", IsRepeatingItem); cmd.Parameters.AddWithValue("CanExpand", CanExpand); cmd.Parameters.AddWithValue("StickToTop", StickToTop); cmd.Parameters.AddWithValue("StickToBottom", StickToBottom); cmd.Parameters.AddWithValue("ContinuedOver", ContinuedOver); cmd.Parameters.AddWithValue("ContinuedFrom", ContinuedFrom); cmd.Parameters.AddWithValue("NotContinuedOver", NotContinuedOver); cmd.Parameters.AddWithValue("NotContinuedFrom", NotContinuedFrom); cmd.Parameters.AddWithValue("KeepTogether", KeepTogether); cmd.Parameters.AddWithValue("MinOrphanWidowRows", MinOrphanWidowRows); int?ownerID = OwnerID <= 0 ? null : (int?)OwnerID; cmd.Parameters.AddWithValue("OwnerID", ownerID); int rv = cmd.ExecuteScalarInt32(); if (rv > 0) { TemplateID = rv; } if (TemplateID <= 0) { Err = "The template could not be saved."; } if (TemplateID > 0) { ChildTemplates.SaveChildenForTemplate(TemplateID, out Err, ConX); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } } return(TemplateID > 0); } catch (Exception ex) { Err = ex.Message; return(false); } finally { if (openConX) { ConX.Close(); } } }
public static Page GetNextNotice(Page CurrentPage, string ClientHash, ref bool ShowingNotices, ref int LastNoticeReadID) { if (!ShowingNotices || CurrentPage == null || CurrentPage.PageID <= 0) { ShowingNotices = false; return(Page.Load(Options.MainIndexPageNo, Options.MainIndexFrameNo)); } int nextPageNo = -1; int nextFrameNo = -1; int noticeID = -1; int noticeReadID = -1; ClientHash = (ClientHash ?? "").Trim(); using (var con = new MySqlConnection(DBOps.ConnectionString)) { con.Open(); string sql = @"SELECT p.PageID,p.PageNo,p.FrameNo,n.NoticeID,r.NoticeReadID FROM `page` p JOIN notice n ON n.PageID=p.PageID LEFT JOIN noticeread r ON r.NoticeID=n.NoticeID WHERE n.IsActive=1 AND (n.StartDate IS NULL OR n.StartDate<=CURRENT_TIMESTAMP()) AND (n.EndDate IS NULL OR n.EndDate>=CURRENT_TIMESTAMP()) AND (r.ClientHash IS NULL OR r.ClientHash='" + ClientHash + @"') AND (r.ReadDate IS NULL OR r.ReadDate<n.UpdatedDate OR r.NoticeReadID=" + LastNoticeReadID + @") ORDER BY PageNo,FrameNo;"; bool foundCurrent = CurrentPage.PageAndFrame == Options.StartPage; using (var cmd = new MySqlCommand(sql, con)) using (var rdr = cmd.ExecuteReader()) { while (rdr.Read()) { int id = rdr.GetInt32("PageID"); if (id == CurrentPage.PageID) { foundCurrent = true; continue; } if (foundCurrent) { nextPageNo = rdr.GetInt32("PageNo"); nextFrameNo = rdr.GetInt32("FrameNo"); noticeID = rdr.GetInt32("NoticeID"); noticeReadID = rdr.GetInt32Safe("NoticeReadID"); break; } } } if (noticeReadID > 0) { sql = @"UPDATE noticeread SET ReadDate=CURRENT_TIMESTAMP() WHERE NoticeReadID=" + noticeReadID; using (var cmd = new MySqlCommand(sql, con)) { cmd.ExecuteNonQuery(); } LastNoticeReadID = noticeReadID; } if (noticeID > 0) { sql = @"INSERT INTO noticeread (NoticeID,ClientHash,ReadDate) VALUES (" + noticeID + @",'" + ClientHash + @"',CURRENT_TIMESTAMP()); SELECT LAST_INSERT_ID();"; using (var cmd = new MySqlCommand(sql, con)) { LastNoticeReadID = cmd.ExecuteScalarInt32(); } } } if (nextPageNo > 0) { return(Page.Load(nextPageNo, nextFrameNo)); } ShowingNotices = false; return(Page.Load(Options.MainIndexPageNo, Options.MainIndexFrameNo)); }
public bool Update(int UpdatedBy, out string Err, MySqlConnection ConX = null) { Err = ""; bool openConX = ConX == null; if (openConX) { ConX = new MySqlConnection(DBOps.ConnectionString); ConX.Open(); } try { string sql = @"UPDATE page SET PageNo=@PageNo, FrameNo=@FrameNo, Title=@Title, BoxMode=@BoxMode, URL=@URL, Contents=@Contents, FromPageFrameNo=@FromPageFrameNo, ToPageFrameNo=@ToPageFrameNo, TeleSoftwareID=@TeleSoftwareID, OwnerID=@OwnerID, IsCarousel=@IsCarousel, CarouselWait=@CarouselWait, Updated=@Updated, UpdatedBy=@UpdatedBy WHERE PageID=@PageID; SELECT ROW_COUNT();"; var cmd = new MySqlCommand(sql, ConX); cmd.Parameters.AddWithValue("PageID", PageID); cmd.Parameters.AddWithValue("PageNo", PageNo); cmd.Parameters.AddWithValue("FrameNo", FrameNo); cmd.Parameters.AddWithValue("Title", (Title ?? "").Trim()); cmd.Parameters.AddWithValue("BoxMode", BoxMode); cmd.Parameters.AddWithValue("URL", (URL ?? "").Trim()); cmd.Parameters.AddWithValue("Contents", Contents); decimal fromPageFrameNo = PageNo + (Convert.ToDecimal(FrameNo) / 100m); cmd.Parameters.AddWithValue("FromPageFrameNo", fromPageFrameNo); decimal toPageFrameNo = ToPageNo + (Convert.ToDecimal(ToFrameNo) / 100m); cmd.Parameters.AddWithValue("ToPageFrameNo", toPageFrameNo); int?tsid = TeleSoftwareID != null && TeleSoftwareID > 0 ? TeleSoftwareID : null; cmd.Parameters.AddWithValue("TeleSoftwareID", tsid); int?ownerID = OwnerID <= 0 ? null : (int?)OwnerID; cmd.Parameters.AddWithValue("OwnerID", ownerID); cmd.Parameters.AddWithValue("IsCarousel", IsCarousel); cmd.Parameters.AddWithValue("CarouselWait", CarouselWait); cmd.Parameters.AddWithValue("Updated", DateTime.Now); int?updBy = UpdatedBy <= 0 ? null : (int?)UpdatedBy; cmd.Parameters.AddWithValue("UpdatedBy", updBy); int rv = cmd.ExecuteScalarInt32(); if (rv <= 0) { Err = "The page could not be saved."; } if (PageID > 0) { Templates.SaveForPage(PageID, out Err, ConX); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } Routing.SaveForPage(PageID, out Err, ConX); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } Zones.SaveForPage(PageID, out Err, ConX); if (!string.IsNullOrWhiteSpace(Err)) { return(false); } } return(rv > 0); } catch (Exception ex) { if (ex.Message.ToLower().Contains("duplicate entry")) { Err = "Page " + PageNo + Frame + " already exists."; } else { Err = ex.Message; } return(false); } finally { if (openConX) { ConX.Close(); } } }