database.py 16 KB
Newer Older
1
2
from flask import render_template, send_file, url_for, redirect, flash, request

Robin Sonnabend's avatar
Robin Sonnabend committed
3
from datetime import datetime, time, date, timedelta
4
import math
5
from io import StringIO, BytesIO
6
7

from shared import db
Robin Sonnabend's avatar
Robin Sonnabend committed
8
from utils import random_string, url_manager, get_etherpad_url
Robin Sonnabend's avatar
Robin Sonnabend committed
9
from models.errors import DateNotMatchingException
10

11
import os
12

13
14
from sqlalchemy import event
from sqlalchemy.orm import relationship, backref, sessionmaker
Robin Sonnabend's avatar
Robin Sonnabend committed
15
from sqlalchemy.ext.hybrid import hybrid_method
16

Robin Sonnabend's avatar
Robin Sonnabend committed
17
18
import config

19
20
21
22
23
24
class ProtocolType(db.Model):
    __tablename__ = "protocoltypes"
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String, unique=True)
    short_name = db.Column(db.String, unique=True)
    organization = db.Column(db.String)
Robin Sonnabend's avatar
Robin Sonnabend committed
25
    usual_time = db.Column(db.Time)
26
27
28
29
30
    is_public = db.Column(db.Boolean)
    private_group = db.Column(db.String)
    public_group = db.Column(db.String)
    private_mail = db.Column(db.String)
    public_mail = db.Column(db.String)
Robin Sonnabend's avatar
Robin Sonnabend committed
31
32
33
    use_wiki = db.Column(db.Boolean)
    wiki_category = db.Column(db.String)
    wiki_only_public = db.Column(db.Boolean)
Robin Sonnabend's avatar
Robin Sonnabend committed
34
    printer = db.Column(db.String)
35
36
37

    protocols = relationship("Protocol", backref=backref("protocoltype"), cascade="all, delete-orphan", order_by="Protocol.id")
    default_tops = relationship("DefaultTOP", backref=backref("protocoltype"), cascade="all, delete-orphan", order_by="DefaultTOP.number")
38
    reminders = relationship("MeetingReminder", backref=backref("protocoltype"), cascade="all, delete-orphan", order_by="MeetingReminder.days_before")
Robin Sonnabend's avatar
Robin Sonnabend committed
39
    todos = relationship("Todo", backref=backref("protocoltype"), order_by="Todo.id")
40

Robin Sonnabend's avatar
Robin Sonnabend committed
41
    def __init__(self, name, short_name, organization, usual_time,
Robin Sonnabend's avatar
Robin Sonnabend committed
42
            is_public, private_group, public_group, private_mail, public_mail,
Robin Sonnabend's avatar
Robin Sonnabend committed
43
            use_wiki, wiki_category, wiki_only_public, printer):
44
45
46
        self.name = name
        self.short_name = short_name
        self.organization = organization
Robin Sonnabend's avatar
Robin Sonnabend committed
47
        self.usual_time = usual_time
48
49
50
51
52
        self.is_public = is_public
        self.private_group = private_group
        self.public_group = public_group
        self.private_mail = private_mail
        self.public_mail = public_mail
Robin Sonnabend's avatar
Robin Sonnabend committed
53
54
55
        self.use_wiki = use_wiki
        self.wiki_category = wiki_category
        self.wiki_only_public = wiki_only_public
Robin Sonnabend's avatar
Robin Sonnabend committed
56
        self.printer = printer
