Added a "top 5 chosen songs this tournament" screen.

[ccbs] / bigscreen / fetch_top_chosen_songs_for_tournament.cpp

diff --git a/bigscreen/fetch_top_chosen_songs_for_tournament.cpp b/bigscreen/fetch_top_chosen_songs_for_tournament.cpp
new file mode 100644 (file)
index 0000000..d6f7078
--- /dev/null
+++ b/bigscreen/fetch_top_chosen_songs_for_tournament.cpp
@@ -0,0 +1,33 @@
+#include "fetch_top_chosen_songs_for_tournament.h"
+
+// small utility function so we can stick TopChosenSongs in a std::set
+bool TopChosen::operator< (const TopChosen &other) const
+{
+       if (title != other.title)
+               return (title < other.title);
+       return (frequency < other.frequency);
+}
+
+FetchTopChosenSongsForTournament::FetchTopChosenSongsForTournament(unsigned tournament, unsigned num, std::vector<TopChosen> *chosen)
+       : tournament(tournament), num(num), chosen(chosen) {}
+       
+void FetchTopChosenSongsForTournament::operator() (pqxx::transaction<> &t)
+{
+       chosen->erase(chosen->begin(), chosen->end());
+
+       // Again, this will break if a song has more than one short title
+       pqxx::result res( t.exec(
+               "SELECT title, shorttitle, COUNT(*) AS frequency FROM scores NATURAL JOIN songs NATURAL LEFT JOIN songshorttitles WHERE tournament="
+               + pqxx::to_string(tournament) + " AND chosen='t' AND song is NOT NULL GROUP BY title,shorttitle ORDER BY COUNT(*) DESC LIMIT "
+               + pqxx::to_string(num)) );
+       
+       for (pqxx::result::const_iterator i = res.begin(); i != res.end(); ++i) {
+               TopChosen ts;
+
+               ts.title = i["title"].as(ts.title);
+               ts.shorttitle = i["shorttitle"].as(ts.shorttitle);
+               ts.frequency = i["frequency"].as(ts.frequency);
+
+               chosen->push_back(ts);
+       }
+}