sztm-excel/server/src/restServer/swisstennis/routes/playerList.js

import fs from 'fs'
import Excel from './excel'
import Player from '../models/player'
import PlayerList from '../models/playerList'
import moment from 'moment'
import { normalize, normalizePhone } from './helpers'
import { session } from '../index'
import { configDb } from '../../api'


async function getPlayerLists (req, res) {
    try {
        const playerLists = await PlayerList.find().select({_id: 1, imported: 1, file: 1, filesize: 1})
        return res.json(playerLists)
    } catch (error) {
        return res.status(400).json({ msg: error.toString() })
    }
}

async function getPlayerList (req, res) {
    try {
        const key = req.query.playerlistId ? { _id: req.query.playerlistId } : {}
        const playerList = await PlayerList.findOne(key).sort({ imported: -1 }).populate('players')
        return res.json(playerList)
    } catch (error) {
        return res.status(400).json({ msg: error.toString() })
    }
}

async function downloadPlayerList (req, res) {
    try {
        const tournament = req.query.tournament || configDb.tournamentId.value
        
        if (!tournament) {
            throw Error('No tournament given.')
        }
        const filename = `PlayerList-${tournament}-${moment().format('YYYYMMDDTHHmmss')}.xls`
        const playerListFile = fs.createWriteStream(`swisstennis_files/${filename}`)
        const playerList = await session.get(`https://comp.swisstennis.ch/advantage/servlet/PlayerList.xls?tournament=Id${tournament}&lang=D`, {stream: true})
        playerList.pipe(playerListFile)
        const streamDone = new Promise((resolve, reject) => {
            playerListFile.on('finish', resolve)
            playerListFile.on('error', reject)
        })
        await streamDone
        const stats = fs.statSync(`swisstennis_files/${filename}`)
        return res.json({ filename, size: stats.size, mtime: stats.mtime })
    } catch (error) {
        return res.status(400).json({ msg: error.toString() })
    }
}

async function parsePlayerList (req, res) {
    try {
        console.log('Parsing file', req.params.filename)
        
        const filePath = `swisstennis_files/${req.params.filename}`
        const dateString = req.params.filename.match(/(\d{4}\d{2}\d{2}T\d{2}\d{2}\d{2})/)[1]
        const fileDate = moment(dateString)
        const stat = fs.statSync(filePath)
        
        const fileNewer = await PlayerList.find({ imported: { $gte: fileDate.toDate() } })
        if (fileNewer.length) {
            throw Error('File has to be newer.')
        }
        
        console.log('About to read the player list.', filePath)
        const worksheets = await Excel.readWorkbook(filePath)
        const worksheet = worksheets.Players
        if (worksheet[4].length !== 32 & worksheet[3][0] !== 'Konkurrenz' & worksheet[3][31] !== 'bezahlt') {
            throw Error(`Wrong file structure. Length: ${worksheet[4].length} (expected 32), Column A name: ${worksheet[3][0]} (expected Konkurrenz).`)
        }
        
        const headers = worksheet.slice(3, 4)
        
        const allPlayers = await Promise.all(worksheet.slice(4, worksheet.length).map(async data => {
            const filePlayer = {
                created: fileDate.toDate(),
                category: normalize(data[0]),
                licenseNr: normalize(data[2]),
                name: normalize(data[5]),
                firstName: normalize(data[6]), 
                nameDP: normalize(data[24]),
                firstNameDP: normalize(data[25]),
                birthDate: data[7] || null,
                email: normalize(data[16]),
                ranking: normalize(data[17]),
                licenseNrDP: normalize(data[23]),
                phonePrivate: normalizePhone(data[13]),
                phoneWork: normalizePhone(data[14]),
                phoneMobile: normalizePhone(data[15]),
                birthDateDP: data[26] || null,
                rankingDP: normalize(data[27]),
                confirmed: !!data[29],
                paid: !!data[31],
            }
            
            filePlayer.gender = filePlayer.category ? 
            filePlayer.category[0] === 'M' ? 'm' : 
            filePlayer.category[0] === 'W' ? 
            'w' : 
            null : 
            null,
        filePlayer.doubles = !!filePlayer.category.match(/DM.*|[MW]D.*/)
        filePlayer.junior = filePlayer.birthDate ? 
        filePlayer.birthDate.getTime() >= (new Date((new Date()).getFullYear() - 19, 11, 31, 23, 59, 59, 999)).getTime() : 
        false
        filePlayer.fullName = filePlayer.doubles ? 
        `${filePlayer.name} ${filePlayer.firstName} / ${filePlayer.nameDP} ${filePlayer.firstNameDP}` : 
        `${filePlayer.name} ${filePlayer.firstName}`
        filePlayer.idString = `${filePlayer.category} % ${filePlayer.fullName}`
        filePlayer.phone = null
        const reMobile = /^\+417/
        if (filePlayer.phoneWork && filePlayer.phoneWork.match(reMobile)) {
            filePlayer.phone = filePlayer.phoneWork
        } else if (filePlayer.phonePrivate && filePlayer.phonePrivate.match(reMobile)) {
            filePlayer.phone = filePlayer.phonePrivate 
        } else if (filePlayer.phoneMobile && filePlayer.phoneMobile.match(reMobile)) {
            filePlayer.phone = filePlayer.phoneMobile
        }
        
        const player = new Player(filePlayer)
        player.save()
        return player._id
    }))
    
    const playerList = new PlayerList({
        imported: fileDate,
        file: req.params.filename,
        fileSize: stat.size,
        players: allPlayers
    })
    await playerList.save()
    
    return res.json({playerList})
} catch (error) {
    return res.status(400).json({ msg: error.toString() })
}
}

async function deletePlayerList (req, res) {
    try {
        const { playerlistId } = req.params
        
        if (!playerlistId) {
            throw Error('No playerlistId given.')
        }
        const playerList = await PlayerList.findOne({ _id: playerlistId })
        if (!playerList) {
            throw Error('PlayerList not found.')
        }
        playerList.remove()
        return res.json({ _id: playerlistId })
    } catch (error) {
        return res.status(400).json({ msg: error.toString() })
    }
}

export default { getPlayerLists, getPlayerList, downloadPlayerList, parsePlayerList, deletePlayerList }