/** 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 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);
}
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 List <AbstractCompetition> GetCompetitions()
{
IEnumerable <string> competitions = ImagePaths.GetCompetitionZipFilesList();
List <AbstractCompetition> competitionList = new List <AbstractCompetition>();
foreach (var item in competitions)
{
var today = DateTime.Parse(DateTime.Now.ToString("yyyy-MM-dd"));
var competitionFileName = ImagePaths.RemoveSourcePathStart(item);
if (competitionFileName.ToLower().EndsWith(".iris"))
{
var competitionDateString = competitionFileName.Substring(0, 10);
var competitionDate = DateTime.Parse(competitionDateString);
var competitionName = competitionFileName.Substring(0, competitionFileName.Length - 5);
if (competitionDate >= today)
{
CompetitionHelper.ExtractFiles(competitionName);
CompetitionHelper.CreateDatabase(competitionName);
AbstractCompetition competition = CompetitionFactory.Load(competitionName);
competitionList.Add(competition);
}
}
}
return(competitionList);
}
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);
}
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();
}
}
/** Extract from zip file to tmp directory */
private static void ExtractFiles(string competitionName)
{
string zipFilePath = ImagePaths.GetZipFile(competitionName);
string destination = ImagePaths.GetExtractDirectory(competitionName);
if (Directory.Exists(destination) == false)
{
ZipFile.ExtractToDirectory(zipFilePath, destination);
}
}
internal static AbstractCompetition Load(string competitionFileName)
{
string competitionDirectory = ImagePaths.GetExtractDirectory(competitionFileName);
AbstractCompetition competition = null;
try
{
// Load image order details
XmlDocument orderingDocument = new XmlDocument();
orderingDocument.Load(competitionDirectory + "/" + CONTROL_FILENAME);
XmlNode rootNode = orderingDocument.FirstChild;
string competitionStyle = rootNode["Style"].InnerText;
string clubName = rootNode["Club"].InnerText;
string trophyName = rootNode["Trophy"].InnerText;
string competitionKey = rootNode["CompKey"].InnerText;
string resultsKey = rootNode["ResultsKey"].InnerText;
bool scoring = rootNode["Scoring"].InnerText.ToLower() == "true";
switch (competitionStyle.ToLower())
{
case "panel":
competition = LoadPanelCompetition(competitionFileName, competitionDirectory, rootNode, clubName, trophyName, competitionKey, resultsKey);
break;
default:
competition = LoadSingleImageCompetition(competitionFileName, competitionDirectory, rootNode, clubName, trophyName, scoring, competitionKey, resultsKey);
break;
}
}
catch (Exception e)
{
string clubName = "Competition zip file broken";
string trophyName = e.Message;
competition = new Competition(competitionFileName, competitionDirectory, clubName, trophyName, null, null);
}
return(competition);
}
internal static string GetDatabaseFile(string competitionName)
{
return(ImagePaths.GetDatabaseDirectory(competitionName) + "/" + competitionName + ".sqlite");
}
