C++ QSqlQuery::bindValue方法代码示例

bool DbManager::helperOneLicenseYear(         const QString& first_name,
                                              const QString& last_name,
                                              const QString& municipality,
                                              const QString& zip,
                                              const QString& province,
                                              const QString& job,
                                              const QString& license,
                                              bool           is_valid,
                                              bool           is_expired)
{
    int license_validity = getLicenseValidity(getIdLicense(license));
    if(license_validity != -1) {
        QDate date_of_issue;
        date_of_issue = QDate::currentDate().addYears(-license_validity + 1);

        date_of_issue = QDate(date_of_issue.year(), 12, 31);

        QSqlQuery query;
        QString query_string;
        query_string =          "SELECT  "
                                                    "people.id, "
                                                    "people.last_name, "
                                                    "people.first_name, "
                                                    "address.street_name, "
                                                    "address.zip, "
                                                    "address.municipality, "
                                                    "address.province, "
                                                    "people.personal_code, "
                                                    "people.job, "
                                                    "people.email, "
                                                    "people.phone, "
                                                    "license.license_name, "
                                                    "people_license.is_valid, "
                                                    "people_license.date_of_issue,"
                                                    "people_license.last_notification "
                                "FROM                people "
                                "INNER JOIN          address             ON people.id = address.person "
                                "INNER JOIN          people_license      ON people_license.id_person = people.id "
                                "INNER JOIN          license             ON license.id = people_license.id_license ";

        query_string += " AND people.last_name LIKE :last_name";
        query_string += " AND people.first_name LIKE :first_name";
        query_string += " AND address.municipality LIKE :municipality";
        query_string += " AND address.zip LIKE :zip";
        query_string += " AND address.province LIKE :province";
        query_string += " AND people.job LIKE :job";
        query_string += " AND license.license_name = :license";
        query_string += " AND people_license.is_valid = 'true'";
        query_string += " AND people_license.date_of_issue <= :date_of_issue";


        query.prepare(query_string);

        query.bindValue(":last_name",       QVariant('%' + last_name + '%'));
        query.bindValue(":first_name",      QVariant('%' + first_name + '%'));
        query.bindValue(":municipality",    QVariant('%' + municipality + '%'));
        query.bindValue(":zip",             QVariant('%' + zip + '%'));
        query.bindValue(":province",        QVariant('%' + province + '%'));
        query.bindValue(":job",             QVariant('%' + job + '%'));
        query.bindValue(":license",         QVariant(license));
        query.bindValue(":date_of_issue",   QVariant(date_of_issue.toJulianDay()));

        search_results_table_model = new SqlQueryModel();
        if(query.exec()) {
            search_results_table_model->setQuery(query);
            last_show_query = query;
            return true;
        } else {
            search_results_table_model->setQuery(query);
            qDebug() << "query failed: " << query.lastError();
            return false;
        }
    } else {
        return false;
    }
}

