CTK/Libs/DICOM/Core/ctkDICOMDatabase.cpp

/*=========================================================================

  Library:   CTK

  Copyright (c) Kitware Inc.

  Licensed under the Apache License, Version 2.0 (the "License");
  you may not use this file except in compliance with the License.
  You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0.txt

  Unless required by applicable law or agreed to in writing, software
  distributed under the License is distributed on an "AS IS" BASIS,
  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  See the License for the specific language governing permissions and
  limitations under the License.

=========================================================================*/

#include <stdexcept>

// Qt includes
#include <QSqlQuery>
#include <QSqlRecord>
#include <QSqlError>
#include <QVariant>
#include <QDate>
#include <QStringList>
#include <QSet>
#include <QFile>
#include <QDirIterator>
#include <QFileInfo>
#include <QDebug>
#include <QFileSystemWatcher>

// ctkDICOM includes
#include "ctkDICOMDatabase.h"
#include "ctkDICOMAbstractThumbnailGenerator.h"

#include "ctkLogger.h"

// DCMTK includes
#include <dcmtk/dcmdata/dcfilefo.h>
#include <dcmtk/dcmdata/dcfilefo.h>
#include <dcmtk/dcmdata/dcdeftag.h>
#include <dcmtk/dcmdata/dcdatset.h>
#include <dcmtk/ofstd/ofcond.h>
#include <dcmtk/ofstd/ofstring.h>
#include <dcmtk/ofstd/ofstd.h>        /* for class OFStandard */
#include <dcmtk/dcmdata/dcddirif.h>   /* for class DicomDirInterface */
#include <dcmimage.h>

#include <dcmtk/dcmjpeg/djdecode.h>  /* for dcmjpeg decoders */
#include <dcmtk/dcmjpeg/djencode.h>  /* for dcmjpeg encoders */
#include <dcmtk/dcmdata/dcrledrg.h>  /* for DcmRLEDecoderRegistration */
#include <dcmtk/dcmdata/dcrleerg.h>  /* for DcmRLEEncoderRegistration */

//------------------------------------------------------------------------------
static ctkLogger logger("org.commontk.dicom.DICOMDatabase" );
//------------------------------------------------------------------------------

//------------------------------------------------------------------------------
class ctkDICOMDatabasePrivate
{
  Q_DECLARE_PUBLIC(ctkDICOMDatabase);
protected:
  ctkDICOMDatabase* const q_ptr;

public:
  ctkDICOMDatabasePrivate(ctkDICOMDatabase&);
  ~ctkDICOMDatabasePrivate();
  void init(QString databaseFile);
  void registerCompressionLibraries();
  bool executeScript(const QString script);
  ///
  /// \brief runs a query and prints debug output of status
  ///
  bool loggedExec(QSqlQuery& query);
  bool loggedExec(QSqlQuery& query, const QString& queryString);

  /// Name of the database file (i.e. for SQLITE the sqlite file)
  QString      DatabaseFileName;
  QString      LastError;
  QSqlDatabase Database;
  QMap<QString, QString> LoadedHeader;

  ctkDICOMAbstractThumbnailGenerator* thumbnailGenerator;
  
  /// these are for optimizing the import of image sequences
  /// since most information are identical for all slices
  OFString lastPatientID;
  OFString lastPatientsName;
  OFString lastPatientsBirthDate;
  OFString lastStudyInstanceUID;
  OFString lastSeriesInstanceUID;
  int lastPatientUID;
};

//------------------------------------------------------------------------------
// ctkDICOMDatabasePrivate methods

//------------------------------------------------------------------------------
ctkDICOMDatabasePrivate::ctkDICOMDatabasePrivate(ctkDICOMDatabase& o): q_ptr(&o)
{
    this->thumbnailGenerator = NULL;
}

//------------------------------------------------------------------------------
void ctkDICOMDatabasePrivate::init(QString databaseFilename)
{
  Q_Q(ctkDICOMDatabase);

  q->openDatabase(databaseFilename);
}

//------------------------------------------------------------------------------
void ctkDICOMDatabasePrivate::registerCompressionLibraries(){
    logger.debug("Register compression libraries");
    // Register the JPEG libraries in case we need them
    //   (registration only happens once, so it's okay to call repeatedly)
    // register global JPEG decompression codecs
    DJDecoderRegistration::registerCodecs();
    // register global JPEG compression codecs
    DJEncoderRegistration::registerCodecs();
    // register RLE compression codec
    DcmRLEEncoderRegistration::registerCodecs();
    // register RLE decompression codec
    DcmRLEDecoderRegistration::registerCodecs();
}

//------------------------------------------------------------------------------
ctkDICOMDatabasePrivate::~ctkDICOMDatabasePrivate()
{
}

//------------------------------------------------------------------------------
bool ctkDICOMDatabasePrivate::loggedExec(QSqlQuery& query)
{
  return (loggedExec(query, QString("")));
}

//------------------------------------------------------------------------------
bool ctkDICOMDatabasePrivate::loggedExec(QSqlQuery& query, const QString& queryString)
{
  bool success;
  if (queryString.compare(""))
    {
    success = query.exec(queryString);
    }
  else
    {
    success = query.exec();
    }
  if (!success)
    {
    QSqlError sqlError = query.lastError();
    logger.debug( "SQL failed\n Bad SQL: " + query.lastQuery());
    logger.debug( "Error text: " + sqlError.text());
    }
  else
    {
  logger.debug( "SQL worked!\n SQL: " + query.lastQuery());
    }
  return (success);
}

