jia_tan_diyic/handle/database.cpp

#include "database.h"
#include "QSqlRecord"
#include <QStringList>

Database::Database(QObject *parent) :
    QObject(parent)
{

}

Database::~Database()
{
    _database.close();
}

void Database::SetTableName(QString table)
{
    _tableName = table + ".db";
}

bool Database::openDB()
{
    _database = QSqlDatabase::addDatabase("QSQLITE","db");
//    _database.setDatabaseName(_tableName);
    _database.setDatabaseName(".//qtDb.db");
    return _database.open();
}

bool Database::createProductDatabase()
{
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);



    bool success = query.exec("CREATE TABLE product ("
                                  "goods_id INTEGER NOT NULL,"              //商品ID
                                  "code_single VARCHAR NOT NULL,"           //码单
                                  "quantity INTEGER NOT NULL,"              //箱数
                                  "batch_no VARCHAR NOT NULL,"              //批号
                                  "category VARCHAR NOT NULL,"              //类型
                                  "level_id INTEGER NOT NULL,"              //等级id
                                  "tube_number INTEGER NOT NULL,"           //筒数
                                  "gross_weight INTEGER NOT NULL,"          //毛重 单位克
                                  "net_weight INTEGER NOT NULL,"            //净重 单位克
                                  "machine_no VARCHAR NOT NULL,"            //机台
                                  "packing_time VARCHAR NOT NULL,"          //打包时间
                                  "packing_type VARCHAR NOT NULL,"          //打包种类
                                  "carton_type VARCHAR NOT NULL,"           //纸箱种类
                                  "operator_id INTEGER NOT NULL,"           //操作员ID
                                  "specs VARCHAR NOT NULL,"                 //规格
                                  "denier VARCHAR,"                         //旦数(D)
                                  "dtex VARCHAR,"                           //分特(dt)
                                  "fiber VARCHAR,"                          //孔数(F)
                                  "tongbu VARCHAR(1) DEFAULT 0,"            //同步状态
                                  "remark VARCHAR);");                      //备注

//     success = query.exec("drop table product;");

    if(success)
    {
        qDebug() << QObject::tr("product数据库表创建成功！\n");
        return true;
    }
    else
    {
        qDebug() << QObject::tr("product数据库表已存在！\n");
        return false;
    }
}

bool Database::createDetailsDatabase()
{
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    bool success = query.exec("CREATE TABLE details ("
                                  "code_single VARCHAR NOT NULL,"           //码单
                                  "case_number VARCHAR NOT NULL,"           //箱号
                                  "box_weight INTEGER NOT NULL,"            //皮重 单位克
                                  "tube_number INTEGER NOT NULL,"           //筒数
                                  "bucket_weight INTEGER NOT NULL,"         //筒重 单位克
                                  "gross_weight INTEGER NOT NULL,"          //毛重 单位克
                                  "net_weight INTEGER NOT NULL,"            //净重 单位克
                                  "sort VARCHAR NOT NULL,"                  //排序
                                  "remark VARCHAR);");                      //备注

//     success = query.exec("drop table details;");

    if(success)
    {
        qDebug() << QObject::tr("details数据库表创建成功！");
        return true;
    }
    else
    {
        qDebug() << QObject::tr("details数据库表已存在！");
        return false;
    }
}

