/***************************************************************************
 *   Copyright (C) 2009 by Eric Maeker, MD.                                *
 *   eric.maeker@free.fr                                                   *
 *   and completed by Pierre-Marie Desombre, GP,                           *
 *   pm.desombre@medsyn.fr                                                 *
 *   This code is free and open source .                                   *
 *   It is released under the terms of the new BSD License.                *
 *                                                                         *
 *   Redistribution and use in source and binary forms, with or without    *
 *   modification, are permitted provided that the following conditions    *
 *   are met:                                                              *
 *   - Redistributions of source code must retain the above copyright      *
 *   notice, this list of conditions and the following disclaimer.         *
 *   - Redistributions in binary form must reproduce the above copyright   *
 *   notice, this list of conditions and the following disclaimer in the   *
 *   documentation and/or other materials provided with the distribution.  *
 *   - Neither the name of the FreeMedForms' organization nor the names of *
 *   its contributors may be used to endorse or promote products derived   *
 *   from this software without specific prior written permission.         *
 *                                                                         *
 *   THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS   *
 *   "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT     *
 *   LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS     *
 *   FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE        *
 *   COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT,  *
 *   INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING,  *
 *   BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES;      *
 *   LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER      *
 *   CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT    *
 *   LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN     *
 *   ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE       *
 *   POSSIBILITY OF SUCH DAMAGE.                                           *
 ***************************************************************************/
 /***************************************************************************
 *   Main Developper : Eric MAEKER, <eric.maeker@free.fr>                  *
 *   Contributors :                                                        *
 *       Docteur Pierre-Marie Desombre,pm.desombre@medsyn.fr               *
 ***************************************************************************/
#include "databasemanager.h"
#include "settings.h"
#include "constants.h"
#include "icore.h"
#include "commandlineparser.h"
#include "./configure/mdp.h"

//#include "completeBase.h"

#include <QProgressDialog>
#include <QMessageBox>
#include <QString>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QApplication>
#include <QtSql>
#include <QDebug>
#include <QCursor>

enum { WarnDebugMessage = true };

using namespace Common;
using namespace Constants;

static inline  Settings *settings() {return Common::ICore::instance()->settings(); }

DatabaseManager::DatabaseManager(QObject *parent) :
        QObject(parent),
        m_IsDrTuxConfigured(false),
        m_IsAccountancyConfigured(false)
{

}

DatabaseManager::~DatabaseManager()
{}

bool DatabaseManager::connectCcam()
{
    Q_ASSERT(!settings()->isClean());
    QString log    = settings()->value(Constants::S_COMPTA_DB_LOGIN).toString();
    QString pass   = settings()->value(Constants::S_COMPTA_DB_PASSWORD).toString();
    QString host   = settings()->value(Constants::S_COMPTA_DB_HOST).toString();
    QString port   = settings()->value(Constants::S_COMPTA_DB_PORT).toString();
    QString driver = settings()->value(Constants::S_COMPTA_DRIVER).toString();
        //--------test driver--------------------------------
    qDebug() << __FILE__ << QString::number(__LINE__)  << "Driver chosen is " << driver;
    int driverType = 0;
    if(driver == "MYSQL"){
        driverType = Driver_MySQL;
    }
    else if(driver == "SQLITE"){
        driverType = Driver_SQLite;
    }
    else if(driver == "POSTGRESQL"){
        driverType = Driver_PostGreSQL;
    }
    else{
        QMessageBox::critical(0,trUtf8("Critical"),trUtf8("Le driver de la base de données ne peut pas être défini.\n"
                    "Vous devez écrire dans config.ini après Driver :\n"
                    "pour MySql : MYSQL\npour Sqlite : SQLITE\npour PostGresql : POSTGRESQL."),QMessageBox::Ok);
        return false;
    }
    QSqlDatabase db1;
    bool dbTest = 0;
    switch (driverType) {
        case Driver_MySQL :
        {
            db1 = QSqlDatabase::addDatabase("QMYSQL", Constants::DB_CCAM);
            db1.setHostName(host);
            db1.setDatabaseName(Constants::DB_CCAM);
            db1.setUserName(log);
            db1.setPassword(pass);
            if (WarnDebugMessage)
                qWarning() << "Connecting CCAM Database" << host << Constants::DB_CCAM << log << pass << port;
            dbTest = (!db1.isOpen() && !db1.open());
            break;
        }
        case Driver_SQLite :
        {
            db1 = QSqlDatabase::addDatabase("QSQLITE", Constants::DB_CCAM);
            db1.setDatabaseName(settings()->userResourcesPath() + QDir::separator() + Constants::DB_CCAM + ".db");
              if (WarnDebugMessage){
                qWarning() << "Connecting CCAM database SQLite " + db1.databaseName() +" "+ QString::number(__LINE__);
              }
            if(!db1.open()){
                qWarning() << "database CCAM cannot open";
            }
            QStringList tables = db1.tables();
            qDebug() << "tables CCAM size = "+QString::number(tables.size());
            dbTest = (tables.size() < 1);
            break;
        }
        case Driver_PostGreSQL :
            break;
    }

    if (dbTest) {
        QMessageBox msgBox;
        msgBox.setText("CCAM database cannot work or do not exists.");
        msgBox.setInformativeText("Do you want to create it ?");
        msgBox.setStandardButtons(QMessageBox::Yes | QMessageBox::Abort);
        msgBox.setDefaultButton(QMessageBox::Yes);
        int ret = msgBox.exec();
        switch(ret){
            case QMessageBox::Yes :
                createCcamDatabase(driverType);
                break;
            case QMessageBox::Abort :
                return false;
                break;
        }

    }

    return true;
}

