library(RMySQL)
con <- dbConnect(MySQL(), dbname="mestrado", user='root', password='')

#ex 1
sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 1 and exercicio_id = 1"

ex_com_tdd <- dbGetQuery(con, sql)

sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 0 and exercicio_id = 1"

ex_sem_tdd <- dbGetQuery(con, sql)

t1 = wilcox.test(ex_com_tdd$efferent_coupling, ex_sem_tdd$efferent_coupling, paired = FALSE)

#ex 2
sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 1 and exercicio_id = 2"

ex_com_tdd <- dbGetQuery(con, sql)

sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 0 and exercicio_id = 2"

ex_sem_tdd <- dbGetQuery(con, sql)

t2 = wilcox.test(ex_com_tdd$efferent_coupling, ex_sem_tdd$efferent_coupling, paired = FALSE)

# ex 3
sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 1 and exercicio_id = 3"

ex_com_tdd <- dbGetQuery(con, sql)

sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 0 and exercicio_id = 3"

ex_sem_tdd <- dbGetQuery(con, sql)

t3 = wilcox.test(ex_com_tdd$efferent_coupling, ex_sem_tdd$efferent_coupling, paired = FALSE)

# ex 4

sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 1 and exercicio_id = 4"

ex_com_tdd <- dbGetQuery(con, sql)

sql <- "select * from (select co.participante_id, co.combinacao_id, exercicio_id, efferent_coupling, (c.exercicio1 = co.exercicio_id and co.combinacao_id mod 2 = 1) + (c.exercicio2 = co.exercicio_id and co.combinacao_id mod 2 = 0) > 0 as fez_tdd from coupling co inner join participantes p on p.id = co.participante_id inner join participantes_combinacoes pc on pc.participante_id = p.id inner join combinacoes c on c.id = pc.combinacao_id where empresa = 'USP') result where fez_tdd = 0 and exercicio_id = 4"

ex_sem_tdd <- dbGetQuery(con, sql)

t4 = wilcox.test(ex_com_tdd$efferent_coupling, ex_sem_tdd$efferent_coupling, paired = FALSE)

dbDisconnect(con)