module.py

    assertion_response_s = base64.b64encode( json.dumps( assertion_response ) )
    credential_id = assertion_response.get( "id" )
    
    q = config.db.query( "SELECT * FROM webauthn WHERE credential_id = %s", ( credential_id, ) )
    user = q.fetchone()
    
    webauthn_user = webauthn.WebAuthnUser( 
        user[ "ukey" ], session[ "username" ], session[ "username" ], None,
        user[ "credential_id" ], user[ "pub_key" ], user[ "sign_count" ], "icnml.unil.ch"
    )

    webauthn_assertion_response = webauthn.WebAuthnAssertionResponse( 
        webauthn_user,
        assertion_response,
        challenge,
        config.ORIGIN,
        uv_required = False
    )
    
    try:
        sign_count = webauthn_assertion_response.verify()
        
    except Exception as e:
        return jsonify( {
            "error": True,
            "message": "Assertion failed. Error: {}".format( e )
        } )
    
    ############################################################################
    
    if len( challenge ) % 4 != 0:
        challenge += "=" * ( 4 - ( len( challenge ) % 4 ) )
    
    newuser = base64.b64decode( challenge )
    newuser = json.loads( newuser )
    user_type = int( newuser[ "user" ][ "account_type" ] )
    email = newuser[ "user" ][ "email" ]
    email_hash = pbkdf2( email, random_data( 100 ), config.EMAIL_NB_ITERATIONS ).hash()
    request_uuid = newuser[ "user" ][ "uuid" ]
    request_id = newuser[ "user" ][ "id" ]
    username_id = newuser[ "user" ][ "username_id" ]
    
    n = config.db.query( "SELECT name FROM account_type WHERE id = %s", ( user_type, ) )
    n = n.fetchone()
    n = n[ 0 ]
    
    username = "{}_{}".format( n, username_id )
    username = username.lower()
    
    try:
        config.db.query( "UPDATE signin_requests SET validation_time = now(), assertion_response = %s, status = 'validated' WHERE id = %s", ( assertion_response_s, request_id ) )
        config.db.query( sql_insert_generate( "users", [ "username", "email", "type" ] ), ( username, email_hash, user_type ) )
        config.db.commit()
    
    except:
        return jsonify( {
            "error": True,
            "message": "Can not insert into database."
        } )
    
    ############################################################################
    
    email_content = render_jinja_html( 
        "templates/email", "signin.html",
        username = username,
        url = "https://icnml.unil.ch" + url_for( "config_new_user", uuid = newuser[ "user" ][ "uuid" ] )
    )
    
    msg = MIMEText( email_content, "html" )
    
    msg[ "Subject" ] = "ICNML - Login information"
    msg[ "From" ] = config.sender
    msg[ "To" ] = email
    
    s = smtplib.SMTP( config.smtpserver )
    s.sendmail( config.sender, [ email ], msg.as_string() )
    s.quit()
    
    ############################################################################
    
    return jsonify( {
        "error": False
    } )

