module.py

    } )

@app.route( baseurl + '/qrcode' )
def send_qrcode():
    if 'username' in session:
        qrcode_value = 'otpauth://totp/ICNML%20' + session[ 'username' ] + '?secret=' + get_secret()
    else:
        qrcode_value = 'otpauth://totp/ICNML?secret=' + get_secret()
    
    img = qrcode.make( qrcode_value )
    
    temp = StringIO()
    img.save( temp, format = "png" )
    temp.seek( 0 )
    
    return send_file( temp, mimetype = 'image/png' )
    
@app.route( baseurl + '/user_qrcode' )
@login_required
def serve_qrcode():
    return render_template( 
        "qrcode.html",
        baseurl = baseurl,
        secret = get_secret(),
        js = config.cdnjs,
        css = config.cdncss
    )

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

def do_decrypt( data ):
    try:
        return AESCipher( session[ 'password' ] ).decrypt( data )
    except:
        return None

def do_encrypt( data ):
    return AESCipher( session[ 'password' ] ).encrypt( data )

################################################################################
#    File upload

@app.route( baseurl + '/upload', methods = [ 'GET', 'POST' ] )
@login_required
def upload_file():
    upload_type = request.form.get( "upload_type", None )
    
    if upload_type == None:
        return jsonify( {
            'error': True,
            'msg': 'Must specify a file type to upload a file'
        } )
    
    if request.method == 'POST':
        if 'file' not in request.files:
            return jsonify( { 'error': True, 'msg': 'No file in the POST request' } )
        
        elif 'upload_id' not in request.form:
            return jsonify( { 'error': True, 'msg': 'No upload_id' } )
        
        else:
            try:
                upload_id = request.form.get( "upload_id" )
                sql = "SELECT id FROM submissions WHERE uuid = %s"
                r = config.db.query( sql, ( upload_id, ) )
                upload_id = r.fetchone()[ 'id' ]
                
            except:
                return jsonify( {
                    'error': True
                } )
            
            file = request.files[ 'file' ]
            filename = do_encrypt( file.filename )
            file_uuid = str( uuid4() )
            
            fp = StringIO()
            
            file.save( fp )
            file_size = fp.tell()
            
            fp.seek( 0 )
            
            if upload_type in [ 'latent_target', 'latent_incidental', 'tenprint_card_front', 'tenprint_card_back' ]:
                img = Image.open( fp )
                img_format = img.format
                width, height = img.size
                res = int( img.info[ 'dpi' ][ 0 ] )
                img = rotate_image_upon_exif( img )
                
                buff = StringIO()
                img.save( buff, format = img_format )
                buff.seek( 0 )
                file_data = buff.getvalue()
                
                if upload_type in [ 'tenprint_card_front', 'tenprint_card_back' ]:
                    create_thumbnail( file_uuid, img )
            
            else:
                file_data = fp.getvalue()
            
            file_data = base64.b64encode( file_data )
            
            if upload_type == "consent_form":
                file_data = gpg.encrypt( file_data, config.gpg_key )
                file_data = str( file_data )
            
            sql = "SELECT id FROM files_type WHERE name = %s"
            upload_type_id = config.db.query( sql, ( upload_type, ) ).fetchone()[ 0 ]
            
            sql = "INSERT INTO files ( folder, creator, filename, type, format, size, width, height, resolution, uuid, data ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s )"
            data = ( upload_id, session[ 'user_id' ], filename, upload_type_id, img_format, file_size, width, height, res, file_uuid, file_data )
            config.db.query( sql, data )
            config.db.commit()
            
            return jsonify( {
                'error': False,
                'filename': filename,
                'filesize': file_size,
                'uuid': file_uuid
            } )
    
    else:
        return abort( 403 )

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

@app.route( baseurl + '/submission/new' )
@login_required
def submission_new():
    return render_template( 
        "submission/new.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        session_security_key = session.get( "session_security_key" )
    )

@app.route( baseurl + '/submission/do_new', methods = [ "GET", "POST" ] )
@login_required
def submission_do_new():
    email = request.form.get( "email", False )
    
    if email:
        # Check for duplicate base upon the email data
        sql = "SELECT id, email_hash FROM submissions WHERE submitter_id = %s"
        r = config.db.query( sql, ( session[ 'user_id' ], ) )
        for case in r.fetchall():
            if pbkdf2( email, case[ 'email_hash' ] ):
                return jsonify( {
                    'error': True,
                    'msg': "Email already used"
                } )
                
                break
        
        else:
            # Insert the new donor
            id = str( uuid4() )
            
            email_aes = do_encrypt( email )
            email_hash = pbkdf2( email, random_data( 50 ), 50000 )
            
            upload_nickname = request.form.get( "upload_nickname", None )
            upload_nickname = do_encrypt( upload_nickname )
            submitter_id = session[ 'user_id' ]
            
            status = "pending"
            
            sql = "INSERT INTO submissions ( uuid, email_aes, email_hash, nickname, status, submitter_id ) VALUES ( %s, %s, %s, %s, %s, %s ) RETURNING id"
            data = ( id, email_aes, email_hash, upload_nickname, status, submitter_id )
            
            config.db.query( sql, data )
            config.db.commit()
            
            return jsonify( {
                'error': False,
                'id': id
            } )
        
    else:
        return jsonify( {
            'error': True,
            'msg': "Email not provided"
        } )