bool DatabaseManager::connectAccountancy()
{
    Q_ASSERT(!settings()->isClean());
    int driverType = 0;
    QString log    = settings()->value(Constants::S_COMPTA_DB_LOGIN).toString();
    QString pass   = settings()->value(Constants::S_COMPTA_DB_PASSWORD).toString();
    QString host   = settings()->value(Constants::S_COMPTA_DB_HOST).toString();
    QString port   = settings()->value(Constants::S_COMPTA_DB_PORT).toString();
    QString driver = settings()->value(Constants::S_COMPTA_DRIVER).toString();
    //--------test driver--------------------------------
    qDebug() << __FILE__ << QString::number(__LINE__)  << "Driver chosen is "+driver;
    if(driver == "MYSQL"){
        driverType = Driver_MySQL;
    }
    else if(driver == "SQLITE"){
        driverType = Driver_SQLite;
    }
    else if(driver == "POSTGRESQL"){
        driverType = Driver_PostGreSQL;
    }
    else{
        QMessageBox::critical(0,trUtf8("Critical"),trUtf8("Le driver de la base de données ne peut pas être défini.\n"
                    "Vous devez écrire dans config.ini après Driver :\n"
                    "pour MySql : MYSQL\npour Sqlite : SQLITE\npour PostGresql : POSTGRESQL."),QMessageBox::Ok);
        return false;
    }
    //--------------------------------------
    QSqlDatabase db;
    bool dbTest = 0;
    switch (driverType) {
        case Driver_MySQL :
        {
            db = QSqlDatabase::addDatabase("QMYSQL", Constants::DB_ACCOUNTANCY);
            db.setHostName(host);
            db.setDatabaseName(Constants::DB_ACCOUNTANCY);
            db.setUserName(log);
            db.setPassword(pass);
              if (WarnDebugMessage){
                qWarning() << "Connecting Accountancy database" << host << Constants::DB_ACCOUNTANCY << log << pass << port;
              }
            dbTest = (!db.isOpen()) && (!db.open());
            break;
        }
        case Driver_SQLite :
        {
            db = QSqlDatabase::addDatabase("QSQLITE", Constants::DB_ACCOUNTANCY);
            db.setDatabaseName(settings()->userResourcesPath() + QDir::separator() + Constants::DB_ACCOUNTANCY + ".db");
              if (WarnDebugMessage){
                qWarning() << "Connecting Accountancy database SQLite " + db.databaseName() +" "+ QString::number(__LINE__);
              }
            if(!db.open()){
                qWarning() << "database accountancy cannot open";
            }
            QStringList tables = db.tables();
            qDebug() << "tables "+QString::number(tables.size());
            dbTest = (tables.size() < 1);
            break;
        }
        case Driver_PostGreSQL :
            break;
    }

    if (dbTest) {
        if (!createAccountancyDatabaseSchema(driverType)) {
            QMessageBox::warning(qApp->activeWindow(),
                                 tr("Fatal error"),
                                 tr("Unable to connect Accountancy database. Application will not work."),
                                 QMessageBox::Abort);
            qWarning() << "FATAL ERROR : Enable to create the accountancy database";
            return false;
        }
        else {
            if (WarnDebugMessage)
                qWarning() << "Connected to Accountancy database"
                           << db.databaseName()
                           << __FILE__
                           << QString::number(__LINE__);
        }
        return false;
    }
    if(isAccountancyDatabaseCorrupted(driverType,db)){
        QMessageBox::critical(0,trUtf8("Critical"),trUtf8("La base comptabilité est incomplète,"
               "\nveuillez cliquer sur Compléter la base dans Paramétrages.")
               +"\n"+QString(__FILE__)+"\n"+QString::number(__LINE__),
               QMessageBox::Ok);
        return false;
    }
    if (testVersion(db))
    {
    	  QDir dirCactot(settings()->applicationBundlePath()+"/FichiersNX");
    	  QString versions;
    	  QStringList listOfFiles = dirCactot.entryList();
    	  for (int i = 0; i < listOfFiles.size(); i += 1)
    	  {
    	  	if (listOfFiles[i].contains("CACTOT"))
    	  	{
    	  		  versions += listOfFiles[i]+"\n";
    	  	    }
    	      }
    	  QUrl ameli("http://www.ameli.fr/accueil-de-la-ccam/telechargement/version-actuelle/index.php");
    	  const QString explanation = trUtf8("Votre version a changé.\nVerifiez la version de la CCAM sur "
    	  "ameli.fr.\nVos fichiers NX sont actuellement "+versions+"."
    	  "\nVoulez vous vérifier si une nouvelle version existe ?");
    	  QMessageBox mess;
    	  mess.setText(explanation);
          mess.setStandardButtons(QMessageBox::Yes | QMessageBox::No);
          mess.setDefaultButton(QMessageBox::Yes);
          int ret = mess.exec();
          switch(ret){
              case QMessageBox::Yes :
                  QDesktopServices::openUrl(QUrl(ameli));
                  break;
              case QMessageBox::No :
                  break;
              default :
                  break;    
              }
	  
        }
    return true;
}

bool DatabaseManager::isAccountancyDatabaseCorrupted(const int driverType,QSqlDatabase &db)
{

    QString driverName = "";
    if(driverType == Driver_MySQL){
        driverName = MYSQL;
        }
    if(driverType == Driver_SQLite){
        driverName = SQLITE;
        }
    if(driverType == Driver_PostGreSQL){
        driverName = POSTGRESQL;
        }
    if(!db.isOpen()){
       qDebug() << "db is not opened " << __FILE__ << __LINE__;
    }
    qDebug() << __FILE__ << " driverName ="+driverName;
    qDebug() << __FILE__ << " database ="+db.connectionName();

    int tablesCount = Common::ICore::instance()->settings()->value(S_COMPTA_NBR_OF_TABLES).toInt();
    if(isAnIndependantProgram() || db.tables().contains("nameindex") ){
        qDebug() << "It is an independant program " << __FILE__ << __LINE__;
             tablesCount += 1;
    }
    QSqlQuery q(db);
    QString base = QString(Constants::DB_ACCOUNTANCY);
    QString req;
    if(driverName == MYSQL){
        req = QString("show tables from %1;").arg(base);
    }
    if(driverName == SQLITE){
        req = QString("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;");
        }
    qDebug() << __FILE__ << QString::number(__LINE__) << req;
    if(!q.exec(req)){
        qWarning() << __FILE__ << " " << QString::number(__LINE__) << q.lastError().text();
        }
    QStringList list;
    QString listStr;
    while(q.next()){
       QString s = q.value(0).toString();
       list << s+"\n";
    }
    qDebug() << "list.size()  ="+QString::number(list.size());
    qDebug() << "list = " << listStr;
    return list.size() !=tablesCount;
}

bool DatabaseManager::isAnIndependantProgram(){
    QString programName = Common::ICore::instance()->commandLine()->value(Common::CommandLine::CL_ProgramName).toString();
    if(!programName.isEmpty()){
        return false;
    }
    return true;
}

bool DatabaseManager::createFirstSqlConnection() const
{
     qDebug() << "in createFirstSqlConnection" << __FILE__;
     if(!settings()->writeAllDefaults()) {
         return false;
     }
     mdp * databaseData = new mdp;
     if(databaseData->exec() == QDialog::Accepted) {
         settings()->setValue(Constants::S_COMPTA_DB_LOGIN,   databaseData->mdpLogin());
         settings()->setValue(Constants::S_COMPTA_DB_PASSWORD,databaseData->mdpPassword());
         settings()->setValue(Constants::S_COMPTA_DB_HOST,    databaseData->mdpHost());

     }
     QSqlDatabase db = QSqlDatabase :: addDatabase(Constants::MYSQL,Constants::DB_FIRST_CONNECTION);
     db.setHostName(settings()->value(S_COMPTA_DB_HOST).toString());
     db.setDatabaseName("mysql");
     db.setUserName(settings()->value(S_COMPTA_DB_LOGIN).toString());
     db.setPassword(settings()->value(S_COMPTA_DB_PASSWORD).toString());
     if(!db.open()) {
         qWarning() << "la base firstConnection ne peut être ouverte.";
     return false;
     }
      qDebug() << __FILE__ << " " << QString::number(__LINE__);
  return true;
}

bool DatabaseManager::createAccountancyDatabase(const int driver) const
{
    qDebug()<< "in createAccountancyDatabase" << __FILE__;
    if (driver==Driver_SQLite)
        return true;

    if (!createFirstSqlConnection()) {//MYSQL
        QMessageBox::warning(qApp->activeWindow(),
                             tr("Fatal error"),
                             tr("An error occured during accountancy database creation.\nDo you have ")+Constants::MYSQL+""
                             ""+tr(" installed ?"),
                             QMessageBox::Abort);
    }
    QSqlDatabase dbFirst = QSqlDatabase::database(Constants::DB_FIRST_CONNECTION);
    QString reqForA = "CREATE DATABASE comptabilite";
    QSqlQuery q(dbFirst);
    q.exec(reqForA);
    if (q.lastError().isValid() == true) {
        QMessageBox::warning(qApp->activeWindow(),
                             tr("Fatal error"),
                             tr("An error occured during accountancy database creation.\n")
                             +__FILE__+" "+QString::number(__LINE__),
                             QMessageBox::Abort);
        if (WarnDebugMessage)
            qWarning() << "SQL Error" << q.lastError().text() ;
        return false;
    }
    return true;
}

