/**
 * 小程序端  企业入口  用户基础功能路由
 */

import * as asyncHandler from 'express-async-handler';
import { checkInterior } from '../middleware/user';
import { getMySqlMs, getPinyinInitials, randomId } from '../tools/system';
import { OPERATIONALDATATYPE, TABLEID, TABLENAME } from '../config/enum/dbEnum';
import { ANSWERTYPE, CHANGESTATE, DEGREE, FOLLOWUPSTATUS, FUHUASTATE, IPRALLTYPE, NEEDCATEGORY, OUTCOME, STATE, ZZMM } from '../config/enum/enum';
import { operationalData, selectData } from '../data/operationalData';
import { initApiDataStorage } from '../data/dataInterfaceWithCache';
import { updateQCCDataTask } from '../biz/qccInit';
import { changeEnumValue } from '../util/verificationEnum';
import { BizError } from '../util/bizError';
import { ERRORENUM } from '../config/enum/errorEnum';
const xlsx = require('node-xlsx');
const path = require('path');
const fs = require('fs');

export function setRouter(httpServer) {
    //管理员登录登出
    httpServer.post('/admin/data/maintenance/datainit/1210', checkInterior, asyncHandler(data1210));
    httpServer.post('/admin/data/maintenance/datainit/answer', checkInterior, asyncHandler(addAnswer));
    httpServer.post('/admin/data/maintenance/datainit/zufanganswer', checkInterior, asyncHandler(addZuFangAnswer));
    httpServer.post('/admin/data/maintenance/datainit/qcc', checkInterior, asyncHandler(qccDatat0519));
    httpServer.post('/admin/data/maintenance/datainit/0620', checkInterior, asyncHandler(data0620));
    httpServer.post('/admin/data/maintenance/datainit/0709', checkInterior, asyncHandler(data0709));
    httpServer.post('/admin/data/maintenance/datainit/uscc', checkInterior, asyncHandler(updateUscc));
    httpServer.post('/admin/data/maintenance/datainit/enterprise', checkInterior, asyncHandler(dataInitEnterprise));
    httpServer.post('/admin/data/maintenance/datainit/loginTime', checkInterior, asyncHandler(dataUpdateLoginTime));
    httpServer.post('/admin/data/maintenance/datainit/updatetime', checkInterior, asyncHandler(updateTime));
    httpServer.post('/admin/data/maintenance/datainit/addenterprise', checkInterior, asyncHandler(addEnterprise));
    httpServer.post('/admin/data/maintenance/datainit/addservice', checkInterior, asyncHandler(addService));
     
}


/**
 * 导入企业服务
 */
export async function addService(req, res) {
    const FIELD_MAPPING = {
        /**企业服务表 */
        '需求类别': 'needCategory',
        '需求内容': 'needContent',
        '申请时间': 'applyTime',
        '跟进状态': 'followUpStatus',
        '解决时间': 'resolveTime',
        '反馈': 'fangKui',
        '反馈时间': 'shouLiTime',
        '结果': 'outcome',
        '备注': 'desc'
    };
    
    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '企业服务导入数据.xlsx' ));
    let dataList1 = sheetMap['未受理'];
    let dataList2 = sheetMap['已受理'];
    let dataList3 = sheetMap['已结束'];

    for (let i = 1; i < dataList1.length; i++) {
        let info = dataList1[i];
        let enterpriseName =info[0];
        let uscc = info[1];

        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {enterpriseName, uscc}, ["eId", "uscc", "enterpriseName"]);
        if (!enterpriseDbInfo.eId) throw new BizError("未匹配到对应企业");
        
        let excelInfo = {
            esId: randomId(TABLEID.企业服务表),
            eId:enterpriseDbInfo.eId,
            needCategory:NEEDCATEGORY[info[2]],
            needContent:info[3],
            applyTime:info[4],
            followUpStatus:FOLLOWUPSTATUS.未受理,
        }

        // 新增企业服务
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业服务表, excelInfo, {});
        console.log(`新增未受理企业服务信息完成：${enterpriseName}`);
    }
    
    for (let i = 1; i < dataList2.length; i++) {
        let info = dataList2[i];
        let enterpriseName =info[0];
        let uscc = info[1];

        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {enterpriseName, uscc}, ["eId", "uscc", "enterpriseName"]);
        if (!enterpriseDbInfo.eId) throw new BizError("未匹配到对应企业");
        //{fangKui, followUpStatus:enumConfig.FOLLOWUPSTATUS.受理中, shouLiTime:getMySqlMs()}
        let excelInfo = {
            esId: randomId(TABLEID.企业服务表),
            eId:enterpriseDbInfo.eId,
            needCategory:NEEDCATEGORY[info[2]],
            needContent:info[3],
            applyTime:info[4],
            fangKui:info[5],
            shouLiTime:getMySqlMs(info[6]),
            followUpStatus:FOLLOWUPSTATUS.受理中,
        }

        // 新增企业服务
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业服务表, excelInfo, {});
        console.log(`新增受理中企业服务信息完成：${enterpriseName}`);
    }
    
    for (let i = 1; i < dataList3.length; i++) {
        let info = dataList3[i];
        let enterpriseName =info[0];
        let uscc = info[1];

        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {enterpriseName, uscc}, ["eId", "uscc", "enterpriseName"]);
        if (!Object.keys(enterpriseDbInfo).length) throw new BizError(ERRORENUM.企业不存在);
        if (!enterpriseDbInfo.eId) throw new BizError(ERRORENUM.企业不存在);
        //{outcome, followUpStatus:enumConfig.FOLLOWUPSTATUS.已完成, desc, resolveTime}
        let excelInfo = {
            esId: randomId(TABLEID.企业服务表),
            eId:enterpriseDbInfo.eId,
            needCategory:NEEDCATEGORY[info[2]],
            needContent:info[3],
            applyTime:info[4],
            fangKui:info[5],
            shouLiTime:getMySqlMs(info[6]),
            outcome: OUTCOME[info[7]],
            desc:info[8],
            followUpStatus:FOLLOWUPSTATUS.已完成,
        }

        // 新增企业服务
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业服务表, excelInfo, {});
        console.log(`新增已完成企业服务信息完成：${enterpriseName}`);
    }

    res.success({isSuccess:true});
}


