# TODO: Add comment
# 
# Author: E.Korsching Feb 11, 2014 - GPL v3
###############################################################################



####
# import xlsx -- all sheets in a -database file-


## these two objects must exist - if not create them; but only once (!) they are the memory
# list for all (!) imported files ever
log.db <- function(log.prefix=""){
	# import xlsx -- all sheets in a database file
	# create log files specific for a certain database
	# give only a file prefix - it will be created in the present work folder
	
	if(log.prefix==""){ cat("\n give a log data prefix - be collection specific ! \n"); return() }
	
	protocol.name <- paste(log.prefix,"protocol.db",sep="")
	list.name <- paste(log.prefix,"list.db",sep="")
	if(exists(x=protocol.name, where=1, mode="any", inherits=F)){
		cat("\n ",protocol.name," already exist - delete manually if you want to start from scratch \n")
		return()
	}
	if(exists(x=list.name, where=1, mode="any", inherits=F)){
		cat("\n ",list.name," already exist - delete manually if you want to start from scratch \n")
		return()
	}
	
	protocol.import.mass <- as.data.frame( matrix(0,1,4) )		# all xlsx workbooks
	names(protocol.import.mass) <- c("date","file.name","number.of.sheets","names.of.sheets")
	protocol.import.mass[1,] <- c(format(Sys.time(), format="%Y %m %d %H:%M"), "creation of list", 0, "--")
	# jobs done
	assign(x=protocol.name, value=protocol.import.mass, pos=1, inherits=F)
	# list of all R data objects ever (all xlsx work sheets)
	assign(x=list.name, value=list(creation=format(Sys.time(), format="%Y %m %d %H:%M")), pos=1, inherits=F)
}


