tmp/wj-pack/backend/app/service/adminUser.js

'use strict';

const Service = require('egg').Service;
const md5 = require('md5-node');
const Excel = require("exceljs");
const path = require("path");

class AdminUserService extends Service {
    
    async login(name, password) {
    
        let { ctx , config, app} = this;

        let pwdmd5 = md5(password);
        let mysql = app.mysql;
        console.log( "pwdmd5=>", pwdmd5);
        
        let data = null;
	try {
	   return  await this.appLogin(name, pwdmd5);
	} catch(error) {   
	   data = await mysql.get("admin_user", {name, password: pwdmd5});
	   if( !data ) {
	        throw "账号密码错误"	
	   }
	}

        let info = data;
        if( info.status == 2) {
            throw "账号已被冻结";
        }

        await mysql.query("update admin_user set last_login_time=now() , last_login_ip=? where id=?", [ctx.ip, info.id]);
       
        let teacherRoleId = await mysql.query("select ur.id from admin_user_role as ur left join admin_role ar on ar.id=ur.role_id where ur.user_id=? and ar.role_code='role_teacher'", [data.id])


        let configAdmin =  config.loginAdmin;

        let token = await ctx.jwtSign({name:info.name, id:info.id, type:1}, configAdmin.secret, { expiresIn: configAdmin.expiresIn });
	
	    if( teacherRoleId.length == 1) return {token, name:info.name, avatar:info.avatar, realname:info.realname, id:info.id, role: 1, type:2};

        return {token, name:info.name, avatar:info.avatar, realname:info.realname, id:info.id,  type:1};
    }

    async appLogin(name, pwdmd5){ //参训人员登录
        let { ctx , config, app} = this;
        let mysql = app.mysql;
        let data = await mysql.get("users", {name, pwd: pwdmd5});
        if( !data )  {
            throw "账号密码错误";
        }

        let info = data;
        if( info.status == 2) {
            throw "账号已被冻结";
        }

        let configAdmin =  config.loginAdmin;

        let token = await ctx.jwtSign({name:info.name, id:info.id, type:2, role:info.role}, configAdmin.secret, { expiresIn: configAdmin.expiresIn });

        return {token, name:info.name, avatar:info.avatar, realname:info.realname, id:info.id, type:2, role:info.role};

    }

     async list(query) {

        let mysql = this.app.mysql;

        let pageNo = query.pageNo;
        let pageSize = query.pageSize;
        
        pageNo = parseInt( pageNo );
        pageSize = parseInt( pageSize );
        let name = query.name;
        let role = query.role;
        
        let ret = {records:[], total: 0, };

        let where = name ?  `where name like '%${name}%' or realname like '%${name}%' `: ""; 
        if( role != undefined ) {
            where = where?where +  `and role=${role}`: `where role=${role}`;
        }
        let sql = `select id, name, realname, code, role,avatar, date_format(last_login_time,'%Y-%m-%d') as last_login_time, last_login_ip from users ${where}`
        
        let total = await mysql.query("select count(id) as count from users " + where);
        ret.total = total[0].count;
        
        let data = await mysql.query( `${sql} limit ${(pageNo-1)*pageSize},${pageSize}`);
        ret.records = data;
        
        return ret;
    }

    async delete(id)
    {
        let mysql = this.app.mysql;
        let ret = await mysql.query("delete from users where id=?",[id]);
        return "删除成功";
    }

    async edit(data) {
        let mysql = this.app.mysql;
        if( data.add_time ) delete data.add_time;

        console.log("editxx", data);

        let ret = await mysql.update("users", data, {where:{id: data.id}});
        return ret;
    }

    async add( data ) {
        let mysql = this.app.mysql;
        data.add_time = mysql.literals.now;
        let ret = await mysql.insert("users", data);
        return ret;
    }

    async resetpwd( data ) {

        let mysql = this.app.mysql;

        if( !data.id ) throw "ID不能为空";
        if( !data.pwd ) throw "密码不能为空";
        if( data.pwd )  data.pwd = md5( data.pwd );

        let ret = await mysql.update("users", data, {where:{id: data.id}});
        return ret;
    }

    async getUserProfile(query) {

        let {ctx} = this;

        console.log("getUserProfile", ctx.header.authorization);
        let id = query.id;
        if( !id ) {
            let authToken =  ctx.header.authorization;
            if ( !authToken ) {
               throw "用户ID为空";
            }

            let config = this.app.config.loginAdmin;
            let ret = await ctx.jwtVerify(authToken, config.secret);
            if( !ret.verify )
            {
                throw "用户ID为空";
            }
            
           let  message = ret.message;
           console.log("message", message);
           if( message.type == 2 ) { //参训人员登录
                id = message.id;
           }
        }
        if( !id ) throw "查看的参训人员ID为空";

        let mysql = this.app.mysql;

        let info = await mysql.query("select id,avatar,realname,code from users where id=? ", [id])
        info = info[0];

        //参训总次数，最早参训时间，最后参训时间
        let state = await mysql.query("select count(rid) as rid, date_format(max(add_time), '%Y-%m-%d') as lastTime, date_format(min(add_time),'%Y-%m-%d') as farTime from train_records_users where uid=?", [id]);  
        state = state[0];

        console.log( state );
        
        //参训科目数
        let subjectIds = await mysql.query("select count(*) as num from (select r.template_id from train_records_users as u left join train_records as r on r.id = u.rid where u.uid=? group by r.template_id) as ids", [id]);  
        subjectIds = subjectIds[0];
        console.log("subject ids " + id, subjectIds);

        //累计训练时长
        let totalTime = await mysql.query("select sum(r.duration) as num from train_records_users as u left join train_records as r on r.id = u.rid where uid=?", [id]);
        totalTime = totalTime[0]?totalTime[0].num: 0;

        // console.log(state, subjectIds);
        return {...info, rids: state.rid, lastTime: state.lastTime, farTime: state.farTime, subjects: subjectIds.num, totalTime: `${Math.ceil(totalTime/60)} 分钟`};
    }
    
