#include "operationlistform.h"
#include "ui_operationlistform.h"
#include "qcustomcalendarwidget.h"
#include "QDateTimeEdit"
#include "QTableView"
#include "tablewidget.h"
#include "QTextCodec"
#include <QSqlDatabase>
#include <QSqlError>
#include <QDebug>
#include <QSqlQuery>
#include <QSqlRecord>
#include <QMessageBox>
#include <QTextCodec>
#include <QAxObject>
#include <QFileDialog>
#include <QDir>
static QSqlDatabase db;
static QDateTimeEdit *dateEdit2,*dateEdit0;
static TableWidget *tableWidget;
OperationListForm::OperationListForm(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::OperationListForm)
{
    ui->setupUi(this);
    QDateTimeEdit *dateEdit = new QDateTimeEdit(QDate::currentDate().addDays(-30), this);
    QDateTimeEdit *dateEdit1 = new QDateTimeEdit(QDate::currentDate(), this);
    dateEdit->setMinimumSize(150,32);
    dateEdit->setMaximumSize(200,32);
    dateEdit1->setMinimumSize(150,32);
    dateEdit1->setMaximumSize(200,32);
    dateEdit2 = dateEdit1;
    dateEdit0 = dateEdit;
    connect(dateEdit,SIGNAL(dateChanged(QDate)),this,SLOT(updateLastDate(QDate)));

    QCustomCalendarWidget* calendarWidget = new QCustomCalendarWidget(this);
    dateEdit->setCalendarPopup(true);  // 日历弹出
    dateEdit->setCalendarWidget(calendarWidget);
    dateEdit->setContextMenuPolicy(Qt::NoContextMenu);
    dateEdit->setFixedSize(150, 26);
    dateEdit->setSizePolicy(QSizePolicy::Fixed, QSizePolicy::Fixed);
    calendarWidget->disconnect(SIGNAL(selectionChanged()));
    calendarWidget->disconnect(SIGNAL(clicked(QDate)));
    connect(calendarWidget, &QCustomCalendarWidget::signalSetCalendarTime, [dateEdit](const QDate& data)
    {
            dateEdit->setDate(data);
    });
    QFont decorationFont("Microsoft Yahei", 11);
    dateEdit->setFont(decorationFont);
    dateEdit1->setFont(decorationFont);
    ui->timeLayout->addWidget(dateEdit,0,0);
    ui->timeLayout->addWidget(ui->to,0,1);

    QCustomCalendarWidget* calendarWidget1 = new QCustomCalendarWidget(this);
    dateEdit1->setCalendarPopup(true);  // 日历弹出
    dateEdit1->setCalendarWidget(calendarWidget1);
    dateEdit1->setMinimumDate(dateEdit->date());
    dateEdit1->setContextMenuPolicy(Qt::NoContextMenu);
    dateEdit1->setFixedSize(150, 26);
    dateEdit1->setSizePolicy(QSizePolicy::Fixed, QSizePolicy::Fixed);

    calendarWidget1->disconnect(SIGNAL(selectionChanged()));
    calendarWidget1->disconnect(SIGNAL(clicked(QDate)));

    connect(calendarWidget1, &QCustomCalendarWidget::signalSetCalendarTime, [dateEdit1](const QDate& data){
            dateEdit1->setDate(data);
    });
    dateEdit1->setFont(decorationFont);
    ui->timeLayout->addWidget(dateEdit1,0,2);

    QStringList header;
    QTextCodec::setCodecForLocale(QTextCodec::codecForName("GB2312"));

    header<<QString::fromLocal8Bit("模块")<<QString::fromLocal8Bit("操作名称")
         <<QString::fromLocal8Bit("操作人")<<QString::fromLocal8Bit("操作时间")
        <<QString::fromLocal8Bit("操 作");
    tableWidget = new TableWidget(this);
    tableWidget->set_title(header);
    tableWidget->set_columnWidth(1,150);
    tableWidget->set_columnWidth(3,200);
    tableWidget->set_columnWidth(0,150);
    tableWidget->set_columnWidth(2,150);
    QVector<QString> vector;
    QVector<QVector<QString> > curr_data;
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("brtyx");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("123456");
    bool ok = db.open();
    if(ok){
        QSqlQuery query(QString("select * from operation where operationtime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(dateEdit0->text()).arg(dateEdit2->text()));
        QSqlRecord rec = query.record();
        while(query.next())
        {
            vector.clear();
            vector.append(query.value("part").toString());
            vector.append(query.value("name").toString());
            vector.append(query.value("username").toString());
            vector.append(query.value("operationtime").toString());
            vector.append(query.value("operation").toString());
            curr_data.append(vector);
        }
    }
    tableWidget->curr_page = 0;
    tableWidget->updata_data(curr_data);
    ui->datalist->addWidget(tableWidget);
}

OperationListForm::~OperationListForm()
{
    delete ui;
}
void OperationListForm::updateLastDate(QDate date){
    dateEdit2->setMinimumDate(date);
}

void OperationListForm::on_search_clicked()
{
    QVector<QString> vector;
    QVector<QVector<QString> > curr_data;
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("brtyx");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("123456");
    bool ok = db.open();
    if(ok){
        QString str;
        if(ui->textEdit->toPlainText()==""){
            str = QString("select * from operation where operationtime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(dateEdit0->text()).arg(dateEdit2->text());
        }else {
            str = QString("select * from operation where operationtime BETWEEN '%1 00:00:00' AND '%2 23:59:59' and (username like '%%3%' or name like '%%3%') ")
                    .arg(dateEdit0->text()).arg(dateEdit2->text()).arg(ui->textEdit->toPlainText());
        }
        QSqlQuery query(str);
        QSqlRecord rec = query.record();
        while(query.next())
        {
            vector.clear();
            vector.append(query.value("part").toString());
            vector.append(query.value("name").toString());
            vector.append(query.value("username").toString());
            vector.append(query.value("operationtime").toString());
            vector.append(query.value("operation").toString());
            curr_data.append(vector);
        }
    }
    tableWidget->curr_page = 0;
    tableWidget->updata_data(curr_data);
}

void OperationListForm::on_export_data_clicked()
{
    QVector<QString> vector;
    QVector<QVector<QString> > curr_data;
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setDatabaseName("brtyx");
    db.setPort(3306);
    db.setUserName("root");
    db.setPassword("123456");
    bool ok = db.open();
    if(ok){
        QString str;
        if(ui->textEdit->toPlainText()==""){
            str = QString("select * from operation where operationtime BETWEEN '%1 00:00:00' AND '%2 23:59:59'").arg(dateEdit0->text()).arg(dateEdit2->text());
        }else {
            str = QString("select * from operation where operationtime BETWEEN '%1 00:00:00' AND '%2 23:59:59' and (username like '%%3%' or name like '%%3%') ")
                    .arg(dateEdit0->text()).arg(dateEdit2->text()).arg(ui->textEdit->toPlainText());
        }
        QSqlQuery query(str);
        QSqlRecord rec = query.record();
        while(query.next())
        {
            vector.clear();
            vector.append(query.value("part").toString());
            vector.append(query.value("name").toString());
            vector.append(query.value("username").toString());
            vector.append(query.value("operationtime").toString());
            vector.append(query.value("operation").toString());
            curr_data.append(vector);
        }
    }
    QStringList header;
    QTextCodec::setCodecForLocale(QTextCodec::codecForName("GB2312"));

    header<<QString::fromLocal8Bit("模块")<<QString::fromLocal8Bit("操作名称")
         <<QString::fromLocal8Bit("操作人")<<QString::fromLocal8Bit("操作时间")
        <<QString::fromLocal8Bit("操 作");
    QString filePath = QFileDialog::getSaveFileName(this, "Save Data", "untitle",
           "Microsoft Excel 2013(*.xlsx)");
   if (!filePath.isEmpty())
   {
       QAxObject *excel = new QAxObject(this);
       excel->setControl("Excel.Application");
       excel->dynamicCall("SetVisible(bool Visible)", false);
       excel->setProperty("DisplayAlerts", false);
       QAxObject *workbooks = excel->querySubObject("WorkBooks");
       workbooks->dynamicCall("Add");
       QAxObject *workbook = excel->querySubObject("ActiveWorkBook");
       QAxObject *worksheets = workbook->querySubObject("Sheets");
       QAxObject *worksheet = worksheets->querySubObject("Item(int)", 1);
       for (int j = 1; j < 6; ++j)
       {
           QAxObject *Range = worksheet->querySubObject("Cells(int,int)", 1, j);
           Range->dynamicCall("SetValue(const QString &)", header[j-1]);
       }
       for (int i = 1; i < curr_data.count()+1; ++i)
       {
           for (int j = 1; j < 6; ++j)
           {
               QAxObject *Range = worksheet->querySubObject("Cells(int,int)", i+1, j);
               Range->dynamicCall("SetValue(const QString &)", curr_data[i-1][j-1]);
           }
       }
       workbook->dynamicCall("SaveAs(const QString &)", QDir::toNativeSeparators(filePath));
       if (excel != NULL)
       {
       excel->dynamicCall("Quit()");
       delete excel;
       excel = NULL;
        }
        QMessageBox::information(this, QStringLiteral("提示"), QStringLiteral("导出成功！"));
   }
}