text:
"""
Python Web Client to invoke the following functions from a Python Web Service:
0. Create users via apply project script?
1. Create users via join project script
2. Apply for new project after login
3. Join existing project after login
4. Approve Join Request
5. Create experiment
6. Start experiment

Later TODO:
1. Remove users and projects
"""
from ConfigParser import SafeConfigParser
import MySQLdb
import generateDeterUserId
import json
import web
import time
import sys
import os
import subprocess
import logging
import base64
import datetime
import re
import urllib
import string
import random

ADMIN = 'ncl'
EXPERIMENT_NOT_FOUND = 'no experiment found'
CONFIG_FILE = "config.ini"
CONFIG_DETER_URI_TITLE = "DETER_URI"
# need time delay to allow subprocess to finish processing
# in seconds
TIME_DELAY = 2
EXP_STATUS_DELAY = 30

# to encode characters not specify in urllib.quote
SPECIAL_CHARMAP_URLLIB = {'_': '%5F', '.': '%2E', '-': '%2D', '/': '%2F'}

urls = (
    '/login', 'login_users',
    '/joinProjectNewUsers', 'join_project_new_users',
    '/applyProjectNewUsers', 'apply_project_new_users',
    '/joinProject', 'join_project_existing_user',
    '/applyProject', 'apply_project_existing_user',
    '/changePassword', 'change_password',
    '/createExperiment', 'create_experiment',
    '/getRunningExperimentsCount', 'get_running_experiments_count',
    '/getSavedImages', 'get_saved_images',
    '/getCveImages', 'get_cve_images',
    '/getLoggedInUsersCount', 'get_logged_in_users',
    '/modifyNSFile', 'modify_ns_file',
    '/modifyExperimentSettings', 'modify_experiment_settings',
    '/saveImage', 'save_image',
    '/deleteImage', 'delete_image',
    '/startExperiment', 'start_experiment',
    '/stopExperiment', 'stop_experiment',
    '/deleteExperiment', 'delete_experiment',
    '/approveJoinRequest', 'approve_join_request',
    '/rejectJoinRequest', 'reject_join_request',
    '/approveProject', 'approve_project',
    '/rejectProject', 'reject_project',
    '/getExpDetails', 'get_exp_details',
    '/getExpStatus', 'get_exp_status2',
    '/getFreeNodes', 'get_free_nodes',
    '/getNodesStatus', 'get_nodes_status',
    '/getTotalNodes', 'get_total_nodes',
    '/getTopoThumbnail', 'get_topo_thumbnail',
    # '/getExpReport', 'get_exp_report_with_nodes',
    '/resetPassword', 'reset_password',
    # '/getUsageStatistics', 'get_usage_statistics',
    '/removeUserFromTeam', 'remove_user_from_team',
    '/getExperimentsByTeam', 'get_experiments_by_team',
    '/getExperiment', 'get_experiment',
    '/getPublicKeys', 'get_pub_keys',
    '/addPublicKey', 'add_pub_key',
    '/deletePublicKey', 'delete_pub_key',
    '/getReservation', 'get_reservation',
    '/releaseNodes', 'release_nodes',
    '/reserveNodes', 'reserve_nodes',
    '/getTeamExptStats', 'show_stats_by_project',
    '/addStudentsByEmail', 'add_students_by_email',
    '/changePasswordStudent', 'change_password_student',
    '/getProjectsWithReservation', 'show_projects_with_reservation',
    '/getNodeList', 'get_node_list'
)

app = web.application(urls, globals())

def escapeSpecialChars(str):
    global SPECIAL_CHARMAP_URLLIB
    str_list = [SPECIAL_CHARMAP_URLLIB.get(c, c) for c in str]
    return ''.join(str_list)

# escape certain bash special characters with backslash
# used for curl -F parameters
# Reference:
# [1] 'Quoting' https://www.freebsd.org/cgi/man.cgi?query=bash&apropos=0&sektion=1&manpath=FreeBSD+9.0-RELEASE+and+Ports&arch=default&format=html
# [2] https://stackoverflow.com/questions/4202538/python-escape-regex-special-characters
def escapeBashArguments(str):
    return re.sub(r'([\$\"\`\\])', r'\\\1', str)

# escape arguments in single quotes and adds a backslash to single quotes for used in command line arguments
# e.g. apple' = 'apple\''
# http://www.php2python.com/wiki/function.escapeshellarg/
def escapeShellArguments(str):
    return "\\'".join("'" + p + "'" for p in str.split("'"))

# encode strings that will be used for arguments in curl commands
# special characters are converted to their hex representations with a percentage-sign
# e.g. apple@ = apple"%"40
# used for curl -d parameters
def escapeUrl(str):
    # percent-encode the url
    urlencoded = urllib.quote(str)

    # urlencode does not encode '_.-/'
    urlencoded = escapeSpecialChars(urlencoded)

    return urlencoded.replace("%", "\"%\"")

###########################################################################################
################################### RESET PASSWORD #######################################
###########################################################################################
class reset_password:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        uid = parsed_json['uid']
        password = escapeShellArguments(parsed_json['password'])
        secret = parsed_json['secret']
        if secret != getAPISecret():
            return '{"msg" : "secret is not matched, nothing to be done"}'
        else:
            logging.info('Resetting password for user %s', uid)

            # use openssl to generate hash string for the password
            cmd1 = "openssl passwd -1 " + password
            pswd_hash = subprocess.check_output(cmd1, shell=True)
            if pswd_hash.startswith('$1$') == 'False':
                logging.warning('Failed to generate password hash: %s', pswd_hash)
                return '{"msg": "failed to generate password hash"}'

            # user deter cmd 'tbacct' to change password
            cmd2 = '/usr/testbed/sbin/tbacct passwd ' + uid + ' \'' + pswd_hash.rstrip() + '\''
            ret_val = subprocess.call(cmd2, shell=True)
            if ret_val != 0:
                logging.warning("Failed to exec cmd: %s", cmd2)
                return '{"msg": "failed to execute tbacct command"}'

            # update tbdb.pswd_expires to 1 year later
            pswd_expires = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(365), '%Y-%m-%d')
            cmd3 = 'mysql -e \"update tbdb.users set pswd_expires=\'' + pswd_expires + '\' where uid=\'' + uid + '\'\"'
            db_ret = subprocess.call(cmd3, shell=True)
            if db_ret != 0:
                logging.warning('Failed to update db: %s', cmd3)
                return '{"msg": "failed to update db"}'

            logging.info("Password was reset for user %s", uid)
            return '{"msg": "password was reset"}'

###########################################################################################
################################### USAGE STATISTICS #######################################
###########################################################################################
# class get_usage_statistics:
#     def POST(self):
#         data = web.data()
#         parsed_json = json.loads(data)
#         pid = parsed_json['pid']
#         start = parsed_json['start']
#         end = parsed_json['end']
#
#         logging.info('Getting usage statistics for project %s, start %s, end %s', pid, start, end)
#
#         # deter_uri = getDeterURI()
#         # cmd = 'curl --silent -k -d \"' + 'range=' + start + '-' + end + '&rtype=' + pid + '&Get=Get\" '+ deter_uri + 'measure_usage.php'
#         #
#         # out = subprocess.check_output(cmd, shell=True)
#         #
#         # if 'failed' in out:
#         # 	logging.warning('Failed to get usage statistics for %s, start %s, end %s; return \'?\'', pid, start, end)
#         # 	return '?'
#
#         begin = int(time.mktime(time.strptime(start + " 00:00:00", "%m/%d/%y %H:%M:%S")))
#         end = int(time.mktime(time.strptime(end + " 23:59:59", "%m/%d/%y %H:%M:%S")))
#         out = subprocess.check_output(["perl", "calcusage.pl", str(begin), str(end), pid])
#
#         usage = extract_usage_info(out, pid)
#         logging.info('Return %s', usage)
#         return usage

#
# def extract_usage_info(str, pid):
#     pattern = '
Project ' + pid.lower() + ' 	(\d+)
'
#     m = re.search(pattern, str)
#     if m:
#         return m.group(1)
#     return '?'

###########################################################################################
################################### CHANGE PASSWORD #######################################
###########################################################################################
class change_password:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        credential_dict = create_credential_dict(parsed_json)
        uid = credential_dict['uid']
        password = credential_dict['password1']
        deter_uri = getDeterURI()
        logging.info('Changing password for %s', uid)
        cookie_file = uid + '.txt'

        password_safe = urllib.quote_plus(password)

        cmd_line = "curl -L -s -k -b " + cookie_file + " -d \"formfields[password1]=" + password_safe + \
                   "&formfields[password2]=" + password_safe + \
                   "&submit=Submit" + "\" " + \
                   deter_uri + "moduserinfo.php?user=" + uid + " -v"

        out = subprocess.check_output(cmd_line, shell=True)

        if 'Done!' in out:
            logging.info('Password changed for %s', uid)
            login(uid, password)
            return '{"msg":"password change success"}'
        else:
            logging.warning('Change password for %s failed: %s', uid, out)
            return '{"msg":"password change fail"}'

#
# def get_pwd(uid):
#     # get the password hash from the database
#     cmd = "mysql -N -e \"select usr_pswd from tbdb.users where uid='" + uid + "'\""
#
#     # print cmd
#     q = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE)
#     out, err = q.communicate()
#
#     output_list = out.split('\n')
#
#     # mysql statement returns two parameters with a newline
#     if len(output_list) < 1:
#         return 'fail'
#
#     return output_list[0]

class login_users:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        return login(parsed_json['uid'], parsed_json['password'])

class join_project_existing_user:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)

        uid = parsed_json['uid']
        pid = parsed_json['pid']

        deter_uri = getDeterURI()

        logging.info('Join project existing user: uid %s, pid %s', uid, pid)

        # calling the deterlab script to join project as existing users
        # use curl -L to follow redirects
        cookie_file = uid + '.txt'
        cmd = "curl -L --silent -k -b " + cookie_file + " -d \"submit=Submit" + \
              "&formfields[pid]=" + pid + "\" " \
              + deter_uri + "joinproject.php -v"

        out = subprocess.check_output(cmd, shell=True)

        if "The project leader has been notified of your application" in out:
            logging.info('Join project existing user: ok')
            return '{"msg":"join project existing user ok"}'
        elif "Invalid Project Name" in out:
            logging.warning('Join project existing user: error, project %s does not exist', pid)
            return '{"msg":"Team was not found on DeterLab"}'
        elif "You are already a member" in out:
            logging.warning(
                'Join project existing user: error, user %s is already a member of project %s', uid, pid)
            return '{"msg":"You are already a member of the team on DeterLab"}'
        elif "The project you as seeking to join is a class." in out:
            logging.warning('Join project existing user: error, project %s is a class', pid)
            return '{"msg":"Team is a teaching class, please contact the team leader for an account"}'
        else:
            logging.warning('Join project existing user: unknown error %s', out)
            return '{"msg":"Unknown error, please contact NCL Support"}'

class apply_project_new_users:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)

        # json key must match that defined under RegistrationService.java
        user_info_dict = create_user_info_dict(parsed_json)
        proj_w_user_info_dict = add_proj_info_dict(parsed_json, user_info_dict)

        # call a python module to generate a unique deterlab userid
        uid = generateDeterUserId.generate(proj_w_user_info_dict['first_name'], proj_w_user_info_dict['last_name'])

        # print uid
        logging.info('apply new project - newly generated deter uid: %s', uid)

        # apply project (create user account in tbdb)
        result = create_project(uid, proj_w_user_info_dict)
        if result == 'good':
            return verify_user(uid)
        elif result == 'email address in use':
            return '{"msg":"Email address already exists, please use a different one"}'
        elif result == 'invalid password':
            return '{"msg":"Invalid password, please use stronger password"}'
        elif result == 'team name is already in use':
            return '{"msg":"Team name already exists, please use a different one"}'
        else:
            return '{"msg":"Unknown error, please contact NCL Support"}'

class join_project_new_users:
    def POST(self):
        # web.header('Content-Type', 'application/json')
        data = web.data()
        parsed_json = json.loads(data)

        # json key must match that defined under RegistrationService.java
        user_info_dict = create_user_info_dict(parsed_json)

        # call a python module to generate a unique deterlab userid
        uid = generateDeterUserId.generate(user_info_dict['first_name'], user_info_dict['last_name'])

        # print uid
        logging.info('join existing project - newly generated deter uid: %s', uid)

        # join project (create user account in tbdb)
        result = join_project(uid, user_info_dict)
        if result == 'good':
            return verify_user(uid)
        elif result == 'email address in use':
            return '{"msg":"Email address already exists, please use a different one"}'
        elif result == 'invalid password':
            return '{"msg":"Invalid password, please use stronger password"}'
        elif result == 'project not found':
            return '{"msg":"Team was not found on DeterLab"}'
        elif result == "team is a class":
            return '{"msg":"Team is a teaching class, please contact the team leader for an account"}'
        else:
            return '{"msg":"Unknown error, please contact NCL Support"}'

def add_proj_info_dict(parsed_json, user_info_dict):
    user_info_dict['proj_name'] = parsed_json['projName']
    user_info_dict['proj_goals'] = parsed_json['projGoals']
    user_info_dict['pid'] = parsed_json['pid']
    user_info_dict['proj_web'] = parsed_json['projWeb']
    user_info_dict['proj_org'] = parsed_json['projOrg']
    user_info_dict['proj_research_type'] = parsed_json['projResearchType']

    if parsed_json['projPublic'] == 'PUBLIC':
        user_info_dict['proj_public'] = 'Yes'
    else:
        user_info_dict['proj_public'] = 'No'

    return user_info_dict

