IDENTIFICATION DIVISION.
       PROGRAM-ID. UC2 INITIAL.
       AUTHOR. Robbin Verbist.
       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
       COPY 'log_bestandbeschrijving'.
       SELECT in-parameters ASSIGN TO parameterPath
                ORGANIZATION IS LINE SEQUENTIAL
                ACCESS MODE IS SEQUENTIAL.
       SELECT out-temp ASSIGN TO 'temp'
                ORGANIZATION IS LINE SEQUENTIAL
                ACCESS MODE IS SEQUENTIAL.
       SELECT out-csv ASSIGN TO 'overview.csv'
                ORGANIZATION IS LINE SEQUENTIAL
                ACCESS MODE IS SEQUENTIAL.
       SELECT out-printer ASSIGN TO PRINTER
                ORGANIZATION IS LINE SEQUENTIAL
                ACCESS MODE IS SEQUENTIAL.
       DATA DIVISION.
       FILE SECTION.
       COPY 'log_beschrijving'.
       FD in-parameters.
       01 in-parameters-line            PIC X(1024).
        88 in-parameter-eof              VALUE HIGH-VALUES.
       FD out-temp.
       01 out-temp-line                 PIC X(1024).
        88 out-temp-eof                  VALUE HIGH-VALUES.
       FD out-csv.
       01 out-csv-line                  PIC X(1024).
        88 out-csv-eof                   VALUE HIGH-VALUES.
       FD out-printer.
       01 out-printer-line              PIC X(1024).
        88 out-printer-eof               VALUE HIGH-VALUES.
       WORKING-STORAGE SECTION.
       01 settings.
     ** Defines the behaviour of the application when a statement is executed
     ** with a warning. Options:
     **         - Y: ignores any warnings, execution continues
     **         - N: bails on warnings, execution halts
     ** See DO-EXECUTE-STATEMENT
        03 query-warning-policy         PIC X VALUE 'N'.
         88 exit-on-warning              VALUE 'N'.
         88 ignore-on-warning            VALUE 'Y'.
     ** Defines the behaviour of the application when a statement is executed
     ** with an error. Options:
     **         - Y: ignores any errors, execution continues
     **         - N: bails on errors, execution halts
     ** See DO-EXECUTE-STATEMENT
        03 query-error-policy       PIC X VALUE 'N'.
         88 exit-on-error                VALUE 'N'.
         88 ignore-on-error              VALUE 'Y'.
     ** Table 'class' - holds state for table-based operations
     ** See TABLE-ADD-VALUE, TABLE-CLEAR, TABLE-PRINT
       01 tablemodel.
        03 table-seperator              PIC X VALUE ';'.
        03 table-row occurs 50.
         05 table-row-usage             PIC 9 VALUE 0.
          88 table-row-unused           VALUE 0.
          88 table-row-used             VALUE 1.
         05 table-column occurs 50.
          07 table-column-usage         PIC 9 VALUE 0.
           88 table-column-unused       VALUE 0.
           88 table-column-used         VALUE 1.
          07 table-value                PIC X(10).
        03 variables.
         05 index-row                   PIC 99.
         05 index-col                   PIC 99.
         05 index-row-value             PIC X(80).
         05 index-col-value             PIC X(10).
         05 table-print-index           PIC 999.

       01 parameter-data.
        03 db-url                       PIC X(1024).
        03 db-statement                 PIC X(1024).
        03 db-connection-status         PIC X VALUE 'N'.
         88 db-connected                 VALUE 'Y'.
         88 db-disconnected              VALUE 'N'.
        03 gameId                       PIC 9(4).
        03 rapport-type                 PIC X(20).
         88 rapport-totaal               VALUE "totaal".
         88 rapport-deel                 VALUE "deel".
         88 rapport-detail               VALUE "detail".
        03 output-type                  PIC X(20).
         88 output-print                 VALUE "print".
         88 output-csv                   VALUE "csv".
        03 detail-args                  PIC X(1024).
       EXEC SQL
         BEGIN DECLARE SECTION
       END-EXEC
       01 SQLCODE PIC S9(3).
       01 SQLSTATE PIC X(5).
       01 query-totaalscores.
        03 team-name                    PIC X(20).
        03 OGS-score                    PIC 99v99.
        03 OHS-score                    PIC 99v99.
        03 OSS-score                    PIC 99v99.
        03 ODQS-score                   PIC 99v99.
       EXEC SQL
         END DECLARE SECTION
       END-EXEC
       LINKAGE SECTION.
     ** The path to the parameter file, required.
       01 parameterPath                 PIC X(1024) VALUE LOW-VALUES.
        88 parameterPathEmpty            VALUE LOW-VALUES.
       PROCEDURE DIVISION USING parameterPath.

