From 5f209bc8ed68bbdb6b7180aaa5bc37acc8545e35 Mon Sep 17 00:00:00 2001 From: wumu <mayi@mayi.com> Date: 星期五, 08 九月 2023 23:25:29 +0800 Subject: [PATCH] 0908 --- internal_system_v1/searchinfo.cpp | 241 ++++++++++++++++++++++++++++++++++++++++++++++++ 1 files changed, 240 insertions(+), 1 deletions(-) diff --git a/internal_system_v1/searchinfo.cpp b/internal_system_v1/searchinfo.cpp index 52166f7..83cc3fe 100644 --- a/internal_system_v1/searchinfo.cpp +++ b/internal_system_v1/searchinfo.cpp @@ -1,12 +1,23 @@ #include "searchinfo.h" #include "ui_searchinfo.h" #include <QDebug> +#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); // 闅愯棌琛屽ご + if(QSqlDatabase::contains("qt_sql_default_connection")){ m_db = QSqlDatabase::addDatabase("qt_sql_default_connection"); }else{ @@ -15,7 +26,9 @@ m_db.setDatabaseName("sqlData.db"); if(m_db.open()){ qDebug()<<"db open ok"; - m_db.close(); + createSqlite(); + //m_db.close(); + searchComName(); // 鏌ヨ鍏徃鍚� }else{ qDebug()<<"db open fail"; } @@ -25,5 +38,231 @@ 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,\ + 琛屼笟浠g爜 TEXT,\ + 鍗曚綅娉ㄥ唽鍦板強鍖哄煙 TEXT,\ + 鍖哄煙浠g爜 TEXT,\ + 鍩庝埂浠g爜 TEXT,\ + 鍗曚綅瑙勬ā TEXT,\ + 浠庝笟鏈熸湯浜烘暟 TEXT,\ + 娉曞畾浠h〃浜� 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::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(); + m_model->setItem(rowCnt-1,0,new QStandardItem(query.value(0).toString())); + m_model->setItem(rowCnt-1,1,new QStandardItem(query.value(1).toString())); + m_model->setItem(rowCnt-1,2,new QStandardItem(query.value(2).toString())); + m_model->setItem(rowCnt-1,3,new QStandardItem(query.value(3).toString())); + m_model->setItem(rowCnt-1,4,new QStandardItem(query.value(4).toString())); + } + } + +} + +void SearchInfo::on_pushButton_export_clicked() +{ + // 姣忎竴寮犺〃鎷挎渶鏂扮殑閭d竴鎵规暟鎹嵆鍙紝鐒跺悗淇濆瓨鍒扮數瀛愯〃鏍间腑 + 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(); + +} -- Gitblit v1.8.0