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 index(request):
err_msg, earliest_utc_ts_var, latest_utc_ts_var = process_filter_data(request)
sltl = DL.SquidLogTableLoader()
""" Show the squid log table """
squid_table = sltl.get_all_rows_unique_start_time()
filtered_squid_table = list()
for row in squid_table:
log_start_time = sltl.get_squid_log_record_field(row, 'start_time')
""" Ensure the timestamp is properly formatted """
if TP.is_timestamp(log_start_time, 2):
log_start_time = TP.timestamp_convert_format(log_start_time, 2, 1)
if int(log_start_time) > int(earliest_utc_ts_var) and int(log_start_time) < int(latest_utc_ts_var):
filtered_squid_table.append(row)
squid_table = filtered_squid_table
squid_table.reverse()
column_names = sltl.get_column_names()
new_column_names = list()
for name in column_names:
new_column_names.append(sltl.get_verbose_column(name))
squid_table = DR.DataReporting()._write_html_table(squid_table, new_column_names)
""" Show the latest log that has been or is loading and its progress """
completion_rate = sltl.get_completion_rate_of_latest_log()
return render_to_response('LML/index.html', {'err_msg' : err_msg, 'squid_table' : squid_table, 'completion_rate' : completion_rate}, 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 get_timestamps_with_interval(self, logFileName, interval):
log_end = self.get_timestamps(logFileName)[1]
end_obj = TP.timestamp_to_obj(log_end, 1)
start_obj = end_obj + datetime.timedelta(minutes=-interval)
start_timestamp = TP.timestamp_from_obj(start_obj, 1, 2)
end_timestamp = TP.timestamp_from_obj(end_obj, 1, 2)
return [start_timestamp, end_timestamp]
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 execute(self):
DataFilter.execute(self)
counts = self._mutable_obj_._counts_
times = self._mutable_obj_._times_
item_keys = self._mutable_obj_._item_keys_
""" Select only the specified item keys """
if len(item_keys) > 0:
counts = self._mutable_obj_.select_metric_keys(counts)
times = self._mutable_obj_.select_metric_keys(times)
""" Convert Times to Integers that indicate relative times AND normalize the intervals in case any are missing """
for key in times.keys():
times[key] = TP.normalize_timestamps(times[key], False, 3)
""" BUG FIX - remove any repeated indices """
fixed_times = list()
for time_index in range(len(times[key]) - 1):
if times[key][time_index] != times[key][time_index + 1]:
fixed_times.append(times[key][time_index])
if len(times[key]) > 0:
fixed_times.append(times[key][-1])
times[key] = fixed_times
times[key], counts[key] = TP.normalize_intervals(times[key], counts[key], self._interval_)
""" If there are missing metrics add them as zeros """
for artifact_key in self._artifact_keys_:
if not(artifact_key in times.keys()):
times[artifact_key] = times[times.keys()[0]]
counts[artifact_key] = [0.0] * len(times[artifact_key])
""" Remove artifacts not in the list if there are any labels specified """
if len(self._artifact_keys_) > 0:
for key in counts.keys():
if key not in self._artifact_keys_:
del counts[key]
del times[key]
def index(request):
err_msg, earliest_utc_ts_var, latest_utc_ts_var = process_filter_data(request)
sltl = DL.SquidLogTableLoader()
""" Show the squid log table """
squid_table = sltl.get_all_rows_unique_start_time()
filtered_squid_table = list()
for row in squid_table:
log_start_time = sltl.get_squid_log_record_field(row, "start_time")
""" Ensure the timestamp is properly formatted """
if TP.is_timestamp(log_start_time, 2):
log_start_time = TP.timestamp_convert_format(log_start_time, 2, 1)
if int(log_start_time) > int(earliest_utc_ts_var) and int(log_start_time) < int(latest_utc_ts_var):
filtered_squid_table.append(row)
squid_table = filtered_squid_table
squid_table.reverse()
column_names = sltl.get_column_names()
new_column_names = list()
for name in column_names:
new_column_names.append(sltl.get_verbose_column(name))
squid_table = DR.DataReporting()._write_html_table(squid_table, new_column_names)
""" Show the latest log that has been or is loading and its progress """
completion_rate = sltl.get_completion_rate_of_latest_log()
return render_to_response(
"LML/index.html",
{"err_msg": err_msg, "squid_table": squid_table, "completion_rate": completion_rate},
context_instance=RequestContext(request),
)
def execute(self):
DataFilter.execute(self)
counts = self._mutable_obj_._counts_
times = self._mutable_obj_._times_
item_keys = self._mutable_obj_._item_keys_
""" Select only the specified item keys """
if len(item_keys) > 0:
counts = self._mutable_obj_.select_metric_keys(counts)
times = self._mutable_obj_.select_metric_keys(times)
""" Convert Times to Integers that indicate relative times AND normalize the intervals in case any are missing """
for key in times.keys():
times[key] = TP.normalize_timestamps(times[key], False, 3)
""" BUG FIX - remove any repeated indices """
fixed_times = list()
for time_index in range(len(times[key]) - 1):
if times[key][time_index] != times[key][time_index + 1]:
fixed_times.append(times[key][time_index])
if len(times[key]) > 0:
fixed_times.append(times[key][-1])
times[key] = fixed_times
times[key], counts[key] = TP.normalize_intervals(
times[key], counts[key], self._interval_)
""" If there are missing metrics add them as zeros """
for artifact_key in self._artifact_keys_:
if not (artifact_key in times.keys()):
times[artifact_key] = times[times.keys()[0]]
counts[artifact_key] = [0.0] * len(times[artifact_key])
""" Remove artifacts not in the list if there are any labels specified """
if len(self._artifact_keys_) > 0:
for key in counts.keys():
if key not in self._artifact_keys_:
del counts[key]
del times[key]
def get_time_of_last_log(self):
log_names = self.get_list_of_logs()
""" In case directory initialize datetime object to one day in the past """
log_time_obj = datetime.datetime.now() + datetime.timedelta(days=-1)
for name in log_names:
""" Retrieve the log timestamp from the filename and convert to datetime objects """
time_stamps = self.get_timestamps_with_interval(name, self._log_copy_interval_)
log_end_time_obj = TP.timestamp_to_obj(time_stamps[1], 1)
if name == log_names[0]:
log_time_obj = log_end_time_obj
else:
if log_end_time_obj > log_time_obj:
log_time_obj = log_end_time_obj
return log_time_obj
def get_timestamps(self, logFileName):
fname_parts = logFileName.split("-")
year = int(fname_parts[1])
month = int(fname_parts[2])
day = int(fname_parts[3])
hour = int(fname_parts[4][0:2])
minute = int(fname_parts[6][0:2])
# Is this an afternoon log?
afternoon = fname_parts[4][2:4] == "PM"
# Adjust the hour as necessary if == 12AM or *PM
if afternoon and hour < 12:
hour = hour + 12
if not (afternoon) and hour == 12:
hour = 0
prev_hr = TP.getPrevHour(year, month, day, hour)
str_month = "0" + str(month) if month < 10 else str(month)
str_day = "0" + str(day) if day < 10 else str(day)
str_hour = "0" + str(hour) if hour < 10 else str(hour)
str_minute = "0" + str(minute) if minute < 10 else str(minute)
prev_month = prev_hr[1]
prev_day = prev_hr[2]
prev_hour = prev_hr[3]
str_prev_month = "0" + str(prev_month) if prev_month < 10 else str(prev_month)
str_prev_day = "0" + str(prev_day) if prev_day < 10 else str(prev_day)
str_prev_hour = "0" + str(prev_hour) if prev_hour < 10 else str(prev_hour)
log_end = str(year) + str_month + str_day + str_hour + str_minute + "00"
log_start = str(prev_hr[0]) + str_prev_month + str_prev_day + str_prev_hour + "5500"
return [log_start, log_end]
def _clear_squid_records(self, start, request_type):
""" Ensure that the range is correct; otherwise abort - critical that outside records are not deleted """
timestamp = TP.timestamp_convert_format(start, 1, 2)
try:
if request_type == self._BANNER_REQUEST_:
self._DL_impressions_.delete_row(timestamp)
elif request_type == self._LP_REQUEST_:
self._DL_LPrequests_.delete_row(timestamp)
logging.info("Executed delete for start time " + timestamp)
except Exception as inst:
logging.error("Could not execute delete for start time " + timestamp)
logging.error(type(inst)) # the exception instance
logging.error(inst.args) # arguments stored in .args
logging.error(inst) # __str__ allows args to printed directly
""" Die if the records cannot be removed """
sys.exit()
def generate_summary(request):
try:
err_msg = ''
"""
PROCESS POST DATA
=================
Escape all user input that can be entered in text fields
"""
if 'utm_campaign' in request.POST:
utm_campaign = MySQLdb._mysql.escape_string(
request.POST['utm_campaign'])
if 'start_time' in request.POST:
start_time = MySQLdb._mysql.escape_string(
request.POST['start_time'].strip())
if not (TP.is_timestamp(start_time, 1)) and not (TP.is_timestamp(
start_time, 2)):
err_msg = 'Incorrectly formatted start timestamp.'
raise Exception()
if 'end_time' in request.POST:
end_time = MySQLdb._mysql.escape_string(
request.POST['end_time'].strip())
if not (TP.is_timestamp(end_time, 1)) and not (TP.is_timestamp(
end_time, 2)):
err_msg = 'Incorrectly formatted end timestamp.'
raise Exception()
if 'iso_filter' in request.POST:
country = MySQLdb._mysql.escape_string(request.POST['iso_filter'])
else:
country = '.{2}'
if 'measure_confidence' in request.POST:
if cmp(request.POST['measure_confidence'], 'yes') == 0:
measure_confidence = True
else:
measure_confidence = False
else:
measure_confidence = False
if 'one_step' in request.POST:
if cmp(request.POST['one_step'], 'yes') == 0:
use_one_step = True
else:
use_one_step = False
else:
use_one_step = False
if 'donations_only' in request.POST:
if cmp(request.POST['donations_only'], 'yes') == 0:
donations_only = True
else:
donations_only = False
else:
donations_only = False
""" Convert timestamp format if necessary """
if TP.is_timestamp(start_time, 2):
start_time = TP.timestamp_convert_format(start_time, 2, 1)
if TP.is_timestamp(end_time, 2):
end_time = TP.timestamp_convert_format(end_time, 2, 1)
""" =============================================== """
"""
GENERATE A REPORT SUMMARY TABLE
===============================
"""
if donations_only:
srl = DL.SummaryReportingLoader(
query_type=FDH._TESTTYPE_DONATIONS_)
else:
srl = DL.SummaryReportingLoader(
query_type=FDH._TESTTYPE_BANNER_LP_)
srl.run_query(start_time,
end_time,
utm_campaign,
min_views=-1,
country=country)
column_names = srl.get_column_names()
summary_results = srl.get_results()
if not (summary_results):
html_table = '<h3>No artifact summary data available for %s.</h3>' % utm_campaign
else:
summary_results_list = list()
for row in summary_results:
summary_results_list.append(list(row))
summary_results = summary_results_list
"""
Format results to encode html table cell markup in results
"""
if measure_confidence:
ret = DR.ConfidenceReporting(
query_type='', hyp_test='').get_confidence_on_time_range(
start_time,
end_time,
utm_campaign,
one_step=use_one_step,
country=country) # first get color codes on confidence
conf_colour_code = ret[0]
for row_index in range(len(summary_results)):
artifact_index = summary_results[row_index][
0] + '-' + summary_results[row_index][
1] + '-' + summary_results[row_index][2]
for col_index in range(len(column_names)):
is_coloured_cell = False
if column_names[col_index] in conf_colour_code.keys():
if artifact_index in conf_colour_code[
column_names[col_index]].keys():
summary_results[row_index][
col_index] = '<td style="background-color:' + conf_colour_code[
column_names[col_index]][
artifact_index] + ';">' + str(
summary_results[row_index]
[col_index]) + '</td>'
is_coloured_cell = True
if not (is_coloured_cell):
summary_results[row_index][
col_index] = '<td>' + str(
summary_results[row_index]
[col_index]) + '</td>'
html_table = DR.DataReporting()._write_html_table(
summary_results,
column_names,
use_standard_metric_names=True,
omit_cell_markup=True)
else:
html_table = DR.DataReporting()._write_html_table(
summary_results,
column_names,
use_standard_metric_names=True)
""" Generate totals only if it's a non-donation-only query """
if donations_only:
srl = DL.SummaryReportingLoader(
query_type=FDH._QTYPE_TOTAL_DONATIONS_)
else:
srl = DL.SummaryReportingLoader(query_type=FDH._QTYPE_TOTAL_)
srl.run_query(start_time,
end_time,
utm_campaign,
min_views=-1,
country=country)
total_summary_results = srl.get_results()
if not (total_summary_results):
html_table = html_table + '<div class="spacer"></div><div class="spacer"></div><h3>No data available for %s Totals.</h3>' % utm_campaign
else:
html_table = html_table + '<div class="spacer"></div><div class="spacer"></div>' + DR.DataReporting(
)._write_html_table(total_summary_results,
srl.get_column_names(),
use_standard_metric_names=True)
metric_legend_table = DR.DataReporting().get_standard_metrics_legend()
conf_legend_table = DR.ConfidenceReporting(
query_type='bannerlp',
hyp_test='TTest').get_confidence_legend_table()
html_table = '<h4><u>Metrics Legend:</u></h4><div class="spacer"></div>' + metric_legend_table + \
'<div class="spacer"></div><h4><u>Confidence Legend for Hypothesis Testing:</u></h4><div class="spacer"></div>' + conf_legend_table + '<div class="spacer"></div><div class="spacer"></div>' + html_table
"""
DETERMINE PAYMENT METHODS
=========================
"""
ccl = DL.CiviCRMLoader()
pm_data_counts, pm_data_conversions = ccl.get_payment_methods(
utm_campaign, start_time, end_time, country=country)
html_table_pm_counts = DR.IntervalReporting(
).write_html_table_from_rowlists(
pm_data_counts, ['Payment Method', 'Portion of Donations (%)'],
'Landing Page')
html_table_pm_conversions = DR.IntervalReporting(
).write_html_table_from_rowlists(pm_data_conversions, [
'Payment Method', 'Visits', 'Conversions', 'Conversion Rate (%)',
'Amount', 'Amount 25'
], 'Landing Page')
html_table = html_table + '<div class="spacer"></div><h4><u>Payment Methods Breakdown:</u></h4><div class="spacer"></div>' + html_table_pm_counts + \
'<div class="spacer"></div><div class="spacer"></div>' + html_table_pm_conversions + '<div class="spacer"></div><div class="spacer"></div>'
return render_to_response('tests/table_summary.html', {
'html_table': html_table,
'utm_campaign': utm_campaign
},
context_instance=RequestContext(request))
except Exception as inst:
if cmp(err_msg, '') == 0:
err_msg = 'Could not generate campaign tabular results.'
return index(request, err_msg=err_msg)
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 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 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 fundraiser totals data at: %s' % self.CACHING_HOME)
end_time = TP.timestamp_from_obj(datetime.datetime.utcnow(), 1, 3)
""" DATA CONFIG """
""" set the metrics to plot """
lttdl = DL.LongTermTrendsLoader(db='db1025')
start_of_2011_fundraiser = '20111116000000'
countries = DL.CiviCRMLoader().get_ranked_donor_countries(start_of_2011_fundraiser)
countries.append('Total')
""" Dictionary object storing lists of regexes - each expression must pass for a label to persist """
year_groups = dict()
for country in countries:
if cmp(country, 'Total') == 0:
year_groups['2011 Total'] = ['2011.*']
year_groups['2010 Total'] = ['2010.*']
else:
year_groups['2011 ' + country] = ['2011' + country]
year_groups['2010 ' + country] = ['2010' + country]
metrics = 'amount'
weights = ''
groups = year_groups
group_metrics = ['year', 'country']
metric_types = DL.LongTermTrendsLoader._MT_AMOUNT_
include_totals = False
include_others = False
hours_back = 0
time_unit = TP.DAY
""" END CONFIG """
""" For each metric use the LongTermTrendsLoader to generate the data to plot """
dr = DR.DataReporting()
times, counts = lttdl.run_fundrasing_totals(end_time, metric_name=metrics, metric_type=metric_types, groups=groups, group_metric=group_metrics, include_other=include_others, \
include_total=include_totals, hours_back=hours_back, weight_name=weights, time_unit=time_unit)
dict_param = dict()
for country in countries:
key_2011 = '2011 ' + country
key_2010 = '2010 ' + country
new_counts = dict()
new_counts[key_2010] = counts[key_2010]
new_counts[key_2011] = counts[key_2011]
new_times = dict()
new_times[key_2010] = times[key_2010]
new_times[key_2011] = times[key_2011]
dr._counts_ = new_counts
dr._times_ = new_times
empty_data = [0] * len(new_times[new_times.keys()[0]])
data = list()
data.append(dr.get_data_lists([''], empty_data))
dict_param[country] = Hlp.combine_data_lists(data)
self.clear_cached_data(key)
self.cache_data(dict_param, key)
logging.info('Caching complete.')
def test(request):
try:
"""
PROCESS POST DATA
=================
Escape all user input that can be entered in text fields
"""
test_name_var = MySQLdb._mysql.escape_string(
request.POST['test_name'].strip())
utm_campaign_var = MySQLdb._mysql.escape_string(
request.POST['utm_campaign'].strip())
start_time_var = MySQLdb._mysql.escape_string(
request.POST['start_time'].strip())
end_time_var = MySQLdb._mysql.escape_string(
request.POST['end_time'].strip())
one_step_var = MySQLdb._mysql.escape_string(
request.POST['one_step'].strip())
country = MySQLdb._mysql.escape_string(request.POST['iso_filter'])
""" Convert timestamp format if necessary """
if TP.is_timestamp(start_time_var, 2):
start_time_var = TP.timestamp_convert_format(start_time_var, 2, 1)
if TP.is_timestamp(end_time_var, 2):
end_time_var = TP.timestamp_convert_format(end_time_var, 2, 1)
if cmp(one_step_var, 'True') == 0:
one_step_var = True
else:
one_step_var = False
try:
test_type_var = MySQLdb._mysql.escape_string(
request.POST['test_type'])
labels = request.POST['artifacts']
except KeyError:
test_type_var, labels = FDH.get_test_type(
utm_campaign_var, start_time_var, end_time_var,
DL.CampaignReportingLoader(
query_type='')) # submit an empty query type
labels = labels.__str__()
label_dict = dict()
label_dict_full = dict()
labels = labels[1:-1].split(',')
""" Parse the labels """
for i in range(len(labels)):
labels[i] = labels[i]
label = labels[i].split('\'')[1]
label = label.strip()
pieces = label.split(' ')
label = pieces[0]
for j in range(len(pieces) - 1):
label = label + '_' + pieces[j + 1]
""" Escape the label parameters """
label = MySQLdb._mysql.escape_string(label)
label_dict_full[label] = label
""" Look at the artifact names and map them into a dict() - Determine if artifacts were chosen by the user """
if request.POST.__contains__('artifacts_chosen'):
artifacts_chosen = request.POST.getlist('artifacts_chosen')
""" Ensure that only two items are selected """
if len(artifacts_chosen) > 2:
raise Exception(
'Please select (checkboxes) exactly two items to test')
for elem in artifacts_chosen:
esc_elem = MySQLdb._mysql.escape_string(str(elem))
label_dict[esc_elem] = esc_elem
else:
label_dict = label_dict_full
""" Parse the added labels IF they are not empty """
for key in label_dict.keys():
try:
if not (request.POST[key] == ''):
label_dict[key] = MySQLdb._mysql.escape_string(
str(request.POST[key]))
else:
label_dict[key] = key
except:
logging.error('Could not find %s in the POST QueryDict.' % key)
for key in label_dict_full.keys():
try:
if not (request.POST[key] == ''):
label_dict_full[key] = MySQLdb._mysql.escape_string(
str(request.POST[key]))
else:
label_dict_full[key] = key
except:
logging.error('Could not find %s in the POST QueryDict.' % key)
"""
EXECUTE REPORT GENERATION
=========================
setup time parameters
determine test metrics
execute queries
"""
sample_interval = 1
start_time_obj = TP.timestamp_to_obj(start_time_var, 1)
end_time_obj = TP.timestamp_to_obj(end_time_var, 1)
time_diff = end_time_obj - start_time_obj
time_diff_min = time_diff.seconds / 60.0
test_interval = int(math.floor(time_diff_min /
sample_interval)) # 2 is the interval
metric_types = FDH.get_test_type_metrics(test_type_var)
metric_types_full = dict()
""" Get the full (descriptive) version of the metric names
!! FIXME / TODO -- order these properly !! """
for i in range(len(metric_types)):
metric_types_full[metric_types[i]] = QD.get_metric_full_name(
metric_types[i])
""" USE generate_reporting_objects() TO GENERATE THE REPORT DATA - dependent on test type """
measured_metric, winner, loser, percent_win, confidence, html_table_pm_banner, html_table_pm_lp, html_table_language, html_table \
= generate_reporting_objects(test_name_var, start_time_var, end_time_var, utm_campaign_var, label_dict, label_dict_full, \
sample_interval, test_interval, test_type_var, metric_types, one_step_var, country)
winner_var = winner[0]
results = list()
for index in range(len(winner)):
results.append({
'metric': measured_metric[index],
'winner': winner[index],
'loser': loser[index],
'percent_win': percent_win[index],
'confidence': confidence[index]
})
template_var_dict = {'results' : results, \
'utm_campaign' : utm_campaign_var, 'metric_names_full' : metric_types_full, \
'summary_table': html_table, 'sample_interval' : sample_interval, \
'banner_pm_table' : html_table_pm_banner, 'lp_pm_table' : html_table_pm_lp, 'html_table_language' : html_table_language, \
'start_time' : TP.timestamp_convert_format(start_time_var, 1, 2) , 'end_time' : TP.timestamp_convert_format(end_time_var, 1, 2)}
html = render_to_response('tests/results_' + test_type_var + '.html',
template_var_dict,
context_instance=RequestContext(request))
"""
WRITE TO TEST TABLE
===================
"""
ttl = DL.TestTableLoader()
""" Format the html string """
html_string = html.__str__()
html_string = html_string.replace('"', '\\"')
if ttl.record_exists(utm_campaign=utm_campaign_var):
ttl.update_test_row(test_name=test_name_var,
test_type=test_type_var,
utm_campaign=utm_campaign_var,
start_time=start_time_var,
end_time=end_time_var,
html_report=html_string,
winner=winner_var)
else:
ttl.insert_row(test_name=test_name_var,
test_type=test_type_var,
utm_campaign=utm_campaign_var,
start_time=start_time_var,
end_time=end_time_var,
html_report=html_string,
winner=winner_var)
return html
except Exception as inst:
logging.error('Failed to correctly generate test report.')
logging.error(type(inst))
logging.error(inst.args)
logging.error(inst)
""" Return to the index page with an error """
try:
err_msg = 'Test Generation failed for: %s. Check the fields submitted for generation. <br><br>ERROR:<br><br>%s' % (
utm_campaign_var, inst.__str__())
except:
err_msg = 'Test Generation failed. Check the fields submitted for generation. <br><br>ERROR:<br><br>%s' % inst.__str__(
)
return campaigns_index(request, kwargs={'err_msg': err_msg})
return show_campaigns(request,
utm_campaign_var,
kwargs={'err_msg': err_msg})
def execute_process(self, key, **kwargs):
logging.info('Commencing caching of fundraiser totals data at: %s' %
self.CACHING_HOME)
end_time = TP.timestamp_from_obj(datetime.datetime.utcnow(), 1, 3)
""" DATA CONFIG """
""" set the metrics to plot """
lttdl = DL.LongTermTrendsLoader(db='db1025')
start_of_2011_fundraiser = '20111116000000'
countries = DL.CiviCRMLoader().get_ranked_donor_countries(
start_of_2011_fundraiser)
countries.append('Total')
""" Dictionary object storing lists of regexes - each expression must pass for a label to persist """
year_groups = dict()
for country in countries:
if cmp(country, 'Total') == 0:
year_groups['2011 Total'] = ['2011.*']
year_groups['2010 Total'] = ['2010.*']
else:
year_groups['2011 ' + country] = ['2011' + country]
year_groups['2010 ' + country] = ['2010' + country]
metrics = 'amount'
weights = ''
groups = year_groups
group_metrics = ['year', 'country']
metric_types = DL.LongTermTrendsLoader._MT_AMOUNT_
include_totals = False
include_others = False
hours_back = 0
time_unit = TP.DAY
""" END CONFIG """
""" For each metric use the LongTermTrendsLoader to generate the data to plot """
dr = DR.DataReporting()
times, counts = lttdl.run_fundrasing_totals(end_time, metric_name=metrics, metric_type=metric_types, groups=groups, group_metric=group_metrics, include_other=include_others, \
include_total=include_totals, hours_back=hours_back, weight_name=weights, time_unit=time_unit)
dict_param = dict()
for country in countries:
key_2011 = '2011 ' + country
key_2010 = '2010 ' + country
new_counts = dict()
new_counts[key_2010] = counts[key_2010]
new_counts[key_2011] = counts[key_2011]
new_times = dict()
new_times[key_2010] = times[key_2010]
new_times[key_2011] = times[key_2011]
dr._counts_ = new_counts
dr._times_ = new_times
empty_data = [0] * len(new_times[new_times.keys()[0]])
data = list()
data.append(dr.get_data_lists([''], empty_data))
dict_param[country] = Hlp.combine_data_lists(data)
self.clear_cached_data(key)
self.cache_data(dict_param, key)
logging.info('Caching complete.')
def execute_process(self, key, **kwargs):
logging.info('Commencing caching of long term trends data at: %s' %
self.CACHING_HOME)
end_time, start_time = TP.timestamps_for_interval(datetime.datetime.utcnow(), 1, \
hours=-self.VIEW_DURATION_HRS, resolution=1)
""" DATA CONFIG """
countries = DL.CiviCRMLoader().get_ranked_donor_countries(start_time)
countries = countries[1:6]
""" set the metrics to plot """
lttdl = DL.LongTermTrendsLoader(db='storage3')
""" Dictionary object storing lists of regexes - each expression must pass for a label to persist """
# country_groups = {'US': ['(US)'], 'CA': ['(CA)'], 'JP': ['(JP)'], 'IN': ['(IN)'], 'NL': ['(NL)']}
payment_groups = {'Credit Card': ['^cc$'], 'Paypal': ['^pp$']}
currency_groups = {
'USD': ['(USD)'],
'CAD': ['(CAD)'],
'JPY': ['(JPY)'],
'EUR': ['(EUR)']
}
lang_cntry_groups = {
'US': ['US..', '.{4}'],
'EN': ['[^U^S]en', '.{4}']
}
top_cntry_groups = dict()
for country in countries:
top_cntry_groups[country] = [country, '.{2}']
# To include click rate
# groups = [ lang_cntry_groups] metrics = ['click_rate'] metrics_index = [3]
# group_metrics = [DL.LongTermTrendsLoader._MT_RATE_] metric_types = ['country', 'language'] include_totals = [True] include_others = [True]
metrics = [
'impressions', 'views', 'donations', 'donations', 'amount',
'amount', 'diff_don', 'diff_don', 'donations', 'conversion_rate'
]
weights = ['', '', '', '', '', '', 'donations', 'donations', '', '']
metrics_index = [0, 1, 2, 2, 2, 4, 5, 5, 6, 6]
groups = [lang_cntry_groups, lang_cntry_groups, lang_cntry_groups, top_cntry_groups, lang_cntry_groups, currency_groups, \
lang_cntry_groups, lang_cntry_groups, payment_groups, payment_groups]
""" The metrics that are used to build a group string to be qualified via regex - the values of the list metrics are concatenated """
group_metrics = [['country', 'language'], ['country', 'language'], ['country', 'language'], \
['country', 'language'], ['country', 'language'], ['currency'], ['country', 'language'], \
['country', 'language'], ['payment_method'], ['payment_method']]
metric_types = [DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, \
DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, \
DL.LongTermTrendsLoader._MT_RATE_WEIGHTED_, DL.LongTermTrendsLoader._MT_RATE_WEIGHTED_, DL.LongTermTrendsLoader._MT_AMOUNT_, \
DL.LongTermTrendsLoader._MT_RATE_]
include_totals = [
True, True, True, False, True, True, False, False, False, True
]
include_others = [
True, True, True, False, True, True, True, True, True, False
]
hours_back = [0, 0, 0, 0, 0, 0, 24, 168, 0, 0]
time_unit = [
TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR,
TP.HOUR, TP.HOUR, TP.HOUR
]
data = list()
""" END CONFIG """
""" For each metric use the LongTermTrendsLoader to generate the data to plot """
for index in range(len(metrics)):
dr = DR.DataReporting()
times, counts = lttdl.run_query(start_time, end_time, metrics_index[index], metric_name=metrics[index], metric_type=metric_types[index], \
groups=groups[index], group_metric=group_metrics[index], include_other=include_others[index], \
include_total=include_totals[index], hours_back=hours_back[index], weight_name=weights[index], \
time_unit=time_unit[index])
times = TP.normalize_timestamps(times, False, time_unit[index])
dr._counts_ = counts
dr._times_ = times
empty_data = [0] * len(times[times.keys()[0]])
data.append(dr.get_data_lists([''], empty_data))
dict_param = Hlp.combine_data_lists(data)
dict_param['interval'] = self.VIEW_DURATION_HRS
dict_param['end_time'] = TP.timestamp_convert_format(end_time, 1, 2)
self.clear_cached_data(key)
self.cache_data(dict_param, key)
logging.info('Caching complete.')
def index(request, **kwargs):
"""
PROCESS POST DATA
=================
"""
if 'err_msg' in kwargs:
err_msg = kwargs['err_msg']
else:
err_msg = ''
try:
latest_utc_ts_var = MySQLdb._mysql.escape_string(
request.POST['latest_utc_ts'].strip())
earliest_utc_ts_var = MySQLdb._mysql.escape_string(
request.POST['earliest_utc_ts'].strip())
if not TP.is_timestamp(earliest_utc_ts_var, 1) or not TP.is_timestamp(
earliest_utc_ts_var, 1):
raise TypeError
if latest_utc_ts_var == '':
latest_utc_ts_var = _end_time_
except KeyError:
earliest_utc_ts_var = _beginning_time_
latest_utc_ts_var = _end_time_
except TypeError:
err_msg = 'Please enter a valid timestamp.'
earliest_utc_ts_var = _beginning_time_
latest_utc_ts_var = _end_time_
ttl = DL.TestTableLoader()
columns = ttl.get_column_names()
test_rows = ttl.get_all_test_rows()
""" Build a list of tests -- apply filters """
l = []
utm_campaign_index = ttl.get_test_index('utm_campaign')
html_report_index = ttl.get_test_index('html_report')
for i in test_rows:
test_start_time = ttl.get_test_field(i, 'start_time')
new_row = list(i)
""" Ensure the timestamp is properly formatted """
if TP.is_timestamp(test_start_time, 2):
test_start_time = TP.timestamp_convert_format(
test_start_time, 2, 1)
new_row[
html_report_index] = '<a href="/tests/report/%s">view</a>' % new_row[
utm_campaign_index]
if int(test_start_time) > int(earliest_utc_ts_var) and int(
test_start_time) < int(latest_utc_ts_var):
l.append(new_row)
l.reverse()
test_table = DR.DataReporting()._write_html_table(
l, columns, use_standard_metric_names=True)
return render_to_response('tests/index.html', {
'err_msg': err_msg,
'test_table': test_table
},
context_instance=RequestContext(request))
def mine_squid_landing_page_requests(self, logFileName):
logging.info("Begin mining of landing page requests in %s" % logFileName)
""" Create the dataloaders and initialize """
sltl = DL.SquidLogTableLoader()
lptl = DL.LandingPageTableLoader()
ipctl = DL.IPCountryTableLoader()
""" Retrieve the log timestamp from the filename """
time_stamps = self.get_timestamps_with_interval(logFileName, self._log_copy_interval_)
end = time_stamps[1]
curr_time = TP.timestamp_from_obj(datetime.datetime.utcnow(), 1, 3)
""" retrieve the start time of the log """
start = self.get_first_timestamp_from_log(logFileName)
""" Initialization - open the file """
logFile, total_lines_in_file = self.open_logfile(logFileName)
# Initialization
hostIndex = 1
queryIndex = 4
pathIndex = 2
""" Clear the old records """
self._clear_squid_records(start, self._LP_REQUEST_)
""" Add a row to the SquidLogTable """
sltl.insert_row(
type="lp_view",
log_copy_time=curr_time,
start_time=start,
end_time=end,
log_completion_pct="0.0",
total_rows="0",
)
line_count = 0
requests_loaded = 0
""" Extract the mining patterns from the DB """
mptl = DL.MiningPatternsTableLoader()
lp_patterns = mptl.get_pattern_lists()[1]
"""
PROCESS REQUESTS FROM FILE
==========================
Sample request:
line =
"sq63.wikimedia.org 757671483 2011-06-01T23:00:01.343 93 98.230.113.246 TCP_MISS/200 10201 GET \
http://wikimediafoundation.org/w/index.php?title=WMFJA085/en/US&utm_source=donate&utm_medium=sidebar&utm_campaign=20101204SB002&country_code=US&referrer=http%3A%2F%2Fen.wikipedia.org%2Fwiki%2FFile%3AMurphy_High_School.jpg CARP/208.80.152.83 text/html http://en.wikipedia.org/wiki/File:Murphy_High_School.jpg \
- Mozilla/4.0%20(compatible;%20MSIE%208.0;%20Windows%20NT%206.1;%20WOW64;%20Trident/4.0;%20FunWebProducts;%20GTB6.6;%20SLCC2;%20.NET%20CLR%202.0.50727;%20.NET%20CLR%203.5.30729;%20.NET%20CLR%203.0.30729;%20Media%20Center%20PC%206.0;%20HPDTDF;%20.NET4.0C)"
"""
line = logFile.readline()
while line != "":
lineArgs = line.split()
""" Get the IP Address of the donor """
ip_add = lineArgs[4]
# SELECT CAST('20070529 00:00:00' AS datetime)
"""
Parse the Timestamp:
Sample timestamp:
timestamp = "2011-06-01T23:00:07.612"
"""
date_and_time = lineArgs[2]
date_string = date_and_time.split("-")
time_string = date_and_time.split(":")
# if the date is not logged ignoere the record
try:
year = date_string[0]
month = date_string[1]
day = date_string[2][:2]
hour = time_string[0][-2:]
min = time_string[1]
sec = time_string[2][:2]
except:
line = logFile.readline()
total_lines_in_file = total_lines_in_file - 1
continue
timestamp_string = year + "-" + month + "-" + day + " " + hour + ":" + min + ":" + sec
"""
Process referrer URL
===================
Sample referrer:
referrer_url = http://en.wikipedia.org/wiki/File:Murphy_High_School.jpg
"""
try:
referrer_url = lineArgs[11]
except IndexError:
referrer_url = "Unavailable"
parsed_referrer_url = up.urlparse(referrer_url)
if parsed_referrer_url[hostIndex] == None:
project = "NONE"
source_lang = "NONE"
else:
hostname = parsed_referrer_url[hostIndex].split(".")
""" If the hostname of the form '<lang>.<project>.org' """
if len(hostname[0]) <= 2:
# referrer_path = parsed_referrer_url[pathIndex].split('/')
project = hostname[0] # wikimediafoundation.org
source_lang = hostname[0]
else:
try:
""" species.wikimedia vs en.wikinews """
project = hostname[0] if (hostname[1] == "wikimedia") else hostname[1]
""" pl.wikipedia vs commons.wikimedia """
source_lang = hostname[0] if (len(hostname[1]) < 5) else "en"
except:
project = "wikipedia" """ default project to 'wikipedia' """
source_lang = "en" """ default lang to english """
"""
Process User agent string
========================
sample user agent string:
user_agent_string = Mozilla/4.0%20(compatible;%20MSIE%208.0;%20Windows%20NT%206.1;%20WOW64;%20Trident/4.0;%20FunWebProducts;%20GTB6.6;%20SLCC2;%20.NET%20CLR%202.0.50727;%20.NET%20CLR%203.5.30729;%20.NET%20CLR%203.0.30729;%20Media%20Center%20PC%206.0;%20HPDTDF;%20.NET4.0C)
"""
try:
user_agent_string = lineArgs[13]
except IndexError:
user_agent_string = ""
try:
user_agent_fields = httpagentparser.detect(user_agent_string)
browser = "NONE"
# Check to make sure fields exist
if len(user_agent_fields["browser"]) != 0:
if len(user_agent_fields["browser"]["name"]) != 0:
browser = user_agent_fields["browser"]["name"]
except:
logging.error("Could not process user agent string.")
browser = "NONE"
"""
Process landing URL
===================
sample landing urls:
landing_url = "http://wikimediafoundation.org/w/index.php?title=WMFJA085/en/US&utm_source=donate&utm_medium=sidebar&utm_campaign=20101204SB002&country_code=US&referrer=http%3A%2F%2Fen.wikipedia.org%2Fwiki%2FFile%3AMurphy_High_School.jpg"
landing_url = "http://wikimediafoundation.org/wiki/WMFJA1/ru"
landing_url = *donate.wikimedia.org/wiki/Special:FundraiserLandingPage?uselang=en&country=US&template=Lp-layout-default&appeal=Appeal-default&form-countryspecific=Form-countryspecific-control&utm_medium=sitenotice&utm_source=B11_Donate_Jimmy_Control&utm_campaign=C11_1107
"""
try:
landing_url = lineArgs[8]
except IndexError:
landing_url = "Unavailable"
hostIndex = 1
queryIndex = 4
pathIndex = 2
parsed_landing_url = up.urlparse(landing_url)
query_fields = cgi.parse_qs(parsed_landing_url[queryIndex]) # Get the banner name and lang
path_pieces = parsed_landing_url[pathIndex].split("/")
include_request, url_match = self.evaluate_landing_url(
landing_url, parsed_landing_url, query_fields, path_pieces, lp_patterns
)
if include_request:
""" Extract the language from the query string
the language has already been read from the url path but if it
exists in the query string this setting should take precedence
"""
try:
source_lang = query_fields["language"][0]
except:
pass
""" Address cases where the query string contains the landing page - ...wikimediafoundation.org/w/index.php?... """
# http://wikimediafoundation.org/wiki/
if url_match == 1:
""" Address cases where the query string does not contain the landing page - ...wikimediafoundation.org/wiki/... """
parsed_landing_url = up.urlparse(landing_url)
query_fields = cgi.parse_qs(parsed_landing_url[queryIndex]) # Get the banner name and lang
landing_path = parsed_landing_url[pathIndex].split("/")
landing_page = landing_path[2]
# URLs of the form ...?county_code=<iso_code>
try:
country = query_fields["country"][0]
# URLs of the form ...<path>/ <lp_name>/<lang>/<iso_code>
except:
try:
if len(landing_path) == 5:
country = landing_path[4]
# source_lang = landing_path[3]
else:
country = landing_path[3]
except:
logging.info("Could not parse country from landing path: %s", landing_url)
line = logFile.readline()
total_lines_in_file = total_lines_in_file - 1
continue
# http://wikimediafoundation.org/w/index.php?
elif url_match == 2:
try:
""" URLs of the form ...?title=<lp_name> """
lp_country = query_fields["title"][0].split("/")
landing_page = lp_country[0]
""" URLs of the form ...?county_code=<iso_code> """
try:
country = query_fields["country"][0]
except:
""" URLs of the form ...?title=<lp_name>/<lang>/<iso_code> """
if len(lp_country) == 3:
country = lp_country[2]
else:
country = lp_country[1]
except:
logging.info("Could not parse landing page request from query string: %s", landing_url)
line = logFile.readline()
total_lines_in_file = total_lines_in_file - 1
continue
# donate.wikimedia.org/wiki/Special:FundraiserLandingPage?
elif url_match == 3:
try:
# e.g. uselang=en&country=US&template=Lp-layout-default&appeal=Appeal-default&form-countryspecific=Form-countryspecific-control&utm_medium=sitenotice&utm_source=B11_Donate_Jimmy_Control&utm_campaign=C11_1107
source_lang = query_fields["uselang"][0]
country = query_fields["country"][0]
landing_page = (
query_fields["template"][0].split("-")[2]
+ "~"
+ query_fields["appeal-template"][0].split("-")[2]
+ "~"
+ query_fields["appeal"][0].split("-")[1]
+ "~"
+ query_fields["form-template"][0].split("-")[2]
+ "~"
+ query_fields["form-countryspecific"][0].split("-")[2]
)
utm_source = query_fields["utm_source"][0]
utm_campaign = query_fields["utm_campaign"][0] + "_" + country
utm_medium = query_fields["utm_medium"][0]
except Exception as inst:
# logging.info(inst) # __str__ allows args to printed directly
# logging.info('Could not parse landing page request from query string: %s', landing_url)
line = logFile.readline()
total_lines_in_file = total_lines_in_file - 1
continue
""" If country is confused with the language use the ip """
if country == country.lower():
# logging.info('Using geo-locator to set ip-address: %s', landing_url)
country = ipctl.localize_IP(ip_add)
""" Ensure fields providing request ID exist """
try:
utm_source = query_fields["utm_source"][0]
utm_campaign = query_fields["utm_campaign"][0]
utm_medium = query_fields["utm_medium"][0]
except KeyError:
line = logFile.readline()
total_lines_in_file = total_lines_in_file - 1
continue
""" Insert record into the landing_page_requests table """
lptl.insert_row(
utm_source_arg=utm_source,
utm_campaign_arg=utm_campaign,
utm_medium_arg=utm_medium,
landing_page_arg=landing_page,
page_url_arg=landing_url,
referrer_url_arg=referrer_url,
browser_arg=browser,
lang_arg=source_lang,
country_arg=country,
project_arg=project,
ip_arg=ip_add,
start_timestamp_arg=start,
timestamp_arg=timestamp_string,
)
requests_loaded = requests_loaded + 1
line = logFile.readline()
line_count = line_count + 1
""" Log Miner Logging - Update the squid_log_record table """
if (line_count % 1000) == 0 or line_count == total_lines_in_file:
completion = float(line_count / total_lines_in_file) * 100.0
sltl.update_table_row(
type="lp_view",
log_copy_time=curr_time,
start_time=start,
end_time=end,
log_completion_pct=completion.__str__(),
total_rows=line_count.__str__(),
)
def mine_squid_impression_requests(self, logFileName):
logging.info("Begin mining of banner impressions in %s" % logFileName)
sltl = DL.SquidLogTableLoader()
itl = DL.ImpressionTableLoader()
""" Retrieve the log timestamp from the filename """
time_stamps = self.get_timestamps_with_interval(logFileName, self._log_copy_interval_)
""" retrieve the start time of the log """
start = self.get_first_timestamp_from_log(logFileName)
end = time_stamps[1]
curr_time = TP.timestamp_from_obj(datetime.datetime.now(), 1, 3)
""" Initialization - open the file """
logFile, total_lines_in_file = self.open_logfile(logFileName)
queryIndex = 4
counts = Hlp.AutoVivification()
# insertStmt = 'INSERT INTO ' + self._impression_table_name_ + self._BANNER_FIELDS_ + ' values '
""" Clear the old records """
self._clear_squid_records(start, self._BANNER_REQUEST_)
""" Add a row to the SquidLogTable """
sltl.insert_row(
type="banner_impression",
log_copy_time=curr_time,
start_time=start,
end_time=end,
log_completion_pct="0.0",
total_rows="0",
)
"""
PROCESS LOG FILE
================
Sample Request:
line =
"sq63.wikimedia.org 757675855 2011-06-01T23:00:07.612 0 187.57.227.121 TCP_MEM_HIT/200 1790 GET \
http://meta.wikimedia.org/w/index.php?title=Special:BannerLoader&banner=B20110601_JWJN001_BR&userlang=pt&db=ptwiki&sitename=Wikip%C3%A9dia&country=BR NONE/- text/javascript http://pt.wikipedia.org/wiki/Modo_e_tempo_verbal \
- Mozilla/5.0%20(Windows%20NT%206.1)%20AppleWebKit/534.24%20(KHTML,%20like%20Gecko)%20Chrome/11.0.696.71%20Safari/534.24"
"""
line_count = 0
line = logFile.readline()
while line != "":
lineArgs = line.split()
"""
Parse the Timestamp:
Sample timestamp:
timestamp = "2011-06-01T23:00:07.612"
"""
try:
time_stamp = lineArgs[2]
time_bits = time_stamp.split("T")
date_fields = time_bits[0].split("-")
time_fields = time_bits[1].split(":")
time_stamp = date_fields[0] + date_fields[1] + date_fields[2] + time_fields[0] + time_fields[1] + "00"
except (ValueError, IndexError):
line = logFile.readline()
total_lines_in_file = total_lines_in_file - 1
continue
# pass
"""
Parse the URL:
Sample url:
url = "http://meta.wikimedia.org/w/index.php?title=Special:BannerLoader&banner=B20110601_JWJN001_BR&userlang=pt&db=ptwiki&sitename=Wikip%C3%A9dia&country=BR"
"""
try:
url = lineArgs[8]
except IndexError:
url = "Unavailable"
parsedUrl = up.urlparse(url)
query = parsedUrl[queryIndex]
queryBits = cgi.parse_qs(query)
""" Extract - project, banner, language, & country data from the url """
project = ""
if "db" in queryBits.keys():
project = queryBits["db"][0]
if project == "" and "sitename" in queryBits.keys():
sitename = queryBits["sitename"][0]
if sitename:
project = sitename
else:
project = "NONE"
if "banner" in queryBits.keys():
banner = queryBits["banner"][0]
else:
banner = "NONE"
if "userlang" in queryBits.keys():
lang = queryBits["userlang"][0]
else:
lang = "NONE"
if "country" in queryBits.keys():
country = queryBits["country"][0]
else:
country = "NONE"
""" Group banner impression counts based on (banner, country, project, language) """
try:
counts[banner][country][project][lang][time_stamp] = (
counts[banner][country][project][lang][time_stamp] + 1
)
except TypeError:
counts[banner][country][project][lang][time_stamp] = 1
line = logFile.readline()
line_count = line_count + 1
""" Log Miner Logging - Update the squid_log_record table """
if line_count % 10000 == 0 or line_count == total_lines_in_file:
completion = float(line_count / total_lines_in_file) * 100.0
sltl.update_table_row(
type="banner_impression",
log_copy_time=curr_time,
start_time=start,
end_time=end,
log_completion_pct=completion.__str__(),
total_rows=line_count.__str__(),
)
""" ====== FILE COMPLETE ====== """
logFile.close()
"""
Break out impression data by minute. This conditional detects when a request with a previously unseen minute in the timestamp appears.
Run through the counts dictionary and insert a row into the banner impressions table for each entry
"""
bannerKeys = counts.keys()
for banner_ind in range(len(bannerKeys)):
banner = bannerKeys[banner_ind]
countryCounts = counts[banner]
countryKeys = countryCounts.keys()
for country_ind in range(len(countryKeys)):
country = countryKeys[country_ind]
projectCounts = countryCounts[country]
projectKeys = projectCounts.keys()
for project_ind in range(len(projectKeys)):
project = projectKeys[project_ind]
langCounts = projectCounts[project]
langKeys = langCounts.keys()
for lang_ind in range(len(langKeys)):
lang = langKeys[lang_ind]
timestampCounts = langCounts[lang]
timestampKeys = timestampCounts.keys()
for timestamp_ind in range(len(timestampKeys)):
timestamp = timestampKeys[timestamp_ind]
count = timestampCounts[timestamp]
itl.insert_row(
utm_source_arg=banner,
referrer_arg=project,
country_arg=country,
lang_arg=lang,
counts_arg=str(count),
on_minute_arg=timestamp,
start_timestamp_arg=start,
)
def test(request):
try:
"""
PROCESS POST DATA
=================
Escape all user input that can be entered in text fields
"""
test_name_var = MySQLdb._mysql.escape_string(request.POST['test_name'].strip())
utm_campaign_var = MySQLdb._mysql.escape_string(request.POST['utm_campaign'].strip())
start_time_var = MySQLdb._mysql.escape_string(request.POST['start_time'].strip())
end_time_var = MySQLdb._mysql.escape_string(request.POST['end_time'].strip())
one_step_var = MySQLdb._mysql.escape_string(request.POST['one_step'].strip())
country = MySQLdb._mysql.escape_string(request.POST['iso_filter'])
""" Convert timestamp format if necessary """
if TP.is_timestamp(start_time_var, 2):
start_time_var = TP.timestamp_convert_format(start_time_var, 2, 1)
if TP.is_timestamp(end_time_var, 2):
end_time_var = TP.timestamp_convert_format(end_time_var, 2, 1)
if cmp(one_step_var, 'True') == 0:
one_step_var = True
else:
one_step_var = False
try:
test_type_var = MySQLdb._mysql.escape_string(request.POST['test_type'])
labels = request.POST['artifacts']
except KeyError:
test_type_var, labels = FDH.get_test_type(utm_campaign_var, start_time_var, end_time_var, DL.CampaignReportingLoader(query_type='')) # submit an empty query type
labels = labels.__str__()
label_dict = dict()
label_dict_full = dict()
labels = labels[1:-1].split(',')
""" Parse the labels """
for i in range(len(labels)):
labels[i] = labels[i]
label = labels[i].split('\'')[1]
label = label.strip()
pieces = label.split(' ')
label = pieces[0]
for j in range(len(pieces) - 1):
label = label + '_' + pieces[j+1]
""" Escape the label parameters """
label = MySQLdb._mysql.escape_string(label)
label_dict_full[label] = label
""" Look at the artifact names and map them into a dict() - Determine if artifacts were chosen by the user """
if request.POST.__contains__('artifacts_chosen'):
artifacts_chosen = request.POST.getlist('artifacts_chosen')
""" Ensure that only two items are selected """
if len(artifacts_chosen) > 2:
raise Exception('Please select (checkboxes) exactly two items to test')
for elem in artifacts_chosen:
esc_elem = MySQLdb._mysql.escape_string(str(elem))
label_dict[esc_elem] = esc_elem
else:
label_dict = label_dict_full
""" Parse the added labels IF they are not empty """
for key in label_dict.keys():
try:
if not(request.POST[key] == ''):
label_dict[key] = MySQLdb._mysql.escape_string(str(request.POST[key]))
else:
label_dict[key] = key
except:
logging.error('Could not find %s in the POST QueryDict.' % key)
for key in label_dict_full.keys():
try:
if not(request.POST[key] == ''):
label_dict_full[key] = MySQLdb._mysql.escape_string(str(request.POST[key]))
else:
label_dict_full[key] = key
except:
logging.error('Could not find %s in the POST QueryDict.' % key)
"""
EXECUTE REPORT GENERATION
=========================
setup time parameters
determine test metrics
execute queries
"""
sample_interval = 1
start_time_obj = TP.timestamp_to_obj(start_time_var, 1)
end_time_obj = TP.timestamp_to_obj(end_time_var, 1)
time_diff = end_time_obj - start_time_obj
time_diff_min = time_diff.seconds / 60.0
test_interval = int(math.floor(time_diff_min / sample_interval)) # 2 is the interval
metric_types = FDH.get_test_type_metrics(test_type_var)
metric_types_full = dict()
""" Get the full (descriptive) version of the metric names
!! FIXME / TODO -- order these properly !! """
for i in range(len(metric_types)):
metric_types_full[metric_types[i]] = QD.get_metric_full_name(metric_types[i])
""" USE generate_reporting_objects() TO GENERATE THE REPORT DATA - dependent on test type """
measured_metric, winner, loser, percent_win, confidence, html_table_pm_banner, html_table_pm_lp, html_table_language, html_table \
= generate_reporting_objects(test_name_var, start_time_var, end_time_var, utm_campaign_var, label_dict, label_dict_full, \
sample_interval, test_interval, test_type_var, metric_types, one_step_var, country)
winner_var = winner[0]
results = list()
for index in range(len(winner)):
results.append({'metric' : measured_metric[index], 'winner' : winner[index], 'loser': loser[index], 'percent_win' : percent_win[index], 'confidence' : confidence[index]})
template_var_dict = {'results' : results, \
'utm_campaign' : utm_campaign_var, 'metric_names_full' : metric_types_full, \
'summary_table': html_table, 'sample_interval' : sample_interval, \
'banner_pm_table' : html_table_pm_banner, 'lp_pm_table' : html_table_pm_lp, 'html_table_language' : html_table_language, \
'start_time' : TP.timestamp_convert_format(start_time_var, 1, 2) , 'end_time' : TP.timestamp_convert_format(end_time_var, 1, 2)}
html = render_to_response('tests/results_' + test_type_var + '.html', template_var_dict, context_instance=RequestContext(request))
"""
WRITE TO TEST TABLE
===================
"""
ttl = DL.TestTableLoader()
""" Format the html string """
html_string = html.__str__()
html_string = html_string.replace('"', '\\"')
if ttl.record_exists(utm_campaign=utm_campaign_var):
ttl.update_test_row(test_name=test_name_var,test_type=test_type_var,utm_campaign=utm_campaign_var,start_time=start_time_var,end_time=end_time_var,html_report=html_string, winner=winner_var)
else:
ttl.insert_row(test_name=test_name_var,test_type=test_type_var,utm_campaign=utm_campaign_var,start_time=start_time_var,end_time=end_time_var,html_report=html_string, winner=winner_var)
return html
except Exception as inst:
logging.error('Failed to correctly generate test report.')
logging.error(type(inst))
logging.error(inst.args)
logging.error(inst)
""" Return to the index page with an error """
try:
err_msg = 'Test Generation failed for: %s. Check the fields submitted for generation. <br><br>ERROR:<br><br>%s' % (utm_campaign_var, inst.__str__())
except:
err_msg = 'Test Generation failed. Check the fields submitted for generation. <br><br>ERROR:<br><br>%s' % inst.__str__()
return campaigns_index(request, kwargs={'err_msg' : err_msg})
return show_campaigns(request, utm_campaign_var, kwargs={'err_msg' : err_msg})
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 index(request, **kwargs):
"""
PROCESS POST DATA
=================
"""
if 'err_msg' in kwargs:
err_msg = kwargs['err_msg']
else:
err_msg = ''
try:
latest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST['latest_utc_ts'].strip())
earliest_utc_ts_var = MySQLdb._mysql.escape_string(request.POST['earliest_utc_ts'].strip())
if not TP.is_timestamp(earliest_utc_ts_var, 1) or not TP.is_timestamp(earliest_utc_ts_var, 1):
raise TypeError
if latest_utc_ts_var == '':
latest_utc_ts_var = _end_time_
except KeyError:
earliest_utc_ts_var = _beginning_time_
latest_utc_ts_var = _end_time_
except TypeError:
err_msg = 'Please enter a valid timestamp.'
earliest_utc_ts_var = _beginning_time_
latest_utc_ts_var = _end_time_
ttl = DL.TestTableLoader()
columns = ttl.get_column_names()
test_rows = ttl.get_all_test_rows()
""" Build a list of tests -- apply filters """
l = []
utm_campaign_index = ttl.get_test_index('utm_campaign')
html_report_index = ttl.get_test_index('html_report')
for i in test_rows:
test_start_time = ttl.get_test_field(i, 'start_time')
new_row = list(i)
""" Ensure the timestamp is properly formatted """
if TP.is_timestamp(test_start_time, 2):
test_start_time = TP.timestamp_convert_format(test_start_time, 2, 1)
new_row[html_report_index] = '<a href="/tests/report/%s">view</a>' % new_row[utm_campaign_index]
if int(test_start_time) > int(earliest_utc_ts_var) and int(test_start_time) < int(latest_utc_ts_var):
l.append(new_row)
l.reverse()
test_table = DR.DataReporting()._write_html_table(l, columns, use_standard_metric_names=True)
return render_to_response('tests/index.html', {'err_msg' : err_msg, 'test_table' : test_table}, context_instance=RequestContext(request))
def generate_summary(request):
try:
err_msg = ''
"""
PROCESS POST DATA
=================
Escape all user input that can be entered in text fields
"""
if 'utm_campaign' in request.POST:
utm_campaign = MySQLdb._mysql.escape_string(request.POST['utm_campaign'])
if 'start_time' in request.POST:
start_time = MySQLdb._mysql.escape_string(request.POST['start_time'].strip())
if not(TP.is_timestamp(start_time, 1)) and not(TP.is_timestamp(start_time, 2)):
err_msg = 'Incorrectly formatted start timestamp.'
raise Exception()
if 'end_time' in request.POST:
end_time = MySQLdb._mysql.escape_string(request.POST['end_time'].strip())
if not(TP.is_timestamp(end_time, 1)) and not(TP.is_timestamp(end_time, 2)):
err_msg = 'Incorrectly formatted end timestamp.'
raise Exception()
if 'iso_filter' in request.POST:
country = MySQLdb._mysql.escape_string(request.POST['iso_filter'])
else:
country = '.{2}'
if 'measure_confidence' in request.POST:
if cmp(request.POST['measure_confidence'], 'yes') == 0:
measure_confidence = True
else:
measure_confidence = False
else:
measure_confidence = False
if 'one_step' in request.POST:
if cmp(request.POST['one_step'], 'yes') == 0:
use_one_step = True
else:
use_one_step = False
else:
use_one_step = False
if 'donations_only' in request.POST:
if cmp(request.POST['donations_only'], 'yes') == 0:
donations_only = True
else:
donations_only = False
else:
donations_only = False
""" Convert timestamp format if necessary """
if TP.is_timestamp(start_time, 2):
start_time = TP.timestamp_convert_format(start_time, 2, 1)
if TP.is_timestamp(end_time, 2):
end_time = TP.timestamp_convert_format(end_time, 2, 1)
""" =============================================== """
"""
GENERATE A REPORT SUMMARY TABLE
===============================
"""
if donations_only:
srl = DL.SummaryReportingLoader(query_type=FDH._TESTTYPE_DONATIONS_)
else:
srl = DL.SummaryReportingLoader(query_type=FDH._TESTTYPE_BANNER_LP_)
srl.run_query(start_time, end_time, utm_campaign, min_views=-1, country=country)
column_names = srl.get_column_names()
summary_results = srl.get_results()
if not(summary_results):
html_table = '<h3>No artifact summary data available for %s.</h3>' % utm_campaign
else:
summary_results_list = list()
for row in summary_results:
summary_results_list.append(list(row))
summary_results = summary_results_list
"""
Format results to encode html table cell markup in results
"""
if measure_confidence:
ret = DR.ConfidenceReporting(query_type='', hyp_test='').get_confidence_on_time_range(start_time, end_time, utm_campaign, one_step=use_one_step, country=country) # first get color codes on confidence
conf_colour_code = ret[0]
for row_index in range(len(summary_results)):
artifact_index = summary_results[row_index][0] + '-' + summary_results[row_index][1] + '-' + summary_results[row_index][2]
for col_index in range(len(column_names)):
is_coloured_cell = False
if column_names[col_index] in conf_colour_code.keys():
if artifact_index in conf_colour_code[column_names[col_index]].keys():
summary_results[row_index][col_index] = '<td style="background-color:' + conf_colour_code[column_names[col_index]][artifact_index] + ';">' + str(summary_results[row_index][col_index]) + '</td>'
is_coloured_cell = True
if not(is_coloured_cell):
summary_results[row_index][col_index] = '<td>' + str(summary_results[row_index][col_index]) + '</td>'
html_table = DR.DataReporting()._write_html_table(summary_results, column_names, use_standard_metric_names=True, omit_cell_markup=True)
else:
html_table = DR.DataReporting()._write_html_table(summary_results, column_names, use_standard_metric_names=True)
""" Generate totals only if it's a non-donation-only query """
if donations_only:
srl = DL.SummaryReportingLoader(query_type=FDH._QTYPE_TOTAL_DONATIONS_)
else:
srl = DL.SummaryReportingLoader(query_type=FDH._QTYPE_TOTAL_)
srl.run_query(start_time, end_time, utm_campaign, min_views=-1, country=country)
total_summary_results = srl.get_results()
if not(total_summary_results):
html_table = html_table + '<div class="spacer"></div><div class="spacer"></div><h3>No data available for %s Totals.</h3>' % utm_campaign
else:
html_table = html_table + '<div class="spacer"></div><div class="spacer"></div>' + DR.DataReporting()._write_html_table(total_summary_results, srl.get_column_names(), use_standard_metric_names=True)
metric_legend_table = DR.DataReporting().get_standard_metrics_legend()
conf_legend_table = DR.ConfidenceReporting(query_type='bannerlp', hyp_test='TTest').get_confidence_legend_table()
html_table = '<h4><u>Metrics Legend:</u></h4><div class="spacer"></div>' + metric_legend_table + \
'<div class="spacer"></div><h4><u>Confidence Legend for Hypothesis Testing:</u></h4><div class="spacer"></div>' + conf_legend_table + '<div class="spacer"></div><div class="spacer"></div>' + html_table
"""
DETERMINE PAYMENT METHODS
=========================
"""
ccl = DL.CiviCRMLoader()
pm_data_counts, pm_data_conversions = ccl.get_payment_methods(utm_campaign, start_time, end_time, country=country)
html_table_pm_counts = DR.IntervalReporting().write_html_table_from_rowlists(pm_data_counts, ['Payment Method', 'Portion of Donations (%)'], 'Landing Page')
html_table_pm_conversions = DR.IntervalReporting().write_html_table_from_rowlists(pm_data_conversions, ['Payment Method', 'Visits', 'Conversions', 'Conversion Rate (%)', 'Amount', 'Amount 25'], 'Landing Page')
html_table = html_table + '<div class="spacer"></div><h4><u>Payment Methods Breakdown:</u></h4><div class="spacer"></div>' + html_table_pm_counts + \
'<div class="spacer"></div><div class="spacer"></div>' + html_table_pm_conversions + '<div class="spacer"></div><div class="spacer"></div>'
return render_to_response('tests/table_summary.html', {'html_table' : html_table, 'utm_campaign' : utm_campaign}, context_instance=RequestContext(request))
except Exception as inst:
if cmp(err_msg, '') == 0:
err_msg = 'Could not generate campaign tabular results.'
return index(request, err_msg=err_msg)
def execute_process(self, key, **kwargs):
logging.info('Commencing caching of long term trends data at: %s' % self.CACHING_HOME)
end_time, start_time = TP.timestamps_for_interval(datetime.datetime.utcnow(), 1, \
hours=-self.VIEW_DURATION_HRS, resolution=1)
""" DATA CONFIG """
countries = DL.CiviCRMLoader().get_ranked_donor_countries(start_time)
countries = countries[1:6]
""" set the metrics to plot """
lttdl = DL.LongTermTrendsLoader(db='storage3')
""" Dictionary object storing lists of regexes - each expression must pass for a label to persist """
# country_groups = {'US': ['(US)'], 'CA': ['(CA)'], 'JP': ['(JP)'], 'IN': ['(IN)'], 'NL': ['(NL)']}
payment_groups = {'Credit Card' : ['^cc$'], 'Paypal': ['^pp$']}
currency_groups = {'USD' : ['(USD)'], 'CAD': ['(CAD)'], 'JPY': ['(JPY)'], 'EUR': ['(EUR)']}
lang_cntry_groups = {'US': ['US..', '.{4}'], 'EN' : ['[^U^S]en', '.{4}']}
top_cntry_groups = dict()
for country in countries:
top_cntry_groups[country] = [country, '.{2}']
# To include click rate
# groups = [ lang_cntry_groups] metrics = ['click_rate'] metrics_index = [3]
# group_metrics = [DL.LongTermTrendsLoader._MT_RATE_] metric_types = ['country', 'language'] include_totals = [True] include_others = [True]
metrics = ['impressions', 'views', 'donations', 'donations', 'amount', 'amount', 'diff_don', 'diff_don', 'donations', 'conversion_rate']
weights = ['', '', '', '', '', '', 'donations', 'donations', '', '']
metrics_index = [0, 1, 2, 2, 2, 4, 5, 5, 6, 6]
groups = [lang_cntry_groups, lang_cntry_groups, lang_cntry_groups, top_cntry_groups, lang_cntry_groups, currency_groups, \
lang_cntry_groups, lang_cntry_groups, payment_groups, payment_groups]
""" The metrics that are used to build a group string to be qualified via regex - the values of the list metrics are concatenated """
group_metrics = [['country', 'language'], ['country', 'language'], ['country', 'language'], \
['country', 'language'], ['country', 'language'], ['currency'], ['country', 'language'], \
['country', 'language'], ['payment_method'], ['payment_method']]
metric_types = [DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, \
DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, DL.LongTermTrendsLoader._MT_AMOUNT_, \
DL.LongTermTrendsLoader._MT_RATE_WEIGHTED_, DL.LongTermTrendsLoader._MT_RATE_WEIGHTED_, DL.LongTermTrendsLoader._MT_AMOUNT_, \
DL.LongTermTrendsLoader._MT_RATE_]
include_totals = [True, True, True, False, True, True, False, False, False, True]
include_others = [True, True, True, False, True, True, True, True, True, False]
hours_back = [0, 0, 0, 0, 0, 0, 24, 168, 0, 0]
time_unit = [TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR, TP.HOUR]
data = list()
""" END CONFIG """
""" For each metric use the LongTermTrendsLoader to generate the data to plot """
for index in range(len(metrics)):
dr = DR.DataReporting()
times, counts = lttdl.run_query(start_time, end_time, metrics_index[index], metric_name=metrics[index], metric_type=metric_types[index], \
groups=groups[index], group_metric=group_metrics[index], include_other=include_others[index], \
include_total=include_totals[index], hours_back=hours_back[index], weight_name=weights[index], \
time_unit=time_unit[index])
times = TP.normalize_timestamps(times, False, time_unit[index])
dr._counts_ = counts
dr._times_ = times
empty_data = [0] * len(times[times.keys()[0]])
data.append(dr.get_data_lists([''], empty_data))
dict_param = Hlp.combine_data_lists(data)
dict_param['interval'] = self.VIEW_DURATION_HRS
dict_param['end_time'] = TP.timestamp_convert_format(end_time,1,2)
self.clear_cached_data(key)
self.cache_data(dict_param, key)
logging.info('Caching complete.')
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 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 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 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))