# def join_project_existing_user( uid, pid):
# 	deter_uri = getDeterURI()
#
# 	# calling the deterlab script to join project as existing users
# 	# use curl -L to follow redirects
# 	cookie_file = uid + '.txt'
# 	cmd = "curl -L --silent -k -b " + cookie_file + " -d \"submit=Submit" + \
# 	 "&formfields[pid]=" + pid + "\" " \
# 		  + deter_uri + "joinproject.php -v"
#
# 	p = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE)
# 	out, err = p.communicate()
#
# 	logging.info('join project current users: %s', out)
#
# 	if "The project leader has been notified of your application" in out :
# 		return '{"msg":"join project request for existing user is successful"}'
# 	elif "Invalid Project Name" in out :
# 		logging.info( 'Join project existing user: error, project %s does not exist', pid)
# 		return '{"msg":"project was not found on DeterLab"}'
# 	else :
# 		return '{"msg":"unknown reason in join project request for existing user"}'

def join_project(uid, my_dict):
    pid = my_dict['pid']

    logging.info('Joining project %s, new user %s ', pid, uid)

    deter_uri = getDeterURI()
    # password cannot contain double-quote '"'
    password = my_dict['password']

    # need to parse in the form fields
    # uid, email address must be unique
    cmd = "curl -L -s -k -X POST" + \
          " -F \"formfields[pid]=" + pid + "\"" + \
          " -F \"formfields[gid]=\"" + \
          " -F \"formfields[usr_name]=" + my_dict['last_name'] + " " + my_dict['first_name'] + "\"" + \
          " -F \"formfields[usr_email]=" + my_dict['email'] + "\"" + \
          " -F \"formfields[usr_phone]=" + my_dict['phone'] + "\"" + \
          " -F \"formfields[usr_title]=" + my_dict['job_title'] + "\"" + \
          " -F \"formfields[usr_affil]=" + my_dict['institution'] + "\"" + \
          " -F \"formfields[usr_affil_abbrev]=" + my_dict['institution_abbrev'] + "\"" + \
          " -F \"formfields[usr_addr]=" + my_dict['address1'] + "\"" + \
          " -F \"formfields[usr_addr2]=" + my_dict['address2'] + "\"" + \
          " -F \"formfields[usr_URL]=http://www.aaa.com\"" + \
          " -F \"formfields[usr_city]=" + my_dict['city'] + "\"" + \
          " -F \"formfields[usr_state]=" + my_dict['region'] + "\"" + \
          " -F \"formfields[usr_zip]=" + my_dict['zip_code'] + "\"" + \
          " -F \"formfields[usr_country]=" + my_dict['country'] + "\"" + \
          " -F \"formfields[uid]=" + uid + "\"" + \
          " -F \"formfields[password1]=" + password + "\"" + \
          " -F \"formfields[password2]=" + password + "\"" + \
          " -F \"submit=Submit\"" + \
          " '" + deter_uri + "joinproject.php -v'"

    out = subprocess.check_output(cmd, shell=True)

    if "As a pending user of the Testbed you will receive a key via email" in out:
        logging.info('Join project new user: OK, user %s created in project %s', uid, pid)
        return 'good'
    elif "Already in use. Did you forget to login?" in out:
        logging.info('Join project new user: error, email address already in use')
        return 'email address in use'
    elif "Invalid Password" in out or "Invalid DETER Password" in out:
        logging.info('Join project new user: error, invalid password')
        return 'invalid password'
    elif "Invalid Project Name" in out:
        logging.info('Join project new user: error, invalid project name')
        return 'project not found'
    elif "The project you as seeking to join is a class." in out:
        logging.warning('Join project new user: error, project %s is a class', pid)
        return "team is a class"
    else:
        logging.info('Join project new user: unknown error %s', out)
        return "unknown error"

def create_project(uid, proj_w_user_info_dict):
    # a new user registers and applies to create a new project
    pid = proj_w_user_info_dict['pid']

    logging.info('Creating new project %s and user %s on Deter', pid, uid)

    deter_uri = getDeterURI()

    # password cannot contain double-quote '"'
    password = proj_w_user_info_dict['password']

    cmd = "curl -L -s -k -X POST" + \
          " -F \"formfields[usr_name]=" + proj_w_user_info_dict['last_name'] + " " + proj_w_user_info_dict[
              'first_name'] + "\"" + \
          " -F \"formfields[usr_email]=" + proj_w_user_info_dict['email'] + "\"" + \
          " -F \"formfields[usr_phone]=" + proj_w_user_info_dict['phone'] + "\"" + \
          " -F \"formfields[usr_title]=" + proj_w_user_info_dict['job_title'] + "\"" + \
          " -F \"formfields[usr_affil]=" + proj_w_user_info_dict['institution'] + "\"" + \
          " -F \"formfields[usr_affil_abbrev]=" + proj_w_user_info_dict['institution_abbrev'] + "\"" + \
          " -F \"formfields[usr_addr]=" + proj_w_user_info_dict['address1'] + "\"" + \
          " -F \"formfields[usr_addr2]=" + proj_w_user_info_dict['address2'] + "\"" + \
          " -F \"formfields[usr_URL]=http://www.aaa.com\"" + \
          " -F \"formfields[usr_city]=" + proj_w_user_info_dict['city'] + "\"" + \
          " -F \"formfields[usr_state]=" + proj_w_user_info_dict['region'] + "\"" + \
          " -F \"formfields[usr_zip]=" + proj_w_user_info_dict['zip_code'] + "\"" + \
          " -F \"formfields[usr_country]=" + proj_w_user_info_dict['country'] + "\"" + \
          " -F \"formfields[uid]=" + uid + "\"" + \
          " -F \"formfields[pid]=" + pid + "\"" + \
          " -F \"formfields[gid]=\"" + \
          " -F \"formfields[proj_name]=" + proj_w_user_info_dict['proj_name'] + "\"" + \
          " -F \"formfields[proj_why]=" + proj_w_user_info_dict['proj_goals'] + "\"" + \
          " -F \"formfields[proj_URL]=" + proj_w_user_info_dict['proj_web'] + "\"" + \
          " -F \"formfields[proj_org]=" + proj_w_user_info_dict['proj_org'] + "\"" + \
          " -F \"formfields[proj_research_type]=" + proj_w_user_info_dict['proj_research_type'] + "\"" + \
          " -F \"formfields[proj_funders]=Other\"" + \
          " -F \"formfields[proj_public]=" + proj_w_user_info_dict['proj_public'] + "\"" + \
          " -F \"formfields[password1]=" + password + "\"" + \
          " -F \"formfields[password2]=" + password + "\"" + \
          " -F \"submit=Submit\"" + \
          " '" + deter_uri + "newproject.php -v'"

    out = subprocess.check_output(cmd, shell=True)

    if "Your project request has been successfully queued" in out:
        logging.info('Create new project new user: project %s and user %s created', pid, uid)
        return "good"
    elif "Already in use. Did you forget to login?" in out:
        logging.warning('Create new project new user: error, email address already in use')
        return "email address in use"
    elif "Invalid Password" in out or "Invalid DETER Password" in out:
        logging.warning('Create new project new user: error, invalid password')
        return "invalid password"
    elif "Already in use. Select another" in out:
        logging.warning('Create new project new user: error, project name %s already in use', pid)
        return "team name is already in use"
    else:
        logging.warning('Create new project new user: unknown error %s', out)
        return "unknown error"

def login(uid, password):
    # create a cookie file unique to each users
    deter_uri = getDeterURI()

    password_safe = urllib.quote_plus(password)
    cookie_file = uid + '.txt'

    login_url = "curl -s -k -c " + cookie_file + " -d " + \
                "\"uid=" + uid + \
                "&password=" + password_safe + \
                "&login=" + uid + "\"" + \
                " '" + deter_uri + "login.php'"

    out = subprocess.check_output(login_url, shell=True)

    if "Login attempt failed" in out:
        logging.warning('User %s login failed: %s', uid, out)
        return '{"msg":"user is logged in error"}'
    elif "Your account has been frozen" in out:
        logging.warning('User %s login failed: Account frozen due to earlier login attempt failures', uid)
        return '{"msg":"user account is frozen"}'
    else:
        logging.info('User %s logged in, cookie file %s created', uid, cookie_file)
        return '{"msg":"user is logged in"}'

# automatically verify a new user in DeterLab
def verify_user(uid):
    logging.info('Trying to verify user %s', uid)

    user_status_before = get_user_status(uid)

    if user_status_before == 'newuser':
        # implies DeterLab has created the user account

        # change status from newuser to unapproved
        cmd = "mysql -e \"update tbdb.users set status='unapproved' where uid='" + uid + "'\""
        subprocess.call(cmd, shell=True)

        user_status_after = get_user_status(uid)

        if user_status_after == 'unapproved':
            logging.info('User %s was verified', uid)
            return '{"msg": "user is created", "uid":"' + uid + '"}'
        else:
            logging.warning('Verify user %s failed: please check the adapter log', uid)
            return '{"msg": "user cannot be verified on DeterLab"}'
    else:
        logging.warning('Verify user %s failed: user not found in table tbdb.users', uid)
        return '{"msg": "user was not created on DeterLab"}'

def get_user_status(uid):
    cmd = "mysql -N -e \"select status from tbdb.users where uid='" + uid + "'\""
    out = subprocess.check_output(cmd, shell=True)

    result_list = out.split('\n')

    if result_list[0] == '':
        return "no such user"
    else:
        return result_list[0]

def create_user_info_dict(parsed_json):
    my_dict = {}
    my_dict['first_name'] = parsed_json['firstName']
    my_dict['last_name'] = parsed_json['lastName']
    my_dict['job_title'] = parsed_json['jobTitle']
    my_dict['password'] = parsed_json['password']
    my_dict['email'] = parsed_json['email']
    my_dict['phone'] = parsed_json['phone']
    my_dict['institution'] = parsed_json['institution']
    my_dict['institution_abbrev'] = parsed_json['institutionAbbreviation']
    my_dict['institution_web'] = parsed_json['institutionWeb']
    my_dict['address1'] = parsed_json['address1']
    my_dict['address2'] = parsed_json['address2']
    my_dict['country'] = parsed_json['country']
    my_dict['region'] = parsed_json['region']
    my_dict['city'] = parsed_json['city']
    my_dict['zip_code'] = parsed_json['zipCode']
    my_dict['pid'] = parsed_json['pid']
    return my_dict

def create_user_info_dict2(parsed_json):
    # for logged on users
    my_dict = {}
    my_dict['uid'] = parsed_json['uid']
    my_dict['pid'] = parsed_json['pid']
    return my_dict

def create_credential_dict(parsed_json):
    # for modifying credentials
    my_dict = {}
    my_dict['uid'] = parsed_json['uid']
    my_dict['password1'] = parsed_json['password1']
    my_dict['password2'] = parsed_json['password2']
    return my_dict

def getDeterURI():
    parser = SafeConfigParser()
    parser.read(CONFIG_FILE)
    return parser.get('DETER_URI', 'uri')

def getDBURI():
    parser = SafeConfigParser()
    parser.read(CONFIG_FILE)
    return parser.get('DB_URI', 'db_uri')

def getDBName():
    parser = SafeConfigParser()
    parser.read(CONFIG_FILE)
    return parser.get('DB_NAME', 'db_name')

def getDomain():
    parser = SafeConfigParser()
    parser.read(CONFIG_FILE)
    return parser.get('DOMAIN', 'domain')

def getAPISecret():
    parser = SafeConfigParser()
    parser.read(CONFIG_FILE)
    return parser.get('API_SECRET', 'secret')

###########################################################################################
# create experiment

class create_experiment:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        experiment_dict = create_experiment_dict(parsed_json)
        pid = experiment_dict['team_name']
        eid = experiment_dict['exp_name']
        logging.info('Creating experiment %s/%s', pid, eid)

        deter_uri = getDeterURI()
        cookie_file = experiment_dict['deter_login'] + ".txt"

        autoswap_timeout = experiment_dict['max_duration']

        if experiment_dict['max_duration'] != "0":
            # experiment set to swap out after some hours
            autoswap_enable = "1"
        else:
            # experiment will never auto swapout
            # still have to set the timeout value for the webpage to process without errors
            autoswap_enable = "0"
            autoswap_timeout = "16"

        cmd_line = "curl --silent -b " + cookie_file + " -k -d \"formfields[exp_pid]=" + experiment_dict['team_name'] + \
                   "&formfields[exp_id]=" + experiment_dict['exp_name'] + \
                   "&formfields[exp_description]=" + experiment_dict['description'] + \
                   "&formfields[exp_nsfile_contents]=" + urllib.quote(experiment_dict['ns_file_content'], safe='') + \
                   "&formfields[exp_noswap_reason]=reason" + \
                   "&formfields[exp_noidleswap_reason]=reason" + \
                   "&formfields[exp_idleswap_timeout]=4" + \
                   "&formfields[exp_autoswap_timeout]=" + autoswap_timeout + \
                   "&formfields[exp_autoswap]=" + autoswap_enable + \
                   "&formfields[exp_swappable]=1" + \
                   "&formfields[exp_swapin]=0" + \
                   "&beginexp=Submit" + "\" " + \
                   deter_uri + "beginexp_html.php -v"

        out = subprocess.check_output(cmd_line, shell=True)

        if "Working ..." in out:
            # DeterLab is prerunning the new experiment
            counter = 0
            while counter <= 100:
                time.sleep(2)
                exp_status = get_exp_status(eid, pid)
                if exp_status == 'swapped':
                    logging.info('Create experiment %s/%s succeeded', pid, eid)
                    return '{"msg":"experiment create success"}'
                elif exp_status == EXPERIMENT_NOT_FOUND:
                    # possible to reach here if ns file still has error which result in no experiment being created
                    logging.warning('Create experiment %s/%s failed: pre-run failed', pid, eid)
                    return '{"msg":"experiment create fail ns file error"}'
                counter += 1
            # few minutes have passed and experiment is still not created on deter?
            logging.warning('Create experiment %s/%s failed: timed out', pid, eid)
            return '{"msg":"experiment create fail"}'
        elif "parse-ns: Parsing failed" in out:
            logging.warning('Create experiment %s/%s failed: Parsing failed', pid, eid)
            return '{"msg":"experiment create fail ns file error"}'
        elif "Already in use" in out:
            logging.warning('Create experiment %s/%s failed: exp name already exists', pid, eid)
            return '{"msg":"experiment create fail exp name already in use"}'
        elif "Your account has not been approved yet" in out:
            logging.warning('Create experiment %s/%s failed: user not approved yet', pid, eid)
            return '{"msg":"experiment create fail"}'
        else:
            logging.warning('Create experiment %s/%s failed: unknown error %s', pid, eid, out)
            return '{"msg":"experiment create fail"}'