bool DatabaseManager::createCcamDatabase(const int driverType){

    QString driverName = "";
    if(driverType == Driver_MySQL){
        if(!QSqlDatabase::database(Constants::DB_FIRST_CONNECTION).isValid()){
              if (!createFirstSqlConnection()) {//MYSQL
                             QMessageBox::warning(qApp->activeWindow(),
                             tr("Fatal error"),
                             tr("An error occured during accountancy database creation.\nDo you have ")
                             +Constants::MYSQL+""
                             ""+tr(" installed ?")+"\n"__FILE__"\n"+QString::number(__LINE__),
                             QMessageBox::Abort);

              }
              qDebug() << __FILE__ << " " << QString::number(__LINE__);
        }

              qDebug() << __FILE__ << " " << QString::number(__LINE__);
              QSqlQuery query(QSqlDatabase::database(Constants::DB_FIRST_CONNECTION));
              if(!query.exec("CREATE DATABASE CCAMTest")){
                  QMessageBox::critical(0,tr("Critical"),tr("CCAMTest cannot be created"),QMessageBox::Abort);
              }
        qDebug() << __FILE__ << " " << QString::number(__LINE__);

    }
    if(driverType == Driver_SQLite){
        driverName = SQLITE;
        }
    if(driverType == Driver_PostGreSQL){
        driverName = POSTGRESQL;
        }

  QSqlDatabase db = QSqlDatabase::database(Constants::DB_CCAM);
  if(parseDumpCcam()==false){
      qDebug() << "CCAM is badly installed " << __FILE__ << " " << QString::number(__LINE__);
  }

  //----liste des tables-------------------
  QStringList CcamTables;
              CcamTables <<  "ACCES1"
                         <<  "ACCES2"
                         <<  "ACTES"
                         <<  "ACTION1"
                         << "ACTION2"
                         << "ACTIVITE"
                         << "ACTIVITEACTE"
                         << "ADM_REMB"
                         << "AGR_RADIO"
                         << "ARBORESCENCE"
                         << "ASSOCIABILITE"
                         << "ASSOCIATION"
                         << "ASS_NPREV"
                         << "CAT_MED"
                         << "CAT_SPEC"
                         << "CLASSE_DMT"
                         << "CODE_PAIEMENT"
                         << "COD_REGROUP"
                         << "COMPAT_EXO_TM"
                         << "COND_GEN"
                         << "DATES_EFFET"
                         << "DMT"
                         << "DOM"
                         << "EXO_TM"
                         << "EXT_DOC"
                         << "FRAIS_DEP"
                         << "HISTO_PHASE"
                         << "INCOMPATIBILITE"
                         << "LISTE_MOTS"
                         << "MEDECINS"
                         << "MODIFICATEUR"
                         << "MODIFICATEURACTE"
                         << "NAT_ASS"
                         << "NOTES"
                         << "NOTESARBORESCENCE"
                         << "NUM_DENTS"
                         << "PHASE"
                         << "PHASEACTE"
                         << "PROCEDURES"
                         << "REC_MED_ACTE"
                         << "RGL_TARIF"
                         << "SPECIALITES"
                         << "TB03"
                         << "TB04"
                         << "TB05"
                         << "TB06"
                         << "TB07"
                         << "TB08"
                         << "TB09"
                         << "TB10"
                         << "TB11"
                         << "TB12"
                         << "TB13"
                         << "TB14"
                         << "TB15"
                         << "TB17"
                         << "TB18"
                         << "TB19"
                         << "TB20"
                         << "THESAURUS"
                         << "TOPOGRAPHIE1"
                         << "TOPOGRAPHIE2"
                         << "TYPENOTE"
                         << "TYPE_ACTE"
                         << "TYPE_FORFAIT"
                         << "VAL_UNITE_OEUVRE"
                         << "VERSION";

     return true;

}

