#spaces.py
'''
AlgoHack Genetic Algorithm for University Semaster Planning
Version 0.03 2018
Niranjan Meegammana Shilpasayura.org
'''

import xdb

def crt_spaces_table(cursor,drop=False):
    if (drop):
        sql="DROP TABLE IF EXISTS spaces;"
        success, count=xdb.runSQL(cursor, sql)

    sql='''CREATE TABLE IF NOT EXISTS spaces (
    spid INTEGER PRIMARY KEY AUTOINCREMENT,
    name varchar(30),
    sptype INTEGER,
    fitness INTEGER,
    gid INTEGER DEFAULT 0,
    semid INTEGER DEFAULT 0)
    '''
    success, count=xdb.runSQL(cursor, sql)
    return success
    
def insert_spaces(cursor,nlect,nlabs,gid,semid, delay):
    # nlabs is number of labs
    # nlecs is number of lecture halls
    # if gid =0 common for all groups else dedicated
    # if semid=0 common for all semasters else dedicated
    sql="SELECT * FROM spaces LIMIT 1";
    success, count=xdb.runSQL(cursor, sql)
    
    if (count > 0):
        print("spaces table: Records exist")
        return False, 0

    sqls=""
    fitness=1
    for i in range (nlect):
        name="Lect Hall " + str(i+1)
        sptype=1
        sqls=sqls +'INSERT INTO spaces (name,sptype,fitness,gid,semid) VALUES ('+ '"{}",{}, {},{},{}'.format(name, sptype,fitness,gid,semid) +');'

    for i in range (nlabs):
        name="Lab " + str(i+1)
        sptype=2
        sqls=sqls +'INSERT INTO spaces (name,sptype,fitness,gid,semid) VALUES ('+ '"{}",{}, {},{},{}'.format(name, sptype,fitness,gid,semid) +');'

    success, count=xdb.runSQL_stmts(cursor, sqls,delay)
    return success, count
    
if __name__ == "__main__":
    delay=0.05
    conn=xdb.opendb('genetic56.db')
    cursor =conn.cursor() # create a cursor object
    success=crt_spaces_table(cursor, True) # create spaces table
    #dedicated lecture hall, lab for group and semaster
    success, count =insert_spaces(cursor,1,1,1,1,delay) # generate records
    xdb.commit(conn)
    xdb.closedb(conn)

[((23, 19, 23, 42), 'xdb.runSQL', 'xdb.runSQL', ({(23, 30, 23, 36): 'cursor', (23, 38, 23, 41): 'sql'}, {}), '(cursor, sql)', False, 'import xdb\n'), ((32, 19, 32, 42), 'xdb.runSQL', 'xdb.runSQL', ({(32, 30, 32, 36): 'cursor', (32, 38, 32, 41): 'sql'}, {}), '(cursor, sql)', False, 'import xdb\n'), ((50, 19, 50, 55), 'xdb.runSQL_stmts', 'xdb.runSQL_stmts', ({(50, 36, 50, 42): 'cursor', (50, 44, 50, 48): 'sqls', (50, 49, 50, 54): 'delay'}, {}), '(cursor, sqls, delay)', False, 'import xdb\n'), ((55, 9, 55, 35), 'xdb.opendb', 'xdb.opendb', ({(55, 20, 55, 34): '"""genetic56.db"""'}, {}), "('genetic56.db')", False, 'import xdb\n'), ((60, 4, 60, 20), 'xdb.commit', 'xdb.commit', ({(60, 15, 60, 19): 'conn'}, {}), '(conn)', False, 'import xdb\n'), ((61, 4, 61, 21), 'xdb.closedb', 'xdb.closedb', ({(61, 16, 61, 20): 'conn'}, {}), '(conn)', False, 'import xdb\n'), ((13, 23, 13, 46), 'xdb.runSQL', 'xdb.runSQL', ({(13, 34, 13, 40): 'cursor', (13, 42, 13, 45): 'sql'}, {}), '(cursor, sql)', False, 'import xdb\n')]