const xlsx = require('node-xlsx');
const path = require('path');
const XLSX = require('xlsx');

import { extractData } from "../util/piecemeal";
import { onceSheetBecomeOfblockData } from "../util/analysisExcel";
import { findEnterpriseByUscc, findEnterpriseList, findUsccList } from "../data/enterprise/enterprise";
import { BizError } from "../util/bizError";
import pyfl from 'pyfl';
import moment = require("moment");
import { FUHUASTATE, INDUSTRY } from "../config/enum";
import { addressIsInZJ, getAddressLatAndLng } from "../tools/transCode";
import { changeAddToString } from "../tools/system";


export async function getZj() {
    let fileSheet = getExcelData("107家孵化器.xlsx", "Sheet1", "张江科学城孵化器概况");
    let fileExcel = fileSheet["张江科学城孵化器概况"].dataList;

    let fhqjwd = [];
    for (let i = 0; i < fileExcel.length; i++) {
        let fuhuaqi = {};
        let dz = fileExcel[i].dz;
        if (dz) {
            let jwd = await getAddressLatAndLng(dz, "上海", "");
            // fhqjwd[fileExcel[i].qymc] = jwd;
            fuhuaqi = jwd;
            fuhuaqi["企业名称"] = fileExcel[i].qymc;
        }
        fhqjwd.push(fuhuaqi);
    }
    
    writeExcel(fhqjwd, "120家孵化器经纬度.xlsx");
    console.log();
}








/**
 * 比较两个表格
 */
export async function compareFiles() {
    let fileExcel1 = getExcelData("文件名称", "Sheet1", "数据块名称");
    let fileExcel2 = getExcelData("文件名称", "Sheet1", "数据块名称");

    let file1 = getFilesData(fileExcel1, "数据块名称", "name");
    let file2 = getFilesData(fileExcel2, "数据块名称", "name");

    let data = [];
    for( let key in file2) {
        if(!(key in file1)) {
            data.push(key);
        }
    }

    console.log("在表1(file1)找不到但是出现在表2(file2)的数据", data);
    console.log();
}



function getFilesData(data, key, name) {
    let result = {};
    data[key].dataList.forEach( info => {
        result[info[name]] = info;
    })

    return result;
}




export async function screenData() {
    let fileSheet = getExcelData("企业数据校验结果.xlsx", "Sheet1", "数据校验");
    let fileExcel = fileSheet["数据校验"].dataList;
    let fileData = {};
    fileExcel.forEach( info => {
        let usccname = info.tyshxydm + info.ywjdrmc;
        fileData[usccname] = info;
    })

    let dbData = await enterpriseList();

    /**数据库存在，表格不存在的数据 */
    let inDbNotFile = [];
    let inDbNotFileCount = dbData.resultCount;
    for( let key in dbData.result) {
        if(!(key in fileData)) {
            inDbNotFile.push(dbData.result[key]);
            inDbNotFileCount[key] = 0;
        }
    }
    writeExcel(inDbNotFile, "数据库存在表格不存在.xlsx");
    console.log("数据库存在，表格不存在的数据", inDbNotFile);

    /**表格存在，数据库不存在的数据 */
    let inFileNotDb = [];
    for( let key in fileData) {
        if(!(key in dbData.result)) {
            inFileNotDb.push({name:fileData[key].ywjdrmc, uscc:fileData[key].tyshxydm});
        }
    }
    writeExcel(inFileNotDb, "表格存在数据库不存在.xlsx");
    console.log("表格存在，数据库不存在的数据", inFileNotDb);

    console.log();
}


export async function enterpriseList() {
    let selectParam = { "$or": [{"$and":[{"draftLock":true}, {"taskId":{"$ne":null}}]}, {"$and":[{"draftLock":false}, {"taskId":null}]}] }; //未提交的企业不能查看

    let dataBaseList = await findEnterpriseList(selectParam);

    let listConf = {
        name:{key:"企业名称"},
        uscc:{key:"统一信用代码 也是登录账号 不可修改"},
    }

    /**组合返回结果 */
    let result = {};
    let resultCount = {};
    dataBaseList.forEach( info => {
        /**截取返回数据 */ 
        let changeData:any = extractData(listConf, info, true);
        let usccname = info.uscc + info.name;

        result[usccname] = changeData;
        resultCount[usccname] = 1;
    });

    return {result, resultCount};
}


function getExcelData(fileName, sheetName, titleList) {
    let result = {};

    let fileData = onceSheetBecomeOfblockData(fileName, sheetName);
    let titltListConfig = {};
    for (let i = 0; i < fileData.length; i++) {
        let {blockData, blockTitle} = fileData[i];
        if (titleList.indexOf(blockTitle) > -1) {
            let titleList = titltListConfig[blockTitle] || [];
            result[blockTitle] = planarArrForObjectList(blockData, 0, titleList);
        }

    }

    return result;
}


function writeExcel(data, name) {
    // 创建一个新的工作簿和工作表  
    const workbook = XLSX.utils.book_new();
    const worksheet = XLSX.utils.json_to_sheet(data); // transactions是交易数据数组  

    // 将工作表添加到工作簿中  
    XLSX.utils.book_append_sheet(workbook, worksheet, 'Sheet1');

    // 将工作簿写入文件  
    const excelFilePath = name; // Excel文件的路径  
    XLSX.writeFile(workbook, excelFilePath);

    console.log(`Excel文件已写入: ${excelFilePath}`);
}


/**
 * planarArrForObjectList  解析二维数组
 * @param planarArr 二维数组 例子:[[title1, title2, title3, title4],[value1, value2, value3, value4],[value5, value6, value7, value8]]
 * @param titleIndex  标题集合所在第一层数组的下标 
 * @param titleList 自定义标题列表 不传则会取每个title的中文首字母作为key
 * @returns {"dataList":[{title1:value1, title2:value2, title3:value3, title4:value4}, {title1:value5, title2:value6, title3:value7, title4:value8}], titleList:[]}
 */
function planarArrForObjectList(planarArr, titleIndex = 0, titleList = []) {
    let dataTitleList = [];
    let dataMaxLength = 0;
    for (let i = titleIndex; i< planarArr.length; i++) {
        let subList = planarArr[i];
        if (i == titleIndex) {
            dataTitleList = subList;
            continue;
        }
        dataMaxLength = Math.max(subList.length, dataMaxLength);
    }

    let useTitleList = !titleList.length ? changeChineseTitleIntoEnglish(dataTitleList) : titleList;
    if (dataMaxLength == 2 && !titleList.length) useTitleList = ["key", "value"];
    
    if (dataTitleList.length < dataMaxLength ) throw new BizError(`解析时数据异常 数据长度：${dataMaxLength} 大于 标题长度${dataTitleList}`);
    if (titleList.length && titleList.length < dataMaxLength) throw new BizError(`解析时数据异常 传入标题长度${titleList.length} 数据最大长度${dataMaxLength}`);

    let dataList = [];
    for (let i = (titleIndex + 1); i< planarArr.length; i++) {
        let subList = planarArr[i];
        let addInfo:any = {};
        subList.forEach((item, index) => {
            let key = useTitleList[index];
            let value = item;
            addInfo[key] = value;
        });
        dataList.push(addInfo);
    }

    return {dataList, titleList:useTitleList };
}

function changeChineseTitleIntoEnglish(titles) {
    let result = [];
    titles.forEach(chineseStr => {
        result.push(pyfl(chineseStr).toLowerCase());
    });
    return result;
}
