PeakyTooth/database_functions.txt at master · nazar-kuziv/PeakyTooth · GitHub

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
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
---------- is_appointment_exist_by_id --------------

SELECT EXISTS (
    SELECT 1
    FROM appointments
    JOIN users ON dentist_id = userid
    WHERE id = appointment_id_input
    AND organization_id = organization_id_input
);

---------- get_user_with_organization_by_login --------------

BEGIN
    RETURN QUERY
    SELECT
        u.userid,
        u.login,
        u.name,
        u.surname,
        u.role,
        o.organization_name
    FROM
        users u
    LEFT JOIN
        organizations o
    ON
        u.organization_id = o.id
    WHERE
        u.login = login_param;
END;

---------- get_data_for_pdf --------------

BEGIN
    RETURN QUERY
    SELECT
        services,
        symptoms_description,
        mucous_membrane,
        periodontium,
        hygiene,
        oral_additional_info,
        dental_diagram,
        additional_info,
        treatments,
        medications,
        date,
        time,
        type,
        patient_name,
        patient_surname,
        sex,
        date_of_birth,
        organization_name,
        EIN,
        organizations.email,
        phone_number
    FROM
        appointments_details
    JOIN
        appointments ON appointment_id = appointments.id
    JOIN
        users ON dentist_id = users.userid
    JOIN
        organizations ON users.organization_id = organizations.id
    JOIN
        patients ON patient_id = patients.id
    WHERE
        users.organization_id = org_id
        AND appointments.id = app_id;
END;

---------- get_appointment_details --------------

SELECT
    appointment_id AS id,
    services,
    symptoms_description,
    mucous_membrane,
    periodontium,
    hygiene,
    oral_additional_info,
    dental_diagram,
    additional_info,
    medications,
    date,
    time,
    patient_id,
    dentist_id,
    organization_id,
    type
FROM
    appointments_details
JOIN
    appointments ON appointment_id = appointments.id
JOIN
    users ON dentist_id = users.userid
WHERE
    organization_id = organization_id_input
    AND appointment_id = appointment_id_input;

---------- get_appointment_data_for_pdf --------------

BEGIN
    RETURN QUERY
    SELECT
        ad.services,
        ad.symptoms_description,
        ad.mucous_membrane,
        ad.periodontium,
        ad.hygiene,
        ad.oral_additional_info,
        ad.dental_diagram,
        ad.additional_info,
        ad.treatments,
        ad.medications,
        a.date AS appointment_date,
        a.time AS appointment_time,
        a.type,
        p.id,
        p.patient_name,
        p.patient_surname,
        p.sex,
        p.date_of_birth,
        o.organization_name,
        o.ein,
        o.email,
        o.phone_number
    FROM
        appointments_details AS ad
    JOIN
        appointments AS a ON ad.appointment_id = a.id
    JOIN
        users AS u ON a.dentist_id = u.userid
    JOIN
        organizations AS o ON u.organization_id = o.id
    JOIN
        patients AS p ON a.patient_id = p.id
    WHERE
        u.organization_id = organization_id_input
        AND a.id = appointment_id_input;
END;