def emailResourceApproval(email, title):
"""
Email resource owner on approval. Using template: resource_approval
@type email: string
@param email: Email address to send to
...
@rtype: Boolean
@returns: Whether emailer was successful or not.
"""
# Create values for template.
emailAccount = Config.get('email')
subject = "Your resource has been approved"
template_values = {
'link': Config.get('default_host'),
'title': title,
'config': Config.get_all()
}
# Render email body.
body = Emailer.render('email/resource_approval', template_values, suffix = 'txt')
# Send email.
try:
return Emailer.send(email, subject, body, from_name = emailAccount['from_name'],
from_address = emailAccount['from_email'])
except Exception, e:
log.info("*** couldn't send resource approval email")
log.error(e)
return False
def error(self, message, headers={}):
log.error("400: %s" % message)
return self.render('error', {
'error_code': 400,
'error_message': message
},
status='400 %s' % message)
def forbidden(self, data='Forbidden', headers={}):
log.error("403: Forbidden: %s" % data)
return self.render('error', {
'error_code': 403,
'error_message': 'Forbidden.'
},
status='403 Forbidden')
def reply(user, message):
message = clean(message)
try:
message_id = framework.controller.Controller.get_db().insert("messages", user_id=user.id, message=message, sms=1, outgoing=1, status="queued")
except Exception, e:
log.error(e)
def getProjectCounts(db):
data = []
try:
sql = """select p.title,
(select count(pu.user_id) from project__user pu
inner join user u on u.user_id = pu.user_id and u.is_active = 1
where pu.project_id = p.project_id) as num_users,
(select count(pi.idea_id) from project__idea pi
inner join idea i on i.idea_id = pi.idea_id and i.is_active = 1
where pi.project_id = p.project_id) as num_ideas,
(select count(pr.project_resource_id) from project__project_resource pr
inner join project_resource r on r.project_resource_id = pr.project_resource_id and r.is_active = 1
where pr.project_id = p.project_id) as num_resources,
(select count(pe.user_id) from project_endorsement pe
inner join user u on u.user_id = pe.user_id and u.is_active = 1
where pe.project_id = p.project_id) as num_endorsements,
coalesce(p.keywords, '') as keywords
from project p
where p.is_active = 1
order by p.title"""
data = list(db.query(sql))
except Exception, e:
log.info("*** couldn't get project counts")
log.error(e)
def send(phone, message):
log.info("Sending sms...")
message = clean(message)
settings = Config.get('twilio')
account = twilio.Account(settings['sid'], settings['token'])
callback = Config.base_url()
if not callback:
callback = Config.get('default_host')
data = { 'From': settings['phone'],
'To': phone,
'Body': message,
'StatusCallback': "%stwilio/status" % callback
}
log.debug(data)
try:
response = account.request('/%s/Accounts/%s/SMS/Messages.json' % (settings['api'], settings['sid']), 'POST', data)
log.info("--> %s" % response)
response = json.loads(response)
smsid = response['TwilioResponse']['SMSMessage']['Sid']
status = "passed"
except Exception, e:
log.error(e)
smsid = None
status = "blocked"
def getUnreviewedProjectResources(db, limit = 10, offset = 0):
data = []
try:
sql = """select pr.project_resource_id,
pr.title, pr.description,
pr.image_id,
pr.location_id,
pr.url,
pr.twitter_url,
pr.facebook_url,
pr.physical_address,
pr.contact_name,
pr.contact_email,
replace(pr.keywords, ' ', ',') as keywords,
l.name as location_name
from project_resource pr
left join location l on l.location_id = pr.location_id
where pr.is_active = 1 and pr.is_hidden = 1
limit $limit offset $offset"""
data = list(db.query(sql, {'limit':limit, 'offset':offset}))
except Exception, e:
log.info("*** couldn't get unreviewed resources")
log.error(e)
def populateResourceData(self):
sql = """select pr.project_resource_id,
pr.title,
pr.description,
pr.url,
pr.contact_name,
pr.contact_email,
pr.image_id,
pr.location_id,
pr.is_official,
o.user_id as owner_user_id,
o.first_name as owner_first_name,
o.last_name as owner_last_name,
o.email as owner_email
from project_resource pr
left join user o on o.user_id = pr.contact_user_id
where pr.project_resource_id = $id;"""
try:
data = list(self.db.query(sql, {'id':self.id}))
if len(data) > 0:
return data[0]
else:
return None
except Exception, e:
log.info("*** couldn't get project resource info")
log.error(e)
return None
def emailAccountDeactivation(email):
"""
Email deleted users. Using template: account_deactivation
@type email: string
@param email: Email address to send to
...
@rtype: Boolean
@returns: Whether emailer was successful or not.
"""
# Create values for template.
emailAccount = Config.get('email')
subject = "Your account has been deactivated"
link = "%stou" % Config.get('default_host')
template_values = {
'link': link,
'config': Config.get_all()
}
# Render email body.
body = Emailer.render('email/account_deactivation', template_values, suffix = 'txt')
# Send email.
try:
return Emailer.send(email, subject, body, from_name = emailAccount['from_name'],
from_address = emailAccount['from_email'])
except Exception, e:
log.info("*** couldn't send account deactivation email")
log.error(e)
return False
def emailTempPassword(email, password):
"""
Email temporary password. Using template: forgot_password
@type email: string
@param email: Email address to send to
...
@rtype: Boolean
@returns: Whether emailer was successful or not.
"""
# Create values for template.
emailAccount = Config.get('email')
subject = "Your password has been reset"
link = "%slogin" % Config.get('default_host')
link = "%stou" % Config.get('default_host')
template_values = {
'password': password,
'link': link,
'config': Config.get_all()
}
# Render email body.
body = Emailer.render('email/forgot_password', template_values, suffix = 'txt')
# Send email.
try:
return Emailer.send(email, subject, body, from_name = emailAccount['from_name'],
from_address = emailAccount['from_email'])
except Exception, e:
log.info("*** couldn't send forgot password email")
log.error(e)
return False
def getNumNewMessages(self):
"""
"""
num = 0
try:
# Select the number of times that someone invited this user to a
# project based on this user's idea. TODO: is my interpretation
# correct? Why is it selecting when this user is the invitee?
# Select the number of times that this user submitted a message
sql = """select
(select count(inv.project_invite_id) from project_invite inv
inner join idea i on i.idea_id = inv.invitee_idea_id and i.user_id = $userId
where inv.created_datetime > $last) +
(select count(pm.project_message_id) from project_message pm
inner join project__user pu on pu.project_id = pm.project_id and pu.user_id = $userId
where pm.is_active = 1 and pm.created_datetime > $last) as total"""
data = list(self.db.query(sql, {'userId':self.id, 'last':self.data.last_account_page_access_datetime}))
num = data[0].total
except Exception, e:
log.info("*** couldn't get number of new msgs for user id %s" % self.id)
log.error(e)
def getProjectCounts(db):
data = []
try:
sql = """select p.title,
(select count(pu.user_id) from project__user pu
inner join user u on u.user_id = pu.user_id and u.is_active = 1
where pu.project_id = p.project_id) as num_users,
(select count(pi.idea_id) from project__idea pi
inner join idea i on i.idea_id = pi.idea_id and i.is_active = 1
where pi.project_id = p.project_id) as num_ideas,
(select count(pr.project_resource_id) from project__project_resource pr
inner join project_resource r on r.project_resource_id = pr.project_resource_id and r.is_active = 1
where pr.project_id = p.project_id) as num_resources,
(select count(pe.user_id) from project_endorsement pe
inner join user u on u.user_id = pe.user_id and u.is_active = 1
where pe.project_id = p.project_id) as num_endorsements,
coalesce(p.keywords, '') as keywords
from project p
where p.is_active = 1
order by p.title"""
data = list(db.query(sql))
except Exception, e:
log.info("*** couldn't get project counts")
log.error(e)
def populateUserData(self):
sql = """
select u.user_key
,u.email
,u.password
,u.salt
,u.phone
,u.first_name
,u.last_name
,u.image_id
,u.location_id
,l.name as location_name
,u.description
,u.affiliation
,u.group_membership_bitmask
,u.email_notification
,coalesce(u.last_account_page_access_datetime, u.created_datetime) as last_account_page_access_datetime
,pl.title
,pl.organization
from user u
left join location l on l.location_id = u.location_id
left join project_leader pl on pl.user_id = u.user_id
where u.user_id = $id and u.is_active = 1"""
try:
data = list(self.db.query(sql, {'id':self.id}))[0]
if len(data) > 0:
return data
else:
return None
except Exception, e:
log.info("*** couldn't get user info user id %s" % self.id)
log.error(e)
return None
def saveFile(self, filename, data, mirror=True, **kwargs):
"""
Save the data into a file. Return True is file successfully saved,
otherwise False.
Attributes:
filename -- The id from the database record that corresponds to the file
data -- The data (string of bytes) contained in the file
"""
localpath = self.getLocalPath(filename)
localsaved = self.saveTemporaryLocalFile(localpath, data)
if not localsaved:
return False
isS3mirror = self.getConfigVar('media')['isS3mirror']
s3path = self.getS3Path(filename)
log.info("*** config = %s, mirror = %s" % (isS3mirror, mirror))
if (isS3mirror and mirror):
try:
result = S3Uploader.upload(localpath, s3path)
log.info(result)
except Exception, e:
tb = traceback.format_exc()
log.error(tb)
return False
def confirm_pid(run_folder):
"""
TBD
"""
import sys, os, signal, __main__
name = prefix(".", os.path.basename(__main__.__file__))
log.info("Attempting to launch daemon %s..." % name)
pid = str(os.getpid())
pidfile = "%s%s.pid" % (run_folder, name)
if os.path.isfile(pidfile):
old_pid = open(pidfile).read()
log.warning("--> pidfile already exists for %s, attempting to kill process..." % old_pid)
try:
result = os.kill(int(old_pid), signal.SIGKILL)
except OSError, e:
if e.args[0] == 3:
log.warning("--> no process with pid %s" % old_pid)
else:
log.error(e)
exit()
else:
log.info("--> killed process %s" % old_pid)
try:
os.unlink(pidfile)
except OSError, e:
log.error("--> could not remove pidfile, %s" % pidfile)
exit()
def add(cls, db, data, app, max_size=None, grayscale=False, mirror=True, thumb_max_size=None):
log.info("ImageServer.add")
try:
id = db.insert("images", app=app)
except Exception, e:
log.error(e)
return None
def getLocationsWithScoring(db):
data = []
log.info("*** hit locations")
try:
# TODO
# this is temporary until actual scoring is determined
sql = """
select l.location_id,
l.name,
l.lat,
l.lon,
count(distinct p.project_id) as num_projects,
count(distinct i.idea_id) as num_ideas,
count(distinct r.project_resource_id) as num_project_resources
from location l
left join project p on p.location_id = l.location_id and p.is_active=1
left join project__user pu on p.project_id=pu.project_id and pu.is_project_admin = 1 and p.is_active=1
left join idea i on l.location_id = i.location_id and i.is_active=1
left join project_resource r on l.location_id = r.location_id and r.is_active=1 and r.is_hidden=0
where l.location_id > 0
group by l.location_id+l.lat+l.lon
order by l.location_id""";
data = list(db.query(sql))
except Exception, e:
log.info("*** couldn't get locations")
log.error(e)
def emailProjectEndorsement(email, title, leaderName):
"""
Email project admins about endorsements. Using template: project_endorsement
@type email: string
@param email: Email address to send to
...
@rtype: Boolean
@returns: Whether emailer was successful or not.
"""
# Create values for template.
emailAccount = Config.get('email')
subject = "%s liked your project!" % leaderName
template_values = {
'title': title,
'leader_name': leaderName,
'config': Config.get_all()
}
# Render email body.
body = Emailer.render('email/project_endorsement', template_values, suffix = 'txt')
# Send email.
try:
return Emailer.send(email, subject, body, from_name = emailAccount['from_name'],
from_address = emailAccount['from_email'])
except Exception, e:
log.info("*** couldn't send endorsement email")
log.error(e)
return False
def emailUnauthenticatedUser(email, authGuid):
"""
Send unauthenticated user a link to authenticate. Using
template: auth_user
@type email: string
@param email: Email address to send to
@rtype: *
@returns: Emailer send response.
"""
# Create values for template.
emailAccount = Config.get('email')
subject = "Please authenticate your account"
link = "%sjoin/auth/%s" % (Config.get('default_host'), authGuid)
template_values = {
'link': link,
'config': Config.get_all()
}
# Render email body.
body = Emailer.render('email/auth_user', template_values, suffix = 'txt')
# Send email.
try:
return Emailer.send(email, subject, body, from_name = emailAccount['from_name'],
from_address = emailAccount['from_email'])
except Exception, e:
log.info("*** couldn't send authenticate user email")
log.error(e)
return False
def remove(cls, db, app, id):
log.info("ImageServer.remove %s %s" % (app, id))
path = ImageServer.path(app, id)
try:
db.query("DELETE FROM images WHERE id=$id", {"id": id})
os.remove(path)
except Exception, e:
log.error(e)
def removeDbRecord(self, db, id):
try:
db.query("DELETE FROM files WHERE id=$id", {'id': id})
log.warning("--> removed id %s" % id)
return True
except Exception, e:
log.error(e)
return False
def stopSMS(db, phone):
try:
db.insert('sms_stopped_phone', phone = phone)
return True
except Exception, e:
log.info("*** couldn't stop messages to phone number %s. Number may already be in database." % phone)
log.error(e)
return False
def setUserOncallStatus(db, userId, status):
try:
db.update('user', where = "user_id = $userId", is_oncall = status, vars = {'userId':userId})
return True
except Exception, e:
log.info("*** problem setting oncall status to %s for user id %s" % (status, userId))
log.error(e)
return False
def updateProjectResourceImage(db, projectResourceId, imageId):
try:
db.update('project_resource', where = "project_resource_id = $id", image_id = imageId, vars = {'id':projectResourceId})
return True
except Exception, e:
log.info("*** couldn't update project image")
log.error(e)
return False
def approveProjectResource(db, projectResourceId, isOfficial = False):
try:
db.update('project_resource', where = "project_resource_id = $projectResourceId", is_hidden = 0, is_official = isOfficial, vars = {'projectResourceId':projectResourceId})
return True
except Exception, e:
log.info("*** couldn't approve project resource %s" % projectResourceId)
log.error(e)
return False
def findIdeasByPhone(db, phone):
try:
sql = "select idea_id from idea where phone = $phone"
return list(db.query(sql, {'phone':phone}))
except Exception, e:
log.info("*** problem getting ideas by phone")
log.error(e)
return None
def updateProjectResourceLocation(db, projectResourceId, locationId):
try:
db.update('project_resource', where = "project_resource_id = $id", location_id = locationId, vars = {'id':projectResourceId})
return True
except Exception, e:
log.info("*** couldn't update project location")
log.error(e)
return False
def setIdeaIsActive(db, ideaId, b):
try:
sql = "update idea set is_active = $b where idea_id = $ideaId"
db.query(sql, {'ideaId':ideaId, 'b':b})
return True
except Exception, e:
log.info("*** problem setting idea is_active = %s for idea_id = %s" % (b, ideaId))
log.error(e)
return False
def addIdeaToProject(db, ideaId, projectId):
try:
db.insert('project__idea', idea_id = ideaId, project_id = projectId)
return True
except Exception, e:
log.info("*** problem adding idea to project")
log.error(e)
return False
def validate(request):
# this is just a cheap validate that depends on the attacker not knowing our AccountSid, it's not secure
settings = Config.get('twilio')
if request('AccountSid') != settings['sid']:
log.error("Request from Twilio does not have correct sid! Possibly an attack! Blocking message.")
log.error("--> was theirs [%s] vs ours [%s]" % (request('AccountSid'), settings['sid']))
return False
return True
def deleteIdea(db, ideaId):
try:
sql = """delete from idea where idea.idea_id = $id"""
db.query(sql, {'id':ideaId})
return True;
except Exception, e:
log.info("*** problem deleting id with id %s" % str(ideaId))
log.error(e)
return False
def basic_processor(handler):
from traceback import format_exception
try:
result = handler()
except Exception, e:
log.error("Unhandled exception: %s" % e)
# Do we want to continue even after exception?
raise
def flagIdea(db, ideaId):
try:
sql = "update idea set num_flags = num_flags + 1 where idea_id = $ideaId"
db.query(sql, {'ideaId':ideaId})
return True
except Exception, e:
log.info("*** problem flagging idea")
log.error(e)
return False
