module.py

    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" ),
        envtype = envtype
    )

@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,
        envtype = envtype
    )

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

@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.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
        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" ),
            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 ):
    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 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():
    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 ):
    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():
    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 ):
    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 ):
    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 ):
    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 ):
    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,
            admin = int( session[ 'account_type' ] ) == 1,
            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 ):
    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 ):
    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 ):
    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 ):
    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 ):
    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():
    return render_template( 
        "PiAnoS/actions.html",
        baseurl = baseurl,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        envtype = envtype
    )

@app.route( baseurl + '/pianos_api/add_user/all' )
@admin_required
def pianos_update_all_accounts():
    action = do_pianos_update_all_accounts()
    if action:
        return jsonify( {
            'error': False
        } )
    
    else:
        return jsonify( {
            'error': True
        } )

def do_pianos_update_all_accounts():
    try:
        sql = """
            SELECT users.username, users.password, account_type.name as g
            FROM users
            LEFT JOIN account_type ON users.type = account_type.id
        """
        for user in config.db.query_fetchall( sql ):
            username, h, group_name = user
            groupid = config.pianosdb.create_group( group_name )
            config.pianosdb.create_user( username = username, hash = h, groupid = groupid )
            config.pianosdb.reset_user( username, hash = h )
        
        config.pianosdb.commit()
        
        return True
    
    except:
        return False

################################################################################
#    Home page

@app.route( baseurl + '/' )
@login_required
def home():
    return render_template( 
        "index.html",
        baseurl = baseurl,
        admin = int( session[ 'account_type' ] ) == 1,
        js = config.cdnjs,
        css = config.cdncss,
        session_timeout = config.session_timeout,
        account_type = session.get( "account_type", None ),
        session_security_key = session.get( "session_security_key" ),
        envtype = envtype
    )

################################################################################
#    Main startup

gpg = gnupg.GPG( **config.gpg_options )
 
for file in os.listdir( config.keys_folder ):
    with open( config.keys_folder + "/" + file, "r" ) as fp:
        gpg.import_keys( fp.read() )

if __name__ == '__main__':
    app.run( debug = debug, host = "0.0.0.0", threaded = True )