def alert_display(): """ Display alert statistics. """ tbl_name = get_customevent_table("alerts") if not tbl_name: # custom event alerts not defined, so return empty output: return [] try: res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" % tbl_name) days = (res[-1][0] - res[0][0]).days + 1 res = run_sql("SELECT COUNT(DISTINCT user),COUNT(*) FROM %s" % tbl_name) users = res[0][0] hits = res[0][1] displays = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'list'" % tbl_name)[0][0] search = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display'" % tbl_name)[0][0] average = hits / days res = [("Alerts page hits", hits)] res.append((" Average per day", average)) res.append((" Unique users", users)) res.append((" Displays", displays)) res.append((" Searches history display", search)) except IndexError: res = [] return res
def destroy_customevent(id=None): """ Removes an existing custom event by destroying the MySQL tables and the event data that might be around. Use with caution! @param id: Human-readable id of the event to be removed. @type id: str @return: A status message @type: str """ if id is None: return "Please specify an existing event id." # Check if the specified id exists if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id,))) == 0: return "Event id [%s] doesn't exist! Aborted." % id else: tbl_name = get_customevent_table(id) run_sql("DROP TABLE %s" % tbl_name) run_sql("DELETE FROM staEVENT WHERE id = %s", (id,)) return ("Event with id [%s] was successfully destroyed.\n" + "Table [%s], with content, was destroyed.") % ( id, tbl_name, )
def basket_display(): """ Display basket statistics. """ tbl_name = get_customevent_table("baskets") if not tbl_name: # custom event baskets not defined, so return empty output: return [] try: res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" % tbl_name) days = (res[-1][0] - res[0][0]).days + 1 public = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display_public'" % tbl_name)[0][0] users = run_sql("SELECT COUNT(DISTINCT user) FROM %s" % tbl_name)[0][0] adds = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'add'" % tbl_name)[0][0] displays = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display' OR action = 'display_public'" % tbl_name)[0][0] hits = adds + displays average = hits / days res = [("Basket page hits", hits)] res.append((" Average per day", average)) res.append((" Unique users", users)) res.append((" Additions", adds)) res.append((" Public", public)) except IndexError: res = [] return res
def alert_display(): """ Display alert statistics. """ tbl_name = get_customevent_table("alerts") if not tbl_name: # custom event alerts not defined, so return empty output: return [] try: res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" % tbl_name) days = (res[-1][0] - res[0][0]).days + 1 res = run_sql("SELECT COUNT(DISTINCT user),COUNT(*) FROM %s" % tbl_name) users = res[0][0] hits = res[0][1] displays = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'list'" % tbl_name)[0][0] search = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'display'" % tbl_name)[0][0] average = hits / days res = [("Alerts page hits", hits)] res.append((" Average per day", average)) res.append((" Unique users", users)) res.append((" Displays", displays)) res.append((" Searches history display", search)) except IndexError: res = [] return res
def basket_display(): """ Display basket statistics. """ tbl_name = get_customevent_table("baskets") if not tbl_name: # custom event baskets not defined, so return empty output: return [] try: res = run_sql("SELECT creation_time FROM %s ORDER BY creation_time" % tbl_name) days = (res[-1][0] - res[0][0]).days + 1 public = run_sql( "SELECT COUNT(*) FROM %s WHERE action = 'display_public'" % tbl_name)[0][0] users = run_sql("SELECT COUNT(DISTINCT user) FROM %s" % tbl_name)[0][0] adds = run_sql("SELECT COUNT(*) FROM %s WHERE action = 'add'" % tbl_name)[0][0] displays = run_sql( "SELECT COUNT(*) FROM %s WHERE action = 'display' OR action = 'display_public'" % tbl_name)[0][0] hits = adds + displays average = hits / days res = [("Basket page hits", hits)] res.append((" Average per day", average)) res.append((" Unique users", users)) res.append((" Additions", adds)) res.append((" Public", public)) except IndexError: res = [] return res
def destroy_customevent(id=None): """ Removes an existing custom event by destroying the MySQL tables and the event data that might be around. Use with caution! @param id: Human-readable id of the event to be removed. @type id: str @return: A status message @type: str """ if id is None: return "Please specify an existing event id." # Check if the specified id exists if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id, ))) == 0: return "Event id [%s] doesn't exist! Aborted." % id else: tbl_name = get_customevent_table(id) run_sql("DROP TABLE %s" % tbl_name) run_sql("DELETE FROM staEVENT WHERE id = %s", (id, )) return ("Event with id [%s] was successfully destroyed.\n" + "Table [%s], with content, was destroyed.") % (id, tbl_name)
def create_customevent(id=None, name=None, cols=[]): """ Creates a new custom event by setting up the necessary MySQL tables. @param id: Proposed human-readable id of the new event. @type id: str @param name: Optionally, a descriptive name. @type name: str @param cols: Optionally, the name of the additional columns. @type cols: [str] @return: A status message @type: str """ if id is None: return "Please specify a human-readable ID for the event." # Only accept id and name with standard characters if not re.search("[^\w]", str(id) + str(name)) is None: return "Please note that both event id and event name needs to be written without any non-standard characters." # Make sure the chosen id is not already taken if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id,))) != 0: return "Event id [%s] already exists! Aborted." % id # Check if the cols are valid titles for argument in cols: if (argument == "creation_time") or (argument == "id"): return "Invalid column title: %s! Aborted." % argument # Insert a new row into the events table describing the new event sql_param = [id] if name is not None: sql_name = "%s" sql_param.append(name) else: sql_name = "NULL" if len(cols) != 0: sql_cols = "%s" sql_param.append(cPickle.dumps(cols)) else: sql_cols = "NULL" run_sql("INSERT INTO staEVENT (id, name, cols) VALUES (%s, " + sql_name + ", " + sql_cols + ")", tuple(sql_param)) tbl_name = get_customevent_table(id) # Create a table for the new event sql_query = ["CREATE TABLE %s (" % tbl_name] sql_query.append("id MEDIUMINT unsigned NOT NULL auto_increment,") sql_query.append("creation_time TIMESTAMP DEFAULT NOW(),") for argument in cols: arg = wash_table_column_name(argument) sql_query.append("%s MEDIUMTEXT NULL," % arg) sql_query.append("INDEX %s (%s(50))," % (arg, arg)) sql_query.append("PRIMARY KEY (id))") sql_str = ' '.join(sql_query) run_sql(sql_str) # We're done! Print notice containing the name of the event. return ("Event table [%s] successfully created.\n" + "Please use event id [%s] when registering an event.") % (tbl_name, id)
def create_customevent(id=None, name=None, cols=[]): """ Creates a new custom event by setting up the necessary MySQL tables. @param id: Proposed human-readable id of the new event. @type id: str @param name: Optionally, a descriptive name. @type name: str @param cols: Optionally, the name of the additional columns. @type cols: [str] @return: A status message @type: str """ if id is None: return "Please specify a human-readable ID for the event." # Only accept id and name with standard characters if not re.search("[^\w]", str(id) + str(name)) is None: return "Please note that both event id and event name needs to be written without any non-standard characters." # Make sure the chosen id is not already taken if len(run_sql("SELECT NULL FROM staEVENT WHERE id = %s", (id, ))) != 0: return "Event id [%s] already exists! Aborted." % id # Check if the cols are valid titles for argument in cols: if (argument == "creation_time") or (argument == "id"): return "Invalid column title: %s! Aborted." % argument # Insert a new row into the events table describing the new event sql_param = [id] if name is not None: sql_name = "%s" sql_param.append(name) else: sql_name = "NULL" if len(cols) != 0: sql_cols = "%s" sql_param.append(cPickle.dumps(cols)) else: sql_cols = "NULL" run_sql( "INSERT INTO staEVENT (id, name, cols) VALUES (%s, " + sql_name + ", " + sql_cols + ")", tuple(sql_param)) tbl_name = get_customevent_table(id) # Create a table for the new event sql_query = ["CREATE TABLE %s (" % tbl_name] sql_query.append("id MEDIUMINT unsigned NOT NULL auto_increment,") sql_query.append("creation_time TIMESTAMP DEFAULT NOW(),") for argument in cols: arg = wash_table_column_name(argument) sql_query.append("%s MEDIUMTEXT NULL," % arg) sql_query.append("INDEX %s (%s(50))," % (arg, arg)) sql_query.append("PRIMARY KEY (id))") sql_str = ' '.join(sql_query) run_sql(sql_str) # We're done! Print notice containing the name of the event. return ("Event table [%s] successfully created.\n" + "Please use event id [%s] when registering an event.") % (tbl_name, id)