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