import.in.db <- function(import.path.xlsx="", log.prefix="", DB.path.name=""){
	# import all xlsx files of a folder into R into one data.frame
	# set import file path -- folder should only contain xlsx files or folders  - but no recursion
	
	#ini
	options( java.parameters="-Xmx4g" )		# 4 gigabyte
			# memory leak : memory use growth constantly and will not be freed at the end of the function (java heap?)
			# gc() has no effect
	require(rJava)
	require(xlsxjars)
	require(xlsx)
	
	if(import.path.xlsx==""){ cat("\n give a relative path to a folder with xlsx files (sub folder will be not processed)\n"); return() }
	if(log.prefix==""){ cat("\n give a log data prefix - be collection specific ! \n"); return() }
	
	protocol.name <- paste(log.prefix,"protocol.db",sep="")
	list.name <- paste(log.prefix,"list.db",sep="")
	# we look for global variables only in the global environment
	if(!exists(x=protocol.name, where=1, mode="any", inherits=F)){
		cat("\n create ",protocol.name," object first - or think about its location\n")
		return()
	}
	if(!exists(x=list.name, where=1, mode="any", inherits=F)){
		cat("\n create ",list.name," object first - or think about its location\n")
		return()
	}
	
	if(DB.path.name==""){ cat("\n give a relative path and name to the database file \n"); return() }
	
	# get last import number and advance by one - if the first time give a '0'
	group.number <- get.batch.import.number(DB.path.name=DB.path.name)
	cat("\n group number : ", group.number)
	
	# make full import path
	import.path.xlsx <- paste(getwd(),import.path.xlsx,sep="")
	# create file name list
	all.files <- list.files(path=import.path.xlsx)		#  ! will always include folders
	all.files.len <- length(all.files)
	# therefore remove folders separately
	all.files.logi <- vector(mode="logical",length=all.files.len)
	for(i in 1:all.files.len){
		all.files.logi[i] <- file.info(paste(import.path.xlsx,"/",all.files[i],sep=""))$isdir
	}
	all.files <- all.files[!all.files.logi]		# get files
	all.files.len <- length(all.files)		# new length
	
	# create a second file name list - but non alpha-numeric characters replaced by '.'
	all.files.clean <- vector(mode="character", length=all.files.len)
	for(i in 1:all.files.len){
		all.files.clean[i] <- gsub("[^[:alnum:]]", ".", all.files[i])
	}
	
	### import all files and all sheets in the database
	counter.sheets <- 0
	counter.rows <- 0
	for(i in 1:all.files.len){
		cat("\n processing file number : ",i," / ",all.files.len," name: ",all.files[i])
		fp <- paste(import.path.xlsx, all.files[i], sep="/")
		wb.ref <- loadWorkbook(fp)
		anz.sheet <- wb.ref$getNumberOfSheets()
		counter.sheets <- counter.sheets + anz.sheet
		names.sheet <- names( getSheets(wb.ref) )
		
		if(anz.sheet>0){
			for(j in 1:anz.sheet){
				cat("\n processing sheet number : ",j," / ",anz.sheet," name: ",names.sheet[j])
				# construct object name
				obj.name <- paste(all.files.clean[i],names.sheet[j],sep=".")
				# make entry in log file
				assign(list.name,
						value=c( get(list.name, pos=1, inherits=T), c(obj.name, format(Sys.time(), format="%Y %m %d %H:%M")) ),
						pos=1, inherits=T)
				
				# import
				tmp2 <- read.xlsx2(file=fp, sheetIndex=j, as.data.frame=TRUE, header=TRUE, colClasses="character", stringsAsFactors=F)
				tmp2 <- tmp2[tmp2[,1]!="", ]	# test on empty lines and remove those
				tmp2.len <- nrow(tmp2)
				counter.rows <- counter.rows + tmp2.len
				tmp2.col <- ncol(tmp2)
				
				#chr pos var quality frequency coverage reads..var. balance genes type known prediction maf   :13
				# target:
				#mutation chr pos var quality frequency coverage reads..var. balance genes type known prediction maf pcranno  : 15
				
				if((tmp2.col<13 | tmp2.col>13)){
					cat("\n We expect 13 import columns (first ID col will be ignored), but we get : ", tmp2.col, " - stop \n")
					print(tmp2[1,])
					cat("\n")
					return()
				}
				
				# add missing column: mutation, pcranno
				mutation <- rep(0, times=tmp2.len)
				pcranno <- rep("", times=tmp2.len)
				tmp2 <- cbind(mutation,tmp2,pcranno, stringsAsFactors=F)
				names(tmp2) <- c("mutation","chr","position","var","quality","frequency","coverage","reads_var","balance","genes","type","known","prediction","maf","pcranno")
				
				# add pcr name(s) to imported data sheet
				for(kk in 1:tmp2.len){
					tmp2[kk, "pcranno"] <- insert.PCR.anno(df.anno="pcr.names", chr.loop=tmp2[kk, "chr"], position.loop=tmp2[kk, "position"], genes.loop=tmp2[kk, "genes"])
				}
				
				# correct data format
				tmp2 <- transform(tmp2,
						chr = as.numeric(chr),
						position = as.numeric(position),
						quality = as.numeric(quality),
						frequency = as.numeric(frequency),
						coverage = as.numeric(coverage),
						reads_var = as.numeric(reads_var),
						balance = as.numeric(balance)
				)
				# add to database
				db.add.data(DB.path.name=DB.path.name, data=tmp2, df.name=obj.name, group.type=group.number)
			}
			# make entry in log file
			assign(protocol.name,
					value=rbind( get(protocol.name, pos=1, inherits=F), c(format(Sys.time(), format="%Y %m %d %H:%M"), all.files[i], j, paste(names.sheet, sep="", collapse=", ")) ),
					pos=1, inherits=F)
		}
	}
	
	# info
	cat("\n present group number : ", group.number)
	cat("\n number of files: ",all.files.len,"\n number of sheets: ",counter.sheets,"\n number of rows: ",counter.rows,"\n")
	# force grabage collection
	gc()		# needs some time, return value: status report
	#
	return()
}