@app.route( baseurl + '/submission/<id>' )
@login_required
def submission_update( id ):
    try:
        sql = "SELECT email_aes as email, nickname, created_time FROM submissions WHERE submitter_id = %s AND uuid = %s"
        r = config.db.query( sql, ( session[ 'user_id' ], id ) )
        user = r.fetchone()
        
        for key in [ 'email', 'nickname' ]:
            user[ key ] = do_decrypt( user[ key ] )
        
        return render_template( 
            "submission/update.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            upload_id = id,
            session_security_key = session.get( "session_security_key" ),
            **user
        )
    
    except:
        return jsonify( {
            'error': True,
            'msg': "Case not found"
        } )

@app.route( baseurl + '/submission/<id>/set/nickname', methods = [ 'POST' ] )
@login_required
def submission_update_nickname( id ):
    nickname = request.form.get( "nickname", None )
    
    if nickname != None and len( nickname ) != 0:
        try:
            nickname = do_encrypt( nickname )
            
            sql = "UPDATE submissions SET nickname = %s WHERE uuid = %s"
            config.db.query( sql, ( nickname, id, ) )
            config.db.commit()
            
            return jsonify( {
                'error': False
            } )
        
        except:
            return jsonify( {
                'error': True,
                'message': "DB error"
            } )
    
    else:
        return jsonify( {
            'error': True,
            'message': "No new nickname in the POST request"
        } )

@app.route( baseurl + '/submission/list' )
@login_required
def submission_list():
    sql = "SELECT * FROM submissions WHERE submitter_id = %s ORDER BY created_time DESC"
    r = config.db.query( sql, ( session[ 'user_id' ], ) )
    q = r.fetchall()
    
    donors = []
    for donor in q:
        donors.append( {
            'id': donor.get( "id", None ),
            'email': do_decrypt( donor.get( "email_aes", None ) ),
            'nickname': do_decrypt( donor.get( "nickname", None ) ),
            'uuid': donor.get( "uuid", None )
        } )
    
    return render_template( 
        "submission/list.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        donors = donors,
        session_security_key = session.get( "session_security_key" )
    )

@app.route( baseurl + '/submission/<id>/latent/list' )
@app.route( baseurl + '/submission/<id>/latent/list/<ltype>' )
@login_required
def submission_latent_list( id, ltype = 'all' ):
    if ltype in [ 'target', 'incidental', 'all' ]:
        sql = "SELECT id, nickname FROM submissions WHERE uuid = %s AND submitter_id = %s"
        r = config.db.query( sql, ( id, session[ 'user_id' ], ) )
        case_id, nickname = r.fetchone()
        nickname = do_decrypt( nickname )
        
        sql = """
            SELECT files.uuid, files.filename, files.size, files.creation_time
            FROM files
            LEFT JOIN files_type ON files.type = files_type.id
            WHERE folder = %s AND
        """
        if ltype == "target":
            sql += " files_type.name = 'latent_target'"
        elif ltype == "incidental":
            sql += " files_type.name = 'latent_incidental'"
        elif ltype == "all":
            sql += " ( files_type.name = 'latent_target' OR files_type.name = 'latent_incidental' )"
        
        sql += " ORDER BY files.id DESC"
        r = config.db.query( sql, ( case_id, ) )
        files = r.fetchall()
        
        for i, v in enumerate( files ):
            v[ 'filename' ] = do_decrypt( v[ 'filename' ] )
            v[ 'size' ] = round( ( float( v[ 'size' ] ) / ( 1024 * 1024 ) ) * 100 ) / 100
        
        return render_template( 
            "submission/latent_list.html",
            baseurl = baseurl,
            js = config.cdnjs,
            css = config.cdncss,
            session_timeout = config.session_timeout,
            submission_id = id,
            latent_type = ltype,
            files = files,
            nickname = nickname,
            session_security_key = session.get( "session_security_key" )
        )
    
    else:
        return abort( 403 )

