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 format_query(query_name, sql_stmnt, args, **kwargs):
country, min_donation, order_str = process_kwargs(kwargs)
if cmp(query_name, 'report_campaign_ecomm') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % (start_time))
elif cmp(query_name, 'report_campaign_logs') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % (start_time, start_time, start_time))
elif cmp(query_name, 'report_campaign_ecomm_by_hr') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', start_time))
elif cmp(query_name, 'report_campaign_logs_by_hr') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', start_time, '%', '%', '%', '%', \
start_time, '%', '%', '%', '%', start_time, '%'))
elif cmp(query_name, 'report_impressions_country') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', start_time))
elif cmp(query_name, 'report_campaign_logs_by_min') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', start_time, '%', '%', '%', '%', \
start_time, '%', '%', '%', '%', start_time))
elif cmp(query_name, 'report_non_US_clicks') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', start_time, '%', '%', '%', start_time))
elif cmp(query_name, 'report_contribution_tracking') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', '%',start_time))
elif cmp(query_name, 'report_total_amounts_by_hr') == 0:
start_time = args[0]
end_time = args[1]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', ' %H', start_time, end_time))
elif cmp(query_name, 'report_total_amounts_by_day') == 0:
start_time = args[0]
end_time = args[1]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '', start_time, end_time))
elif cmp(query_name, 'report_LP_metrics') == 0 or cmp(query_name, 'report_LP_metrics_1S') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
min_views = args[3]
""" Format the condition for minimum views """
if cmp(str(min_views), '-1') == 0:
min_views = ' '
else:
min_views = 'where lp.views > ' + str(min_views) + ' '
sql_stmnt = str(sql_stmnt % (start_time, end_time, campaign, country, start_time, end_time, campaign, country, start_time, end_time, campaign, country, min_views))
elif cmp(query_name, 'report_banner_metrics') == 0 or cmp(query_name, 'report_bannerLP_metrics') == 0 or cmp(query_name, 'report_total_metrics') == 0 or \
cmp(query_name, 'report_banner_metrics_1S') == 0 or cmp(query_name, 'report_bannerLP_metrics_1S') == 0 or cmp(query_name, 'report_total_metrics_1S') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
min_views = args[3]
""" Format the condition for minimum views """
if cmp(str(min_views), '-1') == 0:
min_views = ' '
else:
min_views = 'where lp.views > ' + str(min_views) + ' '
sql_stmnt = str(sql_stmnt % (start_time, end_time, country, start_time, end_time, campaign, country, start_time, end_time, country, \
start_time, end_time, campaign, country, start_time, end_time, campaign, country, min_views))
elif cmp(query_name, 'report_latest_campaign') == 0:
start_time = args[0]
sql_stmnt = str(sql_stmnt % (start_time))
elif cmp(query_name, 'report_banner_impressions_by_hour') == 0:
start = args[0]
end = args[1]
sql_stmnt = str(sql_stmnt % ('%','%','%','%', start, end))
elif cmp(query_name, 'report_ecomm_by_amount') == 0:
start_time = args[0]
end_time = args[1]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', start_time, end_time, end_time))
elif cmp(query_name, 'report_ecomm_by_contact') == 0:
where_str = args[0]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', where_str))
elif cmp(query_name, 'report_LP_metrics_minutely') == 0 or cmp(query_name, 'report_LP_metrics_minutely_1S') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
interval = args[3]
""" The start time for the impression portion of the query should be one second less"""
start_time_obj = TP.timestamp_to_obj(start_time,1)
imp_start_time_obj = start_time_obj + datetime.timedelta(seconds=-1)
imp_start_time_obj_str = TP.timestamp_from_obj(imp_start_time_obj, 1, 3)
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', interval, interval, start_time, end_time, campaign, country, '%', '%', '%', '%', interval, interval, start_time, end_time, campaign, country, \
start_time, end_time, campaign, country, campaign))
elif cmp(query_name, 'report_banner_metrics_minutely') == 0 or cmp(query_name, 'report_bannerLP_metrics_minutely') == 0 or cmp(query_name, 'report_banner_metrics_minutely_1S') == 0 or cmp(query_name, 'report_bannerLP_metrics_minutely_1S') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
interval = args[3]
""" The start time for the impression portion of the query should be one second less"""
start_time_obj = TP.timestamp_to_obj(start_time,1)
imp_start_time_obj = start_time_obj + datetime.timedelta(seconds=-1)
imp_start_time_obj_str = TP.timestamp_from_obj(imp_start_time_obj, 1, 3)
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', interval, interval, imp_start_time_obj_str, end_time, \
country, '%', '%', '%', '%', interval, interval, start_time, end_time, campaign, country, \
'%', '%', '%', '%', interval, interval, start_time, end_time, country, \
'%', '%', '%', '%', interval, interval, start_time, end_time, campaign, \
country, start_time, end_time, campaign, country, campaign, ))
elif cmp(query_name, 'report_campaign_metrics_minutely') == 0 or cmp(query_name, 'report_campaign_metrics_minutely_1S') == 0 or cmp(query_name, 'report_campaign_metrics_minutely_total') == 0 \
or cmp(query_name, 'report_campaign_metrics_minutely_total_1S') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
interval = args[3]
sql_stmnt = str(sql_stmnt % (campaign, '%', '%', '%', '%', interval, interval, start_time, end_time, campaign, country, '%', '%', '%', '%', interval, interval, start_time, end_time, campaign, country))
elif cmp(query_name, 'report_campaign_totals') == 0:
start_time = args[0]
end_time = args[1]
sql_stmnt = str(sql_stmnt % (start_time, end_time))
elif cmp(query_name, 'report_campaign_banners') == 0:
start_time = args[0]
end_time = args[1]
utm_campaign = args[2]
sql_stmnt = str(sql_stmnt % (start_time, end_time, utm_campaign))
elif cmp(query_name, 'report_campaign_lps') == 0:
start_time = args[0]
end_time = args[1]
utm_campaign = args[2]
sql_stmnt = str(sql_stmnt % (start_time, end_time, utm_campaign))
elif cmp(query_name, 'report_campaign_bannerlps') == 0:
start_time = args[0]
end_time = args[1]
utm_campaign = args[2]
sql_stmnt = str(sql_stmnt % (start_time, end_time, utm_campaign))
elif cmp(query_name, 'report_campaign_metrics_minutely_all') == 0 or cmp(query_name, 'report_banner_metrics_minutely_all') == 0 or cmp(query_name, 'report_lp_metrics_minutely_all') == 0:
start_time = args[0]
end_time = args[1]
interval = args[3]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', '%', interval, interval, start_time, end_time))
elif cmp(query_name, 'report_donation_metrics') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
sql_stmnt = str(sql_stmnt % (start_time, end_time, campaign, country, start_time, end_time, campaign, country))
elif cmp(query_name, 'report_total_donations') == 0:
start_time = args[0]
end_time = args[1]
campaign = args[2]
""" Recursively construct the sub-query """
sub_query_name = 'report_donation_metrics'
sub_query_sql = Hlp.file_to_string(projSet.__sql_home__ + sub_query_name + '.sql')
sub_query_sql = format_query(sub_query_name, sub_query_sql, [start_time, end_time, campaign], country=country)
sql_stmnt = str(sql_stmnt % sub_query_sql)
elif cmp(query_name, 'report_daily_totals_by_country') == 0:
start_time = args[0]
end_time = args[1]
sql_stmnt = str(sql_stmnt % ('%', '%', '%', start_time, end_time, country, min_donation, order_str))
else:
return 'no such table\n'
return sql_stmnt
def execute_process(self, key, **kwargs):
logging.info('Commencing caching of live results data at: %s' %
self.CACHING_HOME)
shelve_key = key
""" Find the earliest and latest page views for a given campaign """
lptl = DL.LandingPageTableLoader(db='db1025')
query_name = 'report_summary_results_country.sql'
query_name_1S = 'report_summary_results_country_1S.sql'
campaign_regexp_filter = '^C_|^C11_'
dl = DL.DataLoader(db='db1025')
end_time, start_time = TP.timestamps_for_interval(
datetime.datetime.utcnow(), 1, hours=-self.DURATION_HRS)
""" Should a one-step query be used? """
use_one_step = lptl.is_one_step(
start_time, end_time, 'C11'
) # Assume it is a one step test if there are no impressions for this campaign in the landing page table
"""
Retrieve the latest time for which impressions have been loaded
===============================================================
"""
sql_stmnt = 'select max(end_time) as latest_ts from squid_log_record where log_completion_pct = 100.00'
results = dl.execute_SQL(sql_stmnt)
latest_timestamp = results[0][0]
latest_timestamp = TP.timestamp_from_obj(latest_timestamp, 2, 3)
latest_timestamp_flat = TP.timestamp_convert_format(
latest_timestamp, 2, 1)
ret = DR.ConfidenceReporting(query_type='', hyp_test='',
db='db1025').get_confidence_on_time_range(
start_time,
end_time,
campaign_regexp_filter,
one_step=use_one_step)
measured_metrics_counts = ret[1]
""" Prepare Summary results """
sql_stmnt = Hlp.file_to_string(projSet.__sql_home__ + query_name)
sql_stmnt = sql_stmnt % (start_time, latest_timestamp_flat, start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, \
start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, \
start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, campaign_regexp_filter)
logging.info('Executing report_summary_results ...')
results = dl.execute_SQL(sql_stmnt)
column_names = dl.get_column_names()
if use_one_step:
logging.info('... including one step artifacts ...')
sql_stmnt_1S = Hlp.file_to_string(projSet.__sql_home__ +
query_name_1S)
sql_stmnt_1S = sql_stmnt_1S % (start_time, latest_timestamp_flat, start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, \
start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, \
start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, campaign_regexp_filter)
results = list(results)
results_1S = dl.execute_SQL(sql_stmnt_1S)
""" Ensure that the results are unique """
one_step_keys = list()
for row in results_1S:
one_step_keys.append(str(row[0]) + str(row[1]) + str(row[2]))
new_results = list()
for row in results:
key = str(row[0]) + str(row[1]) + str(row[2])
if not (key in one_step_keys):
new_results.append(row)
results = new_results
results.extend(list(results_1S))
metric_legend_table = DR.DataReporting().get_standard_metrics_legend()
conf_legend_table = DR.ConfidenceReporting(
query_type='bannerlp',
hyp_test='TTest').get_confidence_legend_table()
""" Create a interval loader objects """
sampling_interval = 5 # 5 minute sampling interval for donation plots
ir_cmpgn = DR.IntervalReporting(query_type=FDH._QTYPE_CAMPAIGN_ +
FDH._QTYPE_TIME_,
generate_plot=False,
db='db1025')
ir_banner = DR.IntervalReporting(query_type=FDH._QTYPE_BANNER_ +
FDH._QTYPE_TIME_,
generate_plot=False,
db='db1025')
ir_lp = DR.IntervalReporting(query_type=FDH._QTYPE_LP_ +
FDH._QTYPE_TIME_,
generate_plot=False,
db='db1025')
""" Execute queries """
ir_cmpgn.run(start_time, end_time, sampling_interval, 'donations', '',
{})
ir_banner.run(start_time, end_time, sampling_interval, 'donations', '',
{})
ir_lp.run(start_time, end_time, sampling_interval, 'donations', '', {})
""" Prepare serialized objects """
dict_param = dict()
dict_param['metric_legend_table'] = metric_legend_table
dict_param['conf_legend_table'] = conf_legend_table
dict_param['measured_metrics_counts'] = measured_metrics_counts
dict_param['results'] = results
dict_param['column_names'] = column_names
dict_param['interval'] = sampling_interval
dict_param['duration'] = self.DURATION_HRS
dict_param['start_time'] = TP.timestamp_convert_format(
start_time, 1, 2)
dict_param['end_time'] = TP.timestamp_convert_format(end_time, 1, 2)
dict_param['ir_cmpgn_counts'] = ir_cmpgn._counts_
dict_param['ir_banner_counts'] = ir_banner._counts_
dict_param['ir_lp_counts'] = ir_lp._counts_
dict_param['ir_cmpgn_times'] = ir_cmpgn._times_
dict_param['ir_banner_times'] = ir_banner._times_
dict_param['ir_lp_times'] = ir_lp._times_
self.clear_cached_data(shelve_key)
self.cache_data(dict_param, shelve_key)
logging.info('Caching complete.')
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 execute_process(self, key, **kwargs):
logging.info('Commencing caching of live results data at: %s' % self.CACHING_HOME)
shelve_key = key
""" Find the earliest and latest page views for a given campaign """
lptl = DL.LandingPageTableLoader(db='db1025')
query_name = 'report_summary_results_country.sql'
query_name_1S = 'report_summary_results_country_1S.sql'
campaign_regexp_filter = '^C_|^C11_'
dl = DL.DataLoader(db='db1025')
end_time, start_time = TP.timestamps_for_interval(datetime.datetime.utcnow(), 1, hours=-self.DURATION_HRS)
""" Should a one-step query be used? """
use_one_step = lptl.is_one_step(start_time, end_time, 'C11') # Assume it is a one step test if there are no impressions for this campaign in the landing page table
"""
Retrieve the latest time for which impressions have been loaded
===============================================================
"""
sql_stmnt = 'select max(end_time) as latest_ts from squid_log_record where log_completion_pct = 100.00'
results = dl.execute_SQL(sql_stmnt)
latest_timestamp = results[0][0]
latest_timestamp = TP.timestamp_from_obj(latest_timestamp, 2, 3)
latest_timestamp_flat = TP.timestamp_convert_format(latest_timestamp, 2, 1)
ret = DR.ConfidenceReporting(query_type='', hyp_test='', db='db1025').get_confidence_on_time_range(start_time, end_time, campaign_regexp_filter, one_step=use_one_step)
measured_metrics_counts = ret[1]
""" Prepare Summary results """
sql_stmnt = Hlp.file_to_string(projSet.__sql_home__ + query_name)
sql_stmnt = sql_stmnt % (start_time, latest_timestamp_flat, start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, \
start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, \
start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, campaign_regexp_filter)
logging.info('Executing report_summary_results ...')
results = dl.execute_SQL(sql_stmnt)
column_names = dl.get_column_names()
if use_one_step:
logging.info('... including one step artifacts ...')
sql_stmnt_1S = Hlp.file_to_string(projSet.__sql_home__ + query_name_1S)
sql_stmnt_1S = sql_stmnt_1S % (start_time, latest_timestamp_flat, start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, \
start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, start_time, end_time, campaign_regexp_filter, \
start_time, latest_timestamp_flat, campaign_regexp_filter, start_time, latest_timestamp_flat, campaign_regexp_filter)
results = list(results)
results_1S = dl.execute_SQL(sql_stmnt_1S)
""" Ensure that the results are unique """
one_step_keys = list()
for row in results_1S:
one_step_keys.append(str(row[0]) + str(row[1]) + str(row[2]))
new_results = list()
for row in results:
key = str(row[0]) + str(row[1]) + str(row[2])
if not(key in one_step_keys):
new_results.append(row)
results = new_results
results.extend(list(results_1S))
metric_legend_table = DR.DataReporting().get_standard_metrics_legend()
conf_legend_table = DR.ConfidenceReporting(query_type='bannerlp', hyp_test='TTest').get_confidence_legend_table()
""" Create a interval loader objects """
sampling_interval = 5 # 5 minute sampling interval for donation plots
ir_cmpgn = DR.IntervalReporting(query_type=FDH._QTYPE_CAMPAIGN_ + FDH._QTYPE_TIME_, generate_plot=False, db='db1025')
ir_banner = DR.IntervalReporting(query_type=FDH._QTYPE_BANNER_ + FDH._QTYPE_TIME_, generate_plot=False, db='db1025')
ir_lp = DR.IntervalReporting(query_type=FDH._QTYPE_LP_ + FDH._QTYPE_TIME_, generate_plot=False, db='db1025')
""" Execute queries """
ir_cmpgn.run(start_time, end_time, sampling_interval, 'donations', '',{})
ir_banner.run(start_time, end_time, sampling_interval, 'donations', '',{})
ir_lp.run(start_time, end_time, sampling_interval, 'donations', '',{})
""" Prepare serialized objects """
dict_param = dict()
dict_param['metric_legend_table'] = metric_legend_table
dict_param['conf_legend_table'] = conf_legend_table
dict_param['measured_metrics_counts'] = measured_metrics_counts
dict_param['results'] = results
dict_param['column_names'] = column_names
dict_param['interval'] = sampling_interval
dict_param['duration'] = self.DURATION_HRS
dict_param['start_time'] = TP.timestamp_convert_format(start_time,1,2)
dict_param['end_time'] = TP.timestamp_convert_format(end_time,1,2)
dict_param['ir_cmpgn_counts'] = ir_cmpgn._counts_
dict_param['ir_banner_counts'] = ir_banner._counts_
dict_param['ir_lp_counts'] = ir_lp._counts_
dict_param['ir_cmpgn_times'] = ir_cmpgn._times_
dict_param['ir_banner_times'] = ir_banner._times_
dict_param['ir_lp_times'] = ir_lp._times_
self.clear_cached_data(shelve_key)
self.cache_data(dict_param, shelve_key)
logging.info('Caching complete.')
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))