@app.route( baseurl + "/do/validation_reject", methods = [ "POST" ] )
def do_validation_reject():
    """
        Reject the request for a new user.
    """
    request_id = request.form.get( "id" )
    
    try:
        sql = "UPDATE signin_requests SET status = 'rejected' WHERE id = %s"
        r = config.db.query( sql, ( request_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except Exception as e:
        return jsonify( {
            "error": True
        } )

@app.route( baseurl + "/config/<uuid>" )
def config_new_user( uuid ):
    """
        Serve the first page to the new user to configure his account.
    """
    session.clear()
    
    q = config.db.query( "SELECT email FROM signin_requests WHERE uuid = %s", ( uuid, ) )
    r = q.fetchone()
    try:
        email = r[ "email" ]
        session[ "signin_user_validation_email" ] = email
        session[ "signin_user_validation_uuid" ] = uuid
        
        return render_template( 
            "users/config.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            envtype = envtype,
            next_step = "do_config_new_user"
        )
    
    except:
        return redirect( url_for( "home" ) )

@app.route( baseurl + "/do/config", methods = [ "POST" ] )
def do_config_new_user():
    """
        Save the configuration of the new user to the database.
    """
    email = session[ "signin_user_validation_email" ]
    uuid = session[ "signin_user_validation_uuid" ]
    username = request.form.get( "username" )
    password = request.form.get( "password" )
    
    session[ "username" ] = username
    
    ############################################################################
    
    q = config.db.query( "SELECT count(*) FROM signin_requests WHERE uuid = %s AND email = %s", ( uuid, email, ) )
    r = q.fetchone()
    r = r[ 0 ]
    
    if r == 0:
        return jsonify( {
            "error": True,
            "message": "no signin request"
        } )
    
    q = config.db.query( "SELECT * FROM users WHERE username = %s", ( username, ) )
    user = q.fetchone()
    
    if user == None:
        return jsonify( {
            "error": True,
            "message": "no user"
        } )
    
    elif not password.startswith( "pbkdf2" ):
        return jsonify( {
            "error": True,
            "message": "password not in the correct format"
        } )
    
    elif not pbkdf2( email, user[ "email" ] ).verify():
        return jsonify( {
            "error": True,
            "message": "email not corresponding to the request form"
        } )
    
    elif user.get( "password", None ) != None:
        return jsonify( {
            "error": True,
            "message": "password already set"
        } )
    
    ############################################################################
    
    password = pbkdf2( password, random_data( 65 ), config.PASSWORD_NB_ITERATIONS ).hash()
    
    q = config.db.query( "UPDATE users SET password = %s WHERE username = %s", ( password, username, ) )
    config.db.commit()
    
    session[ "username" ] = username
    
    ############################################################################
    
    return jsonify( {
        "error": False
    } )

@app.route( baseurl + "/config/donor/<h>" )
def config_new_user_donor( h ):
    """
        Serve the configuration page for a new donor.
    """
    session.clear()
    
    sql = "SELECT id, username, email FROM users WHERE type = 2 AND password IS NULL"
    for r in config.db.query_fetchall( sql ):
        if h == hashlib.sha512( r[ "email" ] ).hexdigest():
            user = r
            break
    
    else:
        return redirect( url_for( "home" ) )
    
    session[ "email_hash" ] = h
    session[ "user_id" ] = user[ "id" ]
    
    return render_template( 
        "users/config.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        envtype = envtype,
        next_step = "do_config_new_donor",
        hash = h
    )

@app.route( baseurl + "/do/config/donor", methods = [ "POST" ] )
def do_config_new_donor():
    """
        Save the donor configuration to the database.
    """
    username = request.form.get( "username" )
    password = request.form.get( "password" )
    password = pbkdf2( password, random_data( 100 ), config.PASSWORD_NB_ITERATIONS ).hash()
    h = request.form.get( "hash" )
    
    sql = "SELECT id FROM users WHERE username = %s"
    user_id = config.db.query_fetchone( sql, ( username, ) )[ "id" ]
    
    session[ "username" ] = username
    
    if session[ "email_hash" ] == h and session[ "user_id" ] == user_id:
        q = config.db.query( "UPDATE users SET password = %s WHERE username = %s", ( password, username, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    else:
        return jsonify( {
            "error": True,
            "message": "Invalid parameters"
        } )

@app.route( baseurl + "/totp_help" )
def totp_help():
    """
        Serve the help page for the TOTP.
    """
    return render_template( 
        "totp_help.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        envtype = envtype
    )

################################################################################
#    QR Code generation

def renew_secret():
    """
        Request a new TOTP secret.
    """
    secret = pyotp.random_base32( 40 )
    session[ "secret" ] = secret
    
    return secret

def get_secret():
    """
        Retrieve the current secret.
    """
    secret = session.get( "secret", None )
    if secret == None:
        secret = renew_secret()
    
    return secret

@app.route( baseurl + "/set_secret" )
def set_secret():
    """
        Set the new secret value for the TOTP in the database.
    """
    config.db.query( "UPDATE users SET totp = %s WHERE username = %s", ( session[ "secret" ], session[ "username" ], ) )
    config.db.commit()
    
    return jsonify( {
        "error": False
    } )

@app.route( baseurl + "/secret" )
def request_secret():
    """
        Serve the current secret as JSON.
    """
    get_secret()
    
    return jsonify( {
        "error": False,
        "secret": session[ "secret" ]
    } )

@app.route( baseurl + "/new_secret" )
@login_required
def request_renew_secret():
    """
        Serve current secret.
    """
    renew_secret()
    
    return jsonify( {
        "error": False,
        "secret": session[ "secret" ]
    } )

@app.route( baseurl + "/user/config/totp_qrcode.png" )
def user_totp_qrcode():
    """
        Generate the TOTP PNG QRcode image ready to scan.
    """
    if "username" in session:
        qrcode_value = "otpauth://totp/ICNML%20{}?secret={}".format( session[ "username" ], get_secret() )
    else:
        qrcode_value = "otpauth://totp/ICNML?secret={}".format( get_secret() )
    
    img = qrcode.make( qrcode_value )
    
    temp = StringIO()
    img.save( temp, format = "png" )
    temp.seek( 0 )
    
    return send_file( temp, mimetype = "image/png" )
    
@app.route( baseurl + "/user/config/totp" )
@login_required
def user_totp_config():
    """
        Serve the TOTP configuration page.
    """
    return render_template( 
        "users/totp.html",
        baseurl = baseurl,
        secret = get_secret(),
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        envtype = envtype,
        account_type = session.get( "account_type", None )
    )

################################################################################
#    Data decryption

encryption_prefix = "icnml$"

def do_decrypt( data ):
    """
        Try to decrypt the data stored server-side.
        This encryption is done with a key derived from the password of the user,
        only stored in RAM while the user is connected.
    """
    try:
        data = AESCipher( session[ "password" ] ).decrypt( data )
        
        if data.startswith( encryption_prefix ):
            return data[ len( encryption_prefix ): ]
        else:
            return "-"
    
    except:
        return "-"

def do_encrypt( data ):
    """
        Encryption of any data passed in argument with a key derived from the user password.
        This key is only stored in RAM (in redis) while the user is connected.

        The sensitive shall be encrypted on the client side first, hence never
        leaving the computer of the user.
    """
    return AESCipher( session[ "password" ] ).encrypt( encryption_prefix + data )

################################################################################
#    File upload

@app.route( baseurl + "/upload", methods = [ "GET", "POST" ] )
@login_required
def upload_file():
    """
        Main function dealing with the upload of files (tenprint, latent and consent forms).
        This function accept traditionals images and NIST files for the fingerprint data,
        and PDFs for the consent forms.
    """
    upload_type = request.form.get( "upload_type", None )
    
    file_extension = request.form.get( "extension", None )
    if isinstance( file_extension, str ):
        file_extension = file_extension.lower()
    
    if upload_type == None:
        return jsonify( {
            "error": True,
            "msg": "Must specify a file type to upload a file"
        } )
    
    if request.method == "POST":
        if "file" not in request.files:
            return jsonify( {
                "error": True,
                "msg": "No file in the POST request"
            } )
        
        elif "upload_id" not in request.form:
            return jsonify( {
                "error": True,
                "msg": "No upload_id"
            } )
        
        else:
            try:
                upload_uuid = request.form.get( "upload_id" )
                sql = "SELECT id FROM submissions WHERE uuid = %s"
                r = config.db.query( sql, ( upload_uuid, ) )
                upload_id = r.fetchone()[ "id" ]
                
            except:
                return jsonify( {
                    "error": True,
                    "msg": "upload not related to a submission form"
                } )
            
            file = request.files[ "file" ]
            file_name = do_encrypt( file.filename )
            _, file_ext = os.path.splitext( file.filename )
            file_uuid = str( uuid4() )
            
            fp = StringIO()
            
            file.save( fp )
            file_size = fp.tell()
            
            fp.seek( 0 )
            
            if file_extension in config.NIST_file_extensions:
                file_data = fp.getvalue()
                file_data = base64.b64encode( file_data )
                
                try:
                    n = NISTf_auto( fp )
                except:
                    return jsonify( {
                        "error": True,
                        "msg": "Error while loading the NIST file"
                    } )
                
                # Save the NIST file in the DB
                sql = sql_insert_generate( "files", [ "folder", "creator", "filename", "type", "format", "size", "uuid", "data" ] )
                data = ( upload_id, session[ "user_id" ], file_name, 5, "NIST", file_size, file_uuid, file_data, )
                config.db.query( sql, data )
                
                # Segmentation of the NIST file
                fpc_in_file = []
                for fpc in config.all_fpc:
                    try:
                        try:
                            img = n.get_print( fpc = fpc )
                        except:
                            img = n.get_palmar( fpc = fpc )
                        
                        buff = StringIO()
                        img.save( buff, format = "TIFF" )
                        buff.seek( 0 )
                        img_data = buff.getvalue()
                        img_data = base64.b64encode( img_data )
                        
                        sql = sql_insert_generate( "files_segments", [ "tenprint", "uuid", "pc", "data" ] )
                        data = ( file_uuid, str( uuid4() ), fpc, img_data, )
                        config.db.query( sql, data )
                        
                        fpc_in_file.append( fpc )
                    
                    except:
                        pass
                
                config.db.commit()
                
                return jsonify( {
                    "error": False,
                    "fpc": fpc_in_file
                } )
                    
            else:
                if upload_type in [ "latent_target", "latent_incidental", "tenprint_card_front", "tenprint_card_back" ]:
                    img = Image.open( fp )
                    img_format = img.format
                    width, height = img.size
                    
                    try:
                        res = int( img.info[ "dpi" ][ 0 ] )
                    except:
                        return jsonify( {
                            "error": True,
                            "msg": "No resolution found in the image. Upload not possible at the moment."
                        } )
                    
                    try:
                        img = rotate_image_upon_exif( img )
                    except:
                        pass
                    
                    buff = StringIO()
                    img.save( buff, format = img_format )
                    buff.seek( 0 )
                    file_data = buff.getvalue()
                    
                    if upload_type in [ "tenprint_card_front", "tenprint_card_back" ]:
                        create_thumbnail( file_uuid, img )
                
                else:
                    file_data = fp.getvalue()
                
                file_data_r = file_data
                file_data = base64.b64encode( file_data )
                
                sql = "SELECT id FROM files_type WHERE name = %s"
                upload_type_id = config.db.query( sql, ( upload_type, ) ).fetchone()[ 0 ]
                
                ####################################################################
                
                if upload_type == "consent_form":
                    sql = "SELECT email_aes FROM submissions WHERE uuid = %s"
                    email = config.db.query_fetchone( sql, ( upload_uuid, ) )[ "email_aes" ]
                    email = do_decrypt( email )
                    
                    sql = "SELECT username, email FROM users WHERE type = 2 ORDER BY id DESC"
                    for username_db, email_db in config.db.query_fetchall( sql ):
                        if pbkdf2( email, email_db ).verify():
                            username = username_db
                            url_hash = hashlib.sha512( email_db ).hexdigest()
                            break
                    
                    else:
                        return jsonify( {
                            "error": True,
                            "message": "user not found"
                        } )
                    
                    # Email for the donor
                    email_content = render_jinja_html( 
                        "templates/email", "donor.html",
                        username = username,
                        url = "https://icnml.unil.ch" + url_for( "config_new_user_donor", h = url_hash )
                    )
                    
                    msg = MIMEMultipart()
                    
                    msg[ "Subject" ] = "ICNML - You have been added as donor"
                    msg[ "From" ] = config.sender
                    msg[ "To" ] = email
                    
                    msg.attach( MIMEText( email_content, "html" ) )
                    
                    part = MIMEApplication( file_data_r, Name = "consent_form.pdf" )
                    part[ "Content-Disposition" ] = "attachment; filename=consent_form.pdf"
                    msg.attach( part )
                     
                    try:
                        s = smtplib.SMTP( config.smtpserver )
                        s.sendmail( config.sender, [ email ], msg.as_string() )
                        s.quit()
                    
                    except:
                        return jsonify( {
                            "error": True,
                            "message": "Can not send the email to the user"
                        } )
                    
                    else:
                        # Consent form save
                        file_data = base64.b64encode( file_data )
                        file_data = gpg.encrypt( file_data, *config.gpg_key )
                        file_data = str( file_data )
                        file_data = base64.b64encode( file_data )
                        
                        sql = sql_insert_generate( "cf", [ "uuid", "data", "email" ] )
                        data = ( file_uuid, file_data, pbkdf2( email, iterations = 100000 ).hash(), )
                        config.db.query( sql , data )
                        
                        sql = "UPDATE submissions SET consent_form = true WHERE uuid = %s"
                        config.db.query( sql, ( upload_uuid, ) )
                        
                        config.db.commit()
                    
                else:
                    sql = sql_insert_generate( "files", [
                        "folder", "creator",
                        "filename", "type",
                        "format", "size", "width", "height", "resolution",
                        "uuid", "data"
                    ] )
                    data = (
                        upload_id, session[ "user_id" ],
                        file_name, upload_type_id,
                        img_format, file_size, width, height, res,
                        file_uuid, file_data,
                    )
                    config.db.query( sql, data )
                    config.db.commit()
                
                return jsonify( {
                    "error": False,
                    "uuid": file_uuid
                } )
    
    else:
        return abort( 403 )

################################################################################
#    Submission of a new donor

@app.route( baseurl + "/submission/new" )
@submitter_required
def submission_new():
    """
        Serve the page to start a new submission (new donor).
    """
    return render_template( 
        "submission/new.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + "/submission/do_new", methods = [ "POST" ] )
@submitter_required
def submission_do_new():
    """
        Check the new donor data, and store the new submission process in the database.
    """
    email = request.form.get( "email", False )
    email = email.lower()
    
    if email:
        # Check for duplicate base upon the email data
        sql = "SELECT id, email_hash FROM submissions WHERE submitter_id = %s"
        r = config.db.query( sql, ( session[ "user_id" ], ) )
        for case in r.fetchall():
            if pbkdf2( email, case[ "email_hash" ] ).verify():
                return jsonify( {
                    "error": True,
                    "msg": "Email already used"
                } )
                
                break
        
        else:
            # Insert the new donor
            id = str( uuid4() )
            
            email_aes = do_encrypt( email )
            email_hash = pbkdf2( email, iterations = config.EMAIL_NB_ITERATIONS ).hash()
            
            upload_nickname = request.form.get( "upload_nickname", None )
            upload_nickname = do_encrypt( upload_nickname )
            submitter_id = session[ "user_id" ]
            
            status = "pending"
            
            sql = sql_insert_generate( "submissions", [ "uuid", "email_aes", "email_hash", "nickname", "status", "submitter_id" ] )
            data = ( id, email_aes, email_hash, upload_nickname, status, submitter_id, )
            config.db.query( sql, data )
            config.db.commit()
            
            userid = config.db.query_fetchone( "SELECT nextval( 'username_donor_seq' ) as id" )[ "id" ]
            username = "donor_{}".format( userid )
            sql = sql_insert_generate( "users", [ "username", "email", "type" ] )
            data = ( username, email_hash, 2 )
            config.db.query( sql, data )
            config.db.commit()
            
            return jsonify( {
                "error": False,
                "id": id
            } )
        
    else:
        return jsonify( {
            "error": True,
            "msg": "Email not provided"
        } )

@app.route( baseurl + "/submission/<id>/add_files" )
@submitter_required
def submission_upload_tplp( id ):
    """
        Serve the page to upload tenprint and latent images files.
        This page is not accessible if a consent form is not available in the
        database for this particular donor.
    """
    try:
        sql = """
            SELECT email_aes as email, nickname, created_time, consent_form
            FROM submissions
            WHERE submitter_id = %s AND uuid = %s
        """
        r = config.db.query( sql, ( session[ "user_id" ], id ) )
        user = r.fetchone()
        
        if user[ "consent_form" ]:
            for key in [ "email", "nickname" ]:
                user[ key ] = do_decrypt( user[ key ] )
            
            return render_template( 
                "submission/add_files.html",
                baseurl = baseurl,
                js = config.cdnjs,
                css = config.cdncss,
                session_timeout = config.session_timeout,
                upload_id = id,
                session_security_key = session.get( "session_security_key" ),
                envtype = envtype,
                nist_file_extensions = json.dumps( config.NIST_file_extensions ),
                **user
            )
        else:
            return redirect( url_for( "submission_consent_form", id = id ) )
        
    except:
        return jsonify( {
            "error": True,
            "msg": "Case not found"
        } )

@app.route( baseurl + "/submission/<id>/consent_form" )
@submitter_required
def submission_consent_form( id ):
    """
        Serve the page to upload the consent form for the user.
    """
    sql = """
        SELECT email_aes as email, nickname, created_time
        FROM submissions
        WHERE submitter_id = %s AND uuid = %s
    """
    r = config.db.query( sql, ( session[ "user_id" ], id ) )
    user = r.fetchone()
    
    if user != None:
        for key in [ "email", "nickname" ]:
            user[ key ] = do_decrypt( user[ key ] )
        
        return render_template( 
            "submission/consent_form.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            upload_id = id,
            session_security_key = session.get( "session_security_key" ),
            envtype = envtype,
            **user
        )
    
    else:
        return abort( 404 )

@app.route( baseurl + "/submission/<id>/set/nickname", methods = [ "POST" ] )
@submitter_required
def submission_update_nickname( id ):
    """
        Change the nickname of the donor in the database.

        THIS INFORMATION SHALL BE ENCRYPTED ON THE CLIENT SIDE FIRST WITH A UNIQUE
        ENCRYPTION KEY NOT TRANSMETTED TO THE SERVER!
    """
    nickname = request.form.get( "nickname", None )
    
    if nickname != None and len( nickname ) != 0:
        try:
            nickname = do_encrypt( nickname )
            
            sql = "UPDATE submissions SET nickname = %s WHERE uuid = %s"
            config.db.query( sql, ( nickname, id, ) )
            config.db.commit()
            
            return jsonify( {
                "error": False
            } )
        
        except:
            return jsonify( {
                "error": True,
                "message": "DB error"
            } )
    
    else:
        return jsonify( {
            "error": True,
            "message": "No new nickname in the POST request"
        } )

@app.route( baseurl + "/submission/list" )
@submitter_required
def submission_list():
    """
        Get the list of all submissions folder for the currently logged submitter.
    """
    sql = "SELECT * FROM submissions WHERE submitter_id = %s ORDER BY created_time DESC"
    r = config.db.query( sql, ( session[ "user_id" ], ) )
    q = r.fetchall()
    
    donors = []
    for donor in q:
        donors.append( {
            "id": donor.get( "id", None ),
            "email": do_decrypt( donor.get( "email_aes", None ) ),
            "nickname": do_decrypt( donor.get( "nickname", None ) ),
            "uuid": donor.get( "uuid", None )
        } )
    
    return render_template( 
        "submission/list.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        donors = donors,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + "/submission/<id>/latent/list" )
@app.route( baseurl + "/submission/<id>/latent/list/<ltype>" )
@submitter_required
def submission_latent_list( id, ltype = "all" ):
    """
        Get the list of latent for a particular submission folder.
    """
    if ltype in [ "target", "incidental", "all" ]:
        sql = "SELECT id, nickname FROM submissions WHERE uuid = %s AND submitter_id = %s"
        r = config.db.query( sql, ( id, session[ "user_id" ], ) )
        case_id, nickname = r.fetchone()
        nickname = do_decrypt( nickname )
        
        sql = """
            SELECT files.uuid, files.filename, files.size, files.creation_time
            FROM files
            LEFT JOIN files_type ON files.type = files_type.id
            WHERE folder = %s AND
        """
        if ltype == "target":
            sql += " files_type.name = 'latent_target'"
        elif ltype == "incidental":
            sql += " files_type.name = 'latent_incidental'"
        elif ltype == "all":
            sql += " ( files_type.name = 'latent_target' OR files_type.name = 'latent_incidental' )"
        
        sql += " ORDER BY files.id DESC"
        r = config.db.query( sql, ( case_id, ) )
        files = r.fetchall()
        
        for i, v in enumerate( files ):
            v[ "filename" ] = do_decrypt( v[ "filename" ] )
            v[ "size" ] = round( ( float( v[ "size" ] ) / ( 1024 * 1024 ) ) * 100 ) / 100
        
        return render_template( 
            "submission/latent_list.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            submission_id = id,
            latent_type = ltype,
            files = files,
            nickname = nickname,
            session_security_key = session.get( "session_security_key" ),
            envtype = envtype
        )
    
    else:
        return abort( 403 )

@app.route( baseurl + "/submission/<id>/latent/<lid>" )
@submitter_required
def submission_latent( id, lid ):
    """
        Serve the page to edit a particular latent image.
    """
    sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
    r = config.db.query( sql, ( id, ) )
    submission_id, nickname = r.fetchone()
    nickname = do_decrypt( nickname )
    
    sql = """
        SELECT
            files.uuid, files.filename, files.note,
            files.format, files.resolution, files.width, files.height, files.size,
            files.creation_time, files.type,
            files_type.name as file_type
        
        FROM files
        LEFT JOIN files_type ON files.type = files_type.id
        WHERE
            folder = %s AND
            files.uuid = %s
    """
    
    r = config.db.query( sql, ( submission_id, lid, ) )
    file = r.fetchone()
    file[ "size" ] = round( 100 * float( file[ "size" ] ) / ( 1024 * 1024 ) ) / 100
    file[ "filename" ] = do_decrypt( file[ "filename" ] )
    file[ "note" ] = do_decrypt( file[ "note" ] )
    file[ "file_type" ] = file[ "file_type" ].replace( "latent_", "" )
    
    return render_template( 
        "submission/latent.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        submission_id = id,
        nickname = nickname,
        file = file,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + "/submission/<id>/latent/<lid>/pfsp" )
@submitter_required
def submission_latent_pfsp( id, lid ):
    """
        Serve the page to set the PFSP information (location on the finger
        or the palm print) for the latent.
    """
    sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
    r = config.db.query( sql, ( id, ) )
    submission_id, nickname = r.fetchone()
    nickname = do_decrypt( nickname )
    
    sql = """
        SELECT
            files.uuid, files.filename, files.note,
            files.format, files.resolution, files.width, files.height, files.size,
            files.creation_time, files.type,
            files_type.name as file_type
        
        FROM files
        LEFT JOIN files_type ON files.type = files_type.id
        WHERE
            folder = %s AND
            files.uuid = %s
    """
    
    r = config.db.query( sql, ( submission_id, lid, ) )
    file = r.fetchone()
    file[ "size" ] = round( 100 * float( file[ "size" ] ) / ( 1024 * 1024 ) ) / 100
    file[ "filename" ] = do_decrypt( file[ "filename" ] )
    file[ "note" ] = do_decrypt( file[ "note" ] )
    file[ "file_type" ] = file[ "file_type" ].replace( "latent_", "" )
    
    sql = "SELECT pfsp FROM latent_info WHERE uuid = %s"
    try:
        current_pfsp = config.db.query( sql, ( lid, ) ).fetchone()[ 0 ]
    except:
        current_pfsp = None
    
    for z in pfsp.zones:
        if z[ "desc" ] == current_pfsp:
            current_pfsp = ",".join( z[ "sel" ] )
    
    return render_template( 
        "submission/latent_pfsp.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        submission_id = id,
        nickname = nickname,
        file = file,
        pfsp_zones = pfsp.zones,
        current_pfsp = current_pfsp,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype