// 云函数入口文件
const cloud = require('wx-server-sdk')
cloud.init({
    // API 调用都保持和云函数当前所在环境一致
    env: '隐藏'
})
const db = cloud.database()
//exceljs 安装：npm i exceljs
const ExcelJS = require('exceljs');
//引入superagent  安装 ：npm i superagent
const superagent = require('superagent');
// 云函数入口函数
exports.main = async (event, context) => {
    const wxContext = cloud.getWXContext()
    //查询要成表格的数据 集合名记得改
    const dataList = await db.collection('priceList').get()
    //获取查询到的数据
    const data = dataList.data
    //新建一个工作簿
    const workbook = new ExcelJS.Workbook();
    //创建一个工作表
    const worksheet = workbook.addWorksheet('Sheet 1');
    //设置第一行的行高
    worksheet.properties.defaultRowHeight = 75;
    //设置第一行的列宽
    worksheet.properties.defaultColWidth = 12;
    //设置第一行的文字垂直居中
    worksheet.getRow(1).alignment = {
        vertical: 'middle',
        horizontal: 'center'
    };
    //设置第一行的文字大小加粗
    worksheet.getRow(1).font = {
        size: 16,
        bold: true
    };
    //创建表头信息
    worksheet.columns = [{
        header: '日期',
        key: 'id'
    }, {
        header: '收支',
        key: 'url'
    }, {
        header: '收支类型',
        key: 'url'
    }, {
        header: '账户',
        key: 'url'
    }, {
        header: '金额',
        key: 'url'
    }, {
        header: '项目',
        key: 'url'
    }, {
        header: '经办人',
        key: 'url'
    }, {
        header: '联系电话',
        key: 'url'
    }, {
        header: '备注',
        key: 'url'
    }, {
        header: '发票图片',
        key: 'url'
    }];
    //循环往工作表里加数据
    for (let rowIndex in data) {
        const rowcontent = []
        //获取图片链接
        var urls = encodeURI('https://6465-dev-4gxhzi23098ff1be-1301122876.tcb.qcloud.la/problemFileList/1739515736882.png?sign=ecb9b281e9168a0a6beac76775165f22&t=1739717943' + '/sf200')
        //获取urls的图片链接转化成Base64
        const img2Base64 = await new Promise(async function (resolve, reject) {
            const url = urls;
            await superagent.get(url).buffer(true).parse((res) => {
                let buffer = [];
                res.on('data', (chunk) => {
                    buffer.push(chunk);
                });
                res.on('end', () => {
                    const data = Buffer.concat(buffer);
                    const base64Img = data.toString('base64');
                    resolve('data:image/png;base64,' + base64Img)
                });
            });
        })
        //设置所有导出的数据文字居中
        worksheet.getRow(rowIndex - 1 + 3).alignment = {
            vertical: 'middle',
            horizontal: 'center'
        };
        //把img2Base64的数据生成imageId2，并添加图片到工作表
        const imageId2 = workbook.addImage({
            base64: img2Base64,
            extension: 'png',
        });
        //获取imageId2，修改上面工作表里的图片大小位置
        worksheet.addImage(imageId2, {
            tl: {
                col: 9,
                row: rowIndex - 1 + 2
            },
            ext: {
                width: 100,
                height: 100
            },
            editAs: 'undefined'
        });
        rowcontent.push(data[rowIndex].timeYMD);
        rowcontent.push(data[rowIndex].selectA);
        rowcontent.push(data[rowIndex].selectB);
        rowcontent.push(data[rowIndex].selectC);
        rowcontent.push(data[rowIndex].moneyBind);
        rowcontent.push(data[rowIndex].xiangmu);
        rowcontent.push(data[rowIndex].user);
        rowcontent.push(data[rowIndex].phone);
        rowcontent.push(data[rowIndex].problemInput);
        //更新表格
        worksheet.addRow(rowcontent);
    }
    //生成表格
    const buffer = await workbook.xlsx.writeBuffer();
    //上传到云存储
    return await cloud.uploadFile({
        cloudPath: 'nhb/' + Date.now() + '.xlsx',
        fileContent: buffer,
    })
}