/**
 * 导入遗漏的企业
 * @param req 
 * @param res 
 */
export async function addEnterprise(req, res) {
    const FIELD_MAPPING = {
        /**企业信息 */
        '企业名称': 'enterpriseName',
        '统一社会信用代码': 'uscc',
        '注册日期': 'logonTime',
        '注册地址': 'logonAddress',

        /**用户信息 */
        '联系人': 'userName',
        '联系方式': 'phone',

        /**法人信息 */
        '姓名': 'name',
        '身份证': 'idCard',
        '户籍': 'domicile',
        '学历': 'degree',
        '政治面貌': 'politicalStatus',
        '手机号码': 'farenphone',


        /**孵化信息 */
        '入孵日期-开始时间': 'startTime',
        '入孵日期-截止时间': 'endTime',
        
        /**租赁信息 */
        '楼号': 'building',
        '室号': 'roomNumber',
        '面积': 'area',
        '月租金': 'rent'
    };
    

    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '导入遗漏企业.xlsx' ));
    let dataList = sheetMap['Sheet1'];
    for (let i = 1; i < dataList.length; i++) {
        let info = dataList[i];

        let excelData = {
            enterpriseName:info[0],
            uscc:info[1],
            logonTime:info[15],

            userName:info[13],
            phone:info[14],

            name:info[5],
            idCard:info[9],
            domicile:info[10] || "",
            degree:DEGREE[info[7]],
            farenphone:info[8],

            building:info[2],
            roomNumber:info[3],
            area:info[4]
        }
        /**租赁日期 */
        let [zlstartStr, zlendStr] = info[16].split('-');
        /**入孵日期 */
        let [rfstartStr, rfendStr] = info[17].split('-');

        // 新增企业信息
        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {uscc:excelData.uscc}, ["eId", "uscc", "enterpriseName"]);
        let eId = "";
        if (!enterpriseDbInfo.eId) {
            eId = randomId(TABLEID.企业基础信息表);
            let pinyinName = getPinyinInitials(excelData.enterpriseName)
            
            let enterpriseInfo = { //基础信息表
                eId,
                enterpriseName:excelData.enterpriseName,
                pinyinName,
                uscc: excelData.uscc,
                // logonTime: excelData.logonTime,
                // logonAddress: excelData.logonAddress,
                state:CHANGESTATE.已通过,
                register:CHANGESTATE.已通过,
                
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业基础信息表, enterpriseInfo, {});

            console.log(`新增企业信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        } else {
            eId = enterpriseDbInfo.eId;
        }

        let zulinDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.租赁信息, {eId}, ["leaseId"]);
        if (!zulinDbInfo.leaseId) {
            let enterprise_leaseInfo:any = {//租赁信息表
                leaseId:randomId(TABLEID.租赁信息),
                eId,
                area:excelData.area || 0,
                building:excelData.building || '',
                roomNumber:excelData.roomNumber || '',
                startTime:zlstartStr,
                endTime:zlendStr
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.租赁信息, enterprise_leaseInfo, {});

            console.log(`新增租赁信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let fuhuaDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业孵化信息, {eId}, ["fId"]);
        if (!fuhuaDbInfo.fId) {
            let enterprise_fuhua:any = {//孵化表
                fId:randomId(TABLEID.企业孵化信息),
                eId,
                state:FUHUASTATE.实体孵化,
                startTime:rfstartStr,
                endTime:rfendStr
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业孵化信息, enterprise_fuhua, {});

            console.log(`新增企业孵化信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let farenDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.法人信息表, {eId}, ["legalId"]);
        if (!farenDbInfo.legalId) {
            let enterprise_legal_personInfo:any = {//法人信息
                legalId:randomId(TABLEID.法人信息表),
                eId,
                name: excelData.name,
                idCard: excelData.idCard,
                domicile: excelData.domicile || '',
                degree: excelData.degree || null,
                // politicalStatus:politicalStatus || null,
                phone: excelData.farenphone || null,
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.法人信息表, enterprise_legal_personInfo, {});

            console.log(`新增法人信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let userDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业用户表, {eId}, ["uId"]);
        if (!userDbInfo.uId) {
            // 准备添加用户数据
            let addUserData = {
                uId:randomId(TABLEID.企业用户表),
                userName:excelData.userName,
                phone:excelData.phone+'',
                uscc: excelData.uscc,
                pwd:'111111',
                eId
            };
            // 添加到数据库
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业用户表, addUserData, {});

            console.log(`新增企业用户信息：eId：${eId}，企业名称：${excelData.enterpriseName}，联系人：${excelData.userName}，手机号：${excelData.phone}`);
        }
    }
    
    res.success({isSuccess:true});
}