def create_experiment_dict(parsed_json):
    my_dict = {}
    my_dict['id'] = parsed_json['id']
    my_dict['user_id'] = parsed_json['userId']
    my_dict['team_id'] = parsed_json['teamId']
    my_dict['team_name'] = parsed_json['teamName']
    my_dict['exp_name'] = parsed_json['name']
    my_dict['description'] = parsed_json['description']
    my_dict['ns_file'] = parsed_json['nsFile']
    my_dict['ns_file_content'] = parsed_json['nsFileContent']
    my_dict['idle_swap'] = parsed_json['idleSwap']
    my_dict['max_duration'] = parsed_json['maxDuration']
    my_dict['deter_login'] = parsed_json['deterLogin']
    my_dict['user_server_uri'] = parsed_json['userServerUri']
    return my_dict

# def create_ns_file(file_path, file_content):
#     file = open(file_path, 'w')
#     file.write(file_content)
#     file.close

###########################################################################################
# start experiment

class start_experiment:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        http_command = parsed_json['httpCommand']
        cookie_file = parsed_json['deterLogin'] + ".txt"
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        logging.info('Starting experiment %s/%s', pid, eid)

        cmd_line = craft_http_command(http_command, cookie_file)

        #process = subprocess.call(cmd_line, shell=True, stdout=subprocess.PIPE)
        out = subprocess.check_output(cmd_line, shell=True)
        result = {'msg': 'experiment start fail', 'status': 'swapped', 'report': ''}
        if 'Your experiment has started its swapin' in out:
            logging.info('Experiment %s/%s has started its swapin', pid, eid)
            result['msg'] = 'experiment start success'
            result['status'] = 'activating'
        elif 'Experiment swapping is disabled' in out:
            logging.warning('Start experiment %s/%s failed: swapping is disabled', pid, eid)
        elif 'You do not have permission for' in out:
            logging.warning('Start experiment %s/%s failed: permission denied', pid, eid)
        elif 'Cannot proceed' in out:
            logging.warning('Start experiment %s/%s failed: the experiment is locked down', pid, eid)
        else:
            logging.warning('Start experiment %s/%s failed: %s', pid, eid, out)

        return result

###########################################################################################
# stop experiment

class stop_experiment:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)

        http_command = parsed_json['httpCommand']
        cookie_file = parsed_json['deterLogin'] + ".txt"
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        logging.info('Stopping experiment %s/%s', pid, eid)

        cmd_line = craft_http_command(http_command, cookie_file)

        # process = subprocess.call(cmd_line, shell=True, stdout=subprocess.PIPE)
        #
        # logging.info('stop experiment: %s', process)
        #
        # # to fix service ObjectOptimisticLockingFailureException
        # # treat stop experiment as always success since the status is updated upon page refresh
        #
        # result = '{ "msg": "experiment stop success", "status" : "swapped", "report" : "" }'
        # logging.info('result of stop experiment: %s', result)
        # return result
        out = subprocess.check_output(cmd_line, shell=True)
        result = {'msg': 'experiment stop fail', 'status': 'active', 'report': ''}
        if 'Your experiment has started its swapout' in out:
            logging.info('Experiment %s/%s has started its swapout', pid, eid)
            result['msg'] = 'experiment stop success'
            result['status'] = 'swapped'
        elif 'Experiment swapping is disabled' in out:
            logging.warning('Stop experiment %s/%s failed: swapping is disabled', pid, eid)
        elif 'You do not have permission for' in out:
            logging.warning('Stop experiment %s/%s failed: permission denied', pid, eid)
        elif 'Cannot proceed' in out:
            logging.warning('Stop experiment %s/%s failed: the experiment is locked down', pid, eid)
        else:
            logging.warning('Stop experiment %s/%s failed: %s', pid, eid, out)

        return result

# get experiment activity log for experiment profile page
# returns a dictionary in the format
# { 'msg' : 'success/fail', 'activity_log' : 'activity_log_contents/No activity log found.' }
def get_activity_log(pid, eid):
    # get swapexp log
    result = get_experiment_log_file(pid, eid, 'swapexp.log', 'activity log')

    if result['msg'] == 'get activity log fail':
        # no swapexp log
        # experiment may have just been created
        # find startexp log
        result = get_experiment_log_file(pid, eid, 'startexp.log', 'activity log')

    return result

# get experiment log files under the directory from /usr/testbed/expwork/ for experiment profile page
# filename: nsfile.ns, swapexp.log, startexp.log
# log_type: 'ns file', 'activity log'
# returns a dictionary in the format
# { 'msg' : 'success/fail', 'log_type' : 'file_contents/No file found.' }
def get_experiment_log_file(pid, eid, filename, log_type):
    logging.info('Retrieving %s for %s/%s', log_type, pid, eid)

    filepath = '/usr/testbed/expwork/' + pid + '/' + eid + '/' + filename

    file_not_found = 'No ' + log_type + ' found.'
    log_msg_success = 'Retrieve ' + log_type + ' success'
    msg_success = 'get ' + log_type + ' success'
    msg_fail = 'get ' + log_type + ' fail'

    result = {}
    # prepare the key for the result dictionary, e.g. ns<space>file becomes ns_file
    result_key = log_type.replace(" ", "_")

    if os.path.exists(filepath):
        try:
            with open(filepath) as myFile:
                logging.info(log_msg_success)
                result['msg'] = msg_success
                result[result_key] = myFile.read()
                myFile.close()
        except IOError as e:
            result['msg'] = msg_fail
            result[result_key] = file_not_found
    else:
        result['msg'] = msg_fail
        result[result_key] = file_not_found

    return result

# get realization details for experiment profile page
# returns a dictionary in the format
# { 'msg' : 'success/fail', 'realization_details' : 'realization_details_contents/No experiment details found.' }
def get_realization_details(pid, eid):
    logging.info('Retrieving realization details for %s/%s', pid, eid)

    result = {}

    # invokes tbreport -b
    # don't need the event details which is tbreport -v
    exp_report = get_exp_report(pid, eid)

    if exp_report != '':
        logging.info('Retrieve realization details success')
        result['msg'] = 'get realization details success'
        result['realization_details'] = exp_report
    else:
        result['msg'] = 'get realization details fail'
        result['realization_details'] = 'No experiment details found.'

    return result

###########################################################################################
'''
- retrieve experiment nsfile, realization details and nsfile
- returns a JSON dump of a dictionary in the format:
{
	'ns_file' :
		{
			'msg' : 'success/fail',
			'ns_file' : 'ns_file_contents'
		},
	'realization_details' :
		{
			'msg' : 'success/fail',
			'realization_details' : 'realization_details_contents'
		},
	'activity_log'	:
		{
			'msg' : 'success/fail',
			'activity_log' : 'activity_log_contents'
		}
}
- not to be confused with get_exp_report_with_nodes
- this endpoint is for the experiment_profile page
'''

class get_exp_details:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        logging.info('Retrieving experiment details for %s/%s', pid, eid)

        result = {}
        result['ns_file'] = get_experiment_log_file(pid, eid, 'nsfile.ns', 'ns file')
        result['realization_details'] = get_realization_details(pid, eid)
        result['activity_log'] = get_activity_log(pid, eid)

        return json.dumps(result)

###########################################################################################
# get experiment status by eid, pid

class get_exp_status2:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)

        eid = parsed_json['eid']
        pid = parsed_json['pid']

        status = get_exp_status(eid, pid)

        if status == 'active':
            # exp_report = get_exp_report(pid, eid)
            # exp_report = sanitize(exp_report)
            exp_report = get_exp_nodes_info(pid, eid)
            # logging.info('experiemnt status: %s', exp_report)
            return "{\"status\" : \"" + status + "\", \"report\":" + exp_report + "}"

        return "{ \"status\" : \"" + status + "\" }"

'''
returns a string
1. no experiment found
2. swapped / activating / active /swapping etc
'''

def get_exp_status(eid, pid):

    logging.info('Getting experiment status for %s/%s', pid, eid)

    cmd = "mysql -N -e \"select state from tbdb.experiments where eid='" + eid + "'" + " AND pid='" + pid + "'\""

    out = subprocess.check_output(cmd, shell=True)

    output_list = out.split('\n')

    # if sql query returns empty set: the output_list is ['']
    if output_list[0] == '':
        return EXPERIMENT_NOT_FOUND

    return output_list[0]

def get_exp_report(pid, eid):
    # returns a string
    # get the idx from the deter database
    cmd = "/usr/testbed/bin/tbreport -b " + pid + " " + eid
    out = subprocess.check_output(cmd, shell=True)
    return out

def sanitize(exp_report):
    rv = ''
    # truncate 'Experiment: xxxx' and 'State:....' and newline
    if "Experiment:" and "State:" in exp_report:
        exp_report_list = exp_report.split('\n')
        result_list = []
        # used to truncate lines after Virtual Lan/Link Info...
        # only keep the simple Qualified Name section
        flag = False
        for report_line in exp_report_list[2:]:
            if ("Virtual Lan" in report_line) or ("Physical Node Mapping" in report_line):
                flag = True
                break
            if flag is False:
                result_list.append(report_line)
        rv = '@'.join(result_list)
    return rv

###############################
# craft http command for start and stop experiment

def craft_http_command(http_command, cookie_file):
    deter_uri = getDeterURI()

    cmd_line = "curl --silent -X POST \"" + deter_uri + "swapexp.php" + http_command + "\"" \
                                                                                " -b " + cookie_file + " -k -d \"confirmed=Confirm\" -v"

    return cmd_line

# TODO: use this when using deter wrapper
def get_swapexp_cmd(experiment_dict, swap_type):
    # TODO: get deter-user URL/IP
    # TODO: test this script
    # swap_type = IN/OUT

    # script_wrapper.py --server=deter_user --login=logged_in_personnel swapexp pid eid in/out
    cmd_line = "python script_wrapper.py" + \
               " --server=" + experiment_dict['user_server_uri'] + \
               " --login=" + experiment_dict['deter_login'] + \
               " swapexp" + \
               " " + experiment_dict['team_name'] + \
               " " + experiment_dict['exp_name'] + \
               " " + swap_type

    return cmd_line

###########################################################################################
# delete experiment

class delete_experiment:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        eid = parsed_json['experimentName']
        pid = parsed_json['teamName']
        cookie_file = parsed_json['deterLogin'] + ".txt"
        logging.info('Terminating experiment %s/%s', pid, eid)

        # get the idx from the deter database
        cmd = "mysql -N -e \"select idx from tbdb.experiments where eid='" + eid + "'" + " AND pid ='" + pid + "'\""
        out = subprocess.check_output(cmd, shell=True)
        output_list = out.split('\n')
        # mysql statement returns two parameters with a newline
        if len(output_list) < 1:
            logging.warning('Experiment %s/%s not found, it has been terminated already?', pid, eid)
            result = {'status': 'no experiment found'}
            return result

        idx = output_list[0]
        deter_uri = getDeterURI()
        cmd_line = "curl -s -X POST \"" + deter_uri + "endexp.php?experiment=" + idx + "\"" \
                                                                                " -b " + cookie_file + " -k -d \"confirmed=Confirm\" -v"

        out = subprocess.check_output(cmd_line, shell=True)
        if 'Termination has started' in out:
            logging.info('Termination has started')
            # start to check until the experiment is removed from database
            counter = 0
            while counter < 100:
                exp_status = get_exp_status(eid, pid)
                if exp_status == EXPERIMENT_NOT_FOUND:
                    logging.info('Experiment %s/%s terminated', pid, eid)
                    result = {'status': 'no experiment found'}
                    return result
                counter += 1
                time.sleep(2)
            # experiment is still not removed from database after the while loop
            logging.warning('Termination failed: timed out')
            result = {'status': 'termination timed out'}
            return result
        elif 'Experiment swapping is disabled' in out:
            logging.warning('Termination failed: swapping is disabled')
            result = {'status': 'swapping is disabled'}
            return result
        elif 'You do not have permission to end experiment' in out:
            logging.warning('Termination failed: permission denied')
            result = {'status': 'permission denied'}
            return result
        elif 'the experiment is locked down' in out:
            logging.warning('Termination failed: experiment is locked down')
            result = {'status': 'experiment is locked down'}
            return result
        else:
            logging.warning('Termination failed: %s', out)
            result = {'status': 'could not proceed'}
            return result

###########################################################################################
# approve join request

