library(RMySQL)
library(RColorBrewer)
library(MASS)
mypalette<-c(brewer.pal(8,"Blues")[8:1],brewer.pal(8,"Greens"))
con <- dbConnect(MySQL(), user="user", password="password", dbname= "pride", host = "localhost", port = 4057)
query1 = "select p.name, p.cv_label, p.accession, count(distinct(e.accession)), cast(group_concat(distinct(e.experiment_id) order by 1) as char)
from mzdata_param as p, pride_experiment as e, mzdata_mz_data mz
where mz.mz_data_id= e.mz_data_id
AND p.parent_element_fk=mz.mz_data_id
and p.param_type=5
and cv_label='NEWT'
group by p.name
order by 1"
dataM = dbGetQuery(con, query1)
dbDisconnect(con)

species = dataM[,1]
identVec = vector()
pepVec = vector()
specVec = vector()
for(i in 1:length(species)){
	con1 <- dbConnect(MySQL(), user="user", password="password", dbname= "pride", host = "localhost", port = 4057)
	experimentList = dataM[i,5]
	identificationsQ = paste("select count(identification_id) from pride_identification
	where experiment_id IN ('",experimentList,"')", sep = "")
	identCount =dbGetQuery(con1, identificationsQ)
	peptidesQ = paste("select count(peptide_id)
	from pride_identification i, pride_peptide p
	where i.identification_id = p.identification_id
	and i.experiment_id IN ('",experimentList,"')", sep = "")
	pepCount = dbGetQuery(con1, peptidesQ)
	specQ = paste("select count(*)
	from mzdata_spectrum as s, mzdata_mz_data as m, pride_experiment as e
	where s.mz_data_id=m.mz_data_id
	and m.accession_number = e.accession
	and e.experiment_id IN ('",experimentList,"')", sep = "")
	specCount = dbGetQuery(con1,specQ)
	dbDisconnect(con1)
	pepVec = append(pepVec,as.numeric(pepCount), after = length(pepVec))
	identVec = append(identVec,as.numeric(identCount), after = length(identVec))
	specVec = append(specVec,as.numeric(specCount), after = length(specVec))
}

dataM = cbind(dataM, pepVec)
dataM =  cbind(dataM, identVec)
dataM = cbind(dataM, specVec)
write.matrix(dataM,"C:\\Documents and Settings\\joe\\My Documents\\MOReview\\MOdata_withSpecies.csv", sep = "\t")
# need to correalte species with kingdoms
# need to correlate model organisms or not