bool Database::insertProduct(product_data productData)
{
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    query.prepare("INSERT INTO product(goods_id,"
                          "code_single, quantity, batch_no, category,level_id, tube_number,"
                          "gross_weight, net_weight, machine_no, packing_time,"
                          "packing_type, carton_type, operator_id, specs, denier, dtex, fiber, remark)"
                          "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
    query.bindValue(0,productData.goods_id);
    query.bindValue(1,productData.code_single);
    query.bindValue(2,productData.quantity);
    query.bindValue(3,productData.batch_no);
    query.bindValue(4,productData.category);
    query.bindValue(5,productData.level_id);
    query.bindValue(6,productData.tube_number);
    query.bindValue(7,productData.gross_weight);
    query.bindValue(8,productData.net_weight);
    query.bindValue(9,productData.machine_no);
    query.bindValue(10,productData.packing_time);
    query.bindValue(11,productData.packing_type);
    query.bindValue(12,productData.carton_type);
    query.bindValue(13,productData.operator_id);
    query.bindValue(14,productData.specs);
    query.bindValue(15,productData.denier);
    query.bindValue(16,productData.dtex);
    query.bindValue(17,productData.fiber);
    query.bindValue(18,productData.remark);

    bool success=query.exec();
    return success;
}

bool Database::insertDetails(details_data detailsData)
{
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    query.prepare("INSERT INTO details(code_single,"
                  "case_number, box_weight, tube_number, bucket_weight,"
                  "gross_weight, net_weight, sort, remark)"
                  "VALUES(?,?,?,?,?,?,?,?,?)");
    query.bindValue(0,detailsData.code_single);
    query.bindValue(1,detailsData.case_number);
    query.bindValue(2,detailsData.box_weight);
    query.bindValue(3,detailsData.tube_number);
    query.bindValue(4,detailsData.bucket_weight);
    query.bindValue(5,detailsData.gross_weight);
    query.bindValue(6,detailsData.net_weight);
    query.bindValue(7,detailsData.sort);
    query.bindValue(8,detailsData.remark);

    bool success=query.exec();
    return success;
}

bool Database::changeState(QString codesingle)
{
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    bool success = query.exec("update product set tongbu=1 where code_single="+codesingle);
    if(success)
    {
        return true;
    }
    else
    {
        return false;
    }
}

bool Database::update(QString &table, QStringList &names, QStringList &values, QString &expression)
{
    if (names.size() != values.size())
        return false;
    //UPDATE 表名称 SET 列名称 = 新值 WHERE 列名称 = 某值
//    QSqlQuery query(QSqlDatabase::database());

    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    QString sql = QString("update ")+table+QString(" set ");
    for (int i = 0; i < names.size(); i++)
    {
        sql += names.value(i);
        sql += QString(" = '");
        sql = sql + values.value(i);
        sql = sql + QString("'");
        if (i != names.size()-1)
            sql = sql + QString(" ,");
    }
    sql = sql + QString(" where ") + expression;
    return (query.exec(sql)) ? true : false;
}

bool Database::del(QString &table, QString &expression)
{
    //DELETE FROM 表名称 WHERE 列名称 = 值
    QSqlQuery query(QSqlDatabase::database());
    QString sql = QString("delete from ") + table + QString(" where ") + expression;
    return (query.exec(sql)) ? true : false;
}

QList<QMap<QString,QString>> Database::GetValues(QString table, QStringList &values,QString condition)
{
    dbmxdata.clear();
    dbmxlist.clear();
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    QString sql = QString("select * from ") + table +" "+ condition;
    query.exec(sql);

    qDebug()<<table;
    while (query.next())
    {
        for(int i = 0;i <= values.size()-1;i++){
            qDebug()<<values.at(i) << query.value(values.at(i)).toString();
            dbmxdata.insert(values.at(i),query.value(values.at(i)).toString());
        }
        dbmxlist.append(dbmxdata);
        dbmxdata.clear();
    }
    return dbmxlist;
}

//获取单个字段数据，以数组形式返回；
QStringList Database::GetValuesList(QString table, QString value,QString condition)
{
    QStringList list;
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);
//    QString value;
//    for(int i = 0;i<values.size();i++){
//        value.append(values.at(i));
//        if(!i == values.size()-1){
//            value.append(",");
//        }
//    }
    QString sql = "select "+value+" from " + table +" "+ condition;
    query.exec(sql);
    while (query.next())
    {
            list.append(query.value(value).toString());
//            qDebug()<<query.value(values.at(i)).toString();
    }
    return list;
}

int Database::GetTablePage()
{
    QSqlDatabase db = QSqlDatabase::database("db");
    QSqlQuery query(db);

    QString sql = QString("select count(*) from product");
    query.exec(sql);
    if(query.first())
    {
        return query.value(0).toInt();
    }
    return 0;
}

//void Database::connectDB(QString dbName, QString username, QString password)
//{
//    if (QSqlDatabase::contains("qt_sql_default_connection"))
//    {
//        database = QSqlDatabase::database("qt_sql_default_connection");
//    }
//    else
//    {
//        database = QSqlDatabase::addDatabase("QSQLITE");
//        database.setDatabaseName(dbName);
//        database.setUserName(username);
//        database.setPassword(password);
//    }

//    if (!database.open())
//    {
//        qDebug() << "Error: Failed to connect database." << database.lastError();
//    }
//    else
//    {
//        QSqlQuery sql_query;
//        QString create_sql;
////        create_sql = "create table login (name varchar(100), logintime DATETIME)";
//        sql_query.prepare(create_sql);
//        if(!sql_query.exec())
//        {
//            qDebug() << "Error: Fail to create table." << sql_query.lastError();
//        }
//        else
//        {
//            qDebug() << "Table created!";
//        }
//    }

//}


//bool Database::insert(QString &table, QStringList &names, QStringList &values)
//{
//    if(names.size() != values.size())
//    {
//        return false;
//    }

////    QSqlQuery query(QSqlDatabase::database());

//    QSqlDatabase db = QSqlDatabase::database("db");
//    QSqlQuery query(db);

//    QString sql = QString("insert into ") + table + QString("(");

//    int i;
//    for(i = 0; i < names.size(); i++)
//    {
//        sql = sql + names.value(i);
//        if(i != names.size()-1)
//        {
//            sql += QString(",");
//        }
//        else
//        {
//            sql += QString(")");
//        }
//    }

//    sql = sql + QString("values (");

//    for(i = 0; i < values.size(); i++)
//    {
//        sql = sql + QString("'") + values.value(i) + QString("'");
//        if (i != values.size()-1)
//        {
//            sql += QString(",");
//        }
//    }
//    sql += QString(")");

//    return (query.exec(sql)) ? true : false;
//}