From 6ea2769d53615984e57b26b63b8d56b49c472e26 Mon Sep 17 00:00:00 2001
From: wumu <mayi@mayi.com>
Date: 星期三, 13 九月 2023 22:44:13 +0800
Subject: [PATCH] 0913
---
internal_system_v1/searchinfo.cpp | 303 ++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 303 insertions(+), 0 deletions(-)
diff --git a/internal_system_v1/searchinfo.cpp b/internal_system_v1/searchinfo.cpp
index e19e864..1d5265e 100644
--- a/internal_system_v1/searchinfo.cpp
+++ b/internal_system_v1/searchinfo.cpp
@@ -1,14 +1,317 @@
#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()"); // 閫�鍑篍xcel搴旂敤绋嬪簭
+
+}
+
+void SearchInfo::on_comboBox_currentIndexChanged(const QString &arg1)
+{
+ m_model->setHorizontalHeaderLabels(m_labels[arg1]);
+}
--
Gitblit v1.8.0