class approve_join_request:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        join_request_dict = create_join_request_dict(parsed_json)
        approver = join_request_dict['approver_uid']
        uid = join_request_dict['uid']
        pid = join_request_dict['pid']
        gid = join_request_dict['gid']
        logging.info('Approving %s request to join project %s/%s, approver %s', uid, pid, gid, approver)

        deter_uri = getDeterURI()
        cookie_file = approver + '.txt'

        # check if user is in group first
        member_status_before = get_group_membership_status(pid, uid)

        if member_status_before == 'not found':
            logging.info('Join request for project %s for user %s NOT found', pid, uid)
            return '{"msg":"no join request found"}'
        elif member_status_before == 'local_root' or member_status_before == 'group_root' or member_status_before == 'project_root':
            logging.info('User %s is already a member in project %s', uid, pid)
            return '{"msg":"user is already an approved member in the project"}'

        # only process join request for users not already a member
        # format
        # actions: U[uid]%24%24approval-[pid]%2Fgid=approve postpone, deny, nuke
        # trust level: U[uid]%24%24trust-[pid]%2Fgid=local_root grouproot, user
        cmd_line = "curl -L -s -k -b " + cookie_file + " -d \"U" + uid + "%24%24approval-" + \
                   pid + "%2F" + gid + "=approve" + \
                   "&U" + uid + "%24%24trust-" + pid + "%2F" + \
                   gid + "=local_root" + \
                   "&OK=Submit" + "\" " + \
                   deter_uri + "approveuser.php -v"

        out = subprocess.check_output(cmd_line, shell=True)

        member_status_after = get_group_membership_status(pid, uid)
        if member_status_after == 'local_root':
            logging.info('Join request for project %s for user %s approved', pid, uid)
            return '{"msg":"process join request OK"}'
        else:
            logging.warning('Approve join request for project %s for user %s failed: %s', pid, uid, out)
            return '{"msg":"process join request FAIL"}'

class reject_join_request:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        join_request_dict = create_join_request_dict(parsed_json)
        approver = join_request_dict['approver_uid']
        uid = join_request_dict['uid']
        pid = join_request_dict['pid']
        gid = join_request_dict['gid']
        logging.info('Rejecting %s request to join project %s/%s, approver %s', uid, pid, gid, approver)

        deter_uri = getDeterURI()
        cookie_file = approver + '.txt'

        # check if user is in group first
        member_status_before = get_group_membership_status(pid, uid)

        if member_status_before == 'not found':
            logging.info('Join request for project %s for user %s NOT found', pid, uid)
            return '{"msg":"no join request found"}'
        elif member_status_before == 'local_root' or member_status_before == 'group_root' or member_status_before == 'project_root':
            # useless to reject a member who is already approved
            logging.info('User %s is already a member in project %s', uid, pid)
            return '{"msg":"user is already an approved member in the project"}'

        # format
        # actions: U[uid]%24%24approval-[pid]%2Fgid=approve postpone, deny, nuke
        # trust level: U[uid]%24%24trust-[pid]%2Fgid=local_root grouproot, user
        cmd_line = "curl -L -s -k -b " + cookie_file + " -d \"U" + uid + "%24%24approval-" + \
                   pid + "%2F" + gid + "=nuke" + \
                   "&U" + uid + "%24%24trust-" + pid + "%2F" + \
                   gid + "=local_root" + \
                   "&OK=Submit" + "\" " + \
                   deter_uri + "approveuser.php > -v"

        out = subprocess.check_output(cmd_line, shell=True)

        member_status_after = get_group_membership_status(join_request_dict['pid'], join_request_dict['uid'])
        if member_status_after == 'not found':
            logging.info('Join request for project %s for user %s rejected', pid, uid)
            return '{"msg":"process join request OK"}'
        else:
            logging.warning('Reject join request for project %s for user %s failed: %s', pid, uid, out)
            return '{"msg":"process join request FAIL"}'

def create_join_request_dict(parsed_json):
    my_dict = {}
    my_dict['approver_uid'] = parsed_json['approverUid']
    my_dict['uid'] = parsed_json['uid']
    my_dict['pid'] = parsed_json['pid']
    my_dict['gid'] = parsed_json['gid']
    return my_dict

###########################################################################################

###########################################################################################
# apply project for existing users

class apply_project_existing_user:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        new_project_dict = create_new_project_dict(parsed_json)
        deter_uri = getDeterURI()
        pid = new_project_dict['pid']
        uid = new_project_dict['uid']

        logging.info('Applying project for existing user: pid %s, uid %s', pid, uid)

        cookie_file = uid + '.txt'

        cmd = "curl -L -s -k -b " + cookie_file + " -X POST" + \
              " -F \"formfields[proj_name]=" + new_project_dict['proj_name'] + "\"" + \
              " -F \"formfields[proj_why]=" + new_project_dict['proj_goals'] + "\"" + \
              " -F \"formfields[pid]=" + pid + "\"" + \
              " -F \"formfields[proj_URL]=" + new_project_dict['proj_web'] + "\"" + \
              " -F \"formfields[proj_org]=" + new_project_dict['proj_org'] + "\"" + \
              " -F \"formfields[proj_research_type]=" + new_project_dict['proj_research_type'] + "\"" + \
              " -F \"formfields[proj_funders]=Other\"" + \
              " -F \"formfields[proj_public]=" + new_project_dict['proj_public'] + "\"" + \
              " -F \"submit=Submit\"" + \
              " '" + deter_uri + "newproject.php -v'"

        out = subprocess.check_output(cmd, shell=True)

        if "Your project request has been successfully queued" in out:
            logging.info('Apply project %s for existing user %s ok', pid, uid)
            return '{"msg":"apply project existing users ok"}'
        elif 'Already in use. Select another' in out:
            logging.warning('Apply project existing user: error, project name already in use')
            return '{"msg":"team name already exists, please use a different one"}'
        elif "Illegal characters" in out:
            logging.warning('Apply project existing user: error, illegal characters in pid')
            return '{"msg":"team name contains illegal characters"}'
        else:
            logging.warning('Apply project existing user: unknown error %s', out)
            return '{"msg":"unknown error"}'

def create_new_project_dict(parsed_json):
    my_dict = {}
    my_dict['uid'] = parsed_json['uid']
    my_dict['proj_name'] = parsed_json['projName']
    my_dict['proj_goals'] = parsed_json['projGoals']
    my_dict['pid'] = parsed_json['pid']
    my_dict['proj_web'] = parsed_json['projWeb']
    my_dict['proj_org'] = parsed_json['projOrg']
    my_dict['proj_research_type'] = parsed_json['projResearchType']

    if parsed_json['projPublic'] == 'PUBLIC':
        my_dict['proj_public'] = 'Yes'
    else:
        my_dict['proj_public'] = 'No'

    return my_dict

###########################################################################################

###########################################################################################
# approve project

class approve_project:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        uid = parsed_json['uid']
        is_class = parsed_json['isClass']

        deter_uri = getDeterURI()
        cookie_file = 'ncl.txt'

        logging.info('Approving project %s, head uid %s', pid, uid)

        activateAdminFlag()

        # check if project exists first on deterlab
        proj_status_before = get_proj_status(pid)

        if proj_status_before == 'no project found':
            logging.warning('Approve project %s: NOT found', pid)
            return '{"msg":"no project found"}'
        elif proj_status_before == 'approved':
            logging.info('Approve project %s: already approved', pid)
            return '{"msg":"approve project OK"}'
        elif proj_status_before == 'archived':
            logging.warning('Approve project %s: already archived', pid)
            return '{"msg":"project has been archived"}'
        if not is_class:
            cmd = "curl -L --silent -k -b " + cookie_file + " -d \"approval=approve&prprefix=&silent=Yep&head_uid=" + \
              uid + "&user_interface=emulab&message=&OK=Submit\" " + "\"" + deter_uri + "approveproject.php?pid=" + pid + "\"" + " -v"
        else:
            random_string = random_6_char()
            # TODO: check if randomstring has been used
            cmd = "curl -L --silent -k -b " + cookie_file + \
				 " -d \"approval=approve&prprefix=" + random_string + "&silent=Yep&head_uid=" + uid \
				 + "&user_interface=emulab&message=&OK=Submit\" " \
				 + "\"" + deter_uri + "approveproject.php?pid=" + pid + "\"" + " -v"

        out = subprocess.check_output(cmd, shell=True)

        proj_status = get_proj_status(pid)
        if proj_status == 'approved':
            logging.info('Approve project %s OK', pid)
            return '{"msg":"approve project OK"}'
        else:
            logging.warning('Approve project %s failed: %s', pid, out)
            return '{"msg":"approve project FAIL"}'

def random_6_char():
	string_list = string.ascii_lowercase
	random_string = ''
	for i in range(6):
		random_string += random.choice(string_list)
	return random_string

###########################################################################################
# reject project
class reject_project:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        logging.info('Rejecting project %s', pid)
        deter_uri = getDeterURI()
        cookie_file = 'ncl.txt'

        activateAdminFlag()

        # check if project exists first on deterlab
        proj_status_before = get_proj_status(pid)

        if proj_status_before == 'no project found':
            # return ok because we want to remove entry from our db
            logging.info('Reject project %s: NOT found', pid)
            return '{"msg":"reject project OK"}'
        elif proj_status_before == 'approved':
            logging.warning('Reject project %s: has been approved', pid)
            return '{"msg":"project has been approved"}'
        elif proj_status_before == 'archived':
            logging.warning('Reject project %s: has been archived', pid)
            return '{"msg":"project has been archived"}'

        cmd = "curl -L --silent -k -b " + cookie_file + " -d \"approval=destroy&prprefix=&silent=Yep&head_uid=&user_interface=emulab&message=&OK=Submit\" " + "\"" + deter_uri + "approveproject.php?pid=" + pid + "\"" + " -v"

        out = subprocess.check_output(cmd, shell=True)

        proj_status_after = get_proj_status(pid)
        if proj_status_after == 'no project found':
            logging.info('Reject project %s OK', pid)
            return '{"msg":"reject project OK"}'
        else:
            logging.warning('Reject project %s failed: %s', pid, out)
            return '{"msg":"reject project FAIL"}'

def activateAdminFlag():
    deter_uri = getDeterURI()
    cookie_file = 'ncl.txt'
    cmd = "curl --silent -k -b " + cookie_file + " " + "\"" + deter_uri + "toggle.php?user=ncl&type=adminon&value=1\"" + " -v"
    out = subprocess.check_output(cmd, shell=True)
    logging.info('Activated admin flag')

###########################################################################################
# get status of project
def get_proj_status(pid):
    cmd = "mysql -N -e \"select status from tbdb.projects where pid='" + pid + "'\""
    out = subprocess.check_output(cmd, shell=True)
    output_list = out.split('\n')
    # if sql query returns empty set: the output_list is ['']
    if output_list[0] == '':
        return "no project found"

    status = output_list[0]
    return status

###########################################################################################
# get status of group membership
def get_group_membership_status(pid, uid):
    cmd = "mysql -N -e \"select trust from tbdb.group_membership where pid='" + pid + "' AND uid ='" + uid + "'\""
    out = subprocess.check_output(cmd, shell=True)
    output_list = out.split('\n')
    # if sql query returns empty set: the output_list is ['']
    if output_list[0] == '':
        return "not found"

    status = output_list[0]
    return status

###########################################################################################
# get number of free nodes
class get_free_nodes:
    def GET(self):
        logging.info('Retrieving number of free nodes...')

        cmd = "mysql -N -e \"select count(*) from tbdb.nodes as n left join tbdb.node_types as nt on n.type=nt.type left join tbdb.reserved as r on r.node_id=n.node_id where (role='testnode') and class='pc' and (n.eventstate='POWEROFF' or n.eventstate='ISUP' or n.eventstate='PXEWAIT' or n.eventstate='ALWAYSUP') and r.pid is null and n.reserved_pid is null;\""

        out = subprocess.check_output(cmd, shell=True)

        output_list = out.split('\n')
        logging.info('Number of free nodes returned: %s', output_list)

        # if sql query returns empty set: the output_list is ['']
        if output_list[0] == '':
            return 0

        return output_list[0]

###########################################################################################
# get number of total nodes
class get_total_nodes:
    def GET(self):
        logging.info('Retrieving number of total nodes...')

        cmd = "mysql -N -e \"select n.type,count(*) from tbdb.nodes as n left join tbdb.node_types as nt on n.type=nt.type left join tbdb.node_type_attributes as na on n.type=na.type where (role='testnode') and na.attrkey ='special_hw' group BY n.type, na.attrvalue;\""

        out = subprocess.check_output(cmd, shell=True)

        # type | count
        output_list = out.split('\n')
        logging.info('Number of total nodes returned: %s', output_list)

        # if sql query returns empty set: the output_list is ['']
        if output_list[0] == '':
            return 0

        total_node_count = 0

        for output_block in output_list:
            # split (type count) by whitespaces
            # sum up the node count by type
            output_list2 = output_block.split()

            # ['system type', 'count', '']
            if output_list2:
                total_node_count += int(output_list2[1])

        return total_node_count

###########################################################################################
# get all the nodes status
# include nodes type, physical node number
# output is a json dump of a dictionary in the following format
# { node_type: [{ <status for node 1> }, { <status for node 2> }...{} ] }
# <status for node 1> is a internal dictionary like: { id : <node_id>, status : <status>, pid: <pid>, eid: <eid> }
# returns an empty dictionary if fail
class get_nodes_status:
    def GET(self):
        logging.info('Retrieving node status...')

        output = {}
        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cursor = db.cursor()

        try:
            # fetch all the nodes' types
            # change the attribute from special_hw to others to detect more types
            cursor.execute(
                "select distinct n.type from nodes as n left join node_types as nt on n.type=nt.type left join node_type_attributes as na on n.type=na.type where na.attrkey='special_hw'")
            results = cursor.fetchall()

            # list of lists
            for row in results:
                logging.info('Retrieving current type... %s', row[0])

                # convert to uppercase for ease of making it to a enum in Java
                node_type = row[0].upper()
                # get all the nodes info for each node type
                nodes_lists = []

                # returns node id, event state, pid, reserved pid
                # add eid (modified by Vu)
                cursor.execute("select distinct n.node_id, n.eventstate, ifnull(r.pid,rp.pid) as pid, \
				 n.reserved_pid as reserved_pid, \
				 ifnull(r.eid,rp.eid) as eid \
				 from nodes as n \
				 left join node_types as nt on n.type=nt.type \
				 left join reserved as r on n.node_id=r.node_id \
				 left join reserved as rp on n.phys_nodeid=rp.node_id \
				 where nt.type ='" + node_type + "' \
				 and (role='testnode' or role='virtnode') ORDER BY priority")

                results2 = cursor.fetchall()

                # column value is fetch by row2 sql statement in the order of the select statement, e.g. node_id = row2[0]
                for row2 in results2:
                    node_info = {}
                    node_info['id'] = row2[0]
                    node_info['status'] = convertToNodesStatus(row2[1], row2[2], row2[3])
                    if (row2[2] is None) and row2[3]:
                        node_info['pid'] = row2[3]
                    else:
                        node_info['pid'] = row2[2]
                    node_info['eid'] = row2[4]
                    nodes_lists.append(node_info)

                # sort nodes alphabetically by status then by its id
                output[node_type] = sorted(nodes_lists, key=lambda k: (k['status'], k['id']))

        except:
            logging.warning('Retrieving node status failed')

        db.close()
        return json.dumps(output)

