QStringList NGSD::getEnum(QString table, QString column)
{
//check cache
static QMap<QString, QStringList> cache;
QString hash = table+"."+column;
if (cache.contains(hash))
{
return cache.value(hash);
}
//DB query
SqlQuery q = getQuery();
q.exec("DESCRIBE "+table+" "+column);
while (q.next())
{
QString type = q.value(1).toString();
type.replace("'", "");
type.replace("enum(", "");
type.replace(")", "");
cache[hash] = type.split(",");
return cache[hash];
}
THROW(ProgrammingException, "Could not determine enum values of column '"+column+"' in table '"+table+"'!");
}
bool NGSD::tableEmpty(QString table)
{
SqlQuery query = getQuery();
query.exec("SELECT COUNT(*) FROM " + table);
query.next();
return query.value(0).toInt()==0;
}
//-----------------------------------------------------------------------------
bool PhaseParameterModel::setWeight(Id phaseParameterId, double weight)
{
SqlQuery updateQuery;
updateQuery.prepare("UPDATE PhaseParameter SET Weight = :weight WHERE ID = :id");
updateQuery.bindValue(":weight", weight);
updateQuery.bindValue(":id", phaseParameterId);
return updateQuery.exec();
}
void NGSD::tableExists(QString table)
{
SqlQuery query = getQuery();
query.exec("SHOW TABLES LIKE '" + table + "'");
if (query.size()==0)
{
THROW(DatabaseException, "Table '" + table + "' does not exist!")
}
}
QStringList NGSD::getValues(const QString& query)
{
SqlQuery q = getQuery();
q.exec(query);
QStringList output;
output.reserve(q.size());
while(q.next())
{
output.append(q.value(0).toString());
}
return output;
}
void NGSD::init(QString password)
{
//remove existing tables
SqlQuery query = getQuery();
query.exec("SHOW TABLES");
if (query.size()>0)
{
//check password for re-init of production DB
if (!test_db_ && password!=Settings::string("ngsd_pass"))
{
THROW(DatabaseException, "Password provided for re-initialization of procution database is incorrect!");
}
//get table list
QStringList tables;
while(query.next())
{
tables << query.value(0).toString();
}
//remove old tables
if (!tables.isEmpty())
{
query.exec("SET FOREIGN_KEY_CHECKS = 0;");
query.exec("DROP TABLE " + tables.join(","));
query.exec("SET FOREIGN_KEY_CHECKS = 1;");
}
}
//initilize
executeQueriesFromFile(":/resources/NGSD_schema.sql");
}
//-----------------------------------------------------------------------------
void PhaseParameterModel::update()
{
SqlQuery parameterWeightQuery;
parameterWeightQuery.prepare(QString("SELECT %4.%1, %2, %3 FROM %4 INNER JOIN %5 ON %4.%6 = %5.%7 WHERE %8 = :phaseId ORDER BY %3 DESC")
.arg("ID", "Name", "Weight", "PhaseParameter"
, "Parameter", "ParameterID", "ID"
, "PhaseID")); //%! Тут нужно все-таки %4.%1 т.е. Id другой.
parameterWeightQuery.addBindValue(mPhaseId);
parameterWeightQuery.exec();
setQuery(parameterWeightQuery);
setHeaderData(0, Qt::Horizontal, tr("ID"));
setHeaderData(1, Qt::Horizontal, tr("Name"));
setHeaderData(2, Qt::Horizontal, tr("Weight"));
}
bool SyncJournalDb::sqlFail( const QString& log, const SqlQuery& query )
{
commitTransaction();
qWarning() << "SQL Error" << log << query.error();
Q_ASSERT(!"SQL ERROR");
_db.close();
return false;
}
QVector<double> NGSD::getQCValues(const QString& accession, const QString& filename)
{
//get processing system ID
QString sys_id = getValue("SELECT processing_system_id FROM processed_sample WHERE id='" + processedSampleId(filename) + "'").toString();
//get QC id
QString qc_id = getValue("SELECT id FROM qc_terms WHERE qcml_id='" + accession + "'").toString();
//get QC data
SqlQuery q = getQuery();
q.exec("SELECT nm.value FROM processed_sample_qc as nm, processed_sample as ps WHERE ps.processing_system_id='" + sys_id + "' AND nm.qc_terms_id='" + qc_id + "' AND nm.processed_sample_id=ps.id ");
//fill output datastructure
QVector<double> output;
while(q.next())
{
bool ok = false;
double value = q.value(0).toDouble(&ok);
if (ok) output.append(value);
}
return output;
}
QPair<QString, QString> NGSD::getClassification(const Variant& variant)
{
SqlQuery query = getQuery();
query.exec("SELECT class, comment FROM variant_classification WHERE variant_id='" + variantId(variant) + "'");
if (query.size()==0)
{
return QPair<QString, QString>("n/a", "");
}
else
{
query.next();
return QPair<QString, QString>(query.value(0).toString().trimmed(), query.value(1).toString().trimmed());
}
}
ValidationInfo NGSD::getValidationStatus(const QString& filename, const Variant& variant)
{
SqlQuery query = getQuery();
query.exec("SELECT status, type, comment FROM variant_validation WHERE sample_id='" + sampleId(filename) + "' AND variant_id='" + variantId(variant) + "'");
if (query.size()==0)
{
return ValidationInfo();
}
else
{
query.next();
return ValidationInfo{ query.value(0).toString().trimmed(), query.value(1).toString().trimmed(), query.value(2).toString().trimmed() };
}
}
QString NGSD::variantId(const Variant& variant, bool throw_if_fails)
{
SqlQuery query = getQuery(); //use binding user input (safety)
query.prepare("SELECT id FROM variant WHERE chr=:chr AND start='"+QString::number(variant.start())+"' AND end='"+QString::number(variant.end())+"' AND ref=:ref AND obs=:obs");
query.bindValue(":chr", variant.chr().str());
query.bindValue(":ref", variant.ref());
query.bindValue(":obs", variant.obs());
query.exec();
if (query.size()==0)
{
if (throw_if_fails)
{
THROW(DatabaseException, "Variant " + variant.toString() + " not found in NGSD!");
}
else
{
return "-1";
}
}
query.next();
return query.value(0).toString();
}
QString NGSD::processedSampleId(const QString& filename, bool throw_if_fails)
{
QStringList parts = QFileInfo(filename).baseName().append('_').split('_');
//get sample ID
SqlQuery query = getQuery(); //use binding (user input)
query.prepare("SELECT ps.id FROM processed_sample ps, sample s WHERE s.name=:sample AND ps.sample_id=s.id AND ps.process_id=:psnum");
query.bindValue(":sample", parts[0]);
query.bindValue(":psnum", QString::number(parts[1].toInt()));
query.exec();
if (query.size()==0)
{
if(throw_if_fails)
{
THROW(DatabaseException, "Processed sample name '" + parts[0] + "_" + parts[1] + "' not found in NGSD!");
}
else
{
return "";
}
}
query.next();
return query.value(0).toString();
}
bool ListModel::_removeItem(ListItem* item)
{
for (int i = item->childCount(); i > 0; --i)
_removeItem(item->child(i - 1));
ListItem* parent = item->parent();
int id = item->id();
int row = item->row();
SqlQuery sql;
sql.prepare("UPDATE list_item SET weight = weight - 1 WHERE parent_id = :parent AND weight > :weight");
sql.bindValue(":parent", parent->id());
sql.bindValue(":weight", row);
if (!sql.exec())
return false;
sql.prepare("DELETE FROM list_item WHERE id = :id");
sql.bindValue(":id", id);
if (!sql.exec())
return false;
parent->removeChild(row);
return true;
}
void NGSD::setClassification(const Variant& variant, const QString& classification, const QString& comment)
{
QString v_id = variantId(variant);
QVariant vc_id = getValue("SELECT id FROM variant_classification WHERE variant_id='" + v_id + "'");
SqlQuery query = getQuery(); //use binding (user input)
if (vc_id.isNull()) //insert
{
query.prepare("INSERT INTO variant_classification (variant_id, class, comment) VALUES ('" + v_id + "',:class,:comment)");
}
else //update
{
query.prepare("UPDATE variant_classification SET class=:class, comment=:comment WHERE id='" + vc_id.toString() + "'");
}
query.bindValue(":class", classification);
query.bindValue(":comment", comment);
query.exec();
}
void NGSD::setValidationStatus(const QString& filename, const Variant& variant, const ValidationInfo& info)
{
QString s_id = sampleId(filename);
QString v_id = variantId(variant);
QVariant vv_id = getValue("SELECT id FROM variant_validation WHERE sample_id='" + s_id + "' AND variant_id='" + v_id + "'");
SqlQuery query = getQuery(); //use binding (user input)
if (vv_id.isNull()) //insert
{
QString geno = getValue("SELECT genotype FROM detected_variant WHERE variant_id='" + v_id + "' AND processed_sample_id='" + processedSampleId(filename) + "'", false).toString();
query.prepare("INSERT INTO variant_validation (sample_id, variant_id, genotype, status, type, comment) VALUES ('" + s_id + "','" + v_id + "','" + geno + "',:status,:type,:comment)");
}
else //update
{
query.prepare("UPDATE variant_validation SET status=:status, type=:type, comment=:comment WHERE id='" + vv_id.toString() + "'");
}
query.bindValue(":status", info.status);
query.bindValue(":type", info.type);
query.bindValue(":comment", info.comment);
query.exec();
}
QString NGSD::sampleId(const QString& filename, bool throw_if_fails)
{
QStringList parts = QFileInfo(filename).baseName().append('_').split('_');
//get sample ID
SqlQuery query = getQuery(); //use binding (user input)
query.prepare("SELECT id FROM sample WHERE name=:sample");
query.bindValue(":sample", parts[0]);
query.exec();
if (query.size()==0)
{
if(throw_if_fails)
{
THROW(DatabaseException, "Sample name '" + parts[0] + "' not found in NGSD!");
}
else
{
return "";
}
}
query.next();
return query.value(0).toString();
}
QCCollection NGSD::getQCData(const QString& filename)
{
QString ps_id = processedSampleId(filename, false);
//get QC data
SqlQuery q = getQuery();
q.exec("SELECT n.name, nm.value, n.description, n.qcml_id FROM processed_sample_qc as nm, qc_terms as n WHERE nm.processed_sample_id='" + ps_id + "' AND nm.qc_terms_id=n.id");
QCCollection output;
while(q.next())
{
output.insert(QCValue(q.value(0).toString(), q.value(1).toString(), q.value(2).toString(), q.value(3).toString()));
}
//get KASP data
SqlQuery q2 = getQuery();
q2.exec("SELECT random_error_prob FROM kasp_status WHERE processed_sample_id='" + ps_id + "'");
QString value = "n/a";
if (q2.size()>0)
{
q2.next();
float numeric_value = 100.0 * q2.value(0).toFloat();
if (numeric_value>100.0) //special case: random_error_prob>100%
{
value = "<font color=orange>KASP not performed (see NGSD)</font>";
}
else if (numeric_value>1.0) //random_error_prob>1% => warn
{
value = "<font color=red>"+QString::number(numeric_value)+"%</font>";
}
else
{
value = QString::number(numeric_value)+"%";
}
}
output.insert(QCValue("kasp", value));
return output;
}
QVariant NGSD::getValue(const QString& query, bool no_value_is_ok)
{
SqlQuery q = getQuery();
q.exec(query);
if (q.size()==0)
{
if (no_value_is_ok)
{
return QVariant();
}
else
{
THROW(DatabaseException, "NGSD single value query returned no value: " + query);
}
}
if (q.size()>1)
{
THROW(DatabaseException, "NGSD single value query returned several values: " + query);
}
q.next();
return q.value(0);
}
QModelIndex ListModel::appendChild(const QModelIndex& parent, int row, QString content)
{
if (parent.isValid() && parent.column() != 0)
return QModelIndex();
ListItem* parentItem = itemFromIndex(parent);
if (!parentItem)
return QModelIndex();
SqlQuery sql;
sql.prepare("UPDATE list_item SET weight = weight + 1 WHERE parent_id = :parent AND weight >= :row");
sql.bindValue(":list", _listId);
sql.bindValue(":parent", parentItem->id());
sql.bindValue(":row", row);
if (!sql.exec())
return QModelIndex();
sql.prepare("INSERT INTO list_item (list_id, parent_id, weight, content, created_at) VALUES (:list, :parent, :row, :content, CURRENT_TIMESTAMP)");
sql.bindValue(":list", _listId);
sql.bindValue(":parent", parentItem->id());
sql.bindValue(":row", row);
sql.bindValue(":content", content);
if (!sql.exec())
return QModelIndex();
int id = sql.lastInsertId().toInt();
beginInsertRows(parent, row, row);
ListItem* newItem = new ListItem(_listId, id, content);
if (isNewItemCheckable(parentItem, row))
newItem->setCheckable(true);
parentItem->insertChild(row, newItem);
endInsertRows();
return indexFromItem(newItem);
}
QModelIndex ListModel::_appendAfter(ListItem* item, const QString& content, App::AppendMode mode)
{
SqlQuery sql;
ListItem* parent = item->parent();
if (!parent)
return QModelIndex(); // cannot append after the root
int row = mode == App::AppendAfter ? item->row() + 1 : item->row();
sql.prepare("UPDATE list_item SET weight = weight + 1 WHERE list_id = :list AND parent_id = :parent AND weight >= :weight");
sql.bindValue(":list", _listId);
sql.bindValue(":parent", parent->id());
sql.bindValue(":weight", row);
if (!sql.exec())
return QModelIndex();
sql.prepare("INSERT INTO list_item (list_id, parent_id, weight, content, created_at) VALUES (:list, :parent, :weight, :content, CURRENT_TIMESTAMP)");
sql.bindValue(":list", _listId);
sql.bindValue(":parent", parent->id());
sql.bindValue(":weight", row);
sql.bindValue(":content", content);
if (!sql.exec())
return QModelIndex();
int id = sql.lastInsertId().toInt();
beginInsertRows(indexFromItem(parent), row, row);
ListItem* newItem = new ListItem(_listId, id, content);
parent->insertChild(row, newItem);
if (isNewItemCheckable(item->parent(), row))
newItem->setCheckable(true);
endInsertRows();
return indexFromItem(newItem);
}
void ListModel::_loadItems(ListItem* parent)
{
int parentId = parent->id();
SqlQuery sql;
sql.prepare("SELECT id, weight, content, is_expanded, is_project, is_milestone, is_highlighted, is_checkable, is_completed, is_cancelled, due_date, priority "
"FROM list_item WHERE list_id = :list AND parent_id = :parent "
"ORDER BY weight ASC");
sql.bindValue(":list", _listId);
sql.bindValue(":parent", parentId);
if (!sql.exec())
return;
int currRow = 0;
while (sql.next()) {
int c = -1;
int id = sql.value(++c).toInt();
int row = sql.value(++c).toInt();
QString content = sql.value(++c).toString();
bool isExpanded = sql.value(++c).toBool();
bool isProject = sql.value(++c).toBool();
bool isMilestone = sql.value(++c).toBool();
bool isHighlighted = sql.value(++c).toBool();
bool isCheckable = sql.value(++c).toBool();
bool isCompleted = sql.value(++c).toBool();
bool isCancelled = sql.value(++c).toBool();
QDate dueDate = sql.value(++c).toDate();
int priority = sql.value(++c).toInt();
// Fix gap between row in database
if (row != currRow) {
SqlQuery sql;
sql.prepare("UPDATE list_item SET weight = :weight WHERE id = :id");
sql.bindValue(":weight", currRow);
sql.bindValue(":id", id);
sql.exec();
}
ListItem* item = new ListItem(_listId, id, content, isExpanded, isProject, isMilestone, isHighlighted, isCheckable, isCompleted, isCancelled, dueDate, priority);
parent->appendChild(item);
_loadItems(item);
++currRow;
}
}
void get() {
this->response->body.append(blog::layout::header("Gorilla Labs"));
std::string content;
// load the library
bool loadableMod = true;
void* test = dlopen("./libtest.so", RTLD_LAZY);
if (!test) {
std::cout << "Cannot load library: " << dlerror() << std::endl;
loadableMod = false;
// reset errors
dlerror();
}
create_t* create_controller;
destroy_t* destroy_controller;
const char* dlsym_error;
if (loadableMod) {
// load the symbols
create_controller = (create_t*) dlsym(test, "create");
dlsym_error = dlerror();
if (dlsym_error) {
std::cout << "Cannot load symbol create: " << dlsym_error << std::endl;;
loadableMod = false;
}
}
if (loadableMod) {
destroy_controller = (destroy_t*) dlsym(test, "destroy");
dlsym_error = dlerror();
if (dlsym_error) {
std::cout << "Cannot load symbol destroy: " << dlsym_error << std::endl;
loadableMod = false;
}
}
// create an instance of the class
if (loadableMod) {
WebController* cont = create_controller();
cont->get();
destroy_controller(cont);
dlclose(test);
}
sqlite3* db;
int failed = sqlite3_open("utf.sql", &db);
if (failed == 0) {
SqlQuery stmt = SqlQuery(&db);
/*
stmt.exec("CREATE TABLE utf_table (\
col1 INTEGER NOT NULL,\
col2 CHAR(25) NULL,\
col3 VARCHAR(25) NULL,\
col4 NUMERIC NULL,\
col5 TEXT(25) NULL,\
PRIMARY KEY (col1),\
UNIQUE (col2)\
)");
sqlite3_exec(db, "BEGIN", 0, 0, 0);
sqlite3_exec(db, "INSERT INTO utf_table (col3) VALUES('[ˈruskʲɪj jɪˈzɨk]')", 0, 0, 0);
sqlite3_exec(db, "COMMIT", 0, 0, 0);
*/
stmt.query("SELECT col1, col3 FROM utf_table WHERE col3 = ?");
std::string title = "[ˈruskʲɪj jɪˈzɨk]";
stmt.bind(this->request->get("title"));
stmt.fetchAll();
if (!stmt.failed) {
int row_size = stmt.rows.size();
for (int i = 0; i < row_size; i++) {
SqlRow row = stmt.rows[i];
content.append("found id:" + row.get("col1") + ": " + row.get("col3") + "<br />");
}
} else {
content.append("something bad happened");
}
sqlite3_close(db);
} else {
content.append("failed to open db<br />");
}
content.append("<form method='post'><input name='title' /></form><br />" + this->request->get("title"));
this->response->body.append(content);
this->response->body.append(blog::layout::footer());
}
void NGSD::annotate(VariantList& variants, QString filename)
{
initProgress("NGSD annotation", true);
//get sample ids
QString s_id = sampleId(filename, false);
QString ps_id = processedSampleId(filename, false);
QString sys_id = getValue("SELECT processing_system_id FROM processed_sample WHERE id='" + processedSampleId(filename, false) + "'").toString();
//check if we could determine the sample
bool found_in_db = true;
if (s_id=="" || ps_id=="" || sys_id=="")
{
Log::warn("Could not find processed sample in NGSD by name '" + filename + "'. Annotation will be incomplete because processing system could not be determined!");
found_in_db = false;
}
//get sample ids that have processed samples with the same processing system (not same sample, variants imported, same processing system, good quality of sample, not tumor)
QSet<int> sys_sample_ids;
SqlQuery tmp = getQuery();
tmp.exec("SELECT DISTINCT s.id FROM processed_sample as ps, sample s WHERE ps.processing_system_id='" + sys_id + "' AND ps.sample_id=s.id AND s.tumor='0' AND s.quality='good' AND s.id!='" + s_id + "' AND (SELECT count(id) FROM detected_variant as dv WHERE dv.processed_sample_id = ps.id)>0");
while(tmp.next())
{
sys_sample_ids.insert(tmp.value(0).toInt());
}
//remove all NGSD-specific columns
QList<VariantAnnotationHeader> headers = variants.annotations();
foreach(const VariantAnnotationHeader& header, headers)
{
if (header.name().startsWith("ihdb_"))
{
removeColumnIfPresent(variants, header.name(), true);
}
}
removeColumnIfPresent(variants, "classification", true);
removeColumnIfPresent(variants, "classification_comment", true);
removeColumnIfPresent(variants, "validated", true);
removeColumnIfPresent(variants, "comment", true);
//get required column indices
QString num_samples = QString::number(sys_sample_ids.count());
int ihdb_hom_idx = addColumn(variants, "ihdb_hom", "Homozygous variant counts in NGSD for the same processing system (" + num_samples + " samples).");
int ihdb_het_idx = addColumn(variants, "ihdb_het", "Heterozyous variant counts in NGSD for the same processing system (" + num_samples + " samples).");
int ihdb_wt_idx = addColumn(variants, "ihdb_wt", "Wildtype variant counts in NGSD for the same processing system (" + num_samples + " samples).");
int ihdb_all_hom_idx = addColumn(variants, "ihdb_allsys_hom", "Homozygous variant counts in NGSD independent of the processing system.");
int ihdb_all_het_idx = addColumn(variants, "ihdb_allsys_het", "Heterozygous variant counts in NGSD independent of the processing system.");
int class_idx = addColumn(variants, "classification", "Classification from the NGSD.");
int clacom_idx = addColumn(variants, "classification_comment", "Classification comment from the NGSD.");
int valid_idx = addColumn(variants, "validated", "Validation information from the NGSD. Validation results of other samples are listed in brackets!");
if (variants.annotationIndexByName("comment", true, false)==-1) addColumn(variants, "comment", "Comments from the NGSD. Comments of other samples are listed in brackets!");
int comment_idx = variants.annotationIndexByName("comment", true, false);
//(re-)annotate the variants
SqlQuery query = getQuery();
for (int i=0; i<variants.count(); ++i)
{
//QTime timer;
//timer.start();
//variant id
Variant& v = variants[i];
QByteArray v_id = variantId(v, false).toLatin1();
//variant classification
QVariant classification = getValue("SELECT class FROM variant_classification WHERE variant_id='" + v_id + "'", true);
if (!classification.isNull())
{
v.annotations()[class_idx] = classification.toByteArray().replace("n/a", "");
v.annotations()[clacom_idx] = getValue("SELECT comment FROM variant_classification WHERE variant_id='" + v_id + "'", true).toByteArray().replace("\n", " ").replace("\t", " ");
}
//int t_v = timer.elapsed();
//timer.restart();
//detected variant infos
int dv_id = -1;
QByteArray comment = "";
if (found_in_db)
{
query.exec("SELECT id, comment FROM detected_variant WHERE processed_sample_id='" + ps_id + "' AND variant_id='" + v_id + "'");
if (query.size()==1)
{
query.next();
dv_id = query.value(0).toInt();
comment = query.value(1).toByteArray();
}
}
//validation info
int vv_id = -1;
QByteArray val_status = "";
if (found_in_db)
{
query.exec("SELECT id, status FROM variant_validation WHERE sample_id='" + s_id + "' AND variant_id='" + v_id + "'");
if (query.size()==1)
{
query.next();
vv_id = query.value(0).toInt();
val_status = query.value(1).toByteArray().replace("n/a", "");
}
}
//int t_dv = timer.elapsed();
//timer.restart();
//validation info other samples
int tps = 0;
int fps = 0;
query.exec("SELECT id, status FROM variant_validation WHERE variant_id='"+v_id+"' AND status!='n/a'");
while(query.next())
{
if (query.value(0).toInt()==vv_id) continue;
if (query.value(1).toByteArray()=="true positive") ++tps;
else if (query.value(1).toByteArray()=="false positive") ++fps;
}
if (tps>0 || fps>0)
{
if (val_status=="") val_status = "n/a";
val_status += " (" + QByteArray::number(tps) + "xTP, " + QByteArray::number(fps) + "xFP)";
}
//int t_val = timer.elapsed();
//timer.restart();
//comments other samples
QList<QByteArray> comments;
query.exec("SELECT id, comment FROM detected_variant WHERE variant_id='"+v_id+"' AND comment IS NOT NULL");
while(query.next())
{
if (query.value(0).toInt()==dv_id) continue;
QByteArray tmp = query.value(1).toByteArray().trimmed();
if (tmp!="") comments.append(tmp);
}
if (comments.size()>0)
{
if (comment=="") comment = "n/a";
comment += " (";
for (int i=0; i<comments.count(); ++i)
{
if (i>0)
{
comment += ", ";
}
comment += comments[i];
}
comment += ")";
}
//int t_com = timer.elapsed();
//timer.restart();
//genotype counts
int allsys_hom_count = 0;
int allsys_het_count = 0;
int sys_hom_count = 0;
int sys_het_count = 0;
QSet<int> s_ids_done;
int s_id_int = s_id.toInt();
query.exec("SELECT dv.genotype, ps.sample_id FROM detected_variant as dv, processed_sample ps WHERE dv.processed_sample_id=ps.id AND dv.variant_id='" + v_id + "'");
while(query.next())
{
//skip this sample id
int current_sample = query.value(1).toInt();
if (current_sample==s_id_int) continue;
//skip already seen samples (there could be several processings of the same sample because of different processing systems or because of experment repeats due to quality issues)
if (s_ids_done.contains(current_sample)) continue;
s_ids_done.insert(current_sample);
QByteArray current_geno = query.value(0).toByteArray();
if (current_geno=="hom")
{
++allsys_hom_count;
if (sys_sample_ids.contains(current_sample))
{
++sys_hom_count;
}
}
else if (current_geno=="het")
{
++allsys_het_count;
if (sys_sample_ids.contains(current_sample))
{
++sys_het_count;
}
}
}
//qDebug() << (v.isSNV() ? "S" : "I") << query.size() << t_v << t_dv << t_val << t_com << timer.elapsed();
v.annotations()[ihdb_all_hom_idx] = QByteArray::number(allsys_hom_count);
v.annotations()[ihdb_all_het_idx] = QByteArray::number(allsys_het_count);
if (found_in_db)
{
v.annotations()[ihdb_hom_idx] = QByteArray::number((double)sys_hom_count / sys_sample_ids.count(), 'f', 4);
v.annotations()[ihdb_het_idx] = QByteArray::number((double)sys_het_count / sys_sample_ids.count(), 'f', 4);
v.annotations()[ihdb_wt_idx] = QByteArray::number((double)(sys_sample_ids.count() - sys_hom_count - sys_het_count) / sys_sample_ids.count(), 'f', 4);
v.annotations()[valid_idx] = val_status;
v.annotations()[comment_idx] = comment.replace("\n", " ").replace("\t", " ");
}
else
{
v.annotations()[ihdb_hom_idx] = "n/a";
v.annotations()[ihdb_het_idx] = "n/a";
v.annotations()[ihdb_wt_idx] = "n/a";
v.annotations()[valid_idx] = "n/a";
v.annotations()[comment_idx] = "n/a";
}
emit updateProgress(100*i/variants.count());
}
}
QPair<QByteArray, QByteArray> NGSD::geneToApproved(const QByteArray& gene)
{
//init
static SqlQuery q_gene = getQuery(true);
static SqlQuery q_prev = getQuery(true);
static SqlQuery q_syn = getQuery(true);
static bool init = false;
if (!init)
{
q_gene.prepare("SELECT id FROM gene WHERE symbol=:1");
q_prev.prepare("SELECT g.symbol FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='previous'");
q_syn.prepare("SELECT g.symbol FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='synonym'");
init = true;
}
//approved
q_gene.bindValue(0, gene);
q_gene.exec();
if (q_gene.size()==1)
{
q_gene.next();
return qMakePair(gene, QByteArray("KEPT: is approved symbol"));
}
//previous
q_prev.bindValue(0, gene);
q_prev.exec();
if (q_prev.size()==1)
{
q_prev.next();
return qMakePair(q_prev.value(0).toByteArray(), "REPLACED: " + gene + " is a previous symbol");
}
else if(q_prev.size()>1)
{
QByteArray genes;
while(q_prev.next())
{
if (!genes.isEmpty()) genes.append(", ");
genes.append(q_prev.value(0).toByteArray());
}
return qMakePair(gene, "ERROR: is a previous symbol of the genes " + genes);
}
//synonymous
q_syn.bindValue(0, gene);
q_syn.exec();
if (q_syn.size()==1)
{
q_syn.next();
return qMakePair(q_syn.value(0).toByteArray(), "REPLACED: " + gene + " is a synonymous symbol");
}
else if(q_syn.size()>1)
{
QByteArray genes;
while(q_syn.next())
{
if (!genes.isEmpty()) genes.append(", ");
genes.append(q_syn.value(0).toByteArray());
}
return qMakePair(gene, "ERROR: is a synonymous symbol of the genes " + genes);
}
return qMakePair(gene, QByteArray("ERROR: is unknown symbol"));
}
int NGSD::geneToApprovedID(const QByteArray& gene)
{
//init
static SqlQuery q_gene = getQuery(true);
static SqlQuery q_prev = getQuery(true);
static SqlQuery q_syn = getQuery(true);
static bool init = false;
if (!init)
{
q_gene.prepare("SELECT id FROM gene WHERE symbol=:1");
q_prev.prepare("SELECT g.id FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='previous'");
q_syn.prepare("SELECT g.id FROM gene g, gene_alias ga WHERE g.id=ga.gene_id AND ga.symbol=:1 AND ga.type='synonym'");
init = true;
}
//approved
q_gene.bindValue(0, gene);
q_gene.exec();
if (q_gene.size()==1)
{
q_gene.next();
return q_gene.value(0).toInt();
}
//previous
q_prev.bindValue(0, gene);
q_prev.exec();
if (q_prev.size()==1)
{
q_prev.next();
return q_prev.value(0).toInt();
}
else if(q_prev.size()>1)
{
return -1;
}
//synonymous
q_syn.bindValue(0, gene);
q_syn.exec();
if (q_syn.size()==1)
{
q_syn.next();
return q_syn.value(0).toInt();
}
return -1;
}
void NGSD::annotateSomatic(VariantList& variants, QString filename)
{
//get sample ids
QStringList samples = filename.split('-');
QString ts_id = sampleId(samples[0], false);
//check if we could determine the sample
if (ts_id=="")
{
Log::warn("Could not find processed sample in NGSD from name '" + QFileInfo(filename).baseName() + "'. Annotation will be incomplete because processing system could not be determined!");
}
//remove all NGSD-specific columns
QList<VariantAnnotationHeader> headers = variants.annotations();
foreach(const VariantAnnotationHeader& header, headers)
{
if (header.name().startsWith("som_ihdb"))
{
removeColumnIfPresent(variants, header.name(), true);
}
}
//get required column indices
int som_ihdb_c_idx = addColumn(variants, "som_ihdb_c", "Somatic variant count within NGSD.");
int som_ihdb_p_idx = addColumn(variants, "som_ihdb_p", "Projects with somatic variant in NGSD.");
//(re-)annotate the variants
for (int i=0; i<variants.count(); ++i)
{
Variant& v = variants[i];
SqlQuery query = getQuery();
query.exec("SELECT s.id, dsv.processed_sample_id_tumor, p.name FROM detected_somatic_variant as dsv, variant as v, processed_sample ps, sample as s, project as p WHERE ps.project_id=p.id AND dsv.processed_sample_id_tumor=ps.id and dsv.variant_id=v.id AND ps.sample_id=s.id AND s.tumor='1' AND v.chr='"+v.chr().str()+"' AND v.start='"+QString::number(v.start())+"' AND v.end='"+QString::number(v.end())+"' AND v.ref='"+v.ref()+"' AND v.obs='"+v.obs()+"'");
//process variants
QMap<QByteArray, int> project_map;
QSet<QByteArray> processed_ps_ids;
QSet<QByteArray> processed_s_ids;
while(query.next())
{
QByteArray current_sample = query.value(0).toByteArray();
QByteArray current_ps_id = query.value(1).toByteArray();
QByteArray current_project = query.value(2).toByteArray();
//skip already seen processed samples (there could be several variants because of indel window, but we want to process only one)
if (processed_ps_ids.contains(current_ps_id)) continue;
processed_ps_ids.insert(current_ps_id);
//skip the current sample for general statistics
if (current_sample==ts_id) continue;
//skip already seen samples for general statistics (there could be several processings of the same sample because of different processing systems or because of experment repeats due to quality issues)
if (processed_s_ids.contains(current_sample)) continue;
processed_s_ids.insert(current_sample);
// count
if(!project_map.contains(current_project)) project_map.insert(current_project,0);
++project_map[current_project];
}
QByteArray somatic_projects;
int somatic_count = 0;
QMap<QByteArray, int>::const_iterator j = project_map.constBegin();
while(j!=project_map.constEnd())
{
somatic_count += j.value();
somatic_projects += j.key() + ",";
++j;
}
v.annotations()[som_ihdb_c_idx] = QByteArray::number(somatic_count);
v.annotations()[som_ihdb_p_idx] = somatic_projects;
}
}
