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

'use strict';
const Const = require("../const");
const RoleNames = Const.RoleNames;
const VehicleNames = Const.VehicleNames;
const WeaponNames = Const.WeaponNames;
const MapNames = Const.MapNames;
const StatisName = Const.StatisName;
const Excel = require("exceljs");
const path = require("path");
const await = require("await-stream-ready/lib/await");

const Service = require('egg').Service;

class RecordService extends Service {

    async create(teacherId, templateId, title) {
        let mysql = this.app.mysql;

        let ret = await mysql.query(`insert into train_records (teacher_id, title, template_id,start_time, state) values (?,?,?,now(), 0)`, [teacherId, title, templateId]);
        console.log( ret );
        return {id: ret.insertId};
    }

    //开始训练的时候，调用更新
    async update(data) {
        let mysql = this.app.mysql;
        console.log( data );
        data.state = 1;
        let ret = await mysql.update("train_records", data, {where:{id: data.id}});


        //更新参训用户表
        let config = data.config;
        try {
            config = JSON.parse( config );
            let role = config.role;
            if( role ) {
                await mysql.query("delete from train_records_users where rid=?", [data.id]);
                
                let n = role.length;
                for( let i=0; i<n; i++) {
                    if( role[i].user != -1) {
                        await mysql.insert("train_records_users", {uid:role[i].user, rid: data.id, add_time:mysql.literals.now });
                    }
                }
            }
        } catch (error) {
        }

        return ret;
    }
    
    //完成或取消的时候调用
    async complete( data )
    {
        let mysql = this.app.mysql;
        console.log( data );
        let ok = data.ok;
        let ret = null;
        if( ok == '1') { //完成
            ret = await mysql.query("update train_records set end_time=now(), duration=timestampdiff(second, start_time, now()), state=2 where id=?", [data.id]);
        } else if(ok == '2') { //删除
            ret = await mysql.query("delete from train_records where id=?", [data.id]);
        } else { //取消
            ret = await mysql.query("update train_records set end_time=now(), duration=timestampdiff(second, start_time,  now()),state=3 where id=?", [data.id]);
        }
        return ret;
    }

    async playbackList( query )
    {
        console.log( query );

        let size = 8;
        let page = query.page;
        if( page == undefined || page == null || page == '') page = 0;
        page = parseInt( page );

        let mysql = this.app.mysql;
        let ret = await mysql.query("select r.id, r.title, DATE_FORMAT(r.start_time,'%Y-%m-%d') as start_time, r.playback, t.subject from train_records as r left join train_templates as t on t.id = r.template_id order by r.start_time desc limit ?,?", [page*size, size]);

        let total = await mysql.query("select count(*) as count from  train_records");
        total = total[0].count;

        return {
            total: total,
            size: 8,
            pages: Math.ceil(total / 8),
            list:ret
        };
    }

    async addPlayback( body )
    {
        console.log("add playback", body);

        let {id, playback} = body;
        if( !id || !playback) throw "参数不合法";
        let mysql = this.app.mysql;
        let ret = await mysql.query(`update train_records set playback=? where id=?`, [playback, id]);
        return 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 teacher_id = query.teacher_id;
        
        let ret = {records:[], total: 0, };

        let where = name ?  `where title like '%${name}%'`: ""; 
        if( teacher_id != undefined ) {
            where = where?where + ` and teacher_id=${teacher_id}` : `where teacher_id=${teacher_id}`;
        }

        let sql = `select r.id, r.title,r.state, date_format(r.start_time,'%Y-%m-%d %H:%m:%s') as start_time,
                    date_format(r.end_time,'%Y-%m-%d %H:%m:%s') as end_time, users.name as teacher_name, r.duration, r.playback, t.subject from train_records as r left join users on users.id=r.teacher_id left join train_templates as t on t.id = r.template_id ${where}`
        
        let total = await mysql.query(`select count(id) as count from train_records ${where}`);
        ret.total = total[0].count;
        
        let data = await mysql.query( `${sql} order by id desc limit ${(pageNo-1)*pageSize},${pageSize}`);
        ret.records = data;
        
        return ret;
    }

