#include "zhong.h"
#include "ui_zhong.h"
#include<QMessageBox>
#include<QStandardItem>
#include <QAxObject>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QSqlError>
#include <QDebug>
#include<QFileDialog>
QString id4;
zhong::zhong(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::zhong)
{
    ui->setupUi(this);
    getshui();
    m_addzhong=new addzhong;
    model = new QSqlTableModel(this);
    shuaxin();
    connect(m_addzhong,&addzhong::xinzeng,this,[=](){shuaxin();});

    ui->lineEdit_3->setFixedSize(157,25);
    ui->lineEdit_4->setFixedSize(157,25);
    ui->frame->setFixedSize(201,37);
    ui->frame_2->setFixedSize(201,37);

    ui->comboBox_2->setFixedSize(201,37);
    ui->pushButton_2->setFixedSize(76,37);
    ui->pushButton_3->setFixedSize(76,37);
    ui->pushButton_4->setFixedSize(76,37);
    ui->comboBox->setFixedSize(201,37);
    ui->tableView->setShowGrid(false);
    ui-> tableView->verticalHeader()->setVisible(false);
    ui->tableView->setFixedWidth(1843);
    ui->tableView->setVerticalScrollBarPolicy(Qt::ScrollBarAlwaysOff);
      ui->tableView->setItemDelegateForColumn(6, delegate2);


    connect(delegate2, &ButtonDelegate1::deleteButtonClicked, this, &zhong::shanchu);

    for (int i=0;i<=model->rowCount() ;i++ ) {
        ui->tableView->setRowHeight(i,50);

    }

    ui->tableView->setHorizontalScrollBarPolicy(Qt::ScrollBarAlwaysOff);
    for (int i=1;i<=7 ;i++ ) {
        ui->tableView->setColumnWidth(i,298);

    }
    ui->tableView->setColumnWidth(0,50);

    CenteredItemDelegate4* delegate = new CenteredItemDelegate4();
    ui->tableView->setItemDelegate(delegate);
    ui->tableView->horizontalHeader()->setFixedHeight(40);
    m_date=new date;
    connect(m_date,SIGNAL(kaishi(QString)), this,  SLOT(getkaishi(QString)));
    m_datee=new datee;
    connect(m_datee,SIGNAL(kaishi(QString)), this,  SLOT(getkaishii(QString)));

}


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

void zhong::getkaishi(QString str)
{

    ui->lineEdit_4->setText(str);

}
void zhong::getkaishii(QString str)
{

    ui->lineEdit_3->setText(str);

}
void zhong::bianji(const QModelIndex& index)
{
    ui->tableView->setEditTriggers(QAbstractItemView::AllEditTriggers);
    qDebug() << "编辑按钮被点击，行: " << index.row() << " 列: " << index.column();
}

void zhong::shanchu(const QModelIndex& index)
{
    int result = QMessageBox::information(NULL, "提示", "确定要删除本行内容吗", QMessageBox::Ok | QMessageBox::Cancel);
    if (result == QMessageBox::Ok) {
        qDebug() << "删除按钮被点击，行: " << index.row() << " 列: " << index.column();
        if (model->removeRow(index.row())) {
            if (model->submitAll()) {
                shuaxin();  // 重置视图，强制刷新界面显示

            } else {

            }
        } else {

        }
    }
}
void zhong::getshui()
{
    ui->comboBox->clear();

    QString queryString = QString("SELECT name FROM junshu");
    QSqlQuery mquery;
    if (!mquery.exec(queryString)) {


    }
    while (mquery.next()) {
        QVariant value = mquery.value(0); // 假设要获取的列是查询结果的第一列
        ui->comboBox->addItem(value.toString());

    }


}
//查询
void zhong::on_pushButton_2_clicked()
{

    QString name = ui->comboBox_2->currentText();

    QString number4 = ui->comboBox->currentText();
    QString startTime = ui->lineEdit_4->text(); // 开始时间
    QString endTime = ui->lineEdit_3->text();   // 结束时间

    QSqlQuery query;
    QString str;

    if (ui->lineEdit_4->text() == "" || ui->lineEdit_3->text() == "") {
        // 如果时间输入框为空，执行原来的查询逻辑
        str = QString("SELECT * FROM junzhong WHERE name LIKE '%1%' AND shuname = '%2'")
                  .arg(name)

                  .arg(number4);
    } else {
        // 如果时间输入框不为空，增加时间段内的检索条件
        str = QString("SELECT * FROM junzhong WHERE name LIKE '%1%' AND time BETWEEN '%2' AND '%3' AND shuname = '%4'")
                  .arg(name)
                  .arg(startTime)
                  .arg(endTime)
                  .arg(number4);
    }

    qDebug() << str; // 输出生成的 SQL 语句，方便调试

    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(2, Qt::Horizontal, "菌属名称");
        model->setHeaderData(3, Qt::Horizontal, "菌种名称");
        model->setHeaderData(4, Qt::Horizontal, "创建时间");
        model->setHeaderData(5, Qt::Horizontal, "创建人员");
        model->setHeaderData(6, Qt::Horizontal, "操作");

        // 设置行高
        for (int i = 0; i < model->rowCount(); i++) {
            ui->tableView->setRowHeight(i, 50);
        }
    } else {
        qDebug() << "Query failed:" << query.lastError().text();
    }
}
void zhong::shuaxin()
{

    model->setTable("junzhong");//打开数据库中名为“”的表
    ui->tableView->setModel(model);
    model->select();
    ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);
    ui->tableView->setSelectionBehavior(QAbstractItemView::SelectItems);
    ui->tableView->setSelectionMode(QAbstractItemView::MultiSelection);
    ui->tableView->setAlternatingRowColors(true);
    mask_window.close();



    model->setHeaderData(0,Qt::Horizontal,"序号");
    model->setHeaderData(1,Qt::Horizontal,"项目名称");
    model->setHeaderData(2,Qt::Horizontal,"菌属名称");
    model->setHeaderData(3,Qt::Horizontal,"菌种名称");
    model->setHeaderData(4,Qt::Horizontal,"创建时间");
    model->setHeaderData(5,Qt::Horizontal,"创建人员");
    model->setHeaderData(6,Qt::Horizontal,"操作");
    for (int i=0;i<=model->rowCount() ;i++ ) {
         ui->tableView->setRowHeight(i,50);

    }

    for (int i=1;i<=7 ;i++ ) {
        ui->tableView->setColumnWidth(i,298);

    }

