/**
* Insert mutation and set primary key
*
* @param mutation
* @return number of mutations inserted
* @throws IOException
* @throws Exception
*/
public int insert(MutationUploadVO mutationUploadVO) throws Exception {
int count = 0;
// Exons are never inserted via mutations, just select
mutationUploadVO
.getMutation()
.setExon(this.db.findById(Exon.class, mutationUploadVO.getExonId()));
// Genes are never inserted via mutations, just select
mutationUploadVO
.getMutation()
.setGene(
this.db
.query(MutationGene.class)
.equals(MutationGene.SYMBOL, mutationUploadVO.getGeneSymbol())
.find()
.get(0));
// Insert mutation and set primary key
count = this.db.add(mutationUploadVO.getMutation());
List<Mutation> mutations = this.db.findByExample(mutationUploadVO.getMutation());
if (mutations.size() == 1) {
mutationUploadVO.getMutation().setId(mutations.get(0).getId());
}
return count;
}
/**
* Imports UploadBatch from tab/comma delimited File
*
* @param db database to import into
* @param reader csv reader to load data from
* @param defaults to set default values for each row
* @param dbAction indicating wether to add,update,remove etc
* @param missingValues indicating what value in the csv is treated as 'null' (e.g. "" or "NA")
* @return number of elements imported
*/
public int importCsv(
final Database db,
CsvReader reader,
final Tuple defaults,
final DatabaseAction dbAction,
final String missingValues)
throws DatabaseException, IOException, Exception {
// cache for entities of which xrefs couldn't be resolved (e.g. if there
// is a self-refence)
// these entities can be updated with their xrefs in a second round when
// all entities are in the database
// final List<UploadBatch> uploadBatchsMissingRefs = new
// ArrayList<UploadBatch>();
final MutationService mutationService = new MutationService();
mutationService.setDatabase(db);
final UploadService uploadService = new UploadService();
uploadService.setDatabase(db);
final Submission submission = new Submission();
DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
submission.setDate(dateFormat.format(new Date()));
submission.setReleasedate(dateFormat.format(new Date()));
submission.setIdentifier("S" + new Date());
if (db instanceof JDBCDatabase) {
List<Integer> submitters = new ArrayList<Integer>();
submitters.add(db.getLogin().getUserId());
submission.setSubmitters_Id(submitters);
} else if (db instanceof JpaDatabase) {
List<MolgenisUser> submitters = new ArrayList<MolgenisUser>();
submitters.add(db.findById(MolgenisUser.class, db.getLogin().getUserId()));
// submission.setSubmitters(submitters);
}
db.add(submission);
// cache for objects to be imported from file (in batch)
// TODO: Danny: Use or loose
/* final List<PatientSummaryVO> patientList = */ new ArrayList<PatientSummaryVO>(BATCH_SIZE);
// wrapper to count
final IntegerWrapper total = new IntegerWrapper(0);
reader.setMissingValues(missingValues);
Integer mutationIdentifier = uploadService.getMaxMutationIdentifier();
Integer patientIdentifier = uploadService.getMaxPatientIdentifier();
for (Tuple tuple : reader) {
// parse object, setting defaults and values from file
// if (lineNo > 5) return;
PatientSummaryVO patientSummaryVO = new PatientSummaryVO();
patientSummaryVO.setSubmissionDate(submission.getDate());
patientSummaryVO.setPatientNumber(tuple.getString("Local patient number"));
patientSummaryVO.setPatientGender(
ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Gender")), "unknown"));
patientSummaryVO.setPatientEthnicity(tuple.getString("Ethnicity"));
patientSummaryVO.setPatientAge(ObjectUtils.toString(tuple.getString("Age"), "unknown"));
patientSummaryVO.setPatientDeceased(
ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Deceased")), "unknown"));
patientSummaryVO.setPatientConsent(
ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Signed consent")), "no"));
patientSummaryVO.setVariantSummaryVOList(new ArrayList<MutationSummaryVO>());
if (StringUtils.isNotEmpty(tuple.getString("cDNA change_1"))) {
MutationUploadVO mutationUploadVO = new MutationUploadVO();
mutationUploadVO.setGeneSymbol("COL7A1");
mutationUploadVO.setMutation(new Mutation());
mutationUploadVO.getMutation().setCdna_Notation("c." + tuple.getString("cDNA change_1"));
mutationService.assignValuesFromNotation(mutationUploadVO);
if (StringUtils.isNotEmpty(tuple.getString("Protein change_1")))
mutationUploadVO.getMutation().setAa_Notation("p." + tuple.getString("Protein change_1"));
if (StringUtils.isNotEmpty(tuple.getString("Consequence_1")))
mutationUploadVO
.getMutation()
.setConsequence(ObjectUtils.toString(tuple.getString("Consequence_1"), ""));
mutationUploadVO
.getMutation()
.setInheritance(
ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Inheritance_1")), ""));
mutationIdentifier = mutationIdentifier + 1;
mutationUploadVO.getMutation().setIdentifier("M" + mutationIdentifier);
mutationUploadVO.getMutation().setName("M" + mutationIdentifier);
// Insert mutation if it does not exist already
MutationSearchCriteriaVO criteria = new MutationSearchCriteriaVO();
criteria.setVariation(mutationUploadVO.getMutation().getCdna_Notation());
List<MutationSummaryVO> results = mutationService.findMutations(criteria);
if (results.size() != 1) {
uploadService.insert(mutationUploadVO);
System.out.println(">>>Inserted mutation: " + mutationUploadVO.getMutation().toString());
}
MutationSummaryVO mutationSummaryVO = new MutationSummaryVO();
mutationSummaryVO.setCdnaNotation(mutationUploadVO.getMutation().getCdna_Notation());
patientSummaryVO.getVariantSummaryVOList().add(mutationSummaryVO);
}
// Second mutation can be 'NA' or 'unknown':
// Leave xref == null and add remark in mutation2remark
if (StringUtils.isNotEmpty(tuple.getString("cDNA change_2"))) {
if (tuple.getString("cDNA change_2").equalsIgnoreCase("na"))
patientSummaryVO.setVariantComment(tuple.getString("cDNA change_2").toUpperCase());
else if (tuple.getString("cDNA change_2").equalsIgnoreCase("unknown"))
patientSummaryVO.setVariantComment(tuple.getString("cDNA change_2").toUpperCase());
else {
MutationUploadVO mutationUploadVO = new MutationUploadVO();
mutationUploadVO.setGeneSymbol("COL7A1");
mutationUploadVO.setMutation(new Mutation());
mutationUploadVO.getMutation().setCdna_Notation("c." + tuple.getString("cDNA change_2"));
mutationService.assignValuesFromNotation(mutationUploadVO);
if (StringUtils.isNotEmpty(tuple.getString("Protein change_2")))
mutationUploadVO
.getMutation()
.setAa_Notation("p." + tuple.getString("Protein change_2"));
if (StringUtils.isNotEmpty(tuple.getString("Consequence_2")))
mutationUploadVO
.getMutation()
.setConsequence(ObjectUtils.toString(tuple.getString("Consequence_2"), ""));
mutationUploadVO
.getMutation()
.setInheritance(
ObjectUtils.toString(
StringUtils.lowerCase(tuple.getString("Inheritance_2")), ""));
mutationIdentifier = mutationIdentifier + 1;
mutationUploadVO.getMutation().setIdentifier("M" + mutationIdentifier);
mutationUploadVO.getMutation().setName("M" + mutationIdentifier);
// Insert mutation if it does not exist already
MutationSearchCriteriaVO criteria = new MutationSearchCriteriaVO();
criteria.setVariation(mutationUploadVO.getMutation().getCdna_Notation());
List<MutationSummaryVO> results = mutationService.findMutations(criteria);
if (results.size() != 1) {
uploadService.insert(mutationUploadVO);
System.out.println(
">>>Inserted mutation: " + mutationUploadVO.getMutation().toString());
}
MutationSummaryVO mutationSummaryVO = new MutationSummaryVO();
mutationSummaryVO.setCdnaNotation(mutationUploadVO.getMutation().getCdna_Notation());
patientSummaryVO.getVariantSummaryVOList().add(mutationSummaryVO);
}
}
patientSummaryVO.setPhenotypeMajor(
StringUtils.upperCase(tuple.getString("Phenotype major type")));
patientSummaryVO.setPhenotypeSub(StringUtils.lowerCase(tuple.getString("Phenotype Subtype")));
// PhenotypeDetailsVO phenotypeDetailsVO = new PhenotypeDetailsVO();
// patientSummaryVO.getPhenotypeDetails().setLocation(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Location")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setBlistering(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Blistering")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setHands(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Hands")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setFeet(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Feet")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setArms(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Arms")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setLegs(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Legs")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setProximal_Body_Flexures(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Proximal body flexures")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setTrunk(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Trunk")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setMucous_Membranes(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Mucosa")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setSkin_Atrophy(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Skin atrophy")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setMilia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Milia")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setNail_Dystrophy(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Nail dystrophy")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setAlbopapuloid_Papules(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Albopapuloid papules")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setPruritic_Papules(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Pruritic papules")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setAlopecia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Alopecia")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setSquamous_Cell_Carcinomas(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Squamous cell carcinoma(s)")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setRevertant_Skin_Patch(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Revertant skin patch(es)")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setMechanism(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Mechanism")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setFlexion_Contractures(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Flexion contractures")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setPseudosyndactyly_Hands(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Pseudosyndactyly (hands)")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setMicrostomia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Microstomia")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setAnkyloglossia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Ankyloglossia")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setDysphagia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Swallowing difficulties/ dysphagia/ oesophagus strictures")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setGrowth_Retardation(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Growth retardation")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setAnemia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Anaemia")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setRenal_Failure(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Renal failure")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setDilated_Cardiomyopathy(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Dilated cardiomyopathy")),
// "unknown"));
//
// patientSummaryVO.getPhenotypeDetails().setColoboma(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Coloboma")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setCongenital_Heart_Defect(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Congenital heart defect")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setClp(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("C(L)P")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setChoanal_Anomaly(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Choanal anomaly")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setMental_Retardation(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Mental retardation")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setGrowth_Retardation(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Growth retardation")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setGenital_Hypoplasia(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Genital hypoplasia")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setExternal_Ear_Anomaly(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("External ear anomaly")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setSemicircular_Canal_Anomaly(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Semicircular canal anomaly")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setHearing_Loss(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("Hearing loss")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setTe_Anomaly(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("TE anomaly")),
// "unknown"));
// patientSummaryVO.getPhenotypeDetails().setCn_Dysfunction(ObjectUtils.toString(StringUtils.lowerCase(tuple.getString("CN dysfunction")),
// "unknown"));
// for (String field :
// patientSummaryVO.getPhenotypeDetails().getFields(true))
// {
// if ("".equals(patientSummaryVO.getPhenotypeDetails().get(field)))
// patientSummaryVO.getPhenotypeDetails().set(field, "unknown");
// }
// patientSummaryVO.getPhenotypeDetails().set(tuple.getString(""));
if (tuple.getString("PubMed ID") != null && tuple.getString("Reference") != null) {
List<PublicationVO> publicationVOs = new ArrayList<PublicationVO>();
String[] pubmeds = tuple.getString("PubMed ID").split(";");
String[] titles = tuple.getString("Reference").split(";");
for (int i = 0; i < pubmeds.length; i++) {
PublicationVO publicationVO = new PublicationVO();
publicationVO.setPubmedId(pubmeds[i]);
publicationVO.setName(titles[i]);
publicationVO.setTitle(titles[i]);
publicationVOs.add(publicationVO);
}
patientSummaryVO.setPublicationVOList(publicationVOs);
patientSummaryVO.setPatientConsent("publication");
}
patientIdentifier = patientIdentifier + 1;
patientSummaryVO.setPatientIdentifier("P" + patientIdentifier);
patientSummaryVO.setPatientName("P" + patientIdentifier);
patientSummaryVO.setObservedValueVOList(new ArrayList<ObservedValueVO>());
ObservedValueVO observedValueVO;
observedValueVO = new ObservedValueVO();
observedValueVO.setFeatureName("LH7:2 Amount of type VII collagen");
observedValueVO.setTargetName(patientSummaryVO.getPatientIdentifier());
observedValueVO.setValue(ObjectUtils.toString(tuple.getString("IF LH7:2"), "unknown"));
patientSummaryVO.getObservedValueVOList().add(observedValueVO);
observedValueVO = new ObservedValueVO();
observedValueVO.setFeatureName("IF Retention of type VII Collagen in basal cells");
observedValueVO.setTargetName(patientSummaryVO.getPatientIdentifier());
observedValueVO.setValue(
ObjectUtils.toString(tuple.getString("IF Retention COLVII"), "unknown"));
patientSummaryVO.getObservedValueVOList().add(observedValueVO);
observedValueVO = new ObservedValueVO();
observedValueVO.setFeatureName("Anchoring fibrils Number");
observedValueVO.setTargetName(patientSummaryVO.getPatientIdentifier());
observedValueVO.setValue(ObjectUtils.toString(tuple.getString("EM AF_no"), "unknown"));
patientSummaryVO.getObservedValueVOList().add(observedValueVO);
observedValueVO = new ObservedValueVO();
observedValueVO.setFeatureName("Anchoring fibrils Ultrastructure");
observedValueVO.setTargetName(patientSummaryVO.getPatientIdentifier());
observedValueVO.setValue(ObjectUtils.toString(tuple.getString("EM AF_structure"), "unknown"));
patientSummaryVO.getObservedValueVOList().add(observedValueVO);
observedValueVO = new ObservedValueVO();
observedValueVO.setFeatureName("EM Retention of type VII Collagen in basal cells");
observedValueVO.setTargetName(patientSummaryVO.getPatientIdentifier());
observedValueVO.setValue(
ObjectUtils.toString(tuple.getString("EM_Retention COLVII"), "unknown"));
patientSummaryVO.getObservedValueVOList().add(observedValueVO);
for (int i = 34; ; i++) {
System.out.println(">>>i==" + i);
String colName = tuple.getColName(i);
System.out.println(">>>colName==" + colName);
if (colName == null) break;
observedValueVO = new ObservedValueVO();
observedValueVO.setFeatureName(colName);
observedValueVO.setTargetName(patientSummaryVO.getPatientIdentifier());
observedValueVO.setValue(ObjectUtils.toString(tuple.getString(colName), "unknown"));
patientSummaryVO.getObservedValueVOList().add(observedValueVO);
}
uploadService.insert(patientSummaryVO);
total.set(total.get() + 1);
// patientList.add(patientSummaryVO);
//
// //add to db when batch size is reached
// if(patientList.size() == BATCH_SIZE)
// {
// //resolve foreign keys and copy those entities that could not be
// resolved to the missingRefs list
// uploadBatchsMissingRefs.addAll(resolveForeignKeys(db,
// uploadBatchList));
//
// //update objects in the database using xref_label defined
// secondary key(s) 'id' defined in xref_label
// db.update(uploadBatchList,dbAction, "id");
//
// //clear for next batch
// uploadBatchList.clear();
//
// //keep count
// total.set(total.get() + BATCH_SIZE);
// }
}
// add remaining elements to the database
// if(!uploadBatchList.isEmpty())
// {
// //resolve foreign keys, again keeping track of those entities that
// could not be solved
// uploadBatchsMissingRefs.addAll(resolveForeignKeys(db,
// uploadBatchList));
// //update objects in the database using xref_label defined secondary
// key(s) 'id' defined in xref_label
// db.update(uploadBatchList,dbAction, "id");
// }
//
// //second import round, try to resolve FK's for entities again as they
// might have referred to entities in the imported list
// List<UploadBatch> uploadBatchsStillMissingRefs =
// resolveForeignKeys(db, uploadBatchsMissingRefs);
//
// //if there are still missing references, throw error and rollback
// if(uploadBatchsStillMissingRefs.size() > 0){
// throw new
// Exception("Import of 'UploadBatch' objects failed: attempting to resolve in-list references,
// but there are still UploadBatchs referring to UploadBatchs that are neither in the database
// nor in the list of to-be imported UploadBatchs. (the first one being:
// "+uploadBatchsStillMissingRefs.get(0)+")");
// }
// //else update the entities in the database with the found references
// and return total
// else
// {
// db.update(uploadBatchsMissingRefs,DatabaseAction.UPDATE, "id");
//
// //output count
// total.set(total.get() + uploadBatchList.size());
// logger.info("imported "+total.get()+" uploadBatch from CSV");
//
// return total.get();
// }
return total.get();
}