module.py

def submission_latent_delete( submission_id, latent_id ):
    """
        Delete a latent from the database.
    """
    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" ], latent_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    else:
        return jsonify( {
            "error": True
        } )

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

@app.route( baseurl + "/submission/<submission_id>/delete" )
@submitter_required
def submission_delete( submission_id ):
    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:
        return jsonify( {
            "error": True,
            "message": "Can not delete if a consent form is already uploaded"
        } )

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

def get_submission_uuid_for_file( 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( 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.
    """
    try:
        submission_id = get_submission_uuid_for_file( file_id )
        
        img, _ = image_serve( "thumbnails", file_id, submission_id )
        
        if img == None:
            img, _ = image_serve( "files", file_id, submission_id )
            
            if img == None:
                return abort( 404 )
            
            img = create_thumbnail( file_id, img, submission_id )
        
        buff = pil2buffer( img, "PNG" )
        return send_file( buff, mimetype = "image/png" )
    
    except:
        img = Image.new( "L", ( 210, 297 ), 255 )
        draw = ImageDraw.Draw( img )
        font = ImageFont.truetype( "arial.ttf", 18 )
        draw.text( ( 0, 0 ), "No preview", 0, font = font )
         
        buff = pil2buffer( img, "PNG" )
        return send_file( buff, mimetype = "image/png" )

@app.route( baseurl + "/image/segment/<tenprint_id>/<pc>" )
@login_required
def image_segment_serve( tenprint_id, pc ):
    """
        Serve a preview for a segment image.
    """
    submission_id = get_submission_uuid_for_file( tenprint_id )
    img, file_segment_id = image_serve( "files_segments", ( tenprint_id, pc ), submission_id )
    img = create_thumbnail( file_segment_id, img, submission_id )
    
    buff = pil2buffer( img, "PNG" )
    return send_file( buff, mimetype = "image/png" )

@app.route( baseurl + "/image/template/<tenprint_id>/<side>" )
@app.route( 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.
    """
    if side in [ "front", "back" ]:
        img, _ = image_serve( "tenprint_cards", ( tenprint_id, side ) )
        if action == "preview":
            img.thumbnail( ( 500, 500 ) )
        
        buff = pil2buffer( img, "PNG" )
        return send_file( buff, mimetype = "image/png" )
    
    else:
        return abort( 403 )

def image_serve( db, image_id, submission_id ):
    """
        Backend function to get the image from the database.
    """
    if db == "files_segments":
        if isinstance( image_id, tuple ):
            tp, pc = image_id
            sql = "SELECT data, uuid FROM {} WHERE tenprint = %s AND pc = %s".format( db )
            p = ( tp, pc, )
        else:
            sql = "SELECT data, uuid FROM {} WHERE uuid = %s".format( db )
            p = ( image_id, )
    
    elif db in [ "files", "thumbnails" ]:
        sql = "SELECT data, uuid FROM {} WHERE uuid = %s".format( db )
        p = ( image_id, )
    
    elif db == "tenprint_cards":
        image_id, t = image_id
        sql = "SELECT image_{}, id FROM {} WHERE id = %s".format( t, db )
        p = ( image_id, )
    
    else:
        raise Exception( "table not authorized" )
    
    data = config.db.query( sql, p ).fetchone()
    if data == None:
        return None, None
    
    else:
        img, rid = data
        img = do_decrypt_dek( img, submission_id )
        img = str2img( img )
        
        return img, rid

def str2img( data ):
    """
        Convert a base64 string image to a PIL image.
    """
    if data == None:
        return None
    
    else:
        img = base64.b64decode( data )
        buff = StringIO()
        buff.write( img )
        buff.seek( 0 )
        img = Image.open( buff )
        
        return img

@app.route( 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.
    """
    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.
    """
    sql = "SELECT size, width, height, resolution, format FROM files WHERE uuid = %s"
    r = config.db.query( sql, ( image_id, ) )
    d = r.fetchone()
    
    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.
    """
    img.thumbnail( ( 1000, 1000 ) )
    width, height = img.size
    
    buff = StringIO()
    img.save( buff, format = img.format )
    img_size = buff.tell()
    buff.seek( 0 )
    
    img_data = buff.getvalue()
    img_data = base64.b64encode( img_data )
    img_data = do_encrypt_dek( img_data, submission_id )
    
    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( 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).
    """
    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"
    r = config.db.query( sql, ( tenprint_id, ) )
    img = r.fetchone()
    
    res = img[ "resolution" ]
    img_format = img[ "format" ]
    t = {
        1: "front",
        2: "back"
    }[ img[ "type" ] ]
    
    submission_id = get_submission_uuid_for_file( tenprint_id )
    
    img = do_decrypt_dek( img[ "data" ], submission_id )
    img = base64.b64decode( img )
    
    buff = StringIO()
    buff.write( img )
    buff.seek( 0 )
    img = Image.open( buff )
    
    sql = "SELECT template FROM file_template WHERE file = %s"
    r = config.db.query( sql, ( tenprint_id, ) )
    template_id = r.fetchone()[ "template" ]
    
    zones = get_tenprint_template_zones( template_id, t )
    
    for z in zones:
        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 )
        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( sql, ( tenprint_id, z[ "pc" ], ) ).fetchone()
        
        if q == None:
            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:
            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( baseurl + "/submission/<submission_id>/tenprint/list" )
@submitter_required
def submission_tenprint_list( submission_id ):
    """
        Serve the page with the list of tenprint images, splitted by front, back and NIST format.
    """
    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
            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
    """
    
    r = config.db.query( sql, ( submission_folder_id, ) )
    q = r.fetchall()
    
    tenprint_cards = {
        "1": [],
        "2": [],
        "5": []
    }
    for tenprint in q:
        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 )
        } )
    
    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( baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>" )
@submitter_required
def submission_tenprint( submission_id, tenprint_id ):
    """
        Serve the page to see and edit a tenprint file.
    """
    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,
            file_template.template, files.quality
        FROM files
        LEFT JOIN file_template ON files.uuid = file_template.file
        WHERE
            folder = %s AND
            files.uuid = %s
    """
    
    r = config.db.query( sql, ( submission_folder_id, tenprint_id, ) )
    tenprint_file = r.fetchone()
    
    if tenprint_file[ "type" ] == 5:
        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:
            t = "front"
        elif tenprint_file[ "type" ] == 2:
            t = "back"
        
        ############################################################################
        
        try:
            sql = "SELECT width, height, image_resolution FROM tenprint_cards WHERE id = %s LIMIT 1"
            r = config.db.query( sql, ( tenprint_file[ "template" ], ) )
            tmp = r.fetchone()
            
            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( sql ).fetchall()
        
        ############################################################################
        
        sql = "SELECT id, name FROM quality_type"
        quality_type = config.db.query_fetchall( sql )
        
        ############################################################################
        
        zones = get_tenprint_template_zones( tenprint_file[ "template" ], t )
        datacolumns = [ "tl_x", "tl_y", "br_x", "br_y", "angle" ]
        
        ############################################################################
        
        sql = "SELECT width, height, resolution FROM files WHERE uuid = %s LIMIT 1"
        r = config.db.query( sql, ( tenprint_id, ) )
        img_info = r.fetchone()
        svg_hw_factor = float( img_info[ "width" ] ) / float( img_info[ "height" ] )
        
        return my_render_template( 
            "submission/tenprint.html",
            submission_id = submission_id,
            tenprint_id = tenprint_id,
            file = tenprint_file,
            nickname = nickname,
            t = t,
            card_id = tenprint_file[ "uuid" ],
            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( baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/delete" )
@submitter_required
def submission_tenprint_delete( submission_id, tenprint_id ):
    """
        Endpoint to delete a tenprint image.
    """
    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
    } )

@app.route( baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/set/template", methods = [ "POST" ] )
@submitter_required
def submission_tenprint_set_template( submission_id, tenprint_id ):
    """
        Set the template id for a tenprint image.
    """
    template = request.form.get( "template" )
    
    sql = "SELECT id FROM file_template WHERE file = %s"
    q = config.db.query( sql, ( tenprint_id, ) ).fetchone()
    
    if q == None:
        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
    } )

@app.route( baseurl + "/submission/<submission_id>/<file_type>/<tenprint_id>/set/note", methods = [ "POST" ] )
@submitter_required
def submission_file_set_note( submission_id, file_type, tenprint_id ):
    """
        Store the user encrypted notes for a tenprint image.
    """
    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
    } )