ui->tableView->setColumnWidth(0,50);

    ui->tableView->show();
}
//重置
void zhong::on_pushButton_3_clicked()
{
    model->submitAll();


        ui->tableView->setEditTriggers(QAbstractItemView::NoEditTriggers);


    shuaxin();
}

//新增
void zhong::on_pushButton_4_clicked()
{
    // mask_window.setWindowOpacity(0.2);

    // //设置窗体的背景色,这里的百分比就是透明度
    // mask_window.setStyleSheet("background-color: black;");
    // mask_window.setGeometry(0, 0, 1920,1080); //获取父窗体的几何形状设置当前窗口
    // mask_window.setWindowFlags(Qt::FramelessWindowHint); // 隐藏标题栏
    // //对话框
    // mask_window.show();
    // m_addzhong->setWindowModality(Qt::ApplicationModal);
   // m_addzhong->getshui();
//m_addzhong->show();
    // 弹出文件选择对话框，让用户选择 Excel 文件
    QString filePath = QFileDialog::getOpenFileName(this, tr("选择 Excel 文件"), "", tr("Excel 文件 (*.xlsx *.xls)"));
    if (filePath.isEmpty()) {
        qDebug() << "用户取消了文件选择";
        return;
    }

    // 检查文件是否存在
    if (!QFile::exists(filePath)) {
        qDebug() << "Error: Excel 文件不存在:" << filePath;
        return;
    }

    QAxObject excel("Excel.Application");
    excel.setProperty("Visible", false); // 不显示 Excel 界面

    // 打开 Excel 文件
    QAxObject *workbooks = excel.querySubObject("Workbooks");
    QAxObject *workbook = workbooks->querySubObject("Open(const QString&)", filePath);
    if (!workbook) {
        qDebug() << "Error: 无法打开 Excel 文件:" << filePath;
        excel.dynamicCall("Quit()");
        return;
    }

    QAxObject *worksheet = workbook->querySubObject("Worksheets(int)", 1); // 获取第一个工作表

    QSqlQuery query;

    // 获取 Excel 表格的行数和列数
    QAxObject *usedRange = worksheet->querySubObject("UsedRange");
    QAxObject *rows = usedRange->querySubObject("Rows");
    QAxObject *columns = usedRange->querySubObject("Columns");
    int rowCount = rows->property("Count").toInt();
    int colCount = columns->property("Count").toInt();


    for (int row = 1; row <= rowCount; ++row) {

        QAxObject *cell2 = worksheet->querySubObject("Cells(int,int)", row, 2);
        QAxObject *cell3 = worksheet->querySubObject("Cells(int,int)", row, 3);
        QAxObject *cell4 = worksheet->querySubObject("Cells(int,int)", row, 4);
        QVariant col2Variant = cell2? cell2->property("Text") : QVariant();
        QVariant col3Variant = cell3? cell3->property("Text") : QVariant();
        QVariant col4Variant = cell4? cell4->property("Text") : QVariant();

        qDebug() << "Row" << row << "Data Types:" << col2Variant.typeName() << col3Variant.typeName() << col4Variant.typeName();
        qDebug() << "Row" << row << "Data Values:"  << col2Variant << col3Variant << col4Variant;


        QString col2 = col2Variant.toString();
        QString col3 = col3Variant.toString();
        QString col4 = col4Variant.toString();
        // 插入数据到 SQLite 数据库
        QDateTime dateTime= QDateTime::currentDateTime();//获取系统当前的时间
        QString str = dateTime .toString("yyyy-MM-dd hh:mm:ss");//格式化时间
        query.prepare("INSERT INTO junzhong ( id, shuname, name,ren,time) VALUES ( :id, :shuname, :name,:ren,:time)");

        query.bindValue(":id", col2);
        query.bindValue(":shuname", col3);
        query.bindValue(":name", col4);
        query.bindValue(":ren", id4);
        query.bindValue(":time", str);

        if (!query.exec()) {
            qDebug() << "Error: 无法插入数据:" << query.lastError().text();
        }
    }

    // 关闭 Excel 文件和应用程序
    workbook->dynamicCall("Close()");
    excel.dynamicCall("Quit()");
    QMessageBox::information(NULL, "提示", "数据导入成功。");

    shuaxin();
}

void zhong::on_comboBox_currentTextChanged(const QString &arg1)
{
    ui->comboBox_2->clear();
    QString sql=QString("select name from junzhong where shuname='%1'").arg(arg1);
    qDebug()<<sql;


    QSqlQuery mquery;
    if (!mquery.exec(sql)) {


    }
    while (mquery.next()) {
        QVariant value = mquery.value(0);
        ui->comboBox_2->addItem(value.toString());

    }
}


void zhong::on_pushButton_5_clicked()
{
    m_date->move(650,230);
    m_date->exec();
}


void zhong::on_pushButton_6_clicked()
{
    m_datee->move(880,230);
    m_datee->exec();
}