bool DatabaseManager::createAccountancyDatabaseSchema(const int driver)
{
    bool result = true;
    if ((driver!=Driver_SQLite) && (!createAccountancyDatabase(driver))) {
        QMessageBox::critical(0, tr("Critical"), tr("Unable to create accountancy database."), QMessageBox::Ok);
        result = false;
    }
    QHash<QString, QString> name_sql;
//    name_sql.insert("Database creation", "CREATE DATABASE comptabilite");
    if (driver==Driver_MySQL) {
    name_sql.insert("Table creation : actes_disponibles",
                    "CREATE TABLE IF NOT EXISTS	actes_disponibles ("
                    "id_acte_dispo  int(10)       UNSIGNED NOT NULL auto_increment,"
                    "nom_acte 	    varchar(50)   COLLATE utf8_unicode_ci NOT NULL,"
                    "desc_acte      varchar(200)  COLLATE utf8_unicode_ci NOT NULL,"
                    "type           varchar(100)  COLLATE utf8_unicode_ci NOT NULL,"
                    "montant_total  double        NOT NULL,"
                    "montant_tiers  double        NOT NULL,"
                    "date_effet	    date          NOT NULL,"
                    "PRIMARY KEY(id_acte_dispo));");
    name_sql.insert("Table creation : thesaurus",
                    "CREATE TABLE IF NOT EXISTS	thesaurus ("
                    "id_thesaurus  int(10)       UNSIGNED NOT NULL auto_increment,"
                    "sequence 	    varchar(50)   COLLATE utf8_unicode_ci NOT NULL,"
                    "type           varchar(100)  COLLATE utf8_unicode_ci NOT NULL,"
                    "PRIMARY KEY(id_thesaurus));");
    name_sql.insert("Table creation : forfaits",
                    "CREATE TABLE IF NOT EXISTS	forfaits ("
                    "id_forfaits  int(10)       UNSIGNED NOT NULL auto_increment,"
                    "forfaits 	    varchar(50)   COLLATE utf8_unicode_ci NOT NULL,"
                    "valeur 	    varchar(10)   COLLATE utf8_unicode_ci NOT NULL,"
                    "type           varchar(10)  COLLATE utf8_unicode_ci NOT NULL,"
                    "PRIMARY KEY(id_forfaits));");
    name_sql.insert("Table creation comptes_bancaires",
                    "CREATE TABLE IF NOT EXISTS	comptes_bancaires ("
                    "id_compte	      int(10)		UNSIGNED 			NOT NULL	auto_increment 	,"
                    "id_usr 	      int(10) 		UNSIGNED 			NOT NULL 			,"
                    "libelle 	      varchar(150) 	COLLATE utf8_unicode_ci  	NULL 				,"
                    "titulaire 	      varchar(100) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "nom_banque       varchar(50) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "rib_code_banque  varchar(5) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "rib_code_guichet varchar(5) 	COLLATE utf8_unicode_ci  	NULL 				,"
                    "rib_numcompte    varchar(11) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "rib_cle 	      varchar(2) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "solde_initial    double 						NULL				,"
                    "remarque 	      blob 		 	 			NULL 				,"
                    "PRIMARY KEY(id_compte));");
    name_sql.insert("Table creation depots",
                    "CREATE TABLE IF NOT EXISTS	depots 	  ("
                    "id_depot  	 int(11)      NOT NULL auto_increment  ,"
                    "id_usr 	 int(11)      NOT NULL,"
                    "id_compte 	 int(10)      UNSIGNED NOT NULL,"
                    "type_depot  varchar(10)  COLLATE utf8_unicode_ci NULL,"
                    "date        date         NOT NULL,"
                    "periode_deb date         NULL,"
                    "periode_fin date         NULL,"
                    "blob_depot  blob         NULL,"
                    "remarque 	 text         COLLATE utf8_unicode_ci NULL,"
                    "valide 	 tinyint(4)   NULL,"
                    "PRIMARY KEY(id_depot));");
    name_sql.insert("Table creation honoraires",
                    "CREATE TABLE IF NOT EXISTS	honoraires	("
                    "id_hono  	   int(11)        UNSIGNED NOT NULL	 auto_increment  ,"
                    "id_usr 	   int(11)        NOT NULL,"
                    "id_drtux_usr  int(11)        NULL,"
                    "patient 	   varchar(75) 	  COLLATE utf8_unicode_ci NOT NULL,"
                    "id_site       varchar(10)    COLLATE utf8_unicode_ci NULL,"
                    "id_payeurs    varchar(36)    COLLATE utf8_unicode_ci NULL,"
                    "GUID          varchar(36)    COLLATE utf8_unicode_ci NULL,"
                    "praticien 	   varchar(75)    COLLATE utf8_unicode_ci NOT NULL,"
                    "date          date           NOT NULL,"
                    "acte          blob           NULL,"
                    "acteclair     varchar(12)    COLLATE utf8_unicode_ci NULL,"
                    "remarque      blob           NULL,"
                    "esp           double         NOT NULL,"
                    "chq           double         NOT NULL,"
                    "cb            double         NOT NULL,"
                    "daf           double         NOT NULL,"
                    "autre         double         NOT NULL,"
                    "du            double         NOT NULL,"
                    "du_par        varchar(100)   COLLATE utf8_unicode_ci NULL,"
                    "valide        tinyint(1)     NULL,"
                    "tracabilite   blob           NULL,"
                    "PRIMARY KEY(id_hono));");
    name_sql.insert("Table creation immobilisations",
                    "CREATE TABLE IF NOT EXISTS	immobilisations ("
                    "id_immob  	    int(10)         UNSIGNED NOT NULL auto_increment,"
                    "id_usr 	    int(10)         UNSIGNED NOT NULL,"
                    "id_compte 	    int(10)         UNSIGNED NOT NULL,"
                    "libelle 	    varchar(150)    COLLATE utf8_unicode_ci NULL,"
                    "date_service   date            NOT NULL,"
                    "duree          int(11)         NOT NULL,"
                    "mode           tinyint(1)      NOT NULL,"
                    "valeur         bigint(20) 	    UNSIGNED NOT NULL,"
                    "montant_tva    double          NULL,"
                    "valeur_residuelle bigint(20)   NOT NULL,"
                    "resultat       blob            NOT NULL,"
                    "mouvements     blob            NULL,"
                    "remarque       text            COLLATE utf8_unicode_ci NULL,"
                    "tracabilite    blob            NULL,"
                    "PRIMARY KEY(id_immob));");
    name_sql.insert("Table creation mouvements",
                    "CREATE TABLE IF NOT EXISTS	mouvements	("
                    "id_mouvement  int(10) 	UNSIGNED NOT NULL auto_increment ,"
                    "id_mvt_dispo  int(11)      NOT NULL,"
                    "id_usr        int(10) 	UNSIGNED NOT NULL,"
                    " id_compte    int(10) 	UNSIGNED NOT NULL,"
                    " type         tinyint(3) 	UNSIGNED NOT NULL,"
                    " libelle      varchar(100) COLLATE utf8_unicode_ci NOT NULL,"
                    " date         date         NOT NULL,"
                    " date_valeur  date         NOT NULL,"
                    " montant      double       UNSIGNED NOT NULL,"
                    "remarque      blob         NULL,"
                    " valide       tinyint(4)   NOT NULL,"
                    " tracabilite  blob         NULL,"
                    " validation   tinyint(1)   NULL,"
                    " detail       varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "PRIMARY KEY(id_mouvement));");
    name_sql.insert("Table creation mouvements_disponibles",
                    "CREATE TABLE IF NOT EXISTS	mouvements_disponibles ("
                    "id_mvt_dispo   int(10)      UNSIGNED NOT NULL auto_increment,"
                    "id_mvt_parent  int(11)      NULL,"
                    "type           tinyint(3)   UNSIGNED NOT NULL,"
                    "libelle 	    varchar(100) COLLATE utf8_unicode_ci NOT NULL,"
                    "remarque 	    blob         NULL,"
                    "PRIMARY KEY(id_mvt_dispo));");
    name_sql.insert("Table creation payeurs",
                    "CREATE TABLE IF NOT EXISTS	payeurs	("
                    "ID_Primkey  	bigint(20)   NOT NULL auto_increment,"
                    "id_payeurs 	bigint(20)   NOT NULL,"
                    "nom_payeur 	varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "adresse_payeur     varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "ville_payeur 	varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "code_postal 	varchar(5)   COLLATE utf8_unicode_ci NULL,"
                    "telephone_payeur   varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "PRIMARY KEY(ID_Primkey));");
    name_sql.insert("Table creation sites",
                    "CREATE TABLE IF NOT EXISTS	sites ("
                    "ID_Primkey   bigint(10)  	NOT NULL auto_increment,"
                    "id_site      varchar(10) 	COLLATE utf8_unicode_ci NOT NULL,"
                    "site 	  varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "adresse_site varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "cp_site      varchar(5) 	COLLATE utf8_unicode_ci	NULL,"
                    "ville_site   varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "telsite 	  varchar(50) 	COLLATE utf8_unicode_ci NULL,"
                    "faxsite 	  varchar(50) 	COLLATE utf8_unicode_ci NULL,"
                    "mailsite 	  varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "PRIMARY KEY(ID_Primkey));");
    name_sql.insert("Table creation utilisateurs",
                    "CREATE TABLE IF NOT EXISTS	utilisateurs ("
                    "id_usr  	int(10)  	 	UNSIGNED NOT NULL auto_increment,"
                    "nom_usr 	varchar(150) 		COLLATE utf8_unicode_ci NULL,"
                    "login 	varchar(15) 		COLLATE utf8_unicode_ci	NOT NULL,"
                    "password 	varchar(50) 		COLLATE utf8_unicode_ci	NOT NULL,"
                    "id_drtux_usr 	int(11) 	NULL,"
                    "PRIMARY KEY(id_usr));");
    name_sql.insert("Table creation pourcentages",
                    "CREATE TABLE IF NOT EXISTS	pourcentages ("
                    "id_pourcent	int(10)		UNSIGNED NOT NULL auto_increment,"
                    "type		varchar(100)	COLLATE utf8_unicode_ci NULL,"
                    "pourcentage	varchar(6)	COLLATE utf8_unicode_ci	NOT NULL,"
                    "PRIMARY KEY(id_pourcent));");
    name_sql.insert("Table creation seances",
                    "CREATE TABLE IF NOT EXISTS	seances  ("
                    "id_paiement    int(11)		unsigned NOT NULL auto_increment ,"
                    "id_hono        int(11)  	 	UNSIGNED NOT NULL,"
                    "date           date                NOT NULL,"
                    "id_payeurs     bigint(20)          NOT NULL,"
                    "id_usr         varchar(10)         NULL,"
                    "id_site        varchar(10)         NULL,"
                    "acte           text                COLLATE utf8_unicode_ci	NULL,"
                    "esp            double 		NOT NULL,"
                    "chq            double 		NOT NULL,"
                    "cb             double 		NOT NULL,"
                    "daf            double 		NOT NULL,"
                    "autre          double 		NOT NULL,"
                    "du             double 		NOT NULL,"
                    "valide 	tinyint(1) 		NULL,"
                    "PRIMARY KEY(id_paiement));");
    name_sql.insert("Table paiements",
                    "CREATE TABLE IF NOT EXISTS paiements ("
                    "id_paiement   int(11)  	 	UNSIGNED  NOT NULL  auto_increment ,"
                    "id_hono  	   int(11)  	 	UNSIGNED  NOT NULL ,"
                    "date  	   date  	 	  	  NOT NULL,"
                    "id_payeurs    bigint(20)  	 	  	  NOT NULL,"
                    "acte  	   blob  	 	          NULL  ,"
                    "esp  	   double  	 	  	  NOT  NULL,"
                    "chq  	   double  	 	  	  NOT  NULL,"
                    "cb  	   double  	 	  	  NOT  NULL,"
                    "daf  	   double  	 	  	  NOT  NULL,"
                    "autre  	   double  	 	  	  NOT  NULL,"
                    "du 	   double  	 	  	  NOT  NULL,"
                    "valide  	   tinyint(1)  	 	  	  NULL ,"
                    "PRIMARY KEY(id_paiement));");
    name_sql.insert("Table rapprochement_bancaire",
                    "CREATE TABLE IF NOT EXISTS rapprochement_bancaire ("
                    "id_rap_bank   int(11) UNSIGNED  NOT NULL  auto_increment ,"
                    "id_compte     int(11) UNSIGNED  null,"
                    "solde         double NULL,"
                    "PRIMARY KEY(id_rap_bank));");
    name_sql.insert("Table creation z_version",
                    "CREATE TABLE IF NOT EXISTS	z_version  ("
                    "infos_version blob NULL);");
    name_sql.insert("Table informations",
                    "CREATE TABLE INFORMATIONS ("
                    "id_info int(6) UNSIGNED  NOT NULL  auto_increment ,"
                    "VERSION varchar(6) NULL,"
                    "PRIMARY KEY(id_info));");
    name_sql.insert("Table indemnites_deplacement",
                    "CREATE TABLE IF NOT EXISTS indemnites_deplacement ("
                    "id_IK int(6) UNSIGNED  NOT NULL  auto_increment ,"
                    "type varchar(50) COLLATE utf8_unicode_ci NULL,"
                    "value double NOT  NULL,"
                    "abattement double NOT  NULL,"
                    "indemnites_forfait double NULL,"
                    "PRIMARY KEY(id_IK));");
    if(isAnIndependantProgram()){
        name_sql.insert("Table nameindex",
                        "CREATE TABLE IF NOT EXISTS  nameindex ("
                        "id_index         int(6)                 unsigned                    NOT NULL auto_increment,"
                        "name             varchar(50)            COLLATE  utf8_unicode_ci    NULL,"
                        "surname          varchar(50)            COLLATE  utf8_unicode_ci    NULL,"
                        "guid             varchar(6)                                         NOT NULL,"
                        "PRIMARY KEY(id_index));");
    }
    }
    else if(driver==Driver_SQLite) {
        name_sql.insert("Table creation actes_disponibles",
                    "CREATE TABLE IF NOT EXISTS	actes_disponibles ("
                    "id_acte_dispo  INTEGER PRIMARY KEY,"
                    "nom_acte 	    varchar(50)   COLLATE utf8_unicode_ci NOT NULL,"
                    "desc_acte      varchar(200)  COLLATE utf8_unicode_ci NOT NULL,"
                    "type           varchar(100)  COLLATE utf8_unicode_ci NOT NULL,"
                    "montant_total  double        NOT NULL,"
                    "montant_tiers  double        NOT NULL,"
                    "date_effet	    date          NOT NULL);");
        name_sql.insert("Table creation : thesaurus",
                    "CREATE TABLE IF NOT EXISTS	thesaurus ("
                    "id_thesaurus  INTEGER PRIMARY KEY,"
                    "sequence 	    varchar(50)   COLLATE utf8_unicode_ci NOT NULL,"
                    "type           varchar(100)  COLLATE utf8_unicode_ci NOT NULL);");
        name_sql.insert("Table creation : forfaits",
                    "CREATE TABLE IF NOT EXISTS	forfaits ("
                    "id_forfaits  INTEGER PRIMARY KEY,"
                    "forfaits 	    varchar(50)   COLLATE utf8_unicode_ci NOT NULL,"
                    "valeur 	    varchar(10)   COLLATE utf8_unicode_ci NOT NULL,"
                    "type           varchar(10)  COLLATE utf8_unicode_ci NOT NULL);");

    name_sql.insert("Table creation comptes_bancaires",
                    "CREATE TABLE IF NOT EXISTS	comptes_bancaires ("
                    "id_compte	      INTEGER PRIMARY KEY	,"
                    "id_usr 	      int(10) 		UNSIGNED 			NOT NULL 			,"
                    "libelle 	      varchar(150) 	COLLATE utf8_unicode_ci  	NULL 				,"
                    "titulaire 	      varchar(100) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "nom_banque       varchar(50) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "rib_code_banque  varchar(5) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "rib_code_guichet varchar(5) 	COLLATE utf8_unicode_ci  	NULL 				,"
                    "rib_numcompte    varchar(11) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "rib_cle 	      varchar(2) 	COLLATE utf8_unicode_ci 	NULL 				,"
                    "solde_initial    double 						NULL				,"
                    "remarque 	      blob 		 	 			NULL 			       );");
    name_sql.insert("Table creation depots",
                    "CREATE TABLE IF NOT EXISTS	depots 	  ("
                    "id_depot  	 INTEGER PRIMARY KEY ,"
                    "id_usr 	 int(11)      NOT NULL,"
                    "id_compte 	 int(10)      UNSIGNED NOT NULL,"
                    "type_depot  varchar(10)  COLLATE utf8_unicode_ci NULL,"
                    "date        date         NOT NULL,"
                    "periode_deb date         NULL,"
                    "periode_fin date         NULL,"
                    "blob_depot  blob         NULL,"
                    "remarque 	 text         COLLATE utf8_unicode_ci NULL,"
                    "valide 	 tinyint(4)   NULL);");
    name_sql.insert("Table creation honoraires",
                    "CREATE TABLE IF NOT EXISTS	honoraires	("
                    "id_hono  	   INTEGER PRIMARY KEY ,"
                    "id_usr 	   int(11)        NOT NULL,"
                    "id_drtux_usr  int(11)        NULL,"
                    "patient 	   varchar(75) 	  COLLATE utf8_unicode_ci NOT NULL,"
                    "id_site       varchar(10)    COLLATE utf8_unicode_ci NULL,"
                    "id_payeurs    varchar(36)    COLLATE utf8_unicode_ci NULL,"
                    "GUID          varchar(36)    COLLATE utf8_unicode_ci NULL,"
                    "praticien 	   varchar(75)    COLLATE utf8_unicode_ci NOT NULL,"
                    "date          date           NOT NULL,"
                    "acte          blob           NULL,"
                    "acteclair     varchar(12)    COLLATE utf8_unicode_ci NULL,"
                    "remarque      blob           NULL,"
                    "esp           double         NOT NULL,"
                    "chq           double         NOT NULL,"
                    "cb            double         NOT NULL,"
                    "daf           double         NOT NULL,"
                    "autre         double         NOT NULL,"
                    "du            double         NOT NULL,"
                    "du_par        varchar(100)   COLLATE utf8_unicode_ci NULL,"
                    "valide        tinyint(1)     NULL,"
                    "tracabilite   blob           NULL);");
    name_sql.insert("Table creation immobilisations",
                    "CREATE TABLE IF NOT EXISTS	immobilisations ("
                    "id_immob  	   INTEGER PRIMARY KEY,"
                    "id_usr 	    int(10)         UNSIGNED NOT NULL,"
                    "id_compte 	    int(10)         UNSIGNED NOT NULL,"
                    "libelle 	    varchar(150)    COLLATE utf8_unicode_ci NULL,"
                    "date_service   date            NOT NULL,"
                    "duree          int(11)         NOT NULL,"
                    "mode           tinyint(1)      NOT NULL,"
                    "valeur         bigint(20) 	    UNSIGNED NOT NULL,"
                    "montant_tva    double          NULL,"
                    "valeur_residuelle bigint(20)   NOT NULL,"
                    "resultat       blob            NOT NULL,"
                    "mouvements     blob            NULL,"
                    "remarque       text            COLLATE utf8_unicode_ci NULL,"
                    "tracabilite    blob            NULL);");
    name_sql.insert("Table creation mouvements",
                    "CREATE TABLE IF NOT EXISTS	mouvements	("
                    "id_mouvement  INTEGER PRIMARY KEY,"
                    "id_mvt_dispo  int(11)      NOT NULL,"
                    "id_usr        int(10) 	UNSIGNED NOT NULL,"
                    " id_compte    int(10) 	UNSIGNED NOT NULL,"
                    " type         tinyint(3) 	UNSIGNED NOT NULL,"
                    " libelle      varchar(100) COLLATE utf8_unicode_ci NOT NULL,"
                    " date         date         NOT NULL,"
                    " date_valeur  date         NOT NULL,"
                    " montant      double       UNSIGNED NOT NULL,"
                    "remarque      blob         NULL,"
                    " valide       tinyint(4)   NOT NULL,"
                    " tracabilite  blob         NULL,"
                    " validation   tinyint(1)   NULL,"
                    " detail       varchar(100) COLLATE utf8_unicode_ci NULL);");
    name_sql.insert("Table creation mouvements_disponibles",
                    "CREATE TABLE IF NOT EXISTS	mouvements_disponibles ("
                    "id_mvt_dispo  INTEGER PRIMARY KEY,"
                    "id_mvt_parent  int(11)      NULL,"
                    "type           tinyint(3)   UNSIGNED NOT NULL,"
                    "libelle 	    varchar(100) COLLATE utf8_unicode_ci NOT NULL,"
                    "remarque 	    blob         NULL);");
    name_sql.insert("Table creation payeurs",
                    "CREATE TABLE IF NOT EXISTS	payeurs	("
                    "ID_Primkey  	INTEGER PRIMARY KEY,"
                    "id_payeurs 	bigint(20)   NOT NULL,"
                    "nom_payeur 	varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "adresse_payeur     varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "ville_payeur 	varchar(100) COLLATE utf8_unicode_ci NULL,"
                    "code_postal 	varchar(5)   COLLATE utf8_unicode_ci NULL,"
                    "telephone_payeur   varchar(100) COLLATE utf8_unicode_ci NULL);");
    name_sql.insert("Table creation sites",
                    "CREATE TABLE IF NOT EXISTS	sites ("
                    "ID_Primkey   INTEGER PRIMARY KEY,"
                    "id_site      varchar(10) 	COLLATE utf8_unicode_ci NOT NULL,"
                    "site 	  varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "adresse_site varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "cp_site      varchar(5) 	COLLATE utf8_unicode_ci	NULL,"
                    "ville_site   varchar(100) 	COLLATE utf8_unicode_ci NULL,"
                    "telsite 	  varchar(50) 	COLLATE utf8_unicode_ci NULL,"
                    "faxsite 	  varchar(50) 	COLLATE utf8_unicode_ci NULL,"
                    "mailsite 	  varchar(100) 	COLLATE utf8_unicode_ci NULL);");
    name_sql.insert("Table creation utilisateurs",
                    "CREATE TABLE IF NOT EXISTS	utilisateurs ("
                    "id_usr  	INTEGER PRIMARY KEY,"
                    "nom_usr 	varchar(150) 		COLLATE utf8_unicode_ci NULL,"
                    "login 	varchar(15) 		COLLATE utf8_unicode_ci	NOT NULL,"
                    "password 	varchar(50) 		COLLATE utf8_unicode_ci	NOT NULL,"
                    "id_drtux_usr 	int(11) 	NULL);");
    name_sql.insert("Table creation pourcentages",
                    "CREATE TABLE IF NOT EXISTS	pourcentages ("
                    "id_pourcent	INTEGER PRIMARY KEY,"
                    "type		varchar(100)	COLLATE utf8_unicode_ci NULL,"
                    "pourcentage	varchar(6)	COLLATE utf8_unicode_ci	NOT NULL);");
    name_sql.insert("Table creation seances",
                    "CREATE TABLE IF NOT EXISTS	seances  ("
                    "id_paiement   INTEGER PRIMARY KEY,"
                    "id_hono        int(11)  	 	UNSIGNED NOT NULL,"
                    "date           date                NOT NULL,"
                    "id_payeurs     bigint(20)          NOT NULL,"
                    "id_usr         varchar(10)         NULL,"
                    "id_site        varchar(10)         NULL,"
                    "acte           text                COLLATE utf8_unicode_ci	NULL,"
                    "esp            double 		NOT NULL,"
                    "chq            double 		NOT NULL,"
                    "cb             double 		NOT NULL,"
                    "daf            double 		NOT NULL,"
                    "autre          double 		NOT NULL,"
                    "du             double 		NOT NULL,"
                    "valide 	tinyint(1) 		NULL);");
    name_sql.insert("Table paiements",
                    "CREATE TABLE IF NOT EXISTS paiements ("
                    "id_paiement   INTEGER PRIMARY KEY,"
                    "id_hono  	   int(11)  	 	UNSIGNED  NOT NULL ,"
                    "date  	   date  	 	  	  NOT NULL,"
                    "id_payeurs    bigint(20)  	 	  	  NOT NULL,"
                    "acte  	   blob  	 	          NULL  ,"
                    "esp  	   double  	 	  	  NOT  NULL,"
                    "chq  	   double  	 	  	  NOT  NULL,"
                    "cb  	   double  	 	  	  NOT  NULL,"
                    "daf  	   double  	 	  	  NOT  NULL,"
                    "autre  	   double  	 	  	  NOT  NULL,"
                    "du 	   double  	 	  	  NOT  NULL,"
                    "valide  	   tinyint(1)  	 	  	  NULL);");
    name_sql.insert("Table rapprochement_bancaire",
                    "CREATE TABLE IF NOT EXISTS rapprochement_bancaire ("
                    "id_rap_bank   INTEGER PRIMARY KEY ,"
                    "id_compte     int(11)   null,"
                    "solde         double NULL);");
    name_sql.insert("Table creation z_version",
                    "CREATE TABLE IF NOT EXISTS	z_version  ("
                    "infos_version blob NULL);");
    name_sql.insert("Table informations",
                    "CREATE TABLE INFORMATIONS ("
                    "id_info integer primary key,"
                    "VERSION varchar(6) NULL);");
     name_sql.insert("Table indemnites_deplacement",
                    "CREATE TABLE IF NOT EXISTS indemnites_deplacement ("
                    "id_IK integer primary key,"
                    "type varchar(50)  NULL,"
                    "value double NOT  NULL,"
                    "abattement double NOT  NULL,"
                    "indemnites_forfait double NULL);");
    if(isAnIndependantProgram()){
        name_sql.insert("Table nameindex",
                   "CREATE TABLE IF NOT EXISTS  nameindex ("
                   "id_index         integer primary key,"
                   "name             varchar(50)     NULL,"
                   "surname          varchar(50)     NULL,"
                   "guid             varchar(6)      NOT NULL);");

    }

    }

    // Mass execute SQL queries
    QSqlDatabase db = QSqlDatabase::database(Constants::DB_ACCOUNTANCY);
    if ((!db.isOpen()) && (!db.open())) {
        if (WarnDebugMessage)
            qWarning() << "Unable to create accountancy database, database can not be open.";
        result = false;
    }
    qDebug() << "Hash Size "+QString::number(name_sql.size());
    QHashIterator<QString, QString> it(name_sql);
     while (it.hasNext()) {
         it.next();
         QSqlQuery q(db);
         QString req = it.value();
         if (driver==Driver_SQLite) {
             req = req.remove("COLLATE utf8_unicode_ci", Qt::CaseInsensitive).remove("UNSIGNED", Qt::CaseInsensitive);

         }
         q.exec(req);
         // Is there an error with this req ?
         if (q.lastError().isValid()) {
             QMessageBox::warning(qApp->activeWindow(),
                                  tr("Fatal error"),
                                  tr("An error occured during accountancy database creation.\n"
                                     "Reference : %1\n"
                                     "Error : %2\n\n"
                                     "Application will not work.")
                                  .arg(it.key()).arg(q.lastError().text()),
                                  QMessageBox::Abort);
             if (WarnDebugMessage)
                 qWarning() << "SQL Error" << it.key() << q.lastError().text();
             result = false;
         }
     }
     //-------table paiements et remplissage des bases d'initialisation ----------------------------------
   int numberOfTables = Common::ICore::instance()->settings()->value(S_COMPTA_NBR_OF_TABLES).toInt();
   if(isAnIndependantProgram()){
       numberOfTables++;
   }
   QMessageBox mess;
               mess.setText(trUtf8("La base comptabilite a été installée "
                 "avec ")+QString::number(numberOfTables)
                 +trUtf8(" tables."));
               mess.setStandardButtons(QMessageBox::Close);
               mess.setDefaultButton(QMessageBox::Close);
               int r = mess.exec();
               if(r == QMessageBox::Close)
                 {
                   if(feedAccountancyDatabaseWithDefaults() == true){
                       result = true;
                   }
                   else{
                        QMessageBox::warning(0,tr("Erreur"),trUtf8("L'initialisation des tables n'a pas pu se faire."),
                          QMessageBox::Ok);
                        result = false;
                   }
                 }
    return result;
}