//------------------------------------------------------------------------------
void ctkDICOMDatabase::openDatabase(const QString databaseFile, const QString& connectionName )
{
  Q_D(ctkDICOMDatabase);
  d->DatabaseFileName = databaseFile;
  d->Database = QSqlDatabase::addDatabase("QSQLITE", connectionName);
  d->Database.setDatabaseName(databaseFile);
  if ( ! (d->Database.open()) )
    {
    d->LastError = d->Database.lastError().text();
    return;
    }
  if ( d->Database.tables().empty() )
    {
    if (!initializeDatabase())
      {
      d->LastError = QString("Unable to initialize DICOM database!");
      return;
      }
    }
  if (!isInMemory())
    {
    QFileSystemWatcher* watcher = new QFileSystemWatcher(QStringList(databaseFile),this);
    connect(watcher, SIGNAL(fileChanged(QString)),this, SIGNAL (databaseChanged()) );
    }
}



//------------------------------------------------------------------------------
// ctkDICOMDatabase methods

//------------------------------------------------------------------------------
ctkDICOMDatabase::ctkDICOMDatabase(QString databaseFile)
   : d_ptr(new ctkDICOMDatabasePrivate(*this))
{
  Q_D(ctkDICOMDatabase);
  d->registerCompressionLibraries();
  d->init(databaseFile);
}

ctkDICOMDatabase::ctkDICOMDatabase(QObject* parent)
   : d_ptr(new ctkDICOMDatabasePrivate(*this))
{
    Q_UNUSED(parent);
    Q_D(ctkDICOMDatabase);
    d->registerCompressionLibraries();
}

//------------------------------------------------------------------------------
ctkDICOMDatabase::~ctkDICOMDatabase()
{
}

//----------------------------------------------------------------------------

//------------------------------------------------------------------------------
const QString ctkDICOMDatabase::lastError() const {
  Q_D(const ctkDICOMDatabase);
  return d->LastError;
}

//------------------------------------------------------------------------------
const QString ctkDICOMDatabase::databaseFilename() const {
  Q_D(const ctkDICOMDatabase);
  return d->DatabaseFileName;
}

//------------------------------------------------------------------------------
const QString ctkDICOMDatabase::databaseDirectory() const {
  QString databaseFile = databaseFilename();
  if (!QFileInfo(databaseFile).isAbsolute())
    {
    databaseFile.prepend(QDir::currentPath() + "/");
    }
  return QFileInfo ( databaseFile ).absoluteDir().path();
}

//------------------------------------------------------------------------------
const QSqlDatabase& ctkDICOMDatabase::database() const {
  Q_D(const ctkDICOMDatabase);
  return d->Database;
}

void ctkDICOMDatabase::setThumbnailGenerator(ctkDICOMAbstractThumbnailGenerator *generator){
    Q_D(ctkDICOMDatabase);
    d->thumbnailGenerator = generator;
}

ctkDICOMAbstractThumbnailGenerator* ctkDICOMDatabase::thumbnailGenerator(){
    Q_D(const ctkDICOMDatabase);
    return d->thumbnailGenerator;
}

//------------------------------------------------------------------------------
bool ctkDICOMDatabasePrivate::executeScript(const QString script) {
  QFile scriptFile(script);
  scriptFile.open(QIODevice::ReadOnly);
  if  ( !scriptFile.isOpen() )
    {
    qDebug() << "Script file " << script << " could not be opened!\n";
    return false;
    }

  QString sqlCommands( QTextStream(&scriptFile).readAll() );
  sqlCommands.replace( '\n', ' ' );
  sqlCommands.remove( '\r' );
  sqlCommands.replace("; ", ";\n");

  QStringList sqlCommandsLines = sqlCommands.split('\n');

  QSqlQuery query(Database);

  for (QStringList::iterator it = sqlCommandsLines.begin(); it != sqlCommandsLines.end()-1; ++it)
  {
    if (! (*it).startsWith("--") )
      {
      qDebug() << *it << "\n";
      query.exec(*it);
      if (query.lastError().type())
        {
        qDebug() << "There was an error during execution of the statement: " << (*it);
        qDebug() << "Error message: " << query.lastError().text();
        return false;
        }
      }
  }
  return true;
}

//------------------------------------------------------------------------------
bool ctkDICOMDatabase::initializeDatabase(const char* sqlFileName)
{
  Q_D(ctkDICOMDatabase);
  return d->executeScript(sqlFileName);
}

//------------------------------------------------------------------------------
void ctkDICOMDatabase::closeDatabase()
{
  Q_D(ctkDICOMDatabase);
  d->Database.close();
}

//------------------------------------------------------------------------------
QStringList ctkDICOMDatabase::patients()
{
  Q_D(ctkDICOMDatabase);
  QSqlQuery query(d->Database);
  query.prepare ( "SELECT UID FROM Patients" );
  query.exec();
  QStringList result;
  while (query.next()) 
    {
    result << query.value(0).toString();
    }
  return( result );
}

//------------------------------------------------------------------------------
QStringList ctkDICOMDatabase::studiesForPatient(QString patientUID)
{
  Q_D(ctkDICOMDatabase);
  QSqlQuery query(d->Database);
  query.prepare ( "SELECT StudyInstanceUID FROM Studies WHERE PatientsUID = ?" );
  query.bindValue ( 0, patientUID );
  query.exec();
  QStringList result;
  while (query.next()) 
    {
    result << query.value(0).toString();
    }
  return( result );
}

