New SQL queries - focustm/focust GitHub Wiki

Get all organizations of the user

select bound_user.id as buser_id, organization.title as org from bound_user join organization on organization.id=bound_user.organization_id join core_user on core_user.id=bound_user.user_id where core_user.email='[email protected]';  

Get all access tokens of the user

Only some fields

select access_token.id as acc_token_id, access_token.login_type from access_token join core_user on core_user.id=access_token.user_id where core_user.email='[email protected]';

All fields

select access_token.* from access_token join core_user on core_user.id=access_token.user_id where core_user.email='[email protected]';

Delete all software generated access tokens

delete from access_token using core_user where core_user.id=access_token.user_id and core_user.email='[email protected]' and access_token.login_type=1;

Find bound user of this session

select bound_user_id from access_token where access_token.id=25016;

Find a session where this bound_user works

select access_token.* from access_token where access_token.bound_user_id=3 and access_token.id!=25016;

Clockout bound user

UPDATE bound_user SET current_app_id = NULL WHERE id = 3;

Find old and clockout to insert new one

-- We assume that new bound user id = 4 and old was 3
-- 1. Lock the DB  
BEGIN;

-- 2. Check if correct bound_user is used
SELECT true from core_user JOIN bound_user ON bound_user.user_id=core_user.id 
JOIN access_token ON access_token.user_id=core_user.id 
WHERE bound_user.id=4 AND access_token.id=25013 FOR UPDATE;
  
-- 3. Check if this bound user works from another session
SELECT access_token.id FROM access_token 
JOIN bound_user ON bound_user.id=access_token.bound_user_id WHERE bound_user.id=4 FOR UPDATE; 

-- 4. Check if there is another bound user for this session
SELECT bound_user_id FROM access_token WHERE id = 25013 AND bound_user_id IS NOT NULL FOR UPDATE;

-- In case necessary for clockout old bound user
UPDATE access_token SET bound_user_id = NULL WHERE bound_user_id = 3;
UPDATE bound_user SET current_app_id = NULL WHERE id = 3;

-- 5. Update bound_user_id for this access_token (session)
UPDATE access_token SET bound_user_id = 4 WHERE id = 25013;

-- 6. First, find app provided from the field 'currentlyUsingAppStr', then 'currentlyUsingApp' of request body, then find the id

-- 7. Get id of the currently used app
SELECT id FROM app WHERE title='Google Chrome' FOR UPDATE;

-- if this title is missing then add it
INSERT INTO app (title) VALUES ('Google Chrome') RETURNING id;

-- find app category and employee's last time
SELECT app_categorization2.category, bound_user.last_attendance_switch_epoch_time_in_sec 
FROM app_categorization2
JOIN organization ON organization.id = app_categorization2.organization_id
JOIN bound_user ON bound_user.organization_id = organization.id 
WHERE bound_user.id = 11 AND app_categorization2.app_id=123 FOR UPDATE; 

-- If this app is not categorized for this employee (bound user), then categorize it with an unknown category
INSERT INTO app_categorization2 (app_id, organization_id, category)
SELECT 123, organization_id, 4  -- here 123 is app.id, and 4 is category (unknown)
FROM bound_user
WHERE bound_user.id = 11;  -- here 11 is bound_user.id


-- 6. Update bound user and in case current_attendance status is changed make insertion into 'employees_attendance_transitions'
DO $$
DECLARE
  v_old_status SMALLINT;
  v_old_app_full_id INT;
BEGIN
  -- Get the current 'current_attendance_status' for comparison
  SELECT employees_attendances_times.record_type INTO v_old_status 
    FROM employees_attendances_times 
    JOIN bound_user ON bound_user.id=employees_attendances_times.employee_id 
    WHERE employees_attendances_times.id=bound_user.current_attendance_time_id AND bound_user.id=11;

  -- Get current app id
  SELECT employees_apps_times.app_full_id INTO v_old_app_full_id 
    FROM employees_apps_times 
    JOIN bound_user on bound_user.id=employees_apps_times.employee_id 
    WHERE employees_apps_times.id=bound_user.current_app_full_time_id AND bound_user.id=11;
    
  -- Update 'bound_user' table
  UPDATE bound_user 
    SET current_attendance_time_id = CASE WHEN v_old_status != 2 
                                     THEN 
                                       -- In this case, I would like to make an insertion (like below)
                                       -- INSERT INTO employees_attendances_times (employee_id,epoch_time_in_sec,record_type) VALUES(11,1708760472,2)
                                       -- RETURNING id;
                                       -- to the table `employees_attendances_times` get the id 
                                       -- of the newly inserted record and set this to `current_attendance_time_id`
                                     ELSE 
                                       current_attendance_time_id  -- keep the same 
                                     END,
      current_app_full_time_id = CASE WHEN v_old_app_full_id != 123  -- here 123 is the new id off the app_full
                                     THEN 
                                       -- In this case, I would like to make an insertion (like below)
                                       -- INSERT INTO employees_apps_times (employee_id,app_full_id,epoch_time_in_sec) VALUES(11,123,1708760472)
                                       -- RETURNING id;
                                       -- to the table `employees_attendances_times` get the id 
                                       -- of the newly inserted record and set this to `current_app_full_time_id`
                                     ELSE 
                                       current_app_full_time_id  -- keep the same 
                                     END
    WHERE id = 11;  -- this is bound_user id to be updated
    
END $$;

-- 'app_full' table: insert entry and get id or simply get id
INSERT INTO app_full (app_id, app_image_path, app_main_wnd_text, app_url)
VALUES (10, 'Name of the App', 'wnd text', 'https://facebook.com')
ON CONFLICT (app_id, app_image_path, app_main_wnd_text, app_url)
DO UPDATE SET app_id = EXCLUDED.app_id
RETURNING id;


-- 7. Commit
COMMIT;

Clockout queries

UPDATE bound_user 
SET current_app_id = NULL, 
    current_attendance_status = 1, 
    last_attendance_switch_epoch_time_in_sec = 1708190028 
WHERE id = 11 AND current_attendance_status != 1;