/*bool DatabaseManager::feedAccountancyDatabaseWithDefaults() const
{
    QSqlDatabase db = QSqlDatabase::database(Constants::DB_ACCOUNTANCY);
    QDir dirDump(Common::ICore::instance()->settings()->applicationBundlePath()+"/../resources/dump");
    QStringList filters;
                filters << "*.sql";
    dirDump.setNameFilters(filters);
    QStringList listDump;
                listDump = dirDump.entryList(filters);
     for(int i = 0 ; i < listDump.size() ; i++){
     qDebug() << Common::ICore::instance()->settings()->applicationBundlePath()+"/../resources/dump/"+listDump[i];
     QStringList list;
     QString strList        = "";
     QFile file(Common::ICore::instance()->settings()->applicationBundlePath()+"/../resources/dump/"+listDump[i]);
         if(!file.open(QIODevice::ReadOnly)){
             QMessageBox::warning(0,QObject::tr("Erreur"),listDump[i]+QObject::trUtf8(" non trouvé."),QMessageBox::Ok);
         }
    QTextStream stream(&file);
                stream.setCodec("ISO 8859-1");
       while(!stream.atEnd()){
          QString ligne = stream.readLine();
          strList      += ligne;
           if (ligne.endsWith(";") && (ligne.endsWith("*////;")) == false ){  pour remettre enlever les // dans endsWhith n° 2
 /*               list       << strList;
                strList          = "";
            }
        }
       for(int i = 0; i < list.size() ; i++){
          QSqlQuery query(db);
          query.exec(list[i]);
       }
     }
    return true;
}*/

