module.py

            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, ) )
    
    for key, value in img.iteritems():
        if isinstance( value, str ) and len( value ) > 20:
            value = "{}...".format( value[ 0:20 ] )
        app.logger.debug( "{}: {}".format( key, value ) )
    
    res = img[ "resolution" ]
    img_format = img[ "format" ]
    side = {
        1: "front",
        2: "back"
    }[ img[ "type" ] ]
    
    app.logger.debug( "side: {}".format( side ) )
    
    submission_id = get_submission_uuid_for_file( tenprint_id )
    
    app.logger.debug( "Decrypt data with DEK" )
    img = do_decrypt_dek( img[ "data" ], submission_id )
    img = base64.b64decode( img )
    
    buff = StringIO()
    buff.write( img )
    buff.seek( 0 )
    img = Image.open( buff )
    
    app.logger.debug( "image: {}".format( img ) )
    
    sql = "SELECT template FROM file_template WHERE file = %s"
    template_id = config.db.query_fetchone( sql, ( tenprint_id, ) )[ "template" ]
    
    zones = get_tenprint_template_zones( template_id, side )
    
    app.logger.debug( "Use '{}' as tenprint template".format( template_id ) )
    
    for z in zones:
        app.logger.debug( "Segmenting fpc '{}' ({})".format( z[ "pc" ], z[ "pc_name" ] ) )
        
        tl_x, tl_y, br_x, br_y = map( lambda v: v * res / 2.54 , [ z[ "tl_x" ], z[ "tl_y" ], z[ "br_x" ], z[ "br_y" ] ] )
        tmp = img.crop( ( tl_x, tl_y, br_x, br_y ) )
        buff = StringIO()
        tmp.save( buff, format = img_format )
        buff.seek( 0 )
        
        app.logger.debug( "Encrypting segment image with DEK" )
        file_data = buff.getvalue()
        file_data = base64.b64encode( file_data )
        file_data = do_encrypt_dek( file_data, submission_id )
        
        sql = "SELECT id FROM files_segments WHERE tenprint = %s AND pc = %s"
        q = config.db.query_fetchone( sql, ( tenprint_id, z[ "pc" ], ) )
        
        if q == None:
            app.logger.debug( "Inserting to the database" )
            sql = utils.sql.sql_insert_generate( "files_segments", [ "tenprint", "uuid", "pc", "data" ] )
            data = ( tenprint_id, str( uuid4() ), z[ "pc" ], file_data )
            config.db.query( sql, data )
        
        else:
            app.logger.debug( "Updating the database" )
            sql = "UPDATE files_segments SET data = %s WHERE tenprint = %s AND pc = %s"
            data = ( file_data, tenprint_id, z[ "pc" ] )
            config.db.query( sql, data )
    
    config.db.commit()
    
    return True