    async detail( query ) {
        let {id } = query;
        if( !id) throw "参数不合法";
        let mysql = this.app.mysql;

        let ret = await mysql.query(`select r.id,r.state, r.title, t.subject, date_format(r.start_time,'%Y-%m-%d %H:%m:%s') as start_time, date_format(r.end_time,'%Y-%m-%d %H:%m:%s') as end_time,
               r.config, r.teacher_id, users.name as teacher_name, r.duration, r.playback from train_records as r left join users on r.teacher_id=users.id left join train_templates t on t.id=r.template_id where r.id=?`, [id]);
        
        ret = ret[0];

        let config = {scene:{}, ai:[], objective:[],role:[]};
        
        if( ret.config ) {
            try {
                config = JSON.parse(ret.config);
                let roles =  config.role;
                if( roles ) {
                    let n = roles.length;
                    for(let i=0; i<n; i++) {
                        let u = roles[i];
                        if( u.user != -1) {
                            let userInfo = await mysql.query("select id, name from users where id=" + u.user);
                            userInfo = userInfo[0];
                            if( userInfo ) {
                                u.name = userInfo.name;
                            }
                            //日志数量
                            let logs = await mysql.query(`SELECT COUNT(id) AS nu FROM train_records_logs WHERE rid=${id} AND uid=${u.user}`)
                            u.logs = logs[0].nu;
                            //飞行日志数量
                            let flys = await mysql.query(`SELECT COUNT(id) AS nu FROM train_records_fly WHERE rid=${id} AND uid=${u.user}`)
                            u.flys = flys[0].nu;
                            //发送文书数据
                            let messages = await mysql.query(`SELECT COUNT(id) AS nu FROM train_records_message WHERE rid=${id} AND \`from\`=${u.user}`)
                            u.messages = messages[0].nu;
                        }
                    }
                }
            } catch (error) {
                config = {scene:{}, ai:[], objective:[],role:[]};
            }
        }
        ret.config = config;

        return ret;
    }

    async getUserRecord(query){
        let uid = query.uid;
        let rid = query.rid;
        if( !rid ) throw "ID为空";

        if( !uid ) {//根据token获取用户信息
            let u =  await this.ctx.getLoginedUser();
            if( !u ) throw "用户ID为空";

            if( u.type == 2 ) {
                uid = u.id;
            }
        }
        if(!uid ) throw "用户ID为空";

        let mysql = this.app.mysql;
        let ret = {};

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

        ret.user = {
            ...info
        }

        ret.base = [];

        //训练信息
        let record = await mysql.query(`select r.id, r.title, date_format(r.start_time,'%Y-%m-%d %H:%m:%s') as start_time, date_format(r.end_time,'%Y-%m-%d %H:%m:%s') as end_time,
            r.config, r.duration,t.subject from train_records as r left join train_templates as t on r.template_id=t.id where r.id=?`, [rid]);
        record = record[0];

        ret.base.push({name:"训练任务",value: record.title});
        // ret.base.push({name:"训练开始时间",value: record.start_time});
        ret.base.push({name:"训练持续时间",value: Math.ceil(record.duration / 60)+"分钟" });
        ret.base.push({name:"训练科目",value: record.subject});

        ret.weapon = [];

        let comment = await mysql.query("select comment from train_records_users where uid=? and rid=?", [uid, rid]);
        comment = comment[0];
        ret.comment = comment;
        ret.uid = uid;
        
        try {   
            let config = JSON.parse(record.config);

            console.log("---", config);

            ret.base.push({name:"训练地图", value: MapNames[ parseInt(config.scene.id) ] });

            let role = config.role;
            let n = role.length;
            while( n-- ) {
                let r = role[n];
                if(　r.user == uid ) {
                    ret.base.push({name:"所属小队",value: r.team>0 ? ("小队" + r.team):"..." });
                    ret.base.push({name:"所属小组",value: r.group>0 ?("小队" + r.group):"..." });

                    ret.base.push({name:"角色",value: RoleNames[parseInt(r.role)] });
                    if( r.vehicleid != -1) {
                        ret.base.push({name:"直升机型号",value: VehicleNames[ parseInt(r.vehicleid) ] });
                    }
                    
                    //装备信息
                    if( r.weapon ) {
                        r.weapon.forEach(element => {
                            ret.weapon.push({name: WeaponNames[element.id], value: "x"+element.quantity})
                        });
                    }
                    break;
                }
            }
        } catch (error) {
            console.error( error );
        }
        //训练成绩
        ret.task = [];

        let taskInfo = await mysql.query("select state, value from train_records_result where rid=? and uid=?", [rid, uid]);
        
        taskInfo.forEach( e=>{
            let v = e.value;
            if( e.state == 4 || e.state == 5 || e.state ==6 || e.state == 12 ) {
                v = Math.ceil( v / 60 ) + "分钟";
            }
            ret.task.push({name: StatisName[e.state], value: v});
        });

        return ret;
    }

