module.py

                
                except:
                    qrcode_checked = False
                
                # Email for the donor
                app.logger.info( "Sending the email to the donor" )
                email_content = utils.template.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:
                    with mySMTP() as s:
                        s.sendmail( config.sender, [ email ], msg.as_string() )
                    
                    app.logger.info( "Email sended" )
                
                except:
                    app.logger.error( "Can not send the email to the donor" )
                    return jsonify( {
                        "error": True,
                        "message": "Can not send the email to the user"
                    } )
                
                else:
                    # Consent form save
                    app.logger.info( "Saving the consent form to the database" )
                    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 )
                    
                    email_hash = utils.hash.pbkdf2( email, iterations = config.CF_NB_ITERATIONS ).hash()
                    
                    sql = utils.sql.sql_insert_generate( "cf", [ "uuid", "data", "email", "has_qrcode" ] )
                    data = ( file_uuid, file_data, email_hash, qrcode_checked, )
                    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:
                app.logger.info( "Save the file to the databse" )
                
                file_data = do_encrypt_dek( file_data, submission_uuid )
                
                sql = utils.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
            } )

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

@app.route( config.baseurl + "/submission/new" )
@submission_has_access
def submission_new():
    """
        Serve the page to start a new submission (new donor).
    """
    app.logger.info( "Serve the new donor form" )
    return my_render_template( "submission/new.html" )

