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

5 files changed, 157 insertions, 53 deletions

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
@@ -245,33 +257,55 @@ begin
 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
  *)
 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
 {