#include "sql.h"

SQL::SQL(QObject *parent) : QObject(parent)
{
    if (QSqlDatabase::contains("qt_sql_default_connection"))
    {
        database = QSqlDatabase::database("qt_sql_default_connection");
    }
    else
    {
        // 建立和SQlite数据库的连接
        database = QSqlDatabase::addDatabase("QSQLITE");
        // 设置数据库文件的名字
        database.setDatabaseName("MyDataBase.db");
    }
}
void SQL::open(){
    if (!database.open())
    {
        qDebug() << "Error: Failed to connect database." << database.lastError();
    }
    else
    {
        // do something
    }
}

void SQL::close(){
    database.close();
}

// 清除所有的核心数据
void SQL::foodClearCoreData(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from food where create_user = 0";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::foodClearUserData(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from food where create_user != 0";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
// 添加食物
void SQL::foodAdd(Food food){
    open();
    QSqlQuery sqlQuery;
    QString sql = "SELECT create_id FROM food where create_user = 0 order by create_id desc limit 0,1";
    qDebug()<<sql;
    int index = 1;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            index = sqlQuery.value("create_id").toInt() + 1;
        }
    }
    sql = "insert into food(create_user,create_id,name,type,shelf_time,unit,info,alias,pic) values("
            +QString::number(food.create_user)+","+QString::number(index)+",'"
            +food.name+"',"+QString::number(food.type)+","+QString::number(food.shelf_time)+","
            +QString::number(food.unit)+",'"+food.info+"','"+food.alias+"','"+food.pic+"')";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::foodAdd(QList<Food> foods){
    open();
    QSqlQuery sqlQuery;
    for(int i =0 ;i<foods.size();i++)
    {
        Food food = foods.at(i);
        QString sql = "insert into food(create_user,create_id,name,type,shelf_time,unit,info,alias,pic) values("
                +QString::number(food.create_user)+","+QString::number(food.create_id)+",'"
                +food.name+"',"+QString::number(food.type)+","+QString::number(food.shelf_time)+","
                +QString::number(food.unit)+",'"+food.info+"','"+food.alias+"','"+food.pic+"')";
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }

}

void SQL::unitClearCoreData(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from unit";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::unitAdd(Unit unit){
    open();
    QSqlQuery sqlQuery;
    QString sql = "SELECT id FROM unit order by id desc limit 0,1";
    qDebug()<<sql;
    int index = 1;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            index = sqlQuery.value("id").toInt() + 1;
        }
    }
    sql = "insert into unit(id,name) values("
            +QString::number(index)+",'"+unit.name+"')";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::unitAdd(QList<Unit> units){
    open();
    QSqlQuery sqlQuery;
    for(int i=0;i<units.size();i++)
    {
        Unit unit = units.at(i);
        QString sql = "insert into unit(id,name) values("
                +QString::number(unit.id)+",'"+unit.name+"')";
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }

}

void SQL::foodtypeClearCoreData(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from food_type";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::foodtypeAdd(FoodType foodtype){
    open();
    QSqlQuery sqlQuery;
    QString sql = "SELECT id FROM food_type order by id desc limit 0,1";
    qDebug()<<sql;
    int index = 1;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            index = sqlQuery.value("id").toInt() + 1;
        }
    }
    sql = "insert into food_type(id,name,pic) values("
            +QString::number(index)+",'"+foodtype.name+"','"
            +foodtype.pic+"')";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::foodtypeAdd(QList<FoodType> foodtypes){
    open();
    QSqlQuery sqlQuery;
    for(int i =0 ;i<foodtypes.size();i++)
    {
        FoodType foodtype = foodtypes.at(i);
        QString sql = "insert into food_type(id,name,pic) values("
                +QString::number(foodtype.id)+",'"+foodtype.name+"','"
                +foodtype.pic+"')";
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }

}