export async function updateTime(req, res) {
    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '工作簿1.xlsx' ));
    let dataList = sheetMap['Sheet1'];
    for (let i = 2; i < dataList.length; i++) {
        let info = dataList[i];
        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {uscc:info[3]}, ["eId", "uscc", "enterpriseName"]);
        if (!enterpriseDbInfo.eId) throw new BizError(ERRORENUM.数据不存在);

        // 分割字符串获取开始和结束日期部分
        if (info[44]) {
            let [zlstartStr, zlendStr] = info[44].split('-');
            let lesaeInfo = {
                startTime:getMySqlMs(new Date(zlstartStr).valueOf()),
                endTime:getMySqlMs(new Date(zlendStr).valueOf()),
            }
            await operationalData(OPERATIONALDATATYPE.修改, TABLENAME.租赁信息, lesaeInfo, {eId:enterpriseDbInfo.eId});
            console.log(`修改租赁时间成功${i}，企业名称：${enterpriseDbInfo.enterpriseName}，开始：${lesaeInfo.startTime}，结束：${lesaeInfo.endTime}`)
        }
        
        if (info[46]) {
            let [rfstartStr, rfendStr] = info[46].split('-');
            let rufuInfo = {
                startTime:getMySqlMs(new Date(rfstartStr).valueOf()),
                endTime:getMySqlMs(new Date(rfendStr).valueOf()),
            }
            await operationalData(OPERATIONALDATATYPE.修改, TABLENAME.企业孵化信息, rufuInfo, {eId:enterpriseDbInfo.eId});
            console.log(`修改入孵时间成功${i}，企业名称：${enterpriseDbInfo.enterpriseName}，开始：${rufuInfo.startTime}，结束：${rufuInfo.endTime}`)
        }
        
    }

    res.success({isSuccess:true});
}


export async function updateUscc(req, res) {
    //1.获取数据库现有的企业数据
    let enterpriseList = await selectData(OPERATIONALDATATYPE.查询多个, TABLENAME.企业基础信息表, {}, ["eId", "uscc", "enterpriseName"]);
    for (let i = 0; i < enterpriseList.length; i++) {
        let info = enterpriseList[i];
        await operationalData(OPERATIONALDATATYPE.修改, TABLENAME.企业用户表, {uscc:info.uscc}, { eId: info.eId });
        console.log(`修改uscc成功，${i}`)
    }
    res.success({isSuccess:true});
}