bool DatabaseManager::feedAccountancyDatabaseWithDefaults() const{//by pmd
    bool b = true;
    QHash<QString,QStringList> hash;
    QStringList listeActes;
                listeActes      << "id_acte_dispo"
                                << "nom_acte"
                                << "desc_acte"
                                << "type"
                                << "montant_total"
                                << "montant_tiers"
                                << "date_effet";
    QStringList listeMouvements;
                listeMouvements <<  "id_mvt_dispo"
                                <<  "id_mvt_parent"
                                <<  "type"
                                <<  "libelle"
                                <<  "remarque";
    QStringList listPourcentages;
                listPourcentages<<  "id_pourcent"
                                <<  "type"
                                <<  "pourcentage";

    QStringList listUtilisateurs;
                listUtilisateurs<<  "id_usr"
                                <<  "nom_usr"
                                <<  "login"
                                <<  "password"
                                <<  "id_drtux_usr";

    QStringList listComptesBancaires;
                listComptesBancaires << "id_compte"
                                     << "id_usr"
                                     << "libelle"
                                     << "titulaire"
                                     << "nom_banque"
                                     << "rib_code_banque"
                                     << "rib_code_guichet"
                                     << "rib_numcompte"
                                     << "rib_cle"
                                     << "solde_initial"
                                     << "remarque" ;

     QStringList listInformations;
                 listInformations << "VERSION";
     QStringList listThesaurus;
                 listThesaurus << "id_thesaurus"
                               << "sequence"
                               << "type";
     QStringList listForfaits;
                 listForfaits  << "id_forfaits"
                               << "forfaits"
                               << "valeur"
                               << "type";
     QStringList listIndemnites_deplacement;
                 listIndemnites_deplacement << "id_IK"
                                            << "type"
                                            << "value"
                                            << "abattement"
                                            << "indemnites_forfait";

                hash.insert("actes_disponibles",listeActes);
                hash.insert("mouvements_disponibles",listeMouvements);
                hash.insert("pourcentages",listPourcentages);
                hash.insert("utilisateurs",listUtilisateurs);
                hash.insert("comptes_bancaires",listComptesBancaires);
                hash.insert("INFORMATIONS",listInformations);
                hash.insert("thesaurus",listThesaurus);
                hash.insert("forfaits",listForfaits);
                hash.insert("indemnites_deplacement",listIndemnites_deplacement);


    QSqlDatabase db = QSqlDatabase::database(Constants::DB_ACCOUNTANCY);
    QDir dirDump(Common::ICore::instance()->settings()->applicationResourcesPath()+"/csv_files");
    qDebug() << "Common::ICore::instance()->settings()->applicationResourcesPath()/csv_files) ="+Common::ICore::instance()->settings()->applicationResourcesPath()+"/csv_files";
    QStringList filters;
                filters << "*.csv";
    dirDump.setNameFilters(filters);
    QStringList listDump;
                listDump = dirDump.entryList(filters);
     for(int i = 0 ; i < listDump.size() ; i++){
         QStringList listNameTable = listDump[i].split(".");
         QString table = listNameTable[0];
         qDebug() << __FILE__ << QString::number(__LINE__) << "table for default = " << table;
     //---------récupérer les champs---------------
         QStringList listFields = hash[table];
         QString csvFile = Common::ICore::instance()->settings()->applicationBundlePath()+"/../resources/csv_files/"+listDump[i];
         QFile file(csvFile);
         if(!file.open(QIODevice::ReadOnly)){
             QMessageBox::warning(0,QObject::tr("Erreur"),QObject::trUtf8("Fichier.csv non trouvé."),QMessageBox::Ok);
             return false;
         }
         QTextStream stream(&file);
                    stream.setCodec("UTF-8");//ISO 8859-1
         while(!stream.atEnd()){
             QString ligne = stream.readLine();
             if(ligne.isEmpty()){
                 break;
             }
                     ligne.replace("\"","'");
                     ligne.remove(QRegExp("[\n]"));
                     ligne.remove(QRegExp("[\r\n]"));
             QStringList listValues = ligne.split(";");
             QStringList fields;
             QStringList values;
             for(int a = 0 ; a < listValues.size() ; a++){
                 fields << listFields[a];
                 if(listValues[a] == ""){
                     listValues[a] = "''";
                 }
                 if(listValues[a].contains("Comptes d'attente")){
                              listValues[a].replace("Comptes d'attente","Comptes d''attente");
                 }
                 values << listValues[a];
             }
             QString fieldsStr = fields.join(",");
             QString valuesStr = values.join(",");
             QString insertStr = "INSERT INTO "+table+" ("+fieldsStr+") "
                                 "VALUES("+valuesStr+")";
             qDebug() << "INSERT INTO "+table+" ("+fieldsStr+") VALUES("+valuesStr+")";
             QSqlQuery insertQuery(db);
                       if(!insertQuery.exec(insertStr)){
                           qWarning() << trUtf8("Erreur feedAccountancyDatabaseWithDefault pour table = ")
                                      << table+":"+insertQuery.lastError().text();
                           b = false;
                       }
         }
     }
     return b;
}