delete.batch.in.db <- function(del.batch=NULL, log.prefix="", DB.path.name=""){
	# delete one or more batch numbers and the associated data from a database file
	# e.g. if the import went wrong or else
	
	require(RSQLite)
	
	if(DB.path.name==""){ cat("\n Please give a DB name - stop\n"); return() }
	if(is.null(del.batch)){ cat("\n Please provide one or more batch numbers - stop\n"); return() }
	
	# If the named database does exist, open it
	if(file.exists(paste(getwd(),DB.path.name,sep=""))){					# file.access(names, mode={0124})
		db <- dbConnect(dbDriver("SQLite"), dbname=paste(getwd(),DB.path.name,sep=""))
	}else{
		cat("\n Database does not exist / or wrong name/path - stop \n")
		return()
	}
	
	sql.st0 <- paste("SELECT sample_id,batch FROM sample WHERE batch IN (",paste(del.batch,collapse=","),");",
			sep="")
	
	id.0 <- dbGetQuery(conn=db, sql.st0 )
	cat("\n sample_id range ",range(id.0[,1]))
	cat("\n batch range     ",range(id.0[,2]),"\n")
	
	sql.st1 <- paste("DELETE 
					  FROM variations 
					  WHERE sample_id IN ( SELECT sample_id FROM sample WHERE batch IN (",paste(del.batch,collapse=","),") )",
			sep="")
	
	id.1 <- dbSendQuery(conn=db, sql.st1 )
	id.1a <- dbGetException(conn=db)
	cat("\n status 1: ",unlist(id.1a),"\n")
	
	sql.st2 <- paste("DELETE 
					  FROM sample 
					  WHERE batch IN (",paste(del.batch,collapse=","),")",
			sep="")
	
	id.2 <- dbSendQuery(conn=db, sql.st2 )
	id.2a <- dbGetException(conn=db)
	cat("\n status 2: ",unlist(id.2a),"\n")
	
	
	# close connection
	dbDisconnect(db)
	return()
}



####
# import xlsx -- all sheets in -one- data object


## these two objects must exist - if not create them; but only once (!) they are the memory
# list for all (!) imported files ever
log.one <- function(log.prefix=""){
	# import xlsx -- all sheets in -one- data file
	# create log files specific for a certain database
	# give only a file prefix - it will be created in the present work folder
	
	if(log.prefix==""){ cat("\n give a log data prefix - be collection specific ! \n"); return() }

	protocol.name <- paste(log.prefix,"protocol.one",sep="")
	list.name <- paste(log.prefix,"list.one",sep="")
	if(exists(x=protocol.name, where=1, mode="any", inherits=F)){
		cat("\n ",protocol.name," already exist - delete manually if you want to start from scratch \n")
		return()
	}
	if(exists(x=list.name, where=1, mode="any", inherits=F)){
		cat("\n ",list.name," already exist - delete manually if you want to start from scratch \n")
		return()
	}
	
	protocol.import.mass <- as.data.frame( matrix(0,1,4) )		# all xlsx workbooks
	names(protocol.import.mass) <- c("date","file.name","number.of.sheets","names.of.sheets")
	protocol.import.mass[1,] <- c(format(Sys.time(), format="%Y %m %d %H:%M"), "creation of list", 0, "--")
	# jobs done
	assign(x=protocol.name, value=protocol.import.mass, pos=1, inherits=F)
	# list of all R data objects ever (all xlsx work sheets)
	assign(x=list.name, value=list(creation=format(Sys.time(), format="%Y %m %d %H:%M")), pos=1, inherits=F)
}


