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 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 dashboard():
import db_helpers
from db_helpers import query_db
user = load_user(current_user.id)
match = re.search(r"facebook\.com/(\w+).*", user.companyFacebook)
print(user.companyFacebook)
print(match)
facebookName = match.group(1)
campaigns = db_helpers.query_db('select distinct id, name, start_date, end_date, tags from user_campaigns join campaigns on user_campaigns.campaign_id = campaigns.id where user_id = %s'%(user.id))
end_date = (subtract_years(now, 1)).strftime("%Y-%m-%d")
stats = "id,name,website,description,category,fan_count,post_like_count,post_comment_count,post_type,post_message"
facebook = displayFacebookJSON(facebookName, end_date+'T00:00:00Z', now_date+'T00:00:00Z', stats)['FacebookStatisticData']
#how you would use filterPosts
fb = filterPosts(campaigns[0][4], facebook)
total_likes = 0
for post in fb['posts']:
total_likes += post['post_like_count']
facebook_data={}
facebook_data['num_posts'] = len(fb['posts'])
facebook_data['daily_posts'] = round(facebook_data['num_posts']/365, 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']), 10)
return render_template("dashboard.html", company=company, facebook=fb, facebook_data=facebook_data, post_popularity=post_popularity)
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 return_events(campaign_id):
import db_helpers
events = db_helpers.query_db('select * from events where campaign = %s' % (campaign_id))
events_list = []
for event in events:
event_dict = {"title": event[1], "start": event[4], "end": event[5]}
events_list.append(event_dict)
return events_list
def trackCampaigns():
import db_helpers
print(current_user.id)
campaigns = db_helpers.query_db('select id, name, start_date, end_date from user_campaigns join campaigns on user_campaigns.campaign_id = campaigns.id where user_id = %s'%(current_user.id))
print(campaigns)
return render_template("trackCampaigns.html", campaigns = campaigns)
def trackCampaigns():
import db_helpers
from db_helpers import query_db
print(current_user.id)
campaigns = db_helpers.query_db('select distinct id, name, start_date, end_date, tags from user_campaigns join campaigns on user_campaigns.campaign_id = campaigns.id where user_id = %s'%(current_user.id))
campaigns_list = list()
print(campaigns)
for campaign in campaigns:
t = list(campaign)
#print(t)
campaigns_list.append(t)
# print(now)
for campaign in campaigns_list:
if(datetime.strptime(str(campaign[3]), "%Y-%m-%d") > now):
campaign.append("in_progress")
else:
campaign.append("ended")
#print(campaigns_list)
user = query_db('select * from users where id = %d' % (current_user.id), (), True)
return render_template("trackCampaigns.html", campaigns = campaigns_list, user = user)
def viewCampaign(campaign_id):
import db_helpers
print(campaign_id)
events = db_helpers.query_db('select * from events where campaign = %s'%(campaign_id))
event_form = EventForm(request.form)
if request.method == 'POST':
query = db_helpers.query_db('insert into events (event_name, event_description, event_type, start_date, end_date, campaign) values ("%s", "%s", "%s", "%s", "%s", "%s")'%(
event_form['event_name'],
event_form['event_description'],
event_form['event_type'],
event_form['start_date'],
event_form['end_date'],
campaign_id
))
return render_template('vewCampaign.html', form = event_form, events = events)
return render_template("viewCampaign.html", form = event_form, events = events)
def login():
from db_helpers import query_db
login_form = LoginForm(request.form)
if request.method == 'POST' and login_form.validate():
result = request.form
#print('select * from users where email = %s and password = %s' % (result['email'], result['password']))
user = query_db('select * from users where email = "%s" and password = "******"' % (result['email'], result['password']), (), True)
if user is None:
print('Invalid credentials')
return redirect('/login')
user = load_user(user[0])
login_user(user)
return redirect('/dashboard')
return render_template("auth.html", form=login_form)
def compareCampaigns():
import db_helpers
user = load_user(current_user.id)
campaigns = db_helpers.query_db('select * from campaigns join user_campaigns on (campaigns.id = user_campaigns.campaign_id) where user_campaigns.user_id = %d' % (user.id))
match = re.search(r"facebook\.com/(\w+).*", user.companyFacebook)
facebookName = match.group(1)
if request.method == 'POST':
to_compare = request.form
campaign1 = to_compare['camp1']
campaign2 = to_compare['camp2']
query1 = db_helpers.query_db('select * from campaigns where id = %d'%(int(campaign1)))
query2 = db_helpers.query_db('select * from campaigns where id = %d'%(int(campaign2)))
if(datetime.strptime(query1[0][4], "%Y-%m-%d") > now):
query1.append("in_progress")
else:
query1.append("ended")
if(datetime.strptime(query2[0][4], "%Y-%m-%d") > now):
query2.append("in_progress")
else:
query2.append("ended")
c1_end = query1[0][4]
c1_start = query1[0][3]
stats = "id,name,website,description,category,fan_count,post_like_count,post_comment_count,post_type,post_message"
facebook1 = displayFacebookJSON(facebookName, c1_start+'T00:00:00Z', c1_end+'T00:00:00Z', stats)['FacebookStatisticData']
# facebook stats for campaign 1
fb1 = filterPosts(query1[0][5], facebook1)
total_likes1 = 0
for post in fb1['posts']:
total_likes1 += post['post_like_count']
campaign1_length = datetime.strptime(query1[0][3], "%Y-%m-%d") - datetime.strptime(query1[0][4], "%Y-%m-%d")
c_len1 = campaign1_length.days
facebook_data1={}
facebook_data1['num_posts'] = len(fb1['posts'])
print("num posts for campaign 1: ", facebook_data1['num_posts'])
facebook_data1['daily_posts'] = round(facebook_data1['num_posts']/c_len1, 2)
print("posts made per day for campaign 1: ", facebook_data1['daily_posts'])
facebook_data1['avg_react_per_post'] = round(total_likes1/facebook_data1['num_posts'], 2)
print("avg reacts per post for campaign 1: ", facebook_data1['avg_react_per_post'])
c2_end = query2[0][4]
c2_start = query2[0][3]
facebook2 = displayFacebookJSON(facebookName, c2_start+'T00:00:00Z', c2_end+'T00:00:00Z', stats)['FacebookStatisticData']
# facebook stats for campaign 2
fb2 = filterPosts(query2[0][5], facebook2)
total_likes2 = 0
for post in fb2['posts']:
total_likes2 += post['post_like_count']
campaign2_length = datetime.strptime(query2[0][3], "%Y-%m-%d") - datetime.strptime(query2[0][4], "%Y-%m-%d")
c_len2 = campaign2_length.days
facebook_data2={}
facebook_data2['num_posts'] = len(fb2['posts'])
print("num posts for campaign 2: ", facebook_data2['num_posts'])
facebook_data2['daily_posts'] = round(facebook_data2['num_posts']/c_len2, 2)
print("posts made per day for campaign 2: ", facebook_data2['daily_posts'])
facebook_data2['avg_react_per_post'] = round(total_likes2/facebook_data2['num_posts'], 2)
print("avg reacts per post for campaign 2: ", facebook_data2['avg_react_per_post'])
return render_template("compareCampaigns.html", c1 = query1, c2 = query2, campaigns=campaigns, facebook_data1=facebook_data1, facebook_data2=facebook_data2)
return render_template("compareCampaigns.html", c1 = [], c2 = [], campaigns=campaigns)
def load_user(id):
from db_helpers import query_db
user = query_db('select * from users where id = %s'%(id), (), True)
return User(user) if user else None
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)
