Added new database table for tracking last player checkin with requisite Fortran procedures.

4 files changed, 198 insertions, 15 deletions

diff --git a/captain/api.f90 b/captain/api.f90
index 2f0a746..4d9545e 100644
--- a/captain/api.f90
+++ b/captain/api.f90
@@ -84,15 +84,17 @@ contains
     use captain_db
     use special_filenames
     use logging
+    use query_utilities
     implicit none
     
         type(request), intent(in)::req
         type(response)::resp
         
         character(PLAYER_NAME_LENGTH)::player, instruction
-        integer::job_i, player_i
+        integer::job_i, player_i, qs_platform_index
         
         character(len=:), pointer::checkin_work_json
+        type(query)::q
 
         ! Complete - "/api/player/{name}/job/{jobid}/complete"
         ! Failed   - "/api/player/{name}/job/{jobid}/failed"
@@ -130,6 +132,19 @@ contains
             ! the jobs as failed.
             call mark_working_jobs_as_failed(player_i)
             
+            ! Acknowledge the checkin in the database
+            if(associated(req%query_string)) then
+                call q%init(req%query_string)
+                if(associated(q%get_value("platform"))) then
+                    call acknowledge_checkin(player_i, q%get_value("platform"))
+                else
+                    call acknowledge_checkin(player_i)
+                end if
+                call q%destroy()
+            else
+                call acknowledge_checkin(player_i)
+            end if
+            
             job_i = get_pending_job_for_player(player_i)
             if(job_i < 0) then
                 resp%code = GEMINI_CODE_SUCCESS
diff --git a/captain/db.f90 b/captain/db.f90
index e4b4a4b..b789280 100644
--- a/captain/db.f90
+++ b/captain/db.f90
@@ -200,44 +200,84 @@ contains
         
     end function get_player_names
     
-    function get_instructions_count()
+    function get_instructions_count(player)
     implicit none
         
         type(sqlite3_stmt)::stmt
+        integer, intent(in), optional::player
         integer::get_instructions_count
         
         get_instructions_count = 0
-        if(stmt%prepare(db, "SELECT COUNT(*) FROM instructions") == SQLITE_OK) then
-            if(stmt%step() == SQLITE_ROW) then
-                get_instructions_count = stmt%column_int(0)
+        
+        if(present(player)) then
+        
+            if(stmt%prepare(db, "SELECT COUNT(*) FROM available WHERE player=?") == SQLITE_OK) then
+                if(stmt%bind_int(1, player) == SQLITE_OK) then
+                    if(stmt%step() == SQLITE_ROW) then
+                        get_instructions_count = stmt%column_int(0)
+                    end if
+                end if
+            end if
+            call stmt%finalize()
+            
+        else
+        
+            if(stmt%prepare(db, "SELECT COUNT(*) FROM instructions") == SQLITE_OK) then
+                if(stmt%step() == SQLITE_ROW) then
+                    get_instructions_count = stmt%column_int(0)
+                end if
             end if
+            call stmt%finalize()
+            
         end if
-        call stmt%finalize()
         
     end function get_instructions_count
     
-    function get_instruction_ids() result(res)
+    function get_instruction_ids(player) result(res)
     implicit none
         
         type(sqlite3_stmt)::stmt
+        integer, intent(in), optional::player
         integer, dimension(:), pointer::res
         integer::i,n
         
         res => null()
         
-        n = get_instructions_count()
+        if(present(player)) then
+            n = get_instructions_count(player)
+        else
+            n = get_instructions_count()
+        end if
+        
         if(n > 0) then
             allocate(res(n))
             res = -1
+        
+            if(present(player)) then
+        
+                if(stmt%prepare(db, "SELECT instruction FROM available WHERE player=?") == SQLITE_OK) then
+                    if(stmt%bind_int(1, player) == SQLITE_OK) then
+                        i = 1
+                        do while(stmt%step() == SQLITE_ROW .and. i <= n) 
+                            res(i) = stmt%column_int(0)
+                            i = i + 1
+                        end do
+                    end if
+                end if
+                call stmt%finalize()
+
+            else
             
-            if(stmt%prepare(db, "SELECT id, name FROM instructions ORDER BY name") == SQLITE_OK) then
-                i = 1
-                do while(stmt%step() == SQLITE_ROW .and. i <= n) 
-                    res(i) = stmt%column_int(0)
-                    i = i + 1
-                end do
+                if(stmt%prepare(db, "SELECT id, name FROM instructions ORDER BY name") == SQLITE_OK) then
+                    i = 1
+                    do while(stmt%step() == SQLITE_ROW .and. i <= n) 
+                        res(i) = stmt%column_int(0)
+                        i = i + 1
+                    end do
+                end if
+                call stmt%finalize()
+                
             end if
-            call stmt%finalize()
             
         end if
         
@@ -1113,4 +1153,100 @@ contains
         
     end function get_available_work_pairs_db
     