# covnerts deterlab state to simple free, in_use, reload
def convertToNodesStatus(eventState, pid, reserved_pid):
    free_state = "free"
    in_use_state = "in_use"
    reload_state = "reload"  # may indicate dead machine as well
    reserved_state = "reserved"

    if pid:
        # project is allocated a node
        return in_use_state
    else:
        if (eventState == 'ISUP') or (eventState == 'ALWAYSUP') or \
                (eventState == 'POWEROFF') or (eventState == 'PXEWAIT'):
            if reserved_pid is None:
                return free_state
            else:
                # reserved pid implies nodes are pre-reserved for a project
                return reserved_state
        else:
            return reload_state

###########################################################################################
# get the network topology thumbnail of an experiment
class get_topo_thumbnail:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        logging.info('Retrieving topology thumbnail for %s/%s', pid, eid)

        cmd = "mysql --raw -N -e \"select thumbnail from tbdb.experiment_resources where idx = (select rsrcidx from tbdb.experiment_stats where eid_uuid = (select eid_uuid from tbdb.experiments where pid='" + pid + "' and eid='" + eid + "'));\""

        out = subprocess.check_output(cmd, shell=True)

        logging.info('Done')
        return base64.encodestring(out)

###########################################################################################
# # get exp report with nodes
# def get_exp_report_with_nodes(pid, eid):
# 	logging.info('Consolidating experiment information')
#
# 	# get node info
# 	# cmd = "/usr/testbed/bin/tbreport -n " + pid + " " + eid
# 	cmd = "/usr/testbed/bin/tbreport -n " + pid + " " + eid
#
# 	# print cmd
# 	q = subprocess.Popen(cmd, shell=True, stdout=subprocess.PIPE)
# 	node_info, err = q.communicate()
#
# 	# get physical node mapping
# 	# cmd2 = "/usr/testbed/bin/tbreport -m " + pid + " " + eid
# 	cmd2 = "/usr/testbed/bin/tbreport -m " + pid + " " + eid
#
# 	q2 = subprocess.Popen(cmd2, shell=True, stdout=subprocess.PIPE)
# 	node_mapping, err = q2.communicate()
#
# 	node_info_split = node_info.split('\n')
# 	node_mapping_split = node_mapping.split('\n')
#
# 	# print node_info
# 	# print node_mapping
# 	# print node_info_split[6:-2]
# 	# print node_mapping_split[6:-2]
#
# 	result = {}
# 	# start from index 6 because we want to skip all the headers info
# 	# end at -2 because last two entries is always empty string
# 	'''
# 	information now is in this format
# 	['nodeIDA  typeA   osA   qualifiedNameA', 'nodeNameB...']  tbreport -n result
# 	['nodeIDA  typeA  osA  physicalA'] tbreport -m result
# 	'''
# 	for node_info_str in node_info_split[6:-2]:
# 		# information now is this format ['nodeIDA','typeA','osA','qualifiedNameA']
# 		# ID, TYPE (aka CLASS pc), OS, QUALIFIED_NAME
#
# 		node_info_str_list = node_info_str.split()
# 		node_info_dict = {}
#
# 		# there is a chance OS will be empty if the user did not use tb-set-node-os
# 		if len(node_info_str_list) >= 3:
# 			# OS field exists
# 			node_info_dict['qualifiedName'] = node_info_str_list[-1]
# 		else:
# 			# some other errors occurred:
# 			node_info_dict['qualifiedName'] = 'None'
#
# 		# result[nodeId] = dictionary
# 		# do not add sdn controller nodes
# 		machine_type = node_info_str_list[1]
# 		if machine_type != 'ovxctl' and machine_type != 'ofedge' and machine_type != 'ofcore':
# 			result[node_info_str_list[0]] = node_info_dict
#
# 	for node_mapping_str in node_mapping_split[6:-2]:
# 		# ID (node name e.g. n0), TYPE, OS, PHYSICAL
# 		# add os information here is more reliable
# 		# add node id information
# 		# add another key-pair to the result[nodeId] existing dictionary
# 		node_mapping_str_list = node_mapping_str.split()
# 		node_name = node_mapping_str_list[0]
#
# 		if node_name in result:
# 			# only add more details if result already contains the node name from previous for loop
# 			# because node_mapping_str_list may contain key value such as tbdelay0 which is not in result
# 			# several types of splitted node_mapping_str may occur:
# 			# 1. ['node_name', 'TYPE', 'OS', 'physical node id']
# 			# 2. ['node_name', 'TYPE', 'physical node id', 'physical node id']
# 			if len(node_mapping_str_list) > 3:
# 				if 'ofport' in node_mapping_str_list[2] and 'ofport' in node_mapping_str_list[3]:
# 					# for sdn experiments, the switch has no OS and may be in this form after split: ['switch1p1','ofedge','ofport11', '(ofportpc1)']
# 					# combine [2] and [3] together
# 					result[node_mapping_str_list[0]]['os'] = 'N.A.'
# 					result[node_mapping_str_list[0]]['nodeId'] = node_mapping_str_list[2] + ' ' + node_mapping_str_list[3]
# 				else:
# 					result[node_mapping_str_list[0]]['os'] = node_mapping_str_list[2]
# 					result[node_mapping_str_list[0]]['nodeId'] = node_mapping_str_list[3]
# 			else:
# 				# node_mapping_str_list is length of 3
# 				# e.g. ['switch1p1', 'ofedge', 'ofport11']
# 				result[node_mapping_str_list[0]]['os'] = 'N.A.'
# 				result[node_mapping_str_list[0]]['nodeId'] = node_mapping_str_list[2]
#
# 	logging.info('exp nodes info: %s', result)
# 	return json.dumps(result)

###########################################################################################
# save image
class save_image:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        image_name = parsed_json['imageName']
        pid = parsed_json['pid']
        uid = parsed_json['uid']
        node_id = parsed_json['nodeId']
        logging.info('Saving image %s from node %s, project %s, user %s', image_name, node_id, pid, uid)
        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'

        # check if the node is up; can only create an image from a running machine
        mysql_cmd = " mysql -N -e \"select status from tbdb.node_status where node_id = '" + node_id + "'\""
        # logging.debug("SQL to query node status: " + mysql_cmd)
        status = subprocess.check_output(mysql_cmd, shell=True)
        logging.debug(node_id + ": " + status)
        if status.strip() != 'up':
            logging.warning('Saving image %s failed: can only create image from a running machine', image_name)
            return '{"msg": "Node is not up. You can only create an image from a running node."}'

        # Tran: check if uid is the user who swapped the experiment
        swapper_query = "mysql -N -e \"select u.uid from tbdb.users u, tbdb.experiments e, tbdb.reserved r " + \
                        "where e.swapper_idx = u.uid_idx and e.idx = r.exptidx and r.node_id = '" + node_id + "'\""
        # logging.debug("SQL to query experiment swapper: %s", swapper_query)
        swapper = subprocess.check_output(swapper_query, shell=True)
        logging.debug("Swapper: %s", swapper)
        if swapper.strip() != uid:
            logging.warning("Saving image %s failed: swapper not found from DB, or user is not the swapper")
            return '{"msg": "Permission denied. Only the user who started the experiment can save image."}'

        # retrieve parameters from the current OS on the node
        mysql_cmd = "mysql -N -e \" select o.osname, o.OS, o.version, o.op_mode, o.osfeatures, o.reboot_waittime, i.loadpart, i.imageid " + \
                    " from tbdb.os_info o, tbdb.images i, tbdb.osidtoimageid oi, tbdb.nodes n " + \
                    " where o.osid = n.osid and i.imageid = oi.imageid and oi.osid = n.osid and n.node_id = '" + node_id + "' " + \
                    " group by oi.imageid\""
        # logging.debug("SQL to load OS parameters: %s", mysql_cmd)
        para_row = subprocess.check_output(mysql_cmd, shell=True)
        logging.debug("OS parameters on the node: %s", para_row)
        para_row_list = para_row.split()
        if para_row_list[0] == '':
            logging.warning("Save image %s failed: cannot load parameters from OS on node", image_name)
            return '{"msg":"Failed to load parameters from the current OS on the node."}'

        # retrieve node types from DB
        mysql_cmd = "mysql -N -e \"select distinct n.type from tbdb.nodes as n left join tbdb.node_types as nt on n.type=nt.type left join tbdb.node_type_attributes as a on a.type=n.type where a.attrkey='imageable' and a.attrvalue!='0' and n.role='testnode'\""
        # logging.debug("SQL to retrieve node types: %s", mysql_cmd)
        node_type_row = subprocess.check_output(mysql_cmd, shell=True)
        logging.debug(node_type_row)
        node_type_list = node_type_row.split()

        if node_type_list[0] == '':
            logging.warning("Save image %s failed: cannot load node types from DB", image_name)
            return '{"msg":"Failed to retrieve node types from database."}'

        cmd = "curl -L -s -k -b " + cookie_file + " -X POST" + \
              " -F \"formfields[simple]=0\"" + \
              " -F \"formfields[node_id]=" + node_id + "\"" + \
              " -F \"formfields[pid]=" + pid + "\"" + \
              " -F \"formfields[imagename]=" + image_name + "\"" + \
              " -F \"formfields[description]=default\"" + \
              " -F \"formfields[loadpart]=1\"" + \
              " -F \"formfields[OS]=" + para_row_list[1] + "\"" + \
              " -F \"formfields[version]=" + para_row_list[2] + "\"" + \
              " -F \"formfields[path]=/proj/" + pid + "/images/" + image_name + ".ndz\"" + \
              " -F \"formfields[current_OS]=" + para_row_list[0] + "\"" + \
              " -F \"formfields[os_feature_ping]=checked\"" + \
              " -F \"formfields[os_feature_ssh]=checked\"" + \
              " -F \"formfields[os_feature_ipod]=checked\"" + \
              " -F \"formfields[os_feature_isup]=checked\"" + \
              " -F \"formfields[op_mode]=" + para_row_list[3] + "\""

        # checked all node type, e.g. Intel, SystemX
        # formfields[mtype_all] is not able to select all the node types
        for node_type in node_type_list:
            cmd += " -F \"formfields[mtype_" + node_type + "]=Yep\""

        cmd += " -F \"formfields[wholedisk]=Yep\"" + \
               " -F \"formfields[max_concurrent]=\"" + \
               " -F \"formfields[reboot_waittime]=\"" + \
               " -F \"submit=Submit\"" + \
               " '" + deter_uri + "newimageid_ez.php'"

        # logging.debug("Curl command to save image: %s", cmd)
        out = subprocess.check_output(cmd, shell=True)
        # logging.debug(out)

        if 'Taking a snapshot of node' in out:
            logging.info('Saving image %s has started', image_name)
            return '{"msg":"save image OK"}'
        elif 'Illegal Characters' in out:
            logging.warning('Saving image %s failed: Invalid image name', image_name)
            return '{"msg":"Image name contains invalid characters."}'
        elif 'already exists' in out:
            logging.warning('Saving image %s failed: Image name already exists', image_name)
            return '{"msg":"Image name already exists."}'
        else:
            logging.warning('Saving image %s failed: %s', image_name, out)
            return '{"msg":"There is an error in your request."}'

