sztm-excel/src/App.js

import React from 'react'
import XLSX from 'xlsx'
import PlayerList from './player/player-list'
import MatchList from './match/match-list'
import Stats from './stats/stats'
import { SheetFromArray, Workbook, saveAs } from './excel/excel'
 
class App extends React.Component {
  constructor () {
    super()
    this.state = {
      stats: {
        matchDates: []
      },
      players: [],
      matches: [],
      worksheets: {
        'PlayerList': null,
        'Calendar': null
      },
      filters: {
        date: 'Alle'
      }
    }
    this.handlePlayerList = this.handlePlayerList.bind(this)
    this.handleCalendar = this.handleCalendar.bind(this)
    this.generatePlayerList = this.generatePlayerList.bind(this)
    this.generateCalendar = this.generateCalendar.bind(this)
    this.filterData = this.filterData.bind(this)
    this.generateSchedule = this.generateSchedule.bind(this)
    this.generatePayTable = this.generatePayTable.bind(this)
  }

  componentDidMount () {
    this.generateStats()
  }

  handlePlayerList (event) {
    const file = this.playerList.files[0]
    this.readWorkbook(file, this.generatePlayerList)
  }

  handleCalendar (event) {
    const file = this.calendar.files[0]
    this.readWorkbook(file, this.generateCalendar)
  }

  readWorkbook (file, callback) {
    const reader = new FileReader()
    reader.onload = (e) => {
      const data = e.target.result
      const workbook = XLSX.read(data, {type: 'binary'})
      console.log(workbook)
      if (workbook.SheetNames.length !== 1) {
        throw Error(`Expected only one worksheet in the file, but found ${workbook.SheetNames.length}.`)
      }
      const worksheet = XLSX.utils.sheet_to_row_object_array(workbook.Sheets[workbook.SheetNames[0]], { header: 1 })
      callback(worksheet)
    }
    reader.readAsBinaryString(file)
  }

  generatePlayerList (worksheet) {
    console.log('About to read the player list.')
    const worksheets = { ...this.state.worksheets }
    worksheets['PlayerList'] = worksheet
    this.setState({ worksheets })

    if (worksheet[4].length !== 32 & worksheet[3][0] !== 'Konkurrenz' & worksheet[3][31] !== 'bezahlt') {
      throw Error('Wrong file structure.')
    }
    const players = worksheet.slice(4, worksheet.length)
    this.setState({ players })
    this.generateStats()
    console.log(this.state)
  }

  generateCalendar (worksheet) {
    console.log('About to read the calendar.')
    const worksheets = { ...this.state.worksheets }
    worksheets['Calendar'] = worksheet
    this.setState({ worksheets })

    if (worksheet[2].length !== 8) {
      throw Error('Wrong file structure.')
    }
    const matches = worksheet.slice(2, worksheet.length)
    this.setState({ matches })
    this.generateStats()
    console.log(this.state)
  }

  generateStats () {
    const stats = {
      players: [],
      playerCategories: [],
      matchCategories: [],
      matches: [],
      matchDates: [],
      matchPlaces: []
    }

    stats.players = this.state.players

    this.state.players.forEach((player, key) => {
      if (!stats.playerCategories.includes(player[0])) {
        stats.playerCategories.push(player[0])
      }
    })

    stats.matches = this.state.matches

    this.state.matches.forEach((match, key) => {
      if (!!match[1] & !stats.matchDates.includes(match[1])) {
        stats.matchDates.push(match[1])
      }
      if (!!match[3] & !stats.matchCategories.includes(match[3])) {
        stats.matchCategories.push(match[3])
      }
      if (!!match[0] & !stats.matchPlaces.includes(match[0])) {
        stats.matchPlaces.push(match[0])
      }
    })

    this.setState({ stats })
  }

  filterData () {
    const filters = {
      date: this.date.value
    }
    this.generateStats()
    this.setState({ filters })
  }

  generateSchedule (event) {
    event.preventDefault()

    const matchlist = new Workbook()
    matchlist.SheetNames = []
    matchlist.Sheets = {}

    const worksheets = {}
    const placeArray = this.state.stats.matchPlaces.concat(['Alle'])

    placeArray.forEach((place) => {
      let filter = this.state.filters.date
      let header = [
        [`Spielplan für den ${filter} (${place})`],
        [],
        ['Ort', 'Zeit', 'Kategorie', 'Spieler 1', '', 'Spieler 2', '', '1. Satz', '2. Satz', '3. Satz', 'WO Grund']
      ]
      let matchListPerPlace = this.state.matches.filter((match) => {
        return (match[1] === filter | filter === 'Alle') & (match[0] === place | place === 'Alle')
      }).map((match) => 
        [match[0], match[2], match[3], match[4], match[5], match[6], match[7]]
      )
      console.log(matchListPerPlace)
      worksheets[place] = SheetFromArray(header.concat(matchListPerPlace))
      matchlist.SheetNames.push(place)
      matchlist.Sheets[place] = worksheets[place]
    })
    console.log(matchlist)

    saveAs(matchlist, 'Spielerliste.xlsx')
  }

