#include "databasefunction.h"
#include<QDebug>
/*
 * Date:        2022/11/17
 * Name:        王宇轩
 * Student ID:  20301142
 * Description: 这个类支持用户客户端各界面中,各种有关数据库的操作。
*/
DatabaseFunction::DatabaseFunction()
{

}

//通过车次号查询列车信息
QSqlQuery DatabaseFunction::searchInfoByTrainNum(const QString& trainNum)
{
    sql=QString("select trainNum,startStation,endStation,startTime,endTime"
                " from traininfo where trainNum='%1'").arg(trainNum);
    result.exec(sql);
    return result;
}
//通过车次号查询列车途径站信息
QSqlQuery DatabaseFunction::searchSubInfoByTrainNum(const QString &trainNum)
{
    sql=QString("select trainNum,startStation,endStation,startTime,endTime"
                " from traininfo where trainNum like '%1-%'").arg(trainNum);
    result.exec(sql);
    return result;
}

//根据起始点和终止点查询列车信息
QSqlQuery DatabaseFunction::searchInfoByStation(const QString &start, const QString &end)
{
    sql=QString("select trainNum,startStation,endStation,startTime,endTime"
                " from traininfo where startStation='%1' and endStation='%2'").arg(start,end);
    result.exec(sql);
    return result;
}

//购票界面根据列车号码查询信息
QSqlQuery DatabaseFunction::searchAllInfoByTrainNum(const QString &trainNum)
{
    sql=QString("select * from traininfo where trainNum like '%1%'").arg(trainNum);
    result.exec(sql);
    return result;
}

//购票界面根据起始和终点站查询信息
QSqlQuery DatabaseFunction::searchAllInfoByStation(const QString &start, const QString &end)
{
    sql=QString("select * from traininfo where startStation='%1' and endStation='%2'").arg(start,end);
    result.exec(sql);
    return result;
}

// 购票界面，根据列车号获取经停站和对应的详细信息
QSqlQuery DatabaseFunction::getBuyInfo(const QString &trainNum)
{

    sql=QString("select * from traininfo where trainNum like '%1%'").arg(trainNum);
    result.exec(sql);
    return result;
}

// 订单查询界面，查看已经支付过的订单
QSqlQuery DatabaseFunction::getPayOrdering(const QString & username)
{
    //根据用户名查询到用户的身份证号，借此查询ordering表
    sql=QString("select identifyNum from user where username='%1'").arg(username);
    result.exec(sql);
    result.next();
    QString idCard=result.value(0).toString();

    sql=QString("select id,trainNum,name,startStation,startTime,endStation,endTime,isPay from ordering"
                " where idCard='%1' and isPay not like '0'").arg(idCard);
    result.exec(sql);

    return result;
}

// 未支付订单查询界面
QSqlQuery DatabaseFunction::getUnpayOrdering(const QString & username)
{
    //根据用户名查询到用户的身份证号，借此查询ordering表
    sql=QString("select identifyNum from user where username='%1'").arg(username);
    result.exec(sql);
    result.next();
    QString idCard=result.value(0).toString();

    sql=QString("select id,trainNum,name,startStation,startTime,endStation,endTime,isPay from ordering"
                " where idCard='%1' and isPay = '0'").arg(idCard);
    result.exec(sql);
    return result;
}

// 获取用户的个人信息
QSqlQuery DatabaseFunction::getPersonalInfo(const QString &username)
{
    sql=QString("select username,phoneNum,sex,name,identifyNum from user where username='%1'").arg(username);
    result.exec(sql);
    return result;
}

// 获取指定编号订单的价格
QString DatabaseFunction::getPrice(const QString &id)
{
    sql=QString("select price from ordering where id='%1'").arg(id);
    result.exec(sql);
    result.next();
    return result.value(0).toString();

}

// 获取所有的列车线路
QSqlQuery DatabaseFunction::getTrainAndIncome()
{
    sql=QString("select * from totalProfit");
    result.exec(sql);
    return result;
}

// 删除选中的未支付订单
bool DatabaseFunction::delUnpayOrdering(const QString & id)
{
    sql=QString("delete from ordering where id='%1'").arg(id);
    if(result.exec(sql)){
        return true;
    }
    return false;

}

// 支付未被支付的订单
bool DatabaseFunction::payUnpayOrdering(const QString &id)
{
    sql=QString("update ordering set isPay='1' where id='%1'").arg(id);
    if(result.exec(sql)){
        return true;
    }
    return false;
}

// 退票操作
bool DatabaseFunction::returnOrdering(const QString &id)
{
    sql=QString("delete from ordering where id='%1'").arg(id);
    if(result.exec(sql)){
        return true;
    }
    return false;
}

bool DatabaseFunction::updatePersonalInfo(const QString &username, const QString &phoneNum, const QString &name, const QString &isCard, const QString &sex)
{
    sql=QString("update user set username='%1',phoneNum='%2',sex='%3',name='%4',identifyNum='%5'"
                " where username='%1'").arg(username,phoneNum,sex,name,isCard);
    if(result.exec(sql)){
        return true;
    }
    return false;

}

