aboutsummaryrefslogtreecommitdiff
path: root/captain
diff options
context:
space:
mode:
authorJeffrey Armstrong <jeff@approximatrix.com>2021-06-22 17:04:59 -0400
committerJeffrey Armstrong <jeff@approximatrix.com>2021-06-22 17:04:59 -0400
commitd6f9ce81e4fd27c94cf656699ef4952956dff608 (patch)
treee3d3abab4b22b271b0886dc49830691f9f3f4c3e /captain
parent660235928d1dd57d85cb7bd986c5036783a6073f (diff)
downloadlevitating-d6f9ce81e4fd27c94cf656699ef4952956dff608.tar.gz
levitating-d6f9ce81e4fd27c94cf656699ef4952956dff608.zip
Added new database table for tracking last player checkin with requisite Fortran procedures.
Diffstat (limited to 'captain')
-rw-r--r--captain/api.f9017
-rw-r--r--captain/db.f90164
-rw-r--r--captain/sql/create.sql2
-rw-r--r--captain/web.f9030
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