apisub_views.sql 1.27 KB
Newer Older
Stephane Crozat's avatar
Stephane Crozat committed
1
2
BEGIN;

3
4
5
6
7
8
9
CREATE OR REPLACE VIEW vapi_count AS
SELECT
api AS countid,
COUNT(utclogin) AS nbsub
FROM subscribe
GROUP BY api;

Stephane Crozat's avatar
Stephane Crozat committed
10
11
12
13
14
15
16
17
18
CREATE OR REPLACE VIEW vapi AS
SELECT
*,
TO_CHAR(dbegin,'TMday FMDD TMmonth') AS normdbegin,
CASE WHEN code<10 THEN '000'||code WHEN code>=10 THEN '00'||code END AS normcode,
TO_CHAR(dbegin + duration-1,'TMday FMDD TMmonth') AS normdend,
TO_CHAR(dbegin, 'ww') AS week,
TO_CHAR(dbegin, 'yyyy') AS year,
semester(dbegin) AS semester
19
FROM api LEFT JOIN vapi_count ON id=countid
Stephane Crozat's avatar
Stephane Crozat committed
20
21
22
23
24
25
26
27
28
29
30
31
ORDER BY dbegin, code;

CREATE OR REPLACE VIEW vlocaluser AS
SELECT l.utclogin, u.surname, u.firstname, l.email, MAX(u.speciality) AS speciality, MAX(u.level) AS level,
  CASE WHEN COUNT(*)>1 THEN 'duplication' END AS warning
FROM localuser l
LEFT JOIN utcstudent u ON l.surname=u.surname AND l.firstname=unaccent_string(u.firstname)
GROUP BY l.utclogin, u.surname, u.firstname;

CREATE OR REPLACE VIEW vsubscription AS
SELECT
ap.*,
32
33
34
35
lo.*,
su.subdate,
su.validation,
su.validationdate
Stephane Crozat's avatar
Stephane Crozat committed
36
FROM subscribe su
37
JOIN vapi ap ON ap.id=su.api
Stephane Crozat's avatar
Stephane Crozat committed
38
39
40
41
42
43
44
45
LEFT JOIN vlocaluser lo ON su.utclogin=lo.utclogin
ORDER BY ap.dbegin, ap.code, lo.firstname, lo.surname, lo.utclogin;

CREATE OR REPLACE VIEW vsubscription_anonymous AS
SELECT normcode, name, normdbegin, normdend, size, speciality, level
FROM vsubscription;

COMMIT;