/** Return images that have been given awards */
internal override List <CompetitionImage> GetAwardedImages()
{
List <CompetitionImage> awardedImages = new List <CompetitionImage>();
string databaseFilePath = ImagePaths.GetDatabaseFile(this.GetName());
SQLiteConnection dbConnection = new SQLiteConnection("DataSource=" + databaseFilePath + ";Version=3;");
dbConnection.Open();
string winners_sql = "SELECT name, position FROM winners";
SQLiteCommand winners_cmd = new SQLiteCommand(winners_sql, dbConnection);
SQLiteDataReader winners_reader = winners_cmd.ExecuteReader();
while (winners_reader.Read())
{
var imageName = winners_reader.GetString(0);
var result = winners_reader.GetString(1);
foreach (CompetitionImage eachImage in this.images)
{
if (eachImage.GetFilePath() == imageName)
{
eachImage.SetResult(result);
awardedImages.Add(eachImage);
break;
}
}
}
dbConnection.Close();
return(awardedImages);
}
internal List <CompetitionImage> SelectScoredImages(string sql)
{
List <CompetitionImage> scoredImages = new List <CompetitionImage>();
string databaseFilePath = ImagePaths.GetDatabaseFile(this.GetName());
SQLiteConnection dbConnection = new SQLiteConnection("DataSource=" + databaseFilePath + ";Version=3;");
dbConnection.Open();
SQLiteCommand cmd = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
var timestamp = reader.GetString(0);
var imageName = reader.GetString(1);
var score = reader.GetInt16(2);
foreach (CompetitionImage eachImage in this.images)
{
if (eachImage.GetFilePath() == imageName)
{
eachImage.SetScore(score, timestamp);
scoredImages.Add(eachImage);
break;
}
}
}
}
return(scoredImages);
}
internal static int FetchAwardedImageCount(string competitionName)
{
int heldImagesCount = 0;
string databaseFilePath = ImagePaths.GetDatabaseFile(competitionName);
SQLiteConnection dbConnection = new SQLiteConnection("DataSource=" + databaseFilePath + ";Version=3;");
dbConnection.Open();
string sql = "SELECT count(*) FROM winners";
SQLiteCommand cmd = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
heldImagesCount = reader.GetInt16(0);
}
}
dbConnection.Close();
return(heldImagesCount);
}
internal static List <CompetitionImage> GetHeldPanels(PanelCompetition competition, string competitionName)
{
List <CompetitionImage> heldImages = new List <CompetitionImage>();
string databaseFilePath = ImagePaths.GetDatabaseFile(competitionName);
SQLiteConnection dbConnection = new SQLiteConnection("DataSource=" + databaseFilePath + ";Version=3;");
dbConnection.Open();
string sql = "SELECT name FROM held_images";
SQLiteCommand cmd = new SQLiteCommand(sql, dbConnection);
SQLiteDataReader reader = cmd.ExecuteReader();
if (reader.HasRows)
{
while (reader.Read())
{
CompetitionPanel competitionPanel = competition.GetImagePanelById(reader.GetString(0));
heldImages.Add(competitionPanel.GetPanelImage());
}
}
dbConnection.Close();
return(heldImages);
}
private static void CreateDatabase(string competitionName)
{
string databaseDirectory = ImagePaths.GetDatabaseDirectory(competitionName);
Directory.CreateDirectory(databaseDirectory);
string databaseFilePath = ImagePaths.GetDatabaseFile(competitionName);
if (File.Exists(databaseFilePath) == false)
{
//SQLiteConnection.CreateFile(databaseFilePath);
SQLiteConnection dbConnection = new SQLiteConnection("DataSource=" + databaseFilePath + ";Version=3;");
dbConnection.Open();
String createScoresTableCommandString = "CREATE TABLE IF NOT EXISTS scores (timestamp TEXT, name VARCHAR(255) NOT NULL, author VARCHAR(100), title VARCHAR(100), score NUMBER(2) NOT NULL)";
SQLiteCommand createScoresTableCommand = new SQLiteCommand(createScoresTableCommandString, dbConnection);
createScoresTableCommand.ExecuteNonQuery();
String createHeldImagesTableCommandString = "CREATE TABLE IF NOT EXISTS held_images (timestamp TEXT, name VARCHAR(255) NOT NULL)";
SQLiteCommand createHeldImagesTableCommand = new SQLiteCommand(createHeldImagesTableCommandString, dbConnection);
createHeldImagesTableCommand.ExecuteNonQuery();
String createWinnersTableCommandString = "CREATE TABLE IF NOT EXISTS winners (timestamp TEXT, name VARCHAR(255) NOT NULL, position VARCHAR(2) NOT NULL)";
SQLiteCommand createWinnersTableCommand = new SQLiteCommand(createWinnersTableCommandString, dbConnection);
createWinnersTableCommand.ExecuteNonQuery();
dbConnection.Close();
}
}