@app.route( baseurl + '/submission/<id>/latent/<lid>' )
@login_required
def submission_latent( id, lid ):
    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,
            files_type.name as file_type
        
        FROM files
        LEFT JOIN files_type ON files.type = files_type.id
        WHERE
            folder = %s AND
            files.uuid = %s
    """
    
    r = config.db.query( sql, ( submission_id, lid, ) )
    file = r.fetchone()
    file[ 'size' ] = round( 100 * float( file[ 'size' ] ) / ( 1024 * 1024 ) ) / 100
    file[ 'filename' ] = do_decrypt( file[ 'filename' ] )
    file[ 'note' ] = do_decrypt( file[ 'note' ] )
    file[ 'file_type' ] = file[ 'file_type' ].replace( "latent_", "" ) 
    
    return render_template( 
        "submission/latent.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        submission_id = id,
        nickname = nickname,
        file = file,
        session_security_key = session.get( "session_security_key" )
    )

@app.route( baseurl + '/submission/<id>/latent/<lid>/pfsp' )
@login_required
def submission_latent_pfsp( id, lid ):
    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,
            files_type.name as file_type
        
        FROM files
        LEFT JOIN files_type ON files.type = files_type.id
        WHERE
            folder = %s AND
            files.uuid = %s
    """
    
    r = config.db.query( sql, ( submission_id, lid, ) )
    file = r.fetchone()
    file[ 'size' ] = round( 100 * float( file[ 'size' ] ) / ( 1024 * 1024 ) ) / 100
    file[ 'filename' ] = do_decrypt( file[ 'filename' ] )
    file[ 'note' ] = do_decrypt( file[ 'note' ] )
    file[ 'file_type' ] = file[ 'file_type' ].replace( "latent_", "" ) 
    
    return render_template( 
        "submission/latent_pfsp.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        submission_id = id,
        nickname = nickname,
        file = file,
        pfsp_zones = pfsp.zones,
        session_security_key = session.get( "session_security_key" )
    )

@app.route( baseurl + '/submission/<id>/latent/<lid>/delete' )
@login_required
def submission_latent_delete( id, lid ):
    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' ], 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 ):
    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" )

@app.route( baseurl + '/image/segment/<tid>/<pc>' )
@login_required
def image_segment_serve( tid, pc ):
    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" ):
    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 ):
    if db == "files_segments" and isinstance( id, tuple ):
        tp, pc = id
        sql = "SELECT data, uuid FROM " + db + " WHERE tenprint = %s AND pc = %s"
        p = ( tp, pc, )
    
    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 ):
    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 ):
    d = do_img_info( id )
    
    if d != None:
        return jsonify( d )
    else:
        return abort( 404 )

def do_img_info( id ):
    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 ):
    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 ):
    ret = do_image_tenprint_segmentation( id )
    
    return jsonify( {
        'error': False,
        'data': ret
    } )

def do_image_tenprint_segmentation( id ):
    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 ):
    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 )
        ORDER BY creation_time DESC
    """
    
    r = config.db.query( sql, ( submission_id, ) )
    q = r.fetchall()
    
    tenprint_cards = {
        '1': [],
        '2': []
    }
    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' ],
        submission_id = id,
        nickname = nickname,
        session_security_key = session.get( "session_security_key" )
    )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>' )
@login_required
def submission_tenprint( id, tid ):
    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()
    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
    )

@app.route( baseurl + '/submission/<id>/tenprint/<file>/set/template', methods = [ 'GET', 'POST' ] )
@login_required
def submission_tenprint_set_template( id, file ):
    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 ):
    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 ):
    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" ),
    )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>/segment/<pc>' )
@login_required
def submission_segment( id, tid, pc ):
    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' ] )
        tp_type = tp_file[ 'type' ]
        
        sql = "SELECT name FROM pc WHERE id = %s"
        pc_name = config.db.query( sql, ( pc, ) ).fetchone()[ 0 ]
        
        sql = "SELECT gp.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
        elif pc == 25:
            next_pc = 27
        else:
            next_pc = None
        
        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" )
        )

@app.route( baseurl + '/submission/<id>/tenprint/<tid>/segment/<pc>/set/gp', methods = [ 'POST' ] )
@login_required
def submission_segment_set_gp( id, tid, 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 = "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():
    sql = "SELECT id, country_code, name FROM tenprint_cards"
    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
    )

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

@app.route( baseurl + '/template/tenprint/new/<id>/images' )
@admin_required
def template_tenprint_new_images( id ):
    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
    )

@app.route( baseurl + '/template/tenprint/new/insert', methods = [ 'POST' ] )
@admin_required
def template_tenprint_new_do():
    name = request.form.get( "name" )
    country_code = request.form.get( "country_code" )
    
    print name, 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/upload_image', methods = [ 'POST' ] )
@admin_required
def template_tenprint_new_uploadimage():
    face = request.form.get( "card_face" )
    id = request.form.get( "card_id" )
    
    if face in [ "front", "back" ]:
        data = request.files[ 'file' ]
        
        img = Image.open( data )
        width, height = img.size
        
        fp = StringIO()
        img.save( fp, format = "JPEG" )
        fp.seek( 0 )
        data = fp.getvalue()
        data = base64.b64encode( data )
        
        sql = "UPDATE tenprint_cards SET image_" + face + " = %s, image_" + face + "_width = %s, image_" + face + "_height = %s, image_format = %s WHERE id = %s"
        config.db.query( sql, ( data, width, height, "JPEG", id, ) )
        config.db.commit()
        
        return jsonify( {
            'error': False
        } )
    
    else:
        return abort( 403 )

def get_tenprint_template_zones( id, t ):
    sql = """
        SELECT