******* Function: MAIN-PARAGRAPH
******* Arguments: N/A
******* Main application loop
       MAIN-PARAGRAPH.
       OPEN OUTPUT in-log
       IF parameterPathEmpty THEN
        MOVE "# ERROR: Please supply a parameter file path."
        TO in-log-line
        PERFORM DO-LOG
        PERFORM DO-EXIT
        EXIT PROGRAM
       END-IF
       OPEN INPUT in-parameters
     ** parse the parameter file: read the database URL
       READ in-parameters
        AT END
         MOVE "# ERROR: Incorrect file format." TO in-log-line
         PERFORM DO-LOG
         PERFORM DO-EXIT
         EXIT PROGRAM
       END-READ
       MOVE in-parameters-line TO db-url
     ** parse the parameter file: read the Game ID
       READ in-parameters
        AT END
         MOVE "# ERROR: Incorrect file format." TO in-log-line
         PERFORM DO-LOG
         PERFORM DO-EXIT
         EXIT PROGRAM
       END-READ
       MOVE in-parameters-line TO gameId
     ** parse the parameter file: read and validate the report type
       READ in-parameters
        AT END
         MOVE "# ERROR: Incorrect file format." TO in-log-line
         PERFORM DO-LOG
         PERFORM DO-EXIT
         EXIT PROGRAM
       END-READ
       MOVE in-parameters-line TO rapport-type
       IF NOT (rapport-totaal OR rapport-deel OR rapport-detail)
       THEN
        MOVE "# ERROR: Invalid rapport type option." to in-log-line
        PERFORM DO-LOG
        PERFORM DO-EXIT
        EXIT PROGRAM
       END-IF
     ** parse the parameter file: read and validate the output type
       READ in-parameters
        AT END
         MOVE "# ERROR: Incorrect file format." TO in-log-line
         PERFORM DO-LOG
         PERFORM DO-EXIT
         EXIT PROGRAM
       END-READ
       MOVE in-parameters-line TO output-type
       IF NOT (output-print OR output-csv)
       THEN
        MOVE "# ERROR: Invalid output type option." to in-log-line
        PERFORM DO-LOG
        PERFORM DO-EXIT
        EXIT PROGRAM
       END-IF
      * create report(s)
       IF output-print THEN MOVE ' ' TO table-seperator END-IF
       IF output-csv THEN MOVE ';' TO table-seperator END-IF
       OPEN OUTPUT out-temp
       IF rapport-totaal THEN PERFORM DO-RAPPORT-TOTAAL END-IF
       IF rapport-deel THEN PERFORM DO-RAPPORT-DEEL END-IF
       IF rapport-detail THEN PERFORM DO-RAPPORT-DETAIL END-IF
       CLOSE out-temp
       IF output-print THEN PERFORM DO-OUTPUT-PRINTER END-IF
       IF output-csv THEN PERFORM DO-OUTPUT-CSV END-IF

      * cleanup resources
       PERFORM DO-EXIT
       EXIT PROGRAM.

       DO-RAPPORT-TOTAAL.
     ** Connect to the database, declare a cursor and open it
     ** this cursor will get the relevant scores of the last period
     ** for each team in the given game. (note: the query can probably
     ** be optimized to avoid aggregates, but who has the time?!)
       PERFORM DO-CONNECT
       EXEC SQL
        DECLARE scores CURSOR FOR
        SELECT tgamGameTeams.Name AS Name,
         sum(tgamTeamPeriods.ScoreCumulativeOverallGame) AS OGS,
         sum(tgamTeamPeriods.ScoreCumulativeHardSkills) AS OHS,
         sum(tgamTeamPeriods.ScoreCumulativeSoftSkills) AS OSS,
         sum(tgamTeamPeriods.ScoreCumulativeDocumentQuality) AS ODQS
        FROM tgamTeamPeriods
     INNER JOIN tgamGameTeams
     ON tgamTeamPeriods.TeamId = tgamGameTeams.Id
     INNER JOIN tgamGames
     ON tgamGameTeams.GameId = tgamGames.Id
        WHERE tgamGames.Id = :gameId AND
              tgamTeamPeriods.PeriodId = tgamGames.CurrentPeriodId
        GROUP BY tgamGameTeams.Name
       END-EXEC
       EXEC SQL
        OPEN scores
       END-EXEC
     ** Clear the table buffer and create a header row
       PERFORM TABLE-CLEAR
       MOVE "Team Name" TO index-col-value
       PERFORM TABLE-ADD-VALUE
       ADD 1 TO index-col
       MOVE "OG Score" TO index-col-value
       PERFORM TABLE-ADD-VALUE
       ADD 1 TO index-col
       MOVE "OH Score" TO index-col-value
       PERFORM TABLE-ADD-VALUE
       ADD 1 TO index-col
       MOVE "OS Score" TO index-col-value
       PERFORM TABLE-ADD-VALUE
       ADD 1 TO index-col
       MOVE "ODQ Score" TO index-col-value
       PERFORM TABLE-ADD-VALUE
     ** Move each query result into a new content row.
       ADD 1 TO index-row
       EXEC SQL
        FETCH scores INTO :team-name, :OGS-score, :OHS-score,
                          :OSS-score, :ODQS-score
       END-EXEC
       PERFORM UNTIL SQLCODE = 100
        MOVE 1 TO index-col
        MOVE team-name TO index-col-value
        PERFORM TABLE-ADD-VALUE
        ADD 1 TO index-col
        MOVE OGS-score TO index-col-value
        PERFORM TABLE-ADD-VALUE
        ADD 1 TO index-col
        MOVE OHS-score TO index-col-value
        PERFORM TABLE-ADD-VALUE
        ADD 1 TO index-col
        MOVE OSS-score TO index-col-value
        PERFORM TABLE-ADD-VALUE
        ADD 1 TO index-col
        MOVE ODQS-score TO index-col-value
        PERFORM TABLE-ADD-VALUE
        ADD 1 TO index-row
        EXEC SQL
         FETCH scores INTO :team-name, :OGS-score, :OHS-score,
                           :OSS-score, :ODQS-score
        END-EXEC
       END-PERFORM
       EXEC SQL
        CLOSE scores
       END-EXEC
       PERFORM DO-DISCONNECT
     ** Print the table
       PERFORM TABLE-PRINT.

       DO-RAPPORT-DEEL.
        MOVE "# DO-RAPPORT-DEEL." to in-log-line
        PERFORM DO-LOG.

       DO-RAPPORT-DETAIL.
        MOVE "# DO-RAPPORT-DETAIL." to in-log-line
        PERFORM DO-LOG.