###########################################################################################
#########################DELETE IMAGE #####################################################
###########################################################################################
# delete image
class delete_image:
    def DELETE(self):
        data = web.data()
        parsed_json = json.loads(data)
        image_name = parsed_json['imageName']
        pid = parsed_json['pid']
        uid = parsed_json['uid']
        isImageCreator = parsed_json['isImageCreator']

        image_filepath = "/proj/" + pid + "/images/" + image_name + ".ndz"
        image_sig_filepath = "/proj/" + pid + "/images/sigs/" + image_name + ".ndz.sig"

        result = {}

        if isImageCreator == "yes":
            logging.info('Retrieving creator from imagename %s', image_name)
            # mysql options:
            # -N Do not write column names in result(s).
            # -e statement
            mysql_cmd = "mysql -N -e \"select creator from tbdb.images where imagename=\'" + image_name + "\' and pid=\'" + pid + "\'\""

            creator_query = subprocess.Popen(mysql_cmd, shell=True, stdout=subprocess.PIPE)
            creator_query_out, creator_query_err = creator_query.communicate()

            if creator_query.returncode != 0:
                logging.warning(
                    'Deleting image %s from user %s of project %s: error in DeterLab when retrieving creator of image',
                    image_name, uid, pid)
                return '{"msg": "unknown error in quering database to retrieving creator of image"}'

            creator_query_list = creator_query_out.split('\n')
            if creator_query_list[0] == '':
                return '{"msg": "no creator found when querying database"}'
            else:
                creator = creator_query_list[0]
                if (creator != uid):
                    return '{"msg": "not the creator"}'

        logging.info('Retrieving imageid from imagename %s', image_name)
        # mysql options:
        # -N Do not write column names in result(s).
        # -e statement
        mysql_cmd = "mysql -N -e \"select imageid from tbdb.images where imagename=\'" + image_name + "\' and pid=\'" + pid + "\'\""
        query = subprocess.Popen(mysql_cmd, shell=True, stdout=subprocess.PIPE)
        query_out, query_err = query.communicate()

        if query.returncode != 0:
            logging.warning('Deleting image %s from user %s of project %s: error in DeterLab when retrieving imageid',
                            image_name, uid, pid)
            return '{"msg": "unknown error in querying database to retrieve imageid"}'

        output_list = query_out.split('\n')
        if output_list[0] == '':
            return '{"msg": "required image id is not found when querying database"}'
        else:
            image_id = output_list[0]

        # check if image is in use
        check_inuse_cmd = "mysql -N -e \"select distinct v.eid from tbdb.virt_nodes as v left join tbdb.experiments as e on v.pid=e.pid and v.eid=e.eid where v.pid=\'" + pid + "\' and v.osname=\'" + image_name + "\'\""
        check_inuse_query = subprocess.check_output(check_inuse_cmd, shell=True)

        experiments_list = check_inuse_query.rstrip().split('\n')

        if experiments_list[0] != '':
            logging.warning('Deleting image %s from user %s of project %s: experiments {} still using image',
                            image_name, uid, pid, experiments_list)

            result['msg'] = 'image still in use';
            result['experiments'] = experiments_list;
            return json.dumps(result)
        else:
            # statement receive when executing Curl command
            succesful_statement = "Image " + "\'" + image_id \
                                  + "\'" + " in project " + pid \
                                  + " has been deleted!"
            no_permission_statement = "You do not have permission to destroy ImageID"
            no_imageid_statement = "Could not map page arguments to 'image'"
            image_in_use_statement = "Image " + image_id + " is still in use or busy!"

            logging.info('Deleting imageid %s from web: executing curl command', image_id)
            deter_uri = getDeterURI()
            cookie_file = uid + '.txt'
            # Curl options:
            # -s silent mode
            # -L redo the request on the new place if the requested page has moved to a different location
            # -k  This option allows curl to proceed and operate even for server connections otherwise considered insecure.
            # -b  Pass the data to the HTTP server in the Cookie header
            # -X  Specifies a custom request method to use when communicating with the HTTP server.
            # -F  this lets curl emulate a filled-in form
            # -d data
            curl_cmd = "curl -s -L -k -b " + cookie_file + " -X POST" + \
                       " -d \"imageid=" + image_id + \
                       "&confirmed=Confirm\"" + \
                       " '" + deter_uri + "deleteimageid.php'"
            curl = subprocess.Popen(curl_cmd, shell=True, stdout=subprocess.PIPE)
            curl_out, curl_err = curl.communicate()

            if curl.returncode != 0:
                logging.warning(
                    'Deleting image %s from user %s of project %s: error in DeterLab when using Curl command',
                    image_name, uid, pid)
                return '{"msg": "unknown error when executing Curl command"}'

            elif no_imageid_statement in curl_out:
                logging.warning(
                    'Deleting image %s from user %s of project %s: imageid in Curl command could not be found',
                    image_name, uid, pid)
                return '{"msg": "imageid could not be found when executing Curl command"}'

            elif no_permission_statement in curl_out:
                logging.warning('Deleting image %s from user %s of project %s: no permission to delete the imageid',
                                image_name, uid, pid)
                return '{"msg": "no permission to delete the imageid when executing Curl command"}'

            elif image_in_use_statement in curl_out:
                logging.warning('Deleting image %s from user %s of project %s: image still in use', image_name, uid,
                                pid)
                return '{"msg": "image still in use"}'

            # if Curl is succesfull, we start to remove physical image and image signature in proj directory
            # note: image file will exist but signature file may or may not exist
            elif succesful_statement in curl_out:

                logging.info(
                    'Succesfully delete image from web , starting to deleting physical image and image signature in project %s directory',
                    pid)

                rm_cmd = "sudo rm " + image_filepath + " && sudo rm " + image_sig_filepath
                rm = subprocess.Popen(rm_cmd, shell=True, stdout=subprocess.PIPE)
                rm_out, rm_err = rm.communicate()

                # if no file in directory, the output is in rm_error
                if '' in rm_out or 'No such file or directory' in rm_err:
                    return '{"msg":"delete image OK from both web and project directory"}'

                else:
                    logging.warning(
                        'Deleting image %s from user %s of project %s: error in DeterLab when using rm command',
                        image_name, uid, pid)
                    return '{"msg":"delete image OK from web but there is unknown error when deleting physical image"}'

        # if some other statement is captured then return unknown error
        return '{"msg":"unknown error in deleting image from web"}'

###########################################################################################
# get saved images by team
class get_saved_images:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        logging.info("Getting saved images for team %s", pid)

        cmd = "mysql -N -e \"select osname from tbdb.os_info where pid=\'" + pid + "\'\""
        out = subprocess.check_output(cmd, shell=True)
        image_list = out.split()

        result = {}

        if image_list:
            for osname in image_list:
                # check if the .ndz file exists or not
                ndz_file = "/proj/" + pid + "/images/" + osname + ".ndz"
                cmd = "sudo test -e \"" + ndz_file + "\"; echo $?"
                out = subprocess.check_output(cmd, shell=True)
                # the ndz file does not exist
                if out.strip() == '1':
                    logging.warning("Image file %s does not exist.", ndz_file)
                    result[osname] = 'notfound'
                # the ndz file exists
                else:
                    cmd = "sudo stat -f %z " + ndz_file
                    out = subprocess.check_output(cmd, shell=True)
                    if out.strip() == '0':
                        # .ndz file exists but size 0
                        # if the file size remains 0 for a certain amount of time, meaning that the saving failed
                        cmd = 'sudo stat -f %m ' + ndz_file
                        mtime = subprocess.check_output(cmd, shell=True)
                        if (time.time() - float(mtime.strip())) > 3600 * 24:
                            logging.warning("Size of %s remains 0 for more than 24 hours, saving failed", ndz_file)
                            result[osname] = 'failed'
                        else:
                            result[osname] = 'saving'
                    else:
                        # get size of signature file to check if image has been created successfully
                        # signature file size should be more than zero bytes
                        sig_file = "/proj/" + pid + "/images/sigs/" + osname + ".ndz.sig"
                        cmd = "sudo test -e \"" + sig_file + "\"; echo $?"
                        out = subprocess.check_output(cmd, shell=True)
                        if out.strip() == '1':
                            result[osname] = 'saving'
                        else:
                            cmd = "sudo stat -f %z " + sig_file
                            out = subprocess.check_output(cmd, shell=True)
                            if out.strip() == '0':
                                result[osname] = 'saving'
                            else:
                                result[osname] = 'created'

        logging.debug("Saved images for team %s: %s", pid, result)
        # result dictionary looks like this:
        # { 'imageA' : 'notfound', 'imageB' : 'saving', 'imageC' : 'created' }
        return json.dumps(result)

###########################################################################################
# get cve images that are globally shared
# returns a dump of the resulting dictionary
class get_cve_images:
    def GET(self):
        supported = "4"
        cmd = "mysql -N -e \"select a.imagename, b.OS, a.description from tbdb.images a left join tbdb.os_info b on a.imagename=b.osname where b.supported = " + supported + " order by a.imagename;\""
        p = subprocess.check_output(cmd, shell=True)

        result = {}

        if p:
            logging.info('Retrieving list of global images')
            # ['imageA \t osname \t description \n imageB \t osname \t description']
            image_list = p.split('|')

            # ['imageA \t osname \t description', 'imageB \t osname \t description']
            image_list = image_list[0].split('\n')

            for images in image_list:
                # images may be empty
                if images:
                    # now get individual columns
                    # images_details[0] = image
                    # images_details[1] = osname
                    # images_details[2] = description
                    images_details = images.split('\t')
                    images_details_dict = {}

                    images_details_dict['osname'] = images_details[1]
                    images_details_dict['description'] = images_details[2]

                    result[images_details[0]] = images_details_dict

        # result dictionary looks like this:
        # if empty : {}
        # if non-empty: {'imageA' : {'os' : 'Linux', 'description' : 'Ubuntu 12.04 LTS 64 bit'}, 'imageB' : {...}}
        return json.dumps(result)

###########################################################################################
# remove user from team
class remove_user_from_team:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        uid_to_be_removed = parsed_json['uidToBeRemoved']
        owner_uid = parsed_json['ownerUid']

        logging.info('Removing user %s from team %s, team owner %s', uid_to_be_removed, pid, owner_uid)

        membership = get_group_membership_status(pid, uid_to_be_removed)
        if membership == "not found":
            logging.info('Remove user %s from team %s: no membership record found', uid_to_be_removed, pid)
            return '{"msg":"remove user from team ok"}'

        deter_uri = getDeterURI()
        cookie_file = owner_uid + '.txt'

        cmd = "curl -L -s -k -b " + cookie_file + " -d \"confirmed=Confirm&confirmed_twice=Confirm\"" + \
              " '" + deter_uri + "deleteuser.php?user=" + uid_to_be_removed + "&pid=" + pid + "'"

        out = subprocess.check_output(cmd, shell=True)

        membership = get_group_membership_status(pid, uid_to_be_removed)

        if membership == "not found" and 'Done' in out:
            logging.info('Remove user %s from team %s: ok', uid_to_be_removed, pid)
            return '{"msg":"remove user from team ok"}'
        elif "They must be terminated before you can remove the user!" in out:
            logging.warning('Remove user %s from team %s: failed - user has experiments', uid_to_be_removed, pid)
            return '{"msg":"user has experiments"}'
        else:
            logging.warning('Remove user %s from team %s: failed, member status is %s', uid_to_be_removed, pid,
                            membership)
            logging.debug('Remove user %s from team %s: Deter Output -- %s', uid_to_be_removed, pid, out)
            return '{"msg":"remove user from team failed"}'

###########################################################################################
# get number of logged in users
class get_logged_in_users:
    def GET(self):
        logging.info('Retrieving number of logged in users...')

        cmd = "mysql -N -e \"select count(distinct uid) from tbdb.login where timeout > UNIX_TIMESTAMP(now());\""

        out = subprocess.check_output(cmd, shell=True)

        output_list = out.split('\n')
        logging.info('Number of logged in users returned: %s', output_list)

        # if sql query returns empty set: the output_list is ['']
        if output_list[0] == '':
            return 0

        return output_list[0]

###########################################################################################
# get number of running experiments
class get_running_experiments_count:
    def GET(self):
        logging.info('Retrieving number of running experiments...')

        cmd = "mysql -N -e \"select count(*) from tbdb.experiments where state = 'active' and pid != 'emulab-ops' and pid != 'testbed';\""

        out = subprocess.check_output(cmd, shell=True)

        output_list = out.split('\n')
        logging.info('Number of running experiments returned: %s', output_list)

        # if sql query returns empty set: the output_list is ['']
        if output_list[0] == '':
            return 0

        return output_list[0]

