# TODO: Add comment
# 
# Author: E.Korsching Sep 17, 2014
###############################################################################



## join database tables



# join brca1&2 and br8panel
# values:
# br8			sample_id (1..161) var_id (1..75728) batch (1..5)
# brca1&2		sample_id (1..473) var_id (1..32447) batch (1..9)
#
# strategy:
# -export in both cases sample and variations table (sqlitebrowser)
# -import into R
# -adust in br8 tables: sample_id (+500 in sample&variations) var_id (+33000 in variations) batch (+10 in sample)
# -join sample-sample and variations-variations tables
# -export both resulting tables
# -re-import in a new sqlite database (sqlitebrowser)


# import
tmp.brca12.sample <- read.table("/home/korschi/br2sample.txt", header=T, sep=",", dec=".", stringsAsFactors=F)
tmp.brca12.variations <- read.table("/home/korschi/br2variations.txt", header=T, sep=",", dec=".", stringsAsFactors=F)
tmp.br8.sample <- read.table("/home/korschi/br8sample.txt", header=T, sep=",", dec=".", stringsAsFactors=F)
tmp.br8.variations <- read.table("/home/korschi/br8variations.txt", header=T, sep=",", dec=".", stringsAsFactors=F)

# check
tmp.brca12.sample[1:10,]
tmp.brca12.variations[1:10,]
tmp.br8.sample[1:10,]
tmp.br8.variations[1:10,]

sapply(tmp.brca12.sample,mode)
#tmp.brca12.sample <- transform(tmp.brca12.sample,
#		sample_id = as.numeric(sample_id),
#		batch = as.numeric(batch) )

sapply(tmp.brca12.variations,mode)
sapply(tmp.br8.sample,mode)
sapply(tmp.br8.variations,mode)


# adjust
tmp.br8.sample[,"sample_id"] <- tmp.br8.sample[,"sample_id"] + 500
tmp.br8.sample[,"batch"] <- tmp.br8.sample[,"batch"] + 10

tmp.br8.variations[,"sample_id"] <- tmp.br8.variations[,"sample_id"] + 500
tmp.br8.variations[,"var_id"] <- tmp.br8.variations[,"var_id"] + 33000

# join
tmp.br2.8.4.sample <- rbind(tmp.brca12.sample,tmp.br8.sample)
tmp.br2.8.4.variations <- rbind(tmp.brca12.variations,tmp.br8.variations)


# export
write.table(tmp.br2.8.4.sample, file="/home/korschi/br2.8.4.sample.txt", append=F,
		quote=T, sep=",", eol="\n", na="NA", dec=".", row.names=F, col.names=T)
write.table(tmp.br2.8.4.variations, file="/home/korschi/br2.8.4.variations.txt", append=F,
		quote=T, sep=",", eol="\n", na="NA", dec=".", row.names=F, col.names=T)