#' Cell-by-Cell data Query
#'
#' Get Cell-by-Cell segmentation data stored in MDCStore database given uniqueID
#'
#' @import RODBC
#'
#' @param SERVER the odbc connexion to MDCStore database
#' @param ID the identifiant for connexion
#' @param PWD the password for connexion
#' @param MeasurementID the uniqueID created by MetaXpress/PowerCore segmentation
#' @param getsite Shall siteID be added to data set ?
#' @param TimeCourse Is it a timecourse experiment?
#'
#' @return A data.frame with cell-by-cell data stemming from MetaXpress/PowerCore segmentation
#'
#' @author Allan Sauvat, \email{allan.sauvat@gustaveroussy.fr}
#' @seealso \code{\link{GetMDCInfo}}
#'
#' @examples
#'#Not running
#'DB = GetMDCInfo(SERVER = 'MDCStore',ID = 'sa', PWD = '***')
#'UniqueIDs=DB$MeasurementID[grep(paste0(1500:1515,collapse='|'),DB$PlateID)]) #Get uniquesIDs from plate 1500 to 1515
#'MyData = lapply(UniqueIDs,function(x)GetMDCData(SERVER='YODA-SERVER',MeasurementID=x)) #Create a list that contains cellular data from each plate
#'
#' @export
#'
GetMDCData = function(SERVER = 'MDCStore',ID='moldev', PWD='moldev',MeasurementID, getsite = T, TimeCourse = F){
if(missing(MeasurementID)){return('Please give the measurement ID !')}
#======================================================
ch = odbcConnect(SERVER, uid = ID, pwd = PWD)
#======================================================
TAB = sqlQuery(ch,"SELECT * FROM information_schema.tables WHERE table_name LIKE '%MIC%'")
TAB$UNIQUE_ID = as.numeric(gsub('MIC','',TAB$TABLE_NAME))
TAB = TAB[order(TAB$UNIQUE_ID),,drop=F]
if(!any(TAB$UNIQUE_ID==MeasurementID)){return('No matching analysis in database')}
#=====================================================
RES = sqlFetch(ch,TAB$TABLE_NAME[which(TAB$UNIQUE_ID==MeasurementID)])
PLATE_ID = RES$PLATE_ID[1]
#=====================================================
if(getsite){SITES = sqlQuery(ch,paste('SELECT SITE_ID, X_POSITION, Y_POSITION FROM SITE WHERE PLATE_ID = ',PLATE_ID))}
if(TimeCourse){
TIME=data.frame(t(sapply(unique(RES$SERIES_ID),function(x)sqlQuery(ch,paste('SELECT SERIES_ID, T_INDEX, T_MICROSECONDS FROM SERIES_INFO WHERE SERIES_ID IN (',x,')')))))
RES = merge(RES, TIME, by = 'SERIES_ID' )
}
#=====================================================
FEAT = sqlQuery(ch,paste0("SELECT COLUMN_NAME,MEAS_NAME FROM TABLE_COLUMNS WHERE COLUMN_NAME LIKE '%MDCS%' AND ENTITY_ID=",MeasurementID))
rownames(FEAT)=FEAT$COLUMN_NAME
odbcClose(ch)
#=====================================================
colnames(RES)[grep('MDCS',colnames(RES))] = gsub('Cell: ','',FEAT[grep('MDCS',colnames(RES),value=T),'MEAS_NAME'])
#=====================================================
RES=RES[,!grepl("SERIES_ID|PRINTED_SPOT_ID|SUBSTANCE_NAME|SETTING_ID|RUN_ID|INSTANCE|WELL_X|WELL_Y",colnames(RES))] #Useless columns...
Types=which(sapply(colnames(RES),function(x)typeof(RES[,x]))!='double')
Types=Types[4:length(Types)]
if(length(Types)>1){
RES[,Types]=apply(RES[,Types],2,function(x)as.numeric(as.character(x)))
}else{RES[,Types]=as.character(RES[,Types])}
colnames(RES)[1:4] = c('Cell.ID','Site.ID','Well.ID','Plate.ID')
if(TimeCourse){
colnames(RES)=gsub('T_INDEX','TimePoint',colnames(RES))
colnames(RES)=gsub('T_MICROSECONDS','Time.Microseconds',colnames(RES))
RES=RES[order(RES$TimePoint),,drop=F]
}
RES=RES[order(RES$Well.ID), , drop = FALSE]
if(length(RES$ObjectID)!=0){
RES = RES[!is.na(RES$ObjectID),]
}
#====================================================
if(getsite){
RES=merge(RES,SITES,by.x='Site.ID',by.y='SITE_ID')
colnames(RES)[colnames(RES)=='X_POSITION']='Site.X'
colnames(RES)[colnames(RES)=='Y_POSITION']='Site.Y'
Xmax = max(SITES$X_POSITION)
Ymax = max(SITES$Y_POSITION)
Nsites = Xmax*Ymax
RES$Site.ID=RES$Site.X+Xmax*(RES$Site.Y-1)
RES=RES[order(RES$Well.ID,RES$Site.ID),,drop=F]
}
#====================================================
return(RES)
}
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.