/** 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"); }