DetailMeeting::DetailMeeting(int _meeting_id, QWidget *parent) : QDialog(parent)
{
    global_settings = new QSettings("../Thunderlook/data/settings/settings.ini", QSettings::IniFormat);

    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName(global_settings->value("SQL/addr_ip").toString());
    db.setPort(QString(global_settings->value("SQL/port").toString()).toInt());
    db.setDatabaseName("thunderlook");
    db.setUserName("esgi");
    db.setPassword("esgi");

    if (!db.open())
    {
        qDebug() << "Impossible de se connecter à la base de données." << endl;
        return;
    }

    meeting_id = _meeting_id;

    setWindowTitle("Détails de la réunion");

    QSqlQuery *req = new QSqlQuery();
    req->prepare("SELECT * FROM Meeting m WHERE id = :meeting_id");
    req->bindValue(":meeting_id", meeting_id);
    req->exec();
    QSqlRecord rec = req->record();
    req->next();

    lb_label = new QLabel(req->value(rec.indexOf("title")).toString());

    // DATE BEGIN
    QStringList date_begin = req->value(rec.indexOf("date_begin")).toString().split(" ").at(0).split("/");
    QStringList time_begin = req->value(rec.indexOf("date_begin")).toString().split(" ").at(1).split(":");

    QDate date_begin_edit(date_begin.at(0).toInt(),date_begin.at(1).toInt(),date_begin.at(2).toInt());
    QTime time_begin_edit(time_begin.at(0).toInt(),time_begin.at(1).toInt(),0);

    QDateTime date_time_begin(date_begin_edit,time_begin_edit);

    meeting_dt_begin = new QDateTimeEdit(date_time_begin);
    meeting_dt_begin->setDisplayFormat("dd/MM/yyyy hh:mm");
    meeting_dt_begin->setMinimumTime(QTime(8,0,0,0));
    meeting_dt_begin->setMaximumTime(QTime(19,45,0,0));

    // DATE END
    QStringList date_end = req->value(rec.indexOf("date_end")).toString().split(" ").at(0).split("/");
    QStringList time_end = req->value(rec.indexOf("date_end")).toString().split(" ").at(1).split(":");

    QDate date_end_edit(date_end.at(0).toInt(),date_end.at(1).toInt(),date_end.at(2).toInt());
    QTime time_end_edit(time_end.at(0).toInt(),time_end.at(1).toInt(),0);

    QDateTime date_time_end(date_end_edit,time_end_edit);

    meeting_dt_end = new QDateTimeEdit(date_time_end);
    meeting_dt_end->setDisplayFormat("dd/MM/yyyy hh:mm");
    meeting_dt_end->setMinimumTime(QTime(8,0,0,0));
    meeting_dt_end->setMaximumTime(QTime(19,45,0,0));

    meeting_duration = new QTimeEdit();
    meeting_duration->setDisplayFormat("hh:mm");

    QTime time(0,0,0);
    meeting_duration->setTime(time.addSecs(req->value(rec.indexOf("duration")).toInt()*60));

    btn_action = new QPushButton("Mettre à jour");
    btn_cancel = new QPushButton("Annuler");
    btn_del = new QPushButton("Supprimer cette réunion");

    global_settings = new QSettings("../Thunderlook/data/settings/settings.ini", QSettings::IniFormat);

    QSqlQuery *reqOrganizer = new QSqlQuery();
    reqOrganizer->prepare("SELECT Users.address FROM Meeting,Users WHERE Meeting.id = :meeting_id AND Users.id = Meeting.organizer");
    reqOrganizer->bindValue(":meeting_id", meeting_id);
    reqOrganizer->exec();
    QSqlRecord recOrganizer = reqOrganizer->record();
    reqOrganizer->next();

    // Enable Item if user is not organizer
    if(reqOrganizer->value(recOrganizer.indexOf("address")).toString() != global_settings->value("Send/smtp_user").toString())
    {
        btn_del->setEnabled(false);
        btn_action->setEnabled(false);
        meeting_dt_end->setEnabled(false);
        meeting_dt_begin->setEnabled(false);
        meeting_duration->setEnabled(false);
    }

    cb_organizer = new QComboBox();
    cb_organizer->setEnabled(false);

    QSqlQuery *reqUser = new QSqlQuery();

    reqUser->prepare("SELECT * FROM Users WHERE id = :id_user");
    reqUser->bindValue(":id_user", req->value(rec.indexOf("organizer")).toString());
    QString organiz(req->value(rec.indexOf("organizer")).toString());
    reqUser->exec();
    QSqlRecord recUser = reqUser->record();
    while(reqUser->next())
    {
        cb_organizer->addItem(reqUser->value(recUser.indexOf("address")).toString());
//.........这里部分代码省略.........

void editRoom::on_addButton_clicked()
{

    QString roomno=ui->roomName->text();

    QString nameStr= ui->catList->currentText();

    QSqlDatabase db = QSqlDatabase::addDatabase( "QSQLITE" );

    db.setDatabaseName( "./innovativedb.sqlite" );

    if( !db.open() )
    {
        qDebug() << db.lastError();
        qFatal( "MAYDAY!!!\n\nSOMETHING IS WRONG WITH YOUR DATABASE." );
    }
    qDebug( "Database Connection Validated..." );
    QSqlQuery qry;

    qry.prepare("CREATE TABLE IF NOT EXISTS roomcat (id INTEGET PRIMARY KEY, item VARCHAR(30), price INTEGER)");
    if(!qry.exec())
        qDebug() << qry.lastError();
    else
        qDebug( "Table Created!" );


    qry.prepare("SELECT id FROM roomcat WHERE item = :name");
    qry.bindValue(":name",nameStr);
    if(!qry.exec())
    {
        qDebug() << qry.lastError();
    }
    else
        qDebug( "Table Selected!" );

    int catid=0;
    while (qry.next()) {
        catid = qry.value(0).toInt();
    }
    if(catid==0)
    {
        qFatal("MAYDAY!!! DATABASE ERROR!!!");
    }

    qry.prepare("CREATE TABLE IF NOT EXISTS roomlist (id INTEGET PRIMARY KEY, roomno VARCHAR(5), cat INTEGER, occupied INTEGER)");
    if(!qry.exec())
        qDebug() << qry.lastError();
    else
        qDebug( "Room Table Validated..." );

    if(!qry.exec("SELECT id FROM roomlist"))
    {
        qDebug() << qry.lastError();
    }
    else
        qDebug( "Table Selected!" );

    int roomid=0;
    while (qry.next()) {

        int item = qry.value(0).toInt();
        if(item>roomid)
        {
            roomid=item;
        }
    }
    roomid++;

    qry.prepare("INSERT INTO roomlist (id, roomno, cat, occupied) values (:id, :roomno, :roomcat, :occ)");
    qry.bindValue(":id",roomid);
    qry.bindValue(":roomno",roomno);
    qry.bindValue(":roomcat",catid);
    qry.bindValue(":occ",0);
    if(!qry.exec())
        qDebug() << qry.lastError();
    else
    {
        qDebug( "Inserted to Room Table." );
        ui->roomList->addItem(ui->roomName->text());
        ui->roomName->clear();
    }

}

void DBWorker::deleteSetting(QString name)
{
    QSqlQuery query = prepare("DELETE FROM settings WHERE name = ?");
    query.bindValue(0, name);
    execute(query);
}

bool TestAssistance::extQuestionByType(QUESTIONTYPE type)
{
    //clear buffer
    questionBuffer.clear();
    //open database
    if (!m_objDatabase.open()) {
        packErrorInfo(m_objDatabase.lastError().text(),
                      TA_ERROR_NOTCONNECTED);
        return false;
    }

    QSqlQuery query;
    QString sql("SELECT qtxt,qhard,qimage FROM questions "
                "WHERE qmajor=:major AND qcourse=:course "
                "AND qgrade=:grade AND qtype=:type AND qknowpoint in (");

    //pack qknowpoint
    for(size_t i = 0; i != m_objPaperInfo.kpoint.size(); ++i){
        sql += "'" + m_objPaperInfo.kpoint[i] + "'";
        if(i < m_objPaperInfo.kpoint.size()-1)
            sql += ",";
    }
    sql += ") ORDER BY RANDOM() limit 100";

    query.prepare(sql);
    query.bindValue(":major", m_objPaperInfo.major);
    query.bindValue(":course", m_objPaperInfo.strCourse);
    query.bindValue(":grade", m_objPaperInfo.nGrade);
    query.bindValue(":type", static_cast<int>(type));

    if(query.exec()){
        int maxNum = typeNum(type);
        int easy = floor(maxNum*m_objPaperInfo.fEasy);
        int normal = maxNum*m_objPaperInfo.fNormal;
        int hard = maxNum*m_objPaperInfo.fHard;

        //if sum is not equal to maxNum, set normal=normal+rest
        if(easy + normal + hard < maxNum)
            normal = maxNum - easy - hard;

        QUESTION q;
        int max = typeNum(type);
        while(query.next()){
            if(questionBuffer.size() >= max)
                break;
            int t = query.value(1).toInt();
            if(easy > 0 && t == TA_EASY){
                q.txt = query.value(0).toString();
                q.imgBytes = query.value(2).toByteArray();
                questionBuffer.push_back(q);
                easy--;maxNum--;
                continue;
            }
            if(normal > 0 && t == TA_NORMAL){
                q.txt = query.value(0).toString();
                q.imgBytes = query.value(2).toByteArray();
                questionBuffer.push_back(q);
                normal--;maxNum--;
                continue;
            }
            if(hard > 0 && t == TA_HARD){
                q.txt = query.value(0).toString();
                q.imgBytes = query.value(2).toByteArray();
                questionBuffer.push_back(q);
                hard--;maxNum--;
                continue;
            }
            if(maxNum > 0){
                q.txt = query.value(0).toString();
                q.imgBytes = query.value(2).toByteArray();
                questionBuffer.push_back(q);
                maxNum--;
            }
        }
    }
    else{
        packErrorInfo(query.lastError().text(),
                      TA_ERROR_SQLERROR);
        m_objDatabase.close();
        return false;
    }
    m_objDatabase.close();
    return true;
}

void ITunesFeature::parsePlaylist(QXmlStreamReader &xml, QSqlQuery &query_insert_to_playlists,
                                  QSqlQuery &query_insert_to_playlist_tracks, TreeItem* root) {
    //qDebug() << "Parse Playlist";

    QString playlistname;
    int playlist_id = -1;
    int playlist_position = -1;
    int track_reference = -1;
    //indicates that we haven't found the <
    bool isSystemPlaylist = false;
    bool isPlaylistItemsStarted = false;

    QString key;


    //We process and iterate the <dict> tags holding playlist summary information here
    while (!xml.atEnd() && !m_cancelImport) {
        xml.readNext();

        if (xml.isStartElement()) {

            if (xml.name() == "key") {
                QString key = xml.readElementText();
                // The rules are processed in sequence
                // That is, XML is ordered.
                // For iTunes Playlist names are always followed by the ID.
                // Afterwars the playlist entries occur
                if (key == "Name") {
                    readNextStartElement(xml);
                    playlistname = xml.readElementText();
                    continue;
                }
                //When parsing the ID, the playlistname has already been found
                if (key == "Playlist ID") {
                    readNextStartElement(xml);
                    playlist_id = xml.readElementText().toInt();
                    playlist_position = 1;
                    continue;
                }
                //Hide playlists that are system playlists
                if (key == "Master" || key == "Movies" || key == "TV Shows" ||
                    key == "Music" || key == "Books" || key == "Purchased") {
                    isSystemPlaylist = true;
                    continue;
                }

                if (key == "Playlist Items") {
                    isPlaylistItemsStarted = true;
                    
                    //if the playlist is prebuild don't hit the database
                    if (isSystemPlaylist) continue;
                    query_insert_to_playlists.bindValue(":id", playlist_id);
                    query_insert_to_playlists.bindValue(":name", playlistname);

                    bool success = query_insert_to_playlists.exec();
                    if (!success) {
                        qDebug() << "SQL Error in ITunesTableModel.cpp: line" << __LINE__
                                 << " " << query_insert_to_playlists.lastError();
                        return;
                    }
                    //append the playlist to the child model
                    root->appendChild(playlistname);
                }
                // When processing playlist entries, playlist name and id have
                // already been processed and persisted
                if (key == "Track ID") {

                    readNextStartElement(xml);
                    track_reference = xml.readElementText().toInt();

                    query_insert_to_playlist_tracks.bindValue(":playlist_id", playlist_id);
                    query_insert_to_playlist_tracks.bindValue(":track_id", track_reference);
                    query_insert_to_playlist_tracks.bindValue(":position", playlist_position++);

                    //Insert tracks if we are not in a pre-build playlist
                    if (!isSystemPlaylist && !query_insert_to_playlist_tracks.exec()) {
                        qDebug() << "SQL Error in ITunesFeature.cpp: line" << __LINE__ << " "
                                 << query_insert_to_playlist_tracks.lastError();
                        qDebug() << "trackid" << track_reference;
                        qDebug() << "playlistname; " << playlistname;
                        qDebug() << "-----------------";
                    }
                }
            }
        }
        if (xml.isEndElement()) {
            if (xml.name() == "array") {
                //qDebug() << "exit playlist";
                break;
            }
            if (xml.name() == "dict" && !isPlaylistItemsStarted){
                // Some playlists can be empty, so we need to exit.
                break;
            }
        }
    }
}

void EspecieView::deleteClicked()
{
	if (tableView->currentIndex().row() < 0)
	{
		return;
	}

	QSqlRecord record = model->record(tableView->currentIndex().row());
	int cod = record.value("cod_materialespecie").toInt();
	QString nome = record.value("nome").toString();

	QMessageBox messageBox(QMessageBox::Question, "Admin",
			QString::fromUtf8("Confirma a exclusão do registro <b>%1</b>?").arg(nome), QMessageBox::Yes | QMessageBox::No, this);

	if (messageBox.exec() == QMessageBox::Yes)
	{
		QString detailedText;

		QSqlQuery sql;
		sql.prepare("delete from materialespecie where cod_materialespecie = :cod");
		sql.bindValue(":cod", cod);

		if (!sql.exec())
		{
			QSqlQuery query1;
			query1.prepare("select nome from materiaprima where cod_materialespecie = :cod");
			query1.bindValue(":cod", cod);

			if (query1.exec())
			{
				while (query1.next()) {
					detailedText.append(query1.value(query1.record().indexOf("nome")).toString());
					detailedText.append("\n");
				}
			}

			QSqlQuery query2;
			query2.prepare("delete from materiaprima where cod_materialespecie = :cod");
			query2.bindValue(":cod", cod);

			QMessageBox messageBox;
			messageBox.setWindowTitle("Admin");
			messageBox.setText(QString::fromUtf8(
					"Existe alguma matéria prima sendo referenciada por este material (veja em detalhes)\n"
					"tentar uma exclusão em cadeia forçada?"
				));
			messageBox.setDetailedText(detailedText);
			messageBox.setIcon(QMessageBox::Critical);
			messageBox.setStandardButtons(QMessageBox::Yes | QMessageBox::No);

			switch(messageBox.exec())
			{
				case QMessageBox::Yes:
						query2.exec();
						sql.exec();
					break;

				default:
					break;
			}
		}

		model->select();
	}

}

void MainWindow::addCrossing()
{
	QString curState = ui->comboCurState->currentText();
	QString toState = ui->comboToState->currentText();
	quint32 odometer = ui->spinOdometer->value();
	quint32 curtime = ui->dteTime->dateTime().toTime_t();
	QSqlQuery query;
	Command entry = {"Crossings", odometer, curState};
	
	if( ui->comboCurState->currentIndex() == 0 || ui->comboToState->currentIndex() == 0 )
		return;
	
	query.exec("SELECT Odometer,Destination from Crossings ORDER BY Odometer;");
	query.last();
	QSqlRecord prevresult = query.record();

	int dist = 0;
	if( curState == prevresult.value("Destination").toString() ){
		// calculate distance traveled
		dist = odometer - prevresult.value("Odometer").toInt();

	}
	
	// add the entry to Crossings table
	query.prepare("INSERT INTO Crossings (Date, Odometer, Origin, Destination, Distance) "
				  "VALUES (:date, :odo,:orig,:dest,:dist);");
	query.bindValue(":date", curtime);
	query.bindValue(":odo", odometer);
	query.bindValue(":orig", curState);
	query.bindValue(":dest", toState);
	query.bindValue(":dist", dist);
	query.exec();

	// handle the undo entry
	undoStack.push(entry);
	
	// calculate the updated statistics
	query.prepare("UPDATE States SET "
		"Mileage= (SELECT SUM(Distance) FROM Crossings WHERE Origin=:state1), "
		"NumEntries= (SELECT COUNT(*) FROM Crossings WHERE Origin=:state2 AND Distance<>0) "
		"WHERE Abbrev=:state3;");
	query.bindValue(":state1", curState);
	query.bindValue(":state2", curState);
	query.bindValue(":state3", curState);
	query.exec();
	emit dataUpdated();
	
	// update the log
	addToLog(QString("%1 [%L2]: Crossed from %3 to %4, traveling %5 miles in %3.")
		.arg(QDateTime::fromTime_t(curtime).toString(dtFormat))
		.arg(odometer)
	        .arg(curState)
		.arg(toState)
		.arg(dist)
	);

	// update the widgets
	ui->dteTime->setTime( ui->dteTime->time().addSecs(60) );
	ui->comboCurState->setCurrentIndex(ui->comboToState->currentIndex());
	ui->comboToState->setCurrentIndex(0);
	ui->action_Undo->setEnabled(true);
}

void PostSizTableForm::editRecord()
{
    if(checkingFill()){
        QTextStream stream(&exchangeFile);
        QString line;
        while(!stream.atEnd()){
            stream.readLine();
        }
        if(updateRecord){
            QSqlQuery query;
            query.prepare("UPDATE postsiz SET "
                          "postsizname = :postsizname "
                          "WHERE postsizid = :postsizid");
            query.bindValue(":postsizname",editPostSIZ->text().simplified());
            query.bindValue(":postsizid",indexTemp);
            query.exec();
            if(!query.isActive()){
                QMessageBox::warning(this,QObject::trUtf8("Post SIZ, UPDATE ERROR!"),query.lastError().text());
                return;
            }
            line += "UPDATE postsiz SET postsizname = '";
            line += editPostSIZ->text().toUtf8();
            line += "' WHERE postsizid = '";
            line += indexTemp.toUtf8();
            line += "'";
            line += "\r\n";
            stream<<line;
        }else{
            QSqlQuery query;
            query.prepare("SELECT * FROM postsiz WHERE postsizname = :postsizname");
            query.bindValue(":postsizname",editPostSIZ->text().simplified());
            query.exec();
            query.next();
            if(!query.isValid()){
                QSqlQuery queryInsert;
                queryInsert.prepare("INSERT INTO postsiz ("
                                    "postsizid, "
                                    "postsizname"
                                    ") VALUES(:postsizid, "
                                    ":postsizname"
                                    ");");
                queryInsert.bindValue(":postsizid",indexTemp);
                queryInsert.bindValue(":postsizname",editPostSIZ->text().simplified());
                queryInsert.exec();
                if(!queryInsert.isActive()){
                    QMessageBox::warning(this,QObject::trUtf8("Post SIZ, INSERT ERROR!"),queryInsert.lastError().text());
                    return;
                }
                line += "INSERT INTO postsiz (postsizid, postsizname) VALUES('";
                line += indexTemp.toUtf8();
                line += "', ";
                line += editPostSIZ->text().simplified().toUtf8();
                line += "'";
                line += "\r\n";
                stream<<line;
            }else{
                QString tempString = trUtf8("Документ Сиз-Нормы");
                tempString += editPostSIZ->text().toUtf8();
                tempString += QObject::trUtf8(" - существует!");
                QMessageBox::warning(this,QObject::trUtf8("Внимание!!!"),tempString);
            }
        }
    }
}

//.........这里部分代码省略.........
  }

  db.setDatabaseName(dbName);
  db.setUserName(username);
  db.setPassword(passwd);
  db.setHostName(hostName);
  db.setPort(port.toInt());

  if (!db.open() && autoRunArg)
  {
    handler->message(QtFatalMsg,
                     QObject::tr("Unable to connect to the database "
                                 "with the given information."));
    return 1;
  }

  if (!db.open())
  {
    ParameterList params;
    params.append("name",      Updater::name);
    params.append("copyright", Updater::copyright);
    params.append("version",   Updater::version);
    params.append("build",     Updater::build);
    params.append("username",  username);

    if (haveDatabaseURL)
      params.append("databaseURL", _databaseURL.toLatin1().data());

    login2 newdlg(0, "", true);
    newdlg.set(params, 0);

    if (newdlg.exec() == QDialog::Rejected)
      return 2;

    _databaseURL = newdlg._databaseURL;
    username     = newdlg._user;
    Updater::loggedIn = true;
    mainwin->setWindowTitle();

    QSqlQuery set("SET standard_conforming_strings TO true;");
    if (set.lastError().type() != QSqlError::NoError)
      handler->message(QtWarningMsg,
                       QObject::tr("Unable to set standard_conforming_strings. "
                                   "Updates may fail with unexpected errors."));

    QSqlQuery su;
    su.prepare("SELECT rolsuper FROM pg_roles WHERE (rolname=:user);");
    su.bindValue(":user", username);
    su.exec();
    if (su.first())
    {
      if (! su.value(0).toBool() &&
          handler->question(QObject::tr("You are not logged in as a "
                                        "database super user. The update "
                                        "may fail. Are you sure you want "
                                        "to continue?"),
                            QMessageBox::Yes | QMessageBox::No,
                            QMessageBox::No) == QMessageBox::No)
        return 3;
    }
    else if (su.lastError().type() != QSqlError::NoError &&
             handler->question(QObject::tr("<p>The application received a database "
                                           "error while trying to check the user "
                                           "status of %1. Would you like to try to "
                                           "update anyway?</p><pre>%2</pre>")
                            .arg(username, su.lastError().databaseText()),
                            QMessageBox::Yes | QMessageBox::No,
                            QMessageBox::No) == QMessageBox::No)
      return 4;
  }

  if (! pkgfile.isEmpty())
  {
    autoRunCheck = mainwin->openFile(pkgfile);
  }

  if (autoRunArg)
  {
    bool successful = autoRunCheck && ! pkgfile.isEmpty();
    if (successful)
    {
      successful = mainwin->sStart();
    }
    if (successful)     // not else if
      return 0;
    else
    {
#ifdef Q_OS_WIN32
      mainwin->show();
#else
      qWarning("%s", qPrintable(mainwin->_text->toPlainText()));
      return 5;
#endif
    }
  }
  else
    mainwin->show();

  return app.exec();
}

void Item::db_reid( const QString &from_id, const QString &to_id ) {
    Logger log( "void Item::db_reid( const QString &from_id, const QString &to_id )" );
    // This method would have been a lot easier, if "label" was not the same as "id"...

    log.stream( debug ) << "Will try to reid from id '" << from_id << "' to '" << to_id << "'";

    // Source must exist, dest must not
    if ( ! db_exists( from_id ) ) {
        throw Exception( Errors::ItemDoesNotExist )
                << ( log.stream( error )
                    << "Item with id '" << from_id << " does not exist, so it can not get a new id." );
    }
    if ( db_exists( to_id ) ) {
        throw Exception( Errors::ItemAlreadyExists )
                << ( log.stream( error )
                    << "Item with id '" << to_id << " already exist, so item with id '" << from_id << "' can not get this as a new id." );
    }

    // Make sure that the temp table is not in the way, so drop it now.
    {
        log.stream( debug ) << "Dropping temporary table temp_item_reid if it exists";
        QSqlQuery query;
        log.stream( debug ) << "Query.isActive is " << query.isActive()
                            << ", isValid is " << query.isValid();
        query_check_prepare( query,
                            "drop table if exists temp_item_reid" );
        query_check_exec( query );
    }
    // Now, copy all from from_id to to_id in items, using a temporary table.
    // The temporary table is needed because of foreign key constraints...
    // Need a transaction for this.
    database_transaction( "Item::db_reid( const QString &from_id, const QString &to_id )" );
    try {
        QSqlQuery query;
        log.stream( debug ) << "Creating temporary table to insert data that needs id change into";
        query_check_prepare( query, "create temporary table temp_item_reid as select * from items where id = :from_id");
        query.bindValue( ":from_id", from_id );
        query_check_exec( query );
        // Change the id
        log.stream( debug ) << "Changing the id of the item in the temporary table";
        query_check_prepare( query, "update temp_item_reid set id = :to_id" );
        query.bindValue( ":to_id", to_id );
        query_check_exec( query );
        // Copy back, that is, insert it into items
        log.stream( debug ) << "Copying modified item back into the items table";
        query_check_prepare( query,
                            "insert into items select * from temp_item_reid" );
        query_check_exec( query );
        log.stream( debug ) << "Modifying contractitems to point at the new id";
        // Now, update contract items and itemevents, to point at the new row
        query_check_prepare( query, "update contractitems set item_id = :to_id where item_id = :from_id");
        query.bindValue( ":to_id", to_id );
        query.bindValue( ":from_id", from_id );
        query_check_exec( query );
        log.stream( debug ) << "Modifying itemevents to point at the new id";
        query_check_prepare( query, "update itemevents set item_id = :to_id where item_id = :from_id");
        query.bindValue( ":to_id", to_id );
        query.bindValue( ":from_id", from_id );
        query_check_exec( query );
        // Delete the old item row
        log.stream( debug ) << "Deleting the old item";
        query_check_prepare( query, "delete from items where id = :from_id" );
        query.bindValue( ":from_id", from_id );
        query_check_exec( query );
        // Insert an event for the new id for this
        log.stream( todo ) << "Should add an event line at this point";
        addEventLine( to_id, DB::Item::Event::reid,
                     QString( "Reid from id '%1' to id '%2' (Item::db_reid)" )
                     .arg( from_id ).arg( to_id ) );
    }
    catch( ... ) {
        log.stream( error ) << "Got some kind of error during transaction, trying rollback";
        database_rollback( "Item::db_reid( const QString &from_id, const QString &to_id ) error catcher" );
        throw;
    }
    log.stream( debug ) << "All is well, about to commit";
    database_commit( "Item::db_reid( const QString &from_id, const QString &to_id ) - all well" );
    log.stream( info ) << "Has commit item id change from id '" << from_id << "' to '" << to_id << "'";

}

//missing the year restriction
bool DbManager::helperAllLicensesYear(        const QString& first_name,
                                              const QString& last_name,
                                              const QString& municipality,
                                              const QString& zip,
                                              const QString& province,
                                              const QString& job,
                                              bool           is_valid,
                                              bool           is_expired)
{
    QSqlQuery query;
    QString query_string = "SELECT  "
                                                "people.id, "
                                                "people.last_name, "
                                                "people.first_name, "
                                                "address.street_name, "
                                                "address.zip, "
                                                "address.municipality, "
                                                "address.province, "
                                                "people.personal_code, "
                                                "people.job, "
                                                "people.email, "
                                                "people.phone, "
                                                "license.license_name, "
                                                "people_license.is_valid, "
                                                "people_license.date_of_issue,"
                                                "people_license.last_notification "
                            "FROM                people "
                            "INNER JOIN          address             ON people.id = address.person "
                            "INNER JOIN          people_license      ON people_license.id_person = people.id "
                            "INNER JOIN          license             ON license.id = people_license.id_license ";

    query_string += " AND people.last_name LIKE :last_name";
    query_string += " AND people.first_name LIKE :first_name";
    query_string += " AND address.municipality LIKE :municipality";
    query_string += " AND address.zip LIKE :zip";
    query_string += " AND address.province LIKE :province";
    query_string += " AND people.job LIKE :job";
    if(is_valid) {
        query_string += " AND people_license.is_valid = 'true'";
    } else if(is_expired) {
        query_string += " AND people_license.is_valid = 'false'";
    }

    query.prepare(query_string);

    query.bindValue(":last_name",       QVariant('%' + last_name + '%'));
    query.bindValue(":first_name",      QVariant('%' + first_name + '%'));
    query.bindValue(":municipality",    QVariant('%' + municipality + '%'));
    query.bindValue(":zip",             QVariant('%' + zip + '%'));
    query.bindValue(":province",        QVariant('%' + province + '%'));
    query.bindValue(":job",             QVariant('%' + job + '%'));

    search_results_table_model = new SqlQueryModel();
    if(query.exec()) {
        search_results_table_model->setQuery(query);
        last_show_query = query;
        return true;
    } else {
        search_results_table_model->setQuery(query);
        qDebug() << "query failed: " << query.lastError();
        return false;
    }
}

