0906 export

wumu

2023-09-06 5ac536d5b37be7bfd6f9cf5db62aa83a174ff602

 internal_system_v1/coverinfo.cpp

@@ -1,6 +1,11 @@
#include "coverinfo.h"
#include "ui_coverinfo.h"
#include <QDebug>
#include <QDirIterator>
#include <QSqlQuery>
#include <QSqlRecord>

int g_comId = 0; // 全局变量，保存单位id

CoverInfo::CoverInfo(QWidget *parent) :
    QMainWindow(parent),
@@ -8,6 +13,8 @@
{
    ui->setupUi(this);
    readInfo();
    readDataFromSQL(); // 从数据库读取数据

}

CoverInfo::~CoverInfo()
@@ -17,8 +24,239 @@

void CoverInfo::readInfo()
{
    QXlsx::Document doc("./debug/Data/test.xlsx");
    QString cc = doc.read("A1").toString();
    qDebug()<<"cc"<<cc;
    ui->label_creditCode->setText(cc);
//    QXlsx::Document doc("./debug/Data/test.xlsx");
//    QString cc = doc.read("A1").toString();
//    qDebug()<<"cc"<<cc;
//    ui->label_creditCode->setText(cc);

    QString dirPath = "./debug/Data";
    QStringList list;
    QDirIterator it(dirPath, {"*.xls","*.xlsx"}, QDir::Files | QDir::NoSymLinks | QDir::NoDotAndDotDot, QDirIterator::Subdirectories);
    while(it.hasNext())
    {
        it.next();
        list.append(it.fileInfo().absoluteFilePath());
    }
    foreach(QString str, list)
    {
        qDebug() << str;
        int index = str.indexOf("内审统01表");
        qDebug()<<"index:"<<index;
        if(index > 0){
            QAxObject excel("Excel.Application");
            excel.setProperty("Visible",false);
            excel.setProperty("DisplayAlerts",false);
            qDebug()<<"open:"<<str;
            qDebug()<<"excel:"<<excel.className();
            QAxObject * wbs = excel.querySubObject("WorkBooks");
            QAxObject * wb = wbs->querySubObject("Open(QString&)",str);
            //QAxObject* pWorkBook = excel.querySubObject("ActiveWorkBook");
            //QAxObject* pWorkSheets = pWorkBook->querySubObject("Sheets");//获取工作表
            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();
                // 遍历每一项
//                for(int i=0;i<rows;++i){
//                    QVariantList valrow = valRows[i].toList();
//                    for(int j=0;j<columns;++j){
//                        QVariant valData = valrow[j];
//                        if(valData.isValid()){
//                            qDebug()<<"val:"<< i<< j << valData;
//                        }

//                    }
//                }

                // 统一信用代码
                QVariant uniCode = valRows[9].toList().at(3);
                if(uniCode.isValid()){
                    ui->label_creditCode->setText(uniCode.toString());
                }
                // 单位名称
                QVariant name = valRows[9].toList().at(8);
                if(name.isValid()){
                    ui->label_name->setText(name.toString());
                }
                // 机构类型
                QVariant comType = valRows[12].toList().at(3);
                if(comType.isValid()){
                    ui->label_comType->setText(comType.toString());
                }
                // 主要业务活动
                QString business;
                QVariant first = valRows[18].toList().at(2);
                if(first.isValid()){
                    business.append(first.toString());
                }
                QVariant sec = valRows[18].toList().at(5);
                if(sec.isValid()){
                    business += "、";
                    business.append(sec.toString());
                }
                QVariant third = valRows[18].toList().at(8);
                if(third.isValid()){
                    business += "、";
                    business.append(third.toString());
                }
                if(business.size() > 0){
                    ui->label_business->setText(business);
                }
                // 业务代码
                QVariant busCode = valRows[19].toList().at(4);
                if(busCode.isValid()){
                    ui->label_businessCode->setText(busCode.toString());
                }
                // 注册地址
                QString addr="";
                QVariant province = valRows[21].toList().at(1); // 省份
                QVariant city = valRows[21].toList().at(4); // 城市
                QVariant district = valRows[21].toList().at(7); // 管辖区
                QVariant village = valRows[22].toList().at(2); // 乡镇
                QVariant street = valRows[22].toList().at(4); // 街道
                QVariant Sub = valRows[23].toList().at(2); // 街道办事处
                QVariant committee = valRows[25].toList().at(3); // 居委会
                //qDebug()<<province<<city<<district<<village<<street<<Sub<<committee;
                if(province.isValid()) addr += province.toString();
                if(city.isValid()) addr += city.toString();
                if(district.isValid()) addr += district.toString();
                if(village.isValid()) addr += village.toString();
                if(street.isValid()) addr += street.toString();
                if(Sub.isValid()) addr += Sub.toString();
                if(committee.isValid()) addr += committee.toString();
                if(addr.size()>10)ui->label_addr->setText(addr);
                // 区域代码
                QVariant areaCode = valRows[24].toList().at(2);
                if(areaCode.isValid()) ui->label_areaCode->setText(areaCode.toString());
                // 城乡代码
                QVariant townCode = valRows[24].toList().at(6);
                if(townCode.isValid()) ui->label_townCode->setText(townCode.toString());
                // 单位规模
                QVariant scale = valRows[25].toList().at(3);
                if(scale.isValid()) ui->label_scale->setText(scale.toString());
                // 从业人数
                QVariant people = valRows[26].toList().at(6);
                if(people.isValid()) ui->label_people->setText(people.toString());
                // 法人
                QVariant representative = valRows[27].toList().at(4);
                if(representative.isValid()) ui->label_representative->setText(representative.toString());
                // 会计标准类别 standardType
                QVariant standardType = valRows[27].toList().at(8);
                if(standardType.isValid()) ui->label_standardType->setText(standardType.toString());
                // 长途区号
                QVariant trunkCode = valRows[31].toList().at(2);
                if(trunkCode.isValid()) ui->label_trunkCode->setText(trunkCode.toString());
                // 固定电话
                QVariant fixedTel = valRows[32].toList().at(2);
                if(fixedTel.isValid()) ui->label_fixedTel->setText(fixedTel.toString());
                // 邮政编码
                QVariant postCode = valRows[33].toList().at(2);
                if(postCode.isValid()) ui->label_postCode->setText(postCode.toString());
                // 电子邮箱
                QVariant email = valRows[30].toList().at(5);
                if(email.isValid()) ui->label_email->setText(email.toString());
                // 网址
                QVariant webSite = valRows[32].toList().at(5);
                if(webSite.isValid()) ui->label_webSite->setText(webSite.toString());
            }
            wbs->dynamicCall("Close()");
            excel.dynamicCall("Quit(void)");
        }
    }


