void sql_intro_snippets()
{
{
//! [26]
QSqlDatabase db = QSqlDatabase::addDatabase("QMYSQL");
db.setHostName("bigblue");
db.setDatabaseName("flightdb");
db.setUserName("acarlson");
db.setPassword("1uTbSbAs");
bool ok = db.open();
//! [26]
Q_UNUSED(ok);
}
{
//! [27]
QSqlDatabase firstDB = QSqlDatabase::addDatabase("QMYSQL", "first");
QSqlDatabase secondDB = QSqlDatabase::addDatabase("QMYSQL", "second");
//! [27]
}
{
//! [28]
QSqlDatabase defaultDB = QSqlDatabase::database();
//! [28] //! [29]
QSqlDatabase firstDB = QSqlDatabase::database("first");
//! [29] //! [30]
QSqlDatabase secondDB = QSqlDatabase::database("second");
//! [30]
}
{
// SELECT1
//! [31]
QSqlQuery query;
query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
//! [31]
//! [32]
while (query.next()) {
QString name = query.value(0).toString();
int salary = query.value(1).toInt();
qDebug() << name << salary;
}
//! [32]
}
{
// FEATURE
//! [33]
QSqlQuery query;
int numRows;
query.exec("SELECT name, salary FROM employee WHERE salary > 50000");
QSqlDatabase defaultDB = QSqlDatabase::database();
if (defaultDB.driver()->hasFeature(QSqlDriver::QuerySize)) {
numRows = query.size();
} else {
// this can be very slow
query.last();
numRows = query.at() + 1;
}
//! [33]
}
{
// INSERT1
//! [34]
QSqlQuery query;
query.exec("INSERT INTO employee (id, name, salary) "
"VALUES (1001, 'Thad Beaumont', 65000)");
//! [34]
}
{
// NAMED BINDING
//! [35]
QSqlQuery query;
query.prepare("INSERT INTO employee (id, name, salary) "
"VALUES (:id, :name, :salary)");
query.bindValue(":id", 1001);
query.bindValue(":name", "Thad Beaumont");
query.bindValue(":salary", 65000);
query.exec();
//! [35]
}
{
// POSITIONAL BINDING
//! [36]
QSqlQuery query;
query.prepare("INSERT INTO employee (id, name, salary) "
"VALUES (?, ?, ?)");
query.addBindValue(1001);
query.addBindValue("Thad Beaumont");
query.addBindValue(65000);
query.exec();
//! [36]
}
{
// UPDATE1
//! [37]
QSqlQuery query;
query.exec("UPDATE employee SET salary = 70000 WHERE id = 1003");
//! [37]
}
{
// DELETE1
//! [38]
QSqlQuery query;
query.exec("DELETE FROM employee WHERE id = 1007");
//! [38]
}
{
// TRANSACTION
//! [39]
QSqlDatabase::database().transaction();
QSqlQuery query;
query.exec("SELECT id FROM employee WHERE name = 'Torild Halvorsen'");
if (query.next()) {
int employeeId = query.value(0).toInt();
query.exec("INSERT INTO project (id, name, ownerid) "
"VALUES (201, 'Manhattan Project', "
+ QString::number(employeeId) + ')');
}
QSqlDatabase::database().commit();
//! [39]
}
{
// SQLQUERYMODEL1
//! [40]
QSqlQueryModel model;
model.setQuery("SELECT * FROM employee");
for (int i = 0; i < model.rowCount(); ++i) {
int id = model.record(i).value("id").toInt();
QString name = model.record(i).value("name").toString();
qDebug() << id << name;
}
//! [40]
}
{
// SQLTABLEMODEL1
//! [41]
QSqlTableModel model;
model.setTable("employee");
model.setFilter("salary > 50000");
model.setSort(2, Qt::DescendingOrder);
model.select();
for (int i = 0; i < model.rowCount(); ++i) {
QString name = model.record(i).value("name").toString();
int salary = model.record(i).value("salary").toInt();
qDebug() << name << salary;
}
//! [41]
}
{
// SQLTABLEMODEL2
QSqlTableModel model;
model.setTable("employee");
//! [42]
for (int i = 0; i < model.rowCount(); ++i) {
QSqlRecord record = model.record(i);
double salary = record.value("salary").toInt();
salary *= 1.1;
record.setValue("salary", salary);
model.setRecord(i, record);
}
model.submitAll();
//! [42]
// SQLTABLEMODEL3
int row = 1;
int column = 2;
//! [43]
model.setData(model.index(row, column), 75000);
model.submitAll();
//! [43]
// SQLTABLEMODEL4
//! [44]
model.insertRows(row, 1);
model.setData(model.index(row, 0), 1013);
model.setData(model.index(row, 1), "Peter Gordon");
model.setData(model.index(row, 2), 68500);
model.submitAll();
//! [44]
//! [45]
model.removeRows(row, 5);
//! [45] //! [46]
model.submitAll();
//! [46]
}
}
void loginFrame::readRegistryData()
{
QSqlDatabase db = QSqlDatabase::addDatabase("QSQLITE");
db.setDatabaseName("Registry.db");
if(!db.open())
return;
//从数据库中读取ip
//从数据库中读取自动登录的id，并自动登录
QSqlTableModel *model3 = new QSqlTableModel();
model3->setTable("serverip");
if(model3->select()) //如果userlist表存在
{
QSqlRecord re = model3->record(0);
QString serverip = re.value("ip").toString();
ui->lineEdit_2->setText(serverip);
}
/////////////////////////////////
//从数据库中读取自动登录的id，并自动登录
QSqlTableModel *model2 = new QSqlTableModel();
model2->setTable("autologin");
QString autoid;
if(!model2->select()) //如果userlist表不存在，则返回
autoid = "0";
QSqlRecord re = model2->record(0);
autoid = re.value("id").toString();
QString autoPasw = " ";
int autoIndex = -1;
// QTableView *view = new QTableView(this);
// view->setModel(model2);
// view->show();
//从数据库中读取账号信息
QSqlTableModel *model = new QSqlTableModel();
model->setTable("userlist");
if(!model->select()) //如果userlist表不存在，则返回
return;
// QTableView *view2 = new QTableView(this);
// view2->setModel(model);
// view2->show();
int count = 0;
for(int i=0;i<model->rowCount();i++)
{
QSqlRecord rec = model->record(i);
QString id = rec.value("id").toString();
QString username = QString::fromLocal8Bit(rec.value("username").toByteArray());
QString password = QString::fromLocal8Bit(rec.value("password").toByteArray());
UserInfo user;
user.id = id;
user.password = password;
user.username = username;
RegistyShowID.append(user);
count++;
ui->comboBox->insertItem(count,QIcon(":/images/head.png"),id);
if(id == autoid) //找出自动登录id的index
{
autoIndex = i;
autoPasw = password;
}
}
if(autoid != " " && autoIndex >=0) //若为" "，则没有自动登录的id
{
ui->comboBox->setCurrentIndex(autoIndex);
if(autoPasw != " ")
ui->lineEdit_Password->setText(autoPasw);
this->isauto = true; //自动登录
}
}
void EditColumnFormatsDialog::editItem(EditorType type) {
if (type == EditItem && selectionIsEmpty()) {
selectionIsEmptyWarning();
return;
}
QModelIndex index = listView->currentIndex();
QSqlRecord record = model_->record(index.row());
int id = record.value(ColumnFormat_Id).toInt();
QSqlQuery query(QString("SELECT Intervals.Name FROM ColumnFormats "
"JOIN ColumnFormatInterval USING (ColumnFormatID) "
"JOIN Intervals USING (IntervalID) "
"WHERE ColumnFormatInterval.ColumnFormatID = %1 "
"ORDER BY SortOrder").arg(id));
if (!query.isActive()) {
QMessageBox::warning(this, tr("Database Error"),
query.lastError().text());
return;
}
QList<EpCore::Interval> intervals;
while (query.next()) {
QString value = query.value(0).toString();
intervals.append(value);
}
EditColumnFormatDialog d(type, this);
if (type == EditItem) {
QString columnFormatName = record.value(ColumnFormat_Name).toString();
EpCore::ColumnFormat columnFormat(columnFormatName, intervals);
d.setColumnFormat(columnFormat);
}
else if (type == NewItem) {
EpCore::ColumnFormat columnFormat;
d.setColumnFormat(columnFormat);
}
if (d.exec() == QDialog::Accepted) {
EpCore::ColumnFormat columnFormat = d.columnFormat();
if (type == EditItem)
removeIntervals();
int row = index.row();
if (type == NewItem)
model_->insertRows(row, 1);
model_->setData(model_->index(row, ColumnFormat_Name),
columnFormat.name());
model_->submitAll();
model_->select();
QSqlTableModel updateModel;
updateModel.setTable("ColumnFormats");
updateModel.setFilter(QString("Name = '%1'").arg(columnFormat.name()));
updateModel.select();
int newId = 0;
QString newName;
if (updateModel.rowCount() >= 1) {
QSqlRecord record = updateModel.record(0);
newId = record.value(0).toInt();
newName = record.value(1).toString();
}
QList<EpCore::Interval> intervals = columnFormat.intervals();
QListIterator<EpCore::Interval> iter(intervals);
int i = 0;
QSqlDatabase::database().transaction();
while (iter.hasNext()) {
QSqlQuery lookup(QString("SELECT IntervalID FROM Intervals "
"WHERE Name = '%1'")
.arg(iter.next().name()));
int lookupId;
while (lookup.next()) {
lookupId = lookup.value(0).toInt();
QSqlQuery insertQuery(QString("INSERT INTO "
"ColumnFormatInterval "
"(ColumnFormatID, "
"IntervalID, SortOrder) "
"VALUES (%1, %2, %3)")
.arg(newId).arg(lookupId).arg(i++));
}
}
QSqlDatabase::database().commit();
}
}
int IDResource::getSingleIdRes(QString& idType)
{
QSqlTableModel model;
model.setTable(idType);
QString filter1="";
int minNum,maxNum;
if (idType==tr("ACLID"))
{
filter1=QString("ID>=%1 and ID<=%2").arg(g_minACLID).arg(g_maxACLID);
minNum=g_minACLID;
maxNum=g_maxACLID;
}
else if (idType==tr("XACLID"))
{
filter1=QString("ID>=%1 and ID<=%2").arg(g_minXACLID).arg(g_maxXACLID);
minNum=g_minXACLID;
maxNum=g_maxXACLID;
}
else if (idType==tr("PLID"))
{
filter1=QString("ID>=%1 and ID<=%2").arg(g_minPLID).arg(g_maxPLID);
minNum=g_minPLID;
maxNum=g_maxPLID;
}
model.setFilter(filter1);
model.setSort( 0, Qt::AscendingOrder);
model.select();
QSet<int>idSet;
for(int j=0;j<model.rowCount();++j)
{
QSqlRecord record=model.record(j);
idSet<<record.value("id").toInt();
}
QQueue<int>queueId;
for(int j=minNum;j!=maxNum+1;++j)
{
if(!idSet.contains(j))
{
queueId.enqueue(j);
}
}
idSet.clear();
if(queueId.count()<=0)
{
QMessageBox::information(this, tr("information"), tr("无可用ID资源"));
}
while(true)
{
int resId=queueId.dequeue();
if(!idConflict(resId,idType))
return resId;
}
return 0;
}
void estore::on_confirm_clicked()
{
int num = ui->num->text().toInt();
QSqlTableModel model;
model.setTable("commodity");
model.setFilter(tr("number = '%1'").arg(num));
model.select();
if(model.rowCount()==1)//查询到有一个结果
{
QString name =model.record(0).value("name").toString();
QString description =model.record(0).value("description").toString();
double primeprice = model.record(0).value("primeprice").toDouble();
double saleprice = model.record(0).value("saleprice").toDouble();
int column = model.record(0).value("column").toInt();
int sum = model.record(0).value("sum").toInt();
double discount = model.record(0).value("discount").toDouble();
QString publishmentbrand =model.record(0).value("publishmentbrand").toString();
QString ISBNmaterialcolumn =model.record(0).value("ISBNmaterialcolumn").toString();
QString date =model.record(0).value("date").toString();
QString pagenumbersizeweight =model.record(0).value("pagenumbersizeweight").toString();
QString si = ui->sum->text();
int suminput= si.toInt();
int numflag1=1;
for(int i=0;i<si.size();i++)
if ((si[i]<'0')||(si[i]>'9'))
{
numflag1=0;
break;
}
if(si.size()==0)
{
QMessageBox::critical(NULL, "错误","数量未填写！");
}
else if(numflag1==0)
{
QMessageBox::critical(NULL, "错误","数量填写格式错误！");
}
else if(suminput > sum )
{
QMessageBox::warning(NULL, tr("错误"), tr("输入商品数量大于库存！"));
}
else
{
confirm c(name, suminput);
if(c.exec()==QDialog::Accepted)
{
if (addTrolley(num, name, description, primeprice, saleprice,column, suminput, discount, publishmentbrand, ISBNmaterialcolumn, date, pagenumbersizeweight))
{
model.setData(model.index(0,6),sum-suminput);
model.submitAll();
QMessageBox::information(NULL, tr("成功"), tr("商品成功添加至购物车！"));
accept();
}
}
}
}else
{
QMessageBox::warning(NULL, tr("错误"), tr("未查询到商品！"));
}
}
bool DocumentSaverDB::saveDocument(KraftDoc *doc )
{
bool result = false;
if( ! doc ) return result;
QSqlTableModel model;
model.setTable("document");
QSqlRecord record;
kDebug() << "############### Document Save ################" << endl;
if( doc->isNew() ) {
record = model.record();
} else {
model.setFilter("docID=" + doc->docID().toString());
model.select();
if ( model.rowCount() > 0 ) {
record = model.record(0);
} else {
kError() << "Could not select document record" << endl;
return result;
}
// The document was already saved.
}
if( !doc->isNew() && doc->docTypeChanged() && doc->newIdent() ) {
// an existing doc has a new document type. Fix the doc number cycle and pick a new ident
DocType dt( doc->docType() );
QString ident = dt.generateDocumentIdent( doc );
doc->setIdent( ident );
}
fillDocumentBuffer( record, doc );
if( doc->isNew() ) {
kDebug() << "Doc is new, inserting" << endl;
if( !model.insertRecord(-1, record)) {
QSqlError err = model.lastError();
kDebug() << "################# SQL Error: " << err.text();
}
model.submitAll();
dbID id = KraftDB::self()->getLastInsertID();
doc->setDocID( id );
// get the uniq id and write it into the db
DocType dt( doc->docType() );
QString ident = dt.generateDocumentIdent( doc );
doc->setIdent( ident );
model.setFilter("docID=" + id.toString());
model.select();
if ( model.rowCount() > 0 ) {
model.setData(model.index(0, 1), ident);
model.submitAll();
}
} else {
kDebug() << "Doc is not new, updating #" << doc->docID().intID() << endl;
record.setValue( "docID", doc->docID().toString() );
model.setRecord(0, record);
model.submitAll();
}
saveDocumentPositions( doc );
kDebug() << "Saved document no " << doc->docID().toString() << endl;
return result;
}
void DocumentSaverDB::saveDocumentPositions( KraftDoc *doc )
{
DocPositionList posList = doc->positions();
// invert all pos numbers to avoid a unique violation
// FIXME: We need non-numeric ids
QSqlQuery upq;
QString queryStr = "UPDATE docposition SET ordNumber = -1 * ordNumber WHERE docID=";
queryStr += doc->docID().toString();
queryStr += " AND ordNumber > 0";
upq.prepare( queryStr );
upq.exec();
int ordNumber = 1;
QSqlTableModel model;
model.setTable("docposition");
model.setEditStrategy(QSqlTableModel::OnManualSubmit);
QVector<int> deleteIds;
DocPositionListIterator it( posList );
while( it.hasNext() ) {
DocPositionBase *dpb = it.next();
DocPosition *dp = static_cast<DocPosition*>(dpb);
QSqlRecord record ;
bool doInsert = true;
int posDbID = dp->dbId().toInt();
kDebug() << "Saving Position DB-Id: " << posDbID << endl;
if( dp->toDelete() ) {
kDebug() << "Delete item " << dp->dbId().toString() << endl;
// store the id to delete, rather than killing the model index.
// did that before here, which removed wrong items.
deleteIds << posDbID;
// delete all existing attributes no, which will not disturb the model index
dp->attributes().dbDeleteAll( dp->dbId() );
continue;
}
if( posDbID > -1 ) {
const QString selStr = QString("docID=%1 AND positionID=%2").arg( doc->docID().toInt() ).arg( posDbID );
// kDebug() << "Selecting with " << selStr << endl;
model.setFilter( selStr );
model.select();
if ( model.rowCount() > 0 ) {
if( ! dp->toDelete() )
record = model.record(0);
doInsert = false;
} else {
kError() << "ERR: Could not select document position record" << endl;
return;
}
} else {
// The record is new
record = model.record();
}
if( record.count() > 0 ) {
// kDebug() << "Updating position " << dp->position() << " is " << dp->text() << endl;
QString typeStr = PosTypePosition;
double price = dp->unitPrice().toDouble();
if ( dp->type() == DocPositionBase::ExtraDiscount ) {
typeStr = PosTypeExtraDiscount;
}
record.setValue( "docID", QVariant(doc->docID().toInt()));
record.setValue( "ordNumber", QVariant(ordNumber));
record.setValue( "text", QVariant(dp->text()));
record.setValue( "postype", QVariant(typeStr));
record.setValue( "amount", QVariant(dp->amount()));
int unitId = dp->unit().id();
record.setValue( "unit", QVariant(unitId));
record.setValue( "price", QVariant(price));
record.setValue( "taxType", QVariant(dp->taxType()));
ordNumber++; // FIXME
if( doInsert ) {
kDebug() << "Inserting!" << endl;
model.insertRecord(-1, record);
model.submitAll();
dp->setDbId( KraftDB::self()->getLastInsertID().toInt() );
} else {
kDebug() << "Updating!" << endl;
model.setRecord(0, record);
model.submitAll();
}
} else {
kDebug() << "ERR: No record object found!" << endl;
}
dp->attributes().save( dp->dbId() );
QSqlError err = model.lastError();
if( err.type() != QSqlError::NoError ) {
kDebug() << "SQL-ERR: " << err.text() << " in " << model.tableName() << endl;
}
}
model.submitAll();
/* remove the docpositions that were marked to be deleted */
if( deleteIds.count() ) {
QSqlQuery delQuery;
delQuery.prepare( "DELETE FROM docposition WHERE positionID=:id" );
foreach( int id, deleteIds ) {
kDebug() << "Deleting attribute id " << id;
delQuery.bindValue( ":id", id );
delQuery.exec();
}
void event::on_confirm_clicked()
{
QString discount = ui->discount->text();
int numflag1=1,a=1;
for(int i=0;i<discount.size();i++)
if ((discount[i]<'0')||(discount[i]>'9'))
{
numflag1=0;
break;
}
if(numflag1==0)
{
QMessageBox::critical(NULL, "警告","折扣输入格式有误！");
a=0;
}
float dis = discount.toFloat();
if(dis<1||dis>100)
{
QMessageBox::critical(NULL, "警告","折扣输入格式有误！");
a=0;
}
float d =1-dis/100;
if(ui->book->isChecked())
{
QSqlTableModel model;
model.setTable("commodity");
model.setFilter(QObject::tr("column = 1 ")); //根据类别进行筛选
model.select(); //显示结果
for (int i = 0; i < model.rowCount(); i++)
{
qDebug()<<model.record(i);
qDebug()<<d;
model.setData(model.index(i,7),d);
double p= model.record(i).value("primeprice").toFloat();
double a = d*p;
int yy = (int)(a*100+1.0e-6);
double b = (double)yy/100;
model.setData(model.index(i,4),b);
model.submitAll();
qDebug()<<model.record(i);
}
if(a==1)
{
QMessageBox::information(NULL, "成功","信息更改成功！");
accept();
}
}
else if(ui->clothes->isChecked())
{
QSqlTableModel model;
model.setTable("commodity");
model.setFilter(QObject::tr("column = 2 ")); //根据类别进行筛选
model.select(); //显示结果
for (int i = 0; i < model.rowCount(); i++)
{
qDebug()<<model.record(i);
qDebug()<<d;
model.setData(model.index(i,7),d);
double p= model.record(i).value("primeprice").toFloat();
double a = d*p;
int yy = (int)(a*100+1.0e-6);
double b = (double)yy/100;
model.setData(model.index(i,4),b);
model.submitAll();
qDebug()<<model.record(i);
}
if(a==1)
{
QMessageBox::information(NULL, "成功","信息更改成功！");
accept();
}
}
else if(ui->food->isChecked())
{
QSqlTableModel model;
model.setTable("commodity");
model.setFilter(QObject::tr("column = 3 ")); //根据类别进行筛选
model.select(); //显示结果
qDebug()<<model.rowCount();
for (int i = 0; i < model.rowCount(); i++)
{
qDebug()<<model.record(i);
qDebug()<<d;
model.setData(model.index(i,7),d);
double p= model.record(i).value("primeprice").toFloat();
double a = d*p;
int yy = (int)(a*100+1.0e-6);
double b = (double)yy/100;
model.setData(model.index(i,4),b);
model.submitAll();
qDebug()<<model.record(i);
}
if(a==1)
{
QMessageBox::information(NULL, "成功","信息更改成功！");
accept();
}
}
else if(ui->electronic->isChecked())
{
QSqlTableModel model;
model.setTable("commodity");
model.setFilter(QObject::tr("column = 4 ")); //根据类别进行筛选
model.select(); //显示结果
for (int i = 0; i < model.rowCount(); i++)
{
qDebug()<<model.record(i);
qDebug()<<d;
model.setData(model.index(i,7),d);
double p= model.record(i).value("primeprice").toFloat();
double a = d*p;
int yy = (int)(a*100+1.0e-6);
double b = (double)yy/100;
model.setData(model.index(i,4),b);
model.submitAll();
qDebug()<<model.record(i);
}
if(a==1)
{
QMessageBox::information(NULL, "成功","信息更改成功！");
accept();
}
}
else
{
QMessageBox::critical(NULL, "警告","请选择商品！");
}
}
void ClinicChargeStatisticForm::updateTable()
{
initTable();
QDate startDate = m_startDateEdit->date();
QDate endDate = m_endDateEdit->date();
if(startDate > endDate)
return;
QString strName = m_nameEdit->text();
Gender eGender = (Gender)m_genderComboBox->currentIndex();
if(myDB::connectDB())
{
QSqlTableModel *model = new QSqlTableModel;
model->setTable(strClinicCharge);
QString strSql = "" , strTemp = "";
strTemp = "Gender = " + QString::number((int)eGender);
strSql += strTemp;
if(!strName.isEmpty())
{
strTemp = " and Name = \'" + strName + "\'";
strSql += strTemp;
}
QString strStartTime = startDate.toString("yyyy-MM-dd") + "T00:00:00";
QString strEndTime = endDate.toString("yyyy-MM-dd") + "T23:59:59";
strTemp = " and Time between \'" + strStartTime + "\' and \'" + strEndTime + "\'";
strSql += strTemp;
model->setFilter(strSql);
model->select();
int index = 0;
for(int i = 0; i < model->rowCount();i++)
{
QSqlRecord record = model->record(i);
QString strID = record.value("ID").toString();
QSqlTableModel *mymodel = new QSqlTableModel;
mymodel->setTable(strClinicChargeDetails);
mymodel->setFilter("ChargeId = \'" + strID + "\'");
mymodel->select();
for(int j = 0; j < mymodel->rowCount(); j++)
{
m_resultsModel->setItem(index,0,new QStandardItem(strID));
m_resultsModel->setItem(index,1,new QStandardItem(record.value("Name").toString()));
m_resultsModel->setItem(index,2,new QStandardItem(record.value("Department").toString()));
m_resultsModel->setItem(index,3,new QStandardItem(record.value("Doctor").toString()));
QSqlRecord record1 = mymodel->record(j);
m_resultsModel->setItem(index,4,new QStandardItem(record1.value("ChargeItemName").toString()));
m_resultsModel->setItem(index,5,new QStandardItem(record1.value("ChargeItemCount").toString()));
m_resultsModel->setItem(index,6,new QStandardItem(record1.value("ChargeItemPrice").toString()));
m_resultsModel->setItem(index,7,new QStandardItem(record.value("Time").toString()));
index++;
}
}
}
}