void AdminWindow::on_btn_ViewDetailTransaction_clicked()
{
    QSqlQueryModel* model = new QSqlQueryModel();
    transactionTable = new QTableView();

    QSqlQuery qr;

    float grandTotalSum = 0.0f;
    float grandDiscount = 0.0f;
    float grandVat = 0.0f;

   // qr.prepare("SELECT TransactionsDetails.TransactionID, (SUM(TransactionsDetails.SubTotal)-Transactions.discount) AS TotalSum, Transactions.discount, datetime(Transactions.period, 'unixepoch', 'localtime'), Transactions.boy, Transactions.dining FROM TransactionsDetails, Transactions WHERE Transactions.id = TransactionsDetails.TransactionID AND (datetime(period, 'unixepoch', 'localtime') >= datetime(:fromDate, 'unixepoch', 'localtime')  AND datetime(period, 'unixepoch', 'localtime') <= datetime(:toDate, 'unixepoch', 'localtime')) GROUP BY TransactionsDetails.TransactionID");
    if(userSelectedForTransaction == "ALL")
    {
        qr.prepare("SELECT TransactionsDetails.TransactionID, (SUM(TransactionsDetails.SubTotal)-((Transactions.discount*SUM(TransactionsDetails.SubTotal))/100) + ((Transactions.vat*SUM(TransactionsDetails.SubTotal))/100) ) AS TotalSum, Transactions.discount, Transactions.vat, datetime(Transactions.period, 'unixepoch', 'localtime') AS Transactions_Time, Transactions.boy, Transactions.dining, Transactions.user, Transactions.comments FROM TransactionsDetails, Transactions WHERE Transactions.id = TransactionsDetails.TransactionID AND ((datetime(period, 'unixepoch', 'localtime') >= datetime(:fromDate, 'unixepoch', 'localtime')  AND datetime(period, 'unixepoch', 'localtime') <= datetime(:toDate, 'unixepoch', 'localtime'))) GROUP BY TransactionsDetails.TransactionID");
        qr.bindValue(":toDate", QString::number(toDateTime.toTime_t()));
        qr.bindValue(":fromDate", QString::number(fromDateTime.toTime_t()));

    }


    else
    {
        qr.prepare("SELECT TransactionsDetails.TransactionID, (SUM(TransactionsDetails.SubTotal)-((Transactions.discount*SUM(TransactionsDetails.SubTotal))/100) + ((Transactions.vat*SUM(TransactionsDetails.SubTotal))/100) ) AS TotalSum, Transactions.discount, Transactions.vat, datetime(Transactions.period, 'unixepoch', 'localtime') AS Transactions_Time, Transactions.boy, Transactions.dining, Transactions.user, Transactions.comments FROM TransactionsDetails, Transactions WHERE Transactions.id = TransactionsDetails.TransactionID AND ((datetime(period, 'unixepoch', 'localtime') >= datetime(:fromDate, 'unixepoch', 'localtime')  AND datetime(period, 'unixepoch', 'localtime') <= datetime(:toDate, 'unixepoch', 'localtime')) AND user = :user) GROUP BY TransactionsDetails.TransactionID");
        qr.bindValue(":toDate", QString::number(toDateTime.toTime_t()));
        qr.bindValue(":fromDate", QString::number(fromDateTime.toTime_t()));
        qr.bindValue(":user", userSelectedForTransaction);

    }


   // qr.prepare("SELECT * FROM Transactions WHERE datetime(period, 'unixepoch', 'localtime') >= datetime(:curr, 'unixepoch', 'localtime')");
  //  qr.bindValue(":curr",QString::number(QDateTime::currentDateTime().toTime_t()));
   // qr.bindValue(":unixepoch", "unixepoch");
    bool r = qr.exec();
    if(r)
    {
           model->setQuery(qr);
           transactionTable->setModel(model);

           int count = transactionTable->model()->rowCount();
           for(int i=0; i<count; i++)
           {

               float sum =  transactionTable->model()->data(transactionTable->model()->index(i,1)).toFloat();
               float dis =  transactionTable->model()->data(transactionTable->model()->index(i,2)).toFloat();
               float vat =  transactionTable->model()->data(transactionTable->model()->index(i,3)).toFloat();

               grandTotalSum += sum;

               grandDiscount += ((sum*100)/(100-dis))*(dis/100);
               grandVat += ((sum*100)/(100+vat))*(vat/100);



           }



//        while(qr.next())
//        {
//            qDebug()<<"nazib.........";

//            grandTotalSum += qr.record().value("TotalSum").toFloat();

//            qDebug()<< qr.record().value("TotalSum").toString();
//            qDebug()<< qr.record().value("TransactionID").toString();
//            qDebug()<< qr.record().value("period").toString();
//            qDebug()<< qr.record().value("boy").toString();
//            qDebug()<< qr.record().value("dining").toString();


//        }

           ui->lbl_totalSum->setText("Total : "+QString::number(grandTotalSum)+" <----> Total Discount: "+QString::number(grandDiscount)+" <----> Total Vat: "+QString::number(grandVat));

         grandTotalSum = 0.0f;
         grandDiscount = 0.0f;
         grandVat = 0.0f;

        connect(transactionTable, SIGNAL(doubleClicked(QModelIndex)), this, SLOT(onTransactionTableDoubleClicked(QModelIndex)), Qt::UniqueConnection);

        transactionTable->resizeColumnsToContents();
        transactionTable->horizontalHeader()->setStretchLastSection(true);
        ui->scrollArea_Detail->setWidget(transactionTable);

    }
    else
    {
        qDebug()<<qr.lastError();
    }
}