// 判断用户信息是否完整，若不完整则限制购票。
bool DatabaseFunction::isInfoComplete(const QString &username)
{
    sql=QString("select phoneNum,name,identifyNum from user where username='%1'").arg(username);
    result.exec(sql);
    if(result.next()){
        if(result.value(0).toString().isNull()||result.value(1).toString().isNull()||result.value(2).toString().isNull()){
            return false;
        }
    }
    return true;
}

// 将当前购票的交易额写入数据库
bool DatabaseFunction::writeIncomeDB(const QString &trainNum, const int &income)
{
    // 查询当前数据库中是否存在当前列车的信息。
    sql=QString("select * from totalProfit where trainNum='%1'").arg(trainNum);
    result.exec(sql);
    // 如果不存在当前信息，就新插入
    if(!result.next()){
        sql=QString("insert into totalProfit(trainNum,income) values('%1','%2')").arg(trainNum,QString::number(income));
    }else{
        // 如果存在当前信息，则增加总额度后进行更新
        sql=QString("select income from totalProfit where trainNum='%1'").arg(trainNum);
        result.exec(sql);
        result.next();
        int tempIncome=income+result.value(0).toInt();
        sql=QString("update totalProfit set income='%1' where trainNum='%2'").arg(QString::number(tempIncome),trainNum);
    }
    return result.exec(sql);
}

// 用户退票，将传入订单的收入从总收入中减去
bool DatabaseFunction::desIncomeDB(const QString &trainNum, const int &income)
{
    // 如果存在当前信息，则增加总额度后进行更新
    sql=QString("select income from totalProfit where trainNum='%1'").arg(trainNum);
    result.exec(sql);
    result.next();
    int tempIncome=result.value(0).toInt()-income;

    sql=QString("update totalProfit set income='%1' where trainNum='%2'").arg(QString::number(tempIncome),trainNum);
    return result.exec(sql);
}

// 购票时判断是否还有余票
bool DatabaseFunction::isTicketsEnough(const QString &trainNum, const QString &start, const QString &end)
{
    sql=QString("select startStation,endStation,leftTickets from traininfo where trainNum like '%1-%'").arg(trainNum);
    result.exec(sql);
    bool startFlag=false;
    int minNum=2000;//假定一个初始余票
    while(result.next()){
        if(result.value(0).toString()==start){
            startFlag=true;
        }
        if(startFlag){
           if (result.value(2).toInt()<minNum){
               minNum=result.value(2).toInt();
           }
           // 如果到了本张票的终点站，则终止后续操作
           if(result.value(1).toString()==end){
               break;
           }
        }
    }
    return minNum>0?true:false;
}

// 用户退票时，将归还的余票添加到数据库中
void DatabaseFunction::returnLeftTickets(const QString &trainNum, const QString &start, const QString &end)
{
    sql=QString("select startStation,endStation,leftTickets from traininfo where trainNum like '%1-%'").arg(trainNum);
    result.exec(sql);
    QString tempSQL;//记录临时的sql语句
    QSqlQuery sqlQuery;//临时的执行sql
    QString leftTickets;//退票后新的剩余票数
    bool addFlag=false;//判断当前余票是否+1
    while(result.next()){
        if(result.value(0).toString()==start){
            addFlag=true;
        }
        if(addFlag){
           QString tempStart=result.value(0).toString();
           QString tempEnd=result.value(1).toString();
           leftTickets=QString::number(result.value(2).toInt()+1);
           tempSQL=QString("update traininfo set leftTickets='%1' "
                           "where trainNum like '%2%' and startStation = '%3' "
                           "and endStation='%4'")
                      .arg(leftTickets,trainNum,tempStart,tempEnd);
           sqlQuery.exec(tempSQL);
           // 如果到了本张票的终点站，则终止后续操作
           if(result.value(1).toString()==end){
               break;
           }
        }
    }
}

// 用户购票后将数据库中的余票-1
void DatabaseFunction::minusLeftTickets(const QString &trainNum, const QString &start, const QString &end)
{
    sql=QString("select startStation,endStation,leftTickets from traininfo where trainNum like '%1-%'").arg(trainNum);
    result.exec(sql);
    QString tempSQL;//记录临时的sql语句
    QSqlQuery sqlQuery;//临时的执行sql
    QString leftTickets;//退票后新的剩余票数
    bool minusFlag=false;//判断当前余票是否-1
    while(result.next()){
        if(result.value(0).toString()==start){
            minusFlag=true;
        }
        if(minusFlag){
           QString tempStart=result.value(0).toString();
           QString tempEnd=result.value(1).toString();
           leftTickets=QString::number(result.value(2).toInt()-1);
           tempSQL=QString("update traininfo set leftTickets='%1' "
                           "where trainNum like '%2%' and startStation = '%3' "
                           "and endStation='%4'")
                      .arg(leftTickets,trainNum,tempStart,tempEnd);
           sqlQuery.exec(tempSQL);
           // 如果到了本张票的终点站，则终止后续操作
           if(result.value(1).toString()==end){
               break;
           }
        }
    }
}