******* Function: DO-OUTPUT-CSV
******* Arguments: N/A
******* Writes the contents of the temporary file to the csv file.
       DO-OUTPUT-CSV.
        OPEN INPUT out-temp
        OPEN OUTPUT out-csv
        READ out-temp
         AT END SET out-temp-eof TO TRUE
        END-READ
        PERFORM UNTIL out-temp-eof
         MOVE out-temp-line to out-csv-line
         WRITE out-csv-line
         READ out-temp
          AT END SET out-temp-eof TO TRUE
         END-READ
        END-PERFORM
        CLOSE out-csv
        CLOSE out-temp.

******* Function: DO-OUTPUT-PRINTER
******* Arguments: N/A
******* Writes the contents of the temporary file to the printer.
       DO-OUTPUT-PRINTER.
        OPEN INPUT out-temp
        OPEN OUTPUT out-printer
        READ out-temp
         AT END SET out-temp-eof TO TRUE
        END-READ
        PERFORM UNTIL out-temp-eof
         MOVE out-temp-line to out-printer-line
         WRITE out-printer-line
         READ out-temp
          AT END SET out-temp-eof TO TRUE
         END-READ
        END-PERFORM
        CLOSE out-printer
        CLOSE out-temp.

******* Function: DO-LOG
******* Arguments: in-log-line
******* Provides a hook into the logging system for debugging purposes.
       DO-LOG.
       WRITE in-log-line.

******* Function: DO-CONNECT
******* Arguments: db-url
******* Connects to the database using the given connection string.
       DO-CONNECT.
       IF db-disconnected THEN
        EXEC SQL
         CONNECT
           TO :db-url
           DRIVER "com.microsoft.sqlserver.jdbc.SQLServerDriver"
        END-EXEC
        IF SQLCODE <> 0 THEN
         MOVE "# ERROR: Unable to connect to database." TO in-log-line
         PERFORM DO-LOG
         MOVE db-url TO in-log-line
         PERFORM DO-LOG
         PERFORM DO-EXIT
         EXIT PROGRAM
        END-IF
        SET db-connected TO TRUE
       ELSE
        MOVE "# WARNING: Database is already open." TO in-log-line
        PERFORM DO-LOG
       END-IF.

