#include "searchinfo.h"
|
#include "ui_searchinfo.h"
|
#include <QAxObject>
|
#include <QDebug>
|
#include <QDir>
|
#include <QSqlError>
|
#include <QSqlQuery>
|
#include <QMessageBox>
|
#include <QFileDialog>
|
#include <QCalendarWidget>
|
|
SearchInfo::SearchInfo(QWidget *parent) :
|
QMainWindow(parent),
|
ui(new Ui::SearchInfo)
|
{
|
ui->setupUi(this);
|
// ui->label_2->hide();
|
// ui->label_3->hide();
|
// ui->dateTimeEdit->hide();
|
// ui->dateTimeEdit_2->hide();
|
ui->dateEdit_begin->calendarWidget()->setStyleSheet("background-color:#deab8a");
|
ui->dateEdit_end->calendarWidget()->setStyleSheet("background-color:pink");
|
ui->dateEdit_end->setDate(QDate::currentDate());
|
|
m_model = new QStandardItemModel(this);
|
ui->tableView->setModel(m_model);
|
ui->tableView->verticalHeader()->setVisible(false); // 隐藏行头
|
m_model->setColumnCount(5);
|
QStringList labels;
|
labels<<"ID"<<"指标名称"<<"代码"<<"数量"<<"公司编号"<<"审计时间";
|
m_model->setHorizontalHeaderLabels(labels);
|
if(QSqlDatabase::contains("qt_sql_default_connection")){
|
m_db = QSqlDatabase::addDatabase("qt_sql_default_connection");
|
}else{
|
m_db = QSqlDatabase::addDatabase("QSQLITE");
|
}
|
m_db.setDatabaseName("sqlData.db");
|
if(m_db.open()){
|
qDebug()<<"db open ok";
|
createSqlite();
|
//m_db.close();
|
searchComName(); // 查询公司名
|
}else{
|
qDebug()<<"db open fail";
|
}
|
// 设置表格列标签
|
setLabels();
|
|
}
|
|
SearchInfo::~SearchInfo()
|
{
|
m_db.close();
|
delete ui;
|
}
|
|
void SearchInfo::createSqlite()
|
{
|
// 建表
|
QString sql = "create table Internal_1 (id int primary key, name varchar(30), _020100 int)";
|
QSqlQuery query;
|
if(query.exec(sql))
|
{
|
qDebug()<<"create Internal_1 ok...";
|
}else{
|
qDebug()<<"create Internal_1 fail..."<<query.lastError().text();
|
}
|
|
sql = "CREATE TABLE file_up_info (\
|
id INTEGER PRIMARY KEY AUTOINCREMENT,\
|
name VARCHAR (30),\
|
code TEXT,\
|
up_time TEXT\
|
)";
|
if(query.exec(sql))
|
{
|
qDebug()<<"create file_up_info ok...";
|
}else{
|
qDebug()<<"create file_up_info fail..."<<query.lastError().text();
|
}
|
|
sql = "CREATE TABLE company_info (\
|
id INTEGER PRIMARY KEY AUTOINCREMENT,\
|
name VARCHAR (30),\
|
code VARCHAR (30),\
|
机构类型 TEXT,\
|
主要业务活动 TEXT,\
|
行业代码 TEXT,\
|
单位注册地及区域 TEXT,\
|
区域代码 TEXT,\
|
城乡代码 TEXT,\
|
单位规模 TEXT,\
|
从业期末人数 TEXT,\
|
法定代表人 TEXT,\
|
执行会记标准类别 TEXT,\
|
长途区号 TEXT,\
|
固定电话 TEXT,\
|
邮政编码 TEXT,\
|
电子邮箱 TEXT,\
|
网址 TEXT\
|
)";
|
if(query.exec(sql))
|
{
|
qDebug()<<"create company_info ok...";
|
}else{
|
qDebug()<<"create company_info fail..."<<query.lastError().text();
|
}
|
|
// 审计项目表
|
createTableNoEvidences("audit_project");
|
m_itemAndTable["审计项目"] = "audit_project";
|
|
// 内审工作量表
|
createTableNoEvidences("audit_workload");
|
m_itemAndTable["内审工作量"] = "audit_workload";
|
|
// 问题金额表
|
createTableHasEvidences("problem_money");
|
m_itemAndTable["问题金额"] = "problem_money";
|
|
// 问题个数表
|
createTableHasEvidences("problem_count");
|
m_itemAndTable["问题个数"] = "problem_count";
|
// 问题整改表
|
createTableHasEvidences("problem_rectification");
|
m_itemAndTable["问题整改"] = "problem_rectification";
|
|
// 问题整改--非金额表
|
createTableHasEvidences("problem_no_money");
|
m_itemAndTable["问题整改--非金额"] = "problem_no_money";
|
|
// 处分表
|
createTableNoEvidences("punish");
|
m_itemAndTable["处分"] = "punish";
|
|
// 案件线索表
|
createTableHasUnit("case_clue");
|
m_itemAndTable["案件线索"] = "case_clue";
|
|
}
|
|
void SearchInfo::createTableHasEvidences(QString tableName)
|
{
|
QString sql =QString( "CREATE TABLE %1 ( \
|
id INTEGER PRIMARY KEY AUTOINCREMENT,\
|
kpi_name TEXT,\
|
code TEXT,\
|
num INTEGER,\
|
evidences TEXT,\
|
com_id INTEGER,\
|
time TEXT\
|
)").arg(tableName);
|
QSqlQuery query;
|
if(query.exec(sql))
|
{
|
qDebug()<<QString("create %1 ok...").arg(tableName);
|
}else{
|
qDebug()<<QString("create %1 fail...").arg(tableName)<<query.lastError().text();
|
}
|
}
|
|
void SearchInfo::createTableNoEvidences(QString tableName)
|
{
|
QString sql = QString("CREATE TABLE %1 (\
|
id INTEGER PRIMARY KEY AUTOINCREMENT,\
|
kpi_name TEXT,\
|
code TEXT,\
|
num INTEGER,\
|
com_id INTEGER,\
|
time TEXT\
|
)").arg(tableName);
|
QSqlQuery query;
|
if(query.exec(sql))
|
{
|
qDebug()<<QString("create %1 ok...").arg(tableName);
|
}else{
|
qDebug()<<QString("create %1 fail...").arg(tableName)<<query.lastError().text();
|
}
|
}
|
|
void SearchInfo::createTableHasUnit(QString tableName)
|
{
|
QString sql = QString("CREATE TABLE %1 (\
|
id INTEGER PRIMARY KEY AUTOINCREMENT,\
|
kpi_name TEXT,\
|
code TEXT,\
|
unit TEXT,\
|
num INTEGER,\
|
com_id INTEGER,\
|
time TEXT\
|
)").arg(tableName);
|
QSqlQuery query;
|
if(query.exec(sql))
|
{
|
qDebug()<<QString("create %1 ok...").arg(tableName);
|
}else{
|
qDebug()<<QString("create %1 fail...").arg(tableName)<<query.lastError().text();
|
}
|
}
|
|
void SearchInfo::searchComName()
|
{
|
QSqlQuery query;
|
QString sql = QString("select id,name,code from company_info");
|
if(query.exec(sql)){
|
qDebug()<<"size:"<<query.numRowsAffected();
|
while(query.next()){
|
int id = query.value(0).toInt();
|
QString name = query.value(1).toString();
|
QString code = query.value(2).toString();
|
qDebug()<<"com select:"<<id<<name;
|
m_comInfo[name] = id;
|
m_comCode[name] = code;
|
ui->comboBox_company->addItem(name);
|
}
|
}
|
}
|
|
QVector<QVector<QString>> SearchInfo::getResult(QString &sql,int colCnt)
|
{
|
QVector<QVector<QString>> resultSet;
|
QSqlQuery query;
|
qDebug()<<sql;
|
if(query.exec(sql)){
|
while (query.next()) {
|
QVector<QString> tmp;
|
for(int i=0;i<colCnt;++i){
|
tmp.push_back(query.value(i).toString());
|
}
|
resultSet.append(tmp);
|
}
|
}
|
return resultSet;
|
}
|
|
void SearchInfo::setLabels()
|
{
|
QStringList labels,labels2,labels3;
|
labels<<"ID"<<"指标名称"<<"代码"<<"数量"<<"公司编号"<<"审计时间"<<" ";
|
labels2<<"ID"<<"指标名称"<<"代码"<<"单位"<<"数量"<<"公司编号"<<"审计时间";
|
labels3<<"ID"<<"指标名称"<<"代码"<<"数量"<<"佐证"<<"公司编号"<<"审计时间";
|
m_labels["审计项目"] = labels;
|
m_labels["内审工作量"] = labels;
|
m_labels["问题金额"] = labels3;
|
m_labels["问题个数"] = labels3;
|
m_labels["问题整改"] = labels3;
|
m_labels["问题整改--非金额"] = labels3;
|
m_labels["处分"] = labels;
|
m_labels["案件线索"] = labels2;
|
}
|
|
void SearchInfo::on_pushButton_search_clicked()
|
{
|
// 获取信息，然后查询
|
QString item = ui->comboBox->currentText();
|
QString comName = ui->comboBox_company->currentText();
|
QString sql = QString("select * from %1 where com_id=%2").arg(m_itemAndTable[item]).arg(m_comInfo[comName]);
|
if(ui->checkBox->isChecked()){ // 勾选之后
|
sql = QString("select * from %1 where com_id=%2 and time between '%3' and '%4'").arg(m_itemAndTable[item]).arg(m_comInfo[comName])
|
.arg(ui->dateEdit_begin->date().toString("yyyy-MM-dd"))
|
.arg(ui->dateEdit_end->date().toString("yyyy-MM-dd"));
|
}
|
qDebug()<<__FUNCTION__<<sql;
|
QSqlQuery query;
|
if(query.exec(sql)){
|
qDebug()<<"size:"<<query.size();
|
m_model->setRowCount(0);
|
int rowCnt = 0;
|
while (query.next()) {
|
rowCnt++;
|
m_model->setRowCount(rowCnt);
|
qDebug()<<query.value(0).toInt();
|
for(int i=0;i<7;++i){
|
if(query.value(i).isValid()){
|
m_model->setItem(rowCnt-1,i,new QStandardItem(query.value(i).toString()));
|
}
|
}
|
|
}
|
}
|
|
}
|
|
void SearchInfo::on_pushButton_export_clicked()
|
{
|
if(ui->comboBox_company->currentText() == "公司名称"){
|
QMessageBox::information(this,"导出错误","请选择一个公司名再导出");
|
return;
|
}
|
if(ui->label_excel_path->text().size() < 5){
|
QMessageBox::information(this,"导出设置错误","请先在“导出设置”选择模板及保存文件的路径信息");
|
return;
|
}
|
// 每一张表拿最新的那一批数据即可，然后保存到电子表格中
|
int comId = m_comInfo[ui->comboBox_company->currentText()];
|
QString audit_project = QString("select kpi_name,code,num from %2 where com_id=%1 limit 10").arg(comId).arg("audit_project");
|
QString audit_workload = QString("select kpi_name,code,num from %2 where com_id=%1 limit 1").arg(comId).arg("audit_workload");
|
QString case_clue = QString("select kpi_name,code,unit,num from %2 where com_id=%1 limit 2").arg(comId).arg("case_clue");
|
QString problem_count = QString("select kpi_name,code,num,evidences from %2 where com_id=%1 limit 7").arg(comId).arg("problem_count");
|
QString problem_money = QString("select kpi_name,code,num,evidences from %2 where com_id=%1 limit 11").arg(comId).arg("problem_money");
|
QString problem_no_money = QString("select kpi_name,code,num,evidences from %2 where com_id=%1 limit 5").arg(comId).arg("problem_no_money");
|
QString problem_rectification = QString("select kpi_name,code,num,evidences from %2 where com_id=%1 limit 7").arg(comId).arg("problem_rectification");
|
QString punish = QString("select kpi_name,code,num from %2 where com_id=%1 limit 4").arg(comId).arg("punish");
|
|
QVector<QVector<QString>> audit_project_res = getResult(audit_project,3);
|
QVector<QVector<QString>> audit_workload_res = getResult(audit_workload,3);
|
QVector<QVector<QString>> case_clue_res = getResult(case_clue,4);
|
QVector<QVector<QString>> problem_count_res = getResult(problem_count,4);
|
QVector<QVector<QString>> problem_money_res = getResult(problem_money,4);
|
QVector<QVector<QString>> problem_no_money_res = getResult(problem_no_money,4);
|
QVector<QVector<QString>> problem_rectification_res = getResult(problem_rectification,4);
|
QVector<QVector<QString>> punish_res = getResult(punish,3);
|
qDebug()<<audit_project_res.size()<<punish_res.size();
|
|
// 操作excel
|
// 创建新的电子表格
|
// QAxObject* excel = new QAxObject("Excel.Application"); // 创建Excel对象
|
// excel->setProperty("Visible", false); // 设置Excel不可见
|
// QAxObject* workbooks = excel->querySubObject("Workbooks"); // 获取所有工作簿
|
// QAxObject* workbook = workbooks->querySubObject("Add"); // 添加新工作簿
|
// QAxObject* worksheets = workbook->querySubObject("Worksheets"); // 获取所有工作表
|
// QAxObject* worksheet = worksheets->querySubObject("Item(int)", 1); // 获取第一个工作表
|
// for (int i = 1; i <= 61; i++) {
|
|
// for (int j = 1; j <= 5; j++) {
|
// QString cellValue = "Value"; // 设置要写入的单元格值
|
// worksheet->querySubObject("Cells(int, int)", i, j)->dynamicCall("SetValue(const QVariant&)", cellValue); // 写入单元格值
|
// }
|
//}
|
|
|
// 构建一张表：
|
// QString cellValue = "单位内部审计业务情况表"; // 设置要写入的单元格值
|
// worksheet->querySubObject("Cells(int, int)", 1, 1)->dynamicCall("SetValue(const QVariant&)", cellValue); // 写入单元格值
|
// worksheet->querySubObject("Cells(int, int)", 3, 4)->dynamicCall("SetValue(const QVariant&)", "表 号:");
|
// worksheet->querySubObject("Cells(int, int)", 3, 5)->dynamicCall("SetValue(const QVariant&)", "内审统02表");
|
|
// QString path = QDir::currentPath();
|
// qDebug()<<"path:"<<path;
|
// path += "/debug/Data/save2.xls";
|
// qDebug()<<"path:"<<path;
|
// workbook->dynamicCall("SaveAs(const QString&)", path); // 保存工作簿
|
// workbook->dynamicCall("Close()"); // 关闭工作簿
|
// excel->dynamicCall("Quit()"); // 退出Excel应用程序
|
// QMessageBox::information(this,"导出路径信息",path);
|
|
// 打开已存在的电子表格，然后往里写入数据
|
// :/mayi/附件3_内审统02表_单位内部审计业务情况表.xls
|
|
QAxObject excel("Excel.Application");
|
excel.setProperty("Visible",false);
|
excel.setProperty("DisplayAlerts",false);
|
//QString str = "D:/Qt/QtP/build-internal_system_v1-Desktop_Qt_5_9_4_MinGW_32bit-Debug/debug/Data/附件3_内审统02表_单位内部审计业务情况表.xls";
|
QString str = ui->label_excel_path->text();
|
qDebug()<<"open:"<<str;
|
qDebug()<<"excel:"<<excel.className();
|
QAxObject * wbs = excel.querySubObject("WorkBooks");
|
QAxObject * wb = wbs->querySubObject("Open(QString&)",str);
|
QAxObject * shs = wb->querySubObject("Sheets"); // WorkSheets 也可以
|
int cnt = shs->property("Count").toInt();
|
qDebug()<<"cnt="<<cnt;
|
for(int i=0;i<cnt;++i){
|
QAxObject * sheet = shs->querySubObject("Item(int)", i+1);
|
QString sheetName = sheet->property("Name").toString();
|
qDebug()<<"sh name:"<<sheetName;
|
// 然后再操作表内的数据
|
QAxObject *usedRange = sheet->querySubObject("UsedRange");
|
int rows = usedRange->querySubObject("Rows")->property("Count").toInt();
|
int columns = usedRange->querySubObject("Columns")->property("Count").toInt();
|
qDebug()<<"r--c:"<<rows<<columns;
|
QVariant val = usedRange->dynamicCall("Value"); // 拿到二维表格
|
QVariantList valRows = val.toList();
|
qDebug()<<valRows[11].toList().at(4);
|
sheet->querySubObject("Cells(int, int)", 7, 2)->dynamicCall("SetValue(const QVariant&)", ui->comboBox_company->currentText());
|
sheet->querySubObject("Cells(int, int)", 6, 2)->dynamicCall("SetValue(const QVariant&)", m_comCode[ui->comboBox_company->currentText()]);
|
|
// 写入审计项目 一
|
qDebug()<<"size:"<<audit_project_res.size();
|
QString it = audit_project_res[0][2];
|
//sheet->querySubObject("Cells(int, int)", 13, 5)->dynamicCall("SetValue(doubule)", 4.5);
|
for(int i=0;i<audit_project_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 11+i, 5)->dynamicCall("SetValue(const QVariant&)", audit_project_res[i][2].toDouble());
|
}
|
|
// 写入审计工作量 二
|
for(int i=0;i<audit_workload_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 21+i, 5)->dynamicCall("SetValue(const QVariant&)", audit_workload_res[i][2].toDouble());
|
}
|
// 写入问题金额 三
|
for(int i=0;i<problem_money_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 22+i, 5)->dynamicCall("SetValue(const QVariant&)", problem_money_res[i][2].toDouble());
|
}
|
// 写入问题个数 四
|
for(int i=0;i<problem_count_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 33+i, 5)->dynamicCall("SetValue(const QVariant&)", problem_count_res[i][2].toDouble());
|
}
|
// 写入问题整改(金额) 五
|
for(int i=0;i<problem_rectification_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 40+i, 5)->dynamicCall("SetValue(const QVariant&)", problem_rectification_res[i][2].toDouble());
|
}
|
// 写入问题整改(非金额) 六
|
for(int i=0;i<problem_no_money_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 47+i, 5)->dynamicCall("SetValue(const QVariant&)", problem_no_money_res[i][2].toDouble());
|
}
|
// 写入处分 七
|
for(int i=0;i<punish_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 52+i, 5)->dynamicCall("SetValue(const QVariant&)", punish_res[i][2].toDouble());
|
}
|
// 写入案件线索 八
|
for(int i=0;i<case_clue_res.size();++i){
|
sheet->querySubObject("Cells(int, int)", 56+i, 5)->dynamicCall("SetValue(const QVariant&)", case_clue_res[i][2].toDouble());
|
}
|
|
|
}
|
//wb->dynamicCall("Save()"); // 若文件已存在，则直接保存
|
//wb->dynamicCall("SaveAs(const QString&)", str); // 保存工作簿，若文件不存在，则带名字保存
|
if(ui->lineEdit_excel_save->text().size() > 1){
|
wb->dynamicCall("SaveAs(const QString&)", ui->lineEdit_excel_save->text()); // 保存工作簿，若文件不存在，则带名字保存
|
qDebug()<<"保存到新文件中";
|
}else{
|
wb->dynamicCall("Save()"); // 直接保存到模板文件中
|
qDebug()<<"保存到老文件中";
|
}
|
|
wbs->dynamicCall("Close()");
|
excel.dynamicCall("Quit(void)");
|
QMessageBox::information(this,"导出成功",QString("导出成功，文件路径为:%1").arg(ui->lineEdit_excel_save->text()));
|
|
|
}
|
|
void SearchInfo::on_comboBox_currentIndexChanged(const QString &arg1)
|
{
|
m_model->setHorizontalHeaderLabels(m_labels[arg1]);
|
}
|
|
void SearchInfo::on_toolButton_seleceExcel_clicked()
|
{
|
if(ui->comboBox_company->currentText() == "公司名称"){
|
QMessageBox::information(this,"导出设置错误","请选择一个公司名再设置");
|
return;
|
}
|
QString path = QFileDialog::getOpenFileName(this,"选择表格模板:单位内部审计业务情况表","./","excel(*单位内部审计业务情况表.xls)");
|
ui->label_excel_path->setText(path);
|
QStringList paths = path.split(".");
|
QString newPath = paths.at(0)+"_"+ui->comboBox_company->currentText()+"."+paths.at(1);
|
ui->lineEdit_excel_save->setText(newPath);
|
}
|
|
void SearchInfo::on_comboBox_company_currentIndexChanged(const QString &arg1)
|
{
|
if(ui->lineEdit_excel_save->text().size() > 5){
|
QStringList paths = ui->label_excel_path->text().split(".");
|
QString newPath = paths.at(0)+"_"+arg1+"."+paths.at(1);
|
ui->lineEdit_excel_save->setText(newPath);
|
}
|
}
|
|
void SearchInfo::addComName(QString name)
|
{
|
ui->comboBox_company->addItem(name);
|
}
|
|
void SearchInfo::on_checkBox_clicked()
|
{
|
if(ui->checkBox->isChecked()){
|
ui->dateEdit_begin->setEnabled(true);
|
ui->dateEdit_end->setEnabled(true);
|
}else{
|
ui->dateEdit_begin->setEnabled(false);
|
ui->dateEdit_end->setEnabled(false);
|
}
|
}
|