################################################################################
#    Donor tenprints

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/list" )
@submission_has_access
def submission_tenprint_list( submission_id ):
    """
        Serve the page with the list of tenprint images, splitted by front, back and NIST format.
    """
    app.logger.info( "Get the list of tenprint for the submission '{}'".format( submission_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 id, filename, uuid, type, creation_time
        FROM files
        WHERE folder = %s AND ( type = 1 OR type = 2 OR type = 5 )
        ORDER BY creation_time DESC
    """
    
    q = config.db.query_fetchall( sql, ( submission_folder_id, ) )
    
    tenprint_cards = {
        "1": [],
        "2": [],
        "5": []
    }
    nb = 0
    for tenprint in q:
        app.logger.debug( "tenprint: '{}'".format( tenprint[ "id" ] ) )
        nb += 1
        tenprint_cards[ str( tenprint[ "type" ] ) ].append( {
            "id": tenprint.get( "id", None ),
            "filename": do_decrypt_user_session( tenprint.get( "filename", None ) ),
            "uuid": tenprint.get( "uuid", None ),
            "type": tenprint.get( "type", None )
        } )
    
    app.logger.info( "{} tenprint(s) for the submission '{}'".format( nb, submission_id ) )
    
    return my_render_template( 
        "submission/tenprint_list.html",
        tenprint_cards_front = tenprint_cards[ "1" ],
        tenprint_cards_back = tenprint_cards[ "2" ],
        tenprint_cards_nist = tenprint_cards[ "5" ],
        submission_id = submission_id,
        nickname = nickname
    )

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>" )
@submission_has_access
def submission_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 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,
            file_template.template, files.quality
        FROM files
        LEFT JOIN file_template ON files.uuid = file_template.file
        WHERE
            folder = %s AND
            files.uuid = %s
    """
    
    tenprint_file = config.db.query_fetchone( sql, ( submission_folder_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
        tenprint_file[ "filename" ] = do_decrypt_user_session( tenprint_file[ "filename" ] )
        tenprint_file[ "note" ] = do_decrypt_user_session( tenprint_file[ "note" ] )
        
        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( 
            "submission/tenprint.html",
            submission_id = submission_id,
            file = tenprint_file,
            nickname = nickname,
            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 + "/submission/<submission_id>/tenprint/<tenprint_id>/delete" )
@submission_has_access
def submission_tenprint_delete( submission_id, tenprint_id ):
    """
        Endpoint to delete a tenprint image.
    """
    app.logger.info( "Delete tenprint '{}' from submission '{}'".format( tenprint_id, submission_id ) )
    
    try:
        sql = "DELETE FROM files WHERE creator = %s AND uuid = %s"
        config.db.query( sql, ( session[ "user_id" ], tenprint_id, ) )
        
        sql = "DELETE FROM files_segments WHERE tenprint = %s"
        config.db.query( sql, ( tenprint_id, ) )
        
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/set/template", methods = [ "POST" ] )
@submission_has_access
def submission_tenprint_set_template( submission_id, tenprint_id ):
    """
        Set the template id for a tenprint image.
    """
    try:
        template = request.form.get( "template" )
        app.logger.info( "Set tenprint template id to '{}' for '{}', submission id '{}'".format( template, tenprint_id, submission_id ) )
        
        sql = "SELECT id FROM file_template WHERE file = %s"
        q = config.db.query_fetchone( sql, ( tenprint_id, ) )
        
        if q == None:
            sql = utils.sql.sql_insert_generate( "file_template", [ "file", "template" ] )
            config.db.query( sql, ( tenprint_id, template, ) )
            config.db.commit()
        
        else:
            sql = "UPDATE file_template SET template = %s WHERE file = %s"
            config.db.query( sql, ( template, tenprint_id, ) )
            config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

@app.route( config.baseurl + "/submission/<submission_id>/<file_type>/<tenprint_id>/set/note", methods = [ "POST" ] )
@submission_has_access
def submission_file_set_note( submission_id, file_type, tenprint_id ):
    """
        Store the user encrypted notes for a tenprint image.
    """
    try:
        app.logger.info( "Add note for tenprint '{}', '{}', submission id '{}'".format( tenprint_id, file_type, submission_id ) )
        note = request.form.get( "note" )
        note = do_encrypt_user_session( note )
        
        sql = "UPDATE files SET note = %s WHERE uuid = %s RETURNING id"
        config.db.query( sql, ( note, tenprint_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/set/quality", methods = [ "POST" ] )
@submission_has_access
def submission_tenprint_set_quality( submission_id, tenprint_id ):
    """
        Store the quality for a tenprint image.
    """
    try:
        quality = request.form.get( "quality" )
        app.logger.info( "Set the quality to '{}' for tenprint '{}', submission id '{}'".format( quality, tenprint_id, submission_id ) )
        
        sql = "UPDATE files SET quality = %s WHERE uuid = %s RETURNING id"
        config.db.query( sql, ( quality, tenprint_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

################################################################################
#    Tenprint segments

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/segment/list" )
@submission_has_access
def 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 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 uuid, filename FROM files WHERE folder = %s AND files.uuid = %s"
    
    tenprint_file = config.db.query_fetchone( sql, ( submission_folder_id, tenprint_id, ) )
    filename = do_decrypt_user_session( tenprint_file[ "filename" ] )
    tenprint_id = tenprint_file[ "uuid" ]
    
    ############################################################################
    
    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 ) )
    
    ############################################################################
    
    return my_render_template( 
        "submission/segment_list.html",
        submission_id = submission_id,
        tenprint_id = tenprint_id,
        nickname = nickname,
        filename = filename,
        segments = segments,
        nb_segments = nb_segments
    )

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/segment/<pc>" )
@submission_has_access
def submission_segment( submission_id, tenprint_id, pc ):
    """
        Serve the page to edit the information relative to a segment image.
    """
    app.logger.info( "Serve the edit page for segment '{}'".format( pc ) )
    
    pc = int( pc )
    
    if not pc in config.all_fpc:
        app.logger.error( "'{}' not in the pc_list".format( pc ) )
        return redirect( url_for( "submission_tenprint_segments_list", submission_id = submission_id, tenprint_id = tenprint_id ) )
    
    else:
        app.logger.debug( "Retrieving data for submission '{}', pc '{}'".format( submission_id, pc ) )
        
        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 uuid, filename, type FROM files WHERE folder = %s AND files.uuid = %s"
        tp_file = config.db.query_fetchone( sql, ( submission_folder_id, tenprint_id, ) )
        tp_filename = do_decrypt_user_session( tp_file[ "filename" ] )
        
        sql = "SELECT name FROM pc WHERE id = %s"
        pc_name = config.db.query_fetchone( sql, ( pc, ) )[ "name" ]
        
        sql = """
            SELECT gp.div_name
            FROM donor_fingers_gp
            LEFT JOIN submissions ON donor_fingers_gp.donor_id = submissions.donor_id
            LEFT JOIN gp ON donor_fingers_gp.gp = gp.id
            WHERE submissions.uuid = %s AND donor_fingers_gp.fpc = %s
        """
        try:
            current_gp = config.db.query_fetchone( sql, ( submission_id, pc, ) )[ "div_name" ]
        except:
            current_gp = None
        
        if pc in xrange( 1, 10 ):
            next_pc = pc + 1
            tp_type = "finger"
        elif pc == 10:
            next_pc = None
            tp_type = "finger"
        elif pc == 25:
            next_pc = 27
            tp_type = "palm"
        elif pc == 27:
            next_pc = None
            tp_type = "palm"
        else:
            return abort( 404 )
        
        app.logger.debug( "pc: {}".format( pc ) )
        app.logger.debug( "tp_type: {}".format( tp_type ) )
        app.logger.debug( "next pc: {}".format( next_pc ) )
        
        return my_render_template( 
            "submission/segment.html",
            submission_id = submission_id,
            nickname = nickname,
            pc_name = pc_name,
            tp_filename = tp_filename,
            tenprint_id = tenprint_id,
            pc = pc,
            next_pc = next_pc,
            current_gp = current_gp,
            tp_type = tp_type
        )

@app.route( config.baseurl + "/submission/<submission_id>/tenprint/segment/<pc>/set/gp", methods = [ "POST" ] )
@submission_has_access
def submission_segment_set_gp( submission_id, pc ):
    """
        Set the general pattern of a fingerprint segment image (FPC 1-10).
    """
    try:
        pc = int( pc )
        
        gp = request.form.get( "gp" )
        
        app.logger.info( "Set general pattern for '{}', pc '{}' to '{}'".format( submission_id, pc, gp ) )
        
        sql = "SELECT id FROM gp WHERE name = %s"
        r = config.db.query_fetchone( sql, ( gp, ) )
        if r == None:
            app.logger.error( "General pattern not recognized" )
            return jsonify( {
                "error": True,
                "message": "General pattern not recognized"
            } )
        
        gp_id = r[ "id" ]
        
        sql = """
            SELECT count( * )
            FROM donor_fingers_gp
            LEFT JOIN submissions ON donor_fingers_gp.donor_id = submissions.donor_id
            WHERE submissions.uuid = %s AND donor_fingers_gp.fpc = %s
            GROUP BY donor_fingers_gp.id
        """
        nb = config.db.query_fetchone( sql, ( submission_id, pc, ) )
        
        sql = "SELECT donor_id FROM submissions WHERE uuid = %s"
        donor_id = config.db.query_fetchone( sql, ( submission_id, ) )[ "donor_id" ]
        
        if nb == 0 or nb == None:
            app.logger.debug( "Insert general pattern in database" )
            sql = utils.sql.sql_insert_generate( "donor_fingers_gp", [ "donor_id", "fpc", "gp" ] )
            config.db.query( sql, ( donor_id, pc, gp_id, ) )
        
        else:
            app.logger.debug( "Update general patern in database" )
            sql = "UPDATE donor_fingers_gp SET gp = %s WHERE donor_id = %s AND fpc = %s"
            config.db.query( sql, ( gp_id, donor_id, pc, ) )
        
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

################################################################################
#    Tenprint templates

@app.route( config.baseurl + "/template/tenprint/list" )
@admin_required
def template_tenprint_list():
    """
        Serve the page with the list of templates.
    """
    app.logger.info( "Serve the list of tenprint templates" )
    
    sql = "SELECT id, country_code, name FROM tenprint_cards ORDER BY name ASC"
    tp_templates = config.db.query_fetchall( sql )
    
    app.logger.debug( "{} tenmplates found".format( len( tp_templates ) ) )
    
    return my_render_template( 
        "tp_template/list.html",
        tp_templates = tp_templates
    )

@app.route( config.baseurl + "/template/tenprint/new" )
@admin_required
def template_tenprint_new_meta():
    """
        Serve the page to create a new tenprint template.
    """
    app.logger.info( "Create a new tenprint template" )
    
    return my_render_template( "tp_template/new_meta.html" )

@app.route( config.baseurl + "/template/tenprint/new/<template_id>/images" )
@admin_required
def template_tenprint_new_images( template_id ):
    """
        Add new images to a tenprint template.
    """
    app.logger.info( "Add a new image to the tenprint template '{}'".format( template_id ) )
     
    sql = "SELECT id, name, country_code FROM tenprint_cards WHERE id = %s"
    card = config.db.query_fetchone( sql, ( template_id, ) )
    
    return my_render_template( 
        "tp_template/new_images.html",
        card = card
    )

@app.route( config.baseurl + "/template/tenprint/new/insert", methods = [ "POST" ] )
@admin_required
def template_tenprint_new_do():
    """
        Save the tenprint template to the database.
    """
    try:
        app.logger.info( "Save the tenprint template to the database" )
        name = request.form.get( "name" )
        country_code = request.form.get( "country_code" )
        
        app.logger.debug( "name: {}".format( name ) )
        app.logger.debug( "country code: {}".format( country_code ) )
        
        sql = utils.sql.sql_insert_generate( "tenprint_cards", [ "name", "country_code" ], "id" )
        template_id = config.db.query_fetchone( sql, ( name, country_code, ) )[ "id" ]
        
        app.logger.debug( "Set all zones values to 0 for FPC in {}".format( config.all_fpc ) )
        for pc in config.all_fpc:
            sql = utils.sql.sql_insert_generate( "tenprint_zones", [ "card", "pc", "angle", "tl_x", "tl_y", "br_x", "br_y" ] )
            config.db.query( sql, ( template_id, pc, 0, 0, 0, 0, 0 ) )
        
        config.db.commit()
        
        return jsonify( {
            "error": False,
            "id": template_id
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

@app.route( config.baseurl + "/template/tenprint/new/<template_id>/upload_image", methods = [ "POST" ] )
@admin_required
def template_tenprint_new_uploadimage( template_id ):
    """
        Save the front and back images for a tenprint template to the database.
    """
    app.logger.info( "Upload new image for the tenprint template '{}'".format( template_id ) )
    
    side = request.form.get( "card_face" )
    
    if side in [ "front", "back" ]:
        data = request.files[ "file" ]
        
        img = Image.open( data )
        image_width, image_height = img.size
        try:
            res = img.info[ "dpi" ][ 0 ]
            width = round( image_width * 2.54 / float( res ) )
            height = round( image_height * 2.54 / float( res ) )
        except:
            res = 0
            width = 0
            height = 0
        
        app.logger.debug( "side: {}".format( side ) )
        app.logger.debug( "image: {}".format( img ) )
        app.logger.debug( "width: {}".format( width ) )
        app.logger.debug( "height: {}".format( height ) )
        app.logger.debug( "resolution: {}".format( res ) )
        
        fp = StringIO()
        img.save( fp, format = "JPEG" )
        fp.seek( 0 )
        data = fp.getvalue()
        data = base64.b64encode( data )
        
        app.logger.debug( "Saving the image to the database" )
        
        sql = """
            UPDATE tenprint_cards
            SET
                image_{0} = %s,