#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{
|
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";
|
}
|
|
|
}
|
|
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);
|
}
|
}
|
}
|
|
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()));
|
}
|
}
|
|
}
|