// 食物
QList<Food> SQL::foodSearch(QString name,int size,int index){
    open();
    QSqlQuery sqlQuery;
    if(index>0)
        index--;
    int start = index*size;
    QList<Food> list;
    QString sql = "SELECT * FROM v_food where name like '%"+name+"%' limit "+ QString::number(start)+","+QString::number(size);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Food food;
            food.create_id = sqlQuery.value("create_id").toInt();
            food.create_user = sqlQuery.value("create_user").toInt();
            food.name = sqlQuery.value("name").toString();
            food.type = sqlQuery.value("type").toInt();
            food.shelf_time = sqlQuery.value("shelf_time").toInt();
            food.unit = sqlQuery.value("unit").toInt();
            food.info = sqlQuery.value("info").toString();
            food.alias = sqlQuery.value("alias").toString();
            food.pic = sqlQuery.value("pic").toString();
            food.unitName = sqlQuery.value("unitName").toString();
            food.typeName = sqlQuery.value("typeName").toString();
            food.typePic = sqlQuery.value("typePic").toString();
            list.append(food);
        }
    }
    return list;
}
QList<Food> SQL::foodUserData(){
    open();
    QSqlQuery sqlQuery;
    QList<Food> list;
    QString sql = "SELECT * FROM food where create_user != 0";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Food food;
            food.create_id = sqlQuery.value("create_id").toInt();
            food.create_user = sqlQuery.value("create_user").toInt();
            food.name = sqlQuery.value("name").toString();
            food.type = sqlQuery.value("type").toInt();
            food.shelf_time = sqlQuery.value("shelf_time").toInt();
            food.unit = sqlQuery.value("unit").toInt();
            food.info = sqlQuery.value("info").toString();
            food.alias = sqlQuery.value("alias").toString();
            food.pic = sqlQuery.value("pic").toString();
            list.append(food);
        }
    }
    return list;
}
// 存储
void SQL::storageClearUserData(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from storage";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
QList<Storage> SQL::storageSearchAll(){
    open();
    QSqlQuery sqlQuery;
    QList<Storage> list;
    if(!sqlQuery.exec("SELECT * FROM v_storage"))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Storage storage;
            storage.user_id = sqlQuery.value("create_user").toInt();
            storage.food_id = sqlQuery.value("create_id").toInt();
            storage.name = sqlQuery.value("name").toString();
            storage.type = sqlQuery.value("typeName").toString();
            storage.unit = sqlQuery.value("unitName").toString();
            storage.pic = sqlQuery.value("pic").toString();
            storage.info = sqlQuery.value("info").toString();
            storage.amount = sqlQuery.value("amount").toInt();
            storage.alias = sqlQuery.value("alias").toString();
            list.append(storage);
        }
    }
    return list;
}
QList<Storage> SQL::storageUserData(){
    open();
    QSqlQuery sqlQuery;
    QList<Storage> list;
    if(!sqlQuery.exec("SELECT * FROM storage"))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Storage storage;
            storage.user_id = sqlQuery.value("user_id").toInt();
            storage.food_id = sqlQuery.value("food_id").toInt();
            storage.amount = sqlQuery.value("amount").toInt();
            list.append(storage);
        }
    }
    return list;
}
void SQL::storageAdd(long user_id,long food_id,int amount){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"user_id："<<(int)user_id;
    qDebug()<<"food_id："<<(int)food_id;
    qDebug()<<"amount："<<amount;
    QString sql = "SELECT amount FROM storage where user_id = ? and food_id = ?";
    qDebug()<<sql;
    sqlQuery.prepare(sql);
    sqlQuery.addBindValue((int)user_id);
    sqlQuery.addBindValue((int)food_id);
    sqlQuery.exec();
    bool have = false;
    int total = 0;
    if(!sqlQuery.exec())
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            have = true;
            total = sqlQuery.value("amount").toInt();
        }
    }
    if(!have)
    {
        sql = "insert into storage(user_id,food_id,amount) values("+QString::number(user_id)+","+QString::number(food_id)+","+QString::number(amount)+")";
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }
    else{
        sql = "update storage set amount = "+QString::number(amount+total)+" where user_id = "+QString::number(user_id)+" and food_id = "+QString::number(food_id);
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }
}
void SQL::storageAdd(QList<Storage> storages)
{
    open();
    QSqlQuery sqlQuery;
    for(int i = 0 ;i<storages.size();i++)
    {
        Storage storage = storages.at(i);
        QString sql = "insert into storage(user_id,food_id,amount) values("
                +QString::number(storage.user_id)+","+QString::number(storage.food_id)
                +","+QString::number(storage.amount)+")";
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }
}
void SQL::storageUpdate(long user_id,long food_id,int amount){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"user_id："<<(int)user_id;
    qDebug()<<"food_id："<<(int)food_id;
    qDebug()<<"amount："<<amount;
    QString sql = "update storage set amount = "+QString::number(amount)+" where user_id = "+QString::number(user_id)+" and food_id = "+QString::number(food_id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::storageDelete(long user_id,long food_id){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"user_id："<<(int)user_id;
    qDebug()<<"food_id："<<(int)food_id;
    QString sql = "delete from storage where user_id = "+QString::number(user_id)+" and food_id = "+QString::number(food_id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}

// 提醒
void SQL::remindClearUserData(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from remind";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
QList<Remind> SQL::remindSearchAll(){
    open();
    QSqlQuery sqlQuery;
    QList<Remind> list;
    sqlQuery.exec("SELECT * FROM v_remind");
    if(!sqlQuery.exec())
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Remind remind;
            remind.user_id = sqlQuery.value("create_user").toInt();
            remind.food_id = sqlQuery.value("create_id").toInt();
            remind.name = sqlQuery.value("name").toString();
            remind.type = sqlQuery.value("typeName").toString();
            remind.unit = sqlQuery.value("unitName").toString();
            remind.pic = sqlQuery.value("pic").toString();
            remind.info = sqlQuery.value("info").toString();
            remind.amount = sqlQuery.value("amount").toInt();
            remind.id = sqlQuery.value("id").toInt();
//            QString string = sqlQuery.value("date").toString();
//            remind.date = QDate::fromString("yyyy-MM-dd",string);
            remind.date = sqlQuery.value("date").toDate();
            list.append(remind);
        }
    }
    return list;
}
QList<Remind> SQL::remindUserData(){
    open();
    QSqlQuery sqlQuery;
    QList<Remind> list;
    sqlQuery.exec("SELECT * FROM remind");
    if(!sqlQuery.exec())
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Remind remind;
            remind.user_id = sqlQuery.value("user_id").toInt();
            remind.food_id = sqlQuery.value("food_id").toInt();
            remind.amount = sqlQuery.value("amount").toInt();
            remind.id = sqlQuery.value("id").toInt();
            remind.date = sqlQuery.value("date").toDate();
            list.append(remind);
        }
    }
    return list;
}
void SQL::remindAdd(long user_id,long food_id,int amount,QDate date){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"user_id："<<(int)user_id;
    qDebug()<<"food_id："<<(int)food_id;
    qDebug()<<"amount："<<amount;
    qDebug()<<"date:"<<date;
    QString sql = "SELECT id FROM remind order by id desc limit 0,1";
    qDebug()<<sql;
    int index = 0;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            index = sqlQuery.value("id").toInt() + 1;
        }
    }
    sql = "insert into remind(id,user_id,food_id,amount,date) values("+QString::number(index)+","
            +QString::number(user_id)+","+QString::number(food_id)+","+QString::number(amount)+",'"+date.toString("yyyy-MM-dd")+"')";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}