//    QAxObject excel("./debug/Data/附件2_内审统01表_单位及内部审计机构基本情况表-1.301版(1).xls");
//    excel.setProperty("Visible",true);
//    excel.setProperty("DisplayAlerts",false);

}

void CoverInfo::readDataFromSQL()
{
    QSqlQuery query;
    QString sql;
    if(ui->label_name->text().size() > 1){
        sql = QString("select id,name from company_info where name='%1'").arg(ui->label_name->text());
        qDebug()<<"查询公司sql:"<<sql;
        if(query.exec(sql)){
            qDebug()<<"OK sql"<<__FUNCTION__;
            QSqlRecord res = query.record();
            qDebug()<<query.size()<<query.numRowsAffected()<<res.count();
            if(!query.next()){
                // 插入一条单位信息：
                sql = QString("insert into company_info (name,code,机构类型,主要业务活动,行业代码,单位注册地及区域,"
                              "区域代码,城乡代码,单位规模,从业期末人数,法定代表人,执行会记标准类别,长途区号,固定电话,邮政编码,"
                              "电子邮箱,网址) values ('%1','%2','%3','%4','%5','%6','%7','%8','%9','%10','%11','%12','%13','%14','%15','%16','%17')").arg(ui->label_name->text())
                        .arg(ui->label_creditCode->text()).arg(ui->label_comType->text()).arg(ui->label_business->text()).arg(ui->label_businessCode->text()).arg(ui->label_addr->text())
                        .arg(ui->label_areaCode->text()).arg(ui->label_townCode->text()).arg(ui->label_scale->text()).arg(ui->label_people->text()).arg(ui->label_representative->text())
                        .arg(ui->label_standardType->text()).arg(ui->label_trunkCode->text()).arg(ui->label_fixedTel->text()).arg(ui->label_postCode->text()).arg(ui->label_email->text())
                        .arg(ui->label_webSite->text());
                qDebug()<<"com_insert sql:"<<sql;
                if(query.exec(sql)){
                    qDebug()<<"insert com ok";
                }else{
                    qDebug()<<"insert com fail";
                }

            }else{
                g_comId = query.value(0).toInt();
            }
        }else{
            qDebug()<<"fail sql"<<__FUNCTION__;
        }
    }
    sql = QString("select id,name from company_info");
    if(query.exec(sql)){
        qDebug()<<"size:"<<query.size();
        while(query.next()){
            int id = query.value(0).toInt();
            QString name = query.value(1).toString();
            qDebug()<<"com select:"<<id<<name;
            m_com[name] = id;
            ui->comboBox->addItem(name);
        }
    }

}

void CoverInfo::on_comboBox_currentIndexChanged(const QString &arg1)
{
    QString sql = QString("select * from company_info where name='%1'").arg(arg1);
    QSqlQuery query;
    if(query.exec(sql)){
        qDebug()<<"切换查询成功:"<<arg1;
        while (query.next()) {
            qDebug()<<"切换查询成功查询到目标单位:"<<arg1;
            g_comId = query.value(0).toInt();
            ui->label_name->setText(query.value(1).toString());
            ui->label_creditCode->setText(query.value(2).toString());
            ui->label_comType->setText(query.value(3).toString());
            ui->label_business->setText(query.value(4).toString());
            ui->label_businessCode->setText(query.value(5).toString());
            ui->label_addr->setText(query.value(6).toString());
            ui->label_areaCode->setText(query.value(7).toString());
            ui->label_townCode->setText(query.value(8).toString());
            ui->label_scale->setText(query.value(9).toString());
            ui->label_people->setText(query.value(10).toString());
            ui->label_representative->setText(query.value(11).toString());
            ui->label_standardType->setText(query.value(12).toString());
            ui->label_trunkCode->setText(query.value(13).toString());
            ui->label_fixedTel->setText(query.value(14).toString());
            ui->label_postCode->setText(query.value(15).toString());
            ui->label_email->setText(query.value(16).toString());
            ui->label_webSite->setText(query.value(17).toString());
            break;
        }
    }else{
        qDebug()<<"切换查询失败:"<<arg1;
    }
}