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 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 Feed: columns = [ Column({ 'name': "Titulek", 'id': "title", #'validator': RegexpValidator("^[0-9]$"), 'visibility': ["edit", "static"], 'required': False, 'type': types.String(), }), Column({ 'name': "Odkaz", 'id': "link", #'validator': RegexpValidator("^[0-9]$"), 'visibility': ["edit", "static"], 'required': False, 'type': types.String(), }), Column({ 'name': "Text", 'id': "description", #'validator': RegexpValidator("^[0-9]$"), 'visibility': ["edit", "static"], 'required': False, 'type': types.HTML(), }), Column({ 'name': "Datum", 'id': "date", #'validator': RegexpValidator("^[0-9]$"), 'visibility': ["edit", "static"], 'required': False, 'type': types.Date(), }) ] def __init__(self, session): self.session = session def __default__(self): return "" def getColumns(self): cols = {} for col in self.columns: colId = col.getId() cols[colId] = col.getInfo() return cols def getData(self, query): rows = [] qry = "SELECT * FROM pim.rss_feeds WHERE user_id = %s" args = [self.session['user_id']] feedId = None if 'feed_id' in query: qry += " AND rss_feed_id = %s" args = [self.session['user_id'], query['feed_id']] print "QRY", qry, "ARGS:", args conn = Service.getDbConn() cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor) cursor.execute(qry, args) for feed in cursor.fetchall(): rows += self.getFeedFromURL(feed['rss_url']) Service.putDbConn(conn) #print feed pp = pprint.PrettyPrinter(indent=4) pp.pprint(feed) return { 'data': rows, 'columns': self.getColumns() } def getFeedFromURL(self, url): rows = [] feed = feedparser.parse(url) for entry in feed['entries']: rows.append({ 'title': entry['title'], 'link': entry['link'], 'description': entry['summary'], 'date': entry['updated'], }) return rows