import psycopg2

# Numero de viagens que saem do stand <stand>
def numFromStand(conn, stand):
    select_query = """
        SELECT SUM(num_viagens) FROM taxi_services_T as t, local as l, stand as s
        WHERE t.local_ini_id = l.local_id AND
              l.stand_id = s.stand_id AND
              s.name = '%s'
    """

    cur = conn.cursor()
    cur.execute(select_query % stand)
    res = cur.fetchone()

    if res == None:
        return 0
    return int(res[0])

# Histograma das horas
def histHours(conn):
    select_query = #"""
#        SELECT t.hora, SUM(num_viagens) as nviagens FROM taxi_services_T, tempo
#        WHERE t.tempo_id = tempo_id
#        GROUP BY t.hora
 #   """
    """SELECT tmp, place, MAX(num_viagens) FROM(
           SELECT tempo(dia, mes) as tmp, local(local_id) as place, num_viagens FROM taxi_services_T, tempo
            WHERE local_id = local_id_in) AS aux
GROUP BY tmp, place;"""

    cur = conn.cursor()
    cur.execute(select_query)
    return cur.fetchall()

# Cross-Tabulation: soma de viagens/tempo total por taxi a comecar em cada localizacao
def maxMovement(conn):
    select_query = """
        SELECT taxi_id, 'ALL', 'ALL', local_id_in, 'ALL', SUM(num_viagens), SUM(tempo_total) FROM taxi_services_T, local as l
        WHERE local_ini_id = l.local_id AND
              l.freguesia = '%s'
        GROUP BY taxi_id, local_id_in;
    """

if __name__ == '__main__':
    conn = None
    try:
        # connect to the PostgreSQL server
        conn = psycopg2.connect(host="127.0.0.1",database="guest", user="guest", password="")


    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()

[up201207833@ssh trab1]$ ls
tabdcon.py  tabdcon.py~  tabd_fill.py  tabd_queries.py  '#tabdt1.py#'  tabdt1.py~
[up201207833@ssh trab1]$ cat tabd_fill.py

import psycopg2, datetime
#from config import config

def create_tables(conn):
    commands = (
        """
        DROP TABLE IF EXISTS taxi_services_T, tempo, stand, stand_geom, taxi, local, local_geom
        """,
        """
    CREATE TABLE stand (
        stand_id SERIAL NOT NULL,
        name VARCHAR NOT NULL,
        lotations INTEGER,
        PRIMARY KEY (stand_id)
        )
        """,
        """
    CREATE TABLE stand_geom (
        stand_id SERIAL NOT NULL,
        name VARCHAR NOT NULL,
        geom GEOMETRY,
        freguesia VARCHAR,
        concelho VARCHAR,
        lotations INTEGER,
        PRIMARY KEY (stand_id)
        )
        """
    )

    delete_duplicates_flag = False
    delete_duplicates = """
        DELETE FROM cont_freg_v5 AS t1
            WHERE EXISTS (
                SELECT 1 FROM cont_freg_v5 AS t2
                      WHERE t2.freguesia = t1.freguesia and
                            t2.concelho = t1.concelho and
                            t2.geom = t1.geom and
                            t2.gid > t1.gid
                 ); """

    cur = conn.cursor()
    # create table one by one
    ind = 1
    for command in commands:
        print('Execute command %d' % ind)
        ind += 1
        cur.execute(command)
    if delete_duplicates_flag:
        cur.execute(delete_duplicates)
    # commit the changes
    cur.close()
    print('Added new table')
    conn.commit()
    # close communication with the PostgreSQL database server

def fill_stands(conn):
    select_locals = """ SELECT name, location FROM taxi_stands """
    select_freguesia = """ SELECT freguesia, concelho FROM cont_freg_v5
    WHERE ST_Contains(ST_Transform(geom, 4326), '%s') LIMIT 1 """
    insert_stand = """ INSERT INTO stand(name, lotations) VALUES('%s', %d) RETURNING stand_id """
    insert_stand_geom = """ INSERT INTO stand_geom(name, lotations, concelho, freguesia, geom) VALUES('%s', %d, '%s', '%s', '%s') RETURNING stand_id """

    cur = conn.cursor()
    cur.execute(select_locals)
    local_rows = cur.fetchall()

    for row in local_rows:
        nome = row[0]
        cur.execute(insert_stand % (nome, 0))
        stand_id = cur.fetchone()[0]
        print(stand_id)
        cur.execute(select_freguesia % row[1])
        tup = cur.fetchone()
        print(tup)
        freg, concelho = tup
        cur.execute(insert_stand_geom % (nome, 0, concelho, freg, row[1]))
        #cur.execute(insert_local % (stand_id, freg, concelho))

    cur.close()
    # commit the changes
    print('Filled stand and location table')
    conn.commit()

def insert_local(cur, pt):
    local_geom = """ INSERT INTO local_geom(stand_id, freguesia, concelho, geom) VALUES(%d, '%s', '%s', '%s')"""
    local = """ INSERT INTO local(stand_id, freguesia, concelho) VALUES(%d, '%s', '%s')"""
    select_stand = """
        SELECT stand_id, freguesia, concelho FROM stand_geom
        WHERE ST_Distance(ST_Transform(geom, 4326), '%s') = (
            SELECT MIN(ST_Distance(ST_Transform(geom, 4326), '%s')) FROM stand_geom
        ) """

    cur.execute(select_stand % (pt,  pt))
    stand, freg, concelho = cur.fetchone()
    cur.execute(local_geom % (stand, freg, concelho, pt))
    cur.execute(local % (stand, freg, concelho))

def insert_taxi(cur, tx):
    insert_query = """ INSERT INTO taxi(license) VALUES (%d) """

    cur.execute(insert_query % tx)