void SQL::remindAdd(QList<Remind> reminds)
{
    open();
    QSqlQuery sqlQuery;
    for(int i = 0 ;i<reminds.size();i++)
    {
        Remind remind = reminds.at(i);
        QString sql = "insert into remind(id,user_id,food_id,amount,date) values("+QString::number(remind.id)+","
                +QString::number(remind.user_id)+","+QString::number(remind.food_id)+","
                +QString::number(remind.amount)+",'"+remind.date.toString("yyyy-MM-dd")+"')";
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }
}
void SQL::remindUpdate(long id,int amount, QDate date){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"id："<<id;
    qDebug()<<"amount："<<amount;
    qDebug()<<"date:"<<date;
    QString sql = "update remind set amount = "+QString::number(amount)+" , date ='"+date.toString("yyyy-MM-dd")+"' where id = "+QString::number(id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }

}
void SQL::remindDelete(long id){

    open();
    QSqlQuery sqlQuery;
    qDebug()<<"id:"<<id;
    QString sql = "delete from remind where id = "+QString::number(id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }

}


QList<Option> SQL::optionSearchUncommitAll(){
    open();
    QSqlQuery sqlQuery;
    QList<Option> list;
    QString sql = "SELECT * FROM v_option where status = 0 order by datetime";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Option option;
            option.id = sqlQuery.value("id").toInt();
            option.name = sqlQuery.value("name").toString();
            option.pic = sqlQuery.value("pic").toString();
            option.amount = sqlQuery.value("amount").toInt();
            option.status = sqlQuery.value("status").toInt();
            option.direction = sqlQuery.value("direction").toInt();
            option.food_id = sqlQuery.value("create_id").toInt();
            option.user_id = sqlQuery.value("create_user").toInt();
            option.datetime = sqlQuery.value("datetime").toDateTime();
            option.typeName = sqlQuery.value("typeName").toString();
            option.unitName = sqlQuery.value("unitName").toString();
            list.append(option);
        }
    }
    return list;
}

