def get_officials_by_name(query):
'''
Query the database to get all officials that have a name match with
the query string.
'''
# Normalize query
rm_punct_trans = str.maketrans('', '', '.,:;?[]{}()-_+=*&%$#@!')
query = str(query).translate(rm_punct_trans)
tokens = [tok.strip() for tok in query.split() if tok.strip()]
if len(tokens) == 0:
return None
select_by_name = '''
SELECT
*
FROM officials_info
WHERE
{like_clause}
'''
like_clause = '''
OR '''.join("LOWER(name) LIKE '%{tok}%'".format(tok=tok)
for tok in tokens)
cursor = get_db().cursor()
# print(select_by_name.format(like_clause=like_clause))
cursor.execute(select_by_name.format(like_clause=like_clause))
results = [dict(res) for res in cursor.fetchall()]
partial_matches = []
full_matches = []
for res in results:
if str(res['name']).lower().translate(rm_punct_trans) == query:
full_matches.append(res)
else:
partial_matches.append(res)
return full_matches + partial_matches
def editCampaign(campaign_id):
import db_helpers
db = db_helpers.get_db()
print(campaign_id)
campaign = db_helpers.query_db('select * from campaigns where id = %s'%(campaign_id))
print("campaign is", campaign)
events = db_helpers.query_db('select * from events where campaign = %s'%(campaign_id))
edit_campaign_form = request.form
if request.method == 'POST':
query = db_helpers.query_db('update campaigns set name = "%s", description = "%s", tags = "%s", start_date = "%s", end_date = "%s", comments_target = "%s", sentiment_score = "%s", likes_target = "%s" where id=%s'%(
edit_campaign_form['campaign_name'],
edit_campaign_form['campaign_description'],
edit_campaign_form['tags'],
edit_campaign_form['start_date'],
edit_campaign_form['end_date'],
edit_campaign_form['comment_count'],
edit_campaign_form['sentiment_score'],
edit_campaign_form['like_count'],
campaign_id
))
db.commit()
q1 = db_helpers.query_db('select * from campaigns where id = %s'%(campaign_id))
return render_template("editCampaign.html", campaign=campaign)
def get_officials_by_address(query):
'''
Query the database to get all officials that represent a
particular address.
'''
# Get state, congressional district from Google Civic Information API
state, cd, predicted_address = get_state_district_for_address(query)
if state is None or cd is None:
return None
select_by_address = '''
SELECT
*
FROM officials_info
WHERE
state = :state
AND (
/* Senators */
LOWER(role) LIKE 'senator%'
/* Representatives of particular congressional district */
OR (
LOWER(role) = 'representative'
AND district = :cd
)
)
;
'''
cursor = get_db().cursor()
cursor.execute(select_by_address, {'state': state, 'cd': cd})
return [dict(res) for res in cursor.fetchall()]
def attr_to_str(collection_id, headers, attr):
db = get_db(headers)
docs = db.read(collection_id)
for doc in docs:
if attr in doc.keys():
doc[attr] = str(doc[attr])
db.update(collection_id, docs)
def register():
import db_helpers
from db_helpers import query_db
registration_form = RegistrationForm(request.form)
db = db_helpers.get_db()
cur = db.cursor()
if request.method == 'POST' and registration_form.validate():
result = request.form
# check that the company doesn't already exist
# make db entry
#print('insert into users (email, password, companyName, companyUrl) values ("%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_url']))
cur.execute(
'insert into users (email, password, companyName, companyWebsite, companyFacebook) values ("%s", "%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_website'], result['company_facebook'])
)
db.commit()
#log in user straight away
user = query_db('select * from users where email = "%s" and password = "******"' % (result['email'], result['password']), (), True)
user = load_user(user[0])
login_user(user)
return redirect('/trackCampaigns')
return render_template("reg.html", form=registration_form)
def scheduleCampaign():
import db_helpers
db = db_helpers.get_db()
campaign_id = request.args.get('campaign_id')
print("---", campaign_id)
events = return_events(campaign_id)
print("!!!",events)
#overwrite events.json
with open("events.json", "w") as jsonFile:
json.dump(events, jsonFile)
event_form = EventForm(request.form)
if request.method == 'POST':
q = 'insert into events values (null, "%s", "%s", "%s", "%s", "%s", " %s")' % (
getval(event_form['event_name']),
getval(event_form['event_description']),
getval(event_form['event_type']),
getval(event_form['start_date']),
getval(event_form['end_date']),
campaign_id
)
query = db_helpers.query_db(q)
db.commit()
return render_template('scheduleCampaign.html', form=event_form, events=events, campaign_id=campaign_id)
return render_template('scheduleCampaign.html', form=event_form, events=events, campaign_id=campaign_id)
def createCampaign():
import db_helpers
db = db_helpers.get_db()
create_campaign_form = request.form
if request.method == 'POST':
query = db_helpers.query_db('insert into campaigns (name, description, tags, start_date, end_date, comments_target, sentiment_score, likes_target) values ("%s", "%s", "%s", "%s", "%s", %s, %s, %s)'%(
create_campaign_form['campaign_name'],
create_campaign_form['campaign_description'],
create_campaign_form['tags'],
create_campaign_form['start_date'],
create_campaign_form['end_date'],
create_campaign_form['comment_count'],
create_campaign_form['sentiment_score'],
create_campaign_form['like_count']
))
db.commit()
campaign_id = db_helpers.query_db('select id from campaigns where name = "%s"'%(create_campaign_form['campaign_name']), (), True)
query = db_helpers.query_db('insert into user_campaigns (user_id, campaign_id) values (%s, %s)'%(current_user.id, campaign_id[0]))
db.commit()
return render_template("createCampaign.html")
return render_template("createCampaign.html")
def str_to_float(mode, collection_id, attr):
db = get_db(mode)
docs = db.read(collection_id)
print docs
for doc in docs:
is_dollar = u'$' in doc[attr]
# replace(u'£', u'')
doc[attr] = doc[attr].replace(u'\xa33', u'').replace(u'\xa30', u'').replace('n/a', '-1').replace(',', '').\
replace('From ', '').replace('From: ', '').replace(' / 15 sachets', '').replace('from just ', '').\
replace('from ', '').replace(u'USD $', '').replace(u'$', '')
doc[attr] = doc[attr].strip()
if not doc[attr]:
doc[attr] = '-1'
full_hyphen_pattern = '[0-9]+-[0-9]+'
half_hyphen_pattern = '[0-9]+-'
print doc[attr]
if re.match(full_hyphen_pattern, doc[attr]):
try:
half_match = re.match(half_hyphen_pattern, doc[attr])
doc[attr] = re.sub(full_hyphen_pattern, half_match.group(0), doc[attr]).replace('-', '')
except Exception as e:
print e
print doc[attr]
raise e
try:
doc[attr] = float(doc[attr])
if is_dollar:
doc[attr] *= 0.76
except Exception as e:
print e
print doc
print doc[attr]
raise e
print docs
db.update(collection_id, docs)
def register():
import db_helpers
registration_form = RegistrationForm(request.form)
db = db_helpers.get_db()
cur = db.cursor()
if request.method == 'POST' and registration_form.validate():
result = request.form
# check that the company doesn't already exist
# make db entry
#print('insert into users (email, password, companyName, companyUrl) values ("%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_url']))
cur.execute(
'insert into users (email, password, companyName, companyUrl) values ("%s", "%s", "%s", "%s")'%(result['email'], result['password'], result['company_name'], result['company_url'])
)
db.commit()
return redirect('/login')
return render_template("reg.html", form=registration_form)
def fetch_member_ids(member_id):
'''
Fetch member IDs (Votesmart, Govtrack, Opensecrets) from the database.
'''
select_member_ids = '''
SELECT
id, votesmart_id, opensecrets_id, govtrack_id
FROM officials_info
WHERE
id = :id
;
'''.format()
cursor = get_db().cursor()
cursor.execute(select_member_ids, {'id': member_id})
results = [dict(res) for res in cursor.fetchall()]
if len(results) != 1:
return None
member_ids = {
'bioguide_id': results[0]['id'],
'votesmart_id': results[0]['votesmart_id'],
'opensecrets_id': results[0]['opensecrets_id'],
'govtrack_id': results[0]['govtrack_id'],
}
return member_ids
def viewCampaign():
import db_helpers
db = db_helpers.get_db()
campaign_id = request.args.get('campaign_id')
# get events
events = return_events(int(campaign_id))
print("!!!",events)
#overwrite events.json
with open("events.json", "w") as jsonFile:
json.dump(events, jsonFile)
user = load_user(current_user.id)
match = re.search(r"facebook\.com/(\w+).*", user.companyFacebook)
facebookName = match.group(1)
campaign = db_helpers.query_db('select * from campaigns where id = %s'%(campaign_id))
#print(campaign)
if(datetime.strptime(campaign[0][4], "%Y-%m-%d") > now):
campaign.append("in_progress")
else:
campaign.append("ended")
#sentiments = get_all_weeks(facebookName,campaign[0][3],campaign[0][4])
###
end_date = campaign[0][4]
start_date = campaign[0][3]
stats = "id,name,website,description,category,fan_count,post_like_count,post_comment_count,post_type,post_message"
facebook = displayFacebookJSON(facebookName, start_date+'T00:00:00Z', end_date+'T00:00:00Z', stats)['FacebookStatisticData']
fb = filterPosts(campaign[0][5], facebook)
total_likes = 0
for post in fb['posts']:
total_likes += post['post_like_count']
campaign_length = datetime.strptime(campaign[0][3], "%Y-%m-%d") - datetime.strptime(campaign[0][4], "%Y-%m-%d")
c_len = campaign_length.days
facebook_data={}
facebook_data['num_posts'] = len(fb['posts'])
facebook_data['daily_posts'] = round(facebook_data['num_posts']/c_len, 2)
facebook_data['avg_react_per_post'] = round(total_likes/facebook_data['num_posts'], 2)
# should be done by sentiment but whatever
post_popularity=islice(sort_posts(fb['posts']), 5)
content = sort_posts(fb['posts'])[0]['post_type']
###
event_form = EventForm(request.form)
if request.method == 'POST':
q = 'insert into events values (null, "%s", "%s", "%s", "%s", "%s", " %s")' % (
getval(event_form['event_name']),
getval(event_form['event_description']),
getval(event_form['event_type']),
getval(event_form['start_date']),
getval(event_form['end_date']),
campaign_id
)
query = db_helpers.query_db(q)
db.commit()
return render_template('viewCampaign.html', form = event_form, events = events, campaign = campaign, facebook=facebook, facebook_data=facebook_data, post_popularity=post_popularity, content=content)
def food_status():
db = get_db(DATABASE).cursor()
fridge_contents = query_db(db, 'select * from fridge')
return render_template('food_status.html', fridge_contents=fridge_contents)
