apisub_views.sql 1.29 KB
Newer Older
Stephane Crozat's avatar
Stephane Crozat committed
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
BEGIN;

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
FROM api
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.*,
TO_CHAR(su.subdate,'DD/MM/YYYY') AS subdate,
su.utclogin AS sublogin,
lo.*
FROM subscribe su
JOIN vapi ap ON ap.dbegin=su.dbegin AND ap.code=su.code
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;

CREATE OR REPLACE VIEW vapi_count AS
SELECT
dbegin,
code,
COUNT(utclogin) AS nbsub
FROM vsubscription
GROUP BY dbegin, code;

COMMIT;