Movie Database::getMovie(QString title) { QSqlQuery getMovieQuery; Movie temp; getMovieQuery.prepare("SELECT * FROM movieDB where originalTitle LIKE :title ;"); getMovieQuery.bindValue(":title", "%"+title+"%"); getMovieQuery.exec(); getMovieQuery.last(); if(getMovieQuery.at()+1 == 1) { temp.setTMDB_ID(getMovieQuery.value(0).toInt()); temp.setYear(getMovieQuery.value(3).toInt()); temp.setOriginalTitle(getMovieQuery.value(2).toString()); temp.setTitle(getMovieQuery.value(1).toString()); temp.setRuntime(getMovieQuery.value(4).toInt()); temp.setSynopsis(getMovieQuery.value(5).toString()); }else if(getMovieQuery.at()+1 == -1) { getMovieQuery.clear(); getMovieQuery.prepare("SELECT * FROM movieDB where Title LIKE :title ;"); getMovieQuery.bindValue(":title", "%"+title+"%"); getMovieQuery.exec(); getMovieQuery.last(); if(getMovieQuery.at()+1 == 1) { temp.setTMDB_ID(getMovieQuery.value(0).toInt()); temp.setYear(getMovieQuery.value(3).toInt()); temp.setOriginalTitle(getMovieQuery.value(2).toString()); temp.setTitle(getMovieQuery.value(1).toString()); temp.setRuntime(getMovieQuery.value(4).toInt()); temp.setSynopsis(getMovieQuery.value(5).toString()); } } return temp; }
bool DatabaseMgrSQLite::insertProduct(const QString &aqstrProduct, const QString &aqstrCategory) { if(m_QSqlDatabase.open()) { QString qry; QSqlQuery tempQuery; //Check if the Product defined via the argument is already in the table QString tqry = QString("SELECT ProductID FROM Product WHERE ProductName = '%1'").arg(aqstrProduct); tempQuery.exec(tqry); if(tempQuery.last() == '\0') { //Product not found and should be inserted qry = QString("INSERT INTO Product(ProductName, CategoryID) " "SELECT '%1', CategoryID FROM Category WHERE CategoryName = '%2'").arg(aqstrProduct).arg(aqstrCategory); QSqlQuery query(qry); } else { qDebug() << QString("Product %1 already in the database").arg(aqstrProduct); } return true; } else { std::cout << "Error opening: " << lastError().text().toStdString() << std::endl; return false; } }
//------------------------------------------------------------------------------------------ bool DatabaseMgrSQLite::insertCategory(const QString &aqstrCategory, const QString &aqstrObjectType) { if(m_QSqlDatabase.open()) { QString qry; QSqlQuery tempQuery; //Check if the Category defined via the argument is already in the table QString tqry = QString("SELECT CategoryID FROM Category WHERE CategoryName = '%1'").arg(aqstrCategory); tempQuery.exec(tqry); if(tempQuery.last() == '\0') { //Category not found and should be inserted qry = QString("INSERT INTO Category(CategoryName, ObjectTypeID) " "SELECT '%1', ObjectTypeID FROM ObjectType WHERE ObjectTypeName = '%2'").arg(aqstrCategory).arg(aqstrObjectType); QSqlQuery query(qry); } else { qDebug() << QString("Category %1 already in the database.").arg(aqstrCategory); } return true; } else { std::cout << "Error opening: " << lastError().text().toStdString() << std::endl; return false; } }
void VRShopEditorDlg::updateProduct(const QString& aqstrCategoryName) { //Clear any content of the ComboBox Product m_pComboBoxProductPose->clear(); m_pComboBoxProductCodePose->clear(); QSqlQuery tQuery; //Check if the Category defined via the argument has any element QString tqry = QString("SELECT Product.ProductName FROM Product " "JOIN Category " "ON Product.CategoryID=Category.CategoryID " "AND Category.CategoryName = '%1'").arg(aqstrCategoryName); tQuery.exec(tqry); //See if category includes any item if(tQuery.last() == '\0') { std::cout << "Product is empty." << std::endl; m_pComboBoxProductPose->addItem("<empty>"); } else { //Not empty, navigate through results std::cout << "Product is not empty." << std::endl; QStringList items; QString item; if (tQuery.exec(tqry)) { //next() advances through results until the last item while(tQuery.next()) { items.push_back(tQuery.value(0).toString()); } //Populate ComboBox with adequate data m_pComboBoxProductPose->addItems(items); } } }
void VRShopEditorDlg::updateCategory() { QSqlQuery tQuery; //Check if the Scene defined via the argument is already in the table QString tqry = QString("SELECT CategoryName FROM Category"); tQuery.exec(tqry); //See if category includes any item if(tQuery.last() == '\0') { std::cout << "Category is empty." << std::endl; m_pComboBoxCategoryPose->addItem("<empty>"); } else { //Not empty, navigate through results std::cout << "Category is not empty." << std::endl; QStringList items; QString item; if (tQuery.exec(tqry)) { //next() advances through results until the last item while(tQuery.next()) { items.push_back(tQuery.value(0).toString()); } //Populate ComboBox with adequate data m_pComboBoxCategoryPose->addItems(items); } } }
//--------------------------------------------------------------------------------------------- bool DatabaseMgrSQLite::insertProductCodeImage(const QString &aqstrProductCodeNumber, const QString &aqstrProductCodeImage) { if(m_QSqlDatabase.open()) { QString qry; QSqlQuery tempQuery; //Check if the Product code image defined via the argument is already in the table QString tqry = QString("SELECT ProductCodeImageID FROM ProductCodeImage WHERE ProductCodeImageName = '%1'").arg(aqstrProductCodeImage); tempQuery.exec(tqry); if(tempQuery.last() == '\0') { //Product not found and should be inserted qry = QString("INSERT INTO ProductCodeImage(ProductCodeImageName, ProductCodeID) " "SELECT '%1', ProductCodeID FROM ProductCode WHERE ProductCodeNumber = '%2'").arg(aqstrProductCodeImage).arg(aqstrProductCodeNumber); QSqlQuery query(qry); } else { //Product code Image already in the database. qDebug() << QString("Product Code Image %1 already in the database.").arg(aqstrProductCodeImage); } return true; } else { std::cout << "Error opening: " << lastError().text().toStdString() << std::endl; return false; } }
vector<CategoryStats> DatabaseHelper::getYearStats(SortOrder sortOrder) { QSqlQuery query; query.exec("SELECT DISTINCT year FROM pl_paper"); vector<int> years; while (query.next()) { int year = query.value(0).toInt(); years.push_back(year); } vector<CategoryStats> stats(years.size()); for (vector<CategoryStats>::size_type i = 0; i < stats.size(); ++i) { query.prepare("SELECT COUNT(*) FROM pl_paper WHERE year = :year"); query.bindValue(":year", years[i]); query.exec(); query.last(); stats[i].setName(to_string(static_cast<long long>(years[i]))); stats[i].setCount(query.value(0).toInt()); } sortStats(stats, sortOrder); return stats; }
void LogEngine::checkDBSize() { QString queryString = "SELECT ROWID FROM entries;"; QSqlQuery query; query.exec(queryString); int numRows = 0; if (m_db.driver()->hasFeature(QSqlDriver::QuerySize)) { numRows = query.size(); } else { // this can be very slow query.last(); numRows = query.at() + 1; } if (numRows >= m_dbMaxSize) { // keep only the latest m_dbMaxSize entries qCDebug(dcLogEngine) << "Deleting oldest entries and keep only the latest" << m_dbMaxSize << "entries."; QString queryDeleteString = QString("DELETE FROM entries WHERE ROWID IN (SELECT ROWID FROM entries ORDER BY timestamp DESC LIMIT -1 OFFSET %1);").arg(QString::number(m_dbMaxSize)); if (!query.exec(queryDeleteString)) { qCWarning(dcLogEngine) << "Error deleting oldest log entries to keep size. Driver error:" << query.lastError().driverText() << "Database error:" << query.lastError().databaseText(); } else { emit logDatabaseUpdated(); } } }
void VRShopEditorDlg::addImage2Widget(const QString& aqstrProductCode) { QSqlQuery tQuery; //Check if the Category defined via the argument has any element QString tqry = QString("SELECT ProductCodeImage.ProductCodeImageName FROM ProductCodeImage " "JOIN ProductCode " "ON ProductCodeImage.ProductCodeID=ProductCode.ProductCodeID " "AND ProductCode.ProductCodeNumber = '%1'").arg(aqstrProductCode); tQuery.exec(tqry); //See if category includes any item if(tQuery.last() == '\0') { std::cout << "Product image is empty." << std::endl; } else { //Not empty, navigate through results std::cout << "Product image is not empty." << std::endl; QString name; if (tQuery.exec(tqry)) { //next() advances through results until the last item while(tQuery.next()) { name = tQuery.value(0).toString(); } FunctorPathResource f; QImageReader image(f("/Images/").c_str()+ name); m_pImageViewerChoosingPose->setPixmap(QPixmap::fromImageReader(&image)); } } }
vector<CategoryStats> DatabaseHelper::getAuthorStats(SortOrder sortOrder) { QSqlQuery query; query.exec("SELECT * FROM pl_author"); vector<int> authorsId; vector<string> authorsName; while (query.next()) { int authorId = query.value(0).toInt(); string authorName = query.value(1).toString().toStdString(); authorsId.push_back(authorId); authorsName.push_back(authorName); } vector<CategoryStats> stats(authorsId.size()); for (vector<CategoryStats>::size_type i = 0; i < stats.size(); ++i) { query.prepare("SELECT COUNT(*) FROM pl_paper2author WHERE author_id = :author_id"); query.bindValue(":author_id", authorsId[i]); query.exec(); query.last(); stats[i].setName(authorsName[i]); stats[i].setCount(query.value(0).toInt()); } sortStats(stats, sortOrder); return stats; }
void MainWindow::readAd(int adId)//读取广告 { QSqlQuery adQuery;//对广告表单搜索 if(adQuery.exec("SELECT aditem FROM adtable WHERE id = "+QString().setNum(adId))) { int adNumRows = 0;//定义搜索返回的行数 if(db.driver()->hasFeature(QSqlDriver::QuerySize))//判断驱动是否包含QuerySize { adNumRows = adQuery.size();//将返回的行数赋于fstNumRows } else { adQuery.last();//移至最后一行 adNumRows = adQuery.at()+1;//将返回的行数赋于fstNumRows adQuery.seek(-1);//返回第一行 } if(adNumRows>0)//判断是否大于0 { while(adQuery.next()) { this->adView->loadMap(adQuery.value(0).toByteArray());//读取广告 } } } }
void VRShopEditorDlg::addTexture2Widget(const QString& aqstrTextureName) { QSqlQuery tQuery; //Check if the Category defined via the argument has any element QString tqry = QString("SELECT Texture.TextureName FROM Texture " "WHERE Texture.TextureName = '%1'").arg(aqstrTextureName); tQuery.exec(tqry); //See if category includes any item if(tQuery.last() == '\0') { std::cout << "Texture is empty." << std::endl; } else { //Not empty, navigate through results std::cout << "Texture is not empty." << std::endl; QString name; if (tQuery.exec(tqry)) { //next() advances through results until the last item while(tQuery.next()) { name = tQuery.value(0).toString(); } FunctorPathResource f; QImageReader image(f("/Textures/").c_str()+ name); m_pImageViewerTextureName->setPixmap(QPixmap::fromImageReader(&image)); } } }
vector<CategoryStats> DatabaseHelper::getTagStats(SortOrder sortOrder) { QSqlQuery query; query.exec("SELECT * FROM pl_tag"); vector<int> tagsId; vector<string> tagsName; while (query.next()) { int tagId = query.value(0).toInt(); string tagName = query.value(1).toString().toStdString(); tagsId.push_back(tagId); tagsName.push_back(tagName); } vector<CategoryStats> stats(tagsId.size()); for (vector<CategoryStats>::size_type i = 0; i < stats.size(); ++i) { query.prepare("SELECT COUNT(*) FROM pl_paper2tag WHERE tag_id = :tag_id"); query.bindValue(":tag_id", tagsId[i]); query.exec(); query.last(); stats[i].setName(tagsName[i]); stats[i].setCount(query.value(0).toInt()); } sortStats(stats, sortOrder); return stats; }
vector<CategoryStats> DatabaseHelper::getBookTitleStats(SortOrder sortOrder) { QSqlQuery query; query.exec("SELECT * FROM pl_book_title"); vector<int> bookTitlesId; vector<string> bookTitlesName; while (query.next()) { int bookTitleId = query.value(0).toInt(); string bookTitleName = query.value(1).toString().toStdString(); bookTitlesId.push_back(bookTitleId); bookTitlesName.push_back(bookTitleName); } vector<CategoryStats> stats(bookTitlesId.size()); for (vector<CategoryStats>::size_type i = 0; i < stats.size(); ++i) { query.prepare("SELECT COUNT(*) FROM pl_paper WHERE book_title_id = :book_title_id"); query.bindValue(":book_title_id", bookTitlesId[i]); query.exec(); query.last(); stats[i].setName(bookTitlesName[i]); stats[i].setCount(query.value(0).toInt()); } sortStats(stats, sortOrder); return stats; }
//returns all info associated with the record of specified id HistoryItem DbManager::getHistoryItem(const int& id) const { QSqlQuery query; query.prepare("SELECT * FROM History WHERE id = :id"); query.bindValue(":id", id); if (!query.exec()) { qCritical() << "The getHistoryItemStats select statement couldn't be executed"; } qDebug() << query.size(); HistoryItem historyItem; if (query.next()) { historyItem.id = query.value(query.record().indexOf("id")).toInt(); historyItem.fileName = query.value(query.record().indexOf("fileName")).toString(); //Get the data from the query historyItem.dateTime = query.value(query.record().indexOf("dateTime")).toString(); historyItem.compressionRatio = query.value(query.record().indexOf("compressionRatio")).toDouble(); historyItem.originalSize = query.value(query.record().indexOf("originalSize")).toDouble(); historyItem.compressedSize = query.value(query.record().indexOf("compressedSize")).toDouble(); historyItem.executionTime = query.value(query.record().indexOf("executionTime")).toDouble(); historyItem.notes = query.value(query.record().indexOf("notes")).toString(); } //checking that there was indeed only one query returned. BTW, query.size() always returns 0 query.last(); Q_ASSERT( query.at() + 1 == 1); //how can there be more than one record associated with the unique id? return historyItem; }
int DatabaseHelper::getTagId(const string& tag) { QSqlQuery query; query.prepare("SELECT tag_id FROM pl_tag WHERE tag_name = :tag_name"); query.bindValue(":tag_name", tag.c_str()); query.exec(); if (!query.last()) return -1; return query.value(0).toInt(0); }
int DatabaseHelper::getBookTitleId(const string& bookTitle) { QSqlQuery query; query.prepare("SELECT book_title_id FROM pl_book_title WHERE book_title_name = :book_title_name"); query.bindValue(":book_title_name", bookTitle.c_str()); query.exec(); if (!query.last()) return -1; return query.value(0).toInt(); }
string DatabaseHelper::getTag(int tagId) { QSqlQuery query; query.prepare("SELECT tag_name FROM pl_tag WHERE tag_id = :tag_id"); query.bindValue(":tag_id", tagId); query.exec(); if (!query.last()) return string(); return query.value(0).toString().toStdString(); }
string DatabaseHelper::getAuthor(int authorId) { QSqlQuery query; query.prepare("SELECT author_name FROM pl_author WHERE author_id = :author_id"); query.bindValue(":author_id", authorId); query.exec(); if (!query.last()) return string(); return query.value(0).toString().toStdString(); }
string DatabaseHelper::getBookTitle(int bookTitleId) { QSqlQuery query; query.prepare("SELECT book_title_name FROM pl_book_title WHERE book_title_id = :book_title_id"); query.bindValue(":book_title_id", bookTitleId); query.exec(); if (!query.last()) return string(); return query.value(0).toString().toStdString(); }
int DatabaseHelper::getAuthorId(const string& author) { QSqlQuery query; query.prepare("SELECT author_id FROM pl_author WHERE author_name = :author_name"); query.bindValue(":author_name", author.c_str()); query.exec(); if (!query.last()) return -1; return query.value(0).toInt(); }
bool AddPatient(Patient p) { QSqlQuery query; query.exec("select * from TPatient"); query.last(); int id=query.value(0).toInt()+1; QString sql="insert into TPatient values(" +QString::number(id)+","+"'" +p.getNom()+"' , '" +p.getPrenom() +"' , '" +p.getAdresse() +"' , '" +p.getVille()+"' , '" +QString::number(p.getCP())+"' , '" +p.getCommentaire()+"' , '" +p.getTelephone()+"' , '" +p.getDateDebut().toString("yyyy-MM-dd")+"' , '" +QString::number(p.getDuree())+"' , '" +QString::number(p.getPriorite())+"')"; bool b_test=query.exec(sql); if(!b_test) { qDebug() << query.lastError().text(); qDebug() << "Insertion de données dans TPatient impossible !\n"; return false; } query.exec("select Id from TConsult"); query.last(); int idc=query.value(0).toInt()+1; for(int n=0; n<p.vIdRessource.size(); n++,idc++){ b_test=query.exec("insert into TConsult values(" +QString::number(idc)+"," +QString::number(id)+"," +QString::number(p.vIdRessource.at(n)) +")"); if(!b_test) { qDebug() << query.lastError().text(); qDebug() << "Insertion de données dans TConsult impossible !\n"; return false; } } return true; }
bool CCBO::InitConnection(QString Path) { QString QueryStr; QString systempath,systemdirectory; QStringList env = QProcess::systemEnvironment().filter(QRegExp("^XDG_DATA_HOME=")); if (env.length() == 0) { systemdirectory = QDir::homePath() + "/.local/share"; } else { systemdirectory = env.at(0); systemdirectory = systemdirectory.mid(systemdirectory.indexOf("=") + 1); if (systemdirectory.isEmpty()) { systemdirectory = QDir::homePath() + "/.local/share"; } } systemdirectory = QString("%1/chinese-calendar").arg(systemdirectory); systempath = QString("%1/KylinLunar.db").arg(systemdirectory); if (!QFile::exists(systempath)) { QDir d; if (d.mkpath(systemdirectory) == false) { return false; } if (QFile::copy("/usr/share/chinese-calendar/db/KylinLunar.db", systempath) == false) { return false; } } QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE"); db.setDatabaseName(systempath); if (!db.open()) { return false; } QSqlQuery query; query.exec("select * from lunar where MaxYear=2100"); query.last(); maxyear = query.value(0).toInt(); lunarstr = query.value(1).toByteArray(); return true; }
bool Database::exist(Movie m) { QSqlQuery existQuery; existQuery.prepare("SELECT * FROM movieDB where tmd_ID = :id;"); existQuery.bindValue(":id", m.getTmdbId()); existQuery.exec(); existQuery.last(); if(existQuery.at()+1 == 1) { return true; }else return false; }
QString NumPrefix::getPrefix(QString tableName) { QString returnString; QSettings settings("AO_Batrakov_Inc.", "Food"); QString prefName= settings.value("numprefix").toString(); if(prefName == ""){ QMessageBox::warning(this,tr("Attention!"),tr("Insert Prefix Number!!!")); returnString = ""; close(); PrefixForm prefixForm(this); prefixForm.exec(); }else{ //QString prefName = queryPref.value(0).toString(); QSqlQuery query; QString stringQuery = "SELECT "; stringQuery += tableName; stringQuery += "id FROM "; stringQuery += tableName; stringQuery += " WHERE "; stringQuery += tableName; stringQuery += "id LIKE '%"; stringQuery += prefName; stringQuery += "%' "; stringQuery += " ORDER BY "; stringQuery += tableName; stringQuery += "id"; //stringQuery.arg(prefName); query.exec(stringQuery); QString numberTemp, numberLast; query.last(); numberLast = query.value(0).toString(); if(numberLast == ""){ numberLast = "0"; } numberTemp = numberLast.right(9); int numNum = numberTemp.toInt(); ++numNum; QString nTemp = QString::number(numNum); int www; www = nTemp.count(); QString tt; for(int i = www; i < 9; ++i){ tt += "0"; } tt += QString::number(numNum); tt = prefName + tt; returnString = tt; } return returnString; }
void Execut_window::getAllCount(void) { QSqlQuery query; int temporaryA = 0; int temporaryB = 0; int k = 0; int choiceModel = 0;//选择drawerNo的位置 saveDrawer[0] = 0; if (execute_V->execute_model == 1)//入柜 { choiceModel = 17; } else if (execute_V->execute_model == 2)//还 { choiceModel = 9; } else if ((execute_V->execute_model == 4) || (execute_V->execute_model == 6))//替换 { executeInfoError(1,"请取出试剂"); choiceModel = 8; } if ((execute_V->execute_model == 2) || (execute_V->execute_model == 1) ||\ (execute_V->execute_model == 4) || (execute_V->execute_model == 6)) { query.exec(QString("SELECT * from %1 ORDER BY drawerNo ASC").arg(execute_V->T_executeTable)); query.last(); execute_V->acountRow = query.at()+1;//0 for(int i = 0; i < execute_V->acountRow; i++) { query.seek(i); temporaryA = query.value(choiceModel).toInt(); if(temporaryA != temporaryB) { temporaryB = temporaryA; k++; saveDrawer[0]=k; saveDrawer[k]=temporaryB; } } } }
bool database::authorize(QString userName, QString password) { QSqlQuery que; que.prepare(QString("SELECT `password` " "FROM users " "WHERE users.username = \"%1\"").arg(userName)); if(que.exec()){ que.last(); return (password==que.value(0).toString()); }else{ qDebug() << "Exec failed on authorize"; return false; } }
bool database::isMuted(QString username) { QSqlQuery que; que.prepare(QString("SELECT muted " "FROM users " "WHERE users.username = \"%1\"").arg(username)); if(que.exec()){ que.last(); return (que.value(0).toBool()); }else{ qDebug() << "Exec failed on isMute"; return false; } }
uint database::getComPower(QString comName) { QSqlQuery que; que.prepare(QString("SELECT permissionpower " "FROM permissions " "INNER JOIN users ON permissions.permissionid = users.permissions " "WHERE users.username = \"%1\"").arg(comName)); if(que.exec()){ que.last(); return que.value(0).toUInt(); }else{ qDebug() << "Exec failed on getPower"; return 101; } }
QString database::getColor(QString userName) { QSqlQuery que; que.prepare(QString("SELECT permissioncolor " "FROM permissions " "INNER JOIN users ON permissions.permissionid = users.permissions " "WHERE users.username = \"%1\"").arg(userName)); if(que.exec()){ que.last(); return que.value(0).toString(); }else{ qDebug() << "Exec failed on getColor"; return 0; } }