******* Function: DO-EXECUTE-STATEMENT
******* Arguments: db-statement
******* Executes the statement on the database.
       DO-EXECUTE-STATEMENT.
       IF db-connected THEN
        EXEC SQL
         EXECUTE IMMEDIATE :db-statement
        END-EXEC
        IF SQLCODE < 0 THEN
         MOVE "# ERROR: Error executing the query." TO in-log-line
         PERFORM DO-LOG
         MOVE db-statement TO in-log-line
         PERFORM DO-LOG
         IF exit-on-error THEN
          PERFORM DO-EXIT
          EXIT PROGRAM
         END-IF
        END-IF
        IF SQLCODE > 0 THEN
         MOVE "# ERROR: Warning executing the query." TO in-log-line
         PERFORM DO-LOG
         MOVE db-statement TO in-log-line
         PERFORM DO-LOG
         IF exit-on-warning THEN
          PERFORM DO-EXIT
          EXIT PROGRAM
         END-IF
        END-IF
       ELSE
        MOVE "# WARNING: Database is closed." TO in-log-line
        PERFORM DO-LOG
       END-IF.

******* Function: DO-EXECUTE-STATEMENT
******* Arguments: N/A
******* Disconnects from the database.
       DO-DISCONNECT.
       IF db-connected THEN
        EXEC SQL
         DISCONNECT
        END-EXEC
        IF SQLCODE <> 0 THEN
         MOVE "# ERROR: Unable to disconnect from database."
         TO in-log-line
         PERFORM DO-LOG
        END-IF
        SET db-disconnected TO TRUE
       END-IF.

******* Name: DO-EXIT
******* Arguments: N/A
******* Cleans up any acquired resources and exits the application.
       DO-EXIT.
       PERFORM DO-DISCONNECT
       CLOSE in-parameters
       CLOSE in-log.

******* Function: TABLE-ADD-VALUE
******* Arguments: index-row, index-col, index-col-value
******* Sets the value at the given row, column index and marks the location
******* as used. Note that the row, column indices must be [1, 50]
       TABLE-ADD-VALUE.
       IF 1 <= index-row AND index-row <= 50 THEN
        IF 1 <= index-col AND index-col <= 50 THEN
         SET table-row-used(index-row) TO TRUE
         SET table-column-used(index-row, index-col) TO TRUE
         MOVE index-col-value TO table-value(index-row, index-col)
        END-IF
       END-IF.

******* Function: TABLE-CLEAR
******* Arguments: N/A
******* Clears the table by marking all row, column pairs as unused.
******* Moves the row, column cursor to 1, 1
       TABLE-CLEAR.
       MOVE SPACES TO index-row-value
       MOVE SPACES TO index-col-value
       PERFORM VARYING index-row FROM 1 BY 1 UNTIL index-row>50
       SET table-row-unused(index-row) TO TRUE
        PERFORM VARYING index-col FROM 1 BY 1 UNTIL index-col>50
         SET table-column-unused(index-row, index-col) TO TRUE
         MOVE SPACES TO table-value(index-row, index-col)
        END-PERFORM
       END-PERFORM
       MOVE 1 TO index-row
       MOVE 1 TO index-col.

******* Function: TABLE-PRINT
******* Arguments: N/A
******* Iterates over each used column in each used row in the table.
******* Joins the column values with the given seperator AND delegates the
******* processing of the result to -ONCOLUMNRESULT AND -ONROWRESULT.
******* (Template Method Design Pattern)
       TABLE-PRINT.
       PERFORM VARYING index-row FROM 1 BY 1 UNTIL index-row>50
        IF table-row-used(index-row) THEN
         MOVE SPACES TO index-row-value
         MOVE 1 TO table-print-index
         PERFORM VARYING index-col FROM 1 BY 1 UNTIL index-col>50
          IF table-column-used(index-row, index-col) THEN
           MOVE SPACES TO index-col-value
           MOVE table-value(index-row, index-col) TO index-col-value
           PERFORM TABLE-PRINT-ONCOLUMNRESULT
           STRING index-col-value DELIMITED BY SIZE
            INTO index-row-value WITH POINTER table-print-index
           END-STRING
           STRING table-seperator DELIMITED BY SIZE
            INTO index-row-value WITH POINTER table-print-index
           END-STRING
          END-IF
         END-PERFORM
         PERFORM TABLE-PRINT-ONROWRESULT
        END-IF
       END-PERFORM.

******* Function: TABLE-PRINT-ONCOLUMNRESULT
******* Arguments: index-col, index-col-value
******* Occurs when a column has been processed.
       TABLE-PRINT-ONCOLUMNRESULT.

******* Function: TABLE-PRINT-ONROWRESULT
******* Arguments: index-row, index-row-value
******* Occurs when a row has been processed.
       TABLE-PRINT-ONROWRESULT.
       MOVE index-row-value TO out-temp-line
       WRITE out-temp-line.