void PostSizTableForm::addRecordOfTable()
{
    if(checkingFill()){
        QTextStream stream(&exchangeFile);
        QString line;
        while(!stream.atEnd()){
            stream.readLine();
        }

        ViewListTable listTable("","siznaim",this);
        listTable.exec();
        QString postId = listTable.returnValue();

        QSqlQuery query;
        query.prepare("SELECT * FROM siznaim WHERE siznaimid = :id");
        query.bindValue(":id",postId);
        query.exec();
        while(query.next()){
            int rowCount = sizView->rowCount();
            bool insert = true;
            //Проверка на существование записи
            if (rowCount != 0){
                for(int kk = 0; kk < rowCount; ++kk){
                    QString yy = sizView->item(kk,0)->text();
                    QString pp = query.value(0).toString();
                    if(yy == pp){
                        QString tempString = query.value(1).toString();
                        tempString += QObject::trUtf8(" is availble!");
                        QMessageBox::warning(this,QObject::trUtf8("Atention!!!"),tempString);
                        insert = false;
                        break;
                    }
                }
            }
            if(insert){
//                addIntoTable = true;
//                sizView->insertRow(rowCount);

//                QTableWidgetItem *itemID = new QTableWidgetItem;
//                postView->setItem(rowCount,1,itemID);
//                postView->item(rowCount,1)->setText(query.value(0).toString());

//                QTableWidgetItem *itemName = new QTableWidgetItem;
//                //itemName->setTextAlignment(Qt::AlignCenter);
//                postView->setItem(rowCount,2,itemName);
//                postView->item(rowCount,3)->setText(query.value(1).toString());

//                NumPrefix numPrefix;
//                QString idPostSizList = numPrefix.getPrefix("postsiztable");

//                QSqlQuery queryPSL;
//                queryPSL.prepare("INSERT INTO postsizlist ("
//                                "postsizlistid, postsizid, postid"
//                                ") VALUES(:postsizlistid, :postsizid, :postid)");
//                queryPSL.bindValue(":postsizlistid",idPostSizList);
//                queryPSL.bindValue(":postsizid",indexTemp);
//                queryPSL.bindValue(":postid",query.value(0).toString());
//                queryPSL.exec();
//                if(!queryPSL.isActive()){
//                    QMessageBox::warning(this,QObject::trUtf8("Post SIZ List Table, INSERT ERROR!"),queryPSL.lastError().text());
//                    return;
//                }
//                line += "INSERT INTO postsizlist (postsizlistid, postsizid, postid) VALUES('";
//                line += idPostSizList.toUtf8();
//                line += "', '";
//                line += indexTemp.toUtf8();
//                line += "', '";
//                line += query.value(0).toString().toUtf8();
//                line += "')";
//                line += "\r\n";
//                stream<<line;
            }
        }
    }
}

