sqlite/admin: Report all timestamp fields with localtime instead of UTC/GMT

Made all SELECT queries which is used for reports to use the new 'locdt' SQL
function on timestamp fields.  This converts the UTC/GMT timestamps stored in
the database to the correct timezone of the running admin client.

Signed-off-by: David Sommerseth <dazo@users.sourceforge.net>

7 files changed, 15 insertions, 12 deletions

diff --git a/database/sqlite/administration/attempts.c b/database/sqlite/administration/attempts.c
index d296172..c884b4e 100644
--- a/database/sqlite/administration/attempts.c
+++ b/database/sqlite/administration/attempts.c
@@ -67,7 +67,7 @@ xmlDoc *attempts_list(eurephiaCTX *ctx, eDBfieldMap *fmap) {
         // Query the database for registered attempts
         res = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT username, lower(digest), remoteip, attempts,"
-                                  "       registered, last_attempt, atpid"
+                                  "       locdt(registered), locdt(last_attempt), atpid"
                                   "  FROM openvpn_attempts",
                                   NULL, fmap, "atpid");
         if( sqlite_query_status(res) != dbSUCCESS ) {
diff --git a/database/sqlite/administration/blacklist.c b/database/sqlite/administration/blacklist.c
index 66a5f6f..0662bbc 100644
--- a/database/sqlite/administration/blacklist.c
+++ b/database/sqlite/administration/blacklist.c
@@ -68,7 +68,7 @@ xmlDoc *blacklist_list(eurephiaCTX *ctx, eDBfieldMap *fmap) {
         // Query the database for registered attempts
         res = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT username, lower(digest), remoteip,"
-                                  "       registered, last_accessed, blid"
+                                  "       locdt(registered), locdt(last_accessed), blid"
                                   "  FROM openvpn_blacklist",
                                   NULL, fmap, "blid");
         if( sqlite_query_status(res) != dbSUCCESS ) {
diff --git a/database/sqlite/administration/certificates.c b/database/sqlite/administration/certificates.c
index 8db12ff..93518b6 100644
--- a/database/sqlite/administration/certificates.c
+++ b/database/sqlite/administration/certificates.c
@@ -87,7 +87,7 @@ static xmlDoc *certificate_list(eurephiaCTX *ctx, eDBfieldMap *srch_map, const c
 
         res = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT depth, lower(digest), common_name, organisation, email, "
-				  "       registered, certid"
+				  "       locdt(registered), certid"
                                   "  FROM openvpn_certificates", NULL, srch_map, sortkeys);
         if( sqlite_query_status(res) != dbSUCCESS ) {
                 eurephia_log(ctx, LOG_ERROR, 0, "Could not query the certificate table");
diff --git a/database/sqlite/administration/firewalladmin.c b/database/sqlite/administration/firewalladmin.c
index fd34183..b10c5d0 100644
--- a/database/sqlite/administration/firewalladmin.c
+++ b/database/sqlite/administration/firewalladmin.c
@@ -81,7 +81,7 @@ xmlDoc *fwadmin_search(eurephiaCTX *ctx, eDBfieldMap *fmap) {
                                   "SELECT access_descr, fw_profile, accessprofile, "
                                   "       uid, username, "
                                   "       uac.certid, common_name, organisation, "
-                                  "       email, lower(digest), c.registered, uicid "
+                                  "       email, lower(digest), locdt(c.registered), uicid "
                                   "  FROM openvpn_accesses"
                                   "  LEFT JOIN openvpn_usercerts uac USING (accessprofile)"
                                   "  LEFT JOIN openvpn_users USING (uid)"
diff --git a/database/sqlite/administration/lastlog.c b/database/sqlite/administration/lastlog.c
index ff7b479..2fc2904 100644
--- a/database/sqlite/administration/lastlog.c
+++ b/database/sqlite/administration/lastlog.c
@@ -94,7 +94,8 @@ xmlDoc *eDBadminGetLastlog(eurephiaCTX *ctx, xmlDoc *srch_xml, const char *sortk
         res = sqlite_query_mapped(ctx, SQL_SELECT,
                                   "SELECT llid, ll.certid, protocol, remotehost, remoteport, macaddr,"
                                   "       vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
-                                  "       login, logout, session_duration, session_deleted,"
+                                  "       locdt(login), locdt(logout),"
+                                  "       session_duration, locdt(session_deleted),"
                                   "       bytes_sent, bytes_received, uicid, accessprofile,"
                                   "       access_descr, fw_profile, depth, lower(digest),"
                                   "       common_name, organisation, email, username, ll.uid"
diff --git a/database/sqlite/administration/useraccount.c b/database/sqlite/administration/useraccount.c
index a989257..31d4af2 100644
--- a/database/sqlite/administration/useraccount.c
+++ b/database/sqlite/administration/useraccount.c
@@ -105,8 +105,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
 
         // Query the database, find the user defined in the user map
         uinf = sqlite_query_mapped(ctx, SQL_SELECT,
-                                   "SELECT users.username, users.activated, users.deactivated,"
-                                   "       users.last_accessed, users.uid,"
+                                   "SELECT users.username, locdt(users.activated), locdt(users.deactivated),"
+                                   "       locdt(users.last_accessed), users.uid,"
                                    "       (bl.username IS NOT NULL), opensess, logincount,"
                                    "       (at.attempts > 0)"
                                    "  FROM openvpn_users users"
@@ -181,7 +181,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                         // Extract certificate info
                         qres = sqlite_query(ctx,
                                             "SELECT depth, lower(digest), common_name, organisation, email, "
-                                            "       c.registered, c.certid,  uc.accessprofile, access_descr,"
+                                            "       locdt(c.registered), c.certid,"
+                                            "       uc.accessprofile, access_descr,"
                                             "       fw_profile"
                                             "  FROM openvpn_certificates c"
                                             "  LEFT JOIN openvpn_usercerts uc ON (c.certid = uc.certid)"
@@ -233,7 +234,8 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                         qres = sqlite_query(ctx,
                                             "SELECT llid, ll.certid,protocol,remotehost,remoteport,macaddr,"
                                             "       vpnipaddr, vpnipmask, sessionstatus, sessionkey,"
-                                            "       login, logout, session_duration, session_deleted,"
+                                            "       locdt(login), locdt(logout),"
+                                            "       session_duration, locdt(session_deleted),"
                                             "       bytes_sent, bytes_received, uicid, accessprofile,"
                                             "       access_descr, fw_profile, depth, lower(digest),"
                                             "       common_name, organisation, email"
@@ -308,7 +310,7 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                         xmlNode *atmpt = NULL;
 
                         qres = sqlite_query(ctx,
-                                            "SELECT attempts, registered, last_attempt, atpid"
+                                            "SELECT attempts, locdt(registered), locdt(last_attempt), atpid"
                                             "  FROM openvpn_attempts "
                                             " WHERE username = '%q'", username);
 
@@ -342,7 +344,7 @@ static xmlDoc *useracc_view(eurephiaCTX *ctx, unsigned int infoType,
                         xmlNode *atmpt = NULL;
 
                         qres = sqlite_query(ctx,
-                                            "SELECT registered, last_accessed, blid"
+                                            "SELECT locdt(registered), locdt(last_accessed), blid"
                                             "  FROM openvpn_blacklist "
                                             " WHERE username = '%q'", username);
 
diff --git a/database/sqlite/administration/usercerts.c b/database/sqlite/administration/usercerts.c
index 8e654ab..c6f368e 100644
--- a/database/sqlite/administration/usercerts.c
+++ b/database/sqlite/administration/usercerts.c
@@ -81,7 +81,7 @@ xmlDoc *usercerts_search(eurephiaCTX *ctx, eDBfieldMap *where_m, const char *sor
         }
 
         res = sqlite_query_mapped(ctx, SQL_SELECT,
-                                  "SELECT uicid, ucs.uid AS uid, certid, ucs.registered AS registered,"
+                                  "SELECT uicid, ucs.uid AS uid, certid, locdt(ucs.registered) AS registered,"
                                   "       ucs.accessprofile AS accessprofile, access_descr,"
                                   "       username, "
                                   "       common_name, organisation, email, lower(digest), depth "