    async commentUserRecord(data) {
        let rid = data.rid;
        let uid = data.uid;
        if( !rid || !uid ) throw "ID为空";
        let mysql = this.app.mysql;
        return await mysql.query(`update train_records_users set comment='${data.text}' where rid=${rid} and uid=${uid}`);
    }

    async exportUserListXls(query) {

        console.log("exportXls==>", query );

        let sql = `select id, name, realname, code, date_format(last_login_time,'%Y-%m-%d') as login_time from users`
        if( query.selections ) { //导出指定的内容，否则导出所有
            sql += ` where id in (${query.selections})`;
        }
        let mysql = this.app.mysql;
        let ret = await mysql.query( sql );
       
        let workbook = new Excel.Workbook();
        let sheet  = workbook.addWorksheet("参训学员列表", {
            // views: [{xSplit:1, ySplit:1}],
            pageSetup:{pageSize:9, orientation:'portrait', fitToPage:true, showGridLines:true, horizontalCentered:true},
            headerFooter:{
                firstHeader:"舰航仿真模拟训练人员名单",
                firstFooter:"舰航仿真模拟训练人员名单",
                oddFooter:"第 &P 页，共 &N页"
            }
        });
        
        let style = {alignment: {vertical:'middle', horizontal:'center'}};
        let columns = [
            {
                style,
                header: '#ID',
                key: 'id',
            },{
                style,
                header: '登录名',
                key: 'name',
                width:20,
            },{
                style,
                width:20,
                header: '真实姓名',
                key: 'realname',
            },{
                style,
                width:20,
                header: '编号',
                key: 'code',
            },{
                style,
                width:20,
                header: '最后登录时间',
                key: 'login_time',
            }
        ];
        sheet.columns = columns;
        sheet.addRows(  ret );
        return await workbook.xlsx.writeBuffer();
    }

    async exportProfileXls(query) {

        let ret = await this.getUserProfile(query);
        console.log( "exportXls", ret);


        let workbook = new Excel.Workbook();
        let sheet  = workbook.addWorksheet(ret.realname, {
            // views: [{xSplit:1, ySplit:1}],
            pageSetup:{pageSize:9, orientation:'portrait', fitToPage:true, horizontalCentered:true},
            headerFooter:{
                firstHeader:"舰航仿真模拟训练个人统计",
                firstFooter:"舰航仿真模拟训练个人统计",
            }
        });
        
        let style = {alignment: {vertical:'middle', horizontal:'center'}, font:{size: 16}};
        let columns = [
            {
                style:{alignment: {vertical:'middle', horizontal:'left'}, font:{size: 16}},
                key: 'left',
                width:22,
            },{
                style,
                key: 'center',
                width:21,
            },{
                style:{alignment: {vertical:'middle', horizontal:'right'}, font:{size: 16}},
                width:20,
                key: 'right',
            }
        ];
        sheet.columns = columns;
        let rows = [{ left: ""},{ left: ""}, {left:ret.realname},{ center: ""}, {left: "系统编号:" + ret.code},{ center: ""}];
        rows.push({left: "参训总次数", right:ret.rids + "次"});
        rows.push({left: "参训科目数", right:ret.subjects + "项"});
        rows.push({left: "参训总时间", right:ret.totalTime });
        rows.push({left: "首次参训时间", right:ret.farTime});
        rows.push({left: "最后参训时间", right:ret.lastTime});

        sheet.addRows(rows);
        sheet.getRow(1).height = 115;
        sheet.getRow(3).font = {bold:true, size: 22, name:"Arial"}
        let imageHost = this.app.config.serverhosts.image;
        sheet.getRow(7).border = {bottom: {style:'thin'}};
       

        sheet.getRow(8).border = {bottom: {style:'thin'}};
        sheet.getRow(9).border = {bottom: {style:'thin'}};
        sheet.getRow(10).border = {bottom: {style:'thin'}};
        sheet.getRow(11).border = {bottom: {style:'thin'}};

        sheet.getRow(7).height = 40;
        sheet.getRow(8).height = 40;
        sheet.getRow(9).height = 40;
        sheet.getRow(10).height = 40;
        sheet.getRow(11).height = 40;

        if( ret.avatar ) {
            let image = await this.ctx.curl(imageHost + ret.avatar);
        
            let extension = path.extname(ret.avatar);
            extension = extension.substr(1);
    
            let id = workbook.addImage({buffer: image.data, extension});
            sheet.addImage(id, { tl: {col: 0, row:0}, ext: {width: 120, height: 120} , editAs:undefined});
    
            console.log( extension, id);
        }
        
        return await workbook.xlsx.writeBuffer();
    }
}

module.exports = AdminUserService;