书店售书管理系统之增删查改
使用工具:qt vs2019 odbc sqlserver
实现功能:
(1)书目录入:将购进的书目按要求的指标将其录入数据库中。
(2)查询功能:实现按书籍的编号、名称等指标进行售书情况的查询。
(3)修改功能:当书目的某个或者某些指标录入错误时,能对其进行修改。
(4)删除功能:当某些书目销售错误时,应能删除错误记录,同时调整库存。
(5)显示图书信息。
(6)对用户购书、退书情况进行管理。
(7)刷新
整体布局:
将所有按键改成容易辨别的名称。(自己能分清就行)
一、添加功能
在实现添加图书的时候,我采用新的ui界面录入书的信息的形式实现的。
界面设计如下;
对于主设计界面的 添加图书 按钮的代码如下:
connect(ui.Add, &QPushButton::clicked, this, [=]() {
ADDBOOK* ad = new ADDBOOK;
ad->show();
});
添加功能的具体实现代码如下:
在ADDBOOK.cpp中:
#include "ADDBOOK.h"
#include<qsqlquery.h>//包含查询语言
#include <QDebug>
#include<QtSql/qsqlerror.h>//输出错误语言
#pragma execution_character_set("utf-8")
ADDBOOK::ADDBOOK(QWidget *parent)
: QWidget(parent)
{
ui.setupUi(this);
this->setWindowTitle("添加图书");
//加背景图
QPixmap Images("C:/Users/lenovo/Pictures/20200329175335_erzim.jpg");
QPalette Palette = this->palette();
Palette.setBrush(QPalette::Window, Images);
this->setPalette(Palette);
//OK按钮
connect(ui.OK, &QPushButton::clicked, this, [=]() {
QString number = ui.Num->text();
QString bname = ui.bookna->text();
QString writer = ui.writer->text();
QString bc = ui.bbc->text();
QString type = ui.type->text();
QString price = ui.price->text();
QString hbs = ui.hbs->text();
QString stock = ui.stock->text();
if (number.isEmpty() || bname.isEmpty() || writer.isEmpty() || bc.isEmpty() || type.isEmpty() || price.isEmpty() || hbs.isEmpty() || stock.isEmpty()) {
QMessageBox::information(this, "添加", "信息不完善,请重新录入");
return;
}
QSqlQuery query;
QString qs = QString("insert into binfor(number,bname,writer,bc,type,price,hbs,stock)values('%1','%2', '%3', '%4', '%5', '%6', '%7', '%8')")
.arg(number).arg(bname).arg(writer).arg(bc).arg(type).arg(price).arg(hbs).arg(stock);
if (query.exec(qs)) {//如果插入成功
QMessageBox::information(this, "确定", "添加成功");
}
else {
QMessageBox::information(this, "确定", "添加失败");
qDebug() << query.lastError().text();//输出错误信息
return;
}
});
connect(ui.cancel, &QPushButton::clicked, this, [=]() {
this->hide();
});
}
ADDBOOK::~ADDBOOK()
{}
二、查询功能
实现了精确查询和模糊查询两种
如果确定的想要找哪本书,可以直接搜书名找到该书;输入作者,可以找到这个作者的所有书;当用户想要某种类型的书时,可以搜索类型找到该类型的所有书籍;又或者只记得书名的大概,也可以通过模糊查询找到相关的书籍。
代码如下:
//精确查询
connect(ui.query, &QPushButton::clicked, this, [=]() {
QString bname = ui.lineEdit->text();
QString writer = ui.lineEdit->text();
QString type = ui.lineEdit->text();
model->setFilter(QString("bname = '%1' or writer = '%1' or type = '%1'").arg(bname).arg(writer).arg(type));
model->select();
ui.information->setModel(model);
});
//对书名模糊查询
connect(ui.queryhu, &QPushButton::clicked, this, [=]() {
QString bname = ui.lineEdit->text();
if (bname.length() != 0) {
model->setFilter(QString("bname like '%%1%'").arg(bname));
model->select();
ui.information->setModel(model);
}
});
//记录查询
connect(ui.query3, &QPushButton::clicked, this, [=]() {
QString namee = ui.lineEdit->text();//用户购买
QString nname = ui.lineEdit->text();//用户退货
model2->setFilter(QString("namee = '%1'").arg(namee));
model2->select();
ui.userinfo->setModel(model2);
model3->setFilter(QString("nname = '%1'").arg(namee));
model3->select();
ui.rejject->setModel(model3);
});
三、显示信息
对于显示界面,我采用将Table view 嵌套在Tab Widget中的形式实现。代码如下:
this->setPalette(Palette);
model = new QSqlTableModel(this);
model->setTable("binfor");//显示数据库中的binfor表
model->select();
model->setHeaderData(0, Qt::Horizontal, "编号");
model->setHeaderData(1, Qt::Horizontal, "书名");
model->setHeaderData(2, Qt::Horizontal, "作者");
model->setHeaderData(3, Qt::Horizontal, "出版社");
model->setHeaderData(4, Qt::Horizontal, "类型");
model->setHeaderData(5, Qt::Horizontal, "单价/元");
model->setHeaderData(6, Qt::Horizontal, "已售/本");
model->setHeaderData(7, Qt::Horizontal, "库存/本");
//model->setEditStrategy(QSqlTableModel::OnManualSubmit);
ui.information->setModel(model);
//用户购买信息
model2 = new QSqlTableModel(this);
model2->setTable("users");//显示数据库中的binfor表
model2->select();
model2->setHeaderData(0, Qt::Horizontal, "用户名");
model2->setHeaderData(1, Qt::Horizontal, "出生日期");
model2->setHeaderData(2, Qt::Horizontal, "购买图书");
model2->setHeaderData(3, Qt::Horizontal, "购买时间");
model2->setHeaderData(4, Qt::Horizontal, "联系方式");
model2->setHeaderData(5, Qt::Horizontal, "购买数量");
ui.userinfo->setModel(model2);
//退货信息
model3 = new QSqlTableModel(this);
model3->setTable("reject");//显示数据库中的binfor表
model3->select();
model3->setHeaderData(0, Qt::Horizontal, "退货书籍");
model3->setHeaderData(1, Qt::Horizontal, "退货人");
model3->setHeaderData(2, Qt::Horizontal, "退货数量");
model3->setHeaderData(3, Qt::Horizontal, "退货时间");
model3->setHeaderData(4, Qt::Horizontal, "退货原因");
model3->setHeaderData(5, Qt::Horizontal, "联系电话");
ui.rejject->setModel(model3);
四、删除
选中要删除的行,点击删除,确认,完成删除
//下架
connect(ui.Delete, &QPushButton::clicked, this, [=]() {
//书籍
int row = ui.information->currentIndex().row();
int del = QMessageBox::warning(this, tr("下架当前书籍"), tr("你确定下架该书籍吗"), QMessageBox::Yes | QMessageBox::No);
if (del == QMessageBox::Yes) {
//删除该行 提交到数据库
model->removeRow(row);
model->submitAll();
}
else {
//不删除,撤销操作
model->revertAll();
}
});
五、修改功能
修改也是直接在表格里修改
//修改
connect(ui.Edit, &QPushButton::clicked, this, [=]() {
model->database().transaction();//开始事务操作
//书籍页
if (model->submitAll()) {
model->database().commit();
QMessageBox::information(NULL, "修改成功", "修改成功!");
}
else {
model->database().rollback();
QMessageBox::warning(this, tr("数据库错误"),
tr("数据库错误!"));
}
});
六、购书、退书情况管理
当客户来购买书籍时,管理员要进行登记,但在登记之前要先检查书店是否有客户需要的书籍,有的话库存又是否足够。若都满足,才能成功售出,并将购买信息详细的登记下来。
当客户来退货时,管理员要进行登记,但在登记之前要先检查书店是否这本书,确定是否是该书店卖出的书。若是,才能退货,并将退货信息详细的登记下来
购买和退货按钮:
//用户购买
connect(ui.buy, &QPushButton::clicked, this, [=]() {
Users *bu = new Users();
bu->show();
});
//用户退货
connect(ui.reject, &QPushButton::clicked, this, [=]() {
Rejectt * bu = new Rejectt();
bu->show();
});
购买图书代码:
#include "Users.h"
#include<qsqlquery.h>//包含查询语言
#include <QDebug>
#include <QSqlRecord>
#include<QtSql/qsqlerror.h>//输出错误语言
#pragma execution_character_set("utf-8")
Users::Users(QWidget *parent)
: QWidget(parent)
{
ui.setupUi(this);
model = new QSqlTableModel(this);
this->setWindowTitle("购买用户的信息登记");
QPixmap Images("C:/Users/lenovo/Pictures/20200329175335_erzim.jpg");
QPalette Palette = this->palette();
Palette.setBrush(QPalette::Window, Images);
this->setPalette(Palette);
connect(ui.OOK, &QPushButton::clicked, this, [=]() {
QString namee = ui.namee->text();
QString birth = ui.birth->text();
QString books = ui.books->text();
QString timee = ui.timee->text();
QString phone = ui.phone->text();
QString summ = ui.summ->text();
if (namee.isEmpty() || birth.isEmpty() || books.isEmpty() || timee.isEmpty()||phone.isEmpty()||summ.isEmpty()) {
QMessageBox::information(this, "信息", "信息未完善,请重新填写");
return;
}
// QString stock = ua.stock->text();
QSqlQuery query ("select bname from binfor");//查书名
QSqlQuery query2("select stock from binfor");//找该书库存
QSqlQuery query3("select hbs from binfor");//找已售数量
QSqlRecord rec = query.record();
QSqlRecord rec2 = query2.record();
QSqlRecord rec3 = query3.record();
int b = summ.toInt();//临时变量,方便更新库存
int sum = 0; int nn = 0;
while (query.next()&& query2.next()&& query3.next())
{
rec = query.record();//查书名
int snamecol = rec.indexOf("bname");
QString value = query.value(snamecol).toString();//查书名
rec2 = query2.record();//找该书库存
int stockcol = rec2.indexOf("stock");//找该书库存
QString st = query2.value(stockcol).toString();
int stt = st.toInt();//临时变量,方便更新库存
//设置更改已购数量的相关变量
rec3 = query3.record();//找该书已售
int h = rec3.indexOf("hbs");
QString hb = query3.value(h).toString();
//qDebug() << "hb:" << hb;
int hh = hb.toInt();//临时变量,方便更新已售
//去掉字符串后面的空格
value.remove(QRegExp("\\s* +$"));
// qDebug() << "books:" << books;
if (books == value) {
if (summ <= st) {
QString qs = QString("insert into users(namee,birth,books,timee,phone,summ)values('%1','%2', '%3', '%4', '%5','%6')")
.arg(namee).arg(birth).arg(books).arg(timee).arg(phone).arg(summ);//
sum = stt - b;//新库存
nn = hh + b;//新已售
// qDebug() << "ewer:" << nn;
QString d = QString::number(sum);//字符串形式的库存query.exec(qs)&&
QString dd = QString::number(nn);//字符串形式的已售
// qDebug() << "ewer:" << d;
//qDebug() << "ttttt:" << str;
//更新数据库
//手动加粗,arg()中的值与出现前后无关,重点是%小的在前面
QString qs2 = QString("UPDATE binfor SET stock='%7' WHERE bname='%3'").arg(books).arg(d);
QString qs3 = QString("UPDATE binfor SET hbs='%8' WHERE bname='%3'").arg(books).arg(dd);
if (query.exec(qs)&& query2.exec(qs2)&&query3.exec(qs3)) {//如果插入成功
QMessageBox::information(this, "确定", "购买成功");
this->hide();
return;
}
else {
QMessageBox::information(this, "确定", "添加失败");
qDebug() << query.lastError().text();//输出错误信息
qDebug() << query2.lastError().text();//输出错误信息
qDebug() << query3.lastError().text();//输出错误信息
return;
}
}
else {
QMessageBox::about(this, "购买", "抱歉,库存不足");
return;
}
}
// qDebug() << "bname:" << value;
}
QMessageBox::about(this, "购买", "抱歉,暂时没有该书籍");
// qDebug() <<"sss:" << QString::compare("", "");
});
connect(ui.OFF, &QPushButton::clicked, this, [=]() {
this->hide();
});
}
Users::~Users()
{}
退货代码
#include "Rejectt.h"
#include<qsqlquery.h>//包含查询语言
#include <QDebug>
#include <QSqlRecord>
#include<QtSql/qsqlerror.h>//输出错误语言
#pragma execution_character_set("utf-8")
Rejectt::Rejectt(QWidget *parent)
: QWidget(parent)
{
ui.setupUi(this);
model = new QSqlTableModel(this);
this->setWindowTitle("退货登记");
QPixmap Images("C:/Users/lenovo/Pictures/20200329175335_erzim.jpg");
QPalette Palette = this->palette();
Palette.setBrush(QPalette::Window, Images);
this->setPalette(Palette);
connect(ui.OK, &QPushButton::clicked, this, [=]() {
QString book = ui.book->text();
QString nname = ui.nname->text();
QString num = ui.num->text();
QString tiime = ui.tiime->text();
QString reasons = ui.reasons->text();
QString pho = ui.pho->text();
if (book.isEmpty() || nname.isEmpty() || num.isEmpty() || tiime.isEmpty() || reasons.isEmpty() || pho.isEmpty()) {
QMessageBox::information(this, "信息", "信息未完善,请重新填写");
return;
}
QSqlQuery query("select bname from binfor");//查书名
QSqlRecord rec = query.record();
QSqlQuery query2("select stock from binfor");//找该书库存
QSqlRecord rec2 = query2.record();
QSqlQuery query3("select hbs from binfor");//找已售数量
QSqlRecord rec3 = query3.record();
int b = num.toInt();//临时变量,方便更新库存
qDebug() << "tui:" << b;
int sum = 0; int nn = 0;
while (query.next() && query2.next() && query3.next())
{
rec = query.record();//查书名
int snamecol = rec.indexOf("bname");
QString value = query.value(snamecol).toString();//查书名
rec2 = query2.record();//找该书库存
int stockcol = rec2.indexOf("stock");//找该书库存
QString st = query2.value(stockcol).toString();
int stt = st.toInt();//临时变量,方便更新库存
//设置更改已购数量的相关变量
rec3 = query3.record();//找该书已售
int h = rec3.indexOf("hbs");
QString hb = query3.value(h).toString();
hb.remove(QRegExp("\\s* +$"));
int hh = hb.toInt();//临时变量,方便更新已售
qDebug() << "已售:" << hb;
value.remove(QRegExp("\\s* +$"));
// qDebug() << "books:" << books;
if (book == value) {
QString qs = QString("insert into reject(book,nname,num,timme,reasons,pho)values('%1','%2', '%3', '%4', '%5','%6')")
.arg(book).arg(nname).arg(num).arg(tiime).arg(reasons).arg(pho);
sum = stt + b;//新库存
nn = hh - b;//新已售
// qDebug() << "ewer:" << nn;
QString d = QString::number(sum);//字符串形式的库存query.exec(qs)&&
QString dd = QString::number(nn);//字符串形式的已售
//更新数据库
//手动加粗,arg()中的值与出现前后无关,重点是%小的在前面
QString qs2 = QString("UPDATE binfor SET stock='%7' WHERE bname='%3'").arg(book).arg(d);
QString qs3 = QString("UPDATE binfor SET hbs='%8' WHERE bname='%3'").arg(book).arg(dd);
if (query.exec(qs) && query2.exec(qs2) && query3.exec(qs3)) {//如果插入成功
QMessageBox::information(this, "确定", "退货成功");
this->hide();
return;
}
else {
QMessageBox::information(this, "确定", "添加失败");
qDebug() << query.lastError().text();//输出错误信息
qDebug() << query2.lastError().text();//输出错误信息
qDebug() << query3.lastError().text();//输出错误信息
return;
}
}
}
QMessageBox::about(this, "退货警告", "该书与本书店无关");
// qDebug() <<"sss:" << QString::compare("", "");
});
connect(ui.NO, &QPushButton::clicked, this, [=]() {
this->hide();
});
}
Rejectt::~Rejectt()
{}
如果信息有误,可以删除
//用户删除
connect(ui.Delete_2, &QPushButton::clicked, this, [=]() {
int row2 = ui.userinfo->currentIndex().row();
int del2 = QMessageBox::warning(this, tr("删除"), tr("确定删除该记录吗"), QMessageBox::Yes | QMessageBox::No);
if (del2 == QMessageBox::Yes) {
//删除该行 提交到数据库
model2->removeRow(row2);
model2->submitAll();
}
else {
//不删除,撤销操作
model2->revertAll();
}
});
//退货信息删除
connect(ui.Delete_3, &QPushButton::clicked, this, [=]() {
int row3 = ui.rejject->currentIndex().row();
int del3 = QMessageBox::warning(this, tr("删除"), tr("确定删除该记录吗"), QMessageBox::Yes | QMessageBox::No);
if (del3 == QMessageBox::Yes) {
//删除该行 提交到数据库
model3->removeRow(row3);
model3->submitAll();
}
else {
//不删除,撤销操作
model3->revertAll();
}
});
七、刷新
对更改之后的数据重新显示
//刷新
connect(ui.renew, &QPushButton::clicked, this, [=]() {
model->setTable("binfor");//显示数据库中的binfor表
model->select();
model->setHeaderData(0, Qt::Horizontal, "编号");
model->setHeaderData(1, Qt::Horizontal, "书名");
model->setHeaderData(2, Qt::Horizontal, "作者");
model->setHeaderData(3, Qt::Horizontal, "出版社");
model->setHeaderData(4, Qt::Horizontal, "类型");
model->setHeaderData(5, Qt::Horizontal, "单价/元");
model->setHeaderData(6, Qt::Horizontal, "已售/本");
model->setHeaderData(7, Qt::Horizontal, "库存/本");
//model->setEditStrategy(QSqlTableModel::OnManualSubmit);
ui.information->setModel(model);
//用户购买信息
model2->setTable("users");//显示数据库中的binfor表
model2->select();
model2->setHeaderData(0, Qt::Horizontal, "用户名");
model2->setHeaderData(1, Qt::Horizontal, "出生日期");
model2->setHeaderData(2, Qt::Horizontal, "购买图书");
model2->setHeaderData(3, Qt::Horizontal, "购买时间");
model2->setHeaderData(4, Qt::Horizontal, "联系方式");
model2->setHeaderData(5, Qt::Horizontal, "购买数量");
ui.userinfo->setModel(model2);
model3->setTable("reject");//显示数据库中的binfor表
model3->select();
model3->setHeaderData(0, Qt::Horizontal, "退货书籍");
model3->setHeaderData(1, Qt::Horizontal, "退货人");
model3->setHeaderData(2, Qt::Horizontal, "退货数量");
model3->setHeaderData(3, Qt::Horizontal, "退货时间");
model3->setHeaderData(4, Qt::Horizontal, "退货原因");
model3->setHeaderData(5, Qt::Horizontal, "联系电话");
ui.rejject->setModel(model3);
});