export async function dataUpdateLoginTime(req, res) {// 配置字段映射关系 (Excel表头 -> 数据库字段)
    const FIELD_MAPPING = {
        /**企业信息 */
        '企业名称': 'enterpriseName',
        '统一社会信用代码': 'uscc',
        '注册日期': 'logonTime',
        '注册地址': 'logonAddress',

        /**用户信息 */
        '联系人': 'userName',
        '联系方式': 'phone',

        /**法人信息 */
        '姓名': 'name',
        '身份证': 'idCard',
        '户籍': 'domicile',
        '学历': 'degree',
        '政治面貌': 'politicalStatus',
        '手机号码': 'farenphone',


        /**孵化信息 */
        '入孵日期-开始时间': 'startTime',
        '入孵日期-截止时间': 'endTime',
        
        /**租赁信息 */
        '楼号': 'building',
        '室号': 'roomNumber',
        '面积': 'area',
        '月租金': 'rent'
    };
    

    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '在孵企业导入0619.xlsx' ));
    let dataList = sheetMap['Sheet1'];

    for (let i = 1; i < dataList.length; i++) {
        let info = dataList[i];
        let excelData = {
            enterpriseName:info[0],
            uscc:info[1],
            logonTime:getMySqlMs(new Date(info[15]).valueOf()),

            userName:info[13],
            phone:info[14],

            name:info[5],
            idCard:info[9],
            domicile:info[10] || "",
            degree:DEGREE[info[7]],
            farenphone:info[8],

            building:info[2],
            roomNumber:info[3],
            area:info[4]
        }

        // 新增企业信息
        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {uscc:excelData.uscc}, ["eId", "uscc", "enterpriseName"]);
        if (enterpriseDbInfo.eId) {
            let enterpriseInfo = { //基础信息表
                logonTime: excelData.logonTime
                
            };
            await operationalData(OPERATIONALDATATYPE.修改, TABLENAME.企业基础信息表, enterpriseInfo, {eId:enterpriseDbInfo.eId});

            console.log(`新增企业信息：eId：${enterpriseDbInfo.eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }
    }
    
    res.success({isSuccess:true});
}


export async function dataInitEnterprise(req, res) {// 配置字段映射关系 (Excel表头 -> 数据库字段)
    const FIELD_MAPPING = {
        /**企业信息 */
        '企业名称': 'enterpriseName',
        '统一社会信用代码': 'uscc',
        '注册日期': 'logonTime',
        '注册地址': 'logonAddress',

        /**用户信息 */
        '联系人': 'userName',
        '联系方式': 'phone',

        /**法人信息 */
        '姓名': 'name',
        '身份证': 'idCard',
        '户籍': 'domicile',
        '学历': 'degree',
        '政治面貌': 'politicalStatus',
        '手机号码': 'farenphone',


        /**孵化信息 */
        '入孵日期-开始时间': 'startTime',
        '入孵日期-截止时间': 'endTime',
        
        /**租赁信息 */
        '楼号': 'building',
        '室号': 'roomNumber',
        '面积': 'area',
        '月租金': 'rent'
    };
    

    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '在孵企业导入0619.xlsx' ));
    let dataList = sheetMap['Sheet1'];

    for (let i = 1; i < dataList.length; i++) {
        let info = dataList[i];
        let excelData = {
            enterpriseName:info[0],
            uscc:info[1],
            logonTime:info[15],

            userName:info[13],
            phone:info[14],

            name:info[5],
            idCard:info[9],
            domicile:info[10] || "",
            degree:DEGREE[info[7]],
            farenphone:info[8],

            building:info[2],
            roomNumber:info[3],
            area:info[4]
        }

        // 新增企业信息
        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {uscc:excelData.uscc}, ["eId", "uscc", "enterpriseName"]);
        let eId = "";
        if (!enterpriseDbInfo.eId) {
            eId = randomId(TABLEID.企业基础信息表);
            let pinyinName = getPinyinInitials(excelData.enterpriseName)
            
            let enterpriseInfo = { //基础信息表
                eId,
                enterpriseName:excelData.enterpriseName,
                pinyinName,
                uscc: excelData.uscc,
                // logonTime: excelData.logonTime,
                // logonAddress: excelData.logonAddress,
                state:CHANGESTATE.已通过,
                register:CHANGESTATE.已通过,
                
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业基础信息表, enterpriseInfo, {});

            console.log(`新增企业信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        } else {
            eId = enterpriseDbInfo.eId;
        }

        let zulinDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.租赁信息, {eId}, ["leaseId"]);
        if (!zulinDbInfo.leaseId) {
            let enterprise_leaseInfo:any = {//租赁信息表
                leaseId:randomId(TABLEID.租赁信息),
                eId,
                area:excelData.area || 0,
                building:excelData.building || '',
                roomNumber:excelData.roomNumber || '',
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.租赁信息, enterprise_leaseInfo, {});

            console.log(`新增租赁信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let fuhuaDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业孵化信息, {eId}, ["fId"]);
        if (!fuhuaDbInfo.fId) {
            let enterprise_fuhua:any = {//孵化表
                fId:randomId(TABLEID.企业孵化信息),
                eId,
                state:FUHUASTATE.实体孵化
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业孵化信息, enterprise_fuhua, {});

            console.log(`新增企业孵化信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let farenDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.法人信息表, {eId}, ["legalId"]);
        if (!farenDbInfo.legalId) {
            let enterprise_legal_personInfo:any = {//法人信息
                legalId:randomId(TABLEID.法人信息表),
                eId,
                name: excelData.name,
                idCard: excelData.idCard,
                domicile: excelData.domicile || '',
                degree: excelData.degree || null,
                // politicalStatus:politicalStatus || null,
                phone: excelData.farenphone || null,
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.法人信息表, enterprise_legal_personInfo, {});

            console.log(`新增法人信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let userDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业用户表, {eId}, ["uId"]);
        if (!userDbInfo.uId) {
            // 准备添加用户数据
            let addUserData = {
                uId:randomId(TABLEID.企业用户表),
                userName:excelData.userName,
                phone:excelData.phone+'',
                uscc: excelData.uscc,
                pwd:'111111',
                eId
            };
            // 添加到数据库
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业用户表, addUserData, {});

            console.log(`新增企业用户信息：eId：${eId}，企业名称：${excelData.enterpriseName}，联系人：${excelData.userName}，手机号：${excelData.phone}`);
        }
    }
    
    res.success({isSuccess:true});
}



export async function data0620(req, res) {
    // 配置字段映射关系 (Excel表头 -> 数据库字段)
    const FIELD_MAPPING = {
        /**企业信息 */
        '企业名称': 'enterpriseName',
        '统一社会信用代码': 'uscc',
        '注册日期': 'logonTime',
        '注册地址': 'logonAddress',

        /**用户信息 */
        '联系人': 'userName',
        '联系方式': 'phone',

        /**法人信息 */
        '姓名': 'name',
        '身份证': 'idCard',
        '户籍': 'domicile',
        '学历': 'degree',
        '政治面貌': 'politicalStatus',
        '手机号码': 'farenphone',


        /**孵化信息 */
        '入孵日期-开始时间': 'startTime',
        '入孵日期-截止时间': 'endTime',
        
        /**租赁信息 */
        '楼号': 'building',
        '室号': 'roomNumber',
        '面积': 'area',
        '月租金': 'rent'
    };
    
    //1.获取数据库现有的企业数据
    let enterpriseList = await selectData(OPERATIONALDATATYPE.查询多个, TABLENAME.企业基础信息表, {}, ["eId", "uscc", "enterpriseName"]);

    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '在孵企业导入0619.xlsx' ));
    let dataList = sheetMap['Sheet1'];
    
    // 3. 数据预处理和验证
    if (!dataList || dataList.length === 0) {
        return res.status(400).json({ code: 400, message: 'Excel文件中没有数据' });
    }
    
    // 提取表头
    const headers = dataList[0];
    // 4. 创建字段索引映射
    const fieldIndices = {};
    Object.keys(FIELD_MAPPING).forEach(fieldName => {
        const index = headers.indexOf(fieldName);
        if (index !== -1) {
            fieldIndices[fieldName] = index;
        }
    });
    // 5. 创建数据库映射关系
    const dbUsccMap = new Map();
    enterpriseList.forEach(enterprise => {
        dbUsccMap.set(enterprise.uscc, enterprise);
    });

    // 6. 匹配和更新数据
    const matchedRecords = [];
    const unmatchedRecords = [];
    const newEnterprises = [];

    // 从第二行开始处理数据（跳过表头）
    for (let i = 1; i < dataList.length; i++) {
        const row = dataList[i];
        if (!row || row.length === 0) continue;

        // 自动提取所有配置的字段
        const excelData:any = {};
        Object.entries(FIELD_MAPPING).forEach(([excelField, dbField]) => {
            if (fieldIndices[excelField] !== undefined) {
                excelData[dbField] = row[fieldIndices[excelField]];
            }
        });

        const uscc = excelData.uscc;
        const enterpriseName = excelData.enterpriseName;
        const contactPerson = excelData.userName;
        const contactPhone = excelData.phone;
        if (!uscc) {
            unmatchedRecords.push({
                enterpriseName: enterpriseName,
                reason: '缺少统一社会信用代码'
            });
            continue;
        }

        const dbEnterprise = dbUsccMap.get(uscc);

        if (dbEnterprise) {
            // 检查是否需要更新
            let userInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业用户表, { eId: dbEnterprise.eId }, ["userName", "phone"]);

            if (userInfo.userName !== contactPerson || userInfo.phone !== contactPhone) {
                // 准备更新数据
                const updateData = {
                    userName: contactPerson,
                    phone: contactPhone
                };
                // 修改到数据库
                await operationalData(OPERATIONALDATATYPE.修改, TABLENAME.企业用户表, updateData, { eId: dbEnterprise.eId });
                console.log(`修改数据成功：eId：${dbEnterprise.eId}，企业名称：${dbEnterprise.enterpriseName}，联系人：${contactPerson}，手机号：${contactPhone}`);
                matchedRecords.push({
                    uscc: uscc,
                    enterpriseName: dbEnterprise.enterpriseName,
                    oldContact: {
                        person: dbEnterprise.contactPerson,
                        phone: dbEnterprise.contactPhone
                    },
                    newContact: {
                        person: contactPerson,
                        phone: contactPhone
                    }
                });
            } else {
                // 新增企业信息
                let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {uscc}, ["eId", "uscc", "enterpriseName"]);
                let eId = "";
                if (!enterpriseDbInfo.eId) {
                    eId = randomId(TABLEID.企业基础信息表);
                    let pinyinName = getPinyinInitials(enterpriseName)
                    
                    let enterpriseInfo = { //基础信息表
                        eId,
                        enterpriseName:enterpriseName,
                        pinyinName,
                        uscc: uscc,
                        // logonTime: excelData.logonTime,
                        // logonAddress: excelData.logonAddress,
                        state:CHANGESTATE.已通过,
                        register:CHANGESTATE.已通过,
                        
                    };

                    await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业基础信息表, enterpriseInfo, {});
                } else {
                    eId = enterpriseDbInfo.eId;
                }
                
                let zulinDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.租赁信息, {eId}, ["leaseId"]);
                if (!zulinDbInfo.leaseId) {
                    let enterprise_leaseInfo:any = {//租赁信息表
                        leaseId:randomId(TABLEID.租赁信息),
                        eId,
                        area:excelData.area || 0,
                        building:excelData.building || '',
                        roomNumber:excelData.roomNumber || '',
                        rent:excelData.rent || 0
                    };
                    await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.租赁信息, enterprise_leaseInfo, {});
                }
                
                let fuhuaDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业孵化信息, {eId}, ["fId"]);
                if (!fuhuaDbInfo.fId) {
                    let enterprise_fuhua:any = {//孵化表
                        fId:randomId(TABLEID.企业孵化信息),
                        eId,
                        state:FUHUASTATE.实体孵化
                    };
                    await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业孵化信息, enterprise_fuhua, {});
                }
                
                let farenDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.法人信息表, {eId}, ["legalId"]);
                if (!farenDbInfo.legalId) {
                    let enterprise_legal_personInfo:any = {//法人信息
                        legalId:randomId(TABLEID.法人信息表),
                        eId,
                        name: excelData.name,
                        idCard: excelData.idCard,
                        // domicile: excelData.domicile || '',
                        // degree: excelData.degree || null,
                        // politicalStatus:politicalStatus || null,
                        phone: excelData.farenphone || null,
                    };
                    await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.法人信息表, enterprise_legal_personInfo, {});
                }
                
                let userDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业用户表, {eId}, ["uId"]);
                if (!userDbInfo.uId) {
                    // 准备添加用户数据
                    let addUserData = {
                        uId:randomId(TABLEID.企业用户表),
                        userName:contactPerson,
                        phone:contactPhone+'',
                        uscc: uscc,
                        pwd:'111111',
                        eId
                    };
                    // 添加到数据库
                    await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业用户表, addUserData, {});
                }
                
                console.log(`添加数据成功：eId：${eId}，企业名称：${enterpriseName}，联系人：${contactPerson}，手机号：${contactPhone}`);
            }
        }
    }
    res.success({isSuccess:true});
}


export async function data0709(req, res) {// 配置字段映射关系 (Excel表头 -> 数据库字段)
    const FIELD_MAPPING = {
        /**企业信息 */
        '企业名称': 'enterpriseName',
        '统一社会信用代码': 'uscc',
        // '注册日期': 'logonTime',
        '注册地址': 'logonAddress',

        /**用户信息 */
        '联系人': 'userName',
        '联系方式': 'phone',

        /**法人信息 */
        // '姓名': 'name',
        // '身份证': 'idCard',
        // '户籍': 'domicile',
        // '学历': 'degree',
        // '政治面貌': 'politicalStatus',
        // '手机号码': 'farenphone',


        /**孵化信息 */
        // '入孵日期-开始时间': 'startTime',
        // '入孵日期-截止时间': 'endTime',
        
        /**租赁信息 */
        '楼号': 'building',
        '室号': 'roomNumber',
        // '面积': 'area',
        // '月租金': 'rent'
    };
    

    //2.读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '在孵企业导入20250709.xlsx' ));
    let dataList = sheetMap['Sheet1'];

    for (let i = 1; i < dataList.length; i++) {
        let info = dataList[i];
        let excelData = {
            enterpriseName:info[0],
            uscc:info[1],
            // logonTime:info[15],

            userName:info[4],
            phone:info[5],

            // name:info[5],
            // idCard:info[9],
            // domicile:info[10] || "",
            // degree:DEGREE[info[7]],
            // farenphone:info[8],

            building:info[2],
            roomNumber:info[3],
            // area:info[4]
        }

        // 新增企业信息
        let enterpriseDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业基础信息表, {uscc:excelData.uscc}, ["eId", "uscc", "enterpriseName"]);
        let eId = "";
        if (!enterpriseDbInfo.eId) {
            eId = randomId(TABLEID.企业基础信息表);
            let pinyinName = getPinyinInitials(excelData.enterpriseName)
            
            let enterpriseInfo = { //基础信息表
                eId,
                enterpriseName:excelData.enterpriseName,
                pinyinName,
                uscc: excelData.uscc,
                state:CHANGESTATE.已通过,
                register:CHANGESTATE.已通过,
                
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业基础信息表, enterpriseInfo, {});

            console.log(`新增企业信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        } else {
            eId = enterpriseDbInfo.eId;
        }

        let zulinDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.租赁信息, {eId}, ["leaseId"]);
        if (!zulinDbInfo.leaseId) {
            let enterprise_leaseInfo:any = {//租赁信息表
                leaseId:randomId(TABLEID.租赁信息),
                eId,
                // area:excelData.area || 0,
                building:excelData.building || '',
                roomNumber:excelData.roomNumber || '',
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.租赁信息, enterprise_leaseInfo, {});

            console.log(`新增租赁信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let fuhuaDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业孵化信息, {eId}, ["fId"]);
        if (!fuhuaDbInfo.fId) {
            let enterprise_fuhua:any = {//孵化表
                fId:randomId(TABLEID.企业孵化信息),
                eId,
                state:FUHUASTATE.实体孵化
            };
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业孵化信息, enterprise_fuhua, {});

            console.log(`新增企业孵化信息：eId：${eId}，企业名称：${excelData.enterpriseName}，uscc：${excelData.uscc}`);
        }

        let userDbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.企业用户表, {eId}, ["uId"]);
        if (!userDbInfo.uId) {
            // 准备添加用户数据
            let addUserData = {
                uId:randomId(TABLEID.企业用户表),
                userName:excelData.userName,
                phone:excelData.phone+'',
                uscc: excelData.uscc,
                pwd:'111111',
                eId
            };
            // 添加到数据库
            await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业用户表, addUserData, {});

            console.log(`新增企业用户信息：eId：${eId}，企业名称：${excelData.enterpriseName}，联系人：${excelData.userName}，手机号：${excelData.phone}`);
        }
    }
    
    res.success({isSuccess:true});
}


export async function data1210(req, res) {
    let 入驻信息add = [
        {
            info_enterId:randomId(TABLEID.入驻信息表),
            building:'1',
            occupancyRate:"0%",
            enteredEnterprises:"0"
        },
        {
            info_enterId:randomId(TABLEID.入驻信息表),
            building:'3',
            occupancyRate:"0%",
            enteredEnterprises:"0"
        },
        {
            info_enterId:randomId(TABLEID.入驻信息表),
            building:'4',
            occupancyRate:"0%",
            enteredEnterprises:"0"
        },
    ]
    await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.入驻信息表, 入驻信息add, {});

    //读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '在孵企业导入1210.xlsx' ));
    let dataList = sheetMap['Sheet1'];

    let fuhuaqiExcelData = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '在孵企业导入_孵化器提供1210.xlsx' ));
    let data2List = fuhuaqiExcelData.sheetMap['雨艺孵化器孵化企业明细'];

    let dataMap = {};
    
    for (let i= 1; i < dataList.length; i++) {
        let subList = dataList[i];
        let enterpriseName = subList[0];
        let RAS = subList[1];
        let zhuceziben = subList[3];
        let shijiaoziben = subList[4];
        let logonTime = subList[5];
        let uscc = subList[6];
        let logonAddress = [subList[8], subList[9], subList[10], subList[7].replace("上海市浦东新区", "")];
        let gengDuoDianHua = subList[12];
        let mail = subList[13];
        let moreMail = subList[14];
        let enterpriseType = subList[15];
        let zhuCeHao = subList[17];
        let zuZhiJiGouDaiMa = subList[18];
        let yingYeQiXian = subList[21];
        let qiYeGuiMo = subList[30];
        let tongXinDiZhi = JSON.stringify([subList[8], subList[9], subList[10],subList[34].replace("上海市浦东新区", "")]);
        let jianJie = subList[35];
        let jingYingFanWei = subList[36];
        let dengJiJiGuan = subList[37];
        let naShuiRenZiZhi = subList[38];
        //法人
        let faRenName = subList[2];
        //参保人数
        let canbaoNumber = subList[19] && subList[19] != '-' ? subList[19] : '';
        let canbaoYear = subList[20] && subList[20] != '-' ? subList[20] : '';

        dataMap[enterpriseName] = {
            enterpriseName,
            RAS,
            zhuceziben,
            shijiaoziben,
            uscc,
            logonAddress,
            gengDuoDianHua,
            mail,
            moreMail,
            enterpriseType,
            zhuCeHao,
            zuZhiJiGouDaiMa,
            yingYeQiXian,
            qiYeGuiMo,
            tongXinDiZhi,
            jianJie,
            dengJiJiGuan,
            naShuiRenZiZhi,
            faRenName,
            canbaoNumber,
            canbaoYear,
            jingYingFanWei,
            logonTime
        }


    }

    let 去重={};
    for (let i = 1; i < data2List.length; i++) {
        let subList = data2List[i];
        let phone = subList[14];
        if (!去重[phone]) 去重[phone] = 1;
        else console.log(phone, "重复了");

    }

    for (let i = 1; i < data2List.length; i++) {
        let subList = data2List[i];
        let enterpriseName = subList[1];
        let qccInfo = dataMap[enterpriseName];

        let eId = randomId(TABLEID.企业基础信息表);
        let pinyinName = getPinyinInitials(qccInfo.enterpriseName)
        let enterpriseInfo = {//基础信息表
            eId,
            enterpriseName:qccInfo.enterpriseName,
            pinyinName,
            RAS:qccInfo.RAS,
            zhuceziben:qccInfo.zhuceziben,
            shijiaoziben:qccInfo.shijiaoziben,
            uscc:qccInfo.uscc,
            logonAddress:JSON.stringify(qccInfo.logonAddress),
            gengDuoDianHua:qccInfo.gengDuoDianHua,
            mail:qccInfo.mail,
            moreMail:qccInfo.moreMail,
            enterpriseType:qccInfo.enterpriseType,
            zhuCeHao:qccInfo.zhuCeHao,
            zuZhiJiGouDaiMa:qccInfo.zuZhiJiGouDaiMa,
            yingYeQiXian:qccInfo.yingYeQiXian,
            qiYeGuiMo:qccInfo.qiYeGuiMo,
            tongXinDiZhi:qccInfo.tongXinDiZhi,
            jianJie:qccInfo.jianJie,
            dengJiJiGuan:qccInfo.dengJiJiGuan,
            naShuiRenZiZhi:qccInfo.naShuiRenZiZhi,
            jingYingFanWei:qccInfo.jingYingFanWei,
            logonTime:qccInfo.logonTime,
            shuiHao:subList[2],
            liaison:subList[13],
            liaisonPhone:subList[14]+'',
            shuiWuJu:subList[17] || '',
            state:CHANGESTATE.已通过,
            register:CHANGESTATE.已通过,
            
        };
        
        let enterprise_leaseInfo:any = {//租赁信息表
            leaseId:randomId(TABLEID.租赁信息),
            eId,
            area:subList[4] ? subList[4] : 0,
            building:subList[45] || '',
            // roomNumber:subList[4] || '',
            roomNumber:subList[3] || '',
            bankName:subList[18] || '',
        };

        let enterprise_fuhua:any = {//孵化表
            fId:randomId(TABLEID.企业孵化信息),
            eId,
            jiShuHeTongJiaoYi:subList[30] || 0,
            state:FUHUASTATE.实体孵化
        };

        let enterprise_legal_personInfo:any = {//法人信息
            legalId:randomId(TABLEID.法人信息表),
            eId,
            name:subList[5],
            phone:subList[8],
            idCard:subList[9],
            domicile:subList[10] || '',
        };
        if (subList[7] && DEGREE[subList[7]]) enterprise_legal_personInfo.degree = DEGREE[subList[7]];
        if (subList[11] && ZZMM[subList[11]]) {
            enterprise_legal_personInfo.politicalStatus =ZZMM[subList[11]];
        }
        if (subList[10]) {
            enterprise_legal_personInfo.domicile = subList[10];
        }

        if (subList[40]) {
            let zlsjList = subList[40].split("-");
            enterprise_leaseInfo.startTime = getMySqlMs(new Date(zlsjList[0]).valueOf());
            enterprise_leaseInfo.endTime = getMySqlMs(new Date(zlsjList[1]).valueOf());
        }
        

        if (subList[41] && subList[41] != '/') {
            let mzqList = subList[41].split('-');
            enterprise_leaseInfo.rentFreeStart = getMySqlMs(new Date(mzqList[0]).valueOf());
            enterprise_leaseInfo.rentFreeEnd = getMySqlMs(new Date(mzqList[1]).valueOf());
        }

        let rfrqList = subList[42].split('-');

        enterprise_fuhua.startTime = getMySqlMs(new Date(rfrqList[0]).valueOf());
        enterprise_fuhua.endTime = getMySqlMs(new Date(rfrqList[1]).valueOf());


        let enterprise_userInfo = {//登录账户表
            uId:randomId(TABLEID.企业用户表),
            userName:subList[13],
            phone:subList[14]+'',
            uscc:qccInfo.uscc,
            pwd:'111111',
            eId
        };

        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业基础信息表, enterpriseInfo, {});
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.租赁信息, enterprise_leaseInfo, {});
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业孵化信息, enterprise_fuhua, {});
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.法人信息表, enterprise_legal_personInfo, {});
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业用户表, enterprise_userInfo, {});

        // if(qccInfo.canbaoYear && qccInfo.canbaoNumber) {
        //     let enterprise_canbaoInfo = {
        //         cId:randomId(TABLEID.参保记录),
        //         eId,
        //         annual:getMySqlMs(`${ qccInfo.canbaoYear.match(/\d+/g)[0]}-01-01 00:00:00`),
        //         zhuanKe:0,
        //         benKe:0,
        //         shuoshi:0,
        //         boShi:0,
        //         boshiyishang:0,
        //         qiTa:qccInfo.canbaoNumber
        //     }
        //     await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.参保记录, enterprise_canbaoInfo, {});
        // }
       

        // if (subList[31]) {
            // for(let i = 0; i < parseInt(subList[31]); i++) {
            //     let enterprise_2022iprInfo = {
            //         iprId:randomId(TABLEID.知识产权),
            //         eId,
            //         year:getMySqlMs("2022-01-01 00:00:00"),
            //         number:1,
            //         iprType:IPRALLTYPE.软件著作权,
            //         selectedValue:null,
            //         iprName:null,
            //         iprUrl:null,
            //         // softwareCopyrightCount:null,
            //         // inventionPatentCount:null,
            //         // overseasPatentCount:null,
            //         // plantVarietyCount:null,
            //         // icLayoutCount:null,
    
            //     }
            //     await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.知识产权, enterprise_2022iprInfo, {});
            // }
        // }
        // if (subList[32]) {
            // for(let i = 0; i < parseInt(subList[32]); i++) {
            //     let enterprise_2023iprInfo = {
            //         iprId:randomId(TABLEID.知识产权),
            //         eId,
            //         year:getMySqlMs("2023-01-01 00:00:00"),
            //         number:1,
            //         iprType:IPRALLTYPE.软件著作权,
            //         selectedValue:null,
            //         iprName:null,
            //         iprUrl:null,
            //         // softwareCopyrightCount:null,
            //         // inventionPatentCount:null,
            //         // overseasPatentCount:null,
            //         // plantVarietyCount:null,
            //         // icLayoutCount:null,
            //     }
            //     await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.知识产权, enterprise_2023iprInfo, {});
            // }
            
        // }
        // if (subList[33]) {
            // for(let i = 0; i < parseInt(subList[33]); i++) {
            //     let enterprise_2022patent = {
            //         iprId:randomId(TABLEID.知识产权),
            //         eId,
            //         year:getMySqlMs("2022-01-01 00:00:00"),
            //         number:1,
            //         iprType:IPRALLTYPE.发明专利,
            //         selectedValue:null,
            //         iprName:null,
            //         iprUrl:null,
            //     }
            //     await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.知识产权, enterprise_2022patent, {});
            // }
        // }
        // if (subList[34]) {
            // for(let i = 0; i < parseInt(subList[34]); i++) {
            //     let enterprise_2022patent = {
            //         iprId:randomId(TABLEID.知识产权),
            //         eId,
            //         year:getMySqlMs("2023-01-01 00:00:00"),
            //         number:1,
            //         iprType:IPRALLTYPE.发明专利,
            //         selectedValue:null,
            //         iprName:null,
            //         iprUrl:null,
            //     }
            //     await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.知识产权, enterprise_2022patent, {});
            // }
        // }

        // if (subList[35] || subList[36] || subList[37] || subList[38]) {
        //     let enterprise_qualification:any = {
        //         qId:randomId(TABLEID.企业资质),
        //         eId,
        //     }
        //     if (subList[35]) enterprise_qualification.kxTime =  getMySqlMs(`${ subList[35].match(/\d+/g)[0]}-01-01 00:00:00`);
        //     if (subList[36]) enterprise_qualification.zjtxTime =  getMySqlMs(`${ subList[36].match(/\d+/g)[0]}-01-01 00:00:00`);
        //     if (subList[37]) enterprise_qualification.xjrTime =  getMySqlMs(`${ subList[37].match(/\d+/g)[0]}-01-01 00:00:00`);
        //     if (subList[38]) enterprise_qualification.gaoXinJiShuTime =  getMySqlMs(`${ subList[38].match(/\d+/g)[0]}-01-01 00:00:00`);
        //     await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.企业资质, enterprise_qualification, {});
        // }

    }
   
    console.log("数据导入成功");
    res.success({isSuccess:true});
}


export async function addAnswer(req, res) {
    //读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '高新企业创新能力评价题目V2.xlsx' ));
    let dataList = sheetMap['Sheet1']; //题目

    for (let i= 1; i < dataList.length; i++) {
        let subList = dataList[i];
        // let dbInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.高新企业创新能力评价答题, {subject:subList[2]}, ["gaId"]);
        // if (dbInfo && dbInfo.gaId) break;

        let gaId = randomId(TABLEID.高新企业创新能力评价答题);
        
        let isMultipleChoice = STATE.否;
        if (subList[3] == "是") isMultipleChoice = STATE.是;

        let answerInfo = {
            gaId,
            answerType:subList[0],
            sort:subList[1],
            subject:subList[2],
            isMultipleChoice,
            questionType:subList[4],
        }
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.高新企业创新能力评价答题, answerInfo, {});
        console.log(`写入成功：${subList[2]}`)
    }

    
    let dataList2 = sheetMap['Sheet2']; //答案

    for (let i= 1; i < dataList2.length; i++) {
        let subList = dataList2[i];
        let answerInfo = await selectData(OPERATIONALDATATYPE.查询单个, TABLENAME.高新企业创新能力评价答题, {sort:subList[0]}, ["gaId"]);
        let goId = randomId(TABLEID.高新企业创新能力评价选项);

        let notes = "";
        if (subList[4]) notes = subList[4];
        let answer = "";
        if (subList[2]) answer = subList[2];
        
        let optionInfo = {
            goId,
            gaId:answerInfo.gaId,
            sort:subList[1],
            answer,
            option:subList[3],
            notes,
            score:parseInt(subList[5]),
        }
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.高新企业创新能力评价选项, optionInfo, {});
        console.log(`写入成功：${subList[2]}`)
    }

    res.success({isSuccess:true});
}


export async function addZuFangAnswer(req, res) {
    //读取文件
    let {sheetMap} = getExcel(path.join(__dirname.substring(0,__dirname.indexOf("out")), "res", '高新企业创新能力评价题目V2.xlsx' ));
    let dataList = sheetMap['Sheet3']; //题目

    for (let i= 1; i < dataList.length; i++) {
        let subList = dataList[i];

        let zaId = randomId(TABLEID.租房补贴企业自评);

        let answerInfo = {
            zaId,
            answerType:subList[0],
            sort:subList[1],
            subject:subList[2],
        }
        await operationalData(OPERATIONALDATATYPE.增加, TABLENAME.租房补贴企业自评, answerInfo, {});
        console.log(`写入成功：${subList[2]}`)
    }

    res.success({isSuccess:true});
}


export async function qccDatat0519(req, res) {
    console.log("开始清洗企查查数据");
    // qccData();
    updateQCCDataTask();
    res.success({isSuccess:true})
}


/**
 * 获取当个excel文件数据
 * @param filePath 
 * @returns 
 */
function getExcel(filePath) {
    const workSheetsFromFile = xlsx.parse(filePath);
    let sheetMap = {};
    let sheetList = [];

    for (let i = 0; i < workSheetsFromFile.length; i++) {
        let sheetInfo = workSheetsFromFile[i];
        sheetMap[sheetInfo.name] = sheetInfo.data;
        sheetList.push(sheetInfo);
    }

    return {sheetMap, sheetList}  
}

