database.py 15.5 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
5
6
import math

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

10
import os
11

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

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

18
19
20
21
22
23
24
25
26
27
28
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)
    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
29
30
31
    use_wiki = db.Column(db.Boolean)
    wiki_category = db.Column(db.String)
    wiki_only_public = db.Column(db.Boolean)
32
33
34

    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")
35
    reminders = relationship("MeetingReminder", backref=backref("protocoltype"), cascade="all, delete-orphan", order_by="MeetingReminder.days_before")
Robin Sonnabend's avatar
Robin Sonnabend committed
36
    todos = relationship("Todo", backref=backref("protocoltype"), order_by="Todo.id")
37
38

    def __init__(self, name, short_name, organization,
Robin Sonnabend's avatar
Robin Sonnabend committed
39
40
            is_public, private_group, public_group, private_mail, public_mail,
            use_wiki, wiki_category, wiki_only_public):
41
42
43
44
45
46
47
48
        self.name = name
        self.short_name = short_name
        self.organization = organization
        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
49
50
51
        self.use_wiki = use_wiki
        self.wiki_category = wiki_category
        self.wiki_only_public = wiki_only_public
52
53

    def __repr__(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
54
55
56
57
58
59
60
61
        return ("<ProtocolType(id={}, short_name={}, name={}, "
                "organization={}, is_public={}, private_group={}, "
                "public_group={}, use_wiki={}, wiki_category='{}', "
                "wiki_only_public={})>".format(
            self.id, self.short_name, self.name,
            self.organization, self.is_public, self.private_group,
            self.public_group, self.use_wiki, self.wiki_category,
            self.wiki_only_public))
Robin Sonnabend's avatar
Robin Sonnabend committed
62
63

    def get_latest_protocol(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
64
        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
65
66
67
68
        if len(candidates) == 0:
            return None
        return candidates[0]

Robin Sonnabend's avatar
Robin Sonnabend committed
69
    @hybrid_method
Robin Sonnabend's avatar
Robin Sonnabend committed
70
71
72
73
74
75
76
    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
77
        return (user is not None and self.private_group != "" and self.private_group in user.groups)
Robin Sonnabend's avatar
Robin Sonnabend committed
78
79
80
81

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

Robin Sonnabend's avatar
Robin Sonnabend committed
82
    @staticmethod
Robin Sonnabend's avatar
Robin Sonnabend committed
83
    def get_modifiable_protocoltypes(user):
Robin Sonnabend's avatar
Robin Sonnabend committed
84
85
86
87
88
        return [
            protocoltype for protocoltype in ProtocolType.query.all()
            if protocoltype.has_modify_right(user)
        ]

Robin Sonnabend's avatar
Robin Sonnabend committed
89
90
91
92
93
94
95
96
97
98
99
100
101
102
    @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)
        ]

Robin Sonnabend's avatar
Robin Sonnabend committed
103

104
105
106
107
108

class Protocol(db.Model):
    __tablename__ = "protocols"
    id = db.Column(db.Integer, primary_key=True)
    protocoltype_id = db.Column(db.Integer, db.ForeignKey("protocoltypes.id"))
109
110
111
    source = db.Column(db.String)
    content_public = db.Column(db.String)
    content_private = db.Column(db.String)
112
113
114
115
116
117
    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
118
    done = db.Column(db.Boolean)
119
120
121
122
123
124

    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")

125
    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):
126
127
128
        self.protocoltype_id = protocoltype_id
        self.date = date
        self.source = source
129
130
        self.content_private = content_private
        self.content_public = content_public
131
132
133
134
135
        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
136
        self.done = done
137
138
139
140
141
142
143
144
145
146

    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
147
        new_date = datetime.strptime(remarks["Datum"].value.strip(), "%d.%m.%Y").date()
Robin Sonnabend's avatar
Robin Sonnabend committed
148
149
150
151
152
        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
153
154
155
156
157
        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()
