//插入一行记录
bool DBModule::insertLine( const QString &title, const QString &artist, const QString &time, const QString &filePath )
{
QSqlQuery query;
query.prepare("insert into musicList values(?, ?, ?, ?)");
query.addBindValue(title);
query.addBindValue(artist);
query.addBindValue(time);
query.addBindValue(filePath);
return query.exec();
}
bool Storage::saveFriends(QJsonArray data)
{
QDateTime time;
QString now = time.toLocalTime().toString();
QSqlQuery query;
query.exec("drop table friend");
query.exec("create table friend (id INTEGER primary key autoincrement, "
"userid varchar(30), realname varchar(30), username varchar(30), email varchar(30),"
"image varchar(100), lastchat text, lastchattime varchar(20), lastseen varchar(30), updated varchar(30), created varchar(30))");
qDebug() << query.lastError().databaseText();
foreach (QJsonValue value, data) {
QJsonObject dataObject = value.toObject();
query.prepare("INSERT INTO friend (userid, realname, username, email, image, lastchat, lastchattime, lastseen, updated, created) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
query.addBindValue(dataObject.value("_id"));
query.addBindValue(dataObject.value("name"));
query.addBindValue(dataObject.value("username"));
query.addBindValue(dataObject.value("email"));
query.addBindValue("");
query.addBindValue(dataObject.value("username"));
query.addBindValue("");
query.addBindValue(now);
query.addBindValue(now);
query.addBindValue(now);
qDebug() << query.lastError().databaseText();
query.exec();
qDebug() << query.lastError().databaseText();
}
void secondDiagnostics::on_buttonBox_accepted()
{
QSqlQuery query;
query.prepare("insert into diagnosis(fk_customer, fk_doctor, type, dateD, timeD) values(?, ?, 1, ?, ?)");
query.addBindValue(custID);
query.addBindValue(docID);
query.addBindValue(ui->dateEdit->date());
query.addBindValue(ui->timeEdit->time());
query.exec();
this->deleteLater();
}
void Tag::addToMedia(int mediaId)
{
QSqlQuery query;
query.prepare("INSERT INTO " TABLE_MediaTags " (" KEY_MediaTags_MediaId ", " KEY_MediaTags_TagId ") VALUES(?, ?)");
query.addBindValue(QVariant(mediaId));
query.addBindValue(QVariant(Id));
if(!query.exec())
{
QLOG_ERROR() << "Failed to add MediaTag record: " + query.lastError().text();
}
}
bool EditableSqlModel::setL(int Id, const QString &str, int l)
{
QSqlQuery query;
if(l == 1)
query.prepare("update dict set l1 = ? where id = ?");
else
query.prepare("update dict set l2 = ? where id = ?");
query.addBindValue(str);
query.addBindValue(Id);
return query.exec();
}
bool Warning::addWarning(){
QSqlQuery query;
query.prepare("insert into Warning(bid,wtype,wmsg,wtime)""values(?,?,?,?)");
query.addBindValue(this->bid);
query.addBindValue(this->wtype);
query.addBindValue(this->wmsg);
query.addBindValue(this->wtime.toString("yyyy-MM-dd hh:mm:ss"));
return query.exec();
}
//把学生主观题答案插入主观题答案表
void DBManager::insertSubAnswers(int fpaperid,QString studentid)
{
QSqlQuery query;
query.prepare("insert into subanswers(fpaperid,studentid)" "values(?,?)");
query.addBindValue(fpaperid);
query.addBindValue(studentid);
query.exec();
qDebug() << "insertSubAnswers] " << query.lastError();
}
bool book::reverse(int vipID, QDateTime bookTime, int bookRow)
{
QSqlQuery sql;
if (!isValid(vipID) || rowIsOccupied(bookRow, bookTime))//等VIP好了以后测试
return false;
sql.prepare("INSERT INTO book_record VALUES (NULL, ?, ?, ?);");
sql.addBindValue(vipID);
sql.addBindValue(bookTime.toString("yyyy-MM-dd h:m:s"));
sql.addBindValue(bookRow);
return sql.exec();
}
bool CanceledOrder::add(Model::CanceledOrder canceledOrder)
{
DatabaseManager mgr;
QSqlQuery query;
query.prepare("INSERT INTO order_canceled(order_id, cancel_time) VALUES(?,?)");
query.addBindValue(canceledOrder.order().id());
query.addBindValue(QDateTime::currentDateTime());
return query.exec();
}
QSqlQuery DatabaseManager::insertSpriteRecord(const SpriteModel& spriteModel) {
QSqlQuery query;
query.prepare("INSERT Into Sprite (SPRITE_TYPE_ID, X, Y) VALUES(?,?,?)");
query.addBindValue(spriteModel.getSpriteTypeId());
query.addBindValue(spriteModel.getX());
query.addBindValue(spriteModel.getY());
query.exec();
QSqlQuery query2;
query2.prepare("SELECT * FROM Sprite ORDER BY SPRITE_ID DESC");
query2.exec();
return query2;
}
Database::MarkParameters Database::getValuesFromDatabase(int id_question)
{
QSqlQuery query;
query.prepare("SELECT ef, counter, date, interval "
"FROM marks "
"WHERE id_user = ? AND id_question = ?");
query.addBindValue(currentUserId_);
query.addBindValue(id_question);
query.exec();
query.next();
return MarkParameters(query.value(0).toDouble(), query.value(1).toInt(), query.value(2).toLongLong(), query.value(3).toInt());
}
bool DataBase::is_white(int id_game, QString &id_player){
cout << "is_white()" <<endl;
QSqlQuery query;
query.prepare(QString("SELECT * FROM games WHERE id = ? AND player_1 = ?;"));
query.addBindValue(id_game);
query.addBindValue(id_player);
if(!query.exec()){
cout << query.lastError().text().toStdString() << endl;
return false;
}
return query.next();
}
bool SnippetModel::addEntry(QString title, QString tags, QString language, QString code)
{
QSqlQuery query;
query.prepare("INSERT INTO SNIPPETS(id, title, tags, language, code) values(NULL, ?, ? ,?,?)");
query.addBindValue(title);
query.addBindValue(tags);
query.addBindValue(language);
query.addBindValue(code);
if(query.exec()) return true;
return false;
}
void FormGestioneFerie::on_pbAggiungiPermesso_clicked()
{
for(QDate a=ui->dePermessiDal->date(); a.operator <=( ui->dePermessiAl->date());a= a.addDays(1) )
{
qDebug() << "Permesso per "+ui->cbMatricola->currentText()+" per il giorno "+a.toString("dd/MM/yyyy");
QSqlQuery query;
query.prepare("INSERT INTO assenze (ID, matricola, causa, data)" "VALUES (?, ?, ?, ?)");
// query.addBindValue( ui->cbMatricola->currentText()+"_"+ui->dePermessiDal->date().toString("dd/MM/yyyy"));
query.addBindValue( ui->cbMatricola->currentText()+"_"+a.toString("dd/MM/yyyy"));
query.addBindValue( ui->cbMatricola->currentText());
query.addBindValue( ui->cbTipoPermesso->currentText());
// query.addBindValue( ui->dePermessiDal->date().toString("dd/MM/yyyy"));
query.addBindValue( a.toString("dd/MM/yyyy"));
if(query.exec())
{
QMessageBox msgBox;
msgBox.setText("Inserzione nel database.");
msgBox.setInformativeText(QString("Permesso per il giorno %1 inserito correttamente.").arg(a.toString("dd/MM/yyyy")));
msgBox.setStandardButtons(QMessageBox::Ok );
msgBox.setIcon(QMessageBox::Information);
msgBox.exec();
}
else
{
QMessageBox msgBox;
msgBox.setText("Inserzione nel database.");
msgBox.setInformativeText(QString("Permesso per il giorno %1 NON inserito nel database. Probabilmente è gia stato inserito un permesso per lo stesso giorno.").arg(a.toString("dd/MM/yyyy")));
msgBox.setStandardButtons(QMessageBox::Ok );
msgBox.setIcon(QMessageBox::Critical);
msgBox.exec();
}
QSqlTableModel *model = new QSqlTableModel(this, this->db);
model->setTable("assenze");
model->setEditStrategy(QSqlTableModel::OnFieldChange);
model->select();
model->setFilter(QString("Matricola=\"%1\"").arg(ui->cbMatricola->currentText()));
model->sort(2,Qt::AscendingOrder);
model->setHeaderData(1, Qt::Horizontal, tr("MATRICOLA"));
model->setHeaderData(2, Qt::Horizontal, tr("DATA"));
model->setHeaderData(3, Qt::Horizontal, tr("CAUSA"));
ui->tvPermessi->setModel(model);
ui->tvPermessi->hideColumn(0);
ui->tvPermessi->resizeColumnsToContents();
ui->tvPermessi->resizeRowsToContents();
ui->tvPermessi->selectRow(0);
}
}
void Database::writeShieldHis()
{
QSqlDatabase::database().transaction();
QDateTime datetime = QDateTime::currentDateTime();
QSqlQuery query;
query.setForwardOnly(true);
query.prepare("INSERT INTO shieldhis( time,unitid,type)"
"VALUES ( ?, ?,?)");
query.addBindValue(datetime);
query.addBindValue(0x01000000);
query.addBindValue(0x00FFFF00);
bool bSuccess = query.exec();
if (!bSuccess)
{
QSqlError lastError = query.lastError();
qDebug() << lastError.driverText()
<<QString(QObject::tr("in datebase : insert soundandalarmdevice into the shieldhis error"));
}
// QDateTime dt = QDateTime::currentDateTime();
// /*Before the opening of each transaction，adding this code to improve the speed*/
// QSqlDatabase::database().transaction();
// QSqlQuery query;
// query.prepare("INSERT INTO shieldhis( time,unitid,type)"
// "VALUES ( ?, ?,?)");
// int host = 1;
// int iobr = 1;
// int loop = 1;
// for (int i = 0;i<100; i++)
// {
// query.addBindValue(dt);
// query.addBindValue((host<<24) | (iobr<<16) | (loop<<8) | (i+1));
// query.addBindValue(0x000000FF);
// bool bSuccess = query.exec();
// if (!bSuccess)
// {
// QSqlError lastError = query.lastError();
// qDebug() << lastError.driverText()
// <<QString(QObject::tr("insert shieldhis error"));
// }
// }
/*Close the transaction*/
QSqlDatabase::database().commit();
}
QSqlQuery LocationCompleterModel::createQuery(const QString &searchString, const QString &orderBy,
const QList<QUrl> &alreadyFound, int limit, bool bookmarks, bool exactMatch)
{
QString table = bookmarks ? "bookmarks" : "history";
QString query = QString("SELECT %1.id, %1.url, %1.title, history.count").arg(table);
QStringList searchList;
if (bookmarks) {
query.append(QLatin1String(", bookmarks.icon FROM bookmarks LEFT JOIN history ON bookmarks.url=history.url "));
}
else {
query.append(QLatin1String(" FROM history "));
}
query.append(QLatin1String("WHERE "));
if (exactMatch) {
query.append(QString("%1.title LIKE ? OR %1.url LIKE ? ").arg(table));
}
else {
searchList = searchString.split(QLatin1Char(' '), QString::SkipEmptyParts);
const int slSize = searchList.size();
for (int i = 0; i < slSize; ++i) {
query.append(QString("(%1.title LIKE ? OR %1.url LIKE ?) ").arg(table));
if (i < slSize - 1) {
query.append(QLatin1String("AND "));
}
}
}
for (int i = 0; i < alreadyFound.count(); i++) {
query.append(QString("AND (NOT %1.url=?) ").arg(table));
}
if (!orderBy.isEmpty()) {
query.append("ORDER BY " + orderBy);
}
query.append(QLatin1String(" LIMIT ?"));
QSqlQuery sqlQuery;
sqlQuery.prepare(query);
if (exactMatch) {
sqlQuery.addBindValue(QString("%%1%").arg(searchString));
sqlQuery.addBindValue(QString("%%1%").arg(searchString));
}
else {
foreach(const QString & str, searchList) {
sqlQuery.addBindValue(QString("%%1%").arg(str));
sqlQuery.addBindValue(QString("%%1%").arg(str));
}
}
void SalaryDialog::on_pushButton_clicked()
{
int projectbonus;
QString projectname;
projectname = ui->comboBox->currentText();
projectbonus = ui->lineEdit->text().toInt();
QSqlQuery query;
query.prepare("update perssalaryreg set projectbonus = ? where persid = (select persid from project, workreg where project.projectname = ? and project.projectid = workreg.projectid)");
query.addBindValue(projectbonus);
query.addBindValue(projectname);
query.exec();
}
int Database::countReadyQuestions(int id_course)
{
QSqlQuery query;
query.prepare("SELECT COUNT(id_question) "
"FROM questions NATURAL JOIN marks "
"WHERE id_user = ? AND id_course = ? AND (date <= ? OR counter = 0 )");
query.addBindValue(currentUserId_);
query.addBindValue(id_course);
query.addBindValue(QDate::currentDate().toJulianDay());
query.exec();
query.next();
return query.value(0).toInt();
}
bool book::rowIsOccupied(int bookRow, QDateTime bookTime)
{
QSqlQuery sql;
QDateTime low = QDateTime::currentDateTime();
QDateTime up = low.addSecs(3600);
if (bookTime <= low && bookTime > up)
return false;
sql.prepare("SELECT FROM book_record WHERE book_row = ? AND book_date > ? AND book_date <= ?");
sql.addBindValue(bookRow);
sql.addBindValue(low.toString("yyyy-MM-dd h:m:s"));
sql.addBindValue(up.toString("yyyy-MM-dd h:m:s"));
return sql.exec();
}
bool PgArticuloTipo::Insertar(ArticuloTipo valor)
{
QSqlQuery query;
query.prepare("INSERT INTO articulo_tipo("
" nombre, codigo_imagen)"
"VALUES (?, ?);");
query.addBindValue(valor.getNombre());
query.addBindValue(valor.getCodigo_Imagen());
return query.exec();
}
bool Database::UpdateGrade(Grade grade)
{
QSqlQuery q;
if(!q.prepare(QLatin1String("update grades set peopleId=?, receiveDate=?, gradeString=? where id=?")))
LogError(q.lastError());
q.addBindValue(grade.PeopleId);
q.addBindValue(grade.Date);
q.addBindValue(grade.GradeString);
q.addBindValue(grade.Id);
auto res = q.exec();
LogError(q.lastError());
return res;
}
QVariant Database::AddGrade(Grade grade)
{
QSqlQuery q;
if(!q.prepare(QLatin1String("insert into grades(id, peopleId, receiveDate, gradeString) values(?, ?, ?, ?)")))
LogError(q.lastError());
q.addBindValue(grade.Id);
q.addBindValue(grade.PeopleId);
q.addBindValue(grade.Date);
q.addBindValue(grade.GradeString);
q.exec();
LogError(q.lastError());
return q.lastInsertId();
}
void ScreenshotManager::removeHistory(const QString &fileName, qint64 time)
{
if (!mHistoryInitialized) {
initHistory();
}
QSqlQuery removeQuery;
removeQuery.prepare("DELETE FROM history WHERE fileName = ? AND time = ?");
removeQuery.addBindValue(fileName);
removeQuery.addBindValue(time);
removeQuery.exec();
}
// ---------------------------------------------------------------------------------
success Project::update ( const dp::Project& _p ) const
{
if ( _p.id().isNull())
{
return error();
}
QSqlQuery query;
query.prepare ( UPDATE_PROJECT );
query.addBindValue ( _p.name() );
query.addBindValue ( _p.creationDate() );
query.addBindValue( _p.comment() );
query.addBindValue ( _p.id().toString() );
return execute ( query );
}
int Database::countCoursesWithReadyQuestions()
{
QSqlQuery query;
query.prepare("SELECT COUNT(how_many_questions) "
"FROM (SELECT COUNT(id_user) as how_many_questions "
"FROM coursesOfUsers NATURAL JOIN questions NATURAL JOIN marks "
"WHERE coursesOfUsers.id_user = ? AND (marks.date <= ? OR marks.counter = 0) "
"GROUP BY id_course)");
query.addBindValue(currentUserId_);
query.addBindValue(QDate::currentDate().toJulianDay());
query.exec();
query.next();
return query.value(0).toInt();
}
void DBManager::insertStudent(QString a, QString b, int c, int d, QString e)
{
QSqlQuery query;
query.prepare("insert into student (userid, name, grade, class,password) "
"values (?,?,?,?,?)");
query.addBindValue(a);
query.addBindValue(b);
query.addBindValue(c);
query.addBindValue(d);
query.addBindValue(e);
query.exec();
qDebug() << "insetStudent] " << query.lastError();
}
void TableEditor::changeEvent(QEvent* event)
{
if (event->type() == QEvent::WindowStateChange)
{
bool isMax = false;
if (windowState() == Qt::WindowMaximized)
isMax = true;
QSqlQuery qry;
qry.prepare("INSERT OR REPLACE INTO settings (setting, value) VALUES (?, ?)");
qry.addBindValue("view.table.fullscreen");
qry.addBindValue(isMax);
qry.exec();
}
}
// administrator adds a student
bool Administrator::AddStudent(const UStudent &s) const
{
/*insert a record into the student table*/
QSqlQuery query;
query.prepare("insert into Student (ID, name, birthday, gender, deptName) values (?, ?, ? ,?, ?)");
query.addBindValue(s.GetID());
query.addBindValue(s.GetName().data());
query.addBindValue(QDateTime::fromString(QString::fromStdString(s.GetBirthday()), "yyyy-MM-dd"));
if(!s.GetGender())
query.addBindValue("male");
else
query.addBindValue("female");
query.addBindValue(s.GetDeptName().data());
if(!query.exec())
return false;
/*insert a record into the user table*/
query.prepare("insert into User (ID, type, password) values (?, ?, ?)");
query.addBindValue(s.GetID());
query.addBindValue("student");
query.addBindValue(s.GetPassword());
if(!query.exec())
return false;
return true;
}
void firstDiagnostics::on_buttonBox_accepted()
{
QSqlDatabase::database().transaction();
QSqlQuery query;
query.prepare("insert into diagnosis(fk_customer, fk_doctor, type, fk_partner, dateD, timeD) values(?, ?, ?, ?, ?, ?)");
query.addBindValue(customerID);
query.addBindValue(ui->doctorCombobox->itemData(ui->doctorCombobox->currentIndex()));
query.addBindValue(0);
query.addBindValue(partnerID);
query.addBindValue(ui->dateEdit->date());
query.addBindValue(ui->timeEdit->time());
query.exec();
query.clear();
query.prepare("select fk_customerCard from Customer where id = ?");
query.addBindValue(customerID);
query.exec();
int cardID = 0;
if(query.next())
cardID = query.value(0).toInt();
query.clear();
query.prepare("insert into custCardDoctor values(?, ?)");
query.addBindValue(cardID);
query.addBindValue(ui->doctorCombobox->itemData(ui->doctorCombobox->currentIndex()));
query.exec();
if(!QSqlDatabase::database().commit())
{
QSqlError err;
err = QSqlDatabase::database().lastError();
QSqlDatabase::database().rollback();
QMessageBox::warning(this, tr("Error"), err.text());
}
this->deleteLater();
}
bool CoreData::createDBFile(QString dbname)
{
if (QFile::exists(dbname)) {
QFile::remove(dbname);
}
db.setDatabaseName(dbname);
if (!db.open()) {
QMessageBox::critical(0, QString::fromUtf8("出错了！"), QString::fromUtf8("无法打开数据库文件"));
return false;
}
QSqlQuery query;
query.exec("drop table people ");
query.exec("create table people (id int primary key, name varchar(64), gender char(8), join_date date, status tinyint, comment text)");
qDebug() << db.lastError().type() << " + " << db.lastError().text();
query.exec("drop table date");
query.exec("create table date (id int primary key, date date)");
query.exec("pragma synchronous=0");
qDebug() << db.lastError().type() << " + " << db.lastError().text();
QDate date = QDate::currentDate();
date = date.addYears(-1);
query.prepare("insert into date (id, date) values (?,?)");
int i = 0;
QVariantList ids;
QStringList dateStrings;
for (i=0; i<3000; ++i) {
ids<<i;
date = date.addDays(1);
dateStrings<<date.toString("yyyy-MM-dd");
}
query.addBindValue(ids);
query.addBindValue(dateStrings);
if (!query.execBatch())
qDebug() << query.lastError();
query.exec("pragma synchronous=2");
qDebug()<<query.lastError();
query.exec("create table person_date (id int primary key, person_id integer, date_id integer, foreign key(person_id) references people(id), foreign key(date_id) references date(id))");
qDebug() << query.lastError();
personModel = new QSqlTableModel(0, db);
setPersonModel();
signInModel = new SignInModel(0);
setSignInModel(QDate::currentDate(), db);
query.exec("create table contact (id int primary key, person_id integer, method varchar(64), value varchar(128), foreign key(person_id) references people(id))");
qDebug() << query.lastError();
return true;
}