bool DatabaseManager::parseDumpCcam(){
  qDebug() << "in parseDumpCcam " << QString(__FILE__)+" "+QString::number(__LINE__);
  QSqlDatabase db = QSqlDatabase::database(Constants::DB_CCAM);
  QString driver = settings()->value(Constants::S_COMPTA_DRIVER).toString();
  QApplication::desktop()->setCursor(Qt::BusyCursor);
  if(driver== "MYSQL"){
    qDebug() << "in Mysql " << __FILE__ << QString::number(__LINE__);
    QStringList listeActes;
    QString strList        = "";
    QString pathDumpActesDispo = Common::ICore::instance()->settings()->applicationResourcesPath()
                                                                      +"/sql/CCAMTest_simple.sql";

    QFile fileActesIni(pathDumpActesDispo);
      if(!fileActesIni.open(QIODevice::ReadOnly)){
        QMessageBox::warning(0,QObject::tr("Erreur"),
        QObject::trUtf8("CCAMTest_simple.sql non trouvé\npour : ")+pathDumpActesDispo+"\n"
          +QString(__FILE__)+" "+QString::number(__LINE__),QMessageBox::Ok);
        }
    QTextStream streamActes(&fileActesIni);
        streamActes.setCodec("UTF8");
    while(!streamActes.atEnd()){
        QString ligne = streamActes.readLine();
        if(!ligne.isEmpty() && !ligne.contains("*!40101")){
          strList           += ligne;
          //qDebug() << strList << " " << __FILE__ << QString::number(__LINE__);
            if (ligne.endsWith(";") && (ligne.endsWith("*//;")) == false ){
                listeActes       << strList;
                strList          = "";
                }
        }
    }
    qDebug() << "avant for"+QString(__FILE__)+" "+QString::number(__LINE__);
    qDebug() << "somme listeActes ="+QString::number(listeActes.size());
    int nbrOfTables = listeActes.size();
    for(int i = 0; i < nbrOfTables ; i++){
        QSqlQuery query(db);

        if(!query.exec(listeActes[i])){
            qDebug() << __FILE__ << " " << QString::number(__LINE__) << query.lastError().text();
            return false;
        }
    }
  }
  if(driver=="SQLITE"){
          qDebug() << "in SQLITE " << __FILE__ << QString::number(__LINE__);
    QStringList listeActes;
    QString strList        = "";
    QString pathDumpActesDispo = Common::ICore::instance()->settings()->applicationResourcesPath()
                                                                      +"/sql/CCAMTest_sqlite2.sql";

    QFile fileActesIni(pathDumpActesDispo);
      if(!fileActesIni.open(QIODevice::ReadOnly)){
        QMessageBox::warning(0,QObject::tr("Erreur"),
        QObject::trUtf8("CCAMTest_simple.sql non trouvé\npour : ")+pathDumpActesDispo+"\n"
          +QString(__FILE__)+" "+QString::number(__LINE__),QMessageBox::Ok);
        }
    QTextStream streamActes(&fileActesIni);
        streamActes.setCodec("UTF8");
    while(!streamActes.atEnd()){
        QString ligne = streamActes.readLine();
        if(!ligne.isEmpty() && !ligne.contains("*!40101") && !ligne.contains("KEY")){
          strList           += ligne;

            if (ligne.endsWith(";") && (ligne.endsWith("*//;")) == false ){
                if(strList.contains("CREATE")){
                    QStringList l = strList.split(",");
                    l.replaceInStrings(l[0],l[0]+" primary key ");
                    strList = l.join(",");
                    strList.replace(",);",");");
                }
                listeActes       << strList;
                strList          = "";
                }
        }
    }
    qDebug() << "avant for"+QString(__FILE__)+" "+QString::number(__LINE__);
    qDebug() << "somme listeActes ="+QString::number(listeActes.size());
    int nbrOfTables = listeActes.size();
    for(int i = 0; i < nbrOfTables ; i++){
        QSqlQuery query(db);

        if(!query.exec(listeActes[i])){
            qDebug() << __FILE__ << " " << QString::number(__LINE__) << query.lastError().text();
            qDebug() << __FILE__ << " " << QString::number(__LINE__) << query.lastQuery();
            QApplication::desktop()->setCursor(Qt::ArrowCursor);
            return false;
        }
    }
  }
  QApplication::desktop()->setCursor(Qt::ArrowCursor);

  return true;
}

