def get_fb_sums_per_day(company, since, until, branch):
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery = ''
if since and until and str(since).strip()!='' and str(until).strip()!='':
if company and company != '':
cquery = "and company = '{0}' ".format(company)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
query = """select company, count(*) as posts,
sum(likes) as likes, sum(shares) as shares,
sum(comments) as comments, DATE(created) as day
from facebook
where created >= Date("{0}")
and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY)
{2}
{3}
group by company, day order by company, day""".format(since, until, cquery, bquery)
res = cdict.execute(query)
else:
if company and company != '':
cquery = "where company = '{0}' ".format(company)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " where branch_id = {0} ".format(branch)
query = """select company, count(*) as posts,
sum(likes) as likes, sum(shares) as shares,
sum(comments) as comments, DATE(created) as day
from facebook
{0}
{1}
group by company, day order by company, day""".format(cquery, bquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
def get_twitter_sums_per_weekdays(company, since, until, branch):
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery = ''
if since and until and str(since).strip()!='' and str(until).strip()!='':
if company and company != '':
cquery = "and company = '{0}'".format(company)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
query = """select company, count(*) as tweets,
sum(favorites) as favorites, sum(retweets) as retweets,
DAYNAME(created) as weekday
from twitter
where created >= Date("{0}")
and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY)
{2}
{3}
group by company, weekday order by company, FIELD(weekday, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')""".format(since, until, cquery, bquery)
res = cdict.execute(query)
else:
if company and company != '':
cquery = "where company = '{0}'".format(company)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " where branch_id = {0} ".format(branch)
query = """select company, count(*) as tweets,
sum(favorites) as favorites, sum(retweets) as retweets,
DAYNAME(created) as weekday
from twitter
{0}
{1}
group by company, weekday order by company, FIELD(weekday, 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday')""".format(cquery, bquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
def get_twitter_sums_per_type(company, since, until, branch):
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery = ''
if company and company != '':
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "and created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY) ".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
query = """select company, type, count(type) as types
from twitter
where company='{0}'
{1}
{2}
group by type, company
order by company, type""".format(company.strip(), cquery, bquery)
res = cdict.execute(query)
else:
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "where created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY) ".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " where branch_id = {0} ".format(branch)
query = """select company, type, count(type) as types
from twitter
{0}
{1}
group by type, company
order by company, type""".format(cquery, bquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
def get_fb_sums_per_branch(since, until, branch, bc):
if bc and bc != '':
if not branch or branch == '':
return null
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery=''
groupquery = 'group by branches.id'
if bc and bc != '':
groupquery = 'group by company'
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "and created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY) ".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
query = """select company, company_name, branches.id, branches.name as branch, count(company) as posts,
sum(likes) as likes, sum(shares) as shares, sum(comments) as comments
from facebook, branches
where facebook.branch_id = branches.id
{0}
{1}
{2}
order by likes desc""".format(cquery, bquery, groupquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
def get_twitter_sums_per_timerange(company, since, until, branch):
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery=''
if company and company != '':
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "and created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY) ".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
query = """select gettimerange(created) as timerange,
count(id) as tweets, sum(favorites) as favorites,
sum(retweets) as retweets
from twitter
where company='{0}'
{1}
{2}
group by timerange
order by FIELD(timerange, 'Night (12pm-6am)', 'Morning (6am-12am)', 'Afternoon (12am-6pm)', 'Evening (6pm-12pm)')""".format(company.strip(), cquery, bquery)
res = cdict.execute(query)
else:
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "and created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY) ".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
query = """select gettimerange(created) as timerange,
count(id) as tweets, sum(favorites) as favorites,
sum(retweets) as retweets
from twitter
where 1
{0}
{1}
group by timerange
order by FIELD(timerange, 'Night (12pm-6am)', 'Morning (6am-12am)', 'Afternoon (12am-6pm)', 'Evening (6pm-12pm)')""".format(cquery, bquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
def get_twitter_sums_per_month(company, since, until, branch):
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery = ''
if company and company != '':
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "and created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY) ".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
query = """select MONTHNAME(created) as month, company, count(*) as tweets,
sum(favorites) as favorites,
sum(retweets) as retweets
from twitter
where company='{0}'
{1}
{2}
group by month
order by created, FIELD(month, 'January','February','March','April','May','June','July','August','September','October','November','December')""".format(company.strip(), cquery, bquery)
res = cdict.execute(query)
else:
if since and until and str(since).strip()!='' and str(until).strip()!='':
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
cquery = "where created >= Date('{0}') and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY)".format(since, until)
# cquery = "where created >= Date('{0}') and created <= '{1}'".format(since, until)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " where branch_id = {0} ".format(branch)
query = """select MONTHNAME(created) as month, company, count(*) as tweets,
sum(favorites) as favorites,
sum(retweets) as retweets
from twitter
{0}
{1}
group by month, company
order by company, created, FIELD(month, 'January','February','March','April','May','June','July','August','September','October','November','December')""".format(cquery, bquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
def get_twitter_sums_per_char_range(company, since, until, branch):
c, conn, cdict = connection()
conn.set_character_set('utf8')
cdict.execute('SET NAMES utf8;')
cdict.execute('SET CHARACTER SET utf8;')
cdict.execute('SET character_set_connection=utf8;')
cdict.execute("set session sql_mode='';")
conn.commit()
cquery=''
bquery = ''
if since and until and str(since).strip()!='' and str(until).strip()!='':
if company and company != '':
cquery = "and company = '{0}' ".format(company)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
since = datetime.datetime.strptime(since, '%d/%m/%Y').strftime('%Y-%m-%d')
until = datetime.datetime.strptime(until, '%d/%m/%Y').strftime('%Y-%m-%d')
query = """select company,
count(*) as tweets, getcharrange(text) as charrange,
sum(favorites) as favorites, sum(retweets) as retweets
from twitter
where created >= Date("{0}")
and created < DATE_ADD(DATE('{1}'), INTERVAL 1 DAY)
{2}
{3}
group by charrange, company
order by
company,
FIELD(charrange, '<=100', '101-200', '201-300', '301-400', '401-500', '>500'),
tweets""".format(since, until, cquery, bquery)
res = cdict.execute(query)
else:
if company and company != '':
cquery = "where company = '{0}' ".format(company)
if branch and branch != '':
bquery = " and branch_id = {0} ".format(branch)
else:
if branch and branch != '':
bquery = " where branch_id = {0} ".format(branch)
query = """select company,
count(*) as tweets, getcharrange(text) as charrange,
sum(favorites) as favorites, sum(retweets) as retweets
from twitter
{0}
{1}
group by charrange, company
order by
company,
FIELD(charrange, '<=100', '101-200', '201-300', '301-400', '401-500', '>500'),
tweets""".format(cquery, bquery)
res = cdict.execute(query)
data = cdict.fetchall()
conn.commit()
cdict.close()
conn.close()
gc.collect()
return data