57
58

    def __repr__(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
59
60
61
        return ("<ProtocolType(id={}, short_name={}, name={}, "
                "organization={}, is_public={}, private_group={}, "
                "public_group={}, use_wiki={}, wiki_category='{}', "
Robin Sonnabend's avatar
Robin Sonnabend committed
62
                "wiki_only_public={}, printer={}, usual_time={})>".format(
Robin Sonnabend's avatar
Robin Sonnabend committed
63
64
65
            self.id, self.short_name, self.name,
            self.organization, self.is_public, self.private_group,
            self.public_group, self.use_wiki, self.wiki_category,
Robin Sonnabend's avatar
Robin Sonnabend committed
66
            self.wiki_only_public, self.printer, self.usual_time))
Robin Sonnabend's avatar
Robin Sonnabend committed
67
68

    def get_latest_protocol(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
69
        candidates = sorted([protocol for protocol in self.protocols if protocol.is_done()], key=lambda p: p.date, reverse=True)
Robin Sonnabend's avatar
Robin Sonnabend committed
70
71
72
73
        if len(candidates) == 0:
            return None
        return candidates[0]

Robin Sonnabend's avatar
Robin Sonnabend committed
74
    @hybrid_method
Robin Sonnabend's avatar
Robin Sonnabend committed
75
76
77
78
79
80
81
    def has_public_view_right(self, user):
        return (self.is_public
            or (user is not None and 
                ((self.public_group != "" and self.public_group in user.groups)
                or (self.private_group != "" and self.private_group in user.groups))))

    def has_private_view_right(self, user):
Robin Sonnabend's avatar
Robin Sonnabend committed
82
        return (user is not None and self.private_group != "" and self.private_group in user.groups)
Robin Sonnabend's avatar
Robin Sonnabend committed
83
84
85
86

    def has_modify_right(self, user):
        return self.has_private_view_right(user)

Robin Sonnabend's avatar
Robin Sonnabend committed
87
    @staticmethod
Robin Sonnabend's avatar
Robin Sonnabend committed
88
    def get_modifiable_protocoltypes(user):
Robin Sonnabend's avatar
Robin Sonnabend committed
89
90
91
92
93
        return [
            protocoltype for protocoltype in ProtocolType.query.all()
            if protocoltype.has_modify_right(user)
        ]

Robin Sonnabend's avatar
Robin Sonnabend committed
94
95
96
97
98
99
100
101
102
103
104
105
106
107
    @staticmethod
    def get_public_protocoltypes(user):
        return [
            protocoltype for protocoltype in ProtocolType.query.all()
            if protocoltype.has_public_view_right(user)
        ]

    @staticmethod
    def get_private_protocoltypes(user):
        return [
            protocoltype for protocoltype in ProtocolType.query.all()
            if protocoltype.has_private_view_right(user)
        ]

108
109
110
111
class Protocol(db.Model):
    __tablename__ = "protocols"
    id = db.Column(db.Integer, primary_key=True)
    protocoltype_id = db.Column(db.Integer, db.ForeignKey("protocoltypes.id"))
112
113
114
    source = db.Column(db.String)
    content_public = db.Column(db.String)
    content_private = db.Column(db.String)
115
116
117
118
119
120
    date = db.Column(db.Date)
    start_time = db.Column(db.Time)
    end_time = db.Column(db.Time)
    author = db.Column(db.String)
    participants = db.Column(db.String)
    location = db.Column(db.String)
Robin Sonnabend's avatar
Robin Sonnabend committed
121
    done = db.Column(db.Boolean)
122
123
124
125
126
127

    tops = relationship("TOP", backref=backref("protocol"), cascade="all, delete-orphan", order_by="TOP.number")
    decisions = relationship("Decision", backref=backref("protocol"), cascade="all, delete-orphan", order_by="Decision.id")
    documents = relationship("Document", backref=backref("protocol"), cascade="all, delete-orphan", order_by="Document.is_compiled")
    errors = relationship("Error", backref=backref("protocol"), cascade="all, delete-orphan", order_by="Error.id")

128
    def __init__(self, protocoltype_id, date, source=None, content_public=None, content_private=None, start_time=None, end_time=None, author=None, participants=None, location=None, done=False):
129
130
131
        self.protocoltype_id = protocoltype_id
        self.date = date
        self.source = source
132
133
        self.content_private = content_private
        self.content_public = content_public
134
135
136
137
138
        self.start_time = start_time
        self.end_time = end_time
        self.author = author
        self.participants = participants
        self.location = location
Robin Sonnabend's avatar
Robin Sonnabend committed
139
        self.done = done
140
141
142
143
144
145
146
147
148
149

    def __repr__(self):
        return "<Protocol(id={}, protocoltype_id={})>".format(
            self.id, self.protocoltype_id)

    def create_error(self, action, name, description):
        now = datetime.now()
        return Error(self.id, action, name, now, description)

    def fill_from_remarks(self, remarks):
Robin Sonnabend's avatar
Robin Sonnabend committed
150
        new_date = datetime.strptime(remarks["Datum"].value.strip(), "%d.%m.%Y").date()
Robin Sonnabend's avatar
Robin Sonnabend committed
151
152
153
154
155
        if self.date is not None:
            if new_date != self.date:
                raise DateNotMatchingException(original_date=self.date, protocol_date=new_date)
        else:
            self.date = new_date
Robin Sonnabend's avatar
Robin Sonnabend committed
156
157
158
159
160
        self.start_time = datetime.strptime(remarks["Beginn"].value.strip(), "%H:%M").time()
        self.end_time = datetime.strptime(remarks["Ende"].value.strip(), "%H:%M").time()
        self.author = remarks["Autor"].value.strip()
        self.participants = remarks["Anwesende"].value.strip()
        self.location = remarks["Ort"].value.strip()
161

Robin Sonnabend's avatar
Robin Sonnabend committed
162
163
164
    def is_done(self):
        return self.done

Robin Sonnabend's avatar
Robin Sonnabend committed
165
    def get_identifier(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
166
167
        if self.date is None:
            return None
Robin Sonnabend's avatar
Robin Sonnabend committed
168
169
170
171
        return "{}-{}".format(
            self.protocoltype.short_name.lower(),
            self.date.strftime("%y-%m-%d"))

Robin Sonnabend's avatar
Robin Sonnabend committed
172
173
174
    def get_wiki_title(self):
        return "Protokoll:{}-{:%Y-%m-%d}".format(self.protocoltype.short_name, self.date)

Robin Sonnabend's avatar
Robin Sonnabend committed
175
    def get_etherpad_link(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
176
177
178
        identifier = self.get_identifier()
        if identifier is None:
            return ""
Robin Sonnabend's avatar
Robin Sonnabend committed
179
        return get_etherpad_url(self.get_identifier())
Robin Sonnabend's avatar
Robin Sonnabend committed
180
181
182
183
184
185

    def has_nonplanned_tops(self):
        return len([top for top in self.tops if not top.planned]) > 0

    def get_originating_todos(self):
        return [todo for todo in self.todos if self == todo.get_first_protocol()]
186

Robin Sonnabend's avatar
Robin Sonnabend committed
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
    def has_compiled_document(self):
        candidates = [
            document for document in self.documents
            if document.is_compiled
        ]
        return len(candidates) > 0

    def get_compiled_document(self, private=None):
        candidates = [
            document for document in self.documents
            if document.is_compiled
               and (private is None or document.is_private == private) 
        ]
        private_candidates = [document for document in candidates if document.is_private]
        public_candidates = [document for document in candidates if not document.is_private]
        if len(private_candidates) > 0:
            return private_candidates[0]
        elif len(public_candidates) > 0:
            return public_candidates[0]
        return None

Robin Sonnabend's avatar
Robin Sonnabend committed
208
209
210
    def get_template(self):
        return render_template("protocol-template.txt", protocol=self)

Robin Sonnabend's avatar
Robin Sonnabend committed
211
212
213
    def delete_orphan_todos(self):
        orphan_todos = [
            todo for todo in self.todos
Robin Sonnabend's avatar
Robin Sonnabend committed
214
            if len(todo.protocols) <= 1
Robin Sonnabend's avatar
Robin Sonnabend committed
215
216
217
218
219
220
221
222
223
224
        ]
        for todo in orphan_todos:
            self.todos.remove(todo)
            db.session.delete(todo)

@event.listens_for(Protocol, "before_delete")
def on_protocol_delete(mapper, connection, protocol):
    protocol.delete_orphan_todos()


225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
class DefaultTOP(db.Model):
    __tablename__ = "defaulttops"
    id = db.Column(db.Integer, primary_key=True)
    protocoltype_id = db.Column(db.Integer, db.ForeignKey("protocoltypes.id"))
    name = db.Column(db.String)
    number = db.Column(db.Integer)

    def __init__(self, protocoltype_id, name, number):
        self.protocoltype_id = protocoltype_id
        self.name = name
        self.number = number

    def __repr__(self):
        return "<DefaultTOP(id={}, protocoltype_id={}, name={}, number={})>".format(
            self.id, self.protocoltype_id, self.name, self.number)

    def is_at_end(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
242
        return self.number > 0
243
244
245
246
247
248

class TOP(db.Model):
    __tablename__ = "tops"
    id = db.Column(db.Integer, primary_key=True)
    protocol_id = db.Column(db.Integer, db.ForeignKey("protocols.id"))
    name = db.Column(db.String)
Robin Sonnabend's avatar
Robin Sonnabend committed
249
    number = db.Column(db.Integer)
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
    planned = db.Column(db.Boolean)

    def __init__(self, protocol_id, name, number, planned):
        self.protocol_id = protocol_id
        self.name = name
        self.number = number
        self.planned = planned

    def __repr__(self):
        return "<TOP(id={}, protocol_id={}, name={}, number={}, planned={})>".format(
            self.id, self.protocol_id, self.name, self.number, self.planned)

class Document(db.Model):
    __tablename__ = "documents"
    id = db.Column(db.Integer, primary_key=True)
    protocol_id = db.Column(db.Integer, db.ForeignKey("protocols.id"))
    name = db.Column(db.String)
Robin Sonnabend's avatar
Robin Sonnabend committed
267
    filename = db.Column(db.String)
268
    is_compiled = db.Column(db.Boolean)
269
    is_private = db.Column(db.Boolean)
270

271
    def __init__(self, protocol_id, name, filename, is_compiled, is_private):
272
273
274
275
        self.protocol_id = protocol_id
        self.name = name
        self.filename = filename
        self.is_compiled = is_compiled
276
        self.is_private = is_private
277
278

    def __repr__(self):
279
280
281
282
283
284
        return "<Document(id={}, protocol_id={}, name={}, filename={}, is_compiled={}, is_private={})>".format(
            self.id, self.protocol_id, self.name, self.filename, self.is_compiled, self.is_private)

    def get_filename(self):
        return os.path.join(config.DOCUMENTS_PATH, self.filename)

285
286
287
288
    def as_file_like(self):
        with open(self.get_filename(), "rb") as file:
            return BytesIO(file.read())

289
@event.listens_for(Document, "before_delete")
Robin Sonnabend's avatar
Robin Sonnabend committed
290
def on_document_delete(mapper, connection, document):
291
292
293
294
    if document.filename is not None:
        document_path = os.path.join(config.DOCUMENTS_PATH, document.filename)
        if os.path.isfile(document_path):
            os.remove(document_path)
295
296
297
298

class Todo(db.Model):
    __tablename__ = "todos"
    id = db.Column(db.Integer, primary_key=True)
Robin Sonnabend's avatar
Robin Sonnabend committed
299
    protocoltype_id = db.Column(db.Integer, db.ForeignKey("protocoltypes.id"))
Robin Sonnabend's avatar
Robin Sonnabend committed
300
    number = db.Column(db.Integer)
301
302
303
304
305
306
307
    who = db.Column(db.String)
    description = db.Column(db.String)
    tags = db.Column(db.String)
    done = db.Column(db.Boolean)

    protocols = relationship("Protocol", secondary="todoprotocolassociations", backref="todos")

Robin Sonnabend's avatar
Robin Sonnabend committed
308
309
    def __init__(self, type_id, who, description, tags, done, number=None):
        self.protocoltype_id = type_id
310
311
312
313
        self.who = who
        self.description = description
        self.tags = tags
        self.done = done
Robin Sonnabend's avatar
Robin Sonnabend committed
314
        self.number = number
315
316

    def __repr__(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
317
318
319
320
321
        return "<Todo(id={}, number={}, who={}, description={}, tags={}, done={})>".format(
            self.id, self.number, self.who, self.description, self.tags, self.done)

    def get_id(self):
        return self.number if self.number is not None else self.id
322

Robin Sonnabend's avatar
Robin Sonnabend committed
323
324
325
326
327
328
329
330
    def get_first_protocol(self):
        candidates = sorted(self.protocols, key=lambda p: p.date)
        if len(candidates) == 0:
            return None
        return candidates[0]

    def get_state(self):
        return "[Erledigt]" if self.done else "[Offen]"
Robin Sonnabend's avatar
Robin Sonnabend committed
331
    def get_state_plain(self):
332
        return "Erledigt" if self.done else "Aktiv"
Robin Sonnabend's avatar
Robin Sonnabend committed
333
334
335
336
337
338
339
    def get_state_tex(self):
        return self.get_state_plain()

    def is_new(self, current_protocol=None):
        if current_protocol is not None:
            return self.get_first_protocol() == current_protocol
        return len(self.protocols) == 1
Robin Sonnabend's avatar
Robin Sonnabend committed
340
341
342
343
344
345
346
347
348

    def render_html(self):
        parts = [
            self.get_state(),
            "<strong>{}:</strong>".format(self.who),
            self.description
        ]
        return " ".join(parts)

Robin Sonnabend's avatar
Robin Sonnabend committed
349
350
    def render_latex(self, current_protocol=None):
        return r"\textbf{{{}}}: {}: {} -- {}".format(
Robin Sonnabend's avatar
Robin Sonnabend committed
351
            "Neuer Todo" if self.is_new(current_protocol) else "Todo",
Robin Sonnabend's avatar
Robin Sonnabend committed
352
353
354
355
356
            self.who,
            self.description,
            self.get_state_tex()
        )

Robin Sonnabend's avatar
Robin Sonnabend committed
357
358
359
360
361
362
363
364
    def render_wikitext(self, current_protocol=None):
        return "'''{}:''' {}: {} - {}".format(
            "Neuer Todo" if self.is_new(current_protocol) else "Todo",
            self.who,
            self.description,
            self.get_state_plain()
        )

Robin Sonnabend's avatar
Robin Sonnabend committed
365

Robin Sonnabend's avatar
Robin Sonnabend committed
366

367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
class TodoProtocolAssociation(db.Model):
    __tablename__ = "todoprotocolassociations"
    todo_id = db.Column(db.Integer, db.ForeignKey("todos.id"), primary_key=True)
    protocol_id = db.Column(db.Integer, db.ForeignKey("protocols.id"), primary_key=True)

class Decision(db.Model):
    __tablename__ = "decisions"
    id = db.Column(db.Integer, primary_key=True)
    protocol_id = db.Column(db.Integer, db.ForeignKey("protocols.id"))
    content = db.Column(db.String)

    def __init__(self, protocol_id, content):
        self.protocol_id = protocol_id
        self.content = content

    def __repr__(self):
        return "<Decision(id={}, protocol_id={}, content='{}')>".format(
            self.id, self.protocol_id, self.content)

class MeetingReminder(db.Model):
    __tablename__ = "meetingreminders"
    id = db.Column(db.Integer, primary_key=True)
    protocoltype_id = db.Column(db.Integer, db.ForeignKey("protocoltypes.id"))
390
    days_before = db.Column(db.Integer)
391
392
    send_public = db.Column(db.Boolean)
    send_private = db.Column(db.Boolean)
Robin Sonnabend's avatar
Robin Sonnabend committed
393
    additional_text = db.Column(db.String)
394

Robin Sonnabend's avatar
Robin Sonnabend committed
395
    def __init__(self, protocoltype_id, days_before, send_public, send_private, additional_text):
396
        self.protocoltype_id = protocoltype_id
397
        self.days_before = days_before
398
399
        self.send_public = send_public
        self.send_private = send_private
Robin Sonnabend's avatar
Robin Sonnabend committed
400
        self.additional_text = additional_text
401
402

    def __repr__(self):
403
404
        return "<MeetingReminder(id={}, protocoltype_id={}, days_before={}, send_public={}, send_private={})>".format(
            self.id, self.protocoltype_id, self.days_before, self.send_public, self.send_private)
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424

class Error(db.Model):
    __tablename__ = "errors"
    id = db.Column(db.Integer, primary_key=True)
    protocol_id = db.Column(db.Integer, db.ForeignKey("protocols.id"))
    action = db.Column(db.String)
    name = db.Column(db.String)
    datetime = db.Column(db.DateTime)
    description = db.Column(db.String)

    def __init__(self, protocol_id, action, name, datetime, description):
        self.protocol_id = protocol_id
        self.action = action
        self.name = name
        self.datetime = datetime
        self.description = description

    def __repr__(self):
        return "<Error(id={}, protocol_id={}, action={}, name={}, datetime={})>".format(
            self.id, self.protocol_id, self.action, self.name, self.datetime)