def send(self, message):
conn = SQLTransaction()
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("SELECT * FROM pim.users WHERE user_id = %s",
[self.session['user_id']])
user = cursor.fetchone()
conn.close()
smtpConn = smtplib.SMTP("localhost")
msg = MIMEMultipart()
msg.set_charset("UTF-8")
msg['From'] = self.sender
msg['To'] = self.admin
msg['Subject'] = u"Návrh na zlepšení PIM"
htmlPart = MIMEText(message.encode("utf8"), 'html')
msg.attach(htmlPart)
smtpConn.sendmail("PIM", self.admin, msg.as_string())
smtpConn.quit()
return {"status": "OK"}
def send(self, message):
conn = SQLTransaction()
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("SELECT * FROM pim.users WHERE user_id = %s", [self.session["user_id"]])
user = cursor.fetchone()
conn.close()
smtpConn = smtplib.SMTP("localhost")
msg = MIMEMultipart()
msg.set_charset("UTF-8")
msg["From"] = self.sender
msg["To"] = self.admin
msg["Subject"] = u"Návrh na zlepšení PIM"
htmlPart = MIMEText(message.encode("utf8"), "html")
msg.attach(htmlPart)
smtpConn.sendmail("PIM", self.admin, msg.as_string())
smtpConn.quit()
return {"status": "OK"}
def __init__(self, table, connection = None): self.table = table if connection: self.sqlConn = connection self.useTransaction = True else: self.sqlConn = SQLTransaction()
def __init__(self, userId, accountId):
conn = SQLTransaction()
cursor = conn.cursor(cursor_factory=psycopg2.extras.DictCursor)
cursor.execute("""
SELECT * FROM pim.imap_accounts WHERE
imap_account_id = %s AND user_id = %s""", (accountId, userId))
settings = cursor.fetchone()
conn.close()
if settings['ssl'] == True:
self.imapHandle = imaplib.IMAP4_SSL(settings['host'], settings['port'])
else:
self.imapHandle = imaplib.IMAP4(settings['host'], settings['port'])
if settings['login']:
self.login(settings['login'], settings['password'])
self.attachments = {}
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 SQLDelete(ColumnParser):
table = None
query = "DELETE FROM %(table)s %(where)s"
sqlConn = None
useTransaction = False
def __init__(self, table, connection = None):
self.table = table
if connection:
self.sqlConn = connection
self.useTransaction = True
else:
self.sqlConn = SQLTransaction()
#def setFilter(self, args, join = "AND"):
#filters = []
#for item in args:
#if item in self.searchAliases:
#filters.append(self.searchAliases[item] % args[item])
#else:
#filters.append("%s = '%s'" % (item, args[item]))
#self.where = join.join(filters)
def delete(self):
qry = self.__parseQuery()
cursor = self.sqlConn.cursor()
cursor.execute(qry)
if not self.useTransaction:
self.sqlConn.commit()
self.sqlConn.close()
return True
def __parseQuery(self):
query = self.query % {
'table': self.table,
'where': "WHERE " + " OR ".join(self.filter)
}
return query
def __del__(self):
if self.useTransaction == False:
self.sqlConn.close()
def setFilter(self, query, join = "AND"):
if not query or type(query) not in [dict, list]:
return
if type(query) == dict:
query = [query]
self.filter = []
join = " %s " % join
cursor = self.sqlConn.cursor()
for args in query:
outer = []
args = self.decodeValue(args)
for item in args:
col = self.columns[self.columnsIndex[item]]
if col.searchAlias:
outer.append(cursor.mogrify(col.searchAlias, [args[item]]))
else:
if type(args[item]) in [str, unicode] and args[item][-1:] == "*":
#USE ILIKE OPERATOR
outer.append("%s ILIKE '%s%%'" % (item, args[item][0:-1]))
else:
outer.append("%s = '%s'" % (item, args[item]))
self.filter.append(join.join(outer))
class SQLInsert(ColumnParser):
#table = None
query = "INSERT INTO %(table)s (%(columns)s) VALUES (%(values)s)"
#sqlConn = None
#useTransaction = False
def __init__(self, table, connection = None):
self.table = table
self.addData = {}
if connection:
self.sqlConn = connection
self.useTransaction = True
else:
self.sqlConn = SQLTransaction()
self.useTransaction = False
def insert(self):
qry = self.__parseQuery()
self.data.update(self.addData)
cursor = self.sqlConn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
cursor.execute(qry, self.data)
returns = []
for row in cursor.fetchall():
returns.append(row)
if not self.useTransaction:
self.sqlConn.commit()
#self.sqlConn.close()
return returns[0]
def __parseQuery(self):
data = []
keys = []
returning = ""
if len(self.primaryKeys) > 0:
returning = " RETURNING " + ",".join(self.primaryKeys)
for key in self.data:
if key not in self.columnsInsertable:
continue
data.append("%%(%s)s" % key)
keys.append(key)
for key in self.addData:
data.append("%%(%s)s" % key)
keys.append(key)
#keys = set(keys)
#data = set(data)
query = self.query % {
'table': self.table,
'columns': ", ".join(keys),
'values': ", ".join(data)
}
print "QRY", query + returning
return query + returning
def __del__(self):
if self.useTransaction == False:
self.sqlConn.close()
class SQLUpdate(ColumnParser):
table = None
key = None
query = "UPDATE %(table)s SET %(setter)s %(where)s"
sqlConn = None
useTransaction = False
def __init__(self, table, connection = None):
self.table = table
if connection:
self.sqlConn = connection
self.useTransaction = True
else:
self.sqlConn = SQLTransaction()
def __del__(self):
if self.useTransaction == False:
self.sqlConn.close()
def setKey(self, key):
self.key = key
def update(self):
qry = self.__parseQuery()
cursor = self.sqlConn.cursor()
cursor.execute(qry)
if not self.useTransaction:
self.sqlConn.commit()
self.sqlConn.close()
return True
def __parseQuery(self):
cursor = self.sqlConn.cursor()
setterArray = []
for key in self.data:
#if type(self.data[key]) in [str, unicode]:
#self.data[key] = self.data[key].encode("utf8")
setterArray.append(cursor.mogrify("%s = %s" % (key.encode("utf8"), "%s"), [self.data[key]]))
setter = ", ".join(setterArray)
where = ""
if self.key:
if type(self.key) == dict:
keyArray = []
for i in self.key:
keyArray.append(cursor.mogrify("%s = %s" % (i.encode("utf8"), "%s"), [self.key[i]]))
key = " AND ".join(keyArray)
#elif type(key) == list:
#TODO!!!
where = " WHERE %s" % key
query = self.query % {
'table': self.table,
'setter': setter,
'where': where
}
print "PARSEED QRY", query
return query
