teaching-api/certificate.js

// =============================================================================
var express = require("express");
var request = require("request");
var bcrypt = require("bcryptjs");
let axios = require("axios");
var router = express.Router(); // get an instance of the express Router
const querystring = require("querystring");
const dateformat = require('./comment/dateformat');
var mysql = require("./mysql");
const PDFDocument = require('pdfkit');
// const PDFDocument = require('@hollandjake/pdfkit-table');
const fs = require('fs');
const _mysqlLabor = ["183.36.26.8", "sc_app"]; // 提交的使用用这两个edu數據庫信息

/**
 * 获取某年的证书设置
 */
router.get('/getseeting', async (req, res, next) => {
    let year = req.query.year || new Date().getFullYear();
    let data = await mysql.sqlHandler(_mysqlLabor, 'select * from certificate_seeting where year=' + year);
    if (data && data.length > 0)
        res.json({
            status: true,
            data: data[0]
        });
    else
        res.json({
            status: false,
            err: "没有数据"
        })
})

async function saveSetting(data) {
    try {
        data.daterange_start = dateformat.dateFormat(data.daterange_start);
        data.daterange_end = dateformat.dateFormat(data.daterange_end);
        let res = await mysql.sqlHandler(_mysqlLabor, 'select * from certificate_seeting where year=' + data.year);
        if (res && res.length > 0) {
            return await mysql.sqlHandler(_mysqlLabor, `update certificate_seeting set daterange_start='${data.daterange_start}',daterange_end='${data.daterange_end}',assclasshour=${data.assclasshour},minclasshour=${data.minclasshour} where year=${data.year}`);
        } else {
            return await mysql.sqlHandler(_mysqlLabor, `insert into certificate_seeting(year,daterange_start,daterange_end,assclasshour,minclasshour) values(${data.year},'${data.daterange_start}','${data.daterange_end}',${data.assclasshour},${data.minclasshour})`);
        }
    } catch (err) {
        return false;
    }
}

/**
 * 保存证书设置
 */
router.post('/savesetting', async (req, res, next) => {
    let fromCollection = req.body;
    let save = await saveSetting(fromCollection);
    if (save)
        res.json({ status: true })
    else
        res.json({ status: false, err: '保存失败' })
});

async function saveBack(data) {
    try {
        let res = await mysql.sqlHandler(_mysqlLabor, 'select * from certificate_seeting where year=' + data.year);
        if (res && res.length > 0) {
            let sqlString = `update certificate_seeting set backimg='${data.mapImageUrl}',sealimg='${data.stampUrl}', namefontsize=${data.font[0].fontSize},namefontx=${data.font[0].x},namefonty=${data.font[0].y},namefongcolor='${data.font[0].color}',classhoursize=${data.font[1].fontSize},classhourx=${data.font[1].x},classhoury=${data.font[1].y},classhourcolor='${data.font[1].color}',nosize=${data.font[2].fontSize},nox=${data.font[2].x},noy=${data.font[2].y},nocolor='${data.font[2].color}',datesize=${data.font[3].fontSize},datex=${data.font[3].x},datey=${data.font[3].y},datecolor='${data.font[3].color}',stampsize=${data.stamp.size},stamx=${data.stamp.x},stamy=${data.stamp.y} where year=${data.year}`;
            let save = await mysql.sqlHandler(_mysqlLabor, sqlString);
            if (save)
                return {
                    status: true
                }
            else
                return {
                    status: false,
                    err: '保存失败'
                }

        } else
            return {
                status: false,
                err: '请先保存证书配置'
            }
    } catch (err) {
        console.log('err', err);
        return false;
    }
}

/**
 * 保存证书信息
 */
router.post('/saveback', async (req, res, next) => {
    let fromCollection = req.body;
    let save = await saveBack(fromCollection);
    res.json(save);
})

/**
 * 获取当前用户某个年度的课时以及报名的活动列表
 * @param {*} acid 
 */
async function getUserCertCount(year, openid, pageIndex, pageSize) {
    //获取改年的起始时间
    let settingObj = await mysql.sqlHandler(_mysqlLabor, `select * from certificate_seeting where year=${year}`);
    if (settingObj && settingObj.length > 0) {
        let sqlString = `select a.*,b.acName,b.pic,b.acshape,b.endTime,b.address from activity_apply_const as a left join activity as b on a.acid=b.acId where a.year=${year}  and a.openid='${openid}'  order by a.create_at desc limit ${(pageIndex - 1) * pageSize},${pageSize}`;
        let applayList = await mysql.sqlHandler(_mysqlLabor, sqlString);
        let statics = await mysql.sqlHandler(_mysqlLabor, `select sum(classhour) as classhour from activity_apply_const where openid='${openid}' and year=${year}`);

        return {
            status: true,
            data: {
                applayList: applayList,
                settingObj: settingObj && settingObj.length > 0 ? settingObj[0] : {},
                statics: statics && statics.length > 0 ? statics[0] : {}
            }
        }
    } else
        return {
            status: false,
            err: '该年度没有初始化证书设置'
        }
}