###########################################################################################
# modify experiment
# check ns file syntax before modifying experiment
# waits for the modification to complete
class modify_ns_file:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        uid = parsed_json['uid']
        nsfile = parsed_json['nsfile']
        logging.info('Modifying experiment %s/%s ns file by %s', pid, eid, uid)

        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'
        result = {}

        # get the idx from the deter database
        cmd = "mysql -N -e \"select idx from tbdb.experiments where eid='" + eid + "'" + " AND pid ='" + pid + "'\""

        exp_idx = subprocess.check_output(cmd, shell=True)

        if exp_idx:
            cmd_line = "curl -L --silent -b " + cookie_file + " -k -d \"MAX_FILE_SIZE=512000" + \
                       "&exp_nsfile=" + "\"\"" + \
                       "&exp_localnsfile=" + "\"\"" + \
                       "&nsdata=" + urllib.quote(nsfile) + \
                       "&go=Modify" + "\" " + \
                       deter_uri + "modifyexp.php?experiment=" + exp_idx

            out = subprocess.check_output(cmd_line, shell=True)

            if "Modified NS file contains syntax errors" in out or "Experiment modify could not proceed" in out:
                # get the error output
                error_output = re.search('
((.|\n)*)
', out).group(1)
                logging.warning('Modify experiment %s/%s ns file failed: %s', pid, eid, error_output)
                result['msg'] = 'modify experiment fail ns parse error'
                result['modify_experiment'] = error_output
            else:
                # experiment is modifying
                # inspect the log file
                logfile_cmd = "mysql -N -e \"select logfile from tbdb.experiments where eid='" + eid + "'" + " AND pid ='" + pid + "'\""
                logfile_id = subprocess.check_output(logfile_cmd, shell=True)

                inspect_cmd = "curl -L -s -k -b " + cookie_file + " " + deter_uri + "/spewlogfile.php?logfile=" + logfile_id
                logfile_output = subprocess.check_output(inspect_cmd, shell=True)

                if "Swap Success" in logfile_output:
                    logging.info('Modify experiment %s/%s ns file succeeded', pid, eid)
                    result['msg'] = 'modify experiment success'
                    result['modify_experiment'] = logfile_output.lstrip()
                else:
                    logging.warning('Modify experiment %s/%s ns file failed: %s', pid, eid, logfile_output.lstrip())
                    result['msg'] = 'modify experiment fail'
                    result['modify_experiment'] = logfile_output.lstrip()
        else:
            logging.warning('Experiment %s/%s not found in database', pid, eid)
            result['msg'] = 'modify experiment fail'
            result['modify_experiment'] = 'Experiment not found in database'

        return json.dumps(result)

###########################################################################################
# modify experiment settings
# different PHP call from modify_ns_file
# max duration, swap. etc
class modify_experiment_settings:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        uid = parsed_json['uid']
        logging.info('Modifying experiment %s/%s settings by %s', pid, eid, uid)

        autoswap_timeout = str(parsed_json['maxDuration'])
        autoswap_enable = "0"
        if autoswap_timeout != "0":
            # experiment set to swap out after some hours
            autoswap_enable = "1"
        else:
            # experiment will never auto swapout
            # set the default value to something else other than 0 as the webpage checks for this parameter
            autoswap_timeout = "16"

        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'
        result = {}

        # get the idx from the deter database
        cmd = "mysql -N -e \"select idx from tbdb.experiments where eid='" + eid + "'" + " AND pid ='" + pid + "'\""

        exp_idx = subprocess.check_output(cmd, shell=True)

        if exp_idx:
            cmd_line = "curl --silent -b " + cookie_file + " -k -d \"formfields[autoswap_timeout]=" + autoswap_timeout + \
                       "&formfields[autoswap]=" + autoswap_enable + \
                       "&submit=Submit" + "\" " + \
                       deter_uri + "editexp.php?experiment=" + exp_idx

            out = subprocess.check_output(cmd_line, shell=True)

            if "please fix the following errors" in out:
                logging.warning('Modify experiment %s/%s settings failed: %s', pid, eid, out)
                result['msg'] = 'modify experiment settings: max_duration fail'
            else:
                logging.info('Modify experiment %s/%s settings succeeded', pid, eid)
                result['msg'] = 'modify experiment settings: max_duration success'

        return json.dumps(result)

##############################################################
# get the list of experiments of a project
# If an experiment is active, also returns the realization details
class get_experiments_by_team:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        logging.info('Getting list of experiments for %s', pid)

        cmd = "mysql -N -e \"select e.pid, e.eid, e.state, count(r.node_id) as nodes, round(e.minimum_nodes+.1,0) as min_nodes " + \
              " from tbdb.experiments as e " + \
              " left join tbdb.reserved as r on e.pid=r.pid and e.eid=r.eid " + \
              " where e.pid='" + pid + "' " + \
              " group by e.pid,e.eid order by e.state,e.eid\""

        out = subprocess.check_output(cmd, shell=True)
        #
        # +------------+-----------+---------+-------+-----------+
        # | nclproject | demo      | active  |     2 |         2 |
        # | nclproject | containe2 | swapped |     0 |         2 |
        # | nclproject | container | swapped |     0 |         1 |
        # +------------+-----------+---------+-------+-----------+

        out_list = out.split('\n')
        result = {}

        for expt in out_list:
            if expt:
                expt_details = expt.split()
                eid = expt_details[1]
                state = expt_details[2]
                tmp = {}
                tmp['state'] = state
                tmp['nodes'] = expt_details[3]
                tmp['min_nodes'] = expt_details[4]
                tmp['idle_hours'] = '0'  # will implement the idle-hours later

                if state == 'active':
                    tmp['details'] = get_exp_nodes_info(pid, eid)
                else:
                    tmp['details'] = ''

                result[eid] = tmp

        # {
        # 	"eid1": {"state": "active", "min_nodes": "2", "nodes": "2", "idle_hours": "0", "details": "..."},
        # 	"eid2": {"state": "swapped", "min_nodes": "1", "nodes": "0", "idle_hours": "0", "details": ""},
        # 	"eid3": {"state": "active", "min_nodes": "2", "nodes": "2", "idle_hours": "0", "details": "..."}
        # }
        return json.dumps(result)

##############################################################
# get the status of an experiment of a project
# If the experiment is active, also returns the realization details
class get_experiment:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        logging.info('Getting detailed status for %s/%s', pid, eid)

        cmd = "mysql -N -e \"select e.pid, e.eid, e.state, count(r.node_id) as nodes, round(e.minimum_nodes+.1,0) as min_nodes " + \
              " from tbdb.experiments as e " + \
              " left join tbdb.reserved as r on e.pid=r.pid and e.eid=r.eid " + \
              " where e.pid='" + pid + "' and e.eid='" + eid + "' group by e.pid,e.eid\""

        out = subprocess.check_output(cmd, shell=True)

        # +------------+-----------+---------+-------+-----------+
        # | nclproject | demo      | active  |     2 |         2 |
        # +------------+-----------+---------+-------+-----------+

        out_list = out.split('\n')
        result = {}

        if len(out_list) >= 1 and out_list[0]:
            expt = out_list[0]
            expt_details = expt.split()
            eid = expt_details[1]
            state = expt_details[2]
            tmp = {}
            tmp['state'] = state
            tmp['nodes'] = expt_details[3]
            tmp['min_nodes'] = expt_details[4]
            tmp['idle_hours'] = '0'  # will implement the idle-hours later

            if state == 'active':
                tmp['details'] = get_exp_nodes_info(pid, eid)
            else:
                tmp['details'] = ''

            result[eid] = tmp

        # {
        # 	"eid1": {"state": "active", "min_nodes": "2", "nodes": "2", "idle_hours": "0", "details": "..."}
        # }
        return json.dumps(result)

####################################################################
# get the virtual to physical nodes mapping for an active experiment
def get_exp_nodes_info(pid, eid):
    logging.info('Getting nodes allocation info for %s/%s', pid, eid)

    sql_cmd = "mysql -N -e \"select r.vname,r.node_id,n.type,n.phys_nodeid,o.osname " + \
              " from tbdb.reserved as r " + \
              " left join tbdb.nodes as n on r.node_id=n.node_id " + \
              " left join tbdb.os_info as o on o.osid=n.def_boot_osid " + \
              " where r.pid='" + pid + "' and r.eid='" + eid + "' order by vname\""

    out = subprocess.check_output(sql_cmd, shell=True)
    # logging.debug(out)
    # +-------+---------+---------+-------------+---------------------+
    # | vname | node_id | type    | phys_nodeid | osname              |
    # +-------+---------+---------+-------------+---------------------+
    # | n0    | pc5     | ServerA | pc5         | Ubuntu16.04.3-64-pr |
    # | n1    | pc2     | ServerA | pc2         | Ubuntu16.04.3-64-pr |
    # +-------+---------+---------+-------------+---------------------+

    out_list = out.split('\n')
    result = {}
    for line in out_list:
        if line:
            tmp_list = line.split()
            type = tmp_list[2]
            if type != 'ofedge' and type != 'ofcore' and type != 'ovxctl':
                tmp = {}
                tmp['nodeId'] = tmp_list[1]
                tmp['type'] = tmp_list[2]
                tmp['os'] = tmp_list[4]
                tmp['qualifiedName'] = tmp_list[0] + '.' + eid + '.' + pid + '.' + getDomain()

                result[tmp_list[0]] = tmp

    # {'n0': {'qualifiedName': 'n0.akamai-data-test.minsukgroup.ncl.sg', 'os': 'Ubuntu1404-64-STD', 'nodeId': 'pc10b', 'type': 'SystemX'},
    #  'n1': {'qualifiedName': 'n1.akamai-data-test.minsukgroup.ncl.sg', 'os': 'Ubuntu1404-64-STD', 'nodeId': 'pc11b', 'type': 'SystemX'}}
    # logging.debug(result)
    return json.dumps(result)

####################################################################
# get/add/delete ssh public key
class get_pub_keys:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        uid = parsed_json['uid']
        logging.info('Getting public keys for %s', uid)

        cmd = "mysql -N -e \"select distinct comment, pubkey, stamp, idx from tbdb.users a, tbdb.user_pubkeys b " \
              "where a.uid='" + uid + "' and a.uid_idx=b.uid_idx and b.external=1;\""
        p = subprocess.check_output(cmd, shell=True)
        r = {}

        if p:
            key_list = p.split('|')
            key_list = key_list[0].split('\n')

            for keys in key_list:
                if keys:
                    keys_details = keys.split('\t')
                    keys_details_dict = {}

                    keys_details_dict['comment'] = keys_details[0]
                    keys_details_dict['pubkey'] = keys_details[1]
                    keys_details_dict['stamp'] = keys_details[2]

                    r[keys_details[3]] = keys_details_dict

        return json.dumps(r)

class add_pub_key:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)

        uid = parsed_json['uid']
        pubkey = parsed_json['pubkey']
        passwd = parsed_json['passwd']
        logging.info('Adding public key for %s', uid)

        filename = "/tmp/" + str(time.time()) + ".pub"
        file = open(filename, "w")
        file.write(pubkey)
        file.close()

        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'

        cmd = "curl -L --silent -k -b " + cookie_file + \
              " -F \"MAX_FILE_SIZE=4096" + "\"" + \
              " -F \"usr_keyfile=@" + filename + "\"" + \
              " -F \"formfields[password]=" + passwd + "\"" + \
              " -F \"submit=Submit\" \"" + deter_uri + "showpubkeys.php?user=" + uid + "\" -v"

        out = subprocess.check_output(cmd, shell=True)
        err = {}

        if out.find("No such file") > -1:
            err['PubKey File'] = "No such file"
        elif out.find("Invalid characters") > -1:
            err['PubKey File'] = "Invalid characters"

        if out.find("Must supply a verification password") > -1:
            err['Password'] = "Must supply a verification password"
        elif out.find("Incorrect password") > -1:
            err['Password'] = "Incorrect password"

        if out.find("Please supply keyfile") > -1:
            err['Missing Args'] = "Please supply keyfile"

        if out.find("Could not be parsed. Is it a public key?") > -1:
            err['Pubkey Format'] = "Could not be parsed. Is it a public key?"

        if err:
            logging.warning('Add public key for %s failed: %s', uid, out)
        else:
            logging.info('Add public key for %s succeeded', uid)

        return json.dumps(err)

class delete_pub_key:
    def DELETE(self):
        data = web.data()
        parsed_json = json.loads(data)

        uid = parsed_json['uid']
        kid = parsed_json['kid']
        logging.info('Deleting public key %s for %s', kid, uid)

        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'

        cmd = "curl -L --silent -k -b " + cookie_file + \
              " -F \"confirmed=Confirm\" \"" + deter_uri + "deletepubkey?user=" + uid + "&key=" + kid + "\" -v"

        out = subprocess.check_output(cmd, shell=True)

        return out

###########################################################################################
################################### GET RESERVATION #######################################
###########################################################################################
"""
output is a json dump of a dictionary in the following format
{"status": "OK", "reserved": ["pc1", "pc4", "pc2"], "in_use": [["pc4", "ncltest01", "vnctest"], ["pc2", "testbed-ncl", "thales-poc"], ["pc1", "testbed-ncl", "test"]]}
"""

class get_reservation:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']

        output = {}

        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cursor = db.cursor()

        # get reserved nodes from tbdb.nodes
        # get in_use nodes from tbdb.reserved

        try:
            cursor.execute("select node_id from tbdb.nodes where reserved_pid='" + pid + "'")
            results = cursor.fetchall()
            reserved_list = [row[0] for row in results]

            in_use_list = []

            if reserved_list:
                # convert reserved list into a string for sql statement
                # e.g. ['A','B'] -> ('A','B')
                reserved_list_str = "('" + "','".join(reserved_list) + "')"
                query = 'SELECT node_id, pid, eid FROM tbdb.reserved WHERE node_id IN ' + reserved_list_str
                cursor.execute(query)
                in_use_list = cursor.fetchall()

            output['status'] = 'OK'
            output['reserved'] = reserved_list
            output['in_use'] = in_use_list
            logging.info("Get reservation for %s succeeded", pid)
        except:
            output['status'] = 'FAIL'
            output['reserved'] = []
            output['in_use'] = []
            logging.warning("Get reservation for %s failed", pid)

        db.close()

        return json.dumps(output)

###########################################################################################
################################### RELEASE NODES #########################################
###########################################################################################
"""
release a number of nodes from project
pid: project name
numNodes: number of nodes to release, -1 means releasing all the reserved nodes
return all node_ids that are released
output is a json dump of a dictionary in the following format
{ status : 'OK/FAIL', 'released' : [node_id_list] }
"""

class release_nodes:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        num_of_nodes_to_release = parsed_json['numNodes']

        output = {}
        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cursor = db.cursor()

        try:
            # get all reserved nodes currently
            cursor.execute("select node_id from tbdb.nodes where reserved_pid='" + pid + "'")
            results = cursor.fetchall()
            reserved_list = [row[0] for row in results]

            # get all nodes that are currently used by this team
            cursor.execute("select node_id from tbdb.reserved where pid='" + pid + "'")
            results = cursor.fetchall()
            in_use_list = [row[0] for row in results]

            # compute actual number of nodes to release
            num_of_nodes_reserved = len(reserved_list)
            if int(num_of_nodes_to_release) < 0 or int(num_of_nodes_to_release) > num_of_nodes_reserved:
                actual_num_of_nodes_to_release = num_of_nodes_reserved
            else:
                actual_num_of_nodes_to_release = num_of_nodes_to_release

            reserved_not_in_use_list = [node for node in reserved_list if node not in in_use_list]
            reserved_in_use_list = [node for node in reserved_list if node in in_use_list]
            reserved_list = reserved_not_in_use_list + reserved_in_use_list
            release_list = reserved_list[0:actual_num_of_nodes_to_release]

            if release_list:
                # convert release list into a string for sql statement
                # e.g. ['A','B'] -> ('A','B')
                release_list_str = "('" + "','".join(release_list) + "')"

                cursor.execute(
                    "update tbdb.nodes set reserved_pid = NULL where node_id IN " + release_list_str)

            output['status'] = 'OK'
            output['released'] = release_list
            logging.info("Release nodes for %s succeeded", pid)
        except:
            output['status'] = 'FAIL'
            output['released'] = []
            logging.warning("Release nodes for %s failed", pid)

        db.close()

        return json.dumps(output)

