#include "historicalrecords.h"
#include "ui_historicalrecords.h"
#include<QMessageBox>
#include<QFileDialog>
#include<QAxObject>
#include<QStandardItemModel>
historicalrecords::historicalrecords(QWidget *parent)
    : QWidget(parent)
    , ui(new Ui::historicalrecords)
{
    ui->setupUi(this);
  this->setWindowFlags(Qt::FramelessWindowHint); // 隐藏标题栏
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->setAlternatingRowColors(true);

    model = new QSqlTableModel(this);
    model->setTable("testing");//打开数据库中名为“”的表
     model->select();

    ui->tableView->setModel(model);
    ui->tableView->show();
    model->setHeaderData(0,Qt::Horizontal,"记录");
    model->setHeaderData(1,Qt::Horizontal,"操作类型");
    model->setHeaderData(3,Qt::Horizontal,"样品名称");
    model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
    model->setHeaderData(4,Qt::Horizontal,"采集地点");
    model->setHeaderData(5,Qt::Horizontal,"采集时间");
    model->setHeaderData(6,Qt::Horizontal,"水样类型");
    model->setHeaderData(7,Qt::Horizontal,"进样量");
    model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
    model->setHeaderData(9,Qt::Horizontal,"开始时间");
    model->setHeaderData(10,Qt::Horizontal,"结束时间");

    ui->pushButton->setFixedSize(150,50);
    ui->pushButton_2->setFixedSize(150,50);
    ui->pushButton_4->setFixedSize(150,50);
    ui->pushButton_3->setFixedSize(150,50);

    ui->lineEdit->setFixedSize(300,50);
     ui->lineEdit_2->setFixedSize(300,50);
     ui->widget->setFixedHeight(60);
}

historicalrecords::~historicalrecords()
{
    delete ui;
}

//查询
void historicalrecords::on_pushButton_clicked()
{

    QString number=ui->lineEdit->text();
    QSqlQuery query;
    QString str=QString("samplename = '%1'").arg(number);
    qDebug() <<str;
        model->setFilter(str);
        model->select();
        model->setHeaderData(0,Qt::Horizontal,"记录");
        model->setHeaderData(1,Qt::Horizontal,"操作类型");
        model->setHeaderData(3,Qt::Horizontal,"样品名称");
        model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
        model->setHeaderData(4,Qt::Horizontal,"采集地点");
        model->setHeaderData(5,Qt::Horizontal,"采集时间");
        model->setHeaderData(6,Qt::Horizontal,"水样类型");
        model->setHeaderData(7,Qt::Horizontal,"进样量");
        model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
        model->setHeaderData(9,Qt::Horizontal,"开始时间");
        model->setHeaderData(10,Qt::Horizontal,"结束时间");




}
void historicalrecords::exportTableViewToExcel(QTableView* tableView) {
    // 创建Excel应用程序对象
    QAxObject* excel = new QAxObject("Excel.Application", nullptr);
    if (!excel) {
        QMessageBox::warning(nullptr, "Error", "Could not create Excel application object.");
        return;
    }

    // 显示Excel应用程序（可选）
    excel->setProperty("Visible", true);

    // 获取工作簿对象
    QAxObject* workbooks = excel->querySubObject("Workbooks");
    QAxObject* workbook = workbooks->querySubObject("Add()");

    // 获取工作表对象
    QAxObject* worksheet = workbook->querySubObject("Worksheets(1)");

    // 获取QTableView的模型
    QAbstractItemModel* model = tableView->model();
    if (!model) {
        QMessageBox::warning(nullptr, "Error", "No model associated with the table view.");
        delete excel;
        return;
    }

    int rowCount = model->rowCount();
    int columnCount = model->columnCount();

    // 将模型数据写入Excel工作表
    for (int row = 2; row <= rowCount; ++row) { // Excel的行和列是从1开始的
        for (int column = 1; column <= columnCount; ++column) {
            QModelIndex index = model->index(row - 1, column - 1); // Qt的行和列是从0开始的
            QVariant value = model->data(index);
            QAxObject* cell = worksheet->querySubObject("Cells(int,int)", row, column);
            cell->setProperty("Value", value.toString());
        }
    }
    model->setHeaderData(0,Qt::Horizontal,"记录");
    model->setHeaderData(1,Qt::Horizontal,"操作类型");
    model->setHeaderData(3,Qt::Horizontal,"样品名称");
    model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
    model->setHeaderData(4,Qt::Horizontal,"采集地点");
    model->setHeaderData(5,Qt::Horizontal,"采集时间");
    model->setHeaderData(6,Qt::Horizontal,"水样类型");
    model->setHeaderData(7,Qt::Horizontal,"进样量");
    model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
    model->setHeaderData(9,Qt::Horizontal,"开始时间");
    model->setHeaderData(10,Qt::Horizontal,"结束时间");




    QAxObject* cell1 = worksheet->querySubObject("Cells(int,int)",1, 1);
    cell1->setProperty("Value", "记录");
    QAxObject* cell3 = worksheet->querySubObject("Cells(int,int)",1, 2);
    cell3->setProperty("Value", "样品名称");
    QAxObject* cell4 = worksheet->querySubObject("Cells(int,int)",1, 3);
    cell4->setProperty("Value", "采集地点");
    QAxObject* cell5 = worksheet->querySubObject("Cells(int,int)",1, 4);
    cell5->setProperty("Value", "采集时间");
    QAxObject* cell6 = worksheet->querySubObject("Cells(int,int)",1, 5);
    cell6->setProperty("Value", "水样类型");
    QAxObject* cell7 = worksheet->querySubObject("Cells(int,int)",1, 6);
    cell7->setProperty("Value", "操作类型");
    QAxObject* cell8 = worksheet->querySubObject("Cells(int,int)",1, 7);
    cell8->setProperty("Value", "进样量");
    QAxObject* cell9 = worksheet->querySubObject("Cells(int,int)",1, 8);
    cell9->setProperty("Value", "洗脱时间");
    QAxObject* cell10 = worksheet->querySubObject("Cells(int,int)",1, 9);
    cell10->setProperty("Value", "开始时间");
    QAxObject* cell11 = worksheet->querySubObject("Cells(int,int)",1, 10);
    cell11->setProperty("Value", "结束时间");
    QAxObject* cell2 = worksheet->querySubObject("Cells(int,int)",1, 11);
    cell2->setProperty("Value", "操作类型");

}
//导出
void historicalrecords::on_pushButton_2_clicked()
{
//    exportTableViewToExcel( ui->tableView);
//    QString desktopPath = QStandardPaths::writableLocation(QStandardPaths::DesktopLocation);
//    QString filePath = QDir(desktopPath).filePath("mydata.csv");
//      QString fileName = QFileDialog::getSaveFileName();
   exportTableViewToCsv("");





}
void historicalrecords::exportTableViewToCsv(const QString &filePat)
{
    QAbstractItemModel *model = ui->tableView->model();
    if (!model) {
        qDebug() << "No model set on the table view.";
        return;
    }
    QString filePath = QFileDialog::getSaveFileName(this, "Export to CSV", "", "CSV Files (*.csv)");

        QFile file(filePath);
        if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
            qDebug() << "Could not open file for writing:" << file.errorString();
            return;
        }

        QTextStream out(&file);
