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

'use strict';

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

const MapNames =  require("../const").MapNames;

class CourseService extends Service {

    async list() {
        let mysql = this.app.mysql;
        let ret = await mysql.query("select id, subject, title, config from train_templates where state = 0");
        return {list:ret};
    }

    async adminList(query) {

        let mysql = this.app.mysql;

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

        let where = name ?  `where title like '%${name}%' or subject like '%${name}%' `: ""; 
        let sql = `select id, title, subject, date_format(add_time,'%Y-%m-%d') as add_time, config, state from train_templates ${where}`
        
        let total = await mysql.query(`select count(id) as count from train_templates ${where}`);
        ret.total = total[0].count;
        
        let data = await mysql.query( `${sql} limit ${(pageNo-1)*pageSize},${pageSize}`);
        ret.records = data;
        
        return ret;
    }

    async add( data ) {
        let mysql = this.app.mysql;
        let ret = await mysql.query("insert into train_templates (title, subject, config, add_time) values (?,?,?, now())", [data.title, data.subject, data.config]);


        return {id: ret.insertId};
    }

    async save( data ) {
        let id = data.id;
        if( !id ) throw "id不为空";

        console.log( data );

        let config = data.config;
        if(　typeof config == 'object') {
           config = JSON.stringify(config);
        }

        let mysql = this.app.mysql;
        let ret = await mysql.query("update train_templates set title=?, config=?, add_time=? where id=?",[data.title, config, mysql.literals.now, id]);

        return ret;
    }

    async edit( data ) {
        let id = data.id;
        if( !id ) throw "id不为空";

        console.log( data );

        let config = data.config;
        if(　typeof config == 'object') {
           config = JSON.stringify(config);
        }

        let mysql = this.app.mysql;
        let ret = await mysql.query("update train_templates set title=?,subject=?, config=?, add_time=? where id=?",[data.title, data.subject, config, mysql.literals.now, id]);

        return ret;
    }

    async delete(id)
    {
        let mysql = this.app.mysql;
        let ret = await mysql.query("delete from train_templates where id=?",[id]);
        return ret;
    }
    async lock( body ) {
        let {id, state} = body;
        if( !id ) throw "ID为空";

        let s = state == 0? 2 : 0;

        let mysql = this.app.mysql;
        let ret = await mysql.query("update train_templates set state=? where id=?",[s, id]);
        
        ret.state = s;

        return ret;
    }

    async exportXls(query) {

        console.log("exportXls==>", query );
        let sql = `select id, title, subject, config, date_format(add_time,'%Y-%m-%d') as add_time from train_templates`
        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: 'title',
                width:20,
            },{
                style,
                width:20,
                header: '科目',
                key: 'subject',
            },{
                style,
                width:20,
                header: '地图',
                key: 'map',
            },{
                style,
                width:20,
                header: '更新时间',
                key: 'add_time',
            }
        ];
        sheet.columns = columns;

        let rows = ret.map(item=>{
            
            try {
                let cfg = JSON.parse(item.config );
                let scene = cfg.scene;
                if( scene ) {
                    item.map = MapNames[parseInt(scene.id)];
                }
            } catch (error) {
            }
            delete item.config;

            return item;
        });
        sheet.addRows(  rows );

        return await workbook.xlsx.writeBuffer();
    }
}
module.exports = CourseService;