    //删除记录
    async delete(id)
    {
        let mysql = this.app.mysql;

        //删除文书
        console.log("delete id", id);
        let ret = await mysql.query("delete from train_records_message where rid=?",[id]);
        
        //删除日志
        await mysql.query("delete from train_records_logs where rid=?",[id]);

        //删除飞行日志
        await mysql.query("delete from train_records_fly where rid=?",[id]);

        await mysql.query("delete from train_records where id=?",[id]);

        return true;
    }
    //获取用户训练记录
    async getUserRecordList(query) {
        
        let user = this.ctx.state.admin;
        console.log("getUserRecordList", user);
        
        let uid = user.id;
        let type = user.type;
        if( type != 2 ) return {total:0, records:[]};

        let name = query.name;
        let where = name ?  `where r.title like '%${name}%' and u.uid=${uid} order by r.id desc`: `where u.uid= ${uid} order by r.id desc`;
        
        let select = `r.id, r.title,r.state, date_format(r.start_time,'%Y-%m-%d %H:%m:%s') as start_time,
                    date_format(r.end_time,'%Y-%m-%d %H:%m:%s') as end_time, users.name as teacher_name, r.duration, r.playback, t.subject`
        
        let table= `train_records as r join train_records_users as u on u.rid=r.id left join users on users.id=r.teacher_id left join train_templates as t on t.id = r.template_id`

        return await this.ctx.pageSelect(query, table, select, where);
    }