/**
 * 写入活动课时
 * @param {*} acid 
 * @param {*} openid 
 * @param {*} classhour 
 * @returns 
 */
async function creatUserApplyActClassHour(acid, openid) {
    //获取当前年
    let year = new Date().getFullYear();
    //获取活动内容
    let actInfo = await mysql.sqlHandler(_mysqlLabor, `select a.* from activity as a where a.acId='${acid}'`);
    if (actInfo && actInfo.length > 0) {
        actInfo = actInfo[0];
        let acYear = new Date(actInfo.create_at).getFullYear();
        //查询是否已经记录该课时
        let hasClassHour = await mysql.sqlHandler(_mysqlLabor, `select * from activity_apply_const where acid='${acid}' and openid='${openid}'`);
        if (hasClassHour && hasClassHour.length > 0)
            return { status: false, err: '已经记录' };
        //查询该年的证书课时配置
        let certInfo = await mysql.sqlHandler(_mysqlLabor, `select * from certificate_seeting where year=${acYear}`);
        if (certInfo && certInfo.length > 0) {
            certInfo = certInfo[0];
            let assclasshour = certInfo.assclasshour;
            let insertRes = await mysql.sqlHandler(_mysqlLabor, `insert into activity_apply_const(acid,openid,classhour,year,create_at) values('${acid}','${openid}',${assclasshour},${acYear},'${dateformat.dateFormat(new Date())}')`);
            if (insertRes)
                return { status: true };
            else
                return { status: false, err: '保存失败' };

        } else
            return { status: false, err: '该年度证书配置无效' };

    } else
        return { status: false, err: '没有该活动' }
}

/**
 * 获取用户证书详情
 */
router.get('/getusercertdetail', async (req, res, next) => {
    let fromCollection = req.query;
    let data = await getUserCertCount(fromCollection.year, fromCollection.openid, fromCollection.pageIndex || 1, fromCollection.pageSize || 20);
    res.json(data);
})

async function getStaticsYearForAdmin(year, pageIndex, pageSize, username = "") {
     let otherWhere = username != '' ? ' and c.username like "%' + username + '%"' : '';
    let sqlString = `select a.*,b.acName,b.pic,b.acshape,b.endTime,b.address,c.username,c.schoolName from activity_apply_const as a left join activity as b on a.acid=b.acId left join user as c on a.openid=c.openid where a.year=${year} ${otherWhere}  order by a.create_at desc limit ${(pageIndex - 1) * pageSize},${pageSize}`;
    let list = await mysql.sqlHandler(_mysqlLabor, sqlString);
    let count = await mysql.sqlHandler(_mysqlLabor, `select count(a.id) as count from activity_apply_const as a left join user as c on a.openid=c.openid where a.year=${year}  ${otherWhere}`);
    let statics = await mysql.sqlHandler(_mysqlLabor, `select sum(a.classhour) as classhour from activity_apply_const as a left join user as c on a.openid=c.openid where year=${year} ${otherWhere}`);
    return {
        status: true,
        data: {
            list: list,
            count: count && count.length > 0 ? count[0].count : 0,
            classhour: statics && statics.length > 0 ? statics[0].classhour : 0
        }
    }
}

/**
 * 获取统计数据
 */
router.get('/getstaticsyearforadmin', async (req, res, next) => {
    let year = req.query.year || new Date().getFullYear();
    let pageIndex = req.query.pageIndex || 1;
    let pageSize = req.query.pageSize || 10;
    let username = req.query.username || '';
    let data = await getStaticsYearForAdmin(year, pageIndex, pageSize, username);
    res.json(data);
})

/**
 * 生成PDF
 * @param {*} openid 
 * @param {*} year 
 */
