FAQ | This is a LIVE service | Changelog

Skip to content
Snippets Groups Projects

1206: Add groups for signin system

Merged Dr Catherine Pitt requested to merge 60-new-views-for-mifare-cards into master
2 files
+ 67
0
Compare changes
  • Side-by-side
  • Inline
Files
2
-- tables for mifare signin groups
CREATE TABLE public.signin_system_group (
signin_system_group_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
name TEXT UNIQUE NOT NULL
);
ALTER TABLE public.signin_system_group OWNER TO dev;
-- This is set up as a many-many join to allow us to easily move from a many-one relationship
-- now to a many-many relationship in future
CREATE TABLE public.mm_person_signin_system_group (
person_id BIGINT NOT NULL REFERENCES person(id),
signin_system_group_id BIGINT NOT NULL REFERENCES signin_system_group(signin_system_group_id),
PRIMARY KEY (person_id, signin_system_group_id)
);
-- For now, one group per person
CREATE UNIQUE INDEX one_signin_group_per_person ON mm_person_signin_system_group(person_id);
ALTER TABLE public.mm_person_signin_system_group OWNER TO dev;
-- start with the cleaners group
INSERT INTO public.signin_system_group ( name ) VALUES ( 'Cleaners' );
-- hotwire views to edit it
CREATE VIEW hotwire3.signin_system_group_hid AS
SELECT signin_system_group_id,
name AS signin_system_group_hid
FROM public.signin_system_group;
ALTER VIEW hotwire3.signin_system_group_hid OWNER TO dev;
GRANT SELECT ON hotwire3.signin_system_group_hid TO PUBLIC;
CREATE VIEW hotwire3."10_View/Sign_In_System/Groups" AS
SELECT person_id AS id,
person_id,
signin_system_group_id
FROM public.mm_person_signin_system_group;
ALTER VIEW hotwire3."10_View/Sign_In_System/Groups" OWNER TO dev;
GRANT SELECT, INSERT, UPDATE, DELETE ON hotwire3."10_View/Sign_In_System/Groups" TO cos;
-- for the CSV generating app
CREATE VIEW apps.signin_system_grouped_cards AS
SELECT row_number() OVER () AS id,
COALESCE(COALESCE(COALESCE(p.known_as, p.first_names)::text || ' '::text, ''::text) || p.surname::text, n.lookup_visible_name, c.full_name, c.crsid) AS "Name",
COALESCE(p.email_address, n.lookup_email::character varying, lower(c.crsid)::character varying) AS "Email",
NULL::text AS "Mobile",
NULL::text AS "Role",
mifare_id_to_signin(c.mifare_id) AS "RFID",
signin_system_group.name AS group_name
FROM university_card c
LEFT JOIN person p ON lower(p.crsid::text) = lower(c.crsid)
LEFT JOIN university_cardholder_detail n ON n.crsid = lower(c.crsid)
LEFT JOIN public.mm_person_signin_system_group ON p.id = mm_person_signin_system_group.person_id
LEFT JOIN public.signin_system_group USING (signin_system_group_id)
WHERE c.expires_at > CURRENT_TIMESTAMP AND c.crsid IS NOT NULL AND c.mifare_id IS NOT NULL;
;
ALTER VIEW apps.signin_system_grouped_cards OWNER TO dev;
GRANT SELECT ON apps.signin_system_grouped_cards TO cos,reception;
Loading