void SQL::optionAdd(Option option){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"option: "<<option;
    QString sql = "SELECT id FROM option order by id desc limit 0,1";
    qDebug()<<sql;
    int index = 0;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            index = sqlQuery.value("id").toInt() + 1;
        }
    }
    sql = "insert into option(id,user_id,food_id,status,direction,amount,datetime) values("+QString::number(index)+","
            +QString::number(option.user_id)+","+QString::number(option.food_id)+","
            +QString::number(option.status)+","+QString::number(option.direction)+","+QString::number(option.amount)+",'"
            +option.datetime.toString("yyyy-MM-dd hh:mm:ss")+"')";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}

void SQL::optionAdd(QList<Option> options){
    open();
    if(options.size()>=1){
        QSqlQuery sqlQuery;
        qDebug()<<"option: "<<options;
        QString sql = "SELECT id FROM option order by id desc limit 0,1";
        qDebug()<<sql;
        int index = 0;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
        else
        {
            while(sqlQuery.next())
            {
                index = sqlQuery.value("id").toInt() + 1;
            }
        }
        sql = "insert into option(id,user_id,food_id,status,direction,amount,datetime) values("+QString::number(index++)+","
                +QString::number(options.at(0).user_id)+","+QString::number(options.at(0).food_id)+","
                +QString::number(options.at(0).status)+","+QString::number(options.at(0).direction)+","+QString::number(options.at(0).amount)
                +",'"+options.at(0).datetime.toString("yyyy-MM-dd hh:mm:ss")+"')";
        for (int i=1;i<options.size();i++)
        {
            sql = sql +",("+QString::number(index++)+","
                    +QString::number(options.at(i).user_id)+","+QString::number(options.at(i).food_id)+","
                    +QString::number(options.at(i).status)+","+QString::number(options.at(i).direction)+","+QString::number(options.at(i).amount)
                    +",'"+options.at(i).datetime.toString("yyyy-MM-dd hh:mm:ss")+"')";
        }
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }
    else{
        return;
    }
}
QList<Option> SQL::optionSearchCommitAll(){
    open();
    QSqlQuery sqlQuery;
    QList<Option> list;
    QString sql = "SELECT * FROM v_option where status = 1 order by datetime";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            Option option;
            option.id = sqlQuery.value("id").toInt();
            option.name = sqlQuery.value("name").toString();
            option.pic = sqlQuery.value("pic").toString();
            option.amount = sqlQuery.value("amount").toInt();
            option.status = sqlQuery.value("status").toInt();
            option.direction = sqlQuery.value("direction").toInt();
            option.food_id = sqlQuery.value("create_id").toInt();
            option.user_id = sqlQuery.value("create_user").toInt();
            option.datetime = sqlQuery.value("datetime").toDateTime();
            option.typeName = sqlQuery.value("typeName").toString();
            option.unitName = sqlQuery.value("unitName").toString();
            list.append(option);
        }
    }
    return list;
}

