54 lines
2.1 KiB
Python
54 lines
2.1 KiB
Python
import config
|
|
import MySQLdb
|
|
|
|
|
|
db = MySQLdb.connect(user=config.MYSQL_USER, passwd=config.MYSQL_PASSWD, db=config.MYSQL_DB)
|
|
def logmsg(msg, to, sentby):
|
|
c = db.cursor()
|
|
try:
|
|
c.execute("""INSERT INTO sendlog (msg, sent_by) VALUES (%s, %s)""", (msg, sentby))
|
|
sendlogid = c.lastrowid
|
|
|
|
for recipient in to:
|
|
c.execute("""INSERT INTO sendlog_to (id, recipient) VALUES (%s, %s)""", (sendlogid, recipient))
|
|
db.commit()
|
|
except:
|
|
print "um error"
|
|
db.rollback()
|
|
|
|
def getloggedmsgs(lastOut):
|
|
c = db.cursor(MySQLdb.cursors.DictCursor)
|
|
c.execute("""SELECT sendlog.*, users.name AS sent_by_name FROM sendlog INNER JOIN users ON sendlog.sent_by = users.uid WHERE sendlog.id > %s ORDER BY id DESC""", (lastOut,))
|
|
rows = c.fetchall()
|
|
for r in rows:
|
|
c.execute("""SELECT people.id, people.name FROM sendlog_to INNER JOIN people ON sendlog_to.recipient = people.id WHERE sendlog_to.id = %s """, (r['id'],))
|
|
r['to'] = c.fetchall()
|
|
db.commit()
|
|
return rows
|
|
|
|
def loginboundmsg(inmsg):
|
|
c = db.cursor()
|
|
try:
|
|
# check if this sender is known to us.
|
|
shortfrom = inmsg['From'][2:] # also check for another form of number where +1 is omitted
|
|
c.execute("""SELECT id FROM people WHERE people.phonenum = %s or people.phonenum = %s LIMIT 1""", (inmsg['From'], shortfrom))
|
|
row = c.fetchone()
|
|
sender_id = -1 # default to this if unknown...
|
|
if row: # found? great, update sender_id.
|
|
sender_id = row[0]
|
|
|
|
c.execute("""INSERT INTO inbound_msgs (`From`, SmsMessageSid, AccountSid, Body, SmsStatus, from_known_person) VALUES (%s, %s, %s, %s, %s, %s)""", (inmsg['From'], inmsg['SmsMessageSid'], inmsg['AccountSid'], inmsg['Body'], inmsg['SmsStatus'], sender_id))
|
|
db.commit()
|
|
except:
|
|
print "um error"
|
|
print c._last_executed
|
|
db.rollback()
|
|
|
|
def getinboundmsgs(lastIn):
|
|
c = db.cursor(MySQLdb.cursors.DictCursor)
|
|
c.execute("""SELECT inbound_msgs.id, inbound_msgs.from, inbound_msgs.body, people.id as pid, people.name FROM `inbound_msgs` LEFT JOIN people ON inbound_msgs.from_known_person = people.id WHERE inbound_msgs.id > %s ORDER BY inbound_msgs.id DESC""", (lastIn,))
|
|
print c._last_executed
|
|
rows = c.fetchall()
|
|
db.commit()
|
|
return rows
|