async function creatPdf(openid, year) {
    // 创建一个 PDF 文档实例
    const doc = new PDFDocument();
    process.stdout.setDefaultEncoding('utf8');
    // 将文档内容通过管道写入一个文件
    doc.pipe(fs.createWriteStream(`./static/files/pdf-${openid}-${year}.pdf`));
    // const response = await axios({
    //     method: 'get',
    //     url: 'https://teacherapi.cocorobo.cn/teaching-file/static/yym/Rectangle25.png',
    //     responseType: 'arraybuffer' // 确保我们获取到的是Buffer类型的数据
    // });

    // const imageBuffer = Buffer.from(response.data, 'binary');
    // doc.registerFont('main', './msyh.TTF')
    // doc.moveDown().image(imageBuffer, {
    //     width: 400,
    //     align: 'center', // 居中对齐图片
    //     valign: 'center',
    //     x: 100,
    //     y: 100
    // });
    let bigTitleSize = 30;
    let contentSize = 13;
    let listSize = 12;
    // 添加标题
    doc.font('./msyh.TTF').fontSize(bigTitleSize).fillColor('#000000').text('继续教育证明', {
        paragraphGap: 5,
        align: 'center'
    });
    doc.font('./msyh.TTF').fontSize(contentSize).fillColor('#000000').text('CONTINUING EDUCATION CERTIFICATE', {
        paragraphGap: 5,
        align: 'center'
    });



    //获取用户数据
    let user = await mysql.sqlHandler(_mysqlLabor, `select * from user where openid='${openid}'`);
    let statics = await mysql.sqlHandler(_mysqlLabor, `select sum(classhour) as classhour from activity_apply_const where year=${year} and openid='${openid}'`);
    if (user && user.length > 0 && statics && statics.length > 0) {
        user = user[0];
        let classhour = statics[0].classhour || 0;
        // 添加段落
        doc.font('./msyh.TTF').moveDown().fontSize(contentSize).fillColor('#000000').text(`${user.username}老师(${user.schoolName})，于${year}年度期间，积极参加“丽湖职教双创教育国际虚拟教研室”系列教研活动。该教师在该年度学习期间，累计完成继续教育课程(${classhour}学时)，具体参与情况如下:`, {
            indent: contentSize * 2,
            lineGap: 5,
            align: 'left'
        });
    }


    //获取活动数据
    let sqlString = `select a.*,b.acName,b.pic,b.acshape,b.endTime,b.address,c.username,c.schoolName from activity_apply_const as a left join activity as b on a.acid=b.acId left join user as c on a.openid=c.openid where a.year=${year} and a.openid='${openid}'  order by a.create_at desc`;
    let list = await mysql.sqlHandler(_mysqlLabor, sqlString);


    if (list && list.length > 0) {
        for (let i = 0; i < list.length; i++) {
            doc.font('./msyh.TTF').moveDown().fontSize(listSize).fillColor('#333333').text(`${i + 1}. ${list[i].acName}，${dateformat.dateFormat(list[i].create_at, "year-month-day")}，${list[i].classhour}课时`, {
                indent: 0,
                lineGap: 4,
                align: 'left'
            });

        }
    }
    // 链式调用构建表格


    //主板单位
    doc.font('./msyh.TTF').moveDown().moveDown().moveDown().fontSize(contentSize).fillColor('#000000').text('主办单位:', {
        paragraphGap: 4,
        lineGap: 3,
        align: 'right'
    });

    let dwList = ['深圳职业技术大学工业训练中心(创新创业学院)(代章)', '南京工业职业技术大学创新创业学院', '浙江工贸职业技术学院创业学院', '深圳职业技术大学联合国教科文组织职业教育计划亚非研究与培训中心', '学堂在线'];
    dwList.forEach(item => {
        doc.font('./msyh.TTF').fontSize(contentSize).fillColor('#000000').text(item, {
            paragraphGap: 3,
            lineGap: 3,
            align: 'right'
        });
    })
    //日期
    doc.font('./msyh.TTF').moveDown().fontSize(contentSize).fillColor('#000000').text(`日期：${dateformat.dateFormat(new Date(), "year-month-day")}`, {
        align: 'right'
    });

    //盖章
    const pageWidth = doc.page.width;
    const pageHeight = doc.page.height;
    const margins = doc.page.margins;

    const contentWidth = pageWidth - margins.left - margins.right;
    const contentHeight = pageHeight - margins.top - margins.bottom;
    doc.moveDown(-5).image('./static/yinzhang2026.png', { x: contentWidth - 30, width: 130 });

    // 结束文档
    doc.end();
    return `pdf-${openid}-${year}.pdf`;
}

router.post('/downloadcert', async (req, res, next) => {
    let openid = req.body.openid;
    let year = req.body.year;
    if (fs.existsSync(`./static/files/pdf-${openid}-${year}.pdf`)) {
        res.json({
            status: true,
            data: `/static/files/pdf-${openid}-${year}.pdf`
        })
    } else {
        let pdfPath = await creatPdf(openid, year);
        if (pdfPath && pdfPath != '') {
            res.json({
                status: true,
                data: `/static/files/${pdfPath}`
            })
        } else
            res.json({
                status: false,
                err: 'pdf生成失败'
            })
    }
})

// creatPdf('oe5Ow63GMRguC3D_jROKKFOWdwHc', '2025');
router.creatUserApplyActClassHour = creatUserApplyActClassHour;
module.exports = router;