public static Pages LoadStubs(int ZoneID = -1, string ZoneIDs = null, int MostRecent = -1)
{
var list = new Pages();
var pids = new List <int>();
using (var con = new MySqlConnection(DBOps.ConnectionString))
{
con.Open();
string sql;
if (ZoneIDs == null)
{
string filter = "";
string limit = "";
string order = " ORDER BY PageNo,FrameNo";
if (ZoneID > 0)
{
filter = "WHERE PageID IN (SELECT PageID FROM pagezone pz WHERE pz.ZoneID=" + ZoneID + ") ";
}
else if (ZoneID == -2)
{
filter = "WHERE PageID NOT IN (SELECT PageID FROM pagezone pz) ";
}
else if (MostRecent > 0)
{
filter = "WHERE Updated IS NOT NULL ";
limit = " LIMIT " + MostRecent;
order = " ORDER BY Updated DESC";
}
sql = @"SELECT PageID,PageNo,FrameNo,Title,ToPageFrameNo,OwnerID,Updated,UpdatedBy
FROM page " + filter + order + limit;
}
else
{
string filter = ZoneIDs == "" ? "-1" : ZoneIDs;
sql = @"SELECT p.PageID,PageNo,FrameNo,Title,ToPageFrameNo,OwnerID
FROM `page` p
JOIN pagezone pz ON p.PageID=pz.PageID
JOIN zone z ON pz.ZoneID=z.ZoneID
WHERE z.ZoneID IN(" + filter + @")
ORDER BY PageNo,FrameNo;";
}
var cmd = new MySqlCommand(sql, con);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Page();
item.Read(rdr, true);
list.Add(item);
pids.Add(item.PageID);
}
}
if (pids.Count > 0)
{
sql = @"SELECT PageID,ZoneID
FROM pagezone
WHERE PageID IN(" + string.Join(",", pids) + @")
ORDER BY PageID,ZoneID;";
cmd.CommandText = sql;
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
int pid = rdr.GetInt32("PageID");
int zid = rdr.GetInt32("ZoneID");
var page = list.FirstOrDefault(p => p.PageID == pid);
if (page != null)
{
page.ZoneIDs += (string.IsNullOrWhiteSpace(page.ZoneIDs) ? "" : ",") + zid;
}
}
}
}
}
return(list);
}
public static Pages Search(string Value, bool AllowNone, MySqlConnection ConX = null)
{
var list = new Pages();
if (AllowNone)
{
list.Add(new Page()
{
PageID = -1, Title = "[None]"
});
}
bool openConX = ConX == null;
if (openConX)
{
ConX = new MySqlConnection(DBOps.ConnectionString);
ConX.Open();
}
int pageNo = -1;
int frameNo = -1;
Value = Value ?? "";
string filter = "1=0";
if (Value.Length > 0)
{
string filter2 = "";
var m = frameRegex.Match(Value);
if (m.Success)
{
int.TryParse(m.Groups["PageNo"].Value, out pageNo);
string frame = (m.Groups["Frame"].Value ?? "").Trim().ToLower();
if (frame.Length == 1)
{
frameNo = Convert.ToByte(frame[0]) - 'a';
}
if (pageNo > 0 && frameNo >= 0 && frameNo <= 25)
{
filter2 = " OR (PageNo=@PageNo AND FrameNo=@FrameNo)";
}
}
filter = "((Title LIKE @Title)" + filter2 + ")";
Value = "%" + Value + "%";
}
string sql = @"SELECT PageID,Title,PageNo,FrameNo
FROM `page`
WHERE " + filter + @"
ORDER BY Title,PageID;";
using (var cmd = new MySqlCommand(sql, ConX))
{
cmd.Parameters.AddWithValue("Title", Value);
cmd.Parameters.AddWithValue("PageNo", pageNo);
cmd.Parameters.AddWithValue("FrameNo", frameNo);
using (var rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
var item = new Page();
item.PageID = rdr.GetInt32("PageID");
item.Title = rdr.GetStringNullable("Title");
item.PageNo = rdr.GetInt32("PageNo");
item.FrameNo = rdr.GetInt32("FrameNo");
list.Add(item);
}
}
}
if (openConX)
{
ConX.Close();
}
return(list);
}