import.in.one.df <- function(import.path.xlsx="", log.prefix="", db.df=""){
	# import all xlsx files of a folder into R into one data.frame
	# set import file path -- we assume, that only xlsx files dedicated for import are in that directory
	
	#ini
	require(rJava)
	require(xlsxjars)
	require(xlsx)
	
	if(import.path.xlsx==""){ cat("\n give a relative path to a folder with xlsx files (sub folder will be not processed)\n"); return() }
	if(log.prefix==""){ cat("\n give a log data prefix - be collection specific ! \n"); return() }
	
	protocol.name <- paste(log.prefix,"protocol.one",sep="")
	list.name <- paste(log.prefix,"list.one",sep="")
	# we look for global variables only in the global environment
	if(!exists(x=protocol.name, where=1, mode="any", inherits=F)){
		cat("\n create ",protocol.name," object first - or think about its location\n")
		return()
	}
	if(!exists(x=list.name, where=1, mode="any", inherits=F)){
		cat("\n create ",list.name," object first - or think about its location\n")
		return()
	}
	
	if(db.df==""){ cat("\n give a data.frame name of a new or existing data.frame\n"); return() }
	if(!exists(x=db.df, where=1, mode="any", inherits=F)){
		tmp <- data.frame(matrix(0,1,17))
		names(tmp) <- c("sample.description","susp.mutation","mutation","chr","pos","var","quality","frequency","coverage","reads.var","balance","genes","type","known","prediction","maf")
		assign(x=db.df,
				value=tmp,
				pos=1, inherits=F)
		# the first time give a '0'
		group.number <- 0
	}else{
		# get last import number and advance by one
		group.number <- max(get(db.df)[,"susp.mutation"]) +1
	}
	cat("\n group number : ", group.number)	
	
	
	# make full import path
	import.path.xlsx <- paste(getwd(),import.path.xlsx,sep="")
	
	# create file name list
	all.files <- list.files(path=import.path.xlsx)		#  ! will always include folders
	all.files.len <- length(all.files)
	# therefore remove folders separately
	all.files.logi <- vector(mode="logical",length=all.files.len)
	for(i in 1:all.files.len){
		all.files.logi[i] <- file.info(paste(import.path.xlsx,"/",all.files[i],sep=""))$isdir
	}
	all.files <- all.files[!all.files.logi]		# get files
	all.files.len <- length(all.files)		# new length
	
	# create second file name list - but non alpha-numeric characters replaced by '.'
	all.files.clean <- vector(mode="character", length=all.files.len)
	for(i in 1:all.files.len){
		all.files.clean[i] <- gsub("[^[:alnum:]]", ".", all.files[i])
	}
	
	### import all files and all sheets in one file
	counter.sheets <- 0
	counter.rows <- 0
#	cat("\n 1 ")
	for(i in 1:all.files.len){
		fp <- paste(import.path.xlsx, all.files[i], sep="/")
		wb.ref <- loadWorkbook(fp)
		anz.sheet <- wb.ref$getNumberOfSheets()
		counter.sheets <- counter.sheets + anz.sheet
		names.sheet <- names( getSheets(wb.ref) )
		if(anz.sheet>0){
#			cat("\n 2 ")
			for(j in 1:anz.sheet){
				# construct object name
				obj.name <- paste(all.files.clean[i],names.sheet[j],sep=".")
				# make entry in log file
				assign(list.name,
					value=c( get(list.name, pos=1, inherits=T), c(obj.name, format(Sys.time(), format="%Y %m %d %H:%M")) ),
					pos=1, inherits=T)
			
#				cat("\n 3 ")
				# import
				tmp2 <- read.xlsx2(file=fp, sheetIndex=j, as.data.frame=TRUE, header=TRUE, colClasses="character", stringsAsFactors=F)
				tmp2.len <- nrow(tmp2)
				counter.rows <- counter.rows + tmp2.len
				tmp2.col <- ncol(tmp2)
				
				# correct data format
				tmp2 <- transform(tmp2,
							Chr = as.numeric(Chr),
							Pos = as.numeric(Pos),
							Quality = as.numeric(Quality),
							Frequency = as.numeric(Frequency),
							Coverage = as.numeric(Coverage),
							Reads..Var. = as.numeric(Reads..Var.),
							Balance = as.numeric(Balance)
						)
				
				if((tmp2.col<13 | tmp2.col>13)){
					cat("\n We expect 13 import columns (first ID column will be ignored), but we get : ", tmp2.col, " - stop \n")
					print(tmp2[1,])
					cat("\n")
					return()
				}
				
				# add missing columns
				obj.v <- rep(obj.name, times=tmp2.len)
				susp.mut <- rep(group.number, times=tmp2.len)
				mutation <- rep(0, times=tmp2.len)
				tmp2 <- cbind(obj.v,susp.mut,mutation,tmp2, stringsAsFactors=F)
				
				names(tmp2) <- c("sample.description","susp.mutation","mutation","chr","pos","var","quality","frequency","coverage","reads.var","balance","genes","type","known","prediction","maf")
#				cat("\n 5 ")
				
				# write out and append
				assign(x=db.df, value=rbind(get(db.df, pos=1, inherits=F),tmp2), pos=1, inherits=F)
			}
			# make entry in log file
			assign(protocol.name,
				value=rbind( get(protocol.name, pos=1, inherits=F), c(format(Sys.time(), format="%Y %m %d %H:%M"), all.files[i], j, paste(names.sheet, sep="", collapse=", ")) ),
				pos=1, inherits=F)
		}
	}
	# remove first dummy row
	nrs <- nrow(get(db.df, pos=1, inherits=F))
	if(nrs>1){ assign(x=db.df, value=get(db.df, pos=1, inherits=F)[-1,], pos=1, inherits=F) }
	
	# info
	cat("\n number of files: ",all.files.len,"\n number of sheets: ",counter.sheets,"\n number of rows: ",counter.rows,"\n")
	# force grabage collection
	gc()		# needs some time, return value: status report
	#
	return()
}


