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
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