//------------------------------------------------------------------------------
QStringList ctkDICOMDatabase::seriesForStudy(QString studyUID)
{
  Q_D(ctkDICOMDatabase);
  QSqlQuery query(d->Database);
  query.prepare ( "SELECT SeriesInstanceUID FROM Series WHERE StudyInstanceUID=?");
  query.bindValue ( 0, studyUID );
  query.exec();
  QStringList result;
  while (query.next()) 
    {
    result << query.value(0).toString();
    }
  return( result );
}

//------------------------------------------------------------------------------
QStringList ctkDICOMDatabase::filesForSeries(QString seriesUID)
{
  Q_D(ctkDICOMDatabase);
  QSqlQuery query(d->Database);
  query.prepare ( "SELECT Filename FROM Images WHERE SeriesInstanceUID=?");
  query.bindValue ( 0, seriesUID );
  query.exec();
  QStringList result;
  while (query.next()) 
    {
    result << query.value(0).toString();
    }
  return( result );
}

//------------------------------------------------------------------------------
void ctkDICOMDatabase::loadInstanceHeader (QString sopInstanceUID)
{
  Q_D(ctkDICOMDatabase);
  QSqlQuery query(d->Database);
  query.prepare ( "SELECT Filename FROM Images WHERE SOPInstanceUID=?");
  query.bindValue ( 0, sopInstanceUID );
  query.exec();
  d->LoadedHeader.clear();
  if (query.next())
    {
    QString fileName = query.value(0).toString();
    this->loadFileHeader(fileName);
    }
  return;
}

//------------------------------------------------------------------------------
void ctkDICOMDatabase::loadFileHeader (QString fileName)
{
  Q_D(ctkDICOMDatabase);
  DcmFileFormat fileFormat;
  OFCondition status = fileFormat.loadFile(fileName.toLatin1().data());
  if (status.good())
    {
    DcmDataset *dataset = fileFormat.getDataset();
    DcmStack stack;
    while (dataset->nextObject(stack, true) == EC_Normal)
      {
      DcmObject *dO = stack.top();
      if (dO->isaString())
        {
        QString tag = QString("%1,%2").arg(
            dO->getGTag(),4,16,QLatin1Char('0')).arg(
            dO->getETag(),4,16,QLatin1Char('0'));
        std::ostringstream s;
        dO->print(s);
        d->LoadedHeader[tag] = QString(s.str().c_str());
        }
      }
    }
  return;
}

//------------------------------------------------------------------------------
QStringList ctkDICOMDatabase::headerKeys ()
{
  Q_D(ctkDICOMDatabase);
  return (d->LoadedHeader.keys());
}

//------------------------------------------------------------------------------
QString ctkDICOMDatabase::headerValue (QString key)
{
  Q_D(ctkDICOMDatabase);
  return (d->LoadedHeader[key]);
}

//------------------------------------------------------------------------------
/*
void ctkDICOMDatabase::insert ( DcmDataset *dataset ) {
  this->insert ( dataset, QString() );
}
*/

