From 13220c16acafe9e9c1cab5a59a6be4ce38b43754 Mon Sep 17 00:00:00 2001 From: s_alexander Date: Sun, 6 Dec 2009 12:13:34 +0000 Subject: Add Date to Score, Filter Players, Switch Difficulty - ID: 2901824 applied patch form sf.net bug tracker (id: 2901824) thanks to brunzelchen - Add a date-column to the score entries (UNIX-Timestamp); the date will be shown on Top5- und Stats Screen! - The Players on Top-5-Screen are now filtered: Every player with his best score! - You can switch through the difficulties on Top-5-Screen git-svn-id: svn://svn.code.sf.net/p/ultrastardx/svn/trunk@1972 b956fd51-792f-4845-bead-9b4dfca2ff2c --- src/base/UDataBase.pas | 127 +++++++++++++++++++++++--------------- src/base/USong.pas | 1 + src/base/UThemes.pas | 2 + src/screens/UScreenStatDetail.pas | 2 +- src/screens/UScreenTop5.pas | 78 ++++++++++++++++++++++- 5 files changed, 157 insertions(+), 53 deletions(-) (limited to 'src') diff --git a/src/base/UDataBase.pas b/src/base/UDataBase.pas index bdcbd30f..85b4b8e8 100644 --- a/src/base/UDataBase.pas +++ b/src/base/UDataBase.pas @@ -64,6 +64,7 @@ type Difficulty: byte; SongArtist: UTF8String; SongTitle: UTF8String; + Date: UTF8String; end; TStatResultBestSingers = class(TStatResult) @@ -85,7 +86,7 @@ type TimesSungTot: word; end; - + TDataBaseSystem = class private ScoreDB: TSQLiteDatabase; @@ -107,6 +108,7 @@ type procedure FreeStats(StatList: TList); function GetTotalEntrys(Typ: TStatType): cardinal; function GetStatReset: TDateTime; + function FormatDate(time_stamp: integer): UTF8String; end; var @@ -116,6 +118,7 @@ implementation uses DateUtils, + ULanguage, StrUtils, SysUtils, ULog; @@ -145,7 +148,7 @@ begin Log.LogStatus('Initializing database: "' + Filename.ToNative + '"', 'TDataBaseSystem.Init'); try - + // open database ScoreDB := TSQLiteDatabase.Create(Filename.ToUTF8); fFilename := Filename; @@ -192,7 +195,8 @@ begin '[SongID] INTEGER NOT NULL, ' + '[Difficulty] INTEGER NOT NULL, ' + '[Player] TEXT NOT NULL, ' + - '[Score] INTEGER NOT NULL' + + '[Score] INTEGER NOT NULL, ' + + '[Date] INTEGER NULL' + ');'); ScoreDB.ExecSQL('CREATE TABLE IF NOT EXISTS [' + cUS_Songs + '] (' + @@ -200,7 +204,7 @@ begin '[Artist] TEXT NOT NULL, ' + '[Title] TEXT NOT NULL, ' + '[TimesPlayed] INTEGER NOT NULL, ' + - '[Rating] INTEGER NULL' + + '[Rating] INTEGER NULL' + ');'); // convert data from 1.01 to 1.1 @@ -221,7 +225,15 @@ begin if not ScoreDB.ContainsColumn(cUS_Songs, 'Rating') then begin Log.LogInfo('Outdated song database found - adding column rating to "' + cUS_Songs + '"', 'TDataBaseSystem.Init'); - ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Songs + ' ADD COLUMN Rating INTEGER NULL'); + ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Songs + ' ADD COLUMN [Rating] INTEGER NULL'); + end; + + + //add column date to cUS-Scores + if not ScoreDB.ContainsColumn(cUS_Scores, 'Date') then + begin + Log.LogInfo('adding column date to "' + cUS_Scores + '"', 'TDataBaseSystem.Init'); + ScoreDB.ExecSQL('ALTER TABLE ' + cUS_Scores + ' ADD COLUMN [Date] INTEGER NULL'); end; except @@ -244,6 +256,27 @@ begin inherited; end; +(** + * Format a UNIX-Timestamp into DATE (If 0 then '') + *) +function TDataBaseSystem.FormatDate(time_stamp: integer): UTF8String; +var + Year, Month, Day: word; +begin + Result:=''; + try + if time_stamp<>0 then + begin + DecodeDate(UnixToDateTime(time_stamp), Year, Month, Day); + Result := Format(Language.Translate('STAT_FORMAT_DATE'), [Day, Month, Year]); + end; + except + on E: EConvertError do + Log.LogError('Error Parsing FormatString "STAT_FORMAT_DATE": ' + E.Message); + end; +end; + + (** * Read Scores into SongArray *) @@ -251,27 +284,28 @@ procedure TDataBaseSystem.ReadScore(Song: TSong); var TableData: TSQLiteUniTable; Difficulty: integer; + I: integer; + PlayerListed: boolean; begin if not Assigned(ScoreDB) then Exit; TableData := nil; - try // Search Song in DB TableData := ScoreDB.GetUniTable( - 'SELECT [Difficulty], [Player], [Score] FROM [' + cUS_Scores + '] ' + + 'SELECT [Difficulty], [Player], [Score], [Date] FROM [' + cUS_Scores + '] ' + 'WHERE [SongID] = (' + 'SELECT [ID] FROM [' + cUS_Songs + '] ' + 'WHERE [Artist] = ? AND [Title] = ? ' + 'LIMIT 1) ' + - 'ORDER BY [Score] DESC LIMIT 15', + 'ORDER BY [Score] DESC;', //no LIMIT! see filter below! [Song.Artist, Song.Title]); // Empty Old Scores - SetLength(Song.Score[0], 0); - SetLength(Song.Score[1], 0); - SetLength(Song.Score[2], 0); + SetLength(Song.Score[0], 0); //easy + SetLength(Song.Score[1], 0); //medium + SetLength(Song.Score[2], 0); //hard // Go through all Entrys while (not TableData.EOF) do @@ -281,12 +315,31 @@ begin if ((Difficulty >= 0) and (Difficulty <= 2)) and (Length(Song.Score[Difficulty]) < 5) then begin - SetLength(Song.Score[Difficulty], Length(Song.Score[Difficulty]) + 1); + //filter player + PlayerListed:=false; + if (Length(Song.Score[Difficulty])>0) then + begin + for I := 0 to Length(Song.Score[Difficulty]) - 1 do + begin + if (Song.Score[Difficulty, I].Name = TableData.FieldByName['Player']) then + begin + PlayerListed:=true; + break; + end; + end; + end; - Song.Score[Difficulty, High(Song.Score[Difficulty])].Name := + if not PlayerListed then + begin + SetLength(Song.Score[Difficulty], Length(Song.Score[Difficulty]) + 1); + + Song.Score[Difficulty, High(Song.Score[Difficulty])].Name := TableData.FieldByName['Player']; - Song.Score[Difficulty, High(Song.Score[Difficulty])].Score := + Song.Score[Difficulty, High(Song.Score[Difficulty])].Score := TableData.FieldAsInteger(TableData.FieldIndex['Score']); + Song.Score[Difficulty, High(Song.Score[Difficulty])].Date := + FormatDate(TableData.FieldAsInteger(TableData.FieldIndex['Date'])); + end; end; TableData.Next; @@ -314,9 +367,9 @@ begin if not Assigned(ScoreDB) then Exit; - // Prevent 0 Scores from being added - if (Score <= 0) then - Exit; + // Prevent 0 Scores from being added EDIT: ==> UScreenTop5.pas! + //if (Score <= 0) then + // Exit; TableData := nil; @@ -339,37 +392,10 @@ begin end; // Create new entry ScoreDB.ExecSQL( - 'INSERT INTO [' + cUS_Scores + '] ' + - '([SongID] ,[Difficulty], [Player], [Score]) VALUES ' + - '(?, ?, ?, ?);', - [ID, Level, Name, Score]); - - // Delete last position when there are more than 5 entrys. - // Fixes crash when there are > 5 ScoreEntrys - // Note: GetUniTable is not applicable here, as the results are used while - // table entries are deleted. - TableData := ScoreDB.GetTable( - 'SELECT [Player], [Score] FROM [' + cUS_Scores + '] ' + - 'WHERE [SongID] = ' + InttoStr(ID) + ' AND ' + - '[Difficulty] = ' + InttoStr(Level) + ' ' + - 'ORDER BY [Score] DESC LIMIT -1 OFFSET 5'); - - while (not TableData.EOF) do - begin - // Note: Score is an int-value, so in contrast to Player, we do not bind - // this value. Otherwise we had to convert the string to an int to avoid - // an automatic cast of this field to the TEXT type (although it might even - // work that way). - ScoreDB.ExecSQL( - 'DELETE FROM [' + cUS_Scores + '] ' + - 'WHERE [SongID] = ' + InttoStr(ID) + ' AND ' + - '[Difficulty] = ' + InttoStr(Level) +' AND ' + - '[Player] = ? AND ' + - '[Score] = ' + TableData.FieldByName['Score'], - [TableData.FieldByName['Player']]); - - TableData.Next; - end; + 'INSERT INTO [' + cUS_Scores + '] ' + + '([SongID] ,[Difficulty], [Player], [Score], [Date]) VALUES ' + + '(?, ?, ?, ?, ?);', + [ID, Level, Name, Score, DateTimeToUnix(Now())]); except on E: Exception do Log.LogError(E.Message, 'TDataBaseSystem.AddScore'); @@ -386,7 +412,7 @@ procedure TDataBaseSystem.WriteScore(Song: TSong); begin if not Assigned(ScoreDB) then Exit; - + try // Increase TimesPlayed ScoreDB.ExecSQL( @@ -421,7 +447,7 @@ begin // Create query case Typ of stBestScores: begin - Query := 'SELECT [Player], [Difficulty], [Score], [Artist], [Title] FROM [' + cUS_Scores + '] ' + + Query := 'SELECT [Player], [Difficulty], [Score], [Artist], [Title], [Date] FROM [' + cUS_Scores + '] ' + 'INNER JOIN [' + cUS_Songs + '] ON ([SongID] = [ID]) ORDER BY [Score]'; end; stBestSingers: begin @@ -471,6 +497,7 @@ begin Score := TableData.FieldAsInteger(2); SongArtist := TableData.Fields[3]; SongTitle := TableData.Fields[4]; + Date := FormatDate(TableData.FieldAsInteger(5)); end; end; stBestSingers: begin diff --git a/src/base/USong.pas b/src/base/USong.pas index f8698d43..c465f198 100644 --- a/src/base/USong.pas +++ b/src/base/USong.pas @@ -74,6 +74,7 @@ type TScore = record Name: UTF8String; Score: integer; + Date: UTF8String; end; { used to hold header tags that are not supported by this version of diff --git a/src/base/UThemes.pas b/src/base/UThemes.pas index c1a26927..4322815e 100644 --- a/src/base/UThemes.pas +++ b/src/base/UThemes.pas @@ -409,6 +409,7 @@ type TextNumber: AThemeText; TextName: AThemeText; TextScore: AThemeText; + TextDate: AThemeText; end; TThemeOptions = class(TThemeBasic) @@ -1176,6 +1177,7 @@ begin ThemeLoadTexts(Top5.TextNumber, 'Top5TextNumber'); ThemeLoadTexts(Top5.TextName, 'Top5TextName'); ThemeLoadTexts(Top5.TextScore, 'Top5TextScore'); + ThemeLoadTexts(Top5.TextDate, 'Top5TextDate'); // Options ThemeLoadBasic(Options, 'Options'); diff --git a/src/screens/UScreenStatDetail.pas b/src/screens/UScreenStatDetail.pas index 249626b0..1638cd85 100644 --- a/src/screens/UScreenStatDetail.pas +++ b/src/screens/UScreenStatDetail.pas @@ -234,7 +234,7 @@ begin if (Score > 0) then begin Text[I].Text := Format(FormatStr, - [Singer, Score, Theme.ILevel[Difficulty], SongArtist, SongTitle]); + [Singer, Score, Theme.ILevel[Difficulty], SongArtist, SongTitle, Date]); end; end; end; diff --git a/src/screens/UScreenTop5.pas b/src/screens/UScreenTop5.pas index f9c86643..b0795f54 100644 --- a/src/screens/UScreenTop5.pas +++ b/src/screens/UScreenTop5.pas @@ -47,11 +47,13 @@ type public TextLevel: integer; TextArtistTitle: integer; + DifficultyShow: integer; StaticNumber: array[1..5] of integer; TextNumber: array[1..5] of integer; TextName: array[1..5] of integer; TextScore: array[1..5] of integer; + TextDate: array[1..5] of integer; Fadeout: boolean; @@ -59,6 +61,7 @@ type function ParseInput(PressedKey: cardinal; CharCode: UCS4Char; PressedDown: boolean): boolean; override; function ParseMouse(MouseButton: integer; BtnDown: boolean; X, Y: integer): boolean; override; procedure OnShow; override; + procedure DrawScores(difficulty: integer); function Draw: boolean; override; end; @@ -98,6 +101,34 @@ begin Fadeout := true; end; end; + SDLK_RIGHT: + begin + inc(DifficultyShow); + if (DifficultyShow>2) then + DifficultyShow:=0; + DrawScores(DifficultyShow); + end; + SDLK_LEFT: + begin + dec(DifficultyShow); + if (DifficultyShow<0) then + DifficultyShow:=2; + DrawScores(DifficultyShow); + end; + SDLK_UP: + begin + inc(DifficultyShow); + if (DifficultyShow>2) then + DifficultyShow:=0; + DrawScores(DifficultyShow); + end; + SDLK_DOWN: + begin + dec(DifficultyShow); + if (DifficultyShow<0) then + DifficultyShow:=2; + DrawScores(DifficultyShow); + end; SDLK_SYSREQ: begin Display.SaveScreenShot; @@ -133,6 +164,7 @@ begin TextNumber[I+1] := AddText (Theme.Top5.TextNumber[I]); TextName[I+1] := AddText (Theme.Top5.TextName[I]); TextScore[I+1] := AddText (Theme.Top5.TextScore[I]); + TextDate[I+1] := AddText (Theme.Top5.TextDate[I]); end; end; @@ -141,10 +173,13 @@ procedure TScreenTop5.OnShow; var I: integer; PMax: integer; + sung: boolean; //score added? otherwise in wasn't sung! begin inherited; + sung := false; Fadeout := false; + DifficultyShow := Ini.Difficulty; //ReadScore(CurrentSong); @@ -152,9 +187,16 @@ begin if PMax = 4 then PMax := 5; for I := 0 to PMax do - DataBase.AddScore(CurrentSong, Ini.Difficulty, Ini.Name[I], Round(Player[I].ScoreTotalInt)); + begin + if (Round(Player[I].ScoreTotalInt) > 0) then + begin + DataBase.AddScore(CurrentSong, Ini.Difficulty, Ini.Name[I], Round(Player[I].ScoreTotalInt)); + sung:=true; + end; + end; - DataBase.WriteScore(CurrentSong); + if sung then + DataBase.WriteScore(CurrentSong); DataBase.ReadScore(CurrentSong); Text[TextArtistTitle].Text := CurrentSong.Artist + ' - ' + CurrentSong.Title; @@ -165,9 +207,11 @@ begin Text[TextNumber[I]].Visible := true; Text[TextName[I]].Visible := true; Text[TextScore[I]].Visible := true; + Text[TextDate[I]].Visible := true; Text[TextName[I]].Text := CurrentSong.Score[Ini.Difficulty, I-1].Name; Text[TextScore[I]].Text := IntToStr(CurrentSong.Score[Ini.Difficulty, I-1].Score); + Text[TextDate[I]].Text := CurrentSong.Score[Ini.Difficulty, I-1].Date; end; for I := Length(CurrentSong.Score[Ini.Difficulty]) + 1 to 5 do @@ -176,11 +220,41 @@ begin Text[TextNumber[I]].Visible := false; Text[TextName[I]].Visible := false; Text[TextScore[I]].Visible := false; + Text[TextDate[I]].Visible := false; end; Text[TextLevel].Text := IDifficulty[Ini.Difficulty]; end; +procedure TScreenTop5.DrawScores(difficulty: integer); +var + I: integer; +begin + for I := 1 to Length(CurrentSong.Score[difficulty]) do + begin + Static[StaticNumber[I]].Visible := true; + Text[TextNumber[I]].Visible := true; + Text[TextName[I]].Visible := true; + Text[TextScore[I]].Visible := true; + Text[TextDate[I]].Visible := true; + + Text[TextName[I]].Text := CurrentSong.Score[difficulty, I-1].Name; + Text[TextScore[I]].Text := IntToStr(CurrentSong.Score[difficulty, I-1].Score); + Text[TextDate[I]].Text := CurrentSong.Score[difficulty, I-1].Date; + end; + + for I := Length(CurrentSong.Score[difficulty]) + 1 to 5 do + begin + Static[StaticNumber[I]].Visible := false; + Text[TextNumber[I]].Visible := false; + Text[TextName[I]].Visible := false; + Text[TextScore[I]].Visible := false; + Text[TextDate[I]].Visible := false; + end; + + Text[TextLevel].Text := IDifficulty[difficulty]; +end; + function TScreenTop5.Draw: boolean; //var { -- cgit v1.2.3