class ShinjiGUI(mainwindow.Ui_MainWindow): def dialogAttraction(self): Dialog_Attraction = QtWidgets.QDialog() dialog_ui = dialog_attraction_gui.DialogAttractionGUI(Dialog_Attraction) if Dialog_Attraction.exec(): self.attractionView() pass def dialogCompetitor(self): Dialog_Competitor = QtWidgets.QDialog() dialog_ui = dialog_competitor_gui.DialogCompetitorGUI(Dialog_Competitor) if Dialog_Competitor.exec(): self.competitorView() pass def dialogSong(self): Dialog_Song = QtWidgets.QDialog() dialog_ui = dialog_song_gui.DialogSongGUI(Dialog_Song) if Dialog_Song.exec(): self.songView() pass def songPreview(self): self.preview = video_preview.VideoPreview(fileName=self.tableView_Song_List.currentIndex().sibling( self.tableView_Song_List.currentIndex().row(), 2).data()) self.preview.resize(640, 480) self.preview.setWindowTitle("Song preview") self.preview.show() pass def attractionView(self): model = QSqlQueryModel() model.setQuery('SELECT id, name FROM t_attraction') model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Attraction_List.setModel(model) pass def competitorView(self): model = QSqlQueryModel() model.setQuery('SELECT id, name FROM t_competitor') model.setHeaderData(0, Qt.Horizontal, "ID") model.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Competitor_List.setModel(model) pass def songView(self): model = QSqlQueryModel() model.setQuery('SELECT t_song.id, t_song.name, t_song.file, t_song_type.name, t_anime.name, t_anime_serie.name ' 'FROM t_song ' 'INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id') model.setHeaderData(0, Qt.Horizontal, "Song ID") model.setHeaderData(1, Qt.Horizontal, "Song Name") model.setHeaderData(2, Qt.Horizontal, "Song File") model.setHeaderData(3, Qt.Horizontal, "Song Type") model.setHeaderData(4, Qt.Horizontal, "Anime Name") model.setHeaderData(5, Qt.Horizontal, "Anime Serie") self.tableView_Song_List.setModel(model) pass def songProperties(self): model = QSqlQueryModel() model.setQuery('SELECT t_artist.name FROM fk_song_artist ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE fk_song_artist.id_song = ' + str(self.tableView_Song_List.currentIndex().sibling( self.tableView_Song_List.currentIndex().row(), 0).data())) model.setHeaderData(0, Qt.Horizontal, "Artist Name") self.tableView_Song_Properties.setModel(model) pass def gameView(self): query = QSqlQuery() query.prepare('SELECT t_attraction.id, t_attraction.name, t_event.name FROM t_attraction ' 'INNER JOIN t_event ON t_attraction.id_event = t_event.id') query.setForwardOnly(1) query.exec_() self.comboBox_Game_Attraction.clear() while query.next(): self.comboBox_Game_Attraction.addItem(str(query.value(0)) + ' - ' + query.value(1) + ' - ' + query.value(2)) pass def reloadGameList(self, id_attraction): self.model_Game_List = GameListModel() self.model_Game_List.setTable("t_point") self.model_Game_List.setFilter("id_attraction = " + id_attraction) self.model_Game_List.setEditStrategy(QSqlTableModel.OnManualSubmit) self.model_Game_List.setJoinMode(QSqlRelationalTableModel.LeftJoin) self.model_Game_List.setRelation(2, QSqlRelation('t_competitor', 'id', 'name')) self.model_Game_List.setRelation(5, QSqlRelation('t_competitor', 'id', 'name')) self.model_Game_List.setHeaderData(0, Qt.Horizontal, "ID") self.model_Game_List.setHeaderData(1, Qt.Horizontal, "Point") self.model_Game_List.setHeaderData(2, Qt.Horizontal, "Competitor") self.model_Game_List.setHeaderData(3, Qt.Horizontal, "Song") self.model_Game_List.setHeaderData(5, Qt.Horizontal, "Competitor take") self.model_Game_List.select() self.tableView_Game_List.setModel(self.model_Game_List) self.tableView_Game_List.setItemDelegate(QSqlRelationalDelegate(self.tableView_Game_List)) self.tableView_Game_List.setColumnHidden(4, True) pass def reloadGameCompetitors(self, id_attraction): model_Game_Competitors = QSqlQueryModel() model_Game_Competitors.setQuery('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ON ' 't_point.id_competitor = t_competitor.id ' 'WHERE t_point.id_competitor_take = 1 ' 'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL ' 'SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ' 'ON t_point.id_competitor_take = t_competitor.id ' 'WHERE t_point.id_competitor_take != 1 ' 'AND t_point.id_attraction = ' + id_attraction + ') ' 'GROUP BY name ORDER BY point DESC') model_Game_Competitors.setHeaderData(0, Qt.Horizontal, "Points") model_Game_Competitors.setHeaderData(1, Qt.Horizontal, "Name") self.tableView_Game_Competitors.setModel(model_Game_Competitors) pass def setupGame(self): id_attraction = self.comboBox_Game_Attraction.currentText()[ :self.comboBox_Game_Attraction.currentText().find(" -")] self.reloadGameList(id_attraction) self.reloadGameCompetitors(id_attraction) pass def gameProperties(self): model = QSqlQueryModel() model.setQuery('SELECT t_song.name, t_song_type.name, t_anime.name, t_anime_serie.name, t_artist.name ' 'FROM t_song INNER JOIN t_song_type ON t_song.id_song_type = t_song_type.id ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'INNER JOIN t_anime_serie ON t_anime.id_anime_serie = t_anime_serie.id ' 'INNER JOIN fk_song_artist ON t_song.id = fk_song_artist.id_song ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE t_song.id = ' + str(self.tableView_Game_List.currentIndex().sibling( self.tableView_Game_List.currentIndex().row(), 3).data())) model.setHeaderData(0, Qt.Horizontal, "Song Name") model.setHeaderData(1, Qt.Horizontal, "Song Type") model.setHeaderData(2, Qt.Horizontal, "Anime Name") model.setHeaderData(3, Qt.Horizontal, "Anime Serie") model.setHeaderData(4, Qt.Horizontal, "Artist Name") self.tableView_Game_Properties.setModel(model) pass def gameCommit(self): if hasattr(self, 'model_Game_List'): self.model_Game_List.database().transaction() if self.model_Game_List.submitAll(): self.model_Game_List.database().commit() self.reloadGameCompetitors(self.comboBox_Game_Attraction.currentText()[ :self.comboBox_Game_Attraction.currentText().find(" -")]) self.statusbar.showMessage("Database committed.", 2500) else: self.model_Game_List.database().rollback() self.statusbar.showMessage("The database reported an error: %s" % self.model_Game_List.lastError().text(), 5000) else: self.statusbar.showMessage("Game model not present.", 2500) pass def gameRollback(self): if hasattr(self, 'model_Game_List'): self.model_Game_List.revertAll() else: self.statusbar.showMessage("Game model not present.", 2500) pass def gameNextSong(self): if hasattr(self, 'model_Game_List'): id_attraction = self.comboBox_Game_Attraction.currentText()[ :self.comboBox_Game_Attraction.currentText().find(" -")] query = QSqlQuery() query.prepare("SELECT id FROM t_song") query.setForwardOnly(1) query.exec_() songs_list = [] while query.next(): songs_list.append(int(query.value(0))) query = QSqlQuery() query.prepare("SELECT id_song FROM t_point WHERE id_attraction = " + id_attraction) query.setForwardOnly(1) query.exec_() songs_used = [] while query.next(): songs_used.append(int(query.value(0))) songs_not_used = [] for song in songs_list: if song not in songs_used: songs_not_used.append(song) if len(songs_not_used) == 0: self.statusbar.showMessage("No more songs available!", 5000) else: record = self.model_Game_List.record() record.setValue(3, random.choice(songs_not_used)) record.setValue(4, id_attraction) record.setValue(5, 1) self.model_Game_List.insertRecord(-1, record) else: self.statusbar.showMessage("Game model not present.", 2500) pass def connectDatabase(self): db = db_sqlite.DBSQLite() if db: self.statusbar.showMessage("Database connected!", 2500) self.attractionView() self.competitorView() self.songView() self.gameView() self.statusbar.showMessage("Views refreshed!", 2500) else: self.statusbar.showMessage("Database connection failed!", 5000) pass def childWindow(self): self.game_window.resize(640, 480) self.game_window.setWindowTitle("Shinji Game") self.game_window.show() pass def playPause(self): self.game_window.play() pass def playVisibility(self, visibility, labelString): if hasattr(self, 'model_Game_List'): song_id = str(self.tableView_Game_List.currentIndex().sibling( self.tableView_Game_List.currentIndex().row(), 3).data()) query = QSqlQuery() query.prepare("SELECT file FROM t_song WHERE id = " + song_id) query.setForwardOnly(1) query.exec_() song_file = '' while query.next(): song_file = str(query.value(0)) playback_rate = float(self.lineEdit_Game_PlaybackRate.text()) self.game_window.playV(song_file, playback_rate, visibility, labelString) pass def playVisible(self): if hasattr(self, 'model_Game_List'): query = QSqlQuery() song_id = str(self.tableView_Game_List.currentIndex().sibling( self.tableView_Game_List.currentIndex().row(), 3).data()) query.prepare('SELECT t_song.name, t_anime.name FROM t_song ' 'INNER JOIN t_anime ON t_song.id_anime = t_anime.id ' 'WHERE t_song.id = ' + song_id + ' LIMIT 1') query.setForwardOnly(1) query.exec_() labelString = '<h1>' while query.next(): labelString += 'Song: ' + str(query.value(0)) + ' | Anime: ' + str(query.value(1)) + ' | Artist: ' query.prepare('SELECT t_artist.name FROM fk_song_artist ' 'INNER JOIN t_artist ON fk_song_artist.id_artist = t_artist.id ' 'WHERE fk_song_artist.id_song = ' + song_id) query.setForwardOnly(1) query.exec_() while query.next(): labelString += str(query.value(0)) + ', ' self.playVisibility(True, labelString) pass def playInvisible(self): if hasattr(self, 'model_Game_List'): labelString = self.parseGamePoints() self.playVisibility(False, labelString) pass def parseGamePoints(self): id_attraction = self.comboBox_Game_Attraction.currentText()[ :self.comboBox_Game_Attraction.currentText().find(" -")] query = QSqlQuery() query.prepare('SELECT SUM(point), name FROM (SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ON ' 't_point.id_competitor = t_competitor.id ' 'WHERE t_point.id_competitor_take = 1 ' 'AND t_point.id_attraction = ' + id_attraction + ' UNION ALL ' 'SELECT t_point.point, t_competitor.name ' 'FROM t_point INNER JOIN t_competitor ' 'ON t_point.id_competitor_take = t_competitor.id ' 'WHERE t_point.id_competitor_take != 1 ' 'AND t_point.id_attraction = ' + id_attraction + ') ' 'GROUP BY name ORDER BY point DESC') query.setForwardOnly(1) query.exec_() pointsString = '<h1>Points:<br>' while query.next(): pointsString += str(query.value(0)) + ' - ' + str(query.value(1)) + '<br>' pointsString += '</h1>' return pointsString def showPoints(self): self.game_window.showPoints(self.parseGamePoints()) pass def __init__(self, window): self.setupUi(window) self.actionReload_DB.triggered.connect(self.connectDatabase) self.pushButton_Attraction_Add.clicked.connect(self.dialogAttraction) self.pushButton_Attraction_Edit.clicked.connect(self.dialogAttraction) self.pushButton_Competitor_Add.clicked.connect(self.dialogCompetitor) self.pushButton_Competitor_Edit.clicked.connect(self.dialogCompetitor) self.pushButton_Song_Add.clicked.connect(self.dialogSong) self.pushButton_Song_Edit.clicked.connect(self.dialogSong) self.pushButton_Song_Preview.clicked.connect(self.songPreview) self.pushButton_Game_Commit.clicked.connect(self.gameCommit) self.pushButton_Game_Rollback.clicked.connect(self.gameRollback) self.pushButton_Game_NextSong.clicked.connect(self.gameNextSong) self.pushButton_Game_OpenChildWindow.clicked.connect(self.childWindow) self.pushButton_Game_PlayVisible.clicked.connect(self.playVisible) self.pushButton_Game_PlayInvisible.clicked.connect(self.playInvisible) self.pushButton_Game_PlayPause.clicked.connect(self.playPause) self.pushButton_Game_ShowPoints.clicked.connect(self.showPoints) self.tableView_Song_List.clicked.connect(self.songProperties) self.tableView_Game_List.clicked.connect(self.gameProperties) self.comboBox_Game_Attraction.activated.connect(self.setupGame) self.game_window = game_window.GameWindow() self.connectDatabase() pass