158

Robin Sonnabend's avatar
Robin Sonnabend committed
159
160
161
    def is_done(self):
        return self.done

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

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

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

    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()]
183

Robin Sonnabend's avatar
Robin Sonnabend committed
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
    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
205
206
207
    def get_template(self):
        return render_template("protocol-template.txt", protocol=self)

Robin Sonnabend's avatar
Robin Sonnabend committed
208
209
210
    def delete_orphan_todos(self):
        orphan_todos = [
            todo for todo in self.todos
Robin Sonnabend's avatar
Robin Sonnabend committed
211
            if len(todo.protocols) <= 1
Robin Sonnabend's avatar
Robin Sonnabend committed
212
213
214
215
216
217
218
219
220
221
        ]
        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()


222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
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
239
        return self.number > 0
240
241
242
243
244
245

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
246
    number = db.Column(db.Integer)
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
    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
264
    filename = db.Column(db.String)
265
    is_compiled = db.Column(db.Boolean)
266
    is_private = db.Column(db.Boolean)
267

268
    def __init__(self, protocol_id, name, filename, is_compiled, is_private):
269
270
271
272
        self.protocol_id = protocol_id
        self.name = name
        self.filename = filename
        self.is_compiled = is_compiled
273
        self.is_private = is_private
274
275

    def __repr__(self):
276
277
278
279
280
281
282
        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)

@event.listens_for(Document, "before_delete")
Robin Sonnabend's avatar
Robin Sonnabend committed
283
def on_document_delete(mapper, connection, document):
284
285
286
287
    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)
288
289
290
291

class Todo(db.Model):
    __tablename__ = "todos"
    id = db.Column(db.Integer, primary_key=True)
Robin Sonnabend's avatar
Robin Sonnabend committed
292
    protocoltype_id = db.Column(db.Integer, db.ForeignKey("protocoltypes.id"))
Robin Sonnabend's avatar
Robin Sonnabend committed
293
    number = db.Column(db.Integer)
294
295
296
297
298
299
300
    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
301
302
    def __init__(self, type_id, who, description, tags, done, number=None):
        self.protocoltype_id = type_id
303
304
305
306
        self.who = who
        self.description = description
        self.tags = tags
        self.done = done
Robin Sonnabend's avatar
Robin Sonnabend committed
307
        self.number = number
308
309

    def __repr__(self):
Robin Sonnabend's avatar
Robin Sonnabend committed
310
311
312
313
314
        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
315

Robin Sonnabend's avatar
Robin Sonnabend committed
316
317
318
319
320
321
322
323
    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
324
    def get_state_plain(self):
325
        return "Erledigt" if self.done else "Aktiv"
Robin Sonnabend's avatar
Robin Sonnabend committed
326
327
328
329
330
331
332
    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
333
334
335
336
337
338
339
340
341

    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
342
343
    def render_latex(self, current_protocol=None):
        return r"\textbf{{{}}}: {}: {} -- {}".format(
Robin Sonnabend's avatar
Robin Sonnabend committed
344
            "Neuer Todo" if self.is_new(current_protocol) else "Todo",
Robin Sonnabend's avatar
Robin Sonnabend committed
345
346
347
348
349
            self.who,
            self.description,
            self.get_state_tex()
        )

Robin Sonnabend's avatar
Robin Sonnabend committed
350
351
352
353
354
355
356
357
    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
358

Robin Sonnabend's avatar
Robin Sonnabend committed
359

360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
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"))
383
    days_before = db.Column(db.Integer)
384
385
386
    send_public = db.Column(db.Boolean)
    send_private = db.Column(db.Boolean)

387
    def __init__(self, protocoltype_id, days_before, send_public, send_private):
388
        self.protocoltype_id = protocoltype_id
389
        self.days_before = days_before
390
391
392
393
        self.send_public = send_public
        self.send_private = send_private

    def __repr__(self):
394
395
        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)
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415

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)