export.in.one.df <- function(export.path.name.xlsx="", db.df=""){
	# export in.one.df data.frame in one single Excel file
	#  (maybe after customizing this data.frame)
	# export.path.name.xlsx: relative path in workspace folder and file name (w/o extension)
	
	#ini
	require(rJava)
	require(xlsxjars)
	require(xlsx)
	
	if(export.path.name.xlsx==""){ cat("\n give a relative path to a folder with xlsx file name \n"); return() }
	if(db.df==""){ cat("\n give a data.frame name of an existing data.frame \n"); return() }
	if(!exists(x=db.df, where=1, mode="any", inherits=F)){ cat("\n data.frame not existing \n"); return() }
	
	write.xlsx(x=get(db.df, pos=1, inherits=F), file=paste(getwd(),export.path.name.xlsx,".xlsx",sep=""), sheetName=db.df, col.names=T, row.names=T, append=F, showNA=T)
	
	return()
}



####
# correct Excel work sheets
correct.xlsx.01 <- function(import.path.xlsx="", export.path.xlsx="", chr=17){
	# correct Excel work sheets
	# insert Chr column (e.g. chr 17: BRCA1)  [[and change column name Position->Pos]
	
	#ini
	require(rJava)
	require(xlsxjars)
	require(xlsx)
	
	if(import.path.xlsx==""){ cat("\n give a relative path to an -import- folder with xlsx files (sub folder will be not processed)\n"); return() }
	if(export.path.xlsx==""){ cat("\n give a relative path to an -export- folder with xlsx files \n"); return() }
	
	# make full import path
	import.path.xlsx <- paste(getwd(),import.path.xlsx,sep="")
	export.path.xlsx <- paste(getwd(),export.path.xlsx,sep="")
	
	# create file name list
	all.files <- list.files(path=import.path.xlsx)		#  ! will always include folders
	all.files.len <- length(all.files)
	# therefore remove folders separately
	all.files.logi <- vector(mode="logical",length=all.files.len)
	for(i in 1:all.files.len){
		all.files.logi[i] <- file.info(paste(import.path.xlsx,"/",all.files[i],sep=""))$isdir
	}
	all.files <- all.files[!all.files.logi]		# get files
	all.files.len <- length(all.files)		# new length
	
	### import all files and all sheets in one file
	counter.sheets <- 0
	counter.rows <- 0
	for(i in 1:all.files.len){
		fi <- paste(import.path.xlsx, all.files[i], sep="/")
		fo <- paste(export.path.xlsx, all.files[i], sep="/")
		wb.ref <- loadWorkbook(fi)
		anz.sheet <- wb.ref$getNumberOfSheets()
		counter.sheets <- counter.sheets + anz.sheet
		names.sheet <- names( getSheets(wb.ref) )
		if(anz.sheet>0){
			for(j in 1:anz.sheet){
				# import
				tmp <- read.xlsx2(file=fi, sheetIndex=j, as.data.frame=T, header=T, colClasses="character", stringsAsFactors=F)
#				return(tmp)
				#### customize ####
				tmp.nr <- nrow(tmp)
				tmp.nc <- ncol(tmp)
				names.tmp <- names(tmp)
				counter.rows <- counter.rows + tmp.nr
				chr.v <- rep(chr, tmp.nr)
				tmp <- cbind(Chr=chr.v,tmp)
				names(tmp) <- c("Chr","Pos",names.tmp[-1])
				#### customize ####
#				return(tmp)
				# export
				write.xlsx(x=tmp, file=fo, sheetName=names.sheet[j], col.names=T, row.names=F, append=T, showNA=T)
			}
		}
	}
	# info
	cat("\n number of files: ",all.files.len,"\n number of sheets: ",counter.sheets,"\n number of rows: ",counter.rows,"\n")
	return()
}