+    subroutine acknowledge_checkin(player, platform)
+    implicit none
+    
+        integer, intent(in)::player
+        character(*), intent(in), optional::platform
+        type(sqlite3_stmt)::stmt
+        integer, dimension(8)::right_now
+        integer::res
+        
+        call date_and_time(values=right_now)
+        
+        if(stmt%prepare(db, &
+            "INSERT OR UPDATE INTO checkin(player, year, month, day, hour, minute, second, platform) VALUES(?, ?, ?)") &
+            == SQLITE_OK) &
+        then
+            if(stmt%bind_int(1, player) == SQLITE_OK .AND. &
+               stmt%bind_int(2, right_now(1)) == SQLITE_OK .AND. &
+               stmt%bind_int(3, right_now(2)) == SQLITE_OK .AND. &
+               stmt%bind_int(4, right_now(3)) == SQLITE_OK .AND. &
+               stmt%bind_int(5, right_now(5)) == SQLITE_OK .AND. &
+               stmt%bind_int(6, right_now(6)) == SQLITE_OK .AND. &
+               stmt%bind_int(7, right_now(7)) == SQLITE_OK ) &
+            then
+               
+                if(.not. present(platform)) then
+                    res = stmt%bind_null(8)
+                else
+                    res = stmt%bind_text(8, platform)
+                end if
+                
+                if(res == SQLITE_OK) then
+                    call stmt%step_now()
+                end if
+               
+            end if
+        end if
+        call stmt%finalize()
+        
+    end subroutine acknowledge_checkin
+    
+    ! For definition of values. see Fortran's DATE_AND_TIME
+    subroutine get_last_checkin_time(player, values)
+    use sqlite
+    implicit none
+    
+        integer, intent(in)::player
+        integer, dimension(8), intent(out)::values
+        
+        type(sqlite3_stmt)::stmt
+        integer::i
+        
+        ! This call properly retrieves the offset from UTC if you want it...
+        call date_and_time(values=values)
+        values(1:3) = 0
+        values(5:8) = 0
+        
+        if(stmt%prepare(db, "SELECT year, month, day, hour, minute, second FROM checkin WHERE player=? LIMIT 1") == SQLITE_OK) then
+            if(stmt%bind_int(1, player) == SQLITE_OK) then
+                if(stmt%step() == SQLITE_ROW) then
+                    do i = 1, 7
+                        if(i < 4) then
+                            values(i) = stmt%column_int(i-1)
+                        else if(i >= 5) then
+                            values(i-1) = stmt%column_int(i-1)
+                        end if
+                    end do
+                end if
+            end if
+        end if
+        call stmt%finalize()
+    
+    end subroutine get_last_checkin_time
+    
+    subroutine get_player_platform(player, platform)
+    use sqlite
+    implicit none
+    
+        integer, intent(in)::player
+        character(*), intent(out)::platform
+        type(sqlite3_stmt)::stmt
+        
+        platform = " "
+        
+        if(stmt%prepare(db, "SELECT platform FROM checkin WHERE player=? LIMIT 1") == SQLITE_OK) then
+            if(stmt%bind_int(1, player) == SQLITE_OK) then
+                if(stmt%step() == SQLITE_ROW) then
+                    if(stmt%column_type(0) == SQLITE_TEXT) then
+                        call stmt%column_text(0, platform)
+                    end if
+                end if
+            end if
+        end if
+        call stmt%finalize()
+    
+    end subroutine get_player_platform
+    
 end module captain_db
diff --git a/captain/sql/create.sql b/captain/sql/create.sql
index 6457296..e4a944c 100644
--- a/captain/sql/create.sql
+++ b/captain/sql/create.sql
@@ -12,3 +12,5 @@ CREATE TABLE available(instruction INTEGER, player INTEGER, FOREIGN KEY(instruct
 CREATE TABLE groups(id INTEGER PRIMARY KEY, name TEXT UNIQUE NOT NULL);
 
 CREATE TABLE group_instructions(group_id INTEGER, instruction INTEGER, player INTEGER, FOREIGN KEY(group_id) REFERENCES groups(id) ON DELETE CASCADE, FOREIGN KEY(instruction) REFERENCES instructions(id) ON DELETE CASCADE, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE);
+
+CREATE TABLE checkin(player INTEGER PRIMARY KEY, year INTEGER, month INTEGER, day INTEGER, hour INTEGER, minute INTEGER, second INTEGER, os TEXT DEFAULT NULL, FOREIGN KEY(player) REFERENCES players(id) ON DELETE CASCADE);
diff --git a/captain/web.f90 b/captain/web.f90
index dac658b..ea3127e 100644
--- a/captain/web.f90
+++ b/captain/web.f90
@@ -555,6 +555,36 @@ contains
         
     end function generate_players_html
 
+    function generate_one_player_html(req) result(res)
+    use captain_db
+    use server_response
+    use request_utils
+    implicit none
+
+        type(request), intent(in)::req
+        character(len=:), pointer::res
+        
+        character(len=PLAYER_NAME_LENGTH)::player_name
+        integer::n, pid
+        
+        call req%last_component(player_name)
+        pid = get_player_id(trim(player_name))
+        
+        n = get_instructions_count(player=pid)
+        allocate(character(len=(2*n*PLAYER_NAME_LENGTH + 1024)) :: res)
+        
+        res = "<h2>"//trim(player_name)//"</h2>"
+        
+        ! Last checkin
+        
+        
+        ! List of instructions
+        
+        ! Token assignment
+        
+    
+    end function generate_one_player_html
+
     function generate_one_job_html(req) result(res)
     use captain_db
     use server_response