class RssSettings(Module):
table = "pim.rss_feeds"
columns = [
Column({
'id': "rss_feed_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Název účtu",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"URL",
'id': "rss_url",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
})
]
class FolderTree:
columns = [
Column({
'id': "folder_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Název",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'id': "subfolders",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
})
]
def __init__(self, session):
self.session = session
def getData(self, query={}, limit=None):
imapHandler = Imap(self.session['user_id'], query['accountId'])
if query and 'folder_id' in query:
folders = imapHandler.getFolderTree(query['folder_id'])
else:
folders = imapHandler.getFolderTree()
imapHandler.close()
return {'data': folders}
def getColumns(self):
cols = {}
for col in self.columns:
colId = col.getId()
cols[colId] = col.getInfo()
return cols
class Tasks(Module):
table = "pim.tasks"
columns = [
Column({
'id': "object_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Štítky",
'id': "labels",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit"],
'required': True,
'type': types.Label()
}),
Column({
'name': u"Vytvořeno",
'id': "created",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static"],
'required': False,
'type': types.Date()
}),
Column({
'name': u"Úkol",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Dokončeno",
'id': "finished",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Boolean(),
'editable': True,
'insertable': True
})
]
class VsbTimetable:
columns = [
Column({
'id': "id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Název předmětu",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.String(),
}),
]
def __init__(self, session):
self.session = session
def getData(self, query):
data = None
if 'code' in query and query['code']:
data = vsbParser.getLessons(query['code'].replace("*", "").encode("utf8"))
elif 'name' in query and query['name']:
data = vsbParser.getLessonsList(query['name'].replace("*", "").encode("utf8"))
return {
'data': data
}
def getColumns(self):
cols = {}
for col in self.columns:
colId = col.getId()
cols[colId] = col.getInfo()
return cols
class SmtpSettings(Module):
table = "pim.smtp_accounts"
columns = [
Column({
'id': "smtp_account_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Název účtu",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Login",
'id': "login",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Heslo",
'id': "password",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Password(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Host",
'id': "host",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Port",
'id': "port",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Integer(),
'editable': True,
'insertable': True,
'default': 443
}),
Column({
'name': u"SSL",
'id': "ssl",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Boolean(),
'editable': True,
'insertable': True
})
]
class Users(Module):
table = "pim.users"
columns = [
Column({
'name': "user_id",
'id': "user_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.Integer(),
'editable': False,
'insertable': False,
'flags': ['primaryKey']
}),
Column({
'name': "login",
'id': "login",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.String(),
'editable': False,
'insertable': True
}),
Column({
'name': "password",
'id': "password",
#'validator': IntervalValidator(
#(100, 200),
#(500, 600)
#),
'visibility': ["edit"],
'required': False,
'type': types.Password(),
'editable': True,
'insertable': True,
'value': "***"
}),
Column({
'name': u"Jméno",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': "email",
'id': "email",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
]
adminColumns = [
Column({
'name': "role",
'id': "role",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.UserRole(),
'editable': True,
'insertable': True
}),
Column({
'name': "active",
'id': "active",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.Boolean(),
'editable': False,
'insertable': False
})
]
def __init__(self, session):
self.session = session
if self.session['user_role'] == "admin":
self.columns += self.adminColumns
def getData(self, query = {}):
result = SQLReport("SELECT * FROM pim.users %(where)s %(order)s %(limit)s")
result.setColumns(self.columns)
if self.session['user_role'] != "admin":
result.setFilter({
'user_id': self.session['user_id']
})
else:
result.setFilter(query)
return result.getReport()
def update(self, key, data):
if 'password' in data:
if data['password'] == "***":
del data['password']
else:
data['password'] = md5(data['password']).hexdigest()
if 'role' in data and self.session['user_role'] != "admin":
del data['role']
result = SQLUpdate(self.table)
result.setColumns(self.columns)
result.setKey(key)
result.setData(data)
#test
result.update()
return {}
def insert(self, data):
if type(data) != list:
data = [data]
print "ROLE", self.session['user_role']
if self.session['user_role'] != "admin":
return {
'status': "ERROR",
'error': "Nejste root"
};
pKeys = []
print "INC DATA", data
for i in data:
if 'password' in i:
i['password'] = md5(i['password']).hexdigest()
result = SQLInsert(self.table)
result.setColumns(self.columns)
result.setData(i)
pKeys.append(result.insert())
#conn.commit()
#conn.close()
print "PKEYS", pKeys
return pKeys
class Notes(Module):
table = "pim.notes"
columns = [
Column({
'id': "object_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Štítky",
'id': "labels",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit"],
'required': True,
'type': types.Label()
}),
Column({
'name': u"Vytvořeno",
'id': "created",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static"],
'required': False,
'type': types.Date()
}),
Column({
'name': u"Název",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Poznámka",
'id': "note",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.HTML(),
'editable': True,
'insertable': True
})
]
def getData(self, query = None, limit = None, offset = None):
result = SQLReport("""SELECT N.*,
(SELECT array_agg(label) FROM pim.objects_labels WHERE object_id = N.object_id) AS labels
FROM %s N %s""" % (self.table, "%(where)s %(order)s %(limit)s"))
result.setColumns(self.columns)
result.setFixedFilter("user_id = '%s'" % self.session['user_id'])
result.setFilter(query)
if limit and offset:
result.setLimit(limit, offset)
#result.setOrder({
#"category_id": "DESC"
#})
return result.getReport()
def insert(self, data):
conn = datasources.postgres.SQLTransaction()
labels = None
for i in data:
labels = None
if 'labels' in i:
labels = i['labels']
del i['labels']
result = SQLInsert(self.table, conn)
result.setColumns(self.columns)
result.setData(i)
result.setAdditionalData({
'user_id': self.session['user_id']
})
eventIds = result.insert()
print "EVTID", eventIds
if labels:
#for eId in eventIds:
cursor = conn.cursor()
for label in labels:
cursor.execute("SELECT pim.assign_label(%s, '%s', %s)" % (
eventIds['object_id'], label, self.session['user_id']
))
conn.commit()
conn.close()
return {}
def update(self, key, data):
conn = datasources.postgres.SQLTransaction()
labels = None
labels = None
if 'labels' in data:
labels = data['labels']
del data['labels']
result = SQLUpdate(self.table, conn)
result.setColumns(self.columns)
result.setData(data)
result.setKey(key)
#result.setAdditionalData({
#'user_id': self.session['user_id']
#})
eventIds = result.update()
print "EVTID", eventIds
if labels:
cursor.execute("DELETE FROM pim.objects_labels WHERE object_id = %s", [key['object_id']])
cursor = conn.cursor()
for label in labels:
cursor.execute("SELECT pim.assign_label(%s, '%s', %s)" % (
eventIds['object_id'], label, self.session['user_id']
))
conn.commit()
conn.close()
return {}
class MailMessage:
columns = [
Column({
'id': "mail_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Předmět",
'id': "subject",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'id': "sender",
'name': "Odesílatel",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'id': "date",
'name': "Datum",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Date(),
'editable': True,
'insertable': True
}),
Column({
'id': "html",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.HTML(),
'editable': True,
'insertable': True
}),
Column({
'id': "message",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.EmailMessage(),
'editable': True,
'insertable': True
})
]
def __init__(self, session):
self.session = session
def getData(self, query = {}, limit = None):
imapHandler = Imap(self.session['user_id'], query['accountId'])
if not 'folder_id' in query:
query['folder_id'] = "INBOX"
if query and 'mail_id' in query:
mails = [imapHandler.getMail(query['mail_id'], query['folder_id'])]
else:
mails = []
imapHandler.close()
return {
'data': mails
}
def getColumns(self):
cols = {}
for col in self.columns:
colId = col.getId()
cols[colId] = col.getInfo()
return cols
def sendMail(self, accountId, subject, recipients, message):
conn = SQLTransaction()
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("""
SELECT * FROM pim.smtp_accounts WHERE
smtp_account_id = %s AND user_id = %s""", (accountId, self.session['user_id']))
settings = cursor.fetchone()
conn.close()
smtpConn = smtplib.SMTP(settings['host'])
if settings['login']:
smtpConn.login(settings['login'], settings['password'])
to = ", ".join(recipients)
msg = MIMEMultipart()
msg.set_charset("UTF-8")
msg['From'] = settings['name']
msg['To'] = to
msg['Subject'] = subject
htmlPart = MIMEText(message.encode("utf8"), 'html')
msg.attach(htmlPart)
smtpConn.sendmail(settings['name'], to, msg.as_string())
smtpConn.quit()
return {
'status': "OK"
}
class Events(Module):
table = "pim.events"
columns = [
Column({
'id': "object_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.Integer(),
'editable': False,
'insertable': False,
'flags': ['primaryKey']
}),
Column({
'name': u"Štítky",
'id': "labels",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.Label(),
'editable': True,
'insertable': True,
'searchAlias': ""
}),
Column({
'name': u"Vytvořeno",
'id': "created",
#'validator': IntervalValidator(
#(100, 200),
#(500, 600)
#),
'visibility': ["static"],
'required': False,
'type': types.Date(),
'editable': False,
'insertable': False
}),
Column({
'name': u"Začátek",
'id': "task_start",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.DateTime(),
'editable': True,
'insertable': True,
'searchAlias': "task_start::date <= %s::date + INTERVAL '1 day'"
}),
Column({
'name': u"Konec",
'id': "task_end",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.DateTime(),
'editable': True,
'insertable': True,
'searchAlias': "task_end::date >= %s::date + INTERVAL '1 day'"
}),
Column({
'name': u"Název",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Popis",
'id': "description",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.HTML(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Dokončeno",
'id': "finished",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Boolean(),
'editable': True,
'insertable': True
}),
Column({
'name': u"Opakování",
'id': "recurrence",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Integer(),
'editable': True,
'insertable': True,
'default': 0
}),
Column({
'name': u"Interval opakování",
'id': "recurrence_interval",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Integer(),
'editable': True,
'insertable': True
})
]
def getData(self, query, limit, offset):
print "QUERY", query
dateQuery = None
if query and 'date_range' in query and type(
query['date_range']) == list and len(query['date_range']) > 1:
dateQuery = "task_start::date >= '%s'::date + INTERVAL '1 day' AND task_end::date <= '%s'::date + INTERVAL '1 day'" % (
query['date_range'][0], query['date_range'][1])
del query['date_range']
result = SQLReport(
"""SELECT * FROM pim.events %(where)s %(order)s %(limit)s""")
result.setColumns(self.columns)
result.setFixedFilter("user_id = '%s'" % self.session['user_id'])
if query and 'labels' in query:
if type(query['labels']) == list:
result.addFilter("""
object_id IN (
SELECT object_id FROM pim.objects_labels
WHERE label IN ('%s')
)
""" % "', '".join(query['labels']))
del query['labels']
result.setFilter(query)
if dateQuery:
result.addFilter(dateQuery)
#result.addFilter("user_id = '%s'" % self.session['user_id'])
#result.setLimit(0, 100)
#result.setOrder({
#"category_id": "DESC"
#})
return result.getReport()
def insert(self, data):
conn = datasources.postgres.SQLTransaction()
labels = None
for i in data:
labels = None
if 'labels' in i:
labels = i['labels']
del i['labels']
result = SQLInsert(self.table, conn)
result.setColumns(self.columns)
result.setData(i)
result.setAdditionalData({'user_id': self.session['user_id']})
eventIds = result.insert()
print "EVTID", eventIds
if labels:
#for eId in eventIds:
cursor = conn.cursor()
for label in labels:
cursor.execute("SELECT pim.assign_label(%s, '%s', %s)" %
(eventIds['object_id'], label,
self.session['user_id']))
conn.commit()
conn.close()
return {'status': "OK", 'rowId': eventIds['object_id']}
class MailList:
columns = [
Column({
'id': "mail_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': u"Předmět",
'id': "subject",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'id': "sender",
'name': "Odesílatel",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'id': "date",
'name': "Datum",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.Date(),
'editable': True,
'insertable': True
}),
Column({
'id': "folder",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
})
]
def __init__(self, session):
self.session = session
def getData(self, query = {}, limit = None):
imapHandler = Imap(self.session['user_id'], query['accountId'])
if query and 'folder_id' in query:
mails = imapHandler.getMailList(query['folder_id'])
else:
mails = imapHandler.getMailList()
imapHandler.close()
return {
'data': mails,
'columns': self.getColumns()
}
def getColumns(self):
cols = {}
for col in self.columns:
colId = col.getId()
cols[colId] = col.getInfo()
return cols
class Contacts(Module):
table = "pim.contacts"
columns = [
Column({
'id': "contact_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': "name",
'id': "name",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit"],
'required': True,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': "description",
'id': "description",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.HTML(),
'editable': True,
'insertable': True
}),
#Column({
#'name': "params",
#'id': "params",
##'validator': RegexpValidator("^[0-9]$"),
#'visibility': ["static", "edit"],
#'required': False,
#'type': types.ContactParams(),
#'editable': True,
#'insertable': True
#})
]
def getData(self, query={}, limit=0, offset=0):
result = SQLReport(
"SELECT * FROM pim.contacts %(where)s %(order)s %(limit)s")
result.setColumns(self.columns)
result.setFixedFilter("user_id = '%s'" % self.session['user_id'])
result.setFilter(query)
contacts = result.getReport()
#conn = Service.getDbConn()
#cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
#for value in contacts['data']:
#cursor.execute("SELECT * FROM pim.contacts_params WHERE contact_id = %s", [value['contact_id']])
#params = cursor.fetchall()
#value['params'] = params
#Service.putDbConn(conn)
return contacts
class ContactsParams(Module):
table = "pim.contacts_params"
columns = [
Column({
'id': "param_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit", "static"],
'required': False,
'type': types.Integer(),
'flags': ["primaryKey"]
}),
Column({
'name': "contact",
'id': "contact_id",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["edit"],
'required': True,
'type': types.String(),
'insertable': True
}),
Column({
'name': "param",
'id': "param",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
}),
Column({
'name': "value",
'id': "value",
#'validator': RegexpValidator("^[0-9]$"),
'visibility': ["static", "edit"],
'required': False,
'type': types.String(),
'editable': True,
'insertable': True
})
]
def getData(self, query=None, limit=None, offset=None):
result = SQLReport("SELECT * FROM %s %s" %
(self.table, "%(where)s %(order)s %(limit)s"))
result.setColumns(self.columns)
result.setFilter(query)
if limit and offset:
result.setLimit(limit, offset)
return result.getReport()
def insert(self, data):
if type(data) != list:
data = [data]
#conn = datasources.postgres.SQLTransaction()
pKeys = []
for i in data:
result = SQLInsert(self.table)
result.setColumns(self.columns)
result.setData(i)
#result.setAdditionalData({
#'contact_id': i['contact_id']
#})
pKeys.append(result.insert())
return pKeys