def insert_tempo(cur, ts):
    insert_query = """ INSERT INTO tempo(hora, dia, mes) VALUES (%d, %d, %d) """

    m = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%m'))
    d = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%d'))
    h = int(datetime.datetime.fromtimestamp(int(ts)).strftime('%H'))
    cur.execute(insert_query % (h, d, m))

def fill_services(conn):
    drop_table = """ DROP TABLE IF EXISTS taxi_services_T, local, local_geom, taxi, tempo """
    create_tables = ("""
    CREATE TABLE local_geom (
        local_id SERIAL,
        stand_id INTEGER REFERENCES stand(stand_id),
        freguesia VARCHAR,
        concelho VARCHAR,
        geom GEOMETRY,
        PRIMARY KEY (local_id)
        )
        """,
        """
    CREATE TABLE local (
        local_id SERIAL,
        stand_id integer REFERENCES stand(stand_id),
        freguesia VARCHAR,
        concelho VARCHAR,
        PRIMARY KEY (local_id)
        )
        """,
        """
    CREATE TABLE taxi (
        taxi_id SERIAL NOT NULL,
        license INTEGER,
        PRIMARY KEY (taxi_id)
        )
        """,
        """
    CREATE TABLE tempo (
        tempo_id SERIAL NOT NULL,
        hora INTEGER,
        dia INTEGER,
        mes INTEGER,
        PRIMARY KEY (tempo_id)
        )
        """,
        """
    CREATE TABLE taxi_services_T (
        taxi_id INTEGER references taxi(taxi_id),
        tempo_ini_id INTEGER references tempo(tempo_id),
        tempo_fin_id INTEGER references tempo(tempo_id),
        local_id_in INTEGER references local(local_id),
        local_id_fim INTEGER references local(local_id),
        num_viagens INTEGER,
        tempo_total REAL,
        CONSTRAINT taxi_key PRIMARY KEY(taxi_id,tempo_ini_id,tempo_fin_id,local_id_in,local_id_fim)
        )
        """
    )
    select_services = """ SELECT initial_ts, final_ts, taxi_id, initial_point, final_point FROM taxi_services  """
    select_local = """ SELECT * FROM local_geom WHERE geom = '%s' """
    select_taxi = """ SELECT * FROM taxi WHERE license = %d """
    select_tempo = """ SELECT * FROM tempo WHERE hora = %d and dia = %d and mes = %d """
    insert_service = """ INSERT INTO taxi_services_T(taxi_id, tempo_ini_id, tempo_fin_id, local_id_in, local_id_fim, num_viagens, tempo_total) VALUES(%d, %d, %d, %d, %d, %d, %d) """
    select_service = """ SELECT num_viagens, tempo_total FROM taxi_services_T
        WHERE taxi_id = %d and
              tempo_ini_id = %d and
              tempo_fin_id = %d and
              local_id_in = %d and
              local_id_fim = %d """
    delete_service = """ DELETE FROM taxi_services_T
        WHERE taxi_id = %d and
              tempo_ini_id = %d and
              tempo_fin_id = %d and
              local_id_in = %d and
              local_id_fim = %d """

    cur = conn.cursor()
    cur.execute(drop_table)
    for table in create_tables:
        cur.execute(table)
    cur.execute(select_services)
    local_rows = cur.fetchall()
    print("Created table and selected services")

    for row in local_rows:
        i_ts, f_ts, t_id, i_pt, f_pt = row
        tm = f_ts - i_ts
        ntrips = 1

        cur.execute(select_local % i_pt)
        ires = cur.fetchone()
        if ires == None:
            insert_local(cur, i_pt)
            cur.execute(select_local % i_pt)
            ires = cur.fetchone()

        cur.execute(select_local % f_pt)
        fres = cur.fetchone()
        if fres == None:
            insert_local(cur, f_pt)
            cur.execute(select_local % f_pt)
            fres = cur.fetchone()

        cur.execute(select_taxi % t_id)
        tres = cur.fetchone()
        if tres == None:
            insert_taxi(cur, t_id)
            cur.execute(select_taxi % t_id)
            tres = cur.fetchone()

        itm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(i_ts)).strftime('%H %d %m').split()])
        cur.execute(select_tempo % itm_tup)
        itmres = cur.fetchone()
        if itmres == None:
            insert_tempo(cur, i_ts)
            cur.execute(select_tempo % itm_tup)
            itmres = cur.fetchone()

        ftm_tup = tuple([int(x) for x in datetime.datetime.fromtimestamp(int(f_ts)).strftime('%H %d %m').split()])
        cur.execute(select_tempo % ftm_tup)
        ftmres = cur.fetchone()
        if ftmres == None:
            insert_tempo(cur, f_ts)
            cur.execute(select_tempo % ftm_tup)
            ftmres = cur.fetchone()

        cur.execute(select_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0]))
        serres = cur.fetchone()
        if serres != None:
            cur.execute(delete_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0]))
            ntrips += int(serres[0])
            tm += int(serres[1])

        print("Inserting %d %d" % (ires[0], fres[0]))
        cur.execute(insert_service % (tres[0], itmres[0], ftmres[0], ires[0], fres[0], ntrips, tm))

    cur.close()
    conn.commit()

if __name__ == '__main__':
    conn = None
    try:
        # connect to the PostgreSQL server
        conn = psycopg2.connect(host="127.0.0.1",database="guest", user="guest", password="")

        #create_tables(conn)
        #fill_stands(conn)
        fill_services(conn)
    except (Exception, psycopg2.DatabaseError) as error:
        print(error)
    finally:
        if conn is not None:
            conn.close()