SU9CCPtM

1drop table if exists card_payment;
2drop table if exists bank_payment;
3drop table if exists persons;
4drop view if exists needs_confirmation;
5
6create table card_payment (
7    payment_id int primary key not null,
8    buyer varchar(255) not null,
9    buyer_email varchar(255) not null,
10    payment_date date not null,
11    amount int not null,
12    outcome varchar(255) not null
13    # 2, 3, 4, 5, 6, 11
14);
15
16create table bank_payment (
17    payment_id int primary key not null,
18    buyer varchar(255) not null,
19    payment_date date not null,
20    amount int not null,
21    notes varchar(255) not null
22);
23
24create table persons (
25    person_id int primary key not null,
26    payment_id_f int not null,
27    lastname varchar(255) not null,
28    name varchar(255) not null,
29    street varchar(255) not null,
30    postcode varchar(255) not null,
31    city varchar(255) not null,
32    state varchar(255) not null,
33    country varchar(255) not null,
34    institution varchar(255) not null,
35    position varchar(255) not null,
36    email varchar(255) not null,
37    phone varchar(255) not null,
38    enrolment_date varchar(255) not null,
39    arrival varchar(255) not null,
40    departure varchar(255) not null,
41    reception boolean not null,
42    dinner boolean not null,
43    accomp_dinner int not null,
44    payment_type enum('card', 'onsite', 'transfer') not null,
45    fees_amount int not null,
46    payment_confirmed boolean not null
47    
48    # 0, 1, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 15, 17 not null,
49    # 18, 19, 20, 22, 24, 25, 26
50    
51    # 15 date, 19 bool, 20 bool, 24 payment data, 26 bool
52);
53
54create view needs_confirmation as
55    select person_id, lastname, name, payment_type,
56        concat("bla bla", person_id) as clickable_url
57    from persons
58    where payment_id_f in (
59        select payment_id from card_payment
60        union
61        select payment_id from bank_payment
62    )
63    and payment_confirmed = 0;