show_subset.py

from helper import generate_ui
from collections import OrderedDict


database = 'suggestion_box.db'
input_prompt = 'Select a function (enter a number or enter "q" to exit).\n'
again_prompt = 'Would you like to select another function (Enter "y" to ' +\
               'start again)?\n'


# After a user enters 1 (for active) or 0 (for inactive), a statement is
# executed to find all active or inactive users
def filter_active_users(conn):
    is_active = input('Type 0 to view inactive users or 1 to view active ' +
                      'users.\n')
    while is_active not in ('0', '1'):
        is_active = input('Please enter 0 or 1.\n')

    return conn.execute(
        '''
        SELECT username, full_name, email, password
        FROM user WHERE is_active=%s
        '''
        % is_active
    )


# A user will search the user table for a certain username.
def find_user(conn):
    username = input('Enter a username to lookup.\n')
    return conn.execute(
        '''
        SELECT username, full_name, email, password
        FROM user WHERE username=\'%s\'
        '''
        % username
    )


# The statement will find suggestions in the case that a user wants to be
# contacted and also hasn't been contacted yet.
def filter_to_be_contacted(conn):
    return conn.execute(
        '''
        SELECT author, email, phone, text, timestamp
        FROM suggestion WHERE should_contact=1 AND contacted=0
        '''
    )


# The result set is all suggestions that don't have any responses
def filter_suggestions_without_responses(conn):
    return conn.execute(
        '''
        SELECT author, email, phone, suggestion.text, suggestion.timestamp
        FROM suggestion
        LEFT JOIN response ON suggestion.id=suggestion_id
        GROUP BY suggestion.id
        HAVING COUNT(response.id)=0
        '''
    )


functions = OrderedDict([
    ('Filter Active Users', filter_active_users),
    ('Find User', find_user),
    ('Filter Suggestions With Contact Requests',
     filter_to_be_contacted),
    ('Filter Suggestions Without Responses',
     filter_suggestions_without_responses)
])


if __name__ == '__main__':
    generate_ui(database, functions, input_prompt, again_prompt)