# import PCR bed file in data.frame of workspace
import.pcr.info <- function(import.path.bed="", name.df="", flag=F){
	# import PCR specific names for br8 given in bed format in a data.frame
	# flag:T: add, F : create/overwrite
	# get relative path
	
	if(import.path.bed==""){ cat("\n give a relative path to an import file \n"); return() }
	if(name.df==""){ cat("\n give a data.frame name \n"); return() }
	
	# make full import path
	import.path.bed <- paste(getwd(),import.path.bed,sep="")
	
	# get file name
	import.path.bed.str <- unlist(strsplit(x=import.path.bed, split="/", fixed=T))
	strsplit.len <- length(import.path.bed.str)
	fname <- import.path.bed.str[strsplit.len]
	# import	
	tmp <- read.table( file=import.path.bed, header=F, sep="\t", quote="\"'", dec=".", stringsAsFactors=F )
	names(tmp) <- c("chr","start","end","pcr.name")
	
	if(flag){
		# we look for global variables only in the global environment
		if(exists(x=name.df, where=1, mode="any", inherits=F)){
			assign(x=name.df, value=rbind(get(name.df, pos=1, inherits=F),tmp), pos=1, inherits=F)	# add
		}else{
			assign(x=name.df, value=tmp, pos=1, inherits=F)		# create
		}
	}else{
		assign(x=name.df, value=tmp, pos=1, inherits=F)		# create/overwrite
	}
	
	return()
}


### not functional ### import rs numbers from vcf file in a data.frame of the workspace
import.rs.info <- function(import.path.abs="", in.df=NULL){
	# import NCBI rs All.vcf information in a data.frame
	#  filter by a list of positions, so only nessecary information will be imported (and not all)
	# very specific - customize fn in case
	# get absolute path
	# get in.df with column 1 : chromosome number and  column 2 : genomic positions
	
	#ini
	require(sqldf)		# nice package !
	
	if(import.path.abs==""){ cat("\n give an absolute path to an import file \n"); return() }
	if(is.null(in.df)){ cat("\n give an input data.frame : column 1 : chromosome number, column 2 : genomic position \n"); return() }
	
	## sql filter
	# terminal tests:
	# wc file  :  newline, word, and byte counts
	# head -n 150 file  :  first lines
	# tail -n 150 file  :  last lines
	
	# CHROM POS ID REF ALT QUAL FILTER INFO
	# first we filter on position
	sql.f <- paste("SELECT * FROM file
					WHERE POS IN (",paste(in.df[,2],collapse=","),");",
			sep="")
	
	# select targets
	#    read.table( file=import.path.vcf,  ...  nrow=61220532)       -->  very slow / and wrong idea
	tmp.names <- c("chr", "pos", "id", "ref", "alt", "qual", "filter", "info")
	tmp.data <- read.csv.sql(file=import.path.abs, sql=sql.f, header=T, skip=69, sep="\t",
			colClasses=c("character","numeric","character","character","character", "character", "character", "character"))
	# list of data.frames will be returned;  many entries are NULL because not every positions is available in all chromosomes
	# convert list into data.frame
	tmp.data2 <- NULL
	for(i in 1:length(tmp.data)){
		tmp.data2 <- rbind(tmp.data2,tmp.data[[i]])
	}
	# name columns
	tmp.data2 <- t(tmp.data2)		# transpose
	names(tmp.data2) <- tmp.names
	
	# secondly we filter on chr & pos
	f1 <- function(x,d2){
		d2[ x[1]==d2[,1] & x[2]==d2[,2], ,drop=F]
	}
	res <- apply(in.df,1,f1,d2=tmp.data2)
	# info out
	cat("\n sql.f ",sql.f)
	cat("\n returned rows - raw ",nrow(tmp.data2)," and final ",nrow(res))
	
	return(res)
}


