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;
}
}