###########################################################################################
################################### RESERVE NODES #########################################
###########################################################################################
"""
reserve nodes for a project
pid: project name
numNodes: number of nodes to reserve
machineType: particular type of machine to reserve

output is a json dump of a dictionary in the following format
{ status : 'OK/FAIL', 'message' : 'XXXX', 'reserved' : [node_id_list] }
"""

class reserve_nodes:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        num_of_nodes = parsed_json['numNodes']
        machine_type = parsed_json['machineType']

        output = {}

        # sanitization
        if not num_of_nodes or int(num_of_nodes) <= 0:
            output['status'] = 'FAIL'
            output['message'] = 'Number of nodes not specified'
            output['reserved'] = []
            logging.warning("Reserve %s nodes for %s failed: no of nodes not specified", num_of_nodes, pid)
            return json.dumps(output)

        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cursor = db.cursor()

        try:
            # retrieve list of nodes that are currently used by this project
            # need to filter out node_id like 'ofport10d' which are virtual nodes for SDN
            if machine_type:
                sql_stmt1 = "select r.node_id from tbdb.reserved as r left join tbdb.nodes as n on r.node_id=n.node_id " + \
                            "where r.pid = '" + pid + "' and n.type = '" + machine_type + "' and n.node_id like 'pc%'"
            else:
                sql_stmt1 = "select node_id from tbdb.reserved where pid ='" + pid + "' and node_id like 'pc%'"

            cursor.execute(sql_stmt1)
            in_use_list = [row[0] for row in cursor.fetchall()]

            # compute the list of nodes that are in use but have not been reserved for any project
            in_use_not_reserved_list = []

            if in_use_list:
                in_use_list_str = "('" + "','".join(in_use_list) + "')"
                sql_stmt2 = "select node_id from tbdb.nodes where reserved_pid is NOT NULL and node_id in " + in_use_list_str
                cursor.execute(sql_stmt2)
                in_use_reserved_list = [row[0] for row in cursor.fetchall()]
                in_use_not_reserved_list = [node for node in in_use_list if node not in in_use_reserved_list]

            # retrieve the list of free nodes
            sql_stmt3 = "select n.node_id from tbdb.nodes as n left join tbdb.reserved as r on r.node_id=n.node_id " + \
                        "where role='testnode' " + \
                        "and n.node_id like 'pc%' " + \
                        "and (n.eventstate='POWEROFF' or n.eventstate='ISUP' or n.eventstate='PXEWAIT' or n.eventstate='ALWAYSUP') " + \
                        "and r.pid is NULL and n.reserved_pid is NULL"

            if machine_type:
                sql_stmt3 += " and n.type = '" + machine_type + "'"

            cursor.execute(sql_stmt3)
            free_nodes_list = [row[0] for row in cursor.fetchall()]

            # this is the list of nodes that can be reserved for this project
            reservable_list = in_use_not_reserved_list + free_nodes_list

            if int(num_of_nodes) > len(reservable_list):
                output['status'] = 'FAIL'
                output['message'] = 'Insufficient number of nodes: only ' + str(
                    len(reservable_list)) + ' nodes available'
                output['reserved'] = []
                logging.warning("Reserve %s nodes for %s failed: insufficient no. of nodes", num_of_nodes, pid)
            else:
                to_reserve_list = reservable_list[0:num_of_nodes]
                to_reserve_list_str = "('" + "','".join(to_reserve_list) + "')"
                cursor.execute(
                    "update tbdb.nodes set reserved_pid = '" + pid + "' where node_id in " + to_reserve_list_str)
                num_of_rows_changed = cursor.rowcount

                if num_of_rows_changed != len(to_reserve_list):
                    output['status'] = 'FAIL'
                    output['message'] = 'Only ' + str(num_of_rows_changed) + '/' + str(
                        len(to_reserve_list)) + ' nodes were successfully reserved'
                    output['reserved'] = []
                    logging.warning("Reserve %s nodes for %s partially failed: only %s nodes reserved", num_of_nodes,
                                    pid, num_of_rows_changed)
                else:
                    output['status'] = 'OK'
                    output['message'] = ''
                    output['reserved'] = to_reserve_list
                    logging.info("Reserve %s nodes for %s succeeded", num_of_nodes, pid)

        except:
            output['status'] = 'FAIL'
            output['message'] = 'Failed to reserve nodes for ' + pid
            output['reserved'] = []
            logging.warning("Reserve %s nodes for %s failed: error in SQL execution", num_of_nodes, pid)

        db.close()

        return json.dumps(output)

########################################################
## get all the experiment statistics for a project
########################################################
class show_stats_by_project:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        r = {}

        logging.info('Getting experiment stats for %s', pid)

        cmd = "select t.exptidx, t.start_time, t.end_time, t.action, r.pnodes " + \
              "from tbdb.testbed_stats as t " + \
              "left join tbdb.experiment_stats as s on s.exptidx=t.exptidx " + \
              "left join tbdb.experiment_resources as r on r.idx=t.rsrcidx " + \
              "where s.pid='" + pid + "' and t.exitcode=0 " + \
              "order by t.start_time asc"

        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cur = db.cursor()
        cur.execute(cmd)

        for counter, stats_details in enumerate(cur.fetchall()):
            stats_details_dict = {}
            stats_details_dict['exptidx'],\
            stats_details_dict['start_time'],\
            stats_details_dict['end_time'],\
            stats_details_dict['action'],\
            stats_details_dict['pnodes'] = stats_details
            r[counter] = stats_details_dict

        db.close()

        return json.dumps(r, default=str)

###################################################
## check if a class project has been set up or not;
## if not, set it up as needed
###################################################
"""
pid: the project to set up
uid: uid of the project leader
"""
def set_up_class_if_needed(pid, uid):
    logging.info('Checking class setup for %s by user %s', pid, uid)
    db = MySQLdb.connect(host=getDBURI(), db=getDBName())
    cursor = db.cursor()

    try:
        cursor.execute("SELECT * FROM class_schedule WHERE pid='" + pid + "'")
        rows = cursor.fetchall()
        if not rows: # no schedule found, need to set up the class
            logging.info('Class %s has not been set up. Setting it up now...', pid)
            cookie_file = uid + '.txt'
            deter_uri = getDeterURI()

            # hardcoded, class end date = class start/created date + 1 year
            today = datetime.datetime.strftime(datetime.datetime.now(),'%Y-%m-%d')
            class_end = datetime.datetime.strftime(datetime.datetime.now()+datetime.timedelta(365),'%Y-%m-%d')

            cmd = "curl -L -s -k -b " + cookie_file + \
                  " -d \"semesterends=" + class_end + \
                  "&students=100" + \
                  "&start[1]=" + today + \
                  "&due[1]=" + class_end + \
                  "&perstudent[1]=1&limit[1]=100\""+ \
                  " \'" + deter_uri + "manage_class.php?pid=" + pid + "&action=finishsetup" + "\'"

            out = subprocess.check_output(cmd, shell=True)

            if ('manage_class.php?pid=' + pid + '&action=manage') in out: # Deter redirect to manage students page
                logging.info('Done')
                result = 'ok'
            else:
                logging.warning('There is an error in setting up class %s:\n %s', pid, out)
                result = 'fail'
        else:
            logging.info('Class %s has already been set up', pid)
            result = 'ok'
    except:
        logging.warning('There is an error in checking class schedule for %s', pid)
        result = 'fail'

    db.close()

    return result

###########################################################################################
## add class members by emails
###########################################################################################

"""
add class members by emails
pid: project name
uid: project leader
emails: a string in the format 'hello01@gmail.com\r\nhello02@gmail.com\r\nhello03@gmail.com'

output is a json dump of a dictionary in the following format
{'email1': 'uid1', 'email2': 'uid2'} or
{'error': 'error message'}

"""
class add_students_by_email:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        uid = parsed_json['uid']
        emails = parsed_json['emails']

        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'
        result = {}

        logging.debug('Adding students %s to project %s by user %s', emails, pid, uid)

        # first check if class schedule has been set up
        if set_up_class_if_needed(pid, uid) == 'fail':
            result['error'] = 'Class schedule is not found or cannot be set up'
            return json.dumps(result)

        # ask Deter to add new class members
        cmd = "curl -L -s -k -b " + cookie_file + \
                " -d \"students=" + \
                urllib.quote_plus(emails) + "&trust=localroot\"" + \
                " \'" + deter_uri + "manage_class.php?pid=" + \
                pid + "&action=assign" + "\'"

        out = subprocess.check_output(cmd, shell=True)

        if 'Your accounts are being created' in out:
            # update chpasswd_expires for the new users
            db = MySQLdb.connect(host=getDBURI(), db=getDBName())
            cursor = db.cursor()
            try:
                email_list = emails.split('\r\n')
                for email in email_list:
                    if email:
                        cursor.execute("SELECT uid FROM email_aliases WHERE usr_email='" + email + "'")
                        results = cursor.fetchall()
                        if len(results) < 1:
                            logging.warning('Error in adding members to project %s by user %s: Table email_aliases is not populated', pid, uid)
                            result['error'] = "Table email_aliases is not populated"
                            break
                        else:
                            generated_uid = results[0][0]
                            # change chpasswd_expires to max int(10)
                            cursor.execute("UPDATE users SET chpasswd_expires=4294967295 WHERE uid='" + generated_uid +"'")
                            if cursor.rowcount != 1:
                                logging.warning('Error in adding members to project %s by user %s: Failed to update users.chpasswd_expires', pid, uid)
                                result['error'] = "Failed to update table users"
                                break
                            else:
                                logging.info("Added member %s to project %s", email, pid)
                                result[email] = generated_uid
            except:
                logging.warning('Error in adding members to project %s by user %s: SQL execution failed', pid, uid)
                result['error'] = "Failed to execute SQL"

            db.close()

        elif "Invalid address" in out:
            logging.warning('Error in adding members to project %s by user %s: Invalid address', pid, uid)
            result['error'] = "Invalid email address"
        else:
            logging.warning('Error in adding members to project %s by user %s: %s', pid, uid, out)
            result['error'] = "Unknown error. Please contact NCL Support."

        return json.dumps(result)

###########################################################################################
## reset password for new student member
class change_password_student:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        uid = parsed_json['uid']
        first_name = parsed_json['firstName']
        last_name = parsed_json['lastName']
        phone = parsed_json['phone']
        password = parsed_json['password']
        logging.info('Resetting password for new class member %s', uid)

        deter_uri = getDeterURI()
        cookie_file = uid + '.txt'
        result = {}

        # get key and uid_idx
        sql_cmd = "mysql -N -e \"select uid_idx, chpasswd_key from tbdb.users where uid='" + uid + "'\""
        sql_cmd_out = subprocess.check_output(sql_cmd, shell=True)
        out_list = sql_cmd_out.split('\n')

        if len(out_list) == 0:
            logging.warning('Key and uid_idx not found in table users')
            result['error'] = "Key and uid_idx not found in table users"
        else:
            columns = out_list[0].split()
            uid_idx = columns[0]
            key = columns[1]

        escaped_passwd = urllib.quote_plus(password)

        cmd = "curl -L -s -k -b " + cookie_file + \
                " --data \"usr_name=" + first_name + "+" + last_name +\
                "&usr_phone=" + phone + \
                "&password1=" + escaped_passwd + \
                "&password2=" +  escaped_passwd + \
                "&reset=Reset+Password\"" + \
                 " \'" + deter_uri + "chpasswdstudent.php?user=" + \
                uid_idx + "&key=" + key + "&simple=0" + "\'"

        out = subprocess.check_output(cmd, shell=True)

        if "Your password has been changed" in out:
            logging.info('Done')
        elif "You must enter a valid first and last name" in out:
            logging.warning('First name or last name is not valid')
            result['error'] = "First name or last name is not valid"
        elif "You must supply a password" in out:
            logging.warning('Password is not supplied')
            result['error'] = "Password is not supplied"
        elif "Invalid Password" in out:
            logging.warning('Password is not valid')
            result['error'] = "Password is not valid"
        else:
            logging.warning('Error:\n%s', out)
            result['error'] = "Unknown error. Please contact NCL Support."

        return json.dumps(result)

########################################################################
## show all the projects that have reserved nodes
########################################################################
class show_projects_with_reservation:
    def GET(self):
        logging.info('Getting list of projects with reservation')
        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cursor = db.cursor()
        cursor.execute("select distinct(reserved_pid) from nodes where reserved_pid is not null and node_id like 'pc%'")
        result = cursor.fetchall()
        db.close()
        return json.dumps(result)

########################################################################
## get nodes that are currently used by an experiment
########################################################################
class get_node_list:
    def POST(self):
        data = web.data()
        parsed_json = json.loads(data)
        pid = parsed_json['pid']
        eid = parsed_json['eid']
        logging.info('Getting list of nodes for %s/%s', pid, eid)
        db = MySQLdb.connect(host=getDBURI(), db=getDBName())
        cursor = db.cursor()
        cursor.execute("select node_id from reserved where pid='" + pid + "' and eid='" + eid + "' and node_id like 'pc%'")
        nodes = cursor.fetchall()
        db.close()

        result = ()
        if len(nodes) <= 0:
            logging.warning('There is no node allocated for %s/%s', pid, eid)
        else:
            for node in nodes:
                if node:
                    result += node
            logging.info("Nodes allocated to %s/%s: %s", pid, eid, result)

        return result

if __name__ == "__main__":
    logging.basicConfig(filename='log_adapter.txt', level=logging.DEBUG,
                        format='%(asctime)s - %(levelname)s - %(message)s')
    if(os.getuid() != 10003):
        print("You are not authorized to run adapter.py")
    else:
        app.run()

redpajama_set_name: RedPajamaGithub