bool DatabaseManager::testVersion(QSqlDatabase & db){
    bool test = false;
    QString readVersion;
    QSqlQuery qVersion(db);
    const QString reqVersion = QString("SELECT %1 FROM %2").arg("VERSION","INFORMATIONS");
    if (!qVersion.exec(reqVersion))
    {
    	  qWarning() << __FILE__ << QString::number(__LINE__) << qVersion.lastError().text() ;
        }
    while (qVersion.next())
    {
    	readVersion = qVersion.value(0).toString();
        }
    qWarning() << __FILE__ << QString::number(__LINE__) << "version = "+readVersion ;
    QStringList lineList;
    const QString INFORMATIONScsv = settings()->applicationResourcesPath()+"/csv_files/INFORMATIONS.csv";
    QFile infoCsvFile(INFORMATIONScsv);
    if (!infoCsvFile.open(QIODevice::ReadOnly))
    {
    	  qWarning() << __FILE__ << QString::number(__LINE__) << INFORMATIONScsv+" cannot open" ;
        }
    QTextStream stream(&infoCsvFile);
    while (!stream.atEnd())
    {
    	QString line = stream.readLine();
    	qDebug() << __FILE__ << QString::number(__LINE__) << " line =" << line ;
    	lineList << line;
        }
    if (lineList.size() !=0)
    {
    	  QString version = lineList[0].replace("\"","");
    	  qDebug() << __FILE__ << QString::number(__LINE__) << " version =" << version ;
    	  if (readVersion != version)
    	  {
    	  	  QSqlQuery qNewVersion(db);
    	  	  const QString reqNewVersion = QString("INSERT INTO %1 (%2) VALUES('%3')").arg("INFORMATIONS",
    	  	                                                                                "VERSION",
    	  	                                                                                version);
    	  	  if (qNewVersion.exec(reqNewVersion))
    	  	  {
    	  	  	  qWarning() << __FILE__ << QString::number(__LINE__) << "unable to insert newversion number" ;
    	  	      }
    	  	  test = true;
    	      }
        }
     else{
         QMessageBox::warning(0,trUtf8("Attention"),trUtf8("Pas d'info de version"),QMessageBox::Ok);
         qWarning() << __FILE__ << QString::number(__LINE__) << "Pas d'info de version" ;
         }
     return test;
}