    async exportXls(query) {

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

        let sql = `select r.id, r.title,r.state, date_format(r.start_time,'%Y-%m-%d %H:%m:%s') as start_time,
        date_format(r.end_time,'%Y-%m-%d %H:%m:%s') as end_time, users.name as teacher_name, r.duration, r.playback, t.subject from train_records as r left join users on users.id=r.teacher_id left join train_templates as t on t.id = r.template_id`

        if( query.selections ) { //导出指定的内容，否则导出所有
            sql += ` where r.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:15,
                header: '时长',
                key: 'duration',
            },{
                style,
                width:10,
                header: '状态',
                key: 'state',
            },{
                style,
                width:20,
                header: '开始时间',
                key: 'start_time',
            },{
                style,
                width:20,
                header: '结束时间',
                key: 'end_time',
            }
        ];
        sheet.columns = columns;

        let StatNames = ["创建" ,"开始", "完成", "取消"];

        let rows = ret.map(item=>{
            let state = item.state;
            item.state = StatNames[state];
            let duration = item.duration;
            if( duration ) {
                item.duration = Math.ceil( duration / 60 ) + "分钟"; 
            } else  {
                item.duration = "0 分钟";
            }
            return item;
        });
        sheet.addRows(  rows );
        return await workbook.xlsx.writeBuffer();
    }

    async itemExportXls(query) {
       let data = await this.detail( query )

       let workbook = new Excel.Workbook();
       let sheet  = workbook.addWorksheet("训练详情", {
           // views: [{xSplit:1, ySplit:1}],
           pageSetup:{pageSize:9, orientation:'portrait', fitToPage:true, showGridLines:false, horizontalCentered:true},
           headerFooter:{
               firstHeader:"舰航仿真模拟训练详情",
               firstFooter:"舰航仿真模拟训练详情",
               oddFooter:"第 &P 页，共 &N页"
           }
       });
       
       let style = {alignment: {vertical:'middle', horizontal:'center'}};
      
    //    console.log( data.config.role );

       sheet.getColumn(1).width = 12;
       sheet.getColumn(5).width = 12;

        //第一行
       let row = sheet.getRow(1);
       row.getCell(1).value = "训练配置";
       row.getCell(1).style = {alignment: {vertical:'middle', horizontal:'center'}, font: {size:14, color:{rgba:'1890FFFF'}}};
       row.height = 25

       row.style = {alignment: {vertical:'middle', horizontal:'center'}};

       //训练配置数据
        row = sheet.getRow(2);
        row.height = 20
        sheet.mergeCells('B2:D2');
        row.getCell(1).value = "训练名字："
        let cell = row.getCell(2);
        cell.value = data.title;
        row.getCell(5).value = "科目名字：";
        cell = row.getCell(6);
        cell.value = data.subject;
        sheet.mergeCells('F2:H2');
        
        let scene = data.config.scene;

        sheet.mergeCells('B3:D3');
        sheet.mergeCells('F3:H3');
        row = sheet.getRow(3);
        row.height = 20
        row.getCell(1).value = "训练地图：";
        row.getCell(2).value = Const.MapNames[ parseInt(scene.id) ];

        row.getCell(5).value = "初始天气：";
        row.getCell(6).value = Const.Weathers[ parseInt(scene.weather)];

        sheet.mergeCells('B4:D4');
        row = sheet.getRow(4);
        row.height = 20
        row.getCell(1).value = "初始时间：";

        row.getCell(2).value = Const.Times[ [14,21].indexOf(scene.time) ];

        row = sheet.getRow(5);
        row.height = 20
        row.getCell(1).value = "训练标准：";
        sheet.mergeCells('B5:H5');

        let items = data.config.objective.map(item=>{ return `${item.key}:${item.val}`})
        
        let over = false;
        let currRow = 5

        while( !over )
        {
            let curr = [];
            if( items.length > 4) {
                curr = items.slice(0, 4);
                items = items.slice(4);
                if( items.length == 0 ) over = true;
            } else {
                curr = items;
                over = true;
            }
            if( curr.length != 0) {
                row.getCell(2).value = curr.join("  ");
            } else {
                over = true;
            }
            if( !over ) {
                currRow +=1;
                row = sheet.getRow( currRow );
                row.height = 20
            }
        }

        //参训情况
        currRow +=1;
        row = sheet.getRow(currRow);
        row.height = 25
        row.getCell(1).value = "参训情况";
        row.getCell(1).style = {alignment: {vertical:'middle', horizontal:'center'}, font: {size:14, color:{rgba:'1890FFFF'}}};

        currRow +=1;
        row = sheet.getRow(currRow);
        row.height = 22
        row.getCell(1).value = "#"
        row.getCell(1).border = {
            top: {style:'thin'},
            left: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        row.getCell(2).value = "姓名"
        row.getCell(2).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        row.getCell(3).value = "角色"
        row.getCell(3).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };
        row.getCell(4).value = "飞机型号"
        row.getCell(4).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        row.getCell(5).value = "所属小队"
        row.getCell(5).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        row.getCell(6).value = "所属小组"
        row.getCell(6).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        row.getCell(7).value = "日志数"
        row.getCell(7).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        row.getCell(8).value = "飞行记录"
        row.getCell(8).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };
        row.getCell(9).value = "发送文书"
        row.getCell(9).border = {
            top: {style:'thin'},
            bottom: {style:'thin'},
            right: {style:'thin'}
        };

        let users = data.config.role.filter(item=>item.user != -1);
        let ulen = users.length;
        for(let i=0; i<ulen; i++) {
            currRow +=1;
            let u = users[i];
            row = sheet.getRow( currRow );
            row.getCell(1).value = (i+1) + "";
            row.getCell(1).border = {
                left: {style:'thin'},
                bottom: {style:'thin'},
                right: {style:'thin'}
            };
            row.getCell(2).value = u.name;
            row.getCell(2).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };

            row.getCell(3).value = Const.RoleNames[ u.role ];
            row.getCell(3).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };

            row.getCell(4).value = u.vehicleid != -1? ( Const.VehicleNames[u.vehicleid] + `(${u.num})` ) : "--";
            row.getCell(4).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };

            row.getCell(6).value = u.group != -1 ? ("小组"+u.group) : "--";
            row.getCell(6).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };

            row.getCell(5).value = u.team != -1 ? ("小队"+u.team) : "--";
            row.getCell(5).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };

            row.getCell(7).value = u.logs;
            row.getCell(7).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };
            row.getCell(8).value = u.flys;
            row.getCell(8).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };
            row.getCell(9).value = u.messages;
            row.getCell(9).border = {
                bottom: {style:'thin'},
                right: {style:'thin'}
            };
        }
        //训练状态
        //参训情况
        currRow +=1;

        row = sheet.getRow( currRow );
        row.height = 25
        row.getCell(1).value = "训练状态";
        row.getCell(1).style = {alignment: {vertical:'middle', horizontal:'center'}, font: {size:14, color:{rgba:'1890FFFF'}}};
        
        currRow +=1;
        row = sheet.getRow(currRow);
        row.height = 20
        sheet.mergeCells(`B${currRow}:D${currRow}`);
        row.getCell(1).value = "开始时间："
        
        cell = row.getCell(2);
        cell.value = data.start_time;
        row.getCell(5).value = "结束时间：";
        cell = row.getCell(6);
        cell.value = data.end_time;
        sheet.mergeCells(`F${currRow}:H${currRow}`);

        currRow +=1;
        row = sheet.getRow(currRow);
        row.height = 20
        sheet.mergeCells(`B${currRow}:D${currRow}`);
        row.getCell(1).value = "持续时间："
        
        cell = row.getCell(2);
        let duration = data.duration;
        if( !duration ) duration = 0;
        cell.value = duration + 's';

        row.getCell(5).value = "任务状态：";
        cell = row.getCell(6);
        let state = "未开始";
        if( data.state == 1) state = "训练中";
        else if( data == 2 ) state = "已结束";

        cell.value = state;
        sheet.mergeCells(`F${currRow}:H${currRow}`);

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