module.py

    } )

@app.route( baseurl + "/do/validate_signin_2", methods = [ "POST" ] )
@admin_required
def do_validate_signin_2():
    """
        Verification of the signature of the new user data by the admin.
    """
    challenge = session.get( "validation_user_challenge" )
    assertion_response = request.form
    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:
        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_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"
        config.db.query( sql, ( request_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        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 my_render_template( 
            "users/config.html",
            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 my_render_template( 
        "users/config.html",
        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:
        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 my_render_template( "totp_help.html" )

################################################################################
#    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 my_render_template( 
        "users/totp.html",
        secret = get_secret()
    )

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

def do_decrypt_user_session( data ):
    return do_decrypt( data, session[ "password" ] )

def do_encrypt_user_session( data ):
    return do_encrypt( data, session[ "password" ] )

################################################################################
#    DEK encryption/decryption

def get_dek_from_submissionid( submission_id ):
    """
        Get the Data Encryption Key related to a submission id folder.
    """
    sql = """
        SELECT donor_dek.dek
        FROM donor_dek
        LEFT JOIN users ON users.username = donor_dek.donor_name
        LEFT JOIN submissions ON submissions.donor_id = users.id
        WHERE submissions.uuid = %s
        LIMIT 1
    """
    try:
        return config.db.query_fetchone( sql, ( submission_id, ) )[ "dek" ]
    
    except:
        return None

def do_decrypt_dek( data, submission_id ):
    """
        AES encrypt the data with the Data Encryption Key related to the donor.
    """
    dek = get_dek_from_submissionid( submission_id )
    return do_decrypt( data, dek )

def do_encrypt_dek( data, submission_id ):
    """
        AES decrypt the data with the Data Encryption Key related to the donor.
    """
    dek = get_dek_from_submissionid( submission_id )
    return do_encrypt( data, dek )

################################################################################
#    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 "submission_id" not in request.form:
            return jsonify( {
                "error": True,
                "msg": "No submission_id"
            } )
        
        else:
            try:
                submission_uuid = request.form.get( "submission_id" )
                sql = "SELECT id FROM submissions WHERE uuid = %s"
                r = config.db.query( sql, ( submission_uuid, ) )
                submission_id = r.fetchone()[ "id" ]
                
            except:
                return jsonify( {
                    "error": True,
                    "msg": "upload not related to a submission form"
                } )
            
            uploaded_file = request.files[ "file" ]
            file_name = do_encrypt_user_session( uploaded_file.filename )
            file_uuid = str( uuid4() )
            
            fp = StringIO()
            
            uploaded_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 )
                file_data = do_encrypt_dek( file_data, submission_uuid )
                
                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 = ( submission_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 )
                        img_data = do_encrypt_dek( img_data, submission_uuid )
                        
                        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, submission_uuid )
                
                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, ( submission_uuid, ) )[ "email_aes" ]
                    email = do_decrypt_user_session( 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, ( submission_uuid, ) )
                        
                        config.db.commit()
                    
                else:
                    file_data = do_encrypt_dek( file_data, submission_uuid )
                    
                    sql = sql_insert_generate( "files", [
                        "folder", "creator",
                        "filename", "type",
                        "format", "size", "width", "height", "resolution",
                        "uuid", "data"
                    ] )
                    data = ( 
                        submission_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" )
@submission_has_access
def submission_new():
    """
        Serve the page to start a new submission (new donor).
    """
    return my_render_template( "submission/new.html" )

@app.route( baseurl + "/submission/do_new", methods = [ "POST" ] )
@submission_has_access
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
            donor_uuid = str( uuid4() )
            
            email_aes = do_encrypt_user_session( email )
            email_hash = pbkdf2( email, iterations = config.EMAIL_NB_ITERATIONS ).hash()
            
            upload_nickname = request.form.get( "upload_nickname", None )
            upload_nickname = do_encrypt_user_session( upload_nickname )
            submitter_id = session[ "user_id" ]
            
            status = "pending"
            
            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" ], "id" )
            data = ( username, email_hash, 2 )
            donor_user_id = config.db.query_fetchone( sql, data )[ "id" ]
            
            dek_salt = random_data( 100 )
            dek = pbkdf2( "{}:{}".format( username, email, ), dek_salt, iterations = config.DEK_NB_ITERATIONS, hash_name = "sha512" ).hash( True )
            dek_check = {
                "value": "ok",
                "time": int( time.time() * 1000 ),
                "random": random_data( 10 )
            }
            dek_check = json.dumps( dek_check )
            dek_check = do_encrypt( dek_check, dek )
            
            sql = sql_insert_generate( "donor_dek", [ "donor_name", "salt", "dek", "dek_check", "iterations", "algo", "hash" ], "id" )
            data = ( username, dek_salt, dek, dek_check, config.DEK_NB_ITERATIONS, "pbkdf2", "sha512", )
            config.db.query_fetchone( sql, data )
            
            sql = sql_insert_generate( "submissions", [ "uuid", "email_aes", "email_hash", "nickname", "donor_id", "status", "submitter_id" ] )
            data = ( donor_uuid, email_aes, email_hash, upload_nickname, donor_user_id, status, submitter_id, )
            config.db.query( sql, data )
            
            config.db.commit()
            
            return jsonify( {
                "error": False,
                "id": donor_uuid
            } )
        
    else:
        return jsonify( {
            "error": True,
            "msg": "Email not provided"
        } )

@app.route( baseurl + "/submission/<submission_id>/add_files" )
@submission_has_access
def submission_upload_tplp( submission_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" ], submission_id ) )
        user = r.fetchone()
        
        if user[ "consent_form" ]:
            for key in [ "email", "nickname" ]:
                user[ key ] = do_decrypt_user_session( user[ key ] )
            
            return my_render_template( 
                "submission/add_files.html",
                submission_id = submission_id,
                **user
            )
        else:
            return redirect( url_for( "submission_consent_form", submission_id = submission_id ) )
        
    except:
        return jsonify( {
            "error": True,
            "msg": "Case not found"
        } )

@app.route( baseurl + "/submission/<submission_id>/consent_form" )
@submission_has_access
def submission_consent_form( submission_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" ], submission_id ) )
    user = r.fetchone()
    
    if user != None:
        for key in [ "email", "nickname" ]:
            user[ key ] = do_decrypt_user_session( user[ key ] )
        
        return my_render_template( 
            "submission/consent_form.html",
            submission_id = submission_id,
            **user
        )
    
    else:
        return abort( 404 )

@app.route( baseurl + "/submission/<submission_id>/set/nickname", methods = [ "POST" ] )
@submission_has_access
def submission_update_nickname( submission_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_user_session( nickname )
            
            sql = "UPDATE submissions SET nickname = %s WHERE uuid = %s"
            config.db.query( sql, ( nickname, submission_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" )
@submission_has_access
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_user_session( donor.get( "email_aes", None ) ),
            "nickname": do_decrypt_user_session( donor.get( "nickname", None ) ),
            "uuid": donor.get( "uuid", None )
        } )
    
    return my_render_template( 
        "submission/list.html",
        donors = donors
    )

@app.route( baseurl + "/submission/<submission_id>/latent/list" )
@app.route( baseurl + "/submission/<submission_id>/latent/list/<latent_type>" )
@submission_has_access
def submission_latent_list( submission_id, latent_type = "all" ):
    """
        Get the list of latent for a particular submission folder.
    """
    if latent_type in [ "target", "incidental", "all" ]:
        sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
        r = config.db.query( sql, ( submission_id, ) )
        case_id, nickname = r.fetchone()
        nickname = do_decrypt_user_session( 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 latent_type == "target":
            sql += " files_type.name = 'latent_target'"
        elif latent_type == "incidental":
            sql += " files_type.name = 'latent_incidental'"
        elif latent_type == "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 _, v in enumerate( files ):
            v[ "filename" ] = do_decrypt_user_session( v[ "filename" ] )
            v[ "size" ] = round( ( float( v[ "size" ] ) / ( 1024 * 1024 ) ) * 100 ) / 100
        
        return my_render_template( 
            "submission/latent_list.html",
            submission_id = submission_id,
            latent_type = latent_type,
            files = files,
            nickname = nickname
        )
    
    else:
        return abort( 403 )

@app.route( baseurl + "/submission/<submission_id>/latent/<latent_id>" )
@submission_has_access
def submission_latent( submission_id, latent_id ):
    """
        Serve the page to edit a particular latent image.
    """
    sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
    r = config.db.query( sql, ( submission_id, ) )
    submission_folder_id, nickname = r.fetchone()
    nickname = do_decrypt_user_session( 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_folder_id, latent_id, ) )
    latent = r.fetchone()
    latent[ "size" ] = round( 100 * float( latent[ "size" ] ) / ( 1024 * 1024 ) ) / 100
    latent[ "filename" ] = do_decrypt_user_session( latent[ "filename" ] )
    latent[ "note" ] = do_decrypt_user_session( latent[ "note" ] )
    latent[ "file_type" ] = latent[ "file_type" ].replace( "latent_", "" )
    
    return my_render_template( 
        "submission/latent.html",
        submission_id = submission_id,
        nickname = nickname,
        file = latent
    )

@app.route( baseurl + "/submission/<submission_id>/latent/<latent_id>/pfsp" )
@submission_has_access
def submission_latent_pfsp( submission_id, latent_id ):
    """
        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, ( submission_id, ) )
    submission_folder_id, nickname = r.fetchone()
    nickname = do_decrypt_user_session( 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_folder_id, latent_id, ) )
    latent = r.fetchone()
    latent[ "size" ] = round( 100 * float( latent[ "size" ] ) / ( 1024 * 1024 ) ) / 100
    latent[ "filename" ] = do_decrypt_user_session( latent[ "filename" ] )
    latent[ "note" ] = do_decrypt_user_session( latent[ "note" ] )
    latent[ "file_type" ] = latent[ "file_type" ].replace( "latent_", "" )
    
    sql = "SELECT pfsp FROM latent_info WHERE uuid = %s"
    try:
        current_pfsp = config.db.query( sql, ( latent_id, ) ).fetchone()[ 0 ]
    except:
        current_pfsp = None
    
    for z in pfsp.zones:
        if z[ "desc" ] == current_pfsp:
            current_pfsp = ",".join( z[ "sel" ] )
    
    return my_render_template( 
        "submission/latent_pfsp.html",
        submission_id = submission_id,
        nickname = nickname,
        file = latent,
        pfsp_zones = pfsp.zones,
        current_pfsp = current_pfsp
    )

@app.route( baseurl + "/submission/<submission_id>/latent/<latent_id>/set/pfsp", methods = [ "POST" ] )
@submission_has_access
def submission_latent_pfsp_set( submission_id, latent_id ):
    """
        Save the PFSP information relative to a latent.
    """
    pfsp = request.form.get( "pfsp" )
    
    sql = "SELECT id FROM latent_info WHERE uuid = %s"
    q = config.db.query( sql, ( latent_id, ) ).fetchone()
    
    if q == None:
        sql = sql_insert_generate( "latent_info", [ "uuid", "pfsp" ] )
        config.db.query( sql, ( latent_id, pfsp, ) )
    
    else:
        sql = "UPDATE latent_info SET pfsp = %s WHERE uuid = %s"
        config.db.query( sql, ( pfsp, latent_id, ) )
    
    config.db.commit()
    
    return jsonify( {
        "error": False
    } )

@app.route( baseurl + "/submission/<submission_id>/latent/<latent_id>/delete" )
@submission_has_access