//.........这里部分代码省略.........
                }
                if (key == "Album") {
                    album = content;
                    continue;
                }
                if (key == "Album Artist") {
                    album_artist = content;
                    continue;
                }
                if (key == "Genre") {
                    genre = content;
                    continue;
                }
                if (key == "Grouping") {
                    grouping = content;
                    continue;
                }
                if (key == "BPM") {
                    bpm = content.toInt();
                    continue;
                }
                if (key == "Bit Rate") {
                    bitrate =  content.toInt();
                    continue;
                }
                if (key == "Comments") {
                    comment = content;
                    continue;
                }
                if (key == "Total Time") {
                    playtime = (content.toInt() / 1000);
                    continue;
                }
                if (key == "Year") {
                    year = content;
                    continue;
                }
                if (key == "Location") {
                    QByteArray strlocbytes = content.toUtf8();
                    location = QUrl::fromEncoded(strlocbytes).toLocalFile();
                    // Replace first part of location with the mixxx iTunes Root
                    // on systems where iTunes installed it only strips //localhost
                    // on iTunes from foreign systems the mount point is replaced
                    if (!m_dbItunesRoot.isEmpty()) {
                        location.replace(m_dbItunesRoot, m_mixxxItunesRoot);
                    }
                    continue;
                }
                if (key == "Track Number") {
                    tracknumber = content;
                    continue;
                }
                if (key == "Rating") {
                    //value is an integer and ranges from 0 to 100
                    rating = (content.toInt() / 20);
                    continue;
                }
                if (key == "Track Type") {
                    tracktype = content;
                    continue;
                }
            }
        }
        //exit loop on closing </dict>
        if (xml.isEndElement() && xml.name() == "dict") {
            break;
        }
    }

    // If file is a remote file from iTunes Match, don't save it to the database.
    // There's no way that mixxx can access it.
    if (tracktype == "Remote") {
        return;
    }

    // If we reach the end of <dict>
    // Save parsed track to database
    query.bindValue(":id", id);
    query.bindValue(":artist", artist);
    query.bindValue(":title", title);
    query.bindValue(":album", album);
    query.bindValue(":album_artist", album_artist);
    query.bindValue(":genre", genre);
    query.bindValue(":grouping", grouping);
    query.bindValue(":year", year);
    query.bindValue(":duration", playtime);
    query.bindValue(":location", location);
    query.bindValue(":rating", rating);
    query.bindValue(":comment", comment);
    query.bindValue(":tracknumber", tracknumber);
    query.bindValue(":bpm", bpm);
    query.bindValue(":bitrate", bitrate);

    bool success = query.exec();

    if (!success) {
        LOG_FAILED_QUERY(query);
        return;
    }
}