public static List <Advertisement> GetAds(int schoolid, string and, int page, string order)
{
MySqlCommand command = null;
MySqlDataReader reader = null;
List <Advertisement> ads = new List <Advertisement>();
try
{
connection.Open();
string query = "SELECT listings.id, school, category, subcategory, user, title, description, views, created, boosted, anonymous, group_concat(if (images.deleted = 0, images.image, null) separator ',') as `images` FROM listings LEFT JOIN images on listings.id = images.listing WHERE school = @SchoolId " + and + " AND expiration > NOW() AND listings.deleted = 0 group by id ORDER BY " + order + "created DESC LIMIT @Page, @PerPage";
command = new MySqlCommand(query, connection);
command.Parameters.AddWithValue("@SchoolId", schoolid);
command.Parameters.AddWithValue("@Page", (page - 1) * Constants.ListingsPerPage);
command.Parameters.AddWithValue("@PerPage", Constants.ListingsPerPage);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int id = reader.GetInt16("id");
int category = reader.GetInt16("category");
int subcategory = reader.GetInt16("subcategory");
int user = reader.GetInt16("user");
string title = GetStringData(reader, "title");
string images = GetStringData(reader, "images");
string description = GetStringData(reader, "description");
int views = reader.GetInt16("views");
DateTime created = reader.GetDateTime("created");
bool boosted = reader.GetInt16("boosted") == 1;
bool anonymous = reader.GetInt16("anonymous") == 1;
Advertisement ad = new Advertisement();
ad.Id = id;
ad.School = schoolid;
ad.Category = category;
ad.Subcategory = subcategory;
ad.User = user;
ad.Title = title;
ad.Description = description;
ad.AddImages(images);
ad.Views = views;
ad.Anonymous = anonymous;
ad.Created = created;
ad.Boosted = boosted;
ads.Add(ad);
}
}
command.Dispose();
connection.Close();
return(ads);
}
catch (Exception exception)
{
Utility.Log(exception);
}
finally
{
if (reader != null)
{
reader.Dispose();
reader.Close();
}
if (command != null)
{
command.Dispose();
}
if (connection != null && connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(ads);
}
public static Advertisement GetAd(int adid)
{
MySqlCommand command = null;
MySqlDataReader reader = null;
try
{
connection.Open();
string query = "SELECT school, category, subcategory, user, title, description, views, created, boosted, anonymous, expiration, listings.deleted, group_concat(images.image separator ',') as 'images' FROM listings LEFT JOIN images on listings.id = images.listing WHERE listings.id = @Id AND images.deleted = 0";
command = new MySqlCommand(query, connection);
command.Parameters.AddWithValue("@Id", adid);
reader = command.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
int school = reader.GetInt16("school");
int category = reader.GetInt16("category");
int subcategory = reader.GetInt16("subcategory");
int user = reader.GetInt16("user");
string title = GetStringData(reader, "title");
string images = GetStringData(reader, "images");
string description = GetStringData(reader, "description");
int views = reader.GetInt16("views");
bool boosted = reader.GetInt16("boosted") == 1;
bool deleted = reader.GetInt16("deleted") == 1;
bool anonymous = reader.GetInt16("anonymous") == 1;
DateTime expiration = DateTime.Parse(GetStringData(reader, "expiration"));
DateTime created = reader.GetDateTime("created");
Advertisement ad = new Advertisement();
ad.Id = adid;
ad.School = school;
ad.Category = category;
ad.Subcategory = subcategory;
ad.User = user;
ad.Title = title;
ad.Description = description;
ad.AddImages(images);
ad.Views = views;
ad.Created = created;
ad.Anonymous = anonymous;
ad.Boosted = boosted;
ad.Expiration = expiration;
ad.Deleted = deleted;
reader.Dispose();
reader.Close();
command.Dispose();
connection.Close();
return(ad);
}
}
}
catch (Exception exception)
{
Utility.Log(exception);
}
finally
{
if (reader != null)
{
reader.Dispose();
reader.Close();
}
if (command != null)
{
command.Dispose();
}
if (connection != null && connection.State == ConnectionState.Open)
{
connection.Close();
}
}
return(null);
}