void SQL::optionCommit(long id){
    open();
    QSqlQuery sqlQuery;
    bool haveFind = false;
    int shelf_time=0;
    Option option;
    QString sql = "SELECT * FROM v_option where status = 0 and id = "+QString::number(id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
    else
    {
        while(sqlQuery.next())
        {
            haveFind = true;
            option.id = sqlQuery.value("id").toInt();
            option.name = sqlQuery.value("name").toString();
            option.pic = sqlQuery.value("pic").toString();
            option.amount = sqlQuery.value("amount").toInt();
            option.status = sqlQuery.value("status").toInt();
            option.direction = sqlQuery.value("direction").toInt();
            option.food_id = sqlQuery.value("create_id").toInt();
            shelf_time = sqlQuery.value("shelf_time").toInt();
            option.user_id = sqlQuery.value("create_user").toInt();
            option.datetime = sqlQuery.value("datetime").toDateTime();
            option.typeName = sqlQuery.value("typeName").toString();
            option.unitName = sqlQuery.value("unitName").toString();
        }
    }
    if(haveFind)
    {
        haveFind = false;
        int amount = 0;
        QString sql = "SELECT * FROM storage where user_id = "+QString::number(option.user_id)+" and food_id = "+QString::number(option.food_id);
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
        else
        {
            while(sqlQuery.next())
            {
                haveFind = true;
                amount = sqlQuery.value("amount").toInt();
            }
        }
        // 记录本来就存在
        if(haveFind){
            if(option.direction==1)
            {
                amount = amount + option.amount;
            }
            else{
                amount = amount - option.amount;
            }
            if(amount>0)
            {
                storageUpdate(option.user_id,option.food_id,amount);
            }
            else{
                storageDelete(option.user_id,option.food_id);
            }
        }
        else{
            // 插入记录
            if(option.direction==1){
                storageAdd(option.user_id,option.food_id,option.amount);
            }
        }
        if(option.direction==1){
            remindAdd(option.user_id,option.food_id,option.amount,QDate::fromString(option.datetime.addDays(shelf_time).toString("yyyy-MM-dd"),"yyyy-MM-dd"));
        }
        sql = "update option set status = 1 where id = "+QString::number(id);
        qDebug()<<sql;
        if(!sqlQuery.exec(sql))
        {
            qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
        }
    }
}
void SQL::optionCommit(QList<long> ids){
    for (int i = 0;i<ids.size();i++)
    {
        optionCommit(ids.at(i));
    }
}

void SQL::optionUpdate(long id,int amount,int direction){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"id:"<<id;
    qDebug()<<"amount:"<<amount;
    qDebug()<<"direction:"<<direction;
    QString sql = "update option set amount = "+QString::number(amount)+",direction = "+QString::number(direction)+"  where id = "+QString::number(id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }

}
void SQL::optionDelete(long id){
    open();
    QSqlQuery sqlQuery;
    qDebug()<<"id:"<<id;
    QString sql = "delete from option where id = "+QString::number(id);
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}

void SQL::optionDeleteAllCommit(){
    open();
    QSqlQuery sqlQuery;
    QString sql = "delete from option where status = 1";
    qDebug()<<sql;
    if(!sqlQuery.exec(sql))
    {
        qDebug() << "Error: Fail to query table. " << sqlQuery.lastError();
    }
}