Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

feature request: user access report #1

Open
john-potts opened this issue Jun 6, 2022 · 3 comments
Open

feature request: user access report #1

john-potts opened this issue Jun 6, 2022 · 3 comments
Labels
enhancement New feature or request

Comments

@john-potts
Copy link

I would like to be able to specify a member and a range of time, and receive a report stating whether or not they used their fob within that time period.

this is intended to be used in distinguishing "abandoned" memberships vs members who used the space without paying and were subsequently terminated for non-payment.

@john-potts john-potts added the enhancement New feature or request label Jun 6, 2022
@IanEdington
Copy link
Member

IanEdington commented Jun 14, 2024

This is currently being accomplished with the following query

select u.first_name,
       u.last_name,
       u.id user_id,
       max(lock_in) most_recent_visit,
       min(lock_in) first_visit
        from kos.authentications a
join kos.users u on a.user_id = u.id
        where u.id = <user_id>
         group by u.id
limit 100;

@Kpapadak1
Copy link

Kpapadak1 commented Oct 23, 2024

We need a portal/report be created so that a non-technical BOD member do this? having to do DB queries is not an easy thing for people that do not have access or understand how the backend works.
Also this report should show what door and tools where used with their fob

@IanEdington
Copy link
Member

IanEdington commented Oct 24, 2024

-- who used the table saw in the last 48 hours
select a.lock_in,
       concat(u.first_name, ' ', u.last_name) full_name,
       g.name gatekeeper
from kos.authentications a
         join kos.users u on a.user_id = u.id
         join kos.gatekeepers g on a.gatekeeper_id = g.id
where a.created_at >= now() - interval 2 day
order by 1

This SQL query retrieves information about user authentications from the kos.authentications table, including the lock-in time, the user's full name, and the gatekeeper's name. Here's a breakdown of the query:

  1. Select Clause:

    • a.lock_in: Selects the lock_in column from the kos.authentications table.
    • concat(u.first_name, ' ', u.last_name) full_name: Concatenates the first_name and last_name columns from the kos.users table to create a full name.
    • g.name gatekeeper: Selects the name column from the kos.gatekeepers table and aliases it as gatekeeper.
  2. From Clause:

    • kos.authentications a: Specifies the kos.authentications table with an alias a.
  3. Join Clauses:

    • join kos.users u on a.user_id = u.id: Joins the kos.users table with the kos.authentications table on the user_id column.
    • join kos.gatekeepers g on a.gatekeeper_id = g.id: Joins the kos.gatekeepers table with the kos.authentications table on the gatekeeper_id column.
  4. Where Clause:

    • a.created_at >= now() - interval 2 day: Filters the results to include only records where the created_at column is within the last 2 days.
  5. Order By Clause:

    • order by 1: Orders the results by the first selected column, which is a.lock_in.

The query returns a list of authentications from the last 2 days, including the lock-in time, the user's full name, and the gatekeeper's name, ordered by the lock-in time.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
enhancement New feature or request
Projects
None yet
Development

No branches or pull requests

3 participants