#include "searchinfo.h"
|
#include "ui_searchinfo.h"
|
#include <QAxObject>
|
#include <QDebug>
|
#include <QDir>
|
#include <QSqlError>
|
#include <QSqlQuery>
|
|
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();
|
|
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\
|
)").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\
|
)").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\
|
)").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 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();
|
qDebug()<<"com select:"<<id<<name;
|
m_comInfo[name] = id;
|
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]);
|
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<6;++i){
|
if(query.value(i).isValid()){
|
m_model->setItem(rowCnt-1,i,new QStandardItem(query.value(i).toString()));
|
}
|
}
|
|
}
|
}
|
|
}
|
|
void SearchInfo::on_pushButton_export_clicked()
|
{
|
// 每一张表拿最新的那一批数据即可，然后保存到电子表格中
|
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 <= 5; i++) {
|
for (int j = 1; j <= 5; j++) {
|
QString cellValue = "Value"; // 设置要写入的单元格值
|
worksheet->querySubObject("Cells(int, int)", i, j)->dynamicCall("SetValue(const QVariant&)", cellValue); // 写入单元格值
|
}
|
}
|
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应用程序
|
|
}
|
|
void SearchInfo::on_comboBox_currentIndexChanged(const QString &arg1)
|
{
|
m_model->setHorizontalHeaderLabels(m_labels[arg1]);
|
}
|