# 
create.rs.db <- function(import.path.abs=""){
	# import single CSV file (e.g. NCBI rs All.vcf) in one stand-alone sqlite database
	#  in work directory; db name is the file name w/o extension
	# get absolute path
	
	#ini
	require(RSQLite)
	require(sqldf)
	
	if(import.path.abs==""){ cat("\n give an absolute path to an import file \n"); return() }
	
	# get file name
	import.path.abs.str <- unlist(strsplit(x=import.path.abs, split="/", fixed=T))
	s.len <- length(import.path.abs.str)
	fpath <- paste(import.path.abs.str[-s.len],collapse="/")
	fname.ext <- unlist(strsplit(x=import.path.abs.str[s.len], split=".", fixed=T))
	s.len2 <- length(fname.ext)
	fname <- paste(fname.ext[-s.len2],collapse=".")
	fext <- fname.ext[s.len2]
	cat("\n import.path.abs ",import.path.abs)
	cat("\n fpath ",fpath)
	cat("\n fname ",fname)
	cat("\n fext ",fext)
	
	# If the named database does not yet exist, one is created
	if(file.exists(fname)){					# file.access(names, mode={0124})
		cat("\n Database file is already existing - stop\n")
		return()
	}else{
		db <- dbConnect(dbDriver("SQLite"), dbname=fname)
		cat("\n Database created in : ", getwd(), "\n")
	}
	dbDisconnect(db)            # Close connection
	
	# table prefix should avoid conficts with sql keywords
	sql.c <- paste("CREATE TABLE ","t_",fname," AS SELECT * FROM file",sep="")
	cat("\n sql.c ",sql.c)
	
	# CHROM POS ID REF ALT QUAL FILTER INFO
	read.csv.sql(import.path.abs, sql=sql.c, dbname=fname,
			header=T, skip=69, sep="\t",
			colClasses=c("character","numeric","character","character","character", "character", "character", "character"))
	
	# close an old sqldf connection if it exists
	if(!is.null(getOption("sqldf.connection"))) sqldf()
	
	return()
}




#### the following import variant is not on the same development level (import.in.single.df)
####

# import xlsx -- all sheets in -single- data objects


## these two objects must exist - if not create them; but only once (!) they are the memory
# list for all (!) imported files ever
log.single <- function(log.prefix=""){
	# import xlsx -- all sheets in single data objects
	# create log files specific for a certain database
	# give only a file prefix - it will be created in the present work folder
	
	if(log.prefix==""){ cat("\n give a log data prefix - be collection specific ! \n"); return() }
	
	protocol.name <- paste(log.prefix,"protocol.single",sep="")
	list.name <- paste(log.prefix,"list.single",sep="")
	if(exists(x=protocol.name, where=1, mode="any", inherits=F)){
		cat("\n ",protocol.name," already exist - delete manually if you want to start from scratch \n")
		return()
	}
	if(exists(x=list.name, where=1, mode="any", inherits=F)){
		cat("\n ",list.name," already exist - delete manually if you want to start from scratch \n")
		return()
	}
	
	protocol.import.mass <- as.data.frame( matrix(0,1,4) )		# all xlsx workbooks
	names(protocol.import.mass) <- c("date","file.name","number.of.sheets","names.of.sheets")
	protocol.import.mass[1,] <- c(format(Sys.time(), format="%Y %m %d %H:%M"), "creation of list", 0, "--")
	# jobs done
	assign(x=protocol.name, value=protocol.import.mass, pos=1, inherits=F)
	# list of all R data objects ever (all xlsx work sheets)
	assign(x=list.name, value=list(creation=format(Sys.time(), format="%Y %m %d %H:%M")), pos=1, inherits=F)
}


