def process_filter_data(request):
err_msg = ""
time_curr = datetime.datetime.utcnow()
time_dayback = time_curr + datetime.timedelta(hours=-4)
_beginning_time_ = TP.timestamp_from_obj(time_dayback, 1, 3)
_end_time_ = TP.timestamp_from_obj(time_curr, 1, 3)
"""
PROCESS POST VARS
=================
"""
try:
latest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST["latest_utc_ts"].strip())
if not (TP.is_timestamp(latest_utc_ts_var, 1)) and not (TP.is_timestamp(latest_utc_ts_var, 2)):
raise TypeError
if latest_utc_ts_var == "":
latest_utc_ts_var = _end_time_
ts_format = TP.getTimestampFormat(latest_utc_ts_var)
if ts_format == TP.TS_FORMAT_FORMAT1:
latest_utc_ts_var = TP.timestamp_convert_format(latest_utc_ts_var, TP.TS_FORMAT_FORMAT1, TP.TS_FORMAT_FLAT)
except KeyError:
latest_utc_ts_var = _end_time_
except TypeError:
err_msg = "Please enter a valid end-timestamp."
latest_utc_ts_var = _end_time_
try:
earliest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST["earliest_utc_ts"].strip())
if not (TP.is_timestamp(earliest_utc_ts_var, 1)) and not (TP.is_timestamp(earliest_utc_ts_var, 2)):
raise TypeError
if earliest_utc_ts_var == "":
earliest_utc_ts_var = _beginning_time_
ts_format = TP.getTimestampFormat(earliest_utc_ts_var)
if ts_format == TP.TS_FORMAT_FORMAT1:
earliest_utc_ts_var = TP.timestamp_convert_format(
earliest_utc_ts_var, TP.TS_FORMAT_FORMAT1, TP.TS_FORMAT_FLAT
)
except KeyError:
earliest_utc_ts_var = _beginning_time_
except TypeError:
err_msg = "Please enter a valid start-timestamp."
earliest_utc_ts_var = _beginning_time_
return err_msg, earliest_utc_ts_var, latest_utc_ts_var
def impression_list(request):
err_msg = ''
where_clause = ''
"""
Process times and POST
=============
"""
duration_hrs = 2
end_time, start_time = TP.timestamps_for_interval(datetime.datetime.utcnow(), 1, hours=-duration_hrs)
if 'earliest_utc_ts' in request.POST:
if cmp(request.POST['earliest_utc_ts'], '') != 0:
earliest_utc_ts = MySQLdb._mysql.escape_string(request.POST['earliest_utc_ts'].strip())
format = TP.getTimestampFormat(earliest_utc_ts)
if format == 1:
start_time = earliest_utc_ts
if format == 2:
start_time = TP.timestamp_convert_format(earliest_utc_ts, 2, 1)
elif format == -1:
err_msg = err_msg + 'Start timestamp is formatted incorrectly\n'
if 'latest_utc_ts' in request.POST:
if cmp(request.POST['latest_utc_ts'], '') != 0:
latest_utc_ts = MySQLdb._mysql.escape_string(request.POST['latest_utc_ts'].strip())
format = TP.getTimestampFormat(latest_utc_ts)
if format == 1:
end_time = latest_utc_ts
if format == 2:
end_time = TP.timestamp_convert_format(latest_utc_ts, 2, 1)
elif format == -1:
err_msg = err_msg + 'End timestamp is formatted incorrectly\n'
if 'iso_code' in request.POST:
if cmp(request.POST['iso_code'], '') != 0:
iso_code = MySQLdb._mysql.escape_string(request.POST['iso_code'].strip())
where_clause = "where bi.country regexp '%s' " % iso_code
"""
Format and execute query
========================
"""
query_name = 'report_country_impressions.sql'
sql_stmnt = Hlp.file_to_string(projSet.__sql_home__ + query_name)
sql_stmnt = sql_stmnt % (start_time, end_time, start_time, end_time, start_time, end_time, where_clause)
dl = DL.DataLoader()
results = dl.execute_SQL(sql_stmnt)
column_names = dl.get_column_names()
imp_table = DR.DataReporting()._write_html_table(results, column_names)
return render_to_response('live_results/impression_list.html', {'imp_table' : imp_table.decode("utf-8"), 'err_msg' : err_msg, 'start' : TP.timestamp_convert_format(start_time, 1, 2), 'end' : TP.timestamp_convert_format(end_time, 1, 2)}, context_instance=RequestContext(request))
def process_post_vars(request):
end_time, start_time = TP.timestamps_for_interval(
datetime.datetime.utcnow(), 1, hours=-24)
# POST: minimum donations for records
try:
min_donations_var = MySQLdb._mysql.escape_string(
request.POST['min_donations'].strip())
min_donations_var = int(min_donations_var)
except:
min_donations_var = 0
# POST Start Timestamp for records
try:
earliest_utc_ts_var = MySQLdb._mysql.escape_string(
request.POST['utc_ts'].strip())
""" If the user timestamp is earlier than the default start time run the query for the earlier start time """
ts_format = TP.getTimestampFormat(earliest_utc_ts_var)
""" Ensure the validity of the timestamp input """
if ts_format == TP.TS_FORMAT_FORMAT1:
start_time = TP.timestamp_convert_format(earliest_utc_ts_var,
TP.TS_FORMAT_FORMAT1,
TP.TS_FORMAT_FLAT)
elif ts_format == TP.TS_FORMAT_FLAT:
start_time = earliest_utc_ts_var
except Exception: # In the case the form was incorrectly formatted notify the user
pass
# POST: minimum donations for records
try:
view_order = MySQLdb._mysql.escape_string(
request.POST['view_order'].strip())
if cmp(view_order, 'campaign') == 0:
view_order_str = 'order by utm_campaign, country, language, landing_page desc'
elif cmp(view_order, 'country') == 0:
view_order_str = 'order by country, language, utm_campaign, landing_page desc'
except:
view_order_str = 'order by utm_campaign, country, language, landing_page desc'
return start_time, end_time, min_donations_var, view_order_str
def process_post_vars(request):
end_time, start_time = TP.timestamps_for_interval(datetime.datetime.utcnow(), 1, hours=-24)
# POST: minimum donations for records
try:
min_donations_var = MySQLdb._mysql.escape_string(request.POST['min_donations'].strip())
min_donations_var = int(min_donations_var)
except:
min_donations_var = 0
# POST Start Timestamp for records
try:
earliest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST['utc_ts'].strip())
""" If the user timestamp is earlier than the default start time run the query for the earlier start time """
ts_format = TP.getTimestampFormat(earliest_utc_ts_var)
""" Ensure the validity of the timestamp input """
if ts_format == TP.TS_FORMAT_FORMAT1:
start_time = TP.timestamp_convert_format(earliest_utc_ts_var, TP.TS_FORMAT_FORMAT1, TP.TS_FORMAT_FLAT)
elif ts_format == TP.TS_FORMAT_FLAT:
start_time = earliest_utc_ts_var
except Exception: # In the case the form was incorrectly formatted notify the user
pass
# POST: minimum donations for records
try:
view_order = MySQLdb._mysql.escape_string(request.POST['view_order'].strip())
if cmp(view_order, 'campaign') == 0:
view_order_str = 'order by utm_campaign, country, language, landing_page desc'
elif cmp(view_order, 'country') == 0:
view_order_str = 'order by country, language, utm_campaign, landing_page desc'
except:
view_order_str = 'order by utm_campaign, country, language, landing_page desc'
return start_time, end_time, min_donations_var, view_order_str
def index(request, **kwargs):
crl = DL.CampaignReportingLoader(query_type='totals')
filter_data = True
""" Determine the start and end times for the query """
start_time_obj = datetime.datetime.utcnow() + datetime.timedelta(days=-1)
end_time = TP.timestamp_from_obj(datetime.datetime.utcnow(), 1, 3)
start_time = TP.timestamp_from_obj(start_time_obj, 1, 3)
"""
PROCESS POST KWARGS
===================
"""
err_msg = ''
try:
err_msg = str(kwargs['kwargs']['err_msg'])
except:
pass
"""
PROCESS POST VARS
=================
"""
""" Process error message """
try:
err_msg = MySQLdb._mysql.escape_string(request.POST['err_msg'])
except KeyError:
pass
""" If the filter form was submitted extract the POST vars """
try:
min_donations_var = MySQLdb._mysql.escape_string(
request.POST['min_donations'].strip())
earliest_utc_ts_var = MySQLdb._mysql.escape_string(
request.POST['utc_ts'].strip())
""" If the user timestamp is earlier than the default start time run the query for the earlier start time """
ts_format = TP.getTimestampFormat(earliest_utc_ts_var)
""" Ensure the validity of the timestamp input """
if ts_format == TP.TS_FORMAT_FORMAT1:
start_time = TP.timestamp_convert_format(earliest_utc_ts_var,
TP.TS_FORMAT_FORMAT1,
TP.TS_FORMAT_FLAT)
elif ts_format == TP.TS_FORMAT_FLAT:
start_time = earliest_utc_ts_var
elif cmp(earliest_utc_ts_var, '') == 0:
start_time = TP.timestamp_from_obj(start_time_obj, 1, 3)
else:
raise Exception()
if cmp(min_donations_var, '') == 0:
min_donations_var = -1
else:
min_donations_var = int(min_donations_var)
except KeyError: # In the case the form was not submitted set minimum donations and retain the default start time
min_donations_var = -1
pass
except Exception: # In the case the form was incorrectly formatted notify the user
min_donations_var = -1
start_time = TP.timestamp_from_obj(start_time_obj, 1, 3)
err_msg = 'Filter fields are incorrect.'
"""
GENERATE CAMPAIGN DATA
======================
"""
campaigns, all_data = crl.run_query({
'metric_name': 'earliest_timestamp',
'start_time': start_time,
'end_time': end_time
})
""" Sort campaigns by earliest access """
sorted_campaigns = sorted(campaigns.iteritems(),
key=operator.itemgetter(1))
sorted_campaigns.reverse()
"""
FILTER CAMPAIGN DATA
====================
"""
new_sorted_campaigns = list()
for campaign in sorted_campaigns:
key = campaign[0]
if campaign[1] > 0:
name = all_data[key][0]
if name == None:
name = 'none'
timestamp = TP.timestamp_convert_format(all_data[key][3], 1, 2)
if filter_data:
if all_data[key][2] > min_donations_var:
new_sorted_campaigns.append([
campaign[0], campaign[1], name, timestamp,
all_data[key][2], all_data[key][4]
])
else:
new_sorted_campaigns.append([
campaign[0], campaign[1], name, timestamp,
all_data[key][2], all_data[key][4]
])
sorted_campaigns = new_sorted_campaigns
return render_to_response('campaigns/index.html', {
'campaigns': sorted_campaigns,
'err_msg': err_msg
},
context_instance=RequestContext(request))
def daily_totals(request):
err_msg = ''
start_day_ts = TP.timestamp_from_obj(
datetime.datetime.utcnow() + datetime.timedelta(days=-1), 1, 0)
end_day_ts = TP.timestamp_from_obj(datetime.datetime.utcnow(), 1, 0)
country = '.{2}'
min_donation = 0
order_str = 'order by 1 desc,3 desc'
"""
PROCESS POST
"""
if 'start_day_ts' in request.POST:
if cmp(request.POST['start_day_ts'], '') != 0:
start_day_ts = MySQLdb._mysql.escape_string(
request.POST['start_day_ts'].strip())
format = TP.getTimestampFormat(start_day_ts)
if format == 2:
start_day_ts = TP.timestamp_convert_format(start_day_ts, 2, 1)
# start_day_ts = start_day_ts[:8] + '000000'
elif format == -1:
err_msg = err_msg + 'Start timestamp is formatted incorrectly\n'
if 'end_day_ts' in request.POST:
if cmp(request.POST['end_day_ts'], '') != 0:
end_day_ts = MySQLdb._mysql.escape_string(
request.POST['end_day_ts'].strip())
format = TP.getTimestampFormat(start_day_ts)
if format == 2:
end_day_ts = TP.timestamp_convert_format(end_day_ts, 2, 1)
# end_day_ts = end_day_ts[:8] + '000000'
elif format == -1:
err_msg = err_msg + 'End timestamp is formatted incorrectly\n'
if 'country' in request.POST:
if cmp(request.POST['country'], '') != 0:
country = MySQLdb._mysql.escape_string(request.POST['country'])
if 'min_donation' in request.POST:
if cmp(request.POST['min_donation'], '') != 0:
try:
min_donation = int(
MySQLdb._mysql.escape_string(
request.POST['min_donation'].strip()))
except:
logging.error(
'live_results/daily_totals -- Could not process minimum donation for "%s" '
% request.POST['min_donation'].strip())
min_donation = 0
if 'order_metric' in request.POST:
if cmp(request.POST['order_metric'], 'Date') == 0:
order_str = 'order by 1 desc,3 desc'
elif cmp(request.POST['order_metric'], 'Country') == 0:
order_str = 'order by 2 asc,1 desc'
"""
=== END POST ===
"""
query_name = 'report_daily_totals_by_country'
filename = projSet.__sql_home__ + query_name + '.sql'
sql_stmnt = Hlp.file_to_string(filename)
sql_stmnt = QD.format_query(query_name,
sql_stmnt, [start_day_ts, end_day_ts],
country=country,
min_donation=min_donation,
order_str=order_str)
dl = DL.DataLoader()
results = dl.execute_SQL(sql_stmnt)
html_table = DR.DataReporting()._write_html_table(
results, dl.get_column_names(), use_standard_metric_names=True)
return render_to_response('live_results/daily_totals.html', \
{'html_table' : html_table, 'start_time' : TP.timestamp_convert_format(start_day_ts, 1, 2), 'end_time' : TP.timestamp_convert_format(end_day_ts, 1, 2)}, \
context_instance=RequestContext(request))
def impression_list(request):
err_msg = ''
where_clause = ''
"""
Process times and POST
=============
"""
duration_hrs = 2
end_time, start_time = TP.timestamps_for_interval(
datetime.datetime.utcnow(), 1, hours=-duration_hrs)
if 'earliest_utc_ts' in request.POST:
if cmp(request.POST['earliest_utc_ts'], '') != 0:
earliest_utc_ts = MySQLdb._mysql.escape_string(
request.POST['earliest_utc_ts'].strip())
format = TP.getTimestampFormat(earliest_utc_ts)
if format == 1:
start_time = earliest_utc_ts
if format == 2:
start_time = TP.timestamp_convert_format(earliest_utc_ts, 2, 1)
elif format == -1:
err_msg = err_msg + 'Start timestamp is formatted incorrectly\n'
if 'latest_utc_ts' in request.POST:
if cmp(request.POST['latest_utc_ts'], '') != 0:
latest_utc_ts = MySQLdb._mysql.escape_string(
request.POST['latest_utc_ts'].strip())
format = TP.getTimestampFormat(latest_utc_ts)
if format == 1:
end_time = latest_utc_ts
if format == 2:
end_time = TP.timestamp_convert_format(latest_utc_ts, 2, 1)
elif format == -1:
err_msg = err_msg + 'End timestamp is formatted incorrectly\n'
if 'iso_code' in request.POST:
if cmp(request.POST['iso_code'], '') != 0:
iso_code = MySQLdb._mysql.escape_string(
request.POST['iso_code'].strip())
where_clause = "where bi.country regexp '%s' " % iso_code
"""
Format and execute query
========================
"""
query_name = 'report_country_impressions.sql'
sql_stmnt = Hlp.file_to_string(projSet.__sql_home__ + query_name)
sql_stmnt = sql_stmnt % (start_time, end_time, start_time, end_time,
start_time, end_time, where_clause)
dl = DL.DataLoader()
results = dl.execute_SQL(sql_stmnt)
column_names = dl.get_column_names()
imp_table = DR.DataReporting()._write_html_table(results, column_names)
return render_to_response(
'live_results/impression_list.html', {
'imp_table': imp_table.decode("utf-8"),
'err_msg': err_msg,
'start': TP.timestamp_convert_format(start_time, 1, 2),
'end': TP.timestamp_convert_format(end_time, 1, 2)
},
context_instance=RequestContext(request))
def process_filter_data(request):
err_msg = ''
time_curr = datetime.datetime.utcnow()
time_dayback = time_curr + datetime.timedelta(hours = -4)
_beginning_time_ = TP.timestamp_from_obj(time_dayback, 1, 3)
_end_time_ = TP.timestamp_from_obj(time_curr, 1, 3)
"""
PROCESS POST VARS
=================
"""
try:
latest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST['latest_utc_ts'].strip())
if not(TP.is_timestamp(latest_utc_ts_var, 1)) and not(TP.is_timestamp(latest_utc_ts_var, 2)):
raise TypeError
if latest_utc_ts_var == '':
latest_utc_ts_var = _end_time_
ts_format = TP.getTimestampFormat(latest_utc_ts_var)
if ts_format == TP.TS_FORMAT_FORMAT1:
latest_utc_ts_var = TP.timestamp_convert_format(latest_utc_ts_var, TP.TS_FORMAT_FORMAT1, TP.TS_FORMAT_FLAT)
except KeyError:
latest_utc_ts_var = _end_time_
except TypeError:
err_msg = 'Please enter a valid end-timestamp.'
latest_utc_ts_var = _end_time_
try:
earliest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST['earliest_utc_ts'].strip())
if not(TP.is_timestamp(earliest_utc_ts_var, 1)) and not(TP.is_timestamp(earliest_utc_ts_var, 2)):
raise TypeError
if earliest_utc_ts_var == '':
earliest_utc_ts_var = _beginning_time_
ts_format = TP.getTimestampFormat(earliest_utc_ts_var)
if ts_format == TP.TS_FORMAT_FORMAT1:
earliest_utc_ts_var = TP.timestamp_convert_format(earliest_utc_ts_var, TP.TS_FORMAT_FORMAT1, TP.TS_FORMAT_FLAT)
except KeyError:
earliest_utc_ts_var = _beginning_time_
except TypeError:
err_msg = 'Please enter a valid start-timestamp.'
earliest_utc_ts_var = _beginning_time_
return err_msg, earliest_utc_ts_var, latest_utc_ts_var
def daily_totals(request):
err_msg = ''
start_day_ts = TP.timestamp_from_obj(datetime.datetime.utcnow() + datetime.timedelta(days=-1), 1, 0)
end_day_ts = TP.timestamp_from_obj(datetime.datetime.utcnow(), 1, 0)
country = '.{2}'
min_donation = 0
order_str = 'order by 1 desc,3 desc'
"""
PROCESS POST
"""
if 'start_day_ts' in request.POST:
if cmp(request.POST['start_day_ts'], '') != 0:
start_day_ts = MySQLdb._mysql.escape_string(request.POST['start_day_ts'].strip())
format = TP.getTimestampFormat(start_day_ts)
if format == 2:
start_day_ts = TP.timestamp_convert_format(start_day_ts, 2, 1)
# start_day_ts = start_day_ts[:8] + '000000'
elif format == -1:
err_msg = err_msg + 'Start timestamp is formatted incorrectly\n'
if 'end_day_ts' in request.POST:
if cmp(request.POST['end_day_ts'], '') != 0:
end_day_ts = MySQLdb._mysql.escape_string(request.POST['end_day_ts'].strip())
format = TP.getTimestampFormat(start_day_ts)
if format == 2:
end_day_ts = TP.timestamp_convert_format(end_day_ts, 2, 1)
# end_day_ts = end_day_ts[:8] + '000000'
elif format == -1:
err_msg = err_msg + 'End timestamp is formatted incorrectly\n'
if 'country' in request.POST:
if cmp(request.POST['country'], '') != 0:
country = MySQLdb._mysql.escape_string(request.POST['country'])
if 'min_donation' in request.POST:
if cmp(request.POST['min_donation'], '') != 0:
try:
min_donation = int(MySQLdb._mysql.escape_string(request.POST['min_donation'].strip()))
except:
logging.error('live_results/daily_totals -- Could not process minimum donation for "%s" ' % request.POST['min_donation'].strip())
min_donation = 0
if 'order_metric' in request.POST:
if cmp(request.POST['order_metric'], 'Date') == 0:
order_str = 'order by 1 desc,3 desc'
elif cmp(request.POST['order_metric'], 'Country') == 0:
order_str = 'order by 2 asc,1 desc'
"""
=== END POST ===
"""
query_name = 'report_daily_totals_by_country'
filename = projSet.__sql_home__+ query_name + '.sql'
sql_stmnt = Hlp.file_to_string(filename)
sql_stmnt = QD.format_query(query_name, sql_stmnt, [start_day_ts, end_day_ts], country=country, min_donation=min_donation, order_str=order_str)
dl = DL.DataLoader()
results = dl.execute_SQL(sql_stmnt)
html_table = DR.DataReporting()._write_html_table(results, dl.get_column_names(), use_standard_metric_names=True)
return render_to_response('live_results/daily_totals.html', \
{'html_table' : html_table, 'start_time' : TP.timestamp_convert_format(start_day_ts, 1, 2), 'end_time' : TP.timestamp_convert_format(end_day_ts, 1, 2)}, \
context_instance=RequestContext(request))
def index(request, **kwargs):
crl = DL.CampaignReportingLoader(query_type='totals')
filter_data = True
""" Determine the start and end times for the query """
start_time_obj = datetime.datetime.utcnow() + datetime.timedelta(days=-1)
end_time = TP.timestamp_from_obj(datetime.datetime.utcnow(),1,3)
start_time = TP.timestamp_from_obj(start_time_obj,1,3)
"""
PROCESS POST KWARGS
===================
"""
err_msg = ''
try:
err_msg = str(kwargs['kwargs']['err_msg'])
except:
pass
"""
PROCESS POST VARS
=================
"""
""" Process error message """
try:
err_msg = MySQLdb._mysql.escape_string(request.POST['err_msg'])
except KeyError:
pass
""" If the filter form was submitted extract the POST vars """
try:
min_donations_var = MySQLdb._mysql.escape_string(request.POST['min_donations'].strip())
earliest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST['utc_ts'].strip())
""" If the user timestamp is earlier than the default start time run the query for the earlier start time """
ts_format = TP.getTimestampFormat(earliest_utc_ts_var)
""" Ensure the validity of the timestamp input """
if ts_format == TP.TS_FORMAT_FORMAT1:
start_time = TP.timestamp_convert_format(earliest_utc_ts_var, TP.TS_FORMAT_FORMAT1, TP.TS_FORMAT_FLAT)
elif ts_format == TP.TS_FORMAT_FLAT:
start_time = earliest_utc_ts_var
elif cmp(earliest_utc_ts_var, '') == 0:
start_time = TP.timestamp_from_obj(start_time_obj,1,3)
else:
raise Exception()
if cmp(min_donations_var, '') == 0:
min_donations_var = -1
else:
min_donations_var = int(min_donations_var)
except KeyError: # In the case the form was not submitted set minimum donations and retain the default start time
min_donations_var = -1
pass
except Exception: # In the case the form was incorrectly formatted notify the user
min_donations_var = -1
start_time = TP.timestamp_from_obj(start_time_obj,1,3)
err_msg = 'Filter fields are incorrect.'
"""
GENERATE CAMPAIGN DATA
======================
"""
campaigns, all_data = crl.run_query({'metric_name' : 'earliest_timestamp', 'start_time' : start_time, 'end_time' : end_time})
""" Sort campaigns by earliest access """
sorted_campaigns = sorted(campaigns.iteritems(), key=operator.itemgetter(1))
sorted_campaigns.reverse()
"""
FILTER CAMPAIGN DATA
====================
"""
new_sorted_campaigns = list()
for campaign in sorted_campaigns:
key = campaign[0]
if campaign[1] > 0:
name = all_data[key][0]
if name == None:
name = 'none'
timestamp = TP.timestamp_convert_format(all_data[key][3], 1, 2)
if filter_data:
if all_data[key][2] > min_donations_var:
new_sorted_campaigns.append([campaign[0], campaign[1], name, timestamp, all_data[key][2], all_data[key][4]])
else:
new_sorted_campaigns.append([campaign[0], campaign[1], name, timestamp, all_data[key][2], all_data[key][4]])
sorted_campaigns = new_sorted_campaigns
return render_to_response('campaigns/index.html', {'campaigns' : sorted_campaigns, 'err_msg' : err_msg}, context_instance=RequestContext(request))