  generatePayTable (event) {
    event.preventDefault()

    const paylist = new Workbook()
    paylist.SheetNames = []
    paylist.Sheets = {}

    const worksheets = {}
    const placeArray = this.state.stats.matchPlaces.concat(['Alle'])

    const payerList = {}
    this.state.matches.forEach((match) => {
      if (!!match[4] & !payerList.hasOwnProperty(`${match[3]} ${match[4]}`)) {
        let foundPlayer = this.state.players.find((player) => {
          if (!!player[24]) {
            return (`${player[5]} ${player[6]} / ${player[24]} ${player[25]}` === match[4]) & (match[3] === player[0].replace(/\s+/g, ' '))
          } else {
            return (`${player[5]} ${player[6]}` === match[4]) & (match[3] === player[0].replace(/\s+/g, ' '))
          }
        })
        if (!foundPlayer) {
          console.log(foundPlayer, match)
          throw Error('Player 4 not found. This is an error!')
        }
        payerList[`${match[3]} ${match[4]}`] = [match[0], match[1], match[2], foundPlayer[31]]
      }
      if (!!match[6] & !payerList.hasOwnProperty(`${match[3]} ${match[6]}`)) {
        let foundPlayer = this.state.players.find((player) => {
          if (!!player[24]) {
            return (`${player[5]} ${player[6]} / ${player[24]} ${player[25]}` === match[6]) & (match[3] === player[0].replace(/\s+/g, ' '))
          } else {
            return (`${player[5]} ${player[6]}` === match[6]) & (match[3] === player[0].replace(/\s+/g, ' '))
          }
        })
        if (!foundPlayer) {
          console.log(foundPlayer, match)
          throw Error('Player 6 not found. This is an error!')
        }
        payerList[`${match[3]} ${match[6]}`] = [match[0], match[1], match[2], foundPlayer[31]]
      }
    })
    console.log(payerList)

    placeArray.forEach((place) => {
      let filter = this.state.filters.date
      let header = [
        [`Zahlliste für den ${filter} (${place})`],
        [],
        ['bereits bez.', 'Kategorie', 'Name', 'Betrag bez.', 'Quittung abgeben']
      ]

      let payListPerPlace = [] 
      this.state.matches.filter((match) => {
        return (match[1] === filter | filter === 'Alle') & (match[0] === place | place === 'Alle')
      }).forEach((match) => {
        if (!!match[4]) {
          payListPerPlace.push(match)
        }
        if (!!match[6]) {
          payListPerPlace.push(match)
        }
      })
      console.log(payListPerPlace)
      worksheets[place] = SheetFromArray(header.concat(payListPerPlace))
      paylist.SheetNames.push(place)
      paylist.Sheets[place] = worksheets[place]
    })
    console.log(paylist)

    saveAs(paylist, 'Zahlliste.xlsx')
  }

  render () {
    return (
      <div className='App'>
        <form>
          <label htmlFor='playerList'>Swisstennis playerList.xls</label>
          <input type='file' id='PlayerList' ref={(input) => { this.playerList = input }} accept='.xls' placeholder='playerList File' onChange={this.handlePlayerList} />
          <label htmlFor='Calendar'>Swisstennis Calendar.xls</label>
          <input type='file' id='Calendar' ref={(input) => { this.calendar = input }} accept='.xls' placeholder='Calendar File' onChange={this.handleCalendar} />
          <label htmlFor='Date'>Datum</label>
          <select ref={(input) => { this.date = input }} onChange={this.filterData}>
            <option>Alle</option>
            {this.state.stats.matchDates.map((date, key) => (
              <option key={key}>{date}</option>
            ))}
          </select>
          <button onClick={this.generateSchedule} disabled={!this.state.matches.length}>Spielliste generieren</button>
          <button onClick={this.generatePayTable} disabled={(!this.state.matches.length | !this.state.players.length)}>Zahlliste generieren</button>
        </form>
        <Stats stats={this.state.stats} />
        <PlayerList players={this.state.players} />
        <MatchList matches={this.state.matches} filters={this.state.filters} />
      </div>
    )
  }
}

export default App