import.in.single.df <- function(import.path.xlsx="", log.prefix="", group.number=0){
	# import all xlsx files of a folder into R into single data.frames
	# set import file path -- we assume, that only xlsx files dedicated for import are in that directory
	# group.number: get last import number and advance by one - if the first time give a '0'
	
	#ini
	require(rJava)
	require(xlsxjars)
	require(xlsx)
	
	if(import.path.xlsx==""){ cat("\n give a relative path to a folder with xlsx files (sub folder will be not processed)\n"); return() }
	if(log.prefix==""){ cat("\n give a log data prefix - be collection specific ! \n"); return() }
	
	protocol.name <- paste(log.prefix,"protocol.single",sep="")
	list.name <- paste(log.prefix,"list.single",sep="")
	# we look for global variables only in the global environment
	if(!exists(x=protocol.name, where=1, mode="any", inherits=F)){
		cat("\n create ",protocol.name," object first - or think about its location\n")
		return()
	}
	if(!exists(x=list.name, where=1, mode="any", inherits=F)){
		cat("\n create ",list.name," object first - or think about its location\n")
		return()
	}
	
	cat("\n group number : ", group.number)
	
	# make full import path
	import.path.xlsx <- paste(getwd(),import.path.xlsx,sep="")
	
	# create file name list
	all.files <- list.files(path=import.path.xlsx, pattern=NULL, all.files=FALSE, full.names=FALSE, recursive=FALSE,
			ignore.case=FALSE, include.dirs=FALSE, no..=FALSE)
	all.files.len <- length(all.files)
	
	# create second file name list - but non alpha-numeric characters replaced by '.'
	all.files.clean <- vector(mode="character", length=all.files.len)
	for(i in 1:all.files.len){
		all.files.clean[i] <- gsub("[^[:alnum:]]", ".", all.files[i])
	}
	
	### import all files and all sheets in a file
	# --- in StatET environment we need 'Rterm' instead of 'rj' in run configuration for this part ---
	# see all the available java methods that you can call
	#.jmethods( loadWorkbook(paste(import.path.xlsx,all.files[1],sep="/")) )
	#options(java.parameters="-Xmx2500m")		# Java heap space for big files - gc() advised ?
	
	counter.sheets <- 0
	counter.rows <- 0
	for(i in 1:all.files.len){
		fp <- paste(import.path.xlsx, all.files[i], sep="/")
		wb.ref <- loadWorkbook(fp)
		anz.sheet <- wb.ref$getNumberOfSheets()
		counter.sheets <- counter.sheets + anz.sheet
		names.sheet <- names( getSheets(wb.ref) )
#cat("\n file # ",i)
		if(anz.sheet>0){
			for(j in 1:anz.sheet){
				# construct object name
				obj.name <- paste(all.files.clean[i],names.sheet[j],sep=".")
				# make entry in log file
				assign(list.name,
						value=c( get(list.name, pos=1, inherits=T), c(obj.name, format(Sys.time(), format="%Y %m %d %H:%M")) ),
						pos=1, inherits=T)
				# import			###########correct colClasses to NA and check
				tmp2 <- read.xlsx2(file=fp, sheetIndex=j, as.data.frame=TRUE, header=TRUE, colClasses="character", stringsAsFactors=F)
				assign(x=obj.name,
						value=tmp2,
						pos=1, inherits=F)
				#
				tmp2.len <- nrow(tmp2)
				counter.rows <- counter.rows + tmp2.len
				
				# correct data format
				assign(x=obj.name, value=
								transform(get(obj.name, pos=1, inherits=F),
										Chr = as.numeric(Chr),
										Pos = as.numeric(Pos),
										Quality = as.numeric(Quality),
										Frequency = as.numeric(Frequency),
										Coverage = as.numeric(Coverage),
										Reads..Var. = as.numeric(Reads..Var.),
										Balance = as.numeric(Balance)
								),
						pos=1, inherits=F)
#cat("\n sheet # ",j)
			}
			# make entry in log file
			assign(protocol.name,
					value=rbind( get(protocol.name, pos=1, inherits=F), c(format(Sys.time(), format="%Y %m %d %H:%M"), all.files[i], j, paste(names.sheet, sep="", collapse=", ")) ),
					pos=1, inherits=F)
		}
	}
	
	# info
	cat("\n number of files: ",all.files.len,"\n number of sheets: ",counter.sheets,"\n number of rows: ",counter.rows,"\n")
	# force grabage collection
	gc()		# needs some time, return value: status report
	#
	return()
}