//    QFile file(QFileDialog::getSaveFileName());
//    if (!file.open(QIODevice::WriteOnly | QIODevice::Text)) {
//        qDebug() << "Could not open file for writing:" << file.errorString();
//        return;
//    }

//    QTextStream out(&file);

    // 写入表头（可选）
    int columnCount = model->columnCount();
    for (int col = 0; col < columnCount; ++col) {
        out << model->headerData(col, Qt::Horizontal, Qt::DisplayRole).toString();
        if (col < columnCount - 1) {
            out << ",";
        }
    }
    out << "\n";

    // 写入数据行
    int rowCount = model->rowCount();
    for (int row = 0; row < rowCount; ++row) {
        for (int col = 0; col < columnCount; ++col) {
            QVariant data = model->data(model->index(row, col), Qt::DisplayRole);
            out << data.toString().replace(",", ";"); // 替换逗号以避免CSV格式问题
            if (col < columnCount - 1) {
                out << ",";
            }
        }
        out << "\n";
    }

    file.close();
    qDebug() << "Data exported to CSV successfully.";
}
QString historicalrecords::getDesktopPath()
{
    return QStandardPaths::writableLocation(QStandardPaths::DesktopLocation);
}


//刷新
void historicalrecords::on_pushButton_3_clicked()
{
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
    ui->tableView->setSelectionMode(QAbstractItemView::SingleSelection);
    ui->tableView->setAlternatingRowColors(true);
    // model = new QSqlQueryModel;
    model = new QSqlTableModel(this);
    model->setTable("testing");//打开数据库中名为“”的表
    model->select();
    ui->tableView->setModel(model);
    ui->tableView->show();

    model->setHeaderData(0,Qt::Horizontal,"记录");
    model->setHeaderData(1,Qt::Horizontal,"操作类型");
    model->setHeaderData(3,Qt::Horizontal,"样品名称");
    model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
    model->setHeaderData(4,Qt::Horizontal,"采集地点");
    model->setHeaderData(5,Qt::Horizontal,"采集时间");
    model->setHeaderData(6,Qt::Horizontal,"水样类型");
    model->setHeaderData(7,Qt::Horizontal,"进样量");
    model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
    model->setHeaderData(9,Qt::Horizontal,"开始时间");
    model->setHeaderData(10,Qt::Horizontal,"结束时间");


}


//查询
void historicalrecords::on_pushButton_4_clicked()
{

    QString number=ui->lineEdit_2->text();
    QSqlQuery query;
    QString str=QString("SELECT * FROM testing WHERE time like '%%1%'").arg(number);
    qDebug() <<str;
    query.prepare(str);
   if (query.exec()) { // 确保查询执行成功
       QSqlQueryModel *model = new QSqlQueryModel;
       model->setQuery(query); // 先设置查询，这样模型就会包含查询结果
       ui->tableView->setModel(model);
       ui->tableView->show(); // 显示视图，此时视图将显示查询结果
       model->setHeaderData(0,Qt::Horizontal,"记录");
       model->setHeaderData(1,Qt::Horizontal,"操作类型");
       model->setHeaderData(3,Qt::Horizontal,"样品名称");
       model->setHeaderData(2,Qt::Horizontal,"细菌/病毒");
       model->setHeaderData(4,Qt::Horizontal,"采集地点");
       model->setHeaderData(5,Qt::Horizontal,"采集时间");
       model->setHeaderData(6,Qt::Horizontal,"水样类型");
       model->setHeaderData(7,Qt::Horizontal,"进样量");
       model->setHeaderData(8,Qt::Horizontal,"洗脱时间");
       model->setHeaderData(9,Qt::Horizontal,"开始时间");
       model->setHeaderData(10,Qt::Horizontal,"结束时间");

   } else {

       qDebug() << "Query failed:" << query.lastError().text();
   }
}