@app.route( config.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.
    """
    app.logger.info( "Process the new donor form" )
    
    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"
        for case in config.db.query_fetchall( sql, ( session[ "user_id" ], ) ):
            if utils.hash.pbkdf2( email, case[ "email_hash" ] ).verify():
                app.logger.error( "Email already used for an other submission ({}) by this submitter".format( case[ "id" ] ) )
                return jsonify( {
                    "error": True,
                    "message": "Email already used for an other submission. Check the list of submissions to update the corresponding one."
                } )
                
                break
        
        else:
            app.logger.info( "Insertion of the donor to the databse" )
            # Insert the new donor
            donor_uuid = str( uuid4() )
            app.logger.debug( "Donor uuid: {}".format( donor_uuid ) )
            
            email_aes = do_encrypt_user_session( email )
            email_hash = utils.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 = utils.sql.sql_insert_generate( "users", [ "username", "email", "type" ], "id" )
            data = ( username, email_hash, 2 )
            donor_user_id = config.db.query_fetchone( sql, data )[ "id" ]
            
            app.logger.debug( "Username: {}".format( username ) )
            
            dek_salt, dek, dek_check = dek_generate( email = email, username = username )
            
            app.logger.debug( "DEK salt: {}...".format( dek_salt[ 0:10 ] ) )
            app.logger.debug( "DEK:      {}...".format( dek[ 0:10 ] ) )
            
            sql = utils.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 = utils.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:
        app.logger.error( "No email provided for the submission folder" )
        return jsonify( {
            "error": True,
            "message": "Email not provided"
        } )

@app.route( config.baseurl + "/submission/<submission_id>/add_files" )
@submission_has_access
def submission_upload_tplp( submission_id ):
    """
        Serve the page to upload tenprint and mark images files.
        This page is not accessible if a consent form is not available in the
        database for this particular donor.
    """
    app.logger.info( "Upload a new file in the submission {}".format( submission_id ) )
    
    try:
        dek_check( submission_id )
        
        sql = """
            SELECT email_aes as email, nickname, created_time, consent_form
            FROM submissions
            WHERE submitter_id = %s AND uuid = %s
        """
        user = config.db.query_fetchone( sql, ( session[ "user_id" ], submission_id ) )
        
        if user[ "consent_form" ]:
            app.logger.debug( "The donor has a consent form" )
            app.logger.info( "Serving the add new file page" )
            
            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:
            app.logger.debug( "The donor dont have a consent form in the database" )
            app.logger.info( "Serving the consent form upload page" )
            
            return redirect( url_for( "submission_consent_form", submission_id = submission_id ) )
        
    except:
        return jsonify( {
            "error": True,
            "message": "Case not found"
        } )

@app.route( config.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.
    """
    app.logger.info( "Serve the consent form upload page" )
    
    sql = """
        SELECT email_aes as email, nickname, created_time
        FROM submissions
        WHERE submitter_id = %s AND uuid = %s
    """
    user = config.db.query_fetchone( sql, ( session[ "user_id" ], submission_id ) )
    
    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:
        app.logger.error( "Submission not found" )
        return abort( 404 )

@app.route( config.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!
    """
    app.logger.info( "Save the donor nickname to the database" )
    
    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:
            app.logger.error( "Database error" )
            return jsonify( {
                "error": True,
                "message": "DB error"
            } )
    
    else:
        app.logger.error( "No nickname in the post request" )
        return jsonify( {
            "error": True,
            "message": "No new nickname in the POST request"
        } )

@app.route( config.baseurl + "/submission/list" )
@submission_has_access
def submission_list():
    """
        Get the list of all submissions folder for the currently logged submitter.
    """
    app.logger.info( "Get all submissions for '{}'".format( session[ "username" ] ) )
    
    sql = "SELECT * FROM submissions WHERE submitter_id = %s ORDER BY created_time DESC"
    q = config.db.query_fetchall( sql, ( session[ "user_id" ], ) )
    
    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 )
        } )
        app.logger.debug( "uuid: {}".format( donor[ "uuid" ] ) )
    
    app.logger.info( "{} submissions found".format( len( donors ) ) )
    
    return my_render_template( 
        "submission/list.html",
        donors = donors
    )

@app.route( config.baseurl + "/submission/<submission_id>/mark/list" )
@app.route( config.baseurl + "/submission/<submission_id>/mark/list/<mark_type>" )
@submission_has_access
def submission_mark_list( submission_id, mark_type = "all" ):
    """
        Get the list of mark for a particular submission folder.
    """
    app.logger.info( "Get the list of mark for the submission '{}'".format( submission_id ) )
    app.logger.debug( "mark_type: {}".format( mark_type ) )
    
    if mark_type in [ "target", "incidental", "all" ]:
        sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
        case_id, nickname = config.db.query_fetchone( sql, ( submission_id, ) )
        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 mark_type == "target":
            sql += " files_type.name = 'mark_target'"
        elif mark_type == "incidental":
            sql += " files_type.name = 'mark_incidental'"
        elif mark_type == "all":
            sql += " ( files_type.name = 'mark_target' OR files_type.name = 'mark_incidental' )"
        
        sql += " ORDER BY files.id DESC"
        files = config.db.query_fetchall( sql, ( case_id, ) )
        
        for _, v in enumerate( files ):
            v[ "filename" ] = do_decrypt_user_session( v[ "filename" ] )
            v[ "size" ] = round( ( float( v[ "size" ] ) / ( 1024 * 1024 ) ) * 100 ) / 100
        
        app.logger.debug( "{} marks for '{}'".format( len( files ), submission_id ) )
        
        return my_render_template( 
            "submission/mark_list.html",
            submission_id = submission_id,
            mark_type = mark_type,
            files = files,
            nickname = nickname
        )
    
    else:
        return abort( 403 )

@app.route( config.baseurl + "/submission/<submission_id>/mark/<mark_id>" )
@submission_has_access
def submission_mark( submission_id, mark_id ):
    """
        Serve the page to edit a particular mark image.
    """
    app.logger.info( "Serve the mark page edit" )
    app.logger.debug( "submission {}".format( submission_id ) )
    app.logger.debug( "mark {}".format( mark_id ) )
    
    sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
    submission_folder_id, nickname = config.db.query_fetchone( sql, ( submission_id, ) )
    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
    """
    
    mark = config.db.query_fetchone( sql, ( submission_folder_id, mark_id, ) )
    mark[ "size" ] = round( 100 * float( mark[ "size" ] ) / ( 1024 * 1024 ) ) / 100
    mark[ "filename" ] = do_decrypt_user_session( mark[ "filename" ] )
    mark[ "note" ] = do_decrypt_user_session( mark[ "note" ] )
    mark[ "file_type" ] = mark[ "file_type" ].replace( "mark_", "" )
    
    return my_render_template( 
        "submission/mark.html",
        submission_id = submission_id,
        nickname = nickname,
        file = mark
    )

@app.route( config.baseurl + "/submission/<submission_id>/mark/<mark_id>/pfsp" )
@submission_has_access
def submission_mark_pfsp( submission_id, mark_id ):
    """
        Serve the page to set the PFSP information (location on the finger
        or the palm print) for the mark.
    """
    app.logger.info( "Serve the PFSP edit page" )
    app.logger.debug( "submission {}".format( submission_id ) )
    app.logger.debug( "mark {}".format( mark_id ) )
    
    sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
    submission_folder_id, nickname = config.db.query_fetchone( sql, ( submission_id, ) )
    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
    """
    
    mark = config.db.query_fetchone( sql, ( submission_folder_id, mark_id, ) )
    mark[ "size" ] = round( 100 * float( mark[ "size" ] ) / ( 1024 * 1024 ) ) / 100
    mark[ "filename" ] = do_decrypt_user_session( mark[ "filename" ] )
    mark[ "note" ] = do_decrypt_user_session( mark[ "note" ] )
    mark[ "file_type" ] = mark[ "file_type" ].replace( "mark_", "" )
    
    app.logger.debug( "file size: {}Mo".format( mark[ "size" ] ) )
    
    sql = "SELECT pfsp FROM mark_info WHERE uuid = %s"
    try:
        current_pfsp = config.db.query_fetchone( sql, ( mark_id, ) )[ "pfsp" ]
    except:
        current_pfsp = None
    
    app.logger.debug( "Current PFSP: {}".format( current_pfsp ) )
    
    for z in pfsp.zones:
        if z[ "desc" ] == current_pfsp:
            current_pfsp = ",".join( z[ "sel" ] )
    
    return my_render_template( 
        "submission/mark_pfsp.html",
        submission_id = submission_id,
        nickname = nickname,
        file = mark,
        pfsp_zones = pfsp.zones,
        current_pfsp = current_pfsp
    )

@app.route( config.baseurl + "/submission/<submission_id>/mark/<mark_id>/set/pfsp", methods = [ "POST" ] )
@submission_has_access
def submission_mark_pfsp_set( submission_id, mark_id ):
    """
        Save the PFSP information relative to a mark.
    """
    app.logger.info( "Save the PFSP for submission '{}' mark '{}'".format( submission_id, mark_id ) )
    
    try:
        pfsp = request.form.get( "pfsp" )
        
        sql = "SELECT id FROM mark_info WHERE uuid = %s"
        q = config.db.query_fetchone( sql, ( mark_id, ) )
        
        if q == None:
            sql = utils.sql.sql_insert_generate( "mark_info", [ "uuid", "pfsp" ] )
            config.db.query( sql, ( mark_id, pfsp, ) )
        
        else:
            sql = "UPDATE mark_info SET pfsp = %s WHERE uuid = %s"
            config.db.query( sql, ( pfsp, mark_id, ) )
        
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

@app.route( config.baseurl + "/submission/<submission_id>/mark/<mark_id>/delete" )
@submission_has_access
def submission_mark_delete( submission_id, mark_id ):
    """
        Delete a mark from the database.
    """
    app.logger.info( "Delete mark '{}' from submission '{}'".format( mark_id, submission_id ) )
    
    sql = "SELECT id FROM submissions WHERE submitter_id = %s AND uuid = %s"
    q = config.db.query( sql, ( session[ "user_id" ], submission_id, ) )
    
    if q != None:
        sql = "DELETE FROM files WHERE creator = %s AND uuid = %s"
        config.db.query( sql, ( session[ "user_id" ], mark_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    else:
        return jsonify( {
            "error": True
        } )

################################################################################
#    Submission deletion

@app.route( config.baseurl + "/submission/<submission_id>/delete" )
@submission_has_access
def submission_delete( submission_id ):
    """
        Delete the empty submission. A submission can not be deleted after the upload
        of the consent form and the creation of the donor user.
    """
    app.logger.info( "Delete the submission '{}' for user '{}'".format( submission_id, session[ "username" ] ) )
    
    sql = "SELECT consent_form FROM submissions WHERE submitter_id = %s AND uuid = %s"
    cf = config.db.query_fetchone( sql, ( session[ "user_id" ], submission_id, ) )[ "consent_form" ]
    
    if not cf:
        sql = "DELETE FROM submissions WHERE submitter_id = %s AND uuid = %s"
        config.db.query( sql, ( session[ "user_id" ], submission_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    else:
        app.logger.error( "Can  not delete a submission with consent form" )
        return jsonify( {
            "error": True,
            "message": "Can not delete if a consent form is already uploaded"
        } )

################################################################################
#    Admin submission

@app.route( config.baseurl + "/admin/submission/list" )
@admin_required
def admin_submission_list():
    """
        Get the list of all submissions folder.
    """
    app.logger.info( "Get all submissions" )
    
    sql = """
        SELECT submissions.id, submissions.uuid, users.username
        FROM submissions
        LEFT JOIN users ON submissions.donor_id = users.id
        ORDER BY created_time DESC
    """
    q = config.db.query_fetchall( sql )
    
    donors = []
    for donor in q:
        donors.append( donor )
        app.logger.debug( "uuid: {}".format( donor[ "uuid" ] ) )
    
    app.logger.info( "{} submissions found".format( len( donors ) ) )
    
    return my_render_template( 
        "admin/list.html",
        donors = donors
    )

@app.route( config.baseurl + "/admin/<submission_id>/tenprint/list" )
@admin_required
def admin_tenprint_list( submission_id = "all" ):
    """
        Get the list of all tenprints.
    """
    app.logger.info( "Get all tenprints cards" )
    
    sql = """
        SELECT files.id, files.uuid, files.folder, users.username, submissions.uuid as submission_uuid
        FROM files
        LEFT JOIN submissions ON files.folder = submissions.id
        LEFT JOIN users ON submissions.email_hash = users.email 
        WHERE ( files.type = 1 OR files.type = 2 OR files.type = 5 )
    """
    data = ()
    
    if submission_id != "all":
        sql += " AND submissions.uuid = %s"
        data = ( submission_id, )
    
    sql += """
        ORDER BY users.id ASC, files.type ASC
    """
    
    tenprint_cards = config.db.query_fetchall( sql, data )
    
    app.logger.info( "{} tenprints cards found".format( len( tenprint_cards ) ) )
    
    return my_render_template( 
        "admin/tenprint_list.html",
        tenprint_cards = tenprint_cards
    )

@app.route( config.baseurl + "/admin/<submission_id>/tenprint/<tenprint_id>" )
@admin_required
def admin_tenprint( submission_id, tenprint_id ):
    """
        Serve the page to see and edit a tenprint file.
    """
    app.logger.info( "Serve tenprint edit page for '{}', submission '{}'".format( tenprint_id, submission_id ) )
    sql = """
        SELECT
            files.uuid,
            files.format, files.resolution, files.width, files.height, files.size,
            files.creation_time, files.type,
            file_template.template, files.quality,
            users.username
        FROM files
        LEFT JOIN submissions ON files.folder = submissions.id
        LEFT JOIN users ON submissions.donor_id = users.id
        LEFT JOIN file_template ON files.uuid = file_template.file
        WHERE
            submissions.uuid = %s AND
            files.uuid = %s
    """
    tenprint_file = config.db.query_fetchone( sql, ( submission_id, tenprint_id, ) )
    
    app.logger.debug( "tenprint type: {}".format( tenprint_file[ "type" ] ) )
    
    if tenprint_file[ "type" ] == 5:
        app.logger.debug( "Redirect to the segments list page" )
        return redirect( url_for( "submission_tenprint_segments_list", submission_id = submission_id, tenprint_id = tenprint_id ) )
    
    else:
        tenprint_file[ "size" ] = round( 100 * float( tenprint_file[ "size" ] ) / ( 1024 * 1024 ) ) / 100
        
        if tenprint_file[ "type" ] == 1:
            side = "front"
        elif tenprint_file[ "type" ] == 2:
            side = "back"
        
        ############################################################################
        
        try:
            sql = "SELECT width, height, image_resolution FROM tenprint_cards WHERE id = %s LIMIT 1"
            tmp = config.db.query_fetchone( sql, ( tenprint_file[ "template" ], ) )
            
            card_info = {
                "width": int( round( float( tmp[ "width" ] ) / 2.54 * tmp[ "image_resolution" ] ) ),
                "height": int( round( float( tmp[ "height" ] ) / 2.54 * tmp[ "image_resolution" ] ) ),
                "width_cm": tmp[ "width" ],
                "height_cm": tmp[ "height" ]
            }
        except:
            card_info = {
                "width": 0,
                "height": 0,
                "width_cm": 0,
                "height_cm": 0
            }
        
        ############################################################################
        
        sql = "SELECT id, country_code, name, width, height, size_display FROM tenprint_cards ORDER BY name"
        tenprint_templates = config.db.query_fetchall( sql )
        
        ############################################################################
        
        sql = "SELECT id, name FROM quality_type"
        quality_type = config.db.query_fetchall( sql )
        
        ############################################################################
        
        zones = get_tenprint_template_zones( tenprint_file[ "template" ], side )
        datacolumns = [ "tl_x", "tl_y", "br_x", "br_y", "angle" ]
        
        ############################################################################
        
        sql = "SELECT width, height, resolution FROM files WHERE uuid = %s LIMIT 1"
        img_info = config.db.query_fetchone( sql, ( tenprint_id, ) )
        svg_hw_factor = float( img_info[ "width" ] ) / float( img_info[ "height" ] )
        
        return my_render_template( 
            "admin/tenprint.html",
            submission_id = submission_id,
            file = tenprint_file,
            card_info = card_info,
            img_info = img_info,
            svg_hw_factor = svg_hw_factor,
            zones = zones,
            datacolumns = datacolumns,
            tenprint_templates = tenprint_templates,
            quality_type = quality_type
        )

@app.route( config.baseurl + "/admin/submission/<submission_id>/tenprint/<tenprint_id>/segment/list" )
@admin_required
def admin_submission_tenprint_segments_list( submission_id, tenprint_id ):
    """
        Serve the page with the list of segments for a tenprint image.
    """
    app.logger.info( "Get the list of segments for tenprint '{}', submission id '{}'".format( tenprint_id, submission_id ) )
    
    sql = """
        SELECT files_segments.pc, files_segments.data, pc.name
        FROM files_segments
        LEFT JOIN pc ON pc.id = files_segments.pc
        WHERE tenprint = %s
    """
    segments = config.db.query_fetchall( sql, ( tenprint_id, ) )
    nb_segments = len( segments )
    
    app.logger.debug( "{} segments stored in database".format( nb_segments ) )
    
    sql = "SELECT username FROM users LEFT JOIN submissions ON submissions.donor_id = users.id WHERE submissions.uuid = %s"
    donor_username = config.db.query_fetchone( sql, ( submission_id, ) )[ "username" ]
    
    ############################################################################
    
    return my_render_template( 
        "admin/segment_list.html",
        submission_id = submission_id,
        tenprint_id = tenprint_id,
        donor_username = donor_username,
        segments = segments,
        nb_segments = nb_segments
    )

################################################################################
#    Image processing

def get_submission_uuid_for_file( file_uuid ):
    """
        Get the related submission uuid for a file uuid.
    """
    sql = """
        SELECT submissions.uuid
        FROM submissions
        LEFT JOIN files ON submissions.id = files.folder
        WHERE files.uuid = %s
    """
    return config.db.query_fetchone( sql, ( file_uuid, ) )[ "uuid" ]

@app.route( config.baseurl + "/image/file/<file_id>/preview" )
@login_required
def image_file_serve( file_id ):
    """
        Function to get an image from the database and return it as PNG preview image.
    """
    app.logger.info( "Serve a preview for the file '{}'".format( file_id ) )
    
    try:
        submission_id = get_submission_uuid_for_file( file_id )
        app.logger.debug( "submission id: '{}'".format( submission_id ) )
        
        img, _ = image_serve( "thumbnails", file_id, submission_id )
        
        if img == None:
            app.logger.debug( "No image in the 'thumnnails' database. Recreating the thumbnail" )
            
            img, _ = image_serve( "files", file_id, submission_id )
            
            if img == None:
                return abort( 404 )
            
            app.logger.debug( "Image from the 'files' table: {}".format( img ) )
            img = create_thumbnail( file_id, img, submission_id )
            app.logger.debug( "Thumbnail image: {}".format( img ) )
        
        buff = utils.images.pil2buffer( img, "PNG" )
        return send_file( buff, mimetype = "image/png" )
    
    except:
        app.logger.error( "Error while creating the thumbnail. Serving a 'no preview' image" )
        return send_file( no_preview_image(), mimetype = "image/png" )

@app.route( config.baseurl + "/image/segment/<tenprint_id>/<pc>" )
@login_required
def image_segment_serve( tenprint_id, pc ):
    """
        Serve a preview for a segment image.
    """
    app.logger.info( "Serving a segment image for the tenprint '{}', pc '{}'".format( tenprint_id, pc ) )
    
    try:
        submission_id = get_submission_uuid_for_file( tenprint_id )
        
        app.logger.debug( "submission id: {}".format( submission_id ) )
        
        img, file_segment_id = image_serve( "files_segments", ( tenprint_id, pc ), submission_id )
        img = create_thumbnail( file_segment_id, img, submission_id )
        
        app.logger.debug( "image: {}".format( img ) )
        
        buff = utils.images.pil2buffer( img, "PNG" )
        return send_file( buff, mimetype = "image/png" )
    
    except:
        app.logger.error( "Error while creating the thumbnail. Serving a 'no preview' image" )
        return send_file( no_preview_image(), mimetype = "image/png" )

@app.route( config.baseurl + "/image/template/<tenprint_id>/<side>" )
@app.route( config.baseurl + "/image/template/<tenprint_id>/<side>/<action>" )
@login_required
def image_tp_template( tenprint_id, side, action = "full" ):
    """
        Serve a template image, full-resolution or preview.
    """
    app.logger.info( "Serve the tenprint template image for {}, {}".format( tenprint_id, side ) )
    
    if side in [ "front", "back" ]:
        img, _ = image_serve( "tenprint_cards", ( tenprint_id, side ), None )
        
        if img == None:
            return send_file( no_preview_image(), mimetype = "image/png" )
        
        if action == "preview":
            img.thumbnail( ( 500, 500 ) )
        
        app.logger.debug( "image: {}".format( img ) )
        
        buff = utils.images.pil2buffer( img, "PNG" )
        return send_file( buff, mimetype = "image/png" )
    
    else:
        return abort( 403 )

def image_serve( table, image_id, submission_id ):
    """
        Backend function to get the image from the database.
    """
    app.logger.info( "Serve the image '{}' for submission '{}' from table '{}'".format( image_id, submission_id, table ) )
    
    need_to_decrypt = True
    
    if table == "files_segments":
        if isinstance( image_id, tuple ):
            tp, pc = image_id
            sql = "SELECT data, uuid FROM {} WHERE tenprint = %s AND pc = %s".format( table )
            p = ( tp, pc, )
        else:
            sql = "SELECT data, uuid FROM {} WHERE uuid = %s".format( table )
            p = ( image_id, )
    
    elif table in [ "files", "thumbnails" ]:
        sql = "SELECT data, uuid FROM {} WHERE uuid = %s".format( table )
        p = ( image_id, )
    
    elif table == "tenprint_cards":
        image_id, t = image_id
        sql = "SELECT image_{}, id FROM {} WHERE id = %s".format( t, table )
        p = ( image_id, )
        need_to_decrypt = False
    
    else:
        app.logger.error( "table '{}' not authorized".format( table ) )
        raise Exception( "table not authorized" )
    
    app.logger.debug( "sql:    {}".format( sql ) )
    app.logger.debug( "params: {}".format( p ) )
    
    data = config.db.query_fetchone( sql, p )
    
    if data == None:
        return None, None
    
    else:
        img, rid = data
        
        if img == None:
            return None, None
        
        app.logger.debug( "image: {}...".format( img[ 0:20 ] ) )
        app.logger.debug( "need_to_decrypt: {}".format( need_to_decrypt ) )
        
        if need_to_decrypt:
            img = do_decrypt_dek( img, submission_id )
        
        img = str2img( img )
        
        app.logger.debug( "image: {}".format( img ) )
        
        return img, rid

def str2img( data ):
    """
        Convert a base64 string image to a PIL image.
    """
    app.logger.info( "Convert string image to PIL format" )
    
    if data == None:
        return None
    
    else:
        img = base64.b64decode( data )
        buff = StringIO()
        buff.write( img )
        buff.seek( 0 )
        img = Image.open( buff )
        
        app.logger.debug( "string: {}".format( data[ 0:20 ] ) )
        app.logger.debug( "image: {}".format( img ) )
        
        return img

@app.route( config.baseurl + "/image/file/<image_id>/info" )
@login_required
def img_info( image_id ):
    """
        Get and return the metadata for a particular image.
        See do_img_info() for more informations.
    """
    app.logger.info( "Serve image informations for image '{}'".format( image_id ) )
    
    d = do_img_info( image_id )
    
    if d != None:
        return jsonify( d )
    else:
        return abort( 404 )

def do_img_info( image_id ):
    """
        Retrieve the metadata for a particular image from the database.
    """
    app.logger.debug( "Get image information from database for '{}'".format( image_id ) )
    
    sql = "SELECT size, width, height, resolution, format FROM files WHERE uuid = %s"
    d = config.db.query_fetchone( sql, ( image_id, ) )
    
    for key, value in d.iteritems():
        app.logger.debug( "{}: {}".format( key, value ) )
    
    if d != None:
        return dict( d )
    else:
        return None

def create_thumbnail( file_uuid, img, submission_id ):
    """
        Generate a thumbnail image for a PIL image passed in argument.
    """
    app.logger.info( "Creating a thumbnail for the file '{}', submission '{}'".format( file_uuid, submission_id ) )
    app.logger.debug( "Input image: {}".format( img ) )
    
    img.thumbnail( ( 1000, 1000 ) )
    width, height = img.size
    
    app.logger.debug( "Thumbnail: {}".format( img ) )
    
    img_format = img.format
    buff = StringIO()
    
    if img_format.upper() in [ "TIFF", "TIF" ]:
        img.save( buff, format = img_format, compression = "raw" )
    else:
        img.save( buff, format = img_format )
    
    img_size = buff.tell()
    buff.seek( 0 )
    
    app.logger.debug( "Encrypt the thumnbnail with DEK" )
    img_data = buff.getvalue()
    img_data = base64.b64encode( img_data )
    img_data = do_encrypt_dek( img_data, submission_id )
    
    app.logger.debug( "Saving thumnbail to database" )
    sql = utils.sql.sql_insert_generate( "thumbnails", [ "uuid", "width", "height", "size", "format", "data" ] )
    data = ( file_uuid, width, height, img_size, img.format, img_data, )
    config.db.query( sql, data )
    config.db.commit()
    
    return img

@app.route( config.baseurl + "/image/segment/<tenprint_id>/start" )
@login_required
def image_tenprint_segmentation( tenprint_id ):
    """
        Route to start the segmentation of a tenprint image into segments (fingers or palm images).
    """
    app.logger.info( "Start segmentations for '{}'".format( tenprint_id ) )
    
    ret = do_image_tenprint_segmentation( tenprint_id )
    
    return jsonify( {
        "error": False,
        "data": ret
    } )

def do_image_tenprint_segmentation( tenprint_id ):
    """
        Backend function to create all the segments images for a tenprint souce image.
    """
    sql = "SELECT size, resolution, type, format, data FROM files WHERE uuid = %s"
    img = config.db.query_fetchone( sql, ( tenprint_id, ) )