public static Roles Load(MySqlConnection ConX = null)
{
var list = new Roles();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT * FROM aspnetroles ORDER BY Name;";
var cmd = new MySqlCommand(sql, ConX);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Role();
item.ID = rdr.GetStringNullable("Id");
item.Name = rdr.GetStringNullable("Name");
list.Add(item);
}
}
if (openConX)
{
ConX.Close();
}
return(list);
}
public static bool DeleteForUser(string UserID, out string Err, MySqlConnection ConX = null)
{
Err = "";
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string sql = @"DELETE FROM aspnetuserroles WHERE UserId=@UserId;";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("UserId", (UserID ?? "").Trim());
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
Err = ex.Message;
return(false);
}
finally
{
if (openConX)
{
ConX.Close();
}
}
}
public static void Set(string UserID, string Key, object Value, MySqlConnection ConX = null)
{
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"UPDATE userpref
SET `Value`=@Value
WHERE UserID=@UserID
AND `Key`=@Key;
INSERT INTO userpref (UserID,`Key`,`Value`)
VALUES (@UserID,@Key,@Value);";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("UserID", (UserID ?? "").Trim());
cmd.Parameters.AddWithValue("Key", (Key ?? "").Trim());
cmd.Parameters.AddWithValue("Value", (Value ?? "").ToString());
try
{
cmd.ExecuteNonQuery();
}
catch { }
if (openConX)
{
ConX.Close();
}
}
public bool Save(MySqlConnection ConX = null)
{
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string sql = @"INSERT INTO feed
(FeedURL,`XML`,LastUpdated)
VALUES (@FeedURL,@XML,@LastUpdated)
ON DUPLICATE KEY UPDATE
FeedURL=@FeedURL,XML=@XML,LastUpdated=@LastUpdated;";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("FeedURL", URL);
cmd.Parameters.AddWithValue("XML", XML);
cmd.Parameters.AddWithValue("LastUpdated", DateTime.Now);
return(cmd.ExecuteNonQuery() >= 1);
}
finally
{
if (openConX)
{
ConX.Close();
}
}
}
public bool Save()
{
MySqlConnection ConX = null;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string geo = "NULL";
if (lat != null && lon != null)
{
geo = "POINT(" + lat + "," + lon + ")";
}
string sql = @"UPDATE geo
SET GEO=" + geo + @",
IPAddress=NULL
WHERE ClientHash=@ClientHash;";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("ClientHash", ClientHash);
cmd.ExecuteNonQuery();
if (openConX)
{
ConX.Close();
}
return(true);
}
public static Zones Load(MySqlConnection ConX = null)
{
var list = new Zones();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT * FROM zone ORDER BY Description,ZoneID;";
var cmd = new MySqlCommand(sql, ConX);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Zone();
item.ID = rdr.GetInt32("ZoneID");
item.Description = rdr.GetStringNullable("Description");
list.Add(item);
}
}
if (openConX)
{
ConX.Close();
}
return(list);
}
public static Zone Load(int ZoneID, MySqlConnection ConX = null)
{
var item = new Zone();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = "SELECT * FROM zone WHERE ZoneID=" + ZoneID;
var cmd = new MySqlCommand(sql, ConX);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
item.Read(rdr);
break;
}
}
if (openConX)
{
ConX.Close();
}
return(item);
}
public bool DeleteForPage(int PageID, out string Err, MySqlConnection ConX = null)
{
Err = "";
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string sql = @"DELETE FROM pagezone WHERE PageID=" + PageID;
var cmd = new MySqlCommand(sql, ConX);
cmd.ExecuteNonQuery();
return(true);
}
catch (Exception ex)
{
Err = ex.Message;
return(false);
}
finally
{
if (openConX)
{
ConX.Close();
}
}
}
public bool SaveChildForTemplate(int ParentTemplateID, MySqlConnection ConX = null)
{
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"INSERT INTO templatetree (ParentTemplateID,ChildTemplateID,Seq)
VALUES(@ParentTemplateID,@ChildTemplateID,@Seq);";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("ParentTemplateID", ParentTemplateID);
cmd.Parameters.AddWithValue("ChildTemplateID", TemplateID);
cmd.Parameters.AddWithValue("Seq", Sequence);
cmd.ExecuteNonQuery();
}
if (openConX)
{
ConX.Close();
}
return(true);
}
public static string Connect(IPEndPoint EndPoint, out DateTime LastSeen)
{
LastSeen = DateTime.MinValue;
if (EndPoint == null || EndPoint.Address == null)
{
return("");
}
MySqlConnection ConX = null;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
uint ip = IPEndPointExtensions.ToUint32(EndPoint);
string hash = IPEndPointExtensions.CalculateHash(EndPoint);
string sql = @"INSERT IGNORE INTO geo (ClientHash,IPAddress) VALUES (@ClientHash,@IPAddress);";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("ClientHash", hash);
cmd.Parameters.AddWithValue("IPAddress", ip);
cmd.ExecuteNonQuery();
cmd.CommandText = @"UPDATE geo gg
JOIN geo g ON gg.ClientHash=g.ClientHash
SET gg.IPAddress=@IPAddress
WHERE g.IPAddress IS NULL
AND g.Geo IS NULL
AND g.ClientHash=@ClientHash;";
cmd.ExecuteNonQuery();
}
sql = @"SELECT MAX(`Timestamp`) AS ts
FROM stats
WHERE ClientHash=@ClientHash;";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("ClientHash", hash);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
LastSeen = rdr.GetDateTimeSafe("ts");
break;
}
}
}
if (openConX)
{
ConX.Close();
}
return(hash);
}
public bool LoadChildTemplates(ref HashSet <int> IDs, Template ParentTemplate, MySqlConnection ConX = null, bool StubsOnly = false)
{
bool rv = true;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string fields;
if (StubsOnly)
{
fields = "t.TemplateID,t.Description,t.OwnerID";
}
else
{
fields = "t.*";
}
string sql = @"SELECT " + fields + @"
FROM template t
JOIN templatetree tt ON t.TemplateID=tt.ChildTemplateID
WHERE tt.ParentTemplateID=" + TemplateID + @"
ORDER BY tt.Seq,tt.ChildTemplateID;";
using (var cmd = new MySqlCommand(sql, ConX))
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Template();
item.ParentTemplate = ParentTemplate;
item.Read(rdr, StubsOnly);
if (!IDs.Contains(item.TemplateID))
{
ChildTemplates.Add(item);
IDs.Add(item.TemplateID);
}
}
}
foreach (var child in ChildTemplates)
{
child.LoadChildTemplates(ref IDs, child, ConX);
}
}
finally
{
if (openConX)
{
ConX.Close();
}
}
return(rv);
}
public static Zones Search(string Value, bool AllowNone, MySqlConnection ConX = null)
{
var list = new Zones();
if (AllowNone)
{
list.Add(new Zone()
{
ID = -1, Description = "[None]"
});
}
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
Value = Value ?? "";
string filter = "1=0";
if (Value.Length > 0)
{
filter = "Description LIKE @Description";
Value = "%" + Value + "%";
}
string sql = @"SELECT *
FROM zone
WHERE " + filter + @"
ORDER BY Description,ZoneID;";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("@Description", Value);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Zone();
item.ID = rdr.GetInt32("ZoneID");
item.Description = rdr.GetStringNullable("Description");
list.Add(item);
}
}
}
if (openConX)
{
ConX.Close();
}
return(list);
}
public static List <Geo> Load()
{
var list = new List <Geo>();
MySqlConnection ConX = null;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string sql = @"SELECT ClientHash,IPAddress
FROM geo
WHERE IPAddress IS NOT NULL
AND Geo IS NULL;";
using (var cmd = new MySqlCommand(sql, ConX))
{
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Geo();
item.ClientHash = (rdr.GetStringNullable("ClientHash") ?? "").Trim();
uint ip = rdr.GetUint32Safe("IPAddress");
byte a = (byte)((ip >> 24) & 255);
byte b = (byte)((ip >> 16) & 255);
byte c = (byte)((ip >> 8) & 255);
byte d = (byte)(ip & 255);
string addr = a.ToString() + "." + b.ToString() + "." + c.ToString() + "." + d.ToString();
try
{
item.IPAddress = IPAddress.Parse(addr);
list.Add(item);
}
catch { }
}
}
}
}
catch (Exception /*ex*/)
{
}
finally
{
if (openConX)
{
ConX.Close();
}
}
return(list);
}
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 static Feed Load(string URL, string Expression, MySqlConnection ConX = null)
{
var feed = new Feed();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string sql = "SELECT * FROM feed WHERE FeedURL=@FeedURL;";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("FeedURL", (URL ?? "").Trim().ToLower());
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
feed.Read(URL, rdr);
break;
}
}
}
if (!string.IsNullOrWhiteSpace(URL) && feed.LastUpdated < DateTime.Now.AddMinutes(-Options.UpdateFeedMins))
{
var doc = new XmlDocument();
doc.Load(URL);
feed.URL = (URL ?? "").Trim().ToLower();
feed.XML = doc.InnerXml;
feed.LastUpdated = DateTime.Now;
feed.Save();
feed.Items.Load(doc, Expression);
}
else
{
feed.Items.Load(feed.XML, Expression);
}
}
catch (Exception /*ex*/)
{
}
finally
{
if (openConX)
{
ConX.Close();
}
}
return(feed);
}
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 static Notices Load(MySqlConnection ConX = null)
{
var list = new Notices();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT n.*,p.PageNo,p.FrameNo,p.FromPageFrameNo,p.Title,p.Updated
FROM notice n
JOIN `page` p ON n.PageID=p.PageID
ORDER BY p.FromPageFrameNo;";
var cmd = new MySqlCommand(sql, ConX);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Notice();
item.ID = rdr.GetInt32("NoticeID");
item.PageID = rdr.GetInt32("PageID");
item.StartDate = rdr.GetDateTimeNullable("StartDate");
item.EndDate = rdr.GetDateTimeNullable("EndDate");
item.IsActive = rdr.GetBooleanSafe("IsActive");
item.Updated = rdr.GetDateTimeNullable("UpdatedDate");
DateTime?pageUpdated = rdr.GetDateTimeNullable("Updated");
if (pageUpdated.HasValue && item.Updated.HasValue && pageUpdated > item.Updated)
{
item.Updated = pageUpdated;
}
item.PageTitle = rdr.GetStringNullable("Title");
item.PageFrameNo = rdr.GetDouble("FromPageFrameNo");
int pageNo = rdr.GetInt32("PageNo");
int frame = rdr.GetInt32("FrameNo");
item.PageFrameNoStr = pageNo.ToString() + ((char)Convert.ToByte(((byte)"a"[0]) + frame)).ToString();
list.Add(item);
}
}
if (openConX)
{
ConX.Close();
}
return(list);
}
public static bool SaveForUser(string UserID, List <string> Roles, out string Err, MySqlConnection ConX = null)
{
Err = "";
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
var roles = NXtelData.Roles.Load(ConX);
var rv = DeleteForUser(UserID, out Err, ConX);
if (!string.IsNullOrWhiteSpace(Err))
{
return(false);
}
if (Roles == null || Roles.Count == 0)
{
return(true);
}
foreach (var roleName in Roles)
{
var role = roles.FirstOrDefault(r => r.Name == roleName);
if (role != null)
{
role.SaveForUser(UserID, ConX);
}
}
return(true);
}
catch (Exception ex)
{
Err = ex.Message;
return(false);
}
finally
{
if (openConX)
{
ConX.Close();
}
}
}
public static Templates LoadForPage(int PageID, MySqlConnection ConX = null)
{
var list = new Templates();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
var ids = new HashSet <int>();
string sql = @"SELECT t.*
FROM pagetemplate pt
JOIN template t ON pt.TemplateID=t.TemplateID
WHERE pt.PageID=" + PageID + @"
ORDER BY pt.Seq,t.TemplateID;";
var cmd = new MySqlCommand(sql, ConX);
using (var rdr = cmd.ExecuteReader())
{
int seq = 10;
while (rdr.Read())
{
var item = new Template();
item.Read(rdr);
if (!ids.Contains(item.TemplateID))
{
item.Sequence = seq;
seq += 10;
list.Add(item);
ids.Add(item.TemplateID);
}
}
}
foreach (var t in list)
{
t.LoadChildTemplates(ref ids, t, ConX);
}
if (openConX)
{
ConX.Close();
}
return(list);
}
public static Routes LoadForPage(int PageID, MySqlConnection ConX = null)
{
var sorted = new List <Route>();
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT r.*,p.PageNo AS CurrentPageNo,p.FrameNo AS CurrentFrameNo,
dp.PageID AS DirectPageID,dp.PageNo AS DirectPageNo,dp.FrameNo AS DirectFrameNo,
np.PageID AS NextPageID,np.PageNo AS NextPagePageNo,np.FrameNo AS NextPageFrameNo,
nf.PageID AS NextFrameID,nf.PageNo AS NextFramePageNo,nf.FrameNo AS NextFrameFrameNo
FROM route r
JOIN `page` p on p.PageID=r.PageID
LEFT JOIN `page` dp ON dp.PageID=(SELECT PageID FROM `page` pp WHERE pp.PageNo=r.NextPageNo AND pp.FrameNo=r.NextFrameNo LIMIT 1)
LEFT JOIN `page` np ON np.PageID=(SELECT PageID FROM `page` pp WHERE pp.PageNo=p.PageNo+1 AND pp.FrameNo=0 LIMIT 1)
LEFT JOIN `page` nf ON nf.PageID=(SELECT PageID FROM `page` pp WHERE pp.PageNo=p.PageNo AND pp.FrameNo=p.FrameNo+1 LIMIT 1)
WHERE r.PageID=" + PageID + @"
ORDER BY r.KeyCode;";
var cmd = new MySqlCommand(sql, ConX);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Route();
item.Read(rdr);
sorted.Add(item);
}
}
if (openConX)
{
ConX.Close();
}
sorted = sorted.OrderBy(r => r.Sort).ToList();
var list = new Routes();
list.AddRange(sorted);
return(list);
}
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 int GetIDFromDescription(MySqlConnection ConX = null, bool ResetIfNotFound = true, bool ResetOwnerIfNotFound = true)
{
int rv = -1;
bool found = false;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT TeleSoftwareID FROM telesoftware
WHERE telesoftware.`Key`=@Key
ORDER BY TeleSoftwareID LIMIT 1;";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("Key", (Key ?? "").Trim());
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
rv = rdr.GetInt32("TeleSoftwareID");
TeleSoftwareID = rv;
found = true;
break;
}
}
}
if (ResetIfNotFound && !found)
{
TeleSoftwareID = -1;
}
if (ResetOwnerIfNotFound && !found)
{
OwnerID = -1;
}
if (openConX)
{
ConX.Close();
}
return(rv);
}
public bool Save(string UserID, MySqlConnection ConX = null)
{
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql;
if (this.UserPermissionID <= 0)
{
sql = @"INSERT INTO userpermission (UserID,PermissionType,`From`,`To`)
VALUES(@UserID,@PermissionType,@From,@To);";
}
else
{
sql = @"UPDATE userpermission
SET UserID=@UserID,
PermissionType=@PermissionType,
`From`=@From,
`To`=@To
WHERE UserPermissionID=@UserPermissionID;";
}
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("UserID", (UserID ?? "").Trim());
cmd.Parameters.AddWithValue("PermissionType", (int)Type);
cmd.Parameters.AddWithValue("From", From);
int?to = To > 0 ? To : (int?)null;
cmd.Parameters.AddWithValue("To", to);
cmd.Parameters.AddWithValue("UserPermissionID", UserPermissionID);
cmd.ExecuteNonQuery();
if (openConX)
{
ConX.Close();
}
return(true);
}
public int GetIDFromDescription(MySqlConnection ConX = null, bool ResetIfNotFound = true)
{
int rv = -1;
bool found = false;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT PageID FROM `page`
WHERE Title=@Title
ORDER BY PageID LIMIT 1;";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("Title", (Title ?? "").Trim());
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
rv = rdr.GetInt32("PageID");
PageID = rv;
found = true;
break;
}
}
}
OwnerID = -1;
if (ResetIfNotFound && !found)
{
PageID = -1;
}
if (openConX)
{
ConX.Close();
}
return(rv);
}
public static string Get(string UserID, string Key, string DefaultValue = "", MySqlConnection ConX = null)
{
string val = "";
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string sql = @"SELECT `Value`
FROM userpref
WHERE UserID=@UserID
AND `Key`=@Key
LIMIT 1;";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("UserID", (UserID ?? "").Trim());
cmd.Parameters.AddWithValue("Key", (Key ?? "").Trim());
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
val = rdr.GetStringNullable("Value");
break;
}
}
if (string.IsNullOrEmpty(val) && !string.IsNullOrEmpty(DefaultValue))
{
val = DefaultValue;
}
if (openConX)
{
ConX.Close();
}
return(val);
}
public bool SaveForPage(int PageID, MySqlConnection ConX = null)
{
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
if (GoNextPage || GoNextFrame)
{
// Remove page numbers if they were greyed out in the UI
NextPageNo = null;
NextFrameNo = null;
}
else if (NextPageNo != null && NextFrameNo == null)
{
// Default frame number to 'a' if it was missing in the UI
NextFrameNo = 0;
}
string sql = @"INSERT INTO route (PageID,KeyCode,NextPageNo,NextFrameNo,GoNextPage,GoNextFrame)
VALUES(@PageID,@KeyCode,@NextPageNo,@NextFrameNo,@GoNextPage,@GoNextFrame);";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("PageID", PageID);
cmd.Parameters.AddWithValue("KeyCode", KeyCode);
cmd.Parameters.AddWithValue("NextPageNo", NextPageNo);
cmd.Parameters.AddWithValue("NextFrameNo", NextFrameNo);
cmd.Parameters.AddWithValue("GoNextPage", GoNextPage);
cmd.Parameters.AddWithValue("GoNextFrame", GoNextFrame);
cmd.ExecuteNonQuery();
if (openConX)
{
ConX.Close();
}
return(true);
}
public bool SaveChildenForTemplate(int TemplateID, out string Err, MySqlConnection ConX = null)
{
Err = "";
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
var rv = DeleteChildenForTemplate(TemplateID, out Err, ConX);
if (!string.IsNullOrWhiteSpace(Err))
{
return(false);
}
int seq = 10;
foreach (var item in this)
{
item.Sequence = seq;
item.SaveChildForTemplate(TemplateID, ConX);
seq += 10;
}
return(true);
}
catch (Exception ex)
{
Err = ex.Message;
return(false);
}
finally
{
if (openConX)
{
ConX.Close();
}
}
}
public static bool Update(DateTime Timestamp, string IPAddress, int Page, int Frame)
{
if (Timestamp == DateTime.MinValue || string.IsNullOrWhiteSpace(IPAddress) || (Page < 0 && Frame < 0))
{
return(false);
}
uint ip = IPEndPointExtensions.ToUint32(IPAddress);
if (ip == 0)
{
return(false);
}
MySqlConnection ConX = null;
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
string hash = IPEndPointExtensions.CalculateHash(IPAddress);
string sql = @"INSERT INTO stats (ClientHash,Timestamp,PageNo,FrameNo)
VALUES (@ClientHash,@Timestamp,@PageNo,@FrameNo);";
var cmd = new MySqlCommand(sql, ConX);
cmd.Parameters.AddWithValue("ClientHash", hash);
cmd.Parameters.AddWithValue("Timestamp", Timestamp);
cmd.Parameters.AddWithValue("PageNo", Page);
cmd.Parameters.AddWithValue("FrameNo", Frame);
cmd.ExecuteNonQuery();
if (openConX)
{
ConX.Close();
}
return(true);
}
public bool Save(string UserID, out string Err, MySqlConnection ConX = null)
{
Err = "";
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
try
{
string ids = string.Join(",", this.Where(r => r.UserPermissionID > 0).Select(r => r.UserPermissionID));
var rv = Delete(UserID, ids, out Err, ConX);
if (!string.IsNullOrWhiteSpace(Err))
{
return(false);
}
foreach (var pr in this)
{
pr.Save(UserID, ConX);
}
return(true);
}
catch (Exception ex)
{
Err = ex.Message;
return(false);
}
finally
{
if (openConX)
{
ConX.Close();
}
}
}