@app.route( baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/set/quality", methods = [ "POST" ] )
@submitter_required
def submission_tenprint_set_quality( submission_id, tenprint_id ):
    """
        Store the user encrypted notes for a tenprint image.
    """
    quality = request.form.get( "quality" )
    
    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
    } )

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

@app.route( baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/segment/list" )
@submitter_required
def submission_tenprint_segments_list( submission_id, tenprint_id ):
    """
        Serve the page with the list of segments for a tenprint 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 uuid, filename FROM files WHERE folder = %s AND files.uuid = %s"
    
    r = config.db.query( sql, ( submission_folder_id, tenprint_id, ) )
    tenprint_file = r.fetchone()
    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( sql, ( tenprint_id, ) ).fetchall()
    nb_segments = len( 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( baseurl + "/submission/<submission_id>/tenprint/<tenprint_id>/segment/<pc>" )
@submitter_required
def submission_segment( submission_id, tenprint_id, pc ):
    """
        Serve the page to edit the information relative to a segment image.
    """
    pc = int( pc )
    pc_list = [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 25, 27 ]
    
    if not pc in pc_list:
        return redirect( url_for( "submission_tenprint_segments_list", submission_id = submission_id, tenprint_id = tenprint_id ) )
    
    else:
        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 uuid, filename, type FROM files WHERE folder = %s AND files.uuid = %s"
        r = config.db.query( sql, ( submission_folder_id, tenprint_id, ) )
        tp_file = r.fetchone()
        tp_filename = do_decrypt_user_session( tp_file[ "filename" ] )
        
        sql = "SELECT name FROM pc WHERE id = %s"
        pc_name = config.db.query( sql, ( pc, ) ).fetchone()[ 0 ]
        
        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( sql, ( submission_id, pc, ) ).fetchone()[ 0 ]
        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 )
        
        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( baseurl + "/submission/<submission_id>/tenprint/segment/<pc>/set/gp", methods = [ "POST" ] )
@submitter_required
def submission_segment_set_gp( submission_id, pc ):
    """
        Set the general pattern of a fingerprint segment image (FPC 1-10).
    """
    pc = int( pc )
    
    gp = request.form.get( "gp" )
    
    sql = "SELECT id FROM gp WHERE name = %s"
    r = config.db.query( sql, ( gp, ) ).fetchone()
    if r == None:
        return jsonify( {
            "error": True,
            "message": "General patter 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:
        sql = sql_insert_generate( "donor_fingers_gp", [ "donor_id", "fpc", "gp" ] )
        config.db.query( sql, ( donor_id, pc, gp_id, ) )
        print ( donor_id, pc, gp_id, )
    
    else:
        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
    } )

################################################################################
#    User profile

@app.route( baseurl + "/user/myprofile/tenprint" )
@login_required
def user_myprofile_tenprint():
    """
        Serve the page to see all the information related to the current user.

        This page is the summary of all informations related to the current logged user,
        i.e. the tenprint cards and latent images. The consent form, beeing mendatory to
        upload the tenprint and latent images, and beeing encrypted in the database, is
        not accessible by the user via this interface. The consent form has been sent the 
        the donor by email anyways before uploading any of the images.
    """
    
    sql = """
        SELECT files.id, files.uuid
        FROM users
        LEFT JOIN submissions ON users.email = submissions.email_hash
        LEFT JOIN files ON files.folder = submissions.id
        WHERE users.id = %s AND ( files.type = 1 OR files.type = 2 OR files.type = 5 )
    """
    tenprint_cards = config.db.query_fetchall( sql, ( session[ "user_id" ], ) )
    
    return my_render_template( 
        "users/profile/tenprint.html",
        tenprint_cards = tenprint_cards
    )

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

@app.route( baseurl + "/template/tenprint/list" )
@admin_required
def template_tenprint_list():
    """
        Serve the page with the list of templates.
    """
    sql = "SELECT id, country_code, name FROM tenprint_cards ORDER BY name ASC"
    tp_templates = config.db.query( sql ).fetchall()
    
    return my_render_template( 
        "tp_template/list.html",
        tp_templates = tp_templates
    )

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

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

@app.route( baseurl + "/template/tenprint/new/insert", methods = [ "POST" ] )
@admin_required
def template_tenprint_new_do():
    """
        Save the tenprint template to the database.
    """
    name = request.form.get( "name" )
    country_code = request.form.get( "country_code" )
    
    sql = sql_insert_generate( "tenprint_cards", [ "name", "country_code" ] )
    q = config.db.query( sql, ( name, country_code, ) )
    template_id = q.fetchone()[ 0 ]
    
    for pc in [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 25, 27 ]:
        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
    } )

@app.route( 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 databse.
    """
    face = request.form.get( "card_face" )
    
    if face 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
        
        fp = StringIO()
        img.save( fp, format = "JPEG" )
        fp.seek( 0 )
        data = fp.getvalue()
        data = base64.b64encode( data )
        
        sql = """
            UPDATE tenprint_cards
            SET
                image_{0} = %s,
                image_{0}_width = %s,
                image_{0}_height = %s,
                image_resolution = %s,
                image_format = %s,
                width = %s,
                height = %s
            
            WHERE id = %s""".format( face )
        
        config.db.query( sql, ( data, image_width, image_height, res, "JPEG", width, height, template_id, ) )
        config.db.commit()
        
        if res != 0:
            return jsonify( {
                "error": False
            } )
        
        else:
            return jsonify( {
                "need_action": True,
                "action": "set_resolution"
            } )
    
    else:
        return abort( 403 )

@app.route( baseurl + "/template/tenprint/<template_id>/set/resolution", methods = [ "POST" ] )
@admin_required
def template_tenprint_new_setresolution( template_id ):
    """
        Set the resolution for a tenprint template image.
    """
    res = request.form.get( "resolution" )
    
    try:
        sql = "UPDATE tenprint_cards SET image_resolution = %s WHERE id = %s"
        config.db.query( sql, ( res, template_id, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    except:
        return jsonify( {
            "error": True
        } )

def get_tenprint_template_zones( template_id, t ):
    """
        Get all the segments zones for a template passed in parameter.
    """
    sql = """
        SELECT
            tenprint_zones.pc, tl_x, tl_y, br_x, br_y, angle, pc.name
        FROM tenprint_zones
        JOIN tenprint_zones_location ON tenprint_zones.pc = tenprint_zones_location.pc
        JOIN pc ON tenprint_zones.pc = pc.id
        WHERE
            card = %s AND
            tenprint_zones_location.side = %s
        ORDER BY pc
    """
    r = config.db.query( sql, ( template_id, t, ) ).fetchall()
    
    zones = []
    for pc, tl_x, tl_y, br_x, br_y, angle, pc_name in r:
        tl_x = float_or_null( tl_x )
        tl_y = float_or_null( tl_y )
        br_x = float_or_null( br_x )
        br_y = float_or_null( br_y )
        
        zones.append( {
            "pc": pc,
            "tl_x": tl_x,
            "tl_y": tl_y,
            "br_x": br_x,
            "br_y": br_y,
            "angle": angle,
            "pc_name": pc_name
        } )
    
    return zones

@app.route( baseurl + "/template/tenprint/<template_id>/<side>" )
@login_required
def template_tenprint( template_id, side ):
    """
        Serve the tenprint template page.
    """
    if side in [ "front", "back" ]:
        sql = """
            SELECT
                tenprint_zones.pc, tl_x, tl_y, br_x, br_y, angle, pc.name
            FROM tenprint_zones
            JOIN tenprint_zones_location ON tenprint_zones.pc = tenprint_zones_location.pc
            JOIN pc ON tenprint_zones.pc = pc.id
            WHERE card = %s AND tenprint_zones_location.side = %s ORDER BY pc
        """
        r = config.db.query( sql, ( template_id, side, ) ).fetchall()
        
        zones = []
        for pc, tl_x, tl_y, br_x, br_y, angle, pc_name in r:
            tl_x = float_or_null( tl_x )
            tl_y = float_or_null( tl_y )
            br_x = float_or_null( br_x )
            br_y = float_or_null( br_y )
            
            zones.append( {
                "pc": pc,
                "tl_x": tl_x,
                "tl_y": tl_y,
                "br_x": br_x,
                "br_y": br_y,
                "angle": angle,
                "pc_name": pc_name
            } )
        
        datacolumns = [ "tl_x", "tl_y", "br_x", "br_y", "angle" ]
        
        sql = """
            SELECT
                id,
                name, country_code,
                width, height, size_display,
                image_{0}_width, image_{0}_height,
                image_resolution
            FROM tenprint_cards
            WHERE id = %s LIMIT 1
        """.format( side )

        r = config.db.query( sql, ( template_id, ) )
        img_info = r.fetchone()
        
        card_info = {
            "width": int( round( float( img_info[ "width" ] ) / 2.54 * img_info[ "image_resolution" ] ) ),
            "height": int( round( float( img_info[ "height" ] ) / 2.54 * img_info[ "image_resolution" ] ) ),
        }
        
        svg_h = float( img_info[ "image_{}_height".format( side ) ] )
        svg_w = float( img_info[ "image_{}_width".format( side ) ] )
        svg_hw_factor = svg_w / svg_h
        
        return my_render_template( 
            "tp_template/template.html",
            zones = zones,
            img_info = img_info,
            card_info = card_info,
            svg_hw_factor = svg_hw_factor,
            card_id = template_id,
            side = side,
            datacolumns = datacolumns,
            **config.misc
        )
    
    else:
        return abort( 403 )

@app.route( baseurl + "/template/tenprint/<template_id>/set/zones", methods = [ "POST" ] )
@login_required
def update_zone_coordinates( template_id ):
    """
        Update the segments zones coordinates in the database.
    """
    template_id = int( template_id )
    data = request.form.get( "data" )
    
    if data != None:
        data = json.loads( data )
        
        for pc, value in data.iteritems():
            pc = int( pc )
            
            for coordinate, v in value.iteritems():
                sql = "UPDATE tenprint_zones SET {} = %s WHERE card = %s AND pc = %s".format( coordinate )
                data = ( v, template_id, pc, )
                config.db.query( sql, data )
        
        config.db.commit()
        
        return jsonify( {
            "error": False
        } )
    
    else:
        return abort( 403 )

@app.route( baseurl + "/template/tenprint/<template_id>/delete/zone", methods = [ "POST" ] )
@login_required
def delete_zone_coordinates( template_id ):
    """
        Delete a unused segment zone for a template (for example FPC 25 and 27 on the front-page, ...)
    """
    pc = request.form.get( "pc" )
    
    try:
        sql = "DELETE FROM tenprint_zones WHERE card = %s AND pc = %s"
        config.db.query( sql, ( template_id, pc, ) )
        config.db.commit()
        
        return jsonify( {
            "error": False