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