module.py

    if q != None:
        sql = "DELETE FROM files WHERE creator = %s AND uuid = %s"
        config.db.query( sql, ( session[ 'user_id' ], lid, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
    
    else:
        return jsonify( {
            'error': True
        } )

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

@app.route( baseurl + '/image/file/<id>/preview' )
@login_required
def image_file_serve( id ):
    """
        Function to get an image from the database and return it as PNG preview image.
    """
    try:
        img, _ = image_serve( "thumbnails", id )
        
        if img == None:
            img, _ = image_serve( "files", id )
            
            if img == None:
                return abort( 404 )
            
            img = create_thumbnail( id, img )
        
        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/<tid>/<pc>' )
@login_required
def image_segment_serve( tid, pc ):
    """
        Serve a preview for a segment image.
    """
    img, id = image_serve( "files_segments", ( tid, pc ) )
    img = create_thumbnail( id, img )
    
    buff = pil2buffer( img, "PNG" )
    return send_file( buff, mimetype = "image/png" )

@app.route( baseurl + '/image/template/<tid>/<t>' )
@app.route( baseurl + '/image/template/<tid>/<t>/<action>' )
@login_required
def image_tp_template( tid, t, action = "full" ):
    """
        Serve a template image, full-resolution or preview.
    """
    if t in [ 'front', 'back' ]:
        img, _ = image_serve( "tenprint_cards", ( tid, t ) )
        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, id ):
    """
        Backend function to get the image from the database.
    """
    if db == "files_segments":
        if isinstance( id, tuple ):
            tp, pc = id
            sql = "SELECT data, uuid FROM " + db + " WHERE tenprint = %s AND pc = %s"
            p = ( tp, pc, )
        else:
            sql = "SELECT data, uuid FROM " + db + " WHERE uuid = %s"
            p = ( id, )
    
    elif db in [ "files", "thumbnails" ]:
        sql = "SELECT data, uuid FROM " + db + " WHERE uuid = %s"
        p = ( id, )
    
    elif db == "tenprint_cards":
        id, t = id
        sql = "SELECT image_" + t + ", id FROM " + db + " WHERE id = %s"
        p = ( 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 = 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/<id>/info' )
@login_required
def img_info( id ):
    """
        Get and return the metadata for a particular image.
        See do_img_info() for more informations.
    """
    d = do_img_info( id )
    
    if d != None:
        return jsonify( d )
    else:
        return abort( 404 )

def do_img_info( 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, ( id, ) )
    d = r.fetchone()
    
    if d != None:
        return dict( d )
    else:
        return None

def create_thumbnail( file_uuid, img ):
    """
        Generate a thumbnail image for a PIL image passed in argument.
    """
    img.thumbnail( ( 1000, 1000 ) )
    width, height = img.size
    file_format = img.format
    
    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 )
    
    sql = "INSERT INTO thumbnails ( uuid, width, height, size, format, data ) VALUES ( %s, %s, %s, %s, %s, %s )"
    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/<id>/start' )
@login_required
def image_tenprint_segmentation( id ):
    """
        Route to start the segmentation of a tenprint image into segments (fingers or palm images).
    """
    ret = do_image_tenprint_segmentation( id )
    
    return jsonify( {
        'error': False,
        'data': ret
    } )

def do_image_tenprint_segmentation( 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, ( id, ) )
    img = r.fetchone()
    
    res = img[ 'resolution' ]
    img_format = img[ 'format' ]
    t = {
        1: 'front',
        2: 'back'
    }[ img[ 'type' ] ]
     
    img = base64.b64decode( img[ 'data' ] )
    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, ( 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 )
        
        sql = "SELECT id FROM files_segments WHERE tenprint = %s AND pc = %s"
        q = config.db.query( sql, ( id, z[ 'pc' ], ) ).fetchone()
        
        if q == None:
            sql = "INSERT INTO files_segments ( tenprint, uuid, pc, data ) VALUES ( %s, %s, %s, %s )"
            data = ( 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, id, z[ 'pc' ] )
            config.db.query( sql, data )
    
    config.db.commit()
    
    return True

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

@app.route( baseurl + '/submission/<id>/tenprint/list' )
@login_required
def submission_tenprint_list( 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, ( id, ) )
    submission_id, nickname = r.fetchone()
    nickname = do_decrypt( 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_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( tenprint.get( "filename", None ) ),
            'uuid': tenprint.get( "uuid", None ),
            'type': tenprint.get( "type", None )
        } )
    
    return render_template( 
        "submission/tenprint_list.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        tenprint_cards_front = tenprint_cards[ '1' ],
        tenprint_cards_back = tenprint_cards[ '2' ],
        tenprint_cards_nist = tenprint_cards[ '5' ],
        submission_id = id,
        nickname = nickname,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>' )
@login_required
def submission_tenprint( id, tid ):
    """
        Serve the page to see and edit a tenprint file.
    """
    sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
    r = config.db.query( sql, ( id, ) )
    submission_id, nickname = r.fetchone()
    nickname = do_decrypt( nickname )
    
    sql = """
        SELECT
            files.uuid, files.filename, files.note,
            files.format, files.resolution, files.width, files.height, files.size,
            files.creation_time, files.type,
            file_template.template
        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_id, tid, ) )
    file = r.fetchone()
    
    if file[ "type" ] == 5:
        return redirect( url_for( "submission_tenprint_segments_list", id = id, tid = tid ) )
    
    else:
        file[ 'size' ] = round( 100 * float( file[ 'size' ] ) / ( 1024 * 1024 ) ) / 100
        file[ 'filename' ] = do_decrypt( file[ 'filename' ] )
        file[ 'note' ] = do_decrypt( file[ 'note' ] )
        
        if file[ 'type' ] == 1:
            t = 'front'
        elif 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, ( 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 country_code"
        tenprint_templates = config.db.query( sql ).fetchall()
        
        ############################################################################
        
        zones = get_tenprint_template_zones( 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, ( tid, ) )
        img_info = r.fetchone()
        svg_hw_factor = float( img_info[ 'width' ] ) / float( img_info[ 'height' ] )
        
        return render_template( 
            "submission/tenprint.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            upload_id = id,
            tenprint_id = tid,
            file = file,
            nickname = nickname,
            submission_id = id,
            session_security_key = session.get( "session_security_key" ),
            t = t,
            card_id = file[ 'uuid' ],
            card_info = card_info,
            img_info = img_info,
            svg_hw_factor = svg_hw_factor,
            zones = zones,
            datacolumns = datacolumns,
            tenprint_templates = tenprint_templates,
            envtype = envtype
        )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>/delete' )
@login_required
def submission_tenprint_delete( id, tid ):
    """
        Endpoint to delete a tenprint image.
    """
    sql = "SELECT id FROM submissions WHERE submitter_id = %s AND uuid = %s"
    q = config.db.query( sql, ( session[ 'user_id' ], id, ) )
    
    if q != None:
        sql = "DELETE FROM files WHERE creator = %s AND uuid = %s"
        config.db.query( sql, ( session[ 'user_id' ], tid, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
    
    else:
        return jsonify( {
            'error': True
        } )

@app.route( baseurl + '/submission/<id>/tenprint/<file>/set/template', methods = [ 'GET', 'POST' ] )
@login_required
def submission_tenprint_set_template( id, file ):
    """
        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, ( file, ) ).fetchone()
    
    if q == None:
        sql = "INSERT INTO file_template ( file, template ) VALUES ( %s, %s )"
        config.db.query( sql, ( file, template, ) )
        config.db.commit()
    
    else:
        sql = "UPDATE file_template SET template = %s WHERE file = %s"
        config.db.query( sql, ( template, file, ) )
        config.db.commit()
    
    return jsonify( {
        'error': False
    } )