//------------------------------------------------------------------------------
void ctkDICOMDatabase::insert ( DcmDataset *dataset, bool storeFile, bool generateThumbnail)
{
  Q_D(ctkDICOMDatabase);

  if (!dataset)
    {
    return;
    }
  // Check to see if the file has already been loaded
  OFString sopInstanceUID ;
  dataset->findAndGetOFString(DCM_SOPInstanceUID, sopInstanceUID);

  QSqlQuery fileExists ( d->Database );
  fileExists.prepare("SELECT InsertTimestamp,Filename FROM Images WHERE SOPInstanceUID == ?");
  fileExists.bindValue(0,QString(sopInstanceUID.c_str()));
  fileExists.exec();
  if ( fileExists.next() && QFileInfo(fileExists.value(1).toString()).lastModified() < QDateTime::fromString(fileExists.value(0).toString(),Qt::ISODate) )
    {
    logger.debug ( "File " + fileExists.value(1).toString() + " already added" );
    return;
    }

  OFString patientsName, patientID, patientsBirthDate, patientsBirthTime, patientsSex,
    patientComments, patientsAge;

  OFString studyInstanceUID, studyID, studyDate, studyTime,
    accessionNumber, modalitiesInStudy, institutionName, performingPhysiciansName, referringPhysician, studyDescription;

  OFString seriesInstanceUID, seriesDate, seriesTime,
    seriesDescription, bodyPartExamined, frameOfReferenceUID,
    contrastAgent, scanningSequence;
  OFString instanceNumber;

  Sint32 seriesNumber = 0, acquisitionNumber = 0, echoNumber = 0, temporalPosition = 0;

  //If the following fields can not be evaluated, cancel evaluation of the DICOM file
  dataset->findAndGetOFString(DCM_PatientName, patientsName);
  dataset->findAndGetOFString(DCM_StudyInstanceUID, studyInstanceUID);
  dataset->findAndGetOFString(DCM_SeriesInstanceUID, seriesInstanceUID);
  dataset->findAndGetOFString(DCM_PatientID, patientID);


  dataset->findAndGetOFString(DCM_PatientBirthDate, patientsBirthDate);
  dataset->findAndGetOFString(DCM_PatientBirthTime, patientsBirthTime);
  dataset->findAndGetOFString(DCM_PatientSex, patientsSex);
  dataset->findAndGetOFString(DCM_PatientAge, patientsAge);
  dataset->findAndGetOFString(DCM_PatientComments, patientComments);
  dataset->findAndGetOFString(DCM_StudyID, studyID);
  dataset->findAndGetOFString(DCM_StudyDate, studyDate);
  dataset->findAndGetOFString(DCM_StudyTime, studyTime);
  dataset->findAndGetOFString(DCM_AccessionNumber, accessionNumber);
  dataset->findAndGetOFString(DCM_ModalitiesInStudy, modalitiesInStudy);
  dataset->findAndGetOFString(DCM_InstitutionName, institutionName);
  dataset->findAndGetOFString(DCM_PerformingPhysicianName, performingPhysiciansName);
  dataset->findAndGetOFString(DCM_ReferringPhysicianName, referringPhysician);
  dataset->findAndGetOFString(DCM_StudyDescription, studyDescription);

  dataset->findAndGetOFString(DCM_SeriesDate, seriesDate);
  dataset->findAndGetOFString(DCM_SeriesTime, seriesTime);
  dataset->findAndGetOFString(DCM_SeriesDescription, seriesDescription);
  dataset->findAndGetOFString(DCM_BodyPartExamined, bodyPartExamined);
  dataset->findAndGetOFString(DCM_FrameOfReferenceUID, frameOfReferenceUID);
  dataset->findAndGetOFString(DCM_ContrastBolusAgent, contrastAgent);
  dataset->findAndGetOFString(DCM_ScanningSequence, scanningSequence);

  dataset->findAndGetSint32(DCM_SeriesNumber, seriesNumber);
  dataset->findAndGetSint32(DCM_AcquisitionNumber, acquisitionNumber);
  dataset->findAndGetSint32(DCM_EchoNumbers, echoNumber);
  dataset->findAndGetSint32(DCM_TemporalPositionIdentifier, temporalPosition);

  // store the file if the database is not in memomry
  QString filename;
  if ( storeFile && !this->isInMemory() )
  {
    DcmFileFormat* fileformat = new DcmFileFormat ( dataset );

    QString destinationDirectoryName = databaseDirectory() + "/dicom/";
    QDir destinationDir(destinationDirectoryName);
    QString studySeriesDirectory = QString(studyInstanceUID.c_str()) + "/" + seriesInstanceUID.c_str();
    destinationDir.mkpath(studySeriesDirectory);

    filename = databaseDirectory() + "/dicom/" + pathForDataset(dataset);
    logger.debug ( "Saving file: " + filename );
    OFCondition status = fileformat->saveFile ( filename.toAscii() );
    if ( !status.good() )
      {
      logger.error ( "Error saving file: " + filename + "\nError is " + status.text() );
      delete fileformat;
      return;
      }
    delete fileformat;
  }


  QSqlQuery check_exists_query(d->Database);
  //The patient UID is a unique number within the database, generated by the sqlite autoincrement
  int patientUID = -1;
  if ( patientID != "" && patientsName != "" )
    {
    //Check if patient is already present in the db
    check_exists_query.prepare ( "SELECT * FROM Patients WHERE PatientID = ? AND PatientsName = ?" );
    check_exists_query.bindValue ( 0, QString ( patientID.c_str() ) );
    check_exists_query.bindValue ( 1, QString ( patientsName.c_str() ) );
    check_exists_query.exec();
    
    if (check_exists_query.next())
      {
      patientUID = check_exists_query.value(check_exists_query.record().indexOf("UID")).toInt();
      }
    else
      {
      // Insert it
      QSqlQuery statement ( d->Database );
      statement.prepare ( "INSERT INTO Patients ('UID', 'PatientsName', 'PatientID', 'PatientsBirthDate', 'PatientsBirthTime', 'PatientsSex', 'PatientsAge', 'PatientsComments' ) values ( NULL, ?, ?, ?, ?, ?, ?, ? )" );
      statement.bindValue ( 0, QString ( patientsName.c_str() ) );
      statement.bindValue ( 1, QString ( patientID.c_str() ) );
      statement.bindValue ( 2, QString ( patientsBirthDate.c_str() ) );
      statement.bindValue ( 3, QString ( patientsBirthTime.c_str() ) );
      statement.bindValue ( 4, QString ( patientsSex.c_str() ) );
      // TODO: shift patient's age to study, since this is not a patient level attribute in images
      // statement.bindValue ( 5, QString ( patientsAge.c_str() ) );
      statement.bindValue ( 6, QString ( patientComments.c_str() ) );
      statement.exec ();
      patientUID = statement.lastInsertId().toInt();
      logger.debug ( "New patient inserted: " + QString().setNum ( patientUID ) );
      }
    }

  if ( studyInstanceUID != "" )
    {
    check_exists_query.prepare ( "SELECT * FROM Studies WHERE StudyInstanceUID = ?" );
    check_exists_query.bindValue ( 0, QString ( studyInstanceUID.c_str() ) );
    check_exists_query.exec();
    if(!check_exists_query.next())
      {
      QSqlQuery statement ( d->Database );
      statement.prepare ( "INSERT INTO Studies ( 'StudyInstanceUID', 'PatientsUID', 'StudyID', 'StudyDate', 'StudyTime', 'AccessionNumber', 'ModalitiesInStudy', 'InstitutionName', 'ReferringPhysician', 'PerformingPhysiciansName', 'StudyDescription' ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
      statement.bindValue ( 0, QString ( studyInstanceUID.c_str() ) );
      statement.bindValue ( 1, patientUID );
      statement.bindValue ( 2, QString ( studyID.c_str() ) );
      statement.bindValue ( 3, QDate::fromString ( studyDate.c_str(), "yyyyMMdd" ) );
      statement.bindValue ( 4, QString ( studyTime.c_str() ) );
      statement.bindValue ( 5, QString ( accessionNumber.c_str() ) );
      statement.bindValue ( 6, QString ( modalitiesInStudy.c_str() ) );
      statement.bindValue ( 7, QString ( institutionName.c_str() ) );
      statement.bindValue ( 8, QString ( referringPhysician.c_str() ) );
      statement.bindValue ( 9, QString ( performingPhysiciansName.c_str() ) );
      statement.bindValue ( 10, QString ( studyDescription.c_str() ) );
      if ( !statement.exec() )
        {
        logger.error ( "Error executing statament: " + statement.lastQuery() + " Error: " + statement.lastError().text() );
        }
      }
    }

  if ( seriesInstanceUID != "" )
    {
    check_exists_query.prepare ( "SELECT * FROM Series WHERE SeriesInstanceUID = ?" );
    check_exists_query.bindValue ( 0, QString ( seriesInstanceUID.c_str() ) );
    logger.warn ( "Statement: " + check_exists_query.lastQuery() );
    check_exists_query.exec();
    if(!check_exists_query.next())
      {
      QSqlQuery statement ( d->Database );
      statement.prepare ( "INSERT INTO Series ( 'SeriesInstanceUID', 'StudyInstanceUID', 'SeriesNumber', 'SeriesDate', 'SeriesTime', 'SeriesDescription', 'BodyPartExamined', 'FrameOfReferenceUID', 'AcquisitionNumber', 'ContrastAgent', 'ScanningSequence', 'EchoNumber', 'TemporalPosition' ) VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? )" );
      statement.bindValue ( 0, QString ( seriesInstanceUID.c_str() ) );
      statement.bindValue ( 1, QString ( studyInstanceUID.c_str() ) );
      statement.bindValue ( 2, static_cast<int>(seriesNumber) );
      statement.bindValue ( 3, QString ( seriesDate.c_str() ) );
      statement.bindValue ( 4, QDate::fromString ( seriesTime.c_str(), "yyyyMMdd" ) );
      statement.bindValue ( 5, QString ( seriesDescription.c_str() ) );
      statement.bindValue ( 6, QString ( bodyPartExamined.c_str() ) );
      statement.bindValue ( 7, QString ( frameOfReferenceUID.c_str() ) );
      statement.bindValue ( 8, static_cast<int>(acquisitionNumber) );
      statement.bindValue ( 9, QString ( contrastAgent.c_str() ) );
      statement.bindValue ( 10, QString ( scanningSequence.c_str() ) );
      statement.bindValue ( 11, static_cast<int>(echoNumber) );
      statement.bindValue ( 12, static_cast<int>(temporalPosition) );
      if ( !statement.exec() )
        {
        logger.error ( "Error executing statament: " + statement.lastQuery() + " Error: " + statement.lastError().text() );
        }
      }
    }
  if ( !filename.isEmpty() )
    {
    check_exists_query.prepare ( "SELECT * FROM Images WHERE Filename = ?" );
    check_exists_query.bindValue ( 0, filename );
    check_exists_query.exec();
    if(!check_exists_query.next())
      {
      QSqlQuery statement ( d->Database );
      statement.prepare ( "INSERT INTO Images ( 'SOPInstanceUID', 'Filename', 'SeriesInstanceUID', 'InsertTimestamp' ) VALUES ( ?, ?, ?, ? )" );
      statement.bindValue ( 0, QString ( sopInstanceUID.c_str() ) );
      statement.bindValue ( 1, filename );
      statement.bindValue ( 2, QString ( seriesInstanceUID.c_str() ) );
      statement.bindValue ( 3, QDateTime::currentDateTime() );
      statement.exec();
      }
    }

  if(generateThumbnail){
      if(d->thumbnailGenerator){
        QString studySeriesDirectory = QString(studyInstanceUID.c_str()) + "/" + QString(seriesInstanceUID.c_str());
        //Create thumbnail here
        QString thumbnailPath = databaseDirectory() +
                            "/thumbs/" + this->pathForDataset(dataset) + ".png";
                            //QString(studyInstanceUID.c_str()) + "/" +
                            //QString(seriesInstanceUID.c_str()) + "/" +
                            //QString(sopInstanceUID.c_str()) + ".png";
        QFileInfo thumbnailInfo(thumbnailPath);
        if(!(thumbnailInfo.exists() && (thumbnailInfo.lastModified() > QFileInfo(filename).lastModified()))){
            QDir(databaseDirectory() + "/thumbs/").mkpath(studySeriesDirectory);
            DicomImage dcmImage(QDir::toNativeSeparators(filename).toAscii());
            d->thumbnailGenerator->generateThumbnail(&dcmImage, thumbnailPath);
        }
      }
  }

  if (isInMemory())
    {
      emit databaseChanged();
    }
}

//------------------------------------------------------------------------------
void ctkDICOMDatabase::insert ( const QString& filePath, bool storeFile, bool generateThumbnail, bool createHierarchy, const QString& destinationDirectoryName)
{
  Q_D(ctkDICOMDatabase);
  
  /// first we check if the file is already in the database
  if (fileExistsAndUpToDate(filePath))
  {
    logger.debug( "File " + filePath + " already added.");
    return;
  }

  logger.debug( "Processing " + filePath ); 

  std::string filename = filePath.toStdString();

  DcmFileFormat fileformat;
  DcmDataset *dataset;

  OFCondition status = fileformat.loadFile(filename.c_str());

  dataset = fileformat.getDataset();

  if (!status.good())
  {
    logger.error( "Could not load " + filePath );
    logger.error( "DCMTK says: " + QString(status.text()) );
    return;
  }
  
  // ok, we have loaded a dataset


  OFString patientsName, patientID, patientsBirthDate, patientsBirthTime, patientsSex,
    patientComments, patientsAge;

  OFString studyInstanceUID, studyID, studyDate, studyTime,
    accessionNumber, modalitiesInStudy, institutionName, performingPhysiciansName, referringPhysician, studyDescription;

  OFString seriesInstanceUID, seriesDate, seriesTime,
    seriesDescription, bodyPartExamined, frameOfReferenceUID,
    contrastAgent, scanningSequence;
  OFString instanceNumber, sopInstanceUID ;

  Sint32 seriesNumber = 0, acquisitionNumber = 0, echoNumber = 0, temporalPosition = 0;

  //The patient UID is a unique number within the database, generated by the sqlite autoincrement
  //Thus, this is _not_ the DICOM Patient ID.
  int patientUID = -1;

  //If the following fields can not be evaluated, cancel evaluation of the DICOM file
  if (!dataset->findAndGetOFString(DCM_PatientName, patientsName).good())
  {
    logger.error( "Could not read DCM_PatientName from " + filePath );
    return;
  }

  if (!dataset->findAndGetOFString(DCM_StudyInstanceUID, studyInstanceUID).good())
  {
    logger.error( "Could not read DCM_StudyInstanceUID from " + filePath );
    return;
  }

  if (!dataset->findAndGetOFString(DCM_SeriesInstanceUID, seriesInstanceUID).good())
  {
    logger.error( "Could not read DCM_SeriesInstanceUID from " + filePath );
    return;
  }

  if (!dataset->findAndGetOFString(DCM_SOPInstanceUID, sopInstanceUID).good())
  {
    logger.error( "Could not read DCM_SOPInstanceUID from " + filePath );
    return;
  }
  if (!dataset->findAndGetOFString(DCM_InstanceNumber, instanceNumber).good())
  {
    logger.error( "Could not read DCM_InstanceNumber from " + filePath );
    return;
  }


  dataset->findAndGetOFString(DCM_PatientID, patientID);
  dataset->findAndGetOFString(DCM_PatientBirthDate, patientsBirthDate);
  dataset->findAndGetOFString(DCM_PatientBirthTime, patientsBirthTime);
  dataset->findAndGetOFString(DCM_PatientSex, patientsSex);
  dataset->findAndGetOFString(DCM_PatientAge, patientsAge);
  dataset->findAndGetOFString(DCM_PatientComments, patientComments);
  dataset->findAndGetOFString(DCM_StudyID, studyID);
  dataset->findAndGetOFString(DCM_StudyDate, studyDate);
  dataset->findAndGetOFString(DCM_StudyTime, studyTime);
  dataset->findAndGetOFString(DCM_AccessionNumber, accessionNumber);
  dataset->findAndGetOFString(DCM_ModalitiesInStudy, modalitiesInStudy);
  dataset->findAndGetOFString(DCM_InstitutionName, institutionName);
  dataset->findAndGetOFString(DCM_PerformingPhysicianName, performingPhysiciansName);
  dataset->findAndGetOFString(DCM_ReferringPhysicianName, referringPhysician);
  dataset->findAndGetOFString(DCM_StudyDescription, studyDescription);

  dataset->findAndGetOFString(DCM_SeriesDate, seriesDate);
  dataset->findAndGetOFString(DCM_SeriesTime, seriesTime);
  dataset->findAndGetOFString(DCM_SeriesDescription, seriesDescription);
  dataset->findAndGetOFString(DCM_BodyPartExamined, bodyPartExamined);
  dataset->findAndGetOFString(DCM_FrameOfReferenceUID, frameOfReferenceUID);
  dataset->findAndGetOFString(DCM_ContrastBolusAgent, contrastAgent);
  dataset->findAndGetOFString(DCM_ScanningSequence, scanningSequence);

  dataset->findAndGetSint32(DCM_SeriesNumber, seriesNumber);
  dataset->findAndGetSint32(DCM_AcquisitionNumber, acquisitionNumber);
  dataset->findAndGetSint32(DCM_EchoNumbers, echoNumber);
  dataset->findAndGetSint32(DCM_TemporalPositionIdentifier, temporalPosition);

  logger.debug( "Adding new items to database:" );
  logger.debug( "studyID: " + QString(studyID.c_str()) );
  logger.debug( "seriesInstanceUID: " + QString(seriesInstanceUID.c_str()) );
  logger.debug( "Patient's Name: " + QString(patientsName.c_str()) );

  //-----------------------
  //Add Patient to Database
  //-----------------------

  //Speed up: Check if patient is the same as in last file; very probable, as all images belonging to a study have the same patient
  bool patientExists = false;
  if(d->lastPatientID.compare(patientID) || d->lastPatientsBirthDate.compare(patientsBirthDate) || d->lastPatientsName.compare(patientsName))
  {
    //Check if patient is already present in the db
    QSqlQuery check_exists_query(database());
    std::stringstream check_exists_query_string;
    check_exists_query_string << "SELECT * FROM Patients WHERE PatientID = '" << patientID << "'";
    d->loggedExec(check_exists_query, check_exists_query_string.str().c_str());

    /// we check only patients with the same PatientID
    /// PatientID is not unique in DICOM, so we also compare Name and BirthDate
    /// and assume this is sufficient
    while (check_exists_query.next())
    {
      if (
          check_exists_query.record().value("PatientsName").toString() == patientsName.c_str() &&
          check_exists_query.record().value("PatientsBirthDate").toString() == patientsBirthDate.c_str()
         )
      {
        /// found it
        patientUID = check_exists_query.value(check_exists_query.record().indexOf("UID")).toInt();
        patientExists = true;
        break;
      }
    }
    check_exists_query.finish();

    if(!patientExists)
    {

      QSqlQuery insert_patient_query(database());

      std::stringstream query_string;

      query_string << "INSERT INTO Patients VALUES( NULL,'"
      << patientsName << "','"
      << patientID << "','"
      << patientsBirthDate << "','"
      << patientsBirthTime << "','"
      << patientsSex << "','"
      << patientsAge << "','"
      << patientComments << "')";

      d->loggedExec(insert_patient_query, query_string.str().c_str());

      patientUID = insert_patient_query.lastInsertId().toInt();
      insert_patient_query.finish();
      QString patientUIDQString;
      patientUIDQString.setNum(patientUID);
      logger.debug( "New patient inserted: " + patientUIDQString );
    }
  }
  else
    {
    patientUID = d->lastPatientUID;
    }

  /// keep this for the next image
  d->lastPatientUID = patientUID;
  d->lastPatientID = patientID;
  d->lastPatientsBirthDate = patientsBirthDate;
  d->lastPatientsName = patientsName;

  //---------------------
  //Add Study to Database
  //---------------------

  if(d->lastStudyInstanceUID.compare(studyInstanceUID))
  {
    QSqlQuery check_exists_query(database());
    std::stringstream check_exists_query_string;
    check_exists_query_string << "SELECT * FROM Studies WHERE StudyInstanceUID = '" << studyInstanceUID << "'";
    d->loggedExec(check_exists_query, check_exists_query_string.str().c_str());

    logger.debug( "Checking for study: " + QString(studyInstanceUID.c_str()) );

    if(!check_exists_query.next())
    {

      QSqlQuery insert_query(database());
      std::stringstream query_string;

      // TODO: all INSERTS should be changed to use the prepare/bindValue methods
      // to avoid quoting issues
      insert_query.prepare("INSERT INTO Studies (StudyInstanceUID, PatientsUID, StudyID, StudyDate, StudyTime, AccessionNumber, ModalitiesInStudy, InstitutionName, ReferringPhysician, PerformingPhysiciansName, StudyDescription) VALUES (:StudyInstanceUID, :PatientsUID, :StudyID, :StudyDate, :StudyTime, :AccessionNumber, :ModalitiesInStudy, :InstitutionName, :ReferringPhysician, :PerformingPhysiciansName, :StudyDescription)");

      insert_query.bindValue(":StudyInstanceUID", QString(studyInstanceUID.c_str()));
      insert_query.bindValue(":PatientsUID", patientUID);
      insert_query.bindValue(":StudyID", QString(studyID.c_str()));
      insert_query.bindValue(":StudyDate", QDate::fromString(studyDate.c_str(), "yyyyMMdd").toString("yyyy-MM-dd"));
      insert_query.bindValue(":StudyTime", QString(studyTime.c_str()));
      insert_query.bindValue(":AccessionNumber", QString(accessionNumber.c_str()));
      insert_query.bindValue(":ModalitiesInStudy", QString(modalitiesInStudy.c_str()));
      insert_query.bindValue(":InstitutionName", QString(institutionName.c_str()));
      insert_query.bindValue(":ReferringPhysician", QString(referringPhysician.c_str()));
      insert_query.bindValue(":PerformingPhysiciansName", QString(performingPhysiciansName.c_str()));
      insert_query.bindValue(":StudyDescription", QString(studyDescription.c_str()));

      d->loggedExec(insert_query);
      logger.debug( "Inserted study: " + QString(studyInstanceUID.c_str()) );
    }
  }

  d->lastStudyInstanceUID = studyInstanceUID;

  //----------------------
  //Add Series to Database
  //----------------------

  if(d->lastSeriesInstanceUID.compare(seriesInstanceUID))
  {

    QSqlQuery check_exists_query(database());
    std::stringstream check_exists_query_string;
    check_exists_query_string << "SELECT * FROM Series WHERE SeriesInstanceUID = '" << seriesInstanceUID << "'";
    d->loggedExec(check_exists_query, check_exists_query_string.str().c_str());

    logger.debug( "Checking series: " + QString(seriesInstanceUID.c_str()) );

    if(!check_exists_query.next())
    {

      QSqlQuery insert_query(database());
      std::stringstream query_string;

      query_string << "INSERT INTO Series VALUES('"
        << seriesInstanceUID << "','"
        << studyInstanceUID << "','"
        << static_cast<int>(seriesNumber) << "','"
        << QDate::fromString(seriesDate.c_str(), "yyyyMMdd").toString("yyyy-MM-dd").toStdString() << "','"
        << seriesTime << "','"
        << seriesDescription << "','"
        << bodyPartExamined << "','"
        << frameOfReferenceUID << "','"
        << static_cast<int>(acquisitionNumber) << "','"
        << contrastAgent << "','"
        << scanningSequence << "','"
        << static_cast<int>(echoNumber) << "','"
        << static_cast<int>(temporalPosition) << "')";

      d->loggedExec(insert_query, query_string.str().c_str());
      logger.debug( "Inserted series: " + QString(seriesInstanceUID.c_str()) );
    }
  }

  d->lastSeriesInstanceUID = seriesInstanceUID;

  QString studySeriesDirectory = QString(studyInstanceUID.c_str()) + "/" + seriesInstanceUID.c_str();

  //----------------------------------
  //Move file to destination directory
  //----------------------------------

  QString finalFilePath(filePath);
  if (!destinationDirectoryName.isEmpty())
  {
    QFile currentFile( filePath );
    QDir destinationDir(destinationDirectoryName + "/dicom");
    finalFilePath = sopInstanceUID.c_str();
    if (createHierarchy)
    {
      destinationDir.mkpath(studySeriesDirectory);
      finalFilePath.prepend( destinationDir.absolutePath() + "/"  + studySeriesDirectory + "/" );
    }
    currentFile.copy(finalFilePath);
    logger.debug( "Copy file from: " + filePath );
    logger.debug( "Copy file to  : " + finalFilePath );
  }
  logger.debug(QString("finalFilePath: ") + finalFilePath);

  if (generateThumbnail)
  {
    if(d->thumbnailGenerator)
    {
      QString thumbnailBaseDir =  databaseDirectory() + "/thumbs/";
      QString thumbnailFilename = thumbnailBaseDir + "/" + pathForDataset(dataset) + ".png";
      QFileInfo thumbnailInfo(thumbnailFilename);
      if ( ! ( thumbnailInfo.exists() && thumbnailInfo.lastModified() < QFileInfo(finalFilePath).lastModified() ) )
      {
        QDir(thumbnailBaseDir).mkpath(studySeriesDirectory);
        DicomImage dcmtkImage(QDir::toNativeSeparators(finalFilePath).toStdString().c_str());
        d->thumbnailGenerator->generateThumbnail(&dcmtkImage, thumbnailFilename);
      }
    }
  }

  //------------------------
  //Add Filename to Database
  //------------------------

//    std::stringstream relativeFilePath;
//    relativeFilePath << seriesInstanceUID.c_str() << "/" << currentFilePath.getFileName();

  logger.debug(QString("Adding file path to dabase: ") + finalFilePath);

  QSqlQuery check_exists_query(database());
  std::stringstream check_exists_query_string;
//    check_exists_query_string << "SELECT * FROM Images WHERE Filename = '" << relativeFilePath.str() << "'";
  check_exists_query_string << "SELECT * FROM Images WHERE SOPInstanceUID = '" << sopInstanceUID << "'";
  d->loggedExec(check_exists_query, check_exists_query_string.str().c_str());

  if(!check_exists_query.next())
  {
    QSqlQuery insert_query(database());
    std::stringstream query_string;

    //To save absolute path: destDirectoryPath.str()
    query_string << "INSERT INTO Images VALUES('"
      << sopInstanceUID << "','" << finalFilePath.toStdString() << "','" << seriesInstanceUID << "','" << QDateTime::currentDateTime().toString(Qt::ISODate).toStdString() << "')";

    d->loggedExec(insert_query, query_string.str().c_str());
    logger.debug(QString("added file path to dabase: ") + query_string.str().c_str());
  }



}

bool ctkDICOMDatabase::fileExistsAndUpToDate(const QString& filePath)
{
  Q_D(ctkDICOMDatabase);
  bool result(false);

  QSqlQuery check_filename_query(database());
  check_filename_query.prepare("SELECT InsertTimestamp FROM Images WHERE Filename == ?");
  check_filename_query.bindValue(0,filePath);
  d->loggedExec(check_filename_query);
  if (
    check_filename_query.next() &&
    QFileInfo(filePath).lastModified() < QDateTime::fromString(check_filename_query.value(0).toString(),Qt::ISODate)
    )
  {
    result = true;
  }
  check_filename_query.finish();
  return result; 
}


bool ctkDICOMDatabase::isOpen() const
{
  Q_D(const ctkDICOMDatabase);
  return d->Database.isOpen();
}

bool ctkDICOMDatabase::isInMemory() const
{
  Q_D(const ctkDICOMDatabase);
  return d->DatabaseFileName == ":memory:";
}


QString ctkDICOMDatabase::pathForDataset( DcmDataset *dataset)
{
  if (!dataset)
    {
    return QString();
    }
  OFString studyInstanceUID, seriesInstanceUID, sopInstanceUID;
  dataset->findAndGetOFString(DCM_StudyInstanceUID, studyInstanceUID);
  dataset->findAndGetOFString(DCM_SeriesInstanceUID, seriesInstanceUID);
  dataset->findAndGetOFString(DCM_SOPInstanceUID, sopInstanceUID);

  return QString(studyInstanceUID.c_str()) + "/" + seriesInstanceUID.c_str() + "/" + sopInstanceUID.c_str();

}