#include "database.h"
#include <QSqlQuery>
#include <QVariant>
#include <QDebug>
#include <QSqlError>
#include <QDateTime>
Database* Database::instance = NULL;
Database::Database()
{
}
Database *Database::getInstance()
{
if(instance == NULL){
instance = new Database();
}
return instance;
}
bool Database::login(const QString& admin_id, const QString& admin_password)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("SELECT * FROM admin WHERE admin_id=? AND admin_password=? LIMIT 1");
query.bindValue(0, admin_id);
query.bindValue(1, admin_password);
query.exec();
return query.first();
}
bool Database::addUser(const User &user)
{
QSqlDatabase db = QSqlDatabase::database();
db.transaction();
QSqlQuery query(db);
query.prepare("INSERT INTO user(user_id,user_name,user_count) VALUES(?,?,?)");
query.bindValue(0, user.user_id);
query.bindValue(1, user.user_name);
query.bindValue(2, user.user_count);
if(!query.exec()){
db.rollback();
return false;
}
query.prepare("INSERT INTO goods(user_id,food_id,quantity)"
" SELECT ?,food_id,user_per_quantity*? FROM food");
query.bindValue(0, user.user_id);
query.bindValue(1, user.user_count);
if(!query.exec()){
db.rollback();
return false;
}
return db.commit();
}
bool Database::deleteUser(qint64 user_id)
{
QSqlDatabase db = QSqlDatabase::database();
db.transaction();
QSqlQuery query(db);
query.prepare("DELETE FROM user WHERE user_id=?");
query.bindValue(0, user_id);
if(!query.exec()){
db.rollback();
return false;
}
query.prepare("DELETE FROM goods WHERE user_id=?");
query.bindValue(0, user_id);
if(!query.exec()){
db.rollback();
return false;
}
return db.commit();
}
bool Database::getUserList(QList<User> &userList)
{
QSqlQuery query(QSqlDatabase::database());
bool result = query.exec("SELECT user_id,user_name,user_count FROM user");
while(query.next()){
User user;
user.user_id = query.value(0).toLongLong();
user.user_name = query.value(1).toString();
user.user_count = query.value(2).toDouble();
userList.append(user);
}
return result;
}
QString Database::getUserNameById(qint64 user_id)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("SELECT user_name FROM user WHERE user_id=? LIMIT 1");
query.bindValue(0, user_id);
query.exec();
if(query.first()){
return query.value(0).toString();
}else{
return QString();
}
}
bool Database::updateUserName(qint64 userId, const QString &userName)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE user SET user_name=? WHERE user_id=?");
query.bindValue(0, userName);
query.bindValue(1, userId);
return query.exec();
}
bool Database::updateUserCount(qint64 userId, double userCount)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE user SET user_count=? WHERE user_id=?");
query.bindValue(0, userCount);
query.bindValue(1, userId);
return query.exec();
}
bool Database::updateUserQuantity(qint64 userId, int foodId, int quantity)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE goods SET quantity=? WHERE user_id=? AND food_id=?");
query.bindValue(0, quantity);
query.bindValue(1, userId);
query.bindValue(2, foodId);
return query.exec();
}
bool Database::getUserDetail(QList<QStringList> &userDetail, qint64 userId)
{
//粮食-单位-剩余 / user_id food_id
QSqlQuery query(QSqlDatabase::database());
query.prepare("SELECT food.food_name, food.food_unit, goods.quantity,"
"user.user_id, food.food_id FROM food"
" JOIN goods ON food.food_id=goods.food_id"
" JOIN user ON goods.user_id=user.user_id"
" WHERE user.user_id=?");
query.bindValue(0, userId);
if(query.exec()){
while(query.next()){
QStringList _list;
_list<<query.value(0).toString();
_list<<query.value(1).toString();
_list<<query.value(2).toString();
_list<<query.value(3).toString();
_list<<query.value(4).toString();
userDetail.append(_list);
}
return true;
}else{
return false;
}
}
bool Database::addAdmin(const Admin &admin)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("INSERT INTO admin(admin_id,admin_password,admin_name,admin_type) VALUES(?,?,?,1)");
query.bindValue(0, admin.admin_id);
query.bindValue(1, admin.admin_password);
query.bindValue(2, admin.admin_name);
return query.exec();
}
bool Database::getAdminList(QList<Admin> &adminList)
{
QSqlQuery query(QSqlDatabase::database());
bool result = query.exec("SELECT admin_id,admin_name,admin_type FROM admin");
while(query.next()){
Admin admin;
admin.admin_id = query.value(0).toLongLong();
admin.admin_name = query.value(1).toString();
admin.admin_type = query.value(2).toInt();
adminList.append(admin);
}
return result;
}
QString Database::getAdminNameById(qint64 adminId)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("SELECT admin_name FROM admin WHERE admin_id=? LIMIT 1");
query.bindValue(0, adminId);
query.exec();
if(query.first()){
return query.value(0).toString();
}else{
return QString();
}
}
int Database::getAdminType(qint64 adminId)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("SELECT admin_type FROM admin WHERE admin_id=? LIMIT 1");
query.bindValue(0, adminId);
query.exec();
if(query.first()){
return query.value(0).toInt();
}else{
return 1;
}
}
QString Database::getAuthCode()
{
QSqlQuery query(QSqlDatabase::database());
query.exec("SELECT admin_auth FROM admin WHERE admin_type=0 LIMIT 1");
if(query.first()){
return query.value(0).toString();
}else{
return QString();
}
}
bool Database::setAuthCode(const QString &authCode)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE admin SET admin_auth=? WHERE admin_type=0");
query.bindValue(0, authCode);
return query.exec();
}
bool Database::addFood(const Food &food)
{
QSqlDatabase db = QSqlDatabase::database();
db.transaction();
QSqlQuery query(db);
query.prepare("INSERT INTO food(food_name,food_unit,food_quantity,food_price,user_per_quantity) VALUES(?,?,0,?,?)");
query.bindValue(0, food.food_name);
query.bindValue(1, food.food_unit);
query.bindValue(2, food.food_price);
query.bindValue(3, food.user_per_quantity);
if(!query.exec()){
db.rollback();
return false;
}
int food_id = query.lastInsertId().toInt();
query.prepare("INSERT INTO goods(user_id,food_id,quantity)"
" SELECT user_id,?,?*user_count FROM user");
query.bindValue(0, food_id);
query.bindValue(1, food.user_per_quantity);
if(!query.exec()){
db.rollback();
return false;
}
return db.commit();
}
bool Database::deleteFood(int food_id)
{
QSqlDatabase db = QSqlDatabase::database();
db.transaction();
QSqlQuery query(db);
query.prepare("DELETE FROM food WHERE food_id=?");
query.bindValue(0, food_id);
if(!query.exec()){
db.rollback();
return false;
}
query.prepare("DELETE FROM goods WHERE food_id=?");
query.bindValue(0, food_id);
if(!query.exec()){
db.rollback();
return false;
}
return db.commit();
}
bool Database::updateFoodName(int food_id, const QString &food_name)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE food SET food_name=? WHERE food_id=?");
query.bindValue(0, food_name);
query.bindValue(1, food_id);
return query.exec();
}
bool Database::updateFoodUnit(int food_id, const QString &food_unit)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE food SET food_unit=? WHERE food_id=?");
query.bindValue(0, food_unit);
query.bindValue(1, food_id);
return query.exec();
}
bool Database::updateFoodPrice(int food_id, double food_price)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE food SET food_price=? WHERE food_id=?");
query.bindValue(0, food_price);
query.bindValue(1, food_id);
return query.exec();
}
bool Database::updateFoodUserPerQuantity(int food_id, int user_per_quantity)
{
QSqlQuery query(QSqlDatabase::database());
query.prepare("UPDATE food SET user_per_quantity=? WHERE food_id=?");
query.bindValu
基于QT的粮食分配管理软件.zip （56个子文件） 
FoodAdmin-master 
resource.qrc 297B purchasemodel.h 287B FoodAdmin.pro 1KB mainwindow.h 2KB bean.h 1KB foodmodel.h 574B addadmindialog.h 380B authorizedialog.cpp 520B userdetaildialog.h 738B addfooddialog.h 374B shipmentdialog.ui 3KB mytablemodel.h 737B adduserdialog.ui 7KB usermodel.h 625B mainwindow.cpp 8KB shipmentmodel.cpp 568B mytablemodel.cpp 997B addadmindialog.cpp 1KB database.cpp 18KB main.cpp 628B mainwindow.ui 25KB authorizedialog.ui 2KB shipmentdialog.cpp 12KB suppliermodel.h 234B userdetaildialog.ui 552B foodmodel.cpp 3KB purchasedialog.h 578B authorizedialog.h 450B adminmodel.cpp 523B adduserdialog.cpp 1KB suppliermodel.cpp 554B addsupplierdialog.ui 7KB addadmindialog.ui 7KB shipmentmodel.h 254B addfooddialog.ui 9KB addsupplierdialog.h 404B icon.ico 264KB userdetaildialog.cpp 2KB images 
background.jpg 100KB
shipment.png 2KB supplier.png 4KB admin.png 3KB user.png 3KB warehouse.png 2KB purchase.png 2KB shipmentdialog.h 2KB purchasedialog.cpp 7KB addsupplierdialog.cpp 1KB addfooddialog.cpp 1KB FoodAdmin.pro.user 18KB purchasedialog.ui 9KB adminmodel.h 209B database.h 2KB purchasemodel.cpp 624B usermodel.cpp 3KB adduserdialog.h 372B资源评论