@app.route( baseurl + '/submission/<id>/<t>/<file>/set/note', methods = [ 'POST' ] )
@login_required
def submission_file_set_note( id, t, file ):
    """
        Store the user encrypted notes for a tenprint image.
    """
    note = request.form.get( "note" )
    note = do_encrypt( note )
    
    sql = "UPDATE files SET note = %s WHERE uuid = %s RETURNING id"
    config.db.query( sql, ( note, file, ) )
    config.db.commit()
    
    return jsonify( {
        'error': False
    } )

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

@app.route( baseurl + '/submission/<id>/tenprint/<tid>/segment/list' )
@login_required
def submission_tenprint_segments_list( id, tid ):
    """
        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, ( id, ) )
    submission_id, nickname = r.fetchone()
    nickname = do_decrypt( nickname )
    
    sql = "SELECT uuid, filename FROM files WHERE folder = %s AND files.uuid = %s"
    
    r = config.db.query( sql, ( submission_id, tid, ) )
    file = r.fetchone()
    filename = do_decrypt( file[ 'filename' ] )
    tid = 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, ( tid, ) ).fetchall()
    
    ############################################################################
    
    return render_template( 
        "submission/segment_list.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        upload_id = id,
        tenprint_id = tid,
        nickname = nickname,
        filename = filename,
        submission_id = id,
        tid = tid,
        segments = segments,
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>/segment/<pc>' )
@login_required
def submission_segment( id, tid, 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", id = id, tid = tid ) )
    
    else:
        sql = "SELECT id, nickname FROM submissions WHERE uuid = %s"
        r = config.db.query( sql, ( id, ) )
        submission_id, nickname = r.fetchone()
        nickname = do_decrypt( nickname )
        
        sql = "SELECT uuid, filename, type FROM files WHERE folder = %s AND files.uuid = %s"
        r = config.db.query( sql, ( submission_id, tid, ) )
        tp_file = r.fetchone()
        tp_filename = do_decrypt( 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 files_segments LEFT JOIN gp ON files_segments.gp = gp.id WHERE files_segments.tenprint = %s AND files_segments.pc = %s"
        current_gp = config.db.query( sql, ( tid, pc, ) ).fetchone()[ 0 ]
        
        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 render_template( 
            "submission/segment.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            submission_id = id,
            nickname = nickname,
            pc_name = pc_name,
            tp_filename = tp_filename,
            tid = tid,
            pc = pc,
            next_pc = next_pc,
            current_gp = current_gp,
            tp_type = tp_type,
            session_security_key = session.get( "session_security_key" ),
            envtype = envtype
        )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>/segment/<pc>/set/gp', methods = [ 'POST' ] )
@login_required
def submission_segment_set_gp( id, tid, pc ):
    """
        Set the general pattern of a fingerprint segment image (FPC 1-10).
    """
    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 = "UPDATE files_segments SET gp = %s WHERE tenprint = %s AND pc = %s"
    config.db.query( sql, ( gp_id, tid, pc, ) )
    config.db.commit()
    
    return jsonify( {
        'error': False
    } )

################################################################################
#    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 render_template( 
        "tp_template/list.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        tp_templates = tp_templates,
        envtype = envtype
    )

@app.route( baseurl + '/template/tenprint/new' )
@admin_required
def template_tenprint_new_meta():
    """
        Serve the page to create a new tenprint template.
    """
    return render_template( 
        "tp_template/new_meta.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        envtype = envtype
    )

@app.route( baseurl + '/template/tenprint/new/<id>/images' )
@admin_required
def template_tenprint_new_images( 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, ( id, ) ).fetchone()
    
    return render_template( 
        "tp_template/new_images.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        card = card,
        envtype = envtype
    )

@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 = "INSERT INTO tenprint_cards ( name, country_code ) VALUES ( %s, %s ) RETURNING id"
    q = config.db.query( sql, ( name, country_code, ) )
    id = q.fetchone()[ 0 ]
    
    for pc in [ 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 25, 27 ]:
        sql = "INSERT INTO tenprint_zones ( card, pc, angle, tl_x, tl_y, br_x, br_y ) VALUES ( %s, %s, %s, %s, %s, %s, %s )"
        config.db.query( sql, ( id, pc, 0, 0, 0, 0, 0 ) )
    
    config.db.commit()
    
    return jsonify( {
        'error': False,
        'id': id
    } )

@app.route( baseurl + '/template/tenprint/new/<id>/upload_image', methods = [ 'POST' ] )
@admin_required
def template_tenprint_new_uploadimage( 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, 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/<id>/set/resolution', methods = [ 'POST' ] )
@admin_required
def template_tenprint_new_setresolution( 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, id, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
    
    except:
        return jsonify( {
            'error': True
        } )

def get_tenprint_template_zones( 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, ( 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/<id>/<t>' )
@login_required
def template_tenprint( id, t ):
    """
        Serve the tenprint template page.
    """
    if t 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, ( 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
            } )
        
        datacolumns = [ 'tl_x', 'tl_y', 'br_x', 'br_y', 'angle' ]
        
        sql = 'SELECT id, name, country_code, width, height, size_display, image_' + t + '_width, image_' + t + '_height, image_resolution FROM tenprint_cards WHERE id = %s LIMIT 1'
        r = config.db.query( sql, ( 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_hw_factor = float( img_info[ 'image_' + t + '_width' ] ) / float( img_info[ 'image_' + t + '_height' ] )
        
        return render_template( 
            "tp_template/template.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            account_type = session.get( "account_type", None ),
            zones = zones,
            img_info = img_info,
            card_info = card_info,
            svg_hw_factor = svg_hw_factor,
            card_id = id,
            t = t,
            datacolumns = datacolumns,
            envtype = envtype,
            **config.misc
        )
    
    else:
        return abort( 403 )

@app.route( baseurl + '/template/tenprint/<id>/set/zones', methods = [ "POST" ] )
@login_required
def update_zone_coordinates( id ):
    """
        Update the segments zones coordinates in the database.
    """
    id = int( 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 " + coordinate + " = %s WHERE card = %s AND pc = %s"
                data = ( v, id, pc, )
                config.db.query( sql, data )
        
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
    
    else:
        return abort( 403 )

@app.route( baseurl + '/template/tenprint/<id>/delete/zone', methods = [ "POST" ] )
@login_required
def delete_zone_coordinates( 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, ( id, pc, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
    
    except:
        return jsonify( {
            'error': True
        } )

@app.route( baseurl + "/template/tenprint/<id>/set/<varname>", methods = [ "POST" ] )
@login_required
def update_tptemplate_var( id, varname ):
    """
        Update the name, country_code or displayed size variable for a tenprint template.
    """
    if not varname in [ "name", "country_code", "size_display" ]:
        return jsonify( {
            "error": True
        } )
    
    else:
        try:
            data = request.form.get( varname )
            data = str( data )
            
            sql = "UPDATE tenprint_cards SET " + varname + " = %s WHERE id = %s"
            config.db.query( sql, ( data, id, ) )
            config.db.commit()
            
            return jsonify( {
                'error': False
            } )
            
        except:
            return jsonify( {
                'error': True
            } )

@app.route( baseurl + "/template/tenprint/<id>/set/hw", methods = [ "POST" ] )
@login_required
def update_tptemplate_hw( id ):
    """
        Set the image size of a template.
    """
    try:
        h = request.form.get( "height" )
        w = request.form.get( "width" )
        
        h = float( h )
        w = float( w )
        
        sql = "UPDATE tenprint_cards SET height = %s, width = %s WHERE id = %s"
        config.db.query( sql, ( h, w, id, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
        
    except:
        return jsonify( {
            'error': True
        } )

@app.route( baseurl + "/template/tenprint/<id>/set/resolution" )
@login_required
def update_tptemplate_res( id ):
    """
        Update the resolution of the image for a template.
    """
    try:
        res = request.form.get( "resolution" )
        res = float( res )
        
        sql = "UPDATE tenprint_cards SET image_resolution = %s WHERE id = %s"
        config.db.query( sql, ( res, id, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
        
    except:
        return jsonify( {
            'error': True
        } )

################################################################################
#    PiAnoS API

@app.route( baseurl + '/pianos_api' )
@admin_required
def pianos_actions():
    """
        Serve the page with all actions related to the dedicated PiAnoS server.